SQL for Unity Version 1.0.5

Introduction

SQL for Unity 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.

SQL for Unity is an RDBMS for integrating a local SQL database into Unity projects.
SQL for Unity Client/Server is an extension to the SQL for Unity RDBMS and allows for both local and remote databases

This version of the RDBMS was written in C# with Unity .Net 4.7.1 libraries and tested using Unity 2018.4, 2019.4, 2020.3 and 2021.2 for Standalone, WebGL, Andriod and IOS but should function on all platforms supported by Unity.
While every effort has been made to ensure the compatibility of SQL for Unity with all Unity versions, the correct functioning of the software with Unity Alpha, Beta or Technical releases cannot be guaranteed.

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.


This documentation is based upon SQL for Unity and SQL for Unity Client/Server V.1.0.5 released April 2022.

Next ->




Installation

Please read the files Assets/SSSoftware/SQL4Unity/ReadMe and Assets/SSSoftware/SQL4UnityExample/ReadMe before using SQL for Unity.

To start SQL for Unity 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.

The Windows Command Line Interpreter can be found at Assets/SSSoftware/SQL4Unity/Editor/External/SQL4UnityCLI.zip (SQL4UnityService.zip for Client/Server version). See below for usage. NOTE: Do NOT unzip this into your Unity project as it contains SQL for Unity dll's specific to Windows and will cause problems when inside a unity project.

Client/Server version only.
The Server software can be found at Assets/SSSoftware/SQL4Unity/Editor/External/SQL4UnityService.zip. See below for usage.
it is recommended to unzip this to a local folder e.g. C:\SQL4Unity for testing.
Refer to Client/Server for more information on installation and configuration.
NOTE: SQL4UnityCLI and SQL4UnityService and SQL4UnityAdmin all use the same SQL for Unity dll's so if you unzip them to different folders then you will need these dll's.

<- Previous Next ->



About SQL for Unity

SQL for Unity consists of 5 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
Note: Each table in a database will have a C# class automatically generated for it to allow easy code integration with the database data.

Resources Management Manage Unity Objects used in the database

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


Additionally the system contains
Windows Command Line Interpreter A Windows Command Line program for accessing SQL for Unity databases external to Unity.

Client/Server version only.
Windows Service A windows service facilitating the communication between client applications and remote SQL for Unity databases

<- Previous Next ->



SQL Workbench

The SQLWorkbench is the module where databases can be defined and created/updated

Getting Started

To access the workbench use the Tools -> SQL for Unity -> Workbench menu option in the Editor.


1. SQL for Unity menu options

The workbench window is split into three sections.

Database Schema Definition
SQL Command
Messages & SQL Output


2. SQL Workbench Window

<- Previous Next ->



Using Schemas

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

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 _
Unique within the database
Note: Names are not case sensitive

<- Previous Next ->



Working with Databases
Define 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.


3. Define a Database

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


5. Database defined

<- Previous Next ->



Rename 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.


5A. Database options


6. Rename database

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


8. Database renamed

<- Previous Next ->



Delete 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.

<- Previous Next ->



Create 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.


29. Players table


30. Positions table


31. Player Position table

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


32. Creating the database

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.
As an aid to development iteration when a Unity project is built as a DEVELOPMENT BUILD SQLExecute will automatically check to see if the database has been updated since the previous build, if so then the database from StreamingAssets will overwrite the database in persistentData and this action will be logged to the Player log file.


Note: If you wish to access a database from a location other than Application.persistentdata 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.




33. Database created

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.


34. Recreate database

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


34A. Additional database options

<- Previous Next ->



Database Options
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 (LARGE: True/False }
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/List, UNIQUE: True/False, COLUMNS: column column )

If Key constraints have been defined
CREATE KEY database.table.column TO database.table.column ( ENFORCE: True/False, UPDATE: True/False, DELETE: True/False )
where the first column is the PRIMARY key and the TO column is the FOREIGN key.

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

<- Previous Next ->



Import

The data from a flat file will be imported into the selected database. No existing table data will be lost.
The AutoCommit option is turned off for this action. For large tables a SET COMMIT command may be added to the import data.
SET COMMIT takes 2 forms. Without a parameter an immediate commit will take place
With a numeric parameter e.g. SET COMMIT 500 will perform a commit every n rows, in this case every 500 rows

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

Important: As the statements may be split over several lines each statement MUST be terminated with a semi-colon ';'.


	INSERT INTO Players (id,title,active,prefab,currpos) VALUES (10,'Cube',True,{NAME:Cube},NULL);
	// This is a comment
	INSERT INTO Players
	 (id,title,active,prefab,currpos)
	 VALUES
	 (20,'Sphere',True,{NAME:Sphere},NULL);
	INSERT INTO Positions (id,position) VALUES (0,{1.0, 1.0, 1.0});
	INSERT INTO Positions (id,position) 
	 VALUES (1,{2.0, 2.0, 2.0});
	INSERT INTO PlayerPos (
	playerid,
	posid
	// This line will be ignored
	) VALUES (
	10,
	0
	);
	INSERT INTO PlayerPos (playerid,posid) VALUES (20,1);

<- Previous Next ->



Export

The data from the selected database will be written to a flat file (.sql) in SQL INSERT statement format.
Alternatively a .xml file extension may be used and the data will be written in XML format.

<- Previous Next ->



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

<- Previous Next ->



Refresh Resources

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

<- Previous Next ->



Manage Passwords

Select this option to add a password to your database. Once this option has been set ALL database access will require that a valid password is supplied.
When using the Workbench or Table Editor you will be prompted to enter the password.
When using SQLExecute the password should be appended to the database name in the form


SQLExecute sql = new SQLExecute("MyDatabase#MyPassword");

NOTE: The password itself is not stored in the schema or the database so if the password is lost or forgotton it cannot be recovered.

<- Previous Next ->



Working with Tables
Define 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.


9. Define a Table

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


11. Table defined

<- Previous Next ->



Rename 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.


11A. Table options

<- Previous Next ->



Delete 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.

<- Previous Next ->



Duplicate 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.

<- Previous Next ->



Partitioning a Table

Select the table you wish to partition using the toggle to the left of the table name. The Options button will appear for that table. Press the Options button and select Partition Table. The change will come into effect next time the database is created.

For a normal table the maximum number of rows is limited to 64k. Should you wish to have tables which will contain more than this limit select this option, you will then be able to store up to 2 billion rows although it is unlikely that operating system restraints would allow such a thing.
SQL for Unity uses a 8k row partitioning system. It is therefore advisable to select table partioning for tables that will contain > 10k rows as this may benefit performance.
It is strongly advised NOT to use table partitioning for tables containing < 8k rows as this will impact performance.

<- Previous Next ->



Working with Columns
Define 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.


12. Define a Column

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


14. Column defined

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.


15. Column datatypes

If the column should allow null values click on the toggle. For more information about null values in SQL for Unity see the DataTypes section0 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.
Note: Although you can select multiple columns to be Primary keys this will NOT result in a complex key. To define complex keys define a multi-column index manually.



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.


19. Move column


20. Default value


21. Default value set

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

<- Previous Next ->



Rename 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.


14A. Column options

<- Previous Next ->



Delete a Column

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

<- Previous Next ->



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

<- Previous Next ->



Define Key Constraints

Press the Options button at the end of the column definition and select Edit Constraints.
Note: Constraints should be set FROM the primary table/column TO the foreign table/column.


14B. Edit Key Constraints
Create Constraint

Select the required foreign key table and column from the dropdown.
Note: The drop down is populated with all table/columns which match the datatype of the primary column.

Enforce Constraint: When selected this will check the constraint on Inserts and Updates on the foreign key table.
Should the constraint fail then the error message
Unable to enforce constraint on PRIMARY KEY NAME
Will be returned in the result message and the Insert or Update will fail.

Cascade Update: When selected an Update of the primary column will automatically be applied to the foreign key column
Cascade Delete: When selected a Delete of the primary row will automatically be applied to the foreign key row(s)
Note: If the foreign key also has cascade constraints defined then action on the primary column/row will also cascade down to these tables
Create Index: Selecting this option will create a non unique index for the foreign key.
Note: This option is only available if the column is an indexable data type.

When all options have been selected press the Add button. Multiple constrains can be added to a primary column to different foreign keys.
When all foreign keys have been defined press the Save button.
Press the Cancel button to discard any changes.

Important: For performance reasons it is highly recommended that the primary column be indexed if constraints are to be enforced and that foreign keys be indexed if cascading is required.
Be careful NOT to define circular cascades as this will lead to an infinite loop.

Edit a Constraint
For an existing constraint you can select/unselect any of the existing options then press Save.

Delete a Constraint
Press the Delete button next the the constraint to be removed then press Save.

<- Previous Next ->



Working with Indexes
Define 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.


22. Define Index

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


24. Index defined

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


25. Set index type

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.
List - An array of keys. This type of index is intended to be used for sequential numerical data, such as ID's, it offers extremely fast access but at the expense of using memory for all possible key values. This is the default type for Row Id and Auto Increment Primary Keys.

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.


27. Choose Index columns

<- Previous Next ->



Rename 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.


28. Index options

<- Previous Next ->



Delete an Index

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

<- Previous Next ->



Stored SQL

Defining a Stored SQL

Enter a SQL Statement into the SQL Command area. Press Save As. You will be probpted to enter a name for the sql, this name must be unique within the database. Press Ok and the SQL Command will be saved and will appear under the stored sql's section.
An options button will appear for the selected SQL which allows you to Rename, Drop or Edit the sql.
Parameter names can be used in stored sql's in the form %name% and these names must be replaced with real values at runtime. (See SQLExecute for more information on parameter substitution)
For Example


Select * from Players where ID=%id%
Defines a parameter, id which must be supplied when the sql is Executed. If this is done using the Execute button of SQL Command then a window will appear for you to supply parameter values.

