Re: Business objects, subset of collection

Responding to Frebe...

select i.invoiceid
from invoice i join payment p on i.invoiceid=p.invoiceid
group by i.invoiceid
having sum(p.amount) < i.amount and datediff(now(), i,duedate) >= 10
This is the kind of code I write every. Even though the number of
invoices and payments are very high, the queries perform within a
number of millis. The customer is happy, I am happy.
Once again, if that is all you are doing, that is CRUD/USER processing;
you are just moving piles of data back and forth between the UI and the RDB.
Maybe you can show your code performing the same task.
You want code for inventory forecasts? A Linear programs to allocate
advertising budget to various markets' media? A simulation model of
atmospheric diffusion?
No. I was asking for the OO equivalence for the SQL statement above.
What's the point? Sure I could write AAL or OOPL code to do that. But
the main point here is that one would not want to solve the problem that
way unless there was no other alternative.

The problem is to create reminders for all invoices are not payed and
due date is passed since 10 days. The solution in SQL
could look like.

insert into reminder (invoiceid, remind_date)
select i.invoiceid, now()
from invoice i join payment p on i.invoiceid=p.invoiceid
group by i.invoiceid
having sum(p.amount) < i.amount and datediff(now(), i,duedate) >= 10

If you want to solve the problem another way, you are welcome. There
are of course other alternatives. The question is if they are better,
or not.

IOW, you are providing a common generic data access query problem and
asking for an OO equivalent. But I'm talking about solving customer
specific problems, not generic data storage access problems.

This is a customer specific problem. The problem has very little to do
with storage.

But you are not solving some problem for the customer. All you are doing is presenting data that already exists in the data store in a convenient format for the user (i.e., the notification). That is quintessential CRUD/USER processing.

The techniques used for view conversion are specialized to that particular <software> problem. In particular, they are specialized to the generic data storage access provided by an RDB. IOW, you query is about /extracting/ the data and /presenting/ it, but it doesn't /do/ anything with the data.

Now suppose we need to perform an analysis of accounts payables that forecasts how many accounts will be paid late, what the total late payments will be, and what the probability is that each individual customer will be late. To do that one would need credit information about the customers and a historical track record of past payments.

To obtain the track record one needs to extract the historical payment data from the data store. One might use a query that looks very much like yours in the subsystem dedicated to talking to the DBMS. But once that data is extracted it will be stored in objects that are tailored to the regression and probabilistic analysis. IOW, the real problem solution requires different data structures and will use different techniques to solve the customer's problem. The extraction of the data from the DBMS is quite peripheral to that and would be isolated from the rest of the problem solution by encapsulation in a subsystem that hides the DBMS mechanisms.

To put it another way, the non-CRUD/USER problem starts after the data is extracted from the database using a query like yours. The analogous problem solved compared to your example is what the customer does with the notifications after they have been produced by your query.

Corollary: a non-CRUD/USER application will probably talk to an RDB using SQL or whatever when it extracts or updates data. But that will be a peripheral task to the problem in hand so it would be fully encapsulated in a subsystem. In that subsystem the subject matter /is/ the data store and its mechanisms need to be employed.


The OO
solution would be tailored to a specific problem so it would very rarely
have need of that approach.

The solution (SQL code) is tailored to the specific problem.

That's kind of the point. The "algorithms" for CRUD/USER processing are
very similar because they all solve the same problem: converting RDB
views of the data to UI views and vice versa.

My example aboive has nothing to do with UI.

Those notifications /are/ the UI; they are the interface through which the application communicates with the user. There is no law that requires a UI to be a form-based GUI or browser interface. Using smoke signals, heliographs, or whatever for the UI just requires different hardware and view conversions.


Caching is managed by the database. Caching is not the concern for the
application designer.
Nonsense. Every non-CRUD/USER application I have ever seen had a
client-side cache in some form. By the time the requests get to the
DBMS, it is too late. The DBMS caches just manage server access and
resources (unless you are talking about memory mapped OODBs). One caches
on the client side to minimize DB access.
Another way to elminiate the inter-process communication overhead is
to use stored procedures for "complex processing". That is why most of
TPC (www.tpc.org) implementations rely heavily on stored procedures.
Obviously you could also use a memory mapped SQL database.
The web site didn't have a whole lot to say about stored procedures (but
I didn't plow through all the papers). However, I don't think it has
much to do with the point. There are four contexts of stored procedures:

(1) Enforcing data integrity constraints on the data storage itself.
Such stored procedures are triggered directly by write operations. That
makes a whole lot of sense since data integrity is pretty important to
data storage. It also makes sense to optimize those procedures to the DB
engine.

(2) Application modules that are executed on the client side but are
stored on the server side. This is common in RAD environments because
the event code needs to be stored and managed transparently somewhere
/within/ the IDE. However, it actually adds overhead to the application
because the stored procedure needs to be read from the DB into the
client execution space rather than being there initially (e.g., as in a
compiled image).

