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