955 lines
22 KiB
PL/PgSQL
955 lines
22 KiB
PL/PgSQL
--
|
||
-- PostgreSQL database dump
|
||
--
|
||
|
||
-- Dumped from database version 10.9
|
||
-- Dumped by pg_dump version 12.5
|
||
|
||
\c tj_project
|
||
|
||
SET statement_timeout = 0;
|
||
SET lock_timeout = 0;
|
||
SET idle_in_transaction_session_timeout = 0;
|
||
SET client_encoding = 'UTF8';
|
||
SET standard_conforming_strings = on;
|
||
SELECT pg_catalog.set_config('search_path', '', false);
|
||
SET check_function_bodies = false;
|
||
SET xmloption = content;
|
||
SET client_min_messages = warning;
|
||
SET row_security = off;
|
||
|
||
SET default_tablespace = '';
|
||
|
||
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
|
||
|
||
|
||
--
|
||
-- Name: EXTENSION postgis; Type: COMMENT; Schema: -; Owner:
|
||
--
|
||
|
||
COMMENT ON EXTENSION postgis IS 'PostGIS geometry, geography, and raster spatial types and functions';
|
||
|
||
|
||
--
|
||
-- Name: trigger_set_timestamp(); Type: FUNCTION; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE FUNCTION public.trigger_set_timestamp() RETURNS trigger
|
||
LANGUAGE plpgsql
|
||
AS $$ BEGIN
|
||
NEW.update_time = NOW();
|
||
RETURN NEW;
|
||
|
||
END;$$;
|
||
|
||
|
||
ALTER FUNCTION public.trigger_set_timestamp() OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: apkversion; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.apkversion (
|
||
versionid numeric(2,1),
|
||
version_code character varying(100),
|
||
apkpath character varying(100),
|
||
description text
|
||
);
|
||
|
||
|
||
ALTER TABLE public.apkversion OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: task; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.task (
|
||
id integer NOT NULL,
|
||
task_name character varying(255),
|
||
operator character varying(255),
|
||
create_time timestamp(6) without time zone,
|
||
modify_time timestamp(6) without time zone,
|
||
xml_id integer,
|
||
has_create_tables smallint,
|
||
config text,
|
||
database_name character varying(255),
|
||
introduction character varying(255),
|
||
upload_deadline character varying(10),
|
||
create_tables_md5code character varying(255),
|
||
is_allocation_optional smallint DEFAULT 0,
|
||
is_reviewer_restricted smallint DEFAULT 1
|
||
);
|
||
|
||
|
||
ALTER TABLE public.task OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: COLUMN task.is_allocation_optional; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON COLUMN public.task.is_allocation_optional IS '0 表示该任务必须分配，1 表示无需分配';
|
||
|
||
|
||
--
|
||
-- Name: task_cell; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.task_cell (
|
||
id integer NOT NULL,
|
||
task_id integer NOT NULL,
|
||
geohash character varying(20) NOT NULL,
|
||
geojson text NOT NULL
|
||
);
|
||
|
||
|
||
ALTER TABLE public.task_cell OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: task_cell_data; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.task_cell_data (
|
||
id integer NOT NULL,
|
||
task_id integer NOT NULL,
|
||
table_name character varying(255) NOT NULL,
|
||
data_id integer NOT NULL,
|
||
cell_geohash character varying(255) NOT NULL
|
||
);
|
||
|
||
|
||
ALTER TABLE public.task_cell_data OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: TABLE task_cell_data; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON TABLE public.task_cell_data IS '网格和数据的对应关系';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN task_cell_data.table_name; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON COLUMN public.task_cell_data.table_name IS '数据所在的表';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN task_cell_data.cell_geohash; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON COLUMN public.task_cell_data.cell_geohash IS '网格的 Geohash，作为网格的标识';
|
||
|
||
|
||
--
|
||
-- Name: task_cell_data_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE SEQUENCE public.task_cell_data_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER TABLE public.task_cell_data_id_seq OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: task_cell_data_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER SEQUENCE public.task_cell_data_id_seq OWNED BY public.task_cell_data.id;
|
||
|
||
|
||
--
|
||
-- Name: task_cell_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE SEQUENCE public.task_cell_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER TABLE public.task_cell_id_seq OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: task_cell_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER SEQUENCE public.task_cell_id_seq OWNED BY public.task_cell.id;
|
||
|
||
|
||
--
|
||
-- Name: task_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE SEQUENCE public.task_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
MAXVALUE 2147483647
|
||
CACHE 1;
|
||
|
||
|
||
ALTER TABLE public.task_id_seq OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: task_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER SEQUENCE public.task_id_seq OWNED BY public.task.id;
|
||
|
||
|
||
--
|
||
-- Name: task_image; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.task_image (
|
||
id integer NOT NULL,
|
||
file_name character varying(255) NOT NULL,
|
||
bbox public.geometry NOT NULL,
|
||
task_id integer NOT NULL
|
||
);
|
||
|
||
|
||
ALTER TABLE public.task_image OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: TABLE task_image; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON TABLE public.task_image IS '加密影像';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN task_image.file_name; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON COLUMN public.task_image.file_name IS 'tpkx 文件的名称';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN task_image.bbox; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON COLUMN public.task_image.bbox IS '外接矩形';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN task_image.task_id; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON COLUMN public.task_image.task_id IS '任务ID';
|
||
|
||
|
||
--
|
||
-- Name: task_image_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE SEQUENCE public.task_image_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER TABLE public.task_image_id_seq OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: task_image_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER SEQUENCE public.task_image_id_seq OWNED BY public.task_image.id;
|
||
|
||
|
||
--
|
||
-- Name: task_select_id; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.task_select_id (
|
||
id bigint NOT NULL,
|
||
select_order smallint NOT NULL,
|
||
select_id text NOT NULL,
|
||
src_task_id integer NOT NULL,
|
||
src_table_name character varying(50) NOT NULL,
|
||
dest_task_id integer NOT NULL,
|
||
dest_table_name character varying(50) NOT NULL
|
||
);
|
||
|
||
|
||
ALTER TABLE public.task_select_id OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: COLUMN task_select_id.select_order; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON COLUMN public.task_select_id.select_order IS '抽取顺序';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN task_select_id.select_id; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON COLUMN public.task_select_id.select_id IS '抽取的id字符串，用逗号隔开';
|
||
|
||
|
||
--
|
||
-- Name: task_select_id_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE SEQUENCE public.task_select_id_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER TABLE public.task_select_id_id_seq OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: task_select_id_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER SEQUENCE public.task_select_id_id_seq OWNED BY public.task_select_id.id;
|
||
|
||
|
||
--
|
||
-- Name: task_user_cell; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.task_user_cell (
|
||
id integer NOT NULL,
|
||
task_id integer NOT NULL,
|
||
user_id integer NOT NULL,
|
||
cell_geohash character varying(255) NOT NULL
|
||
);
|
||
|
||
|
||
ALTER TABLE public.task_user_cell OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: TABLE task_user_cell; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON TABLE public.task_user_cell IS '外业人员和在任务中分配到的网格的对应关系';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN task_user_cell.cell_geohash; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON COLUMN public.task_user_cell.cell_geohash IS '用来标识网格的 Geohash';
|
||
|
||
|
||
--
|
||
-- Name: task_user_cell_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE SEQUENCE public.task_user_cell_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER TABLE public.task_user_cell_id_seq OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: task_user_cell_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER SEQUENCE public.task_user_cell_id_seq OWNED BY public.task_user_cell.id;
|
||
|
||
|
||
--
|
||
-- Name: task_user_role; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.task_user_role (
|
||
id integer NOT NULL,
|
||
task_id integer NOT NULL,
|
||
user_id integer NOT NULL,
|
||
work_role smallint DEFAULT 0 NOT NULL
|
||
);
|
||
|
||
|
||
ALTER TABLE public.task_user_role OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: TABLE task_user_role; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON TABLE public.task_user_role IS '某个任务中外业人员的身份（审核人员或采集人员）';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN task_user_role.work_role; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON COLUMN public.task_user_role.work_role IS '1：审核人员 0：采集人员';
|
||
|
||
|
||
--
|
||
-- Name: task_user_role_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE SEQUENCE public.task_user_role_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER TABLE public.task_user_role_id_seq OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: task_user_role_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER SEQUENCE public.task_user_role_id_seq OWNED BY public.task_user_role.id;
|
||
|
||
|
||
--
|
||
-- Name: upms_log_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE SEQUENCE public.upms_log_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER TABLE public.upms_log_id_seq OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: user_permission; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.user_permission (
|
||
id integer NOT NULL,
|
||
description character varying NOT NULL,
|
||
create_time timestamp without time zone DEFAULT now() NOT NULL,
|
||
update_time timestamp without time zone DEFAULT now() NOT NULL
|
||
);
|
||
|
||
|
||
ALTER TABLE public.user_permission OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: TABLE user_permission; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON TABLE public.user_permission IS '用户权限';
|
||
|
||
|
||
--
|
||
-- Name: user_permission_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE SEQUENCE public.user_permission_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER TABLE public.user_permission_id_seq OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: user_permission_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER SEQUENCE public.user_permission_id_seq OWNED BY public.user_permission.id;
|
||
|
||
|
||
--
|
||
-- Name: user_role; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.user_role (
|
||
id integer NOT NULL,
|
||
name character varying NOT NULL,
|
||
create_time timestamp without time zone DEFAULT now() NOT NULL,
|
||
update_time timestamp without time zone DEFAULT now() NOT NULL,
|
||
code integer NOT NULL
|
||
);
|
||
|
||
|
||
ALTER TABLE public.user_role OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: TABLE user_role; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON TABLE public.user_role IS '用户角色';
|
||
|
||
|
||
--
|
||
-- Name: user_role_code; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.user_role_code (
|
||
id integer NOT NULL,
|
||
code integer NOT NULL,
|
||
role integer NOT NULL
|
||
);
|
||
|
||
|
||
ALTER TABLE public.user_role_code OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: user_role_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE SEQUENCE public.user_role_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER TABLE public.user_role_id_seq OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: user_role_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER SEQUENCE public.user_role_id_seq OWNED BY public.user_role.id;
|
||
|
||
|
||
--
|
||
-- Name: user_role_permission; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.user_role_permission (
|
||
id integer NOT NULL,
|
||
role integer NOT NULL,
|
||
permission integer NOT NULL,
|
||
create_time timestamp without time zone DEFAULT now() NOT NULL,
|
||
update_time timestamp without time zone DEFAULT now() NOT NULL
|
||
);
|
||
|
||
|
||
ALTER TABLE public.user_role_permission OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: TABLE user_role_permission; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON TABLE public.user_role_permission IS '角色-权限对照';
|
||
|
||
|
||
--
|
||
-- Name: user_role_premission_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE SEQUENCE public.user_role_premission_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER TABLE public.user_role_premission_id_seq OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: user_role_premission_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER SEQUENCE public.user_role_premission_id_seq OWNED BY public.user_role_permission.id;
|
||
|
||
|
||
--
|
||
-- Name: user_staff; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.user_staff (
|
||
id integer NOT NULL,
|
||
username character varying NOT NULL,
|
||
password character varying NOT NULL,
|
||
salt character varying NOT NULL,
|
||
phone character varying,
|
||
email character varying,
|
||
role integer NOT NULL,
|
||
create_time timestamp without time zone DEFAULT now() NOT NULL,
|
||
update_time timestamp without time zone DEFAULT now() NOT NULL,
|
||
real_name character varying(255) NOT NULL
|
||
);
|
||
|
||
|
||
ALTER TABLE public.user_staff OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: TABLE user_staff; Type: COMMENT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
COMMENT ON TABLE public.user_staff IS '人员信息';
|
||
|
||
|
||
--
|
||
-- Name: user_staff_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE SEQUENCE public.user_staff_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER TABLE public.user_staff_id_seq OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: user_staff_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER SEQUENCE public.user_staff_id_seq OWNED BY public.user_staff.id;
|
||
|
||
|
||
--
|
||
-- Name: userinfo_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE SEQUENCE public.userinfo_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
ALTER TABLE public.userinfo_id_seq OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: xml; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.xml (
|
||
id integer NOT NULL,
|
||
file_name character varying(255),
|
||
md5_code character varying(255),
|
||
file_path text,
|
||
create_time timestamp(6) without time zone,
|
||
modify_time timestamp(6) without time zone
|
||
);
|
||
|
||
|
||
ALTER TABLE public.xml OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: xml_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE SEQUENCE public.xml_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
MAXVALUE 2147483647
|
||
CACHE 1;
|
||
|
||
|
||
ALTER TABLE public.xml_id_seq OWNER TO postgres;
|
||
|
||
--
|
||
-- Name: xml_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER SEQUENCE public.xml_id_seq OWNED BY public.xml.id;
|
||
|
||
|
||
--
|
||
-- Name: task id; Type: DEFAULT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task ALTER COLUMN id SET DEFAULT nextval('public.task_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: task_cell id; Type: DEFAULT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task_cell ALTER COLUMN id SET DEFAULT nextval('public.task_cell_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: task_cell_data id; Type: DEFAULT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task_cell_data ALTER COLUMN id SET DEFAULT nextval('public.task_cell_data_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: task_image id; Type: DEFAULT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task_image ALTER COLUMN id SET DEFAULT nextval('public.task_image_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: task_select_id id; Type: DEFAULT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task_select_id ALTER COLUMN id SET DEFAULT nextval('public.task_select_id_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: task_user_cell id; Type: DEFAULT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task_user_cell ALTER COLUMN id SET DEFAULT nextval('public.task_user_cell_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: task_user_role id; Type: DEFAULT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task_user_role ALTER COLUMN id SET DEFAULT nextval('public.task_user_role_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: user_permission id; Type: DEFAULT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.user_permission ALTER COLUMN id SET DEFAULT nextval('public.user_permission_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: user_role id; Type: DEFAULT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.user_role ALTER COLUMN id SET DEFAULT nextval('public.user_role_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: user_role_permission id; Type: DEFAULT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.user_role_permission ALTER COLUMN id SET DEFAULT nextval('public.user_role_premission_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: user_staff id; Type: DEFAULT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.user_staff ALTER COLUMN id SET DEFAULT nextval('public.user_staff_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: xml id; Type: DEFAULT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.xml ALTER COLUMN id SET DEFAULT nextval('public.xml_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: task_cell_data task_cell_data_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task_cell_data
|
||
ADD CONSTRAINT task_cell_data_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: task_cell task_cell_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task_cell
|
||
ADD CONSTRAINT task_cell_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: task_image task_image_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task_image
|
||
ADD CONSTRAINT task_image_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: task task_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task
|
||
ADD CONSTRAINT task_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: task_select_id task_select_id_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task_select_id
|
||
ADD CONSTRAINT task_select_id_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: task_user_cell task_user_cell_pk; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task_user_cell
|
||
ADD CONSTRAINT task_user_cell_pk UNIQUE (task_id, user_id, cell_geohash);
|
||
|
||
|
||
--
|
||
-- Name: task_user_cell task_user_cell_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task_user_cell
|
||
ADD CONSTRAINT task_user_cell_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: task_user_role task_user_role_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task_user_role
|
||
ADD CONSTRAINT task_user_role_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: user_permission unique_description; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.user_permission
|
||
ADD CONSTRAINT unique_description UNIQUE (description);
|
||
|
||
|
||
--
|
||
-- Name: user_staff unique_email; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.user_staff
|
||
ADD CONSTRAINT unique_email UNIQUE (email);
|
||
|
||
|
||
--
|
||
-- Name: user_staff unique_phone; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.user_staff
|
||
ADD CONSTRAINT unique_phone UNIQUE (phone);
|
||
|
||
|
||
--
|
||
-- Name: user_staff unique_username; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.user_staff
|
||
ADD CONSTRAINT unique_username UNIQUE (username);
|
||
|
||
|
||
--
|
||
-- Name: user_permission user_permission_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.user_permission
|
||
ADD CONSTRAINT user_permission_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: user_role_code user_role_code_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.user_role_code
|
||
ADD CONSTRAINT user_role_code_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: user_role user_role_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.user_role
|
||
ADD CONSTRAINT user_role_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: user_role_permission user_role_premission_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.user_role_permission
|
||
ADD CONSTRAINT user_role_premission_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: user_staff user_staff_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.user_staff
|
||
ADD CONSTRAINT user_staff_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: xml xml_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.xml
|
||
ADD CONSTRAINT xml_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: geom_idx; Type: INDEX; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE INDEX geom_idx ON public.task_image USING gist (bbox);
|
||
|
||
|
||
--
|
||
-- Name: task_user_role_task_id_user_id_uindex; Type: INDEX; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE UNIQUE INDEX task_user_role_task_id_user_id_uindex ON public.task_user_role USING btree (task_id, user_id);
|
||
|
||
|
||
--
|
||
-- Name: user_permission update_timestamp; Type: TRIGGER; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TRIGGER update_timestamp BEFORE UPDATE ON public.user_permission FOR EACH ROW EXECUTE PROCEDURE public.trigger_set_timestamp();
|
||
|
||
|
||
--
|
||
-- Name: user_role update_timestamp; Type: TRIGGER; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TRIGGER update_timestamp BEFORE UPDATE ON public.user_role FOR EACH ROW EXECUTE PROCEDURE public.trigger_set_timestamp();
|
||
|
||
|
||
--
|
||
-- Name: user_role_permission update_timestamp; Type: TRIGGER; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TRIGGER update_timestamp BEFORE UPDATE ON public.user_role_permission FOR EACH ROW EXECUTE PROCEDURE public.trigger_set_timestamp();
|
||
|
||
|
||
--
|
||
-- Name: user_staff update_timestamp; Type: TRIGGER; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TRIGGER update_timestamp BEFORE UPDATE ON public.user_staff FOR EACH ROW EXECUTE PROCEDURE public.trigger_set_timestamp();
|
||
|
||
|
||
--
|
||
-- Name: task_image task_image_task_id_fk; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.task_image
|
||
ADD CONSTRAINT task_image_task_id_fk FOREIGN KEY (task_id) REFERENCES public.task(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
||
|
||
|
||
--
|
||
-- PostgreSQL database dump complete
|
||
--
|
||
|