Location: PHPKode > projects > phlyMail Lite > phlymail/shared/drivers/mysql/driver.php
<?php
/**
 * The system wide database driver.
 * Provides storage functions for use with a mySQL database
 * It automatically detects, whether it can use ext/mysqli and makes use of it, if so
 *
 * @package phlyMail Nahariya 4.0+
 * @subpackage main application
 * @author  Matthias Sommerfeld
 * @copyright 2002-2010 phlyLabs, Berlin http://phlylabs.de
 * @version 4.5.1mod1 2010-11-11
 */
class driver {
    // This holds all config options
    public $DB;
    public $Tbl;
    public $dbh;
    protected $mode = 'classic';
    public $features;

    /**
     * Constructor
     * Read the config and open the DB
     *
     * @param string $Conf Path to the config file
     * @param bool Use secure mail account passwords; if false, these are considedered plain text
     * @return object
     */
    public function __construct($Conf, $secaccpass = true)
    {
        if (!file_exists($Conf)) {
            global $_PM_;
            // Translate old CSV like mysql settings file over to the ini based one
            if (file_exists($_PM_['path']['conf'].'/driver.'.$_PM_['core']['database'].'.conf.php')
                    && !file_exists($_PM_['path']['conf'].'/driver.'.$_PM_['core']['database'].'.ini.php')) {
                if (!is_writable($_PM_['path']['conf'])) die('Please check permissions for '.$_PM_['path']['conf'].' and repeat!');
                $newDB = array();
                foreach (file($_PM_['path']['conf'].'/driver.'.$_PM_['core']['database'].'.conf.php') as $l) {
                    if ($l{0} == '#') continue;
                    if (substr($l, 0, 15) == '<?php die(); ?>') continue;
                    list ($k, $v) = explode(';;', $l);
                    $newDB[str_replace('mysql_', '', $k)] = trim($v);
                }
                $state = basics::save_config($_PM_['path']['conf'].'/driver.'.$_PM_['core']['database'].'.ini.php', $newDB, true, $_PM_['core']['file_umask']);
                if ($state) {
                    @unlink($_PM_['path']['conf'].'/driver.'.$_PM_['core']['database'].'.conf.php');
                } else {
                    die('Could not convert old DB settings file into current format. Check permissions for '.$_PM_['path']['conf'].' and repeat!');
                }
            } else {
                return false;
            }
        }
        // Initialise database driver choices
        $this->DB = parse_ini_file($Conf);
        // Make sure, logging would be possible
        $this->settings['logpath'] = false;
        if (isset($GLOBALS['_PM_']['logging']['log_sql']) && $GLOBALS['_PM_']['logging']['log_sql']) {
            $logpath = $GLOBALS['_PM_']['path']['logging'].'/sql/'.preg_replace('!\%(\w)!e', 'date("\1")', $GLOBALS['_PM_']['logging']['basename']);
            if (basics::create_dirtree(dirname($logpath))) $this->settings['logpath'] = $logpath;
        }
        $this->DB['secaccpass'] = $secaccpass;
        $this->DB['db_pref'] = '`'.$this->DB['database'].'`.'.$this->DB['prefix'].'_';
        $this->Tbl['adb_address'] = $this->DB['db_pref'].'adb_adr';
        $this->Tbl['core_noauth'] = $this->DB['db_pref'].'core_noauth';
        $this->Tbl['core_thumbs'] = $this->DB['db_pref'].'core_thumbs';
        $this->Tbl['user']  = $this->DB['db_pref'].'user';
        $this->Tbl['user_accounting'] = $this->DB['db_pref'].'user_accounting';
        $this->Tbl['user_favfolders'] = $this->DB['db_pref'].'user_favouritefolders';
        $this->Tbl['user_foldersettings'] = $this->DB['db_pref'].'user_foldersettings';
        $this->Tbl['user_quota'] = $this->DB['db_pref'].'user_quota';
        $this->Tbl['user_smslogging'] = $this->DB['db_pref'].'user_smslogging';
        $this->Tbl['sendto_handler'] = $this->DB['db_pref'].'sendto_handler';
        // These should go elsewhere, alongside the methods for mail account management!
        $this->Tbl['profiles'] = $this->DB['db_pref'].'profiles';
        $this->Tbl['signatures'] = $this->DB['db_pref'].'signatures';
        $this->Tbl['aliases'] = $this->DB['db_pref'].'profile_alias';

        // Putting some info to the object, what features the underlying DB supports
        $this->features = array('shares' => false, 'groups' => false, 'permissions' => false);
        // Open Database connection
        $this->dbh = $this->open();
        if (!is_object($this->dbh)) return false;

        $this->query('SET SESSION sql_mode="ALLOW_INVALID_DATES"');
        return true;
    }

    protected function open()
    {
        if (function_exists('mysqli_connect')) {
            $this->mode = 'improved';
            return new driver_mysqli($this->DB['host'], $this->DB['user'], $this->DB['pass'], $this->DB['database']);
        } else {
            $this->mode = 'classic';
            return new driver_mysql($this->DB['host'], $this->DB['user'], $this->DB['pass'], $this->DB['database']);
        }
    }

    /**
     * Interfacing methods to allow the handlers to use this DB connection
     */
    public function query($query)
    {
        if ($this->settings['logpath']) file_put_contents($this->settings['logpath'], date('Y-m-d H:i:s').' ' .$query.LF, FILE_APPEND);
        return $this->dbh->query($query);
    }
    public function fetchrow($qid) { return $this->dbh->fetchrow($qid); }
    public function fetchassoc($qid) { return $this->dbh->fetchassoc($qid); }
    public function fetchobj($qid) { return $this->dbh->fetchobj($qid); }
    public function affected() { return $this->dbh->affected(); }
    public function numrows($qid) { return $this->dbh->numrows($qid); }
    public function error() { return $this->dbh->error(); }
    public function serverinfo() { return $this->dbh->serverinfo(); }
    public function insertid() { return $this->dbh->insertid(); }
    public function close() { return true; }
    public function ping() { if (!$this->dbh->ping()) { unset($this->dbh); $this->open(); } }

    /**
     * Internal method used to escape passed data before building queries thus preventing SQL Injection Attacks.
     * This method takes care of the magic quotes setting.
     * @param  string  Unescaped string
     *[@param  resource  The connection handle of an open MySQL connection for obeying the current encoding]
     *[@param  string  Per default, non numeric values are surrounded by double quotes. Pass whatever you like]
     * @return string  Escaped string
     * @since 3.9.2
     */
    public function escape($value, $res = null, $q = '')
    {
    	if (get_magic_quotes_gpc()) $value = stripslashes($value); // Stripslashes
    	if (!is_numeric($value)) return $q.$this->dbh->escape($value, null).$q; // Quote if not integer
    	return $q.$value.$q;
    }

    //
    // Methods used by the app
    //

    // Check whether a username:password combination matches a valid user of the system
    // Input  : authenticate(string user name)
    // Returns: $return     array data on success, FALSE otherwise
    //          $return[0] uid
    //          $return[1] MD5 hash of user's password
    public function authenticate($un = '')
    {
        return $this->fetchrow($this->query('SELECT 1,password FROM '.$this->Tbl['user'].' WHERE username="'.$this->escape($un).'" AND active="1"'));
    }

    // Return the basic user data for an user ID
    // Input  : get_usrdata(integer user id)
    // Returns: $return    array data on success, FALSE otherwise
    //          $return['accname'][id]  Display name of the account(s)
    public function get_usrdata($uid = 0)
    {
        $return = $this->fetchassoc($this->query('SELECT 1,username,externalemail,contactid'
                .',active,unix_timestamp(logintime) login_time, unix_timestamp(logouttime) logout_time'
                .' FROM '.$this->Tbl['user']));
        if ($return['contactid']) {
            $qid = $this->query('SELECT firstname,lastname,birthday,email1 email,www,tel_business,tel_private,cellular,fax FROM '
                    .$this->Tbl['adb_address'].' WHERE aid='.$return['contactid']);
            if ($qid) {
                $usr = $this->fetchassoc($qid);
                if (is_array($usr)) $return = array_merge($return, $usr);
            }
        }
        return $return;
    }

    // Returns failure count and timestamp for an user ID
    // Input  : get_usrfail(integer user id)
    // Returns: $return    array data on success, FALSE otherwise
    //          $return['fail_count']  Number of failures
    //          $return['fail_time']   Timestamp of last fail
    public function get_usrfail($uid = false)
    {
        if (!$uid) return false;
        return $this->fetchassoc($this->query('SELECT fail_count,fail_time FROM '.$this->Tbl['user']));
    }

    // Set failure count of a certain user
    // Input  : set_usrfail(integer user id)
    // Returns: $return    boolean, TRUE on success, FALSE otherwise
    public function set_usrfail($uid = false)
    {
        if (!$uid) return;
        return $this->query('UPDATE '.$this->Tbl['user'].' set fail_count=fail_count+1, fail_time=unix_timestamp()');
    }

    // Reset failure count ( == set to 0) of a certain user
    // Input  : reset_usrfail(integer user id)
    // Returns: $return    boolean, TRUE on success, FALSE otherwise
    public function reset_usrfail($uid = false)
    {
        if (!$uid) return;
        return $this->query('UPDATE '.$this->Tbl['user'].' set fail_count=0, fail_time=0');
    }

    // Set login timestamp of a specific user
    // Input : set_logintime(integer user id)
    // Return: void
    public function set_logintime($uid = false)
    {
        if (!$uid) return;
        return $this->query('UPDATE '.$this->Tbl['user'].' set logintime=NOW()');
    }

    // Set logout timestamp of a specific user
    // Input : set_logouttime(integer user id)
    // Return: void
    public function set_logouttime($uid = false)
    {
        if (!$uid) return;
        $this->query('UPDATE '.$this->Tbl['user'].' set logouttime=NOW()');
        $qid = $this->query('SELECT TIME_TO_SEC(TIMEDIFF(logouttime, logintime)) FROM '.$this->Tbl['user'].' LIMIT 1');
        list ($online) = $this->fetchrow($qid);
        return $this->set_user_accounting('online', date('Ym'), $uid, $online);

    }

