Thread: Triggers, Stored Procedures, PHP. was: Re: PostgreSQL Advocacy, Thoughts and Comments

On 28/11/2003 17:10 Jason Tesser wrote:
> [snip]
>
> I completely disagree.  I do a lot of programming with PHP and the
> features
> of Postgres come in handy.  Let me give you an example of just some
> basic things.  Triggers!  Why should I have to write insert and update
> triggers in the logic (PHP) if I can handle it at the database level.
> Sql
> is 10x as fast as the language.  Better to handle what you can at the
> database
> level. Same with views and stored procedures.

Stored procedures can be a 2-edged sword. They can lead to business logic
being scattered between the persistence layer and the business layer.
Thats not good for maintaining the application 3 years down the line.
Triggers can also cause maintenance problems. Its so easy to forget/fail
to document that inserting a record into table x causes column y of table
z to be updated. Be careful how and where you use these features as they
can come back to bite you!

> MySQL cannot even handle
> sub-queries yet. I also use Python for standalone interfaces to the data.
>
> Why should I not be able to use the same views and triggers etc  in there
> that I use for my web apps.  PHP is quite powerful if used correctly.

You are, of course, free to do whatever want. But if you have to use
features of the database to compensate for inadequacies in your
programming language maybe you should be using another language?

> Java has its own issues and I am not sure it is as far supiour as you
> are claming it is.  But that is not for this dscussion.

I'm not aware of any "issues" with Java (unless you mean Swing ;)).
> MySQL may be more
> popular with (cheap) web hosting places but that doesn't mean it is the
> best
> or that Postgres wouldn't serve better even in this area.  I am glad
> to see the article written for PHP mag as Postgres would help alot of PHP
> guys that are using MySQL.

Much of the populatity of MySQL seems to stem from PHPs out-of-the-box
support for it. With the MySQL client library license change, this
situation will probably change. There was a long thread about this earlier
this year. Check the archives.


--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

> > Java has its own issues and I am not sure it is as far supiour as you
> > are claming it is.  But that is not for this dscussion.
>
> I'm not aware of any "issues" with Java (unless you mean Swing ;)).

I know for one thing - Java's lack of support for returning tuples is
hugely annoying.

Jon

Paul Thomas wrote:
>
>
>
> On 28/11/2003 17:10 Jason Tesser wrote:
> > [snip]
> >
> > MySQL cannot even handle
> > sub-queries yet. I also use Python for standalone interfaces to
> the data.
> >
> > Why should I not be able to use the same views and triggers etc
>  in there
> > that I use for my web apps.  PHP is quite powerful if used correctly.
>
> You are, of course, free to do whatever want. But if you have to use
> features of the database to compensate for inadequacies in your
> programming language maybe you should be using another language?

This doesn't even make sense in the context of Jasons remark.

>
> > Java has its own issues and I am not sure it is as far supiour as you
> > are claming it is.  But that is not for this dscussion.
>
> I'm not aware of any "issues" with Java (unless you mean Swing ;)).
> > MySQL may be more
> > popular with (cheap) web hosting places but that doesn't mean it is the
> > best
> > or that Postgres wouldn't serve better even in this area.  I am glad
> > to see the article written for PHP mag as Postgres would help
> alot of PHP
> > guys that are using MySQL.
>
> Much of the populatity of MySQL seems to stem from PHPs out-of-the-box
> support for it. With the MySQL client library license change, this
> situation will probably change. There was a long thread about
> this earlier
> this year. Check the archives.
>
>
This is incorrect.  The embedded mysql client library was not added until
PHP4.0 RC1.  PHP's popularity existed long before this.  The real culprit
causing the popularity of MySQL was it's ubiquity among hosting providers
and the virtual non-existence of PG in that arena.  If PG had been more
friendly to shared hosting environments, perhaps this situation wouldn't
have arisen.  Blaming PHP for this situation (and your other comments) show
extreme prejudice.

From: "Paul Thomas" <paul@tmsl.demon.co.uk>:
> Stored procedures can be a 2-edged sword. They can lead to business logic
> being scattered between the persistence layer and the business layer.
> Thats not good for maintaining the application 3 years down the line.
> Triggers can also cause maintenance problems. Its so easy to forget/fail
> to document that inserting a record into table x causes column y of table
> z to be updated. Be careful how and where you use these features as they
> can come back to bite you!

It is all how you organize your app.  Stored proceedures are extremely
useful when they represent a unified API for accessing parts of the
database.  Word of advice:  Keep the database self-contained.  If all you
want is object persistance, then why non use Berkeley Database?  It is even
transactional.  The point of having an RDBMS is to provide more flexibility
than a simple persistance store.  When used sensibly, stored proceedures are
extremely simplifying, not the other way arround.


> >
> > Why should I not be able to use the same views and triggers etc  in
there
> > that I use for my web apps.  PHP is quite powerful if used correctly.
>
> You are, of course, free to do whatever want. But if you have to use
> features of the database to compensate for inadequacies in your
> programming language maybe you should be using another language?

I don't think Jason was compensating for weaknesses in the language-- I
think that he was asking why he woudln't want to build into the database the
universal functions accessed by multiple applications.  And he would be
right in trying to do so.

