1 -- we're using the 'lease' nodetype to model reservable nodes
2 INSERT INTO node_types VALUES ('reservable');
3 -- also the dummynet node_type is obsolete
4 DELETE FROM node_types WHERE node_type='dummynet';
9 lease_id serial PRIMARY KEY, -- id
10 t_from timestamp with time zone NOT NULL, -- from
11 t_until timestamp with time zone NOT NULL, -- until
12 node_id integer REFERENCES nodes NOT NULL, -- subject node
13 slice_id integer REFERENCES slices, -- slice owning the node
15 -- CONSTRAINT future CHECK (t_from > CURRENT_TIMESTAMP),
16 CONSTRAINT start_before_end CHECK (t_until > t_from)
20 -- hook to check for overlapping time slots on a given node_id
21 -- xxx might use the builtin OVERLAPS feature
22 -- http://www.postgresql.org/docs/8.3/interactive/functions-datetime.html
24 CREATE language plpgsql;
25 CREATE FUNCTION overlapping_trigger() RETURNS trigger AS $overlapping_trigger$
27 PERFORM lease_id FROM leases WHERE
28 -- consider only leases on the same node
30 -- consider only non expired leases
31 AND t_until > CURRENT_TIMESTAMP
33 AND NEW.lease_id <> lease_id
34 -- new start date is in range
35 AND ( (NEW.t_from >= t_from AND NEW.t_from < t_until)
36 -- new end date is in range
37 OR (NEW.t_until > t_from AND NEW.t_until <= t_until)
38 -- complete overlap: new from before from, new until after until
39 OR (NEW.t_from <= t_from AND NEW.t_until >= t_until));
41 RAISE EXCEPTION 'overlapping error: node % - slice %, % -> %', NEW.node_id, NEW.slice_id, NEW.t_from, NEW.t_until;
45 $overlapping_trigger$ LANGUAGE plpgsql;
48 TRIGGER overlapping_trigger BEFORE INSERT OR UPDATE
49 ON leases FOR EACH ROW EXECUTE PROCEDURE overlapping_trigger();
52 -- this is to let the API a chance to check for leases attached
53 -- to a node that is not 'reservable'
54 CREATE OR REPLACE VIEW view_all_leases AS
57 CAST(date_part('epoch', leases.t_from) AS bigint) AS t_from,
58 CAST(date_part('epoch', leases.t_until) AS bigint) AS t_until,
60 leases.t_from as s_from,
61 leases.t_until as s_until,
68 CAST( date_part ('epoch',leases.t_until-leases.t_from) AS bigint) AS duration,
69 leases.t_until < CURRENT_TIMESTAMP as expired
70 FROM slices INNER JOIN leases USING (slice_id)
71 JOIN nodes USING (node_id);
73 -- only the relevant leases
74 CREATE OR REPLACE VIEW view_leases AS
75 SELECT * FROM view_all_leases
76 WHERE node_type = 'reservable';
79 --------------------------------------------------
80 UPDATE plc_db_version SET subversion = 101;