76 lines
1.5 KiB
SQL
76 lines
1.5 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
|
|
create table song(
|
|
id int primary key,
|
|
name text not null,
|
|
name_jp text not null,
|
|
location text not null,
|
|
unique(name,name_jp)
|
|
);
|
|
|
|
create table album(
|
|
id int primary key,
|
|
name text not null,
|
|
date date not null,
|
|
description text not null,
|
|
updates text not null,
|
|
code char(3) not null
|
|
);
|
|
|
|
create table artist(
|
|
id int primary key,
|
|
name text not null,
|
|
name_rm text not null
|
|
);
|
|
|
|
create unique index ix_artist_name on artist(
|
|
coalesce(nullif(name_rm,''),name)
|
|
);
|
|
|
|
create table credit(
|
|
id int primary key,
|
|
name text not null
|
|
);
|
|
|
|
create table motif(
|
|
id int primary key,
|
|
name text not null,
|
|
category int not null
|
|
);
|
|
|
|
create table category(
|
|
id int primary key,
|
|
name text not null
|
|
);
|
|
|
|
create table clip(
|
|
song_id int not null references song(id),
|
|
motif_id int not null references motif(id),
|
|
start_ms int not null,
|
|
duration_ms int not null,
|
|
feature int not null,
|
|
primary key(song_id,motif_id)
|
|
);
|
|
|
|
create index ix_motif on clip(
|
|
motif_id
|
|
);
|
|
|
|
create table song_album(
|
|
song_id int not null references song(id),
|
|
album_id int not null references album(id),
|
|
track int not null,
|
|
primary key(album_id,track),
|
|
unique(song_id,album_id)
|
|
);
|
|
|
|
create table song_artist(
|
|
song_id int not null references song(id),
|
|
artist_id int not null references artist(id),
|
|
credit_id int not null references credit(id),
|
|
primary key(song_id,artist_id,credit_id)
|
|
);
|
|
|
|
create index ix_song_artist on song_artist(
|
|
artist_id
|
|
); |