package com.dbxml.db.enterprise.dbfiler;
/*
* dbXML - Native XML Database
* Copyright (c) 1999-2006 The dbXML Group, L.L.C.
*
* Permission is hereby granted, free of charge, to any person obtaining
* a copy of this software and associated documentation files (the
* "Software"), to deal in the Software without restriction, including
* without limitation the rights to use, copy, modify, merge, publish,
* distribute, sublicense, and/or sell copies of the Software, and to
* permit persons to whom the Software is furnished to do so, subject to
* the following conditions:
*
* The above copyright notice and this permission notice shall be included
* in all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
* EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
* IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
* CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
* TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
* SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
*
* $Id: DBFiler.java,v 1.6 2008/08/18 17:24:53 bradford Exp $
*/
import com.dbxml.db.core.data.*;
import java.sql.*;
import java.util.*;
import com.dbxml.db.core.ClassResolver;
import com.dbxml.db.core.Collection;
import com.dbxml.db.core.filer.Filer;
import com.dbxml.db.core.transaction.Transaction;
import com.dbxml.db.core.transaction.TransactionLog;
import com.dbxml.util.Configuration;
import com.dbxml.util.ObjectPool;
import com.dbxml.util.SimpleConfigurable;
import com.dbxml.util.SoftHashMap;
import com.dbxml.util.dbXMLException;
import com.dbxml.xml.SymbolTable;
import com.dbxml.xml.dom.DOMHelper;
import com.dbxml.xml.dtsm.CompressedTable;
import com.dbxml.xml.dtsm.DTSMHelper;
import com.dbxml.xml.dtsm.DocumentTable;
import java.io.File;
import java.io.IOException;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.Text;
import org.xml.sax.SAXException;
/**
* DBFiler quickly maps relational data into XML documents by utilizing
* a single query with a set of outer joins. The Filer does the work of
* consolidating redunant data, thus eliminating the need for many
* SELECT statements.
*/
public final class DBFiler extends SimpleConfigurable implements Filer {
private static final String MAPFILE = "mapfile";
private static final String DRIVER = "driver";
private static final String URL = "url";
private static final String USERNAME = "username";
private static final String PASSWORD = "password";
private static final String NAMESPACE = "namespace";
private static final String SORT = "sort";
private static final String DISTINCT = "distinct";
private static final String PASSTHRU = "passthru";
private static final String TABLE = "table";
private static final String NAME = "name";
private static final String ITEM = "item";
private static final String SRC = "src";
private static final String KEY = "key";
private static final String FKEY = "fkey";
private static final String JOIN = "join";
private static final String RESULT = "result";
private static final String ROW = "row";
private static final String COLUMN = "column";
private static final String ATTRIB = "attrib";
private static final String REQUIRED = "required";
private Collection collection;
private SymbolTable st;
private TableInfo map;
private String select;
private String ns;
private boolean sorting;
private boolean distinct;
private boolean passthru;
private ObjectPool dbPool;
private Map statements = new SoftHashMap();
public DBFiler() {
super();
}
public void setConfig(Configuration config) throws dbXMLException {
super.setConfig(config);
ns = config.getAttribute(NAMESPACE, ns);
sorting = config.getBooleanAttribute(SORT, true);
distinct = config.getBooleanAttribute(DISTINCT, true);
passthru = config.getBooleanAttribute(PASSTHRU, false);
String mapFile = config.getAttribute(MAPFILE);
if ( !passthru ) {
SQLBuilder sb;
if ( mapFile != null && mapFile.length() > 0 ) {
try {
Document doc = DOMHelper.parse(new File(mapFile));
Configuration mapCfg = new Configuration(doc);
sb = new SQLBuilder(mapCfg);
}
catch ( IOException e ) {
throw new dbXMLException(e);
}
catch ( SAXException e ) {
throw new dbXMLException(e);
}
}
else
sb = new SQLBuilder(config.getChild(TABLE));
map = sb.getMap();
select = sb.getSQL();
}
try {
String driver = config.getAttribute(DRIVER);
String url = config.getAttribute(URL);
String user = config.getAttribute(USERNAME);
String password = config.getAttribute(PASSWORD);
ClassResolver.get(driver).newInstance();
dbPool = new DBFilerPool(url, user, password);
}
catch ( Exception e ) {
e.printStackTrace(System.err);
}
}
public String getName() {
return "DBFiler";
}
public void setCollection(Collection collection) {
this.collection = collection;
try {
st = collection.getSymbols();
}
catch ( Exception e ) {
}
}
private void close(Connection conn, Statement s, ResultSet rs) {
try {
if ( rs != null )
rs.close();
if ( s != null )
s.close();
if ( conn != null )
conn.close();
}
catch ( Exception e ) {
}
}
public void flush(Transaction tx) {
// NO-OP
}
public RecordMetaData getRecordMetaData(Transaction tx, Key key) {
return null;
}
private Statements getStatements(Connection conn) {
Statements s = (Statements)statements.get(conn);
if ( s == null ) {
s = new Statements();
statements.put(conn, s);
}
return s;
}
public Record readRecord(Transaction tx, Key key) {
if ( passthru )
return readRecordPassthru(key);
else
return readRecordMapped(key);
}
private Record readRecordPassthru(Key key) {
Connection conn = null;
Statement s = null;
ResultSet rs = null;
try {
String query = key.toString();
conn = getConnection();
s = conn.createStatement();
rs = s.executeQuery(query);
if ( !rs.next() )
return null;
ResultSetMetaData meta = rs.getMetaData();
String[] labels = new String[meta.getColumnCount()];
for ( int i = 0; i < labels.length; i++ )
labels[i] = meta.getColumnName(i + 1);
Document doc = DOMHelper.newDocument();
Element root = doc.createElement(RESULT);
root.setAttribute(KEY, query);
doc.appendChild(root);
if ( ns != null )
root.setAttribute("xmlns", ns);
root.appendChild(doc.createTextNode("\n"));
do {
Element row = doc.createElement(ROW);
root.appendChild(row);
row.appendChild(doc.createTextNode("\n"));
for ( int i = 0; i < labels.length; i++ ) {
Element col = doc.createElement(labels[i]);
row.appendChild(col);
col.appendChild(doc.createTextNode(rs.getString(i + 1)));
row.appendChild(doc.createTextNode("\n"));
}
root.appendChild(doc.createTextNode("\n"));
}
while ( rs.next() );
DocumentTable dt = DTSMHelper.documentToTable(doc, st);
CompressedTable ct = DTSMHelper.compressTable(dt);
return new Record(key, new Value(ct.getData()));
}
catch ( Exception e ) {
e.printStackTrace(System.err);
}
finally {
close(conn, s, rs);
}
return null;
}
private Record readRecordMapped(Key key) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = getConnection();
Statements s = getStatements(conn);
if ( s.readRecordMapped == null ) {
ps = conn.prepareStatement(select);
s.readRecordMapped = ps;
}
ps.setString(1, key.toString());
ps.execute();
rs = ps.getResultSet();
DocBuilder builder = new DocBuilder(rs);
Document doc = builder.getDocument();
if ( doc != null ) {
DocumentTable dt = DTSMHelper.documentToTable(doc, st);
CompressedTable ct = DTSMHelper.compressTable(dt);
return new Record(key, new Value(ct.getData()));
}
}
catch ( Exception e ) {
e.printStackTrace(System.err);
}
finally {
close(conn, null, rs);
}
return null;
}
public boolean writeRecord(Transaction tx, Key key, Value value) {
return false;
}
public boolean deleteRecord(Transaction tx, Key key) {
return false;
}
public long getRecordCount(Transaction tx) {
if ( passthru )
return -1;
else {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
long count = -1;
try {
conn = getConnection();
Statements s = getStatements(conn);
if ( s.getRecordCount == null ) {
String sql = "SELECT COUNT(" + map.key + ") FROM " + map.src;
ps = conn.prepareStatement(sql);
s.getRecordCount = ps;
}
ps.execute();
rs = ps.getResultSet();
if ( rs.next() )
count = rs.getLong(1);
}
catch ( Exception e ) {
e.printStackTrace(System.err);
}
finally {
close(conn, null, rs);
}
return count;
}
}
public RecordSet getRecordSet(Transaction tx) {
if ( passthru )
return new EmptyRecordSet();
else
return new DBRecordSet(tx);
}
public final boolean open() {
return true;
}
public boolean isOpened() {
return true;
}
public boolean exists() {
return true;
}
public boolean close() {
return true;
}
public boolean create() {
return true;
}
public boolean drop() {
return true;
}
public TransactionLog getTransactionLog() {
return null;
}
/**
* DBRecordSet
*/
private class DBRecordSet implements RecordSet {
private Transaction tx;
private Connection conn;
private ResultSet rs;
private PreparedStatement ps;
private boolean hasNext;
public DBRecordSet(Transaction tx) {
this.tx = tx;
try {
conn = getConnection();
Statements s = getStatements(conn);
if ( s.getRecordSet == null ) {
String sql = "SELECT " + map.key + " FROM " + map.src;
ps = conn.prepareStatement(sql);
s.getRecordSet = ps;
}
ps.execute();
rs = ps.getResultSet();
hasNext = rs.next();
if ( !hasNext )
close(conn, null, rs);
}
catch ( Exception e ) {
e.printStackTrace(System.err);
}
}
public boolean hasMoreRecords() {
return hasNext;
}
public Record getNextRecord() {
try {
Key key = new Key(rs.getString(1));
hasNext = rs.next();
if ( !hasNext )
close(conn, null, rs);
return readRecord(tx, key);
}
catch ( Exception e ) {
e.printStackTrace(System.err);
}
return null;
}
public Key getNextKey() {
try {
Key key = new Key(rs.getString(1));
hasNext = rs.next();
if ( !hasNext )
close(conn, null, rs);
return key;
}
catch ( Exception e ) {
e.printStackTrace(System.err);
}
return null;
}
public Value getNextValue() {
try {
Key key = new Key(rs.getString(1));
hasNext = rs.next();
if ( !hasNext )
close(conn, null, rs);
return readRecord(tx, key).getValue();
}
catch ( Exception e ) {
e.printStackTrace(System.err);
}
return null;
}
protected void finalize() {
close();
}
}
private Connection getConnection() {
return (Connection)dbPool.getObject();
}
// All Of The Mapping/Builder Code Follows *********************************
/**
* TableInfo
*/
private class TableInfo {
public TableInfo parent;
public String key; // Key determines uniqueness
public int keyPos; // Column index for the key
public String fkey; // fkey is a foreign key for join
public String jkey; // jkey is a local key for join
public String elem; // Table Element name to create
public String item; // Item Element name to create
public String src; // Table Name
public String prefix; // SQL Table Prefix
public List tables = Collections.synchronizedList(new ArrayList()); // of TableInfo
public List columns = Collections.synchronizedList(new ArrayList()); // of ColumnInfo
}
/**
* ColumnInfo
*/
private class ColumnInfo {
public boolean attrib; // Is this column an attribute?
public String elem; // Element/Attribute name to create
public int colPos; // Column index to retrieve
public boolean required; // Must this column be represented?
}
/**
* SQLBuilder
*/
private class SQLBuilder {
private List cols = new ArrayList();
private int tn = 1; // For numbering table prefixes
private TableInfo map = new TableInfo();
private String sql;
public SQLBuilder(Configuration cfg) {
generateMap(map, cfg);
generateSQL();
}
public TableInfo getMap() {
return map;
}
public String getSQL() {
return sql;
}
private int addColDef(List cols, TableInfo map, String colName) {
String val = map.prefix + "." + colName + " AS \"" + map.prefix + "$" + colName + "\"";
if ( !cols.contains(val) )
cols.add(val);
return cols.indexOf(val) + 1;
}
private void generateMap(TableInfo map, Configuration cfg) {
List tbls = new ArrayList();
map.elem = cfg.getAttribute(NAME, null);
map.src = cfg.getAttribute(SRC, null);
map.item = cfg.getAttribute(ITEM, null);
if ( map.src == null )
map.src = map.elem;
map.prefix = "t" + (tn++);
// Primary Key
map.key = cfg.getAttribute(KEY, null);
map.keyPos = addColDef(cols, map, map.key);
// Foreign Key
map.fkey = cfg.getAttribute(FKEY, null);
if ( map.fkey == null )
map.fkey = map.key;
// Join Key
map.jkey = cfg.getAttribute(JOIN, null);
if ( map.jkey == null )
map.jkey = map.fkey;
// Process child table, column and attrib elements
Configuration[] child = cfg.getChildren();
for ( int i = 0; i < child.length; i++ ) {
String name = child[i].getName();
if ( name.equals(TABLE) )
tbls.add(child[i]);
else if ( name.equals(COLUMN) || name.equals(ATTRIB) ) {
ColumnInfo col = new ColumnInfo();
col.attrib = name.equals(ATTRIB);
col.elem = child[i].getAttribute(NAME, null);
col.required = child[i].getBooleanAttribute(REQUIRED, true);
String tmp = child[i].getAttribute(SRC, null);
if ( tmp == null )
tmp = col.elem;
map.columns.add(col);
col.colPos = addColDef(cols, map, tmp);
}
}
// Process each child table recursively
Iterator iter = tbls.iterator();
while ( iter.hasNext() ) {
Configuration tbl = (Configuration)iter.next();
TableInfo mapTbl = new TableInfo();
mapTbl.parent = map;
map.tables.add(mapTbl);
generateMap(mapTbl, tbl);
}
}
private void generateSQL() {
StringBuffer sb = new StringBuffer();
List sort = new ArrayList();
if ( distinct )
sb.append("SELECT DISTINCT\n ");
else
sb.append("SELECT\n ");
Iterator iter = cols.iterator();
while ( iter.hasNext() ) {
sb.append((String)iter.next());
if ( iter.hasNext() )
sb.append(",\n ");
else
sb.append("\n");
}
sb.append("FROM\n");
sb.append(" " + map.src + " " + map.prefix + "\n");
// Add LEFT OUTER joins for the child tables
generateJoins(map, sb, sort);
sb.append("WHERE\n");
sb.append(" " + map.prefix + "." + map.key + "=\'?\'\n");
// Perform a nested sort on all keys
if ( sorting ) {
sb.append("ORDER BY\n");
sb.append(" ");
iter = sort.iterator();
while ( iter.hasNext() ) {
sb.append((String)iter.next());
if ( iter.hasNext() )
sb.append(", ");
}
}
sql = sb.toString();
}
private void generateJoins(TableInfo map, StringBuffer sb, List sort) {
if ( sorting ) {
String ob = map.prefix + "." + map.key;
if ( !sort.contains(ob) )
sort.add(ob);
}
Iterator iter = map.tables.iterator();
while ( iter.hasNext() ) {
TableInfo info = (TableInfo)iter.next();
sb.append(" LEFT OUTER JOIN ");
sb.append(info.src);
sb.append(" ");
sb.append(info.prefix);
sb.append(" ON ");
sb.append(map.prefix + "." + info.fkey);
sb.append("=");
sb.append(info.prefix + "." + info.jkey);
sb.append("\n");
generateJoins(info, sb, sort);
}
}
}
/**
* DocBuilder
*
* @todo Use a TableBuilder instead of a DOM
*/
private class DocBuilder {
public Map keyMap = new HashMap(); // TableInfo to String
public Map elemMap = new HashMap(); // String to Element
public Document doc;
public ResultSet rs;
public DocBuilder(ResultSet rs) {
this.rs = rs;
build();
}
private Text newLine() {
return doc.createTextNode("\n");
}
private void build() {
try {
doc = DOMHelper.newDocument();
int rows = 0;
while ( rs.next() ) {
keyMap.clear();
mapRow(doc, map);
rows++;
}
Element root = doc.getDocumentElement();
if ( ns != null )
root.setAttribute("xmlns", ns);
if ( rows > 0 )
return;
}
catch ( Exception e ) {
//e.printStackTrace(System.err);
}
doc = null;
}
private void mapRow(Node owner, TableInfo map) throws SQLException {
// Check to see if the join generated anything
String idVal = rs.getString(map.keyPos);
if ( idVal == null )
return;
// Check for keys to do a redundancy check
String pkey;
String key;
if ( map.parent != null ) {
pkey = (String)keyMap.get(map.parent);
key = pkey + ";" + map.prefix + "=" + idVal;
}
else {
pkey = null;
key = map.prefix + "=" + idVal;
}
keyMap.put(map, key);
int size;
// If there's an elem, we need to resolve a holder
if ( map.elem != null ) {
String hkey;
if ( pkey != null )
hkey = pkey + "^" + map.elem;
else
hkey = "^" + map.elem;
Element holder = (Element)elemMap.get(hkey);
if ( holder == null ) {
holder = doc.createElement(map.elem);
holder.appendChild(newLine());
owner.appendChild(holder);
owner.appendChild(newLine());
elemMap.put(hkey, holder);
}
owner = holder;
}
// Check to see if we need to create the element
Element elem = (Element)elemMap.get(key);
if ( elem == null ) {
elem = doc.createElement(map.item);
owner.appendChild(elem);
owner.appendChild(newLine());
elemMap.put(key, elem);
elem.appendChild(newLine());
// Add columns and attributes to the element
size = map.columns.size();
for ( int i = 0; i < size; i++ ) {
ColumnInfo c = (ColumnInfo)map.columns.get(i);
String val = rs.getString(c.colPos);
// If null and not required just ignore
if ( val == null && !c.required )
continue;
if ( !c.attrib ) {
Element col = doc.createElement(c.elem);
if ( val != null )
col.appendChild(doc.createTextNode(val));
elem.appendChild(col);
elem.appendChild(newLine());
}
else {
if ( val != null )
elem.setAttribute(c.elem, val);
else
elem.setAttribute(c.elem, "");
}
}
}
// Process the set of child tables
for ( int i = 0; i < map.tables.size(); i++ )
mapRow(elem, (TableInfo)map.tables.get(i));
}
public Document getDocument() {
return doc;
}
}
/**
* Statements
*/
private class Statements {
public PreparedStatement readRecordMapped;
public PreparedStatement getRecordCount;
public PreparedStatement getRecordSet;
}
}