SQLite

**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** when syntax rules are reduced.
**
** $Id: build.c,v 1.3 2000/05/29 23:30:51 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the code to implement 
** the statement.  Prior action routines should have already

** Add a new column to the table currently being constructed.
*/
void sqliteAddColumn(Parse *pParse, Token *pName){
  Table *p;
  char **pz;
  if( (p = pParse->pNewTable)==0 ) return;
  if( (p->nCol & 0x7)==0 ){
    p->azCol = sqliteRealloc( p->azCol, (p->nCol+9)*sizeof(p->azCol[0]));
  }
  if( p->azCol==0 ){
    p->nCol = 0;
    return;
  }
  pz = &p->azCol[p->nCol++];
  *pz = 0;

** an error for the parser to find and return NULL.
*/
static Table *sqliteTableFromToken(Parse *pParse, Token *pTok){
  char *zName = sqliteTableNameFromToken(pTok);
  Table *pTab = sqliteFindTable(pParse->db, zName);
  sqliteFree(zName);
  if( pTab==0 ){
    sqliteSetNString(&pParse->zErrMsg, "no such table: ", 0, 
        pTok->z, pTok->n, 0);
    pParse->nErr++;
  }
  return pTab;
}

/*
** This routine is called to do the work of a DROP TABLE statement.

  if( pTable!=0 ){
    pTab =  sqliteTableFromToken(pParse, pTable);
  }else{
    pTab =  pParse->pNewTable;
  }
  if( pTab==0 || pParse->nErr ) goto exit_create_index;
  if( pTab->readOnly ){
    sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName, 
      " may not have new indices added", 0);
    pParse->nErr++;
    goto exit_create_index;
  }

  /*
  ** Find the name of the index.  Make sure there is not already another
  ** index or table with the same name.

  ** if any field is not found.
  */
  for(i=0; i<pList->nId; i++){
    for(j=0; j<pTab->nCol; j++){
      if( sqliteStrICmp(pList->a[i].zName, pTab->azCol[j])==0 ) break;
    }
    if( j>=pTab->nCol ){
      sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName, 
        " has no field named ", pList->a[i].zName, 0);
      pParse->nErr++;
      sqliteFree(pIndex);
      goto exit_create_index;
    }
    pIndex->aiField[i] = j;
  }

  ** we don't want to recreate it.
  */
  if( pParse->initFlag==0 ){
    static VdbeOp addTable[] = {
      { OP_Open,        0, 0, MASTER_NAME},
      { OP_New,         0, 0, 0},
      { OP_String,      0, 0, "index"},
      { OP_String,      0, 0, 0},  /* 3 */
      { OP_String,      0, 0, 0},  /* 4 */
      { OP_String,      0, 0, 0},  /* 5 */
      { OP_MakeRecord,  4, 0, 0},
      { OP_Put,         0, 0, 0},
      { OP_Close,       0, 0, 0},
    };
    int n;
    Vdbe *v = pParse->pVdbe;
    int lbl1, lbl2;
    int i;

    if( v==0 ){
      v = pParse->pVdbe = sqliteVdbeCreate(pParse->db->pBe);
    }
    if( v==0 ) goto exit_create_index;
    if( pStart && pEnd ){
      int base;
      n = (int)pEnd->z - (int)pStart->z + 1;
      base = sqliteVdbeAddOpList(v, ArraySize(addTable), addTable);
      sqliteVdbeChangeP3(v, base+3, pIndex->zName, 0);
      sqliteVdbeChangeP3(v, base+4, pTab->zName, 0);
      sqliteVdbeChangeP3(v, base+5, pStart->z, n);
    }
    sqliteVdbeAddOp(v, OP_Open, 0, 0, pTab->zName, 0);
    sqliteVdbeAddOp(v, OP_Open, 1, 0, pIndex->zName, 0);
    lbl1 = sqliteVdbeMakeLabel(v);
    lbl2 = sqliteVdbeMakeLabel(v);
    sqliteVdbeAddOp(v, OP_Next, 0, lbl2, 0, lbl1);
    sqliteVdbeAddOp(v, OP_Key, 0, 0, 0, 0);

**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.3 2000/05/29 23:30:51 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

/*
** Allocate new memory and set it to zero.  Return NULL if

  if( p==0 ){
    return sqliteMalloc(n);
  }
  if( n==0 ){
    sqliteFree(p);
    return 0;
  }
  /* printf("realloc 0x%x size: %d bytes\n", (int)p, n); */
  return realloc(p, n);
}

