The R Project SVN – R-packages – Diff – /trunk/SQLiteDF/src/sqlite_vector.c

 #include "Rmath.h"
 /****************************************************************************
  * UTILITY FUNCTIONS
  ****************************************************************************/
-static char *_create_svector1(SEXP name, const char *type, int * _namelen) {
+char *_create_svector1(SEXP name, const char *type, int * _namelen, int protect) {
     int namelen, res;
-    char *iname = _create_sdf_skeleton1(name, &namelen);
+    char *iname = _create_sdf_skeleton1(name, &namelen, protect);
     if (iname == NULL) return NULL;
     sprintf(g_sql_buf[2], "create table [%s].sdf_data ([row name] text, "
             "V1 %s, primary key ([row name]))", iname, type);
+    }
     char *iname = SDF_INAME(sdf);
     char *varname = CHAR_ELT(name, 0);
-    if (!USE_SDF1(iname, TRUE)) return R_NilValue;
+    if (!USE_SDF1(iname, TRUE, FALSE)) return R_NilValue;
     /* check if sdf & varname w/in that sdf exists */
     sqlite3_stmt *stmt;
     sprintf(g_sql_buf[0], "select [%s] from [%s].sdf_data", varname, iname);
+}
 SEXP sdf_get_variable_length(SEXP svec) {
     char *iname = SDF_INAME(svec);
-    if (!USE_SDF1(iname, TRUE)) return R_NilValue;
+    if (!USE_SDF1(iname, TRUE, FALSE)) return R_NilValue;
     SEXP ret;
     PROTECT(ret = NEW_INTEGER(1));
     INTEGER(ret)[0] = _get_row_count2(iname, 1);
     UNPROTECT(1);
     SEXP ret = R_NilValue, tmp;
     char *iname = SDF_INAME(svec), *varname = SVEC_VARNAME(svec);
     int index, idxlen, i, retlen=0, res;
     sqlite3_stmt *stmt;
-    if (!USE_SDF1(iname, TRUE)) return R_NilValue;
+    if (!USE_SDF1(iname, TRUE, FALSE)) return R_NilValue;
     idxlen = LENGTH(idx);
     if (idxlen < 1) return ret;
     sprintf(g_sql_buf[0], "select [%s] from [%s].sdf_data limit ?,1",
+    }
     return R_NilValue;
+}
+SEXP sdf_variable_summary(SEXP svec, SEXP maxsum) {
+    char *iname, *varname, *type;
+    sqlite3_stmt *stmt;
+    int nprotected = 0;
+    SEXP ret, names;
+    iname = SDF_INAME(svec);
+    varname = SVEC_VARNAME(svec);
+    USE_SDF1(iname, TRUE, FALSE);
+    if ((inherits(svec, "ordered") && ((type = "ordered"))) ||
+            (inherits(svec, "factor") && ((type = "factor")))) {
+        int nrows, i, max_rows = INTEGER(maxsum)[0];
+        sprintf(g_sql_buf[0], "[%s].[%s %s]", iname, type, varname);
+        nrows = _get_row_count2(g_sql_buf[0], FALSE);
+        if (nrows <= max_rows) max_rows = nrows;
+        else { nrows = max_rows; max_rows--; }
+        PROTECT(ret = NEW_INTEGER(nrows)); nprotected = 1;
+        PROTECT(names = NEW_CHARACTER(nrows)); nprotected++;
+        sprintf(g_sql_buf[0], "select [%s].[%s %s].label, count(*) from "
+                "[%s].sdf_data join [%s].[%s %s] on [%s].sdf_data.[%s]=[%s].[%s %s].level "
+                "group by [%s].sdf_data.[%s], [%s].[%s %s].level order by count(*) desc",
+                iname, type, varname, iname, iname, type, varname, iname, varname,
+                iname, type, varname, iname, varname, iname, type, varname);
+        sqlite3_prepare(g_workspace, g_sql_buf[0], -1, &stmt, 0);
+        for (i = 0; i < max_rows; i++) {
+            sqlite3_step(stmt);
+            SET_STRING_ELT(names, i, mkChar((char *)sqlite3_column_text(stmt, 0)));
+            INTEGER(ret)[i] = sqlite3_column_int(stmt, 1);
+        }
+        if (nrows > max_rows) {
+            int others_sum = 0;
+            SET_STRING_ELT(names, nrows-1, mkChar("(Others)"));
+            while (sqlite3_step(stmt) == SQLITE_ROW) {
+                others_sum += sqlite3_column_int(stmt, 1);
+            }
+            INTEGER(ret)[nrows-1] = others_sum;
+        }
+    } else if (inherits(svec, "logical")) {
+        sprintf(g_sql_buf[0], "select count(*) from "
+                "[%s].sdf_data group by [%s] order by [%s]", iname, varname, varname);
+        PROTECT(names = NEW_CHARACTER(3)); nprotected = 1;
+        PROTECT(ret = NEW_CHARACTER(3)); nprotected++;
+        SET_STRING_ELT(names, 0, mkChar("Mode"));
+        SET_STRING_ELT(names, 1, mkChar("FALSE"));
+        SET_STRING_ELT(names, 2, mkChar("TRUE"));
+        sqlite3_prepare(g_workspace, g_sql_buf[0], -1, &stmt, 0);
+        SET_STRING_ELT(ret, 0, mkChar("logical"));
+        sqlite3_step(stmt);
+        SET_STRING_ELT(ret, 1, mkChar((const char *)sqlite3_column_text(stmt, 0)));
+        sqlite3_step(stmt);
+        SET_STRING_ELT(ret, 2, mkChar((const char *)sqlite3_column_text(stmt, 0)));
+    } else return R_NilValue;
+    sqlite3_finalize(stmt);
+    SET_NAMES(ret, names);
+    SET_CLASS(ret, mkString("table"));
+    UNPROTECT(nprotected);
+    return ret;
+}
 /* the global accumulator should be safe if we only do 1 cummulative or
  * aggregate at any time. it won't work for stuffs like "select max(col)-min(col)
  * from sdf_data". */
 static double g_accumulator = 0.0; /* accumulator var for cumsum, cumprod, etc. */
 static int g_start = 0;            /* flag for start of cummulation */
     funcname = CHAR_ELT(func, 0);
     iname_src = SDF_INAME(vector);
     varname_src = SVEC_VARNAME(vector);
     nargs = INTEGER(_nargs)[0];
