rihack.sql

text/x-sql

Download

-- relational integrity hacking:  attempting to create non-equality FK 
-- relationships.


-- parent relation has (non-overlapping) BOX keys, child relation has POINT
-- keys.

BEGIN;

DROP SCHEMA IF EXISTS rihack CASCADE;
CREATE SCHEMA rihack;
SET search_path TO rihack, public;

CREATE DOMAIN INTBOX AS BOX CHECK (
    (VALUE[0])[0] = ROUND((VALUE[0])[0]) AND
    (VALUE[0])[1] = ROUND((VALUE[0])[1]) AND
    (VALUE[1])[0] = ROUND((VALUE[1])[0]) AND
    (VALUE[1])[1] = ROUND((VALUE[1])[1])
);
CREATE DOMAIN INTPOINT AS POINT CHECK (
    VALUE[0] = ROUND(VALUE[0]) AND VALUE[1] = ROUND(VALUE[1])
);

CREATE TABLE boxes (
    id SERIAL PRIMARY KEY,
    region INTBOX NOT NULL,
    name TEXT NOT NULL,
    CONSTRAINT boxes_cannot_overlap EXCLUDE USING gist (region WITH &&)
);

CREATE TABLE points (
    region_id INTEGER REFERENCES boxes,
    poi INTPOINT NOT NULL,
    score INTEGER NOT NULL
);

-- Now, what we want to do is define an FK relationship:
--   FOREIGN KEY points.poi REFERENCES boxes.region WITH <@
-- We're going to hijack the existing constraint on the points table:
--   FOREIGN KEY points.region_id REFERENCES boxes.id WITH =

-- oids of operators:
--  POINT <@ BOX        511
--  BOX @> POINT        433     [commutator of op 511]
--  BOX ~= BOX          499
--  POINT ~= POINT      510
UPDATE pg_constraint SET conkey='{2}', 
                         confkey='{2}',
                         conpfeqop='{433}',
                         conppeqop='{499}',
                         conffeqop='{510}'
    WHERE conname='points_region_id_fkey';

-- these should all work:
INSERT INTO boxes (region, name) VALUES (box(point(2,2), point(5,5)), 'A');
INSERT INTO boxes (region, name) VALUES (box(point(7,7), point(10,10)), 'A');
INSERT INTO points (region_id, poi, score) VALUES (1, point(3,3), 10);
INSERT INTO points (region_id, poi, score) VALUES (1, point(4,4), 5);
INSERT INTO points (region_id, poi, score) VALUES (2, point(8,8), 15);

COMMIT;

-- this should fail:
INSERT INTO points (region_id, poi, score) VALUES (1, point(1,1), 15);