Introduction


SQL for Unity (SQL4Unity) is a Relational Database Management System (RDBMS) using the SQL language as its primary interface.

This RDBMS has been developed especially for Unity and has no external requirements or dependencies other than Unitys' Engine and Editor. It supports many serializable data types defined within Unity directly such as Vectors and Colors and all Unity classes derived from Unity.Object and loadable as a Unity Resource such as Sprites and Prefabs.

The RDBMS is not intended as a replacement for client/server database systems such as MySQL but rather as a system for integrating a local RDBMS into Unity projects.

This version of the RDBMS was written in C# with Unity .Net 3.5 full base class libraries and tested using Unity 2017.4, 2018.4 and 2019.2 for Standalone, WebGL, Andriod 7 and IOS 11 but should function on all platforms supported by Unity.

This manual is not intended as an introduction to Database design or to using the SQL language. An understanding of the basic SQL commands will be assumed through out this documentation.

The code that makes up this RDBMS is copyright SteveSmith.SoftWare 2018. Unless specifically stated in the code file or this documentation no part of the system may be copied or modified. Neither may it be stored in such a way that would make it available to the public outside of the Unity Asset Store.

This documentation is available online at https://stevesmith.software. You will also find there the form for filing a bug report should it be necessary.

Steve Smith 2018.

Installation

Please read the files Assets/SSSoftware/SQL4Unity/ReadMe and Assets/SSSoftware/SQL4UnityExample/ReadMe before using SQL for Unity.
To start SQL4Unity in the Unity Editor go to Tools->SQL for Unity->Workbench
The Assets/StreamingAssets folder will be created in your project if it does not exist when a database is first created from the Workbench.

About SQL for Unity

SQL for Unity (SQL4Unity) consists of 4 main elements:-

SQL Workbench A Unity Editor window for defining and creating databases

SQL Table Editor A Unity Editor window for editing database table data

SQL Execute The programatic interface to interact with a database at run time

SQL Result The class containing the output generated by the SQL Execute interface

As well as many usual datatypes SQL for Unity also supports a range of Unity specific datatypes

Resources Management Manage Unity Objects used in the database

SQL Workbench

The SQLWorkbench is the module where databases can be defined and created/updated
To access the workbench use the Tools -> SQL for Unity -> Workbench menu option in the Editor.



The first time that you open the workbench a default Schema file will be created. This file will hold all of the definitions for the databases that you subsequently define.



Important:- The Schema file is stored in the SQL4Unity/Editor/Resources folder and should NEVER be modified other than through the Workbench module.




The workbench has 6 buttons at the top of the window

Open Schema - Opens an alternative schema file
Rename Schema - Renames a schema
Compact Schema - Reorganises the Schema to reduce disk space
Import Schema - Import a Database definition from a .schema file. Refer to DataBase -> Export Schema for more information
Help - Opens a new window showing this help file
About - Opens a new window containing the links for error reporting

The workbench window is split into three sections.

Data Definition
SQL Command
Messages & SQL Output

Data Definition

Naming Conventions

Database, Table, Column and Index names must comply with the following rules
Length: between 1 and 20 characters.
Contents: a-z A-Z 0-9 _
Note: Names are not case sensitive

Working with Databases

Defining a Database

To define a new Database definition click on the Define Database button. You will be prompted to enter a name for the database. Press OK and the database will appear in the workbench and be selected as the active database.



Note: The database name must be unique within the Schema.



Renaming a Database

Select the database you wish to rename using the toggle to the left of the database name. The Options button will appear for that database. Press the Options button and select Rename. You will be prompted to enter a new name. Press OK and the database will be renamed.





Note: The database name must be unique within the Schema.



Deleting a Database

Select the database you wish to delete using the toggle to the left of the database name. The Options button will appear for that database. Press the Options button and select Drop. You will be prompted to confirm the deletion. Press Continue and the database will be deleted.


Export Schema

