How do you work out the order of link attributes in database when looking at relationships

Tags: #<Tag:0x00007fbc8446b408>

Hi, so I am looking at a relationship for https://musicbrainz.org/recording/a97c7819-8124-4d5f-972f-18386f0eaef5

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

Relationships are formatted for display using link.*_phrase.

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)

1 Like

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.

2 Likes

Thankyou !

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)

No, this is meant as “if there’s an instrument, use that instrument, else, use the word ‘instruments’”.

1 Like

So that link type phrase only refers to that particular link type, not all the link types for the link ?

I dont quite get it.

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).

There’s a schema of these tables on the docs, if it’s useful.

2 Likes

okay thanks, I mean link attribute but confused myself, i did nt known about that schema page so that is useful.