Open in app

Sign in

Write

Sign in

Dynamic Databases: From Idea to Implementation

Sonya Rivers
Just 3 Interactive
Published in
8 min readMar 19, 2019

The first stages of developing ArcLight has gone by quite quickly as we rolled out our game design documentation and finalized the initial design. As we entered into implementation, several questions started to sprout up that we didn’t consider the fine details of how we want to implement systems. We had discussed how we wanted the game to look and feel but not the intricate tech specs on how to make those things happen. Thankfully, with the help of thorough research we were able to come to a consensus on what we wanted to implement for our overall design system. The task that I had chosen was to create a dynamic database system in Unity that would hold relative data for the player’s stats, save/load variables, inventory system, and dialogue. I started with the dialogue first since it seemed more self contained and wouldn’t be modified at runtime.

In my mind the process of extracting dialogue was simple: the player walks up to an NPC, a dialogue box gets triggered to appear, the dialogue box displays the current speaker’s character portrait, the player and the NPC go back and forth until the dialogue has finished, and lastly the dialogue box is closed once the player has finished reading.

Easy as pie, right? Not quite. I wanted our dialogue system to be a bit more polished so that we could extract dynamic dialogue based on current game state events. This meant that specific questions had to be answered by our data:

(1)Who is the player is speaking to? — Is this an enemy, an ally, or a neutral third party? Whoever they are speaking to should have a portrait to reinforce who the current speaker is.

(2) What is the context of this conversation? — What is the speaker’s emotional state? Are they angry, happy, sad, excited, or nonchalant? Are they discussing key plot points to the narrative or are they passively discussing their favorite types of cheese? There should be some kind of visual cues (animations, colors, etc) to indicate to the player what the emotional status of the current speaker is. Also, is the character they are speaking to even involved in a quest or are they just townsfolk or a merchant?

(3) When is this dialogue taking place? — Has the player reached a point in the narrative to move the quest forward or are they still lacking some item or leaving a task unfulfilled that’s blocking their progress? What is the current quest progression level (part 1, 2, 3, etc)? What are some vague hints for what the player’s next objective might be.

(4) Where is the player? — Is the player in Antium (the capital city) or are they in Honorbound Haven (the Warmonger village)? Each segment of dialogue should capture where the player is in terms of regional location.

With these considerations in mind my visualization for our dialogue system’s UI was something like this:

A sample of a dialogue box with a portrait and animated text. Credit: https://friendlycosmonaut.itch.io/dialoguesystem

Easy peasy, right? As I dove into this endeavor I discovered that each functionality component would need to be further broken down as a separate entity to work harmoniously. There are three major functionality components to this system: (1) the UI dialogue box that contains the UI image portrait and TextMeshPro text to be displayed, (2) the JSON text file that would store all of the text and dynamic animation tags to be parsed by a C# companion script, and (3) the SQLite database that would store pointers to where each file is located in the game’s file storage directory which is also accessible for read/write access via a C# script.

The UI text box was easy enough to set up. I simply had an NPC character’s left portrait as a Unity UI image and aligned the character’s name as a TextMeshProUGUI text header and the dialogue body text as a TextMeshProUGUI text in the center. Lastly, I anchored each element’s position in the dialogue box’s Unity UI panel so that regardless of the screen size aspect ratio the UI components would remain anchored in their correct positions relative to the panel’s rect transform.

The JSON file parser was much trickier since the C# script that interprets the JSON file would need to be written a specific way in order for the TextMeshProUGUI text game object to be manipulated at runtime. While TextMeshProUGUI text game object does allow you to add tags such as <color = #somecolorhexvalue > body text </color> in the Unity editor inspector window it doesn’t change those values the same way at runtime.Thanks to the Unity wizards at FlareDust Studios I was able to find an extremely helpful tutorial that allowed me to add tags to a TextMeshProUGUI text game object at runtime similarly to how JavaScript HTML script tags can add color and animation to text on a webpage. This ingenious solution makes each line of text temporarily “invisible” each frame while revealing the animated text one character/one frame at a time for shaky text or colored text visual effects.

Lastly, the SQLite database implementation for Unity was accomplished by using a base plugin by GitHub user Roberto Huertas. I came across a stumbling block with this plugin due to the fact that you cannot easily perform a search query in traditional SQL language (which I was more familiar with) since the SQLite database queries have been translated into C# LINQ expressions so that they can be used within the C# .Net library. Much to my chagrin I was attempting to perform queries such as SELECT →WHERE →X=VALUE instead of using the LINQ expression query which is structured as SELECT →TABLE →WHERE →X.DATAFIELD=VALUE. The languages are very similar, but without prior knowledge of these subtle differences I spent a lot of time trying to reconfigure my queries in a way that wasn’t efficient or useful at all. After the breakthrough revelation that LINQ expressions should be used, I was able to properly configure the database queries to meet our use case. If you’d like to learn more about the specific differences between traditional SQL and LINQ SQL, you can visit this guide by Tutorials Point .

My first attempt at the dynamic dialogue database. This example tests the read/write functionality of the SQLite database. Initially the only entry in the character database is Billy Mays, but after creating two NPC characters they have also been added to the database. Additionally, each character’s lines are displayed in the panel as a TextMeshProUGUI object with rich text tags parsed from a JSON file.

My first attempt at the dynamic dialogue database setup went pretty smoothly until I realized that I’m unable to access characters by their name if they occur more than once in the database (characters in any quest narrative would likely occur several times). My initial instinct to solve this problem was to access each dialogue object by their database index since that is their primary key. That solution wasn’t very efficient either since the JSON file path name should match the character’s name so that anyone on the team can easily write a line of dialogue and pop it into the game without having to rewrite any code or manually search for the index that it refers to in the quest sequence.

This was inefficient because I had to add underscores to the name to access the same character for multiple lines. I was able to also access them by index but that would be nearly impossible to remember accurately as lines are added/removed often throughout development.

I resolved to fix this error by changing the table’s fields for the Dialogue Character object class.

Previously, the fields for the Dialogue Character object were it’s auto-increment primary key (int), the actor’s name (string), the actor’s profile as either a player or NPC to determine which side their portrait would be displayed(string), a URL link to their portrait image file (string), and a file path to the local storage directory for the JSON file containing the lines to be parsed and displayed (string). Talk about information overload!

I believed that better naming conventions for the JSON files would solve this problem. It occurred to me that the name should be a representation of the Who, What, When, and Where previously mentioned in the design process. Therefore, instead of simply accessing Iro the main character as:

_connection.Table<DialogueCharacter>().Where(x => x.Actor == “Iro” && x.ID == 2).FirstOrDefault();

The new database system would access each dialogue instance as:

_connection.Table<Dialogue>().Where(x => x.Instance == “Who_When_Where”).FirstOrDefault();

in which Who would represent the actor’s name, When would represent the point on the narrative timeline (if applicable), and Where would represent the location that the conversation is taking place. For instance, in our tutorial level Iro will be speaking to Digory the librarian and dueling instructor. The first line of dialogue from Digory would be:

_connection.Table<Dialogue>().Where(x => x.Instance == “Digory_TutorialPart1_LuforeCollege”).FirstOrDefault();

and the second line would be:

_connection.Table<Dialogue>().Where(x => x.Instance == “Digory_TutorialPart2_LuforeCollege”).FirstOrDefault();

The JSON file names would have to be precisely matched to this naming convention so that the file would be easy to locate in the local directory. Each JSON file would be stored in Assets/StreamingAssets/DialogueJSON so all the database would have to do to find that file would be to attach the Instance string at the end of the shared file path. For the previous example it would look like:

Assets/StreamingAssets/DialogueJSON/Digory_TutorialPart2_LuforeCollege.json

Similarly the portrait image files would also be retrieved by their PNG file names and stored in Assets/StreamingAssets/DialoguePortraits/.

Voila! The new Dialogue table that contains the auto-increment primary key ID (int) and an instance field that contains the Who/When/Where of every dialogue sequence in a single string. This naming convention makes it SO much easier to point to any piece of dialogue and its relative components without cluttering the database table with extra fields and unnecessary pointers. It also allows other team members to write their own dialogue scripts for any scene without getting bogged down in the nitty-gritty code details.

How does this Who/What/When/Where dialogue system work when putting the pieces together? Let’s go back to our initial steps for extracting dialogue during gameplay and fill in the detailed pieces:

(1) The player walks up to an NPC- The player will use raycasting to check if there is an NPC with a dialogue interaction component within a 45 degree radius of their forward facing direction.

(2) A dialogue box gets triggered to appear- If the player presses the interact button the dialogue control script is prompted to open using the ShowDialogue() method, which cues an animation for the panel to pop in.

(3) The dialogue box displays the current speaker’s character portrait and name- The Dialogue Control script will parse the dialogue component attached to the NPC and assign all the necessary fields for where to find the character’s name, body text, and character portrait which will be assigned to the UI elements in the dialogue panel.

(4) The player and the NPC go back and forth until the dialogue has finished-When the player clicks the bottom right-hand button in the dialogue panel, the Dialogue Control script will call the Next() method to skip to the next dialogue element on the list for the current sequence.

(5) The dialogue box is closed once the player has finished reading- Once the player has reached the end of the sequence the Dialogue Control script’s ToggleCloseButton() method will be called to hide the next button and show the close button, which has a flashing animation that highlights the button so the player will notice it’s time to close the box.

That covers my current implementation of the dynamic dialogue database system. I plan on using similar database tables for read/write access to the inventory, loot drops, enemy spawning, and storing player stats. For now I will continue to fine tune the current system to iron out all of its kinks before moving on to the another component of our game’s functionality.

I hope this post was helpful to anyone attempting this Unity game development feature, and I would love to hear any feedback in the comments if you know a better solution for this! Stay tuned for Part 2.

Resources:

Unity Game Development
Game Design
Game Development
Game Developer
Sqlite

--

--

Sonya Rivers
Just 3 Interactive

Written by Sonya Rivers

5 Followers
Writer for

Game Developer, part time mage

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams