/*$DELIMITER:/ */
CREATE TABLE JCR_SITEM(
	ID VARCHAR(96) NOT NULL,
	PARENT_ID VARCHAR(96) NOT NULL,
	NAME VARCHAR(512) NOT NULL,
	VERSION INT NOT NULL, 
	CONTAINER_NAME VARCHAR(96) NOT NULL,
	I_CLASS INT NOT NULL,
	I_INDEX INT NOT NULL,
	N_ORDER_NUM INT NULL,
	P_TYPE INT NULL, 
	P_MULTIVALUED INT NULL,	
	CONSTRAINT JCR_PK_SITEM PRIMARY KEY(ID),
	CONSTRAINT JCR_FK_SITEM_PARENT FOREIGN KEY(PARENT_ID) REFERENCES JCR_SITEM(ID)
)/
CREATE UNIQUE INDEX JCR_IDX_SITEM_PARENT ON JCR_SITEM(CONTAINER_NAME, PARENT_ID, NAME, I_INDEX, I_CLASS, VERSION DESC)/
CREATE UNIQUE INDEX JCR_IDX_SITEM_PARENT_NAME ON JCR_SITEM(I_CLASS, CONTAINER_NAME, PARENT_ID, NAME, I_INDEX, VERSION DESC)/
CREATE UNIQUE INDEX JCR_IDX_SITEM_PARENT_ID ON JCR_SITEM(I_CLASS, CONTAINER_NAME, PARENT_ID, ID, VERSION DESC)/
CREATE INDEX JCR_IDX_SITEM_N_ORDER_NUM ON JCR_SITEM(I_CLASS, CONTAINER_NAME, PARENT_ID, N_ORDER_NUM)/
CREATE INDEX JCR_IDX_SITEM_NAME ON JCR_SITEM(I_CLASS, CONTAINER_NAME, NAME)/
CREATE TABLE JCR_SVALUE(
    ID BIGINT IDENTITY NOT NULL, 
	DATA IMAGE NULL,
    ORDER_NUM INT NOT NULL,
    PROPERTY_ID VARCHAR(96) NOT NULL,
	STORAGE_DESC VARCHAR(512) NULL,
	CONSTRAINT JCR_PK_SVALUE PRIMARY KEY(ID),
    CONSTRAINT JCR_FK_SVALUE_PROPERTY FOREIGN KEY(PROPERTY_ID) REFERENCES JCR_SITEM(ID)
)/
CREATE UNIQUE INDEX JCR_IDX_SVALUE_PROPERTY ON JCR_SVALUE(PROPERTY_ID, ORDER_NUM)/
CREATE TABLE JCR_SREF(
  NODE_ID VARCHAR(96) NOT NULL,
  PROPERTY_ID VARCHAR(96) NOT NULL,
  ORDER_NUM INT NOT NULL,
  CONSTRAINT JCR_PK_SREF PRIMARY KEY(NODE_ID, PROPERTY_ID, ORDER_NUM)
)/
CREATE UNIQUE INDEX JCR_IDX_SREF_PROPERTY ON JCR_SREF(PROPERTY_ID, ORDER_NUM)/
CREATE TABLE JCR_SITEM_SEQ (
    name VARCHAR(20) NOT NULL,
    nextVal INTEGER NOT NULL,
    CONSTRAINT JCR_PK_SITEM_SEQ PRIMARY KEY (name)
)/
CREATE PROCEDURE JCR_SITEM_NEXT_VAL (@nameSeq VARCHAR(20), @newVal INTEGER, @increment INTEGER)
AS
BEGIN
SET NOCOUNT ON
DECLARE @seq INTEGER

IF (@increment = 1)
  UPDATE JCR_SITEM_SEQ SET nextVal = nextVal + 1 WHERE name = @nameSeq
ELSE
  BEGIN
    SELECT @seq = nextVal FROM JCR_SITEM_SEQ where name=@nameSeq
    IF (@seq < @newVal)
      UPDATE JCR_SITEM_SEQ SET nextVal = @newVal WHERE name = @nameSeq
  END
SELECT @seq = nextVal FROM JCR_SITEM_SEQ where name=@nameSeq
select nextvalue= @seq
return
END/