W3C home > Mailing lists > Public > public-html@w3.org > November 2007

Smoothness between rational and xml (was: Gigantoredesignorrific changes to the Database API)

From: Dmitry Turin <html60@narod.ru>
Date: Tue, 6 Nov 2007 11:32:33 +0200
Message-ID: <9112958390.20071106113233@narod.ru>
To: public-html@w3.org

Good day,

Any wishing (anybody) can forward mentioned letter to any mailing
lists and to any persons.

IH>> http://www.whatwg.org/specs/web-apps/current-work/multipage/section-sql.html
IH>>    var db = openDatabase('test', '');
IH>>    db.transaction(function (tx) {
IH>>      tx.executeSql('SELECT COUNT(*) AS count FROM t WHERE id = ?', [id],
IH>>        function (tx, results) {
IH>>          if (results.rows[0].count == 0)
IH>>            tx.executeSql('INSERT INTO t (id, c) VALUES (?, ?)', [id, data]);
IH>>          else
IH>>            tx.executeSql('UPDATE t SET c = ? WHERE id = ?', [data, id]);
IH>>        });
IH>>    });

DT> I have several proposals inside SQL, maybe it will be interesting for you.
DT> I propose to use XPath inside SQL with the following agreements:

DT> a/@a1
DT>   field "a1" in table "a" or attribute "a1" in XML-element "a"
DT> @a1/@b2
DT>   rational field "b2" in rational field "a1" (created by "row")
DT> a/b
DT>   table or XML-element "b", enclosed into table or XML-element "a"
DT> @a1/b
DT>   XML-element "b" in rational field "a1"

DT> So XPath unify rational table and xml-element into some tabment (TABle-eleMENT),
DT> and rational field and xml-attribute into some fattrib (Field-ATTRIBute).
DT> I also propose XTree (a.b.c), similar to XPath (a/b/c).
DT> Thus SQL-operations between rational and xml will be much smoother.
DT> For example, inserting from rational into xml and vise verse:
DT>   insert into tab (fld) values ('<tag>...</tag>');
DT>   insert into tab (fld) select a.b.c;             -- executed as [1]

DT>   insert into tab values ('<tag>...</tag>');      -- no field after 'tab'
DT>   insert into tab select a.b;                     -- executed as [2]
  
DT> inserting into xml and extraction from it
DT>   insert into tab/@fld/k/m/n values ('<tag>...</tag>');
DT>   insert into tab/@fld/k/m/n select a.b.c;

DT>   insert into tabname (field) select tab/@fld/k/m/n/a.b.c;

DT> updating of xml-attribute and xml-content (if to designate content as @@)
DT>   update tab set @fld/p/q/r/@r1=( select a.b.c                );
DT>   update tab set @fld/p/q/r/@r1=( select t/@field/k/m/n/a.b.c );
DT>   update tab set @fld/p/q/r/@r1='<a>...</a>';

DT>   update tab set @fld/p/q/@@=( select a.b.c                );
DT>   update tab set @fld/p/q/@@=( select t/@field/k/m/n/a.b.c );
DT>   update tab set @fld/p/q/@@='<a>...</a>';

DT> deleting
DT>   delete from tab/@fld/k/m/n;

DT> We also can specify predicates
DT>   where @fld/k/m/n/@n1 =  5;
DT>   where @fld/k/m/n/@n1 in (select a1 from a);
DT>   where @fld/k/m/n/@@  =  '<tag>...</tag>';
DT>   where @fld/k/m/n     in (select a/b/n);

DT> permissions
DT>   grant  insert on tab/@fld/k/m/n to   UserName;
DT>   revoke delete on tab/@fld/k/m/n from UserName;

DT> and triggers
DT>   create trigger TriggerName for tab/@fld/k/m/n
DT>   after insert as begin
DT>     ...
DT>   end;;

DT> More detail is in
DT> http://sql50.euro.ru/site/sql50/en/author/sql-xml_eng.htm


DT> [1]
DT> insert into tab (fld) values ('
DT>   <a     id=1   data=12.3>
DT>     <b   id=10  data=23.4>
DT>       <c id=100 data=56.7/>
DT>       <c id=101 data=67.8/>
DT>     </b>
DT>     <b   id=20  data=34.5>
DT>       <c id=200 data=78.9/>
DT>       <c id=201 data=89.1/>
DT>     </b>
DT>     <b   id=30  data=45.6>
DT>       <c id=200 data=91.2/>
DT>     </b>
DT>   </a>
DT> ');

DT> [2]
DT> --request is equivalent to the DML
DT> insert into tab values ('
DT>   <a        data=12.3>
DT>     <b      data=23.4>
DT>     <b      data=34.5>
DT>     <b      data=45.6>
DT>   </a>
DT> ');

DT> --at condition of following DDL
DT> create table a (
DT>   id   num      primary key,
DT>   lnk  num      references tab,
DT>   data float
DT> );
DT> create table b (
DT>   id   num      primary key,
DT>   ref  num      references a(id),
DT>   data float
DT> );

DT> --request is executed so
DT> insert into tab values (1);
DT> insert into a   values (10, 1, 12.3);
DT> insert into b   values (101,10,23.4);
DT> insert into b   values (102,10,34.5);
DT> insert into b   values (103,10,45.6);


Dmitry Turin
HTML6     (6.5.0)  http://html60.euro.ru
SQL5      (5.7.0)  http://sql50.euro.ru
Unicode7  (7.2.1)  http://unicode70.euro.ru
Computer2 (2.0.2)  http://computer20.euro.ru
Received on Tuesday, 6 November 2007 09:32:14 GMT

This archive was generated by hypermail 2.2.0+W3C-0.50 : Wednesday, 9 May 2012 00:16:09 GMT