4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
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.
11 ******************************************************************************
13 ** This file implements a C-language subroutine that converts the content
14 ** of an SQLite database into UTF-8 text SQL statements that can be used
15 ** to exactly recreate the original database. ROWID values are preserved.
17 ** A prototype of the implemented subroutine is this:
19 ** int sqlite3_db_dump(
21 ** const char *zSchema,
22 ** const char *zTable,
23 ** void (*xCallback)(void*, const char*),
27 ** The db parameter is the database connection. zSchema is the schema within
28 ** that database which is to be dumped. Usually the zSchema is "main" but
29 ** can also be "temp" or any ATTACH-ed database. If zTable is not NULL, then
30 ** only the content of that one table is dumped. If zTable is NULL, then all
33 ** The generate text is passed to xCallback() in multiple calls. The second
34 ** argument to xCallback() is a copy of the pArg parameter. The first
35 ** argument is some of the output text that this routine generates. The
36 ** signature to xCallback() is designed to make it compatible with fputs().
38 ** The sqlite3_db_dump() subroutine returns SQLITE_OK on success or some error
39 ** code if it encounters a problem.
41 ** If this file is compiled with -DDBDUMP_STANDALONE then a "main()" routine
42 ** is included so that this routine becomes a command-line utility. The
43 ** command-line utility takes two or three arguments which are the name
44 ** of the database file, the schema, and optionally the table, forming the
45 ** first three arguments of a single call to the library routine.
53 ** The state of the dump process.
55 typedef struct DState DState
;
57 sqlite3
*db
; /* The database connection */
58 int nErr
; /* Number of errors seen so far */
59 int rc
; /* Error code */
60 int writableSchema
; /* True if in writable_schema mode */
61 int (*xCallback
)(const char*,void*); /* Send output here */
62 void *pArg
; /* Argument to xCallback() */
66 ** A variable length string to which one can append text.
68 typedef struct DText DText
;
70 char *z
; /* The text */
71 int n
; /* Number of bytes of content in z[] */
72 int nAlloc
; /* Number of bytes allocated to z[] */
76 ** Initialize and destroy a DText object
78 static void initText(DText
*p
){
79 memset(p
, 0, sizeof(*p
));
81 static void freeText(DText
*p
){
86 /* zIn is either a pointer to a NULL-terminated string in memory obtained
87 ** from malloc(), or a NULL pointer. The string pointed to by zAppend is
88 ** added to zIn, and the result returned in memory obtained from malloc().
89 ** zIn, if it was not NULL, is freed.
91 ** If the third argument, quote, is not '\0', then it is used as a
92 ** quote character for zAppend.
94 static void appendText(DText
*p
, char const *zAppend
, char quote
){
97 int nAppend
= (int)(strlen(zAppend
) & 0x3fffffff);
102 for(i
=0; i
<nAppend
; i
++){
103 if( zAppend
[i
]==quote
) len
++;
107 if( p
->n
+len
>=p
->nAlloc
){
109 p
->nAlloc
= p
->nAlloc
*2 + len
+ 20;
110 zNew
= sqlite3_realloc(p
->z
, p
->nAlloc
);
119 char *zCsr
= p
->z
+p
->n
;
121 for(i
=0; i
<nAppend
; i
++){
122 *zCsr
++ = zAppend
[i
];
123 if( zAppend
[i
]==quote
) *zCsr
++ = quote
;
126 p
->n
= (int)(zCsr
- p
->z
);
129 memcpy(p
->z
+p
->n
, zAppend
, nAppend
);
136 ** Attempt to determine if identifier zName needs to be quoted, either
137 ** because it contains non-alphanumeric characters, or because it is an
138 ** SQLite keyword. Be conservative in this estimate: When in doubt assume
139 ** that quoting is required.
141 ** Return '"' if quoting is required. Return 0 if no quoting is required.
143 static char quoteChar(const char *zName
){
144 /* All SQLite keywords, in alphabetical order */
145 static const char *azKeywords
[] = {
146 "ABORT", "ACTION", "ADD", "AFTER", "ALL", "ALTER", "ANALYZE", "AND", "AS",
147 "ASC", "ATTACH", "AUTOINCREMENT", "BEFORE", "BEGIN", "BETWEEN", "BY",
148 "CASCADE", "CASE", "CAST", "CHECK", "COLLATE", "COLUMN", "COMMIT",
149 "CONFLICT", "CONSTRAINT", "CREATE", "CROSS", "CURRENT_DATE",
150 "CURRENT_TIME", "CURRENT_TIMESTAMP", "DATABASE", "DEFAULT", "DEFERRABLE",
151 "DEFERRED", "DELETE", "DESC", "DETACH", "DISTINCT", "DROP", "EACH",
152 "ELSE", "END", "ESCAPE", "EXCEPT", "EXCLUSIVE", "EXISTS", "EXPLAIN",
153 "FAIL", "FOR", "FOREIGN", "FROM", "FULL", "GLOB", "GROUP", "HAVING", "IF",
154 "IGNORE", "IMMEDIATE", "IN", "INDEX", "INDEXED", "INITIALLY", "INNER",
155 "INSERT", "INSTEAD", "INTERSECT", "INTO", "IS", "ISNULL", "JOIN", "KEY",
156 "LEFT", "LIKE", "LIMIT", "MATCH", "NATURAL", "NO", "NOT", "NOTNULL",
157 "NULL", "OF", "OFFSET", "ON", "OR", "ORDER", "OUTER", "PLAN", "PRAGMA",
158 "PRIMARY", "QUERY", "RAISE", "RECURSIVE", "REFERENCES", "REGEXP",
159 "REINDEX", "RELEASE", "RENAME", "REPLACE", "RESTRICT", "RIGHT",
160 "ROLLBACK", "ROW", "SAVEPOINT", "SELECT", "SET", "TABLE", "TEMP",
161 "TEMPORARY", "THEN", "TO", "TRANSACTION", "TRIGGER", "UNION", "UNIQUE",
162 "UPDATE", "USING", "VACUUM", "VALUES", "VIEW", "VIRTUAL", "WHEN", "WHERE",
165 int i
, lwr
, upr
, mid
, c
;
166 if( !isalpha((unsigned char)zName
[0]) && zName
[0]!='_' ) return '"';
167 for(i
=0; zName
[i
]; i
++){
168 if( !isalnum((unsigned char)zName
[i
]) && zName
[i
]!='_' ) return '"';
171 upr
= sizeof(azKeywords
)/sizeof(azKeywords
[0]) - 1;
174 c
= sqlite3_stricmp(azKeywords
[mid
], zName
);
175 if( c
==0 ) return '"';
187 ** Release memory previously allocated by tableColumnList().
189 static void freeColumnList(char **azCol
){
191 for(i
=1; azCol
[i
]; i
++){
192 sqlite3_free(azCol
[i
]);
194 /* azCol[0] is a static string */
199 ** Return a list of pointers to strings which are the names of all
200 ** columns in table zTab. The memory to hold the names is dynamically
201 ** allocated and must be released by the caller using a subsequent call
202 ** to freeColumnList().
204 ** The azCol[0] entry is usually NULL. However, if zTab contains a rowid
205 ** value that needs to be preserved, then azCol[0] is filled in with the
206 ** name of the rowid column.
208 ** The first regular column in the table is azCol[1]. The list is terminated
209 ** by an entry with azCol[i]==0.
211 static char **tableColumnList(DState
*p
, const char *zTab
){
213 sqlite3_stmt
*pStmt
= 0;
217 int nPK
= 0; /* Number of PRIMARY KEY columns seen */
218 int isIPK
= 0; /* True if one PRIMARY KEY column of type INTEGER */
219 int preserveRowid
= 1;
222 zSql
= sqlite3_mprintf("PRAGMA table_info=%Q", zTab
);
223 if( zSql
==0 ) return 0;
224 rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
227 while( sqlite3_step(pStmt
)==SQLITE_ROW
){
228 if( nCol
>=nAlloc
-2 ){
230 nAlloc
= nAlloc
*2 + nCol
+ 10;
231 azNew
= sqlite3_realloc(azCol
, nAlloc
*sizeof(azCol
[0]));
232 if( azNew
==0 ) goto col_oom
;
236 azCol
[++nCol
] = sqlite3_mprintf("%s", sqlite3_column_text(pStmt
, 1));
237 if( azCol
[nCol
]==0 ) goto col_oom
;
238 if( sqlite3_column_int(pStmt
, 5) ){
241 && sqlite3_stricmp((const char*)sqlite3_column_text(pStmt
,2),
250 sqlite3_finalize(pStmt
);
254 /* The decision of whether or not a rowid really needs to be preserved
255 ** is tricky. We never need to preserve a rowid for a WITHOUT ROWID table
256 ** or a table with an INTEGER PRIMARY KEY. We are unable to preserve
257 ** rowids on tables where the rowid is inaccessible because there are other
258 ** columns in the table named "rowid", "_rowid_", and "oid".
261 /* If a single PRIMARY KEY column with type INTEGER was seen, then it
262 ** might be an alise for the ROWID. But it might also be a WITHOUT ROWID
263 ** table or a INTEGER PRIMARY KEY DESC column, neither of which are
264 ** ROWID aliases. To distinguish these cases, check to see if
265 ** there is a "pk" entry in "PRAGMA index_list". There will be
266 ** no "pk" index if the PRIMARY KEY really is an alias for the ROWID.
268 zSql
= sqlite3_mprintf("SELECT 1 FROM pragma_index_list(%Q)"
269 " WHERE origin='pk'", zTab
);
270 if( zSql
==0 ) goto col_oom
;
271 rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pStmt
, 0);
274 freeColumnList(azCol
);
277 rc
= sqlite3_step(pStmt
);
278 sqlite3_finalize(pStmt
);
280 preserveRowid
= rc
==SQLITE_ROW
;
283 /* Only preserve the rowid if we can find a name to use for the
285 static char *azRowid
[] = { "rowid", "_rowid_", "oid" };
288 for(i
=1; i
<=nCol
; i
++){
289 if( sqlite3_stricmp(azRowid
[j
],azCol
[i
])==0 ) break;
292 /* At this point, we know that azRowid[j] is not the name of any
293 ** ordinary column in the table. Verify that azRowid[j] is a valid
294 ** name for the rowid before adding it to azCol[0]. WITHOUT ROWID
295 ** tables will fail this last check */
297 rc
= sqlite3_table_column_metadata(p
->db
,0,zTab
,azRowid
[j
],0,0,0,0,0);
298 if( rc
==SQLITE_OK
) azCol
[0] = azRowid
[j
];
306 sqlite3_finalize(pStmt
);
307 freeColumnList(azCol
);
309 p
->rc
= SQLITE_NOMEM
;
314 ** Send mprintf-formatted content to the output callback.
316 static void output_formatted(DState
*p
, const char *zFormat
, ...){
319 va_start(ap
, zFormat
);
320 z
= sqlite3_vmprintf(zFormat
, ap
);
322 p
->xCallback(z
, p
->pArg
);
327 ** Find a string that is not found anywhere in z[]. Return a pointer
330 ** Try to use zA and zB first. If both of those are already found in z[]
331 ** then make up some string and store it in the buffer zBuf.
333 static const char *unused_string(
334 const char *z
, /* Result must not appear anywhere in z */
335 const char *zA
, const char *zB
, /* Try these first */
336 char *zBuf
/* Space to store a generated string */
339 if( strstr(z
, zA
)==0 ) return zA
;
340 if( strstr(z
, zB
)==0 ) return zB
;
342 sqlite3_snprintf(20,zBuf
,"(%s%u)", zA
, i
++);
343 }while( strstr(z
,zBuf
)!=0 );
348 ** Output the given string as a quoted string using SQL quoting conventions.
349 ** Additionallly , escape the "\n" and "\r" characters so that they do not
350 ** get corrupted by end-of-line translation facilities in some operating
353 static void output_quoted_escaped_string(DState
*p
, const char *z
){
356 for(i
=0; (c
= z
[i
])!=0 && c
!='\'' && c
!='\n' && c
!='\r'; i
++){}
358 output_formatted(p
,"'%s'",z
);
364 char zBuf1
[20], zBuf2
[20];
366 if( z
[i
]=='\n' ) nNL
++;
367 if( z
[i
]=='\r' ) nCR
++;
370 p
->xCallback("replace(", p
->pArg
);
371 zNL
= unused_string(z
, "\\n", "\\012", zBuf1
);
374 p
->xCallback("replace(", p
->pArg
);
375 zCR
= unused_string(z
, "\\r", "\\015", zBuf2
);
377 p
->xCallback("'", p
->pArg
);
379 for(i
=0; (c
= z
[i
])!=0 && c
!='\n' && c
!='\r' && c
!='\''; i
++){}
382 output_formatted(p
, "%.*s", i
, z
);
386 p
->xCallback("'", p
->pArg
);
394 p
->xCallback(zNL
, p
->pArg
);
397 p
->xCallback(zCR
, p
->pArg
);
399 p
->xCallback("'", p
->pArg
);
401 output_formatted(p
, ",'%s',char(13))", zCR
);
404 output_formatted(p
, ",'%s',char(10))", zNL
);
410 ** This is an sqlite3_exec callback routine used for dumping the database.
411 ** Each row received by this callback consists of a table name,
412 ** the table type ("index" or "table") and SQL to create the table.
413 ** This routine should print text sufficient to recreate the table.
415 static int dump_callback(void *pArg
, int nArg
, char **azArg
, char **azCol
){
420 DState
*p
= (DState
*)pArg
;
424 if( nArg
!=3 ) return 1;
429 if( strcmp(zTable
, "sqlite_sequence")==0 ){
430 p
->xCallback("DELETE FROM sqlite_sequence;\n", p
->pArg
);
431 }else if( sqlite3_strglob("sqlite_stat?", zTable
)==0 ){
432 p
->xCallback("ANALYZE sqlite_master;\n", p
->pArg
);
433 }else if( strncmp(zTable
, "sqlite_", 7)==0 ){
435 }else if( strncmp(zSql
, "CREATE VIRTUAL TABLE", 20)==0 ){
436 if( !p
->writableSchema
){
437 p
->xCallback("PRAGMA writable_schema=ON;\n", p
->pArg
);
438 p
->writableSchema
= 1;
441 "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
442 "VALUES('table','%q','%q',0,'%q');",
443 zTable
, zTable
, zSql
);
446 if( sqlite3_strglob("CREATE TABLE ['\"]*", zSql
)==0 ){
447 p
->xCallback("CREATE TABLE IF NOT EXISTS ", p
->pArg
);
448 p
->xCallback(zSql
+13, p
->pArg
);
450 p
->xCallback(zSql
, p
->pArg
);
452 p
->xCallback(";\n", p
->pArg
);
455 if( strcmp(zType
, "table")==0 ){
462 azCol
= tableColumnList(p
, zTable
);
463 if( azCol
==0 ) return 0;
466 appendText(&sTable
, "INSERT INTO ", 0);
468 /* Always quote the table name, even if it appears to be pure ascii,
469 ** in case it is a keyword. Ex: INSERT INTO "table" ... */
470 appendText(&sTable
, zTable
, quoteChar(zTable
));
472 /* If preserving the rowid, add a column list after the table name.
473 ** In other words: "INSERT INTO tab(rowid,a,b,c,...) VALUES(...)"
474 ** instead of the usual "INSERT INTO tab VALUES(...)".
477 appendText(&sTable
, "(", 0);
478 appendText(&sTable
, azCol
[0], 0);
479 for(i
=1; azCol
[i
]; i
++){
480 appendText(&sTable
, ",", 0);
481 appendText(&sTable
, azCol
[i
], quoteChar(azCol
[i
]));
483 appendText(&sTable
, ")", 0);
485 appendText(&sTable
, " VALUES(", 0);
487 /* Build an appropriate SELECT statement */
489 appendText(&sSelect
, "SELECT ", 0);
491 appendText(&sSelect
, azCol
[0], 0);
492 appendText(&sSelect
, ",", 0);
494 for(i
=1; azCol
[i
]; i
++){
495 appendText(&sSelect
, azCol
[i
], quoteChar(azCol
[i
]));
497 appendText(&sSelect
, ",", 0);
501 if( azCol
[0]==0 ) nCol
--;
502 freeColumnList(azCol
);
503 appendText(&sSelect
, " FROM ", 0);
504 appendText(&sSelect
, zTable
, quoteChar(zTable
));
506 rc
= sqlite3_prepare_v2(p
->db
, sSelect
.z
, -1, &pStmt
, 0);
509 if( p
->rc
==SQLITE_OK
) p
->rc
= rc
;
511 while( SQLITE_ROW
==sqlite3_step(pStmt
) ){
512 p
->xCallback(sTable
.z
, p
->pArg
);
513 for(i
=0; i
<nCol
; i
++){
514 if( i
) p
->xCallback(",", p
->pArg
);
515 switch( sqlite3_column_type(pStmt
,i
) ){
516 case SQLITE_INTEGER
: {
517 output_formatted(p
, "%lld", sqlite3_column_int64(pStmt
,i
));
521 double r
= sqlite3_column_double(pStmt
,i
);
522 output_formatted(p
, "%!.20g", r
);
526 p
->xCallback("NULL", p
->pArg
);
530 output_quoted_escaped_string(p
,
531 (const char*)sqlite3_column_text(pStmt
,i
));
535 int nByte
= sqlite3_column_bytes(pStmt
,i
);
536 unsigned char *a
= (unsigned char*)sqlite3_column_blob(pStmt
,i
);
538 p
->xCallback("x'", p
->pArg
);
539 for(j
=0; j
<nByte
; j
++){
541 zWord
[0] = "0123456789abcdef"[(a
[j
]>>4)&15];
542 zWord
[1] = "0123456789abcdef"[a
[j
]&15];
544 p
->xCallback(zWord
, p
->pArg
);
546 p
->xCallback("'", p
->pArg
);
551 p
->xCallback(");\n", p
->pArg
);
554 sqlite3_finalize(pStmt
);
563 ** Execute a query statement that will generate SQL output. Print
564 ** the result columns, comma-separated, on a line and then add a
565 ** semicolon terminator to the end of that line.
567 ** If the number of columns is 1 and that column contains text "--"
568 ** then write the semicolon on a separate line. That way, if a
569 ** "--" comment occurs at the end of the statement, the comment
570 ** won't consume the semicolon terminator.
572 static void output_sql_from_query(
573 DState
*p
, /* Query context */
574 const char *zSelect
, /* SELECT statement to extract content */
577 sqlite3_stmt
*pSelect
;
584 va_start(ap
, zSelect
);
585 zSql
= sqlite3_vmprintf(zSelect
, ap
);
588 p
->rc
= SQLITE_NOMEM
;
592 rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pSelect
, 0);
594 if( rc
!=SQLITE_OK
|| !pSelect
){
595 output_formatted(p
, "/**** ERROR: (%d) %s *****/\n", rc
,
596 sqlite3_errmsg(p
->db
));
600 rc
= sqlite3_step(pSelect
);
601 nResult
= sqlite3_column_count(pSelect
);
602 while( rc
==SQLITE_ROW
){
603 z
= (const char*)sqlite3_column_text(pSelect
, 0);
604 p
->xCallback(z
, p
->pArg
);
605 for(i
=1; i
<nResult
; i
++){
606 p
->xCallback(",", p
->pArg
);
607 p
->xCallback((const char*)sqlite3_column_text(pSelect
,i
), p
->pArg
);
610 while( z
[0] && (z
[0]!='-' || z
[1]!='-') ) z
++;
612 p
->xCallback("\n;\n", p
->pArg
);
614 p
->xCallback(";\n", p
->pArg
);
616 rc
= sqlite3_step(pSelect
);
618 rc
= sqlite3_finalize(pSelect
);
620 output_formatted(p
, "/**** ERROR: (%d) %s *****/\n", rc
,
621 sqlite3_errmsg(p
->db
));
622 if( (rc
&0xff)!=SQLITE_CORRUPT
) p
->nErr
++;
627 ** Run zQuery. Use dump_callback() as the callback routine so that
628 ** the contents of the query are output as SQL statements.
630 ** If we get a SQLITE_CORRUPT error, rerun the query after appending
631 ** "ORDER BY rowid DESC" to the end.
633 static void run_schema_dump_query(
641 va_start(ap
, zQuery
);
642 z
= sqlite3_vmprintf(zQuery
, ap
);
644 sqlite3_exec(p
->db
, z
, dump_callback
, p
, &zErr
);
647 output_formatted(p
, "/****** %s ******/\n", zErr
);
655 ** Convert an SQLite database into SQL statements that will recreate that
659 sqlite3
*db
, /* The database connection */
660 const char *zSchema
, /* Which schema to dump. Usually "main". */
661 const char *zTable
, /* Which table to dump. NULL means everything. */
662 int (*xCallback
)(const char*,void*), /* Output sent to this callback */
663 void *pArg
/* Second argument of the callback */
666 memset(&x
, 0, sizeof(x
));
667 x
.rc
= sqlite3_exec(db
, "BEGIN", 0, 0, 0);
668 if( x
.rc
) return x
.rc
;
670 x
.xCallback
= xCallback
;
672 xCallback("PRAGMA foreign_keys=OFF;\nBEGIN TRANSACTION;\n", pArg
);
674 run_schema_dump_query(&x
,
675 "SELECT name, type, sql FROM \"%w\".sqlite_master "
676 "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'",
679 run_schema_dump_query(&x
,
680 "SELECT name, type, sql FROM \"%w\".sqlite_master "
681 "WHERE name=='sqlite_sequence'", zSchema
683 output_sql_from_query(&x
,
684 "SELECT sql FROM sqlite_master "
685 "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0
688 run_schema_dump_query(&x
,
689 "SELECT name, type, sql FROM \"%w\".sqlite_master "
690 "WHERE tbl_name=%Q COLLATE nocase AND type=='table'"
694 output_sql_from_query(&x
,
695 "SELECT sql FROM \"%w\".sqlite_master "
697 " AND type IN ('index','trigger','view')"
698 " AND tbl_name=%Q COLLATE nocase",
702 if( x
.writableSchema
){
703 xCallback("PRAGMA writable_schema=OFF;\n", pArg
);
705 xCallback(x
.nErr
? "ROLLBACK; -- due to errors\n" : "COMMIT;\n", pArg
);
706 sqlite3_exec(db
, "COMMIT", 0, 0, 0);
712 /* The generic subroutine is above. The code the follows implements
713 ** the command-line interface.
715 #ifdef DBDUMP_STANDALONE
719 ** Command-line interface
721 int main(int argc
, char **argv
){
725 const char *zTable
= 0;
728 if( argc
<2 || argc
>4 ){
729 fprintf(stderr
, "Usage: %s DATABASE ?SCHEMA? ?TABLE?\n", argv
[0]);
733 zSchema
= argc
>=3 ? argv
[2] : "main";
734 zTable
= argc
==4 ? argv
[3] : 0;
736 rc
= sqlite3_open(zDb
, &db
);
738 fprintf(stderr
, "Cannot open \"%s\": %s\n", zDb
, sqlite3_errmsg(db
));
742 rc
= sqlite3_db_dump(db
, zSchema
, zTable
,
743 (int(*)(const char*,void*))fputs
, (void*)stdout
);
745 fprintf(stderr
, "Error: sqlite3_db_dump() returns %d\n", rc
);
748 return rc
!=SQLITE_OK
;
750 #endif /* DBDUMP_STANDALONE */