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