User:JimDeLaHunt/AdvancedRelationshipsCensus

The AdvancedRelationships (ARs) of the MusicBrainzDatabase provide a way to describe relationships between Artists, Releases, Tracks, Labels, and URLs. However, the Search and Statistics features of the MusicBrainz site don't provide a way to list what AdvancedRelationshipTypes are defined, or give statistics of how many of which type are defined. (AdvancedRelationshipType does have a list, shorter and more aggregated than this one.) This page helps remedy that situation, by giving a census of AdvancedRelationships.

This is a static snapshot of one version of the database running on one version of the database code. Thus it doesn't report on the exact current AR population, but it does give useful information for talking generally about the population.

Census report

type0	type1	id	linkphrase	# ARs
album	url	29	links to affiliates	0
album	album	9	is a DJ-mix of	3
track	track	13	is a DJ-mix of	39
album	url	27	is a soundtrack for the movie with an IMDb page at	2621
track	url	19	has tabs on OLGA at	1
album	url	30	has Amazon ASIN	91988
album	artist	15	was} {additional:additionally} arranged by	1634
artist	track	15	{additional:additionally} arranged {instrument:% on}	4753
album	artist	30	has {additional} art direction by	535
artist	track	30	provided {additional} art direction on	0
track	url	8	has art direction by	0
album	artist	28	has artist & repertoire support by	188
artist	track	28	provided artist & repertoire support for	5
track	url	6	has artist & repertoire support by	0
album	artist	20	was {additional:additionally} audio engineered by	49
artist	track	20	{additional:additionally} audio engineered	10
album	artist	27	was booked by	56
artist	track	27	provided booking for	1
track	url	5	was booked by	0
album	artist	43	has {additional} chorus master performed by	147
artist	track	46	performed {additional} chorus master on	303
track	track	12	is a compilation of	0
album	album	8	compilations	0
album	artist	40	compilations	0
artist	track	38	compilations	0
track	track	10	compilations	0
album	artist	41	was compiled by	842
artist	track	39	compiled	2
album	artist	14	was {additional:additionally} composed by	7532
artist	track	14	{additional:additionally} composed	47286
album	artist	13	composition	0
artist	track	13	composition	0
album	artist	9	was {additional:additionally} conducted by	2561
artist	track	9	{additional:additionally} conducted	6248
album	album	13	is a cover of	39
track	track	5	is a cover of	11519
album	url	34	has cover art at	3273
album	album	1	covers or other versions	0
track	track	1	covers or other versions	0
album	url	32	is available for download under the Creative Commons {license} license at	853
track	url	21	is available for download under the Creative Commons {license} license at	151
album	artist	29	has {additional} creative direction by	59
artist	track	29	provided {additional} creative direction on	2
track	url	7	has creative direction by	0
album	artist	31	has {additional} design/illustration by	2384
artist	track	31	provided {additional} design/illustration on	0
track	url	9	has design/illustration by	0
album	url	16	discography	3
album	url	24	has a Discogs page at	51401
album	url	21	can be downloaded for free at	1620
track	url	17	can be downloaded for free at	1039
album	artist	53	was {additional:additionally} edited by	148
artist	track	50	{additional:additionally} edited	343
album	artist	19	was {additional:additionally} {co:co-}{executive:executive }engineered by	4746
artist	track	19	{additional:additionally} {co:co-}{executive:executive }engineered	2797
album	album	2	is the earliest release of	1355
track	track	2	is the earliest release of	2915
album	url	18	get the music	0
track	url	15	get the music	0
album	artist	32	has {additional} graphic design by	627
artist	track	32	provided {additional} graphic design on	0
track	url	10	has graphic design by	0
album	artist	3	has {additional} {guest} {instrument} performed by	20306
artist	track	3	performed {additional} {guest} {instrument} on	33198
album	artist	47	has {additional} {instrument} instrumentation by	31
artist	track	43	provided {additional} {instrument} instrumentation for	127
album	artist	26	has legal representation by	46
artist	track	26	provided legal representation for	0
track	url	4	has legal representation by	0
album	artist	42	libretto was {additional:additionally} written by	66
album	artist	50	has {additional} liner notes by	367
artist	track	47	wrote {additional} liner notes for	0
album	album	11	is a live performance of	13
album	artist	22	was {additional:additionally} live sound engineered by	39
artist	track	22	{additional:additionally} live sound engineered	42
album	artist	16	lyrics were {additional:additionally} written by	1524
artist	track	16	{additional:additionally} wrote the lyrics for	26837
album	album	5	is a mash-up of	15
track	track	8	is a mash-up of	1062
album	artist	45	was {additional:additionally} mastered by	3345
artist	track	41	{additional:additionally} mastered	234
track	track	14	is a medley of	188
album	artist	36	has merchandising by	6
artist	track	36	provided merchandising for	0
track	url	14	has merchandising by	0
album	artist	25	contains miscellaneous support from	363
artist	track	25	has a miscellaneous role on	73
track	url	3	contains miscellaneous support from	0
album	artist	23	was {additional:additionally} {co:co-}mixed by	4069
artist	track	23	{additional:additionally} {co:co-}mixed	4556
album	artist	38	was DJ-mixed by	3868
artist	track	40	DJ-mixed	249
album	url	25	has a MusicMoz page at	95
album	artist	48	was {additional:additionally} orchestrated by	190
artist	track	44	{additional:additionally} orchestrated	632
album	url	22	other databases	6
track	url	18	other databases	0
track	track	4	is the earliest version of	1789
album	artist	1	performance	0
artist	track	1	performance	0
album	artist	2	was {additional:additionally} {guest} performed by	1880
artist	track	2	{additional:additionally} {guest} performed	31451
album	artist	5	was {additional:additionally} performed by {orchestra} orchestra	2399
artist	track	5	{orchestra} orchestra {additional:additionally} performed	5345
album	artist	33	has {additional} photography by	1910
artist	track	33	provided {additional} photography on	5
track	url	11	has {additional} photography by	0
album	artist	18	was {additional:additionally} {co:co-}{executive:executive }produced by	15163
artist	track	18	{additional:additionally} {co:co-}{executive:executive }produced	14171
album	artist	17	production	0
artist	track	17	production	0
track	url	1	production	0
album	artist	52	was} {additional:additionally} programmed by	527
artist	track	49	{additional:additionally} programmed {instrument:% on}	657
album	artist	35	was published by	40
album	label	2	was published by	93
artist	track	35	published	104
label	track	2	published	86
track	url	13	was published by	9
album	url	20	can be purchased for download at	1386
track	url	16	can be purchased for download at	141
album	url	19	can be purchased for mail-order at	2281
album	artist	24	was {additional:additionally} recorded by	1620
artist	track	24	{additional:additionally} recorded	1764
track	url	2	was recorded by studio at	26
album	album	3	is a remaster of	842
track	track	3	is a remaster of	161
album	album	7	is a remix of	106
track	track	11	is a remix of	1610
album	artist	11	was {additional:additionally} remixed by	327
artist	track	11	{additional:additionally} remixed	23909
album	album	4	remixes	0
album	artist	10	remixes	0
artist	track	10	remixes	0
track	track	6	remixes	0
album	url	17	has a review page at	1878
album	artist	12	contains {additional} samples by	33
artist	track	12	produced {instrument} material that was {additional:additionally} sampled in	106
album	track	2	provides {additional} {instrument} samples for	14
track	track	7	contains {additional} samples from	1261
album	artist	21	was {additional:additionally} sound engineered by	136
artist	track	21	{additional:additionally} sound engineered	148
album	album	15	is the original for the transl{transliterated:iter}ated track listing	2156
album	artist	34	had travel arrangements by	7
artist	track	34	provided travel arrangements for	0
track	url	12	has travel arrangement by	0
album	artist	44	is a tribute to	1288
album	artist	4	has {additional} {guest} {vocal} vocal performed by	10052
artist	track	4	performed {additional} {guest} {vocal} vocal on	31544
album	url	23	has a Wikipedia page at	16961

