1 /* 2 ** 2014-08-18 3 ** 4 ** The author disclaims copyright to this source code. In place of 5 ** a legal notice, here is a blessing: 6 ** 7 ** May you do good and not evil. 8 ** May you find forgiveness for yourself and forgive others. 9 ** May you share freely, never taking more than you give. 10 ** 11 ************************************************************************* 12 ** This file contains code to implement the "changeset" command line 13 ** utility for displaying and transforming changesets generated by 14 ** the Sessions extension. 15 */ 16 #include "sqlite3.h" 17 #include <stdio.h> 18 #include <stdlib.h> 19 #include <string.h> 20 #include <assert.h> 21 #include <ctype.h> 22 23 24 /* 25 ** Show a usage message on stderr then quit. 26 */ 27 static void usage(const char *argv0){ 28 fprintf(stderr, "Usage: %s FILENAME COMMAND ...\n", argv0); 29 fprintf(stderr, 30 "COMMANDs:\n" 31 " apply DB Apply the changeset to database file DB\n" 32 " concat FILE2 OUT Concatenate FILENAME and FILE2 into OUT\n" 33 " dump Show the complete content of the changeset\n" 34 " invert OUT Write an inverted changeset into file OUT\n" 35 " sql Give a pseudo-SQL rendering of the changeset\n" 36 ); 37 exit(1); 38 } 39 40 /* 41 ** Read the content of a disk file into an in-memory buffer 42 */ 43 static void readFile(const char *zFilename, int *pSz, void **ppBuf){ 44 FILE *f; 45 int sz; 46 void *pBuf; 47 f = fopen(zFilename, "rb"); 48 if( f==0 ){ 49 fprintf(stderr, "cannot open \"%s\" for reading\n", zFilename); 50 exit(1); 51 } 52 fseek(f, 0, SEEK_END); 53 sz = (int)ftell(f); 54 rewind(f); 55 pBuf = sqlite3_malloc( sz ? sz : 1 ); 56 if( pBuf==0 ){ 57 fprintf(stderr, "cannot allocate %d to hold content of \"%s\"\n", 58 sz, zFilename); 59 exit(1); 60 } 61 if( sz>0 ){ 62 if( fread(pBuf, sz, 1, f)!=1 ){ 63 fprintf(stderr, "cannot read all %d bytes of \"%s\"\n", sz, zFilename); 64 exit(1); 65 } 66 fclose(f); 67 } 68 *pSz = sz; 69 *ppBuf = pBuf; 70 } 71 72 /* Array for converting from half-bytes (nybbles) into ASCII hex 73 ** digits. */ 74 static const char hexdigits[] = { 75 '0', '1', '2', '3', '4', '5', '6', '7', 76 '8', '9', 'a', 'b', 'c', 'd', 'e', 'f' 77 }; 78 79 /* 80 ** Render an sqlite3_value as an SQL string. 81 */ 82 static void renderValue(sqlite3_value *pVal){ 83 switch( sqlite3_value_type(pVal) ){ 84 case SQLITE_FLOAT: { 85 double r1; 86 char zBuf[50]; 87 r1 = sqlite3_value_double(pVal); 88 sqlite3_snprintf(sizeof(zBuf), zBuf, "%!.15g", r1); 89 printf("%s", zBuf); 90 break; 91 } 92 case SQLITE_INTEGER: { 93 printf("%lld", sqlite3_value_int64(pVal)); 94 break; 95 } 96 case SQLITE_BLOB: { 97 char const *zBlob = sqlite3_value_blob(pVal); 98 int nBlob = sqlite3_value_bytes(pVal); 99 int i; 100 printf("x'"); 101 for(i=0; i<nBlob; i++){ 102 putchar(hexdigits[(zBlob[i]>>4)&0x0F]); 103 putchar(hexdigits[(zBlob[i])&0x0F]); 104 } 105 putchar('\''); 106 break; 107 } 108 case SQLITE_TEXT: { 109 const unsigned char *zArg = sqlite3_value_text(pVal); 110 putchar('\''); 111 while( zArg[0] ){ 112 putchar(zArg[0]); 113 if( zArg[0]=='\'' ) putchar(zArg[0]); 114 zArg++; 115 } 116 putchar('\''); 117 break; 118 } 119 default: { 120 assert( sqlite3_value_type(pVal)==SQLITE_NULL ); 121 printf("NULL"); 122 break; 123 } 124 } 125 } 126 127 /* 128 ** Number of conflicts seen 129 */ 130 static int nConflict = 0; 131 132 /* 133 ** The conflict callback 134 */ 135 static int conflictCallback( 136 void *pCtx, 137 int eConflict, 138 sqlite3_changeset_iter *pIter 139 ){ 140 int op, bIndirect, nCol, i; 141 const char *zTab; 142 unsigned char *abPK; 143 const char *zType = ""; 144 const char *zOp = ""; 145 const char *zSep = " "; 146 147 nConflict++; 148 sqlite3changeset_op(pIter, &zTab, &nCol, &op, &bIndirect); 149 sqlite3changeset_pk(pIter, &abPK, 0); 150 switch( eConflict ){ 151 case SQLITE_CHANGESET_DATA: zType = "DATA"; break; 152 case SQLITE_CHANGESET_NOTFOUND: zType = "NOTFOUND"; break; 153 case SQLITE_CHANGESET_CONFLICT: zType = "PRIMARY KEY"; break; 154 case SQLITE_CHANGESET_FOREIGN_KEY: zType = "FOREIGN KEY"; break; 155 case SQLITE_CHANGESET_CONSTRAINT: zType = "CONSTRAINT"; break; 156 } 157 switch( op ){ 158 case SQLITE_UPDATE: zOp = "UPDATE of"; break; 159 case SQLITE_INSERT: zOp = "INSERT into"; break; 160 case SQLITE_DELETE: zOp = "DELETE from"; break; 161 } 162 printf("%s conflict on %s table %s with primary key", zType, zOp, zTab); 163 for(i=0; i<nCol; i++){ 164 sqlite3_value *pVal; 165 if( abPK[i]==0 ) continue; 166 printf("%s", zSep); 167 if( op==SQLITE_INSERT ){ 168 sqlite3changeset_new(pIter, i, &pVal); 169 }else{ 170 sqlite3changeset_old(pIter, i, &pVal); 171 } 172 renderValue(pVal); 173 zSep = ","; 174 } 175 printf("\n"); 176 return SQLITE_CHANGESET_OMIT; 177 } 178 179 int main(int argc, char **argv){ 180 int sz, rc; 181 void *pBuf = 0; 182 if( argc<3 ) usage(argv[0]); 183 readFile(argv[1], &sz, &pBuf); 184 185 /* changeset FILENAME apply DB 186 ** Apply the changeset in FILENAME to the database file DB 187 */ 188 if( strcmp(argv[2],"apply")==0 ){ 189 sqlite3 *db; 190 if( argc!=4 ) usage(argv[0]); 191 rc = sqlite3_open(argv[3], &db); 192 if( rc!=SQLITE_OK ){ 193 fprintf(stderr, "unable to open database file \"%s\": %s\n", 194 argv[3], sqlite3_errmsg(db)); 195 sqlite3_close(db); 196 exit(1); 197 } 198 sqlite3_exec(db, "BEGIN", 0, 0, 0); 199 nConflict = 0; 200 rc = sqlite3changeset_apply(db, sz, pBuf, 0, conflictCallback, 0); 201 if( rc ){ 202 fprintf(stderr, "sqlite3changeset_apply() returned %d\n", rc); 203 } 204 if( nConflict ){ 205 fprintf(stderr, "%d conflicts - no changes applied\n", nConflict); 206 sqlite3_exec(db, "ROLLBACK", 0, 0, 0); 207 }else if( rc ){ 208 fprintf(stderr, "sqlite3changeset_apply() returns %d " 209 "- no changes applied\n", rc); 210 sqlite3_exec(db, "ROLLBACK", 0, 0, 0); 211 }else{ 212 sqlite3_exec(db, "COMMIT", 0, 0, 0); 213 } 214 sqlite3_close(db); 215 }else 216 217 /* changeset FILENAME concat FILE2 OUT 218 ** Add changeset FILE2 onto the end of the changeset in FILENAME 219 ** and write the result into OUT. 220 */ 221 if( strcmp(argv[2],"concat")==0 ){ 222 int szB; 223 void *pB; 224 int szOut; 225 void *pOutBuf; 226 FILE *out; 227 const char *zOut = argv[4]; 228 if( argc!=5 ) usage(argv[0]); 229 out = fopen(zOut, "wb"); 230 if( out==0 ){ 231 fprintf(stderr, "cannot open \"%s\" for writing\n", zOut); 232 exit(1); 233 } 234 readFile(argv[3], &szB, &pB); 235 sqlite3changeset_concat(sz, pBuf, szB, pB, &szOut, &pOutBuf); 236 if( fwrite(pOutBuf, szOut, 1, out)!=1 ){ 237 fprintf(stderr, "unable to write all %d bytes of output to \"%s\"\n", 238 szOut, zOut); 239 } 240 fclose(out); 241 sqlite3_free(pOutBuf); 242 sqlite3_free(pB); 243 }else 244 245 /* changeset FILENAME dump 246 ** Show the complete content of the changeset in FILENAME 247 */ 248 if( strcmp(argv[2],"dump")==0 ){ 249 int cnt = 0; 250 int i; 251 sqlite3_changeset_iter *pIter; 252 rc = sqlite3changeset_start(&pIter, sz, pBuf); 253 if( rc!=SQLITE_OK ){ 254 fprintf(stderr, "sqlite3changeset_start() returns %d\n", rc); 255 exit(1); 256 } 257 while( sqlite3changeset_next(pIter)==SQLITE_ROW ){ 258 int op, bIndirect, nCol; 259 const char *zTab; 260 unsigned char *abPK; 261 sqlite3changeset_op(pIter, &zTab, &nCol, &op, &bIndirect); 262 cnt++; 263 printf("%d: %s table=[%s] indirect=%d nColumn=%d\n", 264 cnt, op==SQLITE_INSERT ? "INSERT" : 265 op==SQLITE_UPDATE ? "UPDATE" : "DELETE", 266 zTab, bIndirect, nCol); 267 sqlite3changeset_pk(pIter, &abPK, 0); 268 for(i=0; i<nCol; i++){ 269 sqlite3_value *pVal; 270 pVal = 0; 271 sqlite3changeset_old(pIter, i, &pVal); 272 if( pVal ){ 273 printf(" old[%d]%s = ", i, abPK[i] ? "pk" : " "); 274 renderValue(pVal); 275 printf("\n"); 276 } 277 pVal = 0; 278 sqlite3changeset_new(pIter, i, &pVal); 279 if( pVal ){ 280 printf(" new[%d]%s = ", i, abPK[i] ? "pk" : " "); 281 renderValue(pVal); 282 printf("\n"); 283 } 284 } 285 } 286 sqlite3changeset_finalize(pIter); 287 }else 288 289 /* changeset FILENAME invert OUT 290 ** Invert the changes in FILENAME and writes the result on OUT 291 */ 292 if( strcmp(argv[2],"invert")==0 ){ 293 FILE *out; 294 int szOut = 0; 295 void *pOutBuf = 0; 296 const char *zOut = argv[3]; 297 if( argc!=4 ) usage(argv[0]); 298 out = fopen(zOut, "wb"); 299 if( out==0 ){ 300 fprintf(stderr, "cannot open \"%s\" for writing\n", zOut); 301 exit(1); 302 } 303 sqlite3changeset_invert(sz, pBuf, &szOut, &pOutBuf); 304 if( fwrite(pOutBuf, szOut, 1, out)!=1 ){ 305 fprintf(stderr, "unable to write all %d bytes of output to \"%s\"\n", 306 szOut, zOut); 307 } 308 fclose(out); 309 sqlite3_free(pOutBuf); 310 }else 311 312 /* changeset FILE sql 313 ** Show the content of the changeset as pseudo-SQL 314 */ 315 if( strcmp(argv[2],"sql")==0 ){ 316 int cnt = 0; 317 char *zPrevTab = 0; 318 char *zSQLTabName = 0; 319 sqlite3_changeset_iter *pIter = 0; 320 rc = sqlite3changeset_start(&pIter, sz, pBuf); 321 if( rc!=SQLITE_OK ){ 322 fprintf(stderr, "sqlite3changeset_start() returns %d\n", rc); 323 exit(1); 324 } 325 printf("BEGIN;\n"); 326 while( sqlite3changeset_next(pIter)==SQLITE_ROW ){ 327 int op, bIndirect, nCol; 328 const char *zTab; 329 sqlite3changeset_op(pIter, &zTab, &nCol, &op, &bIndirect); 330 cnt++; 331 if( zPrevTab==0 || strcmp(zPrevTab,zTab)!=0 ){ 332 sqlite3_free(zPrevTab); 333 sqlite3_free(zSQLTabName); 334 zPrevTab = sqlite3_mprintf("%s", zTab); 335 if( !isalnum(zTab[0]) || sqlite3_strglob("*[^a-zA-Z0-9]*",zTab)==0 ){ 336 zSQLTabName = sqlite3_mprintf("\"%w\"", zTab); 337 }else{ 338 zSQLTabName = sqlite3_mprintf("%s", zTab); 339 } 340 printf("/****** Changes for table %s ***************/\n", zSQLTabName); 341 } 342 switch( op ){ 343 case SQLITE_DELETE: { 344 unsigned char *abPK; 345 int i; 346 const char *zSep = " "; 347 sqlite3changeset_pk(pIter, &abPK, 0); 348 printf("/* %d */ DELETE FROM %s WHERE", cnt, zSQLTabName); 349 for(i=0; i<nCol; i++){ 350 sqlite3_value *pVal; 351 if( abPK[i]==0 ) continue; 352 printf("%sc%d=", zSep, i+1); 353 zSep = " AND "; 354 sqlite3changeset_old(pIter, i, &pVal); 355 renderValue(pVal); 356 } 357 printf(";\n"); 358 break; 359 } 360 case SQLITE_UPDATE: { 361 unsigned char *abPK; 362 int i; 363 const char *zSep = " "; 364 sqlite3changeset_pk(pIter, &abPK, 0); 365 printf("/* %d */ UPDATE %s SET", cnt, zSQLTabName); 366 for(i=0; i<nCol; i++){ 367 sqlite3_value *pVal = 0; 368 sqlite3changeset_new(pIter, i, &pVal); 369 if( pVal ){ 370 printf("%sc%d=", zSep, i+1); 371 zSep = ", "; 372 renderValue(pVal); 373 } 374 } 375 printf(" WHERE"); 376 zSep = " "; 377 for(i=0; i<nCol; i++){ 378 sqlite3_value *pVal; 379 if( abPK[i]==0 ) continue; 380 printf("%sc%d=", zSep, i+1); 381 zSep = " AND "; 382 sqlite3changeset_old(pIter, i, &pVal); 383 renderValue(pVal); 384 } 385 printf(";\n"); 386 break; 387 } 388 case SQLITE_INSERT: { 389 int i; 390 printf("/* %d */ INSERT INTO %s VALUES", cnt, zSQLTabName); 391 for(i=0; i<nCol; i++){ 392 sqlite3_value *pVal; 393 printf("%c", i==0 ? '(' : ','); 394 sqlite3changeset_new(pIter, i, &pVal); 395 renderValue(pVal); 396 } 397 printf(");\n"); 398 break; 399 } 400 } 401 } 402 printf("COMMIT;\n"); 403 sqlite3changeset_finalize(pIter); 404 sqlite3_free(zPrevTab); 405 sqlite3_free(zSQLTabName); 406 }else 407 408 /* If nothing else matches, show the usage comment */ 409 usage(argv[0]); 410 sqlite3_free(pBuf); 411 return 0; 412 } 413