*** empty log message ***
[csql.git] / src / tools / isql.cxx
blob4c11cc8f1133686172e2727f7f5e865cb3ac9798
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 <os.h>
17 #include<CSql.h>
18 #include <DatabaseManagerImpl.h>
19 #include <Statement.h>
20 #include <SqlFactory.h>
21 #include <SqlStatement.h>
22 #include <SqlNwConnection.h>
23 #include <SqlNwStatement.h>
24 #ifndef NO_READLINE_LIB
25 #include <readline/readline.h>
26 #include <readline/history.h>
27 #endif
28 #include <NanoTimer.h>
29 #define SQL_STMT_LEN 8192
30 void printVariables();
32 enum STATEMENT_TYPE
34 SELECT =0,
35 EXPLAIN,
36 DDL ,
37 OTHER
39 STATEMENT_TYPE stmtType = SELECT;
40 FILE *fp;
41 AbsSqlConnection *conn;
42 AbsSqlStatement *stmt;
43 AbsSqlStatement *aconStmt; //stmt object when autocommit is on
44 List sqlStmtList;
45 SqlApiImplType type = CSqlUnknown;
46 bool gateway=false, silent=false;
47 bool autocommitmode = true;
48 bool isTimer = false;
49 bool network = false;
50 bool firstPrepare = true;
51 IsolationLevel isoLevel = READ_COMMITTED;
52 void printHelp();
53 bool getInput(bool);
54 void printUsage()
56 printf("Usage: csql [ [-u username] [-p passwd] ] [ [-H hostname] [-P port] ]\n");
57 printf(" [-s sqlfile] \n");
58 printf(" username -> username to connect to database\n");
59 printf(" password -> password to connect to database\n");
60 printf(" hostname -> hostname to connect to database through network\n");
61 printf(" port -> port no\n");
62 printf(" sqlfile -> filename containing sql statements\n");
63 return;
66 bool noUndolog=false;
67 bool exclusive=false;
68 int getConnType(int opt);
70 int main(int argc, char **argv)
72 char username[IDENTIFIER_LENGTH];
73 username [0] = '\0';
74 char password[IDENTIFIER_LENGTH];
75 password [0] = '\0';
76 char hostname[IDENTIFIER_LENGTH];
77 hostname[0] = '\0';
78 char port[8];
79 port[0] ='\0';
80 char filename[512];
81 filename [0] ='\0';
82 int c = 0, opt=0;
83 int connOpt = 0;
84 while ((c = getopt(argc, argv, "u:p:s:o:H:P:gXUS?")) != EOF)
86 switch (c)
88 case 'u' : strcpy(username , argv[optind - 1]); break;
89 case 'p' : strcpy(password , argv[optind - 1]); break;
90 case 's' : strcpy(filename , argv[optind - 1]); break;
91 case 'o' : { connOpt = atoi(argv[optind - 1]); break; }
92 case '?' : { opt = 1; break; } //print help
93 case 'S' : { silent = true; break; } //silent
94 case 'X' : { silent = true; exclusive = true; break; } //silent
95 case 'g' : { gateway = true; break; } //print help
96 case 'U' : { noUndolog = true; break; } //print help
97 case 'H' : { strcpy (hostname, argv[optind - 1]);
98 network = true; break; }
99 case 'P' : { strcpy (port, argv[optind - 1]);
100 network = true; break; }
101 default: printf("Wrong args\n"); exit(1);
104 }//while options
105 //printf("%s %s %s", username, password, filename);
106 if (opt == 1)
108 printUsage();
109 return 0;
111 if (username[0] == '\0' )
113 strcpy(username, I_USER);
114 strcpy(password, I_PASS);
116 if (network) {
117 if (hostname[0] == '\0') { printUsage(); return 0; }
118 if (port[0] == '\0') { printUsage(); return 0; }
120 bool fileFlag = false;
121 if (filename [0] !='\0')
123 fp = fopen(filename,"r");
124 if (fp == NULL)
126 printf("Unable to open the file %s\n", filename);
127 return 1;
129 fileFlag = true;
132 DbRetVal rv = OK;
133 if (connOpt) {
134 noUndolog =false;
135 type = (SqlApiImplType) getConnType(connOpt);
136 conn = SqlFactory::createConnection((SqlApiImplType)type);
137 if(connOpt == 4 || connOpt == 5 || connOpt == 6) {
138 SqlNwConnection *con = (SqlNwConnection *)conn;
139 con->setHost("localhost", 5678);
141 else if (connOpt == 7)
142 conn = SqlFactory::createConnection((SqlApiImplType)type);
143 } else {
144 if (network) {
145 if (gateway) type = CSqlNetworkGateway;
146 else type = CSqlNetwork;
147 conn = SqlFactory::createConnection(type);
148 SqlNwConnection *con = (SqlNwConnection *)conn;
149 con->setHost(hostname, atoi(port));
151 else {
152 if (gateway) type = CSqlGateway;
153 else {
154 if (exclusive) type=CSqlDirect; else type = CSql;
156 conn = SqlFactory::createConnection(type);
159 rv = conn->connect(username,password);
160 if (rv != OK) return 1;
161 if (exclusive) {
162 SqlConnection *sCon = (SqlConnection*) conn;
163 rv = sCon->getExclusiveLock();
164 if (rv != OK) {
165 conn->disconnect();
166 delete conn;
167 return 1;
170 aconStmt = SqlFactory::createStatement(type);
171 /* if (exclusive) {
172 SqlStatement *sqlStmt = (SqlStatement*)aconStmt;
173 sqlStmt->setLoading(true);
175 aconStmt->setConnection(conn);
176 //rv = conn->beginTrans(READ_COMMITTED, TSYNC);
177 rv = conn->beginTrans();
178 if (rv != OK) return 2;
179 while (getInput(fileFlag) == true) continue;
181 //TODO::conn should provide method telling status of the transaction.
182 //if running, then abort it
183 conn->rollback();
184 if (filename [0] !='\0')
186 fclose(fp);
188 conn->disconnect();
189 delete aconStmt;
190 delete conn;
191 return 0;
193 bool handleTransaction(char *st)
195 while (isspace (*st)|| *st == '(' ) st++; // Skip white spaces
196 if (strcasecmp (st, "COMMIT;") == 0 ||
197 strcasecmp (st, "commit;") == 0 )
199 conn->commit();
200 ListIterator it = sqlStmtList.getIterator();
201 while(it.hasElement()) {
202 stmt = (AbsSqlStatement *) it.nextElement();
203 stmt->free();
204 delete stmt;
206 sqlStmtList.reset();
207 //conn->beginTrans(isoLevel, TSYNC);
208 conn->beginTrans(isoLevel);
209 return true;
211 else if (strcasecmp (st, "ROLLBACK;") == 0||
212 strcasecmp (st, "rollback;") == 0)
214 conn->rollback();
215 ListIterator it = sqlStmtList.getIterator();
216 while(it.hasElement()) {
217 stmt = (AbsSqlStatement *)it.nextElement();
218 stmt->free();
219 delete stmt;
221 sqlStmtList.reset();
222 //conn->beginTrans(isoLevel, TSYNC);
223 conn->beginTrans(isoLevel);
224 return true;
226 else if (strcasecmp (st, "SET AUTOCOMMIT ON;") == 0)
228 autocommitmode = true;
229 if (!silent) printf("AUTOCOMMIT Mode is set to ON\n");
230 return true;
232 else if (strcasecmp (st, "SET AUTOCOMMIT OFF;") == 0)
234 autocommitmode = false;
235 if (!silent) printf("AUTOCOMMIT Mode is set to OFF\n");
236 return true;
238 else if (strcasecmp (st, "SET ISOLATION LEVEL UNCOMMITTED;") == 0)
240 isoLevel = READ_UNCOMMITTED;
241 printf("Isolation Level is set to READ_UNCOMMITTED\n");
242 return true;
244 else if (strcasecmp (st, "SET ISOLATION LEVEL COMMITTED;") == 0)
246 isoLevel = READ_COMMITTED;
247 printf("Isolation Level is set to READ_COMMITTED\n");
248 return true;
250 else if (strcasecmp (st, "SET ISOLATION LEVEL REPEATABLE;") == 0)
252 isoLevel = READ_REPEATABLE;
253 printf("Isolation Level is set to READ_REPEATABLE\n");
254 return true;
256 else if (strcasecmp(st, "SET TIMER ON;") == 0)
257 { isTimer=true; return true; }
258 else if (strcasecmp(st, "SET TIMER OFF;") == 0)
259 { isTimer=false; return true; }
260 return false;
262 bool handleEchoAndComment(char *st)
264 while (isspace (*st)|| *st == '(' ) st++; // Skip white spaces
265 if (strncasecmp (st, "ECHO", 4) == 0)
267 printf("%s\n", st);
268 return true;
269 }else if (strncmp(st, "--", 2) == 0)
271 return true;
272 }else if (strncasecmp(st, "help", 2) == 0)
274 printHelp();
275 return true;
276 }else if(strcasecmp(st,"show variables;")==0){
278 printVariables();
279 return true;
280 }else if (strcasecmp(st, "show tables;") == 0) {
281 DbRetVal rv = OK;
282 List tableList = aconStmt->getAllTableNames(rv);
283 ListIterator iter = tableList.getIterator();
284 Identifier *elem = NULL;
285 int ret =0;
286 printf("=============TableNames===================\n");
287 int count =0;
288 while (iter.hasElement())
290 elem = (Identifier*) iter.nextElement();
291 count++;
292 printf(" %s \n", elem->name);
294 if (count ==0) printf(" No tables exist\n");
295 printf("==========================================\n");
296 return true;
297 }else if( strcasecmp(st, "show users;" )==0){
298 DbRetVal rv = OK;
299 List userList = aconStmt->getAllUserNames(rv);
300 ListIterator iter = userList.getIterator();
301 Identifier *elem = NULL;
302 int ret =0;
303 printf("=============UserNames===================\n");
304 while (iter.hasElement())
306 elem = (Identifier*) iter.nextElement();
307 if(0==strcmp( elem->name ,I_USER))
308 continue;
309 printf(" %s \n", elem->name);
311 printf("=========================================\n");
312 return true;
314 return false;
316 void printHelp()
318 printf("CSQL Command List\n");
319 printf("======================================================\n");
320 printf("SHOW TABLES\n");
321 printf("SET AUTOCOMMIT ON | OFF\n");
322 printf("COMMIT | ROLLBACK\n");
323 printf("SET ISOLATION LEVEL UNCOMMITTED | COMMITTED | REPEATABLE\n");
324 printf("CREATE TABLE | INDEX ...\n");
325 printf("DROP TABLE | INDEX ...\n");
326 printf("INSERT ...\n");
327 printf("UPDATE ...\n");
328 printf("DELETE ...\n");
329 printf("SELECT ...\n");
330 printf("QUIT\n");
331 printf("======================================================\n");
333 void printVariables()
335 //SERVER Section Info.
336 printf("=======================================================\n");
337 printf("| SERVER SECTION INFORMATION |\n");
338 printf("=======================================================\n");
339 printf(" SITE_ID\t\t\t= %d\n",Conf::config.getSiteID());
340 printf(" PAGE_SIZE\t\t\t= %d Byte\n",Conf::config.getPageSize());
341 printf(" MAX_PROCS\t\t\t= %d\n",Conf::config.getMaxProcs());
342 printf(" MAX_SYS_DB_SIZE\t\t= %d MB\n",(Conf::config.getMaxSysDbSize()/1048576));
343 printf(" MAX_DB_SIZE\t\t\t= %d MB\n",(Conf::config.getMaxDbSize()/1048576));
344 printf(" SYS_DB_KEY\t\t\t= %d\n",Conf::config.getSysDbKey());
345 printf(" USER_DB_KEY\t\t\t= %d\n",Conf::config.getUserDbKey());
346 printf(" LOG_LEVEL\t\t\t= %d\n",Conf::config.getLogLevel());
347 printf(" MAP_ADDRESS\t\t\t= %ld\n",Conf::config.getMapAddress());
349 if(Conf::config.useDurability())
350 printf(" DURABILITY\t\t\t= True\n");
351 else
352 printf(" DURABILITY\t\t\t= False\n");
354 if(Conf::config.useMmap())
355 printf(" MMAP\t\t\t\t= True\n");
356 else
357 printf(" MMAP\t\t\t\t= False\n");
359 printf(" DURABLE_MODE\t\t\t= %d\n",Conf::config.getDurableMode());
360 printf(" DATABASE_FILE\t\t\t= %s\n",Conf::config.getDbFile());
361 printf(" LOG_FILE_PATH\t\t\t= %s\n",Conf::config.getLogFile());
362 printf(" STDERR_FILE\t\t\t= %s\n",Conf::config.getStderrFile());
364 //Network Section Information
365 printf("=======================================================\n");
366 printf("| NETWORK SECTION INFORMATION |\n");
367 printf("=======================================================\n");
369 if(Conf::config.useCsqlSqlServer())
370 printf(" CSQL_SQL_SERVER\t\t= True\n");
371 else
372 printf(" CSQL_SQL_SERVER\t\t= False\n");
374 printf(" PORT\t\t\t\t= %d\n",Conf::config. getPort());
375 printf(" NETWORK_RESPONSE_TIMEOUT\t= %d\n",Conf::config.getNetworkResponseTimeout());
376 printf(" NETWORK_CONNECT_TIMEOUT\t= %d\n",Conf::config.getNetworkConnectTimeout());
377 printf(" ID_SHM_KEY\t\t\t= %d\n",Conf::config.getShmIDKey());
379 //Client section variables
380 printf("=======================================================\n");
381 printf("| CLIENT SECTION INFORMATION |\n");
382 printf("=======================================================\n");
383 printf(" MUTEX_TIMEOUT_SECS\t\t= %d\n",Conf::config.getMutexSecs());
384 printf(" MUTEX_TIMEOUT_USECS\t\t= %d\n",Conf::config.getMutexUSecs());
385 printf(" MUTEX_TIMEOUT_RETRIES\t\t= %d\n",Conf::config.getMutexRetries());
386 printf(" LOCK_TIMEOUT_SECS\t\t= %d\n",Conf::config.getLockSecs());
387 printf(" LOCK_TIMEOUT_USECS\t\t= %d\n",Conf::config.getLockUSecs());
388 printf(" LOCK_TIMEOUT_RETRIES\t\t= %d\n",Conf::config.getLockRetries());
390 //CACHE Section variables
391 printf("=======================================================\n");
392 printf("| CACHE SECTION INFORMATION |\n");
393 printf("=======================================================\n");
395 if(Conf::config.useCache())
396 printf(" CACHE_TABLE\t\t\t= True\n");
397 else
398 printf(" CACHE_TABLE\t\t\t= False\n");
400 printf(" DSN\t\t\t\t= %s\n",Conf::config.getDSN());
402 if(Conf::config.useTwoWayCache())
403 printf(" ENABLE_BIDIRECTIONAL_CACHE\t= True\n");
404 else
405 printf(" ENABLE_BIDIRECTIONAL_CACHE\t= False\n");
407 printf(" CACHE_RECEIVER_WAIT_SECS\t= %d\n",Conf::config.getCacheWaitSecs());
409 if(Conf::config.getCacheMode()==0)
410 printf(" CACHE_MODE\t\t\t= SYNC\n");
411 else if (Conf::config.getCacheMode()==1)
412 printf(" CACHE_MODE\t\t\t= ASYNC\n");
413 else
414 printf(" CACHE_MODE\t\t\t= Unknown\n");
416 printf(" DS_CONFIG_FILE\t\t\t= %s\n",Conf::config.getDsConfigFile());
417 printf(" TABLE_CONFIG_FILE\t\t= %s\n",Conf::config.getTableConfigFile());
418 printf(" MSG_KEY\t\t\t= %d\n",Conf::config.getMsgKey() );
419 printf(" ASYNC_MSGMAX\t\t\t= %d\n",Conf::config.getAsyncMsgMax());
420 printf(" MAX_QUEUE_LOGS\t\t\t= %d\n",Conf::config.getLogLevel());
421 printf(" CONFL_RESOL_FILE\t\t= %s\n",Conf::config.getConflResoFile());
422 printf(" \nNOTE: To modify above variables, You will be following 'csql.conf' file.\n\n");
425 void setStmtType(char *st)
427 if (strncasecmp (st, "SELECT", 6) == 0) {stmtType=SELECT; return; }
428 else if (strncasecmp (st, "EXPLAIN", 6) == 0) {stmtType=EXPLAIN; return; }
429 else if (strncasecmp (st, "CREATE", 6) == 0) {stmtType=DDL; return; }
430 else if (strncasecmp (st, "DROP", 4) == 0) { stmtType=DDL; return; }
431 stmtType = OTHER;
432 return ;
435 char getQueryFromStdIn(char *buf)
437 char c, *bufBegin=buf;
438 int ln, charCnt=0;
439 #ifdef NO_READLINE_LIB
440 ln=1;
441 printf("CSQL>");
442 while( (c=(char ) getchar()) != EOF && c != ';')
444 *buf++ = c; charCnt++;
445 if(c=='\n') //printf("%1d>",ln++);
446 ln++;
447 if( charCnt == SQL_STMT_LEN ) {
448 printf("SQL Statement length is greater than %d. "
449 "Ignoring the statement.\n", SQL_STMT_LEN );
450 *bufBegin++ =';';
451 *bufBegin ='\0';
452 return 0;
454 //printf("=%d=\n", c);
456 *buf++ = ';';
457 *buf = '\0';
458 #else
459 strcpy(buf, "");
460 char *line = readline("CSQL>");
461 if (line) {strcpy(buf, line); add_history(line); }
462 else { free(line); return EOF; }
463 free(line);
464 #endif
465 return 0;
467 char getQueryFromFile(char *buf)
469 char c, *bufBegin=buf;
470 int charCnt=0;
471 while( (c=(char ) fgetc(fp)) != EOF && c != ';')
473 *buf++ = c; charCnt++;
474 if( charCnt == SQL_STMT_LEN ) {
475 printf("SQL Statement length is greater than %d. "
476 "Ignoring the statement.\n", SQL_STMT_LEN );
477 *bufBegin++ =';';
478 *bufBegin ='\0';
479 return 0;
482 *buf++ = ';';
483 *buf = '\0';
484 return c;
487 bool getInput(bool fromFile)
489 char buffer [SQL_STMT_LEN + 1];
491 char eof;
492 if (fromFile == false)
493 eof = getQueryFromStdIn(buffer);
494 else
495 eof = getQueryFromFile(buffer);
497 char *buf = buffer;
498 while(*buf == ' ' || *buf == '\t' || *buf == '\n') buf++;
499 if (eof == EOF || strncasecmp (buf, "quit", 4) == 0)
500 return false;
501 if (handleTransaction(buf)) return true;
502 if (handleEchoAndComment(buf)) return true;
503 if ( *buf == ';' ) return true; // Null statement.
505 setStmtType(buf);
506 NanoTimer timer;
507 DbRetVal rv;
508 if (autocommitmode) {
509 if (!firstPrepare) aconStmt->free();
510 rv = aconStmt->prepare(buf);
511 if (rv != OK) {
512 printf("Statement prepare failed with error %d\n", rv);
513 return true;
515 firstPrepare = false;
516 stmt=aconStmt;
518 else {
519 stmt = SqlFactory::createStatement(type);
520 stmt->setConnection(conn);
521 rv = stmt->prepare(buf);
522 if (rv != OK) {
523 printf("Statement prepare failed with error %d\n", rv);
524 return true;
526 sqlStmtList.append(stmt);
528 if (noUndolog || exclusive ){
529 SqlStatement *sqlStmt = (SqlStatement*)stmt;
530 sqlStmt->setLoading(true);
532 int rows =0;
533 timer.start();
534 rv = stmt->execute(rows);
535 if (rv != OK)
537 printf("Statement execute failed with error %d\n", rv);
538 if (autocommitmode) {
539 conn->rollback();
540 aconStmt->free();
541 firstPrepare = true; //set this so that it does not free again
542 conn->beginTrans(isoLevel);
544 else {
545 stmt->free();
546 sqlStmtList.remove(stmt);
547 delete stmt; stmt = NULL;
549 return true;
551 timer.stop();
552 if (stmtType == EXPLAIN)
554 stmt->close();
556 else if (stmtType == OTHER && stmt->isSelect()) stmtType = SELECT;
557 else if (stmtType == OTHER )
559 if (!silent) printf("Statement Executed: Rows Affected = %d\n", rows);
561 else if (stmtType == DDL)
563 if (!silent) printf("Statement Executed\n");
565 else
567 FieldInfo *info = new FieldInfo();
568 printf("---------------------------------------------------------\n");
569 printf("\t");
570 for (int i = 0 ; i < stmt->noOfProjFields() ; i++)
572 stmt->getProjFldInfo(i+1, info);
573 printf("%s\t", info->fldName);
575 printf("\n---------------------------------------------------------\n");
576 delete info;
577 void *tuple = NULL;
578 timer.start();
579 while(true)
581 printf("\t");
582 tuple = (char*)stmt->fetchAndPrint(false);
583 printf("\n");
584 if (tuple == NULL) { break; }
586 stmt->close();
587 timer.stop();
589 if (isTimer) printf("Time taken: %lld microsecs\n", timer.sum()/1000);
590 if (autocommitmode)
592 conn->commit();
593 //conn->beginTrans(isoLevel, TSYNC);
594 stmt->free();
595 firstPrepare = true; //set this so that it does not free again
596 conn->beginTrans(isoLevel);
597 return true;
599 return true;
602 int getConnType(int opt)
604 switch(opt) {
605 case 1: { printf("Local CSql\n"); return (int) CSql; }
606 case 2: { printf("Local Adapter\n"); return (int) CSqlAdapter; }
607 case 3: { printf("Local Gateway\n"); return (int) CSqlGateway; }
608 case 4: { printf("Network CSql\n"); return (int) CSqlNetwork; }
609 case 5: { printf("Network Adapter\n"); return (int) CSqlNetworkAdapter;}
610 case 6: { printf("Network Gateway\n"); return (int) CSqlNetworkGateway;}
611 case 7: { printf("Log\n"); return (int) CSqlLog; }
612 case 8: { printf("Log\n"); return (int) CSqlLog; }