# 2001 September 15

#

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

# it tests that the different storage classes (integer, real, text etc.)

# all work correctly.

#

# $Id: types.test,v 1.19 2006/06/27 12:51:13 drh Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# Tests in this file are organized roughly as follows:

#

# types-1.*.*: Test that values are stored using the expected storage

#              classes when various forms of literals are inserted into

#              columns with different affinities.

# types-1.1.*: INSERT INTO <table> VALUES(...)

# types-1.2.*: INSERT INTO <table> SELECT...

# types-1.3.*: UPDATE <table> SET...

#

# types-2.*.*: Check that values can be stored and retrieving using the

#              various storage classes.

# types-2.1.*: INTEGER

# types-2.2.*: REAL

# types-2.3.*: NULL

# types-2.4.*: TEXT

# types-2.5.*: Records with a few different storage classes.

#

# types-3.*: Test that the '=' operator respects manifest types.

#

# Disable encryption on the database for this test.

db close

set DB [sqlite3 db test.db; sqlite3_connection_pointer db]

sqlite3_rekey $DB {}

# Create a table with one column for each type of affinity

do_test types-1.1.0 {

  execsql {

    CREATE TABLE t1(i integer, n numeric, t text, o blob);

  }

} {}

# Each element of the following list represents one test case.

#

# The first value of each sub-list is an SQL literal. The following

# four value are the storage classes that would be used if the

# literal were inserted into a column with affinity INTEGER, NUMERIC, TEXT

# or NONE, respectively.

set values {

  { 5.0    integer integer text real    }

  { 5.1    real    real    text real    }

  { 5      integer integer text integer }

  { '5.0'  integer integer text text    }

  { '5.1'  real    real    text text    }

  { '-5.0' integer integer text text    }

  { '-5.0' integer integer text text    }

  { '5'    integer integer text text    }

  { 'abc'  text    text    text text    }

  { NULL   null    null    null null    }

}

ifcapable {bloblit} {

  lappend values  { X'00'  blob    blob    blob blob    }

}

# This code tests that the storage classes specified above (in the $values

# table) are correctly assigned when values are inserted using a statement

# of the form:

#

# INSERT INTO <table> VALUE(<values>);

#

set tnum 1

foreach val $values {

  set lit [lindex $val 0]

  execsql "DELETE FROM t1;"

  execsql "INSERT INTO t1 VALUES($lit, $lit, $lit, $lit);"

  do_test types-1.1.$tnum {

    execsql {

      SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;

    }

  } [lrange $val 1 end]

  incr tnum

}

# This code tests that the storage classes specified above (in the $values

# table) are correctly assigned when values are inserted using a statement

# of the form:

#

# INSERT INTO t1 SELECT ....

#

set tnum 1

foreach val $values {

  set lit [lindex $val 0]

  execsql "DELETE FROM t1;"

  execsql "INSERT INTO t1 SELECT $lit, $lit, $lit, $lit;"

  do_test types-1.2.$tnum {

    execsql {

      SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;

    }

  } [lrange $val 1 end]

  incr tnum

}

# This code tests that the storage classes specified above (in the $values

# table) are correctly assigned when values are inserted using a statement

# of the form:

#

# UPDATE <table> SET <column> = <value>;

#

set tnum 1

foreach val $values {

  set lit [lindex $val 0]

  execsql "UPDATE t1 SET i = $lit, n = $lit, t = $lit, o = $lit;"

  do_test types-1.3.$tnum {

    execsql {

      SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;

    }

  } [lrange $val 1 end]

  incr tnum

}

execsql {

  DROP TABLE t1;

}

# Open the table with root-page $rootpage at the btree

# level. Return a list that is the length of each record

# in the table, in the tables default scanning order.

proc record_sizes {rootpage} {

  set bt [btree_open test.db 10 0]

  set c [btree_cursor $bt $rootpage 0]

  btree_first $c

  while 1 {

    lappend res [btree_payload_size $c]

    if {[btree_next $c]} break

  }

  btree_close_cursor $c

  btree_close $bt

  set res

}

# Create a table and insert some 1-byte integers. Make sure they 

# can be read back OK. These should be 3 byte records.

do_test types-2.1.1 {

  execsql {

    CREATE TABLE t1(a integer);

    INSERT INTO t1 VALUES(0);

    INSERT INTO t1 VALUES(120);

    INSERT INTO t1 VALUES(-120);

  }

} {}

do_test types-2.1.2 {

  execsql {

    SELECT a FROM t1;

  }

} {0 120 -120}

# Try some 2-byte integers (4 byte records)

do_test types-2.1.3 {

  execsql {

    INSERT INTO t1 VALUES(30000);

    INSERT INTO t1 VALUES(-30000);

  }

} {}

