[Firebug-cvs] fireboard/beta/bin firebug-db-setup.sh,NONE,1.1 firebug.sql,NONE,1.1

[David M. D. <do...@us...>]

Update of /cvsroot/firebug/fireboard/beta/bin
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv25656

Added Files:
	firebug-db-setup.sh firebug.sql 
Log Message:
Start of psql scripts.

--- NEW FILE: firebug-db-setup.sh ---
#!/bin/bash
#
# FILE:         createdb-task
# AUTHOR:       Martin Turon
# DATE CREATED: July 12, 2004
# DESCRIPTION:  PostgreSQL start script
#
# $Id: firebug-db-setup.sh,v 1.1 2005/06/17 23:11:29 doolin Exp $
#

echo "Creating Postgres FireBug database."

#setup-task-db.sh
# --> Corrected script enclosed here

###  IPC Daemon is obsolete 
###  TODO: Check whether postmaster is running.
# start ipc-daemon
#echo "Setting up PostgreSQL database for TASK.  Only need to do this once."
#if cygpath / > /dev/null 2>/dev/null; then
#	ipc-daemon &
#	export PGDATA=/pgdata
#fi

# Create database only if it wasn't created before
#if [ ! -d /pgdata ]; then
#	initdb
#	sed -e 's/#tcpip_socket = false/tcpip_socket = true/' /pgdata/postgresql.conf > /tmp/postgresql.conf
#	mv /tmp/postgresql.conf /pgdata
#fi


### pg_ctl is not on the debian box.
#pg_ctl start -l /tmp/postgresql.log
# wait for postmaster to start up
#sleep 5
#psql -c "drop user tele;" template1
#psql -c "create user tele password 'tiny' createdb createuser;" template1

####  assumes firebug owner is valid, password prompt is not working.
createdb --username firebug --password firebug --owner firebug
#psql --echo-queries task tele < task.sql

# Add MoteView client to db
#echo "Adding MoteView as client."
#psql -c "DELETE FROM task_client_info where name='MoteView';" task
#psql -c "INSERT INTO task_client_info (name, type, clientinfo) VALUES ('MoteView', 'CONFIGURATION', '');" task
#psql -c "INSERT INTO task_mote_info (mote_id, x_coord, y_coord, z_coord, calib, moteinfo, clientinfo_name) VALUES (0, 10, 10, 0, '', 'Gateway', 'MoteView');" task

# Finalize db creation with xlisten tables
#echo "Creating xlisten tables for XSensor."
#psql -c "CREATE TABLE xtest_results ( result_time timestamp without time zone, epoch integer, nodeid integer, parent integer, voltage integer, light integer);" task
#psql -c "CREATE TABLE xtest_results_L ( result_time timestamp without time zone, epoch integer, nodeid integer, parent integer, voltage integer, light integer);" task
#psql -c "CREATE RULE xtest_results AS ON INSERT TO xtest_results DO ( DELETE FROM xtest_results_L WHERE nodeid = NEW.nodeid; INSERT INTO xtest_results_L VALUES ( NEW.* ); );" task
#psql -c "CREATE INDEX xtest_time_idx ON xtest_results USING BTREE ( result_time ) ;" task
#psql -c "DELETE FROM task_query_time_log where query_id=8;" task
#psql -c "DELETE FROM task_query_log where query_id=8;" task
#psql -c "INSERT INTO task_query_log (query_id, tinydb_qid, query_text, query_type, table_name) VALUES (8, 8, 'SELECT nodeid,parent,light,voltage SAMPLE PERIOD 1000', 'sensor', 'xtest_results');" task
#psql -c "INSERT INTO task_query_time_log (query_id, start_time) VALUES (8, now());" task


# Add MEP401/MEP500 enviro Tables:
#echo "Creating xlisten tables for MEP401 / MEP500."
#psql -c "INSERT INTO task_attributes (name, typeid, power_cons, description) VALUES ('inthum', 3, 1, 'Sensirion internal humidity reading') ;" task
#psql -c "INSERT INTO task_attributes (name, typeid, power_cons, description) VALUES ('inttemp', 3, 1, 'Sensirion internal temperature reading') ;" task
#psql -c "INSERT INTO task_attributes (name, typeid, power_cons, description) VALUES ('therm', 3, 1, 'Mica2dot thermistor reading') ;" task

#psql -c "CREATE TABLE enviro_results ( result_time timestamp without time zone, epoch integer, nodeid integer, parent integer, voltage integer, therm integer, humid integer, humtemp integer, inthum integer, inttemp integer, photo1 integer, photo2 integer, photo3 integer, photo4 integer, accel_x integer, accel_y integer, prtemp integer, press integer );" task
#psql -c "CREATE TABLE enviro_results_L ( result_time timestamp without time zone, epoch integer, nodeid integer, parent integer, voltage integer, therm integer, humid integer, humtemp integer, inthum integer, inttemp integer, photo1 integer, photo2 integer, photo3 integer, photo4 integer, accel_x integer, accel_y integer, prtemp integer, press integer );" task

#psql -c "CREATE RULE cache_enviro_results AS ON INSERT TO enviro_results DO ( DELETE FROM enviro_results_L WHERE nodeid = NEW.nodeid; INSERT INTO enviro_results_L VALUES ( NEW.* ); );" task
#psql -c "CREATE INDEX enviro_time_idx ON enviro_results USING BTREE ( result_time ) ;" task

#psql -c "INSERT INTO task_query_log (query_id, tinydb_qid, query_text, query_type, table_name) VALUES (10, 10, 'SELECT nodeid,parent,voltage,therm,humid,humtemp,inthum,inttemp,photo1,photo2,photo3,photo4,accel_x,accel_y,prtemp,press SAMPLE PERIOD 3000', 'sensor', 'enviro_results');" task
#psql -c "INSERT INTO task_query_time_log (query_id, start_time) VALUES (10, now()) ;" task

# Add MTS300/310 Tables:
#echo "Creating tables for MTS300/310."
#psql -c "CREATE TABLE mts310_results ( result_time timestamp without time zone, epoch integer, nodeid integer, parent integer, voltage integer, temp integer, light integer, accel_x integer, accel_y integer, mag_x integer, mag_y integer );" task
#psql -c "CREATE TABLE mts310_results_L ( result_time timestamp without time zone, epoch integer, nodeid integer, parent integer, voltage integer, temp integer, light integer, accel_x integer, accel_y integer, mag_x integer, mag_y integer );" task
#psql -c "CREATE RULE cache_mts310_results AS ON INSERT TO mts310_results DO ( DELETE FROM mts310_results_L WHERE nodeid = NEW.nodeid; INSERT INTO mts310_results_L VALUES ( NEW.* ); );" task
#psql -c "CREATE INDEX mts310_time_idx ON mts310_results USING BTREE ( result_time ) ;" task
#psql -c "INSERT INTO task_query_log (query_id, tinydb_qid, query_text, query_type, table_name) VALUES (12, 12, 'SELECT nodeid,parent,voltage,temp,light,accel_x,accel_y,mag_x,mag_y SAMPLE PERIOD 3000', 'sensor', 'mts310_results');" task
#psql -c "INSERT INTO task_query_time_log (query_id, start_time) VALUES (12, now()) ;" task


# Add MDA300 Plant App Tables:
#echo "Creating tables for MDA300."
#psql -c "CREATE TABLE mda300_results ( result_time timestamp without time zone, epoch integer, nodeid integer, parent integer, voltage integer, humid integer, humtemp integer, echo10 integer, echo20 integer, soiltemp integer );" task
#psql -c "CREATE TABLE mda300_results_L ( result_time timestamp without time zone, epoch integer, nodeid integer, parent integer, voltage integer, humid integer, humtemp integer, echo10 integer, echo20 integer, soiltemp integer );" task
#psql -c "CREATE RULE cache_mda300_results AS ON INSERT TO mda300_results DO ( DELETE FROM mda300_results_L WHERE nodeid = NEW.nodeid; INSERT INTO mda300_results_L VALUES ( NEW.* ); );" task
#psql -c "CREATE INDEX mda300_time_idx ON mda300_results USING BTREE ( result_time ) ;" task
#psql -c "INSERT INTO task_query_log (query_id, tinydb_qid, query_text, query_type, table_name) VALUES (13, 13, 'SELECT nodeid,parent,voltage,humid,humtemp,echo10,echo20,soiltemp SAMPLE PERIOD 6000', 'sensor', 'mda300_results');" task
#psql -c "INSERT INTO task_query_time_log (query_id, start_time) VALUES (13, now()) ;" task

#psql -c "INSERT INTO task_attributes (name, typeid, power_cons, description) VALUES ('echo10', 3, 1, 'Echo10 soil moisture probe') ;" task
#psql -c "INSERT INTO task_attributes (name, typeid, power_cons, description) VALUES ('echo20', 3, 1, 'Echo20 soil moisture probe') ;" task
#psql -c "INSERT INTO task_attributes (name, typeid, power_cons, description) VALUES ('soiltemp', 3, 1, 'Davis soil temperature probe') ;" task


# Finalize db creation with xlisten tables
#echo "Creating tables for Surge."
#psql -c "CREATE TABLE surge_results ( result_time timestamp without time zone, epoch integer, nodeid integer, parent integer, light integer, temp integer, voltage integer, mag_x integer, mag_y integer, accel_x integer, accel_y integer);" task
#psql -c "CREATE TABLE surge_results_L ( result_time timestamp without time zone, epoch integer, nodeid integer, parent integer, light integer, temp integer, voltage integer, mag_x integer, mag_y integer, accel_x integer, accel_y integer);" task
#psql -c "CREATE RULE cache_surge_results AS ON INSERT TO surge_results DO ( DELETE FROM surge_results_L WHERE nodeid = NEW.nodeid; INSERT INTO surge_results_L VALUES ( NEW.* ); );" task
#psql -c "CREATE INDEX surge_time_idx ON surge_results USING BTREE ( result_time ) ;" task
#psql -c "DELETE FROM task_query_time_log where query_id=7;" task
#psql -c "DELETE FROM task_query_log where query_id=7;" task
#psql -c "INSERT INTO task_query_log (query_id, tinydb_qid, query_text, query_type, table_name) VALUES (7, 7, 'SELECT nodeid,parent,light,temp,voltage,mag_x,mag_y,accel_x,accel_y SAMPLE PERIOD 3000', 'sensor', 'surge_results');" task
#psql -c "INSERT INTO task_query_time_log (query_id, start_time) VALUES (7, now());" task






--- NEW FILE: firebug.sql ---
-- SQL for creating tables to initialize a GSK database.
-- This script is platform specific.  It uses some PostgreSQL specific
-- data types (e.g., bytea).

-- task_query_log is a complete log of all queries submitted to 
-- the sensor network
drop table task_query_log cascade;
create table task_query_log (
	query_id int primary key,	/* unique id for queries */
	tinydb_qid smallint,		/* TinyDB query id, 1-byte */
	query_text text not null,	/* query text in TinyDB SQL */
	query_type varchar(20) not null, /* query type: sensor or health */
	table_name varchar(100) not null unique /* name of table where the query results are logged */
	);

-- task_query_time_log records the query start and stop times
drop table task_query_time_log;
create table task_query_time_log (
	query_id int references task_query_log,	/* query id */
	start_time timestamp not null,			/* query start time */
	stop_time timestamp, 					/* query stop time */
	primary key(query_id, start_time)
	);

-- task_command_log is a complete log of all commands submitted to
-- the sensor network
drop table task_command_log cascade;
create table task_command_log (
	command_id	int primary key, /* unique command id */
	submit_time timestamp, /* command submission time */
	command_name varchar(100), /* command name, e.g., reset, sample_rate, ping, calibrate, etc. */
	node_id int, /* target node id, -1 means all nodes */
	query_id int, /* query id the command is targeting, -1 means all queries */
	command_arg int /* single integer argument value, will generalize later */
	);

-- task_command_acks records the acknowledgements from each node in reponse to
-- a command issued from the server.
drop table task_command_acks;
create table task_command_acks (
	command_id	int references task_command_log,
	ack_time	timestamp, /* timestamp when ack comes in */
	node_id		int, /* node id from which the ack is sent */
	query_id	int, /* query id if command is targeting a particular query */
	epoch		int, /* last epoch number for the query */
	sample_rate int /* all acks include the current sample rate in seconds */
	);

-- task_packet_log is a complete log of all raw packets received from 
-- the sensor network.
-- There will also be a per-query table (specified by task_query_log.table_name)
-- logging the query result tuples.
drop table task_packet_log;
create table task_packet_log (
	query_id int references task_query_log, /* id of query that generated the packet */
	epoch int, 		/* epoch number */
	server_time timestamp, /* time received at server */
	mote_time timestamp, 	/* logical time stamped by mote */
	mote_id int, 	/* id of mote that generated the packet */
	raw_packet bytea /* raw packet bytes */
	);

-- task_health_log is a complete log of network health and statistics
-- that can be collected from the sensor network.  It includes all
-- possible network health and statistics related attributes.  Each
-- health query will only collect a subset of these attributes.  The
-- attributes that are not included in the query will be filled with
-- NULL values.
drop table task_health_log;
create table task_health_log (
	query_id int references task_query_log,	/* query id */
	epoch int, 	/* epoch number */
	server_time timestamp, /* time received at server */
	mote_time timestamp, 	/* logical time stamped by mote */
	mote_id int, 			/* mote id */
	parent int, 			/* parent id in routing tree */
	voltage int,			/* battery voltage */
	contention int			/* radio contention */
	/* The exact list of health&stat attributes is yet to be finalized. */
	);

-- task_last_query_id implements a persistent counter for generating
-- unique GSK query ids
drop table task_next_query_id;
create table task_next_query_id (
	query_id int, 	/* next query id */
	tinydb_qid smallint, /* next tinydb query id, 1 byte */
	command_id int /* next command id */
	);
-- query 0 is reserved for the calibration query
insert into task_next_query_id values (1, 1, 0);

-- task_current_results is a view into queryN_results table 
-- for the last query executed.
-- initialized as task_query_log as a placeholder
drop view task_current_results;
create view task_current_results as select * from task_query_log;

-- task_attributes contains information about all the attributes
-- that can be queries from the sensor network
drop table task_attributes;
create table task_attributes (
	name varchar(8), 	/* attribute name, limited to 8 characters */
	typeid int, 		/* type of attribute */
	power_cons int, /* per sample power consumption rate */
	description varchar(1000) /* description of the attribute */
	);
insert into task_attributes values ('nodeid', 3, 1, 'node id');
insert into task_attributes values ('light', 3, 1, 'light sensor reading');
insert into task_attributes values ('temp', 3, 1, 'temperature sensor reading');
insert into task_attributes values ('parent', 3, 1, 'parent node id in routing tree');
insert into task_attributes values ('accel_x', 3, 1, 'accelerometer reading in x axis');
insert into task_attributes values ('accel_y', 3, 1, 'accelerometer reading in y axis');
insert into task_attributes values ('mag_x', 3, 1, 'magnetometer reading in x axis');
insert into task_attributes values ('mag_y', 3, 1, 'magnetometer reading in y axis');
insert into task_attributes values ('noise', 3, 1, 'aggregated microphone readings');
insert into task_attributes values ('tones', 3, 1, 'aggregated number of tones detected');
insert into task_attributes values ('voltage', 3, 1, 'battery voltage level');
insert into task_attributes values ('rawtone', 3, 1, 'raw tone detector output: 1 detected 0 otherwise');
insert into task_attributes values ('rawmic', 3, 1, 'raw microphone reading');
insert into task_attributes values ('freeram', 3, 1, 'amount of RAM available in bytes');
insert into task_attributes values ('qlen', 1, 1, 'global send queue length');
insert into task_attributes values ('mhqlen', 1, 1, 'multi-hop forward queue length');
insert into task_attributes values ('depth', 1, 1, 'multi-hop depth');
insert into task_attributes values ('timelo', 4, 1, 'low 32-bit of mote logical time');
insert into task_attributes values ('timehi', 4, 1, 'high 32-bit of mote logical time');
insert into task_attributes values ('qual', 1, 1, 'quality of multi-hop parent');
insert into task_attributes values ('humid', 3, 1, 'Senirion Humidity sensor humidity reading');
insert into task_attributes values ('humtemp', 3, 1, 'Senirion Humidity sensor temperature reading');
insert into task_attributes values ('echo10', 3, 1, 'Echo10 soil moisture sensor temperature reading');
insert into task_attributes values ('taosbot', 3, 1, 'Bottom Taos Photo sensor reading');
insert into task_attributes values ('taostop', 3, 1, 'Top Taos Photo sensor reading');
insert into task_attributes values ('press', 3, 1, 'Intersema Pressure sensor pressure reading');
insert into task_attributes values ('prtemp', 3, 1, 'Intersema Pressure sensor temperature reading');
insert into task_attributes values ('prcalib', 9, 1, 'Intersema Pressure sensor calibration reading');
insert into task_attributes values ('hamatop', 3, 1, 'Top Hamamatsu light sensor reading');
insert into task_attributes values ('hamabot', 3, 1, 'Bottom Hamamatsu light sensor reading');
insert into task_attributes values ('thermo', 3, 1, 'Melexis sensor thermopile reading');
insert into task_attributes values ('thmtemp', 3, 1, 'Melexis sensor temperature reading');
-- insert into task_attributes values ('content', 3, 1, 'radio contention');

-- task_aggregates contains information about all aggregates that
-- are supported by GSK
drop table task_aggregates;
create table task_aggregates (
	name varchar(32), 	/* name of aggregate */
	return_type int, 	/* return type of aggregate */
	num_args int, 		/* number of arguments */
	arg_type int, 		/* type of the non-constant argument */
	description varchar(1000)	/* description of the aggregate */
	);
insert into task_aggregates values ('winavg', 3, 3, 3, 'temporal windowed average');
insert into task_aggregates values ('winsum', 3, 3, 3, 'temporal windowed sum');
insert into task_aggregates values ('winmin', 3, 3, 3, 'temporal windowed minimum');
insert into task_aggregates values ('winmax', 3, 3, 3, 'temporal windowed maximum');
insert into task_aggregates values ('wincnt', 3, 3, 3, 'temporal windowed count');

-- task_commands contains information about all sensor network 
-- commands supported by GSK
drop table task_commands;
create table task_commands (
	name varchar(8), 	/* name of command */
	return_type int, 	/* return type of the command */
	num_args int, 		/* number of arguments */
	arg_types int[], 	/* argument types */
	description varchar(1000)	/* brief description */
	);
insert into task_commands values ('SetLedR', 9, 1, '{1}', 'SetLedR(0) turns off the red LED, SetLedR(1) turns on the red LED, SetLedR(2) toggles the red LED');
insert into task_commands values ('SetLedY', 9, 1, '{1}', 'SetLedY(0) turns off the yellow LED, SetLedY(1) turns on the yellow LED, SetLedY(2) toggles the yellow LED');
insert into task_commands values ('SetLedG', 9, 1, '{1}', 'SetLedG(0) turns off the green LED, SetLedG(1) turns on the green LED, SetLedG(2) toggles the green LED');
insert into task_commands values ('SetPot', 9, 1, '{1}', 'set potentiometer level');
insert into task_commands values ('Reset', 9, 0, '{}', 'reboot mote');
insert into task_commands values ('LogClr', 9, 0, '{}', 'clear tuple log in EEPROM');
-- insert into task_commands values ('SetSnd', 9, 1, '{2}', 'turn on sounder for n milliseconds');

-- task_client_info contains opaque information about task clients
-- such as layouts.
drop table task_client_info cascade;
create table task_client_info (
	name varchar(100) primary key, 	/* name of client info */
	type varchar(100),	/* java type of client info */
	clientinfo bytea	/* opaque client info object */
	);

-- task_mote_info contains opaque per-mote client information relative to
-- a particular client info.
drop table task_mote_info;
create table task_mote_info (
	mote_id int,	/* mote id */
	x_coord	double precision, /* x coordinate */
	y_coord	double precision, /* y coordinate */
	z_coord	double precision, /* z coordinate */
	calib 	bytea, /* calibration coefficiences, raw bytes from motes */ 
	moteinfo bytea,	/* opaque mote info object */
	clientinfo_name varchar(100) references task_client_info, /* client info name */
	primary key (mote_id, clientinfo_name)
	);

create or replace function humidity(int /* raw humidity */) returns real
	as 'select (-4 + 0.0405 * $1 + -2.8e-6 * $1 * $1)::real as result;'
	language sql;

create or replace function humid_temp(int /* raw temperature */) returns real
	as 'select (-38.4 + 0.0098 * $1)::real as result;'
	language sql;

create or replace function humid_adj(int /* raw humidity */, int /* raw temperature */) returns real
	as 'select ((humid_temp($2) - 25) * (0.01 + 0.00008 * $1) + humidity($1))::real as result;'
	language sql;

CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler
    AS '$libdir/plpgsql', 'plpgsql_call_handler'
    LANGUAGE c;


CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;

create or replace function photo(int /* channel 0 */, int /* channel 1 */) returns real
	as 'declare
			s0 int;
			cc0 int;
			s1 int;
			cc1 int;
			adccount0 int;
			adccount1 int;
			lux real := 0;
		begin
			s0 := $1 & 15;
			cc0 := (($1 & 127) >> 4) & 7;
			s1 := $2 & 15;
			cc1 := (($2 & 127) >> 4) & 7;
			-- check if we are railed
			if s0 = 15 and s1 = 15 and cc0 = 7 and cc1 = 7
			then return null;
			end if;

			adccount0 := (16.5 * (2^cc0 - 1))::int + (s0 * 2^cc0)::int;
			adccount1 := (16.5 * (2^cc1 - 1))::int + (s1 * 2^cc1)::int;

			if adccount0 = 0 then return null; end if;
			if (adccount1 / adccount0) > 27 then return 0.0; end if;
			lux := adccount0 * 0.46 * exp(-3.13 * (adccount1 / adccount0));
			return lux;
		end;'
	language 'plpgsql';

create or replace function thermopile(int /* raw thermopile */) returns real
	as 'select ((($1 >> 4) * (120 - (-20))) / (2^12 - 1) + (-20))::real as result;'
	language sql;

create or replace function thermistor(int /* raw temperature */) returns real
	as 'select ((($1 >> 4) * (85 - (-20))) / (2^12 - 1) + (-20))::real as result;'
	language sql;

create or replace function voltage(int /* raw voltage */) returns real
	as 'select (case when $1 = 0 then 0 else 0.58 * 1024 / $1 end)::real as result;'
	language sql;

create or replace function hamamatsu_current(int /* raw hamamatsu */, int /* raw voltage */) returns real
	as 'select (($1 * voltage($2) / 1024.0) / 5.0)::real as result;'
	language sql;

create or replace function hamamatsu(int /* raw hamamatsu */, int /* raw voltage */) returns real
	as 'select (hamamatsu_current($1, $2) * 1e6)::real as result;'
	language sql;

create or replace function pressure_mbar(int /* raw pressure */, int /* raw temperature */, bytea /* calibration */) returns real
	as 'declare
			c1 int; c2 int; c3 int; c4 int; c5 int; c6 int;
			calib0 int; calib1 int; calib2 int; calib3 int;
			ut1 int;
			dt int;
			temp real;
			off real;
			sens real;
			x real;
			p real;
		begin
			-- parse calibration coefficiences out of byte array
			calib0 := get_byte($3, 0) & 255;
			calib0 := calib0 | (((get_byte($3, 1) & 255) << 8) & 65280);
			calib1 := get_byte($3, 2) & 255;
			calib1 := calib1 | (((get_byte($3, 3) & 255) << 8) & 65280);
			calib2 := get_byte($3, 4) & 255;
			calib2 := calib2 | (((get_byte($3, 5) & 255) << 8) & 65280);
			calib3 := get_byte($3, 6) & 255;
			calib3 := calib3 | (((get_byte($3, 7) & 255) << 8) & 65280);

			c1 := calib0 >> 1;
			c2 := calib2 & 63;
			c2 := c2 << 6;
			c2 := c2 | (calib3 & 63);
			c3 := calib3 >> 6;
			c4 := calib2 >> 6;
			c5 := calib0 << 10;
			c5 := c5 & 1024;
			c5 := c5 + (calib1 >> 6);
			c6 := calib1 & 63;

			ut1 := 8 * c5 + 20224;
			dt := $2 - ut1;
			temp := 200 + dt * (c6 + 50) / 1024;
			off := c2 * 4 + ((c4 - 512) * dt) / 4096;
			sens := c1 + (c3 * dt) / 1024 + 24576;
			x := (sens * ($1 - 7168)) / 16384 - off; 
			p := x * 100 / 32 + 250 * 100;
			return p / 100;
		end;'
	language 'plpgsql';

create or replace function pressure_inHg(int /* raw pressure */, int /* raw temperature */, bytea /* calibration */) returns real
	as 'declare
			c1 int; c2 int; c3 int; c4 int; c5 int; c6 int;
			calib0 int; calib1 int; calib2 int; calib3 int;
			ut1 int;
			dt int;
			off real;
			sens real;
			x real;
			p real;
		begin
			-- parse calibration coefficiences out of byte array
			calib0 := get_byte($3, 0) & 255;
			calib0 := calib0 | (((get_byte($3, 1) & 255) << 8) & 65280);
			calib1 := get_byte($3, 2) & 255;
			calib1 := calib1 | (((get_byte($3, 3) & 255) << 8) & 65280);
			calib2 := get_byte($3, 4) & 255;
			calib2 := calib2 | (((get_byte($3, 5) & 255) << 8) & 65280);
			calib3 := get_byte($3, 6) & 255;
			calib3 := calib3 | (((get_byte($3, 7) & 255) << 8) & 65280);

			c1 := calib0 >> 1;
			c2 := calib2 & 63;
			c2 := c2 << 6;
			c2 := c2 | (calib3 & 63);
			c3 := calib3 >> 6;
			c4 := calib2 >> 6;
			c5 := calib0 << 10;
			c5 := c5 & 1024;
			c5 := c5 + (calib1 >> 6);
			c6 := calib1 & 63;

			ut1 := 8 * c5 + 20224;
			dt := $2 - ut1;
			off := c2 * 4 + ((c4 - 512) * dt) / 4096;
			sens := c1 + (c3 * dt) / 1024 + 24576;
			x := (sens * ($1 - 7168)) / 16384 - off; 
			p := x * 100 / 32 + 250 * 100;
			return p / (100 * 33.864);
		end;'
	language 'plpgsql';

create or replace function pressure_temp(int /* raw temperature */, bytea /* calibration */) returns real
	as 'declare
			c1 int; c2 int; c3 int; c4 int; c5 int; c6 int;
			calib0 int; calib1 int; calib2 int; calib3 int;
			ut1 int;
			dt int;
			temp real;
		begin
			-- parse calibration coefficiences out of byte array
			calib0 := get_byte($2, 0) & 255;
			calib0 := calib0 | (((get_byte($2, 1) & 255) << 8) & 65280);
			calib1 := get_byte($2, 2) & 255;
			calib1 := calib1 | (((get_byte($2, 3) & 255) << 8) & 65280);
			calib2 := get_byte($2, 4) & 255;
			calib2 := calib2 | (((get_byte($2, 5) & 255) << 8) & 65280);
			calib3 := get_byte($2, 6) & 255;
			calib3 := calib3 | (((get_byte($2, 7) & 255) << 8) & 65280);

			c1 := calib0 >> 1;
			c2 := calib2 & 63;
			c2 := c2 << 6;
			c2 := c2 | (calib3 & 63);
			c3 := calib3 >> 6;
			c4 := calib2 >> 6;
			c5 := calib0 << 10;
			c5 := c5 & 1024;
			c5 := c5 + (calib1 >> 6);
			c6 := calib1 & 63;

			ut1 := 8 * c5 + 20224;
			dt := $1 - ut1;
			temp := 200 + dt * (c6 + 50) / 1024;
			return temp / 10;
		end;'
	language 'plpgsql';