The artist has a instrument link type consisting of the additional link attribute and two instrument link attributes.
And can be retrieved from database as follows
select t2.id, t3.name, t6.*, t7.name from l_artist_recording t1 inner join link t2 on t1.link=t2.id inner join link_type t3 on t2.link_type=t3.id left join link_attribute t6 on t6.link=t2.id left join link_attribute_type t7 on t6.attribute_type=t7.id where entity1=1020623 order by t1.entity1, t1.entity0, t2.id;
id | name | link | attribute_type | created | name
-------±-----------±------±---------------±------------------------------±-----------------
12737 | producer | | | |
12740 | instrument | 12740 | 1 | 2011-05-16 15:03:23.368437+00 | additional
12740 | instrument | 12740 | 180 | 2011-05-16 15:03:23.368437+00 | piano
12740 | instrument | 12740 | 75 | 2011-05-16 15:03:23.368437+00 | guitar family
13237 | instrument | 13237 | 126 | 2011-05-16 15:03:23.368437+00 | drums (drum set)
(5 rows)
But what I dont understand is how the database stores link attribute order
i.e so it is always displayed additional guitar family piano rather than additional piano guitar or guitar piano additional
Hi I cant see a phrase column , is that what you mean ?
jthinksearch=# \d link;
Table "musicbrainz.link"
Column | Type | Modifiers
------------------+--------------------------+---------------------------------------------------
id | integer | not null default nextval('link_id_seq'::regclass)
link_type | integer | not null
begin_date_year | smallint |
begin_date_month | smallint |
begin_date_day | smallint |
end_date_year | smallint |
end_date_month | smallint |
end_date_day | smallint |
attribute_count | integer | not null default 0
created | timestamp with time zone | default now()
ended | boolean | not null default false
Indexes:
"link_pkey" PRIMARY KEY, btree (id)
"link_idx_type_attr" btree (link_type, attribute_count)
"link_link_type_idx" btree (link_type)
Check constraints:
"link_ended_check" CHECK ((end_date_year IS NOT NULL OR end_date_month IS NOT NULL OR end_date_day IS NOT NULL) AND ended = true OR end_date_year IS NULL AND end_date_month IS NULL AND end_date_day IS NULL)
It is possible I have removed that column for some reason but I dont remember doing such a thing (im using mbslave)
It’s not link.*_phrase but link_type.*_phrase. That’s the “X performed additional guitar family in Y” phrase. Elsewhere, I’m not sure we have a specific order other than alphabetical, at the moment.
So I think I get it now, in this case we have a link (id=12740) with three link_types, these have the long_link_phrase (I guess when you have multiple link types for a link they must all have the same long_link_phrase)
performed {additional} {guest} {solo} {instrument:%|instruments}
So that determines that if there is a link type of Additional (which there is) this comes before anything else (and if there was an attribute of guest (which there isn’t) that would come before solo etc. Then the instruments come at the end but the order is not defined they are just displayed alphabetically (are we sure about that), and I guess this {instrument:%|instruments} part is interpreted to mean separate multiple instruments by and , is this some particular scripting syntax ?
So if I have understood this right the database specifies how to display the link, and therefore implicity gives an order but no order is actually defined at database level (i.e additional=1, guitar family=2, piano =3)
jthinksearch=# select t2.id, t3.name, t3.long_link_phrase, t6.attribute_type, t7.name from l_artist_recording t1 inner join link t2 on t1.link=t2.id inner join link_type t3 on t2.link_type=t3.id left join link_attribute t6 on t6.link=t2.id left join link_attribute_type t7 on t6.attribute_type=t7.id where entity1=1020623 order by t1.entity1, t1.entity0, t2.id;
id | name | long_link_phrase | attribute_type | name
-------±-----------±-----------------------------------------------------------------------------------------±---------------±-----------------
12737 | producer | {additional:additionally} {assistant} {associate} {co:co-}{executive:executive }produced | |
12740 | instrument | performed {additional} {guest} {solo} {instrument:%|instruments} on | 1 | additional
12740 | instrument | performed {additional} {guest} {solo} {instrument:%|instruments} on | 180 | piano
12740 | instrument | performed {additional} {guest} {solo} {instrument:%|instruments} on | 75 | guitar family
13237 | instrument | performed {additional} {guest} {solo} {instrument:%|instruments} on | 126 | drums (drum set)
(5 rows)
Each link can only have one type (one specific relationship). Each link can have multiple attributes (stored in link_attribute), and each relationship type (link_type) allows a specific selection of attribute types (link_type_attribute_type).
