Meta data

package com.thomasjwilde.WildeDB.Database;

import com.sun.istack.internal.NotNull;
import com.sun.istack.internal.Nullable;
import com.thomasjwilde.WildeDB.WildeBeans.WildeApplication;
import com.thomasjwilde.WildeDB.WildeBeans.WildeBean;
import com.thomasjwilde.WildeDB.WildeBeans.WildeBeanProperty;
import com.thomasjwilde.WildeDB.WildeBeans.WildeBeanUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.*;

public class Database {

    private static Database database;
    private Connection connection;
    public enum DatabaseType{
        SQLITE, MSSQL, OSQL, MYSQL
    }

    public static void main(String[] args) {
        Database.init(DatabaseType.SQLITE, "jdbc:sqlite:Test.db");
        Database database = Database.getInstance();
        //Retrieving the meta data object
//        database.initApplicationBeans();

        WildeApplication.getInstance().printAllBeanProperties();

    }

    /**
     * Method is called prior to any database methods called
     * @param databaseType Enum for database type for the connection (SQLITE, MSSQL, OSQL, MYSQL)
     * @param connectionString Connection string currently is only appropriate for SQLITE
     * @see DatabaseType
     */
    public static void init(DatabaseType databaseType, String connectionString){
        switch (databaseType) {
            case SQLITE:
                try {
                    // Init Connection
                    getInstance().connection = DriverManager.getConnection(connectionString);

                    // Get database table meta data
                    getInstance().initApplicationBeans();

                    // Close database connection on app close
                    Runtime current = Runtime.getRuntime();
                    current.addShutdownHook(new Thread(){
                        @Override
                        public void run() {
                            super.run();
                            getInstance().onApplicationClose();
                        }
                    });
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                break;
        }
    }

    private Database(){
//        if(connection == null){
//            throw new NullPointerException("init must be called prior to getInstance()");
//        }
    }

    public static Database getInstance(){
        if(database == null){
            database = new Database();
        }
        return database;
    }

    /**
     * This method creates wildebeans for all of the tables in the database, wildeproperties for each of those
     * wildebeans, and all subbeans for foreign key references, and properties for those subbeans.  All of these
     * beans are passed to the WildeApplication singleton and can then be used as templates when actual objects are
     * created from those beans.  These templates show the been relationship and allow for automatic join statements in
     * quereies.
     */
    private void initApplicationBeans(){

        ResultSet databaseMetaRs = null;
        ResultSet databaseHelperRs = null;
        ResultSet rsPrimaryKey = null;
        ResultSet rsForeignKey = null;
        ResultSet rsTableColumns = null;

        try {
            DatabaseMetaData metaData = database.connection.getMetaData();

            String[] types = {"TABLE"};
            //Retrieving the columns in the database
            databaseMetaRs = metaData.getTables(null, null, "t_%", types);
            int iter = 1;
            while (databaseMetaRs.next()) {

                String tableName = databaseMetaRs.getString("TABLE_NAME");
                iter++;

                // Create a unique wildebean
                WildeBean wildeBean = new WildeBean();
                wildeBean.setSqlTableName(tableName);

                System.out.println("New Table: " + tableName);
                // Set the primary key
                rsPrimaryKey = metaData.getPrimaryKeys(null, null, tableName);
                // PrimaryKey resultSet will be closed if there is no primary key
                // TODO Will need to be able to have multiple primary keys
                if(!rsPrimaryKey.isClosed())
                    wildeBean.setPrimaryKeyColumn(rsPrimaryKey.getString("COLUMN_NAME"));

                // Set the foreign keys
                // Need to look into what would happen with composite foreign keys
                // This is, a foreign key that has multiple columns which refer to compisite primary key of another table
                // Reference https://sqlite.org/foreignkeys.html#fk_composite
                rsForeignKey = metaData.getImportedKeys(null, null, tableName);
                ResultSetMetaData rsmd = rsForeignKey.getMetaData();

                while (rsForeignKey.next()) {

                    ForeignKey foreignKey = new ForeignKey();
                    foreignKey.setColumnName(rsForeignKey.getString("FKCOLUMN_NAME"));
                    foreignKey.setReferencedTable(rsForeignKey.getString("PKTABLE_NAME"));
                    foreignKey.setReferencedTablePK(rsForeignKey.getString("PKCOLUMN_NAME"));

                    if (!wildeBean.getForeignKeys().contains(foreignKey)) {
                        wildeBean.getForeignKeys().add(foreignKey);
                    }

                    // Following commented code prints all the column names and values from the
                        /*for (int i = 1; i <= rsmd.getColumnCount() ; i++) {
                            System.out.println("ImportKey column: " + rsmd.getColumnName(i) + "; value: " + rsForeignKey.getObject(rsmd.getColumnName(i)));
                        }
                        System.out.println();*/
                }

                // Add the rest of the columns
                // Get the result set for the columns
                rsTableColumns = metaData.getColumns(null, null, tableName, null);
                // ResultSetMetaData can be used to print all columns and values
                ResultSetMetaData columnMd = rsTableColumns.getMetaData();

                // Create the WildeBeanProperties for the WildeBean
                createWildeBeanPropertiesForTableColumnRs(rsTableColumns, wildeBean);

                // Add the bean to the application beans
                WildeApplication.getInstance().getUniqueDatabaseBeans().add(wildeBean);

            }

            // get the helper table info to populate into bean
            databaseHelperRs = metaData.getTables(null, null, "h_%", types);
            while (databaseHelperRs.next()) {
                String tableName = databaseHelperRs.getString("TABLE_NAME");
                switch (tableName) {
                    case WildeApplication.TABLE_SUPPORT:
                        // Get the table support columns and add them to the Application Wilde Beans

                        // Create a unique wildebean
                        WildeBean wildeBean = new WildeBean();
                        wildeBean.setSqlTableName(tableName);

                        System.out.println("New Table: " + tableName);
                        // Set the primary key
                        rsPrimaryKey = metaData.getPrimaryKeys(null, null, tableName);
                        // PrimaryKey resultSet will be closed if there is no primary key
                        if(!rsPrimaryKey.isClosed())
                            wildeBean.setPrimaryKeyColumn(rsPrimaryKey.getString("COLUMN_NAME"));
                        // There are no foreign key columns for the table support

                        // Create the WildeBeanProperties for the WildeBean
                        rsTableColumns = metaData.getColumns(null, null, tableName, null);
                        ResultSetMetaData columnMd = rsTableColumns.getMetaData();

                        // Create the WildeBeanProperties for the WildeBean
                        createWildeBeanPropertiesForTableColumnRs(rsTableColumns, wildeBean);

                        // Add the bean to the application beans
                        WildeApplication.getInstance().getUniqueDatabaseBeans().add(wildeBean);

                        break;
                }

            }

            // Create sub beans for foreign key values
            for (WildeBean wildeBean : WildeApplication.getInstance().getUniqueDatabaseBeans()) {
                setSubBeans(wildeBean);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(databaseMetaRs);
            close(rsPrimaryKey);
            close(rsForeignKey);
            close(rsTableColumns);
        }
    }

    /**
     * This is a recursive method called within initApplicationBeans() to set all sub-beans.  The sub beans are set
     * to the property of the parent bean that owns them, and given a reference to the parent bean.  Parent beans have
     * a method to retrieve their children beans.
     * @param wildeBean WildeBean to check for subbeans.
     */
    private void setSubBeans(WildeBean wildeBean) {

        foreignkeyloop:
        for (ForeignKey foreignKey : wildeBean.getForeignKeys()) {
            WildeBeanProperty wildeBeanProperty = wildeBean.getWildeBeanProperty(foreignKey.getColumnName());
            WildeBean subBean = WildeApplication.getInstance().getWildeBean(foreignKey.getReferencedTable()).newInstance();

            // Go ahead and check the bean name to make sure there is not a circular reference which would cause a stackoverflow
            String subBeanName = WildeBeanUtils.createBeanName(wildeBeanProperty);
            if (subBeanName.contains("$")) {
                // check to make sure there's not a circular reference to the latest bnea
                String[] individualBeanNames = subBeanName.split("\\$");

                for (int i = 0; i < individualBeanNames.length-1; i++) {
                    /*System.out.println("Checking bean name " + individualBeanNames[individualBeanNames.length-1] + " for to ensure it's not equal to the latest bean name " + individualBeanNames[i]);*/
                    if(Objects.equals(individualBeanNames[individualBeanNames.length-1], individualBeanNames[i])){
                        System.out.println("ERROR -- LOOP DETECTED, NOT CREATING SUB BEAN");
                        continue foreignkeyloop;
                    }
                }
            }
            System.out.println("creating sub bean name: " + subBeanName);
            wildeBeanProperty.setSubWildeBean(subBean);
            setSubBeans(subBean);
        }
    }


    /**
     * Method used to print column names of a table
     * @param rsmd ResultSetMetaData collected from rs.getMetaData()
     * @throws SQLException
     */
    private void printAllColumnNames(ResultSetMetaData rsmd) throws SQLException{
        List<String> columns = new ArrayList<>();
        for (int i = 0; i < rsmd.getColumnCount(); i++) {
            columns.add(rsmd.getColumnName(i));
        }
        System.out.println(StringUtils.join(columns, ", "));
    }

    /**
     * This method takes the resultSet from metaData.getColumns for a particular table
     * and populates a wildeBean with WildeBeanProperties for each of the columns
     * @param rsTableColumns ResultSet taken from metaData.getColumns(null, null, tableName, null);
     * @param wildeBean WildeBean whose WildeBeanProperties are going to be populated
     * @throws SQLException
     */
    private void createWildeBeanPropertiesForTableColumnRs(ResultSet rsTableColumns, WildeBean wildeBean) throws SQLException{
        while (rsTableColumns.next()) {
            /*printAllColumnNamesAndValues(rsTableColumns, columnMd);*/

            WildeBeanProperty wildeBeanProperty = new WildeBeanProperty(wildeBean);
            String tableName = wildeBean.getSqlTableName();

            wildeBeanProperty.setSqlTableName(tableName);
            wildeBeanProperty.setSqlColumnName(rsTableColumns.getString("COLUMN_NAME"));
            wildeBeanProperty.setSqlDataType(rsTableColumns.getString("TYPE_NAME"));
            wildeBeanProperty.setColumnLimit(rsTableColumns.getInt("COLUMN_SIZE"));
            wildeBeanProperty.setNullable(Objects.equals(rsTableColumns.getString("IS_NULLABLE"), "YES"));
            wildeBeanProperty.setAutoIncrement(Objects.equals(rsTableColumns.getString("IS_AUTOINCREMENT"), "YES"));
            wildeBeanProperty.setGeneratedColumn(Objects.equals(rsTableColumns.getString("IS_GENERATEDCOLUMN"), "YES"));
            WildeBeanUtils.createDisplayNameFromSqlColumn(wildeBeanProperty);
            wildeBeanProperty.setPrimaryKey(Objects.equals(wildeBean.getPrimaryKeyColumn(), wildeBeanProperty.getSqlColumnName()));
            if (wildeBean.getForeignKeys().isEmpty()) {
                wildeBeanProperty.setForeignKey(false);
            } else {
                // See if there is any match to the column name
                wildeBeanProperty.setForeignKey(
                        wildeBean.getForeignKeys().stream()
                                .anyMatch(foreignKey -> Objects.equals(foreignKey.getColumnName(), wildeBeanProperty.getSqlColumnName())));
            }

            wildeBean.getBeanProperties().add(wildeBeanProperty);
        }
    }

    /**
     * Method used for printing all database table column and values for a particular ResultSet row
     * Should be called within a while(rs.next()) loop
     * @param rs The ResultSet or row whose column and values are being printed
     * @param resultSetMetaData The ResultSetMetaData which should be collected prior to the while loop by rs.getMetaData()
     */
    private void printAllColumnNamesAndValues(ResultSet rs, ResultSetMetaData resultSetMetaData) {
        try {
            for (int i = 1; i <= resultSetMetaData.getColumnCount() ; i++) {
                System.out.println("Table column: " + resultSetMetaData.getColumnName(i) + "; value: " + rs.getObject(resultSetMetaData.getColumnName(i)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * Standard query call for a table and an
     * @param tableName The name of the table to prefer the query
     * @param where A where clause similar to Sql Developer. Should not contain the word "Where".
     * @return
     */
    public ArrayList<WildeBean> query(@NotNull String tableName, @Nullable String where){
        return DBUtil.query(connection, tableName, where);
    }

    public void close(PreparedStatement preparedStatement) {
        if(preparedStatement != null){
            try {
                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public void close(ResultSet rs) {
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public void close(ResultSet rs, PreparedStatement preparedStatement) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(preparedStatement != null){
            try {
                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public void onApplicationClose(){
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}