FAQ

[echo17]

Q: What is the best way to store my game objects?

I don't believe there is a best way, but I will share the way that I personally use.

Instead of trying to store each GameObject / ScriptableObject / etc. in its own data table, I serialize the entire object, or even the set of objects into a string. Then I can just store the string itself in a simple data table that consists of minimal fields. This is actually the method that Unity uses to stores its own internal state of the GameObjects / ScriptableObjects.

public class SerializedState
{
    public int UID { get; set; }
    public string State { get; set; }
}

Serializing is trivial and can be done with a few lines of code:


        public static string SerializeToString(object value)
        {
            if (value == null)
                return null;

            var binaryFormatter = new BinaryFormatter();
            using (var ms = new MemoryStream())
            {
                binaryFormatter.Serialize(ms, value);
                return Encoding.ASCII.GetString(ms.ToArray());
            }
        }

        public static T DeserializeFromString<T>(string data)
        {
            var byteArray = Encoding.ASCII.GetBytes(data)
            var binaryFormatter = new BinaryFormatter();
            using (var ms = new MemoryStream())
            {
                ms.Write(byteArray, 0, byteArray.Length);
                ms.Seek(0, SeekOrigin.Begin);
                return (T)binaryFormatter.Deserialize(ms);
            }
        }


You could then use this to store an object's data in your database like:



MyObjectType myObject;
SerializedState state;

state.State = SerializeToString(myObject);



Some examples:


int myIntObject;
state.State = SerializeToString(myIntObject);
// Obviously this example is a bit silly, but it still shows that you can serialize anything.

MyCustomClass myCustomClass;
state.State = SerializeToString(myCustomClass);

List<GameObject> gameObjects;
state.State = SerializeToString(gameObjects);



MyObjectType above could be anything from simple types like int, string, bool to more complex types like custom class, ScriptableObjects, GameObjects. It could even be an array, list, dictionary, etc. of any type. The serializer can handle just about anything.

Getting data back out of the database could be done like:



MyObjectType myObject;

myObject = DeserializeFromString<MyObjectType>(state.State);



You can see that serializing your data is very powerful for a few reasons:

• You are not limited to the types available in the database. Everything is stored as a string on the database.


• You do not have to reflect the changes you make to your object in the database. You can add, remove, or completely change your objects and the database will always just store a string.


• You can store a single object, a collection of objects, or an entire game state all in a single record.


• Since everything is stored in a single record, you could easily keep track of multiple states in your game. This opens up possibilities for processes like Undo / Redo, rewinding to a previous game state, playing back an entire game based on the states. The possibilities are endless.

You will, however, need to be sure you are storing and retrieving the right structure of the object. If you change your object's structure, you will need to be sure you are deserializing back to the right structure. This could be handled with something like a version number:

public class SerializedState
{
    public int UID { get; set; }
    public int Version { get; set; }
    public string State { get; set; }
}

You would then just need to select the correct object type to deserialize to based on the version number of the state.

You are not limited to byte serialization. There are a number of serializers out there, but one I would recommend is FullSerializer. It also happens to be free, so there is no risk in trying it. Json serialization is lighter than binary serialization, so it will use up less storage in your database. FullSerializer also has an option to byte serialize. Check out FullSerializer here:

github.com/jacobdufault/fullserializer

[echo17]

Q: Does SimpleSQL support encryption?

Please check out SimpleSQL v2.13.0+ for a demo on how you can incorporate encryption into your project.

The public version of SQLite (The underlying library that manages the database connection, which is separate from SimpleSQL, a wrapper for the SQLite library) does not support encryption. In order to use encryption, you have to own a third-party license to the SQLite Encryption Extension (SEE). Because of this, I cannot include the extension in my library. See this link for more information: link

SimpleSQL comes with the source code, so if you are interested in using the third-party libraries you can purchase a license, incorporate them, and recompile the SimpleSQL library. See this link for information on how to compile SimpleSQL: link

