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;