>Firstly, on re-reading my message I realised that it could come across as glib and condescending. If it did, please accept my apologies -- it was certainly not intended in that way.
No offense at all. Don't worry.
>I think the thing that presents the most problems in your approach is that you don't know what the state of the database is before you create a new one and append from etc. It could (potentially) be almost anything. In the intervening period, changes to the database may have removed fields, added tables and so on. What if this particulkar client has missed an upgrade for example.
I didn't state the implicit assumptions, which for us are no problem: exclusive use of the database, no transactions in process, client never changes the structure of the database, only we do. It is common that a client misses an upgrade, that is no problem.
All I have to go on is what I do now, which like I said is only on free tables, and it goes like this:
1) Do ADIR on data directory and empty tables directory.
2) Step through array of data bearing tables. If table exists in both directories, compare structure via COPY STRUCTURE EXTENDED. If no changes, compare indices. If no change, keep going.
3) If anything changed, make backup of data bearing table into a subdirectory, then pack. Copy empty table and APPEND TO. If all OK rename tables appropriately.
4) If new empty tables exist, copy them over.
5) I don't delete obsolete tables automatically.
SQL Server is more complicated because of the security. Also it would be a lot slower.
>The approach I presented at WhilFest, OTOH, goes from one known state to the next, applying updates, moving and massaging data and all the ancillary stuff along the way. Mike Levy suggested that there might be a tool that generated the change script to go from one one database structure to another (but of course it wouldn't know about the data massaging), so you might be able to combine the approaches.
Your approach is fine and perhaps I'll end up using it. The tool that generates the change script sounds interesting.
>Another thought I just had is to keep a copy of each version of the database you ship and then test your change script on that copy so you can verify that you have been meticulous. Any missing meticulousness <g> could be replaced in the script by hand before you ship the update.
You trust my meticulousness far more that I do.
Thanks again for your help.
Alex
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only