PgAccess: A Graphical Client
PgAccess is a graphical administration application for PostgreSQL. It is designed to be similar in function to PC database software, such as Microsoft Access.
Figure 6-1 displays the main PgAccess application window.
The interface allows you to view and modify various aspects of your PostgreSQL database using graphical representations of database elements, such as tables, queries, and views (among others). It can be a convenient escape from the sometimes tedious task of using the psql command line interface.
PgAccess was written in the Tcl/Tk scripting language; this increases its level of portability, as it can be installed and run on any system that supports the Tcl/Tk scripting language (including Linux/UNIX, Windows, and MacOS). As PgAccess is a client side application, PostgreSQL is not required to be on the machine running PgAccess.
Installation and Basic Configuration
There are relatively few things you will need to do to configure PgAccess for use with PostgreSQL. Most importantly, you will need to make sure that Tcl/Tk is installed and configured properly.
To configure PostgreSQL with TCL support, you must have used the --with-tcl flag during source compilation. The use of the --with-tcl flag will configure the appropriate tcl libraries for use with PostgreSQL. The use of this flag will install the pgaccess binary for you.
 | PgAccess will not operate unless you have configured PostgreSQL to support Tcl/Tk. Linux distributions that come with PostgreSQL such as Red Hat and Mandrake should have TCL support compiled in to their PostgreSQL binaries. |
If you did not use the --with-tcl flag during your original compilation you can add TCL support to your existing PostgreSQL configuration without having to reinitialize the PostgreSQL data directories. This is done by re-configuring PostgreSQL using the --with-tcl flag, and then recompiling.
After the re-configuration is finished, clean up the directory by typing: gmake clean, and then compile the code by typing: gmake. Finally, shut down postmaster and type the command: gmake install. This will install the new binaries and libraries for the re-configured PostgreSQL system. Once these are installed you can restart PostgreSQL.
 | If you are going to re-compile PostgreSQL after it has been installed, you must use source from the same version of PostgreSQL that you originally compiled. If you use a different version of the source, you may lose data. As always, it is a good idea to backup your data before performing any changes to your PostgreSQL installation. |
Managing Users
PgAccess provides the ability to graphically modify and manage user accounts associated with the database. Like with command-line clients, it uses CREATE USER and ALTER USER to accomplish these tasks. The difference is that PgAccess provides a graphical front-end to these commands. This feature is available through the Users tab on the left side of the PgAccess window. Figure 6-2 shows this tab.
Clicking on Users will display a list of all users associated with the database. Of the program's three action buttons, only New and Design perform actions from this tab.
Clicking New will allow you to create a new user within the database. As you may notice, the options in this window correlate with the options available through the use of the SQL command, CREATE USER. Use the User name field to set the new user's username, and the Password and verify password fields to set the password (if there will be one). The two check boxes set the CREATEDB and CREATEUSER permissions for the new user, if checked (remember that allowing these permissions creates the new user as a database superuser). You may use the Valid until field to set the valid-until date for the user (the same as the CREATE USER command).
Clicking the Design button allows you to modify the attributes of the selected user account as you would normally do with the SQL command, ALTER USER. As such, the options here correlate with the options available through ALTER USER, such as modifying the username, changing the password, setting the previously mentioned CREATEDB and CREATEUSER permissions, and modifying the valid-until date.
Managing Groups
As of version 0.98.7, the most current version of PgAccess at the printing of this book, PgAccess does not support the management of groups. You can use the command line interface psql to create and modify PostgreSQL user groups.
Creating Databases
To create a database using PgAccess, click the "Database" menu option at the top of the screen, then click "New" and type in the name you wish to give the database. This will create a database as if you had called the SQL CREATE DATABASE command from the currently logged in database user. To use a template other than template1 for the new database or set its encoding type, you will need to either use the createdb, or the CREATE DATABASE command from a command line client.
Creating Tables
It is relatively easy to create and modify the tables of a database with PgAccess through its graphical interface. Figure 6-3 shows this dialog window.
To create a table, first click on the Tables tab, then click the New button to open the "Create new table" window. This window contains various fields and buttons that allow you to modify the attributes of the table being created. Basically, these commands are visual representations of the options found in the SQL CREATE TABLE command.
The following is the list of available inputs within the PgAccess Create Table Dialog.
- Table name
The name of the table you wish to create within PostgreSQL.
- Inherits
You can set what table(s) this table will inherit attributes from. A list of available tables is located in the drop-down box. Note that the list is not restricted to holding just one value; you can click the downward arrow button and choose another table to add that table to the inheritance list.
- Check
Enter any expressions you wish to have checked on INSERT and UPDATE commands.
- Constraint
Enter any constraints you wish to place upon the table.
To add a field to the table, set its attributes with the field name, type, size, and Default value fields, and set its options with the field cannot be null and primary key check box options. Once you have chosen the options, click the Add field button to add the field to the field list. You are able to move fields up and down through the list with the Move up and Move down buttons, delete a field with the Delete field button, and delete all fields with the Delete all button.
Once you are ready to add the table into your database, click the Create button. The following subsections discuss how to insert and delete of rows.
Inserting and updating values
It is possible to insert values into a table using PgAccess; in fact, the process to do so is fairly simple. Click on the Table tab to view the list of tables, then click on the table you wish to modify and click Open.
After clicking open, you should see a window of rows and columns that contain the various fields of your table. You can tab through these columns and rows to reach a target field, or just use your mouse to click on it. The row chosen will become highlighted and a cursor will display, showing you the location where new data will appear. The database will not be updated with your changes until you tab out of the field you were editing; or, alternatively, click into another field with your mouse. PgAccess displays the message: "Updating database..." after you complete one of these actions.
It may be useful to note that it is possible to both sort and filter the table data; this is accomplished by making use of two fields at the top of the table window, named logically "Sort field" and "Filter conditions." It is possible to sort the table by a field or multiple fields by typing the name of the field into the "Sort field" box, optionally including "ASC" or "DESC" if you wish for the sort to be ascending or descending, respectively. You can choose to sort by multiple fields. To do this, include the names of other fields in a comma-delimited list. As an example, you could use the following to sort a list of names by the lastname field, ascending:
lastname ASC
To use the "Filter conditions" box, enter filter conditions such as the following:
(age < 45) and (avgsalary > 40000)
The process for updating table data is the same as for inserting, but you change existing rows rather than adding new ones.
Deleting Values
To delete values from a table, open it in the same manner you would when attempting to insert values: click on the Tables tab, then click on the table you wish to modify and click Open. Within this window are the columns and rows of the table, filled with whatever data has been entered. You can delete either delete rows, or specific fields within a row. To delete a row, click on the desired row, then hit the delete key on your keyboard. PgAccess will display a dialog box asking for confirmation of the delete, in case your choice to delete was accidental. To delete the contents of a field, or the partial contents of a field, click or tab into that field and use the backspace key to delete characters.
Using Queries
As should be expected, you are able to design, edit, and run queries through PgAccess. Click on the Queries tab to view a list of the defined queries associated with your database. This area of the program should be familiar to Microsoft Access users, as the visual query designer and other features are very similar to their counterparts within that program.
To create a new query, click the New button. This will open the "Query builder" window. Before designing the query, you should name it with the Query name field. This name is arbitrary and serves no function within the query; it is needed only so that PgAccess has something to display for this query in the list of available queries. You may also add comments in the comment window at this point.
Manually designing a query
After naming the query, you can either design it manually or use the visual designer tool to speed up the process. To manually design the query, use the large, white box below the Query name field to type in the SELECT statement that will be used to query the database. You can spread this statement out over multiple lines, if you wish.
Using the visual designer
To use the visual designer tool for creation of the new query, click on the Visual designer button. As stated before, the interface to this tool is similar to the query designer tool in Microsoft Access. You are initially given a blank canvas to work with. Add tables to the canvas by typing the name of the table in the Add table field (the cursor enters text into this field by default). Alternatively, you can add tables by clicking the down-arrow button and selecting the table you wish to add from its list of available tables.
Once you've added the tables you wish to use, you can form links between them by clicking and dragging on a field, then pulling it from one table object to the other. When a link is formed it will display as a thin line that connects the two objects together. Note that you can move table objects around the canvas and the link graphic will stretch to fit whatever arrangement you desire. You may delete tables from the canvas by clicking on their labels and hitting the "delete" key on your keyboard. Similarly, links may be deleted between columns by clicking on them and pressing the "delete" key.
Any links between corresponding table columns will be translated into a SQL WHERE clause, specifying conditions upon which to join two table sets. A link will only represent a condition involving the equal-to operator (=). If you require a different condition, the SQL statement can be edited manually in the "Query builder" window; bear in mind that going back to the Visual Designer will cause any modified relationship to be re-created as an equal-to relationship when it is saved.
Figure 6-4 shows the PgAccess Visual Designer interface. It illustrates a fairly involved SQL query, reproduced in a more comprehensible, graphical form.
To select fields that you wish to be included in the results of the query, drag the field name down into the result zone (the cell-divided area at the bottom of the screen). You may define conditions you wish to be applied to results from the query; do this by entering a condition into the "Criteria" field. To see the SQL statement you have created with the visual design, click the Show SQL button. To execute your query (for testing purposes), click the Execute SQL button. When you are done creating the query's design, click on the Save to query builder button.
Executing a query
To view the results of an existing query, click the "Queries" tab, select the desired query from the list and click on the Open button. This will display the retrieved rows in a window similar to the window used for modifying tables, though this table is read-only. You can use the Sort field to sort the records by an expression, or the Filter conditions field to provide a filter expression.
Modifying a query
To modify an existing query, select it in the query list on the query tab and click the Design button. This will display the "Query builder" window, which is the same view as if you were to create a new query. The query's name and SQL statement will be displayed in the window, as well as any comments you added onto it when it was originally designed. From here you can either edit the SQL statement directly or use the visual designer.
Remember to click the "Save query definition" button to save your modifications to an existing query.
Creating Functions
Creating functions within PgAccess is also fairly simple. First click on the Functions tab, then click New. You should now be presented with the "Function" window. Here, you may enter the name of your new function, the parameters it takes (comma-separated), the language it is written in (e.g., SQL, C, plpgsql etc.), and the type of data it returns (if the function returns a value). Once you have defined those options, enter the body of the function in the white box that takes up most of the screen (or the location in the filesystem of the shared object file, if it is a C function). Once finished, click Save.
| You may view existing function definitions by selecting one from the function list, and clicking Open. |
Figure 6-5 illustrates a simple example function, which selects the name of an author based on their id value in the authors table.
See Chapter 7 for more detailed information on creating functions.