up.sql

-- A list of repos karas where downloaded from. All repos should have different
-- names! Their IDs are local to the client.
CREATE TABLE repo
  ( id   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
  , name TEXT    NOT NULL UNIQUE
  );

-- Link a kara in the local referencial to its reference in the distant repo.
-- Local IDs are all uniques and every client should be expected to have
-- different ones.
CREATE TABLE repo_kara
  ( repo_id       INTEGER NOT NULL REFERENCES repo(id)
  , repo_kara_id  INTEGER NOT NULL
  , local_kara_id INTEGER NOT NULL REFERENCES kara(id)
  , PRIMARY KEY (repo_id, repo_kara_id, local_kara_id)
  );

-- A kara entry in the databse.
CREATE TABLE kara
  ( id          INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
  , is_dl       BOOLEAN NOT NULL DEFAULT false
  , song_title  TEXT    NOT NULL
  , song_type   TEXT    NOT NULL
  , song_origin TEXT    NOT NULL
  , source_name TEXT    NOT NULL
  , language    TEXT    NOT NULL REFERENCES iso_639_1(code)
  , kara_hash   TEXT    NOT NULL -- TEXT ABOVE + HASH OF FILE IN FS
  );

-- We can have multiple kara makers for one kara.
CREATE TABLE kara_makers
  ( id   INTEGER NOT NULL REFERENCES kara ON DELETE CASCADE
  , name TEXT    NOT NULL
  , PRIMARY KEY (id, name)
  );

-- Tags are informations used to be able to make queries in a easier way.
CREATE TABLE tag
  ( id   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
  , name TEXT    NOT NULL UNIQUE
  );

-- The content of a tag for kara. Multiple tags for a single kara with the same
-- tag id means that there a list of values.
CREATE TABLE kara_tags
  ( kara_id INTEGER NOT NULL REFERENCES kara(id) ON DELETE CASCADE
  , tag_id  INTEGER NOT NULL REFERENCES tag(id)  ON DELETE CASCADE
  , value   TEXT
  , PRIMARY KEY (kara_id, tag_id, value)
  );

-- Store the history of played karas from the queue. For now we allow a kara to
-- appear multiple times in the history.
CREATE TABLE history
  ( id      INTEGER NOT NULL REFERENCES kara(id) ON DELETE CASCADE
  , epoch   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
  );

-- The list of ISO 639-1 languages with their associated codes.
-- Should add an entry in the api to query available languages.
CREATE TABLE iso_639_1
  ( code     TEXT    NOT NULL PRIMARY KEY
  , name_en  TEXT    NOT NULL UNIQUE
  , is_iso   BOOLEAN NOT NULL DEFAULT false
  , is_macro BOOLEAN NOT NULL DEFAULT false
  );

-- As defined in ISO 639-1:
-- https://archive.wikiwix.com/cache/index2.php?url=http%3A%2F%2Fwww.sil.org%2Fiso639-3%2Fcodes.asp%3Forder%3D639_1%26letter%3D%2525#federation=archive.wikiwix.com&tab=url
INSERT OR REPLACE INTO iso_639_1 (is_macro, is_iso, code, name_en) VALUES
  ( true,  true, "ar", "Arabic"         ),
  ( false, true, "br", "Breton"         ),
  ( false, true, "ca", "Catalan"        ),
  ( false, true, "de", "German"         ),
  ( false, true, "el", "Greek"          ),
  ( false, true, "en", "English"        ),
  ( false, true, "eo", "Esperanto"      ),
  ( false, true, "es", "Spanish"        ),
  ( false, true, "eu", "Basque"         ),
  ( true,  true, "fa", "Persian"        ),
  ( false, true, "fr", "French"         ),
  ( false, true, "he", "Hebrew"         ),
  ( true,  true, "ie", "Interlingue"    ),
  ( false, true, "it", "Italian"        ),
  ( false, true, "ja", "Japanese"       ),
  ( false, true, "ko", "Korean"         ),
  ( false, true, "la", "Latin"          ),
  ( false, true, "nl", "Dutch"          ),
  ( false, true, "no", "Norwegian"      ),
  ( false, true, "oc", "Occitan"        ),
  ( false, true, "pl", "Polish"         ),
  ( false, true, "pt", "Portuguese"     ),
  ( false, true, "ru", "Russian"        ),
  ( false, true, "sv", "Swedish"        ),
  ( false, true, "vi", "Vietnamese"     ),
  ( false, true, "zh", "Chinese"        ),
  ( false, true, "zu", "Zulu"           ),
  -- The things we added because they were not defined in the ISO 639-1 and
  -- because we need them to distinguish some karas.
  ( true,  false, "fx", "Fictional"     ),
  ( true,  false, "ot", "Joker"         );