/ Check-in [78a50971]
Login

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

Overview
Comment:Add support for CREATE TABLE AS. (CVS 377)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:78a50971e9adc8739e7888201c79465a40e1a152
User & Date: drh 2002-02-18 18:30:32
Context
2002-02-18
22:49
Bug fix: CREATE TABLE, followed by DROP TABLE within the same transaction is working now. (CVS 378) check-in: 553579f9 user: drh tags: trunk
18:30
Add support for CREATE TABLE AS. (CVS 377) check-in: 78a50971 user: drh tags: trunk
13:56
Allow general expressions in the VALUES clause of an INSERT statement. (CVS 376) check-in: ec1f3fae user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
645
646
647
648
649
650
651







































































652
653
654
655
656
657
658
...
660
661
662
663
664
665
666





667
668
669
670
671
672
673
674
675
676
677
678
679
...
683
684
685
686
687
688
689













690
691
692
693
694
695
696
...
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
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.74 2002/02/03 17:37:36 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 
................................................................................
*/
static void changeCookie(sqlite *db){
  if( db->next_cookie==db->schema_cookie ){
    db->next_cookie = db->schema_cookie + sqliteRandomByte() + 1;
    db->flags |= SQLITE_InternChanges;
  }
}








































































/*
** This routine is called to report the final ")" that terminates
** a CREATE TABLE statement.
**
** The table structure that other action routines have been building
** is added to the internal hash tables, assuming no errors have
................................................................................
**
** An entry for the table is made in the master table on disk,
** unless this is a temporary table or initFlag==1.  When initFlag==1,
** it means we are reading the sqlite_master table because we just
** connected to the database or because the sqlite_master table has
** recently changes, so the entry for this table already exists in
** the sqlite_master table.  We do not want to create it again.





*/
void sqliteEndTable(Parse *pParse, Token *pEnd){
  Table *p;
  sqlite *db = pParse->db;

  if( pEnd==0 || pParse->nErr || sqlite_malloc_failed ) return;
  p = pParse->pNewTable;
  if( p==0 ) return;

  /* Add the table to the in-memory representation of the database.
  */
  assert( pParse->nameClash==0 || pParse->initFlag==1 );
  if( pParse->explain==0 && pParse->nameClash==0 ){
................................................................................
      assert( p==pOld );  /* Malloc must have failed inside HashInsert() */
      return;
    }
    pParse->pNewTable = 0;
    db->nTable++;
    db->flags |= SQLITE_InternChanges;
  }














  /* If the initFlag is 1 it means we are reading the SQL off the
  ** "sqlite_master" table on the disk.  So do not write to the disk
  ** again.  Extract the root page number for the table from the 
  ** pParse->newTnum field.  (The page number should have been put
  ** there by the sqliteOpenCb routine.)
  */
................................................................................
  */
  if( !pParse->initFlag ){
    int n, addr;
    Vdbe *v;

    v = sqliteGetVdbe(pParse);
    if( v==0 ) return;
    n = Addr(pEnd->z) - Addr(pParse->sFirstToken.z) + 1;


    if( !p->isTemp ){
      sqliteVdbeAddOp(v, OP_NewRecno, 0, 0);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeChangeP3(v, -1, "table", P3_STATIC);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeChangeP3(v, -1, p->zName, P3_STATIC);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeChangeP3(v, -1, p->zName, P3_STATIC);
    }
    addr = sqliteVdbeAddOp(v, OP_CreateTable, 0, p->isTemp);
    sqliteVdbeChangeP3(v, addr, (char *)&p->tnum, P3_POINTER);
    p->tnum = 0;
    if( !p->isTemp ){
      addr = sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeChangeP3(v, addr, pParse->sFirstToken.z, n);





      sqliteVdbeAddOp(v, OP_MakeRecord, 5, 0);
      sqliteVdbeAddOp(v, OP_PutIntKey, 0, 0);
      changeCookie(db);
      sqliteVdbeAddOp(v, OP_SetCookie, db->next_cookie, 0);
      sqliteVdbeAddOp(v, OP_Close, 0, 0);
    }






    sqliteEndWriteOperation(pParse);
  }
}

