--
--	Script to create CRUSH tables in Oracle
--  Uses schema CRUSHFTP with TABLESPACE(s) of CRUSHFTPDATA, CRUSHFTPINDX
--
--
--	Oracle Create USERS
--
CREATE TABLE CRUSHFTP.USERS (
	USERID        NUMBER(8) NOT NULL,
	USERNAME      VARCHAR2(255) NOT NULL,
	PASSWORD      VARCHAR2(255) NOT NULL,
	SERVER_GROUP  VARCHAR2(50))
	PCTFREE 10 INITRANS 1 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPDATA;

ALTER TABLE CRUSHFTP.USERS ADD (
	CONSTRAINT CRUSH_USERS_PK PRIMARY KEY (USERID)
		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
		STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
		TABLESPACE CRUSHFTPINDX);

--
--	CREATE USER_PROPERTIES
--
CREATE TABLE CRUSHFTP.USER_PROPERTIES (
	USERID      NUMBER(8) NOT NULL,
	PROP_NAME   VARCHAR2(255) NOT NULL,
	PROP_VALUE  VARCHAR2(2000))
	PCTFREE 10 INITRANS 1 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPDATA;

ALTER TABLE CRUSHFTP.USER_PROPERTIES ADD (
	CONSTRAINT CRUSH_USER_PROPERTIES_PK PRIMARY KEY (USERID, PROP_NAME)
		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
		STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
		TABLESPACE CRUSHFTPINDX,
	CONSTRAINT CRUSH_USER_PROP_USER_FK FOREIGN KEY (USERID) REFERENCES CRUSHFTP.USERS (USERID) ON DELETE CASCADE);

--
--	Create DOMAIN_ROOT_LIST
--
CREATE TABLE CRUSHFTP.DOMAIN_ROOT_LIST (
	USERID  NUMBER(8) NOT NULL,
	DOMAIN  VARCHAR2(255),
	PATH    VARCHAR2(255))
	PCTFREE 10 INITRANS 1 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPDATA;

ALTER TABLE CRUSHFTP.DOMAIN_ROOT_LIST ADD (
	CONSTRAINT CRUSH_DOMAIN_ROOT_LIST_PK PRIMARY KEY (USERID, DOMAIN, PATH)
		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
		STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
		TABLESPACE CRUSHFTPINDX,
	CONSTRAINT CRUSH_DOMAIN_ROOT_LIST_USER_FK FOREIGN KEY (USERID) REFERENCES CRUSHFTP.USERS (USERID) ON DELETE CASCADE);
                           
--
-- CREATE EVENTS
--
CREATE TABLE CRUSHFTP.EVENTS (
	USERID                  NUMBER(8) NOT NULL,
	SQL_FIELD_NAME          VARCHAR2(255) NOT NULL,
	COMMAND                 VARCHAR2(255) NOT NULL,
	EVENT_DIR_DATA          VARCHAR2(255),
	EVENT_IF_LIST           VARCHAR2(255),
	EVENT_ACTION_LIST       VARCHAR2(255),
	EVENT_USER_ACTION_LIST  VARCHAR2(255),
	EVENT_AFTER_LIST        VARCHAR2(255),
	EVENT_PLUGIN_LIST       VARCHAR2(255) NOT NULL,
	SQL_FIELD_FROM          VARCHAR2(255),
	SQL_FIELD_TO            VARCHAR2(255),
	CC                      VARCHAR2(255),
	BCC                     VARCHAR2(255),
	SUBJECT                 VARCHAR2(255),
	BODY                    VARCHAR2(255),
	EVENT_ALWAYS_CB         VARCHAR2(10) DEFAULT 'false',
	EVENT_AFTER_CB          VARCHAR2(10) DEFAULT 'false',
	EVENT_NOW_CB            VARCHAR2(10) DEFAULT 'false',
	EVENT_IF_CB             VARCHAR2(10) DEFAULT 'false',
	SORT_ORDER              NUMBER(3))
	PCTFREE 10 INITRANS 1 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPDATA;

ALTER TABLE CRUSHFTP.EVENTS ADD (
	CONSTRAINT CRUSH_EVENTS_PK PRIMARY KEY (USERID, SQL_FIELD_NAME)
		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
		STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
		TABLESPACE CRUSHFTPINDX,
	CONSTRAINT CRUSH_EVENTS_USER_FK FOREIGN KEY (USERID) REFERENCES CRUSHFTP.USERS (USERID) ON DELETE CASCADE);

CREATE UNIQUE INDEX CRUSHFTP.IDX_CRUSH_EVENTS_USERID_SO ON CRUSHFTP.EVENTS (USERID, SORT_ORDER)
	PCTFREE 10 INITRANS 2 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPINDX;

--
--	CREATE EVENTS5
--	
CREATE TABLE CRUSHFTP.EVENTS5 (
	USERID      NUMBER(8) NOT NULL,
	EVENT_NAME  VARCHAR2(255) NOT NULL,
	PROP_NAME   VARCHAR2(255) NOT NULL,
	PROP_VALUE  VARCHAR2(2000))
	PCTFREE 10 INITRANS 1 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPDATA;

