I needed to create a small utility to update the lmce_game metadata database when new versions of MAME were released.
I have placed the code in src/lmce_media_update in the LinuxMCE-0810 source tree, and have it cut and paste into the wiki here:
http://wiki.linuxmce.org/index.php/Lmce_media_updateThe code to read this has been placed here:
http://wiki.linuxmce.org/index.php/GameromCurrently the lmce_game database stores simple attribute metadata for a series of ROMs using a schema pattern similar to pluto_media where the central tables are Rom, RomAttribute, and Rom_RomAttribute, creating essentially a Has and Belongs to many Pattern (HABTM)
mysql> show tables;
+---------------------+
| Tables_in_lmce_game |
+---------------------+
| GameSystem |
| Rom |
| RomAttribute |
| RomAttributeType |
| Rom_RomAttribute |
| psc_game_batdet |
| psc_game_bathdr |
| psc_game_batuser |
| psc_game_repset |
| psc_game_schema |
| psc_game_tables |
+---------------------+
11 rows in set (0.00 sec)
The psc_ tables were created by sqlCVS when I told it to create a new repository in the database to hold history data. same for any psc_tables in the database, lmce_game_update is unaware of them, and does not touch them at all.
mysql> explain Rom;
+---------------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+-------------------+----------------+
| PK_Rom | int(11) | NO | PRI | NULL | auto_increment |
| FK_GameSystem | int(11) | NO | MUL | NULL | |
| Romname | varchar(255) | YES | | NULL | |
| psc_id | int(11) | YES | | NULL | |
| psc_batch | int(11) | YES | | NULL | |
| psc_user | int(11) | YES | | NULL | |
| psc_frozen | tinyint(1) | YES | | 0 | |
| psc_mod | timestamp | NO | | CURRENT_TIMESTAMP | |
| psc_restrict | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+-------------------+----------------+
9 rows in set (0.00 sec)
mysql>
The Rom table simply contains filenames, and maps them to PK_Rom entries.
mysql> explain RomAttribute;
+---------------------+------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------+------+-----+-------------------+----------------+
| PK_RomAttribute | int(11) | NO | PRI | NULL | auto_increment |
| FK_RomAttributeType | int(11) | NO | MUL | NULL | |
| FK_GameSystem | int(11) | NO | | NULL | |
| Name | longtext | YES | | NULL | |
| psc_id | int(11) | YES | | NULL | |
| psc_batch | int(11) | YES | | NULL | |
| psc_user | int(11) | YES | | NULL | |
| psc_frozen | tinyint(1) | YES | | 0 | |
| psc_mod | timestamp | NO | | CURRENT_TIMESTAMP | |
| psc_restrict | int(11) | YES | | NULL | |
+---------------------+------------+------+-----+-------------------+----------------+
10 rows in set (0.00 sec)
Similar for attributes, we simply store single attributes, and map them to PK_Attributes.
Notice FK_AttributeType? That's a foreign key.
There is an AttributeType table which simply contains:
mysql> select * from RomAttributeType;
+---------------------+--------------+--------------+-------------+---- ----+-----------+----------+------------+---------------------+--------------+
| PK_RomAttributeType | Define | Description | PicPriority | psc_id | psc_batch | psc_user | psc_frozen | psc_mod | psc_restrict |
+---------------------+--------------+--------------+-------------+--------+-----------+----------+------------+---------------------+--------------+
| 1 | title | Title | NULL | 1 | NULL | NULL | 0 | 0000-00-00 00:00:00 | NULL |
| 2 | year | Year | NULL | 2 | NULL | NULL | 0 | 0000-00-00 00:00:00 | NULL |
| 3 | manufacturer | Manufacturer | NULL | 3 | NULL | NULL | 0 | 0000-00-00 00:00:00 | NULL |
| 4 | genre | Genre | NULL | 4 | NULL | NULL | 0 | 0000-00-00 00:00:00 | NULL |
+---------------------+--------------+--------------+-------------+--------+-----------+----------+------------+---------------------+--------------+
This table isn't really used by lmce_update directly. Instead, when sql2cpp was run to generate the lmce_game class, there was a Define_RomAttributeType.h file, which contains among other things:
#define ROMATTRIBUTETYPE_title_CONST 1
#define ROMATTRIBUTETYPE_year_CONST 2
#define ROMATTRIBUTETYPE_manufacturer_CONST 3
#define ROMATTRIBUTETYPE_genre_CONST 4
this is referenced in the code to provide a consistent way to put human readable constant names to the different attribute type integers.
And finally to join the Roms and RomAttributes together, there is the Rom_RomAttribute Table, which really only has two real columns:
mysql> explain Rom_RomAttribute;
+-----------------+------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------+------+-----+-------------------+-------+
| FK_Rom | int(11) | NO | PRI | NULL | |
| FK_RomAttribute | int(11) | NO | PRI | NULL | |
| psc_id | int(11) | YES | | NULL | |
| psc_batch | int(11) | YES | | NULL | |
| psc_user | int(11) | YES | | NULL | |
| psc_frozen | tinyint(1) | YES | | 0 | |
| psc_mod | timestamp | NO | | CURRENT_TIMESTAMP | |
| psc_restrict | int(11) | YES | | NULL | |
+-----------------+------------+------+-----+-------------------+-------+
8 rows in set (0.00 sec)
Joining a Rom key, and a RomAttribute key.
If you use these naming conventions, PK_ for primary key, FK_ for Foreign Key, and EK_ for External Key, sql2cpp will take these, and build C++ code to be able to link between them.
This code is used in GAMEROM.cpp/.h in src/UpdateMedia. Take a look to see the other side of the equation.
I hope this code shows some more of the tools used to build parts of this system.
-Thom
