public function evaluateQuery(Query $query)
{
$select = $this->evaluateSelect($query->getSelect()) . " ";
$from = $this->evaluateFrom($query->getFrom()) . " ";
$where = $this->evaluateWhere($query->getWhere()) . " ";
$order = $this->evaluateOrder($query->getOrder()) . " ";
$groupBy = $this->evaluateGroupBy($query->getGroupBy()) . " ";
$limit = '';
if ($query->hasLimit()) {
$limit = 'LIMIT ' . $query->getLimitMax() . ' ' . $query->getLimitOffset() . ' ';
// http://www.postgresql.org/docs/8.1/static/queries-limit.html
}
return $select . $from . $where . $order . $limit . $groupBy;
}
public function evaluateQuery(Query $query)
{
$select = $this->evaluateSelect($query->getSelect()) . ' ';
$from = '';
$order = $this->evaluateOrder($query->getOrder()) . ' ';
$groupBy = $this->evaluateGroupBy($query->getGroupBy()) . ' ';
// SQLServer 2008 no tiene LIMIT, hay que hacerlo con una subquery usando ROWNUM
// Soluciona problema de paginacion en SQLServer, como esta en DAL::listAll()
//
if ($query->hasLimit()) {
// FIXME:
// El filtro del where que no es el filtro por rowNum
// debe hacerse en la consulta interna, sino se toman
// rowNums en la consulta interna que luego no estan
// en la consulta final, eso afecta al resultado paginado,
// porque se pagina por rowNum de rows que no matchean el where, ej:
// - consulta interna devuelve 1,2,3,4,5,6,7,8
// - consulta externa tiene max=3, offset=0
// - where matchea solo 2,4,5,7
// - el resultado es solo 2, en lugar de 2,4,5 (son los primeros 3 que matchean)
//
// problema, el where incluye condiciones que no son sobre la tabla utilizada
// en la consulta interna, esto afecta?
$tableName = $query->getLimitTable();
$offset = $query->getLimitOffset();
$max = $query->getLimitMax();
// La consulta interna es para hacer paginacion
// WHERE: Las condiciones donde dice tableName pone T2 (no puede evaluar condiciones sobre atributos de tablas no mencionados en el FROM)
// - http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3b2e0875-e98c-4931-bcb4-e9f449b637d7/the-multipart-identifier-aliasfield-could-not-be-bound
// Hago el evaluate del from aca porque tengo que cambiar el FROM de tableName por la subconsulta necesaria para el limit
$queryFrom = $query->getFrom();
$alias;
if (count($queryFrom) == 0) {
// ERROR! es obligatorio por lo menos una!
throw new Exception("FROM no puede ser vacio");
} else {
/*
$res = "FROM ";
foreach ($queryFrom as $table)
{
if ( $table->name == $tableName )
{
$alias = $table->alias;
$res .= '( SELECT ROW_NUMBER() OVER (ORDER BY id) AS rowNum, * '.
'FROM '. $tableName .
$this->evaluateWhere( $query->getWhere() ) .
' ) '. $alias .', ';
}
else
{
$res .= $table->name .' '. $table->alias .', ';
}
}
$from = substr($res, 0, -2) .' ';
*/
$alias = 'subq';
// * selecciona multiples columnas con mismo nombre en distintas tablas
$subquery = '( ' . $select . ', ROW_NUMBER() OVER (ORDER BY ' . $tableName . '.id) AS rowNum ' . $this->evaluateFrom($query->getFrom()) . ' ' . $this->evaluateWhere($query->getWhere()) . ' ' . $order . $groupBy . ' ) ' . $alias . ' ';
$from = 'FROM ' . $subquery;
$select = 'SELECT * ';
// Select para la query ppal. agregaciones y group se harian en la subquery
}
$where = 'WHERE ' . $alias . '.rowNum-1 >= ' . $offset . ' AND ' . $alias . '.rowNum-1 < ' . ($offset + $max);
return $select . $from . $where;
}
// hasLimit
// !hasLimit
$from = $this->evaluateFrom($query->getFrom()) . ' ';
$where = $this->evaluateWhere($query->getWhere()) . ' ';
return $select . $from . $where . $order . $groupBy;
}
public function evaluateQuery(Query $query)
{
$select = $this->evaluateSelect($query->getSelect()) . ' ';
$from = $this->evaluateFrom($query->getFrom()) . ' ';
$where = $this->evaluateWhere($query->getWhere()) . ' ';
$order = $this->evaluateOrder($query->getOrder()) . ' ';
$groupBy = $this->evaluateGroupBy($query->getGroupBy()) . ' ';
$limit = '';
if ($query->hasLimit()) {
$limit = 'LIMIT ' . $query->getLimitOffset() . ', ' . $query->getLimitMax() . ' ';
// http://www.sqlite.org/lang_select.html
}
return $select . $from . $where . $order . $limit . $groupBy;
}
/**
* @param Query $query
* @return mixed
*/
public function query(Query $query)
{
$time = microtime(TRUE);
$packageName = $query->getPackage();
$counterName = $query->getCounter();
$from = $query->getFrom();
$to = $query->getTo();
$count = $query->getCount();
$action = $query->getAction();
$token = $query->getToken();
$value = $query->getValue();
$tokenCreated = FALSE;
$result = NULL;
$databaseFilename = $this->getDatabaseFileName($packageName);
if (!file_exists($databaseFilename) && $action !== Query::ACTION_SAVE) {
throw new NotFoundException(sprintf('Package %s has no counters. Save a value to initialize!', $packageName));
}
switch ($action) {
case Query::ACTION_COMPARE:
$history = (array) $this->getByCount($packageName, $counterName, 2048);
$lastValue = $this->getLastValue($packageName, $counterName);
$result = array(array('value' => $lastValue, 'time' => $this->getLastTimestamp($packageName, $counterName), 'deviation' => $this->getCalculator()->deviation($history, $lastValue)), array('value' => $value, 'time' => time(), 'deviation' => $this->getCalculator()->deviation($history, $value)));
break;
case Query::ACTION_POLL:
case Query::ACTION_GET:
$this->validatePackageToken($packageName, $token);
if ($from && $count) {
$result = $this->getByRange($packageName, $counterName, $from, $to, $count);
} elseif ($from) {
$result = $this->getByRange($packageName, $counterName, $from, $to);
} elseif ($count) {
$result = $this->getByCount($packageName, $counterName, $count);
} else {
$result = $this->getLastValue($packageName, $counterName);
}
break;
case Query::ACTION_COUNTERS:
$this->validatePackageToken($packageName, $token);
$result = $this->getCountersFromPackage($packageName);
break;
case Query::ACTION_SAVE:
if (!file_exists($databaseFilename)) {
$token = $this->createTokenForPackage($packageName, $token);
$tokenCreated = TRUE;
} else {
$this->validatePackageToken($packageName, $token);
}
$result = $this->saveValue($packageName, $counterName, $value);
break;
default:
throw new NumerologException(sprintf('Invalid Numerolog action: %s', $action));
}
$response = array('values' => $result);
if (1 < count($result) && $action !== Query::ACTION_SAVE && $action !== Query::ACTION_COUNTERS) {
$response['statistics'] = $this->getCalculator()->statistics($result);
}
if ($action === Query::ACTION_POLL) {
// return only the indicated sub-set of statistics
$poll = $query->getPoll();
if (empty($poll)) {
throw new NumerologException('Poll command used with empty poll argument');
} elseif (isset($response[$poll])) {
$response = $response[$poll];
} elseif (isset($response['statistics'][$poll])) {
$response = $response['statistics'][$poll];
} else {
throw new NumerologException(sprintf('Invalid polling data requested: %s', $poll));
}
} else {
$response['querytime'] = round((microtime(TRUE) - $time) * 1000, 5);
}
if ($action === Query::ACTION_SAVE && $tokenCreated) {
$response['token'] = $token;
}
return $response;
}
