ListGen preliminary release

[Buddabing]

Hello,

If there is any interest in this utility I'll keep working on it, otherwise I'll just keep it for my own use.

This utility is a command-line program (currently a Windows command line but is machine portable) which generates game lists.

It uses SQL to filter the game list with whatever parameters the user wants. There are more than thirty fields available for fitering.

To get all trackball games: select * from gamedata where control like 'trackball';

To get vertical games: select * from gamedata where orientation = 'vertical';

To filter out pr0n, mahjong, and quiz games: select
* from gamedata where catver_category not like '%Mature%' and full_desc not like '%Quiz%' and full_desc not like '%Mahjong%';

The program is currently crippled to only generate a plain text game list. It will eventually generate game lists for the most popular front ends.

There are also options in the program to select unique game names, so there won't be many different versions of Pac-Man in the list. The extra text in the game description (World version 3, Japan, etc) can be deleted, too.

The program pulls information from MAME, catver.ini, nplayers.ini, and PacManFan's gameplay.ini.

The program can also select a different version of the same game based on the description. For example, you can set World version to be the highest and Japan to be the lowest. The program detects Japan, Euro, World, and US versions.

Please feel free to leave any feedback or feature requests in this thread or email me at buddabing at houston dot rr dot com.

Here's a link to the zipped executable and support files.

I'll release source in a later version.

Regards,
Buddabing

[PacManFan]

Let me be the first to say "Good Job!". I think this utility will greatly benifit many front ends and the MAME community in general.

-PMF

[JoyMonkey]

This sounds great! I'm having some trouble getting it to do anything (sorry). I don't understand the syntax fully. To try and get a list of vertical games, I typed at the command prompt:
listgen mame.exe * from gamedata where orientation = 'vertical'
and I get the error:
A valid MAME executable must be the first parameter.
I've tried a few variations and get the same error every time. I have mame.exe 0.89 in the same folder as listgen and the ini files.

I know I'm going to kick myself when I hear what I'm doing wrong here...

[Minwah]

A valid MAME executable must be the first parameter.
I've tried a few variations and get the same error every time. I have mame.exe 0.89 in the same folder as listgen and the ini files.

I know I'm going to kick myself when I hear what I'm doing wrong here...

I get the same - put us out of out misery...

[Buddabing]

How to use the program:

On the command line just put listgen (no command line arguments are necessary).

The parameters to listgen are in listgen.ini. The most important parameter is where your MAME executable resides.

Your SQL to filter the list needs to be in filter.sql. The fields in the table are described in the file. For all games, just use select * from gamedata; The filter.sql should be in the zip file.

Parameters in listgen.ini:
rompath - where the MAME roms are located
mamepath - where the MAME executable is located
frontend - the name of the front end (currently only plaintext is supported)
verbose - print out debugging messages
silent - print nothing
audit_roms - only put roms in the rom path into list
use_clones - put clones in list
unique - remove duplicate game names

The priority set the order of US, Euro, etc. versions in the list. If the "unique" flag is set, only the version with the highest priority will be in the list.
us_version_priority
world_version_priority 0
strip_description 0
japan_version_priority -1

reuse_listxml_data - if set, don't call MAME to generate the XML list data every time listgen is called.

In the next version I may have the program search for MAME executables if there isn't one defined in the listgen.ini. I'll also make the error messages a bit less obscure.

HTH,
Buddabing

Edit: Joymonkey should change listgen.ini so that the line with the mame path is:
mamepath .

[JoyMonkey]

thanks!

[Minwah]

Thanks, I got it working too - looks great so far

I used this in filter.sql to make a list of 360degree driving games (I just picked this as I did it using MAMEWAH the other day):

control = 'dial'
and catver_category like '%Driving%'

Just one thing...I have use_clones 0 set but I still get clones in output.txt.

As I said seems great tho, and fast too.

[Buddabing]

Just one thing...I have use_clones 0 set but I still get clones in output.txt.

Oops. That's a bug. As a temporary workaround you can add "and isclone = '0'" to the filter.sql.

I am impressed with how fast the SQLite engine does the filtering.

You can change the output filename in the listgen.ini by adding a line: output xxxxxx

Buddabing

[BuZz880]

This app is great.    Lately I have been thinking about not updating MAME anymore due to a) the high number of games that run too slow to be playable and b) the huge number of newer games with proprietary control setups and more importantly c) the difficulty keeping my lists straight given a) and b).

This app potentially solves my problem, even as it is I think I can easily convert the list into something I can use in Mamewah; obviously, it would be better to have all the DAT info in there too but the key point here is you wanted feedback and I gotta say i'm impressed so far.

[BuZz880]

PS.  It looks to me like nplayer support is missing in this release, correct me if I am wrong.

