/**
* @param \Freetrix\Main\Data\Connection $connection
* @param Query $query
*
* @return bool
*/
public static function checkQuery(\Freetrix\Main\Data\Connection $connection, Query $query)
{
// check interface
if (!$connection instanceof INosqlPrimarySelector) {
return false;
}
// no expressions in select
foreach ($query->getSelectChains() as $selectChain) {
if ($selectChain->getLastElement()->getValue() instanceof ExpressionField) {
return false;
}
}
// skip empty select, just for not handle this useless case in nosql api
if (!count($query->getSelect())) {
return false;
}
// if empty joinmap, group, order and simple filter
if (!count($query->getJoinMap()) && !count($query->getGroupChains()) && !count($query->getOrderChains()) && !count($query->getHavingChains())) {
$entityPrimary = $query->getEntity()->getPrimary();
// check for primary singularity
if (!is_array($entityPrimary)) {
// check if only primary is in filter
if (count($query->getFilterChains()) == 1 && key($query->getFilterChains()) === $entityPrimary) {
$passFilter = true;
// check if only equality operations & 1-level filter
foreach ($query->getFilter() as $filterElement => $filterValue) {
if (is_numeric($filterElement) && is_array($filterValue)) {
// filter has subfilters. not ok
$passFilter = false;
break;
}
if ($filterElement === 'LOGIC') {
continue;
}
$operation = substr($filterElement, 0, 1);
if ($operation !== '=') {
// only equal operation allowed. not ok
$passFilter = false;
break;
}
}
// fine!
if ($passFilter) {
return true;
}
}
}
}
return false;
}
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 = $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;
}
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 static function getInstanceByQuery(Query $query, &$entity_name = null)
{
if (empty($entity_name)) {
$entity_name = 'Tmp' . randString();
}
$query_string = '(' . $query->getQuery() . ')';
$query_chains = $query->getChains();
$replaced_aliases = array_flip($query->getReplacedAliases());
// generate fieldsMap
$fieldsMap = array('TMP_ID' => array('data_type' => 'integer', 'primary' => true));
foreach ($query->getSelect() as $k => $v) {
if (is_array($v)) {
$fieldsMap[$k] = array('data_type' => $v['data_type']);
} else {
$fieldsMap[$k] = array('data_type' => $query_chains[$k]->getLastElement()->getValue()->getDataType());
}
if (isset($replaced_aliases[$k])) {
$fieldsMap[$k]['column_name'] = $replaced_aliases[$k];
}
}
// generate class content
$eval = 'class ' . $entity_name . 'Entity extends ' . __CLASS__ . ' {' . PHP_EOL;
$eval .= 'protected function __construct(){}' . PHP_EOL;
$eval .= 'public function initialize() { $this->className = __CLASS__; $this->filePath = __FILE__;' . PHP_EOL;
$eval .= '$this->dbTableName = ' . var_export($query_string, true) . ';' . PHP_EOL;
$eval .= '$this->fieldsMap = ' . var_export($fieldsMap, true) . ';' . PHP_EOL;
$eval .= '}}';
eval($eval);
return self::getInstance($entity_name);
}
public static function getInstanceByQuery(Query $query, &$entity_name = null)
{
if (empty($entity_name)) {
$entity_name = 'Tmp' . randString();
}
$query_string = '(' . $query->getQuery() . ')';
$query_chains = $query->getChains();
$replaced_aliases = array_flip($query->getReplacedAliases());
// generate fieldsMap
$fieldsMap = array('TMP_ID' => array('data_type' => 'integer', 'primary' => true));
foreach ($query->getSelect() as $k => $v) {
if (is_array($v)) {
$fieldsMap[$k] = array('data_type' => $v['data_type']);
} else {
$fieldsMap[$k] = array('data_type' => $query_chains[$k]->getLastElement()->getValue()->getDataType());
}
if (isset($replaced_aliases[$k])) {
$fieldsMap[$k]['column_name'] = $replaced_aliases[$k];
}
}
// generate class content
$eval = 'class ' . $entity_name . 'Table extends ' . __NAMESPACE__ . '\\DataManager {' . PHP_EOL;
$eval .= 'public static function getMap() {' . PHP_EOL;
$eval .= 'return ' . var_export($fieldsMap, true) . ';' . PHP_EOL;
$eval .= '}';
$eval .= 'public static function getTableName() {' . PHP_EOL;
$eval .= 'return ' . var_export($query_string, true) . ';' . PHP_EOL;
$eval .= '}';
$eval .= 'public static function getFilePath() {' . PHP_EOL;
$eval .= 'return null;' . PHP_EOL;
$eval .= '}';
$eval .= '}';
eval($eval);
return self::getInstance($entity_name);
}
