112 lines
4.5 KiB
Python
112 lines
4.5 KiB
Python
table_quers = ["""CREATE TABLE 'libraries' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'name' TEXT,
|
|
'path' TEXT UNIQUE);""",
|
|
|
|
"""CREATE TABLE 'dirs' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'library' INTEGER,
|
|
'parent' INTEGER,
|
|
'name' TEXT,
|
|
UNIQUE(parent, name)
|
|
)""",
|
|
|
|
"""CREATE TABLE 'genres' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'name' TEXT UNIQUE)""",
|
|
|
|
"""CREATE TABLE 'artists' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'libraryid' INTEGER,
|
|
'dir' INTEGER UNIQUE,
|
|
'name' TEXT)""",
|
|
|
|
"""CREATE TABLE 'albums' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'artistid' INTEGER,
|
|
'coverid' INTEGER,
|
|
'dir' INTEGER,
|
|
'name' TEXT,
|
|
'added' INTEGER NOT NULL DEFAULT -1,
|
|
'played' INTEGER,
|
|
'plays' INTEGER NOT NULL DEFAULT 0,
|
|
UNIQUE (artistid, dir));""",
|
|
|
|
"""CREATE TABLE 'songs' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'library' INTEGER,
|
|
'albumid' BOOLEAN,
|
|
'genre' INTEGER DEFAULT NULL,
|
|
'file' TEXT UNIQUE, -- path from the library root
|
|
'size' INTEGER NOT NULL DEFAULT -1,
|
|
'title' TEXT NOT NULL,
|
|
'lastscan' INTEGER NOT NULL DEFAULT -1,
|
|
'format' TEXT,
|
|
'length' INTEGER,
|
|
'bitrate' INTEGER,
|
|
'track' INTEGER,
|
|
'year' INTEGER
|
|
)""",
|
|
|
|
"""CREATE TABLE 'covers' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'library' INTEGER,
|
|
'type' TEXT,
|
|
'size' TEXT,
|
|
'path' TEXT UNIQUE);""",
|
|
|
|
"""CREATE TABLE 'users' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
'username' TEXT UNIQUE NOT NULL,
|
|
'password' TEXT NOT NULL,
|
|
'admin' BOOLEAN DEFAULT 0,
|
|
'email' TEXT)""",
|
|
|
|
"""CREATE TABLE 'stars' (
|
|
'userid' INTEGER,
|
|
'songid' INTEGER,
|
|
primary key ('userid', 'songid'))""",
|
|
|
|
"""CREATE TABLE 'playlists' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
'ownerid' INTEGER,
|
|
'name' TEXT,
|
|
'public' BOOLEAN,
|
|
'created' INTEGER,
|
|
'changed' INTEGER,
|
|
'cover' INTEGER,
|
|
UNIQUE ('ownerid', 'name'))""",
|
|
|
|
"""CREATE TABLE 'playlist_entries' (
|
|
'playlistid' INTEGER,
|
|
'songid' INTEGER,
|
|
'order' FLOAT)""",
|
|
|
|
"""CREATE TABLE 'podcasts' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'lastscan' INTEGER NOT NULL DEFAULT 0,
|
|
'interval' INTEGER NOT NULL DEFAULT 60,
|
|
'url' TEXT UNIQUE,
|
|
'title' TEXT NOT NULL,
|
|
'description' TEXT,
|
|
'cover' INTEGER,
|
|
'rss_cover' TEXT,
|
|
'status' TEXT)""",
|
|
|
|
"""CREATE TABLE 'podcast_episodes' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'podcastid' INTEGER,
|
|
'date' INTEGER,
|
|
'title' TEXT NOT NULL,
|
|
'description' TEXT,
|
|
'url' TEXT,
|
|
'format' TEXT,
|
|
'status' TEXT,
|
|
UNIQUE('podcastid', 'title'))""",
|
|
|
|
"""CREATE TABLE 'meta' (
|
|
'key' TEXT PRIMARY KEY NOT NULL,
|
|
'value' TEXT);""",
|
|
|
|
"""INSERT INTO meta VALUES ('db_version', '1');"""]
|