2N/A

2N/A#pragma ident "%Z%%M% %I% %E% SMI"

2N/A

2N/A# 2002 March 6

2N/A#

2N/A# The author disclaims copyright to this source code. In place of

2N/A# a legal notice, here is a blessing:

2N/A#

2N/A# May you do good and not evil.

2N/A# May you find forgiveness for yourself and forgive others.

2N/A# May you share freely, never taking more than you give.

2N/A#

2N/A#***********************************************************************

2N/A# This file implements regression tests for SQLite library.

2N/A#

2N/A# This file implements tests for the PRAGMA command.

2N/A#

2N/A# $Id: pragma.test,v 1.9 2004/04/23 17:04:45 drh Exp $

2N/A

2N/Aset testdir [file dirname $argv0]

2N/Asource $testdir/tester.tcl

2N/A

2N/A# Delete the preexisting database to avoid the special setup

2N/A# that the "all.test" script does.

2N/A#

2N/Adb close

2N/Afile delete test.db

2N/Aset DB [sqlite db test.db]

2N/A

2N/Ado_test pragma-1.1 {

2N/A execsql {

2N/A PRAGMA cache_size;

2N/A PRAGMA default_cache_size;

2N/A PRAGMA synchronous;

2N/A PRAGMA default_synchronous;

2N/A }

2N/A} {2000 2000 1 1}

2N/Ado_test pragma-1.2 {

2N/A execsql {

2N/A PRAGMA cache_size=1234;

2N/A PRAGMA cache_size;

2N/A PRAGMA default_cache_size;

2N/A PRAGMA synchronous;

2N/A PRAGMA default_synchronous;

2N/A }

2N/A} {1234 2000 1 1}

2N/Ado_test pragma-1.3 {

2N/A db close

2N/A sqlite db test.db

2N/A execsql {

2N/A PRAGMA cache_size;

2N/A PRAGMA default_cache_size;

2N/A PRAGMA synchronous;

2N/A PRAGMA default_synchronous;

2N/A }

2N/A} {2000 2000 1 1}

2N/Ado_test pragma-1.4 {

2N/A execsql {

2N/A PRAGMA synchronous=OFF;

2N/A PRAGMA cache_size;

2N/A PRAGMA default_cache_size;

2N/A PRAGMA synchronous;

2N/A PRAGMA default_synchronous;

2N/A }

2N/A} {2000 2000 0 1}

2N/Ado_test pragma-1.5 {

2N/A execsql {

2N/A PRAGMA cache_size=4321;

2N/A PRAGMA cache_size;

2N/A PRAGMA default_cache_size;

2N/A PRAGMA synchronous;

2N/A PRAGMA default_synchronous;

2N/A }

2N/A} {4321 2000 0 1}

2N/Ado_test pragma-1.6 {

2N/A execsql {

2N/A PRAGMA synchronous=ON;

2N/A PRAGMA cache_size;

2N/A PRAGMA default_cache_size;

2N/A PRAGMA synchronous;

2N/A PRAGMA default_synchronous;

2N/A }

2N/A} {4321 2000 1 1}

2N/Ado_test pragma-1.7 {

2N/A db close

2N/A sqlite db test.db

2N/A execsql {

2N/A PRAGMA cache_size;

2N/A PRAGMA default_cache_size;

2N/A PRAGMA synchronous;

2N/A PRAGMA default_synchronous;

2N/A }

2N/A} {2000 2000 1 1}

2N/Ado_test pragma-1.8 {

2N/A execsql {

2N/A PRAGMA default_synchronous=OFF;

2N/A PRAGMA cache_size;

2N/A PRAGMA default_cache_size;

2N/A PRAGMA synchronous;

2N/A PRAGMA default_synchronous;

2N/A }

2N/A} {2000 2000 0 0}

2N/Ado_test pragma-1.9 {

2N/A execsql {

2N/A PRAGMA default_cache_size=123;

2N/A PRAGMA cache_size;

2N/A PRAGMA default_cache_size;

2N/A PRAGMA synchronous;

2N/A PRAGMA default_synchronous;

2N/A }

2N/A} {123 123 0 0}

2N/Ado_test pragma-1.10 {

2N/A db close

2N/A set ::DB [sqlite db test.db]

2N/A execsql {

2N/A PRAGMA cache_size;

2N/A PRAGMA default_cache_size;

2N/A PRAGMA synchronous;

2N/A PRAGMA default_synchronous;

2N/A }

2N/A} {123 123 0 0}

2N/Ado_test pragma-1.11 {

2N/A execsql {

2N/A PRAGMA synchronous=NORMAL;

2N/A PRAGMA cache_size;

2N/A PRAGMA default_cache_size;

2N/A PRAGMA synchronous;

2N/A PRAGMA default_synchronous;

2N/A }

2N/A} {123 123 1 0}

2N/Ado_test pragma-1.12 {

2N/A execsql {

2N/A PRAGMA synchronous=FULL;

2N/A PRAGMA cache_size;

2N/A PRAGMA default_cache_size;

2N/A PRAGMA synchronous;

2N/A PRAGMA default_synchronous;

2N/A }

2N/A} {123 123 2 0}

