SQLite

Check-in [67bb88e24c]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the "--lost-and-found" option to the ".recover" command. For setting the name of the orphaned rows table.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | dbdata
Files: files | file ages | folders
SHA3-256: 67bb88e24c74d02ae0c4ac6ff2f873f6b0035ccefe5cccfc71c5686cbc76b4c3
User & Date: dan 2019-04-27 18:47:03.466
Context
2019-04-27
19:36
Add comments and fix formatting issues in new code in shell.c.in. (check-in: b91d819bd1 user: dan tags: dbdata)
18:47
Add the "--lost-and-found" option to the ".recover" command. For setting the name of the orphaned rows table. (check-in: 67bb88e24c user: dan tags: dbdata)
15:35
Fix a problem in the .recover command with recovering WITHOUT ROWID tables where the PK columns are not the leftmost in the CREATE TABLE statement. (check-in: 91df4b8e03 user: dan tags: dbdata)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to ext/misc/dbdata.c.
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
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












-
-
-
-
+
+
+
+
+
+
+
+









-
-
+
+
+

-
-
-
-
-
+
+
+
+
+
+

-
+





-
-
+
+









-
-
-
+
+
+

-
-
-
+
+






+
+
+















-
+
-







/*
** 2019-04-17
**
** 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.
**
******************************************************************************
**
** This file contains an implementation of the eponymous "sqlite_dbdata"
** virtual table. sqlite_dbdata is used to extract data directly from a
** database b-tree page and its associated overflow pages, bypassing the b-tree
** layer. The table schema is equivalent to:
** This file contains an implementation of two eponymous virtual tables,
** "sqlite_dbdata" and "sqlite_dbptr". Both modules require that the
** "sqlite_dbpage" eponymous virtual table be available.
**
** SQLITE_DBDATA:
**   sqlite_dbdata is used to extract data directly from a database b-tree
**   page and its associated overflow pages, bypassing the b-tree layer.
**   The table schema is equivalent to:
**
**     CREATE TABLE sqlite_dbdata(
**       pgno INTEGER,
**       cell INTEGER,
**       field INTEGER,
**       value ANY,
**       schema TEXT HIDDEN
**     );
**
** IMPORTANT: THE VIRTUAL TABLE SCHEMA ABOVE IS SUBJECT TO CHANGE. IN THE
** FUTURE NEW NON-HIDDEN COLUMNS MAY BE ADDED BETWEEN "value" AND "schema".
**   IMPORTANT: THE VIRTUAL TABLE SCHEMA ABOVE IS SUBJECT TO CHANGE. IN THE
**   FUTURE NEW NON-HIDDEN COLUMNS MAY BE ADDED BETWEEN "value" AND
**   "schema".
**
** Each page of the database is inspected. If it cannot be interpreted as a
** b-tree page, or if it is a b-tree page containing 0 entries, the
** sqlite_dbdata table contains no rows for that page.  Otherwise, the table
** contains one row for each field in the record associated with each
** cell on the page. For intkey b-trees, the key value is stored in field -1.
**   Each page of the database is inspected. If it cannot be interpreted as
**   a b-tree page, or if it is a b-tree page containing 0 entries, the
**   sqlite_dbdata table contains no rows for that page.  Otherwise, the
**   table contains one row for each field in the record associated with
**   each cell on the page. For intkey b-trees, the key value is stored in
**   field -1.
**
** For example, for the database:
**   For example, for the database:
**
**     CREATE TABLE t1(a, b);     -- root page is page 2
**     INSERT INTO t1(rowid, a, b) VALUES(5, 'v', 'five');
**     INSERT INTO t1(rowid, a, b) VALUES(10, 'x', 'ten');
**
** the sqlite_dbdata table contains, as well as from entries related to 
** page 1, content equivalent to:
**   the sqlite_dbdata table contains, as well as from entries related to 
**   page 1, content equivalent to:
**
**     INSERT INTO sqlite_dbdata(pgno, cell, field, value) VALUES
**         (2, 0, -1, 5     ),
**         (2, 0,  0, 'v'   ),
**         (2, 0,  1, 'five'),
**         (2, 1, -1, 10    ),
**         (2, 1,  0, 'x'   ),
**         (2, 1,  1, 'ten' );
**
** If database corruption is encountered, this module does not report an
** error. Instead, it attempts to extract as much data as possible and
** ignores the corruption.
**   If database corruption is encountered, this module does not report an
**   error. Instead, it attempts to extract as much data as possible and
**   ignores the corruption.
**
** This module requires that the "sqlite_dbpage" eponymous virtual table be
** available.
**
** SQLITE_DBPTR:
**   The sqlite_dbptr table has the following schema:
**
**     CREATE TABLE sqlite_dbptr(
**       pgno INTEGER,
**       child INTEGER,
**       schema TEXT HIDDEN
**     );
**
**   It contains one entry for each b-tree pointer between a parent and
**   child page in the database.
*/
#if !defined(SQLITEINT_H) 
#include "sqlite3ext.h"

typedef unsigned char u8;
typedef unsigned long u32;

#endif
SQLITE_EXTENSION_INIT1
#include <string.h>
#include <assert.h>

typedef struct DbdataTable DbdataTable;
typedef struct DbdataCursor DbdataCursor;


/* Cursor object */
/* A cursor for the sqlite_dbdata table */
struct DbdataCursor {
  sqlite3_vtab_cursor base;       /* Base class.  Must be first */
  sqlite3_stmt *pStmt;            /* For fetching database pages */

