---

csvtable.c

Go to the documentation of this file.

#ifdef STANDALONE
#include <sqlite3.h>
#else
#include <sqlite3ext.h>
static SQLITE_EXTENSION_INIT1
#endif

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <limits.h>

#ifdef _WIN32
#include <windows.h>
#define strcasecmp  _stricmp
#define strncasecmp _strnicmp
#endif

typedef struct csv_file {
    FILE *f;            
    char *sep;          
    char *quot;         
    int isdos;          
    int maxl;           
    char *line;         
    long pos0;          
    int maxc;           
    int ncols;          
    char **cols;        
} csv_file;

typedef struct csv_guess_fmt {
    int nlines;
    int hist[256];
} csv_guess_fmt;

typedef struct csv_vtab {
    sqlite3_vtab vtab;  
    csv_file *csv;      
    int convert;        
    char coltypes[1];   
} csv_vtab;

typedef struct {
    sqlite3_vtab_cursor cursor;         
    long pos;                           
} csv_cursor;

static void
append_free(char **in)
{
    long *p = (long *) *in;

    if (p) {
        p -= 2;
        sqlite3_free(p);
        *in = 0;
    }
}

static char *
append(char **in, char const *append, char quote)
{
    long *p = (long *) *in;
    long len, maxlen, actlen;
    int i;
    char *pp;
    int nappend = append ? strlen(append) : 0;

    if (p) {
        p -= 2;
        maxlen = p[0];
        actlen = p[1];
    } else {
        maxlen = actlen = 0;
    }
    len = nappend + actlen;
    if (quote) {
        len += 2;
        for (i = 0; i < nappend; i++) {
            if (append[i] == quote) {
                len++;
            }
        }
    } else if (!nappend) {
        return *in;
    }
    if (len >= maxlen - 1) {
        long *q;

        maxlen = (len + 0x03ff) & (~0x3ff);
        q = (long *) sqlite3_realloc(p, maxlen + 1 + 2 * sizeof (long));
        if (!q) {
            return 0;
        }
        if (!p) {
            q[1] = 0;
        }
        p = q;
        p[0] = maxlen;
        *in = (char *) (p + 2);
    }
    pp = *in + actlen;
    if (quote) {
        *pp++ = quote;
        for (i = 0; i < nappend; i++) {
            *pp++ = append[i];
            if (append[i] == quote) {
                *pp++ = quote;
            }
        }
        *pp++ = quote;
        *pp = '\0';
    } else {
        if (nappend) {
            memcpy(pp, append, nappend);
            pp += nappend;
            *pp = '\0';
        }
    }
    p[1] = pp - *in;
    return *in;
}

static char *
unquote(char const *in)
{
    char c, *ret;
    int i;

    ret = sqlite3_malloc(strlen(in) + 1);
    if (ret) {
        c = in[0];
        if ((c == '"') || (c == '\'')) {
            i = strlen(in + 1);
            if ((i > 0) && (in[i] == c)) {
                strcpy(ret, in + 1);
                ret[i - 1] = '\0';
                return ret;
            }
        }
        strcpy(ret, in);
    }
    return ret;
}

static int
maptype(char const *type)
{
    int typelen = type ? strlen(type) : 0;

    if ((typelen >= 3) &&
        (strncasecmp(type, "integer", 7) == 0)) {
        return SQLITE_INTEGER;
    }
    if ((typelen >= 6) &&
        (strncasecmp(type, "double", 6) == 0)) {
        return SQLITE_FLOAT;
    }
    if ((typelen >= 5) &&
        (strncasecmp(type, "float", 5) == 0)) {
        return SQLITE_FLOAT;
    }
    if ((typelen >= 4) &&
        (strncasecmp(type, "real", 4) == 0)) {
        return SQLITE_FLOAT;
    }
    return SQLITE_TEXT;
}

static void
conv_names(char **names, int ncols)
{
    int i;
    char *p, *q;
    static const char ws[] = "\n\t\r\b\v ";

    if (!names || ncols <= 0) {
        return;
    }
    for (i = 0; i < ncols; i++) {
        p = names[i];

        while (*p) {
            if (strchr(ws, *p)) {
                *p++ = '_';
                q = p;
                while (*q && strchr(ws, *q)) {
                    ++q;
                }
                if (*q && q > p) {
                    strcpy(p, q);
                }
                continue;
            }
            ++p;
        }
    }
}

