aligning code formatting
[csql.git] / src / cache / CacheTableLoader.cxx
blob401d4ea8f859acab987948b9e37154f15fc99b73
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<CacheTableLoader.h>
18 #include<TableConfig.h>
19 #include<Util.h>
20 #include<SqlConnection.h>
21 #include<SqlLogConnection.h>
22 #include<SqlStatement.h>
23 #include<SqlFactory.h>
25 DbRetVal CacheTableLoader::load(bool tabDefinition)
27 AbsSqlConnection *conn = SqlFactory::createConnection(CSqlLog);
28 DbRetVal rv = conn->connect(userName, password);
29 if (rv != OK) { delete conn; return ErrSysInit; }
30 AbsSqlStatement *stmt = SqlFactory::createStatement(CSqlLog);
31 stmt->setConnection(conn);
32 SqlLogConnection *logConn = (SqlLogConnection *) conn;
33 logConn->setNoMsgLog(true);
34 logConn->setNoOfflineLog(true);
35 SqlConnection *con = (SqlConnection *) conn->getInnerConnection();
36 DatabaseManager *dbMgr = con->getConnObject().getDatabaseManager();
37 dbMgr->setCanTakeCheckPoint(false);
38 if (tabDefinition == false) {
39 Table *tbl = dbMgr->openTable(tableName);
40 if (tbl == NULL) {
41 dbMgr->setCanTakeCheckPoint(true);
42 conn->disconnect();
43 delete stmt;
44 delete conn;
45 return ErrNotExists;
47 if (tbl->numTuples()) {
48 printError(ErrNotEmpty, "The table '\%s\' is not empty", tableName);
49 dbMgr->closeTable(tbl);
50 dbMgr->setCanTakeCheckPoint(true);
51 conn->disconnect();
52 delete stmt;
53 delete conn;
54 return ErrNotEmpty;
56 dbMgr->closeTable(tbl);
58 conn->beginTrans();
59 rv = load(conn, stmt, tabDefinition);
60 conn->commit();
61 stmt->free();
62 dbMgr->setCanTakeCheckPoint(true);
63 conn->disconnect();
64 delete stmt;
65 delete conn;
66 return rv;
69 DbRetVal CacheTableLoader::load(AbsSqlConnection *conn, AbsSqlStatement *stmt, bool tabDefinition)
71 char dsn[IDENTIFIER_LENGTH];
72 TDBInfo tdbName = mysql;
73 DbRetVal rv = OK;
75 bool isDSNExist = resolveForDSN(dsn, tdbName, rv);
76 if (!isDSNExist) return rv;
78 SqlConnection *con = (SqlConnection *) conn->getInnerConnection();
79 DatabaseManager *dbMgr = con->getConnObject().getDatabaseManager();
81 SQLCHAR outstr[1024];
82 SQLSMALLINT outstrlen;
83 int retValue =0;
84 SQLHENV henv;
85 SQLHDBC hdbc;
86 SQLHSTMT hstmt;
87 retValue = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
88 if (retValue) {
89 printError(ErrSysInit, "Unable to allocate ODBC handle \n");
90 return ErrSysInit;
92 SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
93 retValue = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
94 if (retValue) {
95 printError(ErrSysInit, "Unable to allocate ODBC handle \n");
96 return ErrSysInit;
98 retValue = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)dsn, SQL_NTS,
99 outstr, sizeof(outstr), &outstrlen,
100 SQL_DRIVER_NOPROMPT);
101 if (SQL_SUCCEEDED(retValue)) {
102 printDebug(DM_Gateway, "Connected to target database using dsn = %s\n", dsn);
103 } else {
104 printError(ErrSysInit, "Failed to connect to target database\n");
105 return ErrSysInit;
108 retValue=SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
109 if (retValue) {
110 printError(ErrSysInit, "Unable to allocate ODBC handle \n");
111 return ErrSysInit;
114 char stmtBuf[1024];
115 generateCacheTableStatement(stmtBuf);
116 retValue = SQLPrepare (hstmt, (unsigned char *) stmtBuf, SQL_NTS);
117 if (retValue) {
118 printError(ErrSysInit, "Unable to Prepare ODBC statement \n");
119 return ErrSysInit;
121 int nRecordsToFetch = Conf::config.getNoOfRowsToFetchFromTDB();
122 int nFetchedRecords = 0;
123 SQLUSMALLINT *rowStatus = (SQLUSMALLINT *)
124 malloc(nRecordsToFetch * sizeof(SQLUSMALLINT));
125 memset(rowStatus, 0, nRecordsToFetch * sizeof(SQLUSMALLINT));
126 SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, SQL_BIND_BY_COLUMN, 0);
127 SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (void *) nRecordsToFetch, 0);
128 SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, rowStatus, 0 );
129 SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &nFetchedRecords, 0 );
130 if (tabDefinition) {
131 short totalFields=0;
132 retValue = SQLNumResultCols (hstmt, &totalFields);
133 if (retValue) {
134 printError(ErrSysInit, "Unable to retrieve ODBC total columns\n");
135 return ErrSysInit;
137 logFinest(Conf::logger, "Cache Table noOfFields %hd", totalFields);
138 UWORD icol=1;
139 UCHAR colName[IDENTIFIER_LENGTH];
140 SWORD colNameMax=0;
141 SWORD nameLength=0;
142 SWORD colType=0;
143 SQLULEN colLength = 0;
144 SWORD scale=0;
145 SWORD nullable=0;
146 colNameMax = IDENTIFIER_LENGTH;
147 char columnname[IDENTIFIER_LENGTH];
148 short type; short unique;
149 SQLHSTMT hstmtmeta;
150 retValue=SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmtmeta);
151 if (retValue)
153 printError(ErrSysInit, "Unable to allocate ODBC handle \n");
154 return ErrSysInit;
157 char crtIdxStmt[1024];
158 char *ptr=crtIdxStmt;
159 HashIndexInitInfo *inf = new HashIndexInitInfo();
160 bool isPriIndex = prepareCreateIndexStatement(hstmtmeta, crtIdxStmt, tdbName, inf);
162 bool iskeyfieldExist=false;
163 bool isPKFieldSpecified = false;
164 char *name = NULL;
165 if((strcmp(fieldName,"")!=0) && (strcmp(fieldName,"NULL")!=0) )
167 isPKFieldSpecified = true;
169 if ( isPriIndex && ( strcmp(fieldlistVal,"")!=0 ) &&
170 ( strcmp(fieldlistVal,"NULL") != 0 )) {
171 inf->list.resetIter();
172 while ( (name=inf->list.nextFieldName()) != NULL) {
173 iskeyfieldExist = TableConf::config.isFieldExist(name);
174 if(!iskeyfieldExist) { break; }
176 } else if (isPriIndex) { iskeyfieldExist = true; }
177 if ( isPKFieldSpecified && !(TableConf::config.isFieldExist(fieldName)) )
179 if ( Conf::config.useTwoWayCache() &&
180 (strcmp(fieldlistVal,"")!=0) &&
181 (strcmp(fieldlistVal,"NULL")!=0))
183 printError(ErrSysInit, "Bidirectional caching should have primary key in %s \n", tableName);
184 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
185 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
186 SQLDisconnect (hdbc);
187 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
188 SQLFreeHandle (SQL_HANDLE_ENV, henv);
189 delete inf;
190 return ErrSysInit;
193 if (!iskeyfieldExist && !isPKFieldSpecified )
195 if(Conf::config.useTwoWayCache())
197 printError(ErrSysInit, "Bidirectional caching fail for no primary key in %s \n", tableName);
198 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
199 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
200 SQLDisconnect (hdbc);
201 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
202 SQLFreeHandle (SQL_HANDLE_ENV, henv);
203 delete inf;
204 return ErrSysInit;
208 bool isKeyFld=false;
209 char crtTblStmt[1024];
210 rv = prepareCreateTableStatement(crtTblStmt, hstmt, inf, totalFields,
211 tdbName, isKeyFld);
212 if (rv != OK) {
213 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
214 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
215 SQLDisconnect (hdbc);
216 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
217 SQLFreeHandle (SQL_HANDLE_ENV, henv);
218 delete inf;
219 return ErrSysInit;
221 //printf("table stmt '%s'\n", crtTblStmt);
222 if(((strcmp(fieldName,"")!=0) && (strcmp(fieldName,"NULL")!=0))
223 && !isKeyFld) {
224 printError(ErrSysInit, "Unable to cache Table for %s with key field %s\n", tableName,fieldName);
225 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
226 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
227 SQLDisconnect (hdbc);
228 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
229 SQLFreeHandle (SQL_HANDLE_ENV, henv);
230 delete inf;
231 return ErrSysInit;
233 rv = stmt->prepare(crtTblStmt);
234 if (rv != OK) {
235 printError(ErrSysInit, "Unable to prepare create table stmt\n");
236 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
237 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
238 SQLDisconnect (hdbc);
239 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
240 SQLFreeHandle (SQL_HANDLE_ENV, henv);
241 delete inf;
242 return ErrSysInit;
244 int rows = 0;
245 rv = stmt->execute(rows);
246 if (rv != OK) {
247 printError(ErrSysInit, "Unable to execute create table stmt\n");
248 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
249 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
250 SQLDisconnect (hdbc);
251 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
252 SQLFreeHandle (SQL_HANDLE_ENV, henv);
253 delete inf;
254 return ErrSysInit;
256 logFinest(Conf::logger, "Cache Table: Table Created :%s", crtTblStmt);
258 //Table is created.
259 //Create primary key index if present
260 if (isPriIndex && ( iskeyfieldExist ||
261 (strcmp(fieldlistVal,"")==0 || strcmp(fieldlistVal,"NULL")== 0))) {
262 rv = stmt->prepare(crtIdxStmt);
263 if (rv != OK) {
264 printError(ErrSysInit, "Unable to prepare create table stmt\n");
265 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
266 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
267 SQLDisconnect (hdbc);
268 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
269 SQLFreeHandle (SQL_HANDLE_ENV, henv);
270 delete inf;
271 return ErrSysInit;
273 int rows = 0;
274 rv = stmt->execute(rows);
275 if (rv != OK) {
276 printError(ErrSysInit, "Unable to execute create table stmt\n");
277 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
278 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
279 SQLDisconnect (hdbc);
280 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
281 SQLFreeHandle (SQL_HANDLE_ENV, henv);
282 delete inf;
283 return ErrSysInit;
285 //printf("Primary index created from create Index stmt\n");
287 retValue = SQLCloseCursor(hstmtmeta);
288 rv = createIndex(hstmtmeta, tableName, inf, stmt,isPKFieldSpecified);
289 if(rv!=OK) {
290 dbMgr->dropTable(tableName);
291 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
292 SQLDisconnect (hdbc);
293 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
294 SQLFreeHandle (SQL_HANDLE_ENV, henv);
295 delete inf;
296 return rv;
298 logFinest(Conf::logger, "Cache Table: Index :%s", crtIdxStmt);
299 delete inf;
300 } // tableDefinition scope finishes here
302 else { /***Checking for Table Schema between CSQL and TDB(cachetable -s option)***/
303 rv=checkingSchema(hdbc,hstmt,conn,stmt,tdbName);
304 if(rv != OK){
305 printError(ErrSysInit,"Unable to cache the '%s' table due to schema mismatched.",tableName);
306 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
307 SQLDisconnect (hdbc);
308 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
309 SQLFreeHandle (SQL_HANDLE_ENV, henv);
310 return ErrSysInit;
312 } //***Ends Here
314 // Now load the table with records
315 stmt->free();
317 char insStmt[1024];
318 List fNameList;
319 fNameList.init();
320 SqlStatement *sqlStmt = (SqlStatement *)stmt->getInnerStatement();
321 sqlStmt->setConnection(con);
322 prepareInsertStatement(sqlStmt, &fNameList, insStmt);
323 int totalFields = fNameList.size();
324 rv = stmt->prepare(insStmt);
325 if (rv != OK) {
326 printError(ErrSysInit, "Unable to prepare create table stmt\n");
327 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
328 SQLDisconnect (hdbc);
329 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
330 SQLFreeHandle (SQL_HANDLE_ENV, henv);
331 return ErrSysInit;
333 sqlStmt->setLoading(true);
334 ListIterator fNameIter = fNameList.getIterator();
335 FieldInfo *info = new FieldInfo();
336 int fcount =1; void *valBuf=NULL;
337 Identifier *elem = NULL;
338 void *tembuf=NULL;//For postgre BigInt type
339 BindBuffer *bBuf;
340 List valBufList;
341 int i=0;
342 while (fNameIter.hasElement()) {
343 elem = (Identifier*) fNameIter.nextElement();
344 sqlStmt->getFieldInfo(tableName, (const char*)elem->name, info);
345 int size = 0;
346 if (info->type == typeString || info->type == typeVarchar) {
347 size = nRecordsToFetch * AllDataType::size(info->type,info->length);
348 } else {
349 size = nRecordsToFetch * AllDataType::size(info->type);
351 valBuf = malloc(size);
352 os::memset(valBuf,0,size);
353 int bindLen = 0;
354 if (info->type != typeDate && info->type != typeTime && info->type != typeTimeStamp) {
355 if (info->type == typeLongLong && tdbName == postgres)
356 bindLen = 40;
357 else
358 bindLen = AllDataType::size(info->type,
359 AllDataType::size(info->type, info->length));
360 } else {
361 switch(info->type) {
362 case typeDate: bindLen = sizeof(DATE_STRUCT); break;
363 case typeTime: bindLen = sizeof(TIME_STRUCT); break;
364 case typeTimeStamp: bindLen = sizeof(TIMESTAMP_STRUCT); break;
368 bBuf = (BindBuffer *) SqlStatement::fillBindBuffer(tdbName, info->type, valBuf, bindLen, nRecordsToFetch);
369 valBufList.append(bBuf);
370 retValue = SQLBindCol (hstmt, fcount, AllDataType::convertToSQL_C_Type(info->type,tdbName), valBuf, bindLen, bBuf->nullData);
371 fcount++;
372 if (retValue) {
373 if(tabDefinition) dbMgr->dropTable(tableName);
374 printError(ErrSysInit, "Unable to bind columns in ODBC\n");
375 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
376 SQLDisconnect (hdbc);
377 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
378 SQLFreeHandle (SQL_HANDLE_ENV, henv);
379 return ErrSysInit;
382 delete info;
383 fNameIter.reset();
384 while (fNameIter.hasElement())
385 delete ((FieldName *) fNameIter.nextElement());
386 fNameList.reset();
388 retValue = SQLExecute (hstmt);
389 if (retValue) {
390 printError(ErrSysInit, "Unable to execute ODBC statement\n");
391 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
392 SQLDisconnect (hdbc);
393 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
394 SQLFreeHandle (SQL_HANDLE_ENV, henv);
395 return ErrSysInit;
397 int fldpos=0;
398 do {
399 //TODO: if SQLFetch return other than record not found error
400 //it should drop the table
401 retValue = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
402 if (retValue) break;
403 for (int row = 0; row < nFetchedRecords; row++) {
404 fldpos = 0;
405 void *val = NULL;
406 ListIterator bindIter = valBufList.getIterator();
407 while (bindIter.hasElement()) {
408 bBuf = (BindBuffer*) bindIter.nextElement();
409 if (bBuf->nullData[row] == SQL_NULL_DATA) {
410 stmt->setNull(fldpos+1);
411 } else {
412 val = (void *) ((char *)bBuf->csql + row * bBuf->length);
413 switch (bBuf->type) {
414 case typeString:
416 val = (void *) ((char *)bBuf->csql + row * bBuf->length);
417 if( tdbName == postgres)
418 Util::trimRight((char*)val);
419 break;
421 case typeDate:
423 val = (void *) ((char *)bBuf->csql + row * sizeof(Date));
424 Date *dtCSQL = (Date*) val;
425 void *tVal = (void *) ((char *)bBuf->targetdb + row * sizeof(DATE_STRUCT));
426 DATE_STRUCT *dtTarget = (DATE_STRUCT*) tVal;
427 dtCSQL->set(dtTarget->year,dtTarget->month,dtTarget->day);
428 break;
430 case typeTime:
432 val = (void *) ((char *)bBuf->csql + row * sizeof(Time));
433 Time *dtCSQL = (Time*) val;
434 void *tVal = (void *) ((char *)bBuf->targetdb + row * sizeof(TIME_STRUCT));
435 TIME_STRUCT *dtTarget = (TIME_STRUCT*) tVal;
436 dtCSQL->set(dtTarget->hour,dtTarget->minute,dtTarget->second);
437 break;
439 case typeTimeStamp:
441 val = (void *) ((char *)bBuf->csql + row * sizeof(TimeStamp));
442 TimeStamp *dtCSQL = (TimeStamp*) val;
443 void *tVal = (void *) ((char *)bBuf->targetdb + row * sizeof(TIMESTAMP_STRUCT));
444 TIMESTAMP_STRUCT *dtTarget = (TIMESTAMP_STRUCT*) tVal;
445 dtCSQL->setDate(dtTarget->year,dtTarget->month,dtTarget->day);
446 dtCSQL->setTime(dtTarget->hour,dtTarget->minute,dtTarget->second, dtTarget->fraction);
447 break;
449 case typeLongLong:
451 val = (void *) ((char *)bBuf->csql + row * sizeof(long long));
452 void *tVal = (void *) ((char *)bBuf->targetdb + row * bBuf->length);
453 if (tdbName == postgres) {
454 sscanf((const char*)tVal,"%lld",(long long*) val);
456 break;
459 SqlStatement::setParamValues(stmt, fldpos+1, bBuf->type, bBuf->length, val);
461 fldpos++;
463 int rows = 0;
464 rv = stmt->execute(rows);
465 if (rv != OK) {
466 printError(ErrSysInit, "Unable to cache record in CSQL.\n");
467 if(tabDefinition) dbMgr->dropTable(tableName);
468 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
469 SQLDisconnect (hdbc);
470 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
471 SQLFreeHandle (SQL_HANDLE_ENV, henv);
472 return ErrSysInit;
474 conn->commit();
475 conn->beginTrans();
477 } while (SQL_SUCCEEDED(retValue) && nFetchedRecords == nRecordsToFetch);
478 conn->commit();
479 conn->beginTrans();
481 //PRABA::one operation per transaction gives the best
482 //performance than 100 /Txn in case of durability
483 //TODO::leak:: valBufList and its targetdb buffer
484 ListIterator it = valBufList.getIterator();
485 while(it.hasElement()) {
486 BindBuffer *bb = (BindBuffer *) it.nextElement();
487 if (bb->csql) { free(bb->csql); bb->csql = NULL; }
488 if (bb->targetdb) { free(bb->targetdb); bb->targetdb = NULL; }
489 delete bb; bb = NULL;
491 valBufList.reset();
492 SQLCloseCursor (hstmt);
493 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
494 ::free(rowStatus);
495 SQLDisconnect (hdbc);
496 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
497 SQLFreeHandle (SQL_HANDLE_ENV, henv);
498 logFine(Conf::logger, "Cached Table: %s", tableName);
499 return OK;
502 DbRetVal CacheTableLoader::reload()
504 FILE *fp=NULL;
505 DbRetVal rv = unload(false);
506 if (rv != OK) return rv;
507 //get table cache senarios
508 fp = fopen(Conf::config.getTableConfigFile(),"r");
509 if( fp == NULL ) {
510 printError(ErrSysInit, "csqltable.conf file does not exist");
511 return OK;
513 int mode;
514 rv = OK;
515 char tablename[IDENTIFIER_LENGTH];
516 char fieldname[IDENTIFIER_LENGTH];
517 char field[IDENTIFIER_LENGTH];
518 char condition[IDENTIFIER_LENGTH];
519 char dsnname[IDENTIFIER_LENGTH];
520 while(!feof(fp))
522 fscanf(fp, "%d %s %s %s %s %s\n", &mode, tablename,fieldname,condition,field,dsnname);
523 if(strcmp(tablename,tableName)==0) break;
525 fclose(fp);
526 setCondition(TableConf::config.getRealConditionFromFile(condition));
527 setFieldName(fieldname);
528 setFieldListVal(field);
529 setDsnName(dsnname);
530 rv = load(false);
531 return rv;
534 DbRetVal CacheTableLoader::unload(bool tabDefinition)
536 AbsSqlConnection *conn = SqlFactory::createConnection(CSqlLog);
537 DbRetVal rv = conn->connect(userName, password);
538 if (rv != OK) return ErrSysInit;
539 AbsSqlStatement *stmt = SqlFactory::createStatement(CSqlLog);
540 stmt->setConnection(conn);
541 SqlLogConnection *logConn = (SqlLogConnection *) conn;
542 logConn->setNoMsgLog(true);
543 char statement[1024];
544 if (TableConf::config.isTableCached(tableName) != OK) {
545 printError(ErrNotCached, "The table \'%s\' is not cached", tableName);
546 conn->disconnect();
547 delete stmt;
548 delete conn;
549 return ErrNotCached;
551 SqlConnection *con = (SqlConnection *) conn->getInnerConnection();
552 DatabaseManager *dbMgr = (DatabaseManager*) con->getConnObject().getDatabaseManager();
553 if (dbMgr == NULL) {
554 conn->disconnect();
555 delete stmt; delete conn;
556 printError(ErrSysInit, "Authentication failed\n");
557 return ErrSysInit;
559 if (!tabDefinition)
561 sprintf(statement, "DELETE FROM %s;", tableName);
562 SqlStatement *sqlStmt = (SqlStatement*)stmt;
563 sqlStmt->setLoading(true);
564 rv = stmt->prepare(statement);
565 if (rv != OK) {
566 conn->disconnect();
567 delete stmt; delete conn;
568 return ErrBadCall;
570 conn->beginTrans();
571 int rows = 0;
572 rv = stmt->execute(rows);
573 if (rv != OK) {
574 conn->disconnect();
575 delete stmt; delete conn;
576 return ErrBadCall;
578 conn->commit();
580 else
582 rv = TableConf::config.removeFromCacheTableFile();
583 if (rv != OK) {
584 conn->disconnect(); delete stmt; delete conn;
585 return ErrBadCall;
587 sprintf(statement, "DROP TABLE %s;", tableName);
588 SqlStatement *sqlStmt = (SqlStatement*)stmt;
589 sqlStmt->setLoading(true);
590 rv = stmt->prepare(statement);
591 if (rv != OK) {
592 //TableConf::config.addToCacheTableFile(false);
593 conn->disconnect();
594 delete stmt; delete conn;
595 return ErrBadCall;
597 int rows = 0;
598 rv = stmt->execute(rows);
599 if (rv != OK) {
600 //TableConf::config.addToCacheTableFile(false);
601 conn->disconnect(); delete stmt; delete conn;
602 return ErrBadCall;
605 conn->disconnect();
606 delete stmt; delete conn;
607 logFine(Conf::logger, "Unloaded Cached Table: %s", tableName);
608 return rv;
611 DbRetVal CacheTableLoader::refresh()
613 return OK;
616 DbRetVal CacheTableLoader::recoverAllCachedTables()
618 FILE *fp;
619 Connection conn;
620 DbRetVal rv = conn.open(userName, password);
621 if(rv !=OK) return ErrSysInit;
623 //Note: if connection is not open, configuration veriables may be incorrect
625 fp = fopen(Conf::config.getTableConfigFile(),"r");
626 if( fp == NULL ) {
627 printError(ErrSysInit, "csqltable.conf file does not exist");
628 conn.close();
629 return OK;
631 conn.close();
632 //TODO::take exclusive lock on database
633 char tablename[IDENTIFIER_LENGTH];
634 char fieldname[IDENTIFIER_LENGTH];
635 char condition[IDENTIFIER_LENGTH];
636 char field[IDENTIFIER_LENGTH];
637 char dsnname[IDENTIFIER_LENGTH];
639 int mode;
640 int scanItems=0;
641 rv = OK;
642 while(!feof(fp))
644 scanItems = fscanf(fp, "%d %s %s %s %s %s\n", &mode, tablename,fieldname,condition,field,dsnname);
645 if (scanItems != 6) {
646 tablename[0]='\0';
647 printf("There is no table to be cached.\n");
648 return OK;
650 if (!TableConf::config.isTableCached(mode)) continue;
651 printDebug(DM_Gateway, "Recovering Table from target db: %s\n", tablename);
652 setCondition(TableConf::config.getRealConditionFromFile(condition));
653 if( (strcmp(Conf::config.getDSN(),dsnname)!=0) ){
654 setDsnName(dsnname);
655 setTable(tablename);
656 setFieldName(fieldname);
657 setFieldListVal(field);
658 printf("Recovering table %s %s %s\n", tablename,condition,field);
659 rv = load();
660 if (rv != OK) { fclose(fp); return rv; }
661 } else {
662 setDsnName(Conf::config.getDSN());
663 setTable(tablename);
664 setFieldName(fieldname);
665 setFieldListVal(field);
666 printf("Recovering table %s %s %s\n", tablename,condition,field);
667 rv = load();
668 if (rv != OK) { fclose(fp); return rv; }
670 logFine(Conf::logger, "Recovering Table from target db:%s", tablename);
672 fclose(fp);
673 return OK;
676 DbRetVal CacheTableLoader::createIndex(SQLHSTMT hstmtmeta, char *tableName, HashIndexInitInfo *inf,AbsSqlStatement *stmt,bool isPKFieldSpecified)
678 bool isKeyFld= false;
679 int retValue = 0;
680 char columnname[IDENTIFIER_LENGTH];
681 char indexname[IDENTIFIER_LENGTH];
682 short type;
683 short unique;
684 char *name = NULL;
685 DbRetVal rv = OK;
686 retValue = SQLStatistics(hstmtmeta, NULL, 0, NULL, SQL_NTS,
687 (SQLCHAR*) tableName, SQL_NTS, SQL_INDEX_ALL, SQL_QUICK);
688 retValue = SQLBindCol(hstmtmeta, 4, SQL_C_SHORT,
689 &unique, 2, NULL);
690 retValue = SQLBindCol(hstmtmeta, 6, SQL_C_CHAR,
691 indexname, 129, NULL);
692 retValue = SQLBindCol(hstmtmeta, 7, SQL_C_SHORT,
693 &type, 2, NULL);
694 retValue = SQLBindCol(hstmtmeta, 9, SQL_C_CHAR,
695 columnname, 129,NULL);
696 List indexList;
697 bool isSecondTime = false;
698 CacheIndexInfo *info=NULL;
699 while ((retValue = SQLFetch(hstmtmeta)) == SQL_SUCCESS) {
700 printDebug(DM_Gateway, "Column: %-18s Index Name: %-18s unique:%hd type:%hd\n", columnname, indexname, unique, type);
702 if (type == 3) {
703 bool isFldAdd = false;
704 ListIterator iter = indexList.getIterator();
705 iter.reset();
706 while (iter.hasElement()) {
707 CacheIndexInfo *indInfo = (CacheIndexInfo *)iter.nextElement();
708 if(0 == strcmp( indInfo->indexName, indexname))
710 indInfo->fieldNameList.append(columnname);
711 isFldAdd = true;
714 if(!isFldAdd){
715 info = new CacheIndexInfo();
716 info->fieldNameList.append(columnname);
717 strcpy(info->indexName, indexname);
718 indexList.append(info);
719 isSecondTime = true;
723 ListIterator iter = indexList.getIterator();
724 iter.reset();
725 int noOfPkfield = inf->list.size();
726 char *fName=NULL;
727 char *cptr = NULL;
728 while (iter.hasElement()) {
729 cptr = columnname;
730 bool isFieldExistInCondition = false;
731 bool isPrimary=false;
732 CacheIndexInfo *indInfo = (CacheIndexInfo *)iter.nextElement();
733 int noOfFld= indInfo->fieldNameList.size();
734 indInfo->fieldNameList.resetIter();
735 while ((fName = indInfo->fieldNameList.nextFieldName())!=NULL) {
736 if(( 1 == noOfFld) && (0 == strcmp(fName,fieldName))) {
737 isKeyFld=true;
739 inf->list.resetIter();
740 while ((name=inf->list.nextFieldName())!=NULL)
742 if(0==strcmp(fName,name)) { isPrimary = true; break; }
743 isPrimary = false;
745 if (!TableConf::config.isFieldExist(fName) &&
746 ( (strcmp(fieldlistVal,"")!=0) &&
747 (strcmp(fieldlistVal,"NULL")!=0) )) {
748 isFieldExistInCondition =true;
749 continue;
751 sprintf(cptr, "%s ,",fName);
752 cptr += strlen(cptr);
754 if(isFieldExistInCondition) continue;
755 cptr -=1;
756 *cptr = '\0';
757 if (isPrimary) { continue; }
758 char crtIdxStmt[1024];
759 char indname[128];
760 sprintf(indname, "%s_%s", tableName, indInfo->indexName);
761 sprintf(crtIdxStmt, "CREATE INDEX %s on %s(%s) HASH SIZE 10007;", indname, tableName, columnname);
762 //printf("create index stmt \n'%s'\n", crtIdxStmt);
763 rv = stmt->prepare(crtIdxStmt);
764 if (rv != OK) {
765 printError(ErrSysInit, "Unable to prepare create table stmt\n");
766 return ErrSysInit;
768 int rows = 0;
769 rv = stmt->execute(rows);
770 if (rv != OK) {
771 printError(ErrSysInit, "Unable to execute create table stmt\n");
772 return ErrSysInit;
774 //delete indInfo;
775 }// while meta data fetch for index creation
777 iter.reset();
778 while (iter.hasElement()) delete (CacheIndexInfo *) iter.nextElement();
779 indexList.reset();
781 SQLCloseCursor (hstmtmeta);
782 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
783 if( !isKeyFld && isPKFieldSpecified) {
784 if(shouldForce) {
785 char frcIndStmt[1024];
786 char indname[128];
787 sprintf(indname, "%s_%s", tableName, "keyInd");
788 sprintf(frcIndStmt, "CREATE INDEX %s on %s(%s) HASH;", indname, tableName, fieldName);
789 rv = stmt->prepare(frcIndStmt);
790 if (rv != OK) {
791 printError(ErrSysInit, "Unable to prepare create table stmt\n");
792 return ErrSysInit;
794 int rows = 0;
795 rv = stmt->execute(rows);
796 if (rv != OK) {
797 printError(ErrSysInit, "Unable to execute create table stmt\n");
798 return ErrSysInit;
800 } else {
801 printError(ErrSysInit, "Unable to cache Table for %s with key field %s\n", tableName,fieldName);
802 return ErrSysInit;
805 return OK;
808 // Schema matching between CSQL and TDB table.(cachetable -t <tablename> -s)
809 DbRetVal CacheTableLoader::checkingSchema(SQLHDBC hdbc,SQLHSTMT hstmt, AbsSqlConnection *conn, AbsSqlStatement *stmt,TDBInfo tdbName)
811 DbRetVal rv=OK;
812 int noOfPrimaryKey=0;
813 int retValue=0;
814 int csqlFields=0;
816 SQLSMALLINT tdbFields=0;
817 SQLHSTMT hstmtmeta;
818 char columnname[IDENTIFIER_LENGTH];
820 UWORD icol=1;
821 UCHAR colName[IDENTIFIER_LENGTH];
822 SWORD colNameMax=0;
823 SWORD nameLength=0;
824 SWORD colType=0;
825 SQLULEN colLength = 0;
826 SWORD scale=0;
827 SWORD nullable=0;
828 colNameMax = IDENTIFIER_LENGTH;
830 SqlConnection *con = (SqlConnection *) conn->getInnerConnection();
831 DatabaseManager *dbMgr = con->getConnObject().getDatabaseManager();
833 SqlStatement *sqlStmt = (SqlStatement *)stmt->getInnerStatement();
834 sqlStmt->setConnection(con);
836 List fNameList ;
837 fNameList = sqlStmt->getFieldNameList(tableName, rv);
838 ListIterator fNameIter = fNameList.getIterator();
839 FieldInfo *info = new FieldInfo();
840 Identifier *elem = NULL;
842 retValue=SQLNumResultCols(hstmt, &tdbFields);
843 if(retValue) {
844 printError(ErrSysInit, "Unable to retrieve ODBC total columns.\n");
845 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
846 return ErrSysInit;
848 // CSQL Table fields
849 fNameList = sqlStmt->getFieldNameList(tableName, rv);
850 csqlFields = fNameList.size();
851 // noOfFields in both the database are same or not.
852 if(tdbFields!=csqlFields){
853 printError(ErrSysInit,"Number of fields between CSQL and TDB are not equal.");
854 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
855 return ErrSysInit;
857 retValue=SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmtmeta);
858 if(retValue){
859 printError(ErrSysInit, "Unable to allocate ODBC handle. \n");
860 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
861 return ErrSysInit;
863 retValue=SQLPrimaryKeys(hstmtmeta, NULL, SQL_NTS, NULL, SQL_NTS, (SQLCHAR*) tableName, SQL_NTS);
864 retValue = SQLBindCol(hstmtmeta, 4, SQL_C_CHAR,columnname, 129,NULL);
866 while(icol<=tdbFields){
867 retValue = SQLDescribeCol(hstmt, icol, colName, colNameMax,
868 &nameLength, &colType, &colLength,
869 &scale, &nullable);//TDB Field Name
870 if(retValue){
871 printError(ErrSysInit, "Unable to retrieve ODBC column info.\n");
872 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
873 return ErrSysInit;
875 Util::str_tolower((char*)colName);
876 elem = (Identifier*) fNameIter.nextElement();
877 sqlStmt->getFieldInfo(tableName, (const char*)elem->name, info);
878 char fldName[20];
879 int isNull;
880 int isPrimary;
881 rv = stmt->getParamFldInfo(icol,info);
882 char *name=(info->fldName);//Getting field name for CSQL table.
883 Util::str_tolower((char*)name);
884 if(strcmp(name,(char *)colName) != 0){ //Field name matching between CSQL and TDB.
885 printError(ErrSysInit,"CSQL's '%s' field did not match with TDB's '%s' field.\n",name,(char*)colName);
886 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
887 return ErrSysInit;
890 // DataType matching between CSQL and TDB
891 char ptr[IDENTIFIER_LENGTH]; ptr[0]='\0';
892 char ptr1[IDENTIFIER_LENGTH]; ptr1[0]='\0';
894 sprintf(ptr,"%s",AllDataType::getSQLString (AllDataType::convertFromSQLType(colType,colLength,scale,tdbName)));
895 sprintf(ptr1,"%s",AllDataType::getSQLString(info->type));//CSQL Type
896 if(strcmp(ptr,ptr1)!=0){
897 printError(ErrSysInit,"DataType did not match for '%s' field in CSQL.\n",name);
898 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
899 return ErrSysInit;
902 // Primary Key checking
903 bool tdbPKey=false;
904 if(SQLFetch( hstmtmeta ) == SQL_SUCCESS) tdbPKey=true;
905 if(tdbPKey && (!info->isPrimary))
906 printf("Warning: In CSQL, The %s's '%s' field should have Primery Key constraint.\n",tableName,name);
907 if((!tdbPKey) && info->isPrimary)
908 printf("Warning: In TDB, The %s's '%s' field should have Primary Key constraint.\n",tableName,colName);
910 // NotNull Checking
911 bool isCsqlNotNull=false;
912 bool isTdbNotNull=false;
913 if(tdbName==mysql){
914 if(info->isNull && nullable)
915 printf("Warning: In TDB, The %s's '%s' field should have a NOT NULL constraint.\n",tableName,colName);
916 if((!info->isNull) && (!nullable))
917 printf("Warning: In CSQL, The %s's '%s' field should have a NOT NULL constraint.\n",tableName,name);
919 icol++;
921 return OK;
924 DbRetVal CacheTableLoader::cacheAllTablesFromDs(char *dsnName,bool tableDefinition, bool isDirect,char *username, char *password)
926 char dsn[72];
927 DbRetVal rv = OK;
928 FILE *fp;
929 fp = fopen(Conf :: config.getDsConfigFile(),"r");
930 if(fp==NULL) {
931 printError(ErrSysInit, "csqlds.conf file does not exist");
932 return ErrSysInit;
934 char dsnId[IDENTIFIER_LENGTH]; dsnId[0]='\0';
935 char user[IDENTIFIER_LENGTH]; user[0] = '\0';
936 char passwd[IDENTIFIER_LENGTH]; passwd[0] = '\0';
937 char tdb[IDENTIFIER_LENGTH]; tdb[0]='\0';
938 unsigned int mode;
939 bool isCached=false;
941 // If -d option is disable, the If statementn will true.
942 if(strcmp(dsnName,"")==0) {
943 strcpy(dsnName, Conf::config.getDSN());
945 bool isDSNExist=false;
946 while(!feof(fp)) {
947 fscanf(fp,"%s %s %s %s\n",dsnId,user,passwd,tdb);
948 if(strcmp(dsnId,dsnName)==0) {
949 if( strcmp(user,"NULL")!=0 && strcmp(passwd,"NULL")!=0) {
950 sprintf(dsn,"DSN=%s;UID=%s;PWD=%s;",dsnName,user,passwd);
951 isDSNExist=true;
952 break;
953 }else{
954 sprintf(dsn,"DSN=%s;",dsnName);
955 isDSNExist=true;
956 break;
960 if(!isDSNExist) {
961 printError(ErrNotExists,"Entries is not present in the csqlds.conf file\n");
962 fclose(fp);
963 return ErrNotExists;
965 fclose(fp);
967 TDBInfo tdbName=mysql;
968 if (strcasecmp(tdb,"mysql") == 0) tdbName=mysql;
969 else if (strcasecmp(tdb,"postgres")==0) tdbName=postgres;
970 else printError(ErrNotFound,"Target Database Name is not properly set.Tdb name could be MySql and Postgres.\n");
971 logFine(Conf::logger, "TDB Name:%s\n", tdb);
973 // The ODBC section in intended to get all the tables from TDB,
974 // what SQLTables() is doing that.
976 SQLCHAR outstr[1024];
977 SQLSMALLINT outstrlen;
978 int retValue =0;
979 SQLHENV henv;
980 SQLHDBC hdbc;
981 SQLHSTMT hstmt;
982 SQLSMALLINT columns;
983 char table[IDENTIFIER_LENGTH][IDENTIFIER_LENGTH];
984 int counter=0;
985 char buf[IDENTIFIER_LENGTH];
986 int row = 0;
987 SQLINTEGER indicator[ 5 ];
988 int colPos;//Only to bind table name filed.
990 CacheTableLoader cacheLoader;
992 retValue = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
993 if (retValue) {
994 printError(ErrSysInit, "Unable to allocate ODBC handle \n");
995 return ErrSysInit;
997 // We want ODBC 3 support
998 SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
999 retValue = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
1000 if (retValue) {
1001 printError(ErrSysInit, "Unable to allocate ODBC handle \n");
1002 return ErrSysInit;
1004 retValue = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)dsn, SQL_NTS,
1005 outstr, sizeof(outstr), &outstrlen,
1006 SQL_DRIVER_NOPROMPT);
1007 if (SQL_SUCCEEDED(retValue)) {
1008 printDebug(DM_Gateway, "Connected to target database using dsn = %s\n", dsn);
1009 }else{
1010 printError(ErrSysInit, "Failed to connect to target database\n");
1011 return ErrSysInit;
1013 retValue=SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
1014 if (retValue) {
1015 printError(ErrSysInit, "Unable to allocate ODBC handle \n");
1016 return ErrSysInit;
1018 if(tdbName == mysql){
1019 colPos=3;
1020 // User name is required in upper case for the SQLTables()'s 4th parameter
1021 Util::str_toupper((char*)user);
1022 retValue=SQLTables(hstmt,NULL, 0, (SQLCHAR*)user, SQL_NTS, NULL, 0, (SQLCHAR*)"TABLE", SQL_NTS);
1023 if(retValue){
1024 printError(ErrSysInit, "Unable to retrieve list of tables\n");
1025 return ErrSysInit;
1027 // Binding Column for 3rd parameter to get Table name.
1028 retValue=SQLBindCol(hstmt,3, SQL_C_CHAR,buf,sizeof(buf),NULL);
1029 if(retValue){
1030 printError(ErrSysInit,"Unable to BindCol\n");
1031 return ErrSysInit;
1033 // For Postgres DB , SQLTables() retrieves all system and metadata tables,along with User defined table.
1034 // So Here is a another option to fetch the user defined tables only
1035 }else if(tdbName==postgres){
1036 SQLCHAR table[200]="SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog','information_schema');";
1037 retValue=SQLPrepare(hstmt,table,SQL_NTS);
1038 if(retValue){
1039 printError(ErrSysInit,"Unable to Prapare the statement\n");
1040 return ErrSysInit;
1042 retValue = SQLBindCol(hstmt,1,SQL_C_CHAR,buf,sizeof(buf),NULL);
1043 if(retValue){
1044 printError(ErrSysInit,"Unable to bind the column\n");
1045 return ErrSysInit;
1047 retValue = SQLExecute(hstmt);
1048 if(retValue){
1049 printError(ErrSysInit,"Unable to execute\n");
1050 return ErrSysInit;
1054 while(SQL_SUCCEEDED(retValue = SQLFetch(hstmt))){
1055 // copy Buffer value
1056 //strcpy(&table[counter][0],buf);
1057 cacheLoader.setDsnName(dsnName);
1058 TableConf::config.setDsnName(dsnName);
1059 cacheLoader.setConnParam(username, password);
1060 TableConf::config.setConnParam(username, password);
1061 // Check table is cached or not
1062 mode = TableConf::config.getTableMode(buf);
1063 cacheLoader.setTable(buf);
1064 TableConf::config.setTable(buf);
1065 isCached = TableConf::config.isTableCached(mode);
1066 if(isCached){
1067 printf("Warning: Table '%s' is already cached.\n",buf);
1068 }else{
1069 rv = cacheLoader.load(tableDefinition);
1070 if(rv != OK){
1071 printf("Warning: Table '%s' is present in CSQL locally.\n",buf);
1072 }else{
1073 TableConf::config.addToCacheTableFile(isDirect);
1074 printf("Cached Table:%s\n",buf);
1075 TableConf::config.init();
1078 counter++;
1080 // Checking couter value
1081 if(counter==0)
1082 printf("There is no table present in Target Database.\n");
1083 retValue=SQLCloseCursor(hstmt);
1084 if(retValue){
1085 printError(ErrSysInit,"Unable to close the cursor\n");
1086 return ErrSysInit;
1088 retValue=SQLTransact(henv,hdbc,SQL_COMMIT);
1089 if(retValue){
1090 printError(ErrSysInit,"Unable to commit the transaction\n");
1091 return ErrSysInit;
1093 retValue = SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
1094 if(retValue){
1095 printError(ErrSysInit,"Unable to free statement handle\n");
1096 return ErrSysInit;
1098 retValue = SQLDisconnect(hdbc);
1099 if(retValue){
1100 printError(ErrSysInit,"Unable to disconnect from DS handle\n");
1101 return ErrSysInit;
1103 retValue = SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
1104 if(retValue){
1105 printError(ErrSysInit,"Unable to free connection handle\n");
1106 return ErrSysInit;
1108 retValue = SQLFreeHandle(SQL_HANDLE_ENV,henv);
1109 if(retValue){
1110 printError(ErrSysInit,"Unable to free environment handle\n");
1111 return ErrSysInit;
1113 return OK;
1116 bool CacheTableLoader::resolveForDSN(char *dsn, TDBInfo &tdbName, DbRetVal &rv)
1118 char dsnId[IDENTIFIER_LENGTH]; dsnId[0]='\0';
1119 char user[IDENTIFIER_LENGTH]; user[0] = '\0';
1120 char passwd[IDENTIFIER_LENGTH]; passwd[0] = '\0';
1121 char tdb[IDENTIFIER_LENGTH]; tdb[0]='\0';
1122 FILE *fp;
1124 fp = fopen(Conf::config.getDsConfigFile(),"r");
1125 if(fp==NULL) {
1126 printError(ErrSysInit, "csqlds.conf file does not exist");
1127 rv = ErrSysInit;
1128 return false;
1131 // STARTs Here:
1132 // DSN, user and password value is read here from csql.conf file and
1133 // csqlds.conf file.
1134 // it's true if -d option is specified and the DSN value not matched with
1135 // csql.conf's DSN.
1136 if(strcmp(dsnName,"")==0) strcpy(dsnName, Conf::config.getDSN());
1138 bool isDSNExist=false;
1139 while(!feof(fp)) {
1140 fscanf(fp,"%s %s %s %s\n",dsnId,user,passwd,tdb);
1141 if(strcmp(dsnId,dsnName)==0) { // Both the DSN is matched here
1142 if(strcmp(user,"NULL")!=0 && strcmp(passwd,"NULL")!=0) {
1143 sprintf(dsn,"DSN=%s;UID=%s;PWD=%s;",dsnName,user,passwd);
1144 isDSNExist=true;
1145 break;
1146 } else {
1147 sprintf(dsn,"DSN=%s;",dsnName);
1148 isDSNExist=true;
1149 break;
1153 if(!isDSNExist) {
1154 printError(ErrNotExists,"Entries is not present in the csqlds.conf file\n");
1155 fclose(fp);
1156 rv = ErrNotExists;
1157 return false;
1159 fclose(fp);
1160 if (strcasecmp(tdb,"mysql") == 0) tdbName=mysql;
1161 else if (strcasecmp(tdb,"postgres")==0) tdbName=postgres;
1162 else {
1163 printError(ErrNotFound," Target Database Name is not properly set.Tdb name could be mysql, postgres\n");
1164 rv = ErrNotFound;
1165 return false;
1167 logFine(Conf::logger, "TDB Name:%s\n", tdb);
1168 return isDSNExist;
1171 void CacheTableLoader::generateCacheTableStatement(char *stmtBuf)
1173 if(((strcmp(conditionVal,"")==0) || (strcmp(conditionVal,"NULL")==0)) &&
1174 ((strcmp(fieldlistVal,"")==0) || (strcmp(fieldlistVal,"NULL")==0)))
1176 sprintf(stmtBuf, "SELECT * FROM %s;", tableName);
1178 else if(((strcmp(conditionVal,"")!=0) || (strcmp(conditionVal,"NULL")!=0)) &&
1179 ((strcmp(fieldlistVal,"")==0) || (strcmp(fieldlistVal,"NULL")==0)))
1181 sprintf(stmtBuf,"SELECT * FROM %s where %s;",tableName,conditionVal);
1184 else if(((strcmp(conditionVal,"")==0) || (strcmp(conditionVal,"NULL")==0)) &&
1185 ((strcmp(fieldlistVal,"")!=0) || (strcmp(fieldlistVal,"NULL")!=0)))
1187 sprintf(stmtBuf,"SELECT %s FROM %s;",fieldlistVal,tableName);
1189 else {
1190 sprintf(stmtBuf,"SELECT %s FROM %s where %s;",fieldlistVal,tableName,conditionVal);
1192 logFinest(Conf::logger, "Cache Table Stmt %s", stmtBuf);
1195 bool CacheTableLoader::prepareCreateIndexStatement(SQLHSTMT hstmtmeta,
1196 char *crtIdxStmt, TDBInfo tdbName, HashIndexInitInfo *inf)
1198 char columnname[IDENTIFIER_LENGTH];
1199 char indexname[IDENTIFIER_LENGTH];
1200 int retValue=SQLPrimaryKeys(hstmtmeta, NULL, SQL_NTS, NULL, SQL_NTS,
1201 (SQLCHAR*) tableName, SQL_NTS);
1202 retValue = SQLBindCol(hstmtmeta, 4, SQL_C_CHAR,columnname, 129,NULL);
1203 char *ptr=crtIdxStmt;
1204 sprintf(ptr, "CREATE INDEX %s_PRIMARY on %s ( ", tableName, tableName);
1205 ptr += strlen(ptr);
1206 bool isPriIndex=false;
1207 char indname[IDENTIFIER_LENGTH];
1208 if(SQLFetch( hstmtmeta ) == SQL_SUCCESS)
1210 Util::str_tolower(columnname);
1211 inf->list.append(columnname);
1212 sprintf(ptr, "%s ", columnname);
1213 ptr += strlen(ptr);
1214 while ( SQLFetch( hstmtmeta ) == SQL_SUCCESS ) {
1215 Util::str_tolower(columnname);
1216 inf->list.append(columnname);
1217 sprintf(ptr, ", %s ", columnname);
1218 ptr += strlen(ptr);
1220 sprintf(ptr, ") PRIMARY SIZE 10007;");
1221 inf->indType = hashIndex;
1222 inf->bucketSize = 10007;
1223 inf->isUnique = true; inf->isPrimary = true;
1224 strcpy(inf->tableName, tableName);
1225 strcpy(indexname,"PRIMARY");
1226 sprintf(indname, "%s_%s", tableName, indexname);
1227 isPriIndex=true;
1229 return isPriIndex;
1232 DbRetVal CacheTableLoader::prepareCreateTableStatement(char *crtTblStmt, SQLHSTMT hstmt,
1233 HashIndexInitInfo *inf, int totalFields, TDBInfo tdbName, bool &isKeyFld)
1235 DbRetVal rv = OK;
1236 UWORD icol=1;
1237 UCHAR colName[IDENTIFIER_LENGTH];
1238 SWORD colNameMax=IDENTIFIER_LENGTH;
1239 SWORD nameLength=0;
1240 SWORD colType=0;
1241 SQLULEN colLength = 0;
1242 SWORD scale=0;
1243 SWORD nullable=0;
1244 int retValue = 0;
1245 bool isNullfld=false;
1246 bool firstFld = true;
1247 char *name = NULL;
1249 char *ptr = crtTblStmt;
1250 sprintf(ptr, "CREATE TABLE %s ( ", tableName);
1251 ptr += strlen(ptr);
1252 while (icol <= totalFields) {
1253 retValue = SQLDescribeCol(hstmt, icol, colName, colNameMax,
1254 &nameLength, &colType, &colLength,
1255 &scale, &nullable);
1256 if (retValue) {
1257 printError(ErrSysInit, "Unable to retrieve ODBC column info\n");
1258 return ErrSysInit;
1260 Util::str_tolower((char*)colName);
1261 printDebug(DM_Gateway, "Describe Column %s %d %d %d %d\n",
1262 colName, colType, colLength, scale, nullable);
1263 logFinest(Conf::logger, "Describe Column colName:%s \
1264 colType:%d colLen:%d scale:%d nullable:%d\n",
1265 colName, colType, colLength, scale, nullable);
1266 icol++;
1267 if(strcmp((char*)colName,fieldName)== 0)
1269 isKeyFld=true;
1270 isNullfld=true;
1272 bool isPriFld=false;
1273 if (nullable) {
1274 inf->list.resetIter();
1275 while ((name=inf->list.nextFieldName())!=NULL) {
1276 if(0==strcmp((char*)colName,name)) {
1277 if (firstFld) {
1278 firstFld = false;
1279 sprintf(ptr, "%s %s", colName, AllDataType::getSQLString(
1280 AllDataType::convertFromSQLType(
1281 colType,colLength,scale,tdbName)));
1282 ptr += strlen(ptr);
1283 if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY)
1285 sprintf(ptr, "(%d) NOT NULL",colLength+1);
1286 } else { sprintf(ptr, " NOT NULL"); }
1287 ptr += strlen(ptr);
1288 } else {
1289 sprintf(ptr, ", %s %s", colName, AllDataType::getSQLString(
1290 AllDataType::convertFromSQLType(
1291 colType,colLength,scale,tdbName)));
1292 ptr += strlen(ptr);
1293 if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY)
1295 sprintf(ptr, "(%d) NOT NULL",colLength+1);
1296 } else { sprintf(ptr, " NOT NULL"); }
1297 ptr += strlen(ptr);
1299 //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(
1300 // colType,colLength,scale,tdbName),
1301 // colLength +1, NULL, true);
1302 isPriFld=true;
1303 break;
1306 if(!isPriFld) {
1307 if(!isNullfld) {
1308 if (firstFld) {
1309 firstFld = false;
1310 sprintf(ptr, "%s %s", colName, AllDataType::getSQLString(
1311 AllDataType::convertFromSQLType(
1312 colType,colLength,scale,tdbName)));
1313 ptr += strlen(ptr);
1314 if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY) {
1315 sprintf(ptr, "(%d)",colLength+1);
1316 ptr += strlen(ptr);
1318 } else {
1319 sprintf(ptr, ", %s %s", colName, AllDataType::getSQLString(
1320 AllDataType::convertFromSQLType(
1321 colType,colLength,scale,tdbName)));
1322 ptr += strlen(ptr);
1323 if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY) {
1324 sprintf(ptr, "(%d)",colLength+1);
1325 ptr += strlen(ptr);
1328 //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(
1329 // colType,colLength,scale,tdbName), colLength+1);
1330 } else {
1331 if (firstFld) {
1332 firstFld = false;
1333 sprintf(ptr, "%s %s", colName, AllDataType::getSQLString(
1334 AllDataType::convertFromSQLType(
1335 colType,colLength,scale,tdbName)));
1336 ptr += strlen(ptr);
1337 if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY) {
1338 sprintf(ptr, "(%d) NOT NULL",colLength+1);
1339 } else { sprintf(ptr, " NOT NULL"); }
1340 ptr += strlen(ptr);
1341 } else {
1342 sprintf(ptr, ", %s %s", colName, AllDataType::getSQLString(
1343 AllDataType::convertFromSQLType(
1344 colType,colLength,scale,tdbName)));
1345 ptr += strlen(ptr);
1346 if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY) {
1347 sprintf(ptr, "(%d) NOT NULL",colLength+1);
1348 } else { sprintf(ptr, " NOT NULL"); }
1349 ptr += strlen(ptr);
1351 //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(
1352 // colType,colLength,scale,tdbName),
1353 // colLength+1, NULL, true);
1354 isNullfld=false;
1357 } else {
1358 if (firstFld) {
1359 firstFld = false;
1360 sprintf(ptr, "%s %s", colName, AllDataType::getSQLString(
1361 AllDataType::convertFromSQLType(
1362 colType,colLength,scale,tdbName)));
1363 ptr += strlen(ptr);
1364 if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY) {
1365 sprintf(ptr, "(%d) NOT NULL",colLength+1);
1366 } else { sprintf(ptr, " NOT NULL"); }
1367 ptr += strlen(ptr);
1368 } else {
1369 sprintf(ptr, ", %s %s", colName, AllDataType::getSQLString(
1370 AllDataType::convertFromSQLType(
1371 colType,colLength, scale, tdbName)));
1372 ptr += strlen(ptr);
1373 if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY) {
1374 sprintf(ptr, "(%d) NOT NULL",colLength+1);
1375 } else { sprintf(ptr, " NOT NULL"); }
1376 ptr += strlen(ptr);
1378 //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(
1379 // colType,colLength,scale, tdbName),
1380 // colLength +1, NULL, true);
1383 sprintf(ptr, ");");
1384 ptr += strlen(ptr);
1385 //printf("table stmt '%s'\n", crtTblStmt);
1386 return rv;
1389 void CacheTableLoader::prepareInsertStatement(AbsSqlStatement *stmt, List *fNameList, char *insStmt)
1391 DbRetVal rv = OK;
1392 char *ptr = insStmt;
1393 sprintf(ptr,"INSERT INTO %s VALUES(", tableName);
1394 ptr += strlen(ptr);
1395 bool firstFld = true;
1396 SqlStatement *sqlStmt = (SqlStatement *)stmt;
1397 *fNameList = sqlStmt->getFieldNameList(tableName, rv);
1398 int noOfFields = fNameList->size();
1399 while (noOfFields--) {
1400 if (firstFld) {
1401 firstFld = false;
1402 sprintf(ptr,"?", tableName);
1403 ptr += strlen(ptr);
1404 } else {
1405 sprintf(ptr, ",?");
1406 ptr += strlen(ptr);
1409 sprintf(ptr, ");");
1410 ptr += strlen(ptr);