Sélectionner une révision Git
matrixcommandhandler.ts
Bifurcation depuis
ARISE / matrix-appservice-discord
Le projet source a une visibilité limitée.
disk.sql 5,58 Kio
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
, available INTEGER CHECK(available = 0 OR available = 1) DEFAULT 1 NOT NULL
, string TEXT GENERATED ALWAYS AS
( category || ' - ' || language || ' / ' || source_name || ' - ' || song_type ||
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 OR REPLACE INTO kara_type (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 kara_category
( id INTEGER PRIMARY KEY
, name TEXT NOT NULL
);
INSERT OR REPLACE INTO kara_category (id, name) VALUES
(1, 'vo'), (2, 'va'), (3, 'amv'), (4, 'cdg'), (5, 'autres'), ( 6, 'vocaloid');
CREATE TABLE IF NOT EXISTS language
( id INTEGER PRIMARY KEY
, name TEXT NOT NULL
);
INSERT OR REPLACE 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;
-- 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 AUTOINCREMENT CHECK(position > 0)
, kara_id INTEGER REFERENCES kara
, priority INTEGER NOT NULL DEFAULT 1 CHECK(priority > 0 AND priority < 6)
);
-- Temporary queue table used when reorderingg the queue (for inserts)
CREATE TEMPORARY TABLE queue_tmp IF NOT EXISTS
( position INTEGER PRIMARY KEY AUTOINCREMENT CHECK(position > 0)
, kara_id INTEGER
, priority INTEGER NOT NULL DEFAULT 1 CHECK(priority > 0 AND priority < 6)
);
-- The user table
-- Used for the [password {passwd}] MPD command. The documentation can be found
-- here: https://www.musicpd.org/doc/html/protocol.html#connection-settings.
-- The password command is used to restrict access to specific commands.
CREATE TABLE IF NOT EXISTS users
( username TEXT NOT NULL UNIQUE
, password TEXT NOT NULL UNIQUE
, PRIMARY KEY (username, password)
) WITHOUT ROWID;
INSERT OR REPLACE INTO users (username, password) VALUES ('sakura', 'hashire');
-- The stickers table
-- Used to implement the stickers MPD functionnality, documentation can be found
-- here: https://www.musicpd.org/doc/html/protocol.html#stickers. Need to be
-- authentified to use stickers commands. Support tags for `song` and `plt`.
-- URIs in stickers are kara/playlist IDs.
CREATE TABLE IF NOT EXISTS 'stickers'
( id INTEGER PRIMARY KEY
, name TEXT NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS 'stickers.kara'
( id INTEGER REFERENCES kara ON DELETE CASCADE
, sticker INTEGER REFERENCES stickers ON DELETE CASCADE
, value INTEGER NOT NULL
, PRIMARY KEY (id, sticker)
) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'stickers.plt'
( id INTEGER REFERENCES playlist ON DELETE CASCADE
, sticker INTEGER REFERENCES stickers ON DELETE CASCADE
, value INTEGER NOT NULL
, PRIMARY KEY (id, sticker)
) WITHOUT ROWID;
-- Update jobs history
-- Here are stored karas ids present during a certain update job. May be
-- usefull to keep tracks of kara. Now used to handle the 'deleted' case, i.e.
-- when a kara was here, but was deleted on kurisu.
CREATE TABLE IF NOT EXISTS updates
( job INTEGER NOT NULL CHECK(job >= 0)
, kara_id INTEGER NOT NULL CHECK(kara_id > 0)
, PRIMARY KEY (job, kara_id)
) WITHOUT ROWID;
-- 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.
-- the last_end_update is here to mark a synchronisation point. Multiple
-- update can be done (updating last_update), but a general update can be
-- in progress.
CREATE TABLE IF NOT EXISTS misc
( id INTEGER PRIMARY KEY DEFAULT 42 CHECK(id = 42)
, last_update INTEGER
, last_end_update INTEGER
, update_job INTEGER NOT NULL CHECK(update_job >= 0)
, opened INTEGER NOT NULL CHECK(opened = 0 OR opened = 1)
);
INSERT OR REPLACE INTO misc (id, update_job, opened) VALUES (42, 0, 0);