Re: chooses not to generate code at all

Responding to JXStern...

On Wed, 24 Aug 2005 18:14:41 GMT, "H. S. Lahman"
<h.lahman@xxxxxxxxxxx> wrote:

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


Stored procedure is one thing.

Trigger is another thing.

Referential integrity is a third thing.

As I indicated previously, I agree. But how does that change the discussion? 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 (storing stuff without caring about its semantic content). If one defines a trigger for it, then it is an integral part of the DBMS processing.


Each is a lump of code, both trigger and RI are data-driven, SP is
procedural.


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


She's not talking about stored procedures, so don't take some other
discussion and make believe it is about something it isn't.  Is that
so hard?

She doesn't mention stored procedures per se _in the entire book_. Since that book is about DBMS design using SQL Server, it seems reasonable to assume she doesn't think procedures stored in the DB without triggers are relevant to DBMS design. Which is exactly what I have been saying all along.

Can you find any DBMS design author who says that procedures stored without triggers are different than stored procedures with triggers AND that procedures stored without triggers are relevant to DBMS functionality?

As I responded to Parker, procedures stored in the database AND triggered by DBMS processing are conventionally known as "stored procedures".


Wrong.

Procedures stored without triggers just aren't relevant to DBMS design so when one uses the term "stored procedure" in a DBMS processing context like this one is /always/ talking about procedures with triggers.

Show me a context where the notion of "stored procedure" w/o a trigger is relevant to what the DBMS does to manage data.

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. 


Let me find the SQLServer doc online.

create procedure
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_4hk5.asp?frame=true

create trigger
http://msdn.microsoft.com/library/en-us/tsqlref/ts_create2_7eeq.asp?frame=true

create table (see foreign key for referential integrity)
http://msdn.microsoft.com/library/en-us/tsqlref/ts_create2_8g9x.asp?frame=true

People do this stuff all day, it's not an abstract discussion.

Browse to your content.

This is confusing the creation interface with the semantics. All these describe are the mechanisms for defining procedures, triggers, and tables via a programmatic API. We are agreed that triggers and procedures are different things semantically. The second reference is just an interface convenience that allows one to associate a trigger with the procedure at the same time one creates the procedure rather than as a separate activity _in the API_.

The issue in this thread is using stored procedures to enforce business rules and policies _within the DBMS data management (i.e., the DBMS executes the procedure independently of particular application contexts). Show me the doc that says a procedure w/o a trigger can be invoked by the DBMS engine during /its/ canonical processing.

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. 


It's a complex and apparently very little appreciated subject, outside
of the guys building RDBMS engines and power DBAs who want or need to
know what's inside, but may not appreciate the architectural
implications.  If the compilers and optimizers for SQL that appear in
the major products indicate what it takes to make an MDA efficient,
then either an MDA will HAVE to be built over an existing major RDBMS,
or there will be a much, much larger cost of entry to delivering major
system performance, or they will remain toys.

You keep referring to "an MDA" as if it were concrete. MDA is just a conceptual framework for transforming one model representation into another. If one has a pile of data represented as object attributes in an application and needs to transform that model into an RDB representation, MDA provides the conceptual framework for doing that.

In its purest form MDA would enable producing an equivalent RDB Data Model from a UML application Class Model. One only gets into issues like code generation if one transforms the /entire/ application model, which includes the solution dynamics. In that case one is transforming the dynamic model rather than just the static model.

But the actual mechanisms that implement such a transformation to read/write RDB tuples will be the existing RDB infrastructure technologies like SQL Server. IOW, the implementation mechanisms don't change. All MDA brings to the table is a way or mapping between the representations through meta-models so that the transformation engine can select the correct implementation read/write element for the RDB access to use for a given read/write element in the application model during the transformation.

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. 


Your position is clear, but paradoxically impossible to apply.

The normalized, cannonical data model itself captures huge amounts of
business logic, so it is meaningless to pretend one can keep the logic
outside.

Note that you are using the term 'data model' here in a broader sense than a traditional ERD data model. That's because a traditional Data Model is a pure static representation of data relations. To enforce a lot of business logic you would have to include the notion of <triggered> stored procedures to provide dynamic behaviors.

Given the broader view, the key notion in 'canonical' is generality. My example of {mass, density, volume} will be canonical in a very large number of contexts because it is a <Newtonian> physical law. So long as the DB did not service application contexts in quantum physics, that would be a proper canonical relation to capture in the DB because it will remain true throughout the life of the DB and the applications it serves.

Now consider an IRS ruling that says that a particular type of asset can be depreciated over ten years. You could capture that rule in the DB with a triggered stored procedure that checked for consistency between an assetType field and depreciationTerm field whenever the relevant record was inserted or updated. But that is not a canonical relation relative to the business using the DB because such rules are subject to change over the life of the DB. (Albeit rarely for this example, but still within the life of a lot of legacy code.)

My point in this thread is that one must consider 'canonical' within the context of the life of the DB and its client applications. Just because a relation is canonical at one moment in time is not sufficient justification for enforcing it in the DBMS. So show me a <broadly defined> data model that captures "huge" amounts of business logic and I will show you a major long-term maintenance headache.

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