static void
result_or_bind(sqlite3_context *ctx, sqlite3_stmt *stmt, int idx,
               char *data, int len, int type)
{
    char *endp;

    if (!data) {
        if (ctx) {
            sqlite3_result_null(ctx);
        } else {
            sqlite3_bind_null(stmt, idx);
        }
        return;
    }
    if (type == SQLITE_INTEGER) {
        sqlite_int64 val;
#if defined(_WIN32) || defined(_WIN64)
        char endc;

        if (sscanf(data, "%I64d%c", &val, &endc) == 1) {
            if (ctx) {
                sqlite3_result_int64(ctx, val);
            } else {
                sqlite3_bind_int64(stmt, idx, val);
            }
            return;
        }
#else
        endp = 0;
#ifdef __osf__
        val = strtol(data, &endp, 0);
#else
        val = strtoll(data, &endp, 0);
#endif
        if (endp && (endp != data) && !*endp) {
            if (ctx) {
                sqlite3_result_int64(ctx, val);
            } else {
                sqlite3_bind_int64(stmt, idx, val);
            }
            return;
        }
#endif
    } else if (type == SQLITE_FLOAT) {
        double val;

        endp = 0;
        val = strtod(data, &endp);
        if (endp && (endp != data) && !*endp) {
            if (ctx) {
                sqlite3_result_double(ctx, val);
            } else {
                sqlite3_bind_double(stmt, idx, val);
            }
            return;
        }
    }
    if (ctx) {
        sqlite3_result_text(ctx, data, len, SQLITE_TRANSIENT);
    } else {
        sqlite3_bind_text(stmt, idx, data, len, SQLITE_TRANSIENT);
    }
}

static int
process_col(sqlite3_context *ctx, sqlite3_stmt *stmt, int idx,
            char *data, int type, int conv)
{
    char c, *p;
    const char flchars[] = "Ee+-.,0123456789";

    if (!data) {
        goto putdata;
    }

    /*
     * Floating point number test,
     * converts single comma to dot.
     */
    c = data[0];
    if ((c != '\0') && strchr(flchars + 2, c)) {
        p = data + 1;
        while (*p && strchr(flchars, *p)) {
            ++p;
        }
        if (*p == '\0') {
            char *first = 0;
            int n = 0;

            p = data;
            while (p) {
                p = strchr(p, ',');
                if (!p) {
                    break;
                }
                if (++n == 1) {
                    first = p;
                }
                ++p;
            }
            if (first) {
                *first = '.';
                goto putdata;
            }
        }
    }
    if (conv) {
        char *utf = sqlite3_malloc(strlen(data) * 2 + 2);

        if (utf) {
            p = utf;
            while ((c = *data) != '\0') {
                if (((conv & 10) == 10) && (c == '\\')) {
                    if (data[1] == 'q') {
                        *p++ = '\'';
                        data += 2;
                        continue;
                    }
                }
                if ((conv & 2) && (c == '\\')) {
                    char c2 = data[1];

                    switch (c2) {
                    case '\0':
                        goto convdone;
                    case 'n':
                        *p = '\n';
                        break;
                    case 't':
                        *p = '\t';
                        break;
                    case 'r':
                        *p = '\r';
                        break;
                    case 'f':
                        *p = '\f';
                        break;
                    case 'v':
                        *p = '\v';
                        break;
                    case 'b':
                        *p = '\b';
                        break;
                    case 'a':
                        *p = '\a';
                        break;
                    case '?':
                        *p = '\?';
                        break;
                    case '\'':
                        *p = '\'';
                        break;
                    case '"':
                        *p = '\"';
                        break;
                    case '\\':
                        *p = '\\';
                        break;
                    default:
                        *p++ = c;
                        *p = c2;
                        break;
                    }
                    p++;
                    data += 2;
                    continue;
                }
                if ((conv & 1) && (c & 0x80)) {
                    *p++ = 0xc0 | ((c >> 6) & 0x1f);
                    *p++ = 0x80 | (c & 0x3f);
                } else {
                    *p++ = c;
                }
                data++;
            }
convdone:
            *p = '\0';
            result_or_bind(ctx, stmt, idx, utf, p - utf, type);
            sqlite3_free(utf);
            return SQLITE_OK;
        } else {
            if (ctx) {
                sqlite3_result_error(ctx, "out of memory", -1);
            }
            return SQLITE_NOMEM;
        }
    }
putdata:
    result_or_bind(ctx, stmt, idx, data, -1, type);
    return SQLITE_OK;
}

