foundation/org.eclipse.persistence.core.test.framework/src/main/java/org/eclipse/persistence/testing/tests/ClearDatabaseSchemaTest.java

/*
 * Copyright (c) 1998, 2020 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:
//     21/08/2013-2.6 Chris Delahunt
package org.eclipse.persistence.testing.tests;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import java.util.Vector;

import org.eclipse.persistence.exceptions.DatabaseException;
import org.eclipse.persistence.internal.databaseaccess.Platform;
import org.eclipse.persistence.internal.sessions.AbstractSession;
import org.eclipse.persistence.internal.sessions.ArrayRecord;
import org.eclipse.persistence.logging.AbstractSessionLog;
import org.eclipse.persistence.sessions.DatabaseLogin;
import org.eclipse.persistence.testing.framework.TestCase;

/**
 * This test is used to allow clearing the schema before running automated tests.
 * It is not intended to test behavior, but might be included in test runs where problems can be recorded.
 *
 */
public class ClearDatabaseSchemaTest extends TestCase {

    public ClearDatabaseSchemaTest() {
        setDescription("Clears the database for MYSQL, Oracle DB, Derby, MSSQL, HSQL, Postgres.");
    }

    @Override
    public void test() {
        //TODO: add missing platforms, currently supported are:
        //MySQL, Oracle DB, Derby, HSQLDB, PostgreSQL, MSSQL
        AbstractSession session = (AbstractSession) getSession();
        Platform platform = session.getDatasourcePlatform();
        if (platform.isMySQL()) {
            resetMySQL(session);
        } else if (platform.isOracle()) {
            resetOracle(session);
        } else if (platform.isDerby()) {
            resetDerby(session);
        } else if (platform.isSQLServer()) {
            resetMSSQL(session);
        } else if (platform.isHSQL()) {
            resetHsql(session);
        } else if (platform.isPostgreSQL()) {
            resetPostgres(session);
        } else {
            fail("Clear DB test run on unsupported DB");
        }
        getDatabaseSession().logout();
        getDatabaseSession().login();
    }

    private void resetMySQL(AbstractSession session) {
        ArrayRecord record = null;
        try {
            record = (ArrayRecord) session.executeSQL("select DATABASE()").get(0);
            session.executeNonSelectingSQL("drop database " + record.get("DATABASE()"));
        } catch (DatabaseException x) {
            AbstractSessionLog.getLog().warning("Failed to drop database");
            // Using System.err since session log may not print out the stack trace
            x.printStackTrace(System.err);
        } finally {
            if (record != null) {
                session.executeNonSelectingSQL("create database " + record.get("DATABASE()"));
            } else {
                DatabaseLogin databaseLogin = (DatabaseLogin) session.getDatasourceLogin();
                String url = databaseLogin.getDatabaseURL();

                Properties properties = new Properties();
                properties.put("user", databaseLogin.getUserName());
                properties.put("password", databaseLogin.getPassword());

                int databaseNameSeparatorIndex = url.lastIndexOf('/');
                String databaseName = url.substring(databaseNameSeparatorIndex + 1);
                int propertiesIndex = databaseName.indexOf('?');
                if (propertiesIndex > 0) {
                    for (String propertyString : databaseName.substring(propertiesIndex + 1).split("&")) {
                        String[] propertyDetails = propertyString.split("=");
                        properties.put(propertyDetails[0].trim(), propertyDetails[1].trim());
                    }
                    databaseName = databaseName.substring(0, propertiesIndex);
                }
                url = url.substring(0, databaseNameSeparatorIndex);

                try (Connection connection = DriverManager.getConnection(url, properties)) {
                    connection.prepareStatement("create database " + databaseName).execute();
                } catch (SQLException e) {
                    // Using System.err since session log may not print out the stack trace
                    e.printStackTrace(System.err);
                }
            }
        }
        //unused for now but kept here for alternate option
//        Vector<ArrayRecord> result = session.executeSQL("SELECT concat('ALTER TABLE ', C.TABLE_SCHEMA, '.', C.TABLE_NAME, ' DROP FOREIGN KEY ', C.CONSTRAINT_NAME) "
//                + "FROM information_schema.TABLE_CONSTRAINTS C "
//                + "WHERE C.TABLE_SCHEMA = (SELECT SCHEMA()) and C.CONSTRAINT_TYPE = 'FOREIGN KEY'");
//        List<String> toRetry = execStatements(session, result);
//        result = session.executeSQL("SELECT concat('DROP TABLE IF EXISTS ', T.TABLE_SCHEMA, '.', T.TABLE_NAME) "
//                + "FROM information_schema.TABLES T WHERE T.TABLE_SCHEMA = (SELECT SCHEMA())");
//        toRetry.addAll(execStatements(session, result));
//        assertTrue(toRetry + " statements failed", toRetry.isEmpty());
    }

    private void resetOracle(AbstractSession session) {
        session.executeNonSelectingSQL("BEGIN FOR cur_rec IN (\n" + "SELECT object_name, object_type FROM user_objects WHERE object_type IN "
                + "('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE', 'TYPE'))\n"
                + "LOOP BEGIN IF cur_rec.object_type = 'TABLE' "
                + "THEN EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' \"' || cur_rec.object_name || '\" CASCADE CONSTRAINTS'; "
                + "ELSIF cur_rec.object_type = 'TYPE' THEN EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' \"' || cur_rec.object_name || '\" FORCE'; "
                + "ELSE EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' \"' || cur_rec.object_name || '\"'; "
                + "END IF;\nEXCEPTION WHEN OTHERS "
                + "THEN DBMS_OUTPUT.put_line ('FAILED: DROP ' || cur_rec.object_type || ' \"' || cur_rec.object_name || '\"');"
                + "END;\nEND LOOP; END;");
        session.executeNonSelectingSQL("PURGE user_recyclebin");
        session.executeNonSelectingSQL("PURGE recyclebin");
    }

    private void resetDerby(AbstractSession session) {
        Vector<ArrayRecord> result = session.executeSQL("SELECT 'ALTER TABLE '||S.SCHEMANAME||'.'||T.TABLENAME||' DROP CONSTRAINT '||C.CONSTRAINTNAME\n"
                + "FROM SYS.SYSCONSTRAINTS C, SYS.SYSSCHEMAS S, SYS.SYSTABLES T\n"
                + "WHERE C.SCHEMAID = S.SCHEMAID AND C.TABLEID = T.TABLEID AND S.SCHEMANAME = CURRENT SCHEMA ORDER BY C.REFERENCECOUNT DESC");
        List<String> toRetry = execStatements(session, result);
        final int MAX_ROUNDS = 3;
        int round = 0;
        while (!toRetry.isEmpty() && round < MAX_ROUNDS ) {
            for (Iterator<String> i = toRetry.iterator(); i.hasNext();) {
                String stmt = i.next();
                try {
                    session.executeNonSelectingSQL(stmt);
                    i.remove();
                } catch (DatabaseException de) {
                    //ignore, next round may be successful
                }
            }
            round++;
        }
        result = session.executeSQL("SELECT 'DROP TABLE ' || schemaname ||'.' || tablename FROM SYS.SYSTABLES\n"
                + "INNER JOIN SYS.SYSSCHEMAS ON SYS.SYSTABLES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID\n"
                + "WHERE schemaname = CURRENT SCHEMA");
        toRetry.addAll(execStatements(session, result));
        assertTrue(toRetry + " statements failed", toRetry.isEmpty());
    }

    private void resetMSSQL(AbstractSession session) {
        session.executeNonSelectingSQL(
                "DECLARE @name VARCHAR(256)\n" +
                "DECLARE @subName VARCHAR(256)\n" +
                "DECLARE @statement VARCHAR(256)\n" +
                "WHILE((SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME()) > 0)\n" +
                "BEGIN\n" +
                "\tSELECT TOP 1 @name=TABLE_NAME, @subName=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() ORDER BY CONSTRAINT_TYPE\n" +
                "    SELECT @statement = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@subName) +']'\n" +
                "    EXEC (@statement)\n" +
                "END\n" +
                "WHILE((SELECT COUNT(1) FROM sysobjects WHERE [type] IN ('P', 'V', N'FN', N'IF', N'TF', N'FS', N'FT', 'U') AND category = 0) > 0)\n" +
                "BEGIN\n" +
                "\tSELECT TOP 1 @name=[name], @subName=[type] FROM sysobjects WHERE [type] IN ('P', 'V', N'FN', N'IF', N'TF', N'FS', N'FT', 'U') AND category = 0\n" +
                "    SELECT @statement = 'DROP ' + CASE @subName WHEN 'P' THEN 'PROCEDURE' WHEN 'V' THEN 'VIEW' WHEN 'U' THEN 'TABLE' ELSE 'FUNCTION' END + ' [dbo].[' + RTRIM(@name) +']'\n" +
                "    EXEC (@statement)\n" +
                "END");
    }

    private void resetHsql(AbstractSession session) {
        Vector<ArrayRecord> result = session.executeSQL("select 'DROP TABLE \"' || table_name || '\" CASCADE' FROM INFORMATION_SCHEMA.system_tables "
                + "WHERE table_type = 'TABLE' and table_schem = CURRENT_SCHEMA");
        List<String> toRetry = execStatements(session, result);
        assertTrue(toRetry + " statements failed", toRetry.isEmpty());
    }

    private void resetPostgres(AbstractSession session) {
        Vector<ArrayRecord> result = getSession().executeSQL("SELECT 'DROP TABLE \"' || tablename || '\" CASCADE;' "
                + "FROM pg_tables WHERE schemaname = current_schema();");
        List<String> toRetry = execStatements(session, result);
        assertTrue(toRetry + " statements failed", toRetry.isEmpty());
    }

    private List<String> execStatements(AbstractSession session, Vector<ArrayRecord> records) {
        List<String> failures = new ArrayList<String>();
        for (ArrayRecord ar : records) {
            for (Object o : ar.values()) {
                String stmt = (String) o;
                try {
                    session.executeNonSelectingSQL(stmt);
                } catch (DatabaseException t) {
                    failures.add(stmt);
                }
            }
        }
        return failures;
    }
}