/*$DELIMITER:/ */
CREATE TABLE JCR_MITEM(
	ID VARCHAR2(96) NOT NULL,
	PARENT_ID VARCHAR2(96) NOT NULL,
	NAME VARCHAR2(512) NOT NULL,
	VERSION INTEGER NOT NULL,
	I_CLASS INTEGER NOT NULL,
	I_INDEX INTEGER NOT NULL,
	N_ORDER_NUM INTEGER,
	P_TYPE INTEGER, 
	P_MULTIVALUED INTEGER,	
	CONSTRAINT JCR_PK_MITEM PRIMARY KEY(ID),
	CONSTRAINT JCR_FK_MITEM_PARENT FOREIGN KEY(PARENT_ID) REFERENCES JCR_MITEM(ID)
	)
/
CREATE INDEX JCR_IDX_MITEM_PARENT_FK ON JCR_MITEM(PARENT_ID)
/
CREATE UNIQUE INDEX JCR_IDX_MITEM_PARENT ON JCR_MITEM( PARENT_ID, NAME, I_INDEX, I_CLASS, VERSION DESC)
/
CREATE UNIQUE INDEX JCR_IDX_MITEM_PARENT_NAME ON JCR_MITEM(I_CLASS, PARENT_ID, NAME, I_INDEX, VERSION DESC)
/
CREATE UNIQUE INDEX JCR_IDX_MITEM_PARENT_ID ON JCR_MITEM(I_CLASS, PARENT_ID, ID, VERSION DESC)
/
CREATE INDEX JCR_IDX_MITEM_N_ORDER_NUM ON JCR_MITEM(I_CLASS, PARENT_ID, N_ORDER_NUM)
/
CREATE INDEX JCR_IDX_MITEM_NAME ON JCR_MITEM(I_CLASS, NAME)
/
CREATE TABLE JCR_MVALUE(
	ID INTEGER NOT NULL, 
	DATA BLOB, 
	ORDER_NUM INTEGER NOT NULL, 
	PROPERTY_ID VARCHAR(96) NOT NULL,
	STORAGE_DESC VARCHAR(512),
	CONSTRAINT JCR_PK_MVALUE PRIMARY KEY(ID),
	CONSTRAINT JCR_FK_MVALUE_PROPERTY FOREIGN KEY(PROPERTY_ID) REFERENCES JCR_MITEM(ID)
	)
/
CREATE UNIQUE INDEX JCR_IDX_MVALUE_PROPERTY ON JCR_MVALUE(PROPERTY_ID, ORDER_NUM)
/
CREATE sequence JCR_MVALUE_SEQ
/
CREATE OR REPLACE trigger BI_JCR_MVALUE
  before insert on JCR_MVALUE
  for each row
begin
  SELECT JCR_MVALUE_SEQ.nextval INTO :NEW.ID FROM dual;
end;
/
CREATE TABLE JCR_MREF(
  NODE_ID VARCHAR(96) NOT NULL,
  PROPERTY_ID VARCHAR(96) NOT NULL,
  ORDER_NUM INTEGER NOT NULL,
  CONSTRAINT JCR_PK_MREF PRIMARY KEY(NODE_ID, PROPERTY_ID, ORDER_NUM)
)
/
CREATE UNIQUE INDEX JCR_IDX_MREF_PROPERTY ON JCR_MREF(PROPERTY_ID, ORDER_NUM)
/

ANALYZE INDEX JCR_PK_MITEM COMPUTE STATISTICS
/
ANALYZE INDEX JCR_IDX_MITEM_PARENT_FK COMPUTE STATISTICS
/
ANALYZE INDEX JCR_IDX_MITEM_PARENT COMPUTE STATISTICS
/
ANALYZE INDEX JCR_IDX_MITEM_PARENT_NAME COMPUTE STATISTICS
/
ANALYZE INDEX JCR_IDX_MITEM_N_ORDER_NUM COMPUTE STATISTICS
/
ANALYZE INDEX JCR_IDX_MITEM_PARENT_ID COMPUTE STATISTICS
/
ANALYZE INDEX JCR_PK_MVALUE COMPUTE STATISTICS
/
ANALYZE INDEX JCR_IDX_MVALUE_PROPERTY COMPUTE STATISTICS
/
ANALYZE INDEX JCR_PK_MREF COMPUTE STATISTICS
/
ANALYZE INDEX JCR_IDX_MREF_PROPERTY COMPUTE STATISTICS
/
CREATE SEQUENCE JCR_MITEM_SEQ  INCREMENT BY 1 MINVALUE -1 NOMAXVALUE NOCACHE NOCYCLE
/
CREATE OR REPLACE FUNCTION JCR_MITEM_NEXT_VAL (nameSeq IN VARCHAR, newVal IN INTEGER, increment IN INTEGER )
RETURN INTEGER
AS
result INTEGER;
query VARCHAR(200);

BEGIN
query := 'select '||nameSeq||'.nextval from dual';
execute immediate query into result;

IF (increment = 0)
THEN
  WHILE (result < newVal)
  LOOP
    execute immediate query into result;
  END LOOP;
END IF;
RETURN result;
end;
/
