/ Artifact Content
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Artifact 48c3a5a98687d67ef06fc16d2e603284756bbec3:


# 2010 September 1
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/malloc_common.tcl

db close

do_test quota-1.1 { sqlite3_quota_initialize nosuchvfs 1 } {SQLITE_ERROR}
do_test quota-1.2 { sqlite3_quota_initialize "" 1 }        {SQLITE_OK}
do_test quota-1.3 { sqlite3_quota_initialize "" 1 }        {SQLITE_MISUSE}
do_test quota-1.4 { sqlite3_quota_shutdown }               {SQLITE_OK}

do_test quota-1.5 { sqlite3_quota_initialize "" 0 }        {SQLITE_OK}
do_test quota-1.6 { sqlite3_quota_shutdown }               {SQLITE_OK}
do_test quota-1.7 { sqlite3_quota_initialize "" 1 }        {SQLITE_OK}
do_test quota-1.8 { sqlite3_quota_shutdown }               {SQLITE_OK}


#-------------------------------------------------------------------------
# Some simple warm-body tests with a single database file in rollback 
# mode:
#
#   quota-2.1.*: Test that SQLITE_FULL is returned if the database would
#                exceed the configured quota.
#
#   quota-2.2.*: Test that SQLITE_FULL is not returned and the database
#                grows if the callback extends the quota when the database
#                attempts to grow beyond the configured quota.
#
#   quota-2.3.*: Open and close a db that is not part of any quota group. At
#                one point this was causing mutex refs to be leaked.
#
#   quota-2.4.*: Try to shutdown the quota system before closing the db
#                file. Check that this fails and the quota system still works
#                afterwards. Then close the database and successfully shut
#                down the quota system.
#   
sqlite3_quota_initialize "" 1

proc quota_check {filename limitvar size} {
  upvar $limitvar limit

  lappend ::quota [set limit] $size
  if {[info exists ::quota_request_ok]} { set limit $size }
}

do_test quota-2.1.1 {
  sqlite3_quota_set *test.db 4096 quota_check
} {SQLITE_OK}
do_test quota-2.1.2 {
  sqlite3 db test.db
  execsql {
    PRAGMA page_size=1024;
    PRAGMA auto_vacuum=OFF;
    PRAGMA journal_mode=DELETE;
  }
  set ::quota [list]
  execsql {
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(1, randomblob(1100));
    INSERT INTO t1 VALUES(2, randomblob(1100));
  }
  set ::quota
} {}
do_test quota-2.1.3 { file size test.db } {4096}
do_test quota-2.1.4 {
  catchsql { INSERT INTO t1 VALUES(3, randomblob(1100)) }
} {1 {database or disk is full}}
do_test quota-2.1.5 { set ::quota } {4096 5120}

set ::quota_request_ok 1
set ::quota [list]
do_test quota-2.2.1 {
  execsql { INSERT INTO t1 VALUES(3, randomblob(1100)) }
} {}
do_test quota-2.2.2 { set ::quota } {4096 5120}
do_test quota-2.2.3 { file size test.db } {5120}
unset ::quota_request_ok

do_test quota-2.3.1 {
  sqlite3 db2 bak.db
  db2 close
} {}

do_test quota-2.4.1 {
  sqlite3_quota_shutdown
} {SQLITE_MISUSE}
set ::quota [list]
do_test quota-2.4.2 {
  catchsql { INSERT INTO t1 VALUES(3, randomblob(1100)) }
} {1 {database or disk is full}}
do_test quota-2.4.3 { set ::quota } {5120 6144}
do_test quota-2.4.4 { file size test.db } {5120}
do_test quota-2.4.99 {
  db close
  sqlite3_quota_shutdown
} {SQLITE_OK}

#-------------------------------------------------------------------------
# Try some tests with more than one connection to a database file. Still
# in rollback mode.
#
#   quota-3.1.*: Two connections to a single database file.
#
#   quota-3.2.*: Two connections to each of several database files (that
#                are in the same quota group).
#
proc quota_check {filename limitvar size} {
  upvar $limitvar limit
  lappend ::quota [set limit] $size
  if {[info exists ::quota_request_ok]} { set limit $size }
}

