1 files changed, 443 insertions, 0 deletions
diff --git a/test/alter3.test b/test/alter3.test
new file mode 100644
index 0000000..c6f26b0
--- /dev/null
+++ b/test/alter3.test
@@ -0,0 +1,443 @@
+# 2005 February 19
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    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.
+#
+#*************************************************************************
+# This file implements regression tests for SQLite library.  The
+# focus of this script is testing that SQLite can handle a subtle 
+# file format change that may be used in the future to implement
+# "ALTER TABLE ... ADD COLUMN".
+#
+
+set testdir [file dirname $argv0]
+
+source $testdir/tester.tcl
+
+# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
+ifcapable !altertable {
+  finish_test
+  return
+}
+
+# Determine if there is a codec available on this test.
+#
+if {[catch {sqlite3 -has-codec} r] || $r} {
+  set has_codec 1
+} else {
+  set has_codec 0
+}
+
+
+# Test Organisation:
+# ------------------
+#
+# alter3-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql.
+# alter3-2.*: Test error messages.
+# alter3-3.*: Test adding columns with default value NULL.
+# alter3-4.*: Test adding columns with default values other than NULL.
+# alter3-5.*: Test adding columns to tables in ATTACHed databases.
+# alter3-6.*: Test that temp triggers are not accidentally dropped.
+# alter3-7.*: Test that VACUUM resets the file-format.
+#
+
+# This procedure returns the value of the file-format in file 'test.db'.
+# 
+proc get_file_format {{fname test.db}} {
+  return [hexio_get_int [hexio_read $fname 44 4]]
+}
+
+do_test alter3-1.1 {
+  sqlite3_db_config db LEGACY_FILE_FORMAT 1
+  execsql {
+    CREATE TABLE abc(a, b, c);
+    SELECT sql FROM sqlite_master;
+  }
+} {{CREATE TABLE abc(a, b, c)}}
+do_test alter3-1.2 {
+  execsql {ALTER TABLE abc ADD d INTEGER;}
+  execsql {
+    SELECT sql FROM sqlite_master;
+  }
+} {{CREATE TABLE abc(a, b, c, d INTEGER)}}
+do_test alter3-1.3 {
+  execsql {ALTER TABLE abc ADD e}
+  execsql {
+    SELECT sql FROM sqlite_master;
+  }
+} {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}
+do_test alter3-1.4 {
+  execsql {
+    CREATE TABLE main.t1(a, b);
+    ALTER TABLE t1 ADD c;
+    SELECT sql FROM sqlite_master WHERE tbl_name = 't1';
+  }
+} {{CREATE TABLE t1(a, b, c)}}
+do_test alter3-1.5 {
+  execsql {
+    ALTER TABLE t1 ADD d CHECK (a>d);
+    SELECT sql FROM sqlite_master WHERE tbl_name = 't1';
+  }
+} {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}
+ifcapable foreignkey {
+  do_test alter3-1.6 {
+    execsql {
+      CREATE TABLE t2(a, b, UNIQUE(a, b));
+      ALTER TABLE t2 ADD c REFERENCES t1(c)  ;
+      SELECT sql FROM sqlite_master WHERE tbl_name = 't2' AND type = 'table';
+    }
+  } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}
+}
+do_test alter3-1.7 {
+  execsql {
+    CREATE TABLE t3(a, b, UNIQUE(a, b));
+    ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);
+    SELECT sql FROM sqlite_master WHERE tbl_name = 't3' AND type = 'table';
+  }
+} {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}
+do_test alter3-1.99 {
+  catchsql {
+    # May not exist if foriegn-keys are omitted at compile time.
+    DROP TABLE t2; 
+  }
+  execsql {
+    DROP TABLE abc; 
+    DROP TABLE t1; 
+    DROP TABLE t3; 
+  }
+} {}
+
+do_test alter3-2.1 {
+  execsql {
+    CREATE TABLE t1(a, b);
+    INSERT INTO t1 VALUES(1,2);
+  }
+  catchsql {
+    ALTER TABLE t1 ADD c PRIMARY KEY;
+  }
+} {1 {Cannot add a PRIMARY KEY column}}
+do_test alter3-2.2 {
+  catchsql {
+    ALTER TABLE t1 ADD c UNIQUE
+  }
+} {1 {Cannot add a UNIQUE column}}
+do_test alter3-2.3 {
+  catchsql {
+    ALTER TABLE t1 ADD b VARCHAR(10)
+  }
+} {1 {duplicate column name: b}}
+do_test alter3-2.3 {
+  catchsql {
+    ALTER TABLE t1 ADD c NOT NULL;
+  }
+} {1 {Cannot add a NOT NULL column with default value NULL}}
+do_test alter3-2.4 {
+  catchsql {
+    ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
+  }
+} {0 {}}
+ifcapable view {
+  do_test alter3-2.5 {
+    execsql {
+      CREATE VIEW v1 AS SELECT * FROM t1;
+    }
+    catchsql {
+      alter table v1 add column d;
+    }
+  } {1 {Cannot add a column to a view}}
+}
+do_test alter3-2.6 {
+  catchsql {
+    alter table t1 add column d DEFAULT CURRENT_TIME;
+  }
+} {1 {Cannot add a column with non-constant default}}
+do_test alter3-2.99 {
+  execsql {
+    DROP TABLE t1;
+  }
+} {}
+
+do_test alter3-3.1 {
+  execsql {
+    CREATE TABLE t1(a, b);
+    INSERT INTO t1 VALUES(1, 100);
+    INSERT INTO t1 VALUES(2, 300);
+    SELECT * FROM t1;
+  }
+} {1 100 2 300}
+do_test alter3-3.1 {
+  execsql {
+    PRAGMA schema_version = 10;
+  }
+} {}
+do_test alter3-3.2 {
+  execsql {
+    ALTER TABLE t1 ADD c;
+    SELECT * FROM t1;
+  }
+} {1 100 {} 2 300 {}}
+if {!$has_codec} {
+  do_test alter3-3.3 {
+    get_file_format
+  } {3}
+}
+ifcapable schema_version {
+  do_test alter3-3.4 {
+    execsql {
+      PRAGMA schema_version;
+    }
+  } {11}
+}
+
+do_test alter3-4.1 {
+  db close
+  forcedelete test.db
+  set ::DB [sqlite3 db test.db]
+  sqlite3_db_config db LEGACY_FILE_FORMAT 1
+  execsql {
+    CREATE TABLE t1(a, b);
+    INSERT INTO t1 VALUES(1, 100);
+    INSERT INTO t1 VALUES(2, 300);
+    SELECT * FROM t1;
+  }
+} {1 100 2 300}
+do_test alter3-4.1 {
+  execsql {
+    PRAGMA schema_version = 20;
+  }
+} {}
+do_test alter3-4.2 {
+  execsql {
+    ALTER TABLE t1 ADD c DEFAULT 'hello world';
+    SELECT * FROM t1;
+  }
+} {1 100 {hello world} 2 300 {hello world}}
+if {!$has_codec} {
+  do_test alter3-4.3 {
+    get_file_format
+  } {3}
+}
+ifcapable schema_version {
+  do_test alter3-4.4 {
+    execsql {
+      PRAGMA schema_version;
+    }
+  } {21}
+}
+do_test alter3-4.99 {
+  execsql {
+    DROP TABLE t1;
+  }
+} {}
+
+ifcapable attach {
+  do_test alter3-5.1 {
+    forcedelete test2.db
+    forcedelete test2.db-journal
+    execsql {
+      CREATE TABLE t1(a, b);
+      INSERT INTO t1 VALUES(1, 'one');
+      INSERT INTO t1 VALUES(2, 'two');
+      ATTACH 'test2.db' AS aux;
+      CREATE TABLE aux.t1 AS SELECT * FROM t1;
+      PRAGMA aux.schema_version = 30;
+      SELECT sql FROM aux.sqlite_master;
+    } 
+  } {{CREATE TABLE t1(a,b)}}
+  do_test alter3-5.2 {
+    execsql {
+      ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);
+      SELECT sql FROM aux.sqlite_master;
+    }
+  } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}
+  do_test alter3-5.3 {
+    execsql {
+      SELECT * FROM aux.t1;
+    }
+  } {1 one {} 2 two {}}
+  ifcapable schema_version {
+    do_test alter3-5.4 {
+      execsql {
+        PRAGMA aux.schema_version;
+      }
+    } {31}
+  }
+  if {!$has_codec} {
+    do_test alter3-5.5 {
+      list [get_file_format test2.db] [get_file_format]
+    } {3 3}
+  }
+  do_test alter3-5.6 {
+    execsql {
+      ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
+      SELECT sql FROM aux.sqlite_master;
+    }
+  } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
+  do_test alter3-5.7 {
+    execsql {
+      SELECT * FROM aux.t1;
+    }
+  } {1 one {} 1000 2 two {} 1000}
+  ifcapable schema_version {
+    do_test alter3-5.8 {
+      execsql {
+        PRAGMA aux.schema_version;
+      }
+    } {32}
+  }
+  do_test alter3-5.9 {
+    execsql {
+      SELECT * FROM t1;
+    }
+  } {1 one 2 two}
+  do_test alter3-5.99 {
+    execsql {
+      DROP TABLE aux.t1;
+      DROP TABLE t1;
+    }
+  } {}
+}
+
+#----------------------------------------------------------------
+# Test that the table schema is correctly reloaded when a column
+# is added to a table.
+#
+ifcapable trigger&&tempdb {
+  do_test alter3-6.1 {
+    execsql {
+      CREATE TABLE t1(a, b);
+      CREATE TABLE log(trig, a, b);
+
+      CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN
+        INSERT INTO log VALUES('a', new.a, new.b);
+      END;
+      CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN
+        INSERT INTO log VALUES('b', new.a, new.b);
+      END;
+  
+      INSERT INTO t1 VALUES(1, 2);
+      SELECT * FROM log;
+    }
+  } {b 1 2 a 1 2}
+  do_test alter3-6.2 {
+    execsql {
+      ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';
+      INSERT INTO t1(a, b) VALUES(3, 4);
+      SELECT * FROM log;
+    }
+  } {b 1 2 a 1 2 b 3 4 a 3 4}
+}
+
+if {!$has_codec} {
+  ifcapable vacuum {
+    do_test alter3-7.1 {
+      execsql {
+        VACUUM;
+      }
+      get_file_format
+    } {1}
+    do_test alter3-7.2 {
+      execsql {
+        CREATE TABLE abc(a, b, c);
+        ALTER TABLE abc ADD d DEFAULT NULL;
+      }
+      get_file_format
+    } {3}
+    do_test alter3-7.3 {
+      execsql {
+        ALTER TABLE abc ADD e DEFAULT 10;
+      }
+      get_file_format
+    } {3}
+    do_test alter3-7.4 {
+      execsql {
+        ALTER TABLE abc ADD f DEFAULT NULL;
+      }
+      get_file_format
+    } {3}
+    do_test alter3-7.5 {
+      execsql {
+        VACUUM;
+      }
+      get_file_format
+    } {1}
+  }
+}
+
+# Ticket #1183 - Make sure adding columns to large tables does not cause
+# memory corruption (as was the case before this bug was fixed).
+do_test alter3-8.1 {
+  execsql {
+    CREATE TABLE t4(c1);
+  }
+} {}
+set ::sql ""
+do_test alter3-8.2 {
+  set cols c1
+  for {set i 2} {$i < 100} {incr i} {
+    execsql "
+      ALTER TABLE t4 ADD c$i
+    "
+    lappend cols c$i
+  }
+  set ::sql "CREATE TABLE t4([join $cols {, }])"
+  list 
+} {}
+do_test alter3-8.2 {
+  execsql {
+    SELECT sql FROM sqlite_master WHERE name = 't4';
+  }
+} [list $::sql]
+
+# 2021-07-20:  Add support for detecting CHECK and NOT NULL constraint
+# violations in ALTER TABLE ADD COLUMN
+#
+reset_db
+do_execsql_test alter3-9.1 {
+  CREATE TABLE t1(a,b);
+  INSERT INTO t1 VALUES(1, 2), ('null!',NULL), (3,4);
+} {}
+do_catchsql_test alter3-9.2 {
+  ALTER TABLE t1 ADD COLUMN c CHECK(a!=1);
+} {1 {CHECK constraint failed}}
+do_catchsql_test alter3-9.3 {
+  ALTER TABLE t1 ADD COLUMN c CHECK(a!=3);
+} {1 {CHECK constraint failed}}
+do_catchsql_test alter3-9.4 {
+  ALTER TABLE t1 ADD COLUMN c CHECK(a!=2);
+} {0 {}}
+do_catchsql_test alter3-9.5 {
+  ALTER TABLE t1 ADD COLUMN d AS (b+1) NOT NULL;
+} {1 {NOT NULL constraint failed}}
+do_catchsql_test alter3-9.6 {
+  ALTER TABLE t1 ADD COLUMN d AS (b+1) NOT NULL CHECK(a!=1);
+} {1 {CHECK constraint failed}}
+do_catchsql_test alter3-9.7 {
+  ALTER TABLE t1 ADD COLUMN d AS (b+1) NOT NULL CHECK(a!=3);
+} {1 {NOT NULL constraint failed}}
+
+do_execsql_test alter3-9.10 {
+  CREATE TEMP TABLE t0(m,n);
+  INSERT INTO t0 VALUES(1, 2), ('null!',NULL), (3,4);
+  ATTACH ':memory:' AS aux1;
+  CREATE TABLE aux1.t2(x,y);
+  INSERT INTO t2 VALUES(1, 2), ('null!',NULL), (3,4);
+} {}
+do_catchsql_test alter3-9.11 {
+  ALTER TABLE t0 ADD COLUMN xtra1 AS (n+1) NOT NULL CHECK(m!=1);
+} {1 {CHECK constraint failed}}
+do_catchsql_test alter3-9.12 {
+  ALTER TABLE t0 ADD COLUMN xtra1 AS (n+1) NOT NULL CHECK(m!=3);
+} {1 {NOT NULL constraint failed}}
+do_catchsql_test alter3-9.13 {
+  ALTER TABLE t2 ADD COLUMN xtra1 AS (y+1) NOT NULL CHECK(x!=1);
+} {1 {CHECK constraint failed}}
+do_catchsql_test alter3-9.14 {
+  ALTER TABLE t2 ADD COLUMN xtra1 AS (y+1) NOT NULL CHECK(x!=3);
+} {1 {NOT NULL constraint failed}}
+
+finish_test