Crie um registo de alterações com JDBC usando a funcionalidade de data/hora de confirmação do Spanner

Este documento descreve como usar a funcionalidade de data/hora de confirmação para acompanhar a data e a hora em que as alterações são feitas aos registos da base de dados. Quando tem uma grande base de dados com muitas transações que alteram registos e quer captar as alterações feitas aos conjuntos de dados, a funcionalidade de data/hora de confirmação simplifica esta tarefa.

Através de uma série de exemplos de código, este documento mostra como reescrever a linguagem de manipulação de dados (DML) existente para criar um registo de alterações. O documento expande o artigo Usar as datas/horas de confirmação do Cloud Spanner para criar um registo de alterações com Go demonstrando os métodos com Java e JDBC. A lógica é representada em SQL em vez de como objetos de cliente do Spanner.

Como funcionam as tabelas

Suponha que tem uma tabela de factos denominada msgs e uma tabela de histórico denominada msg_history. Sempre que a tabela de factos é modificada, quer guardar simultaneamente um registo na tabela msg_history. Pode usar o conteúdo da tabela de histórico mais tarde para outras utilizações, por exemplo, para auditoria ou como origem de eventos.

A tabela msgs contém os factos, representados por um ID da transação (id) e a mensagem (msg). A declaração da linguagem de definição de dados (LDD) que usa para criar a tabela tem o seguinte aspeto:

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

A tabela msg_history contém o histórico de transações. O DDL seguinte cria a tabela do histórico. A coluna ts armazena a data/hora de confirmação.

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

Os exemplos

Em vez de escrever apenas na tabela de factos, tem de fazer a operação na tabela de histórico na mesma transação. O controlador JDBC do Spanner suporta a declaração do início e do fim de uma transação, que é uma operação JDBC padrão.

Passo 1: reescreva as operações de inserção

O primeiro passo é reescrever as operações insert a partir do seguinte formulário:

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

  • Altere as inserções para que tenham o seguinte aspeto:

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

    Segue-se o código 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();

Passo 2: reescreva as operações de atualização

Em seguida, reescreva as operações update do seguinte formulário:

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

  • Altere as atualizações para que tenham o seguinte aspeto:

    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;

    Segue-se o código 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();

Passo 3: reescreva as operações de eliminação

Por fim, reescreva as operações delete no seguinte formato:

delete from msgs where id = 1

  • Altere as eliminações para que fiquem semelhantes ao seguinte:

    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;

    Segue-se o código 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();

Usar a tabela do histórico

  • Aceda ao histórico de alterações da seguinte forma:

    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

    Os resultados têm um aspeto semelhante ao seguinte:

    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

O que se segue?