Appendix B. Sample schema SQL scripts
Prev   Next

Appendix B. Sample schema SQL scripts

SQL scripts generated from the class model defined in the section called “Database mapping requirements”: 

class Person : public iopc::OidObject {
public:
	EShort age;
	EString name;
};

class Employee : public Person {
public:
	EInt salary;
};

class Student : public Person {
public:
	EString studcardid;
};

class PhdStudent : public Student {
public:
	short int scholarship;
};

Class mapping uses the following settings. For the format definition see the section called “Class metadata”

Person and Employee use vertical mapping, Student uses horizontal mapping, PhdStudent uses filtered mapping. Its attributes are inserted into Student.

::Person[db.mapping.type];string;vertical
::Student[db.mapping.type];string;horizontal
::PhdStudent[db.mapping.type];string;filtered
::PhdStudent[db.mapping.insertto];string;::Student
::Employee[db.mapping.type];string;vertical
iopc::EString[db.type.length];int;50
CREATE SEQUENCE OIDSEQ MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE;
CREATE SEQUENCE SERIALSEQ MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE;

CREATE TABLE OidObject ( 
	CLASSNAME VARCHAR2(1000), OID NUMBER(10), SERIALID NUMBER(10), 
	CONSTRAINT OidObject_pk  PRIMARY KEY (OID))
CREATE INDEX OidObject_idxcn ON OidObject (CLASSNAME);

CREATE TABLE Person ( 
	age NUMBER(5), name VARCHAR2(50), OID NUMBER(10), 
	CONSTRAINT Person_OID_fk FOREIGN KEY (OID) REFERENCES OidObject(OID), 
	CONSTRAINT Person_pk  PRIMARY KEY (OID));
	
CREATE TABLE Student ( 
	studcardid VARCHAR2(50), age NUMBER(5), name VARCHAR2(50), scholarship NUMBER(5), OID NUMBER(10), 
	CONSTRAINT Student_OID_fk FOREIGN KEY (OID) REFERENCES OidObject(OID), 
	CONSTRAINT Student_pk  PRIMARY KEY (OID));
	
CREATE TABLE Employee ( 
	salary NUMBER(10), OID NUMBER(10), 
	CONSTRAINT Employee_OID_fk FOREIGN KEY (OID) REFERENCES OidObject(OID), 
	CONSTRAINT Employee_pk  PRIMARY KEY (OID));
	
CREATE VIEW OidObject_SV AS (
SELECT
	OidObject.CLASSNAME AS CLASSNAME, OidObject.OID AS OID, OidObject.SERIALID AS SERIALID 
	FROM OidObject 
	WHERE OidObject.CLASSNAME = 'iopc::OidObject');
	
CREATE VIEW Person_SV AS (
SELECT 
	Person.age AS Person_age, Person.name AS Person_name, 
	OidObject.CLASSNAME AS CLASSNAME, OidObject.OID AS OID, OidObject.SERIALID AS SERIALID 
FROM Person INNER JOIN OidObject ON (Person.OID = OidObject.OID) 
WHERE OidObject.CLASSNAME = '::Person');

CREATE VIEW Student_SV AS (
SELECT 
	Student.studcardid AS Student_studcardid, 
	Student.age AS Person_age, Student.name AS Person_name, 
	OidObject.CLASSNAME AS CLASSNAME, OidObject.OID AS OID, OidObject.SERIALID AS SERIALID 
FROM Student INNER JOIN OidObject ON (Student.OID = OidObject.OID) 
WHERE OidObject.CLASSNAME = '::Student');

CREATE VIEW PhdStudent_SV AS (
SELECT 
	Student.studcardid AS Student_studcardid, 
	Student.age AS Person_age, Student.name AS Person_name, 
	Student.scholarship AS PhdStudent_scholarship, 
	OidObject.CLASSNAME AS CLASSNAME, OidObject.OID AS OID, OidObject.SERIALID AS SERIALID 
FROM Student INNER JOIN OidObject ON (Student.OID = OidObject.OID) 
WHERE OidObject.CLASSNAME = '::PhdStudent');

