Databases as objects

topmind wrote:
> Thomas Gagne wrote:
>
>> Let me try to clear something up, and thanks to Topmind, Frans, and
>> Stefan for helping me get there.
>>
>> In OO, objects are subclassed to make them more specific, not more
>> general. I consider SQL to be a low level language, as far as RDBs are
>> concerned, because it is application-ignorant.
>>
>
> So you are defining "low level" as application-ignorant? I find that a
> stretch, but let's continue with it as a working/local definition.
>
Low-level meaning further away from my business. Assembly is even
further away from my business than SQL is. Imagine if I'd created a
language that was application-specific, it would be higher-level than
SQL. For instance, if I'd created a language that understood:

purchase 10 shares of IBM into anAccount

It would be pretty darn high-level. By grafting application-aware
constructs into SQL (views and procedures) it becomes increasingly
higher-level.
>
>> It's like C for
>> relational operations. SQL doesn't know anything about my application.
>>
>> So, I subclass Model (so-to-speak) and add data that is domain-specific
>> to create my domain-specific database.
>>
>
> In the app? Please clarify. Do you mean create the database, or an OO
> *view* of the database?
>
I actually mean, "create the database" as in:
create database bookstore;
create table book (...);
>
>> Why access it from applications
>> using the same domain-ignorant language? Instead, I construct
>> procedures that create a domain-specific interface. Instead of the
>> lower-level
>>
>> select * from account, user where user.userId=X and account.userId =
>> user.userId
>>
>> when instead I can use
>>
>> exec getAccountsFor {}userId=X
>>
>
> <snip>
>> ?
>>
>> Besides its brevity, the procedure name clearly communicates the intent
>> of the operation (stbpp pattern: intention revealing message), makes
>> obvious its parameters, and provides a layer of indirection behind which
>> its implementation can change without affecting the procedure's users.
>>
>
> Use comments. And, "getFoo" is hardly an improvement over
> "select...from Foo".
>
That's a strawman. I'm sure you can imagine a more complicated SELECT
statement joining 12 tables, with or without natural joins, UNION'ing to
another select. Sure, I could comment it, or I could just create a
procedure and "exec searchForAccount {}accountId=..."
>
>> From SQL I've constructed procedures to provide a higher-level,
>> domain-specific, language-and-paradigm-neutral interface to a
>> domain-specific database.
>>
>
> How is it more "neutral" than SQL? A stored procedure still has its own
> syntax and rules. What common scenarios are you saving us from?
>
It's neutral in the sense it can be invoked from C, Python, Java, PHP,
and SQL scripts--all with exactly the same behavior in the same
database. Remember, I'm not wrapping anything in OO, I'm just giving
the DB an API that facilitates my domain solution across language and
paradigm boundaries.

A view does the same thing, only it's not as capable as a stored
procedure is. I can create a view to do all kinds of useful projections
that can be called from any language with an attachment to the database,
but a view can't be extended later to record that a user queried it.
> <snip>
> It again comes down to frequencies, and I dissagree with your frequency
> assessment. We are back to where we ended on the last topic. One should
> look at the human effort and frequency involved, not just use
> (disputed) labels such as "low level" etc. to shape our decision.
>
> It is a kind of Frederick Winslow Taylor (time and motion studies)
> style of decision making. In my years of experience, embedding reduces
> the *net* hopping-around effort. Yes, there are times where isolation
> of all the SQL would save time, but not enough to make up for the
> others.
>
I have insider knowledge of two decent sized commercial finance
applications. One can count the number of lines of SQL because it's
separated into procedures and views, the other can only estimate because
the SQL is embedded. The first /knows/ that 37% of its source code is
SQL (sql, procedures, and views) making up 570 distinct requests of the
DB. The second estimates it around 33% but can not count (so quickly)
the number of distinct DB requests there may be. The designer of the
second (rightly, I think) believes counting the number of SQL lines
would be difficult since they're distributed throughout his code, and
include string concatenation for variables and discriminations spread
throughout functions.

If any SQL has to be modified in the first system, a new procedure can
be loaded into the database without affecting any of dozens of
applications. In fact, their source code needn't even be grep'ed to
find references to tables or columns. The second example would require
a grep, a fix, and a redeployment. Even in an ASP moving something to
production prudently requires a trip through some testing.

