Giocoso: The Database Management Menu

Giocoso always plays music direct from your hard disk -but it only knows what music exists where because of entries stored within a music database that you must create and maintain. A music database is a relational database that is accessed using the sqlite3 program: that's a technical detail which generally no user should care about, since Giocoso does all the talking to sqlite3 for you, in response to menu options you take and input you supply via Giocoso dialog forms. It does mean, however, that a music database will always consist of two fundamental tables (think of them as giant spreadsheets, if you prefer): one called recordings, which lists every single folder in which one or more FLAC files has been detected; and one called plays which stores details of every single folder that Giocoso has played through to completion.

The recordings table is populated at the time of database creation. It therefore represents a 'snapshot' of the state of your hard disk at that specific moment. If you add new recordings to your music collection, you'll need to tell Giocoso to refresh its recordings table. Giocoso refers to this as 'refreshing the database' and it can be done in a 'fast' or 'full' mode. Fast refreshes just go out looking for new FLAC files that weren't there the last time the database was refreshed. A full refresh re-examines every single FLAC file and re-populates the entire recordings table from scratch. The ability to do a full refresh means that the recordings table is 'disposable' -because it can always be reconstructed by doing a fresh scan of your hard disks.

The plays table is only ever added to when a 'play' of music concludes. Giocoso reads the metadata tags inside the FLAC files it plays to determine the composer of the recording, the performing artists, the genre of the piece and so on: all these details are then written into the plays table. The plays table therefore becomes a comprehensive history of your listening habits over time: it's a history that cannot be replaced or reconstructed, so Giocoso makes sure never to delete or wipe the plays table. It then also provides you with menu options to make backing the database easy (so its precious, irreplaceable play history is protected against inadvertent misadventure).

The Giocoso Database Management Menu provides three menu options for creating a database and maintaining or refreshing its recordings table, along with three options that allow you to backup, import into or improve the performance of any given database. A final option allows you to switch between multiple databases at will (you may, for example, have a database of classical music and a separate database of jazz or popular music, and wish to be able to play each type of music at will in the same program).


1. Create a Music Database

This menu option will first prompt you for a name to give the database you're about to create and then will ask you to specify the 'root' of your music collection folders on disk. All sub-folders of that 'root' folder are then searched to discover FLAC files: the scanning process can therefore take quite a long time to complete, if your music collection is large. (For reference, on a 10-year old PC, and with a digital music collection of about 15,000 recordings ripped from 6000 or so CDs, my database scans take about an hour to complete).

On first taking this option, therefore, you'll see this:

You are asked to enter a database name: this can be anything you like, as long as you like and (as you see here) can contain spaces in its name. The only restriction on the names you're allowed to propose are that they must be 'file system safe': the database name becomes the name of the database file stored on disk, so if the name contains 'illegal' characters, they will be stripped out and replaced with the underscore character. Spaces will also be replaced with underscores, and a '.db' extension will be added to the supplied name automatically. The above example will, in other words, become a file called main_classical.db, stored in the $HOME/.local/share/giocoso3/db folder. Had you supplied a name of, say, Music! Good?, that would become the physical file Music__Good_.db. Though Giocoso will therefore 'cope' with weird characters and spaces, I'd recommend you stick to vanilla, single-word database names!

You can press ESC (twice) or TAB round to the Cancel button (and then press Enter) to terminate the database creation process early, should you need to do so. A terminated database creation process results in no database file being created on disk at all.

Once you press [Enter] to submit your chosen database name, you'll then be asked to point to the 'root' of your music collection:

The folder selection dialog in the upper part of the screen can be navigated through using the up- and down-arrow cursor keys. It always starts at the '/' root folder of your file system. Press the Space Bar to select a particular folder once it's highlighted. Select and press the Space Bar on the '..' item to go 'back' up a folder, should you need to. As you select and move through folders and sub-folders, the second panel in dialog will 'fill up' with a text representation of the full path currently selected. For example:

As I've stepped through my folder structure, the path "/sourcedata/music/classical" has been created for me. If it's quicker and easier, you can TAB down to that second panel and directly type the folder name yourself: the effect is the same for Giocoso, however you choose to do it.

A reminder that the 'root of your music collection' means the top-most folder, beneath which your digital music files can be found. In my case, for example, you can see that within /sourcedata/music/classical, I have a lot of sub-folders that start with names such as 'A', 'B' and so on. You can see it more clearly, perhaps, when it's displayed in my usual file manager:

Within the 'A' folder, for example, I have sub-folders representing multiple different composers, such as 'Aaron Copland' and 'Aarre Merikanto'. Within each of those sub-folders, I have a bunch of more sub-folders where each sub-folder represents a single genre, such as 'opera' or 'concerto'. Within each of those, I have sub-folders representing a specific recording of a work of that genre. It's only within these recording-specific files that you'd actually see any FLAC files... but I stop navigating through the folder structure in Giocoso way back at the /sourcedata/music/classical folder: that's the point 'beneath' which all my FLAC files exist. Were I to descend to an 'A' folder, I'd never add Benjamin Britten's music to my Giocoso database. If I descended to an 'Aaron Copland' folder, I'd never add anything written by Arvo Pärt to my database, and so on.

In other words, you want to point to the folder that is at the 'apex' or top of your physical music folder structure, the one beneath which all appropriate FLAC files can be found.

Giocoso will search 'downwards' beneath the specified folder for as many sub-folder levels as it takes: there's no limit enforced in that regard.

All that said, you can always add files to a database by performing subsequent refreshes. If I'd chosen /sourcedata/music/classical/A as my initial 'root' folder, I could always do a subsequent Fast Refresh (Database Management menu Option 2) and specify to add files in /sourcedata/music/classical/B, for example. If you want to build up a large database in small chunks, this is a valid way to do it!

Once you submit a music folder to Giocoso (by pressing Enter), Giocoso will run off to it and search for FLACs within it. If it determines that there are no FLACs to be found anywhere within that folder structure, it will warn you of this fact and then error out:

Pressing Enter at this point will return you to the earlier folder-selection screen, where you can try again to specify an appropriate folder -or just press ESC to cancel out of the whole process.

If Giocoso determines that there are FLAC files to analyse, it will begin to do so:

You can see each folder being visited in the text display at the bottom of the screen: folders are visited in alphabetical order, so the names displayed here will give you a clue as to how much progress has been made to date. The progress bar will also display a 'percentage complete' bar, though it's not especially accurate as far as time goes. It shows the percentage of total folders analysed, but it cannot know ahead of time whether a folder contains a single FLAC or 199 FLACs: analysing 199 files is obviously going to take a lot longer than analysing 1, but either counts as a single folder processed as far as the percentage bar is concerned. Incidentally, the percentage bar also only goes up in 2% increments, so multiple folders will flash by on the bottom before even the first indication of percentage progress is displayed.

Give it time, though, and progress will be evident on either measure:

What is being collected during this scanning-and-analyzing process? Well: for each folder visited, Giocoso looks at the first FLAC found and extracts the tagging data from it, assuming it to be representative of all the other tags stored in any other FLACs found in the same folder. For that first file, Giocoso extracts the ARTIST, ALBUM, GENRE, COMMENT and PERFORMER tags. It also computes a sum total duration (in seconds) for all the FLACs in the folder.

As per this website's general 'philosophy' of tagging, the ARTIST tag is stored as the composer of the work; the ALBUM tag is regarded as being the recording's extended composition name, the COMMENT tag lists all the performers on a recording, whilst the PERFORMER tag stores the name of the 'distinguishing artist': the person (usually the conductor) that makes this recording of a composition unique and distinct from that recording of the same composition.

The collected data is then inserted into the recordings table mentioned previously:

You won't normally see the contents of the recordings table, but various tools can expose it for inspection (the above screenshot shows the DB Browser for SQLite at work). You can also see something of the table's structure by taking the Reporting Menu option 6.

The contents of the recordings table, once collected, will be used by Giocoso to select music recordings to play using any of the Play menu Options 1, 2 or 4. A recording you've ripped but not somehow added to this table cannot be selected for play (though you could force it to be played by using Play menu Options 3 or 5). To add new recordings to the table after the initial database creation process has finished, you'll need to use Database Management options 2 or 3, to 'refresh' the database.


2. Fast Scan for New Recordings

When thinking about how to keep a freshly-created Giocoso recordings table up-to-date, there are two possible approaches:

  • Scrap it in its entirety, go back out to the hard disk and re-scan everything from scratch
  • Assume that what's there is probably OK, so just go out to the hard disk and see if anything new should be added

The Database Management menu Option 2 takes the second approach. It will detect new files that have been added to your music catalogue, but it won't alter anything at all about anything already in the recordings table. Since it is not re-analysing all the tags associated with the thousands of recordings that already exist, and only concerning itself with the tags associated with the handful of new recordings added to the collection, the Fast Scan option is relatively quick.

Taking Option 2 for the first time, you are asked to select a database to refresh:

