Unofficial Pokemon Board Game – SQLite

If you’ve followed along with me in the past, you have probably seen me use project assets such as prefabs or scriptable objects as a sort of database. While this approach has certain conveniences such as the ability to make edits in the inspector and connect references with other project assets, it has the downside of being a little fragile. In this project I decided to use SQLite as my vessel for storing Pokemon data. While it can only store basic data types, SQLite still has plenty of other strengths to offer.

SQLite

While working with SQLite I made frequent use of a free tool called SQLite Browser. It isn’t required, but it is a good visual editor and can be helpful for visualization and debugging purposes.

To work with SQLite in Unity I used a plugin called SQLite4Unity3d. It is already compatible with mobile (iOS and Android) as well as desktop. I have already included it in our project for you, but you still may like to checkout the link for an overview and platform specifics.

In the Scripts/Common/Data folder are a few reusable scripts which I have left in tact in the repository for you already. The SQLite script was copied from SQLite4Unity3d, but the other two scripts in that folder are custom.

When you want to use a Database as a project asset, it should be added to the “StreamingAssets” folder. Note that this is another Unity specific folder much like “Resources”, and it is case sensitive and has no space in its name. On some platforms, the contents of this folder will be copied to another directory, but other platforms such as Android make a single binary. In that case, the database would need to be copied out to a new location for reading and writing. I created the StreamingAssetCopier script in this directory for that purpose, although I haven’t actually tested it yet. If any of you are Android users I would be happy to know if it worked for you or not.

The last script in the “Data” folder is the DatabaseController. This script works with the code provided by SQLite4Unity3d to manage the connection to, and potentially creation of, a database. You create an instance of the class by passing in a file name for the database, and it will automatically build three paths from there based on built-in Unity paths:

  • assetFilePath – It will look in the “StreamingAssets” path for a database by the given name.
  • tempFilePath – A custom temp sub directory added to the “Application.persistentDataPath”. I could use this for games that are in progress but which have not been intentionally saved. It is a sort of buffer to keep me from accidentally corrupting any saved game data.
  • saveFilePath – When loading a game, it will look here first, and fall back to the assetFilePath if no save data is present. The path uses the “Application.persistentDataPath”.

There are only two public methods in this script, one for “Load” and one for “Save”. The “Load” method allows you to pass several flags that help indicate how to treat the database. A readonly connection will be treated differently than a read-write connection. Also, since “opening” a database may require an async file copy, I include a callback action called “onComplete” which will be invoked whenever the process is finished. As a convenience when saving, I left a Debug.Log that prints the saved file path to the console. This way you can navigate to that directory for debug purposes if necessary.

Demo

Let’s get our feet wet with these libraries and perform the basic functions needed to work with a databse.

Test 1

First, let’s allow the SQLite tool to create a database for us. Note that this required a bug fix in the “DatabaseController” – I had to add the following to make sure the “Temp” directory existed before the SQLiteConnection class would create a database there.

// Add this to the "LoadReadWrite" method on line 64, before the statement "DidLoad"
var tempPath = Path.Combine (Application.persistentDataPath, "Temp");
if (!Directory.Exists (tempPath))
	Directory.CreateDirectory (tempPath);

I created a new scene and script called DatabaseDemo. Below is the script:

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using SQLite4Unity3d;

public class DatabaseDemo : MonoBehaviour {

	DatabaseController databaseController;

	void Start () {
		databaseController = new DatabaseController ("Test.db");
		var openFlags = SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite;
		databaseController.Load (openFlags, DatabaseDidLoad);
	}

	void DatabaseDidLoad (DatabaseController sender) {
		Test1 ();
		databaseController.Save ();
		Debug.Log ("Done");
	}
		
	void Test1 () {
		// Don't need to do anything - the database was created when it was attempted to be loaded
	}
}

All this first version does is to look for a database, load it (if it exists, create otherwise) and establish a connection with it. This version creates a database called “Test.db” because I hadn’t already provided one in the “StreamingAssets” folder. Because I tell the databaseController instance to “Save”, it will then clone the database at the “Temp” folder to the normal persistent data path.