Select the database you wish to export using the toggle to the left of the database name. The Options button will appear for that database. Press the Options button and select Export Schema. You will be prompted for a file name . Press Continue and the database definition will be exported.

Export Data format is as follows

DROP DATABASE database
CREATE DATABASE database
DROP TABLE database.table
CREATE TABLE database.table
DROP COLUMN database.table.column
CREATE COLUMN database.table.column ( TYPE: datatype, NULL: True/False, AUTO: True/False, KEY: True/False, VALUE: default value )
DROP INDEX database.table.index
CREATE INDEX database.table.index (TYPE: Dict/Sorted, UNIQUE: True/False, COLUMNS: column column )

For an example of an exported schema refer to SQL4Unity\Example\MyGameDB.schema

Working with Tables

Defining a Table

Select the database for which you wish to define a new table using the toggle to the left of the database name. Press Define Table. You will be prompted to enter a name for the table. Press OK and the table will be created in the Schema.



Note: The table name must be unique within the Database.



Renaming a Table

Select the table you wish to rename using the toggle to the left of the table name. The Options button will appear for that table. Press the Options button and select Rename. You will be prompted to enter a new name. Press OK and the table will be renamed.



Deleting a Table

Select the table you wish to delete using the toggle to the left of the table name. The Options button will appear for that table. Press the Options button and select Drop. The table will be deleted.

Duplicating a Table

Select the table you wish to duplicate using the toggle to the left of the table name. The Options button will appear for that table. Press the Options button and select Duplicate. You will be prompted to enter a name for the table. Press OK and the table and all of its columns and indexes will be duplicated.

Working with Columns

Defining a Column

Select the table for which you wish to define a new column using the toggle to the left of the table name. Press Define Column. You will be prompted to enter a name for the column. Press OK and the column will be created in the Schema.



Note: The column name must be unique within the Database.



Columns are created with the following default atributes:
Data type : string
Allow Nulls: false
Auto Increment: false
Primary Key: false
Default Value: None
To change the Data type of the column click on the dropdown and select from the list provided.



If the column should allow null values click on the toggle. For more information about null values in SQL4Unity see the DataTypes section below.



If the column has the data type 'int', 'short or 'long' you may select that the column be automatically incremented during insert. If this is desired click on the toggle.



If you wish the column to have a unique Index generated for it click on the Primary key toggle.



If you wish to define a default value for a column, to be used for inserts when a column has been omitted, Press Edit. You will be prompted to enter a value. Press OK and the value will be shown in the workbench.







Please refer to the section below on Data Types for more information about which data types are supported and the syntax required when using them.

Renaming a Column

Press the Options button at the end of the column definition and select Rename. You will be prompted to enter a new name. Press OK and the column will be renamed.



Deleting a Column

Press the Options button at the end of the column definition and select Drop. The column will be deleted.

Moving a Column

Press the Options button at the end of the column definition and select either Move Up or Move Down. The column will be moved within the table

Working with Indexes

Defining an Index

Select the table for which you wish to define a new index using the toggle to the left of the table name. Press Define Index. You will be prompted to enter a name for the index. Press OK and the index will be created in the Schema.



Note: The index name must be unique within the Table.



Indexes are created with the following default atributes
Type : Sorted
Unique: true
Columns: None

To change the type of the index click on the type dropdown and select from the list provided



The options are :-
Dict - Dictionary. Best for Auto Increment and Join columns where access will be on equality.
Sorted - A Sorted Dictionary. This type of index will be slightly slower during inserts but will be the faster for searches of all types.

If the index should only allow unique values leave the toggle selected otherwise turn it off.



To add or remove columns to/from the index click on Add/Remove Column and select from the list provided.
The index data type will reflect the data type of the column selected. If more than one column is selected for an index the data type will be multiple.
Only columns which use a C# system.valuetype data type are allowed to be indexed.



Renaming an Index

Press the Options button at the end of the index definition and select Rename. You will be prompted to enter a new name. Press OK and the index will be renamed.



Deleting an Index

Press the Options button at the end of the index definition and select Drop. The index will be deleted.

