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