<?php
/* Copyright (c) 2009 Arnaud Renevier, Inc, published under the modified BSD
   license. */

require ("./inc/db/anydb.php");

class mysqlConnection implements anydbConnection {
    var $link = null;
    var $dbprefix = null;

    public function connect ($host, $user, $pwd, $dbname, $dbprefix) {
        if (!function_exists ("mysql_connect")) {
            throw new Exception (anydbConnection::err_driver_unavailable);
        }
        if ($this->link) { // connection has already been opened
            return;
        }
        $this->link = @mysql_connect ($host,$user,$pwd,true);
        if (!$this->link) {
            throw new Exception (anydbConnection::err_connection);
        }
        if (!mysql_select_db ($dbname, $this->link)) {
            throw new Exception (anydbConnection::err_unknown_database);
        }
        $this->dbprefix = $dbprefix;
    }

    public function users_table_exists () {
        return $this->_tblexists ("users");
    }
    public function create_users_table ($error_if_exists) {
        $query = sprintf ("CREATE TABLE " .
                           ($error_if_exists ? " " : "IF NOT EXISTS ") .
                           "%susers (
                            name VARCHAR(255) NOT NULL, pwd CHAR(32),
                            PRIMARY KEY (name));", $this->dbprefix);
        $this->_execute_query ($query);
    }

    public function items_table_exists () {
        return $this->_tblexists ("items");
    }
    public function create_items_table ($error_if_exists) {
        $query = sprintf ("CREATE TABLE " .
                           ($error_if_exists ? " " : "IF NOT EXISTS ") .
                            "%sitems (
                                imgurl VARCHAR(255),
                                title VARCHAR(127),
                                description TEXT,
                                location POINT,
                                date DATETIME,
                                PRIMARY KEY (imgurl)
                            );", $this->dbprefix);
        $this->_execute_query ($query);
    }

    public function setpwd ($user_name, $pwd) {
        $usrname_escaped = mysql_real_escape_string ($user_name);
        $query = sprintf ("SELECT COUNT(*) FROM %susers WHERE name LIKE '%s';",
                        $this->dbprefix, $usrname_escaped);
        $res = mysql_fetch_array ($this->_execute_query ($query), MYSQL_NUM);
        if ($res [0] == 1) {
            $query = sprintf ("UPDATE %susers SET pwd='%s' WHERE name like '%s';", 
                               $this->dbprefix, md5 ($pwd), $usrname_escaped);
        } else {
            $query = sprintf ("INSERT INTO %susers VALUES ('%s', '%s');", 
                               $this->dbprefix, $usrname_escaped, md5 ($pwd));
        }
        $this->_execute_query ($query);
    }

    public function checkpwdmd5 ($user_name, $pwd_md5) {
        $query = sprintf ("SELECT COUNT(*) FROM %susers WHERE name LIKE '%s'
                           AND pwd LIKE '%s';",
                           $this->dbprefix, 
                           mysql_real_escape_string ($user_name),
                           mysql_real_escape_string ($pwd_md5));
        $res = mysql_fetch_array ($this->_execute_query ($query), MYSQL_NUM);
        if ($res [0] >= 1) {
            return true;
        } else {
            return false;
        }
    }

    public function save_feature ($feature) {
        $query = sprintf ("SELECT imgurl FROM %sitems WHERE imgurl = '%s'", 
                           $this->dbprefix, 
                           mysql_real_escape_string ($feature->imgurl)); 
        $this->_execute_query ($query);
        if (mysql_affected_rows ($this->link) == 0) {
            if ($feature->imgurl_exists ()) {
              $query = sprintf ("INSERT INTO %sitems
                                   (imgurl, title, description, location, date)
                                   VALUES ('%s', '%s', '%s', 
                                   GeomFromText('POINT(%s %s)'), NOW())", 
                              $this->dbprefix,
                              mysql_real_escape_string ($feature->imgurl),
                              mysql_real_escape_string ($feature->title),
                              mysql_real_escape_string ($feature->description),
                              $feature->lon,
                              $feature->lat
                    );
                $this->_execute_query ($query);
                return true;
            } else {
                return false;
            }
        } else {
            $query = sprintf ("UPDATE %sitems SET title='%s', description='%s',
                               location=GeomFromText('POINT(%s %s)')
                               where imgurl = '%s'",
                             $this->dbprefix,
                             mysql_real_escape_string ($feature->title),
                             mysql_real_escape_string ($feature->description),
                             $feature->lon,
                             $feature->lat,
                             mysql_real_escape_string ($feature->imgurl)
                );
            $this->_execute_query ($query);
            return true;
        }
    }

