azophy/benchmark_query.php

## benchmark_query.php
<?php
/*
PHP PDO is mostly based on tutorials in https://phpdelusions.net/pdo.
*/

// connection configs
$DB_TYPE     = getenv('DB_TYPE');
$DB_HOST     = getenv('DB_HOST');
$DB_USER     = getenv('DB_USER');
$DB_PASS     = getenv('DB_PASS');
$DB_DATABASE = getenv('DB_DATABASE');
$charset     = getenv('DB_CHARSET') ?: 'utf8mb4';

// actual input. assumed that this is a webpage, but could also be modified for CLI script.
$params_string = $_POST['params_string'];
$query_string = $_POST['query_string'];

// option to run each data row multiple times to get more accurate measurements
$num_experiment_repeat = 3;
$inbetween_sleep_duration = 2; // seconds

// Map Rows and Loop Through Them. Modified from https://stackoverflow.com/a/41942299/2496217
$rows   = array_map('str_getcsv', explode("\n", $params_string));
$header = array_shift($rows);
$inputs = array_map(fn($row) => array_combine($header, $row), $rows);

// PDO configs
$dsn = "$DB_TYPE:host=$DB_HOST;dbname=$DB_DATABASE;charset=$charset";
$options = [
      PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES   => false,
];

$final_results = false;

function run_experiment() {
  global $dsn, $DB_USER, $DB_PASS, $options, $query_string, $inputs, $num_experiment_repeat, $inbetween_sleep_duration;

  $results = [];

  try {
    $pdo = new PDO($dsn, $DB_USER, $DB_PASS, $options);

    $stmt = $pdo->prepare($query_string);

    foreach ($inputs as $key => $data) {
      if (empty($data)) continue;

      echo("executing row #$key....\n");
      print_r($data);
      $filtered_data = array_filter(
        $data,
        fn($i) => (substr(trim($i),0,1) != '_'),
        ARRAY_FILTER_USE_KEY
      );

      for ($i=0;$i<$num_experiment_repeat;$i++) {
        echo("- attempt  #$i....");flush();
        $error = null;

        if ($i>0) sleep($inbetween_sleep_duration);

        $start_time = microtime(true);

        try {
          $stmt->execute($filtered_data);
        } catch (\Exception $e) {
          $error = $e;
        }

        $end_time = microtime(true);
        $duration = $end_time - $start_time;
        printf("takes (%.3f seconds)\n", $duration);
        flush();

        $results[] = array_merge($data ,compact('start_time', 'end_time', 'error'));
      }

      echo "=======================\n";

    }

    return $results;

  } catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
  }
}
?>

<!-- MVP.css quickstart template: https://github.com/andybrewer/mvp/ -->
<!DOCTYPE html>
<html lang="en">

<head>
    <link rel="icon" href="https://via.placeholder.com/70x70/0000FF">
    <link rel="stylesheet" href="https://unpkg.com/mvp.css@1.12/mvp.css">

    <meta charset="utf-8">
    <meta name="description" content="My description">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>SQL Query Benchmark Runner</title>
</head>

<body>
    <main>
      <section>
            <form action="#" method="POST">
              <header>
                  <h2>Experiment Definition</h2>
              </header>
              <div style="display:flex">
                <aside>
                    <h3>Query</h3>
                    <textarea name="query_string" id="" cols="30" rows="10"><?= $query_string ?></textarea>
                    <p>use named palceholder as described <a href=" https://phpdelusions.net/pdo#prepared">here</a>. column name prefixed with '_' would be ignored.</p>
                </aside>
                <aside>
                    <h3>Params (in CSV)</h3>
                    <textarea name="params_string" id="" cols="30" rows="10"><?= $params_string ?></textarea>
                </aside>
              </div>

              <footer >
                <button style="margin:auto" type="submit">Submit</button>
              </footer>
            </form>
        </section>

        <?php if (isset($_POST['query_string'])): ?>
        <hr>
        <section>
          <details open="" style="width:100%">
            <summary>Execution logs...</summary>
            <pre style="background:#eee;padding:1em"><?php
              // echo print_r($inputs);
              try {
                $final_results = run_experiment();
              } catch (\Exception $e) {
                print_r($e);
              }
            ?></pre>
          </details>
        </section>
        <?php endif ?>

        <?php if ($final_results): ?>
        <hr>
        <section style="width:80wh">
          <header>
            <h2>Final Results:</h2>
          </header>

          <div style="background:#eee;padding:1em"><?= json_encode($final_results); ?></div>
        </section>
        <?php endif ?>
    </main>
</body>

