pragma.test

sqlite-3.4.1,嵌入式数据库.是一个功能强大的开源数据库,给学习和研发以及小型公司的发展带来了全所未有的好处.

  do_test pragma-6.4 {
    execsql {
      pragma index_list(t3);
    }
  } {0 sqlite_autoindex_t3_1 1}
}
ifcapable {!foreignkey} {
  execsql {CREATE TABLE t3(a,b UNIQUE)}
}
do_test pragma-6.5 {
  execsql {
    CREATE INDEX t3i1 ON t3(a,b);
    pragma index_info(t3i1);
  }
} {0 0 a 1 1 b}
} ;# ifcapable schema_pragmas
# Miscellaneous tests
#
ifcapable schema_pragmas {
do_test pragma-7.1 {
  # Make sure a pragma knows to read the schema if it needs to
  db close
  sqlite3 db test.db
  execsql {
    pragma index_list(t3);
  }
} {0 t3i1 0 1 sqlite_autoindex_t3_1 1}
} ;# ifcapable schema_pragmas
ifcapable {utf16} {
  do_test pragma-7.2 {
    db close
    sqlite3 db test.db
    catchsql {
      pragma encoding=bogus;
    }
  } {1 {unsupported encoding: bogus}}
}
ifcapable tempdb {
  do_test pragma-7.3 {
    db close
    sqlite3 db test.db
    execsql {
      pragma lock_status;
    }
  } {main unlocked temp closed}
} else {
  do_test pragma-7.3 {
    db close
    sqlite3 db test.db
    execsql {
      pragma lock_status;
    }
  } {main unlocked}
}


#----------------------------------------------------------------------
# Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA
# user_version" statements.
#
# pragma-8.1: PRAGMA schema_version
# pragma-8.2: PRAGMA user_version
#

ifcapable schema_version {

# First check that we can set the schema version and then retrieve the
# same value.
do_test pragma-8.1.1 {
  execsql {
    PRAGMA schema_version = 105;
  }
} {}
do_test pragma-8.1.2 {
  execsql2 {
    PRAGMA schema_version;
  }
} {schema_version 105}
do_test pragma-8.1.3 {
  execsql {
    PRAGMA schema_version = 106;
  }
} {}
do_test pragma-8.1.4 {
  execsql {
    PRAGMA schema_version;
  }
} 106

# Check that creating a table modifies the schema-version (this is really
# to verify that the value being read is in fact the schema version).
do_test pragma-8.1.5 {
  execsql {
    CREATE TABLE t4(a, b, c);
    INSERT INTO t4 VALUES(1, 2, 3);
    SELECT * FROM t4;
  }
} {1 2 3}
do_test pragma-8.1.6 {
  execsql {
    PRAGMA schema_version;
  }
} 107

# Now open a second connection to the database. Ensure that changing the
# schema-version using the first connection forces the second connection
# to reload the schema. This has to be done using the C-API test functions,
# because the TCL API accounts for SCHEMA_ERROR and retries the query.
do_test pragma-8.1.7 {
  sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
  execsql {
    SELECT * FROM t4;
  } db2
} {1 2 3}
do_test pragma-8.1.8 {
  execsql {
    PRAGMA schema_version = 108;
  }
} {}
do_test pragma-8.1.9 {
  set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY]
  sqlite3_step $::STMT
} SQLITE_ERROR
do_test pragma-8.1.10 {
  sqlite3_finalize $::STMT
} SQLITE_SCHEMA

# Make sure the schema-version can be manipulated in an attached database.
file delete -force test2.db
file delete -force test2.db-journal
do_test pragma-8.1.11 {
  execsql {
    ATTACH 'test2.db' AS aux;
    CREATE TABLE aux.t1(a, b, c);
    PRAGMA aux.schema_version = 205;
  }
} {}
do_test pragma-8.1.12 {
  execsql {
    PRAGMA aux.schema_version;
  }
} 205
do_test pragma-8.1.13 {
  execsql {
    PRAGMA schema_version;
  }
} 108

# And check that modifying the schema-version in an attached database
# forces the second connection to reload the schema.
do_test pragma-8.1.14 {
  sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
  execsql {
    ATTACH 'test2.db' AS aux;
    SELECT * FROM aux.t1;
  } db2
} {}
do_test pragma-8.1.15 {
  execsql {
    PRAGMA aux.schema_version = 206;
  }
} {}
do_test pragma-8.1.16 {
  set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY]
  sqlite3_step $::STMT
} SQLITE_ERROR
do_test pragma-8.1.17 {
  sqlite3_finalize $::STMT
} SQLITE_SCHEMA
do_test pragma-8.1.18 {
  db2 close
} {}

# Now test that the user-version can be read and written (and that we aren't
# accidentally manipulating the schema-version instead).
do_test pragma-8.2.1 {
  execsql2 {
    PRAGMA user_version;
  }
} {user_version 0}
do_test pragma-8.2.2 {
  execsql {
    PRAGMA user_version = 2;
  }
} {}
do_test pragma-8.2.3.1 {
  execsql2 {
    PRAGMA user_version;
  }
} {user_version 2}
do_test pragma-8.2.3.2 {
  db close
  sqlite3 db test.db
  execsql {
    PRAGMA user_version;
  }
} {2}
do_test pragma-8.2.4.1 {
  execsql {
    PRAGMA schema_version;
  }
} {108}
ifcapable vacuum {
  do_test pragma-8.2.4.2 {
    execsql {
      VACUUM;
      PRAGMA user_version;
    }
  } {2}
  do_test pragma-8.2.4.3 {
    execsql {
      PRAGMA schema_version;
    }
  } {109}
}
db eval {ATTACH 'test2.db' AS aux}

# Check that the user-version in the auxilary database can be manipulated (
# and that we aren't accidentally manipulating the same in the main db).
do_test pragma-8.2.5 {
  execsql {
    PRAGMA aux.user_version;
  }
} {0}
do_test pragma-8.2.6 {
  execsql {
    PRAGMA aux.user_version = 3;
  }
} {}
do_test pragma-8.2.7 {
  execsql {
    PRAGMA aux.user_version;
  }
} {3}
do_test pragma-8.2.8 {
  execsql {
    PRAGMA main.user_version;
  }
} {2}

# Now check that a ROLLBACK resets the user-version if it has been modified
# within a transaction.
do_test pragma-8.2.9 {
  execsql {
    BEGIN;
    PRAGMA aux.user_version = 10;
    PRAGMA user_version = 11;
  }
} {}
do_test pragma-8.2.10 {
  execsql {
    PRAGMA aux.user_version;
  }
} {10}
do_test pragma-8.2.11 {
  execsql {
    PRAGMA main.user_version;
  }
} {11}
do_test pragma-8.2.12 {
  execsql {
    ROLLBACK;
    PRAGMA aux.user_version;
  }
} {3}
do_test pragma-8.2.13 {
  execsql {
    PRAGMA main.user_version;
  }
} {2}

# Try a negative value for the user-version
do_test pragma-8.2.14 {
  execsql {
    PRAGMA user_version = -450;
  }
} {}
do_test pragma-8.2.15 {
  execsql {
    PRAGMA user_version;
  }
} {-450}
} ; # ifcapable schema_version


# Test temp_store and temp_store_directory pragmas
#
ifcapable pager_pragmas {
do_test pragma-9.1 {
  db close
  sqlite3 db test.db
  execsql {
    PRAGMA temp_store;
  }
} {0}
do_test pragma-9.2 {
  execsql {
    PRAGMA temp_store=file;
    PRAGMA temp_store;
  }
} {1}
do_test pragma-9.3 {
  execsql {
    PRAGMA temp_store=memory;
    PRAGMA temp_store;
  }
} {2}
do_test pragma-9.4 {
  execsql {
    PRAGMA temp_store_directory;
  }
} {}
do_test pragma-9.5 {
  set pwd [string map {' ''} [pwd]]
  execsql "
    PRAGMA temp_store_directory='$pwd';
  "
} {}
do_test pragma-9.6 {
  execsql { 
    PRAGMA temp_store_directory;
  }
} [list [pwd]]
do_test pragma-9.7 {
  catchsql { 
    PRAGMA temp_store_directory='/NON/EXISTENT/PATH/FOOBAR';
  }
} {1 {not a writable directory}}
do_test pragma-9.8 {
  execsql { 
    PRAGMA temp_store_directory='';
  }
} {}
if {![info exists TEMP_STORE] || $TEMP_STORE<=1} {
  ifcapable tempdb {
    do_test pragma-9.9 {
      execsql { 
        PRAGMA temp_store_directory;
        PRAGMA temp_store=FILE;
        CREATE TEMP TABLE temp_store_directory_test(a integer);
        INSERT INTO temp_store_directory_test values (2);
        SELECT * FROM temp_store_directory_test;
      }
    } {2}
    do_test pragma-9.10 {
      catchsql "
        PRAGMA temp_store_directory='$pwd';
        SELECT * FROM temp_store_directory_test;
      "
    } {1 {no such table: temp_store_directory_test}}
  }
}  
do_test pragma-9.11 {
  execsql {
    PRAGMA temp_store = 0;
    PRAGMA temp_store;
  }
} {0}
do_test pragma-9.12 {
  execsql {
    PRAGMA temp_store = 1;
    PRAGMA temp_store;
  }
} {1}
do_test pragma-9.13 {
  execsql {
    PRAGMA temp_store = 2;
    PRAGMA temp_store;
  }
} {2}
do_test pragma-9.14 {
  execsql {
    PRAGMA temp_store = 3;
    PRAGMA temp_store;
  }
} {0}
breakpoint
do_test pragma-9.15 {
  catchsql {
    BEGIN EXCLUSIVE;
    CREATE TEMP TABLE temp_table(t);
    INSERT INTO temp_table VALUES('valuable data');
    PRAGMA temp_store = 1;
  }
} {1 {temporary storage cannot be changed from within a transaction}}
do_test pragma-9.16 {
  execsql {
    SELECT * FROM temp_table;
    COMMIT;
  }
} {{valuable data}}
} ;# ifcapable pager_pragmas

ifcapable trigger {

do_test pragma-10.0 {
  catchsql {
    DROP TABLE main.t1;
  }
  execsql {
    PRAGMA count_changes = 1;

    CREATE TABLE t1(a PRIMARY KEY);
    CREATE TABLE t1_mirror(a);
    CREATE TABLE t1_mirror2(a);
    CREATE TRIGGER t1_bi BEFORE INSERT ON t1 BEGIN 
      INSERT INTO t1_mirror VALUES(new.a);
    END;
    CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN 
      INSERT INTO t1_mirror2 VALUES(new.a);
    END;
    CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN 
      UPDATE t1_mirror SET a = new.a WHERE a = old.a;
    END;
    CREATE TRIGGER t1_au AFTER UPDATE ON t1 BEGIN 
      UPDATE t1_mirror2 SET a = new.a WHERE a = old.a;
    END;
    CREATE TRIGGER t1_bd BEFORE DELETE ON t1 BEGIN 
      DELETE FROM t1_mirror WHERE a = old.a;
    END;
    CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN 
      DELETE FROM t1_mirror2 WHERE a = old.a;
    END;
  }
} {}

do_test pragma-10.1 {
  execsql {
    INSERT INTO t1 VALUES(randstr(10,10));
  }
} {1}
do_test pragma-10.2 {
  execsql {
    UPDATE t1 SET a = randstr(10,10);
  }
} {1}
do_test pragma-10.3 {
  execsql {
    DELETE FROM t1;
  }
} {1}

} ;# ifcapable trigger

ifcapable schema_pragmas {
  do_test pragma-11.1 {
    execsql2 {
      pragma collation_list;
    }
  } {seq 0 name NOCASE seq 1 name BINARY}
  do_test pragma-11.2 {
    db collate New_Collation blah...
    execsql {
      pragma collation_list;
    }
  } {0 New_Collation 1 NOCASE 2 BINARY}
}

ifcapable schema_pragmas&&tempdb {
  do_test pragma-12.1 {
    sqlite3 db2 test.db
    execsql {
      PRAGMA temp.table_info('abc');
    } db2
  } {}
  db2 close

  do_test pragma-12.2 {
    sqlite3 db2 test.db
    execsql {
      PRAGMA temp.default_cache_size = 200;
      PRAGMA temp.default_cache_size;
    } db2
  } {200}
  db2 close

  do_test pragma-12.3 {
    sqlite3 db2 test.db
    execsql {
      PRAGMA temp.cache_size = 400;
      PRAGMA temp.cache_size;
    } db2
  } {400}
  db2 close
}

ifcapable bloblit {

do_test pragma-13.1 {
  execsql {
    DROP TABLE IF EXISTS t4;
    PRAGMA vdbe_trace=on;
    PRAGMA vdbe_listing=on;
    PRAGMA sql_trace=on;
    CREATE TABLE t4(a INTEGER PRIMARY KEY,b);
    INSERT INTO t4(b) VALUES(x'0123456789abcdef0123456789abcdef0123456789');
    INSERT INTO t4(b) VALUES(randstr(30,30));
    INSERT INTO t4(b) VALUES(1.23456);
    INSERT INTO t4(b) VALUES(NULL);
    INSERT INTO t4(b) VALUES(0);
    INSERT INTO t4(b) SELECT b||b||b||b FROM t4;
    SELECT * FROM t4;
  }
  execsql {
    PRAGMA vdbe_trace=off;
    PRAGMA vdbe_listing=off;
    PRAGMA sql_trace=off;
  }
} {}

} ;# ifcapable bloblit 

# Reset the sqlite3_temp_directory variable for the next run of tests:
sqlite3 dbX :memory:
dbX eval {PRAGMA temp_store_directory = ""}
dbX close

finish_test