# 2005 February 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.

#

#*************************************************************************

# 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".

#

# $Id: alter2.test,v 1.13 2008/03/19 00:21:31 drh Exp $

#

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# We have to have pragmas in order to do this test

ifcapable {!pragma} return

# These tests do not work if there is a codec. 

#

#if {[catch {sqlite3 -has_codec} r] || $r} return

# The file format change affects the way row-records stored in tables (but 

# not indices) are interpreted. Before version 3.1.3, a row-record for a 

# table with N columns was guaranteed to contain exactly N fields. As

# of version 3.1.3, the record may contain up to N fields. In this case

# the M fields that are present are the values for the left-most M 

# columns. The (N-M) rightmost columns contain NULL.

#

# If any records in the database contain less fields than their table

# has columns, then the file-format meta value should be set to (at least) 2. 

#

# This procedure sets the value of the file-format in file 'test.db'

# to $newval. Also, the schema cookie is incremented.

# 

proc set_file_format {newval} {

  hexio_write test.db 44 [hexio_render_int32 $newval]

  set schemacookie [hexio_get_int [hexio_read test.db 40 4]]

  incr schemacookie

  hexio_write test.db 40 [hexio_render_int32 $schemacookie]

  return {}

}

# 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]]

}

# This procedure sets the SQL statement stored for table $tbl in the

# sqlite_master table of file 'test.db' to $sql. Also set the file format

# to the supplied value. This is 2 if the added column has a default that is

# NULL, or 3 otherwise. 

#

