diff options
Diffstat (limited to 'libs/picodb')
40 files changed, 6573 insertions, 0 deletions
diff --git a/libs/picodb/LICENSE b/libs/picodb/LICENSE new file mode 100644 index 00000000..6a362bc1 --- /dev/null +++ b/libs/picodb/LICENSE @@ -0,0 +1,21 @@ +The MIT License (MIT) + +Copyright (c) 2015 Frederic Guillot + +Permission is hereby granted, free of charge, to any person obtaining a copy +of this software and associated documentation files (the "Software"), to deal +in the Software without restriction, including without limitation the rights +to use, copy, modify, merge, publish, distribute, sublicense, and/or sell +copies of the Software, and to permit persons to whom the Software is +furnished to do so, subject to the following conditions: + +The above copyright notice and this permission notice shall be included in +all copies or substantial portions of the Software. + +THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR +IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, +FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE +AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER +LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, +OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN +THE SOFTWARE. diff --git a/libs/picodb/README.md b/libs/picodb/README.md new file mode 100644 index 00000000..722e8317 --- /dev/null +++ b/libs/picodb/README.md @@ -0,0 +1,672 @@ +PicoDb +====== + +PicoDb is a minimalist database query builder for PHP. + +Features +-------- + +- Easy to use, easy to hack, fast and very lightweight +- Supported drivers: Sqlite, Mssql, Mysql, Postgresql +- Requires only PDO +- Use prepared statements +- Handle schema migrations +- Fully unit tested on PHP 5.3, 5.4, 5.5, 5.6 and 7.0 +- License: MIT + +Requirements +------------ + +- PHP >= 5.3 +- PDO extension +- Sqlite, Mssql, Mysql or Postgresql + +Author +------ + +Frédéric Guillot + +Documentation +------------- + +### Installation + +```bash +composer require fguillot/picodb @stable +``` + +### Database connection + +#### Sqlite: + +```php +use PicoDb\Database; + +// Sqlite driver +$db = new Database(['driver' => 'sqlite', 'filename' => ':memory:']); +``` + +The Sqlite driver enable foreign keys by default. + +#### Microsoft SQL server: + +```php +// Optional attributes: +// "schema_table" (the default table name is "schema_version") + +$db = new Database([ + 'driver' => 'mssql', + 'hostname' => 'localhost', + 'username' => 'root', + 'password' => '', + 'database' => 'my_db_name', +]); +``` + +Optional attributes: + +- schema_table + +#### Mysql: + +```php +$db = new Database([ + 'driver' => 'mysql', + 'hostname' => 'localhost', + 'username' => 'root', + 'password' => '', + 'database' => 'my_db_name', + 'ssl_key' => '/path/to/client-key.pem', + 'ssl_cert' => '/path/to/client-cert.pem', + 'ssl_ca' => '/path/to/ca-cert.pem', +]); +``` + +Optional attributes: + +- charset +- schema_table +- port +- ssl_key +- ssl_cert +- ssl_key + +#### Postgres: + +```php +$db = new Database([ + 'driver' => 'postgres', + 'hostname' => 'localhost', + 'username' => 'root', + 'password' => '', + 'database' => 'my_db_name', +]); +``` + +Optional attributes: + +- port +- schema_table + +#### Connecting from an environment variable: + +Let's say you have defined an environment variable: + +```bash +export DATABASE_URL=postgres://user:pass@hostname:6212/db +``` + +PicoDb can parse automatically this URL for you: + +```php +use PicoDb\UrlParser; +use PicoDb\Database; + +$db = new Database(UrlParser::getInstance()->getSettings()); +``` + +#### Connecting from a URL + +```php +use PicoDb\UrlParser; +use PicoDb\Database; + +$db = new Database(UrlParser::getInstance()->getSettings('postgres://user:pass@hostname:6212/db')); +``` + +### Execute any SQL query + +```php +$db->execute('CREATE TABLE mytable (column1 TEXT)'); +``` + +- Returns a `PDOStatement` if successful +- Returns `false` if there is a duplicate key error +- Throws a `SQLException` for other errors + +### Insertion + +```php +$db->table('mytable')->save(['column1' => 'test']); +``` + +or + +```php +$db->table('mytable')->insert(['column1' => 'test']); +``` + +### Fetch last inserted id + +```php +$db->getLastId(); +``` + +### Transactions + +```php +$db->transaction(function ($db) { + $db->table('mytable')->save(['column1' => 'foo']); + $db->table('mytable')->save(['column1' => 'bar']); +}); +``` + +- Returns `true` if the callback returns null +- Returns the callback return value otherwise +- Throws an SQLException if something is wrong + +or + +```php +$db->startTransaction(); +// Do something... +$db->closeTransaction(); + +// Rollback +$db->cancelTransaction(); +``` + +### Fetch all data + +```php +$records = $db->table('mytable')->findAll(); + +foreach ($records as $record) { + var_dump($record['column1']); +} +``` + +### Updates + +```php +$db->table('mytable')->eq('id', 1)->save(['column1' => 'hey']); +``` + +or + +```php +$db->table('mytable')->eq('id', 1)->update(['column1' => 'hey']); +``` + +### Remove records + +```php +$db->table('mytable')->lt('column1', 10)->remove(); +``` + +### Sorting + +```php +$db->table('mytable')->asc('column1')->findAll(); +``` + +or + +```php +$db->table('mytable')->desc('column1')->findAll(); +``` + +or + +```php +$db->table('mytable')->orderBy('column1', 'ASC')->findAll(); +``` + +Multiple sorting: + +```php +$db->table('mytable')->asc('column1')->desc('column2')->findAll(); +``` + +### Limit and offset + +```php +$db->table('mytable')->limit(10)->offset(5)->findAll(); +``` + +### Fetch only some columns + +```php +$db->table('mytable')->columns('column1', 'column2')->findAll(); +``` + +### Fetch only one column + +Many rows: + +```php +$db->table('mytable')->findAllByColumn('column1'); +``` + +One row: + +```php +$db->table('mytable')->findOneColumn('column1'); +``` + +### Custom select + +```php +$db->table('mytable')->select(1)->eq('id', 42)->findOne(); +``` + +### Distinct + +```php +$db->table('mytable')->distinct('columnA')->findOne(); +``` + +### Group by + +```php +$db->table('mytable')->groupBy('columnA')->findAll(); +``` + +### Count + +```php +$db->table('mytable')->count(); +``` + +### Sum + +```php +$db->table('mytable')->sum('columnB'); +``` + +### Sum column values during update + +Add the value 42 to the existing value of the column "mycolumn": + +```php +$db->table('mytable')->sumColumn('mycolumn', 42)->update(); +``` + +### Increment column + +Increment a column value in a single query: + +```php +$db->table('mytable')->eq('another_column', 42)->increment('my_column', 2); +``` + +### Decrement column + +Decrement a column value in a single query: + +```php +$db->table('mytable')->eq('another_column', 42)->decrement('my_column', 1); +``` + +### Exists + +Returns true if a record exists otherwise false. + +```php +$db->table('mytable')->eq('column1', 12)->exists(); +``` + +### Left joins + +```php +// SELECT * FROM mytable LEFT JOIN my_other_table AS t1 ON t1.id=mytable.foreign_key +$db->table('mytable')->left('my_other_table', 't1', 'id', 'mytable', 'foreign_key')->findAll(); +``` + +or + +```php +// SELECT * FROM mytable LEFT JOIN my_other_table ON my_other_table.id=mytable.foreign_key +$db->table('mytable')->join('my_other_table', 'id', 'foreign_key')->findAll(); +``` + +### Equals condition + +```php +$db->table('mytable') + ->eq('column1', 'hey') + ->findAll(); +``` + +### IN condition + +```php +$db->table('mytable') + ->in('column1', ['hey', 'bla']) + ->findAll(); +``` + +### IN condition with subquery + +```php +$subquery = $db->table('another_table')->columns('column2')->eq('column3', 'value3'); + +$db->table('mytable') + ->columns('column_5') + ->inSubquery('column1', $subquery) + ->findAll(); +``` + +### Like condition + +Case-sensitive (only Mysql and Postgres): + +```php +$db->table('mytable') + ->like('column1', '%Foo%') + ->findAll(); +``` + +Not case-sensitive: + +```php +$db->table('mytable') + ->ilike('column1', '%foo%') + ->findAll(); +``` + +### Lower than condition + +```php +$db->table('mytable') + ->lt('column1', 2) + ->findAll(); +``` + +### Lower than or equal condition + +```php +$db->table('mytable') + ->lte('column1', 2) + ->findAll(); +``` + +### Greater than condition + +```php +$db->table('mytable') + ->gt('column1', 3) + ->findAll(); +``` + +### Greater than or equal condition + +```php +$db->table('mytable') + ->gte('column1', 3) + ->findAll(); +``` + +### IS NULL condition + +```php +$db->table('mytable') + ->isNull('column1') + ->findAll(); +``` + +### IS NOT NULL condition + +```php +$db->table('mytable') + ->notNull('column1') + ->findAll(); +``` + +### Multiple conditions + +Add conditions are joined by a `AND`. + +```php +$db->table('mytable') + ->like('column2', '%mytable') + ->gte('column1', 3) + ->findAll(); +``` + +How to make a OR condition: + +```php +$db->table('mytable') + ->beginOr() + ->like('column2', '%mytable') + ->gte('column1', 3) + ->closeOr() + ->eq('column5', 'titi') + ->findAll(); +``` + +### Debugging + +Log generated queries: + +```php +$db->getStatementHandler()->withLogging(); +``` + +Mesure each query time: + +```php +$db->getStatementHandler()->withStopWatch(); +``` + +Get the number of queries executed: + +```php +echo $db->getStatementHandler()->getNbQueries(); +``` + +Get log messages: + +```php +print_r($db->getLogMessages()); +``` + +### Large objects (LOBs) + +Insert a file: + +```php +$db->largeObject('my_table')->insertFromFile('blobColumn', '/path/to/file', array('id' => 'something')); +``` + +Insert from a stream: + +```php +$db->largeObject('my_table')->insertFromStream('blobColumn', $fd, array('id' => 'something')); +``` + +Fetch a large object as a stream (Postgres only): + +```php +$fd = $db->largeObject('my_table')->eq('id', 'something')->findOneColumnAsStream('blobColumn'); +``` + +Fetch a large object as a string: + +```php +echo $db->largeObject('my_table')->eq('id', 'something')->findOneColumnAsString('blobColumn'); +``` + +Drivers: + +- Postgres + - Column type: `bytea` +- Sqlite and Mysql + - Column type: `BLOB` + - PDO do no not supports the stream feature (returns a string instead) + +### Hashtable (key/value store) + +How to use a table as a key/value store: + +```php +$db->execute( + 'CREATE TABLE mytable ( + column1 TEXT NOT NULL UNIQUE, + column2 TEXT default NULL + )' +); + +$db->table('mytable')->insert(['column1' => 'option1', 'column2' => 'value1']); +``` + +Add/Replace some values: + +```php +$db->hashtable('mytable') + ->columnKey('column1') + ->columnValue('column2') + ->put(['option1' => 'new value', 'option2' => 'value2'])); +``` + +Get all values: + +```php +$result = $db->hashtable('mytable')->columnKey('column1')->columnValue('column2')->get(); +print_r($result); + +Array +( + [option2] => value2 + [option1] => new value +) +``` + +or + +```php +$result = $db->hashtable('mytable')->getAll('column1', 'column2'); +``` + +Get a specific value: + +```php +$db->hashtable('mytable') + ->columnKey('column1') + ->columnValue('column2') + ->put(['option3' => 'value3']); + +$result = $db->hashtable('mytable') + ->columnKey('column1') + ->columnValue('column2') + ->get('option1', 'option3'); + +print_r($result); + +Array +( + [option1] => new value + [option3] => value3 +) +``` + +### Schema migrations + +#### Define a migration + +- Migrations are defined in simple functions inside a namespace named "Schema". +- An instance of PDO is passed to first argument of the function. +- Function names has the version number at the end. + +Example: + +```php +namespace Schema; + +function version_1($pdo) +{ + $pdo->exec(' + CREATE TABLE users ( + id INTEGER PRIMARY KEY, + name TEXT UNIQUE, + email TEXT UNIQUE, + password TEXT + ) + '); +} + + +function version_2($pdo) +{ + $pdo->exec(' + CREATE TABLE tags ( + id INTEGER PRIMARY KEY, + name TEXT UNIQUE + ) + '); +} +``` + +#### Run schema update automatically + +- The method `check()` execute all migrations until the version specified +- If an error occurs, the transaction is rollbacked +- Foreign keys checks are disabled if possible during the migration + +Example: + +```php +$last_schema_version = 5; + +$db = new PicoDb\Database(array( + 'driver' => 'sqlite', + 'filename' => '/tmp/mydb.sqlite' +)); + +if ($db->schema()->check($last_schema_version)) { + + // Do something... +} +else { + + die('Unable to migrate database schema.'); +} +``` + +### Use a singleton to handle database instances + +Setup a new instance: + +```php +PicoDb\Database::setInstance('myinstance', function() { + + $db = new PicoDb\Database(array( + 'driver' => 'sqlite', + 'filename' => DB_FILENAME + )); + + if ($db->schema()->check(DB_VERSION)) { + return $db; + } + else { + die('Unable to migrate database schema.'); + } +}); +``` + +Get this instance anywhere in your code: + +```php +PicoDb\Database::getInstance('myinstance')->table(...) +``` 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; + } +} diff --git a/libs/picodb/phpunit.xml b/libs/picodb/phpunit.xml new file mode 100644 index 00000000..77298d47 --- /dev/null +++ b/libs/picodb/phpunit.xml @@ -0,0 +1,30 @@ +<phpunit + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" + xsi:noNamespaceSchemaLocation="http://schema.phpunit.de/4.5/phpunit.xsd" + colors="true" + stopOnError="true" + stopOnFailure="true"> + <testsuites> + <testsuite name="sqlite"> + <file>tests/UrlParserTest.php</file> + <file>tests/SqliteDriverTest.php</file> + <file>tests/SqliteDatabaseTest.php</file> + <file>tests/SqliteSchemaTest.php</file> + <file>tests/SqliteTableTest.php</file> + </testsuite> + <testsuite name="mysql"> + <file>tests/UrlParserTest.php</file> + <file>tests/MysqlDriverTest.php</file> + <file>tests/MysqlDatabaseTest.php</file> + <file>tests/MysqlSchemaTest.php</file> + <file>tests/MysqlTableTest.php</file> + </testsuite> + <testsuite name="postgres"> + <file>tests/UrlParserTest.php</file> + <file>tests/PostgresDriverTest.php</file> + <file>tests/PostgresDatabaseTest.php</file> + <file>tests/PostgresSchemaTest.php</file> + <file>tests/PostgresTableTest.php</file> + </testsuite> + </testsuites> +</phpunit> diff --git a/libs/picodb/tests/AlternativeSchemaFixture.php b/libs/picodb/tests/AlternativeSchemaFixture.php new file mode 100644 index 00000000..bebcf137 --- /dev/null +++ b/libs/picodb/tests/AlternativeSchemaFixture.php @@ -0,0 +1,15 @@ +<?php + +namespace AlternativeSchema; + +use PDO; + +function version_1(PDO $pdo) +{ + $pdo->exec('CREATE TABLE test1 (column1 TEXT)'); +} + +function version_2(PDO $pdo) +{ + $pdo->exec('CREATE TABLE test2 (column2 TEXT)'); +} diff --git a/libs/picodb/tests/MysqlDatabaseTest.php b/libs/picodb/tests/MysqlDatabaseTest.php new file mode 100644 index 00000000..bd819dd9 --- /dev/null +++ b/libs/picodb/tests/MysqlDatabaseTest.php @@ -0,0 +1,101 @@ +<?php + +require_once __DIR__.'/../../../vendor/autoload.php'; + +use PicoDb\Database; + +class MysqlDatabaseTest extends PHPUnit_Framework_TestCase +{ + /** + * @var PicoDb\Database + */ + private $db; + + public function setUp() + { + $this->db = new Database(array('driver' => 'mysql', 'hostname' => 'localhost', 'username' => 'root', 'password' => '', 'database' => 'picodb')); + $this->db->getConnection()->exec('CREATE DATABASE IF NOT EXISTS `picodb`'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS foobar'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS schema_version'); + } + + public function testEscapeIdentifer() + { + $this->assertEquals('`a`', $this->db->escapeIdentifier('a')); + $this->assertEquals('a.b', $this->db->escapeIdentifier('a.b')); + $this->assertEquals('`c`.`a`', $this->db->escapeIdentifier('a', 'c')); + $this->assertEquals('a.b', $this->db->escapeIdentifier('a.b', 'c')); + $this->assertEquals('SELECT COUNT(*) FROM test', $this->db->escapeIdentifier('SELECT COUNT(*) FROM test')); + $this->assertEquals('SELECT COUNT(*) FROM test', $this->db->escapeIdentifier('SELECT COUNT(*) FROM test', 'b')); + } + + public function testEscapeIdentiferList() + { + $this->assertEquals(array('`c`.`a`', '`c`.`b`'), $this->db->escapeIdentifierList(array('a', 'b'), 'c')); + $this->assertEquals(array('`a`', 'd.b'), $this->db->escapeIdentifierList(array('a', 'd.b'))); + } + + public function testThatPreparedStatementWorks() + { + $this->db->getConnection()->exec('CREATE TABLE foobar (id INT AUTO_INCREMENT NOT NULL, something TEXT, PRIMARY KEY (id)) ENGINE=InnoDB'); + $this->db->execute('INSERT INTO foobar (something) VALUES (?)', array('a')); + $this->assertEquals(1, $this->db->getLastId()); + $this->assertEquals('a', $this->db->execute('SELECT something FROM foobar WHERE something=?', array('a'))->fetchColumn()); + } + + /** + * @expectedException PicoDb\SQLException + */ + public function testBadSQLQuery() + { + $this->db->execute('INSERT INTO foobar'); + } + + public function testDuplicateKey() + { + $this->db->getConnection()->exec('CREATE TABLE foobar (something CHAR(1) UNIQUE) ENGINE=InnoDB'); + + $this->assertNotFalse($this->db->execute('INSERT INTO foobar (something) VALUES (?)', array('a'))); + $this->assertFalse($this->db->execute('INSERT INTO foobar (something) VALUES (?)', array('a'))); + + $this->assertEquals(1, $this->db->execute('SELECT COUNT(*) FROM foobar WHERE something=?', array('a'))->fetchColumn()); + } + + public function testThatTransactionReturnsAValue() + { + $this->assertEquals('a', $this->db->transaction(function (Database $db) { + $db->getConnection()->exec('CREATE TABLE foobar (something CHAR(1) UNIQUE) ENGINE=InnoDB'); + $db->execute('INSERT INTO foobar (something) VALUES (?)', array('a')); + + return $db->execute('SELECT something FROM foobar WHERE something=?', array('a'))->fetchColumn(); + })); + } + + public function testThatTransactionReturnsTrue() + { + $this->assertTrue($this->db->transaction(function (Database $db) { + $db->getConnection()->exec('CREATE TABLE foobar (something CHAR(1) UNIQUE) ENGINE=InnoDB'); + $db->execute('INSERT INTO foobar (something) VALUES (?)', array('a')); + })); + } + + /** + * @expectedException PicoDb\SQLException + */ + public function testThatTransactionThrowExceptionWhenRollbacked() + { + $this->assertFalse($this->db->transaction(function (Database $db) { + $db->getConnection()->exec('CREATE TABL'); + })); + } + + public function testThatTransactionReturnsFalseWhithDuplicateKey() + { + $this->assertFalse($this->db->transaction(function (Database $db) { + $db->getConnection()->exec('CREATE TABLE foobar (something CHAR(1) UNIQUE) ENGINE=InnoDB'); + $r1 = $db->execute('INSERT INTO foobar (something) VALUES (?)', array('a')); + $r2 = $db->execute('INSERT INTO foobar (something) VALUES (?)', array('a')); + return $r1 && $r2; + })); + } +} diff --git a/libs/picodb/tests/MysqlDriverTest.php b/libs/picodb/tests/MysqlDriverTest.php new file mode 100644 index 00000000..2666a993 --- /dev/null +++ b/libs/picodb/tests/MysqlDriverTest.php @@ -0,0 +1,73 @@ +<?php + +require_once __DIR__.'/../../../vendor/autoload.php'; + +use PicoDb\Driver\Mysql; + +class MysqlDriverTest extends PHPUnit_Framework_TestCase +{ + /** + * @var PicoDb\Driver\Mysql + */ + private $driver; + + public function setUp() + { + $this->driver = new Mysql(array('hostname' => 'localhost', 'username' => 'root', 'password' => '', 'database' => 'picodb')); + $this->driver->getConnection()->exec('CREATE DATABASE IF NOT EXISTS `picodb`'); + $this->driver->getConnection()->exec('DROP TABLE IF EXISTS foobar'); + $this->driver->getConnection()->exec('DROP TABLE IF EXISTS schema_version'); + } + + /** + * @expectedException LogicException + */ + public function testMissingRequiredParameter() + { + new Mysql(array()); + } + + public function testDuplicateKeyError() + { + $this->assertFalse($this->driver->isDuplicateKeyError(1234)); + $this->assertTrue($this->driver->isDuplicateKeyError(23000)); + } + + public function testOperator() + { + $this->assertEquals('LIKE BINARY', $this->driver->getOperator('LIKE')); + $this->assertEquals('LIKE', $this->driver->getOperator('ILIKE')); + $this->assertEquals('', $this->driver->getOperator('FOO')); + } + + public function testSchemaVersion() + { + $this->assertEquals(0, $this->driver->getSchemaVersion()); + + $this->driver->setSchemaVersion(1); + $this->assertEquals(1, $this->driver->getSchemaVersion()); + + $this->driver->setSchemaVersion(42); + $this->assertEquals(42, $this->driver->getSchemaVersion()); + } + + public function testLastInsertId() + { + $this->assertEquals(0, $this->driver->getLastId()); + + $this->driver->getConnection()->exec('CREATE TABLE foobar (id INT AUTO_INCREMENT NOT NULL, something TEXT, PRIMARY KEY (id)) ENGINE=InnoDB'); + $this->driver->getConnection()->exec('INSERT INTO foobar (something) VALUES (1)'); + + $this->assertEquals(1, $this->driver->getLastId()); + } + + public function testEscape() + { + $this->assertEquals('`foobar`', $this->driver->escape('foobar')); + } + + public function testDatabaseVersion() + { + $this->assertStringStartsWith('5.', $this->driver->getDatabaseVersion()); + } +} diff --git a/libs/picodb/tests/MysqlLobTest.php b/libs/picodb/tests/MysqlLobTest.php new file mode 100644 index 00000000..1291d422 --- /dev/null +++ b/libs/picodb/tests/MysqlLobTest.php @@ -0,0 +1,83 @@ +<?php + +require_once __DIR__.'/../../../vendor/autoload.php'; + +class MysqlLobTest extends PHPUnit_Framework_TestCase +{ + /** + * @var PicoDb\Database + */ + private $db; + + public function setUp() + { + $this->db = new PicoDb\Database(array('driver' => 'mysql', 'hostname' => 'localhost', 'username' => 'root', 'password' => '', 'database' => 'picodb')); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS large_objects'); + $this->db->getConnection()->exec('CREATE TABLE large_objects (id VARCHAR(20), file_content BLOB)'); + $this->db->getStatementHandler()->withLogging(); + } + + public function testInsert() + { + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__, array('id' => 'test')); + $this->assertTrue($result); + } + + public function testInsertFromString() + { + $data = 'test'; + $result = $this->db->largeObject('large_objects')->insertFromString('file_content', $data, array('id' => 'test')); + $this->assertTrue($result); + } + + public function testInsertWithOptionalParams() + { + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__); + $this->assertTrue($result); + } + + public function testFindOneColumnAsStream() + { + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__, array('id' => 'test')); + $this->assertTrue($result); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test')->findOneColumnAsStream('file_content'); + $this->assertSame(md5(file_get_contents(__FILE__)), md5($contents)); + } + + public function testFindOneColumnAsString() + { + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__, array('id' => 'test')); + $this->assertTrue($result); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test')->findOneColumnAsString('file_content'); + $this->assertSame(md5(file_get_contents(__FILE__)), md5($contents)); + } + + public function testUpdate() + { + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__, array('id' => 'test1')); + $this->assertTrue($result); + + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__, array('id' => 'test2')); + $this->assertTrue($result); + + $result = $this->db->largeObject('large_objects')->eq('id', 'test1')->updateFromFile('file_content', __DIR__.'/../LICENSE'); + $this->assertTrue($result); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test1')->findOneColumnAsString('file_content'); + $this->assertSame(md5(file_get_contents(__DIR__.'/../LICENSE')), md5($contents)); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test2')->findOneColumnAsString('file_content'); + $this->assertSame(md5(file_get_contents(__FILE__)), md5($contents)); + + $result = $this->db->largeObject('large_objects')->updateFromFile('file_content', __DIR__.'/../composer.json'); + $this->assertTrue($result); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test1')->findOneColumnAsString('file_content'); + $this->assertSame(md5(file_get_contents(__DIR__.'/../composer.json')), md5($contents)); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test2')->findOneColumnAsString('file_content'); + $this->assertSame(md5(file_get_contents(__DIR__.'/../composer.json')), md5($contents)); + } +} diff --git a/libs/picodb/tests/MysqlSchemaTest.php b/libs/picodb/tests/MysqlSchemaTest.php new file mode 100644 index 00000000..4eeee0b9 --- /dev/null +++ b/libs/picodb/tests/MysqlSchemaTest.php @@ -0,0 +1,49 @@ +<?php + +require_once __DIR__.'/../../../vendor/autoload.php'; +require_once __DIR__.'/SchemaFixture.php'; +require_once __DIR__.'/AlternativeSchemaFixture.php'; + +class MysqlSchemaTest extends PHPUnit_Framework_TestCase +{ + /** + * @var PicoDb\Database + */ + private $db; + + public function setUp() + { + $this->db = new PicoDb\Database(array('driver' => 'mysql', 'hostname' => 'localhost', 'username' => 'root', 'password' => '', 'database' => 'picodb')); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS test1'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS test2'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS schema_version'); + } + + public function testMigrations() + { + $this->assertTrue($this->db->schema()->check(2)); + $this->assertEquals(2, $this->db->getDriver()->getSchemaVersion()); + $this->assertEquals('\Schema', $this->db->schema()->getNamespace()); + } + + public function testFailedMigrations() + { + $this->assertEquals(0, $this->db->getDriver()->getSchemaVersion()); + $this->assertFalse($this->db->schema()->check(3)); + $this->assertEquals(2, $this->db->getDriver()->getSchemaVersion()); + + $logs = $this->db->getLogMessages(); + $this->assertNotEmpty($logs); + $this->assertEquals('Running migration \Schema\version_1', $logs[0]); + $this->assertEquals('Running migration \Schema\version_2', $logs[1]); + $this->assertEquals('Running migration \Schema\version_3', $logs[2]); + $this->assertStringStartsWith('SQLSTATE[42000]: Syntax error or access violation', $logs[3]); + } + + public function testAlternativeSchemaNamespace() + { + $this->assertEquals('\AlternativeSchema', $this->db->schema('\AlternativeSchema')->getNamespace()); + $this->assertTrue($this->db->schema('\AlternativeSchema')->check(2)); + $this->assertEquals(2, $this->db->getDriver()->getSchemaVersion()); + } +} diff --git a/libs/picodb/tests/MysqlTableTest.php b/libs/picodb/tests/MysqlTableTest.php new file mode 100644 index 00000000..62c1730a --- /dev/null +++ b/libs/picodb/tests/MysqlTableTest.php @@ -0,0 +1,356 @@ +<?php + +require_once __DIR__.'/../../../vendor/autoload.php'; + +use PicoDb\Database; +use PicoDb\Table; + +class MysqlTableTest extends PHPUnit_Framework_TestCase +{ + /** + * @var PicoDb\Database + */ + private $db; + + public function setUp() + { + $this->db = new Database(array('driver' => 'mysql', 'hostname' => 'localhost', 'username' => 'root', 'password' => '', 'database' => 'picodb')); + $this->db->getConnection()->exec('CREATE DATABASE IF NOT EXISTS `picodb`'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS test1'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS test2'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS foobar'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS foobar_persist'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS schema_version'); + } + + public function testSelect() + { + $this->assertEquals('SELECT 1 FROM `test`', $this->db->table('test')->select(1)->buildSelectQuery()); + } + + public function testColumns() + { + $this->assertEquals('SELECT `a`, `b` FROM `test`', $this->db->table('test')->columns('a', 'b')->buildSelectQuery()); + } + + public function testDistinct() + { + $this->assertEquals('SELECT DISTINCT `a`, `b` FROM `test`', $this->db->table('test')->distinct('a', 'b')->buildSelectQuery()); + } + + public function testGroupBy() + { + $this->assertEquals('SELECT * FROM `test` GROUP BY `a`', $this->db->table('test')->groupBy('a')->buildSelectQuery()); + } + + public function testOrderBy() + { + $this->assertEquals('SELECT * FROM `test` ORDER BY `a` ASC', $this->db->table('test')->asc('a')->buildSelectQuery()); + $this->assertEquals('SELECT * FROM `test` ORDER BY `a` ASC', $this->db->table('test')->orderBy('a', Table::SORT_ASC)->buildSelectQuery()); + + $this->assertEquals('SELECT * FROM `test` ORDER BY `a` DESC', $this->db->table('test')->desc('a')->buildSelectQuery()); + $this->assertEquals('SELECT * FROM `test` ORDER BY `a` DESC', $this->db->table('test')->orderBy('a', Table::SORT_DESC)->buildSelectQuery()); + + $this->assertEquals('SELECT * FROM `test` ORDER BY `a` ASC, `b` ASC', $this->db->table('test')->asc('a')->asc('b')->buildSelectQuery()); + $this->assertEquals('SELECT * FROM `test` ORDER BY `a` DESC, `b` DESC', $this->db->table('test')->desc('a')->desc('b')->buildSelectQuery()); + + $this->assertEquals('SELECT * FROM `test` ORDER BY `a` ASC, `b` ASC', $this->db->table('test')->orderBy('a')->orderBy('b')->buildSelectQuery()); + $this->assertEquals('SELECT * FROM `test` ORDER BY `a` DESC, `b` DESC', $this->db->table('test')->orderBy('a', Table::SORT_DESC)->orderBy('b', Table::SORT_DESC)->buildSelectQuery()); + + $this->assertEquals('SELECT * FROM `test` ORDER BY `a` DESC, `b` ASC', $this->db->table('test')->desc('a')->asc('b')->buildSelectQuery()); + } + + public function testLimit() + { + $this->assertEquals('SELECT * FROM `test` LIMIT 10', $this->db->table('test')->limit(10)->buildSelectQuery()); + $this->assertEquals('SELECT * FROM `test`', $this->db->table('test')->limit(null)->buildSelectQuery()); + } + + public function testOffset() + { + $this->assertEquals('SELECT * FROM `test` OFFSET 0', $this->db->table('test')->offset(0)->buildSelectQuery()); + $this->assertEquals('SELECT * FROM `test` OFFSET 10', $this->db->table('test')->offset(10)->buildSelectQuery()); + $this->assertEquals('SELECT * FROM `test`', $this->db->table('test')->limit(null)->buildSelectQuery()); + } + + public function testLimitOffset() + { + $this->assertEquals('SELECT * FROM `test` LIMIT 2 OFFSET 0', $this->db->table('test')->offset(0)->limit(2)->buildSelectQuery()); + $this->assertEquals('SELECT * FROM `test` LIMIT 5 OFFSET 10', $this->db->table('test')->offset(10)->limit(5)->buildSelectQuery()); + } + + public function testSubquery() + { + $this->assertEquals('SELECT (SELECT 1 FROM "foobar" WHERE 1=1) AS `b` FROM `test`', $this->db->table('test')->subquery('SELECT 1 FROM "foobar" WHERE 1=1', 'b')->buildSelectQuery()); + } + + public function testConditionEqual() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM `test` WHERE `a` = ? AND `b` = ?', $table->eq('a', 2)->eq('b', 'foobar')->buildSelectQuery()); + $this->assertEquals(array(2, 'foobar'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionNotEqual() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM `test` WHERE `a` != ?', $table->neq('a', 2)->buildSelectQuery()); + $this->assertEquals(array(2), $table->getConditionBuilder()->getValues()); + } + + public function testConditionIn() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM `test` WHERE `a` IN (?, ?)', $table->in('a', array('b', 'c'))->buildSelectQuery()); + $this->assertEquals(array('b', 'c'), $table->getConditionBuilder()->getValues()); + + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM `test`', $table->in('a', array())->buildSelectQuery()); + $this->assertEquals(array(), $table->getConditionBuilder()->getValues()); + } + + public function testConditionInSubquery() + { + $table = $this->db->table('test'); + $subquery = $this->db->table('test2')->columns('c')->eq('d', 'e'); + + $this->assertEquals( + 'SELECT * FROM `test` WHERE `a` IN (SELECT `c` FROM `test2` WHERE `d` = ?)', + $table->inSubquery('a', $subquery)->buildSelectQuery() + ); + + $this->assertEquals(array('e'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionNotIn() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM `test` WHERE `a` NOT IN (?, ?)', $table->notIn('a', array('b', 'c'))->buildSelectQuery()); + $this->assertEquals(array('b', 'c'), $table->getConditionBuilder()->getValues()); + + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM `test`', $table->notIn('a', array())->buildSelectQuery()); + $this->assertEquals(array(), $table->getConditionBuilder()->getValues()); + } + + public function testConditionNotInSubquery() + { + $table = $this->db->table('test'); + $subquery = $this->db->table('test2')->columns('c')->eq('d', 'e'); + + $this->assertEquals( + 'SELECT * FROM `test` WHERE `a` NOT IN (SELECT `c` FROM `test2` WHERE `d` = ?)', + $table->notInSubquery('a', $subquery)->buildSelectQuery() + ); + + $this->assertEquals(array('e'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionLike() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM `test` WHERE `a` LIKE BINARY ?', $table->like('a', '%foobar%')->buildSelectQuery()); + $this->assertEquals(array('%foobar%'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionILike() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM `test` WHERE `a` LIKE ?', $table->ilike('a', '%foobar%')->buildSelectQuery()); + $this->assertEquals(array('%foobar%'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionGreaterThan() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM `test` WHERE `a` > ?', $table->gt('a', 5)->buildSelectQuery()); + $this->assertEquals(array(5), $table->getConditionBuilder()->getValues()); + } + + public function testConditionGreaterThanOrEqual() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM `test` WHERE `a` >= ?', $table->gte('a', 5)->buildSelectQuery()); + $this->assertEquals(array(5), $table->getConditionBuilder()->getValues()); + } + + public function testConditionLowerThan() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM `test` WHERE `a` < ?', $table->lt('a', 5)->buildSelectQuery()); + $this->assertEquals(array(5), $table->getConditionBuilder()->getValues()); + } + + public function testConditionLowerThanOrEqual() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM `test` WHERE `a` <= ?', $table->lte('a', 5)->buildSelectQuery()); + $this->assertEquals(array(5), $table->getConditionBuilder()->getValues()); + } + + public function testConditionIsNull() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM `test` WHERE `a` IS NOT NULL', $table->notNull('a')->buildSelectQuery()); + $this->assertEquals(array(), $table->getConditionBuilder()->getValues()); + } + + public function testCustomCondition() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM `test` WHERE a=c AND `b` = ?', $table->addCondition('a=c')->eq('b', 4)->buildSelectQuery()); + $this->assertEquals(array(4), $table->getConditionBuilder()->getValues()); + } + + public function testOrConditions() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM `test` WHERE `a` IS NOT NULL AND (`b` = ? OR `c` >= ?)', $table->notNull('a')->beginOr()->eq('b', 2)->gte('c', 5)->closeOr()->buildSelectQuery()); + $this->assertEquals(array(2, 5), $table->getConditionBuilder()->getValues()); + } + + public function testPersist() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar_persist (id INT NOT NULL AUTO_INCREMENT, a VARCHAR(10), PRIMARY KEY(id))')); + $this->assertSame(1, $this->db->table('foobar_persist')->persist(array('a' => 'b'))); + } + + public function testInsertUpdate() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar (a TEXT)')); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 'b'))); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 'c'))); + + $this->assertEquals(array(array('a' => 'b'), array('a' => 'c')), $this->db->table('foobar')->findAll()); + + $this->assertEquals(array('b', 'c'), $this->db->table('foobar')->findAllByColumn('a')); + + $this->assertEquals(array('a' => 'b'), $this->db->table('foobar')->findOne()); + + $this->assertEquals('b', $this->db->table('foobar')->findOneColumn('a')); + + $this->assertTrue($this->db->table('foobar')->exists()); + $this->assertTrue($this->db->table('foobar')->eq('a', 'c')->exists()); + $this->assertFalse($this->db->table('foobar')->eq('a', 'e')->exists()); + + $this->assertEquals(2, $this->db->table('foobar')->count()); + $this->assertEquals(1, $this->db->table('foobar')->eq('a', 'c')->count()); + $this->assertEquals(0, $this->db->table('foobar')->eq('a', 'e')->count()); + + $this->assertTrue($this->db->table('foobar')->eq('a', 'c')->remove()); + $this->assertFalse($this->db->table('foobar')->eq('a', 'e')->remove()); + + $this->assertTrue($this->db->table('foobar')->eq('a', 'b')->update(array('a' => 'test'))); + $this->assertTrue($this->db->table('foobar')->eq('a', 'lol')->update(array('a' => 'test'))); + + $this->assertNotEmpty($this->db->table('foobar')->eq('a', 'test')->findOne()); + $this->assertNull($this->db->table('foobar')->eq('a', 'lol')->findOne()); + + $this->assertTrue($this->db->table('foobar')->eq('a', 'test')->save(array('a' => 'plop'))); + $this->assertEquals(1, $this->db->table('foobar')->count()); + } + + public function testSumColumn() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar (b FLOAT, c FLOAT)')); + $this->assertTrue($this->db->table('foobar')->insert(array('b' => 2, 'c' => 3.3))); + + $this->assertTrue($this->db->table('foobar')->sumColumn('b', 2.5)->sumColumn('c', 3)->update()); + + $this->assertEquals( + array('b' => 4.5, 'c' => 6.3), + $this->db->table('foobar')->findOne() + ); + } + + public function testSum() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar (a INTEGER)')); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 2))); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 5))); + $this->assertEquals(7, $this->db->table('foobar')->sum('a')); + } + + public function testIncrement() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar (a INTEGER DEFAULT 0, b INTEGER DEFAULT 0)')); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 2, 'b' => 5))); + $this->assertTrue($this->db->table('foobar')->eq('b', 5)->increment('a', 3)); + $this->assertEquals(5, $this->db->table('foobar')->findOneColumn('a')); + } + + public function testLeftJoin() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE test1 (a INTEGER NOT NULL, foreign_key INTEGER NOT NULL)')); + $this->assertNotFalse($this->db->execute('CREATE TABLE test2 (id INTEGER NOT NULL, b INTEGER NOT NULL)')); + + $this->assertTrue($this->db->table('test2')->insert(array('id' => 42, 'b' => 2))); + $this->assertTrue($this->db->table('test1')->insert(array('a' => 18, 'foreign_key' => 42))); + + $this->assertEquals( + array('a' => 18, 'b' => 2), + $this->db->table('test2')->columns('a', 'b')->eq('a', 18)->left('test1', 't1', 'foreign_key', 'test2', 'id')->findOne() + ); + + $this->assertEquals( + array('a' => 18, 'b' => 2), + $this->db->table('test2')->columns('a', 'b')->eq('a', 18)->join('test1', 'foreign_key', 'id')->findOne() + ); + + $this->assertEquals( + array('a' => 18, 'b' => 2), + $this->db->table('test1')->columns('a', 'b')->join('test2', 'id', 'foreign_key')->findOne() + ); + } + + public function testHashTable() + { + $this->assertNotFalse($this->db->execute( + 'CREATE TABLE foobar ( + column1 VARCHAR(20) NOT NULL UNIQUE, + column2 VARCHAR(20) default NULL + )' + )); + + $this->assertTrue($this->db->table('foobar')->insert(array('column1' => 'option1', 'column2' => 'value1'))); + $this->assertTrue($this->db->table('foobar')->insert(array('column1' => 'option2', 'column2' => 'value2'))); + $this->assertTrue($this->db->table('foobar')->insert(array('column1' => 'option3', 'column2' => 'value3'))); + + $values = array( + 'option1' => 'hey', + 'option4' => 'ho', + ); + + $this->assertTrue($this->db->hashtable('foobar')->columnKey('column1')->columnValue('column2')->put($values)); + + $this->assertEquals( + array('option2' => 'value2', 'option4' => 'ho'), + $this->db->hashtable('foobar')->columnKey('column1')->columnValue('column2')->get('option2', 'option4') + ); + + $this->assertEquals( + array('option2' => 'value2', 'option3' => 'value3', 'option1' => 'hey', 'option4' => 'ho'), + $this->db->hashtable('foobar')->columnKey('column1')->columnValue('column2')->get() + ); + + $this->assertEquals( + array('option2' => 'value2', 'option3' => 'value3', 'option1' => 'hey', 'option4' => 'ho'), + $this->db->hashtable('foobar')->getAll('column1', 'column2') + ); + } +} diff --git a/libs/picodb/tests/PostgresDatabaseTest.php b/libs/picodb/tests/PostgresDatabaseTest.php new file mode 100644 index 00000000..d0d8a644 --- /dev/null +++ b/libs/picodb/tests/PostgresDatabaseTest.php @@ -0,0 +1,100 @@ +<?php + +require_once __DIR__.'/../../../vendor/autoload.php'; + +use PicoDb\Database; + +class PostgresDatabaseTest extends PHPUnit_Framework_TestCase +{ + /** + * @var PicoDb\Database + */ + private $db; + + public function setUp() + { + $this->db = new Database(array('driver' => 'postgres', 'hostname' => 'localhost', 'username' => 'postgres', 'password' => '', 'database' => 'picodb')); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS foobar'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS schema_version'); + } + + public function testEscapeIdentifer() + { + $this->assertEquals('"a"', $this->db->escapeIdentifier('a')); + $this->assertEquals('a.b', $this->db->escapeIdentifier('a.b')); + $this->assertEquals('"c"."a"', $this->db->escapeIdentifier('a', 'c')); + $this->assertEquals('a.b', $this->db->escapeIdentifier('a.b', 'c')); + $this->assertEquals('SELECT COUNT(*) FROM test', $this->db->escapeIdentifier('SELECT COUNT(*) FROM test')); + $this->assertEquals('SELECT COUNT(*) FROM test', $this->db->escapeIdentifier('SELECT COUNT(*) FROM test', 'b')); + } + + public function testEscapeIdentiferList() + { + $this->assertEquals(array('"c"."a"', '"c"."b"'), $this->db->escapeIdentifierList(array('a', 'b'), 'c')); + $this->assertEquals(array('"a"', 'd.b'), $this->db->escapeIdentifierList(array('a', 'd.b'))); + } + + public function testThatPreparedStatementWorks() + { + $this->db->getConnection()->exec('CREATE TABLE foobar (id serial PRIMARY KEY, something TEXT)'); + $this->db->execute('INSERT INTO foobar (something) VALUES (?)', array('a')); + $this->assertEquals(1, $this->db->getLastId()); + $this->assertEquals('a', $this->db->execute('SELECT something FROM foobar WHERE something=?', array('a'))->fetchColumn()); + } + + /** + * @expectedException PicoDb\SQLException + */ + public function testBadSQLQuery() + { + $this->db->execute('INSERT INTO foobar'); + } + + public function testDuplicateKey() + { + $this->db->getConnection()->exec('CREATE TABLE foobar (something TEXT UNIQUE)'); + + $this->assertNotFalse($this->db->execute('INSERT INTO foobar (something) VALUES (?)', array('a'))); + $this->assertFalse($this->db->execute('INSERT INTO foobar (something) VALUES (?)', array('a'))); + + $this->assertEquals(1, $this->db->execute('SELECT COUNT(*) FROM foobar WHERE something=?', array('a'))->fetchColumn()); + } + + public function testThatTransactionReturnsAValue() + { + $this->assertEquals('a', $this->db->transaction(function (Database $db) { + $db->getConnection()->exec('CREATE TABLE foobar (something TEXT UNIQUE)'); + $db->execute('INSERT INTO foobar (something) VALUES (?)', array('a')); + + return $db->execute('SELECT something FROM foobar WHERE something=?', array('a'))->fetchColumn(); + })); + } + + public function testThatTransactionReturnsTrue() + { + $this->assertTrue($this->db->transaction(function (Database $db) { + $db->getConnection()->exec('CREATE TABLE foobar (something TEXT UNIQUE)'); + $db->execute('INSERT INTO foobar (something) VALUES (?)', array('a')); + })); + } + + /** + * @expectedException PicoDb\SQLException + */ + public function testThatTransactionThrowExceptionWhenRollbacked() + { + $this->assertFalse($this->db->transaction(function (Database $db) { + $db->getConnection()->exec('CREATE TABL'); + })); + } + + public function testThatTransactionReturnsFalseWhithDuplicateKey() + { + $this->assertFalse($this->db->transaction(function (Database $db) { + $db->getConnection()->exec('CREATE TABLE foobar (something TEXT UNIQUE)'); + $r1 = $db->execute('INSERT INTO foobar (something) VALUES (?)', array('a')); + $r2 = $db->execute('INSERT INTO foobar (something) VALUES (?)', array('a')); + return $r1 && $r2; + })); + } +} diff --git a/libs/picodb/tests/PostgresDriverTest.php b/libs/picodb/tests/PostgresDriverTest.php new file mode 100644 index 00000000..9798042b --- /dev/null +++ b/libs/picodb/tests/PostgresDriverTest.php @@ -0,0 +1,78 @@ +<?php + +require_once __DIR__.'/../../../vendor/autoload.php'; + +use PicoDb\Driver\Postgres; + +class PostgresDriverTest extends PHPUnit_Framework_TestCase +{ + /** + * @var PicoDb\Driver\Postgres + */ + private $driver; + + public function setUp() + { + $this->driver = new Postgres(array('hostname' => 'localhost', 'username' => 'postgres', 'password' => 'postgres', 'database' => 'picodb')); + $this->driver->getConnection()->exec('DROP TABLE IF EXISTS foobar'); + $this->driver->getConnection()->exec('DROP TABLE IF EXISTS schema_version'); + } + + public function tearDown() + { + $this->driver->closeConnection(); + } + + /** + * @expectedException LogicException + */ + public function testMissingRequiredParameter() + { + new Postgres(array()); + } + + public function testDuplicateKeyError() + { + $this->assertFalse($this->driver->isDuplicateKeyError(1234)); + $this->assertTrue($this->driver->isDuplicateKeyError(23505)); + $this->assertTrue($this->driver->isDuplicateKeyError(23503)); + } + + public function testOperator() + { + $this->assertEquals('LIKE', $this->driver->getOperator('LIKE')); + $this->assertEquals('ILIKE', $this->driver->getOperator('ILIKE')); + $this->assertEquals('', $this->driver->getOperator('FOO')); + } + + public function testSchemaVersion() + { + $this->assertEquals(0, $this->driver->getSchemaVersion()); + + $this->driver->setSchemaVersion(1); + $this->assertEquals(1, $this->driver->getSchemaVersion()); + + $this->driver->setSchemaVersion(42); + $this->assertEquals(42, $this->driver->getSchemaVersion()); + } + + public function testLastInsertId() + { + $this->assertEquals(0, $this->driver->getLastId()); + + $this->driver->getConnection()->exec('CREATE TABLE foobar (id serial PRIMARY KEY, something TEXT)'); + $this->driver->getConnection()->exec('INSERT INTO foobar (something) VALUES (1)'); + + $this->assertEquals(1, $this->driver->getLastId()); + } + + public function testEscape() + { + $this->assertEquals('"foobar"', $this->driver->escape('foobar')); + } + + public function testDatabaseVersion() + { + $this->assertStringStartsWith('9.', $this->driver->getDatabaseVersion()); + } +} diff --git a/libs/picodb/tests/PostgresLobTest.php b/libs/picodb/tests/PostgresLobTest.php new file mode 100644 index 00000000..39cf8fb0 --- /dev/null +++ b/libs/picodb/tests/PostgresLobTest.php @@ -0,0 +1,87 @@ +<?php + +require_once __DIR__.'/../../../vendor/autoload.php'; + +use PicoDb\Database; + +class PostgresLobTest extends PHPUnit_Framework_TestCase +{ + /** + * @var PicoDb\Database + */ + private $db; + + public function setUp() + { + $this->db = new Database(array('driver' => 'postgres', 'hostname' => 'localhost', 'username' => 'postgres', 'password' => 'postgres', 'database' => 'picodb')); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS large_objects'); + $this->db->getConnection()->exec('CREATE TABLE large_objects (id VARCHAR(20), file_content bytea)'); + } + + public function testInsert() + { + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__, array('id' => 'test')); + $this->assertTrue($result); + } + + public function testInsertFromString() + { + $data = 'test'; + $result = $this->db->largeObject('large_objects')->insertFromString('file_content', $data, array('id' => 'test')); + $this->assertTrue($result); + } + + public function testInsertWithOptionalParams() + { + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__); + $this->assertTrue($result); + } + + public function testFindOneColumnAsStream() + { + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__, array('id' => 'test')); + $this->assertTrue($result); + + $fd = $this->db->largeObject('large_objects')->eq('id', 'test')->findOneColumnAsStream('file_content'); + $contents = fread($fd, filesize(__FILE__)); + fclose($fd); + + $this->assertSame(md5(file_get_contents(__FILE__)), md5($contents)); + } + + public function testFindOneColumnAsString() + { + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__, array('id' => 'test')); + $this->assertTrue($result); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test')->findOneColumnAsString('file_content'); + $this->assertSame(md5(file_get_contents(__FILE__)), md5($contents)); + } + + public function testUpdate() + { + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__, array('id' => 'test1')); + $this->assertTrue($result); + + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__, array('id' => 'test2')); + $this->assertTrue($result); + + $result = $this->db->largeObject('large_objects')->eq('id', 'test1')->updateFromFile('file_content', __DIR__.'/../LICENSE'); + $this->assertTrue($result); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test1')->findOneColumnAsString('file_content'); + $this->assertSame(md5(file_get_contents(__DIR__.'/../LICENSE')), md5($contents)); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test2')->findOneColumnAsString('file_content'); + $this->assertSame(md5(file_get_contents(__FILE__)), md5($contents)); + + $result = $this->db->largeObject('large_objects')->updateFromFile('file_content', __DIR__.'/../composer.json'); + $this->assertTrue($result); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test1')->findOneColumnAsString('file_content'); + $this->assertSame(md5(file_get_contents(__DIR__.'/../composer.json')), md5($contents)); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test2')->findOneColumnAsString('file_content'); + $this->assertSame(md5(file_get_contents(__DIR__.'/../composer.json')), md5($contents)); + } +} diff --git a/libs/picodb/tests/PostgresSchemaTest.php b/libs/picodb/tests/PostgresSchemaTest.php new file mode 100644 index 00000000..5ecf1cc5 --- /dev/null +++ b/libs/picodb/tests/PostgresSchemaTest.php @@ -0,0 +1,40 @@ +<?php + +require_once __DIR__.'/../../../vendor/autoload.php'; +require_once __DIR__.'/SchemaFixture.php'; + +class PostgresSchemaTest extends PHPUnit_Framework_TestCase +{ + /** + * @var PicoDb\Database + */ + private $db; + + public function setUp() + { + $this->db = new PicoDb\Database(array('driver' => 'postgres', 'hostname' => 'localhost', 'username' => 'postgres', 'password' => 'postgres', 'database' => 'picodb')); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS test1'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS test2'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS schema_version'); + } + + public function testMigrations() + { + $this->assertTrue($this->db->schema()->check(2)); + $this->assertEquals(2, $this->db->getDriver()->getSchemaVersion()); + } + + public function testFailedMigrations() + { + $this->assertEquals(0, $this->db->getDriver()->getSchemaVersion()); + $this->assertFalse($this->db->schema()->check(3)); + $this->assertEquals(2, $this->db->getDriver()->getSchemaVersion()); + + $logs = $this->db->getLogMessages(); + $this->assertNotEmpty($logs); + $this->assertEquals('Running migration \Schema\version_1', $logs[0]); + $this->assertEquals('Running migration \Schema\version_2', $logs[1]); + $this->assertEquals('Running migration \Schema\version_3', $logs[2]); + $this->assertStringStartsWith('SQLSTATE[42601]: Syntax error', $logs[3]); + } +} diff --git a/libs/picodb/tests/PostgresTableTest.php b/libs/picodb/tests/PostgresTableTest.php new file mode 100644 index 00000000..dc852adf --- /dev/null +++ b/libs/picodb/tests/PostgresTableTest.php @@ -0,0 +1,355 @@ +<?php + +require_once __DIR__.'/../../../vendor/autoload.php'; + +use PicoDb\Database; +use PicoDb\Table; + +class PostgresTableTest extends PHPUnit_Framework_TestCase +{ + /** + * @var PicoDb\Database + */ + private $db; + + public function setUp() + { + $this->db = new Database(array('driver' => 'postgres', 'hostname' => 'localhost', 'username' => 'postgres', 'password' => 'postgres', 'database' => 'picodb')); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS test1'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS test2'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS foobar'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS foobar_persist'); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS schema_version'); + } + + public function testSelect() + { + $this->assertEquals('SELECT 1 FROM "test"', $this->db->table('test')->select(1)->buildSelectQuery()); + } + + public function testColumns() + { + $this->assertEquals('SELECT "a", "b" FROM "test"', $this->db->table('test')->columns('a', 'b')->buildSelectQuery()); + } + + public function testDistinct() + { + $this->assertEquals('SELECT DISTINCT "a", "b" FROM "test"', $this->db->table('test')->distinct('a', 'b')->buildSelectQuery()); + } + + public function testGroupBy() + { + $this->assertEquals('SELECT * FROM "test" GROUP BY "a"', $this->db->table('test')->groupBy('a')->buildSelectQuery()); + } + + public function testOrderBy() + { + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" ASC', $this->db->table('test')->asc('a')->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" ASC', $this->db->table('test')->orderBy('a', Table::SORT_ASC)->buildSelectQuery()); + + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" DESC', $this->db->table('test')->desc('a')->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" DESC', $this->db->table('test')->orderBy('a', Table::SORT_DESC)->buildSelectQuery()); + + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" ASC, "b" ASC', $this->db->table('test')->asc('a')->asc('b')->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" DESC, "b" DESC', $this->db->table('test')->desc('a')->desc('b')->buildSelectQuery()); + + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" ASC, "b" ASC', $this->db->table('test')->orderBy('a')->orderBy('b')->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" DESC, "b" DESC', $this->db->table('test')->orderBy('a', Table::SORT_DESC)->orderBy('b', Table::SORT_DESC)->buildSelectQuery()); + + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" DESC, "b" ASC', $this->db->table('test')->desc('a')->asc('b')->buildSelectQuery()); + } + + public function testLimit() + { + $this->assertEquals('SELECT * FROM "test" LIMIT 10', $this->db->table('test')->limit(10)->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test"', $this->db->table('test')->limit(null)->buildSelectQuery()); + } + + public function testOffset() + { + $this->assertEquals('SELECT * FROM "test" OFFSET 0', $this->db->table('test')->offset(0)->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test" OFFSET 10', $this->db->table('test')->offset(10)->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test"', $this->db->table('test')->limit(null)->buildSelectQuery()); + } + + public function testLimitOffset() + { + $this->assertEquals('SELECT * FROM "test" LIMIT 2 OFFSET 0', $this->db->table('test')->offset(0)->limit(2)->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test" LIMIT 5 OFFSET 10', $this->db->table('test')->offset(10)->limit(5)->buildSelectQuery()); + } + + public function testSubquery() + { + $this->assertEquals('SELECT (SELECT 1 FROM "foobar" WHERE 1=1) AS "b" FROM "test"', $this->db->table('test')->subquery('SELECT 1 FROM "foobar" WHERE 1=1', 'b')->buildSelectQuery()); + } + + public function testConditionEqual() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" = ? AND "b" = ?', $table->eq('a', 2)->eq('b', 'foobar')->buildSelectQuery()); + $this->assertEquals(array(2, 'foobar'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionNotEqual() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" != ?', $table->neq('a', 2)->buildSelectQuery()); + $this->assertEquals(array(2), $table->getConditionBuilder()->getValues()); + } + + public function testConditionIn() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" IN (?, ?)', $table->in('a', array('b', 'c'))->buildSelectQuery()); + $this->assertEquals(array('b', 'c'), $table->getConditionBuilder()->getValues()); + + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test"', $table->in('a', array())->buildSelectQuery()); + $this->assertEquals(array(), $table->getConditionBuilder()->getValues()); + } + + public function testConditionInSubquery() + { + $table = $this->db->table('test'); + $subquery = $this->db->table('test2')->columns('c')->eq('d', 'e'); + + $this->assertEquals( + 'SELECT * FROM "test" WHERE "a" IN (SELECT "c" FROM "test2" WHERE "d" = ?)', + $table->inSubquery('a', $subquery)->buildSelectQuery() + ); + + $this->assertEquals(array('e'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionNotIn() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" NOT IN (?, ?)', $table->notIn('a', array('b', 'c'))->buildSelectQuery()); + $this->assertEquals(array('b', 'c'), $table->getConditionBuilder()->getValues()); + + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test"', $table->notIn('a', array())->buildSelectQuery()); + $this->assertEquals(array(), $table->getConditionBuilder()->getValues()); + } + + public function testConditionNotInSubquery() + { + $table = $this->db->table('test'); + $subquery = $this->db->table('test2')->columns('c')->eq('d', 'e'); + + $this->assertEquals( + 'SELECT * FROM "test" WHERE "a" NOT IN (SELECT "c" FROM "test2" WHERE "d" = ?)', + $table->notInSubquery('a', $subquery)->buildSelectQuery() + ); + + $this->assertEquals(array('e'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionLike() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" LIKE ?', $table->like('a', '%foobar%')->buildSelectQuery()); + $this->assertEquals(array('%foobar%'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionILike() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" ILIKE ?', $table->ilike('a', '%foobar%')->buildSelectQuery()); + $this->assertEquals(array('%foobar%'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionGreaterThan() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" > ?', $table->gt('a', 5)->buildSelectQuery()); + $this->assertEquals(array(5), $table->getConditionBuilder()->getValues()); + } + + public function testConditionGreaterThanOrEqual() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" >= ?', $table->gte('a', 5)->buildSelectQuery()); + $this->assertEquals(array(5), $table->getConditionBuilder()->getValues()); + } + + public function testConditionLowerThan() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" < ?', $table->lt('a', 5)->buildSelectQuery()); + $this->assertEquals(array(5), $table->getConditionBuilder()->getValues()); + } + + public function testConditionLowerThanOrEqual() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" <= ?', $table->lte('a', 5)->buildSelectQuery()); + $this->assertEquals(array(5), $table->getConditionBuilder()->getValues()); + } + + public function testConditionIsNull() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" IS NOT NULL', $table->notNull('a')->buildSelectQuery()); + $this->assertEquals(array(), $table->getConditionBuilder()->getValues()); + } + + public function testCustomCondition() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE a=c AND "b" = ?', $table->addCondition('a=c')->eq('b', 4)->buildSelectQuery()); + $this->assertEquals(array(4), $table->getConditionBuilder()->getValues()); + } + + public function testOrConditions() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" IS NOT NULL AND ("b" = ? OR "c" >= ?)', $table->notNull('a')->beginOr()->eq('b', 2)->gte('c', 5)->closeOr()->buildSelectQuery()); + $this->assertEquals(array(2, 5), $table->getConditionBuilder()->getValues()); + } + + public function testPersist() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar_persist (id SERIAL PRIMARY KEY, a VARCHAR(10))')); + $this->assertSame(1, $this->db->table('foobar_persist')->persist(array('a' => 'b'))); + } + + public function testInsertUpdate() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar (a TEXT)')); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 'b'))); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 'c'))); + + $this->assertEquals(array(array('a' => 'b'), array('a' => 'c')), $this->db->table('foobar')->findAll()); + + $this->assertEquals(array('b', 'c'), $this->db->table('foobar')->findAllByColumn('a')); + + $this->assertEquals(array('a' => 'b'), $this->db->table('foobar')->findOne()); + + $this->assertEquals('b', $this->db->table('foobar')->findOneColumn('a')); + + $this->assertTrue($this->db->table('foobar')->exists()); + $this->assertTrue($this->db->table('foobar')->eq('a', 'c')->exists()); + $this->assertFalse($this->db->table('foobar')->eq('a', 'e')->exists()); + + $this->assertEquals(2, $this->db->table('foobar')->count()); + $this->assertEquals(1, $this->db->table('foobar')->eq('a', 'c')->count()); + $this->assertEquals(0, $this->db->table('foobar')->eq('a', 'e')->count()); + + $this->assertTrue($this->db->table('foobar')->eq('a', 'c')->remove()); + $this->assertFalse($this->db->table('foobar')->eq('a', 'e')->remove()); + + $this->assertTrue($this->db->table('foobar')->eq('a', 'b')->update(array('a' => 'test'))); + $this->assertTrue($this->db->table('foobar')->eq('a', 'lol')->update(array('a' => 'test'))); + + $this->assertNotEmpty($this->db->table('foobar')->eq('a', 'test')->findOne()); + $this->assertNull($this->db->table('foobar')->eq('a', 'lol')->findOne()); + + $this->assertTrue($this->db->table('foobar')->eq('a', 'test')->save(array('a' => 'plop'))); + $this->assertEquals(1, $this->db->table('foobar')->count()); + } + + public function testSumColumn() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar (b REAL, c REAL)')); + $this->assertTrue($this->db->table('foobar')->insert(array('b' => 2, 'c' => 3.3))); + + $this->assertTrue($this->db->table('foobar')->sumColumn('b', 2.5)->sumColumn('c', 3)->update()); + + $this->assertEquals( + array('b' => 4.5, 'c' => 6.3), + $this->db->table('foobar')->findOne() + ); + } + + public function testSum() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar (a INTEGER)')); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 2))); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 5))); + $this->assertEquals(7, $this->db->table('foobar')->sum('a')); + } + + public function testIncrement() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar (a INTEGER DEFAULT 0, b INTEGER DEFAULT 0)')); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 2, 'b' => 5))); + $this->assertTrue($this->db->table('foobar')->eq('b', 5)->increment('a', 3)); + $this->assertEquals(5, $this->db->table('foobar')->findOneColumn('a')); + } + + public function testLeftJoin() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE test1 (a INTEGER NOT NULL, foreign_key INTEGER NOT NULL)')); + $this->assertNotFalse($this->db->execute('CREATE TABLE test2 (id INTEGER NOT NULL, b INTEGER NOT NULL)')); + + $this->assertTrue($this->db->table('test2')->insert(array('id' => 42, 'b' => 2))); + $this->assertTrue($this->db->table('test1')->insert(array('a' => 18, 'foreign_key' => 42))); + + $this->assertEquals( + array('a' => 18, 'b' => 2), + $this->db->table('test2')->columns('a', 'b')->eq('a', 18)->left('test1', 't1', 'foreign_key', 'test2', 'id')->findOne() + ); + + $this->assertEquals( + array('a' => 18, 'b' => 2), + $this->db->table('test2')->columns('a', 'b')->eq('a', 18)->join('test1', 'foreign_key', 'id')->findOne() + ); + + $this->assertEquals( + array('a' => 18, 'b' => 2), + $this->db->table('test1')->columns('a', 'b')->join('test2', 'id', 'foreign_key')->findOne() + ); + } + + public function testHashTable() + { + $this->assertNotFalse($this->db->execute( + 'CREATE TABLE foobar ( + column1 TEXT NOT NULL UNIQUE, + column2 TEXT default NULL + )' + )); + + $this->assertTrue($this->db->table('foobar')->insert(array('column1' => 'option1', 'column2' => 'value1'))); + $this->assertTrue($this->db->table('foobar')->insert(array('column1' => 'option2', 'column2' => 'value2'))); + $this->assertTrue($this->db->table('foobar')->insert(array('column1' => 'option3', 'column2' => 'value3'))); + + $values = array( + 'option1' => 'hey', + 'option4' => 'ho', + ); + + $this->assertTrue($this->db->hashtable('foobar')->columnKey('column1')->columnValue('column2')->put($values)); + + $this->assertEquals( + array('option2' => 'value2', 'option4' => 'ho'), + $this->db->hashtable('foobar')->columnKey('column1')->columnValue('column2')->get('option2', 'option4') + ); + + $this->assertEquals( + array('option2' => 'value2', 'option3' => 'value3', 'option1' => 'hey', 'option4' => 'ho'), + $this->db->hashtable('foobar')->columnKey('column1')->columnValue('column2')->get() + ); + + $this->assertEquals( + array('option2' => 'value2', 'option3' => 'value3', 'option1' => 'hey', 'option4' => 'ho'), + $this->db->hashtable('foobar')->getAll('column1', 'column2') + ); + } +} diff --git a/libs/picodb/tests/SchemaFixture.php b/libs/picodb/tests/SchemaFixture.php new file mode 100644 index 00000000..fe4b5031 --- /dev/null +++ b/libs/picodb/tests/SchemaFixture.php @@ -0,0 +1,21 @@ +<?php + +namespace Schema; + +use PDO; + +function version_1(PDO $pdo) +{ + $pdo->exec('CREATE TABLE test1 (column1 TEXT)'); +} + +function version_2(PDO $pdo) +{ + $pdo->exec('CREATE TABLE test2 (column2 TEXT)'); +} + +function version_3(PDO $pdo) +{ + // Simulate an error + $pdo->exec('CREATE TABL'); +} diff --git a/libs/picodb/tests/SqliteDatabaseTest.php b/libs/picodb/tests/SqliteDatabaseTest.php new file mode 100644 index 00000000..628adb07 --- /dev/null +++ b/libs/picodb/tests/SqliteDatabaseTest.php @@ -0,0 +1,120 @@ +<?php + +require_once __DIR__.'/../../../vendor/autoload.php'; + +use PicoDb\Database; + +class SqliteDatabaseTest extends PHPUnit_Framework_TestCase +{ + /** + * @var PicoDb\Database + */ + private $db; + + public function setUp() + { + $this->db = new Database(array('driver' => 'sqlite', 'filename' => ':memory:')); + } + + public function testEscapeIdentifer() + { + $this->assertEquals('"a"', $this->db->escapeIdentifier('a')); + $this->assertEquals('a.b', $this->db->escapeIdentifier('a.b')); + $this->assertEquals('"c"."a"', $this->db->escapeIdentifier('a', 'c')); + $this->assertEquals('a.b', $this->db->escapeIdentifier('a.b', 'c')); + $this->assertEquals('SELECT COUNT(*) FROM test', $this->db->escapeIdentifier('SELECT COUNT(*) FROM test')); + $this->assertEquals('SELECT COUNT(*) FROM test', $this->db->escapeIdentifier('SELECT COUNT(*) FROM test', 'b')); + } + + public function testEscapeIdentiferList() + { + $this->assertEquals(array('"c"."a"', '"c"."b"'), $this->db->escapeIdentifierList(array('a', 'b'), 'c')); + $this->assertEquals(array('"a"', 'd.b'), $this->db->escapeIdentifierList(array('a', 'd.b'))); + } + + public function testThatPreparedStatementWorks() + { + $this->db->getConnection()->exec('CREATE TABLE foobar (id INTEGER PRIMARY KEY, something TEXT)'); + $this->db->execute('INSERT INTO foobar (something) VALUES (?)', array('a')); + $this->assertEquals(1, $this->db->getLastId()); + $this->assertEquals('a', $this->db->execute('SELECT something FROM foobar WHERE something=?', array('a'))->fetchColumn()); + } + + /** + * @expectedException PicoDb\SQLException + */ + public function testBadSQLQuery() + { + $this->db->execute('INSERT INTO foobar'); + } + + public function testDuplicateKey() + { + $this->db->getConnection()->exec('CREATE TABLE foobar (something TEXT UNIQUE)'); + + $this->assertNotFalse($this->db->execute('INSERT INTO foobar (something) VALUES (?)', array('a'))); + $this->assertFalse($this->db->execute('INSERT INTO foobar (something) VALUES (?)', array('a'))); + + $this->assertEquals(1, $this->db->execute('SELECT COUNT(*) FROM foobar WHERE something=?', array('a'))->fetchColumn()); + } + + public function testThatTransactionReturnsAValue() + { + $this->assertEquals('a', $this->db->transaction(function (Database $db) { + $db->getConnection()->exec('CREATE TABLE foobar (something TEXT UNIQUE)'); + $db->execute('INSERT INTO foobar (something) VALUES (?)', array('a')); + + return $db->execute('SELECT something FROM foobar WHERE something=?', array('a'))->fetchColumn(); + })); + } + + public function testThatTransactionReturnsTrue() + { + $this->assertTrue($this->db->transaction(function (Database $db) { + $db->getConnection()->exec('CREATE TABLE foobar (something TEXT UNIQUE)'); + $db->execute('INSERT INTO foobar (something) VALUES (?)', array('a')); + })); + } + + /** + * @expectedException PicoDb\SQLException + */ + public function testThatTransactionThrowExceptionWhenRollbacked() + { + $this->assertFalse($this->db->transaction(function (Database $db) { + $db->getConnection()->exec('CREATE TABL'); + })); + } + + public function testThatTransactionReturnsFalseWhithDuplicateKey() + { + $this->assertFalse($this->db->transaction(function (Database $db) { + $db->getConnection()->exec('CREATE TABLE foobar (something TEXT UNIQUE)'); + $r1 = $db->execute('INSERT INTO foobar (something) VALUES (?)', array('a')); + $r2 = $db->execute('INSERT INTO foobar (something) VALUES (?)', array('a')); + return $r1 && $r2; + })); + } + + public function testGetInstance() + { + Database::setInstance('main', function () { + return new Database(array('driver' => 'sqlite', 'filename' => ':memory:')); + }); + + $instance1 = Database::getInstance('main'); + $instance2 = Database::getInstance('main'); + + $this->assertInstanceOf('PicoDb\Database', $instance1); + $this->assertInstanceOf('PicoDb\Database', $instance2); + $this->assertTrue($instance1 === $instance2); + } + + /** + * @expectedException LogicException + */ + public function testGetMissingInstance() + { + Database::getInstance('notfound'); + } +} diff --git a/libs/picodb/tests/SqliteDriverTest.php b/libs/picodb/tests/SqliteDriverTest.php new file mode 100644 index 00000000..9965a39c --- /dev/null +++ b/libs/picodb/tests/SqliteDriverTest.php @@ -0,0 +1,70 @@ +<?php + +require_once __DIR__.'/../../../vendor/autoload.php'; + +use PicoDb\Driver\Sqlite; + +class SqliteDriverTest extends PHPUnit_Framework_TestCase +{ + /** + * @var PicoDb\Driver\Sqlite + */ + private $driver; + + public function setUp() + { + $this->driver = new Sqlite(array('filename' => ':memory:')); + } + + /** + * @expectedException LogicException + */ + public function testMissingRequiredParameter() + { + new Sqlite(array()); + } + + public function testDuplicateKeyError() + { + $this->assertFalse($this->driver->isDuplicateKeyError(1234)); + $this->assertTrue($this->driver->isDuplicateKeyError(23000)); + } + + public function testOperator() + { + $this->assertEquals('LIKE', $this->driver->getOperator('LIKE')); + $this->assertEquals('LIKE', $this->driver->getOperator('ILIKE')); + $this->assertEquals('', $this->driver->getOperator('FOO')); + } + + public function testSchemaVersion() + { + $this->assertEquals(0, $this->driver->getSchemaVersion()); + + $this->driver->setSchemaVersion(1); + $this->assertEquals(1, $this->driver->getSchemaVersion()); + + $this->driver->setSchemaVersion(42); + $this->assertEquals(42, $this->driver->getSchemaVersion()); + } + + public function testLastInsertId() + { + $this->assertEquals(0, $this->driver->getLastId()); + + $this->driver->getConnection()->exec('CREATE TABLE foobar (id INTEGER PRIMARY KEY, something TEXT)'); + $this->driver->getConnection()->exec('INSERT INTO foobar (something) VALUES (1)'); + + $this->assertEquals(1, $this->driver->getLastId()); + } + + public function testEscape() + { + $this->assertEquals('"foobar"', $this->driver->escape('foobar')); + } + + public function testDatabaseVersion() + { + $this->assertStringStartsWith('3.', $this->driver->getDatabaseVersion()); + } +} diff --git a/libs/picodb/tests/SqliteLobtest.php b/libs/picodb/tests/SqliteLobtest.php new file mode 100644 index 00000000..d0889655 --- /dev/null +++ b/libs/picodb/tests/SqliteLobtest.php @@ -0,0 +1,84 @@ +<?php + +require_once __DIR__.'/../../../vendor/autoload.php'; + +use PicoDb\Database; + +class SqliteLobTest extends PHPUnit_Framework_TestCase +{ + /** + * @var PicoDb\Database + */ + private $db; + + public function setUp() + { + $this->db = new Database(array('driver' => 'sqlite', 'filename' => ':memory:')); + $this->db->getConnection()->exec('DROP TABLE IF EXISTS large_objects'); + $this->db->getConnection()->exec('CREATE TABLE large_objects (id VARCHAR(20), file_content BLOB)'); + } + + public function testInsert() + { + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__, array('id' => 'test')); + $this->assertTrue($result); + } + + public function testInsertFromString() + { + $data = 'test'; + $result = $this->db->largeObject('large_objects')->insertFromString('file_content', $data, array('id' => 'test')); + $this->assertTrue($result); + } + + public function testInsertWithOptionalParams() + { + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__); + $this->assertTrue($result); + } + + public function testFindOneColumnAsStream() + { + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__, array('id' => 'test')); + $this->assertTrue($result); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test')->findOneColumnAsStream('file_content'); + $this->assertSame(md5(file_get_contents(__FILE__)), md5($contents)); + } + + public function testFindOneColumnAsString() + { + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__, array('id' => 'test')); + $this->assertTrue($result); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test')->findOneColumnAsString('file_content'); + $this->assertSame(md5(file_get_contents(__FILE__)), md5($contents)); + } + + public function testUpdate() + { + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__, array('id' => 'test1')); + $this->assertTrue($result); + + $result = $this->db->largeObject('large_objects')->insertFromFile('file_content', __FILE__, array('id' => 'test2')); + $this->assertTrue($result); + + $result = $this->db->largeObject('large_objects')->eq('id', 'test1')->updateFromFile('file_content', __DIR__.'/../LICENSE'); + $this->assertTrue($result); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test1')->findOneColumnAsString('file_content'); + $this->assertSame(md5(file_get_contents(__DIR__.'/../LICENSE')), md5($contents)); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test2')->findOneColumnAsString('file_content'); + $this->assertSame(md5(file_get_contents(__FILE__)), md5($contents)); + + $result = $this->db->largeObject('large_objects')->updateFromFile('file_content', __DIR__.'/../composer.json'); + $this->assertTrue($result); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test1')->findOneColumnAsString('file_content'); + $this->assertSame(md5(file_get_contents(__DIR__.'/../composer.json')), md5($contents)); + + $contents = $this->db->largeObject('large_objects')->eq('id', 'test2')->findOneColumnAsString('file_content'); + $this->assertSame(md5(file_get_contents(__DIR__.'/../composer.json')), md5($contents)); + } +} diff --git a/libs/picodb/tests/SqliteSchemaTest.php b/libs/picodb/tests/SqliteSchemaTest.php new file mode 100644 index 00000000..7522e10d --- /dev/null +++ b/libs/picodb/tests/SqliteSchemaTest.php @@ -0,0 +1,36 @@ +<?php + +require_once __DIR__.'/../../../vendor/autoload.php'; +require_once __DIR__.'/SchemaFixture.php'; + +class SqliteSchemaTest extends PHPUnit_Framework_TestCase +{ + /** + * @var PicoDb\Database + */ + private $db; + + public function setUp() + { + $this->db = new PicoDb\Database(array('driver' => 'sqlite', 'filename' => ':memory:')); + } + + public function testMigrations() + { + $this->assertTrue($this->db->schema()->check(2)); + $this->assertEquals(2, $this->db->getDriver()->getSchemaVersion()); + } + + public function testFailedMigrations() + { + $this->assertFalse($this->db->schema()->check(3)); + $this->assertEquals(2, $this->db->getDriver()->getSchemaVersion()); + + $logs = $this->db->getLogMessages(); + $this->assertNotEmpty($logs); + $this->assertEquals('Running migration \Schema\version_1', $logs[0]); + $this->assertEquals('Running migration \Schema\version_2', $logs[1]); + $this->assertEquals('Running migration \Schema\version_3', $logs[2]); + $this->assertEquals('SQLSTATE[HY000]: General error: 1 near "TABL": syntax error', $logs[3]); + } +} diff --git a/libs/picodb/tests/SqliteTableTest.php b/libs/picodb/tests/SqliteTableTest.php new file mode 100644 index 00000000..dae718fa --- /dev/null +++ b/libs/picodb/tests/SqliteTableTest.php @@ -0,0 +1,444 @@ +<?php + +require_once __DIR__.'/../../../vendor/autoload.php'; + +use PicoDb\Database; +use PicoDb\Table; + +class SqliteTableTest extends PHPUnit_Framework_TestCase +{ + /** + * @var PicoDb\Database + */ + private $db; + + public function setUp() + { + $this->db = new Database(array('driver' => 'sqlite', 'filename' => ':memory:')); + } + + public function testSelect() + { + $this->assertEquals('SELECT 1 FROM "test"', $this->db->table('test')->select(1)->buildSelectQuery()); + } + + public function testColumns() + { + $this->assertEquals('SELECT "a", "b" FROM "test"', $this->db->table('test')->columns('a', 'b')->buildSelectQuery()); + } + + public function testDistinct() + { + $this->assertEquals('SELECT DISTINCT "a", "b" FROM "test"', $this->db->table('test')->distinct('a', 'b')->buildSelectQuery()); + } + + public function testGroupBy() + { + $this->assertEquals('SELECT * FROM "test" GROUP BY "a"', $this->db->table('test')->groupBy('a')->buildSelectQuery()); + } + + public function testOrderBy() + { + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" ASC', $this->db->table('test')->asc('a')->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" ASC', $this->db->table('test')->orderBy('a', Table::SORT_ASC)->buildSelectQuery()); + + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" DESC', $this->db->table('test')->desc('a')->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" DESC', $this->db->table('test')->orderBy('a', Table::SORT_DESC)->buildSelectQuery()); + + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" ASC, "b" ASC', $this->db->table('test')->asc('a')->asc('b')->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" DESC, "b" DESC', $this->db->table('test')->desc('a')->desc('b')->buildSelectQuery()); + + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" ASC, "b" ASC', $this->db->table('test')->orderBy('a')->orderBy('b')->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" DESC, "b" DESC', $this->db->table('test')->orderBy('a', Table::SORT_DESC)->orderBy('b', Table::SORT_DESC)->buildSelectQuery()); + + $this->assertEquals('SELECT * FROM "test" ORDER BY "a" DESC, "b" ASC', $this->db->table('test')->desc('a')->asc('b')->buildSelectQuery()); + } + + public function testLimit() + { + $this->assertEquals('SELECT * FROM "test" LIMIT 10', $this->db->table('test')->limit(10)->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test"', $this->db->table('test')->limit(null)->buildSelectQuery()); + } + + public function testOffset() + { + $this->assertEquals('SELECT * FROM "test" OFFSET 0', $this->db->table('test')->offset(0)->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test" OFFSET 10', $this->db->table('test')->offset(10)->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test"', $this->db->table('test')->limit(null)->buildSelectQuery()); + } + + public function testLimitOffset() + { + $this->assertEquals('SELECT * FROM "test" LIMIT 2 OFFSET 0', $this->db->table('test')->offset(0)->limit(2)->buildSelectQuery()); + $this->assertEquals('SELECT * FROM "test" LIMIT 5 OFFSET 10', $this->db->table('test')->offset(10)->limit(5)->buildSelectQuery()); + } + + public function testSubquery() + { + $this->assertEquals('SELECT (SELECT 1 FROM "foobar" WHERE 1=1) AS "b" FROM "test"', $this->db->table('test')->subquery('SELECT 1 FROM "foobar" WHERE 1=1', 'b')->buildSelectQuery()); + } + + public function testConditionEqual() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" = ? AND "b" = ?', $table->eq('a', 2)->eq('b', 'foobar')->buildSelectQuery()); + $this->assertEquals(array(2, 'foobar'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionNotEqual() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" != ?', $table->neq('a', 2)->buildSelectQuery()); + $this->assertEquals(array(2), $table->getConditionBuilder()->getValues()); + } + + public function testConditionIn() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" IN (?, ?)', $table->in('a', array('b', 'c'))->buildSelectQuery()); + $this->assertEquals(array('b', 'c'), $table->getConditionBuilder()->getValues()); + + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test"', $table->in('a', array())->buildSelectQuery()); + $this->assertEquals(array(), $table->getConditionBuilder()->getValues()); + } + + public function testConditionInSubquery() + { + $table = $this->db->table('test'); + $subquery = $this->db->table('test2')->columns('c')->eq('d', 'e'); + + $this->assertEquals( + 'SELECT * FROM "test" WHERE "a" IN (SELECT "c" FROM "test2" WHERE "d" = ?)', + $table->inSubquery('a', $subquery)->buildSelectQuery() + ); + + $this->assertEquals(array('e'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionNotIn() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" NOT IN (?, ?)', $table->notIn('a', array('b', 'c'))->buildSelectQuery()); + $this->assertEquals(array('b', 'c'), $table->getConditionBuilder()->getValues()); + + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test"', $table->notIn('a', array())->buildSelectQuery()); + $this->assertEquals(array(), $table->getConditionBuilder()->getValues()); + } + + public function testConditionNotInSubquery() + { + $table = $this->db->table('test'); + $subquery = $this->db->table('test2')->columns('c')->eq('d', 'e'); + + $this->assertEquals( + 'SELECT * FROM "test" WHERE "a" NOT IN (SELECT "c" FROM "test2" WHERE "d" = ?)', + $table->notInSubquery('a', $subquery)->buildSelectQuery() + ); + + $this->assertEquals(array('e'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionLike() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" LIKE ?', $table->like('a', '%foobar%')->buildSelectQuery()); + $this->assertEquals(array('%foobar%'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionILike() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" LIKE ?', $table->ilike('a', '%foobar%')->buildSelectQuery()); + $this->assertEquals(array('%foobar%'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionGreaterThan() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" > ?', $table->gt('a', 5)->buildSelectQuery()); + $this->assertEquals(array(5), $table->getConditionBuilder()->getValues()); + } + + public function testConditionGreaterThanInSubquery() + { + $table = $this->db->table('test'); + $subquery = $this->db->table('test2')->columns('c')->eq('d', 'e'); + + $this->assertEquals( + 'SELECT * FROM "test" WHERE "a" > (SELECT "c" FROM "test2" WHERE "d" = ?)', + $table->gtSubquery('a', $subquery)->buildSelectQuery() + ); + + $this->assertEquals(array('e'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionGreaterThanOrEqual() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" >= ?', $table->gte('a', 5)->buildSelectQuery()); + $this->assertEquals(array(5), $table->getConditionBuilder()->getValues()); + } + + public function testConditionGreaterThanEqualInSubquery() + { + $table = $this->db->table('test'); + $subquery = $this->db->table('test2')->columns('c')->eq('d', 'e'); + + $this->assertEquals( + 'SELECT * FROM "test" WHERE "a" >= (SELECT "c" FROM "test2" WHERE "d" = ?)', + $table->gteSubquery('a', $subquery)->buildSelectQuery() + ); + + $this->assertEquals(array('e'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionLowerThan() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" < ?', $table->lt('a', 5)->buildSelectQuery()); + $this->assertEquals(array(5), $table->getConditionBuilder()->getValues()); + } + + public function testConditionLowerThanInSubquery() + { + $table = $this->db->table('test'); + $subquery = $this->db->table('test2')->columns('c')->eq('d', 'e'); + + $this->assertEquals( + 'SELECT * FROM "test" WHERE "a" < (SELECT "c" FROM "test2" WHERE "d" = ?)', + $table->ltSubquery('a', $subquery)->buildSelectQuery() + ); + + $this->assertEquals(array('e'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionLowerThanOrEqual() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" <= ?', $table->lte('a', 5)->buildSelectQuery()); + $this->assertEquals(array(5), $table->getConditionBuilder()->getValues()); + } + + public function testConditionLowerThanEqualInSubquery() + { + $table = $this->db->table('test'); + $subquery = $this->db->table('test2')->columns('c')->eq('d', 'e'); + + $this->assertEquals( + 'SELECT * FROM "test" WHERE "a" <= (SELECT "c" FROM "test2" WHERE "d" = ?)', + $table->lteSubquery('a', $subquery)->buildSelectQuery() + ); + + $this->assertEquals(array('e'), $table->getConditionBuilder()->getValues()); + } + + public function testConditionIsNull() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" IS NOT NULL', $table->notNull('a')->buildSelectQuery()); + $this->assertEquals(array(), $table->getConditionBuilder()->getValues()); + } + + public function testCustomCondition() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE a=c AND "b" = ?', $table->addCondition('a=c')->eq('b', 4)->buildSelectQuery()); + $this->assertEquals(array(4), $table->getConditionBuilder()->getValues()); + } + + public function testOrConditions() + { + $table = $this->db->table('test'); + + $this->assertEquals('SELECT * FROM "test" WHERE "a" IS NOT NULL AND ("b" = ? OR "c" >= ?)', $table->notNull('a')->beginOr()->eq('b', 2)->gte('c', 5)->closeOr()->buildSelectQuery()); + $this->assertEquals(array(2, 5), $table->getConditionBuilder()->getValues()); + } + + public function testMultipleOrConditions() + { + $table = $this->db->table('test'); + + $this->assertEquals( + 'SELECT * FROM "test" WHERE "a" IS NOT NULL AND ("b" = ? OR ("b" != ? OR "c" = ?) OR "c" >= ?)', + $table + ->notNull('a') + ->beginOr() + ->eq('b', 2) + ->beginOr() + ->neq('b', 6) + ->eq('c', 3) + ->closeOr() + ->gte('c', 5) + ->closeOr() + ->buildSelectQuery() + ); + + $this->assertEquals(array(2, 6, 3, 5), $table->getConditionBuilder()->getValues()); + } + + public function testPersist() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar_persist (id INTEGER PRIMARY KEY, a TEXT)')); + $this->assertSame(1, $this->db->table('foobar_persist')->persist(array('a' => 'b'))); + } + + public function testInsertUpdate() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar (a TEXT)')); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 'b'))); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 'c'))); + + $this->assertEquals(array(array('a' => 'b'), array('a' => 'c')), $this->db->table('foobar')->findAll()); + + $this->assertEquals(array('b', 'c'), $this->db->table('foobar')->findAllByColumn('a')); + + $this->assertEquals(array('a' => 'b'), $this->db->table('foobar')->findOne()); + + $this->assertEquals('b', $this->db->table('foobar')->findOneColumn('a')); + + $this->assertTrue($this->db->table('foobar')->exists()); + $this->assertTrue($this->db->table('foobar')->eq('a', 'c')->exists()); + $this->assertFalse($this->db->table('foobar')->eq('a', 'e')->exists()); + + $this->assertEquals(2, $this->db->table('foobar')->count()); + $this->assertEquals(1, $this->db->table('foobar')->eq('a', 'c')->count()); + $this->assertEquals(0, $this->db->table('foobar')->eq('a', 'e')->count()); + + $this->assertTrue($this->db->table('foobar')->eq('a', 'c')->remove()); + $this->assertFalse($this->db->table('foobar')->eq('a', 'e')->remove()); + + $this->assertTrue($this->db->table('foobar')->eq('a', 'b')->update(array('a' => 'test'))); + $this->assertTrue($this->db->table('foobar')->eq('a', 'lol')->update(array('a' => 'test'))); + + $this->assertNotEmpty($this->db->table('foobar')->eq('a', 'test')->findOne()); + $this->assertNull($this->db->table('foobar')->eq('a', 'lol')->findOne()); + + $this->assertTrue($this->db->table('foobar')->eq('a', 'test')->save(array('a' => 'plop'))); + $this->assertEquals(1, $this->db->table('foobar')->count()); + } + + public function testSumColumn() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar (b REAL, c REAL)')); + $this->assertTrue($this->db->table('foobar')->insert(array('b' => 2, 'c' => 3.3))); + + $this->assertTrue($this->db->table('foobar')->sumColumn('b', 2.5)->sumColumn('c', 3)->update()); + + $this->assertEquals( + array('b' => 4.5, 'c' => 6.3), + $this->db->table('foobar')->findOne() + ); + } + + public function testCallback() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar (a TEXT)')); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 'b'))); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 'c'))); + + $func = function () { + return array('test'); + }; + + $this->assertEquals(array('test'), $this->db->table('foobar')->callback($func)->findAll()); + $this->assertEquals(array('plop'), $this->db->table('foobar')->callback(array($this, 'myCallback'))->findAll()); + } + + public function myCallback(array $records) + { + $this->assertEquals(array(array('a' => 'b'), array('a' => 'c')), $records); + return array('plop'); + } + + public function testSum() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar (a INTEGER)')); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 2))); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 5))); + $this->assertEquals(7, $this->db->table('foobar')->sum('a')); + } + + public function testIncrement() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE foobar (a INTEGER DEFAULT 0, b INTEGER DEFAULT 0)')); + $this->assertTrue($this->db->table('foobar')->insert(array('a' => 2, 'b' => 5))); + $this->assertTrue($this->db->table('foobar')->eq('b', 5)->increment('a', 3)); + $this->assertEquals(5, $this->db->table('foobar')->findOneColumn('a')); + } + + public function testLeftJoin() + { + $this->assertNotFalse($this->db->execute('CREATE TABLE test1 (a INTEGER NOT NULL, foreign_key INTEGER NOT NULL)')); + $this->assertNotFalse($this->db->execute('CREATE TABLE test2 (id INTEGER NOT NULL, b INTEGER NOT NULL)')); + + $this->assertTrue($this->db->table('test2')->insert(array('id' => 42, 'b' => 2))); + $this->assertTrue($this->db->table('test1')->insert(array('a' => 18, 'foreign_key' => 42))); + + $this->assertEquals( + array('a' => 18, 'b' => 2), + $this->db->table('test2')->columns('a', 'b')->eq('a', 18)->left('test1', 't1', 'foreign_key', 'test2', 'id')->findOne() + ); + + $this->assertEquals( + array('a' => 18, 'b' => 2), + $this->db->table('test2')->columns('a', 'b')->eq('a', 18)->join('test1', 'foreign_key', 'id')->findOne() + ); + + $this->assertEquals( + array('a' => 18, 'b' => 2), + $this->db->table('test1')->columns('a', 'b')->join('test2', 'id', 'foreign_key')->findOne() + ); + } + + public function testHashTable() + { + $this->assertNotFalse($this->db->execute( + 'CREATE TABLE toto ( + column1 TEXT NOT NULL UNIQUE, + column2 TEXT default NULL + )' + )); + + $this->assertTrue($this->db->table('toto')->insert(array('column1' => 'option1', 'column2' => 'value1'))); + $this->assertTrue($this->db->table('toto')->insert(array('column1' => 'option2', 'column2' => 'value2'))); + $this->assertTrue($this->db->table('toto')->insert(array('column1' => 'option3', 'column2' => 'value3'))); + + $values = array( + 'option1' => 'hey', + 'option4' => 'ho', + ); + + $this->assertTrue($this->db->hashtable('toto')->columnKey('column1')->columnValue('column2')->put($values)); + + $this->assertEquals( + array('option2' => 'value2', 'option4' => 'ho'), + $this->db->hashtable('toto')->columnKey('column1')->columnValue('column2')->get('option2', 'option4') + ); + + $this->assertEquals( + array('option2' => 'value2', 'option3' => 'value3', 'option1' => 'hey', 'option4' => 'ho'), + $this->db->hashtable('toto')->columnKey('column1')->columnValue('column2')->get() + ); + + $this->assertEquals( + array('option2' => 'value2', 'option3' => 'value3', 'option1' => 'hey', 'option4' => 'ho'), + $this->db->hashtable('toto')->getAll('column1', 'column2') + ); + } +} diff --git a/libs/picodb/tests/UrlParserTest.php b/libs/picodb/tests/UrlParserTest.php new file mode 100644 index 00000000..ede3d3a5 --- /dev/null +++ b/libs/picodb/tests/UrlParserTest.php @@ -0,0 +1,46 @@ +<?php + +use PicoDb\UrlParser; + +require_once __DIR__.'/../../../vendor/autoload.php'; + +class UrlParserTest extends PHPUnit_Framework_TestCase +{ + public function testParseUrl() + { + $urlParser = UrlParser::getInstance(); + $this->assertFalse($urlParser->isEnvironmentVariableDefined()); + + $settings = $urlParser->getSettings('postgres://user:pass@hostname:6212/db'); + $this->assertEquals('postgres', $settings['driver']); + $this->assertEquals('user', $settings['username']); + $this->assertEquals('pass', $settings['password']); + $this->assertEquals('hostname', $settings['hostname']); + $this->assertEquals('6212', $settings['port']); + $this->assertEquals('db', $settings['database']); + } + + public function testParseWrongUrl() + { + $urlParser = new UrlParser(); + $settings = $urlParser->getSettings('/'); + $this->assertEmpty($settings['driver']); + $this->assertFalse($urlParser->isEnvironmentVariableDefined()); + } + + public function testGetUrlFromEnvironment() + { + putenv('DATABASE_URL=postgres://user:pass@hostname:6212/db'); + + $urlParser = new UrlParser(); + $this->assertTrue($urlParser->isEnvironmentVariableDefined()); + + $settings = $urlParser->getSettings(); + $this->assertEquals('postgres', $settings['driver']); + $this->assertEquals('user', $settings['username']); + $this->assertEquals('pass', $settings['password']); + $this->assertEquals('hostname', $settings['hostname']); + $this->assertEquals('6212', $settings['port']); + $this->assertEquals('db', $settings['database']); + } +} |