Return-Path: Delivered-To: apmail-geronimo-scm-archive@www.apache.org Received: (qmail 91793 invoked from network); 25 Oct 2007 15:17:28 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 25 Oct 2007 15:17:28 -0000 Received: (qmail 14953 invoked by uid 500); 25 Oct 2007 15:17:15 -0000 Delivered-To: apmail-geronimo-scm-archive@geronimo.apache.org Received: (qmail 14918 invoked by uid 500); 25 Oct 2007 15:17:15 -0000 Mailing-List: contact scm-help@geronimo.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: dev@geronimo.apache.org List-Id: Delivered-To: mailing list scm@geronimo.apache.org Received: (qmail 14907 invoked by uid 99); 25 Oct 2007 15:17:15 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 25 Oct 2007 08:17:15 -0700 X-ASF-Spam-Status: No, hits=-100.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 25 Oct 2007 15:17:20 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 1B7951A9842; Thu, 25 Oct 2007 08:17:00 -0700 (PDT) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r588248 [3/8] - in /geronimo/sandbox/jetspeed-integration: ./ jetspeed-base/ jetspeed-base/src/main/sql/ jetspeed-database/ jetspeed-database/src/ jetspeed-database/src/main/ jetspeed-database/src/main/database/ jetspeed-database/src/main/d... Date: Thu, 25 Oct 2007 15:16:11 -0000 To: scm@geronimo.apache.org From: prasad@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20071025151700.1B7951A9842@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Added: geronimo/sandbox/jetspeed-integration/jetspeed-database/src/main/database/mssql/phase1-schema.sql URL: http://svn.apache.org/viewvc/geronimo/sandbox/jetspeed-integration/jetspeed-database/src/main/database/mssql/phase1-schema.sql?rev=588248&view=auto ============================================================================== --- geronimo/sandbox/jetspeed-integration/jetspeed-database/src/main/database/mssql/phase1-schema.sql (added) +++ geronimo/sandbox/jetspeed-integration/jetspeed-database/src/main/database/mssql/phase1-schema.sql Thu Oct 25 08:16:02 2007 @@ -0,0 +1,651 @@ + +/* ---------------------------------------------------------------------- */ +/* MEDIA_TYPE */ +/* ---------------------------------------------------------------------- */ + +IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'MEDIA_TYPE') +BEGIN + DECLARE @reftable_1 nvarchar(60), @constraintname_1 nvarchar(60) + DECLARE refcursor CURSOR FOR + select reftables.name tablename, cons.name constraintname + from sysobjects tables, + sysobjects reftables, + sysobjects cons, + sysreferences ref + where tables.id = ref.rkeyid + and cons.id = ref.constid + and reftables.id = ref.fkeyid + and tables.name = 'MEDIA_TYPE' + OPEN refcursor + FETCH NEXT from refcursor into @reftable_1, @constraintname_1 + while @@FETCH_STATUS = 0 + BEGIN + exec ('alter table '+@reftable_1+' drop constraint '+@constraintname_1) + FETCH NEXT from refcursor into @reftable_1, @constraintname_1 + END + CLOSE refcursor + DEALLOCATE refcursor + DROP TABLE MEDIA_TYPE +END +; + +CREATE TABLE MEDIA_TYPE +( + MEDIATYPE_ID INT NOT NULL, + NAME VARCHAR (80) NOT NULL, + CHARACTER_SET VARCHAR (40) NULL, + TITLE VARCHAR (80) NULL, + DESCRIPTION TEXT NULL, + + CONSTRAINT MEDIA_TYPE_PK PRIMARY KEY(MEDIATYPE_ID)); + + + + + +/* ---------------------------------------------------------------------- */ +/* CLIENT */ +/* ---------------------------------------------------------------------- */ + +IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'CLIENT') +BEGIN + DECLARE @reftable_2 nvarchar(60), @constraintname_2 nvarchar(60) + DECLARE refcursor CURSOR FOR + select reftables.name tablename, cons.name constraintname + from sysobjects tables, + sysobjects reftables, + sysobjects cons, + sysreferences ref + where tables.id = ref.rkeyid + and cons.id = ref.constid + and reftables.id = ref.fkeyid + and tables.name = 'CLIENT' + OPEN refcursor + FETCH NEXT from refcursor into @reftable_2, @constraintname_2 + while @@FETCH_STATUS = 0 + BEGIN + exec ('alter table '+@reftable_2+' drop constraint '+@constraintname_2) + FETCH NEXT from refcursor into @reftable_2, @constraintname_2 + END + CLOSE refcursor + DEALLOCATE refcursor + DROP TABLE CLIENT +END +; + +CREATE TABLE CLIENT +( + CLIENT_ID INT NOT NULL, + EVAL_ORDER INT NOT NULL, + NAME VARCHAR (80) NOT NULL, + USER_AGENT_PATTERN VARCHAR (128) NULL, + MANUFACTURER VARCHAR (80) NULL, + MODEL VARCHAR (80) NULL, + VERSION VARCHAR (40) NULL, + PREFERRED_MIMETYPE_ID INT NOT NULL, + + CONSTRAINT CLIENT_PK PRIMARY KEY(CLIENT_ID)); + + + + + +/* ---------------------------------------------------------------------- */ +/* MIMETYPE */ +/* ---------------------------------------------------------------------- */ + +IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'MIMETYPE') +BEGIN + DECLARE @reftable_3 nvarchar(60), @constraintname_3 nvarchar(60) + DECLARE refcursor CURSOR FOR + select reftables.name tablename, cons.name constraintname + from sysobjects tables, + sysobjects reftables, + sysobjects cons, + sysreferences ref + where tables.id = ref.rkeyid + and cons.id = ref.constid + and reftables.id = ref.fkeyid + and tables.name = 'MIMETYPE' + OPEN refcursor + FETCH NEXT from refcursor into @reftable_3, @constraintname_3 + while @@FETCH_STATUS = 0 + BEGIN + exec ('alter table '+@reftable_3+' drop constraint '+@constraintname_3) + FETCH NEXT from refcursor into @reftable_3, @constraintname_3 + END + CLOSE refcursor + DEALLOCATE refcursor + DROP TABLE MIMETYPE +END +; + +CREATE TABLE MIMETYPE +( + MIMETYPE_ID INT NOT NULL, + NAME VARCHAR (80) NOT NULL, + + CONSTRAINT MIMETYPE_PK PRIMARY KEY(MIMETYPE_ID)); + + + + + +/* ---------------------------------------------------------------------- */ +/* CAPABILITY */ +/* ---------------------------------------------------------------------- */ + +IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'CAPABILITY') +BEGIN + DECLARE @reftable_4 nvarchar(60), @constraintname_4 nvarchar(60) + DECLARE refcursor CURSOR FOR + select reftables.name tablename, cons.name constraintname + from sysobjects tables, + sysobjects reftables, + sysobjects cons, + sysreferences ref + where tables.id = ref.rkeyid + and cons.id = ref.constid + and reftables.id = ref.fkeyid + and tables.name = 'CAPABILITY' + OPEN refcursor + FETCH NEXT from refcursor into @reftable_4, @constraintname_4 + while @@FETCH_STATUS = 0 + BEGIN + exec ('alter table '+@reftable_4+' drop constraint '+@constraintname_4) + FETCH NEXT from refcursor into @reftable_4, @constraintname_4 + END + CLOSE refcursor + DEALLOCATE refcursor + DROP TABLE CAPABILITY +END +; + +CREATE TABLE CAPABILITY +( + CAPABILITY_ID INT NOT NULL, + CAPABILITY VARCHAR (80) NOT NULL, + + CONSTRAINT CAPABILITY_PK PRIMARY KEY(CAPABILITY_ID)); + + + + + +/* ---------------------------------------------------------------------- */ +/* CLIENT_TO_CAPABILITY */ +/* ---------------------------------------------------------------------- */ + +IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'CLIENT_TO_CAPABILITY') +BEGIN + DECLARE @reftable_5 nvarchar(60), @constraintname_5 nvarchar(60) + DECLARE refcursor CURSOR FOR + select reftables.name tablename, cons.name constraintname + from sysobjects tables, + sysobjects reftables, + sysobjects cons, + sysreferences ref + where tables.id = ref.rkeyid + and cons.id = ref.constid + and reftables.id = ref.fkeyid + and tables.name = 'CLIENT_TO_CAPABILITY' + OPEN refcursor + FETCH NEXT from refcursor into @reftable_5, @constraintname_5 + while @@FETCH_STATUS = 0 + BEGIN + exec ('alter table '+@reftable_5+' drop constraint '+@constraintname_5) + FETCH NEXT from refcursor into @reftable_5, @constraintname_5 + END + CLOSE refcursor + DEALLOCATE refcursor + DROP TABLE CLIENT_TO_CAPABILITY +END +; + +CREATE TABLE CLIENT_TO_CAPABILITY +( + CLIENT_ID INT NOT NULL, + CAPABILITY_ID INT NOT NULL, +); + + + + + +/* ---------------------------------------------------------------------- */ +/* CLIENT_TO_MIMETYPE */ +/* ---------------------------------------------------------------------- */ + +IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'CLIENT_TO_MIMETYPE') +BEGIN + DECLARE @reftable_6 nvarchar(60), @constraintname_6 nvarchar(60) + DECLARE refcursor CURSOR FOR + select reftables.name tablename, cons.name constraintname + from sysobjects tables, + sysobjects reftables, + sysobjects cons, + sysreferences ref + where tables.id = ref.rkeyid + and cons.id = ref.constid + and reftables.id = ref.fkeyid + and tables.name = 'CLIENT_TO_MIMETYPE' + OPEN refcursor + FETCH NEXT from refcursor into @reftable_6, @constraintname_6 + while @@FETCH_STATUS = 0 + BEGIN + exec ('alter table '+@reftable_6+' drop constraint '+@constraintname_6) + FETCH NEXT from refcursor into @reftable_6, @constraintname_6 + END + CLOSE refcursor + DEALLOCATE refcursor + DROP TABLE CLIENT_TO_MIMETYPE +END +; + +CREATE TABLE CLIENT_TO_MIMETYPE +( + CLIENT_ID INT NOT NULL, + MIMETYPE_ID INT NOT NULL, +); + + + + + +/* ---------------------------------------------------------------------- */ +/* MEDIATYPE_TO_CAPABILITY */ +/* ---------------------------------------------------------------------- */ + +IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'MEDIATYPE_TO_CAPABILITY') +BEGIN + DECLARE @reftable_7 nvarchar(60), @constraintname_7 nvarchar(60) + DECLARE refcursor CURSOR FOR + select reftables.name tablename, cons.name constraintname + from sysobjects tables, + sysobjects reftables, + sysobjects cons, + sysreferences ref + where tables.id = ref.rkeyid + and cons.id = ref.constid + and reftables.id = ref.fkeyid + and tables.name = 'MEDIATYPE_TO_CAPABILITY' + OPEN refcursor + FETCH NEXT from refcursor into @reftable_7, @constraintname_7 + while @@FETCH_STATUS = 0 + BEGIN + exec ('alter table '+@reftable_7+' drop constraint '+@constraintname_7) + FETCH NEXT from refcursor into @reftable_7, @constraintname_7 + END + CLOSE refcursor + DEALLOCATE refcursor + DROP TABLE MEDIATYPE_TO_CAPABILITY +END +; + +CREATE TABLE MEDIATYPE_TO_CAPABILITY +( + MEDIATYPE_ID INT NOT NULL, + CAPABILITY_ID INT NOT NULL, +); + + + + + +/* ---------------------------------------------------------------------- */ +/* MEDIATYPE_TO_MIMETYPE */ +/* ---------------------------------------------------------------------- */ + +IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'MEDIATYPE_TO_MIMETYPE') +BEGIN + DECLARE @reftable_8 nvarchar(60), @constraintname_8 nvarchar(60) + DECLARE refcursor CURSOR FOR + select reftables.name tablename, cons.name constraintname + from sysobjects tables, + sysobjects reftables, + sysobjects cons, + sysreferences ref + where tables.id = ref.rkeyid + and cons.id = ref.constid + and reftables.id = ref.fkeyid + and tables.name = 'MEDIATYPE_TO_MIMETYPE' + OPEN refcursor + FETCH NEXT from refcursor into @reftable_8, @constraintname_8 + while @@FETCH_STATUS = 0 + BEGIN + exec ('alter table '+@reftable_8+' drop constraint '+@constraintname_8) + FETCH NEXT from refcursor into @reftable_8, @constraintname_8 + END + CLOSE refcursor + DEALLOCATE refcursor + DROP TABLE MEDIATYPE_TO_MIMETYPE +END +; + +CREATE TABLE MEDIATYPE_TO_MIMETYPE +( + MEDIATYPE_ID INT NOT NULL, + MIMETYPE_ID INT NOT NULL, +); + + + + + +/* ---------------------------------------------------------------------- */ +/* PORTLET_STATISTICS */ +/* ---------------------------------------------------------------------- */ + +IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'PORTLET_STATISTICS') +BEGIN + DECLARE @reftable_9 nvarchar(60), @constraintname_9 nvarchar(60) + DECLARE refcursor CURSOR FOR + select reftables.name tablename, cons.name constraintname + from sysobjects tables, + sysobjects reftables, + sysobjects cons, + sysreferences ref + where tables.id = ref.rkeyid + and cons.id = ref.constid + and reftables.id = ref.fkeyid + and tables.name = 'PORTLET_STATISTICS' + OPEN refcursor + FETCH NEXT from refcursor into @reftable_9, @constraintname_9 + while @@FETCH_STATUS = 0 + BEGIN + exec ('alter table '+@reftable_9+' drop constraint '+@constraintname_9) + FETCH NEXT from refcursor into @reftable_9, @constraintname_9 + END + CLOSE refcursor + DEALLOCATE refcursor + DROP TABLE PORTLET_STATISTICS +END +; + +CREATE TABLE PORTLET_STATISTICS +( + IPADDRESS VARCHAR (80) NULL, + USER_NAME VARCHAR (80) NULL, + TIME_STAMP DATETIME NULL, + PAGE VARCHAR (80) NULL, + PORTLET VARCHAR (255) NULL, + STATUS INT NULL, + ELAPSED_TIME BIGINT NULL, +); + + + + + +/* ---------------------------------------------------------------------- */ +/* PAGE_STATISTICS */ +/* ---------------------------------------------------------------------- */ + +IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'PAGE_STATISTICS') +BEGIN + DECLARE @reftable_10 nvarchar(60), @constraintname_10 nvarchar(60) + DECLARE refcursor CURSOR FOR + select reftables.name tablename, cons.name constraintname + from sysobjects tables, + sysobjects reftables, + sysobjects cons, + sysreferences ref + where tables.id = ref.rkeyid + and cons.id = ref.constid + and reftables.id = ref.fkeyid + and tables.name = 'PAGE_STATISTICS' + OPEN refcursor + FETCH NEXT from refcursor into @reftable_10, @constraintname_10 + while @@FETCH_STATUS = 0 + BEGIN + exec ('alter table '+@reftable_10+' drop constraint '+@constraintname_10) + FETCH NEXT from refcursor into @reftable_10, @constraintname_10 + END + CLOSE refcursor + DEALLOCATE refcursor + DROP TABLE PAGE_STATISTICS +END +; + +CREATE TABLE PAGE_STATISTICS +( + IPADDRESS VARCHAR (80) NULL, + USER_NAME VARCHAR (80) NULL, + TIME_STAMP DATETIME NULL, + PAGE VARCHAR (80) NULL, + STATUS INT NULL, + ELAPSED_TIME BIGINT NULL, +); + + + + + +/* ---------------------------------------------------------------------- */ +/* USER_STATISTICS */ +/* ---------------------------------------------------------------------- */ + +IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'USER_STATISTICS') +BEGIN + DECLARE @reftable_11 nvarchar(60), @constraintname_11 nvarchar(60) + DECLARE refcursor CURSOR FOR + select reftables.name tablename, cons.name constraintname + from sysobjects tables, + sysobjects reftables, + sysobjects cons, + sysreferences ref + where tables.id = ref.rkeyid + and cons.id = ref.constid + and reftables.id = ref.fkeyid + and tables.name = 'USER_STATISTICS' + OPEN refcursor + FETCH NEXT from refcursor into @reftable_11, @constraintname_11 + while @@FETCH_STATUS = 0 + BEGIN + exec ('alter table '+@reftable_11+' drop constraint '+@constraintname_11) + FETCH NEXT from refcursor into @reftable_11, @constraintname_11 + END + CLOSE refcursor + DEALLOCATE refcursor + DROP TABLE USER_STATISTICS +END +; + +CREATE TABLE USER_STATISTICS +( + IPADDRESS VARCHAR (80) NULL, + USER_NAME VARCHAR (80) NULL, + TIME_STAMP DATETIME NULL, + STATUS INT NULL, + ELAPSED_TIME BIGINT NULL, +); + + + + + +/* ---------------------------------------------------------------------- */ +/* ADMIN_ACTIVITY */ +/* ---------------------------------------------------------------------- */ + +IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'ADMIN_ACTIVITY') +BEGIN + DECLARE @reftable_12 nvarchar(60), @constraintname_12 nvarchar(60) + DECLARE refcursor CURSOR FOR + select reftables.name tablename, cons.name constraintname + from sysobjects tables, + sysobjects reftables, + sysobjects cons, + sysreferences ref + where tables.id = ref.rkeyid + and cons.id = ref.constid + and reftables.id = ref.fkeyid + and tables.name = 'ADMIN_ACTIVITY' + OPEN refcursor + FETCH NEXT from refcursor into @reftable_12, @constraintname_12 + while @@FETCH_STATUS = 0 + BEGIN + exec ('alter table '+@reftable_12+' drop constraint '+@constraintname_12) + FETCH NEXT from refcursor into @reftable_12, @constraintname_12 + END + CLOSE refcursor + DEALLOCATE refcursor + DROP TABLE ADMIN_ACTIVITY +END +; + +CREATE TABLE ADMIN_ACTIVITY +( + ACTIVITY VARCHAR (40) NULL, + CATEGORY VARCHAR (40) NULL, + ADMIN VARCHAR (80) NULL, + USER_NAME VARCHAR (80) NULL, + TIME_STAMP DATETIME NULL, + IPADDRESS VARCHAR (80) NULL, + ATTR_NAME VARCHAR (40) NULL, + ATTR_VALUE_BEFORE VARCHAR (80) NULL, + ATTR_VALUE_AFTER VARCHAR (80) NULL, + DESCRIPTION VARCHAR (128) NULL, +); + + + + + +/* ---------------------------------------------------------------------- */ +/* USER_ACTIVITY */ +/* ---------------------------------------------------------------------- */ + +IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'USER_ACTIVITY') +BEGIN + DECLARE @reftable_13 nvarchar(60), @constraintname_13 nvarchar(60) + DECLARE refcursor CURSOR FOR + select reftables.name tablename, cons.name constraintname + from sysobjects tables, + sysobjects reftables, + sysobjects cons, + sysreferences ref + where tables.id = ref.rkeyid + and cons.id = ref.constid + and reftables.id = ref.fkeyid + and tables.name = 'USER_ACTIVITY' + OPEN refcursor + FETCH NEXT from refcursor into @reftable_13, @constraintname_13 + while @@FETCH_STATUS = 0 + BEGIN + exec ('alter table '+@reftable_13+' drop constraint '+@constraintname_13) + FETCH NEXT from refcursor into @reftable_13, @constraintname_13 + END + CLOSE refcursor + DEALLOCATE refcursor + DROP TABLE USER_ACTIVITY +END +; + +CREATE TABLE USER_ACTIVITY +( + ACTIVITY VARCHAR (40) NULL, + CATEGORY VARCHAR (40) NULL, + USER_NAME VARCHAR (80) NULL, + TIME_STAMP DATETIME NULL, + IPADDRESS VARCHAR (80) NULL, + ATTR_NAME VARCHAR (40) NULL, + ATTR_VALUE_BEFORE VARCHAR (80) NULL, + ATTR_VALUE_AFTER VARCHAR (80) NULL, + DESCRIPTION VARCHAR (128) NULL, +); + + + + + +/* ---------------------------------------------------------------------- */ +/* USER_ACTIVITY */ +/* ---------------------------------------------------------------------- */ + + + + +/* ---------------------------------------------------------------------- */ +/* MEDIA_TYPE */ +/* ---------------------------------------------------------------------- */ + + + + +/* ---------------------------------------------------------------------- */ +/* CLIENT */ +/* ---------------------------------------------------------------------- */ + + + + +/* ---------------------------------------------------------------------- */ +/* MIMETYPE */ +/* ---------------------------------------------------------------------- */ + + + + +/* ---------------------------------------------------------------------- */ +/* CAPABILITY */ +/* ---------------------------------------------------------------------- */ + + + + +/* ---------------------------------------------------------------------- */ +/* CLIENT_TO_CAPABILITY */ +/* ---------------------------------------------------------------------- */ + + + + +/* ---------------------------------------------------------------------- */ +/* CLIENT_TO_MIMETYPE */ +/* ---------------------------------------------------------------------- */ + + + + +/* ---------------------------------------------------------------------- */ +/* MEDIATYPE_TO_CAPABILITY */ +/* ---------------------------------------------------------------------- */ + + + + +/* ---------------------------------------------------------------------- */ +/* MEDIATYPE_TO_MIMETYPE */ +/* ---------------------------------------------------------------------- */ + + + + +/* ---------------------------------------------------------------------- */ +/* PORTLET_STATISTICS */ +/* ---------------------------------------------------------------------- */ + + + + +/* ---------------------------------------------------------------------- */ +/* PAGE_STATISTICS */ +/* ---------------------------------------------------------------------- */ + + + + +/* ---------------------------------------------------------------------- */ +/* USER_STATISTICS */ +/* ---------------------------------------------------------------------- */ + + + + +/* ---------------------------------------------------------------------- */ +/* ADMIN_ACTIVITY */ +/* ---------------------------------------------------------------------- */ + + + Propchange: geronimo/sandbox/jetspeed-integration/jetspeed-database/src/main/database/mssql/phase1-schema.sql ------------------------------------------------------------------------------ svn:eol-style = native Propchange: geronimo/sandbox/jetspeed-integration/jetspeed-database/src/main/database/mssql/phase1-schema.sql ------------------------------------------------------------------------------ svn:keywords = Date Revision Propchange: geronimo/sandbox/jetspeed-integration/jetspeed-database/src/main/database/mssql/phase1-schema.sql ------------------------------------------------------------------------------ svn:mime-type = text/plain