[Sommaire] [Imprimer]

   1  <?php
   2  /**
   3   * This class provides attributes methods for any existing SQL class.
   4   *
   5   * $Horde: framework/SQL/SQL/Attributes.php,v 1.14.10.9 2006/01/01 21:28:33 jan Exp $
   6   *
   7   * Copyright 1999-2006 Chuck Hagenbuch <chuck@horde.org>
   8   *
   9   * See the enclosed file COPYING for license information (LGPL).  If you
  10   * did not receive this file, see http://www.fsf.org/copyleft/lgpl.html.
  11   *
  12   * @author  Chuck Hagenbuch <chuck@horde.org>
  13   * @since   Horde 2.2
  14   * @package Horde_SQL
  15   */
  16  class Horde_SQL_Attributes {
  17  
  18      /**
  19       * The PEAR::DB object to run queries with.
  20       *
  21       * @var DB
  22       */
  23      var $_db;
  24  
  25      /**
  26       * Parameters to use when generating queries:
  27       *   id_column       - The primary id column to use in joins.
  28       *   primary_table   - The main table name.
  29       *   attribute_table - The table that the attributes are stored in.
  30       *
  31       * @var array
  32       */
  33      var $_params = array();
  34  
  35      /**
  36       * The number of copies of the attributes table that we need to join on in
  37       * the current query.
  38       *
  39       * @var integer
  40       */
  41      var $_table_count = 1;
  42  
  43      /**
  44       * Constructor.
  45       *
  46       * @param DB $dbh        A PEAR::DB object.
  47       * @param array $params  The id column, table names, etc.
  48       */
  49      function Horde_SQL_Attributes($dbh, $params)
  50      {
  51          $this->_db = $dbh;
  52          $this->_params = $params;
  53      }
  54  
  55      /**
  56       * Returns all attributes for a given id or multiple ids.
  57       *
  58       * @param integer | array $id  The id to fetch or an array of ids.
  59       *
  60       * @return array  A hash of attributes, or a multi-level hash
  61       *                of ids => their attributes.
  62       */
  63      function getAttributes($id)
  64      {
  65          if (is_array($id)) {
  66              $query = sprintf('SELECT %1$s, attribute_name as name, attribute_key as "key", attribute_value as value FROM %2$s WHERE %1$s IN (%3$s)',
  67                               $this->_params['id_column'],
  68                               $this->_params['attribute_table'],
  69                               implode(', ', $id));
  70  
  71              Horde::logMessage('SQL Query by Horde_SQL_Attributes::getAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
  72              $rows = $this->_db->getAll($query, DB_FETCHMODE_ASSOC);
  73              if (is_a($rows, 'PEAR_Error')) {
  74                  return $rows;
  75              }
  76  
  77              $id_column = $this->_params['id_column'];
  78              $data = array();
  79              foreach ($rows as $row) {
  80                  if (empty($data[$row[$id_column]])) {
  81                      $data[$row[$id_column]] = array();
  82                  }
  83                  $data[$row[$id_column]][] = array('name'  => $row['name'],
  84                                                 'key'   => $row['key'],
  85                                                 'value' => $row['value']);
  86              }
  87              return $data;
  88          } else {
  89              $query = sprintf('SELECT %1$s, attribute_name as name, attribute_key as "key", attribute_value as value FROM %2$s WHERE %1$s = %3$s',
  90                               $this->_params['id_column'],
  91                               $this->_params['attribute_table'],
  92                               (int)$id);
  93              Horde::logMessage('SQL Query by Horde_SQL_Attributes::getAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
  94              return $this->_db->getAll($query, DB_FETCHMODE_ASSOC);
  95          }
  96      }
  97  
  98      /**
  99       * Return a set of ids based on a set of attribute criteria.
 100       *
 101       * @param array $criteria  The array of criteria. Example:
 102       *                         $criteria['OR'] = array(
 103       *                                      array('field' => 'name',
 104       *                                            'op'    => '=',
 105       *                                            'test'  => 'foo'),
 106       *                                      array('field' => 'name',
 107       *                                            'op'    => '=',
 108       *                                            'test'  => 'bar'));
 109       *                          This would return all ids for which the field
 110       *                          attribute_name is either 'foo' or 'bar'.
 111       */
 112      function getByAttributes($criteria)
 113      {
 114          if (!count($criteria)) {
 115              return array();
 116          }
 117  
 118          /* Build the query. */
 119          $this->_table_count = 1;
 120          $query = '';
 121          foreach ($criteria as $key => $vals) {
 122              if ($key == 'OR' || $key == 'AND') {
 123                  if (!empty($query)) {
 124                      $query .= ' ' . $key . ' ';
 125                  }
 126                  $query .= '(' . $this->_buildAttributeQuery($key, $vals) . ')';
 127              }
 128          }
 129  
 130          /* Build the FROM/JOIN clauses. */
 131          $joins = array();
 132          $pairs = array();
 133          for ($i = 1; $i <= $this->_table_count; $i++) {
 134              $joins[] = sprintf('LEFT JOIN %1$s a%2$s ON a%2$s.%3$s = m.%3$s',
 135                                 $this->_params['attribute_table'],
 136                                 $i,
 137                                 $this->_params['id_column']);
 138  
 139              $pairs[] = 'AND a1.attribute_name = a' . $i . '.attribute_name';
 140          }
 141          $joins = implode(' ', $joins);
 142          $pairs = implode(' ', $pairs);
 143  
 144          $query = sprintf('SELECT DISTINCT a1.%s FROM %s m %s WHERE %s %s',
 145                           $this->_params['id_column'],
 146                           $this->_params['primary_table'],
 147                           $joins,
 148                           $query,
 149                           $pairs);
 150  
 151          Horde::logMessage('SQL Query by Horde_SQL_Attributes::getByAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
 152  
 153          return $this->_db->getCol($query);
 154      }
 155  
 156      /**
 157       * Given a new attribute set and an id, insert each into the DB. If
 158       * anything fails in here, rollback the transaction, return the relevant
 159       * error and bail out.
 160       *
 161       * @param integer $id        The id of the record for which attributes are
 162       *                           being inserted.
 163       * @param array $attributes  An hash containing the attributes.
 164       */
 165      function insertAttributes($id, $attributes)
 166      {
 167          foreach ($attributes as $attr) {
 168              $query = 'INSERT INTO ' . $this->_params['attribute_table'] .
 169                       ' (' . $this->_params['id_column'] . ', attribute_name,' .
 170                       ' attribute_key, attribute_value) VALUES (?, ?, ?, ?)';
 171              $values = array((int)$id,
 172                              $attr['name'],
 173                              $attr['key'],
 174                              $attr['value']);
 175  
 176              Horde::logMessage('SQL Query by Horde_SQL_Attributes::insertAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
 177  
 178              $result = $this->_db->query($query, $values);
 179              if (is_a($result, 'PEAR_Error')) {
 180                  $this->_db->rollback();
 181                  $this->_db->autoCommit(true);
 182                  return $result;
 183              }
 184          }
 185  
 186          /* Commit the transaction, and turn autocommit back on. */
 187          $result = $this->_db->commit();
 188          $this->_db->autoCommit(true);
 189      }
 190  
 191      /**
 192       * Given an id, delete all attributes for that id from the
 193       * attributes table.
 194       *
 195       * @param integer $id  The id of the record for which attributes are being
 196       *                     deleted.
 197       */
 198      function deleteAttributes($id)
 199      {
 200          /* Delete attributes. */
 201          $query = sprintf('DELETE FROM %s WHERE %s = %s',
 202                           $this->_params['attribute_table'],
 203                           $this->_params['id_column'],
 204                           (int)$id);
 205  
 206          Horde::logMessage('SQL Query by Horde_SQL_Attributes::deleteAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
 207          $result = $this->_db->query($query);
 208          if (is_a($result, 'PEAR_Error')) {
 209              return $result;
 210          }
 211  
 212          return true;
 213      }
 214  
 215      /**
 216       * Given an id, update all attributes for that id in the attributes table
 217       * with the new attributes.
 218       *
 219       * @param integer $id        The id of the record for which attributes are
 220       *                           being deleted.
 221       * @param array $attributes  An hash containing the attributes.
 222       */
 223      function updateAttributes($id, $attributes)
 224      {
 225          /* Delete the old attributes. */
 226          $result = $this->deleteAttributes($id);
 227          if (is_a($result, 'PEAR_Error')) {
 228              return $result;
 229          }
 230  
 231          /* Insert the new attribute set. */
 232          $result = $this->insertAttributes($id, $attributes);
 233          return $result;
 234      }
 235  
 236      /**
 237       * Build a piece of an attribute query.
 238       *
 239       * @param string $glue     The glue to join the criteria (OR/AND).
 240       * @param array $criteria  The array of criteria.
 241       * @param boolean $join    Should we join on a clean attributes table?
 242       *
 243       * @return string  An SQL fragment.
 244       */
 245      function _buildAttributeQuery($glue, $criteria, $join = false)
 246      {
 247          require_once 'Horde/SQL.php';
 248  
 249          /* Initialize the clause that we're building. */
 250          $clause = '';
 251  
 252          /* Get the table alias to use for this set of criteria. */
 253          if ($join) {
 254              $alias = $this->_getAlias(true);
 255          } else {
 256              $alias = $this->_getAlias();
 257          }
 258  
 259          foreach ($criteria as $key => $vals) {
 260              if (!empty($vals['OR']) || !empty($vals['AND'])) {
 261                  if (!empty($clause)) {
 262                      $clause .= ' ' . $glue . ' ';
 263                  }
 264                  $clause .= '(' . $this->_buildAttributeQuery($glue, $vals) . ')';
 265              } elseif (!empty($vals['JOIN'])) {
 266                  if (!empty($clause)) {
 267                      $clause .= ' ' . $glue . ' ';
 268                  }
 269                  $clause .= $this->_buildAttributeQuery($glue, $vals['JOIN'], true);
 270              } else {
 271                  if (isset($vals['field'])) {
 272                      if (!empty($clause)) {
 273                          $clause .= ' ' . $glue . ' ';
 274                      }
 275                      $clause .= Horde_SQL::buildClause($this->_db, $alias . '.attribute_' . $vals['field'], $vals['op'], $vals['test']);
 276                  } else {
 277                      foreach ($vals as $test) {
 278                          if (!empty($clause)) {
 279                              $clause .= ' ' . $key . ' ';
 280                          }
 281                          $clause .= Horde_SQL::buildClause($this->_db, $alias . '.attribute_' . $test['field'], $test['op'], $test['test']);
 282                      }
 283                  }
 284              }
 285          }
 286  
 287          return $clause;
 288      }
 289  
 290      /**
 291       * Get an alias to an attributes table, incrementing it if
 292       * necessary.
 293       *
 294       * @param boolean $increment  Increment the alias count? Defaults to false.
 295       */
 296      function _getAlias($increment = false)
 297      {
 298          static $seen  = array();
 299  
 300          if ($increment && !empty($seen[$this->_table_count])) {
 301              $this->_table_count++;
 302          }
 303  
 304          $seen[$this->_table_count] = true;
 305          return 'a' . $this->_table_count;
 306      }
 307  
 308      /**
 309       * Attempts to return a reference to a concrete SQL Attributes
 310       * instance based on parameters passed. It will only create a new
 311       * instance if no Attributes instance with the same parameters
 312       * currently exists.
 313       *
 314       * This should be used if multiple SQL attribute tables are
 315       * required.
 316       *
 317       * This method must be invoked as: $var =
 318       * &Horde_SQL_Attributes::singleton()
 319       *
 320       * @param DB $dbh        An object pointing to a SQL database handle.
 321       *
 322       * @param array $params  Parameters for the attributes table, consisting
 323       *                       of the following keys:
 324       *                       'primary_table'   - the main SQL table
 325       *                       'attribute_table' - the second table containing
 326       *                                           the attributes to the main
 327       *                                           table.
 328       *                       'id_column'       - the name of the column with
 329       *                                           the ID or key field.
 330       */
 331      function &singleton($dbh, $params)
 332      {
 333          static $instances;
 334  
 335          if (!isset($instances)) {
 336              $instances = array();
 337          }
 338  
 339          $signature = serialize($params);
 340          if (!isset($instances[$signature])) {
 341              $instances[$signature] = &new Horde_SQL_Attributes($dbh, $params);
 342          }
 343  
 344          return $instances[$signature];
 345      }
 346  
 347  }