src/tools/cachetable.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 <CSql.h>
  17 #include <CacheTableLoader.h>
  18
  19 void printUsage()
  20 {
  21    printf("Usage: cachetable [-u username] [-p passwd] -t tablename [-r]\n");
  22    printf("       tablename -> tablename residing in target database which needs to be cached.\n");
  23    printf("       username -> username to connect with csql.\n");
  24    printf("       passwd -> password for the above username to connect with csql.\n");
  25    printf("       r -> recover all cached tables from the target database.\n");
  26    return;
  27
  28 }
  29
  30 void addToCacheTableFile(char *tablename)
  31 {
  32     FILE *fp;
  33     printf("CACHE_TABLE_FILE is %s\n", Conf::config.getCacheTableFile());
  34     fp = fopen(Conf::config.getCacheTableFile(),"a");
  35     if( fp == NULL ) {
  36         printError(ErrSysInit, "Invalid path/filename in CACHE_TABLE_FILE.\nTable will not be"
  37                                 "recovered in case of crash");
  38         return;
  39     }
  40
  41     fprintf(fp, "%s\n", tablename);
  42     fclose(fp);
  43 }
  44 int main(int argc, char **argv)
  45 {
  46     char username[IDENTIFIER_LENGTH];
  47     username [0] = '\0';
  48     char password[IDENTIFIER_LENGTH];
  49     password [0] = '\0';
  50     int c = 0, opt = 10;
  51     char tablename[IDENTIFIER_LENGTH];
  52     while ((c = getopt(argc, argv, "u:p:t:r?")) != EOF)
  53     {
  54         switch (c)
  55         {
  56             case 'u' : { strcpy(username, argv[optind - 1]); opt=10; break; }
  57             case 'p' : { strcpy(password, argv[optind - 1]); opt=10; break; }
  58             case 't' : { strcpy(tablename, argv[optind - 1]); opt = 2; break; }
  59             case '?' : { opt = 10; break; } //print help
  60             case 'r' : { opt = 3; break; } //recover all the tables
  61
  62             default: opt=10;
  63
  64         }
  65     }//while options
  66     if (opt == 10) {
  67         printUsage();
  68         return 0;
  69     }
  70
  71     //printf("%s %s \n", username, password);
  72     if (username[0] == '\0' )
  73     {
  74         strcpy(username, "root");
  75         strcpy(password, "manager");
  76     }
  77     DbRetVal rv = OK;
  78     CacheTableLoader cacheLoader;
  79
  80     if (opt==2) {
  81         cacheLoader.setTable(tablename);
  82         rv = cacheLoader.load(username, password);
  83         if (rv != OK) exit (1);
  84         rv = cacheLoader.addToCacheTableFile();
  85         if (rv != OK) exit (2);
  86     }else if (opt==3)
  87     {
  88         rv = cacheLoader.recoverAllCachedTables(username, password);
  89         if (rv != OK) exit (1);
  90     }
  91
  92 /*
  93     Connection conn;
  94     DbRetVal rv = conn.open(username, password);
  95     if (rv != OK) return 1;
  96     DatabaseManager *dbMgr = (DatabaseManager*) conn.getDatabaseManager();
  97     if (dbMgr == NULL) { printf("Auth failed\n"); return 2;}
  98
  99     Conf::config.getDSN();
 100     //char* dsn = (char *) "DSN=myodbc3;";
 101     char dsn[72];
 102     sprintf(dsn, "DSN=%s;", Conf::config.getDSN());
 103     printf("DSN VALUE FROM CONFIG=%s\n", dsn);
 104     SQLCHAR outstr[1024];
 105     SQLSMALLINT outstrlen;
 106
 107     int retValue =0;
 108     SQLHENV henv;
 109     SQLHDBC hdbc;
 110     SQLHSTMT hstmt;
 111     retValue = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
 112     if (retValue) exit(1);
 113     SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
 114     retValue = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
 115     if (retValue) exit(1);
 116     retValue = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)dsn, SQL_NTS,
 117                          outstr, sizeof(outstr), &outstrlen,
 118                          SQL_DRIVER_NOPROMPT);
 119     if (SQL_SUCCEEDED(retValue)) {
 120         printf("Connected to target database using dsn = %s\n", dsn);
 121     } else {
 122         fprintf(stderr, "Failed to connect to target database\n");
 123         exit (2);
 124     }
 125
 126
 127     retValue=SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
 128     if (retValue) exit(1);
 129     char stmtBuf[1024];
 130     sprintf(stmtBuf, "SELECT * FROM %s;", tablename);
 131     retValue = SQLPrepare (hstmt, (unsigned char *) stmtBuf, SQL_NTS);
 132     if (retValue) exit(1);
 133     short totalFields=0;
 134     retValue = SQLNumResultCols (hstmt, &totalFields);
 135     if (retValue) exit(1);
 136     printf("ODBC Retrieved total columns = %d\n", totalFields);
 137
 138         UWORD                   icol;
 139         UCHAR                   szColName[IDENTIFIER_LENGTH];
 140         SWORD                   cbColNameMax;
 141         SWORD                   pcbColName;
 142         SWORD                   pfSqlType;
 143         SQLULEN                 pcbColDef;
 144         SWORD                   pibScale;
 145         SWORD                   pfNullable;
 146         TableDef tabDef;
 147
 148         icol = 1; cbColNameMax = IDENTIFIER_LENGTH;
 149         while (icol <= totalFields) {
 150                 retValue = SQLDescribeCol(hstmt, icol, szColName, cbColNameMax,
 151                                         &pcbColName, &pfSqlType, &pcbColDef,
 152                                         &pibScale, &pfNullable);
 153                 if (retValue ) exit (2);
 154
 155                 printf("Describe Column %s %d %d %d\n", szColName, pcbColName, pfSqlType, pcbColDef);
 156                 icol++;
 157                 tabDef.addField((char*) szColName, AllDataType::convertFromSQLType(pfSqlType), pcbColDef);
 158         }
 159     rv = dbMgr->createTable(tablename, tabDef);
 160     if (rv != OK) { printf("Table creation failed in csql for %s\n", tablename);
 161                     conn.close(); SQLDisconnect(hdbc); exit (1); }
 162
 163     Table *table = dbMgr->openTable(tablename);
 164     if (table == NULL){ printf("Table creation failed in csql for %s\n", tablename);
 165                     conn.close(); SQLDisconnect(hdbc); exit (1); }
 166     List fNameList = table->getFieldNameList();
 167     ListIterator fNameIter = fNameList.getIterator();
 168     FieldInfo *info = new FieldInfo();
 169     int fcount =1; void *valBuf; int fieldsize=0;
 170     Identifier *elem = NULL;
 171     DATE_STRUCT sDate;
 172     TIME_STRUCT sTime;
 173     TIMESTAMP_STRUCT sTimestamp;
 174
 175     while (fNameIter.hasElement()) {
 176         elem = (Identifier*) fNameIter.nextElement();
 177         table->getFieldInfo((const char*)elem->name, info);
 178         valBuf = AllDataType::alloc(info->type, info->length);
 179         table->bindFld(elem->name, valBuf);
 180         fieldsize=0;
 181         switch( info->type)
 182         {
 183             case typeString:
 184                 fieldsize = info->length;
 185                 break;
 186             case typeDate:
 187                 //TODO::store valBuf with type in list
 188                 fieldsize = sizeof(DATE_STRUCT);
 189                 valBuf = &sDate;
 190                 break;
 191         }
 192         retValue = SQLBindCol (hstmt, fcount++, AllDataType::convertToSQLType(info->type),
 193                                valBuf, fieldsize, NULL);
 194         if (retValue) exit (12);
 195     }
 196     delete info;
 197     conn.startTransaction();
 198     retValue = SQLExecute (hstmt);
 199     if (retValue) exit (13);
 200     while(true)
 201     {
 202         retValue = SQLFetch (hstmt);
 203         if (retValue) break;
 204         //TODO::convert odbc to csql for date, time, timestamp
 205
 206 //ACCESSING MEMBERS of above structures...
 207   //     sDate.year, sDate.month, sDate.day,
 208     //   sTime.hour, sTime.minute, sTime.second,
 209       // sTimestamp.year, sTimestamp.month, sTimestamp.day,
 210        //sTimestamp.hour, sTimestamp.minute, sTimestamp.second, sTimestamp.fraction
 211
 212
 213         table->insertTuple();
 214         printf("Inserted tuple\n");
 215     }
 216     SQLCloseCursor (hstmt);
 217     conn.commit();
 218     //append the tablename in the cache.table
 219     addToCacheTableFile(tablename);
 220     conn.close();
 221     SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 222     SQLDisconnect (hdbc);
 223     SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 224     SQLFreeHandle (SQL_HANDLE_ENV, henv);
 225 */
 226
 227     return 0;
 228 }