3 SELECT evergreen.upgrade_deps_block_check('1106', :eg_version);
5 ALTER TABLE money.billing
6 ADD COLUMN create_date TIMESTAMP WITH TIME ZONE,
7 ADD COLUMN period_start TIMESTAMP WITH TIME ZONE,
8 ADD COLUMN period_end TIMESTAMP WITH TIME ZONE;
10 --Disable materialized update trigger
11 --It takes forever, and doesn't matter yet for what we are doing, as the
12 --view definition is unchanged (still using billing_ts)
13 ALTER TABLE money.billing DISABLE TRIGGER mat_summary_upd_tgr;
15 --Limit to btype=1 / 'Overdue Materials'
16 --Update day-granular fines first (i.e. 24 hour, 1 day, 2 day, etc., all of which are multiples of 86400 seconds), and simply remove the time portion of timestamp
17 UPDATE money.billing mb
18 SET period_start = date_trunc('day', billing_ts), period_end = date_trunc('day', billing_ts) + (ac.fine_interval - '1 second')
19 FROM action.circulation ac
22 AND (EXTRACT(EPOCH FROM ac.fine_interval))::integer % 86400 = 0;
24 --Update fines for non-day intervals
25 UPDATE money.billing mb
26 SET period_start = billing_ts - ac.fine_interval + interval '1 sec', period_end = billing_ts
27 FROM action.circulation ac
30 AND (EXTRACT(EPOCH FROM ac.fine_interval))::integer % 86400 > 0;
32 SET CONSTRAINTS ALL IMMEDIATE;
33 UPDATE money.billing SET create_date = COALESCE(period_start, billing_ts);
35 --Re-enable update trigger
36 ALTER TABLE money.billing ENABLE TRIGGER mat_summary_upd_tgr;
38 ALTER TABLE money.billing ALTER COLUMN create_date SET DEFAULT NOW();
39 ALTER TABLE money.billing ALTER COLUMN create_date SET NOT NULL;
41 CREATE INDEX m_b_create_date_idx ON money.billing (create_date);
42 CREATE INDEX m_b_period_start_idx ON money.billing (period_start);
43 CREATE INDEX m_b_period_end_idx ON money.billing (period_end);
45 CREATE OR REPLACE FUNCTION money.maintain_billing_ts () RETURNS TRIGGER AS $$
47 NEW.billing_ts := COALESCE(NEW.period_end, NEW.create_date);
51 CREATE TRIGGER maintain_billing_ts_tgr BEFORE INSERT OR UPDATE ON money.billing FOR EACH ROW EXECUTE PROCEDURE money.maintain_billing_ts();