/**
* @param $clause
* @param array $parameters - first is $statement followed by $parameters
* @return $this|SelectQuery
*/
public function __call($clause, $parameters = array())
{
$this->getContext();
//context must be available
$clause = Helpers::toUpperWords($clause);
if ($clause == 'GROUP') {
$clause = 'GROUP BY';
} elseif ($clause == 'ORDER') {
$clause = 'ORDER BY';
} elseif ($clause == 'FOOT NOTE') {
$clause = "\n--";
} elseif ($clause == 'WHERE PRIMARY') {
$clause = 'WHERE';
$primaryKey = $this->context->getDatabaseReflection()->getPrimary($this->getTable());
array_unshift($parameters, "{$this->getTableAlias()}.{$primaryKey}");
}
$args = array_merge(array($clause), $parameters);
if (Strings::contains($clause, 'JOIN')) {
return call_user_func_array(array($this, 'addJoinStatements'), $args);
}
return call_user_func_array(array($this, 'processStatement'), $args);
}
protected function formatValue($value)
{
if (is_string($value)) {
if (strlen($value) > 20) {
$this->remaining[] = $value;
return '?';
} else {
return $this->connection->quote($value);
}
} elseif (is_int($value)) {
return (string) $value;
} elseif (is_float($value)) {
return rtrim(rtrim(number_format($value, 10, '.', ''), '0'), '.');
} elseif (is_bool($value)) {
return $this->driver->formatBool($value);
} elseif ($value === NULL) {
return 'NULL';
} elseif ($value instanceof ActiveRow) {
return $value->getPrimary();
} elseif (is_array($value) || $value instanceof Traversable) {
$vx = $kx = array();
if ($value instanceof Traversable) {
$value = iterator_to_array($value);
}
reset($value);
if (isset($value[0])) {
// non-associative; value, value, value
foreach ($value as $v) {
if (is_array($v) && isset($v[0]) && count($v) > 1) {
// no-associative; (value), (value), (value)
$vx[] = '(' . $this->formatValue($v) . ')';
} else {
$vx[] = $this->formatValue($v);
}
}
if ($this->arrayMode === 'union') {
return implode(' ', $vx);
}
return implode(', ', $vx);
} elseif (Helpers::containsNamedParams($value)) {
//named params e.g. ":id"
foreach ($value as $k => $v) {
if (is_array($v)) {
throw new InvalidArgumentException("Named param '{$k}' contains unsupported array as value.");
}
}
$this->remaining = $this->remaining + $value;
} elseif ($this->arrayMode === 'values') {
// (key, key, ...) VALUES (value, value, ...)
$this->arrayMode = 'multi';
foreach ($value as $k => $v) {
$kx[] = $this->driver->delimite($k);
$vx[] = $this->formatValue($v);
}
return '(' . implode(', ', $kx) . ') VALUES (' . implode(', ', $vx) . ')';
} elseif ($this->arrayMode === 'select') {
// (key, key, ...) SELECT value, value, ...
$this->arrayMode = 'union';
foreach ($value as $k => $v) {
$kx[] = $this->driver->delimite($k);
$vx[] = $this->formatValue($v);
}
return '(' . implode(', ', $kx) . ') SELECT ' . implode(', ', $vx);
} elseif ($this->arrayMode === 'assoc') {
// key=value, key=value, ...
foreach ($value as $k => $v) {
if (substr($k, -1) === '=') {
$k2 = $this->driver->delimite(substr($k, 0, -2));
$vx[] = $k2 . '=' . $k2 . ' ' . substr($k, -2, 1) . ' ' . $this->formatValue($v);
} else {
$vx[] = $this->driver->delimite($k) . '=' . $this->formatValue($v);
}
}
return implode(', ', $vx);
} elseif ($this->arrayMode === 'multi') {
// multiple insert (value, value, ...), ...
foreach ($value as $v) {
$vx[] = $this->formatValue($v);
}
return '(' . implode(', ', $vx) . ')';
} elseif ($this->arrayMode === 'union') {
// UNION ALL SELECT value, value, ...
foreach ($value as $v) {
$vx[] = $this->formatValue($v);
}
return 'UNION ALL SELECT ' . implode(', ', $vx);
} elseif ($this->arrayMode === 'and') {
// (key [operator] value) AND ...
foreach ($value as $k => $v) {
$k = $this->driver->delimite($k);
if (is_array($v)) {
$vx[] = $v ? $k . ' IN (' . $this->formatValue(array_values($v)) . ')' : '1=0';
} else {
$v = $this->formatValue($v);
$vx[] = $k . ($v === 'NULL' ? ' IS ' : ' = ') . $v;
}
}
return $value ? '(' . implode(') AND (', $vx) . ')' : '1=1';
} elseif ($this->arrayMode === 'order') {
// key, key DESC, ...
foreach ($value as $k => $v) {
$vx[] = $this->driver->delimite($k) . ($v > 0 ? '' : ' DESC');
}
return implode(', ', $vx);
}
} elseif ($value instanceof DateTime || $value instanceof DateTimeInterface) {
return $this->driver->formatDateTime($value);
} elseif ($value instanceof SqlLiteral) {
$this->remaining = array_merge($this->remaining, $value->getParameters());
return $value->__toString();
} else {
$this->remaining[] = $value;
return '?';
}
}
public function getPanel()
{
$this->disabled = TRUE;
$s = '';
$h = 'htmlSpecialChars';
$counts = array();
$transactionTime = null;
foreach ($this->queries as $i => $query) {
list($sql, $params, $time, $rows, $connection, $source) = $query;
$command = '';
if (preg_match('#^\\s*(\\w+)#', $sql, $m)) {
$command = strtoupper($m[1]);
@$counts[$command]++;
}
if ($sql == 'TRANSACTION BEGIN') {
$transactionTime = 0;
} elseif (preg_match('#TRANSACTION (COMMIT|ROLLBACK)#', $sql)) {
$time = $transactionTime;
$transactionTime = null;
} elseif ($transactionTime !== null) {
$transactionTime += $time;
}
foreach ((array) $params as $param) {
if (!is_numeric($param)) {
$param = str_replace("'", "\\'", $param);
}
$sql = preg_replace('#\\?#', "'{$param}'", $sql, 1);
}
$explain = NULL;
// EXPLAIN is called here to work SELECT FOUND_ROWS()
if ($this->explain && preg_match('#\\s*\\(?\\s*SELECT\\s#iA', $sql)) {
try {
$cmd = is_string($this->explain) ? $this->explain : 'EXPLAIN';
$explain = $connection->queryArgs("{$cmd} {$sql}", $params)->fetchAll();
} catch (PDOException $e) {
}
}
$s .= "<tr class='nette-DbConnectionPanel-type-{$command}'><td>" . sprintf('%0.3f', $time * 1000);
if ($explain) {
static $counter;
$counter++;
$s .= "<br /><a href='#' class='nette-toggle-collapsed' data-ref='#nette-DbConnectionPanel-row-{$counter}'>explain </a>";
}
$s .= '</td><td class="nette-DbConnectionPanel-sql">' . Helpers::dumpSql(self::$maxLength ? Nette\Utils\Strings::truncate($sql, self::$maxLength) : $sql);
if ($explain) {
$s .= "<table id='nette-DbConnectionPanel-row-{$counter}' class='nette-collapsed'><tr>";
foreach ($explain[0] as $col => $foo) {
$s .= "<th>{$h($col)}</th>";
}
$s .= "</tr>";
foreach ($explain as $row) {
$s .= "<tr>";
foreach ($row as $col) {
$s .= "<td>{$h($col)}</td>";
}
$s .= "</tr>";
}
$s .= "</table>";
}
if ($source) {
$s .= Nette\Diagnostics\Helpers::editorLink($source[0], $source[1])->class('nette-DbConnectionPanel-source');
}
$s .= '</td>';
$s .= '<td>' . $rows . '</td></tr>';
}
$c = '';
foreach ($counts as $command => $count) {
$c .= "<tr><th>{$command}</th><td>{$count}</td><td><input type='checkbox' class='nette-DbConnectionPanel-type-switcher' name='{$command}' id='nette-DbConnectionPanel-type-{$command}' checked='checked' /></td></tr>";
}
return empty($this->queries) ? '' : '<style> #nette-debug td.nette-DbConnectionPanel-sql { background: white !important }
#nette-debug .nette-DbConnectionPanel-source { color: #BBB !important } </style>
<script type="text/javascript">
(function() {
var FW = typeof Tracy !== "undefined" ? Tracy : Nette;
var $ = typeof FW.Q !== "undefined" ? FW.Q.factory : FW.Query.factory;
$(".nette-DbConnectionPanel-type-switcher").bind("click", function(e) {
var name = this.name;
if (this.checked) {
$(".nette-DbConnectionPanel-type-" + name).show();
} else {
$(".nette-DbConnectionPanel-type-" + name).hide();
}
});
})();
</script>
<h1>Queries: ' . count($this->queries) . ($this->totalTime ? ', time: ' . sprintf('%0.3f', $this->totalTime * 1000) . ' ms' : '') . '</h1>
<div class="nette-inner nette-DbConnectionPanel">
<table>
<tr><th>Type</th><th>Count</th><th>Filter</th></tr>' . $c . '
</table>
<br />
<table>
<tr><th>Time ms</th><th>SQL Statement</th><th>Rows</th></tr>' . $s . '
</table>
</div>';
}
/**
* Returns associative array of detected types (IReflection::FIELD_*) in result set.
*/
public function getColumnTypes(PDOStatement $statement)
{
$types = array();
$count = $statement->columnCount();
for ($col = 0; $col < $count; $col++) {
$meta = $statement->getColumnMeta($col);
if (PHP_VERSION_ID < 50417) {
// PHP bug #48724
switch ($meta['name']) {
case 'tinyint':
$meta['native_type'] = 'TINY';
break;
case 'bit':
$meta['native_type'] = 'BIT';
break;
case 'year':
$meta['native_type'] = 'YEAR';
break;
default:
break;
}
}
if (isset($meta['native_type'])) {
$types[$meta['name']] = $type = Helpers::detectType($meta['native_type']);
if ($type === IReflection::FIELD_TIME) {
$types[$meta['name']] = IReflection::FIELD_TIME_INTERVAL;
}
}
}
return $types;
}
/**
* Get query string with expanded params
* @param boolean return
* @return string
*/
public function getQueryExpanded()
{
$query = $this->getQuery();
//must be called before getParameters
$containsNamedParams = false;
if ($params = $this->getParameters()) {
if ($containsNamedParams = Helpers::containsNamedParams($params)) {
$params = array($params);
}
list($query, $params) = $this->context->getPreprocessor()->process(array_merge(array($query), $params));
} else {
list($query, $params) = $this->context->getPreprocessor()->process(array($query));
}
if ($containsNamedParams) {
$that = $this;
$query = preg_replace_callback('#(?<=\\s):[a-z]+#i', function ($m) use($params, $that) {
return $that->quote($params[$m[0]]);
}, $query);
}
return $query;
}