alter.test

sqlite-3.4.1,嵌入式数据库.是一个功能强大的开源数据库,给学习和研发以及小型公司的发展带来了全所未有的好处.

    CREATE TRIGGER trig3 AFTER INSERT ON main.'t8'BEGIN
      SELECT trigfunc('trig3', new.a, new.b, new.c);
    END;
    INSERT INTO t8 VALUES(1, 2, 3);
  }
  set ::TRIGGER
} {trig3 1 2 3}
do_test alter-3.1.8 {
  execsql {
    ALTER TABLE t8 RENAME TO t9;
    INSERT INTO t9 VALUES(4, 5, 6);
  }
  set ::TRIGGER
} {trig3 4 5 6}

# Make sure "ON" cannot be used as a database, table or column name without
# quoting. Otherwise the sqlite_alter_trigger() function might not work.
file delete -force test3.db
file delete -force test3.db-journal
do_test alter-3.2.1 {
  catchsql {
    ATTACH 'test3.db' AS ON;
  }
} {1 {near "ON": syntax error}}
do_test alter-3.2.2 {
  catchsql {
    ATTACH 'test3.db' AS 'ON';
  }
} {0 {}}
do_test alter-3.2.3 {
  catchsql {
    CREATE TABLE ON.t1(a, b, c); 
  }
} {1 {near "ON": syntax error}}
do_test alter-3.2.4 {
  catchsql {
    CREATE TABLE 'ON'.t1(a, b, c); 
  }
} {0 {}}
do_test alter-3.2.4 {
  catchsql {
    CREATE TABLE 'ON'.ON(a, b, c); 
  }
} {1 {near "ON": syntax error}}
do_test alter-3.2.5 {
  catchsql {
    CREATE TABLE 'ON'.'ON'(a, b, c); 
  }
} {0 {}}
do_test alter-3.2.6 {
  catchsql {
    CREATE TABLE t10(a, ON, c);
  }
} {1 {near "ON": syntax error}}
do_test alter-3.2.7 {
  catchsql {
    CREATE TABLE t10(a, 'ON', c);
  }
} {0 {}}
do_test alter-3.2.8 {
  catchsql {
    CREATE TRIGGER trig4 AFTER INSERT ON ON BEGIN SELECT 1; END;
  }
} {1 {near "ON": syntax error}}
do_test alter-3.2.9 {
  catchsql {
    CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END;
  }
} {0 {}}
do_test alter-3.2.10 {
  execsql {
    DROP TABLE t10;
  }
} {}

do_test alter-3.3.1 {
  execsql [subst {
    CREATE TABLE tbl1(a, b, c);
    CREATE $::temp TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN
      SELECT trigfunc('trig1', new.a, new.b, new.c);
    END;
  }]
} {}
do_test alter-3.3.2 {
  execsql {
    INSERT INTO tbl1 VALUES('a', 'b', 'c');
  }
  set ::TRIGGER
} {trig1 a b c}
do_test alter-3.3.3 {
  execsql {
    ALTER TABLE tbl1 RENAME TO tbl2;
    INSERT INTO tbl2 VALUES('d', 'e', 'f');
  } 
  set ::TRIGGER
} {trig1 d e f}
do_test alter-3.3.4 {
  execsql [subst {
    CREATE $::temp TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN
      SELECT trigfunc('trig2', new.a, new.b, new.c);
    END;
  }] 
} {}
do_test alter-3.3.5 {
  execsql {
    ALTER TABLE tbl2 RENAME TO tbl3;
    INSERT INTO tbl3 VALUES('g', 'h', 'i');
  } 
  set ::TRIGGER
} {trig1 g h i}
do_test alter-3.3.6 {
  execsql {
    UPDATE tbl3 SET a = 'G' where a = 'g';
  } 
  set ::TRIGGER
} {trig2 G h i}
do_test alter-3.3.7 {
  execsql {
    DROP TABLE tbl3;
  }
} {}
ifcapable tempdb {
  do_test alter-3.3.8 {
    execsql {
      SELECT * FROM sqlite_temp_master WHERE type = 'trigger';
    }
  } {}
}

} ;# ifcapable trigger