[Buddabing]

This app is great.

[BuZz880]

nplayers_category is an unknown column apparently.  I'm usually pretty good with sql for a guy that maybe uses it once every year or so.

I guess DAT information is obsolete or maybe I have the wrong term but what I am referring to is the 13 or so lines of information seen in the mamewah lst files and include romname,description, rom status, controls, etc.  I think it sources from the listinfo.xml and is nicer I think than just a rom name and description.

[screaming]

If there is any interest in this utility I'll keep working on it, otherwise I'll just keep it for my own use.

  Great job Buddabing! I can't wait to check it out!

-Steve

[Buddabing]

nplayers_category is an unknown column apparently.

[Buddabing]

If there is any interest in this utility I'll keep working on it, otherwise I'll just keep it for my own use.

[Silver]

Yep, this looks very handy....

Nice work!

[vibez]

This looks interesting, cant wait for the full release when mamewah output is enabled

[Buddabing]

This looks interesting, cant wait for the full release when mamewah output is enabled

No need to wait. The current version supports MAMEWah and 3DArcade.

I'm still working on other frontends.

[vibez]

Sweet, I'll check it out

[Nocturnaloner]

How to use the program:

On the command line just put listgen (no command line arguments are necessary).

The parameters to listgen are in listgen.ini. The most important parameter is where your MAME executable resides.

Your SQL to filter the list needs to be in filter.sql. The fields in the table are described in the file. For all games, just use select * from gamedata; The filter.sql should be in the zip file.

Hey, for those of us not familiar with SQL syntax-

Do the double dashes in the default filter.sql file mean that the lines are commented out?

[Nocturnaloner]

Ok, and one more question:

Once you get an output.txt,  how do you make it work with Mamewah?

[Buddabing]

Ok, and one more question:

Once you get an output.txt,

[Nocturnaloner]

Thanks for the quick reply!

I did figure out that we are trying to generate .lst files that go in the mamewah/files directory.  Now my problem is that my filter.sql seems to not work.  Here is what is in mine (I backed up the default one and deleted all the comments for clarity):

select * from gamedata where
   catver_category <> 'Unplayable'
   and catver_category not like '%Mature%'
   and full_desc not like '%Quiz%';

I get some error message, but since the command window closes itself, and there's no log file that I know of, I have no idea what the error is.

[Buddabing]

Thanks for the quick reply!

I did figure out that we are trying to generate .lst files that go in the mamewah/files directory.  Now my problem is that my filter.sql seems to not work.  Here is what is in mine (I backed up the default one and deleted all the comments for clarity):

select * from gamedata where
   catver_category <> 'Unplayable'
   and catver_category not like '%Mature%'
   and full_desc not like '%Quiz%';

I get some error message, but since the command window closes itself, and there's no log file that I know of, I have no idea what the error is.

That SQL seems valid.

Try doing a listgen -verbose > log.txt and examine the log.txt to see if you see anything abnormal.

Do you have the latest executable? It should be dated 2/8/05 and is 199527 bytes in size.

[Nocturnaloner]

Ok, I think I figured it out-

Where do the catver, nplayer, and gameplay.ini files need to be for listgen to find them?  I had catver in the MAME root directory, and it wasn't working, so I put copies in the roms directory, and in the mamewah/listgen directory, which is where I'm running listgen.  One of those made it work.


Also, I haven't actually been able to find gameplay.ini anywhere.  Is it possible to get it without having to download Kyamera?

edit:  I do have the latest version of listgen.  In fact, it's rejecting a line from the earlier version that tells it to reuse_listxml_data.  What's the current syntax for that, so I don't have to generate it every time?

[Buddabing]

Ok, I think I figured it out-

Where do the catver, nplayer, and gameplay.ini files need to be for listgen to find them?  I had catver in the MAME root directory, and it wasn't working, so I put copies in the roms directory, and in the mamewah/listgen directory, which is where I'm running listgen.  One of those made it work.


Also, I haven't actually been able to find gameplay.ini anywhere.  Is it possible to get it without having to download Kyamera?

edit:  I do have the latest version of listgen.  In fact, it's rejecting a line from the earlier version that tells it to reuse_listxml_data.  What's the current syntax for that, so I don't have to generate it every time?

There are now other things for the program to reuse other than listxml, so I changed the name of the parameter to reuse_mame_data. You can do a listgen -showusage to find out all program options. (Just like MAME, I stole the code directly from MAME)

Gameplay.ini is only distributed with Kymaera. The author of Kymaera is also the maintainer of the gameplay.ini.

By default, nplayers.ini and catver.ini should be the same directory as the listgen executable, or you can specify where they reside. Again, look at the -showusage. Just set the path in the listgen.ini to whatever you want and you'll be set.

