Skip to content
Extraits de code Groupes Projets
Sélectionner une révision Git
  • 8c7eb8f404b504483c0e548514aa3bf4f0b49175
  • master par défaut
  • 1-baka-export
  • meson
  • assdraw
  • old-master
  • v3.2.2
  • v3.2.1
  • v3.2.0
  • v3.1.3
  • v3.1.2
  • v3.1.1
  • v3.1.0
  • v3.0.4
  • v3.0.3
  • v3.0.2
  • v3.0.1
  • v3.0.0
  • v2.1.3
  • v2.1.4
  • v2.1.5
  • v2.1.6
  • v2.1.0
  • v2.1.1
  • v2.1.2
  • v2.1.7
26 résultats

text_file_reader.cpp

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