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 rc = sqlite3changeset_concat(sz, pBuf, szB, pB, &szOut, &pOutBuf); 236 if( rc!=SQLITE_OK ){ 237 fprintf(stderr, "sqlite3changeset_concat() returns %d\n", rc); 238 }else if( szOut>0 && fwrite(pOutBuf, szOut, 1, out)!=1 ){ 239 fprintf(stderr, "unable to write all %d bytes of output to \"%s\"\n", 240 szOut, zOut); 241 } 242 fclose(out); 243 sqlite3_free(pOutBuf); 244 sqlite3_free(pB); 245 }else 246 247 /* changeset FILENAME dump 248 ** Show the complete content of the changeset in FILENAME 249 */ 250 if( strcmp(argv[2],"dump")==0 ){ 251 int cnt = 0; 252 int i; 253 sqlite3_changeset_iter *pIter; 254 rc = sqlite3changeset_start(&pIter, sz, pBuf); 255 if( rc!=SQLITE_OK ){ 256 fprintf(stderr, "sqlite3changeset_start() returns %d\n", rc); 257 exit(1); 258 } 259 while( sqlite3changeset_next(pIter)==SQLITE_ROW ){ 260 int op, bIndirect, nCol; 261 const char *zTab; 262 unsigned char *abPK; 263 sqlite3changeset_op(pIter, &zTab, &nCol, &op, &bIndirect); 264 cnt++; 265 printf("%d: %s table=[%s] indirect=%d nColumn=%d\n", 266 cnt, op==SQLITE_INSERT ? "INSERT" : 267 op==SQLITE_UPDATE ? "UPDATE" : "DELETE", 268 zTab, bIndirect, nCol); 269 sqlite3changeset_pk(pIter, &abPK, 0); 270 for(i=0; i<nCol; i++){ 271 sqlite3_value *pVal; 272 pVal = 0; 273 sqlite3changeset_old(pIter, i, &pVal); 274 if( pVal ){ 275 printf(" old[%d]%s = ", i, abPK[i] ? "pk" : " "); 276 renderValue(pVal); 277 printf("\n"); 278 } 279 pVal = 0; 280 sqlite3changeset_new(pIter, i, &pVal); 281 if( pVal ){ 282 printf(" new[%d]%s = ", i, abPK[i] ? "pk" : " "); 283 renderValue(pVal); 284 printf("\n"); 285 } 286 } 287 } 288 sqlite3changeset_finalize(pIter); 289 }else 290 291 /* changeset FILENAME invert OUT 292 ** Invert the changes in FILENAME and writes the result on OUT 293 */ 294 if( strcmp(argv[2],"invert")==0 ){ 295 FILE *out; 296 int szOut = 0; 297 void *pOutBuf = 0; 298 const char *zOut = argv[3]; 299 if( argc!=4 ) usage(argv[0]); 300 out = fopen(zOut, "wb"); 301 if( out==0 ){ 302 fprintf(stderr, "cannot open \"%s\" for writing\n", zOut); 303 exit(1); 304 } 305 rc = sqlite3changeset_invert(sz, pBuf, &szOut, &pOutBuf); 306 if( rc!=SQLITE_OK ){ 307 fprintf(stderr, "sqlite3changeset_invert() returns %d\n", rc); 308 }else if( szOut>0 && fwrite(pOutBuf, szOut, 1, out)!=1 ){ 309 fprintf(stderr, "unable to write all %d bytes of output to \"%s\"\n", 310 szOut, zOut); 311 } 312 fclose(out); 313 sqlite3_free(pOutBuf); 314 }else 315 316 /* changeset FILE sql 317 ** Show the content of the changeset as pseudo-SQL 318 */ 319 if( strcmp(argv[2],"sql")==0 ){ 320 int cnt = 0; 321 char *zPrevTab = 0; 322 char *zSQLTabName = 0; 323 sqlite3_changeset_iter *pIter = 0; 324 rc = sqlite3changeset_start(&pIter, sz, pBuf); 325 if( rc!=SQLITE_OK ){ 326 fprintf(stderr, "sqlite3changeset_start() returns %d\n", rc); 327 exit(1); 328 } 329 printf("BEGIN;\n"); 330 while( sqlite3changeset_next(pIter)==SQLITE_ROW ){ 331 int op, bIndirect, nCol; 332 const char *zTab; 333 sqlite3changeset_op(pIter, &zTab, &nCol, &op, &bIndirect); 334 cnt++; 335 if( zPrevTab==0 || strcmp(zPrevTab,zTab)!=0 ){ 336 sqlite3_free(zPrevTab); 337 sqlite3_free(zSQLTabName); 338 zPrevTab = sqlite3_mprintf("%s", zTab); 339 if( !isalnum(zTab[0]) || sqlite3_strglob("*[^a-zA-Z0-9]*",zTab)==0 ){ 340 zSQLTabName = sqlite3_mprintf("\"%w\"", zTab); 341 }else{ 342 zSQLTabName = sqlite3_mprintf("%s", zTab); 343 } 344 printf("/****** Changes for table %s ***************/\n", zSQLTabName); 345 } 346 switch( op ){ 347 case SQLITE_DELETE: { 348 unsigned char *abPK; 349 int i; 350 const char *zSep = " "; 351 sqlite3changeset_pk(pIter, &abPK, 0); 352 printf("/* %d */ DELETE FROM %s WHERE", cnt, zSQLTabName); 353 for(i=0; i<nCol; i++){ 354 sqlite3_value *pVal; 355 if( abPK[i]==0 ) continue; 356 printf("%sc%d=", zSep, i+1); 357 zSep = " AND "; 358 sqlite3changeset_old(pIter, i, &pVal); 359 renderValue(pVal); 360 } 361 printf(";\n"); 362 break; 363 } 364 case SQLITE_UPDATE: { 365 unsigned char *abPK; 366 int i; 367 const char *zSep = " "; 368 sqlite3changeset_pk(pIter, &abPK, 0); 369 printf("/* %d */ UPDATE %s SET", cnt, zSQLTabName); 370 for(i=0; i<nCol; i++){ 371 sqlite3_value *pVal = 0; 372 sqlite3changeset_new(pIter, i, &pVal); 373 if( pVal ){ 374 printf("%sc%d=", zSep, i+1); 375 zSep = ", "; 376 renderValue(pVal); 377 } 378 } 379 printf(" WHERE"); 380 zSep = " "; 381 for(i=0; i<nCol; i++){ 382 sqlite3_value *pVal; 383 if( abPK[i]==0 ) continue; 384 printf("%sc%d=", zSep, i+1); 385 zSep = " AND "; 386 sqlite3changeset_old(pIter, i, &pVal); 387 renderValue(pVal); 388 } 389 printf(";\n"); 390 break; 391 } 392 case SQLITE_INSERT: { 393 int i; 394 printf("/* %d */ INSERT INTO %s VALUES", cnt, zSQLTabName); 395 for(i=0; i<nCol; i++){ 396 sqlite3_value *pVal; 397 printf("%c", i==0 ? '(' : ','); 398 sqlite3changeset_new(pIter, i, &pVal); 399 renderValue(pVal); 400 } 401 printf(");\n"); 402 break; 403 } 404 } 405 } 406 printf("COMMIT;\n"); 407 sqlite3changeset_finalize(pIter); 408 sqlite3_free(zPrevTab); 409 sqlite3_free(zSQLTabName); 410 }else 411 412 /* If nothing else matches, show the usage comment */ 413 usage(argv[0]); 414 sqlite3_free(pBuf); 415 return 0; 416 } 417