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; }