Sql2cpp example
Rationale
I needed to create a small utility to update the lmce_game metadata database when new versions of MAME were released.
Where To Get the code
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: Lmce_media_update
The code to read this has been placed here: Gamerom
Explanation of Table Schema
Currently 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)
Note about the psc tables
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.
The Rom Table
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.
The RomAttribute Table
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:
The Join Table: Rom_RomAttribute
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.
sql2cpp Naming Conventions and their relationship to different keys
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.
See Also
This code is used in GAMEROM.cpp/.h in src/UpdateMedia. Take a look to see the other side of the equation. Wiki link here: Gamerom
I hope this code shows some more of the tools used to build parts of this system.
-Thom
