User:JimDeLaHunt/AdvancedRelationshipsCensus: Difference between revisions

From MusicBrainz Wiki
Jump to navigationJump to search
mNo edit summary
(Historical snapshot disclaimer. Link to fresher census.)
 
Line 2: Line 2:


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

'''N.B.''': this snapshot is very old, from '''27 January 2008'''. I keep it as a historical record. As of October 2011, the page http://mb.lmfao.org.uk/reltype has a census which seems to be refreshed fairly regularly. [[User:JimDeLaHunt|JimDeLaHunt]] 21:36, 30 October 2011 (UTC)


==Census report==
==Census report==

Latest revision as of 21:36, 30 October 2011

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.

N.B.: this snapshot is very old, from 27 January 2008. I keep it as a historical record. As of October 2011, the page http://mb.lmfao.org.uk/reltype has a census which seems to be refreshed fairly regularly. JimDeLaHunt 21:36, 30 October 2011 (UTC)

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
artist url 17 has an IMDb page at 3754
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
artist url 4 has a biography page at 2377
artist url 24 has a blog at 76
album artist 27 was booked by 56
artist track 27 provided booking for 1
track url 5 was booked by 0
label label 1 business association 0
label url 5 has a catalog of records at 308
album artist 43 has {additional} chorus master performed by 147
artist track 46 performed {additional} chorus master on 303
artist artist 11 collaborated {minor:minorly} {additional:additionally} on 15285
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
artist label 1 contract 0
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
artist label 4 had a creative position at 19
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
artist url 5 has a discography page at 3849
album url 16 discography 3
artist url 1 discography 5
album url 24 has a Discogs page at 51401
artist url 11 has a Discogs page at 45812
label url 9 has a Discogs page at 4683
album url 21 can be downloaded for free at 1620
artist url 8 music can be downloaded for free at 1107
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
artist label 3 had an engineer position at 2
artist url 3 has a fan page at 2354
label url 7 has a fan page at 8
album album 2 is the earliest release of 1355
track track 2 is the earliest release of 2915
artist url 6 get the music 2
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
label url 4 has its history presented at 67
artist url 14 has a picture at 394
album artist 3 has {additional} {guest} {instrument} performed by 20306
artist track 3 performed {additional} {guest} {instrument} on 33198
artist artist 15 does/did {instrument} support for 149
album artist 47 has {additional} {instrument} instrumentation by 31
artist track 43 provided {additional} {instrument} instrumentation for 127
artist artist 9 is/was involved with 106
artist artist 4 performs as 11057
label label 5 is/was distributing the catalog of 254
artist label 6 founded 851
label label 2 is/was the parent label of 1479
label label 3 is/was reissuing the catalog of 28
label label 4 was renamed into 94
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
label url 6 has a logo at 93
album artist 16 lyrics were {additional:additionally} written by 1524
artist track 16 {additional:additionally} wrote the lyrics for 26837
artist artist 8 is/was married to 545
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
artist artist 2 a} member of 53028
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
artist artist 1 musical relationship 0
album url 25 has a MusicMoz page at 95
artist url 12 has a MusicMoz page at 980
artist url 19 has a MySpace page at 10320
label url 10 has a MySpace page at 656
artist url 2 has an official homepage at 28635
label url 3 has an official homepage at 4022
artist url 21 has an online community page at 85
label url 1 online data 0
album artist 48 was {additional:additionally} orchestrated by 190
artist track 44 {additional:additionally} orchestrated 632
album url 22 other databases 6
artist url 9 other databases 2
label url 2 other databases 0
track url 18 other databases 0
track track 4 is the earliest version of 1789
artist artist 6 is the parent of 660
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
artist artist 5 personal relationship 0
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
artist label 2 had a producer position at 14
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
artist url 7 music can be purchased for download at 244
track url 16 can be purchased for download at 141
album url 19 can be purchased for mail-order at 2281
artist url 15 music can be purchased for mail-order at 427
artist url 22 has a PureVolume page at 128
album artist 24 was {additional:additionally} recorded by 1620
artist track 24 {additional:additionally} recorded 1764
artist label 5 had a recording contract with 291
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
artist artist 7 has sibling(s) 1169
album artist 21 was {additional:additionally} sound engineered by 136
artist track 21 {additional:additionally} sound engineered 148
artist artist 13 is/was a supporting musician for 267
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
artist artist 14 does/did {vocal} vocal support for 107
album url 23 has a Wikipedia page at 16961
artist url 10 has a Wikipedia page at 28738
label url 8 has a Wikipedia page at 1028

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

Legend for Census report

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.

Some AdvancedRelationshipTypes are headers for a group of related AR types. For example, remixes is a header for is a remix of, DJ-mixed by, etc. Such AR types may have zero entries.

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 ARs in each of the 15 AdvancedRelationshipTypes
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') union
    (select *, 'artist'::text as type0, 'artist'::text as type1 from lt_artist_artist
    where id != 0 or shortlinkphrase != 'ROOT') union
    (select *, 'artist'::text as type0, 'label'::text  as type1 from lt_artist_label
    where id != 0 or shortlinkphrase != 'ROOT') union
    (select *, 'artist'::text as type0, 'url'::text    as type1 from lt_artist_url
    where id != 0 or shortlinkphrase != 'ROOT') union
    (select *, 'label'::text  as type0, 'label'::text  as type1 from lt_label_label
    where id != 0 or shortlinkphrase != 'ROOT') union
    (select *, 'label'::text  as type0, 'url'::text    as type1 from lt_label_url
    where id != 0 or shortlinkphrase != 'ROOT') union
    (select *, 'url'::text    as type0, 'url'::text    as type1 from lt_url_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     union
    select *, 'artist'::text as type0, 'artist'::text as type1 from l_artist_artist union
    select *, 'artist'::text as type0, 'label'::text  as type1 from l_artist_label  union
    select *, 'artist'::text as type0, 'url'::text    as type1 from l_artist_url    union
    select *, 'label'::text  as type0, 'label'::text  as type1 from l_label_label   union
    select *, 'label'::text  as type0, 'url'::text    as type1 from l_label_url     union
    select *, 'url'::text    as type0, 'url'::text    as type1 from l_url_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.