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