Reporting with Giocoso

1.0 Introduction

Giocoso's database not only stores details about what music you have in your collection, but also what music you've played from your collection. As each play of a recording completes, the fact of that play is noted in the database. Giocoso then provides a number of runtime parameters which you can use to query things about that play history -and, indeed, about your music collection generally. These reporting runtime parameters trigger Giocoso's querying of its database, rather than the playing of any new music.

By definition, all reporting parameters must be used together with the --dbname=xxxx runtime parameter, so that we know precisely what database you are wanting to report on. Note that rather than specifying the database name as a runtime parameter, you can set it in the Giocoso persistent configuration file (DBNAME=xxxx). If that's set in the configuration file, then the runtime parameter is not needed -though if it's provided anyway, it overrides whatever the configuration file may be set to.

There are six reporting runtime parameters which I'll discuss in this article. A special extra parameter (--stats) is also discussed, but as it behaves quite differently from the others, it should be thought of as its own unique kind of parameter.

2.0 Reporting Parameters

2.1 Report

The --report=xxxx runtime parameter produces a list of music plays that have been performed with Giocoso, including a timestamp so you know precisely when something was played and the usual composer+composition+genre metadata, derived from the tag data stored within the FLAC files as they were played.

The parameter takes two possible values: either xxxx is the word screen; or it's a full path and file name. The 'screen' value makes Giocoso write its report to the terminal session it's running in; the 'full path and file name' triggers the output of the report to a pipe-delimited text file somewhere you choose on your file system. If the path or filename contain spaces, wrap the entire parameter value in double quotation marks. Thus, --report=/home/hjr/report.txt would work fine, but --report=/home/hjr/my report.txt would not, because of the space between 'my' and 'report'. To make that second example work, therefore, you would have to type it as --report="/home/hjr/my report.txt", encased in double quotes.

2.1.1 On-Screen Reports

Here is the simplest case:

giocoso --dbname=main --report=screen

...which produces this on-screen output:

You will note that on-screen reports take up a lot of room and you will probably need to increase the width of your terminal session to accommodate all the data without it wrapping. The report shown (the one you get by default, using the simplest reporting syntax possible) is of the Giocoso PLAYS table: it simply lists every play that Giocoso has ever made from your collection, in sequential order, starting with the earliest and ending with the most recent.

You can press the Up- and Down-Arrow keys to step through the report in either direction, one row at a time. You can also use the PgUp and PgDn keys to step through the report one page-full of records at a time. At the bottom of each screen displayed is a colon symbol (:). If you type the letter 'q' there, the report terminates and you are returned to your command line prompt. These 'navigation principles' apply to all reports produced by Giocoso, not just this specific one of the music plays that have taken place.

2.1.2 Text Reports

If the value supplied to the --report parameter is of a path+file name, Giocoso will write the data it would have displayed on the screen to a file on your hard disk, in the folder and with a name as supplied by the parameter value.

If a file of the same name and in the same location is already present, Giocoso will simply over-write it, without warning or prompting.

If the path doesn't exist, Giocoso will attempt to create it; if it cannot do so (because you lack the necessary privileges to create it), Giocoso will error and write nothing.

The simplest case is therefore this one:

giocoso --dbname=main --report=/home/hjr/Desktop/report.txt

...which produces this result:

If I change the specified path+file name to /home/hjr/Desktop/reports/report.txt (i.e., asking for the report to be written into a sub-folder that doesn't exist), I'll get this response instead:

...and you can see that a new sub-folder has been created to house the new report. If, however, I had tried a parameter values of /home2/hjr/Desktop (which doesn't exist, and which requires root privileges to create, since 'home2' is hanging off the root folder), I'd get this response:

...and you can see that Giocoso gives up at this point, since it lacks the ability to escalate its privileges to be able to create the requested folder (for good reasons!)

If you open up one of the reports that does get written, you'd see this sort of output:

This tells you that (a) the report is indeed output as a text file, rather than in some binary format; and (b) the contents of the report are "pipe-delimited", meaning that one data element is separated from the next by the | character. This makes the report rather tricky to read sensibly within a regular text editor... but it makes it very suitable for importing into a spreadsheet. Here is what happens when I open the same report you see above in LibreOffice's 'Calc' spreadsheet program, for example:

You will see that LibreOffice offers to interpret the file as if it's delimited by tab, comma, semicolon, space or 'other'. I've de-selected all of the first four options, switched on the 'other' option and typed a pipe character in the box provided. The display of a data preview in the bottom half of the dialog box tells you that Calc is already interpreting the data correctly, separating things out nicely into individual columns. Clicking [OK] performs the full data import:

Obviously, once a report's data is accessible inside a spreadsheet, it can be graphed and analysed at leisure!

2.2 Reportsort

You have seen above that the ordinary report of plays will list every play in date/time-ascending order. Thus, my play of Messiaen's Thème et variations took place on 9th January 2021 at 4:48pm; the next plays was of some Lambranzi at 5pm; then some Perti at 6:13pm and so on. At row 8, the date suddenly changes to be the 10th of January; at row 24, it's the 11th of January and so on.

Date/Time-ordered listing is only the default and first sort order, however: you can use the --reportsort=x runtime parameter to specify whether to sort by composer (2), composition (3), genre (4) or duration (5). The parameter value is one of those bracketed numbers, representing that named column in the report. If you supply a parameter value outside of the range 1 to 5, 1 is assumed (meaning you simply get the default date+time sort order once more).

By way of example, this command:

giocoso --dbname=main --report=screen --reportsort=3

...yields this sort of output:

...and you can see that each unique composition name is now sorted together. For any particular composition/recording, if it has been played multiple times, the rows of the report will ascend in date/time order: thus you see the three plays of Bowman's 12th Street Rag are listed in 30th January, 2nd February and 2nd June order.

If you ever found yourself wondering, 'When did I last play Das Lied von der Erde?', then this sort of report (ordered by composition name, and within that by date/time of play) is very helpful:

...though you'll have to be good with the PgDn and PgUp keys to flip through hundreds or thousands of plays before finding the one you're interested in!

Having generated a report in one sort order, you cannot dynamically change the sorting order. You can only press 'q' to quit and relaunch Giocoso with a different --reportsort parameter value.

2.3 Reportdays

As you have seen, the reports mentioned so far, however they might be sorted, always include the entire contents of the PLAYS table in the Giocoso database. Once you have been running Giocoso for several months, however, this might mean that hundreds or thousands of play records are included in the report.

The --reportdays=x runtime parameter restricts the report to only listing those plays which have taken place within the past x days.

The parameter value must be a positive integer: if you try supplying a negative number (e.g. -10) or a non-integer value (e.g., 10.4), then a default value is applied of 36,500 -which is the number of days in a century (give or take) and therefore is the equivalent of "don't restrict what gets into the report at all".

Here is an on-screen report that doesn't use the --reportdays parameter at all whilst using the default date/time sort order:

You can see that my earliest plays took place in January 2021. When I re-run this report with this command, however:

giocoso --dbname=main --report=screen --reportdays=10

...then this is the result:

It is still listing things in date order -but now the first record listed is from 8th June 2022. Everything from before that has been excluded from the report. Given that the date as I write is the 18th June, you can see that Giocoso has worked 10 days back and started reporting from there, precisely as I asked.

Be aware that Giocoso Version 2 now reports in whole days. That is, if it is 3pm on the 18th June and you say --reportdays=10, then the report will list all plays that took place from the very beginning of the 8th June, not those which took place 240 hours ago. (This is a change of behaviour from Giocoso Version 1, which would literally have counted backwards 240 hours and starting reporting from there, forward to the present).

2.4 Recordinglist

Just as the --report runtime parameter triggers the production of a report on the history of plays recorded by Giocoso, so the --recordinglist runtime parameter triggers the production of a report on the recordings in the Giocoso database.

This parameter behaves almost exactly as the --report runtime parameter does: it accepts a value of 'screen' or '/full/path/and/filename'. The report will display the composer, the composition name and the duration of the recording in question; it will be sorted in composer name order, and within the composer name, by composition name. Unlike the report of plays, the report of recordings cannot be produced with a different sort order.

The simplest case is therefore:

giocoso --dbname=main --recordinglist=screen

...which yields this sort of report:

Note that the durations are listed in seconds (and decimal fractions thereof). The initial 'id' column shows the unique recording ID that Giocoso assigned to a recording when scanning your music collection (either at database creation time, or during a subsequent refresh). It can be used to force Giocoso to play a specific recording, by plugging the number into the  --recordnumber=x runtime parameter. It is important to realise, however, that this ID is not immutable. If you add new recordings to your collections, then existing ones may sort lower down than previously -and at the next database refresh they will therefore be assigned higher numbers than previously.

The ID is thus a unique identifier for a recording now; it may not identify the same recording in the future.

Changing the Giocoso command slightly to this:

giocoso --dbname=main --recordinglist=/home/hjr/recordings.txt

...results in this sort of output:

...which should be recognisable as exactly the same data as before, but this time presented in pipe-delimited format. As was seen earlier (Section 2.1.2), this format is very suitable for importing into a spreadsheet program of your choice -and performing calculations, graphing, charing and analysis there.

2.5 Composername

Just as the --report parameter produces a report of plays whose content can be restricted by applying the --reportdays parameter, so the list of recordings produced by --recordinglist can be filtered and restricted into containing only those recordings attributed to a particular composer by supplying a --composername=xxxx runtime parameter. For this purpose, remember that Giocoso regards the ARTIST tag as containing the composer's name for any recording, and so it is to data from that tag alone that this parameter value is compared.

To take a simple example: I want to list all the recordings I own that are of music by Zbigniew Preisner. This command will do the job:

giocoso --dbname=main --recordinglist=screen --composername=Zbigniew

For me, that produces this output:

...which tells me, without too much detailed analysis, that I probably ought to be buying more recordings of Preisner's work!

As you can tell from that example, the --composername parameter value is applied case-insensitively and is also wild-carded. That 'bri' will match 'Benjamin Britten' and 'Frank Bridge'. So will a composername of 'BRI' or 'bRi'.

As is true for all runtime parameters that take textual values, if the value you supply contains spaces or apostrophes, wrap the entire value in double quotation marks. If you want to know what music of Vincent d'Indy you own, for example, do not do this:

giocoso --dbname=main --recordinglist=screen --composername=Vincent d'Indy

...because that will result in this:

It might not be very clear what's happening in that screenshot: but basically I'm now 'trapped' inside a '>' prompt that does nothing no matter how many times I press [Enter]. I just get more '>' symbols every time I do! That's because the apostrophe in d'Indy's name is 'naked' and thus is confusing the operating system. If I press Ctrl+C to quit out of this mess and instead submit this command:

giocoso --dbname=main --recordinglist=screen --composername="Vincent d'Indy"

...the double-quotes around the composer's name now effectively 'hides' the naked apostrophe from the operating system and everything then works as it should:

Which is all fine and dandy. Pun intended.

3.0 Stats

The --stats runtime parameter is somewhat unique among the Giocoso reporting parameters. Its output is only ever to screen, never to a text file; its content is completely fixed and cannot be filtered or re-ordered in any way; it requires and will not accept any parameter values. It also only reports on aggregate statistics collected and computed from the Giocoso database. The command:

giocoso --dbname=main --stats

...therefore outputs this report:

The top six rows of the report simply count or accumulate values for the various characteristics named in the 'Statistic' column.

  • Statistics 1, 2 and 3 are summations of the RECORDINGS table: how many distinct composer names (as derived from the ARTIST tag) are contained within that table; how many recordings have been scanned and added to the table; and what is the total duration of all the scanned recordings, converting from seconds into days.
  • Statistics 4 is a simple count of records found in the PLAYS table, thus indicating how many 'listens' to recordings have been stored in the database.
  • Statistic 5 is a computation derived from both the PLAYS and RECORDINGS table. Something is in the PLAYS table if it's been played; something is in the RECORDINGS table if it merely exists. Giocoso therefore simply counts the number of RECORDINGS which do not appear at all in the PLAYS table: those are unplayed recordings.
  • Statistic 6 takes that count of plays and divides by the number of days in which something has been played to derive an 'average count of plays per day'. Be warned that this average might not be computed as you'd expect! Suppose I play 10 recordings on Monday, Tuesday and Wednesday, but nothing at all on Thursday and Friday. That's 30 recordings in total by Saturday morning. Your average mathematician would probably say that the average plays per day is 6, because that's what (10+10+10+0+0)÷5 works out to. However, Giocoso will tell you that your average plays per day is 10, because it ignores completely any days on which nothing at all is played. Thus its calculation is (10+10+10)÷3, which is indeed 10.

The 'Duration Range' section of the Stats report is simply a count of how many recordings have durations that fall within the specific time-ranges listed, and how many of them have been played or remain unplayed. The ranges do not overlap, even if the wording used on-screen perhaps suggests they might! If you add up the 'Total' column in that last screenshot, for example, you'll find it adds to 14,577 which is precisely what Statistic 2 says it should be! Where the text says 'between 10 and 20 minutes', in other words, Giocoso is actually counting recordings whose durations are >10 and <=20; then 'between 20 and 60 minutes' is calculated as recordings whose durations are >20 and <=60 minutes and so on. A single recording cannot, therefore, appear in more than one duration range.

You cannot export the Stats report to a text file (though you can, of course, copy-and-paste the on-screen contents into a text editor).

4.0 Final Points

The --report parameter reports on plays; the --recordinglist parameter reports on recordings. The --stats parameter does something that involves both tables and is sui generis. The three parameters are therefore quite distinct and do quite different things. You cannot therefore combine them. If you use 2 or 3 of the parameters at the same time, an order of precedence takes effect: report > recordinglist > stats. Thus, this command

giocoso --dbname=main --recordinglist=screen --report=screen --stats

...produces this output:

...which is a listing of all the plays that have taken place, meaning it's the output from the --report parameter; similarly the command:

giocoso --dbname=main --stats --recordinglist=screen --report=screen

...produces exactly the same output, because --report always wins over the other two parameters.

5.0 Conclusion

Summing all of this up, therefore:

  • The --report parameter produces reports about plays that have taken place
  • The --recordinglist parameter produces reports about recordings that exist
  • Both reporting parameters can direct their output to screen or to pipe-delimited text files
  • Both accept filtering/search criteria qualifiers. The --report parameter can be modified by --reportdays to alter the amount of data in it; or by --reportsort to change the way the data in a report is sorted. The --recordinglist parameter can be used with the --composername parametet to restrict the data it contains to recordings attributed to the specifically-named composer. The --composername parameter is case-insensitive and will be wild-carded
  • You can --report with or without --reportsort and --reportdays; you can also --recordinglist with or without --composername. But you cannot use --report and --recordinglist simultaneously: if you do, you'll get the output from the --report parameter, and the fact you used the --recordinglist parameter will be completely ignored
  • The --stats parameter produces an on-screen-only summary of the state of both the recordings and plays tables. It cannot be used with either of the --report or --recordlinglist parameters. It cannot be exported to a text file on disk. You cannot modify or affect the stats report's contents in any way

Bear in mind that whilst the runtime parameters discussed here are Giocoso's built-in tools for querying the data it stores in its database, that database is a fully-fledged Sqlite3 relational database. It is therefore perfectly possible to query, report on and analyse the contents of the Giocoso database using any number of third-party tools that knows how to query sqlite3 databases. A good one that is relatively easy to install and use is the DB Browser for SQLite, which is available for Windows, Mac and Linux equally well.

[Back to Front Page]|[Back to Parameter Front Page]