(3) Application modules that are executed on the server side at the
request of the client. These are also common in RAD environments and
usually consist of stored queries or joins. For these to execute on the
server side all they can do is extract/update data in the DB because
there is no mechanism for interacting with the client's data structures
_as they execute_. IOW, what one is storing are CRUD/USER operations on
the stored data whose timing is dictated by the application.

(4) Application models that capture business rules and policies and are
triggered by other data accesses on the server without the application's
knowledge. This is, indeed, more efficient because they can be optimized
around the DB engine. It is also a maintainability nightmare for large
applications. Any application developer that creates such stored
procedures should have their thumbs broken.

In summary, (1) is part of the DBMS franchise; (2) actually adds
overhead; (3) is only useful for CRUD/USER processing (or to handle DB
access for the application); and (4) should be grounds for summary
dismissal. So what is the benefit of stored procedures for a
non-CRUD/USER application?

You are ignoring two other scenarios:
1. You need a user-defined function in your SQL statements. (select
age(birthdate) from employee)

You will have to put more words around that. Any selection one does in SQL is based on values for schema fields. One constructs the SQL query based on what data one needs (i.e., what fields must be accessed). If the procedure is to be stored for reuse, the needed data must be predefined and all one can do is parameterize the selection values.

If you are talking about passing functions a la functional programming, then one in knee deep in (4) and deserves what they get.

2. Performance reasons. By putting "complex processing" inside the
DBMS, you can eliminate the inter-process overhead and have major
performance improvments. Browse the source code for one of the TPC
implementations, and you will see how they do it.

But the only thing being optimized is the data access _given the RDB storage paradigm_. The DBMS has no capability that can optimize for particular problem solutions. The issue here is manipulating the data after it has been accessed.


SQL databases excel in CRUD/USER processing, which is what I keep
saying. Conversely, they suck once one gets out of that realm.
Since your definition of CRUD/USER since to be extremly wide, I am
quite happy with this statement.
How is it extremely wide?

Any example I have given you, you have classified as CRUD/USER.

At a fundamental level CRUD/USER applications
only solve one problem: data view conversions.

The example I gave you is not about data view conversions.

Of course it is! It maps that RDB view (e.g.,
fields like invoiceid, duedate, etc.) into a notification in the UI. Whether an invoice has been paid or not is inherent and unambiguous in the data in the database. You are just converting the RDB view of the data into a more convenient notification view.

Try this question: can you identify any piece of information in the notification that is not a field in the RDB or is not directly derived from fields in the RDB as a dependent variable?



They are limited to the
very fundamental tasks of creating, extracting, updating, sorting,
reporting, and deleting data stored in persistent storage.

It is like saying that the only thing a CPU do is the fundamental
tasks of adding bits.

By George, I think he's got it! A DBMS doesn't solve customer problems any more than a CPU can. The data in a DBMS must be manipulated to solve problems just as fundamental CPU operations must be organized in the proper sequence by a program. By comparison a CPU is actually a cretin compared to a DBMS; at least the DBMS directly supports solving the view conversion problem because it lives at a much higher level of abstraction.


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

H. S. Lahman
hsl@xxxxxxxxxxxxxxxxx
Pathfinder Solutions
http://www.pathfindermda.com
blog: http://pathfinderpeople.blogs.com/hslahman
"Model-Based Translation: The Next Step in Agile Development". Email
info@xxxxxxxxxxxxxxxxx for your copy.
Pathfinder is hiring: http://www.pathfindermda.com/about_us/careers_pos3.php.
(888)OOA-PATH
.

Relevant Pages

  • Re: choices regarding where to place code - in the database or middle tier
    ... Sure, the DBMS is a good place for simple referential integrity constraints, ... to 4 separately-running-but-pipelined stored procedures, ... A typical user would enact a 100 or so business functions per day. ... own stored procedures' by storing the SQL for every business query in the DBMS ...
    (comp.lang.java.databases)
  • Re: choices regarding where to place code - in the database or middle tier
    ... Sure, the DBMS is a good place for simple referential integrity constraints, ... to 4 separately-running-but-pipelined stored procedures, ... A typical user would enact a 100 or so business functions per day. ... own stored procedures' by storing the SQL for every business query in the DBMS ...
    (comp.lang.java.programmer)
  • 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)
  • Re: Q: Open-source to access MS SQL server 2000/2005 DBs from Ada ?
    ... versions are not of importance)? ... I would recommend ODBC, this makes the *binding* independent from any DBMS. ... Of course, the *queries* would depend on the DBMS, but it is easy to localize these dependencies. ... Stored procedures could be a problem, especially if you want to keep some portability. ...
    (comp.lang.ada)
  • Re: Sorting A-Z
    ... You can never rely on the order of records in tables: relational database ... theory says that the DBMS is allowed to put the data wherever it wants to. ... I have a Payment Method table with two fields (Payment ID, ...
    (microsoft.public.access.tablesdbdesign)