Index: src/tclsqlite.c ================================================================== --- src/tclsqlite.c +++ src/tclsqlite.c @@ -9,11 +9,11 @@ ** May you share freely, never taking more than you give. ** ************************************************************************* ** 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 */ #include "sqliteInt.h" #include "hash.h" @@ -331,10 +331,58 @@ #else pVal = Tcl_NewStringObj(zText, -1); #endif return pVal; } + +/* +** 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 @@ -352,24 +400,27 @@ int choice; int rc = TCL_OK; 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 ..."); return TCL_ERROR; } @@ -1039,10 +1090,193 @@ sqlite3_trace(pDb->db, 0, 0); } } 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; idb, 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( i0 && 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; } Index: test/tclsqlite.test ================================================================== --- test/tclsqlite.test +++ test/tclsqlite.test @@ -13,11 +13,11 @@ # # Actually, all tests are based on the TCL interface, so the main # 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 # Check the error messages generated by tclsqlite @@ -32,11 +32,11 @@ lappend v $msg } [list 1 "wrong # args: should be \"$r\""] 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)} set v [catch { db eval {SELECT * FROM t1} data { @@ -130,10 +130,14 @@ } {1 {wrong # args: should be "db collation_needed SCRIPT"}} 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]} { catch {unset ::result} do_test tcl-2.1 { Index: www/tclsqlite.tcl ================================================================== --- www/tclsqlite.tcl +++ www/tclsqlite.tcl @@ -1,9 +1,9 @@ # # 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} { puts "\n

The \"$name\" method

\n" puts $text @@ -44,11 +44,11 @@ the database is stored.

Once an SQLite database is open, it can be controlled using -methods of the dbcmd. There are currently 17 methods +methods of the dbcmd. There are currently 18 methods defined: