This is an updated version of the Artist-CD-Track sample previously posted.
- 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.
- The output format has been improved. Note the use of fold(&,) to format lines of output text.
- 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