<?php
define('DSN', 'sqlite:locktest.sq3');

$lock_types = array('DEFERRED', 'IMMEDIATE', 'EXCLUSIVE');

function get_pdo() {
	$db = new PDO(DSN);
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$db->setAttribute(PDO::ATTR_TIMEOUT, 4);
	return $db;
}

$db = get_pdo();

// prepare statements
$start = microtime(true);
$select = $db->prepare('SELECT COUNT(*) FROM locktest');
$insert = $db->prepare("INSERT OR REPLACE INTO locktest (item) VALUES (?)");
printf("%30s: %0.4f\n", "PREPARE", microtime(true)-$start);
sleep(1);

// implicit read
$start = microtime(true);
$select->execute();
$data = $select->fetchAll();
printf("%30s: %0.4f %d\n", "IMPLICIT READ", microtime(true)-$start, $data[0][0]);
sleep(1);

// implicit write
$start = microtime(true);
$insert->execute(array(rand(0, 1000)));
printf("%30s: %0.4f %d\n", "IMPLICIT WRITE", microtime(true)-$start, $data[0][0], $insert->rowCount());
sleep(1);

foreach($lock_types as $lock_type) {
	
	// read
	$start = microtime(true);
	try {
		$db->exec("BEGIN {$lock_type} TRANSACTION");
	} catch(PDOException $e1) {
		echo "{$lock_type} BEGIN TRANSACTION - {$e1->getMessage()}\n";
		continue;
	}
	$select->execute();
	$data = $select->fetchAll();
	$db->exec("COMMIT");
	printf("%30s: %0.4f %d\n", "{$lock_type} READ", microtime(true)-$start, $data[0][0]);
	sleep(1);
	
	// write
	$start = microtime(true);
	$db->exec("BEGIN {$lock_type} TRANSACTION");
	$insert->execute(array(rand(0, 1000)));
	$db->exec("COMMIT");
	printf("%30s: %0.4f %d\n", "{$lock_type} WRITE", microtime(true)-$start, $data[0][0], $insert->rowCount());
	sleep(1);
	
	// read write
	$start = microtime(true);
	$db->exec("BEGIN {$lock_type} TRANSACTION");
	$select->execute();
	$data = $select->fetchAll();
	// DEFERRED can read the data but throws error on write
	try {
		$insert->execute(array(rand(0, 1000)));
		$db->exec("COMMIT");
		printf("%30s: %0.4f %d\n", "{$lock_type} READ/WRITE", microtime(true)-$start, $data[0][0]);
	} catch(PDOException $e1) {
		echo "{$lock_type} READ/WRITE - {$e1->getMessage()}\n";
		try {
			$db->EXEC("ROLLBACK");
		} catch(PDOException $e2) {
			echo "{$lock_type} READ/WRITE - {$e2->getMessage()}\n";
			$db = get_pdo();
		}
	}
	
	sleep(1);
	
	// write read
	$start = microtime(true);
	$db->exec("BEGIN {$lock_type} TRANSACTION");
	try {
		$insert->execute(array(rand(0, 1000)));
		$select->execute();
		$data = $select->fetchAll();
		$db->exec("COMMIT");
		printf("%30s: %0.4f %d\n", "{$lock_type} WRITE/READ", microtime(true)-$start, $data[0][0]);
	} catch(PDOException $e1) {
		echo "{$lock_type} WRITE/READ - {$e1->getMessage()}\n";
		$db->exec("ROLLBACK");
	}
	sleep(1);
}