1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# 2001 September 15 37c478bd9Sstevel@tonic-gate# 47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 67c478bd9Sstevel@tonic-gate# 77c478bd9Sstevel@tonic-gate# May you do good and not evil. 87c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 97c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate#*********************************************************************** 127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library. The 137c478bd9Sstevel@tonic-gate# focus of this file is testing the VACUUM statement. 147c478bd9Sstevel@tonic-gate# 157c478bd9Sstevel@tonic-gate# $Id: vacuum.test,v 1.15 2004/02/14 16:31:04 drh Exp $ 167c478bd9Sstevel@tonic-gate 177c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 187c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 197c478bd9Sstevel@tonic-gate 207c478bd9Sstevel@tonic-gateproc cksum {{db db}} { 217c478bd9Sstevel@tonic-gate set txt [$db eval {SELECT name, type, sql FROM sqlite_master}]\n 227c478bd9Sstevel@tonic-gate foreach tbl [$db eval {SELECT name FROM sqlite_master WHERE type='table'}] { 237c478bd9Sstevel@tonic-gate append txt [$db eval "SELECT * FROM $tbl"]\n 247c478bd9Sstevel@tonic-gate } 257c478bd9Sstevel@tonic-gate foreach prag {default_synchronous default_cache_size} { 267c478bd9Sstevel@tonic-gate append txt $prag-[$db eval "PRAGMA $prag"]\n 277c478bd9Sstevel@tonic-gate } 287c478bd9Sstevel@tonic-gate set cksum [string length $txt]-[md5 $txt] 297c478bd9Sstevel@tonic-gate # puts $cksum-[file size test.db] 307c478bd9Sstevel@tonic-gate return $cksum 317c478bd9Sstevel@tonic-gate} 327c478bd9Sstevel@tonic-gatedo_test vacuum-1.1 { 337c478bd9Sstevel@tonic-gate execsql { 347c478bd9Sstevel@tonic-gate BEGIN; 357c478bd9Sstevel@tonic-gate CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 367c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(NULL,randstr(10,100),randstr(5,50)); 377c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(123456,randstr(10,100),randstr(5,50)); 387c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 397c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 407c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 417c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 427c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 437c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 447c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT NULL, b||'-'||rowid, c||'-'||rowid FROM t1; 457c478bd9Sstevel@tonic-gate CREATE INDEX i1 ON t1(b,c); 467c478bd9Sstevel@tonic-gate CREATE TABLE t2 AS SELECT * FROM t1; 477c478bd9Sstevel@tonic-gate COMMIT; 487c478bd9Sstevel@tonic-gate DROP TABLE t2; 497c478bd9Sstevel@tonic-gate } 507c478bd9Sstevel@tonic-gate set ::size1 [file size test.db] 517c478bd9Sstevel@tonic-gate set ::cksum [cksum] 527c478bd9Sstevel@tonic-gate expr {$::cksum!=""} 537c478bd9Sstevel@tonic-gate} {1} 547c478bd9Sstevel@tonic-gatedo_test vacuum-1.2 { 557c478bd9Sstevel@tonic-gate execsql { 567c478bd9Sstevel@tonic-gate VACUUM; 577c478bd9Sstevel@tonic-gate } 587c478bd9Sstevel@tonic-gate cksum 597c478bd9Sstevel@tonic-gate} $cksum 607c478bd9Sstevel@tonic-gatedo_test vacuum-1.3 { 617c478bd9Sstevel@tonic-gate expr {[file size test.db]<$::size1} 627c478bd9Sstevel@tonic-gate} {1} 637c478bd9Sstevel@tonic-gatedo_test vacuum-1.4 { 647c478bd9Sstevel@tonic-gate execsql { 657c478bd9Sstevel@tonic-gate BEGIN; 667c478bd9Sstevel@tonic-gate CREATE TABLE t2 AS SELECT * FROM t1; 677c478bd9Sstevel@tonic-gate CREATE TABLE t3 AS SELECT * FROM t1; 687c478bd9Sstevel@tonic-gate CREATE VIEW v1 AS SELECT b, c FROM t3; 697c478bd9Sstevel@tonic-gate CREATE TRIGGER r1 AFTER DELETE ON t2 BEGIN 707c478bd9Sstevel@tonic-gate SELECT 1; 717c478bd9Sstevel@tonic-gate END; 727c478bd9Sstevel@tonic-gate COMMIT; 737c478bd9Sstevel@tonic-gate DROP TABLE t2; 747c478bd9Sstevel@tonic-gate } 757c478bd9Sstevel@tonic-gate set ::size1 [file size test.db] 767c478bd9Sstevel@tonic-gate set ::cksum [cksum] 777c478bd9Sstevel@tonic-gate expr {$::cksum!=""} 787c478bd9Sstevel@tonic-gate} {1} 797c478bd9Sstevel@tonic-gatedo_test vacuum-1.5 { 807c478bd9Sstevel@tonic-gate execsql { 817c478bd9Sstevel@tonic-gate VACUUM; 827c478bd9Sstevel@tonic-gate } 837c478bd9Sstevel@tonic-gate cksum 847c478bd9Sstevel@tonic-gate} $cksum 857c478bd9Sstevel@tonic-gatedo_test vacuum-1.6 { 867c478bd9Sstevel@tonic-gate expr {[file size test.db]<$::size1} 877c478bd9Sstevel@tonic-gate} {1} 887c478bd9Sstevel@tonic-gate 897c478bd9Sstevel@tonic-gatedo_test vacuum-2.1 { 907c478bd9Sstevel@tonic-gate catchsql { 917c478bd9Sstevel@tonic-gate BEGIN; 927c478bd9Sstevel@tonic-gate VACUUM; 937c478bd9Sstevel@tonic-gate COMMIT; 947c478bd9Sstevel@tonic-gate } 957c478bd9Sstevel@tonic-gate} {1 {cannot VACUUM from within a transaction}} 967c478bd9Sstevel@tonic-gatecatch {db eval COMMIT} 977c478bd9Sstevel@tonic-gatedo_test vacuum-2.2 { 987c478bd9Sstevel@tonic-gate sqlite db2 test.db 997c478bd9Sstevel@tonic-gate execsql { 1007c478bd9Sstevel@tonic-gate BEGIN; 1017c478bd9Sstevel@tonic-gate CREATE TABLE t4 AS SELECT * FROM t1; 1027c478bd9Sstevel@tonic-gate CREATE TABLE t5 AS SELECT * FROM t1; 1037c478bd9Sstevel@tonic-gate COMMIT; 1047c478bd9Sstevel@tonic-gate DROP TABLE t4; 1057c478bd9Sstevel@tonic-gate DROP TABLE t5; 1067c478bd9Sstevel@tonic-gate } db2 1077c478bd9Sstevel@tonic-gate set ::cksum [cksum db2] 1087c478bd9Sstevel@tonic-gate catchsql { 1097c478bd9Sstevel@tonic-gate VACUUM 1107c478bd9Sstevel@tonic-gate } 1117c478bd9Sstevel@tonic-gate} {0 {}} 1127c478bd9Sstevel@tonic-gatedo_test vacuum-2.3 { 1137c478bd9Sstevel@tonic-gate cksum 1147c478bd9Sstevel@tonic-gate} $cksum 1157c478bd9Sstevel@tonic-gatedo_test vacuum-2.4 { 1167c478bd9Sstevel@tonic-gate catch {db2 eval {SELECT count(*) FROM sqlite_master}} 1177c478bd9Sstevel@tonic-gate cksum db2 1187c478bd9Sstevel@tonic-gate} $cksum 1197c478bd9Sstevel@tonic-gate 1207c478bd9Sstevel@tonic-gate# Ticket #427. Make sure VACUUM works when the EMPTY_RESULT_CALLBACKS 1217c478bd9Sstevel@tonic-gate# pragma is turned on. 1227c478bd9Sstevel@tonic-gate# 1237c478bd9Sstevel@tonic-gatedo_test vacuum-3.1 { 1247c478bd9Sstevel@tonic-gate db close 1257c478bd9Sstevel@tonic-gate db2 close 1267c478bd9Sstevel@tonic-gate file delete test.db 1277c478bd9Sstevel@tonic-gate sqlite db test.db 1287c478bd9Sstevel@tonic-gate execsql { 1297c478bd9Sstevel@tonic-gate PRAGMA empty_result_callbacks=on; 1307c478bd9Sstevel@tonic-gate VACUUM; 1317c478bd9Sstevel@tonic-gate } 1327c478bd9Sstevel@tonic-gate} {} 1337c478bd9Sstevel@tonic-gate 1347c478bd9Sstevel@tonic-gate# Ticket #464. Make sure VACUUM works with the sqlite_compile() API. 1357c478bd9Sstevel@tonic-gate# 1367c478bd9Sstevel@tonic-gatedo_test vacuum-4.1 { 1377c478bd9Sstevel@tonic-gate db close 1387c478bd9Sstevel@tonic-gate set DB [sqlite db test.db] 1397c478bd9Sstevel@tonic-gate set VM [sqlite_compile $DB {VACUUM} TAIL] 1407c478bd9Sstevel@tonic-gate sqlite_step $VM N VALUES COLNAMES 1417c478bd9Sstevel@tonic-gate} {SQLITE_DONE} 1427c478bd9Sstevel@tonic-gatedo_test vacuum-4.2 { 1437c478bd9Sstevel@tonic-gate sqlite_finalize $VM 1447c478bd9Sstevel@tonic-gate} {} 1457c478bd9Sstevel@tonic-gate 1467c478bd9Sstevel@tonic-gate# Ticket #515. VACUUM after deleting and recreating the table that 1477c478bd9Sstevel@tonic-gate# a view refers to. 1487c478bd9Sstevel@tonic-gate# 1497c478bd9Sstevel@tonic-gatedo_test vacuum-5.1 { 1507c478bd9Sstevel@tonic-gate db close 1517c478bd9Sstevel@tonic-gate file delete -force test.db 1527c478bd9Sstevel@tonic-gate sqlite db test.db 1537c478bd9Sstevel@tonic-gate catchsql { 1547c478bd9Sstevel@tonic-gate CREATE TABLE Test (TestID int primary key); 1557c478bd9Sstevel@tonic-gate INSERT INTO Test VALUES (NULL); 1567c478bd9Sstevel@tonic-gate CREATE VIEW viewTest AS SELECT * FROM Test; 1577c478bd9Sstevel@tonic-gate 1587c478bd9Sstevel@tonic-gate BEGIN; 1597c478bd9Sstevel@tonic-gate CREATE TEMP TABLE tempTest (TestID int primary key, Test2 int NULL); 1607c478bd9Sstevel@tonic-gate INSERT INTO tempTest SELECT TestID, 1 FROM Test; 1617c478bd9Sstevel@tonic-gate DROP TABLE Test; 1627c478bd9Sstevel@tonic-gate CREATE TABLE Test(TestID int primary key, Test2 int NULL); 1637c478bd9Sstevel@tonic-gate INSERT INTO Test SELECT * FROM tempTest; 1647c478bd9Sstevel@tonic-gate COMMIT; 1657c478bd9Sstevel@tonic-gate VACUUM; 1667c478bd9Sstevel@tonic-gate } 1677c478bd9Sstevel@tonic-gate} {0 {}} 1687c478bd9Sstevel@tonic-gatedo_test vacuum-5.2 { 1697c478bd9Sstevel@tonic-gate catchsql { 1707c478bd9Sstevel@tonic-gate VACUUM; 1717c478bd9Sstevel@tonic-gate } 1727c478bd9Sstevel@tonic-gate} {0 {}} 1737c478bd9Sstevel@tonic-gate 1747c478bd9Sstevel@tonic-gate# finish_test 175