Updating description, year and company (1 Viewer)

[Chreekar]

After I've done that SQL stuff, then I go into the v2.5 configuration editor. I was trying to tick/untick the 'Visible' tick box, then hit apply/OK. I'd get a crash dialog that shows which entry it was crashing on. I think the first crash was '88 Games. I removed the ' mark, hit apply, and it would then crash on the next one that had a ' in it. I removed that one, hit apply, and it would crash on the next one.

Some of the ' marks are in different fields such as the game's name, company, etc.

OK, I'm only encoding the ' sign in the path and Title field, so I guess that's the problem.

I also wondered if it's possible to add a line break syntax like <br /> or something in the description? (Haven't really tried yet)

I tried myself to add a line break after the description so that it would be an empty row below it before it starts from the top again when scrolling, but I haven't found out how to make the skin understand that it should linebreak.

 

[dir]

I'm trying to update the MyEmulators3.db3 "visible" flag with the following, but it crashes:

CREATE TABLE temp (title varchar(100), description varchar(200), yearmade varchar(4), company varchar(50), visible boolean);
.separator ;
.import ./mame3.csv temp
UPDATE GAMES SET visible = (SELECT visible FROM TEMP WHERE TEMP.TITLE = GAMES.TITLE);
DROP TABLE TEMP;

I've looked at the MyEmulators3.db3 schema and it says that the 'visible' field is char(5). I've tried this as well:

CREATE TABLE temp (title varchar(100), description varchar(200), yearmade varchar(4), company varchar(50), visible varchar(5));

mame3.csv contains only 1 line:

asteroid;Asteroids (rev 2);1979;Atari;1


When I update the database, the MyEmulator configuration plugin crashes with

************** Exception Text **************
System.FormatException: String was not recognized as a valid Boolean.
at System.Boolean.Parse(String value)
at myEmulators.DB.getGame(String path, Emulator parentEmulator)
at myEmulators.DB.getGames(String path, Emulator emulator, SearchOption recursive, Boolean showHidden)
at myEmulators.DB.getSomeGames(String sqlTail)
at myEmulators.DB.getPCGames()

Clearly, it's expecting a boolean value in the 'visible' field.

I've tried changing the source values:

asteroid;Asteroids (rev 2);1979;Atari;1
asteroid;Asteroids (rev 2);1979;Atari;TRUE
asteroid;Asteroids (rev 2);1979;Atari;True
asteroid;Asteroids (rev 2);1979;Atari;False
asteroid;Asteroids (rev 2);1979;Atari;0

But it always crashes. How do I update the Visible field in the database?

Any idea what I'm doing wrong?

 

[dir]

Well, I've spent several more hours trying every conceivable combination of CREATE TABLE statements to make this work, and failed.

CREATE TABLE temp (title varchar(100), description varchar(200), yearmade varchar(4), company varchar(50), visible boolean NOT NULL DEFAULT 1);
CREATE TABLE temp (title varchar(100), description varchar(200), yearmade varchar(4), company varchar(50), visible boolean);
CREATE TABLE temp (title varchar(100), description varchar(200), yearmade varchar(4), company varchar(50), visible boolean NOT NULL DEFAULT 0);
CREATE TABLE temp (title varchar(100), description varchar(200), yearmade varchar(4), company varchar(50), visible varchar(5));

all fail when I try to do an update using any of these:

asteroid;Asteroids (rev 2);1979;Atari;TRUE
asteroid;Asteroids (rev 2);1979;Atari;0
asteroid;Asteroids (rev 2);1979;Atari;FALSE
asteroid;Asteroids (rev 2);1979;Atari;1
asteroid;Asteroids (rev 2);1979;Atari;t
asteroid;Asteroids (rev 2);1979;Atari;#f

All of these simply cause the MyEmulators configurator plugin to crash with the error below. I'm starting to think that either:

- the Visible field isn't actually a boolean in the database,
- the configurator program has a bug,
- the configurator program's doing something clever to read a character field as a boolean or something
- you can't update a boolean field with the values above because none of them are actually binary TRUE/FALSE values

I'd love to know a way to update the database's Visible field so that I can do a bulk-update. There's no way that it's practical to change 7500+ tick boxes manually!

Dir