Let's see, I should have the Jacobsen book here ... no, not that one.
They say something like, "class design should follow 3NF rules?"
Really?  I'm shocked.  Never have met a practicing OO developer who
would say such a thing.

All the translationists will. B-) A code generator does what you say, not what you meant so they don't have any choice about being unambiguous. The OOA model must be normalized to avoid exactly the same sorts of ambiguities that one would find in an unnormalized RDB.

[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.]


But that is NOT the same thing.

It IS the same thing because those rules and guidelines just paraphrase the RDM. A Class Model is based on exactly the same set theory.

The best and most comprehensive book on OO Class Modeling available is Leon Starr's "Executable UML: How to Build Class Models". He only mentions normalization in passing in one place (pg. 63). However, his primary mechanism for explaining his construction rules is the use of examples that are tables. Such examples are ubiquitous throughout the book and to anyone familiar with 3NF it is painfully obvious how his cookbook construction rules map into 3NF.

Note that hardly any OOA/D books explicitly talk about LSP either. But every book I know of offers construction guidelines to avoid LSP problems. Just because the authors don't explicitly recite the arcane mathematical definitions doesn't mean that their methodologies ignore them.

Similarly, very few OOA/D books explicitly talk about using an asynchronous communication model for behavior in OOA/D. But they all emphasize things like separation of message and method, which implies exactly that sort of model, and they supply methodological practices that are designed to decouple message sending context from receiving method responses.

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


I would just not call that "quite different". 

How about the elimination of the Division table in my example?

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.


You're talking polymorphism?

No. This is strictly a matter of abstraction and OO's flexible view of logical indivisibility. At the higher level of abstraction the code generator would implement the attribute as a special handle to the subsystem containing the expanded view. Thus an attribute read/write access would be transformed into a synchronous subsystem interface call.

[Something of an oversimplification because there is a lot more to the notion of bridges between subsystems in the translation methodologies. For example, there is a notion of 'implicit bridges' where the developer simply identifies a mapping between elements of one subsystem and another at the OOA level. (A very MDA-like activity, BTW.) The transformation engine then automatically creates the appropriate subsystem interfaces and Facade-like dispatching.]

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


I think my argument is that if they map that neatly, they are
identical in the first place.  Your phone number decomposition is just
one system at two levels of abstraction, not two different models.

Exactly. The DBA selects the higher level of abstraction of 'simple domain' for a variety of reasons. However, different levels of abstraction have correspondingly different views and different abstractions. The underlying entity is the same but the abstractions of it can be different.

Consider banking software with an Account object having a Balance attribute. Now consider a GUI subsystem for that banking software where the dominant paradigm is Window/Control. The Account object will be represented in the GUI subsystem as just another instance of Window and the Balance attribute will be represented as an instance of an associated TextBoxControl. One object has become two (though unambiguously linked through a relationship instantiation) and the only link to banking semantics lies in things like title and control labels. Same underlying entity but very different abstractions of it.

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

Not if the applications are properly partitioned. 


Not the same issue at all.

Can the problem be handled?  Sure, with effort.  Does partitioning the
system into encapsulated modules with clean interfaces help?  Sure.
But is that itself a solution?  Not at all, just a tool.

It's just a tool, but for large applications application partitioning is the single most important thing one does during the development. It only takes a few days for even a mega project but getting it right is absolutely critical to all subsequent development and long-term maintenance.

A by-product of application partitioning is the encapsulation of technologies. For example, the problem solution doesn't care if one talks to the user through a web browser, a GUI, command line, or OCR of smoke signals. However, each UI paradigm is quite unique so providing a subsystem for user communications is one of the first things one identifies and encapsulates. Then if one needs to substitute the technology, one just substitutes a new subsystem with the same interface.

In half a century I've watched the UI paradigm evolve from paper tape to punched card to typewriter terminals to character GUIs to full GUIs to browser. Every paradigm shift involved huge amounts of teeth gnashing over legacy code where the old UI technology was littered through all the code. A whole lot of rework and rewriting could have been avoided if those applications had been properly partitioned in the first place.

One of the fundamental criteria for good application partitioning is identifiable subject matter. Most technologies naturally have a unique subject matter (e.g., UI, persistence, hardware access, etc.) that invites encapsulation. More general technologies are often associated with particular sorts of activities (e.g., providing state in a server via XML strings) whose low level manipulations (e.g., parsing XML strings) can be encapsulated in a low level service subsystem. IOW, with good application partitioning the isolation of technologies usually comes for free.


*************
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 there is no trigger, ... favorite database and see what the tools call things. ... 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. ... 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: VAT rate or VAT amount as a column?
    ... Using a trigger means one must not only trust the dbms, one must trust that the trigger was active and in place at all times in the past. ... an update could sneak past it. ...
    (comp.databases.theory)
  • Re: setTimeout and an objects methods
    ... instance property at the time of its execution. ... There is also the question of how often - trigger - will be called. ... var f1 = new Foo; ... What is also happening is that a closure is formed by the ...
    (comp.lang.javascript)