1: <?php
  2: /**
  3:  * Folks storage implementation for PHP's PEAR database abstraction layer.
  4:  *
  5:  * Required values for $params:<pre>
  6:  *      'phptype'       The database type (e.g. 'pgsql', 'mysql', etc.).
  7:  *      'table'         The name of the foo table in 'database'.
  8:  *      'charset'       The database's internal charset.</pre>
  9:  *
 10:  * Required by some database implementations:<pre>
 11:  *      'database'      The name of the database.
 12:  *      'hostspec'      The hostname of the database server.
 13:  *      'protocol'      The communication protocol ('tcp', 'unix', etc.).
 14:  *      'username'      The username with which to connect to the database.
 15:  *      'password'      The password associated with 'username'.
 16:  *      'options'       Additional options to pass to the database.
 17:  *      'tty'           The TTY on which to connect to the database.
 18:  *      'port'          The port on which to connect to the database.</pre>
 19:  *
 20:  * The table structure can be created by the scripts/sql/folks_foo.sql
 21:  * script.
 22:  *
 23:  * Copyright 2008-2012 Horde LLC (http://www.horde.org/)
 24:  *
 25:  * See the enclosed file COPYING for license information (GPL). If you
 26:  * did not receive this file, see http://www.horde.org/licenses/gpl.
 27:  *
 28:  * @author  Duck <duck@obala.net>
 29:  * @package Folks
 30:  */
 31: class Folks_Driver_sql extends Folks_Driver {
 32: 
 33:     /**
 34:      * Handle for the current database connection.
 35:      *
 36:      * @var DB
 37:      */
 38:     private $_db;
 39: 
 40:     /**
 41:      * Handle for the current database connection, used for writing. Defaults
 42:      * to the same handle as $_db if a separate write database is not required.
 43:      *
 44:      * @var DB
 45:      */
 46:     private $_write_db;
 47: 
 48:     /**
 49:      * Constructs a new SQL storage object.
 50:      *
 51:      * @param array $params  A hash containing connection parameters.
 52:      */
 53:     public function __construct($params = array())
 54:     {
 55:         $this->_params = $params;
 56:         $this->_connect();
 57:     }
 58: 
 59:     /**
 60:      * Get usersnames online
 61:      */
 62:     protected function _getOnlineUsers()
 63:     {
 64:         return $this->_db->getCol('SELECT user_uid FROM ' . $this->_params['online'] . ' WHERE user_uid <> ""');
 65:     }
 66: 
 67:     /**
 68:      * Get last visitors
 69:      *
 70:      * @param integer $limit   Username to check
 71:      *
 72:      * @return array  users
 73:      */
 74:     protected function _getRecentVisitors($limit = 10)
 75:     {
 76:         $sql = 'SELECT user_uid FROM ' . $this->_params['online']
 77:             . ' WHERE user_uid <> "" AND user_uid <> "0" '
 78:             . ' ORDER BY time_last_click DESC';
 79: 
 80:         $result = $this->_db->limitQuery($sql, 0, $limit);
 81:         $value = $result->fetchRow(DB_FETCHMODE_ORDERED);
 82:         return $value[0];
 83:     }
 84: 
 85:     /**
 86:      * Get random users
 87:      *
 88:      * @param integer $limit   Username to check
 89:      * @param boolean $online   User is online?
 90:      *
 91:      * @return array  users
 92:      */
 93:     protected function _getRandomUsers($limit = 10, $online = false)
 94:     {
 95:         if ($online) {
 96:             $sql = 'SELECT u.user_uid FROM ' . $this->_params['table'] . ' u, .' . $this->_params['online'] . ' o '
 97:                 . ' WHERE u.user_picture = 1 AND o.user_uid = u.user_uid ORDER BY RAND()';
 98:         } else {
 99:             $sql = 'SELECT user_uid FROM ' . $this->_params['table']
100:                 . ' WHERE user_picture = 1 ORDER BY RAND()';
101:         }
102:         
103:         $result = $this->_db->limitQuery($sql, 0, $limit);
104:         $value = $result->fetchRow(DB_FETCHMODE_ORDERED);
105: 
106:         return $value[0];
107:     }
108: 
109:     /**
110:      * Get usersnames online
111:      */
112:     protected function _updateOnlineStatus()
113:     {
114:         $query = 'REPLACE INTO ' . $this->_params['online'] . ' (user_uid, ip_address, time_last_click) VALUES (?, ?, ?)';
115:         return $this->_write_db->query($query, array($GLOBALS['registry']->getAuth(), $_SERVER['REMOTE_ADDR'], $_SERVER['REQUEST_TIME']));
116:     }
117: 
118:     /**
119:      * Delete users online
120:      */
121:     protected function _deleteOnlineStatus($to)
122:     {
123:         $query = 'DELETE FROM ' . $this->_params['online'] . ' WHERE time_last_click < ?';
124:         return $this->_write_db->query($query, array($to));
125:     }
126: 
127:     /**
128:      * Remove user if is online
129:      */
130:     public function deleteOnlineUser($user)
131:     {
132:         $query = 'DELETE FROM ' . $this->_params['online'] . ' WHERE user_uid = ?';
133:         return $this->_write_db->query($query, array($user));
134:     }
135: 
136:     /**
137:      * Get users by attributes
138:      */
139:     public function getUsers($criteria = array(), $from = 0, $count = 0)
140:     {
141:         $binds = $this->_buildWhere($criteria, false);
142: 
143:         if (!isset($criteria['sort_by'])) {
144:             $criteria['sort_by'] = $GLOBALS['prefs']->getValue('sort_by');
145:         }
146:         if (isset($criteria['sort_dir'])) {
147:             $criteria['sort_dir'] = $criteria['sort_dir'] ? 'ASC' : 'DESC';
148:         } else {
149:             $criteria['sort_dir'] = $GLOBALS['prefs']->getValue('sort_dir') ? 'ASC' : 'DESC';
150:         }
151: 
152:         $binds[0] = 'SELECT u.* ' . $binds[0]
153:                     . ' ORDER BY u.' . $criteria['sort_by']
154:                     . ' ' . $criteria['sort_dir'];
155: 
156:         if ($count) {
157:             $binds[0] = $this->_db->modifyLimitQuery($binds[0], $from, $count);
158:         }
159: 
160:         return $this->_db->getAssoc($binds[0], false, $binds[1], DB_FETCHMODE_ASSOC);
161:     }
162: 
163:     /**
164:      * Count users by attributes
165:      */
166:     public function countUsers($criteria = array())
167:     {
168:         $binds = $this->_buildWhere($criteria, true);
169:         $binds[0] = 'SELECT COUNT(*) ' . $binds[0];
170: 
171:         return $this->_db->getOne($binds[0], $binds[1]);
172:     }
173: 
174:     /**
175:      * Build attributes query
176:      *
177:      * @return array  An array containing sql statement and parameters
178:      */
179:     private function _buildWhere($criteria = array())
180:     {
181:         static $parts;
182: 
183:         $id = serialize($criteria);
184:         if (isset($parts[$id])) {
185:             return $parts[$id];
186:         }
187: 
188:         if (empty($criteria)) {
189:             $parts[$id] = array(' FROM ' . $this->_params['table'] . ' u', array());
190:             return $parts[$id];
191:         }
192: 
193:         $tables = $this->_params['table'] . ' u ';
194:         $params = array();
195:         $where = '';
196: 
197:         // WORD
198:         if (!empty($criteria['word']) && !empty($criteria['by'])) {
199:             foreach ($criteria['by'] as $key) {
200:                 $where .= ' AND u.user_' . $key . ' LIKE ?';
201:                 $params[] = '%' . $criteria['word'] . '%';
202:             }
203:         }
204: 
205:         // EMAIL
206:         if (!empty($criteria['email'])) {
207:             $where .= ' AND u.user_email = ?';
208:             $params[] = $criteria['email'];
209:         }
210: 
211:         // AGES
212:         if (isset($criteria['birthday'])) {
213:             if (is_array($criteria['birthday'])) {
214:                 $where .= ' AND DAYOFYEAR(u.user_birthday) >= ? AND DAYOFYEAR(u.user_birthday)  <= ?';
215:                 $params[] = date('z', $criteria['birthday']['from']) + 1;
216:                 $params[] = date('z', $criteria['birthday']['to']) + 1;
217:             } else {
218:                 $where .= ' AND u.user_birthday LIKE ?';
219:                 $params[] = '%' . $criteria['birthday'];
220:             }
221:         }
222: 
223:         if (isset($criteria['age_from'])) {
224:             $where .= ' AND u.user_birthday <= ?';
225:             $params[] = (date('Y') - $criteria['age_from']) . '-' . date('m-d');
226:         }
227: 
228:         if (isset($criteria['age_to'])) {
229:             $where .= ' AND u.user_birthday >= ?';
230:             $params[] = (date('Y') - $criteria['age_to']) . '-' . date('m-d');
231:         }
232: 
233:         // GOES OUT
234:         if (isset($criteria['out'])) {
235:             $tables .= ', ' . $this->_params['out'] . ' g ';
236:             $where .= ' AND u.user_uid = g.user_uid AND g.out_from >= ? AND g.out_to <= ? ';
237:             $params[] = $criteria['out']['from'];
238:             $params[] = $criteria['out']['to'];
239:         }
240: 
241:         // COUNTERS
242:         if (isset($criteria['has'])) {
243:             foreach ($criteria['has'] as $key) {
244:                 if ($key == 'picture') {
245:                     $where .= ' AND u.user_picture > 0';
246:                 } else {
247:                     $where .= ' AND u.count_' . $key . ' > 0';
248:                 }
249:             }
250:         }
251: 
252:         // ONLINE
253:         if (isset($criteria['online'])) {
254:             $tables .= ', ' . $this->_params['online'] . ' o ';
255:             $where .= ' AND o.user_uid <> "" AND o.user_uid = u.user_uid';
256:         }
257: 
258:         // Gander
259:         if (isset($criteria['user_gender'])) {
260:             $where .= ' AND user_gender = ? ';
261:             $params[] = (int)$criteria['user_gender'];
262:         }
263: 
264:         // City
265:         if (isset($criteria['user_city'])) {
266:             $where .= ' AND user_city LIKE ? ';
267:             $params[] = '%' . $criteria['user_city'] . '%';
268:         }
269: 
270:         $sql = ' FROM ' . $tables;
271:         if (!empty($where)) {
272:             $sql .= ' WHERE ' . substr($where, 4);
273:         }
274: 
275:         $parts[$id] = array($sql, $params);
276: 
277:         return $parts[$id];
278:     }
279: 
280:     /**
281:      * Formats a password using the current encryption.
282:      *
283:      * @param string $user      User we are getting password for
284:      *
285:      * @return string  The encrypted password.
286:      */
287:     protected function _getCryptedPassword($user)
288:     {
289:         $query = 'SELECT user_password FROM ' . $this->_params['table'] . ' WHERE user_uid = ?';
290:         return $this->_db->getOne($query, array($user));
291:     }
292: 
293:     /**
294:      * Get user profile
295:      *
296:      * @param string $user   Username
297:      */
298:     protected function _getProfile($user)
299:     {
300:         $query = 'SELECT user_email, user_status, user_url, user_description, user_comments, '
301:                 . ' user_city, user_country, user_gender, user_birthday, user_video, '
302:                 . ' last_online, last_online_on, activity_log, user_vacation, activity, popularity, '
303:                 . ' user_picture, count_classifieds, count_news, count_videos, '
304:                 . ' count_attendances, count_wishes, count_galleries, count_blogs '
305:                 . ' FROM  ' . $this->_params['table'] . ' WHERE user_uid = ?';
306: 
307:         $result = $this->_db->getRow($query, array(strval($user)), DB_FETCHMODE_ASSOC);
308:         if ($result instanceof PEAR_Error) {
309:             return $result;
310:         } elseif (empty($result)) {
311:             return PEAR::raiseError(sprintf(_("User \"%s\" does not exists."), $user));
312:         }
313: 
314:         return $result;
315:     }
316: 
317:     /**
318:      * Save basic user profile
319:      *
320:      * @param array  $data   A hash containing profile data
321:      * @param string $user   Username
322:      */
323:     protected function _saveProfile($data, $user)
324:     {
325:         if (empty($data['user_picture'])) {
326:             unset($data['user_picture']);
327:         } else {
328:             $image = $this->_saveImage($data['user_picture']['file'], $user);
329:             if ($image instanceof PEAR_Error) {
330:                 return $image;
331:             }
332:             $data['user_picture'] = 1;
333:         }
334: 
335:         $query = 'UPDATE ' . $this->_params['table'] . ' SET '
336:                         . implode(' = ?, ', array_keys($data))
337:                         . ' = ? WHERE user_uid = ?';
338: 
339:         $data[0] = $user;
340: 
341:         return $this->_write_db->query($query, $data);
342:     }
343: 
344:     /**
345:      * Update user comments count
346:      *
347:      * @param string $user   Username
348:      */
349:     public function updateComments($user, $reset = false)
350:     {
351:         $query = 'UPDATE ' . $this->_params['table'] . ' SET count_comments = ';
352:         if ($reset) {
353:             $query .= '0';
354:         } else {
355:             $query .= 'count_comments + 1';
356:         }
357:         $query .= ' WHERE user_uid = ?';
358: 
359:         return $this->_write_db->query($query, array($user));
360:     }
361: 
362:     /**
363:      * Delete user image
364:      *
365:      * @param string $user   Username
366:      */
367:     protected function _deleteImage($user)
368:     {
369:         $query = 'UPDATE ' . $this->_params['table'] . ' SET user_picture = 0 WHERE user_uid = ?';
370:         return $this->_write_db->query($query, array($user));
371:     }
372: 
373:     /**
374:      * Log user view
375:      *
376:      * @param string $user   Username
377:      */
378:     protected function _logView($id)
379:     {
380:         $query = 'REPLACE INTO ' . $this->_params['views'] . ' (view_uid, user_uid, view_time) VALUES (?, ?, ?)';
381:         return $this->_write_db->query($query, array($id, $GLOBALS['registry']->getAuth(), $_SERVER['REQUEST_TIME']));
382:     }
383: 
384:     /**
385:      * Get user groups
386:      *
387:      * @param string $user   Username
388:      */
389:     public function getViews()
390:     {
391:         $query = 'SELECT user_uid FROM ' . $this->_params['views'] . ' WHERE view_uid = ?';
392:         return $this->_db->getCol($query, 0, array($GLOBALS['registry']->getAuth()));
393:     }
394: 
395:    /**
396:     * Check if user exist
397:     *
398:     * @param string $user    Username
399:     *
400:     * @return boolean
401:     */
402:     public function userExists($user)
403:     {
404:         $query = 'SELECT 1 FROM ' . $this->_params['table'] . ' WHERE user_uid = ?';
405:         $result = $this->_db->getOne($query, array($user));
406:         if ($result instanceof PEAR_Error) {
407:             return $result;
408:         }
409: 
410:         return (boolean)$result;
411:     }
412: 
413:     /**
414:      * Adds a set of authentication credentials.
415:      *
416:      * @param string $userId  The userId to add.
417:      * @param array $credentials  The credentials to use.
418:      *
419:      * @return boolean true|PEAR_Error
420:      */
421:     public function addUser($user, $credentials)
422:     {
423:         // password and mail will be added later with the addextra hook
424:         $query = 'INSERT INTO ' . $this->_params['table']
425:                     . ' (user_uid, user_status, user_password, user_email, signup_at, signup_by) '
426:                     . ' VALUES (?, ?, ?, ?, NOW(), ?)';
427:         $params = array($user, 'inactive', $credentials['password'],
428:                         rand() . '@' . $_SERVER['REMOTE_ADDR'],
429:                         $_SERVER['REMOTE_ADDR']);
430: 
431:         return $this->_write_db->query($query, $params);
432:     }
433: 
434:    /**
435:     * Delete user
436:     *
437:     * @param string $user    Username
438:     *
439:     * @return boolean
440:     */
441:     protected function _deleteUser($user)
442:     {
443:         $tables = array($this->_params['table'],
444:                         $this->_params['attributes'],
445:                         $this->_params['friends'],
446:                         $this->_params['testimonials'],
447:                         $this->_params['online'],
448:                         $this->_params['views'],
449:                         $this->_params['out']);
450: 
451:         foreach ($tables as $table) {
452:             $query = 'DELETE FROM ' . $table . ' WHERE user_uid = ?';
453:             $result = $this->_write_db->query($query, array($user));
454:             if ($result instanceof PEAR_Error) {
455:                 return $result;
456:             }
457:         }
458: 
459:         return true;
460:     }
461: 
462:    /**
463:     * Save search criteria
464:     *
465:     * @param string $criteria    Search criteria
466:     * @param string $name    Search name
467:     */
468:     protected function _saveSearch($criteria, $name)
469:     {
470:         $query = 'INSERT INTO ' . $this->_params['search'] . ' (user_uid, search_name, search_criteria) VALUES (?, ?, ?)';
471: 
472:         return $this->_write_db->query($query, array($GLOBALS['registry']->getAuth(), $name, $criteria));
473:     }
474: 
475:    /**
476:     * Get saved search
477:     *
478:     * @return array saved searches
479:     */
480:     protected function _getSavedSearch()
481:     {
482:         $query = 'SELECT search_name FROM ' . $this->_params['search'] . ' WHERE user_uid = ?';
483: 
484:         return $this->_db->getCol($query, 'search_name', $GLOBALS['registry']->getAuth());
485:     }
486: 
487:    /**
488:     * Get saved search criteria
489:     *
490:     * @param string $name    Username
491:     *
492:     * @return array  search criteria
493:     */
494:     protected function _getSearchCriteria($name)
495:     {
496:         $query = 'SELECT search_criteria FROM ' . $this->_params['search'] . ' WHERE user_uid = ? AND search_name = ?';
497: 
498:         return $this->_db->getOne($query, array($GLOBALS['registry']->getAuth(), $name));
499:     }
500: 
501:    /**
502:     * Delete saved search
503:     *
504:     * @param string $name    Username
505:     */
506:     protected function _deleteSavedSearch($name)
507:     {
508:         $query = 'DELETE FROM ' . $this->_params['search'] . ' WHERE user_uid = ? AND search_name = ?';
509: 
510:         return $this->_write_db->query($query, array($GLOBALS['registry']->getAuth(), $name));
511:     }
512: 
513:    /**
514:     * Log users actions
515:     *
516:     * @param string $message    Log message
517:     * @param string $scope    Scope
518:     * @param string $user    Username
519:     *
520:     * @return true on success
521:     */
522:     protected function _logActivity($message, $scope, $user)
523:     {
524:         $query = 'INSERT INTO ' . $this->_params['activity']
525:                 . ' (user_uid, activity_message, activity_scope, activity_date) VALUES (?, ?, ?, ?)';
526: 
527:         return $this->_write_db->query($query, array($user, $message, $scope, $_SERVER['REQUEST_TIME']));
528:     }
529: 
530:    /**
531:     * Get user's activity
532:     *
533:     * @param string $user    Username
534:     * @param string $activity    Number of actions to return
535:     *
536:     * @return array    Activity log
537:     */
538:     protected function _getActivity($user, $limit)
539:     {
540:         $query = 'SELECT activity_message, activity_scope, activity_date, user_uid FROM '
541:                 . $this->_params['activity'] . ' WHERE user_uid = ? '
542:                 . 'ORDER BY activity_date DESC';
543:         $query = $this->_db->modifyLimitQuery($query, 0, $limit);
544: 
545:         return $this->_db->getAll($query, array($user), DB_FETCHMODE_ASSOC);
546:     }
547: 
548:    /**
549:     * Delete users activity
550:     *
551:     * @param string $scope    Scope
552:     * @param integer $date    Date
553:     * @param string $user    Username
554:     *
555:     * @return true on success
556:     */
557:     protected function _deleteActivity($scope, $date, $user)
558:     {
559:         $query = 'DELETE FROM ' . $this->_params['activity']
560:                 . ' WHERE user_uid = ? AND activity_scope = ? AND activity_date = ?';
561: 
562:         return $this->_write_db->query($query, array($user, $scope, $date));
563:     }
564: 
565:     /**
566:      * Attempts to open a persistent connection to the SQL server.
567:      *
568:      * @return boolean  True on success.
569:      * @throws Horde_Exception
570:      */
571:     private function _connect()
572:     {
573:         $this->_db = $GLOBALS['injector']->getInstance('Horde_Core_Factory_DbPear')->create('read', 'folks', 'storage');
574:         $this->_write_db = $GLOBALS['injector']->getInstance('Horde_Core_Factory_DbPear')->create('rw', 'folks', 'storage');
575: 
576:         return true;
577:     }
578: }
579: