RE: [osdldbt-general] Data validation tests

[Mickelson, J. K <joh...@in...>]

Mary,
	I think you are correct. I'll have to sit
	down with a postgresql book and see if I=20
	can't get this comparison figured out. Thanks
	for the tip.

	John Mickelson

-----Original Message-----
From: Mary Edie Meredith [mailto:mar...@os...]=20
Sent: Thursday, June 30, 2005 10:02 AM
To: Mickelson, JohnX K
Cc: osd...@li...
Subject: Re: [osdldbt-general] Data validation tests

On Thu, 2005-06-30 at 09:36 -0700, Mickelson, JohnX K wrote:
> All,
>=20
>             I recently got the dbt3 test working in my environment and
> am happy=20
>=20
>             with it.=20
>=20
> =20
>=20
>             The powers that be have determined that I now need to
> ensure data=20
>=20
>             integrity.=20

> Do any of these dbt tests validate or compare the databases
>=20
>             to the flatfiles to ensure that what is in the database is
> the same info
>=20
>             as the flatfiles?=20
Are you sure this is what they meant by data integrity?  Just a hunch,
but my guess is that what was meant was doing the ACID test defined by
the benchmark.  We didn't implement that, since we never intended to be
audited.=20

To do the compare you describe would be pretty easy.  You can write a
program that reads the flat file line by line, doing a lookup in the
database (based on the fields that are the unique id) for each line.
There should be one and only one row in the database table.  Compare the
data in the database table columns with the data in the flat file.  You
should find a match in every case. =20

Then, to make sure there are no other rows in the table, do a count of
the number of rows in the table, versus the rows in the input file.
They should be the same. =20

I vaguely remember doing this visually when we were developing the test
kits.  Mark, did we ever code something like this and put it in the kit?
=20
Too bad this isn't Informix. They could define an external flat file to
be a table, and you could do a join from an internal table to do the
compare. Sigh.  Does postgreSQL do this, by any chance?
>=20
> =20
>=20
>             John Mickelson
>=20
>=20
--=20
Mary Edie Meredith=20
mar...@os...
503-906-1942
Data Center Linux Initiative Manager
Open Source Development Labs