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     long long count1=0;
 108     int rows = 0;
 109     sprintf(statement, "select count(*) from %s;", tblName);
 110     rv = adConn->beginTrans();
 111     rv = adStmt->prepare(statement);
 112     if(rv != OK) {
 113         delete adStmt; 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         delete adStmt; delete adConn;
 122         printError(rv, "Execute failed");
 123         return rv;
 124     }
 125     if (adStmt->fetch()== NULL) {
 126         delete adStmt; delete adConn;
 127         printError(ErrSysInternal, "Fetch failed");
 128         return ErrSysInternal;
 129     }
 130     adConn->commit();
 131     adStmt->free();
 132     printf("\nNumber of Records:\n");
 133     printf("-------------------+-------------------+-------------------+\n");
 134     printf("  Data             |  In CSQL          |  In TargetDB      |\n");
 135     printf("-------------------+-------------------+-------------------+\n");
 136     printf("  No. Of Records   |  %-6ld           |  %-6ld          |\n", numTuples, count1);
 137     printf("-------------------+-------------------+-------------------+\n");
 138     delete adStmt; delete adConn;
 139     return OK;
 140 }
 141
 142 DbRetVal verifyMismatchingRecords(const char *tblName, int option)
 143 {
 144     char csqlstatement[256];
 145     char tdbstatement[256];
 146     AbsSqlConnection *trgtDbCon = SqlFactory::createConnection(CSqlAdapter);
 147     DbRetVal rv = trgtDbCon->connect(I_USER,I_PASS);
 148     if (rv != OK) {
 149         delete trgtDbCon; return ErrSysInit;
 150     }
 151     AbsSqlStatement *trgtDbStmt =  SqlFactory::createStatement(CSqlAdapter);
 152     trgtDbStmt->setConnection(trgtDbCon);
 153
 154     char fieldName[IDENTIFIER_LENGTH];
 155     fieldName[0] = '\0';
 156     SqlOdbcStatement *ostmt = (SqlOdbcStatement*) trgtDbStmt;
 157     ostmt->getPrimaryKeyFieldName((char*)tblName, fieldName);
 158     if (fieldName[0] == '\0') {
 159         delete trgtDbStmt; delete trgtDbCon;
 160         printError(ErrSysInternal, "Primary key does not exist on table %s", tblName);
 161         return ErrNotExists;
 162     }
 163     printf("\nPrimary key field name is \'%s\'\n", fieldName);
 164     int rows = 0;
 165
 166     //will work for single field primary keys
 167     //composite need to be worked out
 168     FieldInfo *fInfo = new FieldInfo();
 169     ((SqlStatement *)stmt)->getFieldInfo(tblName, fieldName, fInfo);
 170     pkFldType = fInfo->type;
 171     if (pkFldType == typeString) pkFldLen = os::align(fInfo->length + 1);
 172     else pkFldLen = fInfo->length;
 173     void *pkval = AllDataType::alloc(pkFldType, pkFldLen);
 174     memset(pkval, 0, pkFldLen);
 175     //List for primary key field values present in csql server
 176     List valListInCsql;
 177     List valListInTrgtDb;
 178     List sameInBothDb;
 179     List missingList;
 180
 181     sprintf(csqlstatement, "select %s from %s;", fieldName, tblName);
 182     sprintf(tdbstatement, "select %s from %s where %s=?;", fieldName, tblName, fieldName);
 183     rv = stmt->prepare(csqlstatement);
 184     if (rv != OK) {
 185         delete trgtDbStmt; delete trgtDbCon;
 186         printError(rv, "Prepare failed");
 187         return rv;
 188     }
 189     rv = trgtDbStmt->prepare(tdbstatement);
 190     if (rv != OK) {
 191         delete trgtDbStmt; delete trgtDbCon;
 192         printError(rv, "Prepare failed");
 193         return rv;
 194     }
 195     stmt->bindField(1, pkval);
 196     trgtDbStmt->bindField(1, pkval);
 197     rv = conn->beginTrans();
 198     if (rv != OK) {
 199         stmt->free();
 200         delete trgtDbStmt; delete trgtDbCon;
 201         printError(rv, "BeginTrans failed");
 202         return rv;
 203     }
 204     rv = stmt->execute(rows);
 205     if(rv != OK) {
 206         stmt->free();
 207         delete trgtDbStmt; delete trgtDbCon;
 208         printError(ErrSysInternal, "Execute failed");
 209         return rv;
 210     }
 211     rv = trgtDbCon->beginTrans();
 212     int pkFldValSize = sizeof(PrimKeyFldVal) - 1 + pkFldLen;
 213     while(stmt->fetch(rv) != NULL && rv == OK) {
 214         PrimKeyFldVal *pkFldVal = (PrimKeyFldVal *) malloc (pkFldValSize);
 215         memset(pkFldVal, 0, pkFldValSize);
 216         AllDataType::copyVal(&pkFldVal->val, pkval, pkFldType, pkFldLen);
 217         pkFldVal->inCsql = true;
 218         pkFldVal->inTrgtDb = true;
 219         setParamValues(trgtDbStmt, 1, pkFldType, pkFldLen, pkval);
 220         trgtDbStmt->execute(rows);
 221         if (trgtDbStmt->fetch(rv) != NULL) {
 222             sameInBothDb.append(pkFldVal);
 223         } else {
 224             pkFldVal->inTrgtDb = false;
 225             missingList.append(pkFldVal);
 226         }
 227         trgtDbStmt->close();
 228     }
 229     trgtDbCon->commit();
 230     stmt->close();
 231     conn->commit();
 232     stmt->free();
 233     trgtDbStmt->free();
 234
 235     // List for primary key field values present in target DB
 236     sprintf(tdbstatement, "select %s from %s;", fieldName, tblName);
 237     sprintf(csqlstatement, "select %s from %s where %s=?;", fieldName, tblName, fieldName);
 238     rv = trgtDbCon->beginTrans();
 239     rv = trgtDbStmt->prepare(tdbstatement);
 240     if(rv != OK) {
 241         delete trgtDbStmt; delete trgtDbCon;
 242         printError(rv, "Prepare failed");
 243         return rv;
 244     }
 245     stmt->prepare(csqlstatement);
 246     stmt->bindField(1, pkval);
 247     trgtDbStmt->bindField(1, pkval);
 248     rv  = trgtDbStmt->execute(rows);
 249     if(rv != OK) {
 250         delete trgtDbStmt; delete trgtDbCon;
 251         printError(rv, "Execute failed\n");
 252         return rv;
 253     }
 254     conn->beginTrans();
 255     while (trgtDbStmt->fetch() != NULL) {
 256         PrimKeyFldVal *pkFldVal = (PrimKeyFldVal *) malloc (pkFldValSize);
 257         memset(pkFldVal, 0, pkFldValSize);
 258         AllDataType::copyVal(&pkFldVal->val, pkval, pkFldType, pkFldLen);
 259         pkFldVal->inCsql = true;
 260         pkFldVal->inTrgtDb = true;
 261         setParamValues(stmt, 1, pkFldType, pkFldLen, pkval);
 262         stmt->execute(rows);
 263         if (stmt->fetch(rv) == NULL && rv ==OK) {
 264             pkFldVal->inCsql = false;
 265             missingList.append(pkFldVal);
 266         }
 267     }
 268     stmt->close();
 269     trgtDbStmt->close();
 270     conn->commit();
 271     trgtDbCon->commit();
 272     stmt->free();
 273     trgtDbStmt->free();
 274 /*
 275     PrimKeyFldVal *pkArr = NULL;
 276     ListIterator missIter = missingList.getIterator();
 277     int nEitherDb = missingList.size();
 278     if (nEitherDb) {
 279         pkArr = (PrimKeyFldVal *) malloc(nEitherDb * pkFldValSize);
 280         int i = 0;
 281         char *ptr = (char *)pkArr;
 282         while (missIter.hasElement()) {
 283             PrimKeyFldVal *elm = (PrimKeyFldVal *)missIter.nextElement();
 284             memcpy(ptr, elm, pkFldValSize);
 285             ptr += pkFldValSize;
 286         }
 287         if (pkFldType == typeByteInt || pkFldType == typeShort ||
 288                        pkFldType == typeInt || pkFldType == typeLong ||
 289                                                      pkFldType == typeLongLong)
 290             qsort (pkArr, nEitherDb, pkFldValSize, cmpIntPkFldVal);
 291         else if (pkFldType == typeString)
 292             qsort (pkArr, nEitherDb, pkFldValSize, cmpStringPkFldVal);
 293     }
 294 */
 295     // Sorting the primary key field values present in either of the databases
 296     bool missingRecords = false;
 297     printf("\nMissing Records: Marked by \'X\'\n");
 298         printf("-------------------+-------------------+-------------------+\n");
 299         printf("  Primary Key      |  In CSQL          |  In Target DB     |\n");
 300         printf("-------------------+-------------------+-------------------+\n");
 301 /*    if (missingList.size()) {
 302         char *ptr = (char *) pkArr;
 303         missingRecords = true;
 304         for (int i = 0; i < nEitherDb; i++) {
 305             PrimKeyFldVal *pkFldVal = (PrimKeyFldVal *) ptr;
 306             printf("  ");
 307             int nChrs = AllDataType::printVal(&pkFldVal->val, pkFldType,
 308                                                                      pkFldLen);
 309             nChrs = 17 - nChrs;
 310             while (nChrs-- != 0) printf(" ");
 311             if (pkFldVal->inCsql == false) {
 312                 printf("|  X                |                   |\n");
 313             }
 314             else if (pkFldVal->inTrgtDb == false) {
 315                 printf("|                   |  X                |\n");
 316             }
 317             ptr += pkFldValSize;
 318         }
 319         printf("-------------------+-------------------+-------------------+\n");
 320     }
 321     else {
 322         printf("  No missing Records in either of the databases            |\n");
 323         printf("-------------------+-------------------+-------------------+\n");
 324     }
 325 */
 326
 327
 328
 329
 330
 331
 332
 333     ListIterator missIter = missingList.getIterator();
 334     if (missingList.size()) {
 335         missingRecords = true;
 336         while (missIter.hasElement()) {
 337             PrimKeyFldVal *pkFldVal = (PrimKeyFldVal *) missIter.nextElement();
 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         }
 350         printf("-------------------+-------------------+-------------------+\n");
 351     }
 352     else {
 353         printf("  No missing Records in either of the databases            |\n");
 354         printf("-------------------+-------------------+-------------------+\n");
 355     }
 356
 357     // Need to clean up the mess that is no more required
 358     //free (pkArr);
 359     missIter.reset();
 360     PrimKeyFldVal *pkFldVal = NULL;
 361     while ((pkFldVal = (PrimKeyFldVal *) missIter.nextElement()) != NULL)
 362         free (pkFldVal);
 363     missingList.reset();
 364
 365     if (option == 4) {
 366         //statement to fetch the values from the database
 367         sprintf(csqlstatement, "select * from %s where %s = ?;", tblName, fieldName);
 368         rv = stmt->prepare(csqlstatement);
 369         rv = trgtDbStmt->prepare(csqlstatement);
 370         if(rv != OK) {
 371             delete trgtDbStmt; delete trgtDbCon;
 372             printError(rv, "Prepare failed");
 373             return rv;
 374         }
 375
 376         // need to bind each field with buffer which is list of field values
 377         SqlStatement *sqlStmt = (SqlStatement *) stmt;
 378         List fldNameList = sqlStmt->getFieldNameList(tblName);
 379         ListIterator iter = fldNameList.getIterator();
 380         Identifier *fname = NULL;
 381         FieldInfo *fldInfo = new FieldInfo();
 382         List cfieldValueList;
 383         List tfieldValueList;
 384
 385         // List to hold all the records that are present in both the databases
 386         List recordList;
 387         int paramPos = 1;
 388         while (iter.hasElement()) {
 389             fname = (Identifier *) iter.nextElement();
 390             if (NULL == fname) {
 391                 delete trgtDbStmt; delete trgtDbCon;
 392                 delete fldInfo;
 393                 printError(ErrSysFatal, "Fatal:Field Name list has NULL");
 394                 return ErrSysFatal;
 395             }
 396             rv = sqlStmt->getFieldInfo(tblName, fname->name, fldInfo);
 397             if (ErrNotFound == rv) {
 398                 delete trgtDbStmt; delete trgtDbCon;
 399                 delete fldInfo;
 400                 printError(ErrSysInternal, "Field %s does not exist in table", fname->name);
 401                 return ErrSyntaxError;
 402             }
 403             FldVal *cfldVal = new FldVal();
 404             FldVal *tfldVal = new FldVal();
 405             cfldVal->type = fldInfo->type;
 406             tfldVal->type = fldInfo->type;
 407             if(cfldVal->type == typeString)
 408                 cfldVal->length = os::align(fldInfo->length + 1);
 409             else cfldVal->length = fldInfo->length;
 410             cfldVal->value = AllDataType::alloc(fldInfo->type, cfldVal->length);
 411             tfldVal->value = AllDataType::alloc(fldInfo->type, cfldVal->length);
 412             memset(cfldVal->value, 0, cfldVal->length);
 413             memset(tfldVal->value, 0, cfldVal->length);
 414             cfieldValueList.append(cfldVal);
 415             tfieldValueList.append(tfldVal);
 416             stmt->bindField(paramPos, cfldVal->value);
 417             trgtDbStmt->bindField(paramPos, tfldVal->value);
 418             paramPos++;
 419         }
 420         delete fldInfo;
 421         iter.reset();
 422         while ((fname=(Identifier *)iter.nextElement())!= NULL) delete fname;
 423         fldNameList.reset();
 424
 425         // WHERE parameter should be binded with the primary key field value of the list that is present in both the databases
 426         int recSize = 2 * sizeof(List) + pkFldLen;
 427         ListIterator sameValIter = sameInBothDb.getIterator();
 428         PrimKeyFldVal *sameElem = NULL;
 429         while((sameElem = (PrimKeyFldVal *)sameValIter.nextElement()) != NULL) {
 430             conn->beginTrans();
 431             trgtDbCon->beginTrans();
 432             setParamValues(stmt, 1, pkFldType, pkFldLen, sameElem->val);
 433             setParamValues(trgtDbStmt, 1, pkFldType, pkFldLen, 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 }
 758
 759 void setParamValues(AbsSqlStatement *stmt, int parampos, DataType type, int length, void *value)
 760 {
 761     switch(type)
 762     {
 763         case typeInt:
 764             stmt->setIntParam(parampos, *(int*)value);
 765             break;
 766         case typeLong:
 767             stmt->setLongParam(parampos, *(long*)value);
 768             break;
 769         case typeLongLong:
 770             stmt->setLongLongParam(parampos, *(long long*)value);
 771             break;
 772         case typeShort:
 773             stmt->setShortParam(parampos, *(short*)value);
 774             break;
 775         case typeByteInt:
 776             stmt->setByteIntParam(parampos, *(char*)value);
 777             break;
 778         case typeDouble:
 779             stmt->setDoubleParam(parampos, *(double*)value);
 780             break;
 781         case typeFloat:
 782             stmt->setFloatParam(parampos, *(float*)value);
 783             break;
 784         case typeDate:
 785             stmt->setDateParam(parampos, *(Date*)value);
 786             break;
 787         case typeTime:
 788             stmt->setTimeParam(parampos, *(Time*)value);
 789             break;
 790         case typeTimeStamp:
 791             stmt->setTimeStampParam(parampos, *(TimeStamp*)value);
 792             break;
 793         case typeString:
 794             {
 795                 char *d =(char*)value;
 796                 d[length-1] = '\0';
 797                 stmt->setStringParam(parampos, (char*)value);
 798                 break;
 799             }
 800         case typeBinary:
 801             stmt->setBinaryParam(parampos, (char *) value, length);
 802             break;
 803     }
 804     return;
 805 }
 806