    public function delete_feature ($feature) {
        $query = sprintf ("DELETE from %sitems WHERE imgurl like '%s'",
                        $this->dbprefix,
                        mysql_real_escape_string ($feature->imgurl));
        $this->_execute_query ($query);
        return true;
    }

    public function getfeature ($imgurl) {
        $query = sprintf ("SELECT imgurl, title, description, AsText(location)
                           AS location FROM %sitems WHERE imgurl like '%s';", 
                        $this->dbprefix, mysql_real_escape_string ($imgurl));
        $row = mysql_fetch_assoc ($this->_execute_query ($query));
        if (!isset ($row)) {
            return null;
        }
        return $this->_feature_frow_row ($row);
    }

    public function listfeatures () {
        $query = sprintf ("SELECT imgurl, title, description, AsText(location)
                            AS location FROM %sitems;",
                          $this->dbprefix);

        $features = array ();
        $res = $this->_execute_query ($query);
        while ($row = mysql_fetch_assoc ($res)) {
            $feature = $this->_feature_frow_row ($row);
            if (isset ($feature)) {
                $features[] = $feature;
            }
        }
        return $features;
    }

    public function imgurl_exists ($imgurl) {
        $query = sprintf ("SELECT COUNT(*) FROM %sitems WHERE imgurl LIKE '%s';",
                           $this->dbprefix, mysql_real_escape_string ($imgurl));
        $res = mysql_fetch_array  ($this->_execute_query ($query), MYSQL_NUM);
        return ($res [0] >= 1) ? true : false;
    }

    public function mbr () {
        $maxlon = -180;
        $minlon = 180;
        $maxlat = -88;
        $minlat = 88;

        if (!$this->link) {
            return array ($minlon, $minlat, $maxlon, $maxlat);
        }
        try {
            $features = $this->listfeatures ();
        } catch (Exception $e) {
            return array ($minlon, $minlat, $maxlon, $maxlat);
        }

        if (count ($features) == 0) {
            return array ($minlon, $minlat, $maxlon, $maxlat);
        } else if (count ($features) == 1) { 
            // in case there's only one feature, we show an area of at least 
            // 4 x 4 degrees
            $feature = $features [0];

            $minlon = max ($feature->lon - 2, -180);
            $maxlon = min ($feature->lon + 2, 180);
            $minlat = max ($feature->lat - 2, -90);
            $maxlat = min ($feature->lat + 2, 90);

            return array ($minlon, $minlat, $maxlon, $maxlat);
        } else {
            foreach ($features as $feature) {
                $minlon = min ($feature->lon, $minlon);
                $minlat = min ($feature->lat, $minlat);
                $maxlon = max ($feature->lon, $maxlon);
                $maxlat = max ($feature->lat, $maxlat);
            }

            return array ($minlon, $minlat, $maxlon, $maxlat);
        }
    }

    public function getdbname () {
        return "Mysql";
    }

    private function _tblexists ($tblname) {
        $query = sprintf ("SHOW TABLES LIKE '%s%s';",
                            $this->dbprefix, $tblname);
        return mysql_num_rows ($this->_execute_query ($query)) == 1;
    }

    private function _feature_frow_row ($row) {
        // XXX: should I remove invalid features from database ?
        if (!preg_match ('/^POINT\(([0-9\-\.]+)\s+([0-9\-\.]+)\)$/',
                        $row ["location"], $matches)) {
            return null;
        }
        $lon = $matches [1];
        $lat = $matches [2];
        try {
            $feature = new feature ($row ["imgurl"], $row ["title"], $row
                                    ["description"], $lon, $lat);
        } catch (Exception $e) {
            return null;
        }
        return $feature;
    }

    private function _execute_query ($query) {
        if (!function_exists ("mysql_query")) {
            throw new Exception (anydbConnection::err_driver_unavailable);
        }
        if (!$this->link) {
            throw new Exception (anydbConnection::err_query);
        }
        $res = mysql_query ($query, $this->link);
        if ($res == false) {
            throw new Exception (anydbConnection::err_query);
        }
        return $res;
    }
}

$connection = new mysqlConnection();
?>