Thinking about how many times I have seen Sandy Denny1 in concert caused me to wonder how many times I have seen Fairport Convention in concert*. I know that I have this datum stored in my old CD database, but since the great computer blackout2, I discovered that I don't have this program/database installed on my mobile computer. Fortunately I found a backup on a disk from several years ago so at least I could restore the program sources and data. But I couldn't run the program as the database engine involved was the Borland Database Engine (aka BDE) that has been deprecated for at least 10 years if not more.
So before I could look at the data, I would have to convert it to a different format. At first, I thought that I could do this on the Windows 11 computer, but it turns out that FireDAC cannot read from Paradox databases. Fortunately I have an installation of Delphi 7 on a Windows XP virtual machine, and although I hadn't previously installed the BDE installed there, it wasn't difficult to install it. After searching hard in my memory, I remembered that there was a program called the Database Desktop that allows one to see the data in a BDE database; I should point out that in the BDE each table is implemented by four to six different files, so there were about 40 files that together make up the database, and not one big file as Firebird implements databases.
There is an old version of Firebird installed on the VM (no unicode) so initially I defined the seven tables that make up the database. I changed quite a few of the field names within the tables - originally I had defined several fields with names like 'cd title', that caused more than a few problems when writing the code to access the tables. At the moment, the order of the fields within each table is the same as it was in the BDE, but in Firebird it's better to have varchar fields defined at the end of each table instead of having them scattered randomly throughout the table.
After a great deal of mucking about (including one colossal bug of my own making that set me back about an hour), I had a program that converted those seven tables to a more modern format. The next stage in restoring the data was moving the database file to the Windows 11 machine then converting the database to unicode. This was partially complicated by the fact that a few of the tables had fields containing Hebrew. The conversion from win1255 to unicode took somewhat longer than I had expected and not only because of the Hebrew. For example, one field in the 'tracks' table holds the length of the track; for some reason I had stored this in a string field, such as '4:17'. One track out of the 3,217 tracks in the table had the time '4.17', so of course this table didn't convert until I fixed the errant record.
There are two tables ('composer' and 'musician') whose primary key should be a composite of track and person, as per this blog3. But the transfer failed because there were several duplicate entries in both of these tables, i.e. the 'id' field was different (as it was an auto-increment primary key) but there were rows with the same 'track' and 'person'. I deleted these before attempting the conversion. The fact that there were duplicates means that the original program logic was flawed; duplicates cannot be created if the primary key is the composite of track and person.
So now I have a unicode database and I can set about writing the program that accesses this database. Most of the code is going to be new as the original structure left a lot to be desired. Actually, the program was originally intended to be a cd player that could display data about a cd as it was being played and that influenced the design.
A cd database is quite problematic in terms of data design because the unique 'unit' is not a cd but rather a single track - and even this isn't unique as a track can appear on both a 'normal' cd and on a compilation. A track has composers and musicians whereas a cd doesn't have composers. Of course, a track belongs to a cd. The 'concerts' table is a nice addition that doesn't affect any of the track data but can utilise the 'artists' table.
* 22 times
Internal links
[1] 2132
[2] 2041
[3] 2123
| Title | Tags | ||
|---|---|---|---|
| 195 | The world of Henry Orient | Films | |
| 504 | Three emails | Health, Robert Silverberg, MBA | |
| 1161 | Maximum word count for submission | DBA | |
| 1255 | Priority tip: beware of linked files when checking in Windbi | Priority tips | |
| 1334 | Evening ferry | Song writing, Andros, Greece | |
| 1811 | Books (new song) | Song writing |
No comments:
Post a Comment