SQLite

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

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

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

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

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;

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

# 2003 January 29
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script 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.
#

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

#
# 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
a consequence of simplicity.  With less complication, there is
less to go wrong.  So, yes, SQLite is small, fast, and reliable,
but first and foremost, SQLite strives to be simple.
</p>

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