Main Page   Alphabetical List   Data Structures   File List   Data Fields   Globals  

csvtable.c File Reference

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.#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_cursor
 Structure to describe CSV virtual table cursor. More...

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


Typedefs

typedef csv_file csv_file
typedef csv_guess_fmt csv_guess_fmt
typedef csv_vtab csv_vtab

Functions

void append_free (char **in)
 Free dynamically allocated string buffer.

char * append (char **in, char const *append, char quote)
 Append a string to dynamically allocated string buffer with optional quoting.

char * unquote (char const *in)
 Strip off quotes given string.

int maptype (char const *type)
 Map string to SQLite data type.

void conv_names (char **names, int ncols)
 Convert and collapse white space in column names to underscore.

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.

int process_col (sqlite3_context *ctx, sqlite3_stmt *stmt, int idx, char *data, int type, int conv)
 Process one column of the current row.

csv_file * csv_open (const char *filename, const char *sep, const char *quot)
 Open CSV file for reading and return handle to it.

void csv_close (csv_file *csv)
 Close CSV file handle.

int csv_eof (csv_file *csv)
 Test EOF on CSV file handle.

long csv_seek (csv_file *csv, long pos)
 Position CSV file handle.

void csv_rewind (csv_file *csv)
 Rewind CSV file handle.

long csv_tell (csv_file *csv)
 Return current position of CSV file handle.

int csv_getline (csv_file *csv, csv_guess_fmt *guess)
 Read and process one line of CSV file handle.

int csv_ncols (csv_file *csv)
 Return number of columns of current row in CSV file.

char * csv_coldata (csv_file *csv, int n)
 Return nth column of current row in CSV file.

int csv_guess (csv_file *csv)
 Guess CSV layout of CSV file handle.

int csv_vtab_connect (sqlite3 *db, void *aux, int argc, const char *const *argv, sqlite3_vtab **vtabp, char **errp)
 Connect to virtual table.

int csv_vtab_create (sqlite3 *db, void *aux, int argc, const char *const *argv, sqlite3_vtab **vtabp, char **errp)
 Create virtual table.

int csv_vtab_disconnect (sqlite3_vtab *vtab)
 Disconnect virtual table.

int csv_vtab_destroy (sqlite3_vtab *vtab)
 Destroy virtual table.

int csv_vtab_bestindex (sqlite3_vtab *vtab, sqlite3_index_info *info)
 Determines information for filter function according to constraints.

int csv_vtab_open (sqlite3_vtab *vtab, sqlite3_vtab_cursor **cursorp)
 Open virtual table and return cursor.

int csv_vtab_close (sqlite3_vtab_cursor *cursor)
 Close virtual table cursor.

int csv_vtab_next (sqlite3_vtab_cursor *cursor)
 Retrieve next row from virtual table cursor.

int csv_vtab_filter (sqlite3_vtab_cursor *cursor, int idxNum, const char *idxStr, int argc, sqlite3_value **argv)
 Filter function for virtual table.

int csv_vtab_eof (sqlite3_vtab_cursor *cursor)
 Return end of table state of virtual table cursor.

int csv_vtab_column (sqlite3_vtab_cursor *cursor, sqlite3_context *ctx, int n)
 Return column data of virtual table.

int csv_vtab_rowid (sqlite3_vtab_cursor *cursor, sqlite_int64 *rowidp)
 Return current rowid of virtual table cursor.

void csv_import_func (sqlite3_context *ctx, int argc, sqlite3_value **argv)
 Import CSV file as table into database.

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

const sqlite3_module csv_vtab_mod
 SQLite module descriptor.

Typedef Documentation

typedef struct csv_file csv_file
 

typedef struct csv_guess_fmt csv_guess_fmt
 

typedef struct csv_vtab csv_vtab
 

Function Documentation

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.

References append().

Referenced by append(), csv_import_func(), csv_vtab_connect(), and dump_cb().

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(), csv_vtab_connect(), and dump_cb().

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

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

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, and csv_file::ncols.

Referenced by csv_import_func(), and csv_vtab_column().

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

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

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

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

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

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

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

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

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

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 1189 of file csvtable.c.

int csv_vtab_close (  sqlite3_vtab_cursor *    cursor )  [static]
 

Close virtual table cursor.

Parameters:
cursor  cursor pointer
Returns:
SQLite error code

Definition at line 1224 of file csvtable.c.

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 1292 of file csvtable.c.

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

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

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 1146 of file csvtable.c.

References csv_vtab_connect().

int csv_vtab_destroy (  sqlite3_vtab *    vtab )  [static]
 

Destroy virtual table.

Parameters:
vtab  virtual table pointer
Returns:
always SQLITE_OK

Definition at line 1176 of file csvtable.c.

References csv_vtab_disconnect().

int csv_vtab_disconnect (  sqlite3_vtab *    vtab )  [static]
 

Disconnect virtual table.

Parameters:
vtab  virtual 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().

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 1275 of file csvtable.c.

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

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 1258 of file csvtable.c.

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

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 1658 of file csvtable.c.

References csv_import_func(), and csv_vtab_mod.

Referenced by sqlite3_extension_init().

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

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 1202 of file csvtable.c.

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

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 1309 of file csvtable.c.

References csv_cursor::pos.

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

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

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 1676 of file csvtable.c.

References csv_vtab_init().

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.

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 1337 of file csvtable.c.

Referenced by csv_vtab_init().

Generated on 23 Oct 2023 by doxygen.
Contact: chw@ch-werner.de