Main > Software Forum
SQL for "control sets" help requested
(1/3) > >>
Buddabing:
As most of you know, the XML structure produced by MAME changed in version v0.107. Now a game can have more than one "control" defined for it. One example is demndrgn, Demons and Dragons. It has a "doublejoy8way" control defined, as well as a "trackball" control.

My ListGen utility runs off of a SQL engine. Currently there is a SQL table which is created from the XML data which only has one control type in it:


--- Code: ---create temporary table gamedata
(
   ... other fields....
   control varchar[36],
);

--- End code ---

Then I can select the control field or do a where clause or whatever.

What I want to do is to change the control field to some kind of set, so where you would have your select statement be:


--- Code: ---select * from gamedata where control='trackball';
--- End code ---

in previous versions, you would have something like:


--- Code: ---select * from gamedata where controlset contains 'trackball';
--- End code ---

Unfortunately, I don't know how to create a set like that. I believe that I could create a secondary temporary table and call it controlset, somehow keyed off the rom name, but I don't know the SQL syntax required.

Any help would be appreciated.

TIA,
Buddabing



   
Chris:
Hm.  Normally when dealing with a field that can have multiple values, I'd have a separate table for controls, with an ID linking back to the game list. So the ControlData table would have frields ControlID, GameID, ControlName. For each game, you would create one or more entries in ControlTable for each control, with the same game ID. Then you'd have:

SELECT *, ControlData.ControlName from GameData INNER JOIN ControlData ON GameData.GameID=ControlData.GameID WHERE ControlData.ControlName='trackball'

That's off the top of my head, though; I haven't SQL'ed in about 6 months so I might be rusty.
screaming:
Hmm.. As I see it, you have a couple options. Since sqlite doesn't support sets like mysql does, you have to work around it.  The first option will is harder to set up but will just get a list nice and easy like. The second one is easier to set up but might slow your program down since you'll have to test for n number of controls on every row.

1) Do kind of what Chris is saying..  You have 3 tables. gamedata, controldata, and a many-to-many mapping table. 


--- Code: ---SELECT
  game.name
  , control.name
FROM
  gamedata game
 , controldata control
 , mappingtable m
WHERE control.name = '$control'
    AND m.controlid = control.id
    AND game.id = m.gameid

--- End code ---

...or some such.

or, implement your own version of mysql sets with 2 tables. One is the controldata table to keep track of the numbers -> control name mapping.  The numbers are the int representations of binary '1' placement, you know, 1, 2, 4, 8, 16, etc.

The other is the addition of an int field in your gamedata table that you would binary & or binary | to test whether or not that particular game had that particular control.

shrug.
Chris:

--- Quote from: screaming on September 15, 2006, 10:16:54 am ---
--- Code: ---SELECT
  game.name
  , control.name
FROM
  gamedata game
 , controldata control
 , mappingtable m
WHERE control.name = '$control'
    AND m.controlid = control.id
    AND game.id = m.gameid

--- End code ---

--- End quote ---
I haven't used TinySQL... is there a technical reason why you'd do a three-condition WHERE clause rather than a JOIN, or is that just a stylistic difference between us?  (I've been debating whether to use TinySQL for the next DOSCab/WinCab or just keep going with what I've built...)

(And yeah, properly there should be a third table for controls with the many-to-many table having just ID's; I didn't want to complicate things, though.)

--Chris
screaming:
Force of habit, I guess. It's just what I've always done...  The sqlite engine is smart enough to rewrite the joins in there.  I wonder which way would be faster?
Navigation
Message Index
Next page

Go to full version