PostGIS教程学习二十二：使用触发器追踪历史编辑操作

news/2024/7/9 22:58:39 标签: 学习, 数据库, sql, postgresql, 空间计算

PostGIS教程学习二十二：使用触发器追踪历史编辑操作

生产环境下数据库的一个常见要求是能够跟踪用户编辑数据的历史：数据在两个日期之间是如何变化的，是谁操作的，以及它们哪些内容变化了？一些GIS系统通过在客户端接口中包含更改管理功能来跟踪用户的编辑数据操作，但这增加了客户端编辑工具的复杂性。

使用数据库和数据库的触发器机制，可以对任何表进行编辑历史跟踪，从而让客户端保持对编辑表的简单“直接编辑”（客户端不用负责追踪编辑历史的功能，只负责CRUD）。

历史跟踪的工作方式是增加一个记录编辑历史的历史表，为每个编辑操作保留历史记录。历史表包含如下信息：

如果编辑表中创建了一条新记录，则保留新记录添加的时间、操作的用户。
如果编辑表中的一条记录被删除，保留记录被删除的时间、操作的用户。
如果编辑表中的一条记录被更新，则添加删除信息（针对旧状态）和创建信息（针对新状态）。

文章目录

  • PostGIS教程学习二十二：使用触发器追踪历史编辑操作
  • 一、创建历史表
  • 二、操作编辑表
    • 2.1、SQL编辑
  • 三、查找历史表
  • 四、参考资料

一、创建历史表

使用历史表的信息，可以恢复任何时间点编辑表的状态。在本例中，我们将针对nyc_streets表创建历史表，从而追踪nyc_streets表的编辑历史。

1）首先，添加一个新的nyc_streets_history表。这是我们将用来存储所有编辑历史信息的历史表。除了包含nyc_streets表的所有字段外，我们还为历史表增加了五个字段：

hid —— 历史表的主码
created —— 编辑表中对应记录被创建的时间
created_by ——编辑表中对应记录被创建的操作用户
deleted —— 编辑表中对应记录被删除的时间
deleted_by —— 编辑表中对应记录被删除的操作用户

sql">CREATE TABLE nyc_streets_history (
  hid SERIAL PRIMARY KEY,
  gid INTEGER,
  id FLOAT8,
  name VARCHAR(200),
  oneway VARCHAR(10),
  type VARCHAR(50),
  geom GEOMETRY(MultiLinestring,26918),
  created TIMESTAMP,		-- 时间戳（无时区)
  created_by VARCHAR(32),
  deleted TIMESTAMP,
  deleted_by VARCHAR(32)
);

2）接下来，我们将编辑表nyc_streets的当前状态导入到历史表中，这样我们就有一个可以追踪历史编辑的起点。注意，我们插入了创建时间（created）和创建用户（created_by），但删除相关信息为空。

sql">INSERT INTO nyc_streets_history
  (gid, id, name, oneway, type, geom, created, created_by)
   SELECT gid, id, name, oneway, type, geom, now(), current_user
   FROM nyc_streets;

3）现在，我们需要为编辑表创建三个触发器，用于插入、删除和更新操作。首先我们创建触发器函数（PostgreSQL的触发器动作体只能是函数），然后将它们作为触发器动作体绑定到表中。

对于INSERT操作，我们只需记录创建时间（created）和创建用户（created_by）信息并将该新记录添加到历史表中。

sql">CREATE OR REPLACE FUNCTION nyc_streets_insert() RETURNS trigger AS
$$
  BEGIN
    INSERT INTO nyc_streets_history
      (gid, id, name, oneway, type, geom, created, created_by)
    VALUES
      (NEW.gid, NEW.id, NEW.name, NEW.oneway, NEW.type, NEW.geom,
       current_timestamp, current_user);
    RETURN NEW;
  END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER nyc_streets_insert_trigger
AFTER INSERT ON nyc_streets
FOR EACH ROW 
EXECUTE PROCEDURE nyc_streets_insert();

对于DELETE操作，我们只要将对应的历史记录（且deleted字段是NULL）标记为已删除。

sql">CREATE OR REPLACE FUNCTION nyc_streets_delete() RETURNS trigger AS
$$
  BEGIN
    UPDATE nyc_streets_history
      SET deleted = current_timestamp, deleted_by = current_user
      WHERE deleted IS NULL and gid = OLD.gid;
    RETURN NULL;
  END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER nyc_streets_delete_trigger
AFTER DELETE ON nyc_streets
FOR EACH ROW 
EXECUTE PROCEDURE nyc_streets_delete();

对于UPDATE操作，我们首先将对应的历史记录标记为已删除，然后插入更新状态的新记录。

sql">CREATE OR REPLACE FUNCTION nyc_streets_update() RETURNS trigger AS
$$
  BEGIN

    UPDATE nyc_streets_history
      SET deleted = current_timestamp, deleted_by = current_user
      WHERE deleted IS NULL and gid = OLD.gid;

    INSERT INTO nyc_streets_history
      (gid, id, name, oneway, type, geom, created, created_by)
    VALUES
      (NEW.gid, NEW.id, NEW.name, NEW.oneway, NEW.type, NEW.geom,
       current_timestamp, current_user);

    RETURN NEW;

  END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER nyc_streets_update_trigger
AFTER UPDATE ON nyc_streets
FOR EACH ROW 
EXECUTE PROCEDURE nyc_streets_update();

二、操作编辑表

现在启用了历史表，我们可以对编辑表进行编辑，并在历史表中看到日志条目。

请注意这种数据库支持的追踪编辑历史的强大功能：无论使用什么工具进行编辑，比如SQL命令行、基于Web的JDBC工具，还是像QGIS这样的桌面工具，编辑历史都会被持续追踪。

2.1、SQL编辑

让我们把这两条名字叫做“Cumberland Walk”的街道改成更时髦的“Cumberland Wynde”：

sql">UPDATE nyc_streets SET name = 'Cumberland Wynde'
WHERE name = 'Cumberland Walk';

更新这两条街道将会把历史表中原来的街道标记为已删除，删除时间为现在，以及再添加具有新名称的两条新街道。

sql">SELECT hid, gid, name, created, created_by, deleted, deleted_by
FROM nyc_streets_history 
WHERE name = 'Cumberland Walk' OR name = 'Cumberland Wynde'
ORDER BY hid;

在这里插入图片描述

三、查找历史表

既然我们有了历史表，那还有什么用处呢？它对"时间旅行"很有用！要旅行到特定的时刻T（即查看T时刻历史表的状态），我们需要构造一个查询，查询出符合如下规则的记录：

所有在时刻T之前创建且目前尚未删除的记录。
所有在时刻T之前创建，但在T之后删除的记录。
我们可以使用这个逻辑来创建过去某个时刻T对应的数据状态的视图。假设我们的所有测试编辑都发生在过去30分钟内，那我们可以创建一个30分钟前历史表数据状态的视图：

– 30分钟前的历史表的数据状态
– 记录必须在30分钟前创建，并且现在没被删除（DELETED为NULL）
– 或在过去30分钟内已删除的

sql">CREATE OR REPLACE VIEW nyc_streets_thirty_min_ago AS
  SELECT * FROM nyc_streets_history
  WHERE created < (now() - '30min'::interval)
      AND ( deleted IS NULL OR deleted > (now() - '30min'::interval) );

我们还可以创建视图来显示被特定用户（比如用户postgres）编辑的记录：

sql">CREATE OR REPLACE VIEW nyc_streets_postgres AS
  SELECT * FROM nyc_streets_history
  WHERE created_by = 'postgres' OR deleted_by = 'postgres';

四、参考资料

PostgreSQL触发器官方文档
PostgreSQL过程化语言（PL/pgSQL）官方文档


http://www.niftyadmin.cn/n/5361449.html

相关文章

leetcode刷题(剑指offer) 103.二叉树的锯齿形层序遍历

leetcode刷题(剑指offer) 103.二叉树的锯齿形层序遍历

103.二叉树的锯齿形层序遍历 给你二叉树的根节点 root &#xff0c;返回其节点值的 锯齿形层序遍历 。&#xff08;即先从左往右&#xff0c;再从右往左进行下一层遍历&#xff0c;以此类推&#xff0c;层与层之间交替进行&#xff09;。 示例 1&#xff1a; 输入&#xff1a…
阅读更多...
基于BiLSTM-CRF对清华语料文本进行分类

基于BiLSTM-CRF对清华语料文本进行分类

安装TorchCRF !pip install TorchCRF1.0.6 构建BiLSTM-CRF # encoding:utf-8import torch import torch.nn as nn from TorchCRF import CRFfrom torch.utils.data import Dataset from sklearn.model_selection import train_test_split import numpy as npimport torch im…
阅读更多...
基于WordPress开发微信小程序2：决定开发一个wordpress主题

基于WordPress开发微信小程序2：决定开发一个wordpress主题

上一篇&#xff1a;基于WordPress开发微信小程序1&#xff1a;搭建Wordpress-CSDN博客 很快发现一个问题&#xff0c;如果使用别人的主题模板&#xff0c;多多少少存在麻烦&#xff0c;所以一咬牙&#xff0c;决定自己开发一个主题模板&#xff0c;并且开源在gitee上&#xff…
阅读更多...
寒假作业2月2号

寒假作业2月2号

第一章 命名空间 一&#xff0e;选择题 1、编写C程序一般需经过的几个步骤依次是&#xff08;C &#xff09; A. 编辑、调试、编译、连接 B. 编辑、编译、连接、运行 C. 编译、调试、编辑、连接 D. 编译、编辑、连接、运行 2、所谓数据封装就是将一组数据和与这组数据有关…
阅读更多...
SpringBoot使用Kafka详解含完整代码

SpringBoot使用Kafka详解含完整代码

1. 前言 随着大数据和实时处理需求的增长&#xff0c;Kafka作为一种分布式流处理平台&#xff0c;与Spring Boot的集成变得尤为重要。本文将详细探讨如何在Spring Boot应用程序中设置和使用Kafka&#xff0c;从基础概念到高级特性&#xff0c;通过实际代码示例帮助读者深入理解…
阅读更多...
STM32F407移植OpenHarmony笔记8

STM32F407移植OpenHarmony笔记8

继上一篇笔记&#xff0c;成功开启了littlefs文件系统&#xff0c;能读写FLASH上的文件了。 今天继续研究网络功能&#xff0c;让控制台的ping命令能工作。 轻量级系统使用的是liteos_m内核lwip协议栈实现网络功能&#xff0c;需要进行配置开启lwip支持。 lwip的移植分为两部分…
阅读更多...
方案：系统关闭时通过命令行创建support zip

方案：系统关闭时通过命令行创建support zip

文章目录 Environment and requirementsScript：JiraConfluence其他注意事项Environment and requirements The solution runs in Linux based Operating System with: /bin/bashzip and/or gzip applicationsTo run in Windows the OS might have: PowershellScript： Jira …
阅读更多...
024 增强for循环

024 增强for循环

什么是增强for循环 用法 int[] arr {1,3,5,7,9}; for (int i : arr) {System.out.println(i); } 拓展 增强for不止用于数组&#xff0c;还可以用于集合。 增强for是for的偷懒写法&#xff0c;必定可以被for的写法的代替&#xff0c;但for不一定会被增强for代替。
阅读更多...
最新文章

Copyright @ 2022~2023