<?php
/**
* Copyright (C) 2013 Gerrit Addiks.
* This package (including this file) was released under the terms of the GPL-3.0.
* You should have received a copy of the GNU General Public License along with this program.
* If not, see <http://www.gnu.org/licenses/> or send me a mail so i can send you a copy.
* @license GPL-3.0
* @author Gerrit Addiks <*****@*****.**>
* @package Addiks
*/
use Addiks\PHPSQL\PDO\PDO;
use Addiks\PHPSQL\Entity\Exception\MalformedSql;
use Addiks\PHPSQL\Result\ResultWriter;
require_once dirname(__FILE__) . "/bootstrap.php";
$pdo = new PDO("inmemory:some_example_database");
try {
// create a few tables inside that database
// (the ENGINE definition will be ignored)
$pdo->query("\n\n CREATE TABLE `product` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `name` varchar(64) DEFAULT NULL,\n `price` float DEFAULT NULL,\n PRIMARY KEY (`id`)\n ) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n\n CREATE TABLE `customer` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `name` varchar(64) DEFAULT NULL,\n PRIMARY KEY (`id`)\n ) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n\n CREATE TABLE `cart_item` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `customer_id` int(11) DEFAULT NULL,\n `product_id` int(11) DEFAULT NULL,\n PRIMARY KEY (`id`),\n KEY `fk_cusotmer_idx` (`customer_id`),\n KEY `fk_product_idx` (`product_id`),\n CONSTRAINT `fk_product` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,\n CONSTRAINT `fk_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION\n ) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n\n ");
// Oops, forgot the quantity on that cart-item table, let's add it
$pdo->query("ALTER TABLE `cart_item` ADD COLUMN `quantity` FLOAT(3,2) NOT NULL DEFAULT 1.0");
// Let's have a look on the table 'cart_item'
echo new ResultWriter($pdo->query("DESCRIBE `cart_item`")->getResult());
$pdo->query("\n INSERT INTO `product`\n (id, name, price)\n VALUES\n (1, 'Socks', 12.34),\n (2, 'Pants', 56.78),\n (3, 'T-Shirt', 31.41);\n\n INSERT INTO `customer`\n (id, name)\n VALUES\n (12, 'John Smith'),\n (34, 'Ka Ching'),\n (56, 'Hans Müller');\n\n INSERT INTO `cart_item`\n (customer_id, product_id, quantity)\n VALUES\n (12, 1, 4),\n (12, 2, 2),\n (34, 3, 3),\n (56, 1, 2),\n (56, 3, 1);\n ");
// just dump all data
echo new ResultWriter($pdo->query("\n SELECT *\n FROM\n cart_item\n LEFT JOIN\n product ON(product.id = product_id)\n LEFT JOIN\n customer ON(customer.id = customer_id)\n ")->getResult());
// how often were our products ordered?
echo new ResultWriter($pdo->query("\n SELECT\n product.name,\n SUM(cart_item.quantity)\n FROM\n product\n LEFT JOIN\n cart_item ON(id = product_id)\n GROUP BY\n product.name\n ")->getResult());
} catch (MalformedSql $exception) {
echo $exception;
public static function newFromPDO(PDO $pdo)
{
return new static($pdo->getDatabaseResource());
}
* Copyright (C) 2013 Gerrit Addiks.
* This package (including this file) was released under the terms of the GPL-3.0.
* You should have received a copy of the GNU General Public License along with this program.
* If not, see <http://www.gnu.org/licenses/> or send me a mail so i can send you a copy.
* @license GPL-3.0
* @author Gerrit Addiks <*****@*****.**>
* @package Addiks
*/
use Addiks\PHPSQL\PDO\PDO;
require_once dirname(__FILE__) . "/bootstrap.php";
if (isset($argv[1]) && $argv[1] === 'mysql') {
$dsn = "mysql:host=127.0.0.1;dbname=benchmark";
$pdo = new \PDO($dsn, "benchmark", "benchmark");
} else {
$dsn = "inmemory:benchmark";
$pdo = new PDO($dsn);
}
echo " - opened database '{$dsn}'.\n";
echo " - creating table.\n";
$pdo->query("\n CREATE TABLE benchmark_table (\n id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,\n foo VARCHAR(128),\n bar DECIMAL(5,3),\n baz DATETIME\n );\n");
$insertStatement = $pdo->prepare("\n INSERT INTO benchmark_table\n (foo, bar, baz)\n VALUES\n (?, ?, ?)\n");
$selectStatement = $pdo->prepare("SELECT * FROM benchmark_table WHERE id = ?");
$starttime = microtime(true);
srand(0);
$insertCount = 10000;
echo " - inserting {$insertCount} rows.\n";
for ($i = 1; $i <= $insertCount; $i++) {
$insertStatement->execute([md5($i) . md5($i . '-2'), rand(0, 10000000) * 0.001, date("Y-m-d H:i:s", rand(0, 2007583645))]);
if ($i % 1000 === 0) {
echo " - ({$i} / {$insertCount})\n";
}
/**
* Return an array of available PDO drivers
* @link http://www.php.net/manual/en/pdo.getavailabledrivers.php
* @return array PDO::getAvailableDrivers returns an array of PDO driver names. If
* no drivers are available, it returns an empty array.
*/
public static function getAvailableDrivers()
{
return array_merge(['internal', parent::getAvailableDrivers()]);
}