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.
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);
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");
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;