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
