Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the --rbu switch to the "sqldiff" utility. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
098bea26da4533d9ad97a85687cca56f |
User & Date: | dan 2015-07-30 20:26:16.763 |
Context
2015-07-31
| ||
14:43 | Fix a bug in the fts5 porter tokenizer preventing it from passing xCreate() arguments through to its parent tokenizer. (check-in: c3c672af97 user: dan tags: trunk) | |
2015-07-30
| ||
20:26 | Add the --rbu switch to the "sqldiff" utility. (check-in: 098bea26da user: dan tags: trunk) | |
11:38 | Allow RBU tables to be named "data[0-9]*_<target>" instead of strictly "data_<target>". Also update RBU so that it always processes data tables in order sorted by name. (check-in: 287aa30601 user: dan tags: trunk) | |
Changes
Added ext/rbu/rbudiff.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 | # 2015-07-31 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # Tests for the [sqldiff --rbu] command. # # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } source $testdir/tester.tcl set testprefix rbudiff if {$tcl_platform(platform)=="windows"} { set PROG "sqldiff.exe" } else { set PROG "./sqldiff" } if {![file exe $PROG]} { puts "rbudiff.test cannot run because $PROG is not available" finish_test return } db close proc get_rbudiff_sql {db1 db2} { exec $::PROG --rbu $db1 $db2 } proc step_rbu {target rbu} { while 1 { sqlite3rbu rbu $target $rbu set rc [rbu step] rbu close if {$rc != "SQLITE_OK"} break } set rc } proc apply_rbudiff {sql target} { forcedelete rbu.db sqlite3 rbudb rbu.db rbudb eval $sql rbudb close step_rbu $target rbu.db } proc rbudiff_cksum {db1} { sqlite3 dbtmp $db1 set txt [dbtmp eval { SELECT a || '.' || b || '.' || c FROM t1 ORDER BY 1; SELECT a || '.' || b || '.' || c FROM t2 ORDER BY 1; }] dbtmp close md5 $txt } sqlite3 db test.db do_execsql_test 1.0 { CREATE TABLE t1(a PRIMARY KEY, b, c); INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t1 VALUES(4, 5, 6); CREATE TABLE t2(a, b, c, PRIMARY KEY(b, c)); INSERT INTO t2 VALUES(1, 2, 3); INSERT INTO t2 VALUES(4, 5, 6); } db close forcedelete test.db2 forcecopy test.db test.db2 sqlite3 db test.db do_execsql_test 1.1 { INSERT INTO t1 VALUES(7, 8, 9); DELETE FROM t1 WHERE a=4; UPDATE t1 SET c = 11 WHERE a = 1; INSERT INTO t2 VALUES(7, 8, 9); DELETE FROM t2 WHERE a=4; UPDATE t2 SET c = 11 WHERE a = 1; } db close do_test 1.2 { set sql [get_rbudiff_sql test.db test.db2] apply_rbudiff $sql test.db } {SQLITE_DONE} do_test 1.3 { rbudiff_cksum test.db } [rbudiff_cksum test.db2] finish_test |
Changes to tool/sqldiff.c.
︙ | ︙ | |||
255 256 257 258 259 260 261 | ** CREATE TABLE t4(x,y,z,PRIMARY KEY(y,z)) WITHOUT ROWID; ** *pnPKey = 2 ** az = { "y", "z", "x", 0 } ** ** CREATE TABLE t5(rowid,_rowid_,oid); ** az = 0 // The rowid is not accessible */ | | > > > > > | 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 | ** CREATE TABLE t4(x,y,z,PRIMARY KEY(y,z)) WITHOUT ROWID; ** *pnPKey = 2 ** az = { "y", "z", "x", 0 } ** ** CREATE TABLE t5(rowid,_rowid_,oid); ** az = 0 // The rowid is not accessible */ static char **columnNames( const char *zDb, /* Database ("main" or "aux") to query */ const char *zTab, /* Name of table to return details of */ int *pnPKey, /* OUT: Number of PK columns */ int *pbRowid /* OUT: True if PK is an implicit rowid */ ){ char **az = 0; /* List of column names to be returned */ int naz = 0; /* Number of entries in az[] */ sqlite3_stmt *pStmt; /* SQL statement being run */ char *zPkIdxName = 0; /* Name of the PRIMARY KEY index */ int truePk = 0; /* PRAGMA table_info indentifies the PK to use */ int nPK = 0; /* Number of PRIMARY KEY columns */ int i, j; /* Loop counters */ |
︙ | ︙ | |||
334 335 336 337 338 339 340 341 342 343 344 345 346 347 | az = sqlite3_realloc(az, sizeof(char*)*(naz+2) ); if( az==0 ) runtimeError("out of memory"); az[naz++] = safeId((char*)sqlite3_column_text(pStmt,1)); } } sqlite3_finalize(pStmt); if( az ) az[naz] = 0; if( az[0]==0 ){ const char *azRowid[] = { "rowid", "_rowid_", "oid" }; for(i=0; i<sizeof(azRowid)/sizeof(azRowid[0]); i++){ for(j=1; j<naz; j++){ if( sqlite3_stricmp(az[j], azRowid[i])==0 ) break; } if( j>=naz ){ | > > > > > > > > > | 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 | az = sqlite3_realloc(az, sizeof(char*)*(naz+2) ); if( az==0 ) runtimeError("out of memory"); az[naz++] = safeId((char*)sqlite3_column_text(pStmt,1)); } } sqlite3_finalize(pStmt); if( az ) az[naz] = 0; /* If it is non-NULL, set *pbRowid to indicate whether or not the PK of ** this table is an implicit rowid (*pbRowid==1) or not (*pbRowid==0). */ if( pbRowid ) *pbRowid = (az[0]==0); /* If this table has an implicit rowid for a PK, figure out how to refer ** to it. There are three options - "rowid", "_rowid_" and "oid". Any ** of these will work, unless the table has an explicit column of the ** same name. */ if( az[0]==0 ){ const char *azRowid[] = { "rowid", "_rowid_", "oid" }; for(i=0; i<sizeof(azRowid)/sizeof(azRowid[0]); i++){ for(j=1; j<naz; j++){ if( sqlite3_stricmp(az[j], azRowid[i])==0 ) break; } if( j>=naz ){ |
︙ | ︙ | |||
430 431 432 433 434 435 436 | pStmt = db_prepare("SELECT sql FROM aux.sqlite_master WHERE name=%Q", zTab); if( SQLITE_ROW==sqlite3_step(pStmt) ){ fprintf(out, "%s;\n", sqlite3_column_text(pStmt,0)); } sqlite3_finalize(pStmt); if( !g.bSchemaOnly ){ | | | 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 | pStmt = db_prepare("SELECT sql FROM aux.sqlite_master WHERE name=%Q", zTab); if( SQLITE_ROW==sqlite3_step(pStmt) ){ fprintf(out, "%s;\n", sqlite3_column_text(pStmt,0)); } sqlite3_finalize(pStmt); if( !g.bSchemaOnly ){ az = columnNames("aux", zTab, &nPk, 0); strInit(&ins); if( az==0 ){ pStmt = db_prepare("SELECT * FROM aux.%s", zId); strPrintf(&ins,"INSERT INTO %s VALUES", zId); }else{ Str sql; strInit(&sql); |
︙ | ︙ | |||
507 508 509 510 511 512 513 | strInit(&sql); if( g.fDebug==DEBUG_COLUMN_NAMES ){ /* Simply run columnNames() on all tables of the origin ** database and show the results. This is used for testing ** and debugging of the columnNames() function. */ | | | 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 | strInit(&sql); if( g.fDebug==DEBUG_COLUMN_NAMES ){ /* Simply run columnNames() on all tables of the origin ** database and show the results. This is used for testing ** and debugging of the columnNames() function. */ az = columnNames("aux",zTab, &nPk, 0); if( az==0 ){ printf("Rowid not accessible for %s\n", zId); }else{ printf("%s:", zId); for(i=0; az[i]; i++){ printf(" %s", az[i]); if( i+1==nPk ) printf(" *"); |
︙ | ︙ | |||
536 537 538 539 540 541 542 | if( sqlite3_table_column_metadata(g.db,"main",zTab,0,0,0,0,0,0) ){ /* Table missing from source */ dump_table(zTab, out); goto end_diff_one_table; } | | | | 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 | if( sqlite3_table_column_metadata(g.db,"main",zTab,0,0,0,0,0,0) ){ /* Table missing from source */ dump_table(zTab, out); goto end_diff_one_table; } az = columnNames("main", zTab, &nPk, 0); az2 = columnNames("aux", zTab, &nPk2, 0); if( az && az2 ){ for(n=0; az[n]; n++){ if( sqlite3_stricmp(az[n],az2[n])!=0 ) break; } } if( az==0 || az2==0 |
︙ | ︙ | |||
714 715 716 717 718 719 720 721 722 723 724 725 726 727 | end_diff_one_table: strFree(&sql); sqlite3_free(zId); namelistFree(az); namelistFree(az2); return; } /* ** Display a summary of differences between two versions of the same ** table table. ** ** * Number of rows changed ** * Number of rows added | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 | end_diff_one_table: strFree(&sql); sqlite3_free(zId); namelistFree(az); namelistFree(az2); return; } /* ** Check that table zTab exists and has the same schema in both the "main" ** and "aux" databases currently opened by the global db handle. If they ** do not, output an error message on stderr and exit(1). Otherwise, if ** the schemas do match, return control to the caller. */ static void checkSchemasMatch(const char *zTab){ sqlite3_stmt *pStmt = db_prepare( "SELECT A.sql=B.sql FROM main.sqlite_master A, aux.sqlite_master B" " WHERE A.name=%Q AND B.name=%Q", zTab, zTab ); if( SQLITE_ROW==sqlite3_step(pStmt) ){ if( sqlite3_column_int(pStmt,0)==0 ){ runtimeError("schema changes for table %s", safeId(zTab)); } }else{ runtimeError("table %s missing from one or both databases", safeId(zTab)); } sqlite3_finalize(pStmt); } static void strPrintfArray( Str *pStr, /* String object to append to */ const char *zSep, /* Separator string */ const char *zFmt, /* Format for each entry */ char **az, int n /* Array of strings & its size (or -1) */ ){ int i; for(i=0; az[i] && (i<n || n<0); i++){ if( i!=0 ) strPrintf(pStr, "%s", zSep); strPrintf(pStr, zFmt, az[i], az[i], az[i]); } } static void getRbudiffQuery( const char *zTab, char **azCol, int nPK, int bOtaRowid, Str *pSql ){ int i; /* First the newly inserted rows: **/ strPrintf(pSql, "SELECT "); strPrintfArray(pSql, ", ", "%s", azCol, -1); strPrintf(pSql, ", 0"); /* Set ota_control to 0 for an insert */ strPrintf(pSql, " FROM aux.%Q AS n WHERE NOT EXISTS (\n", zTab); strPrintf(pSql, " SELECT 1 FROM ", zTab); strPrintf(pSql, " main.%Q AS o WHERE ", zTab); strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK); strPrintf(pSql, "\n)"); /* Deleted rows: */ strPrintf(pSql, "\nUNION ALL\nSELECT "); strPrintfArray(pSql, ", ", "%s", azCol, nPK); strPrintf(pSql, ", "); strPrintfArray(pSql, ", ", "NULL", &azCol[nPK], -1); strPrintf(pSql, ", 1"); /* Set ota_control to 1 for a delete */ strPrintf(pSql, " FROM main.%Q AS n WHERE NOT EXISTS (\n", zTab); strPrintf(pSql, " SELECT 1 FROM ", zTab); strPrintf(pSql, " aux.%Q AS o WHERE ", zTab); strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK); strPrintf(pSql, "\n) "); /* Updated rows: */ strPrintf(pSql, "\nUNION ALL\nSELECT "); strPrintfArray(pSql, ", ", "n.%s", azCol, nPK); strPrintf(pSql, ",\n"); strPrintfArray(pSql, " ,\n", " CASE WHEN n.%s IS o.%s THEN NULL ELSE n.%s END", &azCol[nPK], -1 ); if( bOtaRowid==0 ){ strPrintf(pSql, ", '"); strPrintfArray(pSql, "", ".", azCol, nPK); strPrintf(pSql, "' ||\n"); }else{ strPrintf(pSql, ",\n"); } strPrintfArray(pSql, " ||\n", " CASE WHEN n.%s IS o.%s THEN '.' ELSE 'x' END", &azCol[nPK], -1 ); strPrintf(pSql, "\nAS ota_control"); strPrintf(pSql, "\nFROM main.%Q AS o, aux.%Q AS n\nWHERE ", zTab, zTab); strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK); strPrintf(pSql, " AND ota_control LIKE '%%x%%'"); /* Now add an ORDER BY clause to sort everything by PK. */ strPrintf(pSql, "\nORDER BY "); for(i=1; i<=nPK; i++) strPrintf(pSql, "%s%d", ((i>1)?", ":""), i); } static void rbudiff_one_table(const char *zTab, FILE *out){ int bOtaRowid; /* True to use an ota_rowid column */ int nPK; /* Number of primary key columns in table */ char **azCol; /* NULL terminated array of col names */ int i; int nCol; Str ct = {0, 0, 0}; /* The "CREATE TABLE data_xxx" statement */ Str sql = {0, 0, 0}; /* Query to find differences */ Str insert = {0, 0, 0}; /* First part of output INSERT statement */ sqlite3_stmt *pStmt = 0; /* --rbu mode must use real primary keys. */ g.bSchemaPK = 1; /* Check that the schemas of the two tables match. Exit early otherwise. */ checkSchemasMatch(zTab); /* Grab the column names and PK details for the table(s). If no usable PK ** columns are found, bail out early. */ azCol = columnNames("main", zTab, &nPK, &bOtaRowid); if( azCol==0 ){ runtimeError("table %s has no usable PK columns", zTab); } /* Build and output the CREATE TABLE statement for the data_xxx table */ strPrintf(&ct, "CREATE TABLE IF NOT EXISTS 'data_%q'(", zTab); if( bOtaRowid ) strPrintf(&ct, "rbu_rowid, "); strPrintfArray(&ct, ", ", "%s", &azCol[bOtaRowid], -1); strPrintf(&ct, ", rbu_control);"); /* Get the SQL for the query to retrieve data from the two databases */ getRbudiffQuery(zTab, azCol, nPK, bOtaRowid, &sql); /* Build the first part of the INSERT statement output for each row ** in the data_xxx table. */ strPrintf(&insert, "INSERT INTO 'data_%q' (", zTab); if( bOtaRowid ) strPrintf(&insert, "rbu_rowid, "); strPrintfArray(&insert, ", ", "%s", &azCol[bOtaRowid], -1); strPrintf(&insert, ", rbu_control) VALUES("); pStmt = db_prepare("%s", sql.z); nCol = sqlite3_column_count(pStmt); while( sqlite3_step(pStmt)==SQLITE_ROW ){ if( ct.z ){ fprintf(out, "%s\n", ct.z); strFree(&ct); } fprintf(out, "%s", insert.z); for(i=0; i<nCol; i++){ if( i>0 ) fprintf(out, ", "); printQuoted(out, sqlite3_column_value(pStmt, i)); } fprintf(out, ");\n"); } sqlite3_finalize(pStmt); strFree(&ct); strFree(&sql); strFree(&insert); } /* ** Display a summary of differences between two versions of the same ** table table. ** ** * Number of rows changed ** * Number of rows added |
︙ | ︙ | |||
756 757 758 759 760 761 762 | if( sqlite3_table_column_metadata(g.db,"main",zTab,0,0,0,0,0,0) ){ /* Table missing from source */ fprintf(out, "%s: missing from first database\n", zTab); goto end_summarize_one_table; } | | | | 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 | if( sqlite3_table_column_metadata(g.db,"main",zTab,0,0,0,0,0,0) ){ /* Table missing from source */ fprintf(out, "%s: missing from first database\n", zTab); goto end_summarize_one_table; } az = columnNames("main", zTab, &nPk, 0); az2 = columnNames("aux", zTab, &nPk2, 0); if( az && az2 ){ for(n=0; az[n]; n++){ if( sqlite3_stricmp(az[n],az2[n])!=0 ) break; } } if( az==0 || az2==0 |
︙ | ︙ | |||
927 928 929 930 931 932 933 | int *aiFlg = 0; /* 0 if column is not part of PK */ int *aiPk = 0; /* Column numbers for each PK column */ int nPk = 0; /* Number of PRIMARY KEY columns */ Str sql; /* SQL for the diff query */ int i, k; /* Loop counters */ const char *zSep; /* List separator */ | | < < | < < < | < < < < | 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 | int *aiFlg = 0; /* 0 if column is not part of PK */ int *aiPk = 0; /* Column numbers for each PK column */ int nPk = 0; /* Number of PRIMARY KEY columns */ Str sql; /* SQL for the diff query */ int i, k; /* Loop counters */ const char *zSep; /* List separator */ /* Check that the schemas of the two tables match. Exit early otherwise. */ checkSchemasMatch(zTab); pStmt = db_prepare("PRAGMA main.table_info=%Q", zTab); while( SQLITE_ROW==sqlite3_step(pStmt) ){ nCol++; azCol = sqlite3_realloc(azCol, sizeof(char*)*nCol); if( azCol==0 ) runtimeError("out of memory"); aiFlg = sqlite3_realloc(aiFlg, sizeof(int)*nCol); if( aiFlg==0 ) runtimeError("out of memory"); |
︙ | ︙ | |||
1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 | printf("Usage: %s [options] DB1 DB2\n", g.zArgv0); printf( "Output SQL text that would transform DB1 into DB2.\n" "Options:\n" " --changeset FILE Write a CHANGESET into FILE\n" " -L|--lib LIBRARY Load an SQLite extension library\n" " --primarykey Use schema-defined PRIMARY KEYs\n" " --schema Show only differences in the schema\n" " --summary Show only a summary of the differences\n" " --table TAB Show only differences in table TAB\n" ); } int main(int argc, char **argv){ | > | 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 | printf("Usage: %s [options] DB1 DB2\n", g.zArgv0); printf( "Output SQL text that would transform DB1 into DB2.\n" "Options:\n" " --changeset FILE Write a CHANGESET into FILE\n" " -L|--lib LIBRARY Load an SQLite extension library\n" " --primarykey Use schema-defined PRIMARY KEYs\n" " --rbu Output SQL to create/populate RBU table(s)\n" " --schema Show only differences in the schema\n" " --summary Show only a summary of the differences\n" " --table TAB Show only differences in table TAB\n" ); } int main(int argc, char **argv){ |
︙ | ︙ | |||
1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 | if( azExt==0 ) cmdlineError("out of memory"); azExt[nExt++] = argv[++i]; }else #endif if( strcmp(z,"primarykey")==0 ){ g.bSchemaPK = 1; }else if( strcmp(z,"schema")==0 ){ g.bSchemaOnly = 1; }else if( strcmp(z,"summary")==0 ){ xDiff = summarize_one_table; }else if( strcmp(z,"table")==0 ){ | > > > | 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 | if( azExt==0 ) cmdlineError("out of memory"); azExt[nExt++] = argv[++i]; }else #endif if( strcmp(z,"primarykey")==0 ){ g.bSchemaPK = 1; }else if( strcmp(z,"rbu")==0 ){ xDiff = rbudiff_one_table; }else if( strcmp(z,"schema")==0 ){ g.bSchemaOnly = 1; }else if( strcmp(z,"summary")==0 ){ xDiff = summarize_one_table; }else if( strcmp(z,"table")==0 ){ |
︙ | ︙ |