-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
154 lines (119 loc) · 4.32 KB
/
schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
drop table if exists Bands;
create table Bands (
band_id integer primary key not null,
added_date text, /* date added to MA */
modified_date text, /* date modified on MA */
insert_date text, /* date entry in DB inserted/updated */
band text not null,
band_url text not null,
country text,
status text,
genre text,
genre_tokens text,
themes text,
themes_tokens text,
comment text
);
drop table if exists Albums;
create table Albums (
album_id integer not null,
band_id integer not null, /* id into Bands table */
added_date text, /* date added to MA */
modified_date text, /* date modified on MA */
insert_date text, /* date entry in DB inserted/updated */
album text not null,
album_url text not null,
type text,
release_date text,
primary key (band_id, album_id),
foreign key(band_id) references Bands(band_id)
);
drop table if exists Artists;
create table Artists (
artist_id integer primary key not null,
added_date text, /* date added to MA */
modified_date text, /* date modified on MA */
insert_date text, /* date entry in DB inserted/updated */
artist text not null,
artist_url text not null,
origin text,
comment text
);
drop table if exists Labels;
create table Labels (
label_id integer primary key not null,
added_date text, /* date added to MA */
modified_date text, /* date modified on MA */
insert_date text, /* date entry in DB inserted/updated */
label text not null,
label_url not null
);
drop table if exists BandLabel;
create table BandLabel (
band_id integer not null,
label_id integer not null,
insert_date text, /* date entry in DB inserted/updated */
primary key (band_id, label_id),
foreign key(band_id) references Bands(band_id),
foreign key(label_id) references Labels(label_id)
);
drop table if exists Reviews;
create table Reviews (
band_id integer not null, /* id into Bands table */
album_id integer not null, /* id into Albums table */
user_id integer not null, /* id into Users table */
modified_date text, /* date modified on MA */
insert_date text, /* date entry in DB inserted/updated */
review_title text,
review_url text,
review_percentage integer,
review text, /* the full review text */
primary key (band_id,album_id,user_id),
foreign key(band_id) references Bands(band_id),
foreign key(album_id) references Albums(album_id),
foreign key(user_id) references Users(user_id)
);
drop table if exists Users;
create table Users (
user_id integer primary key not null, /* MA shows user ID in review URL; can also get it from user's page */
insert_date text, /* date entry in DB inserted/updated */
user text, /* MA username; expected to be unique */
user_url text
);
drop table if exists Similarities;
create table Similarities (
band_id integer not null, /* id into Bands table */
similar_to_id integer not null, /* id into Bands table */
insert_date text, /* date entry in DB inserted/updated */
score integer not null, /* similarity score = users' votes */
primary key (band_id, similar_to_id),
foreign key(band_id) references Bands(band_id),
foreign key(similar_to_id) references Bands(band_id)
);
drop table if exists BandLineup;
create table BandLineup (
band_id integer not null, /* id into Bands table */
artist_id integer not null, /* id into Artists table */
insert_date text, /* date entry in DB inserted/updated */
current_member integer,
current_live_member integer,
past_member integer,
past_live_member integer,
last_known_member integer,
last_known_live_member integer,
primary key (band_id, artist_id),
foreign key(band_id) references Bands(band_id),
foreign key(artist_id) references Artists(artist_id)
);
drop table if exists AlbumLineup;
create table AlbumLineup (
id integer primary key autoincrement,
band_id integer not null, /* id into Bands table */
artist_id integer not null, /* id into Artists table */
insert_date text, /* date entry in DB inserted/updated */
band_member integer,
guest_session integer,
misc_staff integer,
foreign key(band_id) references Bands(band_id),
foreign key(artist_id) references Artists(artist_id)
);