src/tools/isql.cxx

   1 /***************************************************************************
   2  *   Copyright (C) 2007 by www.databasecache.com                           *
   3  *   Contact: praba_tuty@databasecache.com                                 *
   4  *                                                                         *
   5  *   This program is free software; you can redistribute it and/or modify  *
   6  *   it under the terms of the GNU General Public License as published by  *
   7  *   the Free Software Foundation; either version 2 of the License, or     *
   8  *   (at your option) any later version.                                   *
   9  *                                                                         *
  10  *   This program is distributed in the hope that it will be useful,       *
  11  *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
  12  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
  13  *   GNU General Public License for more details.                          *
  14  *                                                                         *
  15  ***************************************************************************/
  16 #include <os.h>
  17 #include<CSql.h>
  18 #include<DatabaseManagerImpl.h>
  19 #include <Statement.h>
  20 #include <SqlFactory.h>
  21 #include <SqlStatement.h>
  22 #include <SqlNwConnection.h>
  23 #include <SqlNwStatement.h>
  24 #include <readline/readline.h>
  25 #include <readline/history.h>
  26 #define SQL_STMT_LEN 1024
  27 enum STMT_TYPE
  28 {
  29     SELECT =0,
  30     DDL ,
  31     OTHER
  32 };
  33 STMT_TYPE stmtType = SELECT;
  34 FILE *fp;
  35 AbsSqlConnection *conn;
  36 AbsSqlStatement *stmt;
  37 AbsSqlStatement *aconStmt; //stmt object when autocommit is on
  38 List sqlStmtList;
  39 SqlApiImplType type = CSqlUnknown;
  40 bool gateway=false, silent=false;
  41 bool autocommitmode = true;
  42 bool network = false;
  43 bool firstPrepare = false;
  44 IsolationLevel isoLevel = READ_COMMITTED;
  45 void printHelp();
  46 bool getInput(bool);
  47 void printUsage()
  48 {
  49    printf("Usage: csql [ [-u username] [-p passwd] ] [ [-H hostname] [-P port] ]\n");
  50    printf("            [-s sqlfile] \n");
  51    printf("       username -> username to connect to database\n");
  52    printf("       password -> password to connect to database\n");
  53    printf("       hostname -> hostname to connect to database through network\n");
  54    printf("       port     -> port no\n");
  55    printf("       sqlfile  -> filename containing sql statements\n");
  56    return;
  57
  58 }
  59
  60 int getConnType(int opt);
  61
  62 int main(int argc, char **argv)
  63 {
  64     char username[IDENTIFIER_LENGTH];
  65     username [0] = '\0';
  66     char password[IDENTIFIER_LENGTH];
  67     password [0] = '\0';
  68     char hostname[IDENTIFIER_LENGTH];
  69     hostname[0] = '\0';
  70     char port[8];
  71     port[0] ='\0';
  72     char filename[512];
  73     filename [0] ='\0';
  74     int c = 0, opt=0;
  75     bool exclusive=false;
  76     int connOpt = 0;
  77     while ((c = getopt(argc, argv, "u:p:s:o:H:P:gXS?")) != EOF)
  78     {
  79         switch (c)
  80         {
  81             case 'u' : strcpy(username , argv[optind - 1]); break;
  82             case 'p' : strcpy(password , argv[optind - 1]); break;
  83             case 's' : strcpy(filename , argv[optind - 1]); break;
  84             case 'o' : { connOpt = atoi(argv[optind - 1]); break; }
  85             case '?' : { opt = 1; break; } //print help
  86             case 'S' : { silent = true; break; } //silent
  87             case 'X' : { silent = true; exclusive = true; break; } //silent
  88             case 'g' : { gateway = true; break; } //print help
  89             case 'H' : { strcpy (hostname, argv[optind - 1]);
  90                          network = true; break; }
  91             case 'P' : { strcpy (port,     argv[optind - 1]);
  92                          network = true; break; }
  93             default: printf("Wrong args\n"); exit(1);
  94
  95         }
  96     }//while options
  97     //printf("%s %s %s", username, password, filename);
  98     if (opt == 1)
  99     {
 100         printUsage();
 101         return 0;
 102     }
 103     if (username[0] == '\0' )
 104     {
 105         strcpy(username, "root");
 106         strcpy(password, "manager");
 107     }
 108     if (network) {
 109         if (hostname[0] == '\0') { printUsage(); return 0; }
 110         if (port[0] == '\0') { printUsage(); return 0; }
 111     }
 112     bool fileFlag = false;
 113     if (filename [0] !='\0')
 114     {
 115         fp = fopen(filename,"r");
 116         if (fp == NULL)
 117         {
 118             printf("Unable to open the file %s\n", filename);
 119             return 1;
 120         }
 121         fileFlag = true;
 122     }
 123
 124     DbRetVal rv = OK;
 125     if (connOpt) {
 126         type = (SqlApiImplType) getConnType(connOpt);
 127         conn = SqlFactory::createConnection((SqlApiImplType)type);
 128         if(connOpt == 4 || connOpt == 5 || connOpt == 6) {
 129             SqlNwConnection *con = (SqlNwConnection *)conn;
 130             con->setHost("localhost", 5678);
 131         }
 132         else if (connOpt == 7)
 133             conn = SqlFactory::createConnection((SqlApiImplType)type);
 134     } else {
 135         if (network) {
 136             if (gateway) type = CSqlNetworkGateway;
 137             else type = CSqlNetwork;
 138             conn = SqlFactory::createConnection(type);
 139             SqlNwConnection *con = (SqlNwConnection *)conn;
 140             con->setHost(hostname, atoi(port));
 141         }
 142         else {
 143             if (gateway) type = CSqlGateway;
 144             else {
 145                if (exclusive) type=CSqlDirect;
 146                else type = CSql;
 147             }
 148             conn = SqlFactory::createConnection(type);
 149         }
 150     }
 151     rv = conn->connect(username,password);
 152     if (rv != OK) return 1;
 153     if (exclusive) {
 154         SqlConnection *sCon = (SqlConnection*) conn;
 155         rv = sCon->getExclusiveLock();
 156         if (rv != OK) {
 157             conn->disconnect();
 158             delete conn;
 159             return 1;
 160         }
 161     }
 162     aconStmt = SqlFactory::createStatement(type);
 163     if (exclusive) {
 164        SqlStatement *sqlStmt = (SqlStatement*)aconStmt;
 165        sqlStmt->setLoading(true);
 166     }
 167     aconStmt->setConnection(conn);
 168     //rv = conn->beginTrans(READ_COMMITTED, TSYNC);
 169     rv = conn->beginTrans();
 170     if (rv != OK) return 2;
 171     while (getInput(fileFlag) == true) continue;
 172
 173     //TODO::conn should provide method telling status of the transaction.
 174     //if running, then abort it
 175     conn->rollback();
 176     if (filename [0] !='\0')
 177     {
 178        fclose(fp);
 179     }
 180     conn->disconnect();
 181     delete aconStmt;
 182     delete conn;
 183     return 0;
 184 }
 185 bool handleTransaction(char *st)
 186 {
 187     while (isspace (*st)|| *st == '(' ) st++; // Skip white spaces
 188     if (strcasecmp (st, "COMMIT;") == 0 ||
 189         strcasecmp (st, "commit;") == 0 )
 190     {
 191         conn->commit();
 192         ListIterator it = sqlStmtList.getIterator();
 193         while(it.hasElement()) {
 194             stmt = (AbsSqlStatement *) it.nextElement();
 195             stmt->free();
 196             delete stmt;
 197         }
 198         sqlStmtList.reset();
 199         //conn->beginTrans(isoLevel, TSYNC);
 200         conn->beginTrans(isoLevel);
 201         return true;
 202     }
 203     else if (strcasecmp (st, "ROLLBACK;") == 0||
 204         strcasecmp (st, "rollback;") == 0)
 205     {
 206         conn->rollback();
 207         ListIterator it = sqlStmtList.getIterator();
 208         while(it.hasElement()) {
 209             stmt = (AbsSqlStatement *)it.nextElement();
 210             stmt->free();
 211             delete stmt;
 212         }
 213         sqlStmtList.reset();
 214         //conn->beginTrans(isoLevel, TSYNC);
 215         conn->beginTrans(isoLevel);
 216         return true;
 217     }
 218     else if (strcasecmp (st, "SET AUTOCOMMIT ON;") == 0)
 219     {
 220         autocommitmode = true;
 221         if (!silent) printf("AUTOCOMMIT Mode is set to ON\n");
 222         return true;
 223     }
 224     else if (strcasecmp (st, "SET AUTOCOMMIT OFF;") == 0)
 225     {
 226         autocommitmode = false;
 227         if (!silent) printf("AUTOCOMMIT Mode is set to OFF\n");
 228         return true;
 229     }
 230     else if (strcasecmp (st, "SET ISOLATION LEVEL UNCOMMITTED;") == 0)
 231     {
 232         isoLevel = READ_UNCOMMITTED;
 233         printf("Isolation Level is set to READ_UNCOMMITTED\n");
 234         return true;
 235     }
 236     else if (strcasecmp (st, "SET ISOLATION LEVEL COMMITTED;") == 0)
 237     {
 238         isoLevel = READ_COMMITTED;
 239         printf("Isolation Level is set to READ_COMMITTED\n");
 240         return true;
 241     }
 242     else if (strcasecmp (st, "SET ISOLATION LEVEL REPEATABLE;") == 0)
 243     {
 244         isoLevel = READ_REPEATABLE;
 245         printf("Isolation Level is set to READ_REPEATABLE\n");
 246         return true;
 247     }
 248     return false;
 249 }
 250 bool handleEchoAndComment(char *st)
 251 {
 252     while (isspace (*st)|| *st == '(' ) st++; // Skip white spaces
 253     if (strncasecmp (st, "ECHO", 4) == 0)
 254     {
 255         printf("%s\n", st);
 256         return true;
 257     }else if (strncmp(st, "--", 2) == 0)
 258     {
 259         return true;
 260     }else if (strncasecmp(st, "help", 2) == 0)
 261     {
 262         printHelp();
 263         return true;
 264     }else if (strcasecmp(st, "show tables;") == 0) {
 265       DbRetVal rv = OK;
 266       List tableList = aconStmt->getAllTableNames(rv);
 267       ListIterator iter = tableList.getIterator();
 268       Identifier *elem = NULL;
 269       int ret =0;
 270       printf("=============TableNames===================\n");
 271       int count =0;
 272       while (iter.hasElement())
 273       {
 274           elem = (Identifier*) iter.nextElement();
 275           count++;
 276           printf("   %s \n", elem->name);
 277       }
 278       if (count ==0) printf("  No tables exist\n");
 279       printf("==========================================\n");
 280
 281         return true;
 282     }
 283     return false;
 284 }
 285 void printHelp()
 286 {
 287     printf("CSQL Command List\n");
 288     printf("======================================================\n");
 289     printf("SHOW TABLES\n");
 290     printf("SET AUTOCOMMIT ON | OFF\n");
 291     printf("COMMIT | ROLLBACK\n");
 292     printf("SET ISOLATION LEVEL UNCOMMITTED | COMMITTED | REPEATABLE\n");
 293     printf("CREATE TABLE | INDEX ...\n");
 294     printf("DROP TABLE | INDEX ...\n");
 295     printf("INSERT ...\n");
 296     printf("UPDATE ...\n");
 297     printf("DELETE ...\n");
 298     printf("SELECT ...\n");
 299     printf("QUIT\n");
 300     printf("======================================================\n");
 301 }
 302 void setStmtType(char *st)
 303 {
 304    if (strncasecmp (st, "SELECT", 6) == 0)  {stmtType=SELECT; return; }
 305    else if (strncasecmp (st, "CREATE", 6) == 0) {stmtType=DDL; return; }
 306    else if (strncasecmp (st, "DROP", 4) == 0) { stmtType=DDL; return; }
 307    stmtType = OTHER;
 308    return ;
 309 }
 310
 311 char getQueryFromStdIn(char *buf)
 312 {
 313     char    c, *bufBegin=buf;
 314     int    ln, charCnt=0;
 315     /*ln=1;
 316     printf("CSQL>");
 317     while( (c=(char ) getchar()) != EOF && c != ';')
 318     {
 319         *buf++ = c; charCnt++;
 320         if(c=='\n') //printf("%1d>",ln++);
 321             ln++;
 322         if( charCnt == SQL_STMT_LEN ) {
 323             printf("SQL Statement length is greater than %d. "
 324             "Ignoring the statement.\n", SQL_STMT_LEN );
 325             *bufBegin++ =';';
 326             *bufBegin ='\0';
 327             return 0;
 328         }
 329         printf("=%d=\n", c);
 330     }
 331     *buf++ = ';';
 332     *buf = '\0';
 333     */
 334     strcpy(buf, "");
 335     char *line = readline("CSQL>");
 336     if (line) {strcpy(buf, line); add_history(line); }
 337     else return EOF;
 338     return 0;
 339 }
 340 char getQueryFromFile(char *buf)
 341 {
 342     char    c, *bufBegin=buf;
 343     int charCnt=0;
 344     while( (c=(char ) fgetc(fp)) != EOF && c != ';')
 345     {
 346         *buf++ = c; charCnt++;
 347         if( charCnt == SQL_STMT_LEN ) {
 348             printf("SQL Statement length is greater than %d. "
 349             "Ignoring the statement.\n", SQL_STMT_LEN );
 350             *bufBegin++ =';';
 351             *bufBegin ='\0';
 352             return 0;
 353         }
 354     }
 355     *buf++ = ';';
 356     *buf = '\0';
 357     return c;
 358 }
 359
 360 bool getInput(bool fromFile)
 361 {
 362     char buffer [SQL_STMT_LEN + 1];
 363
 364     char eof;
 365     if (fromFile == false)
 366         eof = getQueryFromStdIn(buffer);
 367     else
 368         eof = getQueryFromFile(buffer);
 369
 370     char *buf = buffer;
 371     while(*buf == ' ' || *buf == '\t' || *buf == '\n') buf++;
 372     if (eof == EOF || strncasecmp (buf, "quit", 4) == 0)
 373         return false;
 374     if (handleTransaction(buf)) return true;
 375     if (handleEchoAndComment(buf)) return true;
 376     if ( *buf == ';' ) return true; // Null statement.
 377
 378     setStmtType(buf);
 379
 380     DbRetVal rv;
 381     if (autocommitmode) {
 382         if (firstPrepare) aconStmt->free();
 383         rv  = aconStmt->prepare(buf);
 384         if (rv != OK) {
 385             printf("Statement prepare failed with error %d\n", rv);
 386             return true;
 387         }
 388         firstPrepare = true;
 389         stmt=aconStmt;
 390     }
 391     else {
 392         stmt = SqlFactory::createStatement(type);
 393         stmt->setConnection(conn);
 394         rv  = stmt->prepare(buf);
 395         if (rv != OK) {
 396             printf("Statement prepare failed with error %d\n", rv);
 397             return true;
 398         }
 399         sqlStmtList.append(stmt);
 400     }
 401     int rows =0;
 402     rv = stmt->execute(rows);
 403     if (rv != OK)
 404     {
 405         printf("Statement execute failed with error %d\n", rv);
 406         if (autocommitmode) {
 407             conn->rollback();
 408             aconStmt->free();
 409             firstPrepare = false; //set this so that it does not free again
 410             conn->beginTrans(isoLevel);
 411         }
 412         else {
 413             stmt->free();
 414             sqlStmtList.remove(stmt);
 415             delete stmt; stmt = NULL;
 416         }
 417         return true;
 418     }
 419     if (stmtType == OTHER)
 420     {
 421         if (!silent) printf("Statement Executed: Rows Affected = %d\n", rows);
 422     }
 423     else if (stmtType == DDL)
 424     {
 425         if (!silent) printf("Statement Executed\n");
 426     }
 427     else
 428     {
 429         FieldInfo *info = new FieldInfo();
 430         printf("---------------------------------------------------------\n");
 431         printf("\t");
 432         for (int i = 0 ; i < stmt->noOfProjFields() ; i++)
 433         {
 434                         stmt->getProjFldInfo(i+1, info);
 435             printf("%s\t", info->fldName);
 436         }
 437         printf("\n---------------------------------------------------------\n");
 438         delete info;
 439         void *tuple = NULL;
 440         while(true)
 441         {
 442             printf("\t");
 443             tuple = (char*)stmt->fetchAndPrint(false);
 444             printf("\n");
 445             if (tuple == NULL) { break; }
 446         }
 447         stmt->close();
 448     }
 449     if (autocommitmode)
 450     {
 451         conn->commit();
 452         //conn->beginTrans(isoLevel, TSYNC);
 453         stmt->free();
 454         firstPrepare = false; //set this so that it does not free again
 455         conn->beginTrans(isoLevel);
 456         return true;
 457     }
 458     return true;
 459 }
 460
 461 int getConnType(int opt)
 462 {
 463     switch(opt) {
 464                 case 1: { printf("Local CSql\n"); return (int) CSql; }
 465                 case 2: { printf("Local Adapter\n"); return (int) CSqlAdapter; }
 466                 case 3: { printf("Local Gateway\n"); return (int) CSqlGateway; }
 467                 case 4: { printf("Network CSql\n");  return (int) CSqlNetwork; }
 468                 case 5: { printf("Network Adapter\n"); return (int) CSqlNetworkAdapter;}
 469                 case 6: { printf("Netwrok Gateway\n"); return (int) CSqlNetworkGateway;}
 470     }
 471 }