  int iPgno;                      /* Current page number */
  u8 *aPage;                      /* Buffer containing page */
  int nPage;                      /* Size of aPage[] in bytes */
99
100
101
102
103
104
105
106

107
108
109
110
111
112
113

114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132




133
134
135
136
137
138
139
140

141

142
143
144
145
146
147
148
106
107
108
109
110
111
112

113
114
115
116
117
118
119
120
121
122
123
124
125
126





127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148

149
150
151
152
153
154
155
156







-
+







+





-
-
-
-
-









+
+
+
+








+
-
+







  int iField;                     /* Current field number */
  u8 *pHdrPtr;
  u8 *pPtr;
  
  sqlite3_int64 iIntkey;          /* Integer key value */
};

/* The sqlite_dbdata table */
/* Table object */
struct DbdataTable {
  sqlite3_vtab base;              /* Base class.  Must be first */
  sqlite3 *db;                    /* The database connection */
  sqlite3_stmt *pStmt;            /* For fetching database pages */
  int bPtr;                       /* True for sqlite3_dbptr table */
};

/* Column and schema definitions for sqlite_dbdata */
#define DBDATA_COLUMN_PGNO        0
#define DBDATA_COLUMN_CELL        1
#define DBDATA_COLUMN_FIELD       2
#define DBDATA_COLUMN_VALUE       3
#define DBDATA_COLUMN_SCHEMA      4

#define DBPTR_COLUMN_PGNO         0
#define DBPTR_COLUMN_CHILD        1
#define DBPTR_COLUMN_SCHEMA       2

#define DBDATA_SCHEMA             \
      "CREATE TABLE x("           \
      "  pgno INTEGER,"           \
      "  cell INTEGER,"           \
      "  field INTEGER,"          \
      "  value ANY,"              \
      "  schema TEXT HIDDEN"      \
      ")"

/* Column and schema definitions for sqlite_dbptr */
#define DBPTR_COLUMN_PGNO         0
#define DBPTR_COLUMN_CHILD        1
#define DBPTR_COLUMN_SCHEMA       2
#define DBPTR_SCHEMA              \
      "CREATE TABLE x("           \
      "  pgno INTEGER,"           \
      "  child INTEGER,"          \
      "  schema TEXT HIDDEN"      \
      ")"

/*
** Connect to an sqlite_dbdata (pAux==0) or sqlite_dbptr (pAux!=0) virtual 
** Connect to the sqlite_dbdata virtual table.
** table.
*/
static int dbdataConnect(
  sqlite3 *db,
  void *pAux,
  int argc, const char *const*argv,
  sqlite3_vtab **ppVtab,
  char **pzErr
162
163
164
165
166
167
168
169

170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
170
171
172
173
174
175
176

177
178
179
180
181
182
183
184
185
186
187
188

189
190
191
192
193
194
195







-
+











-







  }

  *ppVtab = (sqlite3_vtab*)pTab;
  return rc;
}

/*
** Disconnect from or destroy a dbdata virtual table.
** Disconnect from or destroy a sqlite_dbdata or sqlite_dbptr virtual table.
*/
static int dbdataDisconnect(sqlite3_vtab *pVtab){
  DbdataTable *pTab = (DbdataTable*)pVtab;
  if( pTab ){
    sqlite3_finalize(pTab->pStmt);
    sqlite3_free(pVtab);
  }
  return SQLITE_OK;
}

/*
**
** This function interprets two types of constraints:
**
**       schema=?
**       pgno=?
**
** If neither are present, idxNum is set to 0. If schema=? is present,
** the 0x01 bit in idxNum is set. If pgno=? is present, the 0x02 bit
235
236
237
238
239
240
241
242

243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258




259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274

275
276
277
278
279
280
281
282
283
284



285
286
287
288
289
290
291










292
293
294
295
296




297
298
299
300
301
302
303
242
243
244
245
246
247
248

249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284

285
286
287
288
289
290
291
292
293


294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314




315
316
317
318
319
320
321
322
323
324
325







-
+
















+
+
+
+















-
+








-
-
+
+
+







+
+
+
+
+
+
+
+
+
+

-
-
-
-
+
+
+
+







    pIdx->estimatedRows = 1000000000;
  }
  pIdx->idxNum = (iSchema>=0 ? 0x01 : 0x00) | (iPgno>=0 ? 0x02 : 0x00);
  return SQLITE_OK;
}

/*
** Open a new dbdata cursor.
** Open a new sqlite_dbdata or sqlite_dbptr cursor.
*/
static int dbdataOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
  DbdataCursor *pCsr;

  pCsr = (DbdataCursor*)sqlite3_malloc64(sizeof(DbdataCursor));
  if( pCsr==0 ){
    return SQLITE_NOMEM;
  }else{
    memset(pCsr, 0, sizeof(DbdataCursor));
    pCsr->base.pVtab = pVTab;
  }

  *ppCursor = (sqlite3_vtab_cursor *)pCsr;
  return SQLITE_OK;
}

/*
** Restore a cursor object to the state it was in when first allocated 
** by dbdataOpen().
*/
static void dbdataResetCursor(DbdataCursor *pCsr){
  DbdataTable *pTab = (DbdataTable*)(pCsr->base.pVtab);
  if( pTab->pStmt==0 ){
    pTab->pStmt = pCsr->pStmt;
  }else{
    sqlite3_finalize(pCsr->pStmt);
  }
  pCsr->pStmt = 0;
  pCsr->iPgno = 1;
  pCsr->iCell = 0;
  pCsr->iField = 0;
  pCsr->bOnePage = 0;
}

