Game(s) Database?

[LookToWindward]

Hi,

 

I've just joined and thought I'd say hello. I found this site because I found the GemRB Project. It all started when I got back into BG and BG2 (I got the EE versions of both), I love the game and was intrigued how a game like this was put together. I'm a Mac Developer and when I started looking for tools to let me explore the data files that drive the games, at that time, I couldn't find one that worked on the Mac. I've since found a couple but since they are really just re-skinned Windows App's or written to be Cross Platform in Java, they really don't provide a good experience on the Mac. Anyway, I set about decoding the various file formats that make up the "database" and found the File Formats published on the GemRB site.

 

I've started writing importers written in Object-C (and have a few of the basic files format working, CHR, CRE, BIF, and I'm currently working on the KEY file. Anyway, I took a step back from this and realised that life would be so much easier if all these structures were contained in an SQL database (SQLite actually) and I've started down that track. I have a SQLite file that I have generated that contains all the CREatures from from BG-EE. This was just a proof of concept and now I'm designing the Model/Schema for the data as a whole. Rather that re-invent the wheel, I was wondering if there is already a Model/Schema for this? If so if someone could point me at it, I'd really appreciate it.

 

I would't mind volunteering to work on the GemRB project, I have a lot of experience on Mac and iOS using Cocoa and have worked on a number of High Profile Apps for both Platforms.

 

One thing that struck me was that I was wondering in the GemRB engine could benefit from having all the data built into an SQLIte database?

 

The way I saw it working is, the first time you launch it app it would build the database from the game files, and from then on read the structures in from the database rather than reading from the individual files. There would also be an option to do the reverse, e.g. read the database and generate all the individual files, optionally, in a new folder.

 

As I see it, the advantages of this approach is as follows:

 

1. Each Game (BG, BG2, IWD, IWD2, etc.) could be stored in the same database, therefore if done correctly, resources from the two could be shared and if changed in one could options be changed in all games that use it. For instance if a Spell is present in BG and BG2, and has the same stats, then the data could be used for both games. If you change it, it will change in both games. In this way History could be moved from one game to another, e.g. something you did in BG1 could auto-magically be available in BG2.

 

2. It makes modding much easier, all a modder need provide is a folder with the raw files for the mod, these files would be read in and merged with the database. It would then be really easy to disable/enable the mod at the start of the game, e.g a Mod List could be supplied with a check-box to enable or disable it.

 

3. Cross Platform, SQLite Standard and associated APIs are well defined and easy to use. It is also built into most modern OSs.

 

4. It would provide a clean interface consistent and documentable interface to the data.

 

 

Many other reasons but these are the main ones in my mind!

 

Any comment/suggestions/advice/help greatly appreciated.

 

Cheers

Dave

[lynx]

warning: I am generally disinclined towards databases.

 

Mario started porting DLTCEP to QT5 to make it fully portable and clean it up in the process. If you want a full mac editor, this would be the way to go, especially if you want a kickstart.

 

I don't see how having everything stored in a database would make file (format) parsing any easier. Nor do I remember anyone trying before, but of course I am not omnipresent.

 

1. There is relatively little overlap in files that matter and a merger could introduce mod incompatibilities, eg. when you do want to affect only one game. It would mostly save a bit of disk space. The way the originals work, in the case the same modification could be made, you could just copy the modified files from the override folder to the other game, without any need for scripting or weidu.

 

2. This approach is too naive. Mods have dependencies, conflicts and sinergies with one another. Pure copying and on/off switches cannot work reliably. Modders didn't choose arcane scripting out of masochism. For gam(e) affecting mods, toggling midgame is not advised anyway, so mostly utility mods would benefit. But weidu already provides easy uninstall/install, so it would only be a safe usability gain for a few mods.

 

3. Most. It's an extra dependency nonetheless. If you consider everywhere gemrb runs, this could be an issue. Eg. is there a port to amigaos, reactos?

 

