Annotation of embedaddon/sqlite3/test/dbstatus.test, revision 1.1
1.1 ! misho 1: # 2010 March 10
! 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: #
! 12: # Tests for the sqlite3_db_status() function
! 13: #
! 14:
! 15: set testdir [file dirname $argv0]
! 16: source $testdir/tester.tcl
! 17:
! 18: # Memory statistics must be enabled for this test.
! 19: db close
! 20: sqlite3_shutdown
! 21: sqlite3_config_memstatus 1
! 22: sqlite3_initialize
! 23: sqlite3 db test.db
! 24:
! 25:
! 26: # Make sure sqlite3_db_config() and sqlite3_db_status are working.
! 27: #
! 28: unset -nocomplain PAGESZ
! 29: unset -nocomplain BASESZ
! 30: do_test dbstatus-1.1 {
! 31: db close
! 32: sqlite3 db :memory:
! 33: db eval {
! 34: CREATE TABLE t1(x);
! 35: }
! 36: set sz1 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_CACHE_USED 0] 1]
! 37: db eval {
! 38: CREATE TABLE t2(y);
! 39: }
! 40: set sz2 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_CACHE_USED 0] 1]
! 41: set ::PAGESZ [expr {$sz2-$sz1}]
! 42: set ::BASESZ [expr {$sz1-$::PAGESZ}]
! 43: expr {$::PAGESZ>1024 && $::PAGESZ<1300}
! 44: } {1}
! 45: do_test dbstatus-1.2 {
! 46: db eval {
! 47: INSERT INTO t1 VALUES(zeroblob(9000));
! 48: }
! 49: lindex [sqlite3_db_status db SQLITE_DBSTATUS_CACHE_USED 0] 1
! 50: } [expr {$BASESZ + 10*$PAGESZ}]
! 51:
! 52:
! 53: proc lookaside {db} {
! 54: expr { $::lookaside_buffer_size *
! 55: [lindex [sqlite3_db_status $db SQLITE_DBSTATUS_LOOKASIDE_USED 0] 1]
! 56: }
! 57: }
! 58:
! 59: ifcapable stat3 {
! 60: set STAT3 1
! 61: } else {
! 62: set STAT3 0
! 63: }
! 64:
! 65: ifcapable malloc_usable_size {
! 66: finish_test
! 67: return
! 68: }
! 69:
! 70: #---------------------------------------------------------------------------
! 71: # Run the dbstatus-2 and dbstatus-3 tests with several of different
! 72: # lookaside buffer sizes.
! 73: #
! 74: foreach ::lookaside_buffer_size {0 64 120} {
! 75:
! 76: # Do not run any of these tests if there is SQL configured to run
! 77: # as part of the [sqlite3] command. This prevents the script from
! 78: # configuring the size of the lookaside buffer after [sqlite3] has
! 79: # returned.
! 80: if {[presql] != ""} break
! 81:
! 82: #-------------------------------------------------------------------------
! 83: # Tests for SQLITE_DBSTATUS_SCHEMA_USED.
! 84: #
! 85: # Each test in the following block works as follows. Each test uses a
! 86: # different database schema.
! 87: #
! 88: # 1. Open a connection to an empty database. Disable statement caching.
! 89: #
! 90: # 2. Execute the SQL to create the database schema. Measure the total
! 91: # heap and lookaside memory allocated by SQLite, and the memory
! 92: # allocated for the database schema according to sqlite3_db_status().
! 93: #
! 94: # 3. Drop all tables in the database schema. Measure the total memory
! 95: # and the schema memory again.
! 96: #
! 97: # 4. Repeat step 2.
! 98: #
! 99: # 5. Repeat step 3.
! 100: #
! 101: # Then test that:
! 102: #
! 103: # a) The difference in schema memory quantities in steps 2 and 3 is the
! 104: # same as the difference in total memory in steps 2 and 3.
! 105: #
! 106: # b) Step 4 reports the same amount of schema and total memory used as
! 107: # in step 2.
! 108: #
! 109: # c) Step 5 reports the same amount of schema and total memory used as
! 110: # in step 3.
! 111: #
! 112: foreach {tn schema} {
! 113: 1 { CREATE TABLE t1(a, b) }
! 114: 2 { CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1, c UNIQUE) }
! 115: 3 {
! 116: CREATE TABLE t1(a, b);
! 117: CREATE INDEX i1 ON t1(a, b);
! 118: }
! 119: 4 {
! 120: CREATE TABLE t1(a, b);
! 121: CREATE TABLE t2(c, d);
! 122: CREATE TRIGGER AFTER INSERT ON t1 BEGIN
! 123: INSERT INTO t2 VALUES(new.a, new.b);
! 124: SELECT * FROM t1, t2 WHERE a=c AND b=d GROUP BY b HAVING a>5 ORDER BY a;
! 125: END;
! 126: }
! 127: 5 {
! 128: CREATE TABLE t1(a, b);
! 129: CREATE TABLE t2(c, d);
! 130: CREATE VIEW v1 AS SELECT * FROM t1 UNION SELECT * FROM t2;
! 131: }
! 132: 6y {
! 133: CREATE TABLE t1(a, b);
! 134: CREATE INDEX i1 ON t1(a);
! 135: CREATE INDEX i2 ON t1(a,b);
! 136: CREATE INDEX i3 ON t1(b,b);
! 137: INSERT INTO t1 VALUES(randomblob(20), randomblob(25));
! 138: INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1;
! 139: INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1;
! 140: INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1;
! 141: ANALYZE;
! 142: }
! 143: 7 {
! 144: CREATE TABLE t1(a, b);
! 145: CREATE TABLE t2(c, d);
! 146: CREATE VIEW v1 AS
! 147: SELECT * FROM t1
! 148: UNION
! 149: SELECT * FROM t2
! 150: UNION ALL
! 151: SELECT c||b, d||a FROM t2 LEFT OUTER JOIN t1 GROUP BY c, d
! 152: ORDER BY 1, 2
! 153: ;
! 154: CREATE TRIGGER tr1 INSTEAD OF INSERT ON v1 BEGIN
! 155: SELECT * FROM v1;
! 156: UPDATE t1 SET a=5, b=(SELECT c FROM t2);
! 157: END;
! 158: SELECT * FROM v1;
! 159: }
! 160: 8x {
! 161: CREATE TABLE t1(a, b, UNIQUE(a, b));
! 162: CREATE VIRTUAL TABLE t2 USING echo(t1);
! 163: }
! 164: } {
! 165: set tn "$::lookaside_buffer_size-$tn"
! 166:
! 167: # Step 1.
! 168: db close
! 169: forcedelete test.db
! 170: sqlite3 db test.db
! 171: sqlite3_db_config_lookaside db 0 $::lookaside_buffer_size 500
! 172: db cache size 0
! 173:
! 174: catch { register_echo_module db }
! 175: ifcapable !vtab { if {[string match *x $tn]} continue }
! 176:
! 177: # Step 2.
! 178: execsql $schema
! 179: set nAlloc1 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]
! 180: incr nAlloc1 [lookaside db]
! 181: set nSchema1 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1]
! 182:
! 183: # Step 3.
! 184: drop_all_tables
! 185: set nAlloc2 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]
! 186: incr nAlloc2 [lookaside db]
! 187: set nSchema2 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1]
! 188:
! 189: # Step 4.
! 190: execsql $schema
! 191: set nAlloc3 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]
! 192: incr nAlloc3 [lookaside db]
! 193: set nSchema3 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1]
! 194:
! 195: # Step 5.
! 196: drop_all_tables
! 197: set nAlloc4 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]
! 198: incr nAlloc4 [lookaside db]
! 199: set nSchema4 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1]
! 200: set nFree [expr {$nAlloc1-$nAlloc2}]
! 201:
! 202: # Tests for which the test name ends in an "x" report slightly less
! 203: # memory than is actually freed when all schema items are finalized.
! 204: # This is because memory allocated by virtual table implementations
! 205: # for any reason is not counted as "schema memory".
! 206: #
! 207: # Additionally, in auto-vacuum mode, dropping tables and indexes causes
! 208: # the page-cache to shrink. So the amount of memory freed is always
! 209: # much greater than just that reported by DBSTATUS_SCHEMA_USED in this
! 210: # case.
! 211: #
! 212: # Some of the memory used for sqlite_stat3 is unaccounted for by
! 213: # dbstatus.
! 214: #
! 215: # Finally, on osx the estimate of memory used by the schema may be
! 216: # slightly low.
! 217: #
! 218: if {[string match *x $tn] || $AUTOVACUUM
! 219: || ([string match *y $tn] && $STAT3)
! 220: || ($::tcl_platform(os) == "Darwin")
! 221: } {
! 222: do_test dbstatus-2.$tn.ax { expr {($nSchema1-$nSchema2)<=$nFree} } 1
! 223: } else {
! 224: do_test dbstatus-2.$tn.a { expr {$nSchema1-$nSchema2} } $nFree
! 225: }
! 226:
! 227: do_test dbstatus-2.$tn.b { list $nAlloc1 $nSchema1 } "$nAlloc3 $nSchema3"
! 228: do_test dbstatus-2.$tn.c { list $nAlloc2 $nSchema2 } "$nAlloc4 $nSchema4"
! 229: }
! 230:
! 231: #-------------------------------------------------------------------------
! 232: # Tests for SQLITE_DBSTATUS_STMT_USED.
! 233: #
! 234: # Each test in the following block works as follows. Each test uses a
! 235: # different database schema.
! 236: #
! 237: # 1. Open a connection to an empty database. Initialized the database
! 238: # schema.
! 239: #
! 240: # 2. Prepare a bunch of SQL statements. Measure the total heap and
! 241: # lookaside memory allocated by SQLite, and the memory allocated
! 242: # for the prepared statements according to sqlite3_db_status().
! 243: #
! 244: # 3. Finalize all prepared statements Measure the total memory
! 245: # and the prepared statement memory again.
! 246: #
! 247: # 4. Repeat step 2.
! 248: #
! 249: # 5. Repeat step 3.
! 250: #
! 251: # Then test that:
! 252: #
! 253: # a) The difference in schema memory quantities in steps 2 and 3 is the
! 254: # same as the difference in total memory in steps 2 and 3.
! 255: #
! 256: # b) Step 4 reports the same amount of schema and total memory used as
! 257: # in step 2.
! 258: #
! 259: # c) Step 5 reports the same amount of schema and total memory used as
! 260: # in step 3.
! 261: #
! 262: foreach {tn schema statements} {
! 263: 1 { CREATE TABLE t1(a, b) } {
! 264: SELECT * FROM t1;
! 265: INSERT INTO t1 VALUES(1, 2);
! 266: INSERT INTO t1 SELECT * FROM t1;
! 267: UPDATE t1 SET a=5;
! 268: DELETE FROM t1;
! 269: }
! 270: 2 {
! 271: PRAGMA recursive_triggers = 1;
! 272: CREATE TABLE t1(a, b);
! 273: CREATE TRIGGER tr1 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN
! 274: INSERT INTO t1 VALUES(new.a-1, new.b);
! 275: END;
! 276: } {
! 277: INSERT INTO t1 VALUES(5, 'x');
! 278: }
! 279: 3 {
! 280: PRAGMA recursive_triggers = 1;
! 281: CREATE TABLE t1(a, b);
! 282: CREATE TABLE t2(a, b);
! 283: CREATE TRIGGER tr1 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN
! 284: INSERT INTO t2 VALUES(new.a-1, new.b);
! 285: END;
! 286: CREATE TRIGGER tr2 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN
! 287: INSERT INTO t1 VALUES(new.a-1, new.b);
! 288: END;
! 289: } {
! 290: INSERT INTO t1 VALUES(10, 'x');
! 291: }
! 292: 4 {
! 293: CREATE TABLE t1(a, b);
! 294: } {
! 295: SELECT count(*) FROM t1 WHERE upper(a)='ABC';
! 296: }
! 297: 5x {
! 298: CREATE TABLE t1(a, b UNIQUE);
! 299: CREATE VIRTUAL TABLE t2 USING echo(t1);
! 300: } {
! 301: SELECT count(*) FROM t2;
! 302: SELECT * FROM t2 WHERE b>5;
! 303: SELECT * FROM t2 WHERE b='abcdefg';
! 304: }
! 305: } {
! 306: set tn "$::lookaside_buffer_size-$tn"
! 307:
! 308: # Step 1.
! 309: db close
! 310: forcedelete test.db
! 311: sqlite3 db test.db
! 312: sqlite3_db_config_lookaside db 0 $::lookaside_buffer_size 500
! 313: db cache size 1000
! 314:
! 315: catch { register_echo_module db }
! 316: ifcapable !vtab { if {[string match *x $tn]} continue }
! 317:
! 318: execsql $schema
! 319: db cache flush
! 320:
! 321: # Step 2.
! 322: execsql $statements
! 323: set nAlloc1 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]
! 324: incr nAlloc1 [lookaside db]
! 325: set nStmt1 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1]
! 326: execsql $statements
! 327:
! 328: # Step 3.
! 329: db cache flush
! 330: set nAlloc2 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]
! 331: incr nAlloc2 [lookaside db]
! 332: set nStmt2 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1]
! 333:
! 334: # Step 3.
! 335: execsql $statements
! 336: set nAlloc3 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]
! 337: incr nAlloc3 [lookaside db]
! 338: set nStmt3 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1]
! 339: execsql $statements
! 340:
! 341: # Step 4.
! 342: db cache flush
! 343: set nAlloc4 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]
! 344: incr nAlloc4 [lookaside db]
! 345: set nStmt4 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1]
! 346:
! 347: set nFree [expr {$nAlloc1-$nAlloc2}]
! 348:
! 349: do_test dbstatus-3.$tn.a { expr $nStmt2 } {0}
! 350:
! 351: # Tests for which the test name ends in an "x" report slightly less
! 352: # memory than is actually freed when all statements are finalized.
! 353: # This is because a small amount of memory allocated by a virtual table
! 354: # implementation using sqlite3_mprintf() is technically considered
! 355: # external and so is not counted as "statement memory".
! 356: #
! 357: #puts "$nStmt1 $nFree"
! 358: if {[string match *x $tn]} {
! 359: do_test dbstatus-3.$tn.bx { expr $nStmt1<=$nFree } {1}
! 360: } else {
! 361: do_test dbstatus-3.$tn.b { expr $nStmt1==$nFree } {1}
! 362: }
! 363:
! 364: do_test dbstatus-3.$tn.c { list $nAlloc1 $nStmt1 } [list $nAlloc3 $nStmt3]
! 365: do_test dbstatus-3.$tn.d { list $nAlloc2 $nStmt2 } [list $nAlloc4 $nStmt4]
! 366: }
! 367: }
! 368:
! 369: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to dbstatus.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test