The chances of something needing fixing or enhancing in 33-40% is 1 in 3
or 2 in 5, depending on which estimate you want to take. All fixes
being equal (they are not), the first system's applications will spend
37% less time going through QA, suggesting they can more more quickly
than competitors with 37% of their source code not isolated from their
applications (as yours sounds to be).

Thomas Gagne wrote:
> topmind wrote:
> > <snip>
> >
> > BTW, Microsoft has ADO, DAO, etc. which are OO wrappers around RDBMS.
> > Java and other vendors do also. Whether OO is the best way wrap RDBMS
> > calls is another debate. My point is they already exist.
> >
> > Further, even if OO *was* the best way to access RDBMS thru an app,
> > <snip>
> >
> You're missing something. I am not advocating wrapping the RDB with OO
> stuffs. I am not saying OO is the best way to access a
> database--directly or through any of the frameworks mentioned above. In
> fact, I'm advocating the opposite. Deal with the DB on its own terms,
> but treat it as an object.

Could you be more specific on "treat it as an object"? OOP is not
consistently defined such that we have to be careful about labelling
stored procedures as an OO concept.

> I'm recommending against accessing it using
> its low-level interface (SQL),

In a sister reply, I challenged your labelling of SQL as "low level".

> but instead that a higher-level
> application/schema/problem domain-specific API be constructed, most
> likely using procedures, and that applications should access the DB that
> way.

Like I've described many times, there are some labor-intensive
drawbacks to that.

>
> --
> Visit <http://blogs.instreamfinancial.com/anything.php>
> to read my rants on technology and the finance industry.

-T-

topmind wrote:
> Thomas Gagne wrote:
>
>> topmind wrote:
>>
>>> <snip>
>>>
>>> BTW, Microsoft has ADO, DAO, etc. which are OO wrappers around RDBMS.
>>> Java and other vendors do also. Whether OO is the best way wrap RDBMS
>>> calls is another debate. My point is they already exist.
>>>
>>> Further, even if OO *was* the best way to access RDBMS thru an app,
>>> <snip>
>>>
>>>
>> You're missing something. I am not advocating wrapping the RDB with OO
>> stuffs. I am not saying OO is the best way to access a
>> database--directly or through any of the frameworks mentioned above. In
>> fact, I'm advocating the opposite. Deal with the DB on its own terms,
>> but treat it as an object.
>>
>
> Could you be more specific on "treat it as an object"? OOP is not
> consistently defined such that we have to be careful about labelling
> stored procedures as an OO concept.
>
>
>> I'm recommending against accessing it using
>> its low-level interface (SQL),
>>
>
> In a sister reply, I challenged your labelling of SQL as "low level".
>
>
>> but instead that a higher-level
>> application/schema/problem domain-specific API be constructed, most
>> likely using procedures, and that applications should access the DB that
>> way.
>>
>
> Like I've described many times, there are some labor-intensive
> drawbacks to that.
>
You've said it many times before, but perhaps you can give an example of
some SQL that's easier to fix when embedded than as a
procedure--including your normal QA procedures and promotion to production.

> If you take away inheritence, you get "network structures" (AKA tangled
> pasta). Dr. Codd sought to escape those by applying set theory, and
> network structures thankfully fell out of favor, until the OO crowd
> tried to bring them back from the dead.

Can you give an example of such as tangled pasta? How did Dr Codd make
network structures fall out of favor?

Thomas Gagne wrote:
> topmind wrote:
> > Thomas Gagne wrote:
> >
> >> Let me try to clear something up, and thanks to Topmind, Frans, and
> >> Stefan for helping me get there.
> >>
> >> In OO, objects are subclassed to make them more specific, not more
> >> general. I consider SQL to be a low level language, as far as RDBs are
> >> concerned, because it is application-ignorant.
> >>
> >
> > So you are defining "low level" as application-ignorant? I find that a
> > stretch, but let's continue with it as a working/local definition.
> >
> Low-level meaning further away from my business. Assembly is even
> further away from my business than SQL is. Imagine if I'd created a
> language that was application-specific, it would be higher-level than
> SQL. For instance, if I'd created a language that understood:
>
> purchase 10 shares of IBM into anAccount
>
> It would be pretty darn high-level. By grafting application-aware
> constructs into SQL (views and procedures) it becomes increasingly
> higher-level.