# If the build does not include AUTOINCREMENT fields, omit alter-4.*.
ifcapable autoinc {

do_test alter-4.1 {
  execsql {
    CREATE TABLE tbl1(a INTEGER PRIMARY KEY AUTOINCREMENT);
    INSERT INTO tbl1 VALUES(10);
  }
} {}
do_test alter-4.2 {
  execsql {
    INSERT INTO tbl1 VALUES(NULL);
    SELECT a FROM tbl1;
  }
} {10 11}
do_test alter-4.3 {
  execsql {
    ALTER TABLE tbl1 RENAME TO tbl2;
    DELETE FROM tbl2;
    INSERT INTO tbl2 VALUES(NULL);
    SELECT a FROM tbl2;
  }
} {12}
do_test alter-4.4 {
  execsql {
    DROP TABLE tbl2;
  }
} {}

} ;# ifcapable autoinc

# Test that it is Ok to execute an ALTER TABLE immediately after
# opening a database.
do_test alter-5.1 {
  execsql {
    CREATE TABLE tbl1(a, b, c);
    INSERT INTO tbl1 VALUES('x', 'y', 'z');
  }
} {}
do_test alter-5.2 {
  sqlite3 db2 test.db
  execsql {
    ALTER TABLE tbl1 RENAME TO tbl2;
    SELECT * FROM tbl2;
  } db2
} {x y z}
do_test alter-5.3 {
  db2 close
} {}

foreach tblname [execsql {
  SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite%'
}] {
  execsql "DROP TABLE \"$tblname\""
}

set ::tbl_name "abc\uABCDdef"
do_test alter-6.1 {
  string length $::tbl_name
} {7}
do_test alter-6.2 {
  execsql "
    CREATE TABLE ${tbl_name}(a, b, c);
  "
  set ::oid [execsql {SELECT max(oid) FROM sqlite_master}]
  execsql "
    SELECT sql FROM sqlite_master WHERE oid = $::oid;
  "
} "{CREATE TABLE ${::tbl_name}(a, b, c)}"
execsql "
  SELECT * FROM ${::tbl_name}
"
set ::tbl_name2 "abcXdef"
do_test alter-6.3 {
  execsql "
    ALTER TABLE $::tbl_name RENAME TO $::tbl_name2 
  "
  execsql "
    SELECT sql FROM sqlite_master WHERE oid = $::oid
  "
} "{CREATE TABLE '${::tbl_name2}'(a, b, c)}"
do_test alter-6.4 {
  execsql "
    ALTER TABLE $::tbl_name2 RENAME TO $::tbl_name
  "
  execsql "
    SELECT sql FROM sqlite_master WHERE oid = $::oid
  "
} "{CREATE TABLE '${::tbl_name}'(a, b, c)}"
set ::col_name ghi\1234\jkl
do_test alter-6.5 {
  execsql "
    ALTER TABLE $::tbl_name ADD COLUMN $::col_name VARCHAR
  "
  execsql "
    SELECT sql FROM sqlite_master WHERE oid = $::oid
  "
} "{CREATE TABLE '${::tbl_name}'(a, b, c, $::col_name VARCHAR)}"
set ::col_name2 B\3421\A
do_test alter-6.6 {
  db close
  sqlite3 db test.db
  execsql "
    ALTER TABLE $::tbl_name ADD COLUMN $::col_name2
  "
  execsql "
    SELECT sql FROM sqlite_master WHERE oid = $::oid
  "
} "{CREATE TABLE '${::tbl_name}'(a, b, c, $::col_name VARCHAR, $::col_name2)}"
do_test alter-6.7 {
  execsql "
    INSERT INTO ${::tbl_name} VALUES(1, 2, 3, 4, 5);
    SELECT $::col_name, $::col_name2 FROM $::tbl_name;
  "
} {4 5}

# Ticket #1665:  Make sure ALTER TABLE ADD COLUMN works on a table
# that includes a COLLATE clause.
#
do_test alter-7.1 {
  execsql {
    CREATE TABLE t1(a TEXT COLLATE BINARY);
    ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
    INSERT INTO t1 VALUES(1,'-2');
    INSERT INTO t1 VALUES(5.4e-8,'5.4e-8');
    SELECT typeof(a), a, typeof(b), b FROM t1;
  }
} {text 1 integer -2 text 5.4e-8 real 5.4e-08}

