2024-08-13T22:49:12.sql (2103B)
1 BEGIN; 2 3 -- Check not null in points 4 ALTER TABLE spaceplanner.floorplan_points ADD CONSTRAINT not_null CHECK ( 5 x NOTNULL AND y NOTNULL AND floorplan NOTNULL 6 ); 7 8 -- Make id bigserial in points 9 ALTER TABLE spaceplanner.floorplan_points ALTER COLUMN id TYPE bigint ; 10 CREATE SEQUENCE floorplan_points_id_seq; 11 ALTER SEQUENCE floorplan_points_id_seq OWNED BY spaceplanner.floorplan_points.id; 12 ALTER TABLE spaceplanner.floorplan_points ALTER COLUMN id SET DEFAULT nextval('floorplan_points_id_seq'); 13 14 -- Change primary key in points 15 ALTER TABLE spaceplanner.floorplan_points DROP CONSTRAINT floorplan_points_pkey; 16 ALTER TABLE spaceplanner.floorplan_points ADD CONSTRAINT floorplan_points_pkey PRIMARY KEY (id); 17 18 -- Cascade delete on points (floorplan) 19 ALTER TABLE spaceplanner.floorplan_points ADD CONSTRAINT points_floorplan_fkey 20 FOREIGN KEY (floorplan) REFERENCES spaceplanner.floorplans(id) ON DELETE CASCADE ; 21 22 -- Add pointmaps 23 CREATE TABLE spaceplanner.floorplan_pointmaps ( 24 id bigserial PRIMARY KEY, 25 floorplan bigint REFERENCES spaceplanner.floorplans(id) NOT NULL 26 ON DELETE CASCADE, 27 a bigint REFERENCES spaceplanner.floorplan_points(id) NOT NULL 28 ON DELETE CASCADE, 29 b bigint REFERENCES spaceplanner.floorplan_points(id) NOT NULL 30 ON DELETE CASCADE, 31 type varchar NOT NULL, 32 CONSTRAINT unique_pointmap UNIQUE (floorplan, type, a, b), 33 CONSTRAINT different_ids CHECK (a <> b), 34 CONSTRAINT valid_type CHECK (type = 'wall' OR type = 'door') 35 ); 36 37 CREATE FUNCTION spaceplanner.pointmap_points_same_floorplan() 38 RETURNS trigger AS $pointmap_points_same_floorplan$ 39 DECLARE 40 bad int; 41 BEGIN 42 SELECT count(floorplan) INTO bad 43 FROM spaceplanner.floorplan_points 44 WHERE (id = NEW.a OR id = NEW.b) AND floorplan <> NEW.floorplan; 45 IF (bad <> 0) THEN 46 RAISE EXCEPTION 'points must be from the same floorplan as pointmap'; 47 END IF; 48 RETURN NEW; 49 END; 50 $pointmap_points_same_floorplan$ LANGUAGE plpgsql; 51 52 CREATE CONSTRAINT TRIGGER check_pointmap_points_floorplan 53 AFTER INSERT OR UPDATE ON floorplan_pointmaps 54 FOR EACH ROW 55 EXECUTE FUNCTION spaceplanner.pointmap_points_same_floorplan(); 56 57 COMMIT;