OpenLink Virtuoso Features Demonstrations and Tutorials OpenLink Virtuoso Features Demonstrations and Tutorials Data Management - SQL Tutorial Data Management - XML Tutorial Data Management - Data Replication Tutorial Data Management - RDF Tutorial Web Services - SOAP Services Tutorial Web Services - Wireless Access Protocols Tutorial Business Process Integration - BPEL Tutorial Development - Web 1.0 Tutorial Development - Web 2.0 Tutorial Development - XML Tutorial Development - RDF Tutorial Development - NNTP Tutorial Demo applications - Framework Hosting Tutorial Demo applications - XML Data Access Tutorial Demo applications - Semantic Web Tutorial Integrating Common Language Runtime Objects with Virtuoso Fri, 05 Jun 2009 10:21:00 GMT Using C# Objects to extend Virtuoso via User Defined Types. Overview The following tutorial demonstrates how Virtuoso can be extended through the use of a .NET bound language such as C# to create User Defined Types (UDTs), Stored Procedures, and Functions. This is an enhancement of its Object-Relational Database functionality. The demonstrations in this section highlight transparent integration (hosting) between Virtuoso and the Microsoft .NET and Mono implementations of the ECMA Common Language Infrastructure (CLI). Prerequisites The following prerequisites ensure the usability of these tutorial demos on Windows or Linux (and in other future Mono implementations): Linux Mono Runtime and Frameworks (ideally the version bundled with Virtuoso) Mono SDK (if you seek to write your own Objects for integration with Virtuoso) Virtuoso Universal Server for Linux with Mono Hosting Windows .NET Runtime and Frameworks .Net Runtime and Framework SDK (if you seek to write your own Objects for integration with Virtuoso) Virtuoso Universal Server for Windows with .NET CLR Hosting Tutorial Example The following tutorial shows how C# classes are used to create User Defined Types (UDTs) in Virtuoso. Please follow the steps below to maximize the value of this tutorial: Click on the "Set Initial State" link which registers the two C# classes with Virtuoso. Click on the "ho_s_2.sql" link to see the code behind this step Click on the "Run" links to actually experience the demo Demo Breakdown "ho_s_2.sql" performs the following steps Registers the "MyFinances" and "Point" classes with Virtuoso. Click on the "MyFinances.cs" and "Point.cs" links to see the C# source code of these classes Creates the conventional SQL table "Employee" Creates the Object-Relational table "Supplier" that includes a column named "Location" that is of Type "Point" (a UDT) Populates each of the newly created tables with data Creates the "distance" stored procedure which uses a C# function member (method) to calculate the distance between points. "vsp1.vsp" uses the static method "tax" of the "MyFinances" class to compute the payroll tax values for each employee via the "salary" column (like a SQL function would). Take note of the Virtuoso class invocation syntax for static C# methods (the relevant member function type in this demo; true to C# these are called by name rather than instance). "vsp2.vsp" uses the "distance" method of the UDT based column "location" to test for distance values less than "3". The distance values are return values from the "Point" class. Note that the "Point" class computes the distance between two arbitrary points. "vsp3.vsp" is a variation of the previous demo, with the column "location" being used to access and conditionally test the values of data member "x" (shown in "Point.cs" as 0). "vsp4.vsp" is another variation of a query on the "Supplier" table, in this particular case the query uses a Stored Procedure (see definition in "ho_s_2.sql") to filter its result set. It achieves this by using data member values "x" and "y" from column "location", and the arbitrary values 1 & 2 as input parameters for the Stored Procedure "distance". It then tests the return values for those less than 3. Sending an SMS message through an ASP .NET SOAP Client to update a table Fri, 05 Jun 2009 10:21:00 GMT Demonstrating the use of C# to create Virtuoso hosted Stored Procedures and Triggers. In this demo the database events trigger SMS messages that are relayed via C# based SOAP Client. Overview The following tutorial demonstrates how a C# based Managed SOAP Client is used to create SMS based database notification services inside Virtuoso. This demo executes a database trigger every time a new Supplier record is added, updated or deleted. In this example a C# class acts as a SOAP client to a 3rd party XML Web Service that provides the SMS (Short Message Services) delivery to mobile phones. Note, this demo will work with any phone that is capable of receiving SMS (TEXT) messages. Prerequisites This demo currently works only on a Virtuoso server running under Microsoft Windows with following components installed: Windows .NET Runtime and Frameworks .Net Runtime and Framework SDK (if you seek to write your own Objects for integration with Virtuoso) Virtuoso Universal Server for Windows with .NET CLR Hosting. Tutorial Example Please follow the steps below to maximize the value of this tutorial: Click on the "Set Initial State" link which registers the "redcoalsmssvc" service with Virtuoso. Click on the "ho_s_3.sql" link to see the code behind this step Click on the "Run" links to actually experience the demo Demo Breakdown "ho_s_3.sql" performs the following steps Registers the "redcoalsmssvc" class with Virtuoso. Click on the "redcoalsms.cs" and "redcoalsmsref.cs" links to see the C# source code. Note that the class "redcoalsmssvc" is part of an assembly that has the namespace "redcoalsms" Creates the "Suppliers" table Creates a stored procedure "redcoal_send_sms" which demonstrates the syntax for creating an instance variable which includes the assembly namespace in the type reference. Specifically, note the use of an underscore instead of a dot when making this assignment Creates a Trigger named "send_sms_to_mgr_new_supp" that sends an SMS message each time a new "Supplier" table record is inserted Creates a Trigger named "send_sms_to_mgr_mod_supp" that sends an SMS message each time a "Supplier" record is updated Creates a Trigger named "send_sms_to_mgr_mod_supp" that sends an SMS message each time a supplier record is deleted "setup_sms.vsp" sets up all of the base data and verifies that SMS service connectivity required by this demo is available. "handler.vsp" is the actual interface through which you add or delete supplier records which result in SMS notifications being sent to your mobile phone. Hosting CLR types using VSPX session management Fri, 05 Jun 2009 10:21:01 GMT Demonstrating the use of C# to create Virtuoso hosted Stored Procedures and Triggers. In this demo a database events trigger SMS messages that are relayed via a C# based SOAP Client. It also demonstrates Virtuoso Server Pages session management. Overview This is a variation of HO-S-3 with the SOAP client implemented ASP.NET and also demonstrates Virtuoso Server Pages for XML (VSPX) session management. Prerequisites This demo currently works only on a Virtuoso server running under Microsoft Windows with following components installed: Windows .NET Runtime and Frameworks .Net Runtime and Framework SDK (if you seek to write your own Objects for integration with Virtuoso) Virtuoso Universal Server for Windows with .NET CLR Hosting. Tutorial Example The setup_sms.vspx sets up the SMS sending unit. The handler.vspx does inserts/deletes/updates to demonstrate the SMS sending from triggers. The redcoalsms*.cs is an MS Visual Studio.NET generated SOAP client. The redcoalsms.dll should be installed as a private assembly (not as CodeBase) because of permissions. For details of the Redcoal SOAP service see it's description in http://www.xmethods.com Hosting CLR types using VSPX session management Fri, 05 Jun 2009 10:21:01 GMT Demonstrating the use of C# to create Virtuoso hosted Stored Procedures and Triggers. In this demo a database event trigger SMS messages that are relayed via a .NET DOM frameworks based C# SOAP Client. It also demonstrates Virtuoso Server Pages session management. Overview HO-S-5 is a variation of HO-S-3 that makes use of VSPX session management Prerequisites The following prerequisites ensure the usability of these tutorial demos on Windows or Linux (and in other future Mono implementations): Linux Mono Runtime and Frameworks (ideally the version bundled with Virtuoso) Mono SDK (if you seek to write your own Objects for integration with Virtuoso) Virtuoso Universal Server for Linux with Mono Hosting Windows .NET Runtime and Frameworks .Net Runtime and Framework SDK (if you seek to write your own Objects for integration with Virtuoso) Virtuoso Universal Server for Windows with .NET CLR Hosting Tutorial Example The setup_sms.vspx sets up the SMS sending unit. The handler.vspx does inserts/deletes/updates to demonstrate the SMS sending from triggers. The redcoalsms_dom.cs is an HttpWebRequest SOAP client using DOM to process SOAP result. The redcoalsms_dom.dll should be installed as a private assembly (not as CodeBase) because of permissions. For details of the Redcoal SOAP service see it's description in http://www.xmethods.com This service is compatible with Mono (http://go-mono.com) Integrating Common Language Runtime Objects with Virtuoso Fri, 05 Jun 2009 10:21:00 GMT Using C# Objects to extend Virtuoso via User Defined Types using CREATE LIBRARY/ASSEMBLY syntax. Overview The HO-S-10 sample is very similar to HO-S-2. The difference is using syntax CREATE LIBRARY. The statement imports assembly to internal Virtuoso table and imports types from it. After this the assembly can be removed from physical path and next time will be read from the table. The "DROP LIBRARY" removes assembly from table and drops imported types. Prerequisites The following prerequisites ensure the usability of these tutorial demos on Windows Windows .NET Runtime and Frameworks .Net Runtime and Framework SDK (if you seek to write your own Objects for integration with Virtuoso) Virtuoso Universal Server for Windows with .NET CLR Hosting Tutorial Example The following tutorial shows how C# classes are used to create User Defined Types (UDTs) in Virtuoso. Please follow the steps below to maximize the value of this tutorial: Click on the "Set Initial State" link which registers the two C# classes with Virtuoso. It also copies the Point_ho_s_10.dll file to "tmp" directory in the server root. Note that the directory must exists, must be writeable for the user running virtuoso and must be in the DirsAllowed parameter in Virtuoso ini file. Click on the "ho_s_10.sql" link to see the code behind this step Click on the "Run" links to actually experience the demo Demo Breakdown "ho_s_10.sql" performs the following steps Registers the "Point" classes with Virtuoso. Click on the "Point_ho_s_10.cs" links to see the C# source code of these classes Creates the Object-Relational table "Supplier_ho_s_10" that includes a column named "Location" that is of Type "Point" (a UDT) Populates table with data Using syntax: CREATE LIBRARY/ASSEMBLY "myPoint" as "assembly" WITH PERMISSION_SET = SAFE WITH AUTOREGISTER is simular to: CREATE LIBRARY/ASSEMBLY "myPoint" as "assembly"; and import_clr ('myPoint', NULL); "vsp1.vsp" is a variation of HO-S-2 (vsp3.vsp), with the column "location" being used to access and conditionally test the values of data member "x" (shown in "Point.cs" as 0). Integrating Common Language Runtime Objects with Virtuoso Fri, 05 Jun 2009 10:21:00 GMT Using Restrictions for C# Imported types. Overview This tutorial demonstrates the options PERMISSION_SET = SAFE and PERMISSION_SET = UNRESTRICTED on CREATE LIBRARY / ASSEMBLY. Prerequisites The following prerequisites ensure the usability of these demos on Windows Windows .NET Runtime and Frameworks .Net Runtime and Framework SDK (if you seek to write your own Objects for integration with Virtuoso) Virtuoso Universal Server for Windows with .NET CLR Hosting Tutorial Example The tutorial shows how C# classes are used to create User Defined Types (UDTs) in Virtuoso. Please follow the steps below to maximize the value of this tutorial: Click on the "Set Initial State" link which registers the two C# classes within Virtuoso. It also copies the restricted.dll and unrestricted.dll files to "tmp" directory in the server root. Note that the directory must exists, must be writeable for the user running virtuoso and must be in the DirsAllowed parameter in Virtuoso ini file. Click on the "ho_s_11.sql" link to examine the code behind this step. Click on the "Run" links to execute the demo. Demo Breakdown "ho_s_11.sql" performs the following steps Registers the "Rest" and "UnRest" classes with Virtuoso. Click on the "restricted.cs" and "unrestricted.cs" links to see the C# source code of these classes. The "unrestricted.vsp" is shows classes that try to access File system and system variables. The "restricted.vsp", uses the same clases, but cannot complete the execution sucessfully because the assembly is imported with PERMISSION_SET = SAFE. The sample shows the error returned upon executing the classes. Making Table valued functions in C# Fri, 05 Jun 2009 10:21:00 GMT Using C# Objects to make table valued functions. Overview The following tutorial demonstrates how Virtuoso can be extended through the use of a .NET bound language such as C# to create resultsets. The demonstrations in this section highlight transparent integration (hosting) between Virtuoso and the Microsoft .NET and Mono implementations of the ECMA Common Language Infrastructure (CLI) as well as how to use the Virtuoso in-process ODBC client to call back the server from hosted managed code. Prerequisites The following prerequisites ensure the usability of these tutorial demos on Windows or Linux (and in other future Mono implementations): Linux Mono Runtime and Frameworks (ideally the version bundled with Virtuoso) Mono SDK (if you seek to write your own Objects for integration with Virtuoso) Virtuoso Universal Server for Linux with Mono Hosting Virtuoso .NET provider Windows .NET Runtime and Frameworks .Net Runtime and Framework SDK (if you seek to write your own Objects for integration with Virtuoso) Virtuoso Universal Server for Windows with .NET CLR Hosting Virtuoso .NET provider Tutorial Example The following tutorial shows how C# classes can interact with the Virtuoso server. Please follow the steps below to maximize the value of this tutorial: Copy the file ho_s_14.dll next to the OpenLink.Data.VirtuosoClient.dll (if not allready there). Click on the "Set Initial State" link which registers the C# class with Virtuoso. Click on the "ho_s_14.sql" link to see the code behind this step Click on the "Run" links to actually experience the demo Demo Breakdown "ho_s_14.sql" registers the "openlink.virtuoso.tutorial.ho_s_14.add_data" C# static method with Virtuoso. Click on the "ho_s_14.cs" link to see the C# source code of this class "vsp1.vsp" uses the static method "add_data" of the "openlink.virtuoso.tutorial.ho._s_14" class to make a resultset (like a SQL function would) through an in-process connection to the server. Take note of the usage of "OpenLink.Virtuoso.InProcessPort" key from AppDomain.GetData() to get the port on which the hosting Virtuoso server is listening at. Accessing COM Objects via C# Fri, 05 Jun 2009 10:21:00 GMT Using the .NET to access native COM objects from Virtuoso/PL Overview The following tutorial demonstrates how Virtuoso can access MS Windows COM objects via .NET. The demonstrations in this section highlight transparent integration (hosting) between Virtuoso, the Microsoft .NET implementations of the ECMA Common Language Infrastructure (CLI) and the Win32 COM Layer. Prerequisites The following prerequisites ensure the usability of these tutorial demos on Windows: .NET Runtime and Frameworks .Net Runtime and Framework SDK (if you seek to write your own Objects for integration with Virtuoso) Virtuoso Universal Server for Windows with .NET CLR Hosting Tutorial Example The following tutorial shows how the Virtuoso server can call native Win32 COM objects using the .NET runtime as intermediary. Please follow the steps below to maximize the value of this tutorial: Register the COM library COM/VirtCOMServer/Debug/VirtCOMServer.dll with COM (via regsvr32.exe COM/VirtCOMServer/Debug/VirtCOMServer.dll). Copy the interop assembly COM/VirtCOMServer/VirtCOMServer.dll next to the virtuoso server binary (if not allready there). Click on the "Set Initial State" link which registers the C# interop class for the Interface IVirtCOMObject with Virtuoso. Click on the "ho_s_15.sql" link to see the code behind this step Click on the "Run" links to actually experience the demo Demo Breakdown "ho_s_15.sql" registers the "VirtCOMServer.CVirtCOMObjectClass" C# interop class with Virtuoso. Click on the "VirtCOMObject.c" and "VirtCOMObject.h" link to see the C++ source code of this interface and it's class "vsp1.vsp" uses the methods "AddAmount", "Clear" and "get_balance" of the "VirtCOMServer.IVirtCOMObject" Interface to sum a number of amounts and get the resulting total. Integrating Java Objects with Virtuoso Fri, 05 Jun 2009 10:21:00 GMT Using Java Objects to extend Virtuoso via User Defined Types. Overview The following tutorial demonstrates how Virtuoso can be extended through the use of Java to create User Defined Types (UDTs), Stored Procedures, and Functions. This is an enhancement of its Object-Relational Database functionality. The demonstrations in this section highlight transparent integration (hosting) between Virtuoso and the Java Runtime Environment. Prerequisites The following prerequisites ensure the usability of these tutorial demos on any platform with a Java Runtime environment: Java Runtime (J2EE 1.2 and Higher) Java SDK (if you seek to write your own Objects for integration with Virtuoso) Virtuoso Universal Server with Java Runtime Hosting Tutorial Example The following tutorial shows how Java Classes are used to create User Defined Types (UDTs), Stored Procedures, and Functions in Virtuoso. Please follow the steps below to maximize the value of this tutorial: Click on the "Set Initial State" link which registers the Java classes used in this demo with Virtuoso. Click on the "ho_s_1.sql" link to see the code behind this step Click on the "Run" links to actually experience the demo Demo Breakdown "ho_s_1.sql" performs the following steps Registers the "MyFinances" and "Point" Classes with Virtuoso as UDTs. Click on the "MyFinances.java" and "Point.java" links to see the JAVA source code Creates the conventional SQL table "Employee" Creates the Object-Relational table "Supplier" that includes a column named "Location" that is of Type "Point" (which is a UDT) Populates each of the newly created tables with data Creates the "distance" stored procedure which uses UDT methods to calculate the distance between points. "vsp1.vsp" uses the static method "tax" of the "MyFinances" class to compute the payroll tax values for each employee via the "salary" column (like a SQL function would). "vsp2.vsp" uses the "distance" method of the UDT based column "location" to test for distance values less than "3". The distance values are return values from the "Point" UDT. Note that "Point" class is computing the distance between two arbitrary points "vsp3.vsp" is a variation of the previous demo, with the UDT based column "location" being used to access and conditionally test the values of data member "x" (shown in "Point.java" as 0) "vsp4.vsp" is another variation of a query on the "Supplier" table, in this particular case the query uses a Stored Procedure (see definition in "ho_s_2.sql") to filter its result set. It achieves this by using data member values "x" and "y" from column "location", and the arbitrary values 1 & 2 as input parameters for the Stored Procedure "distance". It then tests the return values for those less than 3. Integrating Java Objects with Virtuoso Fri, 05 Jun 2009 10:21:00 GMT Using Restrictions for Java hosted types. Overview The following tutorial demonstrates how Virtuoso can Restrict Java hosted types. Prerequisites The following prerequisites ensure the usability of these tutorial demos on any platform with a Java Runtime environment: Java Runtime (J2EE 1.2 and Higher) Java SDK (if you seek to write your own Objects for integration with Virtuoso) Virtuoso Universal Server with Java Runtime Hosting Tutorial Example Please follow the steps below to maximize the value of this tutorial: Click on the "Set Initial State" link which registers the Java classes used in this demo with Virtuoso. Click on the "ho_s_13.sql" link to see the code behind this step Click on the "Run" links to actually experience the demo Demo Breakdown "ho_s_13.sql" performs the following steps Registers the "Restricted" and "Unrestricted" Classes with Virtuoso as UDTs. Click on the "Restricted.java" and "Unrestricted.java" links to see the JAVA source code The Restricted types can't write to FS, can't read System envariment. For full list from restrictions, please see documentation. Filter WebDAV content content by XQuery Fri, 05 Jun 2009 10:21:03 GMT fn:collection usage example Preliminary XQuery collection function can be used to get all parsed documents in collection. Virtuoso engine supports several types of collections: http://local.virt/DAV/PATHTO - local DAV collection. Contains all documents in DAV collection with path "PATHTO". http://host:port/path - any other URL that can be used to access remote WebDAV collections In this example collection function is used to filter local blog entries and all cached blog entries from subscriptions. search.vsp uses this collection to filter blogs by XPath exression. For instance, search.vsp with '//a' expression returns all link from all blogs. text.vsp uses collection function to search blogs which contain some word. Note, it uses "contains" predicate to search blogs, this means it is case sensitive. Prerequisites This example needs the following VAD packages to be installed: Conductor Framework Briefcase Feed Manager Weblog If you do not have permissions to install the above please ask the administrator. The VAD installation can be done via Conductor (if it's installed) or via ISQL tool using vad_install() API function. For more details on vad installation please read the user manual. Important: before to run any of the actions bellow you need to setup a ODS account (or use tutorial_demo with password secret). In both cases you will need to login in ODS and create instances of the Briefcase, Feed Manager and Weblog applications. Using the Feed Manager you should subscribe to some news feeds in RSS, Atom or RDF format. Using the Briefcase you can upload some sample data in the account's home directory. Using the Weblog, make few sample blog posts. Example overview This example demonstrates: XQuery collection function XPath extension functions WebDAV Resource Filtering and WebDAV Extension Types (DETs) Example Operation Login into ODS using the link bellow, after successfull authentication the ODS login will redirect browser back to this page Using the link 'Run' start the search.vsp Initially you will have the ODS account's home directory set as a base, so you can change it using the browse button If you don't have initial data loaded or data which not satisfies the conditions, the query execution will be produce empty result. Example Setup The demo support functions are prepared by loading the supplied SQL file How to run the examples In order to enter the initial data via ODS you need to login and create instances via ODS. After entering the initial data, you will need to go back to this page. If you already done this just skip this step In order to run the examples, you need to login in the Web Applications. To login you can use the already defined user "tutorial_demo" with password "secret" case sensitive. When you click the "Run" link of the search.vsp file the browser will be redirected to ODS login page. After succefull login the browser will be redirected back to this page. Filter blogs content by XQuery Fri, 05 Jun 2009 10:21:03 GMT collection usage example Example collection description XQuery collection function can be used to get all parsed documents in collection. Virtuoso engine supports several types of collections: http://local.virt/DAV/PATHTO - local DAV collection. Contains all documents in DAV collection with path "PATHTO". http://..... - any other URI relates to remote DAV collections In this example collection function is used to concatenate the entries from several RSS feed files and show their titles and link to the articles. example.vsp uses this collection to filter blogs by XPath exression. For instance, example.vsp with '//link' expression returns all link from all feeds. Click on the "Set initial state" link to load the XML files in /DAV/feeds/xq_s_2a/ DAV Collection. Click on the "Run" link to experience the demo. Please note that some of the options query files loaded in demo XQ-S-2. Slashdot RSS feed retrieval Fri, 05 Jun 2009 10:21:03 GMT document usage example Example document description XQuery document function can be used to get a parsed document via URL. In this example the document is used to retreive the Slashdot RSS feed and convert the contents into a feed index. Click on the "Set initial state" link to load the slash.xml file in /DAV/xmlsql/ DAV Collection with execute permisions. Click on the "Run" link and the browser will be redirected to /DAV/xmlsql/slash.xml?contenttype=text/html. OPML file generation from XHTML source Fri, 05 Jun 2009 10:21:03 GMT document usage example Example The demo retrieve a HTML pages containing attendees or bloggers listings and convert them to OPML. The conversion is done with XQuery and user-defined XPath functions to resolve the feeds URLs. The demo also retrieve few OPML files and re-construct them having the feeds URLs if they are missing. When setting the initial state, the demo starts to retrieve pages from the different sites. It could take a long time depending on the Internet connection of the machine that Virtuoso is running on. It is safe to leave the pop-up open without waiting it to finish and press on the "Run" links, however if this is the first time you run the initial state, the demo will show only partial results. Once the feeds are resolved they will be cached locally in a database table and the generated OPML files will be stored as a WebDAV resources. The following are source references used in the demo: http://wiki.techcrunch.com/third_meetup http://news.com.com/html/ne/blogs/CNETNewsBlog100.opml http://conferences.oreillynet.com/pub/w/23/speakers.html http://conferences.oreillynet.com/pub/w/38/speakers.html http://www.alwayson-network.com/comments.php?id=10852_0_11_0_C http://www.gnomedex.com/holdings/br_2005%20Gnomedexers.opml http://www.web2con.com/pub/w/40/speakers.html http://www.thenewpr.com/wiki/pmwiki.php?pagename=Resources.CEOBlogsList http://nwr.cowblock.net/index.php?action=list http://okrasoup.typepad.com/black_looks/2005/05/naija_blogs.html http://allafrica.com/afdb/blogs/blogafrica.opml Overview Fri, 05 Jun 2009 10:21:03 GMT XMLELEMENT() function. XMLELEMENT() function. Overview XMLELEMENT takes an element name for identifier, an optional collection of attributes for the element, and arguments that make up the element's content. It returns an XML element. The second parameter may be omitted and at that time the rest parameters may be present. If one of the arguments is a call of the xpath_eval returning an attribute value, then this value would be added to element's content (not to element's attributes). XMLATTRIBUTES() function. Overview This function creates a vector that may be used only as argument of XMLELEMENT function. The vector has an even number of elements, each odd element is a name of an attribute, an even element is its value. If the attribute value is NULL, then no attribute and no value is created. If none of the attribute is created, then the function returns null. If a parameter of the function is a column name, then you can omit the 'AS clause', and Virtuoso uses the partially escaped form of the column name as the attribute name. sx_e_? Demos Countinue with the next sx_e_? demos to see examples of functoins usage Making an XML element without content Fri, 05 Jun 2009 10:21:03 GMT XML element without content Example 1 This example shows a simple use of the XMLELEMENT() for creating the xml element 'Employee' (for each employee) without content: select XMLELEMENT ("Employee") from "Demo"."demo"."Employees" Making an XML element with a text content Fri, 05 Jun 2009 10:21:03 GMT XML element with a text content Example 2 This example shows a use of the XMLELEMENT() for creating the xml element 'EmployeeName' (for each employee) with the text content that is value of the 'LastName' column from the "Demo"."demo"."Employees" table. select XMLELEMENT ("EmployeeName", "LastName") from "Demo"."demo"."Employees"; Making an XML element with attributes Fri, 05 Jun 2009 10:21:03 GMT XML element with attributes Example 3 This example shows a use of the XMLELEMENT() for creating the xml element 'Emp' with three attributes - 'EmployeeID', 'firstname' and 'lastname' getting their values from the 'EmployeeID', 'FirstName' and 'LastName' columns, respectively. The pairs of attribute and its value are produced by XMLATTRIBUTES() function. If a parameter of the XMLATTRIBUTES() has no 'as clause', Virtuoso uses the partially escaped form of the column name as the attribute name. select XMLELEMENT ("Emp", XMLATTRIBUTES ("EmployeeID", "FirstName" as "firstname", "LastName" as "lastname")) from "Demo"."demo"."Employees"; Making an XML element with attribute, a text content and a sublement Fri, 05 Jun 2009 10:21:03 GMT XML element with attribute, a text content and a sublement Example 4 This example shows a use of the XMLELEMENT() for creating the xml element 'Employee' with the 'EmployeeID' attribute, text content that is concatenations of the value of the 'FirstName' column, of the blank character and of the value of the 'LastName', and the 'Title' subelement having 'title' attribute. select XMLELEMENT ('Employee', XMLATTRIBUTES ("EmployeeID"), "FirstName"||' '||"LastName", XMLELEMENT("Title", XMLATTRIBUTES ( "Title" as "title"))) from "Demo"."demo"."Employees"; Making an XML element with two nested subelements Fri, 05 Jun 2009 10:21:03 GMT XML element with two nested subelements Example 5 This example shows a use of the XMLELEMENT() for creating the xml element 'FullName' with two subelements 'firstname' and 'lastname'. Both subelements have the simple text content. select XMLELEMENT ('FullName', XMLELEMENT ('firstname', "FirstName"), XMLELEMENT ('lastname', "LastName")) from "Demo"."demo"."Employees"; Making an XML element with nested XMLFOREST() function Fri, 05 Jun 2009 10:21:03 GMT XML element with XMLFOREST() function call as parameter Example 6 The example shows a use of the XMLELEMENT() with the nested call of XMLFOREST() function. The result is identical to the previous example. select XMLELEMENT ('FullName', XMLFOREST ("FirstName"as "firstname", "LastName" as "lastname")) from "Demo"."demo"."Employees"; Making an XML element with nested XMLCONCAT() function Fri, 05 Jun 2009 10:21:03 GMT XML element with XMLCONCAT() function call as parameter Example 7 The example shows a use of the XMLELEMENT() with the nested call of XMLCONCAT() function. The result is identical to the previous two examples. select XMLELEMENT ('FullName', XMLCONCAT ( XMLELEMENT ('firstname', "FirstName"), XMLELEMENT ('lastname', "LastName"))) from "Demo"."demo"."Employees"; Making an XML element with nested XMLAGG() function Fri, 05 Jun 2009 10:21:03 GMT XML element with XMLAGG() function call as parameter Example 8 This example produces an 'Emp' element with attribute 'Title' and a list of all employees having the title 'Sales Representative' as element content. select XMLELEMENT ('Emp', XMLATTRIBUTES ("Title"), XMLAGG (XMLELEMENT ('Name', "FirstName", ' ', "LastName"))) from "Demo"."demo"."Employees" where "Title"= 'Sales Representative'; Making an XML element with the entity objects as parameters Fri, 05 Jun 2009 10:21:03 GMT XML element with the entity objects as parameters Example 9 This example creates an 'FullAddress' element with four attributes, three of them ('PostalCode', 'Address', 'City') are produced by XMLATTRIBUTES, and the fourth attribute - 'country' is calculated by xquery_eval 'Region' subelement, that is produced by xtree_doc text content, that is produced by xpath_eval 'emp' subelement with text content from the column "LastName", that is created by nested XMLELEMENT select XMLELEMENT ('FullAddress', XMLATTRIBUTES ( "PostalCode", "Address", "City"), xtree_doc ('<Region>WA</Region>'), xquery_eval('//@country', xtree_doc('<a country="USA"/>')), xpath_eval('//@Phone', xtree_doc('<a Phone="(206) 555-9857"/>')), XMLELEMENT('emp', "LastName")) from "Demo"."demo"."Employees" Making a Forest of XML elements Fri, 05 Jun 2009 10:21:03 GMT XMLFOREST() function XMLFOREST() function. Overview XMLFOREST produces a forest of XML elements from the given list of arguments. The arguments may be string expressions with optional aliases. If string expression is a column name, then you can omit the 'AS clause', and Virtuoso uses the partially escaped form of the column name as the name of the enclosing tag. If the expression evaluates to NULL, then no element is created for that expression. If none of the element is created, then the function returns null. Example 1 This example produces a forest of five (or four) elements ('FName', 'LName', 'Title', 'Region' - if there is a value, 'str') with the text content from 'FirstName', 'LastName', 'Title', and 'Region' columns of the "Employees" table and 'simple_string' string, and concatenates the elements produced. Five elements are created for the employee with "EmployeeId" = 1 and four elements are created for the employee with "EmployeeId" = 5: select XMLFOREST ("FirstName" as "FName", "LastName" as "LName", "Title", "Region", 'simple_string' as "str") from "Demo"."demo"."Employees" where "EmployeeId" = 1 or "EmployeeId" = 5 Making a Forest of XML elements by XMLCONCAT() function Fri, 05 Jun 2009 10:21:03 GMT XMLCONCAT() function XMLCONCAT() function Overview XMLCONCAT() accepts a list of XML value expressions as its arguments, and produces a forest of elements by concatenating the XML values that are returned from the same row to make one value. XMLCONCAT works like XMLFOREST, except that XMLCONCAT parameters is a list of XML elements. Null expressions are dropped from the result. If all the value expressions are null, then the function returns null. Example 1 This example produces a forest from the 'FName', 'LName' and 'Region' (if a column value is not NULL) elements for each employee: select XMLCONCAT ( XMLELEMENT ('FName', "FirstName"), XMLELEMENT ('LName', "LastName"), XMLELEMENT ('Region', "Region") ) from "Demo"."demo"."Employees"; Making a Forest of XML elements by XMLAGG() function Fri, 05 Jun 2009 10:21:03 GMT XMLAGG() function XMLAGG() function. Overview XMLAGG is aggregate function that produces a forest of XML elements from the given list of xml elements. It concatenates the values returned from one column of multiple rows, unlike XMLCONCAT, which concatenates the values returned from multiple columns in the same row. Example 1 This example produces a forest of all 'Name' of the employees having the title 'Sales Representative' and places it into one top-level element: select XMLELEMENT ('SalesRepresentatives', XMLAGG (XMLELEMENT ('Name', "FirstName", ' ', "LastName")) ) from "Demo"."demo"."Employees" where "Title"='Sales Representative'; Making a freetext index Fri, 05 Jun 2009 10:21:03 GMT Freetext index over XML data Preliminaries Virtuoso provides a compact and efficient free text indexing capability for text and XML data. A free text index can be created on any character column, including wide and long data. If the column being indexed is XML data, this can be declared and enforced by the text index. XML data will be indexed specially to support efficient XPATH predicate evaluation with the xcontains predicate. create_freetext_index : CREATE TEXT [XML] INDEX ON q_table_name '(' column ')' [WITH KEY column] [NOT INSERT] [CLUSTERED WITH '(' column_commalist ')' ] [USING FUNCTION] [LANGUAGE STRING] ; The XML keyword specifies that the data is to be indexed as XML, hence element names and attributes will be processed separately for use with the XCONTAINS predicate. Example The script inserts the files into the table. The data is stored as text, not as persistent XML. A VSP page shows the list of rows in the table. Each row has the following links: XML source - send the XML text as escaped text enclosed in an HTML <pre> tag. Raw XML - send the XML to the user agent, which must be able to render XML. Making a freetext index Fri, 05 Jun 2009 10:21:03 GMT Freetext index over persistent XML data Example Take the table def from nwxml3.sql note the create text xml index .... The script inserts the files into the table. The data is stored as persistent XML. A VSP page shows the list of rows in the table. Each row has the following links: XML source - send the XML text as escaped text enclosed in an HTML <pre> tag. Raw XML - send the XML to the user agent. Must be able to render XML. Search on freetext indexed data Fri, 05 Jun 2009 10:21:03 GMT Search on freetext indexed data Example The example shows entity references using data from XS-S-1. The VSP page has a button for retrieving title elements from a selected document, where the title element contains a substring. The example uses the 'xpath-contains' predicate. The VSP page has a second button with the same function using text-contains. This illustrates that xcontains + text-contains does not go through links, but that xpath_contains + contains does. The example has a viewer for the source of each page so that the entity references can be seen. The page has some of the rows of the text table as persistent XML and others as text. The columns are declared as ANY in SQL. This illustrates the late binding dependent on representation. Storing of XSLT results Fri, 05 Jun 2009 10:21:03 GMT Search on freetext indexed data using xcontains predicate Example The example shows entity references using data from XS-S-1. The VSP page has a button for retrieving title elements from a selected document, where the title element contains a substring. The example uses the 'xpath contains' predicate. The VSP page has a second button with the same function using text-contains. This illustrates that xcontains + text-contains does not go through links, but that xpath_contains + contains does. The example has a viewer for the source of each page so that the entity references can be seen. The page has some of the rows of the text table as persistent XML and others as text. The columns are declared as ANY in SQL. This illustrates the late binding dependent on representation. Storing of XSLT results Fri, 05 Jun 2009 10:21:03 GMT Storing of XSLT results Example The sample shows storing of XSLT results into a table. The sample will take abstracts and other suitable subsections from the XS-S-1 data and insert them into another table. The VSP will show the source and result of the transformation side by side for any given document. The example uses data from XS-S-1, so it is important to set it first before trying this demo. XPATH interpreter Fri, 05 Jun 2009 10:21:03 GMT Applying the XPATH expression Example This example allows a selection from a document formed by XS-S-1, and applies a user-written XPATH expression to it. The page has selectable sample expressions including: Count of all titles Sum of the length of sect2 titles All first paragraphs of sect2's. The example uses xpath_eval, and iterates on the results if an array is produced. A single element of the XS-S-1 data is picked as the context node. A special button applies the operation on all rows with xpath_contains, showing the result set with doc name and result. This shows how one row can make multiple results with xpath_contains, but will return an array with xpath_eval. XSL-T transformation Fri, 05 Jun 2009 10:21:03 GMT Passing an XML entity as parameter to the XSL-T style-sheet Example This example uses the result from the XML parser in HTML mode to pass a parameter to the XSL-T style-sheet. The XSL-T style-sheet renders the target XML document. The section to be transformed is passed as the entity. Freetext indexing hooks Fri, 05 Jun 2009 10:21:03 GMT Building a freetext index using a custom PL indexing hooks Freetext Index The freetext index has a default mechanism for indexing and unindexing. By default indexing/unindexing is performed over data column. A custom PL hook can be made for indexing/unindexing, and be associated to the freetext index definition. The data from additional columns can be indexed in the custom PL hooks. Working together with off-band data columns to make index sensitive to changes in the additional columns data they should be declared as off-band data. Example The example SQL script does the following steps: Definition of the hooks Making of text index Using the xpath_eval() Fri, 05 Jun 2009 10:21:03 GMT Making the resultsets with xpath_eval() function Preliminaries The xpath_eval() function returns the result of applying the xpath expression to the context node. By default only the first result is returned, but supplying a third argument allows an index for the value to be specified. The default assumes a value of 1 here. A value of 0 returns an array of 0 or more elements, one for each value selected by the xpath expression. Examples The examples create an internal representation of the XML image using xml_tree_doc(). An xpath_eval() statement is then executed against the internal representation of the XML document specified as /ROOT/Customers which identifies the <Customers> nodes to be processed. The loop iterates over result for each attribute value (as CustomerID and ContactName in the first example) and retrieves the necessary values. Then the PL procedures calls result_names() and result() to send the result set to the client. The first example will produce (when applying via ISQL utility) CustomerID ContactName VARCHAR VARCHAR ________________________ VINET Paul Henriot LILAS Carlos Gonzalez Here is the result from execution of the second script: OrderID CustomerID OrderDate ProductID Quantity VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR ______________________________________________________________________ 10248 VINET 1996-07-04T00:00:00 11 12 10248 VINET 1996-07-04T00:00:00 42 10 10283 LILAS 1996-08-16T00:00:00 72 3 The VSP code displays the same data, but formats it into a table. The third example shows XML entities that have returned from first execution of the xpath_eval(); Overview Fri, 05 Jun 2009 10:21:03 GMT Mapping Schemas Mapping Schemas as XML views. XML views of relational data is similar to creating views by using CREATE VIEW statements. XML views can be created by using the XML Schema Definition (XSD) language, and then can be queried by using XML Path language (XPATH) queries or XML Query (XQUERY) queries with using xmlview function. An XSD schema with the special set of annotations is referred to as a mapping schema. These annotations are used within the XSD schema to specify XML data to relational store mapping. This includes mapping between elements and attributes in the XSD schema to tables (views) and columns in the databases. If you do not specify the annotations, default mapping takes place. By default, an XSD element with complex type maps to a table (view) name in the specified database and an element or attribute with a simple type maps to the column with the same name as the element/attribute. These annotations can also be used to specify the hierarchical relationships in XML (thus, representing the relationships in the database because XSD schemas are simply an XML view of relational data). A file containg a mapping schema may be loaded by calling the xml_load_mapping_schema_decl function. A name (without extension .xsd) of the file containg a mapping schema is considered to be the name of the xml view, defined by a given mapping schema. Fri, 05 Jun 2009 10:21:03 GMT Using sql:relation and sql:field Preliminaries The sql:relation annotation maps an XML node in the XSD schema to a database table. The name of a table (view) is specified as the value of the sql:relation annotation. When sql:relation is specified on an element, the scope of this annotation applies to all attributes and subelements that are described in the complex type definition of that element, therefore, providing a shortcut in writing annotations. The sql:relation annotation also may be used if identifiers that are valid in SQL are not valid in XML. For example, 'Order Details' is a valid table name in SQL, but not in XML. In such cases, the sql:relation annotation can be used to specify the mapping, for example: <xsd:element name="OrderDetails" sql:relation="Order Details"> ... The sql:field annotation maps an XML node in the schema to a database column. It's not allowed to specify sql:field on an empty content element. Example In this example, the XSD schema consists of an 'Emp' element of complex type with 'FirstName', 'LastName' and 'title' child elements and the 'EmpID' attribute. The sql:relation annotation maps the 'Emp' element to the Demo.demo.Employees table. The sql:field annotation maps the 'title' element to the 'Title' column and the 'EmpID' attribute to the "EmployeeID" column. No annotations are specified for the 'FirstName' and 'LastName' elements. This results in a default mapping of the elements to the columns with the same names. <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xsd:element name="Emp" sql:relation="Demo.demo.Employees" > <xsd:complexType> <xsd:sequence> <xsd:element name="FirstName" type="xsd:string" /> <xsd:element name="LastName" type="xsd:string" /> <xsd:element name="title" sql:field="Title" type="xsd:string" /> </xsd:sequence> <xsd:attribute name="EmpID" sql:field="EmployeeID" type="xsd:integer" /> </xsd:complexType> </xsd:element> </xsd:schema> Let the schema is written to the file 'EmpSchema.xsd', then after loading this file by xml_load_mapping_schema_decl function, the first example will produce a result for the XPath query: XPATH [__view 'EmpSchema'] /* the second example will produce a result for the XQuery query: select xquery_eval('<doc>{for $r in xmlview("EmpSchema")/* return $r}</doc>', xtree_doc('<q/>')); <doc> is necessary for serialization (any name instead 'doc' is possible) Fri, 05 Jun 2009 10:21:03 GMT Using sql:relationship to Specify Relationships Preliminaries In the annotated XSD schema, the sql:relationship annotation is used to nest the schema elements hierarchically, on the basis of primary key and foreign key relationships among the underlying tables to which the elements map. In specifying the sql:relationship annotation, you must identify: The parent table (Customers) and the child table (Orders). The necessary join condition. (CustomerID in Orders is a child key that refers to the CustomerID parent key in the Customers table.) This information is used in generating the proper hierarchy. (For each parent element, the related child elements appear as subelements.) To provide the table names and the necessary join information, the following attributes are specified on the sql:relationship annotation: 'name' specifies the unique name of the relationship; 'parent' specifies the parent relation (table). This is an optional attribute; if the attribute is not specified, the parent table name is obtained from information in the child hierarchy in the document. If the schema specifies two parent-child hierarchies that use the same <sql:relationship> but different parent elements, you do not specify the parent attribute in <sql:relationship>. This information is obtained from the hierarchy in the schema. 'parent-key' specifies the parent key of the parent. If the parent key is composed of multiple columns, values are specified with a space between them. There is a positional mapping between the values that are specified for the multicolumn key and for the corresponding child key. 'child' specifies the child relation (table). 'child-key' specifies the child key in the child referring to parent-key in parent. If the child key is composed of multiple attributes (columns), the child-key values are specified with a space between them. There is a positional mapping between the values that are specified for the multicolumn key and for the corresponding parent key. These attributes are valid only with the <sql:relationship> element. Example. Specifying the sql:relationship annotation on an element. The following annotated XSD schema includes 'Customer' and 'Order' elements. The 'Order' element is a subelement of the 'Customer' element. In the schema, the sql:relationship annotation is specified on the 'Order' subelement. The relationship itself is defined in the 'appinfo' element. The 'relationship' element identifies CustomerID in the Orders table as a foreign key that refers to the CustomerID primary key in the Customers table. Therefore, orders that belong to a customer appear as a subelement of that 'Customer' element. <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xsd:annotation> <xsd:appinfo> <sql:relationship name="CustOrders" parent="Demo.demo.Customers" parent-key="CustomerID" child="Demo.demo.Orders" child-key="CustomerID" /> </xsd:appinfo> </xsd:annotation> <xsd:element name="Customer" sql:relation="Demo.demo.Customers" type="CustomerType" /> <xsd:complexType name="CustomerType" > <xsd:sequence> <xsd:element name="Order" sql:relation="Demo.demo.Orders" sql:relationship="CustOrders" > <xsd:complexType> <xsd:attribute name="OrderID" type="xsd:integer" /> <xsd:attribute name="CustomerID" type="xsd:string" /> </xsd:complexType> </xsd:element> </xsd:sequence> <xsd:attribute name="CustomerID" type="xsd:string" /> <xsd:attribute name="ContactName" type="xsd:string" /> </xsd:complexType> </xsd:schema> Let the schema is written to the file 'CustOr_constant.xsd', then after loading this file by xml_load_mapping_schema_decl function, the first example will produce a result for the XPath query: XPATH [__view 'Customer_Order'] /Customer[@CustomerID="QUEEN"]; the second example will produce a result for the XQuery query: select xquery_eval('<doc>{for $r in xmlview("Customer_Order")/*[@CustomerID="QUEEN"] return $r}</doc>', xtree_doc('<q/>')) Fri, 05 Jun 2009 10:21:03 GMT Using sql:relationship to Specify Relationship on an attribute Example. The schema in this example includes a 'Customer' element with 'CustomerID' and 'ContactName' child elements and an OrderIDList attribute of IDREFS type. The 'Customer' element maps to the Customers table. By default, the scope of this mapping applies to all the child elements or attributes unless sql:relation is specified on the child element or attribute, in which case, the appropriate primary-key/foreign-key relationship must be defined using the 'relationship' element. And the child element or attribute, which specifies the different table using the relation annotation, must also specify the relationship annotation. <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xsd:annotation> <xsd:appinfo> <sql:relationship name="CustOrders" parent="Demo.demo.Customers" parent-key="CustomerID" child="Demo.demo.Orders" child-key="CustomerID" /> </xsd:appinfo> </xsd:annotation> <xsd:element name="Customer" sql:relation="Demo.demo.Customers" type="CustomerType" /> <xsd:complexType name="CustomerType" > <xsd:sequence> <xsd:element name="ContactName" type="xsd:string" /> <xsd:element name="CompanyName" type="xsd:string" /> <xsd:element name="City" type="xsd:string" /> </xsd:sequence> <xsd:attribute name="OrderIDList" type="xsd:IDREFS" sql:relation="Demo.demo.Orders" sql:field="OrderID" sql:relationship="CustOrders" > </xsd:attribute> <xsd:attribute name="CustomerID" type="xsd:string" /> </xsd:complexType> </xsd:schema> Let the schema is written to the file 'Cust_Order_attr.xsd', then after loading this file by xml_load_mapping_schema_decl function, the first example will produce a result for the XPath query: XPATH [__view 'Cust_Order_attr'] /Customer[@CustomerID="QUEEN"] the second example will produce a result for the XQuery query: select xquery_eval('<doc>{for $r in xmlview("Cust_Order_attr")/*[@CustomerID="QUEEN"] return $r}</doc>', xtree_doc('<q/>')); Fri, 05 Jun 2009 10:21:03 GMT Using sql:relationship to Specify Relationship on multiple elements. Example. Specifying sql:relationship on multiple elements. In this example, the annotated XSD schema contains the 'Customer', 'Order', and 'OD' elements. The 'Order' element is a subelement of the 'Customer' element. <sql:relationship> is specified on the 'Order' subelement; therefore, orders that belong to a customer appear as subelements of 'Customer'. The 'Order' element includes the 'OD' subelement. 'sql:relationship' is specified on 'OD' subelement, so the order details that pertain to an order appear as subelements of that 'Order' element. <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xsd:annotation> <xsd:appinfo> <sql:relationship name="CustOrders" parent="Demo.demo.Customers" parent-key="CustomerID" child="Demo.demo.Orders" child-key="CustomerID" /> <sql:relationship name="OrderOrderDetail" parent="Demo.demo.Orders" parent-key="OrderID" child="Demo.demo.Order_Details" child-key="OrderID" /> </xsd:appinfo> </xsd:annotation> <xsd:element name="Customer" sql:relation="Demo.demo.Customers" > <xsd:complexType> <xsd:sequence> <xsd:element name="Order" sql:relation="Demo.demo.Orders" sql:relationship="CustOrders" maxOccurs="unbounded" > <xsd:complexType> <xsd:sequence> <xsd:element name="OrderDetail" sql:relation="Demo.demo.Order_Details" sql:relationship="OrderOrderDetail" maxOccurs="unbounded" > <xsd:complexType> <xsd:attribute name="OrderID" type="xsd:integer" /> <xsd:attribute name="ProductID" type="xsd:string" /> <xsd:attribute name="Quantity" type="xsd:integer" /> </xsd:complexType> </xsd:element> </xsd:sequence> <xsd:attribute name="OrderID" type="xsd:integer" /> <xsd:attribute name="CustomerID" type="xsd:string" /> </xsd:complexType> </xsd:element> </xsd:sequence> <xsd:attribute name="CustomerID" type="xsd:string" /> </xsd:complexType> </xsd:element> </xsd:schema> Let the schema is written to the file 'Cust_Order_OD.xsd', then after loading this file by xml_load_mapping_schema_decl function, the first example will produce a result for the XPath query: XPATH [__view 'Cust_Order_OD'] /Customer[@CustomerID="QUEEN"]; the second example will produce a result for the XQuery query: select xquery_eval('<doc>{for $r in xmlview("Cust_Order_OD")/*[@CustomerID="QUEEN"] return $r}</doc>', xtree_doc('<q/>')) Fri, 05 Jun 2009 10:21:03 GMT Using sql:is-constant for creating constant elements Preliminaries The sql:is-constant annotation can be used to specify a constant element, an element in the XSD schema that does not map to any database table or column. sql:is-constant takes a Boolean value (false, true), 0 and 1 (0 = false, 1 = true). The is-constant annotation can be specified on an element that does not have any attributes. If this annotation is specified on an element with the value true (or 1), that element is not mapped to the database but still appears in the XML document. The sql:is-constant annotation can be added to a <complexType> element. Example. Specifying sql:is-constant to add a container element In this annotated XSD schema, 'CustomerOrders' is defined as a constant element by specifying the sql:is-constant attribute with the value of 1. Therefore, 'CustomerOrders' element is not mapped to any database table or column. This constant element consists of the 'Order' subelements. Although 'CustomerOrders' element does not map to any database table or column, it still appears in the resulting XML as a container element containing the 'Order' subelements. <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xsd:annotation> <xsd:appinfo> <sql:relationship name="CustOrders" parent="Demo.demo.Customers" parent-key="CustomerID" child="Demo.demo.Orders" child-key="CustomerID" /> </xsd:appinfo> </xsd:annotation> <xsd:element name="Customer" sql:relation="Demo.demo.Customers" > <xsd:complexType> <xsd:sequence> <xsd:element name="CustomerOrders" sql:is-constant="1" > <xsd:complexType> <xsd:sequence> <xsd:element name="Order" sql:relation="Demo.demo.Orders" sql:relationship="CustOrders" maxOccurs="unbounded" > <xsd:complexType> <xsd:attribute name="OrderID" type="xsd:integer" /> <xsd:attribute name="CustomerID" type="xsd:string" /> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> <xsd:attribute name="CustomerID" type="xsd:string" /> </xsd:complexType> </xsd:element> </xsd:schema> Let the schema is written to the file 'CustOr_constant.xsd', then after loading this file by xml_load_mapping_schema_decl function, the first example will produce a result for the XPath query: XPATH [__view 'CustOr_constant'] /*[@CustomerID="QUEEN"] the second example will produce a result for the XQuery query: select xquery_eval('<doc>{for $r in xmlview("CustOr_constant")/*[@CustomerID="QUEEN"] return $r}</doc>', xtree_doc('<q/>')) Fri, 05 Jun 2009 10:21:03 GMT Filtering Values by Using sql:limit-field and sql:limit-value Preliminaries Rows returned from a database query can be limited on the basis of some limiting value. The sql:limit-field is used to identify the database column that contains limiting values and sql:limit-value annotations is used to specify a specific limiting value to be used to filter the data returned. The sql:limit-value annotation is optional. If sql:limit-value is not specified, a NULL value is assumed. Example. This is the mapping schema in which the 'product_Chai' schema attribute maps to the 'ProductID' column in the 'Demo.demo.Products' relation. The values that are returned for this attribute are limited to only 'ProductName' having the 'Chai' value by specifying the sql:limit-field and sql:limit-value annotations. Similarly, the 'product_Chang' schema attribute returns only the 'ProductID' limited to 'ProductName' having the 'Chang' value. <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xsd:annotation> <xsd:appinfo> <sql:relationship name="CategoryProduct" parent="Demo.demo.Categories" parent-key="CategoryID" child="Demo.demo.Products" child-key="CategoryID" /> </xsd:appinfo> </xsd:annotation> <xsd:element name="category" sql:relation="Demo.demo.Categories" type="CategoryType" /> <xsd:complexType name="CategoryType" > <xsd:sequence> <xsd:element name="product_Chai" type="xsd:string" sql:relation="Demo.demo.Products" sql:field="ProductID" sql:limit-field="ProductName" sql:limit-value="Chai" sql:relationship="CategoryProduct" > </xsd:element> <xsd:element name="product_Chang" type="xsd:string" sql:relation="Demo.demo.Products" sql:field="ProductID" sql:limit-field="ProductName" sql:limit-value="Chang" sql:relationship="CategoryProduct" > </xsd:element> </xsd:sequence> <xsd:attribute name="CategoryID" type="xsd:integer" /> <xsd:attribute name="description" sql:field="Description" type="xsd:string" /> </xsd:complexType> </xsd:schema> Let the schema is written to the file 'Cat_Product.xsd', then after loading this file by xml_load_mapping_schema_decl function, the first example will produce a result for the XPath query: XPATH [__view 'Cat_Product'] /*[@CategoryID=1] the second example will produce a result for the XQuery query: select xquery_eval('<doc>{for $r in xmlview("Cat_Product")/*[@CategoryID=1] return $r}</doc>', xtree_doc('<q/>')) Genaral Fri, 05 Jun 2009 10:21:03 GMT Using XPath queries to XML views. Overview. XPATH Implementation and SQL Virtuoso offers XPATH as a query language for XML views. The statement is there converted into SQL in the context of the mapping defined by the __view XPATH option, which is mandatory. An XPATH query string is a valid top level SQL statement. This is interpreted as a single select or union of selects with the result columns being specified by various XPATH options. The basic query string XPATH [__view "xmlview_name"] /xpath_query The same functionality can be got by the following query: select * from (XPATH '[__view "xmlview_name"]/xpath_query')n Xml view can be created by two methods: using CREATE XML VIEW statement using Annotated XSD Schemas The second method is described in 'Using Annotated XSD Schemas for Creating XML View' chapter of this tutorial. CREATE XML VIEW statement The XML view declaration establishes a 'virtual document' a context within which XML hierarchy relationships can be translated into arbitrary joins. The virtual document can be then materialized into an actual set of persistent XML elements or used to generate SQL from XPATH. The XML view declaration corresponds to the grammar rules described in XML Support chapter, Virtuoso XML Services section. Each table in the declaration generates an element into the result document. SQL views can be used as tables to accommodate for hidden joins, sub-queries, ordering and aggregates. If a view is used, which by nature has no primary key, the primary key clause should be used to define a uniquely identifying set of view columns. Each level of the hierarchy is declared as a list of child elements. Each such element maps one table or view into an entity according to a join condition. The join conditions can reference columns from the associated table and columns from tables in parent elements. The join condition can also have scalar filtering conditions. A top element's join condition may only specify scalar conditions. Each set of sibling child nodes is delimited by braces {}. The top level of the view typically consists of one element in the outermost braces. This element has itself a child list delimited by braces. Each such list can have more than one different element. Each element specifies: SQL table Correlation name for use in subsequent joins for this table XML element name to use for delimiting a row of this table List of columns, with optional XML element or attribute names join condition - will relate rows of this table to rows of the table in the enclosing element. If this element is at the top level, this can only consist of scalar conditions Optional PRIMARY KEY clause, needed if the table in this element is a view, does not have a primary key or if a non-primary key unique identity is desired Optional ELEMENT flag Optional list of child elements, delimited by braces The column list can mention a single column or a single column renamed into an XML attribute of a different name. If a column of a table is referenced in a subsequent join condition it must appear in the output columns list. Expressions are not directly allowed but a view with expression columns can be used. The opt_public clause, when present, offers a shorthand for calling xml_view_publish at the same time as making the definition. This makes a DAV resource reflecting the contents of the view. The contents may either be generated on demand or persisted as a DAV accessible XML document. In the latter case the document may be regenerated at a fixed interval. The interval is expressed in minutes. The path is expressed as an absolute path from the root collection of the DAV server. Examples create xml view "cat" as { "Demo"."demo"."Categories" "C" as "category" ("CategoryID", "Description" as "description") { "Demo"."demo"."Products" "P" as "product" ("ProductName") on ("P"."CategoryID" = "C"."CategoryID") } } This declares a two level hierarchy with a category node for each category and a product child node for each product in the category. create xml view "cats_e" as select "category"."CategoryID", "CategoryName", "ProductName", "ProductID" from "Demo".."Categories" "category", "Demo".."Products" as "product" where "product"."CategoryID" = "category"."CategoryID" element; Here is a similar example, this time using the element option. create xml view "product" as { "Demo"."demo"."Products" p as "product" ("ProductID", "ProductName" as "product_name","UnitPrice" as "price", "SupplierID","CategoryID") { "Demo"."demo"."Suppliers" s as "supplier" ("CompanyName") on (s."SupplierID" = p."SupplierID") , "Demo"."demo"."Categories_aux" c as "category" ("Description") on (c."CategoryID" = p."CategoryID") } } This declares a two level hierarchy with a product node for each product and a supplier and category children node for each supplier and category in the product. Examples Fri, 05 Jun 2009 10:21:03 GMT Using XPath queries with options. XML serialization text You can get xml serialization text of the selected entity by the query XPATH [__view "xmlview_name"] /xpath_query e.g. for given 'cat' xml view the full serialization text can be received by the query XPATH [__view "cat"] /* or XPATH [__view "cat"] /category see xp_v_2_sample Select all columns instead of a serialization text You can select all columns of the selected entity instead of its serialization text by using __* option: XPATH [__* __view "cat"] //product ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued INTEGER VARCHAR INTEGER INTEGER VARCHAR DOUBLE PRECISION SMALLINT SMALLINT SMALLINT SMALLINT ______________________________________________________________________________________________________________________ 1 Chai 1 1 10 boxes x 20bags 8 39 0 10 0 2 Chang 1 1 24 - 12 oz bottles 19 17 40 25 0 3 Aniseed Syrup 1 2 12 - 550 ml bottles 10 13 70 25 0 . . . . . . This is only valid when __view is specified and the result set is homogeneous. Select the key instead of the serialization text You can select the key of the selected entities instead of the serialization text by using __key option: XPATH [__key __view "cat"] /*; CategoryID INTEGER NOT NULL _______________________________________________________________________________ 1 2 3 4 5 6 7 8 Examples Fri, 05 Jun 2009 10:21:03 GMT Using XPATH in SQL Queries and Procedures. Example An XPATH expression can appear as a SQL query expression, that is, as a derived table or subquery predicate or scalar subquery. This means that the XPATH expression is expanded compile time to the corresponding SQL. The query select * from (XPATH '[__* __view "DB"."DBA"."cat"]//product/@ProductName') P order by P."ProductName" will evaluate the //product query in the context of the 'cat' XML view and produce a result set consisting of the ordered 'ProductName' attributes of the product entity as defined in the view: ProductName VARCHAR _______________________________________________________________________________ Alice Mutton Aniseed Syrup Boston Crab Meat Camembert Pierrot Carnarvon Tigers Chai Chang Chartreuse verte . . . . . . Zaanse koeken General Fri, 05 Jun 2009 10:21:03 GMT Updategrams Updategram Overview Updategrams allow database updates to be defined as XML. This is ultimately achieved by mapping the XML nodes against corresponding database columns. Updategrams can be used to replace existing data access components in a middle tier. A typical application will include a middle tier consisting of Business Logic and Data Access code. The Data Access code will interface with the database using disconnected Recordsets and Command objects calling stored procedures etc. Most of the Data Access section of the middle tier can be replaced with Updategrams. Most Data Access tiers (both middle tier code and stored procedures) will individually deal with specific database tables or groups of related tables. This can inhibit performance and quite often several round trips to the database are required to complete a transaction. Updategrams can solve this problem by including all of the data in an XML document, which is then mapped to database tables and columns. The entire database update can then be accomplished in one fell swoop. This update can include inserting, updating and deleting data. The 'xmlsql_update' function supports XML-based insert, update, and delete operations performed on an existing table in database. Updategram Basics The general format of an updategram is: <sql:sync xmlns:sql="xml-sql"> <sql:before> <TABLENAME [sql:id="value"] col="value" col="value"?../> </sql:before> <sql:after> <TABLENAME [sql:id="value"] [sql:at-identity="value"] col="value" col="value"?../> </sql:after> </sql:sync> or <sql:sync xmlns:sql="xml-sql"> <sql:before> <TABLENAME [sql:id="value"]> <col>"value"</col> <col>"value"</col> ... </TABLENAME> ... </sql:before> <sql:after> <TABLENAME [sql:id="value"] [sql:at-identity="value"]> <col>"value"</col> <col>"value"</col> ... </TABLENAME> ... </sql:after> </sql:sync> Elements Description The <sync> tag of the updategram signifies the beginning of an operation(s). The rows specified in the <before> refer to existing records in the database. The rows specified in the <after> block refer to what the user wants in the database. TABLENAME identifies target table. The sql:at-identity attribute stores the last identity value added by the system (if possible). The captured identity value can then be used in subsequent operations. The sql:id attribute is used to mark rows. This forces an association between the record specified in the <before> and <after> block in the updategram. When there are multiple instances specified, it is recommended that sql:id attribute be used for all of the instances. Each TABLENAME refers to a single table. Multiple <TABLENAME..../> entries are allowed in the same <before> , or <after> tags, or in both <before> and <after> tags; however, nesting is not allowed. The <before> and <after> tags are optional. A missing tag is the same as having a tag with no content. Determining Actions In an updategram if only the <after> block is specified, the rows specified in the <after> block are inserted in the table(s). If both the <before> and <after> blocks are specified, then the rows specified in the <after> block for which there is no corresponding rows in the <before> block are inserted in the table(s). In an update operation, the instances (rows) specified in the <before> block refer to the existing rows in the database. The corresponding instances (rows) in the <after> block reflect what the user wants in the database. A row update operation is performed if there is an instance (row) in both <before> and <after> sections with the same set of values for the attributes that uniquely identify a row in a table. The set of rows specified in the <before> block must be valid in the database for the updategram to successfully update the rows. In a delete operation, if only the <before> block is specified in the updategram, the instances (rows) specified in the <before> block are deleted from the table(s). If both the <before> and <after> blocks are specified, the instances (rows) for which there is no corresponding instances (rows) in the <after> block are deleted from the table(s). Example This example creates a SQL function that takes the orders and order lines from the demo database and applies a stylesheet to the FOR XML rendition of these to make updategrams. Use the updategrams to fill out a similar table structure containing order summary with only fulfilled orders included. The interface will show the sources of the report and the transformed report which is the updategram. Exercises Fri, 05 Jun 2009 10:21:03 GMT Parameter usage in updategrams Preliminaries This sample program demonstrates the use of parameters in an updategram: Create a form that allows new records to be inserted into a table. Use the form text fields as input parameters for the updategram. After insert display a list of records to show that new record has been inserted into the table. Using a XML temapltes Fri, 05 Jun 2009 10:21:03 GMT Row Insertion and Report Generation using XML Templates Preliminaries This sample program demonstrates the use of XML templates: Use the XS_U_3 SQL script to create a Web virtual directory that allows execution of the XML template. Create a form that allows new records to be inserted into a table. Use the form text fields as input parameters for the XML template. The XML template (shippers.xml) will insert a new record using the form parameters. It makes an XML document using SQL/XML query, and renders it with an XML-T style sheet. General Fri, 05 Jun 2009 10:21:03 GMT FOR XML SQL Clause For XML SQL Clause Overview This feature enables the execution SQL queries against the Virtuoso Server to return results as XML documents rather than as standard rowsets. To retrieve results directly, use the FOR XML clause of the SELECT statement and in the FOR XML clause, specify one of these XML modes: RAW - transforms each row in the query result set into an XML element with the generic identifier row. AUTO - returns query results as nested XML elements. EXPLICIT - authors of query can control the structure of the XML document returned by the query. Examples This example shows a simple use of the For XML SQL 'Auto' statement for executing a query and returning the results in XML format. General Fri, 05 Jun 2009 10:21:03 GMT FOR XML EXPLICIT SQL statements Example This sample demonstrates the use of the FOR XML EXPLICIT mode. Creating a publication Fri, 05 Jun 2009 10:21:01 GMT Creating a replication publication account Preliminaries Virtuoso supports bidirectional transactional replication via mechanism of updateable subscriptions. Every table has only one publisher. Subscribers can update replicated tables on their side and then submit data back to publisher. Publisher performs conflict resolution and either accepts or rejects that data. It is assumed that all the tables in publication with updateable subscriptions option have primary keys and that primary key columns are never modified. Example publication account setup Login to the Conductor UI using the dba account. Go to the "Replication" tab, then go to the "Transactional" tab and then go the "Publications" sub-tab. Press the "Create" button. Enter publication name, check the "Updateable" checkbox and press the "Create" button. Equivalent SQL commands to above Connect to the Virtuoso server via ISQL utility as DBA user. Create publication "repldemo" with log file "repldemo.log" SQL> repl_publish('repldemo', 'repldemo.log', 1); Creating a publication Fri, 05 Jun 2009 10:21:01 GMT Adding tables to publication To add a table to publication execute the following steps: Login to the Conductor UI using the dba account. Go to the "Replication" tab, then go to the "Transactional" tab and then go the "Publications" sub-tab. From the publications list select the link with the name of your publication ("repldemo"). Press the "Add Tables" button. Select a database, then a table or multiple tables (select "Demo.demo.Orders"), and then press "Add to Publication" button. Equivalent SQL commands to above Connect to the Virtuoso server via ISQL utility as DBA user. Add table "Demo.demo.Orders" to publication "repldemo" SQL> repl_pub_add('repldemo', 'Demo.demo.Orders', 2, 1, 0); Creating a publication Fri, 05 Jun 2009 10:21:01 GMT Defining conflict resolvers Preliminaries Because every table can have only one publisher, conflicts can occur only on publisher (when modifications from subscriber are applied). Every table on publisher can have a number of conflict resolvers which are used for conflict resolution. Each conflict resolver has a type ('I', 'U', or 'D') and an order. Conflict resolvers are applied in ascending order. Conflict resolver is a Virtuoso/PL procedure which receives conflicting row (row from subscriber) and some other arguments. Conflict resolver signatures are described in Virtuoso documentation. Conflict resolver can modify the row which is passed in as 'inout' arguments. Conflict resolver should return an integer value which will be used for conflict resolution. Possible return values and their meaning are described in Virtuoso documentation. Conflict resolution occurs differently for each kind of DML operation. Details can be found in Virtuoso documentation. There is a possibility to automatically generate some types of conflict resolvers. Automatically generated conflict resolver classes are: max - row with maximum value of specified column wins min - row with minumum value of specified column wins ave - new value of specified column is calculated as: current_val = (current_val + new_val) / 2 add - new value of specified column is calculated as: current_val = current_val + (new_val - old_val) pub_wins - publisher always wins sub_wins - subscriber always wins Example defining a conflict resolver Login to the Conductor UI using the dba account. Go to the "Replication" tab, then go to the "Transactional" tab and then go the "Publications" sub-tab. Select the link with the name of your publication ("repldemo"). Select the link with the name of the table ("Demo.demo.Orders"). Press the "New Resolver" button. Enter conflict resolver name suffix for example "min_OrderDate", select conflict resolver type (select "U"), select conflict resolver class (select "min"), select a column (select "OrderDate"). You can optionally specify conflict resolver order (default is 100). Press "Add" button. A conflict resolver will be added. You can view and edit conflict resolver code by selecting the link with its name from the list of conflict resolvers. Equivalent SQL commands to above Connect to the Virtuoso server via ISQL utility as DBA user. Create 'U' conflict resolver for table "Demo.demo.Orders" that will choose a row with minimal OrderDate column. SQL> repl_add_cr('Demo.demo.Orders', 'min_OrderDate', 'U', 100, 'min', 'OrderDate'); Creating a subscription Fri, 05 Jun 2009 10:21:01 GMT Creating a subscription Preliminaries In order to run this example you need two Virtuoso servers running (publisher and subscriber). In the example scripts it is assumed that the first Virtuoso server (publisher) is running at localhost:1111 and the second Virtuoso server (subscriber) is running at localhost:1112. If you are running ODBC versions of the servers there should be 2 ODBC DSN's records named "localhost:1111" and "localhost:1112". Example creating a subscription These steps should be executed on subscriber. Login to the Conductor UI using the dba account. Go to the "Replication" tab, then go to the "Transactional" tab and then go the "Subscriptions" sub-tab. Press "New Subscription" button. Select connected DSN from the list and then press "Publications list" button. If the desired DSN is not connected yet you can connect to it using "Specify new data source" dialog below. Select a publication and press "List items" button. Press "Subscribe" button. You can optionally load initial data by checking "Load data" checkbox. Equivalent SQL commands to the above Connect to the subscriber via ISQL utility as DBA user. Define a replication server "demoserver" at "localhost:1111" and subscribe to publication "repldemo" from it. SQL> repl_server('demoserver', 'localhost:1111', 'localhost:1111'); SQL> repl_subscribe('demoserver', 'repldemo', 'dba', 'dba', 'dba', 'dba'); Publishing a table Fri, 05 Jun 2009 10:21:01 GMT Publishing a table for replication Preliminaries Bidirectional snapshot replication allows you to set up snapshot replication between multiple servers where updates can be performed on all servers. Publisher-subscriber model where each table has only one publisher and when an update is performed on subscriber it goes to publisher first, and then to all other subscribers. Conflict resolution may need to take place on the publisher when data coming from a subscriber is processed. It is assumed that all published tables have primary keys and that primary key columns are never modified. Example publishing a table Login to the Conductor UI using the dba account. Go to the "Replication" tab and then go to the "Bidirectional Snapshot" tab. Press the "Add table" button. Enter table name or select the table using "Browse..." button and press "Add" button. Equivalent SQL commands to above Connect to the Virtuoso server via ISQL utility as DBA user. Publish "Demo.demo.Orders" table. SQL> repl_create_snapshot_pub ('Demo.demo.Orders', 2); Publishing a table Fri, 05 Jun 2009 10:21:01 GMT Defining conflict resolvers Preliminaries Because every table can have only one publisher conflicts can occur only on publisher (when modifications from subscriber are applied). Every table on publisher can have a number of conflict resolvers which are used for conflict resolution. Each conflict resolver has a type ('I', 'U', or 'D') and an order. Conflict resolvers are applied in ascending order. Conflict resolver is a Virtuoso/PL procedure which receives conflicting row (row from subscriber) and some other arguments. Conflict resolver signatures are described in Virtuoso documentation. Conflict resolver can modify the row which is passed in as 'inout' arguments. Conflict resolver should return an integer value which will be used for conflict resolution. Possible return values and their meaning are described in Virtuoso documentation. Conflict resolution occurs differently for each kind of DML operation. Details can be found in Virtuoso documentation. There is a possibility to automatically generate some types of conflict resolvers. Automatically generated conflict resolver classes are: max - row with maximum value of specified column wins min - row with minumum value of specified column wins ave - new value of specified column is calculated as: current_val = (current_val + new_val) / 2 pub_wins - publisher always wins sub_wins - subscriber always wins Example defining a conflict resolver Login to the Conductor UI using the dba account. Go to the "Replication" tab and then go to the "Bidirectional Snapshot" tab. Click "Conflict Resolvers" for the desired published table ("Demo.demo.Orders"). Press "New Resolver" button. Enter conflict resolver name suffix (enter "max_OrderDate"), select conflict resolver type (select "U"), select conflict resolver class (select "max"), select a column (select "OrderDate"). You can optionally specify conflict resolver order (default is 100). Press "Add" button. A conflict resolver will be added. You can view and edit conflict resolver code by selecting a link with its name from the list of conflict resolvers. Equivalent SQL commands to above Connect to the Virtuoso server via ISQL utility as DBA user. Create 'U' conflict resolver for table "Demo.demo.Orders" that will choose a row with latest OrderDate. SQL> REPL_ADD_SNAPSHOT_CR ('Demo.demo.Orders', 'max_OrderDate', 'U', 100, 'max', 'OrderDate'); Defining subscribers for published table Fri, 05 Jun 2009 10:21:01 GMT Defining subscribers Preliminaries In order to run this example you need two Virtuoso servers running (publisher and subscriber). In the example scripts it is assumed that the first Virtuoso server (publisher) is running at localhost:1111 and the second Virtuoso server (subscriber) is running at localhost:1112. If you are running ODBC versions of the servers there should be 2 ODBC DSN's records named "localhost:1111" and "localhost:1112". Subscriber should have "ServerName" configuration parameter set to "demoserver2". Example creating a subscription These steps should be executed on publisher. Login to the Conductor UI using the dba account. Go to the "Replication" tab and then go to the "Bidirectional Snapshot" tab. Click "Subscriptions" for desired published table ("Demo.demo.Orders"). Press "New Subscriber" button. Select connected DSN from the list and then press "Create Subscription" button. If the desired DSN is not connected yet you can connect to it using "Specify new data source" dialog below. Equivalent SQL commands to above Connect to the publisher via ISQL utility as DBA user. Define a replication server "demoserver2" at "localhost:1112" and subscribe it to published "Demo.demo.Orders" table. SQL> repl_snp_server('localhost:1112'); SQL> repl_create_snapshot_sub('demoserver2', 'Demo.demo.Orders', 2, 'dba', 'dba'); Bidirectional Snapshot Replication Demo Fri, 05 Jun 2009 10:21:01 GMT Bidirectional snapshot replication demo Preliminaries This tutorial demonstrates basic snapshot replication features. In order to run this tutorial execute the following steps: Setup ODBC data source named "demoserver2". Make sure that user "demo" with password "demo" can connect to data source "demoserver2". Make sure that there is no table named "Shippers" in target data source. The data source can be Virtuoso data source as well as any other data source type which is supported by Virtuoso bidirectional snapshot replication (E.g. Oracle or MS SQL data source). This tutorial will demonstrate Virtuoso heterogeneous snapshot replication capabilities. Publishing a DAV collection Fri, 05 Jun 2009 10:21:01 GMT Publishing a DAV collection for replication Preliminaries Bidirectional snapshot replication allows you to set up snapshot replication between multiple servers where updates can be performed on all servers. Publisher-subscriber model where each DAV collection has only one publisher and when an update is performed on subscriber it goes to publisher first, and then to all other subscribers. Conflict resolution may need to take place on the publisher when DAV resources coming from a subscriber are processed. Example publishing a DAV collection Login to the Conductor UI using the dba account. Go to the "Replication" tab and then go to the "Bidirectional Snapshot" tab. Press "Add DAV Collection" button. Enter DAV collection name or select DAV collection using "Browse..." button and press "Add" button. Equivalent SQL commands to above Connect to the Virtuoso server via ISQL utility as DBA user. Publish "/DAV/doc" DAV collection. SQL> repl_create_snapshot_pub ('/DAV/doc', 1); Publishing a DAV collection Fri, 05 Jun 2009 10:21:01 GMT Defining conflict resolvers Preliminaries Every DAV collection on publisher can have a number of conflict resolvers which are used for conflict resolution. Each conflict resolver has an integer number (order) associated with it. Conflict resolvers are applied in ascending order. Conflict resolver is a Virtuoso/PL procedure which receives conflicting DAV resource. DAV conflict resolver signatures are described in Virtuoso documentation. Conflict resolver can modify the resource which is passed in as 'inout' arguments. Conflict resolver should return an integer value which will be used for conflict resolution. Possible return values and their meaning are described in Virtuoso documentation. There is a possibility to automatically generate some types of conflict resolvers. Automatically generated conflict resolver classes are: max_mtime - resource with maximum modification time wins min_mtime - resource with minumum modification time wins max_ctime - resource with maximum creation time wins min_ctime - resource with minumum creation time wins backup - backup of resource that lost conflict resolution will be performed, conflict resolution will continue notify - owner of resource will be notified if his resource lost conflict resolution pub_wins - publisher always wins sub_wins - subscriber always wins Example defining a conflict resolver Login to the Conductor UI using the dba account. Go to the "Replication" tab and then go to the "Bidirectional Snapshot" tab. Click "Conflict Resolvers" for desired published DAV collection ("/DAV/doc"). Press "New Resolver" button. Enter conflict resolver name suffix (enter "max_mtime"), select conflict resolver class (select "max_mtime"). You can optionally specify conflict resolver order (default is 100). Press "Add" button. A conflict resolver will be added. You can view and edit conflict resolver code by selecting the link with its name from the list of conflict resolvers. Equivalent SQL commands to above Connect to the Virtuoso server via ISQL utility as DBA user. Create a conflict resolver for collection "/DAV/doc" that will choose a resource with maximum modification time (more recent resource). SQL> REPL_ADD_DAV_CR ('/DAV/doc', 'max_mtime', 100, 'max_mtime'); Defining subscribers for a pulished DAV collection Fri, 05 Jun 2009 10:21:01 GMT Defining subscribers Preliminaries In order to run this example you need two Virtuoso servers running (publisher and subscriber). In the example scripts it is assumed that the first Virtuoso server (publisher) is running at localhost:1111 and the second Virtuoso server (subscriber) is running at localhost:1112. If you are running ODBC versions of the servers there should be 2 ODBC DSN's records named "localhost:1111" and "localhost:1112". Subscriber should have "ServerName" configuration parameter set to "demoserver2". Example creating a subscription These steps should be executed on publisher. Login to the Conductor UI using the dba account. Go to the "Replication" tab and then go to the "Bidirectional Snapshot" tab. Click "Subscriptions" for desired published DAV collection ("/DAV/doc"). Press "New Subscriber" button. Select connected DSN from the list and then press "Create Subscription" button. If desired DSN is not connected yet you can connect to it using "Specify new data source" dialog below. Equivalent SQL commands to above Connect to the publisher via ISQL utility as DBA user. Define a replication server "demoserver2" at "localhost:1112" and subscribe it to published "/DAV/doc" DAV collection. SQL> repl_snp_server('localhost:1112'); SQL> repl_create_snapshot_sub('demoserver2', '/DAV/doc', 1, 'dba', 'dba'); SyncML Server Fri, 05 Jun 2009 10:21:01 GMT Setting up SyncML server Preliminaries SyncML is a protocol for syncronizing data between two devices called server and client. Usually as client we may consider PDA, mobile phone or a workstation that have the ability to syncoronize their contacts, calendar etc. databases using SyncML protocol. On the other hand the server have ability to recoginize such requests and to store the data into the repository. Basically this is a protocol for data replication, oriented to the portable devices. The usual message flow is: Initial request from client Server response asking for credentials, if not supplied Client sends credentials included in the request Server accepts or rejects the sync request. When request for sync is accepted, then server checks what sync to perform: slow two-way (full data) or two-way sync. Client send the items that are changed or all items depending of sync type Server sends items that are changed or all items depending of sync type requested. The Virtuoso WebDAV repository can be used to keep such data. The server will recognize SyncML request by MIME content type 'application/vnd.syncml+xml' or 'application/vnd.syncml+wbxml' upon POST request and will act then as SyncML server over HTTP. Shortly: on server side are needed: 1.WebDAV collection with permisions for given accounts 2.Virtual directory assigned to it. Optionally virtual host can be defined in order to work with clients that do not have the ability to specify URL, such clients can work only with virtual root collection. Note that WebDAV collection(s) creation is a vital step; apropriate rights MUST be set before using it with SyncML client; clients can only store items(resources), they can't make sub-folders. Instructions for setting SyncML server and client Create a new folder named '/DAV/sync/' under WebDAV root. This can be done via Conductor UI going to the "Web Application Server" tab and then going to the "Content Management" tab. Create subfolders calendar and contacts under /DAV/sync/ Create a new virtual host with virtual root assigned to /DAV/sync/ WebDAV collection. Please reffer to the documentation for more information. Nokia 92x0 Communicator In Extras menu open "Remote sync" In "Profile settings" choose "New Profile" Select apropriate internet access Enter the Virtuso HTTP server address and port values as you created them earlier. Enter username and password for account which have permissions to the '/DAV/sync/' and descendants. Select tab "Data" Choose Calendar and enter for "Remote calendar" './calendar/'. Choose Contacts and enter for "Remote contacts" './contacts/'. Close the Sync Profile menus, confirm saving Select "Sync" option. Verify that items are uploaded into WebDAV collections: /DAV/sync/calendar/ and /DAV/sync/contacts/. For this you can use Conductor's WebDAV content management UI Edit some of Contacts/Events on device and perform Sync again. Check the repository as in previous step. Note: Editing the items on the server needs knowlage of VCARD/VCALENDAR formats as they can be edited as text files. Siemens SX1 Select "menu" button Choose "Organizer" Choose "Sync" Select from menu "Options", "New sync profile" Give a meaningful name for this profile in "Sync profile name" Bearer type: Internet Access point: choose some predefined Host address: URL to the SyncML server without HTTP port number Port: SyncML HTTP port number User name: username for account which have permissions to the '/DAV/sync/' and descendants. Password: password for the account Calendar: Yes Remote calendar: ./calendar Contacts: Yes Remote contacts: ./contacts HTTP authentication: No HTTP user name: blank HTTP password: blank Select "Back" from menu New profile should exists. Click on it to perform syncronization. Sony Ericsson P900 From main menu choose "Remote Sync" Via menu "Edit" - "Preferences" Server address: [type URL to the SyncML server] User Name: Password: Press button "Done" Click on "Contacts" Enable task: on Server database: ./contacts Press button "Done" To perform Sync on defined items, press button "Sync" Important: Internet connection must be alredy defined and must work with device's browser. Replication Demo between two Datsources (DSNs) Fri, 05 Jun 2009 10:21:01 GMT Replication Demo between two Datsources (DSNs) Preliminaries This sample shows how to replicate two heterogeneous DSNs (MySQL DSN and Microsoft SQL Server DSN for example) with all logic in Virtuoso. The replicated table has two columns. First is varchar Primary key, second integer representing user data. Sample includes: Select DSN for demo. There must be two connected DSNs to start the sample. Create tables on the two DSNs. Attach tables from these DSNs. Show simple page with table content. How it works: The Primary key plus timestamp column from source is replicated locally. On initial state all date from the first DSN s copyed to the Second. The user table on the first DSN has one timestamp column more that the table on the second. This column is used to get new / changed rows on replication state. Instructions for setting: Click on the "Set Initial State" to create virtuoso table. Click on the "Run" links to actually experience the demo. Important: This sample is not thread save. RDF Views Fri, 05 Jun 2009 10:21:00 GMT Develop custom RDF views for NorthWind database. Concept RDF Views map relational data into RDF and allow customizing RDF representation of locally stored RDF data. To let SPARQL clients access relational data as well as physical RDF graphs in a single query, we introduce a declarative Meta Schema Language for mapping SQL Data to RDF Ontologies. As a result, all types of clients can efficiently access all data stored on the server. The mapping functionality dynamically generates RDF Data Sets for popular ontologies such as SIOC, SKOS, FOAF, and ATOM/OWL without disruption to the existing database infrastructure of Web 1.0 or Web 2.0 solutions. RDF views are also suitable for declaring custom representation for RDF triples, e.g. property tables, where one row holds many single-valued properties. The Virtuoso RDF Views meta schema is a built-in feature of Virtuoso's SPARQL to SQL translator. It recognizes triple patterns that refer to graphs for which an alternate representation is declared and translates these into SQL accordingly. The main purpose of this is evaluating SPARQL queries against existing relational databases. There exists previous work from many parties for rendering relational data as RDF and opening it to SPARQL access. We can mention D2RQ, SPASQL, Squirrel RDF, DBLP and others. The Virtuoso effort differs from these mainly in the following: Integration with a triple store. Virtuoso can process a query for which some triple patterns will go to local or remote relational data and some to local physical RDF triples. SPARQL query can be used in any place where SQL can. Database connectivity protocols are neutral to the syntax of queries they transmit, thus any SQL client, e.g. JDBC, ODBC or XMLA application, can send SPARQL queries and fetch result sets. Moreover, a SQL query may contain SPARQL subqueries and SPARQL expressions may use SQL built-in functions and stored procedures. Integration with SQL. Since SPARQL and SQL share the same run time and query optimizer, the query compilation decisions are always made with the best knowledge of the data and its location. This is especially important when mixing triples and relational data or when dealing with relational data distributed across many outside databases. No limits on SPARQL. It remains possible to make queries with unspecified graph or predicate against mapped relational data, even though these may sometimes be inefficient. Coverage of the whole relational model. Multi-part keys etc. are supported in all places. Quad Map Patterns, Value and IRI Classes In the simplest sense, any relational schema can be rendered into RDF by converting all primary keys and foreign keys into IRI's, assigning a predicate IRI to each column, and an rdf:type predicate for each row linking it to a RDF class IRI corresponding to the table. Then a triple with the primary key IRI as subject, the column IRI as predicate and the column's value as object is considered to exist for each column that is neither part of a primary or foreign key. Strictly equating a subject value to a row and each column to a predicate is often good but is too restrictive for the general case. Multiple triples with the same subject and predicate can exist. A single subject can get single-valued properties from multiple tables or in some cases stored procedures. An IRI value of a subject or other field of a triple can be composed from more than one SQL value, these values may reside in different columns, maybe in different joined tables. Some table rows should be excluded from mapping. Thus in the most common case the RDF meta schema should consist of independent transformations; the domain of each transformation is a result-set of some SQL SELECT statement and range is a set of triples. The SELECT that produce the domain is quite simple: it does not use aggregate functions, joins and sorting, only inner joins and WHERE conditions. There is no need to support outer joins in the RDF meta schema because NULLs are usually bad inputs for functions that produce IRIs. In the rare cases when NULLs are OK for functions, outer joins can be encapsulated in SQL views. The range of mapping can be described by a SPARQL triple pattern: a pattern field is a variable if it depends on table columns, otherwise it is a constant. Values of variables in the pattern may have additional restrictions on datatypes, when datatypes of columns are known. This common case of an RDF meta schema is implemented in Virtuoso, with one adjustment. Virtuoso stores quads, not triples, using the graph field (G) to indicate that a triple belongs to some particular application or resource. A SPARQL query may use quads from different graphs without large difference between G and the other three fields of a quad. E.g., variable ?g in expression GRAPH ?g {...} can be unbound. SPARQL has special syntax for "graph group patterns" that is convenient for sets of triple patterns with a common graph, but it also has shorthands for common subject and predicate, so the difference is no more than in syntax. There is only one feature that is specific for graphs but not for other fields: the SPARQL compiler can create restrictions on graphs according to FROM and FROM NAMED clauses. Virtuoso RDF Views should offer the same flexibility with the graphs as SPARQL addressing physical triples. A transformation cannot always be identified by the graph used for ranges because graph may be composed from SQL data. The key element of the meta schema is a "quad map pattern". A simple quad map pattern fully defines one particular transformation from one set of relational columns into triples that match one SPARQL graph pattern. The main part of quad map pattern is four declarations of "quad map values", each declaration specifies how to calculate the value of the corresponding triple field from the SQL data. The pattern also lists boolean SQL expressions that should be used to filter out unwanted rows of source data (and to join multiple tables if source columns belong to different tables). There are also quad map patterns that group together similar quad patterns but do not specify any real transformation or even prevent unwanted transformations from being used, they are described in "Grouping Map Patterns" below. Quad map values refer to schema elements of two further types: "IRI classes" and "literal classes". Implementation In the example script we implement RDF Views for Northwind tables (Customers, Orders, Order Details, Products, Product Categories, Employee, Region, Country, Province). To test the mapper we just use /sparql to execute: sparql select ?o where { graph ?g {?s ?p ?o . filter(?p like '%Country%') }} limit 10; Or use iSparql application. Making an Executable SOAP Directory Fri, 05 Jun 2009 10:21:01 GMT Exposing SOAP Endpoints Example The SQL script makes a directory in the web server space SOAP capable. The interface page has a test button which sends a SOAP request to the server. The Service for testing will just return its own argument. At least 2 web threads are needed, so this should be noted before starting. An Error message is shown if only one is available. The page shows the command and explains the manual operation for doing this from the admin interface. Simple usage of the SOAP service Fri, 05 Jun 2009 10:21:01 GMT Order Entry Service Example The service function accepts data for entering an order into the demo database. The arguments contain the customer id, item to order and quantity. The response is an element with price and expected delivery date, 10 days forward. The function creates a SOAP exception if the customer is not found in the database. The web page will take the data and call the service on the local server. It will display the in and out going messages, including envelopes. WSDL description Fri, 05 Jun 2009 10:21:01 GMT Generating WSDL descriptions for SOAP Services Example The page shows the automaticaly generated WSDL description of the resources granted to SOAPDEMO user. These descriptions are shown as source, and and also rendered by xslt. The original content of WSDL description can be seen at http://[host:port]/services/services.wsdl Data types and SOAP Fri, 05 Jun 2009 10:21:02 GMT Using SQL Data Types in SOAP Services Example Example has a sample call which passes all supported data types. Composite types e.g. arrays are just repeats of a constant element. The page shows the data going in and out. SOAP & remote data sources Fri, 05 Jun 2009 10:21:01 GMT Exposing Third-Party SQL Stored Procedures as SOAP Services Preliminaries This example demonstrates the processing of a result set obtained from a PL procedure defined on a remote database. The example shows a remote execution of a PL procedure against Microsoft SqlServer and Oracle DBMS. The remote procedure is called via rexecute(). The result set is converted to an XML document by a local PL procedure that can then be called via SOAP. The demo VSP pages offers a form to accept DSN, login info and a search string. A soap_call() is executed with these parameters, and the result is parsed with an XSL-T engine for HTML output. SQLServer Preparation Configure this example for SQLServer by loading the ms.sql script using the ISQL tool on behalf of the demo user. Make sure the demo Northwind DB is installed. Create a DSN to the SQLServer DB. Grant rexecute on DSN to the SOAP_SO_S_14 user: GRANT REXECUTE on [SQLServer DSN] to SOAP_SO_S_14; Oracle Preparation Configure this example for Oracle by loading the ora.sql script using the SQLPLUS tool on behalf of the demo user. Make sure the Oracle demo DB is installed. Create a DSN to the Oracle DB. The driver must be 'Microsoft ODBC Driver for Oracle'. Grant rexecute on DSN to the SOAP_SO_S_14 user: GRANT REXECUTE on [Oracle DSN] to SOAP_SO_S_14; SOAP & WSDL service Fri, 05 Jun 2009 10:21:01 GMT Email Address Validation Service Example overview This example demonstrates: The use of raw TCP session operations: ses_connect(), ses_disconnect(), ses_read_line(), ses_write(). A SOAP call. Using the SMTP protocol for email verification. Processing the results with an XSL-T engine. Example Setup The service is prepared by loading the SQL file. This performs the following: Define a stored procedure for the SOAP service that opens a TCP session, and makes a SMTP exchange without sending a mail body to the server. Read responses from the server and makes an XML document. The SOAP service is achieved by defining the /email_service URL to have same functionality as using soap_server() function call. SOAP Interoperability Test Round III Fri, 05 Jun 2009 10:21:01 GMT SOAP Interoperability Test Page Example This example uses the Virtuoso SOAP client for testing a SOAP interop III endpoints. At least 2 web threads are needed, so this should be noted before starting. An Error message is shown if only one is available. The index page shows operations defined for SOAP Interop Round III tests. The second level pages are SOAP clients defined to perform apropriate SOAP call. The VSP based clients make a specific form for entering the input data and selecting a endpoint to test. Once call is made (invoked with Call button), the results will be extracted from SOAP response and will be shown. Also the request and response wire dumps will be shown for debugging purposes. SOAP services Fri, 05 Jun 2009 10:21:01 GMT Google API demo Preliminaries This example demonstrates the ability to access the SOAP API provided by Google. The Google API has three services: Search for text on the web. Check details of a page cached by Google. Find correct spelling for a partial word. The service requests may be configured in various ways by the parameters supplied to the API. Google Registration Google API home page http://www.google.com/apis/ has details about the services offered by Google. You must register with Google to be authorized to use the API. You can sign up for a new account from the Google API home page. Verify your email address by accessing a page, as indicated in the email sent by Google. Collect the license key in a subsequent email from Google. Search Demo Enter the registration key for your personal Google account. The key is stored in a browser cookie, and is loaded when the tutorial is run again. Check the box if you wish to view the result as raw XML format. Enter some text to be searched, and press the search button. The raw XML result shows the overview of the search results. If XML view is not checked, then only the number of pages found is shown. Enter a url of a page that is likely to be cached by Google. Then press the button to get the details of this page. The raw XML result will contain the page typically encoded in Base64. If XML view is not checked, then only the size of the page is shown. Enter one or more words to have the spelling verified. A partial word is possible. Then press the button to verify the spelling. The raw XML result shows the suggested spelling. If XML view is not checked, then only the suggested spelling is shown. Publishing C function as SOAP service Fri, 05 Jun 2009 10:21:01 GMT Publishing C/C++ functions as Web Services Example The Virtuoso distribution includes the sample bif, bif_sample.c. It is thus possible to create following function and make server including it. The next step is creating a stored procedure that calls this function and you are back to publishing a Virtuoso stored procedure. The Service for testing will just return string "Hello world". At least 2 web threads are needed, so this should be noted before starting. An Error message is shown if only one is available. static caddr_t bif_hello_world (caddr_t * qst, caddr_t * err_ret, state_slot_t ** args) { return box_dv_short_string ("Hello world."); } IMPORTANT: You need to start the sample server containing bif_hello_world , in order run this example. Publishing Java class as SOAP service Fri, 05 Jun 2009 10:21:01 GMT Publishing Java classes as Web Services Example This example demonstrates how to call Java VM methods from Virtuoso/PL and expose them as SOAP services. It's based on the java class demo_server.java. Also provided is a demonstration of how the JAVA Reflect API can be used to automatically generate PL wrappers. Click on the Run link or point your browser to: http://[host]:[port]/services/services.vsmx Source code detail javavm_xml.pl - This file contains a Virtuoso/PL procedure which produces a XML description of the java classes. jvm_ref_describe_class (in class_name varchar, in inherited integer := 0) First argument is the absolute JAVA class name (example: java_server or java.util.Calendar). Second argument controls whether to create entries for the inherited constructors/ methods/ attributes of the class, or only for the ones defined in it (java.lang.Class.getDeclaredMethods() vs. java.lang.Class.getMethods()). javavm_pl.xsl - Is an XSLT stylesheet that produces the Virtuoso/PL wrappers based on the XML file from jvm_ref_describe_class. The stylesheet has a parameter "module" = "1" | "0" (default "1") which controls whether it to generate code for a Virtuoso/PL module, or a set of procedures. For each field it generates Get../Set.. methods (or only Get.. if the field is read-only). It also generates wrappers for each class method. For the non-static fields/methods it instantiates a java object every time the method is invoked. java_pl_wrapper.sql - This file is generated using the above files and then the java_properties procedure is added. Important: This demo needs Virtuoso server with Java hosting option. Otherwise demo will not run properly. SOAP services Fri, 05 Jun 2009 10:21:01 GMT Amazon API demo Preliminaries The online retailer Amazon.com has provided developers with an interface, that will allow them to do product searches based on a keyword, and a base category. The API is used either by a SOAP call or by a fetch of a XML document. Amazon.com suggests you to download the developer kit. However this is optional, as it is not necessary to run the following OpenLink tutorial. A developer key is required to access the service. One may be applied for at the Amazon.Com Web API page. The service requests may be configured in various ways by the parameters supplied to the API. Example Product search using SOAP API In the SQL setup file, the SOAP call is prepared by the function call to soap_wsdl_import() which reads in the web service ".wsdl" file from Amazon.com. Enter the Developer Key, result type, root node for the search, and the keyword to be searched for. Hit the search button to get the products matching the keyword. This example uses the "heavy" mode of results, that contains the full customer reviews. A local stylesheet is applied to the result for either an XML or HTML view. SOAP Interoperability Test Round IV Fri, 05 Jun 2009 10:21:01 GMT SOAP Interoperability Test Page Example This example uses the Virtuoso SOAP client for testing a SOAP interop IV endpoints. At least 2 web threads are needed, so this should be noted before starting. An Error message is shown if only one is available. This page shows operations defined for SOAP Interop Round IV tests, grouped by encoding and test group name. The demo pages are SOAP clients defined to perform appropriate SOAP call. The VSP based clients make a specific form for entering the input data and selecting an endpoint to test. Once call is made (invoked with Call button), the results will be extracted from SOAP response and shown. Also the request and response wire dumps will be shown for debugging purposes. SOAP services Fri, 05 Jun 2009 10:21:01 GMT Sending a SMS Example The setup_sms.vspx sets up the SMS sending unit. The handler.vspx does inserts/deletes/updates to demonstrate the SMS sending from triggers. For details of the Redcoal SOAP service see it's description in http://www.xmethods.com SOAP services Fri, 05 Jun 2009 10:21:01 GMT Yahoo GeoCode API demo Overview This example demonstrates how to use Yahoo GeoCode API as a local SOAP service. For more information on Yahoo GoeCode API visit: http://developer.yahoo.net/maps/rest/V1/geocode.html Breakdown In the initial state we create a simple procedure that: takes input parameters as described by Yahoo GoeCode API page. Generates URL and calls it. returns the XML result formated throught raw.xsl. Create SOAP_SO_S_27 user and grant the procedure to it. The SOAP service is achieved by defining the /SOAP_SO_S_27 URL to have same functionality as using soap_server() function call. The client uses AJAX to call the service and show the result. SOAP services Fri, 05 Jun 2009 10:21:01 GMT Google Maps API demo Overview This example demonstrates how to implement Google Maps API using XML and Asynchronous RPC ("AJAX"). For more information on Google Maps API visit: http://www.google.com/apis/maps/documentation/ Breakdown In the Initial state we will extend the Demo.demo.Customers table with some new fields (Longitude and Latitude). Then we will use Yahoo GeoCode API to find Longitude, Latitude values for the customers addresses and store the values in the database. The data_xml.vsp page generates XML from that table similar to the one described in Google Maps API Documentation. We have extend it a little bit to have information for the Customer Name and Address. Click on the 'googlemaps.vsp' Run link to see the generated Google Map. You can click on the markers to view additional information. SOAP services Fri, 05 Jun 2009 10:21:01 GMT Yahoo Maps API demo Overview This example demonstrates how to implement Yahoo Maps API Generated Rss feed. For more information on Yahoo Maps API visit: http://developer.yahoo.net/maps/simple/index.html Breakdown Yahoo Maps API can get map information using data formatted in Web-standard RSS format. We show in 'yahoomaps.vsp' how you can easily generated the feed to be posted to yahoo using just one SQL statement with xml_auto function. For more information on xml_auto refer to the documentation. Research Service for the Microsoft Office 2003 Fri, 05 Jun 2009 10:21:02 GMT Creating Research Service for the Microsoft Office 2003 Research Library Overview Microsoft Office 2003 provides a new tool called "The Research Library", which is available from the task pane in all of the Microsoft Office Suite including Microsoft Office Excel 2003, Microsoft Office PowerPoint 2003, Microsoft Office Word 2003, and Microsoft Office Outlook 2003. By default, the Research Library allows to search for terms using several built-in resources. The Research Library is also extensible: users can add their own research service by creating a Web service that follows schemas defined for Microsoft research services. Prerequisites The following prerequisites are needed to ensure that you can experience this demonstration. Virtuoso 3.5 or above Microsoft Office 2003 or higher Tutorial Example The following demonstration creates a service which exposes two methods: "Registration" and "Query". The first method allows Microsoft Office to discover the Query service; also it could be extended to log the registration info for specific application purposes. The "Query" service is used to make a Free-text search within WebDAV repository by a given set of keywords entered by user in task panel. The demo script also creates a virtual directory which forces "Document/Literal" encoding of SOAP messages, which is another way to expose Virtuoso PL stored procedures without having special SOAP user-defined data-types. Please follow the steps below to maximize the value of this tutorial: Step 1: Run the following sql script to set up so_s_34.sql. Step 2: After executing the initial setup above, add the service using a "Research" task pane by selecting "Research Options" and then "Add Services". Note: To access this Research Task pane, select Research from the Tools menu, or by pressing Ctrl+F1 and choosing Research from the Other Task Panes drop-down list. Step 3: In the presented text entry for the Address , type the URL to the demo service http://host:port/VRes/ and press "Add", then "Continue" and finally "Install" (in that step "Virtuoso FTi Search (PL)" should be displayed). Once you have installed the demo service, you can query it with typing search keywords in "Search for" text entry and selecting the "Virtuoso FTi Search (PL)" from drop-down box. On success a list of top 10 public readable items from WebDAV matching the "AND" of keywords will be displayed. Additional Information: For More details regarding the Microsoft Research Library for Microsoft Office 2003 see http://www.microsoft.com/office/editions/prodinfo/technologies/research.mspx For More details regarding WebDAV see http://docs.openlinksw.com/virtuoso/qswebdav.html#qswebdav For More details regarding Free-Text Search see http://docs.openlinksw.com/virtuoso/freetext.html#freetext Route Planner Demo Fri, 05 Jun 2009 10:21:02 GMT Using a MS MapPoint service for route planning Example There are several mapping services available today, but they only provide point to point services. Once such service is from Microsoft, which has a SOAP-based MapPoint service that allows location searching and distance calculations. The following demonstration uses the Microsoft MapPoint services to resolve addresses and to calculate the distance between two points. The demonstration will also calculate an optimized round-trip from a given start point to the destinations specified. In order to run the demonstration a MapPoint username and password are needed. Evaluation accounts from Microsoft are available here. eBay API demo Fri, 05 Jun 2009 10:21:02 GMT Using a eBay SOAP API Example This example uses eBay SOAP API to perform browsing of categories on eBay sandbox site. The demo uses WSDL_IMPORT_UDT() function to define SOAP proxy class for invocation of the eBay API. This will be done upon initial setup of the demo. The source code for imported UDT for SOAP proxy could be read via eBay.sql source file below. Also it defines a procedure for "getCategories" method invocation and it's used further in functions for vspx tree control to instantiate the nodes. To run the demo you will need to obtain a three developer keys : ApiId, DevId & CertificateId. Further a demo user on ebay sandbox site needs to be created and it's credentials must be used for to make API calls. To obtain developer keys please visit the eBay developer zone and follow the instructions. Once you have registered for eBay developer program and have the Api, Dev & Certificate IDs, go to "getting setup on sandbox" and follow the instructions to make a test user. Northwind query via SOAP Fri, 05 Jun 2009 10:21:02 GMT Building a document style oriented Web services Preliminaries The SOAP messages are XML documents which may follow encoding rules or an XML Schema. The ones that follow encoding rules are known as RPC encoded, so the XML Schema following messages are known as Document/literal encoded. Most of SOAP toolkits can work with both kind of messages, but for some purposes RPC encoded messages are not suitable. The MS Office Infopath supports only document/literal encoded SOAP messages. So to inter operate with it, Web services have to be set to define document/literal SOAP messages and generate appropriate WSDL file. The Virtuoso can expose a services that using document/literal encoding by two ways: explicit declarations in stored procedure's definition default SOAP encoding as an option of the SOAP enabled virtual directory The first approach imply that procedure can be used only in the designated encoding style. So the second is more flexabile as it can switch very easy between encodings without procedure's re-definition. To allow a stored procedure to be exposed in a virtual directory that default SOAP encoding as a document/literal it should not use explicit declarations. This is because explicit declarations are not compatible in all cases. Instead of explicit declarations a user defined types must be used to describe the structures and array modifier have to be used to describe array parameters. Example The example script defines a service that do a query on Northwind Demo Database and return an array of structures containing sales volume per product category. To run example, set the initial state and then click on the Run link or point your browser to http://[host]:[port]/NorthwindSvc/services.vsmx to go to vsmx test pages. Note: the dateinput must follow ISO8601 format (for example: YYYY-MM-DD'T'HH:MM:SS) Serializing Java classes as SOAP structures Fri, 05 Jun 2009 10:21:02 GMT Serializing Java classes as SOAP structures Example To test the service click on the Run link or point your browser to: http://[host]:[port]/services/services.vsmx This example demonstrates how to use Java user defined types for SOAP datatype mappings. It's based on the java class so_s_30.java. It demontsrates the two approaches in using the user defined types for SOAP structure mappings : Attach a user defined type to a predefined schema instance (user defined type SO_S_30). Generate the WSDL based on the user defined type's definition (user defined type SO_S_30_2). In order that to be runnable the so_s_30.java should be compiled to so_s_30.class with the java compiler and so_s_30.class be present in the server's CLASSPATH. Serializing SQL native classes as SOAP structures Fri, 05 Jun 2009 10:21:02 GMT Serializing SQL native classes as SOAP structures Example To test the service click on the Run link or point your browser to: http://[host]:[port]/services/services.vsmx This example demonstrates how to use SQL user defined types for SOAP datatype mappings. It demontsrates the two approaches in using the user defined types for SOAP structure mappings : Attach a user defined type to a predefined schema instance (user defined type SO_S_30). Generate the WSDL based on the user defined type's definition (user defined type SO_S_30_2). The example uses the implicit constructor for SQL native types (based on the default values). Serializing CLR classes as SOAP structures Fri, 05 Jun 2009 10:21:02 GMT Serializing CLR classes as SOAP structures Example To test the service click on the Run link or point your browser to: http://[host]:[port]/services/services.vsmx This example demonstrates how to use CLR user defined types for SOAP datatype mappings. It's based on the CLR class so_s_32.cs. It demontsrates the two approaches in using the user defined types for SOAP structure mappings : Attach a user defined type to a predefined schema instance (user defined type SO_S_30). Generate the WSDL based on the user defined type's definition (user defined type SO_S_30_2). SOAP services Fri, 05 Jun 2009 10:21:02 GMT Sending a SMS using a SOAP and User Defined Type Example The redcoal.sql is a script which implements SOAP proxy wrapper for sending SMS messages. The SOAP proxy wrapper is encapsulated in a User Defined Type The sms.vsp is web interface to the SMS sending unit. For details of the Redcoal SOAP service see it's description in http://www.xmethods.com Collaboration with other clients Fri, 05 Jun 2009 10:21:02 GMT Consuming a Virtuoso Web Service from Microsoft VB.NET VB.NET Virtuoso is able to interact with the Microsoft .NET by providing a WSDL description of all the stored procedures exposed as a SOAP operation within a given virtual directory. The WSDL description is an XML document available at http://<server_http_host_and_port>/SOAP/services.wsdl on any Virtuoso HTTP server. This allows for easy creation of .NET clients. fishselect example The following .NET VBA routine calls the fishselect stored procedure, and prints the results on the console. Open a new VB.NET project for Console Application. Add a web reference to the Virtuoso WSDL end point (http://host:port/SOAP/services.wsdl). Drag and drop the VirtuosoSOAP() from Class wizard in routine code. Module Module1 Sub Main() Dim s As New [drop here the VirtuosoSOAP() method from Class wizard] Dim r As String r = s.fishselect("G%") System.Console.WriteLine(r) End Sub End Module WSDL service Fri, 05 Jun 2009 10:21:02 GMT Generating HTML based Service interactions from a WSDL file Web services architecture Service providers deploy and publish services by registering them with the Service broker. Service requesters find services by searching the Service broker's registry of published services. Service requesters bind to the Service provider and consume the available services. Technology Uses In the world of Web services, each of these three operations involves three complimentary yet distinct technologies: Publishing services uses the Universal Description, Discovery and Integration (UDDI) API Locating services uses a combination of UDDI and the Web Services Description Language (WSDL) Binding to services leverages WSDL and the Simple Object Access Protocol (SOAP). At the most basic level, the Binding operation is the most important of the three. Example The example will create a SOAP based interface and WSDL description for searching an existing Customer Order New orders can be added into the demo database. The first page renders the WSDL as an HTML form for selecting the SOAP method. The second page renders the WSDL again to make a form for input parameters. Note that on this entry form needs to be entered valid _CustomerID (for example ALFKI) and _ProductID (for example is 1). Otherwise the servce will generate SOAP Fault message. Also _ShipVia needs to have a correct value to be entered (for example 1,2 or 3). Final page displays the result from the SOAP call. SOAP & WSDL service Fri, 05 Jun 2009 10:21:02 GMT Stock Quotes service SOAP Server setup An executable virtual directory is needed to create a SOAP based service. The service in practice is a PL procedure or group of PL procedures. In this example the PL module contains a single procedure. The procedure makes a request to the quotes.nasdaq.com. The retrieved XML document is returned as a string. VSP page for WSDL generation: For this example it is so_s_7_wsdl.vsp. A WSDL response is made and sent to the client. VSP for SOAP server: The SOAP server can be invoked using a soap_server() call. The so_s_7_server.vsp page shows setup of the SOAP server using the soap_server(). Example Virtuoso client invoked from VSP Virtuoso client is invoked from the VSP page so_s_7_client.vsp. This page accepts an issuer code and processes it with the Virtuoso SOAP client function - soap_call(). The soap method in this request is the name of the procedure defined in Step 2 above. The result from the SOAP request will be transformed with an XSL-T style-sheet, and sent to the browser as an HTML document. Example AJAX SOAP client invoked from JavaScript The "nasdaq_ajax.html" (listed below) contains a JavaScript code used to invoke SOAP client. This client will invoke the Virtuoso SOAP service as defined above. The JavaScript code uses XMLHttpRequest object. Invoking the operation via VB.NET application The following example demonstrates the usage of the Microsoft .NET against Virtuoso's SOAP service as defined in 'Server Setup': 1. Open a new VB.NET project for Console Application. 2. Add a web reference to the Virtuoso WSDL end point (http://[host:port]/xml-soap/services.wsdl). 3. Drag and drop the VirtuosoSOAP() from Class wizard in routine code. Sub Main() Dim soap As New WebReference1.VirtuosoDB_DBA_NasdaqQuotes() Dim result As String Dim sty As New Xml.Xsl.XslTransform() result = soap.get_NasdaqQuotes("YHOO") sty.Load("http://[host:port]/tutorial/services/so_s_7/sr.xsl") Dim strReader As New IO.StringReader(result) Dim xpDoc As New Xml.XPath.XPathDocument(strReader) Dim arg As New Xml.Xsl.XsltArgumentList() Dim strWriter As New IO.StringWriter() sty.Transform(xpDoc.CreateNavigator(), arg, strWriter) System.Console.WriteLine(strWriter.ToString) End Sub SOAP & WSDL service Fri, 05 Jun 2009 10:21:01 GMT Global provinces and administrative divisions lookup service Example overview This example demonstrates: Fetching HTML from a foreign host to populate a native table. A SOAP call. SQL to XML conversion with FOR XML EXPLICIT. XSL transformation. Example Setup The service is prepared by loading the SQL file. This performs the following: Fetch HTML country and province data from a foreign host. Insert the data into COUNTRY and PROVINCE tables. Fill tables COUNTRY_XML and PROVINCE_XML using http://[host:port]/DAV/factbook/factbook.xml. (http://www.xfront.org/factbook.xml) Define a stored procedure for the SOAP service that queries, using SQL, the data from the tables. The data is returned as XML, by including the FOR XML EXPLICIT clause, using the xml_auto() function to produce this as a string. Sample can use data from: Live feed from CIA factbook Cached XML Data The SOAP service is achieved by defining the /SOAP_SO_S_11 URL to have same functionality as using soap_server() function call. Example Operation Makes a SOAP client request. The XML result is held in a stream. Convert the stream using XSLT to HTML. Send the HTML to the browser for display. Invoking the operation via VB.NET application The following example demonstrates the usage of the Microsoft .NET against Virtuoso's SOAP service as defined in 'Server Setup': 1. Open a new VB.NET project for Console Application. 2. Add a web reference to the Virtuoso WSDL end point (http://[host:port]/SOAP_SO_S_11/services.wsdl). 3. Drag and drop the VirtuosoSOAP() from Class wizard in routine code. Sub Main() Dim soap As New WebReference1.VirtuosoSOAP() Dim result As String Dim sty As New Xml.Xsl.XslTransform() result = soap.administrative_divisions("United States", "") sty.Load("http://[host:port]/tutorial/services/so_s_11/sr.xsl") Dim strReader As New IO.StringReader(result) Dim xpDoc As New Xml.XPath.XPathDocument(strReader) Dim arg As New Xml.Xsl.XsltArgumentList() Dim strWriter As New IO.StringWriter() sty.Transform(xpDoc.CreateNavigator(), arg, strWriter) System.Console.WriteLine(strWriter.ToString) End Sub SOAP & WSDL service Fri, 05 Jun 2009 10:21:01 GMT Consuming Third-Party Services via WSDL URLs Example overview This example demonstrates usage of the WSDL_IMPORT_UDT() function. The result from calling of the WSDL_IMPORT_UDT() is the creation of a User Defined Type (UDT) which represents the SOAP service. Each method in the UDT represents an operation available from this SOAP service (proxy UDT). After successful retrieval of the WSDL, and UDT creation, the function returns UDT definition as string. The example also defines one test endpoint where it exposes the imported UDTs are own methods. Therefore it's easy to test using VSMX file for the demo endpoint. To cleanup the endpoint, use 'Clear cache' button. This would unpublish the SOAP proxy UDTs from the demo test endpoint. To setup and run, the initial script must be loaded. SOAP & WSDL service Fri, 05 Jun 2009 10:21:02 GMT Stock Quotes service hosted within Virtuoso's WebDAV repository Example This example repeats the example SO-S-7, but all VSPs are hosted in WebDAV repository. Important: make sure that EnabledDavVSP is set to 1 in the INI file, to allow VSP execution in WebDAV. The example script loads all pages into the WebDAV, and sets their execution flags. This example needs at least 2 HTTP server threads configured. SOAP & WSDL service Fri, 05 Jun 2009 10:21:02 GMT Stock Quotes client execution from within Virtuoso's WebDAV Repository Example This example repeats the example SO-S-7, but all VSPs for the SOAP client are hosted in the WebDAV repository. The SOAP & WSDL service are achieved by defining the /SOAP_SO_S_9 URL to have same functionality as using soap_server() and soap_wsdl() calls. '/SOAP_SO_S_9' represents the so_s_8_server.vsp and /SOAP_SO_S_9/services.wsdl represents the so_s_8_wsdl.vsp Important: make sure that EnabledDavVSP is set to 1 in the INI file, to allow VSP execution in WebDAV. The example script loads all pages into the WebDAV, and sets their execution flags. WS-ReliableMessaging demo Fri, 05 Jun 2009 10:21:01 GMT WS-Reliable Messaging: Setting up a SOAP endpoint Example The example below demonstrates configuring an endpoint for WS-Reliable Messaging operations. It also contains a simple client for issuing a 'ping' request to an endpoint which supports the WS-ReliableMessaging protocol. Note: To use the anonymous client, 'pingback URI' field in the send form should be empty. Making an SOAP router endpoint Fri, 05 Jun 2009 10:21:01 GMT Setting up a SOAP router endpoint Example This chapter describes general guide lines for using routing capabilities of the Virtuoso SOAP server. In order to have the rest of examples of this section working, the following steps must be performed. If you are going to try .NET examples the MS WSDK toolkit must be installed on a W2K machine. The .NET examples are tested with 1.0.0.0 version of Microsoft.WSDK.dll, so make sure that version of that assembly is the same. Make sure that WS Routing examples of MS WSDK working before trying any of interoperability examples. From this page run the setup script (set the initial state), this will define the routing and ultimate endpoints. The SOAP directory option we are using to setup a WS Routing (SOAP router) is: WS-RP - yes/no , this is to enable WS Routing filter The VSP page is example how Virtuoso SOAP client can be used to invoke the sample services as AddInt or echoString.Form this page we can invoke these operations via different routers. Note that "Operation to invoke" option must be set properly depending of type of endpoint (see remarks on select page). The .NET client example must be complied before trying it. To do this follow the steps: Change in the RoutingClient.cs and referalCache.config files <virtuoso:port> to host and port where your virtuoso HTTP server is listening. compile the example issuing nmake command in the tutorial/services/rp_s_1 directory. On that step you may need to have .NET Visual Studio installed and .NET Framework SDK if you going to compile the client on an other box, make sure that referralCache.config file is in the same directory where is .exe file. Manipulating of message path on a WS-Routing enabled endpoints Fri, 05 Jun 2009 10:21:01 GMT Routing path manipulation Example In order to try the following example you need first to setup the RP-S-1 as it uses the endpoints defined there. Also this example needs at least 3 HTTP threads configured in order to work properly. The VSP page demonstrates how routing path can be manipulated on a SOAP routers using the WS-Referral statements. The example show inserting an intermediary router in routing path Querying a router for already registered intermediary Execution of a single call to an endpoint Traveling of the message between client and endpoint via first and second router (added on step 1) Removal of a registered intermediary If step 1 is invoked twice sequentially an duplicates error will be shown If step 3 is invoked before step 1 or after step 5, an error for missing intermediary from routing path is shown Note: To determinate message routing path, see at content of 'rev' element in response message header when invoking the echoString operation. SOAP & WSDL service Fri, 05 Jun 2009 10:21:01 GMT Stock Quotes Service incorporating AJAX Example This example repeats the example SO-S-7, however client is using AJAX. The AJAX based client retrieves the XML data from Virtuoso SOAP service, and makes a client side XSL-T transformation. SOAP & WSDL service Fri, 05 Jun 2009 10:21:01 GMT Global provinces and administrative divisions lookup service incorporating AJAX Example This example repeats the SO-S-11, however client is using AJAX. Note: The tables and procedures are used from SO-S-11 example, so run SO-S-11 first. The SOAP & WSDL service are achieved by defining the /SOAP_SO_S_11 URL to have same functionality as using soap_server() and soap_wsdl() calls. SOAP & WSDL Fri, 05 Jun 2009 10:21:01 GMT Exchange Rate Conversion service Exchange Rate Conversion Example This example is an exchange calculator in two versions: based on VSP and soap_call() and another AJAX version based on WebService Behaviour. A virtual directory is configured to respond to SOAP service requests, which are handled in the context of the assigned SQL user. The example defines the following service descriptions and links: A SOAP directory of 'exchange' for describing exch's procedures. The WSDL file will be located at http://hostname:[port]/exchange/services.wsdl The server-based webservices file is exchange_rates.vsp, while the AJAX one is exchange_rates_ajax.html. Click on the 'Run' links to experience the demo. Note: loading the exchange.sql file will take some time, as it accesses a foreign host. SOAP & WSDL service Fri, 05 Jun 2009 10:21:01 GMT Web Registration form exploiting global provinces and administrative divisions lookup service Example overview This example demonstrates: Fetching a two-dimensional array from a SOAP service to populate a provinces list box. The clients in this case is using AJAX. Example This example uses the SO-S-11 for initial setup, and demonstrates making a simple registration form. Note: The tables and procedures are used from SO-S-11 example, so run SO-S-11 first. The SOAP & WSDL service are achieved by defining the /SOAP_SO_S_11 URL to have same functionality as using soap_server() and soap_wsdl() calls. SOAP services Fri, 05 Jun 2009 10:21:01 GMT Contact Details Extraction Service Example overview This example demonstrates: Fetching HTML from a foreign host as specified by the user. Rendering the page with regexp_match() to extract the contacts information. For example, it take information from sequences like: "quoted text" said John Smith, Manager at A Company. Making a Web search with the company name to find the domain. Making an email address from contact name, and domain name. A SOAP call. Processing the 4 dimensional array in the AJAX based client. XSL transformation. Example Setup The service is prepared by loading the SQL file. This performs the following: Define a SOAP type for 4 dimensional array. Define a stored procedure for the SOAP service that queries, web target and makes the contact info. The SOAP service is achieved by defining the /SOAP_SO_S_19 URL to have same functionality as using soap_server() function call. Example Operation Get a page from the URL. Substitute characters " as 0x94 etc. Remove <i>, <b>, <strong> elements. Take out CR/LF. Make breaks before <P and <H elements. Parse the page to have consistent escapes such as &quote; . If the above fails then achieve the same with substitutions. Have a function to return a regular expression(s) by given level of recursion. Have a recursive function which gets a regular expression based on depth and apply it against the text. If match found skip rest of pattern matching. If not, apply the next pattern. When contact matches name, title and company, the item will be added to a result array. Company of the result will be searched via Google to find 'home page' or 'welcome'. The top most result link from Google will be parsed to extract the name of the site ie. domain name part. The email will be composed as FirtsName.LastName@domain . A multidimensional array will be produced containing the name, company, title and email. Loop over the resulting array, and make an XML document. Render the XML to the HTML using XSL-T sheet. Invoking the operation via VB.NET application The following example demonstrates the usage of the Microsoft .NET against Virtuoso's SOAP service as defined in 'Server Setup': 1. Open a new VB.NET project for Console Application. 2. Add a web reference to the Virtuoso WSDL end point (http://[host:port]/SOAP_SO_S_19/services.wsdl). 3. Drag and drop the VirtuosoSOAP() from Class wizard in routine code. Sub Main() Dim soap As New WebReference1.VirtuosoSOAP() Dim result As String() Dim len, i As Integer result = soap.ExContacts("http://www.openlinksw.com/press/oplappl4.htm") len = result.Length - 1 For i = 0 To len Step 5 System.Console.WriteLine("Name: " + result(i)) System.Console.WriteLine("Title: " + result(i + 1)) System.Console.WriteLine("Company: " + result(i + 2)) System.Console.WriteLine("Email: " + result(i + 3)) System.Console.WriteLine("Home page: " + result(i + 4)) System.Console.WriteLine("") Next i End Sub Secure SOAP service Fri, 05 Jun 2009 10:21:01 GMT Secure SOAP Service invocation over SSL Example This example repeats the example SO-S-7, but SOAP call is executed over SSL. The SOAP & WSDL service are achieved by defining the /SOAP URL to have same functionality as using soap_server() and soap_wsdl() calls. '/SOAP' represents the so_s_7_server.vsp and /SOAP/services.wsdl represents the so_s_7_wsdl.vsp Make sure that files containing server and client certificate/key data are exists in the server working directory. The following files are required for the example: srv.cert.pem - server certificate srv.key.pem - server private key ca.pem - CA list cli.p12 - client certificate and private key in PKCS#12 format The example script defines and starts a HTTPS server on port 4433. Making an Secure SOAP Directory Fri, 05 Jun 2009 10:21:02 GMT Exposing Secure SOAP Endpoints Example In order to have the rest of examples of this section working, the following steps must be performed. The MS WSDK toolkit must be installed on a W2K mashine. The .NET examples are tested with 1.0.0.0 version of Microsoft.WSDK.dll, so make sure that version of that assembly is the same. Make sure that WS Secure examples of MS WSDK are working before trying any of interoperabilty examples. From this page run setup script (set the initial state), this will define symmetric keys, there is also included a x.509 import, but it's only for demonstration. To make your WSDK applications to work with Virtuoso you need to export from W2K box the certificate and import with registration page on this example. The setup script also defines a SOAP secure directory (/SecureWebServices) which is used in the rest of the examples. The SOAP directory options we are using to secure the messages are: WSS-SEC - yes/no , this is to enable WS secure processing WSS-KEY - name of procedure , which will return a key instance to encrypt the outbound messages WSS-Template - string or null, content of signature template, in that examples we will not make signatures on outbound messages. How to make signatures is explained in the x.509 siging example. WSS-Type - 1/0 to make signature or to encrypt only WSS-Validate-Signature - 0/1/2 - do not validate, validate signature, validate if exists in our examples we will accept both of variants, so 2 is used. Symmetric Encryption Fri, 05 Jun 2009 10:21:02 GMT Secure SOAP Client using Symmetric Encryption (3DES) Example This section describes how to make secure web services call, using a symmetric key encryption. The algorithm used for these examples is tripple-des. Both server and client have a shared secret, which is used to encryt and decrypt the SOAP message. In practice the key is transfered by some secure way between client and server, as if it's captured the all traffic between server and client can be compromised. Virtuoso keeps keys internally and can be instantiated with xenc_key_instance_create (). In this example we are using a key 'WSDK Sample Symmetric Key', imported from WSDK.NET SymmetricEncryption example. This is to have the same key in all places: .NET server and client, Virtuoso server and client. Another posibillity is to generate the key on Virtuoso side with xenc_key_3DES_rand_create() and export to the .NET client and server configuration. To export the 3DES key can be used xenc_key_serialize () function. The Virtuoso VSP based clients demonstrate accessing Virtuoso Web Service with Virtuoso client accessing .NET Web service with Virtuoso client To run .NET client against Virtuoso service you need: This example works with MS WSDK; MS WSE 2.0 obsolete the data encryption directly a key data. to change <virtuoso:port> to host and port where your virtuoso HTTP server is listening. compile the example issuing nmake command in the tutorial/services/ws_s_2 directory. if you going to compile the client on an other box, make sure that .config file is in the same directory where is .exe file. Asymmetric Encryption Fri, 05 Jun 2009 10:21:02 GMT Secure SOAP Client using RSA Encryption Example This section demonstrates how to make encrypted SOAP message using asymmetric algorithm (RSA). These examples are using a X.509 certificate to define the RSA key for encruption. To encrypt the message client uses public key of server's certificate, so only server know private key and can decrypt the message. In practice message itself is encrypted with 3DES algorthm using a random session key. The session key itself is encrypted with public part of RSA key. So the server first decode the session key, and then decrypt the message. The Virtuoso VSP based clients demonstrate accessing Virtuoso Web Service with Virtuoso client using asymmetric algorithm accessing .NET Web service with Virtuoso client using asymmetric algorithm To run .NET client against Virtuoso service you need: Microsoft WSE 2.0 installed Edit the Makefile and specify in CSLIBFLAGS where Microsoft.Web.Services.DLL reside. compile the example issuing nmake command in the tutorial/services/ws_s_3 directory. if you going to compile the client on an other box, make sure that .config file is in the same directory where is .exe file. X.509 Signing Fri, 05 Jun 2009 10:21:02 GMT Secure SOAP Client using X.509 certificate for signing Example This chapter demonstrates how to make signed SOAP message. The signed message contains certificate (only) in the Security header. In that example no encrypted data is sent, but in practice the both approaches can be combined. The server checks signature against this certificate and ensures it's validity. To run .NET client using X.509 certificate signed message, against Virtuoso service you need: Microsoft WSE 2.0 installed Edit the Makefile and specify in CSLIBFLAGS where Microsoft.Web.Services.DLL reside. compile the example issuing nmake command in the tutorial/services/ws_s_4 directory. Username Signing Fri, 05 Jun 2009 10:21:02 GMT Secure SOAP Client using Username and Password for signing Example This example demonstrates how to make signed SOAP message. The signed message contains Username and password in the Security header. In that example no encrypted data is sent. The server checks credentials against database users and ensures it's validity. To run .NET client using Username signed message, against Virtuoso service you need: Microsoft WSE 2.0 installed Edit the Makefile and specify in CSLIBFLAGS where Microsoft.Web.Services.DLL resides. compile the example issuing nmake command in the tutorial/services/ws_s_4a directory. Using a Secure SOAP Services Fri, 05 Jun 2009 10:21:02 GMT Exposing WS Trust SOAP Endpoints Preliminaries The WS Trust protocol is a SOAP based secure service and client that can be used to retrieve a security token (X.509 certificate or similar) in order to be used for making secure operation with an ultimate service in the client's operation. In common scenario the client asks Token Issuer for a security token. Then client uses token from WS Trust response to perform second remote call signed and/or encrypted to another service which is a ultimate target service. Usually Token Issuer, Client and Ultimate service are located on different physical locations. Example Before running this demo make sure that WS-S-1 tutorial is set-up and a valid X.509 certificate and corresponding private key are imported (for server and client usage) via WS-S-1 example certificate upload utility. This example uses WS Trust client to obtain X.509 security token from a Token Issuer service and then it uses to make signed request to dummy Weblog service. In real situation the Weblog service instead of echoing a random URL would create an entry in the user's blog. Note that in that demo all operations are performed in context of a same server (i.e. Token Issuer, Weblog service and client are running on one Virtuoso server instance). The following are demo's main points. Client uses a Username and password to digitally sign the request from Token Issuer. Token Issuer will check the signature and user credentials and then will issue security token to the client The Token Issuer service uses a PL hook to return appropriate X.509 token Client uses the returned token (from Token Issuer service) to match it against local key storage and to digitally sign the second request (to the Weblog service) The Weblog service will just verify the signature and will return an echo of the client's request plus an random URL string and post id. (In the real world such service should add data into a Database keeping the blog posts) The Weblog service will digitally sign the response before sending it to client. To see request/response messages from/to Token Issuer or Web service use the radio group buttons. Note: In this example Token Issuer, Weblog service and client uses same X.509 certificate. Add entity to UDDI Fri, 05 Jun 2009 10:21:02 GMT Business Contact Registration in Virtuoso hosted UDDI registry Example The example shows automatic convert demo..Suppliers to save_business UDDI query. Use UD-S-2 to see the result. Display entry from UDDI server Fri, 05 Jun 2009 10:21:02 GMT Querying Virtuoso hosted UDDI registry Example Setup a URL to a UDDI server Define a Name pattern (begin with) View the UDDI request Get the data from the server Web services registration Fri, 05 Jun 2009 10:21:02 GMT Step by step registration of SOAP services in Virtuoso hosted registry Preliminaries The UDDI provides a flexible way to register any kind of service. The services in particular can be SOAP or WSDL web services. SOAP and WSDL registration example This example makes a business record, and will define a service for it. The services can be provided by an organization (business). Without a defined provider service, it cannot be registered separately. Because there is no tModel for the service, one is provided for SOAP classification (tm.xml). Next step is to create a business description.(be.xml) Then create a service description (bs.xml). Then there is a binding (bnd.xml) of the service and business. This binding has two templates: SOAP template for information via Web page WSDL template for WSDL description of that service The example shows step by step for each template. HTML-to-Wireless Fri, 05 Jun 2009 10:21:02 GMT HTML-to-Wireless Tranformation Preliminaries Traditional HTML-based web pages are not intended for wireless viewing. However, with a little Virtuoso server-side scripting, you can turn any WAP-based wireless phone into a full HTML web browser! Running the Demo on a WAP device To access this application via WAP/WML, enter the following URL into a WAP-enabled wireless device: http://<hostname>/tutorial/apps/wa_b_1/html2wml.vsp Running the Demo on a WAP emulator To simulate viewing the output on a wireless device. Run the RunHtml2wml.html file. Then choose a web-based emulator from one below: Wap.com - This is the most accurate Java and web based emulator, almost to the point of the definitive WAP SDK emulators from Openwave. However, its performance is not always optimal. Wapsilon.com - This is a reasonably accurate emulator, but the user interface handling is not quite the same as a real WAP phone. Wapmore.com - This is a PDA style emulator. Although its interface is a bit quirky, the performance is reasonable. Gelon.net - This is probably the fastest web-based WAP emulator; however, it sometimes exhibits problems displaying WML forms. Download WAP SDK - Our recommended option, this standard WAP SDK for emulators from Openwave.com (formerly Phone.com). WAP Phone Emulator Fri, 05 Jun 2009 10:21:02 GMT WAP Phone Emulator Preliminaries This application demonstrates the power of Virtuoso XSL-T by transforming an XML file in real time into a wireless phone emulator, using a simple XSL stylesheet. Although fuller-featured WAP emulators exist, this demo serves to illustrate what can be achieved with a few lines of VSP code and accompanying stylesheet. However, a more comprehensive and fuller-featured WAP emulator built around the same concepts may be available soon. Running the Demo on a WAP device To access this application, click on 'Run' link below. WAP based Email Fri, 05 Jun 2009 10:21:02 GMT WAP Phone E-mail Retrieval Preliminaries Thought the only way to check your email on a wireless phone was via Yahoo.com? This demo will illustrate how a few VSP scripts on a Virtuoso server can provide a fully-functional wireless E-mail solution. Read messages from your POP-based email account from any WAP-enabled wireless device, or from the wireless phone emulators. Running the Demo on a WAP device To access this application via WAP/WML, enter the following URL into a WAP-enabled wireless device: http://<hostname>/tutorial/apps/wa_b_3/index.vsp Email WAP Application files index.vap - Main page that calls the other files. signup.vsp - Code to register details about a new user. login.vsp - Code to sign-in a registered user. checkmail.vsp - Code to communicate with the POP3 account. logout.vsp - Code to sign-out. Running the Demo on a WAP emulator To simulate viewing the output on a wireless device. Run the RunWapEmail.html file. Then choose a web-based emulator from one below: Wap.com - This is the most accurate Java and web based emulator, almost to the point of the definitive WAP SDK emulators from Openwave. However, its performance is not always optimal. Wapsilon.com - This is a reasonably accurate emulator, but the user interface handling is not quite the same as a real WAP phone. Wapmore.com - This is a PDA style emulator. Although its interface is a bit quirky, the performance is reasonable. Gelon.net - This is probably the fastest web-based WAP emulator; however, it sometimes exhibits problems displaying WML forms. Download WAP SDK - Our recommended option, this standard WAP SDK for emulators from Openwave.com (formerly Phone.com). Echo Fri, 05 Jun 2009 10:21:03 GMT Simple BPEL4WS process Example The example contains code for simple echo service. It accepts a string as an input and echoes back to the client. So it shows the core BPEL concepts for building services. To build and run the example follow the steps bellow: Login into the BPEL UI via http://host:port/BPELGUI. From the 'Home' tab page go to section 'Tasks' and click 'Upload Process' link. Another way is to choose 'Processes' tab and go to the 'Process Upload' sub-tab. For the given entry form in the "Load Deployment Descriptor URI" field type 'http://host:port/BPELDemo/echo/bpel.xml'. Choose the 'Import Process' button. Choose the 'Compile process' button. If compilation succeeds, will go to the 'Processes' page where the process will be in the given processes list. To test the process click "Test" in the listing. This goes to a page allowing you to enter a start message for creating a new instance of this process. Fibonacci function Fri, 05 Jun 2009 10:21:03 GMT Fibonacci function BPEL4WS process Example The Fibonacci function calculates number based on following algorithm: int fi (int i) { if (i < 2) return i; return fi (i-1) + fi (i-2) } The example contains code for a simple Fibonacci function service. It accepts an integer number as an input and calculates the Fibonacci number. The process like the Fibonacci function does a recursive syncronous invocation of itself. The example is allowed with values less than 6. If is entered value greater then 6, will be printed the message "Entry values greater then 6 are not allowed!" and the input entry value will be initialized to 6. To build and run the example follow the steps bellow: Login into the BPEL UI via http://host:port/BPELGUI. From the 'Home' tab page go to section 'Tasks' and click 'Upload Process' link. Another way is to choose 'Processes' tab and go to the 'Process Upload' sub-tab. For the given entry form in the "Process name" field type 'Fi' in order the process to be invoked successfully. In the "Load Deployment Descriptor URI" field type 'http://host:port/BPELDemo/fi/bpel.xml'. Choose the 'Import Process' button. Choose the 'Compile process' button. If compilation succeeds, will go to the 'Processes' page where the process will be in the given processes list. To test the process click "Test" in the listing. This goes to a page allowing you to enter a start message for creating a new instance of this process. Loan Flow Fri, 05 Jun 2009 10:21:03 GMT LoanFlow process Example This example needs BPEL4WS VAD package to be installed on the server, before trying it. To learn more about VAD please visit http://docs.openlinksw.com/virtuoso/VAD.html. The example script creates services for Credit Rating, United Loan and Star Loan. The WSDL descriptions for these services are available via CreditRating.vsp, UnitedLoan.vsp and StarLoan.vsp. On the initial load the BPEL LoanFlow process will be deployed. The test client can be used to test the BPEL LoanFlow process. You can test the example using BPEL UI also by going to 'Processes' tab and click "Test" in the listing. This goes to a page allowing you to enter a start message for creating a new instance of this process. SecLoan Fri, 05 Jun 2009 10:21:03 GMT LoanFlow process using WS-Security Example The example contains code for LoanFlow Using WS-Security. The following are requirments to the caller and the process: The caller must supply a signed and encrypted SOAP message. Process must reply from a separate HTTP connection to the endpoint designated by the caller's ReplyTo WSA header. The process' reply must contain a signed and encrypted SOAP message. The X.509 certificates must be used to sign the message. The Session keys can be tripple-des or AES (128, 192 or 256 bit quality). A Session key must be encrypted with the partner's RSA public key. The test certificates from MS WSE 2.0 toolkit can be used. To build the example follow the steps bellow: This example needs BPEL4WS VAD package to be installed on the server, before trying it. To learn more about VAD please visit http://docs.openlinksw.com/virtuoso/VAD.html. The example script creates services for Credit Rating, United Loan and Star Loan. The WSDL descriptions for these services are available via CreditRating.vsp, UnitedLoan.vsp and StarLoan.vsp. On the initial load the BPEL LoanFlow Using WS-Security process will be deployed. To run the example follow the steps bellow: Login into the BPEL UI via http://host:port/BPELGUI. To enable debugging for the process with name 'SecLoanFlow' check the Debug checkbox in the listing. This will cause all instances of the process to run in single-step mode, pausing at each send or receive of a message. The debug console allows interacting with instances in this mode. To test the process click "Test" in the listing. This goes to a page allowing you to enter a start message for creating a new instance of this process. RMLoan Fri, 05 Jun 2009 10:21:03 GMT Reliable Loan Flow process Example The example contains code for Reliable Loan Flow. Invokation of loan services must be done using WS-RM protocol and rules: The caller must supply a valid WSA header 'ReplyTo' containing a valid response URL. The Process must send a reply from a separate HTTP connection to the endpoint designated by the caller's ReplyTo WSA header using WS-RM. To build the example follow the steps bellow: This example needs BPEL4WS VAD package to be installed on the server, before trying it. To learn more about VAD please visit http://docs.openlinksw.com/virtuoso/VAD.html. This example depends from the SecLoan example. Make sure there is initial load for this example. The example script creates services for Credit Rating, United Loan and Star Loan. The WSDL descriptions for these services are available via CreditRating.vsp, UnitedLoan.vsp and StarLoan.vsp. On the initial load the BPEL Reliable LoanFlow process will be deployed. To run the example follow the steps bellow: Login into the BPEL UI via http://host:port/BPELGUI. To enable debugging for the process with name 'SecLoanFlow' check the Debug checkbox in the listing. This will cause all instances of the process to run in single-step mode, pausing at each send or receive of a message. The debug console allows interacting with instances in this mode. To test the process click "Test" in the listing. This goes to a page allowing you to enter a start message for creating a new instance of this process. SecRMLoan Fri, 05 Jun 2009 10:21:03 GMT Secure and Reliable Loan Flow process Example This example is a combination of Secure and Reliable Loan Flow examples. The calls to the asynchronous services must be done via WS-RM protocol. The calls to partners must be encrypted and signed using the rules: The caller must supply a signed and encrypted SOAP message. The process must reply from a separate HTTP connection to the endpoint designated by the caller's ReplyTo WSA header. The process' reply must contain a signed and encrypted SOAP message. X.509 certificates must be used to sign the message. The session keys can be tripple-des or AES (128, 192 or 256 bit quality). A Session key must be encrypted with the partner's RSA public key. The test certificates from MS WSE 2.0 toolkit can be used. To build the example follow the steps bellow: This example needs BPEL4WS VAD package to be installed on the server, before trying it. To learn more about VAD please visit http://docs.openlinksw.com/virtuoso/VAD.html. This example depends from the SecLoan and the RMLoan example. Make sure there are initial loads for these examples. The example script creates services for Credit Rating, United Loan and Star Loan. The WSDL descriptions for these services are available via CreditRating.vsp, UnitedLoan.vsp and StarLoan.vsp. On the initial load the BPEL Secure and Reliable Loan Flow process will be deployed. To run the example follow the steps bellow: Login into the BPEL UI via http://host:port/BPELGUI. To enable debugging for the process with name 'SecRMLoanFlow' check the Debug checkbox in the listing. This will cause all instances of the process to run in single-step mode, pausing at each send or receive of a message. The debug console allows interacting with instances in this mode. To test the process click "Test" in the listing. This goes to a page allowing you to enter a start message for creating a new instance of this process. Inventory Service Fri, 05 Jun 2009 10:21:03 GMT Using SQL binding and SQLX to generate requests process Example The demo is a service which is invoked on time basis in order to update an inventory. This demo using the Northwind demo Database as an inventory. The client generates a list of items that are under 10 units using SQLX. This XML document is sent to the inventory process and will make new process instance. create procedure DB..update_inventory () { declare req any; whenever not found goto nf; -- the following statement generates an XML document using SQLX -- -- the result would like : -- <Items xmlns="http://temp.org"><Product><ProductID>5</ProductID><Quantity>10</Quantity></Product>...</Items> -- select xmlelement(Items, xmlattributes ('http://temp.org' as xmlns), xmlagg ( xmlelement (item, xmlelement (ProductID, ProductID), xmlelement (Quantity, UnitsInStock + 10) ))) into req from Demo..Products where UnitsInStock < 10 and Discontinued = 0; -- call the process with generated XML document as a input parameter soap_client ( url=>'http://host:port/InventorySvc', operation=>'initiate', parameters=>vector ('par0', req), soap_action=>'initiate', style=>1 ); nf: return; } The process will loop over all items in input and for each will ask for quote. Also the quote service will return a list of wholesalers and their prices. Then the process will choose the best price and will make an order using specific partner URL (this is simulated by appending a URL parameter to the service URL). When an order confirmation is received, the process will update the inventory. At the end of process it will sent an e-mail to the pre-configured operator address. The following code is a pseudo-code which describes the process flow: { declare i, l int; declare j, k, oid int; declare in, out, ord any; in = receive (); l = length (in); for (i = 0; i < l; i++) { declare q, best any; q = getQuote (in[i]); k = length (q); for (j := 0; j < k; j++) { if (q.price < best.price) best = q; } oid = newOrder (best.url, in[i].item, in[i].quantity, best.price); update Demo.demo.Products set UnitsOnStock = UnitsOnStock + in[i].quantity where ProductID = in[i].ProductID; } sendMail ('Inventory have been updated'); } To run the example follow the steps bellow: Load the initial setup file: 'store.sql' Login into the BPEL UI via http://host:port/BPELGUI. From the 'Home' tab page go to section 'Tasks' and click 'Upload Process' link. Another way is to choose 'Processes' tab and go to the 'Process Upload' sub-tab. In the "Load Deployment Descriptor URI" field type 'http://host:port/BPELDemo/sqlexec/bpel.xml'. Choose the 'Import Process' button. Choose the 'Compile process' button. If compilation succeeds, will go to the 'Processes' page where the process will be in the given processes list. Run via ISQL the DB..update_inventory () procedure. Also the update_inventory procedure can be used as a scheduled task (see 'Virtuoso scheduler' section in the documentation for details). UseCases Fri, 05 Jun 2009 10:21:03 GMT BPEL Data Manipulation Options processes Purpose This is a Technical Use Case as per OASIS MS01 document, illustrating various options for data manipulation inside a BPEL process. The following situations are shown: MS01-1: Variable initialization. MS01-2: Assignment of string, numeric, boolean and date values. MS01-3: Assignment of variables by copying other variable's values. MS01-4: Sample XPath calculations. MS01-5&6: Working with array structures. To run the example follow the steps bellow: Load the initial setup file: 'MS01.sql' Login into the BPEL UI via http://host:port/BPELGUI to test the processes. Java Execution Fri, 05 Jun 2009 10:21:03 GMT Java Execution BPEL4WS process Example This example demonstrates how the Java code can be embedded in a BPEL process. The example contains java code which gets data from BPEL variable, prints string to the console and returns string. To build and run the example follow the steps bellow: Make sure system calls are enabled in virtuoso.ini (AllowOSCalls parameter). Make sure CLASSPATH environment variable contains "classlib" entry. Make sure you are running java enabled server (virtuoso-odbc-javavm-t or virtuoso-odbc-javavm-clr-t). Login into the BPEL UI via http://host:port/BPELGUI. From the 'Home' tab page go to section 'Tasks' and click 'Upload Process' link. Another way is to choose 'Processes' tab and go to the 'Process Upload' sub-tab. For the given entry form in the "Load Deployment Descriptor URI" field type 'http://host:port/BPELDemo/java_exec/bpel.xml'. Choose the 'Import Process' button. Choose the 'Compile process' button. If compilation succeeds, will go to the 'Processes' page where the process will be in the given processes list. To test the process click "Test" in the listing. This goes to a page allowing you to enter a start message for creating a new instance of this process. .NET Execution Fri, 05 Jun 2009 10:21:03 GMT .NET Execution BPEL4WS process Example This example demonstrates how the C# code can be embedded in a BPEL process. The example contains .NET code which gets data from BPEL variable, prints string to the console and returns string. To build and run the example follow the steps bellow: Initialize "CLRAssembliesDir" configuration field at BPEL UI 'Configuration' tab with the directory name where the dlls from the distribution are located ( t.e. OpenLink.Data.VirtuosoClient.dll, virt_bpel4ws.dll etc). If you have problems initializing this CLRAssembliesDir, ask your system administrator to initialize this directory for you (and copy all needed DLLs there) or to tell you where this directory is located. Make sure you are running CLR or Mono enabled server (virtuoso-odbc-clr-t, virtuoso-odbc-mono-t, etc). Login into the BPEL UI via http://host:port/BPELGUI. From the 'Home' tab page go to section 'Tasks' and click 'Upload Process' link. Another way is to choose 'Processes' tab and go to the 'Process Upload' sub-tab. For the given entry form in the "Load Deployment Descriptor URI" field type 'http://host:port/BPELDemo/clr_exec/bpel.xml'. Choose the 'Import Process' button. Choose the 'Compile process' button. If compilation succeeds, will go to the 'Processes' page where the process will be in the given processes list. To test the process click "Test" in the listing. This goes to a page allowing you to enter a start message for creating a new instance of this process. processXSLT Fri, 05 Jun 2009 10:21:03 GMT XSLT processing example Example This example demonstrates how the XSLT styles can be used in BPEL script. The example contains invoking the processXSLT XPath function which transforms data from a BPEL variable. To build and run the example follow the steps bellow: Load the initial setup file: ' processXSLT.sql' Login into the BPEL UI via http://host:port/BPELGUI. From the 'Home' tab page go to section 'Tasks' and click 'Upload Process' link. Another way is to choose 'Processes' tab and go to the 'Process Upload' sub-tab. For the given entry form in the "Load Deployment Descriptor URI" field type 'http://host:port/BPELDemo/processXSLT/bpel.xml'. Choose the 'Import Process' button. Choose the 'Compile process' button. If compilation succeeds, will go to the 'Processes' page where the process will be in the given processes list. To test the process click "Test" in the listing. This goes to a page allowing you to enter a start message for creating a new instance of this process. processXQuery Fri, 05 Jun 2009 10:21:03 GMT XQuery processing example Example This example demonstrates how the XQuery program can be used in a BPEL process. The example contains invoking the processXQuery XPath function which returns a result of XQuery program. To build and run the example follow the steps bellow: Login into the BPEL UI via http://host:port/BPELGUI. From the 'Home' tab page go to section 'Tasks' and click 'Upload Process' link. Another way is to choose 'Processes' tab and go to the 'Process Upload' sub-tab. For the given entry form in the "Load Deployment Descriptor URI" field type 'http://host:port/BPELDemo/processXQuery/bpel.xml'. Choose the 'Import Process' button. Choose the 'Compile process' button. If compilation succeeds, will go to the 'Processes' page where the process will be in the given processes list. To test the process click "Test" in the listing. This goes to a page allowing you to enter a start message for creating a new instance of this process. Enter "2" as id and "Kathreen Smith" as "seller" for the test form fields. processXSQL Fri, 05 Jun 2009 10:21:03 GMT XSQL processing example Example This example demonstrates how the XSQL scripts can be used in a BPEL example. The example contains the XSQL file selectCustomers.xsql which selects a customer by given SSN. To build and run the example follow the steps bellow: Login into the BPEL UI via http://host:port/BPELGUI. From the 'Home' tab page go to section 'Tasks' and click 'Upload Process' link. Another way is to choose 'Processes' tab and go to the 'Process Upload' sub-tab. For the given entry form in the "Load Deployment Descriptor URI" field type 'http://host:port/BPELDemo/processXSQL/bpel.xml'. Choose the 'Import Process' button. Choose the 'Compile process' button. If compilation succeeds, will go to the 'Processes' page where the process will be in the given processes list. To test the process click "Test" in the listing. This goes to a page allowing you to enter a start message for creating a new instance of this process. Making an Executable Directory Fri, 05 Jun 2009 10:21:02 GMT Making an executable directory in Virtuoso's Web server space. Preliminaries It is necessary to complete the configuration of the executable directory before any of the subsequent tutorials will function. It is therefore important to follow these steps carefully before trying to run the vs_b_1.vsp file. The example demonstrated here creates an executable directory depending on either tutorial_dav.vad package was installled (i.e. the tutorials are in the DAV repository) or the tutorial_filesystem.dav package ( the tutorials have FS location.) These tutorials are designed for use on an OpenLink Virtuoso V3.0 VDBMS. Earlier versions will not work. Login to the Conductor UI using the dba account. Create the database user account vspdemo Go to "System Admin" tab and then go to the "User Accounts" tab. Click the "Create New Account" link. Enter for Account Name "vspdemo" and a desired password with confirmation. Check the check-box for "Allow SQL/ODBC Logins". Leave the rest of the fields at their default. Press the "Save" button. You will see the vspdemo now listed under current user accounts. Check tutorials type and path of installation (DAV or FS) Go to "Database" tab and then go to the "Interactive SQL" tab. Enter in the text area the following: select TUTORIAL_VDIR_DIR(); Click the "Execute" button. The found result will be the tutorials Path location. Click the button "Return". Enter in the text area the following: select TUTORIAL_IS_DAV(); Click the "Execute" button. The returned result will be the tutorials Type location: 1 - tutorials are installed in DAV, or 0 - tutorials are installed in the local FS. Allow VSP files to be run by vspdemo user with directory browsing allowed Go to "Web Application Server" tab and then go to the "Virtual Domains & Directories" tab. From the given list of HTTP Hosts click the icon in front of the Interface value. Click the "Add new directory" link. If tutorials Type location is FS, then choose the "File System" type and press the button "Next>>"; If tutorials Type location is DAV, then choose the "WebDAV domain" type and press the button "Next>>". Enter "/vs_b_1" for Path, Enter the tutorials Path location with "/tutorial/web/" at the end for physical path (for ex. /vad/vsp/tutorial/web/ or /DAV/VAD/tutorial/web/), select "vspdemo" for the VSP user, select "Allow Directory Browsing" checkbox and then press the "Save Changes" button. To test the new directory: enter the "http://host:port/vs_b_1" in browser. For example http://localhost:8889 as the URL. If all is ok, the content of the web tutorial directory is shown. Equivalent SQL commands to achieve executable directory Connect to the Virtuoso server via ISQL utility as DBA user. Create the user "vspdemo" SQL> create user vspdemo; SQL> user_set_qualifier ('vspdemo', 'vspdemo'); Create the virtual directory with user for execution "vspdemo" SQL> VHOST_REMOVE (vhost=>'*ini*',lhost=>'*ini*',lpath=>'/vs_b_1'); SQL> vhost_define (vhost=>'*ini*',lhost=>'*ini*',lpath=>'/vs_b_1', ppath=>TUTORIAL_VDIR_DIR() || '/tutorial/web/',vsp_user=>'vspdemo', is_brws=>1, is_dav=>TUTORIAL_IS_DAV()); Syntax of a Page Fri, 05 Jun 2009 10:21:02 GMT Overview of Virtuoso Server Pages (VSP) syntax Preliminaries The pages with active content are named Virtuoso Server Pages, or VSP in short. These pages consist of Virtuoso PL and HTML code. The Virtuoso Web server executes only files (resources) with the ".vsp" extension. The VSP file can contain VSP blocks and HTML blocks The VSP block contains the PL code The <?vsp marks the beginning of a VSP block. The ?> marks the end of a VSP block. The HTML block is everything outside of a VSP block. The VSP shortcut in an HTML block is used to include value. The <?= marks the beginning of VSP shortcut. The ?> marks the end of a VSP shortcut. Example The demonstration page uses the <?= <?vsp markup and SQL expressions to generate html content from within a VSP loop. Accessing Data in the Request Fri, 05 Jun 2009 10:21:02 GMT How to access form data. Preliminaries Data supplied to a VSP is normally posted from a form. Pressing a form submit button causes the browser to pass the data to the server. The form data may be encoded in two forms: www-url-encoded or multipart/form-data. Processing of the form data in VSPs. When the request arrives, the web server parses the request and creates three input arguments for VSP processing: path - array with path elements of the requested URL path. Ie. if the request is "http://foo.bar/a/b/c" then "path" argument will be a vector ('a','b','c'). params - array with name and value pairs of the passed form parameters lines - the vector of HTTP request header lines The form data is held in the "params" argument. The value of a named parameter can be extracted from the "params" array in two ways: Using the get_keyword function: declare x varchar; x := get_keyword('fieldname', params, 'default value'); Using the {?'variable_name'} VSP shortcut. x := {?'fieldname'}; Form example - A Simple Calculator. The user can enter two numbers, and choose a desired operation. Pressing the "=" button causes the form to be sent to the server. The VSP execution will show the result of the calculation. This example uses the get_keyword function to retrieve the parameters. Form example - Insert table data The second example demonstrates inserting the user entered form data into a table. This example uses the {?''} shortcuts for accessing the form data. The data in the table will be shown below the form, ordered by date. You must run the sample SQL first to create the table. Note Note that both examples do not handle the SQL errors. The handling of SQL errors in VSPs will be described in section "VS-B-6". Emitting Output to the User Agent Fri, 05 Jun 2009 10:21:02 GMT How to send data to the user-agents Preliminaries The Virtuoso Web server writes the content that is to be sent into the internal string session. The internal string session will be sent to the user-agent after VSP execution (if there is no unhandled SQL errors). There is full control over an internal session. It can be cleared, filled and flushed. Note when flushing an internal session the task will be executed in background, and no output will be sent after this action. The character data can be encoded in various ways. A default encoding (CharSet) can be defined in the database INI file HTTP session control functions http() - writes a string into the internal string session without conversion. http_value() - writes a string with escapes into the internal string session. http_rewrite() - clears the internal string session http_flush() - flushes the internal string to the user-agent and continues processing in background. <?=var ?> - a shortcut to write a variable (or function) from an HTML section. HTTP formatting and charset functions sprintf() - the special codes %V and %U used for HTML and URL escaping respectively. http_url() - converts argument to a URL escaping special characters. Result written to internal session. http_value() - converts argument to HTML escaping special characters. Result written to internal session. current_charset() - returns name of the current charset. Examples Use of http(). Use of http_value(). Use of http_rewrite(). Use of http_url(). Use of <?= ?> tags. Use of sprintf(). Controlling the Response Header Fri, 05 Jun 2009 10:21:02 GMT How to control the HTTP response from VSP Preliminaries The behavior of the user-agents (Eg. browser) depends on the server's HTTP response. The HTTP responses are: positive (2xx), informative (1xx), redirection(3xx), client error(4xx), server error(5xx). The Virtuoso Web server automatically generates positive, client error and server error codes. The http_request_status() function overwrites the default response. Example: http_request_status ('HTTP/1.1 404 Not found'); Note that the response line MUST begin with "HTTP/1.1 ", and numeric code MUST be one described in RFC2616. Some responses require additional information in the HTTP response header. The functions for setting the additional lines in a response header are: http_header() - accept a response header line(s) as string. If more than one line is supplied, they must be separated with <CR><LF> characters. Note that the string must always finish with <CR><LF> chars. Subsequent calls of this function replace the old response. http_header_get() - returns the HTTP response header currently defined. It is useful when you need to append more lines. Redirection example The first example demonstrates a redirection to another page. It uses http_request_status() and http_header() functions to set redirection code 'HTTP/1.1 302 Found' and 'Location:' header line in the response. If a document has moved to another url, it's possible to redirect using a meta tag within the <head> of a page. However using the method in this example, the redirection can be dynamic and conditional. Load random Images The second example demonstrates loading random images onto a page. Three images are loaded into the page by calling the vsp to generate each image. Each image URL is made unique by including a pic parameter. This does not imply the images will be different, however it stops the browser from reusing one image three times. The image content is made by setting the 'Content-Type' header field of the response to the 'image/gif', then returning a randomly chosen block of Gif data. A real world use for a random image, is to show an advertising banner graphic. Rather than change the url for the banner, the actual image content is controlled. This technique might also be useful for changing an appearance of a web page based on some criteria such as if the user has membership access or the time of day is either day or night to show a sun or moon image. Error Recovery Fri, 05 Jun 2009 10:21:02 GMT Error handling in VSPs Preliminaries When processing some form data in VSPs, there may be errors in the PL code (for example due to deadlock condition). The most popular way of catching the SQL errors is to declare an exception handler. declare error varchar; error := null; declare exit handler for sqlstate '*', not found result { error := __SQL_MESSAGE; }; { -- some operation(s) that may cause a SQL error } if (error is not null) -- print the error message To simplify VSP production, the error handler can be written in a common file and included in each page. The external file can be included using the following markup: <?include path_to_the_external_file.extension ?> Deadlock Example The first example demonstrates the creation of a universal deadlock handler. If this file is included in any VSP the deadlock condition will cause a retry up to 3 times on an operation that follows the handler. The VSP simulates the deadlock situation by signalling the error. The error handler is therefore called. Calculator with error handler The second example is the vs_b_3 Calculator example. In this example there is an added handler to trap errors such as division by zero, and entering characters instead of numbers. User Records Fri, 05 Jun 2009 10:21:02 GMT VSP exercise to create user records User record Example The object of the exercise is to handle form data, and data held in a table. This is achieved with 4 separate stages. Each stage has a separate file. Create a user table with a login name, password, real name and address. The login name is the primary key. This table can be made with a SQL file. Write an application which gets data from a form and inserts it into a table. Write a form to prompt for login details and pass to the query page. Write a form for querying the table based on the login name. The login name in the query form will be a SQL LIKE string. This example would be used in web sites that have some form of membership to gain access to information. Media Archive Fri, 05 Jun 2009 10:21:02 GMT Creating a simple file storage system using an SQL table Overview The media archive shall demonstrate a way to upload files and also to download them. The web page shows the upload feature, and download list at the same time. Upload Media The file upload form allows an arbitrary file to be browsed and uploaded. The input control is of type FILE for the upload field. The ENCTYPE of the form must be a "multipart/form-data", so that the uploaded file data and type (attr-file) is accessible to the VSP. Download media Display a list of stored files on a single page and have a download link to each. The default name in the download box must be the original file name. The INLINEFILE pseudo directory shall be used to set the original name of a file. The "Content-Type" header must be set to original media type. Note that INLINEFILE pseudo URL needs a parameter "VSP", this parameter MUST contain the target VSP link encoded as URL. Header Parsing Functions Fri, 05 Jun 2009 10:21:02 GMT Header Parsing Functions (Cookie Example) Preliminaries The HTTP request header contains lines of request information. Every user-agent (browser) sends at least one request line to the Web server. The "lines" argument in VSPs is an array of the user-agent request lines. Methods for accessing the request parameters A specific attribute can be accessed with the http_request_header() function. The header lines can be accessed with aref() function. Parsing the header line can be done with the split_and_decode() function. Cookie Demo This example shows how to set a cookie in the user-agent, and displaying a cookie string when retrieved. Setting the cookie is done with the http_header() function. (see also:VS-B-5) Retrieving the cookie string is done with http_request_header() function. Simple VSPX controls Fri, 05 Jun 2009 10:21:02 GMT Building a VSPX page Preliminaries The framework named Virtuoso Server Pages Extensions (VSPX) is a set of widgets (controls) which are executed on server side. The VSPX page itself is an active page which contains XHTML, VSP and widget code. The VSPX page like VSP is a server side executable, so in both cases the virtual directory where pages are placed needs to be executable. For the details of virtual directory setup please read VS-B-1. It might appear that VSPX pages and VSP pages are similar, but in practice there are differences: The content of a VSPX page must be well-formed XML (like XHTML), in contrast to a VSP page where this is not mandatory. VSPX pages have a container element named "page", VSP pages do not (they are processing instruction based). The controls and containers in the VSPX page are in a special name space: "http://www.openlinksw.com/vspx/", we will alias it with the prefix "vspx:" for the controls in the rest of the tutorials. Any active content must be in vspx:page container, and no more than one vspx:page can be in a separate VSPX source file. The VSPX pages are compiled to a more complex structure than VSPs. The processing model of VSPX pages have five phases on execution: initialization, data-binding, post processing, pre-rendering and rendering. VSP only has pre-processing, and execution. For more details please read the VSPX section in the Virtuoso server documentation. The VSPX page may contain one or more controls, VSPs do not have components. Also each control in the VSPX page (and page container itself) represents a user defined type (class). As VSPX pages may contain macros and may have a pre-processing XSL-T transformation, an intermediate file will be generated in the same place where the source page is. Also the compilation will generate a SQL script as a file before compilation of PL code in memory. These files will be explained later in the VX-S-8 example. Finally there are several notes about VSPX controls (widgets). They need to have unique names in the page space, including the macro expanded ones. It is not mandatory for the page name to be unique in the Web server space. All of the controls are represented by trees of class instances, where each member vc_children contains instances to the children controls. Example The example shows a simple page containing a vspx:page container and basic controls: vspx:label, vspx:url and vspx:include. The vspx:page container (as already discussed) can contain any executable content, but it may not be the topmost element in the page as we may see in the source of simple.vspx page. It also shows how VSP processing instructions can be nested in the VSPX page. On that point we need to say that a good VSPX programming style is to use very minimal VSP code to get the benefit of widgets. The rest of the controls used in this example can be placed in any other control or container allowing content. The special case is vspx:include which we will discuss later. The vspx:label is the simplest text label which can be data bound. The "data bound" term designates the ability of the control to obtain a value (it may be constant or Virtuoso/PL expression) on data bind phase when the page gets executed. The format attribute is used to render the data, so it depends on the data type of the control's value. In practice it's a format string as in sprintf() function. In that example the first label has string value and the format is '%s', but second one is integer and the format used is '%d'. The vspx:url represents a HTML anchor, but value and url are data bind-able. We can talk about it as an extended functionality of the vspx:label control. The last one is a vspx:include control. This is not a control per se, it's rather a place where the expanded content of another document will be placed before VSPX page compilation. In that way we can easily re-use common code between different pages. In that particular case the included file is very simple, but it can also be another VSPX page. With this approach VSPX pages have more complex ways to do macro expansions which will be explained in detail in VX-S-8. Generic VSPX form controls Fri, 05 Jun 2009 10:21:02 GMT Scriptable form and form controls Simple form and field validation example The vspx:form control represents scriptable container of a HTML form. It may contains several other controls which are represented below. In the form.vspx example the form contains two vspx:label, two vspx:text and vspx:button controls. In that way vspx:form behaves as control and container, as it may contain other controls and have rendition. The vspx:label controls are shown in that example again to see that these can be used as a child of any vspx: container. Also the values of vspx:label are data bound to the name of control and name of page class. The vspx:text is a text input control which can accept data from user. It may have modifiers which only change appearance from text to password or hidden field. In that example the vspx:text controls are in their default form. The vspx:button control is a generic scriptable button, which originates post events. In practice it renders as a submit button, but with modifiers it can appear as image or link, so in that cases client-side Java scripts are generated to allow posting. The last in that example we should notice is vspx:validator components assigned to the vspx:text fields. These are used to perform server-side validation of data entered by user. So in short the page will display two fields to enter a data, which must be integer numbers. Where the second one must be in the range from 10 to 20. If you enter a different value an error will appear. Check-box example This example presents a scriptable check-box (vspx:check-box) and submit button which is modified to look-like as link. Changing a state of check-box and posting a form will change the value of the label at the bottom. Radio and radio group controls The radio-button and radio-group represented in this example shows usage of radio controls. The main difference in approach is using a container to designate group of radio boxes or to use attribute "group-name" to do that. These are used as a scriptable variant of radio button in HTML. Select list example This example shows the usage of select-list control, which is a VSPX analogue of the select control. Its members must be pre-defined with vspx:item controls which are similar to "option" in HTML variant. Text area example Sometimes a free-form text needs to be entered in a forms, in that case (in HTML practice) textarea is used. In the VSPX world the scriptable variant of it is vspx:textarea. This example shows simple form allowing 50 chars at max to be entered. Inter-field form validation As we noticed in the last and first example we have a vspx:validators to control the user input. But they was per text field. What if we need to check two or more inputs against each other in some order? In that case we can use the form validation. The date.vspx example shows two fields to be filled as date strings. Each filed is tested to be valid date string, and finally if first date is below second the validation will be passed. This is done with validator assigned to the vspx:form control. The default values are initialized to be in incorrect order to see validation on first hit. Data-bound VSPX controls Fri, 05 Jun 2009 10:21:02 GMT Linking a DB data into a VSPX controls Select list initialized from SQL select statement The power of VSPX controls is easy way to bind to a Database objects as tables. In the selectdb.vspx file is shown an easy way to initialize a simple select list from database data. This example shows Products from Nortwind database rendered as select list. The focus on this control is vspx:data-list control. It's initialized with select statement, so the key and value attributes are used to designate what to be used for the drop-down box. Updating a Database table row via VSPX update form Beside showing a data in a form, the database data need to be changed via forms. The most easy way to do that is to use vspx:form with modifier attribute "type" with value of 'update'. The vspx:form[type='update'] used as container of few more elements: vspx:key - binder to a specific row, can be one or more. Usually it's a primary key. Note that this is a not a control, it's a marker in the update form. vspx:template[type='if-exists'] - this is a wrapper class for content to be shown under specific condition. In that case as if-not-exists template is not specified, content of this will be shown in both situations. In other words this example will show content of that template always. The difference will be in value of the fields inside it. vspx:text - it's used to show the data on the row, and accept the data to be updated. These fields have attribute "column" which designate column to be used for data rendition and update. Actually they are inside a template, but logically they are linked to the update form. vspx:button - this is a submit button used to submit the form. The example will: insert a record in Demo.demo.Customers table if CustomerID doesn'y exists will update the CompanyName column if CustomerID is set. Updating a enumerated values in a Database table To run this example we will first need to setup the initial state (using the link beside SQL script, see bellow). This action will cerate a simple table for the next experiment with update form. In some cases we need to enumerate values to the given column in Database table. In this example we will use a vspx:radio-button bound to the column to represent the data value and to allow it's modification to the some degree. (The degree is possible values allowed by group of radio-buttons). In practice we are repeating the previous example but instead using of vspx:text to enter the data , we will use a vspx:radio-button for the second column. The pane at the bottom of demo page shows the result of update, clicking a link of a 'e-id' will load appropriate record to the update form. The button is used to update the data row as in previous example. Simple scroll-able edit-able data grid (vspx:data-set) The most complex control used to view and modify the data in a Database tables is vspx:data-set. This control represents a result set from a SQL select statement as a table (or some other form depending of templates used) and allows scrolling, editing, inserting or deleting a row. In practice it's a combination of scroll-able grid plus one or two update forms. In the example data_set.vspx is shown navigation over Northwind's Customers table. So the following should be noticed in the demo source: The vspx:data-set contains a two simple templates for the header and footer. The middle template is special denoted by "repeat" value of "type" attribute, it's used to render the row in a grid, form to insert, update form in place of a row in a focus and for case of empty result. The repeat template contains four templates for each case as discussed above The template[if-not-exists] will be rendered when no data found The template[edit] will be displayed when edit button is selected The template[add] will be always displayed, if specified The template[browse] is a one per row to show the content on each row The buttons for scrolling as [ds]_next and [ds]_prev must be defined with these suffixes. The above applies to [ds]_edit, [ds]_delete and [ds]_select. In other words some buttons in data-set must have special names.