/*
** Given a token, look up a table with that name.  If not found, leave
** an error for the parser to find and return NULL.







|







 







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







 







>
>
>
>
>

|



|







 







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







 







|
>
>








|
|
|
|
|
|
|
>
>
>
>
>






>
>
>
>
>
>







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
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
...
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
...
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
...
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.75 2002/02/18 18:30:32 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 
................................................................................
*/
static void changeCookie(sqlite *db){
  if( db->next_cookie==db->schema_cookie ){
    db->next_cookie = db->schema_cookie + sqliteRandomByte() + 1;
    db->flags |= SQLITE_InternChanges;
  }
}

/*
** Measure the number of characters needed to output the given
** identifier.  The number returned includes any quotes used
** but does not include the null terminator.
*/
static int identLength(const char *z){
  int n;
  for(n=2; *z; n++, z++){
    if( *z=='\'' ){ n++; }
  }
  return n;
}

/*
** Write an identifier onto the end of the given string.  Add
** quote characters as needed.
*/
static void identPut(char *z, int *pIdx, char *zIdent){
  int i, j;
  i = *pIdx;
  z[i++] = '\'';
  for(j=0; zIdent[j]; j++){
    z[i++] = zIdent[j];
    if( zIdent[j]=='\'' ) z[i++] = '\'';
  }
  z[i++] = '\'';
  z[i] = 0;
  *pIdx = i;
}

/*
** Generate a CREATE TABLE statement appropriate for the given
** table.  Memory to hold the text of the statement is obtained
** from sqliteMalloc() and must be freed by the calling function.
*/
static char *createTableStmt(Table *p){
  int i, k, n;
  char *zStmt;
  char *zSep, *zSep2, *zEnd;
  n = 0;
  for(i=0; i<p->nCol; i++){
    n += identLength(p->aCol[i].zName);
  }
  n += identLength(p->zName);
  if( n<40 ){
    zSep = "";
    zSep2 = ",";
    zEnd = ")";
  }else{
    zSep = "\n  ";
    zSep2 = ",\n  ";
    zEnd = "\n)";
  }
  n += 25 + 6*p->nCol;
  zStmt = sqliteMalloc( n );
  if( zStmt==0 ) return 0;
  assert( !p->isTemp );
  strcpy(zStmt, "CREATE TABLE ");
  k = strlen(zStmt);
  identPut(zStmt, &k, p->zName);
  zStmt[k++] = '(';
  for(i=0; i<p->nCol; i++){
    strcpy(&zStmt[k], zSep);
    k += strlen(&zStmt[k]);
    zSep = zSep2;
    identPut(zStmt, &k, p->aCol[i].zName);
  }
  strcpy(&zStmt[k], zEnd);
  return zStmt;
}

