| Subject | Re: How to update table structure with SQL? |
|---|---|
| Author | neighbour.kerry |
| Post date | 2009-06-23T12:18:57Z |
--- In firebird-support@yahoogroups.com, "Jarrod Hollingworth" <jarrod@...> wrote:
Forgive me for saying this - sounds terribly complex and time
consuming!! And very prone to error.
I understand what you are saying - I used to use that sort of system at a previous job. Did not like it. So much work. The real problem though, is getting anything from the customer. It is not going to happen with my particular customers. So, any system I device MUST be standalone - no customer interaction whatsoever. It must be invisible. If I supply a new version, it must go in seamlessly - again, no interaction whatsoever.
The only way I would use a versioning system is if I could put a version number in every table (as DBISAM does). Having a version at the database level is not much point, since I do updates at the table level.
I wrote a Delphi/DBISAM script that did exactly what I want to do now. I am really trying to get Firebird to do just what DBISAM can do so easily. I simply include a modified DDL script with every new update. When the program loads, it checks the current database (there can be a lot more than one), checks the table versions, and updates anything required on the fly. It does this EVERY time a database is loaded. Very fast - the customer never knows about it and works flawlessly. It will update ANY database the customer has - of any version, to the current version.
Now, the problems with Firebird are many. The big one is there is no IF EXISTS function. I have worked around this by using Delphi. The other problem seems to be that you cannot really ALTER a column after it is created. Or in the few cases you can, with many restrictions. There are also problems with Procedures and autoinc fields. There may be others I have yet to discover!
I am almost there. I have almost everything working. My real sticking point has been the ALTER columns script. Basically, I think I will just have to give up on that and hope I will never have to alter a column! It is probably not a good thing to do on a live database anyway.
I like the idea of renaming the DB, building a new one, then copying all the data back. Code wise that seems the simplest. The databases I deal with are smallish (40,000 - 100,000 records max), so it should be doable. The problem is it is not transparent. Still, it is a solution, if not a very elegant one.
>etc...etc
> a. Do a metadata and data export from your database (e.g. using SQL Manager for Firebird).
> b. Save this SQL script. It's the current version baseline that you will compare with later.
> c. Add a new value somewhere in the database that stores the current
> database version number. I have a special system table for my app with
Forgive me for saying this - sounds terribly complex and time
consuming!! And very prone to error.
I understand what you are saying - I used to use that sort of system at a previous job. Did not like it. So much work. The real problem though, is getting anything from the customer. It is not going to happen with my particular customers. So, any system I device MUST be standalone - no customer interaction whatsoever. It must be invisible. If I supply a new version, it must go in seamlessly - again, no interaction whatsoever.
The only way I would use a versioning system is if I could put a version number in every table (as DBISAM does). Having a version at the database level is not much point, since I do updates at the table level.
I wrote a Delphi/DBISAM script that did exactly what I want to do now. I am really trying to get Firebird to do just what DBISAM can do so easily. I simply include a modified DDL script with every new update. When the program loads, it checks the current database (there can be a lot more than one), checks the table versions, and updates anything required on the fly. It does this EVERY time a database is loaded. Very fast - the customer never knows about it and works flawlessly. It will update ANY database the customer has - of any version, to the current version.
Now, the problems with Firebird are many. The big one is there is no IF EXISTS function. I have worked around this by using Delphi. The other problem seems to be that you cannot really ALTER a column after it is created. Or in the few cases you can, with many restrictions. There are also problems with Procedures and autoinc fields. There may be others I have yet to discover!
I am almost there. I have almost everything working. My real sticking point has been the ALTER columns script. Basically, I think I will just have to give up on that and hope I will never have to alter a column! It is probably not a good thing to do on a live database anyway.
I like the idea of renaming the DB, building a new one, then copying all the data back. Code wise that seems the simplest. The databases I deal with are smallish (40,000 - 100,000 records max), so it should be doable. The problem is it is not transparent. Still, it is a solution, if not a very elegant one.