do_test quota-3.1.1 {
  file delete -force test.db
  sqlite3_quota_initialize "" 1
  sqlite3_quota_set *test.db 4096 quota_check
} {SQLITE_OK}
do_test quota-3.1.2 {
  sqlite3 db test.db
  execsql {
    PRAGMA page_size = 1024;
    PRAGMA journal_mode = delete;
    PRAGMA auto_vacuum = off;
    CREATE TABLE t1(a PRIMARY KEY, b);
    INSERT INTO t1 VALUES(1, 'one');
  }
  file size test.db
} {3072}
do_test quota-3.1.3 {
  sqlite3 db2 test.db
  set ::quota [list]
  execsql { CREATE TABLE t2(a, b) } db2
  set ::quota
} {}
do_test quota-3.1.4 {
  catchsql { CREATE TABLE t3(a, b) }
} {1 {database or disk is full}}
do_test quota-3.1.5 {
  set ::quota_request_ok 1
  execsql { CREATE TABLE t3(a, b) }
} {}
do_test quota-3.1.6 {
  db close
  db2 close
  sqlite3_quota_set *test.db 0 {}
} {SQLITE_OK}

do_test quota-3.2.1 {
  file delete force test.db test2.db 

  sqlite3_quota_set * 4096 {}
  sqlite3 db1a test.db
  sqlite3 db2a test2.db

  foreach db {db1a db2a} {
    execsql {
      PRAGMA page_size = 1024;
      PRAGMA journal_mode = delete;
      PRAGMA auto_vacuum = off;
      CREATE TABLE t1(a, b);
    } $db
  }

  sqlite3 db1b test.db
  sqlite3 db2b test2.db

  list [file size test.db] [file size test2.db]
} {2048 2048}

catch { unset ::quota_request_ok }

do_test quota-3.2.2 { execsql { INSERT INTO t1 VALUES('x', 'y') } db1a } {}
do_test quota-3.2.3 { execsql { INSERT INTO t1 VALUES('v', 'w') } db1b } {}
do_test quota-3.2.4 { execsql { INSERT INTO t1 VALUES('t', 'u') } db2a } {}
do_test quota-3.2.5 { execsql { INSERT INTO t1 VALUES('r', 's') } db2b } {}

do_test quota-3.2.6 { 
  catchsql { INSERT INTO t1 VALUES(randomblob(500), randomblob(500)) } db1a
} {1 {database or disk is full}}
do_test quota-3.2.7 { 
  catchsql { INSERT INTO t1 VALUES(randomblob(500), randomblob(500)) } db1b
} {1 {database or disk is full}}
do_test quota-3.2.8 { 
  catchsql { INSERT INTO t1 VALUES(randomblob(500), randomblob(500)) } db2a
} {1 {database or disk is full}}
do_test quota-3.2.9 { 
  catchsql { INSERT INTO t1 VALUES(randomblob(500), randomblob(500)) } db2b
} {1 {database or disk is full}}

set ::quota [list]
proc quota_callback {file limitvar size} {
  upvar $limitvar limit
  if {$::tcl_platform(platform)=="windows"} {
    set file [ lindex [string map {\\ \/} $file] 0 ]
  }
  lappend ::quota $file $size
  set limit 0
}
sqlite3_quota_set * 4096 quota_callback
do_test quota-3.3.1 { 
  execsql { INSERT INTO t1 VALUES(randomblob(500), randomblob(500)) } db1a
  execsql { INSERT INTO t1 VALUES(randomblob(500), randomblob(500)) } db1b
  execsql { INSERT INTO t1 VALUES(randomblob(500), randomblob(500)) } db2a
  execsql { INSERT INTO t1 VALUES(randomblob(500), randomblob(500)) } db2b
  set ::quota
} [list [file join [pwd] test.db] 5120]

do_test quota-3.2.X {
  foreach db {db1a db2a db2b db1b} { catch { $db close } }
  sqlite3_quota_set * 0 {}
} {SQLITE_OK}

#-------------------------------------------------------------------------
# Quotas are deleted when unused and when there limit is set to zero
#

# Return a list of all currently defined quotas.  Each quota is identified
# by its pattern.
proc quota_list {} {
  set allq {}
  foreach q [sqlite3_quota_dump] {
    lappend allq [lindex $q 0]
  }
  return [lsort $allq]
}

