Return-Path: Delivered-To: apmail-db-jdo-dev-archive@www.apache.org Received: (qmail 19277 invoked from network); 30 Aug 2005 14:52:53 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 30 Aug 2005 14:52:53 -0000 Received: (qmail 28459 invoked by uid 500); 30 Aug 2005 14:52:52 -0000 Mailing-List: contact jdo-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: jdo-dev@db.apache.org Delivered-To: mailing list jdo-dev@db.apache.org Received: (qmail 28442 invoked by uid 99); 30 Aug 2005 14:52:52 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 30 Aug 2005 07:52:52 -0700 X-ASF-Spam-Status: No, hits=0.9 required=10.0 tests=UPPERCASE_50_75 X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [212.224.30.66] (HELO service-01.spree.de) (212.224.30.66) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 30 Aug 2005 07:53:07 -0700 Received: from [172.16.1.19] (rio.spree.de [172.16.1.19]) (authenticated bits=0) by service-01.spree.de (8.13.4/8.13.4/Debian-3) with ESMTP id j7UEqUTF015930 for ; Tue, 30 Aug 2005 16:52:30 +0200 Message-ID: <431472C4.70009@spree.de> Date: Tue, 30 Aug 2005 16:52:52 +0200 From: Michael Watzek Organization: Tech@Spree GmbH User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: jdo-dev@db.apache.org Subject: 2nd version of inheritance patch 1 of 2 References: <005401c5ace3$00408830$0302a8c0@jpox> <200508300718.10740.andy@jpox.org> <43144B08.5030900@spree.de> <200508301319.55282.andy@jpox.org> In-Reply-To: <200508301319.55282.andy@jpox.org> Content-Type: multipart/mixed; boundary="------------040404010507090800010900" X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N --------------040404010507090800010900 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi Craig, Michelle, I agree with Andy that it makes sense to check in the inheritance patch. If there are still issues concerning the patch then we can handle them as bugs. For this reason, I provided an updated version of the patch which uses qualified class names for mapping 2. I broke up the patch into two files. You find the first file attached. I'll send the second version in a separate mail. Note, that this patch uses the same mapping ids as Michelle's patch implementing schema orm attribute. let me know, if you prefer to change the mapping ids of the inheritance patch. Regards, Michael > Hi Michael, > > >>I'm using the latest JPOX build and still get exceptions for mapping 2 >>for both identity types: >> >>Application identity: org.jpox.metadata.InvalidMetaDataException: Class >>"org.apache.jdo.tck.pc.company.Employee" has been specified to use >>"subclass-table" inheritance strategy yet has at least one field that is >>a Collection/Map. This is not currently supported by JPOX. > > > Hardly suprising since I previously said that this is not currently supported, > in the original mail. I've made some changes to enable this, but haven't yet > removed the metadata check (because I don't want all users going off and > using it yet). > > My email of this morning, and Eriks' of last night, were for the issue about > overriding field specifications of superclasses. > > >>Please apply the inheritance patch and copy the attached ORM files to >>"tck20/test/orm//org/apache/jdo/tck/pc/company". > > > Since these are new tests, what is the problem with just checking them into > SVN ? so then we can just run the TCK when we next get a moment. Since we're > not talking about released software here, I don't see a problem with it. > I personally don't want to get into applying patches to the TCK in order to > try it - since there's only me and Erik working on JPOX core/enhancer in our > spare time, we have enough to be working on right now ;-) > > -- ------------------------------------------------------------------- Michael Watzek Tech@Spree Engineering GmbH mailto:mwa.tech@spree.de Buelowstr. 66 Tel.: ++49/30/235 520 36 10783 Berlin - Germany Fax.: ++49/30/217 520 12 http://www.spree.de/ ------------------------------------------------------------------- --------------040404010507090800010900 Content-Type: text/plain; name="inheritance2.patch-1of2" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="inheritance2.patch-1of2" Index: test/sql/derby/datastoreidentity/schema1.sql =================================================================== --- test/sql/derby/datastoreidentity/schema1.sql (revision 0) +++ test/sql/derby/datastoreidentity/schema1.sql (revision 0) @@ -0,0 +1,159 @@ +-- SchemaType: datastore identity + +-- Inheritance mapping: +-- Separate table for each class in the inheritance hierarchy. +-- Each table contains columns for the declared fields. +-- See tables "persons", "employees", "parttimeemployees", +-- "fulltimeemployees", "insuranceplans", "medicalinsurance", and "dentalinsurance". + +connect 'jdbc:derby:jdotckdb;create=true' user 'tckuser' password 'tckuser'; + +CREATE SCHEMA datastoreidentity1; +SET SCHEMA datastoreidentity1; + +------------------------- +-- company +------------------------- + +ALTER TABLE departments DROP CONSTRAINT EMP_MO_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_PROJ_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_REV_FK; +DROP TABLE dentalinsurance; +DROP TABLE medicalinsurance; +DROP TABLE insuranceplans; +DROP TABLE project_reviewer; +DROP TABLE project_member; +DROP TABLE employee_phoneno_type; +DROP TABLE fulltimeemployees; +DROP TABLE parttimeemployees; +DROP TABLE employees; +DROP TABLE persons; +DROP TABLE projects; +DROP TABLE departments; +DROP TABLE companies; + +CREATE TABLE companies ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + ID INTEGER, + NAME VARCHAR(32) NOT NULL, + FOUNDEDDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT COMPS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE departments ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + ID INTEGER, + NAME VARCHAR(32) NOT NULL, + EMP_OF_THE_MONTH INTEGER, + COMPANYID INTEGER REFERENCES companies, + CONSTRAINT DEPTS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE persons ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + PERSONID INTEGER UNIQUE NOT NULL, + FIRSTNAME VARCHAR(32) NOT NULL, + LASTNAME VARCHAR(32) NOT NULL, + MIDDLENAME VARCHAR(32), + BIRTHDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT PERS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE employees ( + DATASTORE_IDENTITY INTEGER NOT NULL, + HIREDATE VARCHAR(32), + WEEKLYHOURS FLOAT, + DEPARTMENT INTEGER REFERENCES departments, + FUNDINGDEPT INTEGER REFERENCES departments, + MANAGER INTEGER REFERENCES persons, + MENTOR INTEGER REFERENCES persons, + HRADVISOR INTEGER REFERENCES persons, + CONSTRAINT EMPS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT EMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons (DATASTORE_IDENTITY) +); + +CREATE TABLE parttimeemployees ( + DATASTORE_IDENTITY INTEGER NOT NULL, + WAGE FLOAT, + CONSTRAINT PTEMPS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT PTEMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons (DATASTORE_IDENTITY) +); + +CREATE TABLE fulltimeemployees ( + DATASTORE_IDENTITY INTEGER NOT NULL, + SALARY FLOAT, + CONSTRAINT FTEMPS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT FTEMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons (DATASTORE_IDENTITY) +); + +CREATE TABLE insuranceplans ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + INSID INTEGER, + CARRIER VARCHAR(64) NOT NULL, + EMPLOYEE INTEGER REFERENCES persons, + CONSTRAINT INS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE medicalinsurance ( + DATASTORE_IDENTITY INTEGER NOT NULL, + PLANTYPE VARCHAR(8), + CONSTRAINT MEDINS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT MEDINS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES insuranceplans (DATASTORE_IDENTITY) +); + +CREATE TABLE dentalinsurance ( + DATASTORE_IDENTITY INTEGER NOT NULL, + LIFETIME_ORTHO_BENEFIT DECIMAL(22,3), + CONSTRAINT DENTINS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT DENTINS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES insuranceplans (DATASTORE_IDENTITY) +); + +CREATE TABLE projects ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + PROJID INTEGER UNIQUE NOT NULL, + NAME VARCHAR(32) NOT NULL, + BUDGET DECIMAL(11,2) NOT NULL, + CONSTRAINT PROJS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE project_reviewer ( + PROJID INTEGER NOT NULL, + REVIEWER INTEGER NOT NULL +); + +CREATE TABLE project_member ( + PROJID INTEGER REFERENCES projects NOT NULL, + MEMBER INTEGER REFERENCES persons NOT NULL +); + +CREATE TABLE employee_phoneno_type ( + EMPID INTEGER REFERENCES persons NOT NULL, + PHONENO VARCHAR(16) NOT NULL, + TYPE VARCHAR(16) NOT NULL +); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY + (PROJID) REFERENCES projects(PROJID); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_REV_FK FOREIGN KEY + (REVIEWER) REFERENCES persons(PERSONID); + +ALTER TABLE departments + ADD CONSTRAINT EMP_MO_FK FOREIGN KEY + (EMP_OF_THE_MONTH) REFERENCES persons(DATASTORE_IDENTITY); + +disconnect; Property changes on: test/sql/derby/datastoreidentity/schema1.sql ___________________________________________________________________ Name: svn:executable + * Index: test/sql/derby/datastoreidentity/schema2.sql =================================================================== --- test/sql/derby/datastoreidentity/schema2.sql (revision 0) +++ test/sql/derby/datastoreidentity/schema2.sql (revision 0) @@ -0,0 +1,196 @@ +-- SchemaType: datastore identity + +-- Inheritance mapping: +-- Separate table for each class in the inheritance hierarchy. +-- No tables for abstract classes. +-- Each table contains columns for all fields. +-- Managers, mentors, hradvisors, and employees of the month +-- are fulltime employees. +-- Only fulltime employees can have insurances, can be project members, +-- and can be project reviewers. +-- Separate phone number type tables for persons, fulltime employees, +-- and parttime employees. +-- See tables "persons", "parttimeemployees", +-- "fulltimeemployees", "medicalinsurance", "dentalinsurance", +-- "fulltime_employee_phoneno_type", and "parttime_employee_phoneno_type". + +connect 'jdbc:derby:jdotckdb;create=true' user 'tckuser' password 'tckuser'; + +CREATE SCHEMA datastoreidentity2; +SET SCHEMA datastoreidentity2; + +------------------------- +-- company +------------------------- + +ALTER TABLE departments DROP CONSTRAINT EMP_MO_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_PROJ_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_REV_FK; +DROP TABLE dentalinsurance; +DROP TABLE medicalinsurance; +DROP TABLE project_reviewer; +DROP TABLE project_member; +DROP TABLE employee_phoneno_type; +DROP TABLE fulltime_employee_phoneno_type; +DROP TABLE parttime_employee_phoneno_type; +DROP TABLE parttimeemployees; +DROP TABLE fulltimeemployees; +DROP TABLE persons; +DROP TABLE projects; +DROP TABLE departments; +DROP TABLE companies; + +CREATE TABLE companies ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + ID INTEGER, + NAME VARCHAR(32) NOT NULL, + FOUNDEDDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT COMPS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE departments ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + ID INTEGER, + NAME VARCHAR(32) NOT NULL, + EMP_OF_THE_MONTH INTEGER, + COMPANYID INTEGER REFERENCES companies, + CONSTRAINT DEPTS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE persons ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + PERSONID INTEGER UNIQUE NOT NULL, + FIRSTNAME VARCHAR(32) NOT NULL, + LASTNAME VARCHAR(32) NOT NULL, + MIDDLENAME VARCHAR(32), + BIRTHDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT PERS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE fulltimeemployees ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + PERSONID INTEGER UNIQUE NOT NULL, + FIRSTNAME VARCHAR(32) NOT NULL, + LASTNAME VARCHAR(32) NOT NULL, + MIDDLENAME VARCHAR(32), + BIRTHDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + HIREDATE VARCHAR(32), + WEEKLYHOURS FLOAT, + DEPARTMENT INTEGER REFERENCES departments, + FUNDINGDEPT INTEGER REFERENCES departments, + MANAGER INTEGER REFERENCES fulltimeemployees, + MENTOR INTEGER REFERENCES fulltimeemployees, + HRADVISOR INTEGER REFERENCES fulltimeemployees, + SALARY FLOAT, + CONSTRAINT FTEMPS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE parttimeemployees ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + PERSONID INTEGER UNIQUE NOT NULL, + FIRSTNAME VARCHAR(32) NOT NULL, + LASTNAME VARCHAR(32) NOT NULL, + MIDDLENAME VARCHAR(32), + BIRTHDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + HIREDATE VARCHAR(32), + WEEKLYHOURS FLOAT, + DEPARTMENT INTEGER REFERENCES departments, + FUNDINGDEPT INTEGER REFERENCES departments, + MANAGER INTEGER REFERENCES fulltimeemployees, + MENTOR INTEGER REFERENCES fulltimeemployees, + HRADVISOR INTEGER REFERENCES fulltimeemployees, + WAGE FLOAT, + CONSTRAINT PTEMPS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE medicalinsurance ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + INSID INTEGER, + CARRIER VARCHAR(64) NOT NULL, + EMPLOYEE INTEGER REFERENCES fulltimeemployees, + PLANTYPE VARCHAR(8), + CONSTRAINT MEDINS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE dentalinsurance ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + INSID INTEGER, + CARRIER VARCHAR(64) NOT NULL, + EMPLOYEE INTEGER REFERENCES fulltimeemployees, + LIFETIME_ORTHO_BENEFIT DECIMAL(22,3), + CONSTRAINT DENTINS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE projects ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + PROJID INTEGER UNIQUE NOT NULL, + NAME VARCHAR(32) NOT NULL, + BUDGET DECIMAL(11,2) NOT NULL, + CONSTRAINT PROJS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE project_reviewer ( + PROJID INTEGER NOT NULL, + REVIEWER INTEGER NOT NULL +); + +CREATE TABLE project_member ( + PROJID INTEGER REFERENCES projects NOT NULL, + MEMBER INTEGER REFERENCES fulltimeemployees NOT NULL +); + +CREATE TABLE employee_phoneno_type ( + EMPID INTEGER REFERENCES persons NOT NULL, + PHONENO VARCHAR(16) NOT NULL, + TYPE VARCHAR(16) NOT NULL +); + +CREATE TABLE fulltime_employee_phoneno_type ( + EMPID INTEGER REFERENCES fulltimeemployees NOT NULL, + PHONENO VARCHAR(16) NOT NULL, + TYPE VARCHAR(16) NOT NULL +); + +CREATE TABLE parttime_employee_phoneno_type ( + EMPID INTEGER REFERENCES parttimeemployees NOT NULL, + PHONENO VARCHAR(16) NOT NULL, + TYPE VARCHAR(16) NOT NULL +); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY + (PROJID) REFERENCES projects(PROJID); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_REV_FK FOREIGN KEY + (REVIEWER) REFERENCES fulltimeemployees(PERSONID); + +ALTER TABLE departments + ADD CONSTRAINT EMP_MO_FK FOREIGN KEY + (EMP_OF_THE_MONTH) REFERENCES fulltimeemployees(DATASTORE_IDENTITY); + +disconnect; Property changes on: test/sql/derby/datastoreidentity/schema2.sql ___________________________________________________________________ Name: svn:executable + * Index: test/sql/derby/datastoreidentity/schema3.sql =================================================================== --- test/sql/derby/datastoreidentity/schema3.sql (revision 0) +++ test/sql/derby/datastoreidentity/schema3.sql (revision 0) @@ -0,0 +1,157 @@ +-- SchemaType: datastore identity + +-- Inheritance mapping: +-- Person has inheritance strategy "new-table". +-- Employee has inheritance strategy "subclass-table". +-- PartTimeEmployee and FullTimeEmployee have inheritance strategy "new-table". +-- Insurance has inheritance strategy "subclass-table". +-- MedicalInsurance and DentalInsurance have inheritance strategy "new-table". +-- See tables "persons", "parttimeemployees", "fulltimeemployees", +-- "medicalinsurance" and "dentalinsurance". + +connect 'jdbc:derby:jdotckdb;create=true' user 'tckuser' password 'tckuser'; + +CREATE SCHEMA datastoreidentity3; +SET SCHEMA datastoreidentity3; + +------------------------- +-- company +------------------------- + +ALTER TABLE departments DROP CONSTRAINT EMP_MO_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_PROJ_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_REV_FK; +DROP TABLE dentalinsurance; +DROP TABLE medicalinsurance; +DROP TABLE project_reviewer; +DROP TABLE project_member; +DROP TABLE employee_phoneno_type; +DROP TABLE fulltimeemployees; +DROP TABLE parttimeemployees; +DROP TABLE persons; +DROP TABLE projects; +DROP TABLE departments; +DROP TABLE companies; + +CREATE TABLE companies ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + ID INTEGER, + NAME VARCHAR(32) NOT NULL, + FOUNDEDDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT COMPS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE departments ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + ID INTEGER, + NAME VARCHAR(32) NOT NULL, + EMP_OF_THE_MONTH INTEGER, + COMPANYID INTEGER REFERENCES companies, + CONSTRAINT DEPTS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE persons ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + PERSONID INTEGER UNIQUE NOT NULL, + FIRSTNAME VARCHAR(32) NOT NULL, + LASTNAME VARCHAR(32) NOT NULL, + MIDDLENAME VARCHAR(32), + BIRTHDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT PERS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE parttimeemployees ( + DATASTORE_IDENTITY INTEGER NOT NULL, + HIREDATE VARCHAR(32), + WEEKLYHOURS FLOAT, + DEPARTMENT INTEGER REFERENCES departments, + FUNDINGDEPT INTEGER REFERENCES departments, + MANAGER INTEGER REFERENCES persons, + MENTOR INTEGER REFERENCES persons, + HRADVISOR INTEGER REFERENCES persons, + WAGE FLOAT, + CONSTRAINT PTEMPS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT PTEMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons (DATASTORE_IDENTITY) +); + +CREATE TABLE fulltimeemployees ( + DATASTORE_IDENTITY INTEGER NOT NULL, + HIREDATE VARCHAR(32), + WEEKLYHOURS FLOAT, + DEPARTMENT INTEGER REFERENCES departments, + FUNDINGDEPT INTEGER REFERENCES departments, + MANAGER INTEGER REFERENCES persons, + MENTOR INTEGER REFERENCES persons, + HRADVISOR INTEGER REFERENCES persons, + SALARY FLOAT, + CONSTRAINT FTEMPS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT FTEMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons (DATASTORE_IDENTITY) +); + +CREATE TABLE medicalinsurance ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + INSID INTEGER, + CARRIER VARCHAR(64) NOT NULL, + EMPLOYEE INTEGER REFERENCES persons, + PLANTYPE VARCHAR(8), + CONSTRAINT MEDINS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE dentalinsurance ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + INSID INTEGER, + CARRIER VARCHAR(64) NOT NULL, + EMPLOYEE INTEGER REFERENCES persons, + LIFETIME_ORTHO_BENEFIT DECIMAL(22,3), + CONSTRAINT DENTINS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE projects ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + PROJID INTEGER UNIQUE NOT NULL, + NAME VARCHAR(32) NOT NULL, + BUDGET DECIMAL(11,2) NOT NULL, + CONSTRAINT PROJS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE project_reviewer ( + PROJID INTEGER NOT NULL, + REVIEWER INTEGER NOT NULL +); + +CREATE TABLE project_member ( + PROJID INTEGER REFERENCES projects NOT NULL, + MEMBER INTEGER REFERENCES persons NOT NULL +); + +CREATE TABLE employee_phoneno_type ( + EMPID INTEGER REFERENCES persons NOT NULL, + PHONENO VARCHAR(16) NOT NULL, + TYPE VARCHAR(16) NOT NULL +); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY + (PROJID) REFERENCES projects(PROJID); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_REV_FK FOREIGN KEY + (REVIEWER) REFERENCES persons(PERSONID); + +ALTER TABLE departments + ADD CONSTRAINT EMP_MO_FK FOREIGN KEY + (EMP_OF_THE_MONTH) REFERENCES persons(DATASTORE_IDENTITY); + +disconnect; Property changes on: test/sql/derby/datastoreidentity/schema3.sql ___________________________________________________________________ Name: svn:executable + * Index: test/sql/derby/datastoreidentity/schema4.sql =================================================================== --- test/sql/derby/datastoreidentity/schema4.sql (revision 0) +++ test/sql/derby/datastoreidentity/schema4.sql (revision 0) @@ -0,0 +1,133 @@ +-- SchemaType: datastore identity + +-- Inheritance mapping: +-- Person, Employee, and Insurance have inheritance strategy "new-table". +-- PartTimeEmployee, FullTimeEmployee, MedicalInsurance, and DentalInsurance +-- have inheritance strategy "superclass-table". +-- See tables "persons", "employees", and "insuranceplans". + +connect 'jdbc:derby:jdotckdb;create=true' user 'tckuser' password 'tckuser'; + +CREATE SCHEMA datastoreidentity4; +SET SCHEMA datastoreidentity4; + +------------------------- +-- company +------------------------- + +ALTER TABLE departments DROP CONSTRAINT EMP_MO_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_PROJ_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_REV_FK; +DROP TABLE insuranceplans; +DROP TABLE project_reviewer; +DROP TABLE project_member; +DROP TABLE employee_phoneno_type; +DROP TABLE employees; +DROP TABLE persons; +DROP TABLE projects; +DROP TABLE departments; +DROP TABLE companies; + +CREATE TABLE companies ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + ID INTEGER, + NAME VARCHAR(32) NOT NULL, + FOUNDEDDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT COMPS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE departments ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + ID INTEGER, + NAME VARCHAR(32) NOT NULL, + EMP_OF_THE_MONTH INTEGER, + COMPANYID INTEGER REFERENCES companies, + CONSTRAINT DEPTS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE persons ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + PERSONID INTEGER UNIQUE NOT NULL, + FIRSTNAME VARCHAR(32) NOT NULL, + LASTNAME VARCHAR(32) NOT NULL, + MIDDLENAME VARCHAR(32), + BIRTHDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT PERS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE employees ( + DATASTORE_IDENTITY INTEGER NOT NULL, + HIREDATE VARCHAR(32), + WEEKLYHOURS FLOAT, + DEPARTMENT INTEGER REFERENCES departments, + FUNDINGDEPT INTEGER REFERENCES departments, + MANAGER INTEGER REFERENCES persons, + MENTOR INTEGER REFERENCES persons, + HRADVISOR INTEGER REFERENCES persons, + WAGE FLOAT, + SALARY FLOAT, + DISCRIMINATOR varchar(64) NOT NULL, + CONSTRAINT EMPS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT EMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons (DATASTORE_IDENTITY) +); + +CREATE TABLE insuranceplans ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + INSID INTEGER, + CARRIER VARCHAR(64) NOT NULL, + EMPLOYEE INTEGER REFERENCES persons, + PLANTYPE VARCHAR(8), + LIFETIME_ORTHO_BENEFIT DECIMAL(22,3), + DISCRIMINATOR varchar(64) NOT NULL, + CONSTRAINT INS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE projects ( + DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + PROJID INTEGER UNIQUE NOT NULL, + NAME VARCHAR(32) NOT NULL, + BUDGET DECIMAL(11,2) NOT NULL, + CONSTRAINT PROJS_PK PRIMARY KEY (DATASTORE_IDENTITY) +); + +CREATE TABLE project_reviewer ( + PROJID INTEGER NOT NULL, + REVIEWER INTEGER NOT NULL +); + +CREATE TABLE project_member ( + PROJID INTEGER REFERENCES projects NOT NULL, + MEMBER INTEGER REFERENCES persons NOT NULL +); + +CREATE TABLE employee_phoneno_type ( + EMPID INTEGER REFERENCES persons NOT NULL, + PHONENO VARCHAR(16) NOT NULL, + TYPE VARCHAR(16) NOT NULL +); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY + (PROJID) REFERENCES projects(PROJID); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_REV_FK FOREIGN KEY + (REVIEWER) REFERENCES persons(PERSONID); + +ALTER TABLE departments + ADD CONSTRAINT EMP_MO_FK FOREIGN KEY + (EMP_OF_THE_MONTH) REFERENCES persons(DATASTORE_IDENTITY); + +disconnect; Property changes on: test/sql/derby/datastoreidentity/schema4.sql ___________________________________________________________________ Name: svn:executable + * Index: test/sql/derby/applicationidentity/schema1.sql =================================================================== --- test/sql/derby/applicationidentity/schema1.sql (revision 0) +++ test/sql/derby/applicationidentity/schema1.sql (revision 0) @@ -0,0 +1,154 @@ +-- SchemaType: application identity + +-- Inheritance mapping: +-- Separate table for each class in the inheritance hierarchy. +-- Each table contains columns for the declared fields. +-- See tables "persons", "employees", "parttimeemployees", +-- "fulltimeemployees", "insuranceplans", "medicalinsurance", and "dentalinsurance". + +connect 'jdbc:derby:jdotckdb;create=true' user 'tckuser' password 'tckuser'; + +CREATE SCHEMA applicationidentity1; +SET SCHEMA applicationidentity1; + +------------------------- +-- company +------------------------- + +ALTER TABLE departments DROP CONSTRAINT EMP_MO_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_PROJ_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_REV_FK; +DROP TABLE dentalinsurance; +DROP TABLE medicalinsurance; +DROP TABLE insuranceplans; +DROP TABLE project_reviewer; +DROP TABLE project_member; +DROP TABLE employee_phoneno_type; +DROP TABLE fulltimeemployees; +DROP TABLE parttimeemployees; +DROP TABLE employees; +DROP TABLE persons; +DROP TABLE projects; +DROP TABLE departments; +DROP TABLE companies; + +CREATE TABLE companies ( + ID INTEGER NOT NULL, + NAME VARCHAR(32) NOT NULL, + FOUNDEDDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT COMPS_PK PRIMARY KEY (ID) +); + +CREATE TABLE departments ( + ID INTEGER NOT NULL, + NAME VARCHAR(32) NOT NULL, + EMP_OF_THE_MONTH INTEGER, + COMPANYID INTEGER REFERENCES companies, + CONSTRAINT DEPTS_PK PRIMARY KEY (ID) +); + +CREATE TABLE persons ( + PERSONID INTEGER NOT NULL, + FIRSTNAME VARCHAR(32) NOT NULL, + LASTNAME VARCHAR(32) NOT NULL, + MIDDLENAME VARCHAR(32), + BIRTHDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT PERS_PK PRIMARY KEY (PERSONID) +); + +CREATE TABLE employees ( + PERSONID INTEGER NOT NULL, + HIREDATE VARCHAR(32), + WEEKLYHOURS FLOAT, + DEPARTMENT INTEGER REFERENCES departments, + FUNDINGDEPT INTEGER REFERENCES departments, + MANAGER INTEGER REFERENCES persons, + MENTOR INTEGER REFERENCES persons, + HRADVISOR INTEGER REFERENCES persons, + CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID), + CONSTRAINT EMPS_FK FOREIGN KEY (PERSONID) REFERENCES persons (PERSONID) +); + +CREATE TABLE parttimeemployees ( + PERSONID INTEGER NOT NULL, + WAGE FLOAT, + CONSTRAINT PTEMPS_PK PRIMARY KEY (PERSONID), + CONSTRAINT PTEMPS_FK FOREIGN KEY (PERSONID) REFERENCES persons (PERSONID) +); + +CREATE TABLE fulltimeemployees ( + PERSONID INTEGER NOT NULL, + SALARY FLOAT, + CONSTRAINT FTEMPS_PK PRIMARY KEY (PERSONID), + CONSTRAINT FTEMPS_FK FOREIGN KEY (PERSONID) REFERENCES persons (PERSONID) +); + +CREATE TABLE insuranceplans ( + INSID INTEGER NOT NULL, + CARRIER VARCHAR(64) NOT NULL, + EMPLOYEE INTEGER REFERENCES persons, + CONSTRAINT INS_PK PRIMARY KEY (INSID) +); + +CREATE TABLE medicalinsurance ( + INSID INTEGER NOT NULL, + PLANTYPE VARCHAR(8), + CONSTRAINT MEDINS_PK PRIMARY KEY (INSID), + CONSTRAINT MEDINS_FK FOREIGN KEY (INSID) REFERENCES insuranceplans (INSID) +); + +CREATE TABLE dentalinsurance ( + INSID INTEGER NOT NULL, + LIFETIME_ORTHO_BENEFIT DECIMAL(22,3), + CONSTRAINT DENTINS_PK PRIMARY KEY (INSID), + CONSTRAINT DENTINS_FK FOREIGN KEY (INSID) REFERENCES insuranceplans (INSID) +); + +CREATE TABLE projects ( + PROJID INTEGER NOT NULL, + NAME VARCHAR(32) NOT NULL, + BUDGET DECIMAL(11,2) NOT NULL, + CONSTRAINT PROJS_PK PRIMARY KEY (PROJID) +); + +CREATE TABLE project_reviewer ( + PROJID INTEGER NOT NULL, + REVIEWER INTEGER NOT NULL +); + +CREATE TABLE project_member ( + PROJID INTEGER REFERENCES projects NOT NULL, + MEMBER INTEGER REFERENCES persons NOT NULL +); + +CREATE TABLE employee_phoneno_type ( + EMPID INTEGER REFERENCES persons NOT NULL, + PHONENO VARCHAR(16) NOT NULL, + TYPE VARCHAR(16) NOT NULL +); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY + (PROJID) REFERENCES projects(PROJID); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_REV_FK FOREIGN KEY + (REVIEWER) REFERENCES persons(PERSONID); + +ALTER TABLE departments + ADD CONSTRAINT EMP_MO_FK FOREIGN KEY + (EMP_OF_THE_MONTH) REFERENCES persons(PERSONID); + +disconnect; Property changes on: test/sql/derby/applicationidentity/schema1.sql ___________________________________________________________________ Name: svn:executable + * Index: test/sql/derby/applicationidentity/schema2.sql =================================================================== --- test/sql/derby/applicationidentity/schema2.sql (revision 0) +++ test/sql/derby/applicationidentity/schema2.sql (revision 0) @@ -0,0 +1,188 @@ +-- SchemaType: application identity + +-- Inheritance mapping: +-- Separate table for each class in the inheritance hierarchy. +-- No tables for abstract classes. +-- Each table contains columns for all fields. +-- Managers, mentors, hradvisors, and employees of the month +-- are fulltime employees. +-- Only fulltime employees can have insurances, can be project members, +-- and can be project reviewers. +-- Separate phone number type tables for persons, fulltime employees, +-- and parttime employees. +-- See tables "persons", "parttimeemployees", +-- "fulltimeemployees", "medicalinsurance", "dentalinsurance", +-- "fulltime_employee_phoneno_type", and "parttime_employee_phoneno_type". + +connect 'jdbc:derby:jdotckdb;create=true' user 'tckuser' password 'tckuser'; + +CREATE SCHEMA applicationidentity2; +SET SCHEMA applicationidentity2; + +------------------------- +-- company +------------------------- + +ALTER TABLE departments DROP CONSTRAINT EMP_MO_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_PROJ_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_REV_FK; +DROP TABLE dentalinsurance; +DROP TABLE medicalinsurance; +DROP TABLE project_reviewer; +DROP TABLE project_member; +DROP TABLE employee_phoneno_type; +DROP TABLE fulltime_employee_phoneno_type; +DROP TABLE parttime_employee_phoneno_type; +DROP TABLE parttimeemployees; +DROP TABLE fulltimeemployees; +DROP TABLE persons; +DROP TABLE projects; +DROP TABLE departments; +DROP TABLE companies; + +CREATE TABLE companies ( + ID INTEGER NOT NULL, + NAME VARCHAR(32) NOT NULL, + FOUNDEDDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT COMPS_PK PRIMARY KEY (ID) +); + +CREATE TABLE departments ( + ID INTEGER NOT NULL, + NAME VARCHAR(32) NOT NULL, + EMP_OF_THE_MONTH INTEGER, + COMPANYID INTEGER REFERENCES companies, + CONSTRAINT DEPTS_PK PRIMARY KEY (ID) +); + +CREATE TABLE persons ( + PERSONID INTEGER NOT NULL, + FIRSTNAME VARCHAR(32) NOT NULL, + LASTNAME VARCHAR(32) NOT NULL, + MIDDLENAME VARCHAR(32), + BIRTHDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT PERS_PK PRIMARY KEY (PERSONID) +); + +CREATE TABLE fulltimeemployees ( + PERSONID INTEGER NOT NULL, + FIRSTNAME VARCHAR(32) NOT NULL, + LASTNAME VARCHAR(32) NOT NULL, + MIDDLENAME VARCHAR(32), + BIRTHDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + HIREDATE VARCHAR(32), + WEEKLYHOURS FLOAT, + DEPARTMENT INTEGER REFERENCES departments, + FUNDINGDEPT INTEGER REFERENCES departments, + MANAGER INTEGER REFERENCES fulltimeemployees, + MENTOR INTEGER REFERENCES fulltimeemployees, + HRADVISOR INTEGER REFERENCES fulltimeemployees, + SALARY FLOAT, + CONSTRAINT FTEMPS_PK PRIMARY KEY (PERSONID) +); + +CREATE TABLE parttimeemployees ( + PERSONID INTEGER NOT NULL, + FIRSTNAME VARCHAR(32) NOT NULL, + LASTNAME VARCHAR(32) NOT NULL, + MIDDLENAME VARCHAR(32), + BIRTHDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + HIREDATE VARCHAR(32), + WEEKLYHOURS FLOAT, + DEPARTMENT INTEGER REFERENCES departments, + FUNDINGDEPT INTEGER REFERENCES departments, + MANAGER INTEGER REFERENCES fulltimeemployees, + MENTOR INTEGER REFERENCES fulltimeemployees, + HRADVISOR INTEGER REFERENCES fulltimeemployees, + WAGE FLOAT, + CONSTRAINT PTEMPS_PK PRIMARY KEY (PERSONID) +); + +CREATE TABLE medicalinsurance ( + INSID INTEGER NOT NULL, + CARRIER VARCHAR(64) NOT NULL, + EMPLOYEE INTEGER REFERENCES fulltimeemployees, + PLANTYPE VARCHAR(8), + CONSTRAINT MEDINS_PK PRIMARY KEY (INSID) +); + +CREATE TABLE dentalinsurance ( + INSID INTEGER NOT NULL, + CARRIER VARCHAR(64) NOT NULL, + EMPLOYEE INTEGER REFERENCES fulltimeemployees, + LIFETIME_ORTHO_BENEFIT DECIMAL(22,3), + CONSTRAINT DENTINS_PK PRIMARY KEY (INSID) +); + +CREATE TABLE projects ( + PROJID INTEGER NOT NULL, + NAME VARCHAR(32) NOT NULL, + BUDGET DECIMAL(11,2) NOT NULL, + CONSTRAINT PROJS_PK PRIMARY KEY (PROJID) +); + +CREATE TABLE project_reviewer ( + PROJID INTEGER NOT NULL, + REVIEWER INTEGER NOT NULL +); + +CREATE TABLE project_member ( + PROJID INTEGER REFERENCES projects NOT NULL, + MEMBER INTEGER REFERENCES fulltimeemployees NOT NULL +); + +CREATE TABLE employee_phoneno_type ( + EMPID INTEGER REFERENCES persons NOT NULL, + PHONENO VARCHAR(16) NOT NULL, + TYPE VARCHAR(16) NOT NULL +); + +CREATE TABLE fulltime_employee_phoneno_type ( + EMPID INTEGER REFERENCES fulltimeemployees NOT NULL, + PHONENO VARCHAR(16) NOT NULL, + TYPE VARCHAR(16) NOT NULL +); + +CREATE TABLE parttime_employee_phoneno_type ( + EMPID INTEGER REFERENCES parttimeemployees NOT NULL, + PHONENO VARCHAR(16) NOT NULL, + TYPE VARCHAR(16) NOT NULL +); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY + (PROJID) REFERENCES projects(PROJID); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_REV_FK FOREIGN KEY + (REVIEWER) REFERENCES fulltimeemployees(PERSONID); + +ALTER TABLE departments + ADD CONSTRAINT EMP_MO_FK FOREIGN KEY + (EMP_OF_THE_MONTH) REFERENCES fulltimeemployees(PERSONID); + +disconnect; Property changes on: test/sql/derby/applicationidentity/schema2.sql ___________________________________________________________________ Name: svn:executable + * Index: test/sql/derby/applicationidentity/schema3.sql =================================================================== --- test/sql/derby/applicationidentity/schema3.sql (revision 0) +++ test/sql/derby/applicationidentity/schema3.sql (revision 0) @@ -0,0 +1,151 @@ +-- SchemaType: application identity + +-- Inheritance mapping: +-- Person has inheritance strategy "new-table". +-- Employee has inheritance strategy "subclass-table". +-- PartTimeEmployee and FullTimeEmployee have inheritance strategy "new-table". +-- Insurance has inheritance strategy "subclass-table". +-- MedicalInsurance and DentalInsurance have inheritance strategy "new-table". +-- See tables "persons", "parttimeemployees", "fulltimeemployees", +-- "medicalinsurance" and "dentalinsurance". + +connect 'jdbc:derby:jdotckdb;create=true' user 'tckuser' password 'tckuser'; + +CREATE SCHEMA applicationidentity3; +SET SCHEMA applicationidentity3; + +------------------------- +-- company +------------------------- + +ALTER TABLE departments DROP CONSTRAINT EMP_MO_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_PROJ_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_REV_FK; +DROP TABLE dentalinsurance; +DROP TABLE medicalinsurance; +DROP TABLE project_reviewer; +DROP TABLE project_member; +DROP TABLE employee_phoneno_type; +DROP TABLE fulltimeemployees; +DROP TABLE parttimeemployees; +DROP TABLE persons; +DROP TABLE projects; +DROP TABLE departments; +DROP TABLE companies; + +CREATE TABLE companies ( + ID INTEGER NOT NULL, + NAME VARCHAR(32) NOT NULL, + FOUNDEDDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT COMPS_PK PRIMARY KEY (ID) +); + +CREATE TABLE departments ( + ID INTEGER NOT NULL, + NAME VARCHAR(32) NOT NULL, + EMP_OF_THE_MONTH INTEGER, + COMPANYID INTEGER REFERENCES companies, + CONSTRAINT DEPTS_PK PRIMARY KEY (ID) +); + +CREATE TABLE persons ( + PERSONID INTEGER NOT NULL, + FIRSTNAME VARCHAR(32) NOT NULL, + LASTNAME VARCHAR(32) NOT NULL, + MIDDLENAME VARCHAR(32), + BIRTHDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT PERS_PK PRIMARY KEY (PERSONID) +); + +CREATE TABLE parttimeemployees ( + PERSONID INTEGER NOT NULL, + HIREDATE VARCHAR(32), + WEEKLYHOURS FLOAT, + DEPARTMENT INTEGER REFERENCES departments, + FUNDINGDEPT INTEGER REFERENCES departments, + MANAGER INTEGER REFERENCES persons, + MENTOR INTEGER REFERENCES persons, + HRADVISOR INTEGER REFERENCES persons, + WAGE FLOAT, + CONSTRAINT PTEMPS_PK PRIMARY KEY (PERSONID), + CONSTRAINT PTEMPS_FK FOREIGN KEY (PERSONID) REFERENCES persons (PERSONID) +); + +CREATE TABLE fulltimeemployees ( + PERSONID INTEGER NOT NULL, + HIREDATE VARCHAR(32), + WEEKLYHOURS FLOAT, + DEPARTMENT INTEGER REFERENCES departments, + FUNDINGDEPT INTEGER REFERENCES departments, + MANAGER INTEGER REFERENCES persons, + MENTOR INTEGER REFERENCES persons, + HRADVISOR INTEGER REFERENCES persons, + SALARY FLOAT, + CONSTRAINT FTEMPS_PK PRIMARY KEY (PERSONID), + CONSTRAINT FTEMPS_FK FOREIGN KEY (PERSONID) REFERENCES persons (PERSONID) +); + +CREATE TABLE medicalinsurance ( + INSID INTEGER NOT NULL, + CARRIER VARCHAR(64) NOT NULL, + EMPLOYEE INTEGER REFERENCES persons, + PLANTYPE VARCHAR(8), + CONSTRAINT MEDINS_PK PRIMARY KEY (INSID) +); + +CREATE TABLE dentalinsurance ( + INSID INTEGER NOT NULL, + CARRIER VARCHAR(64) NOT NULL, + EMPLOYEE INTEGER REFERENCES persons, + LIFETIME_ORTHO_BENEFIT DECIMAL(22,3), + CONSTRAINT DENTINS_PK PRIMARY KEY (INSID) +); + +CREATE TABLE projects ( + PROJID INTEGER NOT NULL, + NAME VARCHAR(32) NOT NULL, + BUDGET DECIMAL(11,2) NOT NULL, + CONSTRAINT PROJS_PK PRIMARY KEY (PROJID) +); + +CREATE TABLE project_reviewer ( + PROJID INTEGER NOT NULL, + REVIEWER INTEGER NOT NULL +); + +CREATE TABLE project_member ( + PROJID INTEGER REFERENCES projects NOT NULL, + MEMBER INTEGER REFERENCES persons NOT NULL +); + +CREATE TABLE employee_phoneno_type ( + EMPID INTEGER REFERENCES persons NOT NULL, + PHONENO VARCHAR(16) NOT NULL, + TYPE VARCHAR(16) NOT NULL +); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY + (PROJID) REFERENCES projects(PROJID); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_REV_FK FOREIGN KEY + (REVIEWER) REFERENCES persons(PERSONID); + +ALTER TABLE departments + ADD CONSTRAINT EMP_MO_FK FOREIGN KEY + (EMP_OF_THE_MONTH) REFERENCES persons(PERSONID); + +disconnect; Property changes on: test/sql/derby/applicationidentity/schema3.sql ___________________________________________________________________ Name: svn:executable + * Index: test/sql/derby/applicationidentity/schema4.sql =================================================================== --- test/sql/derby/applicationidentity/schema4.sql (revision 0) +++ test/sql/derby/applicationidentity/schema4.sql (revision 0) @@ -0,0 +1,128 @@ +-- SchemaType: application identity + +-- Inheritance mapping: +-- Person, Employee, and Insurance have inheritance strategy "new-table". +-- PartTimeEmployee, FullTimeEmployee, MedicalInsurance, and DentalInsurance +-- have inheritance strategy "superclass-table". +-- See tables "persons", "employees", and "insuranceplans". + +connect 'jdbc:derby:jdotckdb;create=true' user 'tckuser' password 'tckuser'; + +CREATE SCHEMA applicationidentity4; +SET SCHEMA applicationidentity4; + +------------------------- +-- company +------------------------- + +ALTER TABLE departments DROP CONSTRAINT EMP_MO_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_PROJ_FK; +ALTER TABLE project_reviewer DROP CONSTRAINT PR_REV_FK; +DROP TABLE insuranceplans; +DROP TABLE project_reviewer; +DROP TABLE project_member; +DROP TABLE employee_phoneno_type; +DROP TABLE employees; +DROP TABLE persons; +DROP TABLE projects; +DROP TABLE departments; +DROP TABLE companies; + +CREATE TABLE companies ( + ID INTEGER NOT NULL, + NAME VARCHAR(32) NOT NULL, + FOUNDEDDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT COMPS_PK PRIMARY KEY (ID) +); + +CREATE TABLE departments ( + ID INTEGER NOT NULL, + NAME VARCHAR(32) NOT NULL, + EMP_OF_THE_MONTH INTEGER, + COMPANYID INTEGER REFERENCES companies, + CONSTRAINT DEPTS_PK PRIMARY KEY (ID) +); + +CREATE TABLE persons ( + PERSONID INTEGER NOT NULL, + FIRSTNAME VARCHAR(32) NOT NULL, + LASTNAME VARCHAR(32) NOT NULL, + MIDDLENAME VARCHAR(32), + BIRTHDATE VARCHAR(32) NOT NULL, + ADDRID INTEGER, + STREET VARCHAR(64), + CITY VARCHAR(64), + STATE CHAR(2), + ZIPCODE CHAR(5), + COUNTRY VARCHAR(64), + CONSTRAINT PERS_PK PRIMARY KEY (PERSONID) +); + +CREATE TABLE employees ( + PERSONID INTEGER NOT NULL, + HIREDATE VARCHAR(32), + WEEKLYHOURS FLOAT, + DEPARTMENT INTEGER REFERENCES departments, + FUNDINGDEPT INTEGER REFERENCES departments, + MANAGER INTEGER REFERENCES persons, + MENTOR INTEGER REFERENCES persons, + HRADVISOR INTEGER REFERENCES persons, + WAGE FLOAT, + SALARY FLOAT, + DISCRIMINATOR varchar(64) NOT NULL, + CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID), + CONSTRAINT EMPS_FK FOREIGN KEY (PERSONID) REFERENCES persons (PERSONID) +); + +CREATE TABLE insuranceplans ( + INSID INTEGER NOT NULL, + CARRIER VARCHAR(64) NOT NULL, + EMPLOYEE INTEGER REFERENCES persons, + PLANTYPE VARCHAR(8), + LIFETIME_ORTHO_BENEFIT DECIMAL(22,3), + DISCRIMINATOR varchar(64) NOT NULL, + CONSTRAINT INS_PK PRIMARY KEY (INSID) +); + +CREATE TABLE projects ( + PROJID INTEGER NOT NULL, + NAME VARCHAR(32) NOT NULL, + BUDGET DECIMAL(11,2) NOT NULL, + CONSTRAINT PROJS_PK PRIMARY KEY (PROJID) +); + +CREATE TABLE project_reviewer ( + PROJID INTEGER NOT NULL, + REVIEWER INTEGER NOT NULL +); + +CREATE TABLE project_member ( + PROJID INTEGER REFERENCES projects NOT NULL, + MEMBER INTEGER REFERENCES persons NOT NULL +); + +CREATE TABLE employee_phoneno_type ( + EMPID INTEGER REFERENCES persons NOT NULL, + PHONENO VARCHAR(16) NOT NULL, + TYPE VARCHAR(16) NOT NULL +); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY + (PROJID) REFERENCES projects(PROJID); + +ALTER TABLE project_reviewer + ADD CONSTRAINT PR_REV_FK FOREIGN KEY + (REVIEWER) REFERENCES persons(PERSONID); + +ALTER TABLE departments + ADD CONSTRAINT EMP_MO_FK FOREIGN KEY + (EMP_OF_THE_MONTH) REFERENCES persons(PERSONID); + +disconnect; Property changes on: test/sql/derby/applicationidentity/schema4.sql ___________________________________________________________________ Name: svn:executable + * Index: test/conf/inheritance1.conf =================================================================== --- test/conf/inheritance1.conf (revision 0) +++ test/conf/inheritance1.conf (revision 0) @@ -0,0 +1,7 @@ +jdo.tck.description = Completeness test with inheritance mapping 1: \ +Separate table for each class in the inheritance hierarchy. \ +Each table contains columns for the declared fields. \ +Inheritance strategy: new-table for all classes. +jdo.tck.classes = org.apache.jdo.tck.mapping.CompletenessTest +jdo.tck.testdata = org/apache/jdo/tck/pc/company/companyNoRelationships.xml +jdo.tck.mapping = 1 Property changes on: test/conf/inheritance1.conf ___________________________________________________________________ Name: svn:executable + * Index: test/conf/inheritance2.conf =================================================================== --- test/conf/inheritance2.conf (revision 0) +++ test/conf/inheritance2.conf (revision 0) @@ -0,0 +1,14 @@ +jdo.tck.description = Completeness test with inheritance mapping 2: \ +Separate table for each class in the inheritance hierarchy. \ +No tables for abstract classes. \ +Each table contains columns for all fields. \ +Managers, mentors, hradvisors, and employees of the month \ +are fulltime employees. \ +Only fulltime employees can have insurances, can be project members, \ +and can be project reviewers. \ +Separate phone number type tables for persons, fulltime employees, \ +and parttime employees. \ +Inheritance strategy: new-table for all classes. +jdo.tck.classes = org.apache.jdo.tck.mapping.CompletenessTest +jdo.tck.testdata = org/apache/jdo/tck/pc/company/companyNoRelationships.xml +jdo.tck.mapping = 2 Property changes on: test/conf/inheritance2.conf ___________________________________________________________________ Name: svn:executable + * Index: test/conf/inheritance3.conf =================================================================== --- test/conf/inheritance3.conf (revision 0) +++ test/conf/inheritance3.conf (revision 0) @@ -0,0 +1,9 @@ +jdo.tck.description = Completeness test with inheritance mapping 3: \ +Person has inheritance strategy "new-table". \ +Employee has inheritance strategy "subclass-table". \ +PartTimeEmployee and FullTimeEmployee has inheritance strategy "new-table". \ +Insurance has inheritance strategy "subclass-table". \ +MedicalInsurance and DentalInsurance have inheritance strategy "new-table". +jdo.tck.classes = org.apache.jdo.tck.mapping.CompletenessTest +jdo.tck.testdata = org/apache/jdo/tck/pc/company/companyNoRelationships.xml +jdo.tck.mapping = 3 Property changes on: test/conf/inheritance3.conf ___________________________________________________________________ Name: svn:executable + * Index: test/conf/inheritance4.conf =================================================================== --- test/conf/inheritance4.conf (revision 0) +++ test/conf/inheritance4.conf (revision 0) @@ -0,0 +1,7 @@ +jdo.tck.description = Completeness test with inheritance mapping 4: \ +Person, Employee, and Insurance have inheritance strategy "new-table". \ +PartTimeEmployee, FullTimeEmployee, MedicalInsurance, and DentalInsurance \ +have inheritance strategy "superclass-table". +jdo.tck.classes = org.apache.jdo.tck.mapping.CompletenessTest +jdo.tck.testdata = org/apache/jdo/tck/pc/company/companyNoRelationships.xml +jdo.tck.mapping = 4 Property changes on: test/conf/inheritance4.conf ___________________________________________________________________ Name: svn:executable + * Index: test/conf/configurations.list =================================================================== --- test/conf/configurations.list (revision 264788) +++ test/conf/configurations.list (working copy) @@ -5,5 +5,9 @@ company1-1Relationships.conf \ company1-MRelationships.conf \ companyM-MRelationships.conf \ - companyAllRelationships.conf + companyAllRelationships.conf \ + inheritance1.conf \ + inheritance2.conf \ + inheritance3.conf \ + inheritance4.conf --------------040404010507090800010900--