Giocoso 3 – The Pro Menu : Initialise a remote database

1.0 Introduction

Initialising a remote database doesn't mean creating it or building the server it's running on: that's something you have to do as a separate exercise and for which I've provided documentation elsewhere!

When the Pro menu talks about initialising the remote database, therefore, it means:

Checking the local RECORDINGS and PLAYS table, to ensure that no existing data breaches the data lengths allowed in the remote database
Wipe any and all data in the remote GLOBAL_RECORDINGS and GLOBAL_PLAYS table
Connecting to the remote database
Coping the entire contents of its local PLAYS table contents to the remote database
Copying the entire contents of its local RECORDINGS table contents to the remote database
Creating a bunch of views (essentially, stored select statements) in the remote database, thereby enabling full Pro client functionality

Initialisation is therefore destructive (in that if the remote database already has some recording or play data, it's just wiped) and therefore really only ever ought to be performed once, preferably by the client device that you've declared to be 'a primary client' (in the Administration menu, Option 2).

On the other hand, it's a process that can be run as many times as you like: it's the remote database that gets destroyed and re-created, after all. The local PLAYS and RECORDINGS tables stay completely intact, with none of their data altered or deleted in any way. A second run of the initialisation process therefore merely re-creates the remote database, no harm really done. On the other, other hand: bear in mind that a working remote database may have plays data that comes from multiple devices. Running the initialisation process means the global_plays table will only have play data that comes from the client device doing the initialising.

But on the (I've lost count...) other, other, completely different hand: other client devices still have their play data intact and can therefore use the Pro menu, Option 2 to re-push that data to the re-initialised remote database... so, again, in the long run, initialising a database doesn't actually destroy anything irretrievably. It's still not something you want to do very often, however!

If you don't do it at least once, however, then nothing about Giocoso Pro works as intended. So, the moment you configure a remote MySQL database IP address in your Administration menu on your primary device, Option 2, the first thing you should do is run the Pro menu, Option 1. If you try to play music on a primary client without having first initialised the Pro database, for example, you'd see this error message:

Similarly, trying to run the Aggregate Statistics report (Reports menu, Option 1) would simply return these messy-looking results:

These are all symptoms of needing to initialise the remote Pro database!

2.0 Running the Initialisation Process

When you first take the Initialisation menu option, there will be a pause as the program checks the local database to ensure that it contains no data that wouldn't fit into the remote database. If there is found to be data that wouldn't fit, the initialisation process will warn you about what is wrong and will then exit without having initialised anything. It's up to you at that point to work out what data is 'bad' and needs fixing at source. Examples of bad data would be full paths to a FLAC that exceeds 512 characters, or a Composer name that exceeds 256 characters, or a composition name that is longer than 512 characters. The chances of legitimate composer or composition names actually breaching these limits is small, of course: that's why these generous limits were specified in the first place! However, if you've tagged your FLACs in ways this site doesn't approve of, you might (for example) have the conductor, orchestra and soloists entered into the ARTIST tag, which Giocoso will have read as the COMPOSER tag ...and then you might have issues.

If you've tagged according to this site's prescriptions, however, then you are likely to be fine and instead receive this message of encouragement:

You can back out at this stage, without anything having been done to the remote database: just tab round until the 'No' option is highlighted, then press [Enter]. If you press [Enter] when the 'Yes' option is highlighted, however, then this happens:

The initialisation process, as I mentioned in the introduction, is destructive to data in the Pro database -so, the initialisation wizard is very careful to prompt multiple times, letting you back out before anything actually happens in the Pro database itself. So, you're again asked at this point whether you are certain you want to proceed. Click [Enter] with the 'Yes' option highlighted to go ahead:

This time, you are again given an opportunity to back out (not explicitly, it's true: but you could click Ctrl+C here to stop the process actually doing anything), but if you click [OK] (or [Enter]) to proceed, it's the local PLAYS table data that will be copied or 'pushed' to the remote Pro database:

Finally, data actually starts to be copied from the local device to the remote server: you're told how many plays have to be copied across in total, and how many have been processed so far, along with a convenient display of a percentage progress bar. You are past the point of return now: any existing data has been wiped from the remote database and the new data is being copied across. You can interrupt the process (press Ctrl+C), but you'll then simply have to re-run the initialisation process... and start over again from zero as you do so. It's thus better to just let the process tick along at its own pace until it completes. Just one word on terminology, in case there's any doubt: "pushing" or "transferring" data simply means the local data is copied across to the remote database: it does not mean that the local database ever once 'loses' its data. That's how it's possible to re-run the initialisation process, after all: it creates copies of data in one database, but the source doesn't disappear out of the local database, so it's still there should a re-run ever be thought necessary!

Incidentally, the transfer process is quite slow because actual computation is taking place. For every item in the local PLAYS table, the program is taking the COMPOSER and COMPOSITION tags, smashing them together and adding in the time of the play in question, resulting in something like 'BRITTENPETERGRIMES030924', for example. That whole concatenated string is then passed through a hash function to create a weird-looking string of numbers and characters that uniquely identify that particular play of music, called the PLAYHASHVALUE.

Once the local PLAYS table has had its data copied to the Pro database, this happens:

This is just information to let you know the next stage of initialisation is about to happen: there's no 'yes/no' option here and therefore no chance to back out, so just press [Enter] to continue: at this point, the local recordings table will get copied across to the global_recordings equivalent in the Pro database:

A progress bar is once more displayed, along with more specific numbers showing you precisely how many of the local recordings have been copied across. Again, the process takes time because each recording's COMPOSER and COMPOSITION tags are being smooshed together and passed through a hash function to generate a unique identifier called the RECHASHVALUE.

Once the transfer of recordings is complete, this message will appear:

Press [Enter] to dismiss the confirmation message and you'll be returned to the Pro menu. Immediately, at this point, you could run the Reports, Aggregate Statistics option and see something like this:

Instead of the ugly messages you saw before, now you've got real numbers to play with -and you know these numbers are coming from the Pro database, rather than the local one, because of the message about "GLOBAL Aggregate Statistics Report" in the top right-hand corner of the screen (and the fact that the table borders consist of dashes and plus signs!)

You can also go to the Play Music menu, take Option 1 and have this happen:

That's music playing locally, because the global Pro database told the local machine what to play next: basically, once the global database has been initiated, it acts as both 'source of information about what recordings to play' and 'repository of data about what recordings have been played'.

3.0 Summary

Initialisation of a Pro database is a once-off affair, performed by the client Giocoso device that has been declared to be 'primary' in the Administration menu, Option 2: Create or Edit the Configuration file. It involves creating various tables and views in the Pro database, then copying across data from the local recordings and plays tables to become the contents of the global_recordings and global_plays tables. Once you've initialised the Pro database, it can be used by all Giocoso client devices to know what can be played and what has been played.

The initialisation process can be interrupted once started, but then needs to be re-started from scratch and allowed to run to completion.

Should another client device ever take the initialisation option, it will erase the existing Pro database's global_plays and global_recordings tables and replace them with data from its own local equivalent tables. In theory, that makes no difference for the recordings/global_recordings tables, because all client devices are assumed to be able to 'see' the one music collection, so one device's recordings table should be identical to any and all others'. Plays are different however, being unique to the device that played them. The re-populated global_plays table will only contain the initialising devices plays: to have the plays made by other devices added to that, you'll need to use the Pro menu, Option 2 : Push plays to remote database option, which is described separately.

Initialisation is not hard to do and doesn't destroy any local data, but it takes time to complete because each recording and each play has to have unique identifiers (rechashvalue and playhashvalue, respectively) computed before transmission to the Pro database can take place.