/*
* (c) Copyright 2006 Hewlett-Packard Development Company, LP
* All rights reserved.
* [See end of file]
*/
package squirrelrdf;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.hp.hpl.jena.rdf.model.Model;
import com.hp.hpl.jena.rdf.model.ModelFactory;
import com.hp.hpl.jena.rdf.model.Resource;
import com.hp.hpl.jena.vocabulary.RDF;
import com.hp.hpl.jena.vocabulary.RDFS;
import com.hp.hpl.squirrelrdf.rdb.DbMap;
/**
*
* ExtractConfig
*
* @author pldms <damian.steer@hp.com>
*
* Automatically create an RDB configuration for a database.
*/
public class ExtractConfig {
public static void main(String[] args) throws SQLException,
ClassNotFoundException {
if (args.length < 5) {
System.out
.println("Usage: ExtractConfig <server> <driver> <username> <password> <namespace> [<table> +]");
System.out
.println("Or: ExtractConfig <server> <driver> <username> <password> --tables (list tables)");
System.exit(1);
}
String server = args[0];
String driver = args[1];
String user = args[2];
String pass = args[3];
String namespace = args[4];
Class.forName(driver);
Connection conn = DriverManager.getConnection(server, user, pass);
DatabaseMetaData md = conn.getMetaData();
if (namespace.equals("--tables")) {
listTables(md);
conn.close();
}
String[] tables = null;
if (args.length > 5)
{
tables = new String[args.length - 5];
for (int i = 5; i < args.length; i++) { tables[i - 5] = args[i]; }
}
Model configModel = process(server, driver, namespace, user, pass, tables, md);
configModel.write(System.out, "N3");
}
/**
* Generate a configuration from a database schema.
*
* @param server JDBC URI for database
* @param driver The JDBC driver class
* @param namespace A namespace URI for the generated properties and classes
* @param user Username
* @param pass Password
* @param tables The tables to map (all if null)
* @param md Database metadata.
* @return A configuration model
* @throws SQLException
*/
public static Model process(String server, String driver, String namespace, String user, String pass, String[] tables, DatabaseMetaData md) throws SQLException
{
Model model = ModelFactory.createDefaultModel();
model.setNsPrefix("rdfs", "http://www.w3.org/2000/01/rdf-schema#");
model.setNsPrefix("rdf", "http://www.w3.org/1999/02/22-rdf-syntax-ns#");
model.setNsPrefix("owl", "http://www.w3.org/2002/07/owl");
model.setNsPrefix("db", DbMap.NS);
model.setNsPrefix("ex", namespace);
Resource map = model.createResource(namespace + "map");
map.addProperty(RDF.type, DbMap.Map);
Resource serverR = model.createResource(server);
serverR.addProperty(RDF.type, DbMap.Database);
serverR.addProperty(DbMap.user, user);
serverR.addProperty(DbMap.pass, pass);
serverR.addProperty(DbMap.driver, driver);
ResultSet rs = null;
if (tables != null) {
for (int i = 0; i < tables.length; i++)
{
rs = md.getTables(null, null, tables[i], null);
handleTable(md, rs, map, serverR);
}
rs.close();
} else {
rs = md.getTables(null, null, null, null);
handleTable(md, rs, map, serverR);
rs.close();
}
return model;
}
/**
* List tables in this database
* @param md
* @throws SQLException
*/
private static void listTables(DatabaseMetaData md) throws SQLException {
ResultSet rs = md.getTables(null, null, null, null);
while (rs.next()) {
String tableName = rs.getString("TABLE_NAME");
System.out.println("Table: " + tableName);
}
rs.close();
}
/**
* Create config for a table
*
* @param md
* @param rs
* @param map
* @param serverR
* @throws SQLException
*/
private static void handleTable(DatabaseMetaData md, ResultSet rs, Resource map,
Resource serverR) throws SQLException {
Model model = map.getModel();
String ex = model.getNsPrefixURI("ex");
while (rs.next()) {
String tableName = rs.getString("TABLE_NAME");
Resource tableRes = model
.createResource(ex + tableName, RDFS.Class);
map.addProperty(DbMap.mapsClass, tableRes);
tableRes.addProperty(DbMap.database, serverR);
tableRes.addProperty(DbMap.table, tableName);
ResultSet cols = md.getColumns(null, null, tableName, null);
while (cols.next()) {
String colName = cols.getString("COLUMN_NAME");
String type = cols.getString("TYPE_NAME");
Resource col = model.createResource(ex + tableName + "_"
+ colName, RDF.Property);
col.addProperty(RDFS.domain, tableRes);
col.addProperty(DbMap.col, colName);
col.addProperty(DbMap.colType, type);
}
cols.close();
ResultSet forkeys = md.getExportedKeys(null, null, tableName);
while (forkeys.next()) {
String colName = forkeys.getString("PKCOLUMN_NAME");
String fColName = forkeys.getString("FKCOLUMN_NAME");
String fTableName = forkeys.getString("FKTABLE_NAME");
Resource col = model.createResource(ex + tableName + "_"
+ colName, RDF.Property);
Resource fcol = model.createResource(ex + fTableName + "_"
+ fColName, RDF.Property);
col.addProperty(DbMap.foreignKey, fcol);
}
forkeys.close();
ResultSet rowIdentifiers = md.getPrimaryKeys(null, null, tableName);
while (rowIdentifiers.next())
{
String colName = rowIdentifiers.getString("COLUMN_NAME");
Resource col = model.createResource(ex + tableName + "_"
+ colName, RDF.Property);
tableRes.addProperty(DbMap.primaryKey, col);
}
}
}
}
/*
* (c) Copyright 2006 Hewlett-Packard Development Company, LP All rights
* reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
* 1. Redistributions of source code must retain the above copyright notice,
* this list of conditions and the following disclaimer. 2. Redistributions in
* binary form must reproduce the above copyright notice, this list of
* conditions and the following disclaimer in the documentation and/or other
* materials provided with the distribution. 3. The name of the author may not
* be used to endorse or promote products derived from this software without
* specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR IMPLIED
* WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
* MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO
* EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
* PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS;
* OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
* WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
* OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
* ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/