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