src/tools/csqldump.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 <CSql.h>
  18 #include <DatabaseManagerImpl.h>
  19 #include <Database.h>
  20 #include <TableImpl.h>
  21 #include <SqlFactory.h>
  22 #include <SqlStatement.h>
  23
  24 void printUsage()
  25 {
  26    printf("Usage: csqldump [-u username] [-p passwd] [-n noOfStmtsPerCommit] [-T tableName]\n");
  27    printf("       n -> number of statements per commit\n");
  28    printf("            Default value is 100. If system db size is big, then it shall be increased.\n");
  29    printf("       T -> Will dump only the table specified with this option.\n");
  30    return;
  31
  32 }
  33
  34 bool isCached(char *tblName)
  35 {
  36     if (!Conf::config.useCache()) return false;
  37     FILE *fp = fopen(Conf::config.getTableConfigFile(),"r");
  38     if( fp == NULL ) { return OK; }
  39     char ctablename[IDENTIFIER_LENGTH];
  40     char fieldname[IDENTIFIER_LENGTH];
  41     char condition[IDENTIFIER_LENGTH];
  42     char field[IDENTIFIER_LENGTH];
  43     char dsnName[IDENTIFIER_LENGTH];
  44
  45     int mode;
  46     bool isCached=false;
  47     while(!feof(fp)) {
  48         fscanf(fp, "%d %s %s %s %s %s %s %s\n", &mode, ctablename,fieldname,condition,field,dsnName);
  49         if (strcmp (ctablename, tblName) == 0) { isCached=true; break; }
  50     }
  51     fclose(fp);
  52     return isCached;
  53 }
  54
  55 int main(int argc, char **argv)
  56 {
  57     char username[IDENTIFIER_LENGTH];
  58     username [0] = '\0';
  59     char password[IDENTIFIER_LENGTH];
  60     password [0] = '\0';
  61     char tblName[IDENTIFIER_LENGTH];
  62     char conditionVal[IDENTIFIER_LENGTH];
  63     int c = 0, opt = 0;
  64     int noOfStmts =100;
  65     bool exclusive = false;
  66     bool Iscondition=false;
  67     bool schema = false;
  68     char name[IDENTIFIER_LENGTH];
  69     while ((c = getopt(argc, argv, "u:p:n:T:c:XS?")) != EOF) {
  70         switch (c) {
  71             case 'u' : { strcpy(username, argv[optind - 1]); opt=1; break; }
  72             case 'p' : { strcpy(password, argv[optind - 1]); opt=1; break; }
  73             case 'n' : { noOfStmts = atoi(argv[optind - 1]); opt = 5; break; }
  74             case 'T' : { strcpy(tblName,  argv[optind - 1]); opt = 15; break; }
  75             case 'c' : {strcpy(conditionVal,argv[optind -1]); Iscondition=true;break;}
  76             case 'X' : { exclusive = true; break; }
  77             case 'S' : { schema = true; break; }
  78             case '?' : { opt = 10; break; } //print help
  79             default: opt=1; //list all the tables
  80
  81         }
  82     }//while options
  83     if (opt == 10) {
  84         printUsage();
  85         return 0;
  86     }
  87
  88     //printf("%s %s \n", username, password);
  89     if (username[0] == '\0' ) {
  90         strcpy(username, I_USER);
  91         strcpy(password, I_PASS);
  92     }
  93     SqlConnection *sqlconn = (SqlConnection*) SqlFactory::createConnection(CSqlDirect);
  94
  95     SqlStatement *stmt = (SqlStatement*) SqlFactory::createStatement(CSqlDirect);
  96     if (exclusive) { stmt->setLoading(true); }
  97
  98     if (opt == 0 || opt == 1) opt = 5;
  99     if (opt == 5) {
 100         Connection conn;
 101         DbRetVal rv = conn.open(username, password);
 102         if (rv != OK)  {
 103             printf("Unable to get connection to csql\n");
 104             delete sqlconn;
 105             delete stmt;
 106             return 1;
 107         }
 108         DatabaseManagerImpl *dbMgr = (DatabaseManagerImpl*) conn.getDatabaseManager();
 109         if (dbMgr == NULL) {
 110             printf("Unable to retrive db manager\n");
 111             conn.close();
 112             delete stmt;
 113             delete sqlconn;
 114             return 2;
 115         }
 116         List tableList = dbMgr->getAllTableNames();
 117         ListIterator iter = tableList.getIterator();
 118         Identifier *elem = NULL;
 119         int count =0;
 120         while (iter.hasElement()) {
 121             elem = (Identifier*) iter.nextElement();
 122             //if (!exclusive && isCached(elem->name)) continue;
 123             printf("CREATE TABLE %s (", elem->name);
 124             Table *table = dbMgr->openTable(elem->name);
 125                 FieldInfo *info = new FieldInfo();
 126             List fNameList = table->getFieldNameList();
 127             ListIterator fNameIter = fNameList.getIterator();
 128             count++;
 129             bool firstField=true;
 130             Identifier *elem1 = NULL;
 131             char fieldName[IDENTIFIER_LENGTH];
 132             while (fNameIter.hasElement()) {
 133                 elem1 = (Identifier*) fNameIter.nextElement();
 134                 Table::getFieldNameAlone(elem1->name, fieldName);
 135                 rv = table->getFieldInfo(elem1->name, info);
 136                 if (rv !=OK)  {
 137                     printf("unable to retrive info for table %s\n", elem1->name);
 138                     conn.close();
 139                     delete stmt;
 140                     delete sqlconn;
 141                 }
 142                 if (firstField) {
 143                     printf("%s %s ", fieldName, AllDataType::getSQLString(info->type));
 144                     firstField = false;
 145                 } else
 146                     printf(", %s %s ", fieldName, AllDataType::getSQLString(info->type));
 147                 if (info->type == typeString) printf("(%d)",info->length );
 148                 if (info->type == typeBinary) printf("(%d)",info->length);
 149                 if (info->isNull) printf(" NOT NULL ");
 150                 if (info->isDefault) printf(" DEFAULT '%s' ", info->defaultValueBuf);
 151                 if (info->isAutoIncrement) printf(" AUTO_INCREMENT ");
 152             }
 153             fNameIter.reset();
 154             while (fNameIter.hasElement()) {
 155                 elem1 = (Identifier*) fNameIter.nextElement();
 156                 delete elem1;
 157             }
 158             fNameList.reset();
 159             if (table->isFKTable()){
 160                 table->printSQLForeignString();
 161             }
 162             printf(");\n");
 163             table->printSQLIndexString();
 164             delete info;
 165             dbMgr->closeTable(table);
 166         }
 167         conn.close();
 168         if (schema) { delete sqlconn; delete stmt; return 0; }
 169
 170         rv = sqlconn->connect(I_USER, I_PASS);
 171         if (OK !=rv) {
 172             printf("unable to connect to csql\n");
 173             delete sqlconn;
 174             delete stmt;
 175             return 10;
 176         }
 177         stmt->setConnection(sqlconn);
 178         if (exclusive) {
 179             rv = sqlconn->getExclusiveLock();
 180             if (rv != OK) {
 181                 printf("Unable to get exclusive lock\n");
 182                 sqlconn->disconnect();
 183                 delete sqlconn;
 184                 delete stmt;
 185                 return 1;
 186             }
 187         }
 188         iter.reset();
 189         char sqlstring[1024]="";
 190         bool flag=false;
 191         while (iter.hasElement()) {
 192             elem = (Identifier*) iter.nextElement();
 193             //if (!exclusive && isCached(elem->name)) continue;
 194             if (!flag) { printf("SET AUTOCOMMIT OFF;\n"); flag=true; }
 195             sprintf(sqlstring, "SELECT * FROM %s;", elem->name);
 196             sqlconn->beginTrans();
 197             DbRetVal rv = stmt->prepare(sqlstring);
 198             int rows = 0;
 199             rv = stmt->execute(rows);
 200             void *tuple = NULL;
 201             rows = 0;
 202             while(true) {
 203                 tuple = stmt->fetchAndPrint(true);
 204                 if (tuple == NULL) break;
 205                 rows++;
 206                 if (rows % noOfStmts ==0) {
 207                     sqlconn->commit();
 208                     sqlconn->beginTrans();
 209                     printf("COMMIT;\n");
 210                 }
 211             }
 212             if (rows % noOfStmts !=0) {
 213                 sqlconn->commit();
 214                 printf("COMMIT;\n");
 215             }
 216             stmt->close();
 217             stmt->free();
 218         }
 219         iter.reset();
 220         while (iter.hasElement()) {
 221             elem = (Identifier*) iter.nextElement();
 222             delete elem;
 223         }
 224         tableList.reset();
 225     }
 226     if (opt == 15) {
 227         Connection conn;
 228         DbRetVal rv = conn.open(username, password);
 229         if (rv != OK)  {
 230             printf("Unable to get connection to csql\n");
 231             delete sqlconn;
 232             delete stmt;
 233             return 1;
 234         }
 235         DatabaseManagerImpl *dbMgr = (DatabaseManagerImpl*) conn.getDatabaseManager();
 236         if (dbMgr == NULL) { printf("Auth failed\n"); return 2;}
 237         Table *table = dbMgr->openTable(tblName);
 238         if (table == NULL) {
 239             printf("csqldump: Table \'%s\' does not exist\n", tblName);
 240             conn.close();
 241             delete sqlconn;
 242             delete stmt;
 243             return 3;
 244         }
 245         printf("CREATE TABLE %s (", tblName);
 246             FieldInfo *info = new FieldInfo();
 247         List fNameList = table->getFieldNameList();
 248         ListIterator fNameIter = fNameList.getIterator();
 249         bool firstField=true;
 250         Identifier *elem = NULL;
 251         char fieldName[IDENTIFIER_LENGTH];
 252         while (fNameIter.hasElement()) {
 253             elem = (Identifier*) fNameIter.nextElement();
 254             Table::getFieldNameAlone(elem->name, fieldName);
 255             table->getFieldInfo((const char*)elem->name, info);
 256             if (firstField) {
 257                 printf("%s %s ", fieldName, AllDataType::getSQLString(info->type));
 258                 firstField = false;
 259             }
 260             else
 261                 printf(", %s %s ", fieldName, AllDataType::getSQLString(info->type));
 262             if (info->type == typeString) printf("(%d)",info->length);
 263             if (info->type == typeBinary) printf("(%d)",info->length);
 264             if (info->isNull) printf(" NOT NULL ");
 265             if (info->isDefault) printf(" DEFAULT '%s' ", info->defaultValueBuf);
 266         }
 267         printf(");\n");
 268         table->printSQLIndexString();
 269         delete info;
 270         conn.close();
 271         char sqlstring[1024];
 272       //  char sqlstring1[1024];
 273         bool flag=false;
 274         if (!flag) { printf("SET AUTOCOMMIT OFF;\n"); flag=true; }
 275         rv = sqlconn->connect(I_USER, I_PASS);
 276         if (OK !=rv) {
 277             printf("unable to connect\n");
 278             return 10;
 279         }
 280         stmt->setConnection(sqlconn);
 281
 282         if(Iscondition)
 283            sprintf(sqlstring, "SELECT * FROM %s WHERE %s;", tblName,conditionVal);
 284         else
 285            sprintf(sqlstring, "SELECT * FROM %s;", tblName);
 286            sqlconn->beginTrans();
 287            rv = stmt->prepare(sqlstring);
 288
 289         //***********************************************
 290         int rows = 0;
 291         rv = stmt->execute(rows);
 292         void *tuple = NULL;
 293         rows = 0;
 294         while(true) {
 295             tuple = stmt->fetchAndPrint(true);
 296             if (tuple == NULL) break;
 297             rows++;
 298             if (rows % noOfStmts ==0) {
 299                 sqlconn->commit();
 300                 sqlconn->beginTrans();
 301                 printf("COMMIT;\n");
 302             }
 303         }
 304         if (rows % noOfStmts !=0) { sqlconn->commit(); printf("COMMIT;\n"); }
 305         stmt->close();
 306         stmt->free();
 307     }
 308     sqlconn->disconnect();
 309     delete sqlconn;
 310     delete stmt;
 311
 312     return 0;
 313 }