Documentation

database_functions.php

database_functions.php

Functions required for interacting with the database.

Table of Contents

Functions

errorhandler()  : void
Centralised error handler. Display friendly error messages.
db_use_multiple_connection_modes()  : bool
Check if ResourceSpace has been configured to run with differnt users (read-write and/or read-only)
db_set_connection_mode()  : void
Used to force the database connection mode before running a particular SQL query
db_get_connection_mode()  : string
Return the current DB connection mode
db_clear_connection_mode()  : void
Clear the current DB connection mode that is in use to override the current SQL queries.
sql_connect()  : void
Connect to the database using the configured settings.
db_begin_transaction()  : bool
Indicate that from now on we want to group together DML statements into one transaction.
db_end_transaction()  : bool
Tell the database to commit the current transaction.
db_rollback_transaction()  : bool
Tell the database to rollback the current transaction.
ps_query()  : array<string|int, mixed>
Execute a prepared statement and return the results as an array.
copy_value()  : mixed
Copy value as value (flatten / no references)
ps_value()  : string
Return a single value from a database query, or the default if no rows
ps_array()  : array<string|int, mixed>
Like ps_value() but returns an array of all values found
sql_insert_id()  : int
Return the ID of the previously inserted row.
get_query_cache_location()  : string
Returns the location of the query cache files
clear_query_cache()  : bool
Clear all cached queries for cache group $cache
check_db_structs()  : void
Check the database structure conforms to that describe in the /dbstruct folder. Usually only happens after a SQL error after which the SQL is retried, thus the database is automatically upgraded.
CheckDBStruct()  : void
Check the database structure against the text files stored in $path.
sql_limit()  : string
Generate the LIMIT statement for a SQL query
sql_limit_with_total_count()  : array<string|int, mixed>
Utility function to obtain the total found rows while paginating the results.
sql_truncate_text_val()  : string
Query helper to ensure code honours the database schema constraints on text columns.
ps_param_insert()  : string
When constructing prepared statements and using e.g. ref in (some list of values), assists in outputting the correct number of parameters.
ps_param_fill()  : array<string|int, mixed>
When constructing prepared statements and using e.g. ref in (some list of values), assists in preparing the parameter array.
ps_fill_param_array()  : array<string|int, mixed>
Assists in generating parameter arrays where all of the parameters for a given section of sql are the same.
sql_reorder_records()  : void
Re-order rows in the table
columns_in()  : string|array<string|int, mixed>
Returns a comma separated list of table columns from the given table. Optionally, will use an alias instead of the table name to prefix the columns. For inclusion in SQL to replace "select *" which is not supported when using prepared statements.
db_chunk_id_list()  : array<int, array<int, int>>
Database helper to chunk a list of IDs
db_delete_table_records()  : bool
Delete database table records from a list of IDs

Functions

errorhandler()

Centralised error handler. Display friendly error messages.

errorhandler(int $errno, string $errstr, string $errfile, int $errline) : void
Parameters
$errno : int
$errstr : string
$errfile : string
$errline : int

db_use_multiple_connection_modes()

Check if ResourceSpace has been configured to run with differnt users (read-write and/or read-only)

db_use_multiple_connection_modes() : bool
Return values
bool

db_set_connection_mode()

Used to force the database connection mode before running a particular SQL query

db_set_connection_mode(string $name) : void

NOTE: this will generate a global variable that can be used to determine which mode is currently set.

IMPORTANT: It is the responsibility of each function to clear the current db mode once it finished running the query as the variable is not meant to persist between queries.

Parameters
$name : string

The name of the connection mode

db_get_connection_mode()

Return the current DB connection mode

db_get_connection_mode() : string
Return values
string

db_clear_connection_mode()

Clear the current DB connection mode that is in use to override the current SQL queries.

db_clear_connection_mode() : void

@see db_set_connection_mode() for more details.

sql_connect()

Connect to the database using the configured settings.

sql_connect() : void

db_begin_transaction()

Indicate that from now on we want to group together DML statements into one transaction.

db_begin_transaction(string $name) : bool
Parameters
$name : string

Savepoint name for the transaction.

Tags
used-by
deleteAnnotation()
updateAnnotation()
Return values
bool —

Returns TRUE on success or FALSE on failure.

db_end_transaction()

Tell the database to commit the current transaction.

db_end_transaction(string $name) : bool
Parameters
$name : string

