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)
...
well since (in our case) we use data structures that is almost identical to a table in the database, we could make 1 system (written once) that works for all our data structures, unfourtunally it can not handle all cases of changes in the database, I'n not talking about simple changes where there is a column added or removed, or there is a change in how long an varchar can be, if the column changes from "float" to "double" (which i think that in normal usage, will be like 90% of all cases for us atleast), HOWEVER, some changes to the database cannot be catched like this.... ie what happens if a table is split into several tables?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).
or an varchar is changed to an integer? those are harder perheps impossible to make a generic system for...
we will however make a systems that atleast compares the data-structures and the tables, to see of anything is wrong and if it is, it will warn us...
with version 2 i would have to code in every change in the application that does the update, every script manually, etc... and every update would require me to update this system...
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 know this drawback, and have not figured out how to solve it yet :-(
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?
see above :-)
fourtunally for me, I'm around also for every update, but in the near future, this might not be so any more, especially when our potential customers are far away from each others (lots of travels to just install a new version)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.
Sound like a good idea, ie that I should use a md5 hash to recognise the structure. and use that as a sort of versioning of the tables...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 ;-).
I think the md5 would have to be calculated ona per table basis, since if we introduce some bug-fixes in one module only and it requires a table-update, this would be recognised in a future update...
the question is then, do "select create table TEST" work exactly the same on different os:s ie if I have the same table on a windows machine and on a linux machine, does the above query return the exact same result? even eith line endings? or could they return 2 different queries and therefore making the md5 calculating prove worthless?
or are there other ways to get to the database structure that work the same on different platforms...?
/Christian Andersson
---------------------------------------------------------------------
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
