X-Git-Url: https://dev.renevier.net/gitweb.cgi?p=syp.git;a=blobdiff_plain;f=inc%2Fdb%2Fmysql.php;h=8e6253fc6e0f5fbae1cfb40ab29107757a7d9f9d;hp=1e20c34e526fde9ff2a18134c35bfec519bb75a0;hb=3c74920cb66b4e6c47c7e8a0eaeed40ffb7e8544;hpb=7282fabcfef34ef95b8c6bd414f34d77037451e1

diff --git a/inc/db/mysql.php b/inc/db/mysql.php
index 1e20c34..8e6253f 100644
--- a/inc/db/mysql.php
+++ b/inc/db/mysql.php
@@ -2,7 +2,7 @@
 /* Copyright (c) 2009 Arnaud Renevier, Inc, published under the modified BSD
    license. */
 
-require ("./inc/db/anydb.php");
+require_once ("./inc/db/anydb.php");
 
 class mysqlConnection implements anydbConnection {
     var $link = null;
@@ -28,9 +28,8 @@ class mysqlConnection implements anydbConnection {
     public function users_table_exists () {
         return $this->_tblexists ("users");
     }
-    public function create_users_table ($error_if_exists) {
+    public function create_users_table () {
         $query = sprintf ("CREATE TABLE " .
-                           ($error_if_exists ? " " : "IF NOT EXISTS ") .
                            "%susers (
                             name VARCHAR(255) NOT NULL, pwd CHAR(32),
                             PRIMARY KEY (name));", $this->dbprefix);
@@ -40,28 +39,33 @@ class mysqlConnection implements anydbConnection {
     public function items_table_exists () {
         return $this->_tblexists ("items");
     }
-    public function create_items_table ($error_if_exists) {
+    public function create_items_table () {
         $query = sprintf ("CREATE TABLE " .
-                           ($error_if_exists ? " " : "IF NOT EXISTS ") .
                             "%sitems (
-                                imgurl VARCHAR(255),
+                                id MEDIUMINT NOT NULL AUTO_INCREMENT,
+                                location POINT,
                                 title VARCHAR(127),
                                 description TEXT,
-                                location POINT,
+                                imgpath VARCHAR(255),
                                 date DATETIME,
-                                PRIMARY KEY (imgurl)
+                                user VARCHAR(255),
+                                PRIMARY KEY (id)
                             );", $this->dbprefix);
         $this->_execute_query ($query);
     }
 
-    public function setpwd ($user_name, $pwd) {
+    public function setpwd ($user_name, $pwd, $create_if_not_exists) {
         $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';", 
+            if ($create_if_not_exists) {
+                $query = sprintf ("UPDATE %susers SET pwd='%s' WHERE name like '%s';", 
                                $this->dbprefix, md5 ($pwd), $usrname_escaped);
+            } else {
+                throw new Exception (anydbConnection::err_query);
+            }
         } else {
             $query = sprintf ("INSERT INTO %susers VALUES ('%s', '%s');", 
                                $this->dbprefix, $usrname_escaped, md5 ($pwd));
@@ -84,67 +88,80 @@ class mysqlConnection implements anydbConnection {
     }
 
     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 ()) {
+        try {
+            $id = $feature->id;
+        } catch (Exception $e) {}
+        if (isset ($id)) {
+            $query = sprintf ("UPDATE %sitems SET
+                                    imgpath='%s', 
+                                    title='%s', 
+                                    description='%s', 
+                                    location=GeomFromText('POINT(%s %s)')
+                            WHERE id = '%s';",
+                            $this->dbprefix,
+                            mysql_real_escape_string ($feature->imgpath),
+                            mysql_real_escape_string ($feature->title),
+                            mysql_real_escape_string ($feature->description),
+                            $feature->lon,
+                            $feature->lat,
+                            $id);
+                $this->_execute_query ($query);
+                return $feature;
+        } else {
               $query = sprintf ("INSERT INTO %sitems
-                                   (imgurl, title, description, location, date)
-                                   VALUES ('%s', '%s', '%s', 
-                                   GeomFromText('POINT(%s %s)'), NOW())", 
+                              (imgpath, title, description, location, date, user)
+                                VALUES ('%s', '%s', '%s', 
+                               GeomFromText('POINT(%s %s)'), NOW(), '%s')", 
                               $this->dbprefix,
-                              mysql_real_escape_string ($feature->imgurl),
+                              mysql_real_escape_string ($feature->imgpath),
                               mysql_real_escape_string ($feature->title),
                               mysql_real_escape_string ($feature->description),
                               $feature->lon,
-                              $feature->lat
+                              $feature->lat,
+                              mysql_real_escape_string ($feature->user)
                     );
+
                 $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;
+                $id = mysql_insert_id ();
+                return new feature ($id, $feature->lon, $feature->lat,
+                                    $feature->imgpath, $feature->title,
+                                    $feature->description, $feature->date,
+                                    $feature->user);
         }
     }
 
     public function delete_feature ($feature) {
-        $query = sprintf ("DELETE from %sitems WHERE imgurl like '%s'",
+        $query = sprintf ("DELETE from %sitems WHERE id = '%s'",
                         $this->dbprefix,
-                        mysql_real_escape_string ($feature->imgurl));
+                        mysql_real_escape_string ($feature->id));
         $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));
+    public function getfeature ($id) {
+        $query = sprintf ("SELECT id, imgpath, title, description, AsText(location)
+                           AS location, UNIX_TIMESTAMP(date) AS date, user
+                           FROM %sitems WHERE id = '%s';", 
+                        $this->dbprefix, mysql_real_escape_string ($id));
         $row = mysql_fetch_assoc ($this->_execute_query ($query));
-        if (!isset ($row)) {
+        if ($row === false) {
             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);
+    public function listfeatures ($user) {
+        if ($user && ($user != "admin")) {
+            $from_user_query = sprintf (" WHERE user = '%s' ",
+                                        mysql_real_escape_string ($user));
+        } else {
+            $from_user_query = "";
+        }
+
+        $query = sprintf ("SELECT id, imgpath, title, description, AsText(location)
+                            AS location, UNIX_TIMESTAMP(date) AS date, user
+                            FROM %sitems %s;",
+                          $this->dbprefix, $from_user_query);
 
         $features = array ();
         $res = $this->_execute_query ($query);
@@ -157,51 +174,30 @@ class mysqlConnection implements anydbConnection {
         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);
+    public function mostrecentfeatures ($num_features) {
+        $query = sprintf ("SELECT id, imgpath, title, description,
+                           AsText(location) AS location, UNIX_TIMESTAMP(date)
+                           AS date, user FROM %sitems ORDER BY date DESC",
+                           $this->dbprefix);
+        if ($num_features) {
+            $query .= sprintf (" LIMIT %d", $num_features);
         }
-
-        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);
+        $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 array ($minlon, $minlat, $maxlon, $maxlat);
         }
+        return $features;
+    }
+
+    public function imgpath_exists ($imgpath) {
+        $query = sprintf ("SELECT COUNT(*) FROM %sitems WHERE imgpath LIKE '%s';",
+                           $this->dbprefix, mysql_real_escape_string ($imgpath));
+        $res = mysql_fetch_array  ($this->_execute_query ($query), MYSQL_NUM);
+        return ($res [0] >= 1) ? true : false;
     }
 
     public function getdbname () {
@@ -223,8 +219,9 @@ class mysqlConnection implements anydbConnection {
         $lon = $matches [1];
         $lat = $matches [2];
         try {
-            $feature = new feature ($row ["imgurl"], $row ["title"], $row
-                                    ["description"], $lon, $lat);
+            $feature = new feature ($row ["id"], $lon, $lat, $row ["imgpath"],
+                                    $row ["title"], $row ["description"],
+                                    $row ["date"], $row ["user"]);
         } catch (Exception $e) {
             return null;
         }