Re: DB2 store for slide

kiran p Wed, 14 Jul 2004 13:34:15 -0700

Hi Oliver,

Attached is the source for DB2 Adapter.

There are four files:

SQL File 1. Schema: src\conf\schema\DB2Server.sql Java Files: src\stores\org\apache\slide\store\impl\rdbms\ 2. CommonRDBMSAdapter.java: Code common to Oracle and DB2 has been moved to CommonRDBMSAdapter from OracleRDBMSAdapter 3. OracleRDBMSAdapter.java: Updated Oracle adapter to extend from the common adapter and remove the relevant methods 4.DB2RDBMSAdapter.java: The new DB2 adapter.

Please let me know if you have any issues receiving the attachment or if you have any questions regarding the adapter.

Thanks,

Kiran.

*******************************
  From: Oliver Zeigermann <[EMAIL PROTECTED]>
Reply-To: "Slide Developers Mailing List" <[EMAIL PROTECTED]>
To: Slide Developers Mailing List <[EMAIL PROTECTED]>
Subject: Re: DB2 store for slide
Date: Tue, 13 Jul 2004 08:23:24 +0200

This would be great!

Maybe we can even have the DB2 adapter ready for the 2.1 release...

Oliver

kiran p wrote:
  Hi,

We use slide against DB2 amongst host of other databases. However, the slide project does not have any adapters for DB2. The J2EE stores also do not work against DB2. We are developing an adapter DB2 (lot of the code from Oracle adapter is being reused). The reason we had to develop a specific DB2 adapter was because of the way the StandardRDBMSAdapter was written. The StandardRDBMSAdapter makes a lot of use of jdbc/sql code similar to: "insert into BINDING (URI_ID, NAME, CHILD_UURI_ID) select ?, ?, URI_ID from URI where URI_STRING = ?"

However, DB2 does not like the fact that parameter markers are being used for select columns. Its unable to recognize that the markers are being used for values and not for a column name. None of the other databases have a problem with this.

Anyways, my question is would slide project be interested in a DB2 adapter? If so we would like to contribute the DB2 adapter code and the DB2 schema to the slide community. We have a working schema for DB2 8.

Thanks,

Kiran.

_________________________________________________________________ MSN 9 Dial-up Internet Access helps fight spam and pop-ups – now 2 months FREE! http://join.msn.click-url.com/go/onm00200361ave/direct/01/


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

_________________________________________________________________ MSN Toolbar provides one-click access to Hotmail from any Web page – FREE download! http://toolbar.msn.click-url.com/go/onm00200413ave/direct/01/

--==============================================================
-- DBMS name:      IBM DB2 UDB 8.x Common Server
-- Created on:     6/8/2004 3:41:55 PM
--==============================================================


drop table BINDING;

drop table BRANCH;

drop table "LABEL";

drop table LINKS;

drop table LOCKS;

drop table OBJECT;

drop table PARENT_BINDING;

drop table PERMISSIONS;

drop table PROPERTIES;

drop table URI;

drop table VERSION;

drop table VERSION_CONTENT;

drop table VERSION_HISTORY;

drop table VERSION_LABELS;

drop table VERSION_PREDS;


--==============================================================
-- Table: BINDING
--==============================================================
create table BINDING
(
  URI_ID               NUMERIC(10)            not null,
  "NAME"               VARCHAR(512)           not null,
  CHILD_UURI_ID        NUMERIC(10)            not null,
  constraint "P_Key_1" primary key (URI_ID, "NAME", CHILD_UURI_ID)
);

--==============================================================
-- Table: BRANCH
--==============================================================
create table BRANCH
(
  BRANCH_ID            NUMERIC(10)            not null,
  BRANCH_STRING        VARCHAR(512)           not null,
  constraint "P_Key_1" primary key (BRANCH_ID),
  constraint "A_Key_2" unique (BRANCH_STRING)
);

--==============================================================
-- Table: "LABEL"
--==============================================================
create table "LABEL"
(
  LABEL_ID             NUMERIC(10)            not null,
  LABEL_STRING         VARCHAR(512)           not null,
  constraint "P_Key_1" primary key (LABEL_ID)
);

--==============================================================
-- Table: LINKS
--==============================================================
create table LINKS
(
  URI_ID               NUMERIC(10)            not null,
  LINK_TO_ID           NUMERIC(10)            not null,
  constraint "P_Key_1" primary key (URI_ID, LINK_TO_ID)
);

