SQLite

Check-in [fc2aae0457]
Login

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: fc2aae04578c305304a45ec6b76d3ab216cc7526
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
Unified Diff Ignore Whitespace Patch
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
293

294
295
296
297
298
299
300

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 \
  speed.html


doc:	$(DOC)
	mkdir -p doc
	mv $(DOC) doc

install:	sqlite libsqlite.la sqlite.h
	$(LIBTOOL) $(INSTALL) libsqlite.la $(prefix)/lib







>
>
>




















|
>







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
352

353
354
355
356
357
358
359

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 \
  speed.html


doc:	$(DOC)
	mkdir -p doc
	mv $(DOC) doc

install:	sqlite libsqlite.a sqlite.h
	mv sqlite /usr/bin







>
>
>




















|
>







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.2
|
1
2.1.3
Changes to src/expr.c.
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.33 2001/11/08 00:45:21 drh Exp $
*/
#include "sqliteInt.h"

/*
** Walk an expression tree.  Return 1 if the expression is constant
** and 0 if it involves variables.
*/







|







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
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: {
      int i = atoi(pExpr->token.z);
      sqliteVdbeAddOp(v, OP_Integer, i, 0);

      break;
    }
    case TK_FLOAT: {
      int addr = sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeChangeP3(v, addr, pExpr->token.z, pExpr->token.n);
      break;
    }







<
|
>







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
579
580
581
582
583
584
585
586
587
588
589
590
      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_INTEGER ){
        int i = atoi(pExpr->pLeft->token.z);
        sqliteVdbeAddOp(v, OP_Integer, -i, 0);
        break;
      }else if( pExpr->pLeft->op==TK_FLOAT ){
        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;







|
<
<
<
<







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
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.38 2001/11/09 22:41:45 drh Exp $
*/
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include "sqlite.h"
#include <ctype.h>
#ifdef OS_UNIX







|







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
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] = 5;
    p->colWidth[3] = 5;
    p->colWidth[4] = 40;
  }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++){







|
|
|







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
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.32 2001/11/21 02:21:12 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

/*
** If malloc() ever fails, this global variable gets set to 1.







|







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




429
430
431
432
433
434
435

436
437
438
439
440
441
442
  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;
}





/* Notes on string comparisions.
**
** 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.

**
** 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







>
>
>
>
|





|
>







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
528

529
530
531
532
533
534
535
536
537
      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.  Numbers compare in numerical order.

*/
static int privateStrCmp(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 ){







|
>

|







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
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
754
755
756
757
758
759
760
761
762
763
764
765
766
      result = cb - ca;
    };
  }
  return result;
}

/*
** Do a comparison of pure numerics.  If either string is not a pure
** numeric, then return 0.  Otherwise return 1 and set *pResult to be
** negative, zero or positive if the first string are numerially less than
** equal to, or greater than the second.

*/
static int privateCompareNum(const char *a, const char *b, int *pResult){
  char *endPtr;
  double rA, rB;
  int isNumA, isNumB;
  if( isdigit(*a) || ((*a=='-' || *a=='+') && isdigit(a[1])) ){
    rA = strtod(a, &endPtr);
    isNumA = *endPtr==0;
  }else{
    isNumA = 0;
  }

  if( isdigit(*b) || ((*b=='-' || *b=='+') && isdigit(b[1])) ){
    rB = strtod(b, &endPtr);
    isNumB = *endPtr==0;
  }else{

    isNumB = 0;






  }
  if( isNumB==0 && isNumA==0 ) return 0;
  if( isNumA!=isNumB ){
    *pResult =  isNumA - isNumB;
  }else if( rA<rB ){
    *pResult = -1;
  }else if( rA>rB ){
    *pResult = 1;
  }else{
    *pResult = 0;
  }
  return 1;
}

/* This comparison routine is what we use for comparison operations
** in an SQL expression.  (Ex:  name<'Hello' or value<5).  Compare two
** strings.  Use case only as a tie-breaker.  Numbers compare in
** numerical order.






*/
int sqliteCompare(const char *atext, const char *btext){
  int result;
  if( !privateCompareNum(atext, btext, &result) || result==0 ){
    result = privateStrCmp(atext, btext, 0);
    if( result==0 ) result = privateStrCmp(atext, btext, 1);
  }

  return result;
}