do_test types-2.1.4 {

  execsql {

    SELECT a FROM t1;

  }

} {0 120 -120 30000 -30000}

# 4-byte integers (6 byte records)

do_test types-2.1.5 {

  execsql {

    INSERT INTO t1 VALUES(2100000000);

    INSERT INTO t1 VALUES(-2100000000);

  }

} {}

do_test types-2.1.6 {

  execsql {

    SELECT a FROM t1;

  }

} {0 120 -120 30000 -30000 2100000000 -2100000000}

# 8-byte integers (10 byte records)

do_test types-2.1.7 {

  execsql {

    INSERT INTO t1 VALUES(9000000*1000000*1000000);

    INSERT INTO t1 VALUES(-9000000*1000000*1000000);

  }

} {}

do_test types-2.1.8 {

  execsql {

    SELECT a FROM t1;

  }

} [list 0 120 -120 30000 -30000 2100000000 -2100000000 \

        9000000000000000000 -9000000000000000000]

# Check that all the record sizes are as we expected.

ifcapable legacyformat {

  do_test types-2.1.9 {

    set root [db eval {select rootpage from sqlite_master where name = 't1'}]

    record_sizes $root

  } {3 3 3 4 4 6 6 10 10}

} else {

  do_test types-2.1.9 {

    set root [db eval {select rootpage from sqlite_master where name = 't1'}]

    record_sizes $root

  } {2 3 3 4 4 6 6 10 10}

}

# Insert some reals. These should be 10 byte records.

do_test types-2.2.1 {

  execsql {

    CREATE TABLE t2(a float);

    INSERT INTO t2 VALUES(0.0);

    INSERT INTO t2 VALUES(12345.678);

    INSERT INTO t2 VALUES(-12345.678);

  }

} {}

do_test types-2.2.2 {

  execsql {

    SELECT a FROM t2;

  }

} {0.0 12345.678 -12345.678}

# Check that all the record sizes are as we expected.

ifcapable legacyformat {

  do_test types-2.2.3 {

    set root [db eval {select rootpage from sqlite_master where name = 't2'}]

    record_sizes $root

  } {3 10 10}

} else {

  do_test types-2.2.3 {

    set root [db eval {select rootpage from sqlite_master where name = 't2'}]

    record_sizes $root

  } {2 10 10}

}

# Insert a NULL. This should be a two byte record.

do_test types-2.3.1 {

  execsql {

    CREATE TABLE t3(a nullvalue);

    INSERT INTO t3 VALUES(NULL);

  }

} {}

do_test types-2.3.2 {

  execsql {

    SELECT a ISNULL FROM t3;

  }

} {1}

# Check that all the record sizes are as we expected.

do_test types-2.3.3 {

  set root [db eval {select rootpage from sqlite_master where name = 't3'}]

  record_sizes $root

} {2}

# Insert a couple of strings.

do_test types-2.4.1 {

  set string10 abcdefghij

  set string500 [string repeat $string10 50]

  set string500000 [string repeat $string10 5000]

  execsql "

    CREATE TABLE t4(a string);

    INSERT INTO t4 VALUES('$string10');

    INSERT INTO t4 VALUES('$string500');

    INSERT INTO t4 VALUES('$string500000');

  "

} {}

do_test types-2.4.2 {

  execsql {

    SELECT a FROM t4;

  }

} [list $string10 $string500 $string500000]

# Check that all the record sizes are as we expected. This is dependant on

# the database encoding.

if { $sqlite_options(utf16)==0 || [execsql {pragma encoding}] == "UTF-8" } {

  do_test types-2.4.3 {

    set root [db eval {select rootpage from sqlite_master where name = 't4'}]

    record_sizes $root

  } {12 503 50004}

} else {

  do_test types-2.4.3 {

    set root [db eval {select rootpage from sqlite_master where name = 't4'}]

    record_sizes $root

  } {22 1003 1000004}

}

do_test types-2.5.1 {

  execsql {

    DROP TABLE t1;

    DROP TABLE t2;

    DROP TABLE t3;

    DROP TABLE t4;

    CREATE TABLE t1(a, b, c);

  }

} {}

do_test types-2.5.2 {

  set string10 abcdefghij

  set string500 [string repeat $string10 50]

  set string500000 [string repeat $string10 5000]

  execsql "INSERT INTO t1 VALUES(NULL, '$string10', 4000);"

  execsql "INSERT INTO t1 VALUES('$string500', 4000, NULL);"

  execsql "INSERT INTO t1 VALUES(4000, NULL, '$string500000');"

} {}

do_test types-2.5.3 {

  execsql {

    SELECT * FROM t1;

  }

} [list {} $string10 4000 $string500 4000 {} 4000 {} $string500000]

finish_test