[sqlalchemy] Re: Consistency with DB while modifying metadata
On Wednesday 25 July 2007 19:34:50 Marco Mariani wrote: > Anton V. Belyaev ha scritto: > >> again, im not opposed to this feature and ill patch in an > >> adequate (and fully unit-tested) implementation. but have you > >> actually ever *had* this problem? or is it just hypothetical ? > > > > For example, a developer modifies the metadata and checks in. > > Another developer updates and finds strange problems, having the > > old database. > > Then, a SQL script to migrate the database schema should be > provided with the check-in... aha, and someone to write it? i think all this "automate whatspossible" business is stepping on some toes, and eventualy pulling the carpet under... here's some self-citation: >>svil wrote: >> But then - who would want to convert python funcs into sql >> expressions? The whole sql-book-course-consulting-admin world will >> collapse... (-:) have fun, and happy trying the autoload.py svil --~--~-~--~~~---~--~~ 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: Consistency with DB while modifying metadata
Anton V. Belyaev ha scritto: >> again, im not opposed to this feature and ill patch in an adequate >> (and fully unit-tested) implementation. but have you actually ever >> *had* this problem? or is it just hypothetical ? >> > > For example, a developer modifies the metadata and checks in. Another > developer updates and finds strange problems, having the old database. > Then, a SQL script to migrate the database schema should be provided with the check-in... Please, don't believe SQLAlchemy will help you win the Viet Nam war of computer science (*). It's supposed to help us survive. And it's a lot. I'm happily using autoload since the beginning, I've never used the Table() construct if not to replicate a couple of bugs and submit them. And I think my applications are simpler because of it, not in spite of it :-) http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx http://spyced.blogspot.com/2006/02/why-schema-definition-belongs-in.html (with comments from Mike Bayer, Ian Bicking and others) --~--~-~--~~~---~--~~ 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: Consistency with DB while modifying metadata
On Jul 25, 2007, at 12:02 PM, Anton V. Belyaev wrote: >> again, im not opposed to this feature and ill patch in an adequate >> (and fully unit-tested) implementation. but have you actually ever >> *had* this problem? or is it just hypothetical ? > > For example, a developer modifies the metadata and checks in. Another > developer updates and finds strange problems, having the old database. OK, so hypothetical. Im not saying it cant happen, im just saying, its not very *common*, nor is it terribly confusing to resolve if it does happen. --~--~-~--~~~---~--~~ 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: Consistency with DB while modifying metadata
On 25 июл, 19:38, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Jul 25, 2007, at 4:57 AM, Anton V. Belyaev wrote: > > > > > I believe this should be done somehow automatically. Because everyone > > needs this. There should be two separate disjoint options: > > > 1) Autoload, when working with previously created database for rapid > > start. Columns arent specified at all in Python code (SQLAlchemy). > > we do it > > > 2) Tables are specified in the code. Database tables might already > > exist and might not. And when issuing create_all(), all the situations > > should be handled correctly: > > 2a) If tables exist in DB and match Python-defined, ok. > > we do it (minus the "matching" part) > > > 2b) If tables do not exist in DB they are created, ok. > > we do it > > > 2c) If tables exist in DB and there is mismatch with Python-defined, > > an exception is raised. > > new feature. however, a lot of people wouldnt use this feature. For > one, its not a requirement that a Table object match whats in the > database. its often the case that the Table does not define every > column in the DB, or has different types, or is representing a view, > etc. Secondly, reflection of a table is a big performance hit and > not everyone wants that hit at the start of their appication or at > the start of each child process. Thirdly, people also may be > serializing their Table constructs in pickled files or similar so > that the reflection step can be bypassed, thus increasing > performance. To me its a "nice to have" but in practice it would > probably get in the way more often than not if it was "always on". > > Oh, now I see. Since the Tables and Columns are not required to match and sometimes it is useful, then my suggestion obviously shouldnt be the default. Thanks for the explanation! > > >> but why not just use autoload=True across the board in the first > >> place and eliminate the chance of any errors ? > > > 1) I dont know if tables exist. I might need to create them. > > that implies youve already defined explcit Colums in your Tables; > reflection is not needed in this case (except for this "verify the > table" feature). > > > 2) When they exist, autoloading them might cause inconsistency with > > SQLAlchemy-defined tables in sources. This is exactly what I am trying > > to avoid. > > again, im not opposed to this feature and ill patch in an adequate > (and fully unit-tested) implementation. but have you actually ever > *had* this problem? or is it just hypothetical ? For example, a developer modifies the metadata and checks in. Another developer updates and finds strange problems, having the old database. --~--~-~--~~~---~--~~ 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: Consistency with DB while modifying metadata
On Jul 25, 2007, at 4:57 AM, Anton V. Belyaev wrote: > > I believe this should be done somehow automatically. Because everyone > needs this. There should be two separate disjoint options: > > 1) Autoload, when working with previously created database for rapid > start. Columns arent specified at all in Python code (SQLAlchemy). we do it > 2) Tables are specified in the code. Database tables might already > exist and might not. And when issuing create_all(), all the situations > should be handled correctly: > 2a) If tables exist in DB and match Python-defined, ok. we do it (minus the "matching" part) > 2b) If tables do not exist in DB they are created, ok. we do it > 2c) If tables exist in DB and there is mismatch with Python-defined, > an exception is raised. new feature. however, a lot of people wouldnt use this feature. For one, its not a requirement that a Table object match whats in the database. its often the case that the Table does not define every column in the DB, or has different types, or is representing a view, etc. Secondly, reflection of a table is a big performance hit and not everyone wants that hit at the start of their appication or at the start of each child process. Thirdly, people also may be serializing their Table constructs in pickled files or similar so that the reflection step can be bypassed, thus increasing performance. To me its a "nice to have" but in practice it would probably get in the way more often than not if it was "always on". > >> but why not just use autoload=True across the board in the first >> place and eliminate the chance of any errors ? > > 1) I dont know if tables exist. I might need to create them. that implies youve already defined explcit Colums in your Tables; reflection is not needed in this case (except for this "verify the table" feature). > 2) When they exist, autoloading them might cause inconsistency with > SQLAlchemy-defined tables in sources. This is exactly what I am trying > to avoid. again, im not opposed to this feature and ill patch in an adequate (and fully unit-tested) implementation. but have you actually ever *had* this problem? or is it just hypothetical ? --~--~-~--~~~---~--~~ 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: Consistency with DB while modifying metadata
> t = Table('mytable', meta,
> Column(...)
>
> )
>
> someothermeta = MetaData()
> t2 = Table('mytable', someothermetadata, autoload=True,
> autoload_with=)
>
> assert t.compare(t2)
I believe this should be done somehow automatically. Because everyone
needs this. There should be two separate disjoint options:
1) Autoload, when working with previously created database for rapid
start. Columns arent specified at all in Python code (SQLAlchemy).
2) Tables are specified in the code. Database tables might already
exist and might not. And when issuing create_all(), all the situations
should be handled correctly:
2a) If tables exist in DB and match Python-defined, ok.
2b) If tables do not exist in DB they are created, ok.
2c) If tables exist in DB and there is mismatch with Python-defined,
an exception is raised.
If feel this is kind of natural. Though, I am not an expert in DB or
SQLAlchemy.
> but why not just use autoload=True across the board in the first
> place and eliminate the chance of any errors ?
1) I dont know if tables exist. I might need to create them.
2) When they exist, autoloading them might cause inconsistency with
SQLAlchemy-defined tables in sources. This is exactly what I am trying
to avoid.
--~--~-~--~~~---~--~~
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: Consistency with DB while modifying metadata
On 7/24/07, svilen <[EMAIL PROTECTED]> wrote: > > > >> assert t.compare(t2) > > > > > > yes i was hoping for such method (:-) > > > And the best will be if it can produce a list/ hierarchy of > > > differences, which then programaticaly can be iterated - and > > > checked and resolved or raised higher. > > > > > >> but why not just use autoload=True across the board in the first > > >> place and eliminate the chance of any errors ? > > > > > > what do u mean? The db-model of the app will not be the db-model > > > in the database - and the semantix will be gone. > > > Example: > > > from simplistic renaming of columns/ tables, to splitting a > > > class into clas+subclass (table into 2 joined-tables) etc > > > > ok, fine. anyway, feel free to add a trac ticket for this one - > > it'll need a volunteer. > ticket #680, have a look if what i wrote is what was meant in this > thread. > i may look into it after 2-3 weeks - unless someone does it ahead of > me ;P) I'm also interested in the feature but I probably won't work on it for a few months. I'll see how far you've come by that time :). -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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: Consistency with DB while modifying metadata
On 7/25/07, svilen <[EMAIL PROTECTED]> wrote: > > > i just saw there is some usagerecipe ModelUpdate in the wiki, may > > be a good start point: > > http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ModelUpdate > > > and this one: > http://www.sqlalchemy.org/trac/wiki/UsageRecipes/AutoCode As I said in the ticket, you might want to have a look at the migrate project, even though it's half defunct now, it's the best starting point, I guess. And the website seems down at the moment. I don't know if it's permanent or just a temporary failure. -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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: Consistency with DB while modifying metadata
> i just saw there is some usagerecipe ModelUpdate in the wiki, may > be a good start point: > http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ModelUpdate > and this one: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/AutoCode --~--~-~--~~~---~--~~ 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: Consistency with DB while modifying metadata
i just saw there is some usagerecipe ModelUpdate in the wiki, may be a good start point: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ModelUpdate > > >> assert t.compare(t2) > > > > > > yes i was hoping for such method (:-) > > > And the best will be if it can produce a list/ hierarchy of > > > differences, which then programaticaly can be iterated - and > > > checked and resolved or raised higher. > > > > > >> but why not just use autoload=True across the board in the > > >> first place and eliminate the chance of any errors ? > > > > > > what do u mean? The db-model of the app will not be the > > > db-model in the database - and the semantix will be gone. > > > Example: > > > from simplistic renaming of columns/ tables, to splitting a > > > class into clas+subclass (table into 2 joined-tables) etc > > > > ok, fine. anyway, feel free to add a trac ticket for this one - > > it'll need a volunteer. > > ticket #680, have a look if what i wrote is what was meant in this > thread. > i may look into it after 2-3 weeks - unless someone does it ahead > of me ;P) > > --~--~-~--~~~---~--~~ 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: Consistency with DB while modifying metadata
> >> assert t.compare(t2) > > > > yes i was hoping for such method (:-) > > And the best will be if it can produce a list/ hierarchy of > > differences, which then programaticaly can be iterated - and > > checked and resolved or raised higher. > > > >> but why not just use autoload=True across the board in the first > >> place and eliminate the chance of any errors ? > > > > what do u mean? The db-model of the app will not be the db-model > > in the database - and the semantix will be gone. > > Example: > > from simplistic renaming of columns/ tables, to splitting a > > class into clas+subclass (table into 2 joined-tables) etc > > ok, fine. anyway, feel free to add a trac ticket for this one - > it'll need a volunteer. ticket #680, have a look if what i wrote is what was meant in this thread. i may look into it after 2-3 weeks - unless someone does it ahead of me ;P) --~--~-~--~~~---~--~~ 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: Consistency with DB while modifying metadata
On Jul 24, 2007, at 11:07 AM, svilen wrote: > > On Tuesday 24 July 2007 17:30:27 Michael Bayer wrote: >> >> such a feature would make usage of table reflection, and then a >> comparison operation, along the lines of : >> >> ... >> >> assert t.compare(t2) > yes i was hoping for such method (:-) > And the best will be if it can produce a list/ hierarchy of > differences, which then programaticaly can be iterated - and checked > and resolved or raised higher. > >> but why not just use autoload=True across the board in the first >> place and eliminate the chance of any errors ? > what do u mean? The db-model of the app will not be the db-model in > the database - and the semantix will be gone. > Example: > from simplistic renaming of columns/ tables, to splitting a class > into clas+subclass (table into 2 joined-tables) etc ok, fine. anyway, feel free to add a trac ticket for this one - it'll need a volunteer. --~--~-~--~~~---~--~~ 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: Consistency with DB while modifying metadata
On Tuesday 24 July 2007 17:30:27 Michael Bayer wrote: > > such a feature would make usage of table reflection, and then a > comparison operation, along the lines of : > > ... > > assert t.compare(t2) yes i was hoping for such method (:-) And the best will be if it can produce a list/ hierarchy of differences, which then programaticaly can be iterated - and checked and resolved or raised higher. > but why not just use autoload=True across the board in the first > place and eliminate the chance of any errors ? what do u mean? The db-model of the app will not be the db-model in the database - and the semantix will be gone. Example: from simplistic renaming of columns/ tables, to splitting a class into clas+subclass (table into 2 joined-tables) etc --~--~-~--~~~---~--~~ 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: Consistency with DB while modifying metadata
On Jul 24, 2007, at 9:59 AM, Anton V. Belyaev wrote:
>
> Of course db modification is hard. It cant be done completely
> automatically. For now I would like SQLAlchemy just to signal somehow
> when its definitions are different from already existing db tables.
> When I do create_all() it checks anyway tables properties, but doesnt
> let me know when there is mismatch.
such a feature would make usage of table reflection, and then a
comparison operation, along the lines of :
t = Table('mytable', meta,
Column(...)
)
someothermeta = MetaData()
t2 = Table('mytable', someothermetadata, autoload=True,
autoload_with=)
assert t.compare(t2)
so if we had a comprehensive "compare()" method, we could probably
distill the above into something like:
table.verify()
but why not just use autoload=True across the board in the first
place and eliminate the chance of any errors ?
--~--~-~--~~~---~--~~
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: Consistency with DB while modifying metadata
On 24 июл, 17:34, svilen <[EMAIL PROTECTED]> wrote: > On Tuesday 24 July 2007 16:22:43 Anton V. Belyaev wrote: > > > Hey, > > > I believe there is a common approach to the situation, but I just > > dont know it. > > > Let say, I have some tables created in the DB using SQLAlchemy. > > Then I modify Python code, which describes the table (add a column, > > remove another column,...). What is the common way to handle this > > situation? I guess it would be good to have an exception raised > > when there is a mismatch between DB tables and Python-defined > > (using SQLAlchemy). > > Very soon i'll be in your situation (with hundreds of tables), so i'm > very interested if something comes up. > > it's in the todo list of dbcook. my idea so far is: > - automaticaly reverse engineer i.e. autoload the available > db-structure into some metadata. > - create another metadata as of current code > - compare the 2 metadatas, and based on some rules - ??? - > alter/migrate the DB into the new shape. > This has to be as automatic as possible, leaving only certain - if > any - decisions to the user. > Assuming that the main decision - to upgrade or not to upgrade - is > taken positive, and any locks etc explicit access is obtained. > > svil Of course db modification is hard. It cant be done completely automatically. For now I would like SQLAlchemy just to signal somehow when its definitions are different from already existing db tables. When I do create_all() it checks anyway tables properties, but doesnt let me know when there is mismatch. --~--~-~--~~~---~--~~ 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: Consistency with DB while modifying metadata
On Tuesday 24 July 2007 16:22:43 Anton V. Belyaev wrote: > Hey, > > I believe there is a common approach to the situation, but I just > dont know it. > > Let say, I have some tables created in the DB using SQLAlchemy. > Then I modify Python code, which describes the table (add a column, > remove another column,...). What is the common way to handle this > situation? I guess it would be good to have an exception raised > when there is a mismatch between DB tables and Python-defined > (using SQLAlchemy). Very soon i'll be in your situation (with hundreds of tables), so i'm very interested if something comes up. it's in the todo list of dbcook. my idea so far is: - automaticaly reverse engineer i.e. autoload the available db-structure into some metadata. - create another metadata as of current code - compare the 2 metadatas, and based on some rules - ??? - alter/migrate the DB into the new shape. This has to be as automatic as possible, leaving only certain - if any - decisions to the user. Assuming that the main decision - to upgrade or not to upgrade - is taken positive, and any locks etc explicit access is obtained. svil --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