Make sure this script is attached to an object in a new scene and run it. Then open the save file path and verify that a database was actually created!

Test 2

Next, let’s create a table. Add the following class (before the definition of the DatabaseDemo class):

public class Foo {
	[NotNull, PrimaryKey, AutoIncrement]
	public int id { get; set; }
	public int count { get; set; }
	public string name { get; set; }
	public double measure { get; set; }
}

…and then add this method to the end of the DatabaseDemo class after the “Test1” method:

// Creates a table for "Foo"
void Test2 () {
	databaseController.connection.CreateTable<Foo> ();
}

If you modify the “DatabaseDidLoad” method to run “Test2” instead of “Test1” it will load the existing database, and then add a new Table using the properties defined in Foo to create the column names and data types. Because I added a few tags to the “id” property it will also apply those flags to the column.

Run the scene again and then check out the database. It will have been created like so:

 photo GeneratedFooTable_zpsglxf4upv.png

Test 3

Now that we have a table, we can add some entries.

// Create a few entries for "Foo"
void Test3 () {
	databaseController.connection.CreateTable<Foo> ();
	List<Foo> foos = new List<Foo> (3);
	for (int i = 1; i <= 3; ++i) {
		var foo = new Foo {
			count = UnityEngine.Random.Range(0, 100),
			name = string.Format("Foo {0}", i),
			measure = UnityEngine.Random.value
		};
		foos.Add (foo);
	}
	databaseController.connection.InsertAll (foos);
}

Note* – I don’t manually assign the “id” field when creating a Foo instance. Since we used flags on the property, the field will be automatically updated when it is inserted into the database.

Note* – There is also an “Insert” method which would allow me to insert one Foo instance at a time. I used “InsertAll” to add them all in one pass.

Change the “DatabaseDidLoad” method to invoke our “Test3” method instead of “Test2”. Now run the scene and open the database to see the results:

Generated Foo Entries photo GeneratedFooEntries_zpscqjxrjou.png

Test 4

Now let’s fetch and modify an entry in the table. Add the following Test:

// Fetch an entry and modify it
void Test4 () {
	var foo = FetchByID (2);

	// Read
	Debug.Log ("Current name: " + foo.name);

	// Or Write
	foo.name = "New Name";

	// Apply changes
	databaseController.connection.Update (foo);
}

Foo FetchByID (int id) {
	return databaseController.connection.Table<Foo> ()
		.Where (x => x.id == id)
		.FirstOrDefault ();
}

Hopefully you are seeing a pattern here… change the “DatabaseDidLoad” method to invoke our “Test4” method instead of “Test3”. Now run the scene and open the database to verify that our change was successfully applied.

Test 5

For our final test, let’s see how to remove an entry from a table.

// Remove an entry
void Test5 () {
	var foo = FetchByID (3);
	databaseController.connection.Delete (foo);
}

For the last time, let’s change the “DatabaseDidLoad” method to invoke our “Test5” method instead of “Test4”. Now run the scene and open the database to verify that our change was successfully applied.

Manual Database Creation

If you had created a database manually, such as by using SQLiteBrowser, then the data types you see will look a little different than the ones which were automatically created by our code. Some of the SQLite datatypes you might see are:

  • INTEGER
  • TEXT
  • BLOB
  • REAL
  • NUMERIC

You can read more about the SQLite datatypes here, but a quick overview is that each one can be mapped to an equivalent data type in C#. For example, I would use “int” for “INTEGER”, “string” for “TEXT”, and a “float” or “double” for “REAL”. The demo code above would work fine using a manually configured database with these datatypes.

Summary

In this lesson, I discussed a few new scripts which I added to my ever growing list of reusable scripts. With these additions it is much easier to work with a SQLite database. I demonstrated their use in a demo where we covered some important basics including programmatically creating a database, table and entries, as well as discussed how to fetch, modify and delete the objects in our table.

Don’t forget that there is a repository for this project located here. Also, please remember that this repository is using placeholder (empty) assets so attempting to run the game from here is pretty pointless – you will need to follow along with all of the previous lessons first.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s