diff options
Diffstat (limited to 'src/SemanticScuttle/Service')
-rw-r--r-- | src/SemanticScuttle/Service/AuthUser.php | 232 | ||||
-rw-r--r-- | src/SemanticScuttle/Service/Bookmark.php | 1161 | ||||
-rw-r--r-- | src/SemanticScuttle/Service/Bookmark2Tag.php | 714 | ||||
-rw-r--r-- | src/SemanticScuttle/Service/Cache.php | 72 | ||||
-rw-r--r-- | src/SemanticScuttle/Service/CommonDescription.php | 229 | ||||
-rw-r--r-- | src/SemanticScuttle/Service/Factory.php | 154 | ||||
-rw-r--r-- | src/SemanticScuttle/Service/SearchHistory.php | 280 | ||||
-rw-r--r-- | src/SemanticScuttle/Service/Tag.php | 160 | ||||
-rw-r--r-- | src/SemanticScuttle/Service/Tag2Tag.php | 470 | ||||
-rw-r--r-- | src/SemanticScuttle/Service/TagCache.php | 397 | ||||
-rw-r--r-- | src/SemanticScuttle/Service/TagStat.php | 233 | ||||
-rw-r--r-- | src/SemanticScuttle/Service/Template.php | 119 | ||||
-rw-r--r-- | src/SemanticScuttle/Service/Thumbnails.php | 59 | ||||
-rw-r--r-- | src/SemanticScuttle/Service/User.php | 1090 | ||||
-rw-r--r-- | src/SemanticScuttle/Service/User/SslClientCert.php | 283 | ||||
-rw-r--r-- | src/SemanticScuttle/Service/Vote.php | 337 |
16 files changed, 5990 insertions, 0 deletions
diff --git a/src/SemanticScuttle/Service/AuthUser.php b/src/SemanticScuttle/Service/AuthUser.php new file mode 100644 index 0000000..9447ee4 --- /dev/null +++ b/src/SemanticScuttle/Service/AuthUser.php @@ -0,0 +1,232 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ + +require_once 'Auth.php'; +require_once 'SemanticScuttle/Service/User.php'; + +/** + * SemanticScuttle extendet user management service utilizing + * the PEAR Auth package to enable authentication against + * different services, i.e. LDAP or other databases. + * + * Requires the Log packages for debugging purposes. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Christian Weiske <cweiske@cweiske.de> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_AuthUser extends SemanticScuttle_Service_User +{ + /** + * PEAR Auth instance + * + * @var Auth + */ + protected $auth = null; + + /** + * If we want to debug authentication process + * + * @var boolean + */ + protected $authdebug = false; + + /** + * Authentication type (i.e. LDAP) + * + * @var string + * + * @link http://pear.php.net/manual/en/package.authentication.auth.intro-storage.php + */ + var $authtype = null; + + /** + * Authentication options + * + * @var array + * + * @link http://pear.php.net/manual/en/package.authentication.auth.intro.php + */ + var $authoptions = null; + + + + /** + * Returns the single service instance + * + * @param sql_db $db Database object + * + * @return SemanticScuttle_Service_AuthUser + */ + public static function getInstance($db) + { + static $instance; + if (!isset($instance)) { + $instance = new self($db); + } + return $instance; + } + + + + /** + * Create new instance + * + * @var sql_db $db Database object + */ + protected function __construct($db) + { + parent::__construct($db); + + $this->authtype = $GLOBALS['authType']; + $this->authoptions = $GLOBALS['authOptions']; + $this->authdebug = $GLOBALS['authDebug']; + + //FIXME: throw error when no authtype set? + if (!$this->authtype) { + return; + } + require_once 'Auth.php'; + $this->auth = new Auth($this->authtype, $this->authoptions); + //FIXME: check if it worked (i.e. db connection) + if ($this->authdebug) { + require_once 'Log.php'; + $this->auth->logger = Log::singleton( + 'display', '', '', array(), PEAR_LOG_DEBUG + ); + $this->auth->enableLogging = true; + } + $this->auth->setShowLogin(false); + } + + + + /** + * Return current user id based on session or cookie + * + * @return mixed Integer user id or boolean false when user + * could not be found or is not logged on. + */ + public function getCurrentUserId() + { + if (!$this->auth) { + return parent::getCurrentUserId(); + } + + //FIXME: caching? + $name = $this->auth->getUsername(); + if (!$name) { + return parent::getCurrentUserId(); + } + return $this->getIdFromUser($name); + } + + + + /** + * Try to authenticate and login a user with + * username and password. + * + * @param string $username Name of user + * @param string $password Password + * @param boolean $remember If a long-time cookie shall be set + * + * @return boolean True if the user could be authenticated, + * false if not. + */ + public function login($username, $password, $remember = false) + { + if (!$this->auth) { + return parent::login($username, $password, $remember); + } + + $ok = $this->loginAuth($username, $password); + if (!$ok) { + return false; + } + + //utilize real login method to get longtime cookie support etc. + $ok = parent::login($username, $password, $remember); + if ($ok) { + return $ok; + } + + //user must have changed password in external auth. + //we need to update the local database. + $user = $this->getUserByUsername($username); + $this->_updateuser( + $user['uId'], $this->getFieldName('password'), + $this->sanitisePassword($password) + ); + + return parent::login($username, $password, $remember); + } + + + /** + * Uses PEAR's Auth class to authenticate the user against a container. + * This allows us to use LDAP, a different database or some other + * external system. + * + * @param string $username Username to check + * @param string $password Password to check + * + * @return boolean If the user has been successfully authenticated or not + */ + public function loginAuth($username, $password) + { + $this->auth->post = array( + 'username' => $username, + 'password' => $password, + ); + $this->auth->start(); + + if (!$this->auth->checkAuth()) { + return false; + } + + //put user in database + if (!$this->getUserByUsername($username)) { + $this->addUser( + $username, $password, + $username . $GLOBALS['authEmailSuffix'] + ); + } + + return true; + } + + + + + /** + * Logs the current user out of the system. + * + * @return void + */ + public function logout() + { + parent::logout(); + + if ($this->auth) { + $this->auth->logout(); + $this->auth = null; + } + } + +} +?>
\ No newline at end of file diff --git a/src/SemanticScuttle/Service/Bookmark.php b/src/SemanticScuttle/Service/Bookmark.php new file mode 100644 index 0000000..1315350 --- /dev/null +++ b/src/SemanticScuttle/Service/Bookmark.php @@ -0,0 +1,1161 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +require_once 'SemanticScuttle/Model/RemoteUser.php'; + +/** + * SemanticScuttle bookmark service. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_Bookmark extends SemanticScuttle_DbService +{ + /** + * Returns the single service instance + * + * @param DB $db Database object + * + * @return SemanticScuttle_Service + */ + public static function getInstance($db) + { + static $instance; + if (!isset($instance)) { + $instance = new self($db); + } + return $instance; + } + + + + /** + * Creates a new instance. Initializes the table name. + * + * @param DB $db Database object + * + * @uses $GLOBALS['tableprefix'] + */ + public function __construct($db) + { + $this->db = $db; + $this->tablename = $GLOBALS['tableprefix'] .'bookmarks'; + } + + + + /** + * Retrieves the first bookmark whose $fieldname equals + * the given $value. + * + * @param string $fieldname Name of database field + * @param mixed $value Desired value of $fieldname + * @param boolean $all Retrieve from all users (true) + * or only bookmarks owned by the current + * user (false) + * + * @return mixed Database row array when found, boolean false + * when no bookmark matched. + * + * @TODO: merge with getBookmark() + */ + protected function _getbookmark($fieldname, $value, $all = false) + { + if (!$all) { + $userservice = SemanticScuttle_Service_Factory::get('User'); + $uId = $userservice->getCurrentUserId(); + $range = ' AND uId = '. $uId; + } else { + $range = ''; + } + + $query = 'SELECT * FROM '. $this->getTableName() + . ' WHERE ' . $fieldname . ' =' + . ' "' . $this->db->sql_escape($value) .'"' + . $range; + + if (!($dbresult = $this->db->sql_query_limit($query, 1, 0))) { + message_die( + GENERAL_ERROR, + 'Could not get bookmark', '', __LINE__, __FILE__, + $query, $this->db + ); + } + + if ($row = $this->db->sql_fetchrow($dbresult)) { + $output = $row; + } else { + $output = false; + } + $this->db->sql_freeresult($dbresult); + return $output; + } + + + + /** + * Load a single bookmark and return it. + * When a user is logged on, the returned array will contain + * keys "hasVoted" and "vote". + * + * DOES NOT RESPECT PRIVACY SETTINGS! + * + * @param integer $bid Bookmark ID + * @param boolean $include_tags If tags shall be loaded + * + * @return mixed Array with bookmark data or false in case + * of an error. + */ + function getBookmark($bid, $include_tags = false) + { + if (!is_numeric($bid)) { + return false; + } + + $userservice = SemanticScuttle_Service_Factory::get('User'); + + $query_1 = 'B.*'; + $query_2 = $this->getTableName() . ' as B'; + + //Voting system + //needs to be directly after FROM bookmarks + if ($GLOBALS['enableVoting'] && $userservice->isLoggedOn()) { + $cuid = $userservice->getCurrentUserId(); + $vs = SemanticScuttle_Service_Factory::get('Vote'); + $query_1 .= ', !ISNULL(V.bId) as hasVoted, V.vote as vote'; + $query_2 .= ' LEFT JOIN ' . $vs->getTableName() . ' AS V' + . ' ON B.bId = V.bId' + . ' AND V.uId = ' . (int)$cuid; + } + + $sql = 'SELECT ' . $query_1 . ' FROM ' + . $query_2 + .' WHERE B.bId = '. $this->db->sql_escape($bid); + + if (!($dbresult = $this->db->sql_query($sql))) { + message_die( + GENERAL_ERROR, 'Could not get bookmark', + '', __LINE__, __FILE__, $sql, $this->db + ); + } + + if ($row = $this->db->sql_fetchrow($dbresult)) { + if ($include_tags) { + $b2tservice = SemanticScuttle_Service_Factory::get( + 'Bookmark2Tag' + ); + $row['tags'] = $b2tservice->getTagsForBookmark($bid); + } + $output = $row; + } else { + $output = false; + } + $this->db->sql_freeresult($dbresult); + return $output; + } + + + + /** + * Retrieves a bookmark with the given URL. + * DOES NOT RESPECT PRIVACY SETTINGS! + * + * @param string $address URL to get bookmarks for + * @param boolean $all Retrieve from all users (true) + * or only bookmarks owned by the current + * user (false) + * + * @return mixed Array with bookmark data or false in case + * of an error (i.e. not found). + * + * @uses getBookmarkByHash() + * @see getBookmarkByShortname() + */ + public function getBookmarkByAddress($address, $all = true) + { + return $this->getBookmarkByHash($this->getHash($address), $all); + } + + + + /** + * Retrieves a bookmark with the given hash. + * DOES NOT RESPECT PRIVACY SETTINGS! + * + * @param string $hash URL hash + * @param boolean $all Retrieve from all users (true) + * or only bookmarks owned by the current + * user (false) + * + * @return mixed Array with bookmark data or false in case + * of an error (i.e. not found). + * + * @see getHash() + */ + public function getBookmarkByHash($hash, $all = true) + { + return $this->_getbookmark('bHash', $hash, $all); + } + + + + /** + * Returns the hash value of a given address. + * + * @param string $address URL to hash + * @param boolean $bNormalize If the address shall be normalized before + * being hashed + * + * @return string Hash value + */ + public function getHash($address, $bNormalize = true) + { + if ($bNormalize) { + $address = $this->normalize($address); + } + return md5($address); + } + + + + /** + * Retrieves a bookmark that has a given short + * name. + * + * @param string $short Short URL name + * + * @return mixed Array with bookmark data or false in case + * of an error (i.e. not found). + */ + public function getBookmarkByShortname($short) + { + return $this->_getbookmark('bShort', $short, true); + } + + + + /** + * Counts bookmarks for a user. + * + * @param integer $uId User ID + * @param string $status Bookmark visibility/privacy settings: + * 'public', 'shared', 'private' + * or 'all' + * + * @return integer Number of bookmarks + */ + public function countBookmarks($uId, $status = 'public') + { + $sql = 'SELECT COUNT(*) as "0" FROM '. $this->getTableName(); + $sql.= ' WHERE uId = ' . intval($uId); + switch ($status) { + case 'all': + //no constraints + break; + case 'private': + $sql .= ' AND bStatus = 2'; + break; + case 'shared': + $sql .= ' AND bStatus = 1'; + break; + case 'public': + default: + $sql .= ' AND bStatus = 0'; + break; + } + + if (!($dbresult = $this->db->sql_query($sql))) { + message_die( + GENERAL_ERROR, 'Could not get vars', + '', __LINE__, __FILE__, $sql, $this->db + ); + } + $count = $this->db->sql_fetchfield(0, 0); + $this->db->sql_freeresult($dbresult); + return $count; + } + + + + /** + * Check if a bookmark may be edited by the current user + * + * @param integer|array $bookmark Bookmark uId or bookmark array + * + * @return boolean True if allowed + */ + function editAllowed($bookmark) + { + if (!is_numeric($bookmark) + && (!is_array($bookmark) + || !isset($bookmark['bId']) + || !is_numeric($bookmark['bId']) + ) + ) { + return false; + } + + if (!is_array($bookmark) + && !($bookmark = $this->getBookmark($bookmark)) + ) { + return false; + } + + $userservice = SemanticScuttle_Service_Factory::get('User'); + $user = $userservice->getCurrentObjectUser(); + if ($user === null) { + return false; + } + + //user has to be either admin, or owner + if ($GLOBALS['adminsCanModifyBookmarksFromOtherUsers'] + && $userservice->isAdmin($user->username) + ) { + return true; + } else { + return ($bookmark['uId'] == $user->id); + } + } + + + + /** + * Checks if a bookmark for the given URL exists + * already + * + * @param string $address URL of bookmark to check + * @param integer $uid User id the bookmark has to belong to. + * null for all users + * + * @return boolean True when the bookmark with the given URL + * exists for the user, false if not. + */ + public function bookmarkExists($address = false, $uid = null) + { + if (!$address) { + return false; + } + + $crit = array('bHash' => $this->getHash($address)); + if (isset ($uid)) { + $crit['uId'] = $uid; + } + + $sql = 'SELECT COUNT(*) as "0" FROM ' + . $GLOBALS['tableprefix'] . 'bookmarks' + . ' WHERE '. $this->db->sql_build_array('SELECT', $crit); + + if (!($dbresult = $this->db->sql_query($sql))) { + message_die( + GENERAL_ERROR, 'Could not get vars', '', + __LINE__, __FILE__, $sql, $this->db + ); + } + if ($this->db->sql_fetchfield(0, 0) > 0) { + $output = true; + } else { + $output = false; + } + $this->db->sql_freeresult($dbresult); + return $output; + } + + + + /** + * Checks if the given addresses exist + * + * @param array $addresses Array of addresses + * @param integer $uid User ID the addresses shall belong to + * + * @return array Array with addresses as keys, true/false for existence + * as value + */ + public function bookmarksExist($addresses, $uid = null) + { + if (count($addresses) == 0) { + return array(); + } + + $hashes = array(); + $sql = '(0'; + foreach ($addresses as $key => $address) { + $hash = $this->getHash($address); + $hashes[$hash] = $address; + $sql .= ' OR bHash = "' + . $this->db->sql_escape($hash) + . '"'; + } + $sql .= ')'; + if ($uid !== null) { + $sql .= ' AND uId = ' . intval($uid); + } + + $sql = 'SELECT bHash, COUNT(*) as "count" FROM ' + . $this->getTableName() + . ' WHERE ' . $sql + . ' GROUP BY bHash'; + + if (!($dbresult = $this->db->sql_query($sql))) { + message_die( + GENERAL_ERROR, 'Could not get bookmark counts', '', + __LINE__, __FILE__, $sql, $this->db + ); + } + + $existence = array_combine( + $addresses, + array_fill(0, count($addresses), false) + ); + while ($row = $this->db->sql_fetchrow($dbresult)) { + $existence[$hashes[$row['bHash']]] = $row['count'] > 0; + } + + $this->db->sql_freeresult($dbresult); + return $existence; + } + + + + /** + * Adds a bookmark to the database. + * + * Security checks are being made here, but no error reasons will be + * returned. It is the responsibility of the code that calls + * addBookmark() to verify the data. + * + * @param string $address Full URL of the bookmark + * @param string $title Bookmark title + * @param string $description Long bookmark description + * @param string $privateNote Private note for the user. + * @param string $status Bookmark visibility / privacy settings: + * 0 - public + * 1 - shared + * 2 - private + * @param array $tags Array of tags + * @param string $short Short URL name. May be null + * @param string $date Date when the bookmark has been created + * originally. Used in combination with + * $fromImport. Has to be a strtotime() + * interpretable string. + * @param boolean $fromApi True when api call is responsible. + * @param boolean $fromImport True when the bookmark is from an import. + * @param integer $sId ID of user who creates the bookmark. + * + * @return mixed Integer bookmark ID if saving succeeded, false in + * case of an error. Error reasons are not returned. + */ + public function addBookmark( + $address, $title, $description, $privateNote, $status, $tags, + $short = null, + $date = null, $fromApi = false, $fromImport = false, $sId = null + ) { + if ($sId === null) { + $userservice = SemanticScuttle_Service_Factory::get('User'); + $sId = $userservice->getCurrentUserId(); + } + + $address = $this->normalize($address); + if (!SemanticScuttle_Model_Bookmark::isValidUrl($address)) { + return false; + } + + /* + * Note that if date is NULL, then it's added with a date and + * time of now, and if it's present, + * it's expected to be a string that's interpretable by strtotime(). + */ + if (is_null($date) || $date == '') { + $time = time(); + } else { + $time = strtotime($date); + } + $datetime = gmdate('Y-m-d H:i:s', $time); + + if ($short === '') { + $short = null; + } + + // Set up the SQL insert statement and execute it. + $values = array( + 'uId' => intval($sId), + 'bIp' => SemanticScuttle_Model_RemoteUser::getIp(), + 'bDatetime' => $datetime, + 'bModified' => $datetime, + 'bTitle' => $title, + 'bAddress' => $address, + 'bDescription' => $description, + 'bPrivateNote' => $privateNote, + 'bStatus' => intval($status), + 'bHash' => $this->getHash($address), + 'bShort' => $short + ); + + $sql = 'INSERT INTO '. $this->getTableName() + .' ' . $this->db->sql_build_array('INSERT', $values); + $this->db->sql_transaction('begin'); + + if (!($dbresult = $this->db->sql_query($sql))) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, + 'Could not insert bookmark', + '', __LINE__, __FILE__, $sql, $this->db + ); + } + + // Get the resultant row ID for the bookmark. + $bId = $this->db->sql_nextid($dbresult); + if (!isset($bId) || !is_int($bId)) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, + 'Could not insert bookmark', + '', __LINE__, __FILE__, $sql, $this->db + ); + } + + $uriparts = explode('.', $address); + $extension = end($uriparts); + unset($uriparts); + + $b2tservice = SemanticScuttle_Service_Factory::get('Bookmark2Tag'); + $attachok = $b2tservice->attachTags( + $bId, $tags, $fromApi, $extension, false, $fromImport + ); + if (!$attachok) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, + 'Could not insert bookmark', + '', __LINE__, __FILE__, $sql, $this->db + ); + } + $this->db->sql_transaction('commit'); + + // Everything worked out, so return the new bookmark's bId. + return $bId; + }//public function addBookmark(..) + + + + /** + * Update an existing bookmark. + * + * @param integer $bId Bookmark ID + * @param string $address Full URL of the bookmark + * @param string $title Bookmark title + * @param string $description Long bookmark description + * @param string $privateNote Private note for the user. + * @param string $status Bookmark visibility / privacy setting: + * 0 - public + * 1 - shared + * 2 - private + * @param array $categories Array of tags + * @param string $short Short URL name. May be null. + * @param string $date Date when the bookmark has been created + * originally. Used in combination with + * $fromImport. Has to be a strtotime() + * interpretable string. + * @param boolean $fromApi True when api call is responsible. + * + * @return boolean True if all went well, false if not. + */ + public function updateBookmark( + $bId, $address, $title, $description, $privateNote, $status, + $categories, $short = null, $date = null, $fromApi = false + ) { + if (!is_numeric($bId)) { + return false; + } + + // Get the the date; note that the date is in GMT. + $moddatetime = gmdate('Y-m-d H:i:s', time()); + + $address = $this->normalize($address); + + //check if a new address ($address) doesn't already exist + // for another bookmark from the same user + $bookmark = $this->getBookmark($bId); + if ($bookmark['bAddress'] != $address + && $this->bookmarkExists($address, $bookmark['uId']) + ) { + message_die( + GENERAL_ERROR, + 'Could not update bookmark (URL already exists: ' . $address . ')', + '', __LINE__, __FILE__ + ); + return false; + } + + if ($short === '') { + $short = null; + } + + // Set up the SQL update statement and execute it. + $updates = array( + 'bModified' => $moddatetime, + 'bTitle' => $title, + 'bAddress' => $address, + 'bDescription' => $description, + 'bPrivateNote' => $privateNote, + 'bStatus' => $status, + 'bHash' => $this->getHash($address, false), + 'bShort' => $short + ); + + if (!is_null($date)) { + $datetime = gmdate('Y-m-d H:i:s', strtotime($date)); + $updates['bDatetime'] = $datetime; + } + + $sql = 'UPDATE '. $GLOBALS['tableprefix'] . 'bookmarks' + . ' SET '. $this->db->sql_build_array('UPDATE', $updates) + . ' WHERE bId = ' . intval($bId); + $this->db->sql_transaction('begin'); + + if (!($dbresult = $this->db->sql_query($sql))) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not update bookmark', + '', __LINE__, __FILE__, $sql, $this->db + ); + } + + $uriparts = explode('.', $address); + $extension = end($uriparts); + unset($uriparts); + + $b2tservice = SemanticScuttle_Service_Factory :: get('Bookmark2Tag'); + if (!$b2tservice->attachTags($bId, $categories, $fromApi, $extension)) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not update bookmark', + '', __LINE__, __FILE__, $sql, $this->db + ); + } + + $this->db->sql_transaction('commit'); + // Everything worked out, so return true. + return true; + } + + + + /** + * Only get the bookmarks that are visible to the current user. + * Our rules: + * - if the $user is NULL, that means get bookmarks from ALL users, + * so we need to make sure to check the logged-in user's + * watchlist and get the contacts-only bookmarks from + * those users. + * If the user isn't logged-in, just get the public bookmarks. + * + * - if the $user is set and isn't the logged-in user, then get + * that user's bookmarks, and if that user is on the logged-in + * user's watchlist, get the public AND contacts-only + * bookmarks; otherwise, just get the public bookmarks. + * + * - if the $user is set and IS the logged-in user, then + * get all bookmarks. + * + * In case voting is enabled and a user is logged in, + * each bookmark array contains two additional keys: + * 'hasVoted' and 'vote'. + * + * @param integer $start Page number + * @param integer $perpage Number of bookmarks per page + * @param integer $user User ID + * @param mixed $tags Array of tags or tags separated + * by "+" signs + * @param string $terms Search terms separated by spaces + * @param string $sortOrder One of the following values: + * "date_asc", "date_desc", + * "modified_asc", "modified_desc" + * "title_desc", "title_asc", + * "url_desc", "url_asc", + * "voting_asc", "voting_desc" + * @param boolean $watched True if only watched bookmarks + * shall be returned (FIXME) + * @param integer $startdate Filter for creation date. + * SQL-DateTime value + * "YYYY-MM-DD hh:ii:ss' + * @param integer $enddate Filter for creation date. + * SQL-DateTime value + * "YYYY-MM-DD hh:ii:ss' + * @param string $hash Filter by URL hash + * + * @return array Array with two keys: 'bookmarks' and 'total'. + * First contains an array of bookmarks, 'total' + * the total number of bookmarks (without paging). + */ + public function getBookmarks( + $start = 0, $perpage = null, $user = null, $tags = null, + $terms = null, $sortOrder = null, $watched = null, + $startdate = null, $enddate = null, $hash = null + ) { + $userservice = SemanticScuttle_Service_Factory::get('User'); + $b2tservice = SemanticScuttle_Service_Factory::get('Bookmark2Tag'); + $tag2tagservice = SemanticScuttle_Service_Factory::get('Tag2Tag'); + $sId = $userservice->getCurrentUserId(); + + if ($userservice->isLoggedOn()) { + // All public bookmarks, user's own bookmarks + // and any shared with user + $privacy = ' AND ((B.bStatus = 0) OR (B.uId = '. $sId .')'; + $watchnames = $userservice->getWatchNames($sId, true); + foreach ($watchnames as $watchuser) { + $privacy .= ' OR (U.username = "'. $watchuser .'" AND B.bStatus = 1)'; + } + $privacy .= ')'; + } else { + // Just public bookmarks + $privacy = ' AND B.bStatus = 0'; + } + + // Set up the tags, if need be. + if (!is_array($tags) && !is_null($tags)) { + $tags = explode('+', trim($tags)); + } + + $tagcount = count($tags); + for ($i = 0; $i < $tagcount; $i ++) { + $tags[$i] = trim($tags[$i]); + } + + // Set up the SQL query. + $query_1 = 'SELECT DISTINCT '; + if (SQL_LAYER == 'mysql4') { + $query_1 .= 'SQL_CALC_FOUND_ROWS '; + } + $query_1 .= 'B.*, U.'. $userservice->getFieldName('username') + . ', U.name'; + + $query_2 = ' FROM '. $userservice->getTableName() .' AS U' + . ', '. $this->getTableName() .' AS B'; + + $query_3 = ' WHERE B.uId = U.'. $userservice->getFieldName('primary') . $privacy; + + if ($GLOBALS['enableVoting'] && $GLOBALS['hideBelowVoting'] !== null + && !$userservice->isAdmin($userservice->getCurrentUserId()) + ) { + $query_3 .= ' AND B.bVoting >= ' . (int)$GLOBALS['hideBelowVoting']; + } + + if (is_null($watched)) { + if (!is_null($user)) { + $query_3 .= ' AND B.uId = '. $user; + } + } else { + $arrWatch = $userservice->getWatchlist($user); + if (count($arrWatch) > 0) { + $query_3_1 = ''; + foreach ($arrWatch as $row) { + $query_3_1 .= 'B.uId = '. intval($row) .' OR '; + } + $query_3_1 = substr($query_3_1, 0, -3); + } else { + $query_3_1 = 'B.uId = -1'; + } + $query_3 .= ' AND ('. $query_3_1 .') AND B.bStatus IN (0, 1)'; + } + + $query_5 = ''; + if ($hash == null) { + $query_5.= ' GROUP BY B.bHash'; + } + + + //Voting system + //needs to be directly after FROM bookmarks + if ($GLOBALS['enableVoting'] && $userservice->isLoggedOn()) { + $cuid = $userservice->getCurrentUserId(); + $vs = SemanticScuttle_Service_Factory::get('Vote'); + $query_1 .= ', !ISNULL(V.bId) as hasVoted, V.vote as vote'; + $query_2 .= ' LEFT JOIN ' . $vs->getTableName() . ' AS V' + . ' ON B.bId = V.bId' + . ' AND V.uId = ' . (int)$cuid; + } + + switch($sortOrder) { + case 'date_asc': + $query_5 .= ' ORDER BY B.' . $GLOBALS['dateOrderField'] . ' ASC '; + break; + case 'title_desc': + $query_5 .= ' ORDER BY B.bTitle DESC '; + break; + case 'title_asc': + $query_5 .= ' ORDER BY B.bTitle ASC '; + break; + case 'voting_desc': + $query_5 .= ' ORDER BY B.bVoting DESC '; + break; + case 'voting_asc': + $query_5 .= ' ORDER BY B.bVoting ASC '; + break; + case 'url_desc': + $query_5 .= ' ORDER BY B.bAddress DESC '; + break; + case 'url_asc': + $query_5 .= ' ORDER BY B.bAddress ASC '; + break; + case 'modified_desc': + $query_5 .= ' ORDER BY B.bModified DESC '; + break; + case 'modified_asc': + $query_5 .= ' ORDER BY B.bModified ASC '; + break; + default: + $query_5 .= ' ORDER BY B.' . $GLOBALS['dateOrderField'] . ' DESC '; + } + + // Handle the parts of the query that depend on any tags that are present. + $query_4 = ''; + for ($i = 0; $i < $tagcount; $i ++) { + $query_2 .= ', '. $b2tservice->getTableName() .' AS T'. $i; + $query_4 .= ' AND ('; + + $allLinkedTags = $tag2tagservice->getAllLinkedTags( + $this->db->sql_escape($tags[$i]), '>', $user + ); + + while (is_array($allLinkedTags) && count($allLinkedTags)>0) { + $query_4 .= ' T'. $i .'.tag = "'. array_pop($allLinkedTags) .'"'; + $query_4 .= ' OR'; + } + + $query_4 .= ' T'. $i .'.tag = "'. $this->db->sql_escape($tags[$i]) .'"'; + + $query_4 .= ') AND T'. $i .'.bId = B.bId'; + //die($query_4); + } + + // Search terms + if ($terms) { + // Multiple search terms okay + $aTerms = explode(' ', $terms); + $aTerms = array_map('trim', $aTerms); + + // Search terms in tags as well when none given + if (!count($tags)) { + $query_2 .= ' LEFT JOIN '. $b2tservice->getTableName() .' AS T' + . ' ON B.bId = T.bId'; + $dotags = true; + } else { + $dotags = false; + } + + $query_4 = ''; + for ($i = 0; $i < count($aTerms); $i++) { + $query_4 .= ' AND (B.bTitle LIKE "%' + . $this->db->sql_escape($aTerms[$i]) + . '%"'; + $query_4 .= ' OR B.bDescription LIKE "%' + . $this->db->sql_escape($aTerms[$i]) + . '%"'; + //warning : search in private notes of everybody + // but private notes won't appear if not allowed. + $query_4 .= ' OR B.bPrivateNote LIKE "' + . $this->db->sql_escape($aTerms[$i]) + .'%"'; + $query_4 .= ' OR U.username = "' + . $this->db->sql_escape($aTerms[$i]) + . '"'; //exact match for username + if ($dotags) { + $query_4 .= ' OR T.tag LIKE "' + . $this->db->sql_escape($aTerms[$i]) + . '%"'; + } + $query_4 .= ')'; + } + } + + // Start and end dates + if ($startdate) { + $query_4 .= ' AND B.bDatetime > "'. $startdate .'"'; + } + if ($enddate) { + $query_4 .= ' AND B.bDatetime < "'. $enddate .'"'; + } + + // Hash + if ($hash) { + $query_4 .= ' AND B.bHash = "'. $hash .'"'; + } + + + $query = $query_1 . $query_2 . $query_3 . $query_4 . $query_5; + + $dbresult = $this->db->sql_query_limit( + $query, intval($perpage), intval($start) + ); + if (!$dbresult) { + message_die( + GENERAL_ERROR, 'Could not get bookmarks', + '', __LINE__, __FILE__, $query, $this->db + ); + } + + if (SQL_LAYER == 'mysql4') { + $totalquery = 'SELECT FOUND_ROWS() AS total'; + } else { + if ($hash) { + $totalquery = 'SELECT COUNT(*) AS total'. $query_2 + . $query_3 . $query_4; + } else { + $totalquery = 'SELECT COUNT(DISTINCT bAddress) AS total' + . $query_2 . $query_3 . $query_4; + } + } + + if (!($totalresult = $this->db->sql_query($totalquery)) + || (!($row = $this->db->sql_fetchrow($totalresult))) + ) { + message_die( + GENERAL_ERROR, 'Could not get total bookmarks', + '', __LINE__, __FILE__, $totalquery, $this->db + ); + } + + $total = $row['total']; + $this->db->sql_freeresult($totalresult); + + $bookmarks = array(); + $bookmarkids = array(); + while ($row = $this->db->sql_fetchrow($dbresult)) { + $bookmarks[] = $row; + $bookmarkids[] = $row['bId']; + } + if (count($bookmarkids)) { + $tags = $b2tservice->getTagsForBookmarks($bookmarkids); + foreach ($bookmarks as &$bookmark) { + $bookmark['tags'] = $tags[$bookmark['bId']]; + } + } + + $this->db->sql_freeresult($dbresult); + $output = array ('bookmarks' => $bookmarks, 'total' => $total); + return $output; + } + + + + /** + * Delete the bookmark with the given id. + * Also deletes tags and votes for the given bookmark. + * + * @param integer $bookmark Bookmark ID + * + * @return boolean True if all went well, false if not + */ + public function deleteBookmark($bookmark) + { + $bookmark = (int)$bookmark; + + $query = 'DELETE FROM ' . $GLOBALS['tableprefix'] . 'bookmarks' + . ' WHERE bId = '. $bookmark; + $this->db->sql_transaction('begin'); + if (!($dbres = $this->db->sql_query($query))) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not delete bookmark', + '', __LINE__, __FILE__, $query, $this->db + ); + } + + $query = 'DELETE FROM ' . $GLOBALS['tableprefix'] . 'bookmarks2tags' + . ' WHERE bId = '. $bookmark; + $this->db->sql_transaction('begin'); + if (!($dbres = $this->db->sql_query($query))) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not delete tags for bookmark', + '', __LINE__, __FILE__, $query, $this->db + ); + } + + $query = 'DELETE FROM '. $GLOBALS['tableprefix'] .'votes' + . ' WHERE bid = '. $bookmark; + $this->db->sql_transaction('begin'); + if (!($dbres = $this->db->sql_query($query))) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not delete votes for bookmark', + '', __LINE__, __FILE__, $query, $this->db + ); + } + + $this->db->sql_transaction('commit'); + + return true; + } + + + + /** + * Deletes all bookmarks of the given user + * + * @param integer $uId User ID + * + * @return boolean true when all went well + */ + public function deleteBookmarksForUser($uId) + { + $query = 'DELETE FROM '. $GLOBALS['tableprefix'] . 'bookmarks' + . ' WHERE uId = '. intval($uId); + + if (!($dbresult = $this->db->sql_query($query))) { + message_die( + GENERAL_ERROR, 'Could not delete bookmarks', + '', __LINE__, __FILE__, $query, $this->db + ); + } + + return true; + } + + + + /** + * Counts the number of bookmarks that have the same address + * as the given address. + * + * @param string|array $addresses Address/URL to look for, string + * of one address or array with + * multiple ones + * + * @return integer Number of bookmarks minus one that have the address. + * In case $addresses was an array, key-value array + * with key being the address, value said number of + * bookmarks + * + * @internal + * We do support fetching counts for multiple addresses at once + * because that allows us to reduce the number of queries + * we need in the web interface when displaying i.e. + * 10 bookmarks - only one SQL query is needed then. + */ + public function countOthers($addresses) + { + if (!$addresses) { + return false; + } + $bArray = is_array($addresses); + + $us = SemanticScuttle_Service_Factory::get('User'); + $sId = (int)$us->getCurrentUserId(); + + if ($us->isLoggedOn()) { + //All public bookmarks, user's own bookmarks + // and any shared with our user + $privacy = ' AND ((B.bStatus = 0) OR (B.uId = ' . $sId . ')'; + $watchnames = $us->getWatchNames($sId, true); + foreach ($watchnames as $watchuser) { + $privacy .= ' OR (U.username = "' + . $this->db->sql_escape($watchuser) + . '" AND B.bStatus = 1)'; + } + $privacy .= ')'; + } else { + //Just public bookmarks + $privacy = ' AND B.bStatus = 0'; + } + + $addressesSql = ' AND (0'; + foreach ((array)$addresses as $address) { + $addressesSql .= ' OR B.bHash = "' + . $this->db->sql_escape($this->getHash($address)) + . '"'; + } + $addressesSql .= ')'; + + + $sql = 'SELECT B.bAddress, COUNT(*) as count FROM ' + . $us->getTableName() . ' AS U' + . ', '. $GLOBALS['tableprefix'] . 'bookmarks AS B' + . ' WHERE U.'. $us->getFieldName('primary') .' = B.uId' + . $addressesSql + . $privacy + . ' GROUP BY B.bHash'; + + if (!($dbresult = $this->db->sql_query($sql))) { + message_die( + GENERAL_ERROR, 'Could not get other count', + '', __LINE__, __FILE__, $sql, $this->db + ); + } + + //be sure we also list urls in our array + // that are not found in the database + $counts = array_combine( + (array)$addresses, + array_fill(0, count((array)$addresses), 0) + ); + while ($row = $this->db->sql_fetchrow($dbresult)) { + $counts[$row['bAddress']] + = $row['count'] > 0 ? $row['count'] - 1 : 0; + } + $this->db->sql_freeresult($dbresult); + + return $bArray ? $counts : reset($counts); + } + + + + /** + * Normalizes a given address. + * Prepends http:// if there is no protocol specified, + * and removes the trailing slash + * + * @param string $address URL to check + * + * @return string Fixed URL + */ + public function normalize($address) + { + //you know, there is "callto:" and "mailto:" + if (strpos($address, ':') === false) { + $address = 'http://'. $address; + } + + // Delete final / + if (substr($address, -1) == '/') { + $address = substr($address, 0, count($address)-2); + } + + return $address; + } + + + + /** + * Delete all bookmarks. + * Mainly used in unit tests. + * + * @return void + */ + public function deleteAll() + { + $query = 'TRUNCATE TABLE `'. $this->getTableName() .'`'; + $this->db->sql_query($query); + } + +} + +?> diff --git a/src/SemanticScuttle/Service/Bookmark2Tag.php b/src/SemanticScuttle/Service/Bookmark2Tag.php new file mode 100644 index 0000000..a01b5d7 --- /dev/null +++ b/src/SemanticScuttle/Service/Bookmark2Tag.php @@ -0,0 +1,714 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ + +/** + * SemanticScuttle bookmark-tag combination service. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService +{ + /** + * Returns the single service instance + * + * @param DB $db Database object + * + * @return SemanticScuttle_Service + */ + public static function getInstance($db) + { + static $instance; + if (!isset($instance)) { + $instance = new self($db); + } + return $instance; + } + + public function __construct($db) + { + $this->db = $db; + $this->tablename = $GLOBALS['tableprefix'] .'bookmarks2tags'; + } + + function isNotSystemTag($var) { + if (utf8_substr($var, 0, 7) == 'system:') + return false; + else + return true; + } + + /** + * Attach tags to a bookmark. + * + * Make sure that categories is an array of trimmed strings. + * If the categories are coming in from an API call, be sure + * that underscores are converted into strings. + * + * @param integer $bookmarkid ID of the bookmark + * @param array $tags Array of tags (strings, trimmed) + * @param boolean $fromApi If this is from an API call + * @param string $extension File extension (i.e. 'pdf') + * @param boolean $replace If existing tags for this bookmark + * are to be replaced + * @param boolean $fromImport If this is from a file import + * + * @return boolean True if all went well + */ + public function attachTags( + $bookmarkid, $tags, $fromApi = false, + $extension = null, $replace = true, $fromImport = false + ) { + if (!is_array($tags)) { + $tags = trim($tags); + if ($tags != '') { + if (substr($tags, -1) == ',') { + $tags = substr($tags, 0, -1); + } + if ($fromApi) { + $tags = explode(' ', $tags); + } else { + $tags = explode(',', $tags); + } + } else { + $tags = null; + } + } + + $tagservice = SemanticScuttle_Service_Factory::get('Tag'); + $tags = $tagservice->normalize($tags); + + $tags_count = is_array($tags)?count($tags):0; + if (is_array($tags)) { + foreach ($tags as $i => $tag) { + $tags[$i] = trim(utf8_strtolower($tags[$i])); + if ($fromApi) { + $tags[$i] = convertTag($tags[$i], 'in'); + } + } + } + + if ($tags_count > 0) { + // Remove system tags + $tags = array_filter($tags, array($this, "isNotSystemTag")); + + // Eliminate any duplicate categories + $temp = array_unique($tags); + $tags = array_values($temp); + } else { + // Unfiled + $tags[] = 'system:unfiled'; + } + + // Media and file types + if (!is_null($extension)) { + include_once 'SemanticScuttle/functions.php'; + + if ($keys = multi_array_search($extension, $GLOBALS['filetypes'])) { + $tags[] = 'system:filetype:'. $extension; + $tags[] = 'system:media:'. array_shift($keys); + } + } + + // Imported + if ($fromImport) { + $tags[] = 'system:imported'; + } + + $this->db->sql_transaction('begin'); + + if ($replace) { + if (!$this->deleteTagsForBookmark($bookmarkid)){ + $this->db->sql_transaction('rollback'); + message_die(GENERAL_ERROR, 'Could not attach tags (deleting old ones failed)', '', __LINE__, __FILE__, $sql, $this->db); + return false; + } + } + + $bs = SemanticScuttle_Service_Factory::get('Bookmark'); + $tts = SemanticScuttle_Service_Factory::get('Tag2Tag'); + + // Create links between tags + foreach ($tags as $key => $tag) { + if (strpos($tag, '=')) { + // case "=" + $pieces = explode('=', $tag); + $nbPieces = count($pieces); + if ($nbPieces <= 1) { + continue; + } + for ($i = 0; $i < $nbPieces-1; $i++) { + $bookmark = $bs->getBookmark($bookmarkid); + $uId = $bookmark['uId']; + $tts->addLinkedTags($pieces[$i], $pieces[$i+1], '=', $uId); + } + // Attach just the last tag to the bookmark + $tags[$key] = $pieces[0]; + } else { + // case ">" + $pieces = explode('>', $tag); + $nbPieces = count($pieces); + if ($nbPieces <= 1) { + continue; + } + for ($i = 0; $i < $nbPieces-1; $i++) { + $bookmark = $bs->getBookmark($bookmarkid); + $uId = $bookmark['uId']; + $tts->addLinkedTags($pieces[$i], $pieces[$i+1], '>', $uId); + } + // Attach just the last tag to the bookmark + $tags[$key] = $pieces[$nbPieces-1]; + } + } + + //after exploding, there may be duplicate keys + //since we are in a transaction, hasTag() may + // not return true for newly added duplicate tags + $tags = array_unique($tags); + + // Add the tags to the DB. + foreach ($tags as $tag) { + if ($tag == '') { + continue; + } + if ($this->hasTag($bookmarkid, $tag)) { + continue; + } + + $values = array( + 'bId' => intval($bookmarkid), + 'tag' => $tag + ); + + $sql = 'INSERT INTO '. $this->getTableName() + . ' ' . $this->db->sql_build_array('INSERT', $values); + if (!($dbresult = $this->db->sql_query($sql))) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not attach tags', + '', __LINE__, __FILE__, $sql, $this->db + ); + return false; + } + } + $this->db->sql_transaction('commit'); + return true; + } + + function deleteTag($uId, $tag) { + $bs =SemanticScuttle_Service_Factory::get('Bookmark'); + + $query = 'DELETE FROM '. $this->getTableName(); + $query.= ' USING '. $this->getTableName() .', '. $bs->getTableName(); + $query.= ' WHERE '. $this->getTableName() .'.bId = '. $bs->getTableName() .'.bId'; + $query.= ' AND '. $bs->getTableName() .'.uId = '. $uId; + $query.= ' AND '. $this->getTableName() .'.tag = "'. $this->db->sql_escape($tag) .'"'; + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not delete tags', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + return true; + } + + function deleteTagsForBookmark($bookmarkid) { + if (!is_int($bookmarkid)) { + message_die(GENERAL_ERROR, 'Could not delete tags (invalid bookmarkid)', '', __LINE__, __FILE__, $query); + return false; + } + + $query = 'DELETE FROM '. $this->getTableName() .' WHERE bId = '. intval($bookmarkid); + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not delete tags', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + return true; + } + + /* Allow deletion in admin page */ + function deleteTagsForUser($uId) { + $qmask = 'DELETE FROM %s USING %s, %s WHERE %s.bId = %s.bId AND %s.uId = %d'; + $query = sprintf($qmask, + $this->getTableName(), + $this->getTableName(), + $GLOBALS['tableprefix'].'bookmarks', + $this->getTableName(), + $GLOBALS['tableprefix'].'bookmarks', + $GLOBALS['tableprefix'].'bookmarks', + $uId); + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not delete tags', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + return true; + } + + + /** + * Retrieves all tags for a given bookmark except system tags. + * + * @param integer $bookmarkid ID of the bookmark + * @param boolean $systemTags Return "system:*" tags or not + * + * @return array Array of tags + */ + public function getTagsForBookmark($bookmarkid, $systemTags = false) + { + if (!is_numeric($bookmarkid)) { + message_die( + GENERAL_ERROR, 'Could not get tags (invalid bookmarkid)', + '', __LINE__, __FILE__, $query + ); + return false; + } + + $query = 'SELECT tag FROM ' . $this->getTableName() + . ' WHERE bId = ' . intval($bookmarkid); + if (!$systemTags) { + $query .= ' AND LEFT(tag, 7) <> "system:"'; + } + $query .= ' ORDER BY id ASC'; + + if (!($dbresult = $this->db->sql_query($query))) { + message_die( + GENERAL_ERROR, 'Could not get tags', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } + + $tags = array(); + while ($row = $this->db->sql_fetchrow($dbresult)) { + $tags[] = $row['tag']; + } + $this->db->sql_freeresult($dbresult); + return $tags; + } + + + /** + * Retrieves all tags for an array of bookmark IDs + * + * @param array $bookmarkids Array of bookmark IDs + * + * @return array Array of tag arrays. Key is bookmark ID. + */ + public function getTagsForBookmarks($bookmarkids) + { + if (!is_array($bookmarkids)) { + message_die( + GENERAL_ERROR, 'Could not get tags (invalid bookmarkids)', + '', __LINE__, __FILE__, $query + ); + return false; + } else if (count($bookmarkids) == 0) { + return array(); + } + + $query = 'SELECT tag, bId FROM ' . $this->getTableName() + . ' WHERE bId IN (' . implode(',', $bookmarkids) . ')' + . ' AND LEFT(tag, 7) <> "system:"' + . ' ORDER BY id, bId ASC'; + + if (!($dbresult = $this->db->sql_query($query))) { + message_die( + GENERAL_ERROR, 'Could not get tags', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } + + $tags = array_combine( + $bookmarkids, + array_fill(0, count($bookmarkids), array()) + ); + while ($row = $this->db->sql_fetchrow($dbresult)) { + $tags[$row['bId']][] = $row['tag']; + } + $this->db->sql_freeresult($dbresult); + return $tags; + } + + + function &getTags($userid = NULL) { + $userservice =SemanticScuttle_Service_Factory::get('User'); + $logged_on_user = $userservice->getCurrentUserId(); + + $query = 'SELECT T.tag, COUNT(B.bId) AS bCount FROM '. $GLOBALS['tableprefix'] .'bookmarks AS B INNER JOIN '. $userservice->getTableName() .' AS U ON B.uId = U.'. $userservice->getFieldName('primary') .' INNER JOIN '. $GLOBALS['tableprefix'] .'bookmarks2tags AS T ON B.bId = T.bId'; + + $conditions = array(); + if (!is_null($userid)) { + $conditions['U.'. $userservice->getFieldName('primary')] = intval($userid); + if ($logged_on_user != $userid) + $conditions['B.bStatus'] = 0; + } else { + $conditions['B.bStatus'] = 0; + } + + $query .= ' WHERE '. $this->db->sql_build_array('SELECT', $conditions) .' AND LEFT(T.tag, 7) <> "system:" GROUP BY T.tag ORDER BY bCount DESC, tag'; + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not get tags', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + $output = $this->db->sql_fetchrowset($dbresult); + $this->db->sql_freeresult($dbresult); + return $output; + } + + + // Returns the tags related to the specified tags; i.e. attached to the same bookmarks + function &getRelatedTags($tags, $for_user = NULL, $logged_on_user = NULL, $limit = 10) { + $conditions = array(); + // Only count the tags that are visible to the current user. + if ($for_user != $logged_on_user || is_null($for_user)) + $conditions['B.bStatus'] = 0; + + if (!is_null($for_user)) + $conditions['B.uId'] = $for_user; + + // Set up the tags, if need be. + if (is_numeric($tags)) + $tags = NULL; + if (!is_array($tags) and !is_null($tags)) + $tags = explode('+', trim($tags)); + + $tagcount = count($tags); + for ($i = 0; $i < $tagcount; $i++) { + $tags[$i] = trim($tags[$i]); + } + + // Set up the SQL query. + $query_1 = 'SELECT DISTINCTROW T0.tag, COUNT(B.bId) AS bCount FROM '. $GLOBALS['tableprefix'] .'bookmarks AS B, '. $this->getTableName() .' AS T0'; + $query_2 = ''; + $query_3 = ' WHERE B.bId = T0.bId '; + if (count($conditions) > 0) + $query_4 = ' AND '. $this->db->sql_build_array('SELECT', $conditions); + else + $query_4 = ''; + // Handle the parts of the query that depend on any tags that are present. + for ($i = 1; $i <= $tagcount; $i++) { + $query_2 .= ', '. $this->getTableName() .' AS T'. $i; + $query_4 .= ' AND T'. $i .'.bId = B.bId AND T'. $i .'.tag = "'. $this->db->sql_escape($tags[$i - 1]) .'" AND T0.tag <> "'. $this->db->sql_escape($tags[$i - 1]) .'"'; + } + $query_5 = ' AND LEFT(T0.tag, 7) <> "system:" GROUP BY T0.tag ORDER BY bCount DESC, T0.tag'; + $query = $query_1 . $query_2 . $query_3 . $query_4 . $query_5; + + if (! ($dbresult = $this->db->sql_query_limit($query, $limit)) ){ + message_die(GENERAL_ERROR, 'Could not get related tags', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + $output = $this->db->sql_fetchrowset($dbresult); + $this->db->sql_freeresult($dbresult); + return $output; + } + + // Returns the most popular tags used for a particular bookmark hash + function &getRelatedTagsByHash($hash, $limit = 20) { + $userservice = SemanticScuttle_Service_Factory :: get('User'); + $sId = $userservice->getCurrentUserId(); + // Logged in + if ($userservice->isLoggedOn()) { + $arrWatch = $userservice->getWatchList($sId); + // From public bookmarks or user's own + $privacy = ' AND ((B.bStatus = 0) OR (B.uId = '. $sId .')'; + // From shared bookmarks in watchlist + foreach ($arrWatch as $w) { + $privacy .= ' OR (B.uId = '. $w .' AND B.bStatus = 1)'; + } + $privacy .= ') '; + // Not logged in + } else { + $privacy = ' AND B.bStatus = 0 '; + } + + $query = 'SELECT T.tag, COUNT(T.tag) AS bCount FROM '.$GLOBALS['tableprefix'].'bookmarks AS B LEFT JOIN '.$GLOBALS['tableprefix'].'bookmarks2tags AS T ON B.bId = T.bId WHERE B.bHash = \''. $this->db->sql_escape($hash) .'\' '. $privacy .'AND LEFT(T.tag, 7) <> "system:" GROUP BY T.tag ORDER BY bCount DESC'; + + if (!($dbresult = $this->db->sql_query_limit($query, $limit))) { + message_die(GENERAL_ERROR, 'Could not get related tags for this hash', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + $output = $this->db->sql_fetchrowset($dbresult); + $this->db->sql_freeresult($dbresult); + return $output; + } + + + + /** + * Returns the tags used by admin users + * + * @param integer $limit Number of tags to return + * @param integer $logged_on_user ID of the user that's currently logged in. + * If the logged in user equals the $user to find + * tags for, tags of private bookmarks are + * returned. + * @param integer $days Bookmarks have to be changed in the last X days + * if their tags shall count + * @param string $beginsWith The tag name shall begin with that string + * + * @return array Array of found tags. Each tag entry is an array with two keys, + * 'tag' (tag name) and 'bCount'. + * + * @see getPopularTags() + */ + public function getAdminTags( + $limit = 30, $logged_on_user = null, $days = null, $beginsWith = null + ) { + // look for admin ids + $userservice = SemanticScuttle_Service_Factory::get('User'); + $adminIds = $userservice->getAdminIds(); + + // ask for their tags + return $this->getPopularTags( + $adminIds, $limit, $logged_on_user, $days, $beginsWith + ); + } + + + + + /** + * Returns the tags used by users that are part of the user's watchlist, + * and the current user's own tags. + * + * @param integer $user ID of the user to get the watchlist from + * @param integer $limit Number of tags to return + * @param integer $logged_on_user ID of the user that's currently logged in. + * If set, that user is added to the list of + * people to get the tags from + * @param integer $days Bookmarks have to be changed in the last X days + * if their tags shall count + * @param string $beginsWith The tag name shall begin with that string + * + * @return array Array of found tags. Each tag entry is an array with two keys, + * 'tag' (tag name) and 'bCount'. + * + * @see getPopularTags() + */ + public function getContactTags( + $user, $limit = 30, $logged_on_user = null, $days = null, + $beginsWith = null + ) { + // look for contact ids + $userservice = SemanticScuttle_Service_Factory::get('User'); + $contacts = $userservice->getWatchlist($user); + + // add the user (to show him also his own tags) + if (!is_null($logged_on_user)) { + $contacts[] = $logged_on_user; + } + + // ask for their tags + return $this->getPopularTags( + $contacts, $limit, $logged_on_user, $days, $beginsWith + ); + } + + + + /** + * The the most popular tags and their usage count + * + * @param mixed $user Integer user ID or array of user IDs to limit tag + * finding to + * @param integer $limit Number of tags to return + * @param integer $logged_on_user ID of the user that's currently logged in. + * If the logged in user equals the $user to find + * tags for, tags of private bookmarks are + * returned. + * @param integer $days Bookmarks have to be changed in the last X days + * if their tags shall count + * @param string $beginsWith The tag name shall begin with that string + * + * @return array Array of found tags. Each tag entry is an array with two keys, + * 'tag' (tag name) and 'bCount'. + * + * @see getAdminTags() + * @see getContactTags() + */ + public function getPopularTags( + $user = null, $limit = 30, $logged_on_user = null, $days = null, + $beginsWith = null + ) { + $query = 'SELECT' + . ' T.tag, COUNT(T.bId) AS bCount' + . ' FROM ' + . $this->getTableName() . ' AS T' + . ', ' . $GLOBALS['tableprefix'] . 'bookmarks AS B' + . ' WHERE'; + + if (is_null($user) || $user === false) { + $query .= ' B.bId = T.bId AND B.bStatus = 0'; + } else if (is_array($user)) { + $query .= ' (1 = 0'; //tricks + foreach ($user as $u) { + if (!is_numeric($u)) { + continue; + } + $query .= ' OR (' + . ' B.uId = ' . $this->db->sql_escape($u) + . ' AND B.bId = T.bId'; + if ($u !== $logged_on_user) { + //public bookmarks of others + $query .= ' AND B.bStatus = 0'; + } + $query .= ')'; + } + $query .= ' )'; + } else { + $query .= ' B.uId = ' . $this->db->sql_escape($user) + . ' AND B.bId = T.bId'; + if ($user !== $logged_on_user) { + $query .= ' AND B.bStatus = 0'; + } + } + + if (is_int($days)) { + $query .= ' AND B.bDatetime > "' + . gmdate('Y-m-d H:i:s', time() - (86400 * $days)) + . '"'; + } + + if (!is_null($beginsWith)) { + $query .= ' AND T.tag LIKE \'' + . $this->db->sql_escape($beginsWith) + . '%\''; + } + + $query .= ' AND LEFT(T.tag, 7) <> "system:"' + . ' GROUP BY T.tag' + . ' ORDER BY bCount DESC, tag'; + + if (!($dbresult = $this->db->sql_query_limit($query, $limit))) { + message_die( + GENERAL_ERROR, 'Could not get popular tags', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } + + $output = $this->db->sql_fetchrowset($dbresult); + $this->db->sql_freeresult($dbresult); + return $output; + } + + + + function hasTag($bookmarkid, $tag) { + $query = 'SELECT COUNT(*) AS tCount FROM '. $this->getTableName() .' WHERE bId = '. intval($bookmarkid) .' AND tag ="'. $this->db->sql_escape($tag) .'"'; + + if (! ($dbresult = $this->db->sql_query($query)) ) { + message_die(GENERAL_ERROR, 'Could not find tag', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + $output = false; + if ($row = $this->db->sql_fetchrow($dbresult)) { + if ($row['tCount'] > 0) { + $output = true; + } + } + + $this->db->sql_freeresult($dbresult); + return $output; + } + + function renameTag($userid, $old, $new, $fromApi = false) { + $bookmarkservice =SemanticScuttle_Service_Factory::get('Bookmark'); + $tagservice =SemanticScuttle_Service_Factory::get('Tag'); + + if (is_null($userid) || is_null($old) || is_null($new)) + return false; + + // Find bookmarks with old tag + $bookmarksInfo = $bookmarkservice->getBookmarks(0, NULL, $userid, $old); + $bookmarks = $bookmarksInfo['bookmarks']; + + // Delete old tag + $this->deleteTag($userid, $old); + + // Attach new tags + $new = $tagservice->normalize($new); + + foreach (array_keys($bookmarks) as $key) { + $row = $bookmarks[$key]; + $this->attachTags($row['bId'], $new, $fromApi, NULL, false); + } + + return true; + } + + function &tagCloud($tags = NULL, $steps = 5, $sizemin = 90, $sizemax = 225, $sortOrder = NULL) { + + if (is_null($tags) || count($tags) < 1) { + $output = false; + return $output; + } + + $min = $tags[count($tags) - 1]['bCount']; + $max = $tags[0]['bCount']; + + for ($i = 1; $i <= $steps; $i++) { + $delta = ($max - $min) / (2 * $steps - $i); + $limit[$i] = $i * $delta + $min; + } + $sizestep = ($sizemax - $sizemin) / $steps; + foreach ($tags as $row) { + $next = false; + for ($i = 1; $i <= $steps; $i++) { + if (!$next && $row['bCount'] <= $limit[$i]) { + $size = $sizestep * ($i - 1) + $sizemin; + $next = true; + } + } + $tempArray = array('size' => $size .'%'); + $row = array_merge($row, $tempArray); + $output[] = $row; + } + + if ($sortOrder == 'alphabet_asc') { + usort($output, create_function('$a,$b','return strcasecmp(utf8_deaccent($a["tag"]), utf8_deaccent($b["tag"]));')); + } + + return $output; + } + + + + /** + * Deletes all tags in bookmarks2tags + * + * @return void + */ + public function deleteAll() + { + $query = 'TRUNCATE TABLE `'. $this->getTableName() .'`'; + $this->db->sql_query($query); + } + +} +?> diff --git a/src/SemanticScuttle/Service/Cache.php b/src/SemanticScuttle/Service/Cache.php new file mode 100644 index 0000000..fa719d3 --- /dev/null +++ b/src/SemanticScuttle/Service/Cache.php @@ -0,0 +1,72 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ + +/** + * SemanticScuttle caching service + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_Cache extends SemanticScuttle_Service +{ + var $basedir; + var $fileextension = '.cache'; + + /** + * Returns the single service instance + * + * @param DB $db Database object + * + * @return SemanticScuttle_Service + */ + public static function getInstance($db) + { + static $instance; + if (!isset($instance)) { + $instance = new self($db); + } + return $instance; + } + + protected function __construct() + { + $this->basedir = $GLOBALS['dir_cache']; + } + + function Start($hash, $time = 300) { + $cachefile = $this->basedir .'/'. $hash . $this->fileextension; + if (file_exists($cachefile) && time() < filemtime($cachefile) + $time) { + @readfile($cachefile); + echo "\n<!-- Cached: ". date('r', filemtime($cachefile)) ." -->\n"; + unset($cachefile); + exit; + } + ob_start("ob_gzhandler"); + } + + function End($hash) { + $cachefile = $this->basedir .'/'. $hash . $this->fileextension; + $handle = fopen($cachefile, 'w'); + fwrite($handle, ob_get_contents()); + fclose($handle); + ob_flush(); + } +} +?>
\ No newline at end of file diff --git a/src/SemanticScuttle/Service/CommonDescription.php b/src/SemanticScuttle/Service/CommonDescription.php new file mode 100644 index 0000000..d03b34a --- /dev/null +++ b/src/SemanticScuttle/Service/CommonDescription.php @@ -0,0 +1,229 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ + +/** + * SemanticScuttle common bookmark description service. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_CommonDescription extends SemanticScuttle_DbService +{ + /** + * Returns the single service instance + * + * @param DB $db Database object + * + * @return SemanticScuttle_Service + */ + public static function getInstance($db) + { + static $instance; + if (!isset($instance)) { + $instance = new self($db); + } + return $instance; + } + + public function __construct($db) + { + $this->db = $db; + $this->tablename = $GLOBALS['tableprefix'] .'commondescription'; + } + + function addTagDescription($tag, $desc, $uId, $time) { + $tag = self::getSortedTag($tag); + + // Check if no modification + $lastDesc = $this->getLastTagDescription($tag); + if($lastDesc['cdDescription'] == $desc) { + return true; + } + + // If modification + $datetime = gmdate('Y-m-d H:i:s', $time); + $values = array('tag'=>$tag, 'cdDescription'=>$desc, 'uId'=>$uId, 'cdDatetime'=>$datetime); + $sql = 'INSERT INTO '. $this->getTableName() .' '. $this->db->sql_build_array('INSERT', $values); + + if (!($dbresult = $this->db->sql_query($sql))) { + $this->db->sql_transaction('rollback'); + message_die(GENERAL_ERROR, 'Could not add tag description', '', __LINE__, __FILE__, $sql, $this->db); + return false; + } + + return true; + } + + function getLastTagDescription($tag) { + $tag = self::getSortedTag($tag); + + $query = "SELECT *"; + $query.= " FROM `". $this->getTableName() ."`"; + $query.= ' WHERE tag=\'' . $this->db->sql_escape($tag) . "'"; + $query.= " ORDER BY cdDatetime DESC"; + + if (!($dbresult = $this->db->sql_query_limit($query, 1, 0))) { + message_die(GENERAL_ERROR, 'Could not get tag description', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + $row = $this->db->sql_fetchrow($dbresult); + $this->db->sql_freeresult($dbresult); + if ($row) { + return $row; + } else { + return false; + } + } + + function getAllTagsDescription($tag) { + $query = "SELECT *"; + $query.= " FROM `". $this->getTableName() ."`"; + $query.= ' WHERE tag=\'' . $this->db->sql_escape($tag) . "'"; + $query.= " ORDER BY cdDatetime DESC"; + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not get tag descriptions', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + $res = $this->db->sql_fetchrowset($dbresult); + $this->db->sql_freeresult($dbresult); + return $res; + } + + function getDescriptionById($cdId) { + $query = "SELECT *"; + $query.= " FROM `". $this->getTableName() ."`"; + $query.= ' WHERE cdId=\'' . $this->db->sql_escape($cdId) . "'"; + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not get tag descriptions', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + if ($row = $this->db->sql_fetchrow($dbresult)) { + $this->db->sql_freeresult($dbresult); + return $row; + } else { + return false; + } + + } + + function addBookmarkDescription($bHash, $title, $desc, $uId, $time) { + // Check if no modification + $lastDesc = $this->getLastBookmarkDescription($bHash); + if($lastDesc['cdTitle'] == $title && $lastDesc['cdDescription'] == $desc) { + return true; + } + + // If modification + $datetime = gmdate('Y-m-d H:i:s', $time); + $values = array('bHash'=>$bHash, 'cdTitle'=>$title, 'cdDescription'=>$desc, 'uId'=>$uId, 'cdDatetime'=>$datetime); + $sql = 'INSERT INTO '. $this->getTableName() .' '. $this->db->sql_build_array('INSERT', $values); + + if (!($dbresult = $this->db->sql_query($sql))) { + $this->db->sql_transaction('rollback'); + message_die(GENERAL_ERROR, 'Could not add bookmark description', '', __LINE__, __FILE__, $sql, $this->db); + return false; + } + return true; + } + + function getLastBookmarkDescription($bHash) { + $query = "SELECT *"; + $query.= " FROM `". $this->getTableName() ."`"; + $query.= ' WHERE bHash=\'' . $this->db->sql_escape($bHash) . "'"; + $query.= " ORDER BY cdDatetime DESC"; + + if (!($dbresult = $this->db->sql_query_limit($query, 1, 0))) { + message_die(GENERAL_ERROR, 'Could not get bookmark description', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + $row = $this->db->sql_fetchrow($dbresult); + $this->db->sql_freeresult($dbresult); + if ($row) { + return $row; + } else { + return false; + } + } + + function getAllBookmarksDescription($bHash) { + $query = "SELECT *"; + $query.= " FROM `". $this->getTableName() ."`"; + $query.= ' WHERE bHash=\'' . $this->db->sql_escape($bHash) . "'"; + $query.= " ORDER BY cdDatetime DESC"; + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not get bookmark descriptions', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + $rowset = $this->db->sql_fetchrowset($dbresult); + $this->db->sql_freeresult($dbresult); + return $rowset; + } + + function deleteDescriptionsForUser($uId){ + $query = 'DELETE FROM '. $this->getTableName() . ' WHERE uId = '. intval($uId); + + $this->db->sql_transaction('begin'); + if (!($dbresult = $this->db->sql_query($query))) { + $this->db->sql_transaction('rollback'); + message_die(GENERAL_ERROR, 'Could not delete user descriptions', '', + __LINE__, __FILE__, $query, $this->db); + return false; + } + + return true; + } + + + function deleteAll() { + $query = 'TRUNCATE TABLE `'. $this->getTableName() .'`'; + $this->db->sql_query($query); + } + + + + /** + * Sorts a tag combination. + * + * Multiple tags are separated by "+" signs. Semantically, + * tag combinations like "blue+flower" and "flower+blue" are identical. + * This method sorts the single tags so i.e. the common bookmark description + * of "blue+flower" is also shown for "flower+blue". + * + * @param string $tag Single tag or tag combination ("+") + * + * @return string Sorted tag combination + */ + protected static function getSortedTag($tag) + { + $tags = explode('+', $tag); + sort($tags); + return implode('+', $tags); + } + +} +?> diff --git a/src/SemanticScuttle/Service/Factory.php b/src/SemanticScuttle/Service/Factory.php new file mode 100644 index 0000000..b661cdb --- /dev/null +++ b/src/SemanticScuttle/Service/Factory.php @@ -0,0 +1,154 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ + +/** + * Connect to the database and build services. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_Factory +{ + /** + * Array of service instances. + * Key is service name (i.e. "Bookmark") + * + * @var array + */ + protected static $instances = array(); + + /** + * Database connection + * + * @var sql_qb + */ + protected static $db = null; + + /** + * Array of service names -> new service class + * in case you want to overwrite the services. + * + * Key is the old service name (i.e. "Bookmark"), + * value the new class name, e.g. + * "My_Cool_Own_BookmarkService" + * + * @var array + */ + public static $serviceoverrides = array(); + + + + /** + * Returns the service for the given name. + * + * @param string $name Service name (i.e. "Bookmark") + * + * @return SemanticScuttle_Service Service object + */ + public static function get($name) + { + self::loadDb(); + self::loadService($name); + return self::$instances[$name]; + } + + + + /** + * Loads service with the given name into + * self::$instances[$name]. + * + * @param string $name Service name (i.e. 'Bookmark') + * + * @return void + */ + protected static function loadService($name) + { + if (isset(self::$instances[$name])) { + return; + } + + if (isset(self::$serviceoverrides[$name])) { + $class = self::$serviceoverrides[$name]; + } else { + $class = 'SemanticScuttle_Service_' . $name; + } + + if (!class_exists($class)) { + //PEAR classname to filename rule + $file = str_replace('_', '/', $class) . '.php'; + include_once $file; + } + + self::$instances[$name] = call_user_func( + array($class, 'getInstance'), + self::$db + ); + } + + + + /** + * Loads self::$db if it is not loaded already. + * Dies if the connection could not be established. + * + * @return void + */ + protected static function loadDb() + { + global $dbhost, $dbuser, $dbpass, $dbname, + $dbport, $dbpersist, $dbtype, $dbneedssetnames; + + if (self::$db !== null) { + return; + } + include_once 'SemanticScuttle/db/'. $dbtype .'.php'; + $db = new sql_db(); + $db->sql_connect( + $dbhost, $dbuser, $dbpass, $dbname, $dbport, $dbpersist + ); + if (!$db->db_connect_id) { + message_die( + CRITICAL_ERROR, + 'Could not connect to the database', + self::$db + ); + } + + $dbneedssetnames && $db->sql_query('SET NAMES UTF8'); + + self::$db = $db; + } + + + + /** + * Returns sql database object + * + * @return sql_db Database instance + */ + public static function getDb() + { + self::loadDb(); + return self::$db; + } + +} +?> diff --git a/src/SemanticScuttle/Service/SearchHistory.php b/src/SemanticScuttle/Service/SearchHistory.php new file mode 100644 index 0000000..ac0b1c7 --- /dev/null +++ b/src/SemanticScuttle/Service/SearchHistory.php @@ -0,0 +1,280 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ + +/** + * SemanticScuttle search history service. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_SearchHistory extends SemanticScuttle_DbService +{ + /** + * Size of the search history. + * If the number of logged searches is larger than this, + * adding a new search will delete the oldest one automatically. + * + * Use -1 to deactivate automatic deletion. + * + * @var integer + */ + public $sizeSearchHistory; + + + + /** + * Returns the single service instance + * + * @param DB $db Database object + * + * @return SemanticScuttle_Service + */ + public static function getInstance($db) + { + static $instance; + if (!isset($instance)) { + $instance = new self($db); + } + return $instance; + } + + + + /** + * Creates a new instance. + * + * Sets $this->sizeSearchHistory to $GLOBALS['sizeSearchHistory'] or 10 + * if the global variable is not defined. + * + * @param DB $db Database object + */ + public function __construct($db) + { + $this->db = $db; + $this->tablename = $GLOBALS['tableprefix'] . 'searchhistory'; + if (isset($GLOBALS['sizeSearchHistory'])) { + $this->sizeSearchHistory = $GLOBALS['sizeSearchHistory']; + } else { + $this->sizeSearchHistory = 10; + } + } + + + + /** + * Adds a new search to the search history. + * Automatically deletes the oldest search when the number of + * searches is larger than $sizeSearchHistory. + * + * @param string $terms Search terms separated by spaces + * @param string $range - 'all' - search was in all bookmarks + * - 'watchlist' - searched in watchlist + * - any username to show that the search happened + * in his own bookmarks. + * @param integer $nbResults Number of search result rows + * @param integer $uId ID of user that searched + * + * @return boolean True if it has been added, false if not + */ + public function addSearch($terms, $range, $nbResults, $uId = 0) + { + if (strlen($terms) == 0) { + return false; + } + $datetime = gmdate('Y-m-d H:i:s', time()); + + //Insert values + $values = array( + 'shTerms' => $terms, + 'shRange' => $range, + 'shDatetime' => $datetime, + 'shNbResults' => $nbResults, + 'uId' => $uId + ); + $sql = 'INSERT INTO ' . $this->getTableName() + . ' ' . $this->db->sql_build_array('INSERT', $values); + + $this->db->sql_transaction('begin'); + if (!($dbresult = $this->db->sql_query($sql))) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not insert search history', + '', __LINE__, __FILE__, $sql, $this->db + ); + return false; + } + + if ($this->sizeSearchHistory != -1 + && $this->countSearches() > $this->sizeSearchHistory + ) { + $this->deleteOldestSearch(); + } + + return true; + } + + + + /** + * Returns searches with the given features. + * + * @param string $range - 'all' - search was in all bookmarks + * - 'watchlist' - searched in watchlist + * - any username to show that the search happened + * in his own bookmarks. + * @param integer $uId Id of the user who searched. null for any users + * @param integer $nb Number of bookmarks to retrieve (paging) + * @param integer $start Number of bookmark to begin with (paging) + * @param boolean $distinct If the search terms shall be distinct + * @param boolean $withResults Only return searches that had at least one result + * + * @return array Array of search history database rows + */ + public function getAllSearches( + $range = null, $uId = null, $nb = null, + $start = null, $distinct = false, $withResults = false + ) { + $sql = 'SELECT DISTINCT(shTerms),' + . ' shId, shRange, shNbResults, shDatetime, uId'; + $sql.= ' FROM '. $this->getTableName(); + $sql.= ' WHERE 1=1'; + if ($range != null) { + $sql.= ' AND shRange = "'.$range.'"'; + } else { + $sql.= ' AND shRange = "all"'; + } + if ($uId != null) { + $sql.= ' AND uId = '.$uId; + } + if ($withResults == true) { + $sql.= ' AND shNbResults > 0'; + } + if ($distinct) { + $sql.= ' GROUP BY shTerms'; + } + $sql.= ' ORDER BY shId DESC'; + + if (!($dbresult = $this->db->sql_query_limit($sql, $nb, $start))) { + message_die( + GENERAL_ERROR, 'Could not get searches', + '', __LINE__, __FILE__, $sql, $this->db + ); + return false; + } + + $searches = array(); + while ($row = $this->db->sql_fetchrow($dbresult)) { + $searches[] = $row; + } + $this->db->sql_freeresult($dbresult); + return $searches; + } + + + + /** + * Counts the number of searches that have been made in total. + * + * @return integer Number of searches + */ + public function countSearches() + { + $sql = 'SELECT COUNT(*) AS `total` FROM '. $this->getTableName(); + if (!($dbresult = $this->db->sql_query($sql)) + || (!($row = $this->db->sql_fetchrow($dbresult))) + ) { + message_die( + GENERAL_ERROR, 'Could not get total searches', + '', __LINE__, __FILE__, $sql, $this->db + ); + return false; + } + $this->db->sql_freeresult($dbresult); + return $row['total']; + } + + + + /** + * This function allows to limit the number of saved searches + * by deleting the oldest one + * + * @return boolean True when all went well, false in case of an error + */ + public function deleteOldestSearch() + { + $sql = 'DELETE FROM '.$this->getTableName(); + // warning: here the limit is important + $sql .= ' ORDER BY shId ASC LIMIT 1'; + + $this->db->sql_transaction('begin'); + if (!($dbresult = $this->db->sql_query($sql))) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not delete bookmarks', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } + + return true; + } + + + + /** + * Deletes all search history entries that have been made by the user + * with the given ID. + * + * @param integer $uId ID of the user + * + * @return boolean True when all went well, false in case of an error + */ + public function deleteSearchHistoryForUser($uId) + { + $query = 'DELETE FROM '. $this->getTableName() + . ' WHERE uId = ' . intval($uId); + + if (!($dbresult = $this->db->sql_query($query))) { + message_die( + GENERAL_ERROR, 'Could not delete search history', '', + __LINE__, __FILE__, $query, $this->db + ); + return false; + } + + return true; + } + + + + /** + * Deletes all search history entries. + * + * @return void + */ + public function deleteAll() + { + $query = 'TRUNCATE TABLE `'. $this->getTableName() .'`'; + $this->db->sql_query($query); + } + +} +?> diff --git a/src/SemanticScuttle/Service/Tag.php b/src/SemanticScuttle/Service/Tag.php new file mode 100644 index 0000000..bd6bf70 --- /dev/null +++ b/src/SemanticScuttle/Service/Tag.php @@ -0,0 +1,160 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ + +/** + * SemanticScuttle tag management service. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_Tag extends SemanticScuttle_DbService +{ + /** + * Returns the single service instance + * + * @param DB $db Database object + * + * @return SemanticScuttle_Service + */ + public static function getInstance($db) + { + static $instance; + if (!isset($instance)) { + $instance = new self($db); + } + return $instance; + } + + public function __construct($db) + { + $this->db = $db; + $this->tablename = $GLOBALS['tableprefix'] .'tags'; + } + + function getDescription($tag, $uId) { + $query = 'SELECT tag, uId, tDescription'; + $query.= ' FROM '.$this->getTableName(); + $query.= ' WHERE tag = \''. $this->db->sql_escape($tag) . "'"; + $query.= ' AND uId = ' . intval($uId); + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not get tag description', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + $row = $this->db->sql_fetchrow($dbresult); + $this->db->sql_freeresult($dbresult); + if ($row) { + return $row; + } else { + return array('tDescription'=>''); + } + } + + function existsDescription($tag, $uId) { + $query = 'SELECT tag, uId, tDescription'; + $query.= ' FROM '.$this->getTableName(); + $query.= ' WHERE tag = \'' . $this->db->sql_escape($tag) . "'"; + $query.= ' AND uId = "' . intval($uId) . '"'; + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not get tag description', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + $row = $this->db->sql_fetchrow($dbresult); + $this->db->sql_freeresult($dbresult); + if ($row) { + return true; + } else { + return false; + } + } + + function getAllDescriptions($tag) { + $query = 'SELECT tag, uId, tDescription'; + $query.= ' FROM '.$this->getTableName(); + $query.= ' WHERE tag = \''. $this->db->sql_escape($tag) . "'"; + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not get tag description', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + $rowset = $this->db->sql_fetchrowset($dbresult); + $this->db->sql_freeresult($dbresult); + return $rowset; + } + + function updateDescription($tag, $uId, $desc) { + if($this->existsDescription($tag, $uId)) { + $query = 'UPDATE '.$this->getTableName(); + $query.= ' SET tDescription= \'' . $this->db->sql_escape($desc) . "'"; + $query.= ' WHERE tag=\'' . $this->db->sql_escape($tag) . "' AND uId=" . intval($uId); + } else { + $values = array('tag'=>$tag, 'uId'=>$uId, 'tDescription'=>$desc); + $query = 'INSERT INTO '. $this->getTableName() .' '. $this->db->sql_build_array('INSERT', $values); + } + + $this->db->sql_transaction('begin'); + if (!($dbresult = $this->db->sql_query($query))) { + $this->db->sql_transaction('rollback'); + message_die(GENERAL_ERROR, 'Could not delete bookmarks', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + $this->db->sql_transaction('commit'); + return true; + } + + function renameTag($uId, $oldName, $newName) { + $newname = $this->normalize($newName); + + $query = 'UPDATE `'. $this->getTableName() .'`'; + $query.= ' SET tag=\'' . $this->db->sql_escape($newName) . "'"; + $query.= ' WHERE tag=\'' . $this->db->sql_escape($oldName) . "'"; + $query.= ' AND uId=' . intval($uId); + $this->db->sql_query($query); + return true; + } + + /* normalize the input tags which could be a string or an array*/ + function normalize($tags) { + //clean tags from strange characters + $tags = str_replace(array('"', '\'', '/'), "_", $tags); + + //normalize + if(!is_array($tags)) { + $tags = utf8_strtolower(trim($tags)); + } else { + $tags = array_filter($tags);//remove empty values + foreach($tags as $i => $tag) { + $tags[$i] = utf8_strtolower(trim($tags[$i])); + } + } + return $tags; + } + + function deleteAll() { + $query = 'TRUNCATE TABLE `'. $this->getTableName() .'`'; + $this->db->sql_query($query); + } + +} +?> diff --git a/src/SemanticScuttle/Service/Tag2Tag.php b/src/SemanticScuttle/Service/Tag2Tag.php new file mode 100644 index 0000000..9dddc44 --- /dev/null +++ b/src/SemanticScuttle/Service/Tag2Tag.php @@ -0,0 +1,470 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ + +/** + * SemanticScuttle tag-to-tag service which works with tag relations. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_Tag2Tag extends SemanticScuttle_DbService +{ + /** + * Returns the single service instance + * + * @param DB $db Database object + * + * @return SemanticScuttle_Service + */ + public static function getInstance($db) + { + static $instance; + if (!isset($instance)) { + $instance = new self($db); + } + return $instance; + } + + + function __construct($db) + { + $this->db =$db; + $this->tablename = $GLOBALS['tableprefix'] .'tags2tags'; + } + + /** + * Add a tag-to-tag link relation + * + * @param string $tag1 First tag + * @param string $tag2 Second tag + * @param string $relationType Relation: + * "=" (both tags are equal) + * ">" (tag2 is part of tag1) + * @param integer $uId User ID + * + * @return boolean True when it was created, false in case of + * an error or when the link already existed. + */ + public function addLinkedTags($tag1, $tag2, $relationType, $uId) + { + $tagservice = SemanticScuttle_Service_Factory::get('Tag'); + $tag1 = $tagservice->normalize($tag1); + $tag2 = $tagservice->normalize($tag2); + + if ($tag1 == $tag2 || strlen($tag1) == 0 || strlen($tag2) == 0 + || ($relationType != '>' && $relationType != '=') + || !is_numeric($uId) || $uId<=0 + || ($this->existsLinkedTags($tag1, $tag2, $relationType, $uId)) + ) { + return false; + } + + $values = array( + 'tag1' => $tag1, + 'tag2' => $tag2, + 'relationType' => $relationType, + 'uId' => $uId + ); + $query = 'INSERT INTO ' . $this->getTableName() + . ' ' . $this->db->sql_build_array('INSERT', $values); + + //die($query); + if (!($dbresult = $this->db->sql_query($query))) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not attach tag to tag', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } + $this->db->sql_transaction('commit'); + + // Update stats and cache + $this->update($tag1, $tag2, $relationType, $uId); + + return true; + } + + + + /** + * Same as getLinkedTags(), but only tags that have been created + * by admin users are returned. + * + * @param string $tag Tag to get related tags for + * @param string $relationType Type of tag-to-tag relation: >, < or = + * @param boolean $inverseRelation Reverse relation (parent -> child) + * @param array $stopList Array of tags that shall not be returned + * + * @return array Array of tag names + * + * @see getLinkedTags() + */ + public function getAdminLinkedTags( + $tag, $relationType, $inverseRelation = false, $stopList = array() + ) { + // look for admin ids + $userservice = SemanticScuttle_Service_Factory :: get('User'); + $adminIds = $userservice->getAdminIds(); + + //ask for their linked tags + return $this->getLinkedTags( + $tag, $relationType, $adminIds, $inverseRelation, $stopList + ); + } + + + + /** + * Returns an array of tags that are in relation to the given $tag. + * + * @param string $tag Tag to get related tags for + * @param string $relationType Type of tag-to-tag relation: >, < or = + * @param boolean $inverseRelation Reverse relation (parent -> child) + * @param array $stopList Array of tags that shall not be returned + * + * @return array Array of tag names + */ + public function getLinkedTags( + $tag, $relationType, $uId = null, $inverseRelation = false, $stopList = array() + ) { + // Set up the SQL query. + if($inverseRelation) { + $queriedTag = "tag1"; + $givenTag = "tag2"; + } else { + $queriedTag = "tag2"; + $givenTag = "tag1"; + } + + $query = "SELECT DISTINCT ". $queriedTag ." as 'tag'"; + $query.= " FROM `". $this->getTableName() ."`"; + $query.= " WHERE 1=1"; + if($tag !=null) { + $query.= " AND ". $givenTag ." = '". $this->db->sql_escape($tag) ."'"; + } + if($relationType) { + $query.= " AND relationType = '". $this->db->sql_escape($relationType) ."'"; + } + if(is_array($uId)) { + $query.= " AND ( 1=0 "; //tricks always false + foreach($uId as $u) { + $query.= " OR uId = '".intval($u)."'"; + } + $query.= " ) "; + } elseif($uId != null) { + $query.= " AND uId = '".intval($uId)."'"; + } + //die($query); + if (! ($dbresult = $this->db->sql_query($query)) ){ + message_die(GENERAL_ERROR, 'Could not get related tags', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + $rowset = $this->db->sql_fetchrowset($dbresult); + $output = array(); + foreach($rowset as $row) { + if(!in_array($row['tag'], $stopList)) { + $output[] = $row['tag']; + } + } + + //bijective case for '=' + if($relationType == '=' && $inverseRelation == false) { + //$stopList[] = $tag; + $bijectiveOutput = $this->getLinkedTags($tag, $relationType, $uId, true, $stopList); + $output = array_merge($output, $bijectiveOutput); + //$output = array_unique($output); // remove duplication + } + + $this->db->sql_freeresult($dbresult); + return $output; + } + + /* + * Returns all linked tags (all descendants if relation is >, + * all synonyms if relation is = ) + * $stopList allows to avoid cycle (a > b > a) between tags + */ + function getAllLinkedTags($tag1, $relationType, $uId, $stopList=array()) { + if(in_array($tag1, $stopList) || $tag1 == '') { + return array(); + } + + // try to find data in cache + $tcs = SemanticScuttle_Service_Factory::get('TagCache'); + if(count($stopList) == 0) { + $activatedCache = true; + } else { + $activatedCache = false; + } + + // look for existing links + $stopList[] = $tag1; + $linkedTags = $this->getLinkedTags($tag1, $relationType, $uId, false, $stopList); + if($relationType != '=') { + $linkedTags = array_merge($linkedTags, $this->getLinkedTags($tag1, '=', $uId, false, $stopList)); + } + + if(count($linkedTags) == 0) { + return array(); + + } else { + // use cache if possible + if($activatedCache) { + if($relationType == '>') { + $output = $tcs->getChildren($tag1, $uId); + } elseif($relationType == '=') { + $output = $tcs->getSynonyms($tag1, $uId); + } + if(count($output)>0) { + return $output; + } + } + + // else compute the links + $output = array(); + + foreach($linkedTags as $linkedTag) { + $allLinkedTags = $this->getAllLinkedTags($linkedTag, $relationType, $uId, $stopList); + $output[] = $linkedTag; + if(is_array($allLinkedTags)) { + $output = array_merge($output, $allLinkedTags); + } else { + $output[] = $allLinkedTags; + } + } + + // and save in cache + if($activatedCache == true && $uId>0) { + $tcs->updateTag($tag1, $relationType, $output, $uId); + } + + //$output = array_unique($output); // remove duplication + return $output; + + } + } + + function getOrphewTags($relationType, $uId = 0, $limit = null, $orderBy = null) { + $query = "SELECT DISTINCT tts.tag1 as tag"; + $query.= " FROM `". $this->getTableName() ."` tts"; + if($orderBy != null) { + $tsts =SemanticScuttle_Service_Factory::get('TagStat'); + $query.= ", ".$tsts->getTableName() ." tsts"; + } + $query.= " WHERE tts.tag1 <> ALL"; + $query.= " (SELECT DISTINCT tag2 FROM `". $this->getTableName() ."`"; + $query.= " WHERE relationType = '" . $this->db->sql_escape($relationType) . "'"; + if($uId > 0) { + $query.= " AND uId = '".intval($uId)."'"; + } + $query.= ")"; + if($uId > 0) { + $query.= " AND tts.uId = '".intval($uId)."'"; + } + + switch($orderBy) { + case "nb": + $query.= " AND tts.tag1 = tsts.tag1"; + $query.= " AND tsts.relationType = '" . $this->db->sql_escape($relationType) . "'"; + if($uId > 0) { + $query.= " AND tsts.uId = " . intval($uId); + } + $query.= " ORDER BY tsts.nb DESC"; + break; + case "depth": // by nb of descendants + $query.= " AND tts.tag1 = tsts.tag1"; + $query.= " AND tsts.relationType = '" . $this->db->sql_escape($relationType) . "'"; + if($uId > 0) { + $query.= " AND tsts.uId = " . intval($uId); + } + $query.= " ORDER BY tsts.depth DESC"; + break; + case "nbupdate": + $query.= " AND tts.tag1 = tsts.tag1"; + $query.= " AND tsts.relationType = '" . $this->db->sql_escape($relationType) . "'"; + if($uId > 0) { + $query.= " AND tsts.uId = " . intval($uId); + } + $query.= " ORDER BY tsts.nbupdate DESC"; + break; + } + + if($limit != null) { + $query.= " LIMIT 0," . intval($limit); + } + + if (! ($dbresult = $this->db->sql_query($query)) ){ + message_die(GENERAL_ERROR, 'Could not get linked tags', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + $output = $this->db->sql_fetchrowset($dbresult); + $this->db->sql_freeresult($dbresult); + return $output; + } + + function getMenuTags($uId) { + if(strlen($GLOBALS['menuTag']) < 1) { + return array(); + } else { + // we don't use the getAllLinkedTags function in order to improve performance + $query = "SELECT tag2 as 'tag', COUNT(tag2) as 'count'"; + $query.= " FROM `". $this->getTableName() ."`"; + $query.= " WHERE tag1 = '" . $this->db->sql_escape($GLOBALS['menuTag']) . "'"; + $query.= " AND relationType = '>'"; + if($uId > 0) { + $query.= " AND uId = " . intval($uId); + } + $query.= " GROUP BY tag2"; + $query.= " ORDER BY count DESC"; + $query.= " LIMIT 0, " . intval($GLOBALS['maxSizeMenuBlock']); + + if (! ($dbresult = $this->db->sql_query($query)) ){ + message_die(GENERAL_ERROR, 'Could not get linked tags', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + $output = $this->db->sql_fetchrowset($dbresult); + $this->db->sql_freeresult($dbresult); + return $output; + } + } + + + function existsLinkedTags($tag1, $tag2, $relationType, $uId) { + + //$tag1 = mysql_real_escape_string($tag1); + //$tag2 = mysql_real_escape_string($tag2); + + $query = "SELECT tag1, tag2, relationType, uId FROM `". $this->getTableName() ."`"; + $query.= " WHERE tag1 = '" . $this->db->sql_escape($tag1) . "'"; + $query.= " AND tag2 = '" . $this->db->sql_escape($tag2) . "'"; + $query.= " AND relationType = '" . $this->db->sql_escape($relationType) . "'"; + $query.= " AND uId = " . intval($uId); + + //echo($query."<br>\n"); + + $dbres = $this->db->sql_query($query); + $hasTags = $this->db->sql_numrows($dbres) > 0; + $this->db->sql_freeresult($dbres); + return $hasTags; + } + + function getLinks($uId) { + $query = "SELECT tag1, tag2, relationType, uId FROM `". $this->getTableName() ."`"; + $query.= " WHERE 1=1"; + if($uId > 0) { + $query.= " AND uId = " . intval($uId); + } + + $dbres = $this->db->sql_query($query); + $rowset = $this->db->sql_fetchrowset($dbres); + $this->db->sql_freeresult($dbres); + return $rowset; + } + + function removeLinkedTags($tag1, $tag2, $relationType, $uId) { + if(($tag1 != '' && $tag1 == $tag2) || + ($relationType != ">" && $relationType != "=" && $relationType != "") || + ($tag1 == '' && $tag2 == '')) { + return false; + } + $query = 'DELETE FROM '. $this->getTableName(); + $query.= ' WHERE 1=1'; + $query.= strlen($tag1)>0 ? ' AND tag1 = \''. $this->db->sql_escape($tag1) . "'" : ''; + $query.= strlen($tag2)>0 ? ' AND tag2 = \''. $this->db->sql_escape($tag2) . "'" : ''; + $query.= strlen($relationType)>0 ? ' AND relationType = \''. $this->db->sql_escape($relationType) . "'" : ''; + $query.= strlen($uId)>0 ? ' AND uId = '. intval($uId) : ''; + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not remove tag relation', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + + // Update stats and cache + $this->update($tag1, $tag2, $relationType, $uId); + + $this->db->sql_freeresult($dbresult); + return true; + } + + function removeLinkedTagsForUser($uId) { + $query = 'DELETE FROM '. $this->getTableName(); + $query.= ' WHERE uId = '. intval($uId); + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not remove tag relation', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + + // Update stats and cache + $this->update('', '', '', $uId); + + $this->db->sql_freeresult($dbresult); + return true; + } + + function renameTag($uId, $oldName, $newName) { + $tagservice =SemanticScuttle_Service_Factory::get('Tag'); + $newName = $tagservice->normalize($newName); + + $query = 'UPDATE `'. $this->getTableName() .'`'; + $query.= ' SET tag1=\'' . $this->db->sql_escape($newName) ."'"; + $query.= ' WHERE tag1=\'' . $this->db->sql_escape($oldName) . "'"; + $query.= ' AND uId=' . intval($uId); + $this->db->sql_query($query); + + $query = 'UPDATE `'. $this->getTableName() .'`'; + $query.= ' SET tag2=\'' . $this->db->sql_escape($newName) . "'"; + $query.= ' WHERE tag2=\'' . $this->db->sql_escape($oldName) . "'"; + $query.= ' AND uId=' . intval($uId); + $this->db->sql_query($query); + + + // Update stats and cache + $this->update($oldName, NULL, '=', $uId); + $this->update($oldName, NULL, '>', $uId); + $this->update($newName, NULL, '=', $uId); + $this->update($newName, NULL, '>', $uId); + + return true; + + } + + function update($tag1, $tag2, $relationType, $uId) { + $tsts =SemanticScuttle_Service_Factory::get('TagStat'); + $tsts->updateStat($tag1, $relationType, $uId); + + $tcs = SemanticScuttle_Service_Factory::get('TagCache'); + $tcs->deleteByUser($uId); + } + + function deleteAll() { + $query = 'TRUNCATE TABLE `'. $this->getTableName() .'`'; + $this->db->sql_query($query); + + $tsts =SemanticScuttle_Service_Factory::get('TagStat'); + $tsts->deleteAll(); + } + +} +?> diff --git a/src/SemanticScuttle/Service/TagCache.php b/src/SemanticScuttle/Service/TagCache.php new file mode 100644 index 0000000..f8a28af --- /dev/null +++ b/src/SemanticScuttle/Service/TagCache.php @@ -0,0 +1,397 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ + +/** + * SemanticScuttle tag caching service. + * + * This class infers on relation between tags by storing all + * the including tags or synonymous tag. + * For example, if the user creates: tag1>tag2>tag3, the system + * can infer that tag is included into tag1. + * Instead of computing this relation several times, it is saved + * into this current table. + * For synonymy, this table stores also the group of synonymous tags. + * The table must be updated for each modification of + * the relations between tags. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_TagCache extends SemanticScuttle_DbService +{ + + /** + * Returns the single service instance + * + * @param DB $db Database object + * + * @return SemanticScuttle_Service + */ + public static function getInstance($db) + { + static $instance; + if (!isset($instance)) { + $instance = new self($db); + } + return $instance; + } + + protected function __construct($db) + { + $this->db =$db; + $this->tablename = $GLOBALS['tableprefix'] .'tagscache'; + } + + function getChildren($tag1, $uId) { + $tagservice =SemanticScuttle_Service_Factory::get('Tag'); + $tag1 = $tagservice->normalize($tag1); + + if($tag1 == '') return false; + + $query = "SELECT DISTINCT tag2 as 'tag'"; + $query.= " FROM `". $this->getTableName() ."`"; + $query.= " WHERE relationType = '>'"; + $query.= " AND tag1 = '" . $this->db->sql_escape($tag1) . "'"; + $query.= " AND uId = " . intval($uId); + + //die($query); + if (! ($dbresult = $this->db->sql_query($query)) ){ + message_die(GENERAL_ERROR, 'Could not get related tags', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + + $rowset = $this->db->sql_fetchrowset($dbresult); + $output = array(); + foreach($rowset as $row) { + $output[] = $row['tag']; + } + + $this->db->sql_freeresult($dbresult); + return $output; + } + + function addChild($tag1, $tag2, $uId) { + $tagservice =SemanticScuttle_Service_Factory::get('Tag'); + $tag1 = $tagservice->normalize($tag1); + $tag2 = $tagservice->normalize($tag2); + + if($tag1 == $tag2 || strlen($tag1) == 0 || strlen($tag2) == 0 + || ($this->existsChild($tag1, $tag2, $uId))) { + return false; + } + + $values = array('tag1' => $tag1, 'tag2' => $tag2, 'relationType'=> '>', 'uId'=> $uId); + $query = 'INSERT INTO '. $this->getTableName() .' '. $this->db->sql_build_array('INSERT', $values); + //die($query); + if (!($dbresult = $this->db->sql_query($query))) { + $this->db->sql_transaction('rollback'); + message_die(GENERAL_ERROR, 'Could not add tag cache inference', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + $this->db->sql_transaction('commit'); + } + + function removeChild($tag1, $tag2, $uId) { + if(($tag1 != '' && $tag1 == $tag2) || + ($tag1 == '' && $tag2 == '' && $uId == '')) { + return false; + } + + $query = 'DELETE FROM '. $this->getTableName(); + $query.= ' WHERE 1=1'; + $query.= strlen($tag1)>0 ? ' AND tag1 = \''. $this->db->sql_escape($tag1) . "'" : ''; + $query.= strlen($tag2)>0 ? ' AND tag2 = \''. $this->db->sql_escape($tag2) . "'" : ''; + $query.= ' AND relationType = ">"'; + $query.= strlen($uId)>0 ? ' AND uId = ' . intval($uId) : ''; + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not remove tag cache inference', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + } + + function removeChildren($tag1, $uId) { + $this->removeChild($tag1, NULL, $uId); + } + + function existsChild($tag1, $tag2, $uId) { + $tagservice =SemanticScuttle_Service_Factory::get('Tag'); + $tag1 = $tagservice->normalize($tag1); + $tag2 = $tagservice->normalize($tag2); + + $query = "SELECT tag1, tag2, relationType, uId FROM `". $this->getTableName() ."`"; + $query.= " WHERE tag1 = '" . $this->db->sql_escape($tag1) . "'"; + $query.= " AND tag2 = '" . $this->db->sql_escape($tag2) . "'"; + $query.= " AND relationType = '>'"; + $query.= " AND uId = " . intval($uId); + + //echo($query."<br>\n"); + + $dbres = $this->db->sql_query($query); + $rows = $this->db->sql_numrows($dbres); + $this->db->sql_freeresult($dbres); + return $rows > 0; + } + + /* + * Synonyms of a same concept are a group. A group has one main synonym called key + * and a list of synonyms called values. + */ + function addSynonym($tag1, $tag2, $uId) { + + if($tag1 == $tag2 || strlen($tag1) == 0 || strlen($tag2) == 0 + || ($this->existsSynonym($tag1, $tag2, $uId))) { + return false; + } + + $case1 = '0'; // not in DB + if($this->_isSynonymKey($tag1, $uId)) { + $case1 = 'key'; + } elseif($this->_isSynonymValue($tag1, $uId)) { + $case1 = 'value'; + } + + $case2 = '0'; // not in DB + if($this->_isSynonymKey($tag2, $uId)) { + $case2 = 'key'; + } elseif($this->_isSynonymValue($tag2, $uId)) { + $case2 = 'value'; + } + $case = $case1.$case2; + + // all the possible cases + switch ($case) { + case 'keykey': + $values = $this->_getSynonymValues($tag2, $uId); + $this->removeSynonymGroup($tag2, $uId); + foreach($values as $value) { + $this->addSynonym($tag1, $value, $uId); + } + $this->addSynonym($tag1, $tag2, $uId); + break; + + case 'valuekey': + $key = $this->_getSynonymKey($tag1, $uId); + $this->addSynonym($key, $tag2, $uId); + break; + + case 'keyvalue': + $this->addSynonym($tag2, $tag1, $uId); + break; + case 'valuevalue': + $key1 = $this->_getSynonymKey($tag1, $uId); + $key2 = $this->_getSynonymKey($tag2, $uId); + $this->addSynonym($key1, $key2, $uId); + break; + case '0value': + $key = $this->_getSynonymKey($tag2, $uId); + $this->addSynonym($key, $tag1, $uId); + break; + case 'value0': + $this->addSynonym($tag2, $tag1, $uId); + break; + case '0key': + $this->addSynonym($tag2, $tag1, $uId); + break; + default: + $values = array('tag1' => $tag1, 'tag2' => $tag2, 'relationType'=> '=', 'uId'=> $uId); + $query = 'INSERT INTO '. $this->getTableName() .' '. $this->db->sql_build_array('INSERT', $values); + //die($query); + if (!($dbresult = $this->db->sql_query($query))) { + $this->db->sql_transaction('rollback'); + message_die(GENERAL_ERROR, 'Could not add tag cache synonymy', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + $this->db->sql_transaction('commit'); + break; + } + } + + function removeSynonymGroup($tag1, $uId) { + $query = 'DELETE FROM '. $this->getTableName(); + $query.= ' WHERE 1=1'; + $query.= ' AND tag1 = \''. $this->db->sql_escape($tag1) . "'"; + $query.= ' AND relationType = "="'; + $query.= ' AND uId = ' . intval($uId); + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not remove tag cache inference', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + } + + function _isSynonymKey($tag1, $uId) { + $tagservice =SemanticScuttle_Service_Factory::get('Tag'); + $tag1 = $tagservice->normalize($tag1); + + $query = "SELECT tag1 FROM `". $this->getTableName() ."`"; + $query.= " WHERE tag1 = '" . $this->db->sql_escape($tag1) ."'"; + $query.= " AND relationType = '='"; + $query.= " AND uId = " . intval($uId); + + $dbres = $this->db->sql_query($query); + $rows = $this->db->sql_numrows($dbres); + $this->db->sql_freeresult($dbres); + return $rows > 0; + } + + function _isSynonymValue($tag2, $uId) { + $tagservice =SemanticScuttle_Service_Factory::get('Tag'); + $tag2 = $tagservice->normalize($tag2); + + $query = "SELECT tag2 FROM `". $this->getTableName() ."`"; + $query.= " WHERE tag2 = '" . $this->db->sql_escape($tag2) . "'"; + $query.= " AND relationType = '='"; + $query.= " AND uId = " . intval($uId); + + $dbres = $this->db->sql_query($query); + $rows = $this->db->sql_numrows($dbres); + $this->db->sql_freeresult($dbres); + return $rows > 0; + } + + function getSynonyms($tag1, $uId) { + $values = array(); + if($this->_isSynonymKey($tag1, $uId)) { + $values = $this->_getSynonymValues($tag1, $uId); + } elseif($this->_isSynonymValue($tag1, $uId)) { + $key = $this->_getSynonymKey($tag1, $uId); + $values = $this->_getSynonymValues($key, $uId, $tag1); + $values[] = $key; + } + return $values; + } + + function _getSynonymKey($tag2, $uId) { + $tagservice =SemanticScuttle_Service_Factory::get('Tag'); + $tag2 = $tagservice->normalize($tag2); + + if($this->_isSynonymKey($tag2, $uId)) return $tag2; + + if($tag2 == '') return false; + + $query = "SELECT DISTINCT tag1 as 'tag'"; + $query.= " FROM `". $this->getTableName() ."`"; + $query.= " WHERE relationType = '='"; + $query.= " AND tag2 = '" . $this->db->sql_escape($tag2) . "'"; + $query.= " AND uId = " . intval($uId); + + //die($query); + if (! ($dbresult = $this->db->sql_query($query)) ){ + message_die(GENERAL_ERROR, 'Could not get related tags', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + $row = $this->db->sql_fetchrow($dbresult); + $this->db->sql_freeresult($dbresult); + return $row['tag']; + } + + /* + * Return values associated with a key. + * $tagExcepted allows to hide a value. + * + * @return array Array with tag names + */ + protected function _getSynonymValues($tag1, $uId, $tagExcepted = NULL) + { + $tagservice =SemanticScuttle_Service_Factory::get('Tag'); + $tag1 = $tagservice->normalize($tag1); + $tagExcepted = $tagservice->normalize($tagExcepted); + + if($tag1 == '') return false; + + $query = "SELECT DISTINCT tag2 as 'tag'"; + $query .= " FROM `". $this->getTableName() ."`"; + $query .= " WHERE relationType = '='"; + $query .= " AND tag1 = '" . $this->db->sql_escape($tag1) . "'"; + $query .= " AND uId = " . intval($uId); + $query .= $tagExcepted != '' + ? " AND tag2!='" . $this->db->sql_escape($tagExcepted) . "'" + : ''; + + if (! ($dbresult = $this->db->sql_query($query)) ){ + message_die( + GENERAL_ERROR, 'Could not get related tags', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } + + $rowset = $this->db->sql_fetchrowset($dbresult); + + $output = array(); + foreach($rowset as $row) { + $output[] = $row['tag']; + } + + $this->db->sql_freeresult($dbresult); + return $output; + } + + function existsSynonym($tag1, $tag2, $uId) { + if($this->_getSynonymKey($tag1, $uId) == $tag2 || $this->_getSynonymKey($tag2, $uId) == $tag1) { + return true; + } else { + return false; + } + } + + + function updateTag($tag1, $relationType, $otherTags, $uId) { + if($relationType == '=') { + if($this->getSynonyms($tag1, $uId)) { // remove previous data avoiding unconstistency + $this->removeSynonymGroup($tag1, $uId); + } + + foreach($otherTags as $tag2) { + $this->addSynonym($tag1, $tag2, $uId); + } + } elseif($relationType == '>') { + if(count($this->getChildren($tag1, $uId))>0) { // remove previous data avoiding unconstistency + $this->removeChildren($tag1); + } + + foreach($otherTags as $tag2) { + $this->addChild($tag1, $tag2, $uId); + } + } + } + + function deleteByUser($uId) { + $query = 'DELETE FROM '. $this->getTableName() .' WHERE uId = '. intval($uId); + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not delete user tags cache', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + return true; + + } + + function deleteAll() { + $query = 'TRUNCATE TABLE `'. $this->getTableName() .'`'; + $this->db->sql_query($query); + } + +} +?> diff --git a/src/SemanticScuttle/Service/TagStat.php b/src/SemanticScuttle/Service/TagStat.php new file mode 100644 index 0000000..af8d110 --- /dev/null +++ b/src/SemanticScuttle/Service/TagStat.php @@ -0,0 +1,233 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ + +/** + * SemanticScuttle tag statistics service. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_TagStat extends SemanticScuttle_DbService +{ + /** + * Returns the single service instance + * + * @param DB $db Database object + * + * @return SemanticScuttle_Service + */ + public static function getInstance($db) + { + static $instance; + if (!isset($instance)) { + $instance = new self($db); + } + return $instance; + } + + protected function __construct($db) + { + $this->db = $db; + $this->tablename = $GLOBALS['tableprefix'] .'tagsstats'; + } + + function getNbChildren($tag1, $relationType, $uId) { + $tts =SemanticScuttle_Service_Factory::get('Tag2Tag'); + $query = "SELECT tag1, relationType, uId FROM `". $tts->getTableName() ."`"; + $query.= " WHERE tag1 = '" .$tag1 ."'"; + $query.= " AND relationType = '". $relationType ."'"; + $query.= " AND uId = '".$uId."'"; + + $dbres = $this->db->sql_query($query); + $rows = $this->db->sql_numrows($dbres); + $this->db->sql_freeresult($dbres); + return $rows; + } + + function getNbDescendants($tag1, $relationType, $uId) { + $query = "SELECT nb FROM `". $this->getTableName() ."`"; + $query.= " WHERE tag1 = '" .$tag1 ."'"; + $query.= " AND relationType = '". $relationType ."'"; + $query.= " AND uId = '".$uId."'"; + + $dbresults = $this->db->sql_query($query); + $row = $this->db->sql_fetchrow($dbresults); + $this->db->sql_freeresult($dbresults); + if($row['nb'] == null) { + return 0; + } else { + return (int) $row['nb']; + } + } + + function getMaxDepth($tag1, $relationType, $uId) { + $query = "SELECT depth FROM `". $this->getTableName() ."`"; + $query.= " WHERE tag1 = '" .$tag1 ."'"; + $query.= " AND relationType = '". $relationType ."'"; + $query.= " AND uId = '".$uId."'"; + + $dbresults = $this->db->sql_query($query); + $row = $this->db->sql_fetchrow($dbresults); + $this->db->sql_freeresult($dbresults); + if($row['depth'] == null) { + return 0; + } else { + return (int) $row['depth']; + }; + } + + function getNbUpdates($tag1, $relationType, $uId) { + $query = "SELECT nbupdate FROM `". $this->getTableName() ."`"; + $query.= " WHERE tag1 = '" .$tag1 ."'"; + $query.= " AND relationType = '". $relationType ."'"; + $query.= " AND uId = '".$uId."'"; + + $dbresults = $this->db->sql_query($query); + $row = $this->db->sql_fetchrow($dbresults); + $this->db->sql_freeresult($dbresults); + if($row['nbupdate'] == null) { + return 0; + } else { + return (int) $row['nbupdate']; + } + } + + function existStat($tag1, $relationType, $uId) { + $query = "SELECT tag1, relationType, uId FROM `". $this->getTableName() ."`"; + $query.= " WHERE tag1 = '" .$tag1 ."'"; + $query.= " AND relationType = '". $relationType ."'"; + $query.= " AND uId = '".$uId."'"; + + $dbres = $this->db->sql_query($query); + $rows = $this->db->sql_numrows($dbres); + $this->db->sql_freeresult($dbres); + return $rows > 0; + } + + function createStat($tag1, $relationType, $uId) { + $query = "INSERT INTO `". $this->getTableName() ."`"; + $query.= "(tag1, relationType, uId)"; + $query.= " VALUES ('".$tag1."','".$relationType."','".$uId."')"; + $this->db->sql_query($query); + } + + function updateStat($tag1, $relationType, $uId=null, $stoplist=array()) { + if(in_array($tag1, $stoplist)) { + return false; + } + + $tts =SemanticScuttle_Service_Factory::get('Tag2Tag'); + $linkedTags = $tts->getLinkedTags($tag1, $relationType, $uId); + $nbDescendants = 0; + $maxDepth = 0; + foreach($linkedTags as $linkedTag) { + $nbDescendants+= 1 + $this->getNbDescendants($linkedTag, $relationType, $uId); + $maxDepth = max($maxDepth, 1 + $this->getMaxDepth($linkedTag, $relationType, $uId)); + } + $this->setNbDescendants($tag1, $relationType, $uId, $nbDescendants); + $this->setMaxDepth($tag1, $relationType, $uId, $maxDepth); + $this->increaseNbUpdate($tag1, $relationType, $uId); + + // propagation to the precedent tags + $linkedTags = $tts->getLinkedTags($tag1, $relationType, $uId, true); + $stoplist[] = $tag1; + foreach($linkedTags as $linkedTag) { + $this->updateStat($linkedTag, $relationType, $uId, $stoplist); + } + } + + function updateAllStat() { + $tts =SemanticScuttle_Service_Factory::get('Tag2Tag'); + + $query = "SELECT tag1, uId FROM `". $tts->getTableName() ."`"; + $query.= " WHERE relationType = '>'"; + + //die($query); + + if (! ($dbresult = $this->db->sql_query($query)) ){ + message_die(GENERAL_ERROR, 'Could not update stats', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + $rowset = $this->db->sql_fetchrowset($dbresult); + foreach($rowset as $row) { + $this->updateStat($row['tag1'], '>', $row['uId']); + } + $this->db->sql_freeresult($dbresult); + } + + function setNbDescendants($tag1, $relationType, $uId, $nb) { + if(!$this->existStat($tag1, $relationType, $uId)) { + $this->createStat($tag1, $relationType, $uId); + } + $query = "UPDATE `". $this->getTableName() ."`"; + $query.= " SET nb = ". $nb; + $query.= " WHERE tag1 = '" .$tag1 ."'"; + $query.= " AND relationType = '". $relationType ."'"; + $query.= " AND uId = '".$uId."'"; + $this->db->sql_freeresult($this->db->sql_query($query)); + } + + function setMaxDepth($tag1, $relationType, $uId, $depth) { + if(!$this->existStat($tag1, $relationType, $uId)) { + $this->createStat($tag1, $relationType, $uId); + } + $query = "UPDATE `". $this->getTableName() ."`"; + $query.= " SET depth = ". $depth; + $query.= " WHERE tag1 = '" .$tag1 ."'"; + $query.= " AND relationType = '". $relationType ."'"; + $query.= " AND uId = '".$uId."'"; + $this->db->sql_query($query); + } + + function increaseNbUpdate($tag1, $relationType, $uId) { + if(!$this->existStat($tag1, $relationType, $uId)) { + $this->createStat($tag1, $relationType, $uId); + } + $query = "UPDATE `". $this->getTableName() ."`"; + $query.= " SET nbupdate = nbupdate + 1"; + $query.= " WHERE tag1 = '" .$tag1 ."'"; + $query.= " AND relationType = '". $relationType ."'"; + $query.= " AND uId = '".$uId."'"; + + //die($query); + + $this->db->sql_query($query); + } + + function deleteTagStatForUser($uId) { + $query = 'DELETE FROM '. $this->getTableName() .' WHERE uId = '. intval($uId); + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not delete tag stats', '', __LINE__, + __FILE__, $query, $this->db); + return false; + } + + return true; + } + + function deleteAll() { + $query = 'TRUNCATE TABLE `'. $this->getTableName() .'`'; + $this->db->sql_query($query); + } + +} +?> diff --git a/src/SemanticScuttle/Service/Template.php b/src/SemanticScuttle/Service/Template.php new file mode 100644 index 0000000..b5d4cfa --- /dev/null +++ b/src/SemanticScuttle/Service/Template.php @@ -0,0 +1,119 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ + +require_once 'SemanticScuttle/Model/Template.php'; +require_once 'SemanticScuttle/Model/Theme.php'; + +/** + * SemanticScuttle template service. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_Template extends SemanticScuttle_Service +{ + /** + * Full path to template directory. + * + * Set in constructor to + * $GLOBALS['TEMPLATES_DIR'] + * + * @var string + */ + protected $basedir; + + /** + * The template theme to use. + * Set in constructor based on $GLOBALS['theme'] + * + * @var SemanticScuttle_Model_Theme + */ + protected $theme; + + + + /** + * Returns the single service instance + * + * @param DB $db Database object + * + * @return SemanticScuttle_Service + */ + public static function getInstance($db) + { + static $instance; + if (!isset($instance)) { + $instance = new self($db); + } + return $instance; + } + + + + /** + * Create a new instance + */ + protected function __construct() + { + $this->basedir = $GLOBALS['TEMPLATES_DIR']; + $this->theme = new SemanticScuttle_Model_Theme($GLOBALS['theme']); + //FIXME: verify the theme exists + } + + + + /** + * Loads and displays a template file. + * + * @param string $template Template filename relative + * to template dir + * @param array $vars Array of template variables. + * The current theme object will be added + * automatically with name "theme". + * + * @return SemanticScuttle_Model_Template Template object + */ + public function loadTemplate($template, $vars = null) + { + if (substr($template, -4) != '.php') { + $template .= '.php'; + } + + $oldIncPath = get_include_path(); + set_include_path( + $this->basedir . $this->theme->getName() + . PATH_SEPARATOR . $this->basedir . 'default' + //needed since services are instantiated in templates + . PATH_SEPARATOR . $oldIncPath + ); + + $vars['theme'] = $this->theme; + $tpl = new SemanticScuttle_Model_Template( + $template, $vars, $this + ); + $tpl->parse(); + + set_include_path($oldIncPath); + + return $tpl; + } +} + +?>
\ No newline at end of file diff --git a/src/SemanticScuttle/Service/Thumbnails.php b/src/SemanticScuttle/Service/Thumbnails.php new file mode 100644 index 0000000..6151254 --- /dev/null +++ b/src/SemanticScuttle/Service/Thumbnails.php @@ -0,0 +1,59 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Christian Weiske <cweiske@cweiske.de> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ + +/** + * Instantiates the configured website thumbnailer object. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Christian Weiske <cweiske@cweiske.de> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_Thumbnails extends SemanticScuttle_Service +{ + /** + * Instantiates the configured website thumbnailer object. + * + * @return object Website thumbnailer + */ + public function getThumbnailer() + { + if (!isset($GLOBALS['thumbnailsType']) + || $GLOBALS['thumbnailsType'] == '' + ) { + $class = 'SemanticScuttle_Thumbnailer_Null'; + } else { + $class = 'SemanticScuttle_Thumbnailer_' + . ucfirst($GLOBALS['thumbnailsType']); + } + if (!class_exists($class)) { + //PEAR classname to filename rule + $file = str_replace('_', '/', $class) . '.php'; + include_once $file; + } + + $thumbnailer = new $class(); + + if (!isset($GLOBALS['thumbnailsConfig']) + || $GLOBALS['thumbnailsConfig'] == '' + ) { + $thumbnailer->setConfig(null); + } else { + $thumbnailer->setConfig($GLOBALS['thumbnailsConfig']); + } + + return $thumbnailer; + } +} +?> diff --git a/src/SemanticScuttle/Service/User.php b/src/SemanticScuttle/Service/User.php new file mode 100644 index 0000000..434e6b7 --- /dev/null +++ b/src/SemanticScuttle/Service/User.php @@ -0,0 +1,1090 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ + +require_once 'SemanticScuttle/Model/User.php'; + +/** + * SemanticScuttle user management service. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Benjamin Huynh-Kim-Bang <mensonge@users.sourceforge.net> + * @author Christian Weiske <cweiske@cweiske.de> + * @author Eric Dane <ericdane@users.sourceforge.net> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_User extends SemanticScuttle_DbService +{ + /** + * The ID of the currently logged on user. + * NULL when not logged in. + * + * @var integer + */ + protected $currentuserId = null; + + /** + * Currently logged on user from database + * + * @var array + * + * @see getCurrentUserId() + * @see getCurrentUser() + * @see setCurrentUserId() + */ + protected $currentuser = null; + + protected $fields = array( + 'primary' => 'uId', + 'username' => 'username', + 'password' => 'password', + 'privateKey' => 'privateKey' + ); + + protected $profileurl; + protected $sessionkey; + protected $cookiekey; + protected $cookietime = 1209600; // 2 weeks + + /** + * Returns the single service instance + * + * @param sql_db $db Database object + * + * @return SemanticScuttle_Service_User + */ + public static function getInstance($db) + { + static $instance; + if (!isset($instance)) { + $instance = new self($db); + } + return $instance; + } + + protected function __construct($db) + { + $this->db = $db; + $this->tablename = $GLOBALS['tableprefix'] .'users'; + $this->sessionkey = INSTALLATION_ID.'-currentuserid'; + $this->cookiekey = INSTALLATION_ID.'-login'; + $this->profileurl = createURL('profile', '%2$s'); + $this->updateSessionStability(); + } + + /** + * Fetches the desired user row from database, specified by column and value + * + * @param string $fieldname Name of database column to identify user + * @param string $value Value of $fieldname + * + * @return array Database row or boolean false + */ + protected function _getuser($fieldname, $value) + { + $query = 'SELECT * FROM '. $this->getTableName() + . ' WHERE ' . $fieldname . ' = "' . $this->db->sql_escape($value) . '"'; + + if (!($dbresult = $this->db->sql_query($query)) ) { + message_die( + GENERAL_ERROR, 'Could not get user', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } + + $row = $this->db->sql_fetchrow($dbresult); + $this->db->sql_freeresult($dbresult); + if ($row) { + return $row; + } else { + return false; + } + } + + function & getUsers($nb=0) { + $query = 'SELECT * FROM '. $this->getTableName() .' ORDER BY `uId` DESC'; + if($nb>0) { + $query .= ' LIMIT 0, '.$nb; + } + if (! ($dbresult = $this->db->sql_query($query)) ) { + message_die(GENERAL_ERROR, 'Could not get user', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + while ($row = $this->db->sql_fetchrow($dbresult)) { + $users[] = $row; + } + $this->db->sql_freeresult($dbresult); + return $users; + } + + /** + * Returns an array of user objects. + * Array is in order of uids + * + * @param integer $nb Number of users to fetch. + * + * @return array Array of SemanticScuttle_Model_User objects + */ + public function getObjectUsers($nb = 0) + { + $query = 'SELECT * FROM ' . $this->getTableName() + . ' ORDER BY uId DESC'; + + if ($nb > 0) { + $query .= ' LIMIT 0, ' . intval($nb); + } + + if (! ($dbresult = $this->db->sql_query($query)) ) { + message_die( + GENERAL_ERROR, 'Could not get user', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } + + $users = array(); + while ($row = $this->db->sql_fetchrow($dbresult)) { + $users[] = new SemanticScuttle_Model_User( + $row[$this->getFieldName('primary')], + $row[$this->getFieldName('username')] + ); + } + $this->db->sql_freeresult($dbresult); + return $users; + } + + function _randompassword() { + $seed = (integer) md5(microtime()); + mt_srand($seed); + $password = mt_rand(1, 99999999); + $password = substr(md5($password), mt_rand(0, 19), mt_rand(6, 12)); + return $password; + } + + /** + * Updates a single field in the user's database row + * + * @param integer $uId ID of the user + * @param string $fieldname Name of table column to change + * @param string $value New value + * + * @return boolean True if all was well, false if not + */ + public function _updateuser($uId, $fieldname, $value) + { + $updates = array ($fieldname => $value); + $sql = 'UPDATE '. $this->getTableName() + . ' SET '. $this->db->sql_build_array('UPDATE', $updates) + . ' WHERE '. $this->getFieldName('primary') . '=' . intval($uId); + + // Execute the statement. + $this->db->sql_transaction('begin'); + if (!($dbresult = $this->db->sql_query($sql))) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not update user', '', + __LINE__, __FILE__, $sql, $this->db + ); + return false; + } + $this->db->sql_transaction('commit'); + + // Everything worked out, so return true. + return true; + } + + function getProfileUrl($id, $username) { + return sprintf($this->profileurl, urlencode($id), urlencode($username)); + } + + function getUserByUsername($username) { + return $this->_getuser($this->getFieldName('username'), $username); + } + + /** + * Returns user row from database. + * + * @param string $privateKey Private Key + * + * @return array User array from database, false if no user was found + */ + public function getUserByPrivateKey($privateKey) + { + return $this->_getuser($this->getFieldName('privateKey'), $privateKey); + } + + function getObjectUserByUsername($username) { + $user = $this->_getuser($this->getFieldName('username'), $username); + if($user != false) { + return new SemanticScuttle_Model_User( + $user[$this->getFieldName('primary')], $username + ); + } else { + return NULL; + } + } + + /** + * Obtains the ID of the given user name. + * If a user ID is passed, it is returned. + * In case the user does not exist, NULL is returned. + * + * @param string|integer $user User name or user ID + * + * @return integer NULL if not found or the user ID + */ + public function getIdFromUser($user) + { + if (is_int($user)) { + return intval($user); + } else { + $objectUser = $this->getObjectUserByUsername($user); + if ($objectUser != null) { + return $objectUser->getId(); + } + } + return null; + } + + /** + * Returns user row from database. + * + * @param integer $id User ID + * + * @return array User array from database + */ + public function getUser($id) + { + return $this->_getuser($this->getFieldName('primary'), $id); + } + + /** + * Returns user object for given user id + * + * @param integer $id User ID + * + * @return SemanticScuttle_Model_User User object + */ + public function getObjectUser($id) + { + $user = $this->_getuser($this->getFieldName('primary'), $id); + return new SemanticScuttle_Model_User( + $id, $user[$this->getFieldName('username')] + ); + } + + function isLoggedOn() { + return ($this->getCurrentUserId() !== false); + } + + /** + * Tells you if the private key is enabled and valid + * + * @param string $privateKey Private Key + * + * @return boolean True if enabled and valid + */ + public function isPrivateKeyValid($privateKey) + { + // check length of private key + if (strlen($privateKey) == 32) { + return true; + } + return false; + } + + /** + * Returns the current user object + * + * @param boolean $refresh Reload the user from database + * based on current user id + * @param mixed $newval New user value (used internally + * as setter method) + * + * @return array User from database + */ + public function getCurrentUser($refresh = false, $newval = null) + { + if (!is_null($newval)) { + //internal use only: reset currentuser + $this->currentuser = $newval; + } else if ($refresh || !isset($this->currentuser)) { + if ($id = $this->getCurrentUserId()) { + $this->currentuser = $this->getUser($id); + } else { + $this->currentuser = null; + } + } + return $this->currentuser; + } + + /** + * Return current user as object + * + * @param boolean $refresh Reload the user from database + * based on current user id + * @param mixed $newval New user value (used internally + * as setter method) + * + * @return SemanticScuttle_Model_User User object + */ + function getCurrentObjectUser($refresh = false, $newval = null) + { + static $currentObjectUser; + if (!is_null($newval)) { + //internal use only: reset currentuser + $currentObjectUser = $newval; + } else if ($refresh || !isset($currentObjectUser)) { + if ($id = $this->getCurrentUserId()) { + $currentObjectUser = $this->getObjectUser($id); + } else { + $currentObjectUser = null; + } + } + return $currentObjectUser; + } + + function existsUserWithUsername($username) { + if($this->getUserByUsername($username) != '') { + return true; + } else { + return false; + } + } + + function existsUser($id) { + if($this->getUser($id) != '') { + return true; + } else { + return false; + } + } + + /** + * Checks if the given user is an administrator. + * Uses global admin_users property containing admin + * user names. + * + * Passing the user id makes this function load the user + * from database. For efficiency reasons, try to pass + * the user name or database row. + * + * @param integer|array|string $user User ID or user row from DB + * or user name + * + * @return boolean True if the user is admin + */ + function isAdmin($user) + { + if (is_numeric($user)) { + $user = $this->getUser($user); + $user = $user['username']; + } else if (is_array($user)) { + $user = $user['username']; + } + + if (isset($GLOBALS['admin_users']) + && in_array($user, $GLOBALS['admin_users']) + ) { + return true; + } else { + return false; + } + } + + /** + * Return current user id based on session or cookie + * + * @return mixed Integer user id or boolean false when user + * could not be found or is not logged on. + */ + public function getCurrentUserId() + { + if ($this->currentuserId !== null) { + return $this->currentuserId; + } + + if (isset($_SESSION[$this->getSessionKey()])) { + $this->currentuserId = (int)$_SESSION[$this->getSessionKey()]; + return $this->currentuserId; + + } + + if (isset($_COOKIE[$this->getCookieKey()])) { + $cook = explode(':', $_COOKIE[$this->getCookieKey()]); + //cookie looks like this: 'id:md5(username+password)' + $query = 'SELECT * FROM '. $this->getTableName() . + ' WHERE MD5(CONCAT('.$this->getFieldName('username') . + ', '.$this->getFieldName('password') . + ')) = \''.$this->db->sql_escape($cook[1]).'\' AND '. + $this->getFieldName('primary'). ' = '. $this->db->sql_escape($cook[0]); + + if (! ($dbresult = $this->db->sql_query($query)) ) { + message_die( + GENERAL_ERROR, 'Could not get user', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } + + if ($row = $this->db->sql_fetchrow($dbresult)) { + $this->setCurrentUserId( + (int)$row[$this->getFieldName('primary')], true + ); + $this->db->sql_freeresult($dbresult); + return $this->currentuserId; + } + } + + $ssls = SemanticScuttle_Service_Factory::get('User_SslClientCert'); + if ($ssls->hasValidCert()) { + $id = $ssls->getUserIdFromCert(); + if ($id !== false) { + $this->setCurrentUserId($id, true); + return $this->currentuserId; + } + } + return false; + } + + + + /** + * Set the current user ID (i.e. when logging on) + * + * @internal + * No ID verification is being done. + * + * @param integer $user User ID or null to unset the user + * @param boolean $storeInSession Store the user ID in the session + * + * @return void + */ + public function setCurrentUserId($user, $storeInSession = false) + { + if ($user === null) { + $this->currentuserId = null; + if ($storeInSession) { + unset($_SESSION[$this->getSessionKey()]); + } + } else { + $this->currentuserId = (int)$user; + if ($storeInSession) { + $_SESSION[$this->getSessionKey()] = $this->currentuserId; + } + } + //reload user object + $this->getCurrentUser(true); + $this->getCurrentObjectUser(true); + } + + + + /** + * Try to authenticate and login a user with + * username and password. + * + * @param string $username Name of user + * @param string $password Password + * @param boolean $remember If a long-time cookie shall be set + * + * @return boolean True if the user could be authenticated, + * false if not. + */ + public function login($username, $password, $remember = false) + { + $password = $this->sanitisePassword($password); + $query = 'SELECT '. $this->getFieldName('primary') .' FROM '. $this->getTableName() .' WHERE '. $this->getFieldName('username') .' = "'. $this->db->sql_escape($username) .'" AND '. $this->getFieldName('password') .' = "'. $this->db->sql_escape($password) .'"'; + + if (!($dbresult = $this->db->sql_query($query))) { + message_die( + GENERAL_ERROR, + 'Could not get user', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } + + $row = $this->db->sql_fetchrow($dbresult); + $this->db->sql_freeresult($dbresult); + + if ($row) { + $this->setCurrentUserId($row[$this->getFieldName('primary')], true); + if ($remember) { + $cookie = $this->currentuserId . ':' . md5($username.$password); + setcookie( + $this->cookiekey, $cookie, + time() + $this->cookietime, '/' + ); + } + return true; + } else { + return false; + } + } + + /** + * Try to authenticate via the privateKey + * + * @param string $privateKey Private Key + * + * @return boolean true if the user could be authenticated, + * false if not. + */ + public function loginPrivateKey($privateKey) + { + /* Check if private key valid and enabled */ + if (!$this->isPrivateKeyValid($privateKey)) { + return false; + } + + $query = 'SELECT '. $this->getFieldName('primary') .' FROM ' + . $this->getTableName() .' WHERE ' + . $this->getFieldName('privateKey') .' = "' + . $this->db->sql_escape($privateKey) .'"'; + + if (!($dbresult = $this->db->sql_query($query))) { + message_die( + GENERAL_ERROR, + 'Could not get user', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } + + $row = $this->db->sql_fetchrow($dbresult); + $this->db->sql_freeresult($dbresult); + + if ($row) { + $this->setCurrentUserId($row[$this->getFieldName('primary')], false); + return true; + } else { + return false; + } + } + + /** + * Logs the user off + * + * @return void + */ + public function logout() + { + @setcookie($this->getCookiekey(), '', time() - 1, '/'); + unset($_COOKIE[$this->getCookiekey()]); + session_unset(); + $this->currentuserId = null; + $this->currentuser = null; + } + + function getWatchlist($uId) { + // Gets the list of user IDs being watched by the given user. + $query = 'SELECT watched FROM '. $GLOBALS['tableprefix'] .'watched WHERE uId = '. intval($uId); + + if (! ($dbresult = $this->db->sql_query($query)) ) { + message_die(GENERAL_ERROR, 'Could not get watchlist', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + $arrWatch = array(); + if ($this->db->sql_numrows($dbresult) == 0) { + $this->db->sql_freeresult($dbresult); + return $arrWatch; + } + while ($row = $this->db->sql_fetchrow($dbresult)) { + $arrWatch[] = $row['watched']; + } + $this->db->sql_freeresult($dbresult); + return $arrWatch; + } + + + /** + * Gets the list of user names being watched by the given user. + * + * @param integer $uId User ID + * @param boolean $watchedby if false: get the list of users that $uId watches + * if true: get the list of users that watch $uId + * + * @return array Array of user names + */ + public function getWatchNames($uId, $watchedby = false) + { + if ($watchedby) { + $table1 = 'b'; + $table2 = 'a'; + } else { + $table1 = 'a'; + $table2 = 'b'; + } + $primary = $this->getFieldName('primary'); + $userfield = $this->getFieldName('username'); + $query = 'SELECT '. $table1 .'.'. $userfield + . ' FROM '. $GLOBALS['tableprefix'] . 'watched AS W,' + . ' ' . $this->getTableName() .' AS a,' + . ' ' . $this->getTableName() .' AS b' + . ' WHERE W.watched = a.' . $primary + . ' AND W.uId = b.' . $primary + . ' AND ' . $table2 . '.' . $primary . ' = '. intval($uId) + . ' ORDER BY '. $table1 . '.' . $userfield; + + if (!($dbresult = $this->db->sql_query($query))) { + message_die( + GENERAL_ERROR, 'Could not get watchlist', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } + + $arrWatch = array(); + if ($this->db->sql_numrows($dbresult) == 0) { + $this->db->sql_freeresult($dbresult); + return $arrWatch; + } + while ($row = $this->db->sql_fetchrow($dbresult)) { + $arrWatch[] = $row[$this->getFieldName('username')]; + } + $this->db->sql_freeresult($dbresult); + return $arrWatch; + } + + + function getWatchStatus($watcheduser, $currentuser) { + // Returns true if the current user is watching the given user, and false otherwise. + $query = 'SELECT watched FROM '. $GLOBALS['tableprefix'] .'watched AS W INNER JOIN '. $this->getTableName() .' AS U ON U.'. $this->getFieldName('primary') .' = W.watched WHERE U.'. $this->getFieldName('primary') .' = '. intval($watcheduser) .' AND W.uId = '. intval($currentuser); + + if (! ($dbresult = $this->db->sql_query($query)) ) { + message_die(GENERAL_ERROR, 'Could not get watchstatus', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + $retval = true; + if ($this->db->sql_numrows($dbresult) == 0) + $retval = false; + + $this->db->sql_freeresult($dbresult); + return $retval; + } + + function setWatchStatus($subjectUserID) { + if (!is_numeric($subjectUserID)) + return false; + + $currentUserID = $this->getCurrentUserId(); + $watched = $this->getWatchStatus($subjectUserID, $currentUserID); + + if ($watched) { + $sql = 'DELETE FROM '. $GLOBALS['tableprefix'] .'watched WHERE uId = '. intval($currentUserID) .' AND watched = '. intval($subjectUserID); + if (!($dbresult = $this->db->sql_query($sql))) { + $this->db->sql_transaction('rollback'); + message_die(GENERAL_ERROR, 'Could not add user to watch list', '', __LINE__, __FILE__, $sql, $this->db); + return false; + } + } else { + $values = array( + 'uId' => intval($currentUserID), + 'watched' => intval($subjectUserID) + ); + $sql = 'INSERT INTO '. $GLOBALS['tableprefix'] .'watched '. $this->db->sql_build_array('INSERT', $values); + if (!($dbresult = $this->db->sql_query($sql))) { + $this->db->sql_transaction('rollback'); + message_die(GENERAL_ERROR, 'Could not add user to watch list', '', __LINE__, __FILE__, $sql, $this->db); + return false; + } + } + + $this->db->sql_transaction('commit'); + return true; + } + + /** + * Create a new user in database. + * No checks are done in here - you ought to have checked + * everything before calling this method! + * + * @param string $username Username to use + * @param string $password Password to use + * @param string $email Email to use + * @param string $privateKey Key for RSS auth + * + * @return mixed Integer user ID if all is well, + * boolean false if an error occured + */ + public function addUser($username, $password, $email, $privateKey = null) + { + // Set up the SQL UPDATE statement. + $datetime = gmdate('Y-m-d H:i:s', time()); + $password = $this->sanitisePassword($password); + $values = array( + 'username' => $username, + 'password' => $password, + 'email' => $email, + 'uDatetime' => $datetime, + 'uModified' => $datetime, + 'privateKey' => $privateKey + ); + $sql = 'INSERT INTO '. $this->getTableName() + . ' '. $this->db->sql_build_array('INSERT', $values); + + // Execute the statement. + $this->db->sql_transaction('begin'); + if (!($dbresult = $this->db->sql_query($sql))) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not insert user', + '', __LINE__, __FILE__, $sql, $this->db + ); + return false; + } + $uId = $this->db->sql_nextid($dbresult); + $this->db->sql_transaction('commit'); + + return $uId; + } + + /** + * Updates the given user + * + * @param integer $uId ID of user to change + * @param string $password Password to use + * @param string $name Realname to use + * @param string $email Email to use + * @param string $homepage User's homepage + * @param string $uContent User note + * @param string $privateKey RSS Private Key + * @param boolean $enablePrivateKey RSS Private Key Flag + * + * @return boolean True when all is well, false if not + */ + public function updateUser( + $uId, $password, $name, $email, $homepage, $uContent, + $privateKey = null, $enablePrivateKey = false + ) { + if (!is_numeric($uId)) { + return false; + } + + // prepend '-' to privateKey if disabled + if ($privateKey != null && strlen($privateKey) == 32 + && $enablePrivateKey == false + ) { + $privateKey = '-' . $privateKey; + } + + // remove '-' from privateKey if enabling + if ($privateKey != null && strlen($privateKey) == 33 + && $enablePrivateKey == true + ) { + $privateKey = substr($privateKey, 1, 32); + } + + // if new user is enabling Private Key, create new key + if ($privateKey == null && $enablePrivateKey == true) { + $privateKey = $this->getNewPrivateKey(); + } + + // Set up the SQL UPDATE statement. + $moddatetime = gmdate('Y-m-d H:i:s', time()); + if ($password == '') { + $updates = array( + 'uModified' => $moddatetime, + 'name' => $name, + 'email' => $email, + 'homepage' => $homepage, + 'uContent' => $uContent, + 'privateKey' => $privateKey + ); + } else { + $updates = array( + 'uModified' => $moddatetime, + 'password' => $this->sanitisePassword($password), + 'name' => $name, + 'email' => $email, + 'homepage' => $homepage, + 'uContent' => $uContent, + 'privateKey' => $privateKey + ); + } + $sql = 'UPDATE '. $this->getTableName() + . ' SET '. $this->db->sql_build_array('UPDATE', $updates) + . ' WHERE '. $this->getFieldName('primary') . '=' . intval($uId); + + // Execute the statement. + $this->db->sql_transaction('begin'); + if (!($dbresult = $this->db->sql_query($sql))) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not update user', '', + __LINE__, __FILE__, $sql, $this->db + ); + return false; + } + $this->db->sql_transaction('commit'); + + // Everything worked out, so return true. + return true; + } + + + + function getAllUsers ( ) { + $query = 'SELECT * FROM '. $this->getTableName(); + + if (! ($dbresult = $this->db->sql_query($query)) ) { + message_die(GENERAL_ERROR, 'Could not get users', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + $rows = array(); + + while ( $row = $this->db->sql_fetchrow($dbresult) ) { + $rows[] = $row; + } + $this->db->sql_freeresult($dbresult); + return $rows; + } + + // Returns an array with admin uIds + function getAdminIds() { + $admins = array(); + foreach($GLOBALS['admin_users'] as $adminName) { + if($this->getIdFromUser($adminName) != NULL) + $admins[] = $this->getIdFromUser($adminName); + } + return $admins; + } + + function deleteUser($uId) { + $query = 'DELETE FROM '. $this->getTableName() .' WHERE uId = '. intval($uId); + + if (!($dbresult = $this->db->sql_query($query))) { + message_die(GENERAL_ERROR, 'Could not delete user', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + + return true; + } + + /** + * Delete all users and their watch states. + * Mainly used in unit tests. + * + * @return void + */ + public function deleteAll() + { + $query = 'TRUNCATE TABLE `'. $this->getTableName() .'`'; + $this->db->sql_query($query); + + $query = 'TRUNCATE TABLE `' . $GLOBALS['tableprefix'] . 'watched' . '`'; + $this->db->sql_query($query); + } + + /** + * Hashes the password for storage in/querying the database. + * + * @param string $password Password to hash + * + * @return string Hashed password + */ + public function sanitisePassword($password) + { + return sha1(trim($password)); + } + + /** + * Changes the password for the given user to a new, random one. + * + * @param integer $uId User ID + * + * @return string New password of false if something went wrong + */ + public function generatePassword($uId) + { + if (!is_numeric($uId)) { + return false; + } + + $password = $this->_randompassword(); + + $ok = $this->_updateuser( + $uId, $this->getFieldName('password'), + $this->sanitisePassword($password) + ); + + if ($ok) { + return $password; + } else { + return false; + } + } + + /** + * Generates a new private key and confirms it isn't being used. + * Private key is 32 characters long, consisting of lowercase and + * numeric characters. + * + * @return string the new key value + */ + public function getNewPrivateKey() + { + do { + $newKey = md5(uniqid('SemanticScuttle', true)); + } while ($this->privateKeyExists($newKey)); + + return $newKey; + } + + /** + * Checks if a private key already exists + * + * @param string $privateKey key that has been generated + * + * @return boolean true when the private key exists, + * False if not. + */ + public function privateKeyExists($privateKey) + { + if (!$privateKey) { + return false; + } + $crit = array('privateKey' => $privateKey); + + $sql = 'SELECT COUNT(*) as "0" FROM ' + . $GLOBALS['tableprefix'] . 'users' + . ' WHERE '. $this->db->sql_build_array('SELECT', $crit); + + if (!($dbresult = $this->db->sql_query($sql))) { + message_die( + GENERAL_ERROR, 'Could not get vars', '', + __LINE__, __FILE__, $sql, $this->db + ); + } + if ($this->db->sql_fetchfield(0, 0) > 0) { + $exists = true; + } else { + $exists = false; + } + $this->db->sql_freeresult($dbresult); + return $exists; + } + + function isReserved($username) { + if (in_array($username, $GLOBALS['reservedusers'])) { + return true; + } else { + return false; + } + } + + function isValidUsername($username) { + if (strlen($username) < 4) { + return false; + }elseif (strlen($username) > 24) { + // too long usernames are cut by database and may cause bugs when compared + return false; + } elseif (preg_match('/(\W)/', $username) > 0) { + // forbidden non-alphanumeric characters + return false; + } + return true; + } + + + + /** + * Checks if the given email address is valid + * + * @param string $email Email address + * + * @return boolean True if it is valid, false if not + */ + public function isValidEmail($email) + { + return filter_var($email, FILTER_VALIDATE_EMAIL) !== false; + } + + /** + * Sets a session variable. + * Updates it when it is already set. + * This is used to detect if cookies work. + * + * @return void + * + * @see isSessionStable() + */ + public function updateSessionStability() + { + //find out if we have cookies enabled + if (!isset($_SESSION['sessionStable'])) { + $_SESSION['sessionStable'] = 0; + } else { + $_SESSION['sessionStable'] = 1; + } + } + + /** + * Tells you if the session is fresh or old. + * If the session is fresh, it's the first page + * call with that session id. If the session is old, + * we know that cookies (or session persistance) works + * + * @return boolean True if the + * + * @see updateSessionStability() + */ + public function isSessionStable() + { + return $_SESSION['sessionStable'] == 1; + } + + /** + * Get database column name. + * + * @param string $field Field name like 'primary', 'username' + * and 'password' + * + * @return string Real field name + */ + public function getFieldName($field) + { + return $this->fields[$field]; + } + + /** + * Set field name + * + * @param string $field Field name like 'primary', 'username' + * and 'password' + * @param string $value Real database column name + * + * @return void + */ + public function setFieldName($field, $value) + { + $this->fields[$field] = $value; + } + + function getSessionKey() { return $this->sessionkey; } + function setSessionKey($value) { $this->sessionkey = $value; } + + function getCookieKey() { return $this->cookiekey; } + function setCookieKey($value) { $this->cookiekey = $value; } +} + +?> diff --git a/src/SemanticScuttle/Service/User/SslClientCert.php b/src/SemanticScuttle/Service/User/SslClientCert.php new file mode 100644 index 0000000..a6d43be --- /dev/null +++ b/src/SemanticScuttle/Service/User/SslClientCert.php @@ -0,0 +1,283 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Christian Weiske <cweiske@cweiske.de> + * @license AGPL http://www.gnu.org/licenses/agpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ + +/** + * SemanticScuttle SSL client certificate management service + * + * @category Bookmarking + * @package SemanticScuttle + * @author Christian Weiske <cweiske@cweiske.de> + * @license AGPL http://www.gnu.org/licenses/agpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_User_SslClientCert extends SemanticScuttle_DbService +{ + /** + * Creates a new instance, sets database variable and table name. + * + * @param sql_db $db Database object + */ + protected function __construct($db) + { + $this->db = $db; + $this->tablename = $GLOBALS['tableprefix'] .'users_sslclientcerts'; + } + + /** + * Returns the single service instance + * + * @param sql_db $db Database object + * + * @return SemanticScuttle_Service_User + */ + public static function getInstance($db) + { + static $instance; + if (!isset($instance)) { + $instance = new self($db); + } + return $instance; + } + + /** + * Determines if the browser provided a valid SSL client certificate + * + * @return boolean True if the client cert is there and is valid + */ + public function hasValidCert() + { + if (!isset($_SERVER['SSL_CLIENT_M_SERIAL']) + || !isset($_SERVER['SSL_CLIENT_V_END']) + || !isset($_SERVER['SSL_CLIENT_VERIFY']) + || $_SERVER['SSL_CLIENT_VERIFY'] !== 'SUCCESS' + || !isset($_SERVER['SSL_CLIENT_I_DN']) + ) { + return false; + } + + if ($_SERVER['SSL_CLIENT_V_REMAIN'] <= 0) { + return false; + } + + return true; + } + + + + /** + * Registers the currently available SSL client certificate + * with the given user. As a result, the user will be able to login + * using the certifiate + * + * @param integer $uId User ID to attach the client cert to. + * + * @return boolean True if registration was well, false if not. + */ + public function registerCurrentCertificate($uId) + { + $serial = $_SERVER['SSL_CLIENT_M_SERIAL']; + $clientIssuerDn = $_SERVER['SSL_CLIENT_I_DN']; + + $query = 'INSERT INTO ' . $this->getTableName() + . ' '. $this->db->sql_build_array( + 'INSERT', array( + 'uId' => $uId, + 'sslSerial' => $serial, + 'sslClientIssuerDn' => $clientIssuerDn, + 'sslName' => $_SERVER['SSL_CLIENT_S_DN_CN'], + 'sslEmail' => $_SERVER['SSL_CLIENT_S_DN_Email'] + ) + ); + if (!($dbresult = $this->db->sql_query($query))) { + message_die( + GENERAL_ERROR, 'Could not load user for client certificate', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } + + return true; + } + + + + /** + * Takes values from the currently available SSL client certificate + * and adds the available profile data to the user. + * + * @param integer $uId User ID to attach the client cert to. + * + * @return array Array of profile data that were registered. + * Database column name as key, new value as value + */ + public function updateProfileFromCurentCert($uId) + { + $arData = array(); + + if (isset($_SERVER['SSL_CLIENT_S_DN_CN']) + && trim($_SERVER['SSL_CLIENT_S_DN_CN']) != '' + ) { + $arData['name'] = trim($_SERVER['SSL_CLIENT_S_DN_CN']); + } + + if (count($arData)) { + $us = SemanticScuttle_Service_Factory::get('User'); + foreach ($arData as $column => $value) { + $us->_updateuser($uId, $column, $value); + } + } + return $arData; + } + + + + /** + * Tries to detect the user ID from the SSL client certificate passed + * to the web server. + * + * @return mixed Integer user ID if the certificate is valid and + * assigned to a user, boolean false otherwise + */ + public function getUserIdFromCert() + { + if (!$this->hasValidCert()) { + return false; + } + + $serial = $_SERVER['SSL_CLIENT_M_SERIAL']; + $clientIssuerDn = $_SERVER['SSL_CLIENT_I_DN']; + + $query = 'SELECT uId' + . ' FROM ' . $this->getTableName() + . ' WHERE sslSerial = \'' . $this->db->sql_escape($serial) . '\'' + . ' AND sslClientIssuerDn = \'' + . $this->db->sql_escape($clientIssuerDn) + . '\''; + if (!($dbresult = $this->db->sql_query($query))) { + message_die( + GENERAL_ERROR, 'Could not load user for client certificate', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } + + $row = $this->db->sql_fetchrow($dbresult); + $this->db->sql_freeresult($dbresult); + + if (!$row) { + return false; + } + return (int)$row['uId']; + } + + + + /** + * Fetches the certificate with the given ID from database. + * + * @param integer $id Certificate ID in database + * + * @return SemanticScuttle_Model_User_SslClientCert Certificate object + * or null if not found + */ + public function getCert($id) + { + $query = 'SELECT * FROM ' . $this->getTableName() + . ' WHERE id = ' . (int)$id; + if (!($dbresult = $this->db->sql_query($query))) { + message_die( + GENERAL_ERROR, 'Could not load SSL client certificate', + '', __LINE__, __FILE__, $query, $this->db + ); + return null; + } + + if ($row = $this->db->sql_fetchrow($dbresult)) { + $cert = SemanticScuttle_Model_User_SslClientCert::fromDb($row); + } else { + $cert = null; + } + $this->db->sql_freeresult($dbresult); + return $cert; + } + + + + /** + * Fetches all registered certificates for the user from the database + * and returns it. + * + * @return array Array with all certificates for the user. Empty if + * there are none, SemanticScuttle_Model_User_SslClientCert + * objects otherwise. + */ + public function getUserCerts($uId) + { + $query = 'SELECT * FROM ' . $this->getTableName() + . ' WHERE uId = ' . (int)$uId + . ' ORDER BY sslSerial DESC'; + if (!($dbresult = $this->db->sql_query($query))) { + message_die( + GENERAL_ERROR, 'Could not load SSL client certificates', + '', __LINE__, __FILE__, $query, $this->db + ); + return array(); + } + + $certs = array(); + while ($row = $this->db->sql_fetchrow($dbresult)) { + $certs[] = SemanticScuttle_Model_User_SslClientCert::fromDb($row); + } + $this->db->sql_freeresult($dbresult); + return $certs; + } + + + + /** + * Deletes a SSL client certificate. + * No security checks are made here. + * + * @param mixed $cert Certificate object or certificate database id. + * Objects are of type + * SemanticScuttle_Model_User_SslClientCert + * + * @return boolean True if all went well, false if it could not be deleted + */ + public function delete($cert) + { + if ($cert instanceof SemanticScuttle_Model_User_SslClientCert) { + $id = (int)$cert->id; + } else { + $id = (int)$cert; + } + + if ($id === 0) { + return false; + } + + $query = 'DELETE FROM ' . $this->getTableName() + .' WHERE id = ' . $id; + + if (!($dbresult = $this->db->sql_query($query))) { + message_die( + GENERAL_ERROR, 'Could not delete user certificate', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } + + return true; + } +} +?>
\ No newline at end of file diff --git a/src/SemanticScuttle/Service/Vote.php b/src/SemanticScuttle/Service/Vote.php new file mode 100644 index 0000000..85380e7 --- /dev/null +++ b/src/SemanticScuttle/Service/Vote.php @@ -0,0 +1,337 @@ +<?php +/** + * SemanticScuttle - your social bookmark manager. + * + * PHP version 5. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Christian Weiske <cweiske@cweiske.de> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ + +/** + * SemanticScuttle voting system. + * + * Each registered user in SemanticScuttle may vote + * for or against a bookmark, counting +1 or -1. + * The sum of all votes determines the "voting" of a bookmark. + * Every user is equal in voting. Each vote is tied to a user. + * + * @internal + * Votes are saved in a separate "votes" table. + * Additionally to that, the voting and number of votes + * of a bookmark is also stored in the bookmarks table. + * This is done to make sure lookups are really fast, since + * every bookmark in a list shows its voting. + * + * @category Bookmarking + * @package SemanticScuttle + * @author Christian Weiske <cweiske@cweiske.de> + * @license GPL http://www.gnu.org/licenses/gpl.html + * @link http://sourceforge.net/projects/semanticscuttle + */ +class SemanticScuttle_Service_Vote extends SemanticScuttle_DbService +{ + /** + * Returns the single service instance + * + * @param sql_db $db Database object + * + * @return SemanticScuttle_Service_Vote + */ + public static function getInstance($db) + { + static $instance; + if (!isset($instance)) { + $instance = new self($db); + } + return $instance; + } + + + + /** + * Create a new instance. + * + * @param sql_db $db Database object + */ + protected function __construct($db) + { + $this->db = $db; + $this->tablename = $GLOBALS['tableprefix'] . 'votes'; + } + + + + /** + * Returns the sum of votes for the given bookmark. + * + * @internal + * Uses the "votes" table to retrieve the votes, which + * has high costs. It is more efficient to get the sum of + * all votes for a bookmark from the bookmarks table, + * field bVoting. + * + * @param integer $bookmark Bookmark ID + * + * @return integer Vote (can be positive, 0 or negative) + */ + public function getVoting($bookmark) + { + $query = 'SELECT SUM(vote) as sum FROM ' . $this->getTableName() + . ' WHERE bid = "' . $this->db->sql_escape($bookmark) . '"'; + + if (!($dbres = $this->db->sql_query_limit($query, 1, 0))) { + message_die( + GENERAL_ERROR, 'Could not get voting', + '', __LINE__, __FILE__, $query, $this->db + ); + //FIXME: throw exception + } + + $row = $this->db->sql_fetchrow($dbres); + $this->db->sql_freeresult($dbres); + + return (int)$row['sum']; + }//public function getVoting(..) + + + + /** + * Returns the number of users that voted for or + * against the given bookmark. + * + * @internal + * This method uses the votes table to calculate the + * number of votes for a bookmark. In normal life, it + * is more efficient to use the "bVotes" field in the + * bookmarks table. + * + * @param integer $bookmark Bookmark ID + * + * @return integer Number of votes + */ + public function getVotes($bookmark) + { + $query = 'SELECT COUNT(vote) as count FROM ' + . $this->getTableName() + . ' WHERE bid = "' . $this->db->sql_escape($bookmark) . '"'; + + if (!($dbres = $this->db->sql_query_limit($query, 1, 0))) { + message_die( + GENERAL_ERROR, 'Could not get vote count', + '', __LINE__, __FILE__, $query, $this->db + ); + //FIXME: throw exception + } + + $row = $this->db->sql_fetchrow($dbres); + $this->db->sql_freeresult($dbres); + + return (int)$row['count']; + } + + + + /** + * Returns if the user has already voted for + * the given bookmark. + * + * @param integer $bookmark Bookmark ID + * @param integer $user User ID + * + * @return boolean True if the user has already voted + */ + public function hasVoted($bookmark, $user) + { + $query = 'SELECT COUNT(vote) as count FROM ' + . $this->getTableName() + . ' WHERE' + . ' bid = "' . $this->db->sql_escape($bookmark) . '"' + . ' AND uid = "' . $this->db->sql_escape($user) . '"'; + + if (!($dbres = $this->db->sql_query_limit($query, 1, 0))) { + message_die( + GENERAL_ERROR, 'Could not get vote count', + '', __LINE__, __FILE__, $query, $this->db + ); + //FIXME: throw exception + } + + $row = $this->db->sql_fetchrow($dbres); + $this->db->sql_freeresult($dbres); + + return (int)$row['count'] == 1; + } + + + + /** + * Returns the actual vote the given user + * gave the bookmark. + * + * @param integer $bookmark Bookmark ID + * @param integer $user User ID + * + * @return integer Either 1 or -1, null when not voted. + */ + public function getVote($bookmark, $user) + { + $query = 'SELECT vote FROM ' . $this->getTableName() + . ' WHERE' + . ' bid = "' . $this->db->sql_escape($bookmark) . '"' + . ' AND uid = "' . $this->db->sql_escape($user) . '"'; + + if (!($dbres = $this->db->sql_query_limit($query, 1, 0))) { + message_die( + GENERAL_ERROR, 'Could not get vote count', + '', __LINE__, __FILE__, $query, $this->db + ); + //FIXME: throw exception + } + + $row = $this->db->sql_fetchrow($dbres); + $this->db->sql_freeresult($dbres); + + if (!$row) { + return null; + } + + return $row['vote']; + } + + + + /** + * Let a user vote for the bookmark. + * + * @internal + * We check if voting is enabled or not, + * and if the user has already voted. + * It is up to the calling code to make sure + * the user is authorized to vote. + * + * @param integer $bookmark Bookmark ID + * @param integer $user User ID + * @param integer $vote 1 or -1 + * + * @return boolean True if the vote was saved, + * false if there was a problem + * (i.e. already voted) + */ + public function vote($bookmark, $user, $vote = 1) + { + if ($GLOBALS['enableVoting'] == false) { + return false; + } + + if ($vote != -1 && $vote != 1) { + return false; + } + + if ($this->hasVoted($bookmark, $user)) { + $this->removeVote($bookmark, $user); + } + + $res = $this->db->sql_query( + 'INSERT INTO ' . $this->getTableName() + . ' SET' + . ' bid = ' . (int)$bookmark + . ',uid = ' . (int)$user + . ',vote = ' . (int)$vote + ); + //FIXME: check for sql error + $this->db->sql_freeresult($res); + + //update bookmark table + $bm = SemanticScuttle_Service_Factory::get('Bookmark'); + $res = $this->db->sql_query( + $sql='UPDATE ' . $bm->getTableName() + . ' SET bVoting = bVoting + ' . (int)$vote + . ' , bVotes = bVotes + 1' + . ' WHERE bId = ' . (int)$bookmark + ); + $this->db->sql_freeresult($res); + + return true; + } + + + + /** + * Removes a vote from the database + * + * @param integer $bookmark Bookmark ID + * @param integer $user User ID + * + * @return boolean True if all went well, false if not + */ + protected function removeVote($bookmark, $user) + { + $vote = $this->getVote($bookmark, $user); + if ($vote === null) { + return false; + } + + //remove from votes table + $query = 'DELETE FROM ' . $this->getTableName() + . ' WHERE bId = ' . (int)$bookmark + . ' AND uId = ' . (int)$user; + $this->db->sql_query($query); + + //change voting sum in bookmarks table + $bm = SemanticScuttle_Service_Factory::get('Bookmark'); + $res = $this->db->sql_query( + $sql='UPDATE ' . $bm->getTableName() + . ' SET bVoting = bVoting - ' . (int)$vote + . ' , bVotes = bVotes - 1' + . ' WHERE bId = ' . (int)$bookmark + ); + $this->db->sql_freeresult($res); + + return true; + } + + + + /** + * Re-calculates all votings for all bookmarks + * and updates the voting values in the bookmarks + * table. + * This is mainly meant to be an administrative method + * to fix a broken database. + * + * @return void + */ + public function rewriteVotings() + { + $bm = SemanticScuttle_Service_Factory::get('Bookmark'); + $query = 'UPDATE ' . $bm->getTableName() . ' as B SET bVoting = ' + . '(SELECT SUM(vote) FROM ' . $this->getTableName() . ' as V' + . ' WHERE V.bId = B.bId GROUP BY bid)' + . ', bVotes = ' + . '(SELECT COUNT(vote) FROM ' . $this->getTableName() . ' as V' + . ' WHERE V.bId = B.bId GROUP BY bid)'; + $this->db->sql_query($query); + } + + + + /** + * Delete all votes from the database table. + * Used in unit tests. + * + * @return void + */ + public function deleteAll() + { + $query = 'TRUNCATE TABLE `'. $this->getTableName() .'`'; + $this->db->sql_query($query); + } + + +} + +?>
\ No newline at end of file |