The Artist-CD-Track sample – version 2

This is an updated version of the Artist-CD-Track sample previously posted.

  1. The syntax for defining a relvar has changed, so that only the heading need be defined as attribute names and types. The type can be inferred from a literal or variable of that type.
  2. The output format has been improved. Note the use of fold(&,) to format lines of output text.
  3. The code has been annotated to assist in understanding.
// translation of sample application from http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual/Example.pod
#noisy 0

// create the database relvars
artist  := {{ artistid:0, name:'' }}
cd      := {{ cdid:0, artistid:0, title:'', year:0 }}
track   := {{ trackid:0, cdid:0, title:'' }}

// some data in temporary relvars, with auto-generated ordinals
artist_data := {
  { name := 'Michael Jackson'}, 
  { name := 'Eminem' }
} [{ *artistid := ord() }]

cd_data := {
  { title := 'Thriller', name := 'Michael Jackson' },
  { title := 'Bad', name := 'Michael Jackson' },
  { title := 'The Marshall Mathers LP', name := 'Eminem' }
} [{ *cdid := ord() }]

track_data := {
   { title := 'Beat It'         , cd := 'Thriller' },
   { title := 'Billie Jean'     , cd := 'Thriller' },
   { title := 'Dirty Diana'     , cd := 'Bad' },
   { title := 'Smooth Criminal' , cd := 'Bad' },
   { title := 'Leave Me Alone'  , cd := 'Bad' },
   { title := 'Stan'            , cd := 'The Marshall Mathers LP' },
   { title := 'The Way I Am'    , cd := 'The Marshall Mathers LP' }
 } [{ *trackid := ord() }]

 // update the database relvars
artist := union artist_data
cd := union (cd_data join artist)[{ title, cdid, artistid,year:=0}]
track := union (track_data join cd[{ *cd := title }]) [{ trackid, title, cdid }]

// functions to answer various queries
get_tracks_by_cd(t) => cd[ title = t {*title} ] join track
get_tracks_by_artist(a) => (artist[ name = a {*name}] join cd) [{cdid}] join track
get_cd_by_track(t) => track [ title = t {cdid} ] join cd
get_cds_by_artist(a) => artist [name = a {artistid} ] join cd
get_artist_by_track(t) => (track [title = t { cdid }] join cd) [{artistid}] join artist
get_artist_by_cd(t) => (cd [title = t { cdid }] join cd) [{artistid}] join artist

// first show the raw data

crlf := h'd a'
out := crlf & "=== Sample data ===" & crlf
out := artist
out := crlf
out := cd
out := crlf
out := track

// now do the queries

show(title, data:{{str:''}}) => do {
    out := title & crlf & data[ {fold(&, "  " & str & crlf)} ]
}

out := crlf & "=== Query results ===" & crlf
show("Track title:", get_tracks_by_cd('Bad') [ {str:=title} ])
show("Track title:", get_tracks_by_artist('Michael Jackson') [ {str:=title} ])
show("CD title:", get_cd_by_track('Stan') [ {str:=title} ])
show("CD title:", get_cds_by_artist('Michael Jackson') [ {str:=title} ])
show("Artist:", get_artist_by_track('Dirty Diana') [ {str:=name} ])
show("Artist:", get_artist_by_cd('The Marshall Mathers LP') [ {str:=name} ])

The output now looks like this.

 == Sample data ===

artistid | name
--------------------------
       0 | Michael Jackson
       1 | Eminem


cdid     | artistid | title                   | year
--------------------------------------------------------
       0 |        0 | Thriller                |        0
       1 |        0 | Bad                     |        0
       2 |        1 | The Marshall Mathers LP |        0


trackid  | cdid     | title
-------------------------------------
       0 |        0 | Beat It
       1 |        0 | Billie Jean
       2 |        1 | Dirty Diana
       3 |        1 | Smooth Criminal
       4 |        1 | Leave Me Alone
       5 |        2 | Stan
       6 |        2 | The Way I Am

=== Query results ===

Track title:
  Dirty Diana
  Smooth Criminal
  Leave Me Alone

Track title:
  Beat It
  Billie Jean
  Dirty Diana
  Smooth Criminal
  Leave Me Alone

CD title:
  The Marshall Mathers LP

CD title:
  Thriller
  Bad

Artist:
  Michael Jackson

Artist:
  Eminem

Leave a Comment

Filed under Code sample

Leave a Reply

Your email address will not be published. Required fields are marked *