************** Exception Text **************
System.FormatException: String was not recognized as a valid Boolean.
at System.Boolean.Parse(String value)
at myEmulators.DB.getGame(String path, Emulator parentEmulator)
at myEmulators.DB.getGames(String path, Emulator emulator, SearchOption recursive, Boolean showHidden)
at myEmulators.DB.getSomeGames(String sqlTail)
at myEmulators.DB.getPCGames()
at myEmulators.Conf_PCGames..ctor()
at myEmulators.Configuration.Configuration_Load(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

 

[Chreekar]

The visible field is char(5) as you were thinking, and that string is parsed as a Boolean in the program. In other words, it is a string that can have two different values: 'True' and 'False'.

 

[dir]

I've tried reducing the code/update down to the smallest level.

1. I make a copy of a good database that I can open (without crashing) in the MyEmulator configuration add-in, then

2. Run sqlite3.exe using the following:

CREATE TABLE temp (title varchar(100), description varchar(200), yearmade varchar(4), company varchar(50), visible varchar(5));
.separator ;
.import ./mame3.csv temp
UPDATE GAMES SET visible = (SELECT visible FROM TEMP WHERE TEMP.TITLE = GAMES.TITLE);
DROP table temp;

and with mame3.csv having only the following single line:

asteroid;Asteroids (rev 2);1979;Atari;False

3. Then re-run the configurator, and it crashes during open.

Somehow I'm corrupting the database with this single, simple but clearly wrong record.

If I change the UPDATE statement to update the description, year made, etc. it works fine. It only fails if I try touching this 'visible' field.

I can't figure out why I can't write "true" or "false" into a record without the app crashing with the boolean error. Somehow what I'm writing is being stored differently in the database then when the config program writes it.


Will have to puzzle my puzzler some more!

I should point out that I don't really want to muck around with the database, which just leads to supportability issues. But I need a simple way to update the Visible flag to make the Mame collection a bit more manageable.

Ok, I'm making progress. I think the problem's with my SQL statements or something.

The following works:
c:\blah\sqlite3.exe myEmulators3.db3
update games set visible = "false" where games.title = "asteroid";
.exit

This changes the record from

\\MOFO\Mame\Mame\roms\asteroid.zip|0|asteroid|0|0|0|1/01/0001 12:00:00 AM||||True|False

to

\\MOFO\Mame\Mame\roms\asteroid.zip|0|asteroid|0|0|0|1/01/0001 12:00:00 AM||||False|False

and doesn't crash the configurator.

The previous attempts where changing the record to

\\MOFO\Mame\Mame\roms\asteroid.zip|0|asteroid|0|0|0|1/01/0001 12:00:00 AM||||False|False

which causes it to crash.

If you're observant, you'll notice that the record that crashes the configurator appears IDENTICAL to the one that doesn't crash it. So there must be something hidden in the update method that I can't see. I think I need to find another way to do the sql UPDATE statement that doesn't do it the wrong way.

 

[andreyua]

to Dir

How you make csv file with "visible" fields ?

 

[dir]

1. Extract information from Mame:

mame -listxml >mame.xml

2. Create fields.txt with the following in it:

game_name;description;year;manufacturer;cloneof
game_name==game|name
cloneof==game|cloneof

3. Open mame.xml in Notepad. do a find/replace of

.c">
.c" cloneof="NONE">

and save it again. This finds all games that aren't clones and adds the 'cloneof' value to them.
It's a big file so this might take a while.

4. Convert the file to csv:

xml2csv.exe mame.xml mame.csv fields.txt -D=;

5. At this point I opened the mame.csv file in a spreadsheet app (not Excel) and told it to use the ";" as the delimiter.
This neatly formatted the file so that each field was in it's own column: GameName, Description, Year, Manufacturer, and Visible.
I then changed the Visible column so that anything that was "NONE" I changed to "True". and the rest I changed to False.
Note that in the editor I used, it tried to interpret the values as boolean. I just want them as text.
Save the file again.

You should now have a csv file that shows the Visible status of each game that isn't a clone.

The file should then be able to be used by sqlite3.exe as the temp input file for updating the database.

Should.

 

[andreyua]

This updated BAT file from 1-st post.
- with better description (IMHO) contain description+year+company and file info, such:
for example (Bad Dudes vs. Dragonninja (US), Data East USA, 1988, FILENAME=baddudes)
- games.title = games.description, for.ex. (Bad Dudes vs. Dragonninja (US))
- if update database oldest BAT-file (from 1-st post), fields year, description and company from other databases (not MAME) eq NES or SNES, SEGA etc are deleted. With this bat not deleted
- also, if MAME file is BIOS file in this database, BAT file make fields visible -false.

Very sorry for my bad English

 

[buddman]

Is there a way to update this process but have it generate an xml file that the latest MyEmulators can use? The process isn't working for the latest release (3.1).