diff options
Diffstat (limited to 'das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework')
9 files changed, 1248 insertions, 0 deletions
diff --git a/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DB2Setup.java b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DB2Setup.java new file mode 100644 index 0000000000..370c1d42d7 --- /dev/null +++ b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DB2Setup.java @@ -0,0 +1,37 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.tuscany.das.rdb.test.framework; + +import junit.framework.Test; + +public class DB2Setup extends DatabaseSetup { + + public DB2Setup(Test test) { + super(test); + } + + protected void initConnectionProtocol() { + + platformName = "DB2"; + driverName = "com.ibm.db2.jcc.DB2Driver"; + databaseURL = "jdbc:db2:DASTEST"; + + } + +} diff --git a/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DasTest.java b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DasTest.java new file mode 100644 index 0000000000..78b1cf19e1 --- /dev/null +++ b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DasTest.java @@ -0,0 +1,147 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.tuscany.das.rdb.test.framework; + +import java.io.InputStream; +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.util.Iterator; +import java.util.List; + +import junit.framework.TestCase; + +import commonj.sdo.DataObject; +import commonj.sdo.Property; + +/** + * + */ +public class DasTest extends TestCase { + + protected static Connection connection; + + /** + * Tears down the fixture, for example, close a network connection. This method is called after a test is executed. + */ + protected void tearDown() throws Exception { + // if (usingDefaultSetup) + // connection = null; + } + + protected Connection getAutoConnection() throws SQLException { + + Connection c = primGetConnection(); + c.setAutoCommit(true); + return connection; + + } + + protected Connection getConnection() throws SQLException { + + Connection c = primGetConnection(); + c.setAutoCommit(false); + return connection; + } + + /** + * This provides the default connection for runing single test cases on a chosen platform. + */ + private Connection primGetConnection() { + if (connection == null) { + defaultSetup(); + } + return connection; + } + + /** + * This is a bit of a hack since it counts on constructor initialization + * of the DatabaseSet up class and also calls its setUp method directly. + * This is a misuse of this JUnit TestSetup subclass . + * + * TODO - refactor to avoid this hackiness ... could move this logic to its + * own class that is then invoked by DatabaseSetUp + */ + private void defaultSetup() { + + // DatabaseSetup setUp = new DB2Setup(this); + DatabaseSetup setUp = new DerbySetup(this); + try { + setUp.setUp(); + } catch (Exception e) { + throw new RuntimeException(e); + } + + } + + // Utilities + protected InputStream getConfig(String fileName) { + return Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName); + } + + protected void write(String label, ResultSet rs) throws SQLException { + + ResultSetMetaData md = rs.getMetaData(); + int count = md.getColumnCount(); + System.out.println("Contents of ResultSet from " + label); + for (int i = 1; i <= count; i++) { + System.out.print("\t"); + System.out.println(md.getColumnLabel(i)); + } + System.out.println(""); + while (rs.next()) { + for (int i = 1; i <= count; i++) { + System.out.print("\t"); + System.out.print(rs.getString(i)); + } + System.out.println("\t"); + } + System.out.println("done"); + } + + protected void printList(List data) { + Iterator i = data.iterator(); + while (i.hasNext()) { + System.out.println(); + DataObject obj = (DataObject) i.next(); + Iterator props = obj.getType().getProperties().iterator(); + while (props.hasNext()) { + Property p = (Property) props.next(); + if (p.isMany()) { + System.out.print("[ " + p.getName() + " ] "); + Iterator children = obj.getList(p).iterator(); + while (children.hasNext()) { + DataObject child = (DataObject) children.next(); + System.out.print("[ " + child.get("ID") + " ]"); + } + System.out.println(); + } else if (!p.getType().isDataType()) { + DataObject child = obj.getDataObject(p); + if (child != null) { + System.out.println("[ " + p.getName() + " ] " + "[ " + child.get("ID") + " ]"); + } + } else { + System.out.println("[ " + p.getName() + " ] " + obj.get(p)); + } + } + } + } + +} diff --git a/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DatabaseSetup.java b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DatabaseSetup.java new file mode 100644 index 0000000000..0045e10618 --- /dev/null +++ b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DatabaseSetup.java @@ -0,0 +1,525 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.tuscany.das.rdb.test.framework; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.SQLException; +import java.sql.Statement; + +import junit.extensions.TestSetup; +import junit.framework.Test; + +public class DatabaseSetup extends TestSetup { + + protected Statement s; + + protected String platformName = "Not initialized"; + + protected String driverName = "Not initialized"; + + protected String databaseURL = "Not initialized"; + + protected String userName; + + protected String password; + + // Data Types + protected String stringType = "VARCHAR"; + + protected String integerType = "INT"; + + protected String timestampType = "TIMESTAMP"; + + protected String floatType = "FLOAT"; + + protected String decimalType = "DECIMAL"; + + private Connection connection; + + public DatabaseSetup(Test test) { + super(test); + initConnectionProtocol(); + initConnection(); + DasTest.connection = connection; + } + + protected void initConnectionProtocol() { + // Subclasses provide implementation + } + + private void initConnection() { + + try { + + Class.forName(driverName).newInstance(); + if (userName != null) { + connection = DriverManager.getConnection(databaseURL, userName, password); + } else { + connection = DriverManager.getConnection(databaseURL); + } + connection.setAutoCommit(false); + + } catch (SQLException e) { + + if (e.getNextException() != null) { + e.getNextException().printStackTrace(); + } else { + e.printStackTrace(); + } + + throw new RuntimeException(e); + } catch (IllegalAccessException e) { + throw new RuntimeException(e); + } catch (InstantiationException e) { + throw new RuntimeException(e); + } catch (ClassNotFoundException e) { + throw new RuntimeException(e); + } + + } + + protected void setUp() throws Exception { + + System.out.println("Setting up for " + platformName + " run"); + + s = connection.createStatement(); + + try { + dropTriggers(); + dropSequences(); + dropTables(); + dropProcedures(); + dropSchema();//JIRA-952 + + createSchema();//JIRA-952 + createSequences(); + createTables(); + createTriggers(); + createProcedures(); + connection.commit(); + } catch (SQLException e) { + connection.rollback(); + } + + } + + protected void tearDown() throws Exception { + + System.out.println("Ending " + platformName + " run"); + connection.close(); + + } + + //JIRA-952 + private void dropSchema(){ + String[] statements = {"DROP SCHEMA DASTEST1 RESTRICT", + "DROP SCHEMA DASTEST2 RESTRICT", + "DROP SCHEMA DASTEST3 RESTRICT", + }; + + for (int i = 0; i < statements.length; i++) { + try { + s.execute(statements[i]); + } catch (SQLException e) { + // If the table does not exist then ignore the exception on drop + if ((!e.getMessage().contains("does not exist")) && (!e.getMessage().contains("Unknown table")) + && (!e.getMessage().contains("42704"))) { + throw new RuntimeException(e); + } + } + } + } + + private void dropTables() { + + String[] statements = { + "DROP TABLE CUSTOMER", "DROP TABLE ANORDER", "DROP TABLE ORDERDETAILS", + "DROP TABLE ORDERDETAILSDESC", "DROP TABLE ITEM", "DROP TABLE COMPANY", + "DROP TABLE EMPLOYEE", "DROP TABLE DEPARTMENT", "DROP TABLE BOOK", + "DROP TABLE PART", "DROP TABLE TYPETEST", "DROP TABLE CITIES", + "DROP TABLE STATES", "DROP TABLE conmgt.SERVERSTATUS", + "DROP TABLE DOG", "DROP TABLE OWNER", "DROP TABLE KENNEL", + "DROP TABLE VISIT", + "DROP TABLE DASTEST1.CUSTOMER" ,"DROP TABLE DASTEST2.CUSTOMER","DROP TABLE DASTEST3.CUSTOMER", + "DROP TABLE DASTEST1.CITY", "DROP TABLE DASTEST2.CITY", + "DROP TABLE DASTEST2.ACCOUNT", + "DROP TABLE DASTEST3.CUSTORDER", + "DROP TABLE DASTEST3.ORDERDETAILSDESC", "DROP TABLE DASTEST1.ORDERDETAILS", + "DROP TABLE DASTEST1.EMPLOYEE" + }; + + for (int i = 0; i < statements.length; i++) { + try { + s.execute(statements[i]); + } catch (SQLException e) { + // If the table does not exist then ignore the exception on drop + if ((!e.getMessage().contains("does not exist")) && (!e.getMessage().contains("Unknown table")) + && (!e.getMessage().contains("42704"))) { + throw new RuntimeException(e); + } + } + } + } + + protected void dropTriggers() { + + } + + protected void createTriggers() { + + } + + protected void dropSequences() { + + } + + protected void createSequences() { + + } + + protected void dropProcedures() { + + // System.out.println("Dropping procedures"); + + String[] statements = { + + "DROP PROCEDURE GETALLCOMPANIES", "DROP PROCEDURE DELETECUSTOMER", + "DROP PROCEDURE GETNAMEDCOMPANY", "DROP PROCEDURE GETCUSTOMERANDORDERS", + "DROP PROCEDURE GETNAMEDCUSTOMERS", "DROP PROCEDURE GETALLCUSTOMERSANDORDERS" + + }; + + for (int i = 0; i < statements.length; i++) { + try { + s.execute(statements[i]); + } catch (SQLException e) { + // If the proc does not exist then ignore the exception on drop + if (!e.getMessage().contains("does not exist") && !e.getMessage().contains("42704")) { + throw new RuntimeException(e); + } + } + } + } + + //JIRA-952 + private void createSchema() { + // System.out.println("Creating schema"); + try { + + s.execute("CREATE SCHEMA DASTEST1"); + s.execute("CREATE SCHEMA DASTEST2"); + s.execute("CREATE SCHEMA DASTEST3"); + }catch (SQLException e) { + throw new RuntimeException(e); + } + } + private void createTables() { + + // System.out.println("Creating tables"); + + try { + + s.execute(getCreateCustomer()); + s.execute(getCreateAnOrder()); + s.execute(getCreateOrderDetails()); + s.execute(getCreateItem()); + s.execute(getCreateCompany()); + s.execute(getCreateEmployee()); + s.execute(getCreateDepartment()); + s.execute(getCreateBook()); + s.execute(getCreatePart()); + s.execute(getCreateTypeTest()); + s.execute(getCreateStates()); + s.execute(getCreateCities()); + s.execute(getCreateServerStatus()); + + s.execute(getCreateDog()); + s.execute(getCreateOwner()); + s.execute(getCreateKennel()); + s.execute(getCreateVisit()); + s.execute(getCreateOrderDetailsDesc());//JIRA-841 + + //JIRA-952 start + s.execute(getCreateDASTEST1Customer()); + s.execute(getCreateDASTEST1Employee()); + s.execute(getCreateDASTEST2Customer()); + s.execute(getCreateDASTEST3Customer()); + s.execute(getCreateDASTEST1City()); + s.execute(getCreateDASTEST2City()); + s.execute(getCreateDASTEST2Account()); + s.execute(getCreateDASTEST3CustOrder()); + s.execute(getCreateDASTEST1OrderDetails()); + s.execute(getCreateDASTEST3OrderDetailsDesc()); + //JIRA-952 end + } catch (SQLException e) { + throw new RuntimeException(e); + } + } + + protected void createProcedures() { + + // System.out.println("Creating procedures"); + try { + + s.execute("CREATE PROCEDURE GETALLCOMPANIES() PARAMETER STYLE JAVA LANGUAGE JAVA " + + "READS SQL DATA DYNAMIC RESULT SETS 1 " + + "EXTERNAL NAME " + + "'org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getAllCompanies'"); + s.execute("CREATE PROCEDURE DELETECUSTOMER(theId int) PARAMETER STYLE JAVA LANGUAGE JAVA " + + "MODIFIES SQL DATA EXTERNAL NAME " + + "'org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.deleteCustomer'"); + s.execute("CREATE PROCEDURE GETNAMEDCOMPANY(theName VARCHAR(100)) PARAMETER STYLE JAVA LANGUAGE JAVA " + + "READS SQL DATA DYNAMIC RESULT SETS 1 EXTERNAL NAME " + + "'org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getNamedCompany'"); + s.execute("CREATE PROCEDURE GETCUSTOMERANDORDERS(theID INTEGER) PARAMETER STYLE JAVA LANGUAGE JAVA " + + "READS SQL DATA DYNAMIC RESULT SETS 1 EXTERNAL NAME " + + "'org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getCustomerAndOrders'"); + s.execute("CREATE PROCEDURE GETNAMEDCUSTOMERS(theName VARCHAR(100), OUT theCount INTEGER) " + + "PARAMETER STYLE JAVA LANGUAGE JAVA READS SQL DATA DYNAMIC RESULT SETS 1 EXTERNAL NAME " + + "'org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getNamedCustomers'"); + s.execute("CREATE PROCEDURE GETALLCUSTOMERSANDORDERS() PARAMETER STYLE JAVA LANGUAGE JAVA " + + "READS SQL DATA DYNAMIC RESULT SETS 2 EXTERNAL NAME " + + "'org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getAllCustomersAndAllOrders'"); + // TODO - "GETNAMEDCUSTOMERS" is failing on DB2 with SQLCODE: 42723. Need to investigate + } catch (SQLException e) { + throw new RuntimeException(e); + } + } + + // + // This section povides methods that return strings for table creation. + // Platform-specific sublcasses + // can override these as necessary + // + + protected String getCreateCustomer() { + return "CREATE TABLE CUSTOMER (" + getIntegerColumn("ID") + " PRIMARY KEY NOT NULL, " + + getStringColumn("LASTNAME", 30) + + " DEFAULT 'Garfugengheist', " + getStringColumn("ADDRESS", 30) + ")"; + } + + protected String getCreateAnOrder() { + return "CREATE TABLE ANORDER (" + getIntegerColumn("ID") + " PRIMARY KEY NOT NULL, " + + getStringColumn("PRODUCT", 30) + ", " + + getIntegerColumn("QUANTITY") + "," + getIntegerColumn("CUSTOMER_ID") + ")"; + } + + protected String getCreateOrderDetails() { + return "CREATE TABLE ORDERDETAILS (" + getIntegerColumn("ORDERID") + " NOT NULL, " + + getIntegerColumn("PRODUCTID") + + " NOT NULL, PRICE FLOAT, PRIMARY KEY (ORDERID, PRODUCTID))"; + } + + protected String getCreateItem() { + return "CREATE TABLE ITEM (" + getIntegerColumn("ID") + " PRIMARY KEY NOT NULL, " + + getStringColumn("NAME", 30) + ")"; + } + + protected String getCreateCompany() { + return "CREATE TABLE COMPANY (" + getIntegerColumn("ID") + " PRIMARY KEY NOT NULL " + + getGeneratedKeyClause() + " , " + + getStringColumn("NAME", 30) + ", " + getIntegerColumn("EOTMID") + ")"; + } + + protected String getCreateEmployee() { + return "CREATE TABLE EMPLOYEE (" + getIntegerColumn("ID") + " PRIMARY KEY NOT NULL " + + getGeneratedKeyClause() + "," + + getStringColumn("NAME", 30) + "," + getStringColumn("SN", 10) + ", MANAGER SMALLINT, " + + getIntegerColumn("DEPARTMENTID") + ")"; + } + + protected String getCreateDepartment() { + return "CREATE TABLE DEPARTMENT (" + getIntegerColumn("ID") + " PRIMARY KEY NOT NULL " + + getGeneratedKeyClause() + ", " + + getStringColumn("NAME", 30) + "," + getStringColumn("LOCATION", 30) + ", " + + getStringColumn("DEPNUMBER", 10) + "," + + getIntegerColumn("COMPANYID") + ")"; + } + + protected String getCreateBook() { + return "CREATE TABLE BOOK (" + getIntegerColumn("BOOK_ID") + " PRIMARY KEY NOT NULL, " + + getStringColumn("NAME", 50) + "," + + getStringColumn("AUTHOR", 30) + ", " + getIntegerColumn("QUANTITY") + "," + + getIntegerColumn("OCC") + ")"; + } + + protected String getCreatePart() { + return "CREATE TABLE PART (" + getIntegerColumn("ID") + " PRIMARY KEY NOT NULL, NAME VARCHAR(50), " + + getIntegerColumn("QUANTITY") + "," + + getIntegerColumn("PARENT_ID") + " )"; + } + + protected String getCreateTypeTest() { + return "CREATE TABLE TYPETEST (" + getIntegerColumn("ID") + " PRIMARY KEY NOT NULL, " + + getTimestampColumn("ATIMESTAMP") + "," + + getDecimalColumn("ADECIMAL", 9, 2) + "," + getFloatColumn("AFLOAT") + ")"; + } + + protected String getCreateStates() { + return "CREATE TABLE STATES (" + getIntegerColumn("ID") + " PRIMARY KEY NOT NULL, " + + getStringColumn("NAME", 2) + ")"; + } + + protected String getCreateCities() { + return "CREATE TABLE CITIES (" + getIntegerColumn("ID") + " PRIMARY KEY NOT NULL," + + getStringColumn("NAME", 50) + "," + + getIntegerColumn("STATE_ID") + "," + getForeignKeyConstraint("STATES", "ID", "STATE_ID") + ")"; + } + + protected String getCreateServerStatus() { + + return "CREATE TABLE CONMGT.SERVERSTATUS (STATUSID INTEGER PRIMARY KEY NOT NULL " + getGeneratedKeyClause() + + " (START WITH 1 ,INCREMENT BY 1), MANAGEDSERVERID INTEGER NOT NULL, TIMESTAMP TIMESTAMP NOT NULL)"; + + } + + // Dog Kennel Schema + + protected String getCreateDog() { + return "CREATE TABLE DOG (" + getIntegerColumn("ID") + " NOT NULL " + getGeneratedKeyClause() + " , " + + getIntegerColumn("OWNER_ID") + " , " + + getStringColumn("NAME", 20) + ", " + getStringColumn("BREED", 20) + ", " + + getIntegerColumn("OCC_COUNT") + ", " + + "PRIMARY KEY(ID))"; + } + + protected String getCreateOwner() { + return "CREATE TABLE OWNER (" + getIntegerColumn("ID") + " NOT NULL " + getGeneratedKeyClause() + " , " + + getStringColumn("NAME", 20) + ", " + + getStringColumn("CONTACT_PHONE", 20) + ", " + getIntegerColumn("OCC_COUNT") + ", " + + "PRIMARY KEY(ID))"; + } + + protected String getCreateKennel() { + return "CREATE TABLE KENNEL (" + getIntegerColumn("ID") + " NOT NULL " + getGeneratedKeyClause() + + " , " + getIntegerColumn("KNUMBER") + ", " + + getStringColumn("KIND", 20) + ", " + getIntegerColumn("OCC_COUNT") + ", " + "PRIMARY KEY(ID))"; + } + + protected String getCreateVisit() { + return "CREATE TABLE VISIT (" + getIntegerColumn("ID") + " NOT NULL " + getGeneratedKeyClause() + " , " + + getTimestampColumn("CHECK_IN") + + ", " + getTimestampColumn("CHECK_OUT") + ", " + getIntegerColumn("OCC_COUNT") + ", " + + "PRIMARY KEY(ID))"; + } + + protected String getCreateOrderDetailsDesc() { + return "CREATE TABLE ORDERDETAILSDESC ("+ getIntegerColumn("ID") + " NOT NULL, " + getIntegerColumn("ORDERID") + " NOT NULL, " + + getIntegerColumn("PRODUCTID") + + " NOT NULL,"+ getStringColumn("DESCR", 20)+", PRIMARY KEY (ID))"; + } + + //JIRA-952 start + protected String getCreateDASTEST1Customer() { + return "CREATE TABLE DASTEST1.CUSTOMER (" + getIntegerColumn("ID") + " PRIMARY KEY NOT NULL, " + + getStringColumn("LASTNAME", 30) + + " DEFAULT 'Garfugengheist', " + getStringColumn("ADDRESS", 30) + ")"; + } + + protected String getCreateDASTEST1Employee() { + return "CREATE TABLE DASTEST1.EMPLOYEE (" + getIntegerColumn("ID") + " PRIMARY KEY NOT NULL, " + + getStringColumn("LASTNAME", 30) + + " DEFAULT 'Garfugengheist', " + getStringColumn("ADDRESS", 30) + ")"; + } + + protected String getCreateDASTEST2Customer() { + return "CREATE TABLE DASTEST2.CUSTOMER (" + getIntegerColumn("ID") + " PRIMARY KEY NOT NULL, " + + getStringColumn("LASTNAME", 30) + + " DEFAULT 'Garfugengheist', " + getStringColumn("ADDRESS", 30) + ")"; + } + + protected String getCreateDASTEST3Customer() { + return "CREATE TABLE DASTEST3.CUSTOMER (" + getIntegerColumn("ID") + " PRIMARY KEY NOT NULL, " + + getStringColumn("LASTNAME", 30) + + " DEFAULT 'Garfugengheist', " + getStringColumn("ADDRESS", 30) + ")"; + } + + protected String getCreateDASTEST1City() { + return "CREATE TABLE DASTEST1.CITY (" + getIntegerColumn("INDX") + " PRIMARY KEY NOT NULL," + + getStringColumn("NAME", 50) + ")"; + } + + protected String getCreateDASTEST2City() { + return "CREATE TABLE DASTEST2.CITY (" + getIntegerColumn("INDX") + " PRIMARY KEY NOT NULL," + + getStringColumn("NAME", 50) + ")"; + } + + protected String getCreateDASTEST2Account() { + return "CREATE TABLE DASTEST2.ACCOUNT ("+ getIntegerColumn("ACCOUNT_ID") + " PRIMARY KEY NOT NULL," + + getIntegerColumn("CUSTOMER_ID") +", "+ getIntegerColumn("BALANCE") + ")"; + } + + protected String getCreateDASTEST3CustOrder() { + return "CREATE TABLE DASTEST3.CUSTORDER ("+ getIntegerColumn("ORDER_ID") + " PRIMARY KEY NOT NULL," + + getIntegerColumn("CUSTOMER_ID") +", "+getIntegerColumn("ORDER_COUNT")+ ")"; + } + + protected String getCreateDASTEST1OrderDetails() { + return "CREATE TABLE DASTEST1.ORDERDETAILS (" + getIntegerColumn("ORDERID") + " NOT NULL, " + + getIntegerColumn("PRODUCTID") + + " NOT NULL, PRICE FLOAT, PRIMARY KEY (ORDERID, PRODUCTID))"; + } + + protected String getCreateDASTEST3OrderDetailsDesc() { + return "CREATE TABLE DASTEST3.ORDERDETAILSDESC ("+ getIntegerColumn("ID") + " NOT NULL, " + getIntegerColumn("ORDERID") + " NOT NULL, " + + getIntegerColumn("PRODUCTID") + + " NOT NULL,"+ getStringColumn("DESCR", 20)+", PRIMARY KEY (ID))"; + } + //JIRA-952 end + // ///////////////// + + protected String getForeignKeyConstraint(String pkTable, String pkColumn, String foreignKey) { + StringBuffer buffer = new StringBuffer(); + buffer.append("CONSTRAINT FK1 FOREIGN KEY ("); + buffer.append(foreignKey); + buffer.append(") REFERENCES "); + buffer.append(pkTable); + buffer.append("("); + buffer.append(pkColumn); + buffer.append(") ON DELETE NO ACTION ON UPDATE NO ACTION"); + return buffer.toString(); + } + + protected String getStringColumn(String name, int length) { + return name + ' ' + stringType + "(" + Integer.valueOf(length).toString() + ")"; + } + + protected String getIntegerColumn(String name) { + return name + ' ' + integerType; + } + + protected String getGeneratedKeyClause() { + return "GENERATED ALWAYS AS IDENTITY"; + } + + protected String getDecimalColumn(String name, int size1, int size2) { + return name + ' ' + decimalType + "(" + Integer.valueOf(size1).toString() + ',' + + Integer.valueOf(size2).toString() + ")"; + } + + protected String getFloatColumn(String name) { + return name + ' ' + floatType; + } + + protected String getTimestampColumn(String name) { + return name + ' ' + timestampType; + } + +} diff --git a/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DerbySetup.java b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DerbySetup.java new file mode 100644 index 0000000000..300f315465 --- /dev/null +++ b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DerbySetup.java @@ -0,0 +1,44 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.tuscany.das.rdb.test.framework; + +import java.util.Properties; + +import junit.framework.Test; + +public class DerbySetup extends DatabaseSetup { + + public DerbySetup(Test test) { + super(test); + } + + protected void initConnectionProtocol() { + + // Set the derby property to explicitly specify the database location relative + // from current directory to "target" + Properties p = System.getProperties(); + p.put("derby.system.home", "target"); + + platformName = "Derby"; + driverName = "org.apache.derby.jdbc.EmbeddedDriver"; + databaseURL = "jdbc:derby:dastest; create = true"; + + } + +} diff --git a/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/JavaStoredProcs.java b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/JavaStoredProcs.java new file mode 100644 index 0000000000..71a7d1c4e4 --- /dev/null +++ b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/JavaStoredProcs.java @@ -0,0 +1,97 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.tuscany.das.rdb.test.framework; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; + +/** + * Stored Procedures for DB2 and Derby SP tests + * + */ +public final class JavaStoredProcs { + + private JavaStoredProcs() { + + } + + public static void getAllCustomersAndAllOrders(ResultSet[] result1, ResultSet[] result2) throws SQLException { + Connection conn = DriverManager.getConnection("jdbc:default:connection"); + PreparedStatement ps = conn.prepareStatement("SELECT * FROM CUSTOMER"); + + result1[0] = ps.executeQuery(); + + PreparedStatement ps2 = conn.prepareStatement("select * from anorder"); + result2[0] = ps2.executeQuery(); + + } + + public static void getAllCompanies(ResultSet[] results) throws SQLException { + + Connection conn = DriverManager.getConnection("jdbc:default:connection"); + PreparedStatement ps = conn.prepareStatement("SELECT * FROM COMPANY"); + results[0] = ps.executeQuery(); + } + + public static void deleteCustomer(int theId) throws SQLException { + + Connection conn = DriverManager.getConnection("jdbc:default:connection"); + PreparedStatement ps = conn.prepareStatement("DELETE FROM CUSTOMER WHERE ID = ?"); + ps.setInt(1, theId); + ps.execute(); + + } + + public static void getNamedCompany(String theName, ResultSet[] results) throws SQLException { + + Connection conn = DriverManager.getConnection("jdbc:default:connection"); + PreparedStatement ps = conn.prepareStatement("SELECT * FROM COMPANY WHERE NAME = ?"); + ps.setString(1, theName); + results[0] = ps.executeQuery(); + } + + public static void getCustomerAndOrders(int theId, ResultSet[] results) throws SQLException { + + Connection conn = DriverManager.getConnection("jdbc:default:connection"); + PreparedStatement ps = conn + .prepareStatement("SELECT * FROM CUSTOMER LEFT JOIN ANORDER ON CUSTOMER.ID = ANORDER.CUSTOMER_ID " + + "WHERE CUSTOMER.ID = ?"); + ps.setInt(1, theId); + results[0] = ps.executeQuery(); + } + + public static void getNamedCustomers(String theName, int[] outCount, ResultSet[] results) throws SQLException { + + Connection conn = DriverManager.getConnection("jdbc:default:connection"); + PreparedStatement ps = conn.prepareStatement("SELECT * FROM CUSTOMER WHERE LASTNAME = ?"); + ps.setString(1, theName); + results[0] = ps.executeQuery(); + + ps = conn.prepareStatement("SELECT COUNT(*) FROM CUSTOMER WHERE LASTNAME = ?"); + ps.setString(1, theName); + + ResultSet rs = ps.executeQuery(); + rs.next(); + outCount[0] = rs.getInt(1); + } + +} diff --git a/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/MySQLSetup.java b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/MySQLSetup.java new file mode 100644 index 0000000000..580f1c5f25 --- /dev/null +++ b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/MySQLSetup.java @@ -0,0 +1,105 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.tuscany.das.rdb.test.framework; + +import java.sql.SQLException; + +import junit.framework.Test; + +public class MySQLSetup extends DatabaseSetup { + + public MySQLSetup(Test test) { + super(test); + } + + protected void initConnectionProtocol() { + + platformName = "MySQL"; + driverName = "com.mysql.jdbc.Driver"; + databaseURL = "jdbc:mysql:///dastest?user=dastester&password=dastester"; + + } + + protected void createProcedures() { + + String createGetAllCompanies = "CREATE PROCEDURE `dastest`.`GETALLCOMPANIES` () " + + " SELECT * FROM COMPANY "; + + String createDeleteCustomer = "CREATE PROCEDURE `dastest`.`DELETECUSTOMER` (theId INT) " + + " DELETE FROM CUSTOMER WHERE ID = theId "; + + String createGetNamedCustomers = "CREATE PROCEDURE `dastest`.`GETNAMEDCUSTOMERS`(IN thename VARCHAR(30), " + + "OUT theCount INTEGER ) " + " BEGIN " + + " SELECT * FROM CUSTOMER AS CUSTOMER WHERE LASTNAME = theName; " + + " SET theCount = (SELECT COUNT(*) FROM CUSTOMER WHERE LASTNAME = theName); " + " END "; + + String createGetCustomerAndOrders = " CREATE PROCEDURE `dastest`.`GETCUSTOMERANDORDERS` (theId INT) " + + " SELECT * FROM CUSTOMER LEFT JOIN ANORDER ON CUSTOMER.ID = ANORDER.CUSTOMER_ID " + + "WHERE CUSTOMER.ID = theId "; + + String createGetNamedCompany = " CREATE PROCEDURE `dastest`.`GETNAMEDCOMPANY` (theName VARCHAR(100)) " + + " SELECT * FROM COMPANY WHERE NAME = theName"; + + String createCustomersAndOrders = "CREATE PROCEDURE `dastest`.`GETALLCUSTOMERSANDORDERS` () " + + " BEGIN SELECT * FROM CUSTOMER; SELECT * FROM ANORDER; END;"; + + System.out.println("Creating procedures"); + try { + + s.execute(createGetAllCompanies); + s.execute(createDeleteCustomer); + s.execute(createGetNamedCompany); + s.execute(createGetCustomerAndOrders); + s.execute(createGetNamedCustomers); + s.execute(createCustomersAndOrders); + + } catch (SQLException e) { + throw new RuntimeException(e); + } + } + + // Overrides for table creation + protected String getCreateCompany() { + return "CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, NAME VARCHAR(30), EOTMID INT)"; + } + + protected String getCreateEmployee() { + return "CREATE TABLE EMPLOYEE (ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, NAME VARCHAR(30), " + + "SN VARCHAR(10), MANAGER SMALLINT, DEPARTMENTID INT)"; + } + +// protected String getCreateDepartment() { +// return "CREATE TABLE DEPARTMENT (ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, NAME VARCHAR(30), " +// + "LOCATION VARCHAR(30), DEPNUMBER VARCHAR(10), COMPANYID INT, EOTM INT)"; +// } + + protected String getCreateTypeTest() { + return "CREATE TABLE TYPETEST (ID INT PRIMARY KEY NOT NULL, ATIMESTAMP DATETIME, ADECIMAL DECIMAL(9,2), " + + "AFLOAT FLOAT)"; + } + + protected String getCreateServerStatus() { + return "CREATE TABLE CONMGT.SERVERSTATUS (STATUSID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, " + + "MANAGEDSERVERID INTEGER NOT NULL, TIMESTAMP TIMESTAMP NOT NULL)"; + } + protected String getGeneratedKeyClause() { + return "AUTO_INCREMENT"; + } + +} diff --git a/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/RelationshipData.java b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/RelationshipData.java new file mode 100644 index 0000000000..094fe13456 --- /dev/null +++ b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/RelationshipData.java @@ -0,0 +1,80 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.tuscany.das.rdb.test.framework; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; + +public abstract class RelationshipData { + protected Object[][] data; + + protected Connection connection; + + private int currentRow = -1; + + public RelationshipData(Connection c, Object[][] inputData) { + this.connection = c; + this.data = inputData; + } + + public int size() { + return data[0].length; + } + + public int numberOfRows() { + return data.length; + } + + public boolean next() { + ++currentRow; + return currentRow < numberOfRows(); + } + + public void refresh() throws SQLException { + updateRelationships(); + } + + protected abstract String getParentRetrievalStatement(); + + protected abstract String getChildUpdateStatement(); + + protected void updateRelationships() throws SQLException { + // { MegaCorp, Advanced Technologies } + // select company.id from company where company.name = ? + PreparedStatement retrieveParent = connection.prepareStatement(getParentRetrievalStatement()); + // update department set department.companyid = ? where department.name = ? + PreparedStatement updateChild = connection.prepareStatement(getChildUpdateStatement()); + + while (next()) { + retrieveParent.setObject(1, data[currentRow][0]); + retrieveParent.execute(); + ResultSet rs = retrieveParent.getResultSet(); + rs.next(); + Object parentID = rs.getObject(1); + retrieveParent.clearParameters(); + + updateChild.setObject(1, parentID); + updateChild.setObject(2, data[currentRow][1]); + updateChild.execute(); + updateChild.clearParameters(); + } + } +} diff --git a/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/TestData.java b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/TestData.java new file mode 100644 index 0000000000..4b91cde06b --- /dev/null +++ b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/TestData.java @@ -0,0 +1,126 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.tuscany.das.rdb.test.framework; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.SQLException; +import java.sql.Timestamp; +import java.text.DateFormat; +import java.text.ParseException; +import java.text.SimpleDateFormat; +import java.util.Date; + +public abstract class TestData { + + protected static final DateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd hh:ss:mm.SSS"); + + protected static final Timestamp TIMESTAMP = getTimestamp(); + + protected Object[][] data; + + protected Connection connection; + + private int currentRow = -1; + + + public TestData(Connection c, Object[][] customerData) { + this.connection = c; + this.data = customerData; + } + + public int size() { + return data[0].length; + } + + public int numberOfRows() { + return data.length; + } + + public boolean next() { + ++currentRow; + return currentRow < numberOfRows(); + } + + public abstract String getTableName(); + + public Object getObject(int i) { + return data[currentRow][i - 1]; + } + + public void refresh() throws SQLException { + deleteRowsFromTable(); + insertRows(); + } + + protected void deleteRowsFromTable() throws SQLException { + PreparedStatement ps = connection.prepareStatement("delete from " + getTableName()); + ps.execute(); + ps.close(); + } + + protected void insertRows() throws SQLException { + StringBuffer sql = new StringBuffer(); + sql.append("insert into "); + sql.append(getTableName()); + sql.append(" values ("); + for (int i = 1; i < size(); i++) { + sql.append("?,"); + } + sql.append("?)"); + PreparedStatement ps = connection.prepareStatement(sql.toString()); + + while (next()) { + for (int i = 1; i <= size(); i++) { + ps.setObject(i, getObject(i)); + } + ps.execute(); + ps.clearParameters(); + } + ps.close(); + } + + // Utilities + protected static Date getDate() { + + try { + return DATE_FORMAT.parse("1966-12-20 00:00:00.0"); + } catch (ParseException e) { + throw new RuntimeException(e); + } + } + + protected static Date getDate(String timeStamp) { + + try { + return DATE_FORMAT.parse(timeStamp); + } catch (ParseException e) { + throw new RuntimeException(e); + } + } + + public static Timestamp getTimestamp() { + return new Timestamp(getDate().getTime()); + } + + public static Timestamp getTimestamp(String timeStamp) { + return new Timestamp(getDate(timeStamp).getTime()); + } + +} diff --git a/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/TestDataWithExplicitColumns.java b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/TestDataWithExplicitColumns.java new file mode 100644 index 0000000000..5a329d0b74 --- /dev/null +++ b/das-java/tags/1.0-incubating-beta1/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/TestDataWithExplicitColumns.java @@ -0,0 +1,87 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.tuscany.das.rdb.test.framework; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.SQLException; + +import org.apache.log4j.Logger; + +public abstract class TestDataWithExplicitColumns extends TestData { + + private String[] columns; + + private int[] sqlTypes; + + private final Logger logger = Logger.getLogger(TestDataWithExplicitColumns.class); + + public TestDataWithExplicitColumns(Connection c, Object[][] data, String[] columns, int[] sqlTypes) { + super(c, data); + this.columns = columns; + this.sqlTypes = sqlTypes; + } + + private String getColumn(int i) { + return columns[i - 1]; + } + + private int getSqlType(int i) { + return sqlTypes[i - 1]; + } + + // Create an insert statement of the following form ... + // "INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)" + // This is necessary for tables with a generated column since the PK value is not provided + protected void insertRows() throws SQLException { + StringBuffer sql = new StringBuffer(); + sql.append("insert into "); + sql.append(getTableName()); + + sql.append(" ("); + for (int i = 1; i <= size(); i++) { + sql.append(getColumn(i)); + if (i < size()) { + sql.append(','); + } + } + sql.append(" )"); + + sql.append(" values ("); + for (int i = 1; i < size(); i++) { + sql.append("?,"); + } + sql.append("?)"); + + if (this.logger.isDebugEnabled()) { + this.logger.debug(sql.toString()); + } + + PreparedStatement ps = connection.prepareStatement(sql.toString()); + + while (next()) { + for (int i = 1; i <= size(); i++) { + ps.setObject(i, getObject(i), getSqlType(i)); + } + ps.execute(); + ps.clearParameters(); + } + } + +} |