/ Check-in [a9311d9d]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add 'copy' method for tcl interface. Behaves similar to shell .import or COPY statment in 2.x. (CVS 2167)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a9311d9df054a91e231d4e4332df0d661675744d
User & Date: tpoindex 2004-12-17 15:41:12
Context
2004-12-17
20:48
Fix a C++-ism in the previous change to tclsqlite.c. (CVS 2168) check-in: b49b8fdd user: drh tags: trunk
15:41
Add 'copy' method for tcl interface. Behaves similar to shell .import or COPY statment in 2.x. (CVS 2167) check-in: a9311d9d user: tpoindex tags: trunk
2004-12-16
21:09
Make sure LIMITs are handled correctly on UNION operators. Ticket #1035. (CVS 2166) check-in: ece0085f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/tclsqlite.c.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** A TCL Interface to SQLite
    13     13   **
    14         -** $Id: tclsqlite.c,v 1.109 2004/12/02 20:17:02 drh Exp $
           14  +** $Id: tclsqlite.c,v 1.110 2004/12/17 15:41:12 tpoindex Exp $
    15     15   */
    16     16   #ifndef NO_TCL     /* Omit this whole file if TCL is unavailable */
    17     17   
    18     18   #include "sqliteInt.h"
    19     19   #include "hash.h"
    20     20   #include "tcl.h"
    21     21   #include <stdlib.h>
