-- -- PostgreSQL database cluster dump -- SET default_transaction_read_only = off; SET client_encoding = 'UHC'; SET standard_conforming_strings = on; -- -- Roles -- CREATE ROLE cs_replicator; ALTER ROLE cs_replicator WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN REPLICATION PASSWORD 'md5873be65e752e3a8ad2ed38c58b49992d'; CREATE ROLE pgsql; ALTER ROLE pgsql WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION PASSWORD 'md58e132e3c6a65f840ebb36f86cc862351'; CREATE ROLE syshost; ALTER ROLE syshost WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION PASSWORD 'md599a16f5d51b9c66520373037cb9d6018'; -- -- Database creation -- CREATE DATABASE localdb WITH TEMPLATE = template0 OWNER = syshost ENCODING = 'UTF8'; CREATE DATABASE pgsql WITH TEMPLATE = template0 OWNER = pgsql; REVOKE ALL ON DATABASE template1 FROM PUBLIC; REVOKE ALL ON DATABASE template1 FROM pgsql; GRANT ALL ON DATABASE template1 TO pgsql; \connect localdb SET default_transaction_read_only = off; -- -- PostgreSQL database dump -- SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UHC'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; -- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; SET search_path = public, pg_catalog; -- -- Name: create_mata_table_trigger(); Type: FUNCTION; Schema: public; Owner: syshost -- CREATE FUNCTION create_mata_table_trigger() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE traget_schema text; target_table text; full_table text; firestname text; parent text; chkstr text; tran_id bigint; BEGIN traget_schema := 'public'; firestname := 't_meta'; tran_id := NEW.sp_svc_tran_id; parent := firestname || '_' || tran_id; target_table := firestname || '_' || tran_id; full_table := traget_schema || '.' || target_table; PERFORM tablename FROM pg_tables WHERE schemaname = traget_schema AND tablename = target_table; IF FOUND <> TRUE THEN -- table EXECUTE 'CREATE TABLE ' || full_table ||' ( resource_id bigint NOT NULL DEFAULT nextval(''t_dav_resource_resource_id_seq''::regclass), sp_user_seq bigint NOT NULL, sp_svc_tran_id bigint NOT NULL, host_name text NOT NULL, resource_type smallint NOT NULL, uri text NOT NULL, filename_org text, filename_hash text, depth smallint NOT NULL, creation_date bigint NOT NULL, display_name text, get_content_length bigint DEFAULT 0, get_content_type character varying(255), get_lastmodified bigint NOT NULL, source smallint, deleted_yn character(1) DEFAULT ''N''::bpchar, is_cache character(1) DEFAULT ''N''::bpchar, file_lastmodified bigint DEFAULT 0, PRIMARY KEY (resource_id), FOREIGN KEY (sp_svc_tran_id) REFERENCES t_sms_sp_svc_product (sp_svc_tran_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE )'; EXECUTE ' ALTER TABLE ' || full_table ||' OWNER TO syshost'; -- index EXECUTE 'CREATE INDEX ON ' || full_table || ' USING btree (uri)'; -- EXECUTE 'CREATE INDEX ON ' || full_table || ' USING btree (lower(uri)) WHERE deleted_yn = ''N'''; EXECUTE 'CREATE INDEX ON ' || full_table || ' USING btree (filename_hash)'; EXECUTE 'CREATE INDEX ON ' || full_table || ' USING btree (get_lastmodified)'; EXECUTE 'CREATE INDEX ON ' || full_table || ' USING btree (depth, uri)'; -- EXECUTE 'CREATE INDEX ON ' || full_table || ' USING btree (depth, lower(uri)) WHERE deleted_yn = ''N'''; END IF; RETURN NEW; END; $$; ALTER FUNCTION public.create_mata_table_trigger() OWNER TO syshost; -- -- Name: use_dir_trigger(); Type: FUNCTION; Schema: public; Owner: syshost -- CREATE FUNCTION use_dir_trigger() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE cid integer; pdirid bigint; ppath text; patharry text[]; BEGIN -- root path IF NEW.depth <= 0 THEN RETURN NEW; END IF; -- parent path patharry := string_to_array(NEW.path_name, '/'); ppath := ''; FOR cid IN 0 .. (NEW.depth - 1) LOOP ppath := ppath || '/'; ppath := ppath || patharry[cid+2]; END LOOP; SELECT dir_id INTO pdirid FROM t_dav_use_dir WHERE depth = (NEW.depth - 1) AND path_name = ppath; IF (TG_OP = 'INSERT') THEN -- RAISE NOTICE 'pdir_id - %, parent path - %', pdirid, ppath; IF pdirid IS NULL THEN BEGIN IF (NEW.depth - 1) > 0 THEN INSERT INTO t_dav_use_dir (sp_user_seq, sp_svc_tran_id, dir_pid, depth, path_name, dir_used) SELECT NEW.sp_user_seq, NEW.sp_svc_tran_id, 0, (NEW.depth - 1), ppath, 0; ELSE INSERT INTO t_dav_use_dir (sp_user_seq, sp_svc_tran_id, depth, path_name, dir_used) SELECT NEW.sp_user_seq, NEW.sp_svc_tran_id, 0, ppath, 0; END IF; EXCEPTION WHEN unique_violation OR undefined_column THEN -- do nothing END; SELECT dir_id INTO pdirid FROM t_dav_use_dir WHERE depth = (NEW.depth - 1) AND path_name = ppath; END IF ; IF pdirid IS NOT NULL THEN NEW.dir_pid = pdirid; END IF ; /* ELSIF (TG_OP = 'UPDATE') THEN IF ( TG_WHEN = 'BEFORE' ) THEN -- RAISE NOTICE 'UPDATE BEFORE pdir_id - %, parent path - %, used = %', pdirid, ppath, NEW.dir_used; IF pdirid IS NOT NULL THEN UPDATE t_dav_use_dir SET dir_used = dir_used +( NEW.dir_used - OLD.dir_used ) WHERE depth = (NEW.depth - 1) AND path_name = ppath; END IF; END IF; --*/ END IF; RETURN NEW; EXCEPTION WHEN unique_violation OR undefined_column THEN RAISE NOTICE 'use_dir_trigger EXCEPTION'; RETURN NEW; END; $$; ALTER FUNCTION public.use_dir_trigger() OWNER TO syshost; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: t_dav_anonymous; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_dav_anonymous ( sp_user_seq bigint NOT NULL, sp_svc_tran_id bigint NOT NULL, uri text NOT NULL, reg_datetime timestamp without time zone NOT NULL, update_datetime timestamp without time zone NOT NULL ); ALTER TABLE public.t_dav_anonymous OWNER TO syshost; -- -- Name: t_dav_lock; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_dav_lock ( uri text NOT NULL, lock_type integer NOT NULL, lock_token character varying(255), scope integer, file_type integer DEFAULT 1, depth integer, timeout bigint DEFAULT (0)::bigint, owner text, auth_user text, lockkey text ); ALTER TABLE public.t_dav_lock OWNER TO syshost; -- -- Name: t_dav_locknull; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_dav_locknull ( path text NOT NULL, filename_hash text ); ALTER TABLE public.t_dav_locknull OWNER TO syshost; -- -- Name: t_dav_namespace; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_dav_namespace ( ns_id smallint DEFAULT 0 NOT NULL, ns_name character varying(255) DEFAULT ''::character varying ); ALTER TABLE public.t_dav_namespace OWNER TO syshost; -- -- Name: t_dav_property; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_dav_property ( prop_id bigint NOT NULL, ns_id integer NOT NULL, filename_hash character varying(255) NOT NULL, propname character varying(255) NOT NULL, propvalue text NOT NULL ); ALTER TABLE public.t_dav_property OWNER TO syshost; -- -- Name: t_dav_property_prop_id_seq; Type: SEQUENCE; Schema: public; Owner: syshost -- CREATE SEQUENCE t_dav_property_prop_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE public.t_dav_property_prop_id_seq OWNER TO syshost; -- -- Name: t_dav_property_prop_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: syshost -- ALTER SEQUENCE t_dav_property_prop_id_seq OWNED BY t_dav_property.prop_id; -- -- Name: t_dav_resource_resource_id_seq; Type: SEQUENCE; Schema: public; Owner: syshost -- CREATE SEQUENCE t_dav_resource_resource_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE public.t_dav_resource_resource_id_seq OWNER TO syshost; -- -- Name: t_dav_use_dir; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_dav_use_dir ( dir_id bigint NOT NULL, sp_user_seq bigint NOT NULL, sp_svc_tran_id bigint NOT NULL, dir_pid bigint, depth integer, path_name character varying(4096), dir_used bigint DEFAULT 0 ); ALTER TABLE public.t_dav_use_dir OWNER TO syshost; -- -- Name: t_dav_use_dir_dir_id_seq; Type: SEQUENCE; Schema: public; Owner: syshost -- CREATE SEQUENCE t_dav_use_dir_dir_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE public.t_dav_use_dir_dir_id_seq OWNER TO syshost; -- -- Name: t_dav_use_dir_dir_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: syshost -- ALTER SEQUENCE t_dav_use_dir_dir_id_seq OWNED BY t_dav_use_dir.dir_id; -- -- Name: t_fhs_top_status; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_fhs_top_status ( fhs_top integer NOT NULL, fhs_hostname text, lastupdate timestamp without time zone ); ALTER TABLE public.t_fhs_top_status OWNER TO syshost; -- -- Name: t_sms_sp_svc_product; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_sms_sp_svc_product ( sp_svc_tran_id bigint NOT NULL, sp_user_seq bigint NOT NULL, sp_svc_id character varying(32) NOT NULL, sp_svc_begin_date date NOT NULL, sp_svc_end_date date NOT NULL, max_bandwidth bigint, max_str_size bigint, initial_vector character varying(24), private_key character varying(24), reg_datetime timestamp without time zone NOT NULL, update_datetime timestamp without time zone, validate_sp_session_id_yn character(1) DEFAULT 'N'::bpchar NOT NULL, bandwidth_ctrl_yn character(1) DEFAULT 'N'::bpchar NOT NULL, referer_chk_yn character(1) DEFAULT 'N'::bpchar NOT NULL, replication_count integer DEFAULT 1, cal_used_depth integer DEFAULT 0, uri_ignore_case character(1) DEFAULT 'N'::bpchar, del_file_keep_time integer DEFAULT 3 NOT NULL ); ALTER TABLE public.t_sms_sp_svc_product OWNER TO syshost; -- -- Name: COLUMN t_sms_sp_svc_product.validate_sp_session_id_yn; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product.validate_sp_session_id_yn IS 'CSDK에서 설정한 세션아이디를 SP 서버로 검증 요청할 지 여부(Y:yes, N:no)'; -- -- Name: COLUMN t_sms_sp_svc_product.bandwidth_ctrl_yn; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product.bandwidth_ctrl_yn IS '전송속도 조절 기능 사용여부 (''Y'' : 사용, ''N'' : 미사용)'; -- -- Name: COLUMN t_sms_sp_svc_product.referer_chk_yn; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product.referer_chk_yn IS '레퍼러 체크 기능 사용 여부'; -- -- Name: COLUMN t_sms_sp_svc_product.replication_count; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product.replication_count IS 'Replication Count Control'; -- -- Name: COLUMN t_sms_sp_svc_product.cal_used_depth; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product.cal_used_depth IS '볼륨 용량 조회 기준 depth '; -- -- Name: COLUMN t_sms_sp_svc_product.uri_ignore_case; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product.uri_ignore_case IS 'URI 대소문자 무시'; -- -- Name: COLUMN t_sms_sp_svc_product.del_file_keep_time; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product.del_file_keep_time IS '삭제 파일 보존 기간(일단위)'; -- -- Name: t_sms_sp_svc_product_band; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_sms_sp_svc_product_band ( sp_svc_tran_id bigint NOT NULL, sp_user_seq bigint NOT NULL, mode character(2) DEFAULT '10'::character varying NOT NULL, volume_bandwidth_limit character varying(100), volume_bandwidth_current character varying(100), volume_bandwidth_before character varying(100), volume_bandwidth_control_current character varying(10) DEFAULT '1000000'::character varying, volume_bandwidth_control_before character varying(10) DEFAULT '1000000'::character varying, volume_bandwidth_update_datetime timestamp without time zone, session_bandwidth_limit character varying(100), reg_datetime timestamp without time zone DEFAULT now() NOT NULL, update_datetime timestamp without time zone DEFAULT now() NOT NULL ); ALTER TABLE public.t_sms_sp_svc_product_band OWNER TO syshost; -- -- Name: COLUMN t_sms_sp_svc_product_band.volume_bandwidth_limit; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product_band.volume_bandwidth_limit IS '설정한 제한속도 '; -- -- Name: COLUMN t_sms_sp_svc_product_band.volume_bandwidth_current; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product_band.volume_bandwidth_current IS '측정한 현재속도'; -- -- Name: COLUMN t_sms_sp_svc_product_band.volume_bandwidth_before; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product_band.volume_bandwidth_before IS '바로 이전에 측정했던 속도'; -- -- Name: COLUMN t_sms_sp_svc_product_band.volume_bandwidth_control_current; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product_band.volume_bandwidth_control_current IS '현재 제어할 속도의 백분율*10000(''1000000'' : 전송무제한, ''0'' : 전송중지)'; -- -- Name: COLUMN t_sms_sp_svc_product_band.volume_bandwidth_control_before; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product_band.volume_bandwidth_control_before IS '이전 제어한 속도의 백분율*10000(''1000000'' : 전송무제한, ''0'' : 전송중지)'; -- -- Name: COLUMN t_sms_sp_svc_product_band.session_bandwidth_limit; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product_band.session_bandwidth_limit IS '단위 : bit per sec'; -- -- Name: t_sms_sp_svc_product_sync; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_sms_sp_svc_product_sync ( seq integer NOT NULL, sp_svc_tran_id bigint NOT NULL, master_yn character(1) DEFAULT 'N'::bpchar NOT NULL, linked_sp_svc_tran_id bigint NOT NULL, linked_rcts text NOT NULL ); ALTER TABLE public.t_sms_sp_svc_product_sync OWNER TO syshost; -- -- Name: TABLE t_sms_sp_svc_product_sync; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON TABLE t_sms_sp_svc_product_sync IS '[주의] 환원 구조 발생 가능 '; -- -- Name: COLUMN t_sms_sp_svc_product_sync.seq; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product_sync.seq IS '일련번호'; -- -- Name: COLUMN t_sms_sp_svc_product_sync.sp_svc_tran_id; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product_sync.sp_svc_tran_id IS 't_sms_sp_svc_product의 sp_svc_tran_id '; -- -- Name: COLUMN t_sms_sp_svc_product_sync.master_yn; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product_sync.master_yn IS 'sp_svc_tran_id에 대한 master 속성 유무 (Y : Master, N: Slave)'; -- -- Name: COLUMN t_sms_sp_svc_product_sync.linked_sp_svc_tran_id; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product_sync.linked_sp_svc_tran_id IS '자신과 sync된 서비스에 sp_svc_tran_id - Master 경우 : Slave sp_svc_tran_id - Slave 경우 : Master sp_svc_tran_id'; -- -- Name: COLUMN t_sms_sp_svc_product_sync.linked_rcts; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product_sync.linked_rcts IS 'sync된 서비스의 RCTS 도메인 정보 - Master 경우 : Slave RCTS 정보 - Slave 경우 : Master RCTS 정보'; -- -- Name: t_sms_sp_svc_product_sync_seq_seq; Type: SEQUENCE; Schema: public; Owner: syshost -- CREATE SEQUENCE t_sms_sp_svc_product_sync_seq_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE public.t_sms_sp_svc_product_sync_seq_seq OWNER TO syshost; -- -- Name: t_sms_sp_svc_product_sync_seq_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: syshost -- ALTER SEQUENCE t_sms_sp_svc_product_sync_seq_seq OWNED BY t_sms_sp_svc_product_sync.seq; -- -- Name: t_sms_sp_svc_product_val; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_sms_sp_svc_product_val ( sp_svc_tran_id bigint NOT NULL, sp_user_seq bigint NOT NULL, mode character varying(4) DEFAULT '1000'::character varying NOT NULL, auth_server character varying(4000) NOT NULL, auth_path character varying(4000) NOT NULL, auth_port character varying(10) NOT NULL, auth_timeout character varying(10) DEFAULT 10 NOT NULL, bill_server character varying(4000), bill_path character varying(4000), bill_port character varying(10), bill_timeout character varying(10) DEFAULT 30, reg_datetime timestamp without time zone DEFAULT now() NOT NULL, update_datetime timestamp without time zone DEFAULT now() NOT NULL, referer_chk_url character varying(2048), referer_chk_filetype character varying(4000) ); ALTER TABLE public.t_sms_sp_svc_product_val OWNER TO syshost; -- -- Name: COLUMN t_sms_sp_svc_product_val.mode; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product_val.mode IS '1000 Strict (SP접속 불가시 전송거부) 2000 Loose (SP접속 불가시 전송허용)'; -- -- Name: COLUMN t_sms_sp_svc_product_val.referer_chk_url; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product_val.referer_chk_url IS '허용할 레퍼러 URL'; -- -- Name: COLUMN t_sms_sp_svc_product_val.referer_chk_filetype; Type: COMMENT; Schema: public; Owner: syshost -- COMMENT ON COLUMN t_sms_sp_svc_product_val.referer_chk_filetype IS 'referer 체크를 할 파일의 확장자 eg. jpg|bmp|png 모든 파일에 대해 체크할 경우 NULL 또는 '''''; -- -- Name: t_sms_sp_user; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_sms_sp_user ( sp_user_seq bigint NOT NULL, sp_user_id character varying(32) NOT NULL, sp_user_password character varying(32) NOT NULL, reg_datetime timestamp without time zone NOT NULL, update_datetime timestamp without time zone NOT NULL ); ALTER TABLE public.t_sms_sp_user OWNER TO syshost; -- -- Name: prop_id; Type: DEFAULT; Schema: public; Owner: syshost -- ALTER TABLE ONLY t_dav_property ALTER COLUMN prop_id SET DEFAULT nextval('t_dav_property_prop_id_seq'::regclass); -- -- Name: dir_id; Type: DEFAULT; Schema: public; Owner: syshost -- ALTER TABLE ONLY t_dav_use_dir ALTER COLUMN dir_id SET DEFAULT nextval('t_dav_use_dir_dir_id_seq'::regclass); -- -- Name: seq; Type: DEFAULT; Schema: public; Owner: syshost -- ALTER TABLE ONLY t_sms_sp_svc_product_sync ALTER COLUMN seq SET DEFAULT nextval('t_sms_sp_svc_product_sync_seq_seq'::regclass); -- -- Name: fsh_top_status_pk; Type: CONSTRAINT; Schema: public; Owner: syshost; Tablespace: -- ALTER TABLE ONLY t_fhs_top_status ADD CONSTRAINT fsh_top_status_pk PRIMARY KEY (fhs_top); -- -- Name: pk_dav_namespace_ns_id; Type: CONSTRAINT; Schema: public; Owner: syshost; Tablespace: -- ALTER TABLE ONLY t_dav_namespace ADD CONSTRAINT pk_dav_namespace_ns_id PRIMARY KEY (ns_id); -- -- Name: pk_dav_property_prop_id; Type: CONSTRAINT; Schema: public; Owner: syshost; Tablespace: -- ALTER TABLE ONLY t_dav_property ADD CONSTRAINT pk_dav_property_prop_id PRIMARY KEY (prop_id); -- -- Name: pk_dir_dav_used_dir_id; Type: CONSTRAINT; Schema: public; Owner: syshost; Tablespace: -- ALTER TABLE ONLY t_dav_use_dir ADD CONSTRAINT pk_dir_dav_used_dir_id PRIMARY KEY (dir_id); -- -- Name: pk_sms_sp_svc_pdt_sp_svc_tran_id; Type: CONSTRAINT; Schema: public; Owner: syshost; Tablespace: -- ALTER TABLE ONLY t_sms_sp_svc_product ADD CONSTRAINT pk_sms_sp_svc_pdt_sp_svc_tran_id PRIMARY KEY (sp_svc_tran_id); -- -- Name: pk_sms_sp_user_sp_user_seq; Type: CONSTRAINT; Schema: public; Owner: syshost; Tablespace: -- ALTER TABLE ONLY t_sms_sp_user ADD CONSTRAINT pk_sms_sp_user_sp_user_seq PRIMARY KEY (sp_user_seq); -- -- Name: t_dav_anonymous_pkey; Type: CONSTRAINT; Schema: public; Owner: syshost; Tablespace: -- ALTER TABLE ONLY t_dav_anonymous ADD CONSTRAINT t_dav_anonymous_pkey PRIMARY KEY (sp_user_seq, sp_svc_tran_id, uri); -- -- Name: t_sms_sp_svc_product_band_pkey; Type: CONSTRAINT; Schema: public; Owner: syshost; Tablespace: -- ALTER TABLE ONLY t_sms_sp_svc_product_band ADD CONSTRAINT t_sms_sp_svc_product_band_pkey PRIMARY KEY (sp_svc_tran_id); -- -- Name: t_sms_sp_svc_product_sync_pkey; Type: CONSTRAINT; Schema: public; Owner: syshost; Tablespace: -- ALTER TABLE ONLY t_sms_sp_svc_product_sync ADD CONSTRAINT t_sms_sp_svc_product_sync_pkey PRIMARY KEY (seq); -- -- Name: t_sms_sp_svc_product_val_pkey; Type: CONSTRAINT; Schema: public; Owner: syshost; Tablespace: -- ALTER TABLE ONLY t_sms_sp_svc_product_val ADD CONSTRAINT t_sms_sp_svc_product_val_pkey PRIMARY KEY (sp_svc_tran_id); -- -- Name: uk_dir_dav_used_path; Type: CONSTRAINT; Schema: public; Owner: syshost; Tablespace: -- ALTER TABLE ONLY t_dav_use_dir ADD CONSTRAINT uk_dir_dav_used_path UNIQUE (path_name); -- -- Name: idx_dav_anonymous_sp_svc_tran_id; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_dav_anonymous_sp_svc_tran_id ON t_dav_anonymous USING btree (sp_svc_tran_id); -- -- Name: idx_dav_anonymous_sp_user_seq; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_dav_anonymous_sp_user_seq ON t_dav_anonymous USING btree (sp_user_seq); -- -- Name: idx_dav_namespace_ns_name; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_dav_namespace_ns_name ON t_dav_namespace USING btree (ns_name); -- -- Name: idx_dav_property_filename_hash_ns_id; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_dav_property_filename_hash_ns_id ON t_dav_property USING btree (filename_hash, ns_id); -- -- Name: idx_dav_use_dir_depth_path; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_dav_use_dir_depth_path ON t_dav_use_dir USING btree (depth, path_name); -- -- Name: idx_dav_use_dir_depth_path_lower; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_dav_use_dir_depth_path_lower ON t_dav_use_dir USING btree (depth, lower((path_name)::text)); -- -- Name: idx_dav_use_dir_pid; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_dav_use_dir_pid ON t_dav_use_dir USING btree (dir_pid); -- -- Name: idx_sms_sp_svc_pdt_sp_user_seq; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_sms_sp_svc_pdt_sp_user_seq ON t_sms_sp_svc_product USING btree (sp_user_seq); -- -- Name: t_sms_sp_svc_product_sp_svc_id_idx; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX t_sms_sp_svc_product_sp_svc_id_idx ON t_sms_sp_svc_product USING btree (sp_svc_id); -- -- Name: t_sms_sp_user_sp_user_id_idx; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX t_sms_sp_user_sp_user_id_idx ON t_sms_sp_user USING btree (sp_user_id); -- -- Name: trig_sms_sp_svc_product_insert_before; Type: TRIGGER; Schema: public; Owner: syshost -- CREATE TRIGGER trig_sms_sp_svc_product_insert_before BEFORE INSERT ON t_sms_sp_svc_product FOR EACH ROW EXECUTE PROCEDURE create_mata_table_trigger(); -- -- Name: trig_use_dir_inser_before; Type: TRIGGER; Schema: public; Owner: syshost -- CREATE TRIGGER trig_use_dir_inser_before BEFORE INSERT ON t_dav_use_dir FOR EACH ROW EXECUTE PROCEDURE use_dir_trigger(); -- -- Name: fk_dav_anonymous_sp_svc_tran_id; Type: FK CONSTRAINT; Schema: public; Owner: syshost -- ALTER TABLE ONLY t_dav_anonymous ADD CONSTRAINT fk_dav_anonymous_sp_svc_tran_id FOREIGN KEY (sp_svc_tran_id) REFERENCES t_sms_sp_svc_product(sp_svc_tran_id) ON UPDATE CASCADE ON DELETE CASCADE; -- -- Name: fk_dav_use_dir_dav_userdir; Type: FK CONSTRAINT; Schema: public; Owner: syshost -- ALTER TABLE ONLY t_dav_use_dir ADD CONSTRAINT fk_dav_use_dir_dav_userdir FOREIGN KEY (dir_pid) REFERENCES t_dav_use_dir(dir_id); -- -- Name: fk_dir_dav_used_sp_svc_tran_id; Type: FK CONSTRAINT; Schema: public; Owner: syshost -- ALTER TABLE ONLY t_dav_use_dir ADD CONSTRAINT fk_dir_dav_used_sp_svc_tran_id FOREIGN KEY (sp_svc_tran_id) REFERENCES t_sms_sp_svc_product(sp_svc_tran_id); -- -- Name: fk_dir_dav_used_sp_user_seq; Type: FK CONSTRAINT; Schema: public; Owner: syshost -- ALTER TABLE ONLY t_dav_use_dir ADD CONSTRAINT fk_dir_dav_used_sp_user_seq FOREIGN KEY (sp_user_seq) REFERENCES t_sms_sp_user(sp_user_seq); -- -- Name: t_sms_sp_svc_product_val_sp_svc_tran_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: syshost -- ALTER TABLE ONLY t_sms_sp_svc_product_band ADD CONSTRAINT t_sms_sp_svc_product_val_sp_svc_tran_id_fkey FOREIGN KEY (sp_svc_tran_id) REFERENCES t_sms_sp_svc_product(sp_svc_tran_id) ON UPDATE CASCADE ON DELETE CASCADE; -- -- Name: public; Type: ACL; Schema: -; Owner: pgsql -- REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM pgsql; GRANT ALL ON SCHEMA public TO pgsql; GRANT ALL ON SCHEMA public TO PUBLIC; -- -- PostgreSQL database dump complete -- \connect pgsql SET default_transaction_read_only = off; -- -- PostgreSQL database dump -- SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UHC'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; -- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; -- -- Name: public; Type: ACL; Schema: -; Owner: pgsql -- REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM pgsql; GRANT ALL ON SCHEMA public TO pgsql; GRANT ALL ON SCHEMA public TO PUBLIC; -- -- PostgreSQL database dump complete -- \connect postgres SET default_transaction_read_only = off; -- -- PostgreSQL database dump -- SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UHC'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; -- -- Name: postgres; Type: COMMENT; Schema: -; Owner: pgsql -- COMMENT ON DATABASE postgres IS 'default administrative connection database'; -- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; -- -- Name: public; Type: ACL; Schema: -; Owner: pgsql -- REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM pgsql; GRANT ALL ON SCHEMA public TO pgsql; GRANT ALL ON SCHEMA public TO PUBLIC; -- -- PostgreSQL database dump complete -- \connect template1 SET default_transaction_read_only = off; -- -- PostgreSQL database dump -- SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UHC'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; -- -- Name: template1; Type: COMMENT; Schema: -; Owner: pgsql -- COMMENT ON DATABASE template1 IS 'Default template database'; -- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; SET search_path = public, pg_catalog; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: t_code_master; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_code_master ( code character varying(8) NOT NULL, descs character varying(512) NOT NULL, reg_datetime timestamp without time zone NOT NULL, update_datetime timestamp without time zone NOT NULL ); ALTER TABLE public.t_code_master OWNER TO syshost; -- -- Name: t_dav_anonymous; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_dav_anonymous ( sp_user_seq bigint NOT NULL, sp_svc_tran_id bigint NOT NULL, uri text NOT NULL, reg_datetime timestamp without time zone NOT NULL, update_datetime timestamp without time zone NOT NULL, ); ALTER TABLE public.t_dav_anonymous OWNER TO syshost; -- -- Name: t_dav_bandwidth; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_dav_bandwidth ( sp_svc_tran_id bigint NOT NULL, level bigint DEFAULT 0, uri text NOT NULL, reg_datetime timestamp without time zone NOT NULL, update_datetime timestamp without time zone NOT NULL, deleted_yn character(1) DEFAULT 'N'::bpchar ); ALTER TABLE public.t_dav_bandwidth OWNER TO syshost; -- -- Name: t_dav_bandwidth_level; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_dav_bandwidth_level ( sp_svc_tran_id bigint NOT NULL, level bigint DEFAULT 0, level_min integer DEFAULT 0, level_max integer DEFAULT 0, reg_datetime bigint NOT NULL, update_datetime bigint NOT NULL ); ALTER TABLE public.t_dav_bandwidth_level OWNER TO syshost; -- -- Name: t_dav_lock; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_dav_lock ( uri text NOT NULL, lock_type integer NOT NULL, lock_token character varying(255), scope integer, file_type integer DEFAULT 1, depth integer, timeout bigint DEFAULT (0)::bigint, owner text, auth_user text, lockkey text ); ALTER TABLE public.t_dav_lock OWNER TO syshost; -- -- Name: t_dav_locknull; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_dav_locknull ( path text NOT NULL, filename_hash text ); ALTER TABLE public.t_dav_locknull OWNER TO syshost; -- -- Name: t_dav_namespace_ns_id_seq; Type: SEQUENCE; Schema: public; Owner: syshost -- CREATE SEQUENCE t_dav_namespace_ns_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE public.t_dav_namespace_ns_id_seq OWNER TO syshost; -- -- Name: t_dav_namespace; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_dav_namespace ( ns_id bigint DEFAULT nextval('t_dav_namespace_ns_id_seq'::regclass) NOT NULL, ns_name character varying(255) DEFAULT ''::character varying ); ALTER TABLE public.t_dav_namespace OWNER TO syshost; -- -- Name: t_dav_property; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_dav_property ( resource_id bigint NOT NULL, ns_id integer NOT NULL, propname character varying(255) NOT NULL, propvalue text NOT NULL ); ALTER TABLE public.t_dav_property OWNER TO syshost; -- -- Name: t_dav_space_in_use; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_dav_space_in_use ( sp_svc_tran_id bigint NOT NULL, in_use_size bigint NOT NULL, reg_datetime bigint NOT NULL, update_datetime bigint NOT NULL ); ALTER TABLE public.t_dav_space_in_use OWNER TO syshost; -- -- Name: t_health; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_health ( health character(8) NOT NULL ); ALTER TABLE public.t_health OWNER TO syshost; -- -- Name: t_module_log_log_seq_seq; Type: SEQUENCE; Schema: public; Owner: syshost -- CREATE SEQUENCE t_module_log_log_seq_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE public.t_module_log_log_seq_seq OWNER TO syshost; -- -- Name: t_module_log; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_module_log ( log_seq bigint DEFAULT nextval('t_module_log_log_seq_seq'::regclass) NOT NULL, module character varying(255) NOT NULL, tid character varying(52) NOT NULL, n_mode bytea NOT NULL, issuer character varying(128) NOT NULL, service_id character varying(128), result bytea NOT NULL, body_length bigint, reg_datetime timestamp without time zone DEFAULT now() ); ALTER TABLE public.t_module_log OWNER TO syshost; -- -- Name: t_sms_sp_svc_product; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_sms_sp_svc_product ( sp_svc_tran_id bigint NOT NULL, sp_user_seq bigint NOT NULL, sp_svc_id character varying(32) NOT NULL, sp_svc_begin_date date NOT NULL, sp_svc_end_date date NOT NULL, max_bandwidth bigint, max_str_size bigint, initial_vector character varying(24), private_key character varying(24), reg_datetime timestamp without time zone NOT NULL, update_datetime timestamp without time zone ); ALTER TABLE public.t_sms_sp_svc_product OWNER TO syshost; -- -- Name: t_sms_sp_user; Type: TABLE; Schema: public; Owner: syshost; Tablespace: -- CREATE TABLE t_sms_sp_user ( sp_user_seq character varying(16) NOT NULL, sp_user_id character varying(32) NOT NULL, sp_user_password character varying(32) NOT NULL, reg_datetime timestamp without time zone NOT NULL, update_datetime timestamp without time zone NOT NULL ); ALTER TABLE public.t_sms_sp_user OWNER TO syshost; -- -- Name: pk_code_master_code; Type: CONSTRAINT; Schema: public; Owner: syshost; Tablespace: -- ALTER TABLE ONLY t_code_master ADD CONSTRAINT pk_code_master_code PRIMARY KEY (code); -- -- Name: pk_module_log_seq; Type: CONSTRAINT; Schema: public; Owner: syshost; Tablespace: -- ALTER TABLE ONLY t_module_log ADD CONSTRAINT pk_module_log_seq PRIMARY KEY (log_seq); -- -- Name: pk_sms_sp_svc_pdt_sp_svc_tran_id; Type: CONSTRAINT; Schema: public; Owner: syshost; Tablespace: -- ALTER TABLE ONLY t_sms_sp_svc_product ADD CONSTRAINT pk_sms_sp_svc_pdt_sp_svc_tran_id PRIMARY KEY (sp_svc_tran_id); -- -- Name: pk_sms_sp_user_sp_user_seq; Type: CONSTRAINT; Schema: public; Owner: syshost; Tablespace: -- ALTER TABLE ONLY t_sms_sp_user ADD CONSTRAINT pk_sms_sp_user_sp_user_seq PRIMARY KEY (sp_user_seq); -- -- Name: idx_code_master_code; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_code_master_code ON t_code_master USING btree (code); -- -- Name: idx_dav_anonymous_sp_svc_tran_id; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_dav_anonymous_sp_svc_tran_id ON t_dav_anonymous USING btree (sp_svc_tran_id); -- -- Name: idx_dav_anonymous_sp_user_seq; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_dav_anonymous_sp_user_seq ON t_dav_anonymous USING btree (sp_user_seq); -- -- Name: idx_dav_bandwidth_level_sp_svc_tran_id; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_dav_bandwidth_level_sp_svc_tran_id ON t_dav_bandwidth_level USING btree (sp_svc_tran_id); -- -- Name: idx_dav_bandwidth_sp_svc_tran_id; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_dav_bandwidth_sp_svc_tran_id ON t_dav_bandwidth USING btree (sp_svc_tran_id); -- -- Name: idx_dav_space_in_use_sp_svc_tran_id; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_dav_space_in_use_sp_svc_tran_id ON t_dav_space_in_use USING btree (sp_svc_tran_id); -- -- Name: idx_module_data_tid; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_module_data_tid ON t_module_log USING btree (tid); -- -- Name: idx_module_log_log_seq; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_module_log_log_seq ON t_module_log USING btree (log_seq); -- -- Name: idx_sms_sp_svc_pdt_sp_svc_tran_id; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_sms_sp_svc_pdt_sp_svc_tran_id ON t_sms_sp_svc_product USING btree (sp_svc_tran_id); -- -- Name: idx_sms_sp_svc_pdt_sp_user_seq; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_sms_sp_svc_pdt_sp_user_seq ON t_sms_sp_svc_product USING btree (sp_user_seq); -- -- Name: idx_sms_sp_user_sp_user_seq; Type: INDEX; Schema: public; Owner: syshost; Tablespace: -- CREATE INDEX idx_sms_sp_user_sp_user_seq ON t_sms_sp_user USING btree (sp_user_seq); -- -- Name: fk_dav_anonymous_sp_svc_tran_id; Type: FK CONSTRAINT; Schema: public; Owner: syshost -- ALTER TABLE ONLY t_dav_anonymous ADD CONSTRAINT fk_dav_anonymous_sp_svc_tran_id FOREIGN KEY (sp_svc_tran_id) REFERENCES t_sms_sp_svc_product(sp_svc_tran_id) ON UPDATE CASCADE ON DELETE CASCADE; -- -- Name: fk_dav_bandwidth_sp_svc_tran_id; Type: FK CONSTRAINT; Schema: public; Owner: syshost -- ALTER TABLE ONLY t_dav_bandwidth ADD CONSTRAINT fk_dav_bandwidth_sp_svc_tran_id FOREIGN KEY (sp_svc_tran_id) REFERENCES t_sms_sp_svc_product(sp_svc_tran_id) ON UPDATE CASCADE ON DELETE CASCADE; -- -- Name: fk_dav_space_in_use_sp_svc_tran_id; Type: FK CONSTRAINT; Schema: public; Owner: syshost -- ALTER TABLE ONLY t_dav_bandwidth_level ADD CONSTRAINT fk_dav_space_in_use_sp_svc_tran_id FOREIGN KEY (sp_svc_tran_id) REFERENCES t_sms_sp_svc_product(sp_svc_tran_id) ON UPDATE CASCADE ON DELETE CASCADE; -- -- Name: fk_dav_space_in_use_sp_svc_tran_id; Type: FK CONSTRAINT; Schema: public; Owner: syshost -- ALTER TABLE ONLY t_dav_space_in_use ADD CONSTRAINT fk_dav_space_in_use_sp_svc_tran_id FOREIGN KEY (sp_svc_tran_id) REFERENCES t_sms_sp_svc_product(sp_svc_tran_id) ON UPDATE CASCADE ON DELETE CASCADE; -- -- Name: public; Type: ACL; Schema: -; Owner: pgsql -- REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM pgsql; GRANT ALL ON SCHEMA public TO pgsql; GRANT ALL ON SCHEMA public TO PUBLIC; -- -- PostgreSQL database dump complete -- -- -- PostgreSQL database cluster dump complete --