sql 中视图的创建和rule规则的创建使用

梦里梦外; 2023-10-04 17:06 46阅读 0赞

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAMTAyNOWwj-elng_size_20_color_FFFFFF_t_70_g_se_x_16][]

select *
    from metro_xml_data;
    
    -- 主键唯一
    create table if not exists students
    (
        id   serial primary key,
        name varchar(50),
        age  int,
        home varchar(50),
        info jsonb
    );
    
    drop table students;
    
    insert into students(name, age, home, info)
    values ('wang', 19, 'luoyang', '{
      "c_xm": "张三",
      "c_mx": {
        "c_ssdw": "一大队",
        "c_dwbm": "11"
      }
    }')
    on conflict (id) do nothing;
    
    select *
    from students
    where info -> 'c_mx' ->> 'c_dwbm' = '11';
    
    select *
    from students
    where info -> 'c_mx' ->> 'c_ssdw'::varchar = '一大队';
    
    -- upsert方法
    insert into students(name, age, home, info)
    values ('zhu', 80, 'zhengzhou', '{
      "c_xm": "张三",
      "c_mx": {
        "c_ssdw": "一大队",
        "c_dwbm": "11"
      }
    }')
    on CONFLICT (id) DO UPDATE
        SET (name, age)= ('song', 18);
    
    update students
    set name = 'zhu'
    where id = 101;
    
    create table ay_json_test
    (
        id         varchar primary key,
        name       varchar,
        json_value json
    );
    
    insert into ay_json_test
    values ('002', 'ay', '{
      "ay_name": "王健林",
      "home": {
        "type": {
          "interval": "5m"
        },
        "love": "now",
        "you": "None"
      },
      "values": {
        "event": [
          "cpu_r",
          "cpu_w"
        ],
        "data": [
          "cpu_r"
        ],
        "threshold": [
          1,
          1
        ]
      },
      "objects": {
        "al": "beauty"
      }
    }');
    
    -- -> 取出来还是json，->>取出来是值, ::varchar是转位varchar类型
    select id, name, json_value -> 'home' as ayName
    from ay_json_test
    where json_value -> 'home' ->> 'you'::varchar = 'None';
    
    -- 更新所有字段
    explain
    update ay_json_test
    set json_value = '
    {
      "ay_name": "王思聪",
      "home": {
        "type": {
          "interval": "5m"
        },
        "love": "now",
        "you": "None"
      },
      "values": {
        "event": [
          "cpu_r",
          "cpu_w"
        ],
        "data": [
          "cpu_r"
        ],
        "threshold": [
          1,
          1
        ]
      },
      "objects": {
        "al": "beauty"
      }
    }'
    where json_value ->> 'ay_name' = '王健林';
    
    -- 建立rule规则
    CREATE TABLE public.origin_data
    (
        id             serial,
        extra          jsonb NOT Null DEFAULT '{}'::jsonb,
        extra_modified JSONB NOT NULL DEFAULT '{}'::jsonb,
        covered        BOOLEAN        DEFAULT false
    );
    
    
    -- 练习使用pg的rule规则:cascade 集联操作，删除此表的同时，把相关联的表也删除
    drop table if exists public.origin_data cascade;
    
    --- origin data table
    CREATE TABLE public.origin_data
    (
        id             SERIAL,
        extra          JSONB NOT NULL DEFAULT '{}'::jsonb,
        extra_modified JSONB NOT NULL DEFAULT '{}'::jsonb,
        covered        BOOLEAN        DEFAULT false
    );
    
    
    --- shadow data table
    drop table if exists public.shadow_data cascade;
    --- in shadow table, we record only modified rows
    CREATE TABLE public.shadow_data
    (
        id             SERIAL,
        extra          JSONB NOT NULL DEFAULT '{}'::jsonb,
        extra_modified JSONB NOT NULL DEFAULT '{}'::jsonb,
        covered        BOOLEAN        DEFAULT false
    );
    
    
    --- read could be easily handled
    -- CREATE VIEW 创建视图表的名称，AS为映射表的查询语句，可以是多个字表的查询结果，
    -- UNION 可以将两个表的数据做一个汇总，但是两个表要有相同的列，值类型也要相同
    
    -- 创建一个视图，
    CREATE VIEW shadow_view AS
    select *
    from origin_data
    where covered is False
    UNION
    select *
    from shadow_data;
    
    
    INSERT INTO origin_data (extra)
    VALUES ('{
      "a": 2,
      "b": 2
    }'),
           ('{
             "a": 10,
             "b": 20,
             "c": 0
           }');
    
    
    INSERT INTO shadow_data (extra)
    VALUES ('{
      "a": 3,
      "b": 3
    }'),
           ('{
             "a": 30,
             "b": 30,
             "c": 0
           }');
    
    -- 此位数据源表
    SELECT *
    FROM origin_data;
    -- 此视图表只允许读的
    SELECT *
    FROM shadow_view;
    -- 此位数据备份表
    SELECT *
    FROM shadow_data;
    
    
    --- update process that we expected
    UPDATE shadow_data
    SET extra   = '{
      "a": 100
    }',
        covered = true
    WHERE extra ->> 'a' = '30';
    
    -- 1. know from the condition which rows is updated
    -- 2. insert into shadow_data if the row do not exists
    -- 3. update the row in shadow_data if the row already exists
    -- 4. update the row in origin and set it to "COVERED"
    
    -- 1. we could create view, even it is not updatable view
    CREATE VIEW shadow_view_update AS
    select *
    from origin_data
    where covered is False
    UNION
    select *
    from shadow_data;
    
    -- not an updatable view
    UPDATE shadow_view_update
    SET extra = '{
      "a": 2
    }'
    WHERE extra ->> 'a' = '1';
    
    -- 2-4 create rule;DO INSTEAD 代替执行别的操作，而不执行本操作
    -- 创建一个更新视图的操作规则，用别的动作代替执行
    CREATE OR REPLACE RULE update_shadow_rule AS
        ON UPDATE TO shadow_view_update
        DO INSTEAD
        (
        --- 3. update the row in shadow_data if the row already exists
        UPDATE shadow_data
        SET extra          = extra || NEW.extra,
            extra_modified = extra_modified || NEW.extra
        WHERE id = NEW.id;
    
        --- 2. insert if row do not exists
        -- 如果是新数据，就执行插入操作
        INSERT INTO shadow_data
            (id, extra, extra_modified)
        SELECT DISTINCT id,
                        extra || NEW.extra,
                        NEW.extra AS extra_modified
        FROM origin_data
        WHERE id = NEW.id
          AND covered is false;
    
        --- 4. update the row in origin to "COVERED"
        --- 对id匹配的数据，只更新covered
        UPDATE origin_data
        SET covered = true
        WHERE id = NEW.id
          and covered = false;
        );
    
    select *
    from shadow_view_update;
    
    --- update process that we expected
    UPDATE shadow_view_update
    SET extra = '{
      "a": 11
    }'
    WHERE extra ->> 'a' = '10';
    
    SELECT *
    FROM origin_data;
    SELECT *
    FROM shadow_view;
    select *
    from shadow_data;
    
    
    --- a second update
    UPDATE shadow_view_update
    SET extra = '{
      "a": 3
    }'
    WHERE extra ->> 'a' = '2';
    
    --- finally, sync origin change
    -- new 和 old是数据库行处理时候的新旧数据的行记录；
    -- 当更新origin_data表数据的同时，do also 也做更新shadow_data表
    create or replace rule sync_update_origin as on
        update to origin_data
        where new.extra <> old.extra
        do also
        update shadow_data
        set extra = NEW.extra || extra_modified
        where id = NEW.id;
    
    --- try update origin
    UPDATE origin_data
    SET extra = '{
      "a": 1,
      "add one more": 1
    }'
    WHERE extra ->> 'a' = '1';
    
    SELECT *
    FROM origin_data;
    SELECT *
    FROM shadow_view;
    
    -- potential problems
    -- 1. update origin data -> update all related rows
    -- 2. handle shadow on shadow

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAMTAyNOWwj-elng_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/c2fdf639f39a4a2c989c0006cf30341a.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAMTAyNOWwj-elng==,size_20,color_FFFFFF,t_70,g_se,x_16