Open-ILS/src/c-apps/test_qstore.c

   1 /**
   2         @file test_qstore.c
   3         @brief Test driver for routines to build queries from tables in the query schema.
   4
   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.
   7
   8         Synopsis:
   9
  10         test_qstore  [options]  query_id
  11
  12         Query_id is the id of a row in the query.stored_query table, defining a stored query.
  13
  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
  17         bind variables.
  18
  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.
  22
  23         The database password may be read from a specified file or entered from the keyboard.
  24
  25         Options:
  26
  27         -b  Boolean; Display the name of any bind variables, and their default values.
  28
  29         -D  Specifies the name of the database driver; defaults to "pgsql".
  30
  31         -c  Boolean; display column names of the query results, as assigned by PostgreSQL.
  32
  33         -d  Specifies the database name; defaults to "evergreen".
  34
  35         -h  Specifies the hostname of the database; defaults to "localhost".
  36
  37         -i  Specifies the name of the IDL file; defaults to "/openils/conf/fm_IDL.xml".
  38
  39         -p  Specifies the port number of the database; defaults to 5432.
  40
  41         -u  Specifies the database user name; defaults to "evergreen".
  42
  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
  45                 query.
  46
  47         -w  Specifies the name of a file containing the database password (no default).
  48
  49         -x  Boolean: Execute the query and display the results.
  50
  51         Copyright (C) 2010  Equinox Software Inc.
  52         Scott McKellar <scott@esilibrary.com>
  53 */
  54
  55 #include <stdlib.h>
  56 #include <stdio.h>
  57 #include <errno.h>
  58 #include <ctype.h>
  59 #include <unistd.h>
  60 #include <termios.h>
  61 #include <dbi/dbi.h>
  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"
  67
  68 typedef struct {
  69         int new_argc;
  70         char ** new_argv;
  71
  72         int   bind;
  73         char* driver;
  74         int   driver_found;
  75         char* database;
  76         int   database_found;
  77         char* host;
  78         int   host_found;
  79         char* idl;
  80         int   idl_found;
  81         unsigned long port;
  82         int   port_found;
  83         char* user;
  84         int   user_found;
  85         char* password_file;
  86         int   password_file_found;
  87         int   verbose;
  88         int   columns;
  89         int   execute;
  90 } Opts;
  91
  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 );
  99
 100 int main( int argc, char* argv[] ) {
 101
 102         // Parse the command line
 103         printf( "\n" );
 104         Opts opts;
 105         if( get_Opts( argc, argv, &opts )) {
 106                 fprintf( stderr, "Unable to parse command line\n" );
 107                 return EXIT_FAILURE;
 108         }
 109
 110         // Connect to the database
 111         dbi_conn dbhandle = connect_db( &opts );
 112         if( NULL == dbhandle )
 113                 return EXIT_FAILURE;
 114
 115         if( opts.verbose )
 116                 oilsStoredQSetVerbose();
 117
 118         osrfLogSetLevel( OSRF_LOG_WARNING );
 119
 120         // Load the IDL
 121         if ( !oilsIDLInit( opts.idl )) {
 122                 fprintf( stderr, "Unable to load IDL at %s\n", opts.idl );
 123                 return EXIT_FAILURE;
 124         }
 125
 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 ] ));
 131
 132         if( !sq ) {
 133                 show_msgs( state->error_msgs );
 134                 printf( "Unable to build query\n" );
 135         } else {
 136                 // If so requested, show the bind variables
 137                 if( opts.bind )
 138                         show_bind_variables( state->bindvar_list );
 139
 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" );
 144                 }
 145                 else {
 146                         printf( "%s\n", OSRF_BUFFER_C_STR( state->sql ));
 147
 148                         // If so requested, get the column names and display them
 149                         if( opts.columns ) {
 150                                 jsonObject* cols = oilsGetColNames( state, sq );
 151                                 if( cols ) {
 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 );
 156                                         free( cols_out );
 157                                         free( cols_str );
 158                                         jsonObjectFree( cols );
 159                                 } else
 160                                         fprintf( stderr, "Unable to get column names\n\n" );
 161                         }
 162
 163                         // If so requested, execute the query and display the results
 164                         if( opts.execute ) {
 165                                 jsonObject* row = oilsFirstRow( state );
 166                                 if( state->error ) {
 167                                         show_msgs( state->error_msgs );
 168                                         fprintf( stderr, "Unable to execute query\n" );
 169                                 } else {
 170                                         printf( "[" );
 171                                         int first = 1;         // boolean
 172                                         while( row ) {
 173
 174                                                 if( first ) {
 175                                                         printf( "\n\t" );
 176                                                         first = 0;
 177                                                 } else
 178                                                         printf( ",\n\t" );
 179
 180                                                 char* json = jsonObjectToJSON( row );
 181                                                 printf( "%s", json );
 182                                                 free( json );
 183                                                 row = oilsNextRow( state );
 184                                         }
 185                                         if( state->error ) {
 186                                                 show_msgs( state->error_msgs );
 187                                                 fprintf( stderr, "Unable to fetch row\n" );
 188                                         }
 189                                         printf( "\n]\n" );
 190                                 }
 191                         }
 192                 }
 193         }
 194
 195         storedQFree( sq );
 196         buildSQLStateFree( state );
 197
 198         buildSQLCleanup();
 199         if ( dbhandle )
 200                 dbi_conn_close( dbhandle );
 201
 202         return EXIT_SUCCESS;
 203 }
 204
 205 /**
 206         @brief Display the bind variables.
 207         @param vars Pointer to a hash keyed on bind variable name.
 208
 209         The data for each hash entry is a BindVar, a C struct whose members define the
 210         attributes of the bind variable.
 211 */
 212 static void show_bind_variables( osrfHash* vars ) {
 213         printf( "Bind variables:\n\n" );
 214         BindVar* bind = NULL;
 215         osrfHashIterator* iter = osrfNewHashIterator( vars );
 216
 217         // Traverse the hash of bind variables
 218         while(( bind = osrfHashIteratorNext( iter ))) {
 219                 const char* type = NULL;
 220                 switch( bind->type ) {
 221                         case BIND_STR :
 222                                 type = "string";
 223                                 break;
 224                         case BIND_NUM :
 225                                 type = "number";
 226                                 break;
 227                         case BIND_STR_LIST :
 228                                 type = "string list";
 229                                 break;
 230                         case BIND_NUM_LIST :
 231                                 type = "number list";
 232                                 break;
 233                         default :
 234                                 type = "(unrecognized)";
 235                                 break;
 236                 }
 237
 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 );
 243
 244                 char* actual_value = NULL;
 245                 if( bind->actual_value )
 246                         actual_value = jsonObjectToJSONRaw( bind->actual_value );
 247
 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)" );
 255                 printf( "\n" );
 256
 257                 if( default_value )
 258                         free( default_value );
 259
 260                 if( actual_value )
 261                         free( actual_value );
 262         } // end while
 263
 264         osrfHashIteratorFree( iter );
 265 }
 266
 267 /**
 268         @brief Write a series of strings to standard output.
 269         @param sa Array of strings.
 270
 271         Display messages emitted by the query-building machinery.
 272 */
 273 static void show_msgs( const osrfStringArray* sa ) {
 274         if( sa ) {
 275                 int i;
 276                 for( i = 0; i < sa->size; ++i ) {
 277                         const char* s = osrfStringArrayGetString( sa, i );
 278                         if( s )
 279                                 printf( "%s\n", s );
 280                 }
 281         }
 282 }
 283
 284 /**
 285         @brief Connect to the database.
 286         @return If successful, a database handle; otherwise NULL;
 287 */
 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 );
 292         if( !dbhandle ) {
 293                 fprintf( stderr, "Error loading database driver [%s]", opts->driver );
 294                 return NULL;
 295         }
 296
 297         char* pw = NULL;
 298         growing_buffer* buf = buffer_init( 32 );
 299
 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" );
 304                 if( !pwfile ) {
 305                         fprintf( stderr, "Unable to open password file %s\n", opts->password_file );
 306                         buffer_free( buf );
 307                         return NULL;
 308                 } else {
 309                         if( load_pw( buf, pwfile )) {
 310                                 fprintf( stderr, "Unable to load password file %s\n", opts->password_file );
 311                                 buffer_free( buf );
 312                                 return NULL;
 313                         } else
 314                                 pw = buffer_release( buf );
 315                 }
 316         } else {
 317                 if( prompt_password( buf )) {
 318                         fprintf( stderr, "Unable to get password\n" );
 319                         buffer_free( buf );
 320                         return NULL;
 321                 } else
 322                         pw = buffer_release( buf );
 323         }
 324
 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 );
 331
 332         // Connect to the database
 333         const char* err;
 334         if( dbi_conn_connect( dbhandle) < 0 ) {
 335                 sleep( 1 );
 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 );
 340                         free( pw );
 341                         return NULL;
 342                 }
 343         }
 344
 345         free( pw );
 346         return dbhandle;
 347 }
 348
 349 /**
 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.
 354
 355         Intended for use in loading a password.
 356 */
 357 static int load_pw( growing_buffer* buf, FILE* in ) {
 358         buffer_reset( buf );
 359         while( 1 ) {
 360                 int c = getc( in );
 361                 if( '\n' == c || EOF == c )
 362                         break;
 363                 else if( '\b' == c )
 364                         buffer_chomp( buf );
 365                 else
 366                         OSRF_BUFFER_ADD_CHAR( buf, c );
 367         }
 368         return 0;
 369 }
 370
 371 /**
 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.
 375
 376         Read from /dev/tty if possible, or from stdin if not.
 377 */
 378 static int prompt_password( growing_buffer* buf ) {
 379         struct termios oldterm;
 380
 381         printf( "Password: " );
 382         fflush( stdout );
 383
 384         FILE* term = fopen( "//dev//tty", "rw" );
 385         if( NULL == term )
 386                 term = stdin;
 387
 388         // Capture the current state of the terminal
 389         if( tcgetattr( fileno( term ), &oldterm ))
 390                 return 1;
 391
 392         // Turn off echo
 393         struct termios newterm = oldterm;
 394         newterm.c_lflag &= ~ECHO;
 395         if( tcsetattr( fileno( term ), TCSAFLUSH, &newterm ))
 396                 return 1;
 397
 398         // Read the password
 399         int rc = load_pw( buf, term );
 400
 401         // Turn echo back on
 402         (void) tcsetattr( fileno( term ), TCSAFLUSH, &oldterm );  // restore echo
 403
 404         if( term != stdin )
 405                 fclose( term );
 406
 407         return rc;
 408 }
 409
 410 /**
 411         @brief Initialize an Opts structure.
 412         @param pOpts Pointer to the Opts to be initialized.
 413 */
 414 static void initialize_opts( Opts * pOpts ) {
 415         pOpts->new_argc = 0;
 416         pOpts->new_argv = NULL;
 417
 418         pOpts->bind = 0;
 419         pOpts->driver_found = 0;
 420         pOpts->driver = NULL;
 421         pOpts->database_found = 0;
 422         pOpts->database = NULL;
 423         pOpts->host_found = 0;
 424         pOpts->host = NULL;
 425         pOpts->idl_found = 0;
 426         pOpts->idl = NULL;
 427         pOpts->port_found = 0;
 428         pOpts->port = 0;
 429         pOpts->user_found = 0;
 430         pOpts->user = NULL;
 431         pOpts->password_file_found = 0;
 432         pOpts->password_file = NULL;
 433         pOpts->verbose = 0;
 434         pOpts->columns = 0;
 435         pOpts->execute = 0;
 436 }
 437
 438 /**
 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.
 444 */
 445 static int get_Opts( int argc, char * argv[], Opts * pOpts ) {
 446         int rc = 0; /* return code */
 447         unsigned long port_value = 0;
 448         char * tail = NULL;
 449         int opt;
 450
 451         /* Define valid option characters */
 452
 453         const char optstring[] = ":bD:cd:h:i:p:u:vw:x";
 454
 455         /* Initialize members of struct */
 456
 457         initialize_opts( pOpts );
 458
 459         /* Suppress error messages from getopt() */
 460
 461         opterr = 0;
 462
 463         /* Examine command line options */
 464
 465         while( ( opt = getopt( argc, argv, optstring )) != -1 )
 466         {
 467                 switch( opt )
 468                 {
 469                         case 'b' :   /* Display bind variables */
 470                                 pOpts->bind = 1;
 471                                 break;
 472                         case 'c' :   /* Display column names */
 473                                 pOpts->columns = 1;
 474                                 break;
 475                         case 'D' :   /* Get database driver */
 476                                 if( pOpts->driver_found )
 477                                 {
 478                                         fprintf( stderr, "Only one occurrence of -D option allowed\n" );
 479                                         rc = 1;
 480                                         break;
 481                                 }
 482                                 pOpts->driver_found = 1;
 483
 484                                 pOpts->driver = optarg;
 485                                 break;
 486                         case 'd' :   /* Get database name */
 487                                 if( pOpts->database_found )
 488                                 {
 489                                         fprintf( stderr, "Only one occurrence of -d option allowed\n" );
 490                                         rc = 1;
 491                                         break;
 492                                 }
 493                                 pOpts->database_found = 1;
 494
 495                                 pOpts->database = optarg;
 496                                 break;
 497                         case 'h' :   /* Get hostname of database */
 498                                 if( pOpts->host_found )
 499                                 {
 500                                         fprintf( stderr, "Only one occurrence of -h option allowed\n" );
 501                                         rc = 1;
 502                                         break;
 503                                 }
 504                                 pOpts->host_found = 1;
 505
 506                                 pOpts->host = optarg;
 507                                 break;
 508                         case 'i' :   /* Get name of IDL file */
 509                                 if( pOpts->idl_found )
 510                                 {
 511                                         fprintf( stderr, "Only one occurrence of -i option allowed\n" );
 512                                         rc = 1;
 513                                         break;
 514                                 }
 515                                 pOpts->idl_found = 1;
 516
 517                                 pOpts->idl = optarg;
 518                                 break;
 519                         case 'p' :   /* Get port number of database */
 520                                 if( pOpts->port_found )
 521                                 {
 522                                         fprintf( stderr, "Only one occurrence of -p option allowed\n" );
 523                                         rc = 1;
 524                                         break;
 525                                 }
 526                                 pOpts->port_found = 1;
 527
 528                                 /* Skip white space; check for negative */
 529
 530                                 while( isspace( (unsigned char) *optarg ))
 531                                         ++optarg;
 532
 533                                 if( '-' == *optarg )
 534                                 {
 535                                         fprintf( stderr, "Negative argument not allowed for "
 536                                                 "-p option: \"%s\"\n", optarg );
 537                                         rc = 1;
 538                                         break;
 539                                 }
 540
 541                                 /* Convert to numeric value */
 542
 543                                 errno = 0;
 544                                 port_value = strtoul( optarg, &tail, 10 );
 545                                 if( *tail != '\0' )
 546                                 {
 547                                         fprintf( stderr, "Invalid or non-numeric argument "
 548                                                         "to -p option: \"%s\"\n", optarg );
 549                                         rc = 1;
 550                                         break;
 551                                 }
 552                                 else if( errno != 0 )
 553                                 {
 554                                         fprintf( stderr, "Too large argument "
 555                                                 "to -p option: \"%s\"\n", optarg );
 556                                         rc = 1;
 557                                         break;
 558                                 }
 559
 560                                 pOpts->port = port_value;
 561                                 break;
 562                         case 'u' :   /* Get username of database account */
 563                                 if( pOpts->user_found )
 564                                 {
 565                                         fprintf( stderr, "Only one occurrence of -u option allowed\n" );
 566                                         rc = 1;
 567                                         break;
 568                                 }
 569                                 pOpts->user_found = 1;
 570
 571                                 pOpts->user = optarg;
 572                                 break;
 573                         case 'v' :   /* Set verbose mode */
 574                                 pOpts->verbose = 1;
 575                                 break;
 576                         case 'w' :   /* Get name of password_file */
 577                                 if( pOpts->password_file_found )
 578                                 {
 579                                         fprintf( stderr, "Only one occurrence of -w option allowed\n" );
 580                                         rc = 1;
 581                                         break;
 582                                 }
 583                                 pOpts->password_file_found = 1;
 584
 585                                 pOpts->password_file = optarg;
 586                                 break;
 587                         case 'x' :   /* Set execute */
 588                                 pOpts->execute = 1;
 589                                 break;
 590                         case ':' : /* Missing argument */
 591                                 fprintf( stderr, "Required argument missing on -%c option\n",
 592                                          (char) optopt );
 593                                 rc = 1;
 594                                 break;
 595                         case '?' : /* Invalid option */
 596                                 fprintf( stderr, "Invalid option '-%c' on command line\n",
 597                                         (char) optopt );
 598                                 rc = 1;
 599                                 break;
 600                         default :  /* Programmer error */
 601                                 fprintf( stderr, "Internal error: unexpected value '-%c'"
 602                                                 "for optopt", (char) optopt );
 603                                 rc = 1;
 604                                 break;
 605                 } /* end switch */
 606         } /* end while */
 607
 608         /* See if required options were supplied; apply defaults */
 609
 610         if( ! pOpts->driver_found )
 611                 pOpts->driver = "pgsql";
 612
 613         if( ! pOpts->database_found )
 614                 pOpts->database = "evergreen";
 615
 616         if( ! pOpts->host_found )
 617                 pOpts->host = "localhost";
 618
 619         if( ! pOpts->idl_found )
 620                 pOpts->idl = "/openils/conf/fm_IDL.xml";
 621
 622         if( ! pOpts->port_found )
 623                 pOpts->port = 5432;
 624
 625         if( ! pOpts->user_found )
 626                 pOpts->user = "evergreen";
 627
 628         if( optind > argc )
 629         {
 630                 /* This should never happen! */
 631
 632                 fprintf( stderr, "Program error: found more arguments than expected\n" );
 633                 rc = 1;
 634         }
 635         else
 636         {
 637                 /* Calculate new_argcv and new_argc to reflect */
 638                 /* the number of arguments consumed */
 639
 640                 pOpts->new_argc = argc - optind + 1;
 641                 pOpts->new_argv = argv + optind - 1;
 642
 643                 if( pOpts->new_argc < 2UL )
 644                 {
 645                         fprintf( stderr, "Not enough arguments beyond options; must be at least 1\n" );
 646                         rc = 1;
 647                 }
 648         }
 649
 650         return rc;
 651 }