protected function assertCriteriaTranslation($criteria, $expectedSql, $expectedParams, $message = '')
{
$params = array();
$result = BasePeer::createSelectSql($criteria, $params);
$this->assertEquals($expectedSql, $result, $message);
$this->assertEquals($expectedParams, $params, $message);
}
public function testDoInsert()
{
try {
$c = new Criteria();
$c->setPrimaryTableName(BookPeer::TABLE_NAME);
$c->add(BookPeer::AUTHOR_ID, 'lkhlkhj');
BasePeer::doInsert($c, Propel::getServiceContainer()->getWriteConnection(BookPeer::DATABASE_NAME));
} catch (RuntimeException $e) {
$this->assertContains('[INSERT INTO `book` (`AUTHOR_ID`) VALUES (:p1)]', $e->getMessage(), 'SQL query is written in the exception message');
}
}
public function testApplyLimitDuplicateColumnNameWithColumn()
{
Propel::setDb('oracle', new DBOracle());
$c = new Criteria();
$c->setDbName('oracle');
BookPeer::addSelectColumns($c);
AuthorPeer::addSelectColumns($c);
$c->addAsColumn('BOOK_PRICE', BookPeer::PRICE);
$c->setLimit(1);
$params = array();
$asColumns = $c->getAsColumns();
$sql = BasePeer::createSelectSql($c, $params);
$this->assertEquals('SELECT B.* FROM (SELECT A.*, rownum AS PROPEL_ROWNUM FROM (SELECT book.ID AS ORA_COL_ALIAS_0, book.TITLE AS ORA_COL_ALIAS_1, book.ISBN AS ORA_COL_ALIAS_2, book.PRICE AS ORA_COL_ALIAS_3, book.PUBLISHER_ID AS ORA_COL_ALIAS_4, book.AUTHOR_ID AS ORA_COL_ALIAS_5, author.ID AS ORA_COL_ALIAS_6, author.FIRST_NAME AS ORA_COL_ALIAS_7, author.LAST_NAME AS ORA_COL_ALIAS_8, author.EMAIL AS ORA_COL_ALIAS_9, author.AGE AS ORA_COL_ALIAS_10, book.PRICE AS BOOK_PRICE FROM book, author) A ) B WHERE B.PROPEL_ROWNUM <= 1', $sql, 'applyLimit() creates a subselect with aliased column names when a duplicate column name is found');
$this->assertEquals($asColumns, $c->getAsColumns(), 'createSelectSql supplementary add alias column');
}
public function testaddUsingOperatorResetsDefaultOperator()
{
$c = new Criteria();
$c->addUsingOperator('foo1', 'bar1');
$c->_or();
$c->addUsingOperator('foo2', 'bar2');
$c->addUsingOperator('foo3', 'bar3');
$expected = 'SELECT FROM WHERE (foo1=:p1 OR foo2=:p2) AND foo3=:p3';
$params = array();
$result = BasePeer::createSelectSql($c, $params);
$this->assertEquals($expected, $result);
}
/**
* Build a string representation of the Criteria.
*
* @return string A String with the representation of the Criteria.
*/
public function toString()
{
$sb = "Criteria:";
try {
$params = array();
$sb .= "\nSQL (may not be complete): " . BasePeer::createSelectSql($this, $params);
$sb .= "\nParams: ";
$paramstr = array();
foreach ($params as $param) {
$paramstr[] = $param['table'] . '.' . $param['column'] . ' => ' . var_export($param['value'], true);
}
$sb .= implode(", ", $paramstr);
} catch (Exception $exc) {
$sb .= "(Error: " . $exc->getMessage() . ")";
}
return $sb;
}
/**
* Method to create an SQL query based on values in a Criteria.
*
* This method creates only prepared statement SQL (using ? where values
* will go). The second parameter ($params) stores the values that need
* to be set before the statement is executed. The reason we do it this way
* is to let the PDO layer handle all escaping & value formatting.
*
* @param Criteria $criteria Criteria for the SELECT query.
* @param array &$params Parameters that are to be replaced in prepared statement.
* @return string
* @throws \Propel\Runtime\Exception\RuntimeException Trouble creating the query string.
*/
public static function createSelectSql(Criteria $criteria, &$params)
{
$db = Propel::getServiceContainer()->getAdapter($criteria->getDbName());
$dbMap = Propel::getServiceContainer()->getDatabaseMap($criteria->getDbName());
$fromClause = array();
$joinClause = array();
$joinTables = array();
$whereClause = array();
$orderByClause = array();
$orderBy = $criteria->getOrderByColumns();
$groupBy = $criteria->getGroupByColumns();
// get the first part of the SQL statement, the SELECT part
$selectSql = $db->createSelectSqlPart($criteria, $fromClause);
// Handle joins
// joins with a null join type will be added to the FROM clause and the condition added to the WHERE clause.
// joins of a specified type: the LEFT side will be added to the fromClause and the RIGHT to the joinClause
foreach ($criteria->getJoins() as $join) {
$join->setAdapter($db);
// add 'em to the queues..
if (!$fromClause) {
$fromClause[] = $join->getLeftTableWithAlias();
}
$joinTables[] = $join->getRightTableWithAlias();
$joinClause[] = $join->getClause($params);
}
// add the criteria to WHERE clause
// this will also add the table names to the FROM clause if they are not already
// included via a LEFT JOIN
foreach ($criteria->keys() as $key) {
$criterion = $criteria->getCriterion($key);
$table = null;
foreach ($criterion->getAttachedCriterion() as $attachedCriterion) {
$tableName = $attachedCriterion->getTable();
$table = $criteria->getTableForAlias($tableName);
if ($table !== null) {
$fromClause[] = $table . ' ' . $tableName;
} else {
$fromClause[] = $tableName;
$table = $tableName;
}
if (($criteria->isIgnoreCase() || $attachedCriterion->isIgnoreCase()) && $dbMap->getTable($table)->getColumn($attachedCriterion->getColumn())->isText()) {
$attachedCriterion->setIgnoreCase(true);
}
}
$criterion->setAdapter($db);
$sb = '';
$criterion->appendPsTo($sb, $params);
$whereClause[] = $sb;
}
// Unique from clause elements
$fromClause = array_unique($fromClause);
$fromClause = array_diff($fromClause, array(''));
// tables should not exist in both the from and join clauses
if ($joinTables && $fromClause) {
foreach ($fromClause as $fi => $ftable) {
if (in_array($ftable, $joinTables)) {
unset($fromClause[$fi]);
}
}
}
// Add the GROUP BY columns
$groupByClause = $groupBy;
$having = $criteria->getHaving();
$havingString = null;
if ($having !== null) {
$sb = '';
$having->appendPsTo($sb, $params);
$havingString = $sb;
}
if (!empty($orderBy)) {
foreach ($orderBy as $orderByColumn) {
// Add function expression as-is.
if (strpos($orderByColumn, '(') !== false) {
$orderByClause[] = $orderByColumn;
continue;
}
// Split orderByColumn (i.e. "table.column DESC")
$dotPos = strrpos($orderByColumn, '.');
if ($dotPos !== false) {
$tableName = substr($orderByColumn, 0, $dotPos);
$columnName = substr($orderByColumn, $dotPos + 1);
} else {
$tableName = '';
$columnName = $orderByColumn;
}
$spacePos = strpos($columnName, ' ');
if ($spacePos !== false) {
$direction = substr($columnName, $spacePos);
$columnName = substr($columnName, 0, $spacePos);
} else {
$direction = '';
}
$tableAlias = $tableName;
if ($aliasTableName = $criteria->getTableForAlias($tableName)) {
$tableName = $aliasTableName;
}
$columnAlias = $columnName;
if ($asColumnName = $criteria->getColumnForAs($columnName)) {
$columnName = $asColumnName;
}
$column = $tableName ? $dbMap->getTable($tableName)->getColumn($columnName) : null;
if ($criteria->isIgnoreCase() && $column && $column->isText()) {
$ignoreCaseColumn = $db->ignoreCaseInOrderBy("{$tableAlias}.{$columnAlias}");
$orderByClause[] = $ignoreCaseColumn . $direction;
$selectSql .= ', ' . $ignoreCaseColumn;
} else {
$orderByClause[] = $orderByColumn;
}
}
}
if (empty($fromClause) && $criteria->getPrimaryTableName()) {
$fromClause[] = $criteria->getPrimaryTableName();
}
// tables should not exist as alias of subQuery
if ($criteria->hasSelectQueries()) {
foreach ($fromClause as $key => $ftable) {
if (strpos($ftable, ' ') !== false) {
list($realtable, $tableName) = explode(' ', $ftable);
} else {
$tableName = $ftable;
}
if ($criteria->hasSelectQuery($tableName)) {
unset($fromClause[$key]);
}
}
}
// from / join tables quoted if it is necessary
if ($db->useQuoteIdentifier()) {
$fromClause = array_map(array($db, 'quoteIdentifierTable'), $fromClause);
$joinClause = $joinClause ? $joinClause : array_map(array($db, 'quoteIdentifierTable'), $joinClause);
}
// add subQuery to From after adding quotes
foreach ($criteria->getSelectQueries() as $subQueryAlias => $subQueryCriteria) {
$fromClause[] = '(' . BasePeer::createSelectSql($subQueryCriteria, $params) . ') AS ' . $subQueryAlias;
}
// build from-clause
$from = '';
if (!empty($joinClause) && count($fromClause) > 1) {
$from .= implode(" CROSS JOIN ", $fromClause);
} else {
$from .= implode(", ", $fromClause);
}
$from .= $joinClause ? ' ' . implode(' ', $joinClause) : '';
// Build the SQL from the arrays we compiled
$sql = $selectSql . " FROM " . $from . ($whereClause ? " WHERE " . implode(" AND ", $whereClause) : "") . ($groupByClause ? " GROUP BY " . implode(",", $groupByClause) : "") . ($havingString ? " HAVING " . $havingString : "") . ($orderByClause ? " ORDER BY " . implode(",", $orderByClause) : "");
// APPLY OFFSET & LIMIT to the query.
if ($criteria->getLimit() || $criteria->getOffset()) {
$db->applyLimit($sql, $criteria->getOffset(), $criteria->getLimit(), $criteria);
}
return $sql;
}
public function testUseFkQueryNested()
{
$q = ReviewQuery::create()->useBookQuery()->useAuthorQuery()->filterByFirstName('Leo')->endUse()->endUse();
$q1 = ReviewQuery::create()->join('Review.Book', Criteria::LEFT_JOIN)->join('Book.Author', Criteria::LEFT_JOIN)->add(AuthorPeer::FIRST_NAME, 'Leo', Criteria::EQUAL);
// embedded queries create joins that keep a relation to the parent
// as this is not testable, we need to use another testing technique
$params = array();
$result = BasePeer::createSelectSql($q, $params);
$expectedParams = array();
$expectedResult = BasePeer::createSelectSql($q1, $expectedParams);
$this->assertEquals($expectedParams, $params, 'useFkQuery() called nested creates two joins');
$this->assertEquals($expectedResult, $result, 'useFkQuery() called nested creates two joins');
}
/**
* @see DBAdapter::applyLimit()
*
* @param string $sql
* @param integer $offset
* @param integer $limit
* @param null|Criteria $criteria
*/
public function applyLimit(&$sql, $offset, $limit, $criteria = null)
{
if (BasePeer::needsSelectAliases($criteria)) {
$crit = clone $criteria;
$selectSql = $this->createSelectSqlPart($crit, $params, true);
$sql = $selectSql . substr($sql, strpos($sql, 'FROM') - 1);
}
$sql = 'SELECT B.* FROM (' . 'SELECT A.*, rownum AS PROPEL_ROWNUM FROM (' . $sql . ') A ' . ') B WHERE ';
if ($offset > 0) {
$sql .= ' B.PROPEL_ROWNUM > ' . $offset;
if ($limit > 0) {
$sql .= ' AND B.PROPEL_ROWNUM <= ' . ($offset + $limit);
}
} else {
$sql .= ' B.PROPEL_ROWNUM <= ' . $limit;
}
}
public function testCombineDirtyOperators()
{
$this->c->addCond('cond1', "INVOICE.COST1", "1000", Criteria::GREATER_EQUAL);
$this->c->addCond('cond2', "INVOICE.COST2", "2000", Criteria::LESS_EQUAL);
$this->c->combine(array('cond1', 'cond2'), 'AnD', 'cond12');
$this->c->addCond('cond3', "INVOICE.COST3", "8000", Criteria::GREATER_EQUAL);
$this->c->addCond('cond4', "INVOICE.COST4", "9000", Criteria::LESS_EQUAL);
$this->c->combine(array('cond3', 'cond4'), 'aNd', 'cond34');
$this->c->combine(array('cond12', 'cond34'), 'oR');
$expect = "SELECT FROM INVOICE WHERE ((INVOICE.COST1>=:p1 AND INVOICE.COST2<=:p2) OR (INVOICE.COST3>=:p3 AND INVOICE.COST4<=:p4))";
$expect_params = array(array('table' => 'INVOICE', 'column' => 'COST1', 'value' => '1000'), array('table' => 'INVOICE', 'column' => 'COST2', 'value' => '2000'), array('table' => 'INVOICE', 'column' => 'COST3', 'value' => '8000'), array('table' => 'INVOICE', 'column' => 'COST4', 'value' => '9000'));
$params = array();
$result = BasePeer::createSelectSql($this->c, $params);
$this->assertEquals($expect, $result);
$this->assertEquals($expect_params, $params);
}
public function testAddSelectModifier()
{
$c = new Criteria();
$c->setDistinct();
$c->addSelectModifier('SQL_CALC_FOUND_ROWS');
$this->assertEquals(array(Criteria::DISTINCT, 'SQL_CALC_FOUND_ROWS'), $c->getSelectModifiers(), 'addSelectModifier() adds a select modifier to the Criteria');
$c->addSelectModifier('SQL_CALC_FOUND_ROWS');
$this->assertEquals(array(Criteria::DISTINCT, 'SQL_CALC_FOUND_ROWS'), $c->getSelectModifiers(), 'addSelectModifier() adds a select modifier only once');
$params = array();
$result = BasePeer::createSelectSql($c, $params);
$this->assertEquals('SELECT DISTINCT SQL_CALC_FOUND_ROWS FROM ', $result, 'addSelectModifier() adds a modifier to the final query');
}
/**
* Tests the BasePeer::translateFieldName() method
*/
public function testTranslateFieldNameStatic()
{
$types = array(BasePeer::TYPE_PHPNAME, BasePeer::TYPE_STUDLYPHPNAME, BasePeer::TYPE_COLNAME, BasePeer::TYPE_FIELDNAME, BasePeer::TYPE_NUM);
$expecteds = array(BasePeer::TYPE_PHPNAME => 'AuthorId', BasePeer::TYPE_STUDLYPHPNAME => 'authorId', BasePeer::TYPE_COLNAME => 'book.AUTHOR_ID', BasePeer::TYPE_FIELDNAME => 'author_id', BasePeer::TYPE_NUM => 5);
foreach ($types as $fromType) {
foreach ($types as $toType) {
$name = $expecteds[$fromType];
$expected = $expecteds[$toType];
$result = BasePeer::translateFieldName('\\Propel\\Tests\\Bookstore\\Book', $name, $fromType, $toType);
$this->assertEquals($expected, $result);
}
}
}
public function testClone()
{
$bookQuery1 = BookQuery::create()->filterByPrice(1);
$bookQuery2 = clone $bookQuery1;
$bookQuery2->filterByPrice(2);
$params = array();
$sql = BasePeer::createSelectSql($bookQuery1, $params);
$this->assertEquals('SELECT FROM `book` WHERE book.PRICE=:p1', $sql, 'conditions applied on a cloned query don\'t get applied on the original query');
}
public function testCommentDoDelete()
{
$c = new Criteria();
$c->setComment('Foo');
$c->add(BookPeer::TITLE, 'War And Peace');
$con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
BasePeer::doDelete($c, $con);
$expected = 'DELETE /* Foo */ FROM `book` WHERE book.TITLE=\'War And Peace\'';
$this->assertEquals($expected, $con->getLastExecutedQuery(), 'Criteria::setComment() adds a comment to delete queries');
}
public function testJoinWithI18nAddsTheI18nColumns()
{
$q = \I18nBehaviorTest11Query::create()->joinWithI18n();
$params = array();
$sql = BasePeer::createSelectSQL($q, $params);
$expectedSQL = 'SELECT i18n_behavior_test_11.ID, i18n_behavior_test_11.FOO, i18n_behavior_test_11_i18n.ID, i18n_behavior_test_11_i18n.LOCALE, i18n_behavior_test_11_i18n.BAR FROM i18n_behavior_test_11 LEFT JOIN i18n_behavior_test_11_i18n ON (i18n_behavior_test_11.ID=i18n_behavior_test_11_i18n.ID AND i18n_behavior_test_11_i18n.LOCALE = :p1)';
$this->assertEquals($expectedSQL, $sql);
$this->assertEquals('en_EN', $params[0]['value']);
}
/**
* Issue an UPDATE query based the current ModelCriteria and a list of changes.
* This method is called by ModelCriteria::update() inside a transaction.
*
* @param array $values Associative array of keys and values to replace
* @param ConnectionInterface $con a connection object
* @param boolean $forceIndividualSaves If false (default), the resulting call is a BasePeer::doUpdate(), ortherwise it is a series of save() calls on all the found objects
*
* @return Integer Number of updated rows
*/
public function doUpdate($values, $con, $forceIndividualSaves = false)
{
if ($forceIndividualSaves) {
// Update rows one by one
$objects = $this->setFormatter(ModelCriteria::FORMAT_OBJECT)->find($con);
foreach ($objects as $object) {
foreach ($values as $key => $value) {
$object->setByName($key, $value);
}
}
$objects->save($con);
$affectedRows = count($objects);
} else {
// update rows in a single query
$set = new Criteria($this->getDbName());
foreach ($values as $columnName => $value) {
$realColumnName = $this->getTableMap()->getColumnByPhpName($columnName)->getFullyQualifiedName();
$set->add($realColumnName, $value);
}
$affectedRows = BasePeer::doUpdate($this, $set, $con);
call_user_func(array($this->modelPeerName, 'clearInstancePool'));
call_user_func(array($this->modelPeerName, 'clearRelatedInstancePool'));
}
return $affectedRows;
}
