Marvin's Blog【程式人生】

Ability will never catch up with the demand for it

10 Jun 2020

SQLite文档阅读:数据库文件格式

Database File Format 阅读笔记。

1. The Database File

Sqlite数据库的状态由数据库文件以及日志文件共同构成。日志文件分为两种,一种是活动记录(journal),另一种是WAL格式。

页是SQLite存储的基本单元,页的大小可以从512到65536。页的编号从1开始。页是单用途的,可以作为:

  • lock-byte页
  • freelist枝干,freelist末梢
  • b-tree居间页,b-tree末梢页
  • payload overflow页
  • point map页

SQLite的读写操作都是以整页为单位的,可以覆盖多个整页。一个例外是,数据库的前100字节保存数据库的元信息,所以这100字节不是按页读取的。

1.3. The Database Header列举了数据库的元信息。

1.4. The Lock-Byte Page

Lock-Byte页映射到数据库文件从1073741824 到1073742335区间的字节。这个页面主要是为了应对Win95只支持mandatory file locking而不支持advisory file locking而设立的。现在已经没有参考意义,保留只是为了格式上的向后兼容性。

1.5. The Freelist

Freelist即备用页列表,用来回收暂时不用的页面。freelist分两级,枝干和末梢。枝干以链表方式组织,每个枝干可以拥有若干末梢页。

1.6. B-tree Pages

B-tree中的页具有键名,可以根据键名来索引。SQLite使用了两种B-tree,一种是B*-Tree,所有的数据保存在末梢,主要用来存表数据;另一种是B-Tree,间隔页也用来保存数据,主要用来存索引。

1.7. Cell Payload Overflow Pages

溢出页用来盛放B-tree装不下的内容。

1.8. Pointer Map or Ptrmap Pages

指针映射页面是为了让auto_vacuum或者incremental_vacuum效率更高。

2. Schema Layer

讲述如何使用B-tree来支持SQL。

2.1. Record Format

不管是分页的表数据还是索引数据,都是采用"record format“,指定表的栏目,每个栏目的类型以及每个栏目的内容。record format大量使用了可变长的整型值。

2.2. Record Sort Order

索引的b-tree的顺序是由其中records的键值的顺序决定的。

Record的比对是从左到右按栏进行的。碰到第一个不相同的栏,那么这个栏的比对结果就决定了记录的顺序。单栏的比对规则如下:

  • NULL (serial type 0)排第一
  • 接下来是Numeric数值类型(serial types 1 through 9)
  • 然后是Text也就是文本类型(奇数serial types 13及更大),文本排序受collating function的影响
  • 最后是BLOB类型,混沌类型(偶数serial types 12以及更大)

用于文本的Colatting function有以下几种内建类型:

  • BINARY,使用memcmp来勘比
  • NOCASE,在ASCII范围内不区分大小写
  • RTRIM,跟BINARY类似,不过会把字符串末尾的空白去掉。

使用sqlite3_create_collation() 可以注册自定义的collating模式。默认会采用BINARY模式,可以在CREATE TABLE的时候指定其他模式。除非特别指定,创建索引的时候会创建表的时候采用一样的collating模式。

2.3. Representation Of SQL Tables

Schema里面指定的表在磁盘上以一颗b-tree的方式存储。b-tree中的每一个项目表示一个数据行。rowid是64位有符号整型值。数据存储的时候,所有栏的数据都会合并在一起,然后写入这个record format里面。如果表指定了INTEGER PRIMARY KEY ,那么这一栏为NULL,因为SQLite会使用b-tree的键值。

如果一个数据栏的affinity设为REAL,但是保存的值可以无损转化为整型,那么数据会被保存成整型。

2.4. Representation of WITHOUT ROWID Tables

WITHOUT ROWID的表采用索引b-tree而不是使用表格b-tree。并且会把PRIMARY KEY这一栏(按照在CREATE TABLE中出现的位置)录入键值。

2.5. Representation Of SQL Indices

