1.0 Defining Uniqueness in Giocoso
Giocoso uses synthetic primary keys for its local database tables: each new insert is assigned a guaranteed-to-be-unique sequence number that is totally meaningless except for the fact that it's guaranteed to be unique. The reason for doing that was because the natural primary key for music is (as this article explains at length) a combination of Composer Name+Extended Composition Name and those things together can be very long strings, making searching and thus guaranteeing uniqueness rather slow (searching Jean-Joseph Cassanéa de Mondonville + In decachordo psalterio (Higginbottom - 1987) is always going to be slower and harder than searching for the number '5123', for example). When Giocoso was first designed, speed and lightness of touch were considered key design goals -and, indeed, remain so. If you run Giocoso Version 3.30 and above in local-only mode, your primary keys are still going to be synthetic sequence numbers.
Giocoso Pro introduces new design considerations, however: it requires the use of a 'proper' relational database (MySQL or its cousin MariaDB), probably on semi-dedicated hardware, so maintaining 'lightness of touch' can't really be considered a major issue any longer. Accordingly, Giocoso running in Pro mode uses new GLOBAL_PLAYS and GLOBAL_RECORDINGS tables which do use natural primary keys -though somewhat indirectly! If you take a recording's composer and concatenate it with its extended composition name, you can pass the result through a hashing algorithm to produce a 'hash value' which is guaranteed to be unique for those particular textual input values. For speed of computation Giocoso Pro uses the MD5 hashing algorithm to derive these 'semi-synthetic' key values. If you feed that algorithm the value "BeethovenSymphony No. 5 (Karajan - 1962)", which is a mash-up of one example composer+extended composition name, you get a return value of "8bd561c329191dc779d7b607ccfa6a03", which certainly looks ugly and synthetic, but actually counts as a natural key, because its source data are two completely natural key values. Feed the same algorithm the value "BeethovenSymphony No. 6 (Karajan - 1962)" and you get a return of "c22e22b360def939f694033cc78f18db": you get a wildly different result from simplying changing the text value of the symphony name from '5' to '6', so the hash value is truly unique per composer+composition data. [...]
Continue Reading