4. In what sense? As far as I understand, you're just proposing a different on-disk layout. You'd still need to implement all the objects and relations on top of that and I don't see how the end result would be much different from what current editors have.

[LookToWindward]

It would't make file parsing any easier, it would be exactly the same, the only different is, once added to the database, you wouldn't need to parse the file again. And the interface from the Database Engine, would be consistent, e.g. all integer fields would be right size auto-magically without having to "know" how it was stored in the raw file. Thus making the code less reliant on the underlying file format.

 

Why are you disinclined towards Databases? I think SQLite is one of the best things that has happened to the desktop in a long time. SQLite is one of the most reliable and resilient pieces of software ever developed by man!

 

1. Agreed, very little overlap, and it probably wouldn't save that much if any disk space, but it would mean that the item is changed everywhere which is what I getting at, by "item" I mean "resource" really as it would apply to anything, including map tiles which might well save on space.

 

2. Yes, it depends on the Mod in question. In general copying/moving/renaming lots of separate files around that are part of File-Set it not a great idea. If something happens (like the App crashes, or Power goes out, etc), you can be left with the file system in a unknown state. This can lead to all sorts of weird behaviour and data loss - please also see below.

 

3. Not sure, but if it isn't, you can include it the app yourself, it's the same code. See https://sqlite.org. It's the main format for iPhone and iPad and (I think) Android, which makes it future proof!

 

 

4. You wouldn't have to implement the Object's yourself, they would be given to you by the database engine, in a "standard" format. If you get the database Model/Schema right the relationships etc will all be taken care of by the Engine. Basically everything can be made into a One-to-One, One-to-Many, Many-To-One and Many-to-Many relationships. Once you have done this, all changes etc. will ripple through the database auto-magically.

 

SQLite (and Databases in general), guarantee state and data integrity, this making the whole process that much safer. When importing the game or a Mod, nothing is committed to the database until the whole thing has been parsed. When exporting, a new folder is created, leaving the original intact.

 

For instance:

 

A Game is a Many-To-Many to a Character, which is a One-To-One to a Creature, which is a M One-to-One to an Inventory slot, which is a One-To-One to an Arrows Bundle Item which is a Many-To-One.

 

(I think I got the above right, usually I model it with a tool that does the hard work for me!).

 

 

SQLite (and Databases in general), guarantee state and data integrity, thus making the whole process pretty much fool-proof. When importing the game or a Mod, nothing is committed to the database until the whole thing has been parsed. When exporting, a new folder is created, leaving the original intact.

 

Once you get this right, you can auto-magically update the entire reference list in one hit, in the case above.

 

Imagine the Character with the Arrows Bundle above decides to give one of the (say) 80 arrows to someone else. if you did this in C/C++ code, you'd have all manner of checks to do and a lot of operations to split the bundle, create a new Arrow Bundle of just 1 Arrow, update the giving character to reflect the loss of an Arrow, update the recipient to now posses the Arrow. This is error prone and leads to all kinds of bugs and take ages to write and is difficult to test in all situations. This approach also leads to lots of sections of driving code cut and pasted and then modified to suit the new purpose, this itself is ok, but it means extra testing to cover all paths the code will take.

 

 

In case of letting the Database do it, all that work is done, simply by removing it from where it is now and inserting it into the new recipient. If anything goes wrong, the whole transaction is aborted and any operations that have been performed are backtracked out leaving everything as it was before the operation was attempted - automatically. Otherwise, If all goes well, after the call to the engine, everything has been consistently updated. At the same time, any objects that were referencing anything in that change and were affected by the change will be notified and updated - automatically!

 

All database operations are totally thread safe too, meaning it would be much easier to implement multi-threading.

 

--------------------------------------------

 

One of the sub-reasons I began along this path was from my own experiences. I was trying to install some mods and the install crashed leaving files all over the place and I had no idea of the state of the game folder, I had a backup so all was well, but it was a pain!

 