/*
** Close a dbdata cursor.
** Close an sqlite_dbdata or sqlite_dbptr cursor.
*/
static int dbdataClose(sqlite3_vtab_cursor *pCursor){
  DbdataCursor *pCsr = (DbdataCursor*)pCursor;
  dbdataResetCursor(pCsr);
  sqlite3_free(pCsr);
  return SQLITE_OK;
}


/* Decode big-endian integers */
/* 
** Utility methods to decode 16 and 32-bit big-endian unsigned integers. 
*/
static unsigned int get_uint16(unsigned char *a){
  return (a[0]<<8)|a[1];
}
static unsigned int get_uint32(unsigned char *a){
  return (a[0]<<24)|(a[1]<<16)|(a[2]<<8)|a[3];
}

/*
** Load page pgno from the database via the sqlite_dbpage virtual table.
** If successful, set (*ppPage) to point to a buffer containing the page
** data, (*pnPage) to the size of that buffer in bytes and return
** SQLITE_OK. In this case it is the responsibility of the caller to
** eventually free the buffer using sqlite3_free().
**
** Or, if an error occurs, set both (*ppPage) and (*pnPage) to 0 and
** return an SQLite error code.
*/
static int dbdataLoadPage(
  DbdataCursor *pCsr, 
  u32 pgno,
  u8 **ppPage,
  int *pnPage
  DbdataCursor *pCsr,             /* Cursor object */
  u32 pgno,                       /* Page number of page to load */
  u8 **ppPage,                    /* OUT: pointer to page buffer */
  int *pnPage                     /* OUT: Size of (*ppPage) in bytes */
){
  int rc2;
  int rc = SQLITE_OK;
  sqlite3_stmt *pStmt = pCsr->pStmt;

  *ppPage = 0;
  *pnPage = 0;
334
335
336
337
338
339
340




341
342
343
344
345
346
347
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373







+
+
+
+







    if( (z[i]&0x80)==0 ){ *pVal = v; return i+1; }
  }
  v = (v<<8) + (z[i]&0xff);
  *pVal = v;
  return 9;
}

/*
** Return the number of bytes of space used by an SQLite value of type
** eType.
*/
static int dbdataValueBytes(int eType){
  switch( eType ){
    case 0: case 8: case 9:
    case 10: case 11:
      return 0;
    case 1:
      return 1;
357
358
359
360
361
362
363




364
365
366
367
368
369
370
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400







+
+
+
+







    case 7:
      return 8;
    default:
      return ((eType-12) / 2);
  }
}

/*
** Load a value of type eType from buffer pData and use it to set the
** result of context object pCtx.
*/
static void dbdataValue(sqlite3_context *pCtx, int eType, u8 *pData){
  switch( eType ){
    case 0: 
    case 10: 
    case 11: 
      sqlite3_result_null(pCtx);
      break;
407
408
409
410
411
412
413
414

415
416
417
418
419
420
421
437
438
439
440
441
442
443

444
445
446
447
448
449
450
451







-
+







      }
    }
  }
}


/*
** Move a dbdata cursor to the next entry in the file.
** Move an sqlite_dbdata or sqlite_dbptr cursor to the next entry.
*/
static int dbdataNext(sqlite3_vtab_cursor *pCursor){
  DbdataCursor *pCsr = (DbdataCursor*)pCursor;
  DbdataTable *pTab = (DbdataTable*)pCursor->pVtab;

  pCsr->iRowid++;
  while( 1 ){
571
572
573
574
575
576
577
578
579


580
581
582
583
584
585






586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604


605
606
607
608
609
610
611
601
602
603
604
605
606
607


608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639

640
641
642
643
644
645
646
647
648







-
-
+
+






+
+
+
+
+
+


















-
+
+







    }
  }

  assert( !"can't get here" );
  return SQLITE_OK;
}

/* We have reached EOF if previous sqlite3_step() returned
** anything other than SQLITE_ROW;
/* 
** Return true if the cursor is at EOF.
*/
static int dbdataEof(sqlite3_vtab_cursor *pCursor){
  DbdataCursor *pCsr = (DbdataCursor*)pCursor;
  return pCsr->aPage==0;
}

/* 
** Determine the size in pages of database zSchema (where zSchema is
** "main", "temp" or the name of an attached database) and set 
** pCsr->szDb accordingly. If successful, return SQLITE_OK. Otherwise,
** an SQLite error code.
*/
static int dbdataDbsize(DbdataCursor *pCsr, const char *zSchema){
  DbdataTable *pTab = (DbdataTable*)pCsr->base.pVtab;
  char *zSql = 0;
  int rc, rc2;
  sqlite3_stmt *pStmt = 0;

  zSql = sqlite3_mprintf("PRAGMA %Q.page_count", zSchema);
  if( zSql==0 ) return SQLITE_NOMEM;
  rc = sqlite3_prepare_v2(pTab->db, zSql, -1, &pStmt, 0);
  sqlite3_free(zSql);
  if( rc==SQLITE_OK && sqlite3_step(pStmt)==SQLITE_ROW ){
    pCsr->szDb = sqlite3_column_int(pStmt, 0);
  }
  rc2 = sqlite3_finalize(pStmt);
  if( rc==SQLITE_OK ) rc = rc2;
  return rc;
}

