/ Artifact Content
Login

Artifact 45d30922605898392d1619265b8a157a43ba010d:


#
# The author or author's hereby grant to the public domain a non-exclusive,
# fully paid-up, perpetual, license in the software and all related
# intellectual property to make, have made, use, have used, reproduce,
# prepare derivative works, distribute, perform and display the work.  
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ALTER TABLE statement.
#
# $Id: alter.test,v 1.3 2004/11/18 15:44:30 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
ifcapable !altertable {
  finish_test
  return
}

# Create some tables to rename.  Be sure to include some TEMP tables
# and some tables with odd names.
#
do_test alter-1.1 {
  execsql {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,2);
    CREATE TABLE [t1'x1](c UNIQUE, b PRIMARY KEY);
    INSERT INTO [t1'x1] VALUES(3,4);
    CREATE INDEX t1i1 ON T1(B);
    CREATE INDEX t1i2 ON t1(a,b);
    CREATE INDEX i3 ON [t1'x1](b,c);
    CREATE TEMP TABLE "temp table"(e,f,g UNIQUE);
    CREATE INDEX i2 ON [temp table](f);
    INSERT INTO [temp table] VALUES(5,6,7);
  }
  execsql {
    SELECT 't1', * FROM t1
    UNION ALL
    SELECT 't1''x1', * FROM "t1'x1"
    UNION ALL
    SELECT * FROM [temp table]
  }
} {t1 1 2 t1'x1 3 4 5 6 7}
do_test alter-1.2 {
  execsql {
    SELECT type, name, tbl_name FROM sqlite_master
    UNION ALL
    SELECT type, name, tbl_name FROM sqlite_temp_master
    ORDER BY tbl_name, type desc, name
  }
} [list \
     table t1                              t1             \
     index t1i1                            t1             \
     index t1i2                            t1             \
     table t1'x1                           t1'x1          \
     index i3                              t1'x1          \
     index {sqlite_autoindex_t1'x1_1}      t1'x1          \
     index {sqlite_autoindex_t1'x1_2}      t1'x1          \
     table {temp table}                    {temp table}   \
     index i2                              {temp table}   \
     index {sqlite_autoindex_temp table_1} {temp table}   \
  ]

# Make some changes
#
do_test alter-1.3 {
  execsql {
    ALTER TABLE [T1] RENAME to [-t1-];
    ALTER TABLE "t1'x1" RENAME TO T2;
    ALTER TABLE [temp table] RENAME to TempTab;
  }
} {}
integrity_check alter-1.3.1
do_test alter-1.4 {
  execsql {
    SELECT 't1', * FROM [-t1-]
    UNION ALL
    SELECT 't2', * FROM t2
    UNION ALL
    SELECT * FROM temptab
  }
} {t1 1 2 t2 3 4 5 6 7}
do_test alter-1.5 {
  execsql {
    SELECT type, name, tbl_name FROM sqlite_master
    UNION ALL
    SELECT type, name, tbl_name FROM sqlite_temp_master
    ORDER BY tbl_name, type desc, name
  }
} [list \
     table -t1-                         -t1-        \
     index t1i1                         -t1-        \
     index t1i2                         -t1-        \
     table T2                           T2          \
     index i3                           T2          \
     index {sqlite_autoindex_T2_1}      T2          \
     index {sqlite_autoindex_T2_2}      T2          \
     table {TempTab}                    {TempTab}   \
     index i2                           {TempTab}   \
     index {sqlite_autoindex_TempTab_1} {TempTab}   \
  ]

# Make sure the changes persist after restarting the database.
# (The TEMP table will not persist, of course.)
#
do_test alter-1.6 {
  db close
  set DB [sqlite3 db test.db]
  execsql {
    SELECT type, name, tbl_name FROM sqlite_master
    UNION ALL
    SELECT type, name, tbl_name FROM sqlite_temp_master
    ORDER BY tbl_name, type desc, name
  }
} [list \
     table -t1-                         -t1-           \
     index t1i1                         -t1-           \
     index t1i2                         -t1-           \
     table T2                           T2          \
     index i3                           T2          \
     index {sqlite_autoindex_T2_1}      T2          \
     index {sqlite_autoindex_T2_2}      T2          \
  ]

# Make sure the ALTER TABLE statements work with the
# non-callback API
#
do_test alter-1.7 {
  stepsql $DB {
    ALTER TABLE [-t1-] RENAME to [*t1*];
    ALTER TABLE T2 RENAME TO [<t2>];
  }
  execsql {
    SELECT type, name, tbl_name FROM sqlite_master
    UNION ALL
    SELECT type, name, tbl_name FROM sqlite_temp_master
    ORDER BY tbl_name, type desc, name
  }
} [list \
     table *t1*                         *t1*           \
     index t1i1                         *t1*           \
     index t1i2                         *t1*           \
     table <t2>                         <t2>          \
     index i3                           <t2>          \
     index {sqlite_autoindex_<t2>_1}    <t2>          \
     index {sqlite_autoindex_<t2>_2}    <t2>          \
  ]

# Check that ALTER TABLE works on attached databases.
#
do_test alter-1.8.1 {
  file delete -force test2.db
  file delete -force test2.db-journal
  execsql {
    ATTACH 'test2.db' AS aux;
  }
} {}
do_test alter-1.8.2 {
  execsql {
    CREATE TABLE t4(a PRIMARY KEY, b, c);
    CREATE TABLE aux.t4(a PRIMARY KEY, b, c);
    CREATE INDEX i4 ON t4(b);
    CREATE INDEX aux.i4 ON aux.t4(b);
  }
} {}
do_test alter-1.8.3 {
  execsql {
    INSERT INTO t4 VALUES('main', 'main', 'main');
    INSERT INTO aux.t4 VALUES('aux', 'aux', 'aux');
    SELECT * FROM t4 WHERE a = 'main';
  }
} {main main main}
do_test alter-1.8.4 {
  execsql {
    ALTER TABLE t4 RENAME TO t5;
    SELECT * FROM t4 WHERE a = 'aux';
  }
} {aux aux aux}
do_test alter-1.8.5 {
  execsql {
    SELECT * FROM t5;
  }
} {main main main}
do_test alter-1.8.6 {
  execsql {
    SELECT * FROM t5 WHERE b = 'main';
  }
} {main main main}
do_test alter-1.8.7 {
  execsql {
    ALTER TABLE aux.t4 RENAME TO t5;
    SELECT * FROM aux.t5 WHERE b = 'aux';
  }
} {aux aux aux}

# Test error messages
#
do_test alter-2.1 {
  catchsql {
    ALTER TABLE none RENAME TO hi;
  }
} {1 {no such table: none}}
do_test alter-2.2 {
  execsql {
    CREATE TABLE t3(p,q,r);
  }
  catchsql {
    ALTER TABLE [<t2>] RENAME TO t3;
  }
} {1 {there is already another table or index with this name: t3}}
do_test alter-2.3 {
  catchsql {
    ALTER TABLE [<t2>] RENAME TO i3;
  }
} {1 {there is already another table or index with this name: i3}}

# If this compilation does not include triggers, omit the remainder
# of this file.
ifcapable !trigger {
  finish_test
  return
}

# An SQL user-function for triggers to fire, so that we know they
# are working.
proc trigfunc {args} {
  set ::TRIGGER $args
}
db func trigfunc trigfunc

do_test alter-3.1.0 {
  execsql {
    CREATE TABLE t6(a, b, c);
    CREATE TRIGGER trig1 AFTER INSERT ON t6 BEGIN
      SELECT trigfunc('trig1', new.a, new.b, new.c);
    END;
  }
} {}
do_test alter-3.1.1 {
  execsql {
    INSERT INTO t6 VALUES(1, 2, 3);
  }
  set ::TRIGGER
} {trig1 1 2 3}
do_test alter-3.1.2 {
  execsql {
    ALTER TABLE t6 RENAME TO t7;
    INSERT INTO t7 VALUES(4, 5, 6);
  }
  set ::TRIGGER
} {trig1 4 5 6}
do_test alter-3.1.3 {
  execsql {
    DROP TRIGGER trig1;
  }
} {}
do_test alter-3.1.4 {
  execsql {
    CREATE TRIGGER trig2 AFTER INSERT ON main.t7 BEGIN
      SELECT trigfunc('trig2', new.a, new.b, new.c);
    END;
    INSERT INTO t7 VALUES(1, 2, 3);
  }
  set ::TRIGGER
} {trig2 1 2 3}
do_test alter-3.1.5 {
  execsql {
    ALTER TABLE t7 RENAME TO t8;
    INSERT INTO t8 VALUES(4, 5, 6);
  }
  set ::TRIGGER
} {trig2 4 5 6}
do_test alter-3.1.6 {
  execsql {
    DROP TRIGGER trig2;
  }
} {}
do_test alter-3.1.7 {
  execsql {
    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 {}}
finish_test