in Game Design, Game tech, Tools

Using SQLite to Organize Design Data

I haven’t written purely about tech in a long time, but this is a particularly interesting intersection of tech and game design, so I thought I would share it with everybody. Be warned though: This is one of those posts that’s just about the thought process I went through for something and the solution I reached. I’m most definitely not advocating this solution for everybody. Think about it and pick the solution that works for you the best.

By now you’ve probably heard of Lasting Legacy: you’re managing a family around the 19th century through several generations, socializing, choosing good marrying prospects, and helping family members pick an occupation. Ah, occupations…



Occupations and the life and blood of the game. It’s what gives the player lots of new actions and effects that can be combined in lots of different ways to achieve lots of powerful effects. The only downside of occupations is that… there are a lot of them! Right now we have about 50 and that’s just some really basic stuff. I expect the game will ship with about 100 occupations, and possibly more once we add country-specific ones.

50 doesn’t sound too bad you say. Sure, but each occupation has a lot of data that goes along with it: a name, whether it has an action or not, text for the action,text for the log, costs, whether it’s inherited… Hang on, let me tell you exactly what an occupation has:

struct OccupationInfo
     Occupation::Enum occupation;
     Rarity::Enum rarity;
     const char* maleName;
     const char* femaleName;
     const char* description;
     const char* logEntry;
     IconSpriteId::Enum spriteId;
     uint32_t periodFlags;
     int goldIncome;
     bool incomeMultiplied;
     int prestigeIncome;
     int legacyIncome;
     bool action;
     int goldCost;
     int prestigeCost;
     bool educationRequired;
     int targetCount;
     bool pickable;
     bool destructive;
     bool inherited;
     Occupation::Enum childrenOccupation;
     int personLike;
     uint32_t tags;
     AccessoryType::Enum maleAccessories[MaxAccessories];
     AccessoryType::Enum femaleAccessories[MaxAccessories];
     int maleAccessoriesCount;
     int femaleAccessoriesCount;

How do we set all that data for 50 different occupations? Back when I was working for big game companies I’m sure that someone’s job would have been to create a GUI tool to let designers enter those values. Then someone else would create a fancy XML format that could be exported from the tool. Then, if we were lucky, someone else would take that XML and crunch it during the asset conversion process into something binary that could be read directly into memory. Probably someone else would work on tech that would let you hot load new data while the game was running. Sweet!

Except that now I’m a lowly indie developer and I don’t have time for any of that stuff that doesn’t actually make the game better. Fortunately, I also don’t have a team of 50 designers to deal with, so my solution is this:

	OccupationInfo& info = OccupationInfos[Occupation::Banker];
	info = OccupationInfo(Occupation::Banker, "Town Banker", IconSpriteId::Banker);
	strcpy(info.description, "Gain 100 gold. Income decreases by 1.");
	info.logEntry = "%s %s gained %d gold.";
	info.tags = OccupationTags::Gold;
	OccupationInfo& info = OccupationInfos[Occupation::Poisoner];
	info = OccupationInfo(Occupation::Poisoner, "Cunning Poisoner", IconSpriteId::Poisoner);
	strcpy(info.description, "Kill another family member.");
	info.logEntry = "%s %s poisoned %s %s.";
	info.cost = 500;
	info.tags = OccupationTags::Danger;
	info.rarity = Rarity::Uncommon;
	OccupationInfo& info = OccupationInfos[Occupation::Tutor];
	info = OccupationInfo(Occupation::Tutor, "Private Tutor", IconSpriteId::Tutor);
	strcpy(info.description, "Another adult family member leaves their occupation and becomes educated.");
	info.logEntry = "%s %s sent %s %s to school.";
	info.educationRequired = true;
	info.tags = OccupationTags::Social;

“Oh the horror! Hardcoded data!” shriek all new CS students as they recoil from the screen.

But it really isn’t that bad. Actually, it’s pretty great: I don’t have any exporting/importing to do, I don’t have to load anything at runtime, and the compiler does a lot of checking for me. True, I can’t change it on the fly, but I don’t really need to when rebuilding the whole game takes about a second.

Unfortunately the problem is that we have 50 of those initialization blocks. Hopefully a lot more by the time we ship. They’re perfectly fine to work on each of them in isolation, but I’m not able to get a big picture. Have I created a similar one to this new one I’m thinking about? How much do other similar occupations cost? Can I get all the Social occupations together so I can compare them? How many rare occupations do we have in the late period? I can’t answer those questions very well by looking at that code. I needed something else.

Global View

Using some kind of text file wouldn’t help any. It would be a matter of changing that into an ini or an XML file, which would be much more painful without any of the benefits.

I started setting up a Google Spreadsheet, which is something we did for the specialists in Subterfuge. Spreadsheets have some nice capabilities like doing data validation on cells (for example, to enter enums), or letting you sort records based on some criteria. In Subterfuge the spreadsheet method worked reasonably well because we didn’t have as many specialists and each of them had less data, so the spreadsheet was pretty manageable. Here we just had too much data for each occupation and the spreadsheet was getting unwieldy.

Miguel mentioned SQLite, but I wasn’t too keen on linking with some extra library and loading that data at runtime. It also sounded like total overkill to have a full relational database for what amounts to a single table. Then I realized how wrong I totally was.

People have written GUI tools for SQLite that let you create, edit, and browse databases very easily. For example, we’re using DB Browser for SQLite and I think there are better ones out there (although maybe not free ones). Our main table looks something like this:


And the data itself like this:


