3 @brief Test driver for routines to build queries from tables in the query schema.
5 This command-line utility exercises most of the code used in the qstore server, but
6 without the complications of sending and receiving OSRF messages.
10 test_qstore [options] query_id
12 Query_id is the id of a row in the query.stored_query table, defining a stored query.
14 The program reads the specified row in query.stored_query, along with associated rows
15 in other tables, and displays the corresponding query as an SQL command. Optionally it
16 may execute the query, display the column names of the query result, and/or display the
19 In order to connect to the database, test_qstore uses various connection parameters
20 that may be specified on the command line. Any connection parameter not specified
21 reverts to a plausible default.
23 The database password may be read from a specified file or entered from the keyboard.
27 -b Boolean; Display the name of any bind variables, and their default values.
29 -D Specifies the name of the database driver; defaults to "pgsql".
31 -c Boolean; display column names of the query results, as assigned by PostgreSQL.
33 -d Specifies the database name; defaults to "evergreen".
35 -h Specifies the hostname of the database; defaults to "localhost".
37 -i Specifies the name of the IDL file; defaults to "/openils/conf/fm_IDL.xml".
39 -p Specifies the port number of the database; defaults to 5432.
41 -u Specifies the database user name; defaults to "evergreen".
43 -v Boolean; Run in verbose mode, spewing various detailed messages. This option is not
44 likely to be useful unless you are troubleshooting the code that loads the stored
47 -w Specifies the name of a file containing the database password (no default).
49 -x Boolean: Execute the query and display the results.
51 Copyright (C) 2010 Equinox Software Inc.
52 Scott McKellar <scott@esilibrary.com>
62 #include "opensrf/utils.h"
63 #include "opensrf/string_array.h"
64 #include "opensrf/osrf_json.h"
65 #include "openils/oils_idl.h"
66 #include "openils/oils_buildq.h"
86 int password_file_found;
92 static void show_bind_variables( osrfHash* vars );
93 static void show_msgs( const osrfStringArray* sa );
94 static dbi_conn connect_db( Opts* opts );
95 static int load_pw( growing_buffer* buf, FILE* in );
96 static int prompt_password( growing_buffer* buf );
97 static void initialize_opts( Opts * pOpts );
98 static int get_Opts( int argc, char * argv[], Opts * pOpts );
100 int main( int argc, char* argv[] ) {
102 // Parse the command line
105 if( get_Opts( argc, argv, &opts )) {
106 fprintf( stderr, "Unable to parse command line\n" );
110 // Connect to the database
111 dbi_conn dbhandle = connect_db( &opts );
112 if( NULL == dbhandle )
116 oilsStoredQSetVerbose();
118 osrfLogSetLevel( OSRF_LOG_WARNING );
121 if ( !oilsIDLInit( opts.idl )) {
122 fprintf( stderr, "Unable to load IDL at %s\n", opts.idl );
126 // Load the stored query
127 BuildSQLState* state = buildSQLStateNew( dbhandle );
128 state->defaults_usable = 1;
129 state->values_required = 0;
130 StoredQ* sq = getStoredQuery( state, atoi( opts.new_argv[ 1 ] ));
133 show_msgs( state->error_msgs );
134 printf( "Unable to build query\n" );
136 // If so requested, show the bind variables
138 show_bind_variables( state->bindvar_list );
140 // Build the SQL query
141 if( buildSQL( state, sq )) {
142 show_msgs( state->error_msgs );
143 fprintf( stderr, "Unable to build SQL statement\n" );
146 printf( "%s\n", OSRF_BUFFER_C_STR( state->sql ));
148 // If so requested, get the column names and display them
150 jsonObject* cols = oilsGetColNames( state, sq );
152 printf( "Column names:\n" );
153 char* cols_str = jsonObjectToJSON( cols );
154 char* cols_out = jsonFormatString( cols_str );
155 printf( "%s\n\n", cols_out );
158 jsonObjectFree( cols );
160 fprintf( stderr, "Unable to get column names\n\n" );
163 // If so requested, execute the query and display the results
165 jsonObject* row = oilsFirstRow( state );
167 show_msgs( state->error_msgs );
168 fprintf( stderr, "Unable to execute query\n" );
171 int first = 1; // boolean
180 char* json = jsonObjectToJSON( row );
181 printf( "%s", json );
183 row = oilsNextRow( state );
186 show_msgs( state->error_msgs );
187 fprintf( stderr, "Unable to fetch row\n" );
196 buildSQLStateFree( state );
200 dbi_conn_close( dbhandle );
206 @brief Display the bind variables.
207 @param vars Pointer to a hash keyed on bind variable name.
209 The data for each hash entry is a BindVar, a C struct whose members define the
210 attributes of the bind variable.
212 static void show_bind_variables( osrfHash* vars ) {
213 printf( "Bind variables:\n\n" );
214 BindVar* bind = NULL;
215 osrfHashIterator* iter = osrfNewHashIterator( vars );
217 // Traverse the hash of bind variables
218 while(( bind = osrfHashIteratorNext( iter ))) {
219 const char* type = NULL;
220 switch( bind->type ) {
228 type = "string list";
231 type = "number list";
234 type = "(unrecognized)";
238 // The default and actual values are in the form of jsonObjects.
239 // Transform them back into raw JSON.
240 char* default_value = NULL;
241 if( bind->default_value )
242 default_value = jsonObjectToJSONRaw( bind->default_value );
244 char* actual_value = NULL;
245 if( bind->actual_value )
246 actual_value = jsonObjectToJSONRaw( bind->actual_value );
248 // Display the attributes of the current bind variable.
249 printf( "Name: %s\n", bind->name );
250 printf( "Label: %s\n", bind->label );
251 printf( "Type: %s\n", type );
252 printf( "Desc: %s\n", bind->description ? bind->description : "(none)" );
253 printf( "Default: %s\n", default_value ? default_value : "(none)" );
254 printf( "Actual: %s\n", actual_value ? actual_value : "(none)" );
258 free( default_value );
261 free( actual_value );
264 osrfHashIteratorFree( iter );
268 @brief Write a series of strings to standard output.
269 @param sa Array of strings.
271 Display messages emitted by the query-building machinery.
273 static void show_msgs( const osrfStringArray* sa ) {
276 for( i = 0; i < sa->size; ++i ) {
277 const char* s = osrfStringArrayGetString( sa, i );
285 @brief Connect to the database.
286 @return If successful, a database handle; otherwise NULL;
288 static dbi_conn connect_db( Opts* opts ) {
289 // Get a database handle
290 dbi_initialize( NULL );
291 dbi_conn dbhandle = dbi_conn_new( opts->driver );
293 fprintf( stderr, "Error loading database driver [%s]", opts->driver );
298 growing_buffer* buf = buffer_init( 32 );
300 // Get the database password, either from a designated file
301 // or from the terminal.
302 if( opts->password_file_found ) {
303 FILE* pwfile = fopen( opts->password_file, "r" );
305 fprintf( stderr, "Unable to open password file %s\n", opts->password_file );
309 if( load_pw( buf, pwfile )) {
310 fprintf( stderr, "Unable to load password file %s\n", opts->password_file );
314 pw = buffer_release( buf );
317 if( prompt_password( buf )) {
318 fprintf( stderr, "Unable to get password\n" );
322 pw = buffer_release( buf );
325 // Set database connection options
326 dbi_conn_set_option( dbhandle, "host", opts->host );
327 dbi_conn_set_option_numeric( dbhandle, "port", opts->port );
328 dbi_conn_set_option( dbhandle, "username", opts->user );
329 dbi_conn_set_option( dbhandle, "password", pw );
330 dbi_conn_set_option( dbhandle, "dbname", opts->database );
332 // Connect to the database
334 if( dbi_conn_connect( dbhandle) < 0 ) {
336 if ( dbi_conn_connect( dbhandle ) < 0 ) {
337 dbi_conn_error( dbhandle, &err );
338 fprintf( stderr, "Error connecting to database: %s", err );
339 dbi_conn_close( dbhandle );
350 @brief Load one line from an input stream into a growing_buffer.
351 @param buf Pointer to the receiving buffer.
352 @param in Pointer to the input stream.
353 @return 0 in all cases. If there's ever a way to fail, return 1 for failure.
355 Intended for use in loading a password.
357 static int load_pw( growing_buffer* buf, FILE* in ) {
361 if( '\n' == c || EOF == c )
366 OSRF_BUFFER_ADD_CHAR( buf, c );
372 @brief Read a password from the terminal, with echo turned off.
373 @param buf Pointer to the receiving buffer.
374 @return 0 if successful, or 1 if not.
376 Read from /dev/tty if possible, or from stdin if not.
378 static int prompt_password( growing_buffer* buf ) {
379 struct termios oldterm;
381 printf( "Password: " );
384 FILE* term = fopen( "//dev//tty", "rw" );
388 // Capture the current state of the terminal
389 if( tcgetattr( fileno( term ), &oldterm ))
393 struct termios newterm = oldterm;
394 newterm.c_lflag &= ~ECHO;
395 if( tcsetattr( fileno( term ), TCSAFLUSH, &newterm ))
399 int rc = load_pw( buf, term );
402 (void) tcsetattr( fileno( term ), TCSAFLUSH, &oldterm ); // restore echo
411 @brief Initialize an Opts structure.
412 @param pOpts Pointer to the Opts to be initialized.
414 static void initialize_opts( Opts * pOpts ) {
416 pOpts->new_argv = NULL;
419 pOpts->driver_found = 0;
420 pOpts->driver = NULL;
421 pOpts->database_found = 0;
422 pOpts->database = NULL;
423 pOpts->host_found = 0;
425 pOpts->idl_found = 0;
427 pOpts->port_found = 0;
429 pOpts->user_found = 0;
431 pOpts->password_file_found = 0;
432 pOpts->password_file = NULL;
439 @brief Parse the command line.
440 @param argc argc from the command line.
441 @param argv argv from the command line.
442 @param pOpts Pointer to the Opts to be populated.
443 @return Zero if successful, or 1 if not.
445 static int get_Opts( int argc, char * argv[], Opts * pOpts ) {
446 int rc = 0; /* return code */
447 unsigned long port_value = 0;
451 /* Define valid option characters */
453 const char optstring[] = ":bD:cd:h:i:p:u:vw:x";
455 /* Initialize members of struct */
457 initialize_opts( pOpts );
459 /* Suppress error messages from getopt() */
463 /* Examine command line options */
465 while( ( opt = getopt( argc, argv, optstring )) != -1 )
469 case 'b' : /* Display bind variables */
472 case 'c' : /* Display column names */
475 case 'D' : /* Get database driver */
476 if( pOpts->driver_found )
478 fprintf( stderr, "Only one occurrence of -D option allowed\n" );
482 pOpts->driver_found = 1;
484 pOpts->driver = optarg;
486 case 'd' : /* Get database name */
487 if( pOpts->database_found )
489 fprintf( stderr, "Only one occurrence of -d option allowed\n" );
493 pOpts->database_found = 1;
495 pOpts->database = optarg;
497 case 'h' : /* Get hostname of database */
498 if( pOpts->host_found )
500 fprintf( stderr, "Only one occurrence of -h option allowed\n" );
504 pOpts->host_found = 1;
506 pOpts->host = optarg;
508 case 'i' : /* Get name of IDL file */
509 if( pOpts->idl_found )
511 fprintf( stderr, "Only one occurrence of -i option allowed\n" );
515 pOpts->idl_found = 1;
519 case 'p' : /* Get port number of database */
520 if( pOpts->port_found )
522 fprintf( stderr, "Only one occurrence of -p option allowed\n" );
526 pOpts->port_found = 1;
528 /* Skip white space; check for negative */
530 while( isspace( (unsigned char) *optarg ))
535 fprintf( stderr, "Negative argument not allowed for "
536 "-p option: \"%s\"\n", optarg );
541 /* Convert to numeric value */
544 port_value = strtoul( optarg, &tail, 10 );
547 fprintf( stderr, "Invalid or non-numeric argument "
548 "to -p option: \"%s\"\n", optarg );
552 else if( errno != 0 )
554 fprintf( stderr, "Too large argument "
555 "to -p option: \"%s\"\n", optarg );
560 pOpts->port = port_value;
562 case 'u' : /* Get username of database account */
563 if( pOpts->user_found )
565 fprintf( stderr, "Only one occurrence of -u option allowed\n" );
569 pOpts->user_found = 1;
571 pOpts->user = optarg;
573 case 'v' : /* Set verbose mode */
576 case 'w' : /* Get name of password_file */
577 if( pOpts->password_file_found )
579 fprintf( stderr, "Only one occurrence of -w option allowed\n" );
583 pOpts->password_file_found = 1;
585 pOpts->password_file = optarg;
587 case 'x' : /* Set execute */
590 case ':' : /* Missing argument */
591 fprintf( stderr, "Required argument missing on -%c option\n",
595 case '?' : /* Invalid option */
596 fprintf( stderr, "Invalid option '-%c' on command line\n",
600 default : /* Programmer error */
601 fprintf( stderr, "Internal error: unexpected value '-%c'"
602 "for optopt", (char) optopt );
608 /* See if required options were supplied; apply defaults */
610 if( ! pOpts->driver_found )
611 pOpts->driver = "pgsql";
613 if( ! pOpts->database_found )
614 pOpts->database = "evergreen";
616 if( ! pOpts->host_found )
617 pOpts->host = "localhost";
619 if( ! pOpts->idl_found )
620 pOpts->idl = "/openils/conf/fm_IDL.xml";
622 if( ! pOpts->port_found )
625 if( ! pOpts->user_found )
626 pOpts->user = "evergreen";
630 /* This should never happen! */
632 fprintf( stderr, "Program error: found more arguments than expected\n" );
637 /* Calculate new_argcv and new_argc to reflect */
638 /* the number of arguments consumed */
640 pOpts->new_argc = argc - optind + 1;
641 pOpts->new_argv = argv + optind - 1;
643 if( pOpts->new_argc < 2UL )
645 fprintf( stderr, "Not enough arguments beyond options; must be at least 1\n" );