(150 AdvancedRelationshipTypes.) Data as of 2008-01-17. MusicBrainzDatabase code 2007-10-14 branch.

Legend for Census report

• type0, type1: Gives the types of the two MusicBrainz entities which this AdvancedRelationship links. Possible types are: Artist, "album" (an old name for Release still used in the database), Track, Label, or URL. Where type0 and type1 differ, the links are reversed if needed so that type0 is less than type1, e.g. all Track-Artist relationships are reversed into Artist-Track relationships.
• id: An integer which, together with type0 and type1, is the unique technical identifier for the AdvancedRelationshipType.
• linkphrase: An English-language phrase which states the nature of the relationship between the two entities. It describes the relationship, but type0, type1, id is sufficient to identify the AdvancedRelationshipType
• # ARs: The number of AdvancedRelationships actually present in the MusicBrainzDatabase for this AdvancedRelationshipType.
• nn AdvancedRelationshipTypes: How many AdvancedRelationshipTypes are listed in the report. This report lists each AdvancedRelationshipType defined in the database, even if no AdvancedRelationships are created with that type. This line also gives the date of the snapshots of the MusicBrainzDatabase contents and code.
• Report is sorted in order of "Short link phrase", which is similar to "linkphrase" but more succinct, then by type0 and type1. This lists together those relationships with similar meaning but different type0 and type1 values. The report is not in alphabetical order by link phrase or id.

