[Rt-devel] About data integrity rules

[Rafael Martinez]

Jesse Vincent wrote:
>> The schema is really due for a good hard overhaul to allow the DB  
>> engine to enforce data integrity as a second layer of protection which  
>> the application tries to enforce.
> 
> How would you model something like the "ObjectType/ObjectId" foriegn key
> on the Transactions or ACL table?

Hei

With postgresql I would use inheritance [1]. It would be possible to use
a view to get the combined result you get now with Transactions or ACL
tables.

To be able to update with only one SQL statement the inherited tables I
would use a function that will use the  ObjectType parameter to update
the right table.

Foreign keys will be use on the inherited tables to maintain integrity.

A minus for this method is that you have to have a table per ObjectType
that inherits from the main table.

An example for the RT acl table:

-----------------------------------------------------
CREATE TABLE acl (
 id BIGSERIAL NOT NULL,
 principaltype CHARACTER VARYING(25) NOT NULL,
 principalid BIGINT NOT NULL,
 rightname CHARACTER VARYING(25) NOT NULL,
 objecttype INTEGER NOT NULL,
 delegatedby BIGINT DEFAULT 0,
 delegatedfrom BIGINT DEFAULT 0
);

CREATE TABLE acl_queue (
 objectid BIGINT NOT NULL
)INHERITS (acl);

CREATE TABLE acl_group (
 objectid BIGINT NOT NULL
)INHERITS (acl);

CREATE TABLE objecttype (
 id SERIAL NOT NULL,
 objectname CHARACTER VARYING(25) NOT NULL
);

CREATE TABLE queues(
 id BIGSERIAL NOT NULL
......
);

CREATE TABLE groups(
 id BIGSERIAL NOT NULL
.......
);

ALTER TABLE ONLY acl
    ADD CONSTRAINT acl_pkey PRIMARY KEY (id);

ALTER TABLE ONLY acl_queue
    ADD CONSTRAINT acl_queue_pkey PRIMARY KEY (id);

ALTER TABLE ONLY acl_group
    ADD CONSTRAINT acl_group_pkey PRIMARY KEY (id);

ALTER TABLE ONLY objecttype
    ADD CONSTRAINT objecttype_pkey PRIMARY KEY (id);

ALTER TABLE ONLY queues
    ADD CONSTRAINT queues_pkey PRIMARY KEY (id);

ALTER TABLE ONLY groups
    ADD CONSTRAINT groups_pkey PRIMARY KEY (id);

ALTER TABLE ONLY acl
    ADD CONSTRAINT acl_objecttype FOREIGN KEY (objecttype) REFERENCES
objecttype(id) MATCH FULL;

ALTER TABLE ONLY acl_queue
    ADD CONSTRAINT acl_queue_objectid FOREIGN KEY (objectid) REFERENCES
queues(id) MATCH FULL;

ALTER TABLE ONLY acl_group
    ADD CONSTRAINT acl_group_objectid FOREIGN KEY (objectid) REFERENCES
groups(id) MATCH FULL;


CREATE OR REPLACE VIEW acls AS
SELECT a.id, a.principaltype, a.principalid, a.rightname, b.objectname
AS objecttype, a.objectid, a.delegatedby, a.delegatedfrom
FROM (
 SELECT *
 FROM acl_queue
  UNION
 SELECT *
 FROM acl_group
) a
  LEFT JOIN objecttype b ON a.objecttype = b.id;
-----------------------------------------------------

To use only one SQL stamente to update acls with different objecttypes
we could create a function which checks the objecttype parameter and
updates the right inherited table.:

CREATE FUNCTION
update_acl(principaltype,principalid,rightname,objecttype,objectid,delegatedby,delegatedfrom)
....


PD.- With this method it would not be difficult to split the actual RT
acl table in X different ones using inheritance.

My 2 cents.

[1]: http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html

regards.
-- 
 Rafael Martinez, <r.m.guerrero at usit.uio.no>
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/