Client 4 - An Interactive Query Processor

The final client application for this chapter will be a general-purpose interactive command processor. Perl makes it easy for you to create a feature-rich application with a minimum of code: You don't need a lot of scaffolding just to use the basic DBI features. Accordingly, I'll use this application as a way to explain some of the remaining DBI features that haven't really fit in anywhere else. (see Listing 14.19) accepts two kinds of commands from the user. Commands that start with a colon are meta-commands and are processed by the application. Commands that don't begin with a colon are PostgreSQL commands and are sent to the server.

Listing 14.19

 1 #!/usr/bin/perl -W

 2 #

 3 #  Filename:

 4 #


 6 use DBI;

 7 use Term::ReadLine;


 9 my $dbh = DBI->connect("dbi:Pg:", undef, undef, {PrintError => 1})

10   or die "Can't connect to PostgreSQL: $DBI::errstr ($DBI::err)\n";


12 my $term = new Term::ReadLine( 'client4' );


14 print( "\nEnter SQL commands or :help for assistance\n\n" );


16 while( my $command = $term->readline( "--> " )) {

17     if( $command =~ /^:(\w+)\s*(.*)/ ) {

18         eval {

19             my $subr_name = "do_$1";

20             my @args      = split '\s', $2||'';


22             &$subr_name( $dbh, @args );

23         }

24     }

25     else {

26         do_sql_command( $dbh, $command );

27     }

28 }


30 do_quit( $dbh );

The mainline code for this client is a little different from the earlier clients in this chapter. Because this client is interactive, you will need to accept queries and other commands from the user. The Term::ReadLine module (which you use at line 7) offers the Perl equivalent of the GNU ReadLine and History libraries.

The main loop in this application (lines 16 through 28) prompts the user for a command, executes the command, and displays the results (if any).

When you call the $term->readline() method (at line 16), the user is presented with the prompt (-->) and can compose a command string using the editing and history features offered by the Term::ReadLine module. $term->readline() returns the fully composed command string.

This client application handles two different command types. If a command starts with a colon character (:), it is treated as a meta-command and is handled by subroutines that I'll explain in a moment. If a command does not start with a colon, assume that it is a PostgreSQL command, and call the do_sql_command() method to execute the command and display the results.

We will support the following meta-commands:

  • :help

  • :autocommit [0|1]

  • :commit

  • :rollback

  • :trace [0|1|2|3|4] [tracefile]

  • :show_tables

  • :show_table table-name

  • :show_types

Meta-commands are detected and dispatched starting at line 17. If you're not used to reading Perl regular expression strings, the if command at line 17 can look pretty daunting. The =~ operator determines whether the string on the left side ($command) matches the regular-expression on the right side. I'll interpret the regular-expression for you: You want to match a pattern that starts at the beginning of the string (^) and is immediately followed by a colon (:). Next, you expect to see one or more word characters (\w+). A word character is an alphanumeric character or an underscore. I'll explain the extra parenthesis in a moment. Following the leading word, you expect zero or more white space characters (that is, tabs or spaces). Anything else on the command line is gobbled up by the last subpattern (.*).

Two of these subpatterns (\w+ and .*) are enclosed in parentheses. Enclosing a subpattern like this tells Perl that you want it to remember the characters that match that subpattern in a special variable that you can use later. We have two enclosed subpatterns: the characters that match the first subpattern will be remembered in variable $1 and the characters that match the second subpattern will be remembered in $2.

The effect here is that you detect meta-commands by looking for strings that start with a colon immediately followed by a word[7]. If you find one, the first word (the meta-command itself) will show up in $1, and any arguments will show up in $2. That regular-expression operator is pretty powerful, huh?

[7] You could, of course, change the regular-expression to look for a string that starts with a colon, followed by optional white space, followed by a word.

After you have parsed out the meta-command and the optional arguments, use a little more Perl magic to call the subroutine that handles the given command. If the user enters the meta-command :help, you want to call the subroutine do_help(). If the user enters the meta-command :commit, you want to call the subroutine do_commit(). You probably see a pattern developing here; to find the subroutine that handles a given meta-command, you simply glue the characters do_ to the front of the command name. That's what line 19 is doing. At line 19, you are splitting any optional arguments (which are all stored in $2) into an array.

Now to call the appropriate command handler, you call the subroutine, by name, at line 22. Don't let the funky looking expression at line 22 confuse you. This is just a plain-old subroutine call, but Perl determines which subroutine to call by evaluating the contents of the $subr_name variable. Note that you can't defer the name resolution until runtime like this if you are in strict mode?I have omitted the use strict directive from this script. Another approach that you can take is to use strict in most of your code, but specify no strict in the cases that would otherwise cause an error.

