Re: chooses not to generate code at all

Responding to JXStern...

[At least in the conventional sense where the procedure is triggered by specific database updates.

That would be a "trigger", not a "stored procedure" as such.

The whole point of having a stored procedure as an integral part of the DBMS is so that the DBMS engine can execute it when triggered by some update activity on the stored data. If there is no trigger, then... 


My usage of the terms is standard, yours is not.  Boot up your
favorite database and see what the tools call things.


From "Designing Relational Database Systems" by Rebecca Riodan, pg. 70:

"SQL Server implements procedural integrity support by way of trigger procedures that are executed ("triggered") when a record is either inserted, updated, or deleted."

This happened to be the most recent and first DB book I looked at but they all say basically the same thing. She never mentions "stored procedures" per se, so what she is describing is exactly what I am describing. Procedures are stored in the RDB that are executed in response to some DBMS activity that modifies the data. The trigger for execution is insert/update/delete of data by the DBMS and the invocation is controlled by the DBMS. So I have a hard time understanding why my terms are not "standard".

As I responded to Parker, procedures stored in the database AND triggered by DBMS processing are conventionally known as "stored procedures". If the procedure execution is not triggered by DBMS activity, then it is just another pile of data stored in the DB for retrieval by whoever does control its execution. I have never seen any reference that says anything different than that. Give me one and I will acknowledge a terminology disconnect.

In theory one could store code, parametric data, and whatnot in the DBMS that is selectively invoked by external applications. In that case those applications define the scope and context of execution rather than the DBMS' data. So in that case the DBMS is just a storage medium for the procedures and doesn't decide when they should be executed.]

That is exactly what a "stored procedure" is all about.

This is just an arbitrary blob of data and the execution dynamics are external to the DBMS. IOW, it is not an integral part of the DBMS itself.


Well, fine, that's how ANSI felt about it up until recently, too.
Unfortunately, best practice for twenty years has been to use the
non-standard stored procedures to encapsulate functionality and
decouple applications from the data model. 

If you're not into it, btw, you would be AMAZED at the issues of
efficient execution that underlies what seems such a simple thing as a
SQL statement.  Makes your average C compile-link-go seem like a
kiddie script.

I'm afraid I don't see the point. SQL abstracts RDB access so implementation complexity hidden under the hood is not a surprise. But that implementation infrastructure is solving a different problem (implementing an access mechanism for the RDB) not the business problem being solved.

Also, we seem to be wandering a bit, so let me try to clarify my position. I have no problem with stored procedures that address pure DB issues, such as data integrity. For example, if one is going to denormalize the database and store Mass, Density, and Volume, one is going to need to update Mass whenever Density or Volume changes. The obvious place to do that is in the DBMS where the triggering change occurs because the denormalization was a DBA decision, not a problem space decision. There are also data integrity rules that are certain to be invariant independently of particular applications (e.g., start date is earlier than finish date) and it is quite reasonable to enforce that via a stored procedure in the DBMS.

OTOH, regardless of the implementation mechanism (e.g., non-standard stored procedures) or what one calls them ("triggers" vs. "stored procedure"), I submit that one does not want <relatively volatile> dynamic business rules and policies to be executed by the DBMS as if they were data integrity issues.

An RDB is not "an MDA". It is simply a model instance that can be either an Input Model or an Output model under MDA, depending upon context. It happens to instantiate a meta-model, which is reflected in schemas, etc.. That meta-model instantiates a general data model especially tailored to persistence (Table, Tuple, Field). 

I'm not sure that these abstractions are "especially tailored to
persistence".  Certainly "field" is a general concept.

The point here is that SQL is not the only way to access an RDB; it is just one access paradigm. Nor is a conventional RDB the only way to implement Codd's RDM (e.g., OODB's and UML Class Models also honor that model.) 


Oh, I hardly think so.  The point of RDM is normal form, which is
barely even known to most practitioners of OO.


See my response to Parker.

As far as OO practitioners are concerned, it disturbs me that so many are not even aware that the cookbook refactoring rules they apply to a CLass Model are really just normalizing it to 3NF.

[Note, BTW, that a UML Class Model is normalized to 3NF just like an RDB schema. 


Cite me an authority on that, please.  The difference in style and
practice between relational and object is long-standing, if things
have changed I didn't get the memo.


How many do you want?  B-)  For starters, let's go with:

"Object-Oriented Analysis" by David Brown, pg. 333.

"Executable UML" by Mellor and Balcer, pg. 77.

"Object-Oriented Software Engineering" by Ivar Jacobson, pg. 278