do_test quota-4.1.1 {
  sqlite3_quota_set *test.db 0 {}
  quota_list
} {}
do_test quota-4.1.2 {
  sqlite3_quota_set *test.db 4096 {}
  quota_list
} {*test.db}
do_test quota-4.1.3 {
  sqlite3_quota_set *test2.db 0 {}
  quota_list
} {*test.db}
do_test quota-4.1.4 {
  sqlite3_quota_set *test2.db 100000 {}
  quota_list
} {*test.db *test2.db}
do_test quota-4.1.5 {
  sqlite3_quota_set *test.db 0 {}
  quota_list
} {*test2.db}
do_test quota-4.1.6 {
  file delete -force test2.db test2.db-journal test2.db-wal
  sqlite3 db test2.db
  db eval {CREATE TABLE t2(x); INSERT INTO t2 VALUES('tab-t2');}
  quota_list
} {*test2.db}
do_test quota-4.1.7 {
  catchsql {INSERT INTO t2 VALUES(zeroblob(200000))}
} {1 {database or disk is full}}
do_test quota-4.1.8 {
  sqlite3 db2 test2.db
  db2 eval {SELECT * FROM t2}
} {tab-t2}
do_test quota-4.1.9 {
  sqlite3_quota_set *test2.db 0 {}
  catchsql {INSERT INTO t2 VALUES(zeroblob(200000))}
} {0 {}}
do_test quota-4.1.10 {
  quota_list
} {*test2.db}
do_test quota-4.1.11 {
  db2 close
  quota_list
} {*test2.db}
do_test quota-4.1.12 {
  db close
  quota_list
} {}

do_test quota-4.2.1 {
  sqlite3_quota_set A 1000 {}
  sqlite3_quota_set B 1000 {}
  sqlite3_quota_set C 1000 {}
  sqlite3_quota_set D 1000 {}
  quota_list
} {A B C D}
do_test quota-4.2.2 {
  sqlite3_quota_set C 0 {}
  sqlite3_quota_set B 0 {}
  quota_list
} {A D}
do_test quota-4.2.3 {
  sqlite3_quota_set A 0 {}
  sqlite3_quota_set D 0 {}
  quota_list
} {}
do_test quota-4.2.4 {
  sqlite3_quota_set A 1000 {}
  sqlite3_quota_set B 1000 {}
  sqlite3_quota_set C 1000 {}
  sqlite3_quota_set A 0 {}
  sqlite3_quota_set B 0 {}
  sqlite3_quota_set C 0 {}
  quota_list
} {}
do_test quota-4.2.5 {
  sqlite3_quota_set A 1000 {}
  sqlite3_quota_set B 1000 {}
  sqlite3_quota_set C 1000 {}
  sqlite3_quota_set C 0 {}
  sqlite3_quota_set B 0 {}
  sqlite3_quota_set A 0 {}
  quota_list
} {}

do_test quota-4.3.1 {
  sqlite3_quota_set A 1000 quota_callback
  sqlite3 db A
  sqlite3_quota_set A 0 quota_callback
  db close
  quota_list
} {}

do_test quota-4.4.1 {
  sqlite3_quota_set A 1000 quota_callback
  sqlite3_quota_shutdown
} {SQLITE_OK}
do_test quota-4.4.2 {
  quota_list
} {}

#-------------------------------------------------------------------------
# The following tests test that the quota VFS handles malloc and IO 
# errors.
#

sqlite3_quota_initialize "" 1
sqlite3_quota_set *test.db 4096 {}

do_faultsim_test quota-5.1 -prep {
  catch {db close}
} -body {
  sqlite3 db test2.db
}
do_faultsim_test quota-5.2 -prep {
  catch {db close}
} -body {
  sqlite3 db test.db
}

catch { db close }
file delete -force test.db

do_test quota-5.3.prep {
  sqlite3 db test.db
  execsql {
    PRAGMA auto_vacuum = 1;
    PRAGMA page_size = 1024;
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(10, zeroblob(1200));
  }
  faultsim_save_and_close
} {}
do_faultsim_test quota-5.3 -prep {
  faultsim_restore_and_reopen
} -body {
  execsql { DELETE FROM t1 }
}

do_test quota-5.4.1 {
  catch { db close }
  file delete -force test.db
  file mkdir test.db
  list [catch { sqlite3 db test.db } msg] $msg
} {1 {unable to open database file}}

do_faultsim_test quota-5.5 -prep {
  catch { sqlite3_quota_shutdown }
} -body {
  sqlite3_quota_initialize "" 1
}

do_faultsim_test quota-5.6 -prep {
  catch { sqlite3_quota_shutdown }
  sqlite3_quota_initialize "" 1
} -body {
  sqlite3_quota_set * 4096 {}
}

catch { sqlite3_quota_shutdown }
finish_test