|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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;
|
------------------------------------------------------------------------------
|