Those are just the books where I could quickly find an explicit entry in the index. Every OOA/D I know of provides an explicit set of guidelines for organizing a Class Model. Those guidelines are just a paraphrasing of the RDM is less daunting language. (One could argue that John Lakos' "Large Scale C++ Software Design" is largely an exercise in normalization, though he calls it "leveling".)

[I suspect one of the reasons most OOA/D authors don't explicitly mention 3NF is because it is awkward to rationalize it in terms of behavior allocation and identity. That is, we can often legitimately assign a behavior to any of several classes that abstract inanimate problem space entities while NF implies a "hard-wired" 1:1 problem space mapping. One has to get side tracked into the role of abstraction, anthropomorphization, and mapping without explicit identity. So it is easier to just provide cookbook rules like one-behavior-one-class in sermon-on-the-mount mode.]

Bottom line: a UML Class Diagram is just an Entity Relationship Diagram with some additional bells and whistles to map to dynamic views and a unique implementation semantics for subclassing. All the same normalization techniques Just Work.

But the abstractions may be quite different even though both the application and the RDM are abstracting from the same customer problem space.


Impossible.

It happens all the time when one is outside the realm of CRUD/USER processing. How many databases decompose a telephone number into its true simple domains (country code, area code, exchange, number, extension)? But any application that needs those elements individually will model them as separate attributes (if the OOA/D was done properly).

We also abstract only the view that we need for the problem in hand. For example, suppose the RDB has:

Company
+ companyID
+ address
....

Division
+ divisionID
+ location
+ companyID
+ contactID
....

Contact
+ contactID
+ address
+ phone number
....

Now suppose I have an application that supports salespeople where each salesperson is allocated to a particular division of a company. When that salesperson logs in there is only one division of each company in their territory that is relevant to them. From their perspective there are no other divisions. So in the application we might model

Company
+ companyID
+ location
+ contactID
....

Contact
+ contactID
+ address
+ phone number
....

The Division table has disappeared entirely because it is not relevant to the problem in hand. (The application DB Access subsystem will have to understand how to map data requests to the RDB tables, but that is orthogonal to and hidden from the problem solution.)

More important, the OO paradigm manages complexity by having a flexible view of logical indivisibility. I have used scalar attribute ADTs in a high level control subsystem that expanded into several of classes with dozens of individual knowledge attributes in a service subsystem at a lower level of abstraction.

Bottom line: any time one is dealing with a complex problem solution the mapping to the RDB will rarely be 1:1 (though it is also unusual for it to be very different) unless the RDB is dedicated to that application.



(They won't be so different that one cannot map unambiguously between them because they /are/ derived from the same problem space.)


Nonsense.

How so? A Company is a Company is a Company. The views of it may be different between the dynamic (application) perspective and the data (RDB) perspective but the underlying entity semantics is the same. Both views are inherently abstractions of the real thing.

In addition, the application developer should not care if the data is persisted in an RDB, OODB, flat files, or clay tablets; that sort of thing should be completely transparent to the problem solution.] 


Easy to say, but the "impedance mismatch" between technologies is an
every-day problem.

Not if the applications are properly partitioned. At most one has to swap a single subsystem to substitute persistence mechanisms. The interface of the subsystem that the problem solution sees will be exactly the same so the substitution will be transparent.

If you have been following my posts on application partitioning, this is a classic example of why one wants to encapsulate things like UI and persistence. One should be able to swap the technologies without having to rewrite the entire application.


*************
There is nothing wrong with me that could
not be cured by a capful of Drano.

H. S. Lahman
hsl@xxxxxxxxxxxxxxxxx
Pathfinder Solutions  -- Put MDA to Work
http://www.pathfindermda.com
blog: http://pathfinderpeople.blogs.com/hslahman
(888)OOA-PATH



.

Relevant Pages

  • Re: chooses not to generate code at all
    ... >>>DBMS is so that the DBMS engine can execute it when triggered by some ... If there is no trigger, ... Stored procedure is one thing. ... > If the procedure execution is not triggered by DBMS ...
    (comp.object)
  • Re: chooses not to generate code at all
    ... DBMS is so that the DBMS engine can execute it when triggered by some update activity on the stored data. ... If one stores a procedure but does not provide a trigger then that procedure is not an integral part of the DBMS; it's just stored there, which is exactly what DBs do. ... The trigger for execution is insert/update/delete of data by the DBMS and the invocation is controlled by the DBMS. ... high level control subsystem that expanded into several of classes with ...
    (comp.object)
  • Re: Use all available processors ?
    ... Parallelism of a single query execution in the DBMS. ... Processor Affinity for IO in the OS Kernel. ...
    (microsoft.public.sqlserver.clustering)
  • Re: chooses not to generate code at all
    ... DBMS is so that the DBMS engine can execute it when triggered by some update activity on the stored data. ... So I stand by my original assertion that if there is no trigger for the stored procedure, it isn't an integral part of the DBMS execution. ... Consider a memory-mapped OODB like ObjectStore. ...
    (comp.object)
  • Re: Data driven people arguments
    ... > whose semantics is not relevant to the DBMS. ... > Contrast that with stored procedures whose execution is triggered by the ... if I keep my storing and retrieving logic seperate from my other ...
    (comp.object)