csvtable.c File Reference

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

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

typedef struct csv_guess_fmt csv_guess_fmt

◆ csv_vtab

typedef struct csv_vtab 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.

References append().

Referenced by append(), 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
Initial value:
= {
1,
0,
0,
0,
0,
0,
0,
}
static int csv_vtab_rowid(sqlite3_vtab_cursor *cursor, sqlite_int64 *rowidp)
Return current rowid of virtual table cursor.
Definition csvtable.c:1309
static int csv_vtab_disconnect(sqlite3_vtab *vtab)
Disconnect virtual table.
Definition csvtable.c:1160
static int csv_vtab_close(sqlite3_vtab_cursor *cursor)
Close virtual table cursor.
Definition csvtable.c:1224
static int csv_vtab_destroy(sqlite3_vtab *vtab)
Destroy virtual table.
Definition csvtable.c:1176
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
static int csv_vtab_open(sqlite3_vtab *vtab, sqlite3_vtab_cursor **cursorp)
Open virtual table and return cursor.
Definition csvtable.c:1202
static int csv_vtab_next(sqlite3_vtab_cursor *cursor)
Retrieve next row from virtual table cursor.
Definition csvtable.c:1237
static int csv_vtab_eof(sqlite3_vtab_cursor *cursor)
Return end of table state of virtual table cursor.
Definition csvtable.c:1275
static int csv_vtab_column(sqlite3_vtab_cursor *cursor, sqlite3_context *ctx, int n)
Return column data of virtual table.
Definition csvtable.c:1292
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
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
static int csv_vtab_bestindex(sqlite3_vtab *vtab, sqlite3_index_info *info)
Determines information for filter function according to constraints.
Definition csvtable.c:1189

SQLite module descriptor.

Definition at line 1337 of file csvtable.c.

Referenced by csv_vtab_init().


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