Giocoso 3 – The Reporting Menu

Giocoso stores data about what recordings exist in a table called RECORDINGS. It also stores details about what recordings have been played in a table called PLAYS. These two tables can be queried to generate reports. Production of any report is entirely non-destructive: reports show you what data exists, but cannot themselves delete or alter that data. Therefore, they are perfectly safe to experiment with -and I accordingly don't propose to go through each reporting menu option in detail. Try them out, see if the data is of use to you and if not, move on!

Here, then, I'll mention some specific features of all reports, before describing Option 4 (the 'advanced reporting' option) in some detail.

General Report Behaviour

All reports from Giocoso are displayed via the less program, which means navigating around the report is a matter of mastering the 'less' commands. Basic navigation upwards and downwards through a report is achieved by using the up- and down-arrow cursor keys. Those move you through the report one line at a time in the appropriate direction. You can also use the PgUp and PgDn keys to scroll through a report one page at a time in either direction.

To close a report, you just tap the letter q at any time.

To search within a report, tap the forward-slash key (i.e., /) and start typing the text you're looking for. For example, if I was wondering when the last time I played Britten's War Requiem, I'd run the Reporting menu Option 6 ('Complete list of plays'), tap forward-slash, type 'War Requiem' (the search term is case-sensitive, so the use of capital letters is important) and see this:

...and you'll note that the search term has been highlighted -and I can tell I last listened to the work on August 30th 2023. You can then tap the letter n to jump to the next occurrence of the search term (and N to get to the previous one -that's capital N or lower-case n, depending on your desired direction of travel).

It's not especially intuitive, but this use of lower/upper case letters to do opposite-direction things is quite common in less. For example: having opened a new report, if you want to jump to the end of it and see the last few lines, tap the letter G. To then jump to the top of the report, tap g.

Another trick you might want to try is to tap the letter v when viewing a report: this transfers the report to your system's default text editor directly, from where you can use standard commands to save the report to disk. So, for example, before I launch Giocoso, let's say I issue the command:

export EDITOR=kate

When I then run Giocoso, it runs perfectly normally and I can run Reporting menu Option 6 (which lists all plays) without drama: things still open in less, as usual:

You can also see that the report is displayed within a standard X terminal window, which is entirely normal. Now tap the letter 'v':

That's the same data, but this time being displayed within the Kate text editor -the one I set as my default before running Giocoso in the first place. Once the report's displayed in Kate (or any other text editor), I can use File -> Save As options to write out the report, anywhere I like, to disk. When I close Kate, I am returned to the original display-report-in-Xterm window, where I can tap q to quit and return to Giocoso's main menu in the normal way.

Advanced Reporting by SQL

The Reporting menu Option 5 presents a blank text box to you:

In this text box, you can type any valid SQL select statement. Keywords such as 'update', 'insert' and 'delete' are stripped from any input you provide, so this cannot be used to modify the contents of the Giocoso database, but if you know how to write a SQL select statement, this window lets you submit it and have its results returned to you.

This is not the place to teach you how to write correct SQL, of course, but the simplest construction possible is always in the form of: select something from somewhere. The 'something' is a comma-separated list of column names, and the 'somewhere' is the name of a table. So, for example: select composition from recordings which yields this result:

...at which point you notice that it might be useful to know who composed each recording:

...and so a modified version of the query, with the extra column inserted into the 'something' part of the query can be re-run, with the modified results displayed.

You maybe look at that and think 'the output appears to be in composer-name order, but I prefered the original 'composition-name' order. So, you need to add an instruction to order by something:

...and now the rows of the report list compositions that start with punctuation, before those that start with a number, before those that start with standard letters.

Of course, queries can also include aggregation and grouping. So, for example, it's possible to look at all the RECORDINGS, group by composer and sum the total duration of each composer's recordings, like so:

Note how complex queries can be broken up so that consist of multiple lines: Giocoso is happy to handle multi-line queries in this way, fortunately!

Database Schema Design

For the advanced reporting to be of much use to you, of course, you'll need to know the columns making up the tables in the Giocoso music database and their data types. Here they are, first for the RECORDINGS table:

sqlite> pragma table_info('recordings');
cid name         type     notnull dflt_value pk
--- ----------- ------- ------- ---------- --
0    id           INTEGER 1                    1
1    DIRNAME      TEXT     0                    0
2    COMPOSER     TEXT     0                    0
3    COMPOSITION TEXT     0                    0
4    GENRE        TEXT     0                    0
5    COMMENT      TEXT     0                    0
6    PERFORMER    TEXT     0                    0
7    DURATION     numeric 0                    0

Here's the create table statement that was used to construct the table in the first place:

CREATE TABLE recordings (
    "id" integer not null,
    "DIRNAME" text,
    "COMPOSER" text,
    "COMPOSITION" text,
    "GENRE" text,
    "COMMENT" text,
    "PERFORMER" text,
    "DURATION" numeric,
    PRIMARY KEY ("id" AUTOINCREMENT)
);

CREATE INDEX recs2_composer on recordings (composer,dirname);
CREATE INDEX recs2_genre on recordings (genre,dirname);
CREATE INDEX recs2_comment on recordings (comment,dirname);
CREATE INDEX recs2_performer on recordings (performer,dirname);
CREATE INDEX recs2_duration on recordings (duration,dirname);
CREATE INDEX recs2_composition on recordings (composition,dirname);

And the same for the PLAYS table:

sqlite> pragma table_info('plays');
cid name         type     notnull dflt_value pk
--- ----------- ------- ------- ---------- --
0    id           INTEGER 1                    1
1    PLAYDATE     TEXT     0                    0
2    DIRNAME      TEXT     0                    0
3    COMPOSER     TEXT     1                    0
4    COMPOSITION TEXT     0                    0
5    GENRE        TEXT     0                    0
6    PERFORMER    TEXT     0                    0
7    DURATION     numeric 0                    0

And the create table statement for the PLAYS table:

CREATE TABLE IF NOT EXISTS "plays" (
    "id"    integer NOT NULL,
    "PLAYDATE"      text,
    "DIRNAME"       text,
    "COMPOSER"      text NOT NULL,
    "COMPOSITION"   text,
    "GENRE" text,
    "PERFORMER"     text,
    "DURATION"      numeric,
    PRIMARY KEY("id" AUTOINCREMENT)
);

CREATE INDEX plays_dirname_idx ON plays ( dirname);
CREATE UNIQUE INDEX idx_uq_plays on plays (playdate, composer, composition);

There are no inter-table foreign key relationships; the ID column is a synthetic primary key for each table and is thus meaningless. There is a uniqueness constraint on the time of a play, the play's composer and the play's composition name: it's there to stop the database mistakenly inserting the exact same play record into PLAYS twice.

Armed with all that, you should be able to knock some SQL queries together relatively easily!