nz.co.gregs.dbvolution.DBRecursiveQuery Maven / Gradle / Ivy
/*
* Copyright 2015 gregorygraham.
*
* 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 nz.co.gregs.dbvolution;
import nz.co.gregs.dbvolution.internal.query.RecursiveSQLDirection;
import nz.co.gregs.dbvolution.exceptions.*;
import java.sql.*;
import java.util.*;
import nz.co.gregs.dbvolution.columns.*;
import nz.co.gregs.dbvolution.datatypes.*;
import nz.co.gregs.dbvolution.internal.query.RecursiveQueryDetails;
import nz.co.gregs.dbvolution.query.*;
/**
* Provides the infrastructure required to create recursive queries. DBvolution
* uses native recursive queries where possible and emulation when necessary.
*
*
* Recursive queries repeatedly access the same table with new data to produce
* more rows.
*
*
* The two main uses of recursive queries are to retrieve tree data structures
* stored with in the database or to retrieve a path from the tree structure.
*
*
* Tree structures are stored in relational databases as foreign keys
* referencing the same table. That is to say: table T includes a foreign key to
* table T. By following the foreign key we find another row with another
* foreign key to table T and the process repeats.
*
*
* Recursive queries and structures can also be used to access and store
* Digraphs but digraphs are not yet supported.
*
*
Support DBvolution at
* Patreon
*
* @author gregorygraham
* @param the table/DBRow that will be returned from the query and is
* referenced by the foreign key.
*/
public class DBRecursiveQuery {
private final RecursiveQueryDetails queryDetails = new RecursiveQueryDetails<>();
/**
* Changes the default timeout for this query.
*
*
* DBvolution defaults to a timeout of 10000milliseconds (10 seconds) to avoid
* eternal queries.
*
*
* Use this method If you require a longer running query.
*
* @param timeoutInMilliseconds
*
Support DBvolution at
* Patreon
* @return this query.
*/
public synchronized DBRecursiveQuery setTimeoutInMilliseconds(Integer timeoutInMilliseconds) {
this.queryDetails.setTimeoutInMilliseconds(timeoutInMilliseconds);
return this;
}
/**
* Changes the default timeout for this query.
*
*
* DBvolution defaults to a timeout of 10000milliseconds (10 seconds) to avoid
* eternal queries.
*
*
* Use this method If you expect an extremely long query.
*
*
Support DBvolution at
* Patreon
*
* @return this query.
*/
public synchronized DBRecursiveQuery clearTimeout() {
this.queryDetails.setTimeoutInMilliseconds(null);
return this;
}
/**
* Create a DBRecursiveQuery based on the query and foreign key supplied.
*
*
* DBRecursiveQuery uses the query to create the first rows of the recursive
* query. This can be any query and contain any tables. However it must
* contain the table T and the foreign key must be a recursive FK to and from
* table T.
*
*
* After the priming query has been created the foreign key(FK) supplied will
* be followed repeatedly. The FK must be contained in one of the tables of
* the priming query and it must reference the same table, that is to say it
* must be a recursive foreign key.
*
*
* The FK will be repeatedly followed until the root node is reached (an
* ascending query) or the leaf nodes have been reached (a descending query).
* A root node is defined as a row with a null value in the FK. A leaf node is
* a row that has no FKs referencing it.
*
*
* While it is possible to define a root node in other ways only the above
* definition is currently supported.
*
* @param query
* @param keyToFollow
* @throws ColumnProvidedMustBeAForeignKey
* @throws ForeignKeyDoesNotReferenceATableInTheQuery
* @throws ForeignKeyIsNotRecursiveException
*/
public DBRecursiveQuery(DBQuery query, ColumnProvider keyToFollow) throws ColumnProvidedMustBeAForeignKey, ForeignKeyDoesNotReferenceATableInTheQuery, ForeignKeyIsNotRecursiveException {
this.queryDetails.setOriginalQuery(query);
this.queryDetails.setKeyToFollow(keyToFollow);
final Class classReferencedByForeignKey = keyToFollow.getColumn().getClassReferencedByForeignKey();
if (classReferencedByForeignKey == null) {
throw new ColumnProvidedMustBeAForeignKey(keyToFollow);
}
List allTables = query.getAllTables();
boolean found = false;
for (DBRow tab : allTables) {
found = found || (tab.getClass().isAssignableFrom(classReferencedByForeignKey));
}
if (!found) {
throw new ForeignKeyDoesNotReferenceATableInTheQuery(keyToFollow);
}
DBRow instanceOfRow = keyToFollow.getColumn().getInstanceOfRow();
if (!(classReferencedByForeignKey.isAssignableFrom(instanceOfRow.getClass())
|| instanceOfRow.getClass().isAssignableFrom(classReferencedByForeignKey))) {
throw new ForeignKeyIsNotRecursiveException(keyToFollow);
}
}
/**
* Queries that database and returns all descendants including priming and
* leaf nodes of this query.
*
*
* Using this DBRecursiveQuery as a basis, this method descends the tree
* structure finding all descendents of the rows returned by the priming
* query. This is used by {@link #getTrees() } to recreate the tree structure
* stored in the database as a tree of {@link TreeNode TreeNodes}.
*
*
Support DBvolution at
* Patreon
*
* @return a list of all descendants of this query.
* @throws SQLException
*/
public synchronized List getDescendants() throws SQLException {
List resultsList = new ArrayList<>();
queryDetails.setRecursiveQueryDirection(RecursiveSQLDirection.TOWARDS_LEAVES);
List descendants = this.getRowsFromRecursiveQuery(queryDetails);
for (DBQueryRow descendant : descendants) {
resultsList.add(descendant.get(getReturnType(queryDetails)));
}
return resultsList;
}
/**
* Queries that database and returns all ancestors including priming and root
* nodes of this query.
*
*
* Using this DBRecursiveQuery as a basis, this method ascends the tree
* structure finding all ancestors of the rows returned by the priming query.
* This is used by {@link #getPathsToRoot() } to recreate the paths stored in
* the database as a list of {@link TreeNode TreeNodes}.
*
*
Support DBvolution at
* Patreon
*
* @return a list of all descendants of this query.
* @throws SQLException
*/
public synchronized List getAncestors() throws SQLException {
List resultsList = new ArrayList<>();
this.queryDetails.setRecursiveQueryDirection(RecursiveSQLDirection.TOWARDS_ROOT);
List ancestors = this.getRowsFromRecursiveQuery(queryDetails);
for (DBQueryRow ancestor : ancestors) {
resultsList.add(ancestor.get(getReturnType(queryDetails)));
}
return resultsList;
}
@SuppressWarnings("unchecked")
private synchronized T getReturnType(RecursiveQueryDetails details) {
T typeToReturn = details.getTypeToReturn();
ColumnProvider keyToFollow = details.getKeyToFollow();
if (typeToReturn == null) {
final DBRow instanceOfRow = keyToFollow.getColumn().getInstanceOfRow();
Class classReferenceByForeignKey = keyToFollow.getColumn().getClassReferencedByForeignKey();
if (classReferenceByForeignKey == null) {
throw new ColumnProvidedMustBeAForeignKey(keyToFollow);
}
typeToReturn = (T) instanceOfRow;
}
return typeToReturn;
}
/**
* Creates a List of paths (actually non-branching trees) from the rows
* returned by this query to a root node of the example table by repeatedly
* following the recursive foreign key provided.
*
*
* Tree structures are stored in databases using a table with a foreign key to
* the same table (the aforementioned "recursive foreign key"). This method
* provides a simple means of the traversing the stored tree structure to find
* the path to the root node.
*
*
* Where possible DBvolution uses recursive queries to traverse the tree.
*
*
* Recursive queries are only possible on tables that are part of this query,
* and have a foreign key that directly references rows within the table
* itself.
*
*
* In DBvolution it is common to reference a subclass of the table to add
* semantic information and help complex query creation. As such sub-classed
* foreign keys are fully supported.
*
*
Support DBvolution at
* Patreon
*
* @return a list of the ancestors of the results from this query. 1 Database
* exceptions may be thrown
* @throws java.sql.SQLException java.sql.SQLException
*/
public synchronized List> getPathsToRoot() throws SQLException {
List ancestors = getAncestors();
List> paths = new ArrayList<>();
Map> parentMap = new HashMap<>();
Map>> childrenMap = new HashMap<>();
for (T currentRow : ancestors) {
TreeNode currentNode = new TreeNode<>(currentRow);
final String parentPKValue = queryDetails.getKeyToFollow().getColumn().getAppropriateQDTFromRow(currentRow).stringValue();
TreeNode parent = parentMap.get(parentPKValue);
if (parent != null) {
parent.addChild(currentNode);
} else {
List> listOfChildren = childrenMap.get(parentPKValue);
if (listOfChildren == null) {
listOfChildren = new ArrayList<>();
childrenMap.put(parentPKValue, listOfChildren);
}
listOfChildren.add(currentNode);
}
final List> primaryKeys = currentRow.getPrimaryKeys();
for (QueryableDatatype primaryKey : primaryKeys) {
String pkValue = primaryKey.stringValue();
List> children = childrenMap.get(pkValue);
if (children != null) {
for (TreeNode child : children) {
currentNode.addChild(child);
}
childrenMap.remove(pkValue);
}
parentMap.put(pkValue, currentNode);
parent = currentNode.getParent();
if (parent != null) {
paths.remove(parent);
}
if (currentNode.getChildren().isEmpty()) {
paths.add(currentNode);
}
}
}
return paths;
}
/**
* Creates a list of trees from the rows returned by this query to to the leaf
* nodes of the example table by repeatedly following the recursive foreign
* key provided.
*
*
* Tree structures are stored in databases using a table with a foreign key to
* the same table (the aforementioned "recursive foreign key"). This method
* provides a simple means of the traversing the stored tree structure to find
* the path to the leaves.
*
*
* Where possible DBvolution uses recursive queries to traverse the tree.
*
*
* Recursive queries are only possible on tables that are part of this query,
* and have a foreign key that directly references rows within the table
* itself.
*
*
* In DBvolution it is common to reference a subclass of the table to add
* semantic information and help complex query creation. As such sub-classed
* foreign keys are fully supported.
*
*
Support DBvolution at
* Patreon
*
* @return a list of trees of the descendants of the results from this query.
* 1 Database exceptions may be thrown
* @throws java.sql.SQLException java.sql.SQLException
*/
public synchronized List> getTrees() throws SQLException {
List descendants = getDescendants();
List> trees = new ArrayList<>();
Map> parentMap = new HashMap<>();
Map>> childrenMap = new HashMap<>();
for (T currentRow : descendants) {
String parentPKValue = queryDetails.getKeyToFollow().getColumn().getAppropriateQDTFromRow(currentRow).stringValue();
final List> pks = currentRow.getPrimaryKeys();
for (QueryableDatatype pk : pks) {
String pkValue = pk.stringValue();
TreeNode currentNode = new TreeNode<>(currentRow);
List> children = childrenMap.get(pkValue);
if (children != null) {
for (TreeNode child : children) {
currentNode.addChild(child);
trees.remove(child);
}
}
parentMap.put(pkValue, currentNode);
TreeNode parent = parentMap.get(parentPKValue);
if (parent != null) {
parent.addChild(currentNode);
} else {
List> listOfChildren = childrenMap.get(parentPKValue);
if (listOfChildren == null) {
listOfChildren = new ArrayList<>();
childrenMap.put(parentPKValue, listOfChildren);
}
listOfChildren.add(currentNode);
}
if (currentNode.getParent() == null) {
trees.add(currentNode);
} else {
trees.remove(currentNode);
}
}
}
return trees;
}
private synchronized List getRowsFromRecursiveQuery(RecursiveQueryDetails queryDetails) throws SQLException {
if(queryDetails.needsResults(queryDetails.getOptions())){
queryDetails.getOriginalQuery().getDatabase().executeDBQuery(queryDetails);
}
return queryDetails.getResults();
}
}