# Make sure that when a column is added by ALTER TABLE ADD COLUMN and has
# a default value that the default value is used by aggregate functions.
#
do_test alter-8.1 {
  execsql {
    CREATE TABLE t2(a INTEGER);
    INSERT INTO t2 VALUES(1);
    INSERT INTO t2 VALUES(1);
    INSERT INTO t2 VALUES(2);
    ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
    SELECT sum(b) FROM t2;
  }
} {27}
do_test alter-8.2 {
  execsql {
    SELECT a, sum(b) FROM t2 GROUP BY a;
  }
} {1 18 2 9}

#--------------------------------------------------------------------------
# alter-9.X - Special test: Make sure the sqlite_rename_trigger() and
# rename_table() functions do not crash when handed bad input.
#
ifcapable trigger {
  do_test alter-9.1 {
    execsql {SELECT SQLITE_RENAME_TRIGGER(0,0)}
  } {{}}
}
do_test alter-9.2 {
  execsql {
    SELECT SQLITE_RENAME_TABLE(0,0);
    SELECT SQLITE_RENAME_TABLE(10,20);
    SELECT SQLITE_RENAME_TABLE("foo", "foo");
  }
} {{} {} {}}

#------------------------------------------------------------------------
# alter-10.X - Make sure ALTER TABLE works with multi-byte UTF-8 characters 
# in the names.
#
do_test alter-10.1 {
  execsql "CREATE TABLE xyz(x UNIQUE)"
  execsql "ALTER TABLE xyz RENAME TO xyz\u1234abc"
  execsql {SELECT name FROM sqlite_master WHERE name LIKE 'xyz%'}
} [list xyz\u1234abc]
do_test alter-10.2 {
  execsql {SELECT name FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%'}
} [list sqlite_autoindex_xyz\u1234abc_1]
do_test alter-10.3 {
  execsql "ALTER TABLE xyz\u1234abc RENAME TO xyzabc"
  execsql {SELECT name FROM sqlite_master WHERE name LIKE 'xyz%'}
} [list xyzabc]
do_test alter-10.4 {
  execsql {SELECT name FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%'}
} [list sqlite_autoindex_xyzabc_1]

do_test alter-11.1 {
  sqlite3_exec db {CREATE TABLE t11(%c6%c6)}
  execsql {
    ALTER TABLE t11 ADD COLUMN abc;
  }
  catchsql {
    ALTER TABLE t11 ADD COLUMN abc;
  }
} {1 {duplicate column name: abc}}
set isutf16 [regexp 16 [db one {PRAGMA encoding}]]
if {!$isutf16} {
  do_test alter-11.2 {
    execsql {INSERT INTO t11 VALUES(1,2)}
    sqlite3_exec db {SELECT %c6%c6 AS xyz, abc FROM t11}
  } {0 {xyz abc 1 2}}
}
do_test alter-11.3 {
  sqlite3_exec db {CREATE TABLE t11b("%81%82%83" text)}
  execsql {
    ALTER TABLE t11b ADD COLUMN abc;
  }
  catchsql {
    ALTER TABLE t11b ADD COLUMN abc;
  }
} {1 {duplicate column name: abc}}
if {!$isutf16} {
  do_test alter-11.4 {
    execsql {INSERT INTO t11b VALUES(3,4)}
    sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11b}
  } {0 {xyz abc 3 4}}
  do_test alter-11.5 {
    sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11b}
  } {0 {xyz abc 3 4}}
  do_test alter-11.6 {
    sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11b}
  } {0 {xyz abc 3 4}}
}
do_test alter-11.7 {
  sqlite3_exec db {CREATE TABLE t11c(%81%82%83 text)}
  execsql {
    ALTER TABLE t11c ADD COLUMN abc;
  }
  catchsql {
    ALTER TABLE t11c ADD COLUMN abc;
  }
} {1 {duplicate column name: abc}}
if {!$isutf16} {
  do_test alter-11.8 {
    execsql {INSERT INTO t11c VALUES(5,6)}
    sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11c}
  } {0 {xyz abc 5 6}}
  do_test alter-11.9 {
    sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11c}
  } {0 {xyz abc 5 6}}
  do_test alter-11.10 {
    sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11c}
  } {0 {xyz abc 5 6}}
}


finish_test