Index: VERSION ================================================================== --- VERSION +++ VERSION @@ -1,1 +1,1 @@ -3.1.2 +3.1.3 Index: mkdll.sh ================================================================== --- mkdll.sh +++ mkdll.sh @@ -19,11 +19,13 @@ echo $CMD $CMD done echo 'EXPORTS' >tclsqlite3.def echo 'Tclsqlite3_Init' >>tclsqlite3.def +echo 'Tclsqlite_Init' >>tclsqlite3.def echo 'Sqlite3_Init' >>tclsqlite3.def +echo 'Sqlite_Init' >>tclsqlite3.def i386-mingw32msvc-dllwrap \ --def tclsqlite3.def -v --export-all \ --driver-name i386-mingw32msvc-gcc \ --dlltool-name i386-mingw32msvc-dlltool \ --as i386-mingw32msvc-as \ Index: sqlite3.1 ================================================================== --- sqlite3.1 +++ sqlite3.1 @@ -1,10 +1,10 @@ .\" Hey, EMACS: -*- nroff -*- .\" First parameter, NAME, should be all caps .\" Second parameter, SECTION, should be 1-8, maybe w/ subsection .\" other parameters are allowed: see man(7), man(1) -.TH SQLITE 1 "Mon Apr 15 23:49:17 2002" +.TH SQLITE3 1 "Mon Apr 15 23:49:17 2002" .\" Please adjust this date whenever revising the manpage. .\" .\" Some roff macros, for reference: .\" .nh disable hyphenation .\" .hy enable hyphenation @@ -14,161 +14,191 @@ .\" .fi enable filling .\" .br insert line break .\" .sp insert n+1 empty lines .\" for manpage-specific macros, see man(7) .SH NAME -sqlite3 \- A command line interface for SQLite verson 3 +.B sqlite3 +\- A command line interface for SQLite version 3 + .SH SYNOPSIS -.B sqlite -.RI [ options ] " filename " [ SQL ] -.SS SUMMARY +.B sqlite3 +.RI [ options ] +.RI [ databasefile ] +.RI [ SQL ] + +.SH SUMMARY .PP -sqlite is a terminal-based front-end to the SQLite library. It enables -you to type in queries interactively, issue them to SQLite and see the -results. Alternatively, you can specify SQL code on the command-line. In -addition it provides a number of meta-commands. +.B sqlite3 +is a terminal-based front-end to the SQLite library that can evaluate +queries interactively and display the results in multiple formats. +.B sqlite3 +can also be used within shell scripts and other applications to provide +batch processing features. .SH DESCRIPTION -This manual page documents briefly the -.B sqlite -command. -This manual page was written for the Debian GNU/Linux distribution -because the original program does not have a manual page. -.SS GETTING STARTED -.PP -To start the sqlite program, just type "sqlite" followed by the name -the file that holds the SQLite database. If the file does not exist, a -new one is created automatically. The sqlite program will then prompt -you to enter SQL. Type in SQL statements (terminated by a semicolon), -press "Enter" and the SQL will be executed. - -For example, to create a new SQLite database named "ex1" with a single -table named "tbl1", you might do this: -.sp -.nf -$ sqlite3 ex1 -SQLite version 3.0.8 +To start a +.B sqlite3 +interactive session, invoke the +.B sqlite3 +command and optionally provide the name of a database file. If the +database file does not exist, it will be created. If the database file +does exist, it will be opened. + +For example, to create a new database file named "mydata.db", create +a table named "memos" and insert a couple of records into that table: +.sp +$ +.B sqlite3 mydata.db +.br +SQLite version 3.1.3 +.br 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 -goodbye|20 +.br +sqlite> +.B create table memos(text, priority INTEGER); +.br +sqlite> +.B insert into memos values('deliver project description', 10); +.br +sqlite> +.B insert into memos values('lunch with Christine', 100); +.br +sqlite> +.B select * from memos; +.br +deliver project description|10 +.br +lunch with Christine|100 +.br sqlite> .sp -.fi + +If no database name is supplied, the ATTACH sql command can be used +to attach to existing or create new database files. ATTACH can also +be used to attach to multiple databases within the same interactive +session. This is useful for migrating data between databases, +possibly changing the schema along the way. + +Optionally, a SQL statement or set of SQL statements can be supplied as +a single argument. Multiple statements should be separated by +semi-colons. + +For example: +.sp +$ +.B sqlite3 -line mydata.db 'select * from memos where priority > 20;' +.br + text = lunch with Christine +.br +priority = 100 +.br +.sp .SS SQLITE META-COMMANDS .PP -Most of the time, sqlite just reads lines of input and passes them on -to the SQLite library for execution. But if an input line begins with -a dot ("."), then that line is intercepted and interpreted by the -sqlite program itself. These "dot commands" are typically used to -change the output format of queries, or to execute certain prepackaged -query statements. - -For a listing of the available dot commands, you can enter ".help" at -any time. For example: +The interactive interpreter offers a set of meta-commands that can be +used to control the output format, examine the currently attached +database files, or perform administrative operations upon the +attached databases (such as rebuilding indices). Meta-commands are +always prefixed with a dot (.). + +A list of available meta-commands can be viewed at any time by issuing +the '.help' command. For example: .sp +sqlite> +.B .help .nf .cc | -sqlite> .help -.dump ?TABLE? ... Dump the database in an text format +.databases List names and files of attached databases +.dump ?TABLE? ... Dump the database in an SQL text format .echo ON|OFF Turn command echo on or off .exit Exit this program .explain ON|OFF Turn output mode suitable for EXPLAIN on or off. - "off" will revert to the output mode that was - previously in effect .header(s) ON|OFF Turn display of headers on or off .help Show this message +.import FILE TABLE Import data from FILE into TABLE .indices TABLE Show names of all indices on TABLE -.mode MODE Set mode to one of "line(s)", "column(s)", - "insert", "list", or "html" -.mode insert TABLE Generate SQL insert statements for TABLE -.nullvalue STRING Print STRING instead of nothing for NULL data +.mode MODE ?TABLE? Set output mode where MODE is one of: + csv Comma-separated values + column Left-aligned columns. (See .width) + html HTML code + insert SQL insert statements for TABLE + line One value per line + list Values delimited by .separator string + tabs Tab-separated values + tcl TCL list elements +.nullvalue STRING Print STRING in place of NULL values .output FILENAME Send output to FILENAME .output stdout Send output to the screen .prompt MAIN CONTINUE Replace the standard prompts - "sqlite > " and " ...> " - with the strings MAIN and CONTINUE - CONTINUE is optional. .quit Exit this program .read FILENAME Execute SQL in FILENAME -.reindex ?TABLE? Rebuild indices .schema ?TABLE? Show the CREATE statements -.separator STRING Change separator string for "list" mode -.show Show the current values for the following: - .echo - .explain - .mode - .nullvalue - .output - .separator - .width -.tables ?PATTERN? List names of tables matching a pattern +.separator STRING Change separator used by output mode and .import +.show Show the current values for various settings +.tables ?PATTERN? List names of tables matching a LIKE pattern .timeout MS Try opening locked tables for MS milliseconds .width NUM NUM ... Set column widths for "column" mode sqlite> |cc . .sp .fi .SH OPTIONS -The program has the following options: +.B sqlite3 +has the following options: .TP .BI \-init\ file -Read in and process 'file', which contains "dot commands". -You can use this file to initialize display settings. -.TP -.B \-html -Set output mode to HTML. -.TP -.B \-list -Set output mode to 'list'. -.TP -.B \-line -Set output mode to 'line'. -.TP -.B \-column -Set output mode to 'column'. -.TP -.BI \-separator\ separator -Specify which output field separator for 'list' mode to use. -Default is '|'. -.TP -.BI \-nullvalue\ string -When a null is encountered, print 'string'. Default is no string. -.TP -.B \-[no]header -Turn headers on or off. Default is off. +Read and execute commands from +.I file +, which can contain a mix of SQL statements and meta-commands. .TP .B \-echo Print commands before execution. - - -.SH OUTPUT MODE -The SQLite program has different output modes, which define the way -the output (from queries) is formatted. - -In 'list' mode, which is the default, one record per line is output, -each field separated by the separator specified with the -\fB-separator\fP option or \fB.separator\fP command. - -In 'line' mode, each column is output on its own line, records are -separated by blank lines. - -In HTML mode, an XHTML table is generated. - -In 'column' mode, one record per line is output, aligned neatly in colums. +.TP +.B \-[no]header +Turn headers on or off. +.TP +.B \-column +Query results will be displayed in a table like form, using +whitespace characters to separate the columns and align the +output. +.TP +.B \-html +Query results will be output as simple HTML tables. +.TP +.B \-line +Query results will be displayed with one value per line, rows +separated by a blank line. Designed to be easily parsed by +scripts or other programs +.TP +.B \-list +Query results will be displayed with the separator (|, by default) +character between each field value. The default. +.TP +.BI \-separator\ separator +Set output field separator. Default is '|'. +.TP +.BI \-nullvalue\ string +Set string used to represent NULL values. Default is '' +(empty string). +.TP +.B \-version +Show SQLite version. +.TP +.B \-help +Show help on options and exit. + .SH INIT FILE -sqlite can be initialized using resource files. These can be combined with -command line arguments to set up sqlite exactly the way you want it. -Initialization proceeds as follows: +.B sqlite3 +reads an initialization file to set the configuration of the +interactive environment. Throughout initialization, any previously +specified setting can be overridden. The sequence of initialization is +as follows: -o The defaults of +o The default configuration is established as follows: .sp .nf .cc | mode = LIST @@ -177,27 +207,23 @@ continue prompt = " ...> " |cc . .sp .fi -are established. - -o If a file .sqliterc can be found in the user's home directory, it is -read and processed. It should only contain "dot commands". If the -file is not found or cannot be read, processing continues without -notification. - -o If a file is specified on the command line with the -init option, it -is processed in the same manner as .sqliterc - -o All other command line options are processed - -o The database is opened and you are now ready to begin. +o If the file +.B ~/.sqliterc +exists, it is processed first. +can be found in the user's home directory, it is +read and processed. It should generally only contain meta-commands. + +o If the -init option is present, the specified file is processed. + +o All other command line options are processed. .SH SEE ALSO -http://www.hwaci.com/sw/sqlite/ +http://www.sqlite.org/ .br The sqlite-doc package .SH AUTHOR This manual page was originally written by Andreas Rottmann , for the Debian GNU/Linux system (but may be used -by others). +by others). It was subsequently revised by Bill Bumgarner . Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -20,11 +20,11 @@ ** creating ID lists ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** -** $Id: build.c,v 1.311 2005/02/15 20:47:57 drh Exp $ +** $Id: build.c,v 1.312 2005/02/19 08:18:06 danielk1977 Exp $ */ #include "sqliteInt.h" #include /* @@ -744,20 +744,24 @@ ** indices to be created and the table record must come before the ** indices. Hence, the record number for the table must be allocated ** now. */ if( !db->init.busy && (v = sqlite3GetVdbe(pParse))!=0 ){ + int lbl; sqlite3BeginWriteOperation(pParse, 0, iDb); - /* Every time a new table is created the file-format - ** and encoding meta-values are set in the database, in - ** case this is the first table created. + /* If the file format and encoding in the database have not been set, + ** set them now. */ + sqlite3VdbeAddOp(v, OP_ReadCookie, iDb, 1); /* file_format */ + lbl = sqlite3VdbeMakeLabel(v); + sqlite3VdbeAddOp(v, OP_If, 0, lbl); sqlite3VdbeAddOp(v, OP_Integer, db->file_format, 0); sqlite3VdbeAddOp(v, OP_SetCookie, iDb, 1); sqlite3VdbeAddOp(v, OP_Integer, db->enc, 0); sqlite3VdbeAddOp(v, OP_SetCookie, iDb, 4); + sqlite3VdbeResolveLabel(v, lbl); /* This just creates a place-holder record in the sqlite_master table. ** The record created does not contain anything yet. It will be replaced ** by the real entry in code generated at sqlite3EndTable(). ** Index: src/main.c ================================================================== --- src/main.c +++ src/main.c @@ -12,11 +12,11 @@ ** Main file for the SQLite library. The routines in this file ** implement the programmer interface to the library. Routines in ** other files are for internal use by SQLite and should not be ** accessed by users of the library. ** -** $Id: main.c,v 1.280 2005/02/06 02:45:42 drh Exp $ +** $Id: main.c,v 1.281 2005/02/19 08:18:06 danielk1977 Exp $ */ #include "sqliteInt.h" #include "os.h" #include @@ -255,16 +255,27 @@ db->file_format = meta[1]; if( db->file_format==0 ){ /* This happens if the database was initially empty */ db->file_format = 1; } + + if( db->file_format==2 ){ + /* File format 2 is treated exactly as file format 1. New + ** databases are created with file format 1. + */ + db->file_format = 1; + } } /* - ** file_format==1 Version 3.0.0. + ** file_format==1 Version 3.0.0. + ** file_format==2 Version 3.1.3. + ** + ** Version 3.0 can only use files with file_format==1. Version 3.1.3 + ** can read and write files with file_format==1 or file_format==2. */ - if( meta[1]>1 ){ + if( meta[1]>2 ){ sqlite3BtreeCloseCursor(curMain); sqlite3SetString(pzErrMsg, "unsupported file format", (char*)0); return SQLITE_ERROR; } Index: src/shell.c ================================================================== --- src/shell.c +++ src/shell.c @@ -10,11 +10,11 @@ ** ************************************************************************* ** This file contains code to implement the "sqlite" command line ** utility for accessing SQLite databases. ** -** $Id: shell.c,v 1.121 2005/02/03 00:42:35 drh Exp $ +** $Id: shell.c,v 1.122 2005/02/23 12:35:41 drh Exp $ */ #include #include #include #include @@ -654,11 +654,11 @@ if( nArg!=3 ) return 1; zTable = azArg[0]; zType = azArg[1]; zSql = azArg[2]; - if( strcasecmp(zTable,"sqlite_sequence")!=0 ){ + if( strcmp(zTable,"sqlite_sequence")!=0 ){ fprintf(p->out, "%s;\n", zSql); }else{ fprintf(p->out, "DELETE FROM sqlite_sequence;\n"); } Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -41,11 +41,11 @@ ** documentation, headers files, or other derived files. The formatting ** of the code in this file is, therefore, important. See other comments ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** -** $Id: vdbe.c,v 1.455 2005/02/17 00:03:07 drh Exp $ +** $Id: vdbe.c,v 1.456 2005/02/19 08:18:06 danielk1977 Exp $ */ #include "sqliteInt.h" #include "os.h" #include #include "vdbeInt.h" @@ -1680,11 +1680,12 @@ /* Opcode: Column P1 P2 * ** ** Interpret the data that cursor P1 points to as a structure built using ** the MakeRecord instruction. (See the MakeRecord opcode for additional ** information about the format of the data.) Push onto the stack the value -** of the P2-th column contained in the data. +** of the P2-th column contained in the data. If there are less that (P2+1) +** values in the record, push a NULL onto the stack. ** ** If the KeyAsData opcode has previously executed on this cursor, then the ** field might be extracted from the key rather than the data. ** ** If P1 is negative, then the record is stored on the stack rather than in @@ -1848,19 +1849,30 @@ ** arrays. aType[i] will contain the type integer for the i-th ** column and aOffset[i] will contain the offset from the beginning ** of the record to the start of the data for the i-th column */ offset = szHdr; + assert( offset>0 ); i = 0; while( idxaOffset = aOffset; pC->cacheValid = 1; } } - /* Get the column information. + /* Get the column information. If aOffset[p2] is non-zero, then + ** deserialize the value from the record. If aOffset[p2] is zero, + ** then there are not enough fields in the record to satisfy the + ** request. The value is NULL in this case. */ - assert( rc==SQLITE_OK ); - if( zRec ){ - zData = &zRec[aOffset[p2]]; - }else{ - len = sqlite3VdbeSerialTypeLen(aType[p2]); - rc = sqlite3VdbeMemFromBtree(pCrsr, aOffset[p2], len, pC->keyAsData, &sMem); - if( rc!=SQLITE_OK ){ - goto op_column_out; - } - zData = sMem.z; - } - sqlite3VdbeSerialGet(zData, aType[p2], pTos); - pTos->enc = db->enc; + if( aOffset[p2] ){ + assert( rc==SQLITE_OK ); + if( zRec ){ + zData = &zRec[aOffset[p2]]; + }else{ + len = sqlite3VdbeSerialTypeLen(aType[p2]); + rc = sqlite3VdbeMemFromBtree(pCrsr, aOffset[p2], len,pC->keyAsData,&sMem); + if( rc!=SQLITE_OK ){ + goto op_column_out; + } + zData = sMem.z; + } + sqlite3VdbeSerialGet(zData, aType[p2], pTos); + pTos->enc = db->enc; + }else{ + pTos->flags = MEM_Null; + } /* If we dynamically allocated space to hold the data (in the ** sqlite3VdbeMemFromBtree() call above) then transfer control of that ** dynamically allocated space over to the pTos structure rather. ** This prevents a memory copy. Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -14,11 +14,11 @@ ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** -** $Id: where.c,v 1.134 2005/02/02 01:10:45 danielk1977 Exp $ +** $Id: where.c,v 1.135 2005/02/22 09:47:18 danielk1977 Exp $ */ #include "sqliteInt.h" /* ** The query generator uses an array of instances of this structure to @@ -1121,11 +1121,11 @@ pLevel->p1 = iCur; pLevel->p2 = start; if( testOp!=OP_Noop ){ sqlite3VdbeAddOp(v, OP_Recno, iCur, 0); sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0); - sqlite3VdbeAddOp(v, testOp, 0, brk); + sqlite3VdbeAddOp(v, testOp, (int)(('n'<<8)&0x0000FF00), brk); } }else if( pIdx==0 ){ /* Case 4: There is no usable index. We must do a complete ** scan of the entire database table. */ ADDED test/alter2.test Index: test/alter2.test ================================================================== --- /dev/null +++ test/alter2.test @@ -0,0 +1,292 @@ +# 2005 February 18 +# +# 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. +# +#************************************************************************* +# This file implements regression tests for SQLite library. The +# focus of this script is testing that SQLite can handle a subtle +# file format change that may be used in the future to implement +# "ALTER TABLE ... ADD COLUMN". +# +# $Id: alter2.test,v 1.1 2005/02/19 08:18:06 danielk1977 Exp $ +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +# The file format change affects the way row-records stored in tables (but +# not indices) are interpreted. Before version 3.1.3, a row-record for a +# table with N columns was guaranteed to contain exactly N fields. As +# of version 3.1.3, the record may contain up to N fields. In this case +# the M fields that are present are the values for the left-most M +# columns. The (N-M) rightmost columns contain NULL. +# +# If any records in the database contain less fields than their table +# has columns, then the file-format meta value should be set to (at least) 2. +# + +# This procedure sets the value of the file-format in file 'test.db' +# to $newval. Also, the schema cookie is incremented. +# +proc set_file_format {newval} { + set bt [btree_open test.db 10 0] + btree_begin_transaction $bt + set meta [btree_get_meta $bt] + lset meta 2 $newval ;# File format + lset meta 1 [expr [lindex $meta 1]+1] ;# Schema cookie + eval "btree_update_meta $bt $meta" + btree_commit $bt + btree_close $bt +} + +# This procedure returns the value of the file-format in file 'test.db'. +# +proc get_file_format {{fname test.db}} { + set bt [btree_open $fname 10 0] + set meta [btree_get_meta $bt] + btree_close $bt + lindex $meta 2 +} + +# This procedure sets the SQL statement stored for table $tbl in the +# sqlite_master table of file 'test.db' to $sql. +# +proc alter_table {tbl sql} { + sqlite3 dbat test.db + dbat eval { + PRAGMA writable_schema = 1; + UPDATE sqlite_master SET sql = $sql WHERE name = $tbl AND type = 'table'; + PRAGMA writable_schema = 0; + } + dbat close + set_file_format 2 +} + +#----------------------------------------------------------------------- +# Some basic tests to make sure short rows are handled. +# +do_test alter2-1.1 { + execsql { + CREATE TABLE abc(a, b); + INSERT INTO abc VALUES(1, 2); + INSERT INTO abc VALUES(3, 4); + INSERT INTO abc VALUES(5, 6); + } +} {} +do_test alter2-1.2 { + # ALTER TABLE abc ADD COLUMN c; + alter_table abc {CREATE TABLE abc(a, b, c);} +} {} +do_test alter2-1.3 { + execsql { + SELECT * FROM abc; + } +} {1 2 {} 3 4 {} 5 6 {}} +do_test alter2-1.4 { + execsql { + UPDATE abc SET c = 10 WHERE a = 1; + SELECT * FROM abc; + } +} {1 2 10 3 4 {} 5 6 {}} +do_test alter2-1.5 { + execsql { + CREATE INDEX abc_i ON abc(c); + } +} {} +do_test alter2-1.6 { + execsql { + SELECT c FROM abc ORDER BY c; + } +} {{} {} 10} +do_test alter2-1.7 { + execsql { + SELECT * FROM abc WHERE c = 10; + } +} {1 2 10} +do_test alter2-1.8 { + execsql { + SELECT sum(a), c FROM abc GROUP BY c; + } +} {8.0 {} 1.0 10} +do_test alter2-1.9 { + # ALTER TABLE abc ADD COLUMN d; + alter_table abc {CREATE TABLE abc(a, b, c, d);} + execsql { SELECT * FROM abc; } + execsql { + UPDATE abc SET d = 11 WHERE c IS NULL AND a<4; + SELECT * FROM abc; + } +} {1 2 10 {} 3 4 {} 11 5 6 {} {}} +do_test alter2-1.10 { + execsql { + SELECT typeof(d) FROM abc; + } +} {null integer null} +do_test alter2-1.99 { + execsql { + DROP TABLE abc; + } +} {} + +#----------------------------------------------------------------------- +# Test that views work when the underlying table structure is changed. +# +ifcapable view { + do_test alter2-2.1 { + execsql { + CREATE TABLE abc2(a, b, c); + INSERT INTO abc2 VALUES(1, 2, 10); + INSERT INTO abc2 VALUES(3, 4, NULL); + INSERT INTO abc2 VALUES(5, 6, NULL); + CREATE VIEW abc2_v AS SELECT * FROM abc2; + SELECT * FROM abc2_v; + } + } {1 2 10 3 4 {} 5 6 {}} + do_test alter2-2.2 { + # ALTER TABLE abc ADD COLUMN d; + alter_table abc2 {CREATE TABLE abc2(a, b, c, d);} + execsql { + SELECT * FROM abc2_v; + } + } {1 2 10 {} 3 4 {} {} 5 6 {} {}} + do_test alter2-2.3 { + execsql { + DROP TABLE abc2; + DROP VIEW abc2_v; + } + } {} +} + +#----------------------------------------------------------------------- +# Test that triggers work when a short row is copied to the old.* +# trigger pseudo-table. +# +ifcapable trigger { + do_test alter2-3.1 { + execsql { + CREATE TABLE abc3(a, b); + CREATE TABLE blog(o, n); + CREATE TRIGGER abc3_t AFTER UPDATE OF b ON abc3 BEGIN + INSERT INTO blog VALUES(old.b, new.b); + END; + } + } {} + do_test alter2-3.2 { + execsql { + INSERT INTO abc3 VALUES(1, 4); + UPDATE abc3 SET b = 2 WHERE b = 4; + SELECT * FROM blog; + } + } {4 2} + do_test alter2-3.3 { + execsql { + INSERT INTO abc3 VALUES(3, 4); + INSERT INTO abc3 VALUES(5, 6); + } + alter_table abc3 {CREATE TABLE abc3(a, b, c);} + execsql { + SELECT * FROM abc3; + } + } {1 2 {} 3 4 {} 5 6 {}} + do_test alter2-3.4 { + execsql { + UPDATE abc3 SET b = b*2 WHERE a<4; + SELECT * FROM abc3; + } + } {1 4 {} 3 8 {} 5 6 {}} + do_test alter2-3.5 { + execsql { + SELECT * FROM blog; + } + } {4 2 2 4 4 8} + + do_test alter2-3.6 { + execsql { + CREATE TABLE clog(o, n); + CREATE TRIGGER abc3_t2 AFTER UPDATE OF c ON abc3 BEGIN + INSERT INTO clog VALUES(old.c, new.c); + END; + UPDATE abc3 SET c = a*2; + SELECT * FROM clog; + } + } {{} 2 {} 6 {} 10} +} + +#--------------------------------------------------------------------- +# Check that an error occurs if the database is upgraded to a file +# format that SQLite does not support (in this case 3). Note: The +# file format is checked each time the schema is read, so changing the +# file format requires incrementing the schema cookie. +# +do_test alter2-4.1 { + set_file_format 3 +} {} +do_test alter2-4.2 { + catchsql { + SELECT * FROM sqlite_master; + } +} {1 {unsupported file format}} +do_test alter2-4.3 { + sqlite3_errcode $::DB +} {SQLITE_ERROR} +do_test alter2-4.4 { + db close + set ::DB [sqlite3 db test.db] + catchsql { + SELECT * FROM sqlite_master; + } +} {1 {unsupported file format}} +do_test alter2-4.5 { + sqlite3_errcode $::DB +} {SQLITE_ERROR} + +#--------------------------------------------------------------------- +# Check that executing VACUUM on a file with file-format version 2 +# resets the file format to 1. +# +do_test alter2-5.1 { + set_file_format 2 + get_file_format +} {2} +do_test alter2-5.2 { + execsql { + VACUUM; + } +} {} +do_test alter2-5.3 { + get_file_format +} {1} + +#--------------------------------------------------------------------- +# Test that when a database with file-format 2 is opened, new +# databases are still created with file-format 1. +# +do_test alter2-6.1 { + db close + set_file_format 2 + set ::DB [sqlite3 db test.db] + get_file_format +} {2} +do_test alter2-6.2 { + file delete -force test2.db-journal + file delete -force test2.db + execsql { + ATTACH 'test2.db' AS aux; + CREATE TABLE aux.t1(a, b); + } + get_file_format test2.db +} {1} +do_test alter2-6.3 { + execsql { + CREATE TABLE t1(a, b); + } + get_file_format +} {2} + +finish_test + Index: test/capi3.test ================================================================== --- test/capi3.test +++ test/capi3.test @@ -9,11 +9,11 @@ # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this script testing the callback-free C/C++ API. # -# $Id: capi3.test,v 1.29 2005/01/24 10:26:00 danielk1977 Exp $ +# $Id: capi3.test,v 1.30 2005/02/19 08:18:06 danielk1977 Exp $ # set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -485,11 +485,11 @@ # Do this by updating the file format via the btree layer. do_test capi3-7.1 { set ::bt [btree_open test.db 10 0] btree_begin_transaction $::bt set meta [btree_get_meta $::bt] - lset meta 2 2 + lset meta 2 3 eval [concat btree_update_meta $::bt [lrange $meta 0 end]] btree_commit $::bt btree_close $::bt } {} do_test capi3-7.2 { Index: test/corrupt.test ================================================================== --- test/corrupt.test +++ test/corrupt.test @@ -11,11 +11,11 @@ # This file implements regression tests for SQLite library. # # This file implements tests to make sure SQLite does not crash or # segfault if it sees a corrupt database file. # -# $Id: corrupt.test,v 1.7 2005/02/05 12:48:49 danielk1977 Exp $ +# $Id: corrupt.test,v 1.8 2005/02/19 08:18:06 danielk1977 Exp $ catch {file delete -force test.db} catch {file delete -force test.db-journal} set testdir [file dirname $argv0] @@ -25,22 +25,22 @@ # do_test corrupt-1.1 { execsql { BEGIN; CREATE TABLE t1(x); - INSERT INTO t1 VALUES(randstr(10,100)); - INSERT INTO t1 VALUES(randstr(10,100)); - INSERT INTO t1 VALUES(randstr(10,100)); - INSERT INTO t1 SELECT x || randstr(5,10) FROM t1; - INSERT INTO t1 SELECT x || randstr(5,10) FROM t1; - INSERT INTO t1 SELECT x || randstr(5,10) FROM t1; - INSERT INTO t1 SELECT x || randstr(5,10) FROM t1; - INSERT INTO t1 VALUES(randstr(2100,3000)); - INSERT INTO t1 SELECT x || randstr(5,10) FROM t1; - INSERT INTO t1 SELECT x || randstr(5,10) FROM t1; - INSERT INTO t1 SELECT x || randstr(5,10) FROM t1; - INSERT INTO t1 SELECT x || randstr(5,10) FROM t1; + INSERT INTO t1 VALUES(randstr(100,100)); + INSERT INTO t1 VALUES(randstr(90,90)); + INSERT INTO t1 VALUES(randstr(80,80)); + INSERT INTO t1 SELECT x || randstr(5,5) FROM t1; + INSERT INTO t1 SELECT x || randstr(6,6) FROM t1; + INSERT INTO t1 SELECT x || randstr(7,7) FROM t1; + INSERT INTO t1 SELECT x || randstr(8,8) FROM t1; + INSERT INTO t1 VALUES(randstr(3000,3000)); + INSERT INTO t1 SELECT x || randstr(9,9) FROM t1; + INSERT INTO t1 SELECT x || randstr(10,10) FROM t1; + INSERT INTO t1 SELECT x || randstr(11,11) FROM t1; + INSERT INTO t1 SELECT x || randstr(12,12) FROM t1; CREATE INDEX t1i1 ON t1(x); CREATE TABLE t2 AS SELECT * FROM t1; DELETE FROM t2 WHERE rowid%5!=0; COMMIT; } Index: test/intpkey.test ================================================================== --- test/intpkey.test +++ test/intpkey.test @@ -11,11 +11,11 @@ # This file implements regression tests for SQLite library. # # This file implements tests for the special processing associated # with INTEGER PRIMARY KEY columns. # -# $Id: intpkey.test,v 1.20 2004/11/03 13:59:06 drh Exp $ +# $Id: intpkey.test,v 1.21 2005/02/22 09:47:19 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table with a primary key and a datatype other than @@ -517,7 +517,45 @@ catchsql { INSERT INTO t1 VALUES('+1234567890',3,4); } } {0 {}} +# Compare an INTEGER PRIMARY KEY against a TEXT expression. The INTEGER +# affinity should be applied to the text value before the comparison +# takes place. +# +do_test intpkey-14.1 { + execsql { + CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT); + INSERT INTO t3 VALUES(1, 1, 'one'); + INSERT INTO t3 VALUES(2, 2, '2'); + INSERT INTO t3 VALUES(3, 3, 3); + } +} {} +do_test intpkey-14.2 { + execsql { + SELECT * FROM t3 WHERE a>2; + } +} {3 3 3} +do_test intpkey-14.3 { + execsql { + SELECT * FROM t3 WHERE a>'2'; + } +} {3 3 3} +do_test intpkey-14.4 { + execsql { + SELECT * FROM t3 WHERE a<'2'; + } +} {1 1 one} +do_test intpkey-14.5 { + execsql { + SELECT * FROM t3 WHERE a$date" puts "

    $desc

