Fuente de datos: SQL
1Presentación
Si has configurado correctamente los parámetros de conexión a la base de datos, Temma crea automáticamente un objeto de tipo \Temma\Datasources\Sql. Por convención, supondremos que has nombrado esta conexión db en el archivo etc/temma.php (consulta la documentación de configuración).
La conexión está entonces disponible en el controlador de la siguiente forma:
$db = $this->db;En los demás objetos gestionados por el componente de inyección de dependencias, la conexión a la base de datos es accesible de la siguiente forma:
$db = $loader->dataSources->db;
$db = $loader->dataSources['db'];2Configuración
En el archivo etc/temma.php (consulta la documentación de configuración),
declaras el DSN (Data Source Name) usado para conectarte a la base de datos.
La forma de escribir el DSN depende del tipo de base de datos utilizada. Para los servidores de bases de
datos clásicos, se escribe: TYPE://LOGIN:PASSWORD@SERVER[:PORT]/BASE
Esto se aplica a los siguientes tipos de base de datos: mysql, mysqli, pgsql, cubrid, sybase, mssql, dblib, firebird, ibm, informix, sqlsrv, oci, odbc, 4D.
Para las bases de datos sqlite y sqlite2, el DSN es: sqlite:/ruta/al/archivo.sq3
3Llamadas unificadas
Las bases de datos SQL pueden usarse de la misma forma que otras fuentes de datos, para almacenar pares clave-valor, ya sea en bruto (almacenamiento de cadenas) o serializando datos complejos.
Para ello, la conexión a la base de datos debe permitir el acceso a una tabla llamada TemmaData, definida de la siguiente forma:
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.1Acceso tipo array
// verificación de la existencia del dato
if (isset($db['key1']))
doSomething();
// lectura del dato (deserializado)
$data = $db['key1'];
// escritura del dato (serializado)
$db['key1'] = $value;
// eliminación del dato
unset($db['key1']);
// conteo de elementos
$cnt = count($db);
// conteo de elementos que coinciden con un prefijo
$cnt = $db->count('user:');
3.2Métodos generales
// verificación de la existencia del dato
if ($db->isSet('user:1'))
doSomething();
// eliminación del dato
$db->remove('user:1');
// eliminación de varios datos
$db->mRemove(['user:1', 'user:2', 'user:3']);
// eliminación de datos por prefijo
$db->clear('user:');
// elimina todos los datos
$db->flush();
3.3Gestión de datos serializados complejos
// búsqueda de claves que coinciden con un prefijo
$users = $db->search('user:');
// búsqueda de claves por prefijo, con recuperación de datos (deserializados)
$users = $db->search('user:', true);
// búsqueda paginada (10 resultados a partir del 20º)
$users = $db->search('user:', true, 20, 10);
// lectura del dato (deserializado)
$user = $db->get('user:1');
// lectura del dato con valor por defecto
$color = $db->get('color', 'blue');
// lectura del dato con creación del dato si es necesario
$user = $db->get("user:$userId", function() use ($userId) {
return $this->dao->get($userId);
});
// lectura de varios datos (deserializados)
$users = $db->mGet(['user:1', 'user:2', 'user:3']);
// escritura del dato (serializado)
$db->set('user:1', $userData);
// escritura de varios datos (serializados)
$db->mSet([
'user:1' => $user1data,
'user:2' => $user2data,
'user:3' => $user3data,
]);
3.4Gestión de datos en bruto
// búsqueda de claves que coinciden con un prefijo
$colors = $db->find('color:');
// búsqueda de claves por prefijo, con recuperación de datos (en bruto)
$colors = $db->find('color:', true);
// búsqueda paginada (10 resultados a partir del 20º)
$colors = $db->find('color:', true, 20, 10);
// lectura del dato (en bruto)
$html = $db->read('page:home');
// lectura del dato con valor por defecto
$html = $db->read('page:home',
'<html><body><h1>Homepage</h1><body><html>');
// lectura del dato con creación del dato si es necesario
$html = $db->read('page:home', function() {
return file_get_contents('/path/to/homepage.html');
});
// lectura de varios datos (en bruto)
$pages = $db->mRead(['page:home', 'page:admin', 'page:products']);
// copia el dato a un archivo local
$db->copyFrom('page:home', '/path/to/newpage.html');
// copia el dato a un archivo local, con valor por defecto
$db->copyFrom('page:home', '/path/to/newpage.html', $defaultHtml);
// copia el dato a un archivo local, con creación del dato si es necesario
$db->copyFrom('page:home', '/path/to/newpage.html', function() {
return file_get_contents('/path/to/oldpage.html');
});
// escritura del dato (en bruto)
$db->write('color:blue', '#0000ff');
// escritura de varios datos (en bruto)
$db->mWrite([
'color:blue' => '#0000ff',
'color:red' => '#ff0000',
'color:green' => '#00ff00',
]);
// escritura de datos (en bruto) desde un archivo local
$db->copyTo('page:home', '/path/to/homepage.html');
// escritura de varios datos (en bruto) desde archivos locales
$db->mCopyTo([
'page:home' => '/path/to/homepage.html',
'page:admin' => '/path/to/admin.html',
'page:products' => '/path/to/products.html',
]);
4Llamadas específicas
4.1exec()
exec(string $sql, [bool $buffered=false], [?array $parameters=null]) : intEl método exec() se usa para ejecutar consultas para las que no se espera ningún dato de resultado.
Parámetros:
- La consulta SQL que se va a ejecutar.
- (opcional) true para poner la consulta en búfer. Una consulta en búfer queda en espera hasta que se ejecuta una consulta sin búfer; en ese momento, todas las consultas en búfer pendientes se ejecutan primero.
- (opcional) Un array que contiene los parámetros de la consulta.
Si se proporcionan parámetros, la consulta no se pone en búfer.
Ejemplos:
// elimina un artículo por su identificador
$db->exec("DELETE FROM article WHERE id = '12'");
// actualiza la fecha del último inicio de sesión
// de todos los usuarios para los que esta fecha no está definida
$sql = "UPDATE user
SET lastLogin = NOW()
WHERE lastLogin IS NULL";
$db->exec($sql);
Ejemplo usando parámetros:
// elimina un artículo por su identificador
$db->exec(
"DELETE FROM article WHERE id = :id",
parameters: ['id' => 12],
);
// inserta un nuevo usuario
$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]) : mixedEl método queryOne() ejecuta una consulta y devuelve una única fila de datos. El resultado se devuelve como un array asociativo cuyas claves corresponden a los nombres de las columnas.
Parámetros:
- La consulta SQL que se va a ejecutar.
- (opcional) El nombre del campo que se va a devolver. Por defecto, se devuelve el array asociativo completo.
- (opcional) Un array que contiene los parámetros de la consulta.
Si hay consultas en búfer pendientes, se ejecutan primero.
Ejemplo de uso básico:
$sql = "SELECT * FROM article WHERE id = '11'";
$data = $db->queryOne($sql);
print_r($data);
Lo cual produce un resultado similar a:
Array
(
[id] => 11
[status] => valid
[title] => Título del artículo
[creationDate] => 2021-04-04 23:17:39
[content] => Texto del artículo...
[authorId] => 2
)
El segundo parámetro puede contener un nombre de campo. En ese caso, el método devuelve directamente el valor de ese campo.
Ejemplo:
$sql = "SELECT COUNT(*) AS cnt FROM article";
$cnt = $db->queryOne($sql, 'cnt');
print($cnt); // por ejemplo, imprime 1234
El tercer parámetro permite pasar parámetros que se escaparán de forma segura.
Ejemplo:
$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',
]
);
Otro ejemplo usando los tres parámetros:
$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]) : arrayEl método queryAll() ejecuta consultas que devuelven varias filas de datos. Los resultados se devuelven como una lista, donde cada elemento es un array asociativo que representa una fila, con claves correspondientes a los nombres de las columnas.
Parámetros:
- La consulta SQL que se va a ejecutar.
- (opcional) El nombre del campo usado para indexar las filas.
- (opcional) El nombre del campo que se devolverá como valor de cada fila.
- (opcional) Un array que contiene los parámetros de la consulta.
Si hay consultas en búfer pendientes, se ejecutan primero.
El primer parámetro contiene la consulta SQL que se va a ejecutar.
Por ejemplo, el siguiente código:
$sql = "SELECT * FROM article WHERE authorId = '2'";
$data = $db->queryAll($sql);
print_r($data);
Producirá un resultado similar a:
Array
(
[0] => Array
(
[id] => 11
[status] => valid
[title] => Título del artículo
[creationDate] => 2011-04-04 23:17:39
[content] => Texto del artículo ...
[authorId] => 2
)
[1] => Array
(
[id] => 12
[status] => valid
[title] => Otro título
[creationDate] => 2011-04-05 20:34:20
[content] => Texto diferente ...
[authorId] => 2
)
[2] => Array
(
[id] => 16
[status] => waiting
[title] => Tercer artículo
[creationDate] => 2011-04-10 11:03:44
[content] => Otro texto ...
[authorId] => 2
)
)
El segundo parámetro ($key) contiene el nombre del campo usado para indexar la lista de resultados.
Por ejemplo, el siguiente código:
$sql = "SELECT * FROM article WHERE authorId = '2'";
$data = $db->queryAll($sql, 'id');
print_r($data);
Producirá un resultado similar a:
Array
(
[11] => Array
(
[id] => 11
[status] => valid
[title] => Título del artículo
[creationDate] => 2011-04-04 23:17:39
[content] => Texto del artículo ...
[authorId] => 2
)
[12] => Array
(
[id] => 12
[status] => valid
[title] => Otro título
[creationDate] => 2011-04-05 20:34:20
[content] => Texto diferente ...
[authorId] => 2
)
[16] => Array
(
[id] => 16
[status] => waiting
[title] => Tercer artículo
[creationDate] => 2011-04-10 11:03:44
[content] => Otro texto ...
[authorId] => 2
)
)
El tercer parámetro ($valueField) puede contener el nombre de un campo que se usará para devolver un único valor por fila.
Por ejemplo, el siguiente código:
$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);
Producirá un resultado similar a:
Array
(
[0] => Título del artículo
[1] => Otro título
[2] => Tercer artículo
)
Array
(
[11] => Título del artículo
[12] => Otro título
[16] => Tercer artículo
)
El cuarto parámetro (parameters) contiene una lista de parámetros que se proporcionarán a la consulta y se escaparán de forma segura:
$sql = "SELECT * FROM article WHERE status = :status";
$articles = $db->queryAll(
$sql,
parameters: ['status' => 'valid']
);
Ejemplo usando los cuatro parámetros:
$titles = $db->queryAll(
"SELECT * FROM article WHERE status = :status",
'id',
'title',
['status' => 'valid']
);
Esto puede producir un resultado como:
Array
(
[11] => Título del artículo
[12] => Otro título
[16] => Tercer artículo
)
4.4lastInsertId()
lastInsertId(void) : int|stringEl método lastInsertId() devuelve la clave primaria del último registro creado en la conexión actual a la base de datos.
Ejemplo de uso:
// añade un artículo a la base de datos
$sql = "INSERT INTO article
SET title = 'Título', content = 'Texto', creationDate = NOW()";
$db->exec($sql);
// obtiene el identificador del nuevo artículo
$id = $db->lastInsertId();
4.5Escape de caracteres especiales
Al escribir consultas SQL, hay que tener especial cuidado para evitar vulnerabilidades de inyección SQL. Para evitarlo, los caracteres especiales deben escaparse, ya sea usando el argumento parameters (en los métodos exec(), queryOne() y queryAll()), o realizando un escape explícito.
Para ello, el objeto \Temma\Datasources\Sql proporciona los métodos quote() y quoteNull().
Ambos métodos reciben una cadena como parámetro y la devuelven después de escapar todos los caracteres especiales. También añaden comillas simples al principio y al final de la cadena escapada.
El método quoteNull() devuelve la cadena NULL (sin comillas) si recibe una cadena vacía, un valor igual a cero, o un array vacío.
Ejemplos:
$str = "Título del artículo";
$sql = "INSERT INTO article SET text = " . $db->quote($str);
// INSERT INTO article SET text = 'Título del artículo'
$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
4.6Consultas en búfer
Como se explicó anteriormente, el método exec() puede poner consultas en búfer, manteniéndolas en espera hasta que se ejecuta una consulta sin búfer (en cuyo caso, todas las consultas pendientes se ejecutan primero).
Es posible forzar la ejecución de todas las consultas pendientes llamando al método execBufferedRequests().
Ejemplo:
// ejecución de una consulta en búfer
$db->exec("DELETE FROM article");
// ...
// ejecución de las consultas pendientes
$db->execBufferedRequests();
5Consultas preparadas
El objeto \Temma\Datasources\Sql permite ejecutar consultas preparadas de antemano, modificando solo los parámetros usados en cada ejecución.
Los parámetros pueden proporcionarse en forma de lista, cuyos elementos reemplazan los signos de interrogación colocados en la consulta:
// prepara la consulta
$sql = "DELETE FROM article WHERE status = ? AND title != ?";
$statement = $db->prepare($sql);
// primera ejecución
$statement->exec(['invalid', 'Este es bueno']);
// segunda ejecución
$statement->exec(['waiting', 'Este también']);
Los parámetros pueden nombrarse en la consulta, y proporcionarse como un array asociativo:
// prepara la consulta
$sql = "DELETE FROM article WHERE status = :status AND title != :title";
$statement = $db->prepare($sql);
// primera ejecución
$statement->exec([
':status' => 'invalid',
':title' => 'Este es bueno',
]);
// segunda ejecución
$statement->exec([
':status' => 'waiting',
':title' => 'Este también',
]);
En ambos casos, los parámetros se escapan automáticamente (consulta los métodos quote() y quoteNull()).
También es posible usar consultas preparadas para recuperar datos. Los métodos queryOne()
y queryAll() se usan para recuperar una fila de datos y todas las filas de datos,
respectivamente. El primero devuelve un array asociativo cuyas claves son los nombres de las columnas; el
segundo devuelve una lista de arrays asociativos.
Al igual que con el método exec(), estos métodos pueden recibir una lista de
parámetros, o un array asociativo de parámetros.
// prepara la consulta
$sql = "SELECT * FROM article WHERE id = ?";
$statement = $db->prepare($sql);
// primera lectura
$article1 = $statement->queryOne([12]);
// segunda lectura
$article2 = $statement->queryOne([23]);
// prepara la consulta
$sql = "SELECT * FROM article WHERE status = :status";
$statement = $db->prepare($sql);
// lectura
$articles = $statement->queryAll([':status' => 'invalid']);
6Gestión de transacciones
El objeto \Temma\Datasources\Sql ofrece dos formas diferentes de gestionar transacciones.
Mediante una función anónima, pasada como parámetro al método transaction(). Si se lanza una excepción dentro de la función anónima, se realiza automáticamente un rollback; en caso contrario, se realiza automáticamente un commit al final de la ejecución de la función anónima.
// creamos la transacción
// la función anónima recibe la conexión
// a la base de datos como parámetro
$db->transaction(function($db) use ($userId) {
// realizamos varias consultas
$db->exec(...);
$db->exec(...);
$db->exec(...);
});
O de forma explícita, usando los métodos startTransaction(), commit() y rollback():
// abrimos una transacción
$db->startTransaction();
// realizamos varias consultas
$db->exec(...);
$db->exec(...);
$db->exec(...);
// procesamiento condicional
if ($something) {
// la transacción se acepta
$db->commit();
} else {
// la transacción se cancela
$db->rollback();
}