    // Insert a new user into the database
    // Input  : $input  array containing user data
    //          $input['username'] Login name
    //          $input['password'] Password
    //          $input['email']    '0' if none available, '1' if any (found in contactid)
    //          $input['active']   '0' for no, '1' for yes
    // Returns: $return  UserID of created user on success, FALSE otherwise
    public function add_user($input)
    {
        $this->query('INSERT '.$this->Tbl['adb_address'].' SET `type`="user"'
                .',`visibility`='.(isset($input['visibility']) && $input['visibility'] == 'public' ? '"public"' : '"private"')
                .',firstname="'.(isset($input['firstname']) ? $this->escape($input['firstname']) : '').'"'
                .',lastname="'.(isset($input['lastname']) ? $this->escape($input['lastname']) : '').'"'
                .',birthday="'.(isset($input['birthday']) ? $this->escape($input['birthday']) : '').'"'
                .',email1="'.(isset($input['email']) ? $this->escape($input['email']) : '').'"'
                .',www="'.(isset($input['www']) ? $this->escape($input['www']) : '').'"'
                .',tel_private="'.(isset($input['tel_private']) ? $this->escape($input['tel_private']) : '').'"'
                .',tel_business="'.(isset($input['tel_business']) ? $this->escape($input['tel_business']) : '').'"'
                .',cellular="'.(isset($input['cellular']) ? $this->escape($input['cellular']) : '').'"'
                .',fax="'.(isset($input['fax']) ? $this->escape($input['fax']) : '').'"'
                );
        $input['contactid'] = $this->insertid();
        if ($this->query('INSERT '.$this->Tbl['user'].' SET'
                .' username="'.$this->escape($input['username']).'"'
                .',externalemail="'.$this->escape($input['externalemail']).'"'
                .',password=md5("'.$this->escape($input['password']).'")'
                .',contactid='.(isset($input['contactid']) ? intval($input['contactid']) : 'NULL')
                .',active="'.$this->escape($input['active']).'"'
                .',choices="'.(isset($input['choices']) ? $this->escape($input['choices']) : '').'"')) {
            return $this->insertid();
        }
        return false;
    }

    // Update the record of a user in the database
    // Input  : $input  array containing user data
    //          $input['uid']            UserID to update
    //          $input['username']       Login name
    //          $input['password']       Password (Omit if unchanged)
    //          $input['email']  Email address for notifications
    //          $input['active']         '0' for no, '1' for yes (Omit if unchanged)
    // Returns: $return  TRUE on success, FALSE otherwise
    public function upd_user($input)
    {
        if (empty($input)) return true;
        $sqladd = '';
        list ($adrid) = $this->fetchrow($this->query('SELECT u.contactid FROM '.$this->Tbl['user'].' u,'.$this->Tbl['adb_address'].' a'
                .' WHERE u.uid='.intval($input['uid']).' AND a.aid=u.contactid'));
        if ($adrid) {
            $this->query('UPDATE '.$this->Tbl['adb_address'].' SET'
                    .' firstname='.(isset($input['firstname']) ? '"'.$this->escape($input['firstname']).'"' : 'firstname')
                    .',lastname='.(isset($input['lastname']) ? '"'.$this->escape($input['lastname']).'"' : 'lastname')
                    .',birthday='.(isset($input['birthday']) ? '"'.$this->escape($input['birthday']).'"' : 'birthday')
                    .',email1='.(isset($input['email']) ? '"'.$this->escape($input['email']).'"' : 'email1')
                    .',www='.(isset($input['www']) ? '"'.$this->escape($input['www']).'"' : 'www')
                    .',tel_private='.(isset($input['tel_private']) ? '"'.$this->escape($input['tel_private']).'"' : 'tel_private')
                    .',tel_business='.(isset($input['tel_business']) ? '"'.$this->escape($input['tel_business']).'"' : 'tel_business')
                    .',cellular='.(isset($input['cellular']) ? '"'.$this->escape($input['cellular']).'"' : 'cellular')
                    .',fax='.(isset($input['fax']) ? '"'.$this->escape($input['fax']).'"' : 'fax')
                    .(isset($input['visibility']) ? ',`visibility`='.($input['visibility'] == 'public' ? '"public"' : '"private"') : '')
                    .' WHERE aid='.intval($adrid).' AND `type`="user" AND `owner`=0' # FIXME: Better would be .intval($input['uid'])
                    );
        } else {
            $this->query('INSERT '.$this->Tbl['adb_address'].' SET `type`="user"'
                    .',`visibility`='.(isset($input['visibility']) && $input['visibility'] == 'public' ? '"public"' : '"private"')
                    .',firstname="'.(isset($input['firstname']) ? $this->escape($input['firstname']) : '').'"'
                    .',lastname="'.(isset($input['lastname']) ? $this->escape($input['lastname']) : '').'"'
                    .',birthday="'.(isset($input['birthday']) ? $this->escape($input['birthday']) : '').'"'
                    .',email1="'.(isset($input['email']) ? $this->escape($input['email']) : '').'"'
                    .',www="'.(isset($input['www']) ? $this->escape($input['www']) : '').'"'
                    .',tel_private="'.(isset($input['tel_private']) ? $this->escape($input['tel_private']) : '').'"'
                    .',tel_business="'.(isset($input['tel_business']) ? $this->escape($input['tel_business']) : '').'"'
                    .',cellular="'.(isset($input['cellular']) ? $this->escape($input['cellular']) : '').'"'
                    .',fax="'.(isset($input['fax']) ? $this->escape($input['fax']) : '').'"'
                    );
            $sqladd = ',contactid='.$this->insertid();
        }
        return $this->query('UPDATE '.$this->Tbl['user'].' SET uid=uid'.$sqladd
                .(isset($input['username']) ? ',username="'.$this->escape($input['username']).'"' : '')
                .(isset($input['externalemail']) ? ',externalemail="'.$this->escape($input['externalemail']).'"' : '')
                .(isset($input['password']) && $input['password'] ? ',password=md5("'.$this->escape($input['password']).'")' : '')
                .(isset($input['active']) && $input['active'] != '' ? ',active="'.$this->escape($input['active']).'"' : '')
                .' WHERE uid='.intval($input['uid']));
    }

    // Delete a user and his/her accounts from the database
    // Input  : $username  username of the user to be deleted
    // Returns: $return    TRUE on success, FALSE otherwise
    public function delete_user($un)
    {
        list ($uid, $contactid) = $this->fetchrow($this->query('SELECT uid,contactid FROM '.$this->Tbl['user'].' WHERE username="'.$this->escape($un).'"'));
        if ($this->query('DELETE FROM '.$this->Tbl['user'].' WHERE uid='.intval($uid))) {
            $this->query('DELETE FROM '.$this->Tbl['profiles'].' WHERE uid='.intval($uid));
            $this->query('DELETE FROM '.$this->Tbl['user_quota'].' WHERE uid='.intval($uid));
            $this->query('DELETE FROM '.$this->Tbl['user_accounting'].' WHERE uid='.intval($uid));
            $this->query('DELETE FROM '.$this->Tbl['user_favfolders'].' WHERE uid='.intval($uid));
            $this->query('DELETE FROM '.$this->Tbl['user_smslogging'].' WHERE uid='.intval($uid));
            $this->query('DELETE FROM '.$this->Tbl['signatures'].' WHERE uid='.intval($uid));
            $this->query('DELETE FROM '.$this->Tbl['aliases'].' WHERE uid='.intval($uid));
            $this->query('DELETE FROM '.$this->Tbl['adb_address'].' WHERE aid='.intval($contactid));
            if (isset($this->features['shares']) && $this->features['shares']) {
                $this->query('DELETE FROM '.$this->Tbl['share_folder'].' WHERE uid='.intval($uid));
                $this->query('DELETE FROM '.$this->Tbl['share_item'].' WHERE uid='.intval($uid));
            }
            if (isset($this->features['groups']) && $this->features['groups']) {
                $this->query('DELETE FROM '.$this->Tbl['user_group'].' WHERE uid='.intval($uid));
            }
            if (isset($this->features['permissions']) && $this->features['permissions']) {
                $this->query('DELETE FROM '.$this->Tbl['user_perms'].' WHERE uid='.intval($uid));
            }
            return true;
        }
        return false;
    }

    // Get POP3 connection data of a certain user
    // Input  : get_popconnect(integer user id, string user name, integer account number)
    // Returns: $return    array data on success, FALSE otherwise
    //          $return['popserver']  string POP3 server
    //          $return['popport']    string POP3 port
    //          $return['popuser']    string POP3 user name
    //          $return['poppass']    string POP3 password
    //          $return['popnoapop']  use APOP, where 1 means no, 0 auto
    public function get_popconnect($uid = 0, $username = '', $accid = 0, $real_id = 0)
    {
        $q_r = ($real_id) ? '`id`='.intval($real_id) : 'a.accid='.intval($accid);
        $return = $this->fetchassoc($this->query
                ('SELECT a.accname,a.popserver,a.popport,a.popuser,a.poppass,a.popnoapop,a.popsec,a.inbox,a.sent,a.drafts'
                .',a.junk,a.waste,a.templates,a.onlysubscribed, a.imapprefix FROM '
                .$this->Tbl['profiles'].' a WHERE '.$q_r
                ));
        if ($this->DB['secaccpass']) $return['poppass'] = $this->deconfuse($return['poppass'], $return['popserver'].$return['popport'].$return['popuser']);
        return $return;
    }

    //          $return['smtpport']     string SMTP port
    //          $return['smtpuser']     string SMTP user name
    //          $return['smtppass']     string SMTP password
    //          $return['smtpafterpop'] do SMTP-after-POP, where 1 means yes, 0 means no
    public function get_smtpconnect($uid = 0, $username = '', $accid = 0, $real_id = 0)
    {
        $q_r = ($real_id) ? '`id`='.intval($real_id) : 'a.accid='.intval($accid);
        $return = $this->fetchassoc($this->query
                ('SELECT a.accname,a.smtpserver,a.smtpport,a.smtpuser,a.smtppass,a.smtpafterpop,a.smtpsec,a.userheaders FROM '
                .$this->Tbl['profiles'].' a WHERE '.$q_r
                ));
        if ($this->DB['secaccpass']) $return['smtppass'] = $this->deconfuse($return['smtppass'], $return['smtpserver'].$return['smtpport'].$return['smtpuser']);
        $return['userheaders'] = unserialize($return['userheaders']);
        return $return;
    }

    // Set login timestamp of a specific account of a user
    // Input : set_poplogintime(integer user id, integer account id)
    // Return: void
    public function set_poplogintime($uid = false, $accid = false, $pid = false)
    {
        if (!$uid) return;
        if (!$accid && !$pid) return;
        $q_r = ($accid) ? 'accid='.intval($accid) : '`id`='.intval($pid);
        return $this->query('UPDATE '.$this->Tbl['profiles'].' set logintime=NOW() WHERE uid=1 AND '.$q_r);
    }

    // Get index for all accounts of a certain user
    // Input  : get_accidx(integer user id, string user name)
    // Returns: $return      array data on success, FALSE otherwise
    //          $return[id]  Display name of the account(s)
    public function get_accidx($uid = 0, $username = '', $extended = false, $protocol = false)
    {
        $return = array();
        $q_r = ($protocol) ? ' AND `acctype`="'.$this->escape($protocol).'"' : '';
        $qid = $this->query('SELECT `id`, `accid`, `accname`, `acctype` FROM '.$this->Tbl['profiles']
                .' WHERE uid='.intval($uid).$q_r.' ORDER BY `order` ASC, `accid` ASC');
        if ($qid) {
            while ($line = $this->fetchassoc($qid)) {
            	if ($extended) {
                	$return[$line['id']] = $line;
            	} else {
            		$return[$line['accid']] = $line['accname'];
            	}
            }
        }
        return $return;
    }


    /**
     * Returns the ID of the profile a certain email address matches against
     * @param  int  User ID to query the DB for
     * @param  string  Email address to find the profile for
     * @return  int  ID of the profile or FALSE on no match
     * @since 3.6.2
     */
    public function get_profile_from_email($uid = 0, $email = '')
    {
        $return = array(0, 0);
        if (!$email) return $return;
        $query = 'SELECT accid, 0 FROM '.$this->Tbl['profiles'].' WHERE "'.$this->escape($email).'" LIKE CONCAT("%", address, "%") LIMIT 1';
        $return = $this->fetchrow($this->query($query));
        if (!$return[0]) {
            $query = 'SELECT profile, aid FROM '.$this->Tbl['aliases'].' WHERE "'.$this->escape($email).'" LIKE CONCAT("%", email, "%") LIMIT 1';
            $return = $this->fetchrow($this->query($query));
        }
        return $return;
    }

    /**
     * Returns the real DB index for a given "account id" for the given user
     *
     * @param int $uid
     * @param int $accid
     * @return int
     * @since 3.7.1
     */
    public function get_profile_from_accid($uid, $accid)
    {
        list ($return) = $this->fetchrow($this->query('SELECT `id` FROM '.$this->Tbl['profiles'].' WHERE accid='.intval($accid)));
        return $return;
    }

    /**
     * Returns the "account id" for a given real DB index for the given user
     *
     * @param int $uid
     * @param int $id
     * @return int
     * @since 4.3.0
     */
    public function get_accid_from_profile($uid, $id)
    {
        list ($return) = $this->fetchrow($this->query('SELECT `accid` FROM '.$this->Tbl['profiles'].' WHERE `id`='.intval($id)));
        return $return;
    }

    /**
     * Tries to determine a default email address for a user, if none is defined, any email address is returned
     * @param  int  user ID to query the DB for
     * @param  ref  _PM_ structure
     * @return string  Email address on success, false on failure (aka: no profiles yet)
     * @since  3.6.4
     */
    public function get_default_email($uid, &$settings)
    {
        $return = false;
        if (isset($settings['core']['default_profile']) && $settings['core']['default_profile']) {
            $query = 'SELECT address FROM '.$this->Tbl['profiles'].' WHERE accid='.intval($settings['core']['default_profile']);
            list ($return) = $this->fetchrow($this->query($query));
        }
        if (!$return) {
            $query = 'SELECT address FROM '.$this->Tbl['profiles'].' LIMIT 1';
            list ($return) = $this->fetchrow($this->query($query));
        }
        return $return;
    }

    // Get the highest account id in use for a specific user
    // Input  : get_maxaccid(integer user id)
    // Returns: integer next possible profile id
    public function get_maxaccid($uid = 0)
    {
        if (!$uid) return 1;
        list ($curr) = $this->fetchrow($this->query('SELECT max(accid) FROM '.$this->Tbl['profiles']));
        return ($curr+1);
    }

    /**
     * Get personal data of a certain user
     * @param integer user id
     * @param string user name
     * @param integer account number
     * @return array data on success, FALSE otherwise; The erray contains
     * - sig_on     integer is the signature active?
     * - real_name  string real name of the user
     * - address    string email address to use for sending
     * - signature  blob signature
     * - aliases  array (aid => int, real_name => string, email => string)
     */
    public function get_accdata($uid = 0, $username = '', $accid = 0, $real_id = 0)
    {
        $q_r = ($real_id) ? '`id`='.intval($real_id) : 'accid='.intval($accid);
        $return = $this->fetchassoc($this->query('SELECT acctype,accid,uid,sig_on,real_name,checkevery,be_checkevery,leaveonserver,localkillserver,cachetype'
                .',checkspam,inbox,sent,drafts,waste,junk,templates,unix_timestamp(logintime) logintime,address,signature,onlysubscribed'
                .',imapprefix,userheaders,sendvcf FROM '.$this->Tbl['profiles'].' WHERE '.$q_r));
        $return['userheaders'] = ($return['userheaders']) ? unserialize($return['userheaders']) : array();
        $return['aliases'] = array();
        if (!isset($return['uid'])) return $return;
        $qid = $this->query('SELECT `aid`,`real_name`,`email`,`signature`,`sendvcf` FROM '.$this->Tbl['aliases']
                .' WHERE uid='.$return['uid'].' AND profile='.$return['accid']);
        while ($alias = $this->fetchassoc($qid)) {
            $return['aliases'][$alias['aid']] = $alias;
        }
        return $return;
    }

    /**
     * Check, if a given username (already) exists in the database
     *
     * @param string $username Username to check the DB for
     * @param bool $giveuid  Set to true to get the UID, if any; Default false
     * @return bool|int If $giveuid == true returns the UID, else TRUE if the username already exists
     */
    public function checkfor_username($username = '', $giveuid = false)
    {
        $qid = $this->query('SELECT uid FROM '.$this->Tbl['user'].' WHERE username="'.$this->escape($username).'"');
        if (!$this->numrows($qid)) return false;
        if (!$giveuid) return true;
        list ($exists) = $this->fetchrow($qid);
        return $exists;
    }

    /**
     * Insert new account for a user into the database
     * @param array $input  array containing user data
     * @return  Record ID of created account on success, FALSE otherwise
     */
    public function add_account($input)
    {
        list ($input['accid']) = $this->fetchrow($this->query('SELECT max(accid)+1 FROM '.$this->Tbl['profiles']));
        if ($input['accid'] == 0) $input['accid'] = 1;

        if ($this->DB['secaccpass']) $input['poppass'] = $this->confuse($input['poppass'], $input['popserver'].$input['popport'].$input['popuser']);
        if ($this->DB['secaccpass']) $input['smtppass'] = $this->confuse($input['smtppass'], $input['smtpserver'].$input['smtpport'].$input['smtpuser']);
        $input['userheaders'] = isset($input['userheaders']) ? serialize($input['userheaders']) : '';

        if ($this->query('INSERT '.$this->Tbl['profiles']
                .' (uid,accid,acctype,accname,sig_on,popserver,popport,popuser,poppass,popnoapop,popsec'
                .',smtpserver,smtpport,smtpuser,smtppass,smtpafterpop,smtpsec,real_name,address,signature'
                .',leaveonserver,localkillserver,cachetype,checkspam,checkevery,be_checkevery,inbox,sent,drafts,waste,junk,templates'
                .',onlysubscribed,imapprefix,userheaders,sendvcf) values ('
                .'"'.intval($input['uid']).'","'.intval($input['accid']).'","'.$this->escape($input['acctype']).'"'
                .',"'.$this->escape($input['accname']).'"'
                .',"'.$this->escape($input['sig_on']).'","'.$this->escape($input['popserver']).'"'
                .',"'.intval($input['popport']).'","'.$this->escape($input['popuser']).'"'
                .',"'.$this->escape($input['poppass']).'","'.$this->escape($input['popnoapop']).'","'.$this->escape($input['popsec']).'"'
                .',"'.$this->escape($input['smtpserver']).'","'.intval($input['smtpport']).'"'
                .',"'.$this->escape($input['smtpuser']).'","'.$this->escape($input['smtppass']).'"'
                .',"'.$this->escape($input['smtpafterpop']).'","'.$this->escape($input['smtpsec']).'","'.$this->escape($input['real_name']).'"'
                .',"'.$this->escape($input['address']).'","'.intval($input['signature']).'"'
                .',"'.$this->escape($input['leaveonserver']).'","'.$this->escape($input['localkillserver']).'"'
                .',"'.(isset($input['cachetype']) ? $this->escape($input['cachetype']) : 'full').'"'
                .',"'.$this->escape($input['checkspam']).'","'.intval($input['checkevery']).'"'
                .',"'.intval($input['be_checkevery']).'",'.intval($input['inbox'])
                .','.(isset($input['sent']) ? intval($input['sent']) : '0')
                .','.(isset($input['drafts']) ? intval($input['drafts']) : '0')
                .','.(isset($input['waste']) ? intval($input['waste']) : '0')
                .','.(isset($input['junk']) ? intval($input['junk']) : '0')
                .','.(isset($input['templates']) ? intval($input['templates']) : '0')
                .','.(isset($input['onlysubscribed']) ? intval($input['onlysubscribed']) : '"0"')
                .',"'.(isset($input['imapprefix']) ? $this->escape($input['imapprefix']) : '').'"'
                .',"'.$this->escape($input['userheaders']).'"'
                .',"'.(isset($input['sendvcf']) ? $this->escape($input['sendvcf']) : 'none').'")'
                )) {
            return $input['accid'];
        }
        return false;
    }

    /**
     * Update the record of an account for a user in the database
     * @param array $input  array containing user data
     * @return  TRUE on success, FALSE otherwise
     */
    public function upd_account($input)
    {
        if (isset($input['userheaders'])) $input['userheaders'] = serialize($input['userheaders']);
        $query = 'UPDATE '.$this->Tbl['profiles'].' SET ';
        foreach (array
                ('accname', 'acctype', 'sig_on', 'popserver', 'popport', 'popuser', 'popnoapop', 'popsec'
                ,'smtpserver', 'smtpport', 'smtpuser', 'smtpafterpop', 'smtpsec'
                ,'real_name', 'address', 'signature', 'leaveonserver', 'localkillserver', 'cachetype'
                ,'checkspam', 'checkevery', 'be_checkevery', 'inbox', 'sent', 'drafts', 'waste', 'junk', 'templates'
                ,'onlysubscribed' ,'imapprefix', 'userheaders', 'sendvcf') as $field) {
            if (!isset($input[$field])) continue;
            $query.= $field.'="'.$this->escape($input[$field]).'", ';
        }
        if ($input['poppass'] != false) {
            if ($this->DB['secaccpass']) $input['poppass'] = $this->confuse($input['poppass'], $input['popserver'].$input['popport'].$input['popuser']);
            $query .= 'poppass="'.$this->escape($input['poppass']).'", ';
        }
        if ($input['smtppass'] != false) {
            if ($this->DB['secaccpass']) $input['smtppass'] = $this->confuse($input['smtppass'],
                                                $input['smtpserver'].$input['smtpport'].$input['smtpuser']);
            $query .= 'smtppass="'.$this->escape($input['smtppass']).'", ';
        }
        $query .= 'logintime=logintime WHERE accid='.intval($input['accid']);
        return ($this->query($query));
    }

    /**
     * Takes an array as argument, where the order is contained
     * @param array $input Key: Account ID, Value: position in list
     * @return bool
     */
    public function reorder_accounts($uid, $input)
    {
        $uid = intval($uid);
        $oldMap = array();
        foreach ($this->get_accidx($uid, null, true, null) as $id => $arr) {
            $oldMap[$arr['accid']] = $id;
        }
        foreach ($input as $k => $v) {
            $this->query('UPDATE '.$this->Tbl['profiles'].' SET `order`='.($v).' WHERE `id`='.$oldMap[intval($k)]);
            $this->query('UPDATE '.$this->DB['db_pref'].'email_folders SET `layered_id`='.$v // This query possibly breaks some rules ;)
                    .' WHERE `att_icon`=":imapbox" AND `folder_path`="'.$oldMap[intval($k)].':"');
        }
    }

    // Delete an account of a given user from database
    // Input:  delete_account(string username, integer accountID)
    // Return: TRUE on success, FALSE otherwise
    public function delete_account($un = '', $accountnumber = '')
    {
        $sql = 'SELECT u.`uid`, p.`id` FROM '.$this->Tbl['user'].' u, '.$this->Tbl['profiles'].' p'
                .' WHERE u.`username`="'.$this->escape($un).'" AND u.`uid`=p.`uid` AND p.`accid`='.intval($accountnumber);
        list ($uid, $pid) = $this->fetchrow($this->query($sql));
        if ($this->query('DELETE FROM '.$this->Tbl['profiles'].' WHERE uid='.intval($uid).' AND `id`='.intval($pid))) {
            $this->query('DELETE FROM '.$this->Tbl['aliases'].' WHERE `profile`='.intval($pid));
            $this->query('ALTER TABLE '.$this->Tbl['profiles'].' ORDER BY `id`');
            return true;
        }
        return false;
    }

    /**
     * Add an alias (alternative email address plus real name) for a user and profile
     * @param  int  User ID to add the alias for
     * @param  int  profile ID to add the alias to
     * @param  string  Email address
     *[@param  string  Real name (might be empty)]
     *[@param  null|int  NULL for as of account, 0 for none, sig. ID otherwise; Default: NULL]
     *[@param  string  One of the tokens for the VCF type; Default: as account]
     * @return  bool  TRUE if successfull, FALSE on failures
     * @since 3.6.2
     */
    public function add_alias($uid = 0, $pid = 0, $email = '', $realname = '', $sig = null, $vcf = 'default')
    {
        if (!$email) return false;
        $query = 'INSERT '.$this->Tbl['aliases'].' SET `uid`='.intval($uid).',`profile`='.intval($pid)
                .',`email`="'.$this->escape($email).'",`real_name`="'.$this->escape($realname).'"'
                .',`signature`='.(is_null($sig) ? 'NULL' : intval($sig)).',`sendvcf`="'.$this->escape($vcf).'"';
        return $this->query($query);
    }

    /**
     * Update an alias
     * @param  int  User ID to update the alias for
     * @param  int  alias ID to update
     * @param  string  Email address
     *[@param  string  Real name (might be empty)]
     *[@param  null|int  NULL for as of account, 0 for none, sig. ID otherwise; Default: NULL]
     *[@param  string  One of the tokens for the VCF type; Default: as account]
     * @return  bool  TRUE if successfull, FALSE on failures
     * @since 3.6.2
     */
    public function update_alias($uid = 0, $aid = 0, $email = '', $realname = '', $sig = null, $vcf = 'default')
    {
        if (!$email) return;
        $query = 'UPDATE '.$this->Tbl['aliases'].' SET `email`="'.$this->escape($email)
                .'",`real_name`="'.$this->escape($realname).'"'
                .',`signature`='.(is_null($sig) ? 'NULL' : intval($sig)).',`sendvcf`="'.$this->escape($vcf).'"'
                .' WHERE uid='.intval($uid).' AND aid='.intval($aid);
        return $this->query($query);
    }

    /**
     * Delete an alias again
     * @param  int  User ID to delete the alias for
     * @param  int  alias ID to delete
     * @return  bool  TRUE if successfull, FALSE on failures
     * @since 3.6.2
     */
    public function delete_alias($uid, $aid)
    {
        return $this->query('DELETE FROM '.$this->Tbl['aliases'].' where aid='.intval($aid));
    }

    /**
     * Add an userheader (user defined mail header) for a user and profile
     * @param  int  User ID to add the userheader for
     * @param  int  profile ID to add the userheader to
     * @param  string  Header name
     * @param  string  Header value
     * @return  bool  TRUE if successfull, FALSE on failures
     * @since 3.8.2
     */
    public function add_uhead($uid = 0, $pid = 0, $hkey = '', $hval = '')
    {
        if (!$hkey) return false;
        list ($uheads) = $this->fetchrow($this->query('SELECT userheaders FROM '.$this->Tbl['profiles'].' WHERE accid='.intval($pid)));
        $uheads = ($uheads) ? unserialize($uheads) : array();
        $uheads[$hkey] = $hval;
        $uheads = serialize($uheads);
        $query = 'UPDATE '.$this->Tbl['profiles'].' SET userheaders="'.$this->escape($uheads).'" WHERE accid='.intval($pid);
        return $this->query($query);
    }

    /**
     * Update an userheader
     * @param  int  User ID to update the userheader for
     * @param  int  userheader ID to update
     * @param  string  Header name to replace
     * @param  string  Header name (new value)
     * @param  string  Header value
     * @return  bool  TRUE if successfull, FALSE on failures
     * @since 3.8.2
     */
    public function update_uhead($uid = 0, $pid = 0, $oldkey = '', $hkey = '', $hval = '')
    {
        if (!$hkey) return;
        list ($uheads) = $this->fetchrow($this->query('SELECT userheaders FROM '.$this->Tbl['profiles'].' WHERE accid='.intval($pid)));
        $uheads = ($uheads) ? unserialize($uheads) : array();
        if ($hkey != $oldkey) unset($uheads[$oldkey]);
        $uheads[$hkey] = $hval;
        $uheads = serialize($uheads);
        $query = 'UPDATE '.$this->Tbl['profiles'].' SET userheaders="'.$this->escape($uheads).'" WHERE accid='.intval($pid);
        return $this->query($query);
    }

    /**
     * Delete an user defined mail header again
     * @param  int  User ID to delete the userheader for
     * @param  string  Header name
     * @return  bool  TRUE if successfull, FALSE on failures
     * @since 3.8.2
     */
    public function delete_uhead($uid = 0, $pid = 0, $hkey)
    {
        if (!$hkey) return;
        list ($uheads) = $this->fetchrow($this->query('SELECT userheaders FROM '.$this->Tbl['profiles'].' WHERE accid='.intval($pid)));
        $uheads = ($uheads) ? unserialize($uheads) : array();
        unset($uheads[$hkey]);
        $uheads = serialize($uheads);
        $query = 'UPDATE '.$this->Tbl['profiles'].' SET userheaders="'.$this->escape($uheads).'" WHERE accid='.intval($pid);
        return $this->query($query);
    }

    /**
     * Add a signature
     *
     * @param integer $uid
     * @param string $title
     * @param string $signature
     * @param string $signature_html
     * @return bool
     */
    public function add_signature($uid = 0, $title = '', $signature = '', $signature_html = '')
    {
        if (!$uid || !$signature) return;
        return $this->query('INSERT '.$this->Tbl['signatures'].' SET title="'.$this->escape($title).'"'
                .', signature="'.$this->escape($signature).'", signature_html="'.$this->escape($signature_html).'"');
    }

    /**
     * Update a signature
     *
     * @param integer $uid
     * @param integer $id
     * @param string $title
     * @param string $signature
     * @param string $signature_html
     * @return bool
     */
    public function update_signature($uid = 0, $id = 0, $title = '', $signature = '', $signature_html = '')
    {
        if (!$uid || ! $id || !$signature) return;
        return $this->query('UPDATE '.$this->Tbl['signatures'].' SET title="'.$this->escape($title).'"'
                .', signature="'.$this->escape($signature).'", signature_html="'.$this->escape($signature_html).'"'
                .' WHERE id='.intval($id));
    }

    /**
     * Delete a signature
     *
     * @param int $uid
     * @param int $id
     * @return bool
     */
    public function delete_signature($uid = 0, $id = 0)
    {
        return ($this->query('DELETE FROM '.$this->Tbl['signatures'].' WHERE id='.intval($id))
                && $this->query('ALTER TABLE '.$this->Tbl['signatures'].' ORDER BY id ASC'));
    }

    /**
     * Return list of signatures defined for a specific user id
     *
     * @param int $uid
     * @return array
     */
    public function get_signature_list($uid)
    {
        $return = array();
        $qid = $this->query('SELECT id, title, signature, signature_html FROM '.$this->Tbl['signatures']);
        while ($sig = $this->fetchassoc($qid)) {
            $return[$sig['id']] = array('title' => $sig['title'], 'signature' => $sig['signature'], 'signature_html' => $sig['signature_html']);
        }
        return $return;
    }

    /**
     * Get a specific signature
     *
     * @param int $uid
     * @param int $id
     * @return array
     */
    public function get_signature($uid, $id)
    {
        $qid = $this->query('SELECT title, signature, signature_html FROM '.$this->Tbl['signatures'].' WHERE id='.intval($id));
        list ($title, $signature, $signature_html) = $this->fetchrow($qid);
        return array('title' => $title, 'signature' => $signature, 'signature_html' => $signature_html);
    }

    // Switch activity status of a user
    // Input:  onoff_user(string username, integer status) status[0|1]
    // Return: TRUE on success, FALSE otherwise
    public function onoff_user($username, $active)
    {
        return $this->query('UPDATE '.$this->Tbl['user'].' SET active="'.$this->escape($active).'"');
    }

    // Check, if a given username (already) exists in the database
    // Input  : checkfor_username(string username)
    // Returns: Account ID if exists, FALSE otherwise
    public function checkfor_accname($un, $accname = '')
    {
        list ($exists) = $this->fetchrow($this->query('SELECT accid FROM '.$this->Tbl['profiles'].' WHERE accname="'.$this->escape($accname).'"'));
        return ($exists) ? $exists : false;
    }

    /**
     * Get index for all users
     * If a search pattern is given, only usernames containing it will be returned;
     * the pattern may contain '*' or '%' as wildcards
     * If the num (number of users) and optionally the start values are given, only the
     * search results within this range are returned
     *
     *[@param string $pattern]
     *[@param string $criteria]
     *[@param int $num]
     *[@param int $start]
     * @return array $return data on success, FALSE otherwise
     */
    public function get_usridx($pattern = '', $criteria = '', $num = 0, $start = 0)
    {
        $return = array();
        $q_l = 'SELECT 1,username FROM '.$this->Tbl['user'].' WHERE 1';
        if (is_array($pattern)) {
            foreach ($pattern as $k => $v) $pattern[$k] = (is_numeric($v)) ? $v : '"'.$this->escape($v).'"';
            $q_l .= ' AND '.$this->escape($criteria).' IN ('.implode(',', $pattern).')';
        } elseif (strlen($pattern) > 0) {
            $q_l .= ' AND username LIKE "'.str_replace('*', '%', $this->escape($pattern)).'"';
        }
        switch($criteria) {
            case 'inactive': $q_l .= ' AND active="0"';  break;
            case 'active': $q_l .= ' AND active="1"';  break;
            case 'locked': $q_l .= ' AND fail_count>='.intval($GLOBALS['_PM_']['auth']['countonfail']);  break;
        }
        $q_r = ($num != 0 ) ? ' LIMIT '.($start).','.($num) : '';
        $qid = $this->query($q_l.' ORDER BY username'.$q_r);
        while (list($uid, $username) = $this->fetchrow($qid)) $return[$uid] = $username;
        return $return;
    }

    // Get numbers of users, acitve users, inactive users, locked users
    // Input  : get_usroverview(integer $failcount)
    //          where $failcount is the number of failed logins to be considered as 'locked'
    // Returns: $return              array data on Succes, empty array on failure
    //          $return['all']       All users
    //          $return['active']    active
    //          $return['inactive']  inactive
    //          $return['locked']    locked
    public function get_usroverview($failcount)
    {
        $qid = $this->query('SELECT count(*), active FROM '.$this->Tbl['user'].' GROUP by active');
        while(list($number, $active) = $this->fetchrow($qid)) {
            $num[$active] = $number;
        }
        list ($locked) = $this->fetchrow($this->query('SELECT count(*) FROM '.$this->Tbl['user'].' where fail_count >= '.intval($failcount)));
        $return = array
                ('inactive' => isset($num['0']) ? $num['0'] : 0
                ,'active' => isset($num['1']) ? $num['1'] : 0
                ,'locked' => isset($locked) ? $locked : 0
                );
        $return['all'] = $return['active'] + $return['inactive'] + $return['locked'];
        return $return;
    }

    // Get user's personal setup from the DB
    // Input  : get_usr_choices(integer user id)
    // Returns: $return    string data on success, FALSE otherwise
    public function get_usr_choices($uid)
    {
        if ($choices = $this->query('SELECT choices FROM '.$this->Tbl['user'])) {
            list ($choices) = $this->fetchrow($choices);
            if (strstr($choices, ';;')) {
                $return = array();
                foreach (explode(LF, $choices) as $l) {
                    if (strlen(trim($l)) == 0) continue;
                    if ($l{0} == '#') continue;
                    $parts = explode(';;', trim($l));
                    if (!isset($parts[1])) $parts[1] = false;
                    $return['core'][$parts[0]] = $parts[1];
                }
                return $return;
            } else {
                $choices = @unserialize($choices);
                return $choices;
            }
        }
        return false;
    }

    // Input  : set_usr_choices(integer user id, string choices)
    // Returns: $return    TRUE on success, FALSE otherwise
    public function set_usr_choices($uid, $choices)
    {
        $choices = serialize($choices);
        $query = 'UPDATE '.$this->Tbl['user'].' SET choices="'.$this->escape($choices).'"';
        return $this->query($query);
    }

    // Activate user (Register now)
    public function activate($uid = '', $un = '', $pw = '')
    {
        $uid = intval($uid);
        list ($return) = $this->fetchrow($this->query('SELECT 1 FROM '.$this->Tbl['user'].' WHERE username="'.$this->escape($un).'" AND password=md5("'.$this->escape($pw).'") LIMIT 1'));
        if (1 == $return) $this->query('UPDATE '.$this->Tbl['user'].' SET active="1"');
        return $return;
    }

    // Tell, how many users there are in the database
    public function get_usercount()
    {
        list ($return) = $this->fetchrow($this->query('SELECT count(*) FROM '.$this->Tbl['user']));
        return $return;
    }

    // Encrypt a string
    // Input:   confuse(string $data, string $key);
    // Returns: encrypted string
    public function confuse($data = '', $key = '')
    {
        $encoded = ''; $DataLen = strlen($data);
        if (strlen($key) < $DataLen) $key = str_repeat($key, ceil($DataLen/strlen($key)));
        for ($i = 0; $i < $DataLen; ++$i) {
            $encoded .= chr((ord($data{$i}) + ord($key{$i})) % 256);
        }
        return base64_encode($encoded);
    }

    // Decrypt a string
    // Input:   deconfuse(string $data, string $key);
    // Returns: decrypted String
    public function deconfuse($data = '', $key = '')
    {
        $data = base64_decode($data);
        $decoded = '';  $DataLen = strlen($data);
        if (strlen($key) < $DataLen) $key = str_repeat($key, ceil($DataLen/strlen($key)));
        for ($i = 0; $i < $DataLen; ++$i) {
            $decoded .= chr((256 + ord($data{$i}) - ord($key{$i})) % 256);
        }
        return $decoded;
    }

    // Get amount of sent SMS for a certain user in a given month
    public function get_user_accounting($type, $month = '197001', $uid = false)
    {
        $query = 'SELECT SUM(setting) FROM '.$this->Tbl['user_accounting']
                .' WHERE `what`="'.$this->escape($type).'" AND `when`="'.intval($month).'"';
        list ($sum) = $this->fetchrow($this->query($query));
        return $sum;
    }

    public function set_user_accounting($type, $month = '197001', $uid, $amount)
    {
        $type = $this->escape($type);
        $amount = intval($amount);
        $month = intval($month);
        $uid = intval($uid);
        $qid = $this->query('SELECT 1 FROM '.$this->Tbl['user_accounting'].' WHERE `what`="'.$type.'" AND `when`='.$month.' LIMIT 1');
        list ($exists) = $this->fetchrow($qid);
        if ($exists) {
            $query = 'UPDATE '.$this->Tbl['user_accounting'].' SET `setting`=`setting`+'.$amount.' WHERE `what`="'.$type.'" AND `when`='.$month.' AND `uid`='.$uid;
        } else {
            $query = 'INSERT '.$this->Tbl['user_accounting'].' (`what`,`when`,`uid`,`setting`) VALUES ("'.$type.'",'.$month.','.$uid.','.$amount.')';
        }
        $this->query($query);
        return true;
    }

    // Log a sent SMS to MySQL
    public function log_sms_sent($pass)
    {
        if (!isset($pass['when'])) $pass['when'] = time();
        if (!isset($pass['uid']))  $pass['uid'] = 0;
        if (!isset($pass['type'])) $pass['type'] = 0;
        if (!isset($pass['text'])) $pass['text'] = '';
        if (!isset($pass['receiver']) || !isset($pass['size'])) return;

        $query = 'INSERT '.$this->Tbl['user_smslogging'].' (uid, moment, target_number, size, type, content) VALUES (1'
                 .',"'.date('Y-m-d H:i:s', $pass['when']).'","'.$this->escape($pass['receiver']).'",'
                 .intval($pass['size']).','.$this->escape($pass['type']).',"'.$this->escape($pass['text']).'")';
        $this->query($query);
    }

    public function set_fax_sent($month = '197001', $uid = false, $amount = 1)
    {
        if (!$uid) return false;
        list ($exists) = $this->fetchrow($this->query('SELECT 1 FROM '.$this->Tbl['user_accounting']
                        .' WHERE `what`="fax" AND `when`="'.intval($month).'" LIMIT 1'));
        $query = ($exists)
                ? 'UPDATE '.$this->Tbl['user_accounting'].' SET setting=setting+'.intval($amount)
                        .' WHERE `what`="fax" AND `when`="'.intval($month).'"'
                : 'INSERT '.$this->Tbl['user_accounting'].' (`what`,`when`,uid,setting) '
                        .'VALUES ("fax", "'.intval($month).'", '.intval($uid).','.intval($amount).')'
                ;
        $this->query($query);
        return true;
    }

     // Set current deposit of the system
    public function get_sms_global_deposit()
    {
        $query = 'SELECT setting FROM '.$this->Tbl['user_accounting'].' WHERE `what`="sms" AND uid=0';
        list ($sum) = $this->fetchrow($this->query($query));
        return $sum;
    }

     // Decrease the global deposit by one
    public function decrease_sms_global_deposit($amount = 1)
    {
        if (!$amount) $amount = 1;
        $query = 'UPDATE '.$this->Tbl['user_accounting'].' SET setting=setting-'.intval($amount).' WHERE `what`="sms" AND uid=0';
        $this->query($query);
    }

    // Set current deposit of the system
    public function set_sms_global_deposit($deposit)
    {
        list ($exists) = $this->fetchrow($this->query('SELECT 1 FROM '.$this->Tbl['user_accounting'].' WHERE `what`="sms" AND uid=0 LIMIT 1'));
        $query = ($exists)
                ? 'UPDATE '.$this->Tbl['user_accounting'].' SET setting='.intval($deposit).' WHERE `what`="sms" AND uid=0'
                : 'INSERT '.$this->Tbl['user_accounting'].' (setting, `what`, uid) VALUES ('.intval($deposit).',"sms",0)'
                ;
        $this->query($query);
    }

     // Get sent SMS statistics for a specific month
    // Input:  get_sms_stats(int month); Format: YYYYMM
    // Return: array
    //         (int sum of sent SMS
    //         ,int max sent SMS by single user
    //         )
    public function get_sms_stats($month = '197001', $uid = false)
    {
        $add = (!$uid) ? 'uid!=0' : 'uid=1';
        $query = 'SELECT sum(setting), min(setting), max(setting), count(*) FROM '.$this->Tbl['user_accounting']
                .' WHERE `what`="sms" AND `when`="'.intval($month).'" AND '.$add;
        list ($sum, $min, $max, $cnt) = $this->fetchrow($this->query($query));
        // If we have no events, we don't need to try to get those:
        // Same applies on fetching stats for a specific user
        if (isset($sum) && $sum && !$uid) {
            $query = 'SELECT u.uid, u.username FROM '.$this->Tbl['user_accounting'].' a,'.$this->Tbl['user']
                    .' u WHERE a.`what`="sms" AND a.`when`="'.intval($month).'" AND a.'.$add
                    .' AND a.uid=u.uid ORDER by a.setting ASC LIMIT 1';
            list ($min_uid, $min_usr) = $this->fetchrow($this->query($query));
            $query = 'SELECT u.uid, u.username FROM '.$this->Tbl['user_accounting'].' a,'.$this->Tbl['user']
                    .' u WHERE a.`what`="sms" AND a.`when`="'.intval($month).'" AND a.'.$add
                    .' AND a.uid=u.uid ORDER by a.setting DESC LIMIT 1';
            list ($max_uid, $max_usr) = $this->fetchrow($this->query($query));
        }
        return array
                (isset($sum) ? $sum : 0
                ,isset($min) ? $min : 0
                ,isset($max) ? $max : 0
                ,isset($cnt) ? $cnt : 0
                ,isset($min_uid) ? array
                                  ('min_usr' => $min_usr
                                  ,'min_uid' => $min_uid
                                  ,'max_usr' => $max_usr
                                  ,'max_uid' => $max_uid
                                  )
                                : false
                );
    }

    /**
     * Tries to find out the specific quota setting effective for the given user ID.
     * If no setting was found, false will be returned. The same applies, if the setting
     * has been explicitly set to false. False values mean: no limit set.
     *
     * @param int $uid Either a value > 0 for a specific user or == 0 for the global definition
     * @param string $handler The handler this quota setting applies to
     * @param string $what The quota setting to query
     * @return mixed Either FALSE for no setting / unlimited or the specific value defined for the setting
     * @since 3.9.1
     */
    public function quota_get($uid, $handler, $what)
    {
        if ($uid !== 0) {
            $res = $this->query('SELECT `setting` FROM '.$this->Tbl['user_quota'].' WHERE `uid`=1'
                    .' AND `handler`="'.$this->escape($handler).'" AND `what`="'
                    .$this->escape($what).'"');
            // At least one result for that query
            if ($this->numrows($res)) {
                list ($setting) = $this->fetchrow($res);
                return $setting;
            }
        }
        $res = $this->query('SELECT `setting` FROM '.$this->Tbl['user_quota'].' WHERE `uid`=0'
                .' AND `handler`="'.$this->escape($handler).'" AND `what`="'
                .$this->escape($what).'"');
        // At least one result for that query
        if ($this->numrows($res)) {
            list ($setting) = $this->fetchrow($res);
            return $setting;
        }
        return false;
    }

    /**
     * Sets a quota value effective for a specific user ID and handler name. Passing a
     * user ID of 0 you define the global setting, which will take effect, whenever there's
     * no specific value defined for a specific user.
     *
     * @param int $uid Either a value > 0 for a specific user or == 0 for the global definition
     * @param string $handler The handler this quota setting applies to
     * @param string $what The quota setting
     * @param string $setting Its value
     * @return bool
     * @since 3.9.1
     */
    public function quota_set($uid, $handler, $what, $setting)
    {
        $res = $this->query('SELECT 1 FROM '.$this->Tbl['user_quota'].' WHERE `uid`=1'
                .' AND `handler`="'.$this->escape($handler).'" AND `what`="'
                .$this->escape($what).'" LIMIT 1');
        // Determine, whether to update the column or insert it
        $query = ($this->numrows($res))
                ? 'UPDATE '.$this->Tbl['user_quota'].' SET `setting`="'.$this->escape($setting)
                        .'" WHERE `uid`=1 AND `handler`="'.$this->escape($handler)
                        .'" AND `what`="'.$this->escape($what).'"'
                : 'INSERT '.$this->Tbl['user_quota'].' SET `uid`=1'
                        .', `handler`="'.$this->escape($handler).'", `what`="'
                        .$this->escape($what).'", `setting`="'.$this->escape($setting).'"'
                ;
        return $this->query($query);
    }

    /**
     * Explicitly removes a quota definition.
     *
     * @param int $uid Either a value > 0 for a specific user or == 0 for the global definition
     * @param string $handler The handler this quota setting applies to
     * @param string $what The quota setting
     * @return bool
     * @since 3.9.1
     */
    public function quota_drop($uid, $handler, $what)
    {
        return $this->query('DELETE FROM '.$this->Tbl['user_quota'].' WHERE `uid`=1 AND `handler`="'.$this->escape($handler).'" AND `what`="'.$this->escape($what).'"');
    }

    /**
     * This is used by a handler, to add capabilities for certain mimetypes to the global
     * SendTo management table.
     * To signal, that your handler basically handles everything, just pass array('%' => 'accept').
     * To tell, you will handle all text files but HTML, pass array('text/%' => 'accept', 'text/html' => 'ignore').
     *
     * @param array $mimetypes Pass all MIME types here. Keys: MIME type, Values 'accept' or 'ignore'.
     * @param string $handler Handler's internal name, e.g. calendar or files
     * @param bool $on_context Whether to show a context menu entry, Default: true
     * @param bool $on_fetch Whether to be included in mail fetching filters; Default: false
     * @return bool
     * @since 3.9.7
     */
    public function sendto_add_mimehandler($mimetypes, $handler, $on_context = 1, $on_fetch = 0)
    {
        if (!$handler) return false;
        $q_l = 'INSERT INTO '.$this->Tbl['sendto_handler'].' (`behaviour`, `mimetype`, `handler`, `on_context`, `on_fetch`) ';
        $q_r = array();
        foreach ($mimetypes as $type => $behave) {
            $q_r[] = 'VALUES ("'.($behave == 'accept' ? 'accept' : 'ignore').'", "'.$this->escape($type).'"'
                    .', "'.$this->escape($handler).'", "'.($on_context).'", "'.($on_fetch).'")';
        }
        return $this->query($q_l.implode(',', $q_r));
    }

    /**
     * Removes all SendTo entries associated with a certain handler
     *
     * @param string $handler
     * @return bool
     * @since 3.9.7
     */
    public function sendto_remove_mimehandler($handler)
    {
        return $this->query('DELETE FROM '.$this->Tbl['sendto_handler'].' WHERE `handler`="'.$this->escape($handler).'"');
    }

    /**
     * Returns a list of known handlers, which can handle a given MIME type
     *
     * @param string $mimetype
     * @return array  A list of handlers, which can handle the given MIME type
     * @since 3.9.7
     */
    public function sendto_get_mimehandlers($type)
    {
        $type = $this->escape($type);
        $return = array();
        $res = $this->query('SELECT `handler` FROM '.$this->Tbl['sendto_handler']
                .' WHERE (`behaviour`="accept" AND "'.$type.'" LIKE `mimetype`) AND NOT (`behaviour`="ignore" AND "'.$type.'" LIKE `mimetype`) GROUP BY `handler`');
        while (list($handler) = $this->fetchrow($res)) {
            $return[] = $handler;
        }
        return $return;
    }

    /**
     * This returns a list of all supported (or ignored) MIME types for a specific handler.
     * Useful for checks on updates.
     *
     * @param string $handler
     * @return array Keys: MIME types, values: 'accept' or 'ignore'
     * @since 3.9.7
     */
    public function sendto_handler_supports($handler)
    {
        $res = $this->query('SELECT `behaviour`, `mimetype` FROM '.$this->Tbl['sendto_handler'].' WHERE `handler`="'.$this->escape($handler).'"');
        $return = array();
        while (list($behave, $type) = $this->fetchrow($res)) {
            $return[$type] = $behave;
        }
        return $return;
    }

    /**
     * Management method for checking, whether the SendTo DB needs updates. Simply
     * returns all entries of the DB
     *
     * @return array
     * @since 3.9.8
     */
    public function sendto_listall()
    {
        $res = $this->query('SELECT `handler`, `behaviour`, `mimetype` FROM '.$this->Tbl['sendto_handler'].' ORDER BY `handler`');
        $return = array();
        while ($res && $line = $this->fetchassoc($res)) {
            $return[$line['handler']][$line['mimetype']] = $line['behaviour'];
        }
        return $return;
    }

