Hey everyone - long time no post. Sorry about that. I landed my dream job over a year ago and have been pretty busy since then. I'm working at Harebrained Schemes on the new turn-based Battletech game. The last one was over 20 years ago! Come check it out at:
http://battletechgame.com/

Today I'm going to talk a little bit about a metadata database (That's really fun to say btw) and how you might use it in your projects.

What is a metadata database?

Let's start with a couple of definitions. Metadata is just data about other data. And a database is just a structured way of storing and accessing data. So... a metadata database is a structured way of storing and accessing data about other data... Is your mind blown yet?

Why use a metadata database?

I'm going to look at our units for this example. We have a bunch of units with lots of data stored in separate files. Our unit metadata contains things like the filename, the type of unit: mech, vehicle, turret - and tags that describe the kind of unit that it is: medium, sniper, jump_capable. Instead of hard coding our unit spawn points to a specific unit, we configure the spawn point to ask for a unit with the tags "mech" and "medium" to change things up a bit. Without a metadata database, we'd have to load up every single unitdef into memory, and then loop through all of the data to build a list of the units that match, then select a random entry. With a MDDB, we can write a query that returns the list of units that qualify without having to load every single file and load the unit that we need to spawn.

Also, once you put this type of data in a database, you can start writing sql queries to easily get at information. How many maps implement a particular encounter? How many contracts are written for the Escort contract type. Are there any events that can't be triggered because they rely on tags that are never awarded.

Some Library Options

We're using C# and Unity for our project, and these are the main tools we're using for our SQL needs.

• http://www.sqlite.org/ - Free Sql engine
• http://www.mono-project.com/docs/database-access/providers/sqlite/ - Free library for interfacing with SQLite
• http://sqlitebrowser.org/ - Free visual tool for editing your database schema and writing queries.

Some TagSet Query Code

Here's some C# I wrote that builds a dynamic bit of sql based on the number of required tags and excluded tags. It wasn't trivial so I figured I'd share it. One requirement I'm not completely happy with is that the tags you ask for have to be in the database. At the start of the function I insert the ones that weren't present. It made the sql a little bit cleaner to look at and get right.  For our needs it's not a big deal because we'll only be asking for tags that we care about, but if an end user is typing in random stuff you'll want a slightly different approach.

Also I made one change to the Schema since after the graphic was made. I dropped the TagID and just use Name as the primary key. TagSetTag then drops its TagID column and gets a TagName column to point to Tag. I recommend that datamodel instead of the one shown, but our fans made the graphic for the data model and I didn't have a way of updating it easily. The code presented matches the old way.

public static List<TagSet_MDD> GetTagSetWithRequiredTags(this MetadataDatabase mdd, TagSetType tagSetType, TagSet requiredTags, TagSet excludedTags)
{
    // Get the tag rows for the specified tags so we can get their database ids.
    List<Tag_MDD> requiredTagRows = GetOrCreateTagsInTagSet(mdd, requiredTags);
    List<Tag_MDD> excludedTagRows = GetOrCreateTagsInTagSet(mdd, excludedTags);

    // Here's what the query will look like for 2 requried tags and 1 excluded tag
    /*
    select
        ts.*
    from TagSet as ts
        -- Required Tags
        inner join TagSetTag as tst0
            on ts.TagSetID = rtst0.TagSetID
        inner join TagSetTag as tst1
            on ts.TagSetID = rtst1.TagSetID
        -- Excluded tags
        left join TagSetTag as etst0
            on ts.TagSetID = etst0.TagSetID
            and etst0.TagID='tag id 3'
    where
        rtst0.TagID = 'tag id 1'
        and rtst1.TagID = 'tag id 2'
        and etst0.TagID is null
    */

    string queryText = "SELECT ts.* FROM TagSet ts ";
    string joinText = string.Empty;
    string whereClause = string.Format("WHERE ts.TagSetTypeId = {0} ", (int)tagSetType);

    // Join to one instance of TagSetTag for each required tag.
    for (int i = 0; i < requiredTagRows.Count; ++i)
    {
        joinText += string.Format(GTSWRT_RequiredInnerJoinFormat, i);
        whereClause += string.Format(GTSWRT_RequiredWhereClauseFormat, i, requiredTagRows[i].TagID);
    }

    // Join to one instance of TagSetTag for each excluded tag
    for (int i = 0; i < excludedTagRows.Count; ++i)
    {
        joinText += string.Format(GTSWRT_ExcludedLeftJoinFormat, i, excludedTagRows[i].TagID);
        whereClause += string.Format(GTSWRT_ExcludedWhereClauseFormat, i);
    }

    // Assemble the query text and return the results.
    queryText = queryText + joinText + whereClause;

    List<TagSet_MDD> tagSetList = mdd.Query<TagSet_MDD>(queryText).ToList();

    return tagSetList;
}