Re: Anyone besides me using stored procedures?
i rarely use stored procedures because everything I would put in one is easier/faster to handle in the webapp however i make **HEAVY** use of complicated views so that: - i don't deal much with multi-layered orm objects ( like looping though table.joins or table.join.joins ) - i don't deal with writing complex select statements in the orm - i only deal with the ORM to basically get and new/save single-level objects --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~--~~~~--~~--~--~---
Re: Anyone besides me using stored procedures?
On Thu, 2009-08-27 at 12:04 -0700, Jonathan Vanasco wrote: i rarely use stored procedures because everything I would put in one is easier/faster to handle in the webapp however i make **HEAVY** use of complicated views so that: - i don't deal much with multi-layered orm objects ( like looping though table.joins or table.join.joins ) - i don't deal with writing complex select statements in the orm - i only deal with the ORM to basically get and new/save single-level objects That's interesting, so are the db views created in sql outside of your app or is there a way to define db views through SQLAlchemy? If outside, how do you integrate that with your project as far as code control and so on? Iain --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~--~~~~--~~--~--~---
Re: Anyone besides me using stored procedures?
On Wed, Aug 26, 2009 at 2:56 AM, chris molliscmol...@objectlab.com wrote: There is a school of thought of that all operations should be handled in the ORM.. that all data should be accessed as in-memory 'objects' via (OQL or whatever). I guess that's nice and neat from a conceptual perspective.. but not very practical.. particularly when performance matters (which is like..um.. always). ORM's inevitably have to manage large graphs of objects in-memory.. which they are not really designed to do. It's a question of whether databases should be dumb or smart. Some DB admins insist that the proper place for validation is in the database itself, using triggers to ensure all data is meaningful. Some will not even allow clients access to the tables except through stored procedures. That does have an advantage when the same database is used by several clients in different languages. But to me stored procedures are a step back into the dark ages when languages were procedural and limited, with each platform having its own syntax and different features. We might as well put the entire application in the database and not use Python at all. Except that RDBMS scripting languages suck ass. Plus, I value the ability to switch to a different database platform without rewriting my application. ORMs are inherently kludgy, but the advantages of concurrency, ad hoc queries, low memory footprint, autoincrement columns, date comparisions in the database, etc, make RDMBS's worth their while unfortunately. So we're stuck with them. But the active record model just isn't efficient for some things, so trying to do everything through an ORM is nonsense. If you really want a database abstraction layer, it has to be more abstract than an ORM can provide. Otherwise you may be able to port it between different SQL databases, but not between SQL and non-SQL databases. Again, that depends on how sure you are that you'll want to stick to the same database for the long term. -- Mike Orr sluggos...@gmail.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~--~~~~--~~--~--~---
Re: Anyone besides me using stored procedures?
Jonathan Vanasco wrote: i rarely use stored procedures because everything I would put in one is easier/faster to handle in the webapp Might I ask what you're doing that's easier to handle in the webapp? In my current project, I've found most things easier to handle in the DB than in the app itself. -- Aurynn Shaw The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103 PostgreSQL Replication, Consulting, Custom Development, 24x7 support as...@commandprompt.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~--~~~~--~~--~--~---
Re: Anyone besides me using stored procedures?
On Aug 27, 3:19 pm, Iain Duncan iaindun...@telus.net wrote: That's interesting, so are the db views created in sql outside of your app or is there a way to define db views through SQLAlchemy? If outside, how do you integrate that with your project as far as code control and so on? I create/manage the db views outside of sqlalchemy I do two things with my apps: 1- I maintain a directory of the current schema with 1 table per- file 2- I have a db migrations directory where i have incremental changes to the structure, along with updating a specific db value I only 'reflect' tables in sqlalchemy, and treat views as such. the only thing that sqlalchemy needs to read off them is one or more fields to use as a primary key when you first reflect them. i like using sqlalchemy to instantiate/save and load objects, but that's about it. i know sql, and am way more comfortable writing it and fine-tuning queries than i am letting some orm do its magic and hope that command doesn't break by the next version. i should note something here -- I have a rather large centralized 'framework' that I run all my apps on, and it handles all that for me. i put 'framework' in quotes , because it is and it isn't one. its basically a standardized table structure that represents all core social-network / webapp fields, and a collection of controllers and models that can be inherited/subclassed to provide that functionality. it's basically an unreleased project of FindMeOn.com that was slated to be an open-sourcing of our own systems and function as an application bootstrap or 'embed into legacy system' method that we started developing to deploy services for large clients. but then we turned into a patent lawsuit. So the framework got put on a backburner and has slowly been tended to by myself and a few friends. but that handles much of this stuff. whenever i need to add a view, i just do: class view__myclass( SqlAlchemySmartObject ): __id= 'id' # or ['col1','col2'] and then my db initialization routine handles everything else. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~--~~~~--~~--~--~---
Re: Anyone besides me using stored procedures?
On Aug 27, 8:19 pm, Aurynn Shaw as...@commandprompt.com wrote: Might I ask what you're doing that's easier to handle in the webapp? In my current project, I've found most things easier to handle in the DB than in the app itself. One example would be things like cryptographic signing :) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~--~~~~--~~--~--~---
Re: Anyone besides me using stored procedures?
yes, we (trailbender.net) use a lot of stored procedures to abstract a lot of geo-spatial stuff that we do in PostGIS. I find that it's more straightforward to provide a facade to app service developers from the db (particularly for more difficult services)... which is nicely handled by SQLAlchemy... they just call a function and it returns a value or vector of values.. Trying to map geometry columns to the ORM is difficult and large unnecessary, IMO. The stored procedure provides the most efficient plan/mechanism for doing this. There is a school of thought of that all operations should be handled in the ORM.. that all data should be accessed as in-memory 'objects' via (OQL or whatever). I guess that's nice and neat from a conceptual perspective.. but not very practical.. particularly when performance matters (which is like..um.. always).ORM's inevitably have to manage large graphs of objects in-memory.. which they are not really designed to do. Data manipulation in ORM's tend to deteriorate into O(n) list management... which become re-factored anyway (ask Twitter about their experiences with the crappy Rails ORM). Rambiling a bit.. but my point is that the Pylons architecture (of which SQLAlchemy is a base compoment) allows a significant amount of flexibility to choose the most efficient deployment techniques for the specific situation. Stored Procedures support within the framework is just one example of its holistic design philosophy. On Wed, Aug 26, 2009 at 12:00 AM, Philip Jenvey pjen...@underboss.orgwrote: On Aug 25, 2009, at 7:15 PM, Jamie wrote: It seems that most people who adopt a MVC framework are in love with the ORM stuff. I'm just wondering if I'm the only one who insists on hand-writing my queries and sticking them in stored procedures? I guess it's because I come from a DBA/RDBMS development background and tend to make heavier use of the procedural logic available in a RDBMS, whereas most web developers use the DB purely as a bit bucket. I've tried to use a few ORMs, but have found them to be too magical and they get in the way of how I'm used to doing things. (In fact the primary reason I've adopted Pylons is because I can use it without an ORM). Is there any compelling reason why I should force myself to use SQLAlchemy? Stored procedures' popularity have mostly waned because they're not portable across dbs ORMs are a different issue entirely. I can understand having issue with ORMs for generating sub-standard schemas and queries. You shouldn't have that problem with SQLAlchemy -- it avoids being too magical and at heart its interests are aligned with yours as a DBA. The marketing material on its web page claiming it's 'dba approved' and 'unopinionated' is really true. It's not just an ORM, it also provides a SQL expression layer. You can generate queries however you like them. You can also always fall back to sending raw SQL through SQLAlchemy, and can even generate mapped objects from their results. Also the core SQLAlchemy ORM follows the data mapper pattern for better separation between the application's object layer and the database layer. If you're not planning to write everything as stored procedures it'll make your life as a DBA easier, not harder -- Philip Jenvey --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~--~~~~--~~--~--~---
Re: Anyone besides me using stored procedures?
I'm not using stored procedures but I used too. I have a schema that's quite relational and I don't know if its me or what sometimes orm doesn't always suit. Prolly me thinking more relational than oo at the time the basic schema was made That said sqlalchemy helps me a lot in both orm and when I need to sql queries for a report or some such. When I started using sqlalchemy it was because of uncertainty of future enviroments the application would be in and what it would have to use as a db. Pylons was chosen in my mind anyway, because there was more python savy employees than java folk in this rag tag gig I find my self in. I was also a bit tired of java at the time. No regrets so far. It was definatly easier to get going quick, in our situation. On Aug 26, 2009 12:00 PM, Philip Jenvey pjen...@underboss.org wrote: On Aug 25, 2009, at 7:15 PM, Jamie wrote: It seems that most people who adopt a MVC framework... Stored procedures' popularity have mostly waned because they're not portable across dbs ORMs are a different issue entirely. I can understand having issue with ORMs for generating sub-standard schemas and queries. You shouldn't have that problem with SQLAlchemy -- it avoids being too magical and at heart its interests are aligned with yours as a DBA. The marketing material on its web page claiming it's 'dba approved' and 'unopinionated' is really true. It's not just an ORM, it also provides a SQL expression layer. You can generate queries however you like them. You can also always fall back to sending raw SQL through SQLAlchemy, and can even generate mapped objects from their results. Also the core SQLAlchemy ORM follows the data mapper pattern for better separation between the application's object layer and the database layer. If you're not planning to write everything as stored procedures it'll make your life as a DBA easier, not harder -- Philip Jenvey --~--~-~--~~~---~--~~ You received this message because you are sub... --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~--~~~~--~~--~--~---
Re: Anyone besides me using stored procedures?
Jamie wrote: It seems that most people who adopt a MVC framework are in love with the ORM stuff. I'm just wondering if I'm the only one who insists on hand-writing my queries and sticking them in stored procedures? My current project is heavily invested in stored procedures, to the tune of about 10k lines of SQL. Like you, I'm more coming from a DBA/RDBMS standpoint, and find that ORMs never do things properly. I guess it's because I come from a DBA/RDBMS development background and tend to make heavier use of the procedural logic available in a RDBMS, whereas most web developers use the DB purely as a bit bucket. I've tried to use a few ORMs, but have found them to be too magical and they get in the way of how I'm used to doing things. (In fact the primary reason I've adopted Pylons is because I can use it without an ORM). Is there any compelling reason why I should force myself to use SQLAlchemy? As others have said, see if its other tools fit your needs. If not, then it's not for you. It wasn't for me, so I built Simpycity, which does some nice abstractions entirely using raw queries. The best part about Pylons is, if a component doesn't work for you, you can replace it without much trouble at all. Regards, -- Aurynn Shaw The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103 PostgreSQL Replication, Consulting, Custom Development, 24x7 support as...@commandprompt.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~--~~~~--~~--~--~---
Re: Anyone besides me using stored procedures?
Philip Jenvey wrote: On Aug 25, 2009, at 7:15 PM, Jamie wrote: I guess it's because I come from a DBA/RDBMS development background and tend to make heavier use of the procedural logic available in a RDBMS, whereas most web developers use the DB purely as a bit bucket. I've tried to use a few ORMs, but have found them to be too magical and they get in the way of how I'm used to doing things. (In fact the primary reason I've adopted Pylons is because I can use it without an ORM). Is there any compelling reason why I should force myself to use SQLAlchemy? It's not just an ORM, it also provides a SQL expression layer. You can generate queries however you like them. You can also always fall back to sending raw SQL through SQLAlchemy, and can even generate mapped objects from their results. Also the core SQLAlchemy ORM follows the data mapper pattern for better separation between the application's object layer and the database layer. If you're not planning to write everything as stored procedures it'll make your life as a DBA easier, not harder Having recently switched to SQLAlchemy, I'll chime in with my $.02. I don't use the orm parts of it since I'm using my db as a blob store. I guess that puts me in the bitbucket category. Still, I rolled my own sql generation module and it soon grew fairly unwieldy. I switched to using SQLAlchemy's sql expression layer a few months back and things have been simplified a lot. I still use my own solution to map blob actions to sql operations, but the queries are generated through SQLAlchemy. It works very effectively, I've only had to replace a couple of queries with hand-generated ones. At first it seemed awkward to have the schema definition in my application, but I've found it to be easier to migrate between schema versions. - kochhar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~--~~~~--~~--~--~---
Anyone besides me using stored procedures?
It seems that most people who adopt a MVC framework are in love with the ORM stuff. I'm just wondering if I'm the only one who insists on hand-writing my queries and sticking them in stored procedures? I guess it's because I come from a DBA/RDBMS development background and tend to make heavier use of the procedural logic available in a RDBMS, whereas most web developers use the DB purely as a bit bucket. I've tried to use a few ORMs, but have found them to be too magical and they get in the way of how I'm used to doing things. (In fact the primary reason I've adopted Pylons is because I can use it without an ORM). Is there any compelling reason why I should force myself to use SQLAlchemy? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~--~~~~--~~--~--~---
Re: Anyone besides me using stored procedures?
On 26/08/2009, at 12:15 PM, Jamie wrote: It seems that most people who adopt a MVC framework are in love with the ORM stuff. I'm just wondering if I'm the only one who insists on hand-writing my queries and sticking them in stored procedures? A major part of the difference tends to come from use of commercial vs open source RDBMS. I find that use of stored procedures is much more popular with people who use (or come from a background of using) Oracle or MS SQL as opposed to PostgreSQL or MySQL. I work beside a group of .NET developers who prefer stored procedures. My preference is to keep model logic in the application layer (with or without ORM) as that is just what I grew up with. I guess it's because I come from a DBA/RDBMS development background and tend to make heavier use of the procedural logic available in a RDBMS, whereas most web developers use the DB purely as a bit bucket. I've tried to use a few ORMs, but have found them to be too magical and they get in the way of how I'm used to doing things. (In fact the primary reason I've adopted Pylons is because I can use it without an ORM). Is there any compelling reason why I should force myself to use SQLAlchemy? There's certainly no compelling reason to use SQLAlchemy's ORM layer if it doesn't help your development. However, it is worth pointing out that SQLAlchemy provides an excellent database connection pool management layer and SQL query construction toolkit, both of which can be used without touching the ORM stuff. So I suggest that even if you ignore the ORM layer (which is perfectly reasonable) you look at the rest of what SQLAlchemy provides. Cheers, Chris --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~--~~~~--~~--~--~---
Re: Anyone besides me using stored procedures?
I'm the same way. But if other people need to work the code, I recommend sticking with an ORM unless they think like you. Sent from my iPhone On Aug 25, 2009, at 7:15 PM, Jamie jjbe...@gmail.com wrote: It seems that most people who adopt a MVC framework are in love with the ORM stuff. I'm just wondering if I'm the only one who insists on hand-writing my queries and sticking them in stored procedures? I guess it's because I come from a DBA/RDBMS development background and tend to make heavier use of the procedural logic available in a RDBMS, whereas most web developers use the DB purely as a bit bucket. I've tried to use a few ORMs, but have found them to be too magical and they get in the way of how I'm used to doing things. (In fact the primary reason I've adopted Pylons is because I can use it without an ORM). Is there any compelling reason why I should force myself to use SQLAlchemy? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~--~~~~--~~--~--~---
Re: Anyone besides me using stored procedures?
On Aug 25, 2009, at 7:15 PM, Jamie wrote: It seems that most people who adopt a MVC framework are in love with the ORM stuff. I'm just wondering if I'm the only one who insists on hand-writing my queries and sticking them in stored procedures? I guess it's because I come from a DBA/RDBMS development background and tend to make heavier use of the procedural logic available in a RDBMS, whereas most web developers use the DB purely as a bit bucket. I've tried to use a few ORMs, but have found them to be too magical and they get in the way of how I'm used to doing things. (In fact the primary reason I've adopted Pylons is because I can use it without an ORM). Is there any compelling reason why I should force myself to use SQLAlchemy? Stored procedures' popularity have mostly waned because they're not portable across dbs ORMs are a different issue entirely. I can understand having issue with ORMs for generating sub-standard schemas and queries. You shouldn't have that problem with SQLAlchemy -- it avoids being too magical and at heart its interests are aligned with yours as a DBA. The marketing material on its web page claiming it's 'dba approved' and 'unopinionated' is really true. It's not just an ORM, it also provides a SQL expression layer. You can generate queries however you like them. You can also always fall back to sending raw SQL through SQLAlchemy, and can even generate mapped objects from their results. Also the core SQLAlchemy ORM follows the data mapper pattern for better separation between the application's object layer and the database layer. If you're not planning to write everything as stored procedures it'll make your life as a DBA easier, not harder -- Philip Jenvey --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~--~~~~--~~--~--~---
