22#include <sqlite3ext.h>
23static SQLITE_EXTENSION_INIT1
33#define strcasecmp _stricmp
34#define strncasecmp _strnicmp
99 long *p = (
long *) *in;
120 long *p = (
long *) *in;
121 long len, maxlen, actlen;
133 len = nappend + actlen;
136 for (i = 0; i < nappend; i++) {
141 }
else if (!nappend) {
144 if (len >= maxlen - 1) {
147 maxlen = (len + 0x03ff) & (~0x3ff);
148 q = (
long *) sqlite3_realloc(p, maxlen + 1 + 2 *
sizeof (
long));
157 *in = (
char *) (p + 2);
162 for (i = 0; i < nappend; i++) {
172 memcpy(pp,
append, nappend);
193 ret = sqlite3_malloc(strlen(in) + 1);
196 if ((c ==
'"') || (c ==
'\'')) {
198 if ((i > 0) && (in[i] == c)) {
218 int typelen = type ? strlen(type) : 0;
220 if ((typelen >= 3) &&
221 (strncasecmp(type,
"integer", 7) == 0)) {
222 return SQLITE_INTEGER;
224 if ((typelen >= 6) &&
225 (strncasecmp(type,
"double", 6) == 0)) {
228 if ((typelen >= 5) &&
229 (strncasecmp(type,
"float", 5) == 0)) {
232 if ((typelen >= 4) &&
233 (strncasecmp(type,
"real", 4) == 0)) {
250 static const char ws[] =
"\n\t\r\b\v ";
252 if (!names || ncols <= 0) {
255 for (i = 0; i < ncols; i++) {
259 if (strchr(ws, *p)) {
262 while (*q && strchr(ws, *q)) {
287 char *data,
int len,
int type)
293 sqlite3_result_null(ctx);
295 sqlite3_bind_null(
stmt, idx);
299 if (type == SQLITE_INTEGER) {
301#if defined(_WIN32) || defined(_WIN64)
304 if (sscanf(data,
"%I64d%c", &val, &endc) == 1) {
306 sqlite3_result_int64(ctx, val);
308 sqlite3_bind_int64(
stmt, idx, val);
315 val = strtol(data, &endp, 0);
317 val = strtoll(data, &endp, 0);
319 if (endp && (endp != data) && !*endp) {
321 sqlite3_result_int64(ctx, val);
323 sqlite3_bind_int64(
stmt, idx, val);
328 }
else if (type == SQLITE_FLOAT) {
332 val = strtod(data, &endp);
333 if (endp && (endp != data) && !*endp) {
335 sqlite3_result_double(ctx, val);
337 sqlite3_bind_double(
stmt, idx, val);
343 sqlite3_result_text(ctx, data, len, SQLITE_TRANSIENT);
345 sqlite3_bind_text(
stmt, idx, data, len, SQLITE_TRANSIENT);
361 char *data,
int type,
int conv)
364 const char flchars[] =
"Ee+-.,0123456789";
375 if ((c !=
'\0') && strchr(flchars + 2, c)) {
377 while (*p && strchr(flchars, *p)) {
402 char *utf = sqlite3_malloc(strlen(data) * 2 + 2);
406 while ((c = *data) !=
'\0') {
407 if (((conv & 10) == 10) && (c ==
'\\')) {
408 if (data[1] ==
'q') {
414 if ((conv & 2) && (c ==
'\\')) {
462 if ((conv & 1) && (c & 0x80)) {
463 *p++ = 0xc0 | ((c >> 6) & 0x1f);
464 *p++ = 0x80 | (c & 0x3f);
477 sqlite3_result_error(ctx,
"out of memory", -1);
496csv_open(
const char *filename,
const char *sep,
const char *quot)
502 f = fopen(filename,
"rb");
504 f = fopen(filename,
"r");
509 csv = sqlite3_malloc(
sizeof (
csv_file));
517 csv->
sep = sqlite3_malloc(strlen(sep) + 1);
523 strcpy(csv->
sep, sep);
527 if (quot && quot[0]) {
528 csv->
quot = sqlite3_malloc(strlen(quot) + 1);
531 sqlite3_free(csv->
sep);
535 strcpy(csv->
quot, quot);
559 sqlite3_free(csv->
sep);
562 sqlite3_free(csv->
quot);
565 sqlite3_free(csv->
line);
568 sqlite3_free(csv->
cols);
603 return fseek(csv->
f, pos, SEEK_SET);
631 return ftell(csv->
f);
646 int i, index = 0, inq = 0, c, col;
649 if (!csv || !csv->
f) {
664 int c2 = fgetc(csv->
f);
669 }
else if (c2 != EOF) {
674 if (csv->
isdos && (c ==
'\032')) {
675 int c2 = fgetc(csv->
f);
685 if (index >= csv->
maxl - 1) {
686 int n = csv->
maxl * 2;
692 line = sqlite3_malloc(n);
697 memcpy(line, csv->
line, index);
698 sqlite3_free(csv->
line);
703 csv->
line[index++] = c;
704 if (csv->
quot && (p = strchr(csv->
quot, c))) {
713 if (!inq && (c ==
'\n')) {
718 for (i = 0; i < index; i++) {
719 guess->
hist[csv->
line[i] & 0xFF] += 1;
726 for (i = index - 1; i >= 0; i--) {
727 if (csv->
line[i] !=
'\n') {
732 csv->
line[index] =
'\0';
734 sep = csv->
sep ? csv->
sep :
";";
738 csv->
cols = sqlite3_malloc(
sizeof (
char *) * n);
746 if (csv->
quot && (p = strchr(csv->
quot, csv->
line[i]))) {
755 if (!inq && (p = strchr(sep, csv->
line[i]))) {
758 if (col >= csv->
maxc) {
759 int n = csv->
maxc * 2;
762 cols = sqlite3_realloc(csv->
cols, sizeof (
char *) * n);
769 csv->
cols[col++] = p + 1;
777 for (i = 0; i < col; i++) {
779 p = strchr(csv->
quot, *csv->
cols[i]);
785 sep = csv->
cols[i] + strlen(csv->
cols[i]) - 1;
786 if ((sep >= csv->
cols[i]) && (*sep == c)) {
793 if ((*src == c) && (src[1] == c)) {
827 if (csv && csv->
cols) {
843 if (csv && csv->
cols && (n >= 0) && (n < csv->ncols)) {
860 char *p, sep[32], quot[4];
875 memset(&guess, 0,
sizeof (guess));
878 for (i = n = 0; i < 10; i++) {
890 if (guess.
hist[n] > 1) {
894 if (guess.
hist[n] > 1) {
899 for (i = 0; i <
sizeof (sep_test) /
sizeof (sep_test[0]); i++) {
900 if (guess.
hist[sep_test[i].c] > sep_test[i].min * guess.
nlines) {
901 *p++ = sep_test[i].c;
906 p = sqlite3_malloc(strlen(quot) + 1);
910 sqlite3_free(csv->
quot);
918 p = sqlite3_malloc(strlen(sep) + 1);
922 sqlite3_free(csv->
sep);
967 sqlite3_vtab **vtabp,
char **errp)
970 int rc = SQLITE_ERROR, i, k, ncnames, row1, *colmap = 0;
971 char **cnames, *schema = 0, **nargv;
975 *errp = sqlite3_mprintf(
"input file name missing");
978 nargv = sqlite3_malloc(
sizeof (
char *) * argc);
979 memset(nargv, 0,
sizeof (
char *) * argc);
980 for (i = 3; i < argc; i++) {
983 csv =
csv_open(nargv[3], (argc > 6) ? nargv[6] : 0,
984 (argc > 7) ? nargv[7] : 0);
986 *errp = sqlite3_mprintf(
"unable to open input file");
989 for (i = 3; i < argc; i++) {
991 sqlite3_free(nargv[i]);
998 sqlite3_free(colmap);
1002 if (!csv->
sep && !csv->
quot) {
1008 row1 = strtol(nargv[4], 0, 10);
1013 if (csv->
ncols < 1) {
1015 *errp = sqlite3_mprintf(
"unable to get column names");
1022 }
else if (argc > 8) {
1024 cnames = (
char **) nargv + 8;
1028 if (csv->
ncols < 1) {
1030 *errp = sqlite3_mprintf(
"unable to get column names");
1037 colmap = sqlite3_malloc(
sizeof (
int) * ncnames);
1040 *errp = sqlite3_mprintf(
"out of memory");
1043 memset(colmap, 0,
sizeof (
int) * ncnames);
1044 vtab = sqlite3_malloc(
sizeof (
csv_vtab) + ncnames);
1047 *errp = sqlite3_mprintf(
"out of memory");
1050 memset(vtab, 0,
sizeof (*vtab));
1053 vtab->
convert = strtol(nargv[5], 0, 10);
1054 if (row1 && (vtab->
convert & 4)) {
1059 append(&schema,
"CREATE TABLE x(", 0);
1060 for (i = 0; cnames && (i < ncnames); i++) {
1061 if (!cnames[i] || (cnames[i][0] ==
'\0')) {
1064 k = strlen(cnames[i]);
1065 if ((k > 7) && (strncasecmp(
"column_", cnames[i], 7) == 0)) {
1068 if (sscanf(cnames[i] + 7,
"%d%c", &k, &c) == 1) {
1073 for (i = 0; i < ncnames; i++) {
1075 if (!cnames || !cnames[i] || (cnames[i][0] ==
'\0')) {
1080 for (k = 0; k < ncnames; k++) {
1081 if ((k != i) && (colmap[k] == want)) {
1091 sprintf(colname,
"column_%d", colmap[i]);
1092 append(&schema, colname,
'"');
1093 }
else if (row1 > 0) {
1094 append(&schema, cnames[i],
'"');
1095 }
else if (row1 < 0) {
1096 append(&schema, cnames[i],
'"');
1098 char *type = nargv[i + 8];
1101 append(&schema, type, 0);
1105 char *type = cnames[i];
1107 append(&schema, cnames[i], 0);
1108 while (*type && !strchr(
" \t", *type)) {
1111 while (*type && strchr(
" \t", *type)) {
1116 if (i < ncnames - 1) {
1121 rc = sqlite3_declare_vtab(db, schema);
1122 if (rc != SQLITE_OK) {
1124 *errp = sqlite3_mprintf(
"table definition failed, error %d, "
1125 "schema '%s'", rc, schema);
1128 *vtabp = &vtab->
vtab;
1147 const char *
const *argv,
1148 sqlite3_vtab **vtabp,
char **errp)
1204 csv_cursor *cur = sqlite3_malloc(
sizeof (*cur));
1208 return SQLITE_ERROR;
1210 cur->
cursor.pVtab = vtab;
1226 sqlite3_free(cursor);
1259 const char *idxStr,
int argc, sqlite3_value **argv)
1317#if (SQLITE_VERSION_NUMBER > 3004000)
1326csv_vtab_rename(sqlite3_vtab *vtab,
const char *newname)
1357#if (SQLITE_VERSION_NUMBER > 3004000)
1393 int rc, i, k, ncnames, row1, convert = 0, useargs = 0, *colmap = 0;
1394 char *tname, *fname, *sql = 0, **cnames, *coltypes = 0;
1395 sqlite3 *db = (sqlite3 *) sqlite3_user_data(ctx);
1396 sqlite3_stmt *
stmt = 0;
1399 sqlite3_result_error(ctx,
"need at least 2 arguments", -1);
1402 tname = (
char *) sqlite3_value_text(argv[0]);
1404 sqlite3_result_error(ctx,
"table name is NULL", -1);
1407 fname = (
char *) sqlite3_value_text(argv[1]);
1409 sqlite3_result_error(ctx,
"file name is NULL", -1);
1413 (argc > 4) ? (
char *) sqlite3_value_text(argv[4]) : 0,
1414 (argc > 5) ? (
char *) sqlite3_value_text(argv[5]) : 0);
1416 sqlite3_result_error(ctx,
"unable to open input file", -1);
1419 sqlite3_finalize(
stmt);
1423 sqlite3_free(colmap);
1430 if (!csv->
sep && !csv->
quot) {
1436 row1 = sqlite3_value_int(argv[2]);
1441 if (csv->
ncols < 1) {
1442 sqlite3_result_error(ctx,
"unable to get column names", -1);
1449 }
else if (argc > 6) {
1456 if (csv->
ncols < 1) {
1457 sqlite3_result_error(ctx,
"unable to get column names", -1);
1466 convert = sqlite3_value_int(argv[3]);
1467 if (row1 && (convert & 4)) {
1472 append(&sql,
"PRAGMA table_info(", 0);
1473 append(&sql, tname,
'"');
1477 sqlite3_result_error(ctx,
"out of memory", -1);
1480 rc = sqlite3_prepare_v2(db, sql, -1, &
stmt, 0);
1482 if (rc != SQLITE_OK) {
1484 sqlite3_result_error(ctx,
"prepare failed", -1);
1489 rc = sqlite3_step(
stmt);
1490 while (rc == SQLITE_ROW) {
1492 rc = sqlite3_step(
stmt);
1494 if (rc != SQLITE_DONE) {
1496 sqlite3_result_error(ctx,
"select failed", -1);
1501 sqlite3_reset(
stmt);
1503 coltypes = sqlite3_malloc(ncnames);
1507 rc = sqlite3_step(
stmt);
1509 while (rc == SQLITE_ROW) {
1510 coltypes[i++] =
maptype((
char *) sqlite3_column_text(
stmt, 2));
1511 rc = sqlite3_step(
stmt);
1513 if (rc != SQLITE_DONE) {
1518 sqlite3_finalize(
stmt);
1520 colmap = (
int *) sqlite3_malloc(ncnames +
sizeof (
int) * ncnames);
1524 memset(colmap, 0,
sizeof (
int) * ncnames);
1525 coltypes = (
char *) (colmap + ncnames);
1526 append(&sql,
"CREATE TABLE ", 0);
1527 append(&sql, tname,
'"');
1529 for (i = 0; cnames && (i < ncnames); i++) {
1530 if (!cnames[i] || (cnames[i][0] ==
'\0')) {
1533 k = strlen(cnames[i]);
1534 if ((k > 7) && (strncasecmp(
"column_", cnames[i], 7) == 0)) {
1537 if (sscanf(cnames[i] + 7,
"%d%c", &k, &c) == 1) {
1542 for (i = 0; i < ncnames; i++) {
1546 coltypes[i] = SQLITE_TEXT;
1548 char *type = (
char *) sqlite3_value_text(argv[i + 6]);
1554 while (*type && !strchr(
" \t", *type)) {
1557 while (*type && strchr(
" \t", *type)) {
1561 }
else if (!cnames || !cnames[i]) {
1564 for (k = 0; k < ncnames; k++) {
1565 if ((k != i) && (colmap[k] == want)) {
1575 sprintf(colname,
"column_%d", colmap[i]);
1576 append(&sql, colname,
'"');
1577 }
else if (row1 > 0) {
1578 append(&sql, cnames[i],
'"');
1579 }
else if (row1 < 0) {
1580 append(&sql, cnames[i],
'"');
1582 char *type = (
char *) sqlite3_value_text(argv[i + 6]);
1591 if (i < ncnames - 1) {
1596 rc = sqlite3_prepare_v2(db, sql, -1, &
stmt, 0);
1597 if (rc != SQLITE_OK) {
1600 rc = sqlite3_step(
stmt);
1601 if ((rc != SQLITE_DONE) && (rc != SQLITE_OK)) {
1602 sqlite3_result_error(ctx,
"create table failed", -1);
1607 sqlite3_finalize(
stmt);
1610 append(&sql,
"INSERT INTO ", 0);
1611 append(&sql, tname,
'"');
1612 append(&sql,
" VALUES(", 0);
1613 for (i = 0; i < ncnames; i++) {
1614 append(&sql, (i < ncnames - 1) ?
"?," :
"?)", 0);
1616 rc = sqlite3_prepare_v2(db, sql, -1, &
stmt, 0);
1617 if (rc != SQLITE_OK) {
1624 for (i = 0; i < ncnames; i++) {
1628 if (rc != SQLITE_OK) {
1632 rc = sqlite3_step(
stmt);
1633 if ((rc != SQLITE_DONE) && (rc != SQLITE_OK)) {
1634 if ((rc != SQLITE_MISMATCH) && (rc != SQLITE_CONSTRAINT)) {
1636 sqlite3_result_error(ctx,
"insert failed", -1);
1642 sqlite3_reset(
stmt);
1644 sqlite3_result_int(ctx, row1);
1660 sqlite3_create_function(db,
"import_csv", -1, SQLITE_UTF8,
1662 return sqlite3_create_module(db,
"csvtable", &
csv_vtab_mod, 0);
1677 const sqlite3_api_routines *api)
1679 SQLITE_EXTENSION_INIT2(api);
static int csv_vtab_rowid(sqlite3_vtab_cursor *cursor, sqlite_int64 *rowidp)
Return current rowid of virtual table cursor.
static char * csv_coldata(csv_file *csv, int n)
Return nth column of current row in CSV file.
struct csv_guess_fmt csv_guess_fmt
static int csv_vtab_init(sqlite3 *db)
Module initializer creating SQLite functions and modules.
static void csv_close(csv_file *csv)
Close CSV file handle.
static int csv_guess(csv_file *csv)
Guess CSV layout of CSV file handle.
static int csv_ncols(csv_file *csv)
Return number of columns of current row in CSV file.
static void conv_names(char **names, int ncols)
Convert and collapse white space in column names to underscore.
static char * append(char **in, char const *append, char quote)
Append a string to dynamically allocated string buffer with optional quoting.
static const sqlite3_module csv_vtab_mod
SQLite module descriptor.
static int csv_getline(csv_file *csv, csv_guess_fmt *guess)
Read and process one line of CSV file handle.
static int csv_vtab_disconnect(sqlite3_vtab *vtab)
Disconnect virtual table.
static void append_free(char **in)
Free dynamically allocated string buffer.
static int csv_vtab_close(sqlite3_vtab_cursor *cursor)
Close virtual table cursor.
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.
int sqlite3_extension_init(sqlite3 *db, char **errmsg, const sqlite3_api_routines *api)
Initializer for SQLite extension load mechanism.
static int csv_vtab_destroy(sqlite3_vtab *vtab)
Destroy virtual table.
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_eof(csv_file *csv)
Test EOF on CSV file handle.
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 char * unquote(char const *in)
Strip off quotes given string.
static void csv_import_func(sqlite3_context *ctx, int argc, sqlite3_value **argv)
Import CSV file as table into database.
static int csv_vtab_open(sqlite3_vtab *vtab, sqlite3_vtab_cursor **cursorp)
Open virtual table and return cursor.
static int csv_vtab_next(sqlite3_vtab_cursor *cursor)
Retrieve next row from virtual table cursor.
static int csv_vtab_eof(sqlite3_vtab_cursor *cursor)
Return end of table state of virtual table cursor.
static int maptype(char const *type)
Map string to SQLite data type.
static long csv_tell(csv_file *csv)
Return current position of CSV file handle.
static long csv_seek(csv_file *csv, long pos)
Position CSV file handle.
static int csv_vtab_column(sqlite3_vtab_cursor *cursor, sqlite3_context *ctx, int n)
Return column data of virtual table.
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 void csv_rewind(csv_file *csv)
Rewind CSV file handle.
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 process_col(sqlite3_context *ctx, sqlite3_stmt *stmt, int idx, char *data, int type, int conv)
Process one column of the current row.
static int csv_vtab_bestindex(sqlite3_vtab *vtab, sqlite3_index_info *info)
Determines information for filter function according to constraints.
Structure to describe CSV virtual table cursor.
sqlite3_vtab_cursor cursor
SQLite virtual table cursor.
long pos
CSV file position.
Structure to implement CSV file handle.
int isdos
true, when DOS format detected
int ncols
number of columns
char * sep
column separator characters
char * quot
text quoting characters
long pos0
file position for rewind
char ** cols
column buffer
Info to guess CSV layout.
Structure to describe a CSV virtual table.
sqlite3_vtab vtab
SQLite virtual table.
csv_file * csv
CSV file handle.
char coltypes[1]
column types
Driver internal structure representing SQL statement (HSTMT).