Documentation

Data source: SQL

Table of Contents ▼

Presentation

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 temma.json file (see the configuration documentation).

The connection is then available in the controller by writing: 

$this->db

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

$loader->dataSources['db']

Configuration

In the temma.json 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

Unified 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;

Array-type access

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

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

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

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

// elements count
$nbr = count($this->db);

General methods

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

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

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

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

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

Management of complex serialized data

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

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

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

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

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

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

Raw data management

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

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

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

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

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

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

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

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

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

Specific calls

Basic methods

exec
exec(string $sql) : void

The exec() method is used to execute queries for which no data is expected in return.
Examples: 

// delete an article from its identifier
$this->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";
$this->db->exec($sql);
queryOne
queryOne(string $sql, [string $valueField]) : array

The queryOne() method is used to execute queries for which we want to retrieve a row of data. The information is returned in the form of an associative array, whose keys correspond to the names of the fields in the table.

For example, the following code:

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

Will give a result like this:

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 can take the name of one of the fields. In this case, the method will directly return the value of this field.

Example:

$sql = "SELECT COUNT(*) AS nbr FROM article";
$nbr = $this->db->queryOne($sql, 'nbr');
queryAll
queryAll(string $sql) : array

The queryAll() method is used to execute queries that return multiple rows of data. The information is retrieved in the form of a list each element of which is an associative array representing a row of data, the keys of which correspond to the names of the fields in the table.

The first parameter contains the SQL query to execute.

For example, the following code:

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

Will give a result like this:

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

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

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

The second parameter contains the name of the field to use to index the items in the list.

For example, the following code:

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

Will give a result like this:

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

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

    [16] => Array
        (
            [id] => 16
            [status] => waiting
            [title] => Third article
            [creationDate] => 2011-04-10 11:03:44
            [content] => Some text...
            [authorId] => 2
        )
)
lastInsertId
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()";
$this->db->exec($sql);

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

Escaping special characters

When writing SQL queries, be aware of the risk of SQL injection. To deal with it, you have to escape the special characters.

To do this, the \Temma\Datasources\Sql object offers the quote() and quoteNull() methods.

These two methods take a text as a parameter, and return it after escaping each of its special characters. They add apostrophes at the beginning and at the end of the escaped text.

The quoteNull() method returns the string NULL (without apostrophes) if it has been given an empty string, a number equal to zero, or an empty array.

Examples: 

$str = "You're the one";
$sql = "INSERT INTO article SET text = " . $db->quote($str);
// INSERT INTO ARTICLE SET text = 'You\'re the one'

$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

Prepared 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']);

Transactions 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
$this->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
$this->db->startTransaction();

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

// conditional processing
if ($something) {
    // the transaction is accepted
    $db->commit();
} else {
    // the transaction is canceled
    $db->rollback();
}
Previous: Data sources
Next: Memcache

Table of Contents

  1. Migration : How to upgrade from Temma 1.x to version 2
  2. Installation : Download Temma and install it to start your web project
  3. Configuration : All the configuration directives of the etc/temma.json file and the optional environment variables
  4. External libraries : How to use external function libraries
  5. Routing : Temma's default routing system, and advanced routing
  6. Log : Use of the log system, management by criticality levels
  7. Controllers : Essential parts of your web application
  8. Views : Smarty templates or JSON/CSV/RSS/iCal/INI exports
  9. Dependency injection : The backbone of your application developments
  10. Sessions : Extension of user sessions managed by PHP
  11. Data sources : Unified data access management
    1. SQL : Access to relational databases
    2. Memcache : Access to Memcached servers
    3. Redis : Access to Redis servers
    4. File : Access to local file storage
    5. S3 : Access to Amazon S3 storage
    6. Socket : Network communication
    7. SQS : Access to Amazon SQS message queues
    8. Beanstalk : Access to Beanstalkd message queue servers
    9. Smsmode : To send text messages to mobile phones
    10. Slack : To send notifications on Slack
    11. Pushover : To send push notifications to cell phones
  12. Model : How to use DAOs to access databases
  13. Execution flow : How to manage the execution flow, between plugins and the controller
  14. Plugins : How to use plugins, and create your own plugins to modularize your code
  15. Attributes : How to filter access to controllers and actions
  16. Tests : To write automated integration tests.
  17. Command-line interface : To create executable scripts on the command line, automatically initialized by Temma
  18. Helpers : Items offered by Temma to help you in various circumstances