Sélectionner une révision Git
-
Amar Takhar a rédigé
Rename FFmpegsource2/ to libffms/ before the update to r115 from the ffms repo. This doens't make any build changes so things will be broken in this commit. Originally committed to SVN as r3114.
Amar Takhar a rédigéRename FFmpegsource2/ to libffms/ before the update to r115 from the ffms repo. This doens't make any build changes so things will be broken in this commit. Originally committed to SVN as r3114.
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);