+<?php
+/* Copyright (c) 2010 Arnaud Renevier, Inc, published under the modified BSD
+ license. */
+
+require_once ("./inc/db/anydb.php");
+
+class postgresqlConnection implements anydbConnection {
+ var $link = null;
+ var $dbprefix = null;
+
+ public function connect ($host, $user, $pwd, $dbname, $dbprefix) {
+ if (!function_exists ("pg_connect")) {
+ throw new Exception (anydbConnection::err_driver_unavailable);
+ }
+ if ($this->link) { // connection has already been opened
+ return;
+ }
+ $this->link = @pg_connect ("host= " . $host . " user=" . $user . " password=" . $pwd . " dbname=" . $dbname);
+ if (!$this->link) {
+ throw new Exception (anydbConnection::err_connection);
+ }
+ $this->dbprefix = $dbprefix;
+ }
+
+ public function users_table_exists () {
+ return $this->_tblexists ("users");
+ }
+
+ public function create_users_table () {
+ $query = sprintf ("CREATE TABLE " .
+ "%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 () {
+ $query = sprintf ("CREATE TABLE " .
+ "%sitems (
+ id SERIAL,
+ longitude NUMERIC,
+ latitude NUMERIC,
+ title VARCHAR(127),
+ description TEXT,
+ imgpath VARCHAR(255),
+ date TIMESTAMP,
+ owner VARCHAR(255),
+ PRIMARY KEY (id)
+ );", $this->dbprefix);
+ $this->_execute_query ($query);
+ }
+
+ public function user_exists ($user_name) {
+ $usrname_escaped = pg_escape_string ($user_name);
+ $query = sprintf ("SELECT COUNT(*) FROM %susers WHERE name LIKE '%s';",
+ $this->dbprefix, $usrname_escaped);
+ $res = pg_fetch_array ($this->_execute_query ($query));
+ return ($res[0] >= 1);
+ }
+
+ public function setpwd ($user_name, $pwd) {
+ if (strlen ($pwd) == 0) {
+ throw new Exception (anydbConnection::err_query);
+ }
+ $usrname_escaped = pg_escape_string ($user_name);
+ if ($this->user_exists ($user_name)) {
+ $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,
+ pg_escape_string ($user_name),
+ pg_escape_string ($pwd_md5));
+ $res = pg_fetch_array ($this->_execute_query ($query));
+ if ($res[0] >= 1) {
+ return true;
+ } else {
+ return false;
+ }
+ }
+
+ public function save_feature ($feature) {
+ try {
+ $id = $feature->id;
+ } catch (Exception $e) {}
+ if (isset ($id)) {
+ $query = sprintf ("UPDATE %sitems SET
+ imgpath='%s',
+ title='%s',
+ description='%s',
+ longitude='%s',
+ latitude='%s'
+ WHERE id = '%s';",
+ $this->dbprefix,
+ pg_escape_string ($feature->imgpath),
+ pg_escape_string ($feature->title),
+ pg_escape_string ($feature->description),
+ $feature->lon,
+ $feature->lat,
+ $id);
+ $this->_execute_query ($query);
+ return $feature;
+ } else {
+ $query = sprintf ("INSERT INTO %sitems
+ (imgpath, title, description, longitude, latitude, date, owner)
+ VALUES ('%s', '%s', '%s', %s, %s, NOW(), '%s')",
+ $this->dbprefix,
+ pg_escape_string ($feature->imgpath),
+ pg_escape_string ($feature->title),
+ pg_escape_string ($feature->description),
+ $feature->lon,
+ $feature->lat,
+ pg_escape_string ($feature->user)
+ );
+
+ $this->_execute_query ($query);
+ // mimics mysql_insert_id
+ $res = pg_fetch_array(pg_query($this->link, sprintf( "select currval('%sitems_id_seq')", $this->dbprefix)));
+ $id = $res[0];
+ 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 id = '%s'",
+ $this->dbprefix,
+ pg_escape_string ($feature->id));
+ $this->_execute_query ($query);
+ return true;
+ }
+
+ public function getfeature ($id) {
+ $query = sprintf ("SELECT id, imgpath, title, description, longitude, latitude,
+ date_part('epoch', date) AS date, owner
+ FROM %sitems WHERE id = '%s';",
+ $this->dbprefix, pg_escape_string ($id));
+ $row = pg_fetch_assoc ($this->_execute_query ($query));
+ if ($row === false) {
+ return null;
+ }
+ return $this->_feature_frow_row ($row);
+ }
+
+ public function listfeatures ($user) {
+ if ($user && ($user != "admin")) {
+ $from_user_query = sprintf (" WHERE owner = '%s' ",
+ pg_escape_string ($user));
+ } else {
+ $from_user_query = "";
+ }
+
+ $query = sprintf ("SELECT id, imgpath, title, description, longitude, latitude,
+ date_part('epoch', date) AS date, owner
+ FROM %sitems %s;",
+ $this->dbprefix, $from_user_query);
+
+ $features = array ();
+ $res = $this->_execute_query ($query);
+ while ($row = pg_fetch_assoc ($res)) {
+ $feature = $this->_feature_frow_row ($row);
+ if (isset ($feature)) {
+ $features[] = $feature;
+ }
+ }
+ return $features;
+ }
+
+ public function mostrecentfeatures ($num_features) {
+ $query = sprintf ("SELECT id, imgpath, title, description, longitude, latitude,
+ date_part('epoch', date) AS date, owner FROM %sitems ORDER BY date DESC",
+ $this->dbprefix);
+ if ($num_features) {
+ $query .= sprintf (" LIMIT %d", $num_features);
+ }
+ $features = array ();
+ $res = $this->_execute_query ($query);
+ while ($row = pg_fetch_assoc ($res)) {
+ $feature = $this->_feature_frow_row ($row);
+ if (isset ($feature)) {
+ $features[] = $feature;
+ }
+ }
+ return $features;
+ }
+
+ public function imgpath_exists ($imgpath) {
+ $query = sprintf ("SELECT COUNT(*) FROM %sitems WHERE imgpath LIKE '%s';",
+ $this->dbprefix, pg_escape_string ($imgpath));
+ $res = pg_fetch_array ($this->_execute_query ($query));
+ return ($res [0] >= 1);
+ }
+
+ public function getdbname () {
+ return "PostgreSQL";
+ }
+
+ private function _tblexists ($tblname) {
+ $query = sprintf("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='public' and table_name = '%s%s';",
+ $this->dbprefix, $tblname);
+ $res = pg_fetch_array ($this->_execute_query ($query));
+ return ($res[0] >= 1);
+ }
+
+ private function _feature_frow_row ($row) {
+ // XXX: should I remove invalid features from database ?
+ try {
+ $feature = new feature ($row ["id"], $row["longitude"], $row["latitude"], $row ["imgpath"],
+ $row ["title"], $row ["description"],
+ $row ["date"], $row ["owner"]);
+ } catch (Exception $e) {
+ return null;
+ }
+ return $feature;
+ }
+
+ private function _execute_query ($query) {
+ if (!function_exists ("pg_query")) {
+ throw new Exception (anydbConnection::err_driver_unavailable);
+ }
+ if (!$this->link) {
+ throw new Exception (anydbConnection::err_query);
+ }
+ $res = pg_query ($this->link, $query);
+ if ($res == false) {
+ throw new Exception (anydbConnection::err_query);
+ }
+ return $res;
+ }
+
+}
+
+$connection = new postgresqlConnection();
+?>
projects / syp.git / commitdiff