static csv_file *
csv_open(const char *filename, const char *sep, const char *quot)
{
    FILE *f;
    csv_file *csv;

#ifdef _WIN32
    f = fopen(filename, "rb");
#else
    f = fopen(filename, "r");
#endif
    if (!f) {
        return 0;
    }
    csv = sqlite3_malloc(sizeof (csv_file));
    if (!csv) {
error0:
        fclose(f);
        return 0;
    }
    csv->f = f;
    if (sep && sep[0]) {
        csv->sep = sqlite3_malloc(strlen(sep) + 1);
        if (!csv->sep) {
error1:
            sqlite3_free(csv);
            goto error0;
        }
        strcpy(csv->sep, sep);
    } else {
        csv->sep = 0;
    }
    if (quot && quot[0]) {
        csv->quot = sqlite3_malloc(strlen(quot) + 1);
        if (!csv->quot) {
            if (csv->sep) {
                sqlite3_free(csv->sep);
            }
            goto error1;
        }
        strcpy(csv->quot, quot);
    } else {
        csv->quot = 0;
    }
    csv->isdos = 0;
    csv->maxl = 0;
    csv->line = 0;
    csv->pos0 = 0;
    csv->maxc = 0;
    csv->ncols = 0;
    csv->cols = 0;
    return csv;
}

static void
csv_close(csv_file *csv)
{
    if (csv) {
        if (csv->sep) {
            sqlite3_free(csv->sep);
        }
        if (csv->quot) {
            sqlite3_free(csv->quot);
        }
        if (csv->line) {
            sqlite3_free(csv->line);
        }
        if (csv->cols) {
            sqlite3_free(csv->cols);
        }
        if (csv->f) {
            fclose(csv->f);
        }
        sqlite3_free(csv);
    }
}

static int
csv_eof(csv_file *csv)
{
    if (csv && csv->f) {
        return feof(csv->f);
    }
    return 1;
}

static long
csv_seek(csv_file *csv, long pos)
{
    if (csv && csv->f) {
        return fseek(csv->f, pos, SEEK_SET);
    }
    return EOF;
}

static void
csv_rewind(csv_file *csv)
{
    if (csv && csv->f) {
        csv_seek(csv, csv->pos0);
    }
}

static long
csv_tell(csv_file *csv)
{
    if (csv && csv->f) {
        return ftell(csv->f);
    }
    return EOF;
}

static int
csv_getline(csv_file *csv, csv_guess_fmt *guess)
{
    int i, index = 0, inq = 0, c, col;
    char *p, *sep;

    if (!csv || !csv->f) {
        return EOF;
    }
    while (1) {
        c = fgetc(csv->f);
        if (c == EOF) {
            if (index > 0) {
                break;
            }
            return EOF;
        }
        if (c == '\0') {
            continue;
        }
        if (c == '\r') {
            int c2 = fgetc(csv->f);
            c = '\n';

            if (c2 == '\n') {
                csv->isdos = 1;
            } else if (c2 != EOF) {
                ungetc(c2, csv->f);
            }
        }
        /* check for DOS EOF (Ctrl-Z) */
        if (csv->isdos && (c == '\032')) {
            int c2 = fgetc(csv->f);

            if (c2 == EOF) {
                if (index > 0) {
                    break;
                }
                return EOF;
            }
            ungetc(c2, csv->f);
        }
        if (index >= csv->maxl - 1) {
            int n = csv->maxl * 2;
            char *line;

            if (n <= 0) {
                n = 4096;
            }
            line = sqlite3_malloc(n);
            if (!line) {
                return EOF;
            }
            if (csv->line) {
                memcpy(line, csv->line, index);
                sqlite3_free(csv->line);
            }
            csv->maxl = n;
            csv->line = line;
        }
        csv->line[index++] = c;
        if (csv->quot && (p = strchr(csv->quot, c))) {
            if (inq) {
                if (*p == inq) {
                    inq = 0;
                }
            } else {
                inq = *p;
            }
        }
        if (!inq && (c == '\n')) {
            break;
        }
    }
    if (guess) {
        for (i = 0; i < index; i++) {
            guess->hist[csv->line[i] & 0xFF] += 1;
        }
        guess->nlines += 1;
        csv->ncols = 0;
        return 0;
    }

    for (i = index - 1; i >= 0; i--) {
        if (csv->line[i] != '\n') {
            break;
        }
    }
    index = i + 1;
    csv->line[index] = '\0';
    i = inq = col = 0;
    sep = csv->sep ? csv->sep : ";";
    if (!csv->cols) {
        int n = 128;

        csv->cols = sqlite3_malloc(sizeof (char *) * n);
        if (!csv->cols) {
            return EOF;
        }
        csv->maxc = n;
    }
    csv->cols[col++] = csv->line;
    while (i < index) {
        if (csv->quot && (p = strchr(csv->quot, csv->line[i]))) {
            if (inq) {
                if (*p == inq) {
                    inq = 0;
                }
            } else {
                inq = *p;
            }
        }
        if (!inq && (p = strchr(sep, csv->line[i]))) {
            p = csv->line + i;
            *p = '\0';
            if (col >= csv->maxc) {
                int n = csv->maxc * 2;
                char **cols;

                cols = sqlite3_realloc(csv->cols, sizeof (char *) * n);
                if (!cols) {
                    return EOF;
                }
                csv->cols = cols;
                csv->maxc = n;
            }
            csv->cols[col++] = p + 1;
        }
        ++i;
    }
    csv->ncols = col;

    /* strip off quotes */
    if (csv->quot) {
        for (i = 0; i < col; i++) {
            if (*csv->cols[i]) {
                p = strchr(csv->quot, *csv->cols[i]);
                if (p) {
                    char *src, *dst;

                    c = *p;
                    csv->cols[i] += 1;
                    sep = csv->cols[i] + strlen(csv->cols[i]) - 1;
                    if ((sep >= csv->cols[i]) && (*sep == c)) {
                        *sep = '\0';
                    }
                    /* collapse quote escape sequences */
                    src = csv->cols[i];
                    dst = 0;
                    while (*src) {
                        if ((*src == c) && (src[1] == c)) {
                            if (!dst) {
                                dst = src;
                            }
                            src++;
                            while (*src) {
                                *dst++ = *src++;
                                if (*src == c) {
                                    --src;
                                    break;
                                }
                            }
                        }
                        ++src;
                    }
                    if (dst) {
                        *dst++ = '\0';
                    }
                }
            }
        }
    }
    return col;
}

