Base de datos

Nette Database Core es la capa de abstracción de la base de datos y proporciona la funcionalidad básica.

Instalación

Descargue e instale el paquete utilizando Composer:

composer require nette/database

Conexión y configuración

Para conectarse a la base de datos, basta con crear una instancia de la clase Nette\Database\Connection:

$database = new Nette\Database\Connection($dsn, $user, $password);

El parámetro $dsn (nombre de la fuente de datos) es el mismo que utiliza PDO, por ejemplo host=127.0.0.1;dbname=test. En caso de fallo, lanza Nette\Database\ConnectionException.

Sin embargo, una forma más sofisticada ofrece la configuración de la aplicación. Añadiremos una sección database y crea los objetos requeridos y un panel de base de datos en la barra Tracy.

database:
	dsn: 'mysql:host=127.0.0.1;dbname=test'
	user: root
	password: password

El objeto de conexión que recibimos como servicio de un contenedor DI, por ejemplo:

class Model
{
	// pass Nette\Database\Explorer to work with the Database Explorer layer
	public function __construct(
		private Nette\Database\Connection $database,
	) {
	}
}

Para más información, consulte configuración de la base de datos.

Consultas

Para consultar la base de datos utilice el método query() que devuelve un ResultSet.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
}

echo $result->getRowCount(); // returns the number of rows if is known

Sobre el ResultSet es posible iterar una sola vez, si necesitamos iterar múltiples veces, es necesario convertir el resultado al array mediante el método fetchAll().

Usted puede agregar fácilmente parámetros a la consulta, tenga en cuenta el signo de interrogación:

$database->query('SELECT * FROM users WHERE name = ?', $name);

$database->query('SELECT * FROM users WHERE name = ? AND active = ?', $name, $active);

$database->query('SELECT * FROM users WHERE id IN (?)', $ids); // $ids is array

ADVERTENCIA, ¡nunca concatene cadenas para evitar una vulnerabilidad de inyección SQL!

$db->query('SELECT * FROM users WHERE name = ' . $name); // WRONG!!!

En caso de fallo query() lanza Nette\Database\DriverException o uno de sus descendientes:

ConstraintViolationException – violación de cualquier restricción
ForeignKeyConstraintViolationException – clave externa no válida
NotNullConstraintViolationException – violación de la condición NOT NULL
UniqueConstraintViolationException – conflicto de índice único

Además de query(), existen otros métodos útiles:

// returns the associative array id => name
$pairs = $database->fetchPairs('SELECT id, name FROM users');

// returns all rows as array
$rows = $database->fetchAll('SELECT * FROM users');

// returns single row
$row = $database->fetch('SELECT * FROM users WHERE id = ?', $id);

// return single field
$name = $database->fetchField('SELECT name FROM users WHERE id = ?', $id);

En caso de fallo, todos estos métodos lanzan Nette\Database\DriverException.

Insertar, actualizar y eliminar

El parámetro que insertamos en la consulta SQL también puede ser el array (en cuyo caso es posible omitir la sentencia comodín ?), which may be useful for the INSERT:

