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