/**
* @clonedesc com.servoy.j2db.querybuilder.IQueryBuilderJoins#add(String, int, String)
* @sampleas add(String, int)
* @param dataSource data source
* @param joinType join type, one of {@link IQueryBuilderJoin#LEFT_OUTER_JOIN}, {@link
* IQueryBuilderJoin#INNER_JOIN}, {@link IQueryBuilderJoin#RIGHT_OUTER_JOIN}, {@link
* IQueryBuilderJoin#FULL_JOIN}
* @param alias the alias for joining table
*/
@JSFunction
public QBJoin add(String dataSource, int joinType, String alias) throws RepositoryException {
String name;
QBJoin join;
if (alias == null) {
name = UUID.randomUUID().toString();
join = null;
} else {
name = alias;
join = joins.get(name);
}
if (join == null) {
Table foreignTable = root.getTable(dataSource);
join =
addJoin(
new QueryJoin(
name,
parent.getQueryTable(),
new QueryTable(
foreignTable.getSQLName(),
foreignTable.getDataSource(),
foreignTable.getCatalog(),
foreignTable.getSchema(),
alias),
new AndCondition(),
joinType),
dataSource,
name);
}
return join;
}
private QBJoin getOrAddRelation(IRelation relation, String relationName, String alias) {
if (relation == null || !parent.getDataSource().equals(relation.getPrimaryDataSource())) {
if (relation == null) {
Debug.log("relation '" + relationName + "' not found");
} else {
Debug.log(
"relation '"
+ relationName
+ "' does not match parent data source: "
+ parent.getDataSource()
+ '/'
+ relation.getPrimaryDataSource());
}
return null;
}
String name = alias == null ? relationName : alias;
QBJoin join = joins.get(name);
if (join == null) {
try {
Table foreignTable = root.getTable(relation.getForeignDataSource());
if (foreignTable == null) {
Debug.log("foreign table for relation '" + relationName + "' not found");
return null;
}
join =
addJoin(
SQLGenerator.createJoin(
root.getDataProviderHandler(),
relation,
parent.getQueryTable(),
new QueryTable(
foreignTable.getSQLName(),
foreignTable.getDataSource(),
foreignTable.getCatalog(),
foreignTable.getSchema(),
alias),
root.getGlobalScopeProvider()),
relation.getForeignDataSource(),
name);
} catch (RepositoryException e) {
Debug.error("could not load relation '" + relationName + "'", e);
}
}
return join;
}
private String createInversedFKRelationName(Table table, List<String[]> rel_items_list) {
if (rel_items_list.size() == 0) {
return null;
}
String[] element = rel_items_list.get(0);
String ftableName = element[0];
String fcolumnName = element[1];
String ptableName = element[2];
String pcolumnName = element[3];
String relationName =
EditorUtil.getRelationName(ptableName, ftableName, pcolumnName, fcolumnName);
if (rel_items_list.size() > 1 || relationName == null || relationName.trim().length() == 0) {
return table.getSQLName() + "_to_" + ptableName;
}
return relationName;
}
private List getSortColumns(Table table, String sortColumnProvider) {
List sortColumns = null;
if (sortColumnProvider != null) {
sortColumnProvider = sortColumnProvider.trim();
StringTokenizer sortColumnProviderTokenizer = new StringTokenizer(sortColumnProvider, ",");
String sortColumnToken;
while (sortColumnProviderTokenizer.hasMoreTokens()) {
sortColumnToken = sortColumnProviderTokenizer.nextToken().trim();
StringTokenizer sortColumnTokenizer = new StringTokenizer(sortColumnToken, " ");
String sortColumnName = null;
int sortOrder = SortColumn.ASCENDING;
if (sortColumnTokenizer.hasMoreTokens()) sortColumnName = sortColumnTokenizer.nextToken();
if (sortColumnTokenizer.hasMoreTokens()) {
String sOrder = sortColumnTokenizer.nextToken().toLowerCase();
if (sOrder.equals("desc")) sortOrder = SortColumn.DESCENDING;
}
if (sortColumnName != null) {
IColumn sortColumn = table.getColumn(sortColumnName);
if (sortColumn != null) {
SortColumn sCol = new SortColumn(sortColumn);
sCol.setSortOrder(sortOrder);
if (sortColumns == null) sortColumns = new ArrayList();
sortColumns.add(sCol);
}
}
}
}
return sortColumns;
}
public List<RelationData> loadData(IServerInternal server, IProgressMonitor monitor) {
List<RelationData> relations = new ArrayList<RelationData>();
try {
List<String> tableNames = server.getTableAndViewNames(true, true);
// plus 1 for sorting at the end
monitor.beginTask("Loading relations", tableNames.size() + 1);
for (String tableName : tableNames) {
if (isCanceled()) {
return null;
}
Table table = server.getTable(tableName);
Connection connection = null;
ResultSet resultSet = null;
try {
connection = server.getConnection();
DatabaseMetaData dbmd = connection.getMetaData();
Map<String, List<List<String[]>>> relationInfo =
new HashMap<String, List<List<String[]>>>();
resultSet =
dbmd.getExportedKeys(
server.getConfig().getCatalog(),
server.getConfig().getSchema(),
table.getSQLName());
while (resultSet.next()) {
String pcolumnName = resultSet.getString("PKCOLUMN_NAME");
String ftableName = resultSet.getString("FKTABLE_NAME");
String fcolumnName = resultSet.getString("FKCOLUMN_NAME");
String fkname = resultSet.getString("FK_NAME");
String relname = fkname;
if (relname == null) relname = table.getSQLName() + "_to_" + ftableName;
int keySeq = resultSet.getInt("KEY_SEQ");
Debug.trace(
"Found (export) rel: name: "
+ relname
+ " keyseq = "
+ keySeq
+ ' '
+ table.getSQLName()
+ ' '
+ pcolumnName
+ " -> "
+ ftableName
+ ' '
+ fcolumnName);
List<List<String[]>> rel_items_list = relationInfo.get(relname);
if (rel_items_list == null) {
rel_items_list = new ArrayList<List<String[]>>();
relationInfo.put(relname, rel_items_list);
rel_items_list.add(new ArrayList<String[]>());
}
// rel_items_list is a list of items-lists, we are adding items to the last of this
// list
rel_items_list
.get(rel_items_list.size() - 1)
.add(
new String[] {
table.getSQLName(), pcolumnName, ftableName, fcolumnName, fkname
});
}
resultSet = Utils.closeResultSet(resultSet);
resultSet =
dbmd.getImportedKeys(
server.getConfig().getCatalog(),
server.getConfig().getSchema(),
table.getSQLName());
int lastKeySeq = Integer.MAX_VALUE;
List<List<String[]>> fk_rel_items_list = new ArrayList<List<String[]>>();
while (resultSet.next()) {
String pcolumnName = resultSet.getString("PKCOLUMN_NAME");
String ptableName = resultSet.getString("PKTABLE_NAME");
String fcolumnName = resultSet.getString("FKCOLUMN_NAME");
int keySeq = resultSet.getInt("KEY_SEQ");
Debug.trace(
"Found (import) rel: name: "
+ table.getSQLName()
+ "_to_"
+ ptableName
+ " keyseq = "
+ keySeq
+ ' '
+ table.getSQLName()
+ ' '
+ pcolumnName
+ " -> "
+ ptableName
+ ' '
+ fcolumnName);
// assume KEY_SEQ ascending ordered, do not assume 0 or 1 based (jdbc spec is not
// clear on this).
// when KEY_SEQ is not increasing, we have a separate constraint between the same
// tables.
if (fk_rel_items_list.size() == 0 || keySeq <= lastKeySeq) {
fk_rel_items_list.add(new ArrayList<String[]>());
}
lastKeySeq = keySeq;
// add the item to the last list of rel_items_list
fk_rel_items_list
.get(fk_rel_items_list.size() - 1)
.add(
new String[] {
table.getSQLName(), fcolumnName, ptableName, pcolumnName, null
});
}
// generate relation names for the inversed fk constraints
for (List<String[]> rel_items_list : fk_rel_items_list) {
String relationName = createInversedFKRelationName(table, rel_items_list);
List<List<String[]>> rel_items = relationInfo.get(relationName);
if (rel_items == null) {
relationInfo.put(relationName, rel_items = new ArrayList<List<String[]>>());
}
rel_items.add(rel_items_list);
}
resultSet = Utils.closeResultSet(resultSet);
for (Map.Entry<String, List<List<String[]>>> entry : relationInfo.entrySet()) {
String rname = entry.getKey();
List<List<String[]>> rel_items_list = entry.getValue();
// we may have multiple lists of items defined for the same relation name
for (int l = 0; l < rel_items_list.size(); l++) {
List<Column> primaryColumns = new ArrayList<Column>();
List<Column> foreignColumns = new ArrayList<Column>();
for (String[] element : rel_items_list.get(l)) {
// String ptableName = element[0];
String pcolumnName = element[1];
String ftableName = element[2];
String fcolumnName = element[3];
// String fkname = element[4];
Table foreignTable = server.getTable(ftableName);
if (foreignTable == null || foreignTable.isMarkedAsHiddenInDeveloper()) continue;
Column primaryColumn = table.getColumn(pcolumnName);
Column foreignColumn = foreignTable.getColumn(fcolumnName);
if (primaryColumn == null || foreignColumn == null) continue;
primaryColumns.add(primaryColumn);
foreignColumns.add(foreignColumn);
}
if (primaryColumns.size() != 0) {
// postfix the relation name when there are multiple
String relationName = rname;
if (rel_items_list.size() > 1) {
relationName += "_" + (l + 1);
}
boolean defaultAdd =
ServoyModelManager.getServoyModelManager()
.getServoyModel()
.getFlattenedSolution()
.getRelation(relationName)
== null;
relations.add(
new RelationData(
relationName, table, primaryColumns, foreignColumns, defaultAdd));
}
}
}
} catch (RepositoryException e) {
ServoyLog.logError(e);
} catch (SQLException e) {
ServoyLog.logError(e);
} finally {
Utils.closeResultSet(resultSet);
Utils.closeConnection(connection);
}
monitor.worked(1);
}
monitor.done();
} catch (RepositoryException ex) {
ServoyLog.logError(ex);
}
return relations;
}