$database->query('INSERT INTO users ?', [ // here can be omitted question mark
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978)

$id = $database->getInsertId(); // returns the auto-increment of inserted row

$id = $database->getInsertId($sequence); // or sequence value

Inserción múltiple:

$database->query('INSERT INTO users', [
	[
		'name' => 'Jim',
		'year' => 1978,
	], [
		'name' => 'Jack',
		'year' => 1987,
	],
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978), ('Jack', 1987)

También podemos pasar ficheros, objetos DateTime o enumeraciones:

$database->query('INSERT INTO users', [
	'name' => $name,
	'created' => new DateTime, // or $database::literal('NOW()')
	'avatar' => fopen('image.gif', 'r'), // inserts file contents
	'status' => State::New, // enum State
]);

Actualización de filas:

$result = $database->query('UPDATE users SET', [
	'name' => $name,
	'year' => $year,
], 'WHERE id = ?', $id);
// UPDATE users SET `name` = 'Jim', `year` = 1978 WHERE id = 123

echo $result->getRowCount(); // returns the number of affected rows

Para UPDATE, podemos utilizar los operadores += y -=:

$database->query('UPDATE users SET', [
	'age+=' => 1, // note +=
], 'WHERE id = ?', $id);
// UPDATE users SET `age` = `age` + 1

Borrado:

$result = $database->query('DELETE FROM users WHERE id = ?', $id);
echo $result->getRowCount(); // returns the number of affected rows

Consultas avanzadas

Insertar o actualizar, si ya existe:

$database->query('INSERT INTO users', [
	'id' => $id,
	'name' => $name,
	'year' => $year,
], 'ON DUPLICATE KEY UPDATE', [
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`id`, `name`, `year`) VALUES (123, 'Jim', 1978)
//   ON DUPLICATE KEY UPDATE `name` = 'Jim', `year` = 1978

Nótese que Nette Database reconoce el contexto SQL en el que se inserta el parámetro del array y construye el código SQL en consecuencia. Así, a partir del primer array genera (id, name, year) VALUES (123, 'Jim', 1978), mientras que el segundo lo convierte en name = 'Jim', year = 1978.

También podemos describir la ordenación utilizando un array, en el que las claves son nombres de columnas y los valores son booleanos que determinan si se ordena en orden ascendente:

$database->query('SELECT id FROM author ORDER BY', [
	'id' => true, // ascending
	'name' => false, // descending
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC

Si la detección no ha funcionado, puede especificar la forma del conjunto con un comodín ? seguido de una pista. Estas pistas son compatibles:

?values	(clave1, clave2, …) VALUES (valor1, valor2, …)
?set	clave1 = valor1, clave2 = valor2, …
?and	clave1 = valor1 Y clave2 = valor2 …
?or	clave1 = valor1 OR clave2 = valor2 …
?order	clave1 ASC, clave2 DESC

La cláusula WHERE utiliza el operador ?and por lo que las condiciones están vinculadas por AND:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	'year' => $year,
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND `year` = 1978

Que puede cambiarse fácilmente a OR utilizando el comodín ?or:

$result = $database->query('SELECT * FROM users WHERE ?or', [
	'name' => $name,
	'year' => $year,
]);
// SELECT * FROM users WHERE `name` = 'Jim' OR `year` = 1978

Podemos utilizar operadores en las condiciones:

$result = $database->query('SELECT * FROM users WHERE', [
	'name <>' => $name,
	'year >' => $year,
]);
// SELECT * FROM users WHERE `name` <> 'Jim' AND `year` > 1978

Y también en enumeraciones:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => ['Jim', 'Jack'],
	'role NOT IN' => ['admin', 'owner'], // enumeration + operator NOT IN
]);
// SELECT * FROM users WHERE
//   `name` IN ('Jim', 'Jack') AND `role` NOT IN ('admin', 'owner')

También podemos incluir un trozo de código SQL personalizado utilizando el llamado literal SQL:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	'year >' => $database::literal('YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (`year` > YEAR())

Alternativamente:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (year > YEAR())

SQL literal también puede tener sus parámetros:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > ? AND year < ?', $min, $max),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (year > 1978 AND year < 2017)

Gracias a los cuales podemos crear combinaciones interesantes:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('?or', [
		'active' => true,
		'role' => $role,
	]),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (`active` = 1 OR `role` = 'admin')

Nombre de la variable

Existe un comodín ?name que se utiliza si el nombre de la tabla o de la columna es una variable. (Cuidado, no permita que el usuario manipule el contenido de dicha variable):

$table = 'blog.users';
$column = 'name';
$database->query('SELECT * FROM ?name WHERE ?name = ?', $table, $column, $name);
// SELECT * FROM `blog`.`users` WHERE `name` = 'Jim'

Transacciones

Existen tres métodos para tratar las transacciones:

$database->beginTransaction();

$database->commit();

$database->rollback();

Una forma elegante es la que ofrece el método transaction(). Se pasa la llamada de retorno que se ejecuta en la transacción. Si se lanza una excepción durante la ejecución, la transacción se abandona, si todo va bien, la transacción se compromete.

$id = $database->transaction(function ($database) {
	$database->query('DELETE FROM ...');
	$database->query('INSERT INTO ...');
	// ...
	return $database->getInsertId();
});

Como puede ver, el método transaction() devuelve el valor de retorno de la llamada de retorno.

La transacción() también se puede anidar, lo que simplifica la implementación de repositorios independientes.

Reflexión

Nette Database proporciona herramientas para la introspección de la estructura de la base de datos a través de la clase Nette\Database\Reflection. Esta clase permite recuperar información sobre tablas, columnas, índices y claves externas. Puedes utilizar reflection para generar esquemas, crear aplicaciones flexibles que trabajen con bases de datos o construir herramientas generales de bases de datos.

Puede obtener un objeto reflection a partir de una instancia de conexión a una base de datos:

$reflection = $database->getReflection();

Trabajar con tablas

Usando reflection, puedes iterar sobre todas las tablas de la base de datos:

// Listar los nombres de todas las tablas
foreach ($reflection->tables as $tableName => $table) {
    echo $tableName . "\n";
}

// Comprobar si existe una tabla
if ($reflection->hasTable('users')) {
    echo "The 'users' table exists";
}

// Recuperar una tabla específica
$table = $reflection->getTable('users');

Información sobre columnas

Para cada tabla, puede obtener información detallada sobre sus columnas:

// Iterar sobre todas las columnas
foreach ($table->columns as $column) {
    echo "Column: " . $column->name . "\n";
    echo "Type: " . $column->nativeType . "\n";
    echo "Nullable: " . ($column->nullable ? 'Yes': 'No') . "\n";
    echo "Default value: " . ($column->default ?? 'None') . "\n";
    echo "Primary key: " . ($column->primary ? 'Yes': 'No') . "\n";
    echo "Auto-increment: " . ($column->autoIncrement ? 'Yes': 'No') . "\n";
}

// Recuperar una columna específica
$idColumn = $table->getColumn('id');

Índices y claves primarias

Reflection proporciona información sobre índices y claves primarias:

$listColumnNames = fn(array $columns) => implode(', ', array_map(fn($col) => $col->name, $columns));

// Listar todos los índices
foreach ($table->indexes as $index) {
    echo "Index: " . ($index->name ?? 'Unnamed') . "\n";
    echo "Columns: " . $listColumnNames($index->columns) . "\n";
    echo "Unique: " . ($index->unique ? 'Yes': 'No') . "\n";
    echo "Primary key: " . ($index->primary ? 'Yes': 'No') . "\n";
}

// Recuperar la clave primaria
if ($table->primaryKey) {
    echo "Primary key: " . $listColumnNames($table->primaryKey->columns) . "\n";
}

Claves extranjeras

También puede obtener información sobre claves foráneas:

foreach ($table->foreignKeys as $fk) {
    echo "Foreign key: " . ($fk->name ?? 'Unnamed') . "\n";
    echo "Local columns: " . $listColumnNames($fk->localColumns) . "\n";
    echo "References table: {$fk->foreignTable->name}\n";
    echo "References columns: " . $listColumnNames($fk->foreignColumns) . "\n";
}