#!/usr/bin/php
<?php

define('OK', 0);

// 20'000 rows
$lLoops = 2000;
$lChunkSize = 10;

// ----------------------------------------------------------------------
function printUsage()
// ----------------------------------------------------------------------
{

	$lMyName = basename(__FILE__);

	echo "NAME
        $lMyName - Load data test script.

SYNOPSIS
        $lMyName <options>

OPTIONS
        --prepare
                Prepares the test.

        --clean-up
                Cleans-up the remainings.

        --help
                Print this help.

        --run
                Runs the test.

        --variant=<n>
                Runs the test with variant 1..3.

        --user=<username>
                Database user.

        --password=<password>
                Password of database user.

        --host=<hostname>
                Hostname of the database.

        --port=<n>
                Port of the database.

        --database=<database>
                Database name.

        --database-type={mysql|postgresql}
                Database type.

";
}

// ----------------------------------------------------------------------
function checkParameter($aParameter, $aArgv)
// ----------------------------------------------------------------------
{
	$rc = OK;
	// var_dump($aParameter, $aArgv);

	$aOptions = array('h', 'prepare', 'clean-up', 'run', 'help', 'variant', 'user', 'password', 'host', 'port', 'database-type', 'database');
	$aOptionsWithValues = array('variant', 'user', 'password', 'host', 'port', 'database-type', 'database');

	if ( ! array_key_exists('database-type', $aParameter) ) {
		$rc = 99;
		echo 'ERROR' . " (rc=$rc)" . "\n";
		exit($rc);
	}
	if ( ! in_array($aParameter['database-type'], array('postgresql', 'mysql')) ) {
		$rc = 98;
		echo 'ERROR' . " (rc=$rc)" . "\n";
		exit($rc);
	}
	if ( ! array_key_exists('database', $aParameter) ) {
		$rc = 97;
		echo 'ERROR' . " (rc=$rc)" . "\n";
		exit($rc);
	}
	if ( ! array_key_exists('host', $aParameter) ) {
		$rc = 96;
		echo 'ERROR' . " (rc=$rc)" . "\n";
		exit($rc);
	}
	if ( ! array_key_exists('port', $aParameter) ) {
		$rc = 95;
		echo 'ERROR' . " (rc=$rc)" . "\n";
		exit($rc);
	}
	if ( ! array_key_exists('user', $aParameter) ) {
		$rc = 94;
		echo 'ERROR' . " (rc=$rc)" . "\n";
		exit($rc);
	}
	if ( ! array_key_exists('password', $aParameter) ) {
		$rc = 93;
		echo 'ERROR' . " (rc=$rc)" . "\n";
		exit($rc);
	}
	if ( array_key_exists('run', $aParameter) ) {

		if ( ! array_key_exists('variant', $aParameter) ) {
			$rc = 92;
			echo 'ERROR' . " (rc=$rc)" . "\n";
			exit($rc);
		}

		if ( ! in_array(intval($aParameter['variant']), array(1, 2, 3)) ) {
			$rc = 91;
			echo 'ERROR' . " (rc=$rc)" . "\n";
			exit($rc);
		}
	}

	return $rc;
}

// ----------------------------------------------------------------------
function openDatabaseConnection($aParameter, &$dbh)
// ----------------------------------------------------------------------
{
	$rc = OK;
	$dbh = false;

	try {

		$aExtensions = get_loaded_extensions();
		if ( ! in_array('PDO', $aExtensions) ) {
			$rc = 1147;
			$msg = 'PHP extensions PDO is not loaded.' . " (rc=$rc)";
			throw new Exception($msg, $rc);
		}

		$aPDODrivers = PDO::getAvailableDrivers();
		$driver = '';
    if ( 'postgresql' == $aParameter['database-type'] ) {
			$driver = 'pgsql';
			if ( ! in_array($driver, $aPDODrivers) ) {
				$rc = 1148;
				$msg = 'PDO driver ' . $driver . ' is not available.' . " (rc=$rc)";
				throw new Exception($msg, $rc);
			}
		}
		elseif ( 'mysql' == $aParameter['database-type'] ) {
			$driver = 'mysql';
			if ( ! in_array($driver, $aPDODrivers) ) {
				$rc = 1150;
				$msg = 'PDO driver ' . $driver . ' is not available.' . " (rc=$rc)";
				throw new Exception($msg, $rc);
			}
		}

		try {

			$dsn = $driver . ':host=' . $aParameter['host'] . ';port='. $aParameter['port'] . ';dbname=' . $aParameter['database'];

			$dbh = new PDO($dsn, $aParameter['user'], $aParameter['password']);
		}
			catch ( PDOException $e ) {
				$rc = 1149;
				$msg = $e->getMessage() . " (rc=$rc)";
				throw new Exception($msg, $rc);
			}
		}
		catch ( Exception $e ) {
			echo $e->getMessage() . "\n";
		}

	return $rc;
}

