14 Jul, 2009, Silenus wrote in the 1st comment:
Votes: 0
I am curious about this since I am considering using a database system to implement persistence and I was wondering if there were systems around which implement a wrapper around SQL and perhaps allow you to interact with the system in some manner which is more consistent with the host language. Do tools like this exist?
14 Jul, 2009, David Haley wrote in the 2nd comment:
Votes: 0
Yes, check out SqlAlchemy for Python for example.
14 Jul, 2009, Stormy wrote in the 3rd comment:
Votes: 0
Datamapper is a Ruby example.
14 Jul, 2009, elanthis wrote in the 4th comment:
Votes: 0
Just search up for ORM (Object-Relational Manager) tools for your target language. Such tools are most popular and most developed for languages that have a strong Web presence, but there are a few for less Web-popular languages as well.

If all you need is object persistence, look up object persistence frameworks. Some may use a SQL backend while many others do not.

In my opinion, using a relational database to store non-relational data is just a huge pain in the ass and a waste of time, however. ORMs make sense when you need a lot of object-oriented access to data stored in an RDBMS and also need to do heavy searching and querying where the SQL still comes in handy. When all you want to do is stuff objects in and out of a persistence engine, though, SQL will either force you to jump through a billion hoops (ORM or not) to keep the schemas in sync with your development or will force you to use a sub-optimal "flexible" schema that eliminates almost every benefit of using an RDBMS in the first place; even when you can write queries against those schemas, the performance will be atrocious.

If you're coding in C/C++, I really recommend eschewing all of that and just using a hand written save/load framework tailored to your specific data structures. Generic object persistence or relational mapper frameworks are very, very difficult to integrate with C/C++ due to a lack of reflection or native serialization routines, and you will easily end up writing more support code and glue code to use an ORM/object-persistence framework than all the code you would write had you just written custom save/load routines. A simple file-based object-persistence setup is very easy to write. You can even write it over Berkeley DB (now owned by Oracle, but still open source) and get full ACID behavior. You can even get a few simple indices setup if you need a small handful of searches/queries to be run but don't need the full power of SQL.

Of course, if you're coding in Python/Ruby/Java/etc., there are many good ORMs and object-relational mappers out there to choose from, and it's probably better to pick an existing well-tested and well-documented framework over trying to roll your own.
14 Jul, 2009, Silenus wrote in the 5th comment:
Votes: 0
Thanks all. Someone suggested Berkeley DB to me before and I guess I should give it another look and see if it suits my needs.
15 Jul, 2009, quixadhal wrote in the 6th comment:
Votes: 0
The big question to ask yourself is, do you plan to actually USE data relationships in your data, or are you just looking for a way to stuff objects in and out of storage?

One of the big reasons I will occasionally rant against the popularity of MySQL, is that it (AFAIK) lacks support for foreign keys, which is one of the essential tools in building a relational database. Quick example:

Quote
CREATE TABLE area_precips (
area_precip_id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
description TEXT
);

COPY area_precips (area_precip_id, name, description) FROM stdin;
0 Arid Dehydration and skin lesions
1 Dry Thirst and dry skin
2 Normal Normal
3 Damp Uncomfortably humid
4 Wet Rot and mildew
\.

… and then a ways down…

CREATE TABLE areas (
area_id INTEGER NOT NULL PRIMARY KEY,
vnum INTEGER NOT NULL,
name TEXT NOT NULL,
filename TEXT,
data_version INTEGER, – version is a reserved word
author TEXT,
vnum_low INTEGER NOT NULL,
vnum_high INTEGER NOT NULL,
level_soft_low INTEGER DEFAULT 0,
level_soft_high INTEGER DEFAULT 115,
level_hard_low INTEGER DEFAULT 0,
level_hard_high INTEGER DEFAULT 115,
… skip some stuff …
area_precip_id INTEGER DEFAULT 2 REFERENCES area_precips (area_precip_id),

CONSTRAINT "area_id_range" CHECK ( area_id > 0 ),
CONSTRAINT "vnum_multiple" CHECK ( vnum_low = area_id * 100 ),
CONSTRAINT "vnum_range" CHECK ( vnum_high >= vnum_low ),
CONSTRAINT "soft_level_range" CHECK ( level_soft_low <= level_soft_high ),
CONSTRAINT "hard_level_range" CHECK ( level_hard_low <= level_hard_high )
);


Now, this is just a subset of the area table, but it should illustrate what I mean.

When you create a new area, attempting to insert the data for it will cause a series of constraint checks, which the database will perform and if any of them fail, the area insert fails, allowing you to rollback and inform the user that they did something wrong (or that the programmer did something wrong). The advantage of having those checks HERE, is that you don't have to do the very same checking at the code level.

Additionally, you'll notice that area_precip_id is a foreign key, which links to the area_precips table. By setting things up that way, the database will ensure that if you attempt to create a new area, the precipitation value has to be something that already has an entry in that area_precip table. It can be NULL (for indoor areas), but it can't be 27 unless you have an entry for 27 in the table.

Another check which you get by using a foreign key is that if you attempt to delete an entry from the area_precip table (you decide you don't like wet weather anymore, so you DELETE FROM area_precip WHERE area_precip_id = 4. Because it's a foreign key of another table, the database will first make sure there are no areas that are set to be wet climates. If there are, it will cause the delete to fail because that would break the integrity of existing data.

If you really wanted to delete all wet areas, you could use a cascade delete that would also delete every single piece of data in the database that ever (even indirectly) referred to a wet climate. So not only areas, but mobs and objects that were in those areas…. even players who had their home set to those areas! Yes, you have to be careful. :)

If that kind of thing sounds overly complicated, or you don't see why it would be useful, then you probably don't really want to use a relational database. In that case, any old object store will do the job just fine.
15 Jul, 2009, bbailey wrote in the 7th comment:
Votes: 0
quixadhal said:
One of the big reasons I will occasionally rant against the popularity of MySQL, is that it (AFAIK) lacks support for foreign keys, which is one of the essential tools in building a relational database.


Just as an aside, MySQL's supported foreign keys in 5.*, I believe, though only in the InnoDB table engine (along with some other caveats), which is not the default engine. They're supposedly working on standards-compliant, engine-agnostic foreign key support for 6.1. http://www.mysqlconf.com/mysql2009/publi...

I do agree that PostgreSQL (or other, commercial, databases) is likely a better choice for anyone using a RDBMS seriously -now-.
15 Jul, 2009, elanthis wrote in the 8th comment:
Votes: 0
Foreign keys are supported in MySQL and InnoDB is the default storage engine if you want it to be. You can set the default on a per-server or per-database basis, along with the default charset (always go UTF8).

The generic constraint stuff quixadhal mentioned is -not- supported by MySQL in any released version, however.

To be honest, if you have a sane middle-layer between the DB and your main game logic, constraints aren't really that important at all. At work we even only use foreign keys defensively, since our middle-layer ensures that references are automatically updates as appropriate (within transactions, of course). We at one point used to rely on cascading deletes via foreign keys, but found those to be too dangerous when you needed to keep DB data in sync with data stored elsewhere, such as keeping video metadata in the DB synced with large videos stored on the filesystem.
0.0/8