/* Position a cursor back to the beginning.
/* 
** xFilter method for sqlite_dbdata and sqlite_dbptr.
*/
static int dbdataFilter(
  sqlite3_vtab_cursor *pCursor, 
  int idxNum, const char *idxStr,
  int argc, sqlite3_value **argv
){
  DbdataCursor *pCsr = (DbdataCursor*)pCursor;
644
645
646
647
648
649
650

651


652
653
654
655
656
657
658
681
682
683
684
685
686
687
688

689
690
691
692
693
694
695
696
697







+
-
+
+







  }
  if( rc==SQLITE_OK ){
    rc = dbdataNext(pCursor);
  }
  return rc;
}

/* 
/* Return a column for the sqlite_dbdata table */
** Return a column for the sqlite_dbdata or sqlite_dbptr table.
*/
static int dbdataColumn(
  sqlite3_vtab_cursor *pCursor, 
  sqlite3_context *ctx, 
  int i
){
  DbdataCursor *pCsr = (DbdataCursor*)pCursor;
  DbdataTable *pTab = (DbdataTable*)pCursor->pVtab;
695
696
697
698
699
700
701

702


703
704
705
706
707
708
709
734
735
736
737
738
739
740
741

742
743
744
745
746
747
748
749
750







+
-
+
+







        break;
      }
    }
  }
  return SQLITE_OK;
}

/* 
/* Return the ROWID for the sqlite_dbdata table */
** Return the rowid for an sqlite_dbdata or sqlite_dptr table.
*/
static int dbdataRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){
  DbdataCursor *pCsr = (DbdataCursor*)pCursor;
  *pRowid = pCsr->iRowid;
  return SQLITE_OK;
}


Changes to src/shell.c.in.
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1100
1101
1102
1103
1104
1105
1106

1107
1108
1109
1110
1111
1112
1113







-







#define SHFLG_Pagecache      0x00000001 /* The --pagecache option is used */
#define SHFLG_Lookaside      0x00000002 /* Lookaside memory is used */
#define SHFLG_Backslash      0x00000004 /* The --backslash option is used */
#define SHFLG_PreserveRowid  0x00000008 /* .dump preserves rowid values */
#define SHFLG_Newlines       0x00000010 /* .dump --newline flag */
#define SHFLG_CountChanges   0x00000020 /* .changes setting */
#define SHFLG_Echo           0x00000040 /* .echo or --echo setting */
#define SHFLG_Recover        0x00000080 /* .dump is --recover */

/*
** Macros for testing and setting shellFlgs
*/
#define ShellHasFlag(P,X)    (((P)->shellFlgs & (X))!=0)
#define ShellSetFlag(P,X)    ((P)->shellFlgs|=(X))
#define ShellClearFlag(P,X)  ((P)->shellFlgs&=(~(X)))
3573
3574
3575
3576
3577
3578
3579

3580
3581
3582
3583
3584
3585
3586
3572
3573
3574
3575
3576
3577
3578
3579
3580
3581
3582
3583
3584
3585
3586







+







  "   --once                    Do no more than one progress interrupt",
  "   --quiet|-q                No output except at interrupts",
  "   --reset                   Reset the count for each input and interrupt",
#endif
  ".prompt MAIN CONTINUE    Replace the standard prompts",
  ".quit                    Exit this program",
  ".read FILE               Read input from FILE",
  ".recover                 Recover as much data as possible from corrupt db.",
  ".restore ?DB? FILE       Restore content of DB (default \"main\") from FILE",
  ".save FILE               Write in-memory database into FILE",
  ".scanstats on|off        Turn sqlite3_stmt_scanstatus() metrics on or off",
  ".schema ?PATTERN?        Show the CREATE statements matching PATTERN",
  "     Options:",
  "         --indent            Try to pretty-print the schema",
  ".selftest ?OPTIONS?      Run tests defined in the SELFTEST table",
6149
6150
6151
6152
6153
6154
6155






6156
6157
6158
6159
6160
6161
6162
6163
6164
6165
6166
6167



6168
6169
6170
6171
6172
6173
6174
6175
6176
6177
6178
6179
6180
6181
6182
6183






6184
6185
6186
6187
6188
6189
6190
6191
6192
6193
6194
6195
6196











6197
6198
6199
6200
6201
6202
6203
6204
6205
6206
6207
6208
6209
6210





6211
6212
6213
6214

6215
6216
6217

6218
6219
6220
6221


6222
6223
6224
6225
6226
6227
6228
6229
6230
6231
6232
6233
6234
6235
6236
6237







6238

6239
6240
6241
6242
6243
6244
6245
6149
6150
6151
6152
6153
6154
6155
6156
6157
6158
6159
6160
6161
6162
6163
6164
6165
6166
6167
6168
6169
6170
6171
6172
6173
6174
6175
6176
6177
6178
6179
6180
6181
6182
6183
6184
6185
6186
6187
6188
6189
6190
6191
6192
6193
6194
6195
6196
6197
6198
6199
6200
6201
6202
6203
6204
6205
6206
6207
6208
6209
6210
6211
6212
6213
6214
6215
6216
6217
6218
6219
6220
6221
6222
6223
6224
6225
6226
6227
6228
6229
6230
6231
6232
6233
6234
6235
6236
6237
6238
6239
6240
6241
6242
6243


6244
6245
6246

6247
6248
6249
6250

6251
6252
6253
6254
6255

6256
6257
6258
6259
6260
6261
6262
6263
6264
6265
6266
6267
6268
6269
6270
6271
6272
6273
6274

6275
6276
6277
6278
6279
6280
6281
6282







+
+
+
+
+
+












+
+
+
















+
+
+
+
+
+













+
+
+
+
+
+
+
+
+
+
+














+
+
+
+
+


-
-
+


-
+



-
+
+



-












+
+
+
+
+
+
+
-
+








  return rc;
}
/* End of the ".archive" or ".ar" command logic
**********************************************************************************/
#endif /* !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB) */

/*
** If (*pRc) is not SQLITE_OK when this function is called, it is a no-op.
** Otherwise, the SQL statement or statements in zSql are executed using
** database connection db and the error code written to *pRc before
** this function returns.
*/
static void shellExec(sqlite3 *db, int *pRc, const char *zSql){
  int rc = *pRc;
  if( rc==SQLITE_OK ){
    char *zErr = 0;
    rc = sqlite3_exec(db, zSql, 0, 0, &zErr);
    if( rc!=SQLITE_OK ){
      raw_printf(stderr, "SQL error: %s\n", zErr);
    }
    *pRc = rc;
  }
}

/*
** Like shellExec(), except that zFmt is a printf() style format string.
*/
static void shellExecPrintf(sqlite3 *db, int *pRc, const char *zFmt, ...){
  char *z = 0;
  if( *pRc==SQLITE_OK ){
    va_list ap;
    va_start(ap, zFmt);
    z = sqlite3_vmprintf(zFmt, ap);
    va_end(ap);
    if( z==0 ){
      *pRc = SQLITE_NOMEM;
    }else{
      shellExec(db, pRc, z);
    }
    sqlite3_free(z);
  }
}

/*
** If *pRc is not SQLITE_OK when this function is called, it is a no-op.
** Otherwise, an attempt is made to allocate, zero and return a pointer
** to a buffer nByte bytes in size. If an OOM error occurs, *pRc is set
** to SQLITE_NOMEM and NULL returned.
*/
static void *shellMalloc(int *pRc, sqlite3_int64 nByte){
  void *pRet = 0;
  if( *pRc==SQLITE_OK ){
    pRet = sqlite3_malloc64(nByte);
    if( pRet==0 ){
      *pRc = SQLITE_NOMEM;
    }else{
      memset(pRet, 0, nByte);
    }
  }
  return pRet;
}

/*
** If *pRc is not SQLITE_OK when this function is called, it is a no-op.
** Otherwise, zFmt is treated as a printf() style string. The result of
** formatting it along with any trailing arguments is written into a 
** buffer obtained from sqlite3_malloc(), and pointer to which is returned.
** It is the responsibility of the caller to eventually free this buffer
** using a call to sqlite3_free().
** 
** If an OOM error occurs, (*pRc) is set to SQLITE_NOMEM and a NULL 
** pointer returned.
*/
static char *shellMPrintf(int *pRc, const char *zFmt, ...){
  char *z = 0;
  if( *pRc==SQLITE_OK ){
    va_list ap;
    va_start(ap, zFmt);
    z = sqlite3_vmprintf(zFmt, ap);
    va_end(ap);
    if( z==0 ){
      *pRc = SQLITE_NOMEM;
    }
  }
  return z;
}

/*
** When running the ".recover" command, each output table, and the special
** orphaned row table if it is required, is represented by an instance
** of the following struct.
*/
typedef struct RecoverTable RecoverTable;
struct RecoverTable {
  char *zName;                    /* Name of table */
  char *zQuoted;                  /* Quoted version of zName */
  char *zQuoted;                  /* Quoted version of table name */
  int nCol;                       /* Number of columns in table */
  char **azlCol;                  /* Array of column lists */
  int iPk;
  int iPk;                        /* Index of IPK column */
};

/*
** Free a RecoverTable object allocated by recoverNewTable()
** Free a RecoverTable object allocated by recoverFindTable() or
** recoverOrphanTable().
*/
static void recoverFreeTable(RecoverTable *pTab){
  if( pTab ){
    sqlite3_free(pTab->zName);
    sqlite3_free(pTab->zQuoted);
    if( pTab->azlCol ){
      int i;
      for(i=0; i<=pTab->nCol; i++){
        sqlite3_free(pTab->azlCol[i]);
      }
      sqlite3_free(pTab->azlCol);
    }
    sqlite3_free(pTab);
  }
}

