CALL FOR: HACK, adding Oracle implementation to org.mmbase.module.database.support
Called by: Eduard Witteveen Total tally on this call : +3
YEA (3) : Kees Jongenburger, Rob van Maris, Michiel Meeuwissen
ABSTAIN (2) : Pierre van Rooden, Nico Klasens (?)
NAY (0) :
VETO (0) :
No votes, assumed abstained (10): Jaco de Groot, Marcel Maatkamp, David van Zeventer, Johannes Verelst, Daniel Ockeloen, Rob Vermeulen, Rico Jansen, Wilbert Hengst, Gerard van Enk, Mark Huijser
Call result: An amazing number of people did NOT vote. I really wonder if we should do this voting at all.
Anyway.
The change can be added, due to marginal support, in MMBase 1.7 only.
It needs to be tested, and should eventually be ported to the storage classes.
Eduard Witteveen wrote:
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
-- Pierre van Rooden Mediapark, C 107 tel. +31 (0)35 6772815 "Never summon anything bigger than your head."