static int
csv_ncols(csv_file *csv)
{
    if (csv && csv->cols) {
        return csv->ncols;
    }
    return 0;
}

static char *
csv_coldata(csv_file *csv, int n)
{
    if (csv && csv->cols && (n >= 0) && (n < csv->ncols)) {
        return csv->cols[n];
    }
    return 0;
}

static int
csv_guess(csv_file *csv)
{
    csv_guess_fmt guess;
    int i, n;
    char *p, sep[32], quot[4];
    const struct {
        int c;
        int min;
    } sep_test[] = {
        { ',', 2 },
        { ';', 2 },
        { '\t', 2 },
        { ' ', 4 },
        { '|', 2 }
    };

    if (!csv) {
        return EOF;
    }
    memset(&guess, 0, sizeof (guess));
    csv->pos0 = 0;
    csv_rewind(csv);
    for (i = n = 0; i < 10; i++) {
        n = csv_getline(csv, &guess);
        if (n == EOF) {
            break;
        }
    }
    csv_rewind(csv);
    if (n && !i) {
        return EOF;
    }
    p = quot;
    n = '"';
    if (guess.hist[n] > 1) {
        *p++ = n;
    }
    n = '\'';
    if (guess.hist[n] > 1) {
        *p++ = n;
    }
    *p = '\0';
    p = sep;
    for (i = 0; i < sizeof (sep_test) / sizeof (sep_test[0]); i++) {
        if (guess.hist[sep_test[i].c] > sep_test[i].min * guess.nlines) {
            *p++ = sep_test[i].c;
        }
    }
    *p = '\0';
    if (quot[0]) {
        p = sqlite3_malloc(strlen(quot) + 1);
        if (p) {
            strcpy(p, quot);
            if (csv->quot) {
                sqlite3_free(csv->quot);
            }
            csv->quot = p;
        } else {
            return EOF;
        }
    }
    if (sep[0]) {
        p = sqlite3_malloc(strlen(sep) + 1);
        if (p) {
            strcpy(p, sep);
            if (csv->sep) {
                sqlite3_free(csv->sep);
            }
            csv->sep = p;
        } else {
            return EOF;
        }
    }
    return 0;
}

