1 /***************************************************************************
2 * Copyright (C) 2007 by www.databasecache.com *
3 * Contact: praba_tuty@databasecache.com *
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. *
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. *
15 ***************************************************************************/
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 #include <readline/readline.h>
25 #include <readline/history.h>
26 #include <NanoTimer.h>
27 #define SQL_STMT_LEN 8192
28 void printVariables();
37 STATEMENT_TYPE stmtType
= SELECT
;
39 AbsSqlConnection
*conn
;
40 AbsSqlStatement
*stmt
;
41 AbsSqlStatement
*aconStmt
; //stmt object when autocommit is on
43 SqlApiImplType type
= CSqlUnknown
;
44 bool gateway
=false, silent
=false;
45 bool autocommitmode
= true;
48 bool firstPrepare
= false;
49 IsolationLevel isoLevel
= READ_COMMITTED
;
54 printf("Usage: csql [ [-u username] [-p passwd] ] [ [-H hostname] [-P port] ]\n");
55 printf(" [-s sqlfile] \n");
56 printf(" username -> username to connect to database\n");
57 printf(" password -> password to connect to database\n");
58 printf(" hostname -> hostname to connect to database through network\n");
59 printf(" port -> port no\n");
60 printf(" sqlfile -> filename containing sql statements\n");
66 int getConnType(int opt
);
68 int main(int argc
, char **argv
)
70 char username
[IDENTIFIER_LENGTH
];
72 char password
[IDENTIFIER_LENGTH
];
74 char hostname
[IDENTIFIER_LENGTH
];
82 while ((c
= getopt(argc
, argv
, "u:p:s:o:H:P:gXUS?")) != EOF
)
86 case 'u' : strcpy(username
, argv
[optind
- 1]); break;
87 case 'p' : strcpy(password
, argv
[optind
- 1]); break;
88 case 's' : strcpy(filename
, argv
[optind
- 1]); break;
89 case 'o' : { connOpt
= atoi(argv
[optind
- 1]); break; }
90 case '?' : { opt
= 1; break; } //print help
91 case 'S' : { silent
= true; break; } //silent
92 case 'X' : { silent
= true; exclusive
= true; break; } //silent
93 case 'g' : { gateway
= true; break; } //print help
94 case 'U' : { noUndolog
= true; break; } //print help
95 case 'H' : { strcpy (hostname
, argv
[optind
- 1]);
96 network
= true; break; }
97 case 'P' : { strcpy (port
, argv
[optind
- 1]);
98 network
= true; break; }
99 default: printf("Wrong args\n"); exit(1);
103 //printf("%s %s %s", username, password, filename);
109 if (username
[0] == '\0' )
111 strcpy(username
, I_USER
);
112 strcpy(password
, I_PASS
);
115 if (hostname
[0] == '\0') { printUsage(); return 0; }
116 if (port
[0] == '\0') { printUsage(); return 0; }
118 bool fileFlag
= false;
119 if (filename
[0] !='\0')
121 fp
= fopen(filename
,"r");
124 printf("Unable to open the file %s\n", filename
);
133 type
= (SqlApiImplType
) getConnType(connOpt
);
134 conn
= SqlFactory::createConnection((SqlApiImplType
)type
);
135 if(connOpt
== 4 || connOpt
== 5 || connOpt
== 6) {
136 SqlNwConnection
*con
= (SqlNwConnection
*)conn
;
137 con
->setHost("localhost", 5678);
139 else if (connOpt
== 7)
140 conn
= SqlFactory::createConnection((SqlApiImplType
)type
);
143 if (gateway
) type
= CSqlNetworkGateway
;
144 else type
= CSqlNetwork
;
145 conn
= SqlFactory::createConnection(type
);
146 SqlNwConnection
*con
= (SqlNwConnection
*)conn
;
147 con
->setHost(hostname
, atoi(port
));
150 if (gateway
) type
= CSqlGateway
;
152 if (exclusive
) type
=CSqlDirect
; else type
= CSql
;
154 conn
= SqlFactory::createConnection(type
);
157 rv
= conn
->connect(username
,password
);
158 if (rv
!= OK
) return 1;
160 SqlConnection
*sCon
= (SqlConnection
*) conn
;
161 rv
= sCon
->getExclusiveLock();
168 aconStmt
= SqlFactory::createStatement(type
);
170 SqlStatement *sqlStmt = (SqlStatement*)aconStmt;
171 sqlStmt->setLoading(true);
173 aconStmt
->setConnection(conn
);
174 //rv = conn->beginTrans(READ_COMMITTED, TSYNC);
175 rv
= conn
->beginTrans();
176 if (rv
!= OK
) return 2;
177 while (getInput(fileFlag
) == true) continue;
179 //TODO::conn should provide method telling status of the transaction.
180 //if running, then abort it
182 if (filename
[0] !='\0')
191 bool handleTransaction(char *st
)
193 while (isspace (*st
)|| *st
== '(' ) st
++; // Skip white spaces
194 if (strcasecmp (st
, "COMMIT;") == 0 ||
195 strcasecmp (st
, "commit;") == 0 )
198 ListIterator it
= sqlStmtList
.getIterator();
199 while(it
.hasElement()) {
200 stmt
= (AbsSqlStatement
*) it
.nextElement();
205 //conn->beginTrans(isoLevel, TSYNC);
206 conn
->beginTrans(isoLevel
);
209 else if (strcasecmp (st
, "ROLLBACK;") == 0||
210 strcasecmp (st
, "rollback;") == 0)
213 ListIterator it
= sqlStmtList
.getIterator();
214 while(it
.hasElement()) {
215 stmt
= (AbsSqlStatement
*)it
.nextElement();
220 //conn->beginTrans(isoLevel, TSYNC);
221 conn
->beginTrans(isoLevel
);
224 else if (strcasecmp (st
, "SET AUTOCOMMIT ON;") == 0)
226 autocommitmode
= true;
227 if (!silent
) printf("AUTOCOMMIT Mode is set to ON\n");
230 else if (strcasecmp (st
, "SET AUTOCOMMIT OFF;") == 0)
232 autocommitmode
= false;
233 if (!silent
) printf("AUTOCOMMIT Mode is set to OFF\n");
236 else if (strcasecmp (st
, "SET ISOLATION LEVEL UNCOMMITTED;") == 0)
238 isoLevel
= READ_UNCOMMITTED
;
239 printf("Isolation Level is set to READ_UNCOMMITTED\n");
242 else if (strcasecmp (st
, "SET ISOLATION LEVEL COMMITTED;") == 0)
244 isoLevel
= READ_COMMITTED
;
245 printf("Isolation Level is set to READ_COMMITTED\n");
248 else if (strcasecmp (st
, "SET ISOLATION LEVEL REPEATABLE;") == 0)
250 isoLevel
= READ_REPEATABLE
;
251 printf("Isolation Level is set to READ_REPEATABLE\n");
254 else if (strcasecmp(st
, "SET TIMER ON;") == 0)
255 { isTimer
=true; return true; }
256 else if (strcasecmp(st
, "SET TIMER OFF;") == 0)
257 { isTimer
=false; return true; }
260 bool handleEchoAndComment(char *st
)
262 while (isspace (*st
)|| *st
== '(' ) st
++; // Skip white spaces
263 if (strncasecmp (st
, "ECHO", 4) == 0)
267 }else if (strncmp(st
, "--", 2) == 0)
270 }else if (strncasecmp(st
, "help", 2) == 0)
274 }else if(strcasecmp(st
,"show variables;")==0){
278 }else if (strcasecmp(st
, "show tables;") == 0) {
280 List tableList
= aconStmt
->getAllTableNames(rv
);
281 ListIterator iter
= tableList
.getIterator();
282 Identifier
*elem
= NULL
;
284 printf("=============TableNames===================\n");
286 while (iter
.hasElement())
288 elem
= (Identifier
*) iter
.nextElement();
290 printf(" %s \n", elem
->name
);
292 if (count
==0) printf(" No tables exist\n");
293 printf("==========================================\n");
295 }else if( strcasecmp(st
, "show users;" )==0){
297 List userList
= aconStmt
->getAllUserNames(rv
);
298 ListIterator iter
= userList
.getIterator();
299 Identifier
*elem
= NULL
;
301 printf("=============UserNames===================\n");
302 while (iter
.hasElement())
304 elem
= (Identifier
*) iter
.nextElement();
305 if(0==strcmp( elem
->name
,I_USER
))
307 printf(" %s \n", elem
->name
);
309 printf("=========================================\n");
316 printf("CSQL Command List\n");
317 printf("======================================================\n");
318 printf("SHOW TABLES\n");
319 printf("SET AUTOCOMMIT ON | OFF\n");
320 printf("COMMIT | ROLLBACK\n");
321 printf("SET ISOLATION LEVEL UNCOMMITTED | COMMITTED | REPEATABLE\n");
322 printf("CREATE TABLE | INDEX ...\n");
323 printf("DROP TABLE | INDEX ...\n");
324 printf("INSERT ...\n");
325 printf("UPDATE ...\n");
326 printf("DELETE ...\n");
327 printf("SELECT ...\n");
329 printf("======================================================\n");
331 void printVariables()
333 //SERVER Section Info.
334 printf("=======================================================\n");
335 printf("| SERVER SECTION INFORMATION |\n");
336 printf("=======================================================\n");
337 printf(" SITE_ID\t\t\t= %d\n",Conf::config
.getSiteID());
338 printf(" PAGE_SIZE\t\t\t= %d Byte\n",Conf::config
.getPageSize());
339 printf(" MAX_PROCS\t\t\t= %d\n",Conf::config
.getMaxProcs());
340 printf(" MAX_SYS_DB_SIZE\t\t= %d MB\n",(Conf::config
.getMaxSysDbSize()/1048576));
341 printf(" MAX_DB_SIZE\t\t\t= %d MB\n",(Conf::config
.getMaxDbSize()/1048576));
342 printf(" SYS_DB_KEY\t\t\t= %d\n",Conf::config
.getSysDbKey());
343 printf(" USER_DB_KEY\t\t\t= %d\n",Conf::config
.getUserDbKey());
344 printf(" LOG_LEVEL\t\t\t= %d\n",Conf::config
.getLogLevel());
345 printf(" MAP_ADDRESS\t\t\t= %ld\n",Conf::config
.getMapAddress());
347 if(Conf::config
.useDurability())
348 printf(" DURABILITY\t\t\t= True\n");
350 printf(" DURABILITY\t\t\t= False\n");
352 if(Conf::config
.useMmap())
353 printf(" MMAP\t\t\t\t= True\n");
355 printf(" MMAP\t\t\t\t= False\n");
357 printf(" DURABLE_MODE\t\t\t= %d\n",Conf::config
.getDurableMode());
358 printf(" DATABASE_FILE\t\t\t= %s\n",Conf::config
.getDbFile());
359 printf(" LOG_FILE_PATH\t\t\t= %s\n",Conf::config
.getLogFile());
360 printf(" STDERR_FILE\t\t\t= %s\n",Conf::config
.getStderrFile());
362 //Network Section Information
363 printf("=======================================================\n");
364 printf("| NETWORK SECTION INFORMATION |\n");
365 printf("=======================================================\n");
367 if(Conf::config
.useCsqlSqlServer())
368 printf(" CSQL_SQL_SERVER\t\t= True\n");
370 printf(" CSQL_SQL_SERVER\t\t= False\n");
372 printf(" PORT\t\t\t\t= %d\n",Conf::config
. getPort());
373 printf(" NETWORK_RESPONSE_TIMEOUT\t= %d\n",Conf::config
.getNetworkResponseTimeout());
374 printf(" NETWORK_CONNECT_TIMEOUT\t= %d\n",Conf::config
.getNetworkConnectTimeout());
375 printf(" ID_SHM_KEY\t\t\t= %d\n",Conf::config
.getShmIDKey());
377 //Client section variables
378 printf("=======================================================\n");
379 printf("| CLIENT SECTION INFORMATION |\n");
380 printf("=======================================================\n");
381 printf(" MUTEX_TIMEOUT_SECS\t\t= %d\n",Conf::config
.getMutexSecs());
382 printf(" MUTEX_TIMEOUT_USECS\t\t= %d\n",Conf::config
.getMutexUSecs());
383 printf(" MUTEX_TIMEOUT_RETRIES\t\t= %d\n",Conf::config
.getMutexRetries());
384 printf(" LOCK_TIMEOUT_SECS\t\t= %d\n",Conf::config
.getLockSecs());
385 printf(" LOCK_TIMEOUT_USECS\t\t= %d\n",Conf::config
.getLockUSecs());
386 printf(" LOCK_TIMEOUT_RETRIES\t\t= %d\n",Conf::config
.getLockRetries());
388 //CACHE Section variables
389 printf("=======================================================\n");
390 printf("| CACHE SECTION INFORMATION |\n");
391 printf("=======================================================\n");
393 if(Conf::config
.useCache())
394 printf(" CACHE_TABLE\t\t\t= True\n");
396 printf(" CACHE_TABLE\t\t\t= False\n");
398 printf(" DSN\t\t\t\t= %s\n",Conf::config
.getDSN());
400 if(Conf::config
.useTwoWayCache())
401 printf(" ENABLE_BIDIRECTIONAL_CACHE\t= True\n");
403 printf(" ENABLE_BIDIRECTIONAL_CACHE\t= False\n");
405 printf(" CACHE_RECEIVER_WAIT_SECS\t= %d\n",Conf::config
.getCacheWaitSecs());
407 if(Conf::config
.getCacheMode()==0)
408 printf(" CACHE_MODE\t\t\t= SYNC\n");
409 else if (Conf::config
.getCacheMode()==1)
410 printf(" CACHE_MODE\t\t\t= ASYNC\n");
412 printf(" CACHE_MODE\t\t\t= Unknown\n");
414 printf(" DS_CONFIG_FILE\t\t\t= %s\n",Conf::config
.getDsConfigFile());
415 printf(" TABLE_CONFIG_FILE\t\t= %s\n",Conf::config
.getTableConfigFile());
416 printf(" MSG_KEY\t\t\t= %d\n",Conf::config
.getMsgKey() );
417 printf(" ASYNC_MSGMAX\t\t\t= %d\n",Conf::config
.getAsyncMsgMax());
418 printf(" MAX_QUEUE_LOGS\t\t\t= %d\n",Conf::config
.getLogLevel());
419 printf(" CONFL_RESOL_FILE\t\t= %s\n",Conf::config
.getConflResoFile());
420 printf(" \nNOTE: To modify above variables, You will be following 'csql.conf' file.\n\n");
423 void setStmtType(char *st
)
425 if (strncasecmp (st
, "SELECT", 6) == 0) {stmtType
=SELECT
; return; }
426 else if (strncasecmp (st
, "EXPLAIN", 6) == 0) {stmtType
=EXPLAIN
; return; }
427 else if (strncasecmp (st
, "CREATE", 6) == 0) {stmtType
=DDL
; return; }
428 else if (strncasecmp (st
, "DROP", 4) == 0) { stmtType
=DDL
; return; }
433 char getQueryFromStdIn(char *buf
)
435 char c
, *bufBegin
=buf
;
439 while( (c=(char ) getchar()) != EOF && c != ';')
441 *buf++ = c; charCnt++;
442 if(c=='\n') //printf("%1d>",ln++);
444 if( charCnt == SQL_STMT_LEN ) {
445 printf("SQL Statement length is greater than %d. "
446 "Ignoring the statement.\n", SQL_STMT_LEN );
457 char *line
= readline("CSQL>");
458 if (line
) {strcpy(buf
, line
); add_history(line
); }
459 else { free(line
); return EOF
; }
463 char getQueryFromFile(char *buf
)
465 char c
, *bufBegin
=buf
;
467 while( (c
=(char ) fgetc(fp
)) != EOF
&& c
!= ';')
469 *buf
++ = c
; charCnt
++;
470 if( charCnt
== SQL_STMT_LEN
) {
471 printf("SQL Statement length is greater than %d. "
472 "Ignoring the statement.\n", SQL_STMT_LEN
);
483 bool getInput(bool fromFile
)
485 char buffer
[SQL_STMT_LEN
+ 1];
488 if (fromFile
== false)
489 eof
= getQueryFromStdIn(buffer
);
491 eof
= getQueryFromFile(buffer
);
494 while(*buf
== ' ' || *buf
== '\t' || *buf
== '\n') buf
++;
495 if (eof
== EOF
|| strncasecmp (buf
, "quit", 4) == 0)
497 if (handleTransaction(buf
)) return true;
498 if (handleEchoAndComment(buf
)) return true;
499 if ( *buf
== ';' ) return true; // Null statement.
504 if (autocommitmode
) {
505 if (firstPrepare
) aconStmt
->free();
506 rv
= aconStmt
->prepare(buf
);
508 printf("Statement prepare failed with error %d\n", rv
);
515 stmt
= SqlFactory::createStatement(type
);
516 stmt
->setConnection(conn
);
517 rv
= stmt
->prepare(buf
);
519 printf("Statement prepare failed with error %d\n", rv
);
522 sqlStmtList
.append(stmt
);
524 if (noUndolog
|| exclusive
){
525 SqlStatement
*sqlStmt
= (SqlStatement
*)stmt
;
526 sqlStmt
->setLoading(true);
530 rv
= stmt
->execute(rows
);
533 printf("Statement execute failed with error %d\n", rv
);
534 if (autocommitmode
) {
537 firstPrepare
= false; //set this so that it does not free again
538 conn
->beginTrans(isoLevel
);
542 sqlStmtList
.remove(stmt
);
543 delete stmt
; stmt
= NULL
;
548 if (stmtType
== OTHER
&& stmt
->isSelect()) stmtType
= SELECT
;
549 if (stmtType
== OTHER
)
551 if (!silent
) printf("Statement Executed: Rows Affected = %d\n", rows
);
553 else if (stmtType
== EXPLAIN
)
557 else if (stmtType
== DDL
)
559 if (!silent
) printf("Statement Executed\n");
563 FieldInfo
*info
= new FieldInfo();
564 printf("---------------------------------------------------------\n");
566 for (int i
= 0 ; i
< stmt
->noOfProjFields() ; i
++)
568 stmt
->getProjFldInfo(i
+1, info
);
569 printf("%s\t", info
->fldName
);
571 printf("\n---------------------------------------------------------\n");
578 tuple
= (char*)stmt
->fetchAndPrint(false);
580 if (tuple
== NULL
) { break; }
585 if (isTimer
) printf("Time taken: %lld microsecs\n", timer
.sum()/1000);
589 //conn->beginTrans(isoLevel, TSYNC);
591 firstPrepare
= false; //set this so that it does not free again
592 conn
->beginTrans(isoLevel
);
598 int getConnType(int opt
)
601 case 1: { printf("Local CSql\n"); return (int) CSql
; }
602 case 2: { printf("Local Adapter\n"); return (int) CSqlAdapter
; }
603 case 3: { printf("Local Gateway\n"); return (int) CSqlGateway
; }
604 case 4: { printf("Network CSql\n"); return (int) CSqlNetwork
; }
605 case 5: { printf("Network Adapter\n"); return (int) CSqlNetworkAdapter
;}
606 case 6: { printf("Network Gateway\n"); return (int) CSqlNetworkGateway
;}
607 case 7: { printf("Log\n"); return (int) CSqlLog
; }
608 case 8: { printf("Log\n"); return (int) CSqlLog
; }