SQLite

Check-in [131cc6e152]
Login

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

Overview
Comment:Simplification of the syntax: Merely append "WITHOUT rowid" to the end of the table definition.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | omit-rowid
Files: files | file ages | folders
SHA1: 131cc6e152abe1a2d48e6d8d40d2c2f8dbe723e7
User & Date: drh 2013-10-21 02:14:45.234
Context
2013-10-22
01:18
Previous refactor is not going to be helpful because implied indices must be created in the same order that they appear in the CREATE TABLE statement for backwards compatibility. This is a much smaller change to clean up a few loose ends. (check-in: 824b549f9b user: drh tags: omit-rowid)
2013-10-21
23:55
Minor refactoring of the PRIMARY KEY parsing logic to facilitate enhancements. (Closed-Leaf check-in: 4e69dd5f9b user: drh tags: omit-rowid)
02:14
Simplification of the syntax: Merely append "WITHOUT rowid" to the end of the table definition. (check-in: 131cc6e152 user: drh tags: omit-rowid)
2013-10-19
23:31
Experimental changes toward "index only" tables. Add the ability to specify options on CREATE TABLE statements using the WITH clause modeled after PostgreSQL and SQL Server. Only the "omit_rowid" option is currently recognized and that option is currently a no-op. (check-in: 0248ec5e6e user: drh tags: omit-rowid)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588

