Skip to content
Extraits de code Groupes Projets

Réécriture de init.sql

Fusionnées Réécriture de init.sql
schema vers master
1 open thread
Fusionnées
‮requested to merge
schema into master
1 open thread
Affichage du commit bf9d0758
Suivant
Afficher la dernière version
1 file
+ 94
− 254
Comparer les modifications
  • Côte à côte
  • En ligne
database/init.sql
+ 94
− 254
----------------------------------------------------------
PRAGMA case_sensitive_like = false;
---- SETTINGS --------------------------------------------
PRAGMA encoding = 'UTF-8';
----------------------------------------------------------
PRAGMA recursive_triggers = true;
PRAGMA foreign_keys = true;
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
-- Definition of a kara
----------------------------------------------------------
CREATE TABLE IF NOT EXISTS kara
---- TABLES ----------------------------------------------
( id INTEGER PRIMARY KEY AUTOINCREMENT
----------------------------------------------------------
, song_name TEXT NOT NULL
, source_name TEXT NOT NULL
-- a simple kara table
, category INTEGER NOT NULL REFERENCES kara_category
CREATE TABLE IF NOT EXISTS Kara (
, language TEXT REFERENCES language
name TEXT, -- name of the song
, file_path TEXT NOT NULL
title TEXT, -- name of the anime/game/amv/song...
, author_name TEXT
type TEXT, -- ED/d*|OP/d*|AMV|IS|VOCA|LIVE|PV|MV
, author_year INTEGER CHECK(author_year > 0)
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)
CREATE TABLE IF NOT EXISTS kara_category
);
( id INTEGER PRIMARY KEY AUTOINCREMENT
, name TEXT NOT NULL
-- a table to describe the authors of a kara
);
CREATE TABLE IF NOT EXISTS People (
pseudo TEXT UNIQUE PRIMARY KEY NOT NULL, -- the pseudo
INSERT INTO kara_category (name) VALUES
promo INTEGER, -- the promo, an integer at least positive
('ED/d*'), ('OP/d*'), ('AMV'), ('IS'), ('VOCA'), ('LIVE'), ('CDG'), ('PV'),
CHECK(promo >= 0) -- can do almost anything
('MV');
);
CREATE TABLE IF NOT EXISTS language
-- a table to link People to the Kara they
( id INTEGER PRIMARY KEY AUTOINCREMENT
-- have done. Usefull to get the right folder
, name TEXT NOT NULL
-- in the case of a "nouveau" kara.
);
CREATE TABLE IF NOT EXISTS KaraToPeople (
pseudo TEXT NOT NULL, -- the author of the kara
INSERT INTO language (name) VALUES
hash TEXT UNIQUE NOT NULL, -- only one author by kara
('jp'), ('fr'), ('en');
PRIMARY KEY (pseudo, hash), -- each relation is unique
FOREIGN KEY (pseudo) REFERENCES People(pseudo),
-- Playlists are sets of karas that are meant to be played together, kinda like
FOREIGN KEY (hash) REFERENCES Kara(hash)
-- M3U except it's stored in a SQL database.
);
CREATE TABLE IF NOT EXISTS playlist
-- associate an ID (artificial, can change later,
( id INTEGER PRIMARY KEY
-- won't change anything) to a hash (ie. to a kara)
, name TEXT NOT NULL UNIQUE
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
CREATE TABLE IF NOT EXISTS kara_playlist
);
( kara_id INTEGER REFERENCES kara ON DELETE CASCADE
, playlist_id INTEGER REFERENCES playlist ON DELETE CASCADE
-- a playlist is only a name (at the moment)
, PRIMARY KEY (kara_id, playlist_id)
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 ?
);
-- 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
-- link between a kara and a playlist
-- queue and start playback. Most clients offer an interface to edit the queue.
CREATE TABLE IF NOT EXISTS Belongs (
-- https://www.musicpd.org/doc/html/user.html#the-queue
name TEXT NOT NULL, -- the name of the playlist
hash TEXT NOT NULL, -- the hash of the kara
CREATE TABLE IF NOT EXISTS queue
PRIMARY KEY(name, hash),
( position INTEGER PRIMARY KEY
FOREIGN KEY(name) REFERENCES Playlist(name),
, kara_id INTEGER REFERENCES kara
FOREIGN KEY(hash) REFERENCES Kara(hash)
);
);
-- Some notes:
-- the history if we are in playlist mode
-- - There's one and only row.
CREATE TABLE IF NOT EXISTS History (
-- - `paused` 0 = play, 1 = paused
index_hist INTEGER PRIMARY KEY AUTOINCREMENT, -- the position in the history
-- - `random` whether the queue is played randomly
hash TEXT UNIQUE NOT NULL, -- the hash of the kara (unique in the history)
-- - `repeat` whether the queue loops
FOREIGN KEY(hash) REFERENCES Kara(hash)
-- - `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
-- A table to store the position in the history
CREATE TABLE IF NOT EXISTS queue_state
CREATE TABLE IF NOT EXISTS HistoryPosition (
( id INTEGER PRIMARY KEY DEFAULT 42 CHECK(id = 42)
id INTEGER PRIMARY KEY UNIQUE DEFAULT 0, -- to ensure there is only one row in this table
, volume INTEGER NOT NULL DEFAULT 100 CHECK(0 <= volume AND volume <= 100)
length INTEGER NOT NULL DEFAULT 100, -- the length of the history
, paused INTEGER NOT NULL DEFAULT 1
index_hist INTEGER, -- always point a kara in the history. If the history is
, random INTEGER NOT NULL DEFAULT 0
-- empty reading that row has an undefined behaviour
, repeat INTEGER NOT NULL DEFAULT 0
CHECK(id = 0) -- only one row
, single INTEGER NOT NULL DEFAULT 0
);
, current INTEGER CHECK(current >= 0)
, elapsed INTEGER CHECK(elapsed >= 0)
-- the playlist of the mpchc mode
);
-- played files from this type of playlist will be
-- putted in the history, but when selecting files
INSERT INTO queue_state (id) VALUES (42);
-- from this kind of playlist the history won't be
-- taken into account.
CREATE TABLE IF NOT EXISTS MpcHc (
-- Some useful values:
config_id INTEGER, -- the number of the config
-- last_update is the timestamp of the last time the table of kara has been
play_index INTEGER, -- the position in the table
-- updated. This is so lektor doesn't have to read all kara in the filesystem,
hash TEXT, -- the hash of the kara if `fromDatabase` is `true`
-- just the new ones (where their modified timestamp is greater than
filename TEXT, -- the path to the file if `fromDatabase` is `false`
-- last_update). If last_update is NULL then the database is empty.
fromDatabase BOOLEAN NOT NULL -- indicate whever the kara is from the database or the filesystem
CREATE TABLE IF NOT EXISTS misc
);
( id INTEGER PRIMARY KEY DEFAULT 42 CHECK(id = 42)
, last_update INTEGER
-- the config/statte of the application for
);
-- the MpcHc playlists
CREATE TABLE IF NOT EXISTS Config (
INSERT INTO misc (id) VALUES (42);
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);