Modules - Database API

Database API

La Database API, da utilizzare solo nei casi in cui non è possibile usare altre API, utilizza una classe Connection che rappresenta una connessione al database e fornisce metodi come:

• select()
• query()
• insert()
• update()
• delete()
• merge()
• upsert()
• truncate()
• startTransaction()
• schema()

Supponendo di avere una tabella con le colonne id, location, temperature e created, per prima cosa dobbiamo creare la connessione:

use Drupal\Core\Database\Connection; 

final class ExampleStorage { 

  public function __construct( 
    private readonly Connection $database, 
  ) {} 

}

Esempio procedurale:

function nome_modulo_example(): void { 
  $database = \Drupal::database(); 
  // ... 
}

Schema API

La Schema API permette di descrivere le tabelle attraverso array associativi, quindi nel file nome_modulo.install verrà chiamato hook_schema() in modo che all'installazione del modulo vengano create automaticamente le tabelle descritte e alla disinstallazione vengano eliminate. L'hook_schema() è sempre procedurale, ad esempio:

<?php

<?php

/** 
 * Implements hook_schema(). 
 */ 

function nome_modulo_schema(): array {
    $schema = [];

    $schema['nome_modulo_example_measurement'] = [ // nome della tabella
        'description' => 
            'Stores example weather measurements.', 
        'fields' => [ // dentro fields ci sono le colonne
            'id' => [
                'description' => 
                    'Primary identifier.', 
                'type' => 'serial', // campo numerico auto incrementale
                'not null' => TRUE,
            ], 
            'location' => [
                'description' => 
                    'Human-readable location.', 
                'type' => 'varchar', //il tipo varchar richiede una lunghezza
                'length' => 128, 
                'not null' => TRUE,
            ], 
            'temperature' => [
                'description' => 
                    'Measured temperature.', 
                'type' => 'numeric', //il tipo numeric richiede precision e scale
                'precision' => 6, 
                'scale' => 2, 
                'not null' => TRUE,
            ], 
            'created' => [
                'description' => 
                    'Unix timestamp when the measurement was recorded.',
                'type' => 'int', 
                'unsigned' => TRUE, 
                'not null' => TRUE,
            ],
        ], 
        'primary key' => [ // una o più colonne da usare come identificativo univoco
            'id', //il field chiamato id definito sopra
        ], 
        'indexes' => [ //importante per velocizzare la query
            'location' => [
                'location',
            ], 
            'created' => [
                'created',
            ],
        ],
    ];
    return $schema;
}

?>

Oggetto Schema

L'oggetto recuperato con:

$schema = $database->schema();

permette di eseguire queste operazioni:

• tableExists()
• fieldExists()
• indexExists()
• createTable()
• dropTable()
• addField()
• dropField()
• changeField()
• addIndex()
• dropIndex()
• addUniqueKey()
• dropUniqueKey()

Query di SELECT

Le query di select possono ssere statiche o dinamiche. Una query statica contiene direttamente SQL:

$result = $this->database->query( 
  'SELECT id, location, temperature 
  FROM {nome_modulo_example_measurement} //il nome della tabella deve essere dentro {} senza eventuale prefisso
  WHERE location = :location',  //placeholde rper proteggere da SQL injection
  [ 
    ':location' => $location, 
  ], 
);

//sbagliato
WHERE location = '$location'";

//corretto
WHERE location = :location';

Una query dinamica invece viene costruita attraverso metodi PHP:

$query = $this->database->select( 
  'nome_modulo_example_measurement', //qui no parentesi {} nel nome della tabella
  'm', // alias della tabella
); 

$query->fields( 
  'm', 
  [ 
    'id', 
    'location', 
    'temperature', 
    'created', 
  ], 
); 

$query->condition( //eventualmente si può passare terzo parametro '=' che indica l'operatore ma è già di default 
  'm.location', 
  $location, 
); 

$query->orderBy( 
  'm.created', 
  'DESC', 
); 

$query->range( //per usare clausola LIMIT
  0, //indica l'offset
  10, //indica il limit
); 

$result = $query->execute();

Esempi di condition:

$query->condition( 
  'm.temperature', 
  30, 
  '>=', 
);

$query->condition( 
  'm.location', 
  [ 
    'Turin', 
    'Milan', 
    'Rome', 
  ], 
  'IN', 
);

// condizione con due parametri
// equivalente di 
// WHERE ( location = 'Turin' OR location = 'Milan' ) AND temperature >= 25

<?php

$query = $this->database->select(
    'nome_modulo_example_measurement', 
    'm',
);

