PostgreSQL 枚举(enum)类型
1 创建删除枚举类型
drop type if exists attachs.efiletypes;
create type attachs.efiletypes as enum ('other', 'image', 'vedio','file');
2 枚举转换为整数,下标从1开始
drop cast if exists (attachs.efiletypes as integer);
drop function if exists enum2position(anyenum);
/****************************************************************************************
枚举转换为integer函数,下标从1开始
注意需要函烽必须带stable strict属性
****************************************************************************************/
create or replace function enum2position(anyenum)
returns integer
as $$
select array_position(enum_range($1), $1);
$$ language sql stable strict;
/****************************************************************************************
枚举转换为integer类型函数
int to enum select (enum_range(null::attachs.efiletypes))[1];
****************************************************************************************/
create cast (attachs.efiletypes as integer) with function enum2position(anyenum);
使用方法
select ('image'::attachs.efiletypes)::integer
3 整数转换为枚举,下标从1开始
注意:下标不能超过枚举值的数量
select ((enum_range(null::attachs.efiletypes))[1])
4 应用举例
例如发布图文信息时,如果设计为内容和图片一对多关系的话需要先保存内容,然后才能上传图片.如果其它文章也需要该图片的话需要再上传一次,由此带来几个缺点:
- 附件需要多次存储;
- 附件管理不方便,例如上传了一些不合法的附件,查找起来困难;
- 对使用者来说非常不方便,每次引用都需要上传一次图片,虽然可以用url方式,但url不便于记忆.
为克服上述问题,可以把附件设计为windows文件管理器的模式.使用者可以在自已建立附件分类(也可以叫目录,不建使用默认分类),把需要用到的附件先上传至服务器,使用时通过选择的方式播入附件,在编辑时插入和删除附件同时记录到附件关联性表中.
下面的为附件为图片时的表结构设计:
drop table if exists attachs.relations;
drop type if exists attachs.texternal;
drop type if exists attachs.trelation;
drop cast if exists (attachs.efiletypes as integer);
drop type if exists attachs.efiletypes;
/****************************************************************************************
附件类型枚举
****************************************************************************************/
create type attachs.efiletypes as enum ('other', 'image', 'vedio','file');
/****************************************************************************************
附件类型枚举转换为integer类型函数
int to enum select (enum_range(null::attachs.efiletypes))[1];
****************************************************************************************/
create cast (attachs.efiletypes as integer) with function enum2position(anyenum);
/****************************************************************************************
附件来源类型
****************************************************************************************/
create type attachs.trelation as(
id bigint, --附件编号,附件来源由type指出
type attachs.efiletypes --1:其它\2:图\3:视频\4文件
);
/****************************************************************************************
使用此附件的外部来源类型
****************************************************************************************/
create type attachs.texternal as(
featureid integer, --功能编号
targetid bigint --功能对应的记录编号
);
/****************************************************************************************
附件关联性
****************************************************************************************/
create table attachs.relations(
objectid bigserial not null, --唯一编号
external attachs.texternal not null, --使用此附件的外部来源
relation attachs.trelation not null, --附件内部来源
constraint pk_relations_objectid primary key(objectid) with (fillfactor=80) using index tablespace idxcore
)with (fillfactor=80,
autovacuum_enabled=true,toast.autovacuum_enabled=true,
autovacuum_vacuum_threshold=100,autovacuum_analyze_threshold=200,
toast.autovacuum_vacuum_threshold=100);
create index idx_relations_external on attachs.relations(((external).featureid),((external).targetid)) with (fillfactor=80) tablespace idxcore;
create index idx_relations_relation on attachs.relations(((relation).type),((relation).id)) with (fillfactor=80) tablespace idxcore;
/****************************************************************************************
删除外部来源时记得从附件关联性表中删除数据
drop function if exists attachs.relationRemove(integer,bigint[]);
****************************************************************************************/
create or replace function attachs.relationRemove(integer,bigint[])
returns void
as $$
delete from attachs.relations where ((external).featureid)=$1 and ((external).targetid)=any($2);
$$ language sql;
drop table if exists attachs.imgdatas;
drop table if exists attachs.images;
drop cast if exists (attachs.eimgtypes as integer);
drop type if exists attachs.eimgtypes;
/****************************************************************************************
图片类型枚举
****************************************************************************************/
create type attachs.eimgtypes as enum ('other', 'icon', 'gif','png','jpeg');
/****************************************************************************************
图片类型枚举转换为integer类型函数
int to enum select (enum_range(null::attachs.eimgtypes))[1];
****************************************************************************************/
create cast (attachs.eimgtypes as integer) with function enum2position(anyenum);
/****************************************************************************************
文件(上传的附件)扩展名和mimi类型表
本表是由程序自动维护的,不要人工操作
****************************************************************************************/
create table attachs.images(
objectid bigserial not null, --唯一编号
catalogid bigint not null, --每个人的附件分类(也可以叫目录,不建使用默认分类),这里就不贴了,大家根据自己的需求设计
imgtype attachs.eimgtypes not null, --文件类型
name text not null, --原始文件名(不含扩展名)
mime text not null, --mime文件类型
generate timestamptz not null, --上传时间
author attachs.tauthor not null, --作者
constraint pk_images_objectid primary key(objectid) with (fillfactor=80) using index tablespace idxcore,
constraint fk_images_catalogid foreign key(catalogid) references attachs.catalogs(objectid) on delete cascade
)with (fillfactor=80,
autovacuum_enabled=true,toast.autovacuum_enabled=true,
autovacuum_vacuum_threshold=100,autovacuum_analyze_threshold=200,
toast.autovacuum_vacuum_threshold=100);
create index idx_images_range on attachs.images(to_date(generate)) with (fillfactor=80) tablespace idxcore;
create index idx_images_catalog1 on attachs.images(((author).id),((author).type)) with (fillfactor=80) tablespace idxcore where 1=catalogid;
/****************************************************************************************
文件(上传的附件)原始数据存储表
本表是由程序自动维护的,不要人工操作
****************************************************************************************/
create table attachs.imgdatas(
objectid bigint not null, --唯一编号
data bytea not null, --二进制原始数据,实际应用时,上传是图片应该写在磁盘上(包括原始图和缩略图),数据库中存储的用途是当磁盘上的文件被删除时自动恢复
constraint pk_imgdatas_objectid primary key(objectid) with (fillfactor=80) using index tablespace idxcore,
constraint fk_imgdatas_objectid foreign key(objectid) references attachs.images(objectid) on delete cascade
)with (fillfactor=80,
autovacuum_enabled=true,toast.autovacuum_enabled=true,
autovacuum_vacuum_threshold=100,autovacuum_analyze_threshold=200,
toast.autovacuum_vacuum_threshold=100);