Main Page   Alphabetical List   Data Structures   File List   Data Fields   Globals  

impexp.h File Reference


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.

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.


Typedef Documentation

impexp_putc
 

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.

Referenced by export_json_func(), impexp_export_json(), json_output(), json_pb64(), and json_pstrq().


Function Documentation

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:
db  SQLite database pointer
filename  name of output file
hdr  write 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.

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:
db  SQLite database pointer
sql  SQL to be executed
pfunc  pointer to output function
parg  argument 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 impexp_putc, and 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.

Parameters:
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
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.

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:
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
Returns:
number of output lines
Layout of an output row:
  <item>
   <columnname TYPE="INTEGER|REAL|NULL|TEXT|BLOB">value</columnname>
   ...
  </item>
 

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.

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:
db  SQLite database pointer
filename  name of input file
Returns:
number of changes to database

Definition at line 869 of file impexp.c.

References process_input().

int impexp_init sqlite3 *    db
 

Registers the SQLite functions.

Parameters:
db  SQLite 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 1 Dec 2016 by doxygen.
Contact: chw@ch-werner.de