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

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

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

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

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

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

--
--	CREATE TABLE VFS (Virtual File System)
--
CREATE TABLE CRUSHFTP.VFS (
	USERID      NUMBER(8) NOT NULL,
	URL         VARCHAR2(255),
	TYPE        VARCHAR2(50),
	PATH        VARCHAR2(255),
	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;

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

--
--	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),
	FOR_MENU         VARCHAR2(10),
	FOR_CONTEXT_MENU VARCHAR2(10),
	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;

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

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

--
--	CREATE USER_PROPERTIES
--
CREATE TABLE CRUSHFTP.VFS_PROPERTIES (
	USERID      NUMBER(8) NOT NULL,
	PATH   VARCHAR2(1024),
	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;


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

--following is the additional table
CREATE TABLE CRUSHFTP.CRUSHFTP_LOG (
	  LOG_ROW_NUM NUMBER NOT NULL PRIMARY KEY,
	  LOG_MILLIS NUMBER NOT NULL,
	  LOG_TAG VARCHAR2(50) DEFAULT NULL,
	  LOG_DATA VARCHAR2(2000) DEFAULT NULL);