<?php


function connection_mysql($connection_id) {
  static $connections = array();
  if (!isset($connections[$connection_id])) {
    $connection_options['pdo'] = array();
    $connection_options['pdo'][PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
    $cnn = new \PDO("mysql:host=127.0.0.1;dbname=testingsite26",  'root', 'root', $connection_options['pdo']);
    $cnn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $cnn->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
    $connections[$connection_id] = $cnn;
  }
  return $connections[$connection_id];
}

/**
 *
 * @param string $connection_id
 *
 * @return PDO
 */
function connection($connection_id, $fetch_numeric = FALSE) {
  $host = $_SERVER['SABENTIS_DB_HOST'];
  $database = $_SERVER['SABENTIS_DB_DATABASE'];
  $username =  $_SERVER['SABENTIS_DB_LOGIN'];
  $password =  $_SERVER['SABENTIS_DB_PASSWORD'];

  static $connections = array();
  if (!isset($connections[$connection_id])) {
    $connection_options['pdo'] = array();
    $connection_options['pdo'][PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
    $connection_options['pdo'][PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE] = $fetch_numeric;
    $connection_options['pdo'][PDO::ATTR_STRINGIFY_FETCHES] = FALSE;
    $dns = "sqlsrv:server=$host;Database=$database";
    echo $dns;
    $cnn = new \PDO($dns,  $username, $password, $connection_options['pdo']);
    $connections[$connection_id] = $cnn;

  }
  return $connections[$connection_id];
}

/**
 * Summary of prepare
 *
 * @param mixed $connection
 * @param mixed $query
 * @return PDOStatement
 */
function prepare($connection, $query, $buffered = TRUE) {
  $pdo_options = array();
  $pdo_options[PDO::ATTR_EMULATE_PREPARES] = TRUE;
  $pdo_options[PDO::SQLSRV_ATTR_DIRECT_QUERY] = TRUE;
  if ($buffered) {
    $pdo_options[PDO::ATTR_CURSOR] = PDO::CURSOR_SCROLL;
    $pdo_options[PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE] = PDO::SQLSRV_CURSOR_BUFFERED;
  }
  return $connection->prepare($query, $pdo_options);
}

function prepare_mysql($connection, $query, $buffered = TRUE) {
  $pdo_options = array();
  $pdo_options[PDO::MYSQL_ATTR_USE_BUFFERED_QUERY] = $buffered;
  return $connection->prepare($query, $pdo_options);
}

/**
 * Summary of execute
 *
 * @param PDO $connection
 * @param string $query
 *
 * @param PDOStatement;
 */
function execute($connection, $query, array $args = array(), $buffered = TRUE) {
  $st = prepare($connection, $query, $buffered);
  $st->execute($args);
  return $st;
}

function execute_mysql($connection, $query, array $args = array(), $buffered = TRUE) {
  $st = prepare_mysql($connection, $query, $buffered);
  $st->execute($args);
  return $st;
}


//*******************************************************
// TEST BEGIN
//*******************************************************

$connection = connection('default');

// Drop
try {
  execute($connection, 'DROP TABLE TEST');
}
catch(\Exception $e) {}


// Recreate
execute($connection, "CREATE TABLE TEST([id] [int] IDENTITY(1,1) NOT NULL, [myinteger] int, [mybiginteger] bigint, [myfloat] float, [mysmallint] smallint, [mybit] bit, [myreal] real, [mynvarchar] nvarchar(max))");

$sample_data = [
    ':myinteger' => 25,
    ':mybiginteger' =>1236544123211,
    ':myfloat' =>23652.3221452,
    ':mysmallint' =>5632,
    ':mybit' => 1,
    ':myreal' => 1256325.3214587,
    ':mynvarchar' => 'en un lugar de la mancha de cuyo nombre no puedo acordarme...'
  ];

$columns = array_keys($sample_data);
array_walk($columns, function(&$item) {
  $item = substr($item, 1, strlen($item) - 1);
});

$time = microtime(TRUE);
try {
  for ($x = 0; $x < 5000; $x++) {
    execute($connection, "INSERT INTO TEST (" . implode(',', $columns) . ") VALUES (" . implode(',', array_keys($sample_data)) . ")", $sample_data);
  }
}
catch (\Throwable $error) {
  echo $error->getMessage();
  exit();
}
echo "<br/>INSERT LAPSED:" . (microtime(TRUE) - $time) * 1000;

$statement = execute($connection, "SELECT * FROM TEST");
$time = microtime(TRUE);
$i = 0;
while($row = $statement->fetch(PDO::FETCH_ASSOC)) {
  $i++;
}
echo "<br/>LAPSED FETCH_ASSOC ($i):" . (microtime(TRUE) - $time) * 1000;

$statement = execute($connection, "SELECT * FROM TEST");
$time = microtime(TRUE);
$i = 0;
while($row = $statement->fetch(PDO::FETCH_OBJ)) {
  $i++;
}
echo "<br/>LAPSED FETCH_OBJ ($i):" . (microtime(TRUE) - $time) * 1000;


$statement = execute($connection, "SELECT * FROM TEST", [], FALSE);
$time = microtime(TRUE);
$i = 0;
while($row = $statement->fetch(PDO::FETCH_ASSOC)) {
  $i++;
}
echo "<br/>LAPSED FETCH_ASSOC NON BUFFERED ($i):" . (microtime(TRUE) - $time) * 1000;

$statement = execute($connection, "SELECT * FROM TEST", [], FALSE);
$time = microtime(TRUE);
$i = 0;
while($row = $statement->fetch(PDO::FETCH_OBJ)) {
  $i++;
}
echo "<br/>LAPSED FETCH_OBJ NON BUFFERED ($i):" . (microtime(TRUE) - $time) * 1000;

echo "<br/> NOW FETCH WITH NUMERIC";

$connection = connection('default', TRUE);

// Drop
try {
  execute($connection, 'DROP TABLE TEST');
}
catch(\Exception $e) {}


// Recreate
execute($connection, "CREATE TABLE TEST([id] [int] IDENTITY(1,1) NOT NULL, [myinteger] int, [mybiginteger] bigint, [myfloat] float, [mysmallint] smallint, [mybit] bit, [myreal] real, [mynvarchar] nvarchar(max))");

$sample_data = [
    ':myinteger' => 25,
    ':mybiginteger' =>1236544123211,
    ':myfloat' =>23652.3221452,
    ':mysmallint' =>5632,
    ':mybit' => 1,
    ':myreal' => 1256325.3214587,
    ':mynvarchar' => 'en un lugar de la mancha de cuyo nombre no puedo acordarme...'
  ];

$columns = array_keys($sample_data);
array_walk($columns, function(&$item) {
  $item = substr($item, 1, strlen($item) - 1);
});

$time = microtime(TRUE);
try {
  for ($x = 0; $x < 5000; $x++) {
    execute($connection, "INSERT INTO TEST (" . implode(',', $columns) . ") VALUES (" . implode(',', array_keys($sample_data)) . ")", $sample_data);
  }
}
catch (\Throwable $error) {
  echo $error->getMessage();
  exit();
}
echo "<br/>INSERT LAPSED:" . (microtime(TRUE) - $time) * 1000;

$statement = execute($connection, "SELECT * FROM TEST");
$time = microtime(TRUE);
$i = 0;
while($row = $statement->fetch(PDO::FETCH_ASSOC)) {
  $i++;
}
echo "<br/>LAPSED FETCH_ASSOC ($i):" . (microtime(TRUE) - $time) * 1000;

$statement = execute($connection, "SELECT * FROM TEST");
$time = microtime(TRUE);
$i = 0;
while($row = $statement->fetch(PDO::FETCH_OBJ)) {
  $i++;
}
echo "<br/>LAPSED FETCH_OBJ ($i):" . (microtime(TRUE) - $time) * 1000;


$statement = execute($connection, "SELECT * FROM TEST", [], FALSE);
$time = microtime(TRUE);
$i = 0;
while($row = $statement->fetch(PDO::FETCH_ASSOC)) {
  $i++;
}
echo "<br/>LAPSED FETCH_ASSOC NON BUFFERED ($i):" . (microtime(TRUE) - $time) * 1000;

$statement = execute($connection, "SELECT * FROM TEST", [], FALSE);
$time = microtime(TRUE);
$i = 0;
while($row = $statement->fetch(PDO::FETCH_OBJ)) {
  $i++;
}
echo "<br/>LAPSED FETCH_OBJ NON BUFFERED ($i):" . (microtime(TRUE) - $time) * 1000;

# Do the same for MYSQL

echo "<br/> HERE COMES MYSQL";


$connection = connection_mysql('default');

// Drop
try {
  execute_mysql($connection, 'DROP TABLE TEST');
}
catch(\Exception $e) {

}


// Recreate
execute_mysql($connection, "CREATE TABLE TEST (id int NOT NULL AUTO_INCREMENT, myinteger int, mybiginteger int, myfloat float, mysmallint smallint, mybit bit, myreal real, mynvarchar nvarchar(255), PRIMARY KEY (ID))");

$sample_data = [
    ':myinteger' => 25,
    ':mybiginteger' =>2543,
    ':myfloat' =>23652.3221452,
    ':mysmallint' =>532,
    ':mybit' => false,
    ':myreal' => 12325.3214587,
    ':mynvarchar' => 'en un lugar de la mancha de cuyo nombre no puedo acordarme...'
  ];

$columns = array_keys($sample_data);
array_walk($columns, function(&$item) {
  $item = substr($item, 1, strlen($item) - 1);
});

$time = microtime(TRUE);
try {
  for ($x = 0; $x < 5000; $x++) {
    execute_mysql($connection, "INSERT INTO TEST (" . implode(',', $columns) . ") VALUES (" . implode(',', array_keys($sample_data)) . ")", $sample_data);
  }
}
catch (\Throwable $error) {
  echo $error->getMessage();
  exit();
}
echo "<br/>INSERT LAPSED:" . (microtime(TRUE) - $time) * 1000;

$statement = execute_mysql($connection, "SELECT * FROM TEST");
$time = microtime(TRUE);
$i = 0;
while($row = $statement->fetch(PDO::FETCH_ASSOC)) {
  $i++;
}
echo "<br/>LAPSED FETCH_ASSOC ($i):" . (microtime(TRUE) - $time) * 1000;

$statement = execute_mysql($connection, "SELECT * FROM TEST");
$time = microtime(TRUE);
$i = 0;
while($row = $statement->fetch(PDO::FETCH_OBJ)) {
  $i++;
}
echo "<br/>LAPSED FETCH_OBJ ($i):" . (microtime(TRUE) - $time) * 1000;


$statement = execute_mysql($connection, "SELECT * FROM TEST", [], FALSE);
$time = microtime(TRUE);
$i = 0;
while($row = $statement->fetch(PDO::FETCH_ASSOC)) {
  $i++;
}
echo "<br/>LAPSED FETCH_ASSOC NON BUFFERED ($i):" . (microtime(TRUE) - $time) * 1000;

$statement = execute_mysql($connection, "SELECT * FROM TEST", [], FALSE);
$time = microtime(TRUE);
$i = 0;
while($row = $statement->fetch(PDO::FETCH_OBJ)) {
  $i++;
}
echo "<br/>LAPSED FETCH_OBJ NON BUFFERED ($i):" . (microtime(TRUE) - $time) * 1000;