SQLite extension module for importing/exporting database information from/to SQL source text and export to CSV text. More...
#include <sqlite3ext.h>
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <stddef.h>
#include <unistd.h>
#include "impexp.h"
Go to the source code of this file.
Data Structures | |
struct | json_pfs |
JSON output helper structure. More... | |
struct | DUMP_DATA |
Structure for dump callback. More... | |
Macros | |
#define | ISSPACE(c) |
Functions | |
static char * | one_input_line (FILE *fin) |
Read one line of input into dynamically allocated buffer which the caller must free with sqlite3_free() | |
static int | ends_with_semicolon (const char *str, int n) |
Test if string ends with a semicolon. | |
static int | all_whitespace (const char *str) |
Test if string contains entirely whitespace or SQL comment. | |
static int | process_input (sqlite3 *db, FILE *fin) |
Process contents of FILE pointer as SQL commands. | |
static void | quote_func (sqlite3_context *context, int argc, sqlite3_value **argv) |
SQLite function to quote SQLite value depending on optional quote mode. | |
static void | quote_csv_func (sqlite3_context *context, int argc, sqlite3_value **argv) |
SQLite function to quote an SQLite value in CSV format. | |
static void | indent_xml_func (sqlite3_context *context, int argc, sqlite3_value **argv) |
SQLite function to make XML indentation. | |
static void | quote_xml_func (sqlite3_context *context, int argc, sqlite3_value **argv) |
SQLite function to quote a string for XML. | |
static void | import_func (sqlite3_context *ctx, int nargs, sqlite3_value **args) |
SQLite function to read and process SQL commands from a file. | |
int | impexp_import_sql (sqlite3 *db, char *filename) |
Reads SQL commands from filename and executes them against the current database. | |
static void | indent (DUMP_DATA *dd) |
Write indentation to dump. | |
static int | table_dump (DUMP_DATA *dd, char **errp, int fmt, const char *query,...) |
Execute SQL to dump contents of one table. | |
static void | append_free (char **in) |
Free dynamically allocated string buffer. | |
static char * | append (char **in, char const *append, char quote) |
Append a string to dynamically allocated string buffer with optional quoting. | |
static void | quote_xml_str (DUMP_DATA *dd, char *str) |
Quote string for XML output during dump. | |
static int | dump_cb (void *udata, int nargs, char **args, char **cols) |
Callback for sqlite3_exec() to dump one data row. | |
static int | schema_dump (DUMP_DATA *dd, char **errp, const char *query,...) |
Execute SQL on sqlite_master table in order to dump data. | |
static void | export_func (sqlite3_context *ctx, int nargs, sqlite3_value **args) |
SQLite function for SQL output, see impexp_export_sql. | |
static void | export_csv_func (sqlite3_context *ctx, int nargs, sqlite3_value **args) |
SQLite function for CSV output, see impexp_export_csv. | |
static void | export_xml_func (sqlite3_context *ctx, int nargs, sqlite3_value **args) |
SQLite function for XML output, see impexp_export_xml. | |
int | impexp_export_sql (sqlite3 *db, char *filename, int mode,...) |
Writes SQL to filename similar to SQLite's shell ".dump" meta command. | |
int | impexp_export_csv (sqlite3 *db, char *filename, int hdr,...) |
Writes entire tables as CSV to provided filename. | |
int | impexp_export_xml (sqlite3 *db, char *filename, int append, int indnt, char *root, char *item, char *tablename, char *schema) |
Writes a table as simple XML to provided filename. | |
static void | json_pstr (const char *string, json_pfs *pfs) |
Write string using JSON output function. | |
static void | json_pstrq (const char *string, json_pfs *pfs) |
Quote and write string using JSON output function. | |
static void | json_pstrc (const char *string, json_pfs *pfs) |
Conditionally quote and write string using JSON output function. | |
static void | json_pb64 (const unsigned char *blk, int len, json_pfs *pfs) |
Write a blob as base64 string using JSON output function. | |
static int | json_output (sqlite3 *db, char *sql, impexp_putc pfunc, void *parg) |
Execute SQL and write output as JSON. | |
static void | export_json_func (sqlite3_context *ctx, int nargs, sqlite3_value **args) |
SQLite function for JSON output, see impexp_export_json. | |
int | impexp_export_json (sqlite3 *db, char *sql, impexp_putc pfunc, void *parg) |
Executes arbitrary SQL statements and formats the result in JavaScript Object Notation (JSON). | |
int | sqlite3_extension_init (sqlite3 *db, char **errmsg, const sqlite3_api_routines *api) |
Initializer for SQLite extension load mechanism. | |
int | impexp_init (sqlite3 *db) |
Registers the SQLite functions. | |
Variables | |
static const char | space_chars [] = " \f\n\r\t\v" |
SQLite extension module for importing/exporting database information from/to SQL source text and export to CSV text.
2007 January 27
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.
Usage: SQLite function: SELECT import_sql(filename); C function: int impexp_import_sql(sqlite3 *db, char *filename); Reads SQL commands from filename and executes them against the current database. Returns the number of changes to the current database. SQLite function: SELECT export_sql(filename, [mode, tablename, ...]); C function: int impexp_export_sql(sqlite3 *db, char *filename, int mode, ...); Writes SQL to filename similar to SQLite's shell ".dump" meta command. Mode selects the output format: Mode 0 (default): dump schema and data using the optional table names following the mode argument. Mode 1: dump data only using the optional table names following the mode argument. Mode 2: dump schema and data using the optional table names following the mode argument; each table name is followed by a WHERE clause, i.e. "mode, table1, where1, table2, where2, ..." Mode 3: dump data only, same rules as in mode 2. Returns approximate number of lines written or -1 when an error occurred. Bit 1 of mode: when 1 dump data only Bits 8..9 of mode: blob quoting mode 0 default 256 ORACLE 512 SQL Server 768 MySQL SQLite function: SELECT export_csv(filename, hdr, prefix1, tablename1, schema1, ...]); C function: int impexp_export_csv(sqlite3 *db, char *filename, int hdr, ...); [char *prefix1, char *tablename1, char *schema1, ...] Writes entire tables as CSV to provided filename. A header row is written when the hdr parameter is true. The rows are optionally introduced with a column made up of the prefix (non-empty string) for the respective table. If "schema" is NULL, "sqlite_master" is used, otherwise specify e.g. "sqlite_temp_master" for temporary tables or "att.sqlite_master" for the attached database "att". CREATE TABLE A(a,b); INSERT INTO A VALUES(1,2); INSERT INTO A VALUES(3,'foo'); CREATE TABLE B(c); INSERT INTO B VALUES('hello'); SELECT export_csv('out.csv', 0, 'aa', 'A', NULL, 'bb', 'B', NULL); -- CSV output "aa",1,2 "aa",3,"foo" "bb","hello" SELECT export_csv('out.csv', 1, 'aa', 'A', NULL, 'bb', 'B', NULL); -- CSV output "aa","a","b" "aa",1,2 "aa",3,"foo" "bb","c" "bb","hello" SQLite function: SELECT export_xml(filename, appendflag, indent, [root, item, tablename, schema]+); C function: int impexp_export_xml(sqlite3 *db, char *filename, int append, int indent, char *root, char *item, char *tablename, char *schema); Writes a table as simple XML to provided filename. The rows are optionally enclosed with the "root" tag, the row data is enclosed in "item" tags. If "schema" is NULL, "sqlite_master" is used, otherwise specify e.g. "sqlite_temp_master" for temporary tables or "att.sqlite_master" for the attached database "att". <columnname TYPE="INTEGER|REAL|NULL|TEXT|BLOB">value</columnname> ... e.g. CREATE TABLE A(a,b); INSERT INTO A VALUES(1,2.1); INSERT INTO A VALUES(3,'foo'); INSERT INTO A VALUES('',NULL); INSERT INTO A VALUES(X'010203','<blob>'); SELECT export_xml('out.xml', 0, 2, 'TBL_A', 'ROW', 'A'); -- XML output <TBL_A> <ROW> <a TYPE="INTEGER">1</a> <b TYPE="REAL">2.1</b> </ROW> <ROW> <a TYPE="INTEGER">3</a> <b TYPE="TEXT">foo</b> </ROW> <ROW> <a TYPE="TEXT"></a> <b TYPE="NULL"></b> </ROW> <ROW> <a TYPE="BLOB">&x03;</a> <b TYPE="TEXT"><blob></b> </ROW> </TBL_A> Quoting of XML entities is performed only on the data, not on column names and root/item tags. SQLite function: SELECT export_json(filename, sql); C function: int impexp_export_json(sqlite3 *db, char *sql, impexp_putc pfunc, void *parg); Executes arbitrary SQL statements and formats the result in JavaScript Object Notation (JSON). The layout of the result is: object {results, sql} results[] object {columns, rows, changes, last_insert_rowid, error} columns[] object {name, decltype, type } (sqlite3_column_*) rows[][] (sqlite3_column_*) changes (sqlite3_changes) last_insert_rowid (sqlite3_last_insert_rowid) error (sqlite3_errmsg) sql (SQL text) For each single SQL statement in "sql" an object in the "results" array is produced. The function pointer for the output function to "impexp_export_json" has a signature compatible with fputc(3). On Win32 the filename argument may be specified as NULL in order to open a system file dialog for interactive filename selection.
Definition in file impexp.c.
#define ISSPACE | ( | c | ) |
Definition at line 219 of file impexp.c.
Referenced by all_whitespace(), ends_with_semicolon(), and process_input().
|
static |
Test if string contains entirely whitespace or SQL comment.
str | string to be tested |
Definition at line 301 of file impexp.c.
References ISSPACE.
Referenced by process_input().
|
static |
Append a string to dynamically allocated string buffer with optional quoting.
in | input string pointer |
append | string to append |
quote | quote character or NUL |
Definition at line 1034 of file impexp.c.
References append().
Referenced by append(), dump_cb(), and impexp_export_xml().
|
static |
|
static |
Callback for sqlite3_exec() to dump one data row.
udata | information structure for dump |
nargs | number of columns |
args | column data |
cols | column labels |
Definition at line 1151 of file impexp.c.
References append(), append_free(), DUMP_DATA::db, DUMP_DATA::indent, DUMP_DATA::nlines, DUMP_DATA::out, DUMP_DATA::quote_mode, table_dump(), DUMP_DATA::where, and DUMP_DATA::with_schema.
Referenced by schema_dump().
|
static |
Test if string ends with a semicolon.
str | string to be tested |
n | length of string |
Definition at line 286 of file impexp.c.
References ISSPACE.
Referenced by process_input().
|
static |
SQLite function for CSV output, see impexp_export_csv.
ctx | SQLite function context |
nargs | number of arguments |
args | argument vector |
Definition at line 1596 of file impexp.c.
References DUMP_DATA::db, DUMP_DATA::indent, DUMP_DATA::nlines, DUMP_DATA::out, DUMP_DATA::quote_mode, schema_dump(), DUMP_DATA::where, and DUMP_DATA::with_schema.
Referenced by sqlite3_extension_init().
|
static |
SQLite function for SQL output, see impexp_export_sql.
ctx | SQLite function context |
nargs | number of arguments |
args | argument vector |
Definition at line 1499 of file impexp.c.
References DUMP_DATA::db, DUMP_DATA::indent, DUMP_DATA::nlines, DUMP_DATA::out, DUMP_DATA::quote_mode, schema_dump(), table_dump(), DUMP_DATA::where, and DUMP_DATA::with_schema.
Referenced by sqlite3_extension_init().
|
static |
SQLite function for JSON output, see impexp_export_json.
ctx | SQLite function context |
nargs | number of arguments |
args | argument vector |
Definition at line 2405 of file impexp.c.
References json_output().
Referenced by sqlite3_extension_init().
|
static |
SQLite function for XML output, see impexp_export_xml.
ctx | SQLite function context |
nargs | number of arguments |
args | argument vector |
Definition at line 1690 of file impexp.c.
References DUMP_DATA::db, DUMP_DATA::indent, indent(), DUMP_DATA::nlines, DUMP_DATA::out, DUMP_DATA::quote_mode, quote_xml_str(), schema_dump(), DUMP_DATA::where, and DUMP_DATA::with_schema.
Referenced by sqlite3_extension_init().
int impexp_export_csv | ( | sqlite3 * | db, |
char * | filename, | ||
int | hdr, | ||
... ) |
Writes entire tables as CSV to provided filename.
A header row is written when the hdr parameter is true. The rows are optionally introduced with a column made up of the prefix (non-empty string) for the respective table. If "schema" is NULL, "sqlite_master" is used, otherwise specify e.g. "sqlite_temp_master" for temporary tables or "att.sqlite_master" for the attached database "att".
db | SQLite database pointer |
filename | name of output file |
hdr | write header lines when true |
... | tuples of prefix, table name, schema name |
Example:
CREATE TABLE A(a,b); INSERT INTO A VALUES(1,2); INSERT INTO A VALUES(3,'foo') CREATE TABLE B(c); INSERT INTO B VALUES('hello'); SELECT export_csv('out.csv', 0, 'aa', 'A', NULL, 'bb', 'B', NULL); -- CSV output "aa",1,2 "aa",3,"foo" "bb","hello" SELECT export_csv('out.csv', 1, 'aa', 'A', NULL, 'bb', 'B', NULL); -- CSV output "aa","a","b" "aa",1,2 "aa",3,"foo" "bb","c" "bb","hello"
Definition at line 1890 of file impexp.c.
References DUMP_DATA::db, DUMP_DATA::indent, DUMP_DATA::nlines, DUMP_DATA::out, DUMP_DATA::quote_mode, schema_dump(), DUMP_DATA::where, and DUMP_DATA::with_schema.
int impexp_export_json | ( | sqlite3 * | db, |
char * | sql, | ||
impexp_putc | pfunc, | ||
void * | parg ) |
Executes arbitrary SQL statements and formats the result in JavaScript Object Notation (JSON).
db | SQLite database pointer |
sql | SQL to be executed |
pfunc | pointer to output function |
parg | argument for output function |
The layout of the result output is:
object {results, sql} results[] object {columns, rows, changes, last_insert_rowid, error} columns[] object {name, decltype, type } (sqlite3_column_*) rows[][] (sqlite3_column_*) changes (sqlite3_changes) last_insert_rowid (sqlite3_last_insert_rowid) error (sqlite3_errmsg) sql (SQL text)
For each single SQL statement in "sql" an object in the "results" array is produced.
Definition at line 2458 of file impexp.c.
References json_output().
int impexp_export_sql | ( | sqlite3 * | db, |
char * | filename, | ||
int | mode, | ||
... ) |
Writes SQL to filename similar to SQLite's shell ".dump" meta command.
Mode selects the output format.
db | SQLite database pointer |
filename | name of output file |
mode | selects output format |
... | optional table names or tuples of table name, and where-clause depending on mode parameter |
Mode 0 (default): dump schema and data using the optional table names following the mode argument.
Mode 1: dump data only using the optional table names following the mode argument.
Mode 2: dump schema and data using the optional table names following the mode argument; each table name is followed by a WHERE clause, i.e. "mode, table1, where1, table2, where2, ..."
Mode 3: dump data only, same rules as in mode 2.
Other flags in mode:
Bit 1 of mode: when 1 dump data only Bits 8..9 of mode: blob quoting mode 0 default 256 ORACLE 512 SQL Server 768 MySQL
Definition at line 1804 of file impexp.c.
References DUMP_DATA::db, DUMP_DATA::nlines, DUMP_DATA::out, schema_dump(), table_dump(), DUMP_DATA::where, and DUMP_DATA::with_schema.
int impexp_export_xml | ( | sqlite3 * | db, |
char * | filename, | ||
int | append, | ||
int | indent, | ||
char * | root, | ||
char * | item, | ||
char * | tablename, | ||
char * | schema ) |
Writes a table as simple XML to provided filename.
The rows are optionally enclosed with the "root" tag, the row data is enclosed in "item" tags. If "schema" is NULL, "sqlite_master" is used, otherwise specify e.g. "sqlite_temp_master" for temporary tables or "att.sqlite_master" for the attached database "att".
db | SQLite database pointer |
filename | name of output file |
append | if true, append to existing output file |
indent | number of blanks to indent output |
root | optional tag use to enclose table output |
item | tag to use per row |
tablename | table to be output |
schema | optional schema or NULL |
Layout of an output row:
<columnname TYPE="INTEGER|REAL|NULL|TEXT|BLOB">value</columnname> ...
Example:
CREATE TABLE A(a,b); INSERT INTO A VALUES(1,2.1); INSERT INTO A VALUES(3,'foo'); INSERT INTO A VALUES('',NULL); INSERT INTO A VALUES(X'010203','<blob>'); SELECT export_xml('out.xml', 0, 2, 'TBL_A', 'ROW', 'A'); -- XML output <TBL_A> <ROW> <a TYPE="INTEGER">1</a> <b TYPE="REAL">2.1</b> </ROW> <ROW> <a TYPE="INTEGER">3</a> <b TYPE="TEXT">foo</b> </ROW> <ROW> <a TYPE="TEXT"></a> <b TYPE="NULL"></b> </ROW> <ROW> <a TYPE="BLOB">&x03;</a> <b TYPE="TEXT"><blob></b> </ROW> </TBL_A>
Quoting of XML entities is performed only on the data, not on column names and root/item tags.
Definition at line 1974 of file impexp.c.
References append(), DUMP_DATA::db, DUMP_DATA::indent, indent(), DUMP_DATA::nlines, DUMP_DATA::out, DUMP_DATA::quote_mode, quote_xml_str(), schema_dump(), DUMP_DATA::where, and DUMP_DATA::with_schema.
int impexp_import_sql | ( | sqlite3 * | db, |
char * | filename ) |
Reads SQL commands from filename and executes them against the current database.
Returns the number of changes to the current database.
db | SQLite database pointer |
filename | name of input file |
Definition at line 869 of file impexp.c.
References process_input().
int impexp_init | ( | sqlite3 * | db | ) |
Registers the SQLite functions.
db | SQLite database pointer |
Registered functions:
import_sql(filename) export_sql(filename, [mode, tablename, ...]) export_csv(filename, hdr, prefix1, tablename1, schema1, ...) export_xml(filename, appendflg, indent, [root, item, tablename, schema]+) export_json(filename, sql)
On Win32 the filename argument may be specified as NULL in order to open a system file dialog for interactive filename selection.
Definition at line 2521 of file impexp.c.
References sqlite3_extension_init().
|
static |
SQLite function to read and process SQL commands from a file.
ctx | SQLite function context |
nargs | number of arguments |
args | argument vector |
Definition at line 822 of file impexp.c.
References process_input().
Referenced by sqlite3_extension_init().
|
static |
Write indentation to dump.
dd | information structure for dump |
Definition at line 932 of file impexp.c.
References DUMP_DATA::indent, and DUMP_DATA::out.
Referenced by export_xml_func(), and impexp_export_xml().
|
static |
SQLite function to make XML indentation.
context | SQLite function context |
argc | number of arguments |
argv | argument vector |
Definition at line 629 of file impexp.c.
Referenced by sqlite3_extension_init().
|
static |
Execute SQL and write output as JSON.
db | SQLite database pointer |
sql | SQL text |
pfunc | JSON output function |
parg | argument for output function |
Definition at line 2261 of file impexp.c.
References json_pb64(), json_pstr(), json_pstrc(), json_pstrq(), json_pfs::parg, and json_pfs::pfunc.
Referenced by export_json_func(), and impexp_export_json().
|
static |
Write a blob as base64 string using JSON output function.
blk | pointer to blob |
len | length of blob |
pfs | JSON output function |
Definition at line 2208 of file impexp.c.
References json_pstr(), json_pfs::parg, and json_pfs::pfunc.
Referenced by json_output().
|
static |
Write string using JSON output function.
string | string to be written |
pfs | JSON output function |
Definition at line 2053 of file impexp.c.
References json_pfs::parg, and json_pfs::pfunc.
Referenced by json_output(), json_pb64(), json_pstrc(), and json_pstrq().
|
static |
Conditionally quote and write string using JSON output function.
string | string to be written |
pfs | JSON output function |
Definition at line 2191 of file impexp.c.
References json_pstr(), and json_pstrq().
Referenced by json_output().
|
static |
Quote and write string using JSON output function.
string | string to be written |
pfs | JSON output function |
Definition at line 2068 of file impexp.c.
References json_pstr(), json_pfs::parg, and json_pfs::pfunc.
Referenced by json_output(), and json_pstrc().
|
static |
Read one line of input into dynamically allocated buffer which the caller must free with sqlite3_free()
fin | FILE pointer |
Definition at line 229 of file impexp.c.
Referenced by process_input().
|
static |
Process contents of FILE pointer as SQL commands.
db | SQLite database to work on |
fin | input FILE pointer |
Definition at line 341 of file impexp.c.
References all_whitespace(), ends_with_semicolon(), ISSPACE, and one_input_line().
Referenced by impexp_import_sql(), and import_func().
|
static |
SQLite function to quote an SQLite value in CSV format.
context | SQLite function context |
argc | number of arguments |
argv | argument vector |
Definition at line 540 of file impexp.c.
References xdigits.
Referenced by sqlite3_extension_init().
|
static |
SQLite function to quote SQLite value depending on optional quote mode.
context | SQLite function context |
argc | number of arguments |
argv | argument vector |
Layout of arguments:
argv[0] - value to be quoted
argv[1] - value of quote mode (optional)
Definition at line 420 of file impexp.c.
References xdigits.
Referenced by sqlite3_extension_init().
|
static |
SQLite function to quote a string for XML.
context | SQLite function context |
argc | number of arguments |
argv | argument vector |
Definition at line 653 of file impexp.c.
References xdigits.
Referenced by sqlite3_extension_init().
|
static |
Quote string for XML output during dump.
dd | information structure for dump |
str | string to be output |
Definition at line 1104 of file impexp.c.
References DUMP_DATA::out, and xdigits.
Referenced by export_xml_func(), and impexp_export_xml().
|
static |
Execute SQL on sqlite_master table in order to dump data.
dd | information structure for dump |
errp | pointer receiving error message |
query | SQL for sqlite3_*printf() |
... | argument list |
Definition at line 1455 of file impexp.c.
References DUMP_DATA::db, and dump_cb().
Referenced by export_csv_func(), export_func(), export_xml_func(), impexp_export_csv(), impexp_export_sql(), and impexp_export_xml().
int sqlite3_extension_init | ( | sqlite3 * | db, |
char ** | errmsg, | ||
const sqlite3_api_routines * | api ) |
Initializer for SQLite extension load mechanism.
db | SQLite database pointer |
errmsg | pointer receiving error message |
api | SQLite API routines |
Definition at line 2477 of file impexp.c.
References export_csv_func(), export_func(), export_json_func(), export_xml_func(), import_func(), indent_xml_func(), quote_csv_func(), quote_func(), and quote_xml_func().
Referenced by impexp_init().
|
static |
Execute SQL to dump contents of one table.
dd | information structure for dump |
errp | pointer receiving error message |
fmt | if true, use sqlite3_*printf() on SQL |
query | SQL text to perform dump of table |
... | optional arguments |
Definition at line 952 of file impexp.c.
References DUMP_DATA::db, DUMP_DATA::nlines, DUMP_DATA::out, and DUMP_DATA::quote_mode.
Referenced by dump_cb(), export_func(), and impexp_export_sql().