/ Check-in [f9a3a839]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the --changeset option to the sqldiff utility program, for generating a sessions changeset file instead of an SQL diff.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f9a3a8391c28cf13d76ec54f471735d35059acea
User & Date: drh 2015-04-11 13:49:16
Context
2015-04-11
20:20
Fix a problem with sorting large amounts of partially ordered data. check-in: acca97ef user: dan tags: trunk
18:45
Merge latest trunk changes with this branch. Closed-Leaf check-in: cf7590f6 user: dan tags: sorter-opt
13:49
Add the --changeset option to the sqldiff utility program, for generating a sessions changeset file instead of an SQL diff. check-in: f9a3a839 user: drh tags: trunk
13:48
The --changeset option now appears to be working. Closed-Leaf check-in: 1a2e2803 user: drh tags: sqldiff-changeset
11:53
Update tests in whereD.test to account for the change in the previous commit. check-in: da49700c user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to tool/sqldiff.c.

   709    709   end_diff_one_table:
   710    710     strFree(&sql);
   711    711     sqlite3_free(zId);
   712    712     namelistFree(az);
   713    713     namelistFree(az2);
   714    714     return;
   715    715   }
          716  +
          717  +/*
          718  +** Write a 64-bit signed integer as a varint onto out
          719  +*/
          720  +static void putsVarint(FILE *out, sqlite3_uint64 v){
          721  +  int i, n;
          722  +  unsigned char p[12];
          723  +  if( v & (((sqlite3_uint64)0xff000000)<<32) ){
          724  +    p[8] = (unsigned char)v;
          725  +    v >>= 8;
          726  +    for(i=7; i>=0; i--){
          727  +      p[i] = (unsigned char)((v & 0x7f) | 0x80);
          728  +      v >>= 7;
          729  +    }
          730  +    fwrite(p, 8, 1, out);
          731  +  }else{
          732  +    n = 9;
          733  +    do{
          734  +      p[n--] = (unsigned char)((v & 0x7f) | 0x80);
          735  +      v >>= 7;
          736  +    }while( v!=0 );
          737  +    p[9] &= 0x7f;
          738  +    fwrite(p+n+1, 9-n, 1, out);
          739  +  }
          740  +}
          741  +
          742  +/*
          743  +** Write an SQLite value onto out.
          744  +*/
          745  +static void putValue(FILE *out, sqlite3_value *pVal){
          746  +  int iDType = sqlite3_value_type(pVal);
          747  +  sqlite3_int64 iX;
          748  +  double rX;
          749  +  sqlite3_uint64 uX;
          750  +  int j;
          751  +
          752  +  putc(iDType, out);
          753  +  switch( iDType ){
          754  +    case SQLITE_INTEGER:
          755  +      iX = sqlite3_value_int64(pVal);
          756  +      memcpy(&uX, &iX, 8);
          757  +      for(j=56; j>=0; j-=8) putc((uX>>j)&0xff, out);
          758  +      break;
          759  +    case SQLITE_FLOAT:
          760  +      rX = sqlite3_value_int64(pVal);
          761  +      memcpy(&uX, &rX, 8);
          762  +      for(j=56; j>=0; j-=8) putc((uX>>j)&0xff, out);
          763  +      break;
          764  +    case SQLITE_TEXT:
          765  +      iX = sqlite3_value_bytes(pVal);
          766  +      putsVarint(out, (sqlite3_uint64)iX);
          767  +      fwrite(sqlite3_value_text(pVal),1,iX,out);
          768  +      break;
          769  +    case SQLITE_BLOB:
          770  +      iX = sqlite3_value_bytes(pVal);
          771  +      putsVarint(out, (sqlite3_uint64)iX);
          772  +      fwrite(sqlite3_value_blob(pVal),1,iX,out);
          773  +      break;
          774  +    case SQLITE_NULL:
          775  +      break;
          776  +  }
          777  +}
          778  +
          779  +/*
          780  +** Generate a CHANGESET for all differences from main.zTab to aux.zTab.
          781  +*/
          782  +static void changeset_one_table(const char *zTab, FILE *out){
          783  +  sqlite3_stmt *pStmt;          /* SQL statment */
          784  +  char *zId = safeId(zTab);     /* Escaped name of the table */
          785  +  char **azCol = 0;             /* List of escaped column names */
          786  +  int nCol = 0;                 /* Number of columns */
          787  +  int *aiFlg = 0;               /* 0 if column is not part of PK */
          788  +  int *aiPk = 0;                /* Column numbers for each PK column */
          789  +  int nPk = 0;                  /* Number of PRIMARY KEY columns */
          790  +  Str sql;                      /* SQL for the diff query */
          791  +  int i, k;                     /* Loop counters */
          792  +  const char *zSep;             /* List separator */
          793  +
          794  +  pStmt = db_prepare(
          795  +      "SELECT A.sql=B.sql FROM main.sqlite_master A, aux.sqlite_master B"
          796  +      " WHERE A.name=%Q AND B.name=%Q", zTab, zTab
          797  +  );
          798  +  if( SQLITE_ROW==sqlite3_step(pStmt) ){
          799  +    if( sqlite3_column_int(pStmt,0)==0 ){
          800  +      runtimeError("schema changes for table %s", safeId(zTab));
          801  +    }
          802  +  }else{
          803  +    runtimeError("table %s missing from one or both databases", safeId(zTab));
          804  +  }
          805  +  sqlite3_finalize(pStmt);
          806  +  pStmt = db_prepare("PRAGMA main.table_info=%Q", zTab);
          807  +  while( SQLITE_ROW==sqlite3_step(pStmt) ){
          808  +    nCol++;
          809  +    azCol = sqlite3_realloc(azCol, sizeof(char*)*nCol);
          810  +    if( azCol==0 ) runtimeError("out of memory");
          811  +    aiFlg = sqlite3_realloc(aiFlg, sizeof(int)*nCol);
          812  +    if( aiFlg==0 ) runtimeError("out of memory");
          813  +    azCol[nCol-1] = safeId((const char*)sqlite3_column_text(pStmt,1));
          814  +    aiFlg[nCol-1] = i = sqlite3_column_int(pStmt,5);
          815  +    if( i>0 ){
          816  +      if( i>nPk ){
          817  +        nPk = i;
          818  +        aiPk = sqlite3_realloc(aiPk, sizeof(int)*nPk);
          819  +        if( aiPk==0 ) runtimeError("out of memory");
          820  +      }
          821  +      aiPk[i-1] = nCol-1;
          822  +    }
          823  +  }
          824  +  sqlite3_finalize(pStmt);
          825  +  if( nPk==0 ) goto end_changeset_one_table; 
          826  +  strInit(&sql);
          827  +  if( nCol>nPk ){
          828  +    strPrintf(&sql, "SELECT %d", SQLITE_UPDATE);
          829  +    for(i=0; i<nCol; i++){
          830  +      if( aiFlg[i] ){
          831  +        strPrintf(&sql, ",\n       A.%s", azCol[i]);
          832  +      }else{
          833  +        strPrintf(&sql, ",\n       A.%s IS NOT B.%s, A.%s, B.%s",
          834  +                  azCol[i], azCol[i], azCol[i], azCol[i]);
          835  +      }
          836  +    }
          837  +    strPrintf(&sql,"\n  FROM main.%s A, aux.%s B\n", zId, zId);
          838  +    zSep = " WHERE";
          839  +    for(i=0; i<nPk; i++){
          840  +      strPrintf(&sql, "%s A.%s=B.%s", zSep, azCol[aiPk[i]], azCol[aiPk[i]]);
          841  +      zSep = " AND";
          842  +    }
          843  +    zSep = "\n   AND (";
          844  +    for(i=0; i<nCol; i++){
          845  +      if( aiFlg[i] ) continue;
          846  +      strPrintf(&sql, "%sA.%s IS NOT B.%s", zSep, azCol[i], azCol[i]);
          847  +      zSep = " OR\n        ";
          848  +    }
          849  +    strPrintf(&sql,")\n UNION ALL\n");
          850  +  }
          851  +  strPrintf(&sql, "SELECT %d", SQLITE_DELETE);
          852  +  for(i=0; i<nCol; i++){
          853  +    if( aiFlg[i] ){
          854  +      strPrintf(&sql, ",\n       A.%s", azCol[i]);
          855  +    }else{
          856  +      strPrintf(&sql, ",\n       1, A.%s, NULL", azCol[i]);
          857  +    }
          858  +  }
          859  +  strPrintf(&sql, "\n  FROM main.%s A\n", zId);
          860  +  strPrintf(&sql, " WHERE NOT EXISTS(SELECT 1 FROM aux.%s B\n", zId);
          861  +  zSep =          "                   WHERE";
          862  +  for(i=0; i<nPk; i++){
          863  +    strPrintf(&sql, "%s A.%s=B.%s", zSep, azCol[aiPk[i]], azCol[aiPk[i]]);
          864  +    zSep = " AND";
          865  +  }
          866  +  strPrintf(&sql, ")\n UNION ALL\n");
          867  +  strPrintf(&sql, "SELECT %d", SQLITE_INSERT);
          868  +  for(i=0; i<nCol; i++){
          869  +    if( aiFlg[i] ){
          870  +      strPrintf(&sql, ",\n       B.%s", azCol[i]);
          871  +    }else{
          872  +      strPrintf(&sql, ",\n       1, NULL, B.%s", azCol[i]);
          873  +    }
          874  +  }
          875  +  strPrintf(&sql, "\n  FROM aux.%s B\n", zId);
          876  +  strPrintf(&sql, " WHERE NOT EXISTS(SELECT 1 FROM main.%s A\n", zId);
          877  +  zSep =          "                   WHERE";
          878  +  for(i=0; i<nPk; i++){
          879  +    strPrintf(&sql, "%s A.%s=B.%s", zSep, azCol[aiPk[i]], azCol[aiPk[i]]);
          880  +    zSep = " AND";
          881  +  }
          882  +  strPrintf(&sql, ")\n");
          883  +  strPrintf(&sql, " ORDER BY");
          884  +  zSep = " ";
          885  +  for(i=0; i<nPk; i++){
          886  +    strPrintf(&sql, "%s %d", zSep, aiPk[i]+2);
          887  +    zSep = ",";
          888  +  }
          889  +  strPrintf(&sql, ";\n");
          890  +
          891  +  if( g.fDebug & DEBUG_DIFF_SQL ){ 
          892  +    printf("SQL for %s:\n%s\n", zId, sql.z);
          893  +    goto end_changeset_one_table;
          894  +  }
          895  +
          896  +  putc('T', out);
          897  +  putsVarint(out, (sqlite3_uint64)nCol);
          898  +  for(i=0; i<nCol; i++) putc(aiFlg[i]!=0, out);
          899  +  fwrite(zTab, 1, strlen(zTab), out);
          900  +  putc(0, out);
          901  +
          902  +  pStmt = db_prepare("%s", sql.z);
          903  +  while( SQLITE_ROW==sqlite3_step(pStmt) ){
          904  +    int iType = sqlite3_column_int(pStmt,0);
          905  +    putc(iType, out);
          906  +    putc(0, out);
          907  +    switch( sqlite3_column_int(pStmt,0) ){
          908  +      case SQLITE_UPDATE: {
          909  +        for(k=1, i=0; i<nCol; i++){
          910  +          if( aiFlg[i] ){
          911  +            putValue(out, sqlite3_column_value(pStmt,k));
          912  +            k++;
          913  +          }else if( sqlite3_column_int(pStmt,k) ){
          914  +            putValue(out, sqlite3_column_value(pStmt,k+1));
          915  +            k += 3;
          916  +          }else{
          917  +            putc(0, out);
          918  +            k += 3;
          919  +          }
          920  +        }
          921  +        for(k=1, i=0; i<nCol; i++){
          922  +          if( aiFlg[i] ){
          923  +            putc(0, out);
          924  +            k++;
          925  +          }else if( sqlite3_column_int(pStmt,k) ){
          926  +            putValue(out, sqlite3_column_value(pStmt,k+2));
          927  +            k += 3;
          928  +          }else{
          929  +            putc(0, out);
          930  +            k += 3;
          931  +          }
          932  +        }
          933  +        break;
          934  +      }
          935  +      case SQLITE_INSERT: {
          936  +        for(k=1, i=0; i<nCol; i++){
          937  +          if( aiFlg[i] ){
          938  +            putValue(out, sqlite3_column_value(pStmt,k));
          939  +            k++;
          940  +          }else{
          941  +            putValue(out, sqlite3_column_value(pStmt,k+2));
          942  +            k += 3;
          943  +          }
          944  +        }
          945  +        break;
          946  +      }
          947  +      case SQLITE_DELETE: {
          948  +        for(k=1, i=0; i<nCol; i++){
          949  +          if( aiFlg[i] ){
          950  +            putValue(out, sqlite3_column_value(pStmt,k));
          951  +            k++;
          952  +          }else{
          953  +            putValue(out, sqlite3_column_value(pStmt,k+1));
          954  +            k += 3;
          955  +          }
          956  +        }
          957  +        break;
          958  +      }
          959  +    }
          960  +  }
          961  +  sqlite3_finalize(pStmt);
          962  +  
          963  +end_changeset_one_table:
          964  +  while( nCol>0 ) sqlite3_free(azCol[--nCol]);
          965  +  sqlite3_free(azCol);
          966  +  sqlite3_free(aiPk);
          967  +  sqlite3_free(zId);
          968  +}
   716    969   
   717    970   /*
   718    971   ** Print sketchy documentation for this utility program
   719    972   */
   720    973   static void showHelp(void){
   721    974     printf("Usage: %s [options] DB1 DB2\n", g.zArgv0);
   722    975     printf(
   723    976   "Output SQL text that would transform DB1 into DB2.\n"
   724    977   "Options:\n"
          978  +"  --changeset FILE      Write a CHANGESET into FILE\n"
   725    979   "  --primarykey          Use schema-defined PRIMARY KEYs\n"
   726    980   "  --schema              Show only differences in the schema\n"
   727    981   "  --table TAB           Show only differences in table TAB\n"
   728    982     );
   729    983   }
   730    984   
   731    985   int main(int argc, char **argv){
................................................................................
   733    987     const char *zDb2 = 0;
   734    988     int i;
   735    989     int rc;
   736    990     char *zErrMsg = 0;
   737    991     char *zSql;
   738    992     sqlite3_stmt *pStmt;
   739    993     char *zTab = 0;
          994  +  FILE *out = 0;
   740    995   
   741    996     g.zArgv0 = argv[0];
   742    997     for(i=1; i<argc; i++){
   743    998       const char *z = argv[i];
   744    999       if( z[0]=='-' ){
   745   1000         z++;
   746   1001         if( z[0]=='-' ) z++;
         1002  +      if( strcmp(z,"changeset")==0 ){
         1003  +        out = fopen(argv[++i], "wb");
         1004  +        if( out==0 ) cmdlineError("cannot open: %s", argv[i]);
         1005  +      }else
   747   1006         if( strcmp(z,"debug")==0 ){
   748   1007           g.fDebug = strtol(argv[++i], 0, 0);
   749   1008         }else
   750   1009         if( strcmp(z,"help")==0 ){
   751   1010           showHelp();
   752   1011           return 0;
   753   1012         }else
................................................................................
   789   1048     }
   790   1049     rc = sqlite3_exec(g.db, "SELECT * FROM aux.sqlite_master", 0, 0, &zErrMsg);
   791   1050     if( rc || zErrMsg ){
   792   1051       cmdlineError("\"%s\" does not appear to be a valid SQLite database", zDb2);
   793   1052     }
   794   1053   
   795   1054     if( zTab ){
   796         -    diff_one_table(zTab);
         1055  +    if( out ){
         1056  +      changeset_one_table(zTab, out);
         1057  +    }else{
         1058  +      diff_one_table(zTab);
         1059  +    }
   797   1060     }else{
   798   1061       /* Handle tables one by one */
   799   1062       pStmt = db_prepare(
   800   1063         "SELECT name FROM main.sqlite_master\n"
   801   1064         " WHERE type='table' AND sql NOT LIKE 'CREATE VIRTUAL%%'\n"
   802   1065         " UNION\n"
   803   1066         "SELECT name FROM aux.sqlite_master\n"
   804   1067         " WHERE type='table' AND sql NOT LIKE 'CREATE VIRTUAL%%'\n"
   805   1068         " ORDER BY name"
   806   1069       );
   807   1070       while( SQLITE_ROW==sqlite3_step(pStmt) ){
   808         -      diff_one_table((const char*)sqlite3_column_text(pStmt, 0));
         1071  +      const char *zTab = (const char*)sqlite3_column_text(pStmt,0);
         1072  +      if( out ){
         1073  +        changeset_one_table(zTab, out);
         1074  +      }else{
         1075  +        diff_one_table(zTab);
         1076  +      }
   809   1077       }
   810   1078       sqlite3_finalize(pStmt);
   811   1079     }
   812   1080   
   813   1081     /* TBD: Handle trigger differences */
   814   1082     /* TBD: Handle view differences */
   815   1083     sqlite3_close(g.db);
   816   1084     return 0;
   817   1085   }