Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | :-) (CVS 125) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
ab9c533a3a256ca9d59a6a580c6064c9 |
User & Date: | drh 2000-08-04 13:49:02.000 |
Context
2000-08-04
| ||
13:49 | :-) (CVS 1702) (check-in: 14785d94fb user: drh tags: trunk) | |
13:49 | :-) (CVS 125) (check-in: ab9c533a3a user: drh tags: trunk) | |
2000-08-03
| ||
15:13 | spelling error (CVS 124) (check-in: 577421e5d3 user: drh tags: trunk) | |
Changes
Changes to src/tclsqlite.c.
︙ | ︙ | |||
19 20 21 22 23 24 25 | ** Author contact information: ** drh@hwaci.com ** http://www.hwaci.com/drh/ ** ************************************************************************* ** A TCL Interface to SQLite ** | | > > > > > > > > > > > | 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 45 46 47 48 49 | ** Author contact information: ** drh@hwaci.com ** http://www.hwaci.com/drh/ ** ************************************************************************* ** A TCL Interface to SQLite ** ** $Id: tclsqlite.c,v 1.6 2000/08/04 13:49:02 drh Exp $ */ #include "sqlite.h" #include <tcl.h> #include <stdlib.h> #include <string.h> /* ** There is one instance of this structure for each SQLite database ** that has been opened by the SQLite TCL interface. */ typedef struct SqliteDb SqliteDb; struct SqliteDb { sqlite *db; /* The "real" database structure */ Tcl_Interp *interp; /* The interpreter used for this database */ char *zBusy; /* The name of the busy callback routine */ }; /* ** An instance of this structure passes information thru the sqlite ** logic from the original TCL command into the callback routine. */ typedef struct CallbackData CallbackData; struct CallbackData { |
︙ | ︙ | |||
77 78 79 80 81 82 83 | return rc; } /* ** Called when the command is deleted. */ static void DbDeleteCmd(void *db){ | > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 | return rc; } /* ** Called when the command is deleted. */ static void DbDeleteCmd(void *db){ SqliteDb *pDb = (SqliteDb*)db; sqlite_close(pDb->db); if( pDb->zBusy ){ Tcl_Free(pDb->zBusy); } Tcl_Free((char*)pDb); } /* ** This routine is called when a database file is locked while trying ** to execute SQL. */ static int DbBusyHandler(void *cd, const char *zTable, int nTries){ SqliteDb *pDb = (SqliteDb*)cd; int rc; char zVal[30]; char *zCmd; char *zResult; Tcl_DString cmd; Tcl_DStringInit(&cmd); Tcl_DStringAppend(&cmd, pDb->zBusy, -1); Tcl_DStringAppendElement(&cmd, zTable); sprintf(zVal, " %d", nTries); Tcl_DStringAppend(&cmd, zVal, -1); zCmd = Tcl_DStringValue(&cmd); rc = Tcl_Eval(pDb->interp, zCmd); Tcl_DStringFree(&cmd); if( rc!=TCL_OK || atoi(Tcl_GetStringResult(pDb->interp)) ){ return 0; } return 1; } /* ** 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: ** ** sqlite db1 "my_database" ** db1 close ** ** The first command opens a connection to the "my_database" database ** and calls that connection "db1". The second command causes this ** subroutine to be invoked. */ static int DbCmd(void *cd, Tcl_Interp *interp, int argc, char **argv){ char *z; int n, c; SqliteDb *pDb = (SqliteDb*)cd; if( argc<2 ){ Tcl_AppendResult(interp,"wrong # args: should be \"", argv[0], " SUBCOMMAND ...\"", 0); return TCL_ERROR; } z = argv[1]; n = strlen(z); c = z[0]; /* $db busy ?CALLBACK? ** ** Invoke the given callback if an SQL statement attempts to open ** a locked database file. */ if( c=='b' && strncmp(z,"busy",n)==0 ){ if( argc>3 ){ Tcl_AppendResult(interp,"wrong # args: should be \"", argv[0], " busy ?CALLBACK?", 0); return TCL_ERROR; }else if( argc==2 ){ if( pDb->zBusy ){ Tcl_AppendResult(interp, pDb->zBusy, 0); } }else{ if( pDb->zBusy ){ Tcl_Free(pDb->zBusy); pDb->zBusy = 0; } if( argv[2][0] ){ pDb->zBusy = Tcl_Alloc( strlen(argv[2]) + 1 ); if( pDb->zBusy ){ strcpy(pDb->zBusy, argv[2]); } } if( pDb->zBusy ){ pDb->interp = interp; sqlite_busy_handler(pDb->db, DbBusyHandler, pDb); } } }else /* $db close ** ** Shutdown the database */ if( c=='c' && n>=2 && strncmp(z,"close",n)==0 ){ Tcl_DeleteCommand(interp, argv[0]); |
︙ | ︙ | |||
135 136 137 138 139 140 141 | Tcl_SetResult(interp, zRes, TCL_VOLATILE); }else /* ** $db eval $sql ?array { ...code... }? ** ** The SQL statement in $sql is evaluated. For each row, the values are | | > | | > | > > > > > > > > > > > > > > > < > | 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 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 | Tcl_SetResult(interp, zRes, TCL_VOLATILE); }else /* ** $db eval $sql ?array { ...code... }? ** ** The SQL statement in $sql is evaluated. For each row, the values are ** placed in elements of the array named "array" and ...code... is executed. ** If "array" and "code" are omitted, then no callback is every invoked. ** If "array" is an empty string, then the values are placed in variables ** that have the same name as the fields extracted by the query. */ if( c=='e' && strncmp(z,"eval",n)==0 ){ CallbackData cbData; char *zErrMsg; int rc; if( argc!=5 && argc!=3 ){ Tcl_AppendResult(interp,"wrong # args: should be \"", argv[0], " eval SQL ?ARRAY-NAME CODE?", 0); return TCL_ERROR; } pDb->interp = interp; if( argc==5 ){ cbData.interp = interp; cbData.once = 1; cbData.zArray = argv[3]; cbData.zCode = argv[4]; zErrMsg = 0; rc = sqlite_exec(pDb->db, argv[2], DbEvalCallback, &cbData, &zErrMsg); }else{ rc = sqlite_exec(pDb->db, argv[2], 0, 0, &zErrMsg); } if( zErrMsg ){ Tcl_SetResult(interp, zErrMsg, TCL_VOLATILE); free(zErrMsg); } return rc; }else /* ** $db timeout MILLESECONDS ** ** Delay for the number of milliseconds specified when a file is locked. */ if( c=='t' && strncmp(z,"timeout",n)==0 ){ int ms; if( argc!=3 ){ Tcl_AppendResult(interp,"wrong # args: should be \"", argv[0], " timeout MILLISECONDS", 0); return TCL_ERROR; } if( Tcl_GetInt(interp, argv[2], &ms) ) return TCL_ERROR; sqlite_busy_timeout(pDb->db, ms); }else /* The default */ { Tcl_AppendResult(interp,"unknown subcommand \"", z, "\" - should be one of: close complete eval", 0); return TCL_ERROR; } return TCL_OK; } |
︙ | ︙ | |||
193 194 195 196 197 198 199 | ** connection is deleted when the DBNAME command is deleted. ** ** The second argument is the name of the directory that contains ** the sqlite database that is to be accessed. */ static int DbMain(void *cd, Tcl_Interp *interp, int argc, char **argv){ int mode; | | > > > > > > | | > | | 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 | ** connection is deleted when the DBNAME command is deleted. ** ** The second argument is the name of the directory that contains ** the sqlite database that is to be accessed. */ static int DbMain(void *cd, Tcl_Interp *interp, int argc, char **argv){ int mode; SqliteDb *p; char *zErrMsg; if( argc!=3 && argc!=4 ){ Tcl_AppendResult(interp,"wrong # args: should be \"", argv[0], " HANDLE FILENAME ?MODE?\"", 0); return TCL_ERROR; } if( argc==3 ){ mode = 0666; }else if( Tcl_GetInt(interp, argv[3], &mode)!=TCL_OK ){ return TCL_ERROR; } zErrMsg = 0; p = Tcl_Alloc( sizeof(*p) ); if( p==0 ){ Tcl_SetResult(interp, "malloc failed", TCL_STATIC); return TCL_ERROR; } memset(p, 0, sizeof(*p)); p->db = sqlite_open(argv[2], mode, &zErrMsg); if( p->db==0 ){ Tcl_SetResult(interp, zErrMsg, TCL_VOLATILE); Tcl_Free((char*)p); free(zErrMsg); return TCL_ERROR; } Tcl_CreateCommand(interp, argv[1], DbCmd, (char*)p, DbDeleteCmd); return TCL_OK; } /* ** Initialize this module. ** ** This Tcl module contains only a single new Tcl command named "sqlite". |
︙ | ︙ |
Changes to www/fileformat.tcl.
1 2 3 | # # Run this Tcl script to generate the fileformat.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 | # # Run this Tcl script to generate the fileformat.html file. # set rcsid {$Id: fileformat.tcl,v 1.4 2000/08/04 13:49:03 drh Exp $} puts {<html> <head> <title>The SQLite file format</title> </head> <body bgcolor=white> <h1 align=center> |
︙ | ︙ | |||
211 212 213 214 215 216 217 | are just the text values for <b>a</b> columns of table <b>t1</b>. The data for each record of the index is a list of integers where each integer is the GDBM key for an entry in the <b>t1</b> table that has the corresponding value for the <b>a</b> column.</p> The index entry for -11 contains only a single entry and is 4 bytes in size. The index entry for 10 is 16 bytes in size but contains only 2 entries. The first integer is the number of | | < | 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 | are just the text values for <b>a</b> columns of table <b>t1</b>. The data for each record of the index is a list of integers where each integer is the GDBM key for an entry in the <b>t1</b> table that has the corresponding value for the <b>a</b> column.</p> The index entry for -11 contains only a single entry and is 4 bytes in size. The index entry for 10 is 16 bytes in size but contains only 2 entries. The first integer is the number of entires. The two integer keys follow. The last 4 bytes are unused. } puts { <p><hr /></p> <p><a href="index.html"><img src="/goback.jpg" border=0 /> Back to the SQLite Home Page</a> </p> </body></html>} |
Changes to www/index.tcl.
1 2 3 | # # Run this TCL script to generate HTML for the index.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 | # # Run this TCL script to generate HTML for the index.html file. # set rcsid {$Id: index.tcl,v 1.26 2000/08/04 13:49:03 drh Exp $} puts {<html> <head><title>SQLite: An SQL Database Engine Built Atop GDBM</title></head> <body bgcolor=white> <h1 align=center>SQLite: An SQL Database Engine Built Atop <a href="http://www.gnu.org/software/gdbm/gdbm.html">GDBM</a></h1> <p align=center>} |
︙ | ︙ | |||
128 129 130 131 132 133 134 | <p>You can download a tarball containing all source code for SQLite (including the TCL scripts that generate the HTML files for this website) at <a href="sqlite.tar.gz">sqlite.tar.gz</a>.} puts "This is a [file size sqlite.tar.gz] byte download. The tarball was last modified at [clock format [file mtime sqlite.tar.gz]]" puts {</p> | | > > > | 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 | <p>You can download a tarball containing all source code for SQLite (including the TCL scripts that generate the HTML files for this website) at <a href="sqlite.tar.gz">sqlite.tar.gz</a>.} puts "This is a [file size sqlite.tar.gz] byte download. The tarball was last modified at [clock format [file mtime sqlite.tar.gz]]" puts {</p> <p>To build sqlite under Unix, just unwrap the tarball, create a separate build directory, run configure from the build directory and then type "make". For example:</p> <blockquote><pre> $ tar xzf sqlite.tar.gz <i> Unpacks into directory named "sqlite" </i> $ mkdir bld <i> Create a separate build directory </i> $ cd bld $ ../sqlite/configure $ make <i> Builds "sqlite" and "libsqlite.a" </i> $ make test <i> Optional: run regression tests </i> </pre></blockquote> <p>Instructions for building SQLite for WindowsNT are found <a href="crosscompile.html">here</a>. } puts {<h2>Command-line Usage Example</h2> <p>Download the source archive and compile the <b>sqlite</b> program as described above. The type:</p> |
︙ | ︙ |
Changes to www/lang.tcl.
1 2 3 | # # Run this Tcl script to generate the sqlite.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 | # # Run this Tcl script to generate the sqlite.html file. # set rcsid {$Id: lang.tcl,v 1.5 2000/08/04 13:49:03 drh Exp $} puts {<html> <head> <title>Query Language Understood By SQLite</title> </head> <body bgcolor=white> <h1 align=center> |
︙ | ︙ | |||
135 136 137 138 139 140 141 | ON <table-name> ( <column-name> [, <column-name>]* ) } {column-name} { <name> [ ASC | DESC ] } puts { <p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed | | | 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 | ON <table-name> ( <column-name> [, <column-name>]* ) } {column-name} { <name> [ ASC | DESC ] } puts { <p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed by the name of the new index, the keyword "ON", the name of a previously created table that is to be indexed, and a parenthesized list of names of columns in the table that are used for the index key. Each column name can be followed by one of the "ASC" or "DESC" keywords to indicate sort order, but since GDBM does not implement ordered keys, these keywords are ignored.</p> <p>There are no arbitrary limits on the number of indices that can be |
︙ | ︙ |
Changes to www/sqlite.tcl.
1 2 3 | # # Run this Tcl script to generate the sqlite.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 | # # Run this Tcl script to generate the sqlite.html file. # set rcsid {$Id: sqlite.tcl,v 1.12 2000/08/04 13:49:03 drh Exp $} puts {<html> <head> <title>sqlite: A program of interacting with SQLite databases</title> </head> <body bgcolor=white> <h1 align=center> |
︙ | ︙ | |||
24 25 26 27 28 29 30 | <h2>Getting Started</h2> <p>To start the <b>sqlite</b> program, just type "sqlite" followed by the name of an SQLite database. An SQLite database is really just a directory full of GDBM files, so the argument to the sqlite command should really be the name of a directory on your disk. If that directory did not previously contain an SQLite database, a new one | | > | < | 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | <h2>Getting Started</h2> <p>To start the <b>sqlite</b> program, just type "sqlite" followed by the name of an SQLite database. An SQLite database is really just a directory full of GDBM files, so the argument to the sqlite command should really be the name of a directory on your disk. If that directory did not previously contain an SQLite database, a new one is created for you automatically. If the directory did not previously exist, it is automatically created. The <b>sqlite</b> program will then prompt you to enter SQL. Type in SQL statements (terminated by a semicolon), press "Enter" and the SQL will be executed. It's as simple as that!</p> <p>For example, to create a new SQLite database named "ex1" with a single table named "tbl1", you might do this:</p> } proc Code {body} { puts {<blockquote><pre>} regsub -all {&} [string trim $body] {\&} body regsub -all {>} $body {\>} body regsub -all {<} $body {\<} body regsub -all {\(\(\(} $body {<font color="#00671f"><u>} body regsub -all {\)\)\)} $body {</u></font>} body puts $body puts {</pre></blockquote>} } Code { $ (((sqlite ex1))) Enter ".help" for instructions sqlite> (((create table tbl1(one varchar(10), two smallint);))) sqlite> (((insert into tbl1 values('hello!',10);))) sqlite> (((insert into tbl1 values('goodbye', 20);))) sqlite> (((select * from tbl1;))) hello!|10 |
︙ | ︙ | |||
226 227 228 229 230 231 232 | ---------- ---------- hello 10 goodbye 20 sqlite> } puts { | | > > > > > > > > > > > > > > > > > > > > | 226 227 228 229 230 231 232 233 234 235 236 237 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 | ---------- ---------- hello 10 goodbye 20 sqlite> } puts { <p>By default, each column is at least 10 characters wide. Data that is too wide to fit in a column is truncated. You can adjust the column widths using the ".width" command. Like this:</p>} Code { sqlite> (((.width 12 6))) sqlite> (((select * from tbl1;))) one two ------------ ------ hello 10 goodbye 20 sqlite> } puts { <p>The ".width" command in the example above sets the width of the first column to 12 and the width of the second column to 6. All other column widths were unaltered. You can gives as many arguments to ".width" as necessary to specify the widths of as many columns as are in your query results.</p> <p>If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data. This makes the column width self-adjusting. The default width setting for every column is this auto-adjusting 0 value.</p> <p>The column labels that appear on the first two lines of output can be turned on and off using the ".header" dot command. In the examples above, the column labels are on. To turn them off you could do this:</p>} Code { sqlite> (((.header off))) sqlite> (((select * from tbl1;))) hello 10 goodbye 20 sqlite> } puts { <p>Another useful output mode is "insert". In insert mode, the output is formatted to look like SQL INSERT statements. You can use insert mode to generate text that can later be used to input data into a different database.</p> <p>When specifying insert mode, you have to give an extra argument which is the name of the table to be inserted into. For example:</p> } Code { sqlite> (((.mode insert new_table))) sqlite> (((select * from tbl1;))) INSERT INTO 'new_table' VALUES('hello',10); INSERT INTO 'new_table' VALUES('goodbye',20); sqlite> } puts { <p>The last output mode is "html". In this mode, sqlite writes the results of the query as an XHTML table. The beginning <TABLE> and the ending </TABLE> are not written, but all of the intervening <TR>s, <TH>s, and <TD>s are. The html output mode is envisioned as being useful for CGI.</p> } |
︙ | ︙ | |||
361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 | puts { <p>The ".schema" command accomplishes the same thing as setting list mode, then entering the following query:</p> <blockquote><pre> SELECT sql FROM sqlite_master ORDER BY tbl_name, type DESC, name </pre></blockquote> <p>Or, if you give an argument to ".schema" because you only want the schema for a single table, the query looks like this:</p> <blockquote><pre> SELECT sql FROM sqlite_master | > | | 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 | puts { <p>The ".schema" command accomplishes the same thing as setting list mode, then entering the following query:</p> <blockquote><pre> SELECT sql FROM sqlite_master WHERE type!='meta' ORDER BY tbl_name, type DESC, name </pre></blockquote> <p>Or, if you give an argument to ".schema" because you only want the schema for a single table, the query looks like this:</p> <blockquote><pre> SELECT sql FROM sqlite_master WHERE tbl_name LIKE '%s' AND type!='meta' ORDER BY type DESC, name </pre></blockquote> <p>The <b>%s</b> in the query above is replaced by the argument to ".schema", of course.</p> <h2>Converting An Entire Database To An ASCII Text File</h2> |
︙ | ︙ |