Using PostgreSQL with Tcl and Tcl/Tk

Client 1Connecting to the Server

The first step to interacting with a PostgreSQL server is to establish a connection; in this section, you'll use Tcl and Tk to build a simple graphical client that establishes a connection to a PostgreSQL server. The libpgtcl library is implemented on top of the libpq, so many of the features that you see in libpgtcl will seem familiar if you've read through Chapter 8, "The PostgreSQL C APIlibpq." To connect to a PostgreSQL server, use the pg_connect procedure. pg_connect comes in two flavors:

pg_connect -conninfo connection-string

or

pg_connect database-name [-host host-name] [-port port-number] [-tty tty-name] [-options option-string]

The second form is considered obsolete, and I've included it here only for completeness.

The preferred form uses a connection string similar to those used in libpq applications. A connection string is a list of keyword=value pairs, separated by whitespace. Each pair in the connection string specifies the value for a connection property. A typical connection string might look something like this:

host=davinci user=bruce password=koalas dbname=movies

This particular connection string provides four connection properties: a hostname, a username and password, and a database name. Table 16.1 lists the properties that may appear in a connection string.

The second column in Table 16.1 shows the environment variable that libpgtcl will use if you omit the property shown in the first column. For example, if you omit the host property from your connection string, libpgtcl will use the value of the PGHOST environment variable. If you don't supply a particular property in the connection string, and you haven't defined the corresponding environment variable, libpgtcl will use hard-wired default values. To see the hard-wired values, you can use the pg_conndefaults[1] procedure:

[1] I've cleaned up the listing returned by pg_conndefaults to make it easier to read.

$ tclsh % package require Pgtcl 1.4 % foreach prop [pg_conndefaults] { puts $prop } authtype Database-Authtype D 20 {} service Database-Service {} 20 {} user Database-User {} 20 korry password Database-Password * 20 {} dbname Database-Name {} 20 korry host Database-Host {} 40 {} hostaddr Database-Host-IPv4-Address {} 15 {} port Database-Port {} 6 5432 tty Backend-Debug-TTY D 40 {} options Backend-Debug-Options D 40 {} requiressl Require-SSL D 1 0 sslmode SSL-Mode {} 8 prefer

The first column lists property names; the last column displays the final default values that will be used if you don't provide overrides.

The pg_conndefaults procedure returns a list of sublists. The values returned by pg_conndefaults might seem a little confusing until you understand the problem that this procedure was trying to solve. From time to time, the PostgreSQL authors need to introduce new connection properties. How can you support new connection properties without rewriting every PostgreSQL client application? The client application can ask pg_conndefaults for a list of supported properties and then ask the user to provide a value for each of those properties. A robust client application will not have to be recompiled each time a new connection property is introduced; it just prompts the user for more information.

Having said that, you probably won't let me off the hook unless we build a "robust" client application (or at least make an attempt).

The first client application in this chapter does little more than connect to a PostgreSQL server, but does so using a self-adjusting login dialog. This particular client application is rather longbuilding a graphical login dialog from barebones Tcl/Tk is not a trivial task. In a real-world application, you might want to explore add-on toolkits that make it easier to do this sort of work.

Let's dive into the code for client1.tclI'll explain how to use pg_conndefaults as we go. You'll also see how to call the pg_connect procedure. Listing 16.1 shows the start of the client1.tcl application.

Listing 16.1. client1.tclmain

1 #!/usr/local/bin/wish 2 # 3 # Filename: client1.tcl 4 5 proc main { } { 6 7 package require Pgtcl 8 9 wm withdraw . 10 11 set result "retry" 12 13 while { $result == "retry" } { 14 set connstr [connect_dialog] 15 16 if { [catch {pg_connect -conninfo $connstr} conn] } { 17 set result [tk_messageBox 18 -message $conn 19 -title "Connection failed" 20 -type retrycancel] 21 } else { 22 tk_messageBox 23 -message "Connection is: $conn" 24 -title "Connection Ok" 25 26 set result "ok" 27 } 28 } 29 }

The first line specifies the name of the interpreter that should be used to run this script: wish is the graphical Tcl/Tk shell[2]. Line 5 defines a procedure named main. Unlike many other languages, a function with the name of main is not the default entry point for Tcl scriptI'll call this function main just so that it is easily recognizable. In Tcl, the entry point for a program is the first executable line of code outside of a proc definition. In fact, the first few executable lines of code in this program are right at the end of the script (the end of this script is not shown in Listing 16.1; you still have four more listings to get through).

[2] The magic string at the beginning of a shell script such as this is called the shebang line: "she" is for shell and "bang" is how some people pronounce the exclamation point. A shebang line tells the operating system which program should be used to execute the script. Shebang lines are supported on Unix and Linux hosts, but not on Windows systems (except when using the Cygwin environment).

The main function expects no arguments (you can tell that because the braces immediately following the function name are empty).

The first thing that you do in this function is load the libpgtcl library into the Tcl interpreterthat's what the package require statement on line 7 does (if you're using a version of PostgreSQL older than 8.0, use the command load libpgctl instead). Before you can call any PostgreSQL-related functions, you must load the libpgtcl library.

Next, withdraw the root window. If you are not a seasoned Tk programmer, that probably sounds a little ominous. When the wish interpreter starts up, it automatically creates an empty window for you. That window is called a root window, and its name is simply the period character (.). You withdraw the window now so that you can make your own window a little later.

Lines 13 through 28 form a loop. Inside this loop, you create a dialog that prompts the user for connection properties. Figure 16.1 shows the dialog that appears when you run client1.tcl (you may see a slightly different dialog than the one shown in Figure 16.1 depending on which version of libpgtcl you are using).

Figure 16.1. The Connection Info dialog.

If the user clicks the Cancel button, the entire application will end. If the user clicks the Connect button, it tries to connect to a PostgreSQL server using the information provided. If the connect attempt succeeds, a message displays and the application terminates. If a connection attempt fails, you want the user to see a Retry/Cancel dialog that displays the error message and offers a chance to try again.

Repeat the loop at lines 13 through 28 until you establish a connection or until the user presses the Cancel button.

At line 14, call the connect_dialog procedure (you'll see that procedure in a moment) to display the connection dialog and wait for user input. connect_dialog returns a connection string, which is awfully handy because you need a connection string before you can talk to PostgreSQL.

After you have a connection string, call the pg_connect function to attempt a connection. When pg_connect is called, it either establishes a connection or it throws an error. You want to intercept any error messages, so you call pg_connect within a catch{} block. If the call to pg_connect succeeds, catch{} will return 0 (also known as TCL_OK). If pg_connect throws an error, the catch{} command will return a value other than zero. In either case, the conn variable (the third argument to the catch{} command) is modified. In the case of a connection failure, conn will contain the text of the error message. If the connection attempt is successful, conn will contain a connection channel. A channel is similar to a handle (handles are used in many programming language/API combinations). A channel is simply a unique identifier returned by the APIyou give the identifier back to the API when you want to do something with that connection (like execute a command). Like everything else in Tcl, a channel is a string.

If you were not able to establish a connection, display a message to the user by using the tk_messageBox function (see line 17). A typical error message is shown in Figure 16.2.

Figure 16.2. The Connection dialog, Connection failed error message.

After displaying the error message, tk_messageBox waits for the user to click either the Retry button or the Cancel button. tk_messageBox returns a string telling you which option the user selected (either retry or cancel). You store that string in the result variable, which controls the loop. So, if the user clicks the Retry button, you repeat the loop; otherwise, end the loop and terminate the application.

If the connection attempt succeeds, use tk_messageBox again. In this case, display the channel (not really useful but mildly interesting), as shown in Figure 16.3.

Figure 16.3. The Connection dialog, Connection OK message.

That covers the main() function; now let's see how to build a dialog using Tcl/Tk. (I should warn you; it's not pretty.)

Listing 16.2 shows the connect_dialog procedure. This procedure constructs a dialog that prompts the user for connection properties, displays the dialog, and assembles a connection string with the values supplied by the user.

Listing 16.2. client1.tcl-connect_dialog

