# P2JLoggingDatabaseHelper.java

    /*
    ** Module   : P2JLoggingDatabaseHelper.java
    ** Abstract : Helper object for p2j logging database access.
    **
    ** Copyright (c) 2024, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- --------------------------------Description----------------------------------
    ** 001 ICP 20240417 Created first version.
    */

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

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

    import com.goldencode.p2j.cfg.Configuration;
    import com.goldencode.p2j.util.logging.CentralLogger;

    public class P2JLoggingDatabaseHelper
    {
       /** Logger. */
       private static final CentralLogger LOG = CentralLogger.get(P2JLoggingDatabaseHelper.class);

       /** Map that includes a helper for each given database. */
       private static final Map<String, P2JLoggingDatabaseHelper> dbHelpers = new HashMap<>();

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

       /**
        * Constructor to instantiate a helper
        *
        * @param    filename
        *           The filename of the h2 database to be created.
        * @param    user
        *           The user of the h2 database to be created.
        * @param    pass
        *           The password of the h2 database to be created.
        */
       public P2JLoggingDatabaseHelper(String filename, String user, String pass)
       {
          Statement stmt = null;
          try
          {
             String home = Configuration.home();
             char sep = Configuration.P2J_FILE_SEP;
             String p2jHomePath = home + sep;
             String url = "jdbc:h2:"
                        + p2jHomePath
                        + filename + ";"
                        + "DB_CLOSE_DELAY=-1;"
                        + "AUTOCOMMIT=OFF;"
                        + "DEFAULT_LOCK_TIMEOUT=10000;"
                        + "LOCK_MODE=3;"
                        + "MV_STORE=FALSE";
             Class.forName("org.h2.Driver");
             openConnection(url, user, pass);
          }
          catch (Exception e)
          {
             LOG.warning("Couldn't initialize the P2J logging database:" + e.getMessage());
          }

          if (connection != null)
          {
             try
             {
                stmt = connection.createStatement();

                String create = "create table if not exists P2JQuery_profiling (" +
                                "id identity primary key, " +
                                "databaseName varchar, time_stamp timestamp, queryType varchar, fql varchar," +
                                "useCount bigint, processingTime double, location varchar, stackTrace varchar)";
                stmt.execute(create);

                connection.commit();
             }
             catch (Exception e)
             {
                try
                {
                   connection.rollback();
                }
                catch (SQLException  ex)
                {
                   LOG.warning("Couldn't roll back the transaction", ex);
                }

                LOG.warning("Couldn't initialize the P2J logging database:" + e.getMessage());
             }
             finally
             {
                if (stmt != null)
                {
                   try
                   {
                      stmt.close();
                   }
                   catch (SQLException exc)
                   {
                      LOG.warning("Couldn't close the statement", exc);
                   }
                }
             }
          }
       }

       /**
        * Get the logging database helper of a specific database.
        *
        * @param   databaseName
        *          The name of the database in use.
        * @param   fileName
        *          The filename of the H2 database.
        * @param   user
        *          The user of the H2 database.
        * @param   pass
        *          The password of the H2 database.
        *
        * @return  If it exists, an already created helper for that database, or a new one otherwise.
        */
       public static P2JLoggingDatabaseHelper getDbHelper(String databaseName,
                                                          String fileName,
                                                          String user,
                                                          String pass)
       {
          P2JLoggingDatabaseHelper dbHelper = dbHelpers.get(databaseName);
          if (dbHelper != null)
          {
             return dbHelper;
          }
          dbHelper = new P2JLoggingDatabaseHelper(fileName, user, pass);
          dbHelpers.put(databaseName, dbHelper);
          return dbHelper;
       }

       /**
        * Insert a P2j query profiling record.
        *
        * @param    databaseName
        *           The FWD database on which profiling is done.
        * @param    queryType
        *           The query type of the profiled P2j Query.
        * @param    fql
        *           The fql of the profiled P2J Query.
        * @param    useCount
        *           The number of times of the profiled P2J Query was used.
        * @param    processingTime
        *           The amount of time it took to process the query.
        * @param    location
        *           The location of the profiled P2J Query.
        * @param    stackTrace
        *           The stack trace of the profiled P2J Query.
        *
        * @return   Primary key of the created record.
        */
       public Long insertP2JQueryProfiling(String    databaseName,
                                           String    queryType,
                                           String    fql,
                                           Long      useCount,
                                           Double    processingTime,
                                           String    location,
                                           String    stackTrace)
       {
          Long id = null;
          try
          {
             String insert = "insert into P2JQuery_profiling (" +
                             "databaseName, time_stamp, queryType, fql," +
                             "useCount, processingTime, location, stackTrace) " +
                             "values(?, ?, ?, ?, ?, ?, ?, ?)";
             PreparedStatement stmtp2jQueryInsert = connection.prepareStatement(insert,
                                                                                Statement.RETURN_GENERATED_KEYS);
             stmtp2jQueryInsert.setString(1, databaseName);
             stmtp2jQueryInsert.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
             stmtp2jQueryInsert.setString(3, queryType);
             stmtp2jQueryInsert.setString(4, fql);
             stmtp2jQueryInsert.setLong(5, useCount);
             stmtp2jQueryInsert.setDouble(6, processingTime);
             stmtp2jQueryInsert.setString(7, location);
             stmtp2jQueryInsert.setString(8, stackTrace);
             stmtp2jQueryInsert.execute();
             stmtp2jQueryInsert.clearParameters();

             // read back the primary key that was autogenerated
             ResultSet rs = stmtp2jQueryInsert.getGeneratedKeys();
             if (rs.next())
             {
                id = rs.getLong(1);
             }
             connection.commit();
          }
          catch (Exception e)
          {
             if (connection != null)
             {
                try
                {
                   connection.rollback();
                }
                catch (SQLException ex)
                {
                   LOG.warning("Couldn't roll back the transaction", ex);
                }
             }
             LOG.warning("Couldn't insert P2j Query Profiling in database:"+ e.getMessage());
          }
          if (id == null)
          {
             LOG.warning("Couldn't insert P2j Query Profiling in database!");
          }
          return id;
       }

       /**
        * Open a new connection to the database (if one already opened, it will close it first).
        *
        * @param    url
        *           The connection URL of the database.
        * @param    user
        *           The database user configured in directory.xml
        * @param    pass
        *           The database password configured in directory.xml
        */
       private void openConnection(String url, String user, String pass)
       {
          if (connection != null)
          {
             closeConnection();
          }
          try
          {
             connection = DriverManager.getConnection(url, user, pass);
          }
          catch (SQLException e)
          {
             LOG.warning("Couldn't establish connection to logging database:"+ e.getMessage());
          }
       }

       /**
        * Close the current connection.
        */
       private void closeConnection()
       {
          if (connection == null)
          {
             return;
          }

          try
          {
             connection.close();
             connection = null;
          }
          catch (Exception e)
          {
             LOG.warning("Couldn't close the connection to P2J logging database:"+ e.getMessage());
          }
       }
    }