[sqlalchemy] Re: schema changes
-Original Message- From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Chris Withers Sent: 13 February 2008 13:51 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: schema changes Michael Bayer wrote: What if they exist but don't match the spec that SA has created? just try it out...create_all() by default checks the system tables for the presence of a table first before attempting to create it Cool, (same with dropping). When would SA drop a table? When you ask it to via metadata.drop_all() or table.drop(). Docs are here: http://www.sqlalchemy.org/docs/04/metadata.html#metadata_creating this is controlled by a flag called checkfirst. This a parameter to the methods or does it need to be set in some config file? See the docs referenced above. SQLAlchemy on its own doesn't use config files, but some of the frameworks that use it (eg. TurboGears and pylons) allow some SQLAlchemy-related configuration to be done in config files. if you're concerned about people running your application against databases created from a different version and then failing, I would suggest adding a version table to your database which contains data corresponding against the version of your application in some way. Good plan. There has been interest among some SA users over building a generic schema comparison system and I think even some prototypes are available, though I think thats a fairly complicated and unreliable approach to take for this particular issue. Do the projects have a name? I don't know about generic comparisons, but the Migrate project might be a place to start: http://code.google.com/p/sqlalchemy-migrate/ (Note that it is only in the process of being updated for SA 0.4) Hope that helps, Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: schema changes
Michael Bayer wrote: What if they exist but don't match the spec that SA has created? just try it out...create_all() by default checks the system tables for the presence of a table first before attempting to create it Cool, (same with dropping). When would SA drop a table? this is controlled by a flag called checkfirst. This a parameter to the methods or does it need to be set in some config file? if you're concerned about people running your application against databases created from a different version and then failing, I would suggest adding a version table to your database which contains data corresponding against the version of your application in some way. Good plan. There has been interest among some SA users over building a generic schema comparison system and I think even some prototypes are available, though I think thats a fairly complicated and unreliable approach to take for this particular issue. Do the projects have a name? cheers, Chris -- Simplistix - Content Management, Zope Python Consulting - http://www.simplistix.co.uk --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: schema changes
On Wednesday 13 February 2008 22:06:54 Don Dwiggins wrote: [EMAIL PROTECTED] wrote: we've put such a notion in our db, so the db knows what model-version it matches. Then, at start, depending on the versions one can decide which migration script to execute (if the db should be made to match the py-model), or which feautures to drop (if py-model should follow the db). btw there should be some tricky strategy as of what to call db-model-version and when that version really changes. This is getting into a big area: the problem of version control/configuration management for databases its not any bigger than any other configuration management of something structured that is deployed in the field... as long it consists of pieces and these pieces can go/combine wrong... .. (Just having a realized object-level schema should be a big step forward.) mmm this is going slightly offtopic, but there might be many levels of modelling (db-model - mapping - obj-model - concept-model - behavioural-model - ... - philosophy-model if-u-want). The more the merrier ;-) i.e. the easier to change something without affecting something else on a far-away-level -- but is harder to grasp, takes longer to develop, and needs more initial investment. But on a long run pays off very well - of course IF there is no long run, no point doing it. e.g. Right now i have a bitemporal machine applied even over some part of the code of the system, organised as sort of read-only db/repository of replaceable pieces of code. And changes of piece in this repository are treated same as changes in the salary of someone - maybe less dynamic but still changes... adieu svilen --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: schema changes
[EMAIL PROTECTED] wrote: This is getting into a big area: the problem of version control/configuration management for databases its not any bigger than any other configuration management of something structured that is deployed in the field... as long it consists of pieces and these pieces can go/combine wrong... I found it bigger because I couldn't find any ready-made tools for DB versioning (let along diff'ing), and that in a DB, there's a mix of structural elements and data elements that need to be sorted out (it's kind of like doing a version upgrade on a running program without disturbing the program's state 8^). If I've missed something, and this problem has been well and completely solved, I'd be delighted to hear of it. -- 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 sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: schema changes
Hi, ...and what happens if these methods are called and the tables already exist? With metadata.create_all, it only creates ones that don't exist. table.create() will error, or if you use the checkfirst option, will do nothing. What if they exist but don't match the spec that SA has created? SA doesn't know, so it continues until you hit a problem. We should really pull the code in tg-admin sql out into a standalone script. I think having a model vs database diff function would help you out a lot. Paul --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: schema changes
Paul Johnston wrote: SA will only try to create table when you tell it - either table.create() or metadata.create_all() ...and what happens if these methods are called and the tables already exist? What if they exist but don't match the spec that SA has created? cheers, Chris -- Simplistix - Content Management, Zope Python Consulting - http://www.simplistix.co.uk --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: schema changes
On Feb 12, 2008, at 12:34 PM, Chris Withers wrote: Paul Johnston wrote: SA will only try to create table when you tell it - either table.create() or metadata.create_all() ...and what happens if these methods are called and the tables already exist? What if they exist but don't match the spec that SA has created? just try it out...create_all() by default checks the system tables for the presence of a table first before attempting to create it (same with dropping). this is controlled by a flag called checkfirst. it the table exists, nothing is created. theres no comparison which takes place between the table in the DB and whats defined in your application. if you're concerned about people running your application against databases created from a different version and then failing, I would suggest adding a version table to your database which contains data corresponding against the version of your application in some way. There has been interest among some SA users over building a generic schema comparison system and I think even some prototypes are available, though I think thats a fairly complicated and unreliable approach to take for this particular issue. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: schema changes
Hi Chris, What happens when the schema expected by the mappers doesn't match up to the schema in the database? If the SQLAlchemy table definitions don't match the database, you will usually get SQL errors when you try to use them. The TurboGears admin tool can tell you the differences between the database and the SA table definitions. I use this quite a lot, to check the database is ok. To make changes, I first change the SA definitions, run tg-admin sql status, make the changes to the DB by hand, and run it again to check I did it right. This works for me; it could be improved, but that isn't greatly urgent. For getting started with an existing database, try this: http://code.google.com/p/sqlautocode/ Paul --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
