OracleDataBasePreparator

package de.fhdw.wtf.persistence.facade;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.jdbc.OracleTypes;
import de.fhdw.wtf.persistence.exception.OtherSQLException;
import de.fhdw.wtf.persistence.exception.PersistenceException;

/**
 * This class has the ability to check the database for a valid table and procedure structure which is needed for the
 * meta model and to create and drop the structure.
 *
 * Hint: - The Singleton OracleDatabaseManager must have a database connection before you can use this class. - If you
 * rename one of the .sql schema script you need change the names here as well.
 */
public class OracleDataBasePreparator {

	/**
	 * instance of the oracle database manager.
	 */
	private OracleDatabaseManager databaseManager = null;

	private static final String CALL_SUFFIX = " end;";
	private static final String STORED_FUNCTION_PREFIX = "begin ? := ";
	private static final int ORACLE_EXCEPTION_OBJECT_WITH_NAME_ALREADY_EXISTS = 955;
	private static final int ORACLE_EXCEPTION_TABLE_OR_VIEW_DOES_NOT_EXIST = 942;
	private static final int ORACLE_EXCEPTION_SEQUENCER_DOES_NOT_EXIST = 2289;
	private static final int ORACLE_EXCEPTION_OBJECT_DOES_NOT_EXIST = 4043;

	// relative path using "../Database" for correct resolution from different
	// project
	/** Path to the directory of all database scripts. */
	private static final String SCRIPT_ROOT_DIR = "scripts/";

	/**
	 * Name of the procedure which checks the table structure.
	 */
	private static final String PROCEDURE_NAME_CHECK_VALID_TABLE_STRUCTURE = "isValidTableStructureCreated";
	/**
	 * Name of the procedure which checks if all necessary procedures are available.
	 */
	private static final String PROCEDURE_NAME_CHECK_VALID_PROCEDURES = "areProceduresCreated";

	/**
	 * name of the initRoutines SQL-package.
	 */
	private static final String PACKAGE_NAME_INIT_ROUTINES = "InitRoutines";

	/**
	 * filename to the initRoutines SQL-package.
	 */
	private static final String SCRIPT_NAME_INIT_ROUTINES = "InitRoutines.sql";

	/**
	 * filename to the initRoutines SQL-package body.
	 */
	private static final String SCRIPT_NAME_INIT_ROUTINES_BODY = "InitRoutinesBody.sql";

	/**
	 * filename to the meta model schema.
	 */
	private static final String SCRIPT_NAME_CREATE_SCHEMA_TABLES = "schemaF.sql";

	/**
	 * filename to the userexception SQL-package.
	 */
	private static final String SCRIPT_NAME_CREATE_SCHEMA_EXCEPTION = "userException.sql";

	/**
	 * filename to the drop statements.
	 */
	private static final String SCRIPT_NAME_DROP_SCHEMA = "dropStatements.sql";

	/**
	 * filename to the objectfacade SQL-package.
	 */
	private static final String SCRIPT_NAME_CREATE_PROCEDURES_OBJECT_FACADE = "objectfacade.sql";

	/**
	 * filename to the objectfacade SQL-package body.
	 */
	private static final String SCRIPT_NAME_CREATE_PROCEDURES_OBJECT_FACADE_BODY = "objectfacadebody.sql";

	/**
	 * filename to the classfacade SQL-package.
	 */
	private static final String SCRIPT_NAME_CREATE_PROCEDURES_CLASS_FACADE = "classfacade.sql";

	/**
	 * filename to the classfacade SQL-package body.
	 */
	private static final String SCRIPT_NAME_CREATE_PROCEDURES_CLASS_FACADE_BODY = "classfacadebody.sql";

	/**
	 * filename to the accountfacade SQL-package.
	 */
	private static final String SCRIPT_NAME_CREATE_PROCEDURES_ACCOUNT_FACADE = "accountfacade.sql";

	/**
	 * filename to the accountfacade SQL-package body.
	 */
	private static final String SCRIPT_NAME_CREATE_PROCEDURES_ACCOUNT_FACADE_BODY = "accountfacadebody.sql";

	/**
	 * constructor of the databasepreperator.
	 */
	public OracleDataBasePreparator() {
		this.databaseManager = OracleDatabaseManager.getInstance();
	}

	private String readFile(final String path, @SuppressWarnings("unused") final Charset encoding) throws IOException {
		final ClassLoader classLoader = this.getClass().getClassLoader();
		final InputStream inputStream = classLoader.getResourceAsStream(path);
		final StringBuilder stringBuilder = new StringBuilder();
		try (final BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream))) {
			String line;
			while ((line = reader.readLine()) != null) {
				stringBuilder.append(line);
				stringBuilder.append("\n");
			}
		}
		return stringBuilder.toString();
	}

	/**
	 * Creates the initialization routines on the database.
	 *
	 * @throws OtherSQLException
	 *             will be thrown when a sql error is thrown
	 * @throws PersistenceException
	 *             will be thrown when a persistence error occurs.
	 * @throws IOException
	 *             will be thrown when a access to a file is not valid
	 * */
	private void createInitRoutines() throws PersistenceException, IOException {
		try (final CallableStatement initRoutines =
				this.databaseManager.getConnection().prepareCall(
						this.readFile(SCRIPT_ROOT_DIR + SCRIPT_NAME_INIT_ROUTINES, Charset.defaultCharset()))) {
			initRoutines.execute();
		} catch (final SQLException e) {
			throw new OtherSQLException(e);
		}
		try (final CallableStatement initRoutinesBody =
				this.databaseManager.getConnection().prepareCall(
						this.readFile(SCRIPT_ROOT_DIR + SCRIPT_NAME_INIT_ROUTINES_BODY, Charset.defaultCharset()))) {
			initRoutinesBody.execute();
		} catch (final SQLException e) {
			throw new OtherSQLException(e);
		}
	}

	/**
	 * This operation checks if the table structure for the given meta model is valid. Returns true if everything is ok.
	 *
	 * @return true if the stucture is valid
	 * @throws PersistenceException
	 *             will thrown if a persistence error occurs
	 * @throws IOException
	 *             will be thrown when a access to a file is not valid
	 */
	public boolean isTableStructureValid() throws IOException, PersistenceException {
		this.createInitRoutines();

		try (CallableStatement isValidTableStructureCreated =
				this.databaseManager.getConnection().prepareCall(
						STORED_FUNCTION_PREFIX + OracleDatabaseManager.getInstance().getSchemaName() + '.'
								+ PACKAGE_NAME_INIT_ROUTINES + '.' + PROCEDURE_NAME_CHECK_VALID_TABLE_STRUCTURE + ';'
								+ CALL_SUFFIX)) {
			isValidTableStructureCreated.registerOutParameter(1, OracleTypes.NUMBER);
			try {
				isValidTableStructureCreated.execute();
				return isValidTableStructureCreated.getInt(1) != 0;
			} catch (final SQLException e1) {
				/* perhaps there is no InitRoutines package yet? assume empty database */
				return false;
			}
		} catch (final SQLException e) {
			e.printStackTrace();
			throw new OtherSQLException(e);
		}
	}

	/**
	 * Checks if all needed procedures and functions for the meta model access exist. Returns true if everything is ok.
	 *
	 * @return true if all needed procedures and functions are exists
	 * @throws IOException
	 *             will thrown if a file is not accessable
	 * @throws PersistenceException
	 *             will thrown if a persistence error occurs
	 * */
	public boolean areProceduresCreated() throws IOException, PersistenceException {
		this.createInitRoutines();

		try (CallableStatement areValidProceduresCreated =
				this.databaseManager.getConnection().prepareCall(
						STORED_FUNCTION_PREFIX + OracleDatabaseManager.getInstance().getSchemaName() + '.'
								+ PACKAGE_NAME_INIT_ROUTINES + '.' + PROCEDURE_NAME_CHECK_VALID_PROCEDURES + ';'
								+ CALL_SUFFIX)) {
			areValidProceduresCreated.registerOutParameter(1, OracleTypes.NUMBER);
			try {
				areValidProceduresCreated.execute();
				return areValidProceduresCreated.getInt(1) != 0;
			} catch (final SQLException e1) {
				/* perhaps there is no InitRoutines package yet? assume empty database */
				return false;
			}
		} catch (final SQLException e1) {
			throw new OtherSQLException(e1);
		}
	}

	/**
	 * Creates the schema for the database. If a few tables are missing they will be added by this operation.
	 *
	 * @throws PersistenceException
	 *             will thrown if a persistence error occurs
	 * @throws IOException
	 *             will thrown if a file is not accessible
	 * */
	public void createWholeSchema() throws PersistenceException, IOException {
		try (final Statement statement = this.databaseManager.getConnection().createStatement()) {
			final String tableSchemaString =
					this.readFile(SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_SCHEMA_TABLES, Charset.defaultCharset());

			for (final String part : tableSchemaString.split(";")) {
				try {
					statement.execute(part);
				} catch (final SQLException e) {
					if (e.getErrorCode() != ORACLE_EXCEPTION_OBJECT_WITH_NAME_ALREADY_EXISTS) {
						throw e;
					}
				}
			}

			// databaseManager.getConnection().commit();
		} catch (final SQLException e) {
			e.printStackTrace();
			throw new OtherSQLException(e);
		}
	}

	/**
	 * Drops all tables and sequencers of the schema. Warning, all Data stored in the tables will be lost.
	 *
	 * @throws PersistenceException
	 *             will thrown if a persistence error occurs
	 * @throws IOException
	 *             will thrown if a file is not accessable
	 */
	public void dropWholeSchema() throws PersistenceException, IOException {
		try (final Statement statement = this.databaseManager.getConnection().createStatement()) {
			final String tableSchemaString =
					this.readFile(SCRIPT_ROOT_DIR + SCRIPT_NAME_DROP_SCHEMA, Charset.defaultCharset());

			for (String part : tableSchemaString.split(";")) {
				try {
					if (part.startsWith("\n")) {
						part = part.substring(1);
					}
					if (!part.isEmpty()) {
						statement.execute(part);
					}
				} catch (final SQLException e) {
					if (e.getErrorCode() != ORACLE_EXCEPTION_SEQUENCER_DOES_NOT_EXIST
							&& e.getErrorCode() != ORACLE_EXCEPTION_TABLE_OR_VIEW_DOES_NOT_EXIST
							&& e.getErrorCode() != ORACLE_EXCEPTION_OBJECT_DOES_NOT_EXIST) {
						throw e;
					}
				}
			}

			// databaseManager.getConnection().commit();
		} catch (final SQLException e) {
			e.printStackTrace();
			throw new OtherSQLException(e);
		}
	}

	/**
	 * Creates all procedures to access the schema tables.
	 *
	 * @throws PersistenceException
	 *             will thrown if a persistence error occurs
	 * @throws IOException
	 *             will thrown if a file is not accessable
	 */
	public void createProcedures() throws IOException, PersistenceException {
		try (final CallableStatement createProceduresCall =
				this.databaseManager.getConnection().prepareCall(
						this.readFile(SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_SCHEMA_EXCEPTION, Charset.defaultCharset()))) {
			createProceduresCall.execute();
		} catch (final SQLException e) {
			throw new OtherSQLException(e);
		}
		try (final CallableStatement createProceduresCall =
				this.databaseManager.getConnection().prepareCall(
						this.readFile(
								SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_PROCEDURES_CLASS_FACADE,
								Charset.defaultCharset()))) {
			createProceduresCall.execute();
		} catch (final SQLException e) {
			throw new OtherSQLException(e);
		}
		try (final CallableStatement createProceduresCall =
				this.databaseManager.getConnection().prepareCall(
						this.readFile(
								SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_PROCEDURES_CLASS_FACADE_BODY,
								Charset.defaultCharset()))) {
			createProceduresCall.execute();
		} catch (final SQLException e) {
			throw new OtherSQLException(e);
		}
		try (final CallableStatement createProceduresCall =
				this.databaseManager.getConnection().prepareCall(
						this.readFile(
								SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_PROCEDURES_OBJECT_FACADE,
								Charset.defaultCharset()))) {
			createProceduresCall.execute();
		} catch (final SQLException e) {
			throw new OtherSQLException(e);
		}
		try (final CallableStatement createProceduresCall =
				this.databaseManager.getConnection().prepareCall(
						this.readFile(
								SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_PROCEDURES_OBJECT_FACADE_BODY,
								Charset.defaultCharset()))) {
			createProceduresCall.execute();
		} catch (final SQLException e) {
			throw new OtherSQLException(e);
		}
		try (final CallableStatement createProceduresCall =
				this.databaseManager.getConnection().prepareCall(
						this.readFile(
								SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_PROCEDURES_ACCOUNT_FACADE,
								Charset.defaultCharset()))) {
			createProceduresCall.execute();
		} catch (final SQLException e) {
			throw new OtherSQLException(e);
		}
		try (final CallableStatement createProceduresCall =
				this.databaseManager.getConnection().prepareCall(
						this.readFile(
								SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_PROCEDURES_ACCOUNT_FACADE_BODY,
								Charset.defaultCharset()))) {
			createProceduresCall.execute();
		} catch (final SQLException e) {
			throw new OtherSQLException(e);
		}
	}

}