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:

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:

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.

15 thoughts on “Unofficial Pokemon Board Game – SQLite

    1. I’m not an SQLite expert… I’m also not sure what a BBDD is. I haven’t pushed the library to its limits but it looked pretty capable and I think you can even pass it straight SQL query string in case the helper code isn’t flexible enough. Hope that helps.

      1. Sorry, BBDD is DataBase in english, I wrote the spanish acronym:D

        I will dig into Sqlite script, I did a short read so I sure I missed something. Thanks for the quickly answer. You are really helpful with your tutorials.

    1. Good to know, thanks. Out of curiosity though, where did it get stuck? Did it copy the database, and if so, could it establish a connection?

      Also, just in case you wondered, the project database only currently holds a few table definitions, but no other data.

      1. I didn’t test so much due work. I used my own database and I changed DatabaseDemo to adapt my own tables. In a windows 64 bits, work perfectly. On w 32bits and Android, it doesn’t work.

        I updated DLL from githud repo with the new ones and fail too. In one hour, I could add more info

      2. It seems my comment it wasn’t submit a few days ago 🙁

        You should check SQLite4Unity3D repository. They changed android DLL. I downloaded their files and I use their “DataService” to control SQL querys. And it works nice.

    1. Thanks for the heads up. Also, I did know that the database merely held table definitions – gathering or generating the data was left as an exercise for the reader given the copyrighted nature of the project.

      FYI, the author of the SQLite4Unity plugin has videos specific to windows and android. So it “should” be able to work. Perhaps you can get further assistance here:
      https://github.com/codecoding/SQLite4Unity3d

  1. For yall that are trying to use the Commmon scripts, it shows a warning because WWW it’s obsolete.

    The “fix” of the StreamAssetCopier method “Copy” could be something like this (I left the structure as is)

    IEnumerator Copy () {
    UnityWebRequest www = UnityWebRequest.Get(streamingAssetFilePath);
    yield return http://www.SendWebRequest();
    File.WriteAllBytes(destinationFilePath, http://www.downloadHandler.data);
    if (onComplete != null)
    onComplete();
    Destroy(gameObject);
    }

  2. I am having issues with EQLite script having a ton of errors relating to Type such not containing certain defintions and “==” not working

Leave a Reply to Rafael Caballero León Cancel reply

Your email address will not be published. Required fields are marked *