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<CacheTableLoader.h>
  17 #include<Util.h>
  18 DbRetVal CacheTableLoader::addToCacheTableFile()
  19 {
  20     FILE *fp;
  21     fp = fopen(Conf::config.getTableConfigFile(),"a");
  22     if( fp == NULL ) {
  23         printError(ErrSysInit, "Invalid path/filename in TABLE_CONFIG_FILE.\nTable will not be"
  24                                 "recovered in case of crash");
  25         return ErrSysInit;
  26     }
  27     //TODO::if already table present in the file, it means that the
  28     //table is replicated. in this case change mode from
  29     //2 to 3 (repl to replcache)
  30     fprintf(fp, "%d:%s\n", 1, tableName);
  31     fclose(fp);
  32     return OK;
  33 }
  34
  35 DbRetVal CacheTableLoader::removeFromCacheTableFile()
  36 {
  37     FILE *fp, *tmpfp;
  38     char tmpFileName[MAX_FILE_PATH_LEN];
  39     sprintf(tmpFileName, "%s.tmp", Conf::config.getTableConfigFile());
  40     tmpfp = fopen(tmpFileName,"w");
  41     if( tmpfp == NULL ) {
  42         printError(ErrSysInit, "Invalid path/filename in TABLE_CONFIG_FILE.\n");
  43         return ErrSysInit;
  44     }
  45     fp = fopen(Conf::config.getTableConfigFile(),"r");
  46     if( fp == NULL ) {
  47         printError(ErrSysInit, "csqltable.conf file does not exist");
  48         return ErrSysInit;
  49     }
  50     char tablename[IDENTIFIER_LENGTH];
  51     int mode;
  52     while(!feof(fp))
  53     {
  54         fscanf(fp, "%d:%s\n", &mode, tablename);
  55         if (strcmp (tablename, tableName) == 0) continue;
  56         fprintf(tmpfp, "%d:%s\n", mode, tablename);
  57     }
  58     fclose(tmpfp);
  59     fclose(fp);
  60     char sysCommand[MAX_FILE_PATH_LEN * 2];
  61     sprintf(sysCommand, "mv %s %s", tmpFileName, Conf::config.getTableConfigFile());
  62     int ret = system(sysCommand);
  63     if (ret != 0)
  64     {
  65         printError(ErrSysInit, "Check csqltable.conf file permission. unable to remove %s from file", tableName);
  66         return ErrSysInit;
  67     }
  68     return OK;
  69 }
  70
  71 DbRetVal CacheTableLoader::load(bool tabDefinition)
  72 {
  73     Connection conn;
  74     DbRetVal rv = conn.open(userName, password);
  75     if (rv != OK) return ErrSysInit;
  76     DatabaseManager *dbMgr = (DatabaseManager*) conn.getDatabaseManager();
  77     if (dbMgr == NULL) { printError(ErrSysInit, "Auth failed\n"); return ErrSysInit; }
  78     conn.startTransaction();
  79     rv = load(dbMgr, tabDefinition);
  80     conn.commit();
  81     conn.close();
  82     return rv;
  83 }
  84
  85 DbRetVal CacheTableLoader::load(DatabaseManager *dbMgr, bool tabDefinition)
  86 {
  87     char dsn[72];
  88     sprintf(dsn, "DSN=%s;", Conf::config.getDSN());
  89     SQLCHAR outstr[1024];
  90     SQLSMALLINT outstrlen;
  91     DbRetVal rv = OK;
  92     int retValue =0;
  93     SQLHENV henv;
  94     SQLHDBC hdbc;
  95     SQLHSTMT hstmt;
  96     retValue = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
  97     if (retValue) {printError(ErrSysInit, "Unable to allocate ODBC handle \n"); return ErrSysInit; }
  98     SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
  99     retValue = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
 100     if (retValue) {printError(ErrSysInit, "Unable to allocate ODBC handle \n"); return ErrSysInit; }
 101     retValue = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)dsn, SQL_NTS,
 102                          outstr, sizeof(outstr), &outstrlen,
 103                          SQL_DRIVER_NOPROMPT);
 104     if (SQL_SUCCEEDED(retValue)) {
 105         printDebug(DM_Gateway, "Connected to target database using dsn = %s\n", dsn);
 106     } else {
 107         fprintf(stderr, "Failed to connect to target database\n");
 108         return ErrSysInit;
 109     }
 110
 111     retValue=SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
 112     if (retValue) {printError(ErrSysInit, "Unable to allocate ODBC handle \n"); return ErrSysInit; }
 113     char stmtBuf[1024];
 114     sprintf(stmtBuf, "SELECT * FROM %s;", tableName);
 115     retValue = SQLPrepare (hstmt, (unsigned char *) stmtBuf, SQL_NTS);
 116     if (retValue) {printError(ErrSysInit, "Unable to Prepare ODBC statement \n"); return ErrSysInit; }
 117
 118     if (tabDefinition) {
 119         short totalFields=0;
 120         retValue = SQLNumResultCols (hstmt, &totalFields);
 121         if (retValue) {printError(ErrSysInit, "Unable to retrieve ODBC total columns\n"); return ErrSysInit; }
 122         UWORD                   icol;
 123         UCHAR                   colName[IDENTIFIER_LENGTH];
 124         SWORD                   colNameMax;
 125         SWORD                   nameLength;
 126         SWORD                   colType;
 127         SQLULEN                 colLength;
 128         SWORD                   scale;
 129         SWORD                   nullable;
 130         TableDef tabDef;
 131         icol = 1; colNameMax = IDENTIFIER_LENGTH;
 132         while (icol <= totalFields) {
 133             retValue = SQLDescribeCol(hstmt, icol, colName, colNameMax,
 134                                         &nameLength, &colType, &colLength,
 135                                         &scale, &nullable);
 136             if (retValue) {printError(ErrSysInit, "Unable to retrieve ODBC column info\n"); return ErrSysInit; }
 137
 138             printDebug(DM_Gateway, "Describe Column %s %d %d \n", colName, colType, colLength);
 139             icol++;
 140            tabDef.addField((char*) colName, AllDataType::convertFromSQLType(colType), colLength);
 141         }
 142         rv = dbMgr->createTable(tableName, tabDef);
 143         if (rv != OK)
 144         {
 145              printError(ErrSysInit, "Table creation failed in csql for %s\n", tableName);
 146              SQLDisconnect(hdbc);
 147              return ErrSysInit;
 148         }
 149         char columnname[124]; char indexname[124];
 150         short type; short unique;
 151         SQLHSTMT hstmtmeta;
 152         retValue=SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmtmeta);
 153         if (retValue)
 154         {
 155             printError(ErrSysInit, "Unable to allocate ODBC handle \n");
 156             return ErrSysInit;
 157         }
 158
 159         retValue = SQLStatistics(hstmtmeta, NULL, 0, NULL, SQL_NTS,
 160                     (SQLCHAR*) tableName, SQL_NTS, SQL_INDEX_ALL, SQL_QUICK);
 161         retValue = SQLBindCol(hstmtmeta, 4, SQL_C_SHORT,
 162                           &unique, 2, NULL);
 163         retValue = SQLBindCol(hstmtmeta, 6, SQL_C_CHAR,
 164                           indexname, 129, NULL);
 165         retValue = SQLBindCol(hstmtmeta, 7, SQL_C_SHORT,
 166                           &type, 2, NULL);
 167         retValue = SQLBindCol(hstmtmeta, 9, SQL_C_CHAR,
 168                           columnname, 129,NULL);
 169         while ((retValue = SQLFetch(hstmtmeta)) == SQL_SUCCESS)
 170         {  //if (type != SQL_TABLE_STAT)
 171             {
 172                 printDebug(DM_Gateway, "Column: %-18s Index Name: %-18s unique:%hd type:%hd\n",
 173                   columnname, indexname, unique, type);
 174             }
 175             if (type == 3) {
 176                HashIndexInitInfo *info = new HashIndexInitInfo();
 177                info->indType =  hashIndex;
 178                info->bucketSize = 10007;
 179                info->list.append(columnname);
 180                strcpy(info->tableName, tableName);
 181                char indname[128];
 182                sprintf(indname, "%s_%s", tableName, indexname);
 183                rv = dbMgr->createIndex(indname, info);
 184                if (rv != OK)
 185                {
 186                   printError(ErrSysInit, "Index creation failed in csql for %s\n", tableName);
 187                   SQLDisconnect(hdbc);
 188                   return ErrSysInit;
 189                }
 190                delete info;
 191            } else {
 192                printError(ErrSysInit,"CSQL does not support this index type\n");
 193                SQLDisconnect(hdbc);
 194                return ErrSysInit;
 195            }
 196        }// while meta data fetch for index creation
 197        SQLCloseCursor (hstmtmeta);
 198        SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 199     }
 200     Table *table = dbMgr->openTable(tableName);
 201     if (table == NULL) {
 202         printError(ErrSysInit,"unable to open table %d\n", tableName);
 203         dbMgr->closeTable(table);
 204         if (tabDefinition) dbMgr->dropTable(tableName);
 205         SQLDisconnect(hdbc);
 206         return ErrSysInit;
 207     }
 208     table->setUndoLogging(false);
 209     //rv = table->lock(false); //no need to release this lock as it is upgrade from S to X
 210     if (rv != OK)
 211     {
 212         dbMgr->closeTable(table);
 213         if (tabDefinition) dbMgr->dropTable(tableName);
 214         SQLDisconnect(hdbc);
 215         return ErrSysInit;
 216     }
 217     List fNameList = table->getFieldNameList();
 218     ListIterator fNameIter = fNameList.getIterator();
 219     FieldInfo *info = new FieldInfo();
 220     int fcount =1; void *valBuf; int fieldsize=0;
 221     Identifier *elem = NULL;
 222
 223     BindBuffer *bBuf;
 224     List valBufList;
 225     while (fNameIter.hasElement()) {
 226         elem = (Identifier*) fNameIter.nextElement();
 227         table->getFieldInfo((const char*)elem->name, info);
 228         valBuf = AllDataType::alloc(info->type, info->length);
 229         table->bindFld(elem->name, valBuf);
 230         fieldsize=0;
 231         switch( info->type)
 232         {
 233             case typeString:
 234                 fieldsize = info->length;
 235                 break;
 236             case typeDate:
 237                 bBuf = new BindBuffer();
 238                 bBuf->csql = valBuf;
 239                 bBuf->type = typeDate;
 240                 fieldsize = sizeof(DATE_STRUCT);
 241                 bBuf->targetdb = malloc(fieldsize);
 242                 valBuf = bBuf->targetdb;
 243                 valBufList.append(bBuf);
 244                 break;
 245             case typeTime:
 246                 bBuf = new BindBuffer();
 247                 bBuf->csql = valBuf;
 248                 bBuf->type = typeTime;
 249                 fieldsize = sizeof(TIME_STRUCT);
 250                 bBuf->targetdb = malloc(fieldsize);
 251                 valBuf = bBuf->targetdb;
 252                 valBufList.append(bBuf);
 253                 break;
 254             case typeTimeStamp:
 255                 bBuf = new BindBuffer();
 256                 bBuf->csql = valBuf;
 257                 bBuf->type = typeTimeStamp;
 258                 fieldsize = sizeof(TIMESTAMP_STRUCT);
 259                 bBuf->targetdb = malloc(fieldsize);
 260                 valBuf = bBuf->targetdb;
 261                 valBufList.append(bBuf);
 262                 break;
 263         }
 264         retValue = SQLBindCol (hstmt, fcount++, AllDataType::convertToSQLType(info->type),
 265                                valBuf, fieldsize, NULL);
 266         if (retValue) {printError(ErrSysInit, "Unable to bind columns in ODBC\n"); return ErrSysInit; }
 267     }
 268     delete info;
 269     retValue = SQLExecute (hstmt);
 270     if (retValue) {printError(ErrSysInit, "Unable to execute ODBC statement\n"); return ErrSysInit; }
 271     while(true)
 272     {
 273         retValue = SQLFetch (hstmt);
 274         if (retValue) break;
 275         ListIterator bindIter = valBufList.getIterator();
 276         while (bindIter.hasElement()) {
 277             bBuf = (BindBuffer*) bindIter.nextElement();
 278             switch (bBuf->type) {
 279                 case typeDate: {
 280                     Date *dtCSQL = (Date*) bBuf->csql;
 281                     DATE_STRUCT *dtTarget = (DATE_STRUCT*) bBuf->targetdb;
 282                     dtCSQL->set(dtTarget->year,dtTarget->month,dtTarget->day);
 283                     break;
 284                 }
 285                 case typeTime: {
 286                     Time *dtCSQL = (Time*) bBuf->csql;
 287                     TIME_STRUCT *dtTarget = (TIME_STRUCT*) bBuf->targetdb;
 288                     dtCSQL->set(dtTarget->hour,dtTarget->minute,dtTarget->second);
 289                     break;
 290                 }
 291                 case typeTimeStamp: {
 292                     TimeStamp *dtCSQL = (TimeStamp*) bBuf->csql;
 293                     TIMESTAMP_STRUCT *dtTarget = (TIMESTAMP_STRUCT*) bBuf->targetdb;
 294                     dtCSQL->setDate(dtTarget->year,dtTarget->month,dtTarget->day);
 295                     dtCSQL->setTime(dtTarget->hour,dtTarget->minute,dtTarget->second, dtTarget->fraction);
 296                     break;
 297                 }
 298             }
 299         }
 300         table->insertTuple();
 301     }
 302     //TODO::leak:: valBufList and its targetdb buffer
 303     valBufList.reset();
 304     dbMgr->closeTable(table);
 305     SQLCloseCursor (hstmt);
 306     SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 307     SQLDisconnect (hdbc);
 308     SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 309     SQLFreeHandle (SQL_HANDLE_ENV, henv);
 310     return OK;
 311 }
 312
 313 DbRetVal CacheTableLoader::reload()
 314 {
 315     DbRetVal rv = unload(false);
 316     if (rv != OK) return rv;
 317     rv = load(false);
 318     return rv;
 319 }
 320
 321 DbRetVal CacheTableLoader::unload(bool tabDefinition)
 322 {
 323     Connection conn;
 324     DbRetVal rv = conn.open(userName, password);
 325     if (rv != OK) return ErrSysInit;
 326     DatabaseManager *dbMgr = (DatabaseManager*) conn.getDatabaseManager();
 327     if (dbMgr == NULL) { printError(ErrSysInit, "Auth failed\n"); return ErrSysInit; }
 328     if (!tabDefinition)
 329     {
 330         Table *table = dbMgr->openTable(tableName);
 331         if (table == NULL) { conn.close(); return ErrBadCall; }
 332         rv = conn.startTransaction();
 333         if (rv != OK) { dbMgr->closeTable(table); conn.close(); return ErrBadCall; }
 334         table->truncate();
 335         conn.commit();
 336         dbMgr->closeTable(table);
 337     }
 338     else
 339     {
 340         rv = dbMgr->dropTable(tableName);
 341     }
 342     conn.close();
 343
 344     return rv;
 345 }
 346
 347 DbRetVal CacheTableLoader::refresh()
 348 {
 349     return OK;
 350 }
 351
 352 DbRetVal CacheTableLoader::recoverAllCachedTables()
 353 {
 354     FILE *fp;
 355     Connection conn;
 356     DbRetVal rv = conn.open(userName, password);
 357     //Note: if connection is not open, configuration veriables may be incorrect
 358
 359     fp = fopen(Conf::config.getTableConfigFile(),"r");
 360     if( fp == NULL ) {
 361         printError(ErrSysInit, "cachetable.conf file does not exist");
 362         return OK;
 363     }
 364     conn.close();
 365     //TODO::take exclusive lock on database
 366     char tablename[IDENTIFIER_LENGTH];
 367     int mode;
 368     rv = OK;
 369     while(!feof(fp))
 370     {
 371         fscanf(fp, "%d:%s\n", &mode, tablename);
 372         if (mode ==2 )  //just replicated table and not cached
 373             continue;
 374         printDebug(DM_Gateway, "Recovering Table from target db: %s\n", tablename);
 375         setTable(tablename);
 376         printf("Recovering table %s\n", tablename);
 377         rv = load();
 378         if (rv != OK) return rv;
 379     }
 380     fclose(fp);
 381     return OK;
 382 }
 383