[ES 2] Replace XML database for meta data with SQLite

[Aloshi]

There is a known issue where ES will appear to freeze during scraping (very) large collections. This appears to happen because ES is re-parsing and then re-saving the gamelist.xml file.

My proposed fix is to replace the gamelist.xml format with a gamelist.db format (using SQLite) which will allow reading/writing of individual games without having to load/save the entire thing. This would also reduce memory use (game details can stay on disk until they are accessed).

[qjcg]

I was just heading over to the issues page to report exactly this, and suggest an SQLite gamelist as a solution to improve performance. Glad to see you got here first!

[ardee8three]

I do have a similar problem. I run ES on a retropie image and ES keeps freezing randomly, whether it is during scraping or just out of the blue while browsing thru the lists or trying to edit the name of a game. this once also happened when I booted up the pi and did nothing for a minute or so after ES started up. never happens when I'm in game and when this happens I can still access the PI via SFTP and browse thru the directories without and troubles, it's just ES that won't let me do shit. I tried allocating the memory differently with no impact on this issue. I have around 1000 roms stored, split onto 8 systems (mainly classic consoles) and I do believe it has to to with the amount of games and the reading of their gamelist.xml files. I will try to give the PI more power this weekend (gets around 1.2A at the moment, will double it) and see whenther this helps as I read somewhere that not enough power might be the cause. if this changes anything, I'll post it here.

overall I appreciate you guys are looking to get rid of the gamelist.xml files as the bigger they get... well you already now. Keep up the good work guys!

[robertybob]

I had this problem but just installed ES on my Windows PC, scraped everything there, then moved it onto my Pi. I now use a different scraper on my PC as it's a lot quicker.

[Aloshi]

An update on this - the gamelistdb branch has the code for this. I think it mostly works. If anyone is feeling adventurous and would like to try it (and knows how to build ES), please go ahead, I would appreciate some more testing before I merge it into unstable. You can go to "Scraper -> Import XML" in the menus to import existing gamelist.xmls (I might add an automatic check for this on first startup later). I have not tested it very thoroughly.

Known issues:

• Times need to be converted between the format used in the XML and the format used by SQLite.
• Image paths are not currently relative.

[remeh]

Greato! I'll try it this weekend and do some tests on my gamelists.

[Enverex]

Can you let us know the database structure you're using? I generate the gamelist XML files myself using my own (3000 lines of code) scraper so I'll need to modify it to generate the SQLite files instead.

[Aloshi]

I'll do a detailed writeup on the database schema when later. There are some comments in GamelistDB.cpp, but I don't think they're totally accurate right now. It's probably a hell of a lot easier to output XML than update the database directly, so I'm thinking about just keeping an automatic "import gamelist.xml if last modification time is newer than gamelist.db" check at startup. There's also an "export XML" function that isn't hooked up to a menu.

[PrawnCocktail]

I have been testing the gamelistdb build and its working perfectly fine under lubuntu 14.10, The database is definitely worth pursuing and integrating with unstable, I also find it easier to edit the database than the xml file,

[Enverex]

I was expecting it to be to be honest. I do extensive database work so switching to DB queries rather than generating a big string of XML isn't going to be too hard for me. Push it out Aloshi!

[Enverex]

I've just tested this again (after an import) and it doesn't start any faster than it did when it was using XML files. I've got the ROMs on a network drive and on startup I can see the network activity go through the roof, but in reality it shouldn't be loading anything from the network as I'm using "--gamelist-only". I have a feeling this may be to do with this other issue https://github.com/Aloshi/EmulationStation/issues/401

[IDmedia]

Is there any progress on this issue? I'm using EmulationStation (RetroPie) and notice that I can't have more then 10.000 roms before it takes more then 1 minute to boot the arcade. Adding more roms after that significantly increases the loading time. Is there a way to switch from xml to db yet?

[robertybob]

@NeoID No progress, development has halted for the foreseeable future.

[Enverex]

NeoID: You can switch to the new style by using the experimental DB repo BUT there's one major issue. It still seems to scan all the games to see if they exist which actually appears to be the thing that causes the delay, not the use of XML so it doesn't improve performance at all.

As mentioned in my earlier post, https://github.com/Aloshi/EmulationStation/issues/401 appears to be the real issue here, not the storage format of the games.

[aarononeal]

XML vs. SQLite is not the issue. The problem causing slow loads is because of how relative paths are handled. Both the game loader and the theme loader rely on the following functions.

std::string resolveRelativePath(const std::string& _path, const std::string& _relativeTo, const bool _allowHome, const bool _searchParents);
std::string createRelativePath (const std::string& _path, const std::string& _relativeTo, const bool _allowHome, const bool _searchParents);
std::string removeCommonPath   (const std::string& _path, const std::string& _common, bool& _contains, const bool _searchParents);

These methods use isDirectory() to determine whether or not a file should attempt to be loaded from the parent directory when referenced by . instead of the one it is in. This is relevant for themes which load parent relative content.

This is not relevant for loading games and so every single file of every game list hits the file system multiple times to see if a path is a directory. If you're loading from the network or slow media this can take several minutes while every game is probed.

I modified my local copy to add _searchParents per above so that the theme system does (true) and the game loader does not (false).

// removeCommonPath
std::string common = _searchParents ? (isDirectory(_common) ? getGenericPath(_common) : getParent(_common)) : getGenericPath(_common);

// resolveRelativePath
std::string relativeTo = _searchParents ? (isDirectory(_relativeTo) ? getGenericPath(_relativeTo) : getParent(_relativeTo)) : getGenericPath(_relativeTo);

You also need to set the ParseGamelistOnly setting to avoid hitting the file system on load.

With these changes, I brought an 8 minute load time down to about 3 seconds.

I also added this to loadConfig() to get progress on each system loading.

std::string progressText = "Loading ";
progressText.append(name);
progressText.append(" ...");
window->renderLoadingScreen(progressText);