添加版本/历史系统到数据库表

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

---

想要改进这个问题？通过编辑这篇文章添加详细信息并澄清问题。

3 年前已关闭。

Answer 1

你说不想要一个“单独的修订表”，不投票给 FractalizeR 的解决方案，因为这个。好的，这是一个“单表解决方案”...但是，请简化/概括您的问题，以便为所有访问者提供更好的答案和更好地使用此页面：我认为您的问题与 SQL 表上的“修订控制”有关。

“ISO 2008 SQL”的解决方案，那么我认为它也适用于 Microsoft SQL-Server。我在 PostgreSQL 9.1 上测试了它。

在这种问题中，我们可以使用 SQL 视图来“模拟”原始表，并将“版本化表”作为新表，并具有更多属性：
* 一个新属性moment，用于对修订进行排序（排序）和时间注册；
* 用于“可追溯性”的新属性cmd（并非真正必要）。

假设您的原始（和常规）表是t。对于修订控制，您必须添加新属性，但其他程序员不需要看到这个新属性...解决方案是将表 t 重命名为 t_hist 并提供给其他程序员程序员可以使用 SQL VIEW t（作为对 t_hist 的查询）。

t 是一个用于显示常规表的视图：仅“当前元组”。 t_hist 是新表，包含“历史元组”。

假设 t 具有属性 a、b。
PS：在 t_hist 上，我添加了 isTop 以获得更好的 t 性能。

 -- ....
 CREATE TABLE  t_hist (
    -- the old attributes for t:
    id integer NOT NULL, -- a primary key of t
    a varchar(10),  -- any attribute
    b integer,      -- any attribute

    -- new attributes for revision control:
    isTop BOOLEAN NOT NULL DEFAULT true, -- "last version" or "top" indicator
    cmd varchar(60) DEFAULT 'INSERT',    -- for traceability
    moment timestamp NOT NULL DEFAULT now(), -- for sort revisions
    UNIQUE(id,moment)
);

CREATE VIEW t AS
  SELECT id,a,b FROM t_hist WHERE isTop;
   -- same, but better performance, as 
   -- SELECT id,a,b FROM t_hist GROUP BY id,a,b HAVING MAX(moment)=moment  

-- Verifies consistency in INSERT:
CREATE FUNCTION t_hist_uniq_trig() RETURNS TRIGGER AS $
DECLARE
  aux BOOLEAN;
BEGIN
   SELECT true INTO aux FROM t_hist 
   WHERE id=NEW.id AND moment>=NEW.moment;
   IF found THEN -- want removes from top?
     RAISE EXCEPTION 'TRYING TO INCLUDE (ID=%) PREVIOUS TO %', NEW.id, NEW.moment;
    END IF;
    RETURN NEW;
END  $ LANGUAGE plpgsql;
CREATE TRIGGER uniq_trigs BEFORE INSERT ON t_hist 
    FOR EACH ROW EXECUTE PROCEDURE t_hist_uniq_trig();   

CREATE FUNCTION t_reset_top(integer)  RETURNS BOOLEAN AS $BODY$
    UPDATE t_hist SET isTop=false WHERE isTop=true AND id=$1
    RETURNING true;  -- null se nao encontrado
$BODY$ LANGUAGE sql;

--------
-- Implements INSER/UPDATE/DELETE over VIEW t, 
-- and controls unique id of t:
CREATE OR REPLACE FUNCTION t_cmd_trig() RETURNS TRIGGER AS $
DECLARE
  aux BOOLEAN;
BEGIN
  aux:=true;
  IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
    aux := t_reset_top(OLD.id); -- rets. true ou NULL
  ELSE
    SELECT true INTO aux FROM t_hist WHERE id=NEW.id AND isTop;
  END IF;
  IF (TG_OP='INSERT' AND aux IS NULL) OR (TG_OP='UPDATE' AND aux) THEN
    INSERT INTO t_hist (id,a,b,cmd) VALUES (NEW.id, NEW.a,NEW.b,TG_OP);
  ELSEIF TG_OP='DELETE' AND aux THEN -- if first delete
    UPDATE t_hist SET cmd=cmd||' AND DELETE AT '||now()
  ELSEIF TG_OP='INSERT' THEN -- fails by not-unique(id)
    RAISE EXCEPTION 'REGISTER ID=% EXIST', NEW.id;
  ELSEIF TG_OP='UPDATE' THEN -- .. redundance, a trigger not goes here
    RAISE EXCEPTION 'REGISTER ID=% NOT EXIST', NEW.id;
  END IF;
  RETURN NEW; -- discarded
END
$ LANGUAGE plpgsql;
CREATE TRIGGER ins_trigs INSTEAD OF INSERT OR UPDATE OR DELETE ON t 
    FOR EACH ROW EXECUTE PROCEDURE t_cmd_trig();

--  Examples:
INSERT INTO t(id,a,b) VALUES (1,'aaaaaa',3); -- ok
INSERT INTO t(id,a,b) VALUES (1,'bbbbbb',3); -- error
UPDATE t_hist SET a='teste' WHERE id=1;      -- ok
     -- SELECT * from t;        SELECT * from t_hist;
INSERT INTO t(id,a,b) VALUES 
  (2,'bbbbbb',22), -- ok
  (3,'bbbbbb',22), -- ok
  (4,'aaaaaa',2);  -- ok
DELETE FROM t WHERE id=3;
     -- SELECT * from t;        SELECT * from t_hist;

PS：我建议不要尝试将此解决方案应用于没有视图的一张表，您的触发器将非常复杂；也不尝试适应 t_hist 继承 t，其中插入 t_hist 的所有内容都将复制到 t。