select * from stationseries;
select * from sitepoint order by gid;
update sitepoint set sitename='轮南首站' where gid=55;
------------------------------------------------------------------------------ SQL语句
select * from data_dir;
select * from meta_data;
select table_name as name from data_dictionary group by table_name;
update meta_data set create_time=CURRENT_TIMESTAMP,create_user='admin' where 1=1; -- CURRENT_TIME，CURRENT_DATE

select * from data_files;
select * from style_data;   commit;
delete from style_data where id > 22;
select * from data_dictionary order by id;
select *,queryLevel(path_id) as path from meta_data;
select queryLevel(2005);

select count(*) from meta_data where Upper(name) like '%A%';
select *,queryLevel(path_id) path from meta_data where Upper(name) like '%A%' order by id limit 2 offset 0;

alter table style_data add column path varchar(255); comment on column style_data.path is '存储路径';
alter table style_data add column img varchar(255); comment on column style_data.img is '预览图片';

INSERT INTO public.meta_data(
	name, path_id, create_time, create_user, type, format, up_unit, status, version, x_min, y_min, x_max, y_max, coor_sys, accuracy, operation, remarks, s_url)
	VALUES ('SITEPOINT.shp',2007, CURRENT_TIMESTAMP, 'admin', 'SHP', '.shp','设计院/勘察室','正常', 'v1.0', 0, 0, 0, 0, 'CGCS200', '1:2000', 'upload', '', '');
------------------------------------------------------------------------------ 数据目录
DROP TABLE IF EXISTS public.data_dir;

CREATE TABLE public.data_dir
(
    id integer,
    name character varying(60),
    pid integer NOT NULL,
    oid integer DEFAULT 1,
    PRIMARY KEY (id)
);

COMMENT ON TABLE public.data_dir IS '数据目录';
COMMENT ON COLUMN public.data_dir.id IS 'ID';
COMMENT ON COLUMN public.data_dir.name IS '名称';
COMMENT ON COLUMN public.data_dir.pid IS '父节点';
COMMENT ON COLUMN public.data_dir.oid IS '序号';

SELECT COALESCE(MAX(id),1) FROM public.data_dir;
select * from public.data_dir;

DELETE FROM public.data_dir where id<1000;
SELECT id,name,pid,oid FROM public.data_dir order by id;
------------------------------------------------------------------------------ 上传文件
DROP TABLE IF EXISTS public.data_files;

create table public.data_files
(
	id serial primary key,
	mid integer,
	guid varchar(40),
	name varchar(255),
	ext varchar(10),
	path varchar(1024),
	subs varchar(1024),
	remark varchar(1024)
);

COMMENT ON TABLE public.data_files IS '上传文件';
COMMENT ON COLUMN public.data_files.id IS 'ID';
COMMENT ON COLUMN public.data_files.mid IS '元数据ID';
COMMENT ON COLUMN public.data_files.guid IS 'GUID';
COMMENT ON COLUMN public.data_files.name IS '名称';
COMMENT ON COLUMN public.data_files.ext IS '扩展名';
COMMENT ON COLUMN public.data_files.path IS '路径';
COMMENT ON COLUMN public.data_files.subs IS '子文件';
COMMENT ON COLUMN public.data_files.remark IS '备注';

select * from data_files;
------------------------------------------------------------------------------ 查询层级
with recursive rs as(
  select id,name,pid,oid from public.data_dir where id = 2005
  union
  select a.id,a.name,a.pid,a.oid from public.data_dir a, rs b where a.id=b.pid
) select id,name,pid,oid FROM rs order by id,oid;


CREATE OR REPLACE FUNCTION queryLevel(id INTEGER)
RETURNS VARCHAR as $$
  declare
    str varchar='';
    rec varchar; -- public.data_dir%ROWTYPE;
  begin
    for rec in execute 'with recursive rs as(' || 
      'select id,name,pid,oid from public.data_dir where id=' || id ||
	  ' union select a.id,a.name,a.pid,a.oid from public.data_dir a, rs b where a.id=b.pid '||
	  ') select name FROM rs order by id,oid'
    loop
      str = str || '\' || rec; -- rec.name;
    end loop;
	
	if (char_length(str) > 1) then
	  str = SUBSTRING(str, 2);
	end if;
   
    return str;
  end;
$$ LANGUAGE PLPGSQL;

-- 中俄东线管道工程南段\勘察数据\基础地质\区域地质
select queryLevel(2005);

select id,name,pid,oid,queryLevel(id) from data_dir where 1=1 order by id limit 10;

------------------------------------------------------------------------------ 查询层级ID
--drop function queryDir(id INTEGER);
CREATE OR REPLACE FUNCTION queryDir(id INTEGER)
RETURNS integer[] as $$
  declare
    ids integer[];
	sid integer;
  begin
    for sid in execute 'with recursive rs as(' || 
      'select id,pid from public.data_dir where id=' || id ||
	  ' union select a.id,a.pid from public.data_dir a, rs b where a.pid=b.id '||
	  ') select id FROM rs order by id'
    loop
      select array_append(ids, sid) into ids;
    end loop;
	
    return ids;
  end;
$$ LANGUAGE PLPGSQL;

-- {2003,2004,2005,2006,2007,2008,2009}
select queryDir(2003);

select * from data_dir order by id;
select * from meta_data where (path_id = ANY(array[2001,2002,2003,2005]));
select * from meta_data where (path_id = ANY((select queryDir(2005))::integer[])) order by id;
select * from meta_data where path_id=ANY(queryDir(2005)) order by id;
------------------------------------------------------------------------------