internalization of (generic) application layer software to the database TSQL

[mountain man]

Greetings to all database professionals and laymen,

Let us make a bold assumption that we have developed a software
tool for the SQL Server environment which simply acts as an interface
between an end-user in an organization and the database, through
the exclusive use of stored procedures which are authored by the
organization or by software developers.

All development work at the application software level may thereby
be conducted within SQL, by the development of TSQL stored
procedures and their coordination across an organization.

The question then needs to be asked what are the advantages of this
arrangement and what are the disadvantages. I would appreciate
your comments here, as it is difficult for folk heavily involved (eg: me)
in something to obtain objective opinion otherwise.
Potentially it is possible to construct an entire database application
software package using only TSQL stored procedures and this
tool. When a database backup is conducted not only the data is
backed up but also all the "program development" suite, in the
form of stored procedures (and their scheduling, for example).

One of the advantages from my perspective is that this arrangement
implies the possibility that all software external to the database may
be made redundant (except this tool), and along with it, all the other
redundancies of managing application development (life-cycles) within
the database and then coordinating these changes with software
external the database (particulary loading it to the client environment)

You see, I believe that (for example) any and every application written
in VB, C, etc, etc, etc (external to rdbms) expressly for database software
(ie: rdbms software such as SQL Server) involves a redundancy of data
definitions required ---- once within the VB and once within the db.
I would appreciate any feedback on any of the above issues, and
wish everyone the compliments of the season.

Pete Brown
Falls Creek
NSW
Oz
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~
EDITOR:
BoomerangOutPos t: Mountain Man Graphics, Newport Beach, {OZ}
Thematic Threading: Publications of Peace and Of Great Souls
Webulous Coordinates: http://www.mountainman.com.au
QuoteForTheDay:

