/*
* 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;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.apache.tuscany.das.rdb.Command;
import org.apache.tuscany.das.rdb.DAS;
import org.apache.tuscany.das.rdb.test.data.MultiSchemaData;
import org.apache.tuscany.das.rdb.test.framework.DasTest;
import commonj.sdo.DataObject;
public class MultiSchemaTests extends DasTest{
protected void setUp() throws Exception {
super.setUp();
new MultiSchemaData(getAutoConnection()).refresh();
}
protected void tearDown() throws Exception {
super.tearDown();
}
/**If database is present connect to it and create necessary tables, procedures, data etc.
*
* Below is test database schema required.
* Schema Layout -
* Database: DASTEST
* Schema: DASTEST1
* Tables: CUSTOMER, EMPLOYEE, CITY, ORDERDETAILS
* Schema: DASTEST2
* Tables: CUSTOMER, ACCOUNT, CITY
* Schema: DASTEST3
* Tables: CUSTOMER, CUSTORDER, ORDERDETAILSDESC
*/
public void testMulitiSchemaCase1() throws Exception {
DAS das = DAS.FACTORY.createDAS(getConfig("MultiSchemaDasConfig1.xml"), getConnection());
//test case 1 - CRUD when schemaName, tableName, typeName present - DASTEST1.CUSTOMER
//System.out.println("Result:test case 1 - CRUD when schemaName, tableName, typeName present:DASTEST1.CUSTOMER");
//test select
//System.out.println("Result:test case 1 - SELECT");
assertEquals(getCustomers(das).size(), 3);
//test insert - basic
//System.out.println("Result:test case 1 - INSERT(execute())");
addCustomer(das);
assertEquals(getCustomers(das).size(), 4);
//test insert - use SDO
//System.out.println("Result:test case 1 - INSERT(applyChanges())");
addSDOCustomer(das);
assertEquals(getCustomers(das).size(), 5);
//test delete - basic
//System.out.println("Result:test case 1 - DELETE(execute())");
deleteCustomer(das);
assertEquals(getCustomers(das).size(), 4);
//test delete - SDO
//System.out.println("Result:test case 1 - DELETE(applyChanges())");
deleteSDOCustomer(das);
assertEquals(getCustomers(das).size(), 3);
//test update
//System.out.println("Result:test case 1 - UPDATE");
changeFirstCustomerName(das);
assertEquals( ((DataObject)getCustomers(das).get(0)).get("LASTNAME"), "Williams");
//System.out.println("******************************************");
}
public void testMultiSchemaCase2()throws Exception{
DAS das = DAS.FACTORY.createDAS(getConfig("MultiSchemaDasConfig2.xml"), getConnection());
//test case 1 - CRUD when schemaName, tableName present, typeName not present - DASTEST2.ACCOUNT
//System.out.println("Result:test case 2 - CRUD when schemaName, tableName present, typeName not present:DASTEST2.ACCOUNT");
//test select
//System.out.println("Result:test case 2 - SELECT");
assertEquals(getAccounts(das).size(), 4);
//test insert - basic
//System.out.println("Result:test case 2 - INSERT(execute())");
addAccount(das);
assertEquals(getAccounts(das).size(), 5);
//test insert - SDO
//System.out.println("Result:test case 2 - INSERT(applyChanges())");
addSDOAccount(das);
assertEquals(getAccounts(das).size(), 6);
//test delete - basic
//System.out.println("Result:test case 2 - DELETE(execute())");
deleteAccount(das);
assertEquals(getAccounts(das).size(), 5);
//test delete - SDO
//System.out.println("Result:test case 2 - DELETE(applyChanges())");
deleteSDOAccount(das);
assertEquals(getAccounts(das).size(), 4);
//test update
//System.out.println("Result:test case 2 - UPDATE");
changeFirstAccountBalance(das);
assertEquals( ((DataObject)getAccounts(das).get(0)).get("BALANCE"), new Integer(45000));
//System.out.println("******************************************");
}
public void testMultiSchemaCase3() throws Exception{
DAS das = DAS.FACTORY.createDAS(getConfig("MultiSchemaDasConfig3.xml"), getConnection());
//test case 3 - CRUD when
is not there in config - DASTEST1.CITY
//System.out.println("Result:test case 3 - CRUD when is not there in config:DASTEST1.CITY");
//test select
//System.out.println("Result:test case 3 - SELECT");
assertEquals(getCities(das).size(), 2);
//test insert - basic
//System.out.println("Result:test case 3 - INSERT(execute())");
addCity(das);
assertEquals(getCities(das).size(), 3);
//test insert - SDO
//System.out.println("Result:test case 3 - INSERT(applyChanges())");
assertEquals(addSDOCity(das),"Expected failure to insert");
assertEquals(getCities(das).size(), 3);
//test delete - basic
//System.out.println("Result:test case 3 - DELETE(execute())");
deleteCity(das);
assertEquals(getCities(das).size(), 2);
//test delete - SDO
//System.out.println("Result:test case 3 - DELETE(applyChanges())");
assertEquals(deleteSDOCity(das), "Expected failure to delete");
assertEquals(getCities(das).size(), 2);
//test update
//System.out.println("Result:test case 3 - UPDATE");
assertEquals(changeFirstCityName(das), "Expected failure to update");
//System.out.println("******************************************");
}
public void testMultiSchemaCase4() throws Exception{
//System.out.println("Result:test case 4 - with multi schema support ON, have table with no schemaName");
try{
//get das handle for invalid config
DAS das = DAS.FACTORY.createDAS(getConfig("MultiSchemaInvalidDasConfig.xml"), getConnection());
}catch(Exception e){
//No schemaName provided for tableName CUSTORDER when schemaNameSupport is ON
assertEquals("No schemaName provided for tableName CUSTORDER when schemaNameSupport is ON",
e.getMessage());
}
//System.out.println("******************************************");
}
public void testMultiSchemaCase5() throws Exception{
DAS das = DAS.FACTORY.createDAS(getConfig("MultiSchemaDasConfig5.xml"), getConnection());
//test case 5 - SELECT when CUSTOMER.ID is present in DASTEST1 and DASTEST2
//and DASTEST2.CUSTOMER not in config
//System.out.println("Result:test case 5 - SELECT when CUSTOMER.ID is present in DASTEST1 and DASTEST2 and"+
// " DASTEST2.CUSTOMER not in config");
DataObject root = getCustomersFrom2SchemasWithOneSchemaNotInConfig(das);
assertEquals(root.getList("DASTEST1_CUSTOMER").size(), 3);
assertEquals(root.getList("DASTEST2.CUSTOMER").size(), 3);
//System.out.println("******************************************");
}
public void testMultiSchemaCase6()throws Exception{
DAS das = DAS.FACTORY.createDAS(getConfig("MultiSchemaDasConfig6.xml"), getConnection());
//test case 6 - SELECT when CUSTOMER.ID is present in DASTEST1 and DB2ADMIN - and both entries are in Config
//System.out.println("Result:test case 6 - SELECT when CUSTOMER.ID is present in DASTEST1 and DB2ADMIN, and both in config");
//test select
//System.out.println("Result:test case 6 - SELECT");
DataObject root = getCustomersFrom2SchemasBothSchemaInConfig(das);
assertEquals(root.getList("DASTEST1_CUSTOMER").size(),3);
assertEquals(root.getList("DASTEST3_CUSTOMER").size(),3);
//System.out.println("******************************************");
}
public void testMultiSchemaCase7()throws Exception{
DAS das = DAS.FACTORY.createDAS(getConfig("MultiSchemaDasConfig7.xml"), getConnection());
//test case 7 - SELECT when CITY.INDX is present in DASTEST1 and DASTEST2 and no enrty for
//any in config
//System.out.println("Result:test case 7 - SELECT when CITY.INDX is present in DASTEST1 and DASTEST2, and no in config");
//test select
//System.out.println("Result:test case 7 - SELECT");
DataObject root = getCitiesFrom2SchemasNoneInConfig(das);
assertEquals(root.getList("DASTEST1.CITY").size(), 2);
assertEquals(root.getList("DASTEST2.CITY").size(), 2);
//System.out.println("******************************************");
}
public void testMultiSchemaCase8()throws Exception{
DAS das = DAS.FACTORY.createDAS(getConfig("MultiSchemaDasConfig8.xml"), getConnection());
//test case 8 - SELECT using ResultDescriptor - DASTEST2.ACCOUNT
//System.out.println("Result:test case 8 - SELECT using ResultDescriptor:DASTEST2.ACCOUNT");
assertEquals(getAccountRSDesc(das).size(), 4);
//System.out.println("******************************************");
}
public void testMultiSchemaCase9()throws Exception{
DAS das = DAS.FACTORY.createDAS(getConfig("MultiSchemaDasConfig9.xml"), getConnection());
//test case 9, 11 - Implied relationship , PK when both tables in same schema DASTEST2.CUSTOMER, DASTEST2.ACCOUNT
//System.out.println("Result:test case 9, 11 - Implied relationship when both tables in same schema DASTEST2.CUSTOMER, DASTEST2.ACCOUNT");
DataObject root = getSingleSchemaCustomersAccounts(das);
assertEquals(root.getList("DASTEST2.CUSTOMER").size(), 2);
assertEquals(root.getList("DASTEST2.ACCOUNT").size(), 4);
//System.out.println("******************************************");
}
private static Date kbday;
private static Date tbday;
private static DateFormat myformat = new SimpleDateFormat("yyyy.MM.dd");
static {
try {
kbday = myformat.parse("1957.09.27");
tbday = myformat.parse("1966.12.20");
} catch (ParseException e) {
throw new RuntimeException(e);
}
}
public void testMultiSchemaCase12()throws Exception{
DAS das = DAS.FACTORY.createDAS(getConfig("MultiSchemaDasConfig12.xml"), getConnection());
//test case 12 - use converter with multi-schema ON:DASTEST1.EMPLOYEE
//System.out.println("Result:test case 12 - converter on DASTEST1.EMPLOYEE.LASTNAME");
Command read = das.getCommand("testArbitraryConverter");
// Read
DataObject root = read.executeQuery();
// Verify
assertEquals(kbday,root.getDate("DASTEST1.EMPLOYEE[1]/LASTNAME"));
// Modify
root.setDate("DASTEST1.EMPLOYEE[1]/LASTNAME", tbday);
das.applyChanges(root);
// Read
root = read.executeQuery();
// Verify
assertEquals(tbday, root.getDate("DASTEST1.EMPLOYEE[1]/LASTNAME"));
//System.out.println("******************************************");
}
public void testMultiSchemaCase13()throws Exception{
DAS das = DAS.FACTORY.createDAS(getConfig("MultiSchemaDasConfig13.xml"), getConnection());
//Explicit relationship between DASTEST1.CUSTOMER and DASTEST2.ACCOUNT
//System.out.println("Result:test case13 - Explicit relationship when both tables in different schema DASTEST1.CUSTOMER, DASTEST2.ACCOUNT");
// Read some customers and related accounts
Command select = das
.createCommand("SELECT * FROM DASTEST1.CUSTOMER LEFT JOIN DASTEST2.ACCOUNT "+
" ON DASTEST1.CUSTOMER.ID = DASTEST2.ACCOUNT.CUSTOMER_ID");
DataObject root = select.executeQuery();
DataObject cust1 = root.getDataObject("DASTEST1_CUSTOMER[1]");
DataObject cust2 = root.getDataObject("DASTEST1_CUSTOMER[2]");
// Save IDs
Integer cust1ID = (Integer) cust1.get("ID");
Integer cust2ID = (Integer) cust2.get("ID");
//System.out.println("cust 1 ID :"+cust1ID);
//System.out.println("cust 2 ID :"+cust2ID);
// save account count
Integer cust1AccountCount = new Integer(cust1.getList("accounts").size());
Integer cust2AccountCount = new Integer(cust2.getList("accounts").size());
// Move an account to cust1 from cust2
DataObject account = (DataObject) cust2.getList("accounts").get(0);
cust1.getList("accounts").add(account);
// Flush changes
das.applyChanges(root);
// verify cust1 relationship updates
select = das
.createCommand("SELECT * FROM DASTEST1.CUSTOMER LEFT JOIN DASTEST2.ACCOUNT ON "
+ "DASTEST1.CUSTOMER.ID = DASTEST2.ACCOUNT.CUSTOMER_ID where DASTEST1.CUSTOMER.ID = ?");
select.setParameter(1, cust1ID);
root = select.executeQuery();
assertEquals((cust1AccountCount.intValue() + 1), root.getList("DASTEST1_CUSTOMER[1]/accounts").size());
// verify cust2 relationship updates
select.setParameter(1, cust2ID);
root = select.executeQuery();
assertEquals((cust2AccountCount.intValue() - 1),root.getList("DASTEST1_CUSTOMER[1]/accounts").size());
//System.out.println("******************************************");
}
//steer away from implied relationships - we dont support multi schema there!!!
public void testMultiSchemaCase14()throws Exception{
DAS das = DAS.FACTORY.createDAS(getConfig("MultiSchemaDasConfig14.xml"), getConnection());
//Explicit compound key relationship between DASTEST1.ORDERDETAILS and DASTEST3.ORDERDETAILSDESC
//ORDERID <-> ORDERID, PRODUCTID<->PRODUCTID
//System.out.println("Result:test case14 - Explicit compound key relationship when both " +
// "tables in different schema DASTEST1.ORDERDETAILS, DASTEST3.ORDERDETAILSDESC");
// Read some order details and related order details descs
Command select = das
.createCommand("SELECT * FROM DASTEST1.ORDERDETAILS LEFT JOIN DASTEST3.ORDERDETAILSDESC " +
" ON DASTEST1.ORDERDETAILS.ORDERID = DASTEST3.ORDERDETAILSDESC.ORDERID " +
" AND DASTEST1.ORDERDETAILS.PRODUCTID = DASTEST3.ORDERDETAILSDESC.PRODUCTID");
DataObject root = select.executeQuery();
DataObject ordDet1 = root.getDataObject("DASTEST1.ORDERDETAILS[1]");
DataObject ordDet2 = root.getDataObject("DASTEST1.ORDERDETAILS[2]");
// Save IDs
Integer ord1ID = (Integer) ordDet1.get("ORDERID");
Integer prod1ID = (Integer) ordDet1.get("PRODUCTID");
Integer ord2ID = (Integer) ordDet2.get("ORDERID");
Integer prod2ID = (Integer) ordDet2.get("PRODUCTID");
// save order count
Integer order1DetDescCount = new Integer(ordDet1.getList("orderDetailsDesc").size());
Integer order2DetDescCount = new Integer(ordDet2.getList("orderDetailsDesc").size());
// Move an order detail desc to ord det1 from ord det2
DataObject orderDetailsDesc = (DataObject) ordDet2.getList("orderDetailsDesc").get(0);
ordDet1.getList("orderDetailsDesc").add(orderDetailsDesc);
// Flush changes
das.applyChanges(root);
// verify ord det1 relationship updates
select = das
.createCommand("SELECT * FROM DASTEST1.ORDERDETAILS LEFT JOIN DASTEST3.ORDERDETAILSDESC " +
" ON DASTEST1.ORDERDETAILS.ORDERID = DASTEST3.ORDERDETAILSDESC.ORDERID " +
" AND DASTEST1.ORDERDETAILS.PRODUCTID = DASTEST3.ORDERDETAILSDESC.PRODUCTID " +
" where DASTEST1.ORDERDETAILS.ORDERID = ? AND DASTEST1.ORDERDETAILS.PRODUCTID = ?");
select.setParameter(1, ord1ID);
select.setParameter(2, prod1ID);
root = select.executeQuery();
assertEquals((order1DetDescCount.intValue() + 1),
root.getList("DASTEST1.ORDERDETAILS[1]/orderDetailsDesc").size());
// verify ord det2 relationship updates
select.setParameter(1, ord2ID);
select.setParameter(2, prod2ID);
root = select.executeQuery();
assertEquals((order2DetDescCount.intValue() - 1),
root.getList("DASTEST1.ORDERDETAILS[1]/orderDetailsDesc").size());
}
/**
* Display result
* @param list
* @param numCols - number of columns in each row
*/
public static void printList(List list, int numCols){
if(list != null)
System.out.println("list size:"+list.size());
for(int i=0; i