# DBHelper.java

    /*
    ** Module   : DBHelper.java
    ** Abstract : A helper to access the conversion database.
    **
    ** Copyright (c) 2020-2023, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- ---------------------------------------Description----------------------------------------
    ** 001 CA  20200412 Created initial version, for incremental conversion support.
    ** 002 CA  20200423 After the collection is persisted, clear it, to help garbage collection.  Also,
    **                  records in a collections are inserted 2000 at a time.
    ** 003 ECF 20200906 New ORM implementation.
    ** 004 CA  20220402 Moved the 'cvtdb' to the 'cvtpath' folder (defaults to 'cvt' relative to project dir).
    ** 005 GBB 20230512 Logging methods replaced by CentralLogger/ConversionStatus.
    */
    /*
    ** This program is free software: you can redistribute it and/or modify
    ** it under the terms of the GNU Affero General Public License as
    ** published by the Free Software Foundation, either version 3 of the
    ** License, or (at your option) any later version.
    **
    ** This program is distributed in the hope that it will be useful,
    ** but WITHOUT ANY WARRANTY; without even the implied warranty of
    ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    ** GNU Affero General Public License for more details.
    **
    ** You may find a copy of the GNU Affero GPL version 3 at the following
    ** location: https://www.gnu.org/licenses/agpl-3.0.en.html
    **
    ** Additional terms under GNU Affero GPL version 3 section 7:
    **
    **   Under Section 7 of the GNU Affero GPL version 3, the following additional
    **   terms apply to the works covered under the License.  These additional terms
    **   are non-permissive additional terms allowed under Section 7 of the GNU
    **   Affero GPL version 3 and may not be removed by you.
    **
    **   0. Attribution Requirement.
    **
    **     You must preserve all legal notices or author attributions in the covered
    **     work or Appropriate Legal Notices displayed by works containing the covered
    **     work.  You may not remove from the covered work any author or developer
    **     credit already included within the covered work.
    **
    **   1. No License To Use Trademarks.
    **
    **     This license does not grant any license or rights to use the trademarks
    **     Golden Code, FWD, any Golden Code or FWD logo, or any other trademarks
    **     of Golden Code Development Corporation. You are not authorized to use the
    **     name Golden Code, FWD, or the names of any author or contributor, for
    **     publicity purposes without written authorization.
    **
    **   2. No Misrepresentation of Affiliation.
    **
    **     You may not represent yourself as Golden Code Development Corporation or FWD.
    **
    **     You may not represent yourself for publicity purposes as associated with
    **     Golden Code Development Corporation, FWD, or any author or contributor to
    **     the covered work, without written authorization.
    **
    **   3. No Misrepresentation of Source or Origin.
    **
    **     You may not represent the covered work as solely your work.  All modified
    **     versions of the covered work must be marked in a reasonable way to make it
    **     clear that the modified work is not originating from Golden Code Development
    **     Corporation or FWD.  All modified versions must contain the notices of
    **     attribution required in this license.
    */

    package com.goldencode.p2j.convert.db;

    import java.io.*;
    import java.sql.*;
    import java.util.*;
    import java.util.function.*;
    import java.util.logging.*;

    import com.goldencode.ast.*;
    import com.goldencode.p2j.cfg.*;
    import com.goldencode.p2j.convert.*;
    import com.goldencode.p2j.uast.*;

    /**
     * Provides APIs to access the conversion database.
     */
    class DBHelper
    {
       /** Logger */
       private static final ConversionStatus LOG = ConversionStatus.get(DBHelper.class);

       /** Default database user. */
       private static String dbUser = "admin";

       /** Default database password. */
       private static String dbPass = "admin";

       /** Flag indicating the database is connected. */
       private boolean connected = false;

       /** Database connection */
       private Connection connection = null;

       /** Database URL */
       private String dbURL = null;

       /** A cache of prepared statements. */
       private Map<String, PreparedStatement> statements = new HashMap<>();

       /** Cache of db-backed collections. */
       private Map<String, StoredCollection> stores = new HashMap<>();

       /** Flag indicating if we are persisting the collections. */
       private boolean inPersist = false;

       /**
        * Get the state of the {@link #inPersist} flag.
        *
        * @return   See above.
        */
       public boolean inPersist()
       {
          return inPersist;
       }

       /**
        * Check if the database is connected.
        *
        * @return   The {@link #connected} state.
        */
       public boolean connected()
       {
          return connected;
       }

       /**
        * Disconnect the database.
        * <p>
        * This will persist all db-backed collection.
        */
       public void disconnect()
       {
          if (connection == null)
          {
             return;
          }

          try
          {
             this.inPersist = true;
             try
             {
                connection.setAutoCommit(false);
                for (StoredCollection map : stores.values())
                {
                   map.persist();
                   map.clear();
                }
             }
             finally
             {
                connection.commit();
                connection.setAutoCommit(true);
             }
          }
          catch (SQLException e)
          {
             throw new RuntimeException(e);
          }
          finally
          {
             this.inPersist = false;
          }

          stores.clear();

          for (Statement stmt : statements.values())
          {
             try
             {
                stmt.close();
             }
             catch (SQLException e)
             {
                throw new RuntimeException(e);
             }
          }
          statements.clear();

          try
          {
             connection.close();
          }
          catch (Exception exc)
          {
             throw new RuntimeException("Error closing database connection", exc);
          }

          connected = false;
          connection = null;
       }

       /**
        * Connect to the database.
        */
       public void connect()
       {
          if (connection != null)
          {
             disconnect();
          }

          try
          {
             if (dbURL == null)
             {
                String cvtFolder = Configuration.getConversionFolder();
                String dbFolder = cvtFolder + File.separator + "cvtdb";
                String db = dbFolder + File.separator + "cvtdb";

                long maxMemory = Runtime.getRuntime().maxMemory();
                long cacheSize = -1;
                if (maxMemory < Long.MAX_VALUE)
                {
                   cacheSize = maxMemory / 1024 / 2;
                }

                dbURL = "jdbc:h2:"
                      + db + ";"
                      + "DB_CLOSE_DELAY=-1;"
                      + "AUTOCOMMIT=OFF;"
                      + "LOCK_MODE=3;"
                      + "MV_STORE=FALSE;"
                      + "DB_CLOSE_ON_EXIT=TRUE;";

                if (cacheSize > -1)
                {
                   dbURL += (";CACHE_SIZE=" + cacheSize);
                }

                // load driver
                Class.forName("org.h2.Driver");
             }

             connection = DriverManager.getConnection(dbURL, dbUser, dbPass);
             connection.setAutoCommit(true);
             connected = true;
          }
          catch (Exception exc)
          {
             LOG.log(Level.SEVERE, "", exc);

             throw new RuntimeException("Error opening database connection", exc);
          }
       }

       /**
        * Convert the specified type to SQL.
        *
        * @param    clazz
        *           The Java type.
        *
        * @return   The SQL type.
        */
       public String convertToSql(Class<?> clazz)
       {
          if (clazz == String.class)
          {
             return "VARCHAR";
          }
          else if (clazz == Long.class)
          {
             return "BIGINT";
          }
          else if (clazz == Integer.class)
          {
             return "INT";
          }
          else if (clazz == Double.class)
          {
             return "DOUBLE";
          }
          else if (clazz == AstKey.class)
          {
             return "OTHER";
          }
          else if (clazz == Aast.class)
          {
             return "OTHER";
          }
          else if (clazz == FrameAstKey.class)
          {
             return "OTHER";
          }

          throw new IllegalArgumentException("Could not map class " + clazz + " to a Java type!");
       }

       /**
        * Execute an update SQL.
        *
        * @param    sql
        *           The SQL update statement.
        * @param    args
        *           The SQL arguments.
        *
        * @return   The number of affected records.
        */
       public int executeUpdate(String sql, Object... args)
       {
          int res = 0;
          try
          {
             PreparedStatement stmt = createStatement(sql);
             for (int i = 0; i < args.length; i++)
             {
                stmt.setObject(i + 1, args[i]);
             }
             res = stmt.executeUpdate();
             stmt.clearParameters();

             return res;
          }
          catch (SQLException e)
          {
             throw new RuntimeException(e);
          }
       }

       /**
        * Execute a generic SQL.
        *
        * @param    sql
        *           The SQL statement.
        * @param    args
        *           The SQL arguments.
        */
       public void executeSQL(String sql, Object... args)
       {
          try
          {
             PreparedStatement stmt = createStatement(sql);
             for (int i = 0; i < args.length; i++)
             {
                stmt.setObject(i + 1, args[i]);
             }
             stmt.execute();
             stmt.clearParameters();
          }
          catch (SQLException e)
          {
             throw new RuntimeException(e);
          }
       }

       /**
        * Execute an SQL which selects a single integer result.
        *
        * @param    sql
        *           The SQL statement.
        * @param    args
        *           The SQL arguments.
        *
        * @return   The count result.
        */
       public int executeCount(String sql, Object... args)
       {
          try
          {
             PreparedStatement stmt = createStatement(sql);
             for (int i = 0; i < args.length; i++)
             {
                stmt.setObject(i + 1, args[i]);
             }
             ResultSet rs = stmt.executeQuery();
             int count = rs.next() ? rs.getInt(1) : 0;
             stmt.clearParameters();

             return count;
          }
          catch (SQLException e)
          {
             throw new RuntimeException(e);
          }
       }

       /**
        * Execute a SQL query statement.
        *
        * @param    sql
        *           The SQL statement.
        * @param    args
        *           The SQL arguments.
        *
        * @return   The SQL result.
        */
       public List<Object[]> executeQuery(String sql, Object... args)
       {
          try
          {
             PreparedStatement stmt = createStatement(sql);
             for (int i = 0; i < args.length; i++)
             {
                stmt.setObject(i + 1, args[i]);
             }
             ResultSet rs = stmt.executeQuery();

             List<Object[]> res = new ArrayList<>();
             int colCount = rs.getMetaData().getColumnCount();
             while (rs.next())
             {
                Object[] row = new Object[colCount];

                for (int i = 1; i <= colCount; i++)
                {
                   row[i - 1] = rs.getObject(i);
                }

                res.add(row);
             }

             stmt.clearParameters();

             return res;
          }
          catch (SQLException e)
          {
             throw new RuntimeException(e);
          }
       }

       /**
        * Execute a SQL query statement, which will use the specified consumer to process the results.
        *
        * @param    sql
        *           The SQL statement.
        * @param    c
        *           The consumer to process the result set.
        * @param    args
        *           The SQL arguments.
        */
       public void executeQuery(String sql, Consumer<ResultSet> c, Object... args)
       {
          try
          {
             PreparedStatement stmt = createStatement(sql);
             for (int i = 0; i < args.length; i++)
             {
                stmt.setObject(i + 1, args[i]);
             }
             ResultSet rs = stmt.executeQuery();

             while (rs.next())
             {
                c.accept(rs);
             }

             stmt.clearParameters();
          }
          catch (SQLException e)
          {
             throw new RuntimeException(e);
          }
       }

       /**
        * Get the stored collection with the specified name.
        *
        * @param    name
        *           The stored collection name.
        *
        * @return   The found collection, or <code>null</code> if it was not yet added to
        *           {@link #stores}.
        */
       public StoredCollection getCollection(String name)
       {
          return this.stores.get(name);
       }

       /**
        * Register a db-backed collection with the given name.
        *
        * @param    name
        *           The collection name.
        * @param    c
        *           The collection.
        *
        * @throws   IllegalStateException
        *           If there is already a collection with this name in {@link #stores}.
        */
       public void register(String name, StoredCollection c)
       {
          if (this.stores.containsKey(name))
          {
             throw new IllegalStateException("Only one collection must be active for " + name + "!");
          }

          this.stores.put(name, c);
       }

       /**
        * Persist this collection, using the specified SQL and consumer to set the query batch
        * arguments.
        *
        * @param    c
        *           The collection to persist.
        * @param    sql
        *           The insert SQL.
        * @param    ps
        *           The consumer to add the records as a batch insert.
        */
       public void persistCollection(Collection c,
                                     String sql,
                                     BiConsumer<PreparedStatement, Object> ps)
       {
          try
          {
             PreparedStatement stmt = createStatement(sql);

             int batchRecords = 2000;
             int idx = 0;
             for (Object o : c)
             {
                idx = idx + 1;

                ps.accept(stmt, o);

                if (idx % batchRecords == 0)
                {
                   stmt.executeBatch();
                }
             }
             stmt.executeBatch();
             stmt.clearParameters();
          }
          catch (SQLException e)
          {
             throw new RuntimeException(e);
          }
       }

       /**
        * Prepare the SQL statement and cache it.
        *
        * @param    sql
        *           The SQL statement to prepare.
        *
        * @return   The prepared statement (a new instance or an instance from the {@link #statements}
        *           cache).
        *
        * @throws   SQLException
        *           If the SQL statement can't be created.
        */
       private PreparedStatement createStatement(String sql)
       throws SQLException
       {
          PreparedStatement stmt = statements.get(sql);
          if (stmt == null)
          {
             stmt = connection.prepareStatement(sql);
             statements.put(sql, stmt);
          }

          return stmt;
       }
    }