Tuscany RDB DAS Database Setup Utility

This utility provides a jar file to be used by RDB DAS sample applications (web or standalone) to do database setup. So far the samples provided canned pre-created Derby database with tables and data. There was no convenient way to refresh table data during the samples execution. This utility supports Derby database at present and uses a config file to get the setup information.

APIs

public DBInitializer() – uses default provided ConfigFile CannedSampleDBConfig.xml
public DBInitializer(String ConfigFileLocation)
public DBInitializer (InputStream ConfigFileStream)
public void initializeDatabase(boolean clean) - Create database tables and fill data. If clean=true, all tables will be dropped and recreated.
public void initializeDatabaseData(boolean clean) - create database data (with clean=true, if a table has pre-existing data, it will be deleted first)
public void refreshDatabaseData() – calls initializeDatabase(clean=true)
public boolean isDatabaseReady() - will return true, if the tables exist in the database
public boolean isDatabasePopulated() - will return true, if the tables have data

Assumptions:

<table> element lists tables in proper sequence, i.e. parent tables first and then child tables. This sequence will be used in create,insert,delete,drop to take care of referential integrity. The config file required by utility supports the following features through different attributes and elements. Sample xml file is shown at the end.

<ConnectionInfo>

This element provides connection specific information - like vendor specific database URL, user name, password etc. If the DataSource is provided by the web container, user needs to only fill dataSource attribute in this. In case of standalone J2SE samples, user needs to fill ConnectionProperties element inside ConnectionInfo. These two ways of connection specification are mutually exclusive.

<table>

This element needs to specify the table names required by the sample. Only these tables will be considered for creation, data population. This gives flexibility to the user to choose the required set of tables based on the sample's requirement. It also needs to specify in the attributes, the Create SQL command used to create this table.<table> has sequence element <row> which is used to provide the data to be populated in the table.

Set Up

To use this utility, include its jar and required database driver's jar in the classpath. Provide the config file used by this utility , similar to the example below, in the source folder of the sample.

DBConfig.xml example

<?xml version="1.0" encoding="ASCII"?>
<!-- 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. -->

<DBConfig xmlns="http:///org.apache.tuscany.das.rdb/dbconfig.xsd">

<ConnectionInfo>
<ConnectionProperties driverClass="org.apache.derby.jdbc.EmbeddedDriver" databaseURL="jdbc:derby:target/dastest; create = true" loginTimeout="600000" />
</ConnectionInfo>

<Table name="COMPANY" SQLCreate="CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL, NAME VARCHAR(30), EOTMID INTEGER)">
<row> 51, 'ACME Publishing', 0 </row>
<row> 52, 'Do-rite plumbing', 0 </row>
<row> 53, 'MegaCorp', 0 </row>
</Table>

<Table name="DEPARTMENT" SQLCreate="CREATE TABLE DEPARTMENT (ID INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY, NAME VARCHAR(30),LOCATION VARCHAR(30), DEPNUMBER VARCHAR(10),COMPANYID INT)">
<row> 'Advanced Technologies', 'NY', '123', 1 </row>
<row> 'Default Name', '', '', 51 </row>
<row> 'Default Name', '', '', 51 </row>
<row> 'Default Name', '', '', 51 </row>
<row> 'Default Name', '', '', 51 </row>
<row> 'Default Name', '', '', 51 </row>
<row> 'Default Name', '', '', 51 </row>
</Table>

<Table name="EMPLOYEE" SQLCreate="CREATE TABLE EMPLOYEE (ID INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,NAME VARCHAR(30),SN VARCHAR(10), MANAGER SMALLINT, DEPARTMENTID INT)">
<row> 'John Jones','E0001',0,12 </row>
<row> 'Mary Smith','E0002',1,null </row>
<row> 'Jane Doe','E0003',0,12 </row>
<row> 'Al Smith','E0004',1,12 </row>
</Table>

</DBConfig>