10:34 a.m.
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