Schema files

Schema files contain all the SQL commands to create the database schema the way you want, populate the database after creation, or run whenever a client connects to the database.

Schema creation file

This file defines the tables, views, indexes, and triggers that make up the 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;