![]() There is no equivalent to PostgreSQL's table access methods. In particular, since there are no hashed indexes, the design does not need to consider whether the index only needs to support equality queries, as opposed to greater/smaller than comparisons. There is no equivalent to PostgreSQL's index types. The exclusive use of B-trees reduces the amount of schema design decisions. SQLite uses B-trees to store both table and index data. SQLite uses clever heuristics, called type affinity, to map SQL column types such as VARCHAR to the major types above.Ĭhrome database schemas should avoid type affinity, and should not include any information ignored by SQLite. Maximum string length specifiers, such as CHAR(255) are ignored.All string types, such as CHAR, CHARACTER VARYING, VARCHAR, and CLOB, are treated as aliases for TEXT.Numeric precision and scale specifiers, such as DECIMAL(5,2) are ignored.All SQL non-integer numeric types, such as DECIMAL, FLOAT, and DOUBLE PRECISION are treated as aliases for REAL.All SQL integer types, such as TINYINT and BIGINT, are treated as aliases for INTEGER.The column types have almost no influence on the encoding of values. SQLite stores index keys and row values (records / tuples) using a tightly packed format that makes heavy use of varints and variable-length fields. These values are sorted using the bitwise binary comparison offered by memcmp. These values are sorted according to a collating sequence or a collating function.īLOB represents sequences of bytes that are opaque to SQLite. TEXT represents strings (sequences of characters) encoded using a supported SQLite encoding. REAL represents IEEE 754-2008 64-bit floating point numbers. ![]() Boolean values ( TRUE and FALSE) are represented as the integer values 1 and 0. INTEGER represents big-endian twos-complement integers. NULL is a special type for the NULL value. SQLite stores values using 5 major types, which are summarized below. ![]() Each B-tree’s depth depends on its record count (number of records stored in it), and on its node width (how many records fit in a node). The number of pages touched when accessing a B-tree depends on the B-tree‘s depth. These pages are nodes belonging to the B-trees associated with the tables mentioned in the statement. Tying everything together: The performance of a SQL statement is roughly the number of database pages touched (read / written) by the statement. The values and their encodings are described in the Value types section. The Indexing section goes into further details.Įach B-tree node stores multiple tuples of values. So, each B-tree is associated with exactly one table. The data in each index (called entries, records or tuples) is also stored in a separate B-tree. The data in each table (usually called rows, records, or tuples) is stored in a separate B-tree. The page size can only be set when a database file is created, and impacts both SQL statement execution speed, and memory consumption. The database file is an array of fixed-size pages, where each page stores a B-tree node. So, designing schemas that perform well requires understanding how SQLite stores data on disk.Īt a very high level, a SQLite database is a forest of B-trees, some of which are B+-trees. The main bottleneck in SQLite database performance is usually disk I/O. The section summarizes aspects of SQLite that are relevant to schema and query design, and may be surprising to readers with prior experience in other popular SQL database systems, such as PostgreSQL and MySQL. TODO: Explain Chrome decisions - exclusive locking, full per-feature isolation (separate databases and page caches) SQLite for database designers TODO: Explain the process model and locking By contrast, most popular RDMBSes, like PostgreSQL and MySQL, are structured as standalone server processes that accept queries from client processes. This is similar to embedded databases like LevelDB and BerkeleyDB. SQLite runs in the application's process, and shares its memory and other resources. SQLite is architected as a library that can be embedded in another application, such as Chrome. SQLite is a relational database management system (RDBMS) that supports most of SQL.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |