CALL FOR: HACK, adding Oracle implementation to org.mmbase.module.database.support

Eduard Witteveen Thu, 27 Mar 2003 10:12:58 -0800

CALL FOR: HACK, adding Oracle implementation to
org.mmbase.module.database.support

Description:
============
An Oracle implementation for MMBase, which stores it's information only
on in 1 field. Other implementations like MySQL or HsqlDB store their
information on multiple places.
Showing the information in multiple places has been done by using views
on the table's. To keep the database consistent, contrains have been added.

Why:
=======
- Could be a good selling point for MMBase in larger organisations
- After PostgreSQL i wanted to see if i could use somekinda same
structure to store information inside Oracle.

Benefits:
=========
Due to all the contrains in Oracle it becomes possible to store the data
in a consisent way. In PostgreSQL there are already a lot of contrains
and here there are even more! Furthermore storing the information on 1
place is never a bad idea

Version:
========
Current CVS version

Choises:
========
I did not extend org.mmbase.storage.* classes, since they are not
default used in CVS(only when lookup.xml fails!). Furthermore i couldnt
oversee the structure of the classes(or the functionality).

Changes:
========
PostgreSQL71 functionality is now put inside SQL92SingelField and
PostgreSQL72. Only the workaround for wrong behviour large fields is in
PostgreSQL71.
SQL92SingelFields contains the generic functionality for fields on 1
location.
PostgreSQL72 contains the PostgreSQL implementation.
SQL92WithViews the Oracle implementation.
Furthermore some files for configuration are added.

TODO:
=====
- Verify that no bugs appeared inside the PostgreSQL classes
- More testing new functionality
- Research TextSearch functionality
- Research XML functionality inside Oracle
- Or skip check on null-values / check builder-definition and
application combination.

Tested with:
============
Client: Oracle 9.2. and OCI
Server: 8.x
Java: 1.4

Modified files:
(migrated code from PostgreSQL71 to PostgreSQL72 and Sql92SingleFields,
no changes are made to that specific code)
===============
org/mmbase/module/database/support/PostgreSQL71.java
org/mmbase/module/database/support/PostgreSQL72.java
config/databases/lookup.xml

Added files:
============
org/mmbase/module/database/support/Sql92SingleFields.java
org/mmbase/module/database/support/Sql92WithViews.java
config/databases/sql92views.xml

Data definition(here some 'sample' sql statements that are generated:
=====================================================================
CREATE TABLE TEST11_OBJECT (
   M_NUMBER  NUMBER        NOT NULL,
   OTYPE     NUMBER        NOT NULL,
   OWNER     VARCHAR2 (12),
   CONSTRAINT TEST11_CONTRAIN_2
     UNIQUE (M_NUMBER),
   CONSTRAINT TEST11_CONTRAIN_1
   PRIMARY KEY ( M_NUMBER )
);

CREATE TABLE TEST11_INSREL_TABLE (
   M_NUMBER  NUMBER        NOT NULL,
   SNUMBER   NUMBER        NOT NULL,
   DNUMBER   NUMBER        NOT NULL,
   RNUMBER   NUMBER        NOT NULL,
   DIR       NUMBER,
   CONSTRAINT TEST11_CONTRAIN_23
     UNIQUE (M_NUMBER),
   CONSTRAINT TEST11_CONTRAIN_21
   PRIMARY KEY ( M_NUMBER )
);

ALTER TABLE TEST11_INSREL_TABLE ADD  CONSTRAINT TEST11_CONTRAIN_22
  FOREIGN KEY (M_NUMBER)
   REFERENCES SYSTEM.TEST11_OBJECT (M_NUMBER) ON DELETE CASCADE;

ALTER TABLE TEST11_INSREL_TABLE ADD  CONSTRAINT TEST11_CONTRAIN_24
  FOREIGN KEY (SNUMBER)
   REFERENCES SYSTEM.TEST11_OBJECT (M_NUMBER) ON DELETE CASCADE;

ALTER TABLE TEST11_INSREL_TABLE ADD  CONSTRAINT TEST11_CONTRAIN_25
  FOREIGN KEY (DNUMBER)
   REFERENCES SYSTEM.TEST11_OBJECT (M_NUMBER) ON DELETE CASCADE;

ALTER TABLE TEST11_INSREL_TABLE ADD  CONSTRAINT TEST11_CONTRAIN_26
  FOREIGN KEY (RNUMBER)
   REFERENCES SYSTEM.TEST11_OBJECT (M_NUMBER) ON DELETE CASCADE;


CREATE OR REPLACE VIEW TEST11_INSREL ( M_NUMBER,
OTYPE, OWNER, SNUMBER, DNUMBER,
RNUMBER, DIR ) AS SELECT test11_object.m_number, otype, owner, snumber,
dnumber, rnumber, dir FROM test11_insrel_TABLE, test11_object WHERE
test11_insrel_TABLE.m_number = test11_object.m_number

CREATE TABLE TEST11_PEOPLE_TABLE (
   M_NUMBER   NUMBER        NOT NULL,
   FIRSTNAME  VARCHAR2 (32),
   LASTNAME   VARCHAR2 (32),
   EMAIL      VARCHAR2 (32),
   ACCOUNT    VARCHAR2 (12),
   CONSTRAINT TEST11_CONTRAIN_36
     UNIQUE (M_NUMBER),
   CONSTRAINT TEST11_CONTRAIN_34
   PRIMARY KEY ( M_NUMBER )
);

ALTER TABLE TEST11_PEOPLE_TABLE ADD  CONSTRAINT TEST11_CONTRAIN_35
  FOREIGN KEY (M_NUMBER) REFERENCES SYSTEM.TEST11_OBJECT (M_NUMBER) ON
DELETE CASCADE;

CREATE OR REPLACE VIEW TEST11_PEOPLE ( M_NUMBER,
OTYPE, OWNER, FIRSTNAME, LASTNAME,
EMAIL, ACCOUNT ) AS SELECT test11_object.m_number, otype, owner,
firstname, lastname, email, account FROM test11_people_TABLE,
test11_object WHERE test11_people_TABLE.m_number = test11_object.m_number

Code/diff:
==========
Since i migrated everything, i attached the modified files. No diff is
provided(als less info)

START OF CALL: 2003-03-27

END OF CALL: 2003-04-1

[_] +1 (YEA)

[_] +0 (ABSTAIN )

[_] -1 (NAY), because :

[_] VETO, because:

--
Time is on my side,....

Eduard Witteveen
+316 414 789 23

changes.zip
Description: Zip archive

CALL FOR: HACK, adding Oracle implementation to org.mmbase.module.database.support

Reply via email to