impexp.h File Reference

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.
 

Detailed Description

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 Documentation

◆ impexp_putc

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).

Definition at line 177 of file impexp.h.

Function Documentation

◆ impexp_export_csv()

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".

Parameters
dbSQLite database pointer
filenamename of output file
hdrwrite header lines when true
...tuples of prefix, table name, schema name
Returns
number of output lines

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.

◆ 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).

Parameters
dbSQLite database pointer
sqlSQL to be executed
pfuncpointer to output function
pargargument for output function
Returns
SQLite error code

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().

◆ impexp_export_sql()

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.

Parameters
dbSQLite database pointer
filenamename of output file
modeselects output format
...optional table names or tuples of table name, and where-clause depending on mode parameter
Returns
approximate number of lines written or -1 when an error occurred

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.

◆ impexp_export_xml()

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".

Parameters
dbSQLite database pointer
filenamename of output file
appendif true, append to existing output file
indentnumber of blanks to indent output
rootoptional tag use to enclose table output
itemtag to use per row
tablenametable to be output
schemaoptional schema or NULL
Returns
number of output lines

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">&#x01;&#x02;&x03;</a>
         <b TYPE="TEXT">&lt;blob&gt;</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.

◆ impexp_import_sql()

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.

Parameters
dbSQLite database pointer
filenamename of input file
Returns
number of changes to database

Definition at line 869 of file impexp.c.

References process_input().

◆ impexp_init()

int impexp_init ( sqlite3 * db)

Registers the SQLite functions.

Parameters
dbSQLite database pointer
Returns
SQLite error code

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().


Generated on Sun Nov 26 2023 by doxygen.
Contact: chw@ch-werner.de