Why not say "application-specific" instead of "high-level"?

> >
> >> It's like C for
> >> relational operations. SQL doesn't know anything about my application.
> >>
> >> So, I subclass Model (so-to-speak) and add data that is domain-specific
> >> to create my domain-specific database.
> >>
> >
> > In the app? Please clarify. Do you mean create the database, or an OO
> > *view* of the database?
> >
> I actually mean, "create the database" as in:
> create database bookstore;
> create table book (...);
> >
> >> Why access it from applications
> >> using the same domain-ignorant language? Instead, I construct
> >> procedures that create a domain-specific interface. Instead of the
> >> lower-level
> >>
> >> select * from account, user where user.userId=X and account.userId =
> >> user.userId
> >>
> >> when instead I can use
> >>
> >> exec getAccountsFor {}userId=X
> >>
> >
> > <snip>
> >> ?
> >>
> >> Besides its brevity, the procedure name clearly communicates the intent
> >> of the operation (stbpp pattern: intention revealing message), makes
> >> obvious its parameters, and provides a layer of indirection behind which
> >> its implementation can change without affecting the procedure's users.
> >>
> >
> > Use comments. And, "getFoo" is hardly an improvement over
> > "select...from Foo".
> >
> That's a strawman. I'm sure you can imagine a more complicated SELECT
> statement joining 12 tables, with or without natural joins, UNION'ing to
> another select. Sure, I could comment it, or I could just create a
> procedure and "exec searchForAccount {}accountId=..."

Regardless of how large it is, if it has to be changed it has to be
changed. Since SQL changes also tend to mirror app changes and visa
versa, if they are in the same module, we have less hopping around to
do.

> >
> >> From SQL I've constructed procedures to provide a higher-level,
> >> domain-specific, language-and-paradigm-neutral interface to a
> >> domain-specific database.
> >>
> >
> > How is it more "neutral" than SQL? A stored procedure still has its own
> > syntax and rules. What common scenarios are you saving us from?
> >
> It's neutral in the sense it can be invoked from C, Python, Java, PHP,
> and SQL scripts--all with exactly the same behavior in the same
> database.

Same with SQL. That is not a distinquishing feature.

> Remember, I'm not wrapping anything in OO, I'm just giving
> the DB an API that facilitates my domain solution across language and
> paradigm boundaries.
>
> A view does the same thing, only it's not as capable as a stored
> procedure is. I can create a view to do all kinds of useful projections
> that can be called from any language with an attachment to the database,
> but a view can't be extended later to record that a user queried it.

Again, that is a vendor-specific limitation. It is like complaining
about OO because Java does not have multiple inheritance. The lack of
MI is a Java-specific lack, not OO specificly.

> > <snip>
> > It again comes down to frequencies, and I dissagree with your frequency
> > assessment. We are back to where we ended on the last topic. One should
> > look at the human effort and frequency involved, not just use
> > (disputed) labels such as "low level" etc. to shape our decision.
> >
> > It is a kind of Frederick Winslow Taylor (time and motion studies)
> > style of decision making. In my years of experience, embedding reduces
> > the *net* hopping-around effort. Yes, there are times where isolation
> > of all the SQL would save time, but not enough to make up for the
> > others.
> >
> I have insider knowledge of two decent sized commercial finance
> applications. One can count the number of lines of SQL because it's
> separated into procedures and views, the other can only estimate because
> the SQL is embedded. The first /knows/ that 37% of its source code is
> SQL (sql, procedures, and views) making up 570 distinct requests of the
> DB. The second estimates it around 33% but can not count (so quickly)
> the number of distinct DB requests there may be. The designer of the
> second (rightly, I think) believes counting the number of SQL lines
> would be difficult since they're distributed throughout his code, and
> include string concatenation for variables and discriminations spread
> throughout functions.

That is very a minor reason to separate. Is it worth making the app 10%
to 25% more time-consuming to maintain *just* to be able to count
easier? I have to object. Perhaps you have weird managers.

One can get an approximate by sampling about 20 modules, counting the
SQL, finding the percent of source code it consumes, and then count all
the source lines and multiply by the sample percentage.

