inc/db/postgresql.php

   1 <?php
   2 /* Copyright (c) 2010 Arnaud Renevier, Inc, published under the modified BSD
   3    license. */
   4
   5 require_once ("./inc/db/anydb.php");
   6
   7 class postgresqlConnection implements anydbConnection {
   8     var $link = null;
   9     var $dbprefix = null;
  10
  11     public function connect ($host, $user, $pwd, $dbname, $dbprefix) {
  12         if (!function_exists ("pg_connect")) {
  13             throw new Exception (anydbConnection::err_driver_unavailable);
  14         }
  15         if ($this->link) { // connection has already been opened
  16             return;
  17         }
  18         $this->link = @pg_connect ("host= " . $host . " user=" . $user . " password=" . $pwd . " dbname=" . $dbname);
  19         if (!$this->link) {
  20             throw new Exception (anydbConnection::err_connection);
  21         }
  22         $this->dbprefix = $dbprefix;
  23     }
  24
  25     public function users_table_exists () {
  26         return $this->_tblexists ("users");
  27     }
  28
  29     public function create_users_table () {
  30         $query = sprintf ("CREATE TABLE " .
  31                            "%susers (
  32                             name VARCHAR(255) NOT NULL, pwd CHAR(32),
  33                             PRIMARY KEY (name));", $this->dbprefix);
  34         $this->_execute_query ($query);
  35     }
  36
  37     public function items_table_exists () {
  38         return $this->_tblexists ("items");
  39     }
  40     public function create_items_table () {
  41         $query = sprintf ("CREATE TABLE " .
  42                             "%sitems (
  43                                 id SERIAL,
  44                                 longitude NUMERIC,
  45                                 latitude NUMERIC,
  46                                 title VARCHAR(127),
  47                                 description TEXT,
  48                                 imgpath VARCHAR(255),
  49                                 date TIMESTAMP,
  50                                 owner VARCHAR(255),
  51                                 PRIMARY KEY (id)
  52                             );", $this->dbprefix);
  53         $this->_execute_query ($query);
  54     }
  55
  56     public function user_exists ($user_name) {
  57         $usrname_escaped = pg_escape_string ($user_name);
  58         $query = sprintf ("SELECT COUNT(*) FROM %susers WHERE name LIKE '%s';",
  59                         $this->dbprefix, $usrname_escaped);
  60         $res = pg_fetch_array ($this->_execute_query ($query));
  61         return ($res[0] >= 1);
  62     }
  63
  64     public function setpwd ($user_name, $pwd) {
  65         if (strlen ($pwd) == 0) {
  66             throw new Exception (anydbConnection::err_query);
  67         }
  68         $usrname_escaped = pg_escape_string ($user_name);
  69         if ($this->user_exists ($user_name)) {
  70             $query = sprintf ("UPDATE %susers SET pwd='%s' WHERE name like '%s';",
  71                     $this->dbprefix, md5 ($pwd), $usrname_escaped);
  72         } else {
  73             $query = sprintf ("INSERT INTO %susers VALUES ('%s', '%s');",
  74                                $this->dbprefix, $usrname_escaped, md5 ($pwd));
  75         }
  76         $this->_execute_query ($query);
  77     }
  78
  79     public function checkpwdmd5 ($user_name, $pwd_md5) {
  80         $query = sprintf ("SELECT COUNT(*) FROM %susers WHERE name LIKE '%s'
  81                            AND pwd LIKE '%s';",
  82                            $this->dbprefix,
  83                            pg_escape_string ($user_name),
  84                            pg_escape_string ($pwd_md5));
  85         $res = pg_fetch_array ($this->_execute_query ($query));
  86         if ($res[0] >= 1) {
  87             return true;
  88         } else {
  89             return false;
  90         }
  91     }
  92
  93     public function save_feature ($feature) {
  94         try {
  95             $id = $feature->id;
  96         } catch (Exception $e) {}
  97         if (isset ($id)) {
  98             $query = sprintf ("UPDATE %sitems SET
  99                                     imgpath='%s',
 100                                     title='%s',
 101                                     description='%s',
 102                                     longitude='%s',
 103                                     latitude='%s'
 104                             WHERE id = '%s';",
 105                             $this->dbprefix,
 106                             pg_escape_string ($feature->imgpath),
 107                             pg_escape_string ($feature->title),
 108                             pg_escape_string ($feature->description),
 109                             $feature->lon,
 110                             $feature->lat,
 111                             $id);
 112                 $this->_execute_query ($query);
 113                 return $feature;
 114         } else {
 115               $query = sprintf ("INSERT INTO %sitems
 116                               (imgpath, title, description, longitude, latitude, date, owner)
 117                                 VALUES ('%s', '%s', '%s', %s, %s, NOW(), '%s')",
 118                               $this->dbprefix,
 119                               pg_escape_string ($feature->imgpath),
 120                               pg_escape_string ($feature->title),
 121                               pg_escape_string ($feature->description),
 122                               $feature->lon,
 123                               $feature->lat,
 124                               pg_escape_string ($feature->user)
 125                     );
 126
 127                 $this->_execute_query ($query);
 128                 // mimics mysql_insert_id
 129                 $res = pg_fetch_array(pg_query($this->link, sprintf( "select currval('%sitems_id_seq')", $this->dbprefix)));
 130                 $id = $res[0];
 131                 return new feature ($id, $feature->lon, $feature->lat,
 132                                     $feature->imgpath, $feature->title,
 133                                     $feature->description, $feature->date,
 134                                     $feature->user);
 135         }
 136     }
 137
 138     public function delete_feature ($feature) {
 139         $query = sprintf ("DELETE from %sitems WHERE id = '%s'",
 140                         $this->dbprefix,
 141                         pg_escape_string ($feature->id));
 142         $this->_execute_query ($query);
 143         return true;
 144     }
 145
 146     public function getfeature ($id) {
 147         $query = sprintf ("SELECT id, imgpath, title, description, longitude, latitude,
 148                            date_part('epoch', date) AS date, owner
 149                            FROM %sitems WHERE id = '%s';",
 150                         $this->dbprefix, pg_escape_string ($id));
 151         $row = pg_fetch_assoc ($this->_execute_query ($query));
 152         if ($row === false) {
 153             return null;
 154         }
 155         return $this->_feature_frow_row ($row);
 156     }
 157
 158     public function listfeatures ($user) {
 159         if ($user && ($user != "admin")) {
 160             $from_user_query = sprintf (" WHERE owner = '%s' ",
 161                                         pg_escape_string ($user));
 162         } else {
 163             $from_user_query = "";
 164         }
 165
 166         $query = sprintf ("SELECT id, imgpath, title, description, longitude, latitude,
 167                             date_part('epoch', date) AS date, owner
 168                             FROM %sitems %s;",
 169                           $this->dbprefix, $from_user_query);
 170
 171         $features = array ();
 172         $res = $this->_execute_query ($query);
 173         while ($row = pg_fetch_assoc ($res)) {
 174             $feature = $this->_feature_frow_row ($row);
 175             if (isset ($feature)) {
 176                 $features[] = $feature;
 177             }
 178         }
 179         return $features;
 180     }
 181
 182     public function mostrecentfeatures ($num_features) {
 183         $query = sprintf ("SELECT id, imgpath, title, description, longitude, latitude,
 184                            date_part('epoch', date) AS date, owner FROM %sitems ORDER BY date DESC",
 185                            $this->dbprefix);
 186         if ($num_features) {
 187             $query .= sprintf (" LIMIT %d", $num_features);
 188         }
 189         $features = array ();
 190         $res = $this->_execute_query ($query);
 191         while ($row = pg_fetch_assoc ($res)) {
 192             $feature = $this->_feature_frow_row ($row);
 193             if (isset ($feature)) {
 194                 $features[] = $feature;
 195             }
 196         }
 197         return $features;
 198     }
 199
 200     public function imgpath_exists ($imgpath) {
 201         $query = sprintf ("SELECT COUNT(*) FROM %sitems WHERE imgpath LIKE '%s';",
 202                            $this->dbprefix, pg_escape_string ($imgpath));
 203         $res = pg_fetch_array  ($this->_execute_query ($query));
 204         return ($res [0] >= 1);
 205     }
 206
 207     public function getdbname () {
 208         return "PostgreSQL";
 209     }
 210
 211     private function _tblexists ($tblname) {
 212         $query = sprintf("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='public' and table_name = '%s%s';",
 213                             $this->dbprefix, $tblname);
 214         $res = pg_fetch_array ($this->_execute_query ($query));
 215         return ($res[0] >= 1);
 216     }
 217
 218     private function _feature_frow_row ($row) {
 219         // XXX: should I remove invalid features from database ?
 220         try {
 221             $feature = new feature ($row ["id"], $row["longitude"], $row["latitude"], $row ["imgpath"],
 222                                     $row ["title"], $row ["description"],
 223                                     $row ["date"], $row ["owner"]);
 224         } catch (Exception $e) {
 225             return null;
 226         }
 227         return $feature;
 228     }
 229
 230     private function _execute_query ($query) {
 231         if (!function_exists ("pg_query")) {
 232             throw new Exception (anydbConnection::err_driver_unavailable);
 233         }
 234         if (!$this->link) {
 235             throw new Exception (anydbConnection::err_query);
 236         }
 237         $res = pg_query ($this->link, $query);
 238         if ($res == false) {
 239             throw new Exception (anydbConnection::err_query);
 240         }
 241         return $res;
 242     }
 243
 244 }
 245
 246 $connection = new postgresqlConnection();
 247 ?>