Location: PHPKode > projects > phlyMail Lite > phlymail/handlers/bookmarks/driver.mysql.php
<?php
/**
 * driver.mysql.php - MySQL class for bookmarks handler
 * @package phlyMail Nahariya 4.0+ Default Branch
 * @subpackage Handler Bookmarks
 * @copyright 2009-2010 phlyLabs, Berlin (http://phlylabs.de)
 * @version 0.2.4 2010-04-24
 */
// Only valid within phlyMail
if (!defined('_IN_PHM_')) die();

class bookmarks_driver {
    // Valid Search Criteria
    public $criteria_list;

    private $error = array();
    private $append_errors = false;
    private $retain_errors = false;

    // This is the constructor
    public function __construct($uid = 0)
    {
        $this->uid = intval($uid);
        $this->DB = &$GLOBALS['DB'];
        $this->DB->Tbl['bookmarks_item'] = $this->DB->DB['db_pref'].'bookmarks_item';
        $this->DB->Tbl['bookmarks_folder'] = $this->DB->DB['db_pref'].'bookmarks_folder';
        return true;
    }

    public function affected() { return $this->DB->affected(); }

    private function set_error($error)
    {
        if ($this->append_errors) {
            $this->error[] = $error;
        } else {
            $this->error[0] = $error;
        }
    }

    public function get_errors($nl = LF)
    {
        $error = implode($nl, $this->error);
        if (!$this->retain_errors) $this->error = array();
        return $error;
    }

    /**
     * Get count of item stored in the user's address book
     * @param bitfield 1 -> Include "global" addresses, 2 -> public bookmarks
     * [@param int folder (address book) ID to return the count for]
     * @return string count on success or FALSE on failure
     */
    public function get_itemcount($inc_global = 0, $fid = 0, $pattern = '', $criteria = '')
    {
        $folderFilter = 'b.`fid`='.intval($fid);
        if ($fid === 'favourites') {
            $folderFilter = 'b.`favourite`="1"';
        }
        $sql = 'SELECT count(*) FROM '.$this->DB->Tbl['bookmarks_item'].' b'
                 .' WHERE '.$folderFilter.' AND ';
        switch ($inc_global) {
            case 1: $sql .= 'b.owner IN('.$this->uid.',0)'; break;
            case 2: $sql .= 'b.owner=0'; break;
            default: $sql .= 'b.owner='.$this->uid;
        }
        // Do we have a search criteria and a pattern set?
        if ($criteria && $pattern) {
            $pattern = $this->DB->escape($pattern);
            $pattern = (strstr($pattern, '*')) ? str_replace('*', '%', $pattern) : '%'.$pattern.'%';
            if (isset($this->criteria_list[$criteria])) {
                // Flatten the field list
                foreach ($this->criteria_list[$criteria] as $k) { $v[] = 'a.'.$k.' LIKE "'.$pattern.'"'; }
                $sql .= ' AND ('.implode(' OR ', $v).')';
            }
        }
        list ($count) = $this->DB->fetchrow($this->DB->query($sql));
        return $count;
    }

