#
#*************************************************************************
# 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: alter3.test,v 1.4 2005/03/27 01:56:31 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
}










# Test Organisation:
# ------------------
#
# alter3-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql.
# alter3-2.*: Test error messages.
# alter3-3.*: Test adding columns with default value NULL.
................................................................................
} {}
do_test alter3-3.2 {
  execsql {
    ALTER TABLE t1 ADD c;
    SELECT * FROM t1;
  }
} {1 100 {} 2 300 {}}

do_test alter3-3.3 {
  get_file_format
} {3}

do_test alter3-3.4 {
  execsql {
    PRAGMA schema_version;
  }
} {11}

do_test alter3-4.1 {
................................................................................
} {}
do_test alter3-4.2 {
  execsql {
    ALTER TABLE t1 ADD c DEFAULT 'hello world';
    SELECT * FROM t1;
  }
} {1 100 {hello world} 2 300 {hello world}}

do_test alter3-4.3 {
  get_file_format
} {3}

do_test alter3-4.4 {
  execsql {
    PRAGMA schema_version;
  }
} {21}
do_test alter3-4.99 {
  execsql {
................................................................................
  }
} {1 one {} 2 two {}}
do_test alter3-5.4 {
  execsql {
    PRAGMA aux.schema_version;
  }
} {31}

do_test alter3-5.5 {
  list [get_file_format test2.db] [get_file_format]
} {2 3}

do_test alter3-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 alter3-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}
}


ifcapable vacuum {
  do_test alter3-7.1 {
    execsql {
      VACUUM;
    }
    get_file_format
  } {1}
  do_test alter3-7.2 {
    execsql {
      CREATE TABLE abc(a, b, c);
      ALTER TABLE abc ADD d DEFAULT NULL;
    }
    get_file_format
  } {2}
  do_test alter3-7.3 {
    execsql {
      ALTER TABLE abc ADD e DEFAULT 10;
    }
    get_file_format
  } {3}
  do_test alter3-7.4 {
    execsql {
      ALTER TABLE abc ADD f DEFAULT NULL;
    }
    get_file_format
  } {3}
  do_test alter3-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 alter3-8.1 {
  execsql {
    CREATE TABLE t4(c1);
................................................................................
do_test alter3-8.2 {
  execsql {
    SELECT sql FROM sqlite_master WHERE name = 't4';
  }
} [list $::sql]

finish_test

#
#*************************************************************************
# 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: alter3.test,v 1.5 2005/03/28 16:50:22 drh Exp $
#

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# 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:
# ------------------
#
# alter3-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql.
# alter3-2.*: Test error messages.
# alter3-3.*: Test adding columns with default value NULL.
................................................................................
} {}
do_test alter3-3.2 {
  execsql {
    ALTER TABLE t1 ADD c;
    SELECT * FROM t1;
  }
} {1 100 {} 2 300 {}}
if {!$has_codec} {
  do_test alter3-3.3 {
    get_file_format
  } {3}
}
do_test alter3-3.4 {
  execsql {
    PRAGMA schema_version;
  }
} {11}

do_test alter3-4.1 {
................................................................................
} {}
do_test alter3-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 alter3-4.3 {
    get_file_format
  } {3}
}
do_test alter3-4.4 {
  execsql {
    PRAGMA schema_version;
  }
} {21}
do_test alter3-4.99 {
  execsql {
................................................................................
  }
} {1 one {} 2 two {}}
do_test alter3-5.4 {
  execsql {
    PRAGMA aux.schema_version;
  }
} {31}
if {!$has_codec} {
  do_test alter3-5.5 {
    list [get_file_format test2.db] [get_file_format]
  } {2 3}
}
do_test alter3-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 alter3-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 alter3-7.1 {
      execsql {
        VACUUM;
      }
      get_file_format
    } {1}
    do_test alter3-7.2 {
      execsql {
        CREATE TABLE abc(a, b, c);
        ALTER TABLE abc ADD d DEFAULT NULL;
      }
      get_file_format
    } {2}
    do_test alter3-7.3 {
      execsql {
        ALTER TABLE abc ADD e DEFAULT 10;
      }
      get_file_format
    } {3}
    do_test alter3-7.4 {
      execsql {
        ALTER TABLE abc ADD f DEFAULT NULL;
      }
      get_file_format
    } {3}
    do_test alter3-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 alter3-8.1 {
  execsql {
    CREATE TABLE t4(c1);
................................................................................
do_test alter3-8.2 {
  execsql {
    SELECT sql FROM sqlite_master WHERE name = 't4';
  }
} [list $::sql]

finish_test