User:JimDeLaHunt/AdvancedRelationshipsCensus: Difference between revisions
mNo edit summary |
JimDeLaHunt (talk | contribs) (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
- 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 AdvancedRelationshipType
s: 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.
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.