Thread: Re: [GENERAL] stored procedure revisited
>fact that it doesn't do something that most, if not all, commercially >available db systems do can work against us, i.e., portability and upgradability: imagine you want to change that M$ system into Pg, or, I hate to say this, but somehow if your success is so big that you can not live with Pg, you need go to O ect. then, true SP will make things really easy (just systax change, you may even just use our open source facility -- I'm sure there will be, since PL/pgSQL are so close to other PL). In my own case, when I begin to use PL/pgSQL, I put some thinking on the second aspect, I bet others also did that. A true SP will make it more inviting. C is good, and in a sense, for OSS we should encourage more C "scripting" and "hacking" than script scripting. (perl and PL/pgSQL actually is "bad" in this sense). Because IF everybody use C, the use and development will inherently related and the dev. speed will accelate exponentially. However, C/C++ is difficult (I use both C and perl, so I know it). Also, as GOOD excuse, C/C++ is not safe. So, we need PL SP. However, I would like to see data warehouse (or more moderately and accurately data mart) support also -- the point: the priority? ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com
On Wed, 13 Oct 1999, amy cheng wrote:
>
> >fact that it doesn't do something that most, if not all, commercially
> >available db systems do can work against us,
> i.e., portability and upgradability: imagine you want to change that
> M$ system into Pg, or, I hate to say this, but somehow if your
> success is so big that you can not live with Pg, you need go to O ect.
> then, true SP will make things really easy (just systax change, you may even
> just use our open source facility -- I'm sure there will be, since PL/pgSQL
> are so close to other PL). In my own case, when I begin to use PL/pgSQL, I
> put some thinking on the second aspect, I bet
> others also did that. A true SP will make it more inviting.
actually, one would hope that the system has its db independence in the
application layer rather than the database layer. for instance, using
something like NeXT's Enterprise Objects Framework to fetch rows from the
db and translate the rows into objects, you only deal with the objects.
The whole datastore, at this point, becomes irrelevant since you rarely
deal with the underlying SQL -- EOF takes care of all that for you.
Instead, you say "hey, i want all the objects that have their personName
ivar equal to Amy" ( "personName = 'Amy'" ). I'm fairly positive that
Sun's Java equivalent of EOF ( 'Entity Javabeans', iirc ) does the same
sort of thing.
keeping inserts/selects/etc in stored procedures would still require a
rewrite of all the stored procedures when moving to another db vendor,
which may or may not be a large problem depending on that vendor's
imeplementation of stored procedures and SQL in general. granted, you
wouldnt have to completely gut the application and rewrite the whole
bloody thing, but since your app is already going to have some of it
rewritten ( cant use an OCI call on postgresql ), i think it'd made more
sense to abstract things further by putting all the logic into your
objects, EOF or Entity Javabeans, rather than in the db.
so now lets talk code reuse. both options would give you about the same
level of code reuse, but in two completely different ways. stored
procedures ( and company/DBA policy ) pretty much force the user to take
advantage of them rather than doing raw inserts, selects, etc on the
underlying tables. EOF forces you to deal with the objects rather than
sql. either way, all of your business logic is in one location. by
using a higher-level language, however, you wouldnt have to deal with
tedious pl/sql-ish programming. one could also argue that having 20+
different stored procedures is really no better than memorizing the
business logic and duplicating that in the application, bypassing the
procedures altogether. if you have to deal with developing on one dbms
and deploying on another dbms, EOF starts to look even more beautiful --
since your logic is in the objects, not the db, nothing will have to be
ported to the new dbms. in fact, all you really need to do is change the
EOModel; all of your code can remain in binary form.
'problems' with EOF-ish approaches include having to distribute your
framework ( think library ) along with your app, which youd have to do
anyway seeing that your objects are in that framework/package. stored
procedures wouldnt have to be shared outside of the dbms ( obviously ).
personally, i find it a LOT easier to deal with EOF objects rather than a
potentially large PL/SQL ( or PL/pgSQL ) procedure.
what'd be interesting is to compare the use of stored procedures to EOF or
EOF-ish alternatives, using the same data & schema, ofcourse. NeXT/Apple
has a sample db, sample data, and examples of how one can use EOF's
features to augment/replace stored procedures in the dbms.
(java)
public void validateForDelete() throws EOValidation.Exception {
if( !isPaid() )
{
throw new EOValidation.Exception("You can't remove an unpaid fee");
}
super.validateForDelete();
}
(objective-c)
- (NSException *)validateForDelete
{
if( ![self isPaid] )
return [NSException validationExceptionWithFormat:@"You can't remove an unpaid fee"];
return [super validateForDelete];
}
and yes, i do realize that not everyone has the option of using
EOF/Javabeans... nobody's perfect :)
> [SNIP]
> However, I would like to see data warehouse (or more moderately and
> accurately data mart) support also -- the point: the priority?
so either (A) work on implementing tablespaces or (B) donate some money to
postgresql, inc.
---
Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org
"Just think how much deeper the ocean would be if sponges didn't live there."
would it be possible to add a Reply-to header to all outgoing messages ?
Howie wrote: > > actually, one would hope that the system has its db independence in the > application layer rather than the database layer. for instance, using > something like NeXT's Enterprise Objects Framework to fetch rows from the > db and translate the rows into objects, you only deal with the objects. > The whole datastore, at this point, becomes irrelevant since you rarely > deal with the underlying SQL -- EOF takes care of all that for you. > Instead, you say "hey, i want all the objects that have their personName > ivar equal to Amy" ( "personName = 'Amy'" ). I'm fairly positive that > Sun's Java equivalent of EOF ( 'Entity Javabeans', iirc ) does the same > sort of thing. > Well, you are approaching from an application developer's point of view. You want to have database independence so you can move back and forth between all the flavors of databases out there. ODBC was meant to address this issue, and we all know there are still limitations. However, even then database independence doesn't make the discussion of stored procedure irrelevant. Given a better tool to do something, wouldn't you use it? If you have to deal with databases ranging from Access to Oracle, are you going to make your application based on Access capability, since it doesn't have a PL? More than likely you are going to design one version for Access & another for Oracle... From business's POV, application layer is _not_that_ important because production databases (especially OLTP databases) are seldomly moved. Even today the commercial vendors finding themselves supporting the legacy versions. In this sense the capability of the database itself becomes that much more important. For the database administrators, the ability underneath the application layer is very important indeed. Certainly SP offers a lot of horsepower :) Regards, yin-so chen
amy cheng wrote: > > C is good, and in a sense, for OSS we should encourage more C "scripting" > and "hacking" than script scripting. (perl and PL/pgSQL actually is "bad" in > this sense). Because IF everybody use C, the use and development will > inherently related and the dev. speed will > accelate exponentially. However, C/C++ is difficult (I use > both C and perl, so I know it). Also, as GOOD excuse, C/C++ > is not safe. So, we need PL SP. Well, not everyone in this world can work in the C level (I certainly included myself here), and talking about languages is getting awefully close to advocacy :) But just think this way though, if C is the route to go, then why not assembly? When you have an answer of why not, you also have an answer for C as well :) But OTOH, that's why C programmers have nothing to fear about all the VB programmers out there... Because there are jobs only C is appropriate. I am sure you all know this so ignore my mumbling :) > > However, I would like to see data warehouse (or more moderately and > accurately data mart) support also -- the point: the priority? > So, what is the priority? I will argue that SP is a higher priority than data warehousing. The reason? More people would benefit from SP than from data warehousing. Moreover, SP will also draw database administrator's mind-share for PG. Who's going to work with the database? Administrators & application developers, mostly. And if there are features which most administrators or developers would consider lacking, it would be a reason for them to look elsewhere. W/out them pitching for PG, would PG compete well against commercial databases? SPI is great and all, but there is a reason why a PL is also developed. Since the PL is here, then SP is the next logical step :) Regards, yin-so chen
No...we tried in the past, and more hated it then wanted it...
On Wed, 13 Oct 1999, Jim Cromie wrote:
>
> would it be possible to add a Reply-to header to all outgoing messages ?
>
>
> ************
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org