Re: theory and practice: ying and yang

"Paul" <paul_at_test.com> wrote in message news:429c3c03$0$553$ed2619ec_at_ptn-nntp-reader03.plus.net...
> mountain man wrote:
>> My thesis is that if you take this migration to its logical
>> conclusion there may be achievable an optimal stage in which *all*
>> application software in internal to the DBMS in the form of stored
>> procedures.
>>
>> That is precisely what this tool does. It enables a service level
>> portal to the RDBMS for the user which is based solely on the
>> execution of stored procedures, and the ability to handle the
>> consequent sets of data being returned to the user.
>
> Where does the GUI fit into all of this?

The tool *is* the only GUI.

> Even if the DBMS package is bundled with a graphical frontend (e.g.
> Enterprise Manager for SQL Server), it's logically separate from the
> DBMS itself.

Yes, the tool (GUI) is separate from the DBMS and provides generic connection to it. IOW, the same tool (GUI) is used for all different industries, because it contains nothing but the ability to present data sets.

>>> Isn't what you've done standard practice in designing enterprise
>>> database reporting systems? i.e. to have a stored procedure (or in
>>> an ideal world, a view) for each report?
>>
>> Yes. However, the tool enables dynamic drill-down from summary to
>> intermediate summary to detail to log levels on the simple basis that
>> one stored procedure can be called from another. Therefore there
>> are a series of stored procedures for each report, dependent upon the
>> analytical solution being provided.
>
> Why stored procedures and not views? Views can be nested just as well,
> if not more easily. In practice I use stored procedurs as well, but only
> as a workaround for optimizer inefficiencies. In an ideal DBMS would you
> use views?

Both sprocs and views are essentially select statement. Perhaps our preferences are simply based on past use?

>>> Are you saying that this method is preferrable to the "fat client"
>>> or "middleware" method, where you have business logic held outside
>>> the database in things like Cognos, Business Objects, or Crystal
>>> Reports? I thought this was pretty much the same as most other
>>> people think.
>>
>> Absolutely preferable, because it entirely OBVIATES the client. End
>> of story. There is no client required, and therefore no middleware
>> required, at all. Minimal number of moving parts in that all the
>> code and data are being defined once and for all in ONE SOFTWARE
>> LAYER.
>
> Well you have to have some sort of client, surely, because the whole way
> that DBMSs work is by having a central server that holds the data and
> multiple clients requesting information from it (whether via raw SQL or
> via stored procedures, and whether the clients sit on the same physical
> machine or on remote machines). I have to talk to the database somehow,
> and that requires a client, however thin.

Yes, this GUI/tool is the sole client, and its probably the thinnest client you'll ever find because the same client is used for reporting in all industries (eg: intellectual property management systems, college enrolment systems, medical and physio practitioners, practice management systems, manufacturing systems, etc).

IOW, the client never needs to be changed. All change is internal to the (R)DBMS.

>> This is financially preferable because the change management of
>> multiple software layers is an ongoing huge expense, and really all
>> that is being done is the coordination of schema-related tasks
>> between the database and anything external to it (ie: the application
>> layer).
>
> OK, that sounds reasonable. But don't you still have the problem that if
> the table designs change, you have to coordinate the corresponding
> changes in all the stored procedures that access those tables? Granted,
> they're now both in the same language, SQL, but it's still an issue.

If the design were the adding of one column to a table, all stored procedures using that table would not require change. Only the sp's that need to include or make reference to this new column would need to be altered.

And, yes, it is still an issue, but dramatically simplified and bound within the DBMS environment.

>>> Is the point you're making that the procedural language used in
>>> stored procedures isn't part of the relational model?
>>
>> Not in the least, I believe that SQL is computationally complete and
>> there is in fact no problem -- however difficult -- that cannot be
>> overcome using SQL and other native utilities that are available to
>> the current industry RDBMS machinery.
>
> How can SQL talk to a remote machine to draw a window on the screen and
> populate it with controls? Surely it requires drivers to enable clients
> to perform these functions?

The tool is a window on a screen expecting to be populated by a generic dataset (the result set obtained from the execution of a stored procedure).

The user on the remote machine talks to the tool (by a simple line reference of an array, the clicking of a mouse on the summary report line that says "Widgets").

The tool sends this info to the RDBMS along with the fact that a second level report has been invoked.

The RDBMS executes the second level stored procedure *using* the info supplied (in this case "Widgets").

The RDBMS sends back to the tool (GUI) the result set, and the tool displays this to the user.

The user is able to (multiple) sort, or filter upon, this result set, and/or print preview it, and/or export it to various industry standard formats.

>>> In which case all the stored procedures you have could be replaced
>>> with views, which are part of the relational model. This approach
>>> would only break down for the cases I mentioned before i.e. when
>>> you have several data manipulation statements in a single
>>> transaction. But I don't see any of these cases in your example
>>> application.
>>
>> The dynamic drill-down information reporting obtained by chaining
>> multiple series of stored procedures together cannot be replaced by a
>> view.
>
> I don't quite understand what you mean by "drill-down" and I can't find
> references to this on your site.

Here is an example Summary Level:
http://www.mountainman.com.au/software/southwind/southwind_menu_01_task_101_Sales_Analysis_by_Client_Level_1_of_2_CEO.JPG

The user selects to view the detail for "Hungry Owl", on the above screen by double-clicking that row. As a result, the details are shown: http://www.mountainman.com.au/software/southwind/southwind_menu_01_task_101_Sales_Analysis_by_Client_Level_2_of_2_CEO.JPG

> Surely you can have one view that
> provides detailed information, then a second that aggregates the first
> view some more?

Drill down is IMO normally associated with the first screen being a summary, and then the second and subsequent being intermediate and then later, the detail records.

> Are you talking about returning multiple result sets in
> a single go?

No, only one result set is ever returned at once.

>> You dont see any views in my example because I have never yet found
>> an instance where a view is necessarily a better implementation that
>> by performing the task without use of a view.
>>
>> Theoretical limitations about SQL and Views by Date et al are not
>> limitations at all from my perspective, because views are not
>> essential. End of story. ;-)
>
> I think views are nicer from the end-user point of view, but I suspect
> they are a lot more difficult to implement from the point of view of the
> DBMS designer, being declarative rather than procedural.

create view TEST_VIEW
as
select * from table

create procedure TEST_PROC
as
select * from table

How is one declarative and
the other procedural?

Thanks,

-- 
Pete Brown
Falls Creek
OZ
www.mountainman.com.au