The form lists all files with a '.db' extension that are housed within the $HOME/.local/share/giocoso3/db folder. Just use the up- and down-arrow cursor keys to highlight an appropriate entry, then press the Space Bar to add an asterisk next to it its name, as you see I've done for the 'main' database in the above screenshot. Then you can press [Enter] to submit the selection, or TAB around to the OK button and press Enter there. You are then asked to point to the root folder of your music collection, just as you did when you first created the database. The 'root folder' is the last one below which all your FLACs can be found:

You navigate to the correct folder by using the up- and down-arrow cursor keys in the upper panel to highlight a folder name, then pressing Space Bar to 'enter' it. As you do so, the full path you've selected builds up in the second panel on the form. When the correct folder has been reached, you just press [Enter] to submit it. If you prefer, you can just TAB into that second panel and simply type out the full path in the first place: the upper panel will change as the various levels in the full path are typed. Once you submit the folder to Giocoso (by pressing Enter), the program begins scanning the relevant folders:

The program at this point is creating a list of every FLAC that exists in the folder structure. It then compares that list to the recordings table contents it already knows about. The difference between the two must represent 'new' files that exist on disk that aren't in the existing recordings table: it's these files which, in a second stage of processing, Giocoso will go out and analyse in detail and add to the recordings table. Since the numbers of new files compared to existing ones is always likely to be small, that second stage of analysing won't last long and is therefore difficult to screen-capture! But it should look something like this:

Be aware that the Fast Scan only ever adds to the recordings table; it never causes an existing recording to be removed from it. That has interesting implications!

Suppose one day you notice that you have stored a recording in a folder called "Peter Griimes" -there's an extra 'i' in the name that shouldn't be there. Keen to be correct in your cataloguing, you rename the folder so that it correctly says 'Peter Grimes'. If you now do a Fast Scan in Giocoso, Giocoso will notice that a new recording called 'Peter Grimes' exists, and will therefore scan it and add it to the recordings table. However, of course, this now means the recordings table knows of both 'Peter Griimes' and 'Peter Grimes'. What's more, if Giocoso should randomly select to play 'Peter Griimes', it will think it's a valid thing to play, right up until the point where it discovers that folder no longer exists... so it will error at that point.

Also note that it is physical changes to things which trigger the Fast Scan to consider something to be 'new'. If, for example, you were to merely re-tag the ALBUM or ARTIST tags for a recording that's already in the recordings table, the Fast Scan will not notice that anything new has happened and the revised metadata will not, therefore, be added to the database. Only a Full Scan picks up revised metadata like that.

In short, Fast Scans can leave your recordings table in a bit of a messy state, in that they neither clear out old recordings which no longer physically exist on disk, nor update metadata associated with recordings. However, they are useful as relatively quick ways of making Giocoso aware of genuinely new recordings you have recently (and physically) added to your collection.


3. Full Scan All Recordings

The Giocoso Full Scan basically wipes the existing recordings table and re-computes it from scratch by looking afresh at every recording found within the folder structure you point it towards. Functionally, it works exactly as the Fast Scan (Database Management menu Option 2) does, so I won't describe the various screens in too much detail here. A screen first prompts you to pick a database to work on:


You arrow up-and-down until the right one is highlighted, then press the Space Bar to put an asterisk next to it. Then you press [Enter] to submit your choice to Giocoso. After that, you're asked to select (or type) the full path to the 'root' folder of your music collection -the one under which all FLAC files of interest can be found:

Giocoso then scans the file system for details of every FLAC that exists. It then starts visiting each one in turn, extracting its metadata tags, and adding them to a recordings table that has previously been wiped of all its existing content:

Since this part of the process involves reading and extracting data from every FLAC in your collection, it will take quite a long time to complete, if your music collection is quite large. Mine consists of, as the screenshot shows, around 15,300 folders, containing rips of over 6000 CDs: it takes my 10 year-old i5 PC about an hour to process that entire thing. Faster PCs are available!!

At the conclusion of the scan, you'll see this confirmation message:

Press [Enter] to dismiss that message and you'll be returned to the Giocoso main menu. The recordings table will have been re-populated ab initio and will thus be able to be used to generate randomised music plays, using options 1, 2 or 4 from the Play Music menu.

For any SQL gurus out there: be aware that by erasing the original recordings table and re-populating it from scratch, the unique record IDs associated with any given recording will almost certainly change after a full rebuild. The record ID is not, therefore, to be relied on as a consistent, invariant way of identifying particular recordings.


4. Select and use a different database

Giocoso always opens ready to use whatever database is listed in the persistent configuration file ($HOME/.local/share/giocoso3/txt/giocoso.conf):

The SOURCEDB parameter you see highlighted here is the 'default' database that Giocoso will automatically open every time it's launched. The default 'default' database is called 'music', but can be set to anything you like, once you've actually created a database. You'll see the name of the database that is in use in the header area of the main Giocoso program display:

In this case, a database called 'main' has been selected because of the setting in the configuration file, though the display init-capitalises that so that it's shown as 'Main'. This means that all requests to play music will use the recordings table in the 'main' database to determine what music files exist and are capable of being played.

There is no limit to the number of databases you may want to create with Giocoso, however: you might want separate databases for choral and orchestral music, for example; or for classical versus jazz music. If you create multiple databases (using Database Management menu Option 1), you may well want to switch between them, allowing different recordings tables to initiate playback of quite distinct and separate types of music. This is what the Database Management menu Option 4 allows you to do:

The form lists all databases that exist in the $HOME/.local/share/giocoso3/db folder, with the '.db' extension. Your job is to simply use the up- and down-arrow cursor keys to highlight one of the databases listed. That puts an asterisk in the brackets next to the chosen database. Pressing [Enter] at this point will make the selected database the 'live' one in use by the rest of this Giocoso session. A confirmation messasge will be displayed:

Press [Enter] to dismiss that, and then take note of what the main program's header now says:

The newly-chosen database will be used for all future plays until either (a) you re-use Database Management menu Option 4 to select another database; or (b) you quit Giocoso completely and re-run it from scratch, at which point the SOURCEDB set in the persistent configuration file will be the in-use database once more. All other aspects of Giocoso will similarly change to be sourced from the newly-chosen database: for example, the Aggregate Statistics report available from the Reporting menu Option 1 will only report play and recording statistics of the in-use database.



5. Import Plays into a Database

The plays table of a Giocoso database is a precious thing: it stores your entire music listening history and determines what is considered an 'unplayed' composer or work. If it's lost or damaged, it cannot be re-constructed. If you therefore re-install Giocoso on a new PC, or re-install your operating system, you will want to have backed up the existing plays table (see Database Management menu Option 6) -and, in due course, you'll want to restore it back into the fresh Giocoso system. This 'restoration' process is what Database Management menu Option 5 allows. You create a new, blank database with zero (or few, insignificant) plays and then point this option at the backed up database. Giocoso then pulls the play history out of the one into the other.

For example, here is my 'hjr' database play history, as revealed by the Reporting menu Option 1:

From statistic number 6, you'll note that zero records have been played out of this database. Database 'hjr' therefore has a completely empty plays table.

Meanwhile, here are the same statistics for my 'main' database:

Here, then, we see that 14,471 recordings have been played. So now let me take Database Management option 5 for the first time:

You'll note that you are about to be asked to specify the source and target databases: it doesn't matter which database is in use at the time. If I'd used Database Management menu Option 4 to set 'hjr' as the in-use database, that would be irrelevant for what's about to happen.

Here I've used a standard database selection screen to highlight and select the 'main.db' database (note that the form only lists databases that are housed within the $HOME/.local/share/giocoso3/db folder. If you're restoring from a backup, you need to make sure your source database is copied to that location first). Press the Space Bar when the right database is highlighted, to put an asterisk next to its file name. Press [Enter] to confirm your selection:

You are now told you're about to choose a 'target' database, into which the plays table from the source database will be loaded. Again, notice that you are prompted to specify the target database: it makes no difference what database happens to be the already-in-use one:

As before, a fairly standard database selection screen allows you to highlight, then select (with a press of the Space Bar) the name of the database that's going to receive the plays table. Press [Enter] to submit the selection. You'll receive a confirmation message as to what's about to happen:

You have to answer 'yes' here (by arrowing onto the 'Yes' button and pressing [Enter]) for the import to proceed -and you'll notice that 'yes' is not the default option, so to select it requires deliberate effort on your part! If you press [Enter] whilst the 'no' option is highlighted, or if you press ESC to cancel out of the whole affair, no import will take place, and you'll just return to the Giocoso main menu, after you are told that nothing has been altered in either selected database:

If you do choose to proceed, however, then you'll soon see something like this:

If you then re-run the Aggregate Statistics report (first ensuring that the target database is the in-use database, and using Database Management menu Option 4 to set it to be the in-use database if not), you should see that the database has now acquired the play records that previously belonged to the source database:

Here you see database 'hjr' now knows about 14,471 plays, where previously it knew of none: that's the same number the 'main' database knew about, so that's proof that main's play table contents have been pulled into hjr's equivalent.

Note that an import never removes records from the source database: in my example above, database 'main' still has 14,471 play records after the import to 'hjr', in other words. It's also true that the import never deletes play records from the target database, either. That is, if database 'hjr' had already recorded (say) 2,000 plays, then after the import it would be showing a play count of 16,471: the 14,471 records coming across from 'main' don't cause the existing contents of hjr's play table to be wiped, but are added to them.


6. Backup a Database

The Giocoso plays table is never wiped or cleared, because it stores the history of all the recordings you've ever listened to and is thus considered rather important! It's also completely irreplaceable: if it were to become corrupt or be accidentally lost because your hard disk failed, it would be impossible to re-create it from scratch. In this respect, it's completely different in nature from the recordings table, which can always be reconstructed by re-scanning for FLAC files on your hard disk.

In order to protect the plays table, therefore, it's important it is regularly and frequently backed up. If the 'original' is ever lost, you can then create a new, blank database and use the Database Management menu, Option 5 to import the plays into the new database from the backup.

You could backup the Giocoso database in its entirety by copying the relevant .db files stored in the $HOME/.local/share/giocoso3/db folder: that will work perfectly well.

The Database Management menu Option 6 gives you an in-program way of achieving the same thing, which may be more convenient to use for some people.

Taking that option causes the usual 'select a database screen' to appear:

Using the up- and down-arrow cursor keys, you highlight the database you want to back up (you're only allowed to do one database at a time!) Once the required database is highlighted, press the Space Bar to mark the database name with an asterisk. You then press [Enter] to submit the choice.

Almost immediately, you'll see this message appear:


Giocoso databases, even for very large music collections, are really rather small: a matter of a few tens of Megabytes at most. So the backup process is usually extremely quick, and simply involves copying the relevant .db database file out of $HOME/.local/share/giocoso3/db to another folder somewhere on your hard disk. The precise location to which backups are written is given by whatever the BACKUP_DIR parameter is set to in the persistent configuration file. Should that parameter not be set, or if it's set to a folder that doesn't actually exist for some reason, then the default location for backups is $HOME/Desktop.

Backups are created as tar files, with names of the form: databasename-backup-date-hours-minutes-seconds.tar. For example, if I was backing up my database called 'main', I might end up with a file called : main-backup-2023-09-25-17-02-38.tar. Multiple backups of the database can be performed, therefore, without the new one over-writing the old one (since the minutes/seconds part of the filename will be unique every time).

The tar file created by a backup will contain the relevant .db file, inside the complete folder structure from which it came:

You can see here how the 'main.db' file is included within the tar inside it's /home/hjr/.local/share/giocoso/db folder structure. Bear this in mind should you ever want to unpack the archive.

Backups are the sort of thing you want to be doing routinely and automatically, which generally requires scripting and scheduling them via something like crontab. For that reason, Giocoso permits itself to be launched with the --backup runtime parameter:

Here, for example, you see the crontab on my music player: it executes the command --backup every night at 10pm, performing without any prompting the exact thing that Database Management menu Option 6 would do. The fact that there's no prompting, however, means that there's no opportunity to tell Giocoso which database to back up: that's what the SOURCEDB parameter in the persistent configuration is for. As with performing an interactive backup, though, the BACKUP_DIR parameter in the persistent configuration file governs where the backup is written to.

Remember: backups of the Giocoso database is the only way to preserve the plays table against mishap and error, so it is recommended you take frequent backups either interactively via Database Managememnt menu Option 6 or non-interactively via crontab scheduling. Daily backups would be sensible, I think.


7. Optimise a Database

Giocoso databases are usually very small (a few tens of Megabytes at worst) and therefore there is seldom a need to worry about how fast they are read or their data is processed. However, databases do tend to 'degrade' over time: repeated inserts and deletes and updates can leave pockets of 'empty space' within the database file which, while not very big, nevertheless have to be waded through before actual data is reached.

Accordingly, you may want to periodically 'optimise' your database. This triggers what's known as a 'vaccuum' operation on the database, where all data is compacted and empty space is freed up, making everything 'tighter' than before and thus slightly more performant. The Giocoso Database Management menu Option 7 also triggers a rebuild of indexes on, and the re-computation of internal statistics about, the recordings and plays tables. All of this may help make an unusually large database a little smaller and a little bit quicker to access than before -but I wouldn't hold your breath waiting to notice the change!

The option triggers the usual database selection form: you can only optimise one database at a time.

Once a database has been selected, the optimisation process proceeds immediately -and a confirmation message is then displayed.

I don't recommend you optimise your database at all, and certainly not frequently: the gains are likely to be negligible, and the risks associated with compacting a database are not zero. The option is provided for edge-cases where it might make a performance difference, but it's highly unlikely that your music database qualifies as such an edge case (mine doesn't, and I consider it relatively large).