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