RsDb - Real Simple Database for Unity 3D

Overview

RsDb stands for "Really simple Database" : a lightweight RDBMS (Relational Database Management System) designed for Unity 3D.

As much as possible, RsDb provides the functionalities of a classical RDBMS with SQL support with some particularities :

User : mono user - no identification nor authentification managment
Data : in memory database with file oriented storage for portability (1 local file = 1 database, offline)
Model : data is organized in tables (1 table = 1 collection of rows and columns)
Integrity : no duplicate rows in a table, type of a column is restricted, NULL is recognized, read-only mode
Constraints : not null, default value, identity, foreign key
Transactions : data modification can be committed or rolled back (if autocommit mode is off)
Information : content is described through system tables (all tables, all columns, all constraints)
SQL : data manipulation and query (SELECT, INSERT, UPDATE, DELETE, CREATE, SET, ALTER)
Instanciation : through a proxy class with synchronous / asynchronous executions
Studio : complete graphical client for an enhanced experience
No dependency : RsDb is 100% coded using C# and do not rely on any third party librairie
Compatible with Unity 4.7 and 5.4.2

Content

1 - The RsDb package

The RsDb package is composed of the following :

com.heparo.rsdb : the RsDb package (main folder hierarchy) [0 file(s)]

debugger : the assets used to debug / unit test the RsDb system [0 file(s)]

scene : contains the demo scene to create a test database [1 file(s)]
scripts : contains the cs classes used in the demo scene [1 file(s)]

