Data Structures | Typedefs | Functions | Variables
csvtable.c File Reference
#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
 
struct  csv_guess_fmt
 
struct  csv_vtab
 
struct  csv_cursor
 

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. More...
 
static char * append (char **in, char const *append, char quote)
 Append a string to dynamically allocated string buffer with optional quoting. More...
 
static char * unquote (char const *in)
 Strip off quotes given string. More...
 
static int maptype (char const *type)
 Map string to SQLite data type. More...
 
static void conv_names (char **names, int ncols)
 Convert and collapse white space in column names to underscore. More...
 
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. More...
 
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. More...
 
static csv_filecsv_open (const char *filename, const char *sep, const char *quot)
 Open CSV file for reading and return handle to it. More...
 
static void csv_close (csv_file *csv)
 Close CSV file handle. More...
 
static int csv_eof (csv_file *csv)
 Test EOF on CSV file handle. More...
 
static long csv_seek (csv_file *csv, long pos)
 Position CSV file handle. More...
 
static void csv_rewind (csv_file *csv)
 Rewind CSV file handle. More...
 
static long csv_tell (csv_file *csv)
 Return current position of CSV file handle. More...
 
static int csv_getline (csv_file *csv, csv_guess_fmt *guess)
 Read and process one line of CSV file handle. More...
 
static int csv_ncols (csv_file *csv)
 Return number of columns of current row in CSV file. More...
 
static char * csv_coldata (csv_file *csv, int n)
 Return nth column of current row in CSV file. More...
 
static int csv_guess (csv_file *csv)
 Guess CSV layout of CSV file handle. More...
 
static int csv_vtab_connect (sqlite3 *db, void *aux, int argc, const char *const *argv, sqlite3_vtab **vtabp, char **errp)
 Connect to virtual table. More...
 
static int csv_vtab_create (sqlite3 *db, void *aux, int argc, const char *const *argv, sqlite3_vtab **vtabp, char **errp)
 Create virtual table. More...
 
static int csv_vtab_disconnect (sqlite3_vtab *vtab)
 Disconnect virtual table. More...
 
static int csv_vtab_destroy (sqlite3_vtab *vtab)
 Destroy virtual table. More...
 
static int csv_vtab_bestindex (sqlite3_vtab *vtab, sqlite3_index_info *info)
 Determines information for filter function according to constraints. More...
 
static int csv_vtab_open (sqlite3_vtab *vtab, sqlite3_vtab_cursor **cursorp)
 Open virtual table and return cursor. More...
 
static int csv_vtab_close (sqlite3_vtab_cursor *cursor)
 Close virtual table cursor. More...
 
static int csv_vtab_next (sqlite3_vtab_cursor *cursor)
 Retrieve next row from virtual table cursor. More...
 
static int csv_vtab_filter (sqlite3_vtab_cursor *cursor, int idxNum, const char *idxStr, int argc, sqlite3_value **argv)
 Filter function for virtual table. More...
 
static int csv_vtab_eof (sqlite3_vtab_cursor *cursor)
 Return end of table state of virtual table cursor. More...
 
static int csv_vtab_column (sqlite3_vtab_cursor *cursor, sqlite3_context *ctx, int n)
 Return column data of virtual table. More...
 
static int csv_vtab_rowid (sqlite3_vtab_cursor *cursor, sqlite_int64 *rowidp)
 Return current rowid of virtual table cursor. More...
 
static void csv_import_func (sqlite3_context *ctx, int argc, sqlite3_value **argv)
 Import CSV file as table into database. More...
 
static int csv_vtab_init (sqlite3 *db)
 Module initializer creating SQLite functions and modules. More...
 
int sqlite3_extension_init (sqlite3 *db, char **errmsg, const sqlite3_api_routines *api)
 Initializer for SQLite extension load mechanism. More...
 

Variables

static const sqlite3_module csv_vtab_mod
 SQLite module descriptor. More...
 

Detailed Description

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 Documentation

◆ csv_file

typedef struct csv_file csv_file

◆ csv_guess_fmt

◆ csv_vtab

Function Documentation

◆ append()

static char* append ( char **  in,
char const *  append,
char  quote 
)
static

Append a string to dynamically allocated string buffer with optional quoting.