--==============================================================
-- Table: LOCKS
--==============================================================
create table LOCKS
(
  LOCK_ID              NUMERIC(10)            not null,
  OBJECT_ID            NUMERIC(10)            not null,
  SUBJECT_ID           NUMERIC(10)            not null,
  TYPE_ID              NUMERIC(10)            not null,
  EXPIRATION_DATE      NUMERIC(14)            not null,
  IS_INHERITABLE       NUMERIC(1)             not null,
  IS_EXCLUSIVE         NUMERIC(1)             not null,
  OWNER                VARCHAR(512),
  constraint "P_Key_1" primary key (LOCK_ID)
);

--==============================================================
-- Table: OBJECT
--==============================================================
create table OBJECT
(
  URI_ID               NUMERIC(10)            not null,
  CLASS_NAME           VARCHAR(255)           not null,
  constraint "P_Key_1" primary key (URI_ID)
);

--==============================================================
-- Table: PARENT_BINDING
--==============================================================
create table PARENT_BINDING
(
  URI_ID               NUMERIC(10)            not null,
  "NAME"               VARCHAR(512)           not null,
  PARENT_UURI_ID       NUMERIC(10)            not null,
  constraint "P_Key_1" primary key (URI_ID, "NAME", PARENT_UURI_ID)
);

--==============================================================
-- Table: PERMISSIONS
--==============================================================
create table PERMISSIONS
(
  OBJECT_ID            NUMERIC(10)            not null,
  SUBJECT_ID           NUMERIC(10)            not null,
  ACTION_ID            NUMERIC(10)            not null,
  VERSION_NO           VARCHAR(20),
  IS_INHERITABLE       NUMERIC(1)             not null,
  IS_NEGATIVE          NUMERIC(1)             not null,
  SUCCESSION           NUMERIC(10)            not null,
  constraint "A_Key_1" unique (OBJECT_ID, SUBJECT_ID, ACTION_ID),
  constraint "A_Key_2" unique (OBJECT_ID, SUCCESSION)
);

--============================================================== -- Table: PROPERTIES --============================================================== create table PROPERTIES ( VERSION_ID NUMERIC(10) not null, PROPERTY_NAMESPACE VARCHAR(50) not null, PROPERTY_NAME VARCHAR(50) not null, PROPERTY_VALUE VARCHAR(255), PROPERTY_TYPE VARCHAR(50), IS_PROTECTED NUMERIC(1) not null, constraint "A_Key_1" unique (VERSION_ID, PROPERTY_NAMESPACE, PROPERTY_NAME) );

--==============================================================
-- Table: URI
--==============================================================
create table URI
(
  URI_ID               NUMERIC(10)            not null,
  URI_STRING           VARCHAR(250)          not null,
  constraint "P_Key_1" primary key (URI_ID),
  constraint "A_Key_2" unique (URI_STRING)
);

--==============================================================
-- Table: VERSION
--==============================================================
create table VERSION
(
  URI_ID               NUMERIC(10)            not null,
  IS_VERSIONED         NUMERIC(1)             not null,
  constraint "P_Key_1" primary key (URI_ID)
);

--==============================================================
-- Table: VERSION_CONTENT
--==============================================================
create table VERSION_CONTENT
(
  VERSION_ID           NUMERIC(10)            not null,
  CONTENT              blob(1000m),
  constraint "P_Key_1" primary key (VERSION_ID)
);

--==============================================================
-- Table: VERSION_HISTORY
--==============================================================
create table VERSION_HISTORY
(
  VERSION_ID           NUMERIC(10)            not null,
  URI_ID               NUMERIC(10)            not null,
  BRANCH_ID            NUMERIC(10)            not null,
  REVISION_NO          VARCHAR(20)            not null,
  constraint "P_Key_1" primary key (VERSION_ID),
  constraint "A_Key_2" unique (URI_ID, BRANCH_ID, REVISION_NO)
);

--==============================================================
-- Table: VERSION_LABELS
--==============================================================
create table VERSION_LABELS
(
  VERSION_ID           NUMERIC(10)            not null,
  LABEL_ID             NUMERIC(10)            not null,
  constraint "A_Key_1" unique (VERSION_ID, LABEL_ID)
);

