com.novartis.opensource.yada.format.Joiner Maven / Gradle / Ivy
/**
* Copyright 2016 Novartis Institutes for BioMedical Research Inc.
* Licensed 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 com.novartis.opensource.yada.format;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.log4j.Logger;
import org.json.JSONArray;
import org.json.JSONObject;
import com.novartis.opensource.yada.YADAQueryResult;
import com.novartis.opensource.yada.YADARequest;
/**
* Executes on-the-fly joins of result sets from multiple queries. For the moment, only supports delimited and shallow JSON results. (Isn't that enough?!)
* @author Dave Varon
* @since 0.6.2.0
*
*/
public class Joiner {
/**
* Local logger handle
*/
private static Logger l = Logger.getLogger(Joiner.class);
/**
* Flag indicating whether to perform an outer join.
*/
private boolean outer = false;
/**
* The array of {@link YADAQueryResult} objects currently processed by the {@link Response}
*/
private YADAQueryResult[] yqrs;
/**
* Required constructor, called in the {@link Response#compose(YADAQueryResult[])} method.
* @param yqrs the array of {@link YADAQueryResult} objects currently processed by the {@link Response}
*/
public Joiner(YADAQueryResult[] yqrs) {
this.setYadaQueryResults(yqrs);
}
/**
* The meaty bit. Uses hsqld to create in memory db tables for the combined rows of converted results in each yqr.
* Then uses the join spec to build data structures, mapping columns to tables, tables to columns, and table pairs to columns.
* Then builds a select join query from the structures, executes it, wraps and returns the results.
* @return a {@link JSONArray} containing structured results, or a {@link StringBuffer} containing delimited results
* @throws YADAResponseException if there is a problem with the in-memory database
*/
public Object join() throws YADAResponseException
{
Object result = null;
try
{
Class.forName("org.hsqldb.jdbcDriver");
}
catch (ClassNotFoundException e1)
{
//TODO exception handling
}
try(Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:mymemdb", "SA", "");)
{
StringBuffer sql = null;
StringBuffer buffer = new StringBuffer();
JSONArray rows = new JSONArray();
boolean isFormatStructured = isFormatStructured();
// create tables and insert data
for(YADAQueryResult yqr : getYadaQueryResults())
{
// create tables
sql = new StringBuffer();
sql.append("CREATE TABLE");
sql.append(" T"+yqr.hashCode());
sql.append(" (");
for(int col=0;col results = (List)yqr.getConvertedResult(i);
for(String res : results)
{
JSONObject row = new JSONObject(res);
for(int k=1;k<=yqr.getConvertedHeader().size();k++)
{
String key = yqr.getConvertedHeader().get(k-1);
insert.setString(k, row.getString(key));
}
insert.addBatch();
}
}
else // delimited
{
@SuppressWarnings("unchecked")
List> results = (List>) yqr.getConvertedResult(i);
for(int j=0;j> localHeaders = new ArrayList<>();
for(int i=0;i specSet = null;
if(!specStr.equals(""))
{
if(specStr.equals("true"))
{
specSet = new HashSet<>();
for(int i=0;i(Arrays.asList(specStr.split(",")));
}
l.debug("specStr = "+specStr);
l.debug("specSet = "+specSet.toString());
}
// hash the column indexes by request
Map> S_t2c = new LinkedHashMap<>(); // the cols mapped to tables
Map> S_c2t = new HashMap<>(); // the tables mapped to the columns
for(int i=0;i dupeCheck = new HashSet<>();
List iHdr = localHeaders.get(i);
List jHdr = localHeaders.get(i+1);
for(String hdr : iHdr)
{
String _hdr = hdr.replaceAll("\"","");
dupeCheck.add(_hdr);
}
for(String hdr : jHdr)
{
String _hdr = hdr.replaceAll("\"","");
if(dupeCheck.contains(_hdr) && (specSet == null || specSet.contains(_hdr)))
{
// table to columns
if(!S_t2c.containsKey(table))
{
S_t2c.put(table, new HashSet());
}
S_t2c.get(table).add(_hdr);
// column to tables
if(!S_c2t.containsKey(_hdr))
{
S_c2t.put(_hdr, new HashSet());
}
S_c2t.get(_hdr).add(table);
// nextTable to columns
if(!S_t2c.containsKey(nextTable))
{
S_t2c.put(nextTable, new HashSet());
}
S_t2c.get(nextTable).add(_hdr);
// column to tables
S_c2t.get(_hdr).add(nextTable);
}
}
}
// hash the table combo to the col
HashMap,List> S_tt2c = new HashMap<>();
for(String col : S_c2t.keySet())
{
List tables = new ArrayList<>(S_c2t.get(col));
if(tables.size() == 2)
{
if(S_tt2c.get(tables) == null)
S_tt2c.put(tables, new ArrayList<>(Arrays.asList(col)));
else
S_tt2c.get(tables).add(col);
}
else
{
for(int i=0;i biTabs = new ArrayList<>();
biTabs.add(tables.get(i));
biTabs.add(tables.get(++i));
if(S_tt2c.get(biTabs) == null)
S_tt2c.put(biTabs, new ArrayList<>(Arrays.asList(col)));
else
S_tt2c.get(biTabs).add(col);
}
}
}
/*
* i=0, table = t1,
* i=1, table = t2,
* joinTable = (
*/
// build join
sql = new StringBuffer();
sql.append("SELECT");
String delim=" ";
StringBuilder gh = new StringBuilder();
Set globalHeader = getGlobalHeader();
for(String hdr : globalHeader)
{
//TODO consider using COALESCE in here to return empty strings instead of 'null' on LEFT JOINs, maybe make that a parameter as well
gh.append(delim+hdr.replaceAll("\"",""));
delim=", ";
}
sql.append(gh);
sql.append(" FROM");
String[] tables = S_t2c.keySet().toArray(new String[S_t2c.size()]);
// Arrays.sort(tables);
for(int i=0;i joinTables = Arrays.asList(tables[i-1],tables[i]);
List columns = S_tt2c.get(joinTables);
if(columns == null)
{
joinTables = Arrays.asList(tables[i],tables[i-1]);
columns = S_tt2c.get(joinTables);
}
if(isOuter())
sql.append(" LEFT");
sql.append(" JOIN "+table+" ON");
for(int j=0;j0)
sql.append(" AND");
sql.append(" "+joinTables.get(0)+"."+col+" = "+joinTables.get(1)+"."+col);
}
}
}
sql.append(" GROUP BY");
sql.append(gh);
l.debug(sql.toString());
ResultSet rs = null;
String colsep = getYADAQueryParamValue(YADARequest.PS_DELIMITER);
String recsep = getYADAQueryParamValue(YADARequest.PS_ROW_DELIMITER);
try(PreparedStatement select = c.prepareStatement(sql.toString());)
{
rs = select.executeQuery();
if(isFormatStructured)
{
while(rs.next())
{
JSONObject j = new JSONObject();
for(String key : globalHeader)
{
j.put(key, rs.getString(key));
}
rows.put(j);
}
result = rows;
}
else
{
while(rs.next())
{
delim="";
for(int j=0;j getGlobalHeader()
{
LinkedHashSet globalHeader = new LinkedHashSet<>();
for(YADAQueryResult yqr : getYadaQueryResults())
{
// iterate over results and stitch together in StringBuffer
for(String hdr : yqr.getConvertedHeader())
{
globalHeader.add(hdr);
}
}
return globalHeader;
}
/**
* Interrogates the 0-index {@link YADAQueryResult} stored in {@link #getYadaQueryResults()}
* @return {@code true} for JSON and XML, otherwise {@code false}
*/
private boolean isFormatStructured() {
return getYadaQueryResults()[0].isFormatStructured();
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy