Database Structure Patch

Redefine database structure for common values

Introduction

The current database structure for mysql fails as some typical value can not be stored.

• Relative pressure may not exceed 999.9 hPa.
• Wind angle may not exceed 99.9 - Nosense as 360 degrees have to be supported.
• Rain total my not exceed 999.9 mm

Description of Patch

The patch either generate a new table with extended structure.

Installation of Patch

Patch 1: Changing an existing datastructure and keep data

Open the mysql command shell and run:

CREATE TABLE `weather` (
  `timestamp` bigint(14) NOT NULL default '0',
  `rec_date` date NOT NULL default '0000-00-00',
  `rec_time` time NOT NULL default '00:00:00',
  `temp_in` decimal(3,1) NOT NULL default '0.0',
  `temp_out` decimal(3,1) NOT NULL default '0.0',
  `dewpoint` decimal(3,1) NOT NULL default '0.0',
  `rel_hum_in` tinyint(3) NOT NULL default '0',
  `rel_hum_out` tinyint(3) NOT NULL default '0',
  `windspeed` decimal(3,1) NOT NULL default '0.0',
  `wind_angle` decimal(4,1) NOT NULL default '0.0',
  `wind_direction` char(3) NOT NULL default '',
  `wind_chill` decimal(3,1) NOT NULL default '0.0',
  `rain_1h` decimal(3,1) NOT NULL default '0.0',
  `rain_24h` decimal(3,1) NOT NULL default '0.0',
  `rain_total` decimal(5,1) NOT NULL default '0.0',
  `rel_pressure` decimal(5,1) NOT NULL default '0.0',
  `tendency` varchar(7) NOT NULL default '',
  `forecast` varchar(6) NOT NULL default '',
  UNIQUE KEY `timestamp` (`timestamp`)
) TYPE=MyISAM;

You can find an appropriate sql script as an attachment.

ATTENTION: You may loose your data stored. Run a database backup with structure information before! E.g. use mysqldump

Patch 2: Alter the fields and keep the (truncated) data

Open the mysql command shell and run:

alter table weather change rel_pressure rel_pressure decimal(5,1);
alter table weather change rain_total rain_total decimal(5,1);
alter table weather change wind_angle wind_angle decimal(4,1);

Change History of Patch

-- OschenLars 05 Nov 2006

Discussion and Comments

---

Thanks for your patch.

I have changes the HTML to the simpler TWiki markup. You can see how by editing or clicking the raw view link below.

-- KennethLavrsen - 05 Nov 2006

---

I have checked in the patch on SVN. Rev 5.

-- KennethLavrsen - 05 Nov 2006

---

I updated temp_in and temp_out to decimal (4,1) because it failed to save any values above 99.9F

-- RolanYang - 10 Jul 2007

---

Corrected all temperature fields. Also windchill and dewpoint. SVN 10