On Tue, Sep 24, 2013 at 7:25 AM, Michael Bayer <mike...@zzzcomputing.com>wrote:
> > On Sep 23, 2013, at 10:04 PM, Ken Lareau <klar...@tagged.com> wrote: > > > Hopefully this will make sense... > > > > I have a database which is in need of some normalization of the column > > naming in various tables. In an effort to minimize disruption (since > this > > is a live database used by many applications), I'm trying to use a > two-step > > approach: > > > > 1) Add a new column that will 'mirror' the current primary key column, > > then update code to utilize that column once in place > > 2) Once all code is converted, remove the original primary key column > > and make the new column the primary key instead. > > > > In an effort to minimize change to the code I have currently using my > > SQLAlchemy model, I'm trying to find a way to deal with this from within > > the declarative classes themselves. So the questions are: > > > > - Is this even possible, and if so, how can it be done? > > - If not possible, is there a good yet minimally intrusive external > > change that can be done? > > > > My searching through the docs so far hasn't turned up anything useful, > > I'm afraid... > > > > it depends a lot on how you are generating primary key values. how are > the new values being generated? (things like database platform affect > this dramatically). if using something like MySQL's autoincrement, you > probably want to write a trigger that will populate this new column after > an INSERT. overall a trigger is probably the best way to get this extra > value in the table efficiently. > > OTOH if your applications know the new primary key value beforehand, then > SQLAlchemy events can be used to copy the value into the new column. > > you also want to be mindful of indexing on this column, as applications > move to use the new column, you probably want queries to still use indexes. > The primary key values are indeed using autoincrement in MySQL. The reason for doing this is the database is an existing one over which I've been building a library to be used to programmatically access it, but it's not the only library accessing the database, so to prevent potential breakage with the other libraries, we taking the safe approach. The normalization is due to lovely decisions like having all columns be in lowercase except for the primary key (though sometimes other keys also have the same problem) where we have something like 'PackageID'. The new column is meant to replace the original column, but there will be a small period of time that both columns will exist, so they must remain in sync until all of the current libraries and appli- cations are using the new column. At that point, the new column will take over and hopefully no one will be the wiser that things were wrong before. *smile* As far as I know, none of the applications know the primary key value beforehand, so that isn't a concern. Indexing might be, though the hope is the changeover will be relatively short (matter of a day or so), but I can still index the new file if the impact is great enough (the database isn't _too_ large at this point). Of course, this leads back to the original question... how exactly does one accomplish this? :) Hopefully the clarification above helps some in determining this. -- - Ken Lareau -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