<- Previous Next ->



SQL Command

The SQL Command section0 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


35. Additional table 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.


36. Default Insert

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


37. Default Select

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


38. Default Update

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


39. Default Delete

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.

Using the Edit SQL option for a Stored SQL will also populate the command area. Once editor is complete use the Save or Save As buttons to save the changes.

<- Previous Next ->



Error Messages and 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.

<- Previous Next ->



Table Editor

The table editor allows you to manipulate the data within an SQL for Unity 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 SQL for Unity 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.


44. Table Editor window

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.


45. Opened database

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.


46. Edit table data

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.


47. Table options

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.


48. Filter columns

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.


49. Add row to table

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


50. Row options

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.


53. Open Multiple tables

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


54. Reposition windows

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


55. Column options

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.


56. Filter Column



<- Previous Next ->



SQL Execute

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

Overview 


using UnityEngine;

public class MyClass : MonoBehaviour {

	SQL4Unity.SQLResult sqlResult;
	SQL4Unity.SQLExecute sql;

	void Start() {

		// Initialise the result and execute classes
		sqlResult = new SQL4Unity.SQLResult();
		sql = new SQL4Unity.SQLExecute();

		// Open the database MyDataBase
		if (sql.Open("MyDataBase")) {

			// 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.rowsAffected;i++) {

					// Retrieve the row data
					mydatabase_mytable row = sqlResult.Get<mydatabase_mytable>(i);

					// Do something with the data
					string name = row.MyName;
					int score = 0;
					if (!sqlResult.isNull(i,"MyScore"))
						score = row.MyScore;
				}
			} else {

				// Select statement failed
				Debug.Log(sqlResult.message);
			}

			// Close the database
			sql.Close();
		} 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
Execute STOREDSQL

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
Note: Nullable columns may be ommited when used in an INSERT statement

where STOREDSQL is the name of a SQL command previously stored in the database

where VALUE is an allowed value for the column data type
Note: The literal 'null' (without the quotes) can by used for nullable columns.

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)
Note: The data type returned by the above functions will always be of type double

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.


<- Previous Next ->



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

<- Previous Next ->



Methods
Command

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)
bool Command(string query, SQLResult result, SQLRow[] rows)
bool Command(string query, SQLResult result, List<SQLRow> rows)

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(s) 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);

If multiple rows are supplied either as an array or a list a commit will be executed after ALL rows are inserted sucussfully. If any rows are rejected ALL of the supplied rows will be rejected.

bool Command(string query, SQLResult result, SQLParameter parameters)

This is a special case for any SQL statement using parameter substituution
Parameters are defined in the sql statements using the following syntax
%name% - Where name is the name of the parameter
and can be used in a sql statement like

Select * from Players where ID=%id%
To supply the value of %id% at run time we use the SQLParameter class and its method SetValue(string name, object value) as per this example
Note: Parameter names are case sensitive

string query = "Update Players set Title=%name% where ID=%id%";

SQLParameter parameters = new SQLParameter();

sql.AutoCommit(false);

foreach (Player player in players) {

	parameters.SetValue("id", player.ID);
	parameters.SetValue("name", player.Name);

	sql.Command(query, result, parameters);

}

sql.Commit();

<- Previous Next ->



Open

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

<- Previous Next ->



OpenAsync

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
Note: If a password has been applied to the database then that should be appended to the database name in the form DatabaseName#Password

<- Previous Next ->



Commit

void Commit()

If autoCommit is false changes will be written to the database

<- Previous Next ->



Rollback

void RollBack()

If autoCommit is false any unsaved changes will be discarded

<- Previous Next ->



Close

void Close(bool commit)

Close the database. If commit is true any unsaved changes will be written to the database.

<- Previous Next ->



AutoCommit

void AutoCommit(bool commit)

Turns autoCommit on or off. Note: Autocommit is ON by default.

<- Previous Next ->



Language

void Language(string language)

Changes the language of the system. Default EN - English

<- Previous Next ->



ReplaceIfNewer

bool ReplaceIfNewer(string Database, bool forceReplace, out string message, bool keepData)

This option will check if the named database in Application.persistentDataPath is older than a database of the same name in Application.streamingAssetsPath. If so the older database will be replaced with the newer one.
If the option forceReplace is set to True the database will be replaced irrespactive of creation date
If the option keepData is set to True the table data will be copied from the old database to the new one before replacement.

<- Previous Next ->



Targeting WebGL

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.persistentDataPath it will NOT overwrite an existing database

<- Previous Next ->



Example Code

In this section I will publish some useful code snippets as and when I come across them

Outer Join

Outer Joins are not supported by SQL for Unity but the following code shows a method of achieving the same result.

The code can be made more efficient by storing query2 in the database as a stored SQL and using the Execute statement to run it. 

	
		SQL4Unity.SQLExecute sql = new SQL4Unity.SQLExecute("MyGameDB");
		SQL4Unity.SQLResult result = new SQL4Unity.SQLResult();
		SQL4Unity.SQLParameter parms = new SQL4Unity.SQLParameter();
		parms.Add("id", "int");

		string query1 = "Select * from Players";
		string query2 = "Select * from PlayerPos where PlayerId=%id%";

		sql.Command(query1, result);

		mygamedb_players[] players = result.Get<mygamedb_players>();
		mygamedb_playerpos[] positions = new mygamedb_playerpos[players.Length];

		for (int i=0;i<players.Length;i++) {

			parms.SetValue("id", players[i].ID);
			sql.Command(query2, result);

			if (result.rowsAffected == 0)
			{
				positions[i]=null;
			} 
			else
			{
				positions[i]=result.Get<mygamedb_playerpos>(0);
			}
		}

<- Previous Next ->



SQL Result

The SQLResult class will contain all of the information resulting from the execution of an SQL statement

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, inserted, updated or deleted by the command.

Note: The rows collection of SQLResult will be filled by both Select and Insert statements.

<- Previous Next ->



Methods

** 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

<- Previous Next ->



Clear

void Clear() : reset all variables

<- Previous Next ->



Get

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.

<- Previous Next ->



GetColumn

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 isNull method before the Get

<- Previous Next ->



GetColumnNames

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

<- Previous Next ->



isNull

bool isNull(int rowIx, string columnname)
bool isNull(int rowIx, int columnIx)
Returns true if the value for the specified row/column is DBNULL.Value in the database

<- Previous Next ->



Resources Mangement

Due to the nature of Unitys AssetDatabase and the fact the Resource paths cannot be identified outside of the Editor SQL for Unity 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

<- Previous Next ->



Windows Command Line Interpreter

