- From: Dmitry Turin <html60@narod.ru>
- Date: Tue, 6 Nov 2007 11:32:33 +0200
- 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 UTC