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 <SqlStatement.h>
  19 #define SQL_STMT_LEN 1024
  20 enum STMT_TYPE
  21 {
  22     SELECT =0,
  23     DDL ,
  24     OTHER
  25 };
  26 STMT_TYPE stmtType = SELECT;
  27 FILE *fp;
  28 SqlConnection *conn;
  29 SqlStatement *stmt;
  30 bool getInput(bool);
  31 void printUsage()
  32 {
  33    printf("Usage: csql [-u username] [-p passwd] [-s sqlfile] \n");
  34    printf("       username -> username to connect to database\n");
  35    printf("       password -> password to connect to database\n");
  36    printf("       sqlfile -> filename containing sql statements\n");
  37    return;
  38
  39 }
  40
  41 int main(int argc, char **argv)
  42 {
  43     char username[IDENTIFIER_LENGTH];
  44     username [0] = '\0';
  45     char password[IDENTIFIER_LENGTH];
  46     password [0] = '\0';
  47     char filename[512];
  48     filename [0] ='\0';
  49     int c = 0, opt=0;
  50     while ((c = getopt(argc, argv, "u:p:s:?")) != EOF)
  51     {
  52         switch (c)
  53         {
  54             case 'u' : strcpy(username , argv[optind - 1]); break;
  55             case 'p' : strcpy(password , argv[optind - 1]); break;
  56             case 's' : strcpy(filename , argv[optind - 1]); break;
  57             case '?' : { opt = 1; break; } //print help
  58             default: printf("Wrong args\n"); exit(1);
  59
  60         }
  61     }//while options
  62     //printf("%s %s %s", username, password, filename);
  63     if (opt == 1)
  64     {
  65         printUsage();
  66         return 0;
  67     }
  68     if (username[0] == '\0' )
  69     {
  70         strcpy(username, "root");
  71         strcpy(password, "manager");
  72     }
  73     bool fileFlag = false;
  74     if (filename [0] !='\0')
  75     {
  76         fp = fopen(filename,"r");
  77         if (fp == NULL)
  78         {
  79             printf("Unable to open the file %s\n", filename);
  80             return 1;
  81         }
  82         fileFlag = true;
  83     }
  84
  85     DbRetVal rv = OK;
  86     conn = new SqlConnection();
  87     rv = conn->connect(username,password);
  88     if (rv != OK) return 1;
  89     stmt = new SqlStatement();
  90     stmt->setConnection(conn);
  91     rv = conn->beginTrans();
  92     if (rv != OK) return 2;
  93
  94     while (getInput(fileFlag) == true) continue;
  95
  96     //TODO::conn should provide method telling status of the transaction.
  97     //if running, then abort it
  98     conn->rollback();
  99     if (filename [0] !='\0')
 100     {
 101        fclose(fp);
 102     }
 103     delete stmt;
 104     delete conn;
 105     return 0;
 106 }
 107 bool handleTransaction(char *st)
 108 {
 109     while (isspace (*st)|| *st == '(' ) st++; // Skip white spaces
 110     if (strncasecmp (st, "COMMIT", 6) == 0 ||
 111         strncasecmp (st, "commit", 6) == 0 )
 112     {
 113         conn->commit();
 114         conn->beginTrans();
 115         return true;
 116     }
 117     else if (strncasecmp (st, "ROLLBACK", 8) == 0||
 118         strncasecmp (st, "rollback", 8) == 0)
 119     {
 120         conn->rollback();
 121         conn->beginTrans();
 122         return true;
 123     }
 124     return false;
 125 }
 126 bool handleEchoAndComment(char *st)
 127 {
 128     while (isspace (*st)|| *st == '(' ) st++; // Skip white spaces
 129     if (strncasecmp (st, "ECHO", 4) == 0 ||
 130         strncasecmp (st, "echo", 4) == 0 )
 131     {
 132         printf("%s\n", st);
 133         return true;
 134     }else if (strncmp(st, "--", 2) == 0)
 135     {
 136         return true;
 137     }
 138     return false;
 139 }
 140 void setStmtType(char *st)
 141 {
 142    if (strncasecmp (st, "SELECT", 6) == 0)  {stmtType=SELECT; return; }
 143    else if (strncasecmp (st, "CREATE", 6) == 0) {stmtType=DDL; return; }
 144    else if (strncasecmp (st, "DROP", 4) == 0) { stmtType=DDL; return; }
 145    stmtType = OTHER;
 146    return ;
 147 }
 148
 149 char getQueryFromStdIn(char *buf)
 150 {
 151     char    c;
 152     int    ln;
 153
 154     ln=1;
 155     printf("CSQL>");
 156     while( (c=(char ) getchar()) != EOF && c != ';')
 157     {
 158         *buf++ = c;
 159         if(c=='\n')
 160             //printf("%1d>",ln++);
 161             ln++;
 162     }
 163     *buf++ = ';';
 164     *buf = '\0';
 165     return c;
 166 }
 167 char getQueryFromFile(char *buf)
 168 {
 169     char    c;
 170     while( (c=(char ) fgetc(fp)) != EOF && c != ';')
 171     {
 172         *buf++ = c;
 173     }
 174     *buf++ = ';';
 175     *buf = '\0';
 176     return c;
 177 }
 178
 179 bool getInput(bool fromFile)
 180 {
 181     char buffer [SQL_STMT_LEN + 1];
 182
 183     char eof;
 184     if (fromFile == false)
 185         eof = getQueryFromStdIn(buffer);
 186     else
 187         eof = getQueryFromFile(buffer);
 188
 189     char *buf = buffer;
 190     while(*buf == ' ' || *buf == '\t' || *buf == '\n') buf++;
 191     if (eof == EOF || strncasecmp (buf, "quit", 4) == 0)
 192         return false;
 193     if (handleTransaction(buf)) return true;
 194     if (handleEchoAndComment(buf)) return true;
 195
 196     setStmtType(buf);
 197
 198     DbRetVal rv = stmt->prepare(buf);
 199     if (rv != OK)
 200     {
 201         printf("Statement prepare failed with error %d\n", rv);
 202         return true;
 203     }
 204     int rows =0;
 205     rv = stmt->execute(rows);
 206     if (rv != OK)
 207     {
 208         printf("Statement execute failed with error %d\n", rv);
 209         stmt->free();
 210         return true;
 211     }
 212     if (stmtType == OTHER)
 213     {
 214         printf("Statement Executed: Rows Affected = %d\n", rows);
 215     }
 216     else if (stmtType == DDL)
 217     {
 218         printf("Statement Executed\n");
 219     }
 220     else
 221     {
 222         FieldInfo *info = new FieldInfo();
 223         printf("---------------------------------------------------------\n");
 224         printf("\t");
 225         for (int i = 0 ; i < stmt->noOfProjFields() ; i++)
 226         {
 227             stmt->getProjFldInfo(i, info);
 228             printf("%s\t", info->fldName);
 229         }
 230         printf("\n---------------------------------------------------------\n");
 231         delete info;
 232         void *tuple = NULL;
 233         while(true)
 234         {
 235             printf("\t");
 236             tuple = (char*)stmt->fetchAndPrint();
 237             printf("\n");
 238             if (tuple == NULL) { break; }
 239         }
 240         stmt->close();
 241     }
 242     stmt->free();
 243     return true;
 244
 245 }