proc alter_table {tbl sql {file_format 2}} {

  sqlite3 dbat test.db

  set s [string map {' ''} $sql]

  set t [string map {' ''} $tbl]

  dbat eval [subst {

    PRAGMA writable_schema = 1;

    UPDATE sqlite_master SET sql = '$s' WHERE name = '$t' AND type = 'table';

    PRAGMA writable_schema = 0;

  }]

  dbat close

  set_file_format 2

}

#-----------------------------------------------------------------------

# Some basic tests to make sure short rows are handled.

#

do_test alter2-1.1 {

  execsql {

    CREATE TABLE abc(a, b);

    INSERT INTO abc VALUES(1, 2);

    INSERT INTO abc VALUES(3, 4);

    INSERT INTO abc VALUES(5, 6);

  }

} {}

do_test alter2-1.2 {

  # ALTER TABLE abc ADD COLUMN c;

  alter_table abc {CREATE TABLE abc(a, b, c);}

} {}

do_test alter2-1.3 {

  execsql {

    SELECT * FROM abc;

  }

} {1 2 {} 3 4 {} 5 6 {}}

do_test alter2-1.4 {

  execsql {

    UPDATE abc SET c = 10 WHERE a = 1;

    SELECT * FROM abc;

  }

} {1 2 10 3 4 {} 5 6 {}}

do_test alter2-1.5 {

  execsql {

    CREATE INDEX abc_i ON abc(c);

  }

} {}

do_test alter2-1.6 {

  execsql {

    SELECT c FROM abc ORDER BY c;

  }

} {{} {} 10}

do_test alter2-1.7 {

  execsql {

    SELECT * FROM abc WHERE c = 10;

  }

} {1 2 10}

do_test alter2-1.8 {

  execsql {

    SELECT sum(a), c FROM abc GROUP BY c;

  }

} {8 {} 1 10}

do_test alter2-1.9 {

  # ALTER TABLE abc ADD COLUMN d;

  alter_table abc {CREATE TABLE abc(a, b, c, d);}

  execsql { SELECT * FROM abc; }

  execsql {

    UPDATE abc SET d = 11 WHERE c IS NULL AND a<4;

    SELECT * FROM abc;

  }

} {1 2 10 {} 3 4 {} 11 5 6 {} {}}

do_test alter2-1.10 {

  execsql {

    SELECT typeof(d) FROM abc;

  }

} {null integer null}

do_test alter2-1.99 {

  execsql {

    DROP TABLE abc;

  }

} {}

#-----------------------------------------------------------------------

# Test that views work when the underlying table structure is changed.

#

ifcapable view {

  do_test alter2-2.1 {

    execsql {

      CREATE TABLE abc2(a, b, c);

      INSERT INTO abc2 VALUES(1, 2, 10);

      INSERT INTO abc2 VALUES(3, 4, NULL);

      INSERT INTO abc2 VALUES(5, 6, NULL);

      CREATE VIEW abc2_v AS SELECT * FROM abc2;

      SELECT * FROM abc2_v;

    }

  } {1 2 10 3 4 {} 5 6 {}}

  do_test alter2-2.2 {

    # ALTER TABLE abc ADD COLUMN d;

    alter_table abc2 {CREATE TABLE abc2(a, b, c, d);}

    execsql {

      SELECT * FROM abc2_v;

    }

  } {1 2 10 {} 3 4 {} {} 5 6 {} {}}

  do_test alter2-2.3 {

    execsql {

      DROP TABLE abc2;

      DROP VIEW abc2_v;

    }

  } {}

}

#-----------------------------------------------------------------------

# Test that triggers work when a short row is copied to the old.*

# trigger pseudo-table.

#

ifcapable trigger {

  do_test alter2-3.1 {

    execsql {

      CREATE TABLE abc3(a, b);

      CREATE TABLE blog(o, n);

      CREATE TRIGGER abc3_t AFTER UPDATE OF b ON abc3 BEGIN

        INSERT INTO blog VALUES(old.b, new.b);

      END;

    }

  } {}

  do_test alter2-3.2 {

    execsql {

      INSERT INTO abc3 VALUES(1, 4);

      UPDATE abc3 SET b = 2 WHERE b = 4;

      SELECT * FROM blog;

    }

  } {4 2}

  do_test alter2-3.3 {

    execsql {

      INSERT INTO abc3 VALUES(3, 4);

      INSERT INTO abc3 VALUES(5, 6);

    }

    alter_table abc3 {CREATE TABLE abc3(a, b, c);}

    execsql {

      SELECT * FROM abc3;

    }

  } {1 2 {} 3 4 {} 5 6 {}}

  do_test alter2-3.4 {

    execsql {

      UPDATE abc3 SET b = b*2 WHERE a<4;

      SELECT * FROM abc3;

    }

  } {1 4 {} 3 8 {} 5 6 {}}

  do_test alter2-3.5 {

    execsql {

      SELECT * FROM blog;

    }

  } {4 2 2 4 4 8}

  do_test alter2-3.6 {

    execsql {

      CREATE TABLE clog(o, n);

      CREATE TRIGGER abc3_t2 AFTER UPDATE OF c ON abc3 BEGIN

        INSERT INTO clog VALUES(old.c, new.c);

      END;

      UPDATE abc3 SET c = a*2;

      SELECT * FROM clog;

    }

  } {{} 2 {} 6 {} 10}

}

#---------------------------------------------------------------------

# Check that an error occurs if the database is upgraded to a file

# format that SQLite does not support (in this case 5). Note: The 

# file format is checked each time the schema is read, so changing the

# file format requires incrementing the schema cookie.

#

do_test alter2-4.1 {

  db close

  set_file_format 5

  sqlite3 db test.db

} {}

do_test alter2-4.2 {

  # We have to run two queries here because the Tcl interface uses

  # sqlite3_prepare_v2(). In this case, the first query encounters an 

  # SQLITE_SCHEMA error. Then, when trying to recompile the statement, the

  # "unsupported file format" error is encountered. So the error code

  # returned is SQLITE_SCHEMA, not SQLITE_ERROR as required by the following

  # test case.

  #

  # When the query is attempted a second time, the same error message is

  # returned but the error code is SQLITE_ERROR, because the unsupported

  # file format was detected during a call to sqlite3_prepare(), not

  # sqlite3_step().

  #

  catchsql { SELECT * FROM sqlite_master; }

  catchsql { SELECT * FROM sqlite_master; }

} {1 {unsupported file format}}

do_test alter2-4.3 {

  sqlite3_errcode db

} {SQLITE_ERROR}

do_test alter2-4.4 {

  set ::DB [sqlite3_connection_pointer db]

  catchsql {

    SELECT * FROM sqlite_master;

  }

} {1 {unsupported file format}}

do_test alter2-4.5 {

  sqlite3_errcode db

} {SQLITE_ERROR}

#---------------------------------------------------------------------

# Check that executing VACUUM on a file with file-format version 2

# resets the file format to 1.

#

set default_file_format [expr $SQLITE_DEFAULT_FILE_FORMAT==4 ? 4 : 1]

ifcapable vacuum {

  do_test alter2-5.1 {

    set_file_format 2

    db close

    sqlite3 db test.db

    execsql {SELECT 1 FROM sqlite_master LIMIT 1;}

    get_file_format

  } {2}

  do_test alter2-5.2 {

    execsql {    

      VACUUM;

    }

  } {}

  do_test alter2-5.3 {

    get_file_format

  } $default_file_format

}

#---------------------------------------------------------------------

# Test that when a database with file-format 2 is opened, new 

# databases are still created with file-format 1.

#

do_test alter2-6.1 {

  db close

  set_file_format 2

  sqlite3 db test.db

  get_file_format

} {2}

ifcapable attach {

  do_test alter2-6.2 {

    file delete -force test2.db-journal

    file delete -force test2.db

    execsql {

      ATTACH 'test2.db' AS aux;

      CREATE TABLE aux.t1(a, b);

    }

    get_file_format test2.db

  } $default_file_format

}

do_test alter2-6.3 {

  execsql {

    CREATE TABLE t1(a, b);

  }

  get_file_format 

} {2}

#---------------------------------------------------------------------

# Test that types and values for columns added with default values 

# other than NULL work with SELECT statements.

#

do_test alter2-7.1 {

  execsql {

    DROP TABLE t1;

    CREATE TABLE t1(a);

    INSERT INTO t1 VALUES(1);

    INSERT INTO t1 VALUES(2);

    INSERT INTO t1 VALUES(3);

    INSERT INTO t1 VALUES(4);

    SELECT * FROM t1;

  }

} {1 2 3 4}

do_test alter2-7.2 {

  set sql {CREATE TABLE t1(a, b DEFAULT '123', c INTEGER DEFAULT '123')}

  alter_table t1 $sql 3

  execsql {

    SELECT * FROM t1 LIMIT 1;

  }

} {1 123 123}

do_test alter2-7.3 {

  execsql {

    SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;

  }

} {1 integer 123 text 123 integer}

do_test alter2-7.4 {

  execsql {

    SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;

  }

} {1 integer 123 text 123 integer}

do_test alter2-7.5 {

  set sql {CREATE TABLE t1(a, b DEFAULT -123.0, c VARCHAR(10) default 5)}

  alter_table t1 $sql 3

  execsql {

    SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;

  }

} {1 integer -123 integer 5 text}

#-----------------------------------------------------------------------

# Test that UPDATE trigger tables work with default values, and that when

# a row is updated the default values are correctly transfered to the 

# new row.

# 

ifcapable trigger {

db function set_val {set ::val}

  do_test alter2-8.1 {

    execsql {

      CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN

      SELECT set_val(

          old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)||' '||

          new.b||' '||typeof(new.b)||' '||new.c||' '||typeof(new.c) 

      );

      END;

    }

    list

  } {}

}

do_test alter2-8.2 {

  execsql {

    UPDATE t1 SET c = 10 WHERE a = 1;

    SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;

  }

} {1 integer -123 integer 10 text}

ifcapable trigger {

  do_test alter2-8.3 {

    set ::val

  } {-123 integer 5 text -123 integer 10 text}

}

#-----------------------------------------------------------------------

# Test that DELETE trigger tables work with default values, and that when

# a row is updated the default values are correctly transfered to the 

# new row.

# 

ifcapable trigger {

  do_test alter2-9.1 {

    execsql {

      CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN

      SELECT set_val(

          old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)

      );

      END;

    }

    list

  } {}

  do_test alter2-9.2 {

    execsql {

      DELETE FROM t1 WHERE a = 2;

    }

    set ::val

  } {-123 integer 5 text}

}

#-----------------------------------------------------------------------

# Test creating an index on a column added with a default value. 

#

ifcapable bloblit {

  do_test alter2-10.1 {

    execsql {

      CREATE TABLE t2(a);

      INSERT INTO t2 VALUES('a');

      INSERT INTO t2 VALUES('b');

      INSERT INTO t2 VALUES('c');

      INSERT INTO t2 VALUES('d');

    }

    alter_table t2 {CREATE TABLE t2(a, b DEFAULT X'ABCD', c DEFAULT NULL);} 3

    catchsql {

      SELECT * FROM sqlite_master;

    }

    execsql {

      SELECT quote(a), quote(b), quote(c) FROM t2 LIMIT 1;

    }

  } {'a' X'ABCD' NULL}

  do_test alter2-10.2 {

    execsql {

      CREATE INDEX i1 ON t2(b);

      SELECT a FROM t2 WHERE b = X'ABCD';

    }

  } {a b c d}

  do_test alter2-10.3 {

    execsql {

      DELETE FROM t2 WHERE a = 'c';

      SELECT a FROM t2 WHERE b = X'ABCD';

    }

  } {a b d}

  do_test alter2-10.4 {

    execsql {

      SELECT count(b) FROM t2 WHERE b = X'ABCD';

    }

  } {3}

}

finish_test