Monthly Archives: May 2011

Bugzilla

In the test phase, we would like to create a nice list of encountered bugs.
In most situations a project assistant is given the task to maintain this list. All encountered bugs are sent to him. He compiles a list. This list is then maintained to see which bugs are solved and which bugs are ready for a re-test.
However, Bugzilla helps you to create and maintain such list. It is simple to install, it can be maintained by everyone who is connected to the project and it delivers a very nice overview. Moreover, it is easy to install and it is free.
The idea is that Bugzilla is essentially a website that is installed from a so-called tarball that can be downloaded from www.bugzilla.org . It is a set of Perl scripts that act as a programm to store bug notifications in a MySQL database. We then need a webserver (to link user notifications to the database), a DBMS and a Perl interpretor. If you have a so-called LAMP installation at hand, you might have fulfilled the requirements: you then have a webserver (Apache), you have a DBMS (MySQL) and probably a Perl interpretor is available as well. In principle, the installation is straight-forward: download the tarball, unzip the file to get it installed on the website location, create a user and database in MySQL and pronto, you go.
The technical details are nicely described in http://www.bugzilla.org/docs/tip/en/html/installation.html .

For me, it took about 5 hours to get everything installed. I did not encounter a serious problem – it went along smoothly.
The only issue was how to modify the configuration of Apache exactly to get it into the translation of the Perl files. But also here, may resources are available. And after 5 hours, I saw this screen:bugzilla

Deferring referential integrity constraints

Last week, I gave a course on data warehousing. When I overviewed the theory, I noticed I had to spend some time on “disabling the the referential integrity constraints”.
I realised that I had to provide a clear explanation on what is meant by this.
The idea is rather straight forward. When we load sverela tables in a data warehouse, we might come in a situation where we load a certain table that is referenced to by another table. If we load that certain table before the referential table, we might come in a situation that we load certain fields that are not yet included in the referential table. If not dealt with properly, an error results.

Example.

Let us assume we have two tables. The DDL looks like:

CREATE TABLE MOEDER
(
  VREEMD  INTEGER
);
CREATE UNIQUE INDEX MOEDER_PK ON MOEDER
(VREEMD);
ALTER TABLE MOEDER ADD (
  CONSTRAINT MOEDER_PK
 PRIMARY KEY
 (VREEMD);

CREATE TABLE DOCHTER
(
  SLEUTEL  INTEGER,
  VREEMD   INTEGER
);
CREATE UNIQUE INDEX DOCHTER_PK ON DOCHTER
(SLEUTEL);
ALTER TABLE DOCHTER ADD (
  CONSTRAINT DOCHTER_PK
 PRIMARY KEY
 (SLEUTEL);

ALTER TABLE DOCHTER ADD (
  CONSTRAINT DOCHTER_R01 
 FOREIGN KEY (VREEMD) 
 REFERENCES MOEDER (VREEMD)
    DEFERRABLE INITIALLY DEFERRED);

We have two tables: a MOEDER table that contains referential data. We also have have DOCHTER that contains a foreign key (VREEMD). That foreign key can only contain values that are also included in table MOEDER.
Assume, we load the record (SLEUTEL, VREEMD) in DOCHTER that contains a value pair (100,2) with 2 for VREEMD. In that case 2 must be included in the MOEDER.

In a data warehouse, we might have a situation that we load DOCHTER first, followed by MOEDER.

How to deal with the situation that we also want to load data in DOCHTER that are only later loaded in MOEDER?

We solve that situation by adding the condition DEFERRABLE INITIALLY DEFERRED to the referential integrity constrant. In that case, the constraint is only checked when the commit is issued. At that moment, the MOEDER table must have values that are used in the DOCHTER table. That allows us to load the DOCHTER first, followed by a load of MOEDER and subsequently followed by a commit.

That is different from the traditional situation when the check is done at the moment of loading. When we load a DOCHTER record without a proper refence in MOEDER, we get an error. When the DEFFERABLE INITIALLY DEFERRED is added, the check is postponed to the moment of commit. That solves our problem.