foundation/eclipselink.core.test/src/it/java/org/eclipse/persistence/testing/tests/customsqlstoredprocedures/InsuranceORStoredProcedureSystem.java

/*
 * Copyright (c) 1998, 2021 Oracle and/or its affiliates. All rights reserved.
 *
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v. 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0,
 * or the Eclipse Distribution License v. 1.0 which is available at
 * http://www.eclipse.org/org/documents/edl-v10.php.
 *
 * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
 */

// Contributors:
//     Oracle - initial API and implementation from Oracle TopLink
package org.eclipse.persistence.testing.tests.customsqlstoredprocedures;

import org.eclipse.persistence.sessions.DatabaseSession;
import org.eclipse.persistence.tools.schemaframework.FieldDefinition;
import org.eclipse.persistence.tools.schemaframework.SchemaManager;
import org.eclipse.persistence.tools.schemaframework.StoredProcedureDefinition;

/*
 * modeled after EmployeeCustomSQLSystem using the underlying ObjectRelational classes from
 * InsuranceORSystem instead of the Employee model.
 */

public class InsuranceORStoredProcedureSystem extends org.eclipse.persistence.testing.models.insurance.objectrelational.InsuranceORSystem{
    public InsuranceORStoredProcedureSystem() {
    }

        @Override
        public void addDescriptors(DatabaseSession session) {
            session.logout();
            super.addDescriptors(session);

            // Force re-initialize.
            session.login();
        }

        public StoredProcedureDefinition buildOracleStoredProcedureInsertPolicyHolders() {
            StoredProcedureDefinition proc = new StoredProcedureDefinition();
            proc.setName("SProc_Insert_PHolders");

            proc.addArgument("ssn", Long.class);
            proc.addArgument("occupation", String.class, 20);
            proc.addArgument("sex", Character.class);
            proc.addArgument("firstName", String.class, 20);
            proc.addArgument("birthDate", java.sql.Date.class);
            proc.addArgument("lastName", String.class, 20);

            proc.addArgument(new FieldDefinition("address", "Address_type"));
            proc.addArgument(new FieldDefinition("childrenNames", "NameList_type"));
            proc.addArgument("phones", "PhoneList_type");

            String statement = "INSERT INTO PolicyHolders "+
                    "(SSN, OCCUPATION, SEX, FIRSTNAME, BIRTHDATE, LASTNAME, ADDRESS, PHONES, CHILDRENNAMES, POLICIES) "+
                    "VALUES (ssn, occupation, sex, firstName, birthDate, lastName, address, phones, childrenNames, null)";
            proc.addStatement(statement);
            return proc;
        }

        public StoredProcedureDefinition buildOracleStoredProcedureReadFromPolicyHolders() {
            StoredProcedureDefinition proc = new StoredProcedureDefinition();
            proc.setName("SProc_Read_PHolders");

            proc.addInOutputArgument("ssn_v", Long.class);
            proc.addOutputArgument("occupation_v", String.class, 20);
            proc.addOutputArgument("sex_v", Character.class);
            proc.addOutputArgument("firstName_v", String.class, 20);
            proc.addOutputArgument("birthDate_v", java.sql.Date.class);
            proc.addOutputArgument("lastName_v", String.class, 20);

            proc.addOutputArgument("address_v", "Address_type");
            proc.addOutputArgument("childrenNames_v", "NameList_type");
            proc.addOutputArgument("phones_v", "PhoneList_type");

            String statement = "SELECT SSN, OCCUPATION, SEX, FIRSTNAME, BIRTHDATE, LASTNAME, ADDRESS, PHONES, CHILDRENNAMES "/*, POLICIES "*/+
                    "into ssn_v, occupation_v, sex_v, firstName_v, birthDate_v, lastName_v, address_v, phones_v, childrenNames_v "/*, policies_v "*/+
                    "from PolicyHolders where (SSN = ssn_v)";

            proc.addStatement(statement);
            return proc;
        }

        public StoredProcedureDefinition buildOracleStoredProcedureDeletePolicyHolders() {
            StoredProcedureDefinition proc = new StoredProcedureDefinition();
            proc.setName("SProc_Delete_PHolders");

            proc.addInOutputArgument("ssn_v", Long.class);
            proc.addInOutputArgument(new FieldDefinition("occupation_v", String.class, 20));
            proc.addInOutputArgument("sex_v", Character.class);
            proc.addInOutputArgument(new FieldDefinition("firstName_v", String.class, 20));
            proc.addInOutputArgument("birthDate_v", java.sql.Date.class);
            proc.addInOutputArgument(new FieldDefinition("lastName_v", String.class, 20));

            proc.addInOutputArgument(new FieldDefinition("address_v", "Address_type"));
            proc.addInOutputArgument(new FieldDefinition("childrenNames_v", "NameList_type"));
            proc.addInOutputArgument(new FieldDefinition("phones_v", "PhoneList_type"));

            String statement = "SELECT SSN, OCCUPATION, SEX, FIRSTNAME, BIRTHDATE, LASTNAME, ADDRESS, PHONES, CHILDRENNAMES "/*, POLICIES "*/+
                    "into ssn_v, occupation_v, sex_v, firstName_v, birthDate_v, lastName_v, address_v, phones_v, childrenNames_v "/*, policies_v "*/+
                    "from PolicyHolders where (SSN = ssn_v)";

            proc.addStatement(statement);
            proc.addStatement("DELETE FROM PolicyHolders where (SSN = ssn)");
            return proc;
        }

        /**
         * Also creates the procs.
         */
         @Override
         public void createTables (DatabaseSession session) {
            super.createTables(session);
            org.eclipse.persistence.internal.databaseaccess.DatabasePlatform platform = session.getLogin().getPlatform();
            SchemaManager schema = new SchemaManager((session));

            if (platform.isSQLServer()) {

            }
            if (platform.isSybase() || platform.isSQLAnywhere()) {
            }
            if (platform.isOracle()) {

                schema.replaceObject(buildOracleStoredProcedureInsertPolicyHolders());
                schema.replaceObject(buildOracleStoredProcedureReadFromPolicyHolders());
                schema.replaceObject(buildOracleStoredProcedureDeletePolicyHolders());
            }
            if (platform.isDB2()) {
            }
        }

    }