/*
** This function is a no-op if (*pRc) is not SQLITE_OK when it is called.
** Otherwise, it allocates and returns a RecoverTable object based on the
** final four arguments passed to this function. It is the responsibility
** of the caller to eventually free the returned object using
** recoverFreeTable().
*/
static RecoverTable *recoverOldTable(
static RecoverTable *recoverNewTable(
  int *pRc,                       /* IN/OUT: Error code */
  const char *zName,              /* Name of table */
  const char *zSql,               /* CREATE TABLE statement */
  int bIntkey, 
  int nCol
){
  sqlite3 *dbtmp = 0;             /* sqlite3 handle for testing CREATE TABLE */
6305
6306
6307
6308
6309
6310
6311
6312
6313

6314
6315
6316
6317
6318
6319
6320
6342
6343
6344
6345
6346
6347
6348


6349
6350
6351
6352
6353
6354
6355
6356







-
-
+







        );
        if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pPkFinder) ){
          pTab->iPk = sqlite3_column_int(pPkFinder, 0);
          zPk = (const char*)sqlite3_column_text(pPkFinder, 1);
        }
      }

      pTab->zName = shellMPrintf(&rc, "%s", zName);
      pTab->zQuoted = shellMPrintf(&rc, "%Q", pTab->zName);
      pTab->zQuoted = shellMPrintf(&rc, "%Q", zName);
      pTab->azlCol = (char**)shellMalloc(&rc, sizeof(char*) * (nSqlCol+1));
      pTab->nCol = nSqlCol;

      if( bIntkey ){
        pTab->azlCol[0] = shellMPrintf(&rc, "%Q", zPk);
      }else{
        pTab->azlCol[0] = shellMPrintf(&rc, "");
6345
6346
6347
6348
6349
6350
6351
6352

6353
6354
6355
6356
6357
6358
6359
6360
6361
6362
6363
6364
6365
6366
6367
6368
6369
6370
6371
6372
6373
6374
6375
6376
6377
6378
6379
6380

6381
6382
6383
6384
6385
6386
6387
6388
6389
6390
6391
6392

6393
6394
6395
6396
6397








6398
6399


6400
6401
6402
6403






6404
6405

6406
6407
6408
6409
6410

6411
6412
6413
6414
6415
6416
6417
6418
6419
6420
6421
6422

6423
6424
6425
6426
6427















6428
6429
6430
6431
6432
6433
6434
6435
6436
6437
6438
6439
6440
6441
6442

6443
6444
6445
6446
6447
6448
6449
6450
6451
6452
6453
6454
6455

6456
6457
6458




6459
6460
6461
6462
6463
6464

6465
6466
6467
6468
6469
6470
6471
6381
6382
6383
6384
6385
6386
6387

6388
6389
6390
6391
6392
6393
6394
6395
6396
6397
6398
6399
6400

6401
6402
6403
6404
6405
6406
6407
6408
6409
6410
6411
6412
6413
6414

6415
6416
6417
6418
6419
6420
6421
6422
6423
6424
6425
6426
6427
6428
6429
6430
6431
6432
6433
6434
6435
6436
6437
6438
6439
6440
6441


6442
6443

6444


6445
6446
6447
6448
6449
6450
6451

6452
6453
6454
6455


6456
6457
6458
6459
6460
6461
6462
6463
6464
6465
6466
6467

6468





6469
6470
6471
6472
6473
6474
6475
6476
6477
6478
6479
6480
6481
6482
6483
6484
6485
6486
6487
6488
6489
6490
6491
6492
6493
6494
6495
6496
6497
6498
6499
6500
6501
6502
6503
6504
6505
6506
6507
6508
6509
6510
6511

6512
6513
6514
6515
6516
6517
6518
6519
6520
6521
6522
6523
6524
6525
6526
6527
6528
6529
6530
6531
6532
6533







-
+












-














-
+












+





+
+
+
+
+
+
+
+
-
-
+
+
-

-
-
+
+
+
+
+
+

-
+



-
-
+











-
+
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+















+












-
+



+
+
+
+






+







  if( rc!=SQLITE_OK ){
    recoverFreeTable(pTab);
    pTab = 0;
  }
  return pTab;
}