/*
** This routine is called to report the final ")" that terminates
** a CREATE TABLE statement.
**
** The table structure that other action routines have been building
** is added to the internal hash tables, assuming no errors have
................................................................................
**
** An entry for the table is made in the master table on disk,
** unless this is a temporary table or initFlag==1.  When initFlag==1,
** it means we are reading the sqlite_master table because we just
** connected to the database or because the sqlite_master table has
** recently changes, so the entry for this table already exists in
** the sqlite_master table.  We do not want to create it again.
**
** If the pSelect argument is not NULL, it means that this routine
** was called to create a table generated from a 
** "CREATE TABLE ... AS SELECT ..." statement.  The column names of
** the new table will match the result set of the SELECT.
*/
void sqliteEndTable(Parse *pParse, Token *pEnd, Select *pSelect){
  Table *p;
  sqlite *db = pParse->db;

  if( (pEnd==0 && pSelect==0) || pParse->nErr || sqlite_malloc_failed ) return;
  p = pParse->pNewTable;
  if( p==0 ) return;

  /* Add the table to the in-memory representation of the database.
  */
  assert( pParse->nameClash==0 || pParse->initFlag==1 );
  if( pParse->explain==0 && pParse->nameClash==0 ){
................................................................................
      assert( p==pOld );  /* Malloc must have failed inside HashInsert() */
      return;
    }
    pParse->pNewTable = 0;
    db->nTable++;
    db->flags |= SQLITE_InternChanges;
  }

  /* If the table is generated from a SELECT, then construct the
  ** list of columns and the text of the table.
  */
  if( pSelect ){
    Table *pSelTab = sqliteResultSetOfSelect(pParse, 0, pSelect);
    assert( p->aCol==0 );
    p->nCol = pSelTab->nCol;
    p->aCol = pSelTab->aCol;
    pSelTab->nCol = 0;
    pSelTab->aCol = 0;
    sqliteDeleteTable(0, pSelTab);
  }

  /* If the initFlag is 1 it means we are reading the SQL off the
  ** "sqlite_master" table on the disk.  So do not write to the disk
  ** again.  Extract the root page number for the table from the 
  ** pParse->newTnum field.  (The page number should have been put
  ** there by the sqliteOpenCb routine.)
  */
................................................................................
  */
  if( !pParse->initFlag ){
    int n, addr;
    Vdbe *v;

    v = sqliteGetVdbe(pParse);
    if( v==0 ) return;
    addr = sqliteVdbeAddOp(v, OP_CreateTable, 0, p->isTemp);
    sqliteVdbeChangeP3(v, addr, (char *)&p->tnum, P3_POINTER);
    p->tnum = 0;
    if( !p->isTemp ){
      sqliteVdbeAddOp(v, OP_NewRecno, 0, 0);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeChangeP3(v, -1, "table", P3_STATIC);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeChangeP3(v, -1, p->zName, P3_STATIC);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeChangeP3(v, -1, p->zName, P3_STATIC);
      sqliteVdbeAddOp(v, OP_Dup, 4, 0);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      if( pSelect ){
        char *z = createTableStmt(p);
        n = z ? strlen(z) : 0;
        sqliteVdbeChangeP3(v, -1, z, n);
        sqliteFree(z);
      }else{
        assert( pEnd!=0 );
        n = Addr(pEnd->z) - Addr(pParse->sFirstToken.z) + 1;
        sqliteVdbeChangeP3(v, -1, pParse->sFirstToken.z, n);
      }
      sqliteVdbeAddOp(v, OP_MakeRecord, 5, 0);
      sqliteVdbeAddOp(v, OP_PutIntKey, 0, 0);
      changeCookie(db);
      sqliteVdbeAddOp(v, OP_SetCookie, db->next_cookie, 0);
      sqliteVdbeAddOp(v, OP_Close, 0, 0);
    }
    if( pSelect ){
      int op = p->isTemp ? OP_OpenWrAux : OP_OpenWrite;
      sqliteVdbeAddOp(v, op, 1, 0);
      pParse->nTab = 2;
      sqliteSelect(pParse, pSelect, SRT_Table, 1);
    }
    sqliteEndWriteOperation(pParse);
  }
}

