DatabaseAugust 31, 2021
The Database addon contains a relatively simple Godot Resource implementation meant to store "tabular data". It can handle multiple tables and even make a table reference another. It also contains an editor plugin that provides means to create and edit such resources.
The editor plugin uses the UI Tabular Box addon to display and edit the tabular data of a selected table.
All that said, when the addon is activated, a main section is created and once selected the editor should show something similar to this:
Database Main Window
Before I start explaining how to use this addon, I would like to mention that I do explain in the How it Works tab why I have chosen to implement the database as a Resource. If you are asking this question, then know it is answered.
Ok, to begin editing data we obviously need a database resource. There are (at least) two options to create a new one:
- In the editor, within the File System tab, right click the directory to hold the file then New Resource. In the window choose GDDatabase (database.gd) as type then Create. At this point you will need to specify the name of the new file then click *Save".
- Clicking the Create DB button within the Database window. It will bring a generic file browser that allows us to choose the directory that will hold the new resource as well as the name of the new file.
Note the extension of the file, tres or res. The first one will result in the resource storing the data as plain text, while the second will store the data in binary format. Part of the reason I chose to work with Resource for the database implementation is related to this "text vs binary" format and I do elaborate a little more about it in the How it Works tab.
Anyway, once the new database is created, if you chose option 1 to do it, you will need to load the file by clicking the Open DB button. Using the Create DB will automatically open the newly created file. Once a valid resource file is open, the "+" button above the table list will be enabled, allowing us to create new tables associated with this database:
Clicking this button will bring a dialog that provides a few options:
New Table Dialog
The first field of this dialog is the table name, which should be pretty self explanatory. The name can be changed later if so desired (shortly I will show how).
Next is the File Name. Again, this should be pretty self explanatory, but note the Embed checkbox that comes right after the file name. If this this box is enabled then new table resource will be part of the database resource file itself, otherwise it will create a new resource file exclusively for the table. This file will be placed on the exact same directory of the database itself. Note that currently the only way to store table data in binary format is to embed it into a binary database resource.
Finally there is the option to choose which type the ID will be for this table, either Integer or String. This is entirely up to you. Just know that if you change your mind this cannot be changed after the table is created.
For the next image I have created two tables, one embedded and the other in a separate file mostly to show how the UI displays the information. Note that the first table (another) contains its file name written bellow the table name. Hovering the mouse above the file name will bring a tooltip with the full resource path. The second table (something) is embedded and this information is shown bellow its name with the <Embedded> text.
In this image the something table is selected and this fact is shown by the highlight around the UI element holding the table information, plus a slightly shaded color in comparison to the unselected another table.
This UI element within the table list contains two buttons. One that is holding the table name itself and a "-" button. Clicking the table name brings a dialog that allows us to edit the name of the table. In this dialog the "OK" button will be disabled if the new name is empty or if it is equal to the name of any other table handled by the opened database:
Rename Table Dialog
As for the "-" button, if you click it a confirmation dialog will appear. If the "OK" button is pressed then the selected table will be removed from the opened database. Please note that if the table is stored in a dedicated resource, the file will not be deleted, only the database will not "point to it" anymore. In this case it is possible to drag the table's resource file into the table list in order to make the database point to it again. However, if you delete an embedded table, then the data will be lost and there is no way to revert it from within Godot.
Above the table display itself there are two buttons, Add Column and Add Row. On a fresh new table the "Add Row" will be disabled because there are no columns in the table. Wait, how about that "ID" column? Well, indeed, it is shown as a column but it's not a "real one". The "ID" is used as an alternative to row index that allows us to reach the desired data within the table. In other words, we need to add at least one "real column" in order to be able to add rows. That said, clicking the Add Column button will bring a dialog that allows us to specify the title of the column (this can be changed) as well as the value type of it (some types can't be changed):
Add New Column Dialog
As you can see, there are several value type options to choose from. Some of these types cannot be changed after the column is created and this information will be shown within the dialog in case one of these options is chosen. That said, there are a few things that I would like to talk about some of those types:
- Unique String, Unique Integer
Selecting this type will make the behavior be a little bit different when dealing with cell values of this column. Basically repeated values wont be allowed within the column itself. To that end, adding a new row will result in the internal system creating a default unique value for the new cell. Moreover strings cannot be empty. There is no visual difference in how cells of these columns are shown or edited when compared to "ordinary" string and integer columns.
Because columns of this type require a bit of internal upkeep in order to ensure uniqueness, it's advised to avoid using those types if possible. Now please note this is just an advice, not a "never use" statement.
- External String ID, External Integer ID
This column type allows cells to point into rows of a different table handled by the opened database. When one of these two is selected, a new dropdown menu will appear allowing us to select one of the possible tables that can be "linked". Note that two tables cannot reference each other like this. Another thing to keep in mind is that you cannot have two columns in a table referencing the same other table.
In the image bellow I clicked to add a new column into the something table and selected the type to be External Integer ID. Because the another table have its ID type set to Integer when it was created, it appears as a candidate in the External table drop down menu.
Add New Column - External ID
In the sample database there are only two tables. Both have the ID types set to Integer. This means that if the External String ID is selected in the dialog, no table can be a candidate to be referenced. To exemplify how the UI will be shown, there is the image bellow.
Add New Column - Invalid External
Note that a '?' has appeared between the OK and Cancel buttons. Hovering the mouse over it will bring a tooltip with more information about why the OK button is disabled.
- Random Weight
This is a special column that will hold floating point values, defaulting to 1.0. The main idea of this column is to automatically deal with probability weights so you can randomly pick any row from the table. The technique used in here is described in the Weighted Random Selection With Godot Tutorial. Only one column of this type can be added per table.
- Texture, Audio
While the internal stored value of a cell of any of these columns is simply a string holding the path to a resource file (either texture or audio), the cells allows us to preview valid resources. In the case of the texture a thumbnail display the image, while the audio contains buttons that allow playback of those types of resources.
- Generic Resource
Much like textures and audio, a cell of this type of column stores a string holding the path to any kind of resource file supported by Godot.
To continue with the example, I have selected the another table, added three columns, string, texture and audio. Then a few rows just to be able to show some details. The next image showcases this. Things to note in there:
- In the texture column when no image is assigned there will be a default cross image to show the fact. There is a "..." button that if clicked will bring a file dialog that allows us to select any image resource within the project. When a valid texture is selected the label of this button will be changed to display the name of the file. Moving the mouse over the button will bring a tooltip with the full resource path. As mentioned, the thumbnail displays the texture. And, there is a trash bin icon that if clicked will reset the value of the cell to "empty".
- Similar to the texture column, the audio contains the button that allows us to select a valid audio resource as well as display the file name when a valid file is assigned to that cell's value. When there is a resource, the playback buttons are shown and a "progress bar" appears above to show how much of the audio has been played. The trash bin button can be used to clear the value of the cell.
Example Data - Table another
Important: There is a bug related to audio seeking that will prevent the "progress bar" from working in the audio column. This problem also affects the capabilities of pausing and resuming playback. This bug has been fixed in Godot 3.3 so be aware of this issue if you are using an older version of the engine.
Selecting again the something table, I have added the External Integer ID and a Random Weight column. Then 3 rows. The image bellow shows this initial data.
Something Table - Initial Data
Note that I have changed the random weight of the second row to 0.5 to exemplify some things that I want to explain about the Random Weight column shortly. For now lets focus on the external column. When there is no value assigned all that will be shown is a button labeled "...". Clicking it will bring a dropdown menu with the rows of the referenced table as possible options. This dropdown also allows us to type a filter text that can help us find the desired row. It will match the ID or any other cell.
Something Table - External Dropdown
When an external row is selected, the label of the button will be changed to the ID of the selected row. The trash bin button will appear allowing us to clear the cell value. Above the button will appear a preview of each column and value of the selected row, like in the image bellow:
Something Table - External Value
Now for the Random Weight column. If you did read the tutorial about weighted random selection, then you will know that the "random weight" is just a relative probability between the candidates. In the case of this table, rows 1 and 3 have equal chance, while row 2 has half the chance of any of the other two rows. This is important to know when you start assigning the weights. But this column provides some useful information when you are editing the data. If you move the mouse above a cell, a tooltip will be shown providing the total chance the row will be randomly picked as well as the accumulated weight of that row (again, if you read the tutorial this accumulated weight will make more sense):
Before I move into the scripting side of the Database addon, there is one last thing related to the UI that I want to mention. When you open a Database, the full path to it will be displayed at the top of the main window. On its left side there is a button labeled Database file. If you click it a dialog will be shown with some information related to the database:
Database Info
Ok, the UI provides a relatively easy way to fill the database with data. How about retrieving this data within the game logic? For that the Database script offers several functions to obtain any required information. While it is possible to modify the stored data through script, I will not list any of those functions simply because I don't believe it's a good idea to change any stored data when the game is running.
That said, the first thing we need is a way to access the resource itself, and that can be easily done by simply loading the resource. Following the example of this tutorial, we could load the shown database with:
var db: GDDatabase = load("res://resources/sample.tres")
Then, from the db we can easily call any of the functions to retrieve data from it. The functions are listed in the table bellow:
| Function | Description |
|---|---|
has_table(n: String) | Returns true if the given table title exists within the database. |
get_table_list() | Returns an array containing the names of all tables handled by this database. |
get_table(t: String) | Given a table name, obtain a reference to its resource, which is of the DBTable script type |
table_has_column(table: String, title: String) | Given a table name, return true if that table contains a column with the given title. |
get_column_info(from_table: String, col_index: int) | Given a table name and a column index, return a Dictionary containing information related to that column. The fields are name (String) and type (Integer). |
table_has_random_setup(table: String) | Given a table name return true if it contains the Random Weight column type |
get_row_from(table: String, id, expand: bool = false) | Given a table name, retrieve (in a Dictionary) row data based on its id. If expand is set to true then the value (external id) of a column referencing another table will be replaced by a Dictionary containing the row data extracted from that table. This operation is not recursive. |
get_row_by_index_from(table: String, index: int, expand: bool = false) | Works in the exact same way of get_row_from(), however it uses row index rather than ID in order to reach the desired row data. |
randomly_pick_from(table: String, expand: bool = false) | Given a table name, randomly pick a row from it. If the table contains a Random Weight column, the weighted system will be used. Much like get_row_from(), if expand is set to true then any column referencing another table will have its entry in the returned Dictionary replaced by the row data extracted from the referenced table. |
As mentioned, the row data returned by get_row_from(), get_row_by_index_from() and randomly_pick_from() is a Dictionary. Each field in it is the name of a column from the table, holding the column's value. And if "expand" was set to true, then that value, instead of the ID, will be replaced by another row data, but extracted from the referenced table.
Now notice that all of those functions are accessed through the database resource itself, which internally retrieves the table resource, which is a DBTable script. Depending on how many times you need to obtain data from tables, performing those "queries" may negatively impact performance. If that's the case then maybe it's better to first manually obtain the table with get_table() function then directly extract the necessary data from it. So, bellow is the list of available functions within this script. Again, not adding any of the functions used to modify data:
| Function | Description |
|---|---|
get_id_type() | Returns either TYPE_STRING or TYPE_INT, depending on how the table was initially setup. |
get_column_count() | Returns how many columns this table is holding. |
has_column(t: String) | Returns true if the given column title exists within the table. |
has_random_weight_column() | Returns true if this table contains a column to automatically handle weighted random selection. |
get_column_title(index: int) | Given a column index, return its title. |
get_row_count() | Return number of rows currently stored within this table. |
get_row(id) | Extract row data (as a Dictionary) given its ID. |
get_row_by_index(index: int) | Extract row data (as a Dictionary) given its index. |
get_cell_value(rid: int, ctitle: String) | Given a row ID and a column title, return the value of a single cell. If the row or the column doesn't exist, then null will be returned. |
get_random_row() | Randomly choose a row and extract its data. If the table contains a Random Weight column type then the weighted system will be used. |
get_row_id(index: int) | Given a row index, return its ID. |
get_row_acc_weight(row_index: int) | Given a row index return its accumulated random weight. |
get_row_probability(row_index: int) | Given a row index return its probability of being picked by the weighted random system. |
get_total_weight_sum() | Return the total weight sum. |
And so, the core of this Database system is a Resource, implemented in the database.gd file. Originally my intention was to use a JSON file to store the data and deal with that from scripting, as a "wrapper". I even begun the implementation using that idea. The reasoning for this is the fact that it's incredibly easy to visualize JSON data as it's basically text. This fact also brings the advantage of being version control friendly, which is a very nice feature to have. However, the major drawback comes with its size and loading speeds. Granted, for the vast majority of game projects this is most likely not going to be a problem.
But then, when reading the Godot Resource documentation I stumbled into one of its features. Quoting:
Users can save Resources as version-control-friendly text files (*.tres). Upon exporting a game, Godot serializes resource files as binary files (*.res) for increased speed and compression.
This is fantastic because we get the best of "both worlds", text vs binary data! And so, I quickly changed the code to not use JSON.
In the database.gd file there is a Dictionary named _table, which is meant to hold information of all tables managed by the database. The idea is to have the name of the table as key and an instance of DBTable as value. This data is serialized, yet the property is not exposed to the Inspector. This is possible by overriding _get_property_list(), _set() and _get() functions. The "magic" here happens when we set the usage flag as "usage": PROPERTY_USAGE_STORAGE.
Not exposing the variable ensures there is no accidental undesired changes through the Inspector, while granting full control of the resource editing to the editor plugin that is provided with the addon. Shortly I will talk more about this aspect.
The database script offers several functions that basically access the table resource that is stored within the _table variable. A lot of those functions are mostly "just relaying" the desired action into the table. This means that a lot of the actual work is done in the table script (dbtable.gd). Indeed, the tabular data is actually stored within those table resources.
The table script contains a lot more variables in it. Similarly to how it was done with the database itself, those that are meant to be serialized are added into the vector returned by _get_property_list() function.
Looking at the script you may notice that column data information is stored in two different containers (_column_arr and _column_index), an array and a dictionary. Only the array is serialized, while the dictionary is dynamically built when the resource is loaded. What happens here is that each column results in the creation of a dictionary containing two fields, the title and the value type. This same "column dictionary" is shared within both _column_arr and _column_index.
Now, why use two containers like this? Well, the array helps with providing the desired order in which columns are shown within the UI (besides having index access to the columns). The dictionary offers easier access to the column given its title.
As for the row data there are also two different containers, an array and a dictionary (_rowlist and _rindex respectively). And in the same way, the dictionary is dynamically generated. However there is one difference here. In the column data, the index container holds the column title as key. In the case of the rows, the dictionary holds the row ID as key. Each row entry is a dictionary itself. Each field of this row data is the title of a column, holding the cell value.
Another property of the table script that is serialized is the _reftable dictionary. Whenever a column referencing another table is added, this dictionary gets a new entry, having the referenced table as key and the referencing column as value. Based on the information retrieved from this dictionary the database can dynamically build the contents of the _referencer dictionary that is also in the table script. This second container is actually used as a set instead of a pair key-value. This means the values of the entries are irrelevant. The reason for this container to exist is that it makes easier to know (and debug) if a table is being referenced or not.
There are two containers (_randweight and _accweight) that are not serialized. Those are filled only when the Random Weight column type is added. The first container, a dictionary, is used mostly to track which column is actually holding the individual random weights as well as the total weight sum. The second container holds the accumulated weight of each row. Every time the weight of a cell in the tracked column is changed, those containers are updated accordingly.
Finally (for this script), there is the _uindex dictionary property which is also not serialized. The purpose of this property is to help ensure values are unique when dealing with Unique Integer or Unique String column types. Basically, when a column of any of those two types is created, a new dictionary is generated and added as a "field" of _uindex. This inner dictionary is actually used as a set because its purpose is just to check if a value exists or not. So, whenever a value is added into a "unique column", an entry is added into the set of the corresponding column entry within the _uindex. Checking if a value exists becomes a matter of accessing this inner dictionary and checking if it contains that key.
The Editor
And them comes the editor aspect of the addon. As any other editor plugin, it requires a script deriving from EditorPlugin. This is the script implemented in editor/dbeplugin.gd. A lot of what is going on there is pretty much boilerplate code. Yet, there is one aspect of its code that may be of interest, which is the layout saving mechanism. Basically, Godot can request its plugins to generate some data in order to save/load layout between sessions. This is done through get_window_layout() and set_window_layout() functions, which are defined in the base EditorPlugin class. Layout saving can be triggered by calling save_layout() function, which is part of the base EditorPlugin class. When the editor UI is instantiated, a function reference is assigned within it, pointing directly into this save_layout(). An alternative way to do this is to add a signal within the main UI scene and listen to it within the plugin script.
The main scene is defined as a scene (dbemain.tscn) with the dbemain.gd script attached to it. As I have mentioned, most of the actual data storage is done in the DBTable script. When a table is selected for editing, its data is retrieved from the the table resource and shown in a TabularBox (UI Addon). This Control requires a data source in order to provide the data to be rendered. The relevant one is implemented in the editor/dbdsrc.gd file.
An instance of this data source will be created for each table existing in the database. When a table is selected, the corresponding data source is then assigned into the TabularBox. The data source receives an instance of the DBTable resource in order to pull the desired data from it.
When data is changed, however, it is done directly through the editor plugin and changed within the table resource. Once that occurs, some functions are called within the data source in order to notify the TabularBox that something has changed and rendering must be updated.
The TabularBox addon provides a few default column type classes, however those do not cover all of the types that can be stored within the database tables. For that there are four custom column classes, columnaudio.gd, columnexttable.gd, columngres.gd and columnrweight.gd, which are meant for audio, "external" table, generic resource and random weight types, respectively. You can see which column classes are associated with each supported type in the get_class_for_type() function within the data source script. This function is primarily called by the get_column_info() override, which provides to the TabularBox the necessary information to correctly instance the column objects.