Re: [GENERAL] stored procedure revisited

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."