One thing to keep in mind, mobile platform development (Apple at least, most likely Google too since this is a federal law requirement) requires you to complete extra steps if you are using encryption at this level. Some of these include supplying encryption compliance documentation and requiring the user to enable pin access on their device. Other steps may be required as well.

Also, if you use encryption on a database, you won't be able to open and edit the database in any third party tool. This makes it difficult to make modifications as your game develops and evolves as you will have to do every update in code.

If performance becomes an issue, you could remove the encryption / decryption from the property level and just do a bulk load / save encryption when reading and writing to the database. My example was just to show one way of handling encryption behind the scenes with little developer interaction. You could certainly improve performance if you are not concerned with having to provide a little more code at database interaction times.

[echo17]

Q: How do I make my database read-only so that no one can access the database?

Unfortunately, sqlite (the underlying database system for SimpleSQL) is a file-based database system. The database file in your project gets compiled by Unity as an embedded resource, so it can not be read like a file. This is why the plugin copies the database from your embedded location to a file location. This process is the same as it is on the native mobile libraries that sqlite uses. SimpleSQL tries to simplify the process so you do not need to worry about doing this copy.

Fortunately, there is a simple solution to remove access to the database when your application is not running. When your application closes, you can close the database and delete it. In your script you can set up something like (assuming your reference to the SimpleSQLManager is called dbManager):


    void OnApplicationQuit()
    {
        try
        {
            dbManager.Close();
            dbManager.Dispose();

            var filePath = System.IO.Path.Combine(string.IsNullOrEmpty(dbManager.overrideBasePath) ? Application.persistentDataPath : dbManager.overrideBasePath, (dbManager.changeWorkingName ? dbManager.workingName.Trim() : dbManager.databaseFile.name + ".bytes"));
            System.IO.File.Delete(filePath);
        }
        catch (System.Exception ex)
        {
            Debug.LogError(ex.Message);
        }
    }


Note: this only works if you are not updating data at runtime. Otherwise you will be deleting any changes made during runtime.

[echo17]

Q: How do I use SimpleSQL with Playmaker?

Please see the SimpleSQL Playmaker Quickstart guide here: link

This file is also found in the Playmaker Actions script which can be downloaded here: link

[echo17]

How do I download the latest version of the asset?

Unity allows developers to submit assets that will work on specific versions of the Unity Editor. To ensure that you have the latest version of the asset, please upgrade your Unity Editor to the latest version before downloading / importing the asset.

Sometimes Unity will not automatically show you that an asset has an update, so you will need to do a few extra steps to get the latest version.

Open the Asset Store window in the Unity Editor. Click on your asset list icon:



Click on the My Downloads link:



Scroll to find your asset, then click the Update button. Once it is downloaded, you can then import.



After you have imported the update, you will need to go to the Unity editor menu Tools > SimpleSQL > Options, then select a different platform (it does not matter which one), wait a few seconds, then switch back to the platform you had originally set. This will ensure that the latest version of the editor code has been extracted to your project.

[echo17]

How can I fix the error "Missing Dll resource: SimpleSQL.Resources.libsqlite3.so.resource"

Please see this link: stackoverflow.com/questions/39477616/how-can-i-prevent-embedded-resource-file-culture-being-set-based-on-its-filename

[echo17]

How do I compile as a UWP app?

You can modify the source code and remove the LoadRuntimeLibrary() from the Awake() method (see this FAQ for information on how to modify the source).  Then in the Visual Studio solution that Unity generates add the extension SQLite for Universal Windows Platform in the references for the UWP Project.

[echo17]

What is a good program to use to create and edit databases?

I highly recommend DB Browser for SQLite

link

It works on several platforms and even comes with a portable version for Windows. Best of all, it's free!

[echo17]

on Android I am getting the error: DllNotFoundException: Unable to load DLL 'sqlite3': The specified module could not be found.

If using SimpleSQL 2.5.0 or earlier, please update to a newer version. For best results, be sure you are on the latest version of Unity and Android.