ALTER TABLE CRUSHFTP.EVENTS5 ADD (
	CONSTRAINT CRUSH_EVENTS5_PK PRIMARY KEY (USERID, EVENT_NAME, PROP_NAME)
		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
		STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
		TABLESPACE CRUSHFTPINDX,
	CONSTRAINT CRUSH_EVENTS5_USER_FK FOREIGN KEY (USERID) REFERENCES CRUSHFTP.USERS (USERID) ON DELETE CASCADE);

--
--	CREATE TABLE GROUPS
--
CREATE TABLE CRUSHFTP.GROUPS (
	GROUPNAME     VARCHAR2(255) NOT NULL,
	USERID        NUMBER(8) NOT NULL,
	SERVER_GROUP  VARCHAR2(50))
	PCTFREE 10 INITRANS 1 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPDATA;

ALTER TABLE CRUSHFTP.GROUPS ADD (
	CONSTRAINT CRUSH_GROUP_PK PRIMARY KEY (GROUPNAME, USERID)
		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
		STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
		TABLESPACE CRUSHFTPINDX,
	CONSTRAINT CRUSH_GROUP_USER_FK FOREIGN KEY (USERID) REFERENCES CRUSHFTP.USERS (USERID) ON DELETE CASCADE);

--
--	CREATE TABLE INHERITANCE
--
CREATE TABLE CRUSHFTP.INHERITANCE (
	USERID            NUMBER(8) NOT NULL,
	INHERIT_USERNAME  VARCHAR2(255) NOT NULL,
	SORT_ORDER        NUMBER(3))
	PCTFREE 10 INITRANS 1 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPDATA;

ALTER TABLE CRUSHFTP.INHERITANCE ADD (
	CONSTRAINT CRUSH_INHERITANCE_PK PRIMARY KEY (USERID, INHERIT_USERNAME)
		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
		STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
		TABLESPACE CRUSHFTPINDX,
	CONSTRAINT CRUSH_INHERITANCE_USER_FK FOREIGN KEY (USERID) REFERENCES CRUSHFTP.USERS (USERID) ON DELETE CASCADE);

CREATE UNIQUE INDEX CRUSHFTP.IDX_CRUSH_INHERITANCE_USRID_SO ON CRUSHFTP.INHERITANCE (USERID, SORT_ORDER)
	PCTFREE 10 INITRANS 2 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPINDX;

--
--	CREATE TABLE IP_RESTRICTIONS
--
CREATE TABLE CRUSHFTP.IP_RESTRICTIONS (
	USERID      NUMBER(8) NOT NULL,
	START_IP    VARCHAR2(255) NOT NULL,
	TYPE        VARCHAR2(1),
	STOP_IP     VARCHAR2(255) NOT NULL,
	SORT_ORDER  NUMBER(3) NOT NULL)
	PCTFREE 10 INITRANS 1 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPDATA;

ALTER TABLE CRUSHFTP.IP_RESTRICTIONS ADD (
	CONSTRAINT CRUSH_IP_RESTR_PK PRIMARY KEY (USERID, START_IP)
		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
		STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
		TABLESPACE CRUSHFTPINDX,
	CONSTRAINT CRUSH_IP_RESTR_TYPE_C CHECK (TYPE IN ('A','D')),
	CONSTRAINT CRUSH_IP_RESTR_USER_FK FOREIGN KEY (USERID) REFERENCES CRUSHFTP.USERS (USERID) ON DELETE CASCADE);

CREATE UNIQUE INDEX CRUSHFTP.IDX_CRUSH_IP_RESTR_USERID_SO ON CRUSHFTP.IP_RESTRICTIONS (USERID, SORT_ORDER)
	PCTFREE 10 INITRANS 2 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPINDX;

--
--	CREATE TABLE VFS (Virtual File System)
--
CREATE TABLE CRUSHFTP.VFS (
	USERID      NUMBER(8) NOT NULL,
	URL         VARCHAR2(255) NOT NULL,
	TYPE        VARCHAR2(50) NOT NULL,
	PATH        VARCHAR2(255) NOT NULL,
	SORT_ORDER  NUMBER(3) NOT NULL)
	PCTFREE 10 INITRANS 1 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPDATA;

ALTER TABLE CRUSHFTP.VFS ADD (
	CONSTRAINT CRUSH_VFS_PK PRIMARY KEY (USERID, PATH)
		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
		STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
		TABLESPACE CRUSHFTPINDX,
	CONSTRAINT CRUSH_VFS_USER_FK FOREIGN KEY (USERID) REFERENCES CRUSHFTP.USERS (USERID) ON DELETE CASCADE);

CREATE UNIQUE INDEX CRUSHFTP.IDX_CRUSH_VFS_USERID_SO ON CRUSHFTP.VFS (USERID, SORT_ORDER)
	PCTFREE 10 INITRANS 2 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPINDX;

