建表,改表 辅助

  1. drop table if exists h.ddl;
  2. create table h.ddl(
  3.     id bigserial primary key ,
  4.     schemaName varchar not null,
  5.     tableName varchar not null,
  6.     columnName varchar not null,
  7.     columnType varchar not null,
  8.     position bigint not null,
  9.     checkRule varchar not null,
  10.     comment varchar not null,
  11.     defaultValue varchar not null,
  12.     primaryKey bool not null default  false,
  13.     remark jsonb not null default '{}'
  14. );
  16. alter table h.ddl add unique (schemaname,tablename,columnName);
  18. create or replace function h.ddl_beforeInsert() returns trigger language plpgsql as $$
  19.     begin
  20.         if new.position is null then
  21.             new.position = (select count(1) from h.ddl where (schemaname,tablename)=(new.schemaname,new.tablename));
  22.         end if;
  23.         return new;
  24.     end;
  25. $$;
  27. create trigger beforeInsert before insert on h.ddl for row execute procedure h.ddl_beforeInsert();
  30. insert into h.ddl(id,schemaName,tablename,columnName,columnType,position,checkrule,comment,defaultvalue,primarykey) values
  31. (-1,'public','test','id','bigint',0,'','', '',true)
  32. ,(-2,'public','test','c1','bigint',0,'','', '',false)
  33. ,(-3,'public','test','c2','bigint',0,'','', '',false);
  35. create or replace function h.ddl_createTable(v_schemaName varchar,v_tableName varchar) returns void language plpgsql as $$
  36.     declare  sql varchar;
  37.     begin
  38.         select string_agg(s,E'') into sql from (select format('create table %s.%s(', v_schemaName,v_tableName) union all
  39.                        select * from (select format('%s %s not null %s constraint %s_%s_check check(%s),',columnname,columntype, case defaultvalue when '' then '' else 'default '||defaultvalue end, tablename,columnname, case checkrule when '' then 'true' else checkrule end) from h.ddl where (schemaname,tablename) =(v_schemaName,v_tableName) order by position) t  union all
  40.                        select format('primary key(%s)',(select string_agg(columnname,',') from h.ddl where (schemaname,tablename,primarykey) =(v_schemaName,v_tableName,true))) union all
  41.        select ');') t(s) ;
  42.         raise notice '%',sql;
  43.         execute sql;
  44.     end;
  45. $$;
  46. select h.ddl_createTable('public','test');
  48. create or replace function h.ddl_renameColumn( v_schemaName varchar,v_tableName varchar,v_columnName varchar,v_newName varchar) returns jsonb language plpgsql as $$
  49.     declare
  50.         old_it h.ddl = (select ddl from h.ddl where (ddl.schemaName,ddl.tableName,ddl.columnName) = (v_schemaName,v_tableName,v_columnName)) ;
  51.         sql varchar;
  52.     begin
  53.         if old_it is null then return jsonb_build_object('code',1,'msg','该字段不存在'); end if;
  54.          update  h.ddl set columnName =v_newName where id = old_it.id;
  55.         sql = format('alter table %s.%s rename column %s to %s;',v_schemaName,v_tableName,v_columnName,v_newName);
  56.         execute sql;
  57.         return jsonb_build_object('code',0);
  58.     end;
  59. $$;
  61. select h.ddl_renameColumn('public','test','pk','id');
  63. create or replace function h.ddl_reCreateTable(v_schemaName varchar,v_tableName varchar) returns void language plpgsql as $$
  64.     declare sql varchar;
  65.     begin
  66.         sql = sql || (select string_agg(s,'') from (
  67.            select format('drop table if exists %s.%s__xxx;',v_schemaName,v_tableName) union all
  68.            select string_agg(s,E'')  from (select format('create table %s.%s__xxx(', v_schemaName,v_tableName) union all
  69.                                            select string_agg(format('%s %s not null %s check(%s),',columnname,columntype,case defaultvalue when '' then '' else 'default '||defaultvalue end,case checkrule when '' then 'true' else checkrule end),'' order by position) from h.ddl where (schemaname,tablename) =(v_schemaName,v_tableName)  union all
  70.                                            select format('primary key(%s)',(select string_agg(columnname,',') from h.ddl where (schemaname,tablename,primarykey) =(v_schemaName,v_tableName,true))) union all
  71.                                            select ');') t(s) union all
  72.            select format('insert into %s.%s__xxx select %s from %s.%s;',v_schemaName,v_tableName,(select string_agg(columnname||'::'||ddl.columntype,',' order by position) from h.ddl where (schemaname,tablename) = (v_schemaName,v_tableName)),v_schemaName,v_tableName) union all
  73.            select format('drop table if exists %s.%s;alter table %s.%s__xxx rename to %s;',v_schemaName,v_tableName,v_schemaName,v_tableName,v_tableName)
  74.        ) t(s));
  75.     end;
  76. $$;
  78. create or replace function h.ddl_modifyColumn(jsonb) returns jsonb  language plpgsql as $$
  79.     declare
  80.         it h.ddl = jsonb_populate_record(null::h.ddl,$1);
  81.         old_it h.ddl = (select ddl from h.ddl where (schemaName,tableName,columnName) = (it.schemaName,it.tableName,it.columnName)) ;
  82.         sql varchar = '';
  83.     begin
  84.         if old_it is null then return jsonb_build_object('code','1','msg','该字段不存在'); end if;
  85.         --
  86.         if it.columnType is not null then
  87.             update  h.ddl set columnType =it.columnType where id = old_it.id;
  88.             sql  = sql||format('alter table %s.%s alter column %s type %s using %s::%s;',it.schemaName,it.tableName,it.columnName,it.columnType,it.columnName,it.columnType);
  89.         end if;
  90.         --
  91.         if it.checkRule is not null then
  92.             update  h.ddl set checkRule =it.checkRule where id = old_it.id;
  93.             -- 删除check
  94.             sql  = sql|| format('alter table %s.%s drop constraint if exists %s_%s_check;',it.schemaName,it.tableName,it.tableName,it.columnName);
  95.             if it.checkRule != '' then
  96.                 -- 新增check
  97.                 sql  = sql|| format('alter table %s.%s add  check (%s) no inherit;',it.schemaName,it.tableName,it.checkRule);
  98.             end if;
  99.         end if;
  100.         --
  101.         if it.comment is not null then
  102.             update  h.ddl set comment =it.comment where id = old_it.id;
  103.             sql = sql||format('comment on column  %s.%s.%s is ''%s'';',it.schemaName,it.tableName,it.columnname,it.comment);
  104.         end if;
  105.         --
  106.         if it.defaultValue is not null then
  107.             update h.ddl set defaultValue = it.defaultValue where id = old_it.id;
  108.             sql = sql||format('alter table %s.%s alter column  %s set default ''%s'';',it.schemaName,it.tableName,it.tableName,it.defaultValue);
  109.         end if;
  110.         --
  111.         if it.primarykey is not null then
  112.             update h.ddl set primaryKey = it.primaryKey where id = old_it.id;
  113.             -- 删除原主键
  114.             sql = sql||format('alter table %s.%s drop constraint if exists %s_pkey;',it.schemaName,it.tableName,it.tableName);
  115.             -- 创建新主键
  116.             if it.primarykey then
  117.                 sql = sql||format('alter table %s.%s add constraint %s_pkey primary key (%s);',it.schemaName,it.tableName,it.tableName
  118.                     ,(select string_agg(columnname,',') from h.ddl where (schemaname,tablename,primarykey) =(it.schemaName,it.tableName,true)));
  119.             end if;
  120.         end if;
  121.         -- 修改了position, 会重新建表
  122.         if it.position is not null then
  123.             update  h.ddl set position = it.position where id = old_it.id;
  124.             -- update  h.ddl set position = it.position where (schemaName,tableName,columnName) = (it.schemaName,it.tableName,it.columnName);
  125.             perform h.ddl_reCreateTable(it.schemaName,it.tableName);
  126.         end if;
  128.         raise notice '%',sql;
  129.         execute  sql;
  130.         return jsonb_build_object('code',0);
  131.     end;
  132. $$;
  133. select h.ddl_modifyColumn('{"schemaname":"public","tablename": "test","columnname": "id","primarykey": false}'::jsonb);
  134. select h.ddl_modifyColumn('{"schemaname":"public","tablename": "test","columnname": "c1","columntype": "varchar","position": 2,"checkrule": "c1::int<10","primarykey": true}'::jsonb);
  135. select h.ddl_modifyColumn('{"schemaname":"public","tablename": "test","columnname": "c1","columntype": "varchar","primarykey": true}'::jsonb);
  136. select h.ddl_modifyColumn('{"schemaname":"public","tablename": "test","columnname": "c1","columntype": "varchar","comment": "注释"}'::jsonb);
  138. select h.ddl_modifyColumn('{"schemaname":"public","tablename": "test","columnname": "id","position": 0,"comment": "主键"}'::jsonb);
  139. select h.ddl_modifyColumn('{"schemaname":"public","tablename": "test","columnname": "c1","position": 1}'::jsonb);
  140. select h.ddl_modifyColumn('{"schemaname":"public","tablename": "test","columnname": "c2","position": 2}'::jsonb);
  143. select * from h.ddl;
  145. drop table if exists public.player;
  146. create table public.player(
  147.   id bigint not null,
  148.   avatar varchar not null, -- 头像
  149.   code varchar not null , -- 登录账号
  150.   salt varchar not null, -- 登录密码盐
  151.   level varchar not null, -- 级别
  152.   exp bigint not null, -- 经验值
  153.   coins bigint not null, -- 金币
  154.   password varchar not null, -- 登录密码
  155.   createAt bigint not null, -- 创建时间
  156.   updateAt bigint not null, -- 更新时间
  157.                            -- 上边这些字段,管理员才能修改, 或由后台逻辑修改
  159.   nickname varchar not null check ( length(nickname) < 9 ), -- 昵称
  160.   birthday bigint not null, --  生日, 可以给默认值 1901-01-01, 但若是某个业务单据的审批时间, 可以给个不可能出现的只, 比如 0001-01-01
  161.   bloodType varchar not null check ( bloodType in ('A','B','O','AB') ),
  163.   primary key (id)
  164. );
  166. insert into h.ddl(schemaName,tablename,columnName,columnType,comment,position,checkrule,defaultvalue,primarykey) values
  167.      ('public','player','id','bigint','',default,'', '',true)
  168.     ,('public','player','avatar','varchar','头像',default, '','',false)
  169.     ,('public','player','code','varchar', '登录号',default,'','',false)
  170.     ,('public','player','salt','varchar','密码盐',default,'', '',false)
  171.     ,('public','player','level','bigint','等级',default,'', '',false)
  172.     ,('public','player','exp','bigint','经验',default,'', '',false)
  173.     ,('public','player','coins','bigint','金币',default,'', '',false)
  174.     ,('public','player','password','varchar','',default,'', '',false)
  175.     ,('public','player','createAt','bigint','',default,'', '',false)
  176.     ,('public','player','updateAt','bigint','',default,'', '',false)
  177.     ,('public','player','nickname','varchar','',default,'', '',false)
  178.     ,('public','player','birthday','bigint','',default,'', '',false)
  179.     ,('public','player','bloodType','varchar','',default,'', '',false)
  180.     ;
  181. delete from h.ddl where true;;
  183. select * from h.ddl order by id ;
  184. drop  table if exists public.player;
  185. select h.ddl_createTable('public','player');
  186. select * from public.player;
  187. create table public.player(avatar varchar not null  constraint player_avatar_check check(true),birthday bigint not null  constraint player_birthday_check check(true),bloodType varchar not null  constraint player_bloodType_check check(true),code varchar not null default 登录号 constraint player_code_check check(true),coins bigint not null  constraint player_coins_check check(true),createAt bigint not null  constraint player_createAt_check check(true),exp bigint not null  constraint player_exp_check check(经验),id bigint not null  constraint player_id_check check(true),level bigint not null  constraint player_level_check check(等级),nickname varchar not null  constraint player_nickname_check check(true),password varchar not null  constraint player_password_check check(true),salt varchar not null  constraint player_salt_check check(密码盐),updateAt bigint not null  constraint player_updateAt_check check(true),primary key(id));