$query->fields(
    'm', 
    [
        'id', 
        'location', 
        'temperature',
    ],
);


$group = $query->orConditionGroup()
    ->condition(
        'm.location', 
        'Turin',
    )->condition(
        'm.location', 
        'Milan',
);

$query
    ->condition($group)
    ->condition(
        'm.temperature', 
        25, 
        '>=',
    );

Esempio con JOIN:

<?php

$query = $this->database->select(
    'nome_modulo_example_measurement', 
    'm',
);

$alias = $query->leftJoin( //join restituisce l'alias della tabella 
    'users_field_data', 
    'u', 
    'u.uid = m.uid',
);

$query->fields(
    'm', 
    [
        'id', 
        'temperature',
    ],
);

$query->addField(
    $alias, 
    'name', 
    'username',
);

Esempio di COUNT:

$count = $this->database 
    ->select( 
        'nome_modulo_example_measurement', 
        'm', 
    ) 
    ->condition( 
        'm.location', 
        $location, 
    ) 
    ->countQuery() 
    ->execute() 
    ->fetchField();

Result set

L'esecuzione della query ritorna un oggetto che implementa StatementInterface e che può essere iterato:

$result = $query->execute();

foreach ($result as $record) { 
  $id = $record->id; 
  $temperature = $record->temperature; 
}

// per recuperare l'oggetto successivo
$record = $result->fetchObject();

// per ottenere un array associativo
$record = $result->fetchAssoc();

// per recuperare un singolo valore
$temperature = $this->database
    ->query(
        'SELECT temperature 
        FROM {nome_modulo_example_measurement} 
        WHERE id = :id', 
        [
            ':id' => $id,
        ],
    )->fetchField(); 

// per recuperare tutti i record di una singola colonna
//restituisce
// [ 1, 2, 3, ]
$ids = $result->fetchCol();

// per recuperare tutti i record (meglio non usarlo perchè il result set potrebbe essere grande)
$records = $result->fetchAll();

// ottenere un array indicizzato attraverso un campo
//restituisce
// [ 1 => $record1, 2 => $record2, ]
$records = $result->fetchAllAssoc( 
    'id', 
);

//supponendo di avere una query del tipo SELECT id, location
//rstituisce 
//[ 1 => 'Turin', 2 => 'Milan', ]
$locations = $result->fetchAllKeyed();

Query di INSERT

Per inserire un record:

$id = $this->database 
    ->insert( 
        'nome_modulo_example_measurement', 
        ) 
    ->fields([ 
        'location' => 'Turin', 
        'temperature' => 28.4, 
        'created' => time(), 
        ]) 
    ->execute(); //restituisce l'id generato


//per inserire più record
$query = $this->database
  ->insert(
    'nome_modulo_example_measurement',
  )
  ->fields([
    'location',
    'temperature',
    'created',
  ]);

$query->values([
  'Turin',
  28.4,
  1786000000,
]);

$query->values([
  'Milan',
  30.1,
  1786000000,
]);

$query->execute();

Query di UPDATE

Per modificare un record:

$updated = $this->database 
    ->update( 
        'nome_modulo_example_measurement', 
    ) 
    ->fields([ 
        'temperature' => 29.2, 
    ]) 
    ->condition( 
        'id', 
        $id, 
    ) 
    ->execute();

Query di DELETE

Per eliminare una riga:

$deleted = $this->database 
    ->delete( 
        'nome_modulo_example_measurement', 
    ) 
    ->condition( 
        'id', 
        $id, 
    ) 
    ->execute();

Se invece si vuole svuotare una tabella esiste anche il metodo truncate che però è concettualmente diverso.

Transazioni

Una transazione permette di trattare più operazioni di scrittura come un'unica unità logica e si usa nei casi in cui un'operazione deve essere considerata valida soltanto se tutte le scritture collegate hanno successo.

<?php

use Throwable;

$transaction = 
    $this->database
        ->startTransaction();

try {
    $measurementId = 
        $this->database
            ->insert(
                'nome_modulo_example_measurement',
            )
            ->fields([
                'location' => 'Turin', 
                'temperature' => 28.4, 
                'created' => time(),
            ])->execute();

    $this->database
        ->update(
            'nome_modulo_example_statistics',
            )
            ->expression(
                'measurement_count', 
                'measurement_count + 1',
            )
            ->condition(
                'location', 
                'Turin',
            )
            ->execute();

    $transaction->commitOrRelease();
} catch (Throwable $exception) {
    $transaction->rollBack();
    
    throw $exception;
}