[Nocturnaloner]

Everything's working perfectly now, thanks!  And thanks for coding ListGen. 

[slycrel]

  I just wanted to add that I used listgen and 2600's custom utility to make an SQL file from an older mamewah list and it worked very well.  Thanks!

[screaming]

Buddabing,

  I've just been able to really use ListGen for some cool filtering.  Thanks a lot for putting this tool together! I love it!

  I have a couple questions though..  Is there any way to get ListGen to not insert a pipe between fields?  This prevents me from making arbitrary scripts based on ListGen output, for example, I can't make a batch file

Code: [Select]

SELECT 'copy ',romname,' c:\badroms'
  FROM gamedata
 WHERE status = 'preliminary';

or an SQL script

Code: [Select]

SELECT 'insert into roms (rom, cat) values ("', romname, '","', catver_category,'");'
  FROM gamedata
 WHERE catver_category IS NOT NULL;

since my script will end up with pipes before and after the romname.

Second problem: ListGen crashes every time it tries to write to output.txt, or at least that's what ListGen's telling me. output.txt gets created but is zero bytes and I get a crash like the attached image.  It's not critical since the results are in results.tmp, but it's annoying   Any idea?

-Steve

[Buddabing]

Buddabing,

[screaming]

ListGen isn't designed to execute arbitrary SQL. It's only designed to execute "select * from gamedata where <insert condition here". That's because the step that takes the result.tmp and creates output.txt expects a fixed number of fields separated by pipe symbols. If it doesn't get the expected number of fields, it will crash.

It would be easy to add a step that omits the creation of the output file. It may or may not be possible to get rid of the pipe symbols, I would have to look at the SQLite documentation and source.

Please post your listgen.ini and filter.sql that makes ListGen crash.

filter.sql

Code: [Select]

select cloneof
  from gamedata
 where isclone=1
   and catver_category <> 'Unplayable'
   ;

listgen.ini

Code: [Select]

rompath ..\mame92_budda\roms
mamepath ..\mame92_budda
frontend plaintext
verbose 0
silent 0
audit_roms 0
use_clones 1
unique 0
us_version_priority 0
world_version_priority 0
strip_description 0
japan_version_priority -1
reuse_listxml_data 1

  I just realized that if I change 'cloneof' in my filter.sql to '*' it doesn't crash.

  I've never seen the contents of output.txt before.. but running the query

Code: [Select]

select *
  from gamedata
 where isclone=1
   and catver_category <> 'Unplayable'
   ;

  just outputs a list of games, not all the information (as specified by '*') for each game.  This is the way it's supposed to work?  What about  a frontend type of 'exact' and another listgen.ini parameter of seperator (that can be empty) that will give me what I'm looking for.. Would that work?

-Steve

edit:

  I was just thinking more about this, and if ListGen had this option making game lists for any FE would be a breeze.  For MAMEWAH I'd have a query like:

Code: [Select]

SELECT romname, full_desc, year, manufacturer, bios, orientation, input, status, color, sound, catver_category
  FROM gamedata
;

and my listgen.ini would have

Code: [Select]

frontend exact
seperator "\r\n"

Of course, this would be the advanced way to do it and setting "frontend mamewah" would essentially set the appropriate query and seperator automatically.  Then, to support different FE lists it's just a matter of coming up with a format mapping.  You could even have that in a config file and never have to upgrade listgen.exe again (just add a format mapping in a config file)!

 Woah, this is how MAMEWAH Config got so bloated....

[Buddabing]

ListGen isn't designed to execute arbitrary SQL. It's only designed to execute "select * from gamedata where <insert condition here". That's because the step that takes the result.tmp and creates output.txt expects a fixed number of fields separated by pipe symbols. If it doesn't get the expected number of fields, it will crash.

It would be easy to add a step that omits the creation of the output file. It may or may not be possible to get rid of the pipe symbols, I would have to look at the SQLite documentation and source.

Please post your listgen.ini and filter.sql that makes ListGen crash.

filter.sql
[code]
select cloneof

[evlbeaver]

This rocks! I eagerly await the source release so I can use it.

I use a modified (by me) version of lemon launcher under Linux for my cab.

Till now I had to edit the gamelist manually to add games I wanted to appear on the menus.  I was just about to get around to write a program like this to build menus for me.  Looks like you have already done the heavy lifting.

Even if you did support this frontend I will need the source.  I modified the default lemon launcher format to allow for different emulators for each game.

Thanks for another great program!

[screaming]

For example, MAMEWah capitalizes some fields. Others expect the catver info in the game list to be "cooked", ie. joy2way needs to be "2-Way Joystick".

  That's rectified easily enough using SQL.  In fact, the following would produce a valid MAMEWAH LST (in result.tmp anyway) if only I could get rid of the stinkin' vertical-bars.

