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