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>

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.

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

typedef struct csv_file csv_file

csv_guess_fmt

csv_vtab

Function Documentation

static char* append	(	char **	in,
		char const *	append,
		char	quote
	)		`[static]`

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

Parameters:

in	input string pointer
append	string to append
quote	quote 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().

static void append_free ( char ** in ) [static]

Free dynamically allocated string buffer.

Parameters:

in	input string pointer

Definition at line 97 of file csvtable.c.

Referenced by csv_import_func(), and csv_vtab_connect().

static void conv_names	(	char **	names,
		int	ncols
	)		`[static]`

Convert and collapse white space in column names to underscore.

Parameters:

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 void csv_close ( csv_file * csv ) [static]

Close CSV file handle.

Parameters:

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 char* csv_coldata	(	csv_file *	csv,
		int	n
	)		`[static]`

Return nth column of current row in CSV file.

Parameters:

csv	CSV file handle
n	column 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().

static int csv_eof ( csv_file * csv ) [static]

Test EOF on CSV file handle.

Parameters:

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

static int csv_getline	(	csv_file *	csv,
		csv_guess_fmt *	guess
	)		`[static]`

Read and process one line of CSV file handle.

Parameters:

csv	CSV file handle
guess	NULL 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().

static int csv_guess ( csv_file * csv ) [static]

Guess CSV layout of CSV file handle.

Parameters:

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

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

Import CSV file as table into database.

Parameters:

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 to single quote, in addition to backslash substitution

Definition at line 1351 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 int csv_ncols ( csv_file * csv ) [static]

Return number of columns of current row in CSV file.

Parameters:

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

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:

filename	name of CSV file
sep	column separator characters or NULL
quot	string 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().

static void csv_rewind ( csv_file * csv ) [static]

Rewind CSV file handle.

Parameters:

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 long csv_seek	(	csv_file *	csv,
		long	pos
	)		`[static]`

Position CSV file handle.

Parameters:

csv	CSV file handle
pos	position 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().

static long csv_tell ( csv_file * csv ) [static]

Return current position of CSV file handle.

Parameters:

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

static int csv_vtab_bestindex	(	sqlite3_vtab *	vtab,
		sqlite3_index_info *	info
	)		`[static]`

Determines information for filter function according to constraints.

Parameters:

vtab	virtual table
info	index/constraint iinformation

Returns:: SQLite error code

Definition at line 1150 of file csvtable.c.

static int csv_vtab_close ( sqlite3_vtab_cursor * cursor ) [static]

Close virtual table cursor.

Parameters:

cursor cursor pointer

Returns:: SQLite error code

Definition at line 1185 of file csvtable.c.

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

Return column data of virtual table.

Parameters:

cursor	virtual table cursor
ctx	SQLite function context
n	column index

Returns:: SQLite error code

Definition at line 1253 of file csvtable.c.

References csv_vtab::coltypes, csv_vtab::convert, csv_vtab::csv, csv_coldata(), csv_cursor::cursor, and process_col().

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:

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

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 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 int csv_vtab_create	(	sqlite3 *	db,
		void *	aux,
		int	argc,
		const char const	argv,
		sqlite3_vtab **	vtabp,
		char **	errp
	)		`[static]`

Create virtual table.

Parameters:

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

Returns:: SQLite error code

Definition at line 1107 of file csvtable.c.

References csv_vtab_connect().

static int csv_vtab_destroy ( sqlite3_vtab * vtab ) [static]

Destroy virtual table.

Parameters:

vtab	virtual table pointer

Returns:: always SQLITE_OK

Definition at line 1137 of file csvtable.c.

References csv_vtab_disconnect().

static int csv_vtab_disconnect ( sqlite3_vtab * vtab ) [static]

Disconnect virtual table.

Parameters:

vtab	virtual table pointer

Returns:: always SQLITE_OK

Definition at line 1121 of file csvtable.c.

References csv_vtab::csv, and csv_close().

Referenced by csv_vtab_destroy().

static int csv_vtab_eof ( sqlite3_vtab_cursor * cursor ) [static]

Return end of table state of virtual table cursor.

Parameters:

cursor virtual table cursor

Returns:: true/false

Definition at line 1236 of file csvtable.c.

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

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:

cursor	virtual table cursor
idxNum	unused (always 0)
idxStr	unused
argc	number arguments (unused, 0)
argv	argument (nothing)

Returns:: SQLite error code

Definition at line 1219 of file csvtable.c.

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

static int csv_vtab_init ( sqlite3 * db ) [static]

Module initializer creating SQLite functions and modules.

Parameters:

db	database pointer

Returns:: SQLite error code

Definition at line 1591 of file csvtable.c.

References csv_import_func(), and csv_vtab_mod.

Referenced by sqlite3_extension_init().

static int csv_vtab_next ( sqlite3_vtab_cursor * cursor ) [static]

Retrieve next row from virtual table cursor.

Parameters:

cursor virtual table cursor

Returns:: SQLite error code

Definition at line 1198 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 int csv_vtab_open	(	sqlite3_vtab *	vtab,
		sqlite3_vtab_cursor **	cursorp
	)		`[static]`

Open virtual table and return cursor.

Parameters:

vtab	virtual table pointer
cursorp	pointer receiving cursor pointer

Returns:: SQLite error code

Definition at line 1163 of file csvtable.c.

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

static int csv_vtab_rowid	(	sqlite3_vtab_cursor *	cursor,
		sqlite_int64 *	rowidp
	)		`[static]`

Return current rowid of virtual table cursor.

Parameters:

cursor	virtual table cursor
rowidp	value buffer to receive current rowid

Returns:: SQLite error code

Definition at line 1270 of file csvtable.c.

References csv_cursor::pos.

static int maptype ( char const * type ) [static]

Map string to SQLite data type.

Parameters:

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

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:

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

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.

Parameters:

db	SQLite database pointer
errmsg	pointer receiving error message
api	SQLite API routines

Returns:: SQLite error code

Definition at line 1609 of file csvtable.c.

References csv_vtab_init().

static char* unquote ( char const * in ) [static]

Strip off quotes given string.

Parameters:

in	string 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

const sqlite3_module csv_vtab_mod [static]

Initial value:

 {
    1,                   
    csv_vtab_create,     
    csv_vtab_connect,    
    csv_vtab_bestindex,  
    csv_vtab_disconnect, 
    csv_vtab_destroy,    
    csv_vtab_open,       
    csv_vtab_close,      
    csv_vtab_filter,     
    csv_vtab_next,       
    csv_vtab_eof,        
    csv_vtab_column,     
    csv_vtab_rowid,      
    0,                   
    0,                   
    0,                   
    0,                   
    0,                   
    0,                   



}

SQLite module descriptor.

Definition at line 1298 of file csvtable.c.

Referenced by csv_vtab_init().

csvtable.c File Reference

Data Structures

Typedefs

Functions

Variables

Detailed Description

Typedef Documentation

Function Documentation

Variable Documentation