IDS Forum

Re: Viewing Table structure alterations

Posted By: Jonathan Leffler
Date: Thursday, 9 April 2009, at 8:25 a.m.

In Response To: Re: Viewing Table structure alterations (PAUL GATHOGO)

On Wed, Apr 8, 2009 at 20:53, PAUL GATHOGO <pgathogo@gmail.com> wrote:
> Am just wondering how hard is it to have such a feature in the IDS? or How do
> people control such alteration?

People control those who have permission to alter tables - and impose
penalties on those who do undisciplined changes to tables. In many
production shops, especially those with critical data (such as
customer account data that must be protected - whether credit card
numbers or healthcare data or any other sensitive data), changing a
table without permission would be grounds for instant dismissal for
abuse of privileges.

> You can imagine a situation when you have a pool of programmers, some might
> Alter tables without documenting there changes, as an administrator it would
> be good to have a log of such changes...

As was already mentioned (by others), you could enable auditing and
track all DDL changes. This would put a finger on the culprit - if
you have sufficient control over your system that people don't all
login as root and informix to do the database administration work. If
they all login as generic user names, you have no trackability - don't
let them do it. You might want to add role separation to your setup.

Alternatively, you can grope through the logical logs to find when
tables are changed.

However, it is generally simpler to make it a discipline issue - you
change a table without permission, and you don't change any other
tables without permission because you aren't working here any more.

Clearly, if it is a development instance and the changes are required,
then that is a different issue. But the changes should be documented,
and undocumented changes should be an issue (unless the database is
the user's private copy - then they can do what the hell they like as
long as it doesn't break things when the changes are documented and
brought into the main development or production versions of the
database).

You should be keeping a record of the 'official structure' of tables
under some sort of VCS (version control system).

I've been toying with ideas for identifying when two tables have
equivalent (identical) structures. The problem is knowing which
aspects of the structure are critical. For many purposes (I'm
thinking I4GL programs here, though there are other places it could be
of relevance), the table name, sometimes with the owner name, and the
column list with the types of the columns is all that must be the
same. So, I've considered whether an MD5 checksum of the table name
and column list (each entry consisting of column name, coltype,
collength fields from syscolumns) is sufficient. For many of my
purposes, I think it would be. And MD5 is fine for this - I'm looking
for 'probably the same', not 'has definitively not been tampered
with'. This would identify reordered columns and renamed columns and
retyped columns. I'm not sure whether it is reliable for UDTs - the
numbers are not necessarily the same. So, it might be necessary to
use text names for UDTs (row types, distinct types, datablade types,
etc). And if that's the case, then maybe the hash should be over the
text representation of types (CHAR(40) instead of
coltype=0,collength=40) for the basic types.

Why this meditation - w? Well, packaged appropriately, it would give
you an easy mechanism to identify whether the crude table structure is
the same as it used to be. More refined versions might detect
differences in default values, check constraints, indexing
differences, primary key and foreign key constraints, and so on. Note
that it is more difficult to manage some of these - what is the
canonical ordering such that these two tables have the same checksum:

CREATE TABLE Sample1
(

Col01 SERIAL NOT NULL PRIMARY KEY,

Col02 DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NULL,

Col03 INTEGER NOT NULL REFERENCES Sample3(Col1),

Col04 CHAR(1) NOT NULL CHECK(Col04 IN ('Y','N','M','-'))
CONSTRAINT c0_sample1
);

CREATE TABLE Sample
(

Col01 SERIAL NOT NULL,

Col02 DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NULL,

Col03 INTEGER NOT NULL,

Col04 CHAR(1) NOT NULL,

PRIMARY KEY(Col01),

FOREIGN KEY(Col03) REFERENCES Sample3,

CHECK(Col04 IN ('-','Y','M','N')) CONSTRAINT c0_sample2
);

I'm assuming that the primary key of table Sample3 is Col1.
It means that constraint names (both implicit and explicit) have to be
disregarded.
In the case of the two check constraints, the sequence of the
alternative values has changed, but that's a set so there is no
difference in effect - does this matter? What about constraints such
as:

CHECK(Col0x >= 1 AND Col0x <= 20)

CHECK(Col0x <= 20 AND Col0x >= 1)

CHECK(Col0x BETWEEN 1 AND 20)

In these cases, I'd be inclined to say "The spelling of the CHECK
constraint must be identical between the two tables" as recognizing
all the alternatives and reducing them to some canonical form is an
open-ended nightmare.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease
to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Norman Mailer - &quot;Writing books is the closest men ever come to
childbearing.&quot; -
http://www.brainyquote.com/quotes/authors/n/norman_mailer.html

Messages In This Thread

• Viewing Table structure alterations
  PAUL GATHOGO -- Wednesday, 8 April 2009, at 4:04 a.m.
  • RE: Viewing Table structure alterations
    Mark Tyrer -- Wednesday, 8 April 2009, at 4:39 a.m.
  • RE: Viewing Table structure alterations
    Mark Tyrer -- Wednesday, 8 April 2009, at 4:48 a.m.
  • Re: Viewing Table structure alterations
    Art Kagel -- Wednesday, 8 April 2009, at 11:33 a.m.
    • Re: Viewing Table structure alterations
      PAUL GATHOGO -- Wednesday, 8 April 2009, at 11:53 p.m.
      • Re: Viewing Table structure alterations
        Jonathan Leffler -- Thursday, 9 April 2009, at 8:25 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.