>
> If any SQL has to be modified in the first system, a new procedure can
> be loaded into the database without affecting any of dozens of
> applications.

It depends. Again, if the same query is used by *multiple* places in an
app, I am not against putting the SQL into a subroutine to simplify its
change.

> In fact, their source code needn't even be grep'ed to
> find references to tables or columns.

Why not? Why is SP's on the database more searchable than in source
code?

> The second example would require
> a grep, a fix, and a redeployment. Even in an ASP moving something to
> production prudently requires a trip through some testing.

I am not against testing either.

>
> The chances of something needing fixing or enhancing in 33-40% is 1 in 3
> or 2 in 5, depending on which estimate you want to take.

I am not sure what you are measuring here. 33-40% of what?

> All fixes
> being equal (they are not), the first system's applications will spend
> 37% less time going through QA, suggesting they can more more quickly
> than competitors with 37% of their source code not isolated from their
> applications (as yours sounds to be).

Please clarify what you are measuring/comparing.

Again, my decision to embed most SQL is based on my experience with
various change frequencies and change scenarios. It is not a "random"
decision. If your experience differs, so be it. Just don't claim it a
universal "best practice"; otherwise I will hold you to the scientific
method.

By the way, one valid reason to separate is that the SQL "programmer"
is different from the app programmer and one does not know the other
language.

-T-

Neo wrote:
> > If you take away inheritence, you get "network structures" (AKA tangled
> > pasta). Dr. Codd sought to escape those by applying set theory, and
> > network structures thankfully fell out of favor, until the OO crowd
> > tried to bring them back from the dead.
>
> Can you give an example of such as tangled pasta?

OO Visitor pattern.

> How did Dr Codd make
> network structures fall out of favor?

By using examples and logic. And users of RDBMS found them more useful
than network DB's. Large network DB's only exist now for specialized
niches. If it was not for an OODBMS push from OO fans, nobody would
even talk about them anymore.

-T-

> > Can you give an example of such as tangled pasta?
> OO Visitor pattern.

Thx, I am still trying to understand it at wikipedia.

> > How did Dr Codd make network structures fall out of favor?
>
> By using examples and logic. And users of RDBMS found them more useful
> than network DB's. Large network DB's only exist now for specialized
> niches. If it was not for an OODBMS push from OO fans, nobody would
> even talk about them anymore.

The CODYSL network data model is mostly a misnomer. It is better called
a Hierarchal/Relational Hybrid Data Model. A true network database
should allows each thing to be related to any other thing, possibly
similar to the human mind. Much data does tends to fit in table-like
structures making RMDB an excellent tool.

Yet, network structures are everywhere. Following example represent a
network where john likes mary, john hates bob, and like is opposite of
hate. A query finds the person with whom john's relationship is
opposite that of with Mary. Can a RMDB user post an equivalent solution
to model/query this simple network? If possible, the solution's
schema/queries should be resilent to future/unknown data requirements.

(new 'john) (new 'mary) (new 'bob)
(new 'like) (new 'hate) (new 'opposite)

(set like opposite hate)
(set hate opposite like)

(set john like mary)
(set john hate bob)

(; Get person with whom
john's relationship is opposite of that with mary)
(; Gets bob)
(get john (get (get john * mary) opposite *) *)

(; Get person with whom
john's relationship is opposite of that with bob)
(; Gets mary)
(get john (get (get john * bob) opposite *) *)

Thomas Gagne wrote:
> topmind wrote:
> > Thomas Gagne wrote:
> >
> >> topmind wrote:
> >>
> >>> <snip>
> >>>
> >>> BTW, Microsoft has ADO, DAO, etc. which are OO wrappers around RDBMS.
> >>> Java and other vendors do also. Whether OO is the best way wrap RDBMS
> >>> calls is another debate. My point is they already exist.
> >>>
> >>> Further, even if OO *was* the best way to access RDBMS thru an app,
> >>> <snip>
> >>>
> >>>
> >> You're missing something. I am not advocating wrapping the RDB with OO
> >> stuffs. I am not saying OO is the best way to access a
> >> database--directly or through any of the frameworks mentioned above. In
> >> fact, I'm advocating the opposite. Deal with the DB on its own terms,
> >> but treat it as an object.
> >>
> >
> > Could you be more specific on "treat it as an object"? OOP is not
> > consistently defined such that we have to be careful about labelling
> > stored procedures as an OO concept.
> >
> >
> >> I'm recommending against accessing it using
> >> its low-level interface (SQL),
> >>
> >
> > In a sister reply, I challenged your labelling of SQL as "low level".
> >
> >
> >> but instead that a higher-level
> >> application/schema/problem domain-specific API be constructed, most
> >> likely using procedures, and that applications should access the DB that
> >> way.
> >>
> >
> > Like I've described many times, there are some labor-intensive
> > drawbacks to that.
> >
> You've said it many times before, but perhaps you can give an example of
> some SQL that's easier to fix when embedded than as a
> procedure--including your normal QA procedures and promotion to production.

I thought I already did. Anyhow, here is another:

We have a typical Employees table. After 9/11 we want to add a new
column "security clearance level". We need to add this to the Employee
input screen and the Query By Example screen.

For the input screen, we need to change the SQL from:

UPDATE emp SET ... foo=&bar& WHERE empID = &empID&

To

UPDATE emp SET ... foo=&bar&, secClrLvl = &secClrLvl& WHERE empID =
&empID&

It is in the same module that generates the screen. Thus I only have to
visit one module to add this column. I can add it both to the screen
field specification and to the SQL related to inserting and updating.

You would have to visit both the screen app module and the SP(s).
Plus, you have to add new parameters.

Often I also use techniques to generate most of the SET clause based on
data dictionaries or validation routines to kill 2 birds with one
stone. Passing such a generated string can be a PITA with stored
procedures.

-T-

Neo wrote:
> > > Can you give an example of such as tangled pasta?
> > OO Visitor pattern.
>
> Thx, I am still trying to understand it at wikipedia.
>
> > > How did Dr Codd make network structures fall out of favor?
> >
> > By using examples and logic. And users of RDBMS found them more useful
> > than network DB's. Large network DB's only exist now for specialized
> > niches. If it was not for an OODBMS push from OO fans, nobody would
> > even talk about them anymore.
>
> The CODYSL network data model is mostly a misnomer. It is better called
> a Hierarchal/Relational Hybrid Data Model. A true network database
> should allows each thing to be related to any other thing, possibly
> similar to the human mind. Much data does tends to fit in table-like
> structures making RMDB an excellent tool.
>
> Yet, network structures are everywhere. Following example represent a
> network where john likes mary, john hates bob, and like is opposite of
> hate. A query finds the person with whom john's relationship is
> opposite that of with Mary. Can a RMDB user post an equivalent solution
> to model/query this simple network? If possible, the solution's
> schema/queries should be resilent to future/unknown data requirements.

One generally uses many-to-many tables for such. Example:

table: Likes
-------------
personRef1
personRef2

table: Hates
--------------
personRef1
personRef2

table: Opposites
------------
factorRef1
factorRef2

Or we could meta-tize it to make it more flexible:

table: PeopleRelationships
-----------
personRef1
personRef2
relationRef // Example: "Hate" ("relation" table not shown)

table: RelationRelationships
--------
relationRef1
relationRef2
relationRef // Example: "Opposite"

I will leave the query work to somebody else.

But this is kind of a "toy" example, such as an AI lab. I would like to
see something more practical.

>
> (new 'john) (new 'mary) (new 'bob)
> (new 'like) (new 'hate) (new 'opposite)
>
> (set like opposite hate)
> (set hate opposite like)
>
> (set john like mary)
> (set john hate bob)
>
> (; Get person with whom
> john's relationship is opposite of that with mary)
> (; Gets bob)
> (get john (get (get john * mary) opposite *) *)
>
> (; Get person with whom
> john's relationship is opposite of that with bob)
> (; Gets mary)
> (get john (get (get john * bob) opposite *) *)

-T-

> But this is kind of a "toy" example, such as an AI lab. I would like to
> see something more practical. I will leave the query work to somebody else.

If it is a "toy" example, how difficult could it be to post the query
to find the person with whom john's relationship is opposite of, that
with mary? Then I can proceed to compare how an rmdb vs a network-type
db handle additional data requirements.