The windows command line interpreter allows the SQL4Unity databases to be accessed outside of a Unity project.
The program (SQL4UnityCLI.exe and associated .dll's) can be found in a compressed .zip file at SQL4Unity/Editor/External/SQL4UnityCLI.zip
Note: For SQL for Unity Client/Server the CLI program is included in the SQL4UnityService.zip file
Important: The .zip file must be unzipped into a folder external to your Unity project. The .dll's supplied in the .zip are compatible with but NOT replacements for the .dll's supplied with the SQL for Unity package.

SQL4UnityCLI supports the following commands

About - Show Contact, Copyright and Version information
Autocommit ON/OFF - Toggle Auto Commit. Default is ON.
Close - Close the currently open database
Commit - Commit any pending database changes
Help - Show this help text
Quit - Exit program
Rollback - Undo any pending database changes
Open PATH/DATABASENAME#PASSWORD - Open the named database with optional password. Note forward slash

And the following SQL Statements :-
Select
Insert
Update
Delete
Truncate
Execute

For SQL for Unity Client/Server the following connect command is available

Connect db=DATABASENAME pwd=PASSWORD user=USERNAME ip=IPADDRESS port=PORTNUMBER protocol=WS or TCP uuid=UUID secure=True or False

db=DATABASENAME - Required
pwd=PASSWORD - Required if the database is password protected
user=USERNAME - Required if your application has a database per user
ip=IPADDRESS - Default 127.0.0.1
Note: For WS protocol IPADDRESS should be the server domain name.
port=PORTNUMBER - Default 19190
protocol=WS or TCP - Default TCP
uuid=UUID - UUID of your application. Default is the SQL for Unity default UUID
secure=True or False - Default False. WS protocol only

you will receive a message if the database has opened sucussfully or not
if the database is open then you can enter SQL commands against it

<- Previous Next ->



Client/Server

SQL for Unity Client Server

Client/Server is an extension to the standard SQL for Unity package which allows for the hosting of SQL for Unity databases on a remote windows server. These database(s) can then be accessed by your Unity application from any Unity supported platform.

Package Contents

The package contains the following new/changed items

SQL4UnityService.zip - A windows service to provide access to the database(s)
SQL4Unity.dll - Extensions have been added to SQLExecute to facilitate remote connections
SQL4UnityCLI.exe - The Connect command has been added to allow remote connections
SQL4UnityServer.dll - SQL for UnityClient(s) added to the dll.
SQL4UnityLibrary.jslib - Emscripten/Javascript interface for WebGL WebSocket use

Unzip Assets/SSSoftware/SQL4Unity/Editor/External/SQL4UnityService.zip into a local folder (e.g. C:\SQL4Unity) on both your Windows server and any client machine you wish to use for server administration

This will then contain the following folder and files


66. Service Contents
Note: Although the SQL4Unity*.dll's supplied with SQL4UnityService (and SQL4UnityCLI) are compatible with those in the SQL4Unity/Plugins folder they are NOT interchangable. Do NOT try and use these .dll's in a Unity project.

<- Previous Next ->



Installation
Configuration

Open SQL4UnityService.exe.config in a text editor. Under appSettings you will see the following key/value pairs

<add key="Trace" value="false" />
<add key="Home" value="C:\SQL4Unity\Data" />
<add key="ClientTCPPort" value="19190" />
<add key="ClientWSPort" value="19191" />
<add key="WSAddress" value="127.0.0.1" />
<add key="SecureWS" value="false" />
<add key="AdminWSPort" value="19192" />
<add key="AdminTCPPort" value="19193" />
<add key="MaxClients" value="10" />
<add key="Milliseconds" value="2000" />
<add key="UUID" value="fcf368db-3c7f-43e4-8f0e-95e3ccdb999d" />
<add key="ReadOnly" value="false" />

These key/values can be set as follows

Trace - true or false - When set to true additional logging information will be output (See logging below)
Home - The path to the folder where the database file(s) will be stored
ClientTCPPort - The port to be used for Client TCP protocol connections
ClientWSPort - The port to be used for Client WebSocket connections. Must be used for WebGL connections
AdminTCPPort - The port to be used for Administration TCP connections. Used for user defined admin connections
AdminWSPort - The port to be used by the Web based Administration page

Note: If any of the above ports are NOT to be used the values should be set to 0 and no listener will be initialised for the function/protocol. Do not forget to open the ports used in your firewall.

WSAddress - The domain name that should be used to listen for incoming WebSocket connections
SecureWS - true or false. If set to true then wss protocol will be used for WebSocket connections

MaxClients - The maximum number of concurrent client connections allowed
Milliseconds - The number of milliseconds to wait before timing out a connection

UUID - The unique identifier for your application. This exact value must be used on both the server and client side of your application.

Note: There are a number of tools on the internet that generate UUID's. Although the system will function with the default UUID it is strongly recommended for security reasons to generate and use your own.
By specifying a unique UUID it will mean that your databases are only accessible via your Unity application.

ReadOnly - true or false. If set to true then ONLY SQL Select commands will be allowed to be executed against the database(s).

Save your changes!

<- Previous Next ->



Installing the Service

To install the SQL for Unity Service
Open a windows command prompt as Administrator and run install

cd c:\SQL4Unity (or which ever folder you unzipped to)
install.bat


57. Service Installation

In the Task Manager under the Services tab you should now see the SQL for Unity service Running


58. Windows Task Manager
Note: If you wish to make changes to the configuration file after installation you must Stop/Start the service for those changes to take effect.

<- Previous Next ->



Installing Databases

To make a database accessible to the service it must be placed in the 'Home' directory as specified in the config file.
Should you wish to have a database per user (identified by user name) then they should be placed in sub folders of the 'Home' directory where the sub folder name is the user name
e.g.

C:\SQL4Unity\Data\MyGameDB.s4u
Accesible by all without a user name

C:\SQL4Unity\Data\Steve\MyGameDB.s4u
Accessible only by those who supply the user name 'Steve' in their connection parameters

<- Previous Next ->



Logging

In the Event Viewer under Application Logs you should see the SQL4UnityLog events


59. Windows Event Viewer

When Trace is set to true in the config then all client commands will be logged. This could have a detrimental effect on performance and should be used for testing purposes only.

<- Previous Next ->



Testing the Service

There a 3 ways you can ensure that the service is installed and running corectly

Firstly using the SQL for Unity Command Line Interpreter

Open a windows command prompt and run SQL4UnityCLI

cd c:\SQL4Unity (or which ever folder you unzipped to)
SQL4UnityCLI

enter the connect command
Connect db=DATABASENAME pwd=PASSWORD user=USERNAME ip=IPADDRESS port=PORTNUMBER protocol=WS or TCP uuid=UUID secure=True or False
Note: For WS protocol IPADDRESS should be the server domain name.

See Windows Command Line Interpreter for exact usage
you will receive a message if the database has opened sucussfully or not
if the database is open then you can enter SQL commands against it


60. Testing via CLI

If you have enabled Administration TCP protocol you can use the SQL4UnityAdmin program
Open a windows command prompt and run SQL4UnityAdmin

cd c:\SQL4Unity (or which ever folder you unzipped to)
SQL4UnityAdmin

enter the servers command


61. Testing via Admin

If you have enabled the Administration WebSocket protocol then you can see the status of the service by navigating to the following in any web browser
http(s)://IPADDRESS:PORT
where IPADDRESS and PORT are the ip address of the domain specified in the WSAddress and AdminWSPort keys of your config file
You will be prompted to enter your UUID which must be the same as the one used in the service config file
The browser will then display the status of the server processes


62. Testing via Dashboard

<- Previous Next ->



Uninstall

To uninstall the SQL for Unity Service
Open a windows command prompt as Administrator and run uninstall

cd c:\SQL4Unity (or which ever folder you unzipped to)
uninstall.bat


63. Uninstall the Service

<- Previous Next ->



SQLExecute Extensions

Access to the remote database(s) has been added to the SQLExecute class. All of the remote access is done asyncronously so as not to block the application for slow connections and is implemented via callbacks

Methods

Note: When using the TCP protocol it is possible to use both syncronous or asyncronous communications. Using the methods without a callback will use syncronous comms using the methods with a callback will use asyncronous.

bool Connect(Server.Protocol protocol, string ipaddress, int port, string uuid, bool secure=false, bool adminOnly=false, string userName="", Action<bool> callback=null)

adminOnly should only be set to true when you do not with to execute SQL Commands but only wish to use the Backup or Restore fuctions.
Note: For Websocket protocol ipaddress should be the server domain name.

Note: OpenAsync will attempt to open both a local database (if it exists) and the remote one. It is highly recommended that you include a local database with your application even if it is empty of data. If your database contains references to Unity objects, Textures, Materials, Sprites etc then you MUST have a local database available so that SQL4Unity can correctly resolve these objects at runtime.
The SQL for Unity Service does not know how to resolve Unity Objects and relies on the client side of SQL for Unity to do so using the resources which are embedded in the local database.
This usage of both a local and a remote database allows SQL for Unity to fallback to using the local database if the remote database is not available thus ensuring that the clients can work offline as well as online.


The Command method now have the following additional signatures and these should always be used for Web Socket implementations. When using TCP either may be used.

void Command(string command, SQLResult result, Action<bool, SQLResult> callback)
void Command(string command, SQLResult result, SQLRow row, Action<bool, SQLResult> callback)
void Command(string command, SQLResult result, SQLParameter parameters, Action<bool, SQLResult> callback)

In addition to the new Connect method and the extension of the Command methods with a callback parameter the following new methods are available

void BackupToServer(string database, string userName="", Action<bool> callback = null)

To copy a local database to the server

void RestoreFromServer(string database, string userName = "", Action<bool> callback=null)

To copy a database from the server to local, the database will be placed in the Application.persistantData folder and can then be used in the normal way.

void SyncWithServer(bool on)

This method allows for the consistent syncronisation of both a remote and local database. Once this is switched on then the following processing of SQL commands takes place

Select commands are executed against the local database

Insert/Update/Delete commands are executed against the remote database and the resulting changes are sent back and applied to the local database.

<- Previous Next ->



Administration
Web Browser

If you have enabled the Administration WebSocket protocol then you can see the status of the server processes and client connections by navigating to the following in any web browser
http(s)://DOMAIN:PORT
where DOMAIN and PORT are the values specified in the WSAddress and AdminWSPort keys of your config file
You will be prompted to enter your UUID which must be the same as the one used in the service config file


64. Dashboard Sign in
Press Send and the browser will then display the status of the server processes and any client connections


65. Administration Dashboard

The Dashboard displays the following information:-

For Server processes
Type - The type of process, Client or Admin
Protocol - The protocol the process uses
Port - The communication port used. If a server is not in use the port will show Undefined
Status - The process status, Running or Stopped
Connections - The number of clients connected to the server

For Client Processes
Protocol The protocol the client uses
Platform - The Unity platform used by the client
User - The client user name, if any
Database - Which database the client is connected to
Status - The status of the client connection, Open or Closed
IP Address - The IP Address of the client
Access - The type of access the client has to the database
Messages - Error messages, if any, generated by the client

From the Dashboard you can carry out the following tasks:-

Stop Service - Stop the SQL for Unity Service
Note: This causes the whole service to stop to restart it you will need to do so via the Windows Task or Services Manager
Pause - Stop a server process from accepting new client connections
Resume - Allow the server process to accept new client connections
Stop All - Stop ALL client connections from accessing databases
Stop - Stop an individual client from accessing databases
Disconnect - Close the Dashboard connection to the server

<- Previous Next ->



Command Line

The service .zip contains the windows command line program SQL4UnityAdmin.exe. This can be used for administration tasks instead of the Browser based option.
Firstly you will need to modify the configutation file

Open SQL4UnityAdmin.exe.config in a text editor. Under appSettings you will see the following key/value pairs

<add key="IPAddress" value="127.0.0.1" />
<add key="AdminTCPPort" value="19193" />
<add key="UUID" value="fcf368db-3c7f-43e4-8f0e-95e3ccdb999d" />

These key/values must be set as follows

AdminTCPPort - The AdminTCPPort value from the Service config
IPAddress - The IP address of your server
UUID - The UUID value from the service config

Save your changes!


Open a windows command prompt and run SQL for Unity Admin

cd c:\SQL4Unity (or which ever folder you unzipped to)
SQL4UnityAdmin

The Help or ? command will show a list of available commands which work in the same way as the Administration Dashboard.

About - Show Contact, Copyright and Version information
Close - Close the server connection
Help - Show this help text
Servers - Show the status of the Server processes
Clients - Show the Client connection details
Stop Service - Stop the SQL4Unity service
Stop All - Stop all Client connections
Stop ID - Stop the Client with ID (use Clients command to find the ID to stop)
Pause Admin PROTOCOL - Pause the Admin Server for the protocol
Pause Client PROTOCOL - Pause the Client Server for the protocol
Resume Admin PROTOCOL - Resume the Admin Server for the protocol
Resume Client PROTOCOL - Resume the Client Server for the protocol
where PROTOCOL above is one of TCP or WS
Send local=DBNAME remote=DBNAME - Send Database to Server
Receive local=DBNAME remote=DBNAME - Get Database from Server
Quit - Exit program


The servers and clients commands can be used to display the status of the server processes and the client connections


69. Admin Status

Note: For those wishing to make their own custom administration tool the source code for SQL4UnityAdmin is available from the SQL for Unity GitHub repository.
Please contact sql4unity@stevesmith.software with your license information for access to this repository.

<- Previous Next ->



Example Code

An example of the code to access a remote database


	using UnityEngine;
	using System;

	public class DatabaseClient : MonoBehaviour
	{
		public SQL4Unity.Server.Protocol Protocol = SQL4Unity.Server.Protocol.TCP;
		public string Database = "MyGameDB";
		public string UserName=string.Empty;
		public string IpAddress = "127.0.0.1"; // Local IP for testing
		//	Note: For Websocket protocol IpAddress should be the server domain name.
		public bool secure = false;
		int Port = 19190; // Default Client TCP Port
		internal string UUID = "fcf368db-3c7f-43e4-8f0e-95e3ccdb999d"; // Default UUID. Replace with yours
		SQL4Unity.SQLExecute sql=null;

		void Start()
		{
			// Must be WebSocket for WebGL
			if (Application.platform == RuntimePlatform.WebGLPlayer) Protocol = SQL4Unity.Server.Protocol.WebSocket;
			if (Protocol == SQL4Unity.Server.Protocol.WebSocket) Port = 19191; // Default Client WebSocket Port

			Debug.Log("Using Protocol " + Protocol + " to " + IpAddress + ":" + Port);
			// Monobehaviour required for Websocket and TCP Async Connections. Secure Socket = True/False required for WebSocket Protocol
			sql = new SQL4Unity.SQLExecute(this);
			sql.Connect(Protocol, IpAddress, Port, UUID, secure, false, UserName, ConnectCallback);
		}

		// Called once a connection to the server has been made
		void ConnectCallback(bool ok)
		{
			Debug.Log("Connected:" + ok);
			sql.OpenAsync(Database, OpenCallback); // Even if the remote connection failed SQL4Unity will fallback to using a local database
		}

		// Called once the database has been Opened
		void OpenCallback(bool ok) {
			Debug.Log("Database Open:" + ok);
			// Execute an SQL command against the remote database
			if (ok) sql.Command("select * from positions", null, SelectCallback); // We do not need to supply a result as it is passed back to the callback
		}

		// Called when the SQL Command has executed and the results are available
		void SelectCallback(bool ok, SQL4Unity.SQLResult result)
		{
			ShowResults(result);
			sql.Close();
		}

		void ShowResults(SQL4Unity.SQLResult result)
		{
			Debug.Log(result.resultType.ToString() +" "+ result.status.ToString() + " " + result.message);
			if (result.status)
			{
				try
				{
					mygamedb_positions[] players = result.Get<mygamedb_positions>();
					foreach (mygamedb_positions player in players)
					{
						Debug.Log("Player: "+player.ID+" Position: "+player.Position);
					}
				} catch(Exception ex) {
					// May throw an Illegal Cast Exception if the local database is missing
					Debug.Log(ex.Message);
				}
			}
		}
	}
Note: SQLExecute is using the MonoBehaviour contructor and the database is Opened using OpenAsync. This allows the code to function correctly irrespective of the Unity target platform.

<- Previous Next ->



SQL*Server

The SQLServer class is designed to give direct access to a Microsoft SQL*Server database
There are 2 prerequisites required on the SQL*Server side
1) The Instance must be enabled for TCP connections
2) The Instance must accept SQL Authentication

Constructor

SQLServer(string DBName, string username, string password, string ipAddress= "127.0.0.1", int portNr= 51908, string instance= "SQLEXPRESS")
where:
DBName is the name of the SQL*Server Database.
username is the SQL*Server user name.
password is the SQL*Server user password
ipAddress is the IP Address of the instance. Default 127.0.0.1. i.e localhost.
portNr is the TCP port the instance is listening on. Default 51908
instance is the name of the SQL*Server instance. Default SQLEXPRESS.

<- Previous Next ->



Methods

bool Open() - Open the Database. Returns true if the database opened succesfuly
bool Close() - Close the database. Returns true if the database closed succesfuly
bool ExecuteNonQuery(string sqlcmd, bool close=true) - Execute a Non Query sql command. Return true if successful.
System.Data.DataTable ExecuteQuery(string sqlcmd, bool close=true) - Executes a Select query. Returns a Datatable with the results.
int Count() - Returns a count of the number of records affected by the previous sql command.

<- Previous Next ->



Data Types

SQL4Unity supports many more datatypes than a 'normal' RDBMS. These are broken down into 3 categories
C# Data types
Unity Serializable types
Unity Objects

For columns defined as nullable the literal value 'null' (without the quotes) can be used instead of an actual column value. Alternatively, for INSERT statements, the column can be omitted completely.

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.

