List of articles   Terminology   Choose language


Introduction into
Tree Manipulation Language


Foreign key creates tree

Fields of two tables are bound by foreign key, and user enters identical meanings into bound fields during filling database. So there is no necessity to specify these relations in request. Such comfort opens possibility to build tree-type request like the following

tablename1.tablename2.tablename3.tablename4
tablename1.*
It's unimportant, following section of tree is parental table or branch table - it's necessary only relationship of two tables.

First is parental table, then branch table

Let preceding section of tree is parental table (table "a"), and next section is branch table (table "b"), refering to primary key of parental table. Let there are several records in branch table, all records refer to one parental record - this means, that next level of tree contains several elements. We shall name section of tree, consisting of several records of branch table, as set. We list names of tables to extract tree - parental table, then branch table.

DDL TML output from DBMS
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);
a.b.c
<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>
If branch table refers by two own fields to two different records of parental table, then it' necessary to specify name of corresponding field of branch table in TML-request after name of branch table through sign slash "/". We shall name such specification of field in section as determination of section. If determination is not specified, then all branch records will be extracted.
DDL TML output from DBMS
create table a (
  id   num      primary key,
  data float
);
create table b (
  id   num      primary key,
  ref1 num      references a(id),
  ref2 num      references a(id),
  data float
);
create table c (
  id   num      primary key,
  lnk1 num      references b(id),
  lnk2 num      references b(id),
  data float
);
insert into a values (1,        12.3);
insert into a values (2,        23.4);
insert into b values (10, 1, 2, 34.5);
insert into b values (20, 1, 2, 45.6);
insert into b values (30, 1, 2, 56.7);
insert into b values (40, 1, 2, 67.8);
insert into c values (100,10,20,78.9);
insert into c values (101,10,20,89.1);
insert into c values (200,30,40,91.2);
insert into c values (201,30,40,88.8);
a.b/ref1.c/lnk1
<a     id=1           data=12.3>
  <b   id=10  ref2=2  data=34.5>
    <c id=100 lnk2=20 data=78.9/>
    <c id=101 lnk2=20 data=89.1/>
  </b>
  <b   id=30  ref2=2  data=56.7>
    <c id=200 lnk2=40 data=91.2/>
    <c id=201 lnk2=40 data=88.8/>
  </b>
</a>

First is branch table, then parental table

Let preceding section of tree is branch table (table "a"), and next section is parental table (table "b"). We shall name section of tree, consisting of record of parental table, as relay-race. For extraction tree we enumerate the name of the tables - first affiliated, then parental. We list names of tables to extract tree - branch table, then parental table.

DDL TML output from DBMS
create table a (
  id   num      primary key,
  ref  num      references b(id),
  data float
);
create table b (
  id   num      primary key,
  link num      references c(id),
  data float
);
create table c (
  id   num      primary key,
  data float
);
insert into  values (100,    34.5);
insert into b values (10, 100,23.4);
insert into a values (1,  10, 12.3);
a.b.c
<a     id=1   data=12.3>
  <b   id=10  data=23.4>
    <c id=100 data=34.5/>
  </b>
</a>
If branch table refers by two own fields to two different records of parental table, then it' necessary to specify name of corresponding field of branch table in TML-request after name of branch table through sign slash "/". Thus table and its refering field are specified through slash in one section, instead of in different sections. We shall name such specification of field in section also as determination of section. If determination is not specified, then all parental records will be extracted.
DDL TML output from DBMS
create table a (
  id   num      primary key,
  ref1 num      references b(id),
  ref2 num      references b(id),
  data float
);
create table b (
  id   num      primary key,
  lnk1 num      references c(id),
  lnk2 num      references c(id),
  data float
);
create table c (
  id   num      primary key,
  data float
);
insert into  values (201,        78.9);
insert into  values (200,        67.8);
insert into  values (101,        56.7);
insert into  values (100,        45.6);
insert into b values (20, 200,201,34.5);
insert into b values (10, 100,101,23.4);
insert into a values (1,  10, 20, 12.3);
a/ref1.b/lnk1.c
<a     id=1   data=12.3>
  <b   id=10  data=23.4>
    <c id=100 data=45.6/>
  </b>
</a>

Bear's ear

Bear's ear is foreign key, refering to primary key of the same table. We shall always consider several consecutive relay-races, created by bear's ear, as one section, and we shall name this section as list.

It's impossible to find by output data (xml), is section set or list in database.

create table a (
  id   num      primary key,
  ref  num      references a(id),
  data float
);

Foreign key from several fields

If determination is not used, then foreign key, consisting of several fields, bring nothing new. All refering fields of foreign key must be listed through sign of multiplication at use of determination, as in example below.

a.b/b1*b2*b3.c

Aggregates

Tree's model of data (above relational model) have so consequence, that aggregate in it will be also tree's aggregate: temporary variable, in which value of aggregate will be accumulated, can be in any level of hierarchie of tree, in any quantity of places. It means, that it's necessary to put temporary variable not in aggregate (as in SQL), but in models of data. Such "temporary" field don't exist really, and we shall mark them as "fictional" (but they are accessible for writing, unlike fields with mark 'secondary'). So TML touches DDL a little.

create table a {
  a1 int,
  a2 int fictional,
  ...
}

Input-output of data

Both output, and input of data is most natural as XML-text, because it is accessable for man to direct perception and editing (unlike binary formats). For purpose of input and output we shall suppose, that words "record" (of DBMS) and "tag" (of XML) are synonyms, and that words "field" and "attribute" are synonyms too. For example, expressions "enclosed record" and "enclosed table" are absolutely lawfully.

It's desirable to choose number of TCP-port, on which DBMS sits, and type of protocol so, that at least one of possible client of DBMS could communicate with it without middle-ware. The most widely-used protocol from all protocols (http, ftp, smtp, pop3, etc) is HTTP as used by browser, so TML4 will take and send xml-data through it. Picture can be value of field, but its URL is sent at TML-request as value of field for separate HTTP-request of browser instead of picture.

User can send to remote and local database from command line of OS (HTTP will ask login).

c:/dir>  xml2http.exe remote.database.com username password a.xml

c:/dir>  xml2http.exe 127.0.0.1           username password a.xml
Database can send into other remote database from trigger or timer (XML allows to convert data from one DBMS into other DBMS easy; if fields username and password of table
sys are equal null, then database sends as 'anonymous')
sql>     update sys set
           ral="database.remote.com",
           username="tomson",
           password="my_pwd";
sql>     a.b.c ;
sql>     update sys set
           ran="101.102.103.104",
           username="tomson",
           password="my_pwd";
sql>     a.b.c ;

Program-terminal

User can send from command line of program-terminal all,what trigger or timer sends; program-terminal is: 'SqlPlus.exe' for Oracle, 'PgSql.exe' for Postgres, 'iSql.exe' for InterBase, etc. In each beginning transaction (after connection with database or after commands 'commit' or 'rollback'), fields 'ran' and 'ral' of table 'sys' already point to location of program-terminal itself, so command

sql>     a.b.c ;
been giving first after connection, return data to program-terminal. In dependence of adjustments, Program-terminal can:

Alternation of DML- and TML-code

DML- and TML-code and data can alternate: if expression begins

For example

CREATE view x as select name, sum(salary) from employee group by name;
x ;

Resume

Thus discriminating particularities of Tree Manipulation Language are:



Dmitry Turin



List of articles   Terminology   Choose language