>> >> In Sqlalchemy, there is a way to track the changes, and create a small >> module to apply changes to production as development produces fixed-in-time >> deltas. >>
>Currently we keep track of changes in update scripts which we store also in our source repositories. >That works in practice but is a hassle especially with identifying which updates are needed. >This pain point could be remedied by saving the schema version in a table but we have no concrete plans for this yet. >In your mind, does sqlalchemy have the right solution? The sqlalchemy solution has a certain elegance to it. Their table design uses a visitor function concept similar to yours. So incorporating the concept into Wt would not require re-engineering, just additional engineering. How much, I can't say, as I havn't put much thought into how to diff between table versions, and inter-table relationships. They make use of a table in the database holding a version number, or more accurately, a uuid identifying the version. Similar to what you were already thinking. I'm not sure about all the backend machinations, but from a high level perspective, when I'm ready to 'lock' a version, I run a module to visit the table definitions, which creates a snapshot text file of some sort, plus create a two way schema delta file. Each version is identified with a uuid. The schema delta file is a text file so it could be manipulated manually for the cases where the auto-diff couldn't resolve the delta properly. My schemas were simple, so I didn't need to worry about this. There is then a tool for rolling a database forward a step, and another for rolling back a step. Regardless of which way you go, during the process, foreign key relationships are deleted, tables changed as required, and foreign key relationships are then re-established. This eliminates the problem of figuring out in which order the operations need to be performed. >From a Wt perspective, I see several possible scenarios for the schema delta file: * create a text file with schema statements for rolling forward and backward, then would run the appropriate set against the database * hold the meta-data in the database * code generate the schema deltas to be included in the source code to facilitate automated database migrations as new code is released * or some combination of the above If you'd like more detail, I can try to dig deeper. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ------------------------------------------------------------------------------ _______________________________________________ witty-interest mailing list witty-interest@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/witty-interest