/*
** Given a token, look up a table with that name.  If not found, leave
** an error for the parser to find and return NULL.

Changes to src/main.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
390
391
392
393
394
395
396







397
398
399
400
401
402
403
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.58 2002/02/02 15:01:16 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"

/*
** This is the callback routine for the code that initializes the
** database.  See sqliteInit() below for additional information.
................................................................................
        break;
      }
      case ' ':
      case '\t':
      case '\n':
      case '\f': {
        break;







      }
      case '\'': {
        isComplete = 0;
        zSql++;
        while( *zSql && *zSql!='\'' ){ zSql++; }
        if( *zSql==0 ) return 0;
        break;







|







 







>
>
>
>
>
>
>







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.59 2002/02/18 18:30:33 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"

/*
** This is the callback routine for the code that initializes the
** database.  See sqliteInit() below for additional information.
................................................................................
        break;
      }
      case ' ':
      case '\t':
      case '\n':
      case '\f': {
        break;
      }
      case '[': {
        isComplete = 0;
        zSql++;
        while( *zSql && *zSql!=']' ){ zSql++; }
        if( *zSql==0 ) return 0;
        break;
      }
      case '\'': {
        isComplete = 0;
        zSql++;
        while( *zSql && *zSql!='\'' ){ zSql++; }
        if( *zSql==0 ) return 0;
        break;

Changes to src/parse.y.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
64
65
66
67
68
69
70
71
72

73
74
75
76
77





78
79
80
81
82
83
84
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.50 2002/02/18 13:56:37 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
cmd ::= COMMIT trans_opt.      {sqliteCommitTransaction(pParse);}
cmd ::= END trans_opt.         {sqliteCommitTransaction(pParse);}
cmd ::= ROLLBACK trans_opt.    {sqliteRollbackTransaction(pParse);}

///////////////////// The CREATE TABLE statement ////////////////////////////
//
cmd ::= create_table create_table_args.
create_table ::= CREATE(X) temp(T) TABLE ids(Y).
                                           {sqliteStartTable(pParse,&X,&Y,T);}

%type temp {int}
temp(A) ::= TEMP.  {A = 1;}
temp(A) ::= .      {A = 0;}
create_table_args ::= LP columnlist conslist_opt RP(X).
                                           {sqliteEndTable(pParse,&X);}





columnlist ::= columnlist COMMA column.
columnlist ::= column.

// About the only information used for a column is the name of the
// column.  The type is always just "text".  But the code will accept
// an elaborate typename.  Perhaps someday we'll do something with it.
//







|







 







|
|
>



|
|
>
>
>
>
>







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
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
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.51 2002/02/18 18:30:33 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
cmd ::= COMMIT trans_opt.      {sqliteCommitTransaction(pParse);}
cmd ::= END trans_opt.         {sqliteCommitTransaction(pParse);}
cmd ::= ROLLBACK trans_opt.    {sqliteRollbackTransaction(pParse);}

///////////////////// The CREATE TABLE statement ////////////////////////////
//
cmd ::= create_table create_table_args.
create_table ::= CREATE(X) temp(T) TABLE ids(Y). {
   sqliteStartTable(pParse,&X,&Y,T);
}
%type temp {int}
temp(A) ::= TEMP.  {A = 1;}
temp(A) ::= .      {A = 0;}
create_table_args ::= LP columnlist conslist_opt RP(X). {
  sqliteEndTable(pParse,&X,0);
}
create_table_args ::= AS select(S). {
  sqliteEndTable(pParse,0,S);
  sqliteSelectDelete(S);
}
columnlist ::= columnlist COMMA column.
columnlist ::= column.

// About the only information used for a column is the name of the
// column.  The type is always just "text".  But the code will accept
// an elaborate typename.  Perhaps someday we'll do something with it.
//

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
562
563
564
565
566
567
568

569
570
571
572
573
574
575
576
577
578
579
580
581
582
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.86 2002/02/18 01:17:00 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
Expr *sqliteExprFunction(ExprList*, Token*);
void sqliteExprDelete(Expr*);
ExprList *sqliteExprListAppend(ExprList*,Expr*,Token*);
void sqliteExprListDelete(ExprList*);
void sqlitePragma(Parse*,Token*,Token*,int);
void sqliteCommitInternalChanges(sqlite*);
void sqliteRollbackInternalChanges(sqlite*);

void sqliteStartTable(Parse*,Token*,Token*,int);
void sqliteAddColumn(Parse*,Token*);
void sqliteAddNotNull(Parse*, int);
void sqliteAddPrimaryKey(Parse*, IdList*, int);
void sqliteAddColumnType(Parse*,Token*,Token*);
void sqliteAddDefaultValue(Parse*,Token*,int);
void sqliteEndTable(Parse*,Token*);
void sqliteDropTable(Parse*, Token*);
void sqliteDeleteTable(sqlite*, Table*);
void sqliteInsert(Parse*, Token*, ExprList*, Select*, IdList*, int);
IdList *sqliteIdListAppend(IdList*, Token*);
void sqliteIdListAddAlias(IdList*, Token*);
void sqliteIdListDelete(IdList*);
void sqliteCreateIndex(Parse*, Token*, Token*, IdList*, int, Token*, Token*);







|







 







>






|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.87 2002/02/18 18:30:33 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
Expr *sqliteExprFunction(ExprList*, Token*);
void sqliteExprDelete(Expr*);
ExprList *sqliteExprListAppend(ExprList*,Expr*,Token*);
void sqliteExprListDelete(ExprList*);
void sqlitePragma(Parse*,Token*,Token*,int);
void sqliteCommitInternalChanges(sqlite*);
void sqliteRollbackInternalChanges(sqlite*);
Table *sqliteResultSetOfSelect(Parse*,char*,Select*);
void sqliteStartTable(Parse*,Token*,Token*,int);
void sqliteAddColumn(Parse*,Token*);
void sqliteAddNotNull(Parse*, int);
void sqliteAddPrimaryKey(Parse*, IdList*, int);
void sqliteAddColumnType(Parse*,Token*,Token*);
void sqliteAddDefaultValue(Parse*,Token*,int);
void sqliteEndTable(Parse*,Token*,Select*);
void sqliteDropTable(Parse*, Token*);
void sqliteDeleteTable(sqlite*, Table*);
void sqliteInsert(Parse*, Token*, ExprList*, Select*, IdList*, int);
IdList *sqliteIdListAppend(IdList*, Token*);
void sqliteIdListAddAlias(IdList*, Token*);
void sqliteIdListDelete(IdList*);
void sqliteCreateIndex(Parse*, Token*, Token*, IdList*, int, Token*, Token*);

Changes to test/table.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
297
298
299
300
301
302
303
304













































305
#    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 TABLE statement.
#
# $Id: table.test,v 1.13 2001/09/27 15:11:55 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 table-1.1 {
................................................................................
  }
} {a b 9 0 xyz hi y'all}
do_test table-7.3 {
  execsql2 {
    SELECT * FROM weird;
  }
} {desc a asc b explain 9 vacuum 0 delimiters xyz begin hi end y'all}














































finish_test







|







 








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

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
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
#    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 TABLE statement.
#
# $Id: table.test,v 1.14 2002/02/18 18:30:33 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 table-1.1 {
................................................................................
  }
} {a b 9 0 xyz hi y'all}
do_test table-7.3 {
  execsql2 {
    SELECT * FROM weird;
  }
} {desc a asc b explain 9 vacuum 0 delimiters xyz begin hi end y'all}

# Try out the CREATE TABLE AS syntax
#
do_test table-8.1 {
  execsql2 {
    CREATE TABLE t2 AS SELECT * FROM weird;
    SELECT * FROM t2;
  }
} {desc a asc b explain 9 vacuum 0 delimiters xyz begin hi end y'all}
do_test table-8.2 {
  execsql {
    CREATE TABLE 't3''xyz'(a,b,c);
    INSERT INTO [t3'xyz] VALUES(1,2,3);
    SELECT * FROM [t3'xyz];
  }
} {1 2 3}
do_test table-8.3 {
  execsql2 {
    CREATE TABLE [t4'abc] AS SELECT count(*), min(a), max(b+c) FROM [t3'xyz];
    SELECT * FROM [t4'abc];
  }
} {count(*) 1 min(a) 1 max(b+c) 5}
do_test table-8.4 {
  execsql2 {
    CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3'xyz];
    SELECT * FROM t5;
  }
} {y'all 1}
do_test table-8.5 {
  db close
  sqlite db test.db
  execsql2 {
    SELECT * FROM [t4'abc];
  }
} {count(*) 1 min(a) 1 max(b+c) 5}
do_test table-8.6 {
  execsql2 {
    SELECT * FROM t2;
  }
} {desc a asc b explain 9 vacuum 0 delimiters xyz begin hi end y'all}
do_test table-8.7 {
  catchsql {
    SELECT * FROM t5;
  }
} {1 {no such table: t5}}

finish_test

Changes to www/changes.tcl.

21
22
23
24
25
26
27

28
29
30
31
32
33
34
<li>Allow identifiers to be quoted in square brackets, for compatibility
    with MS-Access.</li>
<li>Added support for sub-queries in the FROM clause of a SELECT.</li>
<li>More efficient implementation of sqliteFileExists() under Windows.
    (by Joel Luscy)</li>
<li>The VALUES clause of an INSERT can now contain expressions, including
    scalar SELECT clauses.</li>

}

chng {2002 Feb 14 (2.3.2)} {
<li>Bug fix: There was an incorrect assert() in pager.c.  The real code was
    all correct (as far as is known) so everything should work OK if you
    compile with -DNDEBUG=1.  But without disability the assertions, there
    could be a fault.</li>







>







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<li>Allow identifiers to be quoted in square brackets, for compatibility
    with MS-Access.</li>
<li>Added support for sub-queries in the FROM clause of a SELECT.</li>
<li>More efficient implementation of sqliteFileExists() under Windows.
    (by Joel Luscy)</li>
<li>The VALUES clause of an INSERT can now contain expressions, including
    scalar SELECT clauses.</li>
<li>Added support for CREATE TABLE AS SELECT</li>
}

chng {2002 Feb 14 (2.3.2)} {
<li>Bug fix: There was an incorrect assert() in pager.c.  The real code was
    all correct (as far as is known) so everything should work OK if you
    compile with -DNDEBUG=1.  But without disability the assertions, there
    could be a fault.</li>

Changes to www/index.tcl.

1
2
3
4
5
6
7
8
9
10
11
..
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.53 2002/02/03 19:06:04 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] UTC<br>"
................................................................................
puts {<h2>Features</h2>

<p><ul>
<li>Implements a large subset of SQL92.</li>
<li>A complete database (with multiple tables and indices) is
    stored in a single disk file.</li>
<li>Atomic commit and rollback protect data integrity.</li>
<li>Small memory footprint: about 14000 lines of C code.</li>
<li><a href="speed.html">Four times faster</a> than PostgreSQL.
    Twice as fast as SQLite 1.0.</li>
<li>Very simple 
<a href="c_interface.html">C/C++ interface</a> requires the use of only
three functions and one opaque structure.</li>
<li><a href="tclsqlite.html">TCL bindings</a> included.</li>
<li>A TCL-based test suite provides near 100% code coverage.</li>



|







 







|







1
2
3
4
5
6
7
8
9
10
11
..
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.54 2002/02/18 18:30:33 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] UTC<br>"
................................................................................
puts {<h2>Features</h2>

<p><ul>
<li>Implements a large subset of SQL92.</li>
<li>A complete database (with multiple tables and indices) is
    stored in a single disk file.</li>
<li>Atomic commit and rollback protect data integrity.</li>
<li>Small memory footprint: less than 20K lines of C code.</li>
<li><a href="speed.html">Four times faster</a> than PostgreSQL.
    Twice as fast as SQLite 1.0.</li>
<li>Very simple 
<a href="c_interface.html">C/C++ interface</a> requires the use of only
three functions and one opaque structure.</li>
<li><a href="tclsqlite.html">TCL bindings</a> included.</li>
<li>A TCL-based test suite provides near 100% code coverage.</li>

Changes to www/lang.tcl.

1
2
3
4
5
6
7
8
9
10
11
...
241
242
243
244
245
246
247


248
249
250
251
252
253
254
255
256
...
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
...
321
322
323
324
325
326
327




328
329
330
331
332




333
334
335
336
337
338
339
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.23 2002/02/18 03:21:47 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
Section {CREATE TABLE} {createtable}

Syntax {sql-command} {
CREATE [TEMP | TEMPORARY] TABLE <table-name> (
  <column-def> [, <column-def>]*
  [, <constraint>]*
)


} {column-def} {
<name> <type> [<column-constraint>]*
} {type} {
<typename> |
<typename> ( <number> ) |
<typename> ( <number> , <number> )
} {column-constraint} {
NOT NULL [ <conflict-clause> ] |
PRIMARY KEY [<sort-order>] [ <conflict-clause> ] |
................................................................................
followed by the name of a new table and a parenthesized list of column
definitions and constraints.  The table name can be either an identifier
or a string.  The only reserved table name is "<b>sqlite_master</b>" which
is the name of the table that records the database schema.</p>

<p>Each column definition is the name of the column followed by the
datatype for that column, then one or more optional column constraints.
The datatype for the column is ignored.  All information
is stored as null-terminated strings.
The UNIQUE constraint causes an index to be created on the specified
columns.  This index must contain unique keys.
The DEFAULT constraint
specifies a default value to use when doing an INSERT.
</p>

<p>Specifying a PRIMARY KEY normally just creates a UNIQUE index
................................................................................

<p>There are no arbitrary limits on the number
of columns or on the number of constraints in a table.
The total amount of data in a single row is limited to about
1 megabytes.  (This limit can be increased to 16MB by changing
a single #define in the source code and recompiling.)</p>





<p>The exact text
of each CREATE TABLE statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE TABLE statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.</p>




}

Section DELETE delete

Syntax {sql-statement} {
DELETE FROM <table-name> [WHERE <expression>]
}



|







 







>
>

|







 







|
|







 







>
>
>
>




|
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
...
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
...
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
...
323
324
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
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.24 2002/02/18 18:30:33 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
Section {CREATE TABLE} {createtable}

Syntax {sql-command} {
CREATE [TEMP | TEMPORARY] TABLE <table-name> (
  <column-def> [, <column-def>]*
  [, <constraint>]*
)
} {sql-command} {
CREATE [TEMP | TEMPORARY] TABLE <table-name> AS <select-statement>
} {column-def} {
<name> [<type>] [<column-constraint>]*
} {type} {
<typename> |
<typename> ( <number> ) |
<typename> ( <number> , <number> )
} {column-constraint} {
NOT NULL [ <conflict-clause> ] |
PRIMARY KEY [<sort-order>] [ <conflict-clause> ] |
................................................................................
followed by the name of a new table and a parenthesized list of column
definitions and constraints.  The table name can be either an identifier
or a string.  The only reserved table name is "<b>sqlite_master</b>" which
is the name of the table that records the database schema.</p>

<p>Each column definition is the name of the column followed by the
datatype for that column, then one or more optional column constraints.
The datatype for the column is (usually) ignored and may be omitted.
All information is stored as null-terminated strings.
The UNIQUE constraint causes an index to be created on the specified
columns.  This index must contain unique keys.
The DEFAULT constraint
specifies a default value to use when doing an INSERT.
</p>

<p>Specifying a PRIMARY KEY normally just creates a UNIQUE index
................................................................................

<p>There are no arbitrary limits on the number
of columns or on the number of constraints in a table.
The total amount of data in a single row is limited to about
1 megabytes.  (This limit can be increased to 16MB by changing
a single #define in the source code and recompiling.)</p>

<p>The CREATE TABLE AS form defines the table to be
the result set of a query.  The names of the table columns are
the names of the columns in the result.</p>

<p>The exact text
of each CREATE TABLE statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE TABLE statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.
If the original command was a CREATE TABLE AS then then an equivalent
CREATE TABLE statement is synthesized and store in <b>sqlite_master</b>
in place of the original command.
</p>
}

Section DELETE delete

Syntax {sql-statement} {
DELETE FROM <table-name> [WHERE <expression>]
}