Download test/altercons2.test from AryaWu/sqlite: direct link, hf CLI and curl.
- Browser
- Download file 6.5 kB
-
https://huggingface.co/AryaWu/sqlite/resolve/main/test/altercons2.test
- Command line
-
hf download hf://AryaWu/sqlite/test/altercons2.test
-
curl -L -o altercons2.test https://huggingface.co/AryaWu/sqlite/resolve/main/test/altercons2.test
6.5 kB
| # 2025 September 18 | |
| # | |
| # 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. | |
| # | |
| #************************************************************************* | |
| # | |
| set testdir [file dirname $argv0] | |
| source $testdir/tester.tcl | |
| set testprefix altercons2 | |
| # If SQLITE_OMIT_ALTERTABLE is defined, omit this file. | |
| ifcapable !altertable { | |
| finish_test | |
| return | |
| } | |
| foreach {tn newsql alter res final} { | |
| 1 "CREATE TABLE t1(a, b" | |
| "ALTER TABLE t1 ALTER c SET NOT NULL" | |
| {1 {database disk image is malformed}} | |
| "CREATE TABLE t1(a, b" | |
| 2 "CREATE TABLE t1(a, b, " | |
| "ALTER TABLE t1 ALTER c DROP NOT NULL" | |
| {0 {}} | |
| "CREATE TABLE t1(a, b, " | |
| 3 "CREATE TABLE t1(a, b, CHECK( ..." | |
| "ALTER TABLE t1 ALTER c DROP NOT NULL" | |
| {0 {}} | |
| "CREATE TABLE t1(a, b, CHECK( ..." | |
| 4 "CREATE TABLE t1(a, b, c NOT NULL" | |
| "ALTER TABLE t1 ALTER c DROP NOT NULL" | |
| {0 {}} | |
| "CREATE TABLE t1(a, b, c " | |
| 5 "CREATE TABLE" | |
| "ALTER TABLE t1 ALTER c DROP NOT NULL" | |
| {1 {database disk image is malformed}} | |
| "CREATE TABLE" | |
| 6 "CREATE TABLE" | |
| "ALTER TABLE t1 ADD CONSTRAINT nn CHECK (a!=0)" | |
| {1 {database disk image is malformed}} | |
| "CREATE TABLE" | |
| } { | |
| reset_db | |
| do_execsql_test 1.$tn.0 { | |
| CREATE TABLE t1(a, b, c NOT NULL, CONSTRAINT xyz CHECK( a!=0 )); | |
| } | |
| do_execsql_test 1.$tn.1 { | |
| PRAGMA writable_schema = 1; | |
| UPDATE sqlite_schema SET sql = $::newsql | |
| } | |
| do_catchsql_test 1.$tn.2 $alter $res | |
| do_execsql_test 1.$tn.3 { | |
| SELECT sql FROM sqlite_schema WHERE name='t1' | |
| } [list $final] | |
| } | |
| #------------------------------------------------------------------------- | |
| reset_db | |
| proc xAuth {t args} { | |
| if {$t=="SQLITE_ALTER_TABLE"} { | |
| return "SQLITE_DENY" | |
| } | |
| return "SQLITE_OK" | |
| } | |
| sqlite3 db test.db | |
| db auth xAuth | |
| do_execsql_test 2.0 { | |
| CREATE TABLE x1(a PRIMARY KEY, b CHECK(a!=b) NOT NULL, c); | |
| } | |
| do_catchsql_test 2.1.1 { | |
| ALTER TABLE x1 ADD CONSTRAINT ccc CHECK (a!='a') | |
| } {1 {not authorized}} | |
| do_execsql_test 2.1.2 { | |
| SELECT sql FROM sqlite_schema WHERE name='x1' | |
| } {{CREATE TABLE x1(a PRIMARY KEY, b CHECK(a!=b) NOT NULL, c)}} | |
| do_catchsql_test 2.2.1 { | |
| ALTER TABLE x1 ALTER c SET NOT NULL | |
| } {1 {not authorized}} | |
| do_execsql_test 2.2.2 { | |
| SELECT sql FROM sqlite_schema WHERE name='x1' | |
| } {{CREATE TABLE x1(a PRIMARY KEY, b CHECK(a!=b) NOT NULL, c)}} | |
| #------------------------------------------------------------------------- | |
| reset_db | |
| do_execsql_test 3.0 { | |
| CREATE TABLE t1(x); | |
| } | |
| do_execsql_test 3.1 { | |
| ALTER TABLE t1 ALTER x SET NOT NULL; | |
| } | |
| #------------------------------------------------------------------------- | |
| reset_db | |
| do_execsql_test 4.0 { | |
| CREATE TABLE abc(a, b, c, CONSTRAINT one CONSTRAINT two CHECK (b!=c)); | |
| } | |
| do_execsql_test 4.1 { | |
| ALTER TABLE abc DROP CONSTRAINT one | |
| } | |
| do_execsql_test 4.2 { | |
| SELECT sql FROM sqlite_schema | |
| } { | |
| {CREATE TABLE abc(a, b, c, CONSTRAINT two CHECK (b!=c))} | |
| } | |
| #------------------------------------------------------------------------- | |
| reset_db | |
| # The columns must come before the table constraints in a CREATE TABLE | |
| # statement. This is useful, as it means the DROP CONSTRAINT code does | |
| # not have to handle the constraint immediately following the '(' at | |
| # the start of the column-list. | |
| do_catchsql_test 5.0 { | |
| CREATE TABLE abc(a, b, c, CONSTRAINT two CHECK (b!=c), d) | |
| } {1 {near "d": syntax error}} | |
| do_catchsql_test 5.1 { | |
| CREATE TABLE def(CONSTRAINT abc CHECK( b!=c ), a, b, c); | |
| } {1 {near "CONSTRAINT": syntax error}} | |
| #------------------------------------------------------------------------- | |
| reset_db | |
| do_execsql_test 6.0 { | |
| CREATE TABLE abc(a, b CONSTRAINT two COLLATE nocase CHECK (a!=b), c CONSTRAINT one DEFAULT 'abc'); | |
| } | |
| do_execsql_test 6.1 { | |
| ALTER TABLE abc DROP CONSTRAINT one; | |
| ALTER TABLE abc DROP CONSTRAINT two; | |
| } | |
| do_execsql_test 6.2 { | |
| SELECT sql FROM sqlite_schema | |
| } { | |
| {CREATE TABLE abc(a, b COLLATE nocase CHECK (a!=b), c DEFAULT 'abc')} | |
| } | |
| #------------------------------------------------------------------------- | |
| reset_db | |
| do_execsql_test 7.0 { | |
| CREATE TABLE abc(a, b, c, CONSTRAINT one CHECK (a>b) FOREIGN KEY(a) REFERENCES abc); | |
| } | |
| do_execsql_test 7.1 { | |
| ALTER TABLE abc DROP CONSTRAINT one | |
| } | |
| do_execsql_test 7.2 { | |
| SELECT sql FROM sqlite_schema | |
| } { | |
| {CREATE TABLE abc(a, b, c, FOREIGN KEY(a) REFERENCES abc)} | |
| } | |
| #------------------------------------------------------------------------- | |
| reset_db | |
| do_execsql_test 8.0 { | |
| CREATE TABLE abc(a, b, c, CONSTRAINT one FOREIGN KEY(a) REFERENCES abc); | |
| } | |
| do_catchsql_test 8.1 { | |
| ALTER TABLE abc DROP CONSTRAINT one | |
| } {1 {constraint may not be dropped: one}} | |
| #------------------------------------------------------------------------- | |
| reset_db | |
| do_execsql_test 9.0 { | |
| CREATE TABLE abc(a, b NOT NULL AS (a+1)) | |
| } | |
| do_execsql_test 9.1 { | |
| ALTER TABLE abc ALTER b DROP NOT NULL; | |
| SELECT sql FROM sqlite_schema; | |
| } {{CREATE TABLE abc(a, b AS (a+1))}} | |
| #------------------------------------------------------------------------- | |
| reset_db | |
| do_execsql_test 10.0 { | |
| CREATE TABLE abc(a, b GENERATED ALWAYS AS (a+1)); | |
| INSERT INTO abc VALUES(1), (2); | |
| SELECT * FROM abc; | |
| } {1 2 2 3} | |
| do_execsql_test 10.1 { | |
| ALTER TABLE abc ALTER b SET NOT NULL; | |
| } | |
| do_catchsql_test 10.2 { | |
| INSERT INTO abc VALUES(NULL); | |
| } {1 {NOT NULL constraint failed: abc.b}} | |
| do_execsql_test 10.3 { | |
| INSERT INTO abc VALUES(3); | |
| ALTER TABLE abc ALTER COLUMN b DROP NOT NULL; | |
| } | |
| do_execsql_test 10.4 { | |
| INSERT INTO abc VALUES(NULL); | |
| } | |
| #------------------------------------------------------------------------- | |
| reset_db | |
| do_execsql_test 11.0 { | |
| CREATE TABLE t1(a, b, c); | |
| } | |
| do_execsql_test 11.1.1 { | |
| ALTER TABLE t1 ADD CONSTRAINT c1 CHECK(a=b) --comment | |
| ; | |
| } | |
| do_execsql_test 11.1.2 {ALTER TABLE t1 ADD CONSTRAINT c2 CHECK(a=b) --comment} | |
| do_execsql_test 11.1.3 { | |
| SELECT sql FROM sqlite_schema; | |
| } { | |
| {CREATE TABLE t1(a, b, c, CONSTRAINT c1 CHECK(a=b), CONSTRAINT c2 CHECK(a=b))} | |
| } | |
| do_execsql_test 11.2.1 { | |
| CREATE TABLE t2(a, b); | |
| } | |
| do_execsql_test 11.2.2 {ALTER TABLE t2 ALTER b SET NOT NULL --new cons} | |
| do_execsql_test 11.2.3 { | |
| SELECT sql FROM sqlite_schema WHERE name='t2'; | |
| } { | |
| {CREATE TABLE t2(a, b NOT NULL)} | |
| } | |
| do_execsql_test 11.2.3 { | |
| ALTER TABLE t2 ALTER b DROP NOT NULL; | |
| SELECT sql FROM sqlite_schema WHERE name='t2'; | |
| } { | |
| {CREATE TABLE t2(a, b)} | |
| } | |
| do_execsql_test 11.2.2 {ALTER TABLE t2 ALTER b SET NOT NULL --new cons | |
| ; | |
| } | |
| finish_test | |