1: <?php
  2: /**
  3:  * Agora_Driver_SplitSql:: provides the functions to access both threads
  4:  * sorted in a scope dedicated tables
  5:  *
  6:  * Copyright 2003-2012 Horde LLC (http://www.horde.org/)
  7:  *
  8:  * See the enclosed file COPYING for license information (GPL). If you
  9:  * did not receive this file, see http://www.horde.org/licenses/gpl.
 10:  *
 11:  * @author  Duck <duck@obala.net>
 12:  * @package Agora
 13:  */
 14: class Agora_Driver_SplitSql extends Agora_Driver {
 15: 
 16:     /**
 17:      * Constructor
 18:      */
 19:     public function __construct($scope)
 20:     {
 21:         parent::__construct($scope);
 22: 
 23:         /* trick table name */
 24:         if ($scope != 'agora') {
 25:             $this->_threads_table = 'agora_messages_' . $scope;
 26:             $this->_forums_table = 'agora_forums_' . $scope;
 27:         }
 28:     }
 29: 
 30:     /**
 31:      * Returns an ID for a given forum name.
 32:      *
 33:      * @param string $forum_name  The full forum name.
 34:      *
 35:      * @return integer  The ID of the forum.
 36:      */
 37:     public function getForumId($forum_name)
 38:     {
 39:         static $ids = array();
 40: 
 41:         if (!isset($ids[$forum_name])) {
 42:             $sql = 'SELECT forum_id FROM ' . $this->_forums_table . ' WHERE forum_name = ?';
 43:             $ids[$forum_name] = $this->_db->selectValue($sql, array($forum_name));
 44:         }
 45: 
 46:         return $ids[$forum_name];
 47:     }
 48: 
 49:     /**
 50:      * Get forums ids and titles
 51:      *
 52:      * @return array  An array of forums and form names.
 53:      */
 54:     public function getBareForums()
 55:     {
 56:         if ($this->_scope == 'agora') {
 57:             $sql = 'SELECT forum_id, forum_name FROM ' . $this->_forums_table;
 58:         } else {
 59:             $sql = 'SELECT forum_id, forum_description FROM ' . $this->_forums_table;
 60:         }
 61: 
 62:         return $this->_db->selectAssoc($sql);
 63:     }
 64: 
 65:     /**
 66:      * Fetches a list of forums.
 67:      *
 68:      * @param integer $root_forum  The first level forum.
 69:      * @param boolean $formatted   Whether to return the list formatted or raw.
 70:      * @param string  $sort_by     The column to sort by.
 71:      * @param integer $sort_dir    Sort direction, 0 = ascending,
 72:      *                             1 = descending.
 73:      * @param boolean $add_scope   Add parent forum if forum for another
 74:      *                             scopelication.
 75:      * @param string  $from        The forum to start listing at.
 76:      * @param string  $count       The number of forums to return.
 77:      *
 78:      * @return array  An array of forums.
 79:      * @throws Horde_Exception_NotFound
 80:      * @throws Agora_Exception
 81:      */
 82:     protected function _getForums($root_forum = 0, $formatted = true,
 83:                         $sort_by = 'forum_name', $sort_dir = 0,
 84:                         $add_scope = false,  $from = 0, $count = 0)
 85:     {
 86:         $key = $this->_scope . ':' . $root_forum . ':' . $formatted . ':'
 87:             . $sort_by . ':' . $sort_dir . ':' . $add_scope . ':' . $from
 88:             . ':' . $count;
 89:         $forums = $this->_getCache($key);
 90:         if ($forums) {
 91:             return unserialize($forums);
 92:         }
 93: 
 94:         $sql = 'SELECT forum_id, forum_name';
 95: 
 96:         if ($formatted) {
 97:             $sql .= ', scope, active, forum_description, forum_parent_id, '
 98:                 . 'forum_moderated, forum_attachments, message_count, thread_count, '
 99:                 . 'last_message_id, last_message_author, last_message_timestamp';
100:         }
101: 
102:         $sql .= ' FROM ' . $this->_forums_table . ' WHERE active = ? ';
103:         $params = array(1);
104: 
105:         if ($root_forum != 0) {
106:             $sql .= ' AND forum_parent_id = ? ';
107:             $params[] = $root_forum;
108:         }
109: 
110:         /* Sort by result colomn if possible */
111:         $sql .= ' ORDER BY ';
112:         if ($sort_by == 'forum_name' || $sort_by == 'message_count') {
113:             $sql .= $sort_by;
114:         } else {
115:             $sql .= 'forum_id';
116:         }
117:         $sql .= ' ' . ($sort_dir ? 'DESC' : 'ASC');
118: 
119:         /* Slice direcly in DB. */
120:         if ($count) {
121:             $sql = $this->_db->addLimitOffset($sql, array('limit' => $count, 'offset' => $from));
122:         }
123: 
124:         try {
125:             $forums = $this->_db->selectAll($sql, $params);
126:         } catch (Horde_Db_Exception $e) {
127:             throw new Agora_Exception($e->getMessage());
128:         }
129:         if (empty($forums)) {
130:             throw new Horde_Exception_NotFound(_("There are no forums."));
131:         }
132: 
133:         if ($formatted) {
134:             $forums = $this->_formatForums($forums);
135:         }
136: 
137:         $this->_setCache($key, serialize($forums));
138: 
139:         return $forums;
140:     }
141: 
142:     /**
143:      * Returns a list of threads.
144:      *
145:      * @param integer $thread_root   Message at which to start the thread.
146:      *                               If null get all forum threads
147:      * @param boolean $all_levels    Show all child levels or just one level.
148:      * @param string  $sort_by       The column by which to sort.
149:      * @param integer $sort_dir      The direction by which to sort:
150:      *                                   0 - ascending
151:      *                                   1 - descending
152:      * @param boolean $message_view
153:      * @param string  $from          The thread to start listing at.
154:      * @param string  $count         The number of threads to return.
155:      *
156:      * @throws Agora_Exception
157:      */
158:     protected function _getThreads($thread_root = 0,
159:                          $all_levels = false,
160:                          $sort_by = 'message_modifystamp',
161:                          $sort_dir = 0,
162:                          $message_view = false,
163:                          $from = 0,
164:                          $count = 0)
165:     {
166:         /* Cache */
167:         $key = $this->_scope . ':' . $this->_forum_id . ':' . $thread_root . ':' . intval($all_levels) . ':'
168:              . $sort_by . ':' . $sort_dir . ':' . intval($message_view) . ':' . intval($from) . ':' . intval($count);
169:         $messages = $this->_getCache($key, $thread_root);
170:         if ($messages) {
171:             return unserialize($messages);
172:         }
173: 
174:         $bind = $this->_buildThreadsQuery(null, $thread_root, $all_levels, $sort_by,
175:                                             $sort_dir, $message_view, $from, $count);
176: 
177:         /* Slice direcly in DB. */
178:         if ($sort_by != 'message_thread' && $count) {
179:             $bind[0] = $this->_db->addLimitOffset($bind[0], array('limit' => $count, 'offset' => $from));
180:         }
181: 
182:         try {
183:             $messages = $this->_db->selectAll($bind[0], $bind[1]);
184:         } catch (Horde_Db_Exception $e) {
185:             throw new Agora_Exception($e->getMessage());
186:         }
187: 
188:         $messages = $this->_formatThreads($messages, $sort_by, $message_view, $thread_root);
189: 
190:         $this->_setCache($key, serialize($messages), $thread_root);
191: 
192:         return $messages;
193:     }
194: 
195:     /**
196:      * Returns a list of threads.
197:      *
198:      * @param string  $forum_owner   Forum owner
199:      * @param integer $thread_root   Message at which to start the thread.
200:      *                               If null get all forum threads
201:      * @param boolean $all_levels    Show all child levels or just one level.
202:      * @param string  $sort_by       The column by which to sort.
203:      * @param integer $sort_dir      The direction by which to sort:
204:      *                                   0 - ascending
205:      *                                   1 - descending
206:      * @param boolean $message_view
207:      * @param string  $from          The thread to start listing at.
208:      * @param string  $count         The number of threads to return.
209:      *
210:      * @throws Agora_Exception
211:      */
212:     public function getThreadsByForumOwner($forum_owner,
213:                          $thread_root = 0,
214:                          $all_levels = false,
215:                          $sort_by = 'message_modifystamp',
216:                          $sort_dir = 0,
217:                          $message_view = false,
218:                          $from = 0,
219:                          $count = 0)
220:     {
221:         $bind = $this->_buildThreadsQuery($forum_owner, $thread_root, $all_levels,
222:                                             $sort_by, $sort_dir, $message_view, $from, $count);
223: 
224:         if ($sort_by != 'message_thread' && $count) {
225:             $bind[0] = $this->_db->addLimitOffset($bind[0], array('limit' => $count, 'offset' => $from));
226:         }
227: 
228:         try {
229:             $messages = $this->_db->selectAll($bind[0], $bind[1]);
230:         } catch (Horde_Db_Exception $e) {
231:             throw new Agora_Exception($e->getMessage());
232:         }
233: 
234:         return $this->_formatThreads($messages, $sort_by, $message_view, $thread_root);
235:     }
236: 
237:     /**
238:      * Build threads query.
239:      *
240:      * @param string  $forum_owner   Forum owner
241:      * @param integer $thread_root   Message at which to start the thread.
242:      *                               If null get all forum threads
243:      * @param boolean $all_levels    Show all child levels or just one level.
244:      * @param string  $sort_by       The column by which to sort.
245:      * @param integer $sort_dir      The direction by which to sort:
246:      *                                   0 - ascending
247:      *                                   1 - descending
248:      * @param boolean $message_view
249:      * @param string  $from          The thread to start listing at.
250:      * @param string  $count         The number of threads to return.
251:      */
252:     private function _buildThreadsQuery($forum_owner = null,
253:                          $thread_root = 0,
254:                          $all_levels = false,
255:                          $sort_by = 'message_modifystamp',
256:                          $sort_dir = 0,
257:                          $message_view = false,
258:                          $from = 0,
259:                          $count = 0)
260:     {
261:         $params = array();
262:         $where = '';
263:         $sql = 'SELECT m.message_id, m.forum_id, m.message_thread, m.parents, m.message_author, '
264:              . 'm.message_subject, m.message_timestamp, m.locked, m.view_count, '
265:              . 'm.message_seq, m.attachments';
266: 
267:         if ($message_view) {
268:             $sql .= ', m.body';
269:         }
270: 
271:         if ($thread_root == 0) {
272:             $sql .= ', m.last_message_id, m.last_message_author, m.message_modifystamp AS last_message_timestamp';
273:         }
274: 
275:         /* Get messages form a specific owner */
276:         if ($forum_owner !== null) {
277:             $sql .= ', f.forum_name FROM ' . $this->_threads_table . ' m, ' . $this->_forums_table . ' f';
278:             $where .= ' AND f.author = ? AND f.forum_id = m.forum_id ';
279:             $params[] = $forum_owner;
280:         } else {
281:             $sql .= ' FROM ' . $this->_threads_table . ' m';
282:             /* Get messages form a specific forum */
283:             if ($this->_forum_id) {
284:                 $where .= ' AND m.forum_id = ?';
285:                 $params[] = $this->_forum_id;
286:             }
287:         }
288: 
289:         /* Get all levels? */
290:         if (!$all_levels) {
291:             $where .= ' AND m.parents = ?';
292:             $params[] = '';
293:         }
294: 
295:         /* Get only approved messages. */
296:         if ($this->_forum['forum_moderated']) {
297:             $where .= ' AND m.approved = ?';
298:             $params[] = 1;
299:         }
300: 
301:         if ($thread_root) {
302:             $where .= ' AND (message_id = ? OR message_thread = ?)';
303:             $params[] = (int)$thread_root;
304:             $params[] = (int)$thread_root;
305:         }
306: 
307:         /* Sort by result column. */
308:         $sql .= ' WHERE ' . substr($where, 5) . ' ORDER BY ' . $sort_by . ' ' . ($sort_dir ? 'DESC' : 'ASC');
309: 
310:         return array($sql, $params);
311:     }
312: }
313: