1: <?php
  2: /**
  3:  * Jonah storage implementation for PHP's PEAR database abstraction layer.
  4:  *
  5:  * The table structure can be created by the scripts/db/jonah_news.sql
  6:  * script. The needed tables are jonah_channels and jonah_stories.
  7:  *
  8:  * Copyright 2002-2012 Horde LLC (http://www.horde.org/)
  9:  *
 10:  * See the enclosed file LICENSE for license information (BSD). If you did not
 11:  * did not receive this file, see http://cvs.horde.org/co.php/jonah/LICENSE.
 12:  *
 13:  * @author  Marko Djukic <marko@oblo.com>
 14:  * @author  Chuck Hagenbuch <chuck@horde.org>
 15:  * @author  Jan Schneider <jan@horde.org>
 16:  * @author  Ben Klang <ben@alkaloid.net>
 17:  * @author  Michael J. Rubinsky <mrubinsk@horde.org>
 18:  * @package Jonah
 19:  */
 20: class Jonah_Driver_Sql extends Jonah_Driver
 21: {
 22:     /**
 23:      * Handle for the current database connection.
 24:      *
 25:      * @TODO: Refactor to use Horde_Db
 26:      * @var DB
 27:      */
 28:     protected $_db;
 29: 
 30:     /**
 31:      * Boolean indicating whether or not we're connected to the SQL server.
 32:      *
 33:      * @var boolean
 34:      */
 35:     protected $_connected = false;
 36: 
 37: 
 38:     public function __construct($params = array())
 39:     {
 40:         parent::__construct($params);
 41:         $this->_connect();
 42:     }
 43: 
 44:     /**
 45:      * Saves a channel to the backend.
 46:      *
 47:      * @param array $info  The channel to add.
 48:      *                     Must contain a combination of the following
 49:      *                     entries:
 50:      * <pre>
 51:      * 'channel_id'       If empty a new channel is being added, otherwise one
 52:      *                    is being edited.
 53:      * 'channel_slug'     The channel slug.
 54:      * 'channel_name'     The headline.
 55:      * 'channel_desc'     A description of this channel.
 56:      * 'channel_type'     Whether internal or external.
 57:      * 'channel_interval' If external then interval at which to refresh.
 58:      * 'channel_link'     The link to the source.
 59:      * 'channel_url'      The url from where to fetch the story list.
 60:      * 'channel_image'    A channel image.
 61:      * </pre>
 62:      *
 63:      * @return integer The channel ID.
 64:      * @throws Jonah_Exception
 65:      */
 66:     public function saveChannel(&$info)
 67:     {
 68:         if (empty($info['channel_id'])) {
 69:             $info['channel_id'] = $this->_db->nextId('jonah_channels');
 70:             if ($info['channel_id'] instanceof PEAR_Error) {
 71:                 Horde::logMessage($info['channel_id'], 'ERR');
 72:                 throw new Jonah_Exception($info['channel_id']);
 73:             }
 74:             $sql = 'INSERT INTO jonah_channels' .
 75:                    ' (channel_id, channel_slug, channel_name, channel_type, channel_desc, channel_interval, channel_url, channel_link, channel_page_link, channel_story_url, channel_img)' .
 76:                    ' VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
 77:             $values = array();
 78:         } else {
 79:             $sql = 'UPDATE jonah_channels' .
 80:                    ' SET channel_id = ?, channel_slug = ?, channel_name = ?, channel_type = ?, channel_desc = ?, channel_interval = ?, channel_url = ?, channel_link = ?, channel_page_link = ?, channel_story_url = ?, channel_img = ?' .
 81:                    ' WHERE channel_id = ?';
 82:             $values = array((int)$info['channel_id']);
 83:         }
 84: 
 85:         array_unshift($values,
 86:                       (int)$info['channel_id'],
 87:                       Horde_String::convertCharset($info['channel_slug'], 'UTF-8', $this->_params['charset']),
 88:                       Horde_String::convertCharset($info['channel_name'], 'UTF-8', $this->_params['charset']),
 89:                       (int)$info['channel_type'],
 90:                       isset($info['channel_desc']) ? $info['channel_desc'] : null,
 91:                       isset($info['channel_interval']) ? (int)$info['channel_interval'] : null,
 92:                       isset($info['channel_url']) ? $info['channel_url'] : null,
 93:                       isset($info['channel_link']) ? $info['channel_link'] : null,
 94:                       isset($info['channel_page_link']) ? $info['channel_page_link'] : null,
 95:                       isset($info['channel_story_url']) ? $info['channel_story_url'] : null,
 96:                       isset($info['channel_img']) ? $info['channel_img'] : null);
 97:         Horde::logMessage('SQL Query by Jonah_Driver_sql::saveChannel(): ' . $sql, 'DEBUG');
 98:         $result = $this->_db->query($sql, $values);
 99:         if ($result instanceof PEAR_Error) {
100:             Horde::logMessage($result, 'ERR');
101:             throw new Jonah_Exception($result);
102:         }
103: 
104:         return $info['channel_id'];
105:     }
106: 
107:     /**
108:      * Get a list of stored channels.
109:      *
110:      * @return mixed         An array of channels.
111:      * @throws Jonah_Exception
112:      */
113:     public function getChannels()
114:     {
115:         // @TODO: Remove channel_type filter when tables are updated.
116:         $sql = 'SELECT channel_id, channel_name, channel_type, channel_updated FROM jonah_channels WHERE channel_type = ' . Jonah::INTERNAL_CHANNEL . ' ORDER BY channel_name';
117:         Horde::logMessage('SQL Query by Jonah_Driver_sql::getChannels(): ' . $sql, 'DEBUG');
118:         $result = $this->_db->getAll($sql, DB_FETCHMODE_ASSOC);
119:         if ($result instanceof PEAR_Error) {
120:             Horde::logMessage($result, 'ERR');
121:             throw new Jonah_Exception($result);
122:         }
123:         for ($i = 0; $i < count($result); $i++) {
124:             $result[$i]['channel_name'] = Horde_String::convertCharset($result[$i]['channel_name'], $this->_params['charset'], 'UTF-8');
125:         }
126: 
127:         return $result;
128:     }
129: 
130:     /**
131:      * Retrieve a single channel definition from storage.
132:      *
133:      * @return array  The channel definition array.
134:      * @throws Jonah_Exception
135:      * @throws Horde_Exception_NotFound
136:      */
137:     protected function _getChannel($channel_id)
138:     {
139:         $sql = 'SELECT * FROM jonah_channels WHERE channel_id = ' . (int)$channel_id;
140: 
141:         Horde::logMessage('SQL Query by Jonah_Driver_sql::_getChannel(): ' . $sql, 'DEBUG');
142:         $result = $this->_db->getRow($sql, DB_FETCHMODE_ASSOC);
143:         if ($result instanceof PEAR_Error) {
144:             Horde::logMessage($result, 'ERR');
145:             throw new Jonah_Exception($result);
146:         } elseif (empty($result)) {
147:             throw new Horde_Exception_NotFound(sprintf(_("Channel id \"%s\" not found."), $channel_id));
148:         }
149: 
150:         return $result;
151:     }
152: 
153:     /**
154:      * Update the channel's timestamp
155:      *
156:      * @param integer $channel_id  The channel id.
157:      * @param integer $timestamp   The new timestamp.
158:      *
159:      * @return boolean
160:      * @throws Jonah_Exception
161:      */
162:     protected function _timestampChannel($channel_id, $timestamp)
163:     {
164:         $sql = sprintf('UPDATE jonah_channels SET channel_updated = %s WHERE channel_id = %s',
165:                        (int)$timestamp,
166:                        (int)$channel_id);
167:         Horde::logMessage('SQL Query by Jonah_Driver_sql::_timestampChannel(): ' . $sql, 'DEBUG');
168:         $result = $this->_db->query($sql);
169:         if ($result instanceof PEAR_Error) {
170:             Horde::logMessage($result, 'ERR');
171:             throw new Jonah_Exception($result);
172:         }
173: 
174:         return $result;
175:     }
176: 
177:     /**
178:      * Increment the story's read count.
179:      *
180:      * @param integer $story_id  The story_id to increment.
181:      * @throws Jonah_Exception
182:      */
183:     protected function _readStory($story_id)
184:     {
185:         $sql = 'UPDATE jonah_stories SET story_read = story_read + 1 WHERE story_id = ' . (int)$story_id;
186:         Horde::logMessage('SQL Query by Jonah_Driver_sql::_readStory(): ' . $sql, 'DEBUG');
187:         $result = $this->_db->query($sql);
188:         if ($result instanceof PEAR_Error) {
189:             Horde::logMessage($result, 'ERR');
190:             throw new Jonah_Exception($result);
191:         }
192: 
193:         return $result;
194:     }
195: 
196:     /**
197:      * Remove a channel from storage.
198:      *
199:      * @param integer $channel_id  The channel to remove.
200:      *
201:      * @return boolean.
202:      * @throws Jonah_Exception
203:      *
204:      */
205:     protected function _deleteChannel($channel_id)
206:     {
207:         $sql = 'DELETE FROM jonah_channels WHERE channel_id = ?';
208:         $values = array($channel_id);
209: 
210:         Horde::logMessage('SQL Query by Jonah_Driver_sql::deleteChannel(): ' . $sql, 'DEBUG');
211:         $result = $this->_db->query($sql, $values);
212:         if ($result instanceof PEAR_Error) {
213:             Horde::logMessage($result, 'ERR');
214:             throw new Jonah_Exception($result);
215:         }
216: 
217:         return $result;
218:     }
219: 
220:     /**
221:      * Save a story to storage.
222:      *
223:      * @param array &$info  The story info array.
224:      * @throws Jonah_Exception
225:      */
226:     protected function _saveStory(&$info)
227:     {
228:         if (empty($info['id'])) {
229:             $info['id'] = $this->_db->nextId('jonah_stories');
230:             if ($info['id'] instanceof PEAR_Error) {
231:                 Horde::logMessage($info['id'], 'ERR');
232:                 throw new Jonah_Exception($info['id']);
233:             }
234:             $channel = $this->getChannel($info['channel_id']);
235:             $permalink = $this->getStoryLink($channel, $info);
236:             $sql = 'INSERT INTO jonah_stories (story_id, channel_id, story_author, story_title, story_desc, story_body_type, story_body, story_url, story_published, story_updated, story_read, story_permalink) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
237:             $values = array($permalink);
238:         } else {
239:             $sql = 'UPDATE jonah_stories SET story_id = ?, channel_id = ?, story_author = ?, story_title = ?, story_desc = ?, story_body_type = ?, story_body = ?, story_url = ?, story_published = ?, story_updated = ?, story_read = ? WHERE story_id = ?';
240:             $values = array((int)$info['id']);
241:         }
242: 
243:         if (empty($info['read'])) {
244:             $info['read'] = 0;
245:         }
246: 
247:         /* Deal with any tags */
248:         if (!empty($info['tags'])) {
249:             $tags = explode(',', $info['tags']);
250:         } else {
251:             $tags = array();
252:         }
253:         $this->writeTags($info['id'], $info['channel_id'], $tags);
254: 
255:         array_unshift($values,
256:                       (int)$info['id'],
257:                       (int)$info['channel_id'],
258:                       Horde_String::convertCharset($info['author'], 'UTF-8', $this->_params['charset']),
259:                       Horde_String::convertCharset($info['title'], 'UTF-8', $this->_params['charset']),
260:                       Horde_String::convertCharset($info['description'], 'UTF-8', $this->_params['charset']),
261:                       $info['body_type'],
262:                       isset($info['body']) ? Horde_String::convertCharset($info['body'], 'UTF-8', $this->_params['charset']) : null,
263:                       isset($info['url']) ? $info['url'] : null,
264:                       isset($info['published']) ? (int)$info['published'] : null,
265:                       time(),
266:                       (int)$info['read']);
267: 
268:         Horde::logMessage('SQL Query by Jonah_Driver_sql::_saveStory(): ' . $sql, 'DEBUG');
269:         $result = $this->_db->query($sql, $values);
270:         if ($result instanceof PEAR_Error) {
271:             Horde::logMessage($result, 'ERR');
272:             throw new Jonah_Exception($result);
273:         }
274:         $this->_timestampChannel($info['id'], time());
275: 
276:         return true;
277:     }
278: 
279:     /**
280:      * Converts the text fields of a story from the backend charset to the
281:      * output charset.
282:      *
283:      * @param array $story  A story hash.
284:      *
285:      * @return array  The converted hash.
286:      */
287:     protected function _convertFromBackend($story)
288:     {
289:         $story['title'] = Horde_String::convertCharset($story['title'], $this->_params['charset'], 'UTF-8');
290:         $story['description'] = Horde_String::convertCharset($story['description'], $this->_params['charset'], 'UTF-8');
291:         if (isset($story['body'])) {
292:             $story['body'] = Horde_String::convertCharset($story['body'], $this->_params['charset'], 'UTF-8');
293:         }
294:         if (isset($story['tags'])) {
295:             $story['tags'] = Horde_String::convertCharset($story['tags'], $this->_params['charset'], 'UTF-8');
296:         }
297: 
298:         return $story;
299:     }
300: 
301:     /**
302:      * Look up a channel ID by its name
303:      *
304:      * @param string $channel
305:      *
306:      * @return int Channel ID
307:      */
308:     public function getChannelId($channel)
309:     {
310:         $sql = 'SELECT channel_id FROM jonah_channels WHERE channel_slug = ?';
311:         $values = array($channel);
312:         $result = $this->_db->getOne($sql, $values);
313:         if ($result instanceof PEAR_Error) {
314:             throw new Jonah_Exception($result);
315:         }
316: 
317:         return $result;
318:     }
319: 
320:     /**
321:      * Returns the total number of stories in the specified channel.
322:      *
323:      * @param integer $channel_id  The Channel Id
324:      *
325:      * @return integer  The count
326:      */
327:     public function getStoryCount($channel_id)
328:     {
329:         $sql = 'SELECT count(*) FROM jonah_stories WHERE channel_id = ?';
330:         $result = $this->_db->getOne($sql, $channel_id);
331:         if ($result instanceof PEAR_Error) {
332:             throw new Jonah_Exception($result);
333:         }
334: 
335:         return (int)$result;
336:     }
337: 
338:     /**
339:      * Returns a list of stories from the storage backend filtered by
340:      * arbitrary criteria.
341:      * NOTE: $criteria['channel_id'] MUST be set for this method to work.
342:      *
343:      * @param array $criteria
344:      *
345:      * @return array
346:      *
347:      * @see Jonah_Driver#getStories
348:      */
349:     protected function _getStories($criteria, $order = Jonah::ORDER_PUBLISHED)
350:     {
351:         $sql = 'SELECT DISTINCT(stories.story_id) AS id, ' .
352:            'stories.channel_id, ' .
353:            'stories.story_author AS author, ' .
354:            'stories.story_title AS title, ' .
355:            'stories.story_desc AS description, ' .
356:            'stories.story_body_type AS body_type, ' .
357:            'stories.story_body AS body, ' .
358:            'stories.story_url AS url, ' .
359:            'stories.story_permalink AS permalink, ' .
360:            'stories.story_published AS published, ' .
361:            'stories.story_updated AS updated, ' .
362:            'stories.story_read AS readcount ' .
363:            'FROM jonah_stories AS stories ' .
364:            'LEFT JOIN jonah_stories_tags tags ON (stories.story_id = tags.story_id) ' .
365:            'WHERE stories.channel_id=?';
366: 
367:         $values = array($criteria['channel_id']);
368: 
369:         // Apply date filtering
370:         if (isset($criteria['updated-min'])) {
371:             $sql .= ' AND story_updated >= ?';
372:             $values[] = $criteria['updated-min']->timestamp();
373:         }
374:         if (isset($criteria['updated-max'])) {
375:             $sql .= ' AND story_updated <= ?';
376:             $values[] = $criteria['updated-max']->timestamp();
377:         }
378:         if (isset($criteria['published-min'])) {
379:             $sql .= ' AND story_published >= ?';
380:             $values[] = $criteria['published-min']->timestamp();
381:         }
382:         if (isset($criteria['published-max'])) {
383:             $sql .= ' AND story_published <= ?';
384:             $values[] = $criteria['published-max']->timestamp();
385:         }
386:         if (isset($criteria['published'])) {
387:             $sql .= ' AND story_published IS NOT NULL';
388:         }
389: 
390:         // Apply tag filtering
391:         if (isset($criteria['tags'])) {
392:             $sql .= ' AND (';
393:             $multiple = false;
394:             foreach ($criteria['tags'] as $tag) {
395:                 if (!empty($criteria['tagIDs'][$tag])) {
396:                     if ($multiple) {
397:                         $sql .= ' OR ';
398:                     }
399:                     $sql .= 'tags.tag_id = ?';
400:                     $values[] = $criteria['tagIDs'][$tag];
401:                     $multiple = true;
402:                 }
403:             }
404:             $sql .= ')';
405:         }
406: 
407:         if (isset($criteria['alltags'])) {
408:             $sql .= ' AND (';
409:             $multiple = false;
410:             foreach ($criteria['alltags'] as $tag) {
411:                 if ($multiple) {
412:                     $sql .= ' AND ';
413:                 }
414:                 $sql .= 'tags.tag_id = ?';
415:                 $values[] = $criteria['tagIDs'][$tag];
416:                 $multiple = true;
417:             }
418:             $sql .= ')';
419:         }
420: 
421:         // Filter by story author
422:         if (isset($criteria['author'])) {
423:             $sql .= ' AND stories.story_author = ?';
424:             $values[] = $criteria['author'];
425:         }
426: 
427:         // Filter stories by keyword
428:         if (isset($criteria['keywords'])) {
429:             foreach ($criteria['keywords'] as $keyword) {
430:                 $sql .= ' AND stories.story_body LIKE ?';
431:                 $values[] = '%' . $keyword . '%';
432:             }
433:         }
434:         if (isset($criteria['notkeywords'])) {
435:             foreach ($criteria['notkeywords'] as $keyword) {
436:                 $sql .= ' AND stories.story_body NOT LIKE ?';
437:                 $values[] = '%' . $keyword . '%';
438:             }
439:         }
440: 
441:         switch ($order) {
442:         case Jonah::ORDER_PUBLISHED:
443:             $sql .= ' ORDER BY story_published DESC';
444:             break;
445:         case Jonah::ORDER_READ:
446:             $sql .= ' ORDER BY story_read DESC';
447:             break;
448:         case Jonah::ORDER_COMMENTS:
449:             //@TODO
450:             break;
451:         }
452:         $limit = 0;
453:         if (isset($criteria['limit'])) {
454:             $limit = $criteria['limit'];
455:         }
456:         if (isset($criteria['startnumber']) && isset($criteria['endnumber'])) {
457:             $limit = min($criteria['endnumber'] - $criteria['startnumber'], $criteria['limit']);
458:         }
459:         $start = isset($criteria['startnumber']) ? $criteria['startnumber'] : 0;
460:         Horde::logMessage('SQL Query by Jonah_Driver_sql::_getStories(): ' . $sql, 'DEBUG');
461:         if ($limit || $start != 0) {
462:             $results = array();
463:             $rows = $this->_db->limitQuery($sql, $start, $limit, $values);
464:             while ($rows->fetchInto($row, DB_FETCHMODE_ASSOC)) {
465:                 $results[] = $row;
466:             }
467:         } else {
468:             $results = $this->_db->getAll($sql, $values, DB_FETCHMODE_ASSOC);
469:         }
470:         if ($results instanceof PEAR_Error) {
471:             throw new Jonah_Exception($results);
472:         }
473:         foreach ($results as &$row) {
474:             $row['tags'] = $this->readTags($row['id']);
475:         }
476:         return $results;
477:     }
478: 
479:     /**
480:      * Obtain a channel id from a slug
481:      *
482:      * @param string $slug  The slug to search for.
483:      *
484:      * @return integer  The channel id.
485:      */
486:     protected function _getIdBySlug($slug)
487:     {
488:         // @TODO
489:         throw new Jonah_Exception('Not implemented yet.');
490:     }
491: 
492:     /**
493:      * Retrieve a story from storage.
494:      *
495:      * @param integer $story_id  They story id.
496:      * @param boolean $read      Increment the read counter?
497:      *
498:      * @return The story array.
499:      * @throws Horde_Exception_NotFound
500:      * @throws Jonah_Exception
501:      *
502:      */
503:     protected function _getStory($story_id, $read = false)
504:     {
505:         $sql = 'SELECT stories.story_id as id, ' .
506:            'stories.channel_id, ' .
507:            'stories.story_author AS author, ' .
508:            'stories.story_title AS title, ' .
509:            'stories.story_desc AS description, ' .
510:            'stories.story_body_type AS body_type, ' .
511:            'stories.story_body AS body, ' .
512:            'stories.story_url AS url, ' .
513:            'stories.story_permalink AS permalink, ' .
514:            'stories.story_published AS published, ' .
515:            'stories.story_updated AS updated, ' .
516:            'stories.story_read AS readcount ' .
517:            'FROM jonah_stories AS stories WHERE stories.story_id=?';
518: 
519:         $values = array((int)$story_id);
520: 
521:         Horde::logMessage('SQL Query by Jonah_Driver_sql::_getStory(): ' . $sql, 'DEBUG');
522:         $result = $this->_db->getRow($sql, $values, DB_FETCHMODE_ASSOC);
523:         if ($result instanceof PEAR_Error) {
524:             Horde::logMessage($result, 'ERR');
525:             throw new Jonah_Exception($result);
526:         } elseif (empty($result)) {
527:             throw new Horde_Exception_NotFound(sprintf(_("Story id \"%s\" not found."), $story_id));
528:         }
529:         $result['tags'] = $this->readTags($story_id);
530:         $result = $this->_convertFromBackend($result);
531:         if ($read) {
532:             $this->_readStory($story_id);
533:         }
534: 
535:         return $result;
536:     }
537: 
538:     /**
539:      * Adds a missing permalink to a story.
540:      *
541:      * @param array $story  A story hash.
542:      * @throws Jonah_Exception
543:      */
544:     protected function _addPermalink(&$story)
545:     {
546:         $channel = $this->getChannel($story['channel_id']);
547:         $sql = 'UPDATE jonah_stories SET story_permalink = ? WHERE story_id = ?';
548:         $values = array($this->getStoryLink($channel, $story), $story['id']);
549:         Horde::logMessage('SQL Query by Jonah_Driver_sql::_addPermalink(): ' . $sql, 'DEBUG');
550:         $result = $this->_db->query($sql, $values);
551:         if ($result instanceof PEAR_Error) {
552:             throw new Jonah_Exception($result);
553:         }
554:         $story['permalink'] = $values[0];
555:     }
556: 
557:     /**
558:      * Gets the latest released story from a given internal channel
559:      *
560:      * @param int $channel_id  The channel id.
561:      *
562:      * @return int  The story id.
563:      * @throws Jonah_Exception
564:      * @throws Horde_Exception_NotFound
565:      */
566:     public function getLatestStoryId($channel_id)
567:     {
568:         $sql = 'SELECT story_id FROM jonah_stories' .
569:                ' WHERE channel_id = ? AND story_published <= ?' .
570:                ' ORDER BY story_updated DESC';
571:         $values = array((int)$channel_id, time());
572: 
573:         Horde::logMessage('SQL Query by Jonah_Driver_sql::getLatestStoryId(): ' . $sql, 'DEBUG');
574:         $result = $this->_db->getRow($sql, $values, DB_FETCHMODE_ASSOC);
575:         if ($result instanceof PEAR_Error) {
576:             Horde::logMessage($result, 'ERR');
577:             throw new Jonah_Exception($result);
578:         } elseif (empty($result)) {
579:             return Horde_Exception_NotFound(sprintf(_("Channel \"%s\" not found."), $channel_id));
580:         }
581: 
582:         return $result['story_id'];
583:     }
584: 
585:     /**
586:      */
587:     public function deleteStory($channel_id, $story_id)
588:     {
589:         $sql = 'DELETE FROM jonah_stories' .
590:                ' WHERE channel_id = ? AND story_id = ?';
591:         $values = array((int)$channel_id, (int)$story_id);
592: 
593:         Horde::logMessage('SQL Query by Jonah_Driver_sql::deleteStory(): ' . $sql, 'DEBUG');
594:         $result = $this->_db->query($sql, $values);
595:         if ($result instanceof PEAR_Error) {
596:             Horde::logMessage($result->getMessage(), 'ERR');
597:                 throw new Jonah_Exception($result);
598:         }
599: 
600:         $sql = 'DELETE FROM jonah_stories_tags ' .
601:                'WHERE channel_id = ? AND story_id = ?';
602:         $result = $this->_db->query($sql, $values);
603:         if ($result instanceof PEAR_Error) {
604:             Horde::logMessage($result->getMessage(), 'ERR');
605:             throw new Jonah_Exception($result);
606:         }
607: 
608:         return true;
609:     }
610: 
611:     /**
612:      * Write out the tags for a specific resource.
613:      *
614:      * @param int    $resource_id    The story we are tagging.
615:      * @param int    $channel_id     The channel id for the story we are tagging
616:      * @param array  $tags           An array of tags.
617:      *
618:      * @TODO: Move this to a tagger class that uses Content_Tagger
619:      * @return boolean
620:      * @throws Jonah_Exception
621:      */
622:     public function writeTags($resource_id, $channel_id, $tags)
623:     {
624:         global $conf;
625: 
626:         // First, make sure all tag names exist in the DB.
627:         $tagkeys = array();
628:         $insert = $this->_db->prepare('INSERT INTO jonah_tags (tag_id, tag_name) VALUES(?, ?)');
629:         $query = $this->_db->prepare('SELECT tag_id FROM jonah_tags WHERE tag_name = ?');
630:         foreach ($tags as $tag) {
631:             $tag = Horde_String::lower(trim($tag));
632:             $results = $this->_db->execute($query, $this->_db->escapeSimple($tag));
633:             if ($results instanceof PEAR_Error) {
634:                 throw new Jonah_Exception($results);
635:             } elseif ($results->numRows() == 0) {
636:                 $id = $this->_db->nextId('jonah_tags');
637:                 $result = $this->_db->execute($insert, array($id, $tag));
638:                 $tagkeys[] = $id;
639:             } else {
640:                 $row = $results->fetchRow(DB_FETCHMODE_ASSOC);
641:                 $tagkeys[] = $row['tag_id'];
642:             }
643:         }
644: 
645:         // Free our resources.
646:         $this->_db->freePrepared($insert, true);
647:         $this->_db->freePrepared($query, true);
648: 
649:         $sql = 'DELETE FROM jonah_stories_tags WHERE story_id = ' . (int)$resource_id;
650:         $query = $this->_db->prepare('INSERT INTO jonah_stories_tags (story_id, channel_id, tag_id) VALUES(?, ?, ?)');
651:         Horde::logMessage('SQL query by Jonah_Driver_sql::writeTags: ' . $sql, 'DEBUG');
652:         $this->_db->query($sql);
653:         foreach ($tagkeys as $key) {
654:             $this->_db->execute($query, array($resource_id, $channel_id, $key));
655:         }
656:         $this->_db->freePrepared($query, true);
657: 
658:         /* @TODO We should clear at least any of our cached counts */
659:         return true;
660:     }
661: 
662:     /**
663:      * Retrieve the tags for a specified resource.
664:      *
665:      * @TODO: Move this to a tagger class that uses content_tagger
666:      *
667:      * @param integer     $resource_id    The resource to get tags for.
668:      *
669:      * @return array  An array of tags
670:      */
671:     public function readTags($resource_id)
672:     {
673:         $sql = 'SELECT jonah_tags.tag_id, tag_name FROM jonah_tags INNER JOIN jonah_stories_tags ON jonah_stories_tags.tag_id = jonah_tags.tag_id WHERE jonah_stories_tags.story_id = ?';
674:         Horde::logMessage('SQL query by Jonah_Driver_sql::readTags ' . $sql, 'DEBUG');
675:        $tags = $this->_db->getAssoc($sql, false, array($resource_id), false);
676: 
677:        return $tags;
678:     }
679: 
680:     /**
681:      * Retrieve the list of used tag_names, tag_ids and the total number
682:      * of resources that are linked to that tag.
683:      *
684:      * @param array $tags  An optional array of tag_ids. If omitted, all tags
685:      *                     will be included.
686:      *
687:      * @param array $channel_id  An optional array of channel_ids.
688:      *
689:      * @return array  An array containing tag_name, and total
690:      */
691:     public function listTagInfo($tags = array(), $channel_id = null)
692:     {
693:         if (!is_array($channel_id) && is_numeric($channel_id)) {
694:             $channel_id = array($channel_id);
695:         }
696:         $cache = $GLOBALS['injector']->getInstance('Horde_Cache');
697:         $cache_key = 'jonah_tags_' . md5(serialize($tags) . md5(serialize($channel_id)));
698:         $cache_value = $cache->get($cache_key, $GLOBALS['conf']['cache']['default_lifetime']);
699:         if ($cache_value) {
700:             return unserialize($cache_value);
701:         }
702: 
703:         $haveWhere = false;
704:         $sql = 'SELECT tn.tag_id, tag_name, COUNT(tag_name) total FROM jonah_tags as tn INNER JOIN jonah_stories_tags as t ON t.tag_id = tn.tag_id';
705:         if (count($tags)) {
706:             $sql .= ' WHERE tn.tag_id IN (' . implode(',', $tags) . ')';
707:             $haveWhere = true;
708:         }
709:         if (!is_null($channel_id)) {
710:             if (!$haveWhere) {
711:                 $sql .= ' WHERE';
712:             } else {
713:                 $sql .= ' AND';
714:             }
715:             $channels = array();
716:             foreach ($channel_id as $cid) {
717:                 $c = $this->_getChannel($cid);
718:             }
719:             $channel_id = array_merge($channel_id, $channels);
720:             $sql .= ' t.channel_id IN (' . implode(', ', $channel_id) . ')';
721:         }
722:         $sql .= ' GROUP BY tn.tag_id, tag_name ORDER BY total DESC;';
723:         $results = $this->_db->getAssoc($sql,true, array(), DB_FETCHMODE_ASSOC, false);
724:         if ($results instanceof PEAR_Error) {
725:             throw new Jonah_Exception($results);
726:         }
727:         $cache->set($cache_key, serialize($results));
728: 
729:         return $results;
730:     }
731: 
732:     /**
733:      * Search for resources matching the specified criteria
734:      *
735:      * @param array  $ids          An array of tag_ids to search for. Note that
736:      *                             these are AND'd together.
737:      * @param integer $max         The maximum number of stories to get. If
738:      *                             null, all stories will be returned.
739:      * @param integer $from        The number of the story to start with.
740:      * @param array $channel       Limit the result set to resources
741:      *                             present in these channels
742:      * @param integer $order       How to order the results for internal
743:      *                             channels. Possible values are the
744:      *                             JONAH_ORDER_* constants.
745:      *
746:      * @return mixed  Array of stories
747:      */
748:     public function searchTagsById($ids, $max = 10, $from = 0, $channel_id = array(), $order = Jonah::ORDER_PUBLISHED)
749:     {
750:         if (!is_array($ids) || !count($ids)) {
751:             $stories[] = array();
752:         } else {
753:             $stories = array();
754:             $sql = 'SELECT DISTINCT s.story_id, s.channel_id FROM jonah_stories'
755:                    . ' s, jonah_stories_tags t';
756:             for ($i = 0; $i < count($ids); $i++) {
757:                 $sql .= ', jonah_stories_tags t' . $i;
758:             }
759:             $sql .= ' WHERE s.story_id = t.story_id';
760:             for ($i = 0 ; $i < count($ids); $i++) {
761:                 $sql .= ' AND t' . $i . '.tag_id = ' . $ids[$i] . ' AND t'
762:                         . $i . '.story_id = t.story_id';
763:             }
764: 
765:             /* Limit to particular channels if requested */
766:             if (count($channel_id) > 0) {
767:                 // Have to find out if we are a composite channel or not.
768:                 $channels = array();
769:                 foreach ($channel_id as $cid) {
770:                     $c = $this->_getChannel($cid);
771:                 }
772:                 $channels = array_merge($channel_id, $channels);
773:                 $timestamp = time();
774:                 $sql .= ' AND t.channel_id IN (' . implode(', ', $channels)
775:                         . ') AND s.story_published IS NOT NULL AND '
776:                         . 's.story_published < ' . $timestamp;
777:             }
778: 
779:             switch ($order) {
780:             case Jonah::ORDER_PUBLISHED:
781:                 $sql .= ' ORDER BY story_published DESC';
782:                 break;
783:             case Jonah::ORDER_READ:
784:                 $sql .= ' ORDER BY story_read DESC';
785:                 break;
786:             case Jonah::ORDER_COMMENTS:
787:                 //@TODO
788:                 break;
789:             }
790: 
791:             /* Instantiate the channel object outside the loop if we
792:              * are only limiting to one channel. */
793:             if (count($channel_id) == 1) {
794:                 $channel = $this->getChannel($channel_id[0]);
795:             }
796:             Horde::logMessage('SQL query by Jonah_Driver_sql::searchTags: ' . $sql, 'DEBUG');
797:             $results = $this->_db->limitQuery($sql, $from, $max);
798:             if ($results instanceof PEAR_Error) {
799:                 throw new Jonah_Exception($results);
800:             }
801: 
802:             for ($i = 0; $i < $results->numRows(); $i++) {
803:                 $row = $results->fetchRow();
804:                 $story = $this->_getStory($row[0], false);
805:                 if (count($channel_id > 1)) {
806:                     // Make sure we get the correct channel info for composites
807:                     if (!empty($cchannels[$story['channel_id']])) {
808:                         $channel = $this->getChannel($cchannels[$story['channel_id']]);
809:                     } else {
810:                         $channel = $this->getChannel($story['channel_id']);
811:                     }
812:                 }
813: 
814:                 /* Format story link. */
815:                 $story['link'] = $this->getStoryLink($channel, $story);
816:                 $story = array_merge($story, $channel);
817: 
818:                 /* Format dates. */
819:                 $date_format = $GLOBALS['prefs']->getValue('date_format');
820:                 $story['updated_date'] = strftime($date_format, $story['updated']);
821:                 if (!empty($story['published'])) {
822:                     $story['published_date'] = strftime($date_format, $story['published']);
823:                 }
824: 
825:                 $stories[] = $story;
826:             }
827:         }
828: 
829:         return $stories;
830:     }
831: 
832:     /**
833:      * Search for articles matching specific tag name(s).
834:      *
835:      * @see Jonah_Driver_sql::searchTagsById()
836:      */
837:     public function searchTags($names, $max = 10, $from = 0, $channel_id = array(),
838:                         $order = Jonah::ORDER_PUBLISHED)
839:     {
840:         $ids = $this->getTagIds($names);
841:         if ($ids instanceof PEAR_Error) {
842:             throw new Jonah_Exception($ids);
843:         }
844: 
845:         return $this->searchTagsById(array_values($ids), $max, $from, $channel_id, $order);
846:     }
847: 
848: 
849:     /**
850:      * Return a set of tag names given the tag_ids.
851:      *
852:      * @param array $ids  An array of tag_ids to get names for.
853:      *
854:      * @return mixed  An array of tag names | PEAR_Error.
855:      */
856:     public function getTagNames($ids)
857:     {
858:         $sql = 'SELECT t.tag_name FROM jonah_tags as t WHERE t.tag_id IN(' . str_repeat('?,', count($ids) - 1) . '?)';
859:         $tags = $this->_db->getCol($sql, 0, $ids);
860:         if ($tags instanceof PEAR_Error) {
861:             throw new Jonah_Exception($tags);
862:         }
863: 
864:         return $tags;
865:     }
866: 
867:     /**
868:      * Return a set of tag_ids, given the tag name
869:      *
870:      * @param array $names  An array of names to search for
871:      *
872:      * @return mixed  An array of tag_name => tag_ids | PEAR_Error
873:      */
874:     public function getTagIds($names)
875:     {
876:         $sql = 'SELECT t.tag_name, t.tag_id FROM jonah_tags as t WHERE t.tag_name IN(' . str_repeat('?,', count($names) - 1) . '?)';
877:         $tags = $this->_db->getAssoc($sql, false, $names);
878:         if ($tags instanceof PEAR_Error) {
879:             throw new Jonah_Exception($tags);
880:         }
881: 
882:         return $tags;
883:     }
884: 
885:     /**
886:      * Attempts to open a persistent connection to the SQL server.
887:      *
888:      * @TODO: This class needs to be refactored to use Horde_Db
889:      *
890:      * @return boolean    True on success.
891:      * @throws Jonah_Exception
892:      */
893:     protected function _connect()
894:     {
895:         if ($this->_connected) {
896:             return true;
897:         }
898: 
899:         try {
900:             $this->_db = $GLOBALS['injector']->getInstance('Horde_Core_Factory_DbPear')->create('rw', 'jonah', 'news');
901:         } catch (Horde_Exception $e) {
902:             return PEAR::raiseError($e->getMessage());
903:         }
904:         $this->_connected = true;
905: 
906:         return true;
907:     }
908: 
909: }
910: