/*
    * The SmartWeb Framework
    * Copyright (C) 2004-2006
    *
    * This library is free software; you can redistribute it and/or
    * modify it under the terms of the GNU Lesser General Public
    * License as published by the Free Software Foundation; either
    * version 2.1 of the License, or (at your option) any later version.
    *
   * This library 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
   * Lesser General Public License for more details.
   *
   * You should have received a copy of the GNU Lesser General Public
   * License along with this library; if not, write to the Free Software
   * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
   *
   * For further informations on the SmartWeb Framework please visit
   *
   *                        http://smartweb.sourceforge.net
   */
   
  package net.smartlab.web.page;
  
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.ResultSetMetaData;
  import java.sql.SQLException;
  import java.sql.Statement;
  
  import javax.sql.DataSource;
  
  import org.apache.commons.logging.Log;
  import org.apache.commons.logging.LogFactory;
  
  /**
   * TODO documentation
   * 
   * @author rlogiacco
   */
  public class SQLPaginator extends Paginator {
  
  	/**
  	 * Logger for this class
  	 */
  	protected final Log logger = LogFactory.getLog(SQLPaginator.class);
  
  	/**
  	 * A constant indicating the default isoltaion level.
  	 */
  	private final static int DEFAULT_ISOLATION_LEVEL = -1;
  
  	/**
  	 * The SQL connection pool.
  	 */
  	private DataSource pool;
  
  	/**
  	 * The SQL querywhose result must be paginated.
  	 */
  	private String query;
  
  	/**
  	 * The selected isoltaion level on the database connection.
  	 */
  	private int isolation;
  
  	/**
  	 * Creates an SQLPaginator instance using an SQL connection pool and a
  	 * specified query. The page size is unlimited, the selected page is
  	 * undefined and the isolation level is the default one.
  	 * 
  	 * @param pool the SQL connection pool.
  	 * @param query the selection query for pagination results.
  	 * @throws SQLException if something unexpected occurs while accessing the
  	 *             database.
  	 */
  	public SQLPaginator(DataSource pool, String query) throws SQLException {
  		this(pool, query, 0);
  	}
  
  	/**
  	 * Creates an SQLPaginator with default isolation level and unlimited page
  	 * size.
  	 * 
  	 * @param pool the SQL connection pool.
  	 * @param query the selection query for pagination results.
  	 * @param size the number of elements composing each page.
  	 * @throws SQLException if something unexpected occurs while accessing the
  	 *             database.
  	 */
  	public SQLPaginator(DataSource pool, String query, int size) throws SQLException {
  		this(pool, query, size, Paginator.UNLIMITED_PAGES);
  	}
  
  	/**
  	 * Creates an SQLPaginator with default isoltaion level.
  	 * 
 	 * @param pool the SQL connection pool.
 	 * @param query the selection query for pagination results.
 	 * @param size the number of elements composing each page.
 	 * @param pages the number of pages composing each page block.
 	 * @throws SQLException if something unexpected occurs while accessing the
 	 *             database.
 	 */
 	public SQLPaginator(DataSource pool, String query, int size, int pages) throws SQLException {
 		this(pool, query, size, pages, DEFAULT_ISOLATION_LEVEL);
 	}
 
 	/**
 	 * Creates an SQLPaginator.
 	 * 
 	 * @param pool the SQL connection pool.
 	 * @param query the selection query for pagination results.
 	 * @param size the number of elements composing each page.
 	 * @param pages the number of pages composing each page block.
 	 * @param isolation the isolation level to be used while accessing the
 	 *            database.
 	 * @throws SQLException if something unexpected occurs while accessing the
 	 *             database.
 	 */
 	public SQLPaginator(DataSource pool, String query, int size, int pages, int isolation) throws SQLException {
 		super(size, pages);
 		this.pool = pool;
 		this.query = query;
 		this.isolation = isolation;
 		Connection connection = null;
 		try {
 			connection = pool.getConnection();
 			if (isolation != DEFAULT_ISOLATION_LEVEL) {
 				connection.setTransactionIsolation(isolation);
 			}
 			Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
 			statement.setFetchDirection(ResultSet.TYPE_FORWARD_ONLY);
 			ResultSet rows = connection.createStatement().executeQuery(query);
 			rows.last();
 			super.setCount(rows.getRow());
 		} finally {
 			try {
 				connection.close();
 			} catch (SQLException sqle) {
 				logger.warn("SQLPaginator(pool = " + pool + ", query = " + query + ", size = " + size + ", pages = " + pages + ", isolation = "
 						+ isolation + ") - failed connection close", sqle);
 			}
 		}
 	}
 
 	/**
 	 * @see net.smartlab.web.Paginator#setArray()
 	 */
 	protected void setArray() {
 		if (logger.isTraceEnabled()) {
 			logger.trace("setArray() - start");
 		}
 		Connection connection = null;
 		try {
 			connection = pool.getConnection();
 			if (isolation != DEFAULT_ISOLATION_LEVEL) {
 				connection.setTransactionIsolation(isolation);
 			}
 			Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
 			ResultSet rows = statement.executeQuery(query);
 			rows.absolute(((this.getPage() - 1) * array.length) + 1);
 			for (int i = 0; i < array.length; i++) {
 				array[i] = this.getObject(rows);
 				if (!rows.next() && !(i == array.length - 1)) {
 					array[i + 1] = null;
 					break;
 				}
 			}
 		} catch (SQLException sqle) {
 			throw new PaginationException(sqle);
 		} finally {
 			try {
 				connection.close();
 			} catch (SQLException sqle) {
 				logger.warn("setArray() - failed connection close", sqle);
 			}
 		}
 	}
 
 	/**
 	 * Returns the current row datas.
 	 * 
 	 * @param rows the paginated results.
 	 * @return the current row datas as an array of objects.
 	 */
 	protected Object[] getObject(ResultSet rows) {
 		if (logger.isTraceEnabled()) {
 			logger.trace("getObject(rows = " + rows + ") - start");
 		}
 		try {
 			ResultSetMetaData info = rows.getMetaData();
 			Object[] values = new Object[info.getColumnCount()];
 			for (int i = 0; i < values.length; i++) {
 				values[i] = rows.getObject(i + 1);
 			}
 			return values;
 		} catch (SQLException sqle) {
 			logger.error("getObject(rows = " + rows + ") - error", sqle);
 			return null;
 		}
 	}
 }