Some consider good practice to actually never expose directly the tables to the users but do this through stored procedures and views only. In this way you achieves 2 major things, complete control and security. Users have no permission to select/update/insert/delete on the tables but they are given permission to stored procedures and views that will do that instead. A side but important advantage is that using a stored procedure or a view in application code is far more cleaner and easier than a select statement that can be cumbersome at times to write and maintain. A stored procedure also embeds the business logic and if/when it changes you don't have to touch the application code but only the stored procedure itself, providing that the parameters and column names and number stays the same. Even if they change is far more easier to maintain that then the SQL code which they replace. Beside that stored procedure code is compiled and cashed so they perform much better than ad-hoc queries. Unfortunately very few apply these principles when they do database design and development.
With triggers is another story. In my opinion triggers should be used only when there is NO alternative. The problems with triggers is that they are "hidden" and they can create extremely serious problems performance wise. Imagine an insert of million rows and with each one some complicated code has to be executed in triggers especially if querying some other tables is involved. You have to be careful here as in the short term a trigger might seem the easiest and fastest solution but in the long term you might run into serious problems like very low performance or even locks and/or timeouts. That doesn't mean we shouldn't use them though but as I said we have to be careful. A very good way of avoiding triggers is using stored procedures for insert/update/delete where you can adapt the code to perform much better that you would do it in a trigger and still have the same control and effect on the table. Unfortunately usually what happens is this, web/application developers write queries (select/insert/update/ delete statements at times very complicated and low performing because of their limited SQL experience) which they embed in their code or, in the happy cases, in some external files (XML) and later when they come to change or modify the functionality will add triggers on tables just to let applications intact. In time this practice develops into a bloated database with poorer and poorer performance as the time goes by. In the end they will have to revamp much or everything just to optimize the database code which translates in more cost and wasted time, and only because they treated superficially the database design and development part in the first place. I've seen tables with 10 or even more triggers on them, which in my opinion should be "forbidden". :o) Cheers. On Sep 5, 7:25 am, Joshua Russo <josh.r.ru...@gmail.com> wrote: > Great site! thanks > > With my SQL experience, your comment about database coding seems to ring > true. The database is a much different environment, with > different paradigms, than a standard application environment. When you > really need database coding you know it (kind of like meta programming). > > So far from my experience has been, the times I found we needed database > programming were pretty simply speed related, so difficult to distinguish > from application logic. What types of situations do others find useful for > stored procedures and triggers? > > > > On Sat, Sep 5, 2009 at 9:57 AM, Zberteoc <zbert...@gmail.com> wrote: > > > This is a common mistake almost all non SQL developers make thinking > > in procedural/programming language terms in regards wit SQL and > > database coding. If you're asking me there is nothing cool in the > > feature of creating stored procedures in other than the SQL language. > > MS-SQL introduced that with 2005 version, CLR integration, but I is > > hardly used for one very simple reason it is NOT really necessary. SQL > > code needs to be understood first and only then look elsewhere. > > Anyways, in terms of comparison PgSQL vs MySQL here is a very detailed > > wiki: > > >http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL > > > I have never used PgSQL but i wouldn't hesitate to use it if I needed > > it. All DBMS these days are robust and mature enough to be able to > > rely on them. It comes in the end to what you prefer, how comfortable > > you feel and ease of use, rather than how many terrabytes they can > > deal with as the features lists are more and more the same for all of > > them. Support and online community is also very important, probably > > the most if you're novice in both, and here MySQL prevails as it is > > far more popular. > > > Cheers. > > > On Sep 4, 10:46 pm, Joshua Russo <josh.r.ru...@gmail.com> wrote: > > > Wow, that's a cool trick to be able to implement stored procedures in > > > different languages. I might actually use them more if I could do > > everything > > > in the same language as the application. > > > I only looked quickly through the PostgreSQL docs for subqueries. Thanks > > for > > > the heads up. > > > > As far as the Gis functionality goes, I could see a database like that > > > needing some serious horse power if it became popular. Any thoughts on > > how > > > that would reconcile with the weakness in replication? I suppose most Gis > > > systems are more for out put than input so the slow replication might not > > > really matter that much. > > > > On Sat, Sep 5, 2009 at 1:17 AM, Siemster <gregory.si...@pca.state.mn.us > > >wrote: > > > > > PostgreSQL does support subqueries in the from clause, however iirc, > > > > the subquerys require an alias. > > > > > If you decide to do geo then the PostGis addon to Postgres is very > > > > nice. > > > > > Another nice capability in PostgreSQL is that you can use different > > > > languages for writing your stored procedures (should you wish to use > > > > them). Some of the available languages (in addition to PL/pgSQL) are > > > > Perl, Python, Tcl, PHP, Ruby, R, Scheme, and Java. > > > > > Whether you choose to use Postgres or not, I'd recommend at least > > > > looking at it. There is even a live cd (which I have not tried) at > > > >http://www.postgresql.org/download/whichlets you try PostgreSQL out > > > > without having to install it. > > > > > On Sep 4, 7:38 pm, Joshua Russo <josh.r.ru...@gmail.com> wrote: > > > > > On Fri, Sep 4, 2009 at 11:07 PM, Tim Chase > > > > > <django.us...@tim.thechases.com>wrote: > > > > > > > > I personally don't have any experience with PostgreSQL and I'm > > > > generally > > > > > > > working in a mixed MS and Linux environment. I'm interested to > > hear > > > > > > peoples > > > > > > > views on the pluses and minuses of the two different systems. I'm > > a > > > > bit > > > > > > of a > > > > > > > query geek too. How does that play in? I know in MySQL there are > > > > > > limitations > > > > > > > on where you can use subqueries. Is that true with PostgreSQL? > > (Ya I > > > > > > could > > > > > > > just look that one up but it's just an example.) > > > > > > > I did a writeup of MySQL vs. PostgreSQL a while back: > > >http://www.mail-archive.com/django-users@googlegroups.com/msg70188.html > > > > > > > Most of the issues still stand -- though I understand MySQL now > > > > > > has native(ish) support for Geo information (check the GeoDjango > > > > > > code to see if it supports the MySQL Geo implementation -- last I > > > > > > checked the source it was Oracle & PostgreSQL only). > > > > > > > To answer your direct question, PostgreSQL has long-standing > > > > > > support for all kinds of crazy sub-queries. MySQL has added most > > > > > > of those abilities over time. This used to be a deal-breaker for > > > > > > me, making Postgres the clear winner. Now they're about even. > > > > > > > Lastly, my closing arguments in that link still stand -- if you > > > > > > don't have a pressing need to choose one or the other, code & > > > > > > test for both. > > > > > > Good point on geo side side of things. > > > > > > One place I have found subqueries very useful is in the From. > > > > Functionally > > > > > identical to a view but you don't need to clutter the database with > > > > rarely > > > > > used views. That and you can use variables. If you really wanted to > > get > > > > > fancy you can even nest them. It can save a lot on application logic > > and > > > > > produce some interesting reports. I don't believe either of our > > friends > > > > here > > > > > support them though. That is one feature I would love to see. > > > > > > I tend to agree with your closing arguments. I try to stay away from > > any > > > > > DBMS unique functionality. I very rarely even find much of a need for > > > > > triggers and/or stored procedures. (But they can come in > > exceptionally > > > > handy > > > > > when turning 10s of 1000s of rows of un-normalized data, into close > > to a > > > > > million rows of normalized. Done in a matter of minutes!) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~----------~----~----~----~------~----~------~--~---