After updating, please be sure to go into Tools > SimpleSQL > Options and select any platform besides Android, wait a few seconds for Unity to finish compiling, then select Android again. This will extract the appropriate sqlite library into the Plugins/Android directory of your project.



In Player Settings, be sure you have the scripting backend set to IL2CPP, The API compatibility Level set to .NET 4.x, and both ARMv7 and ARM64 checked in Target Architectures



If you still experience issues, try different Minimum API Levels for Android



You can always download the latest Android sqlite library from sqlite.org: link Look for "Precompiled Binaries for Android" section and download the aar package from there. Place the aar package into your Assets/Plugins/Android folder of your project. Note: not all libraries will work with Unity, so some experimentation with different versions may be necessary.

[echo17]

How do I fix a "Busy" error


This is often caused by having two or more connections open to a database simultaneously. Please be sure you:

1. Close your database connections before switching scenes
   dbManager.Close();
dbManager.Dispose();

   
2. Don't have more than one SimpleSQLManager component pointing to a single database in a scene.
   

Please see this link for more information on the error: link

[echo17]

Why am I getting the error "database disk image is malformed"?

This sometimes happens if you have a database name that includes unusual characters. If you are overwriting the database name in the SimpleSQLManager, please try using a simpler name.

[echo17]

Why am I getting the error "SQLiteException: file is encrypted or is not a database"?

Everyone that has contacted me about this error claims it goes away when using a different database administrator. It seems SQLite Administrator is the biggest cause of this issue. From one of my clients:

"Just wanted to drop a comment that might help others:
I was using the sqliteadmin program suggested in the documentation, but it saved the file in some wrong version which gave me the error that the file was either encrypted or not a database. After i switched to the firefox plugin, everything worked fine.

So if you get that error try another admin tool."

I have also had great success with DB Browser for sqlite. Please see this FAQ for more information: FAQ

[echo17]

How do I keep a persistent connection to my database between scenes?

One way to do this would be to create a singleton that does not get destroyed when a new scene is loaded. Something like:

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using SimpleSQL;

public class DBSingleton : MonoBehaviour
{
   private static DBSingleton instance;

   public static DBSingleton Instance { get { return instance; } }

   public SimpleSQLManager dbManager;

   private void Awake()
   {
       if (instance != null && instance != this)
       {
           Destroy(this.gameObject);
       }
       else
       {
           instance = this;
           DontDestroyOnLoad(this.gameObject);
       }
   }
}

You can then reference this singleton from other scripts like:

var results = DBSingleton.Instance.dbManager.Query<Weapon>("SELECT * FROM Weapon");

[echo17]

How do I build for multiple platforms without having to switch sqlite libraries in the options menu?

Sqlite libraries differ for various platforms, so to get around including all libraries in every project (creating lots of bulk), I put in place a way to switch out which library you would like to use, depending on which platform you are building for. This is done through the Tools > SimpleSQL > Options menu, but you can also program your own custom build pipeline if you wish.

Attached is a custom build example that might give you a good start on this. Place this in your SimpleSQL/Editor path inside your project. To Use, Go to the Tools > SimpleSQL > Build menu and choose the platform to build for.

You'll probably want to tweak the scenes, output files, and build options for each platform, but this should get you going at least. The extraction of sqlite libraries and SimpleSQL runtime dll is pulled straight from the source code at SimpleSQL_Library/SimpleSQL_Editor/OptionsEditorWindow.cs and EditorHelper.cs.

BuildAllPlatforms.cs (11.13 KB)

[echo17]

Q: How do I update my project database with the changes made to the working database at runtime?

One way you could accomplish this is to have an editor script that automatically updates the project database when the scene is stopped playing in the editor.

See this script for an example:

UpdateProjectDatabase.cs (696 B)

This file goes in a folder named Editor in your Assets path:



You will need to update the constants in it to reflect the working database name and the project database path.