The current file formats are historical but we are dependant on them and back in the day it was probably a state-of-the-art system. I was very impressed by the whole Infinity Approach, they did the best they could with the tools available. In those days it wasn't feasible to use a database, but I think that some point it would be prudent to try to upgrade to a better more up to date system and a Database is one relatively easy way of achieving this.

[LookToWindward]

You should check this out:

 

https://sqlite.org/mostdeployed.html

• 300 million copies of Mozilla Firefox.
  
• 20 million Mac computers, each of which contains multiple copies of SQLite
  
• 20 million websites run PHP which has SQLite built in. [3] We have no way of estimating what fraction of those sites actively use SQLite, but we think it is a significant fraction.
  
• 450 million registered Skype users.
  
• 20 million Symbian smartphones shipped in Q3 2007 [5] Newer versions of the SymbianOS have SQLite built in. It is unclear exactly how many Symbian phones actually contain SQLite, so we will use a single quarter's sales as a lower bound.
  
• 10 million Solaris 10 installations, all of which require SQLite in order to boot.
  
• Millions and millions of copies of McAfee anti-virus software all use SQLite internally.
  
• Millions of iPhones use SQLite
  
• Millions and millions of other cellphones from manufactures other than Symbian and Apple use SQLite. This has not been publicly acknowledged by the manufactures but it is known to the SQLite developers.
  
• There are perhaps millions of additional deployments of SQLite that the SQLite developers do not know about.
  
• 
  

[Mike1072]

IE mods are written with the understanding that the files they change may be (and often are) already changed by other mods. The mod installer reads the current file contents and patches in the mod's changes to create the modded file. Thus, the contents of the modded file depend on the mods that have been installed beforehand.

 

There is no easy way to package up the raw files for a mod as you suggest without making almost every mod incompatible with some other mod.

[LookToWindward]

I'm not with you? The mods would be packaged in exactly the same way as now (although it would be easier to change that method if that was ever desired). The way the mod affected the file system, will affect the database in the same way, it's just that the changes will be reversible, e.g. each step is journaled and can therefore be undone easily. The first Mod would override the information of the database at the time the mod was installed, the second mod could override or it could choose to merge it with the current database, resolving and/or reporting conflicts. Each subsequent Mod acts in a similar manner. Think of the initial database at the bottom of a file, each mod forms a filter on top of the previous one. However, the original is never overwritten (except by a wanted base game content update to fix bugs or add to the base game) and at any step of the process you could disable or remove a mod. If this mod affects others mods, then it can resolve them in the same way. So if we have ModA, and ModB, ModC are dependant on it, then removing/disabling ModA will ripple the effect to ModB and ModC auto-magically (maybe after a user warning). It really all bolls down to how you setup the relationships in the Model/Schema, One-To-One, One-To-Many, etc.

[lynx]

But we don't re-parse items except when they are loaded. Once the inmemory objects are created, that's it, no matter how many times they are used. Sure, they can fall out of the LRU cache, but at that point it was justified.

 

I don't understand your integer complaint — there is no doubt now either, unless the format is still unknown, but then it's not an implementation issue.

 

My db disinclination stems from the fact that I've seen them used too many times inappropriately. It's a large hammer and not all nails require it.

 

2. Rare corner case, but yeah, when it happens it's annoying. Weidu also backs up all the files it modifies. I've never seen it crash on linux, even when ran through wine. You should report your problem.

 

4. Just as we explored the formats and then wrote the importers and exporters, you'd have to write your models and schemas. And then adapt the importers/exporters to handle it.

 

If you want to ensure partial installs don't happen (if it can — I don't know weidu internals), then there are simpler ways like first installing to a temporary dir and then moving files over. Practically, it's the same transaction system, just on the kernel/filesystem level. But really, this shouldn't be happening often in the first place, so I think your fear is skewed by your bad experience.

 

The arrow example is crazy. We use objects and functions and other obvious abstractions and it's all already written. This atomicity you want so badly would be good, but mostly if we ever get to multiplayer, to help avoid out-of-sync issues (I guess). Even the failure paths in your example would have to have callbacks, as it's a really complex set of engines/rule systems. Plus some checks would be unavoidable, since you can't do everything after-the-fact.

 

