Example #1
0
 /**
  * Method used to update rows in the DB.  Rows are selected based
  * on selectCriteria and updated using values in updateValues.
  * <p>
  * Use this method for performing an update of the kind:
  * <p>
  * WHERE some_column = some value AND could_have_another_column =
  * another value AND so on.
  *
  * @param      $selectCriteria A Criteria object containing values used in where
  *		clause.
  * @param      $updateValues A Criteria object containing values used in set
  *		clause.
  * @param      PropelPDO $con The PropelPDO connection object to use.
  * @return     int	The number of rows affected by last update statement.  For most
  * 				uses there is only one update statement executed, so this number
  * 				will correspond to the number of rows affected by the call to this
  * 				method.  Note that the return value does require that this information
  * 				is returned (supported) by the Propel db driver.
  * @throws     PropelException
  */
 public static function doUpdate(Criteria $selectCriteria, Criteria $updateValues, PropelPDO $con)
 {
     $db = Propel::getDB($selectCriteria->getDbName());
     $dbMap = Propel::getDatabaseMap($selectCriteria->getDbName());
     // Get list of required tables, containing all columns
     $tablesColumns = $selectCriteria->getTablesColumns();
     if (empty($tablesColumns)) {
         $tablesColumns = array($selectCriteria->getPrimaryTableName() => array());
     }
     // we also need the columns for the update SQL
     $updateTablesColumns = $updateValues->getTablesColumns();
     $affectedRows = 0;
     // initialize this in case the next loop has no iterations.
     foreach ($tablesColumns as $tableName => $columns) {
         $whereClause = array();
         $params = array();
         $stmt = null;
         try {
             $sql = 'UPDATE ';
             if ($queryComment = $selectCriteria->getComment()) {
                 $sql .= '/* ' . $queryComment . ' */ ';
             }
             // is it a table alias?
             if ($tableName2 = $selectCriteria->getTableForAlias($tableName)) {
                 $udpateTable = $tableName2 . ' ' . $tableName;
                 $tableName = $tableName2;
             } else {
                 $udpateTable = $tableName;
             }
             if ($db->useQuoteIdentifier()) {
                 $sql .= $db->quoteIdentifierTable($udpateTable);
             } else {
                 $sql .= $udpateTable;
             }
             $sql .= " SET ";
             $p = 1;
             foreach ($updateTablesColumns[$tableName] as $col) {
                 $updateColumnName = substr($col, strrpos($col, '.') + 1);
                 // add identifiers for the actual database?
                 if ($db->useQuoteIdentifier()) {
                     $updateColumnName = $db->quoteIdentifier($updateColumnName);
                 }
                 if ($updateValues->getComparison($col) != Criteria::CUSTOM_EQUAL) {
                     $sql .= $updateColumnName . '=:p' . $p++ . ', ';
                 } else {
                     $param = $updateValues->get($col);
                     $sql .= $updateColumnName . ' = ';
                     if (is_array($param)) {
                         if (isset($param['raw'])) {
                             $raw = $param['raw'];
                             $rawcvt = '';
                             // parse the $params['raw'] for ? chars
                             for ($r = 0, $len = strlen($raw); $r < $len; $r++) {
                                 if ($raw[$r] == '?') {
                                     $rawcvt .= ':p' . $p++;
                                 } else {
                                     $rawcvt .= $raw[$r];
                                 }
                             }
                             $sql .= $rawcvt . ', ';
                         } else {
                             $sql .= ':p' . $p++ . ', ';
                         }
                         if (isset($param['value'])) {
                             $updateValues->put($col, $param['value']);
                         }
                     } else {
                         $updateValues->remove($col);
                         $sql .= $param . ', ';
                     }
                 }
             }
             $params = self::buildParams($updateTablesColumns[$tableName], $updateValues);
             $sql = substr($sql, 0, -2);
             if (!empty($columns)) {
                 foreach ($columns as $colName) {
                     $sb = "";
                     $selectCriteria->getCriterion($colName)->appendPsTo($sb, $params);
                     $whereClause[] = $sb;
                 }
                 $sql .= " WHERE " . implode(" AND ", $whereClause);
             }
             $stmt = $con->prepare($sql);
             // Replace ':p?' with the actual values
             self::populateStmtValues($stmt, $params, $dbMap, $db);
             $stmt->execute();
             $affectedRows = $stmt->rowCount();
             $stmt = null;
             // close
         } catch (Exception $e) {
             if ($stmt) {
                 $stmt = null;
             }
             // close
             Propel::log($e->getMessage(), Propel::LOG_ERR);
             throw new PropelException(sprintf('Unable to execute UPDATE statement [%s]', $sql), $e);
         }
     }
     // foreach table in the criteria
     return $affectedRows;
 }