I have wrapped the subroutine invocation in an eval{} block. This is roughly equivalent to a try{}/catch{} block in Java?it catches any errors thrown by the code inside of the block. If the user enters an invalid meta-command (that is, a command that starts with a colon but doesn't match any of the do_xxx() subroutines), the eval{} block will silently catch the exception rather than aborting the entire application.

All your command handler subroutines expect to receive a database handle as the first parameter, and then an array of optional parameters.

If the command entered by the user does not match your meta-command regular expression, you assume that the command should be sent to the PostgreSQL server and call the do_sql_command() subroutine (see Listing 14.20).

Listing 14.20

32 sub do_sql_command

33 {

34   my $dbh     = shift;

35   my $command = shift;


37   my $sth = $dbh->prepare( $command );


39   if( defined( $sth )) {

40       if( $sth->execute()) {

41           process_results( $dbh, $sth );

42       }

43   }

44 }

The do_sql_command() subroutine is called whenever the user enters a PostgreSQL command. We expect two arguments in this subroutine: a database handle and the text of the command. There are no surprises in this subroutine: you simply prepare the command, execute it, and call process_results() to finish up.

46 sub do_ping

47 {

48   my( $dbh, @args ) = @_;


50   print( $dbh->ping() ? "Ok\n" : "Not On" );

51 }

This subroutine, do_ping(), is called whenever the user enters the command :ping. The $dbh->ping() subroutine is designed to test the validity of a database handle. The DBD::Pg implementation of this method executes an empty query to ensure that the database connection is still active.

Listing 14.21

53 sub do_autocommit

54 {

55   my( $dbh, @args ) = @_;


57   $dbh->{AutoCommit} = $args[0];


59 }

The do_autocommit() subroutine shown in Listing 14.21 is used to enable or disable AutoCommit mode. By default, every command executed through DBI is committed as soon as it completes. If you want to control transaction boundaries yourself, you must disable AutoCommit mode. To disable AutoCommit, execute the command :autocommit 0. To enable AutoCommit, use :autocommit 1. The $dbh->{AutoCommit} attribute keeps track of the commit mode for a database handle.

Listing 14.22 shows the do_commit() and do_rollback() subroutines.

Listing 14.22, do_rollback

61 sub do_commit

62 {

63   my( $dbh, @args ) = @_;


65   $dbh->commit();

66 }


68 sub do_rollback

69 {

70     my( $dbh, @args ) = @_;


72     $dbh->rollback();

73 }

After you have disabled AutoCommit mode, you can commit and roll back transactions using :commit and :rollback. If you try to :commit or :rollback while AutoCommit is enabled, you will be rewarded with an error message (commit ineffective with AutoCommit enabled.).

Next, you have the do_quit() subroutine (see Listing 14.23).

Listing 14.23

75 sub do_quit

76 {

77     my( $dbh, @args ) = @_;


79     if( defined( $dbh )) {

80         $dbh->disconnect();

81     }


83     exit( 0 );

84 }

The do_quit() subroutine is simple?if the database handle is defined (that is, is not undef), disconnect it. The call to exit() causes this application to end.

In Listing 14.24, you see the do_trace() subroutine.

Listing 14.24

86 sub do_trace

87 {

88     my( $dbh, @args ) = @_;


90     $dbh->trace( @args );


92 }

This subroutine gives you a way to adjust the DBI tracing mechanism. The $dbh_trace() method expects either one or two arguments: a trace level (0 through 4) and an optional filename. Every DBI application starts at trace level 0, meaning that no trace output is generated. If you don't supply a trace filename, trace output is sent to STDOUT (your terminal).

If you want a little information about what's going on under the hood, set the trace level to 1. Here's an example of what you'll see:

--> :trace 1 DBI::db=HASH(0x8208020) trace level set to 1 in DBI 1.30-nothread --> SELECT * FROM customers LIMIT 1; dbd_st_prepare: statement = >SELECT * FROM customers LIMIT 1;< dbd_st_preparse: statement = >SELECT * FROM customers LIMIT 1;< <- prepare('SELECT * FROM customers LIMIT 1;')= DBI::st=HASH(0x82081a0) at graphics/ccc.gifline 37 dbd_st_execute <- execute= 1 at line 39 ...

Okay, you actually get a lot of information at trace level 1, but not as much as you do for higher trace levels. Tracing is useful for debugging and for understanding how DBI and the PostgreSQL driver are carrying out your requests.

Listing 14.25 shows the do_help subroutine.

Listing 14.25

 94 sub do_help

 95 {

 96     print( "Commands\n" );

 97     print( "  :help\t\t\t\tShow help text\n" );

 98     print( "  :autocommit [0|1]\t\tSet AutoCommit\n" );

 99     print( "  :commit\t\t\tCOMMIT TRANSACTION\n" );

100     print( "  :rollback\t\t\tROLLBACK TRANSACTION\n" );

101     print( "  :trace [0|1|2|3|4] [tracefile]\tSet Trace level\n" );

102     print( "  :show_tables\t\t\tShow all table names\n" );

103     print( "  :show_table table_name\tDescribe table\n" );

104     print( "  :show_types\t\t\tList Data Types\n" );

105 }

do_help() is called whenever the user enters the command :help.

This subroutine (do_show_tables(), Listing 14.26) shows how to call the $dbh->table_info() method.

Listing 14.26

107 sub do_show_tables

108 {

109   my( $dbh, @args ) = @_;


111   process_results( $dbh, $dbh->table_info());


113 }

$dbh->table_info() returns a result set containing a list of tables accessible through the database handle. Here is an example:

--> :show_tables


--------- ----------- ---------- ---------- -------

          bruce       customers  TABLE

          bruce       rentals    TABLE

          bruce       returns    TABLE

          bruce       tapes      TABLE

The author of each DBD driver can interpret the $dbh->table_info() request in a different way. The DBD::Pg driver returns all table and view definitions owned by the current user; other drivers may give different results. In some cases, you may find it easier to call the $dbh->tables() method, which returns an array of table names rather than a result set.

The do_show_types() subroutine, shown in Listing 14.27, displays a list of server data types.

Listing 14.27

115 sub do_show_types

116 {

117   my( $dbh, @args ) = @_;


119   print("Type            Type         SQL  Col.  Prefix  \n");

120   print("Name            Parameters   Type Size    Suffix\n");

121   print("--------------- ------------ ---- ----- - ------\n" );


123   foreach my $type ( $dbh->type_info( undef )) {

124       printf( "%-15s %-12s %-3d  %-5d %s %s\n",

125               $type->{TYPE_NAME},

126               $type->{CREATE_PARAMS} || "",

127               $type->{DATA_TYPE},

128               $type->{COLUMN_SIZE},

129               $type->{LITERAL_PREFIX} || " ",

130               $type->{LITERAL_SUFFIX} || " " );

131   }

132 }

At line 123, you call the $dbh->type_info() method: This method returns an array of hash references. Each hash corresponds to a single data type and contains a number of key/value pairs. You print the {TYPE_NAME}, {CREATE_PARAMS}, {DATA_TYPE}, and {COLUMN_SIZE} attributes as well as the prefix and suffix characters. Here is an example:

--> :show_types

Type            Type         SQL  Col.  Prefix

Name            Parameters   Type Size    Suffix

--------------- ------------ ---- ----- - ------

bytea                        -2   4096  ' '

bool                         0    1     ' '

int8                         8    20

int2                         5    5

int4                         4    10

text                         12   4096  ' '

float4          precision    6    12

float8          precision    7    24

abstime                      10   20    ' '

reltime                      10   20    ' '

tinterval                    11   47    ' '

money                        0    24

bpchar          max length   1    4096  ' '

bpchar          max length   12   4096  ' '

varchar         max length   12   4096  ' '

date                         9    10    ' '

time                         10   16    ' '

datetime                     11   47    ' '

timespan                     11   47    ' '

timestamp                    10   19    ' '

You may notice that this list is not a complete list of PostgreSQL data types. It is also not entirely accurate. For example, you know that a VARCHAR column has no maximum length, but it is reported to have a length of 4096 bytes.

The $dbh->type_info() method is implemented by the DBD::Pg driver, not by the DBI package, so the DBD::Pg author chose the data types that he used most often. My recommendation would be to ignore the information returned by this method, at least when you are connected to a PostgreSQL database. You may find this method more useful if you are exploring other database systems.

Listing 14.28 shows the do_show_table() subroutine.

Listing 14.28

134 sub do_show_table

135 {

136   my( $dbh, @args ) = @_;


138   my $sth = $dbh->prepare( "SELECT * FROM $args[0] WHERE 1 <> 1" );


140   if( defined( $sth )) {

141       if( $sth->execute()) {

142           print_meta_data( $dbh, $sth );

143           $sth->finish();

144       }

145   }

146 }

I wanted to include a subroutine that would display the layout of a named table, similar to the \d meta-command in psql. The DBI package does not provide a method that exposes this information, but you can certainly trick it into providing enough metadata that you can build such a method yourself.

The do_show_table() method is called whenever the user enters a command such as :show_table customers. The trick is to construct a query that returns all columns, but is guaranteed to return 0 rows. At line 138, you create and execute a query of the following form:

SELECT * FROM table-name WHERE 1 <> 1;

The WHERE clause in this command can never evaluate to True so it will never return any rows. When you execute this query, you get a result set, even though no rows are returned. You can examine the metadata from this result set to determine the layout of the table. After you have displayed the metadata, call $sth->finish() to tell DBI that you are finished with this result set.

The print_meta_data subroutine is shown in Listing 14.29.

Listing 14.29

148 sub print_meta_data

149 {

150   my $dbh = shift;

151   my $sth = shift;


153   my $field_count = $sth->{NUM_OF_FIELDS};

154   my $names       = $sth->{NAME};

155   my $pg_types    = $sth->{pg_type};


157   print( "Name                          | Type   \n" );

158   print( "------------------------------+--------\n" );


160   for( my $col = 0; $col < $field_count; $col++ ) {

161     printf( "%-30s| %-8s\n", $names->[$col], $pg_types->[$col] );

162   }

163 }

This subroutine prints the metadata associated with a result set. Call print_meta_data() from do_show_table().

This subroutine shows how to obtain the number of fields in a result set ($sth->{NUM_OF_FIELDS}), the name of each column ($sth->{NAME}), and the PostgreSQL data type name for each column ($sth->{pg_type}).

As I mentioned earlier, the DBD::Pg driver adds three PostgreSQL-specific attributes to a statement handle: {pg_type}, {pg_oid_status}, and {pg_ctl_status}.

Here is a sample showing print_meta_data() in action:

--> :show_table customers

Name                          | Type


id                            | int4

customer_name                 | varchar

phone                         | bpchar

birth_date                    | date

The process_results() subroutine (see Listing 14.30) prints the result of a PostgreSQL command.

Listing 14.30

165 sub process_results

166 {

167   my $dbh = shift;

168   my $sth = shift;


170   if( defined( $sth )) {

171       if( $sth->{NUM_OF_FIELDS} == 0 ) {

172         print( $sth->{pg_cmd_status} . "\n" );

173       }

174       else {

175         my($widths, $row_values) = compute_column_widths( $sth );

176         print_column_headings( $sth, $widths );

177         print_results( $sth, $row_values, $widths );

178     }

179   }

180 }

You've already seen most of this code in earlier clients. Start by deciding whether you are processing a SELECT command or some other type of command. If the number of fields in the result set is 0 (that is, this is a non-SELECT command), you simply print the $sth->{pg_cmd_status} attribute. If you decide that you are processing a SELECT command, you compute the column widths, print the column headings, and then print the entire result set.

The compute_column_widths(), print_column_headings(), and print_results() subroutines are identical to those used in earlier in this chapter, so I won't describe them here.

Let's run this client and exercise it a bit:

$ chmod a+x


Enter SQL commands or :help for assistance

--> :help


  :help                         Show help text

  :autocommit [0|1]             Set AutoCommit

  :commit                       COMMIT TRANSACTION

  :rollback                     ROLLBACK TRANSACTION

  :trace [0|1|2|3|4] [filename] Set Trace level

  :show_tables                  Show all table names

  :show_table table_name        Describe table

  :show_types                   List Data Types

So far, so good. This help text was generated by the do_help() subroutine. Now, let's see a list of the tables in this database:

--> :show_tables


--------- ----------- ---------- ---------- -------

          bruce       customers  TABLE

          bruce       rentals    TABLE

          bruce       returns    TABLE

          bruce       tapes      TABLE

Next, I'll turn off AutoCommit mode, create a new table, and show the layout of the new table:

--> :autocommit 0

--> CREATE TABLE foobar( pkey INTEGER, data VARCHAR );


--> :show_table foobar

Name                          | Type


pkey                          | int4

data                          | varchar

Now, let's roll back this transaction and try to view the table layout again:

--> :rollback --> :show_table foobar DBD::Pg::st execute failed: ERROR: Relation "foobar" does not exist at ./ line graphics/ccc.gif141.

The :rollback meta-command apparently worked (we don't see any error messages), but the :show_table meta-command has failed. We expect this :show_table command to fail because we have rolled back the CREATE TABLE command.

You may have noticed that I haven't included any error-handling code in this application. When you make the initial connection to the database (way back at line 9 of this script), you set the {PrintError} attribute to 1 so DBI and the DBD::Pg driver print any error messages that you may encounter.

    Part II: Programming with PostgreSQL