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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
a9311d9df054a91e231d4e4332df0d66 |
User & Date: | tpoindex 2004-12-17 15:41:12.000 |
Context
2004-12-17
| ||
20:48 | Fix a C++-ism in the previous change to tclsqlite.c. (CVS 2168) (check-in: b49b8fdd11 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: a9311d9df0 user: tpoindex tags: trunk) | |
2004-12-16
| ||
21:09 | Make sure LIMITs are handled correctly on UNION operators. Ticket #1035. (CVS 2166) (check-in: ece0085f86 user: drh tags: trunk) | |
Changes
Changes to src/tclsqlite.c.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** A TCL Interface to SQLite ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** A TCL Interface to SQLite ** ** $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" #include "tcl.h" #include <stdlib.h> |
︙ | ︙ | |||
329 330 331 332 333 334 335 336 337 338 339 340 341 342 | pVal = Tcl_NewStringObj(Tcl_DStringValue(&dCol), -1); Tcl_DStringFree(&dCol); #else pVal = Tcl_NewStringObj(zText, -1); #endif return pVal; } /* ** 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 ** in Tcl. For example, if you run Tcl code like this: ** | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 | pVal = Tcl_NewStringObj(Tcl_DStringValue(&dCol), -1); Tcl_DStringFree(&dCol); #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 ** in Tcl. For example, if you run Tcl code like this: ** |
︙ | ︙ | |||
350 351 352 353 354 355 356 | static int DbObjCmd(void *cd, Tcl_Interp *interp, int objc,Tcl_Obj *const*objv){ SqliteDb *pDb = (SqliteDb*)cd; int choice; int rc = TCL_OK; static const char *DB_strs[] = { "authorizer", "busy", "changes", "close", "collate", "collation_needed", | | | | | > | | | | > > | 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 | static int DbObjCmd(void *cd, Tcl_Interp *interp, int objc,Tcl_Obj *const*objv){ SqliteDb *pDb = (SqliteDb*)cd; int choice; int rc = TCL_OK; static const char *DB_strs[] = { "authorizer", "busy", "changes", "close", "collate", "collation_needed", "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_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; } if( Tcl_GetIndexFromObj(interp, objv[1], DB_strs, "option", 0, &choice) ){ return TCL_ERROR; |
︙ | ︙ | |||
1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 | sqlite3_trace(pDb->db, DbTraceHandler, pDb); }else{ sqlite3_trace(pDb->db, 0, 0); } } break; } } /* End of the SWITCH statement */ return rc; } /* ** sqlite3 DBNAME FILENAME ?MODE? ?-key KEY? | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 | sqlite3_trace(pDb->db, DbTraceHandler, pDb); }else{ 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; 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; } /* ** sqlite3 DBNAME FILENAME ?MODE? ?-key KEY? |
︙ | ︙ |
Changes to test/tclsqlite.test.
︙ | ︙ | |||
11 12 13 14 15 16 17 | # This file implements regression tests for TCL interface to the # SQLite library. # # 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. # | | | | 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | # This file implements regression tests for TCL interface to the # SQLite library. # # 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.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 # if {[sqlite3 -has-codec]} { set r "sqlite_orig HANDLE FILENAME ?-key CODEC-KEY?" } else { set r "sqlite3 HANDLE FILENAME ?MODE?" } do_test tcl-1.1 { set v [catch {sqlite3 bogus} msg] 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, 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 { error "The error message" } |
︙ | ︙ | |||
128 129 130 131 132 133 134 135 136 137 138 139 140 141 | set v [catch {db collation_needed} msg] lappend v $msg } {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 "}} if {[sqlite3 -tcl-uses-utf]} { catch {unset ::result} do_test tcl-2.1 { execsql "CREATE TABLE t\u0123x(a int, b\u1235 float)" } {} | > > > > | 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 | set v [catch {db collation_needed} msg] lappend v $msg } {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 { execsql "CREATE TABLE t\u0123x(a int, b\u1235 float)" } {} |
︙ | ︙ |
Changes to www/tclsqlite.tcl.
1 2 3 | # # Run this Tcl script to generate the tclsqlite.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 | # # Run this Tcl script to generate the tclsqlite.html file. # 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 "<a name=\"$name\">\n<h3>The \"$name\" method</h3>\n" puts $text } puts { |
︙ | ︙ | |||
42 43 44 45 46 47 48 | <p> The name of the database is just the name of a disk file in which the database is stored. </p> <p> Once an SQLite database is open, it can be controlled using | | > | 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | <p> The name of the database is just the name of a disk file in which the database is stored. </p> <p> Once an SQLite database is open, it can be controlled using methods of the <i>dbcmd</i>. There are currently 18 methods defined:</p> <p> <ul> } foreach m [lsort { authorizer busy changes close collate collation_needed commit_hook complete copy errorcode eval function last_insert_rowid onecolumn progress timeout |
︙ | ︙ | |||
237 238 239 240 241 242 243 244 245 246 247 248 249 250 | there is more to be entered.</p> <p>The "complete" method is useful when building interactive applications in order to know when the user has finished entering a line of SQL code. This is really just an interface to the <b>sqlite3_complete()</b> C function. Refer to the <a href="c_interface.html">C/C++ interface</a> specification for additional information.</p> } ############################################################################## METHOD timeout { <p>The "timeout" method is used to control how long the SQLite library will wait for locks to clear before giving up on a database transaction. | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 | there is more to be entered.</p> <p>The "complete" method is useful when building interactive applications in order to know when the user has finished entering a line of SQL code. This is really just an interface to the <b>sqlite3_complete()</b> C function. Refer to the <a href="c_interface.html">C/C++ interface</a> 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 will wait for locks to clear before giving up on a database transaction. |
︙ | ︙ |