documentation : contains the HTML documentation of the RsDb package (normally the page you are presently reading... Isn't it ?) [1 file(s)]

css : the CSS file of the documentation [1 file(s)]
images : contains all the images used in the present documentation [59 file(s)]

editor : contains the cs extension used as an RsDb databases client [1 file(s)]

resources : contains all the resources used by the editor [15 file(s)]

scripts : contains all the RsDb core classes [21 file(s)]

com.heparo.utils : a complement to the RsDb core classes (associated utils package) [0 file(s)]

scripts : contains the cs scripts [0 file(s)]

collections : utilitary collection classes [1 file(s)]
io : input / output classes [2 file(s)]

Getting started

Getting started with RsDb is quite easy but needs some piece of code.
The safe and quick approach is to instanciate in a GameObject script a driver in order to access the desired database(s).
Two drivers can be instanciated :

The [asynchronous] one : execute a statement will not wait for a result to be generated (implies to listen to the driver events...)
The [synchronous] one : execute a statement will wait for a result to be generated

You can open any reasonnable number of databases.
You can combine both strategies to access them.
Yes, that means both drivers share the same static list of opened databases.

1 - The asynchronous way

In this example, we will deal with [MyScript] extending [MonoBehaviour] we need : an instance of [RsDbDriverAsync] and [RsDbDriverListener] and register the listener.

// ...

using com.heparo.rsdb.RsDbConstants;			
using com.heparo.rsdb.RsDbDriverAsync;
using com.heparo.rsdb.RsDbDriverListener;

// ...
				
public class MyScript : MonoBehaviour {

	// ...
	
	// The driver to use
	private RsDbDriverAsync p_driver;
	
	// Its associated p_listener
	private RsDbDriverListener p_listener;
	
	// ...
	
	public void Start(){
	
		// ...
		
		// Instanciate a driver	
		p_driver = new RsDbDriverAsync();
		
		// Instanciate a driver listener
		p_listener = new RsDbDriverListener();
		
		// Register the listener
		p_driver.AddRsDbDriverListener( p_listener );
		
		// ...
	
	}
	
}

We have now to implement the event management part using the listener's methods.
In practice, each call to any delegated update-like method will check the listener for new events generated by the driver.
There are 6 different types of event (using the RsDbConstants labels) :

DRIVER_NO_EVENT : no event was generated, nothing to do generally
DRIVER_EXEC_EVENT : the database successfully executed a statement, a result object is available for use
DRIVER_SAVE_EVENT : the database was just successfully saved
DRIVER_LOAD_EVENT : the database was just successfully loaded (or created empty) and its index in driver's repository must be kept !
DRIVER_EXCEPTION_EVENT : an exception was raised by the database when performing an operation
DRIVER_CLOSE_EVENT : the database was successfully closed

Once processed, an event must always be consumed in order to deal with the next awaiting event : indeed, a listener enqueues events for all opened databases.

// ...

using com.heparo.rsdb.RsDbResult;

// ...

// A new event to consume ?
switch( p_listener.GetNextEventType() ){
		
	// DRIVER_NO_EVENT, nothing...
	case RsDbConstants.DRIVER_NO_EVENT :
	
		// ...
		
		// Break
		break;
	
	// DRIVER_EXEC_EVENT
	case RsDbConstants.DRIVER_EXEC_EVENT :
			
		// ...
				
		// Get result if needed
		... p_listener.GetResult() ...
					
		// ...
				
		// Consume event
		p_listener.ConsumeEvent();
				
		// ...
		
		// Break
		break;
			
	// DRIVER_SAVE_EVENT
	case RsDbConstants.DRIVER_SAVE_EVENT :
			
		// ...
				
		// Consume event
		p_listener.ConsumeEvent();
				
		// ...
		
		// Break
		break;
			
	// DRIVER_LOAD_EVENT
	case RsDbConstants.DRIVER_LOAD_EVENT :
			
		// Get the database index
		...  p_listener.GetDatabaseIndex() ...
				
		// ...
				
		// Consume event
		p_listener.ConsumeEvent();
				
		// ...

		// Break
		break;
			
	// DRIVER_EXCEPTION_EVENT
	case RsDbConstants.DRIVER_EXCEPTION_EVENT :
			
		// ...
				
		// Consume event
		p_listener.ConsumeEvent();
				
		// ...

		// Break
		break;
		
	// DRIVER_CLOSE_EVENT
	case RsDbConstants.DRIVER_CLOSE_EVENT :
			
		// ...
				
		// Consume event
		p_listener.ConsumeEvent();
				
		// ...

		// Break
		break;				
		
}

// ...

Finally, what is left is to manipulate effectively the databases and ask the driver to :

Open a connection for given database pathName
Execute the provided statement throo
Save the database using its index (Save As is available too)
Close the database using its index

In practice, it is always wise to use a try and catch block (strongly recommended when executing a statement).

// ...

// Ask driver to open a connection for given database path
p_driver.Connect(#PATH TO DATABASE FILE#);

// ...				

try{

	// ...

	// Execute the provided statement
	p_driver.ExecuteAt(#DATABASE INDEX#,#SQL STATEMENT#);
	
	// ...
						
}
catch(System.Exception exception){

	// ...
						
}
					
// ...
			
// Ask driver to save the database using its index
p_driver.SaveAt(#DATABASE INDEX#);
				
// ...
			
// Ask driver to close the database using its index
p_driver.CloseAt(#DATABASE INDEX#);
				
// ...

In the example above :

#PATH TO DATABASE FILE# is the path to the database file
#DATABASE INDEX# is the index of the database in the driver's list
#SQL STATEMENT# represents any SQL statement to execute

One last word : both drivers share the same static list of opened databases, yes. But the async driver will create others objects to manage requests (jobs).
If you plan to mix both strategies, it is then better to open a database first with the async driver.

2 - The synchronous way

In this example, we will still deal with [MyScript] extending [MonoBehaviour] and we only need : an instance of [RsDbDriverSync].

// ...

using com.heparo.rsdb.RsDbResult;			
using com.heparo.rsdb.RsDbDriverSync;

// ...

// Instanciate a SYNC driver
RsDbDriverSync syncDrv = new RsDbDriverSync();

// ...

syncDrv.Load(#PATH TO DATABASE FILE#);
#DATABASE INDEX# = syncDrv.GetDatabaseIndex(#PATH TO DATABASE FILE#);

// ...

try{

	// ...

	// Execute the provided statement
	RsDbResult result = syncDrv.ExecuteAt(#DATABASE INDEX#,#SQL STATEMENT#);
	
	// ...
						
}
catch(System.Exception exception){

	// ...
						
}

// ...

// Save it
syncDrv.SaveAt(#DATABASE INDEX#);

// ...

// Close it
syncDrv.CloseAt(#DATABASE INDEX#);

// ...

In the example above :

#PATH TO DATABASE FILE# is the path to the database file
#DATABASE INDEX# is the index of the database in the driver's list
#SQL STATEMENT# represents any SQL statement to execute

3 - And beyond ?

Well, normally the goal is to retrieve data through executing SQL statements, isn't it ?
To do so, please, take the time to explore The SQL Spellbook and give a sharp look at the Scripting APIs to incorporate RsDb in your project.
Also, the debugging script (RsDbDebugger.cs) and the editor script (RsDbEditorWindow.cs) provided with this package may be helpfull.

4 - The demonstration scene

A demonstration scene is provided with the package. It's a debugging / unitary test example that will generate a database.

First open the scene

Enter play mode and hit the start button

Quit play mode when the the execution is finished
Your example database is generated and awaits to be opened from your project folder

The RsDb Studio

The RsDb Studio is a graphical editor extension. This tool allows the user to view the structure and content of RsDb databases, browse the data, execute SQL statements, etc.

1 - Launch the studio

In menu [Menu], select [RsDb Studio]

A new dockable and resizable window is created.

2 - Create a new database

Click on the [New Database] icon then enter a name and a folder for your database in the modal file chooser window.
The default extension is [.rsdb] but you can use any other suitable one.

This is the default view of the studio. From top to bottom :

Logo / visual bar (cosmetic!)
Main toolbar with, from left ot right [New Database][Open Database] then the opened databases list
Database toolbar with, from left ot right [Close][Save][Save as] followed by the working modes [Data mode][SQL mode]
The main workspace corresponding to the currently active mode
General infos of the selected database

3 - Open an existing database

Click on the [Open Database] icon then select the file corresponding to the database to open.
The default extensions are [.rsdb] and [.sav] but you can use any other suitable one.

This is the default for an existing non empty database.
As we can see in this picture, the [Data mode] is selected and the tables are listed on the left. None being selected, the right part is left blank.

4 - Switching between opened databases

You can freely open various databases in the studio and switch from one to another using the toolbar as shown above.

5 - Close a database

Click on the [close Database] icon in order to close the currently selected database.

You will be prompted to confirm your choice.

6 - Save a database

Click on the [Save Database] icon to save the currently selected database.

7 - Save a database as

Click on the [Save Database] icon to save the currently selected database using a different name (by showing a filechooser modal dialog).
Note that the original archive is not modified nor deleted.

8 - The data mode

Click on the [Data mode] icon in order to activate the corresponding mode.

This is the default view for the [Data mode] of a non empty database. The tables are listed on the left.
Note that the system tables are not included : they have to be explicitely interrogated through an SQL statement.

In order to explore the content of a table, just select one in the list.
Four options are then available :

Select the row count
Select the content
Select the columns description
Select the constraints description

9 - What ? The content of my cell is truncated !

The width of the columns is fixed in order to keep the things simple.
If you wish to zoom in the content of a particular cell, just click on it

A layer containing the selectable content of the cell appears.
Use the [Close] button to hide it.
Use the [Copy] button to copy the full content of the area to the system clipboard.
This feature is available on any cell of any displayed result in any mode.

10 - The SQL mode

Click on the [SQL mode] icon in order to activate the corresponding mode.

Due to some "limitations" with the text components, two SQL modes are available !
Indeed, in one mode you are able to copy and paste content... without being able to execute any statement : it's the editon submode.
And in the other one, you are able to select a particular SQL statement and execute it, of course the text is still editable : it's the execution submode.

Click on the [SQL edition mode] icon in order to activate the corresponding mode.

Click on the [SQL execution mode] icon in order to activate the corresponding mode.

11 - Execute an SQL statement

In SQL execution mode, select the statement to be executed then click on the [Execute] button to start execution.

12 - Batches of SQL Statements

In SQL execution mode, select the statements to be executed then click on the [Execute] button to start execution.
A batch of SQL statements is a group of two or more SQL statements separated by semicolons (;). They will be executed asynchronously and sequentially.

13 - Commit and Rollback when autocommit is off

In SQL execution mode, when [AUTOCOMMIT] is set to off, two new buttons become visibles : one for [COMMIT] and one for [ROLLBACK].

The SQL Spellbook

This section contains the description of the SQL (Structured Query Language) used to manage information in a RsDb Database.

The entire database strucutre is described though 3 System tables as follow :

Table	Description
SYS_ALL_TABLES	Contains the name of all the declared tables in the database Columns are {NAME}
SYS_ALL_COLUMNS	Contains the description of all the declared columns of each table in the database Columns are {NAME, TYPE, IS_NULLABLE, IS_IDENTITY, LENGTH, DEFAULT, TABLE_NAME}
SYS_ALL_CONSTRAINTS	Contains the description of all the declared constraints on each column of each table in the database Columns are {NAME, TYPE, TABLE_NAME, COLUMN_NAME, FOREIGN_TABLE_NAME, FOREIGN_COLUMN_NAME}

Comments can be freely associated with SQL statements as follow :

Syntax	Description
/... .../	This is the syntax for a BLOCK OF COMMENT : it starts with /* and ends with */ The text can span multiple lines
//...	This is the syntax for a LINE OF COMMENT : it starts with // and ends with a line break \n

Here follows a description of each RsDb built-in datatype :

Datatype	Description
NUMERIC	Positive and negative fixed and floating-point numbers (using int or float 32 bits length datatypes internally) Internal ID is 2.
VARCHAR(size)	Strings having a variable length bounded between 1 and 65536 (64ko). If size is ommited or set to -1 then maximum is used else an exception is raised Internal ID is 12.
BOOLEAN	Having two values (TRUE and FALSE). Internal ID is 16.

Here follows a description of each RsDb term :

Term	Description
'string'	A string is always declared between two quotes If a string contains a quote then it must doubled (example : 'L'empereur' becomes 'L''empereur')
[-]number	A number, can be negative (see 'Arithmetic operators')
*[table.][column\|]**	A column name (the table name can be omitted) or all the columns of a table using the wildcard *
TRUE / FALSE	The TRUE and FALSE values
NULL	The NULL value can appear in any columns of any datatype not restricted to be NOT NULL NULL and comparison conditions : two nulls arguments are equals and one null argument is always 'less' NULL and SQL function : will always return NULL for a given null argument

The description of the operators :

Operator	Description
[-]number	The minus operator is the only arithmetic operator implemented in RsDb. It is an unary operator used to NEGATE a numeric expression
=	Equality test comparing two values
!=	Inequality test comparing two values
<	Less test comparing two values
<=	Less or equal test comparing two values
>	Greater test comparing two values
>=	Greater or equal test comparing two values

Here is the list of the available functions :

Function	Description
ABS(expr)	Returns the absolute value of expr (see Mathf.Abs)
ACOS(expr)	Returns the arc-cosine of expr (see Mathf.Acos)
ALERP(expr1,expr2,expr3)	Interpolates between expr1 (degrees) and expr2 (degrees) by expr3 around 360 degrees (see Mathf.LerpAngle)
APPROX(expr1,expr2)	Returns the similarity of expr1 and expr2 (see Mathf.Approximately)
ASIN(expr)	Returns the arc-sinus of expr (see Mathf.Asin)
ATAN(expr)	Returns the arc-tangent of expr (see Mathf.Atan)
ATAN2(expr1,expr2)	Returns the angle in radians whose Tan is y/x (see Mathf.Atan2)
AVG(expr)	Returns the average value of the values represented by expr Aggregate function
CEIL(expr)	Returns the smallest integer greater than or equal to expression (see Mathf.Ceil)
CLAMP(expr1,expr2,expr3)	Clamp expr1 between expr2 (min) and expr3 (max) (see Mathf.Clamp)
CONCAT(expr1,expr2)	Returns the concatenation of expr2 with expr2
COS(expr)	Returns the cosine of expr in radians (see Mathf.Cos)
COUNT(expr)	Returns how many rows a represented by expr Aggregate function
EXP(expr)	Returns constant e raised to expr power (see Mathf.Exp)
FLOOR(expr)	Returns the largest integer equal to or less than expr (see Mathf.Floor)
ILERP(expr1,expr2,expr3)	Returns the lerp value between expr1 and expr2 by expr3 (see Mathf.InverseLerp)
LERP(expr1,expr2,expr3)	Interpolates between expr1 and expr2 by expr3 (see Mathf.Lerp)
LOG(expr1,expr2)	Returns the logarithm of expr1 in the base specified by expr2 (see Mathf.Log)
MAX(expr)	Returns the maximum value of the values represented by expr Aggregate function
MIN(expr)	Returns the minimum value of the values represented by expr Aggregate function
POW(expr1,expr2)	Returns expr1 raised to the power specified by expr2 (see Mathf.Pow)
ROUND(expr)	Returns expr rounded to the nearest integer (see Mathf.Round)
SIGN(expr)	Returns 1 when expr is positive or zero, -1 when negative (see Mathf.Sign)
SIN(expr)	Returns the sine of expr in radians (see Mathf.Sin)
SQRT(expr)	Returns the square root of expr (see Mathf.Sqrt)
SUBSTR(expr1,expr2,expr3)	Returns a portion of expr1, beginning at expr2 and expr3 characters long
SUM(expr)	Returns sum of the values represented by expr Aggregate function
TAN(expr)	Returns the tangent of expr in radians (see Mathf.Tan)

The common syntax for the four arithmetic operators is not suported. Here follows the implemented functions in order to perform those calculus :

Function	Description
ADD(expr1,expr2)	Returns the numerical evaluation of expr1 + expr2 or concatenation (equiv. to CONCAT) if not possible
SUB(expr1,expr2)	Returns the numerical evaluation of expr1 - expr2
MUL(expr1,expr2)	Returns the numerical evaluation of expr1 * expr2
DIV(expr1,expr2)	Returns the numerical evaluation of expr1 / expr2

Logical operations are definied as follow :

Function	Description
expr1 AND expr2	Returns the boolean evaluation of expr1 AND expr2
expr1 OR expr2	Returns the boolean evaluation of expr1 OR expr2
NOT expr	Returns the boolean negation of expr

Aside from self explanatory elements, here follows some rules :

Column	Description
DEFAULT	DEFAULT is used to replace any omitted or NULL value provided in an INSERT or UPDATE statement for the column. Of course DEFAULT must be compatible with the SQL type of the column.
NOT NULL	Regardless of the type of the column, a value must be always provided unless a DEFAULT value was declared for the column.
IDENTITY	An IDENTITY column can only be NUMERIC. It auto-increments itself by one on any INSERT statement. First value is one (1) by default. It can't be updated. It thus acts as a sequence. Any value provided in an INSERT statement for such a column will be substituted by the current sequence value. An IDENTITY column adds a UNIQUE constraint automatically.

Constraint	Description
UNIQUE	Means that each value in the column can't repeat itself. An IDENTITY column adds a UNIQUE constraint automatically. Internal ID is 0 for the UNIQUE constraint.
FOREIGN KEY	All the fields of this column will REFERENCE the values of a FOREIGN table.column (a symetric constraint is declared for internal use only). The FOREIGN table.column should strongly have a UNIQUE constraint on it. Both columns should also share the same type. Internal ID is 1 for the IMPORTED KEY constraint (for the column that uses another column as reference). Internal ID is 2 for the EXPORTED KEY constraint (for the column that is referenced by another column).

Here follows some rules relative to columns :

Column	Description
NOT NULL	Can not be verified on a non empty table : will ALWAYS raise an error !
IDENTITY	Can not be verified on a non empty table : will ALWAYS raise an error !

Here follows some rules relative to constraints :

Constraint	Description
UNIQUE	Always checked : may raise an error
FOREIGN KEY	Always checked : may raise an error

Note that when dropping a column, all the constraints using this column are removed too.

The name of the constraint must be used : it is automatically generated by the system when created and respect the following rules :

Constraint	Name of the constraint
UNIQUE	UNIQUE_#TableName#.#ColumnName# Example : if table T1 column C1 owns a unique constraint on it then UNIQUE_T1.C1 on T1
FOREIGN KEY	IMPORTED KEY side : IMPORTED_#foreignTableName#.#foreignColumnName# EXPORTED KEY side : EXPORTED_#TableName#.#ColumnName# (can't be directly dropped but automaticaly removed when the IMPORTED one is dropped) Example : if table T1 column C1 references table T2 column C2 then IMPORTED_T2.C2 on T1 and EXPORTED_T1.C1 on T2

Note that when dropping a table, all the constraints using a column of this table are removed too.
Back to top

A successfull UPDATE statement will return a result object (RsDbResult) containing all the modified rows.
As a good practice, it is always recommended to turn AUTOCOMMIT to FALSE when updating.

Back to top

A successfull DELETE statement will return a result object (RsDbResult) containing all the deleted rows.
As a good practice, it is always recommended to turn AUTOCOMMIT to FALSE when deleting.

Back to top

Some precisions about those parameters :

Statement	Description
AUTOCOMMIT	Default is TRUE A commit is always executed before changing AUTOCOMMIT mode
READONLY	Default is FALSE When changing this mode no action on pending transactions is processed
MAXROWS	Default is 5000 (…and minimum is 1 by the way) This parameter limits the maximum number of rows a result object can carry for a SELECT statement

Scripting APIs

In this section are described all the suitable classes for a normal and happy integration of RsDb in a project.

namespace com.heparo.rsdb

public class RsDbDriverAsync : ARsDbDriver

This class is the convenient object to instanciate in order to use the RsDb system in an asynchronous way.
Any client class must register a listener with it in order to reveive event notifications and prior to any database manipulation.

public RsDbDriverAsync()
Constructor

public override void Load(string path)
IRsDbDriver interface method

public override void Connect(string path)
IRsDbDriver interface method

public override RsDbResult ExecuteAt(int index, string sql)
IRsDbDriver interface method

public override void CloseAt(int index)
IRsDbDriver interface method

public override void SaveAt(int index)
IRsDbDriver interface method

public override void SaveAsAt(int index, string path)
IRsDbDriver interface method

public void ProcessExecutionEvent(RsDb database, RsDbResult result)
Process with an execution event (invoked by another thread in a callback mode). This method should never be invoked directly.
Parameter(s) :
- database : the database object concerned by the event
- result : the result object generated by the execution
Returns :
- nothing

public void ProcessSaveEvent(RsDb database)
Process with a save event (invoked by another thread in a callback mode). This method should never be invoked directly.
Parameter(s) :
- database : the database object concerned by the event
Returns :
- nothing

public void ProcessLoadEvent(RsDb database)
Process with a loading event (invoked by another thread in a callback mode). This method should never be invoked directly.
Parameter(s) :
- database : the database object concerned by the event
Returns :
- nothing

public void ProcessCloseEvent(RsDb database)
Process with a close event (invoked by another thread in a callback mode). This method should never be invoked directly.
Parameter(s) :
- database : the database object concerned by the event
Returns :
- nothing

public void ProcessExceptionEvent(RsDb database, System.Exception exception)
Process with an exception event (invoked by another thread in a callback mode). This method should never be invoked directly.
Parameter(s) :
- database : the database object concerned by the event
- exception : the exception object concerned by the event
Returns :
- nothing

public void AddRsDbDriverListener(IRsDbDriverListener listener)
Add a listener
Parameter(s) :
- listener : the listener to add to the listerners list
Returns :
- nothing

public void RemoveRsDbDriverListener(IRsDbDriverListener listener)
Remove a listener
Parameter(s) :
- listener : the listener to remove from the listerners list
Returns :
- nothing

namespace com.heparo.rsdb

public class RsDbDriverSync : ARsDbDriver

This class is the convenient object to instanciate in order to use the RsDb system in a synchronous way.

public RsDbDriverSync()
Constructor

public override void Load(string path)
IRsDbDriver interface method

public override void Connect(string path)
IRsDbDriver interface method

public override RsDbResult ExecuteAt(int index, string sql)
IRsDbDriver interface method

public override void CloseAt(int index)
IRsDbDriver interface method

public override void SaveAt(int index)
IRsDbDriver interface method

public override void SaveAsAt(int index, string path)
IRsDbDriver interface method

namespace com.heparo.rsdb

public class RsDbResult

This class represents the result returned after executing a statement that queries the database.

public RsDbResult()
Constructor

public RsDbResult(List<RsDbRow> rows)
Constructor
Parameter(s) :
- rows : the rows representing the content if this result object

public bool Next()
Moves the reading cursor forward one row from its current position
The cursor is initially positioned before the first row : this method must be called prior to any value reading
Parameter(s) :
- nothing
Returns :
- TRUE if end of the set is not reached, FALSE otherwise

public bool Previous()
Moves the reading cursor backward one row from its current position
Parameter(s) :
- nothing
Returns :
- TRUE if start of the set is not reached, FALSE otherwise

public bool IsEmpty()
Test if this result is empty or not
Parameter(s) :
- nothing
Returns :
- TRUE if result is not empty, FALSE otherwise

public int GetColumnsCount()
Get the number of columns in the result
Parameter(s) :
- nothing
Returns :
- the number of columns

public int GetRowsCount()
Get the number of rows in the result
Parameter(s) :
- nothing
Returns :
- the number of rows

public List<RsDbRow> GetRows()
Get the all the rows of this result
Parameter(s) :
- nothing
Returns :
- the list containing all the rows

public RsDbRow GetRow()
Get the current row of this result
Parameter(s) :
- nothing
Returns :
- the selected row

public float GetFloat(int columnIndex)
Retrieves the value at columnIndex in the current row as float
Parameter(s) :
- columnIndex : the index of the desired column in the current selected row
Returns :
- the value as float or a System.Exception if an error is raised

public int GetInt(int columnIndex)
Retrieves the value at columnIndex in the current row as int
Parameter(s) :
- columnIndex : the index of the desired column in the current selected row
Returns :
- the value as int or a System.Exception if an error is raised

public bool GetBoolean(int columnIndex)
Retrieves the value at columnIndex in the current row as bool
Parameter(s) :
- columnIndex : the index of the desired column in the current selected row
Returns :
- the value as bool or a System.Exception if an error is raised

public string GetString(int columnIndex)
Retrieves the value at columnIndex in the current row as string
Parameter(s) :
- columnIndex : the index of the desired column in the current selected row
Returns :
- the value as string or a System.Exception if an error is raised

public string GetStringNoNull(int columnIndex)
Retrieves the value at columnIndex in the current row as string or the RsDb constant for NULL
Parameter(s) :
- columnIndex : the index of the desired column in the current selected row
Returns :
- the value as string or the RsDb constant for NULL (RsDbConstants.NULL)

public DateTime GetTimestamp(int columnIndex)
Retrieves the value at columnIndex in the current row as a DateTime object
Parameter(s) :
- columnIndex : the index of the desired column in the current selected row
Returns :
- the value as DateTime (RsDbConstants.DATETIME_TIMESTAMP_FORMAT) parsing a string value

public DateTime GetDate(int columnIndex)
Retrieves the value at columnIndex in the current row as a DateTime object
Parameter(s) :
- columnIndex : the index of the desired column in the current selected row
Returns :
- the value as DateTime (RsDbConstants.DATETIME_DATE_FORMAT) parsing a string value

public DateTime GetTime(int columnIndex)
Retrieves the value at columnIndex in the current row as a DateTime object
Parameter(s) :
- columnIndex : the index of the desired column in the current selected row
Returns :
- the value as DateTime (RsDbConstants.DATETIME_TIME_FORMAT) parsing a string value

public System.Object GetObject(int columnIndex)
Retrieves the value at columnIndex in the current row as System.Object
Parameter(s) :
- columnIndex : the index of the desired column in the current selected row
Returns :
- the System.Object representing the value in the row as is, can be NULL

public void Close()
Close the result object : cleaning method

namespace com.heparo.rsdb

public class RsDbConstants

This class contains all the constants used by the RsDb package (here are only listed the usefull ones)

public const int DRIVER_NO_EVENT
The constant used when no driver event is pending

public const int DRIVER_EXCEPTION_EVENT
The constant used when current event is about a raised exception

public const int DRIVER_LOAD_EVENT
The constant used when current event is about a database successfully loaded

public const int DRIVER_EXEC_EVENT
The constant used when current event is about a statement successfully executed

public const int DRIVER_SAVE_EVENT
The constant used when current event is about a database successfully saved

public const int DRIVER_CLOSE_EVENT
The constant used when current event is about a database successfully closed

public const string DEFAULT_EXTENSION
Default extension is "rsdb"

public const string DEFAULT_MAXROWS
Default MAXROWS is 5000

public const string VARCHAR_MAX_LENGTH
Default VARCHAR max length is 65536

public const string DATETIME_TIMESTAMP_FORMAT
Format is "yyyy-MM-dd HH:mm:ss,fff"
Any verchar stored in order to be converted to such value must respect this format

public const string DATETIME_DATE_FORMAT
Format is "yyyy-MM-dd"
Any verchar stored in order to be converted to such value must respect this format

public const string DATETIME_TIME_FORMAT
Format is "HH:mm:ss"
Any verchar stored in order to be converted to such value must respect this format

public const string NULL
The constant used to represent the NULL as a string

namespace com.heparo.rsdb

public class RsDbDriverListener : IRsDbDriverListener

This class implements the interface IRsDbDriverListener

public RsDbDriverListener()
Constructor

namespace com.heparo.rsdb

public class RsDbDriverEvent

This class represents an event fired by a RsDbDriver to all its listeners.

public RsDbDriverEvent(int type, int index)
Constructor
Parameter(s) :
- type : the type of event
- index : the index of the database

public RsDbDriverEvent(int type, int index, RsDbResult result)
Constructor
Parameter(s) :
- type : the type of event
- index : the index of the database - result : the result returned by the database in response of a statement execution

public RsDbDriverEvent(int type, int index, System.Exception exception)
Constructor
Parameter(s) :
- type : the type of event
- index : the index of the database - exception : the exception raised by the database

public int GetEventType()
Get the type of the event
Parameter(s) :
- nothing
Returns :
- the type of the event

public int GetDatabaseIndex()
Get the index of the database concerned by this event
Parameter(s) :
- nothing
Returns :
- the index of the database in the driver's repository

public RsDbResult GetResult()
Get the result embedded in this event
Parameter(s) :
- nothing
Returns :
- the result object or NULL

public System.Exception GetException()
Get the exception embedded in this event
Parameter(s) :
- nothing
Returns :
- the exception object or NULL

namespace com.heparo.rsdb

public abstract class ARsDbDriver : IRsDbDriver

This class represents the abstract class for the drivers of the RsDb system.

public int GetDatabasesCount()
IRsDbDriver interface method

public RsDb GetDatabaseAt(int index)
IRsDbDriver interface method

public string GetDatabaseNameAt(int index)
IRsDbDriver interface method

public List<string> GetDatabasesNameList()
IRsDbDriver interface method

public List<string> GetDatabasesPathList()
IRsDbDriver interface method

public int GetDatabaseIndex(string name)
IRsDbDriver interface method

public void CommitAt(int index)
IRsDbDriver interface method

public void RollbackAt(int index)
IRsDbDriver interface method

public bool IsAutoCommitAt(int index)
IRsDbDriver interface method

public void SetAutoCommitAt(int index, bool autoCommit)
IRsDbDriver interface method

public bool IsReadOnlyAt(int index)
IRsDbDriver interface method

public void SetReadOnlyAt(int index, bool readOnly)
IRsDbDriver interface method

public int GetMaxRowsAt(int index)
IRsDbDriver interface method

public void SetMaxRowsAt(int index, int max)
IRsDbDriver interface method

Extending classes :

RsDbDriverAsync
RsDbDriverSync

namespace com.heparo.rsdb

public interface IRsDbDriver

This interface describes all the suitable methods necessary to dialog with the RsDb databases

int GetDatabasesCount()
Get the number of opened databases in the repository.
Parameter(s) :
- nothing
Returns :
- the number of opened databases

RsDb GetDatabaseAt(int index)
Get the database at the specified index.
Parameter(s) :
- index : the index of the desired database to retrieve.
Returns :
- the database object corresponding to the index in the repository.

string GetDatabaseNameAt(int index)
Get the name of the database at index.
Parameter(s) :
- index : the index of the database.
Returns :
- the name of the database corresponding to index in the repository.

public List<string> GetDatabasesNameList()
Get the list containing the names of all the databases.
Parameter(s) :
- nothing
Returns :
- a list containing the names of the databases.

public int GetDatabaseIndex(string name)
Get the index of a database using its name.
Parameter(s) :
- name : the names of the database
Returns :
- the index of the corresponding database

void CommitAt(int index)
Commit a transaction (or a set of transactions) for the database at specified index.
Parameter(s) :
- index : the index of the database
Returns :
- nothing

void RollbackAt(int index)
Rollback a transaction (or a set of transactions) for the database at specified index.
Parameter(s) :
- index : the index of the database
Returns :
- nothing

bool IsAutoCommitAt(int index)
Is a database in autocommit mode ?
Parameter(s) :
- index : the index of the database
Returns :
- true if the database is in autocimmit mode, false otherwise

void SetAutoCommitAt(int index, bool autoCommit)
Set a database autocommit mode
Parameter(s) :
- index : the index of the database
- autoCommit : true if autocommit, false otherwise
Returns :
- nothing

bool IsReadOnlyAt(int index)
Is a database in read only mode ?
Parameter(s) :
- index : the index of the database
Returns :
- true if the database is in autocimmit mode, false otherwise

void SetReadOnlyAt(int index, bool readOnly)
Set a database read only mode
Parameter(s) :
- index : the index of the database
- readOnly : true if read only, false otherwise
Returns :
- nothing

int GetMaxRowsAt(int index)
Retrieves the maximum number of rows in a result object
Parameter(s) :
- index : the index of the database
Returns :
- the maximum number of rows in a result object

void SetMaxRowsAt(int index, int max)
Set the maximum number of rows in a result object
Parameter(s) :
- index : the index of the database
- max : the new maximum value
Returns :
- nothing

void Load(string path)
Open a database (same as Connect)
Parameter(s) :
- path : the path to the database file
Returns :
- nothing

void Connect(string path)
Open a database
Parameter(s) :
- path : the path to the database file
Returns :
- nothing

RsDbResult ExecuteAt(int index, string sql)
Execute an SQL statement
SELECT, UPDATE and DELETE will return a result object containing the matching rows
Notice that only the result of a SELECT is clamped to the max rows parameter
Parameter(s) :
- index : the index of the database
- sql : the SQL statement to execute
Returns :
- a RsDbResult object or NULL

void CloseAt(int index)
Close a database
Parameter(s) :
- index : the index of the database
Returns :
- nothing

void SaveAt(int index)
Save a database
Parameter(s) :
- index : the index of the database
Returns :
- nothing

void SaveAsAt(int index, string path)
Save a database as provided path
Parameter(s) :
- index : the index of the database
- path : the new path of the database
Returns :
- nothing

Implementing classes :

ARsDbDriver

namespace com.heparo.rsdb

public interface IRsDbDriverListener

This interface describes all the suitable methods necessary to implement a class able to manage the events fired by the RsDbDriverAsync class

void OnRsDbDriverEvent(RsDbDriverEvent e)
Invoked when the driver fires a new event to its listeners
Parameter(s) :
- RsDbDriverEvent : the event fired by the driver
Returns :
- nothing

void ConsumeEvent()
Consume current event
Parameter(s) :
- nothing
Returns :
- nothing

int GetNextEventType()
Dequeue next pending event and shall be called AFTER ConsumeEvent()
Parameter(s) :
- nothing
Returns :
- int : the type of the next event (see RsDbConstants)

int GetEventType()
Get current event type
Parameter(s) :
- nothing
Returns :
- int : the type of the current event (see RsDbConstants)

int GetDatabaseIndex()
Get the index of the current database in the driver repository
Parameter(s) :
- nothing
Returns :
- int : the index of the database concerned by the event in the driver's repository or -1

RsDbResult GetResult()
Get result embedded in this event
Parameter(s) :
- nothing
Returns :
- RsDbResult : the result of the initial query or NULL

System.Exception GetException()
Get the exception embedded in this event
Parameter(s) :
- nothing
Returns :
- System.Exception : The exception embedded in this event or NULL

Implementing classes :

RsDbDriverListener

Known bugs or malfunctions

1 - The text selection / copy / paste problem

Using : RsDb Editor + SQL text edition area or detail view

As stated before, there is a variation between GUI text components : some handle copy / paste other don't but instead handle the selection
Waiting for a futur GUI component evolution...

2 - The "String too long for TextMeshGenerator. Cutting off characters" exception

Using : Unity 5 + RsDb Editor + VARCHAR larger than, let's say, 2^14 = 16384 characters

Unity will throw numerous exceptions on to the console : this won't crash the editor but will be visually annoying.
It seems to be related to a GUI components regression / bug (see here and here if i am not mistaken)

However, it is true that GUI components (TextArea for example) have a limited displayable text size !
You can freely see it with demo database, text is truncated (table view or detail view), using Unity 4 or 5.
This is a 'view' problem (Unity GUI components), not a data alteration problem !
Note that, the SQL text area il too limited.

Changelog

Version 20210410170000

Uploaded version for LTS unity editor version (2020.3.2 at the moment)
Price is lowered as it should be
By personal choice (and because of toooo many versions), I won't upload packages anymore for older versions of Unity : I will stick to the latest LTS version available. My deepest apologies for the inconvenience...
Starting with 2020 version, the editor has now a descent splitpane
The previous packages are kept as is for compatibility purpose

Version 20180813180000

RsDb : SELECT bug correction (and RsDbDebugger is updated accordingly)

Version 20180515170000

RsDb studio : adding support for API change when using [EditorApplication.playModeStateChanged] from 5.x to 2017.2 then 2018.1
RsDb is compatible with Unity 2018.1 HRP and LRP projects

Version 20171127160000

RsDb studio : while in play mode, launching the editor is now blocked and a dialog informs the user
RsDb studio : the instance of the EditorWindow could sometimes be lost and it is now better handled (OnDisable and OnEnable)
RsDb studio : an empty result now explicitly generates an empty result grid with a "No result found" message
RsDb studio : the case of executed statements are not uppered any more
RsDb parser : an horrible mystake is now corrected with the right handling of double quoted in a string value (documentation of 'Term' is updated )

Version 20170118120000

RsDb studio : EditorWindow.GetWindow is now handled properly avoiding taskbar flashings and other focus problems
Serializer : using UNITY_5_0_OR_NEWER all databases will now be compressed using GZipStream (normal decompression mode is used if file is not a GZip one)

Version 20161117120000

Initial released version

Contacts

A discussion on the Unity assets subforum should be available, visit and find it here

Also, for any issue / problem / bug / question relative to RsDb, please feel free to send me an email at : contact@heparo.com

Please, do not forget to place [RsDb] at the beginning of your mail subject.

I speak english
Hablo español
Je parle français

Thank you for puchasing and using this asset !

Kind regards,

HEPARO / Hervé PARONNAUD (www.heparo.com)

All material included in the [RsDb] package was made by me except when mentioned explicitly

Asset Store Terms of Service and EULA
Please refer to : Asset Store Terms of Service and EULA

RsDb Manual

Overview

Content

Getting started

The RsDb Studio

The SQL Spellbook

Scripting APIs

Known bugs or malfunctions

Changelog

Contacts