不论是通过CREATE INDEX生成的索引,还是隐式通过UNIQUE和PRIMARY KEY约束生成的索引,都是采用索引b-tree实现的。

索引B-tree中的每一项对应着所关联的表格B-tree的项。索引B-tree的键值是一个被索引的表栏合并而成的记录,这个记录还包含表行的键值。对于rowid的表,条目的键值是rowid;对于WITHOUT ROWID 的表,条目的键值是PRIMARY KEY。

对于局部索引(partial index),根据其指定的WHERE成分,可以只映射部分关联表格的条目。

略过 2.5.1. Suppression of redundant columns in WITHOUT ROWID secondary indexes

2.6. Storage Of The SQL Database Schema

数据库文件的第一分页是一个表格b-tree根页,记录着一个表,名字叫做sqlite_master(或者sqlite_temp_master,如果这个数据库是TEMP的话),这个表的结构类似于:

CREATE TABLE sqlite_master(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

sqlite_master的每一行代表一个表、索引、视图或者触发器。sqlite_master不仅包含用户自定义的条目,也包含私有的条目。

sqlite_master.type可以是’table’, ‘index’, ‘view’, 或者’trigger’。‘table‘对应的不仅是普通的表格,也可以代表虚拟表格。

sqlite_master.name对应的是条目的名字。如果使用了UNIQUE或者PRIMARY KEY修饰,SQLite会创建内部用的索引,比如"sqlite_autoindex_TABLE_N” 。

对于WITHOUT ROWID 的表格,sqlite_master中没有相应的PRIMARY KEY表现,但是"sqlite_autoindex_TABLE_N” 依然会存在。对于 INTEGER PRIMARY KEY,不会有对应的"sqlite_autoindex_TABLE_N”,可能因为采用的是b-tree的节点做键值吧。

sqlite_master.tbl_name栏在索引和触发器中指向原表格。

sqlite_master.rootpage栏保存着表格和索引的根b-tree分页。对于其他类型,这个栏的值为NULL。

sqlite_master.sql保存至相应的归一化的SQL语句。归一化规则:

  • 语句开头的部分会被转化成大写
  • TEMP和TEMPORARY会被移除
  • 数据库名字修饰符会被移除
  • 前导的空白字符会被移除
  • 前面两个关键字之后的空白会被压缩成单个空格

通过UNIQUE和PRIMARY KEY创建的内部索引不会在sqlite_master.sql中体现。

2.6.1. Internal Schema Objects

sqlite_master可能会包含一个内部对象。这些内部对象会以sqlite_开头。内部对象包括:

  • 类似于"sqlite_autoindex_TABLE_N“这种用于实现UNIQUE和PRIMARY KEY约束
  • ”sqlite_sequence“用于跟踪一个表的INTEGER PRIMARY KEY ,如果这个表是AUTOINCREMENT的。
  • ”sqlite_statN“,保存ANALYZE命令生成的统计信息

2.6.2. The sqlite_sequence table

用于实现AUTOINCREMENT。一旦某个表具有这种属性,那么sqlite_sequence就会被生成,而且永远不能丢弃。sqlite_sequence的图样如下:

CREATE TABLE sqlite_sequence(name,seq);

每个条目记录着一个表的AUTOINCREMENT状态。如果AUTOINCREMENT的表满了,则会返回SQLITE_FULL 。

应用程序是可以修改sqlite_sequence的,前提是sqlite_sequence必须存在。应用程序可以删除sqlite_sequence的所有条目,但是不能丢弃这个表。

2.6.3. The sqlite_stat1 table

略。

2.6.4. The sqlite_stat2 table

略。

2.6.5. The sqlite_stat3 table

略。

3. The Rollback Journal

略。

2.6.6. The sqlite_stat4 table

略。

4. The Write-Ahead Log

略。

其他

.headers on
.mode column
PRAGMA table_info(table_name)

或者

select * from pragma_table_info("table_name")

(草草收尾)

Categories

Tags