gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/default.test (about)

     1  # 2005 August 18
     2  #
     3  # The author disclaims copyright to this source code.  In place of
     4  # a legal notice, here is a blessing:
     5  #
     6  #    May you do good and not evil.
     7  #    May you find forgiveness for yourself and forgive others.
     8  #    May you share freely, never taking more than you give.
     9  #
    10  #*************************************************************************
    11  # This file implements regression tests for SQLite library.  The
    12  # focus of this file is testing corner cases of the DEFAULT syntax
    13  # on table definitions.
    14  #
    15  # $Id: default.test,v 1.3 2009/02/19 14:39:25 danielk1977 Exp $
    16  #
    17  
    18  set testdir [file dirname $argv0]
    19  source $testdir/tester.tcl
    20  
    21  ifcapable bloblit {
    22    do_test default-1.1 {
    23      execsql {
    24        CREATE TABLE t1(
    25          a INTEGER,
    26          b BLOB DEFAULT x'6869'
    27        );
    28        INSERT INTO t1(a) VALUES(1);
    29        SELECT * from t1;
    30      }
    31    } {1 hi}
    32  }
    33  do_test default-1.2 {
    34    execsql {
    35      CREATE TABLE t2(
    36        x INTEGER,
    37        y INTEGER DEFAULT NULL
    38      );
    39      INSERT INTO t2(x) VALUES(1);
    40      SELECT * FROM t2;
    41    }
    42  } {1 {}}
    43  do_test default-1.3 {
    44    catchsql {
    45      CREATE TABLE t3(
    46        x INTEGER,
    47        y INTEGER DEFAULT (max(x,5))
    48      )
    49    }
    50  } {1 {default value of column [y] is not constant}}
    51  
    52  ifcapable pragma {
    53    do_test default-2.1 {
    54      execsql {
    55        CREATE TABLE t4(c DEFAULT 'abc');
    56        PRAGMA table_info(t4);
    57      }
    58    } {0 c {} 0 'abc' 0}
    59    do_test default-2.2 {
    60      execsql {
    61        INSERT INTO t4 DEFAULT VALUES;
    62        PRAGMA table_info(t4);
    63      }
    64    } {0 c {} 0 'abc' 0}
    65  }
    66  
    67  do_execsql_test default-3.1 {
    68    CREATE TABLE t3(
    69      a INTEGER PRIMARY KEY AUTOINCREMENT,
    70      b INT DEFAULT 12345 UNIQUE NOT NULL CHECK( b>=0 AND b<99999 ),
    71      c VARCHAR(123,456) DEFAULT 'hello' NOT NULL ON CONFLICT REPLACE,
    72      d REAL,
    73      e FLOATING POINT(5,10) DEFAULT 4.36,
    74      f NATIONAL CHARACTER(15) COLLATE RTRIM,
    75      g LONG INTEGER DEFAULT( 3600*12 )
    76    );
    77    INSERT INTO t3 VALUES(null, 5, 'row1', '5.25', 'xyz', 321, '432');
    78    SELECT a, typeof(a), b, typeof(b), c, typeof(c), 
    79           d, typeof(d), e, typeof(e), f, typeof(f),
    80           g, typeof(g) FROM t3;
    81  } {1 integer 5 integer row1 text 5.25 real xyz text 321 text 432 integer}
    82  do_execsql_test default-3.2 {
    83    DELETE FROM t3;
    84    INSERT INTO t3 DEFAULT VALUES;
    85    SELECT * FROM t3;
    86  } {2 12345 hello {} 4.36 {} 43200}
    87  do_execsql_test default-3.3 {
    88    CREATE TABLE t300(
    89      a INT DEFAULT 2147483647,
    90      b INT DEFAULT 2147483648,
    91      c INT DEFAULT +9223372036854775807,
    92      d INT DEFAULT -2147483647,
    93      e INT DEFAULT -2147483648,
    94      f INT DEFAULT -9223372036854775808,
    95      g INT DEFAULT (-(-9223372036854775808)),
    96      h INT DEFAULT (-(-9223372036854775807))
    97    );
    98    INSERT INTO t300 DEFAULT VALUES;
    99    SELECT * FROM t300;
   100  } {2147483647 2147483648 9223372036854775807 -2147483647 -2147483648 -9223372036854775808 9.22337203685478e+18 9223372036854775807}
   101  
   102  # Do now allow bound parameters in new DEFAULT values. 
   103  # Silently convert bound parameters to NULL in DEFAULT causes
   104  # in the sqlite_master table, for backwards compatibility.
   105  #
   106  db close
   107  forcedelete test.db
   108  sqlite3 db test.db
   109  sqlite3_db_config db DEFENSIVE 0
   110  do_execsql_test default-4.0 {
   111    CREATE TABLE t1(a TEXT, b TEXT DEFAULT(99));
   112    PRAGMA writable_schema=ON;
   113    UPDATE sqlite_master SET sql='CREATE TABLE t1(a TEXT, b TEXT DEFAULT(:xyz))';
   114  } {}
   115  db close 
   116  sqlite3 db test.db
   117  do_execsql_test default-4.1 {
   118    INSERT INTO t1(a) VALUES('xyzzy');
   119    SELECT a, quote(b) FROM t1;
   120  } {xyzzy NULL}
   121  do_catchsql_test default-4.2 {
   122    CREATE TABLE t2(a TEXT, b TEXT DEFAULT(:xyz));
   123  } {1 {default value of column [b] is not constant}}
   124  do_catchsql_test default-4.3 {
   125    CREATE TABLE t2(a TEXT, b TEXT DEFAULT(abs(:xyz)));
   126  } {1 {default value of column [b] is not constant}}
   127  do_catchsql_test default-4.4 {
   128    CREATE TABLE t2(a TEXT, b TEXT DEFAULT(98+coalesce(5,:xyz)));
   129  } {1 {default value of column [b] is not constant}}
   130  
   131  # 2020-03-09 out-of-bounds memory access discovered by "Eternal Sakura"
   132  # and reported to chromium.
   133  #
   134  reset_db
   135  do_catchsql_test default-5.1 {
   136    CREATE TABLE t1 (a,b DEFAULT(random() NOTNULL IN (RAISE(IGNORE),2,3)));
   137    INSERT INTO t1(a) VALUES(1);
   138  } {1 {RAISE() may only be used within a trigger-program}}
   139  
   140  finish_test