................................................................................
   329    329     pVal = Tcl_NewStringObj(Tcl_DStringValue(&dCol), -1);
   330    330     Tcl_DStringFree(&dCol);
   331    331   #else
   332    332     pVal = Tcl_NewStringObj(zText, -1);
   333    333   #endif
   334    334     return pVal;
   335    335   }
          336  +
          337  +/*
          338  +** This routine reads a line of text from FILE in, stores
          339  +** the text in memory obtained from malloc() and returns a pointer
          340  +** to the text.  NULL is returned at end of file, or if malloc()
          341  +** fails.
          342  +**
          343  +** The interface is like "readline" but no command-line editing
          344  +** is done.
          345  +**
          346  +** copied from shell.c from '.import' command
          347  +*/
          348  +static char *local_getline(char *zPrompt, FILE *in){
          349  +  char *zLine;
          350  +  int nLine;
          351  +  int n;
          352  +  int eol;
          353  +
          354  +  nLine = 100;
          355  +  zLine = malloc( nLine );
          356  +  if( zLine==0 ) return 0;
          357  +  n = 0;
          358  +  eol = 0;
          359  +  while( !eol ){
          360  +    if( n+100>nLine ){
          361  +      nLine = nLine*2 + 100;
          362  +      zLine = realloc(zLine, nLine);
          363  +      if( zLine==0 ) return 0;
          364  +    }
          365  +    if( fgets(&zLine[n], nLine - n, in)==0 ){
          366  +      if( n==0 ){
          367  +        free(zLine);
          368  +        return 0;
          369  +      }
          370  +      zLine[n] = 0;
          371  +      eol = 1;
          372  +      break;
          373  +    }
          374  +    while( zLine[n] ){ n++; }
          375  +    if( n>0 && zLine[n-1]=='\n' ){
          376  +      n--;
          377  +      zLine[n] = 0;
          378  +      eol = 1;
          379  +    }
          380  +  }
          381  +  zLine = realloc( zLine, n+1 );
          382  +  return zLine;
          383  +}
   336    384   
   337    385   /*
   338    386   ** The "sqlite" command below creates a new Tcl command for each
   339    387   ** connection it opens to an SQLite database.  This routine is invoked
   340    388   ** whenever one of those connection-specific commands is executed
   341    389   ** in Tcl.  For example, if you run Tcl code like this:
   342    390   **
................................................................................
   350    398   static int DbObjCmd(void *cd, Tcl_Interp *interp, int objc,Tcl_Obj *const*objv){
   351    399     SqliteDb *pDb = (SqliteDb*)cd;
   352    400     int choice;
   353    401     int rc = TCL_OK;
   354    402     static const char *DB_strs[] = {
   355    403       "authorizer",         "busy",              "changes",
   356    404       "close",              "collate",           "collation_needed",
   357         -    "commit_hook",        "complete",          "errorcode",
   358         -    "eval",               "function",          "last_insert_rowid",
   359         -    "onecolumn",          "progress",          "rekey",
   360         -    "timeout",            "total_changes",     "trace",
          405  +    "commit_hook",        "complete",          "copy",
          406  +    "errorcode",          "eval",              "function",
          407  +    "last_insert_rowid",  "onecolumn",         "progress",
          408  +    "rekey",              "timeout",           "total_changes",
          409  +    "trace",
   361    410       0                    
   362    411     };
   363    412     enum DB_enum {
   364    413       DB_AUTHORIZER,        DB_BUSY,             DB_CHANGES,
   365    414       DB_CLOSE,             DB_COLLATE,          DB_COLLATION_NEEDED,
   366         -    DB_COMMIT_HOOK,       DB_COMPLETE,         DB_ERRORCODE,
   367         -    DB_EVAL,              DB_FUNCTION,         DB_LAST_INSERT_ROWID,
   368         -    DB_ONECOLUMN,         DB_PROGRESS,         DB_REKEY,
   369         -    DB_TIMEOUT,           DB_TOTAL_CHANGES,    DB_TRACE,
          415  +    DB_COMMIT_HOOK,       DB_COMPLETE,         DB_COPY,
          416  +    DB_ERRORCODE,         DB_EVAL,             DB_FUNCTION,
          417  +    DB_LAST_INSERT_ROWID, DB_ONECOLUMN,        DB_PROGRESS,
          418  +    DB_REKEY,             DB_TIMEOUT,          DB_TOTAL_CHANGES,
          419  +    DB_TRACE
   370    420     };
          421  +  /* don't leave trailing commas on DB_enum, it confuses the AIX xlc compiler */
   371    422   
   372    423     if( objc<2 ){
   373    424       Tcl_WrongNumArgs(interp, 1, objv, "SUBCOMMAND ...");
   374    425       return TCL_ERROR;
   375    426     }
   376    427     if( Tcl_GetIndexFromObj(interp, objv[1], DB_strs, "option", 0, &choice) ){
   377    428       return TCL_ERROR;
................................................................................
  1037   1088           sqlite3_trace(pDb->db, DbTraceHandler, pDb);
  1038   1089         }else{
  1039   1090           sqlite3_trace(pDb->db, 0, 0);
  1040   1091         }
  1041   1092       }
  1042   1093       break;
  1043   1094     }
         1095  +
         1096  +  /*    $db copy conflict-algorithm table filename ?SEPARATOR? ?NULLINDICATOR?
         1097  +  **
         1098  +  ** Copy data into table from filename, optionally using SEPARATOR
         1099  +  ** as column separators.  If a column contains a null string, or the
         1100  +  ** value of NULLINDICATOR, a NULL is inserted for the column.
         1101  +  ** conflict-algorithm is one of the sqlite conflict algorithms:
         1102  +  **    rollback, abort, fail, ignore, replace
         1103  +  ** On success, return the number of lines processed, not necessarily same
         1104  +  ** as 'db changes' due to conflict-algorithm selected.
         1105  +  **
         1106  +  ** This code is basically an implementation/enhancement of
         1107  +  ** the sqlite3 shell.c ".import" command.
         1108  +  **
         1109  +  ** This command usage is equivalent to the sqlite2.x COPY statement,
         1110  +  ** which imports file data into a table using the PostgreSQL COPY file format:
         1111  +  **   $db copy $conflit_algo $table_name $filename \t \\N
         1112  +  */
         1113  +  case DB_COPY: {
         1114  +    char *zSep;
         1115  +    char *zNull;
         1116  +    if( objc<5 || objc>7 ){
         1117  +      Tcl_WrongNumArgs(interp, 2, objv, "CONFLICT-ALGORITHM TABLE FILENAME ?SEPARATOR? ?NULLINDICATOR?");
         1118  +      return TCL_ERROR;
         1119  +    }
         1120  +    if( objc>=6 ){
         1121  +      zSep = Tcl_GetStringFromObj(objv[5], 0);
         1122  +    }else{
         1123  +      zSep = "\t";
         1124  +    }
         1125  +    if( objc>=7 ){
         1126  +      zNull = Tcl_GetStringFromObj(objv[6], 0);
         1127  +    }else{
         1128  +      zNull = "";
         1129  +    }
         1130  +    char *zTable;               /* Insert data into this table */
         1131  +    char *zFile;                /* The file from which to extract data */
         1132  +    char *zConflict;            /* The conflict algorithm to use */
         1133  +    sqlite3_stmt *pStmt;        /* A statement */
         1134  +    int rc;                     /* Result code */
         1135  +    int nCol;                   /* Number of columns in the table */
         1136  +    int nByte;                  /* Number of bytes in an SQL string */
         1137  +    int i, j;                   /* Loop counters */
         1138  +    int nSep;                   /* Number of bytes in zSep[] */
         1139  +    int nNull;                  /* Number of bytes in zNull[] */
         1140  +    char *zSql;                 /* An SQL statement */
         1141  +    char *zLine;                /* A single line of input from the file */
         1142  +    char **azCol;               /* zLine[] broken up into columns */
         1143  +    char *zCommit;              /* How to commit changes */
         1144  +    FILE *in;                   /* The input file */
         1145  +    int lineno = 0;             /* Line number of input file */
         1146  +    char zLineNum[80];          /* Line number print buffer */
         1147  +    Tcl_Obj *pResult;           /* interp result */
         1148  +
         1149  +    zConflict = Tcl_GetStringFromObj(objv[2], 0);
         1150  +    zTable = Tcl_GetStringFromObj(objv[3], 0);
         1151  +    zFile = Tcl_GetStringFromObj(objv[4], 0);
         1152  +    nSep = strlen(zSep);
         1153  +    nNull = strlen(zNull);
         1154  +    if( nSep==0 ){
         1155  +      Tcl_AppendResult(interp, "Error: non-null separator required for copy", 0);
         1156  +      return TCL_ERROR;
         1157  +    }
         1158  +    if(sqlite3StrICmp(zConflict, "rollback") != 0 &&
         1159  +       sqlite3StrICmp(zConflict, "abort"   ) != 0 &&
         1160  +       sqlite3StrICmp(zConflict, "fail"    ) != 0 &&
         1161  +       sqlite3StrICmp(zConflict, "ignore"  ) != 0 &&
         1162  +       sqlite3StrICmp(zConflict, "replace" ) != 0 ) {
         1163  +      Tcl_AppendResult(interp, "Error: \"", zConflict, "\", conflict-algorithm must be one of: rollback, abort, fail, ignore, or replace", 0);
         1164  +      return TCL_ERROR;
         1165  +    }
         1166  +    zSql = sqlite3_mprintf("SELECT * FROM '%q'", zTable);
         1167  +    if( zSql==0 ){
         1168  +      Tcl_AppendResult(interp, "Error: no such table: ", zTable, 0);
         1169  +      return TCL_ERROR;
         1170  +    }
         1171  +    nByte = strlen(zSql);
         1172  +    rc = sqlite3_prepare(pDb->db, zSql, 0, &pStmt, 0);
         1173  +    sqlite3_free(zSql);
         1174  +    if( rc ){
         1175  +      Tcl_AppendResult(interp, "Error: ", sqlite3_errmsg(pDb->db), 0);
         1176  +      nCol = 0;
         1177  +    }else{
         1178  +      nCol = sqlite3_column_count(pStmt);
         1179  +    }
         1180  +    sqlite3_finalize(pStmt);
         1181  +    if( nCol==0 ) {
         1182  +      return TCL_ERROR;
         1183  +    }
         1184  +    zSql = malloc( nByte + 50 + nCol*2 );
         1185  +    if( zSql==0 ) {
         1186  +      Tcl_AppendResult(interp, "Error: can't malloc()", 0);
         1187  +      return TCL_ERROR;
         1188  +    }
         1189  +    sqlite3_snprintf(nByte+50, zSql, "INSERT OR %q INTO '%q' VALUES(?", zConflict, zTable);
         1190  +    j = strlen(zSql);
         1191  +    for(i=1; i<nCol; i++){
         1192  +      zSql[j++] = ',';
         1193  +      zSql[j++] = '?';
         1194  +    }
         1195  +    zSql[j++] = ')';
         1196  +    zSql[j] = 0;
         1197  +    rc = sqlite3_prepare(pDb->db, zSql, 0, &pStmt, 0);
         1198  +    free(zSql);
         1199  +    if( rc ){
         1200  +      Tcl_AppendResult(interp, "Error: ", sqlite3_errmsg(pDb->db), 0);
         1201  +      sqlite3_finalize(pStmt);
         1202  +      return TCL_ERROR;
         1203  +    }
         1204  +    in = fopen(zFile, "rb");
         1205  +    if( in==0 ){
         1206  +      Tcl_AppendResult(interp, "Error: cannot open file: ", zFile, NULL);
         1207  +      sqlite3_finalize(pStmt);
         1208  +      return TCL_ERROR;
         1209  +    }
         1210  +    azCol = malloc( sizeof(azCol[0])*(nCol+1) );
         1211  +    if( azCol==0 ) {
         1212  +      Tcl_AppendResult(interp, "Error: can't malloc()", 0);
         1213  +      return TCL_ERROR;
         1214  +    }
         1215  +    sqlite3_exec(pDb->db, "BEGIN", 0, 0, 0);
         1216  +    zCommit = "COMMIT";
         1217  +    while( (zLine = local_getline(0, in))!=0 ){
         1218  +      char *z;
         1219  +      i = 0;
         1220  +      lineno++;
         1221  +      azCol[0] = zLine;
         1222  +      for(i=0, z=zLine; *z; z++){
         1223  +        if( *z==zSep[0] && strncmp(z, zSep, nSep)==0 ){
         1224  +          *z = 0;
         1225  +          i++;
         1226  +          if( i<nCol ){
         1227  +            azCol[i] = &z[nSep];
         1228  +            z += nSep-1;
         1229  +          }
         1230  +        }
         1231  +      }
         1232  +      if( i+1!=nCol ){
         1233  +        char *zErr;
         1234  +        zErr = malloc(200 + strlen(zFile));
         1235  +        sprintf(zErr,"Error: %s line %d: expected %d columns of data but found %d",
         1236  +           zFile, lineno, nCol, i+1);
         1237  +        Tcl_AppendResult(interp, zErr, 0);
         1238  +        free(zErr);
         1239  +        zCommit = "ROLLBACK";
         1240  +        break;
         1241  +      }
         1242  +      for(i=0; i<nCol; i++){
         1243  +        /* check for null data, if so, bind as null */
         1244  +        if ((nNull>0 && strcmp(azCol[i], zNull)==0) || strlen(azCol[i])==0) {
         1245  +          sqlite3_bind_null(pStmt, i+1);
         1246  +        }else{
         1247  +          sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC);
         1248  +        }
         1249  +      }
         1250  +      sqlite3_step(pStmt);
         1251  +      rc = sqlite3_reset(pStmt);
         1252  +      free(zLine);
         1253  +      if( rc!=SQLITE_OK ){
         1254  +        Tcl_AppendResult(interp,"Error: ", sqlite3_errmsg(pDb->db), 0);
         1255  +        zCommit = "ROLLBACK";
         1256  +        break;
         1257  +      }
         1258  +    }
         1259  +    free(azCol);
         1260  +    fclose(in);
         1261  +    sqlite3_finalize(pStmt);
         1262  +    sqlite3_exec(pDb->db, zCommit, 0, 0, 0);
         1263  +
         1264  +    if( zCommit[0] == 'C' ){
         1265  +      /* success, set result as number of lines processed */
         1266  +      pResult = Tcl_GetObjResult(interp);
         1267  +      Tcl_SetIntObj(pResult, lineno);
         1268  +      rc = TCL_OK;
         1269  +    }else{
         1270  +      /* failure, append lineno where failed */
         1271  +      sprintf(zLineNum,"%d",lineno);
         1272  +      Tcl_AppendResult(interp,", failed while processing line: ",zLineNum,0);
         1273  +      rc = TCL_ERROR;
         1274  +    }
         1275  +    break;
         1276  +  }
         1277  +
  1044   1278   
  1045   1279     } /* End of the SWITCH statement */
  1046   1280     return rc;
  1047   1281   }
  1048   1282   
  1049   1283   /*
  1050   1284   **   sqlite3 DBNAME FILENAME ?MODE? ?-key KEY?

Changes to test/tclsqlite.test.

    11     11   # This file implements regression tests for TCL interface to the
    12     12   # SQLite library. 
    13     13   #
    14     14   # Actually, all tests are based on the TCL interface, so the main
    15     15   # interface is pretty well tested.  This file contains some addition
    16     16   # tests for fringe issues that the main test suite does not cover.
    17     17   #
    18         -# $Id: tclsqlite.test,v 1.34 2004/11/23 10:13:03 danielk1977 Exp $
           18  +# $Id: tclsqlite.test,v 1.35 2004/12/17 15:41:13 tpoindex Exp $
    19     19   
    20     20   set testdir [file dirname $argv0]
    21     21   source $testdir/tester.tcl
    22     22   
    23     23   # Check the error messages generated by tclsqlite
    24     24   #
    25     25   if {[sqlite3 -has-codec]} {
................................................................................
    30     30   do_test tcl-1.1 {
    31     31     set v [catch {sqlite3 bogus} msg]
    32     32     lappend v $msg
    33     33   } [list 1 "wrong # args: should be \"$r\""]
    34     34   do_test tcl-1.2 {
    35     35     set v [catch {db bogus} msg]
    36     36     lappend v $msg
    37         -} {1 {bad option "bogus": must be authorizer, busy, changes, close, collate, collation_needed, commit_hook, complete, errorcode, eval, function, last_insert_rowid, onecolumn, progress, rekey, timeout, total_changes, or trace}}
           37  +} {1 {bad option "bogus": must be authorizer, busy, changes, close, collate, collation_needed, commit_hook, complete, copy, errorcode, eval, function, last_insert_rowid, onecolumn, progress, rekey, timeout, total_changes, or trace}}
    38     38   do_test tcl-1.3 {
    39     39     execsql {CREATE TABLE t1(a int, b int)}
    40     40     execsql {INSERT INTO t1 VALUES(10,20)}
    41     41     set v [catch {
    42     42       db eval {SELECT * FROM t1} data {
    43     43         error "The error message"
    44     44       }
................................................................................
   128    128     set v [catch {db collation_needed} msg]
   129    129     lappend v $msg
   130    130   } {1 {wrong # args: should be "db collation_needed SCRIPT"}}
   131    131   do_test tcl-1.19 {
   132    132     set v [catch {db total_changes xyz} msg]
   133    133     lappend v $msg
   134    134   } {1 {wrong # args: should be "db total_changes "}}
          135  +do_test tcl-1.20 {
          136  +  set v [catch {db copy} msg]
          137  +  lappend v $msg
          138  +} {1 {wrong # args: should be "db copy CONFLICT-ALGORITHM TABLE FILENAME ?SEPARATOR? ?NULLINDICATOR?"}}
   135    139   
   136    140   
   137    141   if {[sqlite3 -tcl-uses-utf]} {
   138    142     catch {unset ::result}
   139    143     do_test tcl-2.1 {
   140    144       execsql "CREATE TABLE t\u0123x(a int, b\u1235 float)"
   141    145     } {}

Changes to www/tclsqlite.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the tclsqlite.html file.
     3      3   #
     4         -set rcsid {$Id: tclsqlite.tcl,v 1.11 2004/08/26 01:12:14 drh Exp $}
            4  +set rcsid {$Id: tclsqlite.tcl,v 1.12 2004/12/17 15:41:13 tpoindex Exp $}
     5      5   source common.tcl
     6      6   header {The Tcl interface to the SQLite library}
     7      7   proc METHOD {name text} {
     8      8     puts "<a name=\"$name\">\n<h3>The \"$name\" method</h3>\n"
     9      9     puts $text
    10     10   }
    11     11   puts {
................................................................................
    42     42   <p>
    43     43   The name of the database is just the name of a disk file in which
    44     44   the database is stored.
    45     45   </p>
    46     46   
    47     47   <p>
    48     48   Once an SQLite database is open, it can be controlled using 
    49         -methods of the <i>dbcmd</i>.  There are currently 17 methods
           49  +methods of the <i>dbcmd</i>.  There are currently 18 methods
    50     50   defined:</p>
    51     51   
    52     52   <p>
    53     53   <ul>
    54     54   }
    55     55   foreach m [lsort {
    56     56    authorizer
................................................................................
    57     57    busy
    58     58    changes
    59     59    close
    60     60    collate
    61     61    collation_needed
    62     62    commit_hook
    63     63    complete
           64  + copy
    64     65    errorcode
    65     66    eval
    66     67    function
    67     68    last_insert_rowid
    68     69    onecolumn
    69     70    progress
    70     71    timeout
................................................................................
   237    238   there is more to be entered.</p>
   238    239   
   239    240   <p>The "complete" method is useful when building interactive applications
   240    241   in order to know when the user has finished entering a line of SQL code.
   241    242   This is really just an interface to the <b>sqlite3_complete()</b> C
   242    243   function.  Refer to the <a href="c_interface.html">C/C++ interface</a>
   243    244   specification for additional information.</p>
          245  +}
          246  +
          247  +##############################################################################
          248  +METHOD copy {
          249  +
          250  +<p>
          251  +The "copy" method copies data from a file into a table.
          252  +It returns the number of rows processed successfully from the file.
          253  +The syntax of the copy method looks like this:</p>
          254  +
          255  +<blockquote>
          256  +<i>dbcmd</i>&nbsp;&nbsp;<b>copy</b>&nbsp;&nbsp;<i>conflict-algorithm</i>
          257  +&nbsp;&nbsp;<i>table-name&nbsp;</i>&nbsp;&nbsp;<i>file-name&nbsp;</i>
          258  +&nbsp;&nbsp;&nbsp;&nbsp;?<i>column-separator&nbsp;</i>?
          259  +&nbsp;&nbsp;?<i>null-indicator</i>?
          260  +</blockquote>
          261  +
          262  +<p>Conflict-alogrithm must be one of the SQLite conflict algorithms for
          263  +the INSERT statement: <i>rollback</i>, <i>abort</i>,
          264  +<i>fail</i>,<i>ignore</i>, or <i>replace</i>. See the SQLite Language
          265  +section for <a href="lang.html#conflict">ON CONFLICT</a> for more information.
          266  +The conflict-algorithm must be specified in lower case.
          267  +</p>
          268  +
          269  +<p>Table-name must already exists as a table.  File-name must exist, and
          270  +each row must contain the same number of columns as defined in the table.
          271  +If a line in the file contains more or less than the number of columns defined,
          272  +the copy method rollbacks any inserts, and returns an error.</p>
          273  +
          274  +<p>Column-separator is an optional column separator string.  The default is
          275  +the ASCII tab character \t. </p>
          276  +
          277  +<p>Null-indicator is an optional string that indicates a column value is null.
          278  +The default is an empty string.  Note that column-separator and
          279  +null-indicator are optional positional arguments; if null-indicator
          280  +is specified, a column-separator argument must be specifed and
          281  +precede the null-indicator argument.</p>
          282  +
          283  +<p>The copy method implements similar functionality to the <b>.import</b>
          284  +SQLite shell command. 
          285  +The SQLite 2.x <a href="lang.html#copy"><b>COPY</b></a> statement 
          286  +(using the PostgreSQL COPY file format)
          287  +can be implemented with this method as:</p>
          288  +
          289  +<blockquote>
          290  +dbcmd&nbsp;&nbsp;copy&nbsp;&nbsp;$conflictalgo
          291  +&nbsp;&nbsp;$tablename&nbsp;&nbsp;&nbsp;$filename&nbsp;
          292  +&nbsp;&nbsp;&nbsp;&nbsp;\t&nbsp;
          293  +&nbsp;&nbsp;\\N
          294  +</blockquote>
          295  +
   244    296   }
   245    297   
   246    298   ##############################################################################
   247    299   METHOD timeout {
   248    300   
   249    301   <p>The "timeout" method is used to control how long the SQLite library
   250    302   will wait for locks to clear before giving up on a database transaction.