AryaWu/sqlite

1# 2025 September 182#3# The author disclaims copyright to this source code.  In place of4# 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#12 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15set testprefix altercons216 17# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.18ifcapable !altertable {19  finish_test20  return21}22 23foreach {tn newsql alter res final} {24  1 "CREATE TABLE t1(a, b"25    "ALTER TABLE t1 ALTER c SET NOT NULL"26    {1 {database disk image is malformed}}27    "CREATE TABLE t1(a, b"28 29  2 "CREATE TABLE t1(a, b, "30    "ALTER TABLE t1 ALTER c DROP NOT NULL"31    {0 {}}32    "CREATE TABLE t1(a, b, "33 34  3 "CREATE TABLE t1(a, b, CHECK( ..."35    "ALTER TABLE t1 ALTER c DROP NOT NULL"36    {0 {}}37    "CREATE TABLE t1(a, b, CHECK( ..."38 39  4 "CREATE TABLE t1(a, b, c NOT NULL"40    "ALTER TABLE t1 ALTER c DROP NOT NULL"41    {0 {}}42    "CREATE TABLE t1(a, b, c "43 44  5 "CREATE TABLE"45    "ALTER TABLE t1 ALTER c DROP NOT NULL"46    {1 {database disk image is malformed}}47    "CREATE TABLE"48 49  6 "CREATE TABLE"50    "ALTER TABLE t1 ADD CONSTRAINT nn CHECK (a!=0)"51    {1 {database disk image is malformed}}52    "CREATE TABLE"53 54} {55  reset_db56  do_execsql_test 1.$tn.0 {57    CREATE TABLE t1(a, b, c NOT NULL, CONSTRAINT xyz CHECK( a!=0 ));58  }59  do_execsql_test 1.$tn.1 {60    PRAGMA writable_schema = 1;61    UPDATE sqlite_schema SET sql = $::newsql62  }63  do_catchsql_test 1.$tn.2 $alter $res64 65  do_execsql_test 1.$tn.3 {66    SELECT sql FROM sqlite_schema WHERE name='t1'67  } [list $final]68}69 70#-------------------------------------------------------------------------71 72reset_db73proc xAuth {t args} {74  if {$t=="SQLITE_ALTER_TABLE"} {75    return "SQLITE_DENY"76  }77  return "SQLITE_OK"78}79sqlite3 db test.db80db auth xAuth81 82do_execsql_test 2.0 {83  CREATE TABLE x1(a PRIMARY KEY, b CHECK(a!=b) NOT NULL, c);84}85 86do_catchsql_test 2.1.1 {87  ALTER TABLE x1 ADD CONSTRAINT ccc CHECK (a!='a')88} {1 {not authorized}}89do_execsql_test 2.1.2 {90  SELECT sql FROM sqlite_schema WHERE name='x1'91} {{CREATE TABLE x1(a PRIMARY KEY, b CHECK(a!=b) NOT NULL, c)}}92 93do_catchsql_test 2.2.1 {94  ALTER TABLE x1 ALTER c SET NOT NULL95} {1 {not authorized}}96do_execsql_test 2.2.2 {97  SELECT sql FROM sqlite_schema WHERE name='x1'98} {{CREATE TABLE x1(a PRIMARY KEY, b CHECK(a!=b) NOT NULL, c)}}99 100#-------------------------------------------------------------------------101reset_db102do_execsql_test 3.0 {103  CREATE TABLE t1(x);104}105do_execsql_test 3.1 {106  ALTER TABLE t1 ALTER x SET NOT NULL;107}108 109#-------------------------------------------------------------------------110reset_db111do_execsql_test 4.0 {112  CREATE TABLE abc(a, b, c, CONSTRAINT one CONSTRAINT two CHECK (b!=c));113}114do_execsql_test 4.1 {115  ALTER TABLE abc DROP CONSTRAINT one116}117do_execsql_test 4.2 {118  SELECT sql FROM sqlite_schema119} {120  {CREATE TABLE abc(a, b, c, CONSTRAINT two CHECK (b!=c))}121}122 123#-------------------------------------------------------------------------124reset_db125 126# The columns must come before the table constraints in a CREATE TABLE127# statement. This is useful, as it means the DROP CONSTRAINT code does128# not have to handle the constraint immediately following the '(' at129# the start of the column-list.130do_catchsql_test 5.0 {131  CREATE TABLE abc(a, b, c, CONSTRAINT two CHECK (b!=c), d)132} {1 {near "d": syntax error}}133do_catchsql_test 5.1 {134  CREATE TABLE def(CONSTRAINT abc CHECK( b!=c ), a, b, c);135} {1 {near "CONSTRAINT": syntax error}}136 137#-------------------------------------------------------------------------138reset_db139do_execsql_test 6.0 {140  CREATE TABLE abc(a, b CONSTRAINT two COLLATE nocase CHECK (a!=b), c CONSTRAINT one DEFAULT 'abc');141}142 143do_execsql_test 6.1 {144  ALTER TABLE abc DROP CONSTRAINT one;145  ALTER TABLE abc DROP CONSTRAINT two;146}147 148do_execsql_test 6.2 {149  SELECT sql FROM sqlite_schema150} {151  {CREATE TABLE abc(a, b COLLATE nocase CHECK (a!=b), c DEFAULT 'abc')}152}153 154#-------------------------------------------------------------------------155reset_db156do_execsql_test 7.0 {157  CREATE TABLE abc(a, b, c, CONSTRAINT one CHECK (a>b) FOREIGN KEY(a) REFERENCES abc);158}159do_execsql_test 7.1 {160  ALTER TABLE abc DROP CONSTRAINT one161}162do_execsql_test 7.2 {163  SELECT sql FROM sqlite_schema164} {165  {CREATE TABLE abc(a, b, c, FOREIGN KEY(a) REFERENCES abc)}166}167 168#-------------------------------------------------------------------------169reset_db170do_execsql_test 8.0 {171  CREATE TABLE abc(a, b, c, CONSTRAINT one FOREIGN KEY(a) REFERENCES abc);172}173do_catchsql_test 8.1 {174  ALTER TABLE abc DROP CONSTRAINT one175} {1 {constraint may not be dropped: one}}176 177#-------------------------------------------------------------------------178reset_db179do_execsql_test 9.0 {180  CREATE TABLE abc(a, b NOT NULL AS (a+1))181}182do_execsql_test 9.1 {183  ALTER TABLE abc ALTER b DROP NOT NULL;184  SELECT sql FROM sqlite_schema;185} {{CREATE TABLE abc(a, b AS (a+1))}}186 187#-------------------------------------------------------------------------188reset_db189do_execsql_test 10.0 {190  CREATE TABLE abc(a, b GENERATED ALWAYS AS (a+1));191  INSERT INTO abc VALUES(1), (2);192  SELECT * FROM abc;193} {1 2   2 3}194 195do_execsql_test 10.1 {196  ALTER TABLE abc ALTER b SET NOT NULL;197}198do_catchsql_test 10.2 {199  INSERT INTO abc VALUES(NULL);200} {1 {NOT NULL constraint failed: abc.b}}201do_execsql_test 10.3 {202  INSERT INTO abc VALUES(3);203  ALTER TABLE abc ALTER COLUMN b DROP NOT NULL;204}205do_execsql_test 10.4 {206  INSERT INTO abc VALUES(NULL);207}208 209#-------------------------------------------------------------------------210reset_db211do_execsql_test 11.0 {212  CREATE TABLE t1(a, b, c);213}214 215do_execsql_test 11.1.1 {216  ALTER TABLE t1 ADD CONSTRAINT c1 CHECK(a=b) --comment217  ;218}219 220do_execsql_test 11.1.2 {ALTER TABLE t1 ADD CONSTRAINT c2 CHECK(a=b) --comment}221 222do_execsql_test 11.1.3 {223  SELECT sql FROM sqlite_schema;224} {225  {CREATE TABLE t1(a, b, c, CONSTRAINT c1 CHECK(a=b), CONSTRAINT c2 CHECK(a=b))}226}227 228do_execsql_test 11.2.1 {229  CREATE TABLE t2(a, b);230}231do_execsql_test 11.2.2 {ALTER TABLE t2 ALTER b SET NOT NULL --new cons}232do_execsql_test 11.2.3 {233  SELECT sql FROM sqlite_schema WHERE name='t2';234} {235  {CREATE TABLE t2(a, b NOT NULL)}236}237do_execsql_test 11.2.3 {238  ALTER TABLE t2 ALTER b DROP NOT NULL;239  SELECT sql FROM sqlite_schema WHERE name='t2';240} {241  {CREATE TABLE t2(a, b)}242}243do_execsql_test 11.2.2 {ALTER TABLE t2 ALTER b SET NOT NULL --new cons244;245}246finish_test247 248