Creating and Deploying an Updateable Teiid Web Service War

Version 1

    Overview

    Within Teiid Designer it is possible to generate a Web Service model from a relational table or tables, add them to a VDB and create a deployable JBossWS-CXF war. The generated model and operations provide select capabilities through Teiid. This is useful in itself, but what about CRUD support against these tables? This article will walk through the steps of creating a Teiid Web Service that can insert, update and delete against federated sources.

     

    Requirements for this example

    • Teiid Designer 7.1.1 (included in JBoss Tools 3.2)

    • JBossAS 5.1 or greater with JBossWS-CXF 3.1.* or greater

    • Input and output schemas for our web service. The project set attached to this article contains an xsd you can use (UpdatesSchema.xsd).

    • Create a Books database instance. You will find the DDL required to create a Books database in the attached artifacts. You can create using any database you prefer, but I created a MySQL instance using this DDL. You may need to tweak it if you want to use another DBMS. This example will refer to MySQL. Create a new data source (MySQL_Books-ds.xml) in your JBossAS instance based on the Books database.

    • A running Teiid 7.2 instance

    • Access to a web service client. I use (and love) soapUI.

    • Some level of familiarity with Teiid Designer. This quick start may be helpful to you: http://community.jboss.org/wiki/TeiidDesigner71QuickStartGuide

     

    The Steps

       1. Create Project

           1.1 In Teiid Designer, click File > New > Teiid Model Project

           1.2 Name the project UpdateableBooksWebService

     

       2. Create Source and View Models

           2.1 Import Books from MySQL using Import->Metadata from JDBC

                 2.1.1 Use Model name = MySQL_Books

                 2.1.2 Uncheck “User” on the “Specify Import Options” page of the wizard

                 2.1.3 Click Finish button.  New source model named “MySQL_Books” should be created.

           2.2 Create Updates View Model

                 2.2.1 Click File > New > Teiid Metadata Model

                 2.2.2 Model Name = Updates

                 2.2.3 Model Class = Relational

                 2.2.4 Model Type = View Model

                 2.2.5 Click Finish

                 2.2.6 Right-click Updates > New Child > Base Table

                 2.2.7 Name the base table UpdateBooks

                 2.2.8 Open the Transformation Editor

                 2.2.9 Enter this SQL to create the transformation:

     

    SELECT MySQL_Books.BOOKS.ISBN, TITLE, SUBTITLE, PUBLISHER,

    PUBLISH_YEAR, EDITION, TYPE, MySQL_Books.BOOK_AUTHORS.ISBN AS ISBN_1, AUTHOR_ID

    FROM MySQL_Books.BOOKS, MySQL_Books.BOOK_AUTHORS

     

                 2.2.10 Change the “Supports Update” property of UpdateBooks to true

                 2.2.11 Go to the INSERT tab

                 2.2.12 Uncheck “Use Default”

                 2.2.13 Enter this SQL to define the INSERT procedure:

       

      CREATE PROCEDURE

      BEGIN

      DECLARE integer VARIABLES.KOUNT;

      IF(("INPUT".ISBN IS NULL) OR ("INPUT".TITLE IS NULL) OR ("INPUT".SUBTITLE IS NULL) OR ("INPUT".PUBLISHER IS NULL) OR ("INPUT".PUBLISH_YEAR IS NULL) OR ("INPUT".EDITION IS NULL) OR ("INPUT".EDITION IS NULL) OR ("INPUT".TYPE IS NULL))

      BEGIN

      ERROR 'The following elements ISBN, TITLE, SUBTITLE, PUBLISHER, PUBLISH_YEAR, EDITION and TYPE are not nullable, non-null values should be inserted for these elements';

      END

      ELSE

      BEGIN

      VARIABLES.KOUNT = SELECT COUNT(*) FROM MySQL_Books.BOOKS WHERE MySQL_Books.BOOKS.ISBN = "INPUT".ISBN;

      IF(VARIABLES.KOUNT > 0)

      BEGIN

      ERROR 'Specified ISBN already exists in database - Choose another ISBN to insert';

      END

      ELSE

      BEGIN

      INSERT INTO MySQL_Books.BOOKS (MySQL_Books.Books.ISBN, MySQL_Books.Books.TITLE, MySQL_Books.Books.SUBTITLE, MySQL_Books.Books.PUBLISHER, MySQL_Books.Books.PUBLISH_YEAR, MySQL_Books.Books.EDITION, MySQL_Books.Books.TYPE) VALUES ("INPUT".ISBN, "INPUT".TITLE, "INPUT".SUBTITLE, "INPUT".PUBLISHER, "INPUT".PUBLISH_YEAR, "INPUT".EDITION, "INPUT".TYPE);

      VARIABLES.ROWS_UPDATED = INSERT INTO MySQL_Books.BOOK_AUTHORS (MySQL_Books.BOOK_AUTHORS.ISBN, MySQL_Books.BOOK_AUTHORS.AUTHOR_ID) VALUES ("INPUT".ISBN, "INPUT".AUTHOR_ID);

      END

      END

      END

                    2.2.14 Switch to the DELETE Tab

                    2.2.15 Uncheck “Use Default“

                    2.2.16 Enter this SQL to define the DELETE procedure:

       

      CREATE PROCEDURE

      BEGIN

      DECLARE string VARIABLES.ISBN;

      IF(HAS CRITERIA ON (Updates.UpdateBooks.ISBN))

      BEGIN

      VARIABLES.ISBN = SELECT MySQL_Books.BOOKS.ISBN FROM MySQL_Books.BOOKS WHERE TRANSLATE CRITERIA ON (Updates.UpdateBooks.ISBN);

      VARIABLES.ROWS_UPDATED = DELETE FROM MySQL_Books.BOOKS WHERE MySQL_Books.BOOKS.ISBN = VARIABLES.ISBN;

      DELETE FROM MySQL_Books.BOOK_AUTHORS WHERE MySQL_Books.BOOK_AUTHORS.ISBN = VARIABLES.ISBN;

      END

      END

       

                    2.2.17 Switch to the UPDATE tab

                    2.2.18 Uncheck “Use Default“

                    2.2.19 Enter this SQL (updates the TITLE and SUBTITLE) to define the UPDATE procedure:

       

        CREATE PROCEDURE

        BEGIN

        DECLARE string VARIABLES.ISBN;

        IF(HAS CRITERIA ON (Updates.UpdateBooks.ISBN))

        BEGIN

        VARIABLES.ISBN = SELECT MySQL_Books.BOOKS.ISBN FROM MySQL_Books.BOOKS WHERE TRANSLATE CRITERIA ON (Updates.UpdateBooks.ISBN);

        IF(VARIABLES.ISBN IS NULL)

        BEGIN

        ERROR 'Input ISBN not found in Books table';

        END

        ELSE

        BEGIN

        VARIABLES.ROWS_UPDATED = UPDATE MySQL_Books.BOOKS SET TITLE = "INPUT".TITLE, SUBTITLE = "INPUT".SUBTITLE WHERE (MySQL_Books.BOOKS.ISBN = VARIABLES.ISBN) AND (TRANSLATE = CRITERIA ON (Updates.UpdateBooks.ISBN));

        END

        END

        ELSE

        BEGIN

        ERROR 'Update must specify ISBN';

        END

        END

         

                     2.2.20 Save the Updates model

           

             3. Import XML Schema and Create XML Documents

                 3.1 Import UpdatesSchema.xsd from the file system

                       3.1.1 Right-click UpdatesSchema > New > Teiid Metadata Model

                       3.1.2 Model Name = BooksDoc

                       3.1.3 Model Class = XML

                       3.1.4 Model Type = View Model

                       3.1.5 Select the “Build XML documents from XML schema” optional builder

                       3.1.6 Click Next

                       3.1.7 Build the document from the BooksUpdate, BooksInput and putResults schema root elements and click Finish.

                       3.1.8 Expand BooksDoc until putResultsDocument is visible

                       3.1.9 Rename “putResultsDocument” to “goodResultsDocument”

                       3.1.10 Right-click goodResultsDocument > New Sibling > XML Document

                       3.1.11 Choose UpdatesSchema as the XML Schema File

                       3.1.12 Select putResults and move it to the Virtual Documents side of the accumulator

                       3.1.13 Click Finish

                       3.1.14 Rename “putResultsDocument” to “badResultsDocument”

                       3.1.15 Define the transformation for BooksInputDocument:

           

          SELECT * FROM Updates.UpdateBooks

           

                       3.1.16 Define the transformation for goodResultsDocument:

             

            SELECT 'Operation Successful!' AS results

             

                         3.1.17 Define the transformation for badResultsDocument:

               

              SELECT 'Operation Failed' AS results

               

                           3.1.18 Save BooksDoc

               

                 4. Create Web Service

                     4.1 Click File > New > Metadata Model

                           4.1.1 Model name = BooksWebSvc

                           4.1.2 Model class = Web Service

                           4.1.3 Model type = View Model

                     4.2 Right-click BooksWebSvc > New Child > Interface

                           4.2.1 Name the interface “BooksInterface”

                     4.3 Define the Insert Operation

                           4.3.1 To BooksInterface add a child operation

                           4.3.2 Name the operation “InsertBook”

                     4.4 Add an input to the operation

                           4.4.1 Name = bookIn

                           4.4.2 Content via Element = BooksInput

                           4.4.3 Select the output operation “NewOutput”

                           4.4.4 Set the following properties:

                                    4.4.4.1 Content via Element = putResults

                                    4.4.4.2 XML Document = goodResultsDocument

                           4.4.5 Open the transformation diagram

                           4.4.6 Switch to the Operation Editor

                           4.4.7 Enter the following to define the operation:

               

              CREATE VIRTUAL PROCEDURE

              BEGIN

              DECLARE integer VARIABLES.update_count = 0;

              DECLARE string VARIABLES.IN_ISBN = xpathvalue(BOOKSWEBSVC.BOOKSINTERFACE.INSERTBOOK.bookIn, '/*:BooksInput/*:Books/*:ISBN');

              DECLARE string VARIABLES.IN_TITLE = xpathvalue(BOOKSWEBSVC.BOOKSINTERFACE.INSERTBOOK.bookIn, '/*:BooksInput/*:Books/*:TITLE');

              DECLARE string VARIABLES.IN_SUBTITLE = xpathvalue(BOOKSWEBSVC.BOOKSINTERFACE.INSERTBOOK.bookIn, '/*:BooksInput/*:Books/*:SUBTITLE');

              DECLARE string VARIABLES.IN_PUBLISHER = xpathvalue(BOOKSWEBSVC.BOOKSINTERFACE.INSERTBOOK.bookIn, '/*:BooksInput/*:Books/*:PUBLISHER');

              DECLARE string VARIABLES.IN_PUBLISH_YEAR = xpathvalue(BOOKSWEBSVC.BOOKSINTERFACE.INSERTBOOK.bookIn, '/*:BooksInput/*:Books/*:PUBLISH_YEAR');

              DECLARE string VARIABLES.IN_EDITION = xpathvalue(BOOKSWEBSVC.BOOKSINTERFACE.INSERTBOOK.bookIn, '/*:BooksInput/*:Books/*:EDITION');

              DECLARE string VARIABLES.IN_TYPE = xpathvalue(BOOKSWEBSVC.BOOKSINTERFACE.INSERTBOOK.bookIn, '/*:BooksInput/*:Books/*:TYPE');

              VARIABLES.update_count = INSERT INTO Updates.UpdateBooks (Updates.UpdateBooks.ISBN, Updates.UpdateBooks.TITLE, Updates.UpdateBooks.SUBTITLE, Updates.UpdateBooks.PUBLISHER, Updates.UpdateBooks.PUBLISH_YEAR, Updates.UpdateBooks.EDITION, Updates.UpdateBooks.TYPE) VALUES (VARIABLES.IN_ISBN, VARIABLES.IN_TITLE, VARIABLES.IN_SUBTITLE, convert(VARIABLES.IN_PUBLISHER, biginteger), convert(VARIABLES.IN_PUBLISH_YEAR, biginteger), convert(VARIABLES.IN_EDITION, biginteger), VARIABLES.IN_TYPE);

              IF(VARIABLES.update_count = 1)

              BEGIN

              SELECT * FROM BooksDoc.goodResultsDocument;

              END

              ELSE

              BEGIN

              SELECT * FROM BooksDoc.badResultsDocument;

              END

              END

               

                           4.4.8 Validate and save the transformation

                     4.5 Define the Delete Operation

                           4.5.1 To BookInterface add a child operation

                           4.5.2 Name the operation “DeleteBook”

                           4.5.3 Add an input to the operation

                                    4.5.3.1 Content via Element = ISBNInput

                                    4.5.3.2 Select the output operation “NewOutput”

                                    4.5.3.3 Set the following properties:

                                                4.5.3.3.1 Content via Element = putResults

                                                4.5.3.3.2 XML Document = goodResultsDocument

                           4.5.4 Open the transformation diagram

                           4.5.5 Switch to the Operation Editor

                           4.5.6 Enter the following to define the operation:

                       

                      CREATE VIRTUAL PROCEDURE

                      BEGIN

                      DECLARE integer VARIABLES.delete_count = 0;

                      DECLARE string VARIABLES.IN_ISBN = xpathvalue(BOOKSWEBSVC.BOOKSINTERFACE.DELETEBOOK.isbnIn, '/*:ISBNInput/*:ISBN');

                      VARIABLES.delete_count = DELETE FROM Updates.UpdateBooks WHERE Updates.UpdateBooks.ISBN = VARIABLES.IN_ISBN;

                      IF(VARIABLES.delete_count = 1)

                      BEGIN

                      SELECT * FROM BooksDoc.goodResultsDocument;

                      END

                      ELSE

                      BEGIN

                      SELECT * FROM BooksDoc.badResultsDocument;

                      END

                      END

                       

                             4.6 Define the Update Operation

                                   4.6.1 To BookInterface add a child operation

                                   4.6.2 Name the operation “UpdateBook”

                                   4.6.3 Add an input to the operation

                                            4.6.3.1 Name = bookIn

                                            4.6.3.2 Content via Element = BooksUpdate

                                   4.6.4 Select the output operation “NewOutput”

                                            4.6.4.1 Set the following properties:

                                                        4.6.4.1.1 Content via Element = putResults

                                                        4.6.4.1.2 XML Document = goodResultsDocument

                                   4.6.5 Open the transformation diagram

                                   4.6.6 Switch to the Operation Editor

                                     4.6.7 Enter the following to define the operation:

                           

                          CREATE VIRTUAL PROCEDURE

                          BEGIN

                          DECLARE integer VARIABLES.update_count = 0;

                          DECLARE string VARIABLES.IN_ISBN = xpathvalue(BOOKSWEBSVC.BOOKSINTERFACE.UPDATEBOOK.bookIn, '/*:BooksUpdate/*:Books/*:ISBN');

                          DECLARE string VARIABLES.IN_TITLE = xpathvalue(BOOKSWEBSVC.BOOKSINTERFACE.UPDATEBOOK.bookIn, '/*:BooksUpdate/*:Books/*:TITLE');

                          DECLARE string VARIABLES.IN_SUBTITLE = xpathvalue(BOOKSWEBSVC.BOOKSINTERFACE.UPDATEBOOK.bookIn, '/*:BooksUpdate/*:Books/*:SUBTITLE');

                          VARIABLES.update_count = UPDATE Updates.UpdateBooks SET TITLE = VARIABLES.IN_TITLE, SUBTITLE = VARIABLES.IN_SUBTITLE WHERE Updates.UpdateBooks.ISBN = VARIABLES.IN_ISBN;

                          SELECT * FROM BooksDoc.badResultsDocument;

                          END

                           

                                       4.6.8 Validate and save the transformation

                                 4.7 Save All

                           

                             5. Create the VDB

                                 5.1 Add a VDB to the project (call it whatever you want). I called mine BooksWebSvc.

                                 5.2 To the VDB, add the BooksWebSvc model. The remaining models will automatically added to the VDB based on the dependencies.

                                 5.3 Save the VDB

                           

                             6. Create Teiid Server Instance and Deploy VDB

                                 6.1 In the “Teiid” view of Designer, right-click to add new Teiid instance.

                                 6.2 Right-click on your VDB in the Model Explorer and click Modeling > Deploy. Your VDB will now be available in your Teiid instance.

                                 6.3 Create your Teiid data source using the VDB you just deployed. This can be achieved in the Designer “Teiid” view by right-clicking on the VDB and clicking > Create Data Source. The name will be the JNDI name used in the war generation wizard. Choose the connection profile for your VDB.

                           

                          createdatasource.png

                           

                             7. Generate a JBossWS-CXF War

                                 7.1 Right-click the VDB and click Modeling > Generate JBossWS-CXF War.

                                 7.2  Change the Connection JNDI Name to java:<JNDI Name for Teiid VDB  Datasource created in step 6.3>. Feel free to change any of the  other properties as desired. I will generate a war without security  for purposes of this example. You can use HTTPBasic or WS-Security if  you prefer.

                                   7.3 Click OK. Deploy the generated war to your JBossAS instance.

                             

                             

                               8. Test Your New Web Service

                                   8.1 Get your WSDL URL from http://<host>:<port>/jbossws/services.

                                   8.2  Start your Web service testing tool (soapUI perhaps)

                                   8.3 Import the WSDL into your testing tool

                                   8.4 Invoke the InsertBooks operation

                                   8.5 Enter the required data. Here's what my SOAP request looks like:

                             

                            <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:upd="http://www.teiid.org/UpdateBooks_Output">

                            <soapenv:Header/>

                            <soapenv:Body>

                            <upd:BooksInput>

                            <!--1 or more repetitions:-->

                            <Books>

                            <ISBN>999-999-999</ISBN>

                            <TITLE>Todd Rundgren</TITLE>

                            <SUBTITLE>A Wizard, a true Star</SUBTITLE>

                            <PUBLISHER>6</PUBLISHER>

                            <PUBLISH_YEAR>2010</PUBLISH_YEAR>

                            <EDITION>1</EDITION>

                            <TYPE>Hardback</TYPE>

                            </Books>

                            </upd:BooksInput>

                            </soapenv:Body>

                            </soapenv:Envelope>

                             

                                   8.6 Execute the operation and look for the response stating “Operation Successful!”.

                                   8.7 Execute the UpdateBook operation, using the ISBN used when inserting  the book and change the Title and sub-title. Look for the response  stating “Operation Successful!”

                                   8.8 Execute the DeleteBook operation, using the ISBN used when inserting the book and change the Title and sub-title. Look for the response stating “Operation Successful!”

                               

                              That's an example of how to model and deploy an updateable web service in Teiid Designer. I have attached my model project set to this article so that you may import it into your Designer. Hopefully this will prove as a helpful starting point for whatever your use case may look like.