/*$DELIMITER:/ */
CREATE TABLE JCR_SITEM(
	ID VARCHAR(56) NOT NULL,
	PARENT_ID VARCHAR(56) NOT NULL,
	NAME VARCHAR(512) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
	VERSION INTEGER NOT NULL,
	CONTAINER_NAME VARCHAR(24) NOT NULL,
	I_CLASS INTEGER NOT NULL,
	I_INDEX INTEGER NOT NULL,
	N_ORDER_NUM INTEGER,
	P_TYPE INTEGER, 
	P_MULTIVALUED BOOLEAN,	
	CONSTRAINT JCR_PK_SITEM PRIMARY KEY(ID),
	CONSTRAINT JCR_FK_SITEM_PARENT FOREIGN KEY(PARENT_ID) REFERENCES JCR_SITEM(ID)
) ENGINE=MyISAM/
CREATE UNIQUE INDEX JCR_IDX_SITEM_PARENT ON JCR_SITEM(CONTAINER_NAME, PARENT_ID, NAME(255), I_INDEX, I_CLASS, VERSION DESC)/
CREATE UNIQUE INDEX JCR_IDX_SITEM_PARENT_NAME ON JCR_SITEM(I_CLASS, CONTAINER_NAME, PARENT_ID, NAME(255), 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(255))/
CREATE TABLE JCR_SVALUE(
	ID SERIAL NOT NULL, 
	DATA LONGBLOB,
	ORDER_NUM INTEGER NOT NULL,
	PROPERTY_ID VARCHAR(56) NOT NULL,
	STORAGE_DESC VARCHAR(512),
	CONSTRAINT JCR_PK_SVALUE PRIMARY KEY(ID),
	CONSTRAINT JCR_FK_SVALUE_PROPERTY FOREIGN KEY(PROPERTY_ID) REFERENCES JCR_SITEM(ID)
) ENGINE=MyISAM/
CREATE UNIQUE INDEX JCR_IDX_SVALUE_PROPERTY ON JCR_SVALUE(PROPERTY_ID, ORDER_NUM)/
CREATE INDEX JCR_IDX_SVALUE_STORAGE_DESC ON JCR_SVALUE(PROPERTY_ID, STORAGE_DESC)/
CREATE TABLE JCR_SREF(
  NODE_ID VARCHAR(56) NOT NULL, 
  PROPERTY_ID VARCHAR(56) NOT NULL,
  ORDER_NUM INTEGER NOT NULL,
  CONSTRAINT JCR_PK_SREF PRIMARY KEY(NODE_ID, PROPERTY_ID, ORDER_NUM)
) ENGINE=MyISAM/
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))ENGINE=MyISAM/
CREATE FUNCTION JCR_SITEM_NEXT_VAL (nameSeq VARCHAR(20),newVal INTEGER, increment INTEGER) RETURNS INTEGER
BEGIN
   DECLARE result INTEGER;
   IF (increment = 1)
   THEN
     UPDATE JCR_SITEM_SEQ SET nextVal = LAST_INSERT_ID(nextVal + 1) WHERE name = nameSeq;
   ELSE
      SELECT nextVal INTO result from JCR_SITEM_SEQ where name=nameSeq;
      IF (result < newVal)
      THEN
        UPDATE JCR_SITEM_SEQ SET nextVal = newVal WHERE name = nameSeq;
      END IF;
   END IF;
   SELECT nextVal INTO result from JCR_SITEM_SEQ where name=nameSeq;
   RETURN result;
END/