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<CSql.h>
  17 #include<DatabaseManagerImpl.h>
  18 #include <Statement.h>
  19 #include <SqlFactory.h>
  20 #include <SqlStatement.h>
  21 #include <SqlNwConnection.h>
  22 #include <SqlNwStatement.h>
  23 #define SQL_STMT_LEN 1024
  24 enum STMT_TYPE
  25 {
  26     SELECT =0,
  27     DDL ,
  28     OTHER
  29 };
  30 STMT_TYPE stmtType = SELECT;
  31 FILE *fp;
  32 AbsSqlConnection *conn;
  33 AbsSqlStatement *stmt;
  34
  35 bool gateway=false, silent=false;
  36 bool autocommitmode = true;
  37 bool network = false;
  38 IsolationLevel isoLevel = READ_COMMITTED;
  39 void printHelp();
  40 bool getInput(bool);
  41 void printUsage()
  42 {
  43    printf("Usage: csql [ [-u username] [-p passwd] ] [ [-H hostname] [-P port] ]\n");
  44    printf("            [-s sqlfile] \n");
  45    printf("       username -> username to connect to database\n");
  46    printf("       password -> password to connect to database\n");
  47    printf("       hostname -> hostname to connect to database through network\n");
  48    printf("       port     -> port no\n");
  49    printf("       sqlfile  -> filename containing sql statements\n");
  50    return;
  51
  52 }
  53
  54 int main(int argc, char **argv)
  55 {
  56     char username[IDENTIFIER_LENGTH];
  57     username [0] = '\0';
  58     char password[IDENTIFIER_LENGTH];
  59     password [0] = '\0';
  60     char hostname[IDENTIFIER_LENGTH];
  61     hostname[0] = '\0';
  62     char port[8];
  63     port[0] ='\0';
  64     char filename[512];
  65     filename [0] ='\0';
  66     int c = 0, opt=0;
  67     while ((c = getopt(argc, argv, "u:p:s:H:P:gS?")) != EOF)
  68     {
  69         switch (c)
  70         {
  71             case 'u' : strcpy(username , argv[optind - 1]); break;
  72             case 'p' : strcpy(password , argv[optind - 1]); break;
  73             case 's' : strcpy(filename , argv[optind - 1]); break;
  74             case '?' : { opt = 1; break; } //print help
  75             case 'S' : { silent = true; break; } //silent
  76             case 'g' : { gateway = true; break; } //print help
  77             case 'H' : { strcpy (hostname, argv[optind - 1]);
  78                          network = true; break; }
  79             case 'P' : { strcpy (port,     argv[optind - 1]);
  80                          network = true; break; }
  81             default: printf("Wrong args\n"); exit(1);
  82
  83         }
  84     }//while options
  85     //printf("%s %s %s", username, password, filename);
  86     if (opt == 1)
  87     {
  88         printUsage();
  89         return 0;
  90     }
  91     if (username[0] == '\0' )
  92     {
  93         strcpy(username, "root");
  94         strcpy(password, "manager");
  95     }
  96     if (network) {
  97         if (hostname[0] == '\0') { printUsage(); return 0; }
  98         if (port[0] == '\0') { printUsage(); return 0; }
  99     }
 100     bool fileFlag = false;
 101     if (filename [0] !='\0')
 102     {
 103         fp = fopen(filename,"r");
 104         if (fp == NULL)
 105         {
 106             printf("Unable to open the file %s\n", filename);
 107             return 1;
 108         }
 109         fileFlag = true;
 110     }
 111
 112     DbRetVal rv = OK;
 113     if (gateway)
 114       conn = SqlFactory::createConnection(CSqlGateway);
 115     else if (network) {
 116       conn = new SqlNwConnection();
 117       conn->setInnerConnection(NULL);
 118       SqlNwConnection *con = (SqlNwConnection *)conn;
 119       con->setHost(hostname, atoi(port));
 120     }
 121     else
 122       conn = SqlFactory::createConnection(CSql);
 123
 124     rv = conn->connect(username,password);
 125     if (rv != OK) return 1;
 126     if (gateway)
 127       stmt =  SqlFactory::createStatement(CSqlGateway);
 128     else if (network) {
 129       stmt = new SqlNwStatement();
 130       stmt->setInnerStatement(NULL);
 131     }
 132     else
 133       stmt =  SqlFactory::createStatement(CSql);
 134     stmt->setConnection(conn);
 135     //rv = conn->beginTrans(READ_COMMITTED, TSYNC);
 136     rv = conn->beginTrans();
 137     if (rv != OK) return 2;
 138     while (getInput(fileFlag) == true) continue;
 139
 140     //TODO::conn should provide method telling status of the transaction.
 141     //if running, then abort it
 142     conn->rollback();
 143     if (filename [0] !='\0')
 144     {
 145        fclose(fp);
 146     }
 147     conn->disconnect();
 148     delete stmt;
 149     delete conn;
 150     return 0;
 151 }
 152 bool handleTransaction(char *st)
 153 {
 154     while (isspace (*st)|| *st == '(' ) st++; // Skip white spaces
 155     if (strcasecmp (st, "COMMIT;") == 0 ||
 156         strcasecmp (st, "commit;") == 0 )
 157     {
 158         conn->commit();
 159         //conn->beginTrans(isoLevel, TSYNC);
 160         conn->beginTrans(isoLevel);
 161         return true;
 162     }
 163     else if (strcasecmp (st, "ROLLBACK;") == 0||
 164         strcasecmp (st, "rollback;") == 0)
 165     {
 166         conn->rollback();
 167         //conn->beginTrans(isoLevel, TSYNC);
 168         conn->beginTrans(isoLevel);
 169         return true;
 170     }
 171     else if (strcasecmp (st, "SET AUTOCOMMIT ON;") == 0)
 172     {
 173         autocommitmode = true;
 174         if (!silent) printf("AUTOCOMMIT Mode is set to ON\n");
 175         return true;
 176     }
 177     else if (strcasecmp (st, "SET AUTOCOMMIT OFF;") == 0)
 178     {
 179         autocommitmode = false;
 180         if (!silent) printf("AUTOCOMMIT Mode is set to OFF\n");
 181         return true;
 182     }
 183     else if (strcasecmp (st, "SET ISOLATION LEVEL UNCOMMITTED;") == 0)
 184     {
 185         isoLevel = READ_UNCOMMITTED;
 186         printf("Isolation Level is set to READ_UNCOMMITTED\n");
 187         return true;
 188     }
 189     else if (strcasecmp (st, "SET ISOLATION LEVEL COMMITTED;") == 0)
 190     {
 191         isoLevel = READ_COMMITTED;
 192         printf("Isolation Level is set to READ_COMMITTED\n");
 193         return true;
 194     }
 195     else if (strcasecmp (st, "SET ISOLATION LEVEL REPEATABLE;") == 0)
 196     {
 197         isoLevel = READ_REPEATABLE;
 198         printf("Isolation Level is set to READ_REPEATABLE\n");
 199         return true;
 200     }
 201     return false;
 202 }
 203 bool handleEchoAndComment(char *st)
 204 {
 205     while (isspace (*st)|| *st == '(' ) st++; // Skip white spaces
 206     if (strncasecmp (st, "ECHO", 4) == 0)
 207     {
 208         printf("%s\n", st);
 209         return true;
 210     }else if (strncmp(st, "--", 2) == 0)
 211     {
 212         return true;
 213     }else if (strncasecmp(st, "help", 2) == 0)
 214     {
 215         printHelp();
 216         return true;
 217     }else if (strcasecmp(st, "show tables;") == 0)
 218     {
 219       Connection conn;
 220       conn.open("root","manager");
 221       DatabaseManagerImpl *dbMgr =  (DatabaseManagerImpl*)conn.getDatabaseManager();
 222       if (dbMgr == NULL)
 223       {
 224           printf("Unable to connect to csql server\n");
 225           return true;
 226       }
 227       List tableList = dbMgr->getAllTableNames();
 228       ListIterator iter = tableList.getIterator();
 229       Identifier *elem = NULL;
 230       int ret =0;
 231       printf("=============TableNames===================\n");
 232       int count =0;
 233       while (iter.hasElement())
 234       {
 235           elem = (Identifier*) iter.nextElement();
 236           count++;
 237           printf("   %s \n", elem->name);
 238       }
 239       if (count ==0) printf("  No tables exist\n");
 240       printf("=========================================\n");
 241       conn.close();
 242       return true;
 243     }
 244     return false;
 245 }
 246 void printHelp()
 247 {
 248     printf("CSQL Command List\n");
 249     printf("======================================================\n");
 250     printf("SHOW TABLES\n");
 251     printf("SET AUTOCOMMIT ON|OFF\n");
 252     printf("SET ISOLATION LEVEL UNCOMMITTED|COMMITTED|REPEATABLE\n");
 253     printf("CREATE TABLE|INDEX ...\n");
 254     printf("INSERT ...\n");
 255     printf("UPDATE ...\n");
 256     printf("DELETE ...\n");
 257     printf("SELECT ...\n");
 258     printf("======================================================\n");
 259 }
 260 void setStmtType(char *st)
 261 {
 262    if (strncasecmp (st, "SELECT", 6) == 0)  {stmtType=SELECT; return; }
 263    else if (strncasecmp (st, "CREATE", 6) == 0) {stmtType=DDL; return; }
 264    else if (strncasecmp (st, "DROP", 4) == 0) { stmtType=DDL; return; }
 265    stmtType = OTHER;
 266    return ;
 267 }
 268
 269 char getQueryFromStdIn(char *buf)
 270 {
 271     char    c, *bufBegin=buf;
 272     int    ln, charCnt=0;
 273
 274     ln=1;
 275     printf("CSQL>");
 276     while( (c=(char ) getchar()) != EOF && c != ';')
 277     {
 278         *buf++ = c; charCnt++;
 279         if(c=='\n') //printf("%1d>",ln++);
 280             ln++;
 281         if( charCnt == SQL_STMT_LEN ) {
 282             printf("SQL Statement length is greater than %d. "
 283             "Ignoring the statement.\n", SQL_STMT_LEN );
 284             *bufBegin++ =';';
 285             *bufBegin ='\0';
 286             return 0;
 287         }
 288     }
 289     *buf++ = ';';
 290     *buf = '\0';
 291     return c;
 292 }
 293 char getQueryFromFile(char *buf)
 294 {
 295     char    c, *bufBegin=buf;
 296     int charCnt=0;
 297     while( (c=(char ) fgetc(fp)) != EOF && c != ';')
 298     {
 299         *buf++ = c; charCnt++;
 300         if( charCnt == SQL_STMT_LEN ) {
 301             printf("SQL Statement length is greater than %d. "
 302             "Ignoring the statement.\n", SQL_STMT_LEN );
 303             *bufBegin++ =';';
 304             *bufBegin ='\0';
 305             return 0;
 306         }
 307     }
 308     *buf++ = ';';
 309     *buf = '\0';
 310     return c;
 311 }
 312
 313 bool getInput(bool fromFile)
 314 {
 315     char buffer [SQL_STMT_LEN + 1];
 316
 317     char eof;
 318     if (fromFile == false)
 319         eof = getQueryFromStdIn(buffer);
 320     else
 321         eof = getQueryFromFile(buffer);
 322
 323     char *buf = buffer;
 324     while(*buf == ' ' || *buf == '\t' || *buf == '\n') buf++;
 325     if (eof == EOF || strncasecmp (buf, "quit", 4) == 0)
 326         return false;
 327     if (handleTransaction(buf)) return true;
 328     if (handleEchoAndComment(buf)) return true;
 329     if ( *buf == ';' ) return true; // Null statement.
 330
 331     setStmtType(buf);
 332
 333     DbRetVal rv = stmt->prepare(buf);
 334     if (rv != OK)
 335     {
 336         printf("Statement prepare failed with error %d\n", rv);
 337         return true;
 338     }
 339     int rows =0;
 340     rv = stmt->execute(rows);
 341     if (rv != OK)
 342     {
 343         printf("Statement execute failed with error %d\n", rv);
 344         stmt->free();
 345         return true;
 346     }
 347     if (stmtType == OTHER)
 348     {
 349         if (!silent) printf("Statement Executed: Rows Affected = %d\n", rows);
 350     }
 351     else if (stmtType == DDL)
 352     {
 353         if (!silent) printf("Statement Executed\n");
 354     }
 355     else
 356     {
 357         FieldInfo *info = new FieldInfo();
 358         printf("---------------------------------------------------------\n");
 359         printf("\t");
 360         for (int i = 0 ; i < stmt->noOfProjFields() ; i++)
 361         {
 362             stmt->getProjFldInfo(i+1, info);
 363             printf("%s\t", info->fldName);
 364         }
 365         printf("\n---------------------------------------------------------\n");
 366         delete info;
 367         void *tuple = NULL;
 368         while(true)
 369         {
 370             printf("\t");
 371             tuple = (char*)stmt->fetchAndPrint(false);
 372             printf("\n");
 373             if (tuple == NULL) { break; }
 374         }
 375         stmt->close();
 376     }
 377     stmt->free();
 378     if (autocommitmode)
 379     {
 380         conn->commit();
 381         //conn->beginTrans(isoLevel, TSYNC);
 382         conn->beginTrans(isoLevel);
 383         return true;
 384     }
 385     return true;
 386 }