2N/Ado_test pragma-1.13 {

2N/A db close

2N/A set ::DB [sqlite db test.db]

2N/A execsql {

2N/A PRAGMA cache_size;

2N/A PRAGMA default_cache_size;

2N/A PRAGMA synchronous;

2N/A PRAGMA default_synchronous;

2N/A }

2N/A} {123 123 0 0}

2N/Ado_test pragma-1.14 {

2N/A execsql {

2N/A PRAGMA default_synchronous=FULL;

2N/A PRAGMA cache_size;

2N/A PRAGMA default_cache_size;

2N/A PRAGMA synchronous;

2N/A PRAGMA default_synchronous;

2N/A }

2N/A} {123 123 2 2}

2N/Ado_test pragma-1.15 {

2N/A db close

2N/A set ::DB [sqlite db test.db]

2N/A execsql {

2N/A PRAGMA cache_size;

2N/A PRAGMA default_cache_size;

2N/A PRAGMA synchronous;

2N/A PRAGMA default_synchronous;

2N/A }

2N/A} {123 123 2 2}

2N/A

2N/Ado_test pragma-2.1 {

2N/A execsql {

2N/A PRAGMA show_datatypes=on;

2N/A PRAGMA empty_result_callbacks=off;

2N/A }

2N/A sqlite_datatypes $::DB {SELECT * FROM sqlite_master}

2N/A} {}

2N/Ado_test pragma-2.2 {

2N/A execsql {

2N/A PRAGMA empty_result_callbacks=on;

2N/A }

2N/A sqlite_datatypes $::DB {SELECT * FROM sqlite_master}

2N/A} {text text text integer text}

2N/A

2N/A# Make sure we can read the schema when empty_result_callbacks are

2N/A# turned on. Ticket #406

2N/Ado_test pragma-2.2.1 {

2N/A execsql {

2N/A BEGIN;

2N/A CREATE TABLE tabx(a,b,c,d);

2N/A ROLLBACK;

2N/A SELECT count(*) FROM sqlite_master;

2N/A }

2N/A} {0}

2N/A

2N/Ado_test pragma-2.3 {

2N/A execsql {

2N/A CREATE TABLE t1(

2N/A a INTEGER,

2N/A b TEXT,

2N/A c WHATEVER,

2N/A d CLOB,

2N/A e BLOB,

2N/A f VARCHAR(123),

2N/A g nVaRcHaR(432)

2N/A );

2N/A }

2N/A sqlite_datatypes $::DB {SELECT * FROM t1}

2N/A} {INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}

2N/Ado_test pragma-2.4 {

2N/A sqlite_datatypes $::DB {

2N/A SELECT 1, 'hello', NULL

2N/A }

2N/A} {NUMERIC TEXT TEXT}

2N/Ado_test pragma-2.5 {

2N/A sqlite_datatypes $::DB {

2N/A SELECT 1+2 AS X, 'hello' || 5 AS Y, NULL AS Z

2N/A }

2N/A} {NUMERIC TEXT TEXT}

2N/Ado_test pragma-2.6 {

2N/A execsql {

2N/A CREATE VIEW v1 AS SELECT a+b, b||c, * FROM t1;

2N/A }

2N/A sqlite_datatypes $::DB {SELECT * FROM v1}

2N/A} {NUMERIC TEXT INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}

2N/Ado_test pragma-2.7 {

2N/A sqlite_datatypes $::DB {

2N/A SELECT d,e FROM t1 UNION SELECT a,c FROM t1

2N/A }

2N/A} {INTEGER WHATEVER}

2N/Ado_test pragma-2.8 {

2N/A sqlite_datatypes $::DB {

2N/A SELECT d,e FROM t1 EXCEPT SELECT c,e FROM t1

2N/A }

2N/A} {WHATEVER BLOB}

2N/Ado_test pragma-2.9 {

2N/A sqlite_datatypes $::DB {

2N/A SELECT d,e FROM t1 INTERSECT SELECT c,e FROM t1

2N/A }

2N/A} {WHATEVER BLOB}

2N/Ado_test pragma-2.10 {

2N/A sqlite_datatypes $::DB {

2N/A SELECT d,e FROM t1 INTERSECT SELECT c,e FROM v1

2N/A }

2N/A} {WHATEVER BLOB}

2N/A

2N/A# Construct a corrupted index and make sure the integrity_check

2N/A# pragma finds it.

2N/A#

2N/Aif {![sqlite -has-codec]} {

2N/Ado_test pragma-3.1 {

2N/A execsql {

2N/A BEGIN;

2N/A CREATE TABLE t2(a,b,c);

2N/A CREATE INDEX i2 ON t2(a);

2N/A INSERT INTO t2 VALUES(11,2,3);

2N/A INSERT INTO t2 VALUES(22,3,4);

2N/A COMMIT;

2N/A SELECT rowid, * from t2;

2N/A }

2N/A} {1 11 2 3 2 22 3 4}

2N/Ado_test pragma-3.2 {

2N/A set rootpage [execsql {SELECT rootpage FROM sqlite_master WHERE name='i2'}]

2N/A set db [btree_open test.db]

2N/A btree_begin_transaction $db

2N/A set c [btree_cursor $db $rootpage 1]

2N/A btree_first $c

2N/A btree_delete $c

2N/A btree_commit $db

2N/A btree_close $db

2N/A execsql {PRAGMA integrity_check}

2N/A} {{rowid 1 missing from index i2} {wrong # of entries in index i2}}

2N/A}; # endif has-codec

2N/A

2N/A# Test the temp_store and default_temp_store pragmas

2N/A#

2N/Ado_test pragma-4.2 {

2N/A execsql {

2N/A PRAGMA temp_store='default';

2N/A PRAGMA temp_store;

2N/A }

2N/A} {0}

2N/Ado_test pragma-4.3 {

2N/A execsql {

2N/A PRAGMA temp_store='file';

2N/A PRAGMA temp_store;

2N/A }

2N/A} {1}

2N/Ado_test pragma-4.4 {

2N/A execsql {

2N/A PRAGMA temp_store='memory';

2N/A PRAGMA temp_store;

2N/A }

2N/A} {2}

2N/Ado_test pragma-4.5 {

2N/A execsql {

2N/A PRAGMA default_temp_store='default';

2N/A PRAGMA default_temp_store;

2N/A }

2N/A} {0}

2N/Ado_test pragma-4.6 {

2N/A execsql {

2N/A PRAGMA temp_store;

2N/A }

2N/A} {2}

2N/Ado_test pragma-4.7 {

2N/A db close

2N/A sqlite db test.db

2N/A execsql {

2N/A PRAGMA temp_store;

2N/A }

2N/A} {0}

2N/Ado_test pragma-4.8 {

2N/A execsql {

2N/A PRAGMA default_temp_store;

2N/A }

2N/A} {0}

2N/Ado_test pragma-4.9 {

2N/A execsql {

2N/A PRAGMA default_temp_store='file';

2N/A PRAGMA default_temp_store;

2N/A }

2N/A} {1}

2N/Ado_test pragma-4.10 {

2N/A execsql {

2N/A PRAGMA temp_store;

2N/A }

2N/A} {0}

2N/Ado_test pragma-4.11 {

2N/A db close

2N/A sqlite db test.db

2N/A execsql {

2N/A PRAGMA temp_store;

2N/A }

2N/A} {1}

2N/Ado_test pragma-4.12 {

2N/A execsql {

2N/A PRAGMA default_temp_store;

2N/A }

2N/A} {1}

2N/Ado_test pragma-4.13 {

2N/A execsql {

2N/A PRAGMA default_temp_store='memory';

2N/A PRAGMA default_temp_store;

2N/A }

2N/A} {2}

2N/Ado_test pragma-4.14 {

2N/A execsql {

2N/A PRAGMA temp_store;

2N/A }

2N/A} {1}

2N/Ado_test pragma-4.15 {

2N/A db close

2N/A sqlite db test.db

2N/A execsql {

2N/A PRAGMA temp_store;

2N/A }

2N/A} {2}

2N/Ado_test pragma-4.16 {

2N/A execsql {

2N/A PRAGMA default_temp_store;

2N/A }

2N/A} {2}

2N/Ado_test pragma-4.17 {

2N/A execsql {

2N/A PRAGMA temp_store='file';

2N/A PRAGMA temp_store

2N/A }

2N/A} {1}

2N/Ado_test pragma-4.18 {

2N/A execsql {

2N/A PRAGMA default_temp_store

2N/A }

2N/A} {2}

2N/Ado_test pragma-4.19 {

2N/A db close

2N/A sqlite db test.db

2N/A execsql {

2N/A PRAGMA temp_store

2N/A }

2N/A} {2}

2N/A

2N/A# Changing the TEMP_STORE deletes any existing temporary tables

2N/A#

2N/Ado_test pragma-4.20 {

2N/A execsql {SELECT name FROM sqlite_temp_master}

2N/A} {}

2N/Ado_test pragma-4.21 {

2N/A execsql {

2N/A CREATE TEMP TABLE test1(a,b,c);

2N/A SELECT name FROM sqlite_temp_master;

2N/A }

2N/A} {test1}

2N/Ado_test pragma-4.22 {

2N/A execsql {

2N/A PRAGMA temp_store='file';

2N/A SELECT name FROM sqlite_temp_master;

2N/A }

2N/A} {}

2N/Ado_test pragma-4.23 {

2N/A execsql {

2N/A CREATE TEMP TABLE test1(a,b,c);

2N/A SELECT name FROM sqlite_temp_master;

2N/A }

2N/A} {test1}

2N/Ado_test pragma-4.24 {

2N/A execsql {

2N/A PRAGMA temp_store='memory';

2N/A SELECT name FROM sqlite_temp_master;

2N/A }

2N/A} {}

2N/Ado_test pragma-4.25 {

2N/A catchsql {

2N/A BEGIN;

2N/A PRAGMA temp_store='default';

2N/A COMMIT;

2N/A }

2N/A} {1 {temporary storage cannot be changed from within a transaction}}

2N/Acatchsql {COMMIT}

2N/A

2N/Afinish_test