Hi.
On Fri 2003-01-31 at 12:32:51 +0100, [EMAIL PROTECTED] wrote:
> Hi there, I have a little question for you in the mysql community..
>
> I was wondering how persons in this community handles changes to the
> database when your application that uses the database needs some new
> tables, columns, etc...
I think this always depends on your use case, but since you wanted to
know how we handle this...
> Do you have an application with a sort of upgrade functionallity that
> when started checks the database for incosistency and when it finds some
> updates the database? (ie compares the structure of the table in the
> database to some "template" stored in the application or as
> datafiles/scripts)
>
> Or do you use some "version" system where the application instead of
> looking for inconsistency just look at the version number of the
> database, compares it to some internal version, and updates the database
> accordingly? (ie manually creating all the needed updatescripts that
> needs to run the application and have the application select between these)
>
> Or perhaps you do this update of the database manually when intalling a
> new version?
Effectively that is what I do. Using the development database, I write
the application and change the database accordingly (and save the
statements I use for this). When the program is ready, I write a
script with the SQL commands needed to update the database to the new
version (most times that involves only slightly correcting the
statements I saved beforehand) and test that it results indeed in the
wanted table structure. Then I take both, the new program and the SQL
script and install them in parallel on the production machine.
> For me, version 1 would be preferable, but unfourtunally this does not
> work in every scenario, but i guess that it normally could take care of
> about 90% off all my changes... the second system would be able to take
> care of all type of changes, but it requires more labour...
Huh? I am not sure why you think #2 is more work. I think an
application which tries to make consistency checks and do the correct
SQL statements (#1) is much more complex than one that simply executes
some script based on a version number (#2). Especially since you
probably already have those SQL scripts as side product of your
testing (if you are using a GUI, I sincerly hope it is able to log the
commands it executes for you. Or use the update log of MySQL).
IMHO, the real (potential) drawback of #2 is that it relies on the
version number being correct and therefore fragile against change.
E.g. my experience shows that "emergencies" sometimes require to make
changes by hand, which invalidates the version, and it being an
emergency, the change is high that the version is not changed
accordingly. So I think that #1 has the advantage of being adaptive
while being a lot of work.
I am also not sure, why you think that #1 would be only able to handle
90% of the cases, theoretically it can dump the whole database
structure to find out about types and whatever. Hm. Maybe that's why
you don't think it is much work - only concentrating on the "common"
cases?
> so, how do you all manage this?
I don't use the first two solutions, because I am around when I do
updates anyhow (and I wouldn't want the application to change the
production system significantly when I am not around), so I prefer
doing the "version check" myself. Maybe that is, because I only have a
handful of machines to take care of.
If I had to roll out changes to several hundreds of machines (possibly
clients) and couldn't be sure which version is running, I would use a
of variation of #2: write a little script additionally to the SQL
update-script, which makes a SQL dump of the existing database
structure, compares (maybe via MD5 sum to safe space) that with one
that I made on the test system and bails out, if they differ.
That can be made to work with several versions. In other words, it
would not check a version number saved in the database, but determine
the version by the database structure itself.
That would give #2 with the assurance that an update script is only
run, if the database structure is as expected, no matter happened to
the database in-between (it is fascinating what clients can do to
files they are not supposed to even know about ;-).
HTH,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
