PHP Propel::getDB方法代码示例

 protected function setUp()
 {
     Propel::init(dirname(__FILE__) . '/../../../../Fixtures/bookstore/build/conf/bookstore-conf.php');
     parent::setUp();
     $this->savedAdapter = Propel::getDB(null);
     Propel::setDB(null, new DBSQLite());
 }

 protected function setUp()
 {
     Propel::init(__DIR__ . '/../../../../Fixtures/bookstore/build/conf/bookstore-conf.php');
     parent::setUp();
     $this->c = new Criteria();
     $this->savedAdapter = Propel::getDB(null);
     Propel::setDB(null, new DBSQLite());
 }

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

 /**
  * 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 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);
 }

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

 /**
  * 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 = $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->setDB($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->setDB($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, ' ');
//.........这里部分代码省略.........

 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
     // ---------------------
//.........这里部分代码省略.........

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

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

 public function testOrderByIgnoreCase()
 {
     $originalDB = Propel::getDB();
     Propel::setDB(null, new DBMySQL());
     $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);
 }