-    if (!USE_SDF1(iname_src, TRUE)) return R_NilValue;
+    if (!USE_SDF1(iname_src, TRUE, TRUE)) return R_NilValue;
     /* check nargs */
     if (nargs > 2) {
         Rprintf("Error: Don't know how to handle Math functions w/ more than 2 args\n");
         return R_NilValue;
+    }
     /* create a new sdf, with 1 column named V1 */
-    iname = _create_svector1(R_NilValue, "double", &namelen);
+    iname = _create_svector1(R_NilValue, "double", &namelen, TRUE);
     /* insert into <newsdf>.col, row.names select func(col), rownames */
     sprintf(g_sql_buf[0], "insert into [%s].sdf_data([row name], V1) "
             "select [row name], %s([%s]) from [%s].sdf_data", iname, funcname,
             varname_src, iname_src);
     g_accumulator = 0.0;   /* initialize accumulator */
     g_start = 1;           /* flag that we are at start of accumulating */
     sqlite3_step(stmt);
     sqlite3_finalize(stmt);
+    UNUSE_SDF2(iname);
+    UNUSE_SDF2(iname_src);
     return _create_svector_sexp(iname, "V1", "numeric");
+}
 SEXP sdf_do_variable_op(SEXP func, SEXP vector, SEXP op2) {
     char *iname = NULL, *iname_src, *varname_src, *funcname;
     /* get data from arguments (function name and sqlite.vector stuffs) */
     funcname = CHAR_ELT(func, 0);
     iname_src = SDF_INAME(vector);
     varname_src = SVEC_VARNAME(vector);
-    if (!USE_SDF1(iname_src, TRUE)) return R_NilValue;
+    if (!USE_SDF1(iname_src, TRUE, TRUE)) return R_NilValue;
     switch(funcname[0]) {
         case '+' :
         case '-' :
         case '*' :
         case '/' :
     svec_len = _get_row_count2(iname_src, 1);
     if (functype == 0 || functype == 2 || (functype == 1 && funcname[0] != '!')) {
         char *insert_fmt_string1c, *insert_fmt_string1s;
         char *insert_fmt_string2c, *insert_fmt_string2s;
-        iname = _create_svector1(R_NilValue, (functype == 0) ? "double" : "bit", NULL);
+        iname = _create_svector1(R_NilValue, (functype == 0) ? "double" : "bit", NULL, TRUE);
         if (functype == 1) { /* boolean binary operators */
             insert_fmt_string1s = "insert into [%s].sdf_data "
                         "select [row name], ([%s] != 0) %s (? != 0) from [%s].sdf_data";
             insert_fmt_string2s = "insert into [%s].sdf_data values(?, (? != 0) %s (? != 0))";
+                }
                 res = sqlite3_prepare(g_workspace, g_sql_buf[2], -1, &stmt, 0);
                 _sqlite_error(res);
                 sqlite3_bind_double(stmt, 1, REAL(op2)[0]);
                 sqlite3_step(stmt);
+                sqlite3_finalize(stmt);
             } else if (op2_len <= svec_len) {  /* recycle op2 */
                 sprintf(g_sql_buf[2], "select [row name], [%s] from [%s].sdf_data",
                         varname_src, iname_src);
                 res = sqlite3_prepare(g_workspace, g_sql_buf[2], -1, &stmt2, 0);
                 _sqlite_error(res);
                 if (funcname[0] == '%') {
                     /* sqlite does int div with "/" if both args are int. mod is % also.
                      * fortunately, in both cases the sqlite op is 2nd char of funcname */
                     sprintf(g_sql_buf[2], insert_fmt_string2c, iname, funcname[1]);
-                    _sqlite_exec("begin");
+                    _sqlite_begin;
                     res = sqlite3_prepare(g_workspace, g_sql_buf[2], -1, &stmt, 0);
                     _sqlite_error(res);
                     for (i = 0; i < svec_len; i++) {
                         sqlite3_step(stmt2);
                         sqlite3_bind_text(stmt, 1, (char *)sqlite3_column_text(stmt2, 0), -1, SQLITE_STATIC);
                         sqlite3_bind_int(stmt, 2, (int)sqlite3_column_double(stmt2, 1));
                         sqlite3_bind_int(stmt, 3, (int)REAL(op2)[i % op2_len]);
                         sqlite3_step(stmt);
+                    }
+                    sqlite3_finalize(stmt);
+                    sqlite3_finalize(stmt2);
-                    _sqlite_exec("commit");
+                    _sqlite_commit;
                 } else { /* non-integer binary operation */
                     sprintf(g_sql_buf[2], insert_fmt_string2s, iname, funcname);
-                    _sqlite_exec("begin");
+                    _sqlite_begin;
                     res = sqlite3_prepare(g_workspace, g_sql_buf[2], -1, &stmt, 0);
                     _sqlite_error(res);
                     for (i = 0; i < svec_len; i++) {
                         sqlite3_step(stmt2);
                         sqlite3_bind_text(stmt, 1, (char *)sqlite3_column_text(stmt2, 0), -1, SQLITE_STATIC);
                         sqlite3_bind_double(stmt, 2, sqlite3_column_double(stmt2, 1));
                         sqlite3_bind_double(stmt, 3, REAL(op2)[i % op2_len]);
                         sqlite3_step(stmt);
+                    }
+                    sqlite3_finalize(stmt);
+                    sqlite3_finalize(stmt2);
-                    _sqlite_exec("commit");
+                    _sqlite_commit;
+                }
-                sqlite3_finalize(stmt2);
             } else { /* op2_len > svec_len, recycle svec */
                 sprintf(g_sql_buf[1], "select [%s] from [%s].sdf_data",
                         varname_src, iname_src);
                 if (funcname[0] == '%') {
                     sprintf(g_sql_buf[2], insert_fmt_string2s, iname, funcname);
                     res = sqlite3_prepare(g_workspace, g_sql_buf[2], -1, &stmt, 0);
                     _sqlite_error(res);
+                }
-                i = 0; _sqlite_exec("begin");
+                i = 0; _sqlite_begin;
                 while (i < op2_len) {
                     res = sqlite3_prepare(g_workspace, g_sql_buf[2], -1, &stmt2, 0);
                     _sqlite_error(res);
                     if (funcname[0] == '%') {
                     /* recycle on svec if we loop again */
                     sqlite3_finalize(stmt2);
+                }
+                sqlite3_finalize(stmt);
-                _sqlite_exec("commit");
+                _sqlite_commit;
+            }
-            sqlite3_finalize(stmt);
         } else if (IS_INTEGER(op2) || IS_LOGICAL(op2)) { /* I N T E G E R */
             /* we are taking advantage of the fact that logicals are stored as int.
              * if this becomes untrue in the future, then this is a bug */
             op2_len = LENGTH(op2);
                 if (funcname[0] == '%') {
                     /* sqlite does int div with "/" if both args are int. mod is % also.
                      * fortunately, in both cases the sqlite op is 2nd char of funcname */
                     sprintf(g_sql_buf[2], insert_fmt_string2c, iname, funcname[1]);
-                    _sqlite_exec("begin");
+                    _sqlite_begin;
                     res = sqlite3_prepare(g_workspace, g_sql_buf[2], -1, &stmt, 0);
                     _sqlite_error(res);
                     for (i = 0; i < svec_len; i++) {
                         sqlite3_step(stmt2);
                         sqlite3_bind_text(stmt, 1, (char *)sqlite3_column_text(stmt2, 0), -1, SQLITE_STATIC);
                         sqlite3_bind_int(stmt, 2, sqlite3_column_int(stmt2, 1));
                         sqlite3_bind_int(stmt, 3, INTEGER(op2)[i % op2_len]);
                         sqlite3_step(stmt);
+                    }
-                    _sqlite_exec("commit");
+                    _sqlite_commit;
                 } else {
                     sprintf(g_sql_buf[2], insert_fmt_string2s, iname, funcname);
-                    _sqlite_exec("begin");
+                    _sqlite_begin;
                     res = sqlite3_prepare(g_workspace, g_sql_buf[2], -1, &stmt, 0);
                     _sqlite_error(res);
                     for (i = 0; i < svec_len; i++) {
                         sqlite3_step(stmt2);
                         sqlite3_bind_text(stmt, 1, (char *)sqlite3_column_text(stmt2, 0), -1, SQLITE_STATIC);
                         sqlite3_bind_double(stmt, 2, sqlite3_column_double(stmt2, 1));
                         sqlite3_bind_double(stmt, 3, (double)INTEGER(op2)[i % op2_len]);
                         sqlite3_step(stmt);
+                    }
-                    _sqlite_exec("commit");
+                    _sqlite_commit;
+                }
                 sqlite3_finalize(stmt2);
             } else {
                 sprintf(g_sql_buf[1], "select [%s] from [%s].sdf_data",
                         varname_src, iname_src);
                     sprintf(g_sql_buf[2], insert_fmt_string2s, iname, funcname);
                     res = sqlite3_prepare(g_workspace, g_sql_buf[2], -1, &stmt, 0);
                     _sqlite_error(res);
+                }
-                i = 0; _sqlite_exec("begin");
+                i = 0; _sqlite_begin;
                 while (i < op2_len) {
                     res = sqlite3_prepare(g_workspace, g_sql_buf[2], -1, &stmt2, 0);
                     _sqlite_error(res);
                     if (funcname[0] == '%') {
+                    }
                     /* recycle on svec if we loop again */
                     sqlite3_finalize(stmt2);
+                }
-                _sqlite_exec("commit");
+                _sqlite_commit;
+            }
             sqlite3_finalize(stmt);
         } else if (inherits(op2, "sqlite.vector")) {
             /* op2 is surely not a factor, as handled by the R wrapper */
             char *iname_op2, *varname_op2;
             sqlite3_stmt *stmt3;
             iname_op2 = SDF_INAME(op2);
             varname_op2 = SVEC_VARNAME(op2);
-            if (!USE_SDF1(iname_op2, TRUE)) {
+            if (!USE_SDF1(iname_op2, TRUE, TRUE)) {
                 /* delete created sqlite.vector */
                 Rprintf("Warning: detaching created result SDF %s\n", iname);
                 sdf_detach_sdf(mkString(iname));
                 return R_NilValue;
+            }
+            _sqlite_begin;
             op2_len = _get_row_count2(iname_op2, 1);
             sprintf(g_sql_buf[2], "select [%s] from [%s].sdf_data", varname_src, iname_src);
             res = sqlite3_prepare(g_workspace, g_sql_buf[2], -1, &stmt2, 0);
             res = sqlite3_prepare(g_workspace, g_sql_buf[2], -1, &stmt3, 0);
             _sqlite_error(res);
             if (funcname[0] == '%') {
                 sprintf(g_sql_buf[2], insert_fmt_string2c, iname, funcname[1]);
-                _sqlite_exec("begin");
                 res = sqlite3_prepare(g_workspace, g_sql_buf[2], -1, &stmt, 0);
                 _sqlite_error(res);
                 if (svec_len == op2_len) {
                     for (i = 0; i < svec_len; i++) {
                             sqlite3_step(stmt);
+                        }
                         sqlite3_reset(stmt3);
+                    }
+                }
-                _sqlite_exec("commit");
+                _sqlite_commit;
             } else { /* not an integer op %% or %/% */
                 sprintf(g_sql_buf[2], insert_fmt_string2s, iname, funcname);
-                _sqlite_exec("begin");
                 res = sqlite3_prepare(g_workspace, g_sql_buf[2], -1, &stmt, 0);
                 _sqlite_error(res);
                 if (svec_len == op2_len) {
                     for (i = 0; i < svec_len; i++) {
                             sqlite3_step(stmt);
+                        }
                         sqlite3_reset(stmt3);
+                    }
+                }
-                _sqlite_exec("commit");
+            }
             sqlite3_finalize(stmt);
             sqlite3_finalize(stmt2);
             sqlite3_finalize(stmt3);
