Author: vhalbert(a)redhat.com Date: 2011-04-11 11:34:06 -0400 (Mon, 11 Apr 2011) New Revision: 3081 Added: trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/customer-schema-drop.sql Removed: trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/portfolio-ds.xml Modified: trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/README.txt trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/customer-schema.sql trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/portfolio-vdb.xml Log: TEIID-1519 - changed the dynamicvdb-portfolio example to use the HSQL database provided by jboss as. Modified: trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/README.txt =================================================================== --- trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/README.txt 2011-04-08 19:27:28 UTC (rev 3080) +++ trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/README.txt 2011-04-11 15:34:06 UTC (rev 3081) @@ -1,27 +1,50 @@ -Demonstrates how to use the File Translator to integrate text resources with -a database. This example assumes the use of Derby, but the creation SQL could be -adapted to another database if you choose. +Dynamicvdb-portfolio demonstrates how to federate data from a relational data source with a +text file-based data source. This example uses the HSQL database which is referenced as +the PortfolioDS data source in the server, but the creation SQL could be adapted to another +database if you choose. -Install a recent version of Derby - see http://db.apache.org/derby/derby_downloads.html -Create the example dataset in Derby by running <derby home>/bin/ij customer-schema.sql +Data Source(s) setup: -Put the <derby home>/lib/derbyclient.jar to the "<jboss home>/server/default/lib" directory. +- Start the server (if not already started) +- go to the JMX console (http://localhost:8080/jmx-console/) and select: database=localDB,service=Hypersonic +to present bean options. Now invoke "startDatabaseManager" to bring up HSQL Database Manager. +- Use the File/Open Script menu option to load the teiid-examples/dynamicvdb-portfolio/customer-schema.sql script and and then click Execute SQL to create the required tables and insert the example data. + +Teiid Deployment: + + Copy the following files to the <jboss.home>/server/default/deploy directory. - - portfolio-vdb.xml - - marketdata-file-ds.xml - - portfolio-ds.xml -Start the JBoss Container + (1) portfolio-vdb.xml + (2) marketdata-file-ds.xml + + +Query Demonstrations: -Use the simple client example run script, or another client, to execute a query, e.g. +==== Using the simpleclient example ==== -$./run.sh localhost 31000 dynamicportfolio "select stock.* from product, (call MarketData.getTextFiles('*.txt')) f, TEXTTABLE(f.file COLUMNS symbol string, price bigdecimal HEADER) stock where product.symbol=stock.symbol" +1) Change your working directory to teiid-examples/simpleclient -This example will execute the query against both Derby and the text files. The -files returned from the getTextFiles procedure are passed to the TEXTTABLE table -function (via the nested table correlated reference f.file). The TEXTTABLE -function expects a text file with a HEADER containing entries for at least symbol -and price columns. +2) Use the simpleclient example run script, using the following format +$./run.sh localhost 31000 dynamicportfolio "example query" + + +example queries: + +1 select * from product +2 select stock.* from (call MarketData.getTextFiles('*.txt')) f, TEXTTABLE(f.file COLUMNS symbol string, price bigdecimal HEADER) stock +3. select product.symbol, stock.price, company_name from product, (call MarketData.getTextFiles('*.txt')) f, TEXTTABLE(f.file COLUMNS symbol string, price bigdecimal HEADER) stock where product.symbol=stock.symbol + +Example 1 queries the relational source + +Example 2 queries the text file-based + source + +Example 3 queries both the relational and the text file-based sources. The files returned from the getTextFiles procedure are +passed to the TEXTTABLE table function (via the nested table correlated reference f.file). The TEXTTABLE function expects a +text file with a HEADER containing entries for at least symbol and price columns. + + Added: trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/customer-schema-drop.sql =================================================================== --- trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/customer-schema-drop.sql (rev 0) +++ trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/customer-schema-drop.sql 2011-04-11 15:34:06 UTC (rev 3081) @@ -0,0 +1,21 @@ + +DROP TABLE HOLDINGS +; + +DROP TABLE PRODUCT +; + +DROP TABLE ACCOUNT +; + +DROP TABLE CUSTOMER +; + + + + + + + + + Modified: trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/customer-schema.sql =================================================================== --- trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/customer-schema.sql 2011-04-08 19:27:28 UTC (rev 3080) +++ trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/customer-schema.sql 2011-04-11 15:34:06 UTC (rev 3081) @@ -1,6 +1,6 @@ -CONNECT 'jdbc:derby://localhost:1527/teiid/accounts;create=true;'; -DROP TABLE CUSTOMER; + + CREATE TABLE CUSTOMER ( SSN char(10), @@ -15,7 +15,7 @@ CONSTRAINT CUSTOMER_PK PRIMARY KEY(SSN) ); -DROP TABLE ACCOUNT; + CREATE TABLE ACCOUNT ( ACCOUNT_ID integer, @@ -28,7 +28,7 @@ CONSTRAINT CUSTOMER_FK FOREIGN KEY (SSN) REFERENCES CUSTOMER (SSN) ); -DROP TABLE PRODUCT; + CREATE TABLE PRODUCT ( ID integer, SYMBOL varchar(16), @@ -36,10 +36,10 @@ CONSTRAINT PRODUCT_PK PRIMARY KEY(ID) ); -DROP TABLE HOLDINGS; + CREATE TABLE HOLDINGS ( - TRANSACTION_ID integer GENERATED ALWAYS AS IDENTITY (start with 2000, increment by 1), + TRANSACTION_ID integer IDENTITY, ACCOUNT_ID integer, PRODUCT_ID integer, PURCHASE_DATE timestamp, @@ -49,6 +49,8 @@ CONSTRAINT PRODUCT_FK FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (ID) ); + + INSERT INTO CUSTOMER (SSN,FIRSTNAME,LASTNAME,ST_ADDRESS,APT_NUMBER,CITY,STATE,ZIPCODE,PHONE) VALUES ('CST01002','Joseph','Smith','1234 Main Street','Apartment 56','New York','New York','10174','(646)555-1776'); INSERT INTO CUSTOMER (SSN,FIRSTNAME,LASTNAME,ST_ADDRESS,APT_NUMBER,CITY,STATE,ZIPCODE,PHONE) VALUES ('CST01003','Nicholas','Ferguson','202 Palomino Drive',null,'Pittsburgh','Pennsylvania','15071','(412)555-4327'); INSERT INTO CUSTOMER (SSN,FIRSTNAME,LASTNAME,ST_ADDRESS,APT_NUMBER,CITY,STATE,ZIPCODE,PHONE) VALUES ('CST01004','Jane','Aire','15 State Street',null,'Philadelphia','Pennsylvania','19154','(814)555-6789'); @@ -125,6 +127,7 @@ INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (19990007,1008,{ts '1999-05-11 00:00:00.000'},85); INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (19990008,1005,{ts '1999-05-21 00:00:00.000'},105); INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (19990009,1004,{ts '1999-06-25 00:00:00.000'},120); +INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (19980003,1024,{ts '1999-07-22 00:00:00.000'},150); INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (20000015,1018,{ts '2000-04-20 00:00:00.000'},135); INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (19980006,1030,{ts '2000-06-12 00:00:00.000'},91); INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (20000019,1029,{ts '2000-10-08 00:00:00.000'},351); @@ -135,12 +138,13 @@ INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (20010022,1006,{ts '2001-01-05 00:00:00.000'},237); INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (19990008,1015,{ts '2001-01-23 00:00:00.000'},180); INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (19980005,1025,{ts '2001-03-23 00:00:00.000'},125); -INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (20010027,1020,{ts '2001-08-22 00:00:00.000'},70); +INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (20010027,1024,{ts '2001-08-22 00:00:00.000'},70); INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (20000020,1006,{ts '2001-11-14 00:00:00.000'},125); INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (19980003,1029,{ts '2001-11-15 00:00:00.000'},100); INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (20000021,1011,{ts '2001-12-18 00:00:00.000'},44); +INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (20010027,1028,{ts '2001-12-19 00:00:00.000'},115); INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (20020034,1024,{ts '2002-01-22 00:00:00.000'},189); -INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (19990009,1022,{ts '2002-01-24 00:00:00.000'},30); +INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (19990009,1029,{ts '2002-01-24 00:00:00.000'},30); INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (20020035,1013,{ts '2002-02-12 00:00:00.000'},110); INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (20020035,1034,{ts '2002-02-13 00:00:00.000'},70); INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (20020034,1003,{ts '2002-02-22 00:00:00.000'},25); @@ -153,7 +157,4 @@ INSERT INTO HOLDINGS (ACCOUNT_ID,PRODUCT_ID,PURCHASE_DATE,SHARES_COUNT) VALUES (19980005,1010,{ts '2002-04-01 00:00:00.000'},26); -COMMIT; -DISCONNECT CURRENT; - Deleted: trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/portfolio-ds.xml =================================================================== --- trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/portfolio-ds.xml 2011-04-08 19:27:28 UTC (rev 3080) +++ trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/portfolio-ds.xml 2011-04-11 15:34:06 UTC (rev 3081) @@ -1,21 +0,0 @@ -<?xml version="1.0" encoding="UTF-8"?> -<datasources> - <xa-datasource> - <jndi-name>PortfolioDS</jndi-name> - <isSameRM-override-value>false</isSameRM-override-value> - <xa-datasource-class>org.apache.derby.jdbc.ClientXADataSource</xa-datasource-class> - <xa-datasource-property name="DatabaseName">teiid/accounts</xa-datasource-property> - <xa-datasource-property name="PortNumber">1527</xa-datasource-property> - <xa-datasource-property name="ServerName">localhost</xa-datasource-property> - - <track-connection-by-tx>true</track-connection-by-tx> - <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation> - - <max-pool-size>5</max-pool-size> - <min-pool-size>1</min-pool-size> - <metadata> - <type-mapping>Derby</type-mapping> - </metadata> - </xa-datasource> -</datasources> - Modified: trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/portfolio-vdb.xml =================================================================== --- trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/portfolio-vdb.xml 2011-04-08 19:27:28 UTC (rev 3080) +++ trunk/build/kits/jboss-container/teiid-examples/dynamicvdb-portfolio/portfolio-vdb.xml 2011-04-11 15:34:06 UTC (rev 3081) @@ -9,7 +9,7 @@ "cached" will save a file containing the metadata into the deploy/<vdb name>/<vdb version/META-INF directory --> - <property name="UseConnectorMetadata" value="cached" /> + <property name="UseConnectorMetadata" value="true" /> <!-- @@ -41,9 +41,9 @@ <property name="importer.useFullSchemaName" value="false"/> <!-- - This connector is defined in the "derby-connector-ds.xml" + This connector is defined to reference the HSQL localDS" --> - <source name="derby-connector" translator-name="derby" connection-jndi-name="java:PortfolioDS"/> + <source name="hsql-connector" translator-name="hsql" connection-jndi-name="java:DefaultDS"/> </model> </vdb> \ No newline at end of file