static int
csv_vtab_connect(sqlite3* db, void *aux, int argc, const char * const *argv,
                 sqlite3_vtab **vtabp, char **errp)
{
    csv_file *csv;
    int rc = SQLITE_ERROR, i, k, ncnames, row1, *colmap = 0;
    char **cnames, *schema = 0, **nargv;
    csv_vtab *vtab = 0;

    if (argc < 4) {
        *errp = sqlite3_mprintf("input file name missing");
        return SQLITE_ERROR;
    }
    nargv = sqlite3_malloc(sizeof (char *) * argc);
    memset(nargv, 0, sizeof (char *) * argc);
    for (i = 3; i < argc; i++) {
        nargv[i] = unquote(argv[i]);
    }
    csv = csv_open(nargv[3], (argc > 6) ? nargv[6] : 0,
                   (argc > 7) ? nargv[7] : 0);
    if (!csv) {
        *errp = sqlite3_mprintf("unable to open input file");
cleanup:
        append_free(&schema);
        for (i = 3; i < argc; i++) {
            if (nargv[i]) {
                sqlite3_free(nargv[i]);
            }
        }
        if (vtab) {
            sqlite3_free(vtab);
        }
        if (colmap) {
            sqlite3_free(colmap);
        }
        return rc;
    }
    if (!csv->sep && !csv->quot) {
        csv_guess(csv);
    }
    csv->pos0 = 0;
    row1 = 0;
    if (argc > 4) {
        row1 = strtol(nargv[4], 0, 10);
    }
    if (row1) {
        /* use column names from 1st row */
        csv_getline(csv, 0);
        if (csv->ncols < 1) {
            csv_close(csv);
            *errp = sqlite3_mprintf("unable to get column names");
            goto cleanup;
        }
        csv->pos0 = csv_tell(csv);
        csv_rewind(csv);
        ncnames = csv_ncols(csv);
        cnames = csv->cols;
    } else if (argc > 8) {
        ncnames = argc - 8;
        cnames = (char **) nargv + 8;
    } else {
        /* use number of columns from 1st row */
        csv_getline(csv, 0);
        if (csv->ncols < 1) {
            csv_close(csv);
            *errp = sqlite3_mprintf("unable to get column names");
            goto cleanup;
        }
        csv_rewind(csv);
        ncnames = csv_ncols(csv);
        cnames = 0;
    }
    colmap = sqlite3_malloc(sizeof (int) * ncnames);
    if (!colmap) {
        csv_close(csv);
        *errp = sqlite3_mprintf("out of memory");
        goto cleanup;
    }
    memset(colmap, 0, sizeof (int) * ncnames);
    vtab = sqlite3_malloc(sizeof (csv_vtab) + ncnames);
    if (!vtab) {
        csv_close(csv);
        *errp = sqlite3_mprintf("out of memory");
        goto cleanup;
    }
    memset(vtab, 0, sizeof (*vtab));
    vtab->convert = 0;
    if (argc > 5) {
        vtab->convert = strtol(nargv[5], 0, 10);
        if (row1 && (vtab->convert & 4)) {
            conv_names(cnames, ncnames);
        }
    }
    vtab->csv = csv;
    append(&schema, "CREATE TABLE x(", 0);
    for (i = 0; cnames && (i < ncnames); i++) {
        if (!cnames[i] || (cnames[i][0] == '\0')) {
            continue;
        }
        k = strlen(cnames[i]);
        if ((k > 7) && (strncasecmp("column_", cnames[i], 7) == 0)) {
            char c;

            if (sscanf(cnames[i] + 7, "%d%c", &k, &c) == 1) {
                colmap[i] = k;
            }
        }
    }
    for (i = 0; i < ncnames; i++) {
        vtab->coltypes[i] = SQLITE_TEXT;
        if (!cnames || !cnames[i] || (cnames[i][0] == '\0')) {
            int want = i + 1;
            char colname[64];

            while (1) {
                for (k = 0; k < ncnames; k++) {
                    if ((k != i) && (colmap[k] == want)) {
                        want++;
                        break;
                    }
                }
                if (k >= ncnames) {
                    colmap[i] = want;
                    break;
                }
            }
            sprintf(colname, "column_%d", colmap[i]);
            append(&schema, colname, '"');
        } else if (row1 > 0) {
            append(&schema, cnames[i], '"');
        } else if (row1 < 0) {
            append(&schema, cnames[i], '"');
            if (i + 8 < argc) {
                char *type = nargv[i + 8];

                append(&schema, " ", 0);
                append(&schema, type, 0);
                vtab->coltypes[i] = maptype(type);
            }
        } else {
            char *type = cnames[i];

            append(&schema, cnames[i], 0);
            while (*type && !strchr(" \t", *type)) {
                type++;
            }
            while (*type && strchr(" \t", *type)) {
                type++;
            }
            vtab->coltypes[i] = maptype(type);
        }
        if (i < ncnames - 1) {
            append(&schema, ",", 0);
        }
    }
    append(&schema, ")", 0);
    rc = sqlite3_declare_vtab(db, schema);
    if (rc != SQLITE_OK) {
        csv_close(csv);
        *errp = sqlite3_mprintf("table definition failed, error %d, "
                                "schema '%s'", rc, schema);
        goto cleanup;
    }
    *vtabp = &vtab->vtab;
    *errp = 0;
    vtab = 0;
    goto cleanup;
}

static int
csv_vtab_create(sqlite3* db, void *aux, int argc,
           const char *const *argv,
           sqlite3_vtab **vtabp, char **errp)
{
    return csv_vtab_connect(db, aux, argc, argv, vtabp, errp);
}

static int
csv_vtab_disconnect(sqlite3_vtab *vtab)
{
    csv_vtab *tab = (csv_vtab *) vtab;

    csv_close(tab->csv);
    sqlite3_free(tab);
    return SQLITE_OK;
}

static int
csv_vtab_destroy(sqlite3_vtab *vtab)
{
    return csv_vtab_disconnect(vtab);
}

static int
csv_vtab_bestindex(sqlite3_vtab *vtab, sqlite3_index_info *info)
{
    return SQLITE_OK;
}

static int
csv_vtab_open(sqlite3_vtab *vtab, sqlite3_vtab_cursor **cursorp)
{
    csv_cursor *cur = sqlite3_malloc(sizeof (*cur));
    csv_vtab *tab = (csv_vtab *) vtab;

    if (!cur) {
        return SQLITE_ERROR;
    }
    cur->cursor.pVtab = vtab;
    csv_rewind(tab->csv);
    cur->pos = csv_tell(tab->csv);
    *cursorp = &cur->cursor;
    return SQLITE_OK;
}

static int
csv_vtab_close(sqlite3_vtab_cursor *cursor)
{
    sqlite3_free(cursor);
    return SQLITE_OK;
}

static int
csv_vtab_next(sqlite3_vtab_cursor *cursor)
{
    csv_cursor *cur = (csv_cursor *) cursor;
    csv_vtab *tab = (csv_vtab *) cur->cursor.pVtab;

    cur->pos = csv_tell(tab->csv);
    csv_getline(tab->csv, 0);
    return SQLITE_OK;
}

static int
csv_vtab_filter(sqlite3_vtab_cursor *cursor, int idxNum,
                const char *idxStr, int argc, sqlite3_value **argv)
{
    csv_cursor *cur = (csv_cursor *) cursor;
    csv_vtab *tab = (csv_vtab *) cur->cursor.pVtab;

    csv_rewind(tab->csv);
    return csv_vtab_next(cursor);
}

static int
csv_vtab_eof(sqlite3_vtab_cursor *cursor)
{
    csv_cursor *cur = (csv_cursor *) cursor;
    csv_vtab *tab = (csv_vtab *) cur->cursor.pVtab;

    return csv_eof(tab->csv);
}

static int
csv_vtab_column(sqlite3_vtab_cursor *cursor, sqlite3_context *ctx, int n)
{
    csv_cursor *cur = (csv_cursor *) cursor;
    csv_vtab *tab = (csv_vtab *) cur->cursor.pVtab;
    char *data = csv_coldata(tab->csv, n);

    return process_col(ctx, 0, 0, data, tab->coltypes[n], tab->convert);
}

static int
csv_vtab_rowid(sqlite3_vtab_cursor *cursor, sqlite_int64 *rowidp)
{
    csv_cursor *cur = (csv_cursor *) cursor;

    *rowidp = cur->pos;
    return SQLITE_OK;
}

#if (SQLITE_VERSION_NUMBER > 3004000)

static int
csv_vtab_rename(sqlite3_vtab *vtab, const char *newname)
{
    return SQLITE_OK;
}

#endif

static const sqlite3_module csv_vtab_mod = {
    1,                   /* iVersion */
    csv_vtab_create,     /* xCreate */
    csv_vtab_connect,    /* xConnect */
    csv_vtab_bestindex,  /* xBestIndex */
    csv_vtab_disconnect, /* xDisconnect */
    csv_vtab_destroy,    /* xDestroy */
    csv_vtab_open,       /* xOpen */
    csv_vtab_close,      /* xClose */
    csv_vtab_filter,     /* xFilter */
    csv_vtab_next,       /* xNext */
    csv_vtab_eof,        /* xEof */
    csv_vtab_column,     /* xColumn */
    csv_vtab_rowid,      /* xRowid */
    0,                   /* xUpdate */
    0,                   /* xBegin */
    0,                   /* xSync */
    0,                   /* xCommit */
    0,                   /* xRollback */
    0,                   /* xFindFunction */
#if (SQLITE_VERSION_NUMBER > 3004000)
    csv_vtab_rename,     /* xRename */
#endif
};

