Re: check if database is correctly created

On Wed, Aug 15, 2007 at 10:08:36AM +0200, Alain Roger wrote:

> i would like to check (via PHP or C#) if my database has been correctly
> created.
> for that i use the following SQL :
> select * from pg_tables where tablename = 'xxx' AND schemaname = 'yyy';
> this i repeat till i check all tables.
>
> But how to check sequences, index, functions, and so on ?

Use psql with -E and then issue any variety of \d style
commands to find out what psql does to display indexes,
functions, sequences etc.

However, be aware that checking for the existence of an
appropriately named table/function doesn't give any
guarantuee about what they really *are*.

We do something similar during database upgrade migrations:
we calculate a hash over our tables with columns and column
datatypes (tables only as they hold the real data). Only if
the hash matches an expected value do we migrate (change)
the tables themselves. Views, functions, indexes,
constraints can all be re-run from scratch upon failure
without affecting the data in the tables.

http://cvs.savannah.gnu.org/viewvc/gnumed/gnumed/server/sql/gmSchemaRevisionViews.sql?root=gnumed&view=markup

and now

http://cvs.savannah.gnu.org/viewvc/gnumed/gnumed/server/sql/v5-v6/dynamic/gm-schema.sql?root=gnumed&view=markup

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346