init.sql

PRAGMA case_sensitive_like = false;
PRAGMA encoding            = 'UTF-8';
PRAGMA recursive_triggers  = true;
PRAGMA foreign_keys        = true;


-- Definition of a kara

CREATE TABLE IF NOT EXISTS kara
  ( id          INTEGER PRIMARY KEY AUTOINCREMENT
  , song_name   TEXT    NOT NULL
  , source_name TEXT    NOT NULL
  , category    INTEGER NOT NULL REFERENCES kara_category
  , song_type   INTEGER NOT NULL REFERENCES kara_type
  , song_number INTEGER NOT NULL CHECK(song_number > 0)
  , language    TEXT    REFERENCES language
  , file_path   TEXT    NOT NULL UNIQUE
  , is_new      INTEGER NOT NULL
  , author_name TEXT
  , author_year INTEGER CHECK(author_year > 0)
  , available   INTEGER CHECK(available = 0 OR available = 1) DEFAULT 1 NOT NULL
  , string      TEXT GENERATED ALWAYS AS
    ( song_type || ' - ' || language || ' / ' || source_name || ' - ' || category ||
      song_number || ' - ' || song_name || ' [ ' || author_name || ' ]' ||
      CASE WHEN available = 0 THEN '(U)' ELSE '' END
    ) STORED
  );

CREATE TABLE IF NOT EXISTS kara_type
  ( id    INTEGER PRIMARY KEY
  , name  TEXT    NOT NULL
  );

INSERT INTO kara_type (id, name) VALUES
  (1, 'vo'), (2, 'va'), (3, 'amv'), (4, 'cdg'), (5, 'autres'), ( 6, 'vocaloid');

CREATE TABLE IF NOT EXISTS kara_category
  ( id   INTEGER PRIMARY KEY
  , name TEXT    NOT NULL
  );

INSERT INTO kara_category (id, name) VALUES
  (1, 'ED/d*'), (2, 'OP/d*'), (3, 'AMV'), (4, 'IS'), (5, 'VOCA'), (6, 'LIVE'),
  (7, 'CDG'), (8, 'PV'), (9, 'MV');

CREATE TABLE IF NOT EXISTS language
  ( id   INTEGER PRIMARY KEY
  , name TEXT    NOT NULL
  );

INSERT INTO language (id, name) VALUES
  (1, 'jp'), (2, 'fr'), (3, 'en'), (4, 'ru'), (5, 'sp'), (6, 'it'), (7, 'ch'),
  (8, 'latin'), (9, 'multi'), (10, 'undefined');

-- Playlists are sets of karas that are meant to be played together, kinda like
-- M3U except it's stored in a SQL database.

CREATE TABLE IF NOT EXISTS playlist
  ( id          INTEGER PRIMARY KEY AUTOINCREMENT
  , name        TEXT    NOT NULL UNIQUE
  , last_update INTEGER NOT NULL
  );

CREATE TABLE IF NOT EXISTS kara_playlist
  ( kara_id     INTEGER REFERENCES kara     ON DELETE CASCADE
  , playlist_id INTEGER REFERENCES playlist ON DELETE CASCADE
  , PRIMARY KEY (kara_id, playlist_id)
  ) WITHOUT ROWID;


-- The queue, to quote the MPD docs, is (sometimes called “current playlist”) a
-- list of kara to be played by [lektor]. To play a kara, add it to to the
-- queue and start playback. Most clients offer an interface to edit the queue.
-- https://www.musicpd.org/doc/html/user.html#the-queue

CREATE TABLE IF NOT EXISTS queue
  ( position INTEGER PRIMARY KEY AUTOINCREMENT CHECK(position > 0)
  , kara_id  INTEGER REFERENCES kara
  , priority INTEGER NOT NULL DEFAULT 1 CHECK(priority > 0 AND priority < 6)
  );


-- The user table
-- Used for the [password {passwd}] MPD command. The documentation can be found
-- here: https://www.musicpd.org/doc/html/protocol.html#connection-settings.
-- The password command is used to restrict access to specific commands.

CREATE TABLE IF NOT EXISTS users
  ( username TEXT NOT NULL UNIQUE
  , password TEXT NOT NULL UNIQUE
  , PRIMARY KEY (username, password)
  ) WITHOUT ROWID;

INSERT INTO users (username, password) VALUES ('sakura', 'hashire');


-- The stickers table
-- Used to implement the stickers MPD functionnality, documentation can be found
-- here: https://www.musicpd.org/doc/html/protocol.html#stickers. Need to be
-- authentified to use stickers commands. Support tags for `song` and `plt`.

CREATE TABLE IF NOT EXISTS 'stickers'
  ( id      INTEGER PRIMARY KEY
  , name    TEXT    NOT NULL UNIQUE
  );

CREATE TABLE IF NOT EXISTS 'stickers.song'
  ( id      INTEGER REFERENCES kara     ON DELETE CASCADE
  , sticker INTEGER REFERENCES stickers ON DELETE CASCADE
  , value   INTEGER NOT NULL
  , PRIMARY KEY (id, sticker)
  ) WITHOUT ROWID;

CREATE TABLE IF NOT EXISTS 'stickers.plt'
  ( id      INTEGER REFERENCES playlist ON DELETE CASCADE
  , sticker INTEGER REFERENCES stickers ON DELETE CASCADE
  , value   INTEGER NOT NULL
  , PRIMARY KEY (id, sticker)
  ) WITHOUT ROWID;


-- Some useful values:
-- last_update is the timestamp of the last time the table of kara has been
-- updated.  This is so lektor doesn't have to read all kara in the filesystem,
-- just the new ones (where their modified timestamp is greater than
-- last_update).  If last_update is NULL then the database is empty.
CREATE TABLE IF NOT EXISTS misc
  ( id          INTEGER PRIMARY KEY DEFAULT 42 CHECK(id = 42)
  , last_update INTEGER
  );

INSERT INTO misc (id) VALUES (42);


-- A simple view to select things in the queue, where the order matters and
-- where we need to see all the priorities and position in a flatten manner.
CREATE VIEW IF NOT EXISTS queue_ AS
  SELECT
    ROW_NUMBER() OVER(ORDER BY priority DESC, position ASC) AS position,
    kara_id,
    priority,
    queue.position AS old_position
  FROM queue;