Readme

General data access, storage and manipulation of persistent data in a database.

Migration

Migration from Legacy NWNX

Environment Variables

NWNX_SQL_TYPE

Controls the target database type used at runtime.

Possible values (case insensitive):

MYSQL
POSTGRESQL
SQLITE

Example

export NWNX_SQL_TYPE=POSTGRESQL

NWNX_SQL_HOST

Host name where the database resides. Typically localhost but can be any valid host name or IP address.

Not used if using SQLite.

Example

export NWNX_SQL_HOST=localhost

NWNX_SQL_PORT

The port for the database connection. If left unset it will use the default port.

Only used by MySQL.

Example

export NWNX_SQL_PORT=3307

NWNX_SQL_USERNAME

Database username used for the connection.

Not used if using SQLite.

Example

export NWNX_SQL_USERNAME=nwn_db

NWNX_SQL_PASSWORD

Password for the database connection.

Not used if using SQLite.

Example

export NWNX_SQL_PASSWORD=lathander_rulez

NWNX_SQL_DATABASE

The database to connect to.

The filename of the database when using SQLite, it is stored in the UserDirectory/database folder.

Example

export NWNX_SQL_DATABASE=mymodulename

NWNX_SQL_QUERY_METRICS

Export query execution metrics.

The Metrics_InfluxDB plugin and a visualizer like Grafana are required to view these metrics.

Example

export NWNX_SQL_QUERY_METRICS=true

NWNX_SQL_USE_UTF8

Convert all strings going between the database and game to/from UTF8

This takes into account the core locale as well.

Example

export NWNX_SQL_USE_UTF8=true

NWNX_SQL_CHARACTER_SET

Set the connection's character set to be used.

Only supported on mysql and pgsql. For sqlite this can be achieved with a query.

Examples (MySQL)

export NWNX_SQL_CHARACTER_SET=utf8

export NWNX_SQL_CHARACTER_SET=cp1251

Examples (PostgreSQL)

export NWNX_SQL_CHARACTER_SET=WIN1252