"Consciousn ess is never experienced in the plural, only in
the singular. How does the idea of plurality (emphatically
opposed by the Upanishad writers arise at all? .... the
only possible alternative is simply to keep the immediate
experience that consciousness is a singular of which the
plural is unkown; that there *is* only one thing and that
what seems to be a plurality is merely a series of different
aspects of this one thing produced by deception (the Indian
maya) - in much the same way Gaurisankar and Mt Everest turn
out to be the same peak seen from different valleys."

- E. Schrodinger, "What is Life"
--------------------------------------------------------------------

[louis nguyen]

> All development work at the application software level may thereby

be conducted within SQL, by the development of TSQL stored
procedures and their coordination across an organization.

Hi,

I already write 80% of my code in stored procedures. I also use
xp_cmdshell inside SPs for FTP and other DOS commands. So I'm all for
the idea of encapsulating all development work inside TSQL stored
procedures. With the coming of Yukon and the ability to embed C# and
VB.net code directly in SPs, I would probably write even more of my
code inside stored procedures. The one area where I think code
belongs on the client, involves data-entry applications. Here I want
to instantaneously respond to user clicks & inputs. I don't want to
make a round-trip to the server every time the user clicks - so data
validation and instant totals need to remain on the client.

Ramblings. I have a lot invested in the DB backend and web client
paradigm. The web services and windows app paradigm is not something
I really want to explore. The MSSQLSERVER2000 reporting services
looks promising, but I'm somewhat wary as my users are happy and
comfortable going to our current web sites. If the reporting services
gives me the capability to develop something that looks like a web
site, with logins, bread crumbs, hyperlinks, etc then I'll pursue it.
The coming of Asp.net 2.0 is also promising. The new sqldatasource
and gridview tags will save me a lot of coding and allow me to
concentrate on business logic, instead of how to make ado.net work
with asp.net. I guess my major wish is for M$ to evolve asp.net into
a meta language for all web client functionality. If they do that,
I'll gladly brain dump html, style sheets, javascript ... and
concentrate on Tsql, asp.net and C#.

[Erland Sommarskog]

mountain man (hobbit@souther n_seaweed.com.o p) writes:

Potentially it is possible to construct an entire database application
software package using only TSQL stored procedures and this
tool. When a database backup is conducted not only the data is
backed up but also all the "program development" suite, in the
form of stored procedures (and their scheduling, for example).
Might be that the stored procedures are backed up too, but stored
procedures in a database is to be regarded as binaries. The source
should be elsewhere, in a version-control system. (Which may be implemented
on the top of an SQL database.)
You see, I believe that (for example) any and every application written
in VB, C, etc, etc, etc (external to rdbms) expressly for database
software (ie: rdbms software such as SQL Server) involves a redundancy
of data definitions required ---- once within the VB and once within the
db.

And maybe once in the business layer.

And this is not without good reason. If you were a military commander
who were set to defend something important, would you settle on one
single defense line?

Take an example: order registration. The database needs to know that
customer ID must be registred, and this is probably enforced with
a NOT NULL constraint. But the user-interface also needs to know this,
so that it give the user a comprehensible error message. It is not
sufficient to slam "Attempt to insert NULL in column...".

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[mountain man]

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...

mountain man (hobbit@souther n_seaweed.com.o p) writes:

Potentially it is possible to construct an entire database application
software package using only TSQL stored procedures and this
tool. When a database backup is conducted not only the data is
backed up but also all the "program development" suite, in the
form of stored procedures (and their scheduling, for example).
Might be that the stored procedures are backed up too, but stored
procedures in a database is to be regarded as binaries. The source
should be elsewhere, in a version-control system. (Which may be

implemented on the top of an SQL database.)

Of course.

You see, I believe that (for example) any and every application written
in VB, C, etc, etc, etc (external to rdbms) expressly for database
software (ie: rdbms software such as SQL Server) involves a redundancy
of data definitions required ---- once within the VB and once within the
db.

And maybe once in the business layer.

Absolutely, and then again possibly in an executive information
system of some format spanning everything above. You get
the drift.
And this is not without good reason. If you were a military commander
who were set to defend something important, would you settle on one
single defense line?

Really, it would entirely depend upon the nature of the battle and
what resources the commander had at his or her disposal.

For example, when faced with a superior foe, one's best chance
of survival might necessitate putting all the eggs into the one basket
and retreating to an emminently defensible location. (Helm's Deep ;-)
I think the reason for the redefinition of the data in multiple layers
mapped onto the database has been the historical development of
the technology --- not because it is any more secure or defensible.
Take an example: order registration. The database needs to know that
customer ID must be registred, and this is probably enforced with
a NOT NULL constraint. But the user-interface also needs to know this,
so that it give the user a comprehensible error message. It is not
sufficient to slam "Attempt to insert NULL in column...".

Data validation is obviously the weak link in the model I have
put forward for discussion, however the problems associated
with this issue need to be isolated from some of the benefits
to be gained by operating all development within one environment.

The user interface can be re-arranged as a data staging area.
Data validation can be engineered by multi-process steps and
managed as a separate task.

I appreciate the pin-point response.

Compliments of the season,


Pete Brown
Falls Creek
OZ
--------------------------------------------------------------------
BoomerangOutPos t: Mountain Man Graphics, Newport Beach, {OZ}
Thematic Threading: Publications of Peace and Of Great Souls
Webulous Coordinates: http://www.mountainman.com.au

QuoteForTheDay:

"A skillful soldier is not violent;
An able fighter does not rage;
A mighty conqueror does not give battle;
A great commander is a humble man.

You may call this pacific virtue;
Or say that it is mastery of men;
Or that it is rising to the measure of God,
Or to the stature of the ancients.

- Lao Tzu (about 300BC) - The Way of Life
http://www.mountainman.com.au/tao_7_9.html
--------------------------------------------------------------------

[mountain man]

"louis nguyen" <lo************ @hotmail.com> wrote in message
news:b0******** *************** **@posting.goog le.com...

All development work at the application software level may thereby
be conducted within SQL, by the development of TSQL stored
procedures and their coordination across an organization.
Hi,

I already write 80% of my code in stored procedures.

This is often documented as an exceedingly efficient practice
and for good reason due to the efficiency of the RDBMS software
layer dealing directly with the RDBMS software layer rather than
any other external software environment (layer).

One might easily make the hypothesis that there is a migration of
code from the environment external to the database, to the code
resident internal to the database. That is, from an historical and
developmental perspective.

This thread concerns the hypothetical end-point of such a migration
wherein all code relevant to the application is internal --- ie: stored
procedures.
I also use
xp_cmdshell inside SPs for FTP and other DOS commands. So I'm all for
the idea of encapsulating all development work inside TSQL stored
procedures. With the coming of Yukon and the ability to embed C# and
VB.net code directly in SPs, I would probably write even more of my
code inside stored procedures.
I will await the implementation to comment on this aspect.

The one area where I think code
belongs on the client, involves data-entry applications. Here I want
to instantaneously respond to user clicks & inputs. I don't want to
make a round-trip to the server every time the user clicks - so data
validation and instant totals need to remain on the client.

Data entry and its validation processes ---- the "user interface" --- is
indeed the traditional bastion of "client side code" due to its historical
development in that environment.

However, we both know that every single user interface screen of code
connected to an application represents a redundancy of definitions which
have already been categorically defined within SQL.

An application is consistent of a suite of say 999 user-interface programs
which connect in different manners to a great range of the data in an
extensive and comprehensive database.

There are 999 sets of redundant database definitions requiring attentive
and costly coordinated review possibly every new build. This is alot of
redundancy.
My point here is simply pointing out the (known) redundancies existent
in the current technological development associated with rdbms and
asking what would it be like operating under a methodology of zero
redundancies, where all the code is TSQL, with the exception of
some user-interface rdbms portal software which only deals in
allowing the user to effectively select stored procedures to be run,
or returning to the user the data set output of their execution.
Ramblings. I have a lot invested in the DB backend and web client
paradigm. The web services and windows app paradigm is not something
I really want to explore. The MSSQLSERVER2000 reporting services
looks promising, but I'm somewhat wary as my users are happy and
comfortable going to our current web sites. If the reporting services
gives me the capability to develop something that looks like a web
site, with logins, bread crumbs, hyperlinks, etc then I'll pursue it.
The coming of Asp.net 2.0 is also promising. The new sqldatasource
and gridview tags will save me a lot of coding and allow me to
concentrate on business logic, instead of how to make ado.net work
with asp.net.

Without beating around the bush, it is really the huge efficiencies
and rich arrays of controls made available in the modern rdbms
to effectively and automatically manage data that make the modern
database environment a relative haven of well-greased bearings.

Outside of this environment there is great thrashing about of software
development driven a great deal by the marketing teams. Change
is piecemeally managed or extra change management utility software
applications are employed to track change.

The management of change in the IT environment for a few decades
has driven me to seek a theoretical solution to the enormous problems
associated with coordinating change external and internal to the
database.

My conclusion is all this change is being made far more difficult to
manage due to the fact that the code (ie: the quanta of business
intelligence) is in fact being formally defined across two system
software environments. (ie: one in the client (VB) code,
and once again in the database, repeatedly throughout the
entire extent of the code)

If the base of operations can be reduced to one environment
(ie: the database) then what is now known as the client application
environment will fall away from future technology like the booster
rocket in an outbound shuttle rocket.
I guess my major wish is for M$ to evolve asp.net into
a meta language for all web client functionality. If they do that,
I'll gladly brain dump html, style sheets, javascript ... and
concentrate on Tsql, asp.net and C#.

From my perspective, if M$ bundled some form of SQL Server
portal software which enabled all levels of database related ppl
to write applications in stored procedures, then there exists the
outlandish possibility that every single diverse form of application
that can be written for a database could in theory be run on the
one machine using the same user interface to many SQL databases.

It could unify the application software layer by subsuming it in
entirety within the database software layer. This is about the
theory of the benefits of a model and operations with a smaller
number of moving parts.

At any rate, I appreciate your response and was interested
to read your approach to this, and your plans.

Compliments of the season.


Pete Brown
Falls Creek
OZ
--------------------------------------------------------------------
BoomerangOutPos t: Mountain Man Graphics, Newport Beach, {OZ}
Thematic Threading: Publications of Peace and Of Great Souls
Webulous Coordinates: http://www.mountainman.com.au

QuoteForTheDay:

"Nothing is rich
But the inexhaustible wealth of nature.
She shows us only surfaces,
But she is a million fathoms deep"

- Ralph Waldo Emerson
--------------------------------------------------------------------

[Erland Sommarskog]

mountain man (hobbit@souther n_seaweed.com.o p) writes:

However, we both know that every single user interface screen of code
connected to an application represents a redundancy of definitions which
have already been categorically defined within SQL.

An application is consistent of a suite of say 999 user-interface programs
which connect in different manners to a great range of the data in an
extensive and comprehensive database.

There are 999 sets of redundant database definitions requiring attentive
and costly coordinated review possibly every new build. This is alot of
redundancy.

Writing applications is not about composing beautiful structures with
minimal redudancy, but to create applications that meets business needs,
and a standard business need is that the application provides compre-
hensible error message in case of user errors.

There are just too many programmers out there who fail to understand this.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[mountain man]

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...

mountain man (hobbit@souther n_seaweed.com.o p) writes:

However, we both know that every single user interface screen of code
connected to an application represents a redundancy of definitions which
have already been categorically defined within SQL.

An application is consistent of a suite of say 999 user-interface programs which connect in different manners to a great range of the data in an
extensive and comprehensive database.

There are 999 sets of redundant database definitions requiring attentive
and costly coordinated review possibly every new build. This is alot of
redundancy.
Writing applications is not about composing beautiful structures with
minimal redudancy, but to create applications that meets business needs,
and a standard business need is that the application provides compre-
hensible error message in case of user errors.

I agree with your second claim, but cannot understand why you
cannot also institute both claims simultaneously. That is, granted
that the business application mandates integrity constraints, and
sometimes at various levels, if these are catered for then one is
bound to concientiously consider redundancies.

The minimisation of redundancies is imo a task relegated to the management
and coordination of the business software development cycles,
perhaps at the level of the IT manager of that business.

It might certainly not be a task for some appointed programmer,
but the existence of these redundancies - you must admit (?)---
represents an expense to the funding business that is directly
proportional to the number of redundancies.

There are just too many programmers out there who fail to understand this.

My aim is to engineer a tool by which any manner of application software
components can be quickly and easily created by TSQL programmers
using stored procedures alone.

Your earlier points concerning data validation are a key issue when first
examining this methodology I am proposing, and I will address them in
more depth elsewhere.

Thanks,


Pete Brown
Falls Creek
Oz

[mountain man]

Hi Loius,

Here is a separate response on two separate issues central
to this thread (thanks for the dialogue) ....
"louis nguyen" <lo************ @hotmail.com> wrote in message
news:b0******** *************** **@posting.goog le.com...

All development work at the application software level may thereby
be conducted within SQL, by the development of TSQL stored
procedures and their coordination across an organization.
Hi,

I already write 80% of my code in stored procedures. I also use
xp_cmdshell inside SPs for FTP and other DOS commands. So I'm all for
the idea of encapsulating all development work inside TSQL stored
procedures. With the coming of Yukon and the ability to embed C# and
VB.net code directly in SPs, I would probably write even more of my
code inside stored procedures.

My object is to write 100% of code in stored procedures.
If this is achieved, then all client-side code will be obviated
as will its change-management costs --- which are considerable
to any given organisation in todays world.

The headaches associated will change management will not
be removed, however its domain will be moved from where
it is at the moment - in two software layers (rdbms & apps),
to one unified software layer (rdbms). This is simpler.
The one area where I think code
belongs on the client, involves data-entry applications. Here I want
to instantaneously respond to user clicks & inputs. I don't want to
make a round-trip to the server every time the user clicks - so data
validation and instant totals need to remain on the client.

Here I would like to say to you --- what if your SQL Server network
and database were given the sufficient resources whereby the roundtrip
to the server is "quite acceptable"?

Also, is not the database the final authority on data validation and
instant totals? Consequently, how are you going to ultimately avoid
database IO to get updated data?

Thanks again for your thoughts.


Pete Brown
Falls Creek
OZ

[mountain man]

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...

mountain man (hobbit@souther n_seaweed.com.o p) writes:
....[trimmed]...

You see, I believe that (for example) any and every application written
in VB, C, etc, etc, etc (external to rdbms) expressly for database
software (ie: rdbms software such as SQL Server) involves a redundancy
of data definitions required ---- once within the VB and once within the
db.

Take an example: order registration. The database needs to know that
customer ID must be registred, and this is probably enforced with
a NOT NULL constraint. But the user-interface also needs to know this,
so that it give the user a comprehensible error message. It is not
sufficient to slam "Attempt to insert NULL in column...".

What if the user interface queried the database and provided this
information
from a comprehensible error message table? Or better yet gave the option
to enable workflow screens for immediate customer registration.

Are you talking about saving database IO in reading the vendor table
and an error table?
Thanks for the dialogue.


Pete Brown
Falls Creek
Oz

[Erland Sommarskog]

mountain man (hobbit@souther n_seaweed.com.o p) writes:

What if the user interface queried the database and provided this
information from a comprehensible error message table?
Configuring such a table is a major task, and not even a wise move. Just
because the database complains about NOT NULL violation, does not mean
that the user forgot to input the information. In stead the problem was
a bug somewhere in the food chain that mutilated a variable.
Or better yet gave the option to enable workflow screens for immediate
customer registration.
You mean wizards? Bang! Here, I just laid 150 new customers that you
have to register. It should be done by lunch. I promise you, you don't
want a wizard. You want a form that permits you to work fast, and which
informs you if you miss something.
Are you talking about saving database IO in reading the vendor table
and an error table?

I'm talking about user experience.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp