CVS: phpweather/db pw_db_pgsql.php,1.3,1.4
Brought to you by:
iridium
Menu
▾
▴
CVS: phpweather/db pw_db_pgsql.php,1.3,1.4
|
From: Martin G. <gim...@us...> - 2002-05-20 15:47:12
|
Update of /cvsroot/phpweather/phpweather/db
In directory usw-pr-cvs1:/tmp/cvs-serv25913
Modified Files:
pw_db_pgsql.php
Log Message:
I've updated the PostgreSQL backend so that it actually works! :-)
I've tested it on my PostgreSQL database version 7.2.1 which is the
one that comes with Debian unstable.
Index: pw_db_pgsql.php
===================================================================
RCS file: /cvsroot/phpweather/phpweather/db/pw_db_pgsql.php,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -3 -r1.3 -r1.4
--- pw_db_pgsql.php 26 Mar 2002 18:52:44 -0000 1.3
+++ pw_db_pgsql.php 20 May 2002 15:47:08 -0000 1.4
@@ -9,19 +9,54 @@
* retrive METARs using a PostgreSQL database.
*
* @author Kristian Kristensen <co...@zi...>
+ * @author Martin Geisler <gim...@gi...>
* @version $Id$
*/
class pw_db_pgsql extends pw_db_common {
-
+
+ /**
+ * The next row that should be fetched in the result set.
+ *
+ * This is used by the methods fetch_row() and fetch_array() to keep
+ * track of how far they have come in the result set.
+ *
+ * @var integer
+ * @access private
+ */
+ var $next_row = 0;
+
+
/**
- * This constructor does nothing besides calling the parent constructor.
+ * The number of rows in the current result set.
+ *
+ * We store this here because we have to refer to it again and again
+ * in the fetch_row() and fetch_array() methods.
+ *
+ * @var integer
+ * @access private
+ */
+ //var $num_rows = 0;
+
+
+ /**
+ * This constructor makes sure that the PostgreSQL extension is
+ * loaded and then calls the parent constructor.
*
* @param array the initial properties of the object
*/
function pw_db_pgsql($input = array()) {
+ /* We have to load the PgSQL extension on some systems: */
+ if (!extension_loaded('pgsql')) {
+ if (ereg('win', PHP_OS)) {
+ dl('pgsql.dll');
+ } else {
+ dl('pgsql.so');
+ }
+ }
$this->pw_db_common($input);
}
+
/**
* Gets the type of the database.
*
@@ -52,10 +87,22 @@
if ($this->is_connected) {
return true;
}
+
+ if (empty($this->properties['db_port']))
+ /* Default PostgreSQL port: */
+ $port = 5432;
+ else
+ $port = $this->properties['db_port'];
+
+ $connect_str = sprintf('host=%s port=%s dbname=%s user=%s password=%s',
+ $this->properties['db_hostname'], $port,
+ $this->properties['db_database'],
+ $this->properties['db_username'],
+ $this->properties['db_password']);
if (!$this->properties['db_pconnect']) {
- $this->link_id = pgsql_connect($this->properties['db_database']);
+ $this->link_id = pg_connect($connect_str);
} else {
- $this->link_id = pgsql_pconnect($this->properties['db_database']);
+ $this->link_id = pg_pconnect($connect_str);
}
if ($this->link_id) {
$this->is_connected = true;
@@ -65,6 +112,7 @@
}
return $this->is_connected;
}
+
/**
* Disconnects from the database.
@@ -88,17 +136,16 @@
/**
- * Selects a database.
+ * Selects a database. This should already have been taken care of
+ * when the connection was made to the database, so this will just
+ * run connect() and return whatever it returns.
*
* @return boolean Returns true on success, false otherwise.
* @access public
+ * @see connect()
*/
function select_db() {
- if ($this->is_connected) {
- return true;
- } else {
- return false;
- }
+ return $this->connect();
}
@@ -112,7 +159,16 @@
* @access public
*/
function query($query) {
- return ($this->result_id = pg_exec( $this->link_id, $query));
+ $this->result_id = pg_exec($this->link_id, $query) or
+ $this->error("SQL-statement failed: $query<br>PostgreSQL said " .
+ pg_errormessage());
+
+ /* We can now update $this->num_rows: */
+ //$this->num_rows = pg_numrows($this->result_id);
+ /* The next row is the first row: */
+ $this->next_row = 0;
+
+ return $this->result_id;
}
/**
@@ -122,7 +178,13 @@
* @access public
*/
function fetch_row() {
- return pg_fetch_row($this->result_id,0);
+ if ($this->next_row < $this->num_rows())
+ /* There are still rows left in the result set. We use the
+ post-increment operator (++) to increment $this->next_row
+ immediately after it has been used. */
+ return pg_fetch_row($this->result_id, $this->next_row++);
+ else
+ return false;
}
/**
@@ -133,9 +195,16 @@
* @access public
*/
function fetch_array() {
- return pg_fetch_array($this->result_id,0);
+ if ($this->next_row < $this->num_rows())
+ /* There are still rows left in the result set. We use the
+ post-increment operator (++) to increment $this->next_row
+ immediately after it has been used. */
+ return pg_fetch_array($this->result_id, $this->next_row++);
+ else
+ return false;
}
+
/**
* Returns the number of rows in the result-set.
*
@@ -144,8 +213,10 @@
*/
function num_rows() {
return pg_numrows($this->result_id);
+ //return $this->num_rows;
}
+
/**
* Inserts a METAR into the database.
*
@@ -155,11 +226,11 @@
* @access public
* @see update_metar()
*/
- function insert_metar($station, $metar, $timestamp) {
+ function insert_metar($icao, $metar, $timestamp) {
$this->query('INSERT INTO ' . $this->properties['db_metars'] .
- " SET station = '$station', " .
- "metar = '" . addslashes($metar) . "', " .
- "timestamp = FROM_UNIXTIME($timestamp)");
+ '(icao, metar, timestamp) VALUES (' .
+ "'$icao', '" . addslashes($metar) . "', '" .
+ date('r', $timestamp) . "')");
}
@@ -172,13 +243,14 @@
* @access public
* @see insert_metar()
*/
- function update_metar($station, $metar, $timestamp) {
+ function update_metar($icao, $metar, $timestamp) {
$this->query('UPDATE ' . $this->properties['db_metars'] .
" SET metar = '" . addslashes($metar) .
- "', timestamp = '$timestamp' " .
- "WHERE station = '$station'");
+ "', timestamp = '" . date('r', $timestamp) .
+ "' WHERE icao = '$icao'");
}
+
/**
* Gets a METAR form the database.
*
@@ -186,11 +258,151 @@
* @return string The raw METAR as an array from the database.
* @access public
*/
- function get_metar($station) {
- $this->query('SELECT metar, timestamp FROM ' .
+ function get_metar($icao) {
+ $this->query('SELECT metar, EXTRACT(EPOCH FROM timestamp) FROM ' .
$this->properties['db_metars'] .
- " WHERE station = '$station'");
+ " WHERE icao = '$icao'");
return $this->fetch_row();
+ }
+
+
+ /**
+ * Creates the necessary tables in the database.
+ *
+ * @return bool Returns true if it could connect to the database,
+ * false otherwise.
+ * @access private
+ */
+ function create_tables() {
+ if (!$this->connect()) {
+ return false; // Failure!
+ }
+
+ /* First we make a table for the METARs */
+ $this->query('DROP TABLE ' . $this->properties['db_metars']);
+ $this->query('CREATE TABLE ' . $this->properties['db_metars'] .
+ '(icao char(4) PRIMARY KEY,' .
+ ' metar varchar(255) NOT NULL,' .
+ ' timestamp timestamp with time zone)');
+
+ /* Then we make a table for the stations. */
+ $this->query('DROP TABLE ' . $this->properties['db_stations']);
+ $this->query('CREATE TABLE ' . $this->properties['db_stations'] .
+ '(icao char(4) PRIMARY KEY,' .
+ ' name varchar(255) NOT NULL,' .
+ ' cc char(2) NOT NULL,' .
+ ' country varchar(128) NOT NULL)');
+ $this->query('CREATE INDEX cc_key ON ' .
+ $this->properties['db_stations'] . '(cc)');
+
+ return true; // Succes!
+ }
+
+ /**
+ * Translates an ICAO into a station name
+ *
+ * The boring ICAO (e.g. EKYT) is translated into something like
+ * 'Aalborg, Denmark'.
+ *
+ * @param string The ICAO one want's to translate.
+ * @return string The full name of the station, including country.
+ * @access public
+ */
+ function lookup_icao($icao) {
+ $this->query('SELECT name, country FROM ' .
+ $this->properties['db_stations'] . " WHERE icao = '$icao'");
+ if ($this->num_rows() == 1) {
+ $row = $this->fetch_row();
+ return "$row[0], $row[1]";
+ } else {
+ return $icao;
+ }
+ }
+
+ /**
+ * Inserts the stations into the database.
+ *
+ * It is assumed that create_tables() has been called previously
+ * (and that it returned true), so that the necessary tables are
+ * already created.
+ *
+ * @param array This three-dimensional array starts with a list of
+ * contry-codes. For each country-code the ICAOs and corresponding
+ * locations in that particular country are listed as key => value
+ * pairs.
+ * @param array An associative array with country-codes as the keys
+ * and the names of the countries as the values.
+ * @return bool
+ * @access private
+ */
+ function insert_stations($data, $countries) {
+ if (!$this->connect()) {
+ return false;
+ }
+
+ while(list($cc, $country) = each($countries)) {
+ /* The country names might contain dangerous characters. */
+ $country = addslashes($country);
+ while(list($icao, $location) = each($data[$cc])) {
+ /* The station name might also be dangerous. */
+ $location = addslashes($location);
+ $this->query('INSERT INTO ' . $this->properties['db_stations'] .
+ '(icao, name, cc, country) VALUES ' .
+ "('$icao', '$location', '$cc', '$country')");
+ }
+ }
+ return true;
+ }
+
+
+ /**
+ * Returns a list of available countries.
+ *
+ * @return array An associative array with the country-codes as the
+ * keys and the names of the countries as the values.
+ * @access public
+ */
+ function get_countries() {
+ if (!$this->connect()) {
+ return false;
+ }
+
+ $this->query('SELECT DISTINCT cc, country FROM ' .
+ $this->properties['db_stations'] . ' ORDER BY country');
+ while($row = $this->fetch_row()) {
+ $rows[$row[0]] = $row[1];
+ }
+ return $rows;
+ }
+
+
+ /**
+ * Returns an array of stations.
+ *
+ * @param string The country-code.
+ * @param string This parameter is passed by reference. The name of
+ * the country that corresponds to the country-code is stored here.
+ * @return array An associative array with the ICAO as the key and
+ * the name of the station as the values. The name of the country is
+ * not added to the name of the station.
+ * @access public
+ */
+ function get_icaos($cc, &$country) {
+ if (!$this->connect()) {
+ return false;
+ }
+
+ $this->query('SELECT icao, name, country FROM ' .
+ $this->properties['db_stations'] .
+ " WHERE cc = '$cc' ORDER BY name");
+ /* We have to do this manually the first time, so that we can set
+ $country */
+ list($icao, $name, $country) = $this->fetch_row();
+ $rows[$icao] = $name;
+ while(list($icao, $name) = $this->fetch_row()) {
+ $rows[$icao] = $name;
+ }
+ return $rows;
}
}
|