1589
1590
1591
1592
1593
1594
1595
** 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 sqlite3EndTable(
  Parse *pParse,          /* Parse context */
  Token *pCons,           /* The ',' token after the last column defn. */
  Token *pEnd1,           /* The ')' before options in the CREATE TABLE */
  Token *pEnd2,           /* The final ')' in the whole CREATE TABLE */
  IdList *pOpts,          /* List of table options.  May be NULL */
  Select *pSelect         /* Select from a "CREATE ... AS SELECT" */
){
  Table *p;                 /* The new table */
  sqlite3 *db = pParse->db; /* The database connection */
  int iDb;                  /* Database in which the table lives */
  Index *pIdx;              /* An implied index of the table */

  if( (pEnd1==0 && pSelect==0) || db->mallocFailed ){
    goto end_table_exception;
  }
  p = pParse->pNewTable;
  if( p==0 ) goto end_table_exception;

  assert( !db->init.busy || !pSelect );

  if( pOpts ){
    int i;
    for(i=0; i<pOpts->nId; i++){
      if( sqlite3_stricmp(pOpts->a[i].zName, "omit_rowid")==0 ){
        p->tabFlags |= TF_WithoutRowid;
        if( (p->tabFlags & TF_HasPrimaryKey)==0 ){
          sqlite3ErrorMsg(pParse, "no PRIMARY KEY for table %s", p->zName);
        }
        continue;
      }
      sqlite3ErrorMsg(pParse, "unknown table option: %s", pOpts->a[i].zName);
    }

  }

  iDb = sqlite3SchemaToIndex(db, p->pSchema);

#ifndef SQLITE_OMIT_CHECK
  /* Resolve names in all CHECK constraint expressions.
  */







|
<
|







|
|


|



<
<
<
<
|
|
|
|
<
<
<
<
>







1552
1553
1554
1555
1556
1557
1558
1559

1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575




1576
1577
1578
1579




1580
1581
1582
1583
1584
1585
1586
1587
** 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 sqlite3EndTable(
  Parse *pParse,          /* Parse context */
  Token *pCons,           /* The ',' token after the last column defn. */
  Token *pEnd,            /* The ')' before options in the CREATE TABLE */

  u8 tabOpts,             /* Extra table options. Usually 0. */
  Select *pSelect         /* Select from a "CREATE ... AS SELECT" */
){
  Table *p;                 /* The new table */
  sqlite3 *db = pParse->db; /* The database connection */
  int iDb;                  /* Database in which the table lives */
  Index *pIdx;              /* An implied index of the table */

  if( (pEnd==0 && pSelect==0) || db->mallocFailed ){
    return;
  }
  p = pParse->pNewTable;
  if( p==0 ) return;

  assert( !db->init.busy || !pSelect );





  if( tabOpts & TF_WithoutRowid ){
    if( (p->tabFlags & TF_HasPrimaryKey)==0 ){
      sqlite3ErrorMsg(pParse, "no PRIMARY KEY for table %s", p->zName);
    }




    p->tabFlags |= TF_WithoutRowid;
  }

  iDb = sqlite3SchemaToIndex(db, p->pSchema);

#ifndef SQLITE_OMIT_CHECK
  /* Resolve names in all CHECK constraint expressions.
  */
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
    int n;
    Vdbe *v;
    char *zType;    /* "view" or "table" */
    char *zType2;   /* "VIEW" or "TABLE" */
    char *zStmt;    /* Text of the CREATE TABLE or CREATE VIEW statement */

    v = sqlite3GetVdbe(pParse);
    if( NEVER(v==0) ) goto end_table_exception;

    sqlite3VdbeAddOp1(v, OP_Close, 0);

    /* 
    ** Initialize zType for the new view or table.
    */
    if( p->pSelect==0 ){







|







1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
    int n;
    Vdbe *v;
    char *zType;    /* "view" or "table" */
    char *zType2;   /* "VIEW" or "TABLE" */
    char *zStmt;    /* Text of the CREATE TABLE or CREATE VIEW statement */

    v = sqlite3GetVdbe(pParse);
    if( NEVER(v==0) ) return;

    sqlite3VdbeAddOp1(v, OP_Close, 0);

    /* 
    ** Initialize zType for the new view or table.
    */
    if( p->pSelect==0 ){
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690

1691
1692
1693
1694
1695
1696
1697
      sqlite3VdbeChangeP5(v, OPFLAG_P2ISREG);
      pParse->nTab = 2;
      sqlite3SelectDestInit(&dest, SRT_Table, 1);
      sqlite3Select(pParse, pSelect, &dest);
      sqlite3VdbeAddOp1(v, OP_Close, 1);
      if( pParse->nErr==0 ){
        pSelTab = sqlite3ResultSetOfSelect(pParse, pSelect);
        if( pSelTab==0 ) goto end_table_exception;
        assert( p->aCol==0 );
        p->nCol = pSelTab->nCol;
        p->aCol = pSelTab->aCol;
        pSelTab->nCol = 0;
        pSelTab->aCol = 0;
        sqlite3DeleteTable(db, pSelTab);
      }
    }

    /* Compute the complete text of the CREATE statement */
    if( pSelect ){
      zStmt = createTableStmt(db, p);
    }else{
      n = (int)(pEnd2->z - pParse->sNameToken.z) + 1;

      zStmt = sqlite3MPrintf(db, 
          "CREATE %s %.*s", zType2, n, pParse->sNameToken.z
      );
    }

    /* A slot for the record has already been allocated in the 
    ** SQLITE_MASTER table.  We just need to update that slot with all







|













|
>







1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
      sqlite3VdbeChangeP5(v, OPFLAG_P2ISREG);
      pParse->nTab = 2;
      sqlite3SelectDestInit(&dest, SRT_Table, 1);
      sqlite3Select(pParse, pSelect, &dest);
      sqlite3VdbeAddOp1(v, OP_Close, 1);
      if( pParse->nErr==0 ){
        pSelTab = sqlite3ResultSetOfSelect(pParse, pSelect);
        if( pSelTab==0 ) return;
        assert( p->aCol==0 );
        p->nCol = pSelTab->nCol;
        p->aCol = pSelTab->aCol;
        pSelTab->nCol = 0;
        pSelTab->aCol = 0;
        sqlite3DeleteTable(db, pSelTab);
      }
    }

    /* Compute the complete text of the CREATE statement */
    if( pSelect ){
      zStmt = createTableStmt(db, p);
    }else{
      n = (int)(pParse->sLastToken.z - pParse->sNameToken.z);
      if( pParse->sLastToken.z[0]!=';' ) n += pParse->sLastToken.n;
      zStmt = sqlite3MPrintf(db, 
          "CREATE %s %.*s", zType2, n, pParse->sNameToken.z
      );
    }

    /* A slot for the record has already been allocated in the 
    ** SQLITE_MASTER table.  We just need to update that slot with all
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
    Schema *pSchema = p->pSchema;
    assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
    pOld = sqlite3HashInsert(&pSchema->tblHash, p->zName,
                             sqlite3Strlen30(p->zName),p);
    if( pOld ){
      assert( p==pOld );  /* Malloc must have failed inside HashInsert() */
      db->mallocFailed = 1;
      goto end_table_exception;
    }
    pParse->pNewTable = 0;
    db->flags |= SQLITE_InternChanges;

#ifndef SQLITE_OMIT_ALTERTABLE
    if( !p->pSelect ){
      const char *zName = (const char *)pParse->sNameToken.z;
      int nName;
      assert( !pSelect && pCons && pEnd1 );
      if( pCons->z==0 ){
        pCons = pEnd1;
      }
      nName = (int)((const char *)pCons->z - zName);
      p->addColOffset = 13 + sqlite3Utf8CharLen(zName, nName);
    }
#endif
  }

end_table_exception:
  sqlite3IdListDelete(db, pOpts);
  return;
}

