real api and clearer separation between server and client

[syp.git] / inc / db / mysql.php

diff --git a/inc/db/mysql.php b/inc/db/mysql.php
index 1e20c34e526fde9ff2a18134c35bfec519bb75a0..e4c61d2c8e629a6fa2ccd6ddbbe639c07a1f2d1e 100644 (file)
--- 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;
@@ -44,12 +44,14 @@ class mysqlConnection implements anydbConnection {
         $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);
     }
@@ -84,65 +86,67 @@ 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(), 'admin')", 
                               $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
                     );
+
                 $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);
         }
     }
 
     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 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)
+        $query = sprintf ("SELECT id, imgpath, title, description, AsText(location)
                             AS location FROM %sitems;",
                           $this->dbprefix);
 
@@ -157,9 +161,9 @@ 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));
+    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;
     }
@@ -223,8 +227,8 @@ 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"]);
         } catch (Exception $e) {
             return null;
         }