Commits

Anonymous committed 1c1400a Draft

- add database definition

  • Participants
  • Parent commits d37e746

Comments (0)

Files changed (1)

File database/sensor.sql

+--
+-- PostgreSQL database dump
+--
+
+SET statement_timeout = 0;
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = off;
+SET check_function_bodies = false;
+SET client_min_messages = warning;
+SET escape_string_warning = off;
+
+SET search_path = public, pg_catalog;
+
+ALTER TABLE ONLY public.sensors DROP CONSTRAINT fk_station;
+ALTER TABLE ONLY public.readings DROP CONSTRAINT fk_sensor_id;
+DROP INDEX public.idx_reading_mark;
+ALTER TABLE ONLY public.sensors DROP CONSTRAINT uniq_stn_sensor;
+ALTER TABLE ONLY public.stations DROP CONSTRAINT uniq_station_name;
+ALTER TABLE ONLY public.sensors DROP CONSTRAINT uniq_sensor_name;
+ALTER TABLE ONLY public.syslog DROP CONSTRAINT pk_syslog;
+ALTER TABLE ONLY public.stations DROP CONSTRAINT pk_station;
+ALTER TABLE ONLY public.sensors DROP CONSTRAINT pk_sensor;
+ALTER TABLE ONLY public.readings DROP CONSTRAINT pk_reading;
+ALTER TABLE public.syslog ALTER COLUMN id DROP DEFAULT;
+ALTER TABLE public.readings ALTER COLUMN reading_id DROP DEFAULT;
+DROP SEQUENCE public.syslog_id_seq;
+DROP TABLE public.syslog;
+DROP TABLE public.stations;
+DROP TABLE public.sensors;
+DROP SEQUENCE public.readings_reading_id_seq;
+DROP TABLE public.readings;
+DROP PROCEDURAL LANGUAGE plpgsql;
+DROP SCHEMA public;
+--
+-- Name: public; Type: SCHEMA; Schema: -; Owner: postgres
+--
+
+CREATE SCHEMA public;
+
+
+ALTER SCHEMA public OWNER TO postgres;
+
+--
+-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
+--
+
+COMMENT ON SCHEMA public IS 'standard public schema';
+
+
+--
+-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
+--
+
+CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;
+
+
+ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;
+
+SET search_path = public, pg_catalog;
+
+SET default_tablespace = '';
+
+SET default_with_oids = false;
+
+--
+-- Name: readings; Type: TABLE; Schema: public; Owner: jdm; Tablespace: 
+--
+
+CREATE TABLE readings (
+    reading_id integer NOT NULL,
+    station_id character varying(20),
+    sensor_id character(20) NOT NULL,
+    reading_timestamp_utc timestamp without time zone NOT NULL,
+    reading_value double precision NOT NULL,
+    time_mark integer NOT NULL
+);
+
+
+ALTER TABLE public.readings OWNER TO jdm;
+
+--
+-- Name: TABLE readings; Type: COMMENT; Schema: public; Owner: jdm
+--
+
+COMMENT ON TABLE readings IS 'stores the readings. one record per time period per sensor. the sensor_id must be in the sensors table. the station_id muct be in the station table';
+
+
+--
+-- Name: readings_reading_id_seq; Type: SEQUENCE; Schema: public; Owner: jdm
+--
+
+CREATE SEQUENCE readings_reading_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+ALTER TABLE public.readings_reading_id_seq OWNER TO jdm;
+
+--
+-- Name: readings_reading_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: jdm
+--
+
+ALTER SEQUENCE readings_reading_id_seq OWNED BY readings.reading_id;
+
+
+--
+-- Name: sensors; Type: TABLE; Schema: public; Owner: jdm; Tablespace: 
+--
+
+CREATE TABLE sensors (
+    sensor_id character varying(20) NOT NULL,
+    station_id character varying(20) NOT NULL,
+    sensor_name character varying NOT NULL,
+    display_min double precision,
+    display_max double precision,
+    units character varying(10),
+    colour character varying(10),
+    pg_format character varying(20)
+);
+
+
+ALTER TABLE public.sensors OWNER TO jdm;
+
+--
+-- Name: stations; Type: TABLE; Schema: public; Owner: jdm; Tablespace: 
+--
+
+CREATE TABLE stations (
+    station_id character varying(20) NOT NULL,
+    station_name character varying NOT NULL
+);
+
+
+ALTER TABLE public.stations OWNER TO jdm;
+
+--
+-- Name: syslog; Type: TABLE; Schema: public; Owner: jdm; Tablespace: 
+--
+
+CREATE TABLE syslog (
+    id integer NOT NULL,
+    log_time timestamp without time zone NOT NULL,
+    message character varying NOT NULL
+);
+
+
+ALTER TABLE public.syslog OWNER TO jdm;
+
+--
+-- Name: syslog_id_seq; Type: SEQUENCE; Schema: public; Owner: jdm
+--
+
+CREATE SEQUENCE syslog_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+ALTER TABLE public.syslog_id_seq OWNER TO jdm;
+
+--
+-- Name: syslog_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: jdm
+--
+
+ALTER SEQUENCE syslog_id_seq OWNED BY syslog.id;
+
+
+--
+-- Name: reading_id; Type: DEFAULT; Schema: public; Owner: jdm
+--
+
+ALTER TABLE readings ALTER COLUMN reading_id SET DEFAULT nextval('readings_reading_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: public; Owner: jdm
+--
+
+ALTER TABLE syslog ALTER COLUMN id SET DEFAULT nextval('syslog_id_seq'::regclass);
+
+
+--
+-- Name: pk_reading; Type: CONSTRAINT; Schema: public; Owner: jdm; Tablespace: 
+--
+
+ALTER TABLE ONLY readings
+    ADD CONSTRAINT pk_reading PRIMARY KEY (reading_id);
+
+
+--
+-- Name: pk_sensor; Type: CONSTRAINT; Schema: public; Owner: jdm; Tablespace: 
+--
+
+ALTER TABLE ONLY sensors
+    ADD CONSTRAINT pk_sensor PRIMARY KEY (sensor_id);
+
+
+--
+-- Name: pk_station; Type: CONSTRAINT; Schema: public; Owner: jdm; Tablespace: 
+--
+
+ALTER TABLE ONLY stations
+    ADD CONSTRAINT pk_station PRIMARY KEY (station_id);
+
+
+--
+-- Name: pk_syslog; Type: CONSTRAINT; Schema: public; Owner: jdm; Tablespace: 
+--
+
+ALTER TABLE ONLY syslog
+    ADD CONSTRAINT pk_syslog PRIMARY KEY (id);
+
+
+--
+-- Name: uniq_sensor_name; Type: CONSTRAINT; Schema: public; Owner: jdm; Tablespace: 
+--
+
+ALTER TABLE ONLY sensors
+    ADD CONSTRAINT uniq_sensor_name UNIQUE (sensor_name);
+
+
+--
+-- Name: uniq_station_name; Type: CONSTRAINT; Schema: public; Owner: jdm; Tablespace: 
+--
+
+ALTER TABLE ONLY stations
+    ADD CONSTRAINT uniq_station_name UNIQUE (station_name);
+
+
+--
+-- Name: uniq_stn_sensor; Type: CONSTRAINT; Schema: public; Owner: jdm; Tablespace: 
+--
+
+ALTER TABLE ONLY sensors
+    ADD CONSTRAINT uniq_stn_sensor UNIQUE (station_id, sensor_id);
+
+
+--
+-- Name: idx_reading_mark; Type: INDEX; Schema: public; Owner: jdm; Tablespace: 
+--
+
+CREATE INDEX idx_reading_mark ON readings USING btree (time_mark);
+
+
+--
+-- Name: fk_sensor_id; Type: FK CONSTRAINT; Schema: public; Owner: jdm
+--
+
+ALTER TABLE ONLY readings
+    ADD CONSTRAINT fk_sensor_id FOREIGN KEY (station_id, sensor_id) REFERENCES sensors(station_id, sensor_id);
+
+
+--
+-- Name: fk_station; Type: FK CONSTRAINT; Schema: public; Owner: jdm
+--
+
+ALTER TABLE ONLY sensors
+    ADD CONSTRAINT fk_station FOREIGN KEY (station_id) REFERENCES stations(station_id) ON UPDATE CASCADE;
+
+
+--
+-- Name: public; Type: ACL; Schema: -; Owner: postgres
+--
+
+REVOKE ALL ON SCHEMA public FROM PUBLIC;
+REVOKE ALL ON SCHEMA public FROM postgres;
+GRANT ALL ON SCHEMA public TO postgres;
+GRANT ALL ON SCHEMA public TO PUBLIC;
+
+
+--
+-- PostgreSQL database dump complete
+--
+