User:JimDeLaHunt/AdvancedRelationshipsCensus

From MusicBrainz Wiki
< User:JimDeLaHunt
Revision as of 06:58, 27 January 2008 by JimDeLaHunt (talk) (First content (Imported from MoinMoin))
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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.