</html>
	<?php
	/*
	PHP PDO is mostly based on tutorials in https://phpdelusions.net/pdo.
	*/

	// connection configs
	$DB_TYPE = getenv('DB_TYPE');
	$DB_HOST = getenv('DB_HOST');
	$DB_USER = getenv('DB_USER');
	$DB_PASS = getenv('DB_PASS');
	$DB_DATABASE = getenv('DB_DATABASE');
	$charset = getenv('DB_CHARSET') ?: 'utf8mb4';

	// actual input. assumed that this is a webpage, but could also be modified for CLI script.
	$params_string = $_POST['params_string'];
	$query_string = $_POST['query_string'];

	// option to run each data row multiple times to get more accurate measurements
	$num_experiment_repeat = 3;
	$inbetween_sleep_duration = 2; // seconds

	// Map Rows and Loop Through Them. Modified from https://stackoverflow.com/a/41942299/2496217
	$rows = array_map('str_getcsv', explode("\n", $params_string));
	$header = array_shift($rows);
	$inputs = array_map(fn($row) => array_combine($header, $row), $rows);

	// PDO configs
	$dsn = "$DB_TYPE:host=$DB_HOST;dbname=$DB_DATABASE;charset=$charset";
	$options = [
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
	PDO::ATTR_EMULATE_PREPARES => false,
	];

	$final_results = false;

	function run_experiment() {
	global $dsn, $DB_USER, $DB_PASS, $options, $query_string, $inputs, $num_experiment_repeat, $inbetween_sleep_duration;

	$results = [];

	try {
	$pdo = new PDO($dsn, $DB_USER, $DB_PASS, $options);

	$stmt = $pdo->prepare($query_string);

	foreach ($inputs as $key => $data) {
	if (empty($data)) continue;

	echo("executing row #$key....\n");
	print_r($data);
	$filtered_data = array_filter(
	$data,
	fn($i) => (substr(trim($i),0,1) != '_'),
	ARRAY_FILTER_USE_KEY
	);

	for ($i=0;$i<$num_experiment_repeat;$i++) {
	echo("- attempt #$i....");flush();
	$error = null;

	if ($i>0) sleep($inbetween_sleep_duration);

	$start_time = microtime(true);

	try {
	$stmt->execute($filtered_data);
	} catch (\Exception $e) {
	$error = $e;
	}

	$end_time = microtime(true);
	$duration = $end_time - $start_time;
	printf("takes (%.3f seconds)\n", $duration);
	flush();

	$results[] = array_merge($data ,compact('start_time', 'end_time', 'error'));
	}

	echo "=======================\n";

	}

	return $results;

	} catch (\PDOException $e) {
	throw new \PDOException($e->getMessage(), (int)$e->getCode());
	}
	}
	?>

	<!-- MVP.css quickstart template: https://github.com/andybrewer/mvp/ -->
	<!DOCTYPE html>
	<html lang="en">

	<head>
	<link rel="icon" href="https://via.placeholder.com/70x70/0000FF">
	<link rel="stylesheet" href="https://unpkg.com/mvp.css@1.12/mvp.css">

	<meta charset="utf-8">
	<meta name="description" content="My description">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">

	<title>SQL Query Benchmark Runner</title>
	</head>

	<body>
	<main>
	<section>
	<form action="#" method="POST">
	<header>
	<h2>Experiment Definition</h2>
	</header>
	<div style="display:flex">
	<aside>
	<h3>Query</h3>
	<textarea name="query_string" id="" cols="30" rows="10"><?= $query_string ?></textarea>
	<p>use named palceholder as described <a href=" https://phpdelusions.net/pdo#prepared">here</a>. column name prefixed with '_' would be ignored.</p>
	</aside>
	<aside>
	<h3>Params (in CSV)</h3>
	<textarea name="params_string" id="" cols="30" rows="10"><?= $params_string ?></textarea>
	</aside>
	</div>

	<footer >
	<button style="margin:auto" type="submit">Submit</button>
	</footer>
	</form>
	</section>

	<?php if (isset($_POST['query_string'])): ?>
	<hr>
	<section>
	<details open="" style="width:100%">
	<summary>Execution logs...</summary>
	<pre style="background:#eee;padding:1em"><?php
	// echo print_r($inputs);
	try {
	$final_results = run_experiment();
	} catch (\Exception $e) {
	print_r($e);
	}
	?></pre>
	</details>
	</section>
	<?php endif ?>

	<?php if ($final_results): ?>
	<hr>
	<section style="width:80wh">
	<header>
	<h2>Final Results:</h2>
	</header>

	<div style="background:#eee;padding:1em"><?= json_encode($final_results); ?></div>
	</section>
	<?php endif ?>
	</main>
	</body>

	</html>