1 / 104

SQL :2008

SQL :2008. Dmitry Turin sql 2008 .euro.ru. Web integration. Output as XML. <tab id=3 data=12.3> <tab id=7 data=23.4> <tab id=10 data=34.5> <tab id=25 data=45.6> <tab1 data2=3 data1=12.3> <tab1 data2=7 data1=23.4> <tab1 data2=10 data1=34.5>

mahina
Télécharger la présentation

SQL :2008

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL:2008 Dmitry Turin sql2008.euro.ru

  2. Web integration

  3. Output as XML <tab id=3 data=12.3> <tab id=7 data=23.4> <tab id=10 data=34.5> <tab id=25 data=45.6> <tab1 data2=3 data1=12.3> <tab1 data2=7 data1=23.4> <tab1 data2=10 data1=34.5> <tab1 data2=25 data1=45.6> <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> <?xml-stylesheet type="text/xsl" href="a.xsl"?>' select @id, @data from tab; select tab2/@data2, tab1/@data1 from tab1, tab2 where tab2/@fld2=tab1/@fld1; select * from a.b.c; -- XTree select '<?xml-stylesheet type="text/xsl" href="a.xsl"?>';

  4. Select rational: Set a select * from 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> b c

  5. Set: Determination a select * from a.b#ref1.c#lnk1; <a id=1 data=12.3> <b#ref1 id=10 ref2=2 data=34.5> <c#lnk1 id=100 lnk2=20 data=78.9/> <c#lnk1 id=101 lnk2=20 data=89.1/> </b> <b#ref1 id=30 ref2=2 data=56.7> <c#lnk1 id=200 lnk2=40 data=91.2/> <c#lnk1 id=201 lnk2=40 data=88.8/> </b> </a> b c

  6. Select rational: Relay-race a select * from a.b.c; b <a id=1 data=12.3> <b id=10 data=23.4> <c id=100 data=34.5/> </b> </a> c

  7. Relay-race: Determination a select * from a#ref1.b#lnk1.c; b <a#ref1 id=1 data=12.3> <b#lnk1 id=10 data=23.4> <c id=100 data=45.6/> </b> </a> c

  8. Select rational: List select * from a.b.c; a <a id=88 data=77.7> <b id=1 data=12.3> <c id=3 data=3.1/> <c id=30 data=3.2/> <c id=300 data=3.3/> </b> <b id=10 data=23.4> <c id=4 data=4.1/> <c id=40 data=4.2/> <c id=400 data=4.3/> </b> <b id=100 data=34.5> <c id=5 data=5.1/> <c id=50 data=5.2/> <c id=500 data=5.3/> </b> </a> b c

  9. List: Determination select * from a.b$ref1.c$ref3; a <a id=88 data=77.7> <b$ref1 id=1 data=12.3> <c$ref3 id=3 data=3.1/> <c$ref3 id=30 data=3.2/> <c$ref3 id=300 data=3.3/> </b> <b$ref1 id=10 data=23.4> <c$ref3 id=4 data=4.1/> <c$ref3 id=40 data=4.2/> <c$ref3 id=400 data=4.3/> </b> </a> b c

  10. a/(b | k) / c a / b*/ c a / b+/ c a / b?/ c a / b[@b1] / c a / b[@b1=5] / c a / b[0]/ c a /(b | k)[0]/ c a//b a.(b | k).c a.b*.c a.b+.c a.b?.c a.b[@b1].c a.b[@b1=5].c a.b[0].c a.(b | k)[0].c a..b tab[!=5] XLang XPath XTree

  11. Input as XML DBMS Browser <a data=12.3/> a <form> auto-insert trigger for a <r> <s> <t/> </s> </r> new document select …; • Value for PK of new record is • by trigger on insert (Oracle, etc) • by datatype ‘serial’ (Postgres)

  12. Auto-insert rational: Set a <a data=12.3> <b data=23.4> <c data=56.7/> <c data=67.8/> </b> <b data=34.5> <c data=78.9/> <c data=89.1/> </b> <b data=45.6> <c data=91.2/> </b> </a> b c

  13. Set: Determination a <a data=12.3> <b#ref1 data=34.5> <c#lnk1 data=78.9/> <c#lnk1 data=89.1/> </b> <b#ref1 data=56.7> <c#lnk1 data=91.2/> <c#lnk1 data=88.8/> </b> </a> b c

  14. Auto-insert rational: Relay-race a <a data=12.3> <b data=23.4> <c data=34.5/> </b> </a> b c

  15. Relay-race: Determination a <a#ref1 data=12.3> <b#lnk1 data=23.4> <c data=45.6/> </b> </a> b c

  16. Auto-insert rational: List <a data=77.7> <b data=12.3> <c data=3.1/> <c data=3.2/> <c data=3.3/> </b> <b data=23.4> <c data=4.1/> <c data=4.2/> <c data=4.3/> </b> <b data=34.5> <c data=5.1/> <c data=5.2/> <c data=5.3/> </b> </a> a b c

  17. List: Determination a <a data=77.7> <b$ref1 data=12.3> <c$ref3 data=3.1/> <c$ref3 data=3.2/> <c$ref3 data=3.3/> </b> <b$ref1 data=23.4> <c$ref3 data=4.1/> <c$ref3 data=4.2/> <c$ref3 data=4.3/> </b> </a> b c

  18. Communication terms Sament SAg eleMENT Sag Sending xml-tAG Saribute SAg attRIBUTE

  19. HTTP … <form> <hidden> <a#ref a1=“v1”> </hidden> <input name="a2" value=“v2"> <input name=“a3“ type=“file"> <hidden> <b b1=“bb”/> </a> </hidden> </form> Html-document: Saments: <formdata> <a#ref a1=“v1“ a2=“v2”a3=“UniqueId”> <b b1=“bb”/> </a> </formdata> <file name=“a3“ filename="UniqueId">Y29udG</file>

  20. … is the most … <form> <hidden> <data a1=“v1”> </hidden> <tr id="order#ref" repeat="template"> <td><input type="text" name="row[order].product"></td> <td><input type="text" name="row[order].quantity"></td> <td><button type="remove">Remove This Row</button></td> </tr> <tr> <td><button type="add" template="order">Add Row</button></td> </tr> <hidden> </data> </hidden> </form> Html-document: <formdata> <data a1=“v1”> <order#refproduct=“p1” quantity=“q1”> <order#ref product=“p2” quantity=“q2”> <order#ref product=“p3” quantity=“q3”> </data> </formdata> Saments:

  21. … wide-spread protocol Picture can be value of field, but its URLinstead of picture is sent into output. Database: Saments: b <b b1=“5” b2=“./3652435.jpg”> <img href=“./7254843.jpg”> • SqlPlus.exe (Oracle) • PgSql.exe (Postgres) • iSql.exe (InterBase), etc

  22. XML processing

  23. FK between rational and xml create table a ( a1 string, a2 number, a3 string ); create table b ( b1 number references a(@a1/k/m/@m1), b2 string, foreign key (@b2/p/q/@q1) references a(@a2), b3 string, foreign key (@b3/p/q/@q1) references a(@a3/k/m/@m1) );

  24. Select xml select * from tab/@fld/p/q/r/a.b.c; Insert into sub-xml insert into tab/@fld/k/m/n/@@ select * from a.b.c; insert into tab/@fld/k/m/n/@@ values ('<a>...</a>'); insert into tab/@fld/k/m/n/@@ select * from t/@f/p/q/r/a.b.c; Delete xml delete from tab/@fld/p/q/a/b/c; delete from tab/@fld/p/q/a.b.c;

  25. Update xml update tab set @fld=( select * from a.b#ref.c#lnk ); update tab set @fld=( select * from t/@f/k/m/a.b.c ); update tab set @fld='<a>...</a>'; update tab set @fld/p/q/@q1=( select * from a.b#ref.c#lnk ); update tab set @fld/p/q/@q1=( select * from t/@f/k/m/a.b.c ); update tab set @fld/p/q/@q1='<a>...</a>'; update tab set @fld/p/q/@@=( select * from a.b#ref.c#lnk ); update tab set @fld/p/q/@@=( select * from t/@f/k/m/a.b.c ); update tab set @fld/p/q/@@='<a>...</a>';

  26. Predicates where @fld/k/m/n/@n1 = 5; where @fld/k/m/n/@n1 in (select @a1 from a); where @fld/k/m/n/@@ = '<a>...</a>'; Permissions grant insert on tab/@fld/k/m/nto UserName; revoke delete on tab/@fld/k/m/n from UserName; Triggers create trigger TriggerName for tab/@fld/k/m/n after insert as begin ...end;;

  27. Bound records processing

  28. Update rational update tab set p/q/r/@fld=5; Delete rational delete from tab/p/q/a.b.c;

  29. Insert rational: Set insert into tab select * from p/@p1/q/r/a.b; tab insert into tab values (' <a data=12.3> <b data=23.4/> <b data=34.5/> <b data=45.6/> </a> '); a b

  30. Set: determination insert into tab values (' <a#ref1 data=12.3> <b#lnk1 data=23.4/> <b#lnk1 data=34.5/> <b#lnk1 data=45.6/> </a> '); tab a b

  31. Insert rational: Relay-race insert into tab select * from p/@p1/q/r/a.b; tab insert into tab values (' <a data=12.3> <b data=23.4/> </a> '); a b

  32. Relay-race: Determination insert into tab#ref1 values (' <a#lnk1 data=12.3> <b data=23.4/> </a> '); tab a b

  33. Insert rational: List insert into tab select * from p/@p1/q/r/a.b; insert into tab values (' <a data=1.1 > <b data=21.1/> <b data=21.2/> <b data=21.3/> </a> <a data=1.2 > <b data=22.1/> <b data=22.2/> <b data=22.3/> </a> '); tab a b

  34. List: Determination insert into tab values (' <a$ref1 data=1.1 > <b$ref3 data=21.1/> <b$ref3 data=21.2/> <b$ref3 data=21.3/> </a> <a$ref1 data=1.2 > <b$ref3 data=22.1/> <b$ref3 data=22.2/> <b$ref3 data=22.3/> </a> '); tab a b

  35. Insert rational: into database insert into default: select * from p/@p1/q/r/a.b; insert into default: values (' <a data=12.3> <b#lnk data=23.4/> <b#lnk data=34.5/> <b#lnk data=45.6/> </a> '); insert into default: select* from a.b#lnk; insert into default: values (' <a#ref data=12.3> <b data=23.4/> <b data=34.5/> <b data=45.6/> </a> '); insert into default: select* from a#ref.b;

  36. Database terms Tabment TABle-eleMENT Fattrib Field-ATTRIBute Section level of hierarchy of XTree (whole list is one section) a/@a1 rational or xml @a1/@b2 rational (created by "row") a/b rational or xml @a1/b "a1" is rational, "b" is xml

  37. Syntax of SQL/XLang

  38. Extracted sections select b.d from p/q/a.b.c.d; select b.d from p/q/@q1/a.b.c.d; <b b1=3 b2=5 data=10> <d data=234> <d data=345> </b> <b b1=7 b2=8 data=10> <d data=456> <d data=678> </b>

  39. Extracted fatribs <a a1=56 a2=67 a3=78> <b b1=11 b2=12 b3=13/> <b b1=21 b2=22 b3=23/> <b b1=31 b2=32 b3=33/> </a> select * from a.b; select * from p/q/@q1/s/a.b; select a.b[@b1 @b2] from a.b; select a.b[@b1 @b2] from p/q/@q1/s/a.b; <a a1=56 a2=67 a3=78> <b b1=11 b2=12 /> <b b1=21 b2=22 /> <b b1=31 b2=32 /> </a> select a.b[@b1 as @m, @b2 as @n] from a.b; select a.b[@b1 as @m, @b2 as @n] from p/q/@q1/s/a.b; <a a1=56 a2=67 a3=78> <b m=11 n=12 /> <b m=21 n=22 /> <b m=31 n=32 /> </a>

  40. Virtual foreign key (vFK) select a.b.k.m from p/q/a.b , p/q/k.m where b/@b1=k/@k1; select a.b.k.m from p/q/@q1/s/a.b , p/q/@q1/s/k.m where b/@b1=k/@k1;

  41. vFK and spec-symbols ‘*’, ‘+’ select * from b* where previous(b)/@b2=next(b)/@b1; select * from a.b*.c where previous(b)/@b2=next(b)/@b1; select * from k/m/@m1/a/b* where previous(b)/@b2=next(b)/@b1; select * from k/m/@m1/a.b*.c where previous(b)/@b2=next(b)/@b1;

  42. Aliases and vFK select k.m.n from p/q/c k m n-- rational from p/q/@q1/r/c k m n-- xml where k/@c2=m/@c3and m/@c4=n/@c5; select a.b.k.m.n from a.b , p/q/c k m n -- rational & rational from a.b , p/q/@q1/c k m n -- rational & xml from t/u/@u1/a.b , p/q/@q1/c k m n -- xml & xml where k/@c2=m/@c3 and m/@c4=n/@c5;

  43. XPath continues XPath delete from p/q/a.b.c where t/u/@u1=5 -- rational fatrib 'p/q/t/u/@u1' and t/u/@u1/k/m/@m1=5; -- xml fatrib 'p/q/t/u/@u1/k/m/@m1' delete from p/q/@q1/k/m/a.b.c where t/u/@u1=5; -- xml fatrib 'p/q/@q1/k/m/t/u/@u1' update p/q set k/m/@m1=1, -- rational fatrib 'p/q/k/m/@m1' a/b/@b1/c/d/@d1=3 -- xml fatrib 'p/q/a/b/@b1/c/d/@d1' where t/u/@u1=5 -- rational fatrib 'p/q/t/u/@u1' and t/u/@u1/k/m/@m1=5; -- xml fatrib 'p/q/t/u/@u1/k/m/@m1' update p/q/@q1/r/s set k/m/@m1=1 -- xml fatrib 'p/q/@q1/r/s/k/m/@m1' where t/u/@u1=5; -- xml fatrib 'p/q/@q1/r/s/t/u/@u1'

  44. XPath continues XPath insert into p/q/@@ ( k/m/@m1, -- rational fatrib 'p/q/k/m/@m1' a/b/@b1/c/d/@d1 -- xml fatrib 'p/q/a/b/@b1/c/d/@d1' ) values (1,3) where t/u/@u1=5 -- rational fatrib 'p/q/t/u/@u1' and t/u/@u1/k/m/@m1=5; -- xml fatrib 'p/q/t/u/@u1/k/m/@m1' insert into p/q/@q1/r/s/@@ ( k/m/@m1 -- xml fatrib 'p/q/@q1/r/s/k/m/@m1' ) values (1) where t/u/@u1=5; -- xml fatrib 'p/q/@q1/r/s/t/u/@u1' select * from p/q/a.b.c where t/u/@u1=5 -- rational fatrib 'p/q/t/u/@u1' and t/u/@u1/k/m/@m1=5; -- xml fatrib 'p/q/t/u/@u1/k/m/@m1' select * from p/q/@q1/r/s/a.b.c where t/u/@u1=5; -- rational fatrib 'p/q/@q1/r/s/t/u/@u1'

  45. Conditions for fields from a.b[@b1=5] from a.b[@b1] from a.b.c[@c1=../../@a1] from a.b[position()=1 and @b1=5]*.c from a.b[last() and @b1=5]*.c ‘where’ ‘from’ from a.b where @b1=5 from a.b where @b1 is not null from a.b.c where a/@a1=c/@c1 from a.b*.c where first(b)/@b1=5 from a.b*.c where last(b)/@b1=5 from a.b*.c where @a1 in (select ... )

  46. Permutation and ‘*’, ‘+’ ‘where’ ‘from’ select * from a.b*[@b1~/x/@x1].c; select a.b*.c from a.b*.c, x where @b1~@x1; select * from a.b[@b1 as k1].b[@b1 as m1].b[@b1 as n1].b[@b1 as p1].b[@b1 as q1].b[@b1 as r1].b[@b1 as s1]. ... .c where @k1<>@m1 and @k1<>@n1 and @k1<>@p1 and @k1<>@q1 and @k1<>@r1 and @k1<>@s1 and ... and @m1<>@n1 and @m1<>@p1 and @m1<>@q1 and @m1<>@r1 and @m1<>@s1 and ... and @n1<>@p1 and @n1<>@q1 and @n1<>@r1 and @n1<>@s1 and ... and @p1<>@q1 and @p1<>@r1 and @p1<>@s1 and ... and @q1<>@r1 and @q1<>@s1 and ... and @r1<>@s1 and ... and @k1 in (select @x1 from x) and @m1 in (select @x1 from x) and @n1 in (select @x1 from x) and @p1 in (select @x1 from x) and @q1 in (select @x1 from x) and @r1 in (select @x1 from x) and @s1 in (select @x1 from x) and ... ;

  47. Reject previous designations select a.a1 where b.b1=5 from scheme.tab update a set b=5 update a set b/@c=5 select a/@a1 where b/@b1=5 from scheme§tab update a set @b=5

  48. Kinds of determination

  49. Nog create table a ( id num primary key, data float ); create table b ( id num primary key, ref1 num references a(id), -- important ref2 num references a(id), -- ballast ref3 num references c(id), -- important ref4 num references c(id), -- ballast data float ); create table c ( id num primary key, data float ); .a.b#ref1:ref3.c.

  50. Buckle create table a ( id num primary key, ref1 num references b(id), -- important ref2 num references b(id), -- ballast data float ); create table b ( id num primary key, data float ); create table c ( id num primary key, ref1 num references b(id), -- important ref2 num references b(id), -- ballast data float ) .a#ref1.b.c#ref3.

More Related