Example #2
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     PropelException Trouble creating the query string.
  */
 public static function createSelectSql(Criteria $criteria, &$params)
 {
     $db = Propel::getDB($criteria->getDbName());
     $dbMap = Propel::getDatabaseMap($criteria->getDbName());
     // redundant definition $selectModifiers = array();
     $selectClause = array();
     $fromClause = array();
     $joinClause = array();
     $joinTables = array();
     $whereClause = array();
     $orderByClause = array();
     // redundant definition $groupByClause = array();
     $orderBy = $criteria->getOrderByColumns();
     $groupBy = $criteria->getGroupByColumns();
     $ignoreCase = $criteria->isIgnoreCase();
     $select = $criteria->getSelectColumns();
     $aliases = $criteria->getAsColumns();
     // simple copy
     $selectModifiers = $criteria->getSelectModifiers();
     // get selected columns
     foreach ($select as $columnName) {
         // expect every column to be of "table.column" formation
         // it could be a function:  e.g. MAX(books.price)
         $tableName = null;
         $selectClause[] = $columnName;
         // the full column name: e.g. MAX(books.price)
         $parenPos = strrpos($columnName, '(');
         $dotPos = strrpos($columnName, '.', $parenPos !== false ? $parenPos : 0);
         // [HL] I think we really only want to worry about adding stuff to
         // the fromClause if this function has a TABLE.COLUMN in it at all.
         // e.g. COUNT(*) should not need this treatment -- or there needs to
         // be special treatment for '*'
         if ($dotPos !== false) {
             if ($parenPos === false) {
                 // table.column
                 $tableName = substr($columnName, 0, $dotPos);
             } else {
                 // FUNC(table.column)
                 $tableName = substr($columnName, $parenPos + 1, $dotPos - ($parenPos + 1));
                 // functions may contain qualifiers so only take the last
                 // word as the table name.
                 // COUNT(DISTINCT books.price)
                 $lastSpace = strpos($tableName, ' ');
                 if ($lastSpace !== false) {
                     // COUNT(DISTINCT books.price)
                     $tableName = substr($tableName, $lastSpace + 1);
                 }
             }
             $tableName2 = $criteria->getTableForAlias($tableName);
             if ($tableName2 !== null) {
                 $fromClause[] = $tableName2 . ' ' . $tableName;
             } else {
                 $fromClause[] = $tableName;
             }
         }
         // if $dotPost !== null
     }
     // set the aliases
     foreach ($aliases as $alias => $col) {
         $selectClause[] = $col . " AS " . $alias;
     }
     // add the criteria to WHERE clause
     // this will also add the table names to the FROM clause if they are not already
     // invluded via a LEFT JOIN
     foreach ($criteria->keys() as $key) {
         $criterion = $criteria->getCriterion($key);
         $someCriteria = $criterion->getAttachedCriterion();
         $someCriteriaLength = count($someCriteria);
         $table = null;
         for ($i = 0; $i < $someCriteriaLength; $i++) {
             $tableName = $someCriteria[$i]->getTable();
             $table = $criteria->getTableForAlias($tableName);
             if ($table !== null) {
                 $fromClause[] = $table . ' ' . $tableName;
             } else {
                 $fromClause[] = $tableName;
                 $table = $tableName;
             }
             $ignoreCase = ($criteria->isIgnoreCase() || $someCriteria[$i]->isIgnoreCase()) && strpos($dbMap->getTable($table)->getColumn($someCriteria[$i]->getColumn())->getType(), "VARCHAR") !== false;
             $someCriteria[$i]->setIgnoreCase($ignoreCase);
         }
         $criterion->setDB($db);
         $sb = "";
         $criterion->appendPsTo($sb, $params);
         $whereClause[] = $sb;
     }
     // 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 ((array) $criteria->getJoins() as $join) {
         // The join might have been established using an alias name
         $leftTable = $join->getLeftTableName();
         $leftTableAlias = '';
         if ($realTable = $criteria->getTableForAlias($leftTable)) {
             $leftTableAlias = " {$leftTable}";
             $leftTable = $realTable;
         }
         $rightTable = $join->getRightTableName();
         $rightTableAlias = '';
         if ($realTable = $criteria->getTableForAlias($rightTable)) {
             $rightTableAlias = " {$rightTable}";
             $rightTable = $realTable;
         }
         // determine if casing is relevant.
         if ($ignoreCase = $criteria->isIgnoreCase()) {
             $leftColType = $dbMap->getTable($leftTable)->getColumn($join->getLeftColumnName())->getType();
             $rightColType = $dbMap->getTable($rightTable)->getColumn($join->getRightColumnName())->getType();
             $ignoreCase = $leftColType == 'string' || $rightColType == 'string';
         }
         // build the condition
         $condition = '';
         foreach ($join->getConditions() as $index => $conditionDesc) {
             if ($ignoreCase) {
                 $condition .= $db->ignoreCase($conditionDesc['left']) . $conditionDesc['operator'] . $db->ignoreCase($conditionDesc['right']);
             } else {
                 $condition .= implode($conditionDesc);
             }
             if ($index + 1 < $join->countConditions()) {
                 $condition .= ' AND ';
             }
         }
         // add 'em to the queues..
         if ($joinType = $join->getJoinType()) {
             // real join
             if (!$fromClause) {
                 $fromClause[] = $leftTable . $leftTableAlias;
             }
             $joinTables[] = $rightTable . $rightTableAlias;
             $joinClause[] = $join->getJoinType() . ' ' . $rightTable . $rightTableAlias . " ON ({$condition})";
         } else {
             // implicit join, translates to a where
             $fromClause[] = $leftTable . $leftTableAlias;
             $fromClause[] = $rightTable . $rightTableAlias;
             $whereClause[] = $condition;
         }
     }
     // 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()) {
                 $orderByClause[] = $db->ignoreCaseInOrderBy("{$tableAlias}.{$columnAlias}") . $direction;
                 $selectClause[] = $db->ignoreCaseInOrderBy("{$tableAlias}.{$columnAlias}");
             } else {
                 $orderByClause[] = $orderByColumn;
             }
         }
     }
     if (empty($fromClause) && $criteria->getPrimaryTableName()) {
         $fromClause[] = $criteria->getPrimaryTableName();
     }
     // from / join tables quoten if it is necessary
     if ($db->useQuoteIdentifier()) {
         $fromClause = array_map(array($db, 'quoteIdentifierTable'), $fromClause);
         $joinClause = $joinClause ? $joinClause : array_map(array($db, 'quoteIdentifierTable'), $joinClause);
     }
     // 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 = "SELECT " . ($selectModifiers ? implode(" ", $selectModifiers) . " " : "") . implode(", ", $selectClause) . " 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());
     }
     return $sql;
 }