Giocoso Version 3: Upgrading from Version 2

If you've been a long-time user of Giocoso Version 2, you'll probably have an existing music database whose contents (and, specifically, whose play history) is important to you. You should therefore be aware of what installing Giocoso Version 3 will do to it (if anything) -and whether there is any risk of data corruption or loss from it.

The short answer to those sorts of concerns are: no, there should be no data loss but Giocoso Version 3 will attempt to automatically convert your Version 2 database into a form that Version 3 can work with. The structural modifications are relatively minor, however, so the risk from performing them are pretty small. One particular conversion step can, very rately, go wrong, however: so it's useful to know ahead of time what that issue is and what you might be able to do about it, should you be affected.

In this article, then, I want to document an installation of Giocoso Version 2 on Ubuntu 23.10 and how I went about upgrading to Giocoso Version 3 on it. So that we know whether or not the upgrade process causes me to lose data, I suppose we had better start by working out how much data I've got in the Version 2 database:

You'll note that this database (called 'main', though the screen doesn't show this fact) has quite a large music library but only a modest number of previous 'plays': the key numbers are 15,386 recordings, of which 27 have been played.

Now, before we plunge into the upgrade waters, it's probably wise to understand that that whilst Giocoso Version 3 tries very hard to make sure that nothing bad happens to your data, it cannot be, and is not, foolproof. Accordingly, I would start off by urging you to take a backup copy of your existing Version 2 database and keep it safe, away from your PC (stick it on a USB thumb drive, for example, so nothing can easily touch it). Here's me doing that:

