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