diff options
| -rw-r--r-- | manifest | 18 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | src/tclsqlite.c | 252 | ||||
| -rw-r--r-- | test/tclsqlite.test | 8 | ||||
| -rw-r--r-- | www/tclsqlite.tcl | 56 |
5 files changed, 313 insertions, 23 deletions
@@ -1,5 +1,5 @@ -C Make\ssure\sLIMITs\sare\shandled\scorrectly\son\sUNION\soperators.\s\sTicket\s#1035.\s(CVS\s2166) -D 2004-12-16T21:09:17 +C Add\s'copy'\smethod\sfor\stcl\sinterface.\s\sBehaves\ssimilar\sto\sshell\s.import\sor\sCOPY\sstatment\sin\s2.x.\s(CVS\s2167) +D 2004-12-17T15:41:12 F Makefile.in da09f379b80c8cd78d78abaa0f32ca90a124e884 F Makefile.linux-gcc a9e5a0d309fa7c38e7c14d3ecf7690879d3a5457 F README a01693e454a00cc117967e3f9fdab2d4d52e9bc1 @@ -64,7 +64,7 @@ F src/shell.c e8f4f486cbf6e60d81173146ac8a6522c930fa51 F src/sqlite.h.in fa75850f412808afd38fddc1fd6456f4efc6fb97 F src/sqliteInt.h e0c5c1af95e975645c7a09b151af258d6fca1c53 F src/table.c 25b3ff2b39b7d87e8d4a5da0713d68dfc06cbee9 -F src/tclsqlite.c 68b45ae5a96424abdc1732cb03b1efbb0c1821b3 +F src/tclsqlite.c b2b53e9dfbd0407fbfcd43cff4f34bdd2115c19a F src/test1.c b7d94c54e58f95452387a5cabdf98b2be8059f29 F src/test2.c b11fa244fff02190707dd0879987c37c75e61fc8 F src/test3.c 6f1ec93e13632a004b527049535079eda84c459d @@ -180,7 +180,7 @@ F test/sort.test 87882e6c72a75d45e98a1c802c1ded0eac557d85 F test/subselect.test ff3850d0aab1443dafa4ecbdab1d01e58e7b366d F test/table.test b8b0bee2ac2f3d36a674bc68344c1bdd80e99a18 F test/tableapi.test b21ab097e87a5484bb61029e69e1a4e5c5e65ede -F test/tclsqlite.test 861464afab6e28a0f3b236984cb24b0a7a20e508 +F test/tclsqlite.test 8b9dae3b68a899e5cbaebe6bb51cfc27f2503c6d F test/temptable.test 63a16e3ad19adf073cfbcdf7624c92ac5236522c F test/tester.tcl e7ebedf60c8ebbb0a9af4950981babfbb80cc9a7 F test/thread1.test 776c9e459b75ba905193b351926ac4019b049f35 @@ -259,11 +259,11 @@ F www/quickstart.tcl 6f6f694b6139be2d967b1492eb9a6bdf7058aa60 F www/speed.tcl de99c82c4729a10b6733463636f15473c4ec95bc F www/sqlite.tcl b51fd15f0531a54874de785a9efba323eecd5975 F www/support.tcl 3955da0fd82be68cc5c83d347c05095e80967051 -F www/tclsqlite.tcl 560ecd6a916b320e59f2917317398f3d59b7cc25 +F www/tclsqlite.tcl e73f8f8e5f20e8277619433f7970060ab01088fc F www/vdbe.tcl 095f106d93875c94b47367384ebc870517431618 F www/version3.tcl 092a01f5ef430d2c4acc0ae558d74c4bb89638a0 F www/whentouse.tcl fdacb0ba2d39831e8a6240d05a490026ad4c4e4c -P d012628a78edaa8d3cb438a7dcc3770b2eb8588b -R bd08228a6f97570ef1438e0425471e15 -U drh -Z fcfd946d19b12e062ea66108631d01be +P ece0085f86bd715c95a6c59f41b4a97de2555faf +R a2db8614c6f9e04b98748c322906c74d +U tpoindex +Z 8ba5c26a738ea01e3fbfd3f148568092 diff --git a/manifest.uuid b/manifest.uuid index 30254276c..07c2ae373 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -ece0085f86bd715c95a6c59f41b4a97de2555faf
\ No newline at end of file +a9311d9df054a91e231d4e4332df0d661675744d
\ No newline at end of file diff --git a/src/tclsqlite.c b/src/tclsqlite.c index 8452e372e..83d4a6396 100644 --- a/src/tclsqlite.c +++ b/src/tclsqlite.c @@ -11,7 +11,7 @@ ************************************************************************* ** A TCL Interface to SQLite ** -** $Id: tclsqlite.c,v 1.109 2004/12/02 20:17:02 drh Exp $ +** $Id: tclsqlite.c,v 1.110 2004/12/17 15:41:12 tpoindex Exp $ */ #ifndef NO_TCL /* Omit this whole file if TCL is unavailable */ @@ -335,6 +335,54 @@ static Tcl_Obj *dbTextToObj(char const *zText){ } /* +** This routine reads a line of text from FILE in, stores +** the text in memory obtained from malloc() and returns a pointer +** to the text. NULL is returned at end of file, or if malloc() +** fails. +** +** The interface is like "readline" but no command-line editing +** is done. +** +** copied from shell.c from '.import' command +*/ +static char *local_getline(char *zPrompt, FILE *in){ + char *zLine; + int nLine; + int n; + int eol; + + nLine = 100; + zLine = malloc( nLine ); + if( zLine==0 ) return 0; + n = 0; + eol = 0; + while( !eol ){ + if( n+100>nLine ){ + nLine = nLine*2 + 100; + zLine = realloc(zLine, nLine); + if( zLine==0 ) return 0; + } + if( fgets(&zLine[n], nLine - n, in)==0 ){ + if( n==0 ){ + free(zLine); + return 0; + } + zLine[n] = 0; + eol = 1; + break; + } + while( zLine[n] ){ n++; } + if( n>0 && zLine[n-1]=='\n' ){ + n--; + zLine[n] = 0; + eol = 1; + } + } + zLine = realloc( zLine, n+1 ); + return zLine; +} + +/* ** The "sqlite" command below creates a new Tcl command for each ** connection it opens to an SQLite database. This routine is invoked ** whenever one of those connection-specific commands is executed @@ -354,20 +402,23 @@ static int DbObjCmd(void *cd, Tcl_Interp *interp, int objc,Tcl_Obj *const*objv){ static const char *DB_strs[] = { "authorizer", "busy", "changes", "close", "collate", "collation_needed", - "commit_hook", "complete", "errorcode", - "eval", "function", "last_insert_rowid", - "onecolumn", "progress", "rekey", - "timeout", "total_changes", "trace", + "commit_hook", "complete", "copy", + "errorcode", "eval", "function", + "last_insert_rowid", "onecolumn", "progress", + "rekey", "timeout", "total_changes", + "trace", 0 }; enum DB_enum { DB_AUTHORIZER, DB_BUSY, DB_CHANGES, DB_CLOSE, DB_COLLATE, DB_COLLATION_NEEDED, - DB_COMMIT_HOOK, DB_COMPLETE, DB_ERRORCODE, - DB_EVAL, DB_FUNCTION, DB_LAST_INSERT_ROWID, - DB_ONECOLUMN, DB_PROGRESS, DB_REKEY, - DB_TIMEOUT, DB_TOTAL_CHANGES, DB_TRACE, + DB_COMMIT_HOOK, DB_COMPLETE, DB_COPY, + DB_ERRORCODE, DB_EVAL, DB_FUNCTION, + DB_LAST_INSERT_ROWID, DB_ONECOLUMN, DB_PROGRESS, + DB_REKEY, DB_TIMEOUT, DB_TOTAL_CHANGES, + DB_TRACE }; + /* don't leave trailing commas on DB_enum, it confuses the AIX xlc compiler */ if( objc<2 ){ Tcl_WrongNumArgs(interp, 1, objv, "SUBCOMMAND ..."); @@ -1042,6 +1093,189 @@ static int DbObjCmd(void *cd, Tcl_Interp *interp, int objc,Tcl_Obj *const*objv){ break; } + /* $db copy conflict-algorithm table filename ?SEPARATOR? ?NULLINDICATOR? + ** + ** Copy data into table from filename, optionally using SEPARATOR + ** as column separators. If a column contains a null string, or the + ** value of NULLINDICATOR, a NULL is inserted for the column. + ** conflict-algorithm is one of the sqlite conflict algorithms: + ** rollback, abort, fail, ignore, replace + ** On success, return the number of lines processed, not necessarily same + ** as 'db changes' due to conflict-algorithm selected. + ** + ** This code is basically an implementation/enhancement of + ** the sqlite3 shell.c ".import" command. + ** + ** This command usage is equivalent to the sqlite2.x COPY statement, + ** which imports file data into a table using the PostgreSQL COPY file format: + ** $db copy $conflit_algo $table_name $filename \t \\N + */ + case DB_COPY: { + char *zSep; + char *zNull; + if( objc<5 || objc>7 ){ + Tcl_WrongNumArgs(interp, 2, objv, "CONFLICT-ALGORITHM TABLE FILENAME ?SEPARATOR? ?NULLINDICATOR?"); + return TCL_ERROR; + } + if( objc>=6 ){ + zSep = Tcl_GetStringFromObj(objv[5], 0); + }else{ + zSep = "\t"; + } + if( objc>=7 ){ + zNull = Tcl_GetStringFromObj(objv[6], 0); + }else{ + zNull = ""; + } + char *zTable; /* Insert data into this table */ + char *zFile; /* The file from which to extract data */ + char *zConflict; /* The conflict algorithm to use */ + sqlite3_stmt *pStmt; /* A statement */ + int rc; /* Result code */ + int nCol; /* Number of columns in the table */ + int nByte; /* Number of bytes in an SQL string */ + int i, j; /* Loop counters */ + int nSep; /* Number of bytes in zSep[] */ + int nNull; /* Number of bytes in zNull[] */ + char *zSql; /* An SQL statement */ + char *zLine; /* A single line of input from the file */ + char **azCol; /* zLine[] broken up into columns */ + char *zCommit; /* How to commit changes */ + FILE *in; /* The input file */ + int lineno = 0; /* Line number of input file */ + char zLineNum[80]; /* Line number print buffer */ + Tcl_Obj *pResult; /* interp result */ + + zConflict = Tcl_GetStringFromObj(objv[2], 0); + zTable = Tcl_GetStringFromObj(objv[3], 0); + zFile = Tcl_GetStringFromObj(objv[4], 0); + nSep = strlen(zSep); + nNull = strlen(zNull); + if( nSep==0 ){ + Tcl_AppendResult(interp, "Error: non-null separator required for copy", 0); + return TCL_ERROR; + } + if(sqlite3StrICmp(zConflict, "rollback") != 0 && + sqlite3StrICmp(zConflict, "abort" ) != 0 && + sqlite3StrICmp(zConflict, "fail" ) != 0 && + sqlite3StrICmp(zConflict, "ignore" ) != 0 && + sqlite3StrICmp(zConflict, "replace" ) != 0 ) { + Tcl_AppendResult(interp, "Error: \"", zConflict, "\", conflict-algorithm must be one of: rollback, abort, fail, ignore, or replace", 0); + return TCL_ERROR; + } + zSql = sqlite3_mprintf("SELECT * FROM '%q'", zTable); + if( zSql==0 ){ + Tcl_AppendResult(interp, "Error: no such table: ", zTable, 0); + return TCL_ERROR; + } + nByte = strlen(zSql); + rc = sqlite3_prepare(pDb->db, zSql, 0, &pStmt, 0); + sqlite3_free(zSql); + if( rc ){ + Tcl_AppendResult(interp, "Error: ", sqlite3_errmsg(pDb->db), 0); + nCol = 0; + }else{ + nCol = sqlite3_column_count(pStmt); + } + sqlite3_finalize(pStmt); + if( nCol==0 ) { + return TCL_ERROR; + } + zSql = malloc( nByte + 50 + nCol*2 ); + if( zSql==0 ) { + Tcl_AppendResult(interp, "Error: can't malloc()", 0); + return TCL_ERROR; + } + sqlite3_snprintf(nByte+50, zSql, "INSERT OR %q INTO '%q' VALUES(?", zConflict, zTable); + j = strlen(zSql); + for(i=1; i<nCol; i++){ + zSql[j++] = ','; + zSql[j++] = '?'; + } + zSql[j++] = ')'; + zSql[j] = 0; + rc = sqlite3_prepare(pDb->db, zSql, 0, &pStmt, 0); + free(zSql); + if( rc ){ + Tcl_AppendResult(interp, "Error: ", sqlite3_errmsg(pDb->db), 0); + sqlite3_finalize(pStmt); + return TCL_ERROR; + } + in = fopen(zFile, "rb"); + if( in==0 ){ + Tcl_AppendResult(interp, "Error: cannot open file: ", zFile, NULL); + sqlite3_finalize(pStmt); + return TCL_ERROR; + } + azCol = malloc( sizeof(azCol[0])*(nCol+1) ); + if( azCol==0 ) { + Tcl_AppendResult(interp, "Error: can't malloc()", 0); + return TCL_ERROR; + } + sqlite3_exec(pDb->db, "BEGIN", 0, 0, 0); + zCommit = "COMMIT"; + while( (zLine = local_getline(0, in))!=0 ){ + char *z; + i = 0; + lineno++; + azCol[0] = zLine; + for(i=0, z=zLine; *z; z++){ + if( *z==zSep[0] && strncmp(z, zSep, nSep)==0 ){ + *z = 0; + i++; + if( i<nCol ){ + azCol[i] = &z[nSep]; + z += nSep-1; + } + } + } + if( i+1!=nCol ){ + char *zErr; + zErr = malloc(200 + strlen(zFile)); + sprintf(zErr,"Error: %s line %d: expected %d columns of data but found %d", + zFile, lineno, nCol, i+1); + Tcl_AppendResult(interp, zErr, 0); + free(zErr); + zCommit = "ROLLBACK"; + break; + } + for(i=0; i<nCol; i++){ + /* check for null data, if so, bind as null */ + if ((nNull>0 && strcmp(azCol[i], zNull)==0) || strlen(azCol[i])==0) { + sqlite3_bind_null(pStmt, i+1); + }else{ + sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC); + } + } + sqlite3_step(pStmt); + rc = sqlite3_reset(pStmt); + free(zLine); + if( rc!=SQLITE_OK ){ + Tcl_AppendResult(interp,"Error: ", sqlite3_errmsg(pDb->db), 0); + zCommit = "ROLLBACK"; + break; + } + } + free(azCol); + fclose(in); + sqlite3_finalize(pStmt); + sqlite3_exec(pDb->db, zCommit, 0, 0, 0); + + if( zCommit[0] == 'C' ){ + /* success, set result as number of lines processed */ + pResult = Tcl_GetObjResult(interp); + Tcl_SetIntObj(pResult, lineno); + rc = TCL_OK; + }else{ + /* failure, append lineno where failed */ + sprintf(zLineNum,"%d",lineno); + Tcl_AppendResult(interp,", failed while processing line: ",zLineNum,0); + rc = TCL_ERROR; + } + break; + } + + } /* End of the SWITCH statement */ return rc; } diff --git a/test/tclsqlite.test b/test/tclsqlite.test index 63586e755..ec4e4220d 100644 --- a/test/tclsqlite.test +++ b/test/tclsqlite.test @@ -15,7 +15,7 @@ # interface is pretty well tested. This file contains some addition # tests for fringe issues that the main test suite does not cover. # -# $Id: tclsqlite.test,v 1.34 2004/11/23 10:13:03 danielk1977 Exp $ +# $Id: tclsqlite.test,v 1.35 2004/12/17 15:41:13 tpoindex Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -34,7 +34,7 @@ do_test tcl-1.1 { do_test tcl-1.2 { set v [catch {db bogus} msg] lappend v $msg -} {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}} +} {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}} do_test tcl-1.3 { execsql {CREATE TABLE t1(a int, b int)} execsql {INSERT INTO t1 VALUES(10,20)} @@ -132,6 +132,10 @@ do_test tcl-1.19 { set v [catch {db total_changes xyz} msg] lappend v $msg } {1 {wrong # args: should be "db total_changes "}} +do_test tcl-1.20 { + set v [catch {db copy} msg] + lappend v $msg +} {1 {wrong # args: should be "db copy CONFLICT-ALGORITHM TABLE FILENAME ?SEPARATOR? ?NULLINDICATOR?"}} if {[sqlite3 -tcl-uses-utf]} { diff --git a/www/tclsqlite.tcl b/www/tclsqlite.tcl index a28486da7..69e00e8c8 100644 --- a/www/tclsqlite.tcl +++ b/www/tclsqlite.tcl @@ -1,7 +1,7 @@ # # Run this Tcl script to generate the tclsqlite.html file. # -set rcsid {$Id: tclsqlite.tcl,v 1.11 2004/08/26 01:12:14 drh Exp $} +set rcsid {$Id: tclsqlite.tcl,v 1.12 2004/12/17 15:41:13 tpoindex Exp $} source common.tcl header {The Tcl interface to the SQLite library} proc METHOD {name text} { @@ -46,7 +46,7 @@ the database is stored. <p> Once an SQLite database is open, it can be controlled using -methods of the <i>dbcmd</i>. There are currently 17 methods +methods of the <i>dbcmd</i>. There are currently 18 methods defined:</p> <p> @@ -61,6 +61,7 @@ foreach m [lsort { collation_needed commit_hook complete + copy errorcode eval function @@ -244,6 +245,57 @@ specification for additional information.</p> } ############################################################################## +METHOD copy { + +<p> +The "copy" method copies data from a file into a table. +It returns the number of rows processed successfully from the file. +The syntax of the copy method looks like this:</p> + +<blockquote> +<i>dbcmd</i> <b>copy</b> <i>conflict-algorithm</i> + <i>table-name </i> <i>file-name </i> + ?<i>column-separator </i>? + ?<i>null-indicator</i>? +</blockquote> + +<p>Conflict-alogrithm must be one of the SQLite conflict algorithms for +the INSERT statement: <i>rollback</i>, <i>abort</i>, +<i>fail</i>,<i>ignore</i>, or <i>replace</i>. See the SQLite Language +section for <a href="lang.html#conflict">ON CONFLICT</a> for more information. +The conflict-algorithm must be specified in lower case. +</p> + +<p>Table-name must already exists as a table. File-name must exist, and +each row must contain the same number of columns as defined in the table. +If a line in the file contains more or less than the number of columns defined, +the copy method rollbacks any inserts, and returns an error.</p> + +<p>Column-separator is an optional column separator string. The default is +the ASCII tab character \t. </p> + +<p>Null-indicator is an optional string that indicates a column value is null. +The default is an empty string. Note that column-separator and +null-indicator are optional positional arguments; if null-indicator +is specified, a column-separator argument must be specifed and +precede the null-indicator argument.</p> + +<p>The copy method implements similar functionality to the <b>.import</b> +SQLite shell command. +The SQLite 2.x <a href="lang.html#copy"><b>COPY</b></a> statement +(using the PostgreSQL COPY file format) +can be implemented with this method as:</p> + +<blockquote> +dbcmd copy $conflictalgo + $tablename $filename + \t + \\N +</blockquote> + +} + +############################################################################## METHOD timeout { <p>The "timeout" method is used to control how long the SQLite library |