数据库配置
SQLite没有配置文件。所有这些配置参数都是用pragma来实现。Pragma以独特的方式工作,有些像变量,又有些像命令。
连接缓冲区大小
缓冲区尺寸pragma控制一个连接可以在内存中使用多少个数据库页。要查看当前缓冲区大小的默认值,执行:
sqlite> PRAGMAcache_size;
cache_size
2000
要改变缓冲区大小,执行:
sqlite> PRAGMAcache_size=10000;
sqlite> PRAGMA cache_size;
cache_size
10000
获得数据库信息
可以使用数据库的schemapragma来获得数据库信息,定义如下:
l database_list: Lists information about all attached databases.
l index_info: Lists information about the columns within an index. Ittakes an index name as an argument.
l index_list: Lists information about the indexes in a table. It takesa table name as an argument.
l table_info: Lists information about all columns in a table.
请看下面示例:
sqlite> PRAGMA database_list;
seq name file
0 main /tmp/foods.db
2 db2 /tmp/db
sqlite> CREATE INDEX foods_name_type_idxON foods(name,type_id);
sqlite> PRAGMAindex_info(foods_name_type_idx);
seqn cid name
0 2 name
1 1 type_id
sqlite> PRAGMA index_list(foods);
seq name unique
0 foods_name_type_idx 0
sqlite> PRAGMA table_info(foods);
cid name type notn dflt pk
0 id integer 0 1
1 type_id integer 0 0
2 name text 0 0
页大小、编码和自动排空
The database page size, encoding, andautovacuuming must be set before a database is created. That is, in order toalter the defaults, you must first set these pragmas before creating anydatabase objects in a new database. The defaults are a 1,024-byte page size andUTF-8 encoding. SQLite supports page sizes ranging from 512 to 32,786 bytes, inpowers of 2. Supported encodings are UTF-8, UTF-16le (little-endian UTF-16encoding), and UTF-16be (big-endian UTF-16 encoding).
如果使用auto_vacuumpragma,可以使数据库自动维持最小。一般情况下,当一个事务从数据库中删除了数据并提交后,数据库文件的大小保持不变。当使用了auto_vacuum pragma后,当删除事务提交时,数据库文件会自动缩小。
系统表
sqlite_master表是一个系统表,它包含数据库中所有表、视图、索引和触发器的信息。例如,foods的当前内容如下:
sqlite> SELECT type, name, rootpage FROMsqlite_master;
type name rootpage
table episodes2
table foods3
table foods_episodes4
table food_types5
index foods_name_idx30
table sqlite_sequence50
trigger foods_update_trg0
trigger foods_insert_trg0
trigger foods_delete_trg0
有关sqlite_master表的结构请参考第2章的“获得数据库的Schema信息”一节。
sqlite_master包含一个称为sql的字段,存储了创建对象的DDL命令,如:
sqlite> SELECT sql FROM sqlite_masterWHERE name='foods_update_trg';
返回:
CREATE TRIGGER foods_update_trg
BEFORE UPDATE OF type_id ON foods
BEGIN
SELECT CASE
WHEN(SELECT id FROM food_types WHERE id=NEW.type_id) IS NULL
THENRAISE( ABORT,
'Foreign Key Violation: foods.type_id is notin food_types.id')
END;
END
查看Query的执行
可以用EXPLAIN命令查看SQLite执行一个查询的方法。EXPLAIN列出一个SQL命令编译后的VDBE程序。
sqlite> .m col
sqlite> .h on
sqlite> .w 4 15 3 3 3 10 3
sqlite> EXPLAIN SELECT * FROM foods;
addr opcode p1 p2 p3 p4 p5 comment
---- --------------- --- --- --- ---------- --- -------
0 Trace 0 0 0 00
1 Goto 0 11 0 00
2 OpenRead 0 7 0 3 00
3 Rewind 0 9 0 00
4 Rowid 0 1 0 00
5 Column 0 1 2 00
6 Column 0 2 3 00
7 ResultRow 1 3 0 00
8 Next 0 4 0 01
9 Close 0 0 0 00
10 Halt 0 0 0 00
11 Transaction 0 0 0 00
12 VerifyCookie 0 40 0 00
13 TableLock 0 7 0 foods 00
14 Goto 0 2 0 00