275 lines
7.3 KiB
PL/PgSQL
275 lines
7.3 KiB
PL/PgSQL
-- Drop all safely-rebuildable things.
|
|
|
|
DROP VIEW IF EXISTS mr_consumption;
|
|
DROP FUNCTION IF EXISTS give_reading;
|
|
DROP FUNCTION IF EXISTS take_reading;
|
|
DROP PROCEDURE IF EXISTS ensure_sane;
|
|
DROP FUNCTION IF EXISTS sanity_limit;
|
|
DROP FUNCTION IF EXISTS consumption_per_day;
|
|
DROP FUNCTION IF EXISTS fluid_unit;
|
|
DROP PROCEDURE IF EXISTS ensure_monotonic;
|
|
DROP TRIGGER IF EXISTS mr_freeze ON meter_reading;
|
|
DROP FUNCTION IF EXISTS mr_freeze;
|
|
--------------------------------------------------------------------------------
|
|
|
|
-- Rebuild them.
|
|
|
|
CREATE OR REPLACE FUNCTION mr_freeze ()
|
|
RETURNS TRIGGER
|
|
AS $$
|
|
BEGIN
|
|
IF OLD.when_entered < CURRENT_TIMESTAMP - '3 days'::INTERVAL THEN
|
|
RAISE EXCEPTION 'To change old rows, disable trigger mr_freeze.';
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;
|
|
|
|
CREATE TRIGGER mr_freeze
|
|
BEFORE UPDATE OR DELETE
|
|
ON meter_reading
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE mr_freeze();
|
|
--------------------------------------------------------------------------------
|
|
|
|
CREATE OR REPLACE PROCEDURE ensure_monotonic
|
|
( IN i_fluid fluid_t,
|
|
IN i_reading reading_d,
|
|
IN i_when_taken DATE
|
|
)
|
|
AS $$
|
|
BEGIN
|
|
PERFORM 'x' FROM meter_reading
|
|
WHERE when_taken < i_when_taken AND fluid = i_fluid AND reading > i_reading
|
|
ORDER BY when_taken DESC LIMIT 1
|
|
FOR UPDATE OF meter_reading;
|
|
IF FOUND THEN
|
|
RAISE EXCEPTION 'bad reading: an earlier reading is higher.';
|
|
END IF;
|
|
|
|
PERFORM 'x' FROM meter_reading
|
|
WHERE when_taken > i_when_taken AND fluid = i_fluid AND reading < i_reading
|
|
ORDER BY when_taken DESC LIMIT 1
|
|
FOR UPDATE OF meter_reading;
|
|
IF FOUND THEN
|
|
RAISE EXCEPTION 'bad reading: a later reading is lower.';
|
|
END IF;
|
|
RETURN;
|
|
END;
|
|
$$
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
;
|
|
--------------------------------------------------------------------------------
|
|
|
|
CREATE OR REPLACE FUNCTION fluid_unit
|
|
( IN i_fluid fluid_t
|
|
)
|
|
RETURNS unit_t
|
|
AS $$
|
|
SELECT
|
|
CASE i_fluid
|
|
WHEN 'E' THEN 'KWh'
|
|
WHEN 'G' THEN 'm³'
|
|
WHEN 'W' THEN 'm³'
|
|
ELSE NULL
|
|
END::unit_t
|
|
$$
|
|
LANGUAGE SQL
|
|
IMMUTABLE
|
|
CALLED ON NULL INPUT
|
|
SECURITY DEFINER
|
|
;
|
|
--------------------------------------------------------------------------------
|
|
|
|
CREATE OR REPLACE FUNCTION consumption_per_day
|
|
( IN i_fluid fluid_t,
|
|
IN i_reading reading_d,
|
|
IN i_when_taken DATE
|
|
)
|
|
RETURNS quantity_d
|
|
AS $$
|
|
SELECT
|
|
( i_fluid,
|
|
(i_reading - mr.reading) / (i_when_taken - mr.when_taken),
|
|
fluid_unit(i_fluid)
|
|
)::quantity_d
|
|
FROM meter_reading AS mr
|
|
WHERE mr.when_taken < i_when_taken AND mr.fluid = i_fluid
|
|
ORDER BY mr.when_taken DESC LIMIT 1
|
|
$$
|
|
LANGUAGE SQL
|
|
STABLE
|
|
CALLED ON NULL INPUT
|
|
SECURITY DEFINER
|
|
;
|
|
--------------------------------------------------------------------------------
|
|
|
|
CREATE OR REPLACE FUNCTION sanity_limit (IN i_fluid fluid_t)
|
|
RETURNS reading_d
|
|
AS $$
|
|
SELECT CASE i_fluid
|
|
WHEN 'E' THEN 6.0
|
|
WHEN 'G' THEN 6.0
|
|
ELSE 0.2 -- 'W'
|
|
END::reading_d;
|
|
$$
|
|
LANGUAGE SQL
|
|
IMMUTABLE
|
|
CALLED ON NULL INPUT
|
|
SECURITY DEFINER
|
|
;
|
|
--------------------------------------------------------------------------------
|
|
|
|
CREATE OR REPLACE PROCEDURE ensure_sane
|
|
( IN i_fluid fluid_t,
|
|
IN i_reading reading_d,
|
|
IN i_when_taken DATE
|
|
)
|
|
AS $$
|
|
DECLARE
|
|
v_qpd quantity_d := consumption_per_day(i_fluid, i_reading, i_when_taken);
|
|
BEGIN
|
|
IF i_when_taken > CURRENT_DATE THEN
|
|
RAISE EXCEPTION 'date % is in the future', i_when_taken;
|
|
ELSIF i_when_taken < CURRENT_DATE - (5 * 356) THEN
|
|
RAISE EXCEPTION 'date % is very old', i_when_taken;
|
|
ELSIF v_qpd.size > sanity_limit(i_fluid) THEN
|
|
RAISE EXCEPTION 'whoa, too high: implies % % per day.',
|
|
v_qpd.size, v_qpd.unit;
|
|
END IF;
|
|
PERFORM 'x' FROM meter_reading
|
|
WHERE when_taken = i_when_taken
|
|
AND fluid = i_fluid AND when_given IS NOT NULL
|
|
FOR UPDATE OF meter_reading;
|
|
IF FOUND THEN
|
|
RAISE EXCEPTION
|
|
'refusing to change a reading you''ve given to the supplier.';
|
|
END IF;
|
|
RETURN;
|
|
END;
|
|
$$
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
;
|
|
--------------------------------------------------------------------------------
|
|
|
|
CREATE OR REPLACE FUNCTION take_reading
|
|
( IN i_fluid fluid_t,
|
|
IN i_reading reading_d,
|
|
IN i_when_taken DATE DEFAULT CURRENT_DATE
|
|
)
|
|
RETURNS meter_reading.id%TYPE
|
|
AS $$
|
|
DECLARE
|
|
v_id meter_reading.id%TYPE;
|
|
BEGIN
|
|
IF i_fluid IS NULL OR i_reading IS NULL THEN
|
|
RAISE EXCEPTION 'bad arguments, fluid or reading missing.';
|
|
END IF;
|
|
|
|
CALL ensure_monotonic(i_fluid, i_reading, i_when_taken);
|
|
CALL ensure_sane(i_fluid, i_reading, i_when_taken);
|
|
|
|
INSERT INTO meter_reading AS mr (when_taken, fluid, reading)
|
|
VALUES (i_when_taken, i_fluid, i_reading)
|
|
ON CONFLICT (when_taken, fluid) DO UPDATE
|
|
SET reading = i_reading, when_entered = CLOCK_TIMESTAMP()
|
|
WHERE mr.when_taken = i_when_taken AND mr.fluid = i_fluid
|
|
RETURNING mr.id INTO STRICT v_id;
|
|
|
|
RETURN v_id;
|
|
END;
|
|
$$
|
|
LANGUAGE plpgsql
|
|
VOLATILE
|
|
CALLED ON NULL INPUT
|
|
SECURITY DEFINER
|
|
;
|
|
--------------------------------------------------------------------------------
|
|
|
|
CREATE OR REPLACE FUNCTION give_reading
|
|
( IN i_fluid fluid_t,
|
|
IN i_when_taken DATE,
|
|
IN i_when_given DATE DEFAULT CURRENT_DATE
|
|
)
|
|
RETURNS meter_reading.id%TYPE
|
|
AS $$
|
|
DECLARE
|
|
v_id meter_reading.id%TYPE;
|
|
v_rowcount SMALLINT;
|
|
BEGIN
|
|
IF i_fluid IS NULL OR i_when_taken IS NULL THEN
|
|
RAISE EXCEPTION 'bad arguments, fluid or when_taken missing.';
|
|
ELSIF i_when_given > CURRENT_DATE THEN
|
|
RAISE EXCEPTION 'given date % is in the future', i_when_given;
|
|
ELSIF i_when_given < i_when_taken THEN
|
|
RAISE EXCEPTION 'given date % precedes taken date %',
|
|
i_when_given, i_when_taken;
|
|
END IF;
|
|
|
|
UPDATE meter_reading
|
|
SET when_given = i_when_given
|
|
WHERE when_taken = i_when_taken AND fluid = i_fluid
|
|
RETURNING id INTO v_id;
|
|
|
|
GET DIAGNOSTICS v_rowcount := ROW_COUNT;
|
|
IF v_rowcount != 1 THEN
|
|
RAISE EXCEPTION '% rows updated for % %',
|
|
v_rowcount, i_fluid, i_when_taken;
|
|
END IF;
|
|
RETURN v_id;
|
|
END;
|
|
$$
|
|
LANGUAGE plpgsql
|
|
VOLATILE
|
|
CALLED ON NULL INPUT
|
|
SECURITY DEFINER
|
|
;
|
|
--------------------------------------------------------------------------------
|
|
|
|
CREATE OR REPLACE VIEW mr_consumption AS
|
|
WITH r0 AS (
|
|
SELECT
|
|
id,
|
|
fluid,
|
|
when_taken,
|
|
reading,
|
|
( fluid,
|
|
(reading - FIRST_VALUE(reading) OVER pred)
|
|
/
|
|
(when_taken - FIRST_VALUE(when_taken) OVER pred),
|
|
fluid_unit(fluid)
|
|
)::quantity_d
|
|
AS
|
|
daily_consumption,
|
|
when_given,
|
|
when_entered
|
|
FROM
|
|
meter_reading
|
|
WINDOW
|
|
-- Look for the reading's predecessor (if any) so as to compute
|
|
-- consumption.
|
|
pred AS (
|
|
PARTITION BY fluid
|
|
ORDER BY when_taken
|
|
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW
|
|
)
|
|
)
|
|
SELECT
|
|
r0.id, r0.fluid, r0.when_taken, r0.reading,
|
|
(r0.daily_consumption).size AS dc_size,
|
|
(r0.daily_consumption).unit AS dc_unit,
|
|
r0.when_given, r0.when_entered
|
|
FROM
|
|
r0;
|
|
--------------------------------------------------------------------------------
|
|
|
|
GRANT EXECUTE ON ROUTINE
|
|
consumption_per_day, sanity_limit,
|
|
take_reading, give_reading
|
|
TO mr_user;
|
|
|
|
GRANT SELECT ON mr_consumption TO mr_user;
|
|
--------------------------------------------------------------------------------
|