/** * @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); }