From 81b566d916827fa6ee03b6fbf06c180ec249e7e7 Mon Sep 17 00:00:00 2001 From: arno Date: Fri, 16 Apr 2010 17:42:01 +0200 Subject: [PATCH 1/1] postgresql support --- README.txt | 9 +- api.php | 2 +- inc/db/postgresql.php | 247 ++++++++++++++++++++++++++++++++++++++++++ inc/html/admin.php | 2 +- inc/html/index.php | 2 +- inc/html/install.php | 40 ++++++- inc/html/upgrade.php | 2 +- inc/i10n/de/syp.php | 22 +++- inc/i10n/en/syp.php | 12 ++ inc/i10n/fr/syp.php | 22 +++- inc/settings.php.in | 3 + items.php | 2 +- news.php | 2 +- 13 files changed, 352 insertions(+), 15 deletions(-) create mode 100644 inc/db/postgresql.php diff --git a/README.txt b/README.txt index 8381162..bf24c24 100644 --- a/README.txt +++ b/README.txt @@ -6,11 +6,10 @@ image and its description. Requirements ------------ - php5 -- mysql extension for php -- mysql must support spatial extension; mysql version must be >= 4.1 for MyISAM - tables or >= 5.0.16 for other tables. I assumed it would not be a problem. - If that's a problem for you, send me a mail, and I'll do without spatial - support. +- mysql (or postgresql) extension for php +- If mysql is used, it must support spatial extension; mysql version must be >= + 4.1 for MyISAM tables or >= 5.0.16 for other tables. +- If postgresql is used, version must be at least 7.4 Installation ------------ diff --git a/api.php b/api.php index e74f3b4..2dbfac4 100644 --- a/api.php +++ b/api.php @@ -385,7 +385,7 @@ function main ($con) { if (!@include_once ("./inc/settings.php")) { error_server (); } -require_once ("./inc/db/mysql.php"); +require_once ("./inc/db/" . (defined ("DBTYPE")? DBTYPE: "mysql") . ".php"); require_once ("./inc/utils.php"); try { 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(); +?> diff --git a/inc/html/admin.php b/inc/html/admin.php index b0df4b5..ef43708 100644 --- a/inc/html/admin.php +++ b/inc/html/admin.php @@ -7,7 +7,7 @@ $error = false; if (!@include_once ("./inc/settings.php")) { $error = true; } -require_once ("./inc/db/mysql.php"); +require_once ("./inc/db/" . (defined ("DBTYPE")? DBTYPE: "mysql") . ".php"); require_once ("./inc/utils.php"); try { diff --git a/inc/html/index.php b/inc/html/index.php index 550ee48..f1e3239 100644 --- a/inc/html/index.php +++ b/inc/html/index.php @@ -3,7 +3,7 @@ license. */ @include_once ("./inc/settings.php"); -require_once ("./inc/db/mysql.php"); +require_once ("./inc/db/" . (defined ("DBTYPE")? DBTYPE: "mysql") . ".php"); try { $connection->connect (DBHOST, DBUSER, DBPWD, DBNAME, DBPREFIX); diff --git a/inc/html/install.php b/inc/html/install.php index 4b2d02c..7bab8ef 100644 --- a/inc/html/install.php +++ b/inc/html/install.php @@ -54,12 +54,14 @@ function create_install_form () { if (isset ($_POST ["db_form_submit"])) { + $type = $_POST ["db_type"]; $host = $_POST ["db_host"]; $name = $_POST ["db_name"]; $user = $_POST ["db_user"]; $prefix = $_POST ["db_prefix"]; $title = $_POST ["site_title"]; } else { + $type = "mysql"; $host = "localhost"; $user = "syp"; $name = "syp"; @@ -70,6 +72,37 @@ print '
' . "\n"; print '' . trans ("SYP configuration") . '' . "\n"; + $drivers = array(); + $handle = opendir("./inc/db"); + if (!$handle) { + par_error_and_leave (trans ('Could not list inc/db directory')); + } + while (false !== ($file = readdir($handle))) { + if ($file == "." or $file == "..") { + continue; + } + $driver_name = substr($file,0,strrpos($file,'.')); + if ($driver_name == "anydb") { + continue; + } + array_push ($drivers, $driver_name); + } + closedir($handle); + + + print '
' . "\n" . + '" . "\n"; + print '
' . "\n" . @@ -119,6 +152,7 @@ } } + define (DBTYPE, _unquote ($_POST ["db_type"])); define (DBHOST, _unquote ($_POST ["db_host"])); define (DBNAME, _unquote ($_POST ["db_name"])); define (DBUSER, _unquote ($_POST ["db_user"])); @@ -138,7 +172,9 @@ leave (); } - require_once ("./inc/db/mysql.php"); + if (!include_once ("./inc/db/" . DBTYPE . ".php")) { + par_error_and_leave (trans("Unkown backend: ", DBTYPE)); + } require_once ("./inc/utils.php"); try { @@ -174,7 +210,7 @@ if ($handle) { while (!feof ($handle)) { $line = fgets ($handle, 4096); - foreach (array ("DBHOST", "DBNAME", "DBUSER", "DBPWD", "DBPREFIX", "SITETITLE") as $value) { + foreach (array ("DBTYPE", "DBHOST", "DBNAME", "DBUSER", "DBPWD", "DBPREFIX", "SITETITLE") as $value) { $pattern = "(define\s+\(\s*\"$value\"\s*,\s*\")[^\"]*(\"\s*\)\s*;)"; if (preg_match( "/$pattern/", $line, $match )) { $line = $match[1] . addslashes (constant ($value)) . $match[2]; diff --git a/inc/html/upgrade.php b/inc/html/upgrade.php index 5808684..38eefe6 100644 --- a/inc/html/upgrade.php +++ b/inc/html/upgrade.php @@ -30,7 +30,7 @@ return true; } - require_once ("./inc/db/mysql.php"); + require_once ("./inc/db/" . (defined ("DBTYPE")? DBTYPE: "mysql") . ".php"); require_once ("./inc/install_utils.php"); require_once ("./inc/utils.php"); diff --git a/inc/i10n/de/syp.php b/inc/i10n/de/syp.php index 72b264e..a55c0c7 100644 --- a/inc/i10n/de/syp.php +++ b/inc/i10n/de/syp.php @@ -58,6 +58,21 @@ "SYP-Konfiguration" , + "Could not list inc/db directory" + => + "" + , + + "You can specify a database backend. Mysql is the most available for standard web hosting services." + => + "" + , + + "database backend:" + => + "" + , + "address of the database server (example: localhost, db.myhost.com or 192.168.0.15)." => "Adresse des Datenbank-Servers (z.B.: localhost, db.myhost.com or 192.168.0.15)." @@ -123,6 +138,11 @@ "Installation starten" , + "Unknown backend:" + => + "" + , + "not supported" => "nicht unterstützt" @@ -506,4 +526,4 @@ , ) -?> \ No newline at end of file +?> diff --git a/inc/i10n/en/syp.php b/inc/i10n/en/syp.php index df80f51..effab76 100644 --- a/inc/i10n/en/syp.php +++ b/inc/i10n/en/syp.php @@ -41,6 +41,15 @@ "SYP configuration" => "", + "Could not list inc/db directory" + => "", + + "You can specify a database backend. Mysql is the most available for standard web hosting services." + => "", + + "database backend:" + => "", + "address of the database server (example: localhost, db.myhost.com or 192.168.0.15)." => "", @@ -80,6 +89,9 @@ "Start install" => "", + "Unknown backend:" + => "", + "not supported" => "", diff --git a/inc/i10n/fr/syp.php b/inc/i10n/fr/syp.php index 26b93b1..9638201 100644 --- a/inc/i10n/fr/syp.php +++ b/inc/i10n/fr/syp.php @@ -58,6 +58,21 @@ "Configuration de SYP" , + "Could not list inc/db directory" + => + "Impossible de lister inc/db" + , + + "You can specify a database backend. Mysql is the most available for standard web hosting services." + => + "You pouvez définir un type de base de données. Mysql est disponible chez la plupart des hébergeurs." + , + + "database backend:" + => + "type de base de données :" + , + "address of the database server (example: localhost, db.myhost.com or 192.168.0.15)." => "adresse du serveur de base de données (exemple: localhost, db.myhost.com ou 192.168.0.15)" @@ -123,6 +138,11 @@ "Commencer l'installation" , + "Unknown backend:" + => + "Type de base de données inconnu:" + , + "not supported" => "support non détecté" @@ -507,4 +527,4 @@ , ) -?> \ No newline at end of file +?> diff --git a/inc/settings.php.in b/inc/settings.php.in index ed12c5f..845e6b2 100644 --- a/inc/settings.php.in +++ b/inc/settings.php.in @@ -2,6 +2,9 @@ /* Copyright (c) 2009 Arnaud Renevier, Inc, published under the modified BSD license. */ +// database type +define ("DBTYPE", "mysql"); + // database hostname define ("DBHOST", "localhost"); diff --git a/items.php b/items.php index e2fe1db..37f089d 100644 --- a/items.php +++ b/items.php @@ -78,7 +78,7 @@ if (!@include_once ("./inc/settings.php")) { exit ("server error"); } require_once ("./inc/utils.php"); -require_once ("./inc/db/mysql.php"); +require_once ("./inc/db/" . (defined ("DBTYPE")? DBTYPE: "mysql") . ".php"); try { $connection->connect (DBHOST, DBUSER, DBPWD, DBNAME, DBPREFIX); diff --git a/news.php b/news.php index cc06521..886b00e 100644 --- a/news.php +++ b/news.php @@ -123,7 +123,7 @@ function main ($features) { if (!@include_once ("./inc/settings.php")) { exit ("server error"); } -require_once ("./inc/db/mysql.php"); +require_once ("./inc/db/" . (defined ("DBTYPE")? DBTYPE: "mysql") . ".php"); require_once ("./inc/utils.php"); try { -- 2.39.2