Data source: SQL

1Presentation

If you have correctly configured the database connection parameters, Temma automatically creates an object of type \Temma\Datasources\Sql. By convention, we will assume that you have named this connection db in the etc/temma.php file (see the configuration documentation).

The connection is then available in the controller by writing:

$db = $this->db;

In the other objects managed by the dependency injection component, the connection to the database is accessible by writing:

$db = $loader->dataSources->db;
$db = $loader->dataSources['db'];

2Configuration

In the etc/temma.php file (see the configuration documentation), you declare the DSN (Data Source Name) used to connect to the database.
The way the DSN is written depends on the type of database involved. For classic database servers, it is written: TYPE://LOGIN:PASSWORD@SERVER[:PORT]/BASE

This applies to the following database types: mysql, mysqli, pgsql, cubrid, sybase, mssql, dblib, firebird, ibm, informix, sqlsrv, oci, odbc, 4D.

For sqlite and sqlite2 databases, the DSN is: sqlite:/chemin/vers/fichier.sq3

3Unified calls

SQL databases can be used in the same way as other data sources, to store key-value pairs, either raw (string storage) or by serializing complex data.

To do this, the database connection must allow access to a table named TemmaData, defined as follows:

CREATE TABLE TemmaData (
    key    CHAR(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
    data   LONGTEXT,
    PRIMARY KEY (key)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

3.1Array-type access

// verification of data existence
if (isset($db['key1']))
    doSomething();

// read data (deserialized)
$data = $db['key1'];

// data writing (serialized)
$db['key1'] = $value;

// data deletion
unset($db['key1']);

// elements count
$cnt = count($db);

3.2General methods

// verification of data existence
if ($db->isSet('user:1'))
    doSomething();

// data deletion
$db->remove('user:1');

// multiple data deletion
$db->mRemove(['user:1', 'user:2', 'user:3']);

// prefix-based data deletion
$db->clear('user:');

// delete all data
$db->flush();

3.3Management of complex serialized data

// search for keys matching a prefix
$users = $db->search('user:');

// prefix-based key search, with data recovery (deserialized)
$users = $db->search('user:', true);

// data reading (deserialized)
$user = $db->get('user:1');
// reading data with default value
$color = $db->get('color', 'blue');
// read data with data creation if necessary
$user = $db->get("user:$userId", function() use ($userId) {
    return $this->dao->get($userId);
});

// read multiple data (deserialized)
$users = $db->mGet(['user:1', 'user:2', 'user:3']);

// data writing (serialized)
$db->set('user:1', $userData);

// multiple data writing (serialized)
$db->mSet([
    'user:1' => $user1data,
    'user:2' => $user2data,
    'user:3' => $user3data,
]);

3.4Raw data management

// search for keys matching a prefix
$colors = $db->find('color:');

// prefix-based key search, with (raw) data retrieval
$colors = $db->find('color:', true);

// read data (raw)
$html = $db->read('page:home');
// read data with default value
$html = $db->read('page:home',
                        '<html><body><h1>Homepage</h1><body><html>');
// read data with data creation if necessary
$html = $db->read('page:home', function() {
    return file_get_contents('/path/to/homepage.html');
});

// read multiple (raw) data
$pages = $db->mRead(['page:home', 'page:admin', 'page:products']);

// copy data to a local file
$db->copyFrom('page:home', '/path/to/newpage.html');
// copy data to local file, with default value
$db->copyFrom('page:home', '/path/to/newpage.html', $defaultHtml);
// copy data to a local file, with data creation if necessary
$db->copyFrom('page:home', '/path/to/newpage.html', function() {
    return file_get_contents('/path/to/oldpage.html');
});

// data writing (raw)
$db->write('color:blue', '#0000ff');

// write multiple (raw) data
$db->mWrite({
    'color:blue'  => '#0000ff',
    'color:red'   => '#ff0000',
    'color:green' => '#00ff00',
]);

// write (raw) data from a local file
$db->copyTo('page:home', '/path/to/homepage.html');

// write multiple (raw) data from local files
$db->mCopyTo([
    'page:home'     => '/path/to/homepage.html',
    'page:admin'    => '/path/to/admin.html',
    'page:products' => '/path/to/products.html',
]);

4Specific calls

4.1exec()

exec(string $sql, [bool $buffered=false], [?array $parameters=null]) : void

The exec() method is used to execute queries for which no result data is expected.

Parameters:

The SQL query to execute.
(optional) true to buffer the query. A buffered query is queued until a non-buffered query is executed; at that time, all pending buffered queries are executed first.
(optional) An array containing the query parameters.

If parameters are provided, the query is not buffered.

Examples:

// delete an article by its identifier
$db->exec("DELETE FROM article WHERE id = '12'");

// update the date of the last identification
// of all the users for whom this date is not defined
$sql = "UPDATE user
        SET lastLogin = NOW()
        WHERE lastLogin IS NULL";
$db->exec($sql);

Example using parameters:

// delete an article by its identifier
$db->exec(
    "DELETE FROM article WHERE id = :id",
    parameters: ['id' => 12],
);

// insert a new user
$db->exec(
    "INSERT INTO user
     SET email = :email,
         name = :name,
         admin = :isAdmin",
    parameters: [
        'email' => 'vador@darkside.com',
        'name'  => 'Darth Vader',
        'admin' => true,
    ]
);

4.2queryOne()

queryOne(string $sql, [?string $valueField=null], [?array $parameters=null]) : array

The queryOne() method executes a query and returns a single row of data. The result is returned as an associative array whose keys correspond to the column names.

Parameters:

The SQL query to execute.
(optional) The name of the field to return. By default, the full associative array is returned.
(optional) An array containing the query parameters.

If buffered queries are pending, they are executed first.

Basic usage example:

$sql = "SELECT * FROM article WHERE id = '11'";
$data = $db->queryOne($sql);
print_r($data);

Which produces a result similar to:

Array
(
    [id] => 11
    [status] => valid
    [title] => Title of the article
    [creationDate] => 2021-04-04 23:17:39
    [content] => Text of the article...
    [authorId] => 2
)

The second parameter may contain a field name. In that case, the method directly returns the value of that field.

Example:

$sql = "SELECT COUNT(*) AS cnt FROM article";
$cnt = $db->queryOne($sql, 'cnt');
print($cnt); // e.g. prints 1234

The third parameter allows passing parameters that will be safely escaped.

Example:

$sql = "SELECT * FROM article
        WHERE date_creation > :dateFrom
          AND status = :status
        ORDER BY id DESC
        LIMIT 1";
$article = $db->queryOne(
    $sql,
    parameters: [
        'dateFrom' => '1990-01-01',
        'status'   => 'valid',
    ]
);

Another example using all three parameters:

$sql = "SELECT title FROM article WHERE id = :id";
$title = $db->queryOne(
    $sql,
    'title',
    ['id' => 123]
);

4.3queryAll()

queryAll(string $sql, [string $key], [string $valueField], [?array $parameters=null]) : array

The queryAll() method executes queries that return multiple rows of data. The results are returned as a list, where each element is an associative array representing a row, with keys corresponding to the column names.

Parameters:

The SQL query to execute.
(optional) The name of the field used to index the rows.
(optional) The name of the field to return as the value for each row.
(optional) An array containing the query parameters.

If buffered queries are pending, they are executed first.

The first parameter contains the SQL query to execute.

For example, the following code:

$sql = "SELECT * FROM article WHERE authorId = '2'";
$data = $db->queryAll($sql);
print_r($data);

Will produce a result similar to:

Array
(
    [0] => Array
        (
            [id] => 11
            [status] => valid
            [title] => Article title
            [creationDate] => 2011-04-04 23:17:39
            [content] => Article text ...
            [authorId] => 2
        )

    [1] => Array
        (
            [id] => 12
            [status] => valid
            [title] => Another title
            [creationDate] => 2011-04-05 20:34:20
            [content] => Different text ...
            [authorId] => 2
        )

    [2] => Array
        (
            [id] => 16
            [status] => waiting
            [title] => Third article
            [creationDate] => 2011-04-10 11:03:44
            [content] => Another text ...
            [authorId] => 2
        )
)

The second parameter ($key) contains the name of the field used to index the result list.

For example, the following code:

$sql = "SELECT * FROM article WHERE authorId = '2'";
$data = $db->queryAll($sql, 'id');
print_r($data);

Will produce a result similar to:

Array
(
    [11] => Array
        (
            [id] => 11
            [status] => valid
            [title] => Article title
            [creationDate] => 2011-04-04 23:17:39
            [content] => Article text ...
            [authorId] => 2
        )

    [12] => Array
        (
            [id] => 12
            [status] => valid
            [title] => Another title
            [creationDate] => 2011-04-05 20:34:20
            [content] => Different text ...
            [authorId] => 2
        )

    [16] => Array
        (
            [id] => 16
            [status] => waiting
            [title] => Third article
            [creationDate] => 2011-04-10 11:03:44
            [content] => Another text ...
            [authorId] => 2
        )
)

The third parameter ($valueField) may contain the name of a field that will be used to return a single value per row.

For example, the following code:

$sql = "SELECT * FROM article WHERE authorId = '2'";

$data = $db->queryAll($sql, null, 'title');
print_r($data);

$data = $db->queryAll($sql, 'id', 'title');
print_r($data);

Will produce a result similar to:

Array
(
    [0] => Article title
    [1] => Another title
    [2] => Third article
)
Array
(
    [11] => Article title
    [12] => Another title
    [16] => Third article
)

The fourth parameter (parameters) contains a list of parameters that will be provided to the query and safely escaped:

$sql = "SELECT * FROM article WHERE status = :status";
$articles = $db->queryAll(
    $sql,
    parameters: ['status' => 'valid']
);

Example using all four parameters:

$titles = $db->queryAll(
    "SELECT * FROM article WHERE status = :status",
    'id',
    'title',
    ['status' => 'valid']
);

This may produce a result such as:

Array
(
    [11] => Article title
    [12] => Another title
    [16] => Third article
)

4.4lastInsertId()

lastInsertId(void) : int|string

The lastInsertId() method returns the primary key of the last record created on the current connection to the database.

Example of use:

// add an article to the database
$sql = "INSERT INTO article
        SET title = 'Titre', content = 'Texte', creationDate = NOW()";
$db->exec($sql);

// get the identifier of the new article
$id = $db->lastInsertId();

4.5Escaping special characters

When writing SQL queries, special care must be taken to avoid SQL injection vulnerabilities. To prevent this, special characters must be escaped, either by using the parameters argument (in the exec(), queryOne() and queryAll() methods), or by performing explicit escaping.

For this purpose, the \Temma\Datasources\Sql object provides the quote() and quoteNull() methods.

Both methods take a string as parameter and return it after escaping all special characters. They also add single quotes at the beginning and at the end of the escaped string.

The quoteNull() method returns the string NULL (without quotes) if it is given an empty string, a value equal to zero, or an empty array.

Examples:

$str = "Article title";
$sql = "INSERT INTO article SET text = " . $db->quote($str);
// INSERT INTO article SET text = 'Article title'

$str = '';
$sql = "INSERT INTO article SET text = " . $db->quote($str);
// INSERT INTO article SET text = ''

$str = '';
$sql = "INSERT INTO article SET text = " . $db->quoteNull($str);
// INSERT INTO article SET text = NULL

5Prepared requests

The \Temma\Datasources\Sql object allows you to play queries prepared in advance, modifying only the parameters used at each execution.

The parameters can be provided in the form of a list, the elements of which replace the question marks placed in the query:

// prepare the request
$sql = "DELETE FROM article WHERE status = ? AND title != ?";
$statement = $db->prepare($sql);
// first execution
$statement->exec(['invalid', 'This one is good']);
// second execution
$statement->exec(['waiting', 'This one too']);

The parameters can be named in the query, and provided as an associative array:

// prepare the request
$sql = "DELETE FROM article WHERE status = :status AND title != :title";
$statement = $db->prepare($sql);
// first execution
$statement->exec([
    ':status' => 'invalid',
    ':title' => 'This one is good',
]);
// second execution
$statement->exec([
    ':status' => 'waiting',
    ':title' => 'This one too',
]);

In both cases, the parameters are automatically escaped (see methods quote() and quoteNull()).

It is also possible to use prepared queries to retrieve data. The queryOne() and queryAll() methods are used to retrieve a row of data and all the rows of data, respectively. The first returns an associative array whose keys are the column names; the second returns a list of associative arrays.
As with the exec() method, these methods can take a list of parameters, or an associative array of parameters.

// prepare the request
$sql = "SELECT * FROM article WHERE id = ?";
$statement = $db->prepare($sql);
// first read
$article1 = $statement->queryOne([12]);
// second read
$article2 = $statement->queryOne([23]);

// prepare the request
$sql = "SELECT * FROM article WHERE status = :status";
$statement = $db->prepare($sql);
// read
$articles = $statement->queryAll([':status' => 'invalid']);

6Transactions management

The \Temma\Datasources\Sql object provides two different ways to manage transactions.

Through an anonymous function, passed as a parameter to the transaction() method. If an exception is thrown in the anonymous function, an automatic rollback is performed; otherwise a commit is performed automatically at the end of the execution of the anonymous function.

// we create the transaction
// the anonymous function receives the connection
// to the database as a parameter
$db->transaction(function($db) use ($userId) {
    // we perform various requests
    $db->exec(...);
    $db->exec(...);
    $db->exec(...);
});

Or explicitly, using the startTransaction(), commit() and rollback() methods:

// we open a transaction
$db->startTransaction();

// we perform various requests
$db->exec(...);
$db->exec(...);
$db->exec(...);

// conditional processing
if ($something) {
    // the transaction is accepted
    $db->commit();
} else {
    // the transaction is canceled
    $db->rollback();
}