Database

This is another very technical blog post which provides a look into the new database library which has been added to the TSM codebase with TSM4. This is an internal code library which makes it easier for us to implement new features in TSM4. This new database library is made possible by the class library which I discussed in a previous blog post.

Structured Data

Our main motivation for creating this database library was to make it easier to represent large datasets in a very structured way which could easily be consumed by the higher-level application logic. Another goal was to be able to connect UI elements to underlying data in a standard way which would allow the UI to update itself automatically when there are changes to the underlying data. Let’s dive into how our database library accomplishes these things.

One of the things which TSM tracks is the exact contents of the player’s bags. This dataset is used for many things including calculating inventory quantities for display in tooltips and use in varying features such as restocking via Crafting and determining what items can be posted on the auction house with an Auctioning Post Scan. For each slot of a player’s bags which contains an item, there is a well-defined set of attributes which we care about:

• location (bag and slot number)
• item link
• stack size
• item icon texture
• bind type (i.e. BoP/BoA)

In TSM3, the code which tracked this information was scattered across multiple places in the codebase, and the data was stored in a variety of different lua tables, each with their own structure. This lead to a lot of duplicated effort as we were scanning the player’s bags multiple times to track the various pieces of data which we care about. With this new database library, we can store all this information in a strictly defined and centralized manner, and the varying consumers can make queries against it to satisfy their own individual use-cases. Here is how the database gets defined for tracking the player’s bags:

“`

local BAG_DB_SCHEMA = {

fields = {

slotId = “number”,

bag = “number”,

slot = “number”,

itemLink = “string”,

itemString = “string”,

baseItemString = “string”,

autoBaseItemString = “string”,

itemTexture = “number”,

quantity = “number”,

isBoP = “boolean”,

isBoA = “boolean”,

},

fieldAttributes = {

slotId = { “unique”, “index” },

bag = { “index” },

}

}
“`

As you can see, we define all the fields (aka columns) of the database and their type. The fields closely match the list I gave above. Our database library also supports special attributes such as “unique” and “index” which are more advanced constraints on the data which can be stored within the database and allow for the data to be stored and retrieved more efficiently.

Now that we’ve defined the schema (aka structure) of the database, any time we want to insert into the database, the library will enforce that the data we are inserting (aka the row) confirms to this schema. This means that the database will contain a set of rows which are guaranteed to all have the exact same fields, and therefore can be used by the higher-level application logic in a standardized way. One place in which the database library enforces this is when a new row is inserted into the database. Here’s an example of what the code to insert a new row in our player bag tracking database looks like:

“`

private.db:NewRow()

:SetField(“slotId”, slotId)

:SetField(“bag”, bag)

:SetField(“slot”, slot)

:SetField(“itemLink”, link)

:SetField(“itemString”, itemString)

:SetField(“baseItemString”, baseItemString)

:SetField(“autoBaseItemString”, autoBaseItemString)

:SetField(“itemTexture”, texture)

:SetField(“quantity”, quantity)

:SetField(“isBoP”, isBoP)

:SetField(“isBoA”, isBoA)

:Save()

“`

As you can see, we simply create a new row, set all the fields which are defined by the schema, and then call :Save() which will cause the new row to be inserted into the database. How the database library actually stores the data within the database is an internal implementation detail, and is cleanly abstracted from the higher-level application logic.

Queries

Having this well-defined data structure also allows us to access and filter the data in a standardized way, which is done by making queries against the database. A query is the only way to read data from the database as it allows us to do much more complicated things than just iterating through a regular lua table, and in some cases can be significantly faster. For example, if we want to print out the items in the player’s backpack (bag 0) in order of descending quantity, we could perform a query against the database which would look like the following:

“`

local query = bagDB:NewQuery()

:Equal(“bag”, 0)

:OrderBy(“quantity”, false)

for _, row in query:Iterator() do

print(row:GetField(“itemLink”))

end
“`

If you are familiar with databases, you’ll recognize that this code is using SQL-like methods on the query. Instead of parsing raw SQL commands, the database library exposes a vast set of methods on the query object for building up statements like this. Specifically, this query is looking for all rows in the database which have a bag of 0 and ordering the results by quantity in descending order (indicated by the ‘false’ parameter). We can then easily iterate through the results of the query to get these rows.

The database library is also able to optimize this query because we’ve applied the index attribute to the ‘bag’ column in our schema. The index attribute tells our database library that we often query based on this column, so it should organize the data internally in such a way that doing so will be faster. In this example, this means that the database library can quickly find every row in the database with a ‘bag’ field of 0, without having to look through every single row in the database. This optimization provides a significant speed-up in this example, even over storing the data in a regular lua table where all the data entries are stored in a flat list.

Integration with UI Elements

One of the goals I touched on at the start was to be able to automatically update UIs when the underlying data changes. For example, in the Auctioning tab of the AH UI in TSM4, we provide a visual list of all items in the player’s bags which can be posted. As the items in the player’s bags changes (i.e. if the player is crafting things), this table in the UI should update accordingly. Another example of UIs which need to update when the data changes are the Destroying and Crafting UIs, both of which have major components which display data based (either directly or indirectly) on player bag data.

The way in which we accomplish this is to have the contents of the UIs be directly tied to a database query. The database library provides a mechanism for the UI elements themselves to be notified when the results of a query object have changed, in which case the UI elements can be automatically redrawn. So, when the player’s bags change, the tracking code will update the player bag database, which will then cause any queries against that database to also receive that update, and all UIs to automatically get redrawn. This way, the higher-level application logic (i.e. the UI code) doesn’t need to know or care what causes the underlying data to change, or even how it changes, which makes it far simpler, and greatly reduces the amount of duplicated code.

Benefits

It would be an understatement to say that TSM deals with a lot of data, including many things which are used internally and not exposed directly to the user. With the new database library in TSM4, we have a standard way of storing, accessing, and manipulating these various collections of data which integrates nicely with our UI code. Overall, this results in TSM4 being faster as it’s not performing duplicate effort to collect the data it needs as well as improves the lives of developers working on the code base by abstracting away how the data is stored and how it gets updated from the high-level logic. Specifically, the example query above would involve much more code and would be much harder to read through without the use of the database library.