(see https://www.postgresql.org/docs/current/multibyte.html for list)

Files
file	nwnx_sql.nss

Functions
int	NWNX_SQL_PrepareQuery (string query)
	Prepares the provided query for execution. More...

int	NWNX_SQL_ExecutePreparedQuery ()
	Executes a query which has been prepared. More...

int	NWNX_SQL_ExecuteQuery (string query)
	Directly execute an SQL query. More...

int	NWNX_SQL_ReadyToReadNextRow ()

void	NWNX_SQL_ReadNextRow ()
	Reads the next row of returned data. More...

string	NWNX_SQL_ReadDataInActiveRow (int column=0)

void	NWNX_SQL_PreparedInt (int position, int value)
	Set the int value of a prepared statement at given position. More...

void	NWNX_SQL_PreparedString (int position, string value)
	Set the string value of a prepared statement at given position. More...

void	NWNX_SQL_PreparedFloat (int position, float value)
	Set the float value of a prepared statement at given position. More...

void	NWNX_SQL_PreparedObjectId (int position, object value)
	Set the ObjectId value of a prepared statement at given position. More...

void	NWNX_SQL_PreparedObjectFull (int position, object value, int base64=TRUE)
	Set the full serialized object value of a prepared statement at given position. More...

void	NWNX_SQL_PreparedNULL (int position)
	Set the NULL value of a prepared statement at given position. More...

void	NWNX_SQL_PreparedJson (int position, json value)
	Set the Json value of a prepared statement at given position. Convienence function to match other Prepared(type) functions. More...

object	NWNX_SQL_ReadFullObjectInActiveRow (int column=0, object owner=OBJECT_INVALID, float x=0.0, float y=0.0, float z=0.0, int base64=TRUE)
	Like NWNX_SQL_ReadDataInActiveRow, but for full serialized objects. More...

int	NWNX_SQL_GetAffectedRows ()
	Gets the rows affected by a query. More...

string	NWNX_SQL_GetDatabaseType ()

void	NWNX_SQL_DestroyPreparedQuery ()
	Free any resources attached to an existing prepared query. More...

string	NWNX_SQL_GetLastError ()

int	NWNX_SQL_GetPreparedQueryParamCount ()
	Gets the number of parameteres expected by a prepared query. More...

void	NWNX_SQL_PostgreSQL_SetNextQueryResultsBinaryMode ()
	Set the next query to return full binary results ON THE FIRST COLUMN ONLY. More...

Function Documentation

◆ NWNX_SQL_PrepareQuery()

int NWNX_SQL_PrepareQuery ( string query )

Prepares the provided query for execution.

Note: This does not execute the query. Will also clear any previous state.

Parameters

query The query to prepare.

Returns: TRUE if the query was successfully prepared.

Definition at line 117 of file nwnx_sql.nss.

◆ NWNX_SQL_ExecutePreparedQuery()

int NWNX_SQL_ExecutePreparedQuery ( )

Executes a query which has been prepared.

Returns: The ID of this query if successful, else FALSE.

Definition at line 126 of file nwnx_sql.nss.

◆ NWNX_SQL_ExecuteQuery()

int NWNX_SQL_ExecuteQuery ( string query )

Directly execute an SQL query.

Note: Clears previously prepared query states.

Returns: The ID of this query if successful, else FALSE.

Definition at line 134 of file nwnx_sql.nss.

◆ NWNX_SQL_ReadyToReadNextRow()

int NWNX_SQL_ReadyToReadNextRow ( )

Returns: TRUE if one or more rows are ready, FALSE otherwise.

Definition at line 147 of file nwnx_sql.nss.

◆ NWNX_SQL_ReadNextRow()

void NWNX_SQL_ReadNextRow ( )

Reads the next row of returned data.

Remarks: Should only be called after a successful call to NWNX_SQL_ReadyToReadNextRow().

Definition at line 155 of file nwnx_sql.nss.

◆ NWNX_SQL_ReadDataInActiveRow()

string NWNX_SQL_ReadDataInActiveRow ( int column = 0 )

Parameters

column The column to read in the active row.

Returns: Data at the nth (0-based) column of the active row.

Remarks: Should only be called after a successful call to NWNX_SQL_ReadNextRow().

Definition at line 162 of file nwnx_sql.nss.

◆ NWNX_SQL_PreparedInt()

void NWNX_SQL_PreparedInt	(	int	position,
		int	value
	)

Set the int value of a prepared statement at given position.

Parameters

position	The nth ? in a prepared statement.
value	The value to set.

Definition at line 172 of file nwnx_sql.nss.

◆ NWNX_SQL_PreparedString()

void NWNX_SQL_PreparedString	(	int	position,
		string	value
	)

Set the string value of a prepared statement at given position.

Parameters

position	The nth ? in a prepared statement.
value	The value to set.

Definition at line 180 of file nwnx_sql.nss.

◆ NWNX_SQL_PreparedFloat()

void NWNX_SQL_PreparedFloat	(	int	position,
		float	value
	)

Set the float value of a prepared statement at given position.

Parameters

position	The nth ? in a prepared statement.
value	The value to set.

Definition at line 188 of file nwnx_sql.nss.

◆ NWNX_SQL_PreparedObjectId()

void NWNX_SQL_PreparedObjectId	(	int	position,
		object	value
	)

Set the ObjectId value of a prepared statement at given position.

Parameters

position	The nth ? in a prepared statement.
value	The value to set.

Definition at line 196 of file nwnx_sql.nss.

◆ NWNX_SQL_PreparedObjectFull()

void NWNX_SQL_PreparedObjectFull	(	int	position,
		object	value,
		int	base64 = `TRUE`
	)

Set the full serialized object value of a prepared statement at given position.

Parameters

position	The nth ? in a prepared statement.
value	The value to set.
base64	Use base64-encoded string format if TRUE (default), otherwise use binary format.

Definition at line 204 of file nwnx_sql.nss.

◆ NWNX_SQL_PreparedNULL()

void NWNX_SQL_PreparedNULL ( int position )

Set the NULL value of a prepared statement at given position.

Parameters

position The nth ? in a prepared statement.

Definition at line 213 of file nwnx_sql.nss.

◆ NWNX_SQL_PreparedJson()

void NWNX_SQL_PreparedJson	(	int	position,
		json	value
	)

Set the Json value of a prepared statement at given position. Convienence function to match other Prepared(type) functions.

Parameters

position	The nth ? in a prepared statement.
value	The value to set.

Definition at line 220 of file nwnx_sql.nss.

◆ NWNX_SQL_ReadFullObjectInActiveRow()

object NWNX_SQL_ReadFullObjectInActiveRow	(	int	column = `0`,
		object	owner = `OBJECT_INVALID`,
		float	x = `0.0`,
		float	y = `0.0`,
		float	z = `0.0`,
		int	base64 = `TRUE`
	)

Like NWNX_SQL_ReadDataInActiveRow, but for full serialized objects.

The object will be deserialized and created in the game. New object ID is returned.

The exact behavior depends on type of deserialized object and owner object:

If object is an item, and owner if placeable, creature or container, the item will be created in its inventory
If owner is an area, the object will be created on the ground at Vector(x,y,z)
Otherwise, the object will be created outside the world and needs to be ported manually.

Parameters

column	The column to read in the active row.
owner	The owner of the object.
x,y,z	The vector for objects to be placed in areas.
base64	Use base64-encoded string format if TRUE (default), otherwise use binary format.

Returns: The deserialized object.

Definition at line 228 of file nwnx_sql.nss.

◆ NWNX_SQL_GetAffectedRows()

int NWNX_SQL_GetAffectedRows ( )

Gets the rows affected by a query.

Remarks: This command is for non-row-based statements like INSERT, UPDATE, DELETE, etc.

Returns: Number of rows affected by SQL statement or -1 if the query was not non-row-based.

Definition at line 242 of file nwnx_sql.nss.

◆ NWNX_SQL_GetDatabaseType()

string NWNX_SQL_GetDatabaseType ( )

Gets the database type.

Returns: The database type we're interacting with.

Remarks: This is the same value as the value of NWNX_SQL_TYPE environment variable.

Definition at line 250 of file nwnx_sql.nss.

◆ NWNX_SQL_DestroyPreparedQuery()

void NWNX_SQL_DestroyPreparedQuery ( )

Free any resources attached to an existing prepared query.

Remarks: Resources are automatically freed when a new query is prepared, so calling this isn't necessary.

Definition at line 258 of file nwnx_sql.nss.

◆ NWNX_SQL_GetLastError()

string NWNX_SQL_GetLastError ( )

Returns: The last error message generated by the database.

Definition at line 265 of file nwnx_sql.nss.

◆ NWNX_SQL_GetPreparedQueryParamCount()

int NWNX_SQL_GetPreparedQueryParamCount ( )

Gets the number of parameteres expected by a prepared query.

Returns: Returns the number of parameters expected by the prepared query or -1 if no query is prepared.

Definition at line 273 of file nwnx_sql.nss.

◆ NWNX_SQL_PostgreSQL_SetNextQueryResultsBinaryMode()

void NWNX_SQL_PostgreSQL_SetNextQueryResultsBinaryMode ( )

Set the next query to return full binary results ON THE FIRST COLUMN ONLY.

Note: This is ONLY needed on PostgreSQL, and ONLY if you want to deserialize raw bytea in NWNX_SQL_ReadFullObjectInActiveRow with base64=FALSE.

Definition at line 281 of file nwnx_sql.nss.

Readme

Migration

Environment Variables

NWNX_SQL_TYPE

NWNX_SQL_HOST

NWNX_SQL_PORT

NWNX_SQL_USERNAME

NWNX_SQL_PASSWORD

NWNX_SQL_DATABASE

NWNX_SQL_QUERY_METRICS

NWNX_SQL_USE_UTF8

NWNX_SQL_CHARACTER_SET

Files

Functions

Function Documentation

◆ NWNX_SQL_PrepareQuery()

◆ NWNX_SQL_ExecutePreparedQuery()

◆ NWNX_SQL_ExecuteQuery()

◆ NWNX_SQL_ReadyToReadNextRow()

◆ NWNX_SQL_ReadNextRow()

◆ NWNX_SQL_ReadDataInActiveRow()

◆ NWNX_SQL_PreparedInt()

◆ NWNX_SQL_PreparedString()

◆ NWNX_SQL_PreparedFloat()

◆ NWNX_SQL_PreparedObjectId()

◆ NWNX_SQL_PreparedObjectFull()

◆ NWNX_SQL_PreparedNULL()

◆ NWNX_SQL_PreparedJson()

◆ NWNX_SQL_ReadFullObjectInActiveRow()

◆ NWNX_SQL_GetAffectedRows()

◆ NWNX_SQL_GetDatabaseType()

◆ NWNX_SQL_DestroyPreparedQuery()

◆ NWNX_SQL_GetLastError()

◆ NWNX_SQL_GetPreparedQueryParamCount()

◆ NWNX_SQL_PostgreSQL_SetNextQueryResultsBinaryMode()