Creating the Database.

All of the above actions have, up until now, only been recorded in the Schema. In order for the actual database file to be created or updated it must first be created.







Select the database to be crreated using the toggle to the left of its name and Press the options button next to it. Select the option Create.



The database will now be created and will be stored in the Assets/StreamingAssets folder with a .s4u file extension the actions taken by the workbench will be shown in the message area.

Important : The database will be accessed from this folder when SQLExecute is run from within the editor. When SQLExecute is run from a built Unity project the database will be copied to the Application.persistentdata path if it has not already been so.

Note: If you wish to access a database from a location other than Application.persistendata path you can use the .Open() and .OpenAsync() methods of SQLExecute and supply a path name. You must have read/write access to this location. Alternatively you can use .Open() or .OpenAsync() to copy a database to Application.persistendata. For more information please refer to the sections SQLExecute and WebGL below.





For each table in the database a C# script will be generated and stored in the SQL4Unity/Scripts folder. These scripts will be named databasename_tablename.



Important: The database file is a proprietary format and should NOT be modified outside of the RDBMS system.

If the database already exists you will be prompted to Replace or Update the database. If the database contains no data you wish to retain choose Replace otherwise choose Update.



Warning: Updating a database containing data may result in data loss. If, for example, the datatype of a column has been changed and the existing values in the database are of an incompatible data type this information will be lost.

Once that database has been created 4 more options are added to the database options menu



Import
The data from a flat file will be imported into the selected database. Any exiting table data will be lost.

Note. Lines starting with double slash // will be treated as a comment and ignored.

Export
The data from the selected database will be written to a flat file (.sql) in SQL INSERT statement format.

Compact DataBase
The data from the database will be exported to a temporary .sql file. The database will be recreated and then the data imported from the temporary file

Refresh Resources
Update the database with the current Unity Object Resource data.
See Resource Management for more information

SQL Command.

The SQL Command section allows you to enter, parse and execute SQL statements against the selected database once it has been created. This can be very helpful in validating SQL commands before implementing them in your Unity Engine code.

In order to facilitate this the table options menu has 6 built in commands available. To access these commands select the table using its toggle, press Options and choose from the following options



View Data - The SQL Command 'SELECT * FROM table' will be executed and the results displayed.
Truncate Table - The SQL Command 'TRUNCATE table' will be executed
Make Insert - A template INSERT command for the table will be displayed in the SQL command text area.



Make Select - A template SELECT command for the table will be displayed in the SQL command text area.



Make Update - A template UPDATE command for the table will be displayed in the SQL command text area.



Make Delete - A template DELETE command for the table will be displayed in the SQL command text area.



Once a command has been entered you can ensure that it is a valid command by pressing the Parse button. Any parse errors found will be shown in the message area.

Pressing the Execute button will execute the entered command against the selected database and the results of the command will be shown in the message area.

Messages & SQL Output.

All of the output from the DataBase->Create, PARSE and EXECUTE will be displayed here. Press the Clear button to clear the output.

The extended documentation on https://stevesmith.software contains more detail about the parse error messages.

SQL Table Editor

The table editor allows you to manipulate the data within an SQL4Unity database without having to use SQL statements.

Click 'Open Database' to select a database. A file select window will be shown of the current project Assets/StreamingAssets folder and all SQL4Unity databases (file extension .s4u)
Select a database and click OK.
Note: Only databases within the current project may be opened.
Click 'Help' to open this help text.
Note:After opening a database a button 'Refresh Resource Files' will appear. Click this button to update your database with the latest Resources in your project.
See Resources Management section for more information.



Once a database has been opened a list of the database tables will be shown in the Database window/
To open a table click on the table name.
To close a table click on the name again.



Once a table has been opened a list of the table columns will be shown in the database window and a table window will be opened showing the table data
The column list will show the column name, it's data type and a toggle.
If the column allows NULL values the datatype will have a '?' suffix.



