Database (XF->db)

The database can be accessed by using the simple XELOS singleton XF->db. As the overall performance was important during the development XELOS is only compatible with mySQL Databases and their respective forks (Percona, MariaDB) as we use highly optimized SQL code in many scenarios which make intensive use of mySQL specific commands.

Configuration

The database can be configured in the config.custom.php by using the following options:

define('XF_DB_SERVER','mysql.hostname');
define('XF_DB_DATABASE','MY_XELOS_DATABASE_NAME');
define('XF_DB_USER','USER');
define('XF_DB_PASS','PASSWORD');

Queries

Whereas the regular way of accessing your modules data is by using the XELOS models as they provide a convenient wrapper around the data access tier. --> See Models

In some cases it might be necessary to conduct a manual query (e.g. for improved performance or if you need to use mySQL specific functions). In this case you can always use the $XF->db wrapper:

$result = $XF->db->query("SELECT * FROM myTable", 'fetch_assoc', __FILE__, __LINE__);

This executes the passed query and returns an assoc array with all rows and columns for further processing. If no result has been found the function returns FALSE.

It is recommended to pass FILE and LINE (3rd + 4th param) as this allows you to see the exact source of an SQL error in the XELOS error log.

Table Names

As XELOS manages its own table namespaces (every module can be instantiated multiple times) it is necessary to use placeholder instead of fixed table names. XELOS will replace these during the query with the correct table names.

For system tables the placeholders have the format ###sys|user### which will be translated to system_user or ###tbl|contact### to reference your module's table contact.

Important: To be able to use a module's specific table name placeholders you must use $this->mod->dbquery() which knows the context of your current's module and uses the correct table namespaces.

Passing Values / Escaping

As the SQL syntax is a potential risk for attacks it is important that all values which are passed from the frontend / user to a query must be escaped and therefore prevent SQL Injections. While this is being done automatically if you use XELOS' models you have to make sure to always escape values if you place them in a manual query. This should be done using the $XF->db->query_with_params() helper:

$query = "SELECT * FROM table1 WHERE name = :search_by_name OR id IN (:id_list)";
$params = [
   search_by_name => 'MasterOfDesaster',
   id_list => [1,2,3,4,5]
]
$return = $XF->db->query_with_params($query, $params);

This helper automatically replaces all name placeholders (starting with ':') with the value from the associative params array. All values are automatically escaped. For IN queries you can also pass an array as in the example which will be translated to the corresponding SQL syntax and each value will be escaped individually as well.

Caching

The result of each query can be cached if necessary. You should consider caching for queries in case they require some time to be calculated and if the result is not expected to change frequently. To enable caching you can simply pass a "TTL - Time to Live in seconds" as additional parameter. XELOS will store the result for this exact query in the system cache until the TTL expires. In this timeframe all subsequent DB query calls with the same SQL will be pulled from the Cache (even if the table's data has been changed in the meantime - you need to invalidate the cache manually!).

$result = $XF->db->query("SELECT * FROM myTable", 'fetch_assoc', __FILE__, __LINE__, $ttl = 3600);

Transactions

@TODO - Note about compatibility with Percona Cluster

Scaling and Redundancy

If you need a redundant and scalable setup we recommend using Percona XTRA DB Cluster. Using a traditional mySQL Replication schema is not recommended nor supported.