mirror of
https://github.com/simon987/music-graph-scripts.git
synced 2025-04-04 06:52:58 +00:00
484 lines
16 KiB
PL/PgSQL
484 lines
16 KiB
PL/PgSQL
CREATE OR REPLACE FUNCTION fn_sortname(name text, mb_sortname text) RETURNS text AS
|
|
$$
|
|
declare
|
|
sn text;
|
|
BEGIN
|
|
|
|
sn = regexp_replace(name, '[^a-zA-Z0-9.\-!?&çéàâäëïöü'' ]', '_', 'g');
|
|
|
|
if length(replace(sn, '_', '')) = 0 then
|
|
return upper(regexp_replace(mb_sortname, '[^\w.\-!?& ]', '_', 'g'));
|
|
end if;
|
|
|
|
return upper(sn);
|
|
END
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE OR REPLACE FUNCTION fn_sortname(name text) RETURNS text AS
|
|
$$
|
|
BEGIN
|
|
return upper(regexp_replace(name, '[^a-zA-Z0-9.\-!?&çéàâäëïöü'' ]', '_'));
|
|
END
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TABLE mg.translate_artist_artist_rel
|
|
(
|
|
mb_name TEXT PRIMARY KEY,
|
|
mg_name TEXT
|
|
);
|
|
INSERT INTO mg.translate_artist_artist_rel
|
|
VALUES ('teacher', 'TEACHER_OF'),
|
|
('composer-in-residence', 'HAS_COMPOSER-IN-RESIDENCE_STATUS_IN'),
|
|
('member of band', 'IS_MEMBER_OF'),
|
|
('voice actor', 'IS_VOICE_ACTOR_OF'),
|
|
('tribute', 'IS_TRIBUTE_TO'),
|
|
('supporting musician', 'IS_SUPPORTING_MUSICIAN_OF'),
|
|
('instrumental supporting musician', 'IS_INSTRUMENTAL_SUPPORTING_MUSICIAN_OF'),
|
|
('personal relationship', 'HAS_PERSONAL_RELATIONSHIP_WITH'),
|
|
('musical relationships', 'HAS_MUSICAL_RELATIONSHIP_WITH'),
|
|
('collaboration', 'HAS_COLLABORATED_WITH'),
|
|
('married', 'IS_MARRIED_WITH'),
|
|
('sibling', 'IS_SIBLING_OF'),
|
|
('parent', 'IS_PARENT_OF'),
|
|
('is person', 'IS'),
|
|
('conductor position', 'IS_CONDUCTOR_OF'),
|
|
('vocal supporting musician', 'DOES_VOCAL_SUPPORT_FOR'),
|
|
('artistic director', 'IS_ARTIST_DIRECTOR_OF'),
|
|
('subgroup', 'IS_SUBGROUP_OF'),
|
|
('founder', 'IS_FOUNDER_OF'),
|
|
('involved with', 'IS_INVOLVED_WITH'),
|
|
('named after', 'IS_NAMED_AFTER');
|
|
|
|
CREATE TABLE mg.translate_artist_release_rel
|
|
(
|
|
mb_name TEXT PRIMARY KEY,
|
|
mg_name text
|
|
);
|
|
INSERT INTO mg.translate_artist_release_rel
|
|
VALUES ('translator', 'TRANSLATED'),
|
|
('liner notes', 'WROTE_LINER_NOTES'),
|
|
('lyricist', 'IS_LYRICIST_FOR'),
|
|
('lacquer cut', 'DID_LACQUER_CUT_FOR'),
|
|
('samples from artist', 'HAS_SAMPLES_IN'),
|
|
('remixes and compilations', NULL),
|
|
('composition', 'COMPOSED'),
|
|
('booking', 'DID_BOOKING_FOR'),
|
|
('balance', 'DID_BALANCE_FOR'),
|
|
('misc', 'HAS_MISC_ROLE_IN'),
|
|
('conductor', 'CONDUCTED'),
|
|
('legal representation', 'PROVIDED_LEGAL_REPRESENTATION_FOR'),
|
|
('design/illustration', 'DID_DESIGN_FOR'),
|
|
('performing orchestra', 'PERFORMED_FOR'),
|
|
('producer', 'PRODUCED'),
|
|
('instrument', 'PERFORMED_INSTRUMENT_FOR'),
|
|
('writer', 'WROTE_LYRICS_FOR'),
|
|
('production', 'DID_PRODUCTION_FOR'),
|
|
('performance', 'PERFORMED_FOR'),
|
|
('composer', 'IS_COMPOSER_FOR'),
|
|
('sound', 'DID_SOUND_FOR'),
|
|
('remixer', 'DID_REMIXING_FOR'),
|
|
('orchestrator', 'IS_ORCHESTRATOR_FOR'),
|
|
('compiler', 'DID_COMPILATION_FOR'),
|
|
('vocal arranger', 'IS_ARRANGER_FOR'),
|
|
('arranger', 'IS_ARRENGER_FOR'),
|
|
('mix-DJ', 'MIXED'),
|
|
('editor', 'IS_EDITOR_FOR'),
|
|
('illustration', 'DID_ILLUSTRATION_FOR'),
|
|
('audio', 'DID_AUDIO_FOR'),
|
|
('publishing', 'IS_PUBLISHER_FOR'),
|
|
('art direction', 'DID_ART_DIRECTION_FOR'),
|
|
('design', 'DID_DESIGN_FOR'),
|
|
('instrument arranger', 'IS_ARRANGER_FOR'),
|
|
('chorus master', 'IS_CHORUS_MASTER_FOR'),
|
|
('photography', 'DID_PHOTOGRAPHY_FOR'),
|
|
('performer', 'PERFORMED_IN'),
|
|
('graphic design', 'DID_GRAPHIC_DESIGN_FOR'),
|
|
('booklet editor', 'IS_BOOKLET_EDITOR_FOR'),
|
|
('programming', 'DID_PROGRAMING_FOR'),
|
|
('copyright', 'IS_COPYRIGHT_HOLDER_OF'),
|
|
('piano technician', 'IS_PIANO_TECNICIAN_FOR'),
|
|
('phonographic copyright', 'IS_PHONOGRAPHIC_COPYRIGHT_HOLDER_OF'),
|
|
('mastering', 'DID_MASTERING_FOR'),
|
|
('vocal', 'PERFORED_VOCALS_FOR'),
|
|
('librettist', 'IS_LIBRETTIST_FOR'),
|
|
('mix', 'MIXED'),
|
|
('recording', 'DID_RECORDING_FOR'),
|
|
('concertmaster', 'IS_CONCERTMASTER_FOR'),
|
|
('engineer', 'IS_ENGINEER_FOR'),
|
|
('tribute', 'IS_TRIBUTE_TO'),
|
|
('dedicated to', 'IS_DEDICATED_TO'),
|
|
('creative direction', NULL),
|
|
('artists and repertoire', NULL);
|
|
|
|
|
|
CREATE TABLE mg.translate_label_label_rel
|
|
(
|
|
mb_name TEXT PRIMARY KEY,
|
|
mg_name text
|
|
);
|
|
INSERT INTO mg.translate_label_label_rel
|
|
VALUES ('label rename', 'WAS_RENAMED_TO'),
|
|
('imprint', 'DOES_IMPRINT_FOR'),
|
|
('label distribution', 'DOES_DISTRIBUTION_FOR'),
|
|
('business association', 'HAS_BUSINESS_ASSOCIATION_TO'),
|
|
('label ownership', 'OWNS'),
|
|
('label reissue', 'DOES_REISSUING_FOR');
|
|
|
|
|
|
CREATE OR REPLACE VIEW mg.artist AS
|
|
SELECT gid as "id:ID(Artist)",
|
|
artist.name,
|
|
fn_sortname(artist.name, sort_name) as sortname,
|
|
COALESCE(begin_date_year, 0) as "year:int",
|
|
comment,
|
|
COALESCE(lfm.listeners, 0) as "listeners:int",
|
|
COALESCE(lfm.playcount, 0) as "playcount:int",
|
|
(CASE WHEN type = 2 THEN 'Artist;Group' ELSE 'Artist' END) as ":LABEL"
|
|
FROM artist
|
|
LEFT JOIN mg.lastfm_artist lfa ON lfa.mbid = artist.gid
|
|
LEFT JOIN mg.lastfm_artist_meta lfm ON lfa.name = lfm.name;
|
|
|
|
CREATE OR REPLACE VIEW mg.artist_artist AS
|
|
SELECT a0.gid as ":START_ID(Artist)",
|
|
a1.gid as ":END_ID(Artist)",
|
|
0 as "weight:float",
|
|
t.mg_name as ":TYPE"
|
|
FROM l_artist_artist
|
|
INNER JOIN artist a0 ON entity0 = a0.id
|
|
INNER JOIN artist a1 ON entity1 = a1.id
|
|
INNER JOIN link l on l.id = l_artist_artist.link
|
|
INNER JOIN link_type lt ON lt.id = l.link_type
|
|
INNER JOIN mg.translate_artist_artist_rel t ON t.mb_name = lt.name
|
|
UNION ALL
|
|
SELECT lfa0.mbid,
|
|
lfa1.mbid,
|
|
weight,
|
|
'IS_RELATED_TO'
|
|
FROM mg.lastfm_artist_artist
|
|
INNER JOIN mg.lastfm_artist lfa0 ON lfa0.name = mg.lastfm_artist_artist.name0
|
|
INNER JOIN mg.lastfm_artist lfa1 ON lfa1.name = mg.lastfm_artist_artist.name1
|
|
WHERE lfa0.mbid IN (SELECT gid FROM artist)
|
|
AND lfa1.mbid IN (SELECT gid FROM artist)
|
|
UNION ALL
|
|
SELECT s0.mbid,
|
|
s1.mbid,
|
|
index::float / 20,
|
|
'IS_RELATED_TO'
|
|
FROM mg.spotify_artist_artist
|
|
INNER JOIN mg.spotify_artist s0 ON s0.spotid = mg.spotify_artist_artist.spotid0
|
|
INNER JOIN mg.spotify_artist s1 ON s1.spotid = mg.spotify_artist_artist.spotid1
|
|
WHERE s0.mbid IN (SELECT gid FROM artist)
|
|
AND s1.mbid IN (SELECT gid FROM artist);
|
|
|
|
|
|
|
|
CREATE OR REPLACE VIEW mg.release AS
|
|
SELECT release_group.gid as "id:ID(Release)",
|
|
release_group.name,
|
|
coalesce(m.first_release_date_year, 0) as "year:int",
|
|
CONCAT('Release;', t.name) as ":LABEL"
|
|
FROM release_group
|
|
INNER JOIN release_group_meta m ON m.id = release_group.id
|
|
LEFT JOIN release_group_primary_type t ON t.id = release_group.type;
|
|
|
|
CREATE OR REPLACE VIEW mg.artist_release AS
|
|
SELECT a.gid as ":START_ID(Artist)",
|
|
rg.gid as ":END_ID(Release)",
|
|
t.mg_name as ":TYPE"
|
|
FROM l_artist_release_group
|
|
INNER JOIN artist a on a.id = l_artist_release_group.entity0
|
|
INNER JOIN release_group rg on rg.id = l_artist_release_group.entity1
|
|
INNER JOIN release r on r.release_group = l_artist_release_group.id
|
|
INNER JOIN link l on l.id = l_artist_release_group.link
|
|
INNER JOIN link_type lt ON lt.id = l.link_type
|
|
INNER JOIN mg.translate_artist_release_rel t ON t.mb_name = lt.name
|
|
WHERE t.mg_name IS NOT NULL
|
|
AND r.status = 1
|
|
UNION ALL
|
|
SELECT a.gid as ":START_ID(Artist)",
|
|
rg.gid as ":END_ID(Release)",
|
|
t.mg_name as ":TYPE"
|
|
FROM l_artist_release
|
|
INNER JOIN artist a on a.id = l_artist_release.entity0
|
|
INNER JOIN release r on r.id = l_artist_release.entity1
|
|
INNER JOIN release_group rg on rg.id = r.release_group
|
|
INNER JOIN link l on l.id = l_artist_release.link
|
|
INNER JOIN link_type lt ON lt.id = l.link_type
|
|
INNER JOIN mg.translate_artist_release_rel t ON t.mb_name = lt.name
|
|
WHERE t.mg_name IS NOT NULL
|
|
AND r.status = 1
|
|
UNION ALL
|
|
SELECT a.gid as ":START_ID(Artist)",
|
|
rg.gid as ":END_ID(Release)",
|
|
'CREDITED_FOR' as ":TYPE"
|
|
FROM release
|
|
INNER JOIN artist_credit_name cn ON cn.artist_credit = release.artist_credit
|
|
INNER JOIN artist a on a.id = cn.artist
|
|
INNER JOIN release_group rg on rg.id = release.release_group
|
|
WHERE status = 1;
|
|
|
|
CREATE OR REPLACE VIEW mg.track AS
|
|
SELECT row_number() over (ORDER BY sa.mbid) as "id:ID(Track)",
|
|
mg.spotify_artist_track.album as "album",
|
|
mg.spotify_artist_track.track as "track",
|
|
mg.spotify_artist_track.url as "url"
|
|
FROM mg.spotify_artist_track
|
|
INNER JOIN mg.spotify_artist sa ON spotify_artist_track.spotid = sa.spotid
|
|
INNER JOIN mg.artist ON "id:ID(Artist)" = sa.mbid
|
|
WHERE url != 'None';
|
|
|
|
|
|
CREATE OR REPLACE VIEW mg.artist_track AS
|
|
SELECT sa.mbid as ":START_ID(Artist)",
|
|
row_number() over (ORDER BY sa.mbid) as ":END_ID(Track)"
|
|
FROM mg.spotify_artist_track
|
|
INNER JOIN mg.spotify_artist sa ON spotify_artist_track.spotid = sa.spotid
|
|
INNER JOIN mg.artist ON "id:ID(Artist)" = sa.mbid
|
|
WHERE url != 'None';
|
|
|
|
|
|
DROP VIEW mg.tag CASCADE ;
|
|
CREATE OR REPLACE VIEW mg.tag AS
|
|
WITH occurences AS (
|
|
SELECT name,
|
|
COUNT(*) as count
|
|
FROM (
|
|
SELECT lower(name) as name
|
|
FROM release_group_tag
|
|
INNER JOIN tag t ON t.id = tag
|
|
UNION ALL
|
|
SELECT lower(name)
|
|
FROM release_tag
|
|
INNER JOIN tag t ON t.id = tag
|
|
UNION ALL
|
|
SELECT lower(name)
|
|
FROM mg.lastfm_artist_tag
|
|
UNION ALL
|
|
SELECT tag
|
|
FROM mg.spotify_artist_tag
|
|
) as tags
|
|
GROUP BY name
|
|
)
|
|
SELECT row_number() over (ORDER BY name) as "id:ID(Tag)",
|
|
name,
|
|
count as "occurences:int"
|
|
FROM occurences
|
|
WHERE count > 5;
|
|
|
|
|
|
CREATE OR REPLACE VIEW mg.release_tag AS
|
|
SELECT rg.gid as ":START_ID(Release)",
|
|
t."id:ID(Tag)" as ":END_ID(Tag)",
|
|
greatest(least(release_group_tag.count::float / 6, 1), 0.2) as "weight:float"
|
|
FROM release_group_tag
|
|
INNER JOIN release_group rg ON rg.id = release_group_tag.release_group
|
|
INNER JOIN tag mbt ON mbt.id = release_group_tag.tag
|
|
INNER JOIN mg.tag t ON t.name = mbt.name
|
|
WHERE release_group_tag.count > 0
|
|
UNION ALL
|
|
SELECT rg.gid as ":START_ID(Release)",
|
|
t."id:ID(Tag)" as ":END_ID(Tag)",
|
|
greatest(least(release_tag.count::float / 6, 1), 0.2) as "weight:float"
|
|
FROM release_tag
|
|
INNER JOIN release r ON r.id = release_tag.release
|
|
INNER JOIN release_group rg ON rg.id = r.release_group
|
|
INNER JOIN tag mbt ON mbt.id = release_tag.tag
|
|
INNER JOIN mg.tag t ON t.name = mbt.name
|
|
WHERE release_tag.count > 0
|
|
AND r.status = 1;
|
|
|
|
CREATE OR REPLACE VIEW mg.artist_tag AS
|
|
SELECT a.gid as ":START_ID(Artist)",
|
|
t."id:ID(Tag)" as ":END_ID(Tag)",
|
|
greatest(least(artist_tag.count::float / 8, 1), 0.2) as "weight:float"
|
|
FROM artist_tag
|
|
INNER JOIN artist a on artist_tag.artist = a.id
|
|
INNER JOIN tag mbt ON mbt.id = artist_tag.tag
|
|
INNER JOIN mg.tag t ON t.name = mbt.name
|
|
UNION ALL
|
|
SELECT DISTINCT a.gid as ":START_ID(Artist)",
|
|
t."id:ID(Tag)" as ":END_ID(Tag)",
|
|
0.5 as "weight:float"
|
|
FROM mg.lastfm_artist_tag lfmt
|
|
INNER JOIN mg.lastfm_artist lfma on lfmt.name = lfma.name
|
|
INNER JOIN artist a on a.gid = lfma.mbid
|
|
INNER JOIN mg.tag t ON t.name = lower(lfmt.tag)
|
|
UNION ALL
|
|
SELECT DISTINCT a.gid as ":START_ID(Artist)",
|
|
t."id:ID(Tag)" as ":END_ID(Tag)",
|
|
0.5 as "weight:float"
|
|
FROM mg.spotify_artist_tag spott
|
|
INNER JOIN mg.spotify_artist spota on spota.spotid = spott.spotid
|
|
INNER JOIN artist a on a.gid = spota.mbid
|
|
INNER JOIN mg.tag t ON t.name = lower(spott.tag);
|
|
|
|
CREATE OR REPLACE VIEW mg.tag_tag AS
|
|
SELECT t1."id:ID(Tag)" as ":START_ID(Tag)",
|
|
t2."id:ID(Tag)" as ":END_ID(Tag)",
|
|
greatest(least(tag_relation.weight::float / 12, 1), 0.2) as "weight:float"
|
|
FROM tag_relation
|
|
INNER JOIN tag mbt1 ON mbt1.id = tag_relation.tag1
|
|
INNER JOIN tag mbt2 ON mbt2.id = tag_relation.tag2
|
|
INNER JOIN mg.tag t1 ON mbt1.name = t1.name
|
|
INNER JOIN mg.tag t2 ON mbt1.name = t2.name;
|
|
|
|
|
|
CREATE OR REPLACE VIEW mg.label AS
|
|
SELECT label.gid as "id:ID(Label)",
|
|
label.name,
|
|
fn_sortname(label.name) as sortname,
|
|
-- label_code as code,
|
|
(CASE
|
|
WHEN lt.name IS NULL
|
|
THEN concat('Label;', lt.name)
|
|
ELSE 'Label' END) as ":LABEL"
|
|
FROM label
|
|
LEFT JOIN label_type lt on label.type = lt.id;
|
|
|
|
CREATE OR REPLACE VIEW mg.release_label AS
|
|
SELECT rg.gid as ":START_ID(Release)",
|
|
l.gid as ":END_ID(Label)"
|
|
FROM l_label_release
|
|
INNER JOIN label l on l_label_release.entity0 = l.id
|
|
INNER JOIN release r on l_label_release.entity1 = r.id
|
|
INNER JOIN release_group rg on r.release_group = rg.id
|
|
WHERE r.status = 1;
|
|
-- UNION
|
|
-- SELECT l.gid as ":START_ID(Release)",
|
|
-- r.gid as ":END_ID(Label)"
|
|
-- FROM l_label_release_group
|
|
-- INNER JOIN label l on l_label_release_group.entity0 = l.id
|
|
-- INNER JOIN release_group rg on l_label_release_group.entity1 = rg.id
|
|
-- INNER JOIN release r on r.release_group = rg.id
|
|
|
|
|
|
CREATE OR REPLACE VIEW mg.label_label AS
|
|
SELECT l0.gid as ":START_ID(Label)",
|
|
l1.gid as ":END_ID(Label)",
|
|
t.mg_name as ":TYPE"
|
|
FROM l_label_label
|
|
INNER JOIN label l0 on l_label_label.entity0 = l0.id
|
|
INNER JOIN label l1 on l_label_label.entity1 = l1.id
|
|
INNER JOIN link l on l.id = l_label_label.link
|
|
INNER JOIN link_type lt ON lt.id = l.link_type
|
|
INNER JOIN mg.translate_label_label_rel t ON t.mb_name = lt.name;
|
|
|
|
--------------
|
|
|
|
CREATE TABLE mg.covers
|
|
(
|
|
mbid uuid PRIMARY KEY,
|
|
ts timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
tn bytea
|
|
);
|
|
|
|
CREATE TABLE mg.lastfm_artist
|
|
(
|
|
name TEXT PRIMARY KEY,
|
|
mbid uuid UNIQUE
|
|
);
|
|
|
|
CREATE TABLE mg.lastfm_raw_data
|
|
(
|
|
name TEXT,
|
|
mbid uuid,
|
|
ts timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
data jsonb,
|
|
PRIMARY KEY (name, mbid)
|
|
);
|
|
|
|
CREATE TABLE mg.lastfm_artist_meta
|
|
(
|
|
name TEXT PRIMARY KEY,
|
|
listeners int,
|
|
playcount int
|
|
);
|
|
|
|
CREATE TABLE mg.lastfm_artist_tag
|
|
(
|
|
name TEXT,
|
|
tag TEXT,
|
|
PRIMARY KEY (name, tag)
|
|
);
|
|
|
|
CREATE TABLE mg.lastfm_artist_artist
|
|
(
|
|
name0 TEXT,
|
|
name1 TEXT,
|
|
weight float,
|
|
ts timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (name0, name1)
|
|
);
|
|
|
|
--------------
|
|
|
|
CREATE TABLE mg.spotify_artist
|
|
(
|
|
spotid TEXT PRIMARY KEY,
|
|
mbid UUID UNIQUE
|
|
);
|
|
|
|
CREATE TABLE mg.spotify_artist_meta
|
|
(
|
|
spotid TEXT PRIMARY KEY,
|
|
name TEXT,
|
|
followers int,
|
|
popularity int,
|
|
ts timestamp DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE mg.spotify_artist_tag
|
|
(
|
|
spotid TEXT,
|
|
tag TEXT,
|
|
PRIMARY KEY (spotid, tag)
|
|
);
|
|
|
|
CREATE TABLE mg.spotify_artist_album
|
|
(
|
|
spotid TEXT,
|
|
album TEXT,
|
|
PRIMARY KEY (spotid, album)
|
|
);
|
|
|
|
CREATE TABLE mg.spotify_artist_track
|
|
(
|
|
spotid TEXT,
|
|
track TEXT,
|
|
album TEXT,
|
|
url TEXT,
|
|
PRIMARY KEY (spotid, track)
|
|
);
|
|
|
|
CREATE TABLE mg.spotify_artist_artist
|
|
(
|
|
spotid0 TEXT,
|
|
spotid1 TEXT,
|
|
index int,
|
|
ts timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (spotid0, spotid1)
|
|
);
|
|
|
|
|
|
CREATE TABLE mg.spotify_raw_data
|
|
(
|
|
query TEXT,
|
|
endpoint TEXT,
|
|
ts timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
data jsonb,
|
|
PRIMARY KEY (query, endpoint)
|
|
);
|
|
|
|
--------
|
|
CREATE OR REPLACE FUNCTION asciifold(text) RETURNS text
|
|
AS
|
|
'/pglib/libasciifolding.so',
|
|
'asciifold' LANGUAGE C STRICT
|
|
PARALLEL SAFE;
|
|
|
|
CREATE OR REPLACE FUNCTION asciifold_lower(text) RETURNS text
|
|
AS
|
|
'/pglib/libasciifolding.so',
|
|
'asciifold_lower' LANGUAGE C STRICT
|
|
PARALLEL SAFE;
|