+            _sqlite_commit;
+            UNUSE_SDF2(iname_op2);
+        }
     } else if (functype == 1 && funcname[0] != '!') { /* unary not operator */
-        iname = _create_svector1(R_NilValue, "bit", NULL);
+        iname = _create_svector1(R_NilValue, "bit", NULL, TRUE);
         sprintf(g_sql_buf[2], "insert into [%s].sdf_data "
                     "select [row name], [%s] == 0 from [%s].sdf_data",
                     iname, varname_src, iname_src);
         res = sqlite3_prepare(g_workspace, g_sql_buf[2], -1, &stmt, 0);
         _sqlite_error(res);
         sqlite3_step(stmt);
         sqlite3_finalize(stmt);
+    }
-    if (iname != NULL)
+    if (iname != NULL) {
         return _create_svector_sexp(iname, "V1",
                 (functype == 0) ? "numeric" : "logical");
+        UNUSE_SDF2(iname);
+    }
+    UNUSE_SDF2(iname_src);
     return R_NilValue;
+}
     /* get data from arguments (function name and sqlite.vector stuffs) */
     funcname = CHAR_ELT(func, 0);
     iname_src = SDF_INAME(vector);
     varname_src = SVEC_VARNAME(vector);
-    if (!USE_SDF1(iname_src, TRUE)) return R_NilValue;
+    if (!USE_SDF1(iname_src, TRUE, FALSE)) return R_NilValue;
     g_narm = LOGICAL(na_rm)[0];
     if (strcmp(funcname, "range") == 0) {
         /* special handling for range. use min then max */
         g_start = 1;
     int res;
     iname_src = SDF_INAME(svec);
     varname_src = SVEC_VARNAME(svec);
-    if (!USE_SDF1(iname_src, TRUE)) return R_NilValue;
+    if (!USE_SDF1(iname_src, TRUE, TRUE)) return R_NilValue;
     /* determine type of svec */
     sprintf(g_sql_buf[0], "select [%s] from [%s].sdf_data limit 1", varname_src, iname_src);
     res = sqlite3_prepare(g_workspace, g_sql_buf[0], -1, &stmt, 0);
     _sqlite_error(res);
     sqlite3_step(stmt);
     strcpy(g_sql_buf[0], sqlite3_column_decltype(stmt, 0));
     sqlite3_finalize(stmt);
     /* create a new vector of that type */
-    iname = _create_svector1(R_NilValue, g_sql_buf[0], NULL);
+    iname = _create_svector1(mkString("tmp-sort"), g_sql_buf[0], NULL, TRUE);
     /* insert to new sdf ordered */
     sprintf(g_sql_buf[0], "insert into [%s].sdf_data "
             "select [row name], [%s] from [%s].sdf_data "
             "order by [%s] %s", iname, varname_src, iname_src, varname_src,
         if (inherits(svec, "ordered")) type = "ordered";
         else type = "factor";
         _copy_factor_levels2(type, iname_src, varname_src, iname, "V1");
     } else type = CHAR_ELT(GET_CLASS(svec), 1);
+    UNUSE_SDF2(iname_src);
+    UNUSE_SDF2(iname);
     return _create_svector_sexp(iname, "V1", type);
+}
 /****************************************************************************
  * VECTOR MATH/OPS/GROUP OPERATIONS

The R Project SVN R-packages

(root)/trunk/SQLiteDF/src/sqlite_vector.c – Rev 3397 → 3419