Parameters
ininput string pointer
appendstring to append
quotequote character or NUL
Returns
new string to be free'd with append_free()

Definition at line 118 of file csvtable.c.

Referenced by csv_import_func(), and csv_vtab_connect().

◆ append_free()

static void append_free ( char **  in)
static

Free dynamically allocated string buffer.

Parameters
ininput string pointer

Definition at line 97 of file csvtable.c.

Referenced by csv_import_func(), and csv_vtab_connect().

◆ conv_names()

static void conv_names ( char **  names,
int  ncols 
)
static

Convert and collapse white space in column names to underscore.

Parameters
namesstring vector of column names
ncolsnumber of columns

Definition at line 246 of file csvtable.c.

Referenced by csv_import_func(), and csv_vtab_connect().

◆ csv_close()

static void csv_close ( csv_file csv)
static

Close CSV file handle.

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

◆ csv_coldata()

static char* csv_coldata ( csv_file csv,
int  n 
)
static

Return nth column of current row in CSV file.

Parameters
csvCSV file handle
ncolumn number
Returns
string pointer or NULL

Definition at line 841 of file csvtable.c.

References csv_file::cols.

Referenced by csv_import_func(), and csv_vtab_column().

◆ csv_eof()

static int csv_eof ( csv_file csv)
static

Test EOF on CSV file handle.

Parameters
csvCSV file handle
Returns
true when file position is at EOF

Definition at line 584 of file csvtable.c.

References csv_file::f.

Referenced by csv_vtab_eof().

◆ csv_getline()

static int csv_getline ( csv_file csv,
csv_guess_fmt guess 
)
static

Read and process one line of CSV file handle.

Parameters
csvCSV file handle
guessNULL or buffer for guessing file format
Returns
number of columns on success, EOF on error

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

◆ csv_guess()

static int csv_guess ( csv_file csv)
static

Guess CSV layout of CSV file handle.

Parameters
csvCSV file handle
Returns
0 on succes, EOF on error

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

◆ csv_import_func()

static void csv_import_func ( sqlite3_context *  ctx,
int  argc,
sqlite3_value **  argv 
)
static

Import CSV file as table into database.

Parameters
ctxSQLite function context
argcnumber of arguments
argvargument 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().

◆ csv_ncols()

static int csv_ncols ( csv_file csv)
static

Return number of columns of current row in CSV file.

Parameters
csvCSV file handle
Returns
number of columns of current row

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

◆ csv_open()

static csv_file* csv_open ( const char *  filename,
const char *  sep,
const char *  quot 
)
static

Open CSV file for reading and return handle to it.

Parameters
filenamename of CSV file
sepcolumn separator characters or NULL
quotstring quote characters or NULL
Returns
CSV file handle

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

◆ csv_rewind()

static void csv_rewind ( csv_file csv)
static

Rewind CSV file handle.

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

◆ csv_seek()

static long csv_seek ( csv_file csv,
long  pos 
)
static

Position CSV file handle.

Parameters
csvCSV file handle
posposition to seek
Returns
0 on success, EOF on error

Definition at line 600 of file csvtable.c.

References csv_file::f.

Referenced by csv_rewind().

◆ csv_tell()

static long csv_tell ( csv_file csv)
static

Return current position of CSV file handle.

Parameters
csvCSV file handle
Returns
current file position

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

◆ csv_vtab_bestindex()

static int csv_vtab_bestindex ( sqlite3_vtab *  vtab,
sqlite3_index_info *  info 
)
static

Determines information for filter function according to constraints.

Parameters
vtabvirtual table
infoindex/constraint iinformation
Returns
SQLite error code

Definition at line 1189 of file csvtable.c.

◆ csv_vtab_close()

static int csv_vtab_close ( sqlite3_vtab_cursor *  cursor)
static

Close virtual table cursor.

Parameters
cursorcursor pointer
Returns
SQLite error code

Definition at line 1224 of file csvtable.c.

◆ csv_vtab_column()

static int csv_vtab_column ( sqlite3_vtab_cursor *  cursor,
sqlite3_context *  ctx,
int  n 
)
static

Return column data of virtual table.

Parameters
cursorvirtual table cursor
ctxSQLite function context
ncolumn index
Returns
SQLite error code

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

◆ csv_vtab_connect()

static int csv_vtab_connect ( sqlite3 *  db,
void *  aux,
int  argc,
const char *const *  argv,
sqlite3_vtab **  vtabp,
char **  errp 
)
static

Connect to virtual table.

Parameters
dbSQLite database pointer
auxuser specific pointer (unused)
argcargument count
argvargument vector
vtabppointer receiving virtual table pointer
errppointer receiving error messag
Returns
SQLite error code

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

◆ csv_vtab_create()

static int csv_vtab_create ( sqlite3 *  db,
void *  aux,
int  argc,
const char *const *  argv,
sqlite3_vtab **  vtabp,
char **  errp 
)
static

Create virtual table.

Parameters
dbSQLite database pointer
auxuser specific pointer (unused)
argcargument count
argvargument vector
vtabppointer receiving virtual table pointer
errppointer receiving error messag
Returns
SQLite error code

Definition at line 1146 of file csvtable.c.

References csv_vtab_connect().

◆ csv_vtab_destroy()

static int csv_vtab_destroy ( sqlite3_vtab *  vtab)
static

Destroy virtual table.

Parameters
vtabvirtual table pointer
Returns
always SQLITE_OK

Definition at line 1176 of file csvtable.c.

References csv_vtab_disconnect().

◆ csv_vtab_disconnect()

static int csv_vtab_disconnect ( sqlite3_vtab *  vtab)
static

Disconnect virtual table.

Parameters
vtabvirtual table pointer
Returns
always SQLITE_OK

Definition at line 1160 of file csvtable.c.

References csv_vtab::csv, and csv_close().

Referenced by csv_vtab_destroy().

◆ csv_vtab_eof()

static int csv_vtab_eof ( sqlite3_vtab_cursor *  cursor)
static

Return end of table state of virtual table cursor.

Parameters
cursorvirtual table cursor
Returns
true/false

Definition at line 1275 of file csvtable.c.

References csv_vtab::csv, csv_eof(), and csv_cursor::cursor.

◆ csv_vtab_filter()

static int csv_vtab_filter ( sqlite3_vtab_cursor *  cursor,
int  idxNum,
const char *  idxStr,
int  argc,
sqlite3_value **  argv 
)
static

Filter function for virtual table.

Parameters
cursorvirtual table cursor
idxNumunused (always 0)
idxStrunused
argcnumber arguments (unused, 0)
argvargument (nothing)
Returns
SQLite error code

Definition at line 1258 of file csvtable.c.

References csv_vtab::csv, csv_rewind(), csv_vtab_next(), and csv_cursor::cursor.

◆ csv_vtab_init()

static int csv_vtab_init ( sqlite3 *  db)
static

Module initializer creating SQLite functions and modules.

Parameters
dbdatabase pointer
Returns
SQLite error code

Definition at line 1658 of file csvtable.c.

References csv_import_func(), and csv_vtab_mod.

Referenced by sqlite3_extension_init().

◆ csv_vtab_next()

static int csv_vtab_next ( sqlite3_vtab_cursor *  cursor)
static

Retrieve next row from virtual table cursor.

Parameters
cursorvirtual table cursor
Returns
SQLite error code

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

◆ csv_vtab_open()

static int csv_vtab_open ( sqlite3_vtab *  vtab,
sqlite3_vtab_cursor **  cursorp 
)
static

Open virtual table and return cursor.

Parameters
vtabvirtual table pointer
cursorppointer receiving cursor pointer
Returns
SQLite error code

Definition at line 1202 of file csvtable.c.

References csv_vtab::csv, csv_rewind(), csv_tell(), csv_cursor::cursor, and csv_cursor::pos.

◆ csv_vtab_rowid()

static int csv_vtab_rowid ( sqlite3_vtab_cursor *  cursor,
sqlite_int64 *  rowidp 
)
static

Return current rowid of virtual table cursor.

Parameters
cursorvirtual table cursor
rowidpvalue buffer to receive current rowid
Returns
SQLite error code

Definition at line 1309 of file csvtable.c.

References csv_cursor::pos.

◆ maptype()

static int maptype ( char const *  type)
static

Map string to SQLite data type.

Parameters
typestring to be mapped
Returns
SQLITE_TEXT et.al.

Definition at line 216 of file csvtable.c.

