Database

The Database subsystem is built into the Urho3D library only when one of these two build options are enabled: URHO3D_DATABASE_ODBC and URHO3D_DATABASE_SQLITE. When both options are enabled then URHO3D_DATABASE_ODBC takes precedence. These build options determine which database API the subsystem will use. The ODBC DB API is more suitable for native application, especially the game server, where it allows the app to establish connection to any ODBC compliant databases like SQLite, MySQL/MariaDB, PostgreSQL, Sybase SQL, Oracle, etc. The SQLite DB API, on the other hand, is suitable for mobile application which embeds the SQLite database and its engine into the app itself. The Database subsystem wraps the underlying DB API using a unified URHO3D API, so no or minimal code changes are required to the library user when switching between these two build options.

Currently the implementation just supports immediate SQL statement execution. Prepared statements and transaction management will be added later when the need arises. The subsystem has a simple database connection pooling capability. This internal database connection pool should not be confused with ODBC connection pool option when ODBC DB API is being used. The internal pooling is enabled by default, except when ODBC DB API is being used and when ODBC driver manager 3.0 or later is being detected in the host system, in which case the ODBC connection pool option should be used instead to manage the database connection pooling.

Establishing database connection

A new database connection is established by calling Connect() and passing it with a so-called database connection string. The database connection string does not only identify which database to connect to, but also other relevant database connection settings like: database user id, user password, host, and port number, etc. The format of the database connection string is controlled by the underlying DB API. In general the connection string is really the only thing that need to be changed when switching the underlying DB API and when switching the databases to connect to.

When the connection is successfully established, a valid DbConnection object is returned. After done with the database connection, an application should disconnect the database connection by calling the Disconnect(). This is a good practise. When the Urho3D game engine exits, the destructor of the database subsystem automatically disconnects all the still active database connections.

ODBC connection string

The exact format of the ODBC connection string depends on the ODBC driver for a specific database vendor. Consult the accompanying documentation of the specific ODBC driver on how to construct the database connection string. Both DSN and DSN-less connection string are supported. For the purpose of illustration, this subsection will just explain how to connect to a MySQL/MariaDB database as an example. The MySQL/MariaDB database server is assumed to be running on a localhost on a default port with database name called 'test', and user id & password are "testuser" & "testpassword".

DSN connection string

Use the GUI tool provided by host system to define the ODBC data source. It can be user DSN or system DSN. On Linux host system, simply add the new data source entry in the '~/.odbc.ini' or '/etc/odbc.ini', respectively. Create the file if it does not exist yet. The data source entry must at least contains the following information.

# These settings assume the host system uses unixODBC as ODBC driver manager
# Settings for iODBC driver manager would be slightly different
[testDSN]
Driver=MariaDB      # This is the name of the ODBC driver installed in the /etc/odbcinst.ini
Description=MariaDB test database
Server=localhost
Port=
User=testuser
Password=testpassword
Database=test
Option=3
Socket=

To connect to this data source, use the following connection string:

DSN=testDSN

DSN-less connection string

To connect to the same database above without pre-configuring it as an ODBC data source, use the connection string like this:

Driver=MariaDB;Database=test;User=testuser;Password=testpassword;Option=3

SQLite connection string

The SQLite database is a single disk file. The SQLite connection string is simply a path to the location of that single disk file. Both absolute and relative paths work. When the path is valid but the disk file does not exist yet then SQLite database engine will automatically create the disk file and hence create a new empty database in the process. The drawback with this approach is, there is no way to pass additional database connection settings. In order to do that, SQLite DB API also supports connection string using RFC 3986 URI format. Consult SQLite documentation on URI format for more detail. For illustration purposes, this subsection will assume the SQLite disk file is called 'test.db' located in home directory and current working directory is home directory.

Path connection string

With the above assumption, the following example connection strings work equally.

Relative path:

test.db

Or absolute path:

/home/testuser/test.db

URI connection string

In this format the additional database connection setting can be passed as query parameters. As an example, to connect to the same database as above but in read-only and shared-cache mode then the connection string can be rewritten as:

file:./test.db?mode=ro&cache=shared

Or:

file:///home/testuser/test.db?mode=ro&cache=shared

Immediate SQL statement execution

Use the Execute() to execute an SQL statement in immediate mode. In immediate mode, the SQL statement is opened, prepared, executed, and finalized in one go. It is a convenient method to perform adhoc query but it may not be good for system performance when a same query is being performed repeatedly. The method returns a DbResult object regardless of whether the query type is DML (Data Manipulation Language) or DDL (Data Definition Language). The DbResult object only contains the resultset when the SQL statement being executed is a select query. Use the GetNumColumns() and GetNumRows() to find out the size of the resultset. Use the GetColumns() and GetRows() to get the actual column headers data and rows data, respectively. For DML statement, use the GetNumAffectedRows() to find out the number of affected rows.

The number of rows in the DbResult object may be less than the actual number of rows being fetched from the database. This is because the fetched rows could be instructed to be filtered out by E_DBCURSOR event handler. The whole rows fetching process could also be aborted upon request of E_DBCURSOR event handler.

SQL execution using prepared statements and dynamic parameter bindings

Not yet supported at this moment.

Transaction Management

Not yet supported at this moment. Currently all statements are auto-committed unless the database is connected as read-only (in which case DML and DDL statements would cause an error to be logged).

Database cursor event

When executing an SQL statement there is an option to enable the sending of database cursor event. This event is sent on each row in the resultset as it is being fetched. i.e. the events could be sent multiple times before the Execute() method returns. Application can subscribe to this event to influence how the resultset is being populated in the DbResult object.

The E_DBCURSOR has input and output parameters.

Output parameters:

P_SQL        SQL query string currently being executed (String)
P_NUMCOLS    Number of columns in the resultset (unsigned)
P_COLHEADERS Column headers in the resultset (StringVector)
P_COLVALUES  Row data as it is being fetched (VariantVector)

Input parameters:

P_FILTER     Set to true to filter out this row from the DbResult object
P_ABORT      Set to true to abort further fetching process

The P_FILTER could be used for any additional client-side filtering logic that is otherwise difficult to be carried out at the database server-side using WHERE or HAVING clause. The P_ABORT when used does not affect rows that are already populated into DbResult object.