DatabaseSchema

Database Schema

Five tables are used to record the thermostats' settings, comfort levels, hot water timers, temperature history, and interesting events. A sixth table is used to record external temperature readings from an online weather service.

Table settings

The settings table records basic information about the thermostats and their configuration:

Field	Type	Description
thermostat	varchar(255)	Identifier for the thermostat (currently the IP address or hostname)
name	varchar(20)	Key used to identify the setting
value	varchar(255)	Value of the setting as text

The following settings are currently stored:

Name	Description
host	IP address or hostname used to communicate with the thermostat
vendor	Thermostat manufacturer (Heatmiser or OEM)
version	Thermostat version number (#.#)
model	Thermostat model (DT, DT-E, PRT, PRT-E, PRTHW, or TM1)
heating	Heating operating mode (off, frost, heating or n/a)
hotwater	Hot water operating mode (off, hotwater or n/a)
units	Temperature units (C or F)
holiday	End of holiday (YYYY-MM-DD hh:mm:ss) or zero length string if not enabled
progmode	Thermostat program mode (5/2 or 7)

Table comfort

The comfort table records the programmed comfort levels used to control heating:

Field	Type	Description
thermostat	varchar(255)	Identifier for the thermostat (currently the IP address or hostname)
day	tinyint(1)	Day number (0/1 for Weekday/Weekend or 0...6 for Monday...Sunday)
entry	tinyint(1)	Program index within the day (0...3)
time	time	Start time for the comfort level (hh:mm:00)
target	tinyint(2)	Comfort level temperature (##)

Only valid entries are stored, so there may be fewer than four indexes specified for each day.

Table timer

The timer table records the timer programs used to control hot water:

Field	Type	Description
thermostat	varchar(255)	Identifier for the thermostat (currently the IP address or hostname)
day	tinyint(1)	Day number (0/1 for Weekday/Weekend or 0...6 for Monday...Sunday)
entry	tinyint(1)	Program index within the day (0...3)
timeon	time	Start time for the hot water (hh:mm:00)
timeoff	time	End time for the hot water (hh:mm:00)

Only valid entries are stored, so there may be fewer than four indexes specified for each day.

Table temperatures

The temperatures table logs the current and target temperatures:

Field	Type	Description
thermostat	varchar(255)	Identifier for the thermostat (currently the IP address or hostname)
time	datetime	The date and time (YYYY-MM-DD hh:mm:ss)
air	decimal(3,1)	The current air temperature (##.#)
target	tinyint(2)	The target temperature (##) or 0 if heating is disabled
comfort	tinyint(2)	The current comfort level (##)

Table events

The events table logs interesting events, such as the heating or hot water switching on or off, or the target temperature being changed:

Field	Type	Description
thermostat	varchar(255)	Identifier for the thermostat (currently the IP address or hostname)
time	datetime	The date and time (YYYY-MM-DD hh:mm:ss)
class	varchar(20)	The class of event (heating, target or hotwater)
state	varchar(20)	Textual description of the status
temperature	tinyint(2)	Target heating temperature (##) or hot water status (0 or 1)

The details of the information recorded for each event depend on the class:

Class	State	Temperature
heating	Heating off (0) or on (1)	n/a (NULL)
target	Cause (off, holiday, away, comfortlevel, optimumstart, hold or manual)	Target heating temperature (##)
hotwater	Cause (off, holiday, away, timer, boost or override)	Hot water off (0) or on (1)

Table weather

The weather table logs external temperatures from an online weather service:

Field	Type	Description
time	datetime	The date and time (YYYY-MM-DD hh:mm:ss)
external	decimal(3,1)	The current external temperature (##.#)