Let me give you an example:  One of the large projects I maintain is HERMES
(http://hermes.sourceforge.net).  Hermes relies on its own user and
permissions catalogs in order to provide a consistant administrative
interface across database managers and simplify the task of assigning
permissions to users and groups.  The differences in syntax can them be
handled in wrapper layers, etc.

However, it makes sense to try to wrap these catalogs using stored
proceedures so that third-party apps don't necessarily need to be aware of
the structure of the catalogs when assigning permissions.  This way, too,
the db users' catalog and the user catalog in the RDBMS can be guaranteed to
be in sync.  It will also allow me eventually to directly enforce
permissions using triggers rather than rely on the RDBMS model (useful in
shared hosting environments).

>
> > Java has its own issues and I am not sure it is as far supiour as you
> > are claming it is.  But that is not for this dscussion.
>
> I'm not aware of any "issues" with Java (unless you mean Swing ;)).

Every language has "issues."  This is not the time or place for a
development environemnt holy war ;-)  But--- PHP and Python all the way ;-)

>
> Much of the populatity of MySQL seems to stem from PHPs out-of-the-box
> support for it. With the MySQL client library license change, this
> situation will probably change. There was a long thread about this earlier
> this year. Check the archives.
>
Putting the cart before the horse.  MySQL is far easier to administer in a
shared hosting environment.  Maybe one of these days, I will put together a
package for managing PostgreSQL accounts in this way.  If there is interest,
please email me off-list and we can get started.  I don't expect MySQL's
dominance to change until we can offer an easy-to-administer alternative for
these environments.

Best Wishes,
Chris Travers

El Sáb 29 Nov 2003 08:45, Paul Thomas escribió:
> On 28/11/2003 17:10 Jason Tesser wrote:
> > [snip]
> >
> > I completely disagree.  I do a lot of programming with PHP and the
> > features
> > of Postgres come in handy.  Let me give you an example of just some
> > basic things.  Triggers!  Why should I have to write insert and update
> > triggers in the logic (PHP) if I can handle it at the database level.
> > Sql
> > is 10x as fast as the language.  Better to handle what you can at the
> > database
> > level. Same with views and stored procedures.
>
> Stored procedures can be a 2-edged sword. They can lead to business logic
> being scattered between the persistence layer and the business layer.
> Thats not good for maintaining the application 3 years down the line.
> Triggers can also cause maintenance problems. Its so easy to forget/fail
> to document that inserting a record into table x causes column y of table
> z to be updated. Be careful how and where you use these features as they
> can come back to bite you!

Please, don't blame the feature. Blame the poor documentation that the
programmer did.

If you program the right way from the beginning, Stored Procedures, Views,
Triggers, etc. are great for getting good performace applications. And it is
the way DB Apps Designers should work, IMHO.

--
 10:42:02 up 3 days, 17:06,  1 user,  load average: 0.01, 0.02, 0.06
-----------------------------------------------------------------
Martín Marqués        | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica  |  DBA, Programador, Administrador
             Universidad Nacional
                  del Litoral
-----------------------------------------------------------------

On 29/11/2003 12:53 Rod K wrote:
> [snip]
> > You are, of course, free to do whatever want. But if you have to use
> > features of the database to compensate for inadequacies in your
> > programming language maybe you should be using another language?
>
> This doesn't even make sense in the context of Jasons remark.

It makes sense to me. Maybe English is a write-only language after-all ;-)

> [snip]
> > Much of the populatity of MySQL seems to stem from PHPs out-of-the-box
> > support for it. With the MySQL client library license change, this
> > situation will probably change. There was a long thread about
> > this earlier
> > this year. Check the archives.
> >
> >
> This is incorrect.  The embedded mysql client library was not added until
> PHP4.0 RC1.

Thats not the impression I got from from reading the thread concening
MySQL/PHP et al earlier this year. But whatever the exact historical
details are, the current popularity of MySQL as the back-end for PHP apps
is well-documented.

> PHP's popularity existed long before this.  The real culprit
> causing the popularity of MySQL was it's ubiquity among hosting providers
> and the virtual non-existence of PG in that arena.  If PG had been more
> friendly to shared hosting environments, perhaps this situation wouldn't
> have arisen.

I think culprit is a bit strong. If I were in the hosting business a few
years ago I would probably have made the same decision on the belief that
most people would not need anything more than a few rudimentary database
features for which MySQL would suffice.
> Blaming PHP for this situation (and your other comments) show
> extreme prejudice.

Where have I blamed PHP for anything? Nowhere. As for extreme predudice,
if you wish to grosely mis-interpret my opinions of the limitations of cgi
scripts in that way, thats fine by me.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Paul Thomas wrote:
>
>
> On 29/11/2003 12:53 Rod K wrote:
> > [snip]
> > > You are, of course, free to do whatever want. But if you have to use
> > > features of the database to compensate for inadequacies in your
> > > programming language maybe you should be using another language?
> >
> > This doesn't even make sense in the context of Jasons remark.
>
> It makes sense to me. Maybe English is a write-only language after-all ;-)
>

Jason wrote:
    "I also use Python for standalone interfaces to the data. Why should I not
be able to use the same views and triggers etc  in there
 that I use for my web apps."

Now, how is your comment about "language inadequacies" relevent?  Wanting to
use the RDBMS to handle business logic makes perfect sense, especially when
multiple front ends will be accessing.  This has nothing to do with
perceived "inadequacies."



> > [snip]
> > > Much of the populatity of MySQL seems to stem from PHPs out-of-the-box
> > > support for it. With the MySQL client library license change, this
> > > situation will probably change. There was a long thread about
> > > this earlier
> > > this year. Check the archives.
> > >
> > >
> > This is incorrect.  The embedded mysql client library was not
> added until
> > PHP4.0 RC1.
>
> Thats not the impression I got from from reading the thread concening
> MySQL/PHP et al earlier this year. But whatever the exact historical
> details are, the current popularity of MySQL as the back-end for PHP apps
> is well-documented.

It is your choice to believe your impressions of those posts or do the
research.  The popularity of MySQL as a back-end for PHP has to do with the
popularity of MySQL. Not because of "PHPs out-of-the-box support for it."
In fact, the popularity of MySQL as a back-end LED TO the incorporation of
the MySQL client API in PHP.  You've reversed cause and effect.  The
popularity of MySQL stems from the popularity of MySQL among hosting
providers.  The popularity of MySQL among hosting providers comes from the
ease of administration of MySQL vs Postgres in those environments.

>
> > PHP's popularity existed long before this.  The real culprit
> > causing the popularity of MySQL was it's ubiquity among hosting
> providers
> > and the virtual non-existence of PG in that arena.  If PG had been more
> > friendly to shared hosting environments, perhaps this situation wouldn't
> > have arisen.
>
> I think culprit is a bit strong. If I were in the hosting business a few
> years ago I would probably have made the same decision on the belief that
> most people would not need anything more than a few rudimentary database
> features for which MySQL would suffice.
> > Blaming PHP for this situation (and your other comments) show
> > extreme prejudice.
>
> Where have I blamed PHP for anything? Nowhere.

You quoted yourself and still missed it.  Here it is again:

"Much of the populatity of MySQL seems to stem from PHPs out-of-the-box
support for it."

 As for extreme predudice,
> if you wish to grosely mis-interpret my opinions of the
> limitations of cgi
> scripts in that way, thats fine by me.
>

Coloring reality with your "opinions" is prejudicial, like it or not.

Quoted as gospel by various people:
>> MySQL cannot even handle sub-queries yet.

BTW, is that really still true?  I thought they had at least some
support for subqueries by now.

            regards, tom lane

They do in alpha quality 4.1.x series I believe.

http://www.mysql.com/doc/en/MySQL_4.1_Nutshell.html

Regards

T.


Tom Lane wrote:

Quoted as gospel by various people: 

MySQL cannot even handle sub-queries yet.     

BTW, is that really still true?  I thought they had at least some
support for subqueries by now.
		regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend 

"Rod K" <rod@23net.net> writes:
> Paul Thomas wrote:
>> Much of the populatity of MySQL seems to stem from PHPs out-of-the-box
>> support for it.

> This is incorrect.  The embedded mysql client library was not added until
> PHP4.0 RC1.  PHP's popularity existed long before this.  The real culprit
> causing the popularity of MySQL was it's ubiquity among hosting providers
> and the virtual non-existence of PG in that arena.  If PG had been more
> friendly to shared hosting environments, perhaps this situation wouldn't
> have arisen.

You are both engaging in the most blatant form of historical
revisionism.  Of course PHP's support for MySQL didn't drive MySQL
adoption --- it was the other way around, PHP adapted to MySQL because
that was what was out there.  I think "friendly to shared hosting
environments" is a made-up reason as well.  The real reason PG lost
mindshare to MySQL in the early web days is that at the time, PG was
hard to install, somewhat buggy, and poorly documented.  (Which was not
surprising considering that none of these mattered much in its original
academic environment.)  MySQL didn't do much, maybe, but what it could
do it did pretty well and without install/learning curve hassles.  We
had mostly caught up on those criteria by perhaps 7.1 or 7.2, but the
mindshare gap remains.

            regards, tom lane

Tom,
>
>
> "Rod K" <rod@23net.net> writes:
> > Paul Thomas wrote:
> >> Much of the populatity of MySQL seems to stem from PHPs out-of-the-box
> >> support for it.
>
> > This is incorrect.  The embedded mysql client library was not
> added until
> > PHP4.0 RC1.  PHP's popularity existed long before this.  The
> real culprit
> > causing the popularity of MySQL was it's ubiquity among hosting
> providers
> > and the virtual non-existence of PG in that arena.  If PG had been more
> > friendly to shared hosting environments, perhaps this situation wouldn't
> > have arisen.
>
> You are both engaging in the most blatant form of historical
> revisionism.

I am?  I mis-spoke (see below) but my point was clear and you stated the
same.

Of course PHP's support for MySQL didn't drive MySQL
> adoption --- it was the other way around, PHP adapted to MySQL because
> that was what was out there.

My point.

 I think "friendly to shared hosting
> environments" is a made-up reason as well.  The real reason PG lost
> mindshare to MySQL in the early web days is that at the time, PG was
> hard to install, somewhat buggy, and poorly documented.

"...friendly to shared hosting environments" was not exactly what I meant to
say. It WAS a PITA for HOSTING PROVIDERS for exactly the reasons you state,
which is why MySQL was usually chosen.

  (Which was not
> surprising considering that none of these mattered much in its original
> academic environment.)  MySQL didn't do much, maybe, but what it could
> do it did pretty well and without install/learning curve hassles.  We
> had mostly caught up on those criteria by perhaps 7.1 or 7.2, but the
> mindshare gap remains.
>
Agreed

I agree... as a newcomer to PG from MySQL and a web hosting provider, PG is no more difficult to admin than MySQL IMHO.  Just requires a little understanding.  But with tools like PGAdmin, even the understanding required is reduced.

I beleive that MySQL just achieved a snowball effect, and that's just the way it happened.  PG is easy to install.  Although one hold-up has to be the fact that I for instance can't run PG on my works laptop that runs XP (without significant hassle).  My laptop is where I prototype all of my applications (if I am able) and my life would be sooo much easier if I could run a PG server alongside my various other bits of IDE.  I tend to be a little more tenacious than most, I'm sure if I find it hassle, then others do too.   Many developers that I know of, use a windows machine and dev tools, to develop what will eventually be a Unix app.  Just easier that way sometimes.

