Ejemplo n.º 1
0
 protected function assertCriteriaTranslation($criteria, $expectedSql, $expectedParams, $message = '')
 {
     $params = array();
     $result = BasePeer::createSelectSql($criteria, $params);
     $this->assertEquals($expectedSql, $result, $message);
     $this->assertEquals($expectedParams, $params, $message);
 }
Ejemplo n.º 2
0
 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');
     }
 }
Ejemplo n.º 3
0
 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');
 }
Ejemplo n.º 4
0
 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);
 }
Ejemplo n.º 5
0
 /**
  * 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;
 }
Ejemplo n.º 6
0
 /**
  * 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;
 }
Ejemplo n.º 7
0
 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');
 }
Ejemplo n.º 8
0
 /**
  * @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;
     }
 }
Ejemplo n.º 9
0
 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);
 }
Ejemplo n.º 10
0
 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');
 }
Ejemplo n.º 11
0
 /**
  * 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);
         }
     }
 }
Ejemplo n.º 12
0
 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');
 }
Ejemplo n.º 13
0
 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']);
 }
Ejemplo n.º 15
0
 /**
  * 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;
 }