The R Project SVN R-packages

#include <stdio.h>

#include <string.h>

#include "sqlite_dataframe.h"

SEXP sdf_import_sqlite_table(SEXP _dbfilename, SEXP _tblname, SEXP _sdfiname) {

    sqlite3 *db;

    sqlite3_stmt *stmt;

    int res, nrows, ncols, buflen0, buflen1, buflen3, i;

    int tblname_len, colname_len, iname_len;

    char *dbfilename, *tblname, *iname, *type;

    const char *colname;

    SEXP ret = NULL;

    dbfilename = CHAR_ELT(_dbfilename, 0);

    tblname = CHAR_ELT(_tblname, 0);

    /* determine if we were passed a real sqlite db */

    db = _is_sqlitedb(dbfilename);

    if (!db) {

        Rprintf("Error: %s is not a SQLite database.\n", dbfilename);

        return R_NilValue;

    }

    /* check if the table exists in the db */

    sprintf(g_sql_buf[0], "select count(*) from [%s]", tblname);

    res = sqlite3_prepare(db, g_sql_buf[0], -1, &stmt, 0);

    if (_sqlite_error(res)) { 

        sqlite3_finalize(stmt); sqlite3_close(db);

        return R_NilValue; 

    }

    sqlite3_step(stmt);

    nrows = sqlite3_column_int(stmt, 0);

    sqlite3_finalize(stmt); sqlite3_close(db);

    if (nrows == 0) {

        Rprintf("Error: Table contains no rows. Aborting import operation.\n");

        return R_NilValue;

    }

    /* we have to attach it so we can transfer easily */

    /* unload sdf's if we run to the MAX_ATTACHED limit */

    _prepare_attach2();

    /* attach the table to be imported */

    sprintf(g_sql_buf[0], "attach '%s' as [@@import@@]", dbfilename);

    res = _sqlite_exec(g_sql_buf[0]);

    if (_sqlite_error(res)) return R_NilValue;

    /* create the sdf where the data will be imported to */

    iname = _create_sdf_skeleton1(_sdfiname, NULL, FALSE);

    if (iname == NULL) goto sdf_import_sqlite_table__out;

    iname_len = strlen(iname);  /* needed for buf resizing below */

    tblname_len = strlen(tblname);

    /* get column types and names */

    sprintf(g_sql_buf[0], "select * from [@@import@@].[%s]", tblname);

    res = sqlite3_prepare(g_workspace, g_sql_buf[0], -1, &stmt, 0);

    _sqlite_error(res);

    sqlite3_step(stmt);

    ncols = sqlite3_column_count(stmt);

    /* create the sql scripts to execute: the create table and the insert-select */

    buflen0 = sprintf(g_sql_buf[0], "create table [%s].sdf_data([row name] text", iname);

    buflen1 = sprintf(g_sql_buf[1], "select cumsum(1)");

    buflen3 = sprintf(g_sql_buf[3], "from [@@import@@].[%s] ", tblname);

    for (i = 0; i < ncols; i++) {

        type = _str_tolower(g_sql_buf[2], sqlite3_column_decltype(stmt, i));

        colname = sqlite3_column_name(stmt, i);

        colname_len = strlen(colname);

        if (strstr(type, "int")) {

            _expand_buf(0, buflen0 + strlen(colname) + 10);

            buflen0 += sprintf(g_sql_buf[0]+buflen0, ",[%s] int", colname);

            _expand_buf(1, buflen1 + colname_len + 10);

            buflen1 += sprintf(g_sql_buf[1]+buflen1, ",[%s]", colname);

        } else if (strstr(type, "char") || strcmp(type, "text") == 0) {

            _expand_buf(0, buflen0 + strlen(colname) + 10);

            buflen0 += sprintf(g_sql_buf[0]+buflen0, ",[%s] int", colname);

            _expand_buf(1, buflen1 + colname_len + iname_len + 20);

            buflen1 += sprintf(g_sql_buf[1]+buflen1, ",[%s].[factor %s].level",

                   iname, colname);

            /* create factor table */

            _create_factor_table2(iname, "factor", colname);

            /* insert factor levels and labels */

            sprintf(g_sql_buf[2], "insert into [%s].[factor %s] (label)"

                   " select distinct [%s] from [@@import@@].[%s]", 

                   iname, colname, colname, tblname);

            res = _sqlite_exec(g_sql_buf[2]);

            _sqlite_error(res);

            _init_sqlite_function_accumulator();

            sprintf(g_sql_buf[2], "update [%s].[factor %s] set level=cumsum(1)",

                   iname, colname);

            res = _sqlite_exec(g_sql_buf[2]);

            _sqlite_error(res);

            /* add join clause to from clause */

            _expand_buf(3, buflen3 + colname_len * 3 + iname_len * 2 + strlen(tblname) + 32);

            buflen3 += sprintf(g_sql_buf[3] + buflen3, " join [%s].[factor %s] "

                    "on [@@import@@].[%s].[%s] = [%s].[factor %s].label",

                    iname, colname, tblname, colname, iname, colname);

        } else if (strcmp(type, "blob") == 0 || !*type) {

            /* not supported */

            warning("skipping column %s because it is a blob.\n", colname);

        } else { /* numeric / float / real / ... */

            _expand_buf(0, buflen0 + strlen(colname) + 10);

            buflen0 += sprintf(g_sql_buf[0]+buflen0, ",[%s] double", colname);

            _expand_buf(1, buflen1 + colname_len + 10);

            buflen1 += sprintf(g_sql_buf[1]+buflen1, ",[%s]", colname);

        }

    }

    sqlite3_finalize(stmt);

    /* execute create script */

    _expand_buf(0, buflen0 + 10);

    sprintf(g_sql_buf[0] + buflen0, ")");

    res = _sqlite_exec(g_sql_buf[0]);

    if (_sqlite_error(res)) {

        sdf_detach_sdf(mkString(iname));

        goto sdf_import_sqlite_table__out;

    }

    /* execute insert-select script */

    _init_sqlite_function_accumulator();

    _expand_buf(0, buflen1 + buflen3 + iname_len + 32 );

    sprintf(g_sql_buf[0], "insert into [%s].sdf_data %s %s", iname, g_sql_buf[1], g_sql_buf[3]);

    res = _sqlite_exec(g_sql_buf[0]);

    if (_sqlite_error(res)) {

        sdf_detach_sdf(mkString(iname));

        goto sdf_import_sqlite_table__out;

    }

    /* create sexp sdf */

    ret = _create_sdf_sexp(iname);

sdf_import_sqlite_table__out:

    _sqlite_exec("detach [@@import@@]");

    return ret;

}