MySQL Web Server, Jsp, Java, Tomcat, J2Ee, Hibernate Programming Blog

Page 31 ay ry 2 CHAPTER 11 POSTGRESQL ADMINISTRATION Table 11-1. Principal Files in the bin Directory (Continued) Program Description pg_dump Utility to back up a database pg_dumpall Utility to back up all databases in an installation pg_restore Utility to restore a database from backup data vacuumdb Utility to help optimize the database ipcclean Utility to delete shared memory segments after a crash (Linux only) pg_config Utility to report PostgreSQL configuration createlang Utility to add support for language extensions (see Chapter 10) droplang Utility to delete language support ecpg Embedded SQL compiler (optional, see Chapter 14) The data Directory The data directory contains subdirectories with data files for the base installation, and also the log files that PostgreSQL uses internally. Normally, you never need to know about the subdirectories of the data directory. Also in this directory are several configuration files, which contain important configuration settings you may wish, or need, to change. Table 11-2 lists the user-accessible files in the data subdirectory. Table 11-2. User-Accessible Files in the data Subdirectory Program Description pg_hba.conf Configures client authentication options pg_ident.conf Configures operating system to PostgreSQL authentication name mapping when using ident-based authentication PG_VERSION Contains the version number of the installation, for example 8.0 postgresql.conf Main configuration file for the PostgreSQL installation postmaster.opts Gives the default command-line options to the postmaster program postmaster.pid Contains the process ID of the postmaster process and an identification of the main data directory (this file is generally present only when the database is running) The pg_hba.conf File The hba (host based authentication) file tells the PostgreSQL server how to authenticate users, based on a combination of their location, type of authentication, and the database they wish to access.
We would like to recommend you tested and proved virtual web hosting services, which you will surely find to be of great quality.

Page 31 ay ry 2 310 CHAPTER 11 POSTGRESQL ADMINISTRATION On a Windows system, by default, your installation base directory will be something like C:\Program Files\PostgreSQL\8.0.0, under which you will find several subdirectories. On Linux, the base directory for a source code installation will generally be /usr/local/pgsql. For a prebuilt binary installation, the location will vary. A common location is /var/lib/pgsql, but you may find that some of the binary files have been put in directories already in the search path, such as /usr/bin, to make accessing them more convenient. Under the PostgreSQL base installation directory, you will normally find around seven subdirectories, depending on your options and operating system: bin data doc include lib man share On Windows, the man subdirectory will be omitted, but probably at least one additional subdirectory, pgAdmin III, will be present. You will find additional directories, such and jdbc and odbc, if you installed some of the optional components. In this section, we will take a brief tour of the seven subdirectories, and along the way look at the more important configuration files and the significant options in them that we might wish to change. The bin Directory The bin directory contains a large number of executable files. Table 11-1 lists the principal files in this directory. Table 11-1. Principal Files in the bin Directory Program Description postgres Database back-end server postmaster Database listener process (the same executable as postgres) psql Command-line tool for PostgreSQL initdb Utility to initialize the database system pg_ctl PostgreSQL control start, stop, and restart the server createuser Utility to create a database user dropuser Utility to delete a database user createdb Utility to create a database dropdb Utility to delete a database
From our experience, we are can tell you that you can find a reliable and cheap webhost service at Java Web Hosting services.

Page 30 ay CHAPTER 11 ry 2 PostgreSQL Administration In this chapter, we will look at how to care for a PostgreSQL database. This covers items ranging from configuring access to the system through managing the placement of database files, maintaining performance, and, crucially, backing up your system. As we progress through this chapter, we will cover the following topics: System-level configuration of a PostgreSQL installation Database initialization Server startup and shutdown User and group management Tablespace management Database and schema management Backup and recovery Ongoing maintenance of a PostgreSQL server While learning and experimenting with these administrative tasks, you will want to use a test PostgreSQL system that doesn t contain any information you particularly care about. Making experimental system-wide changes or testing backup and restore procedures on a PostgreSQL database that contains live data is not a good idea. System Configuration We saw in Chapter 3 how to install PostgreSQL, but we didn t really look in any depth at the resulting directory structure and files. Now we will explore the PostgreSQL file system and main system configuration options. The PostgreSQL file system layout is essentially the same on Windows and Linux platforms. On a Linux system, the base directory of the installation will vary slightly, depending on which installation method you used: installing from prepackaged executables, such as binary RPMs, or compiling it yourself from source code. There may also be fewer or more directories, depending on which options you installed.
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

Page 30 ay ry 2 Page 30 ay ry 2
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.