static void
csv_import_func(sqlite3_context *ctx, int argc, sqlite3_value **argv)
{
    csv_file *csv;
    int rc, i, k, ncnames, row1, convert = 0, useargs = 0, *colmap = 0;
    char *tname, *fname, *sql = 0, **cnames, *coltypes = 0;
    sqlite3 *db = (sqlite3 *) sqlite3_user_data(ctx);
    sqlite3_stmt *stmt = 0;

    if (argc < 2) {
        sqlite3_result_error(ctx, "need at least 2 arguments", -1);
        return;
    }
    tname = (char *) sqlite3_value_text(argv[0]);
    if (!tname) {
        sqlite3_result_error(ctx, "table name is NULL", -1);
        return;
    }
    fname = (char *) sqlite3_value_text(argv[1]);
    if (!fname) {
        sqlite3_result_error(ctx, "file name is NULL", -1);
        return;
    }
    csv = csv_open(fname,
                   (argc > 4) ? (char *) sqlite3_value_text(argv[4]) : 0,
                   (argc > 5) ? (char *) sqlite3_value_text(argv[5]) : 0);
    if (!csv) {
        sqlite3_result_error(ctx, "unable to open input file", -1);
cleanup:
        if (stmt) {
            sqlite3_finalize(stmt);
        }
        append_free(&sql);
        if (colmap) {
            sqlite3_free(colmap);
        }
        if (csv) {
            csv_close(csv);
        }
        return;
    }
    if (!csv->sep && !csv->quot) {
        csv_guess(csv);
    }
    csv->pos0 = 0;
    row1 = 0;
    if (argc > 2) {
        row1 = sqlite3_value_int(argv[2]);
    }
    if (row1) {
        /* use column names from 1st row */
        csv_getline(csv, 0);
        if (csv->ncols < 1) {
            sqlite3_result_error(ctx, "unable to get column names", -1);
            goto cleanup;
        }
        csv->pos0 = csv_tell(csv);
        csv_rewind(csv);
        ncnames = csv_ncols(csv);
        cnames = csv->cols;
    } else if (argc > 6) {
        ncnames = argc - 6;
        cnames = 0;
        useargs = 1;
    } else {
        /* use number of columns from 1st row */
        csv_getline(csv, 0);
        if (csv->ncols < 1) {
            sqlite3_result_error(ctx, "unable to get column names", -1);
            goto cleanup;
        }
        csv_rewind(csv);
        ncnames = csv_ncols(csv);
        cnames = 0;
    }
    convert = 0;
    if (argc > 3) {
        convert = sqlite3_value_int(argv[3]);
        if (row1 && (convert & 4)) {
            conv_names(cnames, ncnames);
        }
    }
    /* test if table exists */
    append(&sql, "PRAGMA table_info(", 0);
    append(&sql, tname, '"');
    append(&sql, ")", 0);
    if (!sql) {
oom:
        sqlite3_result_error(ctx, "out of memory", -1);
        goto cleanup;
    }
    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    append_free(&sql);
    if (rc != SQLITE_OK) {
prepfail:
        sqlite3_result_error(ctx, "prepare failed", -1);
        goto cleanup;
    }
    /* find number of colums */
    i = 0;
    rc = sqlite3_step(stmt);
    while (rc == SQLITE_ROW) {
        i++;
        rc = sqlite3_step(stmt);
    }
    if (rc != SQLITE_DONE) {
selfail:
        sqlite3_result_error(ctx, "select failed", -1);
        goto cleanup;
    }
    if (i > 0) {
        /* get column types */
        sqlite3_reset(stmt);
        ncnames = i;
        coltypes = sqlite3_malloc(ncnames);
        if (!coltypes) {
            goto oom;
        }
        rc = sqlite3_step(stmt);
        i = 0;
        while (rc == SQLITE_ROW) {
            coltypes[i++] = maptype((char *) sqlite3_column_text(stmt, 2));
            rc = sqlite3_step(stmt);
        }
        if (rc != SQLITE_DONE) {
            goto selfail;
        }
    } else {
        /* create new table */
        sqlite3_finalize(stmt);
        stmt = 0;
        colmap = (int *) sqlite3_malloc(ncnames + sizeof (int) * ncnames);
        if (!colmap) {
            goto oom;
        }
        memset(colmap, 0, sizeof (int) * ncnames);
        coltypes = (char *) (colmap + ncnames);
        append(&sql, "CREATE TABLE ", 0);
        append(&sql, tname, '"');
        append(&sql, "(", 0);
        for (i = 0; cnames && (i < ncnames); i++) {
            if (!cnames[i] || (cnames[i][0] == '\0')) {
                continue;
            }
            k = strlen(cnames[i]);
            if ((k > 7) && (strncasecmp("column_", cnames[i], 7) == 0)) {
                char c;

                if (sscanf(cnames[i] + 7, "%d%c", &k, &c) == 1) {
                    colmap[i] = k;
                }
            }
        }
        for (i = 0; i < ncnames; i++) {
            int want = i + 1;
            char colname[64];

            coltypes[i] = SQLITE_TEXT;
            if (useargs) {
                char *type = (char *) sqlite3_value_text(argv[i + 6]);

                if (!type) {
                    goto defcol;
                }
                append(&sql, type, 0);
                while (*type && !strchr(" \t", *type)) {
                    type++;
                }
                while (*type && strchr(" \t", *type)) {
                    type++;
                }
                coltypes[i] = maptype(type);
            } else if (!cnames || !cnames[i]) {
defcol:
                while (1) {
                    for (k = 0; k < ncnames; k++) {
                        if ((k != i) && (colmap[k] == want)) {
                            want++;
                            break;
                        }
                    }
                    if (k >= ncnames) {
                        colmap[i] = want;
                        break;
                    }
                }
                sprintf(colname, "column_%d", colmap[i]);
                append(&sql, colname, '"');
            } else if (row1 > 0) {
                append(&sql, cnames[i], '"');
            } else if (row1 < 0) {
                append(&sql, cnames[i], '"');
                if (i + 6 < argc) {
                    char *type = (char *) sqlite3_value_text(argv[i + 6]);

                    if (type) {
                        append(&sql, " ", 0);
                        append(&sql, type, 0);
                        coltypes[i] = maptype(type);
                    }
                }
            }
            if (i < ncnames - 1) {
                append(&sql, ",", 0);
            }
        }
        append(&sql, ")", 0);
        rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
        if (rc != SQLITE_OK) {
            goto prepfail;
        }
        rc = sqlite3_step(stmt);
        if ((rc != SQLITE_DONE) && (rc != SQLITE_OK)) {
            sqlite3_result_error(ctx, "create table failed", -1);
            goto cleanup;
        }
        append_free(&sql);
    }
    sqlite3_finalize(stmt);
    stmt = 0;
    /* make INSERT statement */
    append(&sql, "INSERT INTO ", 0);
    append(&sql, tname, '"');
    append(&sql, " VALUES(", 0);
    for (i = 0; i < ncnames; i++) {
        append(&sql, (i < ncnames - 1) ? "?," : "?)", 0);
    }
    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK) {
        goto prepfail;
    }
    append_free(&sql);
    /* import the CSV file */
    row1 = 0;
    while (csv_getline(csv, 0) != EOF) {
        for (i = 0; i < ncnames; i++) {
            char *data = csv_coldata(csv, i);

            rc = process_col(0, stmt, i + 1, data, coltypes[i], convert);
            if (rc != SQLITE_OK) {
                goto inserr;
            }
        }
        rc = sqlite3_step(stmt);
        if ((rc != SQLITE_DONE) && (rc != SQLITE_OK)) {
            if ((rc != SQLITE_MISMATCH) && (rc != SQLITE_CONSTRAINT)) {
inserr:
                sqlite3_result_error(ctx, "insert failed", -1);
                goto cleanup;
            }
        } else {
            row1++;
        }
        sqlite3_reset(stmt);
    }
    sqlite3_result_int(ctx, row1);
    goto cleanup;
}

#ifndef STANDALONE
static
#endif
int
csv_vtab_init(sqlite3 *db)
{
    sqlite3_create_function(db, "import_csv", -1, SQLITE_UTF8,
                            (void *) db, csv_import_func, 0, 0);
    return sqlite3_create_module(db, "csvtable", &csv_vtab_mod, 0);
}

#ifndef STANDALONE

int
sqlite3_extension_init(sqlite3 *db, char **errmsg,
                       const sqlite3_api_routines *api)
{
    SQLITE_EXTENSION_INIT2(api);
    return csv_vtab_init(db);
}

#endif

---

Generated on 23 Oct 2023 by doxygen.