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