src/tools/cacheverify.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 <SqlFactory.h>
  19 #include <SqlStatement.h>
  20 #include <SqlOdbcConnection.h>
  21 #include <SqlOdbcStatement.h>
  22
  23 AbsSqlConnection *conn=NULL;
  24 AbsSqlStatement *stmt=NULL;
  25 DataType pkFldType = typeUnknown;
  26 int pkFldLen = 0;
  27 void printUsage()
  28 {
  29    printf("Usage: cacheverify [-U username] [-P passwd] -t tablename [-p] [-f]\n");
  30    printf("       username -> username to connect with csql.\n");
  31    printf("       passwd -> password for the above username to connect with csql.\n");
  32    printf("       tablename -> cached table name in csql from target db.\n");
  33    printf("       p -> verification at primary key field level\n");
  34    printf("       f -> verification at record level\n");
  35    printf("       ? -> help\n");
  36    return;
  37 }
  38
  39 typedef struct PrimaryKeyField {
  40     bool inCsql;
  41     bool inTrgtDb;
  42     char val[1];
  43 } PrimKeyFldVal;
  44
  45 typedef struct FldVal {
  46     void *value;
  47     DataType type;
  48     int length;
  49     int pos;
  50 }FldVal;
  51
  52 typedef struct Rec {
  53     List csqlFldValList;
  54     List tdbFldValList;
  55     char val[1];
  56 }Record;
  57
  58 int cmpIntPkFldVal (const void *pkfv1, const void *pkfv2)
  59 {
  60     PrimKeyFldVal *p1 = (PrimKeyFldVal *)pkfv1;
  61     PrimKeyFldVal *p2 = (PrimKeyFldVal *)pkfv2;
  62     bool result = false;
  63     result=AllDataType::compareVal(&p1->val,&p2->val,OpLessThan,pkFldType);
  64     if (result) return -1;
  65     else return 1;
  66 }
  67
  68 int cmpStringPkFldVal (const void *pkfv1, const void *pkfv2)
  69 {
  70     PrimKeyFldVal *p1 = (PrimKeyFldVal *)pkfv1;
  71     PrimKeyFldVal *p2 = (PrimKeyFldVal *)pkfv2;
  72     char *val1 = (char *) &p1->val;
  73     char *val2 = (char *) &p2->val;
  74     if (strcmp(val1, val2) < 0) return -1;
  75     else return 1;
  76 }
  77
  78 int cmpIntRecord (const void *pkfv1, const void *pkfv2)
  79 {
  80     Record *p1 = (Record *)pkfv1;
  81     Record *p2 = (Record *)pkfv2;
  82     if (AllDataType::compareVal(&p1->val, &p2->val, OpLessThan, pkFldType))
  83         return -1;
  84     else return 1;
  85 }
  86
  87 int cmpStringRecord (const void *pkfv1, const void *pkfv2)
  88 {
  89     Record *p1 = (Record *)pkfv1;
  90     Record *p2 = (Record *)pkfv2;
  91     char *val1 = (char *) &p1->val;
  92     char *val2 = (char *) &p2->val;
  93     if (strcmp(val1, val2) < 0) return -1;
  94     else return 1;
  95 }
  96
  97 void setParamValues(AbsSqlStatement *stmt, int parampos, DataType type, int length, void *value);
  98
  99 DbRetVal verifyCount(const char *tblName, long numTuples)
 100 {
 101     char statement[200];
 102     AbsSqlConnection *adConn = SqlFactory::createConnection(CSqlAdapter);
 103     DbRetVal rv = adConn->connect(I_USER,I_PASS);
 104     if (rv != OK) { delete adConn; return ErrSysInit; }
 105     AbsSqlStatement *adStmt = SqlFactory::createStatement(CSqlAdapter);
 106     adStmt->setConnection(adConn);
 107     float count = 0;
 108     int count1=0;
 109     int rows = 0;
 110     sprintf(statement, "select count(*) from %s;", tblName);
 111     rv = adConn->beginTrans();
 112     rv = adStmt->prepare(statement);
 113     if(rv != OK) {
 114         delete adStmt; delete adConn;
 115         printf("Prepare failed\n");
 116         return rv;
 117     }
 118     SqlOdbcConnection *adcon= (SqlOdbcConnection *)adConn;
 119     adStmt->bindField(1, &count1);
 120     rv  = adStmt->execute(rows);
 121     if(rv != OK) {
 122         delete adStmt; delete adConn;
 123         printf("Execute failed\n");
 124         return rv;
 125     }
 126     if (adStmt->fetch()== NULL) {
 127         delete adStmt; delete adConn;
 128         printf("Fetch failed\n");
 129         return ErrSysInternal;
 130     }
 131     adConn->commit();
 132     adStmt->free();
 133     printf("\nNumber of Records:\n");
 134     printf("-------------------+-------------------+-------------------+\n");
 135     printf("  Data             |  In CSQL          |  In TargetDB      |\n");
 136     printf("-------------------+-------------------+-------------------+\n");
 137     printf("  No. Of Records   |  %-6ld           |  %-6d           |\n", numTuples, count1);
 138     printf("-------------------+-------------------+-------------------+\n");
 139     delete adStmt; delete adConn;
 140     return OK;
 141 }
 142
 143 DbRetVal verifyMismatchingRecords(const char *tblName, int option)
 144 {
 145     char csqlstatement[256];
 146     char tdbstatement[256];
 147     AbsSqlConnection *trgtDbCon = SqlFactory::createConnection(CSqlAdapter);
 148     DbRetVal rv = trgtDbCon->connect(I_USER,I_PASS);
 149     if (rv != OK) {
 150         delete trgtDbCon; return ErrSysInit;
 151     }
 152     AbsSqlStatement *trgtDbStmt =  SqlFactory::createStatement(CSqlAdapter);
 153     trgtDbStmt->setConnection(trgtDbCon);
 154
 155     char fieldName[IDENTIFIER_LENGTH];
 156     fieldName[0] = '\0';
 157     SqlOdbcStatement *ostmt = (SqlOdbcStatement*) trgtDbStmt;
 158     ostmt->getPrimaryKeyFieldName((char*)tblName, fieldName);
 159     if (fieldName[0] == '\0') {
 160         delete trgtDbStmt; delete trgtDbCon;
 161         printf("Primary key does not exist on table %s\n", tblName);
 162         return ErrNotExists;
 163     }
 164     printf("\nPrimary key field name is \'%s\'\n", fieldName);
 165     int rows = 0;
 166
 167     //will work for single field primary keys
 168     //composite need to be worked out
 169     FieldInfo *fInfo = new FieldInfo();
 170     ((SqlStatement *)stmt)->getFieldInfo(tblName, fieldName, fInfo);
 171     pkFldType = fInfo->type;
 172     if (pkFldType == typeString) pkFldLen = os::align(fInfo->length + 1);
 173     else pkFldLen = fInfo->length;
 174     void *pkval = AllDataType::alloc(pkFldType, pkFldLen);
 175     memset(pkval, 0, pkFldLen);
 176     //List for primary key field values present in csql server
 177     List valListInCsql;
 178     List valListInTrgtDb;
 179     List sameInBothDb;
 180     List missingList;
 181
 182     sprintf(csqlstatement, "select %s from %s;", fieldName, tblName);
 183     sprintf(tdbstatement, "select %s from %s where %s=?;", fieldName, tblName, fieldName);
 184     rv = stmt->prepare(csqlstatement);
 185     if (rv != OK) {
 186         delete trgtDbStmt; delete trgtDbCon;
 187         printError(rv, "Prepare failed");
 188         return rv;
 189     }
 190     rv = trgtDbStmt->prepare(tdbstatement);
 191     if (rv != OK) {
 192         delete trgtDbStmt; delete trgtDbCon;
 193         printError(rv, "Prepare failed");
 194         return rv;
 195     }
 196     stmt->bindField(1, pkval);
 197     trgtDbStmt->bindField(1, pkval);
 198     rv = conn->beginTrans();
 199     if (rv != OK) {
 200         stmt->free();
 201         delete trgtDbStmt; delete trgtDbCon;
 202         printError(rv, "BeginTrans failed");
 203         return rv;
 204     }
 205     rv = stmt->execute(rows);
 206     if(rv != OK) {
 207         stmt->free();
 208         delete trgtDbStmt; delete trgtDbCon;
 209         printf("Execute failed\n");
 210         return rv;
 211     }
 212     rv = trgtDbCon->beginTrans();
 213     int pkFldValSize = sizeof(PrimKeyFldVal) - 1 + pkFldLen;
 214     while(stmt->fetch(rv) != NULL && rv == OK) {
 215         PrimKeyFldVal *pkFldVal = (PrimKeyFldVal *) malloc (pkFldValSize);
 216         memset(pkFldVal, 0, pkFldValSize);
 217         AllDataType::copyVal(&pkFldVal->val, pkval, pkFldType, pkFldLen);
 218         pkFldVal->inCsql = true;
 219         pkFldVal->inTrgtDb = true;
 220         setParamValues(trgtDbStmt, 1, pkFldType, pkFldLen, pkval);
 221         trgtDbStmt->execute(rows);
 222         if (trgtDbStmt->fetch(rv) != NULL) {
 223             sameInBothDb.append(pkFldVal);
 224         } else {
 225             pkFldVal->inTrgtDb = false;
 226             missingList.append(pkFldVal);
 227         }
 228         trgtDbStmt->close();
 229     }
 230     trgtDbCon->commit();
 231     stmt->close();
 232     conn->commit();
 233     stmt->free();
 234     trgtDbStmt->free();
 235
 236     // List for primary key field values present in target DB
 237     sprintf(tdbstatement, "select %s from %s;", fieldName, tblName);
 238     sprintf(csqlstatement, "select %s from %s where %s=?;", fieldName, tblName, fieldName);
 239     rv = trgtDbCon->beginTrans();
 240     rv = trgtDbStmt->prepare(tdbstatement);
 241     if(rv != OK) {
 242         delete trgtDbStmt; delete trgtDbCon;
 243         printf("Prepare failed\n");
 244         return rv;
 245     }
 246     stmt->prepare(csqlstatement);
 247     stmt->bindField(1, pkval);
 248     trgtDbStmt->bindField(1, pkval);
 249     rv  = trgtDbStmt->execute(rows);
 250     if(rv != OK) {
 251         delete trgtDbStmt; delete trgtDbCon;
 252         printf("Execute failed\n");
 253         return rv;
 254     }
 255     conn->beginTrans();
 256     while (trgtDbStmt->fetch() != NULL) {
 257         PrimKeyFldVal *pkFldVal = (PrimKeyFldVal *) malloc (pkFldValSize);
 258         memset(pkFldVal, 0, pkFldValSize);
 259         AllDataType::copyVal(&pkFldVal->val, pkval, pkFldType, pkFldLen);
 260         pkFldVal->inCsql = true;
 261         pkFldVal->inTrgtDb = true;
 262         setParamValues(stmt, 1, pkFldType, pkFldLen, pkval);
 263         stmt->execute(rows);
 264         if (stmt->fetch(rv) == NULL && rv ==OK) {
 265             pkFldVal->inCsql = false;
 266             missingList.append(pkFldVal);
 267         }
 268     }
 269     stmt->close();
 270     trgtDbStmt->close();
 271     conn->commit();
 272     trgtDbCon->commit();
 273     stmt->free();
 274     trgtDbStmt->free();
 275 /*
 276     PrimKeyFldVal *pkArr = NULL;
 277     ListIterator missIter = missingList.getIterator();
 278     int nEitherDb = missingList.size();
 279     if (nEitherDb) {
 280         pkArr = (PrimKeyFldVal *) malloc(nEitherDb * pkFldValSize);
 281         int i = 0;
 282         char *ptr = (char *)pkArr;
 283         while (missIter.hasElement()) {
 284             PrimKeyFldVal *elm = (PrimKeyFldVal *)missIter.nextElement();
 285             memcpy(ptr, elm, pkFldValSize);
 286             ptr += pkFldValSize;
 287         }
 288         if (pkFldType == typeByteInt || pkFldType == typeShort ||
 289                        pkFldType == typeInt || pkFldType == typeLong ||
 290                                                      pkFldType == typeLongLong)
 291             qsort (pkArr, nEitherDb, pkFldValSize, cmpIntPkFldVal);
 292         else if (pkFldType == typeString)
 293             qsort (pkArr, nEitherDb, pkFldValSize, cmpStringPkFldVal);
 294     }
 295 */
 296     // Sorting the primary key field values present in either of the databases
 297     bool missingRecords = false;
 298     printf("\nMissing Records: Marked by \'X\'\n");
 299         printf("-------------------+-------------------+-------------------+\n");
 300         printf("  Primary Key      |  In CSQL          |  In Target DB     |\n");
 301         printf("-------------------+-------------------+-------------------+\n");
 302 /*    if (missingList.size()) {
 303         char *ptr = (char *) pkArr;
 304         missingRecords = true;
 305         for (int i = 0; i < nEitherDb; i++) {
 306             PrimKeyFldVal *pkFldVal = (PrimKeyFldVal *) ptr;
 307             printf("  ");
 308             int nChrs = AllDataType::printVal(&pkFldVal->val, pkFldType,
 309                                                                      pkFldLen);
 310             nChrs = 17 - nChrs;
 311             while (nChrs-- != 0) printf(" ");
 312             if (pkFldVal->inCsql == false) {
 313                 printf("|  X                |                   |\n");
 314             }
 315             else if (pkFldVal->inTrgtDb == false) {
 316                 printf("|                   |  X                |\n");
 317             }
 318             ptr += pkFldValSize;
 319         }
 320         printf("-------------------+-------------------+-------------------+\n");
 321     }
 322     else {
 323         printf("  No missing Records in either of the databases            |\n");
 324         printf("-------------------+-------------------+-------------------+\n");
 325     }
 326 */
 327
 328
 329
 330
 331
 332
 333
 334     ListIterator missIter = missingList.getIterator();
 335     if (missingList.size()) {
 336         missingRecords = true;
 337         while (missIter.hasElement()) {
 338             PrimKeyFldVal *pkFldVal = (PrimKeyFldVal *) missIter.nextElement();
 339             printf("  ");
 340             int nChrs = AllDataType::printVal(&pkFldVal->val, pkFldType,
 341                                                                      pkFldLen);
 342             nChrs = 17 - nChrs;
 343             while (nChrs-- != 0) printf(" ");
 344             if (pkFldVal->inCsql == false) {
 345                 printf("|  X                |                   |\n");
 346             }
 347             else if (pkFldVal->inTrgtDb == false) {
 348                 printf("|                   |  X                |\n");
 349             }
 350         }
 351         printf("-------------------+-------------------+-------------------+\n");
 352     }
 353     else {
 354         printf("  No missing Records in either of the databases            |\n");
 355         printf("-------------------+-------------------+-------------------+\n");
 356     }
 357
 358     // Need to clean up the mess that is no more required
 359     //free (pkArr);
 360     missIter.reset();
 361     PrimKeyFldVal *pkFldVal = NULL;
 362     while ((pkFldVal = (PrimKeyFldVal *) missIter.nextElement()) != NULL)
 363         free (pkFldVal);
 364     missingList.reset();
 365
 366     if (option == 4) {
 367         //statement to fetch the values from the database
 368         sprintf(csqlstatement, "select * from %s where %s = ?;", tblName, fieldName);
 369         rv = stmt->prepare(csqlstatement);
 370         rv = trgtDbStmt->prepare(csqlstatement);
 371         if(rv != OK) {
 372             delete trgtDbStmt; delete trgtDbCon;
 373             printf("Prepare failed\n");
 374             return rv;
 375         }
 376
 377         // need to bind each field with buffer which is list of field values
 378         SqlStatement *sqlStmt = (SqlStatement *) stmt;
 379         List fldNameList = sqlStmt->getFieldNameList(tblName);
 380         ListIterator iter = fldNameList.getIterator();
 381         Identifier *fname = NULL;
 382         FieldInfo *fldInfo = new FieldInfo();
 383         List cfieldValueList;
 384         List tfieldValueList;
 385
 386         // List to hold all the records that are present in both the databases
 387         List recordList;
 388         int paramPos = 1;
 389         while (iter.hasElement()) {
 390             fname = (Identifier *) iter.nextElement();
 391             if (NULL == fname) {
 392                 delete trgtDbStmt; delete trgtDbCon;
 393                 delete fldInfo;
 394                 printf("Should never happen. Field Name list has NULL\n");
 395                 return ErrSysFatal;
 396             }
 397             rv = sqlStmt->getFieldInfo(tblName, fname->name, fldInfo);
 398             if (ErrNotFound == rv) {
 399                 delete trgtDbStmt; delete trgtDbCon;
 400                 delete fldInfo;
 401                 printf("Field %s does not exist in table\n", fname->name);
 402                 return ErrSyntaxError;
 403             }
 404             FldVal *cfldVal = new FldVal();
 405             FldVal *tfldVal = new FldVal();
 406             cfldVal->type = fldInfo->type;
 407             tfldVal->type = fldInfo->type;
 408             if(cfldVal->type == typeString)
 409                 cfldVal->length = os::align(fldInfo->length + 1);
 410             else cfldVal->length = fldInfo->length;
 411             cfldVal->value = AllDataType::alloc(fldInfo->type, cfldVal->length);
 412             tfldVal->value = AllDataType::alloc(fldInfo->type, cfldVal->length);
 413             memset(cfldVal->value, 0, cfldVal->length);
 414             memset(tfldVal->value, 0, cfldVal->length);
 415             cfieldValueList.append(cfldVal);
 416             tfieldValueList.append(tfldVal);
 417             stmt->bindField(paramPos, cfldVal->value);
 418             trgtDbStmt->bindField(paramPos, tfldVal->value);
 419             paramPos++;
 420         }
 421         delete fldInfo;
 422         iter.reset();
 423         while ((fname=(Identifier *)iter.nextElement())!= NULL) delete fname;
 424         fldNameList.reset();
 425
 426         // WHERE parameter should be binded with the primary key field value of the list that is present in both the databases
 427         int recSize = 2 * sizeof(List) + pkFldLen;
 428         ListIterator sameValIter = sameInBothDb.getIterator();
 429         PrimKeyFldVal *sameElem = NULL;
 430         while((sameElem = (PrimKeyFldVal *)sameValIter.nextElement()) != NULL) {
 431             conn->beginTrans();
 432             trgtDbCon->beginTrans();
 433             setParamValues(stmt, 1, pkFldType, pkFldLen, sameElem->val);
 434             setParamValues(trgtDbStmt, 1, pkFldType, pkFldLen, sameElem->val);
 435             rv  = stmt->execute(rows);
 436             rv  = trgtDbStmt->execute(rows);
 437             if(rv != OK) {
 438                 delete trgtDbStmt; delete trgtDbCon;
 439                 printf("Execute failed\n");
 440                 return rv;
 441             }
 442             if (stmt->fetch() != NULL && trgtDbStmt->fetch() != NULL) {
 443                 Record *rec = (Record *) malloc(recSize);
 444                 memset(rec, 0, recSize);
 445                 AllDataType::copyVal(&rec->val, &sameElem->val,
 446                                                           pkFldType, pkFldLen);
 447                 ListIterator cfldValIter = cfieldValueList.getIterator();
 448                 ListIterator tfldValIter = tfieldValueList.getIterator();
 449                 int pos = 1;
 450                 while (cfldValIter.hasElement() && tfldValIter.hasElement()) {
 451                     FldVal *cfldVal = (FldVal *) cfldValIter.nextElement();
 452                     FldVal *tfldVal = (FldVal *) tfldValIter.nextElement();
 453                     if (AllDataType::compareVal(cfldVal->value, tfldVal->value, OpEquals, cfldVal->type, cfldVal->length) == false) {
 454                         FldVal *cfldValue = new FldVal();
 455                         FldVal *tfldValue = new FldVal();
 456                         cfldValue->type = cfldVal->type;
 457                         tfldValue->type = cfldVal->type;
 458                         cfldValue->length = cfldVal->length;
 459                         tfldValue->length = cfldVal->length;
 460                         cfldValue->value = AllDataType::alloc(cfldValue->type, cfldValue->length);
 461                         tfldValue->value = AllDataType::alloc(cfldValue->type, cfldValue->length);
 462                         cfldValue->pos = pos; tfldValue->pos = pos;
 463                         memset(cfldValue->value, 0, cfldValue->length);
 464                         memset(tfldValue->value, 0, cfldValue->length);
 465                         AllDataType::cachecopyVal(cfldValue->value, cfldVal->value, cfldVal->type, cfldVal->length);
 466                         AllDataType::cachecopyVal(tfldValue->value, tfldVal->value, cfldVal->type, cfldVal->length);
 467                         rec->csqlFldValList.append(cfldValue);
 468                         rec->tdbFldValList.append(tfldValue);
 469                     }
 470                     pos++;
 471                 }
 472                 if (rec->csqlFldValList.size()) recordList.append(rec);
 473             }
 474             stmt->close();
 475             trgtDbStmt->close();
 476             conn->commit();
 477             trgtDbCon->commit();
 478         }
 479         // stmt->free(); // dont free it just yet needed further up
 480         trgtDbStmt->free();
 481
 482         // freeing the field value list that is present in both the databases
 483         PrimKeyFldVal *pkFldVal = NULL;
 484         while ((pkFldVal = (PrimKeyFldVal *) sameValIter.nextElement()) != NULL)
 485             delete pkFldVal;
 486         sameInBothDb.reset();
 487 /*
 488         // sort the records based on Primary key that is present in both the databases
 489         int size = recordList.size();
 490         char *arr = (char *) malloc(size * recSize);
 491         memset(arr, 0, size * recSize);
 492         char *ptr = arr;
 493         int i = 0;
 494         ListIterator recIter = recordList.getIterator();
 495         while (recIter.hasElement()) {
 496             Record *rec = (Record *) recIter.nextElement();
 497             memcpy(ptr, rec, recSize);
 498             ptr += recSize;
 499         }
 500         if (pkFldType == typeByteInt || pkFldType == typeShort ||
 501                         pkFldType == typeInt || pkFldType == typeLong ||
 502                                                      pkFldType == typeLongLong)
 503             qsort(arr, size, recSize, cmpIntRecord);
 504         else if (pkFldType == typeString)
 505             qsort(arr, size, recSize, cmpStringRecord);
 506 */
 507
 508         int flag = 0;
 509         bool isConsistent = true;
 510         printf("\nInconsistent Records for the same key:\n");
 511         printf("-------------------+-------------------+-------------------+-------------------+\n");
 512         printf("  %-16s |  %-16s |  %-16s |  %-16s |\n", "Primary Key", "Field Name", "In CSQL", "In Trgt DB");
 513         printf("-------------------+-------------------+-------------------+-------------------+\n");
 514 /*        ptr = arr;
 515         char *fldname = NULL;
 516         for (int i = 0; i < size; i++) {
 517             Record *recd = (Record *) ptr;
 518             ListIterator csqlIt = recd->csqlFldValList.getIterator();
 519             ListIterator trgtDbIt = recd->tdbFldValList.getIterator();
 520             flag = 0;
 521             while (csqlIt.hasElement() && trgtDbIt.hasElement()) {
 522                 FldVal *csqlElem = (FldVal *) csqlIt.nextElement();
 523                 FldVal *trgtDbElem = (FldVal *) trgtDbIt.nextElement();
 524                 fldname = ((SqlStatement *) stmt)->getFieldName(csqlElem->pos);
 525                 if (AllDataType::compareVal(csqlElem->value, trgtDbElem->value, OpEquals, csqlElem->type, csqlElem->length) == false) {
 526                     isConsistent = false;
 527                     if (! flag) {
 528                         printf("  ");
 529                         int cnt = AllDataType::printVal(&recd->val,
 530                                                           pkFldType, pkFldLen);
 531                         cnt = 17 - cnt;
 532                         while (cnt-- != 0) printf(" ");
 533                         printf("|  %-16s |  ", fldname);
 534                         flag = 1;
 535                     }
 536                     else printf("                   |  %-16s |  ", fldname);
 537                     int cnt =  AllDataType::printVal(csqlElem->value, csqlElem->type, csqlElem->length);
 538                     cnt = 17 - cnt;
 539                     while (cnt-- != 0)
 540                         printf(" ");
 541                     printf("|  ");
 542                     cnt = AllDataType::printVal(trgtDbElem->value, trgtDbElem->type, trgtDbElem->length);
 543                     cnt = 17 - cnt;
 544                     while (cnt-- != 0)
 545                         printf(" ");
 546                     printf("|\n");
 547                 }
 548             }
 549             ptr += recSize;
 550         }
 551 */
 552         ListIterator recIter = recordList.getIterator();
 553
 554         char *fldname = NULL;
 555         if (recordList.size()) {
 556             isConsistent = false;
 557             while(recIter.hasElement()) {
 558                 Record *recd = (Record *) recIter.nextElement();
 559                 ListIterator csqlIt = recd->csqlFldValList.getIterator();
 560                 ListIterator trgtDbIt = recd->tdbFldValList.getIterator();
 561                 flag = 0;
 562                 while (csqlIt.hasElement()) {
 563                     FldVal *csqlElem = (FldVal *) csqlIt.nextElement();
 564                     FldVal *trgtDbElem = (FldVal *) trgtDbIt.nextElement();
 565                     fldname = ((SqlStatement *) stmt)->getFieldName(csqlElem->pos);
 566                     if (! flag) {
 567                         printf("  ");
 568                         int cnt = AllDataType::printVal(&recd->val,
 569                                                           pkFldType, pkFldLen);
 570                         cnt = 17 - cnt;
 571                         while (cnt-- != 0) printf(" ");
 572                         printf("|  %-16s |  ", fldname);
 573                         flag = 1;
 574                     }
 575                     else printf("                   |  %-16s |  ", fldname);
 576                     int cnt =  AllDataType::printVal(csqlElem->value, csqlElem->type, csqlElem->length);
 577                     cnt = 17 - cnt;
 578                     while (cnt-- != 0) printf(" ");
 579                     printf("|  ");
 580                     cnt = AllDataType::printVal(trgtDbElem->value, trgtDbElem->type, trgtDbElem->length);
 581                     cnt = 17 - cnt;
 582                     while (cnt-- != 0) printf(" ");
 583                     printf("|\n");
 584                 }
 585             }
 586         }
 587
 588         if (isConsistent == true && missingRecords == false)
 589             printf("                  The data is consistent in both the databases                 |\n");
 590         else if (isConsistent == true && missingRecords == true)
 591             printf("           The data is consistent for the records with the same key            |\n");
 592         printf("-------------------+-------------------+-------------------+-------------------+\n");
 593
 594         // clean up all the mess before leaving
 595         stmt->free();
 596         //free(arr);
 597         Record *itm = NULL;
 598         while((itm = (Record *) recIter.nextElement()) != NULL) {
 599             ListIterator cit = (ListIterator) itm->csqlFldValList.getIterator();
 600             ListIterator tit = (ListIterator) itm->tdbFldValList.getIterator();
 601             FldVal *cfldVal = NULL; FldVal *tfldVal = NULL;
 602             while( (cfldVal = (FldVal *) cit.nextElement()) != NULL &&
 603                    (tfldVal = (FldVal *) tit.nextElement()) != NULL ) {
 604                 free (cfldVal->value); free (tfldVal->value);
 605                 delete cfldVal; delete tfldVal;
 606             }
 607             cit.reset(); tit.reset();
 608         }
 609         recordList.reset();
 610     }
 611     delete trgtDbStmt; delete trgtDbCon;
 612     return OK;
 613 }
 614
 615 int main(int argc, char **argv)
 616 {
 617     char username[IDENTIFIER_LENGTH];
 618     username [0] = '\0';
 619     char password[IDENTIFIER_LENGTH];
 620     password [0] = '\0';
 621     int c = 0, opt = 10;
 622     char tableName[IDENTIFIER_LENGTH];
 623     tableName[0] = '\0';
 624     bool tableNameSpecified = false;
 625
 626     while ((c = getopt(argc, argv, "U:P:t:pf?")) != EOF)
 627     {
 628         switch (c)
 629         {
 630             case 'U' : { strcpy(username, argv[optind - 1]); opt=10; break; }
 631             case 'P' : { strcpy(password, argv[optind - 1]); opt=10; break; }
 632             case 't' : { strcpy(tableName, argv[optind - 1]);
 633                          if (opt==10) opt = 2;
 634                          tableNameSpecified = true;
 635                          break;
 636                        }
 637             case 'p' : { opt = 3; break; } //verify at primary key level
 638             case 'f' : { opt = 4; break; } //verify at record level
 639             case '?' : { opt = 10; break; } //print help
 640             default: opt=10;
 641
 642         }
 643     }//while options
 644     if (opt == 10) {
 645         printUsage();
 646         return 0;
 647     }
 648
 649     if (!tableNameSpecified) {
 650         printf("Table name is not specified. Check usage with ?\n");
 651         return 1;
 652     }
 653
 654     //printf("%s %s \n", username, password);
 655     if (username[0] == '\0' )
 656     {
 657         strcpy(username, I_USER);
 658         strcpy(password, I_PASS);
 659     }
 660
 661     conn = SqlFactory::createConnection(CSqlDirect);
 662     DbRetVal rv = conn->connect(username, password);
 663     if (rv != OK) {
 664         printf("Authentication failed\n");
 665         delete conn;
 666         return 1;
 667     }
 668     stmt = SqlFactory::createStatement(CSqlDirect);
 669     stmt->setConnection(conn);
 670     bool found = false;
 671     List tableNameList = stmt->getAllTableNames(rv);
 672     ListIterator it = tableNameList.getIterator();
 673     while (it.hasElement()) {
 674         Identifier *elem = (Identifier *) it.nextElement();
 675         if (strcmp(elem->name, tableName) == 0) {
 676             found = true;
 677             break;
 678         }
 679     }
 680     FILE *fp;
 681     fp = fopen(Conf::config.getTableConfigFile(),"r");
 682     if( fp == NULL ) {
 683         conn->disconnect();
 684         delete stmt; delete conn;
 685         printf("cachetable.conf file does not exist\n");
 686         return 2;
 687     }
 688     char tablename[IDENTIFIER_LENGTH];
 689     char fieldname[IDENTIFIER_LENGTH];
 690     char condition[IDENTIFIER_LENGTH];
 691     char field[IDENTIFIER_LENGTH];
 692     char dsnName[IDENTIFIER_LENGTH];
 693
 694     int mode;
 695     bool isCached = false;
 696     while(!feof(fp)) {
 697         fscanf(fp, "%d %s %s %s %s %s\n", &mode, tablename,fieldname,condition,field,dsnName);
 698         if (strcmp(tableName, tablename) == 0) {
 699             isCached = true;
 700             break;
 701         }
 702     }
 703     fclose(fp);
 704     long numTuples = 0;
 705     int rows;
 706
 707     char statement[200];
 708     sprintf(statement, "select count(*) from %s;", tableName);
 709     rv = stmt->prepare(statement);
 710     if (rv != OK) {
 711         conn->disconnect();
 712         delete stmt; delete conn;
 713         return 3;
 714     }
 715     rv = conn->beginTrans();
 716     if (rv != OK) {
 717         conn->disconnect();
 718         delete stmt; delete conn;
 719         return 4;
 720     }
 721     stmt->bindField(1, &numTuples);
 722     stmt->execute(rows);
 723     if (rv != OK) {
 724         conn->disconnect();
 725         delete stmt; delete conn;
 726         return 5;
 727     }
 728     void *tuple = stmt->fetch(rv);
 729     stmt->close();
 730     conn->commit();
 731     stmt->free();
 732
 733     if (isCached == false) {
 734         conn->disconnect();
 735         printf("The table \'%s\' is not cached\n", tableName);
 736         delete stmt; delete conn; return 5;
 737     }
 738
 739     if (opt == 2) {
 740         rv = verifyCount(tableName, numTuples);
 741         if (rv != OK) {
 742             conn->disconnect(); delete stmt; delete conn; return 7;
 743         }
 744     }
 745
 746     if (opt == 3 || opt == 4) {
 747         rv = verifyCount(tableName, numTuples);
 748         if (rv != OK) {
 749             conn->disconnect(); delete stmt; delete conn; return 8;
 750         }
 751         rv = verifyMismatchingRecords(tableName, opt);
 752         if (rv != OK) {
 753             conn->disconnect(); delete stmt; delete conn; return 9;
 754         }
 755     }
 756     conn->disconnect(); delete stmt; delete conn;
 757     return 0;
 758 }
 759
 760 void setParamValues(AbsSqlStatement *stmt, int parampos, DataType type, int length, void *value)
 761 {
 762     switch(type)
 763     {
 764         case typeInt:
 765             stmt->setIntParam(parampos, *(int*)value);
 766             break;
 767         case typeLong:
 768             stmt->setLongParam(parampos, *(long*)value);
 769             break;
 770         case typeLongLong:
 771             stmt->setLongLongParam(parampos, *(long long*)value);
 772             break;
 773         case typeShort:
 774             stmt->setShortParam(parampos, *(short*)value);
 775             break;
 776         case typeByteInt:
 777             stmt->setByteIntParam(parampos, *(char*)value);
 778             break;
 779         case typeDouble:
 780             stmt->setDoubleParam(parampos, *(double*)value);
 781             break;
 782         case typeFloat:
 783             stmt->setFloatParam(parampos, *(float*)value);
 784             break;
 785         case typeDate:
 786             stmt->setDateParam(parampos, *(Date*)value);
 787             break;
 788         case typeTime:
 789             stmt->setTimeParam(parampos, *(Time*)value);
 790             break;
 791         case typeTimeStamp:
 792             stmt->setTimeStampParam(parampos, *(TimeStamp*)value);
 793             break;
 794         case typeString:
 795             {
 796                 char *d =(char*)value;
 797                 d[length-1] = '\0';
 798                 stmt->setStringParam(parampos, (char*)value);
 799                 break;
 800             }
 801         case typeBinary:
 802             stmt->setBinaryParam(parampos, (char *) value, length);
 803             break;
 804     }
 805     return;
 806 }
 807