Skip to content
Extraits de code Groupes Projets

Réécriture de init.sql

Fusionnées Réécriture de init.sql
schema vers master
1 fil de conversation non résolu
Fusionnées ‮ a demandé de fusionner schema vers master
1 fil de conversation non résolu
Comparer
et
2 files
+ 3191
− 254
Comparer les modifications
  • Côte à côte
  • En ligne

Fichiers

database/init.sql
+ 104
− 254
----------------------------------------------------------
---- SETTINGS --------------------------------------------
----------------------------------------------------------
PRAGMA case_sensitive_like = false; -- ignore case for the 'LIKE' function
PRAGMA encoding = 'UTF-8'; -- store things in utf8
PRAGMA recursive_triggers = true; -- recursiv triggers
----------------------------------------------------------
---- TABLES ----------------------------------------------
----------------------------------------------------------
-- a simple kara table
CREATE TABLE IF NOT EXISTS Kara (
name TEXT, -- name of the song
title TEXT, -- name of the anime/game/amv/song...
type TEXT, -- ED/d*|OP/d*|AMV|IS|VOCA|LIVE|PV|MV
category TEXT, -- vo|va|vf|voca|cdg|autre|amv
nouveau BOOLEAN NOT NULL DEFAULT FALSE, -- is this kara a nouveau kara?
hash TEXT UNIQUE NOT NULL -- hash = sha1(name + title + type + category)
);
-- a table to describe the authors of a kara
CREATE TABLE IF NOT EXISTS People (
pseudo TEXT UNIQUE PRIMARY KEY NOT NULL, -- the pseudo
promo INTEGER, -- the promo, an integer at least positive
CHECK(promo >= 0) -- can do almost anything
);
-- a table to link People to the Kara they
-- have done. Usefull to get the right folder
-- in the case of a "nouveau" kara.
CREATE TABLE IF NOT EXISTS KaraToPeople (
pseudo TEXT NOT NULL, -- the author of the kara
hash TEXT UNIQUE NOT NULL, -- only one author by kara
PRIMARY KEY (pseudo, hash), -- each relation is unique
FOREIGN KEY (pseudo) REFERENCES People(pseudo),
FOREIGN KEY (hash) REFERENCES Kara(hash)
);
-- associate an ID (artificial, can change later,
-- won't change anything) to a hash (ie. to a kara)
CREATE TABLE IF NOT EXISTS Bucket (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, -- a way for the user to select the kara
hash TEXT UNIQUE NOT NULL REFERENCES Kara(hash) -- the kara pointed by this id
);
-- a playlist is only a name (at the moment)
CREATE TABLE IF NOT EXISTS Playlist (
name TEXT PRIMARY KEY UNIQUE NOT NULL, -- The name of the playlist
useNouveau BOOLEAN NOT NULL DEFAULT TRUE -- Should use the nouveau kara ?
);
-- link between a kara and a playlist
CREATE TABLE IF NOT EXISTS Belongs (
name TEXT NOT NULL, -- the name of the playlist
hash TEXT NOT NULL, -- the hash of the kara
PRIMARY KEY(name, hash),
FOREIGN KEY(name) REFERENCES Playlist(name),
FOREIGN KEY(hash) REFERENCES Kara(hash)
);
-- the history if we are in playlist mode
CREATE TABLE IF NOT EXISTS History (
index_hist INTEGER PRIMARY KEY AUTOINCREMENT, -- the position in the history
hash TEXT UNIQUE NOT NULL, -- the hash of the kara (unique in the history)
FOREIGN KEY(hash) REFERENCES Kara(hash)
);
-- A table to store the position in the history
CREATE TABLE IF NOT EXISTS HistoryPosition (
id INTEGER PRIMARY KEY UNIQUE DEFAULT 0, -- to ensure there is only one row in this table
length INTEGER NOT NULL DEFAULT 100, -- the length of the history
index_hist INTEGER, -- always point a kara in the history. If the history is
-- empty reading that row has an undefined behaviour
CHECK(id = 0) -- only one row
);
-- the playlist of the mpchc mode
-- played files from this type of playlist will be
-- putted in the history, but when selecting files
-- from this kind of playlist the history won't be
-- taken into account.
CREATE TABLE IF NOT EXISTS MpcHc (
config_id INTEGER, -- the number of the config
play_index INTEGER, -- the position in the table
hash TEXT, -- the hash of the kara if `fromDatabase` is `true`
filename TEXT, -- the path to the file if `fromDatabase` is `false`
fromDatabase BOOLEAN NOT NULL -- indicate whever the kara is from the database or the filesystem
);
-- the config/statte of the application for
-- the MpcHc playlists
CREATE TABLE IF NOT EXISTS Config (
id INTEGER PRIMARY KEY AUTOINCREMENT, -- to have multiple configurations (ie. multiple playlists)
name TEXT UNIQUE DEFAULT "configuration", -- the name of that configuration (ie. name of the MpcHc playlist)
locked BOOLEAN DEFAULT FALSE, -- config is initialized ? if not the dynamic playlist is locked
play_index INTEGER NOT NULL, -- current position in the mpchc playlist.
random BOOLEAN NOT NULL DEFAULT FALSE -- read the playlist randomly or not
);
--------------------------------------------------------------------------------------------------------------------------------------------------
---- TRIGERS -------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------
-- trigger to limit the number of kara in the history and record the last inserted
-- element in the history. If a kara is inserted, that means that we no longer
-- read the history and resumed the normal play.
CREATE TRIGGER IF NOT EXISTS TriggerHistoryOnInsert AFTER INSERT ON History
BEGIN
DELETE FROM history WHERE ROWID NOT IN (SELECT ROWID FROM history ORDER BY ROWID DESC LIMIT 100); -- Delete old elements of the history
UPDATE HistoryPosition SET index_hist = NEW.index_hist; -- Update the current index of the history
END;
-- trigger to delete the rows in Belongs when deleting an entry in Playlist
CREATE TRIGGER IF NOT EXISTS TriggerBelongsDeletePlaylistName BEFORE DELETE ON Playlist
BEGIN
DELETE FROM Belongs WHERE Belongs.name = OLD.name; -- The content of the playlist to delete
END;
-- trigger to delete the rows in the MpcHc table when deleting the dynamic they belongs
-- to in the Config table.
CREATE TRIGGER IF NOT EXISTS TriggerConfigDeleteDynamicPlaylist BEFORE DELETE ON Config
BEGIN
DELETE FROM MpcHc WHERE MpcHc.config_id = OLD.id; -- The child of the Config to delete
END;
-- security trigger to avoid deleting content of automatic playlists (e.g. vo, autres, ...)
CREATE TRIGGER IF NOT EXISTS TriggerPreservAutomaticPlaylists BEFORE DELETE ON Belongs
BEGIN WITH RECURSIVE protected(p) AS (VALUES('vo'), ('va'), ('vf'), ('voca'), ('cdg'), ('autre'), ('amv'), ('all')) SELECT CASE -- Basic protected playlists
WHEN OLD.name IN protected THEN RAISE (ABORT, 'The playlist is protected') -- In the future, aditional protected playlist may be stored in another table.
END; END;
-- yet another security trigger to avoid deleting automatic playlists (e.g. vo, autres, ...)
CREATE TRIGGER IF NOT EXISTS TriggerPreservAutomaticPlaylists BEFORE DELETE ON Playlist
BEGIN WITH RECURSIVE protected(p) AS (VALUES('vo'), ('va'), ('vf'), ('voca'), ('cdg'), ('autre'), ('amv'), ('all')) SELECT CASE -- Basic protected playlists
WHEN OLD.name IN protected THEN RAISE (ABORT, 'The playlist is protected') -- In the future, aditional protected playlist may be stored in another table.
END; END;
-- trigger for actions to be done after a new kara is added in the database
-- ED/d*|OP/d*|AMV|IS|VOCA is not checked at the moment, must be done before the insert on kara
CREATE TRIGGER IF NOT EXISTS TriggerActionsOnAddKara AFTER INSERT ON Kara
BEGIN
INSERT INTO Bucket(hash) VALUES (NEW.hash); -- Generate an ID for the kara
INSERT INTO Belongs(hash, name) VALUES (NEW.hash, NEW.category), (NEW.hash, 'all'); -- Add the kara to the right automatic playlist
END;
--------------------------------------------------------------------------------------------------------------------------------------------------
---- VIEWS ---------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------
-- View the current index in the history and its validity
CREATE VIEW IF NOT EXISTS ViewHistoryIndex AS
SELECT
HistoryPosition.index_hist AS IDX, -- the index in the history
IFNULL((SELECT History.index_hist FROM History, HistoryPosition WHERE History.index_hist < HistoryPosition.index_hist ORDER BY History.index_hist DESC LIMIT 1), -1) PREV_IDX, -- get the previous id or a default invalid one if none
IFNULL((SELECT History.index_hist FROM History, HistoryPosition WHERE History.index_hist > HistoryPosition.index_hist ORDER BY History.index_hist ASC LIMIT 1), -1) NEXT_IDX, -- get the next id or a default invalid one if none
IFNULL((SELECT History.hash FROM History, HistoryPosition WHERE History.index_hist < HistoryPosition.index_hist ORDER BY History.index_hist DESC LIMIT 1), '') PREV_HASH, -- get the previous hash or a default invalid one if none
IFNULL((SELECT History.hash FROM History, HistoryPosition WHERE History.index_hist > HistoryPosition.index_hist ORDER BY History.index_hist ASC LIMIT 1), '') NEXT_HASH, -- get the next hash or a default invalid one if none
CASE WHEN HistoryPosition.index_hist IN (SELECT index_hist FROM History) THEN TRUE ELSE FALSE END VALID -- the validity of the current index
FROM HistoryPosition LIMIT 1;
CREATE VIEW IF NOT EXISTS MpcHcIndexes AS SELECT config_id, play_index, COUNT(play_index) FROM MpcHc GROUP BY config_id; -- View the indexes of all configurations in MpcHc playlists
CREATE VIEW IF NOT EXISTS ViewPlaylistSummary AS SELECT Belongs.name AS PLNAME, COUNT(Belongs.hash) AS PLSIZE FROM Belongs GROUP BY Belongs.name; -- View all playlists with their number of elements
CREATE VIEW IF NOT EXISTS ViewKaraCount AS SELECT COUNT(Kara.hash) AS LEN FROM Kara; -- View the total number of kara in the database
-- View all formated kara with their id
CREATE VIEW IF NOT EXISTS ViewKara AS
SELECT
(Kara.category || '/' || Kara.name || ' - ' || Kara.type || ' - ' || Kara.title) AS KARA, -- Pseudo relative filepath
Bucket.id AS ID, -- the visible id
Kara.hash AS HASH -- the hash of the kara
FROM Bucket JOIN Kara ON Bucket.hash = Kara.hash GROUP BY Kara.hash HAVING Kara.nouveau = FALSE;
-- View a relative path to a nouveau kara with their author
CREATE VIEW IF NOT EXISTS ViewNouveauKara AS
SELECT
('nouveaux/' || People.pseudo || '/' || Kara.category || '/' || Kara.name || ' - ' || Kara.type || ' - ' || Kara.title) AS KARA, -- Pseudo relative filepath
Bucket.id AS ID, -- the visible id
Kara.hash AS HASH, -- the hash of the kara
People.pseudo AS PSEUDO -- the name of the bakateux that timed the kara
FROM Kara
JOIN Bucket ON Kara.hash = Bucket.hash
JOIN KaraToPeople ON KaraToPeople.hash = Kara.hash
JOIN People ON People.pseudo = KaraToPeople.pseudo
GROUP BY Kara.hash HAVING Kara.nouveau = TRUE;
-- Playlist view using Kara views
CREATE VIEW IF NOT EXISTS ViewPlaylistContent AS
SELECT
Belongs.name AS PLNAME, -- The name of the playlist
Bucket.id AS ID, -- The visible id of the kara
ViewKara.KARA AS KARA -- The kara
FROM Belongs JOIN Bucket ON Belongs.hash = Bucket.hash JOIN ViewKara ON Belongs.hash = ViewKara.HASH;
CREATE VIEW IF NOT EXISTS ViewKaraAll AS SELECT KARA, ID, HASH FROM ViewKara UNION SELECT KARA, ID, HASH FROM ViewNouveauKara; -- View all Kara, nouveaux or not
-- View all playlists with all their content, unused for the moment, will be because take into account the nouveau kara
CREATE VIEW IF NOT EXISTS ViewPlaylist AS -- (PLNAME, HASH, KARA, KARAPATH, AUTHOR)
SELECT
Belongs.name AS PLNAME,
Belongs.hash AS HASH,
CASE WHEN nouveau = FALSE THEN ('/home/kara/' || category || '/' || Kara.name || ' - ' || type || ' - ' || title || '.mkv') -- Path for a non-nouveau kara
ELSE ('/home/kara/nouveaux/' || People.pseudo || '/' || category || '/' || Kara.name || ' - ' || type || ' - ' || title || '.mkv') -- Path for a nouveau kara
END KARAPATH,
KaraToPeople.pseudo AS AUTHOR -- The author if available
FROM Playlist JOIN Belongs ON Belongs.plname = Playlist.name -- Get the playlist
JOIN Kara ON Belongs.hash = Kara.hash LEFT OUTER JOIN KaraToPeople ON KaraToPeople.hash = Kara.hash; -- And its correspondig kara
-- View the content of a config
CREATE VIEW IF NOT EXISTS ConfigContent AS
SELECT
Config.name AS PLNAME, -- The name of the config i.e. the name of the dynamic playlist
Config.id AS ID, -- The id of the dynamic playlist
MpcHc.fromDatabase AS ISDB, -- Is this file from the database or directly from the filesystem
CASE
WHEN MpcHc.fromDatabase = FALSE THEN MpcHc.filename -- If the file is from the filesystem get the fullpath with the extension
ELSE (SELECT ('/home/kara/' || KARA || '.mkv') AS KARA FROM ViewKaraAll WHERE ViewKaraAll.HASH = MpcHc.hash) -- If it's from the db, get the filepath with the extension
END KARA, -- Always a valid path to directly pass it to mpv
MpcHc.play_index AS PLAYINDEX -- The index in the playlist of this kara
FROM Config LEFT OUTER JOIN MpcHc ON Config.id = MpcHc.config_id;
-- View the prev/next/current kara in a dynamic playlist
CREATE VIEW IF NOT EXISTS ViewDynamicHistoryIndex AS
WITH RECURSIVE maxmin(PREV_IDX, CFG) AS ( -- maxmin is the table of all the just before indexes for every config
SELECT MAX(MpcHc.play_index), Config.id -- the max of all the mins
FROM MpcHc JOIN Config ON MpcHc.config_id = Config.id -- join the config to all its indexes
GROUP BY Config.id HAVING MpcHc.play_index < Config.play_index AND Config.locked = FALSE AND MpcHc.play_index >= 0 -- get all the indexes after the current one
),
minmax(NEXT_IDX, CFG) AS ( -- minmax is the table of all the juste after indexes for every config
SELECT MIN(MpcHc.play_index), Config.id -- the min of all the maxs
FROM MpcHc JOIN Config ON MpcHc.config_id = Config.id -- join the config to all its indexes
GROUP BY Config.id HAVING MpcHc.play_index > Config.play_index AND Config.locked = FALSE AND MpcHc.play_index >= 0 -- get all the indexes after the current one
)
SELECT
Config.play_index AS IDX, -- the index in the history
IFNULL(maxmin.PREV_IDX, -1) P_IDX, -- get the previous id or a default invalid one if none TODO: put this stuff above, using joins
IFNULL(minmax.NEXT_IDX, -1) N_IDX, -- get the next id or a default invalid one if none
IFNULL(PContent.KARAPATH, '') PREV_FILE, -- get the previous file or a default invalid one if none
IFNULL(NContent.KARAPATH, '') NEXT_KARA, -- get the next file or a default invalid one if none
CASE WHEN Config.play_index IN (SELECT play_index FROM MpcHc) THEN TRUE ELSE FALSE END VALID -- the validity of the current index
FROM Config
LEFT OUTER JOIN maxmin ON Config.id = maxmin.CFG LEFT OUTER JOIN ConfigContent AS PContent ON maxmin.PREV_IDX = PContent.PLAYINDEX -- the previous kara
LEFT OUTER JOIN minmax ON Config.id = minmax.CFG LEFT OUTER JOIN ConfigContent AS NContent ON maxmin.PREV_IDX = NContent.PLAYINDEX -- the next kara
GROUP BY Config.id HAVING locked = FALSE;
--------------------------------------------------------------------------------------------------------------------------------------------------
---- SOME DEFAULT VALUES -------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO Playlist(name) VALUES ('vo'), ('va'), ('vf'), ('voca'), ('cdg'), ('autres'), ('amv'), ('all'); -- The automatic and protected playlists
INSERT INTO Config(name, locked, play_index, random) VALUES ("Default", FALSE, -1, FALSE); -- The default dynamic playlist, make it read in order and not locked
INSERT INTO HistoryPosition(id, length, index_hist) VALUES (0, 100, -1); -- The metadata about the history
INSERT INTO People(pseudo, promo) VALUES ('Kubat', 2021);
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
, language TEXT REFERENCES language
, file_path TEXT NOT NULL
, is_new INTEGER NOT NULL
, author_name TEXT
, author_year INTEGER CHECK(author_year > 0)
);
CREATE TABLE IF NOT EXISTS kara_type
( id INTEGER PRIMARY KEY AUTOINCREMENT
, name TEXT NOT NULL
);
INSERT INTO kara_type (name) VALUES
('vo'), ('va'), ('vf'), ('amv'), ('cdg'), ('autres'), ('vocaloid');
CREATE TABLE IF NOT EXISTS kara_category
( id INTEGER PRIMARY KEY AUTOINCREMENT
, name TEXT NOT NULL
);
INSERT INTO kara_category (name) VALUES
('ED/d*'), ('OP/d*'), ('AMV'), ('IS'), ('VOCA'), ('LIVE'), ('CDG'), ('PV'),
('MV');
CREATE TABLE IF NOT EXISTS language
( id INTEGER PRIMARY KEY AUTOINCREMENT
, name TEXT NOT NULL
);
INSERT INTO language (name) VALUES
('jp'), ('fr'), ('en'), ('ru'), ('sp'), ('it'), ('ch');
-- 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
, name TEXT NOT NULL UNIQUE
);
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)
);
-- 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
, kara_id INTEGER REFERENCES kara
);
-- Some notes:
-- - There's one and only row.
-- - `paused` 0 = play, 1 = paused
-- - `random` whether the queue is played randomly
-- - `repeat` whether the queue loops
-- - `single` whether only one kara loops
-- - `current` the position in the queue of the kara being played
-- - `elapsed` the number of seconds from the beginning of the current kara
CREATE TABLE IF NOT EXISTS queue_state
( id INTEGER PRIMARY KEY DEFAULT 42 CHECK(id = 42)
, volume INTEGER NOT NULL DEFAULT 100 CHECK(0 <= volume AND volume <= 100)
, paused INTEGER NOT NULL DEFAULT 1
, random INTEGER NOT NULL DEFAULT 0
, repeat INTEGER NOT NULL DEFAULT 0
, single INTEGER NOT NULL DEFAULT 0
, current INTEGER CHECK(current >= 0)
, elapsed INTEGER CHECK(elapsed >= 0)
);
INSERT INTO queue_state (id) VALUES (42);
-- 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);