Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Make the distinction between text and numeric data. (CVS 710) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
310ac4fbaf0ed63f98bfacb55259960b |
User & Date: | drh 2002-08-13 23:02:57.000 |
Context
2002-08-14
| ||
00:08 | Update documentation to better explain the typelessness of SQLite and to describe the distinction between text and numeric data. (CVS 711) (check-in: 4ff0f578ec user: drh tags: trunk) | |
2002-08-13
| ||
23:02 | Make the distinction between text and numeric data. (CVS 710) (check-in: 310ac4fbaf user: drh tags: trunk) | |
20:45 | Documentation and makefile updates. (CVS 709) (check-in: 92c403f485 user: drh tags: trunk) | |
Changes
Changes to VERSION.
|
| | | 1 | 2.7.0 |
Changes to src/main.c.
︙ | ︙ | |||
10 11 12 13 14 15 16 | ** ************************************************************************* ** 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. ** | | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ** ************************************************************************* ** 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.97 2002/08/13 23:02:57 drh Exp $ */ #include "sqliteInt.h" #include "os.h" #include <ctype.h> /* ** A pointer to this structure is used to communicate information |
︙ | ︙ | |||
276 277 278 279 280 281 282 | ** file_format==2 Version 2.2.0. Add support for INTEGER PRIMARY KEY. ** file_format==3 Version 2.6.0. Fix empty-string index bug. ** file_format==4 Version 2.7.0. Add support for separate numeric and ** text datatypes. */ if( db->file_format==0 ){ /* This happens if the database was initially empty */ | | | | 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 | ** file_format==2 Version 2.2.0. Add support for INTEGER PRIMARY KEY. ** file_format==3 Version 2.6.0. Fix empty-string index bug. ** file_format==4 Version 2.7.0. Add support for separate numeric and ** text datatypes. */ if( db->file_format==0 ){ /* This happens if the database was initially empty */ db->file_format = 4; }else if( db->file_format>4 ){ sqliteBtreeCloseCursor(curMain); sqliteSetString(pzErrMsg, "unsupported file format", 0); return SQLITE_ERROR; } /* Read the schema information out of the schema tables */ |
︙ | ︙ | |||
401 402 403 404 405 406 407 | rc = sqlite_exec(db, "BEGIN; SELECT name FROM sqlite_master WHERE type='table';", upgrade_3_callback, &initData, &zErr); if( rc==SQLITE_OK ){ sqliteBtreeGetMeta(db->pBe, meta); | | | 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 | rc = sqlite_exec(db, "BEGIN; SELECT name FROM sqlite_master WHERE type='table';", upgrade_3_callback, &initData, &zErr); if( rc==SQLITE_OK ){ sqliteBtreeGetMeta(db->pBe, meta); meta[2] = 4; sqliteBtreeUpdateMeta(db->pBe, meta); sqlite_exec(db, "COMMIT", 0, 0, 0); } if( rc!=SQLITE_OK ){ sqliteSetString(pzErrMsg, "unable to upgrade database to the version 2.6 format", zErr ? ": " : 0, zErr, 0); |
︙ | ︙ |
Changes to src/util.c.
︙ | ︙ | |||
10 11 12 13 14 15 16 | ** ************************************************************************* ** Utility functions used throughout sqlite. ** ** This file contains functions for allocating memory, comparing ** strings, and stuff like that. ** | | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ** ************************************************************************* ** Utility functions used throughout sqlite. ** ** This file contains functions for allocating memory, comparing ** strings, and stuff like that. ** ** $Id: util.c,v 1.48 2002/08/13 23:02:57 drh Exp $ */ #include "sqliteInt.h" #include <stdarg.h> #include <ctype.h> /* ** If malloc() ever fails, this global variable gets set to 1. |
︙ | ︙ | |||
439 440 441 442 443 444 445 446 447 448 449 450 451 452 | register unsigned char *a, *b; a = (unsigned char *)zLeft; b = (unsigned char *)zRight; while( N-- > 0 && *a!=0 && UpperToLower[*a]==UpperToLower[*b]){ a++; b++; } return N<0 ? 0 : *a - *b; } /* ** The sortStrCmp() function below is used to order elements according ** to the ORDER BY clause of a SELECT. The sort order is a little different ** from what one might expect. This note attempts to describe what is ** going on. ** ** We want the main string comparision function used for sorting to | > | 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 | register unsigned char *a, *b; a = (unsigned char *)zLeft; b = (unsigned char *)zRight; while( N-- > 0 && *a!=0 && UpperToLower[*a]==UpperToLower[*b]){ a++; b++; } return N<0 ? 0 : *a - *b; } #if 0 /* NOT USED */ /* ** The sortStrCmp() function below is used to order elements according ** to the ORDER BY clause of a SELECT. The sort order is a little different ** from what one might expect. This note attempts to describe what is ** going on. ** ** We want the main string comparision function used for sorting to |
︙ | ︙ | |||
618 619 620 621 622 623 624 625 626 627 628 629 630 631 | } case 5: { result = cb - ca; }; } return result; } /* ** Return TRUE if z is a pure numeric string. Return FALSE if the ** string contains any character which is not part of a number. ** ** Am empty string is considered numeric. */ | > | 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 | } case 5: { result = cb - ca; }; } return result; } #endif /* NOT USED */ /* ** Return TRUE if z is a pure numeric string. Return FALSE if the ** string contains any character which is not part of a number. ** ** Am empty string is considered numeric. */ |
︙ | ︙ | |||
647 648 649 650 651 652 653 | while( isdigit(*z) ){ z++; } } } return *z==0; } /* This comparison routine is what we use for comparison operations | | > > > > | | > > | < < < < | 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 | while( isdigit(*z) ){ z++; } } } return *z==0; } /* This comparison routine is what we use for comparison operations ** between numeric values in an SQL expression. "Numeric" is a little ** bit misleading here. What we mean is that the strings have a ** type of "numeric" from the point of view of SQL. The strings ** do not necessarily contain numbers. They could contain text. ** ** If the input strings both look like actual numbers then they ** compare in numerical order. Numerical strings are always less ** than non-numeric strings so if one input string looks like a ** number and the other does not, then the one that looks like ** a number is the smaller. Non-numeric strings compare in ** lexigraphical order (the same order as strcmp()). */ int sqliteCompare(const char *atext, const char *btext){ int result; int isNumA, isNumB; if( atext==0 ){ return -1; }else if( btext==0 ){ |
︙ | ︙ | |||
692 693 694 695 696 697 698 | result = strcmp(atext, btext); } return result; } /* ** This routine is used for sorting. Each key is a list of one or more | | | | | | | > > > > | | > > > | | < < < | < < < | 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 | result = strcmp(atext, btext); } return result; } /* ** This routine is used for sorting. Each key is a list of one or more ** null-terminated elements. The list is terminated by two nulls in ** a row. For example, the following text is a key with three elements ** ** Aone\000Dtwo\000Athree\000\000 ** ** Both arguments will have the same number of elements. This routine ** returns negative, zero, or positive if the first argument is less ** than, equal to, or greater than the first. (Result is a-b). ** ** Each element begins with one of the characters "+", "-", "A", "D". ** This character determines the sort order and collating sequence: ** ** + Sort numerically in ascending order ** - Sort numerically in descending order ** A Sort as strings in ascending order ** D Sort as strings in descending order. ** ** For the "+" and "-" sorting, pure numeric strings (strings for which the ** isNum() function above returns TRUE) always compare less than strings ** that are not pure numerics. Non-numeric strings compare in memcmp() ** order. This is the same sort order as the sqliteCompare() function ** above generates. ** ** The last point is a change from version 2.6.3 to version 2.7.0. In ** version 2.6.3 and earlier, substrings of digits compare in numerical ** and case was used only to break a tie. ** ** Elements that begin with 'A' or 'D' compare in memcmp() order regardless ** of whether or not they look like a number. ** ** Note that the sort order imposed by the rules above is the same ** from the ordering defined by the "<", "<=", ">", and ">=" operators ** of expressions and for indices. This was not the case for version ** 2.6.3 and earlier. */ int sqliteSortCompare(const char *a, const char *b){ int len; int res = 0; int isNumA, isNumB; while( res==0 && *a && *b ){ |
︙ | ︙ | |||
766 767 768 769 770 771 772 | res = +1; break; } }else if( isNumB ){ res = +1; break; }else{ | | | < < < < < | 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 | res = +1; break; } }else if( isNumB ){ res = +1; break; }else{ res = strcmp(&a[1],&b[1]); if( res ) break; } } len = strlen(&a[1]) + 2; a += len; b += len; } if( *a=='-' || *a=='D' ) res = -res; |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
26 27 28 29 30 31 32 | ** type to the other occurs as necessary. ** ** Most of the code in this file is taken up by the sqliteVdbeExec() ** function which does the work of interpreting a VDBE program. ** But other routines are also provided to help in building up ** a program instruction by instruction. ** | | | 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | ** type to the other occurs as necessary. ** ** Most of the code in this file is taken up by the sqliteVdbeExec() ** function which does the work of interpreting a VDBE program. ** But other routines are also provided to help in building up ** a program instruction by instruction. ** ** $Id: vdbe.c,v 1.168 2002/08/13 23:02:57 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** The following global variable is incremented every time a cursor ** moves, either by the OP_MoveTo or the OP_Next opcode. The test |
︙ | ︙ | |||
2304 2305 2306 2307 2308 2309 2310 | } break; }else{ if( Stringify(p, tos) || Stringify(p, nos) ) goto no_mem; c = strcmp(zStack[nos], zStack[tos]); } switch( pOp->opcode ){ | | | | | | | | 2304 2305 2306 2307 2308 2309 2310 2311 2312 2313 2314 2315 2316 2317 2318 2319 2320 2321 2322 2323 | } break; }else{ if( Stringify(p, tos) || Stringify(p, nos) ) goto no_mem; c = strcmp(zStack[nos], zStack[tos]); } switch( pOp->opcode ){ case OP_StrEq: c = c==0; break; case OP_StrNe: c = c!=0; break; case OP_StrLt: c = c<0; break; case OP_StrLe: c = c<=0; break; case OP_StrGt: c = c>0; break; default: c = c>=0; break; } POPSTACK; POPSTACK; if( pOp->p2 ){ if( c ) pc = pOp->p2-1; }else{ p->tos++; |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
9 10 11 12 13 14 15 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. Also found here are subroutines ** to generate VDBE code to evaluate expressions. ** | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. Also found here are subroutines ** to generate VDBE code to evaluate expressions. ** ** $Id: where.c,v 1.61 2002/08/13 23:02:58 drh Exp $ */ #include "sqliteInt.h" /* ** The query generator uses an array of instances of this structure to ** help it analyze the subexpressions of the WHERE clause. Each WHERE ** clause subexpression is separated from the others by an AND operator. |
︙ | ︙ | |||
745 746 747 748 749 750 751 752 753 754 755 756 757 758 | break; } } } pLevel->iMem = pParse->nMem++; cont = pLevel->cont = sqliteVdbeMakeLabel(v); sqliteVdbeAddOp(v, OP_MakeKey, nColumn, 0); if( nColumn==pIdx->nColumn ){ sqliteVdbeAddOp(v, OP_MemStore, pLevel->iMem, 0); testOp = OP_IdxGT; }else{ sqliteVdbeAddOp(v, OP_Dup, 0, 0); sqliteVdbeAddOp(v, OP_IncrKey, 0, 0); sqliteVdbeAddOp(v, OP_MemStore, pLevel->iMem, 1); | > | 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 | break; } } } pLevel->iMem = pParse->nMem++; cont = pLevel->cont = sqliteVdbeMakeLabel(v); sqliteVdbeAddOp(v, OP_MakeKey, nColumn, 0); sqliteAddIdxKeyType(v, pIdx); if( nColumn==pIdx->nColumn ){ sqliteVdbeAddOp(v, OP_MemStore, pLevel->iMem, 0); testOp = OP_IdxGT; }else{ sqliteVdbeAddOp(v, OP_Dup, 0, 0); sqliteVdbeAddOp(v, OP_IncrKey, 0, 0); sqliteVdbeAddOp(v, OP_MemStore, pLevel->iMem, 1); |
︙ | ︙ | |||
927 928 929 930 931 932 933 934 935 936 937 938 939 940 | }else{ testOp = nEqColumn>0 ? OP_IdxGE : OP_Noop; leFlag = 1; } if( testOp!=OP_Noop ){ pLevel->iMem = pParse->nMem++; sqliteVdbeAddOp(v, OP_MakeKey, nEqColumn + (score & 1), 0); if( leFlag ){ sqliteVdbeAddOp(v, OP_IncrKey, 0, 0); } sqliteVdbeAddOp(v, OP_MemStore, pLevel->iMem, 1); } /* Generate the start key. This is the key that defines the lower | > | 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 | }else{ testOp = nEqColumn>0 ? OP_IdxGE : OP_Noop; leFlag = 1; } if( testOp!=OP_Noop ){ pLevel->iMem = pParse->nMem++; sqliteVdbeAddOp(v, OP_MakeKey, nEqColumn + (score & 1), 0); sqliteAddIdxKeyType(v, pIdx); if( leFlag ){ sqliteVdbeAddOp(v, OP_IncrKey, 0, 0); } sqliteVdbeAddOp(v, OP_MemStore, pLevel->iMem, 1); } /* Generate the start key. This is the key that defines the lower |
︙ | ︙ | |||
971 972 973 974 975 976 977 978 979 980 981 982 983 984 | }else{ geFlag = 1; } brk = pLevel->brk = sqliteVdbeMakeLabel(v); cont = pLevel->cont = sqliteVdbeMakeLabel(v); if( nEqColumn>0 || (score&2)!=0 ){ sqliteVdbeAddOp(v, OP_MakeKey, nEqColumn + ((score&2)!=0), 0); if( !geFlag ){ sqliteVdbeAddOp(v, OP_IncrKey, 0, 0); } sqliteVdbeAddOp(v, OP_MoveTo, pLevel->iCur, brk); }else{ sqliteVdbeAddOp(v, OP_Rewind, pLevel->iCur, brk); } | > | 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 | }else{ geFlag = 1; } brk = pLevel->brk = sqliteVdbeMakeLabel(v); cont = pLevel->cont = sqliteVdbeMakeLabel(v); if( nEqColumn>0 || (score&2)!=0 ){ sqliteVdbeAddOp(v, OP_MakeKey, nEqColumn + ((score&2)!=0), 0); sqliteAddIdxKeyType(v, pIdx); if( !geFlag ){ sqliteVdbeAddOp(v, OP_IncrKey, 0, 0); } sqliteVdbeAddOp(v, OP_MoveTo, pLevel->iCur, brk); }else{ sqliteVdbeAddOp(v, OP_Rewind, pLevel->iCur, brk); } |
︙ | ︙ |
Changes to test/expr.test.
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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing expressions. # | | | 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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing expressions. # # $Id: expr.test,v 1.26 2002/08/13 23:02:58 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table to work with. # execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)} |
︙ | ︙ | |||
185 186 187 188 189 190 191 | test_expr expr-3.31 {t1='xyz', t2=NULL} {t1||t2} {{}} test_expr expr-3.32 {t1='xyz', t2='abc'} {t1||' hi '||t2} {{xyz hi abc}} test_expr expr-4.1 {t1='abc', t2='Abc'} {t1<t2} 0 test_expr expr-4.2 {t1='abc', t2='Abc'} {t1>t2} 1 test_expr expr-4.3 {t1='abc', t2='Bbc'} {t1<t2} 0 test_expr expr-4.4 {t1='abc', t2='Bbc'} {t1>t2} 1 | | | | > > > > > > > > > > | 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 | test_expr expr-3.31 {t1='xyz', t2=NULL} {t1||t2} {{}} test_expr expr-3.32 {t1='xyz', t2='abc'} {t1||' hi '||t2} {{xyz hi abc}} test_expr expr-4.1 {t1='abc', t2='Abc'} {t1<t2} 0 test_expr expr-4.2 {t1='abc', t2='Abc'} {t1>t2} 1 test_expr expr-4.3 {t1='abc', t2='Bbc'} {t1<t2} 0 test_expr expr-4.4 {t1='abc', t2='Bbc'} {t1>t2} 1 test_expr expr-4.5 {t1='0', t2='0.0'} {t1==t2} 0 test_expr expr-4.6 {t1='0.000', t2='0.0'} {t1==t2} 0 test_expr expr-4.7 {t1=' 0.000', t2=' 0.0'} {t1==t2} 0 test_expr expr-4.8 {t1='0.0', t2='abc'} {t1<t2} 1 test_expr expr-4.9 {t1='0.0', t2='abc'} {t1==t2} 0 test_expr expr-4.10 {r1='0.0', r2='abc'} {r1>r2} 0 test_expr expr-4.11 {r1='abc', r2='Abc'} {r1<r2} 0 test_expr expr-4.12 {r1='abc', r2='Abc'} {r1>r2} 1 test_expr expr-4.13 {r1='abc', r2='Bbc'} {r1<r2} 0 test_expr expr-4.14 {r1='abc', r2='Bbc'} {r1>r2} 1 test_expr expr-4.15 {r1='0', r2='0.0'} {r1==r2} 1 test_expr expr-4.16 {r1='0.000', r2='0.0'} {r1==r2} 1 test_expr expr-4.17 {r1=' 0.000', r2=' 0.0'} {r1==r2} 0 test_expr expr-4.18 {r1='0.0', r2='abc'} {r1<r2} 1 test_expr expr-4.19 {r1='0.0', r2='abc'} {r1==r2} 0 test_expr expr-4.20 {r1='0.0', r2='abc'} {r1>r2} 0 test_expr expr-5.1 {t1='abc', t2='xyz'} {t1 LIKE t2} 0 test_expr expr-5.2 {t1='abc', t2='ABC'} {t1 LIKE t2} 1 test_expr expr-5.3 {t1='abc', t2='A_C'} {t1 LIKE t2} 1 test_expr expr-5.4 {t1='abc', t2='abc_'} {t1 LIKE t2} 0 test_expr expr-5.5 {t1='abc', t2='A%C'} {t1 LIKE t2} 1 test_expr expr-5.5b {t1='ac', t2='A%C'} {t1 LIKE t2} 1 |
︙ | ︙ |
Changes to test/func.test.
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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing built-in functions. # | | | 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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing built-in functions. # # $Id: func.test,v 1.15 2002/08/13 23:02:58 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table to work with. # do_test func-0.0 { |
︙ | ︙ | |||
103 104 105 106 107 108 109 | # do_test func-3.0 { execsql {DELETE FROM tbl1} foreach word "contains UTF-8 characters hi\u1234ho" { execsql "INSERT INTO tbl1 VALUES('$word')" } execsql {SELECT t1 FROM tbl1 ORDER BY t1} | | | | | | | | | | | | | 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 | # do_test func-3.0 { execsql {DELETE FROM tbl1} foreach word "contains UTF-8 characters hi\u1234ho" { execsql "INSERT INTO tbl1 VALUES('$word')" } execsql {SELECT t1 FROM tbl1 ORDER BY t1} } "UTF-8 characters contains hi\u1234ho" do_test func-3.1 { execsql {SELECT length(t1) FROM tbl1 ORDER BY t1} } {5 10 8 5} do_test func-3.2 { execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1} } {UT ch co hi} do_test func-3.3 { execsql {SELECT substr(t1,1,3) FROM tbl1 ORDER BY t1} } "UTF cha con hi\u1234" do_test func-3.4 { execsql {SELECT substr(t1,2,2) FROM tbl1 ORDER BY t1} } "TF ha on i\u1234" do_test func-3.5 { execsql {SELECT substr(t1,2,3) FROM tbl1 ORDER BY t1} } "TF- har ont i\u1234h" do_test func-3.6 { execsql {SELECT substr(t1,3,2) FROM tbl1 ORDER BY t1} } "F- ar nt \u1234h" do_test func-3.7 { execsql {SELECT substr(t1,4,2) FROM tbl1 ORDER BY t1} } "-8 ra ta ho" do_test func-3.8 { execsql {SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1} } "8 s s o" do_test func-3.9 { execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1} } "F- er in \u1234h" do_test func-3.10 { execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1} } "TF- ter ain i\u1234h" do_test func-3.99 { execsql {DELETE FROM tbl1} foreach word {this program is free software} { execsql "INSERT INTO tbl1 VALUES('$word')" } execsql {SELECT t1 FROM tbl1} } {this program is free software} |
︙ | ︙ |
Changes to test/index.test.
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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the CREATE INDEX statement. # | | | 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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the CREATE INDEX statement. # # $Id: index.test,v 1.20 2002/08/13 23:02:58 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a basic index and verify it is added to sqlite_master # do_test index-1.1 { |
︙ | ︙ | |||
70 71 72 73 74 75 76 | lappend v $msg } {1 {table test1 has no column named f4}} # Try creating a bunch of indices on the same table # set r {} for {set i 1} {$i<100} {incr i} { | | | | 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 | lappend v $msg } {1 {table test1 has no column named f4}} # Try creating a bunch of indices on the same table # set r {} for {set i 1} {$i<100} {incr i} { lappend r [format index%02d $i] } do_test index-3.1 { execsql {CREATE TABLE test1(f1 int, f2 int, f3 int, f4 int, f5 int)} for {set i 1} {$i<100} {incr i} { set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])" execsql $sql } execsql {SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='test1' ORDER BY name} } $r |
︙ | ︙ |
Changes to test/limit.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the LIMIT ... OFFSET ... clause # of SELECT statements. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the LIMIT ... OFFSET ... clause # of SELECT statements. # # $Id: limit.test,v 1.5 2002/08/13 23:02:58 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data # set fd [open data1.txt w] |
︙ | ︙ | |||
111 112 113 114 115 116 117 | 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x || 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x || 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x || 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x AS y FROM t3 LIMIT 1000; SELECT x FROM t4 ORDER BY y DESC LIMIT 1 OFFSET 999; } | | | 111 112 113 114 115 116 117 118 119 120 | 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x || 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x || 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x || 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x AS y FROM t3 LIMIT 1000; SELECT x FROM t4 ORDER BY y DESC LIMIT 1 OFFSET 999; } } {1000} finish_test |
Changes to test/misc1.test.
︙ | ︙ | |||
9 10 11 12 13 14 15 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for miscellanous features that were # left out of other test files. # | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for miscellanous features that were # left out of other test files. # # $Id: misc1.test,v 1.12 2002/08/13 23:02:58 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Test the creation and use of tables that have a large number # of columns. # |
︙ | ︙ | |||
35 36 37 38 39 40 41 | append cmd ")"; execsql $cmd execsql "SELECT x99 FROM manycol" } 99 do_test misc1-1.2 { execsql {SELECT x0, x10, x25, x50, x75 FROM manycol} } {0 10 25 50 75} | | | > > > | 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 60 61 62 | append cmd ")"; execsql $cmd execsql "SELECT x99 FROM manycol" } 99 do_test misc1-1.2 { execsql {SELECT x0, x10, x25, x50, x75 FROM manycol} } {0 10 25 50 75} do_test misc1-1.3.1 { for {set j 100} {$j<=1000} {incr j 100} { set cmd "INSERT INTO manycol VALUES($j" for {set i 1} {$i<=99} {incr i} { append cmd ",[expr {$i+$j}]" } append cmd ")" execsql $cmd } execsql {SELECT x50 FROM manycol ORDER BY x80+0} } {50 150 250 350 450 550 650 750 850 950 1050} do_test misc1-1.3.2 { execsql {SELECT x50 FROM manycol ORDER BY x80} } {1050 150 250 350 450 550 650 750 50 850 950} do_test misc1-1.4 { execsql {SELECT x75 FROM manycol WHERE x50=350} } 375 do_test misc1-1.5 { execsql {SELECT x50 FROM manycol WHERE x99=599} } 550 do_test misc1-1.6 { |
︙ | ︙ |
Changes to test/sort.test.
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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the CREATE TABLE statement. # | | | 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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the CREATE TABLE statement. # # $Id: sort.test,v 1.5 2002/08/13 23:02:58 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a bunch of data to sort against # do_test sort-1.0 { |
︙ | ︙ | |||
97 98 99 100 101 102 103 | do_test sort-1.11 { execsql {SELECT n FROM t1 ORDER BY log DESC, flt DESC} } {8 7 6 4 5 3 2 1} # These tests are designed to reach some hard-to-reach places # inside the string comparison routines. # | > > > | | | > > | > > | | | | > > > > | < > | 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 | do_test sort-1.11 { execsql {SELECT n FROM t1 ORDER BY log DESC, flt DESC} } {8 7 6 4 5 3 2 1} # These tests are designed to reach some hard-to-reach places # inside the string comparison routines. # # (Later) The sorting behavior changed in 2.7.0. But we will # keep these tests. You can never have too many test cases! # do_test sort-2.1.1 { execsql { UPDATE t1 SET v='x' || -flt; UPDATE t1 SET v='x-2b' where v=='x-0.123'; SELECT v FROM t1 ORDER BY v; } } {x-123 x-2.15 x-2b x-3.141592653 x-4221 x0.0013442 x1.6 x11} do_test sort-2.1.2 { execsql { SELECT v FROM t1 ORDER BY substr(v,2,999); } } {x-123 x-2.15 x-2b x-3.141592653 x-4221 x0.0013442 x1.6 x11} do_test sort-2.1.3 { execsql { SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0; } } {x-4221 x-123 x-3.141592653 x-2.15 x-2b x0.0013442 x1.6 x11} do_test sort-2.1.4 { execsql { SELECT v FROM t1 ORDER BY substr(v,2,999) DESC; } } {x11 x1.6 x0.0013442 x-4221 x-3.141592653 x-2b x-2.15 x-123} do_test sort-2.1.5 { execsql { SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0 DESC; } } {x11 x1.6 x0.0013442 x-2b x-2.15 x-3.141592653 x-123 x-4221} # This is a bug fix for 2.2.4. # Strings are normally mapped to upper-case for a caseless comparison. # But this can cause problems for characters in between 'Z' and 'a'. # do_test sort-3.1 { execsql { |
︙ | ︙ | |||
149 150 151 152 153 154 155 156 | } } {aglie` 2 aglientu 1 agna 3} do_test sort-3.4 { execsql { SELECT a, b FROM t2 ORDER BY a DESC; } } {agna 3 aglientu 1 aglie` 2} | > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 | } } {aglie` 2 aglientu 1 agna 3} do_test sort-3.4 { execsql { SELECT a, b FROM t2 ORDER BY a DESC; } } {agna 3 aglientu 1 aglie` 2} # Version 2.7.0 testing. # do_test sort-4.1 { execsql { INSERT INTO t1 VALUES(9,'x2.7',3,'IX',4.0e5); INSERT INTO t1 VALUES(10,'x5.0e10',3,'X',-4.0e5); INSERT INTO t1 VALUES(11,'x-4.0e9',3,'XI',4.1e4); INSERT INTO t1 VALUES(12,'x01234567890123456789',3,'XII',-4.2e3); SELECT n FROM t1 ORDER BY n; } } {1 2 3 4 5 6 7 8 9 10 11 12} do_test sort-4.2 { execsql { SELECT n||'' FROM t1 ORDER BY 1; } } {1 10 11 12 2 3 4 5 6 7 8 9} do_test sort-4.3 { execsql { SELECT n+0 FROM t1 ORDER BY 1; } } {1 2 3 4 5 6 7 8 9 10 11 12} do_test sort-4.4 { execsql { SELECT n||'' FROM t1 ORDER BY 1 DESC; } } {9 8 7 6 5 4 3 2 12 11 10 1} do_test sort-4.5 { execsql { SELECT n+0 FROM t1 ORDER BY 1 DESC; } } {12 11 10 9 8 7 6 5 4 3 2 1} do_test sort-4.6 { execsql { SELECT v FROM t1 ORDER BY 1; } } {x-123 x-2.15 x-2b x-3.141592653 x-4.0e9 x-4221 x0.0013442 x01234567890123456789 x1.6 x11 x2.7 x5.0e10} do_test sort-4.7 { execsql { SELECT v FROM t1 ORDER BY 1 DESC; } } {x5.0e10 x2.7 x11 x1.6 x01234567890123456789 x0.0013442 x-4221 x-4.0e9 x-3.141592653 x-2b x-2.15 x-123} do_test sort-4.8 { execsql { SELECT substr(v,2,99) FROM t1 ORDER BY 1; } } {-123 -2.15 -2b -3.141592653 -4.0e9 -4221 0.0013442 01234567890123456789 1.6 11 2.7 5.0e10} do_test sort-4.9 { execsql { SELECT substr(v,2,99)+0.0 FROM t1 ORDER BY 1; } } {-4000000000 -4221 -123 -3.141592653 -2.15 -2 0.0013442 1.6 2.7 11 50000000000 1.23456789012346e+18} finish_test |
Changes to test/table.test.
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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the CREATE TABLE statement. # | | | 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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the CREATE TABLE statement. # # $Id: table.test,v 1.19 2002/08/13 23:02:58 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a basic table and verify it is added to sqlite_master # do_test table-1.1 { |
︙ | ︙ | |||
188 189 190 191 192 193 194 | execsql {SELECT name FROM sqlite_master WHERE type!='meta'} } {} # Try creating large numbers of tables # set r {} for {set i 1} {$i<=100} {incr i} { | | | | | | | | | 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 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 | execsql {SELECT name FROM sqlite_master WHERE type!='meta'} } {} # Try creating large numbers of tables # set r {} for {set i 1} {$i<=100} {incr i} { lappend r [format test%03d $i] } do_test table-4.1 { for {set i 1} {$i<=100} {incr i} { set sql "CREATE TABLE [format test%03d $i] (" for {set k 1} {$k<$i} {incr k} { append sql "field$k text," } append sql "last_field text)" execsql $sql } execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} } $r do_test table-4.1b { db close sqlite db test.db execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} } $r # Drop the even numbered tables # set r {} for {set i 1} {$i<=100} {incr i 2} { lappend r [format test%03d $i] } #execsql {--vdbe-trace-on--} do_test table-4.2 { for {set i 2} {$i<=100} {incr i 2} { set sql "DROP TABLE [format TEST%03d $i]" execsql $sql } execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} } $r #exit # Drop the odd number tables # do_test table-4.3 { for {set i 1} {$i<=100} {incr i 2} { set sql "DROP TABLE [format test%03d $i]" execsql $sql } execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} } {} # Try to drop a table that does not exist # do_test table-5.1 { set v [catch {execsql {DROP TABLE test009}} msg] lappend v $msg } {1 {no such table: test009}} # Try to drop sqlite_master # do_test table-5.2 { set v [catch {execsql {DROP TABLE sqlite_master}} msg] lappend v $msg } {1 {table sqlite_master may not be dropped}} |
︙ | ︙ |
Changes to test/version.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the ability of the library to detect # past or future file format version numbers and respond appropriately. # | | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the ability of the library to detect # past or future file format version numbers and respond appropriately. # # $Id: version.test,v 1.6 2002/08/13 23:02:59 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Current file format version set VX 4 # Create a new database # do_test version-1.1 { execsql { CREATE TABLE t1(x); INSERT INTO t1 VALUES(1); |
︙ | ︙ |
Changes to www/changes.tcl.
︙ | ︙ | |||
20 21 22 23 24 25 26 27 28 29 30 31 32 33 | } proc chng {date desc} { puts "<DT><B>$date</B></DT>" puts "<DD><P><UL>$desc</UL></P></DD>" } chng {2002 Aug 12 (2.6.3)} { <li>Add the ability to read both little-endian and big-endian databases. So database created under SunOS or MacOSX can be read and written under Linux or Windows and vice versa.</li> <li>Convert to the new website: http://www.sqlite.org/</li> <li>Allow transactions to span Linux Threads</li> | > > > > > > | 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | } proc chng {date desc} { puts "<DT><B>$date</B></DT>" puts "<DD><P><UL>$desc</UL></P></DD>" } chng {2002 Aug ?? (2.7.0)} { <li>Make a distinction between numeric and text values when sorting. Text values sort according to memcmp(). Numeric values sort in numeric order.</li> } chng {2002 Aug 12 (2.6.3)} { <li>Add the ability to read both little-endian and big-endian databases. So database created under SunOS or MacOSX can be read and written under Linux or Windows and vice versa.</li> <li>Convert to the new website: http://www.sqlite.org/</li> <li>Allow transactions to span Linux Threads</li> |
︙ | ︙ |
Changes to www/formatchng.tcl.
1 2 3 | # # Run this Tcl script to generate the formatchng.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 | # # Run this Tcl script to generate the formatchng.html file. # set rcsid {$Id: formatchng.tcl,v 1.7 2002/08/13 23:02:59 drh Exp $ } puts {<html> <head> <title>File Format Changes in SQLite</title> </head> <body bgcolor=white> <h1 align=center> |
︙ | ︙ | |||
132 133 134 135 136 137 138 139 140 141 142 143 144 145 | the format conversion logic.<p> Version 2.6.0 or later of the library cannot open read-only database files from version 2.5.6 or earlier, since read-only files cannot be upgraded to the new format.</p> </td> </tr> </table> </blockquote> <p> To perform a database reload, have ready versions of the <b>sqlite</b> command-line utility for both the old and new version of SQLite. Call these two executables "<b>sqlite-old</b>" | > > > > > > > > > > > > > > > > > > > > > | 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 | the format conversion logic.<p> Version 2.6.0 or later of the library cannot open read-only database files from version 2.5.6 or earlier, since read-only files cannot be upgraded to the new format.</p> </td> </tr> <tr> <td valign="top">2.6.3 to 2.7.0</td> <td valign="top">2002-Aug-13</td> <td><p>Beginning with version 2.7.0, SQLite understands two different datatypes: text and numeric. Text data sorts in memcmp() order. Numeric data sorts in numerical order if it looks like a number, or in memcmp() order if it does not.</p> <p>When SQLite version 2.7.0 or later opens a 2.6.3 or earlier database, it assumes all columns of all tables have type "numeric". For 2.7.0 and later databases, columns have type "text" if their datatype string contains the substrings "char" or "clob" or "blob" or "text". Otherwise they are of type "numeric".</p> <p>Because "text" columns have a different sort order from numeric, indices on "text" columns occur in a different order for version 2.7.0 and later database. Hence version 2.6.3 and earlier of SQLite will be unable to read a 2.7.0 or later database. But version 2.7.0 and later of SQLite will read earlier database version.</p> </td> </tr> </table> </blockquote> <p> To perform a database reload, have ready versions of the <b>sqlite</b> command-line utility for both the old and new version of SQLite. Call these two executables "<b>sqlite-old</b>" |
︙ | ︙ |