contrib/orm-persistence/create_db_mysql.sql

DROP DATABASE IF EXISTS jackrabbit  ;

CREATE DATABASE jackrabbit  ;

USE jackrabbit  ;

CREATE TABLE JCR_NODE(
  UUID VARCHAR(36) NOT NULL,
  PARENT_UUID VARCHAR(36),
  DEFINITION_ID VARCHAR(100),
  NODE_TYPE VARCHAR(100), 
  PRIMARY KEY (UUID)
) TYPE=InnoDB;

CREATE TABLE JCR_PROPERTY(
  ITEM_ID VARCHAR(200) NOT NULL, 
  NAME VARCHAR(100),
  VALUE VARCHAR(100),
  PARENT_UUID VARCHAR(36),
  PROP_TYPE INTEGER,
  DEFINITION_ID VARCHAR(100), 
  MULTI_VALUED CHAR(1), 
  PRIMARY KEY (ITEM_ID)
) TYPE=InnoDB;

CREATE TABLE JCR_NODE_MIXIN_TYPE(
  NODE_UUID VARCHAR(36) NOT NULL,
  MIXIN_TYPE VARCHAR(100)
) TYPE=InnoDB;

CREATE TABLE JCR_NODE_PARENT(
  NODE_UUID VARCHAR(36) NOT NULL,
  PARENT_UUID VARCHAR(36)
) TYPE=InnoDB;

CREATE TABLE JCR_CHILD_NODE(
  PARENT_UUID VARCHAR(36) NOT NULL,
  UUID VARCHAR(36),
  NAME VARCHAR(100),
  SAMENAME_INDEX INTEGER,
  CHILDREN_INDEX INTEGER
) TYPE=InnoDB;

CREATE TABLE JCR_NODE_PROPERTY(
  PARENT_UUID VARCHAR(36) NOT NULL,
  NAME VARCHAR(100)
) TYPE=InnoDB;

CREATE TABLE JCR_NODE_REF(
  NREF_ID INTEGER NOT NULL AUTO_INCREMENT, 
  TARGET_UUID VARCHAR(36) NOT NULL,
  PROP_UUID VARCHAR(36),
  PROP_NAME VARCHAR(100), 
  PRIMARY KEY (NREF_ID)
) TYPE=InnoDB;

CREATE TABLE JCR_BLOB(
  BLOB_ID INTEGER NOT NULL AUTO_INCREMENT, 
  PARENT_UUID VARCHAR(36),
  PROP_NAME VARCHAR(100), 
  VALUE_INDEX INTEGER, 
  BLOB_SIZE BIGINT, 
  BLOB_VALUE BLOB,
  PRIMARY KEY(BLOB_ID)
) TYPE=InnoDB;


CREATE INDEX JCR_NODE_PROPERTY_INDEX1 ON JCR_NODE_PROPERTY(PARENT_UUID, NAME);
CREATE INDEX JCR_NODE_PROPERTY_INDEX2 ON JCR_NODE_PROPERTY(PARENT_UUID);

CREATE INDEX JCR_CHILD_NODE_INDEX1 ON JCR_CHILD_NODE (PARENT_UUID, UUID, NAME, SAMENAME_INDEX);
CREATE INDEX JCR_CHILD_NODE_INDEX2 ON JCR_CHILD_NODE (PARENT_UUID, CHILDREN_INDEX);

CREATE INDEX JCR_NODE_MIXIN_TYPE_INDEX1 ON JCR_NODE_MIXIN_TYPE (NODE_UUID, MIXIN_TYPE);
CREATE INDEX JCR_NODE_MIXIN_TYPE_INDEX2 ON JCR_NODE_MIXIN_TYPE (NODE_UUID);

CREATE INDEX JCR_NODE_PARENT_INDEX1 ON JCR_NODE_PARENT (NODE_UUID, PARENT_UUID);
CREATE INDEX JCR_NODE_PARENT_INDEX2 ON JCR_NODE_PARENT (NODE_UUID);

CREATE INDEX JCR_NODE_REF_INDEX1 ON JCR_NODE_REF(TARGET_UUID, PROP_UUID, PROP_NAME);
CREATE INDEX JCR_NODE_REF_INDEX2 ON JCR_NODE_REF(TARGET_UUID);

CREATE INDEX JCR_BLOB_INDEX1 ON JCR_BLOB(PARENT_UUID, PROP_NAME, VALUE_INDEX);