1 # 2004 November 12

     2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # a legal notice, here is a blessing:

     5 #

     6 #    May you do good and not evil.

     7 #    May you find forgiveness for yourself and forgive others.

     8 #    May you share freely, never taking more than you give.

     9 #

    10 #*************************************************************************

    11 # This file implements regression tests for SQLite library.  The

    12 # focus of this script is testing the AUTOINCREMENT features.

    13 #

    14 # $Id: autoinc.test,v 1.13 2008/08/11 18:44:58 drh Exp $

    15 #

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 # If the library is not compiled with autoincrement support then

    21 # skip all tests in this file.

    22 #

    23 ifcapable {!autoinc} {

    24   finish_test

    25   return

    26 }

    27 

    28 # The database is initially empty.

    29 #

    30 do_test autoinc-1.1 {

    31   execsql {

    32     SELECT name FROM sqlite_master WHERE type='table';

    33   }

    34 } {}

    35 

    36 # Add a table with the AUTOINCREMENT feature.  Verify that the

    37 # SQLITE_SEQUENCE table gets created.

    38 #

    39 do_test autoinc-1.2 {

    40   execsql {

    41     CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y);

    42     SELECT name FROM sqlite_master WHERE type='table';

    43   }

    44 } {t1 sqlite_sequence}

    45 

    46 # The SQLITE_SEQUENCE table is initially empty

    47 #

    48 do_test autoinc-1.3 {

    49   execsql {

    50     SELECT * FROM sqlite_sequence;

    51   }

    52 } {}

    53 do_test autoinc-1.3.1 {

    54   catchsql {

    55     CREATE INDEX seqidx ON sqlite_sequence(name)

    56   }

    57 } {1 {table sqlite_sequence may not be indexed}}

    58 

    59 # Close and reopen the database.  Verify that everything is still there.

    60 #

    61 do_test autoinc-1.4 {

    62   db close

    63   sqlite3 db test.db

    64   execsql {

    65     SELECT * FROM sqlite_sequence;

    66   }

    67 } {}

    68 

    69 # We are not allowed to drop the sqlite_sequence table.

    70 #

    71 do_test autoinc-1.5 {

    72   catchsql {DROP TABLE sqlite_sequence}

    73 } {1 {table sqlite_sequence may not be dropped}}

    74 do_test autoinc-1.6 {

    75   execsql {SELECT name FROM sqlite_master WHERE type='table'}

    76 } {t1 sqlite_sequence}

    77 

    78 # Insert an entries into the t1 table and make sure the largest key

    79 # is always recorded in the sqlite_sequence table.

    80 #

    81 do_test autoinc-2.1 {

    82   execsql {

    83     SELECT * FROM sqlite_sequence

    84   }

    85 } {}

    86 do_test autoinc-2.2 {

    87   execsql {

    88     INSERT INTO t1 VALUES(12,34);

    89     SELECT * FROM sqlite_sequence;

    90   }

    91 } {t1 12}

    92 do_test autoinc-2.3 {

    93   execsql {

    94     INSERT INTO t1 VALUES(1,23);

    95     SELECT * FROM sqlite_sequence;

    96   }

    97 } {t1 12}

    98 do_test autoinc-2.4 {

    99   execsql {

   100     INSERT INTO t1 VALUES(123,456);

   101     SELECT * FROM sqlite_sequence;

   102   }

   103 } {t1 123}

   104 do_test autoinc-2.5 {

   105   execsql {

   106     INSERT INTO t1 VALUES(NULL,567);

   107     SELECT * FROM sqlite_sequence;

   108   }

   109 } {t1 124}

   110 do_test autoinc-2.6 {

   111   execsql {

   112     DELETE FROM t1 WHERE y=567;

   113     SELECT * FROM sqlite_sequence;

   114   }

   115 } {t1 124}

   116 do_test autoinc-2.7 {

   117   execsql {

   118     INSERT INTO t1 VALUES(NULL,567);

   119     SELECT * FROM sqlite_sequence;

   120   }

   121 } {t1 125}

   122 do_test autoinc-2.8 {

   123   execsql {

   124     DELETE FROM t1;

   125     SELECT * FROM sqlite_sequence;

   126   }

   127 } {t1 125}

   128 do_test autoinc-2.9 {

   129   execsql {

   130     INSERT INTO t1 VALUES(12,34);

   131     SELECT * FROM sqlite_sequence;

   132   }

   133 } {t1 125}

   134 do_test autoinc-2.10 {

   135   execsql {

   136     INSERT INTO t1 VALUES(125,456);

   137     SELECT * FROM sqlite_sequence;

   138   }

   139 } {t1 125}

   140 do_test autoinc-2.11 {

   141   execsql {

   142     INSERT INTO t1 VALUES(-1234567,-1);

   143     SELECT * FROM sqlite_sequence;

   144   }

   145 } {t1 125}

   146 do_test autoinc-2.12 {

   147   execsql {

   148     INSERT INTO t1 VALUES(234,5678);

   149     SELECT * FROM sqlite_sequence;

   150   }

   151 } {t1 234}

   152 do_test autoinc-2.13 {

   153   execsql {

   154     DELETE FROM t1;

   155     INSERT INTO t1 VALUES(NULL,1);

   156     SELECT * FROM sqlite_sequence;

   157   }

   158 } {t1 235}

   159 do_test autoinc-2.14 {

   160   execsql {

   161     SELECT * FROM t1;

   162   }

   163 } {235 1}

   164 

   165 # Manually change the autoincrement values in sqlite_sequence.

   166 #

   167 do_test autoinc-2.20 {

   168   execsql {

   169     UPDATE sqlite_sequence SET seq=1234 WHERE name='t1';

   170     INSERT INTO t1 VALUES(NULL,2);

   171     SELECT * FROM t1;

   172   }

   173 } {235 1 1235 2}

   174 do_test autoinc-2.21 {

   175   execsql {

   176     SELECT * FROM sqlite_sequence;

   177   }

   178 } {t1 1235}

   179 do_test autoinc-2.22 {

   180   execsql {

   181     UPDATE sqlite_sequence SET seq=NULL WHERE name='t1';

   182     INSERT INTO t1 VALUES(NULL,3);

   183     SELECT * FROM t1;

   184   }

   185 } {235 1 1235 2 1236 3}

   186 do_test autoinc-2.23 {

   187   execsql {

   188     SELECT * FROM sqlite_sequence;

   189   }

   190 } {t1 1236}

   191 do_test autoinc-2.24 {

   192   execsql {

   193     UPDATE sqlite_sequence SET seq='a-string' WHERE name='t1';

   194     INSERT INTO t1 VALUES(NULL,4);

   195     SELECT * FROM t1;

   196   }

   197 } {235 1 1235 2 1236 3 1237 4}

   198 do_test autoinc-2.25 {

   199   execsql {

   200     SELECT * FROM sqlite_sequence;

   201   }

   202 } {t1 1237}

   203 do_test autoinc-2.26 {

   204   execsql {

   205     DELETE FROM sqlite_sequence WHERE name='t1';

   206     INSERT INTO t1 VALUES(NULL,5);

   207     SELECT * FROM t1;

   208   }

   209 } {235 1 1235 2 1236 3 1237 4 1238 5}

   210 do_test autoinc-2.27 {

   211   execsql {

   212     SELECT * FROM sqlite_sequence;

   213   }

   214 } {t1 1238}

   215 do_test autoinc-2.28 {

   216   execsql {

   217     UPDATE sqlite_sequence SET seq='12345678901234567890'

   218       WHERE name='t1';

   219     INSERT INTO t1 VALUES(NULL,6);

   220     SELECT * FROM t1;

   221   }

   222 } {235 1 1235 2 1236 3 1237 4 1238 5 1239 6}

   223 do_test autoinc-2.29 {

   224   execsql {

   225     SELECT * FROM sqlite_sequence;

   226   }

   227 } {t1 1239}

   228 

   229 # Test multi-row inserts

   230 #

   231 do_test autoinc-2.50 {

   232   execsql {

   233     DELETE FROM t1 WHERE y>=3;

   234     INSERT INTO t1 SELECT NULL, y+2 FROM t1;

   235     SELECT * FROM t1;

   236   }

   237 } {235 1 1235 2 1240 3 1241 4}

   238 do_test autoinc-2.51 {

   239   execsql {

   240     SELECT * FROM sqlite_sequence

   241   }

   242 } {t1 1241}

   243 

   244 ifcapable tempdb {

   245   do_test autoinc-2.52 {

   246     execsql {

   247       CREATE TEMP TABLE t2 AS SELECT y FROM t1;

   248     }

   249     execsql {

   250       INSERT INTO t1 SELECT NULL, y+4 FROM t2;

   251       SELECT * FROM t1;

   252     }

   253   } {235 1 1235 2 1240 3 1241 4 1242 5 1243 6 1244 7 1245 8}

   254   do_test autoinc-2.53 {

   255     execsql {

   256       SELECT * FROM sqlite_sequence

   257     }

   258   } {t1 1245}

   259   do_test autoinc-2.54 {

   260     execsql {

   261       DELETE FROM t1;

   262       INSERT INTO t1 SELECT NULL, y FROM t2;

   263       SELECT * FROM t1;

   264     }

   265   } {1246 1 1247 2 1248 3 1249 4}

   266   do_test autoinc-2.55 {

   267     execsql {

   268       SELECT * FROM sqlite_sequence

   269     }

   270   } {t1 1249}

   271 }

   272 

   273 # Create multiple AUTOINCREMENT tables.  Make sure all sequences are

   274 # tracked separately and do not interfere with one another.

   275 #

   276 do_test autoinc-2.70 {

   277   catchsql {

   278     DROP TABLE t2;

   279   }

   280   execsql {

   281     CREATE TABLE t2(d, e INTEGER PRIMARY KEY AUTOINCREMENT, f);

   282     INSERT INTO t2(d) VALUES(1);

   283     SELECT * FROM sqlite_sequence;

   284   }

   285 } [ifcapable tempdb {list t1 1249 t2 1} else {list t1 1241 t2 1}]

   286 do_test autoinc-2.71 {

   287   execsql {

   288     INSERT INTO t2(d) VALUES(2);

   289     SELECT * FROM sqlite_sequence;

   290   }

   291 } [ifcapable tempdb {list t1 1249 t2 2} else {list t1 1241 t2 2}]

   292 do_test autoinc-2.72 {

   293   execsql {

   294     INSERT INTO t1(x) VALUES(10000);

   295     SELECT * FROM sqlite_sequence;

   296   }

   297 } {t1 10000 t2 2}

   298 do_test autoinc-2.73 {

   299   execsql {

   300     CREATE TABLE t3(g INTEGER PRIMARY KEY AUTOINCREMENT, h);

   301     INSERT INTO t3(h) VALUES(1);

   302     SELECT * FROM sqlite_sequence;

   303   }

   304 } {t1 10000 t2 2 t3 1}

   305 do_test autoinc-2.74 {

   306   execsql {

   307     INSERT INTO t2(d,e) VALUES(3,100);

   308     SELECT * FROM sqlite_sequence;

   309   }

   310 } {t1 10000 t2 100 t3 1}

   311 

   312 

   313 # When a table with an AUTOINCREMENT is deleted, the corresponding entry

   314 # in the SQLITE_SEQUENCE table should also be deleted.  But the SQLITE_SEQUENCE

   315 # table itself should remain behind.

   316 #

   317 do_test autoinc-3.1 {

   318   execsql {SELECT name FROM sqlite_sequence}

   319 } {t1 t2 t3}

   320 do_test autoinc-3.2 {

   321   execsql {

   322     DROP TABLE t1;

   323     SELECT name FROM sqlite_sequence;

   324   }

   325 } {t2 t3}

   326 do_test autoinc-3.3 {

   327   execsql {

   328     DROP TABLE t3;

   329     SELECT name FROM sqlite_sequence;

   330   }

   331 } {t2}

   332 do_test autoinc-3.4 {

   333   execsql {

   334     DROP TABLE t2;

   335     SELECT name FROM sqlite_sequence;

   336   }

   337 } {}

   338 

   339 # AUTOINCREMENT on TEMP tables.

   340 #

   341 ifcapable tempdb {

   342   do_test autoinc-4.1 {

   343     execsql {

   344       SELECT 1, name FROM sqlite_master WHERE type='table';

   345       SELECT 2, name FROM sqlite_temp_master WHERE type='table';

   346     }

   347   } {1 sqlite_sequence}

   348   do_test autoinc-4.2 {

   349     execsql {

   350       CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y);

   351       CREATE TEMP TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b);

   352       SELECT 1, name FROM sqlite_master WHERE type='table';

   353       SELECT 2, name FROM sqlite_temp_master WHERE type='table';

   354     }

   355   } {1 sqlite_sequence 1 t1 2 t3 2 sqlite_sequence}

   356   do_test autoinc-4.3 {

   357     execsql {

   358       SELECT 1, * FROM main.sqlite_sequence;

   359       SELECT 2, * FROM temp.sqlite_sequence;

   360     }

   361   } {}

   362   do_test autoinc-4.4 {

   363     execsql {

   364       INSERT INTO t1 VALUES(10,1);

   365       INSERT INTO t3 VALUES(20,2);

   366       INSERT INTO t1 VALUES(NULL,3);

   367       INSERT INTO t3 VALUES(NULL,4);

   368     }

   369   } {}

   370   

   371   ifcapable compound {

   372   do_test autoinc-4.4.1 {

   373     execsql {

   374       SELECT * FROM t1 UNION ALL SELECT * FROM t3;

   375     }

   376   } {10 1 11 3 20 2 21 4}

   377   } ;# ifcapable compound

   378   

   379   do_test autoinc-4.5 {

   380     execsql {

   381       SELECT 1, * FROM main.sqlite_sequence;

   382       SELECT 2, * FROM temp.sqlite_sequence;

   383     }

   384   } {1 t1 11 2 t3 21}

   385   do_test autoinc-4.6 {

   386     execsql {

   387       INSERT INTO t1 SELECT * FROM t3;

   388       SELECT 1, * FROM main.sqlite_sequence;

   389       SELECT 2, * FROM temp.sqlite_sequence;

   390     }

   391   } {1 t1 21 2 t3 21}

   392   do_test autoinc-4.7 {

   393     execsql {

   394       INSERT INTO t3 SELECT x+100, y  FROM t1;

   395       SELECT 1, * FROM main.sqlite_sequence;

   396       SELECT 2, * FROM temp.sqlite_sequence;

   397     }

   398   } {1 t1 21 2 t3 121}

   399   do_test autoinc-4.8 {

   400     execsql {

   401       DROP TABLE t3;

   402       SELECT 1, * FROM main.sqlite_sequence;

   403       SELECT 2, * FROM temp.sqlite_sequence;

   404     }

   405   } {1 t1 21}

   406   do_test autoinc-4.9 {

   407     execsql {

   408       CREATE TEMP TABLE t2(p INTEGER PRIMARY KEY AUTOINCREMENT, q);

   409       INSERT INTO t2 SELECT * FROM t1;

   410       DROP TABLE t1;

   411       SELECT 1, * FROM main.sqlite_sequence;

   412       SELECT 2, * FROM temp.sqlite_sequence;

   413     }

   414   } {2 t2 21}

   415   do_test autoinc-4.10 {

   416     execsql {

   417       DROP TABLE t2;

   418       SELECT 1, * FROM main.sqlite_sequence;

   419       SELECT 2, * FROM temp.sqlite_sequence;

   420     }

   421   } {}

   422 }

   423 

   424 # Make sure AUTOINCREMENT works on ATTACH-ed tables.

   425 #

   426 ifcapable tempdb&&attach {

   427   do_test autoinc-5.1 {

   428     file delete -force test2.db

   429     file delete -force test2.db-journal

   430     sqlite3 db2 test2.db

   431     execsql {

   432       CREATE TABLE t4(m INTEGER PRIMARY KEY AUTOINCREMENT, n);

   433       CREATE TABLE t5(o, p INTEGER PRIMARY KEY AUTOINCREMENT);

   434     } db2;

   435     execsql {

   436       ATTACH 'test2.db' as aux;

   437       SELECT 1, * FROM main.sqlite_sequence;

   438       SELECT 2, * FROM temp.sqlite_sequence;

   439       SELECT 3, * FROM aux.sqlite_sequence;

   440     }

   441   } {}

   442   do_test autoinc-5.2 {

   443     execsql {

   444       INSERT INTO t4 VALUES(NULL,1);

   445       SELECT 1, * FROM main.sqlite_sequence;

   446       SELECT 2, * FROM temp.sqlite_sequence;

   447       SELECT 3, * FROM aux.sqlite_sequence;

   448     }

   449   } {3 t4 1}

   450   do_test autoinc-5.3 {

   451     execsql {

   452       INSERT INTO t5 VALUES(100,200);

   453       SELECT * FROM sqlite_sequence

   454     } db2

   455   } {t4 1 t5 200}

   456   do_test autoinc-5.4 {

   457     execsql {

   458       SELECT 1, * FROM main.sqlite_sequence;

   459       SELECT 2, * FROM temp.sqlite_sequence;

   460       SELECT 3, * FROM aux.sqlite_sequence;

   461     }

   462   } {3 t4 1 3 t5 200}

   463 }

   464 

   465 # Requirement REQ00310:  Make sure an insert fails if the sequence is

   466 # already at its maximum value.

   467 #

   468 ifcapable {rowid32} {

   469   do_test autoinc-6.1 {

   470     execsql {

   471       CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w);

   472       INSERT INTO t6 VALUES(2147483647,1);

   473       SELECT seq FROM main.sqlite_sequence WHERE name='t6';

   474     }

   475   } 2147483647

   476 }

   477 ifcapable {!rowid32} {

   478   do_test autoinc-6.1 {

   479     execsql {

   480       CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w);

   481       INSERT INTO t6 VALUES(9223372036854775807,1);

   482       SELECT seq FROM main.sqlite_sequence WHERE name='t6';

   483     }

   484   } 9223372036854775807

   485 }

   486 do_test autoinc-6.2 {

   487   catchsql {

   488     INSERT INTO t6 VALUES(NULL,1);

   489   }

   490 } {1 {database or disk is full}}

   491 

   492 # Allow the AUTOINCREMENT keyword inside the parentheses

   493 # on a separate PRIMARY KEY designation.

   494 #

   495 do_test autoinc-7.1 {

   496   execsql {

   497     CREATE TABLE t7(x INTEGER, y REAL, PRIMARY KEY(x AUTOINCREMENT));

   498     INSERT INTO t7(y) VALUES(123);

   499     INSERT INTO t7(y) VALUES(234);

   500     DELETE FROM t7;

   501     INSERT INTO t7(y) VALUES(345);

   502     SELECT * FROM t7;

   503   }

   504 } {3 345.0}

   505 

   506 # Test that if the AUTOINCREMENT is applied to a non integer primary key

   507 # the error message is sensible.

   508 do_test autoinc-7.2 {

   509   catchsql {

   510     CREATE TABLE t8(x TEXT PRIMARY KEY AUTOINCREMENT);

   511   }

   512 } {1 {AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY}}

   513 

   514 

   515 # Ticket #1283.  Make sure that preparing but never running a statement

   516 # that creates the sqlite_sequence table does not mess up the database.

   517 #

   518 do_test autoinc-8.1 {

   519   catch {db2 close}

   520   catch {db close}

   521   file delete -force test.db

   522   sqlite3 db test.db

   523   set DB [sqlite3_connection_pointer db]

   524   set STMT [sqlite3_prepare $DB {

   525      CREATE TABLE t1(

   526        x INTEGER PRIMARY KEY AUTOINCREMENT

   527      )

   528   } -1 TAIL]

   529   sqlite3_finalize $STMT

   530   set STMT [sqlite3_prepare $DB {

   531      CREATE TABLE t1(

   532        x INTEGER PRIMARY KEY AUTOINCREMENT

   533      )

   534   } -1 TAIL]

   535   sqlite3_step $STMT

   536   sqlite3_finalize $STMT

   537   execsql {

   538     INSERT INTO t1 VALUES(NULL);

   539     SELECT * FROM t1;

   540   }

   541 } {1}

   542 

   543 # Ticket #3148

   544 # Make sure the sqlite_sequence table is not damaged when doing

   545 # an empty insert - an INSERT INTO ... SELECT ... where the SELECT

   546 # clause returns an empty set.

   547 #

   548 do_test autoinc-9.1 {

   549   db eval {

   550     CREATE TABLE t2(x INTEGER PRIMARY KEY AUTOINCREMENT, y);

   551     INSERT INTO t2 VALUES(NULL, 1);

   552     CREATE TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b);

   553     INSERT INTO t3 SELECT * FROM t2 WHERE y>1;

   554 

   555     SELECT * FROM sqlite_sequence WHERE name='t3';

   556   }

   557 } {t3 0}

   558 

   559 

   560 finish_test