Referenced by csv_import_func(), and csv_vtab_connect().

◆ process_col()

static int process_col ( sqlite3_context *  ctx,
sqlite3_stmt *  stmt,
int  idx,
char *  data,
int  type,
int  conv 
)
static

Process one column of the current row.

Parameters
ctxSQLite function context or NULL
stmtSQLite statement or NULL
idxparameter index, 1-based
datastring data
typeSQLite type
convconversion flags

Definition at line 360 of file csvtable.c.

References result_or_bind().

Referenced by csv_import_func(), and csv_vtab_column().

◆ result_or_bind()

static void result_or_bind ( sqlite3_context *  ctx,
sqlite3_stmt *  stmt,
int  idx,
char *  data,
int  len,
int  type 
)
static

Make result data or parameter binding accoring to type.

Parameters
ctxSQLite function context or NULL
stmtSQLite statement or NULL
idxparameter number, 1-based
datastring data
lenstring length
typeSQLite type

Definition at line 286 of file csvtable.c.

Referenced by process_col().

◆ sqlite3_extension_init()

int sqlite3_extension_init ( sqlite3 *  db,
char **  errmsg,
const sqlite3_api_routines *  api 
)

Initializer for SQLite extension load mechanism.

Parameters
dbSQLite database pointer
errmsgpointer receiving error message
apiSQLite API routines
Returns
SQLite error code

Definition at line 1676 of file csvtable.c.

References csv_vtab_init().

◆ unquote()

static char* unquote ( char const *  in)
static

Strip off quotes given string.

Parameters
instring to be processed
Returns
new string to be free'd with sqlite3_free()

Definition at line 188 of file csvtable.c.

Referenced by csv_vtab_connect().

Variable Documentation

◆ csv_vtab_mod

const sqlite3_module csv_vtab_mod
static
csv_vtab_create
static int csv_vtab_create(sqlite3 *db, void *aux, int argc, const char *const *argv, sqlite3_vtab **vtabp, char **errp)
Create virtual table.
Definition: csvtable.c:1146
csv_vtab_destroy
static int csv_vtab_destroy(sqlite3_vtab *vtab)
Destroy virtual table.
Definition: csvtable.c:1176
csv_vtab_filter
static int csv_vtab_filter(sqlite3_vtab_cursor *cursor, int idxNum, const char *idxStr, int argc, sqlite3_value **argv)
Filter function for virtual table.
Definition: csvtable.c:1258
csv_vtab_column
static int csv_vtab_column(sqlite3_vtab_cursor *cursor, sqlite3_context *ctx, int n)
Return column data of virtual table.
Definition: csvtable.c:1292
csv_vtab_close
static int csv_vtab_close(sqlite3_vtab_cursor *cursor)
Close virtual table cursor.
Definition: csvtable.c:1224
csv_vtab_eof
static int csv_vtab_eof(sqlite3_vtab_cursor *cursor)
Return end of table state of virtual table cursor.
Definition: csvtable.c:1275
csv_vtab_bestindex
static int csv_vtab_bestindex(sqlite3_vtab *vtab, sqlite3_index_info *info)
Determines information for filter function according to constraints.
Definition: csvtable.c:1189
csv_vtab_disconnect
static int csv_vtab_disconnect(sqlite3_vtab *vtab)
Disconnect virtual table.
Definition: csvtable.c:1160
csv_vtab_connect
static int csv_vtab_connect(sqlite3 *db, void *aux, int argc, const char *const *argv, sqlite3_vtab **vtabp, char **errp)
Connect to virtual table.
Definition: csvtable.c:966
csv_vtab_next
static int csv_vtab_next(sqlite3_vtab_cursor *cursor)
Retrieve next row from virtual table cursor.
Definition: csvtable.c:1237
csv_vtab_rowid
static int csv_vtab_rowid(sqlite3_vtab_cursor *cursor, sqlite_int64 *rowidp)
Return current rowid of virtual table cursor.
Definition: csvtable.c:1309
csv_vtab_open
static int csv_vtab_open(sqlite3_vtab *vtab, sqlite3_vtab_cursor **cursorp)
Open virtual table and return cursor.
Definition: csvtable.c:1202

Generated on Mon Aug 17 2020 by doxygen.
Contact: chw@ch-werner.de