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