Alongside the open table name a 'Options' button is displayed. This button shows the table level options.

	Refresh	Refresh the table data
	Sort Ascending	Show the table data sorted by ascending row id. (Default)
	Sort Descending	Show the table data sorted by descending row id
	Truncate	Reset the table to it's original empty state. Note: This option will delete all data in the table and cannot be undone.
	Import	Import table data from a .SQL file. Note: This will remove all existing data.
	Export	Export the table data to a .SQL file. Note: The data will be exported in SQL INSERT format.

Clicking on a column toggle will show or hide that column data in the table window.
Tip: This is useful to remove columns which have defaul values or allow NULL values from the table window when adding new rows.



The top row displayed in the table window is for adding new rows to the table.
Fill in all of the required columns and then press 'Add'
If there are any validation errors a message will appear at the bottom of the main window.



Right click on any column within a row to show the row menu

	Set to NULL	Set the column value to NULL
	Delete Row	Delete the row from the table

To change a column value select the field and enter the new data.
A 'Save' button will be shown at the bottom of the table window.
Once all edits have been made press Save to update the database.



You can have multiple tables open at the same time. Each table window can be dragged and positioned within the main window.



Right click on the column name in the table window to show the column menu.

	Filter coluumn name	Apply a filter to the column
	Sort Ascending	Sort the table by this columns values

Choosing the Filter option above will display a dialog box where a filter value can be entered.
Press OK to apply the filter.
Press Cancel to remove a filter.
Note: Filters may be applied to multiple columns at the same time.

SQL Execute

The SQL Execute module is used in your scripts to execute SQL commands against a database.

using UnityEngine;

public class MyClass : MonoBehaviour {

   SQL4Unity.SQLResult sqlResult;
   SQL4Unity.SQLExecute sql;

   void Start() {
      sqlResult = new SQL4Unity.SQLResult();
      sql = new SQL4Unity.SQLExecute();
      // Open the database MyDataBase
      if (sql.Command("use MyDataBase", sqlResult)) {
         // Select all rows from MyTable
         if (sql.Command("select MyName, MyScore from MyTable", sqlResult)) {
            // Iterate over the rows returned
            for (int i=0;i< sqlResult.Rows.Count;i++) {
               // Retrieve the row data
               mydatabase_mytable row = sqlResult.Get<mydatabase_mytable>(i);
               // Do something with the data
               string name = row.MyName;
               if (!sqlResult.isNull(i,"MyScore"))
                  int score = row.MyScore;
            }
         } else {
            // Select statement failed
            Debug.Log(sqlResult.message);
         }
      } else {
         // Open database failed
         Debug.Log(sqlResult.message);
      }
   }
}

The Command method will always return true if the command completed sucessfully, even if it resulted in 0 rows being affected. It will return false if any error occured.
Commands have the following syntax:

Insert into TABLE (COLUMN LIST) values (VALUE LIST)

Select COLUMN LIST from TABLE LIST [where COLUMN OPERAND VALUE ...] [Order By COLUMN LIST] or [Group By COLUMN LIST]

Update TABLE set COLUMN=VALUE,... [where COLUMN OPERAND VALUE ...]

Delete from TABLE [where COLUMN OPERAND VALUE ...]

Truncate TABLE

where TABLE is the table name

where TABLE LIST is a list of TABLE seperated by commas

where COLUMN is the column name

where COLUMN LIST is a list of COLUMN seperated by commas

where VALUE is an allowed value for the column data type

where VALUE LIST is a list of VALUE seperated by commas.

where OPERAND is one of =, !=, >, >=, <, <=, in, between

when OPERAND is 'in' VALUE must be (VALUE LIST)
when OPERAND is 'between' VALUE must be (VALUE,VALUE)
Note: The 2 VALUES in the between clause are inclusive

the COLUMN OPERAND VALUE sequence may be repeated using the words AND or OR seperated by spaces

when the Group By clause is used one or more of he following numeric functions may be included in the Select COLUMN LIST
Count(*) or
Count(COLUMN)
Sum(COLUMN)
Min(COLUMN)
Max(COLUMN)

See the Data Types section of this documentation for the correct syntax of each value data type.

The DELETE command will remove rows from the TABLE without affecting rowid or auto increment values.
The TRUNCATE command will remove ALL rows from the TABLE and reset rowid and auto increment values to zero.


SQLExecute Constructors


SQLExecute() : The standard constructor
SQLExecute(string Database) : Open the database named in the parameter
SQLExecute(MonoBehaviour mono) : Initialise SQLExecute for asyncronous database open
Note: This consructor must be used when targeting WebGL. Please refer to the section on WebGL below


SQLExecute Methods


bool Command(string query, SQLResult result) : Execute the SQL command stored in query and fill the SQLResult class. If the method returns false result.message will contain the reason

bool Command(string query, SQLResult result, SQLRow row) : This is a special case for the SQL Insert statement. Where the insert statement is formatted as
Insert into TABLE (COLUMN LIST) values ()
The column values will be taken from the supplied SQLRow instance. For example

            mydatabase_mytable row = new mydatabase_mytable();

            row.myColumn = 1;

            row.myColumn1 = "Hello world";

            sql.Command("Insert into mytable (myColumn, myColumn1) values ()", result, row);

     

bool Open(string name) : Open the database name at location streamingAssets/persistentData. If a database is already open it will be closed. Returns true if the datbase open sucussfully or False if an error occured

bool Open(string name, string path, bool copy) : Open the database name at location path and optionally copy it to persistentData. If a database is already open it will be closed. Returns true if the datbase copy and open are sucussfull or False if an error occured

bool OpenAsync(string name, Action<bool> callback) : Open the database name at location streamingAssets/persistentData. If a database is already open it will be closed. The method in callback is invoked when the database is opened

bool OpenAsync(string name, string path, bool copy, Action<bool> callback) : Open the database name at location path and optionally copy it to persistentData. If a database is already open it will be closed. The method in callback is invoked when the database has been copied is open

Note: The above two methods must be used if targting WebGL. Please refer to the section on WebGL below
void Commit() : If autoCommit is false changes will be written to the database

void RollBack() : If autoCommit is false any unsaved changes will be discarded

void Close(bool commit) : Close the database. If commit is true any unsaved changes will be written to the database.

void AutoCommit(bool commit) : Turns autoCommit on or off.

void Language(string language) : Changes the language of the system. Default EN - English

Targeting WebGL and Async database open

When targeting WebGL the constructor of SQLExecute must use the MonoBehaviour format.
The database can then be opened using one of the OpenAsync methods.
Once the callback from these methods has executed the database can then be used in the normal way.

Important: In order to access a database from a webserver the server must allow the file extension .s4u with a mime type of application/octet-stream

Note: If the OpenAsync(string Database, Action<bool> callback) method is called when running in the Unity Editor the database will be opened in the Application.streamingassets path

            
        using UnityEngine;
        using SQL4Unity

        public class OpenDB : MonoBehaviour
        {
            SQLExecute sql = null;
            SQLResult result = new SQLResult();

            void Start()
            {
                sql = new SQLExecute(this);
                sql.OpenAsync("MyDatabase", IsOpen); // Copy database from StreamingAssets to PersisentData then open
                // OR
                sql.OpenAsync("MyDatabase", "https://stevesmith.software", true, IsOpen); // Copy database to PersisentData from webserver then open
            }

            void IsOpen(bool ok)
            {
                if (ok)
                {
                    sql.Command("Select * from MyTable", result);
                    // Do somthing here
                    sql.Close(false);
                }
                else
                {
                    Debug.Log("Database unavailable");
                }
            }
        }
    

Note: Should you wish to access a database from another location using file:///, http:// or https:// protocols this structure can also be used. If you do not have read/write access to the location you must use the .OpenAsync() method with copy set to true so that the database can be copied to Application.persistentdata before opening
The database copy will ONLY be done if the database does NOT exist in Application.persistentdata it will NOT overwrite an existing database

