Re: Gigantoredesignorrific changes to the Database API

Ian,

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>    });

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

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

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

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

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

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

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

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

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

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

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

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


  
[1]
insert into tab (fld) values ('
  <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>
');

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

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

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



Dmitry Turin
SQL5      (5.7.0)  http://sql50.euro.ru
HTML6     (6.5.0)  http://html60.euro.ru
Unicode7  (7.2.1)  http://unicode70.euro.ru
Computer2 (2.0.2)  http://computer20.euro.ru

Received on Monday, 5 November 2007 14:29:46 UTC