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 1. 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,
  49                                             fieldname,condition,field,dsnName);
  50         if (strcmp (ctablename, tblName) == 0) { isCached=true; break; }
  51     }
  52     fclose(fp);
  53     return isCached;
  54 }
  55
  56 SqlConnection *sqlconn;
  57 SqlStatement *stmt;
  58
  59 int main(int argc, char **argv)
  60 {
  61     char username[IDENTIFIER_LENGTH];
  62     username [0] = '\0';
  63     char password[IDENTIFIER_LENGTH];
  64     password [0] = '\0';
  65     char tblName[IDENTIFIER_LENGTH];
  66     char conditionVal[IDENTIFIER_LENGTH];
  67     int c = 0, opt = 0;
  68     int noOfStmts =1;
  69     bool exclusive = false;
  70     bool Iscondition=false;
  71     bool schema = false;
  72     char name[IDENTIFIER_LENGTH];
  73     while ((c = getopt(argc, argv, "u:p:n:T:c:XS?")) != EOF) {
  74         switch (c) {
  75             case 'u' : { strcpy(username, argv[optind - 1]); opt=1; break; }
  76             case 'p' : { strcpy(password, argv[optind - 1]); opt=1; break; }
  77             case 'n' : { noOfStmts = atoi(argv[optind - 1]); opt = 5; break; }
  78             case 'T' : { strcpy(tblName,  argv[optind - 1]); opt = 15; break; }
  79             case 'c' : {
  80                            strcpy(conditionVal,argv[optind -1]);
  81                            Iscondition=true;break;
  82                        }
  83                 case 'X' : { exclusive = true; break; }
  84             case 'S' : { schema = true; break; }
  85             case '?' : { opt = 10; break; } //print help
  86             default: opt=1; //list all the tables
  87
  88         }
  89     }//while options
  90     if (opt == 10) {
  91         printUsage();
  92         return 0;
  93     }
  94
  95     //printf("%s %s \n", username, password);
  96     if (username[0] == '\0' ) {
  97         strcpy(username, I_USER);
  98         strcpy(password, I_PASS);
  99     }
 100     sqlconn = (SqlConnection*) SqlFactory::createConnection(CSqlDirect);
 101     DbRetVal rv = sqlconn->connect(username, password);
 102     if (rv != OK)  {
 103         printf("Unable to get connection to csql\n");
 104         delete sqlconn; delete stmt; return 1;
 105     }
 106     stmt = (SqlStatement*) SqlFactory::createStatement(CSqlDirect);
 107     stmt->setSqlConnection(sqlconn);
 108     if (exclusive) { stmt->setLoading(true); }
 109     if (opt == 0 || opt == 1) opt = 5;
 110     if (opt == 5) {
 111         os::signal(SIGCSQL1, SIG_IGN);
 112         DatabaseManagerImpl *dbMgr = (DatabaseManagerImpl*)
 113                                  sqlconn->getConnObject().getDatabaseManager();
 114         if (dbMgr == NULL) {
 115             printf("Unable to retrive db manager\n");
 116             sqlconn->disconnect(); delete stmt; delete sqlconn; return 2;
 117         }
 118         List tableList = dbMgr->getAllTableNames();
 119         ListIterator iter = tableList.getIterator();
 120         Identifier *elem = NULL;
 121         int count =0;
 122         while (iter.hasElement()) {
 123             elem = (Identifier*) iter.nextElement();
 124             //if (!exclusive && isCached(elem->name)) continue;
 125             printf("CREATE TABLE %s (", elem->name);
 126             Table *table = dbMgr->openTable(elem->name);
 127             if (NULL == table) {
 128                 printError(ErrSysInternal,
 129                                         "Unable to open table %s", elem->name);
 130                 break;
 131             }
 132                 FieldInfo *info = new FieldInfo();
 133             List fNameList = table->getFieldNameList();
 134             ListIterator fNameIter = fNameList.getIterator();
 135             count++;
 136             bool firstField=true;
 137             Identifier *elem1 = NULL;
 138             char fieldName[IDENTIFIER_LENGTH];
 139             while (fNameIter.hasElement()) {
 140                 elem1 = (Identifier*) fNameIter.nextElement();
 141                 Table::getFieldNameAlone(elem1->name, fieldName);
 142                 rv = table->getFieldInfo(elem1->name, info);
 143                 if (rv !=OK)  {
 144                     printf("unable to retrive info for table %s\n",
 145                                                                   elem1->name);
 146                     sqlconn->disconnect(); delete stmt; delete sqlconn;
 147                     return 3;
 148                 }
 149                 if (firstField) {
 150                     printf("%s %s ", fieldName,
 151                                         AllDataType::getSQLString(info->type));
 152                     firstField = false;
 153                 } else
 154                     printf(", %s %s ", fieldName,
 155                                         AllDataType::getSQLString(info->type));
 156                 if (info->type == typeString) printf("(%d)",info->length );
 157                 if (info->type == typeBinary) printf("(%d)",info->length);
 158                 if (info->type == typeVarchar) printf("(%d)",info->length);
 159                 if (info->isNull) printf(" NOT NULL ");
 160                 if (info->isDefault) printf(" DEFAULT '%s' ",
 161                                                         info->defaultValueBuf);
 162                 if (info->isAutoIncrement) printf(" AUTO_INCREMENT ");
 163             }
 164             fNameIter.reset();
 165             while (fNameIter.hasElement()) {
 166                 elem1 = (Identifier*) fNameIter.nextElement();
 167                 delete elem1;
 168             }
 169             fNameList.reset();
 170             if (table->isFKTable()){
 171                 table->printSQLForeignString();
 172             }
 173             printf(");\n");
 174             table->printSQLIndexString();
 175             delete info;
 176             dbMgr->closeTable(table);
 177         }
 178         if (schema) {
 179             sqlconn->disconnect();
 180             delete sqlconn; delete stmt; return 0;
 181         }
 182 /*
 183         rv = sqlconn->connect(I_USER, I_PASS);
 184         if (OK !=rv) {
 185             printf("unable to connect to csql\n");
 186             delete sqlconn;
 187             delete stmt;
 188             return 10;
 189         }
 190 */
 191         stmt->setSqlConnection(sqlconn);
 192         if (exclusive) {
 193             rv = sqlconn->getExclusiveLock();
 194             if (rv != OK) {
 195                 printf("Unable to get exclusive lock\n");
 196                 sqlconn->disconnect(); delete sqlconn; delete stmt; return 4;
 197             }
 198         }
 199         iter.reset();
 200         char sqlstring[1024]="";
 201         bool flag=false;
 202         while (iter.hasElement()) {
 203             elem = (Identifier*) iter.nextElement();
 204             //if (!exclusive && isCached(elem->name)) continue;
 205             if (!flag) {
 206                 if (noOfStmts !=1) printf("SET AUTOCOMMIT OFF;\n");
 207                 flag=true;
 208             }
 209             sprintf(sqlstring, "SELECT * FROM %s;", elem->name);
 210             sqlconn->beginTrans();
 211             DbRetVal rv = stmt->prepare(sqlstring);
 212             int rows = 0;
 213             rv = stmt->execute(rows);
 214             void *tuple = NULL;
 215             rows = 0;
 216             while(true) {
 217                 tuple = stmt->fetchAndPrint(true);
 218                 if (tuple == NULL) break;
 219                 rows++;
 220                 if (rows % noOfStmts ==0) {
 221                     sqlconn->commit();
 222                     sqlconn->beginTrans();
 223                     if (noOfStmts !=1) printf("COMMIT;\n");
 224                 }
 225             }
 226             if (rows % noOfStmts !=0) {
 227                 sqlconn->commit();
 228                 if (noOfStmts !=1) printf("COMMIT;\n");
 229             }
 230             stmt->close();
 231             stmt->free();
 232         }
 233         iter.reset();
 234         while (iter.hasElement()) {
 235             elem = (Identifier*) iter.nextElement();
 236             delete elem;
 237         }
 238         tableList.reset();
 239     }
 240     if (opt == 15) {
 241         os::signal(SIGCSQL1, SIG_IGN);
 242         DatabaseManagerImpl *dbMgr = (DatabaseManagerImpl*)
 243                                  sqlconn->getConnObject().getDatabaseManager();
 244         if (dbMgr == NULL) {
 245             printf("Unable to retrive db manager\n");
 246             sqlconn->disconnect(); delete stmt; delete sqlconn; return 5;
 247         }
 248         Table *table = dbMgr->openTable(tblName);
 249         if (table == NULL) {
 250             printf("csqldump: Table \'%s\' does not exist\n", tblName);
 251             sqlconn->disconnect(); delete stmt; delete sqlconn; return 6;
 252         }
 253         printf("CREATE TABLE %s (", tblName);
 254             FieldInfo *info = new FieldInfo();
 255         List fNameList = table->getFieldNameList();
 256         ListIterator fNameIter = fNameList.getIterator();
 257         bool firstField=true;
 258         Identifier *elem = NULL;
 259         char fieldName[IDENTIFIER_LENGTH];
 260         while (fNameIter.hasElement()) {
 261             elem = (Identifier*) fNameIter.nextElement();
 262             Table::getFieldNameAlone(elem->name, fieldName);
 263             table->getFieldInfo((const char*)elem->name, info);
 264             if (firstField) {
 265                 printf("%s %s ", fieldName,
 266                                         AllDataType::getSQLString(info->type));
 267                 firstField = false;
 268             }
 269             else
 270                 printf(", %s %s ", fieldName,
 271                                         AllDataType::getSQLString(info->type));
 272             if (info->type == typeString) printf("(%d)",info->length);
 273             if (info->type == typeBinary) printf("(%d)",info->length);
 274             if (info->isNull) printf(" NOT NULL ");
 275             if (info->isDefault) printf(" DEFAULT '%s' ",
 276                                                         info->defaultValueBuf);
 277         }
 278         printf(");\n");
 279         table->printSQLIndexString();
 280         delete info;
 281         char sqlstring[SQL_STMT_LEN];
 282         if (noOfStmts != 1) printf("SET AUTOCOMMIT OFF;\n");
 283
 284         if(Iscondition)
 285             sprintf(sqlstring, "SELECT * FROM %s WHERE %s;", tblName,
 286                                                              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                 if (noOfStmts !=1) printf("COMMIT;\n");
 305             }
 306         }
 307         if (rows % noOfStmts !=0) {
 308             sqlconn->commit();
 309             if (noOfStmts !=1) printf("COMMIT;\n");
 310         }
 311         stmt->close();
 312         stmt->free();
 313     }
 314     sqlconn->disconnect();
 315     delete sqlconn;
 316     delete stmt;
 317     return 0;
 318 }