Open2300 - Database Structure Patch
You are here: Foswiki>Open2300 Web>DatabaseStructurePatch (14 Apr 2009, JanNoorlandt)Edit Attach

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
Topic revision: r5 - 14 Apr 2009, JanNoorlandt
Copyright © 1999-2018 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
This website only use harmless session cookies. See Cookie Policy for details. By using this website you accept the use of these cookies.