- From: Dmitry Turin <html60@narod.ru>
- Date: Mon, 5 Nov 2007 16:30:05 +0200
- To: public-html@w3.org
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