static RecoverTable *recoverNewTable(
static RecoverTable *recoverFindTable(
  ShellState *pState, 
  int *pRc,
  int iRoot,
  int bIntkey,
  int nCol,
  int *pbNoop
){
  sqlite3_stmt *pStmt = 0;
  RecoverTable *pRet = 0;
  int bNoop = 0;
  const char *zSql = 0;
  const char *zName = 0;


  /* Search the recovered schema for an object with root page iRoot. */
  shellPreparePrintf(pState->db, pRc, &pStmt,
      "SELECT type, name, sql FROM recovery.schema WHERE rootpage=%d", iRoot
  );
  while( *pRc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
    const char *zType = (const char*)sqlite3_column_text(pStmt, 0);
    if( bIntkey==0 && sqlite3_stricmp(zType, "index")==0 ){
      bNoop = 1;
      break;
    }
    if( sqlite3_stricmp(zType, "table")==0 ){
      zName = (const char*)sqlite3_column_text(pStmt, 1);
      zSql = (const char*)sqlite3_column_text(pStmt, 2);
      pRet = recoverOldTable(pRc, zName, zSql, bIntkey, nCol);
      pRet = recoverNewTable(pRc, zName, zSql, bIntkey, nCol);
      break;
    }
  }

  shellFinalize(pRc, pStmt);
  *pbNoop = bNoop;
  return pRet;
}

static RecoverTable *recoverOrphanTable(
  ShellState *pState, 
  int *pRc, 
  const char *zLostAndFound,
  int nCol
){
  RecoverTable *pTab = 0;
  if( nCol>=0 && *pRc==SQLITE_OK ){
    int i;

    /* This block determines the name of the orphan table. The prefered
    ** name is zLostAndFound. But if that clashes with another name
    ** in the recovered schema, try zLostAndFound_0, zLostAndFound_1
    ** and so on until a non-clashing name is found.  */
    int iTab = 0;
    char *zTab = shellMPrintf(pRc, "%s", zLostAndFound);
    sqlite3_stmt *pTest = 0;
    raw_printf(pState->out, 
        "CREATE TABLE recover_orphan(rootpgno INTEGER, "
    shellPrepare(pState->db, pRc,
        "SELECT 1 FROM recovery.schema WHERE name=?", &pTest
        "pgno INTEGER, nfield INTEGER, id INTEGER"
    );
    for(i=0; i<nCol; i++){
      raw_printf(pState->out, ", c%d", i);
    if( pTest ) sqlite3_bind_text(pTest, 1, zTab, -1, SQLITE_TRANSIENT);
    while( *pRc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pTest) ){
      shellReset(pRc, pTest);
      sqlite3_free(zTab);
      zTab = shellMPrintf(pRc, "%s_%d", zLostAndFound, iTab++);
      sqlite3_bind_text(pTest, 1, zTab, -1, SQLITE_TRANSIENT);
    }
    raw_printf(pState->out, ");\n");
    shellFinalize(pRc, pTest);

    pTab = (RecoverTable*)shellMalloc(pRc, sizeof(RecoverTable));
    if( pTab ){
      pTab->zName = shellMPrintf(pRc, "%s", "recover_orphan");
      pTab->zQuoted = shellMPrintf(pRc, "%Q", pTab->zName);
      pTab->zQuoted = shellMPrintf(pRc, "%Q", zTab);
      pTab->nCol = nCol;
      pTab->iPk = -2;
      if( nCol>0 ){
        pTab->azlCol = (char**)shellMalloc(pRc, sizeof(char*) * (nCol+1));
        if( pTab->azlCol ){
          pTab->azlCol[nCol] = shellMPrintf(pRc, "");
          for(i=nCol-1; i>=0; i--){
            pTab->azlCol[i] = shellMPrintf(pRc, "%s, NULL", pTab->azlCol[i+1]);
          }
        }
      }
    }


    if( *pRc!=SQLITE_OK ){
      recoverFreeTable(pTab);
      pTab = 0;
    }
      if( *pRc!=SQLITE_OK ){
        recoverFreeTable(pTab);
        pTab = 0;
      }else{
        raw_printf(pState->out, 
            "CREATE TABLE %s(rootpgno INTEGER, "
            "pgno INTEGER, nfield INTEGER, id INTEGER", pTab->zQuoted
        );
        for(i=0; i<nCol; i++){
          raw_printf(pState->out, ", c%d", i);
        }
        raw_printf(pState->out, ");\n");
      }
    }
    sqlite3_free(zTab);
  }
  return pTab;
}

/*
** This function is called to recover data from the database. A script
** to construct a new database containing all recovered data is output
** on stream pState->out.
*/
static int recoverDatabaseCmd(ShellState *pState, int nArg, char **azArg){
  int rc = SQLITE_OK;
  sqlite3_stmt *pLoop = 0;        /* Loop through all root pages */
  sqlite3_stmt *pPages = 0;       /* Loop through all pages in a group */
  sqlite3_stmt *pCells = 0;       /* Loop through all cells in a page */
  const char *zRecoveryDb = "";   /* Name of "recovery" database */
  const char *zLostAndFound = "lost_and_found";
  int i;
  int nOrphan = -1;
  RecoverTable *pOrphan = 0;

  int bFreelist = 1;              /* 0 if --freelist-corrupt is specified */
  for(i=1; i<nArg; i++){
    char *z = azArg[i];
    int n;
    if( z[0]=='-' && z[1]=='-' ) z++;
    n = strlen(z);
    if( n<=17 && memcmp("-freelist-corrupt", z, n)==0 ){
      bFreelist = 0;
    }
    }else
    if( n<=12 && memcmp("-recovery-db", z, n)==0 && i<(nArg-1) ){
      i++;
      zRecoveryDb = azArg[i];
    }else
    if( n<=15 && memcmp("-lost-and-found", z, n)==0 && i<(nArg-1) ){
      i++;
      zLostAndFound = azArg[i];
    }
    else{
      raw_printf(stderr, "unexpected option: %s\n", azArg[i]); 
      raw_printf(stderr, "options are:\n");
      raw_printf(stderr, "    --freelist-corrupt\n");
      raw_printf(stderr, "    --recovery-db DATABASE\n");
      raw_printf(stderr, "    --lost-and-found TABLE-NAME\n");
      return 1;
    }
  }

  shellExecPrintf(pState->db, &rc,
    /* Attach an in-memory database named 'recovery'. Create an indexed 
    ** cache of the sqlite_dbptr virtual table. */
6595
6596
6597
6598
6599
6600
6601
6602

6603
6604
6605
6606
6607
6608
6609
6657
6658
6659
6660
6661
6662
6663

6664
6665
6666
6667
6668
6669
6670
6671







-
+







      , &pLoop
  );
  if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pLoop) ){
    nOrphan = sqlite3_column_int(pLoop, 0);
  }
  shellFinalize(&rc, pLoop);
  pLoop = 0;
  pOrphan = recoverOrphanTable(pState, &rc, nOrphan);
  pOrphan = recoverOrphanTable(pState, &rc, zLostAndFound, nOrphan);

  shellPrepare(pState->db, &rc,
      "SELECT pgno FROM recovery.map WHERE root=?", &pPages
  );
  shellPrepare(pState->db, &rc,
      "SELECT max(field), group_concat(shell_escape_crnl(quote(value)), ', ')"
      "FROM sqlite_dbdata WHERE pgno = ? AND field != ?"
6620
6621
6622
6623
6624
6625
6626
6627

6628
6629
6630
6631

6632
6633
6634
6635
6636
6637
6638
6682
6683
6684
6685
6686
6687
6688

6689
6690
6691
6692

6693
6694
6695
6696
6697
6698
6699
6700







-
+



-
+







  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pLoop) ){
    int iRoot = sqlite3_column_int(pLoop, 0);
    int bIntkey = sqlite3_column_int(pLoop, 1);
    int nCol = sqlite3_column_int(pLoop, 2);
    int bNoop = 0;
    RecoverTable *pTab;

    pTab = recoverNewTable(pState, &rc, iRoot, bIntkey, nCol, &bNoop);
    pTab = recoverFindTable(pState, &rc, iRoot, bIntkey, nCol, &bNoop);
    if( bNoop || rc ) continue;
    if( pTab==0 ) pTab = pOrphan;

    if( 0==sqlite3_stricmp(pTab->zName, "sqlite_sequence") ){
    if( 0==sqlite3_stricmp(pTab->zQuoted, "'sqlite_sequence'") ){
      raw_printf(pState->out, "DELETE FROM sqlite_sequence;\n");
    }
    sqlite3_bind_int(pPages, 1, iRoot);
    sqlite3_bind_int(pCells, 2, pTab->iPk);

    while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pPages) ){
      int iPgno = sqlite3_column_int(pPages, 0);
7038
7039
7040
7041
7042
7043
7044
7045
7046
7047



7048
7049
7050
7051



7052
7053
7054
7055



7056
7057
7058
7059
7060
7061



7062
7063
7064
7065
7066
7067
7068




7069
7070
7071
7072
7073
7074
7075
7100
7101
7102
7103
7104
7105
7106



7107
7108
7109
7110



7111
7112
7113
7114



7115
7116
7117
7118
7119
7120



7121
7122
7123
7124
7125
7126




7127
7128
7129
7130
7131
7132
7133
7134
7135
7136
7137







-
-
-
+
+
+

-
-
-
+
+
+

-
-
-
+
+
+



-
-
-
+
+
+



-
-
-
-
+
+
+
+







    /* Set writable_schema=ON since doing so forces SQLite to initialize
    ** as much of the schema as it can even if the sqlite_master table is
    ** corrupt. */
    sqlite3_exec(p->db, "SAVEPOINT dump; PRAGMA writable_schema=ON", 0, 0, 0);
    p->nErr = 0;
    if( zLike==0 ){
      run_schema_dump_query(p,
          "SELECT name, type, sql FROM sqlite_master "
          "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'"
          );
        "SELECT name, type, sql FROM sqlite_master "
        "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'"
      );
      run_schema_dump_query(p,
          "SELECT name, type, sql FROM sqlite_master "
          "WHERE name=='sqlite_sequence'"
          );
        "SELECT name, type, sql FROM sqlite_master "
        "WHERE name=='sqlite_sequence'"
      );
      run_table_dump_query(p,
          "SELECT sql FROM sqlite_master "
          "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0
          );
        "SELECT sql FROM sqlite_master "
        "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0
      );
    }else{
      char *zSql;
      zSql = sqlite3_mprintf(
          "SELECT name, type, sql FROM sqlite_master "
          "WHERE tbl_name LIKE %Q AND type=='table'"
          "  AND sql NOT NULL", zLike);
        "SELECT name, type, sql FROM sqlite_master "
        "WHERE tbl_name LIKE %Q AND type=='table'"
        "  AND sql NOT NULL", zLike);
      run_schema_dump_query(p,zSql);
      sqlite3_free(zSql);
      zSql = sqlite3_mprintf(
          "SELECT sql FROM sqlite_master "
          "WHERE sql NOT NULL"
          "  AND type IN ('index','trigger','view')"
          "  AND tbl_name LIKE %Q", zLike);
        "SELECT sql FROM sqlite_master "
        "WHERE sql NOT NULL"
        "  AND type IN ('index','trigger','view')"
        "  AND tbl_name LIKE %Q", zLike);
      run_table_dump_query(p, zSql, 0);
      sqlite3_free(zSql);
    }
    if( p->writableSchema ){
      raw_printf(p->out, "PRAGMA writable_schema=OFF;\n");
      p->writableSchema = 0;
    }
