test/drop-many.test

# 2024-02-29
#
# 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.
#
#***********************************************************************
#
# Test cases for DROP TABLE, DROP INDEX, DROP TRIGGER, and DROP VIEW that
# list multiple objects to be dropped.


set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix drop-many

do_execsql_test 1.1 {
  CREATE TABLE t1(a);
  CREATE TABLE t2(b, c UNIQUE);
  CREATE TABLE t3(d TEXT PRIMARY KEY, e);
  CREATE INDEX t3e ON t3(e);
  ATTACH ':memory:' AS aux1;
  CREATE TABLE aux1.t4(f INT, g INT, h INT PRIMARY KEY) WITHOUT ROWID;
  CREATE INDEX aux1.t4g ON t4(g);
  CREATE INDEX t2b ON t2(b);
  CREATE VIEW v5 AS SELECT 1,2,3;
  CREATE VIEW v6 AS SELECT * FROM t3;
  CREATE VIEW aux1.v7 AS SELECT 'hello';
  CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT 'this is trigger r1'; END;
  CREATE TRIGGER r2 BEFORE DELETE ON t2 BEGIN INSERT INTO t1 VALUES(old.b); END;
  CREATE TRIGGER aux1.r3 AFTER UPDATE ON t4 BEGIN SELECT 'trigger r3'; END;
  CREATE TRIGGER aux1.r4 INSTEAD OF UPDATE ON v7 BEGIN SELECT NULL; END;
} {}
do_execsql_test 1.2 {
  BEGIN;
  DROP TABLE t2, t4, t3, t1;
  SELECT name FROM sqlite_schema WHERE type='table'
  UNION ALL
  SELECT name from aux1.sqlite_schema WHERE type='table'
  ORDER BY name;
  ROLLBACK;
} {}
do_catchsql_test 1.3.1 {
  DROP TABLE t2, t4, t3, t05, t1;
} {1 {no such table: t05}}
do_execsql_test 1.3.2 {
  SELECT name FROM sqlite_schema WHERE type='table'
  UNION ALL
  SELECT name from aux1.sqlite_schema WHERE type='table'
  ORDER BY name;
} {t1 t2 t3 t4}
do_execsql_test 1.4 {
  BEGIN;
  DROP TABLE IF EXISTS t01, t2, t02, t4, t03, t3, t04, t1, t05;
  SELECT name FROM sqlite_schema WHERE type='table'
  UNION ALL
  SELECT name from aux1.sqlite_schema WHERE type='table'
  ORDER BY name;
  ROLLBACK;
} {}
do_catchsql_test 1.5.1 {
  DROP TABLE IF EXISTS t2, t4, t3, v7, t1;
} {1 {use DROP VIEW to delete view v7}}
do_execsql_test 1.5.2 {
  SELECT name FROM sqlite_schema WHERE type='table'
  UNION ALL
  SELECT name from aux1.sqlite_schema WHERE type='table'
  ORDER BY name;
} {t1 t2 t3 t4}


do_execsql_test 2.1 {
  BEGIN;
  DROP VIEW v5, v6, v7;
  SELECT name FROM sqlite_schema WHERE type='view'
  UNION ALL
  SELECT name from aux1.sqlite_schema WHERE type='view'
  ORDER BY name;
  ROLLBACK;
} {}
do_catchsql_test 2.2.1 {
  DROP VIEW v5, v6, v8, v7;
} {1 {no such view: v8}}
do_execsql_test 2.2.2 {
  SELECT name FROM sqlite_schema WHERE type='view'
  UNION ALL
  SELECT name from aux1.sqlite_schema WHERE type='view'
  ORDER BY name;
} {v5 v6 v7}
do_catchsql_test 2.3.1 {
  DROP VIEW v5, v6, t1, v7;
} {1 {use DROP TABLE to delete table t1}}
do_execsql_test 2.3.2 {
  SELECT name FROM sqlite_schema WHERE type='view'
  UNION ALL
  SELECT name from aux1.sqlite_schema WHERE type='view'
  ORDER BY name;
} {v5 v6 v7}


do_execsql_test 3.1 {
  BEGIN;
  DROP INDEX t2b, aux1.t4g, main.t3e;
  SELECT name FROM sqlite_schema WHERE type='index' AND name NOT LIKE 'sqlite%'
  UNION ALL
  SELECT name from aux1.sqlite_schema WHERE type='index'
  ORDER BY name;
  ROLLBACK;
} {}
do_catchsql_test 3.2.1 {
  DROP INDEX t2b, aux1.t4g, t1, main.t3e;
} {1 {no such index: t1}}
do_execsql_test 3.2.2 {
  SELECT name FROM sqlite_schema WHERE type='index' AND name NOT LIKE 'sqlite%'
  UNION ALL
  SELECT name from aux1.sqlite_schema WHERE type='index'
  ORDER BY name;
} {t2b t3e t4g}
do_execsql_test 3.3 {
  BEGIN;
  DROP INDEX IF EXISTS aux1.none, t2b, none2, aux1.t4g, main.t3e, none3;
  SELECT name FROM sqlite_schema WHERE type='index' AND name NOT LIKE 'sqlite%'
  UNION ALL
  SELECT name from aux1.sqlite_schema WHERE type='index'
  ORDER BY name;
  ROLLBACK;
} {}


do_execsql_test 4.1 {
  BEGIN;
  DROP TRIGGER main.r1, r2, r3, aux1.r4;
  SELECT name FROM sqlite_schema WHERE type='trigger'
  UNION ALL
  SELECT name from aux1.sqlite_schema WHERE type='trigger'
  ORDER BY name;
  ROLLBACK;
} {}
do_catchsql_test 4.2.1 {
  DROP TRIGGER main.r1, r2, r3, main.t1, aux1.r4;
} {1 {no such trigger: main.t1}}
do_execsql_test 4.2.2 {
  SELECT name FROM sqlite_schema WHERE type='trigger'
  UNION ALL
  SELECT name from aux1.sqlite_schema WHERE type='trigger'
  ORDER BY name;
} {r1 r2 r3 r4}
do_execsql_test 4.3 {
  BEGIN;
  DROP TRIGGER IF EXISTS none1, main.r1, r2, aux1.none2, r3, aux1.r4;
  SELECT name FROM sqlite_schema WHERE type='trigger'
  UNION ALL
  SELECT name from aux1.sqlite_schema WHERE type='trigger'
  ORDER BY name;
  ROLLBACK;
} {}
    
finish_test