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