Créer un journal des modifications avec JDBC à l'aide de la fonctionnalité d'horodatage de commit de Spanner

Ce document explique comment utiliser la fonctionnalité d'horodatage de commit pour suivre la date et l'heure auxquelles des modifications sont apportées à vos enregistrements de base de données. Lorsque vous disposez d'une base de données volumineuse avec de nombreuses transactions modifiant les enregistrements, et que vous souhaitez capturer les modifications apportées aux ensembles de données, la fonctionnalité d'horodatage de commit simplifie cette tâche.

Grâce à plusieurs exemples de code, ce document vous montre comment réécrire le langage de manipulation de données (LMD) existant pour créer un journal des modifications. Il complète la page Créer un journal des modifications avec Go à l'aide des horodatages de commit Cloud Spanner en faisant la démonstration des méthodes avec Java et JDBC. La logique est représentée en SQL plutôt que sous la forme d'objets client Spanner.

Fonctionnement des tables

Supposons que vous disposiez d'une table de faits appelée msgs et d'une table d'historique appelée msg_history. Chaque fois que la table de faits est modifiée, vous souhaitez créer simultanément un enregistrement dans la table msg_history. Vous pourrez utiliser le contenu de la table d'historique ultérieurement à d'autres fins, par exemple pour l'audit ou comme source d'événement.

La table msgs contient les faits, représentés par un ID de transaction (id) et le message (msg). L'instruction LDD (langage de définition de données) que vous utilisez pour créer la table ressemble à ceci :

CREATE TABLE msgs (
    id INT64,
    msg STRING(MAX),
) PRIMARY KEY (id)

La table msg_history contient l'historique des transactions. L'instruction LDD suivante crée la table d'historique. La colonne ts stocke l'horodatage de commit.

CREATE TABLE msgs_history (
    id INT64,
    ts TIMESTAMP OPTIONS (allow_commit_timestamp=true),
    previous_msg STRING(MAX)
) PRIMARY KEY (ts, id)

Exemples

Au lieu de procéder à l'écriture seulement dans la table de faits, vous devez effectuer l'opération dans la table d'historique dans la même transaction. Le pilote JDBC de Spanner accepte la déclaration du début et de la fin d'une transaction, qui est une opération JDBC standard.

Étape 1 : Réécrire les opérations d'insertion

La première étape consiste à réécrire les opérations insert au format suivant :

insert into msgs (id, msg) values (1, 'a real msg')

  • Modifiez les insertions de sorte qu'elles se présentent comme suit :

    insert into msgs_history (id, previous_msg, ts) values (1, Null, PENDING_COMMIT_TIMESTAMP());
insert into msgs (id, msg) values (1, 'a real msg');

    Voici le code Java :

    // insert into history
stmt.executeUpdate(
    "insert into msgs_history (id, previous_msg, ts)"
        + " values (1, Null, PENDING_COMMIT_TIMESTAMP())");
// insert into real table
stmt.executeUpdate("insert into msgs (id, msg)" + " values (1, 'a real msg')");
conn.commit();

Étape 2 : Réécrire les opérations de mise à jour

Ensuite, vous réécrivez les opérations update au format suivant :

update msgs set msg = 'new message' where id = 1

  • Modifiez les mises à jour de sorte qu'elles se présentent comme suit :

    insert msgs_history (id, previous_msg, ts) values ((select id from msgs where id =1 ), (select msg from msgs where id = 1), PENDING_COMMIT_TIMESTAMP());
update msgs set msg = 'new message' where id = 1;

    Voici le code Java :

    // insert into historystmt.executeUpdate(
    "insert msgs_history (id, previous_msg, ts)"
        + " values  ((select id from msgs where id =1),"
        + " (select msg from msgs where id = 1), PENDING_COMMIT_TIMESTAMP())");
// update into fact table
stmt.executeUpdate("update msgs set msg = 'new message' where id = 1");
conn.commit();

Étape 3 : Réécrire les opérations de suppression

Enfin, vous réécrivez les opérations delete au format suivant :

delete from msgs where id = 1

  • Modifiez les suppressions de sorte qu'elles se présentent comme suit :

    insert msgs_history (id, previous_msg, ts) values ((select id from msgs where id =1 ), (select msg from msgs where id = 1), PENDING_COMMIT_TIMESTAMP());
delete from msgs where id = 1;

    Voici le code Java :

    // insert into history
stmt.executeUpdate(
    "insert msgs_history (id, previous_msg, ts)"
        + " values  ((select id from msgs where id =1),"
        + " (select msg from msgs where id = 1), PENDING_COMMIT_TIMESTAMP()) ");
// delete from real table
stmt.executeUpdate("delete from msgs where id = 1");
conn.commit();

Utiliser la table d'historique

  • Accédez à l'historique des modifications comme suit :

    select id,msg,current_timestamp() as ts
from msgs where id = 1
union all
select id,previous_msg, ts as msg
from msgs_history where id = 1 order by ts desc

    Les résultats ressemblent à ce qui suit :

    id    msg             ts
1     new message     2020-02-07T07:44:10.24833726Z
1     a real msg      2020-01-14T10:07:20.137935Z
1                     2020-01-14T10:07:20.070374Z

Étape suivante