Client 4 - An Interactive Query Processor
Client 4 ”An Interactive Query ProcessorThe 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. client4.pl (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 client4.pl ”mainline 1 #!/usr/bin/perl -W 2 # 3 # Filename: client4.pl 4 # 5 6 use DBI; 7 use Term::ReadLine; 8 9 my $dbh = DBI->connect("dbi:Pg:", undef, undef, {PrintError => 1}) 10 or die "Can't connect to PostgreSQL: $DBI::errstr ($DBI::err)\n"; 11 12 my $term = new Term::ReadLine( 'client4' ); 13 14 print( "\nEnter SQL commands or :help for assistance\n\n" ); 15 16 while( my $command = $term->readline( "--> " )) { 17 if( $command =~ /^:(\w+)\s*(.*)/ ) { 18 eval { 19 my $subr_name = "do_"; 20 my @args = split '\s', ''; 21 22 &$subr_name( $dbh, @args ); 23 } 24 } 25 else { 26 do_sql_command( $dbh, $command ); 27 } 28 } 29 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:
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?
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 client4.pl ”do_sql_command 32 sub do_sql_command 33 { 34 my $dbh = shift; 35 my $command = shift; 36 37 my $sth = $dbh->prepare( $command ); 38 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 ) = @_; 49 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 client4.pl ”do_autocommit 53 sub do_autocommit 54 { 55 my( $dbh, @args ) = @_; 56 57 $dbh->{AutoCommit} = $args[0]; 58 59 } The do_autocommit() subroutine shown in Listing 14.21 is used to enable or disable A utoCommit 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 client4.pl ”do_commit, do_rollback 61 sub do_commit 62 { 63 my( $dbh, @args ) = @_; 64 65 $dbh->commit(); 66 } 67 68 sub do_rollback 69 { 70 my( $dbh, @args ) = @_; 71 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 client4.pl ”do_quit 75 sub do_quit 76 { 77 my( $dbh, @args ) = @_; 78 79 if( defined( $dbh )) { 80 $dbh->disconnect(); 81 } 82 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 client4.pl ”do_trace 86 sub do_trace 87 { 88 my( $dbh, @args ) = @_; 89 90 $dbh->trace( @args ); 91 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 client4.pl 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 client4.pl ”do_help 94 sub do_help 95 { 96 print( "Commands\n" ); 97 print( " :help\t\t\t\tShow help text\n" ); 98 print( " :autocommit [01]\t\tSet AutoCommit\n" ); 99 print( " :commit\t\t\tCOMMIT TRANSACTION\n" ); 100 print( " :rollback\t\t\tROLLBACK TRANSACTION\n" ); 101 print( " :trace [01234] [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 client4.pl ”do_show_tables 107 sub do_show_tables 108 { 109 my( $dbh, @args ) = @_; 110 111 process_results( $dbh, $dbh->table_info()); 112 113 } $dbh->table_info() returns a result set containing a list of tables accessible through the database handle. Here is an example: --> :show_tables TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS --------- ----------- ---------- ---------- ------- 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 client4.pl ”do_show_types 115 sub do_show_types 116 { 117 my( $dbh, @args ) = @_; 118 119 print("Type Type SQL Col. Prefix \n"); 120 print("Name Parameters Type Size Suffix\n"); 121 print("--------------- ------------ ---- ----- - ------\n" ); 122 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 client4.pl ”do_show_table 134 sub do_show_table 135 { 136 my( $dbh, @args ) = @_; 137 138 my $sth = $dbh->prepare( "SELECT * FROM $args[0] WHERE 1 <> 1" ); 139 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 client4.pl ”print_meta_data 148 sub print_meta_data 149 { 150 my $dbh = shift; 151 my $sth = shift; 152 153 my $field_count = $sth->{NUM_OF_FIELDS}; 154 my $names = $sth->{NAME}; 155 my $pg_types = $sth->{pg_type}; 156 157 print( "Name Type \n" ); 158 print( "------------------------------+--------\n" ); 159 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 client4.pl ”process_results 165 sub process_results 166 { 167 my $dbh = shift; 168 my $sth = shift; 169 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 client3e.pl earlier in this chapter, so I won't describe them here. Let's run this client and exercise it a bit: $ chmod a+x client4.pl $./client4.pl Enter SQL commands or :help for assistance --> :help Commands :help Show help text :autocommit [01] Set AutoCommit :commit COMMIT TRANSACTION :rollback ROLLBACK TRANSACTION :trace [01234] [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 TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS --------- ----------- ---------- ---------- ------- 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 ); CREATE TABLE --> :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 ./client4.pl line 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. |
line 37 dbd_st_execute <- execute= 1 at client4.pl line 39 ... 
EAN: 2147483647
Pages: 220