// ----------------------------------------------------------------------
function closeDatabaseConnection($dbh)
// ----------------------------------------------------------------------
{
	$dbh = null;
}

// ----------------------------------------------------------------------
function prepareTest($dbh, $aParameter)
// ----------------------------------------------------------------------
{
	$rc = OK;
	echo 'Prepare test...' . "\n";

	// DROP TABLE pending_data;

	if ( 'mysql' == $aParameter['database-type'] ) {
		$sql = 'CREATE TABLE pending_data (
  id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  data text NOT NULL,
  tracker_id varchar(255) NOT NULL,
  track_time datetime DEFAULT NULL,
  imported_at datetime DEFAULT NULL,
  created datetime NOT NULL,
  pool_id bigint DEFAULT NULL,
  gsm_quality tinyint DEFAULT NULL
)';
	}
	elseif ( 'postgresql' == $aParameter['database-type'] ) {
		$sql = 'CREATE TABLE pending_data (
  id SERIAL PRIMARY KEY,
  data text NOT NULL,
  tracker_id varchar(255) NOT NULL,
  track_time TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
  imported_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
  created TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  pool_id bigint DEFAULT NULL,
  gsm_quality smallint DEFAULT NULL
)';
	}

	$void = $dbh->exec($sql);

	$sql = 'CREATE INDEX tracker_id ON pending_data (tracker_id)';
	$void = $dbh->exec($sql);

	$sql = 'CREATE INDEX imported_at ON pending_data (imported_at)';
	$void = $dbh->exec($sql);

	$sql = 'CREATE INDEX tracker_id_2 ON pending_data (tracker_id, imported_at)';
	$void = $dbh->exec($sql);

	$sql = 'CREATE INDEX created ON pending_data (created)';
	$void = $dbh->exec($sql);

	$sql = 'CREATE INDEX ix_pending_data_pool_tracker ON pending_data (pool_id, tracker_id)';
	$void = $dbh->exec($sql);

	echo 'Inserting rows...' . "\n";

	$sql = "INSERT INTO pending_data (data, tracker_id, track_time, imported_at, created, pool_id)
SELECT 'Some very long string with tracker characteristics and measurement data', 31415926535897932384626, NULL, NULL, '2026-01-29 10:30:21', 26";
	$void = $dbh->exec($sql);

	// Blow table up to 16 M rows
	// for ( $i = 1; $i <= 24; $i++ ) {
	for ( $i = 1; $i <= 20; $i++ ) {
		$sql = "INSERT INTO pending_data (data, tracker_id, track_time, imported_at, created, pool_id)
SELECT 'Some very long string with tracker characteristics and measurement data', 31415926535897932384626, NULL, NULL, '2026-01-29 10:30:21', 26 FROM pending_data";
		$void = $dbh->exec($sql);
	}

	echo 'Optimize/Vacuum table...' . "\n";

	if ( 'mysql' == $aParameter['database-type'] ) {
		$sql = 'OPTIMIZE TABLE pending_data';
	}
	elseif ( 'postgresql' == $aParameter['database-type'] ) {
		$sql = 'VACUUM FULL pending_data';
	}
	// OPTIMIZE TABLE returns rows and this seems to cause an error on exec:
	// Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.
	// Consider using PDOStatement::fetchAll().
	// Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the Pdo\Mysql::ATTR_USE_BUFFERED_QUERY attribute.
	$sth = $dbh->query($sql);
	$void = $sth->fetchAll();

	if ( 'mysql' == $aParameter['database-type'] ) {
		$sql = 'CREATE TABLE final_data LIKE pending_data';
	}
	elseif ( 'postgresql' == $aParameter['database-type'] ) {
		$sql = 'CREATE TABLE final_data (LIKE pending_data INCLUDING ALL)';
	}
	$void = $dbh->exec($sql);

	echo 'Rows inserted: ';

	$sql = 'SELECT COUNT(*) AS count FROM pending_data';
	$sth = $dbh->query($sql, PDO::FETCH_ASSOC);
	$result = $sth->fetchAll();
	echo $result[0]['count'] . "\n";

	echo 'Ready for testing...' . "\n";
	return $rc;
}

// ----------------------------------------------------------------------
function cleanUpTest($dbh, $aParameter)
// ----------------------------------------------------------------------
{
	$rc = OK;
	echo 'Clean-up test...' . "\n";

	$sql = 'DROP TABLE IF EXISTS pending_data CASCADE';
	$void = $dbh->exec($sql);

	$sql = 'DROP TABLE IF EXISTS final_data CASCADE';
	$void = $dbh->exec($sql);

	return $rc;
}

