How to Open an SQL Prompt to the UYKFD Database

MySQL

mysql -u $UYKFD_DB_USER uykfd

HSQLDB

Assuming that the HSQLDB jar is in your directory, use a command like:

java -jar hsqldb-1.8.0.7.jar --inlineRc URL=$UYKFD_DB_URL,USER=$UYKFD_DB_USER

The HSQLDB jar can be found in lib_managed/compile once the program has been built with sbt:

java -jar lib_managed/compile/hsqldb-1.8.0.7.jar --inlineRc URL=$UYKFD_DB_URL,USER=$UYKFD_DB_USER

The UYKFD.. variables are described in the configuration.

Note that by default the SQL tool does not commit changes - you must explicitly enter commit; before exiting, or use --autoCommit.

Useful Queries

Note: MySQL requires that table names be in CAPS.

• The artists and their canonical forms (the canonical forms are sent to Last FM)

select a.value, c.value 
  from artists as a
  join artist_canonical as ac
    on a.id = ac.artist
  join canonicals as c
    on ac.canonical = c.id;

• Canonical forms of artists without tags (LastfmArtistTag.untaggedArtists)

select id, value
  from (select distinct canonical
          from artist_canonical
          left outer join lfm_artist_tags
            on canonical = left
         where left is null)
  join canonicals
    on canonical = id;

• Basic data on a track, given its ID

select *
  from tracks as t
  join albums as b
    on t.album = b.id
  join artists as a
    on t.artist = a.id
  join song_titles as s
    on t.song_title = s.id
  join artist_canonical as ac
    on a.id = ac.artist
  join canonicals as c
    on ac.canonical = c.id
 where t.id = 651;

• Tags for a given artist

select a.value, c.value
  from artists as a
  join artist_canonical as ac
    on a.id = ac.artist
  join lfm_artist_tags as t
    on t.left = ac.canonical
  join canonicals as c
    on t.right = c.id
 where a.value = 'Underworld';