59 lines
1.5 KiB
SQL
59 lines
1.5 KiB
SQL
BEGIN TRANSACTION;
|
|
|
|
CREATE TABLE account (
|
|
account TEXT NOT NULL,
|
|
fan_id INTEGER NOT NULL,
|
|
currency TEXT,
|
|
PRIMARY KEY (account)
|
|
) WITHOUT ROWID;
|
|
|
|
CREATE TABLE item (
|
|
account TEXT NOT NULL,
|
|
item_id INTEGER NOT NULL,
|
|
item_type TEXT NOT NULL,
|
|
band_id INTEGER NOT NULL,
|
|
added INTEGER NOT NULL,
|
|
updated INTEGER NOT NULL,
|
|
purchased INTEGER NOT NULL,
|
|
featured_track INTEGER,
|
|
why TEXT,
|
|
also_collected_count INTEGER NOT NULL,
|
|
item_title TEXT NOT NULL,
|
|
item_url TEXT NOT NULL,
|
|
item_art_url TEXT NOT NULL,
|
|
band_name TEXT NOT NULL,
|
|
band_url TEXT NOT NULL,
|
|
featured_track_title TEXT,
|
|
featured_track_number INTEGER,
|
|
featured_track_duration NUMERIC,
|
|
album_id INTEGER,
|
|
album_title TEXT,
|
|
price INTEGER NOT NULL,
|
|
currency STRING NOT NULL,
|
|
label TEXT,
|
|
label_id INTEGER,
|
|
PRIMARY KEY (account, item_id)
|
|
) WITHOUT ROWID;
|
|
|
|
CREATE TABLE track (
|
|
account TEXT NOT NULL,
|
|
item_id INTEGER NOT NULL,
|
|
track_id INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
artist TEXT NOT NULL,
|
|
track_number INTEGER,
|
|
duration NUMERIC NOT NULL,
|
|
mp3 TEXT,
|
|
PRIMARY KEY (account, item_id, track_id),
|
|
FOREIGN KEY (account, item_id) REFERENCES item (account, item_id) ON DELETE CASCADE
|
|
) WITHOUT ROWID;
|
|
|
|
CREATE TABLE item_tag (
|
|
account TEXT NOT NULL,
|
|
item_id INTEGER NOT NULL,
|
|
tag TEXT NOT NULL,
|
|
PRIMARY KEY (account, item_id, tag),
|
|
FOREIGN KEY (account, item_id) REFERENCES item (account, item_id) ON DELETE CASCADE
|
|
) WITHOUT ROWID;
|
|
|
|
COMMIT;
|