Symaptic: os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/pragma.test@bde4ae8d615e (annotated)

sl@0	1	# 2002 March 6
sl@0	2	#
sl@0	3	# The author disclaims copyright to this source code. In place of
sl@0	4	# a legal notice, here is a blessing:
sl@0	5	#
sl@0	6	# May you do good and not evil.
sl@0	7	# May you find forgiveness for yourself and forgive others.
sl@0	8	# May you share freely, never taking more than you give.
sl@0	9	#
sl@0	10	#***********************************************************************
sl@0	11	# This file implements regression tests for SQLite library.
sl@0	12	#
sl@0	13	# This file implements tests for the PRAGMA command.
sl@0	14	#
sl@0	15	# $Id: pragma.test,v 1.66 2008/09/02 00:52:52 drh Exp $
sl@0	16
sl@0	17	set testdir [file dirname $argv0]
sl@0	18	source $testdir/tester.tcl
sl@0	19
sl@0	20	# Test organization:
sl@0	21	#
sl@0	22	# pragma-1.*: Test cache_size, default_cache_size and synchronous on main db.
sl@0	23	# pragma-2.*: Test synchronous on attached db.
sl@0	24	# pragma-3.*: Test detection of table/index inconsistency by integrity_check.
sl@0	25	# pragma-4.*: Test cache_size and default_cache_size on attached db.
sl@0	26	# pragma-5.*: Test that pragma synchronous may not be used inside of a
sl@0	27	# transaction.
sl@0	28	# pragma-6.*: Test schema-query pragmas.
sl@0	29	# pragma-7.*: Miscellaneous tests.
sl@0	30	# pragma-8.*: Test user_version and schema_version pragmas.
sl@0	31	# pragma-9.*: Test temp_store and temp_store_directory.
sl@0	32	# pragma-10.*: Test the count_changes pragma in the presence of triggers.
sl@0	33	# pragma-11.*: Test the collation_list pragma.
sl@0	34	# pragma-14.*: Test the page_count pragma.
sl@0	35	# pragma-15.*: Test that the value set using the cache_size pragma is not
sl@0	36	# reset when the schema is reloaded.
sl@0	37	#
sl@0	38
sl@0	39	ifcapable !pragma {
sl@0	40	finish_test
sl@0	41	return
sl@0	42	}
sl@0	43
sl@0	44	# Delete the preexisting database to avoid the special setup
sl@0	45	# that the "all.test" script does.
sl@0	46	#
sl@0	47	db close
sl@0	48	file delete test.db test.db-journal
sl@0	49	file delete test3.db test3.db-journal
sl@0	50	sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
sl@0	51
sl@0	52
sl@0	53	ifcapable pager_pragmas {
sl@0	54	set DFLT_CACHE_SZ [db one {PRAGMA default_cache_size}]
sl@0	55	set TEMP_CACHE_SZ [db one {PRAGMA temp.default_cache_size}]
sl@0	56	do_test pragma-1.1 {
sl@0	57	execsql {
sl@0	58	PRAGMA cache_size;
sl@0	59	PRAGMA default_cache_size;
sl@0	60	PRAGMA synchronous;
sl@0	61	}
sl@0	62	} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]
sl@0	63	do_test pragma-1.2 {
sl@0	64	execsql {
sl@0	65	PRAGMA synchronous=OFF;
sl@0	66	PRAGMA cache_size=1234;
sl@0	67	PRAGMA cache_size;
sl@0	68	PRAGMA default_cache_size;
sl@0	69	PRAGMA synchronous;
sl@0	70	}
sl@0	71	} [list 1234 $DFLT_CACHE_SZ 0]
sl@0	72	do_test pragma-1.3 {
sl@0	73	db close
sl@0	74	sqlite3 db test.db
sl@0	75	execsql {
sl@0	76	PRAGMA cache_size;
sl@0	77	PRAGMA default_cache_size;
sl@0	78	PRAGMA synchronous;
sl@0	79	}
sl@0	80	} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]
sl@0	81	do_test pragma-1.4 {
sl@0	82	execsql {
sl@0	83	PRAGMA synchronous=OFF;
sl@0	84	PRAGMA cache_size;
sl@0	85	PRAGMA default_cache_size;
sl@0	86	PRAGMA synchronous;
sl@0	87	}
sl@0	88	} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 0]
sl@0	89	do_test pragma-1.5 {
sl@0	90	execsql {
sl@0	91	PRAGMA cache_size=4321;
sl@0	92	PRAGMA cache_size;
sl@0	93	PRAGMA default_cache_size;
sl@0	94	PRAGMA synchronous;
sl@0	95	}
sl@0	96	} [list 4321 $DFLT_CACHE_SZ 0]
sl@0	97	do_test pragma-1.6 {
sl@0	98	execsql {
sl@0	99	PRAGMA synchronous=ON;
sl@0	100	PRAGMA cache_size;
sl@0	101	PRAGMA default_cache_size;
sl@0	102	PRAGMA synchronous;
sl@0	103	}
sl@0	104	} [list 4321 $DFLT_CACHE_SZ 1]
sl@0	105	do_test pragma-1.7 {
sl@0	106	db close
sl@0	107	sqlite3 db test.db
sl@0	108	execsql {
sl@0	109	PRAGMA cache_size;
sl@0	110	PRAGMA default_cache_size;
sl@0	111	PRAGMA synchronous;
sl@0	112	}
sl@0	113	} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]
sl@0	114	do_test pragma-1.8 {
sl@0	115	execsql {
sl@0	116	PRAGMA default_cache_size=123;
sl@0	117	PRAGMA cache_size;
sl@0	118	PRAGMA default_cache_size;
sl@0	119	PRAGMA synchronous;
sl@0	120	}
sl@0	121	} {123 123 2}
sl@0	122	do_test pragma-1.9.1 {
sl@0	123	db close
sl@0	124	sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db]
sl@0	125	execsql {
sl@0	126	PRAGMA cache_size;
sl@0	127	PRAGMA default_cache_size;
sl@0	128	PRAGMA synchronous;
sl@0	129	}
sl@0	130	} {123 123 2}
sl@0	131	ifcapable vacuum {
sl@0	132	do_test pragma-1.9.2 {
sl@0	133	execsql {
sl@0	134	VACUUM;
sl@0	135	PRAGMA cache_size;
sl@0	136	PRAGMA default_cache_size;
sl@0	137	PRAGMA synchronous;
sl@0	138	}
sl@0	139	} {123 123 2}
sl@0	140	}
sl@0	141	do_test pragma-1.10 {
sl@0	142	execsql {
sl@0	143	PRAGMA synchronous=NORMAL;
sl@0	144	PRAGMA cache_size;
sl@0	145	PRAGMA default_cache_size;
sl@0	146	PRAGMA synchronous;
sl@0	147	}
sl@0	148	} {123 123 1}
sl@0	149	do_test pragma-1.11 {
sl@0	150	execsql {
sl@0	151	PRAGMA synchronous=FULL;
sl@0	152	PRAGMA cache_size;
sl@0	153	PRAGMA default_cache_size;
sl@0	154	PRAGMA synchronous;
sl@0	155	}
sl@0	156	} {123 123 2}
sl@0	157	do_test pragma-1.12 {
sl@0	158	db close
sl@0	159	sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db]
sl@0	160	execsql {
sl@0	161	PRAGMA cache_size;
sl@0	162	PRAGMA default_cache_size;
sl@0	163	PRAGMA synchronous;
sl@0	164	}
sl@0	165	} {123 123 2}
sl@0	166
sl@0	167	# Make sure the pragma handler understands numeric values in addition
sl@0	168	# to keywords like "off" and "full".
sl@0	169	#
sl@0	170	do_test pragma-1.13 {
sl@0	171	execsql {
sl@0	172	PRAGMA synchronous=0;
sl@0	173	PRAGMA synchronous;
sl@0	174	}
sl@0	175	} {0}
sl@0	176	do_test pragma-1.14 {
sl@0	177	execsql {
sl@0	178	PRAGMA synchronous=2;
sl@0	179	PRAGMA synchronous;
sl@0	180	}
sl@0	181	} {2}
sl@0	182	} ;# ifcapable pager_pragmas
sl@0	183
sl@0	184	# Test turning "flag" pragmas on and off.
sl@0	185	#
sl@0	186	ifcapable debug {
sl@0	187	# Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG
sl@0	188	#
sl@0	189	do_test pragma-1.15 {
sl@0	190	execsql {
sl@0	191	PRAGMA vdbe_listing=YES;
sl@0	192	PRAGMA vdbe_listing;
sl@0	193	}
sl@0	194	} {1}
sl@0	195	do_test pragma-1.16 {
sl@0	196	execsql {
sl@0	197	PRAGMA vdbe_listing=NO;
sl@0	198	PRAGMA vdbe_listing;
sl@0	199	}
sl@0	200	} {0}
sl@0	201	}
sl@0	202
sl@0	203	do_test pragma-1.17 {
sl@0	204	execsql {
sl@0	205	PRAGMA parser_trace=ON;
sl@0	206	PRAGMA parser_trace=OFF;
sl@0	207	}
sl@0	208	} {}
sl@0	209	do_test pragma-1.18 {
sl@0	210	execsql {
sl@0	211	PRAGMA bogus = -1234; -- Parsing of negative values
sl@0	212	}
sl@0	213	} {}
sl@0	214
sl@0	215	# Test modifying the safety_level of an attached database.
sl@0	216	ifcapable pager_pragmas&&attach {
sl@0	217	do_test pragma-2.1 {
sl@0	218	file delete -force test2.db
sl@0	219	file delete -force test2.db-journal
sl@0	220	execsql {
sl@0	221	ATTACH 'test2.db' AS aux;
sl@0	222	}
sl@0	223	} {}
sl@0	224	do_test pragma-2.2 {
sl@0	225	execsql {
sl@0	226	pragma aux.synchronous;
sl@0	227	}
sl@0	228	} {2}
sl@0	229	do_test pragma-2.3 {
sl@0	230	execsql {
sl@0	231	pragma aux.synchronous = OFF;
sl@0	232	pragma aux.synchronous;
sl@0	233	pragma synchronous;
sl@0	234	}
sl@0	235	} {0 2}
sl@0	236	do_test pragma-2.4 {
sl@0	237	execsql {
sl@0	238	pragma aux.synchronous = ON;
sl@0	239	pragma synchronous;
sl@0	240	pragma aux.synchronous;
sl@0	241	}
sl@0	242	} {2 1}
sl@0	243	} ;# ifcapable pager_pragmas
sl@0	244
sl@0	245	# Construct a corrupted index and make sure the integrity_check
sl@0	246	# pragma finds it.
sl@0	247	#
sl@0	248	# These tests won't work if the database is encrypted
sl@0	249	#
sl@0	250	do_test pragma-3.1 {
sl@0	251	db close
sl@0	252	file delete -force test.db test.db-journal
sl@0	253	sqlite3 db test.db
sl@0	254	execsql {
sl@0	255	PRAGMA auto_vacuum=OFF;
sl@0	256	BEGIN;
sl@0	257	CREATE TABLE t2(a,b,c);
sl@0	258	CREATE INDEX i2 ON t2(a);
sl@0	259	INSERT INTO t2 VALUES(11,2,3);
sl@0	260	INSERT INTO t2 VALUES(22,3,4);
sl@0	261	COMMIT;
sl@0	262	SELECT rowid, * from t2;
sl@0	263	}
sl@0	264	} {1 11 2 3 2 22 3 4}
sl@0	265	ifcapable attach {
sl@0	266	if {![sqlite3 -has-codec] && $sqlite_options(integrityck)} {
sl@0	267	do_test pragma-3.2 {
sl@0	268	db eval {SELECT rootpage FROM sqlite_master WHERE name='i2'} break
sl@0	269	set pgsz [db eval {PRAGMA page_size}]
sl@0	270	# overwrite the header on the rootpage of the index in order to
sl@0	271	# make the index appear to be empty.
sl@0	272	#
sl@0	273	set offset [expr {$pgsz*($rootpage-1)}]
sl@0	274	hexio_write test.db $offset 0a00000000040000000000
sl@0	275	db close
sl@0	276	sqlite3 db test.db
sl@0	277	execsql {PRAGMA integrity_check}
sl@0	278	} {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}
sl@0	279	do_test pragma-3.3 {
sl@0	280	execsql {PRAGMA integrity_check=1}
sl@0	281	} {{rowid 1 missing from index i2}}
sl@0	282	do_test pragma-3.4 {
sl@0	283	execsql {
sl@0	284	ATTACH DATABASE 'test.db' AS t2;
sl@0	285	PRAGMA integrity_check
sl@0	286	}
sl@0	287	} {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}
sl@0	288	do_test pragma-3.5 {
sl@0	289	execsql {
sl@0	290	PRAGMA integrity_check=4
sl@0	291	}
sl@0	292	} {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2}}
sl@0	293	do_test pragma-3.6 {
sl@0	294	execsql {
sl@0	295	PRAGMA integrity_check=xyz
sl@0	296	}
sl@0	297	} {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}
sl@0	298	do_test pragma-3.7 {
sl@0	299	execsql {
sl@0	300	PRAGMA integrity_check=0
sl@0	301	}
sl@0	302	} {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}
sl@0	303
sl@0	304	# Add additional corruption by appending unused pages to the end of
sl@0	305	# the database file testerr.db
sl@0	306	#
sl@0	307	do_test pragma-3.8 {
sl@0	308	execsql {DETACH t2}
sl@0	309	file delete -force testerr.db testerr.db-journal
sl@0	310	set out [open testerr.db w]
sl@0	311	fconfigure $out -translation binary
sl@0	312	set in [open test.db r]
sl@0	313	fconfigure $in -translation binary
sl@0	314	puts -nonewline $out [read $in]
sl@0	315	seek $in 0
sl@0	316	puts -nonewline $out [read $in]
sl@0	317	close $in
sl@0	318	close $out
sl@0	319	execsql {REINDEX t2}
sl@0	320	execsql {PRAGMA integrity_check}
sl@0	321	} {ok}
sl@0	322	do_test pragma-3.8.1 {
sl@0	323	execsql {PRAGMA quick_check}
sl@0	324	} {ok}
sl@0	325	do_test pragma-3.9 {
sl@0	326	execsql {
sl@0	327	ATTACH 'testerr.db' AS t2;
sl@0	328	PRAGMA integrity_check
sl@0	329	}
sl@0	330	} {{* in database t2 *
sl@0	331	Page 4 is never used
sl@0	332	Page 5 is never used
sl@0	333	Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}
sl@0	334	do_test pragma-3.10 {
sl@0	335	execsql {
sl@0	336	PRAGMA integrity_check=1
sl@0	337	}
sl@0	338	} {{* in database t2 *
sl@0	339	Page 4 is never used}}
sl@0	340	do_test pragma-3.11 {
sl@0	341	execsql {
sl@0	342	PRAGMA integrity_check=5
sl@0	343	}
sl@0	344	} {{* in database t2 *
sl@0	345	Page 4 is never used
sl@0	346	Page 5 is never used
sl@0	347	Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2}}
sl@0	348	do_test pragma-3.12 {
sl@0	349	execsql {
sl@0	350	PRAGMA integrity_check=4
sl@0	351	}
sl@0	352	} {{* in database t2 *
sl@0	353	Page 4 is never used
sl@0	354	Page 5 is never used
sl@0	355	Page 6 is never used} {rowid 1 missing from index i2}}
sl@0	356	do_test pragma-3.13 {
sl@0	357	execsql {
sl@0	358	PRAGMA integrity_check=3
sl@0	359	}
sl@0	360	} {{* in database t2 *
sl@0	361	Page 4 is never used
sl@0	362	Page 5 is never used
sl@0	363	Page 6 is never used}}
sl@0	364	do_test pragma-3.14 {
sl@0	365	execsql {
sl@0	366	PRAGMA integrity_check(2)
sl@0	367	}
sl@0	368	} {{* in database t2 *
sl@0	369	Page 4 is never used
sl@0	370	Page 5 is never used}}
sl@0	371	do_test pragma-3.15 {
sl@0	372	execsql {
sl@0	373	ATTACH 'testerr.db' AS t3;
sl@0	374	PRAGMA integrity_check
sl@0	375	}
sl@0	376	} {{* in database t2 *
sl@0	377	Page 4 is never used
sl@0	378	Page 5 is never used
sl@0	379	Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {* in database t3 *
sl@0	380	Page 4 is never used
sl@0	381	Page 5 is never used
sl@0	382	Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}
sl@0	383	do_test pragma-3.16 {
sl@0	384	execsql {
sl@0	385	PRAGMA integrity_check(10)
sl@0	386	}
sl@0	387	} {{* in database t2 *
sl@0	388	Page 4 is never used
sl@0	389	Page 5 is never used
sl@0	390	Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {* in database t3 *
sl@0	391	Page 4 is never used
sl@0	392	Page 5 is never used
sl@0	393	Page 6 is never used} {rowid 1 missing from index i2}}
sl@0	394	do_test pragma-3.17 {
sl@0	395	execsql {
sl@0	396	PRAGMA integrity_check=8
sl@0	397	}
sl@0	398	} {{* in database t2 *
sl@0	399	Page 4 is never used
sl@0	400	Page 5 is never used
sl@0	401	Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {* in database t3 *
sl@0	402	Page 4 is never used
sl@0	403	Page 5 is never used}}
sl@0	404	do_test pragma-3.18 {
sl@0	405	execsql {
sl@0	406	PRAGMA integrity_check=4
sl@0	407	}
sl@0	408	} {{* in database t2 *
sl@0	409	Page 4 is never used
sl@0	410	Page 5 is never used
sl@0	411	Page 6 is never used} {rowid 1 missing from index i2}}
sl@0	412	}
sl@0	413	do_test pragma-3.99 {
sl@0	414	catchsql {DETACH t3}
sl@0	415	catchsql {DETACH t2}
sl@0	416	file delete -force testerr.db testerr.db-journal
sl@0	417	catchsql {DROP INDEX i2}
sl@0	418	} {0 {}}
sl@0	419	}
sl@0	420
sl@0	421	# Test modifying the cache_size of an attached database.
sl@0	422	ifcapable pager_pragmas&&attach {
sl@0	423	do_test pragma-4.1 {
sl@0	424	execsql {
sl@0	425	ATTACH 'test2.db' AS aux;
sl@0	426	pragma aux.cache_size;
sl@0	427	pragma aux.default_cache_size;
sl@0	428	}
sl@0	429	} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
sl@0	430	do_test pragma-4.2 {
sl@0	431	execsql {
sl@0	432	pragma aux.cache_size = 50;
sl@0	433	pragma aux.cache_size;
sl@0	434	pragma aux.default_cache_size;
sl@0	435	}
sl@0	436	} [list 50 $DFLT_CACHE_SZ]
sl@0	437	do_test pragma-4.3 {
sl@0	438	execsql {
sl@0	439	pragma aux.default_cache_size = 456;
sl@0	440	pragma aux.cache_size;
sl@0	441	pragma aux.default_cache_size;
sl@0	442	}
sl@0	443	} {456 456}
sl@0	444	do_test pragma-4.4 {
sl@0	445	execsql {
sl@0	446	pragma cache_size;
sl@0	447	pragma default_cache_size;
sl@0	448	}
sl@0	449	} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
sl@0	450	do_test pragma-4.5 {
sl@0	451	execsql {
sl@0	452	DETACH aux;
sl@0	453	ATTACH 'test3.db' AS aux;
sl@0	454	pragma aux.cache_size;
sl@0	455	pragma aux.default_cache_size;
sl@0	456	}
sl@0	457	} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
sl@0	458	do_test pragma-4.6 {
sl@0	459	execsql {
sl@0	460	DETACH aux;
sl@0	461	ATTACH 'test2.db' AS aux;
sl@0	462	pragma aux.cache_size;
sl@0	463	pragma aux.default_cache_size;
sl@0	464	}
sl@0	465	} {456 456}
sl@0	466	} ;# ifcapable pager_pragmas
sl@0	467
sl@0	468	# Test that modifying the sync-level in the middle of a transaction is
sl@0	469	# disallowed.
sl@0	470	ifcapable pager_pragmas {
sl@0	471	do_test pragma-5.0 {
sl@0	472	execsql {
sl@0	473	pragma synchronous;
sl@0	474	}
sl@0	475	} {2}
sl@0	476	do_test pragma-5.1 {
sl@0	477	catchsql {
sl@0	478	BEGIN;
sl@0	479	pragma synchronous = OFF;
sl@0	480	}
sl@0	481	} {1 {Safety level may not be changed inside a transaction}}
sl@0	482	do_test pragma-5.2 {
sl@0	483	execsql {
sl@0	484	pragma synchronous;
sl@0	485	}
sl@0	486	} {2}
sl@0	487	catchsql {COMMIT;}
sl@0	488	} ;# ifcapable pager_pragmas
sl@0	489
sl@0	490	# Test schema-query pragmas
sl@0	491	#
sl@0	492	ifcapable schema_pragmas {
sl@0	493	ifcapable tempdb&&attach {
sl@0	494	do_test pragma-6.1 {
sl@0	495	set res {}
sl@0	496	execsql {SELECT * FROM sqlite_temp_master}
sl@0	497	foreach {idx name file} [execsql {pragma database_list}] {
sl@0	498	lappend res $idx $name
sl@0	499	}
sl@0	500	set res
sl@0	501	} {0 main 1 temp 2 aux}
sl@0	502	}
sl@0	503	do_test pragma-6.2 {
sl@0	504	execsql {
sl@0	505	pragma table_info(t2)
sl@0	506	}
sl@0	507	} {0 a {} 0 {} 0 1 b {} 0 {} 0 2 c {} 0 {} 0}
sl@0	508	db nullvalue <<NULL>>
sl@0	509	do_test pragma-6.2.2 {
sl@0	510	execsql {
sl@0	511	CREATE TABLE t5(
sl@0	512	a TEXT DEFAULT CURRENT_TIMESTAMP,
sl@0	513	b DEFAULT (5+3),
sl@0	514	c TEXT,
sl@0	515	d INTEGER DEFAULT NULL,
sl@0	516	e TEXT DEFAULT ''
sl@0	517	);
sl@0	518	PRAGMA table_info(t5);
sl@0	519	}
sl@0	520	} {0 a TEXT 0 CURRENT_TIMESTAMP 0 1 b {} 0 5+3 0 2 c TEXT 0 <<NULL>> 0 3 d INTEGER 0 NULL 0 4 e TEXT 0 '' 0}
sl@0	521	db nullvalue {}
sl@0	522	ifcapable {foreignkey} {
sl@0	523	do_test pragma-6.3 {
sl@0	524	execsql {
sl@0	525	CREATE TABLE t3(a int references t2(b), b UNIQUE);
sl@0	526	pragma foreign_key_list(t3);
sl@0	527	}
sl@0	528	} {0 0 t2 a b}
sl@0	529	do_test pragma-6.4 {
sl@0	530	execsql {
sl@0	531	pragma index_list(t3);
sl@0	532	}
sl@0	533	} {0 sqlite_autoindex_t3_1 1}
sl@0	534	}
sl@0	535	ifcapable {!foreignkey} {
sl@0	536	execsql {CREATE TABLE t3(a,b UNIQUE)}
sl@0	537	}
sl@0	538	do_test pragma-6.5 {
sl@0	539	execsql {
sl@0	540	CREATE INDEX t3i1 ON t3(a,b);
sl@0	541	pragma index_info(t3i1);
sl@0	542	}
sl@0	543	} {0 0 a 1 1 b}
sl@0	544
sl@0	545	ifcapable tempdb {
sl@0	546	# Test for ticket #3320. When a temp table of the same name exists, make
sl@0	547	# sure the schema of the main table can still be queried using
sl@0	548	# "pragma table_info":
sl@0	549	do_test pragma-6.6.1 {
sl@0	550	execsql {
sl@0	551	CREATE TABLE trial(col_main);
sl@0	552	CREATE TEMP TABLE trial(col_temp);
sl@0	553	}
sl@0	554	} {}
sl@0	555	do_test pragma-6.6.2 {
sl@0	556	execsql {
sl@0	557	PRAGMA table_info(trial);
sl@0	558	}
sl@0	559	} {0 col_temp {} 0 {} 0}
sl@0	560	do_test pragma-6.6.3 {
sl@0	561	execsql {
sl@0	562	PRAGMA temp.table_info(trial);
sl@0	563	}
sl@0	564	} {0 col_temp {} 0 {} 0}
sl@0	565	do_test pragma-6.6.4 {
sl@0	566	execsql {
sl@0	567	PRAGMA main.table_info(trial);
sl@0	568	}
sl@0	569	} {0 col_main {} 0 {} 0}
sl@0	570	}
sl@0	571	} ;# ifcapable schema_pragmas
sl@0	572	# Miscellaneous tests
sl@0	573	#
sl@0	574	ifcapable schema_pragmas {
sl@0	575	do_test pragma-7.1 {
sl@0	576	# Make sure a pragma knows to read the schema if it needs to
sl@0	577	db close
sl@0	578	sqlite3 db test.db
sl@0	579	execsql {
sl@0	580	pragma index_list(t3);
sl@0	581	}
sl@0	582	} {0 t3i1 0 1 sqlite_autoindex_t3_1 1}
sl@0	583	} ;# ifcapable schema_pragmas
sl@0	584	ifcapable {utf16} {
sl@0	585	do_test pragma-7.2 {
sl@0	586	db close
sl@0	587	sqlite3 db test.db
sl@0	588	catchsql {
sl@0	589	pragma encoding=bogus;
sl@0	590	}
sl@0	591	} {1 {unsupported encoding: bogus}}
sl@0	592	}
sl@0	593	ifcapable tempdb {
sl@0	594	do_test pragma-7.3 {
sl@0	595	db close
sl@0	596	sqlite3 db test.db
sl@0	597	execsql {
sl@0	598	pragma lock_status;
sl@0	599	}
sl@0	600	} {main unlocked temp closed}
sl@0	601	} else {
sl@0	602	do_test pragma-7.3 {
sl@0	603	db close
sl@0	604	sqlite3 db test.db
sl@0	605	execsql {
sl@0	606	pragma lock_status;
sl@0	607	}
sl@0	608	} {main unlocked}
sl@0	609	}
sl@0	610
sl@0	611
sl@0	612	#----------------------------------------------------------------------
sl@0	613	# Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA
sl@0	614	# user_version" statements.
sl@0	615	#
sl@0	616	# pragma-8.1: PRAGMA schema_version
sl@0	617	# pragma-8.2: PRAGMA user_version
sl@0	618	#
sl@0	619
sl@0	620	ifcapable schema_version {
sl@0	621
sl@0	622	# First check that we can set the schema version and then retrieve the
sl@0	623	# same value.
sl@0	624	do_test pragma-8.1.1 {
sl@0	625	execsql {
sl@0	626	PRAGMA schema_version = 105;
sl@0	627	}
sl@0	628	} {}
sl@0	629	do_test pragma-8.1.2 {
sl@0	630	execsql2 {
sl@0	631	PRAGMA schema_version;
sl@0	632	}
sl@0	633	} {schema_version 105}
sl@0	634	do_test pragma-8.1.3 {
sl@0	635	execsql {
sl@0	636	PRAGMA schema_version = 106;
sl@0	637	}
sl@0	638	} {}
sl@0	639	do_test pragma-8.1.4 {
sl@0	640	execsql {
sl@0	641	PRAGMA schema_version;
sl@0	642	}
sl@0	643	} 106
sl@0	644
sl@0	645	# Check that creating a table modifies the schema-version (this is really
sl@0	646	# to verify that the value being read is in fact the schema version).
sl@0	647	do_test pragma-8.1.5 {
sl@0	648	execsql {
sl@0	649	CREATE TABLE t4(a, b, c);
sl@0	650	INSERT INTO t4 VALUES(1, 2, 3);
sl@0	651	SELECT * FROM t4;
sl@0	652	}
sl@0	653	} {1 2 3}
sl@0	654	do_test pragma-8.1.6 {
sl@0	655	execsql {
sl@0	656	PRAGMA schema_version;
sl@0	657	}
sl@0	658	} 107
sl@0	659
sl@0	660	# Now open a second connection to the database. Ensure that changing the
sl@0	661	# schema-version using the first connection forces the second connection
sl@0	662	# to reload the schema. This has to be done using the C-API test functions,
sl@0	663	# because the TCL API accounts for SCHEMA_ERROR and retries the query.
sl@0	664	do_test pragma-8.1.7 {
sl@0	665	sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
sl@0	666	execsql {
sl@0	667	SELECT * FROM t4;
sl@0	668	} db2
sl@0	669	} {1 2 3}
sl@0	670	do_test pragma-8.1.8 {
sl@0	671	execsql {
sl@0	672	PRAGMA schema_version = 108;
sl@0	673	}
sl@0	674	} {}
sl@0	675	do_test pragma-8.1.9 {
sl@0	676	set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY]
sl@0	677	sqlite3_step $::STMT
sl@0	678	} SQLITE_ERROR
sl@0	679	do_test pragma-8.1.10 {
sl@0	680	sqlite3_finalize $::STMT
sl@0	681	} SQLITE_SCHEMA
sl@0	682
sl@0	683	# Make sure the schema-version can be manipulated in an attached database.
sl@0	684	file delete -force test2.db
sl@0	685	file delete -force test2.db-journal
sl@0	686	ifcapable attach {
sl@0	687	do_test pragma-8.1.11 {
sl@0	688	execsql {
sl@0	689	ATTACH 'test2.db' AS aux;
sl@0	690	CREATE TABLE aux.t1(a, b, c);
sl@0	691	PRAGMA aux.schema_version = 205;
sl@0	692	}
sl@0	693	} {}
sl@0	694	do_test pragma-8.1.12 {
sl@0	695	execsql {
sl@0	696	PRAGMA aux.schema_version;
sl@0	697	}
sl@0	698	} 205
sl@0	699	}
sl@0	700	do_test pragma-8.1.13 {
sl@0	701	execsql {
sl@0	702	PRAGMA schema_version;
sl@0	703	}
sl@0	704	} 108
sl@0	705
sl@0	706	# And check that modifying the schema-version in an attached database
sl@0	707	# forces the second connection to reload the schema.
sl@0	708	ifcapable attach {
sl@0	709	do_test pragma-8.1.14 {
sl@0	710	sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
sl@0	711	execsql {
sl@0	712	ATTACH 'test2.db' AS aux;
sl@0	713	SELECT * FROM aux.t1;
sl@0	714	} db2
sl@0	715	} {}
sl@0	716	do_test pragma-8.1.15 {
sl@0	717	execsql {
sl@0	718	PRAGMA aux.schema_version = 206;
sl@0	719	}
sl@0	720	} {}
sl@0	721	do_test pragma-8.1.16 {
sl@0	722	set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY]
sl@0	723	sqlite3_step $::STMT
sl@0	724	} SQLITE_ERROR
sl@0	725	do_test pragma-8.1.17 {
sl@0	726	sqlite3_finalize $::STMT
sl@0	727	} SQLITE_SCHEMA
sl@0	728	do_test pragma-8.1.18 {
sl@0	729	db2 close
sl@0	730	} {}
sl@0	731	}
sl@0	732
sl@0	733	# Now test that the user-version can be read and written (and that we aren't
sl@0	734	# accidentally manipulating the schema-version instead).
sl@0	735	do_test pragma-8.2.1 {
sl@0	736	execsql2 {
sl@0	737	PRAGMA user_version;
sl@0	738	}
sl@0	739	} {user_version 0}
sl@0	740	do_test pragma-8.2.2 {
sl@0	741	execsql {
sl@0	742	PRAGMA user_version = 2;
sl@0	743	}
sl@0	744	} {}
sl@0	745	do_test pragma-8.2.3.1 {
sl@0	746	execsql2 {
sl@0	747	PRAGMA user_version;
sl@0	748	}
sl@0	749	} {user_version 2}
sl@0	750	do_test pragma-8.2.3.2 {
sl@0	751	db close
sl@0	752	sqlite3 db test.db
sl@0	753	execsql {
sl@0	754	PRAGMA user_version;
sl@0	755	}
sl@0	756	} {2}
sl@0	757	do_test pragma-8.2.4.1 {
sl@0	758	execsql {
sl@0	759	PRAGMA schema_version;
sl@0	760	}
sl@0	761	} {108}
sl@0	762	ifcapable vacuum {
sl@0	763	do_test pragma-8.2.4.2 {
sl@0	764	execsql {
sl@0	765	VACUUM;
sl@0	766	PRAGMA user_version;
sl@0	767	}
sl@0	768	} {2}
sl@0	769	do_test pragma-8.2.4.3 {
sl@0	770	execsql {
sl@0	771	PRAGMA schema_version;
sl@0	772	}
sl@0	773	} {109}
sl@0	774	}
sl@0	775
sl@0	776	ifcapable attach {
sl@0	777	db eval {ATTACH 'test2.db' AS aux}
sl@0	778
sl@0	779	# Check that the user-version in the auxilary database can be manipulated (
sl@0	780	# and that we aren't accidentally manipulating the same in the main db).
sl@0	781	do_test pragma-8.2.5 {
sl@0	782	execsql {
sl@0	783	PRAGMA aux.user_version;
sl@0	784	}
sl@0	785	} {0}
sl@0	786	do_test pragma-8.2.6 {
sl@0	787	execsql {
sl@0	788	PRAGMA aux.user_version = 3;
sl@0	789	}
sl@0	790	} {}
sl@0	791	do_test pragma-8.2.7 {
sl@0	792	execsql {
sl@0	793	PRAGMA aux.user_version;
sl@0	794	}
sl@0	795	} {3}
sl@0	796	do_test pragma-8.2.8 {
sl@0	797	execsql {
sl@0	798	PRAGMA main.user_version;
sl@0	799	}
sl@0	800	} {2}
sl@0	801
sl@0	802	# Now check that a ROLLBACK resets the user-version if it has been modified
sl@0	803	# within a transaction.
sl@0	804	do_test pragma-8.2.9 {
sl@0	805	execsql {
sl@0	806	BEGIN;
sl@0	807	PRAGMA aux.user_version = 10;
sl@0	808	PRAGMA user_version = 11;
sl@0	809	}
sl@0	810	} {}
sl@0	811	do_test pragma-8.2.10 {
sl@0	812	execsql {
sl@0	813	PRAGMA aux.user_version;
sl@0	814	}
sl@0	815	} {10}
sl@0	816	do_test pragma-8.2.11 {
sl@0	817	execsql {
sl@0	818	PRAGMA main.user_version;
sl@0	819	}
sl@0	820	} {11}
sl@0	821	do_test pragma-8.2.12 {
sl@0	822	execsql {
sl@0	823	ROLLBACK;
sl@0	824	PRAGMA aux.user_version;
sl@0	825	}
sl@0	826	} {3}
sl@0	827	do_test pragma-8.2.13 {
sl@0	828	execsql {
sl@0	829	PRAGMA main.user_version;
sl@0	830	}
sl@0	831	} {2}
sl@0	832	}
sl@0	833
sl@0	834	# Try a negative value for the user-version
sl@0	835	do_test pragma-8.2.14 {
sl@0	836	execsql {
sl@0	837	PRAGMA user_version = -450;
sl@0	838	}
sl@0	839	} {}
sl@0	840	do_test pragma-8.2.15 {
sl@0	841	execsql {
sl@0	842	PRAGMA user_version;
sl@0	843	}
sl@0	844	} {-450}
sl@0	845	} ; # ifcapable schema_version
sl@0	846
sl@0	847	# Check to see if TEMP_STORE is memory or disk. Return strings
sl@0	848	# "memory" or "disk" as appropriate.
sl@0	849	#
sl@0	850	proc check_temp_store {} {
sl@0	851	db eval {CREATE TEMP TABLE IF NOT EXISTS a(b)}
sl@0	852	db eval {PRAGMA database_list} {
sl@0	853	if {$name=="temp"} {
sl@0	854	set bt [btree_from_db db 1]
sl@0	855	if {[btree_ismemdb $bt]} {
sl@0	856	return "memory"
sl@0	857	}
sl@0	858	return "disk"
sl@0	859	}
sl@0	860	}
sl@0	861	return "unknown"
sl@0	862	}
sl@0	863
sl@0	864
sl@0	865	# Test temp_store and temp_store_directory pragmas
sl@0	866	#
sl@0	867	ifcapable pager_pragmas {
sl@0	868	do_test pragma-9.1 {
sl@0	869	db close
sl@0	870	sqlite3 db test.db
sl@0	871	execsql {
sl@0	872	PRAGMA temp_store;
sl@0	873	}
sl@0	874	} {0}
sl@0	875	if {$TEMP_STORE<=1} {
sl@0	876	do_test pragma-9.1.1 {
sl@0	877	check_temp_store
sl@0	878	} {disk}
sl@0	879	} else {
sl@0	880	do_test pragma-9.1.1 {
sl@0	881	check_temp_store
sl@0	882	} {memory}
sl@0	883	}
sl@0	884
sl@0	885	do_test pragma-9.2 {
sl@0	886	db close
sl@0	887	sqlite3 db test.db
sl@0	888	execsql {
sl@0	889	PRAGMA temp_store=file;
sl@0	890	PRAGMA temp_store;
sl@0	891	}
sl@0	892	} {1}
sl@0	893	if {$TEMP_STORE==3} {
sl@0	894	# When TEMP_STORE is 3, always use memory regardless of pragma settings.
sl@0	895	do_test pragma-9.2.1 {
sl@0	896	check_temp_store
sl@0	897	} {memory}
sl@0	898	} else {
sl@0	899	do_test pragma-9.2.1 {
sl@0	900	check_temp_store
sl@0	901	} {disk}
sl@0	902	}
sl@0	903
sl@0	904	do_test pragma-9.3 {
sl@0	905	db close
sl@0	906	sqlite3 db test.db
sl@0	907	execsql {
sl@0	908	PRAGMA temp_store=memory;
sl@0	909	PRAGMA temp_store;
sl@0	910	}
sl@0	911	} {2}
sl@0	912	if {$TEMP_STORE==0} {
sl@0	913	# When TEMP_STORE is 0, always use the disk regardless of pragma settings.
sl@0	914	do_test pragma-9.3.1 {
sl@0	915	check_temp_store
sl@0	916	} {disk}
sl@0	917	} else {
sl@0	918	do_test pragma-9.3.1 {
sl@0	919	check_temp_store
sl@0	920	} {memory}
sl@0	921	}
sl@0	922
sl@0	923	do_test pragma-9.4 {
sl@0	924	execsql {
sl@0	925	PRAGMA temp_store_directory;
sl@0	926	}
sl@0	927	} {}
sl@0	928	ifcapable wsd {
sl@0	929	do_test pragma-9.5 {
sl@0	930	set pwd [string map {' ''} [file nativename [pwd]]]
sl@0	931	execsql "
sl@0	932	PRAGMA temp_store_directory='$pwd';
sl@0	933	"
sl@0	934	} {}
sl@0	935	do_test pragma-9.6 {
sl@0	936	execsql {
sl@0	937	PRAGMA temp_store_directory;
sl@0	938	}
sl@0	939	} [list [file nativename [pwd]]]
sl@0	940	do_test pragma-9.7 {
sl@0	941	catchsql {
sl@0	942	PRAGMA temp_store_directory='/NON/EXISTENT/PATH/FOOBAR';
sl@0	943	}
sl@0	944	} {1 {not a writable directory}}
sl@0	945	do_test pragma-9.8 {
sl@0	946	execsql {
sl@0	947	PRAGMA temp_store_directory='';
sl@0	948	}
sl@0	949	} {}
sl@0	950	if {![info exists TEMP_STORE] \|\| $TEMP_STORE<=1} {
sl@0	951	ifcapable tempdb {
sl@0	952	do_test pragma-9.9 {
sl@0	953	execsql {
sl@0	954	PRAGMA temp_store_directory;
sl@0	955	PRAGMA temp_store=FILE;
sl@0	956	CREATE TEMP TABLE temp_store_directory_test(a integer);
sl@0	957	INSERT INTO temp_store_directory_test values (2);
sl@0	958	SELECT * FROM temp_store_directory_test;
sl@0	959	}
sl@0	960	} {2}
sl@0	961	do_test pragma-9.10 {
sl@0	962	catchsql "
sl@0	963	PRAGMA temp_store_directory='$pwd';
sl@0	964	SELECT * FROM temp_store_directory_test;
sl@0	965	"
sl@0	966	} {1 {no such table: temp_store_directory_test}}
sl@0	967	}
sl@0	968	}
sl@0	969	}
sl@0	970	do_test pragma-9.11 {
sl@0	971	execsql {
sl@0	972	PRAGMA temp_store = 0;
sl@0	973	PRAGMA temp_store;
sl@0	974	}
sl@0	975	} {0}
sl@0	976	do_test pragma-9.12 {
sl@0	977	execsql {
sl@0	978	PRAGMA temp_store = 1;
sl@0	979	PRAGMA temp_store;
sl@0	980	}
sl@0	981	} {1}
sl@0	982	do_test pragma-9.13 {
sl@0	983	execsql {
sl@0	984	PRAGMA temp_store = 2;
sl@0	985	PRAGMA temp_store;
sl@0	986	}
sl@0	987	} {2}
sl@0	988	do_test pragma-9.14 {
sl@0	989	execsql {
sl@0	990	PRAGMA temp_store = 3;
sl@0	991	PRAGMA temp_store;
sl@0	992	}
sl@0	993	} {0}
sl@0	994	do_test pragma-9.15 {
sl@0	995	catchsql {
sl@0	996	BEGIN EXCLUSIVE;
sl@0	997	CREATE TEMP TABLE temp_table(t);
sl@0	998	INSERT INTO temp_table VALUES('valuable data');
sl@0	999	PRAGMA temp_store = 1;
sl@0	1000	}
sl@0	1001	} {1 {temporary storage cannot be changed from within a transaction}}
sl@0	1002	do_test pragma-9.16 {
sl@0	1003	execsql {
sl@0	1004	SELECT * FROM temp_table;
sl@0	1005	COMMIT;
sl@0	1006	}
sl@0	1007	} {{valuable data}}
sl@0	1008
sl@0	1009	do_test pragma-9.17 {
sl@0	1010	execsql {
sl@0	1011	INSERT INTO temp_table VALUES('valuable data II');
sl@0	1012	SELECT * FROM temp_table;
sl@0	1013	}
sl@0	1014	} {{valuable data} {valuable data II}}
sl@0	1015
sl@0	1016	do_test pragma-9.18 {
sl@0	1017	set rc [catch {
sl@0	1018	db eval {SELECT t FROM temp_table} {
sl@0	1019	execsql {pragma temp_store = 1}
sl@0	1020	}
sl@0	1021	} msg]
sl@0	1022	list $rc $msg
sl@0	1023	} {1 {temporary storage cannot be changed from within a transaction}}
sl@0	1024
sl@0	1025	} ;# ifcapable pager_pragmas
sl@0	1026
sl@0	1027	ifcapable trigger {
sl@0	1028
sl@0	1029	do_test pragma-10.0 {
sl@0	1030	catchsql {
sl@0	1031	DROP TABLE main.t1;
sl@0	1032	}
sl@0	1033	execsql {
sl@0	1034	PRAGMA count_changes = 1;
sl@0	1035
sl@0	1036	CREATE TABLE t1(a PRIMARY KEY);
sl@0	1037	CREATE TABLE t1_mirror(a);
sl@0	1038	CREATE TABLE t1_mirror2(a);
sl@0	1039	CREATE TRIGGER t1_bi BEFORE INSERT ON t1 BEGIN
sl@0	1040	INSERT INTO t1_mirror VALUES(new.a);
sl@0	1041	END;
sl@0	1042	CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN
sl@0	1043	INSERT INTO t1_mirror2 VALUES(new.a);
sl@0	1044	END;
sl@0	1045	CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN
sl@0	1046	UPDATE t1_mirror SET a = new.a WHERE a = old.a;
sl@0	1047	END;
sl@0	1048	CREATE TRIGGER t1_au AFTER UPDATE ON t1 BEGIN
sl@0	1049	UPDATE t1_mirror2 SET a = new.a WHERE a = old.a;
sl@0	1050	END;
sl@0	1051	CREATE TRIGGER t1_bd BEFORE DELETE ON t1 BEGIN
sl@0	1052	DELETE FROM t1_mirror WHERE a = old.a;
sl@0	1053	END;
sl@0	1054	CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN
sl@0	1055	DELETE FROM t1_mirror2 WHERE a = old.a;
sl@0	1056	END;
sl@0	1057	}
sl@0	1058	} {}
sl@0	1059
sl@0	1060	do_test pragma-10.1 {
sl@0	1061	execsql {
sl@0	1062	INSERT INTO t1 VALUES(randstr(10,10));
sl@0	1063	}
sl@0	1064	} {1}
sl@0	1065	do_test pragma-10.2 {
sl@0	1066	execsql {
sl@0	1067	UPDATE t1 SET a = randstr(10,10);
sl@0	1068	}
sl@0	1069	} {1}
sl@0	1070	do_test pragma-10.3 {
sl@0	1071	execsql {
sl@0	1072	DELETE FROM t1;
sl@0	1073	}
sl@0	1074	} {1}
sl@0	1075
sl@0	1076	} ;# ifcapable trigger
sl@0	1077
sl@0	1078	ifcapable schema_pragmas {
sl@0	1079	do_test pragma-11.1 {
sl@0	1080	execsql2 {
sl@0	1081	pragma collation_list;
sl@0	1082	}
sl@0	1083	} {seq 0 name NOCASE seq 1 name RTRIM seq 2 name BINARY}
sl@0	1084	do_test pragma-11.2 {
sl@0	1085	db collate New_Collation blah...
sl@0	1086	execsql {
sl@0	1087	pragma collation_list;
sl@0	1088	}
sl@0	1089	} {0 New_Collation 1 NOCASE 2 RTRIM 3 BINARY}
sl@0	1090	}
sl@0	1091
sl@0	1092	ifcapable schema_pragmas&&tempdb {
sl@0	1093	do_test pragma-12.1 {
sl@0	1094	sqlite3 db2 test.db
sl@0	1095	execsql {
sl@0	1096	PRAGMA temp.table_info('abc');
sl@0	1097	} db2
sl@0	1098	} {}
sl@0	1099	db2 close
sl@0	1100
sl@0	1101	do_test pragma-12.2 {
sl@0	1102	sqlite3 db2 test.db
sl@0	1103	execsql {
sl@0	1104	PRAGMA temp.default_cache_size = 200;
sl@0	1105	PRAGMA temp.default_cache_size;
sl@0	1106	} db2
sl@0	1107	} {200}
sl@0	1108	db2 close
sl@0	1109
sl@0	1110	do_test pragma-12.3 {
sl@0	1111	sqlite3 db2 test.db
sl@0	1112	execsql {
sl@0	1113	PRAGMA temp.cache_size = 400;
sl@0	1114	PRAGMA temp.cache_size;
sl@0	1115	} db2
sl@0	1116	} {400}
sl@0	1117	db2 close
sl@0	1118	}
sl@0	1119
sl@0	1120	ifcapable bloblit {
sl@0	1121
sl@0	1122	do_test pragma-13.1 {
sl@0	1123	execsql {
sl@0	1124	DROP TABLE IF EXISTS t4;
sl@0	1125	PRAGMA vdbe_trace=on;
sl@0	1126	PRAGMA vdbe_listing=on;
sl@0	1127	PRAGMA sql_trace=on;
sl@0	1128	CREATE TABLE t4(a INTEGER PRIMARY KEY,b);
sl@0	1129	INSERT INTO t4(b) VALUES(x'0123456789abcdef0123456789abcdef0123456789');
sl@0	1130	INSERT INTO t4(b) VALUES(randstr(30,30));
sl@0	1131	INSERT INTO t4(b) VALUES(1.23456);
sl@0	1132	INSERT INTO t4(b) VALUES(NULL);
sl@0	1133	INSERT INTO t4(b) VALUES(0);
sl@0	1134	INSERT INTO t4(b) SELECT b\|\|b\|\|b\|\|b FROM t4;
sl@0	1135	SELECT * FROM t4;
sl@0	1136	}
sl@0	1137	execsql {
sl@0	1138	PRAGMA vdbe_trace=off;
sl@0	1139	PRAGMA vdbe_listing=off;
sl@0	1140	PRAGMA sql_trace=off;
sl@0	1141	}
sl@0	1142	} {}
sl@0	1143
sl@0	1144	} ;# ifcapable bloblit
sl@0	1145
sl@0	1146	ifcapable pager_pragmas {
sl@0	1147	db close
sl@0	1148	file delete -force test.db
sl@0	1149	sqlite3 db test.db
sl@0	1150
sl@0	1151	do_test pragma-14.1 {
sl@0	1152	execsql { pragma auto_vacuum = 0 }
sl@0	1153	execsql { pragma page_count }
sl@0	1154	} {0}
sl@0	1155
sl@0	1156	do_test pragma-14.2 {
sl@0	1157	execsql {
sl@0	1158	CREATE TABLE abc(a, b, c);
sl@0	1159	PRAGMA page_count;
sl@0	1160	}
sl@0	1161	} {2}
sl@0	1162
sl@0	1163	do_test pragma-14.3 {
sl@0	1164	execsql {
sl@0	1165	BEGIN;
sl@0	1166	CREATE TABLE def(a, b, c);
sl@0	1167	PRAGMA page_count;
sl@0	1168	}
sl@0	1169	} {3}
sl@0	1170
sl@0	1171	do_test pragma-14.4 {
sl@0	1172	set page_size [db one {pragma page_size}]
sl@0	1173	expr [file size test.db] / $page_size
sl@0	1174	} {2}
sl@0	1175
sl@0	1176	do_test pragma-14.5 {
sl@0	1177	execsql {
sl@0	1178	ROLLBACK;
sl@0	1179	PRAGMA page_count;
sl@0	1180	}
sl@0	1181	} {2}
sl@0	1182
sl@0	1183	do_test pragma-14.6 {
sl@0	1184	file delete -force test2.db
sl@0	1185	sqlite3 db2 test2.db
sl@0	1186	execsql {
sl@0	1187	PRAGMA auto_vacuum = 0;
sl@0	1188	CREATE TABLE t1(a, b, c);
sl@0	1189	CREATE TABLE t2(a, b, c);
sl@0	1190	CREATE TABLE t3(a, b, c);
sl@0	1191	CREATE TABLE t4(a, b, c);
sl@0	1192	} db2
sl@0	1193	db2 close
sl@0	1194	execsql {
sl@0	1195	ATTACH 'test2.db' AS aux;
sl@0	1196	PRAGMA aux.page_count;
sl@0	1197	}
sl@0	1198	} {5}
sl@0	1199	}
sl@0	1200
sl@0	1201	# Test that the value set using the cache_size pragma is not reset when the
sl@0	1202	# schema is reloaded.
sl@0	1203	#
sl@0	1204	ifcapable pager_pragmas {
sl@0	1205	db close
sl@0	1206	sqlite3 db test.db
sl@0	1207	do_test pragma-15.1 {
sl@0	1208	execsql {
sl@0	1209	PRAGMA cache_size=59;
sl@0	1210	PRAGMA cache_size;
sl@0	1211	}
sl@0	1212	} {59}
sl@0	1213	do_test pragma-15.2 {
sl@0	1214	sqlite3 db2 test.db
sl@0	1215	execsql {
sl@0	1216	CREATE TABLE newtable(a, b, c);
sl@0	1217	} db2
sl@0	1218	db2 close
sl@0	1219	} {}
sl@0	1220	do_test pragma-15.3 {
sl@0	1221	# Evaluating this statement will cause the schema to be reloaded (because
sl@0	1222	# the schema was changed by another connection in pragma-15.2). At one
sl@0	1223	# point there was a bug that reset the cache_size to its default value
sl@0	1224	# when this happened.
sl@0	1225	execsql { SELECT * FROM sqlite_master }
sl@0	1226	execsql { PRAGMA cache_size }
sl@0	1227	} {59}
sl@0	1228	}
sl@0	1229
sl@0	1230	# Reset the sqlite3_temp_directory variable for the next run of tests:
sl@0	1231	sqlite3 dbX :memory:
sl@0	1232	dbX eval {PRAGMA temp_store_directory = ""}
sl@0	1233	dbX close
sl@0	1234
sl@0	1235	finish_test

author	sl@SLION-WIN7.fritz.box
	Fri, 15 Jun 2012 03:10:57 +0200
changeset 0	bde4ae8d615e
permissions	-rw-r--r--