Author Topic: An sql2cpp example - lmce_game_updater  (Read 2539 times)

tschak909

  • LinuxMCE God
  • ****
  • Posts: 5549
  • DOES work for LinuxMCE.
    • View Profile
An sql2cpp example - lmce_game_updater
« on: July 23, 2009, 10:24:34 am »
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_update

The code to read this has been placed here:
http://wiki.linuxmce.org/index.php/Gamerom

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)

Code: [Select]
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.

Code: [Select]
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.

Code: [Select]
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:

Code: [Select]
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:

Code: [Select]
#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:

Code: [Select]
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