sqlite3(1)'s .dump turned into a library
Ivan Shmakov
Submitted-by: onei...@gmail.com
Last-modified: 2011-08-28 +00:00
Copyright-Notice: both the README and the code are in the public domain
README.sqltedmp-is -*- Text -*-
This is a collection of bits extracted out of the SQLite's Shell
(shell.c, sqlite-autoconf-3070701.tar.gz.)
Usage is like:
sqlite3_dump_schema (database, 1, stderr);
sqlite3_dump_table (database, "mytable", 1, stderr);
This facility is intended as a debugging aid.
There's one known issue with the code: it only examines the
sqlite_master table, so the temporary tables (defined in
sqlite_temp_master) aren't shown in the dump. While it's likely
to be reasonable for the sqlite3(1) shell, it somewhat lowers
the value of the code as a debugging aid.
README.sqltedmp-is ends here
/*** sqltedmp.h --- Dump an Sqlite3 database -*- C -*- */
/*** Ivan Shmakov, 2011 */
/** This code is in the public domain. */
/*** Code: */
#ifndef SQLTEDMP_H
#define SQLTEDMP_H
#include <stdio.h> /* for FILE */
#include <sqlite3.h> /* for sqlite3 */
int sqlite3_dump_schema (sqlite3 *db,
int wrap_p, FILE *out);
int sqlite3_dump_table (sqlite3 *db, const char *table,
int wrap_p, FILE *out);
#endif
/*** Emacs stuff */
/** Local variables: */
/** coding: us-ascii */
/** mode: outline-minor */
/** fill-column: 72 */
/** indent-tabs-mode: nil */
/** outline-regexp: "[*][*][*]" */
/** End: */
/** LocalWords: */
/*** sqltedmp.h ends here */
/*** sqltedmp.c --- Dump an Sqlite3 database -*- C -*- */
/*** Parts of shell.c (sqlite-autoconf-3070701.tar.gz) */
/** This code is in the public domain. */
/*** Code: */
#define _GNU_SOURCE
#include <assert.h>
#include <stdarg.h>
#include <stdio.h>
#include <stdlib.h> /* for malloc (), free () */
#include <string.h> /* for strcmp (), strncmp () */
#include <sqlite3.h>
#include <sqltedmp.h>
#define UNUSED_PARAMETER(anything)
struct callback_data {
sqlite3 *db;
FILE *out;
};
static void
header (FILE *out)
{
int r
= fputs (("PRAGMA foreign_keys=OFF;\n"
"BEGIN TRANSACTION;\n"),
out);
assert (r >= 0);
/* . */
}
static void
trailer (FILE *out)
{
int r
= fputs (("COMMIT;\n"),
out);
assert (r >= 0);
/* . */
}
/* piece of shell.c */
static const char *zShellStatic = 0;
static void
shellstaticFunc (
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
assert( 0==argc );
assert( zShellStatic );
UNUSED_PARAMETER(argc);
UNUSED_PARAMETER(argv);
sqlite3_result_text(context, zShellStatic, -1, SQLITE_STATIC);
}
static int strlen30(const char *z){
const char *z2 = z;
while( *z2 ){ z2++; }
return 0x3fffffff & (int)(z2 - z);
}
static char *appendText(char *zIn, char const *zAppend, char quote){
int len;
int i;
int nAppend = strlen30(zAppend);
int nIn = (zIn?strlen30(zIn):0);
len = nAppend+nIn+1;
if( quote ){
len += 2;
for(i=0; i<nAppend; i++){
if( zAppend[i]==quote ) len++;
}
}
zIn = (char *)realloc(zIn, len);
if( !zIn ){
return 0;
}
if( quote ){
char *zCsr = &zIn[nIn];
*zCsr++ = quote;
for(i=0; i<nAppend; i++){
*zCsr++ = zAppend[i];
if( zAppend[i]==quote ) *zCsr++ = quote;
}
*zCsr++ = quote;
*zCsr++ = '\0';
assert( (zCsr-zIn)==len );
}else{
memcpy(&zIn[nIn], zAppend, nAppend);
zIn[len-1] = '\0';
}
return zIn;
}
static int run_table_dump_query(
FILE *out, /* Send output here */
sqlite3 *db, /* Database to query */
const char *zSelect, /* SELECT statement to extract content */
const char *zFirstRow /* Print before first row, if not NULL */
){
sqlite3_stmt *pSelect;
int rc;
rc = sqlite3_prepare(db, zSelect, -1, &pSelect, 0);
if( rc!=SQLITE_OK || !pSelect ){
return rc;
}
rc = sqlite3_step(pSelect);
while( rc==SQLITE_ROW ){
if( zFirstRow ){
fprintf(out, "%s", zFirstRow);
zFirstRow = 0;
}
fprintf(out, "%s;\n", sqlite3_column_text(pSelect, 0));
rc = sqlite3_step(pSelect);
}
return sqlite3_finalize(pSelect);
}
static int dump_callback(void *pArg, int nArg, char **azArg, char **azCol){
int rc;
const char *zTable;
const char *zType;
const char *zSql;
const char *zPrepStmt = 0;
struct callback_data *p = (struct callback_data *)pArg;
UNUSED_PARAMETER(azCol);
if( nArg!=3 ) return 1;
zTable = azArg[0];
zType = azArg[1];
zSql = azArg[2];
if( strcmp(zTable, "sqlite_sequence")==0 ){
zPrepStmt = "DELETE FROM sqlite_sequence;\n";
}else if( strcmp(zTable, "sqlite_stat1")==0 ){
fprintf(p->out, "ANALYZE sqlite_master;\n");
}else if( strncmp(zTable, "sqlite_", 7)==0 ){
return 0;
}else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){
char *zIns;
#if 0
/* NB: we have no ->writableSchema */
if( !p->writableSchema ){
fprintf(p->out, "PRAGMA writable_schema=ON;\n");
p->writableSchema = 1;
}
#endif
zIns = sqlite3_mprintf(
"INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
"VALUES('table','%q','%q',0,'%q');",
zTable, zTable, zSql);
fprintf(p->out, "%s\n", zIns);
sqlite3_free(zIns);
return 0;
}else{
fprintf(p->out, "%s;\n", zSql);
}
if( strcmp(zType, "table")==0 ){
sqlite3_stmt *pTableInfo = 0;
char *zSelect = 0;
char *zTableInfo = 0;
char *zTmp = 0;
int nRow = 0;
zTableInfo = appendText(zTableInfo, "PRAGMA table_info(", 0);
zTableInfo = appendText(zTableInfo, zTable, '"');
zTableInfo = appendText(zTableInfo, ");", 0);
rc = sqlite3_prepare(p->db, zTableInfo, -1, &pTableInfo, 0);
free(zTableInfo);
if( rc!=SQLITE_OK || !pTableInfo ){
return 1;
}
zSelect = appendText(zSelect, "SELECT 'INSERT INTO ' || ", 0);
zTmp = appendText(zTmp, zTable, '"');
if( zTmp ){
zSelect = appendText(zSelect, zTmp, '\'');
}
zSelect = appendText(zSelect, " || ' VALUES(' || ", 0);
rc = sqlite3_step(pTableInfo);
while( rc==SQLITE_ROW ){
const char *zText = (const char *)sqlite3_column_text(pTableInfo, 1);
zSelect = appendText(zSelect, "quote(", 0);
zSelect = appendText(zSelect, zText, '"');
rc = sqlite3_step(pTableInfo);
if( rc==SQLITE_ROW ){
zSelect = appendText(zSelect, ") || ',' || ", 0);
}else{
zSelect = appendText(zSelect, ") ", 0);
}
nRow++;
}
rc = sqlite3_finalize(pTableInfo);
if( rc!=SQLITE_OK || nRow==0 ){
free(zSelect);
return 1;
}
zSelect = appendText(zSelect, "|| ')' FROM ", 0);
zSelect = appendText(zSelect, zTable, '"');
rc = run_table_dump_query(p->out, p->db, zSelect, zPrepStmt);
if( rc==SQLITE_CORRUPT ){
zSelect = appendText(zSelect, " ORDER BY rowid DESC", 0);
rc = run_table_dump_query(p->out, p->db, zSelect, 0);
}
if( zSelect ) free(zSelect);
}
return 0;
}
static int run_schema_dump_query(
struct callback_data *p,
const char *zQuery,
char **pzErrMsg
){
int rc;
rc = sqlite3_exec(p->db, zQuery, dump_callback, p, pzErrMsg);
if( rc==SQLITE_CORRUPT ){
char *zQ2;
int len = strlen30(zQuery);
if( pzErrMsg ) sqlite3_free(*pzErrMsg);
zQ2 = malloc( len+100 );
if( zQ2==0 ) return rc;
sqlite3_snprintf(sizeof(zQ2), zQ2, "%s ORDER BY rowid DESC", zQuery);
rc = sqlite3_exec(p->db, zQ2, dump_callback, p, pzErrMsg);
free(zQ2);
}
return rc;
}
/* piece of shell.c ends here */
int
sqlite3_dump_schema (sqlite3 *db,
int wrap_p, FILE *out)
{
struct callback_data d = {
.db = db,
.out = out
};
struct callback_data *p
= &d;
if (wrap_p) {
header (out);
}
/* piece of shell.c */
sqlite3_create_function(db, "shellstatic", 0, SQLITE_UTF8, 0,
shellstaticFunc, 0, 0);
run_schema_dump_query(p,
"SELECT name, type, sql FROM sqlite_master "
"WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'", 0
);
run_schema_dump_query(p,
"SELECT name, type, sql FROM sqlite_master "
"WHERE name=='sqlite_sequence'", 0
);
run_table_dump_query(p->out, p->db,
"SELECT sql FROM sqlite_master "
"WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0
);
/* piece of shell.c ends here */
if (wrap_p) {
trailer (out);
}
/* . */
return 0;
}
int
sqlite3_dump_table (sqlite3 *db, const char *table,
int wrap_p, FILE *out)
{
struct callback_data d = {
.db = db,
.out = out
};
struct callback_data *p
= &d;
if (wrap_p) {
header (out);
}
/* piece of shell.c */
sqlite3_create_function(db, "shellstatic", 0, SQLITE_UTF8, 0,
shellstaticFunc, 0, 0);
zShellStatic = table;
run_schema_dump_query(p,
"SELECT name, type, sql FROM sqlite_master "
"WHERE tbl_name LIKE shellstatic() AND type=='table'"
" AND sql NOT NULL", 0);
run_table_dump_query(p->out, p->db,
"SELECT sql FROM sqlite_master "
"WHERE sql NOT NULL"
" AND type IN ('index','trigger','view')"
" AND tbl_name LIKE shellstatic()", 0
);
zShellStatic = 0;
/* piece of shell.c ends here */
if (wrap_p) {
trailer (out);
}
/* . */
return 0;
}
/*** Emacs trailer */
/** Local variables: */
/** coding: us-ascii */
/** mode: outline-minor */
/** fill-column: 72 */
/** indent-tabs-mode: nil */
/** outline-regexp: "[*][*][*]" */
/** End: */
/*** sqltedmp.c ends here */
--
FSF associate member #7257 Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)