ozeigermann 2004/02/25 01:13:20
Added: src/conf/schema dropPostgresSchema.sql MySqlSchema.sql
SybaseSchema.sql SQLServerSchema.sql
createPostgresSchema.sql
Removed: src/stores/org/apache/slide/store/impl/rdbms MySqlSchema.sql
SQLServerSchema.sql SybaseSchema.sql
dropPostgresSchema.sql createPostgresSchema.sql
Log:
Moved schemas from src to conf to have it available in future binary releases
Revision Changes Path
1.1 jakarta-slide/src/conf/schema/dropPostgresSchema.sql
Index: dropPostgresSchema.sql
===================================================================
/*
* $Header: /home/cvs/jakarta-slide/src/conf/schema/dropPostgresSchema.sql,v 1.1
2004/02/25 09:13:20 ozeigermann Exp $
* $Revision: 1.1 $
* $Date: 2004/02/25 09:13:20 $
*
* ====================================================================
*
* Copyright 1999-2002 The Apache Software Foundation
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
*/
/*
* drop the SQL schema used by org.apache.slide.impl.rdbms.PostgresAdapter.
* Tested with Postgres 7.4.
*
*/
DROP VIEW LOCKS_VIEW;
DROP VIEW PERMISSIONS_VIEW;
DROP VIEW BINDING_VIEW;
DROP VIEW OBJECT_VIEW;
DROP TABLE PROPERTIES;
DROP TABLE VERSION_CONTENT;
DROP TABLE VERSION_PREDS;
DROP TABLE VERSION_LABELS;
DROP TABLE VERSION_HISTORY;
DROP TABLE VERSION;
DROP TABLE BINDING;
DROP TABLE PARENT_BINDING;
DROP TABLE LINKS;
DROP TABLE LOCKS;
DROP TABLE BRANCH;
DROP TABLE LABEL;
DROP TABLE PERMISSIONS;
DROP TABLE OBJECT;
DROP TABLE URI;
1.1 jakarta-slide/src/conf/schema/MySqlSchema.sql
Index: MySqlSchema.sql
===================================================================
# --------------------------------------------------------
drop table if exists OBJECT;
drop table if exists CHILDREN;
drop table if exists BINDING;
drop table if exists PARENT_BINDING;
drop table if exists LINKS;
drop table if exists LOCKS;
drop table if exists VERSION_CONTENT;
drop table if exists PROPERTIES;
drop table if exists PERMISSIONS;
drop table if exists VERSION_PREDS;
drop table if exists VERSION_LABELS;
drop table if exists VERSION_HISTORY;
drop table if exists VERSION;
drop table if exists BRANCH;
drop table if exists LABEL;
drop table if exists URI;
# --------------------------------------------------------
CREATE TABLE URI (
URI_ID bigint NOT NULL auto_increment,
URI_STRING varchar(255) NOT NULL,
PRIMARY KEY (URI_ID),
KEY URI_IX2 (URI_STRING)
) TYPE=InnoDB;
# --------------------------------------------------------
CREATE TABLE OBJECT (
URI_ID bigint NOT NULL,
CLASS_NAME varchar(255) NOT NULL,
PRIMARY KEY (URI_ID)
) TYPE=InnoDB;
ALTER TABLE OBJECT ADD CONSTRAINT OBJECT_FK1 FOREIGN KEY OBJECT_FK1(URI_ID)
REFERENCES URI (URI_ID);
# --------------------------------------------------------
CREATE TABLE BINDING (
URI_ID bigint NOT NULL,
NAME varchar(255) NOT NULL,
CHILD_UURI_ID bigint NOT NULL,
PRIMARY KEY (URI_ID, NAME, CHILD_UURI_ID),
KEY BINDING_IX (CHILD_UURI_ID)
) TYPE=InnoDB;
ALTER TABLE BINDING ADD CONSTRAINT BINDING_FK1 FOREIGN KEY
BINDING_FK1(URI_ID) REFERENCES URI (URI_ID);
ALTER TABLE BINDING ADD CONSTRAINT BINDING_FK2 FOREIGN KEY
BINDING_FK2(CHILD_UURI_ID) REFERENCES URI (URI_ID);
CREATE TABLE PARENT_BINDING (
URI_ID bigint NOT NULL,
NAME varchar(255) NOT NULL,
PARENT_UURI_ID bigint NOT NULL,
PRIMARY KEY (URI_ID, NAME, PARENT_UURI_ID),
KEY PARENT_BINDING_IX (PARENT_UURI_ID)
) TYPE=InnoDB;
ALTER TABLE PARENT_BINDING ADD CONSTRAINT PARENT_BINDING_FK1 FOREIGN KEY
PARENT_BINDING_FK1(URI_ID) REFERENCES URI (URI_ID);
ALTER TABLE PARENT_BINDING ADD CONSTRAINT PARENT_BINDING_FK2 FOREIGN KEY
PARENT_BINDING_FK2(PARENT_UURI_ID) REFERENCES URI (URI_ID);
# --------------------------------------------------------
CREATE TABLE LINKS (
URI_ID bigint NOT NULL,
LINK_TO_ID bigint NOT NULL,
PRIMARY KEY (URI_ID,LINK_TO_ID),
key LINK_IX2 (LINK_TO_ID)
) TYPE=InnoDB;
ALTER TABLE LINKS ADD CONSTRAINT LINK_FK1 FOREIGN KEY LINK_FK1(URI_ID)
REFERENCES URI (URI_ID);
ALTER TABLE LINKS ADD CONSTRAINT LINK_FK2 FOREIGN KEY LINK_FK2(LINK_TO_ID)
REFERENCES URI (URI_ID);
# --------------------------------------------------------
CREATE TABLE LOCKS (
LOCK_ID bigint NOT NULL,
OBJECT_ID bigint NOT NULL,
SUBJECT_ID bigint NOT NULL,
TYPE_ID bigint NOT NULL,
EXPIRATION_DATE bigint NOT NULL,
IS_INHERITABLE tinyint(1) NOT NULL,
IS_EXCLUSIVE tinyint(1) NOT NULL,
OWNER varchar(255),
PRIMARY KEY (LOCK_ID),
KEY LOCK_IX2 (OBJECT_ID),
KEY LOCK_IX3 (SUBJECT_ID),
KEY LOCK_IX4 (TYPE_ID)
) TYPE=InnoDB;
ALTER TABLE LOCKS ADD CONSTRAINT LOCK_FK1 FOREIGN KEY LOCK_FK1(LOCK_ID)
REFERENCES URI (URI_ID);
ALTER TABLE LOCKS ADD CONSTRAINT LOCK_FK2 FOREIGN KEY LOCK_FK2(OBJECT_ID)
REFERENCES URI (URI_ID);
ALTER TABLE LOCKS ADD CONSTRAINT LOCK_FK3 FOREIGN KEY LOCK_FK3(SUBJECT_ID)
REFERENCES URI (URI_ID);
ALTER TABLE LOCKS ADD CONSTRAINT LOCK_FK4 FOREIGN KEY LOCK_FK4(TYPE_ID)
REFERENCES URI (URI_ID);
# --------------------------------------------------------
CREATE TABLE BRANCH (
BRANCH_ID bigint NOT NULL auto_increment,
BRANCH_STRING varchar(255) NOT NULL,
PRIMARY KEY (BRANCH_ID)
) TYPE=InnoDB;
# --------------------------------------------------------
CREATE TABLE LABEL (
LABEL_ID bigint NOT NULL auto_increment,
LABEL_STRING varchar(255) NOT NULL,
PRIMARY KEY (LABEL_ID)
) TYPE=InnoDB;
# --------------------------------------------------------
CREATE TABLE VERSION (
URI_ID bigint NOT NULL,
IS_VERSIONED tinyint(1) NOT NULL,
PRIMARY KEY (URI_ID)
) TYPE=InnoDB;
ALTER TABLE VERSION ADD CONSTRAINT REVISON_FK1 FOREIGN KEY
VERSION_FK1(URI_ID) REFERENCES URI (URI_ID);
# --------------------------------------------------------
CREATE TABLE VERSION_HISTORY (
VERSION_ID bigint NOT NULL auto_increment,
URI_ID bigint NOT NULL,
BRANCH_ID bigint NOT NULL,
REVISION_NO varchar(20) NOT NULL,
PRIMARY KEY (VERSION_ID),
UNIQUE KEY URI_ID (BRANCH_ID,URI_ID,REVISION_NO),
KEY URI_ID_IX3 (URI_ID)
) TYPE=InnoDB;
ALTER TABLE VERSION_HISTORY ADD CONSTRAINT VERSION_HISTORY_FK1 FOREIGN KEY
VERSION_HISTORY_FK1(URI_ID) REFERENCES URI (URI_ID);
ALTER TABLE VERSION_HISTORY ADD CONSTRAINT VERSION_HISTORY_FK2 FOREIGN KEY
VERSION_HISTORY_FK2(BRANCH_ID) REFERENCES BRANCH (BRANCH_ID);
# --------------------------------------------------------
CREATE TABLE VERSION_PREDS (
VERSION_ID bigint NOT NULL,
PREDECESSOR_ID bigint NOT NULL,
PRIMARY KEY (VERSION_ID,PREDECESSOR_ID),
KEY PREDECESSOR_ID_IX2 (PREDECESSOR_ID)
) TYPE=InnoDB;
ALTER TABLE VERSION_PREDS ADD CONSTRAINT REVISON_PREDS_FK1 FOREIGN KEY
VERSION_PREDS_FK1(VERSION_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
ALTER TABLE VERSION_PREDS ADD CONSTRAINT REVISON_PREDS_FK2 FOREIGN KEY
VERSION_PREDS_FK2(PREDECESSOR_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
# --------------------------------------------------------
CREATE TABLE VERSION_LABELS (
VERSION_ID bigint NOT NULL,
LABEL_ID bigint NOT NULL,
PRIMARY KEY (VERSION_ID,LABEL_ID),
KEY LABEL_ID_IX2 (LABEL_ID)
) TYPE=InnoDB;
ALTER TABLE VERSION_LABELS ADD CONSTRAINT REVISON_LABELS_FK1 FOREIGN KEY
VERSION_LABELS_FK1(VERSION_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
ALTER TABLE VERSION_LABELS ADD CONSTRAINT REVISON_LABELS_FK2 FOREIGN KEY
VERSION_LABELS_FK2(LABEL_ID) REFERENCES LABEL (LABEL_ID);
# --------------------------------------------------------
CREATE TABLE VERSION_CONTENT (
VERSION_ID bigint NOT NULL,
CONTENT blob NOT NULL,
PRIMARY KEY (VERSION_ID)
) TYPE=InnoDB;
ALTER TABLE VERSION_CONTENT ADD CONSTRAINT REVISON_CONTENT_FK1 FOREIGN KEY
VERSION_CONTENT_FK1(VERSION_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
# --------------------------------------------------------
CREATE TABLE PROPERTIES (
VERSION_ID bigint NOT NULL,
PROPERTY_NAMESPACE varchar(50) NOT NULL,
PROPERTY_NAME varchar(50) NOT NULL,
PROPERTY_VALUE varchar(255) NOT NULL,
PROPERTY_TYPE varchar(50) NOT NULL,
IS_PROTECTED tinyint(1) NOT NULL,
PRIMARY KEY (VERSION_ID,PROPERTY_NAMESPACE,PROPERTY_NAME)
) TYPE=InnoDB;
ALTER TABLE PROPERTIES ADD CONSTRAINT PROPERTIES_FK1 FOREIGN KEY
PROPERTIES_FK1(VERSION_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
# --------------------------------------------------------
CREATE TABLE PERMISSIONS (
OBJECT_ID bigint NOT NULL,
SUBJECT_ID bigint NOT NULL,
ACTION_ID bigint NOT NULL,
VERSION_NO varchar(20),
IS_INHERITABLE tinyint(1) NOT NULL,
IS_NEGATIVE tinyint(1) NOT NULL,
SUCCESSION int NOT NULL,
PRIMARY KEY (SUBJECT_ID,OBJECT_ID,ACTION_ID),
UNIQUE KEY OBJECT_ID_2 (OBJECT_ID,SUCCESSION),
KEY ACTION_ID_IX3 (ACTION_ID)
) TYPE=InnoDB;
ALTER TABLE PERMISSIONS ADD CONSTRAINT PERMISSIONS_FK1 FOREIGN KEY
PERMISSIONS_FK1(OBJECT_ID) REFERENCES URI (URI_ID);
ALTER TABLE PERMISSIONS ADD CONSTRAINT PERMISSIONS_FK2 FOREIGN KEY
PERMISSIONS_FK2(SUBJECT_ID) REFERENCES URI (URI_ID);
ALTER TABLE PERMISSIONS ADD CONSTRAINT PERMISSIONS_FK3 FOREIGN KEY
PERMISSIONS_FK3(ACTION_ID) REFERENCES URI (URI_ID);
1.1 jakarta-slide/src/conf/schema/SybaseSchema.sql
Index: SybaseSchema.sql
===================================================================
/**********************************************************************/
/****** DROP SLIDE TABLES ******/
/**********************************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'OBJECT')
DROP TABLE OBJECT
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'CHILDREN')
DROP TABLE CHILDREN
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'BINDING')
DROP TABLE BINDING
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'PARENT_BINDING')
DROP TABLE PARENT_BINDING
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'LINKS')
DROP TABLE LINKS
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'LOCKS')
DROP TABLE LOCKS
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'VERSION_CONTENT')
DROP TABLE VERSION_CONTENT
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'PROPERTIES')
DROP TABLE PROPERTIES
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'PERMISSIONS')
DROP TABLE PERMISSIONS
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'VERSION_PREDS')
DROP TABLE VERSION_PREDS
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'VERSION_LABELS')
DROP TABLE VERSION_LABELS
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'VERSION_HISTORY')
DROP TABLE VERSION_HISTORY
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'VERSION')
DROP TABLE VERSION
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'BRANCH')
DROP TABLE BRANCH
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'LABEL')
DROP TABLE LABEL
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'URI')
DROP TABLE URI
GO
/**********************************************************************/
/****** DROP EXISTING USER DEFINED DATA TYPES ******/
/**********************************************************************/
sp_droptype id_type
GO
sp_droptype uri_str_type
GO
sp_droptype revision_no_type
GO
sp_droptype hash_type
GO
sp_droptype literal_str_type
GO
sp_droptype value_str_type
GO
/**********************************************************************/
/****** ADD USER DEFINED DATA TYPES ******/
/**********************************************************************/
sp_addtype id_type, "numeric(18,0)"
GO
sp_addtype uri_str_type, "varchar(255)"
GO
sp_addtype revision_no_type, "varchar(20)"
GO
sp_addtype hash_type, "numeric(18,0)"
GO
sp_addtype value_str_type, "varchar(255)"
GO
/**********************************************************************/
/****** CREATE SLIDE TABLES ******/
/**********************************************************************/
CREATE TABLE dbo.URI (
URI_ID id_type IDENTITY UNIQUE NOT NULL,
URI_STRING uri_str_type UNIQUE NOT NULL,
-- UNIQUE NONCLUSTERED (URI_ID)
)
GO
CREATE INDEX XUID
ON URI(URI_ID)
GO
CREATE INDEX XUSTRING
ON URI(URI_STRING)
GO
CREATE TABLE dbo.OBJECT (
URI_ID id_type PRIMARY KEY,
CLASS_NAME varchar(255) NOT NULL,
CONSTRAINT FK_OBJECT_URI_ID
FOREIGN KEY (URI_ID)
REFERENCES URI (URI_ID)
)
GO
CREATE TABLE dbo.BINDING (
URI_ID id_type NOT NULL
REFERENCES URI (URI_ID),
NAME varchar(238) NOT NULL, -- index must not be more than
256 bytes
CHILD_UURI_ID id_type NOT NULL
REFERENCES URI (URI_ID),
UNIQUE CLUSTERED (URI_ID, NAME, CHILD_UURI_ID)
)
GO
CREATE TABLE dbo.PARENT_BINDING (
URI_ID id_type NOT NULL
REFERENCES URI (URI_ID),
NAME varchar(238) NOT NULL, -- index must not be more than
256 bytes
PARENT_UURI_ID id_type NOT NULL
REFERENCES URI (URI_ID),
UNIQUE CLUSTERED (URI_ID, NAME, PARENT_UURI_ID)
)
GO
CREATE INDEX XCHILDREN1
ON CHILDREN(URI_ID)
GO
CREATE INDEX XCHILDREN2
ON CHILDREN(CHILD_URI_ID)
GO
-- early versions of Sybase do not allow more than 16 tables per query
-- URI has too many foreign keys which internally add to the tables used in a query
on it
-- remove foreign keys to URI from links as they are likely to be used little
CREATE TABLE dbo.LINKS (
URI_ID id_type NOT NULL
/* REFERENCES URI (URI_ID) */ ,
LINK_TO_ID id_type NOT NULL
/* REFERENCES URI (URI_ID) */ ,
--UNIQUE CLUSTERED (URI_ID, LINK_TO_ID)
)
GO
CREATE INDEX XURI_ID
ON LINKS(URI_ID)
GO
CREATE INDEX XLINK_TO_ID
ON LINKS(LINK_TO_ID)
GO
CREATE TABLE dbo.LOCKS (
LOCK_ID id_type PRIMARY KEY,
OBJECT_ID id_type NOT NULL
REFERENCES URI (URI_ID),
SUBJECT_ID id_type NOT NULL
REFERENCES URI (URI_ID),
TYPE_ID id_type NOT NULL
REFERENCES URI (URI_ID),
EXPIRATION_DATE numeric(14, 0) NOT NULL,
IS_INHERITABLE bit NOT NULL,
IS_EXCLUSIVE bit NOT NULL,
OWNER varchar(255),
CONSTRAINT FK_LOCKS_LOCK_ID
FOREIGN KEY (LOCK_ID)
REFERENCES URI (URI_ID)
)
GO
CREATE TABLE dbo.BRANCH (
BRANCH_ID id_type IDENTITY UNIQUE NOT NULL,
BRANCH_STRING varchar(255) UNIQUE NOT NULL
)
GO
CREATE TABLE dbo.LABEL (
LABEL_ID id_type IDENTITY UNIQUE NOT NULL,
LABEL_STRING varchar(255) NOT NULL
)
GO
CREATE TABLE dbo.VERSION (
URI_ID id_type PRIMARY KEY,
IS_VERSIONED bit NOT NULL,
CONSTRAINT FK_VERSION_URI_ID
FOREIGN KEY (URI_ID)
REFERENCES URI (URI_ID)
)
GO
CREATE TABLE dbo.VERSION_HISTORY (
VERSION_ID id_type IDENTITY UNIQUE NOT NULL,
URI_ID id_type NOT NULL
REFERENCES VERSION (URI_ID),
BRANCH_ID id_type NOT NULL
REFERENCES BRANCH (BRANCH_ID),
REVISION_NO VARCHAR(20) NOT NULL,
--UNIQUE CLUSTERED (URI_ID, BRANCH_ID, REVISION_NO)
)
GO
CREATE INDEX XVERSION_HISTORY1
ON VERSION_HISTORY(URI_ID, BRANCH_ID, REVISION_NO)
GO
CREATE TABLE dbo.VERSION_PREDS (
VERSION_ID id_type NOT NULL
REFERENCES VERSION_HISTORY (VERSION_ID),
PREDECESSOR_ID id_type NOT NULL
REFERENCES VERSION_HISTORY (VERSION_ID),
UNIQUE CLUSTERED (VERSION_ID, PREDECESSOR_ID)
)
GO
CREATE INDEX XVERSION_PREDS1
ON VERSION_PREDS(VERSION_ID, PREDECESSOR_ID)
GO
CREATE TABLE dbo.VERSION_LABELS (
VERSION_ID id_type NOT NULL
REFERENCES VERSION_HISTORY (VERSION_ID),
LABEL_ID id_type NOT NULL
REFERENCES LABEL (LABEL_ID),
UNIQUE CLUSTERED (VERSION_ID, LABEL_ID)
)
GO
CREATE TABLE dbo.VERSION_CONTENT (
VERSION_ID id_type PRIMARY KEY,
CONTENT image NOT NULL,
CONSTRAINT FK_VC_VERSION_ID
FOREIGN KEY (VERSION_ID)
REFERENCES VERSION_HISTORY (VERSION_ID),
)
GO
CREATE TABLE dbo.PROPERTIES (
VERSION_ID id_type NOT NULL
REFERENCES VERSION_HISTORY (VERSION_ID),
PROPERTY_NAMESPACE varchar(50) NOT NULL,
PROPERTY_NAME varchar(50) NOT NULL,
PROPERTY_VALUE varchar(255) NOT NULL,
PROPERTY_TYPE varchar(50) NOT NULL,
IS_PROTECTED bit NOT NULL,
UNIQUE CLUSTERED (VERSION_ID, PROPERTY_NAMESPACE, PROPERTY_NAME)
)
GO
CREATE TABLE dbo.PERMISSIONS (
OBJECT_ID id_type NOT NULL
REFERENCES URI (URI_ID),
SUBJECT_ID id_type NOT NULL
REFERENCES URI (URI_ID),
ACTION_ID id_type NOT NULL
REFERENCES URI (URI_ID),
VERSION_NO VARCHAR(20) NULL,
IS_INHERITABLE bit NOT NULL,
IS_NEGATIVE bit NOT NULL,
-- Both order and sequence would be more suitable, but can not be used
SUCCESSION int NOT NULL,
UNIQUE CLUSTERED (OBJECT_ID, SUBJECT_ID, ACTION_ID),
UNIQUE (OBJECT_ID, SUCCESSION)
)
GO
1.1 jakarta-slide/src/conf/schema/SQLServerSchema.sql
Index: SQLServerSchema.sql
===================================================================
/**********************************************************************/
/****** DROP SLIDE TABLES ******/
/**********************************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[OBJECT]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[OBJECT]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BINDING]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[BINDING]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[PARENT_BINDING]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[PARENT_BINDING]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CHILDREN]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[CHILDREN]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[LINKS]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[LINKS]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[LOCKS]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[LOCKS]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[VERSION_CONTENT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[VERSION_CONTENT]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PROPERTIES]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[PROPERTIES]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PERMISSIONS]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[PERMISSIONS]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[VERSION_PREDS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[VERSION_PREDS]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[VERSION_LABELS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[VERSION_LABELS]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[VERSION_HISTORY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[VERSION_HISTORY]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[VERSION]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[VERSION]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BRANCH]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[BRANCH]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[LABEL]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[LABEL]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[URI]') AND
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[URI]
GO
/**********************************************************************/
/****** DROP EXISTING USER DEFINED DATA TYPES ******/
/**********************************************************************/
sp_droptype id_type
GO
sp_droptype uri_str_type
GO
sp_droptype revision_no_type
GO
sp_droptype hash_type
GO
sp_droptype literal_str_type
GO
sp_droptype value_str_type
GO
/**********************************************************************/
/****** ADD USER DEFINED DATA TYPES ******/
/**********************************************************************/
sp_addtype id_type, bigint
GO
sp_addtype uri_str_type, "nvarchar(800)"
GO
sp_addtype revision_no_type, "nvarchar(20)"
GO
sp_addtype hash_type, bigint
GO
sp_addtype literal_str_type, "nvarchar(3000)"
GO
sp_addtype value_str_type, "nvarchar(255)"
GO
/**********************************************************************/
/****** CREATE SLIDE TABLES ******/
/**********************************************************************/
CREATE TABLE dbo.URI (
URI_ID id_type IDENTITY UNIQUE NOT NULL,
URI_STRING uri_str_type UNIQUE NOT NULL,
-- UNIQUE NONCLUSTERED (URI_ID)
)
GO
CREATE INDEX XUID
ON URI(URI_ID)
GO
CREATE INDEX XUSTRING
ON URI(URI_STRING)
GO
CREATE TABLE dbo.OBJECT (
URI_ID id_type PRIMARY KEY,
CLASS_NAME nvarchar(255) NOT NULL,
CONSTRAINT FK_OBJECT_URI_ID
FOREIGN KEY (URI_ID)
REFERENCES URI (URI_ID)
)
GO
CREATE TABLE dbo.BINDING (
URI_ID id_type NOT NULL
REFERENCES URI (URI_ID),
NAME uri_str_type NOT NULL,
CHILD_UURI_ID id_type NOT NULL
REFERENCES URI (URI_ID),
UNIQUE CLUSTERED (URI_ID, NAME, CHILD_UURI_ID)
)
GO
CREATE TABLE dbo.PARENT_BINDING (
URI_ID id_type NOT NULL
REFERENCES URI (URI_ID),
NAME uri_str_type NOT NULL,
PARENT_UURI_ID id_type NOT NULL
REFERENCES URI (URI_ID),
UNIQUE CLUSTERED (URI_ID, NAME, PARENT_UURI_ID)
)
GO
CREATE TABLE dbo.LINKS (
URI_ID id_type NOT NULL FOREIGN KEY
REFERENCES URI (URI_ID),
LINK_TO_ID id_type NOT NULL FOREIGN KEY
REFERENCES URI (URI_ID),
--UNIQUE CLUSTERED (URI_ID, LINK_TO_ID)
)
GO
CREATE INDEX XURI_ID
ON LINKS(URI_ID)
GO
CREATE INDEX XLINK_TO_ID
ON LINKS(LINK_TO_ID)
GO
CREATE TABLE dbo.LOCKS (
LOCK_ID id_type PRIMARY KEY,
OBJECT_ID id_type NOT NULL FOREIGN KEY
REFERENCES URI (URI_ID),
SUBJECT_ID id_type NOT NULL FOREIGN KEY
REFERENCES URI (URI_ID),
TYPE_ID id_type NOT NULL FOREIGN KEY
REFERENCES URI (URI_ID),
EXPIRATION_DATE numeric(14, 0) NOT NULL,
IS_INHERITABLE bit NOT NULL,
IS_EXCLUSIVE bit NOT NULL,
OWNER nvarchar(255),
CONSTRAINT FK_LOCKS_LOCK_ID
FOREIGN KEY (LOCK_ID)
REFERENCES URI (URI_ID)
)
GO
CREATE TABLE dbo.BRANCH (
BRANCH_ID id_type IDENTITY UNIQUE NOT NULL,
BRANCH_STRING nvarchar(255) UNIQUE NOT NULL,
UNIQUE NONCLUSTERED (BRANCH_ID)
)
GO
CREATE TABLE dbo.LABEL (
LABEL_ID id_type IDENTITY UNIQUE NOT NULL,
LABEL_STRING nvarchar(255) NOT NULL,
UNIQUE NONCLUSTERED (LABEL_ID)
)
GO
CREATE TABLE dbo.VERSION (
URI_ID id_type PRIMARY KEY,
IS_VERSIONED bit NOT NULL,
CONSTRAINT FK_VERSION_URI_ID
FOREIGN KEY (URI_ID)
REFERENCES URI (URI_ID)
)
GO
CREATE TABLE dbo.VERSION_HISTORY (
VERSION_ID id_type IDENTITY UNIQUE NOT NULL,
URI_ID id_type NOT NULL FOREIGN KEY
REFERENCES VERSION (URI_ID),
BRANCH_ID id_type NOT NULL FOREIGN KEY
REFERENCES BRANCH (BRANCH_ID),
REVISION_NO nVARCHAR(20) NOT NULL,
--UNIQUE CLUSTERED (URI_ID, BRANCH_ID, REVISION_NO)
)
GO
CREATE INDEX XVERSION_HISTORY1
ON VERSION_HISTORY(URI_ID, BRANCH_ID, REVISION_NO)
GO
CREATE TABLE dbo.VERSION_PREDS (
VERSION_ID id_type NOT NULL FOREIGN KEY
REFERENCES VERSION_HISTORY (VERSION_ID),
PREDECESSOR_ID id_type NOT NULL FOREIGN KEY
REFERENCES VERSION_HISTORY (VERSION_ID),
UNIQUE CLUSTERED (VERSION_ID, PREDECESSOR_ID)
)
GO
CREATE INDEX XVERSION_PREDS1
ON VERSION_PREDS(VERSION_ID, PREDECESSOR_ID)
GO
CREATE TABLE dbo.VERSION_LABELS (
VERSION_ID id_type NOT NULL FOREIGN KEY
REFERENCES VERSION_HISTORY (VERSION_ID),
LABEL_ID id_type NOT NULL FOREIGN KEY
REFERENCES LABEL (LABEL_ID),
UNIQUE CLUSTERED (VERSION_ID, LABEL_ID)
)
GO
CREATE TABLE dbo.VERSION_CONTENT (
VERSION_ID id_type PRIMARY KEY,
CONTENT image NOT NULL,
CONSTRAINT FK_VC_VERSION_ID
FOREIGN KEY (VERSION_ID)
REFERENCES VERSION_HISTORY (VERSION_ID),
)
GO
CREATE TABLE dbo.PROPERTIES (
VERSION_ID id_type NOT NULL FOREIGN KEY
REFERENCES VERSION_HISTORY (VERSION_ID),
PROPERTY_NAMESPACE nvarchar(50) NOT NULL,
PROPERTY_NAME nvarchar(50) NOT NULL,
PROPERTY_VALUE nvarchar(255) NOT NULL,
PROPERTY_TYPE nvarchar(50) NOT NULL,
IS_PROTECTED bit NOT NULL,
UNIQUE CLUSTERED (VERSION_ID, PROPERTY_NAMESPACE, PROPERTY_NAME)
)
GO
CREATE TABLE dbo.PERMISSIONS (
OBJECT_ID id_type NOT NULL FOREIGN KEY
REFERENCES URI (URI_ID),
SUBJECT_ID id_type NOT NULL FOREIGN KEY
REFERENCES URI (URI_ID),
ACTION_ID id_type NOT NULL FOREIGN KEY
REFERENCES URI (URI_ID),
VERSION_NO nVARCHAR(20) NULL,
IS_INHERITABLE bit NOT NULL,
IS_NEGATIVE bit NOT NULL,
-- Both order and sequence would be more suitable, but can not be used
SUCCESSION int NOT NULL,
UNIQUE CLUSTERED (OBJECT_ID, SUBJECT_ID, ACTION_ID),
UNIQUE (OBJECT_ID, SUCCESSION)
)
GO
1.1 jakarta-slide/src/conf/schema/createPostgresSchema.sql
Index: createPostgresSchema.sql
===================================================================
/*
* $Header: /home/cvs/jakarta-slide/src/conf/schema/createPostgresSchema.sql,v 1.1
2004/02/25 09:13:20 ozeigermann Exp $
* $Revision: 1.1 $
* $Date: 2004/02/25 09:13:20 $
*
* ====================================================================
*
* Copyright 1999-2002 The Apache Software Foundation
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
*/
/*
* create the SQL schema used by org.apache.slide.impl.rdbms.PostgresAdapter.
* Tested with Postgres 7.4.
*
*/
CREATE TABLE URI (
URI_ID serial PRIMARY KEY NOT NULL,
URI_STRING varchar(800) UNIQUE NOT NULL
);
CREATE TABLE OBJECT (
URI_ID integer PRIMARY KEY,
CLASS_NAME varchar(255) NOT NULL,
CONSTRAINT FK_OBJECT_URI_ID
FOREIGN KEY (URI_ID)
REFERENCES URI (URI_ID)
);
CREATE TABLE BINDING (
URI_ID integer NOT NULL REFERENCES URI(URI_ID),
NAME varchar(255) NOT NULL,
CHILD_UURI_ID integer NOT NULL REFERENCES URI(URI_ID),
PRIMARY KEY (URI_ID, NAME, CHILD_UURI_ID)
);
CREATE TABLE PARENT_BINDING (
URI_ID integer NOT NULL REFERENCES URI (URI_ID),
NAME varchar(255) NOT NULL,
PARENT_UURI_ID integer NOT NULL REFERENCES URI (URI_ID),
PRIMARY KEY (URI_ID, NAME, PARENT_UURI_ID)
);
/* TODO Which indices for binding? */
CREATE TABLE LINKS (
URI_ID integer NOT NULL REFERENCES URI (URI_ID),
LINK_TO_ID integer NOT NULL REFERENCES URI (URI_ID),
UNIQUE (URI_ID, LINK_TO_ID)
);
CREATE INDEX XURI_ID
ON LINKS(URI_ID);
CREATE INDEX XLINK_TO_ID
ON LINKS(LINK_TO_ID);
CREATE TABLE LOCKS (
LOCK_ID integer PRIMARY KEY,
OBJECT_ID integer REFERENCES URI (URI_ID),
SUBJECT_ID integer REFERENCES URI (URI_ID),
TYPE_ID integer REFERENCES URI (URI_ID),
EXPIRATION_DATE numeric(14, 0) NOT NULL,
IS_INHERITABLE smallint NOT NULL,
IS_EXCLUSIVE smallint NOT NULL,
OWNER varchar(255),
CONSTRAINT FK_LOCKS_LOCK_ID
FOREIGN KEY (LOCK_ID)
REFERENCES URI (URI_ID)
);
CREATE TABLE BRANCH (
BRANCH_ID serial UNIQUE NOT NULL,
BRANCH_STRING varchar(255) UNIQUE NOT NULL
);
CREATE TABLE LABEL (
LABEL_ID serial UNIQUE NOT NULL,
LABEL_STRING varchar(255) NOT NULL
);
CREATE TABLE VERSION (
URI_ID integer PRIMARY KEY,
IS_VERSIONED smallint NOT NULL,
CONSTRAINT FK_VERSION_URI_ID
FOREIGN KEY (URI_ID)
REFERENCES URI (URI_ID)
);
CREATE TABLE VERSION_HISTORY (
VERSION_ID serial UNIQUE NOT NULL,
URI_ID integer NOT NULL REFERENCES URI (URI_ID),
BRANCH_ID integer NOT NULL REFERENCES BRANCH (BRANCH_ID),
REVISION_NO VARCHAR(20) NOT NULL,
UNIQUE (URI_ID, BRANCH_ID, REVISION_NO)
);
CREATE INDEX XVERSION_HISTORY1
ON VERSION_HISTORY(URI_ID, BRANCH_ID, REVISION_NO);
CREATE TABLE VERSION_PREDS (
VERSION_ID integer NOT NULL REFERENCES VERSION_HISTORY
(VERSION_ID),
PREDECESSOR_ID integer NOT NULL REFERENCES VERSION_HISTORY
(VERSION_ID),
UNIQUE (VERSION_ID, PREDECESSOR_ID)
);
CREATE INDEX XVERSION_PREDS1
ON VERSION_PREDS(VERSION_ID, PREDECESSOR_ID);
CREATE TABLE VERSION_LABELS (
VERSION_ID integer NOT NULL REFERENCES VERSION_HISTORY
(VERSION_ID),
LABEL_ID integer NOT NULL REFERENCES LABEL (LABEL_ID),
UNIQUE (VERSION_ID, LABEL_ID)
);
CREATE TABLE VERSION_CONTENT (
VERSION_ID integer PRIMARY KEY REFERENCES VERSION_HISTORY
(VERSION_ID),
CONTENT bytea
);
CREATE TABLE PROPERTIES (
VERSION_ID integer NOT NULL REFERENCES VERSION_HISTORY
(VERSION_ID),
PROPERTY_NAMESPACE varchar(50) NOT NULL,
PROPERTY_NAME varchar(50) NOT NULL,
PROPERTY_VALUE varchar(255) NOT NULL,
PROPERTY_TYPE varchar(50) NOT NULL,
IS_PROTECTED smallint NOT NULL,
UNIQUE (VERSION_ID, PROPERTY_NAMESPACE, PROPERTY_NAME)
);
CREATE TABLE PERMISSIONS (
OBJECT_ID integer NOT NULL REFERENCES URI (URI_ID),
SUBJECT_ID integer NOT NULL REFERENCES URI (URI_ID),
ACTION_ID integer NOT NULL REFERENCES URI (URI_ID),
VERSION_NO VARCHAR(20) NULL,
IS_INHERITABLE smallint NOT NULL,
IS_NEGATIVE smallint NOT NULL,
-- Both order and sequence would be more suitable, but can not be used
SUCCESSION int NOT NULL,
UNIQUE (OBJECT_ID, SUBJECT_ID, ACTION_ID),
UNIQUE (OBJECT_ID, SUCCESSION)
);
/**
* The views are not used by slide, but only as a debugging/administration help.
*/
CREATE VIEW OBJECT_VIEW AS
SELECT u.URI_STRING,o.CLASS_NAME FROM URI u, OBJECT o WHERE o.URI_ID = u.URI_ID;
CREATE VIEW BINDING_VIEW AS
SELECT u1.URI_STRING AS PARENT,u2.URI_STRING AS CHILD
FROM BINDING b,URI u1, URI u2 WHERE b.URI_ID = u1.URI_ID AND b.CHILD_UURI_ID =
u2.URI_ID;
CREATE VIEW PERMISSIONS_VIEW AS
SELECT u1.URI_STRING AS OBJECT, u2.URI_STRING AS SUBJECT, u3.URI_STRING AS ACTION,
p.VERSION_NO,p.IS_INHERITABLE,p.IS_NEGATIVE, p.SUCCESSION
FROM PERMISSIONS p,URI u1,URI u2, URI u3
WHERE p.OBJECT_ID = u1.URI_ID AND p.SUBJECT_ID = u2.URI_ID AND p.ACTION_ID =
u3.URI_ID;
CREATE VIEW LOCKS_VIEW AS
SELECT l.LOCK_ID,ou.URI_STRING AS OBJECT,su.URI_STRING AS SUBJECT,tu.URI_STRING AS
TYPE ,l.EXPIRATION_DATE,l.IS_INHERITABLE,l.IS_EXCLUSIVE
FROM LOCKS l, URI ou,URI su,URI tu
WHERE l.OBJECT_ID = ou.URI_ID AND l.SUBJECT_ID = su.URI_ID AND l.TYPE_ID
= su.URI_ID;
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