Page 30 ay Summary ry 2 CHAPTER 10 FUNCTIONS, STORED PROCEDURES, AND TRIGGERS In this chapter, we looked at ways in which we can extend the functionality of PostgreSQL queries. We have seen that PostgreSQL provides many operators and functions that we can use to refine queries and extract information. The procedural languages supported by PostgreSQL allow us to develop quite sophisticated server-side processing by writing procedures in PL/pgSQL, SQL, and other languages. This provides the opportunity for the database server to implement complex application functionality independently of the client. Stored procedures are stored in the database itself and may be called by the application or, in the form of triggers, called automatically when changes are made to database tables. This gives us another means of enforcing referential integrity. For simple referential integrity, it s generally best to stick to constraints, as they are more straightforward, efficient, and less error-prone. The power of triggers and stored procedures comes when your declarative constraints become very complex, or you wish to implement a constraint that is too complex for the declarative form. Now that we have covered some advanced PostgreSQL techniques, in the next chapter, we will move on to the topic of how to care for a PostgreSQL database.
From our experience, we can recommend PHP5 Web Hosting services, if you need affordable webhost to host and run your web application.

Page 30 ay ry 2 306 CHAPTER 10 FUNCTIONS, STORED PROCEDURES, AND TRIGGERS bpfinal=# bpfinal=# SELECT customer_id, fname, lname FROM customer; customer_id | fname | lname ————-+———–+——–1 | Jenny | Stones 2 | Andrew | Stones 4 | Adrian | Matthew 5 | Simon | Cozens 6 | Neil | Matthew 7 | Richard | Stones 8 | Ann | Stones 9 | Christine | Hickman 10 | Mike | Howard 11 | Dave | Jones 12 | Richard | Neill 13 | Laura | Hardy 14 | Bill | O’Neill 15 | David | Hudson (14 rows) bpfinal=# Why Use Stored Procedures and Triggers? There are numerous reasons for using stored procedures and triggers. Here are some of them: Provide central validation: We can enforce conditions on table updates in one place, independent of our client applications. If the conditions need to change, they change in one place only. Track changes: We can use a trigger to create an audit trail, writing to another table when rows in a table are updated. This could record the user who made the change, the time, the date, and perhaps even the data that changed. Enhance security: By using the PostgreSQL current_user variable, we can enforce our own security. Defer deletions: We could use a trigger to mark rows for deletion at a later date, rather than deleting them when an application tries to. Provide a mapping for clients: We can use triggers and stored procedures to create a simpler, single-table version of some of our data that can be updated more easily by our users. For example, we could create a table and link it to Microsoft Excel. As rows in this table are updated, we can update rows in the real tables, which might have a more complex structure.
We recommend you use shared web hosting services, because many users agree that it is cheap, reliable and customer-satisfying webhost.

Page 30 ay ry 2 CHAPTER 10 FUNCTIONS, STORED PROCEDURES, AND TRIGGERS bpfinal=# DELETE FROM customer WHERE customer_id = 15; NOTICE: deletion aborted: outstanding orders present DELETE 0 bpfinal=# A notification is returned regarding outstanding orders. We see the delete affected no rows, as it was not allowed to proceed. Deleting customers with no outstanding orders presents no problem, as long as we delete the entries in the orderinfotable first. This is because we are using a constraint on this table to prevent just this sort of maverick deletion. The trigger takes care of this and, therefore, provides us with another way to approach the referential integrity issue. Customer number 3 has no outstanding orders, so we can delete that customer and let the trigger do all the work: bpfinal=# DELETE FROM customer WHERE customer_id = 3; NOTICE: deletion allowed: no outstanding orders DELETE 1 bpfinal=# Checking the tables show no lingering sign of our deleted customer: bpfinal=# SELECT * FROM orderinfo; orderinfo_id | customer_id | date_placed | date_shipped | shipping ————–+————-+————-+————–+———2 | 8 | 2004-06-23 | 2004-06-24 | 0.00 4 | 13 | 2004-09-03 | 2004-09-10 | 2.99 5 | 8 | 2004-07-21 | 2004-07-24 | 0.00 3 | 15 | 2004-09-02 | | 3.99 (4 rows) bpfinal=# SELECT * FROM orderline; orderinfo_id | item_id | quantity ————–+———+———2 | 1 | 1 2 | 10 | 1 2 | 7 | 2 2 | 4 | 2 3 | 2 | 1 3 | 1 | 1 4 | 5 | 2 5 | 1 | 1 5 | 3 | 1 (9 rows)
We recommend cheap and reliable webhost to host and run your web applications: Coldfusion Web Hosting services.

