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