You're added!
See http://www.owfs.org/index.php?page=postgres-and-python-by-jerry-scharf
Paul Alfille
On 12/17/06, Jerry Scharf <[EMAIL PROTECTED]> wrote:
Jerry Scharf wrote:
> Paul Alfille wrote:
>
>> Shall I put the whole thing (your program, explantation, and schema)
>> on the website?
>>
>> Paul Alfille
>>
Here's the schema for the program in a form to be fed into psql. All the
tables are defined to the Ruby on Rails style to allow RoR web apps to
use this with minimum effort. There is a dangling foreign key to
zone_id, so that constraint could be removed or your own zone table
could be created,
For those who are postgres wizardly, there is a duplicate of the
readings table called ow_sensor_reading_archives. There is a new feature
in postgres 8.2 that allows a copy from table to table with a select to
identify the rows. This makes a program that pulls data out of the live
reading and into the archive easy, and keeps the performance on
ow_sensor_readings acceptable.
enjoy,
jerry
CREATE TABLE sensor_types (
id SERIAL NOT NULL UNIQUE,
modified_at TIMESTAMP NOT NULL DEFAULT(CURRENT_TIMESTAMP),
name TEXT NOT NULL UNIQUE,
description TEXT,
PRIMARY KEY (id)
);
CREATE TABLE ow_sensors (
id SERIAL NOT NULL UNIQUE,
modified_at TIMESTAMP NOT NULL DEFAULT(CURRENT_TIMESTAMP),
name TEXT NOT NULL UNIQUE,
description TEXT,
active INT,
zone_id INT,
ow_id TEXT,
sensor_type_id INT,
low_limit FLOAT,
high_limit FLOAT,
calibration FLOAT, -- offset measured for the sensor
FOREIGN KEY (zone_id) REFERENCES zones(id) ON DELETE RESTRICT,
FOREIGN KEY (sensor_type_id) REFERENCES sensor_types(id) ON DELETE
RESTRICT,
PRIMARY KEY (id)
);
CREATE TABLE ow_sensor_readings (
id SERIAL NOT NULL UNIQUE,
read_at TIMESTAMP NOT NULL DEFAULT(CURRENT_TIMESTAMP),
ow_sensor_id INT,
value FLOAT,
FOREIGN KEY (ow_sensor_id) REFERENCES ow_sensors(id) ON DELETE RESTRICT,
PRIMARY KEY (id)
);
CREATE TABLE sensor_medians (
id SERIAL NOT NULL UNIQUE,
read_at TIMESTAMP NOT NULL DEFAULT(CURRENT_TIMESTAMP),
ow_sensor_id INT,
epoch INT,
value FLOAT,
FOREIGN KEY (ow_sensor_id) REFERENCES ow_sensors(id) ON DELETE RESTRICT,
PRIMARY KEY (id)
);
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share
your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Owfs-developers mailing list
Owfs-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/owfs-developers
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Owfs-developers mailing list
Owfs-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/owfs-developers