    /**
     * Get all bookmarks stored for a user
     *[@param bitfield 1 -> Include "global" addresses, 2 -> public bookmarks]
     *[@param  string  Search pattern]
     *[@param  string Search criteria]
     *[@param  integer  Number of entries to return]
     *[@param  integer  Start entry]
     *[@param  string  order by field]
     *[@param  string  order direction ('asc|desc')]
     * @return  mixed  array data on success; FALSE otherwise
     */
    public function get_index($inc_global = 0, $fid = 0, $pattern = '', $criteria = '', $num = 0, $start = 0, $order_by = false, $order_dir = 'ASC')
    {
        $return = array();
        $folderFilter = 'b.`fid`='.intval($fid);
        if ($fid === 'favourites') {
            $folderFilter = 'b.`favourite`="1"';
        }
        $sql = 'SELECT b.`id`, b.`name`, b.`url`, b.`favourite`, b.`fid` '
                 .', (SELECT f.`name` FROM '.$this->DB->Tbl['bookmarks_folder'].' f WHERE f.`id`=b.`fid` LIMIT 1) `folder`'
                 .', IF (b.owner!='.$this->uid.', 1, 0) `global`'
                 .' FROM '.$this->DB->Tbl['bookmarks_item'].' b'
                 .' WHERE '.$folderFilter.' AND ';
        switch ($inc_global) {
            case 1: $sql .= 'b.owner IN('.$this->uid.',0)'; break;
            case 2: $sql .= 'b.owner=0'; break;
            default: $sql .= 'b.owner='.$this->uid;
        }
        // Do we have a search criteria and a pattern set?
        if ($criteria && $pattern) {
            $pattern = $this->DB->escape($pattern);
            $pattern = (strstr($pattern, '*')) ? str_replace('*', '%', $pattern) : '%'.$pattern.'%';
            if (isset($this->criteria_list[$criteria])) {
                // Flatten the field list
                foreach ($this->criteria_list[$criteria] as $k) { $v[] = 'a.'.$k.' LIKE "'.$pattern.'"'; }
                $sql .= ' AND ('.implode(' OR ', $v).')';
            }
        }
        // Order by / direction given?
        $order_dir = ('ASC' == $order_dir) ? 'ASC' : 'DESC';
        $sql .= ' ORDER BY ' . ($order_by ? '`'.$this->DB->escape($order_by).'` '.$order_dir : 'b.`name`');
        if ($num != 0) $sql .= ' LIMIT '.($start).','.($num);

        if ($qid = $this->DB->query($sql)) {
            while ($line = $this->DB->fetchassoc($qid)) $return[] = $line;
        }
        return $return;
    }

    /**
     * Return a specific bookmark
     * @param int  item ID
     *[@param bool  Include global bookmarks in the query; default is false]
     * @return array data on success or FALSE on failure
     */
    public function get_item($id = 0, $inc_global = 0)
    {
        $id = ($id) ? intval($id) : 0;
        switch ($inc_global) {
            case 1: $q_r = 'b.owner IN('.$this->uid.',0)'; break;
            case 2: $q_r = 'b.owner=0'; break;
            default: $q_r = 'b.owner='.$this->uid;
        }
        $query = 'SELECT b.`id`, b.`name`, b.`url`, b.`description`, b.`favourite`, b.`fid` '
             .', (SELECT f.`name` FROM '.$this->DB->Tbl['bookmarks_folder'].' f WHERE f.`id`=b.`fid` LIMIT 1) `folder`'
             .', IF (b.`owner`!='.$this->uid.', 1, 0) `global`'
             .' FROM '.$this->DB->Tbl['bookmarks_item'].' b WHERE b.`id`='.$id.' AND '.$q_r;
        $return = $this->DB->fetchassoc($this->DB->query($query));
        return $return;
    }

    /**
     * Returns the list of favourite bookmarks for the current user
     *
     *[@param bool  Include global bookmarks in the query; default is false]
     * @return array data on success or FALSE on failure
     */
    public function get_favourites($inc_global = 0, $countOnly = false)
    {
        switch ($inc_global) {
            case 1: $q_r = 'b.owner IN('.$this->uid.',0)'; break;
            case 2: $q_r = 'b.owner=0'; break;
            default: $q_r = 'b.owner='.$this->uid;
        }
        if ($countOnly) {
            $query = 'SELECT COUNT(*) `anzahl` FROM '.$this->DB->Tbl['bookmarks_item'].' b WHERE b.`favourite`="1" AND '.$q_r;
            $return = $this->DB->fetchassoc($this->DB->query($query));
            $return = $return['anzahl'];
        } else {
            $query = 'SELECT b.`id`, b.`name` FROM '.$this->DB->Tbl['bookmarks_item'].' b'
                    .' WHERE b.`favourite`="1" AND '.$q_r.' ORDER BY b.`name` ASC';
            $return = $this->DB->fetchassoc($this->DB->query($query));
        }
        return $return;
    }

    /**
     * Delete a given item from address book
     * @param int $aid  item id to delete
     * @return TRUE on success or FALSE on failure
     */
    public function delete_item($id = 0)
    {
        $id = intval($id);
        $query = 'SELECT 1 FROM '.$this->DB->Tbl['bookmarks_item'].' WHERE `id`='.$id.' AND `owner`='.$this->uid.' LIMIT 1';
        list ($result) = $this->DB->fetchrow($this->DB->query($query));
        if (!$result) return -2;
        return $this->DB->query('DELETE FROM '.$this->DB->Tbl['bookmarks_item'].' WHERE `id`='.$id);
    }

    /**
     * Add an item to the database
     * Omit data you don't want to set
     * Set the owner to 0 for a global item
     * @param array $data
     * @return TRUE on success or FALSE on failure
     */
    public function add_item($data)
    {
        $add = array();
        foreach (array('name' => '""', 'url' => '""', 'description' => '""', 'favourite' => '"0"', 'fid' => '"0"'
                ,'added' => 'NULL', 'modified' => 'NULL', 'visited' => 'NULL') as $k => $v) {
            $add[] = (isset($data[$k])) ? $k.'="'.$this->DB->escape($data[$k]).'"' : $k.'='.$v;
        }
        if (!empty($add)) {
            $add[] = '`owner`='.(isset($data['owner']) && 0 == $data['owner'] ? '0' : $this->uid);
            if ($this->DB->query('INSERT '.$this->DB->Tbl['bookmarks_item'].' SET ' . implode(',', $add)).',`uuid`="'.$this->DB->escape(basics::uuid()).'"') {
                return true;
            }
        }
        return false;
    }

    /**
     * Update an item in the address book
     * Omit data you don't want to update
     * Input  : adb_update_item(array field data)
     * @return TRUE on success or FALSE on failure
     */
    public function update_item($data)
    {
        $add = array();
        foreach (array('name', 'url', 'description', 'favourite', 'fid', 'added', 'modified', 'visited') as $k) {
            if (isset($data[$k])) $add[] = '`'.$k.'`="' . $this->DB->escape($data[$k]) . '"';
        }
        if (!empty($add)) {
            $query = 'UPDATE '.$this->DB->Tbl['bookmarks_item'].' SET '.implode(',', $add).',`uuid`="'.$this->DB->escape(basics::uuid()).'" WHERE `id`='.intval($data['id']).' AND `owner`='.$this->uid;
            return $this->DB->query($query);
        }
        return false;
    }

    public function copy_item($id, $folder)
    {
        $orig = $this->get_item($id, 3);
        $orig['fid'] = $folder;
        $orig['added'] = time();
        return $this->add_item($orig);
    }

    /**
     * Moves an item form one folder to another
     *
     * @param int $item  ID of the item to move
     * @param int $folder  ID of the folder to move the item to
     */
    public function move_item($item, $folder)
    {
        return $this->update_item(array('id' => $item, 'fid' => $folder));
    }

    /**
     * Return list of folders associated with a certain user
     * @param integer user id
     * @param boolean with global folders?
     * [@param string pattern
     * [@param integer num
     * [@param integer start]]])
     * @return $return array data on success, FALSE otherwise
     */
    public function get_folderlist($inc_global = 0, $raw = false)
    {
        $this->fidx = array();
        $q_l = 'SELECT `id`,`name`,`description`,`layered_id`,`childof`,`owner` FROM '.$this->DB->Tbl['bookmarks_folder']
                .($inc_global ? ' WHERE `owner` IN('.$this->uid.',0)' : ' WHERE `owner`='.$this->uid)
                .' ORDER BY IF(`owner`!= 0, 0, 1) ASC, `childof` ASC, `layered_id` ASC';
        $qid = $this->DB->query($q_l);
        while ($line = $this->DB->fetchassoc($qid)) {
            $this->fidx[$line['childof']][$line['id']] = $line;
        }
        return $raw === true ? $this->fidx : $this->read_folders_flat(0, 0);
    }

    /**
    * Read all available folders below doc root and return as array,
    * opposed to read_folders() the returned array does not reflect the real
    * structure of the folders, just the order and a 'level' attribute will tell
    * you about it.
    * @param  int $parent_id  ID of the folder to start with, Default: 0
    *[@param  int $level  Starting level, in doubt leave blank]
    * @return  array of folders and their meta data
    */
    protected function read_folders_flat($parent_id = 0, $level = 0)
    {
        $return = array();
        // Not valid parent ID
        if (!isset($this->fidx[$parent_id])) return array();

        foreach ($this->fidx[$parent_id] as $k => $v) {
            $return[$k] = array
                    ('path' => $k
                    ,'name' => $v['name']
                    ,'level' => $level
                    ,'childof' => $v['childof']
                    ,'owner' => $v['owner']
                    ,'description' => $v['description']
                    );
            if (isset($this->fidx[$k])) {
                $return[$k]['subdirs'] = true;
                $return = $return + $this->read_folders_flat($k, $level+1);
            } else {
                $return[$k]['subdirs'] = false;
            }
        }
        return $return;
    }

    /**
     * Special method just needed for the folder browser on copy / move
     */
    public function return_fidx()
    {
        return $this->fidx;
    }

    public function get_sharedfolderlist()
    {
        return false;
    }

    /**
     * Return folder by given owner and folder id
     * Input  : adb_get_folder(integer owner, integer folder id)
     * @return string folder name on success, FALSE otherwise
     */
    public function get_folder($fid = 0)
    {
        if (!$fid) return false;
        $fid = (int) $fid;
        $query = 'SELECT * FROM '.$this->DB->Tbl['bookmarks_folder'].' WHERE `id`='.$fid;
        return $this->DB->fetchassoc($this->DB->query($query));
    }

    /**
     * Insert a folder
     * Input  : adb_add_folder(integer owner, integer folder id, string folder name)
     * @return TRUE on success, FALSE otherwise
     */
    public function add_folder($name = '', $desc = '', $childof = 0)
    {
        $name = $this->DB->escape($name);
        $childof = intval($childof);
        $qid = $this->DB->query('SELECT max(layered_id) FROM '.$this->DB->Tbl['bookmarks_folder'].' WHERE `childof`='.$childof.' AND `owner`='.$this->uid);
        list ($max_layered) = $this->DB->fetchrow($qid);
        $query = 'INSERT '.$this->DB->Tbl['bookmarks_folder'].' SET `owner`='.$this->uid
                .',`name`="'.$this->DB->escape($name).'",`description`="'.$this->DB->escape($desc).'",`childof`='.$childof
                .',`layered_id`='.(intval($max_layered)+1).',`uuid`="'.$this->DB->escape(basics::uuid()).'"';
        if ($this->DB->query($query)) {
            return $this->DB->insertid();
        }
        return false;
    }

    /**
     * Update a given folder
     * Input  : adb_update_folder(integer owner, integer folder id, string folder name)
     * @return TRUE on success, FALSE otherwise
     */
    public function update_folder($fid = 0, $name = null, $desc = null, $childof = false, $layered = false)
    {
        if (!$fid || !$name) return false;
        $query = 'UPDATE '.$this->DB->Tbl['bookmarks_folder'].' SET `uuid`="'.$this->DB->escape(basics::uuid()).'"';
        if (!is_null($name)) $query .= ',`name`="'.$this->DB->escape($name).'"';
        if (!is_null($desc)) $query .= ',`description`="'.$this->DB->escape($desc).'"';
        if ($childof) $query .= ',childof='.intval($childof);
        if ($layered) $query .= ',layered_id='.intval($layered);
        $query .= ' WHERE `id`='.intval($fid).' AND `owner`='.$this->uid;
        if (!$this->DB->query($query)) {
            $this->set_error($this->DB->error());
            return false;
        }
        return true;
    }

    /**
     * Check, whether a folder name for a ceratin user already exists
     * Input  : adb_checkfor_foldername(integer owner, string foldername)
     * @return folder id if yes, FALSE otherwise
     */
    public function checkfor_foldername($name = '', $childof = false)
    {
        $query = 'SELECT `id` FROM '.$this->DB->Tbl['bookmarks_folder'].' WHERE `owner`='.$this->uid.' AND name="'.$this->DB->escape($name).'"';
        if ($childof !== false) $query .= ' AND childof='.intval($childof);
        list ($result) = $this->DB->fetchrow($this->DB->query($query));
        return ($result) ? $result : false;
    }