    /**
     * A speical method pair to switch between confused and clear text passwords for SMTP / POP3 / IMAP
     */
    public function confused_cleartext()
    {
        $r = array();
        $qid = $this->query('SELECT id, poppass, popserver, popport, popuser, smtppass, smtpserver, smtpport, smtpuser FROM '.$this->Tbl['profiles']);
        while ($line = $this->fetchassoc($qid)) {
            $r[] = array
                    ('i' => $line['id']
                    ,'p' => $this->deconfuse($line['poppass'], $line['popserver'].$line['popport'].$line['popuser'])
                    ,'s' => $this->deconfuse($line['smtppass'], $line['smtpserver'].$line['smtpport'].$line['smtpuser'])
                    );
        }
        foreach ($r as $v) {
            $this->query('UPDATE '.$this->Tbl['profiles'].' SET `poppass`="'.$this->escape($v['p']).'", `smtppass`="'.$this->escape($v['s']).'" WHERE id='.$v['i']);
        }
    }
    public function cleartext_confused()
    {
        $r = array();
        $qid = $this->query('SELECT id, poppass, popserver, popport, popuser, smtppass, smtpserver, smtpport, smtpuser FROM '.$this->Tbl['profiles']);
        while ($line = $this->fetchassoc($qid)) {
            $r[] = array
                    ('i' => $line['id']
                    ,'p' => $this->confuse($line['poppass'], $line['popserver'].$line['popport'].$line['popuser'])
                    ,'s' => $this->confuse($line['smtppass'], $line['smtpserver'].$line['smtpport'].$line['smtpuser'])
                    );
        }
        foreach ($r as $v) {
            $this->query('UPDATE '.$this->Tbl['profiles'].' SET `poppass`="'.$this->escape($v['p']).'", `smtppass`="'.$this->escape($v['s']).'" WHERE id='.$v['i']);
        }
    }

    /**
     * Retrieves the list of favourite folders for a given user
     *
     * @param int $uid
     * @return array
     */
    public function favfold_getlist($uid)
    {
        $return = array();
        $qid = $this->query('SELECT `handler`,`fid` FROM '.$this->Tbl['user_favfolders'].' ORDER BY `order` ASC, `ffid` ASC');
        while ($res = $this->fetchassoc($qid)) {
            if ($res['fid'] == 0) $res['fid'] = 'root';
            $return[] = $res;
        }
        return $return;
    }

    /**
     * Adds a favourite folder for a given user
     *
     * @param int $uid ID of the user
     * @param string $handler Name of the handler the folder belongs to
     * @param int $fid ID of the folder within the handler
     * @return boolean
     */
    public function favfold_add($uid, $handler, $fid)
    {
        $order = 0;
        $qid = $this->query('SELECT MAX(`order`) maxorder FROM '.$this->Tbl['user_favfolders'].' WHERE `uid`='.intval($uid));
        if ($qid) {
            $line = $this->fetchassoc($qid);
            if (isset($line['maxorder'])) $order = $line['maxorder'];
        }
        return $this->query('REPLACE INTO '.$this->Tbl['user_favfolders'].' SET `uid`=1'
                .',`handler`="'.$this->escape($handler).'",`fid`='.intval($fid).',`order`='.intval($order+1));
    }

    /**
     * Removes a favourite folder for a given user
     *
     * @param int $uid ID of the user
     * @param string $handler Name of the handler the folder belongs to
     * @param int $fid ID of the folder within the handler
     * @return boolean
     */
    public function favfold_drop($uid, $handler, $fid)
    {
        return $this->query('DELETE FROM '.$this->Tbl['user_favfolders'].' WHERE `handler`="'.$this->escape($handler).'" AND `fid`='.intval($fid));
    }


    /**
     * Takes an array as argument, where the order is contained
     *
     * @param int $uid ID of the user
     * @param array $input Key: Account ID, Value: position in list
     * @return bool
     */
    public function favfold_reorder($uid, $input)
    {
        $uid = intval($uid);
        foreach ($input as $k => $v) {
            list ($handler, $fid) = explode('_', $k);
            if ($fid == 'root') $fid = 0; // Reverse mapping
            $this->query('UPDATE '.$this->Tbl['user_favfolders'].' SET `order`='.($v).' WHERE `handler`="'.$this->escape($handler).'" AND `fid`='.intval($fid));
        }
    }

    /**
     * Set setting(s) for a folder
     *
     * @param string $hdl  Name of the handler, e.g. email
     * @param int $fid  ID of the folder
     * @param int $uid  ID of the user
     * @param string|array $key  Name of the key or array with key => val pairs
     *[@param mixed $val  A scalar value, an array, object or null if $key is an array]
     * @return unknown
     */
    public function foldersetting_set($hdl, $fid, $uid, $key, $val = null)
    {
        if (!is_null($val)) {
            $key = array($key => $val);
        }
        $sql = 'REPLACE INTO '.$this->Tbl['user_foldersettings'].' (`handler`,`fid`,`key`,`val`) VALUES ';
        $i = 0;
        foreach ($key as $k => $v) {
            if ($i) $sql .= ',';
            if (is_array($v) || is_object($v) || is_bool($v)) {
                $v = serialize($v);
            }
            $sql .= '("'.$this->escape($hdl).'", '.intval($fid).', "'.$this->escape($k).'", "'.$this->escape($v).'")';
            $i++;
        }
        return $this->query($sql);
    }

    /**
     * Get setting(s) for a folder
     *
     * @param string $hdl  Name of the handler, e.g. email
     * @param int $fid  ID of the folder
     * @param int $uid  ID of the user
     *[@param string $key  f given, only the value for that key is returned]
     * @return mixed
     */
    public function foldersetting_get($hdl, $fid, $uid, $key = null)
    {
        $return = array();
        $qh = $this->query('SELECT `key`, `val` FROM '.$this->Tbl['user_foldersettings'].' WHERE `handler`="'.$this->escape($hdl).'"'
                .' AND `fid`='.intval($fid).(!is_null($key) ? ' AND `key`="'.$this->escape($key).'"' : '')
                );
        if ($this->numrows($qh)) {
            while ($line = $this->fetchassoc($qh)) {
                $line['unser'] = @unserialize($line['val']);
                $return[$line['key']] = ($line['unser'] !== unserialize(false)) ? $line['unser'] : $line['val'];
            }
        }
        if (!is_null($key)) {
            return (isset($return[$key]) ? $return[$key] : null);
        }
        return $return;
    }

    /**
     * Retrieve a list of folders, where a certain setting is applied. This might
     * be only a key (and all possible values) or a certain value for a certain
     * key.
     *
     * @param string $hdl  Name of the handler, e.g. email
     *[@param int $uid  ID of the user]
     * @param string $key  Name of the key
     *[@param string $val  The desired value; serialize it for non scalars]
     */
    public function foldersettings_find($hdl, $uid = null, $key, $val = null)
    {
        $return = array();
        $qh = $this->query('SELECT `fid` FROM '.$this->Tbl['user_foldersettings']
                .' WHERE `handler`="'.$this->escape($hdl).'"'
                .(!is_null($uid) ? ' AND `uid`='.intval($uid) : '')
                .' AND `key`="'.$this->escape($key).'"'
                .(!is_null($val) ? ' AND `val`="'.$this->escape($val).'"' : '')
                );
        if ($this->numrows($qh)) {
            while ($line = $this->fetchassoc($qh)) {
                $return[] = $line['fid'];
            }
        }
        return $return;
    }

    /**
     * Drop more or less specific settings for folders
     *
     * @param string $hdl  Name of the handler, e.g. email
     *[@param int $fid  ID of the folder to delete the settings for]
     *[@param int $uid  ID of the user to delete the settings for]
     *[@param string $key  Name of the key, depends on handler]
     * @return bool
     */
    public function foldersetting_del($hdl, $fid = null, $uid = null, $key = null)
    {
        return $this->query('DELETE FROM '.$this->Tbl['user_foldersettings'].' WHERE `handler`="'.$this->escape($hdl).'"'
                .(!is_null($fid) ? ' AND `fid`='.intval($fid) : '')
                .(!is_null($key) ? ' AND `key`="'.$this->escape($key).'"' : '')
                );
    }

    //
    // Core facility for storing thumbnails in the DB
    //

    /**
     * Adds a thumbnail to the database
     *
     * @param string $handler  The handler this thumb belongs to
     * @param int $item The item ID within the handler the thumb belongs to
     *[@param string $type An optional type of thumb, which can be used to store various sizes of thumbs per item]
     * @param string $mime  MIME type of the thumb, usually one of the image/* subtypes
     *[@param int $l The strlen() of the thumbnail stream in bytes]
     *[@param int $w The effective width of the thumbnail (NOT the boundary size!)]
     *[@param int $h The effective height of the thumbnail (NOT the boundary size!)
     * @param string $stream The raw binary thumbnail image data
     * @return unknown
     */
    public function thumb_add($handler, $item, $type = '', $mime, $l = 0, $w = 0, $h = 0, $stream)
    {
        $this->query('INSERT INTO '.$this->Tbl['core_thumbs'].' SET `uuid`="'.basics::uuid().'",`handler`="'.$this->escape($handler).'"'
                .',`item`='.intval($item).',`type`="'.$this->escape($type).'",`mime`="'.$this->escape($mime).'"'
                .',`len`='.intval($l).',`w`='.intval($w).',`h`='.intval($h).',`body`="'.addslashes($stream).'"');
        return $this->insertid();
    }

    public function thumb_drop($handler, $item, $type = null)
    {
        return $this->query('DELETE FROM '.$this->Tbl['core_thumbs'].' WHERE `handler`="'.$handler.'" AND `item`='.intval($item)
                .(!is_null($type) ? ' AND `type`="'.$this->escape($type).'"' : ''));
    }

    public function thumb_get($handler, $item, $type = null)
    {
        $qid = $this->query('SELECT `mime`, `len` size, `w` width, `h` height, `body` as `stream`,`uuid` FROM '.$this->Tbl['core_thumbs']
                .' WHERE `handler`="'.$handler.'" AND `item`='.intval($item)
                .(!is_null($type) ? ' AND `type`="'.$this->escape($type).'"' : '').' LIMIT 1');
        if (!$this->numrows($qid)) return false; // No thumb found
        return $this->fetchassoc($qid);
    }

    //
    // Handling XNAs (eXternal No Auth requests)
    //

    /**
     * Register an XNA
     *
     * @param string $handler  Name of the handler, required
     * @param string $load  Load argument, required
     *[@param string $action  Action argument, optional]
     *[@param string $uuid  UUID (if applicable), optional]
     */
    public function xna_register($handler, $load, $action = '', $uuid = null)
    {
        if (is_null($uuid)) {
            $uuid = basics::uuid();
        }
        $query = 'INSERT '.$this->Tbl['core_noauth'].' SET `uuid`="'.$this->escape($uuid).'"'
                .',`handler`="'.$this->escape($handler).'", `load`="'.$this->escape($load).'"'
                .',`action`="'.$this->escape($action).'"';
        if ($this->query($query)) {
            return $uuid;
        }
        return false;
    }

    /**
     * Unregister XNA (delete from DB)
     *
     * @param string $uuid  UUID to identify request
     * @return bool
     */
    public function xna_unregister($uuid)
    {
        return $this->query('DELETE FROM '.$this->Tbl['core_noauth'].' WHERE `uuid`="'.$this->escape($uuid).'"');
    }

    /**
     * Query details for an XNA
     *
     * @param string $uuid  UUID to identify request
     * @return array|false  Array on success, FALSE on failure
     */
    public function xna_query($uuid)
    {
        $qid = $this->query('SELECT * FROM '.$this->Tbl['core_noauth'].' WHERE `uuid`="'.$this->escape($uuid).'"');
        if (false === $qid || !$this->numrows($qid)) return false;
        return $this->fetchassoc($qid);
    }

    //
    // Methods for keeping the DB structure up to date. Used by runonce.php
    //

    /**
     * Reads the structure of the currently used Database and returns it as an array structure
     * @param  void
     * @return  array  keys: table names, values: arrays with column names as keys and column definitions as values
     * @since  3.1.5
     */
    public function get_structure()
    {
        $qid = $this->query('SHOW TABLES FROM `'.$this->DB['database'].'` LIKE "'.$this->DB['prefix'].'_%"');
        $return = array();
        while (list($table) = $this->fetchrow($qid)) {
            // I need the plain table name without the prefix and the DB name and stuff
            $tbl = preg_replace('!^'.preg_quote($this->DB['prefix'].'_', '!').'!', '', $table);
            $return[$tbl] = array();
        }
        foreach ($return as $table => $v) {
            $return[$table] = array('fields' => array(), 'index' => array());
            $qid = $this->query('SHOW COLUMNS FROM '.$this->DB['db_pref'].$table);
            while ($line = $this->fetchassoc($qid)) {
                $return[$table]['fields'][$line['Field']] = array
                        ('type' => $line['Type']
                        ,'null' => ($line['Null'] == 'NO') ? 0 : 1
                        ,'key' => $line['Key'] == 'PRI' ? 1 : 0
                        ,'default' => (is_null($line['Default'])) ? false : $line['Default']
                        ,'extra' => $line['Extra'] == 'auto_increment' ? 1 : 0
                        );
            }
            $qid = $this->query('SHOW INDEX FROM '.$this->DB['db_pref'].$table);
            while ($line = $this->fetchassoc($qid)) {
                if ($line['Key_name'] == 'PRIMARY') continue;
                if ($line['Non_unique'] == 1) {
                    $return[$table]['index'][$line['Key_name']] = $line['Column_name'];
                } else {
                    if (isset($return[$table]['unique'][$line['Key_name']])) {
                        $return[$table]['unique'][$line['Key_name']] .= ',`'.$line['Column_name'].'`';
                    } else {
                        $return[$table]['unique'][$line['Key_name']] = '`'.$line['Column_name'].'`';
                    }
                }
            }
        }
        return $return;
    }

    /**
     * This method updates the DB structure. Takes two arguments, the first is the tables to add, the second the tables
     * to update. Be aware, that dropping either tables or fields is not possible, since this could (and probably would)
     * interfer with the idea of the owner of the licence also owning the data stored in the tables. Dropping unknown
     * or no longer necessary fields would by chance destroy data the client probably still needs
     *
     * @param  array  Tables to add; keys: table names, values: 2 arrays with key == 'fields' for the field definitions (keys
     *   are the field names, values
     * @param  array  keys: table names, values: arrays with column names as keys and column definitions as values, additionally
     *         the flag 'action' tells whether to add a field (value 'add') or alter it (value 'alter')
     * @param  array  list of statements to run (for more complex update tasks like rewriting values, if necessary)
     * @return  bool  true on success, false on failure
     * @since  3.1.5
     */
    public function update_structure($add, $alter, $statement)
    {
        // Add new tables
        foreach ($add as $table => $def) {
            $query = 'CREATE TABLE '.$this->DB['db_pref'].$table.' (';
            $i = 0;
            foreach ($def['fields'] as $field => $fdef) {
                if ($i) $query .= ', ';
                $query .= '`'.$field.'` '.$fdef['type'].' '.($fdef['null'] ? 'NULL' : 'NOT NULL')
                        .$this->update_structure_defval($fdef['default'], $fdef['type'])
                        .($fdef['key'] ? ' PRIMARY KEY' : '').($fdef['extra'] ? ' auto_increment' : '');
                $i++;
            }
            foreach ($def['index'] as $field => $fdef) {
                if ($i) $query .= ', ';
                if (false === strpos($fdef, '`')) $fdef = '`'.$fdef.'`';
                $query .= ' INDEX `'.$field.'` ('.$fdef.') ';
                $i++;
            }
            foreach ($def['unique'] as $field => $fdef) {
                if ($i) $query .= ', ';
                if (false === strpos($fdef, '`')) $fdef = '`'.$fdef.'`';
                $query .= ' UNIQUE KEY `'.$field.'` ('.$fdef.') ';
                $i++;
            }
            $query .= ')';
            $this->query($query);
            $e = $this->error();
            if ($e) echo $e.LF.$query.LF.LF;
        }
        // Add new fields if necessary
        foreach ($alter as $table => $def) {
            $query = 'ALTER TABLE '.$this->DB['db_pref'].$table.' ';
            $i = 0;
            foreach ($def['fields'] as $field => $fdef) {
                if ($i) $query .= ', ';
                $query .= 'ADD `'.$field.'` '.$fdef['type'].' '.($fdef['null'] ? 'NULL' : 'NOT NULL')
                        .$this->update_structure_defval($fdef['default'], $fdef['type'])
                        .($fdef['key'] ? ' PRIMARY KEY' : '').($fdef['extra'] ? ' auto_increment' : '');
                $i++;
            }
            foreach ($def['index'] as $field => $fdef) {
                if ($i) $query .= ', ';
                if (false === strpos($fdef, '`')) $fdef = '`'.$fdef.'`';
                $query .= 'ADD INDEX `'.$field.'` ('.$fdef.') ';
                $i++;
            }
            foreach ($def['unique'] as $field => $fdef) {
                if ($i) $query .= ', ';
                if (false === strpos($fdef, '`')) $fdef = '`'.$fdef.'`';
                $query .= 'ADD UNIQUE KEY `'.$field.'` ('.$fdef.') ';
                $i++;
            }
            $this->query($query);
            $e = $this->error();
            if ($e) echo $e.LF.$query.LF.LF;
        }
        foreach ($statement as $query) {
            $query = str_replace('{prefix}', $this->DB['db_pref'], $query);
            $this->query($query);
            $e = $this->error();
            if ($e) echo $e.LF.$query.LF.LF;
        }
    }

    /**
     * Takes the setting for a default value and returns the right SQL portion for it
     *
     * @param mixed $val
     * @return string
     * @since 4.0.5
     */
    public function update_structure_defval($val, $type)
    {
        if (in_array($type, array('blob', 'mediumblob', 'longblob', 'tinyblob', 'text', 'mediumtext', 'longtext', 'tinytext'))) {
            return '';
        }
        return (false !== $val ? ($val === 'NULL' ? ' DEFAULT NULL' : (is_int($val) ? ' DEFAULT '.$val : ' DEFAULT "'.$val.'"')) : '');
    }
}

class driver_mysql {

    public $dbh;
    public function __construct($host, $user, $pass, $db = null)
    {
        $dbh = mysql_connect($host, $user, $pass);
        if (is_resource($dbh)) {
            $this->dbh = $dbh;
            if (!is_null($db)) mysql_select_db($db, $dbh);
            return true;
        }
        return false;
    }
    public function close() { return mysql_close($this->dbh); }
    public function query($query) { return mysql_query($query, $this->dbh); }
    public function fetchrow($qid) { return mysql_fetch_row($qid); }
    public function fetchassoc($qid) { return mysql_fetch_assoc($qid); }
    public function fetchobj($qid) { return mysql_fetch_object($qid); }
    public function affected() { return mysql_affected_rows($this->dbh); }
    public function numrows($qid) { return mysql_num_rows($qid); }
    public function error() { return mysql_error($this->dbh); }
    public function serverinfo() { return mysql_get_server_info(); }
    public function ping() { return mysql_ping(); }

    // Since it does not use the bulit in function it is safe against overflow
    // when using BIGINT columns for the auto_increment field.
    public function insertid()
    {
        list ($return) = $this->fetchrow($this->query('SELECT LAST_INSERT_ID()'));
        return $return;
    }

    public function escape($value) { return mysql_real_escape_string($value, $this->dbh); }
}

if (!function_exists('mysqli_connect')) return;

class driver_mysqli extends mysqli {

    public function __construct($host, $user, $pass, $db = null)
    {
        $port = $socket = null;
        if (false !== (strpos($host, ':'))) {
            list ($host, $socket) = explode(':', $host, 2);
            if (is_numeric($socket)) {
                $port = $socket;
                $socket = null;
            }
        }
        return parent::__construct($host, $user, $pass, $db, $port, $socket);
    }
    public function fetchrow($qid) { return $qid->fetch_row(); }
    public function fetchassoc($qid) { return $qid->fetch_assoc(); }
    public function fetchobj($qid) { return $qid->fetch_object(); }
    public function affected() { return $this->affected_rows; }
    public function numrows($qid) { return $qid->num_rows; }
    public function error() { return $this->error; }
    public function serverinfo() { return $this->server_info; }
    public function insertid() { return $this->insert_id; }
    public function escape($value) { return $this->real_escape_string($value); }
}
?>
Return current item: phlyMail Lite