--
--	CREATE TABLE VFS_PERMISSIONS
--
CREATE TABLE CRUSHFTP.VFS_PERMISSIONS (
	USERID  NUMBER(8) NOT NULL,
	PATH    VARCHAR2(255) NOT NULL,
	PRIVS   VARCHAR2(255) NOT NULL)
	PCTFREE 10 INITRANS 1 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPDATA;

ALTER TABLE CRUSHFTP.VFS_PERMISSIONS ADD (
	CONSTRAINT CRUSH_VFS_PERMISSIONS_PK PRIMARY KEY (USERID, PATH)
		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
		STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
		TABLESPACE CRUSHFTPINDX,
	CONSTRAINT CRUSH_VFS_PERMISSIONS_FK FOREIGN KEY (USERID) REFERENCES CRUSHFTP.USERS (USERID) ON DELETE CASCADE);

--
--	CREATE TABLE WEB_BUTTONS
--
CREATE TABLE CRUSHFTP.WEB_BUTTONS (
	USERID           NUMBER(8) NOT NULL,
	SQL_FIELD_KEY    VARCHAR2(255) NOT NULL,
	SQL_FIELD_VALUE  VARCHAR2(255) NOT NULL,
	SORT_ORDER       NUMBER(3) NOT NULL)
	PCTFREE 10 INITRANS 1 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPDATA;

ALTER TABLE CRUSHFTP.WEB_BUTTONS ADD (
	CONSTRAINT CRUSH_WEB_BUTTONS_PK PRIMARY KEY (USERID, SQL_FIELD_KEY)
		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
		STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
		TABLESPACE CRUSHFTPINDX,
	CONSTRAINT CRUSH_WEB_BUTTONS_USER_FK FOREIGN KEY (USERID) REFERENCES CRUSHFTP.USERS (USERID) ON DELETE CASCADE);

CREATE UNIQUE INDEX CRUSHFTP.IDX_CRUSH_WEB_BUTTONS_USRID_SO ON CRUSHFTP.WEB_BUTTONS (USERID, SORT_ORDER)
	PCTFREE 10 INITRANS 2 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPINDX;

--
--	CREATE TABLE WEB_CUSTOMIZATIONS
--
CREATE TABLE CRUSHFTP.WEB_CUSTOMIZATIONS (
	USERID           NUMBER(8) NOT NULL,
	SQL_FIELD_KEY    VARCHAR2(255) NOT NULL,
	SQL_FIELD_VALUE  VARCHAR2(255) NOT NULL,
	SORT_ORDER       NUMBER(3) NOT NULL)
	PCTFREE 10 INITRANS 1 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPDATA;

ALTER TABLE CRUSHFTP.WEB_CUSTOMIZATIONS ADD (
	CONSTRAINT CRUSH_WEB_CUSTOMIZATIONS_PK PRIMARY KEY (USERID, SQL_FIELD_KEY)
		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
		STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
		TABLESPACE CRUSHFTPINDX,
	CONSTRAINT CRUSH_WEB_CUSTOM_USER_FK FOREIGN KEY (USERID) REFERENCES CRUSHFTP.USERS (USERID) ON DELETE CASCADE);

CREATE UNIQUE INDEX CRUSHFTP.IDX_CRUSH_WEB_CUSTOM_USERID_SO ON CRUSHFTP.WEB_CUSTOMIZATIONS (USERID, SORT_ORDER)
	PCTFREE 10 INITRANS 2 MAXTRANS 255
	STORAGE (INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
	TABLESPACE CRUSHFTPINDX;

CREATE TABLE CRUSHFTP.MODIFIED_TIMES ( 
  SERVER_GROUP VARCHAR2(50), 
  PROP_NAME VARCHAR2(255), 
  PROP_VALUE VARCHAR2(255) 
  ) 
  TABLESPACE CRUSHFTPDATA 
; 

CREATE SEQUENCE CRUSHFTP.USERS_SEQ 
INCREMENT BY 1 
NOMAXVALUE 
START WITH 1 
MINVALUE 1 
NOCACHE 
NOCYCLE 
ORDER; 

-- trigger to get the next sequence-value for userid 
CREATE OR REPLACE TRIGGER CRUSHFTP.USERS_TRG BEFORE INSERT OR UPDATE ON CRUSHFTP.USERS 
FOR EACH ROW 
DECLARE 
v_newVal NUMBER(12) := 0; 
v_incval NUMBER(12) := 0; 
BEGIN 
  IF INSERTING AND :new.USERID IS NULL THEN 
    SELECT USERS_SEQ.NEXTVAL INTO v_newVal FROM DUAL; 
    IF v_newVal = 1 THEN 
      SELECT NVL(max(USERID),0) INTO v_newVal FROM USERS; 
      v_newVal := v_newVal + 1; 
      LOOP 
           EXIT WHEN v_incval>=v_newVal; 
           SELECT USERS_SEQ.nextval INTO v_incval FROM dual; 
      END LOOP; 
    END IF; 
   :new.USERID := v_newVal; 
  END IF; 
END;