[dev] Re: [cvs] commit: horde/lib/SQL Attributes.php
Marko Djukic
mdjukic at horde.org
Wed Nov 19 18:22:50 PST 2003
Quoting Marko Djukic <tech at oblo.com>:
> Attributes class to allow any SQL table to have attribute capabilites like
> Horde_Categories (but without the child/parent logic present in
> Horde_Categories).
is this a good direction for handling this?
horde_category_attributes could then be handled as attached patch - essentially
just adding an extra class to extend Horde_SQL_Attributes and change the
getByAttributes to handle child/parent logic, and cutting out the rest of the
duplicated code.
marko
-------------- next part --------------
Index: Category/sql.php
===================================================================
RCS file: /repository/horde/lib/Category/sql.php,v
retrieving revision 1.97
diff -u -r1.97 sql.php
--- Category/sql.php 5 Nov 2003 23:25:45 -0000 1.97
+++ Category/sql.php 20 Nov 2003 00:33:38 -0000
@@ -1,4 +1,7 @@
<?php
+
+require_once HORDE_BASE . '/lib/SQL/Attributes.php';
+
/**
* The Category_sql:: class provides an SQL implementation of the Horde
* category system.
@@ -48,11 +51,11 @@
var $_connected = false;
/**
- * The number of copies of the horde_category_attributes table
- * that we need to join on in the current query.
- * @var integer $_tableCount
+ * The attributes object that will handle the actual operations with
+ * horde_category_attributes table.
+ * @var object $_attributes
*/
- var $_tableCount = 1;
+ var $_attributes = null;
/**
* Constructs a new SQL category object.
@@ -63,6 +66,12 @@
{
parent::Category($params);
$this->_connect();
+
+ /* Set up the attributes object in the $_attributes var. */
+ $att_params = array('table_main' => 'horde_categories',
+ 'table_attributes' => 'horde_category_attributes',
+ 'id_col' => 'category_id');
+ $this->_attributes = &Category_Attributes::singleton($this->_db, $att_params);
}
/**
@@ -627,184 +636,21 @@
{
$this->_connect();
- if (is_array($cid)) {
- $query = sprintf('SELECT category_id, attribute_name as name, attribute_key as "key", attribute_value as value FROM %s WHERE category_id IN (%s)',
- $this->_params['table_attributes'],
- implode(', ', $cid));
-
- Horde::logMessage('SQL Query by Category_sql::getCategoryAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
- $rows = $this->_db->getAll($query, DB_FETCHMODE_ASSOC);
- if (is_a($rows, 'PEAR_Error')) {
- return $rows;
- }
-
- $data = array();
- foreach ($rows as $row) {
- if (empty($data[$row['category_id']])) {
- $data[$row['category_id']] = array();
- }
- $data[$row['category_id']][] = array('name' => $row['name'],
- 'key' => $row['key'],
- 'value' => $row['value']);
- }
- return $data;
- } else {
- $query = sprintf('SELECT attribute_name as name, attribute_key as "key", attribute_value as value FROM %s WHERE category_id = %s',
- $this->_params['table_attributes'],
- (int)$cid);
-
- Horde::logMessage('SQL Query by Category_sql::getCategoryAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
- return $this->_db->getAll($query, DB_FETCHMODE_ASSOC);
- }
+ return $this->_attributes->getAttributes($cid);
}
/**
* Return a set of category ids based on a set of attribute criteria.
*
- * @param array $criteria The array of criteria.
- * @param string $parent (optional) The parent share to start searching at.
- * @param boolean $allLevels (optional) Return all levels, or just the direct children
- * of $parent? Defaults to all levels.
+ * @param array $criteria The array of criteria.
+ * @param optional string $parent The parent share to start searching at.
+ * @param optional bool $allLevels Return all levels, or just the direct
+ * children of $parent? Defaults to all
+ * levels.
*/
function getCategoriesByAttributes($criteria, $parent = '-1', $allLevels = true)
{
- if (!count($criteria)) {
- return array();
- }
-
- /* Build the query. */
- $this->_tableCount = 1;
- $query = '';
- foreach ($criteria as $key => $vals) {
- if ($key == 'OR' || $key == 'AND') {
- if (!empty($query)) {
- $query .= ' ' . $key . ' ';
- }
- $query .= '(' . $this->_buildAttributeQuery($key, $vals) . ')';
- }
- }
-
- // Add filtering by parent, and for one or all levels.
- $levelQuery = '';
- if ($parent != '-1') {
- $parts = explode(':', $parent);
- $parents = '';
- $pstring = '';
- foreach ($parts as $part) {
- $pstring .= (empty($pstring) ? '' : ':') . $part;
- $pid = $this->getCategoryId($pstring);
- if (is_a($pid, 'PEAR_Error')) {
- return $pid;
- }
- $parents .= ':' . $pid;
- }
-
- if ($allLevels) {
- $levelQuery = sprintf('AND (category_parents = %s OR category_parents LIKE %s)',
- $this->_db->quote($parents),
- $this->_db->quote($parents . ':%'));
- } else {
- $levelQuery = sprintf('AND category_parents = %s', $this->_db->quote($parents));
- }
- } elseif (!$allLevels) {
- $levelQuery = "AND category_parents = ''";
- }
-
- // Build the FROM/JOIN clauses.
- $joins = array();
- $pairs = array();
- for ($i = 1; $i <= $this->_tableCount; $i++) {
- $joins[] = 'LEFT JOIN ' . $this->_params['table_attributes'] . ' a' . $i . ' ON a' . $i . '.category_id = c.category_id';
- $pairs[] = 'AND a1.attribute_name = a' . $i . '.attribute_name';
- }
- $joins = implode(' ', $joins);
- $pairs = implode(' ', $pairs);
-
- $query = sprintf('SELECT DISTINCT a1.category_id, c.category_name FROM %s c %s' .
- ' WHERE c.group_uid = %s AND %s %s %s',
- $this->_params['table'],
- $joins,
- $this->_db->quote($this->_params['group']),
- $query,
- $levelQuery,
- $pairs);
-
- Horde::logMessage('SQL Query by Category_sql::getCategoriesByAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
-
- return $this->_db->getAssoc($query);
- }
-
- /**
- * Build a piece of an attribute query.
- *
- * @param string $glue The glue to join the criteria (OR/AND).
- * @param array $criteria The array of criteria.
- * @param boolean $join Should we join on a clean horde_category_attributes
- * table? Defaults to false.
- *
- * @return string An SQL fragment.
- */
- function _buildAttributeQuery($glue, $criteria, $join = false)
- {
- require_once HORDE_BASE . '/lib/SQL.php';
-
- // Initialize the clause that we're building.
- $clause = '';
-
- // Get the table alias to use for this set of criteria.
- if ($join) {
- $alias = $this->_getAlias(true);
- } else {
- $alias = $this->_getAlias();
- }
-
- foreach ($criteria as $key => $vals) {
- if (!empty($vals['OR']) || !empty($vals['AND'])) {
- if (!empty($clause)) {
- $clause .= ' ' . $glue . ' ';
- }
- $clause .= '(' . $this->_buildAttributeQuery($glue, $vals) . ')';
- } elseif (!empty($vals['JOIN'])) {
- if (!empty($clause)) {
- $clause .= ' ' . $glue . ' ';
- }
- $clause .= $this->_buildAttributeQuery($glue, $vals['JOIN'], true);
- } else {
- if (isset($vals['field'])) {
- if (!empty($clause)) {
- $clause .= ' ' . $glue . ' ';
- }
- $clause .= Horde_SQL::buildClause($this->_db, $alias . '.attribute_' . $vals['field'], $vals['op'], $vals['test']);
- } else {
- foreach ($vals as $test) {
- if (!empty($clause)) {
- $clause .= ' ' . $key . ' ';
- }
- $clause .= Horde_SQL::buildClause($this->_db, $alias . '.attribute_' . $test['field'], $test['op'], $test['test']);
- }
- }
- }
- }
-
- return $clause;
- }
-
- /**
- * Get an alias to horde_category_attributes, incrementing it if
- * necessary.
- *
- * @param boolean $increment Increment the alias count? Defaults to no.
- */
- function _getAlias($increment = false)
- {
- static $seen = array();
-
- if ($increment && !empty($seen[$this->_tableCount])) {
- $this->_tableCount++;
- }
-
- $seen[$this->_tableCount] = true;
- return 'a' . $this->_tableCount;
+ return $this->_attributes->getByAttributes($criteria, $parent, $allLebels);
}
/**
@@ -935,6 +781,78 @@
}
return true;
+ }
+
+}
+
+class Category_Attributes extends Horde_SQL_Attributes {
+
+ function getByAttributes($criteria, $parent = '-1', $allLevels = true)
+ {
+ if (!count($criteria)) {
+ return array();
+ }
+
+ /* Build the query. */
+ $this->_table_count = 1;
+ $query = '';
+ foreach ($criteria as $key => $vals) {
+ if ($key == 'OR' || $key == 'AND') {
+ if (!empty($query)) {
+ $query .= ' ' . $key . ' ';
+ }
+ $query .= '(' . $this->_buildAttributeQuery($key, $vals) . ')';
+ }
+ }
+
+ // Add filtering by parent, and for one or all levels.
+ $levelQuery = '';
+ if ($parent != '-1') {
+ $parts = explode(':', $parent);
+ $parents = '';
+ $pstring = '';
+ foreach ($parts as $part) {
+ $pstring .= (empty($pstring) ? '' : ':') . $part;
+ $pid = $this->getCategoryId($pstring);
+ if (is_a($pid, 'PEAR_Error')) {
+ return $pid;
+ }
+ $parents .= ':' . $pid;
+ }
+
+ if ($allLevels) {
+ $levelQuery = sprintf('AND (category_parents = %s OR category_parents LIKE %s)',
+ $this->_db->quote($parents),
+ $this->_db->quote($parents . ':%'));
+ } else {
+ $levelQuery = sprintf('AND category_parents = %s', $this->_db->quote($parents));
+ }
+ } elseif (!$allLevels) {
+ $levelQuery = "AND category_parents = ''";
+ }
+
+ // Build the FROM/JOIN clauses.
+ $joins = array();
+ $pairs = array();
+ for ($i = 1; $i <= $this->_table_count; $i++) {
+ $joins[] = 'LEFT JOIN ' . $this->_params['table_attributes'] . ' a' . $i . ' ON a' . $i . '.category_id = c.category_id';
+ $pairs[] = 'AND a1.attribute_name = a' . $i . '.attribute_name';
+ }
+ $joins = implode(' ', $joins);
+ $pairs = implode(' ', $pairs);
+
+ $query = sprintf('SELECT DISTINCT a1.category_id, c.category_name FROM %s c %s' .
+ ' WHERE c.group_uid = %s AND %s %s %s',
+ $this->_params['table'],
+ $joins,
+ $this->_db->quote($this->_params['group']),
+ $query,
+ $levelQuery,
+ $pairs);
+
+ Horde::logMessage('SQL Query by Category_sql::getCategoriesByAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
+
+ return $this->_db->getAssoc($query);
}
}
More information about the dev
mailing list