/**
* Renders the One_Query into an xpath that can fetch results from the XML-datastore
*
* @param One_Query $query
*/
public function render(One_Query $query)
{
$this->query = $query;
$this->scheme = $this->query;
$resources = $this->scheme->getResources();
if (trim($query->getRaw()) != '') {
return $query->getRaw();
}
$xpath = $resources['item'];
// add possible filters to the query
if (isset($resources['filter'])) {
$filters = explode(';', $resources['filter']);
if (count($filters) > 0) {
foreach ($filters as $filterName) {
if ($filterName != '') {
$filter = One_Repository::getFilter($filterName, $query->name());
$filter->affect($query);
}
}
}
}
$whereClauses = $query->getWhereClauses();
if (!is_null($whereClauses)) {
$clauses = $this->whereClauses($whereClauses);
if (!is_null($clauses)) {
$xpath .= '[' . $clauses . ']';
}
}
return $xpath;
}
public function affect(One_Query $query)
{
$search = $this->_filterParams['search'];
if ($search) {
$query->where('name', 'like', "%{$search}%");
}
}
/**
* Add a condition to the container
*
* @param string $object
* @param string $op
* @param mixed $val
*/
public function where($object, $op = 'eq', $val = '')
{
if ($this->query->isRelationDefinition($object)) {
$parts = explode(':', $object);
$this->query->setJoin($parts[0]);
}
$condition = new One_Query_Condition($object, $op, $val);
$this->conditions[] = $condition;
return $this->conditions[count($this->conditions) - 1];
}
/**
* Apply filters to the queryscheme
*
* @param One_Query $q
*/
public function affect(One_Query $query)
{
$cx = new One_Context();
$term = $cx->get('term', '');
$field = $cx->get('field', $query->getScheme()->getIdentityAttribute()->getName());
if ('' != trim($term)) {
$query->where($field, 'contains', $term);
} else {
$query->where('isFalse', 'literal', 'false');
}
}
/**
* Affect the One_Query appropriately
*
* @param One_Query $q
* @param string $attrName
* @param mixed $attrVal
* @final
*/
public final function affectQuery(One_Query $q, $attrName, $attrVal)
{
$allowed = self::getAllowed();
$opVal = $this->getValue();
if (!is_null($opVal)) {
if (in_array($opVal, $allowed)) {
$op = $opVal;
} else {
$op = $allowed['default'];
}
}
$q->where($attrName, $op, $attrVal);
}
/**
* Apply filters to the queryscheme
*
* @param One_Query $q
*/
public function affect(One_Query $query)
{
$filters = $this->filters();
$searchform = $filters['searchform'];
$cx = new One_Context();
$scheme = $query->getScheme();
$idAttr = $scheme->getIdentityAttribute()->getName();
$form = One_Form_Factory::createForm($scheme, $searchform);
$widgets = $this->getWidgets($form, $scheme);
foreach ($widgets as $widget) {
$widget->affectQuery($query);
}
// for searchresults, results can always be grouped by id, since we only want to show a same result once
$query->setGroup($idAttr);
}
/**
* Returns the scheme of the One_Query instance
*
* @return One_Scheme
*/
public function getInstanceScheme()
{
$selects = $this->query->getSelect();
$instanceScheme = $this->query->getScheme();
$usedSchemes = array();
foreach ($selects as $select) {
if (preg_match('/([^\\(]+\\()?([^:]+):([^:]+)/', $select, $matches) > 0) {
$usedSchemes[] = trim($matches[2]);
}
}
if (count($usedSchemes) == 1) {
$this->defineRole($usedSchemes[0]);
$instanceScheme = $this->joins[$usedSchemes[0]]['scheme'];
}
return $instanceScheme;
}
/**
* Parses the order from the One_Query instance to understandable data for Onterspire
*
* @return array
*/
private function createOrder()
{
$qOrders = $this->query->getOrder();
foreach ($qOrders as $qOrder) {
preg_match('/^([a-zA-Z0-9_]+)*(\\+|\\-)?$/', $qOrder, $matches);
$dir = $matches[2] == '-' ? ' DESC' : 'ASC';
$order = array('SortBy' => $matches[1], 'Direction' => $dir);
}
return $order;
}
/**
* On selection from a certain scheme and when the feed task is being performed,
* select specified attributes needed for creating a feed
*
* @param One_Query $query
*/
public function onSelect(One_Query $query)
{
if ($_REQUEST['task'] == 'feed') {
$scheme = $query->getScheme();
$options = $scheme->get('behaviorOptions.' . strtolower($this->getName()));
$title = $options['title'];
$description = $options['description'];
$pubDate = $options['pubDate'];
$id = $scheme->getIdentityAttribute()->getName();
$select = array();
$select[] = '`' . $id . '` AS `id`';
if (!is_null($title)) {
$select[] = '`' . $title . '` AS `title`';
}
if (!is_null($description)) {
$select[] = '`' . $description . '` AS `description`';
}
if (!is_null($pubDate)) {
$select[] = '`' . $pubDate . '` AS `pubDate`';
}
$query->select($select);
}
}
/**
* Apply filters to the queryscheme
*
* @param One_Query $q
*/
public function affect(One_Query $query)
{
$filters = array();
$unparsed_filters = $this->filters();
foreach ($unparsed_filters as $key => $value) {
$tmp = explode("__", $key);
switch ($tmp[1]) {
case "op":
case "value":
$filters[$tmp[0]][$tmp[1]] = $value;
break;
default:
throw new One_Exception('Expecting params in the form attribute__op or attribute__value');
break;
}
}
foreach ($filters as $attribute => $options) {
if (trim($options['op']) != '' && trim($options['value']) != '') {
$query->where($attribute, $options['op'], $options['value']);
} else {
throw new One_Exception('Expecting both an operator and a value for attribute ' . $attribute);
}
}
}
/**
* Affect the One_Query appropriately
*
* @param One_Query $q
*/
public function affectQuery(One_Query $q)
{
$scheme = $q->getScheme();
$name = $this->getName();
$value = $this->getValue();
$op = $this->getOp($this->getCfg('optype'));
if (trim($value) != '') {
if (!is_null($this->getCfg('role'))) {
$parts = explode(':', $this->getCfg('role'));
$role = $parts[1];
$link = $scheme->getLink($role);
if (!is_null($link)) {
$target = $link->getTarget();
$targetScheme = One_Repository::getScheme($target);
$targetAttr = $this->getCfg('targetAttribute');
if (!is_null($targetScheme->getAttribute($targetAttr))) {
if (!is_null($op)) {
$op->affectQuery($q, $role . ':' . $targetAttr, $value);
} else {
$q->where($role . ':' . $targetAttr, 'contains', $value);
// default action
}
}
}
} else {
if (!is_null($scheme->getAttribute($name))) {
if (!is_null($op)) {
$op->affectQuery($q, $name, $value);
} else {
$q->where($name, 'contains', $value);
// default action
}
}
}
}
}
/**
* Render the query
*
* @param One_Query $query
* @return string
*/
public function render(One_Query $query, $overrideFilters = false)
{
$this->query = $query;
$this->scheme = $this->query->getScheme();
// if the person wants to perform a raw query, return the raw query
if (!is_null($query->getRaw())) {
if (One_Config::get('debug.query')) {
echo '<pre>';
var_dump($query->getRaw());
echo '</pre>';
}
return $query->getRaw();
}
$this->query = $query;
$this->scheme = $this->query->getScheme();
$resources = $this->scheme->getResources();
// fetch collection to fetch data from
$this->_collection = $resources['collection'];
// add possible filters to the query
if (!$overrideFilters && isset($resources['filter'])) {
$filters = explode(';', $resources['filter']);
if (count($filters) > 0) {
foreach ($filters as $filterName) {
if ($filterName != '') {
$filter = One_Repository::getFilter($filterName, $query->getScheme()->getName());
$filter->affect($query);
}
}
}
}
$findQuery = array('fields' => array(), 'query' => array());
if (count($query->getSelect()) > 0) {
$findQuery['fields'] = $this->createSelects($query->getSelect());
}
// get where clauses
$whereClauses = $query->getWhereClauses();
$where = NULL;
if (!is_null($whereClauses)) {
$where = $this->whereClauses($whereClauses);
}
if (!is_null($where)) {
$findQuery['query'] = $where;
}
if (One_Config::get('debug.query')) {
echo '<pre>';
var_dump($findQuery);
echo '</pre>';
}
$findQuery = json_encode($findQuery);
return $findQuery;
}
/**
* Processes any possibly given filters and alters the One_Query object accordingly.
*
* The filter option provided by eg. the com_one menu item is a list of filters, comma-separated, to apply.
* The extraParameters are passed to the filters
*
* @param One_Query $query
* @param array $filters
*/
protected function processQueryConditions(One_Query $query)
{
$filterString = $this->getVariable('filters', '');
if ($filterString) {
$filters = explode(',', $filterString);
$filterParameters = parse_ini_string($this->getVariable('filterParameters', ','));
foreach ($filters as $filterName) {
$filter = One_Repository::getFilter($filterName, $query->getScheme()->getName(), $filterParameters);
$filter->affect($query);
}
}
}
/**
* Return the number of results when the query is performed
*
* @param One_Query $query
* @param boolean $overrideFilters
* @return int
*/
public function executeSchemeCount(One_Query $query, $overrideFilters = false)
{
$scheme = $query->getScheme();
$db = $this->db($scheme);
$renderer = $this->getRenderer();
// Need to remember the old Select in case the One_Query is reused afterwards
$oldSelect = $query->getSelect();
$query->setSelect(array('COUNT(*) AS total'));
$sql = $renderer->render($query, $overrideFilters);
// put the old Select back into the One_Query
$query->setSelect($oldSelect);
// execute query
$result = mysql_query($sql, $db);
if ($result == false) {
throw new One_Exception(mysql_error() . '<br />' . $sql);
}
$count = mysql_result($result, 0, 'total');
return $count;
}
/**
* Return the number of results when the query is performed
*
* @param One_Query $query
* @param boolean $overrideFilters
* @return int
*/
public function executeSchemeCount(One_Query $query, $overrideFilters = false)
{
$scheme = $query->getScheme();
$db = $this->db($scheme);
$renderer = $this->getRenderer();
$searchQuery = $renderer->render($query, $overrideFilters);
$connectionMeta = $scheme->getResources();
$collection = $db->selectCollection($connectionMeta['collection']);
try {
$count = $collection->count($searchQuery['query']);
} catch (Exception $e) {
throw new One_Exception('Query failed: ' . $e->getMessage());
}
return $count;
}
/**
* Render the query
*
* @param One_Query $query
* @return string
*/
public function render(One_Query $query, $overrideFilters = false)
{
$this->query = $query;
$this->scheme = $this->query->getScheme();
// if the person wants to perform a raw query, return the raw query
if (!is_null($query->getRaw())) {
if (One_Config::get('debug.query')) {
echo '<pre>';
var_dump($query->getRaw());
echo '</pre>';
}
return $query->getRaw();
}
$this->query = $query;
$this->scheme = $this->query->getScheme();
$resources = $this->scheme->getResources();
// fetch main table to fetch data from
$this->mainTable = $resources['table'];
$this->defineRole('$self$');
// add possible filters to the query
if (!$overrideFilters && isset($resources['filter'])) {
$filters = explode(';', $resources['filter']);
if (count($filters) > 0) {
foreach ($filters as $filterName) {
if ($filterName != '') {
$filter = One_Repository::getFilter($filterName, $query->getScheme()->getName());
$filter->affect($query);
}
}
}
}
// TR20100531 No longer needs to be run after the rest since joins are now checked while adding
// sselects, order, ...
$joins = NULL;
$qJoins = $query->getJoins();
if (count($qJoins) > 0) {
foreach ($qJoins as $join => $type) {
$this->defineRole($join);
$query->setRoleAlias($join, $this->aliases[$join]);
$joins .= $this->createJoin($query->getRole($join), $type);
}
}
// *** TODO: change the '*' to only the relavant fields defined in the scheme
$selects = $this->aliases['$self$'] . '.*';
if (count($query->getSelect()) > 0) {
$selects = $this->createSelects($query->getSelect());
}
// get where clauses
$whereClauses = $query->getWhereClauses();
$where = NULL;
if (!is_null($whereClauses)) {
$where = $this->whereClauses($whereClauses);
}
// get having clauses
$havingClauses = $query->getHavingClauses();
$having = NULL;
if (!is_null($havingClauses)) {
$having = $this->whereClauses($havingClauses);
}
// get order
$order = $this->createOrder();
//get grouping
$group = $this->createGroup();
// get limit
$limit = $this->createLimit();
$sql = 'SELECT ' . $selects . ' FROM `' . $this->mainTable . '` ' . $this->aliases['$self$'];
if (!is_null($joins)) {
$sql .= $joins;
}
if (!is_null($where)) {
$sql .= ' WHERE ' . $where;
}
if (!is_null($group)) {
$sql .= ' GROUP BY ' . $group;
}
if (!is_null($having)) {
$sql .= ' HAVING ' . $having;
}
if (!is_null($order)) {
$sql .= ' ORDER BY ' . $order;
}
if (!is_null($limit)) {
$sql .= ' LIMIT ' . $limit;
}
if (One_Config::get('debug.query')) {
echo '<pre>';
var_dump($sql);
echo '</pre>';
}
return $sql;
}
public function affect(One_Query $query)
{
$query->setOrder('type,name');
}
public function affect(One_Query $query)
{
$query->where('enabled', 'eq', 1);
}
/**
* Return the number of results when the query is performed
*
* @param One_Query $query
* @param boolean $overrideFilters
* @return int
*/
public function executeSchemeCount(One_Query $query, $overrideFilters = false)
{
$scheme = $query->getScheme();
$db = $this->db($scheme);
$renderer = $this->getRenderer();
// Need to remember the old Select in case the One_Query is reused afterwards
$oldSelect = $query->getSelect();
$query->setSelect(array('COUNT( * ) AS total'));
$sql = $renderer->render($query, $overrideFilters);
// put the old Select back into the One_Query
$query->setSelect($oldSelect);
$db->setQuery($sql);
return intval($db->loadResult());
}
/**
* Class constructor
* Note: used to accept strings, pass One_Repository::getScheme($string) instead
*
* @param One_Scheme $scheme
*/
public function __construct(One_Scheme $scheme)
{
parent::__construct($scheme);
$this->clauseContainer = new One_Query_Condition_Container($this, 'AND');
$this->havingContainer = new One_Query_Condition_Container($this, 'AND');
}
/**
* Processes any possibly given filters and alters the One_Query object accordingly
*
* @param One_Query $q
*/
protected function processQueryConditions($q)
{
$condition = $this->getVariable('query', '');
if ($condition) {
$filters = array();
parse_str($this->getVariable('filters', ''), $filters);
$c = One_Repository::getFilter($condition, $q->getScheme()->getName(), $filters);
$c->affect($q);
}
}
/**
* Return the number of results when the query is performed
*
* @param One_Query $query
* @param boolean $overrideFilters
* @return int
*/
public function executeSchemeCount(One_Query $query, $overrideFilters = false)
{
$scheme = $query->getScheme();
$db = $this->db($scheme);
$renderer = $this->getRenderer();
// Need to remember the old Select in case the One_Query is reused afterwards
$oldSelect = $query->getSelect();
$query->setSelect(array('COUNT(*) AS total'));
$sql = $renderer->render($query, $overrideFilters);
// put the old Select back into the One_Query
$query->setSelect($oldSelect);
// execute query
$result = $db->query($sql);
if ($result === false) {
throw new One_Exception('MYSQLI error (' . $db->connect_errno . ') ' . $db->connect_error);
}
$row = $result->fetch_assoc();
$count = $row['total'];
return $count;
}
/**
* Render the query
*
* @param One_Query $query
* @return string
*/
public function render(One_Query $query, $overrideFilters = false)
{
$this->query = $query;
$this->scheme = $this->query->getScheme();
// if the person wants to perform a raw query, return the raw query
if (!is_null($query->getRaw())) {
if (One_Config::get('debug.query')) {
echo '<pre>';
var_dump($query->getRaw());
echo '</pre>';
}
return $query->getRaw();
}
$this->query = $query;
$this->scheme = $this->query->getScheme();
$resources = $this->scheme->getResources();
// fetch main table to fetch data from
$this->mainTable = $resources['table'];
$this->defineRole('$self$');
// add possible filters to the query
if (!$overrideFilters && isset($resources['filter'])) {
$filters = explode(';', $resources['filter']);
if (count($filters) > 0) {
foreach ($filters as $filterName) {
if ($filterName != '') {
$filter = One_Repository::getFilter($filterName, $query->getScheme()->getName());
$filter->affect($query);
}
}
}
}
// TR20100531 No longer needs to be run after the rest since joins are now checked while adding
// sselects, order, ...
$joins = NULL;
$qJoins = $query->getJoins();
if (count($qJoins) > 0) {
foreach ($qJoins as $join => $type) {
$this->defineRole($join);
$query->setRoleAlias($join, $this->aliases[$join]);
$joins .= $this->createJoin($query->getRole($join), $type);
}
}
$selects = $this->aliases['$self$'] . '.*';
if (count($query->getSelect()) > 0) {
$selects = $this->createSelects($query->getSelect());
}
// get where clauses
$whereClauses = $query->getWhereClauses();
$where = NULL;
if (!is_null($whereClauses)) {
$where = $this->whereClauses($whereClauses);
}
// get having clauses
$havingClauses = $query->getHavingClauses();
$having = NULL;
if (!is_null($havingClauses)) {
$having = $this->whereClauses($havingClauses);
}
// get order
$order = $this->createOrder();
//get grouping
$group = $this->createGroup();
// get limit
$limit = $this->createLimit();
$sql = 'SELECT ' . $selects . ' FROM ' . $this->mainTable . ' ' . $this->aliases['$self$'];
if (!is_null($joins)) {
$sql .= $joins;
}
if (!is_null($where)) {
$sql .= ' WHERE ' . $where;
}
if (!is_null($group)) {
$sql .= ' GROUP BY ' . $group;
}
if (!is_null($having)) {
$sql .= ' HAVING ' . $having;
}
if (!is_null($order)) {
$sql .= ' ORDER BY ' . $order;
}
if (!is_null($limit)) {
/* Use the following format to replace MySQL LIMIT for PL/SQL :
SELECT * FROM (
SELECT rownum rnum, a.*
FROM(
SELECT fieldA,fieldB
FROM table
ORDER BY fieldA
) a
WHERE rownum <= START + LIMIT
)
WHERE rnum >= START
** or **
SELECT rownum rnum, a.*
FROM(
SELECT fieldA,fieldB
FROM table
ORDER BY fieldA
) a
WHERE rownum <= LIMIT
*/
$qLimit = $this->query->getLimit();
if (isset($qLimit['start']) && intval($qLimit['start']) > -1) {
$start = intval($qLimit['start']);
} else {
$start = 0;
}
if (isset($qLimit['limit']) && intval($qLimit['limit']) > 0) {
$limit = intval($qLimit['limit']);
} else {
$limit = 50;
}
// @TODO: clean this up
// create alias for rownum field
$rnfield = $this->createAlias();
$subsel = $this->createAlias();
$sql = "SELECT rownum {$rnfield}, {$subsel}.* FROM ( {$sql} ) {$subsel} WHERE rownum <= {$limit}";
if ($start) {
$sql = "SELECT * FROM ( {$sql} ) WHERE {$rnfield} > {$start}";
}
}
if (One_Config::get('debug.query')) {
echo '<pre>';
var_dump($sql);
echo '</pre>';
}
return $sql;
}
public static function setDebug($bool)
{
self::$_debug = $bool;
}