# 2002 March 6

#

# 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.

#

#***********************************************************************

# This file implements regression tests for SQLite library.

#

# This file implements tests for the PRAGMA command.

#

# $Id: pragma.test,v 1.66 2008/09/02 00:52:52 drh Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# Test organization:

#

# pragma-1.*: Test cache_size, default_cache_size and synchronous on main db.

# pragma-2.*: Test synchronous on attached db.

# pragma-3.*: Test detection of table/index inconsistency by integrity_check.

# pragma-4.*: Test cache_size and default_cache_size on attached db.

# pragma-5.*: Test that pragma synchronous may not be used inside of a

#             transaction.

# pragma-6.*: Test schema-query pragmas.

# pragma-7.*: Miscellaneous tests.

# pragma-8.*: Test user_version and schema_version pragmas.

# pragma-9.*: Test temp_store and temp_store_directory.

# pragma-10.*: Test the count_changes pragma in the presence of triggers.

# pragma-11.*: Test the collation_list pragma.

# pragma-14.*: Test the page_count pragma.

# pragma-15.*: Test that the value set using the cache_size pragma is not

#              reset when the schema is reloaded.

#

ifcapable !pragma {

  finish_test

  return

}

# Delete the preexisting database to avoid the special setup

# that the "all.test" script does.

#

db close

file delete test.db test.db-journal

file delete test3.db test3.db-journal

sqlite3 db test.db; set DB [sqlite3_connection_pointer db]

ifcapable pager_pragmas {

set DFLT_CACHE_SZ [db one {PRAGMA default_cache_size}]

set TEMP_CACHE_SZ [db one {PRAGMA temp.default_cache_size}]

do_test pragma-1.1 {

  execsql {

    PRAGMA cache_size;

    PRAGMA default_cache_size;

    PRAGMA synchronous;

  }

} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]

do_test pragma-1.2 {

  execsql {

    PRAGMA synchronous=OFF;

    PRAGMA cache_size=1234;

    PRAGMA cache_size;

    PRAGMA default_cache_size;

    PRAGMA synchronous;

  }

} [list 1234 $DFLT_CACHE_SZ 0]

do_test pragma-1.3 {

  db close

  sqlite3 db test.db

  execsql {

    PRAGMA cache_size;

    PRAGMA default_cache_size;

    PRAGMA synchronous;

  }

} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]

do_test pragma-1.4 {

  execsql {

    PRAGMA synchronous=OFF;

    PRAGMA cache_size;

    PRAGMA default_cache_size;

    PRAGMA synchronous;

  }

} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 0]

do_test pragma-1.5 {

  execsql {

    PRAGMA cache_size=4321;

    PRAGMA cache_size;

    PRAGMA default_cache_size;

    PRAGMA synchronous;

  }

} [list 4321 $DFLT_CACHE_SZ 0]

do_test pragma-1.6 {

  execsql {

    PRAGMA synchronous=ON;

    PRAGMA cache_size;

    PRAGMA default_cache_size;

    PRAGMA synchronous;

  }

} [list 4321 $DFLT_CACHE_SZ 1]

do_test pragma-1.7 {

  db close

  sqlite3 db test.db

  execsql {

    PRAGMA cache_size;

    PRAGMA default_cache_size;

    PRAGMA synchronous;

  }

} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]

do_test pragma-1.8 {

  execsql {

    PRAGMA default_cache_size=123;

    PRAGMA cache_size;

    PRAGMA default_cache_size;

    PRAGMA synchronous;

  }

} {123 123 2}

do_test pragma-1.9.1 {

  db close

  sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db]

  execsql {

    PRAGMA cache_size;

    PRAGMA default_cache_size;

    PRAGMA synchronous;

  }

} {123 123 2}

ifcapable vacuum {

  do_test pragma-1.9.2 {

    execsql {

      VACUUM;

      PRAGMA cache_size;

      PRAGMA default_cache_size;

      PRAGMA synchronous;

    }

  } {123 123 2}

}

do_test pragma-1.10 {

  execsql {

    PRAGMA synchronous=NORMAL;

    PRAGMA cache_size;

    PRAGMA default_cache_size;

    PRAGMA synchronous;

  }

} {123 123 1}

do_test pragma-1.11 {

  execsql {

    PRAGMA synchronous=FULL;

    PRAGMA cache_size;

    PRAGMA default_cache_size;

    PRAGMA synchronous;

  }

} {123 123 2}

do_test pragma-1.12 {

  db close

  sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db]

  execsql {

    PRAGMA cache_size;

    PRAGMA default_cache_size;

    PRAGMA synchronous;

  }

} {123 123 2}

# Make sure the pragma handler understands numeric values in addition

# to keywords like "off" and "full".

#

do_test pragma-1.13 {

  execsql {

    PRAGMA synchronous=0;

    PRAGMA synchronous;

  }

} {0}