The command being issued in the terminal is just tar -cf <name-of-outputfile> $HOME/.local/share/giocoso, so that the entire Giocoso 2 folder gets wrapped up inside the .tar file you see appearing in my $HOME folder in the file manager on the right. Call this the precautionary backup: and make sure you take one equivalently! (By the way, the dot in the .local folder name means that folder is hidden. You'll need to Ctrl+H to see hidden files -and that's true if you try to explore or extract the tar file after it's been created, too... otherwise you'll think the archive contains nothing, when it in fact does!

With the precautionary backup done, let me now download and install Giocoso Version 3:

bash gioinst

...which results, after much software downloading, in this:

The 'Fetching components, checking the status and comparing the checksum...' messages are standard Version 3 outputs that all users would receive. The 'Restoring and converting...' message is unique to those upgrading from Version 2, however: and the fact that there's a green tick next to the message is a good sign, too!

I'll explore what that 'restore and convert' process has done in just a moment. For now, let me launch Giocoso Version 3 and see if I can play music with it, without doing anything further:

Oh! Apparently not! What has gone wrong here? Well, nothing really. Giocoso Version 3 has just been installed and it's default database is called 'music'. It happens that my Version 2 database is called 'main', which is obviously not the same database name. All Version 3 is saying, 'you haven't configured me to open anything other than something called 'music' and you haven't even created a database of that name, either!'. So let's just correct that, by taking Administration menu, Option 3:

And here's the first indication that, actually, everything is perfectly fine: the Giocoso Version 3 database selection tool is aware of the existence of a database called 'main' -which must mean that the Version 2 database has been 'ported' across from the Version 2 installation, because this form only lists databases that are housed in $HOME/.local/share/giocoso3/db, a completely new folder that wouldn't have existed back in Version 2 days. Anyway: I've selected the database (by pressing the Space Bar), so I can just press [Enter] through the rest of the Configuration File Editor screens:

...and now the top of the Giocoso Version 3 main program window is reporting that it is '...using database Main', which sounds promising. Does music playback work now?

It certainly does! So the 'restore and convert' process seems to actually yield workable results, so long as you remember to configure your new Giocoso Version 3 installation to open the correct database on startup first. One final thing to check, therefore:

....when I run the Reporting menu Option 1, Giocoso Version 3's aggregate statistics report (which now does clearly mention the database name in use!) shows 15,286 recordings of which 27 have been played. Those are precisely the figures that Version 2 had before the upgrade: the upgrade has therefore preserved our play history perfectly.

The Details

So what, precisely, does the Giocoso Version 3 installer do when it 'restores and converts' a Version 2 database? Well, out of an abundance of caution, the first thing it does is take a backup (or at least tries to: it might fail to do so for all sorts of reasons, so taking your own precautionary backup before things start remains a good idea!):

The $HOME/.local/share/giocoso-backup folder you see highlighted in that screenshot is a one-for-one copy of the giocoso folder, taken by the Giocoso Version 3 installer. It's there to fall back to, should anything bad happen!

The installer then creates an entirely new giocoso3 folder for the new version of the program, within which it creates all sorts of sub-folders:

The db folder is where all Giocoso Version 3 databases must reside, so when all the Version 3 software and files have been downloaded (populating the bin, art and txt sub-folders) the installer copies all *.db and related files it finds in the giocoso folder into the new db sub-folder. Note I say 'copies': it doesn't move anything, so the original database and its associated files are left intact and entirely undisturbed in the original giocoso folder. We therefore end up in this state:

The database file has come across, along with its ".walmode" companion (it's there for technical, database-integrity issues which I won't go into here).

So, if you take stock: your Version 2 database is still there in ../giocoso. A copy of it is also present in ../giocoso-backup. And another copy of it is hopefully available wherever you wrote your 'precautionary backup': there ought to be no way a Version 3 installation can lose, damage, destroy or otherwise irritate your Version 2 database, therefore! If you find you can't stand using Giocoso Version 3, you could simply delete the giocoso3 folder and your Version 2 database is still there, where it has always been, ready for use. On the other hand, a week or so after you find that Giocoso Version 3 is the greatest thing since sliced bread, you may want to go round your file system clearing away the un-numbered giocoso folder and its giocoso-backup equivalent, for the sake of some house-keeping (your precautionary backup can be deleted at that point too, of course).

But something else changed during the Giocoso Version 3 installation that you may need to be aware of:

In this screenshot, I've changed to the /usr/bin folder and issued an ls -l giocos* command. This lists anything within /usr/bin that is vaguely related to Giocoso! You will note that there is a file called, one called and one in blue called plain giocoso, which is shown as 'pointing to' the /usr/bin/ file.

  • The file is the original Version 2 executable for Giocoso: I didn't put a version number into that executable's name back then, because I lacked the necessary foresight to do so! You can tell it's the Version 2 executable because of the April 22nd 2023 date associated with it.
  • The file is the new Version 3 executable, freshly installed.
  • The plain giocoso file is a symbolic link (sort of like a Windows' shortcut), which allows you to type 'giocoso' and still have the operating system realise you mean to invoke the file. It's a convenience provided so that you can launch the program by typing one word, rather than having to remember to add in a version number and a '.sh' file extension.

The point here, therefore, is that the old Version 2 of Giocoso can still be run, if you really wanted to, by typing the command (in full): If you just type giocoso, then Version 3 of the program will launch. And if you typed, that would also launch Version 3 of the program. The two versions of the program continue to co-exist after the Version 3 installation, in other words, and either or both versions will run entirely independently of the other:

I don't recommend doing this, though. The point is more that, having just installed Giocoso Version 3, you might hate it... and you can therefore just delete the file in /usr/bin, which will leave you able to run the old Version 2 of the program just fine. Once you are committed to using Version 3, though, I'd be deleting the old file in /usr/bin to avoid all possibility of confusion.

Be aware that if you do run the two versions in 'parallel running' mode for a while, plays made in one version will not be recorded in the database used by the other version. Giocoso Version 2 stores its database in a completely different location from Version 3 and they do not even know of the other's existence, let alone cooperate in sharing data between themselves!

Database Specifics & Diagnostics

The Giocoso Version 3 installer makes small amendments to the internal structures of the Version 2 database when it copies across into the new version's folder structure. The specific alterations are:

  • Add not null constraints to the COMPOSER and COMPOSITION columns in the PLAYS table
  • Adds a unique index on the PLAYDATE,COMPOSER,COMPOSITION column combo in the PLAYS table

The first of these changes could alter the number of records in the resulting Version 3 PLAYS table: if the Version 2 play history contained recordings with blank composer or composition tags, for example, then those would be lost during the conversion process. Arguably, that would be no great loss, since a database record that says, 'Howard listened to, er, something ...but I can't tell you what' is about as much use as a chocolate teapot. Anyway: the point is that a converted Version 2-to-Version 3 database might lose some data during the conversion, but the data lost will not have had meaningful content in the first place.

The second change (the addition of a unique index) is actually the more challenging of the two alterations. It might, after all, fail if, for some reason, your play history contained two data records claiming you listened to the same thing by the same composer at exactly the same time. Not only would that be a practical impossibility, but it's also vanishingly unlikely that a Giocoso Version 2 database would duplicate data in that way. But... just be aware that a failure during the Giocoso Version 3 installation could be displayed at this point, because of the addition of a constraint on allowed data that was not previously present.

It's actually rather important that this new unique constraint is in-place on the converted database, so you should check it, post-installation, by opening a new terminal session and typing the commands:

cd $HOME/.local/share/giocoso3/db
sqlite3 main.db -cmd ".indexes plays" .exit

I'm obviously saying 'main.db' in there, because that's the name of my converted database: you type in whatever.db is actually your database name. That's true for all the commands that follow, too. Anyway, here's what happens to my database when I issue the command:

I'm seeing a response of idx_uq_plays -and since that's the name of the index the installer attempted to create, then you now know there's no duplicate data in your PLAYS table and the index was indeed created correctly.

However, if no index name is returned at this point, then it's a sure-fire sign that its creation at installation time failed because of the presence of duplicate data in the PLAYS table. That needs fixing before playing anything new with Giocoso Version 3. Unfortunately, the 'fix' involves the deletion of the duplicate data (which therefore involves data loss) and rather a lot of complicated typing in a terminal! If you're up for it, therefore, the start of the fix is to type these commands:

cd $HOME/.local/share/giocoso3/db
sqlite3 main.db -cmd "select * from plays where id in (select id from (select id, count(*) from plays group by playdate, composer, composition having count(*) > 1))" .exit

That will list (and not delete, so it's safe) anything that's duplicating in the PLAYS table:

The output is not nice to read, but I can see I have two duplicate records in this database and that one involves a Romance by Finzi and the other involves some Consort music by Lawes. If I had 2000 rows output at this point, I think I'd pause and consider my life-choices hitherto... because that would be a lot of data just to wipe out. But in this example, I think I can reasonably lose these two 'plays' (though actually at least 4 records will be deleted, because of course each record shown represents at least 2 distinct data items in the PLAYS table). The fix that actually deletes data is therefore now this command:

sqlite3 main.db -cmd "delete from plays where id in (select id from (select id, count(*) from plays group by playdate, composer, composition having count(*) > 1))" .exit

This time, the 'delete' command will actually eradicate the duplicates -and a re-select using the same select statement as before should return nothing:

...which is exactly what you see happening here. If the repeated 'select' returns nothing, that means the dodgy data has gone, so all that's left to do is create the index that the installer would have liked to create for you in the first place:

sqlite3 main.db -cmd "create unique index idx_uq_plays on plays (playdate, composer, composition)" .exit

...which should return nothing. But now you can issue the sqlite3 main.db -cmd ".indexes plays" .exit query again and this time it will return the name of the required index:

It happens that I (deliberately!) created the index with a 'idz' rather than an 'idx' name on this occasion, firstly to show you that I did indeed create a brand new index successfully using these commands... and secondly, to show you that the name of the index is really irrelevant. So long as the unique index exists on the combination of those three columns, we're in business.

I hasten to add: you are incredibly unlikely to need to fiddle around in the way shown. Giocoso Version 2 simply didn't go around duplicating data at random, so your original PLAYS table should be 'clean' from that perspective -though it does also rather depend on how good you are at tagging your music files properly, about which there is always some wiggle-room!

[ User Manual Home ]