SQL Result
The SQLResult class will contain all of the information resulting for the execution of an SQL statement and has the following properties

message : Error or Informational message about the parsing and execution of the command
lastID : The last rowId generated by an INSERT
rowsAffected : The number of rows selected, updated or deleted by the command.

And the following methods

Clear() : reset all variables

After SQL Select commands

Dictionary<int, Dictionary<string,object>> Get() : Returns all data rows in the rows collection as a dictionary.

Dictionary<string,object> Get(int rowIx) : Returns the data row at position rowIx in the rows collection as a dictionary.

database_table Get<database_table>(int rowIx) : Return the data row at position rowIx in the rows collection as a class.

database_table[] Get<database_table>() : Returns all data rows in the rows collection as a class array.

object Get(int rowIx, string column) : Returns the value of column from the data row at position rowIX of the rows collection. Column should contain the column name**.

T Get<T>(int rowIx, string column) : Returns the value cast to Type T of column from the data row at position rowIX of the rows collection. Column should contain the column name**.

object Get(int rowIx, int colIx) : Returns the value of the column from the data row at position rowIX of the rows collection and position colIx of the columns collection.

T Get<T>(int rowIx, int colIx) : Returns the value cast to Type T of the column from the data row at position rowIX of the rows collection and position colIx of the columns collection.

object[] GetColumn(string column) : Returns all the values of a column from every data row. Column should contain the column name**.

T[] GetColumn<T>(string column) : Returns all the values cast to Type T of a column from every data row. Column should contain the column name**.

object[] GetColumn(int colIx) : Returns all the values of a column from every data row in position colIx of the columns collection.

T[] GetColumn<T>(int colIx) : Returns all the values cast to Type T of a column from every data row in position colIx of the columns collection.


Important: Columns containing Null values will return the C# value DBNull.Value Except when using the Get<T> syntax when it will return default(T)
To Test for a Null value when using Get<T> use the method bool isNull(int rowIx, string columnname) or bool isNull(int rowIx, int columnIx)

List<string> GetColumnNames() : Returns a list of the columns selected in the query. The names are in the format database.table.column

** Column names must be in one of the following formats database.table.column, table.column, column.
NOTE: Both rowIx and colIx start from 0. object o = result.Get(0, 0) will return the first column value from the first row

Distribution

This RDBMS system comprises many C# scripts split into 3 plugin dll's. When you wish to distribute your finished product you are allowed and must include the compiled version of the scripts in the SQL4Unity/Scripts and SQL4Unity/Plugins/SQL4Unity.dll and SQL4UnityData.dll as these will be necessary to support the operation of your database at run time.
The SQL4UnityData.dll must always be the same version as was used to create the database. Using the wrong version will result in the database being unusable.
If your project fails to build for your selected platform make sure that the SQL4Unity/Plugins/SQL4UnityEditor.dll is marked as only Editor in the Inspector

Support

Whilst every effort has been made to test this software thoroughly in a system of this complexity it is always possible for bugs to have slipped through the net. Should you be unlucky enough to encounter such a bug please accept my humble apologies and raise a bug report.


To raise a bug report go to https://stevesmith.software and use the form provided or email SQL4Unity@stevesmith.software

Change Requests

This RDBMS only implements a subset of the SQL specification. This was a deliberate design decision in order to keep the runtime as small and fast as possible
If there is specific functionality which has not been implemented but which you feel should have been please raise a change request. I cannot guarantee that these will be implemented in any future releases but all requests will be seriously considered.

To register a change request go to https://stevesmith.software and use the form provided or email SQL4Unity@stevesmith.software

Internationalisation

The SQL4Unity/Resources folder contains a file called SQL4UnityEN.txt. This file contains all of the error and informational messages that are used in the system. You may change the wording or translate into anther language any of the text in this file. Do not change the sequence of the messages as this will cause problems with the functioning of the system.

Should you translate the entirity of this file into another language and wish to publish it please send a copy to SQL4Unity@stevesmith.software and if accepted it will be incorpoated into a future release of the RDBMS.

