Schema files

Schema files define a database by providing the SQL commands to create it, populate it, or the statements to execute whenever a client connects to it.

Schema creation file

This file defines the tables, views, indexes, and triggers that make up a database. Here's an example of a schema creation file:

CREATE TABLE library (
  fid             INTEGER PRIMARY KEY AUTOINCREMENT,
  ftype           INTEGER DEFAULT 0 NOT NULL,
  last_sync       INTEGER DEFAULT 0 NOT NULL,
  last_played     INTEGER DEFAULT 0 NOT NULL,
  filename        TEXT DEFAULT '' NOT NULL,
  offset          TEXT DEFAULT '' NOT NULL
);

CREATE TABLE library_genres (
  genre_id        INTEGER PRIMARY KEY AUTOINCREMENT,
  genre           TEXT
);

CREATE INDEX library_genres_index_1 on library_genres(genre);

CREATE TABLE library_artists (
  artist_id       INTEGER PRIMARY KEY AUTOINCREMENT,
  artist          TEXT
);

CREATE INDEX library_artists_index_1 on library_artists(artist);

Data schema file

This file contains SQL commands to populate the database with initial data just after creation. Here's an example of a data schema file:

INSERT INTO library_genres(genre_id, genre) VALUES(1, NULL);

INSERT INTO library_artists(artist_id, artist) VALUES(1, NULL);

INSERT INTO library_artists(artist_id, artist)
  VALUES(2, "The Beatles");

INSERT INTO library_artists(artist_id, artist)
  VALUES(3, "The Rolling Stones");

INSERT INTO library_artists(artist_id, artist) VALUES(4, "The Doors");

Client schema file

This file contains SQL commands to execute whenever a client connects to the database. Here's an example of a client schema file:

PRAGMA journal_mode = PERSIST;
PRAGMA case_sensitive_like = true;
PRAGMA locking_mode = EXCLUSIVE;