Database Documentation

The game editor and tools uses a database back-end to store all sorts of various data for the production purposes. Even though all data are exported to XML files which are used while playing the game in the end, you need the database for modifying most parts of the game.

Most of the time you don't have to work with the database directly, because the editor handles the data manipulations for you. But there are a few cases where you have to put the data into the database manually (either directly with your SQL client or preferably using our MS Excel plugin DB Addin). The most common such scenario is when you want to create a completely new item or modify some existing one.

Database inheritance structure

The database tables use sort of an inheritance to gradually extend properties of records of the same type. There is a base table, which contains only the common properties (columns), and it can be inherited by other tables, which add more specific properties. Note that a table can inherit from a table that is already inherited from another one. For example base table item contains only the most basic properties shared by all items in the game, this table is inherited by pickable_item adding properties common for each item that can be picked up by anyone, and this table is further inherited by player_item with properties that are only needed for items that can be picked up by a player. Specifically the tables regarding items have a bit complicated inheritance structure, you can see its schema below.

Inheritance schema of the items related tables in the game database

When something is defined with an inherited table, this single record is in fact spanning all the inherited tables, because each inherited table in fact contains only columns that were added for that purpose, therefore its more basic data are stored in the inherited tables. Following the previous example, the base item table contains the item_name column (since that is property useful for all items), pickable_item table has weight column (because all items that can be picked up need it), but it doesn't have the item_name column itself. All these inherited tables have some sort of an id column (it's typically the primary key of the table) which value ties the specific record across all these tables together.

The DB Addin makes working with this structure easier. When you open some inherited table, the addin automatically gathers all columns of inherited tables all the way to the base table, and shows them at one place. You can tell which columns were gathered from which tables by hovering over the column's name. In general the columns are ordered from the base table to the most specific inherited table.

Example: On the picture below is portion of the misc table opened in DB Addin (some columns are hidden for better readability), which is used to define various quest items. Here the column item_name is from the base table item, columns weight and price are from the pickable_item table (inherited from the item table), ui_name and ui_info are from the player_item table (which is inherited from the pickable_item), is_quest column is from the questible_item table (inherited from player_item), and misc_type_id and misc_subtype_id are from the misc table (inherited from the questible_item table). The column item_id is the unique identifier that exists in each of these tables, and is used to match the individual rows across all these tables together.

Portion of the misc table opened in DB Addin showing various properties inherited from other tables

You can find out whether some table is inherited from some other table (and from which table) with the DB Addin. On the list of tables there is a column indicating type of each table (ttBase and ttInherited) and next column contains name of the table that it is inherited from.

Creating new records

When you want to create a new record in the database, it is important not to potentially mess up the inheritance structure. When you are creating something in the inherited tables manually (using SQL client), you have to create the matching records in all the tables all the way to the base table. Therefore it is recommended to use the DB Addin. When you open some table there, you can directly fill in the values even for the inherited tables, and the addin correctly creates rows in all necessary tables.

Even while using the DB Addin it is sometimes a bit tricky to correctly create a record in this structure (especially while dealing with items). It is important to pick a correct table where to create the new record. A rule of thumb is that you should create new records in the leaf tables in the inheritance schema (you can see diagram of such schema for items above). Although it is not always the truth, for example when you are creating new body armor, it is supposed to be created in the armor table, which is still inherited by the helmet table.

Creating new items

The items related tables have by far the most complicated inheritance structure in the database (you can see it on the diagram above). The color indicates whether the table is potentially correct place to create a new item record or not. The green tables are the ones you are most likely going to use for creating new items. You could technically create something in the yellow tables, but it will be very rare scenario (and probably quite difficult to get working the way you want). If you would create something new directly in the red tables, it will break the database schema and then it shouldn't be possible to export it unless you create the matching record in the inheriting tables as well, which would fix the issue. But keep in mind, that the DB Addin automatically fills in the inherited tables, so the new record might be conflicting with the one created earlier.