use of com.servoy.j2db.persistence.Column in project servoy-client by Servoy.
the class FoundSet method loadExternalPKList.
public boolean loadExternalPKList(IDataSet ds) throws ServoyException {
if (sheet.getTable() == null) {
// $NON-NLS-1$
fsm.getApplication().reportJSError("couldn't load dataset on a foundset that has no table", null);
return 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;
}
IDataSet pks = pksAndRecords.getPks();
Object[] selectedPK = (pks != null && getSelectedIndex() >= 0 && getSelectedIndex() < pks.getRowCount()) ? pks.getRow(getSelectedIndex()) : null;
int sizeBefore = getSize();
if (sizeBefore > 1) {
fireSelectionAdjusting();
}
IDataSet set;
if (ds != null && ds.getRowCount() > 0) {
List<Column> pkColumns = sheet.getTable().getRowIdentColumns();
if (ds.getColumnCount() < pkColumns.size()) {
// $NON-NLS-1$ //$NON-NLS-2$
throw new RuntimeException("Dataset column count (" + ds.getColumnCount() + ") does not match table pk size (" + pkColumns.size() + ')');
}
Set<String> pkhashes = new HashSet<String>(ds.getRowCount());
List<Object[]> pkRows = new ArrayList<Object[]>(ds.getRowCount());
for (int i = 0; i < ds.getRowCount(); i++) {
Object[] row = ds.getRow(i);
Object[] pkrow = new Object[pkColumns.size()];
for (int j = 0; j < pkColumns.size(); j++) {
pkrow[j] = pkColumns.get(j).getAsRightType(row[j], true, false);
}
if (// check for duplicate pks
pkhashes.add(RowManager.createPKHashKey(pkrow))) {
pkRows.add(pkrow);
}
}
set = new BufferedDataSet(null, pkRows);
} else {
set = new BufferedDataSet(null);
}
// Load with pk set: remove all conditions (except foundset filters) and set pks as search-condition
QuerySelect sqlSelect = AbstractBaseQuery.deepClone(creationSqlSelect);
// Set a dynamic pk condition, when pks are added, these are added to the condition automatically.
sqlSelect.setCondition(SQLGenerator.CONDITION_SEARCH, SQLGenerator.createDynamicPKSetConditionForFoundset(this, sqlSelect.getTable(), set));
// not possible to keep related, can limit the just supplied pkset, which would awkward
fsm.getSQLGenerator().addSorts(sqlSelect, sqlSelect.getTable(), this, sheet.getTable(), lastSortColumns, false, false);
clearOmit(sqlSelect);
int sizeAfter = set.getRowCount();
IFoundSetChanges changes = pksAndRecords.setPksAndQuery(set, sizeAfter, sqlSelect);
clearInternalState(true);
if (((fsm.config.verifyPKDatasetAgainstTableFilters() && fsm.getTableFilterParams(sheet.getServerName(), sqlSelect) != null) || sqlSelect.getCondition(SQLGenerator.CONDITION_FILTER) != null) && set.getRowCount() > 0) {
fireDifference(sizeBefore, sizeAfter, changes);
// some PKs in the set may not be valid for the current filters
refreshFromDBInternal(null, true, set.getRowCount(), true, false);
} else {
if (pksAndRecords.getPks().getRowCount() > 0)
getRecord(0);
fireDifference(sizeBefore, sizeAfter, changes);
// try to preserve selection after load pk list; if not possible select first record
if (selectedPK != null) {
if (!selectRecord(selectedPK)) {
setSelectedIndex(getSize() > 0 ? 0 : -1);
}
}
}
return true;
}
use of com.servoy.j2db.persistence.Column in project servoy-client by Servoy.
the class FoundSet method alldataproviders.
/**
* Get all dataproviders of the foundset.
*
* @sample
* var dataprovidersNames = %%prefix%%alldataproviders;
* application.output("This foundset has " + dataprovidersNames.length + " data providers.")
* for (var i=0; i<dataprovidersNames.length; i++)
* application.output(dataprovidersNames[i]);
*
* @special
*/
@JSReadonlyProperty
public String[] alldataproviders() {
List<String> al = new ArrayList<String>();
Table table = (Table) getTable();
if (table != null) {
try {
Iterator<Column> columnsIt = table.getColumnsSortedByName();
while (columnsIt.hasNext()) {
IColumn c = columnsIt.next();
al.add(c.getDataProviderID());
}
Iterator<AggregateVariable> aggIt = fsm.getApplication().getFlattenedSolution().getAggregateVariables(table, true);
while (aggIt.hasNext()) {
AggregateVariable av = aggIt.next();
al.add(av.getDataProviderID());
}
Iterator<ScriptCalculation> scriptIt = fsm.getApplication().getFlattenedSolution().getScriptCalculations(table, true);
while (scriptIt.hasNext()) {
ScriptCalculation sc = scriptIt.next();
if (al.contains(sc.getDataProviderID()))
al.remove(sc.getDataProviderID());
al.add(sc.getDataProviderID());
}
} catch (Exception ex) {
Debug.error(ex);
}
}
return al.toArray(new String[al.size()]);
}
use of com.servoy.j2db.persistence.Column 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);
}
use of com.servoy.j2db.persistence.Column in project servoy-client by Servoy.
the class SQLGenerator method makeQueryColumns.
private static ArrayList<IQuerySelectValue> makeQueryColumns(Iterator<Column> it, QueryTable queryTable, QueryInsert insert) {
ArrayList<IQuerySelectValue> queryColumns = new ArrayList<IQuerySelectValue>();
List<QueryColumn> insertColumns = new ArrayList<QueryColumn>();
while (it.hasNext()) {
Column column = it.next();
ColumnInfo ci = column.getColumnInfo();
if (ci != null && ci.isExcluded()) {
continue;
}
QueryColumn queryColumn = column.queryColumn(queryTable);
if (isBlobColumn(column)) {
String alias = column.getDataProviderID().substring(0, Math.min(Column.MAX_SQL_OBJECT_NAME_LENGTH - (IDataServer.BLOB_MARKER_COLUMN_ALIAS.length() + 1), column.getDataProviderID().length())) + '_' + IDataServer.BLOB_MARKER_COLUMN_ALIAS;
// make sure the alias is unique (2 media columns starting with the same name may clash here)
char c = 'a';
for (int i = 0; i < queryColumns.size(); i++) {
IQuerySelectValue sv = queryColumns.get(i);
if (alias.equals(sv.getAlias())) {
// alias not unique, replace first char to make it unique
alias = (c++) + alias.substring(1);
// search again
i = 0;
}
}
queryColumns.add(new QueryColumnValue(Integer.valueOf(1), alias, true));
} else {
queryColumns.add(queryColumn);
}
if (insert != null && (ci == null || !ci.isDBManaged())) {
insertColumns.add(queryColumn);
}
}
if (insert != null) {
insert.setColumnValues(insertColumns.toArray(new QueryColumn[insertColumns.size()]), new Placeholder(new TablePlaceholderKey(queryTable, PLACEHOLDER_INSERT_KEY)));
}
return queryColumns;
}
use of com.servoy.j2db.persistence.Column in project servoy-client by Servoy.
the class SQLGenerator method getPKSelectSqlSelect.
/*
* _____________________________________________________________ The methods below belong to this class
*/
// SQL pk(s) select for foundset,concatenating those strings will always deliver a executable SQL
// Note: removeUnusedJoins must be false when the resulting query is changed afterwards (like adding columns)
QuerySelect getPKSelectSqlSelect(IGlobalValueEntry provider, Table table, QuerySelect oldSQLQuery, List<IRecordInternal> findStates, boolean reduce, IDataSet omitPKs, List<SortColumn> orderByFields, boolean removeUnusedJoins) throws ServoyException {
if (table == null) {
throw new RepositoryException(ServoyException.InternalCodes.TABLE_NOT_FOUND);
}
QuerySelect retval;
if (oldSQLQuery != null) {
retval = deepClone(oldSQLQuery);
retval.setGroupBy(null);
// will be generated based on foundset sorting
if (orderByFields != null)
retval.clearSorts();
// remove all servoy conditions, except filter, search and relation
for (String conditionName : retval.getConditionNames()) {
if (conditionName.startsWith(SERVOY_CONDITION_PREFIX) && !(CONDITION_FILTER.equals(conditionName) || CONDITION_SEARCH.equals(conditionName) || CONDITION_RELATION.equals(conditionName))) {
retval.setCondition(conditionName, null);
}
}
} else {
retval = new QuerySelect(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema()));
}
// Example:-select pk1,pk2 from tablename1 where ((fieldname1 like '%abcd%') or ((fieldname2 like '%xyz%')) (retrieve max 200 rows)
ArrayList<IQuerySelectValue> pkQueryColumns = new ArrayList<IQuerySelectValue>(3);
ArrayList<Column> pkColumns = new ArrayList<Column>(3);
// getPrimaryKeys from table
Iterator<Column> pks = table.getRowIdentColumns().iterator();
// make select
if (!pks.hasNext()) {
throw new RepositoryException(ServoyException.InternalCodes.PRIMARY_KEY_NOT_FOUND, new Object[] { table.getName() });
}
while (pks.hasNext()) {
Column column = pks.next();
pkColumns.add(column);
pkQueryColumns.add(column.queryColumn(retval.getTable()));
}
retval.setColumns(pkQueryColumns);
if (omitPKs != null && omitPKs.getRowCount() != 0) {
// omit is rebuild each time
retval.setCondition(CONDITION_OMIT, createSetConditionFromPKs(IBaseSQLCondition.NOT_OPERATOR, pkQueryColumns.toArray(new QueryColumn[pkQueryColumns.size()]), pkColumns, omitPKs));
} else if (oldSQLQuery != null) {
retval.setCondition(CONDITION_OMIT, oldSQLQuery.getConditionClone(CONDITION_OMIT));
}
if (// new
findStates != null && findStates.size() != 0) {
ISQLCondition moreWhere = null;
for (IRecordInternal obj : findStates) {
if (obj instanceof FindState) {
moreWhere = OrCondition.or(moreWhere, createConditionFromFindState((FindState) obj, retval, provider, pkQueryColumns));
}
}
if (moreWhere != null) {
if (reduce) {
retval.addCondition(CONDITION_SEARCH, moreWhere);
} else {
retval.addConditionOr(CONDITION_SEARCH, moreWhere);
}
if (retval.getJoins() != null) {
// check if the search condition has an or-condition
final boolean[] hasOr = { false };
retval.getCondition(CONDITION_SEARCH).acceptVisitor(new IVisitor() {
public Object visit(Object o) {
if (o instanceof OrCondition && ((OrCondition) o).getConditions().size() > 1) {
hasOr[0] = true;
return new VisitorResult(o, false);
}
return o;
}
});
if (hasOr[0]) {
// override join type to left outer join, a related OR-search should not make the result set smaller
for (ISQLJoin join : retval.getJoins()) {
if (join instanceof QueryJoin && ((QueryJoin) join).getJoinType() == IQueryConstants.INNER_JOIN) {
((QueryJoin) join).setJoinType(IQueryConstants.LEFT_OUTER_JOIN);
}
}
}
}
}
}
// make orderby
if (orderByFields != null || retval.getSorts() == null) {
List<SortColumn> orderBy = orderByFields == null ? new ArrayList<SortColumn>(3) : orderByFields;
if (orderBy.size() == 0) {
for (Column pkColumn : pkColumns) {
orderBy.add(new SortColumn(pkColumn));
}
}
addSorts(retval, retval.getTable(), provider, table, orderBy, true, false);
}
if (removeUnusedJoins) {
// remove unneeded joins, some may have been added because of a previous sort and are no longer needed.
retval.removeUnusedJoins(false);
}
// 1 do not remove sort or groupby test, will cause invalid queries
// 1 this one causes error and can not be fixed,
// 1 if (joinswherepart.length() != 0 && !sortIsRelated && groupbyKeyword == STRING_EMPTY && table.getPrimaryKeyCount() == 1)
// 1 sql select distinct(s_contacts.contactsid) from s_contacts,s_companies where s_contacts.company_id = s_companies.company_id order by s_contacts.surname ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list
// retval may have set distinct and plainPKSelect flag based on previous sort columns, make sure to reset first
retval.setDistinct(false);
retval.setPlainPKSelect(false);
if (// if joined pks comes back multiple times
retval.getJoins() != null && retval.getColumns().size() == 1 && isDistinctAllowed(retval.getColumns(), retval.getSorts())) {
retval.setDistinct(true);
} else if (// plain pk select
retval.getJoins() == null && retval.getColumns().size() == pkColumns.size()) {
retval.setPlainPKSelect(true);
}
return retval;
}
Aggregations