|
|
Posted by steve on 11/23/07 10:18
On Nov 21, 6:34 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
Hello Ed,
>Add to this the idea of type where each result is a different type
>In other words, TABLE (X INT, Y INT) is a different variable type
>from TABLE (M VARCHAR(15), N VARCHAR(15))?
Exactly. Think of sql strings. This table, TABLE(TABLE (M VARCHAR(15),
N VARCHAR(15)),
is a differnt type than TABLE (N VARCHAR(16), N VARCHAR(16))! This
means that we couldn't compare the two and undermines real relational
division. To declare how many characters in a string is clearly the
opposite of what the relational idea of data independence is all
about. Relationally there can only be a 'string' type having
absolutely nothing to do with its storage characteristics. And this
is the same idea in any programming language. This is just one
manifestation of how sqls design ignores the concept of a strong type.
LINQ is an attempt to hide the fact that:
DECLARE @N VARCHAR(5),@M VARCHAR(6),@P VARCHAR(5),@Q VARCHAR(6)
represents 4 different types. This is but one simple form of the idea
that sql guarantees impedence mismatch! And having the choice of
changing the database or the access to it, MS chose access to the
database (LINQ). The sql community seems to not consider how bizarre
and confounding things like this look to developers coming to sql for
the first time. It must be force of habit blinding a more critical
look at how things are. There are so many that sql defeats the idea of
strong types that it would be better and easier to build a new
database system for application development. And have sql available
for everything else:)
>What if the data you want to return is not multiple scalars, but
>rather multiple tables? Upon reflection, I suppose tables could
>be nested in this model, i.e. you can return
> TABLE(T1 TABLE(X INT, Y INT), T2 TABLE(M VARCHAR(15), N VARCHAR(15)))
The relational database emphasizes types to define structures that can
be used to model processes, ie.
create table T1
{
A:Integer,
B:String,
T:row{X:Integer,Y:String},
S:row{B:Integer,C:list(String)}
key{A}
};
This is possible thru system provided types and user defined types.
It's also supported thru explicit conversion processes between one
particular type and another. The foundation to support these
constructs is unique to a relational system and does not exist in sql.
Whether a specific struture makes logical sense and whether it's
supported is another matter. Tables within tables is open to question.
Even if the system supports it would it make sense or would another
type of structure be more appropriate? This question goes to the edges
of a relational system and I'm afraid I can't do it justice here:)
>This would allow bad developers to commit the common 'a,b,c' 1NF
>violation in a whole new way, but then bad developers can screw
>things up in any language.
The view that strings like 'a,b,c' violate the idea of the atomicity
of a column in an sql table is a direct result of sql's lack of types
and lack of relationships between types. There is no violation of any
kind in a relational system because the string can be stored as
a single value of a column retaining the concept that there individual
elements involved. It would simply be stored as a 'list' type.
For example column B of table TableList takes a comma delimited string
and splits it into a list type.
create table TableList
from
table
{
row{1 A,'A,B,C'.Split({','}) B},
row{2,'D, E , F, G'.Split({','})},
row{3,'H,I,J,K'.Split({','})}
};
The table definition of TableList is:
create table TableList
A:Integer,
B:list(String),
key{A}
For each row of the table column A can be retrieved and the individual
items of column B, the list, are availiable.
Select the value of A and the value of the 1st item in the
list(B).
select TableList {A,B[0] ListItem1};
A ListItem1
- ---------
1 A
2 D
3 H
Directly address the 1st item in the list of A=2.
select TableList[2].B[0];
D
Directly address the last item in the list of A=3.
select TableList[3].B[TableList[3].B.Count()-1];
K
Get column B for the row where A=1 and convert the list into a table.
select ToTable(TableList[1].B,'Str','Index');
or
select ToTable( (TableList where A=1 over{B})[].B, 'Str','Index');
Str Index
--- -----
A 0
B 1
C 2
It's types that a relational system guarantees integrity for and high
level operators that allow the explicit conversions between that
developers should have for application development. And this is the
same idea the MS net team calls 'functional programming' which is what
they developed LINQ for. But a relational system 'is' functional
programming!:)
>bad developers can screw things up in any language.
Sure but application development with sql has a tendency to make
anyone a nitwit at some time or other. I'm for less nitwits :)
>> 'where' and 'having').
>I'd guess these are both for efficiency.
GROUP BY was added after the original SELECT but instead of
redesigning the language they saw no problem with leaving in two
constructs that do the same thing! Ever time I see an MS paper on
'best practices' I have got to laugh:)
On table variables:
>But you agree that (1) it has some features of variables, and (2) it
>could reasonably be extended to have more features of variables?
I don't think MS could lock its developer army in a hotel and tell
them to make sql a little more relational:) They have two choices.
Either buy a relational system (like D4) or start from the ground up
to
develop one. The gulf between a relational system and sql is too great
to try to simply make changes in sql server. Which ever major vendor
does either will 'own' application development :)
>A lot of people find SQL pretty straightforward, especially in this
>newsgroup. Your choice of (pseudo-)syntax will make a difference to
>them. (You might get different responses from a newsgroup focusing
>on front-end programming languages, especially if they already
>resemble Pascal as D4 seems to do.)
Sure at least a dozen people who write books and articles find sql
straightforward:) For the rest I'd like to at least see a choice for
them. Again, I hope some can get beyond just syntax to grasp what
a relational system would offer.
best,
steve
www.beyondsql.blogspot.com
Navigation:
[Reply to this message]
|