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