PostgreSQL 字典表设计
PostgreSQL 字典表设计
字典常用于下拉列表选择等,直接在程序中写死不太灵活,因此经常将字典数据放到数据库中,通过后台可以方便的添加/修改/删除字典,前端选择模块中的数据也会相应的发生变化.
字典存储在数据库中也会带来一些问题:
1.有些字典数据非常少(比如是和否),不值得为每一类字典都建一张表,可以将所有字典数据合并在一张表里;
2.应用数据中一般保存的是字典id,使用时根据id到字典表中查询相应的的名称或其它相关数据,但是当字典表发生变化时(例如名称改变),在变更之后的新数据没有问题,但是历史数据会变成新的名称.虽然可以在应用数据中采用直接保存字典名的方法保证历史数据不发生变化,但是使用字典名会带来存储增大/检索速度变慢等问题,权衡下来还是建议采用id的方式保存.
在实际应用中可以结合触发器为避免上述问题,以下为示例:
本文重点
触发器函数返回值分为NEW、OLD、NULL三种,使用时要注意以下规则:
- insert动作时只有NEW;
- update动作时包含OLD、NEW.其中OLD表示更新前的数据,NEW表示更新的数据;
- delete时只有OLD;
- 触发器函数返回值为NULL时,如果触发器是在before时,不会插入、更新、删除数据.如果是在after时不会有任何操作和没有触发器效果一样;
- 假设触发表为a,在触发器函数中如果要update a表中的值,直接设置NEW.field=新值,然后返回New即可,同时要将触发器设置为before,在更新之前触发;
- 配置完成后,就和普通表一样操作(新增\删除\修改),但是保证所有数据都是存在的.
1.创建分类表
分类表不是必须的,直接使用字典表也可以达到相同的功能.创建分类表的目的是降低程序的复杂程度,方便管理.
drop function if exists catalogs_save(bigint,bigint,text,integer,text);drop trigger if exists tri_del_catalogs on catalogs;
drop function if exists trif_del_catalogs();drop function if exists catalogs_ischilds(bigint);drop table if exists catalogs;
/****************************************************************************************分类表不允许用户添加分类,分类由开发人员添加本表数据量预计<10000,因此只要主键索引catalogs表不允许物理删除,删除时将revoke改为true如果删除的节点包含子类则不允许删除
drop table if exists catalogs;
****************************************************************************************/
create table catalogs(objectid bigint not null, --唯一编号,手动设置parentid bigint not null, --父级编号,指向本表的objectid,没有父级设置为0(递归)name text not null, --名称describe text, --备注sort integer not null, --排序,相对于parentid的排序category integer not null, --类别,用于取不同的目录(1:表示字典目录,2表示标准目录,3表示xx,具体由开发人员自己定义)revoke boolean default(false) not null, --是否作废 constraint pk_catalogs_objectid primary key(objectid) with (fillfactor=80)
)with (fillfactor=80,autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=300,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=300);
/****************************************************************************************检查是否包含子类函数
drop function if exists catalogs_ischilds(bigint);
****************************************************************************************/
create or replace function catalogs_ischilds(bigint
) returns boolean
as $$with recursive cte(objectid,parentid) as(select t1.objectid,t1.parentid from catalogs t1 where t1.parentid=$1 and revoke=falseunion allselect t2.objectid,t2.parentid from catalogs t2 inner join cte on t2.parentid=cte.objectidwhere revoke=false),childs as(select count(*) as icount from cte)select (case when icount > 1 thentrueelsefalseend) from childs;
$$ language sql strict;
/****************************************************************************************当删除数据里,通过触发器取消删除动作(返回NULL)同时将是否作废标志改为ture,表示已经作废操作和普通表一样示例: delete from catalogs where objectid=1;不删除编号为1的记录,但是将编号为1记录作废状态修改为true如果编号为1的记录包含有子记录,则抛出异常提示,必须先删除子节录
drop trigger if exists tri_del_catalogs on catalogs;
drop function if exists trif_del_catalogs();
****************************************************************************************/
create or replace function trif_del_catalogs()
returns trigger as $$declarebegin if (TG_OP = 'DELETE' and OLD.objectid is not null) thenif catalogs_ischilds(OLD.objectid) thenraise exception '分类编号"%"包含有下级节点,不能删除!',OLD.objectid;end if;update catalogs set revoke=true where objectid=OLD.objectid;end if;return NULL; end;
$$ language 'plpgsql' SECURITY DEFINER;create trigger tri_del_catalogs before delete on catalogs for each row execute procedure trif_del_catalogs();/****************************************************************************************新增分类
drop function if exists catalogs_save(bigint,bigint,text,integer,text);
****************************************************************************************/
create or replace function catalogs_save(iobjectid bigint,iparentid bigint,iname text,icategory integer default 1,idescribe text default null
) returns bigint
as $$declarev_id bigint;v_count bigint;beginif( (select 1 from catalogs where objectid=iobjectid) is not null ) thenraise exception '分类编号"%"已经存在,请使用其它编号!',iobjectid;end if;select count(*) into v_count from catalogs where parentid=iparentid;if( 0 = v_count ) thenv_count := 1;elsev_count := v_count + 1;end if;insert into catalogs(objectid,parentid,name,describe,category,sort) values(iobjectid,iparentid,iname,idescribe,icategory,v_count) returning objectid into v_id;return v_id;end;
$$ language plpgsql;--具体层级没有限制,可以无限递归下去
do $$declare v_parentid bigint;declare v_parentid1 bigint;declare v_parentid2 bigint;
beginv_parentid :=catalogs_save(1,0,'xxx主类');v_parentid1:=catalogs_save(2,v_parentid,'xxx子类1');perform catalogs_save(3,v_parentid,'xxx子类2');perform catalogs_save(4,v_parentid1,'xxx子类1的子类');perform catalogs_save(5,0,'是否缴费');perform catalogs_save(6,0,'计量单位');
end$$;--查询指定的分类数据
select objectid,parentid,name,describe from catalogs where category=1 and revoke=false order by parentid,sort在程序中的实际效果图:
2.创建字典表和历史字典表
为避免字典数据发生改变里对引用数据的影响问题,同时考虑到字典数据可能会比较多,因此需要创建两个表,一个表为正常使用字典表,另一个为字典历史数据表(任何改变的数据均在这个表里).
注意两个表要一模一样,但是不要使用继承的方式,因为继承稍不注意就会删除两个表的数据,它们之间通过触发器来关联.
drop function if exists dictionarys_name(bigint);
drop function if exists dictionarys_save(bigint,bigint,text,text);drop trigger if exists tri_chadel_dictionarys on dictionarys;
drop function if exists trif_chadel_dictionarys();drop trigger if exists tri_disable_dicthistorys on dicthistorys;
drop function if exists trif_disable_dicthistorys();drop table if exists dicthistorys;
drop table if exists dictionarys;
drop sequence if exists seqdictionarys;
drop type if exists tdictionarys;/****************************************************************************************字典表(平时我们使用这个表)考虑到经常发生变化,因此将字典表页填充率设置为60%同时禁用autovacuum系数,当表中300个元组(Tuple)发生变化是自动触发autovacuum修改和删除字典表时,将历史数据转移到字典历史数据表中,通过触发器控制
drop table if exists dictionarys;
****************************************************************************************/
create table dictionarys(objectid bigserial not null, --唯一编号，主键parentid bigint not null, --分类编号,外键name text not null, --名称describe text, --备注sort integer not null, --排序(在本类别中从1开始)constraint pk_dictionarys_objectid primary key(objectid) with (fillfactor=60),constraint fk_dictionarys_parentid foreign key(parentid) references catalogs(objectid) on delete cascade
)with (fillfactor=80,autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=300,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=300);
create index idx_dictionarys_parentid on dictionarys(parentid) with (fillfactor=60);/****************************************************************************************字典历史数据表此表只能新增,禁止更新和删除数据,因此填充率为100%(默认值)同时禁用autovacuum系数,当表中300个元组(Tuple)发生变化是自动触发autovacuum本表是通过触发器操作的,不要人为操作这个表
drop table if exists dicthistorys;
****************************************************************************************/
create table dicthistorys(objectid bigint not null, --唯一编号，主键parentid bigint not null, --分类编号,外键name text not null, --名称describe text, --备注sort integer not null, --排序constraint pk_dicthistorys_objectid primary key(objectid) with (fillfactor=100),constraint fk_dicthistorys_parentid foreign key(parentid) references catalogs(objectid) on delete cascade
)with (fillfactor=100,autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=300,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=300);
create index idx_dicthistorys_parentid on dicthistorys(parentid) with (fillfactor=100);3.通过触发器关联表
注意在文章开头时的重点内容
3.1创建自定义类型
因为在触器的insert里里不能直接用NEW关键字,因此先创建一个类型
/****************************************************************************************字典表序列--注意除了主键(因为自增原因)其它字段名称和类型必须和表一模一样
drop type if exists tdictionarys;
****************************************************************************************/
create type tdictionarys as (parentid bigint,name text,describe text,sort integer
);3.2创建主表触发器
/****************************************************************************************修改和删除时将数据保存到历史表
drop trigger if exists tri_chadel_dictionarys on dictionarys;
drop function if exists trif_chadel_dictionarys();
****************************************************************************************/
create or replace function trif_chadel_dictionarys()
returns trigger as $$declarev_id bigint; --获取自增时的编号v_row tdictionarys;beginif TG_OP = 'UPDATE' thenif( OLD.objectid is not null ) then--不更新数据,直接新增一行v_row := row(NEW.parentid,NEW.name,NEW.describe,NEW.sort);insert into dictionarys(parentid,name,describe,sort)values(v_row.parentid,v_row.name,v_row.describe,v_row.sort) returning objectid into v_id;--删除待更新的数据,删除时会再次触发DELETE,历史数据会在DELETE中新增,因此这里不需要写历史数据新增脚本delete from dictionarys where objectid=OLD.objectid;end if;return NULL; --触发器是before,返回NULL则不更新数据(psql message受影响的行数显示为0,实际已经新增了一行)elsif TG_OP = 'DELETE' thenif( OLD.objectid is not null) then --保存到历史数据表后删除insert into dicthistorys(objectid,parentid,name,describe,sort)values(OLD.objectid,OLD.parentid,OLD.name,OLD.describe,OLD.sort);return OLD;elsereturn NULL;end if;end if;end;
$$ language 'plpgsql' SECURITY DEFINER;create trigger tri_chadel_dictionarys before update or delete on dictionarysfor each row execute procedure trif_chadel_dictionarys();3.3创建历史数据表触发器
历史数据表触发器作用是禁止修改和删除历史数据.
/****************************************************************************************禁止修改字典数据历史表
drop trigger if exists tri_disable_dicthistorys on dicthistorys;
drop function if exists trif_disable_dicthistorys();
****************************************************************************************/
create or replace function trif_disable_dicthistorys()
returns trigger as $$declarebeginif (TG_OP = 'UPDATE' ) thenraise exception '禁止修改字典数据历史表';elsif ( TG_OP = 'DELETE' ) thenraise exception '禁止修改字典数据历史表';end if;return NULL;end;
$$ language 'plpgsql' SECURITY DEFINER;create trigger tri_disable_dicthistorys before update or delete on dicthistorysfor each row execute procedure trif_disable_dicthistorys();4.操作函数
4.1保存字典数据
/****************************************************************************************保存字典
drop function if exists dictionarys_save(bigint,bigint,text,text);
****************************************************************************************/
create or replace function dictionarys_save(iobjectid bigint,iparentid bigint,iname text,idescribe text default null
) returns bigint
as $$declarev_id bigint;v_change integer;begin/*检查关键字是否存在*/if( iobjectid is null or (select 1 from dictionarys where objectid=iobjectid) is null ) thenif( catalogs_ischilds(iparentid) ) thenraise exception '字典分类编号"%"包含有下级节点,不能添加!',iparentid;end if;insert into dictionarys(parentid,name,describe,sort) values(iparentid,iname,idescribe,(select (count(*)+1) from dictionarys where parentid=iparentid)) returning objectid into v_id; else--因为更新在触发器里新增了一行,并且触发器返回NULL取消更新,因此取不到objectid的值--所以直接返回NULL,程序里要注意检查update dictionarys set name=iname,describe=idescribewhere objectid=iobjectid;return NULL;end if;return v_id;end;
$$ language plpgsql;4.2根据ID获取字典数据名称
/****************************************************************************************获取指定的字典名称
drop function if exists dictionarys_name(bigint);
****************************************************************************************/
create or replace function dictionarys_name(bigint)returns text
as $$with cte as(select name from dictionarys where objectid=$1union allselect name from dicthistorys where objectid=$1)select name from cte limit 1;
$$ language sql strict immutable;5.导入测试数据
--计量单位
do $$declare v_parentid bigint;
beginv_parentid := 4;perform dictionarys_save(null,v_parentid,'%',null);perform dictionarys_save(null,v_parentid,'g/l',null);perform dictionarys_save(null,v_parentid,'kcal/㎏',null);perform dictionarys_save(null,v_parentid,'㎏',null);perform dictionarys_save(null,v_parentid,'㎏/㎡',null);perform dictionarys_save(null,v_parentid,'kj/㎏',null);perform dictionarys_save(null,v_parentid,'min',null);perform dictionarys_save(null,v_parentid,'MPa',null);perform dictionarys_save(null,v_parentid,'㎜',null);perform dictionarys_save(null,v_parentid,'吨',null);
end$$;--是否缴费
do $$declare v_parentid bigint;
beginv_parentid := 5;perform dictionarys_save(null,v_parentid,'是',null);perform dictionarys_save(null,v_parentid,'否',null);
end$$;6.测试
--查看字典表和字典历史数据表
select * from dictionarys;
select * from dicthistorys;
--更新是否缴费中的是为xxx
update dictionarys set name='xxx' where objectid=11;
--删除是否缴费中的否
delete from dictionarys where objectid=12;
--更新字典历史数据表,抛出异常
update dicthistorys set name='a';
--删除字典历史数据表,抛出异常
delete from dicthistorys;
--查询objectid为12的字典名称(已经删除)
select dictionarys_name(12)