do_test pragma-1.14 {

  execsql {

    PRAGMA synchronous=2;

    PRAGMA synchronous;

  }

} {2}

} ;# ifcapable pager_pragmas

# Test turning "flag" pragmas on and off.

#

ifcapable debug {

  # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG

  #

  do_test pragma-1.15 {

    execsql {

      PRAGMA vdbe_listing=YES;

      PRAGMA vdbe_listing;

    }

  } {1}

  do_test pragma-1.16 {

    execsql {

      PRAGMA vdbe_listing=NO;

      PRAGMA vdbe_listing;

    }

  } {0}

}

do_test pragma-1.17 {

  execsql {

    PRAGMA parser_trace=ON;

    PRAGMA parser_trace=OFF;

  }

} {}

do_test pragma-1.18 {

  execsql {

    PRAGMA bogus = -1234;  -- Parsing of negative values

  }

} {}

# Test modifying the safety_level of an attached database.

ifcapable pager_pragmas&&attach {

  do_test pragma-2.1 {

    file delete -force test2.db

    file delete -force test2.db-journal

    execsql {

      ATTACH 'test2.db' AS aux;

    } 

  } {}

  do_test pragma-2.2 {

    execsql {

      pragma aux.synchronous;

    } 

  } {2}

  do_test pragma-2.3 {

    execsql {

      pragma aux.synchronous = OFF;

      pragma aux.synchronous;

      pragma synchronous;

    } 

  } {0 2}

  do_test pragma-2.4 {

    execsql {

      pragma aux.synchronous = ON;

      pragma synchronous;

      pragma aux.synchronous;

    } 

  } {2 1}

} ;# ifcapable pager_pragmas

# Construct a corrupted index and make sure the integrity_check

# pragma finds it.

#

# These tests won't work if the database is encrypted

#

do_test pragma-3.1 {

  db close

  file delete -force test.db test.db-journal

  sqlite3 db test.db

  execsql {

    PRAGMA auto_vacuum=OFF;

    BEGIN;

    CREATE TABLE t2(a,b,c);

    CREATE INDEX i2 ON t2(a);

    INSERT INTO t2 VALUES(11,2,3);

    INSERT INTO t2 VALUES(22,3,4);

    COMMIT;

    SELECT rowid, * from t2;

  }

} {1 11 2 3 2 22 3 4}