/*
** Create a string from the 2nd and subsequent arguments (up to the
** first NULL argument), store the string in memory obtained from
** sqliteMalloc() and make the pointer indicated by the 1st argument

# Copyright (c) 1999, 2000 D. Richard Hipp
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public
# License as published by the Free Software Foundation; either
# version 2 of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
# 
# You should have received a copy of the GNU General Public
# License along with this library; if not, write to the
# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
# Boston, MA  02111-1307, USA.
#
# Author contact information:
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: crtidx.test,v 1.1 2000/05/29 23:30:51 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 crtidx-1.1 {
  execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
  execsql {CREATE INDEX index1 ON test1(f1)}
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} {index1 test1}
do_test crtidx-1.1b {
  execsql {SELECT name, sql, tbl_name, type FROM sqlite_master 
           WHERE name='index1'}
} {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
do_test crtidx-1.1c {
  db close
  sqlite db testdb
  execsql {SELECT name, sql, tbl_name, type FROM sqlite_master 
           WHERE name='index1'}
} {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
do_test crtidx-1.1d {
  db close
  sqlite db testdb
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} {index1 test1}

# Verify that the index dies with the table
#
do_test crtidx-1.2 {
  execsql {DROP TABLE test1}
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} {}

# Try adding an index to a table that does not exist
#
do_test crtidx-2.1 {
  set v [catch {execsql {CREATE INDEX index1 ON test1(f1)}} msg]
  lappend v $msg
} {1 {no such table: test1}}

# Try adding an index on a field of a table where the table
# exists but the field does not.
#
do_test crtidx-2.1 {
  execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
  set v [catch {execsql {CREATE INDEX index1 ON test1(f4)}} msg]
  lappend v $msg
} {1 {table test1 has no field named f4}}

# Try an index with some fields that match and others that do now.
#
do_test crtidx-2.2 {
  set v [catch {execsql {CREATE INDEX index1 ON test1(f1, f2, f4, f3)}} msg]
  execsql {DROP TABLE test1}
  lappend v $msg
} {1 {table test1 has no field named f4}}

# Try creating a bunch of indices on the same table
#
set r {}
for {set i 1} {$i<100} {incr i} {
  lappend r index$i
}
do_test crtidx-3.1 {
  execsql {CREATE TABLE test1(f1 int, f2 int, f3 int, f4 int, f5 int)}
  for {set i 1} {$i<100} {incr i} {
    set sql "CREATE INDEX index$i ON test1(f[expr {($i%5)+1}])"
    execsql $sql
  }
  execsql {SELECT name FROM sqlite_master 
           WHERE type='index' AND tbl_name='test1'
           ORDER BY name}
} $r

# Add a single entry to the table.  Verify that files are created
# for every index.
#
set r {}
for {set i 1} {$i<100} {incr i} {
  lappend r testdb/index$i.tbl
}
do_test crtidx-3.2 {
  execsql {INSERT INTO test1 VALUES(1,2,3,4,5)}
  lsort -dictionary [glob testdb/index*.tbl]
} $r

# Verify that all the indices go away when we drop the table.
#
do_test crtidx-3.3 {
  execsql {DROP TABLE test1}
  execsql {SELECT name FROM sqlite_master 
           WHERE type='index' AND tbl_name='test1'
           ORDER BY name}
} {}
do_test crtidx-3.4 {
  lsort -dictionary [glob -nocomplain testdb/index*.tbl]
} {}

# Create a table and insert values into that table.  Then create
# an index on that table.  Verify that we can select values
# from the table correctly using the index.
#
do_test crtidx-4.1 {
  execsql {CREATE TABLE test1(cnt int, power int)}
  for {set i 1} {$i<20} {incr i} {
    execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
  }
  execsql {CREATE INDEX index1 ON test1(cnt)}
  execsql {CREATE INDEX index2 ON test1(cnt)}
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} {index1 index2 test1}
do_test crtidx-4.2 {
  execsql {SELECT cnt FROM test1 WHERE power=4}
} {2}
do_test crtidx-4.3 {
  execsql {SELECT cnt FROM test1 WHERE power=1024}
} {10}
do_test crtidx-4.4 {
  execsql {SELECT power FROM test1 WHERE cnt=6}
} {64}
do_test crtidx-4.5 {
  execsql {DROP TABLE test1}
} {}

# Do not allow indices to be added to sqlite_master
#
do_test crtidx-5.1 {
  set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]
  lappend v $msg
} {1 {table sqlite_master may not have new indices added}}
do_test crtidx-5.2 {
  execsql {SELECT name FROM sqlite_master}
} {}

# Do not allow indices with duplicate names to be added
#
do_test crtidx-6.1 {
  execsql {CREATE TABLE test1(f1 int, f2 int)}
  execsql {CREATE TABLE test2(g1 real, g2 real)}
  execsql {CREATE INDEX index1 ON test1(f1)}
  set v [catch {execsql {CREATE INDEX index1 ON test2(g1)}} msg]
  lappend v $msg
} {1 {index "index1" already exists}}
do_test crtidx-6.1b {
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} {index1 test1 test2}
do_test crtidx-6.2 {
  set v [catch {execsql {CREATE INDEX test1 ON test2(g1)}} msg]
  lappend v $msg
} {1 {there is already a table named "test1"}}
do_test crtidx-6.2b {
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} {index1 test1 test2}
do_test crtidx-6.3 {
  execsql {DROP TABLE test1}
  execsql {DROP TABLE test2}
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} {}

# Create a primary key
#
do_test crtidx-7.1 {
  execsql {CREATE TABLE test1(f1 int, f2 int primary key)}
  for {set i 1} {$i<20} {incr i} {
    execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
  }
  lsort -dictionary [glob testdb/test1*.tbl]
} {testdb/test1.tbl testdb/test1__primary_key.tbl}
do_test crtidx-7.2 {
  execsql {SELECT f1 FROM test1 WHERE f2=65536}
} {16}
do_test crtidx-7.3 {
  set code [execsql {EXPLAIN SELECT f1 FROM test1 WHERE f2=65536}]
  expr {[lsearch $code test1__primary_key]>0}
} {1}

finish_test

#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE TABLE statement.
#
# $Id: crttbl.test,v 1.2 2000/05/29 23:30:51 drh Exp $

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

# Create a basic table and verify it is added to sqlite_master
#
do_test crttbl-1.1 {

#
do_test crttbl-1.6 {
  db close
  sqlite db testdb
  execsql {SELECT name FROM sqlite_master}
} {}


# Verify that we cannot make two tables with the same name
#
do_test crttbl-2.1 {
  execsql {CREATE TABLE test2(one text)}
  set v [catch {execsql {CREATE TABLE test2(two text)}} msg]
  lappend v $msg
} {1 {table "test2" already exists}}
do_test crttbl-2.1b {
  set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
  lappend v $msg
} {1 {table "sqlite_master" already exists}}
do_test crttbl-2.1c {
  db close
  sqlite db testdb
  set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
  lappend v $msg
} {1 {table "sqlite_master" already exists}}
do_test crttbl-2.1d {
  execsql {DROP TABLE test2; SELECT name FROM sqlite_master}
} {}

# Verify that we cannot make a table with the same name as an index
#
do_test crttbl-2.2 {
  execsql {CREATE TABLE test2(one text); CREATE INDEX test3 ON test2(one)}
  set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
  lappend v $msg
} {1 {there is already an index named "test3"}}
do_test crttbl-2.2b {
  db close
  sqlite db testdb
  set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
  lappend v $msg
} {1 {there is already an index named "test3"}}
do_test crttbl-2.2c {
  execsql {DROP INDEX test3}
  set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
  lappend v $msg
} {0 {}}
do_test crttbl-2.2d {
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} {test2 test3}
do_test crttbl-2.2e {
  execsql {DROP TABLE test2; DROP TABLE test3}
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} {}

# Create a table with many field names
#
set big_table \
{CREATE TABLE big(
  f1 varchar(20),
  f2 char(10),
  f3 varchar(30),
  f4 text,
  f5 text,
  f6 text,
  f7 text,
  f8 text,
  f9 text,
  f10 text,
  f11 text,
  f12 text,
  f13 text,
  f14 text,
  f15 text,
  f16 text,
  f17 text,
  f18 text,
  f19 text,
  f20 text
)}
do_test crttbl-3.1 {
  execsql $big_table
  execsql {SELECT sql FROM sqlite_master}
} \{$big_table\}
do_test crttbl-3.2 {
  set v [catch {execsql {CREATE TABLE BIG(xyz foo)}} msg]
  lappend v $msg
} {1 {table "BIG" already exists}}
do_test crttbl-3.3 {
  set v [catch {execsql {CREATE TABLE biG(xyz foo)}} msg]
  lappend v $msg
} {1 {table "biG" already exists}}
do_test crttbl-3.4 {
  set v [catch {execsql {CREATE TABLE bIg(xyz foo)}} msg]
  lappend v $msg
} {1 {table "bIg" already exists}}
do_test crttbl-3.5 {
  db close
  sqlite db testdb
  set v [catch {execsql {CREATE TABLE Big(xyz foo)}} msg]
  lappend v $msg
} {1 {table "Big" already exists}}
do_test crttbl-3.6 {
  execsql {DROP TABLE big}
  execsql {SELECT name FROM sqlite_master}
} {}

# Try creating large numbers of tables
#
set r {}
for {set i 1} {$i<=100} {incr i} {
  lappend r test$i
}
do_test crttbl-4.1 {
  for {set i 1} {$i<=100} {incr i} {
    set sql "CREATE TABLE test$i ("
    for {set k 1} {$k<$i} {incr k} {
      append sql "field$k text,"
    }
    append sql "last_field text)"
    execsql $sql
  }
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} $r
do_test crttbl-4.1b {
  db close
  sqlite db testdb
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} $r

# Drop the even number tables
#
set r {}
for {set i 1} {$i<=100} {incr i 2} {
  lappend r test$i
}
do_test crttbl-4.2 {
  for {set i 2} {$i<=100} {incr i 2} {
    set sql "DROP TABLE TEST$i"
    execsql $sql
  }
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} $r

# Drop the odd number tables
#
do_test crttbl-4.3 {
  for {set i 1} {$i<=100} {incr i 2} {
    set sql "DROP TABLE test$i"
    execsql $sql
  }
  execsql {SELECT name FROM sqlite_master ORDER BY name}
} {}

finish_test