[sqlalchemy] Re: is sqlalchemy-migrate the right way to go?
This thread is of interest to me as well, although the problem I'm facing is somewhat different. Just to expand the space a little, here's my situation: - A legacy database schema with hundreds of tables and procedures. - An application that accesses the database, with occasional updates delivered to customers (not all of them will upgrade at once) -- each update needs to update the database as well as part of the installation (with live data existing in the database, of course). - Customers may add some of their own tables and procs, and have custom versions of some standard procs. (Maintaining those is the customer's problem, although we do help.) To bring some sanity to this, I've created a folder hierarchy of scripts and some programs to apply them to create or update a DB (I call it the Standard Database). This hierarchy is under version control. Still, though, it's too unwieldy to distribute and manage, and doesn't solve all the problems. Currently, we just have a folder of scripts associated with each update and run them against the customer's DB. This leaves us with the duplicate script problem, of course; sigh. I'd like to turn the Standard Database into an application that would do the kinds of things being discussed here, in particular upgrading a DB as appropriate, while respecting both the live data and local modifications. Part of this would be something like a DB diff that could let the user know what needs to be updated. Just a brain dump at this point, -- Don Dwiggins Advanced Publishing Technology -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: is sqlalchemy-migrate the right way to go?
We will definitely also need a migration tool. We've only briefly looked into the sqlalchemy-migrate tool, but were immediately disappointed in its apparent requirement to keep versions of the schema. In our book, we see the ideal tool as one that doesn't care about versions: it just looks at the current database schema versus the schema loaded into sqlalchemy and decides what DDL statements are needed to bring the current schema inline with what has been loaded into python. We understand there are dangers/limitations to this approach, but in 95% of the cases, this is all you need or want. There is an 'upgrade_db_from_model' which maybe does exactly this, but our understanding is lacking. The whole point is to avoid needing to define a single change in two places. For example, if I want to add a column to a table and I require the programmer to remember to place this change in both the 'master' tables.py file *as well as* an incremental schema version file, I am only asking for trouble that sooner or later someone (or I) will miss one of the two and end in problems. Are there any tools that do this (reflect and introspect to create dynamic DDL upgrade statements)? On May 17, 1:50 pm, Michael Bayer mike...@zzzcomputing.com wrote: On May 17, 2010, at 1:13 PM, Chris Withers wrote: Hi All, I want our production systems to start caring about the versions of their schemas. sqlalchemy-migrate was the first project I came across which addresses this. What other projects should I look at in this area, or is sqlalchemy-migrate the obvious choice? If it is, how can I, in my application, check what version of the schema the current database is? (the idea being that the app will refuse to start unless the schema version is that expected by the app) Related: how can I short circuit this when the database is empty and bump the schema version up to the latest for the app? (ie: dev instances, where the process is to blow the db away often so no need for migration) sqlalchemy-migrate is the obvious choice. I am also developing a micro-migrations system called Alembic (http://bitbucket.org/zzzeek/alembic) in conjunction with my current work project but not all features have been implemented yet - its expected that I'll be getting them in a more polished state in the coming months. The Migrate project can of course steal any and all desireable features and code from Alembic freely as I would like sqlalchemy-migrate to remain the default choice. as for the related issue, I think its best that your setup provide a setup-app type of command which generates an initial schema, and embeds the current migrate version number. Here's a snip of a related Pylons websetup.py: from migrate.versioning.api import version_control, version, upgrade from migrate.versioning.exceptions import DatabaseAlreadyControlledError # Create the tables if they aren't there already meta.Base.metadata.create_all(bind=meta.engine, checkfirst=True) # setup migrate versioning table if not present try: latest_version = version(migrate) version_control(pylons.config['sqlalchemy.url'], migrate, version=latest_version, echo=True) except DatabaseAlreadyControlledError: log.info(migrate table already present) # do any migrate upgrades pending... upgrade(pylons.config['sqlalchemy.url'], migrate, version=latest_version, echo=True) The migrations system should only be used for changes to an existing schema. Other check this before running types of functionality can be accomplished similarly. As a side note, I also have an elaborate monkeypatch to migrate to get it to work with transactional DDL. Simple support for transactional DDL is one of the goals of the new tool I am writing. If you're not on Postgresql or MS-SQL, then you can't use transactional DDL anyway. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: is sqlalchemy-migrate the right way to go?
there's a tool for Django called South that does this. But personally I think writing a few lines of alter_column() is preferable to a heaping dose of schema-guessing magic. On May 17, 2010, at 3:28 PM, Kent wrote: We will definitely also need a migration tool. We've only briefly looked into the sqlalchemy-migrate tool, but were immediately disappointed in its apparent requirement to keep versions of the schema. In our book, we see the ideal tool as one that doesn't care about versions: it just looks at the current database schema versus the schema loaded into sqlalchemy and decides what DDL statements are needed to bring the current schema inline with what has been loaded into python. We understand there are dangers/limitations to this approach, but in 95% of the cases, this is all you need or want. There is an 'upgrade_db_from_model' which maybe does exactly this, but our understanding is lacking. The whole point is to avoid needing to define a single change in two places. For example, if I want to add a column to a table and I require the programmer to remember to place this change in both the 'master' tables.py file *as well as* an incremental schema version file, I am only asking for trouble that sooner or later someone (or I) will miss one of the two and end in problems. Are there any tools that do this (reflect and introspect to create dynamic DDL upgrade statements)? On May 17, 1:50 pm, Michael Bayer mike...@zzzcomputing.com wrote: On May 17, 2010, at 1:13 PM, Chris Withers wrote: Hi All, I want our production systems to start caring about the versions of their schemas. sqlalchemy-migrate was the first project I came across which addresses this. What other projects should I look at in this area, or is sqlalchemy-migrate the obvious choice? If it is, how can I, in my application, check what version of the schema the current database is? (the idea being that the app will refuse to start unless the schema version is that expected by the app) Related: how can I short circuit this when the database is empty and bump the schema version up to the latest for the app? (ie: dev instances, where the process is to blow the db away often so no need for migration) sqlalchemy-migrate is the obvious choice.I am also developing a micro-migrations system called Alembic (http://bitbucket.org/zzzeek/alembic) in conjunction with my current work project but not all features have been implemented yet - its expected that I'll be getting them in a more polished state in the coming months. The Migrate project can of course steal any and all desireable features and code from Alembic freely as I would like sqlalchemy-migrate to remain the default choice. as for the related issue, I think its best that your setup provide a setup-app type of command which generates an initial schema, and embeds the current migrate version number. Here's a snip of a related Pylons websetup.py: from migrate.versioning.api import version_control, version, upgrade from migrate.versioning.exceptions import DatabaseAlreadyControlledError # Create the tables if they aren't there already meta.Base.metadata.create_all(bind=meta.engine, checkfirst=True) # setup migrate versioning table if not present try: latest_version = version(migrate) version_control(pylons.config['sqlalchemy.url'], migrate, version=latest_version, echo=True) except DatabaseAlreadyControlledError: log.info(migrate table already present) # do any migrate upgrades pending... upgrade(pylons.config['sqlalchemy.url'], migrate, version=latest_version, echo=True) The migrations system should only be used for changes to an existing schema. Other check this before running types of functionality can be accomplished similarly. As a side note, I also have an elaborate monkeypatch to migrate to get it to work with transactional DDL.Simple support for transactional DDL is one of the goals of the new tool I am writing. If you're not on Postgresql or MS-SQL, then you can't use transactional DDL anyway. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post
Re: [sqlalchemy] Re: is sqlalchemy-migrate the right way to go?
On Mon, May 17, 2010 at 9:49 PM, Kent k...@retailarchitects.com wrote: Ideally, I agree. Practically speaking, though, we came from a company where dozens and dozens of developers worked on the system and it was structured exactly this way (a master file and a series of incremental upgrade scripts). It was always getting messed up between the two sets of schema definitions until eventually we developed a schema comparison tool and all those problems seemed to vanish. I'm obviously not saying SQLAlchemy needs to provide this, but just trying to make a case for its usefulness. Thanks for your input. I'v never tried it, but maybe miruku is what you are looking for: http://bitbucket.org/gjhiggins/miruku/wiki/Home -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: is sqlalchemy-migrate the right way to go?
Thanks, that looks like its conceptually what we are hoping for, at least. On 5/17/2010 3:58 PM, Tamás Bajusz wrote: On Mon, May 17, 2010 at 9:49 PM, Kentk...@retailarchitects.com wrote: Ideally, I agree. Practically speaking, though, we came from a company where dozens and dozens of developers worked on the system and it was structured exactly this way (a master file and a series of incremental upgrade scripts). It was always getting messed up between the two sets of schema definitions until eventually we developed a schema comparison tool and all those problems seemed to vanish. I'm obviously not saying SQLAlchemy needs to provide this, but just trying to make a case for its usefulness. Thanks for your input. I'v never tried it, but maybe miruku is what you are looking for: http://bitbucket.org/gjhiggins/miruku/wiki/Home -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