Important:The DataType class contains a ToString method for converting C# variables into a format that can be parsed by SQL statements. It is strongly reccommended that you use this method when constructing SQL statEments from variables

For Example: string query = "UPDATE myTable set myVector3="+SQL4Unity.DataType.ToString(Vector3.up)+" where id = 1";
will convert the Unity Vector3.up into the SQL4Unity string format { 0,1,0 }

C# Data types

byte - A single unsigned byte. 0 - 255
sbyte - A single signed byte. -127 - +127
short - A 16 bit integer number
ushort - A 16 bit unsigned integer number
int - A 32 bit integer number
uint - A 32 bit unsigned integer number
long - A 64 bit integer number
ulong - A 64 bit unsigned integer number
char - A single character
double - A number including decimal point but no separators. Optionally terminated by the letter d
float - A number including decimal point but no separators. Optionally terminated by a letter f
Note: doubles and floats are validated using the Invariant Culture. Using seperators other than comma or a comma instead of a decimal point will lead to incorrect interpretation of your input
So 999,999.00 is valid input but 999.999,99 or 999,99 will result in incorrect validation
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.

Blob - A class that can contain a byte array.
Use byte[] bytes = blob.Get(); and blob.Set(byte[]); to access the array using byte data.
A Blob can also be used to store a Serializable C# Class as a byte array
Use MyClass myclass = blob.Get<MyClass>(); and blob.Set<MyClass>(myClass);
These methods automatically handle the conversion from/to byte array format
Blob also supports the usage of a Unity Texture2D as a parameter.
Use:
Texture2D tex = ...;
Blob blob = new Blob(tex);
or
Texture2D tex = ...;
Blob blob = new Blob();
blob.Set(tex);
and to fill a texture from a Blob
blob.Get(tex);
Note: blob.Get(Texture2D) does not create a new texture it fills an existing one with the blob contents.