CREATE VIEW Employee_SV AS (
SELECT 
	Employee.salary AS Employee_salary, 
	Person.age AS Person_age, Person.name AS Person_name, 
	OidObject.CLASSNAME AS CLASSNAME, OidObject.OID AS OID, OidObject.SERIALID AS SERIALID 
FROM Employee INNER JOIN 
	Person ON (Employee.OID = Person.OID) INNER JOIN 
	OidObject ON (Employee.OID = OidObject.OID) 
WHERE OidObject.CLASSNAME = '::Employee');

CREATE VIEW Employee_PV AS (
SELECT 
	Employee.salary AS Employee_salary, 
	Person.age AS Person_age, Person.name AS Person_name, 
	OidObject.CLASSNAME AS CLASSNAME, OidObject.OID AS OID, OidObject.SERIALID AS SERIALID 
FROM Employee INNER JOIN 
	Person ON (Employee.OID = Person.OID) INNER JOIN 
	OidObject ON (Employee.OID = OidObject.OID));
	
CREATE VIEW PhdStudent_PV AS (
SELECT 
	Student.studcardid AS Student_studcardid, 
	Student.age AS Person_age, Student.name AS Person_name, 
	Student.scholarship AS PhdStudent_scholarship, 
	OidObject.CLASSNAME AS CLASSNAME, OidObject.OID AS OID, OidObject.SERIALID AS SERIALID 
FROM Student INNER JOIN OidObject ON (Student.OID = OidObject.OID) 
WHERE OidObject.CLASSNAME IN ('::PhdStudent'));

CREATE VIEW Student_PV AS (
SELECT 
	Student.studcardid AS Student_studcardid, 
	Student.age AS Person_age, Student.name AS Person_name, 
	OidObject.CLASSNAME AS CLASSNAME, OidObject.OID AS OID, OidObject.SERIALID AS SERIALID 
FROM Student INNER JOIN OidObject ON (Student.OID = OidObject.OID));

CREATE VIEW Person_PV AS (
SELECT 
	Person.age AS Person_age, Person.name AS Person_name, 
	OidObject.CLASSNAME AS CLASSNAME, OidObject.OID AS OID, OidObject.SERIALID AS SERIALID 
FROM Person INNER JOIN OidObject ON (Person.OID = OidObject.OID) 
UNION ALL 
SELECT 
	Student.age AS Person_age, Student.name AS Person_name, 
	OidObject.CLASSNAME AS CLASSNAME, OidObject.OID AS OID, OidObject.SERIALID AS SERIALID 
FROM Student INNER JOIN OidObject ON (Student.OID = OidObject.OID));

CREATE VIEW OidObject_PV AS (SELECT * FROM OidObject);

ADT mapping is set on all classes. ADT hierarchy root is OidObject. 

iopc::OidObject[db.mapping.type];string;object
::Person[db.mapping.type];string;object
::Student[db.mapping.type];string;object
::PhdStudent[db.mapping.type];string;object
::Employee[db.mapping.type];string;object
iopc::EString[db.type.length];int;50
CREATE SEQUENCE OIDSEQ MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE;
CREATE SEQUENCE SERIALSEQ MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE;

CREATE OR REPLACE TYPE tOidObject AS OBJECT ( 
	OID NUMBER(10), CLASSNAME VARCHAR2(1000), SERIALID NUMBER(10), 
	STATIC PROCEDURE insert_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER), 
	STATIC PROCEDURE update_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER), 
	STATIC PROCEDURE delete_object(p_OID NUMBER) ) NOT FINAL;

CREATE TABLE OidObject OF tOidObject ( 
  CONSTRAINT OidObject_pk  PRIMARY KEY (OID) );