Credit cards are easy to use, in fact their darn difficult not to use.  MySQL is similar to this, when they announce a new release, the release is available not only as source, but .exe and a dozen other platforms all waiting to be used.  So it's a really easy decsion to make if you know that you can run your DB anywhere.  Install it from RPM, or .exe.

Just my 2 cents again.

T.


Tom Lane wrote:

"Rod K" <rod@23net.net> writes: 

Paul Thomas wrote:   

Much of the populatity of MySQL seems to stem from PHPs out-of-the-box
support for it.     

This is incorrect.  The embedded mysql client library was not added until
PHP4.0 RC1.  PHP's popularity existed long before this.  The real culprit
causing the popularity of MySQL was it's ubiquity among hosting providers
and the virtual non-existence of PG in that arena.  If PG had been more
friendly to shared hosting environments, perhaps this situation wouldn't
have arisen.   

You are both engaging in the most blatant form of historical
revisionism.  Of course PHP's support for MySQL didn't drive MySQL
adoption --- it was the other way around, PHP adapted to MySQL because
that was what was out there.  I think "friendly to shared hosting
environments" is a made-up reason as well.  The real reason PG lost
mindshare to MySQL in the early web days is that at the time, PG was
hard to install, somewhat buggy, and poorly documented.  (Which was not
surprising considering that none of these mattered much in its original
academic environment.)  MySQL didn't do much, maybe, but what it could
do it did pretty well and without install/learning curve hassles.  We
had mostly caught up on those criteria by perhaps 7.1 or 7.2, but the
mindshare gap remains.
		regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command   (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) 

[snip]


> Stored procedures can be a 2-edged sword. They can lead to business logic
> being scattered between the persistence layer and the business layer.
> Thats not good for maintaining the application 3 years down the line.
> Triggers can also cause maintenance problems. Its so easy to forget/fail
> to document that inserting a record into table x causes column y of table
> z to be updated. Be careful how and where you use these features as they
> can come back to bite you!

A programmer that doesn't document stuff needs to find a new job :-)
This is more of an issue with management.  Anyone who does database apps
for on any kind of a large scale will tell you that views, triggers, etc..
are essential.  I am currently in teh process of writing a complete solution
for the college I develop for.  Finance, accounting, pos, registration,
student tracking etc...

>> MySQL cannot even handle
>> sub-queries yet. I also use Python for standalone interfaces to the data.
>> Why should I not be able to use the same views and triggers etc  in there
>> that I use for my web apps.  PHP is quite powerful if used correctly.

> You are, of course, free to do whatever want. But if you have to use
> features of the database to compensate for inadequacies in your
> programming language maybe you should be using another language?

You might not have understood me or I am not understanding you.  Changing
languages is not teh problem, if the database doesn't support views it still
won't if you change languages lol!  Changing databases in this case is the
answer.

<snip>

> I'm not aware of any "issues" with Java (unless you mean Swing ;)).
Swig is awful.

Much of the populatity of MySQL seems to stem from PHPs out-of-the-box
support for it. With the MySQL client library license change, this
situation will probably change. There was a long thread about this earlier
this year. Check the archives.

<huge snip> read previous messages if you are interested :)

> where have I blamed PHP for anything? Nowhere. As for extreme predudice,
> if you wish to grosely mis-interpret my opinions of the limitations of cgi
> scripts in that way, thats fine by me.

OK look all I am saying is Postgres is a good and yes I will say it even
a better solution than MySQL for PHP.  I am not trying to attack you but I
wonder about your experience with PHP development.  It is not a cgi
language. In dealing with web applications and frontends to database or
even just a dynamic web site PHP has every bit the power and ability that
Java does and the development time is way down. If you need more power
IMO Python is the way to go.  With PHP, Python and POstgres there isn't
anything from a web application or even standalone with Python that can't
be done.  Using MySQL though will limit you because of the lack of features,
which is what I was trying to communicate before.

In article <7515.1070124713@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> writes:

> Quoted as gospel by various people:
>>> MySQL cannot even handle sub-queries yet.

> BTW, is that really still true?  I thought they had at least some
> support for subqueries by now.

Subqueries are supported in MySQL 4.1 which came out some months ago,
but this version is (rightfully) not yet declared stable.

On 29/11/2003 13:41 Chris Travers wrote:
> [snip]
> It is all how you organize your app.  Stored proceedures are extremely
> useful when they represent a unified API for accessing parts of the
> database.  Word of advice:  Keep the database self-contained.  If all you
> want is object persistance, then why non use Berkeley Database?  It is
> even
> transactional.  The point of having an RDBMS is to provide more
> flexibility
> than a simple persistance store.  When used sensibly, stored proceedures
> are
> extremely simplifying, not the other way arround.

Actually I've worked on projects where views and stored procedures have
been used to present a logical view of the underlying data and help to
isolate the application from database changes. I think this is an entirely
reasonable thing to do. Berkely DB me? Never! PostgreSQL rules! :)

