diff options
Diffstat (limited to 'libs/picodb/lib/PicoDb')
19 files changed, 3692 insertions, 0 deletions
diff --git a/libs/picodb/lib/PicoDb/Builder/BaseBuilder.php b/libs/picodb/lib/PicoDb/Builder/BaseBuilder.php new file mode 100644 index 00000000..e075ae3c --- /dev/null +++ b/libs/picodb/lib/PicoDb/Builder/BaseBuilder.php @@ -0,0 +1,86 @@ +<?php + +namespace PicoDb\Builder; + +use PicoDb\Database; + +/** + * Class InsertBuilder + * + * @package PicoDb\Builder + * @author Frederic Guillot + */ +abstract class BaseBuilder +{ + /** + * @var Database + */ + protected $db; + + /** + * @var ConditionBuilder + */ + protected $conditionBuilder; + + /** + * @var string + */ + protected $table = ''; + + /** + * @var string[] + */ + protected $columns = array(); + + /** + * InsertBuilder constructor + * + * @param Database $db + * @param ConditionBuilder $condition + */ + public function __construct(Database $db, ConditionBuilder $condition) + { + $this->db = $db; + $this->conditionBuilder = $condition; + } + + /** + * Get object instance + * + * @static + * @access public + * @param Database $db + * @param ConditionBuilder $condition + * @return static + */ + public static function getInstance(Database $db, ConditionBuilder $condition) + { + return new static($db, $condition); + } + + /** + * Set table name + * + * @access public + * @param string $table + * @return $this + */ + public function withTable($table) + { + $this->table = $table; + return $this; + } + + /** + * Set columns name + * + * @access public + * @param string[] $columns + * @return $this + */ + public function withColumns(array $columns) + { + $this->columns = $columns; + return $this; + } +} diff --git a/libs/picodb/lib/PicoDb/Builder/ConditionBuilder.php b/libs/picodb/lib/PicoDb/Builder/ConditionBuilder.php new file mode 100644 index 00000000..b0465b6e --- /dev/null +++ b/libs/picodb/lib/PicoDb/Builder/ConditionBuilder.php @@ -0,0 +1,377 @@ +<?php + +namespace PicoDb\Builder; + +use PicoDb\Database; +use PicoDb\Table; + +/** + * Handle SQL conditions + * + * @package PicoDb\Builder + * @author Frederic Guillot + */ +class ConditionBuilder +{ + /** + * Database instance + * + * @access private + * @var Database + */ + private $db; + + /** + * Condition values + * + * @access private + * @var array + */ + private $values = array(); + + /** + * SQL AND conditions + * + * @access private + * @var string[] + */ + private $conditions = array(); + + /** + * SQL OR conditions + * + * @access private + * @var OrConditionBuilder[] + */ + private $orConditions = array(); + + /** + * SQL condition offset + * + * @access private + * @var int + */ + private $orConditionOffset = 0; + + /** + * Constructor + * + * @access public + * @param Database $db + */ + public function __construct(Database $db) + { + $this->db = $db; + } + + /** + * Build the SQL condition + * + * @access public + * @return string + */ + public function build() + { + return empty($this->conditions) ? '' : ' WHERE '.implode(' AND ', $this->conditions); + } + + /** + * Get condition values + * + * @access public + * @return array + */ + public function getValues() + { + return $this->values; + } + + /** + * Returns true if there is some conditions + * + * @access public + * @return boolean + */ + public function hasCondition() + { + return ! empty($this->conditions); + } + + /** + * Add custom condition + * + * @access public + * @param string $sql + */ + public function addCondition($sql) + { + if ($this->orConditionOffset > 0) { + $this->orConditions[$this->orConditionOffset]->withCondition($sql); + } + else { + $this->conditions[] = $sql; + } + } + + /** + * Start OR condition + * + * @access public + */ + public function beginOr() + { + $this->orConditionOffset++; + $this->orConditions[$this->orConditionOffset] = new OrConditionBuilder(); + } + + /** + * Close OR condition + * + * @access public + */ + public function closeOr() + { + $condition = $this->orConditions[$this->orConditionOffset]->build(); + $this->orConditionOffset--; + + if ($this->orConditionOffset > 0) { + $this->orConditions[$this->orConditionOffset]->withCondition($condition); + } else { + $this->conditions[] = $condition; + } + } + + /** + * Equal condition + * + * @access public + * @param string $column + * @param mixed $value + */ + public function eq($column, $value) + { + $this->addCondition($this->db->escapeIdentifier($column).' = ?'); + $this->values[] = $value; + } + + /** + * Not equal condition + * + * @access public + * @param string $column + * @param mixed $value + */ + public function neq($column, $value) + { + $this->addCondition($this->db->escapeIdentifier($column).' != ?'); + $this->values[] = $value; + } + + /** + * IN condition + * + * @access public + * @param string $column + * @param array $values + */ + public function in($column, array $values) + { + if (! empty($values)) { + $this->addCondition($this->db->escapeIdentifier($column).' IN ('.implode(', ', array_fill(0, count($values), '?')).')'); + $this->values = array_merge($this->values, $values); + } + } + + /** + * IN condition with a subquery + * + * @access public + * @param string $column + * @param Table $subquery + */ + public function inSubquery($column, Table $subquery) + { + $this->addCondition($this->db->escapeIdentifier($column).' IN ('.$subquery->buildSelectQuery().')'); + $this->values = array_merge($this->values, $subquery->getConditionBuilder()->getValues()); + } + + /** + * NOT IN condition + * + * @access public + * @param string $column + * @param array $values + */ + public function notIn($column, array $values) + { + if (! empty($values)) { + $this->addCondition($this->db->escapeIdentifier($column).' NOT IN ('.implode(', ', array_fill(0, count($values), '?')).')'); + $this->values = array_merge($this->values, $values); + } + } + + /** + * NOT IN condition with a subquery + * + * @access public + * @param string $column + * @param Table $subquery + */ + public function notInSubquery($column, Table $subquery) + { + $this->addCondition($this->db->escapeIdentifier($column).' NOT IN ('.$subquery->buildSelectQuery().')'); + $this->values = array_merge($this->values, $subquery->getConditionBuilder()->getValues()); + } + + /** + * LIKE condition + * + * @access public + * @param string $column + * @param mixed $value + */ + public function like($column, $value) + { + $this->addCondition($this->db->escapeIdentifier($column).' '.$this->db->getDriver()->getOperator('LIKE').' ?'); + $this->values[] = $value; + } + + /** + * ILIKE condition + * + * @access public + * @param string $column + * @param mixed $value + */ + public function ilike($column, $value) + { + $this->addCondition($this->db->escapeIdentifier($column).' '.$this->db->getDriver()->getOperator('ILIKE').' ?'); + $this->values[] = $value; + } + + /** + * Greater than condition + * + * @access public + * @param string $column + * @param mixed $value + */ + public function gt($column, $value) + { + $this->addCondition($this->db->escapeIdentifier($column).' > ?'); + $this->values[] = $value; + } + + /** + * Greater than condition with subquery + * + * @access public + * @param string $column + * @param Table $subquery + */ + public function gtSubquery($column, Table $subquery) + { + $this->addCondition($this->db->escapeIdentifier($column).' > ('.$subquery->buildSelectQuery().')'); + $this->values = array_merge($this->values, $subquery->getConditionBuilder()->getValues()); + } + + /** + * Lower than condition + * + * @access public + * @param string $column + * @param mixed $value + */ + public function lt($column, $value) + { + $this->addCondition($this->db->escapeIdentifier($column).' < ?'); + $this->values[] = $value; + } + + /** + * Lower than condition with subquery + * + * @access public + * @param string $column + * @param Table $subquery + */ + public function ltSubquery($column, Table $subquery) + { + $this->addCondition($this->db->escapeIdentifier($column).' < ('.$subquery->buildSelectQuery().')'); + $this->values = array_merge($this->values, $subquery->getConditionBuilder()->getValues()); + } + + /** + * Greater than or equals condition + * + * @access public + * @param string $column + * @param mixed $value + */ + public function gte($column, $value) + { + $this->addCondition($this->db->escapeIdentifier($column).' >= ?'); + $this->values[] = $value; + } + + /** + * Greater than or equal condition with subquery + * + * @access public + * @param string $column + * @param Table $subquery + */ + public function gteSubquery($column, Table $subquery) + { + $this->addCondition($this->db->escapeIdentifier($column).' >= ('.$subquery->buildSelectQuery().')'); + $this->values = array_merge($this->values, $subquery->getConditionBuilder()->getValues()); + } + + /** + * Lower than or equals condition + * + * @access public + * @param string $column + * @param mixed $value + */ + public function lte($column, $value) + { + $this->addCondition($this->db->escapeIdentifier($column).' <= ?'); + $this->values[] = $value; + } + + /** + * Lower than or equal condition with subquery + * + * @access public + * @param string $column + * @param Table $subquery + */ + public function lteSubquery($column, Table $subquery) + { + $this->addCondition($this->db->escapeIdentifier($column).' <= ('.$subquery->buildSelectQuery().')'); + $this->values = array_merge($this->values, $subquery->getConditionBuilder()->getValues()); + } + + /** + * IS NULL condition + * + * @access public + * @param string $column + */ + public function isNull($column) + { + $this->addCondition($this->db->escapeIdentifier($column).' IS NULL'); + } + + /** + * IS NOT NULL condition + * + * @access public + * @param string $column + */ + public function notNull($column) + { + $this->addCondition($this->db->escapeIdentifier($column).' IS NOT NULL'); + } +} diff --git a/libs/picodb/lib/PicoDb/Builder/InsertBuilder.php b/libs/picodb/lib/PicoDb/Builder/InsertBuilder.php new file mode 100644 index 00000000..9d06c405 --- /dev/null +++ b/libs/picodb/lib/PicoDb/Builder/InsertBuilder.php @@ -0,0 +1,36 @@ +<?php + +namespace PicoDb\Builder; + +/** + * Class InsertBuilder + * + * @package PicoDb\Builder + * @author Frederic Guillot + */ +class InsertBuilder extends BaseBuilder +{ + /** + * Build SQL + * + * @access public + * @return string + */ + public function build() + { + $columns = array(); + $placeholders = array(); + + foreach ($this->columns as $column) { + $columns[] = $this->db->escapeIdentifier($column); + $placeholders[] = ':'.$column; + } + + return sprintf( + 'INSERT INTO %s (%s) VALUES (%s)', + $this->db->escapeIdentifier($this->table), + implode(', ', $columns), + implode(', ', $placeholders) + ); + } +} diff --git a/libs/picodb/lib/PicoDb/Builder/OrConditionBuilder.php b/libs/picodb/lib/PicoDb/Builder/OrConditionBuilder.php new file mode 100644 index 00000000..0defeaf4 --- /dev/null +++ b/libs/picodb/lib/PicoDb/Builder/OrConditionBuilder.php @@ -0,0 +1,43 @@ +<?php + +namespace PicoDb\Builder; + +/** + * Class OrConditionBuilder + * + * @package PicoDb\Builder + * @author Frederic Guillot + */ +class OrConditionBuilder +{ + /** + * List of SQL conditions + * + * @access protected + * @var string[] + */ + protected $conditions = array(); + + /** + * Add new condition + * + * @access public + * @param string $condition + * @return $this + */ + public function withCondition($condition) { + $this->conditions[] = $condition; + return $this; + } + + /** + * Build SQL + * + * @access public + * @return string + */ + public function build() + { + return '('.implode(' OR ', $this->conditions).')'; + } +} diff --git a/libs/picodb/lib/PicoDb/Builder/UpdateBuilder.php b/libs/picodb/lib/PicoDb/Builder/UpdateBuilder.php new file mode 100644 index 00000000..300ea9b0 --- /dev/null +++ b/libs/picodb/lib/PicoDb/Builder/UpdateBuilder.php @@ -0,0 +1,56 @@ +<?php + +namespace PicoDb\Builder; + +/** + * Class UpdateBuilder + * + * @package PicoDb\Builder + * @author Frederic Guillot + */ +class UpdateBuilder extends BaseBuilder +{ + /** + * @var string[] + */ + protected $sumColumns = array(); + + /** + * Set columns name + * + * @access public + * @param string[] $columns + * @return $this + */ + public function withSumColumns(array $columns) + { + $this->sumColumns = $columns; + return $this; + } + + /** + * Build SQL + * + * @access public + * @return string + */ + public function build() + { + $columns = array(); + + foreach ($this->columns as $column) { + $columns[] = $this->db->escapeIdentifier($column).'=?'; + } + + foreach ($this->sumColumns as $column) { + $columns[] = $this->db->escapeIdentifier($column).'='.$this->db->escapeIdentifier($column).' + ?'; + } + + return sprintf( + 'UPDATE %s SET %s %s', + $this->db->escapeIdentifier($this->table), + implode(', ', $columns), + $this->conditionBuilder->build() + ); + } +} diff --git a/libs/picodb/lib/PicoDb/Database.php b/libs/picodb/lib/PicoDb/Database.php new file mode 100644 index 00000000..22c9d2fb --- /dev/null +++ b/libs/picodb/lib/PicoDb/Database.php @@ -0,0 +1,370 @@ +<?php + +namespace PicoDb; + +use Closure; +use PDOException; +use LogicException; +use PicoDb\Driver\Mssql; +use PicoDb\Driver\Sqlite; +use PicoDb\Driver\Mysql; +use PicoDb\Driver\Postgres; + +/** + * Database + * + * @package PicoDb + * @author Frederic Guillot + */ +class Database +{ + /** + * Database instances + * + * @static + * @access private + * @var array + */ + private static $instances = array(); + + /** + * Statement object + * + * @access protected + * @var StatementHandler + */ + protected $statementHandler; + + /** + * Queries logs + * + * @access private + * @var array + */ + private $logs = array(); + + /** + * Driver instance + * + * @access private + */ + private $driver; + + /** + * Initialize the driver + * + * @access public + * @param array $settings + */ + public function __construct(array $settings = array()) + { + $this->driver = DriverFactory::getDriver($settings); + $this->statementHandler = new StatementHandler($this); + } + + /** + * Destructor + * + * @access public + */ + public function __destruct() + { + $this->closeConnection(); + } + + /** + * Register a new database instance + * + * @static + * @access public + * @param string $name Instance name + * @param Closure $callback Callback + */ + public static function setInstance($name, Closure $callback) + { + self::$instances[$name] = $callback; + } + + /** + * Get a database instance + * + * @static + * @access public + * @param string $name Instance name + * @return Database + */ + public static function getInstance($name) + { + if (! isset(self::$instances[$name])) { + throw new LogicException('No database instance created with that name'); + } + + if (is_callable(self::$instances[$name])) { + self::$instances[$name] = call_user_func(self::$instances[$name]); + } + + return self::$instances[$name]; + } + + /** + * Add a log message + * + * @access public + * @param mixed $message + * @return Database + */ + public function setLogMessage($message) + { + $this->logs[] = is_array($message) ? var_export($message, true) : $message; + return $this; + } + + /** + * Add many log messages + * + * @access public + * @param array $messages + * @return Database + */ + public function setLogMessages(array $messages) + { + foreach ($messages as $message) { + $this->setLogMessage($message); + } + + return $this; + } + + /** + * Get all queries logs + * + * @access public + * @return array + */ + public function getLogMessages() + { + return $this->logs; + } + + /** + * Get the PDO connection + * + * @access public + * @return \PDO + */ + public function getConnection() + { + return $this->driver->getConnection(); + } + + /** + * Get the Driver instance + * + * @access public + * @return Mssql|Sqlite|Postgres|Mysql + */ + public function getDriver() + { + return $this->driver; + } + + /** + * Get the last inserted id + * + * @access public + * @return integer + */ + public function getLastId() + { + return (int) $this->driver->getLastId(); + } + + /** + * Get statement object + * + * @access public + * @return StatementHandler + */ + public function getStatementHandler() + { + return $this->statementHandler; + } + + /** + * Release the PDO connection + * + * @access public + */ + public function closeConnection() + { + $this->driver->closeConnection(); + } + + /** + * Escape an identifier (column, table name...) + * + * @access public + * @param string $value Value + * @param string $table Table name + * @return string + */ + public function escapeIdentifier($value, $table = '') + { + // Do not escape custom query + if (strpos($value, '.') !== false || strpos($value, ' ') !== false) { + return $value; + } + + if (! empty($table)) { + return $this->driver->escape($table).'.'.$this->driver->escape($value); + } + + return $this->driver->escape($value); + } + + /** + * Escape an identifier list + * + * @access public + * @param array $identifiers List of identifiers + * @param string $table Table name + * @return string[] + */ + public function escapeIdentifierList(array $identifiers, $table = '') + { + foreach ($identifiers as $key => $value) { + $identifiers[$key] = $this->escapeIdentifier($value, $table); + } + + return $identifiers; + } + + /** + * Execute a prepared statement + * + * Note: returns false on duplicate keys instead of SQLException + * + * @access public + * @param string $sql SQL query + * @param array $values Values + * @return \PDOStatement|false + */ + public function execute($sql, array $values = array()) + { + return $this->statementHandler + ->withSql($sql) + ->withPositionalParams($values) + ->execute(); + } + + /** + * Run a transaction + * + * @access public + * @param Closure $callback Callback + * @return mixed + */ + public function transaction(Closure $callback) + { + try { + + $this->startTransaction(); + $result = $callback($this); + $this->closeTransaction(); + + return $result === null ? true : $result; + } catch (PDOException $e) { + return $this->statementHandler->handleSqlError($e); + } + } + + /** + * Begin a transaction + * + * @access public + */ + public function startTransaction() + { + if (! $this->getConnection()->inTransaction()) { + $this->getConnection()->beginTransaction(); + } + } + + /** + * Commit a transaction + * + * @access public + */ + public function closeTransaction() + { + if ($this->getConnection()->inTransaction()) { + $this->getConnection()->commit(); + } + } + + /** + * Rollback a transaction + * + * @access public + */ + public function cancelTransaction() + { + if ($this->getConnection()->inTransaction()) { + $this->getConnection()->rollBack(); + } + } + + /** + * Get a table object + * + * @access public + * @param string $table + * @return Table + */ + public function table($table) + { + return new Table($this, $table); + } + + /** + * Get a hashtable object + * + * @access public + * @param string $table + * @return Hashtable + */ + public function hashtable($table) + { + return new Hashtable($this, $table); + } + + /** + * Get a LOB object + * + * @access public + * @param string $table + * @return LargeObject + */ + public function largeObject($table) + { + return new LargeObject($this, $table); + } + + /** + * Get a schema object + * + * @access public + * @param string $namespace + * @return Schema + */ + public function schema($namespace = null) + { + $schema = new Schema($this); + + if ($namespace !== null) { + $schema->setNamespace($namespace); + } + + return $schema; + } +} diff --git a/libs/picodb/lib/PicoDb/Driver/Base.php b/libs/picodb/lib/PicoDb/Driver/Base.php new file mode 100644 index 00000000..790cd623 --- /dev/null +++ b/libs/picodb/lib/PicoDb/Driver/Base.php @@ -0,0 +1,234 @@ +<?php + +namespace PicoDb\Driver; + +use PDO; +use LogicException; +use PDOException; + +/** + * Base Driver class + * + * @package PicoDb\Driver + * @author Frederic Guillot + */ +abstract class Base +{ + /** + * List of required settings options + * + * @access protected + * @var array + */ + protected $requiredAttributes = array(); + + /** + * PDO connection + * + * @access protected + * @var PDO + */ + protected $pdo = null; + + /** + * Create a new PDO connection + * + * @abstract + * @access public + * @param array $settings + */ + abstract public function createConnection(array $settings); + + /** + * Enable foreign keys + * + * @abstract + * @access public + */ + abstract public function enableForeignKeys(); + + /** + * Disable foreign keys + * + * @abstract + * @access public + */ + abstract public function disableForeignKeys(); + + /** + * Return true if the error code is a duplicate key + * + * @abstract + * @access public + * @param integer $code + * @return boolean + */ + abstract public function isDuplicateKeyError($code); + + /** + * Escape identifier + * + * @abstract + * @access public + * @param string $identifier + * @return string + */ + abstract public function escape($identifier); + + /** + * Get non standard operator + * + * @abstract + * @access public + * @param string $operator + * @return string + */ + abstract public function getOperator($operator); + + /** + * Get last inserted id + * + * @abstract + * @access public + * @return integer + */ + abstract public function getLastId(); + + /** + * Get current schema version + * + * @abstract + * @access public + * @return integer + */ + abstract public function getSchemaVersion(); + + /** + * Set current schema version + * + * @abstract + * @access public + * @param integer $version + */ + abstract public function setSchemaVersion($version); + + /** + * Constructor + * + * @access public + * @param array $settings + */ + public function __construct(array $settings) + { + foreach ($this->requiredAttributes as $attribute) { + if (! isset($settings[$attribute])) { + throw new LogicException('This configuration parameter is missing: "'.$attribute.'"'); + } + } + + $this->createConnection($settings); + $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); + } + + /** + * Get the PDO connection + * + * @access public + * @return PDO + */ + public function getConnection() + { + return $this->pdo; + } + + /** + * Release the PDO connection + * + * @access public + */ + public function closeConnection() + { + $this->pdo = null; + } + + /** + * Upsert for a key/value variable + * + * @access public + * @param string $table + * @param string $keyColumn + * @param string $valueColumn + * @param array $dictionary + * @return bool False on failure + */ + public function upsert($table, $keyColumn, $valueColumn, array $dictionary) + { + try { + $this->pdo->beginTransaction(); + + foreach ($dictionary as $key => $value) { + + $rq = $this->pdo->prepare('SELECT 1 FROM '.$this->escape($table).' WHERE '.$this->escape($keyColumn).'=?'); + $rq->execute(array($key)); + + if ($rq->fetchColumn()) { + $rq = $this->pdo->prepare('UPDATE '.$this->escape($table).' SET '.$this->escape($valueColumn).'=? WHERE '.$this->escape($keyColumn).'=?'); + $rq->execute(array($value, $key)); + } + else { + $rq = $this->pdo->prepare('INSERT INTO '.$this->escape($table).' ('.$this->escape($keyColumn).', '.$this->escape($valueColumn).') VALUES (?, ?)'); + $rq->execute(array($key, $value)); + } + } + + $this->pdo->commit(); + + return true; + } + catch (PDOException $e) { + $this->pdo->rollBack(); + return false; + } + } + + /** + * Run EXPLAIN command + * + * @access public + * @param string $sql + * @param array $values + * @return array + */ + public function explain($sql, array $values) + { + return $this->getConnection()->query('EXPLAIN '.$this->getSqlFromPreparedStatement($sql, $values))->fetchAll(PDO::FETCH_ASSOC); + } + + /** + * Replace placeholder with values in prepared statement + * + * @access protected + * @param string $sql + * @param array $values + * @return string + */ + protected function getSqlFromPreparedStatement($sql, array $values) + { + foreach ($values as $value) { + $sql = substr_replace($sql, "'$value'", strpos($sql, '?'), 1); + } + + return $sql; + } + + /** + * Get database version + * + * @access public + * @return array + */ + public function getDatabaseVersion() + { + return $this->getConnection()->query('SELECT VERSION()')->fetchColumn(); + } +} diff --git a/libs/picodb/lib/PicoDb/Driver/Mssql.php b/libs/picodb/lib/PicoDb/Driver/Mssql.php new file mode 100644 index 00000000..83e75af2 --- /dev/null +++ b/libs/picodb/lib/PicoDb/Driver/Mssql.php @@ -0,0 +1,178 @@ +<?php + +namespace PicoDb\Driver; + +use PDO; + +/** + * Microsoft SQL Server Driver + * + * @package PicoDb\Driver + * @author Algy Taylor <thomas.taylor@cmft.nhs.uk> + */ +class Mssql extends Base +{ + /** + * List of required settings options + * + * @access protected + * @var array + */ + protected $requiredAttributes = array( + 'hostname', + 'username', + 'password', + 'database', + ); + + /** + * Table to store the schema version + * + * @access private + * @var array + */ + private $schemaTable = 'schema_version'; + + /** + * Create a new PDO connection + * + * @access public + * @param array $settings + */ + public function createConnection(array $settings) + { + $dsn = 'sqlsrv:Server=' . $settings['hostname'] . ';Database=' . $settings['database']; + + if (! empty($settings['port'])) { + $dsn .= ';port=' . $settings['port']; + } + + $this->pdo = new PDO($dsn, $settings['username'], $settings['password']); + + if (isset($settings['schema_table'])) { + $this->schemaTable = $settings['schema_table']; + } + } + + /** + * Enable foreign keys + * + * @access public + */ + public function enableForeignKeys() + { + $this->pdo->exec('EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"; GO;'); + } + + /** + * Disable foreign keys + * + * @access public + */ + public function disableForeignKeys() + { + $this->pdo->exec('EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"; GO;'); + } + + /** + * Return true if the error code is a duplicate key + * + * @access public + * @param integer $code + * @return boolean + */ + public function isDuplicateKeyError($code) + { + return $code == 2601; + } + + /** + * Escape identifier + * + * https://msdn.microsoft.com/en-us/library/ms175874.aspx + * + * @access public + * @param string $identifier + * @return string + */ + public function escape($identifier) + { + return '['.$identifier.']'; + } + + /** + * Get non standard operator + * + * @access public + * @param string $operator + * @return string + */ + public function getOperator($operator) + { + if ($operator === 'LIKE' || $operator === 'ILIKE') { + return 'LIKE'; + } + + return ''; + } + + /** + * Get last inserted id + * + * @access public + * @return integer + */ + public function getLastId() + { + return $this->pdo->lastInsertId(); + } + + /** + * Get current schema version + * + * @access public + * @return integer + */ + public function getSchemaVersion() + { + $this->pdo->exec("CREATE TABLE IF NOT EXISTS [".$this->schemaTable."] ([version] INT DEFAULT '0')"); + + $rq = $this->pdo->prepare('SELECT [version] FROM ['.$this->schemaTable.']'); + $rq->execute(); + $result = $rq->fetchColumn(); + + if ($result !== false) { + return (int) $result; + } + else { + $this->pdo->exec('INSERT INTO ['.$this->schemaTable.'] VALUES(0)'); + } + + return 0; + } + + /** + * Set current schema version + * + * @access public + * @param integer $version + */ + public function setSchemaVersion($version) + { + $rq = $this->pdo->prepare('UPDATE ['.$this->schemaTable.'] SET [version]=?'); + $rq->execute(array($version)); + } + + /** + * Run EXPLAIN command + * + * @param string $sql + * @param array $values + * @return array + */ + public function explain($sql, array $values) + { + $this->getConnection()->exec('SET SHOWPLAN_ALL ON'); + return $this->getConnection()->query($this->getSqlFromPreparedStatement($sql, $values))->fetchAll(PDO::FETCH_ASSOC); + } +} diff --git a/libs/picodb/lib/PicoDb/Driver/Mysql.php b/libs/picodb/lib/PicoDb/Driver/Mysql.php new file mode 100644 index 00000000..7e5cce0a --- /dev/null +++ b/libs/picodb/lib/PicoDb/Driver/Mysql.php @@ -0,0 +1,268 @@ +<?php + +namespace PicoDb\Driver; + +use PDO; +use PDOException; + +/** + * Mysql Driver + * + * @package PicoDb\Driver + * @author Frederic Guillot + */ +class Mysql extends Base +{ + /** + * List of required settings options + * + * @access protected + * @var array + */ + protected $requiredAttributes = array( + 'hostname', + 'username', + 'password', + 'database', + ); + + /** + * Table to store the schema version + * + * @access private + * @var array + */ + private $schemaTable = 'schema_version'; + + /** + * Create a new PDO connection + * + * @access public + * @param array $settings + */ + public function createConnection(array $settings) + { + $this->pdo = new PDO( + $this->buildDsn($settings), + $settings['username'], + $settings['password'], + $this->buildOptions($settings) + ); + + if (isset($settings['schema_table'])) { + $this->schemaTable = $settings['schema_table']; + } + } + + /** + * Build connection DSN + * + * @access protected + * @param array $settings + * @return string + */ + protected function buildDsn(array $settings) + { + $charset = empty($settings['charset']) ? 'utf8' : $settings['charset']; + $dsn = 'mysql:host='.$settings['hostname'].';dbname='.$settings['database'].';charset='.$charset; + + if (! empty($settings['port'])) { + $dsn .= ';port='.$settings['port']; + } + + return $dsn; + } + + /** + * Build connection options + * + * @access protected + * @param array $settings + * @return array + */ + protected function buildOptions(array $settings) + { + $options = array( + PDO::MYSQL_ATTR_INIT_COMMAND => 'SET sql_mode = STRICT_ALL_TABLES', + ); + + if (! empty($settings['ssl_key'])) { + $options[PDO::MYSQL_ATTR_SSL_KEY] = $settings['ssl_key']; + } + + if (! empty($settings['ssl_cert'])) { + $options[PDO::MYSQL_ATTR_SSL_CERT] = $settings['ssl_cert']; + } + + if (! empty($settings['ssl_ca'])) { + $options[PDO::MYSQL_ATTR_SSL_CA] = $settings['ssl_ca']; + } + + if (! empty($settings['persistent'])) { + $options[PDO::ATTR_PERSISTENT] = $settings['persistent']; + } + + if (! empty($settings['timeout'])) { + $options[PDO::ATTR_TIMEOUT] = $settings['timeout']; + } + + if (isset($settings['verify_server_cert'])) { + $options[PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT] = $settings['verify_server_cert']; + } + + if (! empty($settings['case'])) { + $options[PDO::ATTR_CASE] = $settings['case']; + } + + return $options; + } + + /** + * Enable foreign keys + * + * @access public + */ + public function enableForeignKeys() + { + $this->pdo->exec('SET FOREIGN_KEY_CHECKS=1'); + } + + /** + * Disable foreign keys + * + * @access public + */ + public function disableForeignKeys() + { + $this->pdo->exec('SET FOREIGN_KEY_CHECKS=0'); + } + + /** + * Return true if the error code is a duplicate key + * + * @access public + * @param integer $code + * @return boolean + */ + public function isDuplicateKeyError($code) + { + return $code == 23000; + } + + /** + * Escape identifier + * + * @access public + * @param string $identifier + * @return string + */ + public function escape($identifier) + { + return '`'.$identifier.'`'; + } + + /** + * Get non standard operator + * + * @access public + * @param string $operator + * @return string + */ + public function getOperator($operator) + { + if ($operator === 'LIKE') { + return 'LIKE BINARY'; + } + else if ($operator === 'ILIKE') { + return 'LIKE'; + } + + return ''; + } + + /** + * Get last inserted id + * + * @access public + * @return integer + */ + public function getLastId() + { + return $this->pdo->lastInsertId(); + } + + /** + * Get current schema version + * + * @access public + * @return integer + */ + public function getSchemaVersion() + { + $this->pdo->exec("CREATE TABLE IF NOT EXISTS `".$this->schemaTable."` (`version` INT DEFAULT '0') ENGINE=InnoDB CHARSET=utf8"); + + $rq = $this->pdo->prepare('SELECT `version` FROM `'.$this->schemaTable.'`'); + $rq->execute(); + $result = $rq->fetchColumn(); + + if ($result !== false) { + return (int) $result; + } + else { + $this->pdo->exec('INSERT INTO `'.$this->schemaTable.'` VALUES(0)'); + } + + return 0; + } + + /** + * Set current schema version + * + * @access public + * @param integer $version + */ + public function setSchemaVersion($version) + { + $rq = $this->pdo->prepare('UPDATE `'.$this->schemaTable.'` SET `version`=?'); + $rq->execute(array($version)); + } + + /** + * Upsert for a key/value variable + * + * @access public + * @param string $table + * @param string $keyColumn + * @param string $valueColumn + * @param array $dictionary + * @return bool False on failure + */ + public function upsert($table, $keyColumn, $valueColumn, array $dictionary) + { + try { + + $sql = sprintf( + 'REPLACE INTO %s (%s, %s) VALUES %s', + $this->escape($table), + $this->escape($keyColumn), + $this->escape($valueColumn), + implode(', ', array_fill(0, count($dictionary), '(?, ?)')) + ); + + $values = array(); + + foreach ($dictionary as $key => $value) { + $values[] = $key; + $values[] = $value; + } + + $rq = $this->pdo->prepare($sql); + $rq->execute($values); + + return true; + } + catch (PDOException $e) { + return false; + } + } +} diff --git a/libs/picodb/lib/PicoDb/Driver/Postgres.php b/libs/picodb/lib/PicoDb/Driver/Postgres.php new file mode 100644 index 00000000..86036839 --- /dev/null +++ b/libs/picodb/lib/PicoDb/Driver/Postgres.php @@ -0,0 +1,212 @@ +<?php + +namespace PicoDb\Driver; + +use PDO; +use PDOException; + +/** + * Postgres Driver + * + * @package PicoDb\Driver + * @author Frederic Guillot + */ +class Postgres extends Base +{ + /** + * List of required settings options + * + * @access protected + * @var array + */ + protected $requiredAttributes = array( + 'database', + ); + + /** + * Table to store the schema version + * + * @access private + * @var array + */ + private $schemaTable = 'schema_version'; + + /** + * Create a new PDO connection + * + * @access public + * @param array $settings + */ + public function createConnection(array $settings) + { + $dsn = 'pgsql:dbname='.$settings['database']; + $username = null; + $password = null; + $options = array(); + + if (! empty($settings['username'])) { + $username = $settings['username']; + } + + if (! empty($settings['password'])) { + $password = $settings['password']; + } + + if (! empty($settings['hostname'])) { + $dsn .= ';host='.$settings['hostname']; + } + + if (! empty($settings['port'])) { + $dsn .= ';port='.$settings['port']; + } + + if (! empty($settings['timeout'])) { + $options[PDO::ATTR_TIMEOUT] = $settings['timeout']; + } + + $this->pdo = new PDO($dsn, $username, $password, $options); + + if (isset($settings['schema_table'])) { + $this->schemaTable = $settings['schema_table']; + } + } + + /** + * Enable foreign keys + * + * @access public + */ + public function enableForeignKeys() + { + } + + /** + * Disable foreign keys + * + * @access public + */ + public function disableForeignKeys() + { + } + + /** + * Return true if the error code is a duplicate key + * + * @access public + * @param integer $code + * @return boolean + */ + public function isDuplicateKeyError($code) + { + return $code == 23505 || $code == 23503; + } + + /** + * Escape identifier + * + * @access public + * @param string $identifier + * @return string + */ + public function escape($identifier) + { + return '"'.$identifier.'"'; + } + + /** + * Get non standard operator + * + * @access public + * @param string $operator + * @return string + */ + public function getOperator($operator) + { + if ($operator === 'LIKE') { + return 'LIKE'; + } + else if ($operator === 'ILIKE') { + return 'ILIKE'; + } + + return ''; + } + + /** + * Get last inserted id + * + * @access public + * @return integer + */ + public function getLastId() + { + try { + $rq = $this->pdo->prepare('SELECT LASTVAL()'); + $rq->execute(); + + return $rq->fetchColumn(); + } + catch (PDOException $e) { + return 0; + } + } + + /** + * Get current schema version + * + * @access public + * @return integer + */ + public function getSchemaVersion() + { + $this->pdo->exec("CREATE TABLE IF NOT EXISTS ".$this->schemaTable." (version INTEGER DEFAULT 0)"); + + $rq = $this->pdo->prepare('SELECT "version" FROM "'.$this->schemaTable.'"'); + $rq->execute(); + $result = $rq->fetchColumn(); + + if ($result !== false) { + return (int) $result; + } + else { + $this->pdo->exec('INSERT INTO '.$this->schemaTable.' VALUES(0)'); + } + + return 0; + } + + /** + * Set current schema version + * + * @access public + * @param integer $version + */ + public function setSchemaVersion($version) + { + $rq = $this->pdo->prepare('UPDATE '.$this->schemaTable.' SET version=?'); + $rq->execute(array($version)); + } + + /** + * Run EXPLAIN command + * + * @param string $sql + * @param array $values + * @return array + */ + public function explain($sql, array $values) + { + return $this->getConnection()->query('EXPLAIN (FORMAT YAML) '.$this->getSqlFromPreparedStatement($sql, $values))->fetchAll(PDO::FETCH_ASSOC); + } + + /** + * Get database version + * + * @access public + * @return array + */ + public function getDatabaseVersion() + { + return $this->getConnection()->query('SHOW server_version')->fetchColumn(); + } +} diff --git a/libs/picodb/lib/PicoDb/Driver/Sqlite.php b/libs/picodb/lib/PicoDb/Driver/Sqlite.php new file mode 100644 index 00000000..0503d336 --- /dev/null +++ b/libs/picodb/lib/PicoDb/Driver/Sqlite.php @@ -0,0 +1,199 @@ +<?php + +namespace PicoDb\Driver; + +use PDO; +use PDOException; + +/** + * Sqlite Driver + * + * @package PicoDb\Driver + * @author Frederic Guillot + */ +class Sqlite extends Base +{ + /** + * List of required settings options + * + * @access protected + * @var array + */ + protected $requiredAttributes = array('filename'); + + /** + * Create a new PDO connection + * + * @access public + * @param array $settings + */ + public function createConnection(array $settings) + { + $options = array(); + + if (! empty($settings['timeout'])) { + $options[PDO::ATTR_TIMEOUT] = $settings['timeout']; + } + + $this->pdo = new PDO('sqlite:'.$settings['filename'], null, null, $options); + $this->enableForeignKeys(); + } + + /** + * Enable foreign keys + * + * @access public + */ + public function enableForeignKeys() + { + $this->pdo->exec('PRAGMA foreign_keys = ON'); + } + + /** + * Disable foreign keys + * + * @access public + */ + public function disableForeignKeys() + { + $this->pdo->exec('PRAGMA foreign_keys = OFF'); + } + + /** + * Return true if the error code is a duplicate key + * + * @access public + * @param integer $code + * @return boolean + */ + public function isDuplicateKeyError($code) + { + return $code == 23000; + } + + /** + * Escape identifier + * + * @access public + * @param string $identifier + * @return string + */ + public function escape($identifier) + { + return '"'.$identifier.'"'; + } + + /** + * Get non standard operator + * + * @access public + * @param string $operator + * @return string + */ + public function getOperator($operator) + { + if ($operator === 'LIKE' || $operator === 'ILIKE') { + return 'LIKE'; + } + + return ''; + } + + /** + * Get last inserted id + * + * @access public + * @return integer + */ + public function getLastId() + { + return $this->pdo->lastInsertId(); + } + + /** + * Get current schema version + * + * @access public + * @return integer + */ + public function getSchemaVersion() + { + $rq = $this->pdo->prepare('PRAGMA user_version'); + $rq->execute(); + + return (int) $rq->fetchColumn(); + } + + /** + * Set current schema version + * + * @access public + * @param integer $version + */ + public function setSchemaVersion($version) + { + $this->pdo->exec('PRAGMA user_version='.$version); + } + + /** + * Upsert for a key/value variable + * + * @access public + * @param string $table + * @param string $keyColumn + * @param string $valueColumn + * @param array $dictionary + * @return bool False on failure + */ + public function upsert($table, $keyColumn, $valueColumn, array $dictionary) + { + try { + $this->pdo->beginTransaction(); + + foreach ($dictionary as $key => $value) { + + $sql = sprintf( + 'INSERT OR REPLACE INTO %s (%s, %s) VALUES (?, ?)', + $this->escape($table), + $this->escape($keyColumn), + $this->escape($valueColumn) + ); + + $rq = $this->pdo->prepare($sql); + $rq->execute(array($key, $value)); + } + + $this->pdo->commit(); + + return true; + } + catch (PDOException $e) { + $this->pdo->rollBack(); + return false; + } + } + + /** + * Run EXPLAIN command + * + * @access public + * @param string $sql + * @param array $values + * @return array + */ + public function explain($sql, array $values) + { + return $this->getConnection()->query('EXPLAIN QUERY PLAN '.$this->getSqlFromPreparedStatement($sql, $values))->fetchAll(PDO::FETCH_ASSOC); + } + + /** + * Get database version + * + * @access public + * @return array + */ + public function getDatabaseVersion() + { + return $this->getConnection()->query('SELECT sqlite_version()')->fetchColumn(); + } +} diff --git a/libs/picodb/lib/PicoDb/DriverFactory.php b/libs/picodb/lib/PicoDb/DriverFactory.php new file mode 100644 index 00000000..13151ba7 --- /dev/null +++ b/libs/picodb/lib/PicoDb/DriverFactory.php @@ -0,0 +1,45 @@ +<?php + +namespace PicoDb; + +use LogicException; +use PicoDb\Driver\Mssql; +use PicoDb\Driver\Mysql; +use PicoDb\Driver\Postgres; +use PicoDb\Driver\Sqlite; + +/** + * Class DriverFactory + * + * @package PicoDb + * @author Frederic Guillot + */ +class DriverFactory +{ + /** + * Get database driver from settings or environment URL + * + * @access public + * @param array $settings + * @return Mssql|Mysql|Postgres|Sqlite + */ + public static function getDriver(array $settings) + { + if (! isset($settings['driver'])) { + throw new LogicException('You must define a database driver'); + } + + switch ($settings['driver']) { + case 'sqlite': + return new Sqlite($settings); + case 'mssql': + return new Mssql($settings); + case 'mysql': + return new Mysql($settings); + case 'postgres': + return new Postgres($settings); + default: + throw new LogicException('This database driver is not supported'); + } + } +} diff --git a/libs/picodb/lib/PicoDb/Hashtable.php b/libs/picodb/lib/PicoDb/Hashtable.php new file mode 100644 index 00000000..17afd0e6 --- /dev/null +++ b/libs/picodb/lib/PicoDb/Hashtable.php @@ -0,0 +1,112 @@ +<?php + +namespace PicoDb; + +use PDO; + +/** + * HashTable (key/value) + * + * @package PicoDb + * @author Frederic Guillot + * @author Mathias Kresin + */ +class Hashtable extends Table +{ + /** + * Column for the key + * + * @access private + * @var string + */ + private $keyColumn = 'key'; + + /** + * Column for the value + * + * @access private + * @var string + */ + private $valueColumn = 'value'; + + /** + * Set the key column + * + * @access public + * @param string $column + * @return $this + */ + public function columnKey($column) + { + $this->keyColumn = $column; + return $this; + } + + /** + * Set the value column + * + * @access public + * @param string $column + * @return $this + */ + public function columnValue($column) + { + $this->valueColumn = $column; + return $this; + } + + /** + * Insert or update + * + * @access public + * @param array $hashmap + * @return boolean + */ + public function put(array $hashmap) + { + return $this->db->getDriver()->upsert($this->getName(), $this->keyColumn, $this->valueColumn, $hashmap); + } + + /** + * Hashmap result [ [column1 => column2], [], ...] + * + * @access public + * @return array + */ + public function get() + { + $hashmap = array(); + + // setup where condition + if (func_num_args() > 0) { + $this->in($this->keyColumn, func_get_args()); + } + + // setup to select columns in case that there are more than two + $this->columns($this->keyColumn, $this->valueColumn); + + $rq = $this->db->execute($this->buildSelectQuery(), $this->conditionBuilder->getValues()); + $rows = $rq->fetchAll(PDO::FETCH_NUM); + + foreach ($rows as $row) { + $hashmap[$row[0]] = $row[1]; + } + + return $hashmap; + } + + /** + * Shortcut method to get a hashmap result + * + * @access public + * @param string $key Key column + * @param string $value Value column + * @return array + */ + public function getAll($key, $value) + { + $this->keyColumn = $key; + $this->valueColumn = $value; + return $this->get(); + } +} diff --git a/libs/picodb/lib/PicoDb/LargeObject.php b/libs/picodb/lib/PicoDb/LargeObject.php new file mode 100644 index 00000000..ba5e3b92 --- /dev/null +++ b/libs/picodb/lib/PicoDb/LargeObject.php @@ -0,0 +1,167 @@ +<?php + +namespace PicoDb; + +use PDO; +use PicoDb\Builder\InsertBuilder; +use PicoDb\Builder\UpdateBuilder; + +/** + * Handle Large Objects (LOBs) + * + * @package PicoDb + * @author Frederic Guillot + */ +class LargeObject extends Table +{ + /** + * Fetch large object as file descriptor + * + * This method is not compatible with Sqlite and Mysql (return a string instead of resource) + * + * @access public + * @param string $column + * @return resource + */ + public function findOneColumnAsStream($column) + { + $this->limit(1); + $this->columns($column); + + $rq = $this->db->getStatementHandler() + ->withSql($this->buildSelectQuery()) + ->withPositionalParams($this->conditionBuilder->getValues()) + ->execute(); + + $rq->bindColumn($column, $fd, PDO::PARAM_LOB); + $rq->fetch(PDO::FETCH_BOUND); + + return $fd; + } + + /** + * Fetch large object as string + * + * @access public + * @param string $column + * @return string + */ + public function findOneColumnAsString($column) + { + $fd = $this->findOneColumnAsStream($column); + + if (is_string($fd)) { + return $fd; + } + + return stream_get_contents($fd); + } + + /** + * Insert large object from stream + * + * @access public + * @param string $blobColumn + * @param resource|string $blobDescriptor + * @param array $data + * @return bool + */ + public function insertFromStream($blobColumn, &$blobDescriptor, array $data = array()) + { + $columns = array_merge(array($blobColumn), array_keys($data)); + $this->db->startTransaction(); + + $result = $this->db->getStatementHandler() + ->withSql(InsertBuilder::getInstance($this->db, $this->conditionBuilder) + ->withTable($this->name) + ->withColumns($columns) + ->build() + ) + ->withNamedParams($data) + ->withLobParam($blobColumn, $blobDescriptor) + ->execute(); + + $this->db->closeTransaction(); + + return $result !== false; + } + + /** + * Insert large object from file + * + * @access public + * @param string $blobColumn + * @param string $filename + * @param array $data + * @return bool + */ + public function insertFromFile($blobColumn, $filename, array $data = array()) + { + $fp = fopen($filename, 'rb'); + $result = $this->insertFromStream($blobColumn, $fp, $data); + fclose($fp); + return $result; + } + + /** + * Insert large object from string + * + * @access public + * @param string $blobColumn + * @param string $blobData + * @param array $data + * @return bool + */ + public function insertFromString($blobColumn, &$blobData, array $data = array()) + { + return $this->insertFromStream($blobColumn, $blobData, $data); + } + + /** + * Update large object from stream + * + * @access public + * @param string $blobColumn + * @param resource $blobDescriptor + * @param array $data + * @return bool + */ + public function updateFromStream($blobColumn, &$blobDescriptor, array $data = array()) + { + $values = array_merge(array_values($data), $this->conditionBuilder->getValues()); + $columns = array_merge(array($blobColumn), array_keys($data)); + + $this->db->startTransaction(); + + $result = $this->db->getStatementHandler() + ->withSql(UpdateBuilder::getInstance($this->db, $this->conditionBuilder) + ->withTable($this->name) + ->withColumns($columns) + ->build() + ) + ->withPositionalParams($values) + ->withLobParam($blobColumn, $blobDescriptor) + ->execute(); + + $this->db->closeTransaction(); + + return $result !== false; + } + + /** + * Update large object from file + * + * @access public + * @param string $blobColumn + * @param string $filename + * @param array $data + * @return bool + */ + public function updateFromFile($blobColumn, $filename, array $data = array()) + { + $fp = fopen($filename, 'r'); + $result = $this->updateFromStream($blobColumn, $fp, $data); + fclose($fp); + return $result; + } +} diff --git a/libs/picodb/lib/PicoDb/SQLException.php b/libs/picodb/lib/PicoDb/SQLException.php new file mode 100644 index 00000000..7e570834 --- /dev/null +++ b/libs/picodb/lib/PicoDb/SQLException.php @@ -0,0 +1,15 @@ +<?php + +namespace PicoDb; + +use Exception; + +/** + * SQLException + * + * @package PicoDb + * @author Frederic Guillot + */ +class SQLException extends Exception +{ +} diff --git a/libs/picodb/lib/PicoDb/Schema.php b/libs/picodb/lib/PicoDb/Schema.php new file mode 100644 index 00000000..a0280368 --- /dev/null +++ b/libs/picodb/lib/PicoDb/Schema.php @@ -0,0 +1,119 @@ +<?php + +namespace PicoDb; + +use PDOException; + +/** + * Schema migration class + * + * @package PicoDb + * @author Frederic Guillot + */ +class Schema +{ + /** + * Database instance + * + * @access protected + * @var Database + */ + protected $db = null; + + /** + * Schema namespace + * + * @access protected + * @var string + */ + protected $namespace = '\Schema'; + + /** + * Constructor + * + * @access public + * @param Database $db + */ + public function __construct(Database $db) + { + $this->db = $db; + } + + /** + * Set another namespace + * + * @access public + * @param string $namespace + * @return Schema + */ + public function setNamespace($namespace) + { + $this->namespace = $namespace; + return $this; + } + + /** + * Get schema namespace + * + * @access public + * @return string + */ + public function getNamespace() + { + return $this->namespace; + } + + /** + * Check the schema version and run the migrations + * + * @access public + * @param integer $last_version + * @return boolean + */ + public function check($last_version = 1) + { + $current_version = $this->db->getDriver()->getSchemaVersion(); + + if ($current_version < $last_version) { + return $this->migrateTo($current_version, $last_version); + } + + return true; + } + + /** + * Migrate the schema to one version to another + * + * @access public + * @param integer $current_version + * @param integer $next_version + * @return boolean + */ + public function migrateTo($current_version, $next_version) + { + try { + for ($i = $current_version + 1; $i <= $next_version; $i++) { + $this->db->startTransaction(); + $this->db->getDriver()->disableForeignKeys(); + + $function_name = $this->getNamespace().'\version_'.$i; + + if (function_exists($function_name)) { + $this->db->setLogMessage('Running migration '.$function_name); + call_user_func($function_name, $this->db->getConnection()); + } + + $this->db->getDriver()->setSchemaVersion($i); + $this->db->getDriver()->enableForeignKeys(); + $this->db->closeTransaction(); + } + } catch (PDOException $e) { + $this->db->setLogMessage($e->getMessage()); + $this->db->cancelTransaction(); + $this->db->getDriver()->enableForeignKeys(); + return false; + } + + return true; + } +} diff --git a/libs/picodb/lib/PicoDb/StatementHandler.php b/libs/picodb/lib/PicoDb/StatementHandler.php new file mode 100644 index 00000000..d0cdaa49 --- /dev/null +++ b/libs/picodb/lib/PicoDb/StatementHandler.php @@ -0,0 +1,353 @@ +<?php + +namespace PicoDb; + +use PDO; +use PDOException; +use PDOStatement; + +/** + * Statement Handler + * + * @package PicoDb + * @author Frederic Guillot + */ +class StatementHandler +{ + /** + * Database instance + * + * @access protected + * @var Database + */ + protected $db = null; + + /** + * Flag to calculate query time + * + * @access protected + * @var boolean + */ + protected $stopwatch = false; + + /** + * Start time + * + * @access protected + * @var float + */ + protected $startTime = 0; + + /** + * Execution time of all queries + * + * @access protected + * @var float + */ + protected $executionTime = 0; + + /** + * Flag to log generated SQL queries + * + * @access protected + * @var boolean + */ + protected $logQueries = false; + + /** + * Run explain command on each query + * + * @access protected + * @var boolean + */ + protected $explain = false; + + /** + * Number of SQL queries executed + * + * @access protected + * @var integer + */ + protected $nbQueries = 0; + + /** + * SQL query + * + * @access protected + * @var string + */ + protected $sql = ''; + + /** + * Positional SQL parameters + * + * @access protected + * @var array + */ + protected $positionalParams = array(); + + /** + * Named SQL parameters + * + * @access protected + * @var array + */ + protected $namedParams = array(); + + /** + * Flag to use named params + * + * @access protected + * @var boolean + */ + protected $useNamedParams = false; + + /** + * LOB params + * + * @access protected + * @var array + */ + protected $lobParams = array(); + + /** + * Constructor + * + * @access public + * @param Database $db + */ + public function __construct(Database $db) + { + $this->db = $db; + } + + /** + * Enable query logging + * + * @access public + * @return $this + */ + public function withLogging() + { + $this->logQueries = true; + return $this; + } + + /** + * Record query execution time + * + * @access public + * @return $this + */ + public function withStopWatch() + { + $this->stopwatch = true; + return $this; + } + + /** + * Execute explain command on query + * + * @access public + * @return $this + */ + public function withExplain() + { + $this->explain = true; + return $this; + } + + /** + * Set SQL query + * + * @access public + * @param string $sql + * @return $this + */ + public function withSql($sql) + { + $this->sql = $sql; + return $this; + } + + /** + * Set positional parameters + * + * @access public + * @param array $params + * @return $this + */ + public function withPositionalParams(array $params) + { + $this->positionalParams = $params; + return $this; + } + + /** + * Set named parameters + * + * @access public + * @param array $params + * @return $this + */ + public function withNamedParams(array $params) + { + $this->namedParams = $params; + $this->useNamedParams = true; + return $this; + } + + /** + * Bind large object parameter + * + * @access public + * @param $name + * @param $fp + * @return $this + */ + public function withLobParam($name, &$fp) + { + $this->lobParams[$name] =& $fp; + return $this; + } + + /** + * Get number of queries executed + * + * @access public + * @return int + */ + public function getNbQueries() + { + return $this->nbQueries; + } + + /** + * Execute a prepared statement + * + * Note: returns false on duplicate keys instead of SQLException + * + * @access public + * @return PDOStatement|false + */ + public function execute() + { + try { + $this->beforeExecute(); + + $pdoStatement = $this->db->getConnection()->prepare($this->sql); + $this->bindParams($pdoStatement); + $pdoStatement->execute(); + + $this->afterExecute(); + return $pdoStatement; + } catch (PDOException $e) { + return $this->handleSqlError($e); + } + } + + /** + * Bind parameters to PDOStatement + * + * @access protected + * @param PDOStatement $pdoStatement + */ + protected function bindParams(PDOStatement $pdoStatement) + { + $i = 1; + + foreach ($this->lobParams as $name => $variable) { + if (! $this->useNamedParams) { + $parameter = $i; + $i++; + } else { + $parameter = $name; + } + + $pdoStatement->bindParam($parameter, $variable, PDO::PARAM_LOB); + } + + foreach ($this->positionalParams as $value) { + $pdoStatement->bindValue($i, $value, PDO::PARAM_STR); + $i++; + } + + foreach ($this->namedParams as $name => $value) { + $pdoStatement->bindValue($name, $value, PDO::PARAM_STR); + } + } + + /** + * Method executed before query execution + * + * @access protected + */ + protected function beforeExecute() + { + if ($this->logQueries) { + $this->db->setLogMessage($this->sql); + } + + if ($this->stopwatch) { + $this->startTime = microtime(true); + } + } + + /** + * Method executed after query execution + * + * @access protected + */ + protected function afterExecute() + { + if ($this->stopwatch) { + $duration = microtime(true) - $this->startTime; + $this->executionTime += $duration; + $this->db->setLogMessage('query_duration='.$duration); + $this->db->setLogMessage('total_execution_time='.$this->executionTime); + } + + if ($this->explain) { + $this->db->setLogMessages($this->db->getDriver()->explain($this->sql, $this->positionalParams)); + } + + $this->nbQueries++; + $this->cleanup(); + } + + /** + * Reset internal properties after execution + * The same object instance is used + * + * @access protected + */ + protected function cleanup() + { + $this->sql = ''; + $this->useNamedParams = false; + $this->positionalParams = array(); + $this->namedParams = array(); + $this->lobParams = array(); + } + + /** + * Handle PDOException + * + * @access public + * @param PDOException $e + * @return bool + * @throws SQLException + */ + public function handleSqlError(PDOException $e) + { + $this->cleanup(); + $this->db->cancelTransaction(); + $this->db->setLogMessage($e->getMessage()); + + if ($this->db->getDriver()->isDuplicateKeyError($e->getCode())) { + return false; + } + + throw new SQLException('SQL Error: '.$e->getMessage()); + } +} diff --git a/libs/picodb/lib/PicoDb/Table.php b/libs/picodb/lib/PicoDb/Table.php new file mode 100644 index 00000000..404b5cbe --- /dev/null +++ b/libs/picodb/lib/PicoDb/Table.php @@ -0,0 +1,729 @@ +<?php + +namespace PicoDb; + +use PDO; +use Closure; +use PicoDb\Builder\ConditionBuilder; +use PicoDb\Builder\InsertBuilder; +use PicoDb\Builder\UpdateBuilder; + +/** + * Table + * + * @package PicoDb + * @author Frederic Guillot + * + * @method $this addCondition($sql) + * @method $this beginOr() + * @method $this closeOr() + * @method $this eq($column, $value) + * @method $this neq($column, $value) + * @method $this in($column, array $values) + * @method $this inSubquery($column, Table $subquery) + * @method $this notIn($column, array $values) + * @method $this notInSubquery($column, Table $subquery) + * @method $this like($column, $value) + * @method $this ilike($column, $value) + * @method $this gt($column, $value) + * @method $this gtSubquery($column, Table $subquery) + * @method $this lt($column, $value) + * @method $this ltSubquery($column, Table $subquery) + * @method $this gte($column, $value) + * @method $this gteSubquery($column, Table $subquery) + * @method $this lte($column, $value) + * @method $this lteSubquery($column, Table $subquery) + * @method $this isNull($column) + * @method $this notNull($column) + */ +class Table +{ + /** + * Sorting direction + * + * @access public + * @var string + */ + const SORT_ASC = 'ASC'; + const SORT_DESC = 'DESC'; + + /** + * Condition instance + * + * @access protected + * @var ConditionBuilder + */ + protected $conditionBuilder; + + /** + * Database instance + * + * @access protected + * @var Database + */ + protected $db; + + /** + * Table name + * + * @access protected + * @var string + */ + protected $name = ''; + + /** + * Columns list for SELECT query + * + * @access private + * @var array + */ + private $columns = array(); + + /** + * Columns to sum during update + * + * @access private + * @var array + */ + private $sumColumns = array(); + + /** + * SQL limit + * + * @access private + * @var string + */ + private $sqlLimit = ''; + + /** + * SQL offset + * + * @access private + * @var string + */ + private $sqlOffset = ''; + + /** + * SQL order + * + * @access private + * @var string + */ + private $sqlOrder = ''; + + /** + * SQL custom SELECT value + * + * @access private + * @var string + */ + private $sqlSelect = ''; + + /** + * SQL joins + * + * @access private + * @var array + */ + private $joins = array(); + + /** + * Use DISTINCT or not? + * + * @access private + * @var boolean + */ + private $distinct = false; + + /** + * Group by those columns + * + * @access private + * @var array + */ + private $groupBy = array(); + + /** + * Callback for result filtering + * + * @access private + * @var Closure + */ + private $callback = null; + + /** + * Constructor + * + * @access public + * @param Database $db + * @param string $name + */ + public function __construct(Database $db, $name) + { + $this->db = $db; + $this->name = $name; + $this->conditionBuilder = new ConditionBuilder($db); + } + + /** + * Return the table name + * + * @access public + * @return string + */ + public function getName() + { + return $this->name; + } + + /** + * Return ConditionBuilder object + * + * @access public + * @return ConditionBuilder + */ + public function getConditionBuilder() + { + return $this->conditionBuilder; + } + + /** + * Insert or update + * + * @access public + * @param array $data + * @return boolean + */ + public function save(array $data) + { + return $this->conditionBuilder->hasCondition() ? $this->update($data) : $this->insert($data); + } + + /** + * Update + * + * @access public + * @param array $data + * @return boolean + */ + public function update(array $data = array()) + { + $values = array_merge(array_values($data), array_values($this->sumColumns), $this->conditionBuilder->getValues()); + $sql = UpdateBuilder::getInstance($this->db, $this->conditionBuilder) + ->withTable($this->name) + ->withColumns(array_keys($data)) + ->withSumColumns(array_keys($this->sumColumns)) + ->build(); + + return $this->db->execute($sql, $values) !== false; + } + + /** + * Insert + * + * @access public + * @param array $data + * @return boolean + */ + public function insert(array $data) + { + return $this->db->getStatementHandler() + ->withSql(InsertBuilder::getInstance($this->db, $this->conditionBuilder) + ->withTable($this->name) + ->withColumns(array_keys($data)) + ->build() + ) + ->withNamedParams($data) + ->execute() !== false; + } + + /** + * Insert a new row and return the ID of the primary key + * + * @access public + * @param array $data + * @return bool|int + */ + public function persist(array $data) + { + if ($this->insert($data)) { + return $this->db->getLastId(); + } + + return false; + } + + /** + * Remove + * + * @access public + * @return boolean + */ + public function remove() + { + $sql = sprintf( + 'DELETE FROM %s %s', + $this->db->escapeIdentifier($this->name), + $this->conditionBuilder->build() + ); + + $result = $this->db->execute($sql, $this->conditionBuilder->getValues()); + return $result->rowCount() > 0; + } + + /** + * Fetch all rows + * + * @access public + * @return array + */ + public function findAll() + { + $rq = $this->db->execute($this->buildSelectQuery(), $this->conditionBuilder->getValues()); + $results = $rq->fetchAll(PDO::FETCH_ASSOC); + + if (is_callable($this->callback) && ! empty($results)) { + return call_user_func($this->callback, $results); + } + + return $results; + } + + /** + * Find all with a single column + * + * @access public + * @param string $column + * @return mixed + */ + public function findAllByColumn($column) + { + $this->columns = array($column); + $rq = $this->db->execute($this->buildSelectQuery(), $this->conditionBuilder->getValues()); + + return $rq->fetchAll(PDO::FETCH_COLUMN, 0); + } + + /** + * Fetch one row + * + * @access public + * @return array|null + */ + public function findOne() + { + $this->limit(1); + $result = $this->findAll(); + + return isset($result[0]) ? $result[0] : null; + } + + /** + * Fetch one column, first row + * + * @access public + * @param string $column + * @return string + */ + public function findOneColumn($column) + { + $this->limit(1); + $this->columns = array($column); + + return $this->db->execute($this->buildSelectQuery(), $this->conditionBuilder->getValues())->fetchColumn(); + } + + /** + * Build a subquery with an alias + * + * @access public + * @param string $sql + * @param string $alias + * @return $this + */ + public function subquery($sql, $alias) + { + $this->columns[] = '('.$sql.') AS '.$this->db->escapeIdentifier($alias); + return $this; + } + + /** + * Exists + * + * @access public + * @return integer + */ + public function exists() + { + $sql = sprintf( + 'SELECT 1 FROM %s '.implode(' ', $this->joins).$this->conditionBuilder->build(), + $this->db->escapeIdentifier($this->name) + ); + + $rq = $this->db->execute($sql, $this->conditionBuilder->getValues()); + $result = $rq->fetchColumn(); + + return $result ? true : false; + } + + /** + * Count + * + * @access public + * @return integer + */ + public function count() + { + $sql = sprintf( + 'SELECT COUNT(*) FROM %s '.implode(' ', $this->joins).$this->conditionBuilder->build().$this->sqlOrder.$this->sqlLimit.$this->sqlOffset, + $this->db->escapeIdentifier($this->name) + ); + + $rq = $this->db->execute($sql, $this->conditionBuilder->getValues()); + $result = $rq->fetchColumn(); + + return $result ? (int) $result : 0; + } + + /** + * Sum + * + * @access public + * @param string $column + * @return float + */ + public function sum($column) + { + $sql = sprintf( + 'SELECT SUM(%s) FROM %s '.implode(' ', $this->joins).$this->conditionBuilder->build().$this->sqlOrder.$this->sqlLimit.$this->sqlOffset, + $this->db->escapeIdentifier($column), + $this->db->escapeIdentifier($this->name) + ); + + $rq = $this->db->execute($sql, $this->conditionBuilder->getValues()); + $result = $rq->fetchColumn(); + + return $result ? (float) $result : 0; + } + + /** + * Increment column value + * + * @access public + * @param string $column + * @param string $value + * @return boolean + */ + public function increment($column, $value) + { + $sql = sprintf( + 'UPDATE %s SET %s=%s+%d '.$this->conditionBuilder->build(), + $this->db->escapeIdentifier($this->name), + $this->db->escapeIdentifier($column), + $this->db->escapeIdentifier($column), + $value + ); + + return $this->db->execute($sql, $this->conditionBuilder->getValues()) !== false; + } + + /** + * Decrement column value + * + * @access public + * @param string $column + * @param string $value + * @return boolean + */ + public function decrement($column, $value) + { + $sql = sprintf( + 'UPDATE %s SET %s=%s-%d '.$this->conditionBuilder->build(), + $this->db->escapeIdentifier($this->name), + $this->db->escapeIdentifier($column), + $this->db->escapeIdentifier($column), + $value + ); + + return $this->db->execute($sql, $this->conditionBuilder->getValues()) !== false; + } + + /** + * Left join + * + * @access public + * @param string $table Join table + * @param string $foreign_column Foreign key on the join table + * @param string $local_column Local column + * @param string $local_table Local table + * @param string $alias Join table alias + * @return $this + */ + public function join($table, $foreign_column, $local_column, $local_table = '', $alias = '') + { + $this->joins[] = sprintf( + 'LEFT JOIN %s ON %s=%s', + $this->db->escapeIdentifier($table), + $this->db->escapeIdentifier($alias ?: $table).'.'.$this->db->escapeIdentifier($foreign_column), + $this->db->escapeIdentifier($local_table ?: $this->name).'.'.$this->db->escapeIdentifier($local_column) + ); + + return $this; + } + + /** + * Left join + * + * @access public + * @param string $table1 + * @param string $alias1 + * @param string $column1 + * @param string $table2 + * @param string $column2 + * @return $this + */ + public function left($table1, $alias1, $column1, $table2, $column2) + { + $this->joins[] = sprintf( + 'LEFT JOIN %s AS %s ON %s=%s', + $this->db->escapeIdentifier($table1), + $this->db->escapeIdentifier($alias1), + $this->db->escapeIdentifier($alias1).'.'.$this->db->escapeIdentifier($column1), + $this->db->escapeIdentifier($table2).'.'.$this->db->escapeIdentifier($column2) + ); + + return $this; + } + + /** + * Inner join + * + * @access public + * @param string $table1 + * @param string $alias1 + * @param string $column1 + * @param string $table2 + * @param string $column2 + * @return $this + */ + public function inner($table1, $alias1, $column1, $table2, $column2) + { + $this->joins[] = sprintf( + 'JOIN %s AS %s ON %s=%s', + $this->db->escapeIdentifier($table1), + $this->db->escapeIdentifier($alias1), + $this->db->escapeIdentifier($alias1).'.'.$this->db->escapeIdentifier($column1), + $this->db->escapeIdentifier($table2).'.'.$this->db->escapeIdentifier($column2) + ); + + return $this; + } + + /** + * Order by + * + * @access public + * @param string $column Column name + * @param string $order Direction ASC or DESC + * @return $this + */ + public function orderBy($column, $order = self::SORT_ASC) + { + $order = strtoupper($order); + $order = $order === self::SORT_ASC || $order === self::SORT_DESC ? $order : self::SORT_ASC; + + if ($this->sqlOrder === '') { + $this->sqlOrder = ' ORDER BY '.$this->db->escapeIdentifier($column).' '.$order; + } + else { + $this->sqlOrder .= ', '.$this->db->escapeIdentifier($column).' '.$order; + } + + return $this; + } + + /** + * Ascending sort + * + * @access public + * @param string $column + * @return $this + */ + public function asc($column) + { + $this->orderBy($column, self::SORT_ASC); + return $this; + } + + /** + * Descending sort + * + * @access public + * @param string $column + * @return $this + */ + public function desc($column) + { + $this->orderBy($column, self::SORT_DESC); + return $this; + } + + /** + * Limit + * + * @access public + * @param integer $value + * @return $this + */ + public function limit($value) + { + if (! is_null($value)) { + $this->sqlLimit = ' LIMIT '.(int) $value; + } + + return $this; + } + + /** + * Offset + * + * @access public + * @param integer $value + * @return $this + */ + public function offset($value) + { + if (! is_null($value)) { + $this->sqlOffset = ' OFFSET '.(int) $value; + } + + return $this; + } + + /** + * Group by + * + * @access public + * @return $this + */ + public function groupBy() + { + $this->groupBy = func_get_args(); + return $this; + } + + /** + * Custom select + * + * @access public + * @param string $select + * @return $this + */ + public function select($select) + { + $this->sqlSelect = $select; + return $this; + } + + /** + * Define the columns for the select + * + * @access public + * @return $this + */ + public function columns() + { + $this->columns = func_get_args(); + return $this; + } + + /** + * Sum column + * + * @access public + * @param string $column + * @param mixed $value + * @return $this + */ + public function sumColumn($column, $value) + { + $this->sumColumns[$column] = $value; + return $this; + } + + /** + * Distinct + * + * @access public + * @return $this + */ + public function distinct() + { + $this->columns = func_get_args(); + $this->distinct = true; + return $this; + } + + /** + * Add callback to alter the resultset + * + * @access public + * @param Closure|array $callback + * @return $this + */ + public function callback($callback) + { + $this->callback = $callback; + return $this; + } + + /** + * Build a select query + * + * @access public + * @return string + */ + public function buildSelectQuery() + { + if (empty($this->sqlSelect)) { + $this->columns = $this->db->escapeIdentifierList($this->columns); + $this->sqlSelect = ($this->distinct ? 'DISTINCT ' : '').(empty($this->columns) ? '*' : implode(', ', $this->columns)); + } + + $this->groupBy = $this->db->escapeIdentifierList($this->groupBy); + + return trim(sprintf( + 'SELECT %s FROM %s %s %s %s %s %s %s', + $this->sqlSelect, + $this->db->escapeIdentifier($this->name), + implode(' ', $this->joins), + $this->conditionBuilder->build(), + empty($this->groupBy) ? '' : 'GROUP BY '.implode(', ', $this->groupBy), + $this->sqlOrder, + $this->sqlLimit, + $this->sqlOffset + )); + } + + /** + * Magic method for sql conditions + * + * @access public + * @param string $name + * @param array $arguments + * @return $this + */ + public function __call($name, array $arguments) + { + call_user_func_array(array($this->conditionBuilder, $name), $arguments); + return $this; + } + + /** + * Clone function ensures that cloned objects are really clones + */ + public function __clone() + { + $this->conditionBuilder = clone $this->conditionBuilder; + } +} diff --git a/libs/picodb/lib/PicoDb/UrlParser.php b/libs/picodb/lib/PicoDb/UrlParser.php new file mode 100644 index 00000000..d8fcaf00 --- /dev/null +++ b/libs/picodb/lib/PicoDb/UrlParser.php @@ -0,0 +1,93 @@ +<?php + +namespace PicoDb; + +/** + * Class UrlParser + * + * @package PicoDb + * @author Frederic Guillot + */ +class UrlParser +{ + /** + * URL + * + * @access private + * @var string + */ + private $url; + + /** + * Constructor + * + * @access public + * @param string $environmentVariable + */ + public function __construct($environmentVariable = 'DATABASE_URL') + { + $this->url = getenv($environmentVariable); + } + + /** + * Get object instance + * + * @access public + * @param string $environmentVariable + * @return static + */ + public static function getInstance($environmentVariable = 'DATABASE_URL') + { + return new static($environmentVariable); + } + + /** + * Return true if the variable is defined + * + * @access public + * @return bool + */ + public function isEnvironmentVariableDefined() + { + return ! empty($this->url); + } + + /** + * Get settings from URL + * + * @access public + * @param string $url + * @return array + */ + public function getSettings($url = '') + { + $url = $url ?: $this->url; + $components = parse_url($url); + + if ($components === false) { + return array(); + } + + return array( + 'driver' => $this->getUrlComponent($components, 'scheme'), + 'username' => $this->getUrlComponent($components, 'user'), + 'password' => $this->getUrlComponent($components, 'pass'), + 'hostname' => $this->getUrlComponent($components, 'host'), + 'port' => $this->getUrlComponent($components, 'port'), + 'database' => ltrim($this->getUrlComponent($components, 'path'), '/'), + ); + } + + /** + * Get URL component + * + * @access private + * @param array $components + * @param string $component + * @return mixed|null + */ + private function getUrlComponent(array $components, $component) + { + return ! empty($components[$component]) ? $components[$component] : null; + } +} |