物理删除备份schema

-- 物理删除备份schema
create schema DeletedBackup;

drop table if exists DeletedBackup.FieldPermission;
create table DeletedBackup.FieldPermission(
    id long, uri string, tableName string ,operation long
    ,roleIds long[] -- 适用的角色
    ,fields string[] -- 允许的字段
    ,createAt long,createOr long,updateAt long,modifier long
 ,oCreateAt long,oCreator -- 操作人, 操作时间
);

角色-字段操作权限

drop table if exists DeletedBackup.FieldPermission;
create table DeletedBackup.FieldPermission(
    id long, uri string, tableName string ,operation long
    ,roleIds long[] -- 适用的角色
    ,fields string[] -- 允许的字段
    ,createAt long,createOr long,updateAt long,modifier long
    ,oCreateAt long,oCreator long -- 操作人, 操作时间
);

drop table if exists public.SysRoleAccount;
create table public.SysRoleAccount(
  id long, roleId long,accountId long, usable long
  ,createAt long,createOr long,updateAt long,modifier long
);

-- 某个用户, 在某个接口内, 可传参修改哪些字段
select unnest(fields) from FieldPermission where (uri,tableName,operation) = ('','',0) and (select  unnest(roleIds) intersect select roleId from SysRoleAccount where usable = 0 and accountId = ? limit  1)>0;
-- 一般来说, 也用不到这么精确控制, 需要禁用时走前端, 而不是接口处理, 不过可以通过配置这里, 反过来统一禁用前端控件
select fields @> array['']::string[] from FieldPermission where (uri,tableName,operation) = ('','',0) and (select  unnest(roleIds) intersect select roleId from SysRoleAccount where usable = 0 and accountId = ? limit  1)>0;

-- 做交集运算时, limit 1 将针对两边, 而不是后部分
select * from (values(1),(2)) t intersect select * from (values(3),(2)) t2 limit 1;