/ Check-in [38401dfb]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Bug fixes and enhancements entered while on jury recess. (CVS 2246)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 38401dfbd5e3b50dd4e7a11562a7770347cebdf4
User & Date: drh 2005-01-20 22:48:48
Context
2005-01-20
23:23
Fix comment typo in mkopcodec.awk. (CVS 2247) check-in: 61771482 user: drh tags: trunk
22:48
Bug fixes and enhancements entered while on jury recess. (CVS 2246) check-in: 38401dfb user: drh tags: trunk
13:36
Added the SQLITE_OMIT_SUBQUERY compile-time option and the EXISTS operator. Regression tests are currently failing with an assertion fault. (CVS 2245) check-in: d30fdf0f user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
607
608
609
610
611
612
613




614
615
616
617
618
619
620
....
1530
1531
1532
1533
1534
1535
1536


1537
1538
1539
1540
1541
1542
1543
1544
1545

1546
1547
1548
1549
1550
1551
1552
**    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.185 2005/01/20 13:36:20 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
static int exprNodeIsConstant(void *pArg, Expr *pExpr){
  switch( pExpr->op ){
    case TK_ID:
    case TK_COLUMN:
    case TK_DOT:
    case TK_AGG_FUNCTION:
    case TK_FUNCTION:




      *((int*)pArg) = 0;
      return 2;
    default:
      return 0;
  }
}