I get that you like data-driven approaches, but I think it's too late for that, since the games weren't designed with that in mind.

[lynx]

I'm not with you? The mods would be packaged in exactly the same way as now (although it would be easier to change that method if that was ever desired). The way the mod affected the file system, will affect the database in the same way, it's just that the changes will be reversible, e.g. each step is journaled and can therefore be undone easily. The first Mod would override the information of the database at the time the mod was installed, the second mod could override or it could choose to merge it with the current database, resolving and/or reporting conflicts. Each subsequent Mod acts in a similar manner. Think of the initial database at the bottom of a file, each mod forms a filter on top of the previous one. However, the original is never overwritten (except by a wanted base game content update to fix bugs or add to the base game) and at any step of the process you could disable or remove a mod. If this mod affects others mods, then it can resolve them in the same way. So if we have ModA, and ModB, ModC are dependant on it, then removing/disabling ModA will ripple the effect to ModB and ModC auto-magically (maybe after a user warning). It really all bolls down to how you setup the relationships in the Model/Schema, One-To-One, One-To-Many, etc.

If this is all one wants, they can just use a SCM for the data. That works already everywhere.

 

This would require the modding tools to also support the sql models etc, unless you wanted to unpack everything first.

[LookToWindward]

Another real benefit is game saving.

 

