use of com.servoy.j2db.query.QueryTable in project servoy-client by Servoy.
the class Messages method loadMessagesFromDatabaseRepositoryInternal.
/**
* CURRENTLY FOR INTERNAL USE ONLY, DO NOT CALL.
*
* @exclude
*/
public static void loadMessagesFromDatabaseRepositoryInternal(String i18nDatasource, String clientId, Properties settings, IDataServer dataServer, IRepository repository, Properties properties, Properties localeProperties, Locale language, int loadingType, String searchKey, String searchText, String columnNameFilter, String[] columnValueFilter, IFoundSetManagerInternal fm) {
noConnection = false;
String[] names = getServerTableNames(i18nDatasource, settings);
String serverName = names[0];
String tableName = names[1];
if (serverName == null || tableName == null || "".equals(serverName) || "".equals(tableName)) {
noConnection = true;
return;
}
try {
// $NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
Debug.trace("Loading messages from DB: Server: " + serverName + " Table: " + tableName + " Language: " + language);
IServer server = repository.getServer(serverName);
if (server == null) {
noConnection = true;
return;
}
Table table = (Table) server.getTable(tableName);
if (table == null) {
noConnection = true;
return;
}
Column filterColumn = null;
if (columnNameFilter != null) {
// check if column exists
filterColumn = table.getColumn(columnNameFilter);
if (filterColumn == null) {
return;
}
}
String[] iColumnValueFilter = columnValueFilter;
boolean isColumnValueFilterChanged = false;
if (filterColumn == null && fm != null) {
QueryTable messagesTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
QuerySelect sql = new QuerySelect(messagesTable);
ArrayList<TableFilter> tableFilters = fm.getTableFilterParams(serverName, sql);
if (tableFilters != null) {
for (TableFilter tableFilter : tableFilters) {
TableFilterdefinition tableFilterdefinition = tableFilter.getTableFilterdefinition();
if (tableFilterdefinition instanceof DataproviderTableFilterdefinition) {
DataproviderTableFilterdefinition dpTtableFilterdefinition = (DataproviderTableFilterdefinition) tableFilterdefinition;
Object value = dpTtableFilterdefinition.getValue();
if (value instanceof Object[]) {
filterColumn = table.getColumn(dpTtableFilterdefinition.getDataprovider());
iColumnValueFilter = new String[((Object[]) value).length];
for (int i = 0; i < ((Object[]) value).length; i++) {
iColumnValueFilter[i] = ((Object[]) value)[i] != null ? ((Object[]) value)[i].toString() : null;
}
isColumnValueFilterChanged = true;
break;
}
}
}
}
}
if (!isColumnValueFilterChanged) {
loadMessagesFromDatabaseRepositorySinglefilter(server, table, clientId, dataServer, properties, localeProperties, language, loadingType, searchKey, searchText, filterColumn, null, fm);
}
if (iColumnValueFilter != null) {
for (int i = iColumnValueFilter.length - 1; i >= 0; i--) {
loadMessagesFromDatabaseRepositorySinglefilter(server, table, clientId, dataServer, properties, localeProperties, language, loadingType, searchKey, searchText, filterColumn, iColumnValueFilter[i], fm);
}
}
} catch (Exception e) {
// $NON-NLS-1$
Debug.error("Couldn't get the default messages");
Debug.error(e);
invalidConnection = true;
}
}
use of com.servoy.j2db.query.QueryTable in project servoy-client by Servoy.
the class QBJoins method getOrAddRelation.
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 = getJoin(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), true, root.getGlobalScopeProvider()), relation.getForeignDataSource(), name);
} catch (RepositoryException e) {
Debug.error("could not load relation '" + relationName + "'", e);
}
}
return join;
}
use of com.servoy.j2db.query.QueryTable in project servoy-client by Servoy.
the class QBJoins method add.
/**
* @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) {
String name;
QBJoin join;
if (alias == null) {
name = UUID.randomUUID().toString();
join = null;
} else {
name = alias;
join = getJoin(name);
}
if (join == null) {
Table foreignTable = root.getTable(dataSource);
join = addJoin(new QueryJoin(name, parent.getQueryTable(), new TableExpression(new QueryTable(foreignTable.getSQLName(), foreignTable.getDataSource(), foreignTable.getCatalog(), foreignTable.getSchema(), alias)), new AndCondition(), joinType, true), dataSource, name);
}
return join;
}
use of com.servoy.j2db.query.QueryTable in project servoy-client by Servoy.
the class QueryBuilderSerializer method getXstream.
/**
* @return
*/
private XStream getXstream() {
if (xStream == null) {
xStream = new XStream(new DomDriver());
xStream.registerConverter(new ReplacedObjectConverter(xStream.getMapper(), AbstractBaseQuery.QUERY_SERIALIZE_DOMAIN));
for (Class<? extends IWriteReplace> cls : ReplacedObject.getDomainClasses(AbstractBaseQuery.QUERY_SERIALIZE_DOMAIN)) {
String className = cls.getSimpleName();
String registeredName;
if (QueryTable.class.getSimpleName().equals(className)) {
// QueryTable version 2, includes dataSource
registeredName = className + "2";
} else if (QueryTable1.class.getSimpleName().equals(className)) {
// legacy QueryTable version 1, without dataSource
registeredName = QueryTable.class.getSimpleName();
} else {
registeredName = className;
}
xStream.alias(registeredName, cls);
}
}
return xStream;
}
use of com.servoy.j2db.query.QueryTable in project servoy-client by Servoy.
the class FoundSet method loadByQuery.
public boolean loadByQuery(String query, Object[] args) throws ServoyException {
if (query == null || sheet.getTable() == null)
return false;
int from_index = -1;
int order_by_index;
// check requirements
if (!SQLGenerator.isSelectQuery(query)) {
throw new IllegalArgumentException(SQLGenerator.SQL_QUERY_VALIDATION_MESSAGE + ':' + query);
}
String sql_lowercase = Utils.toEnglishLocaleLowerCase(query);
// $NON-NLS-1$
order_by_index = sql_lowercase.lastIndexOf("order by");
boolean analyse_query_parts = (order_by_index != -1);
if (analyse_query_parts) {
// subquery. NOTE: this means that the ordering defined in the order-by part is lost.
if (// $NON-NLS-1$
((from_index = sql_lowercase.indexOf("from")) == -1) || (sql_lowercase.indexOf(Utils.toEnglishLocaleLowerCase(sheet.getTable().getSQLName())) == -1) || // $NON-NLS-1$
(sql_lowercase.indexOf("group by") != -1) || // $NON-NLS-1$
(sql_lowercase.indexOf("having") != -1) || // $NON-NLS-1$
(sql_lowercase.indexOf("union") != -1) || // $NON-NLS-1$
(sql_lowercase.indexOf("join") != -1) || // $NON-NLS-1$
(sql_lowercase.indexOf(".") == -1)) {
analyse_query_parts = false;
}
}
if (initialized && (getFoundSetManager().getEditRecordList().stopIfEditing(this) != ISaveConstants.STOPPED)) {
fsm.getApplication().reportJSError(// $NON-NLS-1$
"couldn't load dataset because foundset had edited records but couldn't save it: " + this + ", edited record(s): " + Utils.stringJoin(getFoundSetManager().getEditRecordList().getEditedRecords(this), '.') + ", failed record(s): " + Utils.stringJoin(getFoundSetManager().getEditRecordList().getFailedRecords(this), '.'), null);
return false;
}
QuerySelect originalQuery = pksAndRecords.getQuerySelectForReading();
QuerySelect sqlSelect = AbstractBaseQuery.deepClone(creationSqlSelect);
sqlSelect.clearCondition(SQLGenerator.CONDITION_RELATION);
sqlSelect.clearCondition(SQLGenerator.CONDITION_OMIT);
if (rowManager != null)
rowManager.clearAndCheckCache();
initialized = true;
Object[] whereArgs = null;
if (args != null) {
whereArgs = new Object[args.length];
for (int i = 0; i < args.length; i++) {
Object o = args[i];
if (o != null && o.getClass().equals(Date.class)) {
o = new Timestamp(((Date) o).getTime());
}
whereArgs[i] = o;
}
}
// for instance, loadRecords(SQL) followed by extended search (S) and invertrecords executes query 'NOT(SQL OR S)'
if (!analyse_query_parts) {
// do not analyze the parts of the query, just create a set-condition that compares the pk columns with the result of the subquery
Iterator<Column> pkIt = ((Table) getTable()).getRowIdentColumns().iterator();
if (!pkIt.hasNext()) {
throw new RepositoryException(ServoyException.InternalCodes.PRIMARY_KEY_NOT_FOUND, new Object[] { getTable().getName() }).setContext(this.toString());
}
List<QueryColumn> pkQueryColumns = new ArrayList<QueryColumn>();
while (pkIt.hasNext()) {
Column c = pkIt.next();
pkQueryColumns.add(c.queryColumn(sqlSelect.getTable()));
}
// must strip of the order-by part because not all databases (Oracle, who else) like order-by in subselect
String customQuery = query;
if (order_by_index > 0) {
// query contains order-by clause, find the next a closing bracket if it exists.
// order-by has to be removed because some dbs do not allow that inside subselect.
char[] chars = query.toCharArray();
int level = 1;
int i;
for (i = order_by_index; level > 0 && i < chars.length; i++) {
switch(chars[i]) {
case ')':
level--;
break;
case '(':
level++;
break;
}
}
// in that case we can leave the ordering in place because it it not the sorting of the top-level query.
if (level == 1) {
// order-by clause was at the end
customQuery = query.substring(0, order_by_index);
}
}
sqlSelect.setCondition(SQLGenerator.CONDITION_SEARCH, new SetCondition(IBaseSQLCondition.IN_OPERATOR, pkQueryColumns.toArray(new QueryColumn[pkQueryColumns.size()]), new QueryCustomSelect(customQuery, whereArgs), true));
// set the previous sort, add all joins that are needed for this sort
List<IQuerySort> origSorts = originalQuery.getSorts();
if (origSorts != null) {
ArrayList<IQuerySort> sorts = new ArrayList<IQuerySort>();
// find which sorts we will use and which tables are needed for that
Set<BaseQueryTable> sortTables = new HashSet<BaseQueryTable>();
for (IQuerySort isort : origSorts) {
if (isort instanceof QuerySort) {
QuerySort sort = (QuerySort) isort;
IQuerySelectValue icolumn = sort.getColumn();
if (icolumn instanceof QueryColumn) {
QueryColumn column = (QueryColumn) icolumn;
sortTables.add(column.getTable());
sorts.add(sort);
}
}
// ignore custom sorts and sorts on other things than columns
}
// try to find the joins that are needed to satisfy tablesToResolve
List<BaseQueryTable> tablesToResolve = new ArrayList<BaseQueryTable>();
tablesToResolve.addAll(sortTables);
List<BaseQueryTable> resolvedTables = new ArrayList<BaseQueryTable>();
resolvedTables.add(sqlSelect.getTable());
ArrayList<ISQLJoin> requiredJoins = new ArrayList<ISQLJoin>();
boolean found = true;
while (found && tablesToResolve.size() > 0) {
BaseQueryTable table = tablesToResolve.remove(0);
if (resolvedTables.contains(table)) {
continue;
}
found = false;
ArrayList<ISQLJoin> joins = originalQuery.getJoins();
if (joins != null) {
for (ISQLJoin ijoin : joins) {
if (!found && ijoin instanceof ISQLTableJoin) {
ISQLTableJoin join = (ISQLTableJoin) ijoin;
if (table.equals(join.getForeignTable())) {
// have to add this join
tablesToResolve.add(join.getPrimaryTable());
resolvedTables.add(table);
requiredJoins.add(join);
found = true;
}
}
}
}
}
if (found) {
sqlSelect.setJoins(requiredJoins);
sqlSelect.setSorts(sorts);
} else {
// $NON-NLS-1$
Debug.log("Could not restore order by in loadRecords(): couild not find all tables for sorting in " + originalQuery);
}
}
} else {
// create a query with the different parts as custom elements
sqlSelect.clearJoins();
// not needed when you have no joins and may conflict with order by
sqlSelect.setDistinct(false);
String tables;
// $NON-NLS-1$
int where_index = sql_lowercase.indexOf("where");
if (where_index == -1) {
tables = query.substring(from_index + 4, order_by_index);
// no where-clause, remove the search condition (was set to FALSE in clear()
sqlSelect.clearCondition(SQLGenerator.CONDITION_SEARCH);
} else {
tables = query.substring(from_index + 4, where_index);
sqlSelect.setCondition(SQLGenerator.CONDITION_SEARCH, new CustomCondition(query.substring(where_index + 5, order_by_index).trim(), whereArgs));
}
// pick the foundset main table from the tables in the query (does not have to be the first one, we generate sql ourselves
// that puts the main table at the end, see QueryGenerator)
boolean foundTable = false;
String mainTable = sheet.getTable().getName();
StringBuilder otherTables = new StringBuilder();
// $NON-NLS-1$
StringTokenizer tok = new StringTokenizer(tables, ",");
// default alias to table name
String mainTableAlias = mainTable;
// $NON-NLS-1$
String whitespace = "\\s+";
while (tok.hasMoreElements()) {
String tableName = tok.nextToken().trim();
String[] lcTableName = tableName.toLowerCase().split(whitespace);
if (matchesMainTablename(lcTableName[0])) {
foundTable = true;
// either 'tabname', 'tabname aliasname' or 'tabname AS aliasname', when no alias is given, use table name as alias
mainTableAlias = tableName.split(whitespace)[lcTableName.length - 1];
} else {
if (otherTables.length() > 0) {
// $NON-NLS-1$
otherTables.append(", ");
}
otherTables.append(tableName);
}
}
// set table alias or unalias table when no alias was used
BaseQueryTable qTable = sqlSelect.getTable();
sqlSelect.relinkTable(sqlSelect.getTable(), new QueryTable(qTable.getName(), qTable.getDataSource(), qTable.getCatalogName(), qTable.getSchemaName(), mainTableAlias));
if (otherTables.length() > 0) {
// $NON-NLS-1$
if (!foundTable)
throw new IllegalArgumentException(fsm.getApplication().getI18NMessage("servoy.foundSet.query.error.firstTable"));
// $NON-NLS-1$
sqlSelect.addJoin(new QueryCustomJoin("foundset.loadbyquery", sqlSelect.getTable(), otherTables.toString()));
}
ArrayList<IQuerySort> sorts = new ArrayList<IQuerySort>();
// $NON-NLS-1$
Enumeration<Object> sortParts = new StringTokenizer(query.substring(order_by_index + 8), ",");
while (sortParts.hasMoreElements()) {
sorts.add(new QueryCustomSort(((String) sortParts.nextElement()).trim()));
}
sqlSelect.setSorts(sorts);
}
return loadByQuery(sqlSelect);
}
Aggregations