--- In firebird-support@yahoogroups.com, "martinthrelly"
<martinthrelly@...> wrote:

>
> is it possible to add/remove fields to/from a table at runtime via SQL?

YMMV. If you are lucky enough to do it at a time where no connection
happens to be caring about a particular table you may escape
unscathed, but you will often get bitten attempting to add a foreign
key constraint to a table that is in use.

Certainly removing fields that deployed applications are expecting to
see, or adding additional constraints that deployed applications are
unaware of may cause various problems.

Also be mindful when adding new fields with not null constraints
without triggers to assist (ie autopopulate) such fields with respect
to the legacy applications that are unaware of those fields.

>
> if so is this considered inherently bad practice and could lead to an
> unstable environment? (it seems pretty non standard to me - just
> checking?)

Well I can see the usefulness in doing so. Adding additional
functionality without having to take the database offline has numerous
advantages, but there are also numerous issues. There has been a
discussion in firebird-devel recently that outlines a number of
problems that need to be overcome.

That said, our database upgrades look as follows:

1. Get everyone out (easier said than done), shutdown secondary
services etc.
2. Rename DB file (proves everyone is out)
3. Take a file copy backup of the renamed file (no alias means no
possibility of anyone connecting to it in our configuration).
4. Apply .SQL script(s).
5. If errors, rename the old database file back to live and transfer
the problem database for analysis (ie should never happen).
6. Otherwise, rename the upgraded database file so others can see.
(Depending on the age of the database we may also do a backup-restore
cycle before renaming the new upgraded database back to live.)
7. Startup secondary services and notify users they may reconnect (if
necessary).

Adam