CREATE OR REPLACE TYPE BODY tOidObject AS 
	STATIC PROCEDURE insert_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER) IS 
		BEGIN INSERT INTO OidObject VALUES(tOidObject(p_OID, p_CLASSNAME, p_SERIALID)); END; 
	STATIC PROCEDURE update_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER) IS 
		BEGIN UPDATE OidObject X SET VALUE(X) = NEW tOidObject(p_OID, p_CLASSNAME, p_SERIALID) WHERE X.OID = p_OID; END; 
	STATIC PROCEDURE delete_object(p_OID NUMBER) IS 
		BEGIN DELETE FROM OidObject WHERE OID = p_OID; END; END;

CREATE INDEX OidObject_idxcn ON OidObject (CLASSNAME);

CREATE OR REPLACE TYPE tPerson UNDER tOidObject ( 
	age NUMBER(5), name VARCHAR2(50), 
	STATIC PROCEDURE insert_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2), 
	STATIC PROCEDURE update_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2), 
	STATIC PROCEDURE delete_object(p_OID NUMBER) ) NOT FINAL;
	
CREATE OR REPLACE TYPE BODY tPerson AS 
	STATIC PROCEDURE insert_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2) IS BEGIN 
		INSERT INTO OidObject VALUES(tPerson(p_OID, p_CLASSNAME, p_SERIALID, p_age, p_name)); END; 
	STATIC PROCEDURE update_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2) IS BEGIN 
		UPDATE OidObject X SET VALUE(X) = NEW tPerson(p_OID, p_CLASSNAME, p_SERIALID, p_age, p_name) WHERE X.OID = p_OID; END; 
	STATIC PROCEDURE delete_object(p_OID NUMBER) IS BEGIN 
		DELETE FROM OidObject WHERE OID = p_OID; END; END;
		
CREATE OR REPLACE TYPE tStudent UNDER tPerson ( studcardid VARCHAR2(50), 
	STATIC PROCEDURE insert_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2, p_studcardid VARCHAR2), 
	STATIC PROCEDURE update_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2, p_studcardid VARCHAR2), 
	STATIC PROCEDURE delete_object(p_OID NUMBER) ) NOT FINAL;
	
CREATE OR REPLACE TYPE BODY tStudent AS 
	STATIC PROCEDURE insert_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2, p_studcardid VARCHAR2) IS BEGIN 
		INSERT INTO OidObject VALUES(tStudent(p_OID, p_CLASSNAME, p_SERIALID, p_age, p_name, p_studcardid)); END; 
	STATIC PROCEDURE update_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2, p_studcardid VARCHAR2) IS BEGIN 
		UPDATE OidObject X SET VALUE(X) = NEW tStudent(p_OID, p_CLASSNAME, p_SERIALID, p_age, p_name, p_studcardid) WHERE X.OID = p_OID; END; 
	STATIC PROCEDURE delete_object(p_OID NUMBER) IS BEGIN 
		DELETE FROM OidObject WHERE OID = p_OID; END; END;
		
CREATE OR REPLACE TYPE tPhdStudent UNDER tStudent ( scholarship NUMBER(5), 
	STATIC PROCEDURE insert_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2, p_studcardid VARCHAR2, p_scholarship NUMBER), 
	STATIC PROCEDURE update_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2, p_studcardid VARCHAR2, p_scholarship NUMBER), 
	STATIC PROCEDURE delete_object(p_OID NUMBER) ) NOT FINAL;
	
CREATE OR REPLACE TYPE BODY tPhdStudent AS 
	STATIC PROCEDURE insert_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2, p_studcardid VARCHAR2, p_scholarship NUMBER) IS BEGIN 
		INSERT INTO OidObject VALUES(tPhdStudent(p_OID, p_CLASSNAME, p_SERIALID, p_age, p_name, p_studcardid, p_scholarship)); END; 
	STATIC PROCEDURE update_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2, p_studcardid VARCHAR2, p_scholarship NUMBER) IS BEGIN 
		UPDATE OidObject X SET VALUE(X) = NEW tPhdStudent(p_OID, p_CLASSNAME, p_SERIALID, p_age, p_name, p_studcardid, p_scholarship) WHERE X.OID = p_OID; END; 
	STATIC PROCEDURE delete_object(p_OID NUMBER) IS BEGIN 
		DELETE FROM OidObject WHERE OID = p_OID; END; END;
		