Changes to test/recover.test.
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
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







-
+








-

+
-
+
+
+
+







proc compare_dbs {db1 db2} {
  compare_result $db1 $db2 "SELECT sql FROM sqlite_master ORDER BY 1"
  foreach tbl [$db1 eval {SELECT name FROM sqlite_master WHERE type='table'}] {
    compare_result $db1 $db2 "SELECT * FROM $tbl"
  }
}

proc do_recover_test {tn} {
proc do_recover_test {tn {tsql {}} {res {}}} {
  set fd [open "|$::CLI test.db .recover"]
  fconfigure $fd -encoding binary
  fconfigure $fd -translation binary
  set sql [read $fd]
  close $fd

  forcedelete test.db2
  sqlite3 db2 test.db2
  breakpoint
  execsql $sql db2
  if {$tsql==""} {
  uplevel [list do_test $tn [list compare_dbs db db2] {}]
    uplevel [list do_test $tn [list compare_dbs db db2] {}]
  } else {
    uplevel [list do_execsql_test -db db2 $tn $tsql $res]
  }
  db2 close
}

set doc {
  hello
  world
}
91
92
93
94
95
96
97
98



























99
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129








+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+

  CREATE TABLE t1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID;
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
  INSERT INTO t1 VALUES(7, 8, 9);
}

do_recover_test 2.1.1

do_execsql_test 2.2.0 {
  PRAGMA writable_schema = 1;
  DELETE FROM sqlite_master WHERE name='t1';
}
do_recover_test 2.2.1 {
  SELECT name FROM sqlite_master
} {lost_and_found}

do_execsql_test 2.3.0 {
  CREATE TABLE lost_and_found(a, b, c);
}
do_recover_test 2.3.1 {
  SELECT name FROM sqlite_master
} {lost_and_found lost_and_found_0}

do_execsql_test 2.4.0 {
  CREATE TABLE lost_and_found_0(a, b, c);
}
do_recover_test 2.4.1 {
  SELECT name FROM sqlite_master;
  SELECT * FROM lost_and_found_1;
} {lost_and_found lost_and_found_0 lost_and_found_1
  2 2 3 {} 2 3 1
  2 2 3 {} 5 6 4
  2 2 3 {} 8 9 7
}

finish_test