// ----------------------------------------------------------------------
function runTest($dbh, $aParameter, $pLoops, $pChunkSize)
// ----------------------------------------------------------------------
{
	$rc = OK;

	echo 'Run test number ' . $aParameter['variant'] . '...' . "\n";

	$lStartTime = microtime(true);

	switch ($aParameter['variant']) {
		case 1:

			for ( $loop = 1; $loop <= $pLoops ; $loop++ ) {

				$sql = 'SELECT * FROM pending_data LIMIT ' . $pChunkSize;
				if ( $sth = $dbh->query($sql, PDO::FETCH_ASSOC) ) {

					// Do not make too big chunks!
					foreach ( $sth->fetchAll() as $row ) {

						// Some other processing

						$sql = sprintf("INSERT INTO final_data VALUES (%d, '%s', '%s', '%s', '%s', '%s', %d, %d)"
								, $row['id'], $row['data'], $row['tracker_id']
								, is_null($row['track_time']) ? '2016-01-01 00:00:00' : $row['track_time']
								, is_null($row['imported_at']) ? '2016-01-01 00:00:00' : $row['imported_at']
								, $row['created'], $row['pool_id'], $row['gsm_quality']);
						$dbh->query($sql);
						$sql = sprintf("DELETE FROM pending_data WHERE id = %d", $row['id']);
						$dbh->query($sql);
					}
				}
			}
			break;
		case 2:

			for ( $loop = 1; $loop <= $pLoops ; $loop++ ) {

				if ( $sth = $dbh->query('SELECT * FROM pending_data LIMIT ' . $pChunkSize, PDO::FETCH_ASSOC) ) {

					$dbh->query('START TRANSACTION');

					// Do not make too big chunks!
					foreach ( $sth->fetchAll() as $row ) {

						// Some other processing

						$sql = sprintf("INSERT INTO final_data VALUES (%d, '%s', '%s', '%s', '%s', '%s', %d, %d)"
						, $row['id'], $row['data'], $row['tracker_id']
						, is_null($row['track_time']) ? '2016-01-01 00:00:00' : $row['track_time']
						, is_null($row['imported_at']) ? '2016-01-01 00:00:00' : $row['imported_at']
						, $row['created'], $row['pool_id'], $row['gsm_quality']);
						$dbh->query($sql);
						$sql = sprintf("DELETE FROM pending_data WHERE id = '%d'", $row['id']);
						$dbh->query($sql);
					}

					$dbh->query('COMMIT');
				}
			}
			break;
		case 3:

			for ( $loop = 1; $loop <= $pLoops ; $loop++ ) {

				if ( $sth = $dbh->query('SELECT * FROM pending_data LIMIT ' . $pChunkSize, PDO::FETCH_ASSOC) ) {

					$aIds = array();
					$aRows = array();

					$sql = 'INSERT INTO final_data VALUES ';

					// Some other processing

					// Do not make too big chunks!
					foreach ( $sth->fetchAll() as $row ) {
						array_push($aIds, $row['id']);
						array_push($aRows, sprintf("(%d, '%s', '%s', '%s', '%s', '%s', %d, %d)"
						, $row['id'], $row['data'], $row['tracker_id']
						, is_null($row['track_time']) ? '2016-01-01 00:00:00' : $row['track_time']
						, is_null($row['imported_at']) ? '2016-01-01 00:00:00' : $row['imported_at']
						, $row['created'], $row['pool_id'], $row['gsm_quality']));
					}
					$sql .= implode(', ', $aRows);

					$dbh->query('START TRANSACTION');
					$dbh->query($sql);

					$sql = sprintf("DELETE FROM pending_data WHERE id IN (%s)", implode(', ', $aIds));
					$dbh->query($sql);

					$dbh->query('COMMIT');
				}
			}
		break;
	}

	$lEndTime = microtime(true);
	echo 'Finished in: ' . round($lEndTime - $lStartTime, 3) . ' seconds.' . "\n";

	return $rc;
}

// ----------------------------------------------------------------------
// MAIN
// ----------------------------------------------------------------------

$rc = OK;

$shortopts = 'h';
$longopts  = array(
	'prepare'
, 'clean-up'
, 'run'
, 'help'
, 'variant:'
, 'user:'
, 'password:'
, 'host:'
, 'port:'
, 'database-type:'
, 'database:'
);

$aParameter = getopt($shortopts, $longopts);

if ( array_key_exists('help', $aParameter) || array_key_exists('h', $aParameter) ) {
	$rc = OK;
	printUsage();
	exit($rc);
}

$ret = checkParameter($aParameter, $argv);

$ret = openDatabaseConnection($aParameter, $dbh);
if ( OK != $ret ) {
	exit($ret);
}

if ( array_key_exists('prepare', $aParameter) ) {
	$ret = prepareTest($dbh, $aParameter);
}
elseif ( array_key_exists('clean-up', $aParameter) ) {
	$ret = cleanUpTest($dbh, $aParameter);
}
elseif ( array_key_exists('run', $aParameter) ) {
	$ret = runTest($dbh, $aParameter, $lLoops, $lChunkSize);
}
else {
	$rc = 100;
	echo 'ERROR' . " (rc=$rc)" . "\n";
}

$ret = closeDatabaseConnection($dbh);

exit($rc);

?>