#ifndef SQLITE_OMIT_VIEW
/*
** The parser calls this routine in order to create a new VIEW
*/
void sqlite3CreateView(







|








|

|






<
<
<
<







1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758




1759
1760
1761
1762
1763
1764
1765
    Schema *pSchema = p->pSchema;
    assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
    pOld = sqlite3HashInsert(&pSchema->tblHash, p->zName,
                             sqlite3Strlen30(p->zName),p);
    if( pOld ){
      assert( p==pOld );  /* Malloc must have failed inside HashInsert() */
      db->mallocFailed = 1;
      return;
    }
    pParse->pNewTable = 0;
    db->flags |= SQLITE_InternChanges;

#ifndef SQLITE_OMIT_ALTERTABLE
    if( !p->pSelect ){
      const char *zName = (const char *)pParse->sNameToken.z;
      int nName;
      assert( !pSelect && pCons && pEnd );
      if( pCons->z==0 ){
        pCons = pEnd;
      }
      nName = (int)((const char *)pCons->z - zName);
      p->addColOffset = 13 + sqlite3Utf8CharLen(zName, nName);
    }
#endif
  }




}

#ifndef SQLITE_OMIT_VIEW
/*
** The parser calls this routine in order to create a new VIEW
*/
void sqlite3CreateView(
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
  n = (int)(sEnd.z - pBegin->z);
  z = pBegin->z;
  while( ALWAYS(n>0) && sqlite3Isspace(z[n-1]) ){ n--; }
  sEnd.z = &z[n-1];
  sEnd.n = 1;

  /* Use sqlite3EndTable() to add the view to the SQLITE_MASTER table */
  sqlite3EndTable(pParse, 0, &sEnd, &sEnd, 0, 0);
  return;
}
#endif /* SQLITE_OMIT_VIEW */

#if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_VIRTUALTABLE)
/*
** The Table structure pTable is really a VIEW.  Fill in the names of







|







1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
  n = (int)(sEnd.z - pBegin->z);
  z = pBegin->z;
  while( ALWAYS(n>0) && sqlite3Isspace(z[n-1]) ){ n--; }
  sEnd.z = &z[n-1];
  sEnd.n = 1;

  /* Use sqlite3EndTable() to add the view to the SQLITE_MASTER table */
  sqlite3EndTable(pParse, 0, &sEnd, 0, 0);
  return;
}
#endif /* SQLITE_OMIT_VIEW */

#if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_VIRTUALTABLE)
/*
** The Table structure pTable is really a VIEW.  Fill in the names of
Changes to src/parse.y.
159
160
161
162
163
164
165
166
167
168
169
170
171

172

173

174



175

176
177
178
179
180
181
182
ifnotexists(A) ::= .              {A = 0;}
ifnotexists(A) ::= IF NOT EXISTS. {A = 1;}
%type temp {int}
%ifndef SQLITE_OMIT_TEMPDB
temp(A) ::= TEMP.  {A = 1;}
%endif  SQLITE_OMIT_TEMPDB
temp(A) ::= .      {A = 0;}
create_table_args ::= LP columnlist conslist_opt(X) RP(E1). {
  sqlite3EndTable(pParse,&X,&E1,&E1,0,0);
}
create_table_args ::= LP columnlist conslist_opt(X) RP(E1)
                      WITH LP idlist(Z) RP(E2). {
  sqlite3EndTable(pParse,&X,&E1,&E2,Z,0);

}

create_table_args ::= AS select(S). {

  sqlite3EndTable(pParse,0,0,0,0,S);



  sqlite3SelectDelete(pParse->db, S);

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

// A "column" is a complete description of a single column in a
// CREATE TABLE statement.  This includes the column name, its
// datatype, and other keywords such as PRIMARY KEY, UNIQUE, REFERENCES,







|
|

|
<
|
>

>
|
>
|
>
>
>
|
>







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
ifnotexists(A) ::= .              {A = 0;}
ifnotexists(A) ::= IF NOT EXISTS. {A = 1;}
%type temp {int}
%ifndef SQLITE_OMIT_TEMPDB
temp(A) ::= TEMP.  {A = 1;}
%endif  SQLITE_OMIT_TEMPDB
temp(A) ::= .      {A = 0;}
create_table_args ::= LP columnlist conslist_opt(X) RP(E) table_options(F). {
  sqlite3EndTable(pParse,&X,&E,F,0);
}
create_table_args ::= AS select(S). {

  sqlite3EndTable(pParse,0,0,0,S);
  sqlite3SelectDelete(pParse->db, S);
}
%type table_options {u8}
table_options(A) ::= .    {A = 0;}
table_options(A) ::= WITHOUT nm(X). {
  if( X.n==5 && sqlite3_strnicmp(X.z,"rowid",5)==0 ){
    A = TF_WithoutRowid;
  }else{
    A = 0;
    sqlite3ErrorMsg(pParse, "unknown table option: %.*s", X.n, X.z);
  }
}
columnlist ::= columnlist COMMA column.
columnlist ::= column.

// A "column" is a complete description of a single column in a
// CREATE TABLE statement.  This includes the column name, its
// datatype, and other keywords such as PRIMARY KEY, UNIQUE, REFERENCES,
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
// This obviates the need for the "id" nonterminal.
//
%fallback ID
  ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
  CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
  IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN
  QUERY KEY OF OFFSET PRAGMA RAISE RELEASE REPLACE RESTRICT ROW ROLLBACK
  SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH
%ifdef SQLITE_OMIT_COMPOUND_SELECT
  EXCEPT INTERSECT UNION
%endif SQLITE_OMIT_COMPOUND_SELECT
  REINDEX RENAME CTIME_KW IF
  .
%wildcard ANY.








|







211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
// This obviates the need for the "id" nonterminal.
//
%fallback ID
  ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
  CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
  IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN
  QUERY KEY OF OFFSET PRAGMA RAISE RELEASE REPLACE RESTRICT ROW ROLLBACK
  SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITHOUT
%ifdef SQLITE_OMIT_COMPOUND_SELECT
  EXCEPT INTERSECT UNION
%endif SQLITE_OMIT_COMPOUND_SELECT
  REINDEX RENAME CTIME_KW IF
  .
%wildcard ANY.

Changes to src/sqliteInt.h.
2769
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
void sqlite3AddColumn(Parse*,Token*);
void sqlite3AddNotNull(Parse*, int);
void sqlite3AddPrimaryKey(Parse*, ExprList*, int, int, int);
void sqlite3AddCheckConstraint(Parse*, Expr*);
void sqlite3AddColumnType(Parse*,Token*);
void sqlite3AddDefaultValue(Parse*,ExprSpan*);
void sqlite3AddCollateType(Parse*, Token*);
void sqlite3EndTable(Parse*,Token*,Token*,Token*,IdList*,Select*);
int sqlite3ParseUri(const char*,const char*,unsigned int*,
                    sqlite3_vfs**,char**,char **);
Btree *sqlite3DbNameToBtree(sqlite3*,const char*);
int sqlite3CodeOnce(Parse *);

Bitvec *sqlite3BitvecCreate(u32);
int sqlite3BitvecTest(Bitvec*, u32);







|







2769
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
void sqlite3AddColumn(Parse*,Token*);
void sqlite3AddNotNull(Parse*, int);
void sqlite3AddPrimaryKey(Parse*, ExprList*, int, int, int);
void sqlite3AddCheckConstraint(Parse*, Expr*);
void sqlite3AddColumnType(Parse*,Token*);
void sqlite3AddDefaultValue(Parse*,ExprSpan*);
void sqlite3AddCollateType(Parse*, Token*);
void sqlite3EndTable(Parse*,Token*,Token*,u8,Select*);
int sqlite3ParseUri(const char*,const char*,unsigned int*,
                    sqlite3_vfs**,char**,char **);
Btree *sqlite3DbNameToBtree(sqlite3*,const char*);
int sqlite3CodeOnce(Parse *);

Bitvec *sqlite3BitvecCreate(u32);
int sqlite3BitvecTest(Bitvec*, u32);
Changes to test/alter.test.
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874

  do_test alter-15.$tn.2 {
    catchsql "ALTER TABLE $tbl ADD COLUMN xyz"
  } [list 1 "table $tbl may not be altered"]
}

#------------------------------------------------------------------------
# Verify that ALTER TABLE works on tables with WITH options.
#
do_execsql_test alter-16.1 {
  CREATE TABLE t16a(a TEXT, b REAL, c INT, PRIMARY KEY(a,b)) WITH (omit_rowid);
  INSERT INTO t16a VALUES('abc',1.25,99);
  ALTER TABLE t16a ADD COLUMN d TEXT DEFAULT 'xyzzy';
  INSERT INTO t16a VALUES('cba',5.5,98,'fizzle');
  SELECT * FROM t16a ORDER BY a;
} {abc 1.25 99 xyzzy cba 5.5 98 fizzle}
do_execsql_test alter-16.2 {
  ALTER TABLE t16a RENAME TO t16a_rn;







|


|







857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874

  do_test alter-15.$tn.2 {
    catchsql "ALTER TABLE $tbl ADD COLUMN xyz"
  } [list 1 "table $tbl may not be altered"]
}

#------------------------------------------------------------------------
# Verify that ALTER TABLE works on tables with the WITHOUT rowid option.
#
do_execsql_test alter-16.1 {
  CREATE TABLE t16a(a TEXT, b REAL, c INT, PRIMARY KEY(a,b)) WITHOUT rowid;
  INSERT INTO t16a VALUES('abc',1.25,99);
  ALTER TABLE t16a ADD COLUMN d TEXT DEFAULT 'xyzzy';
  INSERT INTO t16a VALUES('cba',5.5,98,'fizzle');
  SELECT * FROM t16a ORDER BY a;
} {abc 1.25 99 xyzzy cba 5.5 98 fizzle}
do_execsql_test alter-16.2 {
  ALTER TABLE t16a RENAME TO t16a_rn;
Changes to test/tableopts.test.
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


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

do_test tableopt-1.1 {
  catchsql {
    CREATE TABLE t1(a,b) WITH (omit_rowid);
  }
} {1 {no PRIMARY KEY for table t1}}
do_test tableopt-1.2 {
  catchsql {
    CREATE TABLE t1(a,b) WITH (unknown1, unknown2);
  }
} {1 {unknown table option: unknown2}}
do_test tableopt-1.3 {
  catchsql {
    CREATE TABLE t1(a,b,c,PRIMARY KEY(a,b)) WITH (omit_rowid, unknown3);
  }
} {1 {unknown table option: unknown3}}
do_test tableopt-1.4 {
  catchsql {
    CREATE TABLE t1(a,b,c,PRIMARY KEY(a,b)) WITH (unknown4, omit_rowid);
  }
} {1 {unknown table option: unknown4}}

do_execsql_test tableopt-2.1 {
  CREATE TABLE t1(a, b, c, PRIMARY KEY(a,b)) WITH (omit_rowid);
  INSERT INTO t1 VALUES(1,2,3),(2,3,4);
  SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b;
} {3 4}
do_execsql_test tableopt-2.2 {
  VACUUM;
  SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b;
} {3 4}
do_test tableopt-2.3 {
  sqlite3 db2 test.db
  db2 eval {SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b;}
} {3 4}
db2 close










  
finish_test







|




|


<
<
<
|
<
<
<
<
<
<
<

|












>
>
>
>
>
>
>
>
>
>


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


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

do_test tableopt-1.1 {
  catchsql {
    CREATE TABLE t1(a,b) WITHOUT rowid;
  }
} {1 {no PRIMARY KEY for table t1}}
do_test tableopt-1.2 {
  catchsql {
    CREATE TABLE t1(a,b) WITHOUT unknown2;
  }
} {1 {unknown table option: unknown2}}











do_execsql_test tableopt-2.1 {
  CREATE TABLE t1(a, b, c, PRIMARY KEY(a,b)) WITHOUT rowid;
  INSERT INTO t1 VALUES(1,2,3),(2,3,4);
  SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b;
} {3 4}
do_execsql_test tableopt-2.2 {
  VACUUM;
  SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b;
} {3 4}
do_test tableopt-2.3 {
  sqlite3 db2 test.db
  db2 eval {SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b;}
} {3 4}
db2 close

# Make sure the "without" keyword is still usable as a table or
# column name.
#
do_execsql_test tableopt-3.1 {
  CREATE TABLE without(x INTEGER PRIMARY KEY, without TEXT);
  INSERT INTO without VALUES(1, 'xyzzy'), (2, 'fizzle');
  SELECT * FROM without WHERE without='xyzzy';
} {1 xyzzy}

  
finish_test
Changes to tool/mkkeywordhash.c.
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
  { "UNIQUE",           "TK_UNIQUE",       ALWAYS                 },
  { "UPDATE",           "TK_UPDATE",       ALWAYS                 },
  { "USING",            "TK_USING",        ALWAYS                 },
  { "VACUUM",           "TK_VACUUM",       VACUUM                 },
  { "VALUES",           "TK_VALUES",       ALWAYS                 },
  { "VIEW",             "TK_VIEW",         VIEW                   },
  { "VIRTUAL",          "TK_VIRTUAL",      VTAB                   },
  { "WITH",             "TK_WITH",         ALWAYS                 },
  { "WHEN",             "TK_WHEN",         ALWAYS                 },
  { "WHERE",            "TK_WHERE",        ALWAYS                 },
};

/* Number of keywords */
static int nKeyword = (sizeof(aKeywordTable)/sizeof(aKeywordTable[0]));








|







258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
  { "UNIQUE",           "TK_UNIQUE",       ALWAYS                 },
  { "UPDATE",           "TK_UPDATE",       ALWAYS                 },
  { "USING",            "TK_USING",        ALWAYS                 },
  { "VACUUM",           "TK_VACUUM",       VACUUM                 },
  { "VALUES",           "TK_VALUES",       ALWAYS                 },
  { "VIEW",             "TK_VIEW",         VIEW                   },
  { "VIRTUAL",          "TK_VIRTUAL",      VTAB                   },
  { "WITHOUT",          "TK_WITHOUT",      ALWAYS                 },
  { "WHEN",             "TK_WHEN",         ALWAYS                 },
  { "WHERE",            "TK_WHERE",        ALWAYS                 },
};

/* Number of keywords */
static int nKeyword = (sizeof(aKeywordTable)/sizeof(aKeywordTable[0]));