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

>
> --- 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
>

hi adam

this looks like it is not an option.

i am looking at how we can search user defined data in a fast manner.
currently the data is held in an EAV pivot fashion making ORDER BY
searches need a cross tab query. so i think i need a flat table to
search and sort fast.

the trouble is some of our clients add up to 300 defined fields
sometimes. so i created an addional post for this. i was thinking
about creating a single table with 300 columns to hold this data in
flat format.

i dont know if u understand where i am coming from. but if you do,
your input would be much appreciated. :)