You could have a checkpoint a bit like the Auto-Save file, but instead of saving a snapshot of how the world looks now, it would just save the differences (delta's). This means that you could easily go back in time to a particular date/time! Since only the delta's are saved, the amount of data would be trivial, so you could easily store 100+ days back in one hour GT increments. Also it would be possible to merge just the changes from a particular time back into the current time, again auto-magically resolving any conflicts, and having gone back in time, you decided you liked it the first time around, you can easily go back!

[LookToWindward]

I've already written most of the importers in Objective-C (well with an Objective-C wrapper), they import a file structure and make an Objective-C object out it, optionally following the file chain. From these objects I generator SQLite Code that creates the tables and populates them with the data). At this stage it's just a dumb black box database.

 

I agree that it really would come into it's own for multi-player (in fact it would make it a doddle!), but apart from the benefits I've already stated, I was thinking it would make expansions/mods much more interesting long term.

 

Think of multi-player, but rather than being able to have N players, you could have remote extensions published by the Modder.

 

Imagine an Inn in the game, where you go to find Mods/Extensions/Expansions etc. The developers of the mod(s) puts an Ad on the board in the bar, you go to the bar and can see what quests are available. You pick you like the sound of one and when you choose it, it goes to a server and uses the same mechanisms described above to merge in the Mod. From the Mod developers point of view, if they want to change something, they'd have a choice of applying the Mod to games already started or to new games only. It would be simple for collaboration effects, one developer could develop a level independently and developers could share their databases privately before releasing the the general public.

 

All this could be achieved now I suppose, but with a lot of detail and platform specific mucking about that in theory could change at any time, I thinking more mobile here.

[LookToWindward]

You wouldn't really need to provide calls back for change notifications (although there are provisions for it if you want to be notified on a field-by-field basis) as such, the data would be refreshed into the in-memory object by the Engine and the common (base class) of the object in question. They'd be an end transaction status call back that could probably be handed generically for most cases and where it needed to be handled you could override this and provide you own, possibly calling the base implementation.

 

Anyway, I'm going to finish doing the first part of building of the database, because I've started it and I want to use it for my own editor for BG-EE, BG2-EE Mac.

[LookToWindward]

A really good example of what I am talking about here is that fact that there are two almost identical file structures for the BG-EE and BG2-EE games. This is because the game was originally sold only on the Beamdog/Steam/Whatever. Then they made an Mac Store version which requires a different file structure, so, a lot, if not all of the mods and their instructions stop working! There have been some changes to the installers to fix this but it's still a problem, especially on iOS where the file system is hidden and difficult to get at by the user. What I am saying is that if all this information was stored in a single file with a clearly defined interface all these problems would disappear. Engine Developers and Mod developers alike could forget about the file system and the packaging and get on with the content!

 

If they suddenly change the way in which the file system is accessed on either iOS or Android (as has been the case many times on iOS), then what? You'd have to recode and possible make different versions to cope with it.

 

I'm an Apple developer and I know for a fact that file paths will soon all become URLs and URIs (to the outside world) which will break everything again I should imagine!

[Mike1072]

I'm not with you? The mods would be packaged in exactly the same way as now (although it would be easier to change that method if that was ever desired). The way the mod affected the file system, will affect the database in the same way, it's just that the changes will be reversible, e.g. each step is journaled and can therefore be undone easily. The first Mod would override the information of the database at the time the mod was installed, the second mod could override or it could choose to merge it with the current database, resolving and/or reporting conflicts. Each subsequent Mod acts in a similar manner. Think of the initial database at the bottom of a file, each mod forms a filter on top of the previous one. However, the original is never overwritten (except by a wanted base game content update to fix bugs or add to the base game) and at any step of the process you could disable or remove a mod. If this mod affects others mods, then it can resolve them in the same way. So if we have ModA, and ModB, ModC are dependant on it, then removing/disabling ModA will ripple the effect to ModB and ModC auto-magically (maybe after a user warning). It really all bolls down to how you setup the relationships in the Model/Schema, One-To-One, One-To-Many, etc.

 

Suppose there is a Mod A, Mod B, and Mod C which all change the same file from the base game. This scenario is extremely common.

 

As you suggest, you could install Mod A on an unmodded base game, and keep track of the both the unmodded files/data and the files/data altered by Mod A. This would allow you to easily toggle Mod A on and off by swapping between the two sets.

 

If you also wanted to install Mod B and retain the ability to toggle both mods on and off, you would need to install Mod B on the unmodded base game and on the Mod A installation. This would leave you with 4 sets of data: unmodded, Mod A, Mod B, and Mod A+B.

 

To install Mod C as well, and keep all three mods toggleable, you would need to install Mod C on top of each existing saved installation. At this point you would be keeping track of 8 sets of data: unmodded, Mod A, Mod B, Mod A+B, Mod C, Mod A+C, Mod B+C, and Mod A+B+C.

 

With each additional mod, the number of files to track and the mod installation time would increase exponentially.

 

WeiDU currently offers a slower but saner way to toggle mods off or on. WeiDU takes a snapshot before each mod is installed, so if you want to uninstall a mod that is not the most recently installed, WeiDU will uninstall all the mods up to that point, uninstall the targeted mod, and then reinstall the other mods.

[LookToWindward]

Suppose there is a Mod A, Mod B, and Mod C which all change the same file from the base game. This scenario is extremely common

 

I think you misunderstand what I mean, could you give a simple example please?

[LookToWindward]

Here is what I mean.

 

"-" = represents a field within a file/database from the base game.

"a" = represents a field within a file/database from the ModA

"b" = represents a field within a file/database from the ModB

 

So:

 

 

----------- (10 fields within the database, 0 thru 9)

----------a (would change field 9)

----------b (would change field 9)

 

When the database is read, the base fields are read BUT if the corresponding mod is enabled, fill in that field from Mod.

 

In this case, both mods change the same field in a manner that SETs a new value, so they are mutually exclusive. The only thing that is duplicated are the fields that are changed.

 

Another case would be:

 

----------- (10 fields within the database, 0 thru 9)

----------a (would change field 9)

---------b- (would change field 8)

 

Now, there are a number of possibilities of what the developer wants, he wants value b in the in field 8 field, but he wants the base game value for field 9 or i he may want the value b and whatever field 9 is currently or he may want the value of field 10 to always be set as per ModA (or a list of Mods).

 

This just describes a SET operation, ADD for instance, would allow the field to be added to:

 

 

----------- (10 fields within the database, 0 thru 9)

----------+N (would add N to field 9)

----------+N (would add N field 9)