CREATE OR REPLACE TYPE tEmployee UNDER tPerson ( salary NUMBER(10), 
	STATIC PROCEDURE insert_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2, p_salary NUMBER), 
	STATIC PROCEDURE update_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2, p_salary NUMBER), 
	STATIC PROCEDURE delete_object(p_OID NUMBER) ) NOT FINAL;
	
CREATE OR REPLACE TYPE BODY tEmployee AS 
	STATIC PROCEDURE insert_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2, p_salary NUMBER) IS BEGIN 
		INSERT INTO OidObject VALUES(tEmployee(p_OID, p_CLASSNAME, p_SERIALID, p_age, p_name, p_salary)); END; 
	STATIC PROCEDURE update_object(p_OID NUMBER, p_CLASSNAME VARCHAR2, p_SERIALID NUMBER, p_age NUMBER, p_name VARCHAR2, p_salary NUMBER) IS BEGIN 
		UPDATE OidObject X SET VALUE(X) = NEW tEmployee(p_OID, p_CLASSNAME, p_SERIALID, p_age, p_name, p_salary) WHERE X.OID = p_OID; END; 
	STATIC PROCEDURE delete_object(p_OID NUMBER) IS BEGIN 
		DELETE FROM OidObject WHERE OID = p_OID; END; END;
		
CREATE VIEW OidObject_SV AS (
SELECT 
	TREAT(VALUE(X) AS tOidObject).OID AS OID, TREAT(VALUE(X) AS tOidObject).CLASSNAME AS CLASSNAME, TREAT(VALUE(X) AS tOidObject).SERIALID AS SERIALID 
FROM OidObject X 
WHERE X.CLASSNAME = 'iopc::OidObject');

CREATE VIEW Person_SV AS (
SELECT 
	TREAT(VALUE(X) AS tPerson).OID AS OID, 
	TREAT(VALUE(X) AS tPerson).age AS Person_age, TREAT(VALUE(X) AS tPerson).name AS Person_name, 
	TREAT(VALUE(X) AS tPerson).CLASSNAME AS CLASSNAME, TREAT(VALUE(X) AS tPerson).SERIALID AS SERIALID 
FROM OidObject X WHERE X.CLASSNAME = '::Person');

CREATE VIEW Student_SV AS (
SELECT 
	TREAT(VALUE(X) AS tStudent).OID AS OID, 
	TREAT(VALUE(X) AS tStudent).studcardid AS Student_studcardid, TREAT(VALUE(X) AS tStudent).age AS Person_age, TREAT(VALUE(X) AS tStudent).name AS Person_name, 
	TREAT(VALUE(X) AS tStudent).CLASSNAME AS CLASSNAME, TREAT(VALUE(X) AS tStudent).SERIALID AS SERIALID 
FROM OidObject X WHERE X.CLASSNAME = '::Student');

CREATE VIEW PhdStudent_SV AS (
SELECT 
	TREAT(VALUE(X) AS tPhdStudent).OID AS OID, 
	TREAT(VALUE(X) AS tPhdStudent).scholarship AS PhdStudent_scholarship, 
	TREAT(VALUE(X) AS tPhdStudent).studcardid AS Student_studcardid, TREAT(VALUE(X) AS tPhdStudent).age AS Person_age, TREAT(VALUE(X) AS tPhdStudent).name AS Person_name, 
	TREAT(VALUE(X) AS tPhdStudent).CLASSNAME AS CLASSNAME, TREAT(VALUE(X) AS tPhdStudent).SERIALID AS SERIALID 
FROM OidObject X WHERE X.CLASSNAME = '::PhdStudent');

