Giocoso 3 – The Pro Menu : Extract remote data to CSV

1.0 Introduction

It's all very well having a remote, shared, Pro database of recordings and plays ...but how can you see that data and make sure it's what you think it should be? To be honest, the best way of viewing data in a MySQL database is to install a GUI client and start browsing through the database structures and data in a nice, point-and-click manner: my own recommendation is to use the DBeaver tool for this purpose:

Some people might find using a tool like this a bit daunting, however, and therefore Giocoso allows the remote recordings and plays data to be extracted to a local "CSV" file. The abbreviation 'CSV' means 'comma separated variable', where each piece of distinct data is separated from the next by a comma. However, classical music has names such as "Serenade for tenor, horn and strings" to deal with... and the extra comma in that name could cause trouble! Therefore, Giocoso actually uses the pipe character (i.e., | ) to separate the various bits of data: so you'll end up with something more like "Benjamin Britten|Serenade for tenor, horn and strings|Peter Pears" and so on. Regardless of what the data delimiter actually is, however, you can simply import a "CSV" file into a spreadsheet tool like Excel or LibreOffice Calc and have the data presented to you in easily-readable format, without the need to install specialist database management tools first.

The Pro menu, Option 5 extracts the remote PLAYS table data to a local text file; Option 6 does the same thing for the remote RECORDINGS table. Both options write their output into the $HOME/.local/share/giocoso3/txt folder, over-writing any previous data extraction when it does so, without prompting. Note that the term 'extracting' does not imply that any data is actually removed from the Pro database: it definitely isn't, and the Pro database remains fully populated and functional no matter how many times you take these options. "Extracting" in this context simply means "making a copy of", in a totally non-destructive manner.

2.0 The Data Extraction Process

Whether you're using the Pro menu Option 5 (extract the plays) or Option 6 (extract the recordings instead), the program immediately responds like this:

That is to say, the relevant remote data (recordings in the case of the above screenshot, but it looks and works almost identically for plays) is immediately written out to a local file and the full path to and name of that file is shown to you in the resulting confirmation message: it takes less than a second to extract around 20,000 records on my not-fast system, so it's practically instantaneous.

Click [OK] to dismiss the message (making a note of the path and filename first, of course!) and you'll return to the Pro menu display.

3.0 Using the Data Extractions

The new file(s) can be found using your system's standard file manager:

You could inspect the contents of either file by opening them in, say, your system's default text editor. In my case, I can right-click one of the files and open it in, for example, the kwrite text editor:

You'll note that the lines of data are very long -but you should be able to make out the pipe delimiters separating the different bits of information about each play or recording. In this state, you could certainly search for particular strings of data, but it's obviously not terribly useful to see the data this way.

So, instead, if I launch my computer's spreadsheet program (which happens to be LibreOffice Calc, but could just as well be Excel or something similar), take the File -> Open options and point it at one of the data extraction files, I see this:

The program knows that this text file isn't a native spreadsheet, so proposes a way of interpreting the data so it can be imported into a normal spreadsheet. The crucial bit to set correctly here is the 'Separator options' one: that's where you specify what the delimiter is between the different columns of data within the extract. Various 'normal' delimiters are offered, such as tab, comma, semi-colon and so on, but none of these are correct for what Giocoso has just produced! Instead, you see me putting a tick next to the 'Other' option and then, in the box provided next to that, I've typed the pipe character |. Toward the bottom of the screen, you can see that the spreadsheet program is immediately parsing the text file with this pipe delimiter in mind ...and looks to be making a pretty good job of distinguishing (in this example) the rechashvalue from the filesuffix column, so the pipe delimiter definitely looks like the correct thing to use. Click [OK] to confirm that:

The data is then imported in a much more usable form than before: you may well have to fiddle with column widths and row heights and all the other formatting paraphernalia that modern spreadsheet programs provide, but at least the data is intelligible! By default, the data is extracted in composer-name order, but you can use the spreadsheet's options for sorting data to achieve a sort on any of the columns you like.

4.0 Summary

It's not suggested that there's a pressing need to extract the Pro database's recordings and plays tables to local text files, nor that you will want to do this very often. Indeed, if you did ever feel the need to inspect your Pro database's data often, I would encourage you to invest that time and effort (but no money, happily!) in learning a tool like DBeaver, which makes the live version of your Pro data immediately available.

For those who only have an occasional need to inspect their Pro data, however, or who don't need or want to master a 'proper' database management or query tool, Giocoso's data extraction options are provided to make occasional, one-off, data extractions as painless as possible. The extraction process itself is unsubtle, asks no questions and is completed in mere seconds (if that). Actually using the data thus extracted probably takes longer to do than the data extraction itself! Even so, it is generally a matter of a few tweaks of import options for pretty standard spreadsheet programs.