User:JimDeLaHunt/AdvancedRelationshipsCensus: Difference between revisions
JimDeLaHunt (talk | contribs) (First content (Imported from MoinMoin)) |
JimDeLaHunt (talk | contribs) (Disclaimer, only ARs involving Album and Track at the moment. (Imported from MoinMoin)) |
||
Line 5: | Line 5: | ||
==Census report== |
==Census report== |
||
Note: this presently has just the ARs which involve album or track. I expect to have all ARs represented by 2008-02-01. --[[User:JimDeLaHunt|JimDeLaHunt]]. |
|||
{| border="1" |
{| border="1" |
||
|- |
|- |
Revision as of 07:32, 27 January 2008
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
Note: this presently has just the ARs which involve album or track. I expect to have all ARs represented by 2008-02-01. --JimDeLaHunt.
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 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.
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.