<?php
namespace ModulesAbstract\PluginReports\Report\Model\Gatherer;

/**
 * Return results by iterating over results in a table.
 * Internally paginates data, to avoid eating too much RAM, because ADODB ALWAYS buffers results
 * Accepts a callback function, called for each row, allowing further sub-selects or data-uncompressing to take place
 *
 * Accepted filters:
 * 	none, the query should come with pre-filtering built-in
 *
 * WARNING: this class will have an undefined behaviour (double reporting of some double rows or completly skip some rows) if the table contents are changed while paginating/transferring data
 *
 * This class is mostly used as a workaround for large datasets being returned by mysql, and adodb's choice to always buffer results
 * Optimizations for most queries include:
 * 	- do NOT pre-select blob columns, as they will force mysql to use an on-disk temp table, no matter how bug the data is
 * 		@see https://dev.mysql.com/doc/refman/5.7/en/blob.html:
 * 			> Instances of BLOB or TEXT columns in the result of a query that is processed using a temporary table causes
 * 			> the server to use a table on disk rather than in memory because the MEMORY storage engine does not support
 * 			> those data types
 * 	- avoid doing ORDER BY, because this will mean in a lot of cases use of on-disk-temp-table, used for sorting
 * 	- alter the server settings:
 * 		innodb_file_per_table=1
 * 		innodb_file_format=Barracuda,
 * 		ALTER TABLE `big_data_table_with_lots_of_records` ENGINE=InnoDB row_format=compressed;
 *
 *
 * From tests:
 * 	- when using this in ModulesAbstract\PluginReports\Report\Model\Gatherer\BspPeriodRowsCachedAndPaginated
 * 		(the original gatherer did only 1 SQL, joining with BLOB-data-table)
 * 		this takes 3-4 times longer to execute than the original gatherer
 * 		but uses 1.25Mb RAM, versus 300Mb RAM the original gatherer
 *
 * 	- when using this in a uTests (synthetic benchmark, without other interactions with other classes)
 * 		(the original did paginated and post-select for each blob-data-row, the second test did paginated SQLs, joining with BLOB-data-table)
 * 		this takes 5m vs 38m
 * 		but uses 1.75Mb RAM vs 1.25Mb
 * 		tested on a table with 460k records. On a smaller table (50k records), the original, simplified SQL will be much faster (19s vs 10s)
 *
 * @author Lucian Sirbu
 */
class DatabasePaginatedIterator extends GathererAbstract
{
	protected $query = null;
	protected $queryParams = null;

	protected $page = 0;

	protected $queryResultsIterator = null;

	protected $preReturnCallback = null;

	protected $preFilterCallback = null;

	public function __construct($query, $queryParams=[])
	{
		$this->query = $query;
		$this->queryParams = $queryParams;

		$this->options['queueLength'] = 2500;
	}

	public function getRequiredFilters()
	{
		return [];
	}

	public function getAcceptedFilters()
	{
		return [];
	}

	public function setPreReturnCallback($preReturnCallback)
	{
		$this->preReturnCallback = $preReturnCallback;
		return $this;
	}

	public function setPreFilterCallback($preFilterCallback)
	{
		$this->preFilterCallback = $preFilterCallback;
		return $this;
	}

	protected function objectInitialize()
	{
	}

	/**
	 * (non-PHPdoc)
	 * @see \ModulesAbstract\PluginReports\Report\Model\Gatherer\GathererAbstract::objectDestroy()
	 */
	protected function objectDestroy()
	{
		$this->queryResultsIterator = null;
	}

	/**
	 * Build the SQL to use a paginated query
	 * @param string $query
	 * @param int $page
	 * @param int $queueLength
	 * @return string
	 */
	protected function buildPaginatedQuery($query, $page, $queueLength)
	{
		$query.= " LIMIT __RESULTS_START__,__RESULTS_PER_PAGE__";
		$replacements = [
			'__RESULTS_START__' => 		$page*$queueLength,
			'__RESULTS_PER_PAGE__' => 	$queueLength,
		];

		return str_replace(array_keys($replacements), array_values($replacements), $query);
	}

	/**
	 * (non-PHPdoc)
	 * @see \ModulesAbstract\PluginReports\Report\Model\Gatherer\GathererAbstract::objectNext()
	 */
	protected function objectNext()
	{
		$ret = null;

		if (!$this->queryResultsIterator) {
			$db = \DB::getInstance();
			$this->queryResultsIterator = $db->Execute($this->buildPaginatedQuery($this->query, $this->page, $this->options['queueLength']), $this->queryParams);
			if ($db->ErrorNo()) {
				throw new \DBException("Cannot run paginated query!");
			}

			$this->page++;

			$this->stats['resultsOnThisPage'] = 0;
			$this->stats['page:next']++;
		}

		// we still have data to iterate over
		if (!$this->queryResultsIterator->EOF) {
			$this->stats['resultsOnThisPage']++;
			$ret = $this->queryResultsIterator->FetchRow();

			if ($this->preFilterCallback) {
				$fcn = $this->preFilterCallback;
				if ($fcn($ret)=='intern:skip') {
					return 'intern:skip';
				}
			}

			// use the callback
			if ($this->preReturnCallback) {
				$fcn = $this->preReturnCallback;
				return $fcn($ret);
			}

			// else
			return $ret;
		}
		else {
			if ($this->stats['resultsOnThisPage'] < $this->options['queueLength']) {
				/**
				 * The resultsset if finished and we returned less than the expected queueLength results
				 *  or we went one page 'after' all results were finished
				 *  Any of the above cases means that there is nothing to report anymore, so stop the iterator
				 */
				return null;
			}
			else {
				// we still have 'pages' to go through, reset the ResultSet, repeat loop
				$this->queryResultsIterator = null;
				return 'intern:skip';
			}
		}

		return 'intern:skip';
	}
}
