/ Check-in [fdf4b31a]
Login

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

Overview
Comment::-) (CVS 7)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:fdf4b31a18fcbbcd358bf92c91fccbf94a79bc26
User & Date: drh 2000-05-29 23:30:51
Context
2000-05-29
23:48
:-) (CVS 8) check-in: e34143c2 user: drh tags: trunk
23:30
:-) (CVS 7) check-in: fdf4b31a user: drh tags: trunk
20:41
:-) (CVS 6) check-in: 1517f852 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
...
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
...
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
...
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
...
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
...
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
...
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
**   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.2 2000/05/29 17:44:25 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+8)*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, "\"", 1, 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 being indexed 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},  /* 2 */
      { OP_String,      0, 0, 0},  /* 3 */
      { OP_String,      0, 0, 0},  /* 4 */
      { OP_MakeRecord,  4, 0, 0},
      { OP_Put,         0, 0, 0},
      { OP_Close,       0, 0, 0},
    };
    int n;
    Vdbe *v = pParse->pVdbe;
    int lbl1, lbl2;
................................................................................
      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+2, pIndex->zName, 0);
      sqliteVdbeChangeP3(v, base+3, pTab->zName, 0);
      sqliteVdbeChangeP3(v, base+4, 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);







|







 







|







 







|
|







 







|
|







 







|
|







 







|
|
|







 







|
|
|







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
...
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
...
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
...
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
...
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
...
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
...
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
**   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;
................................................................................
      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);

Changes to src/util.c.

22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
..
63
64
65
66
67
68
69

70
71
72
73
74
75
76
**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.2 2000/05/29 17:44:25 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;
  }

  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







|







 







>







22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
..
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
**
*************************************************************************
** 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

Added test/crtidx.test.

























































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
# 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

Changes to test/crttbl.test.

19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
..
85
86
87
88
89
90
91



















































































































































92
#   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.1 2000/05/29 20:41: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}
} {}




















































































































































finish_test







|







 







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

19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
..
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
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
#   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