Play Music By Advanced SQL Selection

If you know anything about databases, you'll know that you can query them by means of a 'Structured Query Language' (SQL) select statement. If you are fluent in writing SQL, you can use the Play Music menu Option 4 : Play music by advanced SQL selection to construct SQL select statements to pick out music you want to listen to and have Giocoso play the results. If you are not familiar with relational databases and SQL, then I strongly suggest you give this menu option a miss! The word 'advanced' is in there for a reason!!

Giocoso will not hand-hold you at this point: you're expected to know how to construct the WHERE clauses of a SQL query, in a syntactically correct manner for a MySQL (or MariaDB) database.

To give you a hint as to why you might want to get up to speed with this menu option, however, consider this use of the selection filter form (from the Play Music menu Option 2):

It's fairly clear, I think, what the user is trying to do here: I'd like to listen to quartets and/or chamber music by two different British composers. It's a perfectly reasonable request, but Giocoso will respond to it this way:

This is because Giocoso matches each filter with the contents of each metadata tag… so saying 'Composer = Britten or Bridge' means Giocoso is looking for music whose ARTIST tag was literally set to 'Britten or Bridge'… and that's an absurd value to assign to an ARTIST tag! Giocoso is not reading these filters in an intelligent manner, basically: it's treating them as literal text, so only literal matches work.

The same is true for the GENRE tag: you'll probably have tagged your music as either being a 'Quartet' or as being 'Chamber' music, but it's highly unlikely that you'll have tagged any of it with the literal text 'Quartet or Chamber'. If you didn't, then nothing is going to match the filter settings on this screen!

The Giocoso 'standard' selection filter cannot, in other words, do 'or' tests…. which is why you might want to start writing your own SQL which can do so with relative ease. Here, for example, is a selection test that would precisely give accurate results which the filter form was attempting to achieve but could not:

You'll first note that the form displayed only processes the text in a SQL query that comes after the WHERE keyword. I do not need (and in fact must not write!) anything from before that point: Giocoso fills all that in for you. You do not need to say “select * from…” or mention table names, for example. All you have to do is mention composers, genres, performers and other tag-derived information in a SQL WHERE clause in a way that makes logical sense.

Giocoso will also 'sanitise' your input to some extent. If, for example, you use the keywords update or delete or include a semi-colon in your text, those will all be stripped out of your text automatically. If the resulting query results in gibberish, so be it: you'll be told nothing matches your selection criteria. If more than one recording does match the submitted WHERE clause, then you'll be offered a chance to save the matching records in a playlist. If you choose not to, fine: playback of the selections begins immediately. If you do save your playlist, playback begins immediately after the playlist is saved.

Here is the playlist resulting from the Britten/Bridge/Chamber/Quartet query you see me submitting above:

It certainly mentions recordings by the two named composers and in the two genres requested (and no others!): so multiple selections joined with AND and OR keywords, with judicious use of parentheses to constrain processing order along logically-meaningful lines has certainly worked in this case.

Hopefully, you'll now see why the Advanced SQL option exists -but if you aren't familiar with writing SQL, it's a high mountain to climb. I would strongly recommend installing a tool such as DB Browser for SQLite and using its GUI tools to investigate the Giocoso database structure and to practice SQL selections before hoping to use Play Menu option 4 to meaningfully generate the sort of subtle and complex music selections it's capable of, anyway.

A couple of notes to conclude.

• If you are not using Giocoso Pro features, the table you'll be querying is called RECORDINGS, and you can query columns called: DIRNAME (the folder in which a FLAC is physically found), COMPOSER, COMPOSITION, GENRE, COMMENT, PERFORMER and DURATION. Remember that COMMENT is where Giocoso assumes you store your complete list of performers for a recording and PERFORMER is where you store the full name of the 'distinguishing artist'. See Axioms 3 and 6.
• If you are using Giocoso Pro features, you'll be querying a table called GLOBAL_RECORDINGS and can query columns called: FILESUFFIX (almost equivalent to DIRNAME), COMPOSER, COMPOSITION, GENRE, COMMENT, PERFORMER and DURATION. The difference between DIRNAME and FILESUFFIX is simply that DIRNAME is the full path to the location of the file on the computer Giocoso is running on, where FILESUFFIX is only the part of the physical location of the file that is common to all computers that are running Giocoso on the same home network.
• DURATION is always stored in seconds, so a query of “duration < 30” is asking to play recordings that are less than 30 seconds long! If you want minutes, remember to multiply by 60 to match what's stored in the database: “duration < 1800” will indeed give you recordings that last less than 30 minutes (because there are 3600 seconds to an hour, of course).
• Wildcards can be specified, provided you use the syntax column LIKE %required_text%. For example, composer like %rit% would find recordings by Benjamin Britten and Moritz Moszkowski.
• AND and OR joins are fine, but if you use both, usual SQL rules of ambiguity apply, which can be resolved only by use of parentheses. For example composer like '%britten%' and genre = 'choral' or genre = 'symphonic' is ambiguous: do you mean, give me everything by Benjamin Britten, then everything (by anyone) that's a choral or symphonic work? Or do you mean, give me everything by Benjamin Britten that's a choral work plus everything that's a symphonic work regardless of composer. Or (most likely) did you mean, give me everything that Benjamin Britten wrote that's either a choral work or a symphonic one? If you meant this last one, you would have to re-write the query with brackets: composer like '%britten%' and (genre = 'choral' or genre = 'symphonic')
• Values must be single-quoted. It's composer like '%britten%' not composer like %britten%.
• If you are querying for a string that itself contains something that looks like a single quote, it must be escaped. If you wanted to search for the works of Vincent d'Indy, for example, your query would have to be in the form: composer='Vincent d''Indy'
• Writing useful queries can be tricky, so once you've got one that you like, note that Giocoso remembers is so long as you don't type a new query in. If you tap the K (or k) key to “rekall” the previously-submitted SQL query, Giocoso will pre-load the dialog screen where you type fresh queries with the last-submitted query. The recalled SQL can then be modified as you see fit or simply re-submitted as-is.

| Back to Software Home | Back to Giocoso Documentation Home | Back to Play Music Menu |