SQLite extension module for mapping a CSV file as a read-only SQLite virtual table plus extension function to import a CSV file as a real table. More...
#include <sqlite3ext.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <limits.h>
Go to the source code of this file.
Data Structures | |
struct | csv_file |
Structure to implement CSV file handle. More... | |
struct | csv_guess_fmt |
Info to guess CSV layout. More... | |
struct | csv_vtab |
Structure to describe a CSV virtual table. More... | |
struct | csv_cursor |
Structure to describe CSV virtual table cursor. More... | |
Typedefs | |
typedef struct csv_file | csv_file |
typedef struct csv_guess_fmt | csv_guess_fmt |
typedef struct csv_vtab | csv_vtab |
Functions | |
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 char * | unquote (char const *in) |
Strip off quotes given string. | |
static int | maptype (char const *type) |
Map string to SQLite data type. | |
static void | conv_names (char **names, int ncols) |
Convert and collapse white space in column names to underscore. | |
static void | result_or_bind (sqlite3_context *ctx, sqlite3_stmt *stmt, int idx, char *data, int len, int type) |
Make result data or parameter binding accoring to type. | |
static int | process_col (sqlite3_context *ctx, sqlite3_stmt *stmt, int idx, char *data, int type, int conv) |
Process one column of the current row. | |
static csv_file * | csv_open (const char *filename, const char *sep, const char *quot) |
Open CSV file for reading and return handle to it. | |
static void | csv_close (csv_file *csv) |
Close CSV file handle. | |
static int | csv_eof (csv_file *csv) |
Test EOF on CSV file handle. | |
static long | csv_seek (csv_file *csv, long pos) |
Position CSV file handle. | |
static void | csv_rewind (csv_file *csv) |
Rewind CSV file handle. | |
static long | csv_tell (csv_file *csv) |
Return current position of CSV file handle. | |
static int | csv_getline (csv_file *csv, csv_guess_fmt *guess) |
Read and process one line of CSV file handle. | |
static int | csv_ncols (csv_file *csv) |
Return number of columns of current row in CSV file. | |
static char * | csv_coldata (csv_file *csv, int n) |
Return nth column of current row in CSV file. | |
static int | csv_guess (csv_file *csv) |
Guess CSV layout of CSV file handle. | |
static int | csv_vtab_connect (sqlite3 *db, void *aux, int argc, const char *const *argv, sqlite3_vtab **vtabp, char **errp) |
Connect to virtual table. | |
static int | csv_vtab_create (sqlite3 *db, void *aux, int argc, const char *const *argv, sqlite3_vtab **vtabp, char **errp) |
Create virtual table. | |
static int | csv_vtab_disconnect (sqlite3_vtab *vtab) |
Disconnect virtual table. | |
static int | csv_vtab_destroy (sqlite3_vtab *vtab) |
Destroy virtual table. | |
static int | csv_vtab_bestindex (sqlite3_vtab *vtab, sqlite3_index_info *info) |
Determines information for filter function according to constraints. | |
static int | csv_vtab_open (sqlite3_vtab *vtab, sqlite3_vtab_cursor **cursorp) |
Open virtual table and return cursor. | |
static int | csv_vtab_close (sqlite3_vtab_cursor *cursor) |
Close virtual table cursor. | |
static int | csv_vtab_next (sqlite3_vtab_cursor *cursor) |
Retrieve next row from virtual table cursor. | |
static int | csv_vtab_filter (sqlite3_vtab_cursor *cursor, int idxNum, const char *idxStr, int argc, sqlite3_value **argv) |
Filter function for virtual table. | |
static int | csv_vtab_eof (sqlite3_vtab_cursor *cursor) |
Return end of table state of virtual table cursor. | |
static int | csv_vtab_column (sqlite3_vtab_cursor *cursor, sqlite3_context *ctx, int n) |
Return column data of virtual table. | |
static int | csv_vtab_rowid (sqlite3_vtab_cursor *cursor, sqlite_int64 *rowidp) |
Return current rowid of virtual table cursor. | |
static void | csv_import_func (sqlite3_context *ctx, int argc, sqlite3_value **argv) |
Import CSV file as table into database. | |
static int | csv_vtab_init (sqlite3 *db) |
Module initializer creating SQLite functions and modules. | |
int | sqlite3_extension_init (sqlite3 *db, char **errmsg, const sqlite3_api_routines *api) |
Initializer for SQLite extension load mechanism. | |
Variables | |
static const sqlite3_module | csv_vtab_mod |
SQLite module descriptor. | |
SQLite extension module for mapping a CSV file as a read-only SQLite virtual table plus extension function to import a CSV file as a real table.
2012 July 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 csvtable.c.
typedef struct csv_file csv_file |
typedef struct csv_guess_fmt csv_guess_fmt |
typedef struct csv_vtab csv_vtab |
|
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 118 of file csvtable.c.
References append().
Referenced by append(), csv_import_func(), and csv_vtab_connect().
|
static |
Free dynamically allocated string buffer.
in | input string pointer |
Definition at line 97 of file csvtable.c.
Referenced by csv_import_func(), and csv_vtab_connect().
|
static |
Convert and collapse white space in column names to underscore.
names | string vector of column names |
ncols | number of columns |
Definition at line 246 of file csvtable.c.
Referenced by csv_import_func(), and csv_vtab_connect().
|
static |
Close CSV file handle.
csv | CSV file handle |
Definition at line 555 of file csvtable.c.
References csv_file::cols, csv_file::f, csv_file::line, csv_file::quot, and csv_file::sep.
Referenced by csv_import_func(), csv_vtab_connect(), and csv_vtab_disconnect().
|
static |
Return nth column of current row in CSV file.
csv | CSV file handle |
n | column number |
Definition at line 841 of file csvtable.c.
References csv_file::cols.
Referenced by csv_import_func(), and csv_vtab_column().
|
static |
Test EOF on CSV file handle.
csv | CSV file handle |
Definition at line 584 of file csvtable.c.
References csv_file::f.
Referenced by csv_vtab_eof().
|
static |
Read and process one line of CSV file handle.
csv | CSV file handle |
guess | NULL or buffer for guessing file format |
Definition at line 644 of file csvtable.c.
References csv_file::cols, csv_file::f, csv_guess_fmt::hist, csv_file::isdos, csv_file::line, csv_file::maxc, csv_file::maxl, csv_file::ncols, csv_guess_fmt::nlines, csv_file::quot, and csv_file::sep.
Referenced by csv_guess(), csv_import_func(), csv_vtab_connect(), and csv_vtab_next().
|
static |
Guess CSV layout of CSV file handle.
csv | CSV file handle |
Definition at line 856 of file csvtable.c.
References csv_getline(), csv_rewind(), csv_guess_fmt::hist, min, csv_guess_fmt::nlines, csv_file::pos0, csv_file::quot, and csv_file::sep.
Referenced by csv_import_func(), and csv_vtab_connect().
|
static |
Import CSV file as table into database.
ctx | SQLite function context |
argc | number of arguments |
argv | argument vector |
Argument vector contains:
argv[0] - name of table to create (required)
argv[1] - filename (required)
argv[2] - number, when non-zero use first line as column names, when negative use given type names (optional)
argv[3] - number, when non-zero, translate data (optional, see below)
argv[4] - column separator characters (optional)
argv[5] - string quoting characters (optional)
argv[6] - column/type name for first column (optional)
..
argv[X] - column/type name for last column (optional)
Translation flags:
1 - convert ISO-8859-1 to UTF-8
2 - perform backslash substitution
4 - convert and collapse white-space in column names to underscore
10 - convert \q to single quote, in addition to backslash substitution
Definition at line 1390 of file csvtable.c.
References append(), append_free(), csv_file::cols, conv_names(), csv_close(), csv_coldata(), csv_getline(), csv_guess(), csv_ncols(), csv_open(), csv_rewind(), csv_tell(), maptype(), csv_file::ncols, csv_file::pos0, process_col(), csv_file::quot, and csv_file::sep.
Referenced by csv_vtab_init().
|
static |
Return number of columns of current row in CSV file.
csv | CSV file handle |
Definition at line 825 of file csvtable.c.
References csv_file::cols, and csv_file::ncols.
Referenced by csv_import_func(), and csv_vtab_connect().
|
static |
Open CSV file for reading and return handle to it.
filename | name of CSV file |
sep | column separator characters or NULL |
quot | string quote characters or NULL |
Definition at line 496 of file csvtable.c.
References csv_file::cols, csv_file::f, csv_file::isdos, csv_file::line, csv_file::maxc, csv_file::maxl, csv_file::ncols, csv_file::pos0, csv_file::quot, and csv_file::sep.
Referenced by csv_import_func(), and csv_vtab_connect().
|
static |
Rewind CSV file handle.
csv | CSV file handle |
Definition at line 614 of file csvtable.c.
References csv_seek(), csv_file::f, and csv_file::pos0.
Referenced by csv_guess(), csv_import_func(), csv_vtab_connect(), csv_vtab_filter(), and csv_vtab_open().
|
static |
Position CSV file handle.
csv | CSV file handle |
pos | position to seek |
Definition at line 600 of file csvtable.c.
References csv_file::f.
Referenced by csv_rewind().
|
static |
Return current position of CSV file handle.
csv | CSV file handle |
Definition at line 628 of file csvtable.c.
References csv_file::f.
Referenced by csv_import_func(), csv_vtab_connect(), csv_vtab_next(), and csv_vtab_open().
|
static |
Determines information for filter function according to constraints.
vtab | virtual table |
info | index/constraint iinformation |
Definition at line 1189 of file csvtable.c.
|
static |
Close virtual table cursor.
cursor | cursor pointer |
Definition at line 1224 of file csvtable.c.
|
static |
Return column data of virtual table.
cursor | virtual table cursor |
ctx | SQLite function context |
n | column index |
Definition at line 1292 of file csvtable.c.
References csv_vtab::coltypes, csv_vtab::convert, csv_vtab::csv, csv_coldata(), csv_cursor::cursor, and process_col().
|
static |
Connect to virtual table.
db | SQLite database pointer |
aux | user specific pointer (unused) |
argc | argument count |
argv | argument vector |
vtabp | pointer receiving virtual table pointer |
errp | pointer receiving error messag |
Argument vector contains:
argv[0] - module name
argv[1] - database name
argv[2] - table name (virtual table)
argv[3] - filename (required)
argv[4] - number, when non-zero use first line as column names, when negative use given type names (optional)
argv[5] - number, when non-zero, translate data (optional, see below)
argv[6] - column separator characters (optional)
argv[7] - string quoting characters (optional)
argv[8] - column/type name for first column (optional)
..
argv[X] - column/type name for last column (optional)
Translation flags:
1 - convert ISO-8859-1 to UTF-8
2 - perform backslash substitution
4 - convert and collapse white-space in column names to underscore
10 - convert \q to single quote, in addition to backslash substitution
Definition at line 966 of file csvtable.c.
References append(), append_free(), csv_file::cols, csv_vtab::coltypes, conv_names(), csv_vtab::convert, csv_vtab::csv, csv_close(), csv_getline(), csv_guess(), csv_ncols(), csv_open(), csv_rewind(), csv_tell(), maptype(), csv_file::ncols, csv_file::pos0, csv_file::quot, csv_file::sep, unquote(), and csv_vtab::vtab.
Referenced by csv_vtab_create().
|
static |
Create virtual table.
db | SQLite database pointer |
aux | user specific pointer (unused) |
argc | argument count |
argv | argument vector |
vtabp | pointer receiving virtual table pointer |
errp | pointer receiving error messag |
Definition at line 1146 of file csvtable.c.
References csv_vtab_connect().
|
static |
Destroy virtual table.
vtab | virtual table pointer |
Definition at line 1176 of file csvtable.c.
References csv_vtab_disconnect().
|
static |
Disconnect virtual table.
vtab | virtual table pointer |
Definition at line 1160 of file csvtable.c.
References csv_vtab::csv, and csv_close().
Referenced by csv_vtab_destroy().
|
static |
Return end of table state of virtual table cursor.
cursor | virtual table cursor |
Definition at line 1275 of file csvtable.c.
References csv_vtab::csv, csv_eof(), and csv_cursor::cursor.
|
static |
Filter function for virtual table.
cursor | virtual table cursor |
idxNum | unused (always 0) |
idxStr | unused |
argc | number arguments (unused, 0) |
argv | argument (nothing) |
Definition at line 1258 of file csvtable.c.
References csv_vtab::csv, csv_rewind(), csv_vtab_next(), and csv_cursor::cursor.
|
static |
Module initializer creating SQLite functions and modules.
db | database pointer |
Definition at line 1658 of file csvtable.c.
References csv_import_func(), and csv_vtab_mod.
Referenced by sqlite3_extension_init().
|
static |
Retrieve next row from virtual table cursor.
cursor | virtual table cursor |
Definition at line 1237 of file csvtable.c.
References csv_vtab::csv, csv_getline(), csv_tell(), csv_cursor::cursor, and csv_cursor::pos.
Referenced by csv_vtab_filter().
|
static |
Open virtual table and return cursor.
vtab | virtual table pointer |
cursorp | pointer receiving cursor pointer |
Definition at line 1202 of file csvtable.c.
References csv_vtab::csv, csv_rewind(), csv_tell(), csv_cursor::cursor, and csv_cursor::pos.
|
static |
Return current rowid of virtual table cursor.
cursor | virtual table cursor |
rowidp | value buffer to receive current rowid |
Definition at line 1309 of file csvtable.c.
References csv_cursor::pos.
|
static |
Map string to SQLite data type.
type | string to be mapped |
Definition at line 216 of file csvtable.c.
Referenced by csv_import_func(), and csv_vtab_connect().
|
static |
Process one column of the current row.
ctx | SQLite function context or NULL |
stmt | SQLite statement or NULL |
idx | parameter index, 1-based |
data | string data |
type | SQLite type |
conv | conversion flags |
Definition at line 360 of file csvtable.c.
References result_or_bind().
Referenced by csv_import_func(), and csv_vtab_column().
|
static |
Make result data or parameter binding accoring to type.
ctx | SQLite function context or NULL |
stmt | SQLite statement or NULL |
idx | parameter number, 1-based |
data | string data |
len | string length |
type | SQLite type |
Definition at line 286 of file csvtable.c.
Referenced by process_col().
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 1676 of file csvtable.c.
References csv_vtab_init().
|
static |
Strip off quotes given string.
in | string to be processed |
Definition at line 188 of file csvtable.c.
Referenced by csv_vtab_connect().
|
static |
SQLite module descriptor.
Definition at line 1337 of file csvtable.c.
Referenced by csv_vtab_init().