FrançaisGet started

Mobile and embedded databases

The database that lives inside the app: SQLite and its quirks, the WAL journal, schema migrations and offline synchronisation.

  • 26 cards
  • 4 topics
  • Free
Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

  1. Concepts
  2. Questions
  3. Sort and link
  4. Scenarios

A few cards from the deck

  • Embedded database: what is it?

    Show the answer

    A database that runs inside the application's own process, with no separate server, and often fits in a single file.

    No installation and no network: the application opens the file. In exchange, concurrency and per-user rights are limited.

  • In SQLite: CREATE TABLE t(n INTEGER); INSERT INTO t VALUES ('12'), ('douze'); SELECT typeof(n) FROM t;. What does the query return, row by row?

    • integer, then text
    • text, then text
    • integer, then null
    • the insertion of 'douze' fails
    Show the answer

    integer, then text

    The text '12' is convertible, so it is stored as an integer. 'douze' is not: SQLite keeps it as text, with no error. The declared type is only an affinity.

  • SQLite stores a whole database in … and allows only … at a time.

    Show the answer

    SQLite stores a whole database in a single file and allows only one writer at a time.

    That is the reason for its success in mobile apps, and for its limit in a highly concurrent service.

  • Type affinity: what is it?

    Show the answer

    SQLite's rule that a column's declared type is a conversion preference, not a strict constraint.

    A convertible value is converted ('12' becomes an integer), otherwise it is kept as is: an integer column can therefore hold text.

  • After an update, the app crashes with “no such column: priorite” for existing users only. Which fix is right?

    • Create the full schema at every startup with CREATE TABLE
    • Numbered migrations applied at startup according to the schema version
    • Ask users to reinstall the application
    • Switch the column to type TEXT so it accepts anything
    Show the answer

    Numbered migrations applied at startup according to the schema version

    The schema is created only at install: old databases lack the column. A CREATE TABLE IF NOT EXISTS does not alter an existing table, and a reinstall wipes local data.

Study this deck