SQLite extension module for importing/exporting database information from/to SQL source text and export to CSV text. More...
Go to the source code of this file.
Typedefs | |
typedef void(* | impexp_putc) (int c, void *arg) |
The function pointer for the output function to "impexp_export_json" has a signature compatible with fputc(3). | |
Functions | |
int | impexp_import_sql (sqlite3 *db, char *filename) |
Reads SQL commands from filename and executes them against the current database. | |
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 indent, char *root, char *item, char *tablename, char *schema) |
Writes a table as simple XML to provided filename. | |
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 | impexp_init (sqlite3 *db) |
Registers the SQLite functions. | |
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.
Definition in file impexp.h.
typedef void(* impexp_putc) (int c, void *arg) |
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().