protected function setUp() { parent::setUp(); $this->c = new Criteria(); $this->savedAdapter = Propel::getDB(null); Propel::setDB(null, new DBSQLite()); }
public function testInvalidCharset() { $this->markTestSkipped(); $db = Propel::getDB(BookPeer::DATABASE_NAME); if ($db instanceof DBSQLite) { $this->markTestSkipped(); } $a = new Author(); $a->setFirstName("Б."); $a->setLastName("АКУНИН"); $a->save(); $authorNameWindows1251 = iconv("utf-8", "windows-1251", $a->getLastName()); $a->setLastName($authorNameWindows1251); // Different databases seem to handle invalid data differently (no surprise, I guess...) if ($db instanceof DBPostgres) { try { $a->save(); $this->fail("Expected an exception when saving non-UTF8 data to database."); } catch (Exception $x) { print $x; } } else { // No exception is thrown by MySQL ... (others need to be tested still) $a->save(); $a->reload(); $this->assertEquals("", $a->getLastName(), "Expected last_name to be empty (after inserting invalid charset data)"); } }
protected function addSortCriteria($c) { if ($sort_column = $this->getUser()->getAttribute('sort', 'nombre', 'sf_admin/cuenta/sort')) { $sort_column = Propel::getDB($c->getDbName())->quoteIdentifier($sort_column); if ($this->getUser()->getAttribute('type', 'asc', 'sf_admin/cuenta/sort') == 'asc') { $c->addAscendingOrderByColumn($sort_column); } else { $c->addDescendingOrderByColumn($sort_column); } } }
public function testCreateSelectSqlPart() { Propel::setDb('oracle', new DBOracle()); $db = Propel::getDB(); $c = new Criteria(); $c->addSelectColumn(BookPeer::ID); $c->addAsColumn('book_ID', BookPeer::ID); $fromClause = array(); $selectSql = $db->createSelectSqlPart($c, $fromClause); $this->assertEquals('SELECT book.ID, book.ID AS book_ID', $selectSql, 'createSelectSqlPart() returns a SQL SELECT clause with both select and as columns'); $this->assertEquals(array('book'), $fromClause, 'createSelectSqlPart() adds the tables from the select columns to the from clause'); }
/** * @see Command * * @throws \InvalidArgumentException When the target directory does not exist */ protected function execute(InputInterface $input, OutputInterface $output) { $this->writeSection($output, '[Propel] You are running the command: propel:table:drop'); $tablesToDelete = $input->getArgument('table'); if ($input->getOption('force')) { $nbTable = count($tablesToDelete); $tablePlural = $nbTable > 1 || $nbTable == 0 ? 's' : ''; if ('prod' === $this->getApplication()->getKernel()->getEnvironment()) { $count = count($input->getArgument('table')) ?: 'all'; $this->writeSection($output, 'WARNING: you are about to drop ' . $count . ' table' . $tablePlural . ' in production !', 'bg=red;fg=white'); if (false === $this->askConfirmation($output, 'Are you sure ? (y/n) ', false)) { $output->writeln('<info>Aborted, nice decision !</info>'); return -2; } } try { list($name, $config) = $this->getConnection($input, $output); $connection = \Propel::getConnection($name); $adapter = \Propel::getDB($name); $showStatement = $connection->prepare('SHOW TABLES;'); $showStatement->execute(); $allTables = $showStatement->fetchAll(\PDO::FETCH_COLUMN); if ($nbTable) { foreach ($tablesToDelete as $tableToDelete) { if (!array_search($tableToDelete, $allTables)) { throw new \InvalidArgumentException(sprintf('Table %s doesn\'t exist in the database.', $tableToDelete)); } } } else { $tablesToDelete = $allTables; } $connection->exec('SET FOREIGN_KEY_CHECKS = 0;'); array_walk($tablesToDelete, function (&$table, $key, $dbAdapter) { $table = $dbAdapter->quoteIdentifierTable($table); }, $adapter); $tablesToDelete = join(', ', $tablesToDelete); if ('' !== $tablesToDelete) { $connection->exec('DROP TABLE ' . $tablesToDelete . ' ;'); $output->writeln(sprintf('Table' . $tablePlural . ' <info><comment>%s</comment> has been dropped.</info>', $tablesToDelete)); } else { $output->writeln('<info>No tables have been dropped</info>'); } $connection->exec('SET FOREIGN_KEY_CHECKS = 1;'); } catch (\Exception $e) { $this->writeSection($output, array('[Propel] Exception catched', '', $e->getMessage()), 'fg=white;bg=red'); } } else { $output->writeln('<error>You have to use the "--force" option to drop some tables.</error>'); } }
/** * Init some properties with the help of outer class * @param Criteria $criteria The outer class */ public function init(Criteria $criteria) { // init $this->db try { $db = Propel::getDB($criteria->getDbName()); $this->setDB($db); } catch (Exception $e) { // we are only doing this to allow easier debugging, so // no need to throw up the exception, just make note of it. Propel::log("Could not get a DBAdapter, sql may be wrong", Propel::LOG_ERR); } // init $this->realtable $realtable = $criteria->getTableForAlias($this->table); $this->realtable = $realtable ? $realtable : $this->table; }
public function executeIndex(sfWebRequest $request) { $this->setLayout(false); sfConfig::set('sf_web_debug', false); // Open the connection $con = \Propel::getConnection($request->getParameter('connection')); // Get the adapter $db = \Propel::getDB($request->getParameter('connection')); try { $this->query = base64_decode($request->getParameter('base64_query')); $stmt = $db->doExplainPlan($con, $this->query); $this->results = $stmt->fetchAll(\PDO::FETCH_ASSOC); } catch (Exception $e) { $this->getResponse()->setContent('<div class="error">This query cannot be explained.</div>'); } }
/** * @link http://propel.phpdb.org/trac/ticket/425 */ public function testMultipleFunctionInCriteria() { $db = Propel::getDB(BookPeer::DATABASE_NAME); try { $c = new Criteria(); $c->setDistinct(); if ($db instanceof DBPostgres) { $c->addSelectColumn("substring(" . BookPeer::TITLE . " from position('Potter' in " . BookPeer::TITLE . ")) AS col"); } else { $this->markTestSkipped(); } $stmt = BookPeer::doSelectStmt($c); } catch (PropelException $x) { $this->fail("Paring of nested functions failed: " . $x->getMessage()); } }
public function testGetLatestQuery() { $con = Propel::getConnection(BookPeer::DATABASE_NAME); $con->setLastExecutedQuery(123); $this->assertEquals(123, $con->getLastExecutedQuery(), 'DebugPDO has getter and setter for last executed query'); $c = new Criteria(); $c->add(BookPeer::TITLE, 'Harry%s', Criteria::LIKE); $books = BookPeer::doSelect($c, $con); $latestExecutedQuery = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` WHERE book.TITLE LIKE 'Harry%s'"; if (!Propel::getDB(BookPeer::DATABASE_NAME)->useQuoteIdentifier()) { $latestExecutedQuery = str_replace('`', '', $latestExecutedQuery); } $this->assertEquals($latestExecutedQuery, $con->getLastExecutedQuery(), 'PropelPDO updates the last executed query on every request'); BookPeer::doDeleteAll($con); $latestExecutedQuery = "DELETE FROM book"; $this->assertEquals($latestExecutedQuery, $con->getLastExecutedQuery(), 'PropelPDO updates the last executed query on every request'); }
public static function doBackupRecord(\Criteria $criteria, PropelPDO $con) { $db = Propel::getDB($criteria->getDbName()); $dbMap = Propel::getDatabaseMap($criteria->getDbName()); $keys = $criteria->keys(); if (!empty($keys)) { $tableName = $criteria->getTableName($keys[0]); } else { throw new PropelException("Database insert attempted without anything specified to insert"); } $tableMap = $dbMap->getTable($tableName); $whereClause = array(); $peer = $tableMap->getPeerClassname(); $versionTable = $peer::$workspaceBehaviorVersionName; $originTable = $tableMap->getName(); $tables = $criteria->getTablesColumns(); if (empty($tables)) { throw new \PropelException("Empty Criteria"); } $fields = array_keys($tableMap->getColumns()); $fields = implode(', ', $fields); foreach ($tables as $tableName => $columns) { $whereClause = array(); $params = array(); $stmt = null; try { foreach ($columns as $colName) { $sb = ""; $criteria->getCriterion($colName)->appendPsTo($sb, $params); $whereClause[] = $sb; } $sql = sprintf("INSERT INTO %s (%s) SELECT %s FROM %s WHERE %s", $versionTable, $fields, $fields, $originTable, implode(" AND ", $whereClause)); $stmt = $con->prepare($sql); $db->bindValues($stmt, $params, $dbMap); $stmt->execute(); $stmt->closeCursor(); } catch (Exception $e) { Propel::log($e->getMessage(), Propel::LOG_ERR); throw new PropelException(sprintf('Unable to execute INSERT INTO statement [%s]', $sql), $e); } } // for each table }
/** * Renders the profiler panel for the given token. * * @param string $token The profiler token * @param string $connection The connection name * @param integer $query * * @return Symfony\Component\HttpFoundation\Response A Response instance */ public function explainAction($token, $connection, $query) { $profiler = $this->container->get('profiler'); $profiler->disable(); $profile = $profiler->loadProfile($token); $queries = $profile->getCollector('propel')->getQueries(); if (!isset($queries[$query])) { return new Response('This query does not exist.'); } // Open the connection $con = \Propel::getConnection($connection); // Get the adapter $db = \Propel::getDB($connection); try { $stmt = $db->doExplainPlan($con, $queries[$query]['sql']); $results = $stmt->fetchAll(\PDO::FETCH_ASSOC); } catch (\Exception $e) { return new Response('<div class="error">This query cannot be explained.</div>'); } return $this->container->get('templating')->renderResponse('PropelBundle:Panel:explain.html.twig', array('data' => $results, 'query' => $query)); }
public function testOrderByIgnoreCase() { $originalDB = Propel::getDB(); Propel::setDB(null, new DBMySQL()); $criteria = new Criteria(); $criteria->setIgnoreCase(true); $criteria->addAscendingOrderByColumn(BookPeer::TITLE); $criteria->addAsColumn(BookPeer::TITLE, 'title'); $params = array(); $sql = BasePeer::createSelectSql($criteria, $params); $expectedSQL = 'SELECT UPPER(book.TITLE) FROM `book` ORDER BY UPPER(book.TITLE) ASC'; $this->assertEquals($expectedSQL, $sql); $criteria = new Criteria(); $criteria->setIgnoreCase(true); $criteria->addAscendingOrderByColumn(BookPeer::TITLE); BookPeer::addSelectColumns($criteria); $params = array(); $sql = BasePeer::createSelectSql($criteria, $params); $expectedSQL = 'SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID, UPPER(book.TITLE) FROM `book` ORDER BY UPPER(book.TITLE) ASC'; $this->assertEquals($expectedSQL, $sql); Propel::setDB(null, $originalDB); }
public static function populate($con = null) { if ($con === null) { $con = Propel::getConnection(BookPeer::DATABASE_NAME); } $con->beginTransaction(); // Add publisher records // --------------------- $scholastic = new Publisher(); $scholastic->setName("Scholastic"); // do not save, will do later to test cascade $morrow = new Publisher(); $morrow->setName("William Morrow"); $morrow->save($con); $morrow_id = $morrow->getId(); $penguin = new Publisher(); $penguin->setName("Penguin"); $penguin->save(); $penguin_id = $penguin->getId(); $vintage = new Publisher(); $vintage->setName("Vintage"); $vintage->save($con); $vintage_id = $vintage->getId(); $rowling = new Author(); $rowling->setFirstName("J.K."); $rowling->setLastName("Rowling"); // no save() $stephenson = new Author(); $stephenson->setFirstName("Neal"); $stephenson->setLastName("Stephenson"); $stephenson->save($con); $stephenson_id = $stephenson->getId(); $byron = new Author(); $byron->setFirstName("George"); $byron->setLastName("Byron"); $byron->save($con); $byron_id = $byron->getId(); $grass = new Author(); $grass->setFirstName("Gunter"); $grass->setLastName("Grass"); $grass->save($con); $grass_id = $grass->getId(); $phoenix = new Book(); $phoenix->setTitle("Harry Potter and the Order of the Phoenix"); $phoenix->setISBN("043935806X"); $phoenix->setAuthor($rowling); $phoenix->setPublisher($scholastic); $phoenix->setPrice(10.99); $phoenix->save($con); $phoenix_id = $phoenix->getId(); $qs = new Book(); $qs->setISBN("0380977427"); $qs->setTitle("Quicksilver"); $qs->setPrice(11.99); $qs->setAuthor($stephenson); $qs->setPublisher($morrow); $qs->save($con); $qs_id = $qs->getId(); $dj = new Book(); $dj->setISBN("0140422161"); $dj->setTitle("Don Juan"); $dj->setPrice(12.99); $dj->setAuthor($byron); $dj->setPublisher($penguin); $dj->save($con); $dj_id = $dj->getId(); $td = new Book(); $td->setISBN("067972575X"); $td->setTitle("The Tin Drum"); $td->setPrice(13.99); $td->setAuthor($grass); $td->setPublisher($vintage); $td->save($con); $td_id = $td->getId(); $r1 = new Review(); $r1->setBook($phoenix); $r1->setReviewedBy("Washington Post"); $r1->setRecommended(true); $r1->setReviewDate(time()); $r1->save($con); $r1_id = $r1->getId(); $r2 = new Review(); $r2->setBook($phoenix); $r2->setReviewedBy("New York Times"); $r2->setRecommended(false); $r2->setReviewDate(time()); $r2->save($con); $r2_id = $r2->getId(); $blob_path = _LOB_SAMPLE_FILE_PATH . '/tin_drum.gif'; $clob_path = _LOB_SAMPLE_FILE_PATH . '/tin_drum.txt'; $m1 = new Media(); $m1->setBook($td); $m1->setCoverImage(file_get_contents($blob_path)); // CLOB is broken in PDO OCI, see http://pecl.php.net/bugs/bug.php?id=7943 if (get_class(Propel::getDB()) != "DBOracle") { $m1->setExcerpt(file_get_contents($clob_path)); } $m1->save($con); // Add book list records // --------------------- // (this is for many-to-many tests) $blc1 = new BookClubList(); $blc1->setGroupLeader("Crazyleggs"); $blc1->setTheme("Happiness"); $brel1 = new BookListRel(); $brel1->setBook($phoenix); $brel2 = new BookListRel(); $brel2->setBook($dj); $blc1->addBookListRel($brel1); $blc1->addBookListRel($brel2); $blc1->save(); $bemp1 = new BookstoreEmployee(); $bemp1->setName("John"); $bemp1->setJobTitle("Manager"); $bemp2 = new BookstoreEmployee(); $bemp2->setName("Pieter"); $bemp2->setJobTitle("Clerk"); $bemp2->setSupervisor($bemp1); $bemp2->save($con); $bemp3 = new BookstoreCashier(); $bemp3->setName("Tim"); $bemp3->setJobTitle("Cashier"); $bemp3->save($con); $role = new AcctAccessRole(); $role->setName("Admin"); $bempacct = new BookstoreEmployeeAccount(); $bempacct->setBookstoreEmployee($bemp1); $bempacct->setAcctAccessRole($role); $bempacct->setLogin("john"); $bempacct->setPassword("johnp4ss"); $bempacct->save($con); // Add bookstores $store = new Bookstore(); $store->setStoreName("Amazon"); $store->setPopulationServed(5000000000); // world population $store->setTotalBooks(300); $store->save($con); $store = new Bookstore(); $store->setStoreName("Local Store"); $store->setPopulationServed(20); $store->setTotalBooks(500000); $store->save($con); $summary = new BookSummary(); $summary->setSummarizedBook($phoenix); $summary->setSummary("Harry Potter does some amazing magic!"); $summary->save(); $con->commit(); }
public function testDebugLatestQuery() { $con = Propel::getConnection(BookPeer::DATABASE_NAME); $c = new Criteria(); $c->add(BookPeer::TITLE, 'Harry%s', Criteria::LIKE); $con->useDebug(false); $this->assertEquals('', $con->getLastExecutedQuery(), 'PropelPDO reinitializes the latest query when debug is set to false'); $books = BookPeer::doSelect($c, $con); $this->assertEquals('', $con->getLastExecutedQuery(), 'PropelPDO does not update the last executed query when useLogging is false'); $con->useDebug(true); $books = BookPeer::doSelect($c, $con); $latestExecutedQuery = "SELECT book.id, book.title, book.isbn, book.price, book.publisher_id, book.author_id FROM `book` WHERE book.title LIKE 'Harry%s'"; if (!Propel::getDB(BookPeer::DATABASE_NAME)->useQuoteIdentifier()) { $latestExecutedQuery = str_replace('`', '', $latestExecutedQuery); } $this->assertEquals($latestExecutedQuery, $con->getLastExecutedQuery(), 'PropelPDO updates the last executed query when useLogging is true'); BookPeer::doDeleteAll($con); $latestExecutedQuery = "DELETE FROM `book`"; $this->assertEquals($latestExecutedQuery, $con->getLastExecutedQuery(), 'PropelPDO updates the last executed query on delete operations'); $sql = 'DELETE FROM book WHERE 1=1'; $con->exec($sql); $this->assertEquals($sql, $con->getLastExecutedQuery(), 'PropelPDO updates the last executed query on exec operations'); $sql = 'DELETE FROM book WHERE 2=2'; $con->query($sql); $this->assertEquals($sql, $con->getLastExecutedQuery(), 'PropelPDO updates the last executed query on query operations'); $stmt = $con->prepare('DELETE FROM book WHERE 1=:p1'); $stmt->bindValue(':p1', '2'); $stmt->execute(); $this->assertEquals("DELETE FROM book WHERE 1='2'", $con->getLastExecutedQuery(), 'PropelPDO updates the last executed query on prapared statements'); $con->useDebug(false); $this->assertEquals('', $con->getLastExecutedQuery(), 'PropelPDO reinitializes the latest query when debug is set to false'); $con->useDebug(true); }
protected function getCountStatement($con = null) { $dbMap = Propel::getDatabaseMap($this->getDbName()); $db = Propel::getDB($this->getDbName()); if ($con === null) { $con = Propel::getConnection($this->getDbName(), Propel::CONNECTION_READ); } // check that the columns of the main class are already added (if this is the primary ModelCriteria) if (!$this->hasSelectClause() && !$this->getPrimaryCriteria()) { $this->addSelfSelectColumns(); } $this->configureSelectColumns(); $needsComplexCount = $this->getGroupByColumns() || $this->getOffset() || $this->getLimit() || $this->getHaving() || in_array(Criteria::DISTINCT, $this->getSelectModifiers()); try { $this->basePreSelect($con); $params = array(); if ($needsComplexCount) { if (BasePeer::needsSelectAliases($this)) { if ($this->getHaving()) { throw new PropelException('Propel cannot create a COUNT query when using HAVING and duplicate column names in the SELECT part'); } $db->turnSelectColumnsToAliases($this); } $selectSql = BasePeer::createSelectSql($this, $params); $sql = 'SELECT COUNT(*) FROM (' . $selectSql . ') propelmatch4cnt'; } else { // Replace SELECT columns with COUNT(*) $this->clearSelectColumns()->addSelectColumn('COUNT(*)'); $sql = BasePeer::createSelectSql($this, $params); } $stmt = $con->prepare($sql); $db->bindValues($stmt, $params, $dbMap); $stmt->execute(); } catch (PropelException $e) { if ($stmt) { $stmt = null; // close } Propel::log($e->getMessage(), Propel::LOG_ERR); throw new PropelException(sprintf('Unable to execute COUNT statement [%s]', $sql), $e); } return $stmt; }
protected function getCountStatement($con = null) { $dbMap = Propel::getDatabaseMap($this->getDbName()); $db = Propel::getDB($this->getDbName()); if ($con === null) { $con = Propel::getConnection($this->getDbName(), Propel::CONNECTION_READ); } $con->beginTransaction(); try { $this->basePreSelect($con); $key = $this->getQueryKey(); if ($key && $this->cacheContains($key)) { $params = $this->getParams(); $sql = $this->cacheFetch($key); } else { if (!$this->hasSelectClause() && !$this->getPrimaryCriteria()) { $this->addSelfSelectColumns(); } $params = array(); $needsComplexCount = $this->getGroupByColumns() || $this->getOffset() || $this->getLimit() || $this->getHaving() || in_array(Criteria::DISTINCT, $this->getSelectModifiers()); if ($needsComplexCount) { if (BasePeer::needsSelectAliases($this)) { if ($this->getHaving()) { throw new PropelException('Propel cannot create a COUNT query when using HAVING and duplicate column names in the SELECT part'); } $db->turnSelectColumnsToAliases($this); } $selectSql = BasePeer::createSelectSql($this, $params); $sql = 'SELECT COUNT(*) FROM (' . $selectSql . ') propelmatch4cnt'; } else { // Replace SELECT columns with COUNT(*) $this->clearSelectColumns()->addSelectColumn('COUNT(*)'); $sql = BasePeer::createSelectSql($this, $params); } if ($key) { $this->cacheStore($key, $sql); } } $stmt = $con->prepare($sql); BasePeer::populateStmtValues($stmt, $params, $dbMap, $db); $stmt->execute(); $con->commit(); } catch (PropelException $e) { $con->rollback(); throw $e; } return $stmt; }
/** * 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()); $fromClause = array(); $joinClause = array(); $joinTables = array(); $whereClause = array(); $orderByClause = array(); $orderBy = $criteria->getOrderByColumns(); $groupBy = $criteria->getGroupByColumns(); $ignoreCase = $criteria->isIgnoreCase(); // get the first part of the SQL statement, the SELECT part $selectSql = self::createSelectSqlPart($criteria, $fromClause); // 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->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 ($criteria->getJoins() as $join) { // The join might have been established using an alias name $leftTable = $join->getLeftTableName(); if ($realTable = $criteria->getTableForAlias($leftTable)) { $leftTableForFrom = $realTable . ' ' . $leftTable; $leftTable = $realTable; } else { $leftTableForFrom = $leftTable; } $rightTable = $join->getRightTableName(); if ($realTable = $criteria->getTableForAlias($rightTable)) { $rightTableForFrom = $realTable . ' ' . $rightTable; $rightTable = $realTable; } else { $rightTableForFrom = $rightTable; } // 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[] = $leftTableForFrom; } $joinTables[] = $rightTableForFrom; $joinClause[] = $join->getJoinType() . ' ' . $rightTableForFrom . " ON ({$condition})"; } else { // implicit join, translates to a where $fromClause[] = $leftTableForFrom; $fromClause[] = $rightTableForFrom; $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()) { $ignoreCaseColumn = $db->ignoreCaseInOrderBy("{$tableAlias}.{$columnAlias}"); $orderByClause[] = $ignoreCaseColumn . $direction; $selectSql .= ', ' . $ignoreCaseColumn; } else { $orderByClause[] = $orderByColumn; } } } if (empty($fromClause) && $criteria->getPrimaryTableName()) { $fromClause[] = $criteria->getPrimaryTableName(); } // 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); } // 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; }
/** * 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 Creole 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 = strpos($columnName, '('); $dotPos = strpos($columnName, '.'); // [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()) && $dbMap->getTable($table)->getColumn($someCriteria[$i]->getColumn())->getType() == "string"; $someCriteria[$i]->setIgnoreCase($ignoreCase); } $criterion->setDB($db); $sb = ""; $criterion->appendPsTo($sb, $params); $whereClause[] = $sb; } // handle RIGHT (straight) joins // Loop through the 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 // New Code. foreach ((array) $criteria->getJoins() as $join) { // we'll only loop if there's actually something here // 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 if ($ignoreCase) { $condition = $db->ignoreCase($join->getLeftColumn()) . '=' . $db->ignoreCase($join->getRightColumn()); } else { $condition = $join->getLeftColumn() . '=' . $join->getRightColumn(); } // add 'em to the queues.. if ($joinType = $join->getJoinType()) { if (!$fromClause) { $fromClause[] = $leftTable . $leftTableAlias; } $joinTables[] = $rightTable . $rightTableAlias; $joinClause[] = $join->getJoinType() . ' ' . $rightTable . $rightTableAlias . " ON ({$condition})"; } else { $fromClause[] = $leftTable . $leftTableAlias; $fromClause[] = $rightTable . $rightTableAlias; $whereClause[] = $condition; } } // Unique from clause elements $fromClause = array_unique($fromClause); // 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]); } } } /* // Old Code. $joins =& $criteria->getJoins(); if (!empty($joins)) { for ($i=0, $joinSize=count($joins); $i < $joinSize; $i++) { $join =& $joins[$i]; $join1 = $join->getLeftColumn(); $join2 = $join->getRightColumn(); $tableName = substr($join1, 0, strpos($join1, '.')); $table = $criteria->getTableForAlias($tableName); if ($table !== null) { $fromClause[] = $table . ' ' . $tableName; } else { $fromClause[] = $tableName; } $dot = strpos($join2, '.'); $tableName = substr($join2, 0, $dot); $table = $criteria->getTableForAlias($tableName); if ($table !== null) { $fromClause[] = $table . ' ' . $tableName; } else { $fromClause[] = $tableName; $table = $tableName; } $ignoreCase = ($criteria->isIgnoreCase() && ($dbMap->getTable($table)->getColumn(substr($join2, $dot + 1))->getType() == "string")); if ($ignoreCase) { $whereClause[] = $db->ignoreCase($join1) . '=' . $db->ignoreCase($join2); } else { $whereClause[] = $join1 . '=' . $join2; } if ($join->getJoinType()) { $leftTable = $fromClause[count($fromClause) - 2]; $rightTable = $fromClause[count($fromClause) - 1]; $onClause = $whereClause[count($whereClause) - 1]; unset($whereClause[count($whereClause) - 1]); $fromClause [] = $leftTable . ' ' . $join->getJoinType() . ' ' . $rightTable . ' ON ' . $onClause; // remove all references to joinTables made by selectColumns, criteriaColumns for ($i = 0, $fromClauseSize=count($fromClause); $i < $fromClauseSize; $i++) { if ($fromClause[$i] == $leftTable || $fromClause[$i] == $rightTable) { unset($fromClause[$i]); } } } // If join type } // Join for loop } // If Joins */ // 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 = strpos($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 ($column && $column->getType() == 'string') { $orderByClause[] = $db->ignoreCaseInOrderBy("{$tableAlias}.{$columnAlias}") . $direction; $selectClause[] = $db->ignoreCaseInOrderBy("{$tableAlias}.{$columnAlias}"); } else { $orderByClause[] = $orderByColumn; } } } // Build the SQL from the arrays we compiled $sql = "SELECT " . ($selectModifiers ? implode(" ", $selectModifiers) . " " : "") . implode(", ", $selectClause) . " FROM " . (!empty($joinClause) && count($fromClause) > 1 && substr(get_class($db), 0, 7) == 'DBMySQL' ? "(" . implode(", ", $fromClause) . ")" : implode(", ", $fromClause)) . ($joinClause ? ' ' . implode(' ', $joinClause) : '') . ($whereClause ? " WHERE " . implode(" AND ", $whereClause) : "") . ($groupByClause ? " GROUP BY " . implode(",", $groupByClause) : "") . ($havingString ? " HAVING " . $havingString : "") . ($orderByClause ? " ORDER BY " . implode(",", $orderByClause) : ""); Propel::log($sql . ' [LIMIT: ' . $criteria->getLimit() . ', OFFSET: ' . $criteria->getOffset() . ']', Propel::LOG_DEBUG); return $sql; }
/** * Method to create select SQL. * * @param Criteria $criteria object used to create the SELECT statement. * @param String $tableName * @param Array &$params * @throws PropelException Any exceptions caught during processing will be * rethrown wrapped into a PropelException. */ private function createSelectSql($criteria, $tableName, &$params) { $db = Propel::getDB($criteria->getDbName()); // redundant definition $selectModifiers = array(); $selectClause = array(); $fromClause = array(); $joinClause = array(); $joinTables = array(); $whereClause = array(); $orderByClause = array(); $groupByClause = array(); $orderBy = $criteria->getOrderByColumns(); $groupBy = $criteria->getGroupByColumns(); $ignoreCase = $criteria->isIgnoreCase(); $select = $criteria->getSelectColumns(); $aliases = $criteria->getAsColumns(); $fromClause[] = $criteria->getDBArrayTable(); // simple copy $selectModifiers = $criteria->getSelectModifiers(); // get selected columns foreach ($select as $columnName) { $tableName = null; $selectClause[] = $columnName; // the full column name: e.g. MAX(books.price) $parenPos = strpos($columnName, '('); $dotPos = strpos($columnName, '.'); // [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 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()) && $dbMap->getTable($table)->getColumn($someCriteria[$i]->getColumn())->getType() == "string"; $someCriteria[$i]->setIgnoreCase($ignoreCase); } $criterion->setDB($db); $cri['table'] = $criterion->table; $cri['field'] = $criterion->column; $cri['comparison'] = $criterion->comparison == '=' ? '==' : $criterion->comparison; $cri['value'] = $criterion->getValue(); $sb = ""; $sb .= "\$row['" . $cri['field'] . "'] " . $cri['comparison'] . "'" . $cri['value'] . "'"; $params[] = $cri; //$criterion->appendPsTo($sb, $params); $whereClause[] = $sb; } // Unique from clause elements $fromClause = array_unique($fromClause); 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 = strpos($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 = ''; } $orderByClause[] = array('columnName' => $columnName, 'direction' => $direction); } } // Build the SQL from the arrays we compiled $sql = "SELECT " . ($selectModifiers ? implode(" ", $selectModifiers) . " " : "") . implode(", ", $selectClause) . " FROM " . $fromClause[0] . ($whereClause ? " WHERE " . implode(" AND ", $whereClause) : "") . ($groupByClause ? " GROUP BY " . implode(",", $groupByClause) : ""); $dataSql['selectClause'] = $selectClause; $dataSql['fromClause'] = $fromClause; $dataSql['whereClause'] = $whereClause; $dataSql['orderByClause'] = $orderByClause; $dataSql['sql'] = $sql; return $dataSql; }
public function testCreateSelectSqlPartAliasAll() { $db = Propel::getDB(BookPeer::DATABASE_NAME); $c = new Criteria(); $c->addSelectColumn(BookPeer::ID); $c->addAsColumn('book_ID', BookPeer::ID); $fromClause = array(); $selectSql = $db->createSelectSqlPart($c, $fromClause, true); $this->assertEquals('SELECT book.ID AS book_ID_1, book.ID AS book_ID', $selectSql, 'createSelectSqlPart() aliases all columns if passed true as last parameter'); $this->assertEquals(array(), $fromClause, 'createSelectSqlPart() does not add the tables from an all-aliased list of select columns'); }
define('API_DIR', BASE_DIR . 'api' . DIRECTORY_SEPARATOR); define('MODULES_DIR', BASE_DIR . 'modules' . DIRECTORY_SEPARATOR); define('VIEWS_DIR', BASE_DIR . 'views' . DIRECTORY_SEPARATOR); define('LOCALES_DIR', BASE_DIR . 'locales' . DIRECTORY_SEPARATOR); require INC_DIR . 'Exceptions.php'; require INC_DIR . 'Val.php'; require INC_DIR . 'Config.php'; require INC_DIR . 'Localizer.php'; require INC_DIR . 'Router.php'; require INC_DIR . 'ControllerDispatcher.php'; Config::load(CONFIG_DIR . 'cfg.json'); $baseUrl = Config::get('baseurl'); if (!$baseUrl) { throw new \Exception('Invalid configuration. Missing "baseurl" definition.'); } Localizer::load(LOCALES_DIR); Router::init($baseUrl); /* Setup propel ---------------------------------------------*/ set_include_path(get_include_path() . PATH_SEPARATOR . ENTITIES_CLASSES_DIR . LIB_DIR . PATH_SEPARATOR . BASE_DIR . PATH_SEPARATOR); require_once LIB_DIR . '/propel/runtime/lib/Propel.php'; try { \Propel::init(ENTITIES_DIR . 'build' . DIRECTORY_SEPARATOR . 'conf' . DIRECTORY_SEPARATOR . PROJECT_NAME . '-conf.php'); \Propel::getDB()->setCharset(\Propel::getConnection(), 'UTF8'); \Transaction::initAmounts(Config::get('amounts', TYPE_ARRAY), Config::get('member_fee', TYPE_FLOAT), Config::get('base_currency')); } catch (\Exception $e) { // Do NOT output stacktrace because it holds the plain pg password. echo $e->getMessage(); error_log($e->__toString()); exit; }
public static function doMultiInsert($tableName, $values, $dbName = null, $rawValues = false, $method = 'INSERT') { $query = PropelQuery::from($tableName); if (!$dbName) { $dbName = $query->getDbName(); } $adapter = Propel::getDB($dbName); $tableMap = $query->getTableMap(); $dbMap = $tableMap->getDatabaseMap(); $con = Propel::getConnection($dbName, Propel::CONNECTION_WRITE); try { $sql = ''; $p = 1; $params = array(); $columns = array(); $colNames = array(); foreach ($values as $row) { if ($p === 1) { foreach (array_keys($row) as $phpName) { if ($tableMap->hasColumnByPhpName($phpName)) { $columns[$phpName] = $tableMap->getColumnByPhpName($phpName); $colNames[$phpName] = $columns[$phpName]->getColumnName(); } } } $sql .= $p === 1 ? '(' : ',('; $pp = $p; foreach ($columns as $phpName => $column) { $sql .= ($p === $pp ? '' : ',') . ':p' . $p++; $value = $row[$phpName]; if (!$rawValues) { $value = self::_getColumnRawValue($column, $value); } $params[] = array('column' => $colNames[$phpName], 'table' => $tableMap->getName(), 'value' => $value); } $sql .= ')'; } if ($p === 1) { return 0; } $table = $tableMap->getName(); if ($adapter->useQuoteIdentifier()) { $colNames = array_map(array($adapter, 'quoteIdentifier'), $colNames); $table = $adapter->quoteIdentifierTable($table); } $sql = $method . ' INTO ' . $table . ' (' . implode(',', $colNames) . ')' . ' VALUES ' . $sql; $adapter->cleanupSQL($sql, $params, new Criteria($dbName), $dbMap); $stmt = $con->prepare($sql); $adapter->bindValues($stmt, $params, $dbMap, $adapter); $stmt->execute(); return $stmt->rowCount(); } catch (Exception $e) { Propel::log($e->getMessage(), Propel::LOG_ERR); throw new PropelException(sprintf('Unable to execute INSERT statement [%s]', $sql), $e); } }
public function testMssqlApplyLimitWithOffsetMultipleOrderBy() { $db = Propel::getDB(BookPeer::DATABASE_NAME); if (!$db instanceof DBMSSQL) { $this->markTestSkipped(); } $c = new Criteria(BookPeer::DATABASE_NAME); $c->addSelectColumn(BookPeer::ID); $c->addSelectColumn(BookPeer::TITLE); $c->addSelectColumn(PublisherPeer::NAME); $c->addAsColumn('PublisherName', '(SELECT MAX(publisher.NAME) FROM publisher WHERE publisher.ID = book.PUBLISHER_ID)'); $c->addJoin(BookPeer::PUBLISHER_ID, PublisherPeer::ID, Criteria::LEFT_JOIN); $c->addDescendingOrderByColumn('PublisherName'); $c->addAscendingOrderByColumn(BookPeer::TITLE); $c->setOffset(20); $c->setLimit(20); $params = array(); $expectedSql = "SELECT [book.ID], [book.TITLE], [publisher.NAME], [PublisherName] FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT MAX(publisher.NAME) FROM publisher WHERE publisher.ID = book.PUBLISHER_ID) DESC, book.TITLE ASC) AS [RowNumber], book.ID AS [book.ID], book.TITLE AS [book.TITLE], publisher.NAME AS [publisher.NAME], (SELECT MAX(publisher.NAME) FROM publisher WHERE publisher.ID = book.PUBLISHER_ID) AS [PublisherName] FROM book LEFT JOIN publisher ON (book.PUBLISHER_ID=publisher.ID)) AS derivedb WHERE RowNumber BETWEEN 21 AND 40"; $sql = BasePeer::createSelectSql($c, $params); $this->assertEquals($expectedSql, $sql); }
public function testHavingRaw() { $db = Propel::getDB(); $c = new Criteria(); $c->addSelectColumn(BookPeer::TITLE); $c->addAsColumn("isb_n", BookPeer::ISBN); $c->addHaving('isb_n = ?', '1234567890123', PDO::PARAM_STR); $expected = 'SELECT book.title, book.isbn AS ' . $db->quoteIdentifier('isb_n') . ' FROM book HAVING isb_n = :p1'; $params = array(); $result = BasePeer::createSelectSql($c, $params); $this->assertEquals($expected, $result); }
/** * Restore database from file. * * @todo Fix $maxExecutionTime. * * @param string|resource $file * @param array|null $tables * @param float $maxExecutionTime * @param int $continueLine * @param Curry_Backend|null $backend * @return bool True on success, false otherwise. */ public static function restoreFromFile($file, $tables = null, $maxExecutionTime = 0, $continueLine = 0, Curry_Backend $backend = null) { global $CURRY_DATABASE_RESTORE; $CURRY_DATABASE_RESTORE = true; $fp = is_string($file) ? fopen($file, "r") : $file; $t = microtime(true); $total = 0; $skipped = 0; $failed = 0; $session = new Zend_Session_Namespace(__CLASS__); $con = Propel::getConnection(); $con->beginTransaction(); $adapter = Propel::getDB(); if ($adapter instanceof DBMySQL) { $con->exec("SET foreign_key_checks = 0"); } // Read header $firstline = stream_get_line($fp, self::MAX_LINE_LENGTH, "\n"); $header = json_decode($firstline, true); if (is_array($header) && isset($header['header'])) { $header = $header['header']; // Check header version $version = isset($header['version']) ? (int) $header['version'] : 0; if ($version > self::VERSION) { throw new Exception('Unsupported database version. The file you are trying to restore from is from a newer version of currycms.'); } // Check page version $pageVersion = isset($header['page-version']) ? (int) $header['page-version'] : 0; if ($pageVersion > Page::VERSION) { throw new Exception('Unsupported page version. The file you are trying to restore from is from a newer version of currycms.'); } if ($backend) { $backend->addMessage("Restoring from " . $header['date']); } if ($pageVersion !== Page::VERSION) { if ($backend) { $backend->addMessage("Migrating data from version {$pageVersion} to " . Page::VERSION, Curry_Backend::MSG_WARNING); } Page::preMigrate($pageVersion); } } else { throw new Exception('Invalid header'); } // Empty tables if ($continueLine == 0) { foreach (Curry_Propel::getModels() as $classes) { foreach ($classes as $table) { try { if (is_array($tables) && !in_array($table, $tables)) { continue; } if (!method_exists($table, 'delete')) { if ($backend) { $backend->addMessage("Skipping read-only table: {$table}", Curry_Backend::MSG_WARNING); } continue; } $tableName = PropelQuery::from($table)->getTableMap()->getName(); // use basePeer to avoid foreign key emulation in Normal peer class BasePeer::doDeleteAll($tableName, $con); } catch (Exception $e) { throw new Exception('Unable to empty table ' . $table . ': ' . $e->getMessage()); } } } if ($backend) { $backend->addMessage("Cleared tables in " . round(microtime(true) - $t, 2) . "s"); } $t = microtime(true); } else { $total = $session->total; $skipped = $session->skipped; $failed = $session->failed; if ($backend) { $backend->addMessage("Continuing from line {$continueLine}."); } for ($i = 0; $i < $continueLine; ++$i) { stream_get_line($fp, self::MAX_LINE_LENGTH, "\n"); } } $currentTable = null; $buffer = array(); while (!feof($fp)) { // Read line $data = json_decode(stream_get_line($fp, self::MAX_LINE_LENGTH, "\n"), true); ++$total; if (is_array($data) && isset($data['table'])) { if (is_array($tables) && !in_array($data['table'], $tables) || !method_exists($data['table'], 'delete')) { ++$skipped; continue; } // Verify columns for new table if ($data['table'] !== $currentTable && $currentTable !== null && $backend) { $backend->addMessage('Restoring rows for table ' . $data['table']); $columns = Curry_Array::objectsToArray(PropelQuery::from($data['table'])->getTableMap()->getColumns(), null, 'getPhpName'); $added = array_diff($columns, array_keys($data['values'])); $removed = array_diff(array_keys($data['values']), $columns); if (count($added)) { $backend->addMessage('New column(s): ' . join(', ', $added), Curry_Backend::MSG_WARNING); } if (count($removed)) { $backend->addMessage('Removed column(s): ' . join(', ', $removed), Curry_Backend::MSG_WARNING); } } // Flush buffer when changing tables if ($data['table'] !== $currentTable || count($buffer) >= self::MULTIINSERT_MAXBUFFER) { if ($currentTable !== null && count($buffer)) { Curry_Propel::doMultiInsert($currentTable, $buffer); } $currentTable = $data['table']; $buffer = array(); } // Migrate data if ($pageVersion !== Page::VERSION) { if (!Page::migrateData($data['table'], $data['values'], $pageVersion)) { continue; } } $buffer[] = $data['values']; } else { if ($backend) { $backend->addMessage('Unable to read data on line ' . $total, Curry_Backend::MSG_ERROR); } ++$failed; } // check execution time if ($maxExecutionTime && Curry_Core::getExecutionTime() > $maxExecutionTime) { if ($currentTable !== null && count($buffer)) { Curry_Propel::doMultiInsert($currentTable, $buffer); } $session->total = $total; $session->skipped = $skipped; $session->failed = $failed; $params = array('module' => 'Curry_Backend_Database', 'view' => 'ContinueRestore', 'file' => $file, 'tables' => $tables, 'line' => $total, 'max_execution_time' => $maxExecutionTime); url('', $params)->redirect(302, true); } } // Flush buffer if ($currentTable !== null && count($buffer)) { Curry_Propel::doMultiInsert($currentTable, $buffer); } if ($pageVersion !== Page::VERSION) { Page::postMigrate($pageVersion); } if ($adapter instanceof DBMySQL) { $con->exec("SET foreign_key_checks = 1"); } $con->commit(); $CURRY_DATABASE_RESTORE = false; if ($backend) { if ($skipped) { $backend->addMessage("Skipped {$skipped} rows"); } if ($failed) { $backend->addMessage("Failed to add {$failed} rows", Curry_Backend::MSG_ERROR); } $backend->addMessage("Added " . ($total - $skipped - $failed) . " / {$total} rows in " . round(microtime(true) - $t, 2) . "s", !$failed ? Curry_Backend::MSG_SUCCESS : Curry_Backend::MSG_ERROR); } if (is_string($file)) { fclose($fp); } return !$failed; }
/** * Convenience method to get the DBAdapter registered with Propel for this database. * @return DBAdapter * @see Propel::getDB(string) */ public function getDBAdapter() { return Propel::getDB($this->name); }
/** * 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; }
public function testOrderByIgnoreCaseWithWhereClause() { $originalDB = Propel::getDB(); Propel::setDB(null, new DBMySQL()); $criteria = new Criteria(); $criteria->setIgnoreCase(true); $criteria->addAscendingOrderByColumn(BookPeer::TITLE); $criteria->add('book.title', false); BookPeer::addSelectColumns($criteria); $params = array(); $sql = BasePeer::createSelectSql($criteria, $params); $expectedSQL = 'SELECT book.id, book.title, book.isbn, book.price, book.publisher_id, book.author_id, UPPER(book.title) FROM `book` WHERE UPPER(book.title)=UPPER(:p1) ORDER BY UPPER(book.title) ASC'; $this->assertEquals($expectedSQL, $sql); Propel::setDB(null, $originalDB); }
/** * Make explain plan of the query * * @param PropelPDO $con propel connection * @throws PropelException on error * @return array array of the explain plan */ public function explain($con = null) { if ($con === null) { $con = Propel::getConnection($this->getDbName()); } $this->basePreSelect($con); // check that the columns of the main class are already added (if this is the primary ModelCriteria) if (!$this->hasSelectClause() && !$this->getPrimaryCriteria()) { $this->addSelfSelectColumns(); } $this->configureSelectColumns(); $db = Propel::getDB($this->getDbName()); try { $stmt = $db->doExplainPlan($con, $this); return $stmt->fetchAll(PDO::FETCH_ASSOC); } catch (Exception $e) { Propel::log($e->getMessage(), Propel::LOG_ERR); throw new PropelException('Unable to execute query explain plan', $e); } }
/** * Executes a COUNT query using either a simple SQL rewrite or, for more complex queries, a * sub-select of the SQL created by createSelectSql() and returns the statement. * * @param Criteria $criteria A Criteria. * @param PropelPDO $con A PropelPDO connection to use. * @return PDOStatement The resultset statement. * @throws PropelException * @see createSelectSql() */ public static function doCount(Criteria $criteria, PropelPDO $con = null) { $dbMap = Propel::getDatabaseMap($criteria->getDbName()); $db = Propel::getDB($criteria->getDbName()); if ($con === null) { $con = Propel::getConnection($criteria->getDbName(), Propel::CONNECTION_READ); } $stmt = null; if ($criteria->isUseTransaction()) { $con->beginTransaction(); } $needsComplexCount = $criteria->getGroupByColumns() || $criteria->getOffset() || $criteria->getLimit() || $criteria->getHaving() || in_array(Criteria::DISTINCT, $criteria->getSelectModifiers()); try { $params = array(); if ($needsComplexCount) { $selectSql = self::createSelectSql($criteria, $params); $sql = 'SELECT COUNT(*) FROM (' . $selectSql . ') AS propelmatch4cnt'; } else { // Replace SELECT columns with COUNT(*) $criteria->clearSelectColumns()->addSelectColumn('COUNT(*)'); $sql = self::createSelectSql($criteria, $params); } $stmt = $con->prepare($sql); self::populateStmtValues($stmt, $params, $dbMap, $db); $stmt->execute(); if ($criteria->isUseTransaction()) { $con->commit(); } } catch (Exception $e) { if ($stmt) { $stmt = null; } // close if ($criteria->isUseTransaction()) { $con->rollBack(); } Propel::log($e->getMessage(), Propel::LOG_ERR); throw new PropelException($e); } return $stmt; }