[Dbconfig-common-devel] Re: dbconfig-common and sql errors

[sean finney]

hi pierre,

(cc'ing this to the dbconfig-common list)

On Thu, 2007-03-22 at 19:10 +0100, Pierre Chifflier wrote:
> I'm the maintainer of some packages using SQL databases. I'm currently
> trying to add dbconfig-common support to some packages (for ex,
> prewikka, or libpreludedb0), but I'm facing a problem and have no real
> solution:
> 
> In postgresql, the IF EXISTS option to DROP TABLE was added only in
> version 8.0. This means that, using version 7.2, any DROP TABLE
> instruction will create an error if the table does not exist.
> According to PostgreSQL documentation, these errors can be ignored
> (there is no replacement).
> If is very common in SQL script files to find DROP/CREATE sequences, and
> this is the case for prelude.
> 
> However, this makes the package uninstalable, since the sql script must
> be executed two time to remove errors (since the second time, the tables
> exists there is no error).
> 
> Is there a simple solution for that case ? For now, I have only 2
> options:
> - comment the 'DROP TABLES' instructions in the sql script. This is not
>   very satisfying ,since the debian script can no more be the same as
>   the install script, and many problems are likely to happen in case of
>   upgrades/removal;
> - try to make the install script (here, dbconfig-common) ignore these
>   particular errors only.
> 
> Is there another solution ? I suppose this is a common problem, but
> can't figure how to solve it cleanly.

i'd say the easiest solution is to fix the sql script, since it's the
sql that's broken in this case.  i'm not sure what kind of
upgrade/removal problems you envision happening.

however, i'd be open to changing dbconfig-common to be a little more
flexible for this, if it could be done in a generalizable way.  for
example, grepping for error strings wouldn't meet this requirement :)

some ideas:

- provide a way to specify different sql to use when connecting to
  servers of different versions.
- provide a way to try the sql file a second time if it fails the first
  time, before giving up and telling the user.

the second option would be the easiest to implement, though i imagine
this won't solve all the possible problems resulting from different db
server versions and SQL syntaxes.



	sean
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 191 bytes
Desc: This is a digitally signed message part
Url : http://lists.alioth.debian.org/pipermail/dbconfig-common-devel/attachments/20070324/23ee762a/attachment.pgp