If it is required to use byte array data in an SQL statement then that data must be converted to Base64 Encoded string format.
The Datatype class provides 2 ways of making Base64 strings
string base64 = DataType.ToString(byte[]); - string from byte array
string base64 = DataType.ToString(Blob); - string from a Blob object
Blob also has a ToString method which returns a base64 string.

<- Previous Next ->



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.

<- Previous Next ->



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




<- Previous Next ->



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 C# reserved words SQL4Unity 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
 public
private
class
list
dictionary
using
true
false
if
else
for
foreach
do
while
until
hashtable
hashset
object
namespace
 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.



<- Previous Next ->



Backup/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
Assets/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
      SQL4UnityServer.dll - If used
   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

Git Source Code Control
For those of you who use GIT as your source control, the following added to your gitignore file will ensure that you always maintain a consistent SQL for Unity environment


	/Assets/*
	!Assets/SSSoftware/

	/Assets/SSSoftware/*
	!/Assets/SSSoftware/SQL4Unity/

	/Assets/SSSoftware/SQL4Unity/*
	!/Assets/SSSoftware/SQL4Unity/Scripts/
	!/Assets/SSSoftware/SQL4Unity/Editor/

	/Assets/SSSoftware/SQL4Unity/Editor/*
	!/Assets/SSSoftware/SQL4Unity/Editor/Resources/

	/Assets/SSSoftware/SQL4Unity/Editor/Resources/*
	!/Assets/SSSoftware/SQL4Unity/Editor/Resources/Schema.asset
	!/Assets/SSSoftware/SQL4Unity/Editor/Resources/Schema.asset.meta
	/Assets/StreamingAssets/*
	!/Assets/StreamingAssets/*.s4u
	!/Assets/StreamingAssets/*.s4u.meta

<- Previous Next ->



Additional Information

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
      External
         SQL4UnityCLI.zip - Windows Command Line Interpreter. May be deleted if not required
         SQL4UnityService.zip - Client/Server Only. The Windows service software
      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 SQL for Unity RDBMS Engine
      SQL4UnityData.dll - The SQL for Unity Database data structures
      SQL4UnityEditor.dll - The SQL for Unity Unity Editor code
      SQL4UnityServer.dll - The SQL for Unity Unity Client/Server code
      SQL4UnityLibrary.jslib - Client/Server Only. The WebGL websocket interface code
   Resources
      SQL4Unityxx.txt - SQL for Unity 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 SQL for Unity 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
      MyGameDB.sql - Example MyGameDB SQL export file
      Resources
         Capsule - Unity Prefab
         Cube - Unity Prefab
         Cylinder - Unity Prefab
         Sphere - Unity Prefab
         Schema.asset - Example SQL for Unity 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 SQL for Unity Database. This file may be deleted

<- Previous Next ->



Distribution

This RDBMS system comprises many C# scripts split into 4 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

<- Previous Next ->



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

<- Previous Next ->



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

<- Previous Next ->



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.

<- Previous