SQLite

ifcapable {!pragma} return

# Do not use a codec for tests in this file, as the database file is
# manipulated directly using tcl scripts. See proc [set_file_format].
#
do_not_use_codec

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

ifcapable !altertable {
  finish_test
  return
}

# Determine if there is a codec available on this test.
#
if {[catch {sqlite3 -has_codec} r] || $r} {
  set has_codec 1
} else {
  set has_codec 0
}


# Test Organisation:

# 
proc get_file_format {{fname test.db}} {
  return [hexio_get_int [hexio_read $fname 44 4]]
}

do_test alter3-1.1 {
  execsql {

    CREATE TABLE abc(a, b, c);
    SELECT sql FROM sqlite_master;
  }
} {{CREATE TABLE abc(a, b, c)}}
do_test alter3-1.2 {
  execsql {ALTER TABLE abc ADD d INTEGER;}
  execsql {

}

do_test alter3-4.1 {
  db close
  file delete -force test.db
  set ::DB [sqlite3 db test.db]
  execsql {

    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(1, 100);
    INSERT INTO t1 VALUES(2, 300);
    SELECT * FROM t1;
  }
} {1 100 2 300}
do_test alter3-4.1 {

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

# Determine if there is a codec available on this test.
#
if {[catch {sqlite3 -has_codec} r] || $r} {
  set has_codec 1
} else {
  set has_codec 0
}


# Test Organisation:
# ------------------
#
# alter4-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql.
# alter4-2.*: Test error messages.
# alter4-3.*: Test adding columns with default value NULL.
# alter4-4.*: Test adding columns with default values other than NULL.
# alter4-5.*: Test adding columns to tables in ATTACHed databases.
# alter4-6.*: Test that temp triggers are not accidentally dropped.
# alter4-7.*: Test that VACUUM resets the file-format.
#

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

do_test alter4-1.1 {
  execsql {
    CREATE TEMP TABLE abc(a, b, c);
    SELECT sql FROM sqlite_temp_master;
  }
} {{CREATE TABLE abc(a, b, c)}}
do_test alter4-1.2 {

} {}
do_test alter4-3.2 {
  execsql {
    ALTER TABLE t1 ADD c;
    SELECT * FROM t1;
  }
} {1 100 {} 2 300 {}}
if {!$has_codec} {
  do_test alter4-3.3 {
    get_file_format
  } {3}
}
ifcapable schema_version {
  do_test alter4-3.4 {
    execsql {
      PRAGMA schema_version;
    }
  } {10}
}

} {}
do_test alter4-4.2 {
  execsql {
    ALTER TABLE t1 ADD c DEFAULT 'hello world';
    SELECT * FROM t1;
  }
} {1 100 {hello world} 2 300 {hello world}}
if {!$has_codec} {
  do_test alter4-4.3 {
    get_file_format
  } {3}
}
ifcapable schema_version {
  do_test alter4-4.4 {
    execsql {
      PRAGMA schema_version;
    }
  } {20}
}

  ifcapable schema_version {
    do_test alter4-5.4 {
      execsql {
        PRAGMA aux.schema_version;
      }
    } {31}
  }
  if {!$has_codec} {
    do_test alter4-5.5 {
      list [get_file_format test2.db] [get_file_format]
    } {2 3}
  }
  do_test alter4-5.6 {
    execsql {
      ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
      SELECT sql FROM aux.sqlite_master;
    }
  } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
  do_test alter4-5.7 {

      ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';
      INSERT INTO t1(a, b) VALUES(3, 4);
      SELECT * FROM log;
    }
  } {b 1 2 a 1 2 b 3 4 a 3 4}
}

if {!$has_codec} {
  ifcapable vacuum {
    do_test alter4-7.1 {
      execsql {
        VACUUM;
      }
      get_file_format
    } {1}
    do_test alter4-7.2 {
      execsql {
        CREATE TEMP TABLE abc(a, b, c);
        ALTER TABLE abc ADD d DEFAULT NULL;
      }
      get_file_format
    } {2}
    do_test alter4-7.3 {
      execsql {
        ALTER TABLE abc ADD e DEFAULT 10;
      }
      get_file_format
    } {3}
    do_test alter4-7.4 {
      execsql {
        ALTER TABLE abc ADD f DEFAULT NULL;
      }
      get_file_format
    } {3}
    do_test alter4-7.5 {
      execsql {
        VACUUM;
      }
      get_file_format
    } {1}
  }
}

# Ticket #1183 - Make sure adding columns to large tables does not cause
# memory corruption (as was the case before this bug was fixed).
do_test alter4-8.1 {
  execsql {
    CREATE TEMP TABLE t4(c1);
  }
} {}

    COMMIT;
  } -exclude $ex
}

# Test IO errors when replaying two hot journals from a 2-file 
# transaction. This test only runs on UNIX.
ifcapable crashtest&&attach {
  if {![catch {sqlite3 -has_codec} r] && !$r} {
    do_ioerr_test ioerr-6 -ckrefcount true -tclprep {
      execsql {
        ATTACH 'test2.db' as aux;
        CREATE TABLE tx(a, b);
        CREATE TABLE aux.ty(a, b);
      }
      set rc [crashsql -delay 2 -file test2.db-journal {