    /**
     * Delete a given folder from the DB.
     * This method drops all the items AND SUBFOLDERS contained!
     * @param int $fid  ID of the folder to delete
     * @return TRUE on success or FALSE on failure
     */
    public function dele_folder($fid = 0)
    {
        $fid = intval($fid);
        $qid = $this->DB->query('SELECT `id` FROM '.$this->DB->Tbl['bookmarks_folder'].' WHERE `owner`='.$this->uid.' AND `childof`='.$fid);
        while ($line = $this->DB->fetchassoc($qid)) {
            // Go deeper in the structure and drop all children first
            $this->dele_folder($line['id']);
        }
        // Only drop items, when deleting parent folder succeeded
        if ($this->DB->query('DELETE FROM '.$this->DB->Tbl['bookmarks_folder'].' WHERE `id`='.$fid)) {
            $this->DB->query('DELETE FROM '.$this->DB->Tbl['bookmarks_item'].' WHERE `fid`='.$fid.' AND `owner`='.$this->uid);
        }
        return true;
    }

    public function move_folder($fid, $childof)
    {
        $query = 'UPDATE '.$this->DB->Tbl['bookmarks_folder'].' SET childof='.intval($childof).' WHERE `id`='.intval($fid).' AND `owner`='.$this->uid;
        if (!$this->DB->query($query)) {
            $this->set_error($this->DB->error());
            return false;
        }
        return true;
    }

    public function remove_user()
    {
        return
                // All folders of this user get dropped
                $this->DB->query('DELETE FROM '.$this->DB->Tbl['bookmarks_folder'].' WHERE `owner`='.intval($this->uid))
                // and all addresses of that user ...
                && $this->DB->query('DELETE FROM '.$this->DB->Tbl['bookmarks_item'].' WHERE `owner`='.intval($this->uid));
    }

    public function quota_bookmarksnum($stats = false)
    {
        if (false == $stats) {
            $query = 'SELECT count(*) FROM '.$this->DB->Tbl['bookmarks_item'].' WHERE `owner`='.intval($this->uid);
            list ($num) = $this->DB->fetchrow($this->DB->query($query));
            return $num;
        }
        $query = 'SELECT count(distinct `owner`), count(*) FROM '.$this->DB->Tbl['bookmarks_item'].' WHERE `owner`>0';
        list ($cnt, $sum) = $this->DB->fetchrow($this->DB->query($query));
        if ($cnt) {
            $query = 'SELECT `owner`, count(owner) moep FROM '.$this->DB->Tbl['bookmarks_item'].' WHERE `owner`>0 GROUP BY `owner` ORDER BY moep DESC LIMIT 1';
            list ($max_uid, $max_cnt) = $this->DB->fetchrow($this->DB->query($query));
        }
        return array
                ('count' => isset($cnt) ? $cnt : 0
                ,'sum' => isset($sum) ? $sum : 0
                ,'max_uid' => isset($max_uid) ? $max_uid : 0
                ,'max_count' => isset($max_cnt) ? $max_cnt : 0
                );
    }

    public function quota_foldersnum($stats = false)
    {
        if (false == $stats) {
            $query = 'SELECT count(*) FROM '.$this->DB->Tbl['bookmarks_folder'].' WHERE `owner`='.intval($this->uid);
            list ($num) = $this->DB->fetchrow($this->DB->query($query));
            return $num;
        }
        $query = 'SELECT count(distinct `owner`), count(*) FROM '.$this->DB->Tbl['bookmarks_folder'].' WHERE `owner`>0';
        list ($cnt, $sum) = $this->DB->fetchrow($this->DB->query($query));
        if ($cnt) {
            $query = 'SELECT `owner`, count(owner) moep FROM '.$this->DB->Tbl['bookmarks_folder'].' WHERE `owner`>0 GROUP BY `owner` ORDER BY moep DESC LIMIT 1';
            list ($max_uid, $max_cnt) = $this->DB->fetchrow($this->DB->query($query));
        }
        return array
                ('count' => isset($cnt) ? $cnt : 0
                ,'sum' => isset($sum) ? $sum : 0
                ,'max_uid' => isset($max_uid) ? $max_uid : 0
                ,'max_count' => isset($max_cnt) ? $max_cnt : 0
                );
    }
}
?>
Return current item: phlyMail Lite