1: <?php
  2: /**
  3:  * Preferences storage implementation for a SQL database.
  4:  *
  5:  * Copyright 1999-2012 Horde LLC (http://www.horde.org/)
  6:  *
  7:  * See the enclosed file COPYING for license information (LGPL). If you
  8:  * did not receive this file, see http://www.horde.org/licenses/lgpl21.
  9:  *
 10:  * @author   Jon Parise <jon@horde.org>
 11:  * @author   Michael Slusarz <slusarz@horde.org>
 12:  * @category Horde
 13:  * @license  http://www.horde.org/licenses/lgpl21 LGPL 2.1
 14:  * @package  Prefs
 15:  */
 16: class Horde_Prefs_Storage_Sql extends Horde_Prefs_Storage_Base
 17: {
 18:     /**
 19:      * Handle for the current database connection.
 20:      *
 21:      * @var Horde_Db_Adapter
 22:      */
 23:     protected $_db;
 24: 
 25:     /**
 26:      * Constructor.
 27:      *
 28:      * @param string $user   The username.
 29:      * @param array $params  Configuration parameters.
 30:      * <pre>
 31:      * 'db' - (Horde_Db_Adapter) [REQUIRED] The DB instance.
 32:      * 'table' - (string) The name of the prefs table.
 33:      *           DEFAULT: 'horde_prefs'
 34:      * </pre>
 35:      *
 36:      * @throws InvalidArgumentException
 37:      */
 38:     public function __construct($user, array $params = array())
 39:     {
 40:         if (!isset($params['db'])) {
 41:             throw new InvalidArgumentException('Missing db parameter.');
 42:         }
 43:         $this->_db = $params['db'];
 44:         unset($params['db']);
 45: 
 46:         $params = array_merge(array(
 47:             'table' => 'horde_prefs'
 48:         ), $params);
 49: 
 50:         parent::__construct($user, $params);
 51:     }
 52: 
 53:     /**
 54:      * Returns the charset of the DB backend.
 55:      *
 56:      * @return string  The connection's charset.
 57:      */
 58:     public function getCharset()
 59:     {
 60:         return $this->_db->getOption('charset');
 61:     }
 62: 
 63:     /**
 64:      */
 65:     public function get($scope_ob)
 66:     {
 67:         $charset = $this->_db->getOption('charset');
 68:         $query = 'SELECT pref_scope, pref_name, pref_value FROM ' .
 69:             $this->_params['table'] . ' ' .
 70:             'WHERE pref_uid = ? AND pref_scope = ?';
 71:         $values = array($this->_params['user'], $scope_ob->scope);
 72: 
 73:         try {
 74:             $result = $this->_db->selectAll($query, $values);
 75:             $columns = $this->_db->columns($this->_params['table']);
 76:         } catch (Horde_Db_Exception $e) {
 77:             throw new Horde_Prefs_Exception($e);
 78:         }
 79: 
 80:         foreach ($result as $row) {
 81:             $name = trim($row['pref_name']);
 82:             $value = $columns['pref_value']->binaryToString($row['pref_value']);
 83:             $scope_ob->set($name, Horde_String::convertCharset($value, $charset, 'UTF-8'));
 84:         }
 85: 
 86:         return $scope_ob;
 87:     }
 88: 
 89:     /**
 90:      */
 91:     public function store($scope_ob)
 92:     {
 93:         if (!$this->_db->isActive()) {
 94:             $this->_db->reconnect();
 95:         }
 96: 
 97:         $charset = $this->_db->getOption('charset');
 98: 
 99:         // For each preference, check for an existing table row and
100:         // update it if it's there, or create a new one if it's not.
101:         foreach ($scope_ob->getDirty() as $name) {
102:             $value = $scope_ob->get($name);
103:             $values = array($this->_params['user'], $name, $scope_ob->scope);
104: 
105:             if (is_null($value)) {
106:                 $query = 'DELETE FROM ' . $this->_params['table'] .
107:                     ' WHERE pref_uid = ? AND pref_name = ?' .
108:                     ' AND pref_scope = ?';
109: 
110:                 try {
111:                     $this->_db->delete($query, $values);
112:                 } catch (Horde_Db_Exception $e) {
113:                     throw new Horde_Prefs_Exception($e);
114:                 }
115:             } else {
116:                 // Does a row already exist for this preference?
117:                 $query = 'SELECT 1 FROM ' . $this->_params['table'] .
118:                     ' WHERE pref_uid = ? AND pref_name = ?' .
119:                     ' AND pref_scope = ?';
120: 
121:                 try {
122:                     $check = $this->_db->selectValue($query, $values);
123:                 } catch (Horde_Db_Exception $e) {
124:                     throw new Horde_Prefs_Exception($e);
125:                 }
126: 
127:                 /* Driver has no support for storing locked status. */
128:                 $value = Horde_String::convertCharset($value, 'UTF-8', $charset);
129:                 $value = new Horde_Db_Value_Binary($value);
130: 
131:                 if (empty($check)) {
132:                     // Insert a new row.
133:                     $query = 'INSERT INTO ' . $this->_params['table'] . ' ' .
134:                         '(pref_uid, pref_scope, pref_name, pref_value) VALUES' .
135:                         '(?, ?, ?, ?)';
136:                     $values = array(
137:                         $this->_params['user'],
138:                         $scope_ob->scope,
139:                         $name,
140:                         $value
141:                     );
142: 
143:                     try {
144:                         $this->_db->insert($query, $values);
145:                     } catch (Horde_Db_Exception $e) {
146:                         throw new Horde_Prefs_Exception($e);
147:                     }
148:                 } else {
149:                     // Update the existing row.
150:                     $query = 'UPDATE ' . $this->_params['table'] .
151:                         ' SET pref_value = ?' .
152:                         ' WHERE pref_uid = ?' .
153:                         ' AND pref_name = ?' .
154:                         ' AND pref_scope = ?';
155:                     $values = array(
156:                         $value,
157:                         $this->_params['user'],
158:                         $name,
159:                         $scope_ob->scope
160:                     );
161: 
162:                     try {
163:                         $this->_db->update($query, $values);
164:                     } catch (Horde_Db_Exception $e) {
165:                         throw new Horde_Prefs_Exception($e);
166:                     }
167:                 }
168:             }
169:         }
170:     }
171: 
172:     /**
173:      */
174:     public function remove($scope = null, $pref = null)
175:     {
176:         $query = 'DELETE FROM ' . $this->_params['table'] .
177:                  ' WHERE pref_uid = ?';
178:         $values = array($this->_params['user']);
179: 
180:         if (!is_null($scope)) {
181:             $query .= ' AND pref_scope = ?';
182:             $values[] = $scope;
183: 
184:             if (!is_null($pref)) {
185:                 $query .= ' AND pref_name = ?';
186:                 $values[] = $pref;
187:             }
188:         }
189: 
190:         try {
191:             $this->_db->delete($query, $values);
192:         } catch (Horde_Db_Exception $e) {
193:             throw new Horde_Prefs_Exception($e);
194:         }
195:     }
196: 
197:     /**
198:      * Lists all available scopes.
199:      *
200:      * @since Horde_Prefs 1.1.0
201:      *
202:      * @return array The list of scopes stored in the backend.
203:      */
204:     public function listScopes()
205:     {
206:         $query = 'SELECT ' . $this->_db->distinct('pref_scope') . ' FROM '
207:             . $this->_params['table'];
208:         try {
209:             return $this->_db->selectValues($query);
210:         } catch (Horde_Db_Exception $e) {
211:             throw new Horde_Prefs_Exception($e);
212:         }
213:     }
214: 
215: }
216: