About data integrity rules

RT Developers
1

Hello

We are running RT 3.6.1 in a webcluster (via LVS) with 2 nodes and
PostgreSQL 8.2 as the database backend.

We can see that there are not data integrity rules defined in the
database schema (we suppose that the application implements this). We
have seen a couple of times problems with RT because there were data
inconsistency between tables (probably because a chain of actions in the
application/system has triggered a bug)

Our question:

Would it be possible to get the data model of the database schema so we
could identify all relations between tables/attributes and create the
data integrity rules needed?

We will do the job of creating these rules with pleasure. And you can
get the code when we are finnish.

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

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

2

Would it be possible to get the data model of the database schema so
we
could identify all relations between tables/attributes and create the
data integrity rules needed?

The schema and data model are not consistent with using foreign key
constraints. There are cases where fields which should reference
other tables use arbitrary values to represent “no value” rather than
nulls. Also, there are other places where you might need to write
custom complex triggers to enforce the relationships.

I was able to do some of the foreign keys for the prior version of the
schema from RT 2.x, which you may find in the archives here.

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.

Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806

3

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?

4

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.

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

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

5

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
ACLthat’s bee
tables.

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

So, we’d need a table per object type per thing we want this arbitrary
keying into. CustomFields, ACLs, Transactions, Links (and links do it
twice).
And it only works on Postgres. This is just about the only option
that we ever came up with when we explored the possibilities. And it
really just doesn’t feel like a good tradeoff to me.

-j

6

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?

The relational model answer is that you store real world facts relevant
to your business in a database.

There is no business rule demanding “ObjectType/ObjectId”. In the
real-world business, there are rules involving people, groups, tickets,
acls, history, etc. Therefore, a design in the spirit of the relational
model would start from those business rules.

Regards,
Jeff Davis

7

Jesse Vincent wrote:

[…]

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

So, we’d need a table per object type per thing we want this arbitrary
keying into. CustomFields, ACLs, Transactions, Links (and links do it
twice).

Yes, with the data model in use, this is the only way I can think of (We
are talking only about 4-5 object types, right?)

And it only works on Postgres. This is just about the only option that
we ever came up with when we explored the possibilities. And it really
just doesn’t feel like a good tradeoff to me.

The only thing I have found in MySQL with some similarity to
SQL-inheritance is “MERGE Storage Engine” 1 and it only works with
MyISAM tables so it is not really a solution.

I suppose that you could implement ‘foreign key checks’ using triggers
instead of the ‘foreign key check implementation of the database’, but I
have never done this. This should work with both MySQL and PostgreSQL
but I don’t know how effective this solution would be.

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

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