weatherdata/sql/database.sql

-- weatherdata database setup

CREATE TABLE IF NOT EXISTS data (
	timestamp	timestamp with time zone PRIMARY KEY,
	temp		double precision,
	humidity	double precision,
	pressure	double precision,
	battery		double precision
);

-- update data table from previous versions
DO $$
	BEGIN
		BEGIN
			ALTER TABLE "data" ADD COLUMN "pressure"
			    double precision;
		EXCEPTION WHEN duplicate_column THEN
			RAISE NOTICE
			    'column "pressure" already exists in "data".';
		END;
	END;
$$;

DO $$
	BEGIN
		BEGIN
			ALTER TABLE "data" ADD COLUMN "battery"
			    double precision;
		EXCEPTION WHEN duplicate_column THEN
			RAISE NOTICE
			    'column "battery" already exists in "data".';
		END;
	END;
$$;

-- aggregate error messages for some time in the db
CREATE TABLE IF NOT EXISTS errors (
	device		varchar(36),
	timestamp	timestamp with time zone DEFAULT NOW() NOT NULL,
	error		text NOT NULL,
	PRIMARY KEY (device, timestamp)
);