Difference between revisions of "Sql2cpp example"

Revision as of 09:46, 23 July 2009

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.

A Note about Foreign Keys and the related RomAttributeType table.

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

The Code is here Lmce_game_update.

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