public function getSqlFromCommand(SqlCommand $commandObj, $flags = self::MODE_DEFAULT) { $sql = array(); $command = $commandObj->getCommand(); $arguments = $commandObj->getArguments(); $comments = $commandObj->getComments(); foreach ($comments as $comment) { $sql[] = $comment; } if ($flags == self::MODE_QUICK) { $a = array(); foreach ($arguments as $argument) { $a[$argument[0]][] = $argument[1]; } ksort($a); foreach ($a as $priority => $list) { // var_dump("-------",$arguments,$priority,$list); $sql[] = "{$command}\n " . implode(",\n ", $list) . ";\n"; } } else { foreach ($arguments as $argument) { $sql[] = "{$command} {$argument[1]};\n"; } } return $sql; }
private function Convert(SqlCommand $command) { for ($p = 0; $p < $command->Parameters->Count(); $p++) { $curParam = $command->Parameters->Items($p); $value = $curParam->Value; if (is_array($value)) { $value = implode("','", $value); $value = "'{$value}'"; } $this->_values[str_replace('@', '', $curParam->Name)] = $value; } $this->_query = str_replace('@', ':', $command->GetQuery()); }
function testDatabaseQueryLoadsValuesInCorrectOrder() { $name1 = 'paramName1'; $val1 = 'value1'; $name2 = 'paramName2'; $val2 = 'value2'; $cn = new FakeDBConnection(); $db = new Database($cn); $p1 = new Parameter($name1, $val1); $p2 = new Parameter($name2, $val2); $parameters = new Parameters(); $parameters->Add($p1); $parameters->Add($p2); $command = new SqlCommand("SELECT * FROM sometable WHERE col1 = @{$name1} AND col2 = @{$name2}"); $command->SetParameters($parameters); $adapter = new Mdb2CommandAdapter($command); $vals = $adapter->GetValues(); $this->assertEquals(2, count($vals)); $this->assertEquals($val1, $vals[$name1]); $this->assertEquals($val2, $vals[$name2]); $this->assertEquals("SELECT * FROM sometable WHERE col1 = :{$name1} AND col2 = :{$name2}", $adapter->GetQuery()); }
private function Convert(SqlCommand &$command) { $query = $command->GetQuery(); for ($p = 0; $p < $command->Parameters->Count(); $p++) { $curParam = $command->Parameters->Items($p); if (is_null($curParam->Value)) { $query = str_replace($curParam->Name, 'null', $query); } if (is_array($curParam->Value)) { $escapedValues = array(); foreach ($curParam->Value as $value) { $escapedValues[] = mysqli_real_escape_string($this->_db, $value); } $values = implode("','", $escapedValues); $inClause = "'{$values}'"; $query = str_replace($curParam->Name, $inClause, $query); } else { $escapedValue = mysqli_real_escape_string($this->_db, $curParam->Value); $query = str_replace($curParam->Name, "'{$escapedValue}'", $query); } } $this->_query = $query . ';'; }
public function __construct($userId, $token, Date $insertTime, $serializedSession) { parent::__construct(Queries::UPDATE_USER_SESSION); $this->AddParameter(new Parameter(ParameterNames::USER_ID, $userId)); $this->AddParameter(new Parameter(ParameterNames::SESSION_TOKEN, $token)); $this->AddParameter(new Parameter(ParameterNames::DATE_MODIFIED, $insertTime->ToDatabase())); $this->AddParameter(new Parameter(ParameterNames::USER_SESSION, $serializedSession)); }
function testSqlCommandIsUsedInQuery() { $SqlCommand = new SqlCommand('query'); $param = new Parameter('n1', 'v1'); $SqlCommand->AddParameter($param); $cn = new FakeDBConnection(); $db = new Database($cn); $db->Query($SqlCommand); $this->assertEquals($SqlCommand, $cn->_LastSqlCommand); $this->assertEquals($param, $cn->_LastSqlCommand->Parameters->Items(0)); }
public function GetQuery() { $baseQuery = $this->baseCommand->GetQuery(); $baseQueryUpper = strtoupper($baseQuery); $numberOfWheres = substr_count($baseQueryUpper, 'WHERE'); $numberOfSelects = substr_count($baseQueryUpper, 'SELECT'); $hasWhere = $numberOfWheres !== false && $numberOfWheres > 0 && $numberOfWheres == $numberOfSelects; $hasOrderBy = stripos($baseQuery, 'ORDER BY') !== false; $hasGroupBy = stripos($baseQuery, 'GROUP BY') !== false; $newWhere = $this->filter->Where(); if ($hasWhere) { // get between where and order by, replace with match plus new stuff $pos = strripos($baseQuery, 'WHERE'); $baseQuery = substr_replace($baseQuery, 'WHERE (', $pos, strlen('WHERE')); $groupBySplit = preg_split("/GROUP BY/ims", $baseQuery); $orderBySplit = preg_split("/ORDER BY/ims", $baseQuery); if (count($groupBySplit) > 1) { $queryFragment = trim($groupBySplit[0]); $groupBy = trim($groupBySplit[1]); $query = "{$queryFragment} ) AND ({$newWhere}) GROUP BY {$groupBy}"; } elseif (count($orderBySplit) > 1) { $queryFragment = trim($orderBySplit[0]); $orderBy = trim($orderBySplit[1]); $query = "{$queryFragment} ) AND ({$newWhere}) ORDER BY {$orderBy}"; } else { $query = "{$baseQuery}) AND ({$newWhere})"; } } else { if ($hasGroupBy) { $query = str_ireplace('group by', " WHERE {$newWhere} GROUP BY", $baseQuery); } elseif ($hasOrderBy) { $query = str_ireplace('order by', " WHERE {$newWhere} ORDER BY", $baseQuery); } else { // no where, no order by, just append new where clause $query = "{$baseQuery} WHERE {$newWhere}"; } } return $query; }
public function __construct(ISqlCommand $baseCommand, $limit, $offset) { parent::__construct(); $this->baseCommand = $baseCommand; $this->limit = $limit; $this->offset = $offset; $this->Parameters = $baseCommand->Parameters; }