////////////////////////////////////////////////////////////////////////////////
   // MillScript: an Open Spice interpreter and batch website creation tool
   // Copyright (C) 2001-2004 Open World Ltd
   // Copyright (C) 2005 Kevin Rogers
   //
   // This file is part of MillScript.
   //
   // MillScript is free software; you can redistribute it and/or modify it under
   // the terms of the GNU General Public License as published by the Free
  // Software Foundation; either version 2 of the License, or (at your option)
  // any later version.
  //
  // MillScript 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 General Public License for
  // more details.
  //
  // You should have received a copy of the GNU General Public License along with
  // MillScript; if not, write to the Free Software Foundation, Inc., 59 Temple
  // Place, Suite 330, Boston, MA  02111-1307  USA
  ////////////////////////////////////////////////////////////////////////////////
  package org.millscript.millscript.functions;
  
  import org.millscript.commons.util.list.ELinkedList;
  import org.millscript.millscript.alert.Alerts;
  import org.millscript.millscript.datatypes.DatabaseRecord;
  import org.millscript.millscript.datatypes.DatabaseSource;
  import org.millscript.millscript.vm.Machine;
  
  import java.sql.Connection;
  import java.sql.Date;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.ResultSetMetaData;
  import java.sql.SQLException;
  import java.util.GregorianCalendar;
  
  /**
   * This class implements a millscript function to execute an SQL query through
   * the JDBC interface. The query is done using a prepared statement, which
   * should improve efficiency for multiple queries. The generated function
   * requires at least one argument, which will be the DataSource for the query.
   * Any subsequent arguments are treated as parameters for the prepared
   * statement.
   * <pre>
   * f( datasource, arg1, arg2, arg3, ... )
   * </pre>
   * The function returns a list-of-maps.
   *
   * @since 9.6.4
   */
  public class JdbcExecuteQueryFunction extends Function {
  
      /**
       * The SQL statement this function will prepare and execute.
       */
      private final String sql;
  
      /**
       * The Connection, cached when the function is first appplied, or the
       * DataSource changes.
       */
      private Connection cachedConn;
  
      /**
       * The PreparedStatement, cached when the function is first applied, or the
       * DataSource changes.
       */
      private PreparedStatement cachedPrep;
  
      /**
       * Construct a new function which will execute the specifed SQL as a
       * prepared statement.
       *
       * @param s the string containing SQL to prepare and execute
       */
      public JdbcExecuteQueryFunction( final String s ) {
          this.sql = s;
      }
  
      /**
       * @see org.millscript.millscript.functions.Function#apply(org.millscript.millscript.vm.Machine, int)
       */
      @Override
      public void apply( final Machine mc, final int nargs ) {
  
          // Check that we received at least one argument
          this.checkNargsGT( mc, 1, nargs );
  
          // Get any prepared statement arguments
          final Object[] prepArgs = mc.popArgsArray( nargs - 1 );
  
          // Pop the DataSource we will use for executing the prepared statement
          final DatabaseSource source = mc.popDatabaseSource();
  
          try {
  
              // Obtain a connection from the supplied DataSource
              Connection conn = source.getConnection();
 
             // First check if the supplied DataSource is the same as the one we
             // have cached from previous applications of this function.
             if ( conn != cachedConn ) {
                 // Close the existing prepared statement and connection.
                 if ( cachedPrep != null ) {
                     cachedPrep.close();
                 }
 
                 // Using the new connection, prepare the SQL statement
                 cachedPrep = conn.prepareStatement( sql );
 
                 // Now we're fully initialised, store the supplied DataSource.
                 cachedConn = conn;
             }
 
             // Initialise the parameters for the prepared statement by iterating
             // through each one and setting it.
             for ( int i = 0; i < prepArgs.length; i++ ) {
                 this.cachedPrep.setObject( i + 1, prepArgs[ i ] );
             }
 
             ResultSet rs = this.cachedPrep.executeQuery();
 
             // Push the results from the execution of the statement.
             mc.pushObject( changeResultSet( rs ) );
 
             // Close the result set to release resources
             rs.close();
 
             // Clear any parameters from the prepared statement.
             this.cachedPrep.clearParameters();
 
         } catch ( SQLException ex ) {
             // We would want to report the SQL state and the PostgreSQL vendor
             // error code as commented out below, except they don't provide it.
             // culprit( "SQLState", ex.getSQLState() ),
             // culprit( "PostgreSQL error code", String.valueOf( ex.getErrorCode() ) ),
             throw(
                 Alerts.eval(
                     "SQL query failure",
                     ex.getMessage()
                 ).culprit( "SQL", sql ).mishap()
             );
         }
 
     }
 
     /**
      * Changes the supplied ResultSet into a LinkedList of database records.
      *
      * @param   rs  the result set containing the results to change
      * @return  a LinkedList of database records
      */
     private ELinkedList changeResultSet( final ResultSet rs ) throws SQLException {
 
         // The meta data describing the columns in the result set
         final ResultSetMetaData meta = rs.getMetaData();
 
         // The list of database records we will return
         final ELinkedList< DatabaseRecord > results = new ELinkedList< DatabaseRecord >();
 
         // The number of columns in a row
         final int columnCount = meta.getColumnCount();
 
         // Iterate through each row in the result set
         while ( rs.next() ) {
 
             // A DatabaseRecord represents a single row in the results of the
             // SQL query.
             final DatabaseRecord record = new DatabaseRecord();
 
             // We must unlock the record so that we can insert keys that do not
             // start with "!"
             record.unlock();
 
             // Loop through each column in the row
             for ( int index = 1; index <= columnCount; index++ ) {
 
                 // Get the column name
                 final String columnName = meta.getColumnName( index );
 
                 // Perform any special processing depending on the type of data
                 // held in the column.
                 switch ( meta.getColumnType( index ) ) {
 
                     case java.sql.Types.DATE:
 
                         final GregorianCalendar cal = new GregorianCalendar();
                         Date date = rs.getDate( index, cal );
 
                         if ( date != null ) {
                             cal.setTime( date );
                         }
 
                         record.insert( columnName, cal );
                         break;
 
                     default:
                         record.insert( columnName, rs.getObject( index ) );
 
                 }
 
             }
 
             // We must lock the record so that the original data from the
             // database is protected.
             record.lock();
 
             // Add the record to the list of database records to return.
             results.addLast( record );
 
         }
 
         int size = results.size();
 
         System.out.println( "Received " + size + " record" + ( size == 1 ? "" : "s" ) );
 
         // Return the list of database records
         return results;
     }
 
     /**
      * Returns a string representation of this function. It shows that it
      * requires one or more arguments.
      *
      * @return    a string representation of this function
      */
     @Override
     public String toString() {
         String name = getName();
         return (
             name == null ?
             "<function (1+)>" :
             "<function " + name + "(1+)>"
         );
     }
 
 }