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         os::signal(SIGCSQL1, SIG_IGN);
 109         DatabaseManagerImpl *dbMgr = (DatabaseManagerImpl*) conn.getDatabaseManager();
 110         if (dbMgr == NULL) {
 111             printf("Unable to retrive db manager\n");
 112             conn.close();
 113             delete stmt;
 114             delete sqlconn;
 115             return 2;
 116         }
 117         List tableList = dbMgr->getAllTableNames();
 118         ListIterator iter = tableList.getIterator();
 119         Identifier *elem = NULL;
 120         int count =0;
 121         while (iter.hasElement()) {
 122             elem = (Identifier*) iter.nextElement();
 123             //if (!exclusive && isCached(elem->name)) continue;
 124             printf("CREATE TABLE %s (", elem->name);
 125             Table *table = dbMgr->openTable(elem->name);
 126                 FieldInfo *info = new FieldInfo();
 127             List fNameList = table->getFieldNameList();
 128             ListIterator fNameIter = fNameList.getIterator();
 129             count++;
 130             bool firstField=true;
 131             Identifier *elem1 = NULL;
 132             char fieldName[IDENTIFIER_LENGTH];
 133             while (fNameIter.hasElement()) {
 134                 elem1 = (Identifier*) fNameIter.nextElement();
 135                 Table::getFieldNameAlone(elem1->name, fieldName);
 136                 rv = table->getFieldInfo(elem1->name, info);
 137                 if (rv !=OK)  {
 138                     printf("unable to retrive info for table %s\n", elem1->name);
 139                     conn.close();
 140                     delete stmt;
 141                     delete sqlconn;
 142                 }
 143                 if (firstField) {
 144                     printf("%s %s ", fieldName, AllDataType::getSQLString(info->type));
 145                     firstField = false;
 146                 } else
 147                     printf(", %s %s ", fieldName, AllDataType::getSQLString(info->type));
 148                 if (info->type == typeString) printf("(%d)",info->length );
 149                 if (info->type == typeBinary) printf("(%d)",info->length);
 150                 if (info->type == typeVarchar) printf("(%d)",info->length);
 151                 if (info->isNull) printf(" NOT NULL ");
 152                 if (info->isDefault) printf(" DEFAULT '%s' ", info->defaultValueBuf);
 153                 if (info->isAutoIncrement) printf(" AUTO_INCREMENT ");
 154             }
 155             fNameIter.reset();
 156             while (fNameIter.hasElement()) {
 157                 elem1 = (Identifier*) fNameIter.nextElement();
 158                 delete elem1;
 159             }
 160             fNameList.reset();
 161             if (table->isFKTable()){
 162                 table->printSQLForeignString();
 163             }
 164             printf(");\n");
 165             table->printSQLIndexString();
 166             delete info;
 167             dbMgr->closeTable(table);
 168         }
 169         conn.close();
 170         if (schema) { delete sqlconn; delete stmt; return 0; }
 171
 172         rv = sqlconn->connect(I_USER, I_PASS);
 173         if (OK !=rv) {
 174             printf("unable to connect to csql\n");
 175             delete sqlconn;
 176             delete stmt;
 177             return 10;
 178         }
 179         stmt->setConnection(sqlconn);
 180         if (exclusive) {
 181             rv = sqlconn->getExclusiveLock();
 182             if (rv != OK) {
 183                 printf("Unable to get exclusive lock\n");
 184                 sqlconn->disconnect();
 185                 delete sqlconn;
 186                 delete stmt;
 187                 return 1;
 188             }
 189         }
 190         iter.reset();
 191         char sqlstring[1024]="";
 192         bool flag=false;
 193         while (iter.hasElement()) {
 194             elem = (Identifier*) iter.nextElement();
 195             //if (!exclusive && isCached(elem->name)) continue;
 196             if (!flag) { printf("SET AUTOCOMMIT OFF;\n"); flag=true; }
 197             sprintf(sqlstring, "SELECT * FROM %s;", elem->name);
 198             sqlconn->beginTrans();
 199             DbRetVal rv = stmt->prepare(sqlstring);
 200             int rows = 0;
 201             rv = stmt->execute(rows);
 202             void *tuple = NULL;
 203             rows = 0;
 204             while(true) {
 205                 tuple = stmt->fetchAndPrint(true);
 206                 if (tuple == NULL) break;
 207                 rows++;
 208                 if (rows % noOfStmts ==0) {
 209                     sqlconn->commit();
 210                     sqlconn->beginTrans();
 211                     printf("COMMIT;\n");
 212                 }
 213             }
 214             if (rows % noOfStmts !=0) {
 215                 sqlconn->commit();
 216                 printf("COMMIT;\n");
 217             }
 218             stmt->close();
 219             stmt->free();
 220         }
 221         iter.reset();
 222         while (iter.hasElement()) {
 223             elem = (Identifier*) iter.nextElement();
 224             delete elem;
 225         }
 226         tableList.reset();
 227     }
 228     if (opt == 15) {
 229         Connection conn;
 230         DbRetVal rv = conn.open(username, password);
 231         if (rv != OK)  {
 232             printf("Unable to get connection to csql\n");
 233             delete sqlconn;
 234             delete stmt;
 235             return 1;
 236         }
 237         os::signal(SIGCSQL1, SIG_IGN);
 238         DatabaseManagerImpl *dbMgr = (DatabaseManagerImpl*) conn.getDatabaseManager();
 239         if (dbMgr == NULL) { printf("Auth failed\n"); return 2;}
 240         Table *table = dbMgr->openTable(tblName);
 241         if (table == NULL) {
 242             printf("csqldump: Table \'%s\' does not exist\n", tblName);
 243             conn.close();
 244             delete sqlconn;
 245             delete stmt;
 246             return 3;
 247         }
 248         printf("CREATE TABLE %s (", tblName);
 249             FieldInfo *info = new FieldInfo();
 250         List fNameList = table->getFieldNameList();
 251         ListIterator fNameIter = fNameList.getIterator();
 252         bool firstField=true;
 253         Identifier *elem = NULL;
 254         char fieldName[IDENTIFIER_LENGTH];
 255         while (fNameIter.hasElement()) {
 256             elem = (Identifier*) fNameIter.nextElement();
 257             Table::getFieldNameAlone(elem->name, fieldName);
 258             table->getFieldInfo((const char*)elem->name, info);
 259             if (firstField) {
 260                 printf("%s %s ", fieldName, AllDataType::getSQLString(info->type));
 261                 firstField = false;
 262             }
 263             else
 264                 printf(", %s %s ", fieldName, AllDataType::getSQLString(info->type));
 265             if (info->type == typeString) printf("(%d)",info->length);
 266             if (info->type == typeBinary) printf("(%d)",info->length);
 267             if (info->isNull) printf(" NOT NULL ");
 268             if (info->isDefault) printf(" DEFAULT '%s' ", info->defaultValueBuf);
 269         }
 270         printf(");\n");
 271         table->printSQLIndexString();
 272         delete info;
 273         conn.close();
 274         char sqlstring[1024];
 275       //  char sqlstring1[1024];
 276         bool flag=false;
 277         if (!flag) { printf("SET AUTOCOMMIT OFF;\n"); flag=true; }
 278         rv = sqlconn->connect(I_USER, I_PASS);
 279         if (OK !=rv) {
 280             printf("unable to connect\n");
 281             return 10;
 282         }
 283         stmt->setConnection(sqlconn);
 284
 285         if(Iscondition)
 286            sprintf(sqlstring, "SELECT * FROM %s WHERE %s;", tblName,conditionVal);
 287         else
 288            sprintf(sqlstring, "SELECT * FROM %s;", tblName);
 289            sqlconn->beginTrans();
 290            rv = stmt->prepare(sqlstring);
 291
 292         //***********************************************
 293         int rows = 0;
 294         rv = stmt->execute(rows);
 295         void *tuple = NULL;
 296         rows = 0;
 297         while(true) {
 298             tuple = stmt->fetchAndPrint(true);
 299             if (tuple == NULL) break;
 300             rows++;
 301             if (rows % noOfStmts ==0) {
 302                 sqlconn->commit();
 303                 sqlconn->beginTrans();
 304                 printf("COMMIT;\n");
 305             }
 306         }
 307         if (rows % noOfStmts !=0) { sqlconn->commit(); printf("COMMIT;\n"); }
 308         stmt->close();
 309         stmt->free();
 310     }
 311     sqlconn->disconnect();
 312     delete sqlconn;
 313     delete stmt;
 314
 315     return 0;
 316 }