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