31 proc connect_dialog { } { 32 33 global next_row 34 35 set next_row 0 36 set set_focus true 37 38 # Create a new window with the title 39 # "Connection Info" 40 # 41 set w [toplevel .dlg] 42 wm title .dlg "Connection Info" 43 44 # Create the labels and entry fields for this dialog 45 # 46 47 foreach prop [pg_conndefaults] { 48 49 set varname [lindex $prop 0] 50 set label_text [lindex $prop 1] 51 set type [lindex $prop 2] 52 set length [lindex $prop 3] 53 set default [lindex $prop 4] 54 55 if { $type != "D" } { 56 57 global $varname 58 59 set $varname $default 60 61 set entry [add_label_field .dlg $label_text $varname] 62 63 if { $type == "*" } { 64 $entry configure -show "*" 65 } 66 67 if { $set_focus == "true" } { 68 focus -force $entry 69 set set_focus false 70 } 71 } 72 } 73 74 # Create the "Connect" and "Cancel" buttons 75 add_button .dlg.default "Connect" {set result Ok} 1 76 add_button .dlg.cancel "Cancel" {exit} 2 77 78 .dlg.default configure -default active 79 80 vwait result 81 82 set result "" 83 84 foreach prop [pg_conndefaults] { 85 86 set type [lindex $prop 2] 87 88 if { $type != "D" } { 89 90 set varname "$[lindex $prop 0]" 91 set varval [subst $varname] 92 93 if { $varval != "" } { 94 append result "[lindex $prop 0]=$varval " 95 } 96 } 97 } 98 99 destroy .dlg 100 101 return $result 102 }

You can find Tk extension libraries that make dialogs easier to build, but we'll build our own so you can stick to plain vanilla Tcl/Tk code.

Lines 33, 35, and 36 initialize a few variables that you will be using in this procedure; I'll explain the purpose of each variable as we go.

To construct the dialog shown in Figure 16.1, you will create a new toplevel widget named .dlg (at line 41). The toplevel widget automatically resizes as you add more widgets to it. To manage the placement of child widgets within .dlg, you will use the grid layout manager. The grid layout manager arranges child widgets in a grid (makes sense so far). You build a grid with two columns: A text label goes in the left column and the corresponding text entry widget goes in the right column. You use the next_row global variable to keep track of which grid row you are working on.

At line 47, enter a loop that iterates through each connection property returned by pg_conndefaults. Remember, pg_conndefaults returns a list of connection properties and enough information about each property so that you can construct a connection dialog. pg_conndefaults returns a list of sublists: Each sublist corresponds to a single connection property. There are five items in each sublist, and you pick apart the items at lines 49 through 53. The first item is the property name; for example, authtype, user, and password. You will create a variable that holds the value of each connection property; the name of the variable is the same as the name of the property. The second item is a descriptive name such as Database-User or Database-Name. The descriptive name displays as a prompt. The third item in the sublist is a property type. There are three possible values for the property type: an empty string, the character "D", and the character "*". If the property type is set to D, the property is meant for debugging purposes and should not normally be displayed to a casual user. If the property type is set to *, the property holds secret information (such as a password) and should not be echoed to the screen. If the property type is an empty string, it needs no special handling. You will ignore debug properties and arrange for any password fields to be displayed as * characters. The fourth sublist item returned by pg_conndefaults is the suggested length of the property valueyou will ignore this item for now. The final item in each sublist is the default value for the property. The default value reflects the environment variable associated with the property, or it reflects the hard-wired value if the environment variable has not been defined.

After picking apart the property sublist, you start processing it at line 55. The if statement at line 55 ensures that you ignore debug properties. I mentioned earlier that you will create a new variable for each connection propertythat happens at line 57. For example, if [lindex $prop 0] evaluates to password, you will create a new global variable named password. At line 57, you assign the default value (if any) to the new variable.

Next, add a label widget and an entry widget for each value that you want. The add_label_field procedure expects three parameters: a parent widget (.dlg), the text to display, and a variable that holds the value entered by the user.

When you call add_label_field (which you will examine next), two widgets are created. The first, a label widget, displays the text that was provided. The second, an entry widget, holds a value entered by the user. add_label_field returns the name of the new entry widgetyou'll need that name to customize the widget.

At lines 63 and 64, you configure any "secret" properties (that is, passwords) to show asterisks rather than the actual characters entered by the user.

Next, at lines 67 through 70, you force the focus to the first entry widget in the dialog. When a widget has focus, keyboard and mouse events are sent to that widget and that widget holds the text cursor. You force the focus to the first modifiable widget on the dialog so that it lands in a useful, predictable place.

At lines 75 and 76, you create the two buttons that appear at the bottom of your dialog. When the user clicks on the first button (labeled Connect), Tcl will execute the command {set result Ok}. If the user clicks on the second button (labeled Cancel), Tcl will execute the command {exit}, terminating the entire application.

If the user presses the Return key, the default widget will be activated. You want the Return key to trigger the Connect button, so make that the default widget (see line 78).

At this point, you have created all the widgets that you want to display to the user. You have a toplevel widget that contains a collection of labels and text entry widgets, and you have a pair of buttons so the user can make something happen. Now, you want to display the complete dialog to the user and wait for him to click the Connect button or the Cancel button. That's what the vwait procedure does (line 80). The argument for vwait is the name of a variable; in this case, result. The vwait procedure waits for the result variable to change. result changes when the user clicks the Connect button because the code executed by the Connect button is {set result 1}.

Remember, if the user clicks the Cancel button, the exit procedure is invoked, terminating the entire application.

After the user has clicked the Connect button, you construct a connection string from the values the user had entered. To do this, loop through each non-debug property and extract the property name. You use the property name to reconstruct the name of the variable that holds the property value (line 90). After you know the variable name, you can extract the value (line 91). If the property value is non-null, you construct a property=value pair and append it to the result string.

Finally, destroy the toplevel window (.dlg) and return the connection string to the caller.

This procedure (connect_dialog) gives you a self-adjusting procedure that prompts the user for connection properties, even if you run a newer (or older) version of PostgreSQL that supports a different set of properties.

Now, let's look at the helper functions: add_label_field and add_button. The add_label_field procedure is shown in Listing 16.3.

Listing 16.3. client1.tcl-add_label_field

104 proc add_label_field { w text textvar } { 105 106 global next_row 107 108 set next_row [expr $next_row + 1] 109 set label_path "$w.label_$textvar" 110 set entry_path "$w.$textvar" 111 112 label $label_path -text $text 113 grid $label_path -row $next_row -column 1 -sticky e 114 115 entry $entry_path -textvariable $textvar 116 grid $entry_path -row $next_row -column 2 -sticky w 117 118 bind $entry_path "$w.default invoke" 119 120 return $entry_path 121 }

This procedure creates two new widgets: a label widget and a text entry widget. The caller provides three arguments: a parent widget (w), the text to appear in the label widget (text), and the name of a variable that will hold the value that the user types into the entry widget (textvar).

We use the next_row global variable to determine where the label and enTRy widgets will be located. If you refer to line 35 of the previous listing (Listing 16.2), you'll see that next_row to zero was initialized before building the dialog.

Lines 109 and 110 construct the name that you will use for the label widget and for the enTRy widget. The widget names are constructed from the name of the text variable provided by the caller.

At line 112, you create the label widget and place the given text on the label. At line 113, you position the label widget using Tcl's grid layout manager. Always position the label widget in the first (leftmost) column and entry widget in the second (rightmost) column.

The -sticky option is used to position a widget within the grid cell. Specifying -sticky e means that the east (right) side of the widget sticks to the edge of the grid cell. The widget is right-justified within the cell.

At lines 115 and 116, you create the entry widget and position it within the grid.

Line 118 creates a binding for the Return key. If the user clicks the Return key while the entry widget is in focus, you want to trigger (or invoke) the $w.default button (that is, the Connect button). To accomplish this, bind the Return key to the code fragment $w.default invoke.

Finally, return the name of the enTRy widget to the caller.

The final procedure in client1.tcl is add_button (shown in Listing 16.4).

Listing 16.4. client1.tcl-add_button

123 proc add_button { path text command column } { 124 125 global next_row 126 127 if { $column == 1 } { 128 set next_row [expr $next_row + 1] 129 set sticky "w" 130 } else { set sticky "e" } 131 132 button $path -text $text -command $command 133 grid $path -row $next_row -column $column -sticky $sticky 134 135 bind $path "$path invoke" 136 }

The caller provides four parameters: the name of the widget (path), the text to display on the button (text), a command to execute when the button is pressed (command), and a column number (column). The column number, along with the next_row global variable, determines which grid cell will hold the new button.

Line 132 creates and configures the button widget, and line 133 positions the button within the grid layout manager. Finally, you bind the command $path invoke to the Return key. It's a little odd, but Tk doesn't do that automaticallypressing the Return key doesn't trigger a button widget unless you explicitly configure the button to do so.

Listing 16.5 shows the mainline code for client1.tcl. When the Tcl interpreter runs this script, it begins execution at line 140 (the first command outside of a procedure body). The mainline code is simple; you invoke the procedure main (refer to Listing 16.1) and exit when that procedure completes.

Listing 16.5. client1.tcl-mainline

138 # Mainline code follows 139 # 140 main 141 exit

Making the Connection Dialog Reusable

The connect_dialog procedure that you just finished turns out to be rather handy. Let's rearrange the code a little to make this procedure more reusable.

The easiest way to share code among Tcl applications is to factor the desired procedures into a separate source file and source that file into your applications. When you source a file, you are copying the contents of that file into your application at runtime. If you are familiar with C or C++, source is identical to #include.

We'll create a new file named pgconnect.tcl that contains only the code that you want to share among various applications. Listing 16.6 shows the outline of pgconnect.tcl.

Listing 16.6. pgconnect.tcl-outline

# Filename: pgconnect.tcl proc connect_dialog { } { ... } proc add_label_field { w text textvar } { ... } proc add_button { path text command column } { ... } proc connect { } { ... }

You can see that the connect_dialog, add_label_field, and add_button procedures are copied into pgconnect.tcl. I've also removed the mainline code and the main procedurethat code will be provided by the calling application. I've added one new procedure: connect. The body of the connect function is shown in Listing 16.7.

Listing 16.7. pgconnect.tcl-connect

1 proc connect { } { 2 3 package require Pgtcl 4 5 set result "retry" 6 7 while { $result == "retry" } { 8 set connstr [connect_dialog] 9 10 if { [catch {pg_connect -conninfo $connstr} conn] } { 11 set result [tk_messageBox 12 -message $conn 13 -title "Connection failed" 14 -type retrycancel] 15 } else { 16 return $conn 17 } 18 } 19 return {} 20 }

The connect procedure is similar to the main procedure from client1.tcl. After loading the libpgtcl library, connect enters a loop that calls the connect_dialog procedure until a connection is made or the user cancels. If a connection is made, connect will return the connection handle to the caller; otherwise, it will return an empty string.

Now that you've factored the connection dialog logic into a separate source file, you can use these procedures in multiple applications. Listing 16.8 shows a new version of the client1.tcl application, rewritten to take advantage of pgconnect.tcl.

Listing 16.8. client1a.tcl

1 #!/usr/local/bin/wish 2 # 3 # Filename: client1a.tcl 4 5 proc main { } { 6 7 wm withdraw . 8 9 set conn [connect] 10 11 if { $conn != {} } { 12 tk_messageBox 13 -message "Connection is: $conn" 14 -title "Connection Ok" 15 } 16 17 pg_disconnect $conn 18 19 } 20 21 # Mainline code follows 22 # 23 24 source pgconnect.tcl 25 26 main 27 exit

This new application is much shorter than the original version. I'll point out two changes that I've made to this code. First, at line 24, I replaced the connect_dialog, add_label_field, and add_button procedures with source pgconnect.tcl. Because I haven't included a pathname in the source command, Tcl looks for pgconnect.tcl in the current directory. The other change that I've made is at line 17you call pg_disconnect to free up the connection handle when you are finished with it. You should call pg_disconnect to gracefully close a connection handle when you no longer need the connection. Closing the connection handle is not strictly required, but it is good form to free up resources as soon as you are done with them.

Now that you know how to connect to a PostgreSQL database from Tcl (and how to disconnect when you're finished), let's look at the steps required to execute a SQL command and process the results.