................................................................................
      if( pDef->needCollSeq ){
        if( !pColl ) pColl = pParse->db->pDfltColl; 
        sqlite3VdbeOp3(v, OP_CollSeq, 0, 0, (char *)pColl, P3_COLLSEQ);
      }
      sqlite3VdbeOp3(v, OP_Function, nExpr, p2, (char*)pDef, P3_FUNCDEF);
      break;
    }


    case TK_SELECT: {
      if( pExpr->iTable>=0 ){
        sqlite3VdbeAddOp(v, OP_Gosub, 0, pExpr->iTable);
        VdbeComment((v, "# run subquery"));
      }
      sqlite3VdbeAddOp(v, OP_MemLoad, pExpr->iColumn, 0);
      VdbeComment((v, "# load subquery result"));
      break;
    }

    case TK_IN: {
      int addr;
      char affinity;

      /* Figure out the affinity to use to create a key from the results
      ** of the expression. affinityStr stores a static string suitable for
      ** P3 of OP_MakeRecord.







|







 







>
>
>
>







 







>
>









>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
....
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
**    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.186 2005/01/20 22:48:48 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
static int exprNodeIsConstant(void *pArg, Expr *pExpr){
  switch( pExpr->op ){
    case TK_ID:
    case TK_COLUMN:
    case TK_DOT:
    case TK_AGG_FUNCTION:
    case TK_FUNCTION:
#ifndef SQLITE_OMIT_SUBQUERY
    case TK_SELECT:
    case TK_EXISTS:
#endif
      *((int*)pArg) = 0;
      return 2;
    default:
      return 0;
  }
}

................................................................................
      if( pDef->needCollSeq ){
        if( !pColl ) pColl = pParse->db->pDfltColl; 
        sqlite3VdbeOp3(v, OP_CollSeq, 0, 0, (char *)pColl, P3_COLLSEQ);
      }
      sqlite3VdbeOp3(v, OP_Function, nExpr, p2, (char*)pDef, P3_FUNCDEF);
      break;
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case TK_EXISTS:
    case TK_SELECT: {
      if( pExpr->iTable>=0 ){
        sqlite3VdbeAddOp(v, OP_Gosub, 0, pExpr->iTable);
        VdbeComment((v, "# run subquery"));
      }
      sqlite3VdbeAddOp(v, OP_MemLoad, pExpr->iColumn, 0);
      VdbeComment((v, "# load subquery result"));
      break;
    }
#endif
    case TK_IN: {
      int addr;
      char affinity;

      /* Figure out the affinity to use to create a key from the results
      ** of the expression. affinityStr stores a static string suitable for
      ** P3 of OP_MakeRecord.

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.231 2005/01/20 13:36:20 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  ExprList *pEList    /* Expressions defining the result set */
){
  Vdbe *v = pParse->pVdbe;
  int i, j;
  sqlite3 *db = pParse->db;
  int fullNames, shortNames;

#ifdef SQLITE_OMIT_EXPLAIN
  /* If this is an EXPLAIN, skip this step */
  if( pParse->explain ){
    return;
  }
#endif

  assert( v!=0 );







|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.232 2005/01/20 22:48:48 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  ExprList *pEList    /* Expressions defining the result set */
){
  Vdbe *v = pParse->pVdbe;
  int i, j;
  sqlite3 *db = pParse->db;
  int fullNames, shortNames;

#ifndef SQLITE_OMIT_EXPLAIN
  /* If this is an EXPLAIN, skip this step */
  if( pParse->explain ){
    return;
  }
#endif

  assert( v!=0 );

Changes to src/util.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.127 2005/01/17 07:53:44 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

#if SQLITE_MEMDEBUG>2 && defined(__GLIBC__)
#include <execinfo.h>
................................................................................
** for all bytes that have the 8th bit set and one byte with the 8th
** bit clear.  Except, if we get to the 9th byte, it stores the full
** 8 bits and is the last byte.
*/
int sqlite3PutVarint(unsigned char *p, u64 v){
  int i, j, n;
  u8 buf[10];
  if( v & 0xff00000000000000 ){
    p[8] = v;
    v >>= 8;
    for(i=7; i>=0; i--){
      p[i] = (v & 0x7f) | 0x80;
      v >>= 7;
    }
    return 9;







|







 







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.128 2005/01/20 22:48:48 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

#if SQLITE_MEMDEBUG>2 && defined(__GLIBC__)
#include <execinfo.h>
................................................................................
** for all bytes that have the 8th bit set and one byte with the 8th
** bit clear.  Except, if we get to the 9th byte, it stores the full
** 8 bits and is the last byte.
*/
int sqlite3PutVarint(unsigned char *p, u64 v){
  int i, j, n;
  u8 buf[10];
  if( v & (((u64)0xff000000)<<32) ){
    p[8] = v;
    v >>= 8;
    for(i=7; i>=0; i--){
      p[i] = (v & 0x7f) | 0x80;
      v >>= 7;
    }
    return 9;

Changes to src/vdbe.c.

39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
....
2864
2865
2866
2867
2868
2869
2870







2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
....
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.443 2005/01/17 03:40:08 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
    ** to double the speed of the COPY operation.
    */
    int res, rx=SQLITE_OK, cnt;
    i64 x;
    cnt = 0;
    assert( (sqlite3BtreeFlags(pC->pCursor) & BTREE_INTKEY)!=0 );
    assert( (sqlite3BtreeFlags(pC->pCursor) & BTREE_ZERODATA)==0 );







    if( !pC->useRandomRowid ){
      if( pC->nextRowidValid ){
        v = pC->nextRowid;
      }else{
        rx = sqlite3BtreeLast(pC->pCursor, &res);
        if( res ){
          v = 1;
        }else{
          sqlite3BtreeKeySize(pC->pCursor, &v);
          v = keyToInt(v);
          if( v==0x7fffffffffffffff ){
            pC->useRandomRowid = 1;
          }else{
            v++;
          }
        }
      }

................................................................................
#ifndef SQLITE_OMIT_AUTOINCREMENT
      if( pOp->p2 ){
        Mem *pMem;
        assert( pOp->p2>0 && pOp->p2<p->nMem );  /* P2 is a valid memory cell */
        pMem = &p->aMem[pOp->p2];
        Integerify(pMem);
        assert( (pMem->flags & MEM_Int)!=0 );  /* mem(P2) holds an integer */
        if( pMem->i==0x7fffffffffffffff || pC->useRandomRowid ){
          rc = SQLITE_FULL;
          goto abort_due_to_error;
        }
        if( v<pMem->i+1 ){
          v = pMem->i + 1;
        }
        pMem->i = v;
      }
#endif

      if( v<0x7fffffffffffffff ){
        pC->nextRowidValid = 1;
        pC->nextRowid = v+1;
      }else{
        pC->nextRowidValid = 0;
      }
    }
    if( pC->useRandomRowid ){







|







 







>
>
>
>
>
>
>










|







 







|










|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
....
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
....
2896
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
2919
2920
2921
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.444 2005/01/20 22:48:48 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
    ** to double the speed of the COPY operation.
    */
    int res, rx=SQLITE_OK, cnt;
    i64 x;
    cnt = 0;
    assert( (sqlite3BtreeFlags(pC->pCursor) & BTREE_INTKEY)!=0 );
    assert( (sqlite3BtreeFlags(pC->pCursor) & BTREE_ZERODATA)==0 );

    /* Some compilers complain about constants of the form 0x7fffffffffffffff.
    ** Others complain about 0x7ffffffffffffffffLL.  The following macro seems
    ** to provide the constant while making all compilers happy.
    */
#   define MAX_I64  ( (((u64)0x7fffffff)<<32) | (u64)0xffffffff )

    if( !pC->useRandomRowid ){
      if( pC->nextRowidValid ){
        v = pC->nextRowid;
      }else{
        rx = sqlite3BtreeLast(pC->pCursor, &res);
        if( res ){
          v = 1;
        }else{
          sqlite3BtreeKeySize(pC->pCursor, &v);
          v = keyToInt(v);
          if( v==MAX_I64 ){
            pC->useRandomRowid = 1;
          }else{
            v++;
          }
        }
      }

................................................................................
#ifndef SQLITE_OMIT_AUTOINCREMENT
      if( pOp->p2 ){
        Mem *pMem;
        assert( pOp->p2>0 && pOp->p2<p->nMem );  /* P2 is a valid memory cell */
        pMem = &p->aMem[pOp->p2];
        Integerify(pMem);
        assert( (pMem->flags & MEM_Int)!=0 );  /* mem(P2) holds an integer */
        if( pMem->i==MAX_I64 || pC->useRandomRowid ){
          rc = SQLITE_FULL;
          goto abort_due_to_error;
        }
        if( v<pMem->i+1 ){
          v = pMem->i + 1;
        }
        pMem->i = v;
      }
#endif

      if( v<MAX_I64 ){
        pC->nextRowidValid = 1;
        pC->nextRowid = v+1;
      }else{
        pC->nextRowidValid = 0;
      }
    }
    if( pC->useRandomRowid ){

Changes to src/vdbeaux.c.

1483
1484
1485
1486
1487
1488
1489
1490

1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
u32 sqlite3VdbeSerialType(Mem *pMem){
  int flags = pMem->flags;

  if( flags&MEM_Null ){
    return 0;
  }
  if( flags&MEM_Int ){
    /* Figure out whether to use 1, 2, 4 or 8 bytes. */

    i64 i = pMem->i;
    if( i>=-127 && i<=127 ) return 1;
    if( i>=-32767 && i<=32767 ) return 2;
    if( i>=-8388607 && i<=8388607 ) return 3;
    if( i>=-2147483647 && i<=2147483647 ) return 4;
    if( i>=-140737488355328L && i<=140737488355328L ) return 5;
    return 6;
  }
  if( flags&MEM_Real ){
    return 7;
  }
  if( flags&MEM_Str ){
    int n = pMem->n;







|
>





|







1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
u32 sqlite3VdbeSerialType(Mem *pMem){
  int flags = pMem->flags;

  if( flags&MEM_Null ){
    return 0;
  }
  if( flags&MEM_Int ){
    /* Figure out whether to use 1, 2, 4, 6 or 8 bytes. */
#   define MAX_6BYTE ((((i64)0x00010000)<<32)-1)
    i64 i = pMem->i;
    if( i>=-127 && i<=127 ) return 1;
    if( i>=-32767 && i<=32767 ) return 2;
    if( i>=-8388607 && i<=8388607 ) return 3;
    if( i>=-2147483647 && i<=2147483647 ) return 4;
    if( i>=-MAX_6BYTE && i<=MAX_6BYTE ) return 5;
    return 6;
  }
  if( flags&MEM_Real ){
    return 7;
  }
  if( flags&MEM_Str ){
    int n = pMem->n;

Changes to src/where.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.130 2005/01/19 23:24:51 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.
................................................................................

  /* Analyze all of the subexpressions.
  */
  for(i=0; i<pTabList->nSrc; i++){
    createMask(&maskSet, pTabList->a[i].iCursor);
  }
  for(pTerm=aExpr, i=0; i<nExpr; i++, pTerm++){
    TriggerStack *pStack;
    exprAnalyze(pTabList, &maskSet, pTerm);
  }

  /* Figure out what index to use (if any) for each nested loop.
  ** Make pWInfo->a[i].pIdx point to the index to use for the i-th nested
  ** loop where i==0 is the outer loop and i==pTabList->nSrc-1 is the inner
  ** loop. 







|







 







<







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
646
647
648
649
650
651
652

653
654
655
656
657
658
659
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.131 2005/01/20 22:48:48 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.
................................................................................

  /* Analyze all of the subexpressions.
  */
  for(i=0; i<pTabList->nSrc; i++){
    createMask(&maskSet, pTabList->a[i].iCursor);
  }
  for(pTerm=aExpr, i=0; i<nExpr; i++, pTerm++){

    exprAnalyze(pTabList, &maskSet, pTerm);
  }

  /* Figure out what index to use (if any) for each nested loop.
  ** Make pWInfo->a[i].pIdx point to the index to use for the i-th nested
  ** loop where i==0 is the outer loop and i==pTabList->nSrc-1 is the inner
  ** loop. 

Changes to test/capi2.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
593
594
595
596
597
598
599

600
601
602
603
604
605
606
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script testing the callback-free C/C++ API.
#
# $Id: capi2.test,v 1.23 2005/01/12 12:44:04 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Return the text values from the current row pointed at by STMT as a list.
proc get_row_values {STMT} {
................................................................................
} {4}
do_test capi2-7.11a {
  execsql {SELECT count(*) FROM t1}
} {4}

ifcapable {explain} {
  do_test capi2-7.12 {

    set x [stepsql $DB {EXPLAIN SELECT * FROM t1}]
    lindex $x 0
  } {0}
}

# Ticket #261 - make sure we can finalize before the end of a query.
#







|







 







>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script testing the callback-free C/C++ API.
#
# $Id: capi2.test,v 1.24 2005/01/20 22:48:48 drh Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Return the text values from the current row pointed at by STMT as a list.
proc get_row_values {STMT} {
................................................................................
} {4}
do_test capi2-7.11a {
  execsql {SELECT count(*) FROM t1}
} {4}

ifcapable {explain} {
  do_test capi2-7.12 {
btree_breakpoint
    set x [stepsql $DB {EXPLAIN SELECT * FROM t1}]
    lindex $x 0
  } {0}
}

# Ticket #261 - make sure we can finalize before the end of a query.
#

Changes to www/different.tcl.

1
2
3
4
5
6
7
8
..
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
..
59
60
61
62
63
64
65

















66
67
68
69
70
71
72
73
74
75
76
77
78
79
set rcsid {$Id: different.tcl,v 1.1 2005/01/17 03:42:52 drh Exp $}
source common.tcl
header {Distinctive Features Of SQLite}
puts {
<p>
This page highlights some of the characteristics of SQLite that are
unusual and which make SQLite different from many other SQL
database engines.
................................................................................
  SQLite uses no configuration files.
  Nothing needs to be done to tell the system that SQLite is running.
  No actions are required to recover after a system crash or power failure.
  There is nothing to troubleshoot.
  <p>
  SQLite just works.
  <p>
  An SQLite database is an ordinary disk file.  If SQLite can read
  the disk file then it can read anything in the database.  If the disk
  file and its directory are writable, then SQLite can change anything
  in the database.

}

feature serverless {Serverless} {
  Most SQL database engines are implemented as a separate server
  process.  Programs that want to access the database communicate
  with the server using some kind of interprocess communcation
  (typically TCP/IP) to send requests to the server and to receive
................................................................................
  is a single persistent process, it is able control database access with
  more precision, allowing for finer grain locking and better concurrancy.
  <p>
  Most SQL database engines are client/server based.  Of those that are
  serverless, SQLite is the only one that this author knows of that
  allows multiple applications to access the same database at the same time.
}


















feature small {Compact} {
  When optimized for size, the whole SQLite library with everything enabled
  is less than 220KiB in size (as measured on an ix86 using the "size"
  utility from the GNU compiler suite.)  Unneeded features can be disabled
  at compile-time to further reduce the size of the library to under
  180KiB if desired.
  <p>
  Most other SQL database engines are much larger than this.  IBM boasts
  that it's recently released CloudScape database engine is "only" a 2MiB
  jar file - 10 times larger than SQLite even after it is compressed!
  Firefox boasts that it's client-side library is only 350KiB.  That's
  50% larger than SQLite and does not even contain the database engine.
  The Berkeley DB library from Sleepycat is 450KiB and it lacks a schema
|







 







|
|
|
<
<







 







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






|







1
2
3
4
5
6
7
8
..
24
25
26
27
28
29
30
31
32
33


34
35
36
37
38
39
40
..
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
set rcsid {$Id: different.tcl,v 1.2 2005/01/20 22:48:49 drh Exp $}
source common.tcl
header {Distinctive Features Of SQLite}
puts {
<p>
This page highlights some of the characteristics of SQLite that are
unusual and which make SQLite different from many other SQL
database engines.
................................................................................
  SQLite uses no configuration files.
  Nothing needs to be done to tell the system that SQLite is running.
  No actions are required to recover after a system crash or power failure.
  There is nothing to troubleshoot.
  <p>
  SQLite just works.
  <p>
  Other more familiar database engines run great once you get them going.
  But doing the initial installation and configuration can be
  intimidatingly complex.


}

feature serverless {Serverless} {
  Most SQL database engines are implemented as a separate server
  process.  Programs that want to access the database communicate
  with the server using some kind of interprocess communcation
  (typically TCP/IP) to send requests to the server and to receive
................................................................................
  is a single persistent process, it is able control database access with
  more precision, allowing for finer grain locking and better concurrancy.
  <p>
  Most SQL database engines are client/server based.  Of those that are
  serverless, SQLite is the only one that this author knows of that
  allows multiple applications to access the same database at the same time.
}

feature onefile {Single Database File} {
  An SQLite database is a single ordinary disk file that can be located
  anywhere in the directory hierarchy.  If SQLite can read
  the disk file then it can read anything in the database.  If the disk
  file and its directory are writable, then SQLite can change anything
  in the database.   Database files can easily be copied onto a USB
  memory stick or emailed for sharing.
  <p>
  Other SQL database engines tend to store data as a large collection of
  files.  Often these files are in a standard location that only the
  database engine itself can access.  This makes the data more secure,
  but also makes it harder to access.  Some SQL database engines provide
  the option of writing directly to disk and bypassing the filesystem
  all together.  This provides added performance, but at the cost of
  considerable setup and maintenance complexity.
}

feature small {Compact} {
  When optimized for size, the whole SQLite library with everything enabled
  is less than 220KiB in size (as measured on an ix86 using the "size"
  utility from the GNU compiler suite.)  Unneeded features can be disabled
  at compile-time to further reduce the size of the library to under
  170KiB if desired.
  <p>
  Most other SQL database engines are much larger than this.  IBM boasts
  that it's recently released CloudScape database engine is "only" a 2MiB
  jar file - 10 times larger than SQLite even after it is compressed!
  Firefox boasts that it's client-side library is only 350KiB.  That's
  50% larger than SQLite and does not even contain the database engine.
  The Berkeley DB library from Sleepycat is 450KiB and it lacks a schema

Changes to www/whentouse.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
..
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
...
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
...
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
#
# Run this TCL script to generate HTML for the goals.html file.
#
set rcsid {$Id: whentouse.tcl,v 1.3 2005/01/03 01:33:00 drh Exp $}
source common.tcl
header {Appropriate Uses For SQLite}

puts {
<p>
SQLite is different from most other SQL database engines in that its
primary design goal is to be simple:
</p>

<ul>
<li>Simple to administer</li>
<li>Simple to operate</li>
<li>Simple to use in a program</li>
<li>Simple to maintain and customize</li>
</ul>

<p>
Many people like SQLite because it is small and fast.  But those
qualities are just happy accidents.
Users also find that SQLite is very reliable.  Reliability is
................................................................................
<p>
Simplicity in a database engine can be either a strength or a
weakness, depending on what you are trying to do.  In order to
achieve simplicity, SQLite has had to sacrifice other characteristics
that some people find useful, such as high concurrency, fine-grained
access control, a rich set of built-in functions, stored procedures,
esoteric SQL language features, XML and/or Java extensions,
tera- or peta-byte scalability, and so forth.  If you need these
kinds of features and don't mind the added complexity that they
bring, then SQLite is probably not the database for you.
SQLite is not intended to be an enterprise database engine.  It
not designed to compete with Oracle or PostgreSQL.
</p>

<p>
The basic rule of thumb for when it is appropriate to use SQLite is
................................................................................
</ul>

<h2>Situations Where Another RDBMS May Work Better</h2>

<ul>
<li><p><b>Client/Server Applications</b><p>

<p>If you have many client programs access a common database
over a network, you should consider using a client/server database
engine instead of SQLite.  SQLite will work over a network filesystem,
but because of the latency associated with most network filesystems,
performance will not be great.  Also, the file locking logic of
many network filesystems implementation contains bugs (on both Unix
and windows).  If file locking does not work like it should,
it might be possible for two or more client programs to modify the
................................................................................

<li><p><b>High Concurrency</b></p>

<p>
SQLite uses reader/writer locks on the entire database file.  That means
if any process is reading from any part of the database, all other
processes are prevented from writing any other part of the database.
Similarly, if any one process is writing to any part of the database,
all other processes are prevented from reading any other part of the
database.
For many situations, this is not a problem.  Each application
does its database work quickly and moves on, and no lock lasts for more
than a few dozen milliseconds.  But there are some applications that require
more concurrency, and those applications may need to seek a different
solution.



|












|







 







|
|







 







|







 







|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
...
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
...
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
#
# Run this TCL script to generate HTML for the goals.html file.
#
set rcsid {$Id: whentouse.tcl,v 1.4 2005/01/20 22:48:49 drh Exp $}
source common.tcl
header {Appropriate Uses For SQLite}

puts {
<p>
SQLite is different from most other SQL database engines in that its
primary design goal is to be simple:
</p>

<ul>
<li>Simple to administer</li>
<li>Simple to operate</li>
<li>Simple to embed in a larger program</li>
<li>Simple to maintain and customize</li>
</ul>

<p>
Many people like SQLite because it is small and fast.  But those
qualities are just happy accidents.
Users also find that SQLite is very reliable.  Reliability is
................................................................................
<p>
Simplicity in a database engine can be either a strength or a
weakness, depending on what you are trying to do.  In order to
achieve simplicity, SQLite has had to sacrifice other characteristics
that some people find useful, such as high concurrency, fine-grained
access control, a rich set of built-in functions, stored procedures,
esoteric SQL language features, XML and/or Java extensions,
tera- or peta-byte scalability, and so forth.  If you need some of these
latter of features and do not mind the added complexity that they
bring, then SQLite is probably not the database for you.
SQLite is not intended to be an enterprise database engine.  It
not designed to compete with Oracle or PostgreSQL.
</p>

<p>
The basic rule of thumb for when it is appropriate to use SQLite is
................................................................................
</ul>

<h2>Situations Where Another RDBMS May Work Better</h2>

<ul>
<li><p><b>Client/Server Applications</b><p>

<p>If you have many client programs accessing a common database
over a network, you should consider using a client/server database
engine instead of SQLite.  SQLite will work over a network filesystem,
but because of the latency associated with most network filesystems,
performance will not be great.  Also, the file locking logic of
many network filesystems implementation contains bugs (on both Unix
and windows).  If file locking does not work like it should,
it might be possible for two or more client programs to modify the
................................................................................

<li><p><b>High Concurrency</b></p>

<p>
SQLite uses reader/writer locks on the entire database file.  That means
if any process is reading from any part of the database, all other
processes are prevented from writing any other part of the database.
Similarly, if any one process is writing to the database,
all other processes are prevented from reading any other part of the
database.
For many situations, this is not a problem.  Each application
does its database work quickly and moves on, and no lock lasts for more
than a few dozen milliseconds.  But there are some applications that require
more concurrency, and those applications may need to seek a different
solution.