# DatabaseService.java

    /*
    ** Module   : DatabaseService.java
    ** Abstract : Helper object for database access.
    **
    ** Copyright (c) 2017-2020, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- --------------------------------Description----------------------------------
    ** 001 ECF 20170428 Created first version.
    ** 002 ECF 20171011 Escape single backslash character in LIKE phrase r-value.
    ** 003 ECF 20180823 Added MVCC=FALSE to JDBC URL for upgrade to H2 1.4.197.
    ** 004 AIL 20200906 Dropped MVCC=FALSE and MULTI_THREADED=1 for upgrade to H2 1.4.200.
    */

    /*
    ** 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.report.server;

    import java.sql.*;
    import org.h2.jdbcx.*;
    import com.goldencode.p2j.cfg.*;

    /**
     * This class provides database services needed by the report API and maintains a connection
     * pool with the database.
     */
    class DatabaseService
    {
       /** Absolute path to the project's home directory */
       static final String p2jHomePath;

       /** Singleton instance of this class */
       private static final DatabaseService singleton;

       static
       {
          try
          {
             // determine P2J_HOME path
             String home = Configuration.home();
             char sep = Configuration.P2J_FILE_SEP;
             p2jHomePath = home + sep;

             singleton = new DatabaseService();
             singleton.initialize();

             // load database driver
             Class.forName("org.h2.Driver");
          }
          catch (ClassNotFoundException | ConfigurationException exc)
          {
             throw new RuntimeException(exc);
          }
       }

       /** Connection pool (using default size of 10 and timeout of 30 seconds) */
       private final JdbcConnectionPool pool;

       /**
        * Default constructor. Creates the connection pool and initialize the database for the
        * report API's use.
        */
       DatabaseService()
       {
          long maxMemory = Runtime.getRuntime().maxMemory();
          long cacheSize = -1;
          if (maxMemory < Long.MAX_VALUE)
          {
             cacheSize = maxMemory / 1024 / 2;
          }

          String url = "jdbc:h2:"
                     + p2jHomePath
                     + "rptdb/rptdb;"
                     + "DB_CLOSE_DELAY=-1;"
                     + "DB_CLOSE_ON_EXIT=FALSE;"
                     + "AUTOCOMMIT=OFF;"
                     + "DEFAULT_LOCK_TIMEOUT=10000;"
                     + "LOCK_MODE=3;"
                     + "MV_STORE=FALSE";
          if (cacheSize > -1)
          {
             url += (";CACHE_SIZE=" + cacheSize);
          }

          // TODO: use real user/pass
          pool = JdbcConnectionPool.create(url, "admin", "admin");
       }

       /**
        * Get the singleton instance of this class.
        *
        * @return  Database access services object.
        */
       static DatabaseService get()
       {
          return singleton;
       }

       /**
        * Check out a connection from the connection pool.
        *
        * @return  Shared JDBC connection.
        *
        * @throws  SQLException
        *          if there is an error retrieving, opening, or preparing the connection.
        */
       Connection openConnection()
       throws SQLException
       {
          Connection conn = pool.getConnection();

          try (Statement stmt = conn.createStatement())
          {
             stmt.execute("set autocommit off");
             conn.commit();
          }

          return conn;
       }

       /**
        * Escape all percent and underscore symbols in the text that will be used as the r-value
        * of a SQL LIKE expression, since these characters have special meaning to the LIKE
        * processing. Backslash ({@code \}) is used as the escape character. Also escape single
        * backslash characters.
        *
        * @param   text
        *          R-value text.
        *
        * @return  String with embedded escape sequences for percent and underscore characters.
        */
       String escapeLikeRValue(String text)
       {
          return text.replaceAll("\\\\", "\\\\\\\\").replaceAll("%", "\\%").replaceAll("_", "\\_");
       }

       /**
        * Initialize database services for all sessions to use. This involves creating a global
        * temporary table to represent the selection of files which are active for the sessions'
        * report result filtering purposes.
        */
       void initialize()
       {
          try (Connection conn = openConnection())
          {
             try (Statement stmt = conn.createStatement())
             {
                // create active_file temp table
                String sql =
                   "create global temporary table active_file (fid bigint, sid bigint, " +
                   "constraint fk_actfile_fid foreign key(fid) references file(id))";
                stmt.execute(sql);

                conn.commit();
             }
             catch (SQLException exc)
             {
                conn.rollback();

                throw new RuntimeException(exc);
             }
          }
          catch (SQLException exc)
          {
             throw new RuntimeException(exc);
          }
       }
    }