Savepoint name for the transaction.

Tags
used-by
deleteAnnotation()
updateAnnotation()
Return values
bool —

Returns TRUE on success or FALSE on failure.

db_rollback_transaction()

Tell the database to rollback the current transaction.

db_rollback_transaction(string $name) : bool
Parameters
$name : string

Savepoint name for the transaction.

Return values
bool —

Returns TRUE on success or FALSE on failure.

ps_query()

Execute a prepared statement and return the results as an array.

ps_query(string $sql[, array<string|int, mixed> $parameters = array() ][, string $cache = "" ][, int $fetchrows = -1 ][, bool $dbstruct = true ][, int $logthis = 2 ][, bool $reconnect = true ][, mixed $fetch_specific_columns = false ]) : array<string|int, mixed>
Parameters
$sql : string

The SQL to execute

$parameters : array<string|int, mixed> = array()

An array of parameters used in the SQL in the order: type, value, type, value... and so on. Types are as follows: i - integer, d - double, s - string, b - BLOB. Example: array("s","This is the first SQL parameter and is a string","d",3.14)

$cache : string = ""

Disk based caching - cache the results on disk, if a cache group is specified. The group allows selected parts of the cache to be cleared by certain operations, for example clearing all cached site content whenever site text is edited.

$fetchrows : int = -1

set we don't have to loop through all the returned rows. We just fetch $fetchrows row but pad the array to the full result set size with empty values.

$dbstruct : bool = true

Set to false to prevent the dbstruct being checked on an error - only set by operations doing exactly that to prevent an infinite loop

$logthis : int = 2

No longer used

$reconnect : bool = true
$fetch_specific_columns : mixed = false
Tags
used-by
getAnnotations()
ps_value()
ps_array()
do_search()
get_activity_log()
extractFitsMetadata()
get_resource_type_field()
copy_locked_data()
copy_locked_fields()
copyRelatedResources()
HookFacesViewCustompanels()
api_faces_set_node()
faces_detect_missing()
Return values
array<string|int, mixed>

copy_value()

Copy value as value (flatten / no references)

copy_value(mixed $v) : mixed
Parameters
$v : mixed

ps_value()

Return a single value from a database query, or the default if no rows

ps_value(string $query, array<string|int, mixed> $parameters, mixed $default[, string $cache = "" ]) : string

NOTE: The value returned must have the column name aliased to 'value'

Parameters
$query : string

SQL query

$parameters : array<string|int, mixed>

SQL parameters with types, as for ps_query()

$default : mixed

Default value to return if no rows returned

$cache : string = ""

Cache category (optional)

Tags
uses
ps_query()
used-by
getResourceAnnotationsCount()
do_search()
initFacialRecognition()
get_resource_path()
Return values
string

ps_array()

Like ps_value() but returns an array of all values found

ps_array(string $query[, array<string|int, mixed> $parameters = array() ][, string $cache = "" ]) : array<string|int, mixed>

NOTE: The value returned must have the column name aliased to 'value'

Parameters
$query : string

SQL query

$parameters : array<string|int, mixed> = array()

SQL parameters with types, as for ps_query()

$cache : string = ""

Cache category (optional)

Tags
uses
ps_query()
Return values
array<string|int, mixed>

sql_insert_id()

Return the ID of the previously inserted row.

sql_insert_id() : int
Return values
int

get_query_cache_location()

Returns the location of the query cache files

get_query_cache_location() : string
Return values
string

clear_query_cache()

Clear all cached queries for cache group $cache

clear_query_cache(string $cache) : bool

If we've already done this on this page load, don't do it again as it will only add to the load in the case of batch operations.

Parameters
$cache : string
Return values
bool

check_db_structs()

Check the database structure conforms to that describe in the /dbstruct folder. Usually only happens after a SQL error after which the SQL is retried, thus the database is automatically upgraded.

check_db_structs([bool $verbose = false ]) : void

This function calls CheckDBStruct() for all plugin paths and the core project.

Parameters
$verbose : bool = false

CheckDBStruct()

Check the database structure against the text files stored in $path.

CheckDBStruct(string $path[, bool $verbose = false ]) : void

Add tables / columns / data / indices as necessary.

Parameters
$path : string
$verbose : bool = false

sql_limit()

Generate the LIMIT statement for a SQL query

sql_limit(int $offset, int $rows) : string
Parameters
$offset : int

