The Practical Benefits of the Relational Model
Nathan Allan
Thanks to Paul G. Brown for the thread, "The Theoretical Foundations of
the Relational Model." In the same spirit, I would like to present a
(partial) list of practical benefits that the Relational Model enables.
Keep in mind that I am NOT necessarily describing SQL as it does fall
short of many of these points. Please feel free to point out any major
ones I miss.
-ALL integrity constraints (AKA business rules) can be enforced
declaratively. Why is this good?:
-Less work - ...just tell the system what must be "true"
-System optimizable - if I say that only Green items can be put into the
'parts' table, than when I ask for all Red parts, the system can 'know'
that it isn't even worth looking for Red parts.
-Transactional implications - with procedural code, the developer must
handle certain transactional implications like when two tables (or
objects ;) reference each other.
-Less buggy - no procedural code means that bugs are isolated to the
well tested system, not the app developer's code.
-Easier to maintain - just modify the constraint definition.
-For more read "What not How" from C. J. Date.
-Transaction management is (can be) system provided. OO engineers are
re-inventing this wheel, but worse they are moving it into the domain
of the application developer. Have you written a 'compensating resource
manager' for your object hierarchy lately (so you can rollback/redo
changes)? Or perhaps tried to implement transaction isolation across a
distributed set of objects? Just the fact that the RM allows the system
to COMPLETELY automate all facets of transaction management should say
something!
-Paging/buffering is (can be) system provided. Due to the
implementation independent nature of the RM, main memory vs. long term
storage is not part of the application development paradigm. The result
is that memory management can be handled (and optimized) by the system,
not the application developer. In the RM, the app developer merely
deals with global and/or local variables. How those variables are
loaded, stored is completely up to the system. In OO, the developer is
responsible for 'persistence'; granted the OS will most likely manage
the paging of main memory at a low level.
-Applications can be completely described independent of a particular
platform implementation. Applications that don't become obsolete just
because platforms change... what more is there to say on this!
-Application logic can be centralized rather than scattered throughout
the application layers.
-Mathematical style data manipulation and analysis lets us both 'look
at' AND update our data from ANY perspective.
-Conceptual simplicity. There are only a few concepts to learn in the
RM, and they are relatively simple. This makes training, implementing,
and maintaining application much easier. To date (pun intended), no one
has presented a model that is simpler (and is complete).
-Concepts are well defined. There are not the gray areas found in OO...
like which class to put a 'binary' method into, or whether to use a
method or a 'write-only' property. How about the fact that the V-table
is tied to the type, not the operator (so no future 'virtual' operators
can be defined by someone other than the type designer).
-It is not necessary to traverse logically un-related information. In
OO, for example, I may decide to put SaleItems 'under' (or as a list
within) Sales. Then when I want to see how many we have sold of a
particular part, I can't just join the Parts with the SaleItems, I have
to get to the SaleItems 'through' the Sales. This argument applies to
any data management solution that arbitrarily decides that all data is
hierarchical.
-Specialization by Constraint and Generalization by Constraint. In
English this means that I can treat an ellipse as a circle if the
ellipse 'really is' as circle.
-Performance tuning without messing up my application logic. The RM is
a CONCEPTUAL model and allows for an implementation to do really about
anything so long as the prescribed logic is preserved. Data could be
stored in any way (including redundantly or distributed) without
changing the application logic.
-User interface derivability. It is possible (though never accomplished
until recently) to provide COMPLETE user interfaces that are derived
from any perspective of the data. This is only possible due to things
like updateable views (expressions) and declarative integrity rules.
People (even relational folks) are very skeptical about this, but I have
seen it work.
-"metadata" inference. In other words, it is possible, with a
Relational implementation to no only know information like referential
integrity constraints (FKs), keys, and other meta-data about base
tables, but also about any arbitrary expression. This allows us, for
example, to ask for the "default" column values of an arbitrary
expression, or to know what the implicit references are of any
expression. This also happens to be one of enabling factors for user
interface derivation.
-System provided optimization. A Relational system can optimize at many
levels. The internal (storage/query processing) level can optimize
physical access paths based on heuristics. Semantic optimizations can
be made by analyzing and transforming the algebra. Extended literals,
Contradictions, and Tautologies can be detected and optimized by the
system. Further, the system can optimize the external (or consumer/user
interface) of the system by detecting constraints that can be evaluated
on the client where they can be enforced without a network round-trip.
-Automatic navigational transformations. A system can automatically
re-formulate expressions (again due to the formal algebra) to navigate,
"search within" and fetch subsets of data AS THE USER CONSUMES IT. In
other words, the developer doesn't have to pre-determine the granularity
of data to be retrieved by the client. The system can "pipeline" the
information to the client as it is needed. The performance, memory,
bandwidth, and productivity benefits of this item alone are staggering.
-Fewer maintenance points. With the application logic centralized,
there are few to zero 'copies' of the application schema to maintain.
-Data retrieval/manipulation is provided by the system. This may seem
like a no-brainer, but in the OO realm, if you want a Join, you write a
Join. You don't get these set-based operations 'for free'.
-Well defined and formal "model" for type inheritance. This actually
isn't a Relational thing because type is an orthoginal (apples and
oranges) concept to the RM. I still think it is worth making explicit
because it should be clear that OO doesn't have an exclusive on the
concept. Well defined inheritance is beneficial because the SYSTEM can
help us enforce domain constraints and such. Not to mention providing
the app developer with the ability to rely and deterministic and well
defined behavior from the system.
Now I should state that I use an OO programming language daily. As C.
J. Date has pointed out, OO brought one... perhaps two good concepts to
the table (neither of which are original but never mind):
-User defined types (OOP encourages the practice)
-Type inheritance.
OO is is not a "model" and is certainly not a data management solution.
--
Nathan Allan
--
Posted via http://dbforums.com
mountain man
news:1745087.1...@dbforums.com...
...[trim]....
> -Application logic can be centralized rather than scattered throughout
> the application layers.
Centralised where?
Farmer Brown
Nathan Allan
>> -Application logic can be centralized rather than scattered
>> throughout the
>> application layers.
>Centralised where?
In the catalog of the RDBMS.
mountain man
>
> >> -Application logic can be centralized rather than scattered
> >> throughout the
> >> application layers.
>
> >Centralised where?
>
> In the catalog of the RDBMS.
You mean that some form of information index
concerning the application logic resident elsewhere
(in the applications software environment on the
desktop or application server) is kept in the
catalog?
The logic is still "out there" in the Apps,
is it not, at this moment in history?
Jan.Hidders
That depends on what you defined as "the application logic". Some would say
it is the set of static and dynamic constraints that should hold for the
data in the database. Others would say that it also includes that what
happens when to what part of the data given certain new information and user
actions (think of triggers here). An even wider definition could include
what the application does and shows (windoes, menus, buttons, et cetera) to
the user. I have the feeling this latter definition is what you use, but
Nathan is probably using it in the first and narrowest meaning since that is
how it is mostly used in a database context.
-- Jan Hidders
mountain man
news:3d6de600$1...@news.uia.ac.be...
> In article <p7ib9.17835$g9.5...@newsfeeds.bigpond.com>,
> mountain man <prfb...@magna.com.au> wrote:
> >
> >"Nathan Allan" <mem...@dbfoums.com> wrote in message
> >news:1748698.1...@dbforums.com...
> >>
> >> >> -Application logic can be centralized rather than scattered
throughout
> >> >> the application layers.
> >>
> >> >Centralised where?
> >>
> >> In the catalog of the RDBMS.
> >
> >You mean that some form of information index concerning the application
> >logic resident elsewhere (in the applications software environment on the
> >desktop or application server) is kept in the catalog?
> >
> >The logic is still "out there" in the Apps, is it not, at this moment in
> >history?
>
> That depends on what you defined as "the application logic". Some would
say
> it is the set of static and dynamic constraints that should hold for the
> data in the database. Others would say that it also includes that what
> happens when to what part of the data given certain new information and
user
> actions (think of triggers here). An even wider definition could include
> what the application does and shows (windoes, menus, buttons, et cetera)
to
> the user. I have the feeling this latter definition is what you use, but
No, my definition of this "application logic" is not restricted to the
client
application screens, or the database contraints, triggers, sprocs, etc. My
definition would have to include the concept of "organisational
intelligence"
and if the organisation were busines oriented, then this would be called
the "business intelligence" or "business rules".
Some of the organisational intelligence OI has been migrated from the
Apps environmet into the RDBMS --- no argument about that, but
in most apps the bulk remains in the app_code, on the client.
> Nathan is probably using it in the first and narrowest meaning since that
is
> how it is mostly used in a database context.
> -- Jan Hidders
I can see that there are probably expressly reserved terms in the
theory of databases which connote specific meaning, in which
case it is my misunderstanding of the academic side of the coin.
Best wishes
Farmer Brown
Falls Creek
OZ
Paul Vernon
>application screens, or the database contraints, triggers, sprocs, etc.
My
>definition would have to include the concept of "organisational
>intelligence" and if the organisation were busines oriented,
> then this would be called the "business intelligence" or "business
rules".
Humm, so your "organisational intelligence" = "business rules" if we are
talking about "business oriented organisations"
So why exclude NGOs etc?
But more to the point "business rules" = Database Constraints as far as
I'm (and, oh him again: CJ Date) are concerned.
If you disagree, give me an example of piece of "organisational
intelligence" that you don't think can be expressed as relation values or
as database constraints.
Regards
Paul Vernon
Business Intelligence (not a term I have a great love for), IBM Global
Services
Jan.Hidders
Paul Vernon <paul....@ukk.ibmm.comm> wrote:
>
>Hi Jan.
Hello Paul,
>By dynamic you mean what Date would call transition constraints, yes?
Exactly.
>On this subject, don't dynamic constraints just become static
>constraints in the case where you keep all history in your database?
Yes, they do.
>Besides, that is, the need to enforce the 'arrow of time' (i.e. can't
>update the history tuples). So enforcing the 'arrow of time' becomes the
>only required dynamic constraint
That depends a bit on what you mean with "keeping all your history in your
database". Given a time-varying predicate P(x) there are two moments to
consider:
1. the moment the database was told that P(a) holds and
2. the moment at which P(a) is true (this can also be an interval).
These two moments are often assumed to be the same, but in reality almost
never are. If you only store moment 2 then it should be possible to update
history tupels since you may learn afterwards that P(a) did in fact not hold
at moment t. However, if you store moment 1 then indeed you should never
update historic records.
But, the transition constraints usually say something about the predicate in
function of the moments of type 2.
You see the problem here?
>E.g. with table T and attribute A of type INTEGER
>and dynamic constraint X: A > A' (where A' is the old value of A)
>
>becomes
> with table H and attribute A of type INTEGER and B of type TIMEPOINT
>and static constraint X: H1.A > H2.A FROM H as H1, H as H2 JOIN H1.B
>IS_SUBSEQUENT_TO H2.B
>
>with the dynamic constraint B = CURRENT TIMEPOINT
Yes, although I prefer good old-fashioned tuple-calculus here:
FORALL H1, H2 IN H : IF H1.B > H2.B THEN H1.A > H2.A
and we shouldn't forget that this formulation is easy here because the
transition condition happens to be transitive. If it isn't then its
formulation becomes a bit more difficult.
-- Jan Hidders
Jan.Hidders
mountain man <prfb...@magna.com.au> wrote:
>
>No, my definition of this "application logic" is not restricted to the
>client application screens, or the database contraints, triggers, sprocs,
>etc. My definition would have to include the concept of "organisational
>intelligence" and if the organisation were busines oriented, then this
>would be called the "business intelligence" or "business rules".
Oh dear, I'm affraid that doesn't make it any more clear what you are
talking about. The terms "business logic" and "business rules" are also
often used quite loosely and the best and most exact definition is the one
it gets in database theory and that is in fact again identical to the set of
static and dynamic constraints for the data we already talked about. So what
kind of rules are you talking about then, and why are you so sure that they
cannot be modelled as database constraints? Unless you can explain this,
your remarks run the risk of remaining meaningless.
-- Jan Hidders
Nathan Allan
> concerning the application logic resident elsewhere
> (in the applications software environment on the
> desktop or application server) is kept in the
> catalog?
A RM implementation must have some type of repository for such things
as operators, types, variables (including relation variables) and
such. This is sometimes called a "catalog" in DBMS speak.
> The logic is still "out there" in the Apps,
> is it not, at this moment in history?
What logic? The presentation logic is in the presentation layer, the
storage logic is in the storage layer, etc. Ideally each of these
layers is "driven" from a central definition, but they no doubt must
exist!
See my other posting about the elusive "inside" concept. The DBMS is
PART of the application.
BTW, sorry my other posts got stuck as replies to the root post.
--
Nathan Allan
Nathan Allan
Mountain Main,
What does this elusive concept of being "inside" the DBMS really mean?
-Everything in the same OS "process" space (so much for multi-user)?
-Everything that is managed by the system (but what does "managed"
entail)?
-Information that is stored by the system (code also being information)?
-Everything that is not "outside" the system (obvious recursive
definition)?
I would suggest that this topic is a red herring. The bottom line of
development productivity is determined by the amount of work required of
the app developer to bring a general system into a specific state. The
resulting application itself forms a DBMS of sorts. Not a general
purpose one, but one specifically made up of all components necessary to
provide the designated services.
All this said, the essence of the problem is that we want no more
"layers" of complexity than are necessary to create the application. We
also want to describe the application as much at the same "level" as
possible. To this end, it is terribly useful to have a single language
in which to describe the application... then have the rest of the system
(possibly even consisting of other components including the clients) be
able to derive their behavior appropriately. This may sound far
fetched, but I know it is possible because I have seen a Relational
implementation that does this. I will stop here since this is a theory
group. ;-)
mountain man
news:akli2v$2jve$1...@sp15at20.hursley.ibm.com...
> >my definition of this "application logic" is not restricted to the client
> >application screens, or the database contraints, triggers, sprocs, etc.
> My
> >definition would have to include the concept of "organisational
> >intelligence" and if the organisation were busines oriented,
> > then this would be called the "business intelligence" or "business
> rules".
>
> Humm, so your "organisational intelligence" = "business rules" if we are
> talking about "business oriented organisations"
>
> So why exclude NGOs etc?
> But more to the point "business rules" = Database Constraints as far as
> I'm (and, oh him again: CJ Date) are concerned.
>
> If you disagree, give me an example of piece of "organisational
> intelligence" that you don't think can be expressed as relation values or
> as database constraints.
No. I agree this is emminently feasible in theory.
See below.
> Regards
> Paul Vernon
> Business Intelligence (not a term I have a great love for), IBM Global
> Services
Thankyou also Jan for the response, very similar to the
above, inasmuch as I probably need to be educated as to
the formalised and reserved manner of expressing things
in "theory of databases".
My problem stems from the fact that I studied database
theory in Uni only for a short time in the very early 70s.
From 1984 to the present I have been involved with the
management of production databases, and my experience
is from government and business...SQLServer rdbms
and Wang Labs DBMS
From the above there are two issues to try and clarify:
1) What are the NGO's (which I have excluded?)
2) I do not disagree. In fact it is my observation not from
theory but from the production floor that has convinced
me that "IN THEORY" there is nothing that cannot be
expressed by the SQL at the RDBMS level.
In practice however, one invariably finds that the bulk
of this OI/BR/etc is in fact resident in the applications
code and NOT in the RDBMS. This was my point.
ie: That RDBMS applications retain much of the OI
at the applications software level, inside explicit code
sections in VB,C,Java, etc, etc, etc.
ie: that the physical location of the expression of the
relational values is not always inside the RDBMS,
that although there appears to be some form of physical
migration of OI/BR happening (eg: increased use of
stored procedures, triggers, contraints, etc) there is
still bulk resident ALSO within the Apps Environment
and not resident where I would like to see it exclusively
located, internal to the RDBMS (as per CJDate, etc)
I would like to stop here in order to determine
whether you think I am making any sense.
Thanks for the responses to this issue.
Best wishes,
Farmer Brown
Falls Creek,
OZ
PS: My apologies to Nathan Allan who started this thread
for tangentiating in this manner a document which looked
very broad yet cohesively structured.
Paul Vernon
some of which are arguably, 'not business orientated'
>2) I do not disagree.
Nor, do we.
If you follow Nathan's email address, you'll find at least one attempt to
put this theory into practice.
Regards
Paul Vernon
Business Intelligence, IBM Global Services
Jan.Hidders
mountain man <prfb...@magna.com.au> wrote:
>
>2) I do not disagree. In fact it is my observation not from theory but
>from the production floor that has convinced me that "IN THEORY" there is
>nothing that cannot be expressed by the SQL at the RDBMS level.
Do you mean that SQL can express all queries? That's not true, it may often
be in practice, but certainly not in theory; SQL is not computationally
complete. Or do you mean that with SQL you can express all database
constraints? That's also not true. Roughly speaking it only covers
first-order logic.
>In practice however, one invariably finds that the bulk of this OI/BR/etc
>is in fact resident in the applications code and NOT in the RDBMS. This
>was my point.
Yes, but (1) you still haven't explained exactly what you mean with
"OI/BR/etc" and (2) it's not clear whose fault that is. Many RDBMSs already
allow you to formulate complex database constraints. What do you think is
the problem then? Are the constraint languages not powerful enough? Is it
not efficient enough? Are the application designer not able to formulate the
database constraints in some formal logic? Or what?
-- Jan Hidders
Paul Vernon
>>constraints in the case where you keep all history in your database?
>Yes, they do.
Cool
>>Besides, that is, the need to enforce the 'arrow of time' (i.e. can't
>>update the history tuples). So enforcing the 'arrow of time' becomes the
>>only required dynamic constraint
>That depends a bit on what you mean with "keeping all your history in
your
>database".
"No Data Descruction" i.e. no information is ever deleted.
>Given a time-varying predicate P(x) there are two moments to
>consider:
>1. the moment the database was told that P(a) holds and
>2. the moment at which P(a) is true (this can also be an interval).
>These two moments are often assumed to be the same, but in reality almost
>never are. If you only store moment 2 then it should be possible to
update
>history tupels since you may learn afterwards that P(a) did in fact not
hold
>at moment t. However, if you store moment 1 then indeed you should never
>update historic records.
Yep, with you there. Bi-temporal time, that kind of stuff.
Darwen, Date & Lorentzos's upcomming Temporal Databases book should be
good for this topic.
>But, the transition constraints usually say something about the predicate
in
>function of the moments of type 2.
Not with SQL triggers that compare OLD and NEW rows, thats type 1
> FORALL H1, H2 IN H : IF H1.B > H2.B THEN H1.A > H2.A
>
> and we shouldn't forget that this formulation is easy here because the
>transition condition happens to be transitive. If it isn't then its
>formulation becomes a bit more difficult.
Yep, I'ld noticed that.
I guess I'm suggesting that specifying constraints in terms of OLD and NEW
databases is not a good idea.
For one its a pain if you need OLD-1, OLD-2,.. or
OLD-between-5-and-10-Secs-ago,...
Or, in my case, bulk load intervals (or sampled snapshots) of history into
a data warehouse. To check dynamic constraints, the DBMS would need to map
the OLD concept to any attributes holding Type1 info.
And also suggesting, that there is a logical difference between a database
that includes the concept of one directional time (via CURRENT TIMEPOINT
or some such device) and a database that does not. I.e. the constraint
OLD.A > NEW.A only has any meaning becuase I can't travel back in time. ?
mountain man
> In article <j%yb9.18405$g9.5...@newsfeeds.bigpond.com>,
> mountain man <prfb...@magna.com.au> wrote:
> >
> >2) I do not disagree. In fact it is my observation not from theory but
> >from the production floor that has convinced me that "IN THEORY" there is
> >nothing that cannot be expressed by the SQL at the RDBMS level.
>
> Do you mean that SQL can express all queries? That's not true, it may
often
> be in practice, but certainly not in theory; SQL is not computationally
> complete.
Can you provide me with an example of computation
exemplifying the (theoretical) incompleteness of SQL?
> Or do you mean that with SQL you can express all database
> constraints? That's also not true. Roughly speaking it only covers
> first-order logic.
While that may be, armed with SQL code, some R&D and
an RDBMS scheduled task queue I can enforce all constraints
without exception whatever they may be.
> >In practice however, one invariably finds that the bulk of this OI/BR/etc
> >is in fact resident in the applications code and NOT in the RDBMS. This
> >was my point.
>
> Yes, but (1) you still haven't explained exactly what you mean with
> "OI/BR/etc"
Organisational Intelligence/Business Rules/etc
The guts of the specific database application software code
that the organisation or business happens to use.
>and (2) it's not clear whose fault that is. Many DBMSs already
> allow you to formulate complex database constraints. What do you think is
> the problem then?
The problem is the percentage of "OI/BR/etc" in the RDBMS
and the percentage of "OI/BR/etc" remnant in the database applications
software environment (external to the RDBMS).
The problem as I see it is to leverage the percentage towards
the RDBMS as far as it possibly can be shoved, and there is
no reason not to aim for 100%.
>Are the constraint languages not powerful enough? Is it
> not efficient enough? Are the application designer not able to formulate
the
> database constraints in some formal logic? Or what?
When any contemporary database application software system
environment is loaded on top of an RDBMS, which has already
been loaded on top of an Operating System Software environment,
the total number of software systems environments is 3.
Using current program development tools and methodologies
application developers do not use the RDBMS exclusively
to code constraints and rules and often maintain these in
the Apps environment code.
Ideally, according to CJ Date, this situation should be resolvable
in the general sense such that all the logic contained in the APPS
environment (ie: OI/BR/etc) might be represented entirely within
the RDBMS. Somehow. But how is this to happen?
The problem is to migrate the code (usually written by application
vendors) representing the BR out of the application code and into
the RDBMS by some means. Does this make any sense to
you on the other side of the Pacific?
Jan.Hidders
mountain man <prfb...@magna.com.au> wrote:
>"Jan.Hidders" <hid...@hcoss.uia.ac.be> wrote in message
>news:3d6f5561$1...@news.uia.ac.be...
>> In article <j%yb9.18405$g9.5...@newsfeeds.bigpond.com>,
>> mountain man <prfb...@magna.com.au> wrote:
>> >
>> >2) I do not disagree. In fact it is my observation not from theory but
>> >from the production floor that has convinced me that "IN THEORY" there
>> >is nothing that cannot be expressed by the SQL at the RDBMS level.
>>
>> Do you mean that SQL can express all queries? That's not true, it may
>> often be in practice, but certainly not in theory; SQL is not
>> computationally complete.
>
>Can you provide me with an example of computation exemplifying the
>(theoretical) incompleteness of SQL?
Sure, try saying "there are no cycles in this binary relation" in one SQL
statement.
>> Or do you mean that with SQL you can express all database
>> constraints? That's also not true. Roughly speaking it only covers
>> first-order logic.
>
>While that may be, armed with SQL code, some R&D and an RDBMS scheduled
>task queue I can enforce all constraints without exception whatever they
>may be.
Interesting. How would you tackle the no-cycles constraint?
>> >In practice however, one invariably finds that the bulk of this
>> >OI/BR/etc is in fact resident in the applications code and NOT in the
>> >RDBMS. This was my point.
>>
>> Yes, but (1) you still haven't explained exactly what you mean with
>> "OI/BR/etc"
>
>Organisational Intelligence/Business Rules/etc The guts of the specific
>database application software code that the organisation or business
>happens to use.
Is that the best definition you can come up with? "The guts of the database
application"? That's not very informative I'm afraid and I happen to believe
you can do better than that.
>> Are the constraint languages not powerful enough? Is it not efficient
>> enough? Are the application designer not able to formulate the database
>> constraints in some formal logic? Or what?
>
>When any contemporary database application software system environment is
>loaded on top of an RDBMS, which has already been loaded on top of an
>Operating System Software environment, the total number of software systems
>environments is 3.
Sure. And how exactly does that answer my question?
>Using current program development tools and methodologies application
>developers do not use the RDBMS exclusively to code constraints and rules
>and often maintain these in the Apps environment code.
Yes, I already agreed with that. The question was if you had an explanarion
for why this is so.
>Ideally, according to CJ Date, this situation should be resolvable in the
>general sense such that all the logic contained in the APPS environment
>(ie: OI/BR/etc) might be represented entirely within the RDBMS. Somehow.
>But how is this to happen?
Again, wy doesn't it happen already? Before you can come up with a solution
you have to understand this first.
>The problem is to migrate the code (usually written by application vendors)
>representing the BR out of the application code and into the RDBMS by some
>means. Does this make any sense to you on the other side of the Pacific?
The Pacific? I think you are looking in the wrong direction. :-)
-- Jan Hidders
mountain man
> In article <kdPb9.19157$g9.5...@newsfeeds.bigpond.com>,
> mountain man <prfb...@magna.com.au> wrote:
> >"Jan.Hidders" <hid...@hcoss.uia.ac.be> wrote in message
> >news:3d6f5561$1...@news.uia.ac.be...
> >> In article <j%yb9.18405$g9.5...@newsfeeds.bigpond.com>,
> >> mountain man <prfb...@magna.com.au> wrote:
> >> >
> >> >2) I do not disagree. In fact it is my observation not from theory
but
> >> >from the production floor that has convinced me that "IN THEORY" there
> >> >is nothing that cannot be expressed by the SQL at the RDBMS level.
> >>
> >> Do you mean that SQL can express all queries? That's not true, it may
> >> often be in practice, but certainly not in theory; SQL is not
> >> computationally complete.
> >
> >Can you provide me with an example of computation exemplifying the
> >(theoretical) incompleteness of SQL?
>
> Sure, try saying "there are no cycles in this binary relation" in one SQL
> statement.
More information please on the quoted phrase.
And why the restriction about one SQL statement?
Incomplete means incomplete, does it not?.
Whether one or one thoud\sand and one?
> >> Or do you mean that with SQL you can express all database
> >> constraints? That's also not true. Roughly speaking it only covers
> >> first-order logic.
> >
> >While that may be, armed with SQL code, some R&D and an RDBMS scheduled
> >task queue I can enforce all constraints without exception whatever they
> >may be.
>
> Interesting. How would you tackle the no-cycles constraint?
Slowly, after you tell me exactly what it represents,
or possibly point me at some online documentation.
> >> >In practice however, one invariably finds that the bulk of this
> >> >OI/BR/etc is in fact resident in the applications code and NOT in the
> >> >RDBMS. This was my point.
> >>
> >> Yes, but (1) you still haven't explained exactly what you mean with
> >> "OI/BR/etc"
> >
> >Organisational Intelligence/Business Rules/etc The guts of the specific
> >database application software code that the organisation or business
> >happens to use.
>
> Is that the best definition you can come up with? "The guts of the
database
> application"? That's not very informative I'm afraid and I happen to
believe
> you can do better than that.
Let us assume that we have database application software product
running at some organisation using some RDBMS. It has client side
code and server side code (stored procs, etc)
If you were to examine the set of code from the Application
and add to it that set of server side code, supplied by the vendor
of the application, and add that to that the code which is supplied
by the RDBMS vendor, then in that (union) set of code is defined
whatever "capturable intelligence" that organisation has at
this time invested in computer systems.
It sounds as if there is some "theoretical definition"?
If you know it, consider letting me know.
>>> Are the constraint languages not powerful enough? Is it not efficient
> >> enough? Are the application designer not able to formulate the database
> >> constraints in some formal logic? Or what?
> >
> >When any contemporary database application software system environment is
> >loaded on top of an RDBMS, which has already been loaded on top of an
> >Operating System Software environment, the total number of software
systems
> >environments is 3.
>
> Sure. And how exactly does that answer my question?
Sequentially? Humor aside, I'd expect it to answer your question
on the basis that the environment is not a single RDBMS but rather
an interaction of 3 environment. Database theory seems to apply to the
internal realms of an RDBMS, although I could be off base.
> >Using current program development tools and methodologies application
> >developers do not use the RDBMS exclusively to code constraints and rules
> >and often maintain these in the Apps environment code.
>
> Yes, I already agreed with that. The question was if you had an
explanarion
> for why this is so.
Well, historical reason and reasons of profit, trade and commerce
often mixes that which is theorietical with that which is merely
commercial.
Take your standard Application Package vendor for the RDBMS
environment. Why should this vendor release control of the code
held in the program object code (applications software) layer, and
migrate it into the RDBMS? If there were some form of reasons
for doing this, or not doing this, what would these reasons be?
I have no explanation for the history of things. Do you?
> >Ideally, according to CJ Date, this situation should be resolvable in the
> >general sense such that all the logic contained in the APPS environment
> >(ie: OI/BR/etc) might be represented entirely within the RDBMS. Somehow.
> >But how is this to happen?
>
> Again, wy doesn't it happen already? Before you can come up with a
solution
> you have to understand this first.
Well this seems like a very important juncture.
If you could exlain yourself, I'd be grateful.
> >The problem is to migrate the code (usually written by application
vendors)
> >representing the BR out of the application code and into the RDBMS by
some
> >means. Does this make any sense to you on the other side of the Pacific?
>
> The Pacific? I think you are looking in the wrong direction. :-)
A philosophical conclude? :-)
East and west ... it all resolves to the sphere of earth,
covered by a layer of water and a layer of air that is
interpenetrated by the electromagnetic energy of
sunshine.
So where on the planetary surface are you?
Belgium? So we would need to skip over the
land mass between the Pacific and the Atlantic
as well....
Farmer Brown
Falls Creek,
Australia
'
Alfredo Novoa
Hi
On 29 Aug 2002 20:24:56 +0200, hid...@hcoss.uia.ac.be (Jan.Hidders)
wrote:
>So what
>kind of rules are you talking about then, and why are you so sure that they
>cannot be modelled as database constraints?
Maybe presentation rules
You can represent presentation rules in the form of relations, but I
don't think that presentation rules are database constraints.
Alfredo
Alfredo Novoa
<prfb...@magna.com.au> wrote:
>> >The logic is still "out there" in the Apps, is it not, at this moment in
>> >history?
Yes, it is in the apps, but it is not very good.
>application screens, or the database contraints, triggers, sprocs, etc. My
Triggers are only needed due the limitations of SQL declarative
expressivity.
>Some of the organisational intelligence OI has been migrated from the
>Apps environmet into the RDBMS --- no argument about that, but
>in most apps the bulk remains in the app_code, on the client.
Most apps are bad designed.
Regards
Alfredo
Joel Meulenberg
Hello Nathan.
> -User interface derivability. It is possible (though never accomplished
> until recently) to provide COMPLETE user interfaces that are derived
> from any perspective of the data. This is only possible due to things
> like updateable views (expressions) and declarative integrity rules.
> People (even relational folks) are very skeptical about this, but I have
> seen it work.
I have dabbled in this a little and have been dreaming about doing it
well for quite a while. Could you recommend any reference material,
prototypes, products, etc. that you believe are significant
contributions toward deriving user interfaces for relational
databases?
Any feedback is much appreciated.
+Joel Meulenberg
Paul Vernon
SQL
> statement.
I guess this is cheating as I don't believe recursive SQL is part of the
SQL standard, but in DB2 SQL:
create table t(a integer not null, b integer not null, primary key (a,b))
;
insert into t values (1,2), (2,3), (2,4), (3,5), (6,1)
;
CHECK(EXISTS(
with i (a, b, c) as (select a, b, 1 as c from t
union all
select s.a, s.b, i.c + 1
from i as i, t as s
where i.b = s.a
and c <= (select count(*) from t)
)
select * from i
where c = (select count(*) from t)
)
;
Ok, so I don't even have table constraints in DB2, but I could put the
above in some triggers. 0 rows = no cycles, >0 rows = cycles.
insert into t values (4,1);
--------------------------- Command entered ----------------------------
with i (a, b, c) as (select a, b, 1 as c from t
union all
select s.a, s.b, i.c + 1
from i as i, t as s
where i.b = s.a
and c <= (select count(*) from t)
)
select distinct * from i
where c = (select count(*) from t)
------------------------------------------------------------------------
A B C
----------- ----------- -----------
4 1 6
1 2 6
2 3 6
2 4 6
3 5 6
5 record(s) selected with 1 warning messages printed.
Jan.Hidders
Paul Vernon <paul....@ukk.ibmm.comm> wrote:
>> Sure, try saying "there are no cycles in this binary relation" in one SQL
>> statement.
>
>I guess this is cheating as I don't believe recursive SQL is part of the
>SQL standard, but in DB2 SQL:
Yes, that is cheating. :-) I never knew that DB2 could do that. Impressive.
Thanks for showing it.
Made me wonder for a moment if such a recursive SQL would be computationally
complete, but since you stay within PSPACE it is of course not.
-- Jan Hidders
Jan.Hidders
mountain man <prfb...@magna.com.au> wrote:
>"Jan.Hidders" <hid...@hcoss.uia.ac.be> wrote in message
>news:3d6fd524$1...@news.uia.ac.be...
>> In article <kdPb9.19157$g9.5...@newsfeeds.bigpond.com>,
>> mountain man <prfb...@magna.com.au> wrote:
>> >
>> >Can you provide me with an example of computation exemplifying the
>> >(theoretical) incompleteness of SQL?
>>
>> Sure, try saying "there are no cycles in this binary relation" in one SQL
>> statement.
>
>More information please on the quoted phrase.
You don't know what a cycle is? It's a list of edges in a graph (as in graph
theory) such that (1) every next edge starts from the node that the previous
edge ended in and (2) the last edge ends in the node that the first edge
started in. A binary relation is a relation with two columns, and therefore
can be thought of as representing a graph by interpreting every row as an
edge.
To make it even more concrete, we have a relation Parent_of(parent, child)
and you have to check if nobody is indirectly his or her own ancestor.
>And why the restriction about one SQL statement?
That's usually all you get in a CHECK statement. And besides, SQL embedded
in a computationally complete language would of course trivially be
computationally complete.
>Incomplete means incomplete, does it not?. Whether one or one thoud\sand
>and one?
What are you going to combine those thousand results with? With SQL? Then
you can also do it one SQL statement. With something else? Then we are no
longer talking about the completeness of SQL but of SQL plus some other
language.
>> Interesting. How would you tackle the no-cycles constraint?
>
>Slowly, after you tell me exactly what it represents, or possibly point me
>at some online documentation.
It should be clear by now.
>> Is that the best definition you can come up with? "The guts of the
>> database application"? That's not very informative I'm afraid and I
>> happen to believe you can do better than that.
>
>Let us assume that we have database application software product
>running at some organisation using some RDBMS. It has client side
>code and server side code (stored procs, etc)
>
>If you were to examine the set of code from the Application
>and add to it that set of server side code, supplied by the vendor
>of the application, and add that to that the code which is supplied
>by the RDBMS vendor, then in that (union) set of code is defined
>whatever "capturable intelligence" that organisation has at
>this time invested in computer systems.
>
>It sounds as if there is some "theoretical definition"?
>If you know it, consider letting me know.
If I had an idea of what you are talking about I wouldn't be asking for a
definition, would I now? :-) And I don't want a "theoretical definition", I
just want a *good* definition and good definitions are a very practical thing
to have if you want to a descent discussion about this stuff.
In the above you are talking about the code. But the code is not the logic,
although it does in some sense contain the logic. However, in most common
uses of terms like "application logic", "bussiness rules" et cetera the
whole point is that you make these rules explicit in some declarative form
and store them in a central place. Otherwise you are just moving your code
around and what would that solve?
Nathan Allan
> > -User interface derivability. It is possible (though never accomplished
> > until recently) to provide COMPLETE user interfaces that are derived
> > from any perspective of the data. This is only possible due to things
> > like updateable views (expressions) and declarative integrity rules.
> > People (even relational folks) are very skeptical about this, but I have
> > seen it work.
>
> I have dabbled in this a little and have been dreaming about doing it
> well for quite a while. Could you recommend any reference material,
> prototypes, products, etc. that you believe are significant
> contributions toward deriving user interfaces for relational
> databases?
First, let me "come clean" and state that if we are going to start
talking about products, I am partial to one in particular. My company
has a product that attempts to exploit the conceptual power of the
Relational Model to attain most of the benefits I enumerated. See
www.alphora.com. Now that such matters are out of the way, I will
briefly describe the way we architected UI derivation in Dataphor. I
will omit implementation stuff like caching and such:
The derivation process conceptually revolves around the recognition of
the data manipulation patterns that arise from all the possible
combinations of keys and foreign keys. We categorized and labeled
each of these patterns.
The UI derivation "engine" is a request/response system made up of
several, highly autonomous subsystems. A request is made with
essentially the following arguments:
-A "page type" (browse, edit, add, delete, view, etc.). Despite the
term "page" the resulting UI descriptions are not HTML, but are client
independent.
-A query. This can be any valid D4 (our Relational Algebra language
based on Tutorial D) expression. Note: SQL expressions can be
translated to D4 using our RDBMS.
-An "elaborate?" flag.
The processing flows through the conceptual subsystems like this:
Request->Describe->Elaborate->Structure->Layout->Produce->Emit->Respond
-Request - contains the above "arguments"
-Describe - this step gathers detailed information about the query.
The relational engine "walks" the expression and infers information
about stuff like:
-Types/Columns (most DBMSs do this)
-Keys
-Referential integrity constraints
-Metadata "tags" which arbitrarily "adorn" the schema
-Elaborate - this optional step takes the basic query and elaborates
on it by modifying the expression and even joining in other tables
that might be relevant.
-Structure - determines column groupings and orderings (based on
implicit logical groupings, priorities, and explicit groupings)
-Layout - uses Flow (Horizontal or Vertical) and Break metadata to
construct proper containership for the columns.
-Produce - emits a DOM (document object model) from the provided
layout based on column types and metadata.
-Emit - in our implementation this emits the DOM as an XML document
for consumption by a client. Note that the actual DATA is not in the
DOM or XML. The document includes the information necessary for the
client to interact with the DBMS to consume the data.
The mentioned metadata tags can be attached to all objects in the
schema. They allow the user to "steer" the derivation process. For
example, a "Title" metadata tag could be attached to a column. The
metadata tags are then sought by the derivation engine. As mentioned,
the describe step also infers these tags, so a column tag overrides a
domain tag of the same name, etc.
The describe step is the most intricate of the lot. It collaborates
with the entire relational query processor. It is also very important
because derivation could never be complete with merely a list of
column names and associated data types. Describe provides the ability
to extend the concept of logical data independence (derived relations
behave just like base relations) to the user interface. For example,
if table A references table X and I ask system to describe 'A join B',
I know that the resulting derived table references table X. I can use
this information to build a link to view table X or even embed table X
in the same UI (also determined by metadata tags).
Perhaps out of the domain of this discussion, but an important facet
to have in such a system is a customization engine. We implemented it
in a way that allows the developer to make arbitrary changes to the
resulting UI and save only those changes. When the customized UIs are
requested, they are automatically merged with their dynamic
"ancestors" so they are still as dynamic as possible.
I should point out something else that is important about all this UI
automation stuff: it depends heavily on a truly Relational DBMS. Take
updateable views for example. If all queries are not updateable, then
the developer must provide update logic... which means a step down in
automation.
[sigh] There is far more to this topic than I can possibly do justice
to in this posting. I'll just summarize by saying that:
a) I have never seen this done correctly/completely before.
b) I know it is possible because we built it and it works well.
--
Nathan Allan
mountain man
> In article <UhXb9.19391$g9.6...@newsfeeds.bigpond.com>,
> mountain man <prfb...@magna.com.au> wrote:
> >"Jan.Hidders" <hid...@hcoss.uia.ac.be> wrote in message
> >news:3d6fd524$1...@news.uia.ac.be...
> >> In article <kdPb9.19157$g9.5...@newsfeeds.bigpond.com>,
> >> mountain man <prfb...@magna.com.au> wrote:
> >> >
> >> >Can you provide me with an example of computation exemplifying the
> >> >(theoretical) incompleteness of SQL?
> >>
> >> Sure, try saying "there are no cycles in this binary relation" in one
SQL
> >> statement.
> >
> >More information please on the quoted phrase.
>
> You don't know what a cycle is? It's a list of edges in a graph (as in
graph
> theory) such that (1) every next edge starts from the node that the
previous
> edge ended in and (2) the last edge ends in the node that the first edge
> started in. A binary relation is a relation with two columns, and
therefore
> can be thought of as representing a graph by interpreting every row as an
> edge.
>
> To make it even more concrete, we have a relation Parent_of(parent, child)
> and you have to check if nobody is indirectly his or her own ancestor.
Thanks for the definition. Lets look at the final more concrete example
you provide immediately above. It is not a problem in recursion with an
unknown quantity of levels in recursion for the general solution?
> >And why the restriction about one SQL statement?
>
> That's usually all you get in a CHECK statement. And besides, SQL embedded
> in a computationally complete language would of course trivially be
> computationally complete.
Fair enough. But there is no reason that, if everything required
has not been packed into a CHECK statement, you cannot write
something yourself, as appropriate to the situation at hand.
> >Incomplete means incomplete, does it not?. Whether one or one thoud\sand
> >and one?
>
> What are you going to combine those thousand results with? With SQL? Then
> you can also do it one SQL statement. With something else? Then we are no
> longer talking about the completeness of SQL but of SQL plus some other
> language.
I am going to combine successive results of checking for problems
in the data over consecutive instances of the database table, for
example daily, after close of business ... overnight, on a scheduled
task queue.
The targets might be all the rows existent in this relationship
table. To seek out rows which represent an exception to one
form of data integrity check or another is an analysis task. In
the first approximations of this task we log rows which pass
the exception checks, and narrow down the compliment set.
Once every row has been accounted for, it is either OK, in
which case it is flagged as such, or it is not, in which case it
is examined by further analysis.
To couple such a successful task to a scheduler and perform routine
checks on the data (eg: daily) is known as an EMS (Exception
Management System)
> >> Interesting. How would you tackle the no-cycles constraint?
> >
> >Slowly, after you tell me exactly what it represents, or possibly point
me
> >at some online documentation.
>
> It should be clear by now.
>
These problems can be solved operationally by means of
iterated data integrity exception checks.
The entire cless of such problems are in fact resolved
operationally by the implementation of database specific
data integrity exception management systems. I have
been doing this for a living for some time in a number
of environments, and they work well.
See further:
a sample (Data Integrity) EMS FAQ at:
http://www.mountainman.com.au/software/Winluck/EMS_FAQ.html
It would solve plenty of problems if the code could
be removed from the (client or apps server) applications
environment, and migrated (100%) into the RDBMS
environment.
Explicity, I will repeat this:
If you classify the hardware and operating system
software environment (bundled with the network OS)
as Software Systems Environment 1, and the RDBMS
as Environment 2 we are left with the Applications
Software as the Software systems environment 3.
IT management is all about juggling these 3 environments
in a production sense. Env1 and Env2 are respectable
and well behaved, there being now in history, only a
few combinations dominant between them.
But Environment 3 is rampant with every imaginable
species of systems software, and in comparison to the
standard 2 juggling items above, is a large flaming
hephalump.
Moving all the code from the environment 3 into
the RDBMS will yield a lean and mean analytical
machine, and I see it as the way of the future, and
I see the (database) applications software environment
being cast aside from the future technology as a used
booster rocket might fall away from the main
mission module.
Two computer systems environments are far easier
and exceedingly far less expensive to manage than
three.
GoranG
wrote:
>In article <akvgqp$2mks$1...@sp15at20.hursley.ibm.com>,
I don't have the 99 standard, but as I understand recursion is covered
in it.
http://www-db.stanford.edu/~widom/cs145/notes/recursion.txt
also
http://groups.google.com/groups?q=recursion+SQL-99+celko&hl=hr&lr=&ie=UTF-8&oe=UTF-8&selm=3d3ca25d%240%2458372%2475868355%40news.frii.net&rnum=1
( GoranG79 AT hotmail.com )
Jan.Hidders
GoranG <n...@spam.net> wrote:
>
>I don't have the 99 standard, but as I understand recursion is covered
>in it.
That's also my understanding. But only positive recursion, so you stay
within PTIME. Does anybody know if DB2 also allows only positive recursion?
-- Jan Hidders
robert
>
<snip>
> Explicity, I will repeat this:
>
> If you classify the hardware and operating system
> software environment (bundled with the network OS)
> as Software Systems Environment 1, and the RDBMS
> as Environment 2 we are left with the Applications
> Software as the Software systems environment 3.
>
> IT management is all about juggling these 3 environments
> in a production sense. Env1 and Env2 are respectable
> and well behaved, there being now in history, only a
> few combinations dominant between them.
>
> But Environment 3 is rampant with every imaginable
> species of systems software, and in comparison to the
> standard 2 juggling items above, is a large flaming
> hephalump.
>
> Moving all the code from the environment 3 into
> the RDBMS will yield a lean and mean analytical
> machine, and I see it as the way of the future, and
> I see the (database) applications software environment
> being cast aside from the future technology as a used
> booster rocket might fall away from the main
> mission module.
>
> Two computer systems environments are far easier
> and exceedingly far less expensive to manage than
> three.
>
<snip>
while i agree completely with you and the others who argue to
put all the logic in the DB, where it belongs, there remains
the issue of n-tier design. to capsulize: 1-tier is host/terminal
and screen interaction can be (depending on the capabilities
of the host) tied to the DB (Progress DB/4GL is an example);
2-tier is "classic" client/server and n-tier is anything more
complicated. once you hit 2-tier level, the question of user
feedback raises its head. M$ "offers" bound data controls,
effectively re-making the system into 1-tier. users like this,
because they get immediate gratification; coders like this
because they don't have to duplicate edits.
the alternative is to build n-tier systems which emulate the good
olde days of OS/360 and the 3270: block mode interaction.
the html based web is effectively thus. i assert that this step
backwards is why intranet applications based on n-tier haven't
been stellar. where's the additional bang for the buck?? from a
user's standpoint, there usually isn't. the only situations where
it might get done is in MF land where block mode has been the norm,
so there was never a character mode terminal application. they don't
miss what they didn't have.
so: unless one has a 1-tier application, those idiot web developers,
and dinosaur MF coders (of whom many still think VSAM is the bee's
knees), lobby for putting data management in code where it can be seen
by each tier. the suits who make the decisions don't know any better
(they generally came up through sales/marketing and think powerpoint
is analysis), so they go with the majority. their argument is that
the client needs the edits, the mid-tier(s) need the edits; why hide
them all the way at the backend where no one can see them?????
the RM folks lose.
it's too bad, but there it is.
robert
Tibor Karaszi
news:da3c2186.0209...@posting.google.com...
> while i agree completely with you and the others who argue to
> put all the logic in the DB, where it belongs, there remains
> the issue of n-tier design.
Perhaps I'm missing the point, but I don't think that the discussion is
about having the actual code executing in the DBMS. If the logic is well
enough described, it can be used by dev tools that generates a large amount
of the "outer" tiers code.
Or, the outer tiers could even read this information at run time hence
adjust to changing rules. A simple example is a client app reading a check
constraint definition through the INFORMATION_SCHEMA views and apply an
appropriate input mask which only allow data format as described in the
constraint (or similar for a default value).
--
Tibor Karaszi
Costin Cozianu
I think you generaly made all the points very well, but at one specific
point you're
enthusiasm made you exaggerate a little bit :)
> -Well defined and formal "model" for type inheritance. This actually
> isn't a Relational thing because type is an orthoginal (apples and
> oranges) concept to the RM. I still think it is worth making explicit
> because it should be clear that OO doesn't have an exclusive on the
> concept. Well defined inheritance is beneficial because the SYSTEM can
> help us enforce domain constraints and such. Not to mention providing
> the app developer with the ability to rely and deterministic and well
> defined behavior from the system.
This is simply not true. There is no well defined and formal model of "type
inheritance" as
part of the relational model. As you noted, relational model is kind of
orthogonal to types.
Of course you're probably referring to Date&Darwen's proposal in the Third
Manifesto,
and is probably a value judgement to say whether that is formal and well
defined, but within
a safe margin we can say it doesn't meet the criteria.
But first of all you're using bad terminology, it's not "type inheritance",
it is _subtyping_.
Subtyping and inheritance are look-alikes but they should be treated
distinctly.
Type inheritance is the term Date&Darwen came up with after collecting
almost all the bad
references from the OO literature (the UML and OMG ODMG kind of stuff) and
almost no good one
(there's only one good reference to Cardelli and Wegner, which is too old
and superceded
by later work).
To get an idea what is needed to present a formal and well defined type
system proposal,
and all the issues a type system needs to address you might want to access
some very
good introductory book available on the web:
Andrea Asperti and Giuseppe Longo: Categories, Types and Structures
Simon Thompson: Type Theory and Functional Programming
Robert Constable: Naive Type Theory
Of course there's _a lot_ more, but these are amazingly good books on the
subject, and they
build from scratch all the way up (most other literature on type theory
requires that you
have a lot of background in this very specialized area).
Costin Cozianu
Jan.Hidders
>>
>> To make it even more concrete, we have a relation Parent_of(parent,
>> child) and you have to check if nobody is indirectly his or her own
>> ancestor.
>
>Thanks for the definition. Lets look at the final more concrete example
>you provide immediately above. It is not a problem in recursion with an
>unknown quantity of levels in recursion for the general solution?
Precisely.
>> >And why the restriction about one SQL statement?
>>
>> That's usually all you get in a CHECK statement. And besides, SQL embedded
>> in a computationally complete language would of course trivially be
>> computationally complete.
>
>Fair enough. But there is no reason that, if everything required
>has not been packed into a CHECK statement, you cannot write
>something yourself, as appropriate to the situation at hand.
Sure. But that is a rather uninteresing observation. Anything can be
programmed if you are a good enough programmer and have enough time and
resources. But the question is also how efficient is that going to be if you
are not an excellent programmer and if the table is rather big. If the
database could do it for you, that would probably be a big gain.
>> >Incomplete means incomplete, does it not?. Whether one or one thoud\sand
>> >and one?
>>
>> What are you going to combine those thousand results with? With SQL? Then
>> you can also do it one SQL statement. With something else? Then we are no
>> longer talking about the completeness of SQL but of SQL plus some other
>> language.
>
>I am going to combine successive results of checking for problems
>in the data over consecutive instances of the database table, for
>example daily, after close of business ... overnight, on a scheduled
>task queue.
That may be unacceptable because the transactions would not be able to
commit until you have made that check. The whole point of having integrity
constraints is that they should always be maintained, not just at midnight.
>> In the above you are talking about the code. But the code is not the
>> logic, although it does in some sense contain the logic. However, in most
>> common uses of terms like "application logic", "bussiness rules" et
>> cetera the whole point is that you make these rules explicit in some
>> declarative form and store them in a central place. Otherwise you are
>> just moving your code around and what would that solve?
>
>It would solve plenty of problems if the code could
>be removed from the (client or apps server) applications
>environment, and migrated (100%) into the RDBMS
>environment.
Sure. It sometimes solves problems and sometimes it creates new ones. What
you are suggesting is essentially going back to mainframes.
-- Jan Hidders
Paul Vernon
>about having the actual code executing in the DBMS. If the logic is well
>enough described
i.e. it is declarative
>it can be used by dev tools that generates a large amount
>of the "outer" tiers code.
but as soon as you have generation of code, you loose the ability to
modify the database schema, without fully generating the code again. Also
code, once generated, almost always gets manually modified so you loose
the linkage.
>Or, the outer tiers could even read this information at run time hence
>adjust to changing rules. A simple example is a client app reading a
check
>constraint definition through the INFORMATION_SCHEMA views and apply an
>appropriate input mask which only allow data format as described in the
>constraint (or similar for a default value).
Yes, but that client app will still have a hard-coded column names, so
when the DB schema changes, someone will need to manually alter the client
app.
The nirvana is to be able to alter and extend a database schema hundreds
of times a day without any(many) of the existing 'apps' breaking. This is
only possible where the 'apps' are generated from and linked directly to
the database schema.
robert
well, this is what mountain man said:
"Moving all the code from the environment 3 into
the RDBMS will yield a lean and mean analytical
machine,..."
check constraints, triggers, and stored procedures (although in DB2
they are neither) do execute "in" the DBMS. and this is a Good Thing.
but it raises the question of data validation. anyone who has had
to put javascript code in a web page knows this. the alternative is
not validate at the client, and revert to "block mode input". for
most users, those that have been using character, edit on the keystroke,
unix applications (mostly); this is a loss of functionality. so, they
get duplicate edits. they don't get, until submission, what used to be
called 'file dependent edits'. those character mode applications could
do that, too. thus the argument by webbies to put validation
on the "mid-tier" where it is visible to all. until there is an easy,
and portable, way to enforce the RM in n-tier we lose the argument.
while i've not researched it a lot, transaction processing monitors (CICS
is the grand daddy of them all) may provide this functionality by acting
as the "mid-tier".
i eagerly await a solution.
robert
mountain man
> In article <g4id9.24099$g9.6...@newsfeeds.bigpond.com>,
> mountain man <prfb...@magna.com.au> wrote:
> >"Jan.Hidders" <hid...@hcoss.uia.ac.be> wrote in message
> >news:3d73ca4d$1...@news.uia.ac.be...
> >>
> >> To make it even more concrete, we have a relation Parent_of(parent,
> >> child) and you have to check if nobody is indirectly his or her own
> >> ancestor.
> >
> >Thanks for the definition. Lets look at the final more concrete example
> >you provide immediately above. It is not a problem in recursion with an
> >unknown quantity of levels in recursion for the general solution?
>
> Precisely.
>
> >> >And why the restriction about one SQL statement?
> >>
> >> That's usually all you get in a CHECK statement. And besides, SQL
embedded
> >> in a computationally complete language would of course trivially be
> >> computationally complete.
> >
> >Fair enough. But there is no reason that, if everything required
> >has not been packed into a CHECK statement, you cannot write
> >something yourself, as appropriate to the situation at hand.
>
> Sure. But that is a rather uninteresing observation. Anything can be
> programmed if you are a good enough programmer and have enough time and
> resources.
Which includes education, research and development.
>But the question is also how efficient is that going to be if you
> are not an excellent programmer and if the table is rather big. If the
> database could do it for you, that would probably be a big gain.
Database integrity has often been focused on specific
problems, such as your above example of relationships
and recursions. But in practice, database integrity
issues extend to literally dozens of other issues that
are entirely outside the realm of database constraints.
It is always up to someone within the organisation to
call the shots over the issue of database integrity. If
such issues are recognised as important, and they should
be, then a certain very small percentage of funds available
to the IT budget (because it is only a small task in the
scope of evertyhing happening in the IT environment)
and other resources should be applied .
Data integrity might start with db constraints, but its
issues extend well into the user database values. Also,
changing environmental factors (eg: new legislation) are
such that they can be seen to create integity problems
at specific conversion points, if not approached correctly.
> >> >Incomplete means incomplete, does it not?. Whether one or one
thoud\sand
> >> >and one?
> >>
> >> What are you going to combine those thousand results with? With SQL?
Then
> >> you can also do it one SQL statement. With something else? Then we are
no
> >> longer talking about the completeness of SQL but of SQL plus some other
> >> language.
> >
> >I am going to combine successive results of checking for problems
> >in the data over consecutive instances of the database table, for
> >example daily, after close of business ... overnight, on a scheduled
> >task queue.
>
> That may be unacceptable because the transactions would not be able to
> commit until you have made that check. The whole point of having integrity
> constraints is that they should always be maintained, not just at
midnight.
You may have missed my point. If you make the decision to enforce
integrity constraints and then find you have a queue of uncommitted
transactions due to bad integrity at the end of the day, then that is
your problem to solve .... operationally.
I would solve this problem by removing the constraints, or relaxing
them until there were zero uncommitting problems. If you read the
above carefully, my approach is to check the integrity of the entire
environment on a routine basis, and refer up the failed integrity
exception checks to be reviewed in an intelligent manner by the
organisation.
There are always the grey areas of database integrity, and always
will be due to the specific nature of the RDBMS, the APP(s) and
the organisational users of both these software systems environments.
But perhaps the mechanism to most spectacularly reveal the grey area
is simply change itself ---- day by day. If you have not before tried
to track change this might not be immediately apparent, but let me
assure you, from a production operational perspective, you will see
the grey area of data integrity.
> >> In the above you are talking about the code. But the code is not the
> >> logic, although it does in some sense contain the logic. However, in
most
> >> common uses of terms like "application logic", "bussiness rules" et
> >> cetera the whole point is that you make these rules explicit in some
> >> declarative form and store them in a central place. Otherwise you are
> >> just moving your code around and what would that solve?
> >
> >It would solve plenty of problems if the code could
> >be removed from the (client or apps server) applications
> >environment, and migrated (100%) into the RDBMS
> >environment.
>
> Sure. It sometimes solves problems and sometimes it creates new ones. What
> you are suggesting is essentially going back to mainframes.
I am suggesting that there is a pathway whereby the (db) applications
environment is internalised within the RDBMS environment and
essentially disappears from technology as a booster stage rocket
would disappear from an outbound assembly.
This will create new problems, but the problems will be within the
RDBMS environment in which there is invested globally a great deal
of technical expertise. These will be far easier to solve that problems
which now exist in the db applications environment and need to be
addressed in a complete profusion of rampant evolving applications
in every organsiation of the planet ---- separate, disparate, like the
profuse foilage of a large shrub that needs trimming, pruning.
It may resemble a mainframe environment due its architecture
and the investment already placed by organisations in the RDBMS
and supporting HW, OS, NWOS bundle, but that is where the
similarity ends.
The modern RDBMS environment has far greater data processing
resources than the mainframe environment from which it evolved.
Best wishes,
Farmer Brown
Falls Creek,
Australia
http://www.mountainman.com.au/software/
mountain man
I have been trying to contact IBM Australia over the
last few weeks to arrange a meeting so that I can look
under the bonnet of DB2 and see what needs to be done
to get a product in that environment.
An Oracle product is further progressed than DB2, and
trivial from any MS platform, but is also separate.
I have a working solution for SQLServer here:
http://www.mountainman.com.au/software/LittleSteps
If anyone here is from IBM and knows someone in IBM
Australia who might be interested in contacting me, then
please --- many thanks if you can.
Best wishes for now,
Nathan Allan
> but as soon as you have generation of code, you loose the ability to
> modify the database schema, without fully generating the code again. Also
> code, once generated, almost always gets manually modified so you loose
> the linkage.
I agree that code generation introduces many problems. Nonetheless,
it is highly likely that in an enterprise software application, schema
duplication will be necessary. The idea is to architect the
application framework to the duplication is managed by the system and
is therefore transparent to the developer.
> >Or, the outer tiers could even read this information at run time ...
>
> Yes, but that client app will still have a hard-coded column names, so
> when the DB schema changes, someone will need to manually alter the client
> app.
Again, if the architecture is such that the client manages the
duplication, the duplication is automated. We did just this with our
Frontend tools in Dataphor. The results are that you can make changes
to the schema and see them automatically reflected in the UI.
> The nirvana is to be able to alter and extend a database schema hundreds
> of times a day without any(many) of the existing 'apps' breaking. This is
> only possible where the 'apps' are generated from and linked directly to
> the database schema.
Exactly... that's what we do in Dataphor.
--
Nathan Allan
Nathan Allan
> check constraints, triggers, and stored procedures (although in DB2
> they are neither) do execute "in" the DBMS. and this is a Good Thing.
> but it raises the question of data validation. anyone who has had
> to put javascript code in a web page knows this. the alternative is
> not validate at the client, and revert to "block mode input". for
> most users, those that have been using character, edit on the keystroke,
> unix applications (mostly); this is a loss of functionality. so, they
> get duplicate edits. they don't get, until submission, what used to be
> called 'file dependent edits'. those character mode applications could
> do that, too. thus the argument by webbies to put validation
> on the "mid-tier" where it is visible to all. until there is an easy,
> and portable, way to enforce the RM in n-tier we lose the argument.
"Validation" as you are calling it is just the pre-checking of
integrity constraints (for the purpose of implementation in another
layer of the application). There is at least one solution to this
problem, though I think it is more of an implementation issue, and is
only indirectly related to the Relational Model. The following is the
solution employed in our Dataphor product:
1. Allow all integrity constraints (including arbitrary database wide
ones) to be expressed declaratively.
2. Provide a "proposal" (or "what if") interface as part of the Call
Level Interface (CLI). Given a prepared execution plan for any
expression, allow the external level system (client) to ask data
validation questions about any rows of that data type. In other
words, without posting, allow the client to ask what would happen if
it did post. This is really just a foundation for the next item.
3. For a given execution plan being consumed by the client, have the
DBMS detect what relevant constraints can be evaluated in "isolation"
on the client. Allow the client to download the code for these
constraints and enforce them there automatically.
The result is that the application developer can put all integrity
constraints into the schema. The client will automatically enforce
the schema constraints and will even do so without consuming server
and network resources.
This solution is indirectly associated with the Relational Model
because the RM gives us the necessary foundation to:
a) express EVERY integrity constraint (declaratively).
b) provides a formal enough foundation to allow us to determine which
constraints, in an arbitrary set, can be evaluated in isolation.
Note: in Dataphor, we extend the "proposal" interface to also include
"default" and "change" queries. This allows the client to retrieve
default values such as surrogate key generators (inferred from any
expression) and can perform calculations and lookups for columns.
> while i've not researched it a lot, transaction processing monitors (CICS
> is the grand daddy of them all) may provide this functionality by acting
> as the "mid-tier".
A proper DBMS with full integrity enforcement is it's own best
"transaction processing monitor" is it not?
> i eagerly await a solution.
May I be so presumptuous as to assert that your wait is over. ;-)
--
Nathan Allan
Nathan Allan
> Database integrity has often been focused on specific
> problems, such as your above example of relationships
> and recursions. But in practice, database integrity
> issues extend to literally dozens of other issues that
> are entirely outside the realm of database constraints.
Really?! Like what? If you are talking about your exception
management system, may I suggest that a DBMS with proper integrity
enforcement capabilities is something like a real-time EMS. As Jan
points out, I don't want to know at midnight that my data is "bad". I
don't want it become bad in the first place.
> It is always up to someone within the organisation to
> call the shots over the issue of database integrity. If
> such issues are recognised as important, and they should
> be, then a certain very small percentage of funds available
> to the IT budget (because it is only a small task in the
> scope of evertyhing happening in the IT environment)
> and other resources should be applied .
If you are suggesting that the responsibility of data integrity should
be placed on a database administrator, I would say you are proscribing
a big step backwards.
> Data integrity might start with db constraints, but its
> issues extend well into the user database values. Also,
> changing environmental factors (eg: new legislation) are
> such that they can be seen to create integity problems
> at specific conversion points, if not approached correctly.
None of which implies that DBMS enforced constraints won't adequately
and completely suffice (assuming that the DBMS in question has FULL
integrity enforcement capabilities). The way I read what you are
saying is that "constraints must be allowed to change," which I
completely agree with.
> You may have missed my point. If you make the decision to enforce
> integrity constraints and then find you have a queue of uncommitted
> transactions due to bad integrity at the end of the day, then that is
> your problem to solve .... operationally.
The point is that you usually don't wait until the end of the day. If
someone puts in invalid data, they should be told immediately (or at
least as immediately as is practical). If an automated system is
putting in data, then there should be some methodology for correcting
or reporting the problem. In the latter case, an EMS type solution
comes into play, but at the application level. The "bad" data should
not be allowed in the "main" database... it should be in some type of
queue. In other words, EMS type of integrity resolution can and
should be done at the application level, not the system level.
> I would solve this problem by removing the constraints, or relaxing
> them until there were zero uncommitting problems. If you read the
> above carefully, my approach is to check the integrity of the entire
> environment on a routine basis, and refer up the failed integrity
> exception checks to be reviewed in an intelligent manner by the
> organisation.
As stated above, this is an application design decision and not really
a DBMS design issue. Regardless, the design I would highly recommend
would be to very definitely NOT relax the constraints... how can you
rely on data in such a database? I would keep the questionable data
in a separate place until it is verified appropriately.
> There are always the grey areas of database integrity, and always
> will be due to the specific nature of the RDBMS, the APP(s) and
> the organisational users of both these software systems environments.
This may be the case, but it should not be so. If all integrity can
be described in terms of logical predicates, than the only "gray
areas" should be application design decisions, not system level
logical considerations.
> But perhaps the mechanism to most spectacularly reveal the grey area
> is simply change itself ---- day by day. If you have not before tried
> to track change this might not be immediately apparent, but let me
> assure you, from a production operational perspective, you will see
> the grey area of data integrity.
Gray is not a product of change, it is a product of poor
understanding.
> I am suggesting that there is a pathway whereby the (db) applications
> environment is internalised within the RDBMS environment and
> essentially disappears from technology as a booster stage rocket
> would disappear from an outbound assembly.
You still haven't defined this concept of being "internal". As I
stated in another post... the application in all of it's layers
constitutes one system.
> This will create new problems, but the problems will be within the
> RDBMS environment in which there is invested globally a great deal
> of technical expertise. These will be far easier to solve that problems
> which now exist in the db applications environment and need to be
> addressed in a complete profusion of rampant evolving applications
> in every organsiation of the planet ---- separate, disparate, like the
> profuse foilage of a large shrub that needs trimming, pruning.
What you are aiming for is simplicity. We all agree that simpler is
better (so long as nothing is lost). Though at first glance it seems
appealing, you should give the whole notion of "inside the DBMS" more
thought because I don't think it is the grail you seek.
--
Nathan Allan
Nathan Allan
> Maybe presentation rules
Many if not most of the "presentation rules" can be derived from a
relational schema. As for other presentation rules, unless someone
can come up with a formal basis for describing them, it would be
difficult at best to fully automate them.
--
Nathan Allan
Lennart Jonsson
Hi Jan. Could you elaborate a bit on computional completenes, positive
recursion and PTIME/PSPACE? It was quite a few years since I took a
course regarding such things, and I couldnt find the definitions in
the textbooks which I suspect would be closest to the subject
(Languages and Machines by Thomas A. Sudkamp and Introduction to
Mathematical Logic by Elliot Medelson). Perhaps the definitions are
known by other names in any of these books? Neither did I dig up
something interesting using google, so if you could provide some info
regarding these subjects it would be great.
Regarding DB2 the documentaion doesnt say much about what kind of
recursion it supports (as far as I can see, but that might just be me
:-). Fumbling around with recursive sql I did some tests on different
recursive functions. By cheating a bit (there seems to be a lot of
cheaters using DB2 :-) and calculate the move_nr this seems to solve
the "Towers of Hanoi" (I have not convinced my self yet, as mentioned
I'm fumbling around here):
db2 -t "CREATE FUNCTION TOWERS (n int, from int, to int, help int)
RETURNS table(from int, to int, move_nr int)
LANGUAGE SQL CONTAINS SQL
NO EXTERNAL ACTION DETERMINISTIC
RETURN
WITH trace (x, f, t, h, c) AS (
values (n, from, to, help, power(2, n-1))
union all
select x-1, f, h, t, c + power(2, x-2) from trace where x>1
union all
select x-1, h, t, f, c - power(2, x-2) from trace where x>1
) SELECT f, t, c FROM trace";
db2 "select from, to from table(towers(3,1,2,3)) x order by move_nr"
FROM TO
----------- -----------
SQL0347W The recursive common table expression "LELLE.TRACE" may
contain an
infinite loop. SQLSTATE=01605
1 2
3 2
3 1
1 2
2 3
1 3
1 2
7 record(s) selected with 1 warning messages printed.
Ackerman next, but I have a feeling that this will mean trouble ;-)
/Lennart
Jan.Hidders
Lennart Jonsson <len...@kommunicera.umea.se> wrote:
>
>Hi Jan. Could you elaborate a bit on computional completenes, positive
>recursion and PTIME/PSPACE? It was quite a few years since I took a course
>which I suspect would be closest to the subject (Languages and Machines by
>Thomas A. Sudkamp and Introduction to Mathematical Logic by Elliot
>Medelson).
Well, these terms are slightly different from their standard meaning in
computability theory, so the standard definition will not be precisely
correct anyway. I'll give a short explanation here, if you want to know more
I can advise you the excellent book:
Abiteboul, S.; Hull, R. and Vianu, V. Foundations of Databases.
Addison-Wesley, 1995.
The problem with standard computational completeness is that it talks about
functions from (tuples of) natural numbers to natural numbers, or from
strings to strings. But a query language expresses functions from a tuple of
relations to a relation. This is solved by mapping these to Turing tapes,
i.e., strings such that every computable function over strings also defines
a function over relations.
However, in database theory we often want to concentrate us on the
manipulation of tables and ignore the computations with the values in the
columns. Therefore (and some other reasons wich take longer to explain) it
is often assumed that the only allowed operation on these values is the
equality operator between two columns. In that case the computable functions
are limited to the socalled generic functions which might informally be
described as the functions that can only compare the column values. To give
an example, the function that given the input unary relation { < a >, < b >
} results in the relation { < a > } is not a generic function. More formally
a function f is said to be generic if it holds for every permutation p of
column values that f = p^-1 o f o p. This says that the function stays the
same if I first replace the column values with other values (say, a and b
are replaced with c and d) and then perform the function and end with
reversing the replacement (c and d are again replaced with a and b). It's
not easy to explain the intuition behind this in a few words, so you will
just have to take my word for it now. :-)
The botom-line is that we only consider generic functions that are
computable. A query language is then called computationally complete if it
can express all these functions.
The terms PTIME and PSPACE are usually defined only for decision problems
but they are also sometimes used in database theory to describe the set of
functions that can be computed in polynomial time and polynomial space
respectively.
Finally, the term positive recursion refers to a restriction of how
recursion may be used. Suppose we define a relation R recursively in terms
of itself the depending on the place where R is used in its own definition
it will contribute positively or negatively to the result. For example, in:
R := R UNION SELECT R.begin, S.end FROM R, S WHERE R.end = S.begin;
you can see that R contributes positively to itself (if R grows the result
of the recomputation of the right-hand side also grows). This can be
enforced by allowing R only to occur in 'positive' places in the query. For
example in
R := (SELECT .. FROM .. WHERE ..) MINUS (SELECT .. FROM .. WHERE ..);
the first FROM clause would be a positive place and the second FROM clause
would be a negative place. Another example would be:
R := SELECT .. FROM .. WHERE NOT EXISTS ( SELECT .. FROM .. WHERE ..)
where the first FROM clause would be a postive place and the second nested
FROM clause would be a negative place. If there had not been a NOT then the
second FROM clause would also have been a positive place. The story is
similar if you use NOT IN and IN.
I think you can guess from this how an inductive definition for positive
FROM clauses would look.
If you limit the usage of R to the positive FROM clauses then you have the
advantage that the recursive equations always have a unique minimal solution
and this solution can be computed in polynomial time (if you ignore the
computations on column values).
>Regarding DB2 the documentaion doesnt say much about what kind of
>recursion it supports (as far as I can see, but that might just be me
>:-). Fumbling around with recursive sql I did some tests on different
>recursive functions. By cheating a bit (there seems to be a lot of
>cheaters using DB2 :-) and calculate the move_nr this seems to solve
>the "Towers of Hanoi" (I have not convinced my self yet, as mentioned
>I'm fumbling around here):
Yes, if you start doing arithmetic and have general recursion then you can
define any computable function on numbers, even the Ackerman function :-).
But note that you might then still be computationally incomplete.
-- Jan Hidders
Lennart Jonsson
Excellent explanation. Thanx a lot
/Lennart
Lauri Pietarinen
an interesting result in this paper:
Logics with Aggregate Operators:
http://citeseer.nj.nec.com/272190.html
It basically says (so I have been told!) that
closure in relations is an NP-complete
problem if no ordering is available.
(please correct me, if this is not
the right interpretation)
Lauri Pietarinen
Mikito Harakiri
I wonder if logical approach is relevant for aggregates at all.
Aggregation naturally leads to bags (with loss of many nice algebraic
properties). But even more natural, IMHO, it fits into theory of
distributions. For example, a bag
X Y
- -
1 1
1 1
1 2
becomes
2*delta(X-1)*delta(Y-1) + delta(X-1)*delta(Y-2)
where delta is Dirac delta distribution (see wolfram mathworld).
In that interpretation join is just a convolution.
I was unable to find any literature on the subject, however. Any
pointers?
mountain man
news:fedf3d42.02090...@posting.google.com...
> "mountain man" <prfb...@magna.com.au> wrote in message
news:<iLwd9.24826$g9.7...@newsfeeds.bigpond.com>...
>
> > Database integrity has often been focused on specific
> > problems, such as your above example of relationships
> > and recursions. But in practice, database integrity
> > issues extend to literally dozens of other issues that
> > are entirely outside the realm of database constraints.
>
> Really?! Like what?
eg: Retrospective contraints. eg: Executive decision of
DDMMCCYY to the effect that Field "A" can no
longer be allowed to be null, and we have 330 records
out of 3 million rows in which A is null. We can script
a new and unique value (eg: A = Change of ddmmccyy)
into these 330 records, for an interim period of time,
and have these items reviewed and altered to a a more
appropriate value. The queue of rows where A =
Change of ddmmyycc will then deplete to zero at
which time the operational integrity is restored to the
base it had prior to the change.
>If you are talking about your exception
> management system, may I suggest that a DBMS with proper integrity
> enforcement capabilities is something like a real-time EMS.
In an ideal world when the integrity constraints can be implemented
to the database at that precise and appropriate time from which
such constraints might apply, your suggestion is eminently
considerable.
However in the real world the implementation of change
can never be so implemented in ALL circumstances. There
are ALWAYS exceptions to the rule and at some stage
these are best handled by the proactive EMS operating
outside the constraints, and from first principles.
> As Jan
> points out, I don't want to know at midnight that my data is "bad". I
> don't want it become bad in the first place.
The data has just become "bad" due to an executive decision
just after lunch yesterday afternoon. An EMS will tell you
there are 330 rows where A is null. Once someone has
filled these holes, the constraint can be globally applied to
the field "A". An EMS will then continue to tell you how
many of these 330 rows need to be reverted to one of the
original available values of field "A", until all is done.
> > It is always up to someone within the organisation to
> > call the shots over the issue of database integrity. If
> > such issues are recognised as important, and they should
> > be, then a certain very small percentage of funds available
> > to the IT budget (because it is only a small task in the
> > scope of evertyhing happening in the IT environment)
> > and other resources should be applied .
>
> If you are suggesting that the responsibility of data integrity should
> be placed on a database administrator, I would say you are proscribing
> a big step backwards.
I am suggesting that database integrity issues are addressable
by automated exception management systems which can be
constructed out of the native RDBMS utilities.
The directive as to which specific integrity exception checks
should be engineered first and second, etc would obviously
come from some proactive management concern. But there
is no reason that the DBA (or delegated party) cannot
effect them.
> > There are always the grey areas of database integrity, and always
> > will be due to the specific nature of the RDBMS, the APP(s) and
> > the organisational users of both these software systems environments.
>
> This may be the case, but it should not be so. If all integrity can
> be described in terms of logical predicates, than the only "gray
> areas" should be application design decisions, not system level
> logical considerations.
I have seen this term around a bit, but have not completely
understood what it means ... "logical predicates". Can indeed
all integrity be described in such terms? Can the example
earlier provided be described in such terms?
> > This will create new problems, but the problems will be within the
> > RDBMS environment in which there is invested globally a great deal
> > of technical expertise. These will be far easier to solve that problems
> > which now exist in the db applications environment and need to be
> > addressed in a complete profusion of rampant evolving applications
> > in every organsiation of the planet ---- separate, disparate, like the
> > profuse foilage of a large shrub that needs trimming, pruning.
>
> What you are aiming for is simplicity. We all agree that simpler is
> better (so long as nothing is lost). Though at first glance it seems
> appealing, you should give the whole notion of "inside the DBMS" more
> thought because I don't think it is the grail you seek.
Thanks for your comments, Nathan.
I appreciate your perspective and observations.
Lauri Pietarinen
> > > Database integrity has often been focused on specific
> > > problems, such as your above example of relationships
> > > and recursions. But in practice, database integrity
> > > issues extend to literally dozens of other issues that
> > > are entirely outside the realm of database constraints.
> >
> > Really?! Like what?
>
> eg: Retrospective contraints. eg: Executive decision of
> DDMMCCYY to the effect that Field "A" can no
> longer be allowed to be null, and we have 330 records
> out of 3 million rows in which A is null. We can script
> a new and unique value (eg: A = Change of ddmmccyy)
> into these 330 records, for an interim period of time,
> and have these items reviewed and altered to a a more
> appropriate value. The queue of rows where A =
> Change of ddmmyycc will then deplete to zero at
> which time the operational integrity is restored to the
> base it had prior to the change.
>
These are called transition constraints. A simple
example is that you have to get married before you
can divorce ;-) In your case you would specify that
A cannot be modified from 'not null' to 'null'.
A query would give a report on how many violating
rows still exist.
regards,
Lauri
mountain man
news:e9d83568.02090...@posting.google.com...
Thanks for the formalised definition. While we are being formal,
what is the "logical predicate" of a such a transitional contraint?
Best wishes,
Farmer Brown
Falls Creek,
OZ
Lauri Pietarinen
> > ´
> > These are called transition constraints. A simple
> > example is that you have to get married before you
> > can divorce ;-) In your case you would specify that
> > A cannot be modified from 'not null' to 'null'.
>
>
> what is the "logical predicate" of a such a transitional contraint?
>
I guess something like
NOT (old.A IS NOT NULL AND new.A IS NULL)
where 'old' and 'new' with the obvious meanings.
This is of course something that is possible already
today using triggers.
regards,
Lauri Pietarinen
--CELKO--
of the SQL standard, but in DB2 SQL: <<
The WITH clause is part of SQL-99 and not SQL-92. But I wisht aht
INCITS H2 had never approved it. It is not needed. I hafe a book on
Trees & Heirarchies in SQL in the works in which I show the reader
several ways to model tree structures and include constraints to avoid
cycles with pure DDL.
CREATE TABLE OrgChart
(emp CHAR(10) NOT NULL PRIMARY KEY,
boss CHAR(10) REFERENCES OrgChart(emp),
-- no self-refs
CHECK (boss <> emp),
-- only one NULL boss
CHECK ((SELECT COUNT(*) FROM OrgChart) - 1
= (SELECT COUNT(*)
FROM (SELECT emp FROM OrgChart
UNION
SELECT boss FROM OrgChart
WHERE person IS NOT NULL))
-- nodes & edges tally right
Example:
OrgChart
emp boss
==========
1 null
2 3
3 4
4 2
Now let's run thru the constraints:
1) CHECK (boss <> emp) = TRUE
2) CHECK ((SELECT COUNT(emp) FROM AdjList) -1
= (SELECT COUNT(boss) FROM AdjList),
becomes CHECK ((4 -1) = 3) = TRUE
3) However:
CHECK ((SELECT COUNT(*) FROM OrgChart) - 1
= (SELECT COUNT(*)
FROM (SELECT emp FROM OrgChart
UNION
SELECT boss FROM OrgChart))));
becomes, using a SQL-92 table constructor:
CHECK ((4 - 1)
= (SELECT COUNT(*)
FROM (VALUES (1),(2),(3),(4)
UNION
VALUES (2),(3),(4))
becomes
CHECK (3
= (SELECT COUNT(*)
FROM VALUES (1),(2),(3),(4)
becomes
CHECK (3 = 4) = FALSE
Pablo Sanchez
news:3eebeea0.02090...@posting.google.com:
>>> I guess this is cheating as I don't believe recursive SQL is
>>> part
> of the SQL standard, but in DB2 SQL: <<
>
> The WITH clause is part of SQL-99 and not SQL-92. But I wisht aht
> INCITS H2 had never approved it. It is not needed.
Do you hold the opinion that it's not needed for functional reasons?
I'm wondering if the WITH adds readability, which in my opinion, is
a good reason to add.
Thx!
--
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com
Lennart Jonsson
> >> I guess this is cheating as I don't believe recursive SQL is part
> of the SQL standard, but in DB2 SQL: <<
>
> The WITH clause is part of SQL-99 and not SQL-92. But I wisht aht
> INCITS H2 had never approved it. It is not needed. I hafe a book on
> Trees & Heirarchies in SQL in the works in which I show the reader
> several ways to model tree structures and include constraints to avoid
> cycles with pure DDL.
>
Hmmm, recursive queries are not needed since there exists a structure
that can be modeled without them ;-)
--CELKO--
structure that can be modeled without them ;-) <<
Good shot! One of my Masters degreees is in math, so I like recursive
solutions and proof by induction, et al.
However, SQL was supposed to be used by Commercial programmers and
commercial work does not need full blown recursion. If you can
effectively represent a heirarchy, you are fine.
When I was on ANSI X3H2 (now INCITS H2), David Beech from Oracle wrote
a paper with a dozen SQL3 puzzles for the committee to solve. The DDL
used all of the new stuff we were adding to SQL-92 at the time. The
queries were simple things about customers, addresses, orders. He was
not trying to give us anything more complex than a freshman pop quiz.
Nobody on the committee could get the right answers because of the
comlexity of the language. What chance does a simple, Commercial
programmer have against somethig like that?
I wish I could find Ed Djkstra's and Nick Wirth's quotes about
programming lanaguage design. It was something to the effect that a
good language makes good programming easy and bad programming harder
to do, but not impossible. A language should not be bloated with
features that the user doesn't understand or use, etc.
Paul Vernon
>structure that can be modeled without them ;-) <<
>Good shot! One of my Masters degreees is in math, so I like
>recursive solutions and proof by induction, et al.
>However, SQL was supposed to be used by Commercial programmers
>and commercial work does not need full blown recursion.
Oh don't be so patronising. We may not all have multiple masters degrees,
but recursion ain't rocket science.
>If you can effectively represent a heirarchy, you are fine.
But what about all the existing representations? I certainly require
recursion to follow FK dependances in system catalog tables for example.
>When I was on ANSI X3H2 (now INCITS H2), David Beech from Oracle
>wrote a paper with a dozen SQL3 puzzles for the committee to solve.
>The DDL used all of the new stuff we were adding to SQL-92 at the
>time. The queries were simple things about customers, addresses,
>orders. He was not trying to give us anything more complex than
>a freshman pop quiz.
>Nobody on the committee could get the right answers because of the
>comlexity of the language.
Not even you Joe? :-)
I don't suppose that paper is online anywhere?
>What chance does a simple, Commercial programmer have against
>somethig like that?
I'll agree the syntax is not nice, but then that's just following the SQL
tradition :-)
>I wish I could find Ed Djkstra's and Nick Wirth's quotes about
>programming lanaguage design. It was something to the effect that a
>good language makes good programming easy and bad programming harder
>to do, but not impossible. A language should not be bloated with
>features that the user doesn't understand or use, etc.
Agreed but it should be computationally complete (or as near as damit).
Lennart Jonsson
> >> Hmmm, recursive queries are not needed since there exists a
> structure that can be modeled without them ;-) <<
>
> Good shot! One of my Masters degreees is in math, so I like recursive
> solutions and proof by induction, et al.
>
> However, SQL was supposed to be used by Commercial programmers and
> commercial work does not need full blown recursion. If you can
> effectively represent a heirarchy, you are fine.
>
I see your point, but I think one of the problems is that some (many?)
organisations
can not be modeled as a tree. The organisation itself is surely a
tree, but the emploeyees (besides belonging in one department)
participate in different projects, subprojects, subsubprojects,...
Similar structures may apear if one can group people together for
other reasons than projects. In such scenarios the organisation no
longer is a tree, but rather some form of graph. I played around a bit
with your nested set model, but I failed to extend it to handle such
graphs. Perhaps you discuss the possablity in your upcoming book?
> When I was on ANSI X3H2 (now INCITS H2), David Beech from Oracle wrote
> a paper with a dozen SQL3 puzzles for the committee to solve. The DDL
> used all of the new stuff we were adding to SQL-92 at the time. The
> queries were simple things about customers, addresses, orders. He was
> not trying toprogramming give us anything more complex than a freshman pop quiz.
> Nobody on the committee could get the right answers because of the
> comlexity of the language. What chance does a simple, Commercial
> programmer have against somethig like that?
>
...and I fully agree that the recursive construction in SQL doesnt
make it any easier. The syntax for recursion is rather weird, not to
mention the semantics ;-). However, table functions and views make it
possible to "encapsulate" the recursion, and provide an abstraction
layer for the simple commersial programmers, like my self :-)
Finally I must admit that I havent used the recursive possbilities of
DB2 for something serious. Instead I use a separate table which keep
track of the transitive closure of the graph (which is maintained via
triggers). As an experiment (as soon as I get the time that is :) I
will rewrite the functions that queries the tc table, and replace it
with recursion. If the performance is accepteble I will probably give
it a go, since the tc table becomes rather large for large
trees/graphs
[...]
/Lennart
Nathan Allan
> >> Hmmm, recursive queries are not needed since there exists a
> structure that can be modeled without them ;-) <<
>
> Good shot! One of my Masters degreees is in math, so I like recursive
> solutions and proof by induction, et al.
Yet you are saying that recursion isn't necessary as long as we can
handle trees?
Enforcing the right constraints is only half the battle. Without a
transitive closure operator or some other recursive mechanism, the
language is still not complete for the purposes of expression. The
SQL standard may have a recursive facility that IBM followed, but
nobody else seems to be interested in standards compliance (an obvious
overstatement for drama).
> However, SQL was supposed to be used by Commercial programmers and
> commercial work does not need full blown recursion.
> If you can
> effectively represent a heirarchy, you are fine.
SQL was _supposed_ to be a prototype language. Politics forced it
into being commercial and politics have kept such a horrid language in
place.
I must respectfully take issue with the notion that "commercial work
does not need full blown recursion." This is absurd unless your
notion of "commercial" is a contact manager. Hasn't the SQL tail
wagged the data model dog long enough! If other structures been an
option to easily construct, enforce, and manipulate, there would
likely be far more variety of structures used in today's applications.
I built a relational linked list the other day (as part of a
commercial application). Something I wouldn't have probably even
considered doing in SQL.
> When I was on ANSI X3H2 (now INCITS H2), David Beech from Oracle wrote
> a paper with a dozen SQL3 puzzles for the committee to solve. The DDL
> used all of the new stuff we were adding to SQL-92 at the time. The
> queries were simple things about customers, addresses, orders. He was
> not trying to give us anything more complex than a freshman pop quiz.
>
> Nobody on the committee could get the right answers because of the
> comlexity of the language. What chance does a simple, Commercial
> programmer have against somethig like that?
I agree. It's clearly time for the industry to end it's love affair
with SQL. But first there must be something to take it's place. D4
perhaps?! ;-)
--
Nathan Allan
Jan.Hidders
Nathan Allan <nat...@alphora.com> wrote:
>
>I agree. It's clearly time for the industry to end it's love affair
>with SQL. But first there must be something to take it's place. D4
>perhaps?! ;-)
My money is on XQuery. Whether I think that is a good thing is another
question.
-- Jan Hidders
Paul G. Brown
> Nobody on the committee could get the right answers because of the
> comlexity of the language. What chance does a simple, Commercial
> programmer have against somethig like that?
Garn, Joe. You're suffering from a delusion common among 'fire-fight'
consultants (those highly paid and in-demand experts brought into a project
on the point of collapse to see if anything can be done to save it). If most
of your day-to-day experience relates to 'things going wrong', and 'problems',
the perception of widespread failure colors your judgement. (I've seen the
same thing in some of my colleagues . . . )
Most commercial programmers are not 'simple'. They tend to be competent
professionals, on the whole. They build large, difficult and complex
information systems and along the way solve all kinds of problems. They
read the books (which is why publishers keep producing them) and they pay
attention. The overwhelming majority of database development projects
proceed without calling in the smoke jumpers like yourself.
And regarding the "Wow! SQL is such a complex language!" point. There are
two kinds of programming language questions you could ask. One kind concerns
the quirks of the standard, and the other relates to how to solve practical
problems using it. Not many developers can answer questions like 'What is the
'C' library function for finding a substring in another string?', or even
'What is the size of a long int?' off the top of their heads. And fewer still
give you Djkstra's algorithm for shortest path through a graph (or even
simpler things, like how to implement a binary heap). But anyone can read
K&R or Harbison & Steele or Sedgewick and figure it out. Pop quizes are
fun, but they're fairly poor ways to assess a language's relative complexity.
Recursion and closure (WITH) in SQL are necessary, because there are
problems you can't solve easily without them. Graphs and digraphs and
questions like 'How many departments have exceeded their salary budget this
year?' crop up in real systems from time to time. It is a poor toolbox that
does not include some way of dealing with an expected task.
I dunno. SQL feels about right to me. For simple, common tasks there is a
fairly straightforward way of handling it. And for the more complex problems
we can still avail ourselves of the relational equivalents of Duff's Device.
KR
P "smoke jumpin' no more" b
--CELKO--
degrees,
but recursion ain't rocket science. <<
It is really hard for commercial programmers. Hell, it was hard for
my college students, too. Remeber how you learn recursion?
1) copy sample program from textbook and run it.
2) copy sample program from book correctly and run it.
3) forget about it for a year or two.
4) try to use it for a real problem.
5) write recursive program.
6) write recursive program that stops.
7) write recursive program that stops when it should.
8) write more code until epithany hits and you finally see the wa or
tao of it.
>> But what about all the existing representations? I certainly
require
recursion to follow FK dependances in system catalog tables for
example. <<
You don't need that; the system needs that. You look at an ER
diagram.
>> Not even you Joe? :-) <<
Nope, not even me! And I was the only guy who was writign SQL daily
for a living back then.
>> I don't suppose that paper is online anywhere? <<
Nope.
>> I'll agree the syntax is not nice, but then that's just following
the SQL tradition :-) <,
Hey! that's my baby you are calling ugly!
--CELKO--
(many?)
of graph. I played around a bit with your nested set model, but I
failed to extend it to handle such graphs. <<
Me, too, and I have been trying for years. I keep coming back to an
adjacency list model or a adjacency array model and too much
procedural code for my taste. I need to find the time to go to a
college library and look up other representations for more general
graphs.
>> ...and I fully agree that the recursive construction in SQL doesnt
make it any easier. The syntax for recursion is rather weird, not to
mention the semantics ;-). <<
It is just a way to hide an iteration as far as I can tell ...
Pablo Sanchez
news:c0d87ec0.02091...@posting.google.com:
>>> Oh don't be so patronising. We may not all have multiple masters
> degrees,
> but recursion ain't rocket science. <<
>
> It is really hard for commercial programmers. Hell, it was hard
> for my college students, too. Remeber how you learn recursion?
> 1) copy sample program from textbook and run it.
> 2) copy sample program from book correctly and run it.
> 3) forget about it for a year or two.
> 4) try to use it for a real problem.
> 5) write recursive program.
> 6) write recursive program that stops.
> 7) write recursive program that stops when it should.
> 8) write more code until epithany hits and you finally see the wa
> or tao of it.
The students in my class (when I was at the Univ) all seemed to
grasp recursion pretty well. I think you're making it sound a bit
more complicated than what it is or perhaps you're dealing with a
different set of people. Even now, the folks I work with all
understand and if need be, can implement it well.
I personally rather do a loop construct than suffering the cost of
pushing down the program stack out of developer convenience.
Nathan Allan
> Hey! that's my baby you are calling ugly!
If SQL is "your baby", than you must have married your cousin! ;-)
--
Nathan Allan
Nathan Allan
> My money is on XQuery. Whether I think that is a good thing is another
> question.
Egad! This implies that your "money" is also on XML or some other
form of hierarchical system. These are a mistake the industry seems
to perpetually forget it made. With a decent Relational system as an
option, I dare say people would be less attracted to such nastiness.
Don't get me wrong... XML has it's purposes... but NOT as a data
management "model". I am speaking both from theory AND from personal
experience.
All that said, you could be right. But it would be a sad day if you
are. I put my money (obviously) on D4 because the proof is in the
pudding. If D4 users put XQuery users out of business--and they
will--who can argue with that! :-)
--
Nathan Allan
Nathan Allan
> And regarding the "Wow! SQL is such a complex language!" point. There are
> two kinds of programming language questions you could ask. One kind concerns
> the quirks of the standard, and the other relates to how to solve practical
> problems using it.
You do have a point about the relevance of the pop quiz, but I think
you go to far in justifying SQL. It is true that many of us are
accustomed to SQL and have learned to live with its many quirks, but
does that make the faults excusable?
> I dunno. SQL feels about right to me. For simple, common tasks there is a
> fairly straightforward way of handling it. And for the more complex problems
> we can still avail ourselves of the relational equivalents of Duff's Device.
In my experience, most do NOT feel the way that you do. When we
interview developers, we usually include a SIMPLE SQL test. Not of
some obscure aspects, but of basic things. Nearly all applicants
crumble shortly after basic restrictions. Could they figure it out
given some books and time? Of course. More importantly, is there an
alternative to SQL that is simple and elegant enough to enable even
the simplest of practitioners to answer every one of our questions
correctly... on the spot? If the answer is yes--and I think it
is--then we are doing ourselves a big disservice by hanging on the SQL
merely because we are used to it.
--
Nathan Allan
Paul G. Brown
> In my experience, most do NOT feel the way that you do. When we
> interview developers, we usually include a SIMPLE SQL test. Not of
> some obscure aspects, but of basic things. Nearly all applicants
> crumble shortly after basic restrictions. Could they figure it out
> given some books and time? Of course. More importantly, is there an
> alternative to SQL that is simple and elegant enough to enable even
> the simplest of practitioners to answer every one of our questions
> correctly... on the spot? If the answer is yes--and I think it
> is--then we are doing ourselves a big disservice by hanging on the SQL
> merely because we are used to it.
Well first, I'm not sure I care how they feel. ;-) But your evidence seems
to support my conclusion: simple stuff they get, more complex stuff they
need to work out.
And on alternatives: I've used quel, and worked with Datalog, and I've
done some Prolog. But the closest thing I've seen to the kind of interface
you're describing is Access and something called 'Visionary', which are
both distinguished by the fact that they're not languages at all! But to
be truly useful these kinds of tools need strongly typed schema (something
more semantically rich than lots of INTEGER and VARCHAR(32) columns).
Also: Beware building a tool any fool could use. Only fools will use it.
(I wish I'd made that up. )
KR
P "rushing in" b
--CELKO--
'fire-fight' consultants (those highly paid and in-demand experts
brought into a project on the point of collapse to see if anything can
be done to save it). If most of your day-to-day experience relates to
'things going wrong', and 'problems', the perception of widespread
failure colors your judgement. (I've seen the same thing in some of my
colleagues . . . ) <<
I grant that point. But I make most of money by teaching SQL to
programmers. I'm a multiple college degreed, Mensa member with a
zillion years experience in what I am teaching. I'd be surprised and
delighted if I found a newbie who got it all down in a week long
class.
What does depress me is the number of programmers who have not had an
undergrad database class and are being asked to write a database.
That is dangerous for the company. I am also depressed at the number
of "programmers" who have passed a certification test and cannot
actually program. I see people who will write a bubble sort in Java
because it is the only algorithm they can think of! They expect to
get a million dollars a year, of course.
>> Recursion and closure (WITH) in SQL are necessary, because there
are problems you can't solve easily without them. Graphs and digraphs
and questions like 'How many departments have exceeded their salary
budget this year?' crop up in real systems from time to time. It is a
poor toolbox that does not include some way of dealing with an
expected task. <<
It is a once in a blue moon task. I do not keep air tools around the
house even tho I know I will change a tire. When I need an exception
job done, I find the right tool and rent it.
Your departmental example is bad, since I can do that one with a
nested set model of the organization and GROUP BY. But try something
like Traveling Salesman or stable marriages. Did those two in SQL and
I found that the set narture of SQL gives you ALL the answers, and it
does not stop at the first one or when it is near-optimal. This eats
up a lot of resources, fast.
Bob Hairgrove
wrote:
>[snip]...Traveling Salesman or stable marriages. Did those two in SQL <
>[/snip]
Hi Joe,
Is your TS example published, or can you show us a little bit here,
perhaps? That would be great to see...
Bob Hairgrove
rhairgro...@Pleasebigfoot.com
Paul G. Brown
> house even tho I know I will change a tire. When I need an exception
> job done, I find the right tool and rent it.
But (opting into your analogy) our job *is* car repair. Your point
sounds like the kinds of arguments the MySQL folk use: 50% of SQL is rarely
used and slows things down (both true). Their (sensible) conclusion is that
there exists a need for a really fast, simple, SQL engine. And the evidence is
that they're right. But . . .
> Your departmental example is bad, since I can do that one with a
> nested set model of the organization and GROUP BY. But try something
> like Traveling Salesman or stable marriages. Did those two in SQL and
> I found that the set narture of SQL gives you ALL the answers, and it
> does not stop at the first one or when it is near-optimal. This eats
> up a lot of resources, fast.
I didn't explain the example very well. It was meant to speak
more to the utility of 'closure' (the WITH) keyword, which was something
else you said constituted an over-complication.
CREATE TABLE Depts (
Id Dept_Num NOT NULL PRIMARY KEY,
Name Label NOT NULL,
Budget Money NOT NULL
);
CREATE TABLE Emps (
Id Emp_Id NOT NULL PRIMARY KEY,
Dept Dept_Num NOT NULL
REFERENCES Depts ( Id ),
Name PersonName NOT NULL,
Salary Money NOT NULL
CHECK ( MONEY > '$US12550.00')
);
The question, again, was 'How many departments are exceeding their
budget?" Getting a list of departments exceeding their budget is
a GROUP BY query, but in SQL-92 (entry) getting the *COUNT* of
such departments requires the use of a temp table (or a client-side
cursor). (Working with the ol' PGB SQL Parser here, and it's known to be
buggy).
WITH ( Overbudget AS ( SELECT E.Dept, SUM ( E.Salary )
FROM Emps E
GROUP BY E.Dept
HAVING SUM ( E.Salary ) > ( SELECT D.Budget
FROM Depts D
WHERE E.Dept = D.Id ))
SELECT COUNT(*) FROM Overbudget;
I'm not sure you would have done the complete 'Travelling Salesman' BTW.
That's an NP-hard problem, and the most efficient approximate algorithms
would perform pretty poorly in SQL translation (lots of probes of temp
tables). Most Graph algorithms (outbranchings, flows, etc) are also hard.
Over small data sets shortest path (Edsger again!) is reasonable (Don
Chamberlain's book in SQL also uses it as an example).
As a simple, useful example, try topological sorting, or N-outneighbors.
These are pretty common in production planning systems. I've seen big,
nasty, poorly performing stored procedures written to accomplish this. Might
be nice to do it with a single, declarative expression.
Anyway, I wouldn't do Graphs in SQL that way. I'd advocate something like
this:
CREATE TABLE Edges (
Id Edge_Id NOT NULL PRIMARY KEY,
From Node_Id NOT NULL,
To Node_Id NOT NULL,
Weight DOUBLE NOT NULL
);
"What is the shortest path from node 'A to node 'Z"?
WITH ( Graph AS ( SELECT Merge ( Graph ( E.Edge_Id, E.From, E.To, E.Weight))
AS Total_Graph
FROM Edges E )),
SELECT S.Seq_Num,
Get_Id ( S.Edge ),
Get_From ( S.Edge ),
Get_To ( S.Edge ),
Get_Weight ( S.Edge )
FROM TABLE ( Edges ( Shortest_Path ( Graph.Total_Graph, 'A', 'B')));
Graph() is a constructor for a Graph of a single edge.
Merge() is an aggregate (like SUM() or AVG()) which takes a set of Graphs and
computes a composite Graph (eliminating duplicate edges).
Shortest_Path() takes a Graph and two node Ids and uses Dijkstra's or
Bell-Ford to compute shortest path (returns a Graph/ Path ).
Edges() is a table function which takes a Graph and returns a topological
sort (in the case of a Path, there is exactly one topological sort).
SQL as used here is a sort of framework: a declarative language for
'tying it all together). But the objects/data values in the framework, and
the operations they support, can be as internally complex as you like. In
this case the only SQL type is DOUBLE. The whole point, of course, is that
once you have these primitive operations embedded in the SQL you can turn
them to all kinds of uses and apply other predicates without the need for
a new program/recompile, etc, each time.
KR
P "go with the flow" b
David Cressey
is a
> fairly straightforward way of handling it. And for the more complex
problems
> we can still avail ourselves of the relational equivalents of Duff's
Device.
SQL feels about right to me, too. But I think of it as an interface
language, rather than as a programming language.
Why do people want the interface language to be a complete programming
language? What does that buy you?
David Cressey
The best way to learn recursion is to previously learn recursion. ;)
Well, ok, a simpler case of recursion.
And the simplest cases of recursion can be understood without reference to
the concept being learned.
--
Regards,
David Cressey
www.dcressey.com
David Cressey
> I grant that point. But I make most of money by teaching SQL to
> programmers. I'm a multiple college degreed, Mensa member with a
> zillion years experience in what I am teaching. I'd be surprised and
> delighted if I found a newbie who got it all down in a week long
> class.
>
A few years back, I was making most of my money teaching database design and
programming to seasoned professionals who had never been exposed to
databases. Most of the classes lasted one week. Most of the people who
"got it all down" got a chance to use SQL and a DBMS in the weeks following
the course. So its hard to separate out what they learned from me, what
they learned on the job, and what the interaction between those two
learning modes was.
The hard part was not teaching them how databases work. The hard part was
teaching them what databases are for. If they never got that, then they
tended to conclude that programming against databases was like programming
against files, only slower and less efficient.
Part of my material involved teaching SQL. Some of the same comments apply.
So, maybe you could give a short commentary on "what is SQL for?"
Paul Vernon
> language, rather than as a programming language.
>Why do people want the interface language to be a complete programming
>language? What does that buy you?
Only having to learn one language. No 'impedance mismatch'. Only having to
define all your UDT, operators, functions once. Not having to manually
keep the code written in the 'programming language' inline with the
'interface language' when the schema changes. Having relational
capabilities in the 'programming language' - e.g. relational operations on
local variables. Need I go on?
Regards
Paul Vernon
Nathan Allan
> And on alternatives: I've used quel, and worked with Datalog, and I've
> done some Prolog. But the closest thing I've seen to the kind of interface
> you're describing is Access and something called 'Visionary', which are
> both distinguished by the fact that they're not languages at all! But to
> be truly useful these kinds of tools need strongly typed schema (something
> more semantically rich than lots of INTEGER and VARCHAR(32) columns).
I am implying D4, which is our algebraic implementation of 'D'. I'll
post a link to the (vastly improved) language docs when we release our
new version (soon).
> Also: Beware building a tool any fool could use. Only fools will use it.
> (I wish I'd made that up. )
Though cynicism may be cute and often fitting, it is sometime tiring.
Especially to those who are giving their all in efforts to better the
situation. ;-)
Your warning would be true for a "lowest common denominator"
compromise, but doesn't describe "powerfully simple" solutions.
--
Nathan Allan
Peter Koch Larsen
[large snip]
> -Concepts are well defined. There are not the gray areas found in OO...
> like which class to put a 'binary' method into, or whether to use a
> method or a 'write-only' property. How about the fact that the V-table
> is tied to the type, not the operator (so no future 'virtual' operators
> can be defined by someone other than the type designer).
I can hear a lot of advertising for Alphora which probably happens to
be a good product and which - i know - is built around the Date/Darwen
hypothetical D language. To be fair, however, you should not use the
comparison with C++ (which i see shining through the above paragraph).
C++ was defined for systems programming and is defined with strict
criteriae regarding compatibility with C as well as performance. Let
me respond to your specific criticism here:
1) Grey areas as to where to put a binary method.
In practical life, these areas are not so grey at all. Very often, the
answer is evident and in the few practical cases where they are not,
C++ does provide you with the means to define your own multiple
dispatch methods - and put your operator outside any class, which are
for good reasons not part of the C++ language. Instead of extending
the C++ language, the designers chose to provide a mechanism
(templates) which allows the USER to extend the language, and this
solution is the what is used to build - generically - such diverse
stuff as vectors (arrays), lists and multimethods.Thus handling cases
such as these is really a breeze, and you can implement them in a way
that is most efficient for the case in hand. Let me refer you to
Alexandriescus "Modern C++ design" for more information on such
matters.
The hypothetical language D does support multimethods, but with a
prize: grey areas otherwere. If you define operators
OP(CIRCLE,ELLIPSE), OP(ELLIPSE,ELLIPSE) and OP(ELLIPSE,CIRCLE) the
system is silent of which method that is called when you invoke it
with two circle-parameters. C++ is defined very precisely and to force
a solution on a problem such as above into a (ISO) standard would
really be detrimental in many aspects.
2) Method/property:
There is no such thing as a property in C++. If you are referring to
C# or some extension of the C++ language (which I believe you might
be), it is true that you have a freedom of choice. Anyway, i do not
see why this freedom should be a minus. In D you certainly also have
these kind of choices.
3) V-tables:
I should mention that there is no such thing as a V-table in C++. The
standard describes a model, not an implementation.
4) Furthermore I do not consider the relational model well defined -
and certainly not in "The Third Manifesto".
[snip]
> -Specialization by Constraint and Generalization by Constraint. In
> English this means that I can treat an ellipse as a circle if the
> ellipse 'really is' as circle.
I must admit that this "Specialization by Constraint" stuff makes me a
little nervous. How well thought of is this model? As a hint let me
just ask you these questions:
1) Is RATIONAL a SUPERTYPE of INTEGER? If no, then why - this is
perhaps the most evident place to have "S by C". If yes, is there then
a difference in physical representation for these two types?
2) How do you perform integer division? How do you perform RATIONAL
division?
3) If you declare a type COMPLEX, can you then declare it a SUPERTYPE
of RATIONAL? If no, why? If yes how is the physical representation?
[snip]
> -"metadata" inference. In other words, it is possible, with a
> Relational implementation to no only know information like referential
> integrity constraints (FKs), keys, and other meta-data about base
> tables, but also about any arbitrary expression. This allows us, for
> example, to ask for the "default" column values of an arbitrary
> expression, or to know what the implicit references are of any
> expression. This also happens to be one of enabling factors for user
> interface derivation.
I do not quite understand the term "implicit references [...] of any
expression". Could you please explain?
>
> -Well defined and formal "model" for type inheritance. This actually
> isn't a Relational thing because type is an orthoginal (apples and
> oranges) concept to the RM. I still think it is worth making explicit
> because it should be clear that OO doesn't have an exclusive on the
> concept. Well defined inheritance is beneficial because the SYSTEM can
> help us enforce domain constraints and such.
I do not see the connection here. Surely it must be possible to
enforce constraints even if type inheritance is not used. Constraint
enforcement is even possible (and easy!) in C++ - whether you use
inheritance or not.
[snip]
>
> Now I should state that I use an OO programming language daily. As C.
> J. Date has pointed out, OO brought one... perhaps two good concepts to
> the table (neither of which are original but never mind):
> -User defined types (OOP encourages the practice)
Ahh - you must explain where user defined types then originated.
> -Type inheritance.
> OO is is not a "model" and is certainly not a data management solution.
What about generic programming.
This "OO is not a model" is often heard. However, in all the
litterature that i have read about relational systems (including a few
of Mr Date!), I have not seen ONE reference to a mathematical
text-book or similar that is supposed to describe the relational
model. Sure, relational systems are based upon set theory, but this
alone does not in my opinion warrant all these model-claims. For one
thing, the relational model has not considered the implications that
computers are finite (and thus that precision is) very much.
As for the model in "The Third Manifesto", I do not consider it a
model at all. It is a very rough sketch, where much detail is left out
and justifications for the prescriptions (and proscriptions) have no
or little theoretical foundation. The model of inheritance is just one
such place!
That said, I must hasten to add that I do like the relational model
very much and that i agree with you on most of your other points. We
just should not jump on the "third manifesto" waggon without a far
more careful study of the implications of that model.
I have read elsewhere that you can provide us with a pointer to the
Alphora documentation. I would be very happy if you could provide me
with a copy to.
Kind regards
Peter Koch Larsen
Peter Koch Larsen
> In article <akvgqp$2mks$1...@sp15at20.hursley.ibm.com>,
> Paul Vernon <paul....@ukk.ibmm.comm> wrote:
> >> Sure, try saying "there are no cycles in this binary relation" in one SQL
> >> statement.
> >
> >I guess this is cheating as I don't believe recursive SQL is part of the
> >SQL standard, but in DB2 SQL:
>
> Thanks for showing it.
>
> Made me wonder for a moment if such a recursive SQL would be computationally
> complete, but since you stay within PSPACE it is of course not.
>
> -- Jan Hidders
Actually, recursion is part of the SQL:1999 standard. Correct me if I am wrong.
Lauri Pietarinen
> Also: Beware building a tool any fool could use. Only fools will use it.
> (I wish I'd made that up. )
Hmm... Chess looks like a game that is so simple that any fool
can learn how to play it...
regards,
Lauri
David Cressey
> Only having to learn one language. No 'impedance mismatch'. Only having to
> define all your UDT, operators, functions once. Not having to manually
> keep the code written in the 'programming language' inline with the
> 'interface language' when the schema changes. Having relational
> capabilities in the 'programming language' - e.g. relational operations on
> local variables. Need I go on?
>
Yes, please do go on, if the remaining values are useful to present. I was
asking the question for real, and not rhetorically. I realize in retrospect
that it could be read as rhetorical, but that was not my intent.
Your initial list is a good one, and in part, I share your perceptions of
the value of having a programming language that can also serve as an
interface language (or, if you prefer, having an interface language that can
also serve as a programming language).
I'd like to suggest Oracle's PL/SQL as one possible way to look at this
question. I don't mean to suggest that PL/SQL is the "right answer" to the
problem at hand. All I'm suggesting is that it is a useful one to look at
in order to clarify the issues.
As far as your list goes, I'm going to play devil's advocate in what
follows. I apologize in advance to anyone who thinks I shouldn't do that.
On the search for a single language. This has been the holy grail of
computing since the 1940s. We are further away than we ever were. The
reason we can't see it is the same reason that was given for the failure to
find the holy grail: we are impure.
Burdening the interface language with becoming the universal programming
language will simply weigh the language down to the point where it fails in
its initial mission.
On "impedance mismatch". No argument there. At least, I can't think of
one.
On not having to manually prevent source code skew when the schema changes.
This is not a language issue. The good news is that physical database
changes that do not represent logical schema changes can be "encapsulated in
side the database", meaning made transparent to the rest of the community.
Schema changes that reflect a changed view of the logical data requirements
are going to propagate into the application source, regardless of whether
the application is coded in one language or more than one language. Source
code management and configuration management are issues regardless of
whether there is one source language or there are many.
Having relational capabilities in the programming language. No argument
there. It would be great to be able to do select, project, join, etc. in
the programming language. But aside from defining "relations" as objects
upon which relational operators can operate, aren't we talking about a
relatively trivial extension of existing languages?
Paul Vernon
>of computing since the 1940s. We are further away than we ever were.
>The reason we can't see it is the same reason that was given for the
>failure to find the holy grail: we are impure.
:-) In a sense this is very true. To find this holy grail the single
language would need to be pervasive, so that even our operating systems
embodied the same concepts as the language.
Not easy to get to there from here. We are rather locked in. It might be
easier than say getting the whole world to drive on the same side of the
road, but I'm not sure how much easier.
>Burdening the interface language with becoming the universal programming
>language will simply weigh the language down to the point where it fails
>in its initial mission.
What burdens programming languages is lack of conceptual integrity, not
their ambition.
>Having relational capabilities in the programming language. No argument
>there. It would be great to be able to do select, project, join, etc. in
>the programming language. But aside from defining "relations" as
objects
>upon which relational operators can operate, aren't we talking about a
>relatively trivial extension of existing languages?
If it's trivial, why has it not been done? Or has it?
David Cressey
> their ambition.
As I said, those who are impure cannot see the holy grail. Lack of
concpetual integrity is simply another way of putting it. I differ about
whether ambition is irrelevant. The more ambitious the goal, the more lack
of conceptual integrity gets in the way. It's a trade off.
> If it's trivial, why has it not been done? Or has it?
A real good question, Paul. AFAIK no one has built these capabilities into
a language. What comes close are some of the "in memory" databases, or so
it seems to me. I'm looking forward to hearing from people who have used
them, or who know that "it" has been done.
I'm going to add the proviso that, while extending the language to allow
relational operators to be expressed is trivial, implementing the
relational operators is anything but trivial. A crude implementation is
likely to be terribly slow, or not general enough, or both. And
implementing them the right way is likely to involve a large engineering
effort, if the example of engineering relational DBMS products is any
indicator.
Nathan Allan
> Why do people want the interface language to be a complete programming
> language? What does that buy you?
From the DBMS perspective, it makes sense to provide a facility for
the definition of "custom" relational operators. So if one subscribes
to the concept of a DBMS only supporting an "interface language", then
where are such operators defined?
A relational database system with no imperative aspect would be
completely functional, and therefore completely static. A read-only
database doesn't do us much good. It follows that we must treat the
functional aspects of the database as a subsystem within an imperative
one. As is the case for many DBMSs, the imperative portion of the
overall system may be external to the expression engine (with three
basic update operations thrown in). In this design, data types of the
database system are matched to those in the procedural language.
Chances are, the procedural language will not have direct support for
the DBMS' types, especially relation types. The result is that the
external language must interface again with the DBMS to perform
operations against these data types. This "impedance mismatch"
increases complexity while reducing performance and optimizability.
The impedance mismatch problem also leads to duplicated logic and
degrades implementation independence.
We have hopefully established that imperative operations are a
necessary component of a DBMS, and it is rational to view the
functional aspects as a facility within the procedural realm. It then
follows that we can (and should) provide both within a single
language. This consolidates type definition, ensures consistent
operations, increases optimization potential, maximizes code reuse,
and simplifies the application logic.
From another posting:
> Burdening the interface language with
> becoming the universal programming
> language will simply weigh the language
> down to the point where it fails in
> its initial mission.
Why? Not only would I argue that it does not "burden" the "interface
language" (functional or expressive subsystem), but I would assert
that it helps to generalize and improve it. My argument is perhaps
backed by the fact that expressions are an essential part of an
imperative language.
> Having relational capabilities in the
> programming language. No argument
> there. It would be great to be able
> to do select, project, join, etc. in
> the programming language. But aside
> from defining "relations" as objects
> upon which relational operators can
> operate, aren't we talking about a
> relatively trivial extension of
> existing languages?
If by "objects" you mean "types", then yes. It isn't rocket science,
but considering nobody currently does it (my organization excepted of
course ;-), you would think it were!
As mentioned, the essence of the discussion focuses on the support for
relational data types. One way to look at this whole "impedance
mismatch" issues is from a perspective of types. It would seem
ludicrous to build one language to deal with integers, another to deal
with floats, yet another for arrays, etc. Isn't that what the notion
of using a distinct language for relational operations is about?!
--
Nathan Allan
David Cressey
> of using a distinct language for relational operations is about?!
I think you are right, but I think we ended up in that situation by
accident, rather than intent.
I would say that SQL became the de facto standard language for data exchange
between applications and databases more or less the same way that other de
facto standards come into being. It has to do with being in the right place
at the right time.
Since relational DBMS systems had functions for supporting relational
operations, and programming languages did not, it seemed to make sense to
allow the SQL SELECT statement to include reference to all the relational
operators that the DBMS supported. In essence, the SELECT allows a view to
be specified and requested in one step. I think that made sense at the
time.
Given that situation, it was only a matter of time before programmers began
to use tables managed by an RDBMS for the sake of being able to join, etc.
rather than for the traditional reasons one puts some data in a database.
So, you end up with the SQL being an add in to procedural languages, for the
sake of relational functionality, and not just an interface, for the sake
of data exchange. Your critique of that state of affairs is spot on, IMO.
At this stage, my question is, "what makes sense, going forward?" Should a
new language be developed, that takes on a different from SQL's mission,
but one that overlaps SQL's mission? It sounds, from the discussion of "D"
and its family of languages, as though the answer is "yes", at least for
some of the important authors. If a new language is developed, is that
going to increase or decrease the total amount of confusion generated by the
present plethora of languages? Does anybody care?
mountain man
> At this stage, my question is, "what makes sense, going forward?" Should
a
> new language be developed, that takes on a different from SQL's mission,
> but one that overlaps SQL's mission? It sounds, from the discussion of
"D"
> and its family of languages, as though the answer is "yes", at least for
> some of the important authors. If a new language is developed, is that
> going to increase or decrease the total amount of confusion generated by
the
> present plethora of languages? Does anybody care?
I agree with your earlier comments about SQL being there at the
right time and place. You could probably summarise this by the
remark that all computer systems software "evolves" in time.
Rather than ask the question on the future evolution of new RDBMS
languages, I have taken the approach that SQL, in combination with the
management services provided by most RDBMS (eg: task scheduler)
will be able to handle 100% of the problems encountered in realtime.
Therefore I do not see evolution of SQL or of any language capacity
to be of any real importance in the entire future picture of things.
Rather, I see the important evolutionary trends as they relate to the
(site) management of the RDBMS environment.
Specifically, I see an entire new generation of database application
"software" being written _exclusively_ using the RDBMS native
utilities (largely stored procedures). The end-point of this evolution
is a shift in the location of the (db) applications software environment.
It will disappear from the current desktop/apps server environment
external to the RDBMS, and move internal to the RDBMS. It will
evolve in this manner because it is far easier to manage two systems
software environments than three. For further detail see:
http://www.mountainman.com.au/software/history/it7.html
http://www.mountainman.com.au/software/history/it8.html
Farmer Brown
Falls Creek
OZ
David Cressey
I'm going to spend a little time reading your web papers before responding.
In the meantime, reacting to just your message,
I think there's an alternative formulation, that I'll call the "Java
approach". That is to separate the
"locus of compilation" from the "locus of execution". That is, the Java
source code gets written, maintained, and compiled
at one location on the net, while the executable code is stored, retrieved,
and executed under the auspices of the RDBMS.
I think there are important problems with that approach, but the benefits
are large enough so that we will likely be seeing it for a decade or so.
--
Regards,
David Cressey
www.dcressey.com
"mountain man" <prfb...@magna.com.au> wrote in message
news:NM9i9.35590$g9.1...@newsfeeds.bigpond.com...
John Jacob
> languages, I have taken the approach that SQL, in combination with the
> management services provided by most RDBMS (eg: task scheduler)
> will be able to handle 100% of the problems encountered in realtime.
>
> Therefore I do not see evolution of SQL or of any language capacity
> to be of any real importance in the entire future picture of things.
> Rather, I see the important evolutionary trends as they relate to the
> (site) management of the RDBMS environment.
>
> Specifically, I see an entire new generation of database application
> "software" being written _exclusively_ using the RDBMS native
> utilities (largely stored procedures). The end-point of this evolution
> is a shift in the location of the (db) applications software environment.
>
> It will disappear from the current desktop/apps server environment
> external to the RDBMS, and move internal to the RDBMS. It will
> evolve in this manner because it is far easier to manage two systems
> software environments than three. For further detail see:
The technology to effect this type of system has been in place for
decades, and yet this solution has not been developed. Primarily
because black-box systems such as the one you describe where all the
code is 'inside the RDBMS' (as an aside, I wish you wouldn't call them
RDBMSs, they aren't, they are SQL-based DBMSs) does not lend itself to
client application development. A software system must be provided
such that the business rules (organizational intelligence if you
prefer, same thing) enforced by the server can be seamlessly and
transparently enforced by the client without additional development
effort.
As for integrity, the idea that stored procedures can be run at
pre-determined intervals to validate data is nothing new, nor is it
exceptionally clever. Integrity is the most important aspect of any
given database system, and the fact is that SQL-based systems fall far
too short in this arena (and many others) to present a viable option
moving forward. A new language must be built (and has been, I might
add) which can express these integrity constraints without resorting
to triggered procedures. Mountains of error-prone, procedural code in
proprietary DBMS dialects would be completely eliminated if the
database language simply allowed the expression of database-wide
integrity constraints. This is the goal, and this is the future of
database management systems.
Regards,
Bryn Rhodes
Alphora
mountain man
> > languages, I have taken the approach that SQL, in combination with the
> > management services provided by most RDBMS (eg: task scheduler)
> > will be able to handle 100% of the problems encountered in realtime.
> >
> > Therefore I do not see evolution of SQL or of any language capacity
> > to be of any real importance in the entire future picture of things.
> > Rather, I see the important evolutionary trends as they relate to the
> > (site) management of the RDBMS environment.
> >
> > Specifically, I see an entire new generation of database application
> > "software" being written _exclusively_ using the RDBMS native
> > utilities (largely stored procedures). The end-point of this evolution
> > is a shift in the location of the (db) applications software
environment.
> >
> > It will disappear from the current desktop/apps server environment
> > external to the RDBMS, and move internal to the RDBMS. It will
> > evolve in this manner because it is far easier to manage two systems
> > software environments than three. For further detail see:
http://www.mountainman.com.au/software/LittleSteps
> The technology to effect this type of system has been in place for
> decades, and yet this solution has not been developed.
Not quite. It requires the presence of separately addressable
(from the apps environment) stored procedures within the (R)DBMS.
Was this available so long ago? I doubt it would have been more
than a decade, but would indeed be interested to learn the answer
to the following question ....
*******[Open Question]*********************************
When did each of the major (R)DBMS vendors introduced the
functionality of separately addressable stored procedures which
could be called directly (and with input/output parameters) from
a call in the applications environment?
Oracle: ?
IBM: ?
Microsoft: about 1994
Other (R)DBMS vendors: ?
*******[Close Question]*********************************
to continue, however ...
More importantly, it requires the software supplier to convert
all his/her code to the form of (R)DBMS stored procedures (ie: 100%).
and having all the source code visible to the end client DBA, etc.
Such solutions have not before been developed commercially before
because it is an extremely OPEN SOURCE approach, and software
vendors get terribly skittish about such an animal.
>Primarily
> because black-box systems such as the one you describe where all the
> code is 'inside the RDBMS' (as an aside, I wish you wouldn't call them
> RDBMSs, they aren't, they are SQL-based DBMSs) does not lend itself to
> client application development.
On the contrary, I am making such a product available and one of the
more important benefits it has is the ability to be able to rapidly
develop and maintain client applications.
> A software system must be provided
> such that the business rules (organizational intelligence if you
> prefer, same thing) enforced by the server can be seamlessly and
> transparently enforced by the client without additional development
> effort.
Under my proposed arrangement, if the client had contracted
with a software vendor for the provision of a new software
package, then the vendor will simply provide the client with
a database within which reside the database stored procedures
which define - in totality - the new application (less the portal).
The software system so provided does not require an applications
environment out on the scattered desktops or applications servers
as it is totally catered for within the (R)DBMS.
It works fine.
> As for integrity, the idea that stored procedures can be run at
> pre-determined intervals to validate data is nothing new, nor is it
> exceptionally clever.
I have not seen any arguments to the contrary.
> Integrity is the most important aspect of any
> given database system, and the fact is that SQL-based systems fall far
> too short in this arena (and many others) to present a viable option
> moving forward.
Your first statement is accurate, concerning the central
role of data integrity, but your second statement needs to
be qualified a little more. On face value, to me, it looks
just plain wrong. My perspective is from that of an IT
manager who has seen a number of SQL based systems
that have been engineered to an optimum level of
automation, including data integrity exception
management.
In my experience, it is not the system falling far short
but the custodian engineers and managers of that system.
There are always work arounds. It is the nature and the
demand of the production environment. Nothing is perfect.
Be prepared. Automate integrity exception checking. etc
>A new language must be built (and has been, I might
> add) which can express these integrity constraints without resorting
> to triggered procedures. Mountains of error-prone, procedural code in
> proprietary DBMS dialects would be completely eliminated if the
> database language simply allowed the expression of database-wide
> integrity constraints. This is the goal, and this is the future of
> database management systems.
Perhaps from your perspective it is. It really depends on the
importance you attach to the mechanism(s) available to address
the maintenance of database integrity constraints and the means
by which you ultimately resolve these issues 1) today in a
production environment and 2) in some future technology.
However, from my perspective, the goal of the evolution of
computer software systems will be their ability to discard the
entire (db) applications software system environment as does
a command module eject its primary fuel booster module in
an outbound trajectory.
1) 1st software system environment: Hardware OS and net OS
2) 2nd software system environment: (R)DBMS <<-------|
3) 3rd software system environment: (db) applications -->|
Environments 1 and 2 and known stable reliable products
which may have as few as a dozen vendor combinations
covering more than 95% of all (R)DBMS sites.
Environment 3 is a rampant uncontrolled flaming elephant
that has been in service since the very beginning and needs
to be lead to retirement and pasture.
Once the 3rd environment is no longer, the evolution of
technology running now exclusively within the 1st and 2nd
computer software environments will move into a new
evolutionary phase of the analytical machine technology.
Only then will it commence to pick up speed and
a true direction, and a that stage, things will start moving
ahead again.
But for now, I think many of us are simply sitting
back and watching the same old circus show.
> Regards,
> Bryn Rhodes
> Alphora
Best wishes,
Farmer Brown
Falls Creek,
Australia
www.mountainman.com.au
Nathan Allan
> At this stage, my question is, "what makes sense, going forward?" Should a
> new language be developed, that takes on a different from SQL's mission,
> but one that overlaps SQL's mission? It sounds, from the discussion of "D"
> and its family of languages, as though the answer is "yes", at least for
> some of the important authors. If a new language is developed, is that
> going to increase or decrease the total amount of confusion generated by the
> present plethora of languages? Does anybody care?
First, let's assume that the "new" language supports all functionality
required for both the relational and imperative aspects of ANY
application. If this is not the case, than such language doesn't
really qualify to replace or improve anything. So let's assume this
is the case. By necessity, the injection of this new language into an
existing developer's paradigm implies increased complexity. Once
introduced however, complexity is clearly reduced.
Do people care? I think that most practitioners do care, they just
don't know it! ;-) They care when they are given a new tool that
dramatically increases their productivity. Similarly, they care when
competition gets such a tool and suddenly increases their
productivity. What they don't care about is unimplemented theory. We
cared enough (as tools AND application practitioners) to implement
what we feel is the "new" language. :-)
--
Nathan Allan
Nathan Allan
> I agree with your earlier comments about SQL being there at the
> right time and place. You could probably summarise this by the
> remark that all computer systems software "evolves" in time.
Whoa... being in the right place at the right time best falls in the
category of "politics", not technological innovation/evolution.
> Specifically, I see an entire new generation of database application
> "software" being written _exclusively_ using the RDBMS native
> utilities (largely stored procedures). The end-point of this evolution
> is a shift in the location of the (db) applications software environment.
>
> It will disappear from the current desktop/apps server environment
> external to the RDBMS, and move internal to the RDBMS. It will
> evolve in this manner because it is far easier to manage two systems
> software environments than three. For further detail see:
Your suggestions revolves around the assumption that today's DBMSs are
a capable environment for application development. This is
unfortunately not the case and is the reason that we have "application
servers" and such. It is clearly desirable to remove the conceptual
"middle tier" and reduce overall application complexity. However,
this layer can only be eliminated by a aptly capable DBMS. Existing
systems can be preserved, but only as a "storage engine" underlying a
more capable DBMS.
I will respond more on your proposed "layers" in another posting...
--
Nathan Allan
Nathan Allan
> Not quite. It requires the presence of separately addressable
> (from the apps environment) stored procedures within the (R)DBMS.
What is the "apps environment"? More precision would greatly help
this conversation.
> Was this available so long ago? I doubt it would have been more
> than a decade, but would indeed be interested to learn the answer
> to the following question ....
You want to know the amount of time that various DBMS venders have had
externally exposed procedures/functions? What relevance does this
have? Such capabilities are relatively old news. The industry
flirted with thin client / fat server style applications using this
methodology, but many moved away from it in favor of external
programming languages. Why? Because the systems suffer from deep and
severe limitations.
> More importantly, it requires the software supplier to convert
> all his/her code to the form of (R)DBMS stored procedures (ie: 100%).
> and having all the source code visible to the end client DBA, etc.
>
> Such solutions have not before been developed commercially before
> because it is an extremely OPEN SOURCE approach, and software
> vendors get terribly skittish about such an animal.
They certainly HAVE been developed commercially before. Many
applications still are built like this, and most DBMSs have the
ability to encrypt/obfuscate user code to protect IP.
> On the contrary, I am making such a product available and one of the
> more important benefits it has is the ability to be able to rapidly
> develop and maintain client applications.
How about client enforced constraints, defaults/ID generators,
calculated columns, navigation & buffering, multi-table transactional
implications, and other such matters? Today's DBMSs provide little
help in handling these matters.
> Under my proposed arrangement, if the client had contracted
> with a software vendor for the provision of a new software
> package, then the vendor will simply provide the client with
> a database within which reside the database stored procedures
> which define - in totality - the new application (less the portal).
I think you are seriously down-playing the front-end portion of the
application. This portion can easily constitute the majority of the
development and maintenance time using today's common methodologies.
What you are saying here reads to me as, "the software vendor will
deliver a half-completed application." ;-)
> > As for integrity, the idea that stored procedures can be run at
> > pre-determined intervals to validate data is nothing new, nor is it
> > exceptionally clever.
>
> I have not seen any arguments to the contrary.
Here are a few:
-Non real-time nature of integrity enforcement (when can you trust
your data?).
-Arbitrary nature of which constraints are enforced where...
-Imperative nature of procedure based constraint enforcement
encourages bugs, degrades performance, reduces optimizability.
Though I have stated this in another posting (with no reply), let me
state it again: if (big IF) all integrity constraints can be enforced
by the DBMS, then we don't need an exception management system for
integrity enforcement. If there is some application specific reason
to put non-conforming data temporarily into the system, than such data
should be separately modeled. That is precisely the task of
application development: deciding what integrity constrains go where.
Your proposed "exception management system" is therefore an
application-level facility.
> My perspective is from that of an IT
> manager who has seen a number of SQL based systems
> that have been engineered to an optimum level of
> automation, including data integrity exception
> management.
Your argument has jumped from discussing the concepts to basically, "I
have seen people work around these problems." The database system is
there to help us out as much as possible. The more integrity the
system enforces, the less the application developer must enforce. The
obviously desirable extreme is a system that enforces 100% of the
integrity. Without resorting to arguments outside of the conceptual
realm, please state why it would not be desirable for the system to
enforce ALL integrity declaratively.
> In my experience, it is not the system falling far short
> but the custodian engineers and managers of that system.
> There are always work arounds. It is the nature and the
> demand of the production environment. Nothing is perfect.
> Be prepared. Automate integrity exception checking. etc
How about "Automate integrity checking".
> Perhaps from your perspective it is. It really depends on the
> importance you attach to the mechanism(s) available to address
> the maintenance of database integrity constraints and the means
> by which you ultimately resolve these issues 1) today in a
> production environment and 2) in some future technology.
The bottom line is that you are making an argument for a less general
solution. The "exception management system" you espouse can be
implemented in a "pure" DBMS that can enforce ALL integrity. However,
not all benefits of such a DBMS can be provided by an exception
management system.
> 1) 1st software system environment: Hardware OS and net OS
> 2) 2nd software system environment: (R)DBMS <<-------|
> 3) 3rd software system environment: (db) applications -->|
There are many different ways we could draw lines between conceptual
systems. Calling the "3rd environment" the "application" is bad
terminology. The sum of all systems that together provide the service
constitute the software application.
> Environment 3 is a rampant uncontrolled flaming elephant
> that has been in service since the very beginning and needs
> to be lead to retirement and pasture.
I presume you are including the user interface in this "3rd
environment." Surely you aren't suggesting the elimination of that!
Not only does the 3rd environment need to stay (so the application has
a client), but environments 1 and 2 could actually be combined to
further reduce complexity. Combining the DBMS with the OS has been
done in various forms over the years with varying levels of success
(AS/400). The minimal number of environments cannot be reduced below
2 unless we cram all of the users into the server room and let them
duke it out. :-)
> Once the 3rd environment is no longer, the evolution of
> technology running now exclusively within the 1st and 2nd
> computer software environments will move into a new
> evolutionary phase of the analytical machine technology.
So, "once we keep those users out of the application, things can
really begin to progress!" ;-)
--
Nathan Allan
mountain man
news:fedf3d42.02092...@posting.google.com...
> "mountain man" <prfb...@magna.com.au> wrote in message
news:<gHui9.36246$g9.1...@newsfeeds.bigpond.com>...
>
> > Not quite. It requires the presence of separately addressable
> > (from the apps environment) stored procedures within the (R)DBMS.
>
> What is the "apps environment"? More precision would greatly help
> this conversation.
The environment which houses the database application software.
Normally located on the distributed client desktop or one or more
application servers.
Precision, as you point out, is everything. To this end I have
attempted to outline this argument on the following page:
http://www.mountainman.com.au/software/history/it8.html
> > Was this available so long ago? I doubt it would have been more
> > than a decade, but would indeed be interested to learn the answer
> > to the following question ....
>
> You want to know the amount of time that various DBMS venders have had
> externally exposed procedures/functions? What relevance does this
> have? Such capabilities are relatively old news. The industry
> flirted with thin client / fat server style applications using this
> methodology, but many moved away from it in favor of external
> programming languages. Why? Because the systems suffer from deep and
> severe limitations.
At that time, they may well have.
> > More importantly, it requires the software supplier to convert
> > all his/her code to the form of (R)DBMS stored procedures (ie: 100%).
> > and having all the source code visible to the end client DBA, etc.
> >
> > Such solutions have not before been developed commercially before
> > because it is an extremely OPEN SOURCE approach, and software
> > vendors get terribly skittish about such an animal.
>
> They certainly HAVE been developed commercially before. Many
> applications still are built like this, and most DBMSs have the
> ability to encrypt/obfuscate user code to protect IP.
Point taken here, with the encryption facility. However when
the full nature of such commercial products is examined in detail
my questions would have to be these:
1) How many lines of code exist external to the RDBMS?
2) How many lines of code exist internal to the RDBMS?
3) What is the function of the code external to the DB?
> > On the contrary, I am making such a product available and one of the
> > more important benefits it has is the ability to be able to rapidly
> > develop and maintain client applications.
>
> How about client enforced constraints, defaults/ID generators,
> calculated columns, navigation & buffering, multi-table transactional
> implications, and other such matters? Today's DBMSs provide little
> help in handling these matters.
Just because we do not seem to have an RDBMS vendor
who has answered all the above such matters in today's
world, should I who seek such a solution say to myself:
"Perhaps I will return to the shop tomorrow, and see whether
such a perfect system, that will have no problems at all, will
be ready. If it is, then I will buy it. But if it is not, then I will
wait until it is ready"
Can you imagine the problems of this approach?
Yet it seems you support it.
All the problems that people have thrown up in this newsgroup
and others, and in the planet's stock of comments concerning
RDBMS software are resolvable in production with workarounds.
Some folk need to run the RDBMS software to manage their
organisation and they take all the above list of problems on board
with them. And they are aware of these problems. And they
work around each and every one of them.
> > Under my proposed arrangement, if the client had contracted
> > with a software vendor for the provision of a new software
> > package, then the vendor will simply provide the client with
> > a database within which reside the database stored procedures
> > which define - in totality - the new application (less the portal).
>
> I think you are seriously down-playing the front-end portion of the
> application. This portion can easily constitute the majority of the
> development and maintenance time using today's common methodologies.
> What you are saying here reads to me as, "the software vendor will
> deliver a half-completed application." ;-)
The front end of the application which is fully defined within the
RDBMS is a portal to the RDBMS. One small light-footed program
with absolutely zero application specific code. Consequently, it
requires zero maintenance when developing using it, because all
such development, by implicit design, is achieved exclusively by
harnessing RDBMS stored procedures.
Through this portal, all organisational users have the application
presented to them.
> > > As for integrity, the idea that stored procedures can be run at
> > > pre-determined intervals to validate data is nothing new, nor is it
> > > exceptionally clever.
> >
> > I have not seen any arguments to the contrary.
>
> Here are a few:
> -Non real-time nature of integrity enforcement (when can you trust
> your data?).
As often as you run the check. You want to run the check every
2.7 seconds, then you can trust it at the end of every 2.7 second
interval during the business day.
Let me ask you a question about a production system, and
not a hypothetical theoritical analytical machine sitting on
the drawing board for future release ....
You and others have asked this question:
"How often can you trust your data?"
Now I ask you ... provide an alternative approach to have this
question answered in realtime, (re: a production RDBMS) now,
today. ;-)
.
> -Arbitrary nature of which constraints are enforced where...
> -Imperative nature of procedure based constraint enforcement
> encourages bugs, degrades performance, reduces optimizability.
Ditto --- see above.
> Though I have stated this in another posting (with no reply), let me
> state it again: if (big IF) all integrity constraints can be enforced
> by the DBMS, then we don't need an exception management system for
> integrity enforcement.
Nathan, this is not even a big IF.
It is a non-existent IF at them moment.
And I am used to working in production.
I do not have the time to consider something that
does not exist. Of course, it would be wonderful
if, in the evolution of the RDBMS such facilities
could be evolved.
And yes, as such facilities appear we can certain cancel
and relax the corresponding data integrity exception checking.
But until they appear, what is there to be done?
My response is an EMS.
>If there is some application specific reason
> to put non-conforming data temporarily into the system, than such data
> should be separately modeled. That is precisely the task of
> application development: deciding what integrity constrains go where.
> Your proposed "exception management system" is therefore an
> application-level facility.
>
> > My perspective is from that of an IT
> > manager who has seen a number of SQL based systems
> > that have been engineered to an optimum level of
> > automation, including data integrity exception
> > management.
>
> Your argument has jumped from discussing the concepts to basically, "I
> have seen people work around these problems." The database system is
> there to help us out as much as possible. The more integrity the
> system enforces, the less the application developer must enforce. The
> obviously desirable extreme is a system that enforces 100% of the
> integrity. Without resorting to arguments outside of the conceptual
> realm, please state why it would not be desirable for the system to
> enforce ALL integrity declaratively.
You have no argument from me, IF IT CAN BE DONE.
My point is simply:
it is not done yet.
> > In my experience, it is not the system falling far short
> > but the custodian engineers and managers of that system.
> > There are always work arounds. It is the nature and the
> > demand of the production environment. Nothing is perfect.
> > Be prepared. Automate integrity exception checking. etc
>
> How about "Automate integrity checking".
This indeed would be a service that the RDBMS vendors
could introduce in the absence of all those other features
and database services you reference above.
I have constructed my own automated integrity checking,
and I believe all production sites need an automated EMS.
> > Perhaps from your perspective it is. It really depends on the
> > importance you attach to the mechanism(s) available to address
> > the maintenance of database integrity constraints and the means
> > by which you ultimately resolve these issues 1) today in a
> > production environment and 2) in some future technology.
>
> The bottom line is that you are making an argument for a less general
> solution. The "exception management system" you espouse can be
> implemented in a "pure" DBMS that can enforce ALL integrity. However,
> not all benefits of such a DBMS can be provided by an exception
> management system.
This EMS product is but a specific instance of an application.
It is something I can do at a production site using some RDBMS
product X. I do not have the means to change product X, so
I have to work around its shortcomings.
> > 1) 1st software system environment: Hardware OS and net OS
> > 2) 2nd software system environment: (R)DBMS <<-------|
> > 3) 3rd software system environment: (db) applications -->|
>
> There are many different ways we could draw lines between conceptual
> systems. Calling the "3rd environment" the "application" is bad
> terminology. The sum of all systems that together provide the service
> constitute the software application.
While that may be so, in examination of the actual code and
modules which comprise this software you must admit that
you would be able to separate those threads which deal with
specific transactions within each of the above layers.
> > Environment 3 is a rampant uncontrolled flaming elephant
> > that has been in service since the very beginning and needs
> > to be lead to retirement and pasture.
>
> I presume you are including the user interface in this "3rd
> environment." Surely you aren't suggesting the elimination of that!
See above. The user interface will be one small light-footed
RDBMS portal software program.
> Not only does the 3rd environment need to stay (so the application has
> a client), but environments 1 and 2 could actually be combined to
> further reduce complexity. Combining the DBMS with the OS has been
> done in various forms over the years with varying levels of success
> (AS/400). The minimal number of environments cannot be reduced below
> 2 unless we cram all of the users into the server room and let them
> duke it out. :-)
There is a diagram on the page earlier provided
http://www.mountainman.com.au/software/history/it8.html
which shows that in the arrangement there is a "box" opposite
the RDBMS server on the client stack --- intentionally left blank.
The entire 3rd environment can go providing the RDBMS portal
software is able to be seen as the occupant of that box. If not,
then envisage an application software environment where the
number of separate executable programs is one, not many, and
the entire (db) apps environment is one program - the portal.
> > Once the 3rd environment is no longer, the evolution of
> > technology running now exclusively within the 1st and 2nd
> > computer software environments will move into a new
> > evolutionary phase of the analytical machine technology.
>
> So, "once we keep those users out of the application, things can
> really begin to progress!" ;-)
Ha hA! ;-)
There could be some truth in this as well.
But in the long run the users are the most important elements
in the equation.
My proposal is to give each user only one program in order
to perform IO with the RDBMS rather than a suite of three
thousand (eg) executables (each on their desktop or clustered on
an apps server).
It is an approach of simplification by internalisation.
The applications get moved inside the RDBMS and are
referenced by an RDBMS client portal software.
Thanks for the thoughtful dialogue.
Best wishes,
--
Farmer Brown
Falls Creek, OZ
http://www.mountainman.com.au/software
mountain man
news:<NM9i9.35590$g9.1...@newsfeeds.bigpond.com>...
>
> > I agree with your earlier comments about SQL being there at the
> > right time and place. You could probably summarise this by the
> > remark that all computer systems software "evolves" in time.
>
> Whoa... being in the right place at the right time best falls in the
> category of "politics", not technological innovation/evolution.
Hi Nathan, while "politics" obviously is a large element amidst
all the factors that interplay and in combination give rise to the
changes we have witness for example in the last 40 years, who
was there in 1960 that could have predicted the systems and their
specifications that are now available in 2002?
My term "evolution" simply recognises that there are a multitude
of forces at play, beyond any one corporation or individual.
> > Specifically, I see an entire new generation of database application
> > "software" being written _exclusively_ using the RDBMS native
> > utilities (largely stored procedures). The end-point of this evolution
> > is a shift in the location of the (db) applications software
environment.
> >
> > It will disappear from the current desktop/apps server environment
> > external to the RDBMS, and move internal to the RDBMS. It will
> > evolve in this manner because it is far easier to manage two systems
> > software environments than three. For further detail see:
>
> Your suggestions revolves around the assumption that today's DBMSs are
> a capable environment for application development.
I have demonstrated with R&D on MS SQLServer over the last 2 years
that at least this RDBMS provides such a capable environment. I have
done some small amount of research into the connectivity into Oracle
with reasonable success. I have ordered the DB2 CD from IBM Aust
and will be looking at whether DB2 can be so configured.
So while I cannot today demo a working version in Oracle and DB2,
I can certainly provide the demo with SQLServer.
>This is
> unfortunately not the case and is the reason that we have "application
> servers" and such. It is clearly desirable to remove the conceptual
> "middle tier" and reduce overall application complexity. However,
> this layer can only be eliminated by a aptly capable DBMS. Existing
> systems can be preserved, but only as a "storage engine" underlying a
> more capable DBMS.
The capability of the RDBMS software environment has risen steeply
over the last few years (at least with SQLServer). It may well be that
the microsoft product is unique, but my research tells me otherwise.
This capability to which you refer above, has been engineered
out of the native utilities available to the MS environment, and
works well. Perhaps the utilities are different in the other RDBMS
environments, but I will determine that soon.
> I will respond more on your proposed "layers" in another posting...
Thanks.
And best wishes,
Alfredo Novoa
<prfb...@magna.com.au> wrote:
Hi
>> > external to the RDBMS, and move internal to the RDBMS. It will
>> > evolve in this manner because it is far easier to manage two systems
>> > software environments than three. For further detail see:
Yes, but it is also far easier to manage one system software
enviroment than two :-).
>Not quite. It requires the presence of separately addressable
>(from the apps environment) stored procedures within the (R)DBMS.
Triggered procedures or stored procedures are very often needed due
the poor declarative integrity capabilities of SQL DBMS's.
With a 'D' language you can develop a complex business system without
the use of any procedural code.
>More importantly, it requires the software supplier to convert
>all his/her code to the form of (R)DBMS stored procedures (ie: 100%).
I think it would be better replacing the procedural code with the use
of a declarative specification language.
>and having all the source code visible to the end client DBA, etc.
Not necesarily, I know systems where the stored procedure's source
code was deleted after compilation.
>Such solutions have not before been developed commercially before
>because it is an extremely OPEN SOURCE approach, and software
>vendors get terribly skittish about such an animal.
And because the relational model was never implemented until now.
>On the contrary, I am making such a product available and one of the
>more important benefits it has is the ability to be able to rapidly
>develop and maintain client applications.
If you have a generic client application you will be faster deploying
it because you don't have to develop anything.
>Under my proposed arrangement, if the client had contracted
>with a software vendor for the provision of a new software
>package, then the vendor will simply provide the client with
>a database within which reside the database stored procedures
>which define - in totality - the new application (less the portal).
And why not the entire system with the portal?
In a lot of cases it is perfectly possible.
>On face value, to me, it looks
>just plain wrong. My perspective is from that of an IT
>manager who has seen a number of SQL based systems
>that have been engineered to an optimum level of
>automation, including data integrity exception
>management.
But the data integrity is enforced using procedural code, and it is
costly and error prone.
And also you need to write stored procedures for all multitable views
in order to make them updateable. With a real RDBMS all relations are
updateable without aditional effort.
>Perhaps from your perspective it is. It really depends on the
>importance you attach to the mechanism(s) available to address
>the maintenance of database integrity constraints
DBMS's are just for this. If it is not very important what is
important? :-)
>1) 1st software system environment: Hardware OS and net OS
>2) 2nd software system environment: (R)DBMS <<-------|
>3) 3rd software system environment: (db) applications -->|
>
>Environments 1 and 2 and known stable reliable products
>which may have as few as a dozen vendor combinations
>covering more than 95% of all (R)DBMS sites.
Enviroment 2 is very narrow. The only RDBMS I know is Dataphor. And it
uses SQL DBMS's behind the scenes.
Regards
Alfredo
Alfredo Novoa
wrote:
Hi
>> 1) 1st software system environment: Hardware OS and net OS
>> 2) 2nd software system environment: (R)DBMS <<-------|
>> 3) 3rd software system environment: (db) applications -->|
>
>There are many different ways we could draw lines between conceptual
>systems. Calling the "3rd environment" the "application" is bad
>terminology. The sum of all systems that together provide the service
>constitute the software application.
IMO the sum of all components that together provide the service
constitute the software system.
Applications are a part of software systems.
>I presume you are including the user interface in this "3rd
>environment."
In DBMS based systems, applications are usually only user interfaces.
Basically a console replacement.
Alfredo
David Cressey
> all the factors that interplay and in combination give rise to the
> changes we have witness for example in the last 40 years, who
> was there in 1960 that could have predicted the systems and their
> specifications that are now available in 2002?
Vannevar Bush did a pretty good job of outlining a vision of what things
would look like in a
world where information transmission, storage, and processing were
ubiquitous.
While many of the details were missing from his prediction, his vision was
far more specific than, say, the forecasts of Nostradamus.
David Cressey
> RDBMS is a portal to the RDBMS. One small light-footed program
> with absolutely zero application specific code. Consequently, it
> requires zero maintenance when developing using it, because all
> such development, by implicit design, is achieved exclusively by
> harnessing RDBMS stored procedures.
Some people are building systems that use the web browser as the "generic
lightweight client".
If the browser includes a JVM, there is a wide range of application
specific behavior that can be
loaded "just in time". I see some problems, due to web protocol, but
nevertheless...
.
mountain man
Thanks for your comments.
I have given the functionality of "web enablement" much thought
and have in fact developed a version of the application using the
MS .asp platform.
The web browser can be used for the (R)DBMS I/O or not,
as the case may be.
mountain man
news:3d8c66f4...@news.wanadoo.es...
> On Fri, 20 Sep 2002 11:43:58 +1000, "mountain man"
> <prfb...@magna.com.au> wrote:
>
> Hi
>
> >> > external to the RDBMS, and move internal to the RDBMS. It will
> >> > evolve in this manner because it is far easier to manage two systems
> >> > software environments than three. For further detail see:
>
> Yes, but it is also far easier to manage one system software
> enviroment than two :-).
First steps first. ;-)
> >Not quite. It requires the presence of separately addressable
> >(from the apps environment) stored procedures within the (R)DBMS.
>
> Triggered procedures or stored procedures are very often needed due
> the poor declarative integrity capabilities of SQL DBMS's.
>
> With a 'D' language you can develop a complex business system without
> the use of any procedural code.
>
> >More importantly, it requires the software supplier to convert
> >all his/her code to the form of (R)DBMS stored procedures (ie: 100%).
>
> I think it would be better replacing the procedural code with the use
> of a declarative specification language.
>
> >and having all the source code visible to the end client DBA, etc.
>
> Not necesarily, I know systems where the stored procedure's source
> code was deleted after compilation.
>
> >Such solutions have not before been developed commercially before
> >because it is an extremely OPEN SOURCE approach, and software
> >vendors get terribly skittish about such an animal.
>
> And because the relational model was never implemented until now.
>
> >On the contrary, I am making such a product available and one of the
> >more important benefits it has is the ability to be able to rapidly
> >develop and maintain client applications.
>
> If you have a generic client application you will be faster deploying
> it because you don't have to develop anything.
While this may be quite true, at some stage the end client
(whether that is an end organisation, or a developer) will need
to develop -- in little steps -- an application.
At this end user development stage, the process of development
for any one specific task is simply the creation of one or more
stored procedures.
Deployment of this application to the end user is available as soon
as the user re-examines the database (ie: it is automatic, and nothing
is needed to be deployed into the client environment).
> >Under my proposed arrangement, if the client had contracted
> >with a software vendor for the provision of a new software
> >package, then the vendor will simply provide the client with
> >a database within which reside the database stored procedures
> >which define - in totality - the new application (less the portal).
>
> And why not the entire system with the portal?
Perhaps I was not clear. The portal is an integral part of the
arrangement and exists as the user interface to the (R)DBMS.
In theory it is the one program object that replaces the hundreds or
thousands of (application software) objects currently in the your
standard environment 3.
Development of the application occurs within the (R)DBMS
and requires zero client changes to this portal software. Therefore
anyone who develops applications using this methodology need
only develop stored procedures.
These of course are deliverable to the end organisational client
after development in the form of a standard (R)DBMS database.
> In a lot of cases it is perfectly possible.
>
> >On face value, to me, it looks
> >just plain wrong. My perspective is from that of an IT
> >manager who has seen a number of SQL based systems
> >that have been engineered to an optimum level of
> >automation, including data integrity exception
> >management.
>
> But the data integrity is enforced using procedural code, and it is
> costly and error prone.
While you may have such a view of such practice, let me assure you
that it is neither costly nor error prone when done properly. It is not
costly simply because once the checks are established, automation
kicks in and replicates the effect as long as the system runs for zero
cost.
> And also you need to write stored procedures for all multitable views
> in order to make them updateable. With a real RDBMS all relations are
> updateable without aditional effort.
>
> >Perhaps from your perspective it is. It really depends on the
> >importance you attach to the mechanism(s) available to address
> >the maintenance of database integrity constraints
>
> DBMS's are just for this. If it is not very important what is
> important? :-)
>
> >1) 1st software system environment: Hardware OS and net OS
> >2) 2nd software system environment: (R)DBMS <<-------|
> >3) 3rd software system environment: (db) applications -->|
> >
> >Environments 1 and 2 and known stable reliable products
> >which may have as few as a dozen vendor combinations
> >covering more than 95% of all (R)DBMS sites.
>
> Enviroment 2 is very narrow. The only RDBMS I know is Dataphor. And it
> uses SQL DBMS's behind the scenes.
Environment 2 consists of DB2, Oracle, SQLServer and the host
of other (R)DBMS software.
I have no problem with the role Dataphor is assuming in its
endeavor to make RDBMS out of (R)DBMS.
Best wishes,
Alfredo Novoa
<prfb...@magna.com.au> wrote:
>> >Under my proposed arrangement, if the client had contracted
>> >with a software vendor for the provision of a new software
>> >package, then the vendor will simply provide the client with
>> >a database within which reside the database stored procedures
>> >which define - in totality - the new application (less the portal).
>>
>> And why not the entire system with the portal?
>
>Perhaps I was not clear. The portal is an integral part of the
>arrangement and exists as the user interface to the (R)DBMS.
What I meant is that the database may be a good place for the
information which define the user interface.
It is sometimes called database presentation rules.
>> But the data integrity is enforced using procedural code, and it is
>> costly and error prone.
>
>While you may have such a view of such practice, let me assure you
>that it is neither costly nor error prone when done properly.
Well, what I meant is that it is costly and error prone compared to
doing it declaratively.
> It is not
>costly simply because once the checks are established, automation
>kicks in and replicates the effect as long as the system runs for zero
>cost.
But the difficulty is in establishing the checks with procedural code
instead of declarative statements.
>Environment 2 consists of DB2, Oracle, SQLServer and the host
>of other (R)DBMS software.
>
>I have no problem with the role Dataphor is assuming in its
>endeavor to make RDBMS out of (R)DBMS.
Ah, I thougth that (R)DBMS meant relational DBMS. If (R)DBMS means
pseudo RDBMS then I agree :)
Regards
Alfredo
mountain man
> On Sun, 22 Sep 2002 09:44:26 +1000, "mountain man"
> <prfb...@magna.com.au> wrote:
>
> >> >Under my proposed arrangement, if the client had contracted
> >> >with a software vendor for the provision of a new software
> >> >package, then the vendor will simply provide the client with
> >> >a database within which reside the database stored procedures
> >> >which define - in totality - the new application (less the portal).
> >>
> >> And why not the entire system with the portal?
> >
> >Perhaps I was not clear. The portal is an integral part of the
> >arrangement and exists as the user interface to the (R)DBMS.
>
> What I meant is that the database may be a good place for the
> information which define the user interface.
Of course, but usually the database and the user are physically
separated by several layers of systems software and on separate
machines. The portal is the client end of the arrangement.
> It is sometimes called database presentation rules.
Before that it was simply known
as a menu and security arrangement.
> >> But the data integrity is enforced using procedural code, and it is
> >> costly and error prone.
> >
> >While you may have such a view of such practice, let me assure you
> >that it is neither costly nor error prone when done properly.
>
> Well, what I meant is that it is costly and error prone compared to
> doing it declaratively.
Maybe we should compare quotes one day.
> > It is not
> >costly simply because once the checks are established, automation
> >kicks in and replicates the effect as long as the system runs for zero
> >cost.
>
> But the difficulty is in establishing the checks with procedural code
> instead of declarative statements.
Declaritive statements have their place, and limiting conditions
or functionality or implementation. At some point, checks with
procedural code will pay off by catching what falls through the
cracks of the complete implementation of declaritive statements.
The checks are often simple procedure code, easy to write
and maintain. They often save otherwise sophisticated
systems.
> >Environment 2 consists of DB2, Oracle, SQLServer and the host
> >of other (R)DBMS software.
> >
> >I have no problem with the role Dataphor is assuming in its
> >endeavor to make RDBMS out of (R)DBMS.
>
> Ah, I thougth that (R)DBMS meant relational DBMS. If (R)DBMS means
> pseudo RDBMS then I agree :)
I use the series of ascii "(R)DBMS" to mean those
environments currently provided by DB2, Oracle
and SQLServer, etc
The R is bracketted to signify that separate threads
exist for the discussion of relational theory.
> Regards
> Alfredo
Nathan Allan
> My term "evolution" simply recognises that there are a multitude
> of forces at play, beyond any one corporation or individual.
Fair enough. :-)
> > Your suggestions revolves around the assumption that today's DBMSs are
> > a capable environment for application development.
>
> I have demonstrated with R&D on MS SQLServer over the last 2 years
> that at least this RDBMS provides such a capable environment.
Hmmm... I am familiar with SQL Server and would have to strongly
disagree. Imperative programming for SQL Server is done in "Transact
SQL" which is probably one of the worst languages I have ever
encountered. There is a running joke in our office that the only
thing consistent in TSQL is it's inconsistency. TSQL aside, SQL
Server definitely is not capable of enforcing more than basic
integrity constraints (real time, and even WITH triggers).
> The capability of the RDBMS software environment has risen steeply
> over the last few years (at least with SQLServer). It may well be that
> the microsoft product is unique, but my research tells me otherwise.
They add feature after feature, while ignoring fundamental issues.
:-)
Regards,
--
Nathan Allan
Nathan Allan
> At that time, they may well have.
We will have to agree to disagree, because frankly I think you give
today's SQL DBMSs too much credit. They have added fluff here and
there, but for the most part, they are the same sorry creatures. If
you would like to experience this first hand, try building a general
purpose relational DBMS that uses them "underneath." I don't know how
many times I have sighed with frustration over some quirk we were
forced to work around in these systems. And unlike application
specific work arounds, general work arounds are not easy! <rant> All
because someone decided it would be a good idea to prevent "case"
statements from being nested more than 10 levels deep, or that only
two levels of nesting are necessary within correlated sub queries in
the "from" clause. </rant>
> 1) How many lines of code exist external to the RDBMS?
> 2) How many lines of code exist internal to the RDBMS?
> 3) What is the function of the code external to the DB?
I think the point you are trying to make is clear: centralize the
application logic. I think everybody agrees that this is a worthy
endeavor. Accomplishing this without unreasonable compromise is
another thing.
> > How about client enforced constraints, defaults/ID generators,
> > calculated columns, navigation & buffering, multi-table transactional
> > implications, and other such matters? Today's DBMSs provide little
> > help in handling these matters.
>
> Just because we do not seem to have an RDBMS vendor
> who has answered all the above such matters in today's
> world, should I who seek such a solution say to myself:
>
> "Perhaps I will return to the shop tomorrow, and see whether
> such a perfect system, that will have no problems at all, will
> be ready. If it is, then I will buy it. But if it is not, then I will
> wait until it is ready"
>
> Can you imagine the problems of this approach?
> Yet it seems you support it.
Let's look at where I am coming from. My company (Alphora) built a
system that actually solves many of the raised issues and provides
benefits such as those in my original posting. We are not complaining
about the issues (well, maybe a little ;-), we have done something
about them. I agree that waiting for a perfect solution will not get
anyone far. That is why we did what we did. We wanted it. Nobody
had it. So we built it!
> All the problems that people have thrown up in this newsgroup
> and others, and in the planet's stock of comments concerning
> RDBMS software are resolvable in production with workarounds.
Certainly, but the more "around" the workarounds are, the longer
projects take to build and maintain. What we have built can be looked
at as a very elegant workaround because it "lives" with existing
systems. But to the application developer it seems a lot more like a
1st class solution. :-)
> > I think you are seriously down-playing the front-end portion of the
> > application. This portion can easily constitute the majority of the
> > development and maintenance time using today's common methodologies.
> > What you are saying here reads to me as, "the software vendor will
> > deliver a half-completed application." ;-)
>
> The front end of the application which is fully defined within the
> RDBMS is a portal to the RDBMS. One small light-footed program
> with absolutely zero application specific code. Consequently, it
> requires zero maintenance when developing using it, because all
> such development, by implicit design, is achieved exclusively by
> harnessing RDBMS stored procedures.
Many people today take this approach. If the DBMS has the complete
ability to enforce integrity, such procedures are not necessary.
Development of data access procedures en mass is a time consuming,
high maintenance, and limiting task. And those are the good
attributes! :-)
> Through this portal, all organisational users have the application
> presented to them.
Again, I think you underestimate the potential requirements on this
"portal." Your purposed stored procedure solution is more evidence of
this.
> > > > As for integrity, the idea that stored procedures can be run at
> > > > pre-determined intervals to validate data is nothing new, nor is it
> > > > exceptionally clever.
> > >
> > > I have not seen any arguments to the contrary.
> >
> > Here are a few:
> > -Non real-time nature of integrity enforcement (when can you trust
> > your data?).
>
> As often as you run the check. You want to run the check every
> 2.7 seconds, then you can trust it at the end of every 2.7 second
> interval during the business day.
2.7 seconds or even continuous looping is not real-time. Transactions
form the basis of time variance within a database, not an extenal
clock or loop. There is a huge logical difference between the data
being "usually good" and being "always good." I am not just being
nit-picky. The user or system who is attempting modification should
know that the data is bad. The data should never be allowed to be
bad. C. J. Date: "Database design is really all about specifying
integrity constraints! Database design is constraint definition."
> Now I ask you ... provide an alternative approach to have this
> question answered in realtime, (re: a production RDBMS) now,
> today. ;-)
Okay: (www.alphora.com) Version 1.0 has been out since June. 1.1
comes out soon.
> Nathan, this is not even a big IF.
> It is a non-existent IF at them moment.
> And I am used to working in production.
See above... so am I. ;-)
> And yes, as such facilities appear we can certain cancel
> and relax the corresponding data integrity exception checking.
> But until they appear, what is there to be done?
> My response is an EMS.
And I am saying that there IS a solution to these things and it CAN be
done it real time. In fact all of the goals you are aiming for are
accomplished elegantly in Dataphor. For example, we have (and have
had) Windows and Web UIs that are dynamically derived from the
database. They require no data access stored procedures and are
driven from the data model. Check it out... I think you will like it!
> You have no argument from me, IF IT CAN BE DONE.
>
> My point is simply:
> it is not done yet.
I would be very interested in your feedback about Dataphor.
> This indeed would be a service that the RDBMS vendors
> could introduce in the absence of all those other features
> and database services you reference above.
We can do it... and we can still use those systems underneath.
> > There are many different ways we could draw lines between conceptual
> > systems. Calling the "3rd environment" the "application" is bad
> > terminology. The sum of all systems that together provide the service
> > constitute the software application.
>
> While that may be so, in examination of the actual code and
> modules which comprise this software you must admit that
> you would be able to separate those threads which deal with
> specific transactions within each of the above layers.
The reason I harp on this point is because I think it blurs the
picture. The ideal to me draws a THICK line between application
developer concerns and the concerns of the system
programmer/administrator. As is illustrated by the "portal"
discussion, implementation layers don't really matter so long as they
are maintained as part of the system. The implementation should be
transparent to the application developer. The implementation can give
or take layers, switch out subsystems, or be replaced altogether, so
long as the application logic is not affected. The RM provides the
conceptual foundation for this very line.
> The entire 3rd environment can go providing the RDBMS portal
> software is able to be seen as the occupant of that box. If not,
> then envisage an application software environment where the
> number of separate executable programs is one, not many, and
> the entire (db) apps environment is one program - the portal.
Again, we differ. As stated, it doesn't matter how many executables
or layers there are, as long as they form one general implementation
that can readily be brought to a specific state for the purpose of a
specific application. The implementing systems will naturally become
simpler as a byproduct of drawing this logical/physical line more
clearly.
> My proposal is to give each user only one program in order
> to perform IO with the RDBMS rather than a suite of three
> thousand (eg) executables (each on their desktop or clustered on
> an apps server).
Fire up the Dataphor Windows or Web Client. ;-) I am not trying to
push product (well, yes I am) but this is exactly what you are
describing.
> It is an approach of simplification by internalisation.
> The applications get moved inside the RDBMS and are
> referenced by an RDBMS client portal software.
> Thanks for the thoughtful dialogue.
Yes, I think we have identified some of the same problems and have
arrived at similar solutions. I hope you will download and take a
look at what we have. I would be interested in hearing your thoughts.
Sincerely,
--
Nathan Allan