[code]
SELECT
UPPER(romname),
'
',
full_desc,
'
',
CASE
  WHEN year = ''    THEN '19XX'
  WHEN year IS NULL THEN '19XX'
  ELSE year
END,
'
',
manufacturer,
'
',
UPPER(cloneof),
'
',
UPPER(romof),
'
',
screen,
'
',
orientation,
'
',
CASE
  WHEN control = 'joy8way'       THEN '8-Way Joystick'
  WHEN control = 'joy4way'       THEN '4-Way Joystick'
  WHEN control = 'doublejoy8way' THEN 'Double 8-Way Joystick'
  WHEN control = 'doublejoy4way' THEN 'Double 4-Way Joystick'
  WHEN control = 'paddle'        THEN 'Paddle (or ~270

[Buddabing]

This rocks! I eagerly await the source release so I can use it.

I use a modified (by me) version of lemon launcher under Linux for my cab.

Till now I had to edit the gamelist manually to add games I wanted to appear on the menus.

[Buddabing]

[screaming]

Add this line to your filter.sql:
.separator ' '

  Awesome!

  Buddabing,

  If you're going to implement something like this whole views thing to make your life easier in supporting different frontends, this SQL will let me select anything I want from a "table" called mamewah and output as a valid MAMEWAH list (it SHOULD.. I haven't verified it yet though  ).  The same thing could be done for DK, Kymaera, 3DArcade, etc, using stored views.

  I must have been getting tired last night and tired means forgetful :/ You can create TEMPORARY views on temporary tables (duh). Anyway, behold the power of SQL:

[code]
.separator ''

CREATE TEMPORARY VIEW mamewah
AS
SELECT
UPPER(romname),
'
',
full_desc,
'
',
CASE
  WHEN year = ''    THEN '19XX'
  WHEN year IS NULL THEN '19XX'
  ELSE year
END AS year,
'
',
manufacturer,
'
',
UPPER(cloneof),
'
',
UPPER(romof),
'
',
screen,
'
',
orientation,
'
',
CASE
  WHEN control = 'joy8way'       THEN '8-Way Joystick'
  WHEN control = 'joy4way'       THEN '4-Way Joystick'
  WHEN control = 'doublejoy8way' THEN 'Double 8-Way Joystick'
  WHEN control = 'doublejoy4way' THEN 'Double 4-Way Joystick'
  WHEN control = 'paddle'        THEN 'Paddle (or ~270

[Buddabing]

Add this line to your filter.sql:
.separator ' '

[screaming]

Keep in mind, however, that MAMEWah is perhaps the simplest game list to generate.

I'm not sure how to rig this up, but it's got to be simple. Joe User should not have to deal with creating views and such. I'd say we would need to put the above SQL into mamewah.sql and just have the WHERE clause in the filter.sql, then append the two files together to be executed.

Suggestions welcome.

  I was just thinking you'd create all your temporary views right after you create your temporary gamedata table. Hard coded I guess, but game list formats don't change all that often.  The user's filter.sql file would just contain the select statement for the view they want to query instead of the gamedata table:

Code: [Select]

SELECT * FROM mamewah_view WHERE catver_category <> 'Unplayable' AND control LIKE '%wheel%';

(assuming that mamewah_list is the view as described above)

The only time the user would have to mess with views (or even know what a view is) would be if you're not yet supporting a game list format, batch file format (you could have a view that automatically creates your MMM bat files, for example).  That way, also, you could leave it up to the FE developer to just hand you a SELECT statement for their own gamelist format (and you code in the CREATE TEMPORARY VIEW statement) instead of you having to create them all from scratch.

  Either way, you're still not limited ListGen to ONLY using views; Any kind of SELECT statement even against gamedata the way it is would still work just the way it is.

  Normally I would suggest putting all these views in a separate config/sql file, like your suggested mamewah.sql, but this is stuff nobody would ever touch anyway.  People that needed to define their own views (why??) could do so in their filter.sql. *shrug*  What do you think?

sab
P.S. One way to get rid of having to do the .separator '' deal is to concat all the fields instead of specifying them individually:

Code: [Select]

SELECT
UPPER(romname) ||
'
' ||
full_desc ||
'
' ||
CASE
  WHEN year = ''    THEN '19XX'
  WHEN year IS NULL THEN '19XX'
  ELSE year
END AS year ||
'
' ||
etc..

That would only return one column, but since you don't care if you're only generating a gamelist and not really manipulating the data further it should be fine... *shrug*

[Buddabing]

I posted a new binary which has the option to stop processing after the SQL filter step.

Link