Data types

The following data types are supported by SQL4Unity.

C#

int - A 32 bit integer number
short - A 16 bit integer number
long - A 64 bit integer number
double - A number including decimal point but no seperators
float - A number including decimal point but no seperators. Terminated by a letter F
byte - A single byte. 0 - 255
char - A single character

string - A string literal. Strings must be enclosed in single quotes and may not contain a single quote

bool - Boolean. true or false. Boolean columns are always Not Null columns.

DateTime - A Date, Time or Date and Time. SQL4Unity uses a class, SQLDate, to represent dates and times. The methods .set(), .get() and .ToString() must be used to manipulate the data. The .raw() method returns the Date/Time in Unix format (Number of seconds since 1st January 1970). In SQL statements Dates are in the format 'YYYY-MM-DD' and Times in the format 'HH:MM' or 'HH:MM:SS' A Date and Time uses 'YYYY-MM-DD HH:MM:SS'.

Where YYYY is a 4 digit Year, MM is a 1 or 2 digit Month where January = 1 (1-12), DD is a 1 or 2 digit Day of month (1-31)
AND HH is a 1 or 2 digit Hour ( 0 - 23), MM is a 1 or 2 digit Minutes (0-59), SS is a 1 or 2 digit seconds (0-59).
The hyphens in the date string and the colons in the time string are required. The strings must be enclosed in single quotes.

The .ToString() method supports a format string, this string can be any C# DateTime format string.

To format a DateTime variable for SQL usage use the static SQLDate.ToString(DateTime date) method.
To get the current date and time use the static SQLDate.Now() method.
To get the current date use the static SQLDate.Today() method.
These methods include the necessary enclosing single quotes in their output string.

TimeStamp - A time stamp is a special form of DateTime and cannot be used in an Insert or Update statement. A TimeStamp takes two forms dependent upon the setting of the Allow Null option. The time stamp column will always be set to the current date and time during Insert. If the Allow Null option is off the time stamp column will also be set to the current date and time during Update.

Unity serializable types

Vector2 - 2 numbers in the form { n, n }
Vector2Int - 2 integers in the form { n, n }
Vector3 - 3 numbers in the form { n, n, n }
Vector3Int - 3 integers in the form { n, n, n }
Vector4 - 4 numbers in the form { n, n, n, n }
Quaternion - 4 numbers in the form { n, n, n, n }
Rect - 4 numbers in the form { n, n, n, n }
RectInt - 4 integers in the form { n, n, n, n }
Color - 4 numbers in the form { n, n, n, n }. Range 0-1 incl.
Color32 - 4 integers in the form { n, n, n, n }. Range 0-255 incl.

Unity Objects

GameObject - A reference to a GameObject in the form { NAME:name, TAG:tag } where either NAME or TAG or both must be supplied.

Note: The Game object datatype cannot be assigned a value in the Editor only at run time

Texture2D - A reference to a 2D texture in a Resources folder in the form { NAME:path/name }
Material - A reference to a material in a Resources folder in the form { NAME:path/name }
Sprite - A reference to a 2D texture to be used as a sprite in a Resources folder in the form { NAME:path/name }
AudioClip - A reference to an audio clip in a Resources folder in the form { NAME:path/name }
Resource - A reference to a resource in a Resources folder in the form { NAME:path/name }. Stored as a Unity.Object

Important:If your database uses Unity Objects it is important that the Resources within the database are kept up to date. See Resources Management for more information

Note: Those data types that are specified enclosed in braces { ... } are a non standard extension of SQL made specifically for use in this RDBMS to support the Unity data types.

If there any specifc Unity serializable types or objects that you would like added to the above list please email SQL4Unity@stevesmith.software with your request.

Resources Management

