-- Table: t_dav_use_dir

-- DROP TABLE t_dav_use_dir;

CREATE TABLE t_dav_use_dir
(
  dir_id bigserial 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,
  CONSTRAINT pk_dir_dav_used_dir_id PRIMARY KEY (dir_id),
  CONSTRAINT fk_dav_use_dir_dav_userdir FOREIGN KEY (dir_pid)
      REFERENCES t_dav_use_dir (dir_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  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) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_dir_dav_used_sp_user_seq FOREIGN KEY (sp_user_seq)
      REFERENCES t_sms_sp_user (sp_user_seq) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT uk_dir_dav_used_path UNIQUE (path_name)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE t_dav_use_dir OWNER TO syshost;

-- Index: idx_dav_use_dir_pk

-- DROP INDEX idx_dav_use_dir_pk;

CREATE INDEX idx_dav_use_dir_pk
  ON t_dav_use_dir
  USING btree
  (dir_id);


-- Index: idx_dav_use_dir_depth_path

-- DROP INDEX idx_dav_use_dir_depth_path;

CREATE INDEX idx_dav_use_dir_depth_path
  ON t_dav_use_dir
  USING btree
  (depth, path_name);

-- Index: idx_dav_use_dir_depth_path_lower

-- DROP INDEX idx_dav_use_dir_depth_path_lower;

CREATE INDEX idx_dav_use_dir_depth_path_lower
  ON t_dav_use_dir
  USING btree
  (depth, lower(path_name));


-- Index: idx_dav_use_dir_pid

-- DROP INDEX idx_dav_use_dir_pid;

CREATE INDEX idx_dav_use_dir_pid
  ON t_dav_use_dir
  USING btree
  (dir_pid);


-- Trigger: trig_use_dir_inser_before on t_dav_use_dir

-- DROP TRIGGER trig_use_dir_inser_before ON t_dav_use_dir;

CREATE TRIGGER trig_use_dir_inser_before
  BEFORE INSERT
  ON t_dav_use_dir
  FOR EACH ROW
  EXECUTE PROCEDURE use_dir_trigger();