diff options
| -rw-r--r-- | README.markdown | 12 | ||||
| -rw-r--r-- | docs/configuration.rst | 34 | ||||
| -rw-r--r-- | docs/querying.rst | 32 | ||||
| -rw-r--r-- | idiorm.php | 71 | ||||
| -rw-r--r-- | phpunit.xml | 1 | ||||
| -rw-r--r-- | test/ConfigTest.php | 27 | ||||
| -rw-r--r-- | test/ConfigTest53.php | 36 | ||||
| -rw-r--r-- | test/QueryBuilderPsr1Test53.php | 554 | ||||
| -rw-r--r-- | test/QueryBuilderTest.php | 12 | ||||
| -rw-r--r-- | test/bootstrap.php | 11 |
10 files changed, 779 insertions, 11 deletions
diff --git a/README.markdown b/README.markdown index 0b9bb9a..c06b09c 100644 --- a/README.markdown +++ b/README.markdown @@ -25,6 +25,7 @@ Features * Database agnostic. Currently supports SQLite, MySQL, Firebird and PostgreSQL. May support others, please give it a try! * Supports collections of models with method chaining to filter or apply actions to multiple results at once. * Multiple connections supported +* PSR-1 compliant methods (any method can be called in camelCase instead of underscores eg. `find_many()` becomes `findMany()`) - you'll need PHP 5.3+ Documentation ------------- @@ -66,6 +67,17 @@ foreach ($tweets as $tweet) { Changelog --------- +#### 1.4.0 - release 2013-XX-XX + +* Add PSR-1 compliant camelCase method calls to Idiorm (PHP 5.3+ required) [[crhayes](https://github.com/crhayes)] - [issue #108](https://github.com/j4mie/idiorm/issues/108) +* Add static method `get_config()` to access current configuration [[javierd](https://github.com/mikejestes)] - [issue #141](https://github.com/j4mie/idiorm/issues/141) +* Add logging callback functionality [[lalop](https://github.com/lalop)] - [issue #130](https://github.com/j4mie/idiorm/issues/130) +* Uses table aliases in `WHERE` clauses [[vicvicvic](https://github.com/vicvicvic)] - [issue #140](https://github.com/j4mie/idiorm/issues/140) +* Ignore result columns when calling an aggregate function [[tassoevan](https://github.com/tassoevan)] - [issue #120](https://github.com/j4mie/idiorm/issues/120) +* Improve documentation [[bruston](https://github.com/bruston)] - [issue #111](https://github.com/j4mie/idiorm/issues/111) +* Improve PHPDoc on `get_db()` [[mailopl](https://github.com/mailopl)] - [issue #106](https://github.com/j4mie/idiorm/issues/106) +* Improve documentation [[sjparsons](https://github.com/sjparsons)] - [issue #103](https://github.com/j4mie/idiorm/issues/103) + #### 1.3.0 - release 2013-01-31 * Documentation moved to [idiorm.rtfd.org](http://idiorm.rtfd.org) and now built using [Sphinx](http://sphinx-doc.org/) diff --git a/docs/configuration.rst b/docs/configuration.rst index 395a1eb..a1c356a 100644 --- a/docs/configuration.rst +++ b/docs/configuration.rst @@ -65,6 +65,16 @@ once. 'etc' => 'etc' )); +Use the ``get_config`` method to read current settings. + +.. code-block:: php + + <?php + $isLoggingEnabled = ORM::get_config('logging'); + ORM::configure('logging', false); + // some crazy loop we don't want to log + ORM::configure('logging', $isLoggingEnabled); + Database authentication details ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ @@ -215,6 +225,30 @@ the log. ``ORM::get_last_query()`` returns the most recent query executed. ``ORM::get_query_log()`` returns an array of all queries executed. +Query logger +^^^^^^^^^^^^ + +Setting: ``logger`` + +.. note:: + + You must enable ``logging`` for this setting to have any effect. + +It is possible to supply a ``callable`` to this configuration setting, which will +be executed for every query that idiorm executes. In PHP a ``callable`` is anything +that can be executed as if it were a function. Most commonly this will take the +form of a anonymous function. + +This setting is useful if you wish to log queries with an external library as it +allows you too whatever you would like from inside the callback function. + +.. code-block:: php + + <?php + ORM::configure('logger', function($log_string) { + echo $log_string; + }); + Query caching ^^^^^^^^^^^^^ diff --git a/docs/querying.rst b/docs/querying.rst index 1e5afd3..a460a6a 100644 --- a/docs/querying.rst +++ b/docs/querying.rst @@ -27,6 +27,38 @@ which contains the columns ``id`` (the primary key of the record - Idiorm assumes the primary key column is called ``id`` but this is configurable, see below), ``name``, ``age`` and ``gender``. +A note on PSR-1 and camelCase +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +All the methods detailed in the documentation can also be called in a PSR-1 way: +underscores (_) become camelCase. Here follows an example of one query chain +being converted to a PSR-1 compliant style. + +.. code-block:: php + + <?php + // documented and default style + $person = ORM::for_table('person')->where('name', 'Fred Bloggs')->find_one(); + + // PSR-1 compliant style + $person = ORM::forTable('person')->where('name', 'Fred Bloggs')->findOne(); + +As you can see any method can be changed from the documented underscore (_) format +to that of a camelCase method name. + +.. note:: + + In the background the PSR-1 compliant style uses the `__call()` and + `__callStatic()` magic methods to map the camelCase method name you supply + to the original underscore method name. It then uses `call_user_func_array()` + to apply the arguments to the method. If this minimal overhead is too great + then you can simply revert to using the underscore methods to avoid it. In + general this will not be a bottle neck in any application however and should + be considered a micro-optimisation. + + As `__callStatic()` was added in PHP 5.3.0 you will need at least that version + of PHP to use this feature in any meaningful way. + Single records ^^^^^^^^^^^^^^ @@ -65,6 +65,7 @@ 'driver_options' => null, 'identifier_quote_character' => null, // if this is null, will be autodetected 'logging' => false, + 'logger' => null, 'caching' => false, 'return_result_sets' => false, ); @@ -197,6 +198,19 @@ } /** + * Retrieve configuration options by key, or as whole array. + * @param string $key + * @param string $connection_name Which connection to use + */ + public static function get_config($key = null, $connection_name = self::DEFAULT_CONNECTION) { + if ($key) { + return self::$_config[$connection_name][$key]; + } else { + return self::$_config[$connection_name]; + } + } + + /** * Despite its slightly odd name, this is actually the factory * method used to acquire instances of the class. It is named * this way for the sake of a readable interface, ie @@ -394,6 +408,13 @@ self::$_last_query = $bound_query; self::$_query_log[$connection_name][] = $bound_query; + + + if(is_callable(self::$_config[$connection_name]['logger'])){ + $logger = self::$_config[$connection_name]['logger']; + $logger($bound_query); + } + return true; } @@ -616,8 +637,11 @@ if('*' != $column) { $column = $this->_quote_identifier($column); } + $result_columns = $this->_result_columns; + $this->_result_columns = array(); $this->select_expr("$sql_function($column)", $alias); $result = $this->find_one(); + $this->_result_columns = $result_columns; $return_value = 0; if($result !== false && isset($result->$alias)) { @@ -936,7 +960,12 @@ protected function _add_simple_condition($type, $column_name, $separator, $value) { // Add the table name in case of ambiguous columns if (count($this->_join_sources) > 0 && strpos($column_name, '.') === false) { - $column_name = "{$this->_table_name}.{$column_name}"; + $table = $this->_table_name; + if (!is_null($this->_table_alias)) { + $table = $this->_table_alias; + } + + $column_name = "{$table}.{$column_name}"; } $column_name = $this->_quote_identifier($column_name); return $this->_add_condition($type, "{$column_name} {$separator} ?", $value); @@ -1657,7 +1686,7 @@ } } - $this->_dirty_fields = array(); + $this->_dirty_fields = $this->_expr_fields = array(); return $success; } @@ -1775,6 +1804,44 @@ public function __isset($key) { return $this->offsetExists($key); } + + /** + * Magic method to capture calls to undefined class methods. + * In this case we are attempting to convert camel case formatted + * methods into underscore formatted methods. + * + * This allows us to call ORM methods using camel case and remain + * backwards compatible. + * + * @param string $name + * @param array $arguments + * @return ORM + */ + public function __call($name, $arguments) + { + $method = strtolower(preg_replace('/([a-z])([A-Z])/', '$1_$2', $name)); + + return call_user_func_array(array($this, $method), $arguments); + } + + /** + * Magic method to capture calls to undefined static class methods. + * In this case we are attempting to convert camel case formatted + * methods into underscore formatted methods. + * + * This allows us to call ORM methods using camel case and remain + * backwards compatible. + * + * @param string $name + * @param array $arguments + * @return ORM + */ + public static function __callStatic($name, $arguments) + { + $method = strtolower(preg_replace('/([a-z])([A-Z])/', '$1_$2', $name)); + + return call_user_func_array(array('ORM', $method), $arguments); + } } /** diff --git a/phpunit.xml b/phpunit.xml index 4718e62..efbc81c 100644 --- a/phpunit.xml +++ b/phpunit.xml @@ -6,6 +6,7 @@ <testsuites> <testsuite name="Idiorm Test Suite"> <directory suffix="Test.php">test</directory> + <directory suffix="Test53.php" phpVersion="5.3.0" phpVersionOperator=">=">test</directory> </testsuite> </testsuites> </phpunit>
\ No newline at end of file diff --git a/test/ConfigTest.php b/test/ConfigTest.php index 83194b4..7644181 100644 --- a/test/ConfigTest.php +++ b/test/ConfigTest.php @@ -97,4 +97,29 @@ class ConfigTest extends PHPUnit_Framework_TestCase { $this->tearDownIdColumnOverrides(); } -}
\ No newline at end of file + public function testGetConfig() { + $this->assertTrue(ORM::get_config('logging')); + ORM::configure('logging', false); + $this->assertFalse(ORM::get_config('logging')); + ORM::configure('logging', true); + } + + public function testGetConfigArray() { + $expected = array( + 'connection_string' => 'sqlite::memory:', + 'id_column' => 'primary_key', + 'id_column_overrides' => array(), + 'error_mode' => PDO::ERRMODE_EXCEPTION, + 'username' => null, + 'password' => null, + 'driver_options' => null, + 'identifier_quote_character' => '`', + 'logging' => true, + 'logger' => null, + 'caching' => false, + 'return_result_sets' => false, + ); + $this->assertEquals($expected, ORM::get_config()); + } + +} diff --git a/test/ConfigTest53.php b/test/ConfigTest53.php new file mode 100644 index 0000000..ef8af77 --- /dev/null +++ b/test/ConfigTest53.php @@ -0,0 +1,36 @@ +<?php + +class ConfigTest53 extends PHPUnit_Framework_TestCase { + + public function setUp() { + // Enable logging + ORM::configure('logging', true); + + // Set up the dummy database connection + $db = new MockPDO('sqlite::memory:'); + ORM::set_db($db); + + ORM::configure('id_column', 'primary_key'); + } + + public function tearDown() { + ORM::configure('logging', false); + ORM::set_db(null); + + ORM::configure('id_column', 'id'); + } + + public function testLoggerCallback() { + ORM::configure('logger', function($log_string) { + return $log_string; + }); + $function = ORM::get_config('logger'); + $this->assertTrue(is_callable($function)); + + $log_string = "UPDATE `widget` SET `added` = NOW() WHERE `id` = '1'"; + $this->assertEquals($log_string, $function($log_string)); + + ORM::configure('logger', null); + } + +}
\ No newline at end of file diff --git a/test/QueryBuilderPsr1Test53.php b/test/QueryBuilderPsr1Test53.php new file mode 100644 index 0000000..a51103e --- /dev/null +++ b/test/QueryBuilderPsr1Test53.php @@ -0,0 +1,554 @@ +<?php + +class QueryBuilderPsr1Test53 extends PHPUnit_Framework_TestCase { + + public function setUp() { + // Enable logging + ORM::configure('logging', true); + + // Set up the dummy database connection + $db = new MockPDO('sqlite::memory:'); + ORM::setDb($db); + } + + public function tearDown() { + ORM::configure('logging', false); + ORM::setDb(null); + } + + public function testFindMany() { + ORM::forTable('widget')->findMany(); + $expected = "SELECT * FROM `widget`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testFindOne() { + ORM::forTable('widget')->findOne(); + $expected = "SELECT * FROM `widget` LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testFindOneWithPrimaryKeyFilter() { + ORM::forTable('widget')->findOne(5); + $expected = "SELECT * FROM `widget` WHERE `id` = '5' LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testWhereIdIs() { + ORM::forTable('widget')->whereIdIs(5)->findOne(); + $expected = "SELECT * FROM `widget` WHERE `id` = '5' LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testSingleWhereClause() { + ORM::forTable('widget')->where('name', 'Fred')->findOne(); + $expected = "SELECT * FROM `widget` WHERE `name` = 'Fred' LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testMultipleWhereClauses() { + ORM::forTable('widget')->where('name', 'Fred')->where('age', 10)->findOne(); + $expected = "SELECT * FROM `widget` WHERE `name` = 'Fred' AND `age` = '10' LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testWhereNotEqual() { + ORM::forTable('widget')->whereNotEqual('name', 'Fred')->findMany(); + $expected = "SELECT * FROM `widget` WHERE `name` != 'Fred'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testWhereLike() { + ORM::forTable('widget')->whereLike('name', '%Fred%')->findOne(); + $expected = "SELECT * FROM `widget` WHERE `name` LIKE '%Fred%' LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testWhereNotLike() { + ORM::forTable('widget')->whereNotLike('name', '%Fred%')->findOne(); + $expected = "SELECT * FROM `widget` WHERE `name` NOT LIKE '%Fred%' LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testWhereIn() { + ORM::forTable('widget')->whereIn('name', array('Fred', 'Joe'))->findMany(); + $expected = "SELECT * FROM `widget` WHERE `name` IN ('Fred', 'Joe')"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testWhereNotIn() { + ORM::forTable('widget')->whereNotIn('name', array('Fred', 'Joe'))->findMany(); + $expected = "SELECT * FROM `widget` WHERE `name` NOT IN ('Fred', 'Joe')"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testLimit() { + ORM::forTable('widget')->limit(5)->findMany(); + $expected = "SELECT * FROM `widget` LIMIT 5"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testLimitAndOffset() { + ORM::forTable('widget')->limit(5)->offset(5)->findMany(); + $expected = "SELECT * FROM `widget` LIMIT 5 OFFSET 5"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testOrderByDesc() { + ORM::forTable('widget')->orderByDesc('name')->findOne(); + $expected = "SELECT * FROM `widget` ORDER BY `name` DESC LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testOrderByAsc() { + ORM::forTable('widget')->orderByAsc('name')->findOne(); + $expected = "SELECT * FROM `widget` ORDER BY `name` ASC LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testOrderByExpression() { + ORM::forTable('widget')->orderByExpr('SOUNDEX(`name`)')->findOne(); + $expected = "SELECT * FROM `widget` ORDER BY SOUNDEX(`name`) LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testMultipleOrderBy() { + ORM::forTable('widget')->orderByAsc('name')->orderByDesc('age')->findOne(); + $expected = "SELECT * FROM `widget` ORDER BY `name` ASC, `age` DESC LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testGroupBy() { + ORM::forTable('widget')->groupBy('name')->findMany(); + $expected = "SELECT * FROM `widget` GROUP BY `name`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testMultipleGroupBy() { + ORM::forTable('widget')->groupBy('name')->groupBy('age')->findMany(); + $expected = "SELECT * FROM `widget` GROUP BY `name`, `age`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testGroupByExpression() { + ORM::forTable('widget')->groupByExpr("FROM_UNIXTIME(`time`, '%Y-%m')")->findMany(); + $expected = "SELECT * FROM `widget` GROUP BY FROM_UNIXTIME(`time`, '%Y-%m')"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testHaving() { + ORM::forTable('widget')->groupBy('name')->having('name', 'Fred')->findOne(); + $expected = "SELECT * FROM `widget` GROUP BY `name` HAVING `name` = 'Fred' LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testMultipleHaving() { + ORM::forTable('widget')->groupBy('name')->having('name', 'Fred')->having('age', 10)->findOne(); + $expected = "SELECT * FROM `widget` GROUP BY `name` HAVING `name` = 'Fred' AND `age` = '10' LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testHavingNotEqual() { + ORM::forTable('widget')->groupBy('name')->havingNotEqual('name', 'Fred')->findMany(); + $expected = "SELECT * FROM `widget` GROUP BY `name` HAVING `name` != 'Fred'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testHavingLike() { + ORM::forTable('widget')->groupBy('name')->havingLike('name', '%Fred%')->findOne(); + $expected = "SELECT * FROM `widget` GROUP BY `name` HAVING `name` LIKE '%Fred%' LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testHavingNotLike() { + ORM::forTable('widget')->groupBy('name')->havingNotLike('name', '%Fred%')->findOne(); + $expected = "SELECT * FROM `widget` GROUP BY `name` HAVING `name` NOT LIKE '%Fred%' LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testHavingIn() { + ORM::forTable('widget')->groupBy('name')->havingIn('name', array('Fred', 'Joe'))->findMany(); + $expected = "SELECT * FROM `widget` GROUP BY `name` HAVING `name` IN ('Fred', 'Joe')"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testHavingNotIn() { + ORM::forTable('widget')->groupBy('name')->havingNotIn('name', array('Fred', 'Joe'))->findMany(); + $expected = "SELECT * FROM `widget` GROUP BY `name` HAVING `name` NOT IN ('Fred', 'Joe')"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testHavingLessThan() { + ORM::forTable('widget')->groupBy('name')->havingLt('age', 10)->havingGt('age', 5)->findMany(); + $expected = "SELECT * FROM `widget` GROUP BY `name` HAVING `age` < '10' AND `age` > '5'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testHavingLessThanOrEqualAndGreaterThanOrEqual() { + ORM::forTable('widget')->groupBy('name')->havingLte('age', 10)->havingGte('age', 5)->findMany(); + $expected = "SELECT * FROM `widget` GROUP BY `name` HAVING `age` <= '10' AND `age` >= '5'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testHavingNull() { + ORM::forTable('widget')->groupBy('name')->havingNull('name')->findMany(); + $expected = "SELECT * FROM `widget` GROUP BY `name` HAVING `name` IS NULL"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testHavingNotNull() { + ORM::forTable('widget')->groupBy('name')->havingNotNull('name')->findMany(); + $expected = "SELECT * FROM `widget` GROUP BY `name` HAVING `name` IS NOT NULL"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testRawHaving() { + ORM::forTable('widget')->groupBy('name')->havingRaw('`name` = ? AND (`age` = ? OR `age` = ?)', array('Fred', 5, 10))->findMany(); + $expected = "SELECT * FROM `widget` GROUP BY `name` HAVING `name` = 'Fred' AND (`age` = '5' OR `age` = '10')"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testComplexQuery() { + ORM::forTable('widget')->where('name', 'Fred')->limit(5)->offset(5)->orderByAsc('name')->findMany(); + $expected = "SELECT * FROM `widget` WHERE `name` = 'Fred' ORDER BY `name` ASC LIMIT 5 OFFSET 5"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testWhereLessThanAndGreaterThan() { + ORM::forTable('widget')->whereLt('age', 10)->whereGt('age', 5)->findMany(); + $expected = "SELECT * FROM `widget` WHERE `age` < '10' AND `age` > '5'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testWhereLessThanAndEqualAndGreaterThanAndEqual() { + ORM::forTable('widget')->whereLte('age', 10)->whereGte('age', 5)->findMany(); + $expected = "SELECT * FROM `widget` WHERE `age` <= '10' AND `age` >= '5'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testWhereNull() { + ORM::forTable('widget')->whereNull('name')->findMany(); + $expected = "SELECT * FROM `widget` WHERE `name` IS NULL"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testWhereNotNull() { + ORM::forTable('widget')->whereNotNull('name')->findMany(); + $expected = "SELECT * FROM `widget` WHERE `name` IS NOT NULL"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testRawWhereClause() { + ORM::forTable('widget')->whereRaw('`name` = ? AND (`age` = ? OR `age` = ?)', array('Fred', 5, 10))->findMany(); + $expected = "SELECT * FROM `widget` WHERE `name` = 'Fred' AND (`age` = '5' OR `age` = '10')"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testRawWhereClauseWithPercentSign() { + ORM::forTable('widget')->whereRaw('STRFTIME("%Y", "now") = ?', array(2012))->findMany(); + $expected = "SELECT * FROM `widget` WHERE STRFTIME(\"%Y\", \"now\") = '2012'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testRawWhereClauseWithNoParameters() { + ORM::forTable('widget')->whereRaw('`name` = "Fred"')->findMany(); + $expected = "SELECT * FROM `widget` WHERE `name` = \"Fred\""; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testRawWhereClauseInMethodChain() { + ORM::forTable('widget')->where('age', 18)->whereRaw('(`name` = ? OR `name` = ?)', array('Fred', 'Bob'))->where('size', 'large')->findMany(); + $expected = "SELECT * FROM `widget` WHERE `age` = '18' AND (`name` = 'Fred' OR `name` = 'Bob') AND `size` = 'large'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testRawQuery() { + ORM::forTable('widget')->rawQuery('SELECT `w`.* FROM `widget` w')->findMany(); + $expected = "SELECT `w`.* FROM `widget` w"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testRawQueryWithParameters() { + ORM::forTable('widget')->rawQuery('SELECT `w`.* FROM `widget` w WHERE `name` = ? AND `age` = ?', array('Fred', 5))->findMany(); + $expected = "SELECT `w`.* FROM `widget` w WHERE `name` = 'Fred' AND `age` = '5'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testSimpleResultColumn() { + ORM::forTable('widget')->select('name')->findMany(); + $expected = "SELECT `name` FROM `widget`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testMultipleSimpleResultColumns() { + ORM::forTable('widget')->select('name')->select('age')->findMany(); + $expected = "SELECT `name`, `age` FROM `widget`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testSpecifyTableNameAndColumnInResultColumns() { + ORM::forTable('widget')->select('widget.name')->findMany(); + $expected = "SELECT `widget`.`name` FROM `widget`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testMainTableAlias() { + ORM::forTable('widget')->tableAlias('w')->findMany(); + $expected = "SELECT * FROM `widget` `w`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testAliasesInResultColumns() { + ORM::forTable('widget')->select('widget.name', 'widget_name')->findMany(); + $expected = "SELECT `widget`.`name` AS `widget_name` FROM `widget`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testAliasesInSelectManyResults() { + ORM::forTable('widget')->selectMany(array('widget_name' => 'widget.name'), 'widget_handle')->findMany(); + $expected = "SELECT `widget`.`name` AS `widget_name`, `widget_handle` FROM `widget`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testLiteralExpressionInResultColumn() { + ORM::forTable('widget')->selectExpr('COUNT(*)', 'count')->findMany(); + $expected = "SELECT COUNT(*) AS `count` FROM `widget`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testLiteralExpressionInSelectManyResultColumns() { + ORM::forTable('widget')->selectManyExpr(array('count' => 'COUNT(*)'), 'SUM(widget_order)')->findMany(); + $expected = "SELECT COUNT(*) AS `count`, SUM(widget_order) FROM `widget`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testSimpleJoin() { + ORM::forTable('widget')->join('widget_handle', array('widget_handle.widget_id', '=', 'widget.id'))->findMany(); + $expected = "SELECT * FROM `widget` JOIN `widget_handle` ON `widget_handle`.`widget_id` = `widget`.`id`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testSimpleJoinWithWhereIdIsMethod() { + ORM::forTable('widget')->join('widget_handle', array('widget_handle.widget_id', '=', 'widget.id'))->findOne(5); + $expected = "SELECT * FROM `widget` JOIN `widget_handle` ON `widget_handle`.`widget_id` = `widget`.`id` WHERE `widget`.`id` = '5' LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testInnerJoin() { + ORM::forTable('widget')->innerJoin('widget_handle', array('widget_handle.widget_id', '=', 'widget.id'))->findMany(); + $expected = "SELECT * FROM `widget` INNER JOIN `widget_handle` ON `widget_handle`.`widget_id` = `widget`.`id`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testLeftOuterJoin() { + ORM::forTable('widget')->leftOuterJoin('widget_handle', array('widget_handle.widget_id', '=', 'widget.id'))->findMany(); + $expected = "SELECT * FROM `widget` LEFT OUTER JOIN `widget_handle` ON `widget_handle`.`widget_id` = `widget`.`id`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testRightOuterJoin() { + ORM::forTable('widget')->rightOuterJoin('widget_handle', array('widget_handle.widget_id', '=', 'widget.id'))->findMany(); + $expected = "SELECT * FROM `widget` RIGHT OUTER JOIN `widget_handle` ON `widget_handle`.`widget_id` = `widget`.`id`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testFullOuterJoin() { + ORM::forTable('widget')->fullOuterJoin('widget_handle', array('widget_handle.widget_id', '=', 'widget.id'))->findMany(); + $expected = "SELECT * FROM `widget` FULL OUTER JOIN `widget_handle` ON `widget_handle`.`widget_id` = `widget`.`id`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testMultipleJoinSources() { + ORM::forTable('widget') + ->join('widget_handle', array('widget_handle.widget_id', '=', 'widget.id')) + ->join('widget_nozzle', array('widget_nozzle.widget_id', '=', 'widget.id')) + ->findMany(); + $expected = "SELECT * FROM `widget` JOIN `widget_handle` ON `widget_handle`.`widget_id` = `widget`.`id` JOIN `widget_nozzle` ON `widget_nozzle`.`widget_id` = `widget`.`id`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testJoinWithAliases() { + ORM::forTable('widget')->join('widget_handle', array('wh.widget_id', '=', 'widget.id'), 'wh')->findMany(); + $expected = "SELECT * FROM `widget` JOIN `widget_handle` `wh` ON `wh`.`widget_id` = `widget`.`id`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testJoinWithAliasesAndWhere() { + ORM::forTable('widget')->tableAlias('w')->join('widget_handle', array('wh.widget_id', '=', 'w.id'), 'wh')->whereEqual('id', 1)->findMany(); + $expected = "SELECT * FROM `widget` `w` JOIN `widget_handle` `wh` ON `wh`.`widget_id` = `w`.`id` WHERE `w`.`id` = '1'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testJoinWithStringConstraint() { + ORM::forTable('widget')->join('widget_handle', "widget_handle.widget_id = widget.id")->findMany(); + $expected = "SELECT * FROM `widget` JOIN `widget_handle` ON widget_handle.widget_id = widget.id"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testSelectWithDistinct() { + ORM::forTable('widget')->distinct()->select('name')->findMany(); + $expected = "SELECT DISTINCT `name` FROM `widget`"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testInsertData() { + $widget = ORM::forTable('widget')->create(); + $widget->name = "Fred"; + $widget->age = 10; + $widget->save(); + $expected = "INSERT INTO `widget` (`name`, `age`) VALUES ('Fred', '10')"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testInsertDataContainingAnExpression() { + $widget = ORM::forTable('widget')->create(); + $widget->name = "Fred"; + $widget->age = 10; + $widget->setExpr('added', 'NOW()'); + $widget->save(); + $expected = "INSERT INTO `widget` (`name`, `age`, `added`) VALUES ('Fred', '10', NOW())"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testInsertDataUsingArrayAccess() { + $widget = ORM::forTable('widget')->create(); + $widget['name'] = "Fred"; + $widget['age'] = 10; + $widget->save(); + $expected = "INSERT INTO `widget` (`name`, `age`) VALUES ('Fred', '10')"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testUpdateData() { + $widget = ORM::forTable('widget')->findOne(1); + $widget->name = "Fred"; + $widget->age = 10; + $widget->save(); + $expected = "UPDATE `widget` SET `name` = 'Fred', `age` = '10' WHERE `id` = '1'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testUpdateDataContainingAnExpression() { + $widget = ORM::forTable('widget')->findOne(1); + $widget->name = "Fred"; + $widget->age = 10; + $widget->setExpr('added', 'NOW()'); + $widget->save(); + $expected = "UPDATE `widget` SET `name` = 'Fred', `age` = '10', `added` = NOW() WHERE `id` = '1'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testUpdateMultipleFields() { + $widget = ORM::forTable('widget')->findOne(1); + $widget->set(array("name" => "Fred", "age" => 10)); + $widget->save(); + $expected = "UPDATE `widget` SET `name` = 'Fred', `age` = '10' WHERE `id` = '1'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testUpdateMultipleFieldsContainingAnExpression() { + $widget = ORM::forTable('widget')->findOne(1); + $widget->set(array("name" => "Fred", "age" => 10)); + $widget->setExpr(array("added" => "NOW()", "lat_long" => "GeomFromText('POINT(1.2347 2.3436)')")); + $widget->save(); + $expected = "UPDATE `widget` SET `name` = 'Fred', `age` = '10', `added` = NOW(), `lat_long` = GeomFromText('POINT(1.2347 2.3436)') WHERE `id` = '1'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testUpdateMultipleFieldsContainingAnExpressionAndOverridePreviouslySetExpression() { + $widget = ORM::forTable('widget')->findOne(1); + $widget->set(array("name" => "Fred", "age" => 10)); + $widget->setExpr(array("added" => "NOW()", "lat_long" => "GeomFromText('POINT(1.2347 2.3436)')")); + $widget->lat_long = 'unknown'; + $widget->save(); + $expected = "UPDATE `widget` SET `name` = 'Fred', `age` = '10', `added` = NOW(), `lat_long` = 'unknown' WHERE `id` = '1'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testDeleteData() { + $widget = ORM::forTable('widget')->findOne(1); + $widget->delete(); + $expected = "DELETE FROM `widget` WHERE `id` = '1'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testDeleteMany() { + ORM::forTable('widget')->whereEqual('age', 10)->delete_many(); + $expected = "DELETE FROM `widget` WHERE `age` = '10'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testCount() { + ORM::forTable('widget')->count(); + $expected = "SELECT COUNT(*) AS `count` FROM `widget` LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testIgnoreSelectAndCount() { + ORM::forTable('widget')->select('test')->count(); + $expected = "SELECT COUNT(*) AS `count` FROM `widget` LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testMax() { + ORM::forTable('person')->max('height'); + $expected = "SELECT MAX(`height`) AS `max` FROM `person` LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testMin() { + ORM::forTable('person')->min('height'); + $expected = "SELECT MIN(`height`) AS `min` FROM `person` LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testAvg() { + ORM::forTable('person')->avg('height'); + $expected = "SELECT AVG(`height`) AS `avg` FROM `person` LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testSum() { + ORM::forTable('person')->sum('height'); + $expected = "SELECT SUM(`height`) AS `sum` FROM `person` LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + /** + * Regression tests + */ + public function testIssue12IncorrectQuotingOfColumnWildcard() { + ORM::forTable('widget')->select('widget.*')->findOne(); + $expected = "SELECT `widget`.* FROM `widget` LIMIT 1"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testIssue57LogQueryRaisesWarningWhenPercentSymbolSupplied() { + ORM::forTable('widget')->whereRaw('username LIKE "ben%"')->findMany(); + $expected = 'SELECT * FROM `widget` WHERE username LIKE "ben%"'; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testIssue57LogQueryRaisesWarningWhenQuestionMarkSupplied() { + ORM::forTable('widget')->whereRaw('comments LIKE "has been released?%"')->findMany(); + $expected = 'SELECT * FROM `widget` WHERE comments LIKE "has been released?%"'; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testIssue74EscapingQuoteMarksIn_quote_identifier_part() { + $widget = ORM::forTable('widget')->findOne(1); + $widget->set('ad`ded', '2013-01-04'); + $widget->save(); + $expected = "UPDATE `widget` SET `ad``ded` = '2013-01-04' WHERE `id` = '1'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } + + public function testIssue90UsingSetExprAloneDoesTriggerQueryGeneration() { + $widget = ORM::forTable('widget')->findOne(1); + $widget->setExpr('added', 'NOW()'); + $widget->save(); + $expected = "UPDATE `widget` SET `added` = NOW() WHERE `id` = '1'"; + $this->assertEquals($expected, ORM::getLastQuery()); + } +} + diff --git a/test/QueryBuilderTest.php b/test/QueryBuilderTest.php index bd888c0..6da0997 100644 --- a/test/QueryBuilderTest.php +++ b/test/QueryBuilderTest.php @@ -373,6 +373,12 @@ class QueryBuilderTest extends PHPUnit_Framework_TestCase { $this->assertEquals($expected, ORM::get_last_query()); } + public function testJoinWithAliasesAndWhere() { + ORM::for_table('widget')->table_alias('w')->join('widget_handle', array('wh.widget_id', '=', 'w.id'), 'wh')->where_equal('id', 1)->find_many(); + $expected = "SELECT * FROM `widget` `w` JOIN `widget_handle` `wh` ON `wh`.`widget_id` = `w`.`id` WHERE `w`.`id` = '1'"; + $this->assertEquals($expected, ORM::get_last_query()); + } + public function testJoinWithStringConstraint() { ORM::for_table('widget')->join('widget_handle', "widget_handle.widget_id = widget.id")->find_many(); $expected = "SELECT * FROM `widget` JOIN `widget_handle` ON widget_handle.widget_id = widget.id"; @@ -477,6 +483,12 @@ class QueryBuilderTest extends PHPUnit_Framework_TestCase { $expected = "SELECT COUNT(*) AS `count` FROM `widget` LIMIT 1"; $this->assertEquals($expected, ORM::get_last_query()); } + + public function testIgnoreSelectAndCount() { + ORM::for_table('widget')->select('test')->count(); + $expected = "SELECT COUNT(*) AS `count` FROM `widget` LIMIT 1"; + $this->assertEquals($expected, ORM::get_last_query()); + } public function testMax() { ORM::for_table('person')->max('height'); diff --git a/test/bootstrap.php b/test/bootstrap.php index 4e8795d..03ad785 100644 --- a/test/bootstrap.php +++ b/test/bootstrap.php @@ -8,19 +8,14 @@ require_once dirname(__FILE__) . '/../idiorm.php'; * */ class MockPDOStatement extends PDOStatement { - private $current_row = 0; - /** - * Return some dummy data - */ - /** - * These are necessary to run the tests on hhvm. In fact, newing up a PDOStatement (or derivative) should not be - * technically used since there is no direct constructor for PDOStatement, even though it does work with Zend - */ public function __construct() {} public function execute($params) {} + /** + * Return some dummy data + */ public function fetch($fetch_style=PDO::FETCH_BOTH, $cursor_orientation=PDO::FETCH_ORI_NEXT, $cursor_offset=0) { if ($this->current_row == 5) { return false; |