22 #include <sqlite3ext.h> 23 static 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;
124 int nappend = append ? strlen(append) : 0;
133 len = nappend + actlen;
136 for (i = 0; i < nappend; i++) {
137 if (append[i] == quote) {
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++) {
164 if (append[i] == quote) {
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);
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)) {
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, ncnames, row1;
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]);
1002 row1 = strtol(nargv[4], 0, 10);
1007 if (csv->
ncols < 1) {
1009 *errp = sqlite3_mprintf(
"unable to get column names");
1016 }
else if (argc > 8) {
1018 cnames = (
char **) nargv + 8;
1022 if (csv->
ncols < 1) {
1024 *errp = sqlite3_mprintf(
"unable to get column names");
1031 vtab = sqlite3_malloc(
sizeof(
csv_vtab) + ncnames);
1034 *errp = sqlite3_mprintf(
"out of memory");
1037 memset(vtab, 0,
sizeof (*vtab));
1040 vtab->
convert = strtol(nargv[5], 0, 10);
1041 if (row1 && (vtab->
convert & 4)) {
1046 append(&schema,
"CREATE TABLE x(", 0);
1047 for (i = 0; i < ncnames; i++) {
1049 if (!cnames || !cnames[i]) {
1052 sprintf(colname,
"column_%d", i + 1);
1053 append(&schema, colname,
'"');
1054 }
else if (row1 > 0) {
1055 append(&schema, cnames[i],
'"');
1056 }
else if (row1 < 0) {
1057 append(&schema, cnames[i],
'"');
1059 char *type = nargv[i + 8];
1062 append(&schema, type, 0);
1066 char *type = cnames[i];
1068 append(&schema, cnames[i], 0);
1069 while (*type && !strchr(
" \t", *type)) {
1072 while (*type && strchr(
" \t", *type)) {
1077 if (i < ncnames - 1) {
1082 rc = sqlite3_declare_vtab(db, schema);
1083 if (rc != SQLITE_OK) {
1086 *errp = sqlite3_mprintf(
"table definition failed, error %d, " 1087 "schema '%s'", rc, schema);
1090 *vtabp = &vtab->
vtab;
1108 const char *
const *argv,
1109 sqlite3_vtab **vtabp,
char **errp)
1165 csv_cursor *cur = sqlite3_malloc(
sizeof(*cur));
1169 return SQLITE_ERROR;
1171 cur->
cursor.pVtab = vtab;
1187 sqlite3_free(cursor);
1220 const char *idxStr,
int argc, sqlite3_value **argv)
1278 #if (SQLITE_VERSION_NUMBER > 3004000) 1287 csv_vtab_rename(sqlite3_vtab *vtab,
const char *newname)
1318 #if (SQLITE_VERSION_NUMBER > 3004000) 1354 int rc, i, ncnames, row1, convert = 0, useargs = 0;
1355 char *tname, *fname, *sql = 0, **cnames, *coltypes = 0;
1356 sqlite3 *db = (sqlite3 *) sqlite3_user_data(ctx);
1357 sqlite3_stmt *
stmt = 0;
1360 sqlite3_result_error(ctx,
"need at least 2 arguments", -1);
1363 tname = (
char *) sqlite3_value_text(argv[0]);
1365 sqlite3_result_error(ctx,
"table name is NULL", -1);
1368 fname = (
char *) sqlite3_value_text(argv[1]);
1370 sqlite3_result_error(ctx,
"file name is NULL", -1);
1374 (argc > 4) ? (
char *) sqlite3_value_text(argv[4]) : 0,
1375 (argc > 5) ? (
char *) sqlite3_value_text(argv[5]) : 0);
1377 sqlite3_result_error(ctx,
"unable to open input file", -1);
1380 sqlite3_finalize(stmt);
1384 sqlite3_free(coltypes);
1391 if (!csv->
sep && !csv->
quot) {
1397 row1 = sqlite3_value_int(argv[2]);
1402 if (csv->
ncols < 1) {
1403 sqlite3_result_error(ctx,
"unable to get column names", -1);
1410 }
else if (argc > 6) {
1417 if (csv->
ncols < 1) {
1418 sqlite3_result_error(ctx,
"unable to get column names", -1);
1427 convert = sqlite3_value_int(argv[3]);
1428 if (row1 && (convert & 4)) {
1433 append(&sql,
"PRAGMA table_info(", 0);
1434 append(&sql, tname,
'"');
1438 sqlite3_result_error(ctx,
"out of memory", -1);
1441 rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
1443 if (rc != SQLITE_OK) {
1445 sqlite3_result_error(ctx,
"prepare failed", -1);
1450 rc = sqlite3_step(stmt);
1451 while (rc == SQLITE_ROW) {
1453 rc = sqlite3_step(stmt);
1455 if (rc != SQLITE_DONE) {
1457 sqlite3_result_error(ctx,
"select failed", -1);
1462 sqlite3_reset(stmt);
1464 coltypes = sqlite3_malloc(ncnames);
1468 rc = sqlite3_step(stmt);
1470 while (rc == SQLITE_ROW) {
1471 coltypes[i++] =
maptype((
char *) sqlite3_column_text(stmt, 2));
1472 rc = sqlite3_step(stmt);
1474 if (rc != SQLITE_DONE) {
1479 sqlite3_finalize(stmt);
1481 coltypes = sqlite3_malloc(ncnames);
1485 append(&sql,
"CREATE TABLE ", 0);
1486 append(&sql, tname,
'"');
1488 for (i = 0; i < ncnames; i++) {
1491 coltypes[i] = SQLITE_TEXT;
1493 char *type = (
char *) sqlite3_value_text(argv[i + 6]);
1499 while (*type && !strchr(
" \t", *type)) {
1502 while (*type && strchr(
" \t", *type)) {
1506 }
else if (!cnames || !cnames[i]) {
1508 sprintf(colname,
"column_%d", i + 1);
1509 append(&sql, colname,
'"');
1510 }
else if (row1 > 0) {
1511 append(&sql, cnames[i],
'"');
1512 }
else if (row1 < 0) {
1513 append(&sql, cnames[i],
'"');
1515 char *type = (
char *) sqlite3_value_text(argv[i + 6]);
1524 if (i < ncnames - 1) {
1529 rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
1530 if (rc != SQLITE_OK) {
1533 rc = sqlite3_step(stmt);
1534 if ((rc != SQLITE_DONE) && (rc != SQLITE_OK)) {
1535 sqlite3_result_error(ctx,
"create table failed", -1);
1540 sqlite3_finalize(stmt);
1543 append(&sql,
"INSERT INTO ", 0);
1544 append(&sql, tname,
'"');
1545 append(&sql,
" VALUES(", 0);
1546 for (i = 0; i < ncnames; i++) {
1547 append(&sql, (i < ncnames - 1) ?
"?," :
"?)", 0);
1549 rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
1550 if (rc != SQLITE_OK) {
1557 for (i = 0; i < ncnames; i++) {
1560 rc =
process_col(0, stmt, i + 1, data, coltypes[i], convert);
1561 if (rc != SQLITE_OK) {
1565 rc = sqlite3_step(stmt);
1566 if ((rc != SQLITE_DONE) && (rc != SQLITE_OK)) {
1567 if ((rc != SQLITE_MISMATCH) && (rc != SQLITE_CONSTRAINT)) {
1569 sqlite3_result_error(ctx,
"insert failed", -1);
1575 sqlite3_reset(stmt);
1577 sqlite3_result_int(ctx, row1);
1593 sqlite3_create_function(db,
"import_csv", -1, SQLITE_UTF8,
1595 return sqlite3_create_module(db,
"csvtable", &
csv_vtab_mod, 0);
1610 const sqlite3_api_routines *api)
1612 SQLITE_EXTENSION_INIT2(api);
static int csv_eof(csv_file *csv)
Test EOF on CSV file handle.
static char * append(char **in, char const *append, char quote)
Append a string to dynamically allocated string buffer with optional quoting.
static int csv_vtab_filter(sqlite3_vtab_cursor *cursor, int idxNum, const char *idxStr, int argc, sqlite3_value **argv)
Filter function for virtual table.
int sqlite3_extension_init(sqlite3 *db, char **errmsg, const sqlite3_api_routines *api)
Initializer for SQLite extension load mechanism.
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.
csv_file * csv
CSV file handle.
static int csv_guess(csv_file *csv)
Guess CSV layout of CSV file handle.
static int csv_getline(csv_file *csv, csv_guess_fmt *guess)
Read and process one line of CSV file handle.
Driver internal structure representing SQL statement (HSTMT).
static void csv_rewind(csv_file *csv)
Rewind CSV file handle.
static void csv_import_func(sqlite3_context *ctx, int argc, sqlite3_value **argv)
Import CSV file as table into database.
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_destroy(sqlite3_vtab *vtab)
Destroy virtual table.
char * sep
column separator characters
char * quot
text quoting characters
static long csv_seek(csv_file *csv, long pos)
Position CSV file handle.
static char * unquote(char const *in)
Strip off quotes given string.
Info to guess CSV layout.
static char * csv_coldata(csv_file *csv, int n)
Return nth column of current row in CSV file.
Structure to implement CSV file handle.
static int csv_vtab_eof(sqlite3_vtab_cursor *cursor)
Return end of table state of virtual table cursor.
static const sqlite3_module csv_vtab_mod
SQLite module descriptor.
long pos0
file position for rewind
static int csv_ncols(csv_file *csv)
Return number of columns of current row in CSV file.
static csv_file * csv_open(const char *filename, const char *sep, const char *quot)
Open CSV file for reading and return handle to it.
int ncols
number of columns
long pos
CSV file position.
static int maptype(char const *type)
Map string to SQLite data type.
sqlite3_vtab_cursor cursor
SQLite virtual table cursor.
static int csv_vtab_open(sqlite3_vtab *vtab, sqlite3_vtab_cursor **cursorp)
Open virtual table and return cursor.
char coltypes[1]
column types
char ** cols
column buffer
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_close(sqlite3_vtab_cursor *cursor)
Close virtual table cursor.
static int csv_vtab_rowid(sqlite3_vtab_cursor *cursor, sqlite_int64 *rowidp)
Return current rowid of virtual table cursor.
int isdos
true, when DOS format detected
static int csv_vtab_column(sqlite3_vtab_cursor *cursor, sqlite3_context *ctx, int n)
Return column data of virtual table.
Structure to describe a CSV virtual table.
static long csv_tell(csv_file *csv)
Return current position of CSV file handle.
Structure to describe CSV virtual table cursor.
static void append_free(char **in)
Free dynamically allocated string buffer.
struct csv_guess_fmt csv_guess_fmt
static int csv_vtab_disconnect(sqlite3_vtab *vtab)
Disconnect virtual table.
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 csv_vtab_bestindex(sqlite3_vtab *vtab, sqlite3_index_info *info)
Determines information for filter function according to constraints.
static int csv_vtab_next(sqlite3_vtab_cursor *cursor)
Retrieve next row from virtual table cursor.
static void conv_names(char **names, int ncols)
Convert and collapse white space in column names to underscore.
sqlite3_vtab vtab
SQLite virtual table.