2 /* Copyright (c) 2010 Arnaud Renevier, Inc, published under the modified BSD
5 require_once ("./inc/db/anydb.php");
7 class postgresqlConnection implements anydbConnection {
11 public function connect ($host, $user, $pwd, $dbname, $dbprefix) {
12 if (!function_exists ("pg_connect")) {
13 throw new Exception (anydbConnection::err_driver_unavailable);
15 if ($this->link) { // connection has already been opened
18 $this->link = @pg_connect ("host= " . $host . " user=" . $user . " password=" . $pwd . " dbname=" . $dbname);
20 throw new Exception (anydbConnection::err_connection);
22 $this->dbprefix = $dbprefix;
25 public function users_table_exists () {
26 return $this->_tblexists ("users");
29 public function create_users_table () {
30 $query = sprintf ("CREATE TABLE " .
32 name VARCHAR(255) NOT NULL, pwd CHAR(32),
33 PRIMARY KEY (name));", $this->dbprefix);
34 $this->_execute_query ($query);
37 public function items_table_exists () {
38 return $this->_tblexists ("items");
40 public function create_items_table () {
41 $query = sprintf ("CREATE TABLE " .
52 );", $this->dbprefix);
53 $this->_execute_query ($query);
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);
64 public function setpwd ($user_name, $pwd) {
65 if (strlen ($pwd) == 0) {
66 throw new Exception (anydbConnection::err_query);
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);
73 $query = sprintf ("INSERT INTO %susers VALUES ('%s', '%s');",
74 $this->dbprefix, $usrname_escaped, md5 ($pwd));
76 $this->_execute_query ($query);
79 public function checkpwdmd5 ($user_name, $pwd_md5) {
80 $query = sprintf ("SELECT COUNT(*) FROM %susers WHERE name LIKE '%s'
83 pg_escape_string ($user_name),
84 pg_escape_string ($pwd_md5));
85 $res = pg_fetch_array ($this->_execute_query ($query));
93 public function save_feature ($feature) {
96 } catch (Exception $e) {}
98 $query = sprintf ("UPDATE %sitems SET
106 pg_escape_string ($feature->imgpath),
107 pg_escape_string ($feature->title),
108 pg_escape_string ($feature->description),
112 $this->_execute_query ($query);
115 $query = sprintf ("INSERT INTO %sitems
116 (imgpath, title, description, longitude, latitude, date, owner)
117 VALUES ('%s', '%s', '%s', %s, %s, NOW(), '%s')",
119 pg_escape_string ($feature->imgpath),
120 pg_escape_string ($feature->title),
121 pg_escape_string ($feature->description),
124 pg_escape_string ($feature->user)
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)));
131 return new feature ($id, $feature->lon, $feature->lat,
132 $feature->imgpath, $feature->title,
133 $feature->description, $feature->date,
138 public function delete_feature ($feature) {
139 $query = sprintf ("DELETE from %sitems WHERE id = '%s'",
141 pg_escape_string ($feature->id));
142 $this->_execute_query ($query);
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) {
155 return $this->_feature_frow_row ($row);
158 public function listfeatures ($user) {
159 if ($user && ($user != "admin")) {
160 $from_user_query = sprintf (" WHERE owner = '%s' ",
161 pg_escape_string ($user));
163 $from_user_query = "";
166 $query = sprintf ("SELECT id, imgpath, title, description, longitude, latitude,
167 date_part('epoch', date) AS date, owner
169 $this->dbprefix, $from_user_query);
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;
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",
187 $query .= sprintf (" LIMIT %d", $num_features);
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;
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);
207 public function getdbname () {
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);
218 private function _feature_frow_row ($row) {
219 // XXX: should I remove invalid features from database ?
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) {
230 private function _execute_query ($query) {
231 if (!function_exists ("pg_query")) {
232 throw new Exception (anydbConnection::err_driver_unavailable);
235 throw new Exception (anydbConnection::err_query);
237 $res = pg_query ($this->link, $query);
239 throw new Exception (anydbConnection::err_query);
246 $connection = new postgresqlConnection();