Due to the nature of Unitys AssetDataBase and the fact the Resource paths cannot be identified outside of the Editor SQL4Unity stores this information internally.
When a database is created the Resources folders are scanned and the information stored.
This information is updated when individual Resources are used and can also be Refreshed in the SQL Workbench, SQL Table Editor or by using the Menu option Tools -> SQL for Unity -> Refresh Resources
Finally in the folder SQL4Unity/Editor is the script SQLPreBuild which will run automatically when you build your project to ensure that the latest Resource information is present in the database at runtime.
If you do not use Unity Objects within your database this script may be deleted

Reserverd words

The following words are not allowed to be used for Database, Table, Column or Index names. All reserverd words are NOT case sensitive.

SQL Reserved words

insert
select
update
delete
create
drop
alter
from
into
set
where
order
by
group
having
join
left
right
null
not
primarykey
count
sum
min
max

C# reserved words

public
private
class
list
dictionary
using
true
false
if
else
for
foreach
do
while
until
hashtable
hashset
object
namespace

SQL4Unity reserved words

schema
database
table
column
index
indexdict
indexlist
indexsorted
indextree
indexrow
datarow
datatable
systable
workbench
datatype
utility
rowid
rowidix
dropped
btree
compare
datatype
reserved
name

In addition, the names asigned to data types (see above) are also reserved words.

Package Contents

The SQL4Unity package is made up of the following components:

Assets/SSSoftware/SQL4Unity/
   ReadMe.txt - Read this file before using SQL4Unity
   Editor
      SQLPreBuild.cs - Resource update on Build. Not included in build. This script may be deleted if you do not use Unity Objects in your database
      Resources - Editor resource files. Not included in build
         Schema.asset - The Database Schema file. Created by SQLWorkbench
         SQL4Unity.html - This documentation
   Scripts - Automagically generated Database table scripts are stored here
      mygamedb_players.cs - Example MyGameDB Players script. This script may be deleted
      mygamedb_positions.cs - Example MyGameDB Positions script. This script may be deleted
      mygamedb_playerpos.cs - Example MyGameDB PlayerPos script. This script may be deleted
   Plugins
      SQL4Unity.dll - The SQL4Unity RDBMS Engine
      SQL4UnityData.dll - The SQL4Unity Database data structures
      SQL4UnityEditor.dll - The SQL4Unity Unity Editor code
   Resources
      SQL4Unityxx.txt - SQL4Unity language files where xx is the language code. e.g. EN for English
      If informational and error messages are not required in production builds. These files should be deleted or moved
   Example - An example of SQL4Unity usage. This folder and its subfolders may be deleted if the example is no longer needed
      Example - Example Unity Scene
      ReadMe - Installation and usage instructions for the example
      MyGameDB.schema - Example SQLWorkbench schema export file
      Resources
         Capsule - Unity Prefab
         Cube - Unity Prefab
         Cylinder - Unity Prefab
         Sphere - Unity Prefab
         Schema.asset - Example SQL4Unity schema.
      Scripts
         InitDB.cs - Return the example database to its initial state
         SQLExample.cs - The example run time code
      Assets/StreamingAssets - The database files are stored here. Databases have the file extension .s4u
         MyGameDB - Example SQL4Unity Database. This file may be deleted

Backup and Restore

When you make backups of your data always include the following folders/files

Assets/SSSoftware/SQL4Unity/
   Editor
      Resources
         Schema.asset - The Database Schema file. Created by SQLWorkbench
         Schema.asset.meta - Unity meta file
   Scripts
Assests/StreamingAssets
To restore your files copy back all four of the folders mentioned above.

Failure to do so will result in inconsistancies between your databases and the schema file and may result in data loss.

To make a backup or copy for runtime use only include the following

Assets/SSSoftware/SQL4Unity/
   Scripts
      yourDatabase_*.cs
   Plugins
      SQL4Unity.dll
      SQL4UnityData.dll
   Resources - This folder is optional and only needs to be included if informational and error messages are required
      SQL4Unityxx.txt - SQL4Unity language files where xx is the language code. e.g. EN for English
Assets/StreamingAssets
   yourDatabase.s4u


Important: When making backups or restoring data always make sure that the corresponding Unity .meta files are included