Dmitry Turin wrote:
you are proposing is to ... break the relational model of SQL
No. I'm proposing to add new thinking about relational model
(primary i want to write "to add new view", but i remember, that
"view" is reserved word in SQL :) ).
Сonvince yourself, please:
create table a (
id num primary key;
data float;
);
create table b (
id num primary key;
ref num references a(id);
data float;
);
create table c (
id num primary key;
link num references b(id);
data float;
);
insert into a values (1, 12.3);
insert into b values (10, 1, 23.4);
insert into b values (20, 1, 34.5);
insert into b values (30, 1, 45.6);
insert into c values (100,10,56.7);
insert into c values (101,10,67.8);
insert into c values (200,20,78.9);
insert into c values (201,20,89.1);
insert into c values (300,30,91.2);
My suggest is "a.b.c"-request. Output is
<a id=1 data=12.3>
<b id=10 data=23.4>
<c id=100 data=56.7/>
<c id=101 data=67.8/>
</b>
<b id=20 data=34.5>
<c id=200 data=78.9/>
<c id=201 data=89.1/>
</b>
<b id=30 data=45.6>
<c id=200 data=91.2/>
</b>
</a>
I wrote about that in
http://sql4.by.ru/site/sql40/en/author/introduction_eng.htm
http://sql4.by.ru/site/sql40/en/author/determination_eng.htm
to make it look more like XML
Understand: XML is not model of data (in my suggest), XML is format of output.
Can you offer better format for output of tree?
If you can, write please, i listen.
Your format seems to have thrown away all type information. How do I
know with the "id=200" that 200 is an int rather than a byte or string?
Also, how do we add the new table?
CREATE TABLE d (
aref num references a,
bref num references b,
cref num references c
);
because of some special purpose application
Reception of tree from database is really often case.
Well it's sometimes the case. I'm not sure you've given any evidence
that *most* (or even many) accesses to a database want to retrieve a
simple tree. And only a tree - presumably your alternative access method
doesn't let me grab "nets" (directed graphs, possibly cyclic).
For example, open several schemes of database (in Erwin, in
RationalRose, in other modeller) - scheme is net (count).
So - not a tree?
After that, look at source of server programs, servicing this databases.
These programs get part of net (not part of one table!, not part of
joined tables! not part of one view!) and put it into program-visualizer.
Also not trees (necessarily)
In back direction, server program get tree (records for several tables, not for
one table!)
and put it into database.
I'm not sure that anyone is clear why you just don't write this as a
simple php/perl/ruby/whatever script? There are libraries that will
output XML for you in most of these I'd suspect, and if not it's just an
afternoon's work.
I mean, your application is written in *some* language, isn't it?
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