Specifies the offset of the first row to return

$rows : int

Specifies the maximum number of rows to return

Return values
string

sql_limit_with_total_count()

Utility function to obtain the total found rows while paginating the results.

sql_limit_with_total_count(PreparedStatementQuery $query, null|int $rows, null|int $offset[, bool $cachecount = false ][, null|PreparedStatementQuery $countquery = null ]) : array<string|int, mixed>

IMPORTANT: the input query MUST have a deterministic order so it can help with performance and not have an undefined behaviour

Parameters
$query : PreparedStatementQuery

SQL query

$rows : null|int

Specifies the maximum number of rows to return. Usually set by a global configuration option (e.g $default_perpage, $default_perpage_list).

$offset : null|int

Specifies the offset of the first row to return. Use NULL to not offset.

$cachecount : bool = false

Use previously cached count if available?

$countquery : null|PreparedStatementQuery = null

Optional separate query to obtain count, usually without ORDER BY

Return values
array<string|int, mixed> —

Returns a:

  • total: int - count of total found records (before paging)
  • data: array - paged result set

sql_truncate_text_val()

Query helper to ensure code honours the database schema constraints on text columns.

sql_truncate_text_val(string $v, int $len) : string

IMPORTANT: please use where appropriate! In some cases, truncating may mean losing useful information (e.g contextual data), in which case changing the column type may be a better option.

Parameters
$v : string

String value that may require truncating

$len : int

Desired length (limit as imposed by the database schema). https://www.resourcespace.com/knowledge-base/developers/database_schema

Return values
string

ps_param_insert()

When constructing prepared statements and using e.g. ref in (some list of values), assists in outputting the correct number of parameters.

ps_param_insert(int $count) : string
Parameters
$count : int

How many parameters to insert, e.g. 3 returns "?,?,?"

Return values
string

ps_param_fill()

When constructing prepared statements and using e.g. ref in (some list of values), assists in preparing the parameter array.

ps_param_fill(array<string|int, mixed> $array, string $type) : array<string|int, mixed>
Parameters
$array : array<string|int, mixed>

The input array, to prepare for output. Will return this array but with type entry inserted before each value.

$type : string

The column type as per ps_query

Return values
array<string|int, mixed>

ps_fill_param_array()

Assists in generating parameter arrays where all of the parameters for a given section of sql are the same.

ps_fill_param_array(string $string, string $value, string $type) : array<string|int, mixed>
Parameters
$string : string

A portion of sql that contains one or more placeholders

$value : string

The value that should be used to generate the array of parameters

$type : string

The column type of $value as per ps_query

Return values
array<string|int, mixed>

sql_reorder_records()

Re-order rows in the table

sql_reorder_records(string $table, array<string|int, mixed> $refs) : void
Parameters
$table : string

Table name. MUST have an "order_by" column.

$refs : array<string|int, mixed>

List of record IDs in the new desired order

columns_in()

Returns a comma separated list of table columns from the given table. Optionally, will use an alias instead of the table name to prefix the columns. For inclusion in SQL to replace "select *" which is not supported when using prepared statements.

columns_in(string $table[, string $alias = null ][, string $plugin = null ][, bool $return_list = false ]) : string|array<string|int, mixed>
Parameters
$table : string

The source table

$alias : string = null

Optionally, a different alias to use

$plugin : string = null

[DEPRECATED] Specifies that this table is defined in a plugin with the supplied name

$return_list : bool = false

Set to true to return a list of column names. Note: the alias is ignored in this mode.

Return values
string|array<string|int, mixed>

db_chunk_id_list()

Database helper to chunk a list of IDs

db_chunk_id_list(array<int, int> $refs) : array<int, array<int, int>>
Parameters
$refs : array<int, int>
Return values
array<int, array<int, int>>

db_delete_table_records()

Delete database table records from a list of IDs

db_delete_table_records(string $table, array<int, int> $refs, callable $logger) : bool 
return db_delete_table_records(
    'brand_guidelines_content',
    $refs,
    fn($ref) => log_activity(null, LOG_CODE_DELETED, null, 'brand_guidelines_content', 'content', $ref)
);

Example how to not log it:

return db_delete_table_records('brand_guidelines_content', $refs, fn() => null);
Parameters
$table : string

Database table name

$refs : array<int, int>

List of database IDs

$logger : callable
Return values
bool —

True if it executed the query, false otherwise

On this page

Search results