SQL Query for Census report

This is the SQL query code, which, run on a standalone copy of the MusicBrainzDatabase, generated this report. It took about 8 seconds on JimDeLaHunt's machine.

-- print # of each type of AR which includes track or album (aka Release)
select lt.type0, lt.type1, lt.id, lt.linkphrase, count(l.id) as "# ARs"
from (
 (
 (select *, 'album'::text  as type0, 'album'::text  as type1 from lt_album_album
 where id != 0 or shortlinkphrase != 'ROOT') union
 (select *, 'album'::text  as type0, 'track'::text  as type1 from lt_album_track
 where id != 0 or shortlinkphrase != 'ROOT') union
 (select *, 'track'::text  as type0, 'track'::text  as type1 from lt_track_track
 where id != 0 or shortlinkphrase != 'ROOT') union
 (select *, 'album'::text  as type0, 'artist'::text as type1 from lt_album_artist
 where id != 0 or shortlinkphrase != 'ROOT') union
 (select *, 'album'::text  as type0, 'label'::text  as type1 from lt_album_label
 where id != 0 or shortlinkphrase != 'ROOT') union
 (select *, 'album'::text  as type0, 'url'::text    as type1 from lt_album_url
 where id != 0 or shortlinkphrase != 'ROOT') union
 (select *, 'artist'::text as type0, 'track'::text  as type1 from lt_artist_track
 where id != 0 or shortlinkphrase != 'ROOT') union
 (select *, 'label'::text  as type0, 'track'::text  as type1 from lt_label_track
 where id != 0 or shortlinkphrase != 'ROOT') union
 (select *, 'track'::text  as type0, 'url'::text    as type1 from lt_track_url
 where id != 0 or shortlinkphrase != 'ROOT')
  ) as lt full join (
  select *, 'album'::text  as type0, 'album'::text  as type1 from l_album_album  union
  select *, 'album'::text  as type0, 'track'::text  as type1 from l_album_track  union
  select *, 'track'::text  as type0, 'track'::text  as type1 from l_track_track  union
  select *, 'album'::text  as type0, 'artist'::text as type1 from l_album_artist union
  select *, 'album'::text  as type0, 'label'::text  as type1 from l_album_label  union
  select *, 'album'::text  as type0, 'url'::text    as type1 from l_album_url    union
  select *, 'artist'::text as type0, 'track'::text  as type1 from l_artist_track union
  select *, 'label'::text  as type0, 'track'::text  as type1 from l_label_track  union
  select *, 'track'::text  as type0, 'url'::text    as type1 from l_track_url
   ) as l on (l.link_type = lt.id and l.type0 = lt.type0 and l.type1 = lt.type1)
   ) group by lt.id, lt.shortlinkphrase, lt.linkphrase, lt.type0, lt.type1
   order by lt.shortlinkphrase, lt.type0, lt.type1;

Whither this content?

JimDeLaHunt created this page under a user directory as a way of publishing the information without needing to work out where or how this information should best be presented. I think it would be great for it to become a report or entry on the statistics page. -- JimDeLaHunt 2008-01-26

Discussion

Please enter discussion items here. Please remember to write your wiki user name and date after each comment.