X-Git-Url: https://dev.renevier.net/gitweb.cgi?p=syp.git;a=blobdiff_plain;f=inc%2Fdb%2Fpostgresql.php;fp=inc%2Fdb%2Fpostgresql.php;h=eb8d3bfcf0937b2bf6e4b2a8d353869e2626373d;hp=0000000000000000000000000000000000000000;hb=81b566d916827fa6ee03b6fbf06c180ec249e7e7;hpb=42ad41096b29573ca0429b316d364195eac02e8e diff --git a/inc/db/postgresql.php b/inc/db/postgresql.php new file mode 100644 index 0000000..eb8d3bf --- /dev/null +++ b/inc/db/postgresql.php @@ -0,0 +1,247 @@ +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(); +?>