ifcapable attach {

  if {![sqlite3 -has-codec] && $sqlite_options(integrityck)} {

    do_test pragma-3.2 {

      db eval {SELECT rootpage FROM sqlite_master WHERE name='i2'} break

      set pgsz [db eval {PRAGMA page_size}]

      # overwrite the header on the rootpage of the index in order to

      # make the index appear to be empty.

      #

      set offset [expr {$pgsz*($rootpage-1)}]

      hexio_write test.db $offset 0a00000000040000000000

      db close

      sqlite3 db test.db

      execsql {PRAGMA integrity_check}

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

    do_test pragma-3.3 {

      execsql {PRAGMA integrity_check=1}

    } {{rowid 1 missing from index i2}}

    do_test pragma-3.4 {

      execsql {

        ATTACH DATABASE 'test.db' AS t2;

        PRAGMA integrity_check

      }

    } {{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}}

    do_test pragma-3.5 {

      execsql {

        PRAGMA integrity_check=4

      }

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

    do_test pragma-3.6 {

      execsql {

        PRAGMA integrity_check=xyz

      }

    } {{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}}

    do_test pragma-3.7 {

      execsql {

        PRAGMA integrity_check=0

      }

    } {{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}}

    # Add additional corruption by appending unused pages to the end of

    # the database file testerr.db

    #

    do_test pragma-3.8 {

      execsql {DETACH t2}

      file delete -force testerr.db testerr.db-journal

      set out [open testerr.db w]

      fconfigure $out -translation binary

      set in [open test.db r]

      fconfigure $in -translation binary

      puts -nonewline $out [read $in]

      seek $in 0

      puts -nonewline $out [read $in]

      close $in

      close $out

      execsql {REINDEX t2}

      execsql {PRAGMA integrity_check}

    } {ok}

    do_test pragma-3.8.1 {

      execsql {PRAGMA quick_check}

    } {ok}

    do_test pragma-3.9 {

      execsql {

        ATTACH 'testerr.db' AS t2;

        PRAGMA integrity_check

      }

    } {{*** in database t2 ***

Page 4 is never used

Page 5 is never used

Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}

    do_test pragma-3.10 {

      execsql {

        PRAGMA integrity_check=1

      }

    } {{*** in database t2 ***

Page 4 is never used}}

    do_test pragma-3.11 {

      execsql {

        PRAGMA integrity_check=5

      }

    } {{*** in database t2 ***

Page 4 is never used

Page 5 is never used

Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2}}

    do_test pragma-3.12 {

      execsql {

        PRAGMA integrity_check=4

      }

    } {{*** in database t2 ***

Page 4 is never used

Page 5 is never used

Page 6 is never used} {rowid 1 missing from index i2}}

    do_test pragma-3.13 {

      execsql {

        PRAGMA integrity_check=3

      }

    } {{*** in database t2 ***

Page 4 is never used

Page 5 is never used

Page 6 is never used}}

    do_test pragma-3.14 {

      execsql {

        PRAGMA integrity_check(2)

      }

    } {{*** in database t2 ***

Page 4 is never used

Page 5 is never used}}

    do_test pragma-3.15 {

      execsql {

        ATTACH 'testerr.db' AS t3;

        PRAGMA integrity_check

      }

    } {{*** in database t2 ***

Page 4 is never used

Page 5 is never used

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 ***

Page 4 is never used

Page 5 is never used

Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}

    do_test pragma-3.16 {

      execsql {

        PRAGMA integrity_check(10)

      }

    } {{*** in database t2 ***

Page 4 is never used

Page 5 is never used

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 ***

Page 4 is never used

Page 5 is never used

Page 6 is never used} {rowid 1 missing from index i2}}

    do_test pragma-3.17 {

      execsql {

        PRAGMA integrity_check=8

      }

    } {{*** in database t2 ***

Page 4 is never used

Page 5 is never used

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 ***

Page 4 is never used

Page 5 is never used}}

    do_test pragma-3.18 {

      execsql {

        PRAGMA integrity_check=4

      }

    } {{*** in database t2 ***

Page 4 is never used

Page 5 is never used

Page 6 is never used} {rowid 1 missing from index i2}}

  }

  do_test pragma-3.99 {

    catchsql {DETACH t3}

    catchsql {DETACH t2}

    file delete -force testerr.db testerr.db-journal

    catchsql {DROP INDEX i2}

  } {0 {}}

}

# Test modifying the cache_size of an attached database.

ifcapable pager_pragmas&&attach {

do_test pragma-4.1 {

  execsql {

    ATTACH 'test2.db' AS aux;

    pragma aux.cache_size;

    pragma aux.default_cache_size;

  } 

} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]

do_test pragma-4.2 {

  execsql {

    pragma aux.cache_size = 50;

    pragma aux.cache_size;

    pragma aux.default_cache_size;

  } 

} [list 50 $DFLT_CACHE_SZ]

do_test pragma-4.3 {

  execsql {

    pragma aux.default_cache_size = 456;

    pragma aux.cache_size;

    pragma aux.default_cache_size;

  } 

} {456 456}

do_test pragma-4.4 {

  execsql {

    pragma cache_size;

    pragma default_cache_size;

  } 

} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]

do_test pragma-4.5 {

  execsql {

    DETACH aux;

    ATTACH 'test3.db' AS aux;

    pragma aux.cache_size;

    pragma aux.default_cache_size;

  } 

} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]

do_test pragma-4.6 {

  execsql {

    DETACH aux;

    ATTACH 'test2.db' AS aux;

    pragma aux.cache_size;

    pragma aux.default_cache_size;

  } 

} {456 456}

} ;# ifcapable pager_pragmas

# Test that modifying the sync-level in the middle of a transaction is

# disallowed.

ifcapable pager_pragmas {

do_test pragma-5.0 {

  execsql {

    pragma synchronous;

  } 

} {2}

do_test pragma-5.1 {

  catchsql {

    BEGIN;

    pragma synchronous = OFF;

  } 

} {1 {Safety level may not be changed inside a transaction}}

do_test pragma-5.2 {

  execsql {

    pragma synchronous;

  } 

} {2}

catchsql {COMMIT;}

} ;# ifcapable pager_pragmas

# Test schema-query pragmas

#

ifcapable schema_pragmas {

ifcapable tempdb&&attach {

  do_test pragma-6.1 {

    set res {}

    execsql {SELECT * FROM sqlite_temp_master}

    foreach {idx name file} [execsql {pragma database_list}] {

      lappend res $idx $name

    }

    set res

  } {0 main 1 temp 2 aux}

}

do_test pragma-6.2 {

  execsql {

    pragma table_info(t2)

  }

} {0 a {} 0 {} 0 1 b {} 0 {} 0 2 c {} 0 {} 0}

db nullvalue <<NULL>>

do_test pragma-6.2.2 {

  execsql {

    CREATE TABLE t5(

      a TEXT DEFAULT CURRENT_TIMESTAMP, 

      b DEFAULT (5+3),

      c TEXT,

      d INTEGER DEFAULT NULL,

      e TEXT DEFAULT ''

    );

    PRAGMA table_info(t5);

  }

} {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}

db nullvalue {}

ifcapable {foreignkey} {

  do_test pragma-6.3 {

    execsql {

      CREATE TABLE t3(a int references t2(b), b UNIQUE);

      pragma foreign_key_list(t3);

    }

  } {0 0 t2 a b}