" } + +chng {2005 February 19 (3.1.3)} { +
  • Fix a problem with VACUUM on databases from which tables containing +AUTOINCREMENT have been dropped.
    • +
  • Add forward compatibility to the future version 3.2 database file +format.
    • +
  • Documentation updates
    • +} chng {2005 February 15 (3.1.2)} {
  • Fix a bug that can lead to database corruption if there are two open connections to the same database and one connection does a VACUUM and the second makes some change to the database.
    • Index: www/index.tcl ================================================================== --- www/index.tcl +++ www/index.tcl @@ -58,10 +58,14 @@ puts "

    $date - $title

    " regsub -all "\n( *\n)+" $text "

    \n\n

    " txt puts "

    $txt

    " puts "
    " } + +newsitem {2005-Feb-19} {Version 3.1.3 Released} { + Version 3.1.3 cleans up some minor issues discovered in version 3.1.2. +} newsitem {2005-Feb-15} {Versions 2.8.16 and 3.1.2 Released} { A critical bug in the VACUUM command that can lead to database corruption has been fixed in both the 2.x branch and the main 3.x line. This bug has existed in all prior versions of SQLite. @@ -101,6 +105,6 @@ puts {

    Old news...

    } -footer {$Id: index.tcl,v 1.107 2005/02/15 13:38:46 drh Exp $} +footer {$Id: index.tcl,v 1.108 2005/02/19 13:46:25 drh Exp $} Index: www/lang.tcl ================================================================== --- www/lang.tcl +++ www/lang.tcl @@ -1,9 +1,9 @@ # # Run this Tcl script to generate the lang-*.html files. # -set rcsid {$Id: lang.tcl,v 1.83 2005/02/14 06:38:41 danielk1977 Exp $} +set rcsid {$Id: lang.tcl,v 1.84 2005/02/19 12:44:16 drh Exp $} source common.tcl if {[llength $argv]>0} { set outputdir [lindex $argv 0] } else { @@ -901,11 +901,11 @@ NOTNULL | [NOT] BETWEEN AND | [NOT] IN ( ) | [NOT] IN ( ) | [NOT] IN [ .] | -( ) | +[EXISTS] ( ) | CASE [] LP WHEN THEN RPPLUS [ELSE ] END } {like-op} { LIKE | NOT LIKE } {glob-op} { GLOB | NOT GLOB @@ -1019,17 +1019,24 @@ column can be used, except that you cannot change the value of a row key in an UPDATE or INSERT statement. "SELECT * ..." does not return the row key.

    SELECT statements can appear in expressions as either the -right-hand operand of the IN operator or as a scalar quantity. -In both cases, the SELECT should have only a single column in its +right-hand operand of the IN operator, as a scalar quantity, or +as the operand of an EXISTS operator. +As a scalar quantity or the operand of an IN operator, +the SELECT should have only a single column in its result. Compound SELECTs (connected with keywords like UNION or EXCEPT) are allowed. -A SELECT in an expression is evaluated once before any other processing -is performed, so none of the expressions within the select itself can -refer to quantities in the containing expression.

    +With the EXISTS operator, the columns in the result set of the SELECT are +ignored and the expression returns TRUE if one or more rows exist +and FALSE if the result set is empty. +If no terms in the SELECT expression refer to value in the containing +query, then the expression is evaluated once prior to any other +processing and the result is reused as necessary. If the SELECT expression +does contain variables from the outer query, then the SELECT is reevaluated +every time it is needed.

    When a SELECT is the right operand of the IN operator, the IN operator returns TRUE if the result of the left operand is any of the values generated by the select. The IN operator may be preceded by the NOT keyword to invert the sense of the test.

    Index: www/pragma.tcl ================================================================== --- www/pragma.tcl +++ www/pragma.tcl @@ -1,9 +1,9 @@ # # Run this Tcl script to generate the pragma.html file. # -set rcsid {$Id: pragma.tcl,v 1.8 2005/01/10 06:39:57 danielk1977 Exp $} +set rcsid {$Id: pragma.tcl,v 1.11 2005/02/19 13:05:48 drh Exp $} source common.tcl header {Pragma statements supported by SQLite} proc Section {name {label {}}} { puts "\n
    " @@ -15,11 +15,11 @@ puts {

    The PRAGMA command is a special command used to modify the operation of the SQLite library or to query the library for internal (non-table) data. The PRAGMA command is issued using the same -interface as other SQLite commands (e.g. SELECT, INSERT) but is different +interface as other SQLite commands (e.g. SELECT, INSERT) but is different in the following important respects:

    • Specific pragma statements may be removed and others added in future releases of SQLite. Use with caution! @@ -173,15 +173,16 @@
      PRAGMA full_column_names = 0 | 1;

      Query or change the full-column-names flag. This flag affects the way SQLite names columns of data returned by SELECT statements when the expression for the column is a table-column name or the wildcard "*". Normally, such result columns are named - . if the SELECT statement joins two or - more tables together, or simply if the SELECT + <table-name/alias><column-name> if the SELECT statement joins + two or + more tables together, or simply <column-name> if the SELECT statement queries a single table. When the full-column-names flag - is set, such columns are always named ., - regardless of whether or not a join is performed. + is set, such columns are always named <table-name/alias> + <column-name> regardless of whether or not a join is performed.

      If both the short-column-names and full-column-names are set, then the behaviour associated with the full-column-names flag is exhibited.

      @@ -197,21 +198,21 @@ the value of macro SQLITE_MAX_PAGE_SIZE during compilation.

      • -

    • PRAGMA full_column_names; -
      PRAGMA full_column_names =       0 | 1;

      +

    • PRAGMA short_column_names; +
      PRAGMA short_column_names =       0 | 1;

      Query or change the short-column-names flag. This flag affects the way SQLite names columns of data returned by SELECT statements when the expression for the column is a table-column name or the wildcard "*". Normally, such result columns are named - . if the SELECT statement joins two or - more tables together, or simply if the SELECT - statement queries a single table. When the short-column-names flag - is set, such columns are always named , regardless of - whether or not a join is performed. + <table-name/alias>lt;column-name> if the SELECT statement + joins two or more tables together, or simply <column-name> if + the SELECT statement queries a single table. When the short-column-names + flag is set, such columns are always named <column-name> + regardless of whether or not a join is performed.

      If both the short-column-names and full-column-names are set, then the behaviour associated with the full-column-names flag is exhibited.

      @@ -247,76 +248,81 @@

    • PRAGMA temp_store; -
      PRAGMA temp_store = DEFAULT;       (0) -
      PRAGMA temp_store = MEMORY;       (2) -
      PRAGMA temp_store = FILE;       (1)

      -

      Query or change the setting of the "temp_store" flag affecting - the database for the duration of the current database connection. - The temp_store flag reverts to its default value when the database - is closed and reopened. For additional information on the temp_store - flag, see the description of the - default_temp_store pragma. Note that it is possible for - the library compile-time options to override this setting. - - -

    • PRAGMA temp_store; -
      PRAGMA temp_store = DEFAULT;       (0) -
      PRAGMA temp_store = MEMORY;       (2) -
      PRAGMA temp_store = FILE;       (1)

      +
      PRAGMA temp_store = DEFAULT; (0) +
      PRAGMA temp_store = FILE;       (1) +
      PRAGMA temp_store = MEMORY;       (2)

      Query or change the setting of the "temp_store" parameter. - When temp_store is DEFAULT (0), the compile-time value of the - symbol TEMP_STORE is used for the temporary database. When - temp_store is MEMORY (2), an in-memory database is used. - When temp_store is FILE (1), a temporary database file on disk - will be used. See PRAGMA - temp_store_directory for further temporary storage options when + When temp_store is DEFAULT (0), the compile-time C preprocessor macro + TEMP_STORE is used to determine where temporary tables and indices + are stored. When + temp_store is MEMORY (2) temporary tables and indices are kept in memory. + When temp_store is FILE (1) temporary tables and indices are stored + in a file. The + temp_store_directory pragma can be used to specify the directory + containing this file. FILE is specified. When the temp_store setting is changed, - all existing temporary tables, indices, triggers, and viewers are + all existing temporary tables, indices, triggers, and views are immediately deleted.

      -

      It is possible for the library compile-time symbol - TEMP_STORE to override this setting. The following table summarizes - this:

      - - - - - - - - - - - -
      TEMP_STOREtemp_storetemp database location
      0anyfile
      10file
      11file
      12memory
      20memory
      21file
      22memory
      3anymemory
      -
      • -
      +

      It is possible for the library compile-time C preprocessor symbol + TEMP_STORE to override this pragma setting. The following table summarizes + the interaction of the TEMP_STORE preprocessor macro and the + temp_store pragma:

      + +
      + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
      TEMP_STOREPRAGMA
      temp_store      		Storage used for
      TEMP tables and indices
      0anyfile
      10file
      11file
      12memory
      20memory
      21file
      22memory
      3anymemory
      +
      + +

    • PRAGMA temp_store_directory;
      PRAGMA temp_store_directory = 'directory-name';

      -

      Query or change the setting of the "temp_store_directory" flag affecting - the database for the duration of the current database connection. - The temp_store_directory flag reverts to its default value when the database - is closed and reopened. Setting temp_store_directory allows control of the - placement of temporary files created by SQLite when PRAGMA - temp_store is FILE (1), - or when the compile time default temporary store is FILE. - Otherwise, when the temp_store (or default) setting is - MEMORY (2), setting temp_store_directory has no effect.

      +

      Query or change the setting of the "temp_store_directory" - the + directory where files used for storing temporary tables and indices + are kept. This setting lasts for the duration of the current connection + only and resets to its default value for each new connection opened.

      When the temp_store_directory setting is changed, all existing temporary tables, indices, triggers, and viewers are immediately deleted. In practice, temp_store_directory should be set immediately after the database is opened.

      The value directory-name should be enclosed in single quotes. To revert the directory to the default, set the directory-name to - a null string, e.g., PRAGMA temp_store_directory = ''. An + an empty string, e.g., PRAGMA temp_store_directory = ''. An error is raised if directory-name is not found or is not writable.

      The default directory for temporary files depends on the OS. For Unix/Linux/OSX, the default is the is the first writable directory found @@ -435,9 +441,20 @@
      PRAGMA vdbe_trace = OFF; (0)

      Turn tracing of the virtual database engine inside of the SQLite library on and off. This is used for debugging. See the VDBE documentation for more information.

      • + + +

    • PRAGMA vdbe_listing = ON; (1) +
      PRAGMA vdbe_listing = OFF;       (0)

      +

      Turn listings of virtual machine programs on and off. + With listing is on, the entire content of a program is printed + just prior to beginning execution. This is like automatically + executing an EXPLAIN prior to each statement. The statement + executes normally after the listing is printed. + This is used for debugging. See the + VDBE documentation for more + information.

    } -