1 # SQLite abstraction layer
5 ## SQLite for system designers
7 [SQLite](https://www.sqlite.org/) is a
8 [relational database management system (RDBMS)](https://en.wikipedia.org/wiki/Relational_database#RDBMS)
9 that [supports most of SQL](https://www.sqlite.org/lang.html).
11 SQLite is architected as a library that can be embedded in another application,
12 such as Chrome. SQLite runs in the application's process, and shares its memory
13 and other resources. This is similar to embedded databases like
14 [LevelDB](https://github.com/google/leveldb) and
15 [BerkeleyDB](https://en.wikipedia.org/wiki/Berkeley_DB). By contrast, most
16 popular RDMBSes, like [PostgreSQL](https://www.postgresql.org/) and
17 [MySQL](https://www.mysql.com/), are structured as standalone server processes
18 that accept queries from client processes.
20 TODO: Explain the process model and locking
22 TODO: Explain Chrome decisions -- exclusive locking, full per-feature isolation
23 (separate databases and page caches)
26 ## SQLite for database designers
28 The section summarizes aspects of SQLite that are relevant to schema and
29 query design, and may be surprising to readers with prior experience in other
30 popular SQL database systems, such as
31 [PostgreSQL](https://www.postgresql.org/) and [MySQL](https://www.mysql.com/).
34 ### Data storage model {#storage-model}
36 The main bottleneck in SQLite database performance is usually disk I/O. So,
37 designing schemas that perform well requires understanding how SQLite stores
40 At a very high level, a SQLite database is a forest of
41 [B-trees](https://en.wikipedia.org/wiki/B-tree), some of which are
42 [B+-trees](https://en.wikipedia.org/wiki/B%2B_tree). The database file is an
43 array of fixed-size pages, where each page stores a B-tree node. The page size
44 can only be set when a database file is created, and impacts both SQL statement
45 execution speed, and memory consumption.
47 The data in each table (usually called *rows*, *records*, or *tuples*) is stored
48 in a separate B-tree. The data in each index (called *entries*, *records* or
49 *tuples*) is also stored in a separate B-tree. So, each B-tree is associated
50 with exactly one table. The [*Indexing* section](#indexing-model) goes into
53 Each B-tree node stores multiple tuples of values. The values and their
54 encodings are described in the [*Value types* section](#data-types).
56 Tying everything together: The performance of a SQL statement is roughly the
57 number of database pages touched (read / written) by the statement. These pages
58 are nodes belonging to the B-trees associated with the tables mentioned in the
59 statement. The number of pages touched when accessing a B-tree depends on the
60 B-tree's depth. Each B-tree's depth depends on its record count (number of
61 records stored in it), and on its node width (how many records fit in a node).
64 #### Value types {#data-types}
66 SQLite stores values using
67 [5 major types](https://www.sqlite.org/datatype3.html), which are summarized
70 1. NULL is a special type for the `NULL` value.
72 2. INTEGER represents big-endian twos-complement integers. Boolean values
73 (`TRUE` and `FALSE`) are represented as the integer values 1 and 0.
75 3. REAL represents IEEE 754-2008 64-bit floating point numbers.
77 4. TEXT represents strings (sequences of characters) encoded using a
78 [supported SQLite encoding](https://www.sqlite.org/c3ref/c_any.html). These
80 [sorted](https://www.sqlite.org/datatype3.html#sort_order) according to
81 [a collating sequence](https://www.sqlite.org/datatype3.html#collation) or
82 [a collating function](https://www.sqlite.org/c3ref/create_collation.html).
84 5. BLOB represents sequences of bytes that are opaque to SQLite. These values are
85 sorted using the bitwise binary comparison offered by
86 [memcmp](https://en.cppreference.com/w/cpp/string/byte/memcmp).
88 SQLite stores index keys and row values (records / tuples) using
89 [a tightly packed format](https://sqlite.org/fileformat2.html#record_format)
90 that makes heavy use of [varints](https://sqlite.org/fileformat2.html#varint)
91 and variable-length fields. The column types have almost no influence on the
92 encoding of values. This has the following consequences.
94 * All SQL integer types, such as `TINYINT` and `BIGINT`, are treated as aliases
96 * All SQL non-integer numeric types, such as `DECIMAL`, `FLOAT`, and
97 `DOUBLE PRECISION` are treated as aliases for `REAL`.
98 * Numeric precision and scale specifiers, such as `DECIMAL(5,2)` are ignored.
99 * All string types, such as `CHAR`, `CHARACTER VARYING`, `VARCHAR`, and `CLOB`,
100 are treated as aliases for `TEXT`.
101 * Maximum string length specifiers, such as `CHAR(255)` are ignored.
103 SQLite uses clever heuristics, called
104 [type affinity](https://www.sqlite.org/datatype3.html#type_affinity),
105 to map SQL column types such as `VARCHAR` to the major types above.
107 Chrome database schemas should avoid type affinity, and should not include any
108 information ignored by SQLite.
111 #### Indexing {#indexing-model}
113 SQLite [uses B-trees](https://www.sqlite.org/fileformat2.html#pages) to store
114 both table and index data.
116 The exclusive use of B-trees reduces the amount of schema design decisions.
119 * There is no equivalent to
120 [PostgreSQL's index types](https://www.postgresql.org/docs/13/indexes-types.html).
121 In particular, since there are no hashed indexes, the design does not need to
122 consider whether the index only needs to support equality queries, as opposed
123 to greater/smaller than comparisons.
125 * There is no equivalent to
126 [PostgreSQL's table access methods](https://www.postgresql.org/docs/13/tableam.html).
127 Each table is clustered by a primary key index, which is implicitly stored in
130 By default, table rows (records / tuples) are stored in a B-tree keyed by
131 [rowid](https://sqlite.org/lang_createtable.html#rowid), an automatically
132 assigned 64-bit integer key. Effectively, these tables are clustered by rowid,
133 which acts as an implicit primary key. Opting out of this SQLite-specific
134 default requires appending
135 [`WITHOUT ROWID`](https://sqlite.org/withoutrowid.html) to the `CREATE TABLE`
138 SQLite's [B-tree page format](https://sqlite.org/fileformat2.html#b_tree_pages)
139 has optimized special cases for tables clustered by rowid. This makes rowid the
140 most efficient [surrogate key](https://en.wikipedia.org/wiki/Surrogate_key)
141 implementation in SQLite. To make this optimization easier to use, any column
142 that is a primary key and has an `INTEGER` type is considered an alias for
146 [is stored in a B-tree](https://sqlite.org/fileformat2.html#representation_of_sql_indices).
147 Each index entry is stored as a B-tree node whose key is made up of the record's
148 index key column values, followed by the record's primary key column values.
150 `WITHOUT ROWID` table indexes can include primary key columns without additional
151 storage costs. This is because indexes for `WITHOUT ROWID` tables enjoy
152 [a space optimization](https://sqlite.org/fileformat2.html#representation_of_sql_indices)
153 where columns in both the primary key and the index key are not stored twice in
154 B-tree nodes. Note that data in such tables cannot be recovered by `sql::Recovery`.
157 ### Statement execution model {#query-model}
159 At [a very high level](https://www.sqlite.org/arch.html), SQLite compiles SQL
160 statements (often called *queries*) into bytecode executed by a virtual machine
161 called the VDBE, or [the bytecode engine](https://www.sqlite.org/opcode.html).
162 A compiled statement can be executed multiple times, amortizing the costs of
163 query parsing and planning. Chrome's SQLite abstraction layer makes it easy to
164 use compiled queries.
166 Assuming effective use of cached statements, the performance of a SQL statement
167 comes down to the *query plan* that SQLite generates for the statement. The
168 query plan is the sequence of B-tree accesses used to execute the statement,
169 which determines the number of B-tree pages touched.
171 The rest of this section summarizes the following SQLite documentation pages.
173 1. [query planner overview](https://www.sqlite.org/queryplanner.html)
174 2. [query optimizer overview](https://www.sqlite.org/optoverview.html)
175 3. [`EXPLAIN QUERY PLAN` output description](https://www.sqlite.org/eqp.html)
177 At a high level, a SQLite query plan is a sequence of **nested** loops, where
178 each loop iterates over the data in a B-tree. Each loop can use the current
179 record of the outer loops.
181 TODO: Complete this section. Cover joins, sorting, etc.
183 #### Getting SQLite's query plans
185 Ideally, the SQL schemas and statements used by Chrome features would be simple
186 enough that the query plans would be obvious to the reader.
188 When this isn't the case, the fastest way to get the query plan is to load the
189 schema in [the SQLite shell](https://sqlite.org/cli.html), and use
190 [`EXPLAIN QUERY PLAN`](https://www.sqlite.org/eqp.html).
192 The following command builds a SQLite shell that uses Chrome's build of SQLite,
193 and supports the `EXPLAIN QUERY PLAN` command.
196 autoninja -C out/Default sqlite_dev_shell
199 Inside the SQLite shell, the `.eqp on` directive automatically shows the results
200 of `EXPLAIN QUERY PLAN` for every SQL statement executed in the shell.
203 #### Query steps {#query-step-types}
205 Query steps are the building blocks of SQLite query plans. Each query step is
206 essentially a loop that iterates over the records in a B-tree. These loops
207 differ in terms of how many B-tree pages they touch, and how many records they
208 produce. This sub-section lists the types of steps implemented by SQLite.
212 Scans visit an entire (table or index) B-tree. For this reason, scans are almost
213 never acceptable in Chrome. Most of our features don't have limits on the amount
214 of stored data, so scans can result in an unbounded amount of I/O.
216 A *table scan* visits the entire table's B-tree.
218 A *covering index scan* visits an entire index B-tree, but doesn't access the
219 associated table B-tree.
221 SQLite doesn't have any special optimization for `COUNT(*)` queries. In other
222 words, SQLite does not track subtree sizes in its B-tree nodes.
224 Reviewers sometimes emphasize performance issues by calling the scans *full*
225 table scans and *full* index scans, where "full" references the fact that the
226 number of B-tree pages accessed is proportional to the entire data set stored on
229 TODO: Complete this section. Add examples in a way that doesn't make the section
234 Searches access a subset of a (table or index) B-tree nodes. Searches limit the
235 amount of nodes they need to access based on query restrictions, such as terms
236 in the `WHERE` clause. Seeing a `SEARCH` in a query plan is not a guarantee of
237 performance. Searches can vary wildly in the amount of B-tree pages they need to
240 One of the fastest possible searches is a *table search* that performs exactly
241 one B-tree lookup, and produces at most one record.
243 The other fastest possible search is a *covering index search* that also
244 performs one lookup, and produces at most one record.
246 TODO: Complete this section. Add examples in a way that doesn't make the section
252 The following pieces of advice usually come up in code reviews.
255 ### Quickly iterating on SQL statements
257 [The SQLite shell](https://sqlite.org/cli.html) offers quick feedback for
258 converging on valid SQL statement syntax, and avoiding SQLite features that are
259 disabled in Chrome. In addition, the
260 [`EXPLAIN`](https://www.sqlite.org/lang_explain.html) and
261 [`EXPLAIN QUERY PLAN`](https://www.sqlite.org/eqp.html) statements show the
262 results of SQLite's query planner and optimizer, which are very helpful for
263 reasoning about the performance of complex queries. The SQLite shell directive
264 `.eqp on` automatically issues `EXPLAIN QUERY PLAN` for all future commands.
267 The following commands set up SQLite shells using Chrome's build of SQLite.
270 autoninja -C out/Default sqlite_shell sqlite_dev_shell
273 * `sqlite_shell` runs the SQLite build that we ship in Chrome. It offers the
274 ground truth on whether a SQL statement can be used in Chrome code or not.
275 * `sqlite_dev_shell` enables the `EXPLAIN` and `EXPLAIN QUERY PLAN` statements,
276 as well as a few features used by [Perfetto](https://perfetto.dev/)'s analysis
282 SQLite queries are usually embedded as string literals in C++ code. The
283 advice here has the following goals.
285 1. Easy to read queries. The best defense against subtle bugs is making the
286 queries very easy to read, so that any bugs become obvious at code review
287 time. SQL string literals don't benefit from our code analysis
288 infrastructure, so the only lines of defense against bugs are testing and
291 2. Simplify crash debugging. We will always have a low volume of non-actionable
292 crash reports, because Chrome runs on billions of devices, some of which have
293 faulty RAM or processors.
295 3. No unnecessary performance overheads. The C++ optimizer doesn't understand
296 SQL query literals, so the queries end up as written in the Chrome binary.
297 Extra characters cost binary size, as well as CPU time (which turns into
298 battery usage) during query parsing.
300 4. Match the embedding language (C++) style guide. This reduces the mental
301 context switch overhead for folks who write and/or review C++ code that
304 Format statements like so.
307 static constexpr char kOriginInfoSql[] =
309 "CREATE TABLE origin_infos("
310 "origin TEXT NOT NULL,"
311 "last_modified INTEGER NOT NULL,"
312 "secure INTEGER NOT NULL)";
315 static constexpr char kInsertSql[] =
317 "INSERT INTO infos(origin,last_modified,secure) "
321 static constexpr char kSelectSql[] =
323 "SELECT origin,last_modified,secure FROM origins "
324 "WHERE last_modified>? "
325 "ORDER BY last_modified";
329 * [SQLite keywords](https://sqlite.org/lang_keywords.html) should use ALL CAPS.
330 This makes SQL query literals easier to distinguish and search for.
332 * Identifiers, such as table and row names, should use snake_case.
334 * Identifiers, keywords, and parameter placeholders (`?`) should be separated by
335 exactly one character. Separators may be spaces (` `), commas (`,`), or
336 parentheses (`(`, `)`).
338 * Statement-ending semicolons (`;`) are omitted.
340 * SQL statements are stored in variables typed `static constexpr char[]`, or in
341 string literals passed directly to methods.
343 * [`INSERT` statements](https://sqlite.org/lang_insert.html) should list all the
344 table columns by name, in the same order as the corresponding `CREATE TABLE`
347 * [`SELECT` statements](https://sqlite.org/lang_select.html) should list the
348 desired table columns by name, in the same order as the corresponding
349 `CREATE TABLE` statements. `SELECT *` is strongly discouraged, at least until
350 we have schema checks on database opens.
352 * [`SELECT` statements](https://sqlite.org/lang_select.html) that retrieve more
353 than one row should include an
354 [`ORDER BY` clause](https://sqlite.org/lang_select.html#the_order_by_clause)
355 to clarify the implicit ordering.
356 * SELECTs whose outer loop is a table search or table scan implicitly order
357 results by [rowid](https://sqlite.org/lang_createtable.html#rowid) or, in
358 the case of [`WITHOUT ROWID`](https://sqlite.org/withoutrowid.html) tables,
359 by the table's primary key.
360 * SELECTs whose outer loop is an index scan or index search order results
361 according to that index.
363 * [`CREATE INDEX` statements](https://sqlite.org/lang_createindex.html) should
364 immediately follow the
365 [`CREATE TABLE` statement](https://sqlite.org/lang_createtable.html) for the
368 * Explicit `CREATE UNIQUE INDEX` statements should be preferred to
369 [`UNIQUE` constraints on `CREATE TABLE`](https://sqlite.org/lang_createtable.html#unique_constraints).
371 * Values must either be embedded in the SQL statement string literal, or bound
372 using [parameters](https://www.sqlite.org/lang_expr.html#varparam).
374 * Parameter placeholders should always use the `?` syntax. Alternative syntaxes,
375 such as `?NNN` or `:AAAA`, have few benefits in a codebase where the `Bind`
376 statements are right next to the queries, and are less known to readers.
378 * SQL statements should be embedded in C++ as string literals. The `char[]` type
379 makes it possible for us to compute query length at compile time in the
380 future. The `static` and `constexpr` qualifiers both ensure optimal code
383 * Do not execute multiple SQL statements (e.g., by calling `Step()` or `Run()`
384 on `sql::Statement`) on the same C++ line. It's difficult to get more than
385 line numbers from crash reports' stack traces.
390 Identifiers (table / index / column names and aliases) must not be
391 [current SQLite keywords](https://sqlite.org/lang_keywords.html). Identifiers
392 may not start with the `sqlite_` prefix, to avoid conflicting with the name of a
393 [SQLite internal schema object](https://www.sqlite.org/fileformat2.html#storage_of_the_sql_database_schema).
395 Column types should only be one of the the SQLite storage types (`INTEGER`,
396 `REAL`, `TEXT`, `BLOB`), so readers can avoid reasoning about SQLite's type
399 Columns that will store boolean values should have the `INTEGER` type.
401 Columns that will store `base::Time` values should have the `INTEGER` type.
402 Values should be serialized using `sql::Statement::BindTime()` and deserialized
403 using `sql::Statement::ColumnTime()`.
405 Column types should not include information ignored by SQLite, such as numeric
406 precision or scale specifiers, or string length specifiers.
409 [`NOT NULL` constraints](https://sqlite.org/lang_createtable.html#not_null_constraints)
410 whenever possible. This saves maintainers from having to reason about the less
411 intuitive cases of [`NULL` handling](https://sqlite.org/nulls.html).
413 `NOT NULL` constraints must be explicitly stated in column definitions that
414 include `PRIMARY KEY` specifiers. For historical reasons, SQLite
415 [allows NULL primary keys](https://sqlite.org/lang_createtable.html#the_primary_key)
416 in most cases. When a table's primary key is composed of multiple columns,
417 each column's definition should have a `NOT NULL` constraint.
419 Columns should avoid `DEFAULT` values. Columns that have `NOT NULL` constraints
420 and lack a `DEFAULT` value are easier to review and maintain, as SQLite takes
421 over the burden of checking that `INSERT` statements aren't missing these
424 Surrogate primary keys should use the column type `INTEGER PRIMARY KEY`, to take
425 advantage of SQLite's rowid optimizations.
426 [`AUTOINCREMENT`](https://www.sqlite.org/autoinc.html) should only be used where
427 primary key reuse would be unacceptable.
430 ### Discouraged features
432 SQLite exposes a vast array of functionality via SQL statements. The following
433 features are not a good match for SQL statements used by Chrome feature code.
435 #### PRAGMA statements {#no-pragmas}
437 [`PRAGMA` statements](https://www.sqlite.org/pragma.html) should never be used
438 directly. Chrome's SQLite abstraction layer should be modified to support the
439 desired effects instead.
441 Direct `PRAGMA` use limits our ability to customize and secure our SQLite build.
442 `PRAGMA` statements may turn on code paths with less testing / fuzzing coverage.
443 Furthermore, some `PRAGMA` statements invalidate previously compiled queries,
444 reducing the efficiency of Chrome's compiled query cache.
446 #### Virtual tables {#no-virtual-tables}
448 [`CREATE VIRTUAL TABLE` statements](https://www.sqlite.org/vtab.html) should not
449 be used. The desired functionality should be implemented in C++, and access
450 storage using standard SQL statements.
452 Virtual tables are [SQLite's module system](https://www.sqlite.org/vtab.html).
453 SQL statements on virtual tables are essentially running arbitrary code, which
454 makes them very difficult to reason about and maintain. Furthermore, the virtual
455 table implementations don't receive the same level of fuzzing coverage as the
458 Access to virtual tables is disabled by default for SQLite databases opened with
459 Chrome's `sql::Database` infrastructure. This is intended to steer feature
460 developers away from the discouraged feature.
462 Chrome's SQLite build has virtual table functionality reduced to the minimum
463 needed to support [FTS3](https://www.sqlite.org/fts3.html) in WebSQL, and an
465 [SQLite's run-time loading mechanism](https://www.sqlite.org/loadext.html) is
467 [built-in virtual tables](https://www.sqlite.org/vtablist.html) are disabled as
470 Ideally we would disable SQLite's virtual table support using
471 [SQLITE_OMIT_VIRTUALTABLE](https://sqlite.org/compile.html#omit_virtualtable)
472 once [WebSQL](https://www.w3.org/TR/webdatabase/) is removed from Chrome, but
473 virtual table support is required to use SQLite's [built-in corruption recovery
474 module](https://www.sqlite.org/recovery.html). The [SQLITE_DBPAGE virtual
475 table](https://www.sqlite.org/dbpage.html) is also enabled only for corruption
476 recovery and should not be used in Chrome.
478 #### Foreign key constraints {#no-foreign-keys}
480 [SQL foreign key constraints](https://sqlite.org/foreignkeys.html) should not be
481 used. All data validation should be performed using explicit `SELECT` statements
482 (generally wrapped as helper methods) inside transactions. Cascading deletions
483 should be performed using explicit `DELETE` statements inside transactions.
485 Chrome features cannot rely on foreign key enforcement, due to the
486 possibility of data corruption. Furthermore, foreign key constraints make it
487 more difficult to reason about system behavior (Chrome feature code + SQLite)
488 when the database gets corrupted. Foreign key constraints also make it more
489 difficult to reason about query performance.
491 As a result, foreign key constraints are not enforced on SQLite databases
492 opened with Chrome's `sql::Database` infrastructure.
495 [WebSQL](https://www.w3.org/TR/webdatabase/) is removed from Chrome, we plan
496 to disable SQLite's foreign key support using
497 [SQLITE_OMIT_FOREIGN_KEY](https://sqlite.org/compile.html#omit_foreign_key).
499 #### CHECK constraints {#no-checks}
501 [SQL CHECK constraints](https://sqlite.org/lang_createtable.html#check_constraints)
502 should not be used, for the same reasons as foreign key constraints. The
503 equivalent checks should be performed in C++, typically using `DCHECK`.
506 [WebSQL](https://www.w3.org/TR/webdatabase/) is removed from Chrome, we plan
507 to disable SQLite's CHECK constraint support using
508 [SQLITE_OMIT_CHECK](https://sqlite.org/compile.html#omit_check).
510 #### Triggers {#no-triggers}
512 [SQL triggers](https://sqlite.org/lang_createtrigger.html) should not be used.
514 Triggers significantly increase the difficulty of reviewing and maintaining
515 Chrome features that use them.
517 Triggers are not executed on SQLite databases opened with Chrome's
518 `sql::Database` infrastructure. This is intended to steer feature developers
519 away from the discouraged feature.
521 After [WebSQL](https://www.w3.org/TR/webdatabase/) is removed from Chrome, we
522 plan to disable SQLite's trigger support using
523 [SQLITE_OMIT_TRIGGER](https://sqlite.org/compile.html#omit_trigger).
525 #### Common Table Expressions {#no-ctes}
527 [SQL Common Table Expressions (CTEs)](https://sqlite.org/lang_with.html) should
528 not be used. Chrome's SQL schemas and queries should be simple enough that
529 the factoring afforded by
530 [ordinary CTEs](https://sqlite.org/lang_with.html#ordinary_common_table_expressions)
532 [Recursive CTEs](https://sqlite.org/lang_with.html#recursive_common_table_expressions)
533 should be implemented in C++.
535 Common Table Expressions do not open up any query optimizations that would not
536 be available otherwise, and make it more difficult to review / analyze queries.
538 #### Views {#no-views}
540 SQL views, managed by the
541 [`CREATE VIEW` statement](https://www.sqlite.org/lang_createview.html) and the
542 [`DROP VIEW` statement](https://www.sqlite.org/lang_dropview.html), should not
543 be used. Chrome's SQL schemas and queries should be simple enough that the
544 factoring afforded by views is not necessary.
546 Views are syntactic sugar, and do not open up any new SQL capabilities. SQL
547 statements on views are more difficult to understand and maintain, because of
548 the extra layer of indirection.
550 Access to views is disabled by default for SQLite databases opened with Chrome's
551 `sql::Database` infrastructure. This is intended to steer feature developers
552 away from the discouraged feature.
555 [WebSQL](https://www.w3.org/TR/webdatabase/) is removed from Chrome, we plan
556 to disable SQLite's VIEW support using
557 [SQLITE_OMIT_VIEW](https://www.sqlite.org/compile.html#omit_view).
559 #### Double-quoted string literals {#no-double-quoted-strings}
561 String literals should always be single-quoted. That being said, string literals
562 should be rare in Chrome code, because any user input must be injected using
563 statement parameters and the `Statement::Bind*()` methods.
565 Double-quoted string literals are non-standard SQL syntax. The SQLite authors
566 [currently consider this be a misfeature](https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted).
568 SQLite support for double-quoted string literals is disabled for databases
569 opened with Chrome's `sql::Database` infrastructure. This is intended to steer
570 feature developers away from this discouraged feature.
573 [WebSQL](https://www.w3.org/TR/webdatabase/) is removed from Chrome, we plan
574 to disable SQLite's support for double-quoted string literals using
575 [SQLITE_DQS=0](https://www.sqlite.org/compile.html#dqs).
577 #### Compound SELECT statements {#no-compound-queries}
579 [Compound SELECT statements](https://www.sqlite.org/lang_select.html#compound_select_statements)
580 should not be used. Such statements should be broken down into
581 [simple SELECT statements](https://www.sqlite.org/lang_select.html#simple_select_processing),
582 and the operators `UNION`, `UNION ALL`, `INTERSECT` and `EXCEPT` should be
585 A single compound SELECT statement is more difficult to review and properly
586 unit-test than the equivalent collection of simple SELECT statements.
587 Furthermore, the compound SELECT statement operators can be implemented more
588 efficiently in C++ than in SQLite's bytecode interpreter (VDBE).
591 [WebSQL](https://www.w3.org/TR/webdatabase/) is removed from Chrome, we plan
592 to disable SQLite's compound SELECT support using
593 [SQLITE_OMIT_COMPOUND_SELECT](https://www.sqlite.org/compile.html#omit_compound_select).
595 #### Built-in functions {#no-builtin-functions}
597 SQLite's [built-in functions](https://sqlite.org/lang_corefunc.html) should be
598 only be used in SQL statements where they unlock significant performance
599 improvements. Chrome features should store data in a format that leaves the most
600 room for query optimizations, and perform any necessary transformations after
601 reading / before writing the data.
603 * [Aggregation functions](https://sqlite.org/lang_aggfunc.html) are best
604 replaced with C++ code that iterates over rows and computes the desired
606 * [Date and time functions](https://sqlite.org/lang_datefunc.html) are best
607 replaced by `base::Time` functionality.
608 * String-processing functions, such as
609 [`printf()`](https://sqlite.org/printf.html) and `trim()` are best replaced
610 by C++ code that uses the helpers in `//base/strings/`.
611 * Wrappers for [SQLite's C API](https://sqlite.org/c3ref/funclist.html), such as
612 `changes()`, `last_insert_rowid()`, and `total_changes()`, are best replaced
613 by functionality in `sql::Database` and `sql::Statement`.
614 * SQLite-specific functions, such as `sqlite_source_id()` and
615 `sqlite_version()` should not be necessary in Chrome code, and may suggest a
616 problem in the feature's design.
618 [Math functions](https://sqlite.org/lang_mathfunc.html) and
619 [Window functions](https://sqlite.org/windowfunctions.html#biwinfunc) are
620 disabled in Chrome's SQLite build.
622 #### ATTACH DATABASE statements {#no-attach}
624 [`ATTACH DATABASE` statements](https://www.sqlite.org/lang_attach.html) should
625 not be used. Each Chrome feature should store its data in a single database.
626 Chrome code should not assume that transactions across multiple databases are
629 We plan to remove all existing `ATTACH DATABASE` use from Chrome.
632 ### Disabled features
634 We aim to disable SQLite features that should not be used in Chrome, subject to
635 the constraint of keeping WebSQL's feature set stable. We currently disable all
636 new SQLite features, to avoid expanding the attack surface exposed to WebSQL.
637 This stance may change once WebSQL is removed from Chrome.
639 The following SQLite features have been disabled in Chrome.
643 Chrome features should prefer
644 [procotol buffers](https://developers.google.com/protocol-buffers) to JSON for
645 on-disk (persistent) serialization of extensible structured data.
647 Chrome features should store the values used by indexes directly in their own
648 columns, instead of relying on
649 [SQLite's JSON support](https://www.sqlite.org/json1.html).
653 [SQLite's UPSERT implementation](https://www.sqlite.org/lang_UPSERT.html) has
654 been disabled in order to avoid increasing WebSQL's attack surface. UPSERT is
655 disabled using the `SQLITE_OMIT_UPSERT` macro, which is not currently included
656 in [the SQLite compile-time option list](https://www.sqlite.org/compile.html),
657 but exists in the source code.
659 We currently think that the new UPSERT functionality is not essential to
660 implementing Chrome features efficiently. An example where UPSERT is necessary
661 for the success of a Chrome feature would likely get UPSERT enabled.
663 #### Window functions
665 [Window functions](https://sqlite.org/windowfunctions.html#biwinfunc) have been
666 disabled primarily because they cause a significant binary size increase, which
667 leads to a corresponding large increase in the attack surface exposed to WebSQL.
669 Window functions increase the difficulty of reviewing and maintaining the Chrome
670 features that use them, because window functions add complexity to the mental
671 model of query performance.
673 We currently think that this maintenance overhead of window functions exceeds
674 any convenience and performance benefits (compared to simpler queries