000001  # 2013-11-26
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  #
000012  # Requirements testing for WITHOUT ROWID tables.
000013  #
000014  
000015  set testdir [file dirname $argv0]
000016  source $testdir/tester.tcl
000017  
000018  ifcapable !incrblob {
000019    finish_test
000020    return
000021  }
000022  
000023  # EVIDENCE-OF: R-36924-43758 By default, every row in SQLite has a
000024  # special column, usually called the "rowid", that uniquely identifies
000025  # that row within the table.
000026  #
000027  # EVIDENCE-OF: R-32341-39358 However if the phrase "WITHOUT ROWID" is
000028  # added to the end of a CREATE TABLE statement, then the special "rowid"
000029  # column is omitted.
000030  #
000031  do_execsql_test without_rowid5-1.1 {
000032    CREATE TABLE t1(a PRIMARY KEY,b,c);
000033    CREATE TABLE t1w(a PRIMARY KEY,b,c) WITHOUT ROWID;
000034    INSERT INTO t1 VALUES(1565,681,1148),(1429,1190,1619),(425,358,1306);
000035    INSERT INTO t1w SELECT a,b,c FROM t1;
000036    SELECT rowid, _rowid_, oid FROM t1 ORDER BY a DESC;
000037  } {1 1 1 2 2 2 3 3 3}
000038  do_catchsql_test without_rowid5-1.2 {
000039    SELECT rowid FROM t1w;
000040  } {1 {no such column: rowid}}
000041  do_catchsql_test without_rowid5-1.3 {
000042    SELECT _rowid_ FROM t1w;
000043  } {1 {no such column: _rowid_}}
000044  do_catchsql_test without_rowid5-1.4 {
000045    SELECT oid FROM t1w;
000046  } {1 {no such column: oid}}
000047  
000048  # EVIDENCE-OF: R-00217-01605 To create a WITHOUT ROWID table, simply add
000049  # the keywords "WITHOUT ROWID" to the end of the CREATE TABLE statement.
000050  # For example: CREATE TABLE IF NOT EXISTS wordcount( word TEXT PRIMARY
000051  # KEY, cnt INTEGER ) WITHOUT ROWID;
000052  #
000053  do_execsql_test without_rowid5-2.1 {
000054    CREATE TABLE IF NOT EXISTS wordcount(
000055      word TEXT PRIMARY KEY,
000056      cnt INTEGER
000057    ) WITHOUT ROWID;
000058    INSERT INTO wordcount VALUES('one',1);
000059  } {}
000060  do_catchsql_test without_rowid5-2.2 {
000061    SELECT rowid FROM wordcount;
000062  } {1 {no such column: rowid}}
000063  
000064  # EVIDENCE-OF: R-24770-17719 As with all SQL syntax, the case of the
000065  # keywords does not matter. One can write "WITHOUT rowid" or "without
000066  # rowid" or "WiThOuT rOwId" and it will mean the same thing.
000067  #
000068  do_execsql_test without_rowid5-2.3 {
000069    CREATE TABLE IF NOT EXISTS wordcount_b(
000070      word TEXT PRIMARY KEY,
000071      cnt INTEGER
000072    ) WITHOUT rowid;
000073    INSERT INTO wordcount_b VALUES('one',1);
000074  } {}
000075  do_catchsql_test without_rowid5-2.4 {
000076    SELECT rowid FROM wordcount_b;
000077  } {1 {no such column: rowid}}
000078  do_execsql_test without_rowid5-2.5 {
000079    CREATE TABLE IF NOT EXISTS wordcount_c(
000080      word TEXT PRIMARY KEY,
000081      cnt INTEGER
000082    ) without rowid;
000083    INSERT INTO wordcount_c VALUES('one',1);
000084  } {}
000085  do_catchsql_test without_rowid5-2.6 {
000086    SELECT rowid FROM wordcount_c;
000087  } {1 {no such column: rowid}}
000088  do_execsql_test without_rowid5-2.7 {
000089    CREATE TABLE IF NOT EXISTS wordcount_d(
000090      word TEXT PRIMARY KEY,
000091      cnt INTEGER
000092    ) WITHOUT rowid;
000093    INSERT INTO wordcount_d VALUES('one',1);
000094  } {}
000095  do_catchsql_test without_rowid5-2.8 {
000096    SELECT rowid FROM wordcount_d;
000097  } {1 {no such column: rowid}}
000098  
000099  # EVIDENCE-OF: R-01418-51310 However, only "rowid" works as the keyword
000100  # in the CREATE TABLE statement.
000101  #
000102  do_catchsql_test without_rowid5-3.1 {
000103    CREATE TABLE IF NOT EXISTS error1(
000104      word TEXT PRIMARY KEY,
000105      cnt INTEGER
000106    ) WITHOUT _rowid_;
000107  } {1 {unknown table option: _rowid_}}  
000108  do_catchsql_test without_rowid5-3.2 {
000109    CREATE TABLE IF NOT EXISTS error2(
000110      word TEXT PRIMARY KEY,
000111      cnt INTEGER
000112    ) WITHOUT oid;
000113  } {1 {unknown table option: oid}}  
000114  
000115  # EVIDENCE-OF: R-58033-17334 An error is raised if a CREATE TABLE
000116  # statement with the WITHOUT ROWID clause lacks a PRIMARY KEY.
000117  #
000118  # EVIDENCE-OF: R-63443-09418 Every WITHOUT ROWID table must have a
000119  # PRIMARY KEY.
000120  #
000121  # EVIDENCE-OF: R-27966-31616 An attempt to create a WITHOUT ROWID table
000122  # without a PRIMARY KEY results in an error.
000123  #
000124  do_catchsql_test without_rowid5-4.1 {
000125    CREATE TABLE IF NOT EXISTS error3(
000126      word TEXT UNIQUE,
000127      cnt INTEGER
000128    ) WITHOUT ROWID;
000129  } {1 {PRIMARY KEY missing on table error3}}
000130  
000131  # EVIDENCE-OF: R-48230-36247 The special behaviors associated "INTEGER
000132  # PRIMARY KEY" do not apply on WITHOUT ROWID tables.
000133  #
000134  do_execsql_test without_rowid5-5.1 {
000135    CREATE TABLE ipk(key INTEGER PRIMARY KEY, val TEXT) WITHOUT ROWID;
000136    INSERT INTO ipk VALUES('rival','bonus'); -- ok to insert non-integer key
000137    SELECT * FROM ipk;
000138  } {rival bonus}
000139  do_catchsql_test without_rowid5-5.2a {
000140    BEGIN;
000141    INSERT INTO ipk VALUES(NULL,'sample'); -- no automatic generation of keys
000142  } {1 {NOT NULL constraint failed: ipk.key}}
000143  do_execsql_test without_rowid5-5.2b {
000144    ROLLBACK;
000145  } {}
000146  
000147  # EVIDENCE-OF: R-33142-02092 AUTOINCREMENT does not work on WITHOUT
000148  # ROWID tables.
000149  #
000150  # EVIDENCE-OF: R-53084-07740 An error is raised if the "AUTOINCREMENT"
000151  # keyword is used in the CREATE TABLE statement for a WITHOUT ROWID
000152  # table.
000153  #
000154  do_catchsql_test without_rowid5-5.3 {
000155    CREATE TABLE ipk2(key INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)WITHOUT ROWID;
000156  } {1 {AUTOINCREMENT not allowed on WITHOUT ROWID tables}}
000157  
000158  # EVIDENCE-OF: R-27831-00579 NOT NULL is enforced on every column of the
000159  # PRIMARY KEY in a WITHOUT ROWID table.
000160  #
000161  # EVIDENCE-OF: R-29781-51289 So, ordinary rowid tables in SQLite violate
000162  # the SQL standard and allow NULL values in PRIMARY KEY fields.
000163  #
000164  # EVIDENCE-OF: R-27472-62612 But WITHOUT ROWID tables do follow the
000165  # standard and will throw an error on any attempt to insert a NULL into
000166  # a PRIMARY KEY column.
000167  #
000168  do_execsql_test without_rowid5-5.4 {
000169    CREATE TABLE nn(a, b, c, d, e, PRIMARY KEY(c,a,e));
000170    CREATE TABLE nnw(a, b, c, d, e, PRIMARY KEY(c,a,e)) WITHOUT ROWID;
000171    INSERT INTO nn VALUES(1,2,3,4,5);
000172    INSERT INTO nnw VALUES(1,2,3,4,5);
000173  } {}
000174  do_execsql_test without_rowid5-5.5 {
000175    INSERT INTO nn VALUES(NULL, 3,4,5,6);
000176    INSERT INTO nn VALUES(3,4,NULL,7,8);
000177    INSERT INTO nn VALUES(4,5,6,7,NULL);
000178    SELECT count(*) FROM nn;
000179  } {4}
000180  do_catchsql_test without_rowid5-5.6 {
000181    INSERT INTO nnw VALUES(NULL, 3,4,5,6);
000182  } {1 {NOT NULL constraint failed: nnw.a}}
000183  do_catchsql_test without_rowid5-5.7 {
000184    INSERT INTO nnw VALUES(3,4,NULL,7,8)
000185  } {1 {NOT NULL constraint failed: nnw.c}}
000186  do_catchsql_test without_rowid5-5.8 {
000187    INSERT INTO nnw VALUES(4,5,6,7,NULL)
000188  } {1 {NOT NULL constraint failed: nnw.e}}
000189  do_execsql_test without_rowid5-5.9 {
000190    SELECT count(*) FROM nnw;
000191  } {1}
000192  
000193  # Ticket f2be158c57aaa8c6 (2021-08-18)
000194  # NOT NULL ON CONFLICT clauses work on WITHOUT ROWID tables now.
000195  # 
000196  do_test without_rowid5-5.100 {
000197    db eval {
000198      DROP TABLE IF EXISTS t5;
000199      CREATE TABLE t5(
000200        a INT NOT NULL ON CONFLICT ROLLBACK,
000201        b TEXT,
000202        c TEXT,
000203        PRIMARY KEY(a,b)
000204      ) WITHOUT ROWID;
000205      BEGIN;
000206      INSERT INTO t5(a,b,c) VALUES(1,2,3);
000207    }
000208    catch {db eval {INSERT INTO t5(a,b,c) VALUES(NULL,6,7);}}
000209    db eval {
000210      SELECT * FROM t5;
000211    }
000212  } {}
000213  do_test without_rowid5-5.101 {
000214    db eval {
000215      DROP TABLE IF EXISTS t5;
000216      CREATE TABLE t5(
000217        a INT NOT NULL ON CONFLICT ABORT,
000218        b TEXT,
000219        c TEXT,
000220        PRIMARY KEY(a,b)
000221      ) WITHOUT ROWID;
000222      BEGIN;
000223      INSERT INTO t5(a,b,c) VALUES(1,2,3);
000224    }
000225    catch {db eval {INSERT INTO t5(a,b,c) VALUES(NULL,6,7);}}
000226    db eval {
000227      COMMIT;
000228      SELECT * FROM t5;
000229    }
000230  } {1 2 3}
000231  do_test without_rowid5-5.102 {
000232    db eval {
000233      DROP TABLE IF EXISTS t5;
000234      CREATE TABLE t5(
000235        a INT NOT NULL ON CONFLICT FAIL,
000236        b TEXT,
000237        c TEXT,
000238        PRIMARY KEY(a,b)
000239      ) WITHOUT ROWID;
000240    }
000241    catch {db eval {INSERT INTO t5(a,b,c) VALUES(1,2,3),(NULL,4,5),(6,7,8);}}
000242    db eval {
000243      SELECT * FROM t5;
000244    }
000245  } {1 2 3}
000246  do_test without_rowid5-5.103 {
000247    db eval {
000248      DROP TABLE IF EXISTS t5;
000249      CREATE TABLE t5(
000250        a INT NOT NULL ON CONFLICT IGNORE,
000251        b TEXT,
000252        c TEXT,
000253        PRIMARY KEY(a,b)
000254      ) WITHOUT ROWID;
000255      INSERT INTO t5(a,b,c) VALUES(1,2,3),(NULL,4,5),(6,7,8);
000256      SELECT * FROM t5;
000257    }
000258  } {1 2 3 6 7 8}
000259  do_test without_rowid5-5.104 {
000260    db eval {
000261      DROP TABLE IF EXISTS t5;
000262      CREATE TABLE t5(
000263        a INT NOT NULL ON CONFLICT REPLACE DEFAULT 3,
000264        b TEXT,
000265        c TEXT,
000266        PRIMARY KEY(a,b)
000267      ) WITHOUT ROWID;
000268      INSERT INTO t5(a,b,c) VALUES(1,2,3),(NULL,4,5),(6,7,8);
000269      SELECT * FROM t5;
000270    }
000271  } {1 2 3 3 4 5 6 7 8}
000272  
000273  
000274  # EVIDENCE-OF: R-12643-30541 The incremental blob I/O mechanism does not
000275  # work for WITHOUT ROWID tables.
000276  #
000277  # EVIDENCE-OF: R-40134-30296 Table zTable is a WITHOUT ROWID table
000278  #
000279  do_execsql_test without_rowid5-6.1 {
000280    CREATE TABLE b1(a INTEGER PRIMARY KEY, b BLOB) WITHOUT ROWID;
000281    INSERT INTO b1 VALUES(1,x'0102030405060708090a0b0c0d0e0f');
000282  } {}
000283  do_test without_rowid5-6.2 {
000284    set rc [catch {db incrblob b1 b 1} msg]
000285    lappend rc $msg
000286  } {1 {cannot open table without rowid: b1}}
000287  
000288  
000289  finish_test