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 int main(int argc, char **argv){ 128 int sz, rc; 129 void *pBuf = 0; 130 if( argc<3 ) usage(argv[0]); 131 readFile(argv[1], &sz, &pBuf); 132 133 /* changeset FILENAME apply DB 134 ** Apply the changeset in FILENAME to the database file DB 135 */ 136 if( strcmp(argv[2],"apply")==0 ){ 137 fprintf(stderr, "not yet implemented\n"); 138 }else 139 140 /* changeset FILENAME concat FILE2 OUT 141 ** Add changeset FILE2 onto the end of the changeset in FILENAME 142 ** and write the result into OUT. 143 */ 144 if( strcmp(argv[2],"concat")==0 ){ 145 fprintf(stderr, "not yet implemented\n"); 146 }else 147 148 /* changeset FILENAME dump 149 ** Show the complete content of the changeset in FILENAME 150 */ 151 if( strcmp(argv[2],"dump")==0 ){ 152 int cnt = 0; 153 int i; 154 sqlite3_changeset_iter *pIter; 155 rc = sqlite3changeset_start(&pIter, sz, pBuf); 156 if( rc!=SQLITE_OK ){ 157 fprintf(stderr, "sqlite3changeset_start() returns %d\n", rc); 158 exit(1); 159 } 160 while( sqlite3changeset_next(pIter)==SQLITE_ROW ){ 161 int op, bIndirect, nCol; 162 const char *zTab; 163 unsigned char *abPK; 164 sqlite3changeset_op(pIter, &zTab, &nCol, &op, &bIndirect); 165 cnt++; 166 printf("%d: %s table=[%s] indirect=%d nColumn=%d\n", 167 cnt, op==SQLITE_INSERT ? "INSERT" : 168 op==SQLITE_UPDATE ? "UPDATE" : "DELETE", 169 zTab, bIndirect, nCol); 170 sqlite3changeset_pk(pIter, &abPK, 0); 171 for(i=0; i<nCol; i++){ 172 sqlite3_value *pVal; 173 pVal = 0; 174 sqlite3changeset_old(pIter, i, &pVal); 175 if( pVal ){ 176 printf(" old[%d]%s = ", i, abPK[i] ? "pk" : " "); 177 renderValue(pVal); 178 printf("\n"); 179 } 180 pVal = 0; 181 sqlite3changeset_new(pIter, i, &pVal); 182 if( pVal ){ 183 printf(" new[%d]%s = ", i, abPK[i] ? "pk" : " "); 184 renderValue(pVal); 185 printf("\n"); 186 } 187 } 188 } 189 sqlite3changeset_finalize(pIter); 190 }else 191 192 /* changeset FILENAME invert OUT 193 ** Invert the changes in FILENAME and writes the result on OUT 194 */ 195 if( strcmp(argv[2],"invert")==0 ){ 196 FILE *out; 197 int szOut = 0; 198 void *pOutBuf = 0; 199 if( argc!=4 ) usage(argv[0]); 200 out = fopen(argv[3], "wb"); 201 if( out==0 ){ 202 fprintf(stderr, "cannot open \"%s\" for writing\n", argv[3]); 203 exit(1); 204 } 205 sqlite3changeset_invert(sz, pBuf, &szOut, &pOutBuf); 206 if( fwrite(pOutBuf, szOut, 1, out)!=1 ){ 207 fprintf(stderr, "unable to write all %d bytes of output to \"%s\"\n", 208 szOut, argv[3]); 209 } 210 fclose(out); 211 sqlite3_free(pOutBuf); 212 }else 213 214 /* changeset FILE sql 215 ** Show the content of the changeset as pseudo-SQL 216 */ 217 if( strcmp(argv[2],"sql")==0 ){ 218 int cnt = 0; 219 char *zPrevTab = 0; 220 char *zSQLTabName = 0; 221 sqlite3_changeset_iter *pIter = 0; 222 rc = sqlite3changeset_start(&pIter, sz, pBuf); 223 if( rc!=SQLITE_OK ){ 224 fprintf(stderr, "sqlite3changeset_start() returns %d\n", rc); 225 exit(1); 226 } 227 printf("BEGIN;\n"); 228 while( sqlite3changeset_next(pIter)==SQLITE_ROW ){ 229 int op, bIndirect, nCol; 230 const char *zTab; 231 sqlite3changeset_op(pIter, &zTab, &nCol, &op, &bIndirect); 232 cnt++; 233 if( zPrevTab==0 || strcmp(zPrevTab,zTab)!=0 ){ 234 sqlite3_free(zPrevTab); 235 sqlite3_free(zSQLTabName); 236 zPrevTab = sqlite3_mprintf("%s", zTab); 237 if( !isalnum(zTab[0]) || sqlite3_strglob("*[^a-zA-Z0-9]*",zTab)==0 ){ 238 zSQLTabName = sqlite3_mprintf("\"%w\"", zTab); 239 }else{ 240 zSQLTabName = sqlite3_mprintf("%s", zTab); 241 } 242 printf("/****** Changes for table %s ***************/\n", zSQLTabName); 243 } 244 switch( op ){ 245 case SQLITE_DELETE: { 246 unsigned char *abPK; 247 int i; 248 const char *zSep = " "; 249 sqlite3changeset_pk(pIter, &abPK, 0); 250 printf("/* %d */ DELETE FROM %s WHERE", cnt, zSQLTabName); 251 for(i=0; i<nCol; i++){ 252 sqlite3_value *pVal; 253 if( abPK[i]==0 ) continue; 254 printf("%sc%d=", zSep, i+1); 255 zSep = " AND "; 256 sqlite3changeset_old(pIter, i, &pVal); 257 renderValue(pVal); 258 } 259 printf(";\n"); 260 break; 261 } 262 case SQLITE_UPDATE: { 263 unsigned char *abPK; 264 int i; 265 const char *zSep = " "; 266 sqlite3changeset_pk(pIter, &abPK, 0); 267 printf("/* %d */ UPDATE %s SET", cnt, zSQLTabName); 268 for(i=0; i<nCol; i++){ 269 sqlite3_value *pVal = 0; 270 sqlite3changeset_new(pIter, i, &pVal); 271 if( pVal ){ 272 printf("%sc%d=", zSep, i+1); 273 zSep = ", "; 274 renderValue(pVal); 275 } 276 } 277 printf(" WHERE"); 278 zSep = " "; 279 for(i=0; i<nCol; i++){ 280 sqlite3_value *pVal; 281 if( abPK[i]==0 ) continue; 282 printf("%sc%d=", zSep, i+1); 283 zSep = " AND "; 284 sqlite3changeset_old(pIter, i, &pVal); 285 renderValue(pVal); 286 } 287 printf(";\n"); 288 break; 289 } 290 case SQLITE_INSERT: { 291 int i; 292 printf("/* %d */ INSERT INTO %s VALUES", cnt, zSQLTabName); 293 for(i=0; i<nCol; i++){ 294 sqlite3_value *pVal; 295 printf("%c", i==0 ? '(' : ','); 296 sqlite3changeset_new(pIter, i, &pVal); 297 renderValue(pVal); 298 } 299 printf(");\n"); 300 break; 301 } 302 } 303 } 304 printf("COMMIT;\n"); 305 sqlite3changeset_finalize(pIter); 306 sqlite3_free(zPrevTab); 307 sqlite3_free(zSQLTabName); 308 }else 309 310 /* If nothing else matches, show the usage comment */ 311 usage(argv[0]); 312 sqlite3_free(pBuf); 313 return 0; 314 } 315