M2Nt2JqT

1-- Clear out the database
2DROP TABLE IF EXISTS artist_master;
3DROP TABLE IF EXISTS artist_release;
4DROP TABLE IF EXISTS label_release;
5DROP TABLE IF EXISTS extraartist_release;
6DROP TABLE IF EXISTS labels;
7DROP TABLE IF EXISTS artists;
8DROP TABLE IF EXISTS releases;
9DROP TABLE IF EXISTS masters;
10
11-- Tables
12CREATE TABLE labels(
13    id INT,
14    name TEXT,
15    data_quality CHAR(20),
16    urls TEXT[],
17    parent_label TEXT,
18    PRIMARY KEY(id)
19);
20CREATE INDEX labels_name_idx ON labels (name);
21CREATE INDEX labels_parentlabel_idx ON labels (parent_label);
22
23CREATE TABLE artists(
24    id INT,
25    name TEXT,
26    data_quality CHAR(20),
27    realname TEXT,
28    profile TEXT,
29    namevariations TEXT[],
30    aliases TEXT[],
31    urls TEXT[],
32    PRIMARY KEY(id)
33);
34CREATE INDEX artists_name_idx ON artists (name);
35CREATE INDEX artist_namevariations_idx on "artists" USING GIN ("namevariations");
36CREATE INDEX artist_aliases_idx on "artists" USING GIN ("aliases");
37
38CREATE TABLE releases(
39    id INT,
40    name TEXT,
41    date CHAR(20),
42    country TEXT,
43    genres TEXT[],
44    styles TEXT[],
45    master_id INT,
46    has_master INT,
47    PRIMARY KEY(id)
48);
49CREATE INDEX release_genre_idx on "releases" USING GIN ("genres");
50CREATE INDEX release_styles_idx on "releases" USING GIN ("styles");
51CREATE INDEX release_master_id_idx on "releases" ("master_id");
52
53CREATE TABLE masters(
54    id INT,
55    name TEXT,
56    year INT,
57    main_release INT,
58    genres TEXT[],
59    styles TEXT[],
60    PRIMARY KEY(id)
61);
62
63CREATE INDEX masters_genre_idx on "masters" USING GIN ("genres");
64CREATE INDEX masters_styles_idx on "masters" USING GIN ("styles");
65CREATE INDEX masters_main_release_idx on "masters" ("main_release");
66
67CREATE TABLE artist_master(
68    artist_id INT,
69    master_id INT
70);
71CREATE INDEX am_artist_id_idx ON artist_master (artist_id);
72CREATE INDEX am_master_id_idx ON artist_master (master_id);
73
74CREATE TABLE artist_release(
75    artist_id INT,
76    release_id INT
77);
78CREATE INDEX ar_artist_id_idx ON artist_release (artist_id);
79CREATE INDEX ar_release_id_idx ON artist_release (release_id);
80
81CREATE TABLE extraartist_release(
82    artist_id INT,
83    release_id INT,
84    role TEXT
85);
86CREATE INDEX ear_artist_id_idx ON extraartist_release (artist_id);
87CREATE INDEX ear_release_id_idx ON extraartist_release (release_id);
88CREATE INDEX ear_role_idx ON extraartist_release (role);
89
90
91----
92
93CREATE TABLE wordsets (
94    key            text,
95    items        text[]
96);
97
98CREATE TABLE artist_primary_category (
99    artist_id integer NOT NULL,
100    category TEXT
101);