CVS: phpweather/db pw_db_adodb.php,1.1,1.2 pw_db_mysql.php,1.9,1.10
Brought to you by:
iridium
|
From: Etienne T. <eti...@us...> - 2003-10-02 22:54:53
|
Update of /cvsroot/phpweather/phpweather/db
In directory sc8-pr-cvs1:/tmp/cvs-serv19332/db
Modified Files:
pw_db_adodb.php pw_db_mysql.php
Log Message:
Added retrieval of archived metars. The current METAR is still in metar and decoded_metar, time is in YYYYDDMMhhmmss format. There are additionnal arrays metar_arch decoded_metar_arch which contain all the METARs in the period set by data_retrieval::set_times(). Modified db/pw_adodb.php and db/pw_mysql.php.
Index: pw_db_adodb.php
===================================================================
RCS file: /cvsroot/phpweather/phpweather/db/pw_db_adodb.php,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -3 -r1.1 -r1.2
--- pw_db_adodb.php 10 Nov 2002 23:15:42 -0000 1.1
+++ pw_db_adodb.php 2 Oct 2003 22:54:46 -0000 1.2
@@ -70,6 +70,8 @@
$this->link_id = &ADONewConnection($this->properties['db_adodb_driver']);
+ $this->link_id->SetFetchMode(ADODB_FETCH_BOTH);
+
if ($this->properties['db_pconnect']) {
$this->link_id->PConnect($this->properties['db_hostname'],
$this->properties['db_username'],
@@ -189,14 +191,16 @@
* @param string The ICAO of the station.
* @param string The raw METAR.
* @param integer A standard UNIX timestamp.
+ * @param string The time of the report
* @access public
* @see update_metar()
*/
- function insert_metar($icao, $metar, $timestamp) {
- $this->query(sprintf('INSERT INTO %s SET icao = "%s", ' .
- 'metar = "%s", timestamp = FROM_UNIXTIME(%d)',
- $this->properties['db_metars'], $icao,
- addslashes($metar), intval($timestamp)));
+ function insert_metar($icao, $metar, $timestamp, $time) {
+ $this->query(sprintf('INSERT INTO %s SET icao = "%s", time = "%s", ' .
+ 'metar = "%s", timestamp = FROM_UNIXTIME(%d)',
+ $this->properties['db_metars'], $icao, $time,
+ addslashes($metar), intval($timestamp)));
+ $this->insert_metar_arch($icao, $metar, $time);
}
@@ -206,17 +210,48 @@
* @param string The ICAO of the station.
* @param string The raw METAR.
* @param integer A standard UNIX timestamp.
+ * @param string The time of the report
* @access public
* @see insert_metar()
*/
- function update_metar($icao, $metar, $timestamp) {
+ function update_metar($icao, $metar, $timestamp, $time) {
$this->query(sprintf('UPDATE %s' .
' SET metar = "%s", timestamp = FROM_UNIXTIME(%d)' .
' WHERE icao = "%s"',
$this->properties['db_metars'], addslashes($metar),
intval($timestamp), $icao));
+ $this->insert_metar_arch($icao, $metar, $time);
+ }
+
+
+ /**
+ * Inserts an archive METAR into the database.
+ *
+ * @param string The ICAO of the station.
+ * @param string The raw METAR.
+ * @param integer The time of the report.
+ * @access public
+ */
+ function insert_metar_arch($icao, $metar, $time) {
+ if(isset($this->properties['archive_metars']) &&
+ $this->properties['archive_metars']==true) {
+ $this->query(sprintf('SHOW TABLES LIKE "%s"',
+ $this->properties['db_metars_arch']));
+ if ($this->num_rows()==1) {
+ $this->query(sprintf('DELETE FROM %s WHERE icao = "%s" AND ' .
+ 'time = "%s"' ,
+ $this->properties['db_metars_arch'],
+ $icao,$time));
+ $this->query(sprintf('INSERT IGNORE INTO %s SET icao = "%s", ' .
+ 'time = "%s", ' .
+ 'metar = "%s"',
+ $this->properties['db_metars_arch'], $icao,
+ $time,
+ addslashes($metar)));
+ }
+ }
}
-
+
/**
* Gets a METAR form the database.
@@ -225,10 +260,127 @@
* @return string The raw METAR as an array from the database.
* @access public
*/
- function get_metar($icao) {
- $this->query(sprintf('SELECT metar, UNIX_TIMESTAMP(timestamp)' .
+ function get_metar($icao, $time_from=false, $time_to=false) {
+// $this->query(sprintf('SELECT metar, UNIX_TIMESTAMP(timestamp)' .
+// ' FROM %s WHERE icao = "%s"',
+// $this->properties['db_metars'], $icao));
+// return $this->fetch_row();
+
+ /* Set num fetch mode */
+ $tmp_fetch_mode = $this->link_id->SetFetchMode(ADODB_FETCH_NUM);
+
+ if($time_from===false || $this->properties['archive_metars']===false) {
+ /* fetch the current metar */
+ $query = sprintf('SELECT metar, UNIX_TIMESTAMP(timestamp) AS timestamp, time' .
+ ' FROM %s WHERE icao = "%s" LIMIT 1',
+ $this->properties['db_metars'], $icao);
+ }
+ else if ($time_to!==false) {
+ /* fetch archived metars between $time_from and $time_to */
+ $query = sprintf('SELECT metar, UNIX_TIMESTAMP(time) AS timestamp, time' .
+ ' FROM %s WHERE icao = "%s" AND time>="%s" AND time<"%s" ORDER BY time ASC',
+ $this->properties['db_metars_arch'], $icao, $time_from, $time_to);
+ }
+ else {
+ /* fetch archived metars from $time_from */
+ $query = sprintf('SELECT metar, UNIX_TIMESTAMP(time) AS timestamp, time' .
+ ' FROM %s WHERE icao = "%s" AND time>="%s" ORDER BY time ASC',
+ $this->properties['db_metars_arch'], $icao,$time_from);
+ }
+
+ $this->query($query);
+
+ if($this->num_rows()==0) $metar_array = false;
+ else {
+ $metar_array = array();
+ while($row = $this->fetch_row()) {
+ $metar_array[] = $row;
+ }
+ }
+
+ /* Set old fetch mode */
+ $this->link_id->SetFetchMode($tmp_fetch_mode);
+
+ return $metar_array;
+ }
+
+
+ /**
+ * Inserts a TAF into the database.
+ *
+ * @param string The ICAO of the station.
+ * @param string The raw TAF.
+ * @param integer A standard UNIX timestamp.
+ * @param string The time of the report
+ * @access public
+ * @see update_taf()
+ */
+ function insert_taf($icao, $taf, $timestamp, $time) {
+ $this->query(sprintf('INSERT INTO %s SET icao = "%s", ' .
+ 'taf = "%s", timestamp = FROM_UNIXTIME(%d)',
+ $this->properties['db_tafs'], $icao,
+ addslashes($taf), intval($timestamp)));
+ $this->insert_taf_arch($icao, $taf, $time);
+ }
+
+ /**
+ * Updates an existing TAF in the database.
+ *
+ * @param string The ICAO of the station.
+ * @param string The raw TAF.
+ * @param integer A standard UNIX timestamp.
+ * @param string The time of the report
+ * @access public
+ * @see insert_taf()
+ */
+ function update_taf($icao, $taf, $timestamp, $time) {
+ $this->query(sprintf('UPDATE %s' .
+ ' SET taf = "%s", timestamp = FROM_UNIXTIME(%d)' .
+ ' WHERE icao = "%s"',
+ $this->properties['db_tafs'], addslashes($taf),
+ intval($timestamp), $icao));
+ $this->insert_taf_arch($icao, $taf, $time);
+ }
+
+ /**
+ * Inserts an archive TAF into the database.
+ *
+ * @param string The ICAO of the station.
+ * @param string The raw TAF.
+ * @param integer The time of the report.
+ * @access public
+ */
+ function insert_taf_arch($icao, $taf, $time) {
+ if(isset($this->properties['archive_tafs']) &&
+ $this->properties['archive_tafs']==true) {
+ $this->query(sprintf('SHOW TABLES LIKE "%s"',
+ $this->properties['db_tafs_arch']));
+ if ($this->num_rows()==1) {
+ $this->query(sprintf('DELETE FROM %s WHERE icao = "%s" AND ' .
+ 'time = "%s"' ,
+ $this->properties['db_tafs_arch'],
+ $icao,$time));
+ $this->query(sprintf('INSERT IGNORE INTO %s SET icao = "%s", ' .
+ 'time = "%s", ' .
+ 'taf = "%s"',
+ $this->properties['db_tafs_arch'], $icao,
+ $time,
+ addslashes($taf)));
+ }
+ }
+ }
+
+ /**
+ * Gets a TAF form the database.
+ *
+ * @param string The ICAO of the station.
+ * @return string The raw TAF as an array from the database.
+ * @access public
+ */
+ function get_taf($icao) {
+ $this->query(sprintf('SELECT taf, UNIX_TIMESTAMP(timestamp)' .
' FROM %s WHERE icao = "%s"',
- $this->properties['db_metars'], $icao));
+ $this->properties['db_tafs'], $icao));
return $this->fetch_row();
}
@@ -249,10 +401,33 @@
$this->query('DROP TABLE IF EXISTS ' . $this->properties['db_metars']);
$this->query('CREATE TABLE ' . $this->properties['db_metars'] . '(
icao char(4) NOT NULL,
+ time timestamp(14),
metar varchar(255) NOT NULL,
timestamp timestamp(14),
- PRIMARY KEY (icao),
- UNIQUE icao (icao))');
+ PRIMARY KEY (icao)');
+
+ /* Then we make a table for the TAFs */
+ $this->query('DROP TABLE IF EXISTS ' . $this->properties['db_tafs']);
+ $this->query('CREATE TABLE ' . $this->properties['db_tafs'] . '(
+ icao char(4) NOT NULL,
+ time timestamp(14),
+ taf varchar(255) NOT NULL,
+ timestamp timestamp(14),
+ PRIMARY KEY (icao)');
+
+ /* We make the archival databases */
+ $this->query('DROP TABLE IF EXISTS ' . $this->properties['db_metars_arch']);
+ $this->query('CREATE TABLE ' . $this->properties['db_metars_arch'] . '(
+ icao char(4) NOT NULL,
+ time timestamp(14) NOT NULL,
+ metar varchar(255) NOT NULL,
+ PRIMARY KEY (icao,time)');
+ $this->query('DROP TABLE IF EXISTS ' . $this->properties['db_tafs_arch']);
+ $this->query('CREATE TABLE ' . $this->properties['db_tafs_arch'] . '(
+ icao char(4) NOT NULL,
+ time timestamp(14) NOT NULL,
+ taf varchar(255) NOT NULL,
+ PRIMARY KEY (icao,time)');
/* Then we make a table for the stations. */
$this->query('DROP TABLE IF EXISTS ' . $this->properties['db_stations']);
Index: pw_db_mysql.php
===================================================================
RCS file: /cvsroot/phpweather/phpweather/db/pw_db_mysql.php,v
retrieving revision 1.9
retrieving revision 1.10
diff -u -3 -r1.9 -r1.10
--- pw_db_mysql.php 30 Sep 2003 20:01:15 -0000 1.9
+++ pw_db_mysql.php 2 Oct 2003 22:54:46 -0000 1.10
@@ -184,9 +184,9 @@
* @see update_metar()
*/
function insert_metar($icao, $metar, $timestamp, $time) {
- $this->query(sprintf('INSERT INTO %s SET icao = "%s", ' .
+ $this->query(sprintf('INSERT INTO %s SET icao = "%s", time = "%s", ' .
'metar = "%s", timestamp = FROM_UNIXTIME(%d)',
- $this->properties['db_metars'], $icao,
+ $this->properties['db_metars'], $icao, $time,
addslashes($metar), intval($timestamp)));
$this->insert_metar_arch($icao, $metar, $time);
}
@@ -244,12 +244,43 @@
* @return string The raw METAR as an array from the database.
* @access public
*/
- function get_metar($icao) {
- $this->query(sprintf('SELECT metar, UNIX_TIMESTAMP(timestamp)' .
- ' FROM %s WHERE icao = "%s"',
- $this->properties['db_metars'], $icao));
- return $this->fetch_row();
- }
+ function get_metar($icao, $time_from=false, $time_to=false) {
+// $this->query(sprintf('SELECT metar, UNIX_TIMESTAMP(timestamp)' .
+// ' FROM %s WHERE icao = "%s"',
+// $this->properties['db_metars'], $icao));
+// return $this->fetch_row();
+
+ if($time_from===false || $this->properties['archive_metars']===false) {
+ /* fetch the current metar */
+ $query = sprintf('SELECT metar, UNIX_TIMESTAMP(timestamp) AS timestamp, time' .
+ ' FROM %s WHERE icao = "%s" LIMIT 1',
+ $this->properties['db_metars'], $icao);
+ }
+ else if ($time_to!==false) {
+ /* fetch archived metars between $time_from and $time_to */
+ $query = sprintf('SELECT metar, UNIX_TIMESTAMP(time) AS timestamp, time' .
+ ' FROM %s WHERE icao = "%s" AND time>="%s" AND time<"%s" ORDER BY time ASC',
+ $this->properties['db_metars_arch'], $icao, $time_from, $time_to);
+ }
+ else {
+ /* fetch archived metars from $time_from */
+ $query = sprintf('SELECT metar, UNIX_TIMESTAMP(time) AS timestamp, time' .
+ ' FROM %s WHERE icao = "%s" AND time>="%s" ORDER BY time ASC',
+ $this->properties['db_metars_arch'], $icao,$time_from);
+ }
+
+ $this->query($query);
+
+ if($this->num_rows()==0) $metar_array = false;
+ else {
+ $metar_array = array();
+ while($row = $this->fetch_row()) {
+ $metar_array[] = $row;
+ }
+ }
+
+ return $metar_array;
+ }
/**
* Inserts a TAF into the database.
@@ -277,7 +308,7 @@
* @access public
* @see insert_taf()
*/
- function update_taf($icao, $taf, $timestamp) {
+ function update_taf($icao, $taf, $timestamp, $time) {
$this->query(sprintf('UPDATE %s' .
' SET taf = "%s", timestamp = FROM_UNIXTIME(%d)' .
' WHERE icao = "%s"',
@@ -345,31 +376,30 @@
$this->query('DROP TABLE IF EXISTS ' . $this->properties['db_metars']);
$this->query('CREATE TABLE ' . $this->properties['db_metars'] . '(
icao char(4) NOT NULL,
+ time timestamp(14),
metar varchar(255) NOT NULL,
timestamp timestamp(14),
- PRIMARY KEY (icao),
- UNIQUE icao (icao))');
+ PRIMARY KEY (icao)');
/* Then we make a table for the TAFs */
$this->query('DROP TABLE IF EXISTS ' . $this->properties['db_tafs']);
$this->query('CREATE TABLE ' . $this->properties['db_tafs'] . '(
icao char(4) NOT NULL,
+ time timestamp(14),
taf varchar(255) NOT NULL,
timestamp timestamp(14),
- PRIMARY KEY (icao),
- UNIQUE icao (icao))');
+ PRIMARY KEY (icao)');
/* We make the archival databases */
-
$this->query('DROP TABLE IF EXISTS ' . $this->properties['db_metars_arch']);
$this->query('CREATE TABLE ' . $this->properties['db_metars_arch'] . '(
- icao varchar(4) NOT NULL,
+ icao char(4) NOT NULL,
time timestamp(14) NOT NULL,
metar varchar(255) NOT NULL,
PRIMARY KEY (icao,time)');
$this->query('DROP TABLE IF EXISTS ' . $this->properties['db_tafs_arch']);
$this->query('CREATE TABLE ' . $this->properties['db_tafs_arch'] . '(
- icao varchar(4) NOT NULL,
+ icao char(4) NOT NULL,
time timestamp(14) NOT NULL,
taf varchar(255) NOT NULL,
PRIMARY KEY (icao,time)');
@@ -382,7 +412,6 @@
cc char(2) NOT NULL,
country varchar(128) NOT NULL,
PRIMARY KEY (icao),
- UNIQUE icao (icao),
KEY cc (cc))');
return true; // Success!
|