CREATE VIEW Employee_SV AS (
SELECT 
	TREAT(VALUE(X) AS tEmployee).OID AS OID, 
	TREAT(VALUE(X) AS tEmployee).salary AS Employee_salary, 
	TREAT(VALUE(X) AS tEmployee).age AS Person_age, TREAT(VALUE(X) AS tEmployee).name AS Person_name, 
	TREAT(VALUE(X) AS tEmployee).CLASSNAME AS CLASSNAME, TREAT(VALUE(X) AS tEmployee).SERIALID AS SERIALID 
FROM OidObject X WHERE X.CLASSNAME = '::Employee');

CREATE VIEW Employee_PV AS (
SELECT 
	TREAT(VALUE(X) AS tEmployee).OID AS OID, 
	TREAT(VALUE(X) AS tEmployee).salary AS Employee_salary, 
	TREAT(VALUE(X) AS tEmployee).age AS Person_age, TREAT(VALUE(X) AS tEmployee).name AS Person_name, 
	TREAT(VALUE(X) AS tEmployee).CLASSNAME AS CLASSNAME, TREAT(VALUE(X) AS tEmployee).SERIALID AS SERIALID 
FROM OidObject X WHERE VALUE(X) IS OF (tEmployee));

CREATE VIEW PhdStudent_PV AS (
SELECT 
	TREAT(VALUE(X) AS tPhdStudent).OID AS OID, 
	TREAT(VALUE(X) AS tPhdStudent).scholarship AS PhdStudent_scholarship, 
	TREAT(VALUE(X) AS tPhdStudent).studcardid AS Student_studcardid, TREAT(VALUE(X) AS tPhdStudent).age AS Person_age, TREAT(VALUE(X) AS tPhdStudent).name AS Person_name, 
	TREAT(VALUE(X) AS tPhdStudent).CLASSNAME AS CLASSNAME, TREAT(VALUE(X) AS tPhdStudent).SERIALID AS SERIALID 
FROM OidObject X WHERE VALUE(X) IS OF (tPhdStudent));

CREATE VIEW Student_PV AS (
	SELECT TREAT(VALUE(X) AS tStudent).OID AS OID, 
	TREAT(VALUE(X) AS tStudent).studcardid AS Student_studcardid, TREAT(VALUE(X) AS tStudent).age AS Person_age, TREAT(VALUE(X) AS tStudent).name AS Person_name, 
	TREAT(VALUE(X) AS tStudent).CLASSNAME AS CLASSNAME, TREAT(VALUE(X) AS tStudent).SERIALID AS SERIALID 
FROM OidObject X WHERE VALUE(X) IS OF (tStudent));

CREATE VIEW Person_PV AS (
	SELECT TREAT(VALUE(X) AS tPerson).OID AS OID, 
	TREAT(VALUE(X) AS tPerson).age AS Person_age, TREAT(VALUE(X) AS tPerson).name AS Person_name, 
	TREAT(VALUE(X) AS tPerson).CLASSNAME AS CLASSNAME, TREAT(VALUE(X) AS tPerson).SERIALID AS SERIALID 
FROM OidObject X WHERE VALUE(X) IS OF (tPerson));

CREATE VIEW OidObject_PV AS (SELECT * FROM OidObject);

There is a problem in Oracle 10g in execution of porymorphic views belonging to classes that use ADT mapping. In some scenarios the predicate WHERE VALUE(X) IS OF (tType) AND (X.Property_of_Type_tType oper Value) is evaluated incorrectly and no rows are returned from those queries regardless if part of the query is hidden in the view or not. For this reason the following global setting can be used: 

globals[db.oracle.workaround.10gOO];bool;true

When the workaround is enabled, the WHERE clause in Polymorphic views is generated in the following way: 

WHERE X.OID IN (SELECT Y.OID FROM OidObject Y WHERE VALUE(Y) IS OF(tType))

The problem is fixed in Oracle 11g.

Prev   Next
Caching Home Appendix C. Metadata overview