D8 DB Database - pierregermain/MyDrupal GitHub Wiki

Creación

  • utf8_spanish_ci
  • utf8_general_ci
  • utf8_unicode_ci

Conectarse a la DB

3 maneras de hacerlo:

$connection = Drupal\Core\Database\Database::getConnection();
$connection = \Drupal::database();
$connection = \Drupal::service('database');

Se obtiene con ello \Drupal\Core\Database\Connection

Creación Schema

Schema API: Usamos los ficheros schema para definir cómo guardar nuestros datos en DB

Desde hook_schema() cremos nuestras tablas. Se declara en los ficheros *.install

Primero se ejecuta hook_schema y despues hook_install al instalar un módulo.

Ejemplo:

/**
 * Implements hook_schema().
 */
function mymodule_schema() {
  $schema['file_usage'] = [
    'description' => 'Track where a file is used.',
    'fields' => [
      'fid' => [
        'description' => 'File ID.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ],
      'module' => [
        'description' => 'The name of the module that is using the file.',
        'type' => 'varchar_ascii',
        'length' => DRUPAL_EXTENSION_NAME_MAX_LENGTH,
        'not null' => TRUE,
        'default' => '',
      ],
    ],
    'primary key' => ['fid'],
    'indexes' => [
      'fid_module' => ['fid', 'module'],
    ],
  ];
  return $schema;
}

Tipos de campos muy usados

// Autoincrementado
      'id' => [
        'description' => 'ID.',
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ],
// Status con 0 y 1
      'status' => [
        'description' => 'A flag to indicate true (1) or false (0).',
        'type' => 'int',
        'size' => 'small',
        'not null' => TRUE,
        'default' => 0,
      ],
// Título
      'title' => [
        'description' => 'The node title.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ],
// Fecha
'publish_date' => [
  'description' => t('The publish date for the single news'),
  'type' => 'text',
  'mysql_type' => 'datetime',
  'not null' => FALSE,
],

En el caso del campo Fecha se usa 'mysl_type' igual a datetime porque es lo que queremos usar. Pero también usamos 'type' igual a 'text' ya que si no se usa una DB tipo mysql puede ser que dicho sistema de db no tenga el tipo 'datetime' y es por lo que necesitamos un sistema tipo fallback option.

Trabajar con Schema pragmáticamente

Una vez realizado la conexión ($connection) a la db podemos obtener el schema pragmáticamente

$schema = $this->connection->schema();

Creación de nueva tabla:

$table_definition = [
// ...
];
$schema->createTable('cachetags', $table_definition);

Otros métodos de tabla:

$schema->dropTable('cachetags');
$schema->renameTable('foo', 'new_name');
$tableExists = $schema->tableExists('foo');
$tables = $schema->findTables('test_%');
$schema->dropField('table_name', 'field_name');
$fieldExists = $schema->fieldExists('table_name', 'field_name');

Añadir un nuevo campo:

$field_definition = [
// ...
];
$schema->addField('table_name', 'new_field', $field_definition);

Cambiar un campo:

$field_definition = [
// ...
];
$schema->changeField('table_name', 'field', 'field', $field_definition);

Trabajar con índices

$schema->addPrimaryKey('table', ['uid', 'nid']);
$schema->dropPrimaryKey('table');

$schema->addUniqueKey('table', 'name', ['field']);
$schema->dropUniqueKey('table', 'name');

Añadir Índice

$spec = drupal_get_module_schema('module_name', 'table_name');
$schema->addIndex('bar', 'nid', ['nid'], $spec);

Borrar Índice

$schema->dropIndex('bar', 'nid');

Comprobar si existe un índice:

$schema->indexExists('bar', 'nid')

Realizar Sentencias

Sentencias estáticas (SQL Directo)

$result = $this->connection->query('SELECT source, alias, langcode
FROM {url_alias} WHERE pid = :pid', [':pid' => $pid])
->fetchAssoc();

Mas info aqui: https://www.drupal.org/docs/drupal-apis/database-api/result-sets

Sentencias dinámicas: DBTNG (Database: The Next Generation)

Se hacen las sentencias por partes partiendo del objeto $query.

Nos devuelve \Drupal\Core\Database\StatementInterface

$query = $this->connection->select('node', 'n')
  ->fields('n', ['nid', 'type'])
  ->condition('nid', 1, '>');

//Podemos seguir añadiendo métodos al objeto query (select)
$query->orderBy('type', 'ASC');

//Ejecuta la sentencia
$result = $query->execute();

//Recuperamos los valores devueltos por la sentencia
while($record = $result->fetchAssoc()) {
  //operaciones a realizar con cada registro
}

Para probar sentencias podemos usar drupalconsole snippet.

Recuperación de datos

Foreach

$connection = \Drupal::database();
$result = $connection->query('SELECT nid, totalcount FROM {node_counter}');
foreach ($result as $record){
  //valores disponibles: $record->nid y $record->totalcount
}

fetchObject()

while ($record = $result->fetchObject()){
  //valores disponibles: $record->nid y $record->totalcount
};

fetchAssoc()

while ($record = $result->fetchAssoc()){
//valores disponibles: $record['nid'] y $record['totalcount']
};

fetchAll() y fetchAllAssoc()

Recupear todos los valores sin bucle.

$records = $result->fetchAll();
//dump($records);
$records[12]->nid = 12;
$records[15]->nid = 15;

$records = $result->fetchAllAssoc('nid');
//dump($records);
$records[12]['nid'] = 12;
$records[12]['totalcount'] = 3;

fetchCol()

$records = $result->fetchCol('nid');
//dump($records);
$records[0] = 12;
$records[1] = 15;

rowCount()

$contador = $result->rowCount();

Sentencias estáticas

Método Connection:query()

El método Connection::query($query, $args, $options) ejecuta una sentencia estática sobre la base de datos.

Ejemplo:

$result = $connection->query('SELECT * FROM {users_field_data} u
WHERE u.uid = :uid', [':uid' => $uid]);

Método Connection:query_range()

El método Connection::queryRange($query, $from, $count, $args, $options) es similar a Connection::query() pero permite obtener un número limitado de registros en la consulta. El funcionamiento es similar a utilizar LIMIT dentro de la sentencia.

Ejemplo:

$uid = 1;
$result = $connection->queryRange('SELECT * FROM {watchdog} u WHERE u.uid = :uid', 0, 5, [':uid' => $uid]);
foreach ($result as $record){
  //dump($record);
}

Sentencias dinámicas: Select

Todas las clases de sentencias dinámicas heredan de Query

Sentencia Select

Select

Definición en la API de la clase Select:

class Select extends Query implements SelectInterface {
use QueryConditionTrait;

Ejemplo de Uso:

// Siempre definimos nuestra tabla con un alias, en este caso 'n'
$query = $connection->select('node_counter', 'n')
  ->fields('n')
  ->condition('n.totalcount', 1, '>');

// Añadir más métodos
$query->orderBy('n.totalcount', 'DESC')->orderBy('n.timestamp', 'DESC');

// Ejecuta la sentencia
$result = $query->execute();

// Se obtienen los resultados
foreach ($result as $record){
  dump($record);
}

Depuración de sentencias SQL con dpq()

dpq($query);

Obtenemos la SQL ejecutada :)

Métodos de la clase Select

Más info en la API de Select

fields()

$query->fields('n', ['nid', 'totalcount', 'daycount']);
$query->fields('n'); // Añade todos los campos (SELECT *)

addField()

$query->addField('n', 'title', 'node_title');

distinct()

$query->distinct();

condition()

// El valor de nid = 1
$query->condition('n.nid', 1);
// El valor de nid > 1
$query->condition('n.nid', 1, '>');
// El valor de nid está entre 5 y 10
$query->condition('n.nid', [5, 10], 'BETWEEN');
// El valor de name comienza por 'node_'
$query->condition('n.name', 'node_%', 'LIKE');

andConditionGroup(), orConditionGroup()

// OR: Se debe cumplir una de las condiciones
$query->orConditionGroup()
  ->condition('n.id', 100, '>')
  ->condition('n.name', 'node_%', 'LIKE');

// AND: Se deben cumplir todas las condiciones
$query->andConditionGroup()
  ->condition('n.nid', $node->id())
  ->condition('n.langcode', 'es');

isNull(), isNotNull()

// El campo title debe ser NULL
$query->isNull('title');

// El campo uid no debe ser NULL
$query->isNotNull('uid');

exits(), notExists()

// Se define una subquery como objeto Select independiente
$subquery = $connection->select('foo', 'f')
  ->fields('f')
  ->condition('f.bar', 100, '>');

// Se comprueba si la subquery no devuelve resultados
$query->notExists($subquery);

orderBy()

// Ordena por el campo title, en orden ascendente
$query->orderBy('title', 'ASC');

range()

// Devuelve 10 elementos a partir del quinto elemento
$query->range(5, 10);
// Devuelve los 10 primeros resultados
$query->range(0, 10);

addExpression()

// Cuenta los registros devueltos
$query->addExpression('COUNT(*)', 'counter');
// Devuelve el valor máximo de nid
$query->addExpression('MAX(nid)', 'max_nid');

Recuerda usarlo con groupBy!

groupBy()

$query->groupBy('uid');

having(), havingCondition(), havingIsNull(), havingExists(), etc.

$query->havingCondition('uid', 1, '<>');

join(), addJoin(), innerJoin(), leftJoin() y rightJoin()

$query = $this->connection->select('book', 'b')
->fields('b', array('nid', 'bid'));

// Join con la tabla menu_links
// con la condicón b.mlid = ml.mlid
$query->join('menu_links', 'ml', 'b.mlid = ml.mlid');

// Ejemplo de join utilizando placeholders
$query->join('user', 'u', 'n.uid = u.uid AND u.uid = :uid',
[':uid' => 5]);

union()

// Realiza un UNION ALL de los resultados de $query y $query2
$query->union($query2, 'ALL');

countQuery()

$query = $connection->select('node_counter', 'n')
  ->fields('n')
  ->condition('n.totalcount', 1, '>');

// Obtiene el número de registros
$count = $query->countQuery()->execute()->fetchField();

// Ejecuta la sentencia para obtener los registros
$result = $query->execute();

Más métodos

Ejemplo Mostrar resultados en una tabla $build

$query = $this->connection
      ->select('comment', 'c');
$query->join('comment_field_data', 'cfd', 'c.cid = cfd.cid');
$query->fields('c', array('uuid', 'extra'));
$query->fields('cfd', ['cid', 'entity_id', 'subject', 'uid']);

$query->orderBy('cid', 'DESC');
dpq($query);

$result = $query->execute();
$rows = [];

$build['comment_table'] = [
  '#type' => 'table',
  '#header' => ['subject','uuid','cid','entity_id','uid','extra'],
];
foreach ($result as $record){
  $rows[] = [
    $record->subject,
    $record->uuid,
    $record->cid,
    $record->entity_id,
    $record->uid,
    $record->extra,
  ];
}

$build['comment_table']['#rows'] = $rows;

Sentencias dinámicas: Insert

Métodos disponibles

Ejemplo:

// Inserta en la tabla semaphore los valores de los campos
// name, value
$connection->insert('semaphore')
  ->fields([
    'name' => $name,
    'value' => $this->getLockId(),
    ])
->execute();

Retorno de datos en la INSERT

Si queremos que la sentencia nos devuelva datos podemos usar lo siguiente

$connection->insert('tabla',$options);

donde:

  • $options['return'] = Database::RETURN_INSERT_ID; Devuelve el ID de la clave primaria insertado.

  • $options['return'] = Database::RETURN_NULL; Devuelve siempre NULL

  • $options['return'] = Database::RETURN_AFFECTED; Devuelve el número de registros modificados

Métodos disponibles en la INSERT

  • fields()

  • values()

En este caso usaremos fields() para designar los campos, y values() para insertar los valores

$insert = $connection->insert('test_people')
  ->fields(['job', 'age', 'name']);

$insert->values([
  'job' => 'Presenter',
  'age' => 31,
  'name' => 'Tiffany',
]);
  • execute()

Sentencias dinámicas: Update

Métodos disponibles

// Actualiza los registros de la tabla foo
// en los que se cumple la condición fid = $node->fid
$connection->update('nombre_tabla')
  ->fields([
    'vid' => $foo->vid,
    'title' => $foo->title,
  ])
  ->condition('fid', $node->fid)
  ->execute();

Retorno de datos con *$options['return']:

$options['return'] = Database::RETURN_AFFECTED Devuelve número de registros modificados

Se pueden usar los otros valores visto en el INSERT.

Métodos de UPDATE

  • fields()
  • Condiciones:
    • condition()
    • andConditionGroup()
    • orConditionGroup()
    • exists
    • notExists
    • isNull
    • notIsNull
  • execute()

Uso de expresiones

Podemos usar el método expression para realizar actualizaciones en función de una expresión

Ejemplo: bar = bar + $step

$connection->update('foo')
  ->fields([
    'vid' => $foo->vid,
    'title' => $foo->title,
  ])
  ->condition('fid', $node->fid)
  ->expression('bar', 'bar + :step', array(':step' => $step))
  ->execute();

Otro ejemplo:

      $update = $this->connection->update('nombre_tabla')
        ->fields(['lastcount' => time()])
        ->condition('uid', $uid)
        ->condition('route', $route)
        ->expression('user_count', 'user_count + 1');

Sentencias dinámicas: Upsert

Crea una sentencia que ejecuta un UPDATE o un INSERT en función de si el registro existe o no existe en la tabla.

Esta clase solo puede ser utilizada con tablas que tienen un índice único, generalmente la clave primaria.

$upsert = $connection->upsert('test_people')
  ->key('job')
  ->fields(['job', 'age', 'name']);

// Añade un nuevo registro
$upsert->values([
  'job' => 'Presenter',
  'age' => 31,
  'name' => 'Tiffany',
]);

// Actualiza un registro existente
$upsert->values([
  'job' => 'Speaker',
  'age' => 32, //modifica el valor anterior
  'name' => 'Meredith',
]);

$upsert->execute();

Sentencia dinámica: Merge

https://api.drupal.org/api/drupal/core!lib!Drupal!Core!Database!Query!Merge.php/class/Merge/8

Sentencias dinámicas: Delete

$connection->delete('batch')
  ->condition('bid', $bid)
  ->execute();

Sentencia dinámica: Truncate

// Vacía la tabla watchdog
$connection->truncate('watchdog')->execute();

Transacciones

  • Transaction permite hacer rollback sobre acciones ya realizadas.

  • es necesario que la basede datos soporte transacciones.

Ejemplo:

$transaction = $this->connection->startTransaction();
try { // Operaciones dentro de la transacción
  //...
}
catch (\Exception $e) {
  $transaction->rollback();
  throw $e;
}

Etiquetado de sentencias

  • solo las sentencias de tipo Select permiten el uso de etiquetas.
  • Permiten que otros módulos o funciones puedan localizar una sentencia y actuar sobre ella.

Ejemplo:

/**
* Implements hook_query_alter().
*/
function my_module_query_alter(AlterableInterface $query)
{
// Comprobamos si la $query tiene la etiqueta que buscamos
  if ($query->hasTag('my_tag')) {
    $query->range(0, 2);
    $query->condition('bar', 100, '>');
  }
}

Añadir DB al Settings

Podemos añadir db's al settings, para acceder a ellas desde nuestro código haríamos

// Guardamos base de datos actual
$default_db = Database::getConnection()->getKey();

// Nos conectamos a la extradb
Database::setActiveConnection('extradb');

// Operaciones sobre la BD extradb
// $records = Database::getConnection()->select('mytable1', 'm')...

// Recupera la conexión a la base de datos anterior
Database::setActiveConnection($default_db);

Añadir DB en código

Podemos acceder una DB configurada en código usando Database::addConnectionInfo