--==============================================================
-- Table: VERSION_PREDS
--==============================================================
create table VERSION_PREDS
(
  VERSION_ID           NUMERIC(10)            not null,
  PREDECESSOR_ID       NUMERIC(10)            not null,
  constraint "A_Key_1" unique (VERSION_ID, PREDECESSOR_ID)
);

CREATE TRIGGER URI_TRG NO CASCADE BEFORE INSERT ON URI referencing NEW AS newrow FOR EACH ROW MODE DB2SQL SET newrow.URI_ID = COALESCE((SELECT MAX(URI_ID) FROM URI) + 1, 1);

CREATE TRIGGER BRANCH_TRG NO CASCADE BEFORE INSERT ON BRANCH referencing NEW AS newrow FOR EACH ROW MODE DB2SQL SET newrow.BRANCH_ID = COALESCE((SELECT MAX(BRANCH_ID) FROM BRANCH) + 1, 1);

CREATE TRIGGER LABEL_TRG NO CASCADE BEFORE INSERT ON LABEL referencing NEW AS newrow FOR EACH ROW MODE DB2SQL SET newrow.LABEL_ID = COALESCE((SELECT MAX(LABEL_ID) FROM LABEL) + 1, 1);

CREATE TRIGGER VERSION_HIST_TRG NO CASCADE BEFORE INSERT ON VERSION_HISTORY referencing NEW AS newrow FOR EACH ROW MODE DB2SQL SET newrow.VERSION_ID = COALESCE((SELECT MAX(VERSION_ID) FROM VERSION_HISTORY) + 1, 1);


alter table BINDING
  add constraint "F_Reference_2" foreign key (URI_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table BINDING
  add constraint "F_Reference_3" foreign key (CHILD_UURI_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table LINKS
  add constraint "F_Reference_6" foreign key (URI_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table LINKS
  add constraint "F_Reference_7" foreign key (LINK_TO_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table LOCKS
  add constraint "F_Reference_10" foreign key (SUBJECT_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table LOCKS
  add constraint "F_Reference_11" foreign key (TYPE_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table LOCKS
  add constraint "F_Reference_8" foreign key (LOCK_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table LOCKS
  add constraint "F_Reference_9" foreign key (OBJECT_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table OBJECT
  add constraint "F_Reference_1" foreign key (URI_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table PARENT_BINDING
  add constraint "F_Reference_4" foreign key (URI_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table PARENT_BINDING
  add constraint "F_Reference_5" foreign key (PARENT_UURI_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table PERMISSIONS
  add constraint "F_Reference_21" foreign key (OBJECT_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table PERMISSIONS
  add constraint "F_Reference_22" foreign key (SUBJECT_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table PERMISSIONS
  add constraint "F_Reference_23" foreign key (ACTION_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table PROPERTIES
  add constraint "F_Reference_20" foreign key (VERSION_ID)
     references VERSION_HISTORY (VERSION_ID)
     on delete restrict on update restrict;

alter table VERSION
  add constraint "F_Reference_12" foreign key (URI_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table VERSION_CONTENT
  add constraint "F_Reference_19" foreign key (VERSION_ID)
     references VERSION_HISTORY (VERSION_ID)
     on delete restrict on update restrict;

alter table VERSION_HISTORY
  add constraint "F_Reference_13" foreign key (URI_ID)
     references URI (URI_ID)
     on delete restrict on update restrict;

alter table VERSION_HISTORY
  add constraint "F_Reference_14" foreign key (BRANCH_ID)
     references BRANCH (BRANCH_ID)
     on delete restrict on update restrict;

alter table VERSION_LABELS
  add constraint "F_Reference_17" foreign key (VERSION_ID)
     references VERSION_HISTORY (VERSION_ID)
     on delete restrict on update restrict;

alter table VERSION_LABELS
  add constraint "F_Reference_18" foreign key (LABEL_ID)
     references "LABEL" (LABEL_ID)
     on delete restrict on update restrict;

alter table VERSION_PREDS
  add constraint "F_Reference_15" foreign key (VERSION_ID)
     references VERSION_HISTORY (VERSION_ID)
     on delete restrict on update restrict;

alter table VERSION_PREDS
  add constraint "F_Reference_16" foreign key (PREDECESSOR_ID)
     references VERSION_HISTORY (VERSION_ID)
     on delete restrict on update restrict;

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: DB2 store for slide

Reply via email to