Início / dba / Perguntas / 337863
Accepted
Nick Parrill
Asked: 2024-03-20 07:05:01 +0800 CST

Par de chaves estrangeiras MariaDB que faz referência ao registro de tabela com versão do sistema

  • 772

Estou alterando um sistema de reservas de código aberto para atender às necessidades do meu empregador. Gostaria de fazer com que a edição ou exclusão de um serviço não modifique ou exclua necessariamente o serviço referenciado pelos compromissos existentes (por exemplo, editar o preço de um serviço deixa o mesmo preço listado para os compromissos que foram feitos antes da alteração) .

Atualmente, eu o implementei de forma que a tabela que armazena o serviço seja versionada usando o versionamento do sistema MariaDB, e os compromissos armazenem o row_startregistro do serviço para buscá-lo como estava na criação do compromisso posteriormente. Os compromissos atualmente usam o ID do serviço como chave estrangeira, e eu gostaria de mudar isso para um par de chaves usando o ID e as row_endcolunas de forma que o compromisso faça referência ao serviço como ele existia quando o compromisso foi feito ou editado pela última vez usando row_end. Dessa forma posso excluir ou editar registros de serviço na versão "presente" da tabela de serviços sem afetar os compromissos existentes para esse serviço.

Basicamente eu tenho:

CREATE TABLE `services` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(256) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci WITH SYSTEM VERSIONING;

INSERT INTO `services` VALUES
(1, 'Service', 30, 'This is a description', 20.00);

CREATE TABLE `appointments` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `start_datetime` datetime DEFAULT NULL,
  `end_datetime` datetime DEFAULT NULL,
  `id_services` int(11) DEFAULT NULL,
  `service_timestamp` timestamp(6) NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `appointments` VALUES
(1, '2024-03-19 11:30:00', '2024-03-19 12:00:00', 1, '2038-01-18 21:14:07.999999');

ALTER TABLE appointments 
ADD CONSTRAINT `appointments_services` 
FOREIGN KEY (`id_services`, `service_timestamp`) 
REFERENCES services(`id`, `row_end`) 
ON DELETE CASCADE ON UPDATE CASCADE;

E eu gostaria do resultado de:

UPDATE `services` SET `price`=30.00 WHERE `id` = 1

Nas nomeações a serem:

table : appointments
+-------+---------------------+---------------------+-------------+----------------------------+
|    id |      start_datetime |        end_datetime | id_services |          service_timestamp |
+-------+---------------------+---------------------+-------------+----------------------------+
|     1 | 2024-03-19 11:30:00 | 2024-03-19 12:00:00 |           1 | (When the update happened) |
+-------+---------------------+---------------------+-------------+----------------------------+

Em vez disso, recebo:

table : appointments
+-------+---------------------+---------------------+-------------+----------------------------+
|    id |      start_datetime |        end_datetime | id_services |          service_timestamp |
+-------+---------------------+---------------------+-------------+----------------------------+
|     1 | 2024-03-19 11:30:00 | 2024-03-19 12:00:00 |           1 | 2038-01-18 21:14:07.999999 |
+-------+---------------------+---------------------+-------------+----------------------------+

E tentar atualizar service_timestamp para row_end da versão do serviço antes que a atualização quebre a restrição de chave estrangeira.

Eu gostaria de continuar usando o controle de versão do sistema MariaDB, pois acho que ele supera em muito qualquer coisa que eu mesmo pudesse criar, mas se houver outra maneira de registrar alterações nos serviços e recuperar certas versões deles, estou aberto a sugestões.

Existe uma maneira de criar uma restrição de chave estrangeira que faça referência a toda a tabela com versão do sistema, em vez de apenas à versão atual, de modo que, quando um registro de serviço for atualizado ou excluído, a chave estrangeira faça referência ao registro antes de ser atualizado ou excluído? Não tenho muita experiência com bancos de dados, então qualquer conselho é bem-vindo.

mariadb

2 respostas

  1. As tabelas de versão do sistema são um sistema de auditoria onde os registros anteriores são imutáveis. É a solução errada atualizá-los como um sistema de reservas.

    O que o MariaDB tem são períodos de tempo de aplicação sem sobreposições, que é o modelo perfeito para um sistema de reservas.

    CREATE TABLE `appointments` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `start_datetime` datetime DEFAULT NULL,
  `end_datetime` datetime DEFAULT NULL,
  `id_services` int(11) DEFAULT NULL,
  `service_timestamp` timestamp(6) NULL DEFAULT NULL,
  PERIOD FOR p(start_datetime,send_datetime),
  UNIQUE (id_services, p WITHOUT OVERLAPS)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

    As tabelas de versão do sistema ainda podem ser usadas, mas como um registro histórico de como era a reserva em um determinado momento. Pode ser útil renomear start_datetime/end_datetimepara booking_time_{start,end}para evitar conflitos de nome com tabelas de versão do sistema, se você fizer isso.

    • 0
  2. Best Answer

    A partir deste relatório de problema do jira e dessas linhas de código no repositório do github, parece que as linhas históricas são explicitamente ignoradas nas restrições FK, então acredito que o comportamento que desejo é impossível usando as tabelas com versão do sistema do MariaDB.

    Ainda estou procurando uma solução, mas como a intenção desta pergunta era ver se eu poderia implementar o snapshot usando especificamente as tabelas com versão do sistema , deixarei isso como resposta e encontrarei uma solução diferente, a menos que alguém caso contrário, você poderá encontrar uma maneira de contornar a verificação da linha histórica na restrição FK.

    EDITAR:

    Consegui obter um comportamento semelhante ao da tabela com versão do sistema e, ao mesmo tempo, fazer referência a uma versão histórica específica da linha de serviço no compromisso, criando uma tabela de "histórico" separada e colocando gatilhos na tabela de serviço da seguinte forma:

    CREATE TABLE `services_history` (
  `id` int(11) NOT NULL,
  `name` varchar(256) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `rs` timestamp(6) NOT NULL DEFAULT current_timestamp(6),
  `re` timestamp(6) NOT NULL DEFAULT from_unixtime(2147483647.999999),
   PRIMARY KEY (`id`,`re`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

DELIMITER $$
CREATE TRIGGER `services_delete` BEFORE DELETE ON `services` 
FOR EACH ROW 
UPDATE services_history
    SET re = CURRENT_TIMESTAMP
    WHERE id = OLD.id AND re = DEFAULT(re)
$$
CREATE TRIGGER `services_insert` AFTER INSERT ON `services` 
FOR EACH ROW 
INSERT INTO services_history(`id`, `name`, `duration`, `description`, `price`)
VALUES (NEW.id, NEW.name, NEW.duration, NEW.description, NEW.price)
$$
CREATE TRIGGER `services_update` BEFORE UPDATE ON `services` 
FOR EACH ROW BEGIN
UPDATE services_history
    SET re = CURRENT_TIMESTAMP
    WHERE id = NEW.id AND re = DEFAULT(re);

INSERT INTO services_history(`id`, `name`, `duration`, `description`, `price`)
VALUES (NEW.id, NEW.name, NEW.duration, NEW.description, NEW.price);
END
$$
DELIMITER ;

    Isso funciona do jeito que eu preciso, mas não usa controle de versão do sistema. Eventualmente, gostaria de configurá-lo de forma que o gatilho de atualização armazene apenas as colunas que foram alteradas para economizar espaço e criar uma visualização que reconstrua o serviço em um determinado momento, acumulando todas as alterações até aquele ponto.

    • 0

relate perguntas