Page 30 ay ry 2 304 CHAPTER 10 FUNCTIONS, STORED PROCEDURES, AND TRIGGERS — for referential integrity we have to tidy up — we will need to delete all completed orders — but first delete the information about the orders for order_record in select * from orderinfo where customer_id = old.customer_id loop delete from orderline where orderinfo_id = order_record.orderinfo_id; end loop; — now delete the order records delete from orderinfo where customer_id = old.customer_id; — return the old record to allow customer to be deleted return old; else — orders present return NULL to prevent deletion raise notice ‘deletion aborted: outstanding orders present’; return NULL; end if; end; $$ language plpgsql; create trigger trig_customer before delete on customer for each row execute procedure customer_trigger(); To verify the behavior of this trigger, let s check it out. First, make one of the orders have a NULL shipped date to indicate that it is still pending: bpfinal=# UPDATE orderinfo SET date_shipped = NULL WHERE orderinfo_id = 3; UPDATE 1 bpfinal=# SELECT * FROM orderinfo; orderinfo_id | customer_id | date_placed | date_shipped | shipping ————–+————-+————-+————–+———1 | 3 | 2004-03-13 | 2004-03-17 | 2.99 2 | 8 | 2004-06-23 | 2004-06-24 | 0.00 4 | 13 | 2004-09-03 | 2004-09-10 | 2.99 5 | 8 | 2004-07-21 | 2004-07-24 | 0.00 3 | 15 | 2004-09-02 | | 3.99 (5 rows) bpfinal=# Now try to delete customer number 15 whose order number 3 is outstanding:
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

Page 30 ay ry 2 CHAPTER 10 FUNCTIONS, STORED PROCEDURES, AND TRIGGERS 303 Table 10-12. PostgreSQL Trigger Procedure Variables Variable Description NEW A record containing the new database row OLD A record containing the old database row TG_NAME A variable containing the name of the trigger that fired and caused the trigger procedure to run TG_WHEN A text variable containing the text ‘BEFORE’or ‘AFTER’, depending on the type of the trigger TG_LEVEL A text variable containing ‘ROW’ or ‘STATEMENT’, depending on the trigger definition TG_OP A text variable containing ‘INSERT’, ‘DELETE’, or ‘UPDATE’, depending on the event that occurred resulting in this trigger being fired TG_RELID An object identifier representing the table the trigger has been activated upon TG_RELNAME The name of the table that the trigger has been fired upon TG_NARGS An integer variable containing the number of arguments specified in the trigger definition TG_ARGV An array of strings containing the procedure parameters, starting at zero; invalid indexes return NULL values Try It Out: Create Another Trigger As a final example, we can create another trigger function, which prevents a customer from being deleted if there are any orders outstanding for that customer. We check whether the date_shipped column in the orderinfo table is NULL for any orders placed by the customer about to be deleted, and disallow the deletion. If there are no outstanding orders, we can allow the deletion to go ahead, but we need to tidy up by deleting orders placed by this customer in the past, as well as the information about the items in those orders. create function customer_trigger() returns trigger AS $$ declare order_record record; beginx — about to delete a customer — disallow if orders pending select * into order_record from orderinfo where customer_id = old.customer_id and date_shipped is NULL; if not found then — all OK, delete of customer can proceed raise notice ‘deletion allowed: no outstanding orders’;
Check Tomcat Web Hosting services for best quality webspace to host your web application.

Page 30 ay ry 2 302 CHAPTER 10 FUNCTIONS, STORED PROCEDURES, AND TRIGGERS Load the function and trigger definition from a script file: bpfinal=# \i sproc.sql … CREATE FUNCTION CREATE FUNCTION1; CREATE TRIGGER bpfinal=# Then try adjusting the stock of an item so that it drops to 3 or below: bpfinal=# UPDATE stock SET quantity = 3 WHERE item_id = 1; NOTICE: in trigger, mq is 3 UPDATE 1 bpfinal=# We can see that the trigger has fired and raised a notice to tell us. The update to the stock proceeds, but the trigger also updates the reorders table, adding a new row: bpfinal=# SELECT * FROM reorders; item_id | message ———+————1 | Wood Puzzle (1 row) bpfinal=# How It Works The trigger procedure is called whenever an INSERTor UPDATE takes place on the stock table. It checks the stock quantity as it appears after the UPDATE has taken place, and if it is less than the minimum quantity, adds a record to the reorders table. You may have noticed that we have used a couple of new features in the trigger procedure. First, the trigger procedure arguments are not referred to as $1, $2, and so on. The arguments are made available via one of a number of special variables for automatically triggering procedures. The arguments are passed in an array named TG_ARGV, starting at TG_ARGV[0]. The special variables made available to trigger procedures are listed in Table 10-12. Second, inside the trigger procedures, two very special records are made available: OLDand NEW. These contain (for ROW triggers) the data from the row being affected by the UPDATE that fired the trigger. As you may guess, OLDcontains data from before the UPDATE, and NEW contains data from after the update (or proposed UPDATE for a BEFORE trigger).
Check Tomcat Web Hosting services for best quality webspace to host your web application.