/*

** If you compile just this one file with the -DTEST_COMPARE=1 option,
** it generates a program to test the comparisons routines.  
*/
#ifdef TEST_COMPARE
#include <stdlib.h>
#include <stdio.h>
int sortCmp(const char **a, const char **b){
  return sqliteCompare(*a, *b);
}
int main(int argc, char **argv){
  int i, j, k, n, cnt;
  static char *azStr[] = {
     "abc", "aBc", "abcd", "aBcd", 
     "123", "124", "1234", "-123", "-124", "-1234", "+124",
     "123.45", "123.456", "123.46", "-123.45", "-123.46", "-123.456", 
     "x9", "x10", "x-9", "x-10", "X9", "X10",
     "1.234e+02", "+123", "1.23E2", "1.2345e+2", "-1.2345e2", "+w"
  };
  n = sizeof(azStr)/sizeof(azStr[0]);
  qsort(azStr, n, sizeof(azStr[0]), sortCmp);
  for(i=0; i<n; i++){
    printf("%s\n", azStr[i]);
  }
  printf("Sanity1...");
  fflush(stdout);
  cnt = 0;
  for(i=0; i<n-1; i++){
    char *a = azStr[i];
    for(j=i+1; j<n; j++){
      char *b = azStr[j];
      if( sqliteCompare(a,b) != -sqliteCompare(b,a) ){
        printf("Failed!  \"%s\" vs \"%s\"\n", a, b);
        i = j = n;
      }
      cnt++;
    }
  }
  if( i<n ){
    printf(" OK (%d)\n", cnt);
  }
  printf("Sanity2...");
  fflush(stdout);
  cnt = 0;
  for(i=0; i<n; i++){
    char *a = azStr[i];
    for(j=0; j<n; j++){
      char *b = azStr[j];
      for(k=0; k<n; k++){
        char *c = azStr[k];
        int x1, x2, x3, success;
        x1 = sqliteCompare(a,b);
        x2 = sqliteCompare(b,c);
        x3 = sqliteCompare(a,c);
        if( x1==0 ){
          success = x2==x3;
        }else if( x1<0 ){
          success = (x2<=0 && x3<=0) || x2>0;
        }else{
          success = (x2>=0 && x3>=0) || x2<0;
        }
        if( !success ){
          printf("Failed!  \"%s\" vs \"%s\" vs \"%s\"\n", a, b, c);
          i = j = k = n+1;
        }
        cnt++;
      }
    }
  }
  if( i<n+1 ){
    printf(" OK (%d)\n", cnt);
  }
  return 0;
}
#endif

/*
** 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.













*/
int sqliteSortCompare(const char *a, const char *b){
  int len;
  int res = 0;


  while( res==0 && *a && *b ){





    res = sqliteCompare(&a[1], &b[1]);

















    if( res==0 ){






      len = strlen(a) + 1;
      a += len;
      b += len;
    }
  }
  if( *a=='-' ) res = -res;
  return res;
}

/*
** Some powers of 64.  These numbers and their recipricals should
** all have exact representations in the floating point format.
*/
#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)







|
<
|
|
>

|
|
<
<
|
<
|
<
<

>
|
<
|
<
>
|
>
>
>
>
>
>
|
<
<
<
<
<
<
<
<
<

|



|
|
|
>
>
>
>
>
>



|
|
|
<
>
|
|
|
<
>
|
<
<
|
<
<
<
<
<
<
<
|
<
<
<
<
<
|
|
<
<
<
|
<
<
|
<
<
<
<
<
<
<

<

<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
|
|
<
<
<
<
|
|
<
<
<
<
<
<
<
|

<















>
>
>
>
>
>
>
>
>
>
>
>
>




>


>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
|
|
|
<






|
|







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
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.3 2001/11/12 13:10:53 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 {







|







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
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"
} {B 2}

# Try doing some indexing on big columns
#
do_test bigrow-2.1 {
  execsql {
    CREATE INDEX i1 ON t1(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
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.16 2001/10/13 02:59:09 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)}













|







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
136
137






138
139
140
141
142
143
144
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
test_expr expr-4.3 {t1='abc', t2='Bbc'} {t1<t2} 1
test_expr expr-4.4 {t1='abc', t2='Bbc'} {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







>



>







|
|
>
>
>
>
>
>







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
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.16 2001/11/23 00:24:12 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 {













|







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>&lt</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
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.47 2001/11/12 12:43:22 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>"



|







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>