> [snip] I don't think Jason was compensating for weaknesses in the
> language-- I
> think that he was asking why he woudln't want to build into the database
> the
> universal functions accessed by multiple applications.  And he would be
> right in trying to do so.
>
> Let me give you an example:  One of the large projects I maintain is
> HERMES
> (http://hermes.sourceforge.net).  Hermes relies on its own user and
> permissions catalogs in order to provide a consistant administrative
> interface across database managers and simplify the task of assigning
> permissions to users and groups.  The differences in syntax can them be
> handled in wrapper layers, etc.
>
> However, it makes sense to try to wrap these catalogs using stored
> proceedures so that third-party apps don't necessarily need to be aware
> of
> the structure of the catalogs when assigning permissions.  This way, too,
> the db users' catalog and the user catalog in the RDBMS can be guaranteed
> to
> be in sync.  It will also allow me eventually to directly enforce
> permissions using triggers rather than rely on the RDBMS model (useful in
> shared hosting environments).
>
> >
> > > Java has its own issues and I am not sure it is as far supiour as you
> > > are claming it is.  But that is not for this dscussion.
> >
> > I'm not aware of any "issues" with Java (unless you mean Swing ;)).
>
> Every language has "issues."  This is not the time or place for a
> development environemnt holy war ;-)  But--- PHP and Python all the way
> ;-)

Python? Hm. Is this the 5 minute argument or the full half hour? :-)

>
> >
> > Much of the populatity of MySQL seems to stem from PHPs out-of-the-box
> > support for it. With the MySQL client library license change, this
> > situation will probably change. There was a long thread about this
> earlier
> > this year. Check the archives.
> >
> Putting the cart before the horse.  MySQL is far easier to administer in
> a
> shared hosting environment.  Maybe one of these days, I will put together
> a
> package for managing PostgreSQL accounts in this way.  If there is
> interest,
> please email me off-list and we can get started.  I don't expect MySQL's
> dominance to change until we can offer an easy-to-administer alternative
> for
> these environments.

Why not contribute to one of the existing PG admin utilities such as
pgAdmin III or phpPgAdmin?

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Jason Tesser wrote:

> [snip]
> A programmer that doesn't document stuff needs to find a new job :-)
> This is more of an issue with management.  Anyone who does database apps
> for on any kind of a large scale will tell you that views, triggers, etc..
> are essential.  I am currently in teh process of writing a complete solution
> for the college I develop for.  Finance, accounting, pos, registration,
> student tracking etc...
>

   I'm going to hop on this thread and ask a question rather than rant
(although ranting is fine by me ... rant away).

   Could someone explain to me the usefulness of views?  I understand
how they are created.  I understand a single query can be created as a
view returning all records in a single column of a single table, or
maybe even multiple columns across many tables using a complex join.

   That sounds find if all you want to do is to populate your drop-down
list box with selection choices or use the same search criteria each
time.  But if I want to access certain information for a particular
customer that requires joins and the like, then a view would be great.
But as far as I know, I am unable to place search parameters into a
view.  Is this false or am I totally missing the point of views?

   Shane D

Shane D <shane.dawalt@wright.edu> writes:

>    Could someone explain to me the usefulness of views?  I understand
> how they are created.  I understand a single query can be created as a
> view returning all records in a single column of a single table, or
> maybe even multiple columns across many tables using a complex join.
>
>    That sounds find if all you want to do is to populate your
> drop-down list box with selection choices or use the same search
> criteria each time.  But if I want to access certain information for a
> particular customer that requires joins and the like, then a view
> would be great. But as far as I know, I am unable to place search
> parameters into a view.  Is this false or am I totally missing the
> point of views?

It's false.  You can treat a view just like a table and add clauses to
your query that restrict it beyond what the view gives you.  I think
that's what you're asking about...

Views are useful for things like:

1) Insulating apps from details of the schema which may change
2) Giving different users different, well, views of the data, perhaps
   on a column basis.  Create a view that only shows a subset of
   columns, and only allow unprivileged users access to the view, not
   the underlying table(s).

-Doug

Doug McNaught wrote:

> It's false.  You can treat a view just like a table and add clauses to
> your query that restrict it beyond what the view gives you.  I think
> that's what you're asking about...

   Thanks for your reply.

   I found an example in the postgresql reference manual in the "CREATE
VIEW" section that shows exactly what you said (reproduced below).

CREATE VIEW kinds AS
SELECT *
FROM films
WHERE kind = ’Comedy’;

   The manual uses the view thusly:

SELECT * FROM kinds;

   But what if the films table also had a field for the production
company.  This implies based on the view definition that it too, has the
field (call it prod_co).  Could I use the following query to select all
Comedy films distributed by the 'Small Company' production company?

SELECT * FROM kinds WHERE prod_co = 'Small Company';

   Yes this is contribed, but humor me please.

   Shane

Shane D <shane.dawalt@wright.edu> writes:

>    But what if the films table also had a field for the production
> company.  This implies based on the view definition that it too, has
> the field (call it prod_co).  Could I use the following query to
> select all Comedy films distributed by the 'Small Company' production
> company?
>
> SELECT * FROM kinds WHERE prod_co = 'Small Company';

Sure, as long as the column is part of the view, you can use it to
constrain the SELECT.

-Doug

"Shane D" <shane.dawalt@wright.edu> Wrote:
<snip>
>
>    Could someone explain to me the usefulness of views?  I understand
> how they are created.  I understand a single query can be created as a
> view returning all records in a single column of a single table, or
> maybe even multiple columns across many tables using a complex join.

Before I go into the usefulness of views, it is important to understand that
views behave sort of like "logical tables" which can be used in SELECT
queries (or in PostgreSQL, if you add the proper RULEs, you can also use
them in INSERT, UPDATE, or DELETE queries as well).

A view is defined by a select query (often but not always a join).  This can
be useful for:
1)  Aggregating tables with different permission levels into a single
logical table, hence giving the effect of per-column permissions.
2)  Subdividing the table into several logical tables with different
permissions based on which view the row appears in.
3)  Providing application-specific presentations of the data, thus
insulating them from the actual structure, or allowing a denormalized view
of a highly normalized database.
4)  Data mining and reporting:  Views can aggregate tables in ways that make
it easier to make sense of data.  Views can be aggregated into other views,
allowing very abstract approaches to reporting.

>
>    That sounds find if all you want to do is to populate your drop-down
> list box with selection choices or use the same search criteria each
> time.  But if I want to access certain information for a particular
> customer that requires joins and the like, then a view would be great.
> But as far as I know, I am unable to place search parameters into a
> view.  Is this false or am I totally missing the point of views?
>
Think of it this way:  PostgreSQL can do the following thigns with your
data:
1) Store it
2)  Retrieve it.
3)  Ensure that the data is meaningful (via Referential Integrity
enforcement, etc.)
4)  Present it in various ways (i.e. complex select statements, views, etc.)

Views represent a tool for changing the presentation of the data in the
database.  Neither more nor less.

For that join you are mentioning, one would have to know how you were
looking at the information, etc. to know whether a simple join would be the
best way to go or whether a view would be better.

Best Wishes,
Chris Travers

On 29/11/2003 16:24 Jason Tesser wrote:
> [snip]
> A programmer that doesn't document stuff needs to find a new job :-)

Agreed. So you're replaced him and inherited a documentation-free
application. How many favours has he done you by squirrelling away section
of business logic in the database?

> This is more of an issue with management.  Anyone who does database apps
> for on any kind of a large scale will tell you that views, triggers,
> etc..
> are essential.  I am currently in teh process of writing a complete
> solution
> for the college I develop for.  Finance, accounting, pos, registration,
> student tracking etc...

I've worked on stuff for some of the largest companies in the world if
that counts. Mind you, I've been in the business 24 years (18 of those as
an independent consultant) so maybe I'm just a newbie :)

For your accounting, take a look at SQL-Ledger (www.sql-ledger.org). It
might save you months of effort.

> You might not have understood me or I am not understanding you.

It feels like we're 2 people divided by a common language...



--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

El Sáb 29 Nov 2003 13:51, Tom Lane escribió:
> Quoted as gospel by various people:
> >> MySQL cannot even handle sub-queries yet.
>
> BTW, is that really still true?  I thought they had at least some
> support for subqueries by now.

Sorry if it's a stupid question, but what's the difference between subqueries
and subselects?

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telemática
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Martin Marques writes:

> Sorry if it's a stupid question, but what's the difference between subqueries
> and subselects?

Nothing.

--
Peter Eisentraut   peter_e@gmx.net

El Dom 30 Nov 2003 10:21, Peter Eisentraut escribió:
> Martin Marques writes:
>
> > Sorry if it's a stupid question, but what's the difference between
subqueries
> > and subselects?
>
> Nothing.

Wow, I thought that they were different things.
I could swear that I say a press release saying they had it in 4.0. That's why
I thought that they were different things.

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telemática
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Chris Travers wrote:
> "Shane D" <shane.dawalt@wright.edu> Wrote:
>>   Could someone explain to me the usefulness of views?  I understand
>>how they are created.  I understand a single query can be created as a
>>view returning all records in a single column of a single table, or
>>maybe even multiple columns across many tables using a complex join.

> 3)  Providing application-specific presentations of the data, thus
> insulating them from the actual structure, or allowing a denormalized view
> of a highly normalized database.

In several cases, we've taken long functions from various perl and PHP
code bases, combined the "select" queries from them into views, and
converted the rest of the logic into stored procedures (in plpgsql, no
less).

>>   That sounds find if all you want to do is to populate your drop-down
>>list box with selection choices or use the same search criteria each
>>time.  But if I want to access certain information for a particular
>>customer that requires joins and the like, then a view would be great.
>>But as far as I know, I am unable to place search parameters into a
>>view.  Is this false or am I totally missing the point of views?

> For that join you are mentioning, one would have to know how you were
> looking at the information, etc. to know whether a simple join would be the
> best way to go or whether a view would be better.

But as a sampler, you can use the view to create a virtual table (that's
a tautology, isn't it) which contains the the data set that the function
uses as for output (IIRC, this is called the "domain"). The specifics of
your function can be coded into a stored procedure, which can accept
(for example) a customer ID, and return all the values from the view
that relate to that customer.

In that case, you'd probably start the definition of your plpgsql stored
procedure as:

create or replace function get_transactions (INTEGER) returns set of
record as '
DECLARE
    cust_id ALIAS FOR $1;
BEGIN
for r in select ... from ... loop
     return next r;
end loop;
return;
END
' language 'plpgsql';

But I would certainly love to have parameterised views :)

Alex

On Mon, Dec 01, 2003 at 09:38:06AM +1100, Alex Satrapa wrote:

> create or replace function get_transactions (INTEGER) returns set of
> record as '
> DECLARE
>    cust_id ALIAS FOR $1;
> BEGIN
> for r in select ... from ... loop
>     return next r;
> end loop;
> return;
> END
> ' language 'plpgsql';
>
> But I would certainly love to have parameterised views :)

Me too.  I've created many functions to extract data that are joined to
other functions.  All in all the result is not as optimal as it could
be, because the optimizer can not poke into the functions, and the
estimates about functions are only guesses.  If one could use
parametrized views instead of functions the whole mess would probably be
more optimal.

Maybe there's a TODO here?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)

Alvaro Herrera wrote:
> Me too.  I've created many functions to extract data that are joined to
> other functions.  All in all the result is not as optimal as it could
> be, because the optimizer can not poke into the functions, and the
> estimates about functions are only guesses.  If one could use
> parametrized views instead of functions the whole mess would probably be
> more optimal.
>

How is a "parameterized view" any different than a set returning SQL
function? In either case, you've got the same work to do to teach the
optimizer how to understand it, no? Seems like the todo is just that,
teach the optimizer how to do better with set-returning SQL functions.

Joe

After a long battle with technology, shane.dawalt@wright.edu (Shane D), an earthling, wrote:
>    That sounds find if all you want to do is to populate your
> drop-down list box with selection choices or use the same search
> criteria each time.  But if I want to access certain information for a
> particular customer that requires joins and the like, then a view
> would be great. But as far as I know, I am unable to place search
> parameters into a view.  Is this false or am I totally missing the
> point of views?

A VIEW is essentially "macroexpanded" into being the query requested,
in more-or-less the manner LISP handles macro expansion.

Suppose I define a view...

  create view january_transactions as
     select * from transaction_table where trans_on between
         '2003-01-01' and '2003-02-01';

I can then narrow things down when I use the view...

  select * from january_transactions  -- So I'm looking only at Jan
    where txn_type in (1, 2, 4);

If there's a "parameter" that you're expecting to use, then that means
that's a field you want to make sure you are selecting so that, when
you use the view, you can throw in a WHERE clause to specify the
"parameter."  That's what the "where txn_type in (1,2,4)" part
expresses.

One of the guys I work with is building "data warehouse" application
code; I keep commending that he use VIEWs as much as possible, and
building summary tables only when performance dictates it.  And the
way to define the views most usefully is to make them fairly generic.

In most cases, that means that the VIEW should JOIN tables together to
extract useful information.  And anything that could be a parameter
should be selected.  That way, filtering can be done on the view, and
so the view can be used for multiple reports.
--
output = reverse("moc.enworbbc" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/linuxxian.html
"Heuristics (from the  French heure, "hour") limit the  amount of time
spent executing something.  [When using heuristics] it shouldn't take
longer than an hour to do something."

I used it first because

1) someone suggested it and I didn't know any better
2) install, setup, maintanance and using it is easier than breathing.  You'd
be surprised how much of a difference it makes to a newbie to not have to do
things like vacuum regularly and the ability to change a column type (I'm
not saying this is a good idea, just that it seemed nice at the time), stuff
like that.
3) their online documentation was great, learning how to do new stuff was
fast and easy
4) It SEEMED to work fine (I say seemed because I never had anything happen
to me like an int overflow problem)
5) For the type of work I started off with I didn't badly need the features
that mysql lacks

I'm betting that this is the case with many mysql users.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: <pgsql-general@postgreSQL.org>
Sent: Saturday, November 29, 2003 10:01 AM
Subject: Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL
Advocacy, Thoughts and Comments


> "Rod K" <rod@23net.net> writes:
> > Paul Thomas wrote:
> >> Much of the populatity of MySQL seems to stem from PHPs out-of-the-box
> >> support for it.
>
> > This is incorrect.  The embedded mysql client library was not added
until
> > PHP4.0 RC1.  PHP's popularity existed long before this.  The real
culprit
> > causing the popularity of MySQL was it's ubiquity among hosting
providers
> > and the virtual non-existence of PG in that arena.  If PG had been more
> > friendly to shared hosting environments, perhaps this situation wouldn't
> > have arisen.
>
> You are both engaging in the most blatant form of historical
> revisionism.  Of course PHP's support for MySQL didn't drive MySQL
> adoption --- it was the other way around, PHP adapted to MySQL because
> that was what was out there.  I think "friendly to shared hosting
> environments" is a made-up reason as well.  The real reason PG lost
> mindshare to MySQL in the early web days is that at the time, PG was
> hard to install, somewhat buggy, and poorly documented.  (Which was not
> surprising considering that none of these mattered much in its original
> academic environment.)  MySQL didn't do much, maybe, but what it could
> do it did pretty well and without install/learning curve hassles.  We
> had mostly caught up on those criteria by perhaps 7.1 or 7.2, but the
> mindshare gap remains.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Note: I am a php developer and I love it, but...

>In dealing with web applications and frontends to database or
>even just a dynamic web site PHP has every bit the power and ability that
>Java does and the development time is way down.

Uh, how about threads.  I know that you don't need them much but it sure
would
be nice to be able to do background processing.

>If you need more power
>IMO Python is the way to go.

I am not that familiar with pything, not to get off topic here but what you
can do in
python that you can't do in PHP?

> python that you can't do in PHP?

Python is an immensely powerful language.  It is essentially the successor
to both LISP and Smalltalk.  It has things like closures (well, not
complete, but pretty close), generators, a huge OO library, easy-to-use
exceptions (i.e. - in comparison to Java), and I believe with stackless
Python you can also do continuations.  Also, there's some really cool
add-on modules.  Psyco, for instance, is a run-time optimizing compiler,
that compiles several versions of your code based on the data you get.

Jon


>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

>>In dealing with web applications and frontends to database or
>>even just a dynamic web site PHP has every bit the power and ability that
>>Java does and the development time is way down.
>
> Uh, how about threads.  I know that you don't need them much but it sure
> would be nice to be able to do background processing.
[sNip]

        PHP doesn't support threads?  I've always thought of Forking as
overkill where threads are light and elegant.

--
Randolf Richardson - rr@8x.ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.

On Tue, 2 Dec 2003, Randolf Richardson wrote:

> >>In dealing with web applications and frontends to database or
> >>even just a dynamic web site PHP has every bit the power and ability that
> >>Java does and the development time is way down.
> >
> > Uh, how about threads.  I know that you don't need them much but it sure
> > would be nice to be able to do background processing.
> [sNip]
>
>         PHP doesn't support threads?  I've always thought of Forking as
> overkill where threads are light and elegant.

True on some platforms (Windows, Solaris) untrue on others (Linux,
possibly BSD) where threads are not much lighter than processes.

PHP doesn't have thread support.

Given a table like the following:

CREATE TABLE mytable (
    progr integer PRIMARY KEY,
    record_creation_date date DEFAULT current_date,
    ...
    other columns
    ...
);

is there a way to deny any modifications to 'record_creation_date'
without using a view?

Thanks,
Claudio

--
Claudio Succa
PERTEL - Torino - Italy
+39-011-437.4141
http://www.pertel.it
http://www.uniassist.it

Claudio Succa <claudio.succa.ml@pertel.it> writes:

> Given a table like the following:
>
> CREATE TABLE mytable (
>     progr integer PRIMARY KEY,
>     record_creation_date date DEFAULT current_date,
>     ...
>     other columns
>     ...
> );
>
> is there a way to deny any modifications to 'record_creation_date'
> without using a view?

Sure, you can use a trigger.

-Doug

Il ven, 2003-12-12 alle 20:11, Doug McNaught ha scritto:
> Claudio Succa <claudio.succa.ml@pertel.it> writes:
>
> > Given a table like the following:
> >
> > CREATE TABLE mytable (
> >     progr integer PRIMARY KEY,
> >     record_creation_date date DEFAULT current_date,
> >     ...
> >     other columns
> >     ...
> > );
> >
> > is there a way to deny any modifications to 'record_creation_date'
> > without using a view?
>
> Sure, you can use a trigger.
>
> -Doug

Thanks a lot Doug.

(Not to reinvent the wheel, do you know where I could find a suitable
function to use in the trigger?)

Claudio

--
Claudio Succa
PERTEL - Torino - Italy
+39-011-437.4141
http://www.pertel.it
http://www.uniassist.it

Claudio Succa <claudio.succa.ml@pertel.it> writes:

> (Not to reinvent the wheel, do you know where I could find a suitable
> function to use in the trigger?)

No, but it should be pretty trivial to write.  Just set up a BEFORE
UPDATE trigger that compares OLD.ts_field against NEW.ts_field and
does a RAISE ERROR if they're different.  The PL/pgSQL docs have a few
decent examples of how to write a trigger function.

-Doug

Or, depending if you just want to ignore updates to that field (not
always best, but possible, similar to a view).

CREATE OR REPLACE FUNCTION block_col()
RETURNS TRIGGER AS '
    BEGIN
        NEW.ts_field := OLD.ts_field;
        RETURN NEW;
    END;
' LANGUAGE PLPGSQL;

In place of the assignment, you could also test for inequality and raise
an error as Doug suggested:
        IF NEW.ts_field != OLD.ts_field THEN
            RAISE EXCEPTION ''Update to % Not Permitted'',
                ts_field
        END IF;
Best Wishes,
Chris Travers


On Sat, 2003-12-13 at 23:24, Doug McNaught wrote:
> Claudio Succa <claudio.succa.ml@pertel.it> writes:
>
> > (Not to reinvent the wheel, do you know where I could find a suitable
> > function to use in the trigger?)
>
> No, but it should be pretty trivial to write.  Just set up a BEFORE
> UPDATE trigger that compares OLD.ts_field against NEW.ts_field and
> does a RAISE ERROR if they're different.  The PL/pgSQL docs have a few
> decent examples of how to write a trigger function.
>
> -Doug
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

Il sab, 2003-12-13 alle 15:48, Chris Travers ha scritto:
> Or, depending if you just want to ignore updates to that field (not
> always best, but possible, similar to a view).
>
> CREATE OR REPLACE FUNCTION block_col()
> RETURNS TRIGGER AS '
>     BEGIN
>         NEW.ts_field := OLD.ts_field;
>         RETURN NEW;
>     END;
> ' LANGUAGE PLPGSQL;
>

Great. It works exactly as I need.

(notice: I had to replace RETURNS TRIGGER with RETURNS OPAQUE)

Thanks everybody,
Claudio


--
Claudio Succa
PERTEL - Torino - Italy
+39-011-437.4141
http://www.pertel.it
http://www.uniassist.it

Randolf Richardson wrote:
>>>In dealing with web applications and frontends to database or
>>>even just a dynamic web site PHP has every bit the power and ability that
>>>Java does and the development time is way down.
>>
>>Uh, how about threads.  I know that you don't need them much but it sure
>>would be nice to be able to do background processing.
>
> [sNip]
>
>         PHP doesn't support threads?

Nope...

   I've always thought of Forking as
> overkill where threads are light and elegant.
>

Depends on your environment... Forking in Linux is fairly lightweight.

Joshua D. Drake





--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

> Randolf Richardson wrote:
> >>>In dealing with web applications and frontends to database or
> >>>even just a dynamic web site PHP has every bit the power and ability
that
> >>>Java does and the development time is way down.
> >>
> >>Uh, how about threads.  I know that you don't need them much but it sure
> >>would be nice to be able to do background processing.
> >
> > [sNip]
> >
> >     PHP doesn't support threads?
>
> Nope...
>
>    I've always thought of Forking as
> > overkill where threads are light and elegant.
> >
>
> Depends on your environment... Forking in Linux is fairly lightweight.

But when you are using php with a web server you can't really use the
process contol functions safely.  Forking apache processes on your own is
not a good idea.  If forking were an option with php/apache it would do just
fine.  But currently it is not a viable solution.

http://us2.php.net/manual/en/ref.pcntl.php (read the first paragraph)