SQL for "control sets" help requested

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

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

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]

select * from gamedata where control='trackball';
in previous versions, you would have something like:

Code: [Select]

select * from gamedata where controlset contains 'trackball';
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]

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

...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]

Code: [Select]

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


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?

[Chris]

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?

Depends on the SQL engine, indexes, primary keys, etc.  If I recall, SQLite (don't know why I said TinySQL before, that's Java) doesn't have a lot of the niceties of a full-fledged relational database (it just treats each record as a string, exactly like WinCab does). JOINS are the "textbook" way to do it, and thus I would expect that they would be more efficient, but in a project like this, all that's important is that you get the desired results.  Your approach was was sufficiently different to the way I was trained that I thought it might be a SQLite technical limitation you were working around.

[screaming]

Nope!  If you were trained at all then your way is probably more correct My experience is 100% hands on, and I've never been trained.

[Buddabing]

Okay, how about:

Code: [Select]

create temporary table gamedata
(
   romname varchar[12] unique primary key;
   ... other fields...
);

create temporary table controldata
(
   romname varchar[12] ;
   control varchar[36];
   ... other fields...
);

select * from
(gamedata inner join

(select * from controldata where controldata.control='trackball')

on gamedata.romname=controldata.romname);


The engine is SQLite 2.

I don't want to get any duplicate rows, just one row per game, on the main select.


   
   

[youki]

Sorry i didn't read all the thread so may be i repeat something.


I think you have to do a new table : ControlSet

with column :

ControlSetID
GameID
Control

then you could query like that

Select distinct GameId , (other columns..) from gamedata , ControlSet where (gamedata.GameID=controlset.GameID And controlset.Control like "Trackball");

Sorry, my SQL is a little bit rusty... but just to give an Idea.

[Chris]

I don't want to get any duplicate rows, just one row per game, on the main select.

Then when doing your main select, don't include the INNER JOIN.  If you do include the INNER JOIN without the WHERE clause you'll get back one row for each control in a game.

Code: [Select]

select * from
(gamedata inner join

(select * from controldata where controldata.control='trackball')

on gamedata.romname=controldata.romname);

This doesn't look quite right.  Try:

Code: [Select]

select *, controldata.* from gamedata
inner join controldata on gamedata.romname=controldata.romname
where controldata.control='trackball';
Although you really don't need * from controldata; that will get you romname twice.  All you care about in controldata is the name of the control, so:

Code: [Select]

select *, controldata.control from gamedata
inner join controldata on gamedata.romname=controldata.romname
where controldata.control='trackball';
Something to note: if a game has no entry in the controldata table, it won't appear in the output AT ALL.  Of course, this shouldn't happen.  But if there are really games that have no control in the table, the INNER JOIN ("only include records that are in both tables") should be a LEFT OUTER JOIN ("always include all records from the table on the left").

--Chris

[screaming]

Chris, you're so wickid smaht. Can i be your friend?

[Buddabing]

What I really want is to not include the fields from controldata in the select, so that the default:

Code: [Select]

select * from gamedata;
will always work. So, I guess I need a select with no extra fields.

Code: [Select]

select gamedata.* from

(gamedata inner join controldata on gamedata.romname=controldata.romname)
where controldata.control='trackball';


Thanks for your help!

[Chris]

What I really want is to not include the fields from controldata in the select, so that the default:

Code: [Select]

select * from gamedata;
will always work. So, I guess I need a select with no extra fields.

Code: [Select]

select gamedata.* from

(gamedata inner join controldata on gamedata.romname=controldata.romname)
where controldata.control='trackball';


Thanks for your help!

You would get it anyway; my example was slightly wrong.  So just remove the parenthesis from your version and you'll get it all (but again, romname will appear twice in the output records).

For my example to work the way I intended, the first * should have been gamedata.*.

[SirPoonga]

Budda, I really should show you how the controls.dat database is organized

screaming, don't worry, I was taught to use the where too because at that time it was the most supported technique.  I do inner joins now but you will see if I am in a hurry to find some data I will use the where clause.

I just wanted to make a note that there is a RIGHT outer join to include all results on the right BUT it is LEFT reversed.  In fact older MySQL versions only supported LEFT so you just rearrange the order your tables were listed

Correction Chris
select *, controldata.control
That will return ALL columns from ALL table and an extra controldata.control column.

Select * FROM table1, table2 blah blah blah  will return all columns from all tables
Select table1.*, table2.GUID FROM table1, table2 blah blah blah  will return all columns from tbale1 and the GUID column form table2

Also screaming you did have the more proper way of organizing the data...
FROM
  gamedata game
 , controldata control
 , mappingtable m

A mapping table, this will only work if budda makes a seperate table with all the control types in a table.  Budda, this is the proper way to do it (3NF normalized) but you can probably get away with just having a controls table that has a game id in it.

Edit:  I wrote this after screaming made his post, I see you corrected yourself before I finished typing

[Chris]

I just wanted to make a note that there is a RIGHT outer join to include all results on the right BUT it is LEFT reversed.  In fact older MySQL versions only supported LEFT so you just rearrange the order your tables were listed

There's a good point in this sentence: different SQL implementations use different "dialects" of SQL.  I run into this problem all the time taking queries from my Microsoft SQL Server to our AS/400.  Basic SELECTs should be the same everywhere, but just be aware that you may not be able to simply cut and paste examples of more complicated queries without a little tinkering.  Isn't SQL fun?

--Chris