Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix comparison functions so that they are consistent with the order of elements in indices. Fix the handling of large integers. (CVS 317) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
fc2aae04578c305304a45ec6b76d3ab2 |
User & Date: | drh 2001-11-24 00:31:46.000 |
Context
2001-11-24
| ||
00:45 | Version 2.1.3 (CVS 458) (check-in: 974d42839b user: drh tags: trunk) | |
00:31 | Fix comparison functions so that they are consistent with the order of elements in indices. Fix the handling of large integers. (CVS 317) (check-in: fc2aae0457 user: drh tags: trunk) | |
2001-11-23
| ||
00:30 | Version 2.1.2 (CVS 459) (check-in: f14835df32 user: drh tags: trunk) | |
Changes
Changes to Makefile.in.
︙ | ︙ | |||
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 | tclsqlite.html: $(TOP)/www/tclsqlite.tcl tclsh $(TOP)/www/tclsqlite.tcl >tclsqlite.html speed.html: $(TOP)/www/speed.tcl tclsh $(TOP)/www/speed.tcl >speed.html download.html: $(TOP)/www/download.tcl tclsh $(TOP)/www/download.tcl >download.html # Files to be published on the website. # DOC = \ index.html \ sqlite.html \ changes.html \ lang.html \ opcode.html \ arch.html \ arch.png \ vdbe.html \ c_interface.html \ crosscompile.html \ mingw.html \ tclsqlite.html \ download.html \ | > > > | > | 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 | tclsqlite.html: $(TOP)/www/tclsqlite.tcl tclsh $(TOP)/www/tclsqlite.tcl >tclsqlite.html speed.html: $(TOP)/www/speed.tcl tclsh $(TOP)/www/speed.tcl >speed.html faq.html: $(TOP)/www/faq.tcl tclsh $(TOP)/www/faq.tcl >faq.html download.html: $(TOP)/www/download.tcl tclsh $(TOP)/www/download.tcl >download.html # Files to be published on the website. # DOC = \ index.html \ sqlite.html \ changes.html \ lang.html \ opcode.html \ arch.html \ arch.png \ vdbe.html \ c_interface.html \ crosscompile.html \ mingw.html \ tclsqlite.html \ download.html \ speed.html \ faq.html doc: $(DOC) mkdir -p doc mv $(DOC) doc install: sqlite libsqlite.la sqlite.h $(LIBTOOL) $(INSTALL) libsqlite.la $(prefix)/lib |
︙ | ︙ |
Changes to Makefile.template.
︙ | ︙ | |||
325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 | tclsqlite.html: $(TOP)/www/tclsqlite.tcl tclsh $(TOP)/www/tclsqlite.tcl >tclsqlite.html speed.html: $(TOP)/www/speed.tcl tclsh $(TOP)/www/speed.tcl >speed.html download.html: $(TOP)/www/download.tcl tclsh $(TOP)/www/download.tcl >download.html # Files to be published on the website. # DOC = \ index.html \ sqlite.html \ changes.html \ lang.html \ opcode.html \ arch.html \ arch.png \ vdbe.html \ c_interface.html \ crosscompile.html \ mingw.html \ tclsqlite.html \ download.html \ | > > > | > | 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 | tclsqlite.html: $(TOP)/www/tclsqlite.tcl tclsh $(TOP)/www/tclsqlite.tcl >tclsqlite.html speed.html: $(TOP)/www/speed.tcl tclsh $(TOP)/www/speed.tcl >speed.html faq.html: $(TOP)/www/faq.tcl tclsh $(TOP)/www/faq.tcl >faq.html download.html: $(TOP)/www/download.tcl tclsh $(TOP)/www/download.tcl >download.html # Files to be published on the website. # DOC = \ index.html \ sqlite.html \ changes.html \ lang.html \ opcode.html \ arch.html \ arch.png \ vdbe.html \ c_interface.html \ crosscompile.html \ mingw.html \ tclsqlite.html \ download.html \ speed.html \ faq.html doc: $(DOC) mkdir -p doc mv $(DOC) doc install: sqlite libsqlite.a sqlite.h mv sqlite /usr/bin |
︙ | ︙ |
Changes to VERSION.
|
| | | 1 | 2.1.3 |
Changes to src/expr.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains routines used for analyzing expressions and ** for generating VDBE code that evaluates expressions in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains routines used for analyzing expressions and ** for generating VDBE code that evaluates expressions in SQLite. ** ** $Id: expr.c,v 1.34 2001/11/24 00:31:46 drh Exp $ */ #include "sqliteInt.h" /* ** Walk an expression tree. Return 1 if the expression is constant ** and 0 if it involves variables. */ |
︙ | ︙ | |||
507 508 509 510 511 512 513 | sqliteVdbeAddOp(v, OP_Column, pExpr->iTable, pExpr->iColumn); }else{ sqliteVdbeAddOp(v, OP_Recno, pExpr->iTable, 0); } break; } case TK_INTEGER: { | < | > | 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 | sqliteVdbeAddOp(v, OP_Column, pExpr->iTable, pExpr->iColumn); }else{ sqliteVdbeAddOp(v, OP_Recno, pExpr->iTable, 0); } break; } case TK_INTEGER: { sqliteVdbeAddOp(v, OP_String, 0, 0); sqliteVdbeChangeP3(v, -1, pExpr->token.z, pExpr->token.n); break; } case TK_FLOAT: { int addr = sqliteVdbeAddOp(v, OP_String, 0, 0); sqliteVdbeChangeP3(v, addr, pExpr->token.z, pExpr->token.n); break; } |
︙ | ︙ | |||
572 573 574 575 576 577 578 | dest = sqliteVdbeCurrentAddr(v) + 2; sqliteVdbeAddOp(v, op, 0, dest); sqliteVdbeAddOp(v, OP_AddImm, -1, 0); break; } case TK_UMINUS: { assert( pExpr->pLeft ); | | < < < < | 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 | dest = sqliteVdbeCurrentAddr(v) + 2; sqliteVdbeAddOp(v, op, 0, dest); sqliteVdbeAddOp(v, OP_AddImm, -1, 0); break; } case TK_UMINUS: { assert( pExpr->pLeft ); if( pExpr->pLeft->op==TK_FLOAT || pExpr->pLeft->op==TK_INTEGER ){ Token *p = &pExpr->pLeft->token; char *z = sqliteMalloc( p->n + 2 ); sprintf(z, "-%.*s", p->n, p->z); sqliteVdbeAddOp(v, OP_String, 0, 0); sqliteVdbeChangeP3(v, -1, z, p->n+1); sqliteFree(z); break; |
︙ | ︙ |
Changes to src/shell.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains code to implement the "sqlite" command line ** utility for accessing SQLite databases. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains code to implement the "sqlite" command line ** utility for accessing SQLite databases. ** ** $Id: shell.c,v 1.39 2001/11/24 00:31:46 drh Exp $ */ #include <stdlib.h> #include <string.h> #include <stdio.h> #include "sqlite.h" #include <ctype.h> #ifdef OS_UNIX |
︙ | ︙ | |||
552 553 554 555 556 557 558 | }else if( c=='e' && strncmp(azArg[0], "explain", n)==0 ){ p->mode = MODE_Column; p->showHeader = 1; p->colWidth[0] = 4; p->colWidth[1] = 12; | | | | | 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 | }else if( c=='e' && strncmp(azArg[0], "explain", n)==0 ){ p->mode = MODE_Column; p->showHeader = 1; p->colWidth[0] = 4; p->colWidth[1] = 12; p->colWidth[2] = 10; p->colWidth[3] = 10; p->colWidth[4] = 35; }else if( c=='h' && strncmp(azArg[0], "header", n)==0 && nArg>1 ){ int j; char *z = azArg[1]; int val = atoi(azArg[1]); for(j=0; z[j]; j++){ |
︙ | ︙ |
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.33 2001/11/24 00:31:46 drh Exp $ */ #include "sqliteInt.h" #include <stdarg.h> #include <ctype.h> /* ** If malloc() ever fails, this global variable gets set to 1. |
︙ | ︙ | |||
422 423 424 425 426 427 428 | 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; } | > > > > | | > | 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 | 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 ** sort both numbers and alphanumeric words into the correct sequence. ** The same routine should do both without prior knowledge of which ** type of text the input represents. It should even work for strings ** which are a mixture of text and numbers. (It does not work for ** numeric substrings in exponential notation, however.) ** ** To accomplish this, we keep track of a state number while scanning ** the two strings. The states are as follows: ** ** 1 Beginning of word ** 2 Arbitrary text ** 3 Integer |
︙ | ︙ | |||
521 522 523 524 525 526 527 | 1, 0, 2, 1, 4, /* State 2: Integer */ 1, 0, 3, 1, 5, /* State 3: Negative integer */ 1, 0, 4, 1, 1, /* State 4: Real number */ 1, 0, 5, 1, 1, /* State 5: Negative real num */ }; /* This routine does a comparison of two strings. Case is used only | | > | | 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 | 1, 0, 2, 1, 4, /* State 2: Integer */ 1, 0, 3, 1, 5, /* State 3: Negative integer */ 1, 0, 4, 1, 1, /* State 4: Real number */ 1, 0, 5, 1, 1, /* State 5: Negative real num */ }; /* This routine does a comparison of two strings. Case is used only ** if useCase!=0. Numeric substrings compare in numerical order for the ** most part but this routine does not understand exponential notation. */ static int sortStrCmp(const char *atext, const char *btext, int useCase){ register unsigned char *a, *b, *map, ca, cb; int result; register int cclass = 0; a = (unsigned char *)atext; b = (unsigned char *)btext; if( useCase ){ |
︙ | ︙ | |||
595 596 597 598 599 600 601 | result = cb - ca; }; } return result; } /* | | < | | > | | < < | < | < < > | < | < > | > > > > > > | < < < < < < < < < | | | | > > > > > > | | | < > | | | < > | < < | < < < < < < < | < < < < < | | < < < | < < | < < < < < < < < < < < < < < < < < < < < < < < < < < < | | | < < < < | | < < < < < < < | < > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > | > > > > > > | | | < | | | 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 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 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 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 744 745 746 747 748 749 750 751 752 753 | 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. */ static int isNum(const char *z){ if( *z=='-' || *z=='+' ) z++; if( !isdigit(*z) ){ return *z==0; } z++; while( isdigit(*z) ){ z++; } if( *z=='.' ){ z++; if( !isdigit(*z) ) return 0; while( isdigit(*z) ){ z++; } if( *z=='e' || *z=='E' ){ z++; if( *z=='+' || *z=='-' ) z++; if( !isdigit(*z) ) return 0; while( isdigit(*z) ){ z++; } } } return *z==0; } /* This comparison routine is what we use for comparison operations ** in an SQL expression. (Ex: name<'Hello' or value<5). ** ** Numerical strings compare in numerical order. Numerical strings ** are always less than non-numeric strings. Non-numeric strings ** compare in lexigraphical order (the same order as strcmp()). ** ** This is NOT the comparison function used for sorting. The sort ** order is a little bit different. See sqliteSortCompare below ** for additional information. */ int sqliteCompare(const char *atext, const char *btext){ int result; int isNumA = isNum(atext); int isNumB = isNum(btext); if( isNumA ){ if( !isNumB ){ result = -1; }else{ double rA, rB; rA = atof(atext); rB = atof(btext); if( rA<rB ){ result = -1; }else if( rA>rB ){ result = +1; }else{ result = 0; } } }else if( isNumB ){ result = +1; }else { result = strcmp(atext, btext); } return result; } /* ** This routine is used for sorting. Each key is a list of one or more ** null-terminated strings. The list is terminated by two nulls in ** a row. For example, the following text is key with three strings: ** ** +one\000-two\000+three\000\000 ** ** Both arguments will have the same number of strings. 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). ** ** Every string begins with either a "+" or "-" character. If the ** character is "-" then the return value is negated. This is done ** to implement a sort in descending order. ** ** For sorting purposes, pur numeric strings (strings for which the ** isNum() function above returns TRUE) always compare less than strings ** that are not pure numerics. Within non-numeric strings, substrings ** of digits compare in numerical order. Finally, case is used only ** to break a tie. ** ** Note that the sort order imposed by the rules above is different ** from the ordering defined by the "<", "<=", ">", and ">=" operators ** of expressions. The operators compare non-numeric strings in ** lexigraphical order. This routine does the additional processing ** to sort substrings of digits into numerical order and to use case ** only as a tie-breaker. */ int sqliteSortCompare(const char *a, const char *b){ int len; int res = 0; int isNumA, isNumB; while( res==0 && *a && *b ){ isNumA = isNum(&a[1]); isNumB = isNum(&b[1]); if( isNumA ){ double rA, rB; if( !isNumB ){ res = -1; break; } rA = atof(&a[1]); rB = atof(&b[1]); if( rA<rB ){ res = -1; break; } if( rA>rB ){ res = +1; break; } }else if( isNumB ){ res = +1; break; }else{ res = sortStrCmp(&a[1],&b[1],0); if( res==0 ){ res = sortStrCmp(&a[1],&b[1],1); } if( res!=0 ){ break; } } len = strlen(&a[1]) + 2; a += len; b += len; } if( *a=='-' ) res = -res; return res; } /* ** Some powers of 64. These constants are needed in the ** sqliteRealToSortable() routine below. */ #define _64e3 (64.0 * 64.0 * 64.0) #define _64e4 (64.0 * 64.0 * 64.0 * 64.0) #define _64e15 (_64e3 * _64e4 * _64e4 * _64e4) #define _64e16 (_64e4 * _64e4 * _64e4 * _64e4) #define _64e63 (_64e15 * _64e16 * _64e16 * _64e16) #define _64e64 (_64e16 * _64e16 * _64e16 * _64e16) |
︙ | ︙ |
Changes to test/bigrow.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 stressing the library by putting large amounts # of data in a single row of a table. # | | | 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 stressing the library by putting large amounts # of data in a single row of a table. # # $Id: bigrow.test,v 1.4 2001/11/24 00:31:47 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Make a big string that we can use for test data # do_test bigrow-1.0 { |
︙ | ︙ | |||
86 87 88 89 90 91 92 | } } {2} do_test bigrow-1.8 { execsql "SELECT b FROM t1 WHERE a=='$::big1'" } {abc} do_test bigrow-1.9 { execsql "SELECT b FROM t1 WHERE a!='$::big1' ORDER BY a" | | | 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 | } } {2} do_test bigrow-1.8 { execsql "SELECT b FROM t1 WHERE a=='$::big1'" } {abc} do_test bigrow-1.9 { execsql "SELECT b FROM t1 WHERE a!='$::big1' ORDER BY a" } {2 B} # Try doing some indexing on big columns # do_test bigrow-2.1 { execsql { CREATE INDEX i1 ON t1(a) } |
︙ | ︙ |
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.17 2001/11/24 00:31:47 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)} |
︙ | ︙ | |||
69 70 71 72 73 74 75 76 77 78 79 80 81 82 | test_expr expr-1.40 {i1=1, i2=2} {+(i2+i1)} {3} test_expr expr-1.41 {i1=1, i2=2} {-(i2+i1)} {-3} test_expr expr-1.42 {i1=1, i2=2} {i1|i2} {3} test_expr expr-1.43 {i1=1, i2=2} {i1&i2} {0} test_expr expr-1.44 {i1=1} {~i1} {-2} test_expr expr-1.45 {i1=1, i2=3} {i1<<i2} {8} test_expr expr-1.46 {i1=32, i2=3} {i1>>i2} {4} test_expr expr-2.1 {r1=1.23, r2=2.34} {r1+r2} 3.57 test_expr expr-2.2 {r1=1.23, r2=2.34} {r1-r2} -1.11 test_expr expr-2.3 {r1=1.23, r2=2.34} {r1*r2} 2.8782 test_expr expr-2.4 {r1=1.23, r2=2.34} {r1/r2} 0.525641025641026 test_expr expr-2.5 {r1=1.23, r2=2.34} {r2/r1} 1.90243902439024 test_expr expr-2.6 {r1=1.23, r2=2.34} {r2<r1} 0 | > > > > > > > > > | 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 | test_expr expr-1.40 {i1=1, i2=2} {+(i2+i1)} {3} test_expr expr-1.41 {i1=1, i2=2} {-(i2+i1)} {-3} test_expr expr-1.42 {i1=1, i2=2} {i1|i2} {3} test_expr expr-1.43 {i1=1, i2=2} {i1&i2} {0} test_expr expr-1.44 {i1=1} {~i1} {-2} test_expr expr-1.45 {i1=1, i2=3} {i1<<i2} {8} test_expr expr-1.46 {i1=32, i2=3} {i1>>i2} {4} test_expr expr-1.47 {i1=9999999999, i2=8888888888} {i1<i2} 0 test_expr expr-1.48 {i1=9999999999, i2=8888888888} {i1=i2} 0 test_expr expr-1.49 {i1=9999999999, i2=8888888888} {i1>i2} 1 test_expr expr-1.50 {i1=99999999999, i2=99999999998} {i1<i2} 0 test_expr expr-1.51 {i1=99999999999, i2=99999999998} {i1=i2} 0 test_expr expr-1.52 {i1=99999999999, i2=99999999998} {i1>i2} 1 test_expr expr-1.53 {i1=099999999999, i2=99999999999} {i1<i2} 0 test_expr expr-1.54 {i1=099999999999, i2=99999999999} {i1=i2} 1 test_expr expr-1.55 {i1=099999999999, i2=99999999999} {i1>i2} 0 test_expr expr-2.1 {r1=1.23, r2=2.34} {r1+r2} 3.57 test_expr expr-2.2 {r1=1.23, r2=2.34} {r1-r2} -1.11 test_expr expr-2.3 {r1=1.23, r2=2.34} {r1*r2} 2.8782 test_expr expr-2.4 {r1=1.23, r2=2.34} {r1/r2} 0.525641025641026 test_expr expr-2.5 {r1=1.23, r2=2.34} {r2/r1} 1.90243902439024 test_expr expr-2.6 {r1=1.23, r2=2.34} {r2<r1} 0 |
︙ | ︙ | |||
119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 | test_expr expr-3.19 {t1='abc', t2='xyz'} {t1<>t2} 1 test_expr expr-3.20 {t1='xyz', t2='abc'} {t1<>t2} 1 test_expr expr-3.21 {t1='abc', t2='abc'} {t1<>t2} 0 test_expr expr-3.22 {t1='abc', t2='xyz'} {t1!=t2} 1 test_expr expr-3.23 {t1='xyz', t2='abc'} {t1!=t2} 1 test_expr expr-3.24 {t1='abc', t2='abc'} {t1!=t2} 0 test_expr expr-3.25 {t1=NULL, t2='hi'} {t1 isnull} 1 test_expr expr-3.26 {t1=NULL, t2='hi'} {t2 isnull} 0 test_expr expr-3.27 {t1=NULL, t2='hi'} {t1 notnull} 0 test_expr expr-3.28 {t1=NULL, t2='hi'} {t2 notnull} 1 test_expr expr-3.29 {t1='xyz', t2='abc'} {t1||t2} {xyzabc} test_expr expr-3.30 {t1=NULL, t2='abc'} {t1||t2} {abc} test_expr expr-3.31 {t1='xyz', t2=NULL} {t1||t2} {xyz} 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 | > > | | > > > > > > | 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 | test_expr expr-3.19 {t1='abc', t2='xyz'} {t1<>t2} 1 test_expr expr-3.20 {t1='xyz', t2='abc'} {t1<>t2} 1 test_expr expr-3.21 {t1='abc', t2='abc'} {t1<>t2} 0 test_expr expr-3.22 {t1='abc', t2='xyz'} {t1!=t2} 1 test_expr expr-3.23 {t1='xyz', t2='abc'} {t1!=t2} 1 test_expr expr-3.24 {t1='abc', t2='abc'} {t1!=t2} 0 test_expr expr-3.25 {t1=NULL, t2='hi'} {t1 isnull} 1 test_expr expr-3.25b {t1=NULL, t2='hi'} {t1 is null} 1 test_expr expr-3.26 {t1=NULL, t2='hi'} {t2 isnull} 0 test_expr expr-3.27 {t1=NULL, t2='hi'} {t1 notnull} 0 test_expr expr-3.28 {t1=NULL, t2='hi'} {t2 notnull} 1 test_expr expr-3.28b {t1=NULL, t2='hi'} {t2 is not null} 1 test_expr expr-3.29 {t1='xyz', t2='abc'} {t1||t2} {xyzabc} test_expr expr-3.30 {t1=NULL, t2='abc'} {t1||t2} {abc} test_expr expr-3.31 {t1='xyz', t2=NULL} {t1||t2} {xyz} 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} 1 test_expr expr-4.6 {t1='0.000', t2='0.0'} {t1==t2} 1 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 {t1='0.0', t2='abc'} {t1>t2} 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/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.17 2001/11/24 00:31:47 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 { |
︙ | ︙ | |||
326 327 328 329 330 331 332 333 334 | } for {set i 1} {$i<=50} {incr i} { execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)" } set sqlite_search_count 0 concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count } {0.10 3} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 | } for {set i 1} {$i<=50} {incr i} { execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)" } set sqlite_search_count 0 concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count } {0.10 3} # Numeric strings should compare as if they were numbers. So even if the # strings are not character-by-character the same, if they represent the # same number they should compare equal to one another. Verify that this # is true in indices. # do_test index-12.1 { execsql { CREATE TABLE t4(a,b); INSERT INTO t4 VALUES('0.0',1); INSERT INTO t4 VALUES('0.00',2); INSERT INTO t4 VALUES('abc',3); INSERT INTO t4 VALUES('-1.0',4); INSERT INTO t4 VALUES('+1.0',5); INSERT INTO t4 VALUES('0',6); INSERT INTO t4 VALUES('00000',7); SELECT a FROM t4 ORDER BY b; } } {0.0 0.00 abc -1.0 +1.0 0 00000} do_test index-12.2 { execsql { SELECT a FROM t4 WHERE a==0 ORDER BY b } } {0.0 0.00 0 00000} do_test index-12.3 { execsql { SELECT a FROM t4 WHERE a<0.5 ORDER BY b } } {0.0 0.00 -1.0 0 00000} do_test index-12.4 { execsql { SELECT a FROM t4 WHERE a>-0.5 ORDER BY b } } {0.0 0.00 abc +1.0 0 00000} do_test index-12.5 { execsql { CREATE INDEX t4i1 ON t4(a); SELECT a FROM t4 WHERE a==0 ORDER BY b } } {0.0 0.00 0 00000} do_test index-12.6 { execsql { SELECT a FROM t4 WHERE a<0.5 ORDER BY b } } {0.0 0.00 -1.0 0 00000} do_test index-12.7 { execsql { SELECT a FROM t4 WHERE a>-0.5 ORDER BY b } } {0.0 0.00 abc +1.0 0 00000} finish_test |
Changes to www/changes.tcl.
︙ | ︙ | |||
12 13 14 15 16 17 18 19 20 21 22 23 24 25 | } proc chng {date desc} { puts "<DT><B>$date</B></DT>" puts "<DD><P><UL>$desc</UL></P></DD>" } chng {2001 Nov 22 (2.1.2)} { <li>Changes to support 64-bit architectures.</li> <li>Fix a bug in the locking protocol.</li> <li>Fix a bug that could (rarely) cause the database to become unreadable after a DROP TABLE due to corruption to the SQLITE_MASTER table.</li> | > > > > > > > > | 12 13 14 15 16 17 18 19 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 {2001 Nov 23 (2.1.3)} { <li>Fix the behavior of comparison operators (ex: "<b><</b>", "<b>==</b>", etc.) so that they are consistent with the order of entries in an index.</li> <li>Correct handling of integers in SQL expressions that are larger than what can be represented by the machine integer.</li> } chng {2001 Nov 22 (2.1.2)} { <li>Changes to support 64-bit architectures.</li> <li>Fix a bug in the locking protocol.</li> <li>Fix a bug that could (rarely) cause the database to become unreadable after a DROP TABLE due to corruption to the SQLITE_MASTER table.</li> |
︙ | ︙ |
Added www/faq.tcl.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 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 | # # Run this script to generated a faq.html output file # puts {<html> <head> <title>SQLite Frequently Asked Questions</title> </head> <body bgcolor="white"> <h1 align="center">Frequently Asked Questions</h1> } set cnt 1 proc faq {question answer} { set ::faq($::cnt) [list [string trim $question] [string trim $answer]] incr ::cnt } ############# # Enter questions and answers here. faq { How do I create an AUTOINCREMENT field. } { SQLite does not support AUTOINCREMENT. If you need a unique key for a new entry in a table, you can create an auxiliary table with a single entry that holds the next available value for that key. Like this: <blockquote><pre> CREATE TABLE counter(cnt); INSERT INTO counter VALUES(1); </pre></blockquote> Once you have a counter set up, you can generate a unique key as follows: <blockquote><pre> BEGIN TRANSACTION; SELECT cnt FROM counter; UPDATE counter SET cnt=cnt+1; COMMIT; </pre></blockquote> There are other ways of simulating the effect of AUTOINCREMENT but this approach seems to be the easiest and most efficient. } faq { SQLite lets me insert a string into a database column of type integer! } { <p>This is a feature, not a bug. SQLite is typeless. Any data can be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. The datatype you assign to a column in the CREATE TABLE command is (mostly) ignored. Every column is able to hold an arbitrary length string.</p> <p>Because SQLite ignores data types, you can omit the data type definition from columns in CREATE TABLE statements. For example, instead of saying <blockquote><pre> CREATE TABLE t1( f1 int, f2 varchar(10), f3 boolean ); </pre></blockquote> You can save yourself a lot of typing and formatting by omitting the data type declarations, like this: <blockquote><pre> CREATE TABLE t1(f1,f2,f3); </pre></blockquote> </p> } faq { Why does SQLite think that the expression '0'=='00' is TRUE? } { <p>This is a consequence of SQLite being typeless. All data is stored internally as a null-terminated string. There is no concept of separate data types for strings and numbers.</p> <p>When doing a comparison, SQLite looks at the string on both sides of the comparison operator. If both strings look like pure numeric values (with no extra punctuation or spacing) then the strings are converted to floating point numbers using <b>atof()</b> and the results are compared. The results of <b>atof("0")</b> and <b>atof("00")</b> are both 0.0, so those two strings are considered to be equal.</p> <p>If only one string in a comparison is a pure numeric, then that string is assumed to be less than the other. Of neither string is a pure numeric, then <b>strcmp()</b> is used for the comparison.</p> } faq { The second INSERT in the following sequence of commands returns with constraint error. <blockquote> CREATE TABLE t(s varchar(10) primary key);<br> INSERT INTO t VALUES('0');<br> INSERT INTO t VALUES('0.0');<br> </blockquote> Why is this? } { <p>Because column <b>s</b> is a primary key, all values of <b>s</b> must be unique. But SQLite thinks that <b>'0'</b> and <b>'0.0'</b> are the same value because they compare equal to one another numerically. (See the previous question.) Hence the values are not unique and the constraint fails.</p> <p>You can work around this issue in several ways:</p> <ol> <li><p>Remove the <b>primary key</b> clause from the CREATE TABLE so that <b>s</b> can contain more than one entry with the same value. If you need an index on the <b>s</b> column then create it separately. </p></li> <li><p>Prepend a space to the beginning of every <b>s</b> value. The initial space will mean that the entries are not pure numerics and hence will be compared as strings using <b>strcmp()</b>.</p></li> </ol> } faq { My linux box is not able to read an SQLite database that was created on my SparcStation. } { <p>The x86 processor on your windows box is little-endian (meaning that the least signification byte of integers comes first) but the Sparc is big-endian (the most significant bytes comes first). SQLite databases created on a little-endian architecture cannot be used on a big-endian machine and vice versa.</p> <p>If you need to move the database from one machine to another, you'll have to do an ASCII dump of the database on the source machine and then reconstruct the database at the destination machine. The following is a typical command for transferring an SQLite databases between two machines: <blockquote><pre> echo .dump | sqlite from.db | ssh sparc 'sqlite to.db' </pre></blockquote> The command above assumes the name of the destination machine is <b>sparc</b> and that you have SSH running on both the source and destination. An alternative approach is to save the output of the first <b>sqlite</b> command in a temporary file, move the temporary file to the destination machine, then run the second <b>sqlite</b> command while redirecting input from the temporary file.</p> } # End of questions and answers. ############# puts {<DL COMPACT>} for {set i 1} {$i<$cnt} {incr i} { puts " <DT><A HREF=\"#q$i\">($i)</A></DT>" puts " <DD>[lindex $faq($i) 0]</DD>" } puts {</DL><HR />} for {set i 1} {$i<$cnt} {incr i} { puts "<A NAME=\"q$i\">" puts "<P><B>($i) [lindex $faq($i) 0]</B></P>\n" puts "<BLOCKQUOTE>[lindex $faq($i) 1]</BLOCKQUOTE>\n" } 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.48 2001/11/24 00:31:47 drh Exp $} puts {<html> <head><title>SQLite: An SQL Database Engine In A C Library</title></head> <body bgcolor=white> <h1 align=center>SQLite: An SQL Database Engine In A C Library</h1> <p align=center>} puts "This page was last modified on [lrange $rcsid 3 4] GMT<br>" |
︙ | ︙ | |||
101 102 103 104 105 106 107 108 109 110 111 112 113 114 | </p> <h2>Documentation</h2> <p>The following documentation is currently available:</p> <p><ul> <li>Information on the <a href="sqlite.html">sqlite</a> command-line utility.</li> <li>The <a href="lang.html">SQL Language</a> subset understood by SQLite.</li> <li>The <a href="c_interface.html">C/C++ Interface</a>.</li> <li>The <a href="tclsqlite.html">Tcl Binding</a> to SQLite.</li> <li>The <a href="arch.html">Architecture of the SQLite Library</a> describes how the library is put together.</li> | > | 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 | </p> <h2>Documentation</h2> <p>The following documentation is currently available:</p> <p><ul> <li><a href="faq.html">Frequently Asked Questions</a> are available online.</li> <li>Information on the <a href="sqlite.html">sqlite</a> command-line utility.</li> <li>The <a href="lang.html">SQL Language</a> subset understood by SQLite.</li> <li>The <a href="c_interface.html">C/C++ Interface</a>.</li> <li>The <a href="tclsqlite.html">Tcl Binding</a> to SQLite.</li> <li>The <a href="arch.html">Architecture of the SQLite Library</a> describes how the library is put together.</li> |
︙ | ︙ |