It looks like a spreadsheet until you realize that you can very easily search any field and you can even create “views” with more complex queries ahead of time. So now I can quickly see how many rare occupations we have in the late period with a single click. Very sweet! It definitely gives me that global view I was hoping for.

How about loading this data from the database at runtime? I doubt it would be a big deal, but it’s still something I’m not crazy about. Fortunately, it’s super easy to extract the data from SQLite. We have this in our make file:

@sqlite3 -header -csv $(DB_SRC) 'SELECT * FROM Occupations' > $(DB_CSV)

And that spits out a nice comma-separated file with all the data. Still, loading csv files at runtime isn’t much of an improvement, so we do a bit more processing. Initially I started converting the csv file into an ini file (since we already have an ini-reader library), but then I realized there was no point in doing that. Unlike Subterfuge, I’ll never want to have different versions of the game data around and load them based on different rules versions. We just want one true version. What’s the easiest way to put that data in the game? C code!

So with a simple Python script, I convert that csv file into a C file that looks an awful lot like the one we started with, except that this time it’s completely derived from the contents of the database. One of the great benefits of this is that we can let the compiler do a lot of the checking instead of having to do it in the script or (gasp) at runtime. For example, some occupations force their children to have a particular occupation (King -> Prince). I don’t even have any code that checks that the occupation you enter there is valid. We just generate code like this:

info.childrenOccupation = Occupation::XXXXX;

If XXXXX doesn’t happen to be a valid Occupation enum, you get a build error until you fix it. And again, since the whole turnaround time between editing the database and building the game can be a second or two, it’s really no big deal at all.


I left one small detail: Constants. Look at some of the first code I put up early on:

strcpy(info.description, "Gain 100 gold. Income decreases by 1.");

What’s wrong with that? Yup, you got it. Somewhere else in the code, I have some constants like this:

const int BankerReward = 100;
const int BankerIncomePenalty = 1;

Now every time I change how much money the Banker gives, I need to change the string and the constant (let’s not even talk about localized strings yet).

So instead now I use global string replacement while we’re generating the C code. The string on the database is this:

Gain [[BankerReward]] gold. Income decreases by [[BankerIncomePenalty]].

Whenever we load the csv file, we look for any substrings with the pattern [[…]] and we try to match them from a header file with constants that we’ve loaded. If we find them, we replace them with their values, and if we don’t, we spit out an error and stop because clearly someone wanted to have a string replaced there and probably mistyped it.

Does all of that sound complicated? Not really. When I said earlier that the Python script was simple, I wasn’t kidding. This is the bulk of it, including the constant replacement (just a couple helper functions are missing):

def main():
	dir = os.path.dirname(os.path.realpath(__file__))
	baseDir = dir + "/../LastingLegacy/AssetsRaw/"
	reader = csv.DictReader(open(baseDir + 'db/occupations.csv', 'rU'))

	sourceCodeDir = baseDir + "../src/"
	globalVariables = readGlobalVars(sourceCodeDir)
	pattern = re.compile(r'\b(' + '|'.join(globalVariables.keys()) + r')\b')

	Rarities = {'C': 'Rarity::Common' , 'U': 'Rarity::Uncommon', 'R': 'Rarity::Rare', 'Q': 'Rarity::Unique'}
	Periods = {'E': 'Period::Early' , 'M': 'Period::Middle', 'L': 'Period::Late'}

	defFile = open(sourceCodeDir + "", 'w')
	for entry in reader:
		type = entry['id']
		defFile.write('{\nOccupationInfo& info = OccupationInfos[Occupation::' + type + '];\n')
		defFile.write('info.occupation = Occupation::' + type + ';\n')
		defFile.write('info.spriteId = IconSpriteId::' + type + ';\n')
		defFile.write('info.maleName = "' + entry['maleName'] + '";\n')
		if entry['femaleName']:
			defFile.write('info.femaleName = "' + entry['femaleName'] + '";\n')
			defFile.write('info.femaleName = info.maleName;\n')
		description = multipleReplace(entry['description'], globalVariables)
		defFile.write('info.description = "' + description +'";\n')
		defFile.write('info.logEntry = "' + entry['logEntry'] + '";\n')
		WriteOptionalBool(defFile, entry, 'educationRequired', type)
		WriteOptionalKey(defFile, entry, 'goldCost', type)
		WriteOptionalKey(defFile, entry, 'prestigeCost', type)
		WriteOptionalKey(defFile, entry, 'goldIncome', type)
		WriteOptionalKey(defFile, entry, 'prestigeIncome', type)
		WriteOptionalKey(defFile, entry, 'legacyIncome', type)
		WriteOptionalBool(defFile, entry, 'incomeMultiplied', type)
		WriteOptionalBool(defFile, entry, 'action', type)
		WriteEnum(defFile, entry, 'rarity', Rarities, type)
		WritePeriodFlags(defFile, entry, 'periods', Periods, type)
		WriteOptionalKey(defFile, entry, 'targetCount', type)
		WriteOptionalBool(defFile, entry, 'destructive', type)
		WriteOptionalBool(defFile, entry, 'pickable', type)
		WriteOptionalBool(defFile, entry, 'inherited', type)
		if entry['childrenOccupation']:
			defFile.write("info.childrenOccupation = Occupation::" + entry['childrenOccupation'] + ";\n")
		WriteOptionalKey(defFile, entry, 'personLike', type)
		WriteOptionalTags(defFile, entry, 'tags', type)
		WriteOptionalAccessories(defFile, entry, 'maleAccessories', type)
		WriteOptionalAccessories(defFile, entry, 'femaleAccessories', type)



Here’s the full script in case anyone is interested in the details.

Boom! Done!

Ship it.