Java tutorial
/*************************************************************** * This file is part of the [fleXive](R) framework. * * Copyright (c) 1999-2014 * UCS - unique computing solutions gmbh (http://www.ucs.at) * All rights reserved * * The [fleXive](R) project is free software; you can redistribute * it and/or modify it under the terms of the GNU Lesser General Public * License version 2.1 or higher as published by the Free Software Foundation. * * The GNU Lesser General Public License can be found at * http://www.gnu.org/licenses/lgpl.html. * A copy is found in the textfile LGPL.txt and important notices to the * license from the author are found in LICENSE.txt distributed with * these libraries. * * This library is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * For further information about UCS - unique computing solutions gmbh, * please see the company website: http://www.ucs.at * * For further information about [fleXive](R), please see the * project website: http://www.flexive.org * * * This copyright notice MUST APPEAR in all copies of the file! ***************************************************************/ package com.flexive.core.storage.genericSQL; import com.flexive.core.Database; import com.flexive.core.LifeCycleInfoImpl; import com.flexive.core.conversion.ConversionEngine; import com.flexive.core.flatstorage.FxFlatStorageManager; import com.flexive.core.storage.EnvironmentLoader; import com.flexive.core.structure.FxEnvironmentImpl; import com.flexive.core.structure.FxPreloadGroupAssignment; import com.flexive.core.structure.FxPreloadType; import com.flexive.shared.CacheAdmin; import com.flexive.shared.FxLanguage; import com.flexive.shared.FxSharedUtils; import com.flexive.shared.exceptions.FxInvalidParameterException; import com.flexive.shared.exceptions.FxLoadException; import com.flexive.shared.exceptions.FxNotFoundException; import com.flexive.shared.scripting.*; import com.flexive.shared.security.ACL; import com.flexive.shared.security.ACLCategory; import com.flexive.shared.security.Mandator; import com.flexive.shared.structure.*; import com.flexive.shared.value.FxString; import com.flexive.shared.value.FxValue; import com.flexive.shared.value.ReferencedContent; import com.flexive.shared.workflow.Route; import com.flexive.shared.workflow.Step; import com.flexive.shared.workflow.StepDefinition; import com.flexive.shared.workflow.Workflow; import com.google.common.collect.Maps; import com.thoughtworks.xstream.XStream; import org.apache.commons.lang.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import java.sql.*; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import static com.flexive.core.DatabaseConst.*; /** * generic sql environment loader implementation * * @author Markus Plesser (markus.plesser@flexive.com), UCS - unique computing solutions gmbh (http://www.ucs.at) */ public class GenericEnvironmentLoader implements EnvironmentLoader { protected static final Log LOG = LogFactory.getLog(GenericEnvironmentLoader.class); /** * {@inheritDoc} */ @Override public List<ACL> loadACLs(Connection con) throws FxLoadException { Statement stmt = null; String curSql; ArrayList<ACL> result = new ArrayList<ACL>(250); try { final Map<Long, FxString[]> labels = Database.loadFxStrings(con, TBL_ACLS, "LABEL"); // 1 2 3 4 5 6 7 curSql = "SELECT DISTINCT acl.ID, acl.NAME, acl.CAT_TYPE, acl.DESCRIPTION, acl.COLOR, acl.MANDATOR, mand.NAME, " + // 8 9 10 11 " acl.CREATED_BY, acl.CREATED_AT, acl.MODIFIED_BY, acl.MODIFIED_AT FROM " + TBL_ACLS + " acl, " + TBL_MANDATORS + " mand WHERE mand.ID=acl.MANDATOR AND acl.ID != " + ACL.NULL_ACL_ID + " order by acl.NAME"; stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(curSql); while (rs != null && rs.next()) { final long id = rs.getLong(1); result.add(new ACL(id, rs.getString(2), getTranslation(labels, id, 0, false), rs.getInt(6), rs.getString(7), rs.getString(4), rs.getString(5), ACLCategory.getById(rs.getInt(3)), LifeCycleInfoImpl.load(rs, 8, 9, 10, 11))); } } catch (SQLException exc) { throw new FxLoadException(LOG, "Failed to load all ACLs: " + exc.getMessage(), exc); } finally { Database.closeObjects(GenericEnvironmentLoader.class, null, stmt); } return result; } /** * {@inheritDoc} */ @Override public Mandator[] loadMandators(Connection con) throws FxLoadException { PreparedStatement ps = null; try { // Load all mandators within the system ps = con.prepareStatement( "SELECT ID,NAME,METADATA,IS_ACTIVE,CREATED_BY,CREATED_AT,MODIFIED_BY,MODIFIED_AT FROM " + TBL_MANDATORS + " order by upper(NAME)"); ResultSet rs = ps.executeQuery(); ArrayList<Mandator> result = new ArrayList<Mandator>(20); while (rs != null && rs.next()) { int metaDataId = rs.getInt(3); if (rs.wasNull()) { metaDataId = -1; } result.add(new Mandator(rs.getInt(1), rs.getString(2), metaDataId, rs.getBoolean(4), LifeCycleInfoImpl.load(rs, 5, 6, 7, 8))); } // return the result return result.toArray(new Mandator[result.size()]); } catch (SQLException se) { FxLoadException le = new FxLoadException(se.getMessage(), se); LOG.error(le); throw le; } finally { Database.closeObjects(GenericEnvironmentLoader.class, null, ps); } } /** * {@inheritDoc} */ @Override public List<FxDataType> loadDataTypes(Connection con) throws FxLoadException { Statement stmt = null; ArrayList<FxDataType> alRet = new ArrayList<FxDataType>(20); try { String sql = "SELECT d.TYPECODE, d.NAME, t.LANG, t.DESCRIPTION FROM " + TBL_STRUCT_DATATYPES + " d LEFT JOIN " + TBL_STRUCT_DATATYPES + ML + " t ON t.ID=d.TYPECODE ORDER BY d.TYPECODE, t.LANG ASC"; stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql); FxDataType dtCurr = null; Map<Long, String> hmDesc = new HashMap<Long, String>(5); String name = null; while (rs != null && rs.next()) { if (dtCurr != null && rs.getLong(1) != dtCurr.getId()) { dtCurr.initialize(name, new FxString(FxLanguage.DEFAULT_ID, hmDesc)); alRet.add(dtCurr); hmDesc.clear(); dtCurr = null; } if (dtCurr == null) for (FxDataType dt : FxDataType.values()) { if (dt.getId() == rs.getInt(1)) { dtCurr = dt; break; } } if (dtCurr == null) throw new FxLoadException(LOG, "ex.structure.dataType.unknownId", rs.getInt(1)); hmDesc.put(rs.getLong(3), rs.getString(4)); name = rs.getString(2); } if (dtCurr != null) { dtCurr.initialize(name, new FxString(FxLanguage.DEFAULT_ID, hmDesc)); alRet.add(dtCurr); } return alRet; } catch (SQLException e) { throw new FxLoadException(LOG, e, "ex.db.sqlError", e.getMessage()); } finally { Database.closeObjects(GenericEnvironmentLoader.class, null, stmt); } } /** * {@inheritDoc} */ @Override public List<FxGroup> loadGroups(Connection con) throws FxLoadException { Statement stmt = null; ArrayList<FxGroup> alRet = new ArrayList<FxGroup>(50); try { final Map<Long, List<FxStructureOption>> groupOptions = loadAllGroupOptions(con); final Map<Long, FxString[]> mlText = Database.loadFxStrings(con, TBL_STRUCT_GROUPS, "DESCRIPTION", "HINT"); // 1 2 3 4 5 final String sql = "SELECT ID, NAME, DEFMINMULT, DEFMAXMULT, MAYOVERRIDEMULT FROM " + TBL_STRUCT_GROUPS + " ORDER BY ID ASC"; stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql); long id; while (rs != null && rs.next()) { id = rs.getLong(1); FxString label; FxString hint; if (mlText.containsKey(id)) { label = mlText.get(id)[0]; hint = mlText.get(id)[1]; } else { label = new FxString(""); hint = new FxString(""); } alRet.add(new FxGroup(id, rs.getString(2), label, hint, rs.getBoolean(5), FxMultiplicity.of(rs.getInt(3), rs.getInt(4)), FxSharedUtils.get(groupOptions, id, new ArrayList<FxStructureOption>(0)))); } return alRet; } catch (SQLException e) { throw new FxLoadException(LOG, e, "ex.db.sqlError", e.getMessage()); } finally { Database.closeObjects(GenericEnvironmentLoader.class, null, stmt); } } /** * {@inheritDoc} */ @Override public List<FxProperty> loadProperties(Connection con, FxEnvironment environment) throws FxLoadException, FxNotFoundException { Statement stmt = null; ArrayList<FxProperty> alRet = new ArrayList<FxProperty>(50); try { final Map<Long, List<FxStructureOption>> propertyOptions = loadAllPropertyOptions(con); final Map<Long, FxString[]> mlText = Database.loadFxStrings(con, TBL_STRUCT_PROPERTIES, "DESCRIPTION", "HINT"); // 1 2 3 4 5 String sql = "SELECT ID, NAME, DEFMINMULT, DEFMAXMULT, MAYOVERRIDEMULT," + //6 7 8 9 "ACL, MAYOVERRIDEACL, DATATYPE, REFTYPE, " + //10 11 12 "ISFULLTEXTINDEXED, DEFAULT_VALUE, SYSINTERNAL, " + //13 14 "REFLIST, UNIQUEMODE FROM " + TBL_STRUCT_PROPERTIES + " ORDER BY ID ASC"; stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql); FxValue defaultValue; String name; long id; int minMult; int maxMult; boolean mayOverrideMult; boolean mayOverrideACL; ACL acl; FxDataType dataType; boolean fulltextIndexed; long refTypeId; long refListId; boolean systemInternal; UniqueMode uniqueMode; final XStream xStream = ConversionEngine.getXStream(); while (rs != null && rs.next()) { id = rs.getLong(1); name = rs.getString(2); minMult = rs.getInt(3); maxMult = rs.getInt(4); mayOverrideMult = rs.getBoolean(5); acl = environment.getACL(rs.getInt(6)); mayOverrideACL = rs.getBoolean(7); dataType = environment.getDataType(rs.getLong(8)); refTypeId = rs.getLong(9); if (rs.wasNull()) refTypeId = -1; fulltextIndexed = rs.getBoolean(10); String _def = rs.getString(11); defaultValue = null; if (!StringUtils.isEmpty(_def) && CacheAdmin.isEnvironmentLoaded()) { try { defaultValue = (FxValue) xStream.fromXML(_def); if (defaultValue != null && defaultValue.isEmpty()) defaultValue = null; if (defaultValue != null) { defaultValue.setXPath(name); } } catch (Exception e) { defaultValue = null; LOG.warn("Failed to decode default value for property [" + name + "]: " + e.getMessage(), e); } } systemInternal = rs.getBoolean(12); refListId = rs.getLong(13); if (rs.wasNull()) refListId = -1; uniqueMode = UniqueMode.getById(rs.getInt(14)); FxString label; FxString hint; if (mlText.containsKey(id)) { label = mlText.get(id)[0]; hint = mlText.get(id)[1]; } else { label = new FxString(""); hint = new FxString(""); } alRet.add(new FxProperty(id, name, label, hint, systemInternal, mayOverrideMult, FxMultiplicity.of(minMult, maxMult), mayOverrideACL, acl, dataType, defaultValue, fulltextIndexed, (refTypeId == -1 ? null : environment.getType(refTypeId)), (refListId == -1 ? null : environment.getSelectList(refListId)), uniqueMode, FxSharedUtils.get(propertyOptions, id, new ArrayList<FxStructureOption>(0)))); } return alRet; } catch (SQLException e) { throw new FxLoadException(LOG, e, "ex.db.sqlError", e.getMessage()); } finally { Database.closeObjects(GenericEnvironmentLoader.class, null, stmt); } } /** * Load all options for group assignments * * @param con an open and valid connection * @return options * @throws SQLException on errors */ private Map<Long, List<FxStructureOption>> loadAllGroupAssignmentOptions(Connection con) throws SQLException { return loadAllOptions(con, "ASSID", "ASSID IS NOT NULL", TBL_STRUCT_GROUP_OPTIONS); } /** * Load all options for groups * * @param con an open and valid connection * @return options * @throws SQLException on errors */ private Map<Long, List<FxStructureOption>> loadAllGroupOptions(Connection con) throws SQLException { return loadAllOptions(con, "ID", "ASSID IS NULL", TBL_STRUCT_GROUP_OPTIONS); } /** * Load all options for property assignments * * @param con an open and valid connection * @return options * @throws SQLException on errors */ private Map<Long, List<FxStructureOption>> loadAllPropertyAssignmentOptions(Connection con) throws SQLException { return loadAllOptions(con, "ASSID", "ASSID IS NOT NULL", TBL_STRUCT_PROPERTY_OPTIONS); } /** * Load all options for properties * * @param con an open and valid connection * @return options * @throws SQLException on errors */ private Map<Long, List<FxStructureOption>> loadAllPropertyOptions(Connection con) throws SQLException { return loadAllOptions(con, "ID", "ASSID IS NULL", TBL_STRUCT_PROPERTY_OPTIONS); } /** * Load all options for types * * @param con an open and valid connection * @return options * @throws SQLException on errors */ private Map<Long, List<FxStructureOption>> loadAllTypeOptions(Connection con) throws SQLException { return loadAllTypeOptions(con, "ID", TBL_STRUCT_TYPES_OPTIONS); } private Map<Long, List<FxStructureOption>> loadAllTypeOptions(Connection con, String idColumn, String table) throws SQLException { Statement stmt = null; Map<Long, List<FxStructureOption>> result = new HashMap<Long, List<FxStructureOption>>(50); try { stmt = con.createStatement(); final ResultSet rs = stmt .executeQuery("SELECT " + idColumn + ",OPTKEY,MAYOVERRIDE,ISINHERITED,OPTVALUE FROM " + table); while (rs.next()) { final long id = rs.getLong(1); if (!result.containsKey(id)) { result.put(id, new ArrayList<FxStructureOption>()); } FxStructureOption.setOption(result.get(id), rs.getString(2), rs.getBoolean(3), rs.getBoolean(4), rs.getString(5)); } return result; } finally { Database.closeObjects(GenericEnvironmentLoader.class, null, stmt); } } private Map<Long, List<FxStructureOption>> loadAllOptions(Connection con, String idColumn, String whereClause, String table) throws SQLException { Statement stmt = null; Map<Long, List<FxStructureOption>> result = new HashMap<Long, List<FxStructureOption>>(500); try { stmt = con.createStatement(); final ResultSet rs = stmt.executeQuery("SELECT " + idColumn + ",OPTKEY,MAYOVERRIDE,ISINHERITED,OPTVALUE FROM " + table + " WHERE " + whereClause); final Map<String, String> cachedKeys = Maps.newHashMap(); while (rs.next()) { final long id = rs.getLong(1); if (!result.containsKey(id)) { result.put(id, new ArrayList<FxStructureOption>()); } final String dbValue = cachedString(cachedKeys, rs.getString(5)); final String value; if (FxStructureOption.VALUE_FALSE.equals(dbValue)) { // check string constants to avoid creating "0" and "1" strings all over the place value = FxStructureOption.VALUE_FALSE; } else if (FxStructureOption.VALUE_TRUE.equals(dbValue)) { value = FxStructureOption.VALUE_TRUE; } else { value = dbValue; } FxStructureOption.setOption(result.get(id), cachedString(cachedKeys, rs.getString(2)), rs.getBoolean(3), rs.getBoolean(4), value); } return result; } finally { Database.closeObjects(GenericEnvironmentLoader.class, null, stmt); } } /** * {@inheritDoc} */ @Override public List<FxType> loadTypes(Connection con, FxEnvironment environment) throws FxLoadException { Statement stmt = null; PreparedStatement ps = null; String curSql; ArrayList<FxType> result = new ArrayList<FxType>(20); try { final Map<Long, List<FxStructureOption>> typeOptions = loadAllTypeOptions(con); // 1 2 3 4 ps = con.prepareStatement("SELECT TYPESRC, TYPEDST, MAXSRC, MAXDST FROM " + TBL_STRUCT_TYPERELATIONS + " WHERE TYPEDEF=?"); // 1 2 3 4 5 6 curSql = "SELECT ID, NAME, PARENT, STORAGE_MODE, CATEGORY, TYPE_MODE, " + //7 8 9 10 11 12 "LANG_MODE, TYPE_STATE, SECURITY_MODE, TRACKHISTORY, HISTORY_AGE, MAX_VERSIONS," + //13 14 15 16 17 18 19 20 21 "REL_TOTAL_MAXSRC, REL_TOTAL_MAXDST, CREATED_BY, CREATED_AT, MODIFIED_BY, MODIFIED_AT, ACL, WORKFLOW, ICON_REF, " + // 22 23 24 25 "MULTIPLE_CONTENT_ACLS, INSUPERTYPEQUERY, DEFACL, AUTO_VERSION" + " FROM " + TBL_STRUCT_TYPES + " ORDER BY NAME"; stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(curSql); ResultSet rsRelations; final Map<Long, FxString[]> labels = Database.loadFxStrings(con, TBL_STRUCT_TYPES, "description"); while (rs != null && rs.next()) { try { final long id = rs.getLong(1); ps.setLong(1, id); ArrayList<FxTypeRelation> alRelations = new ArrayList<FxTypeRelation>(10); rsRelations = ps.executeQuery(); while (rsRelations != null && rsRelations.next()) alRelations.add(new FxTypeRelation(new FxPreloadType(rsRelations.getLong(1)), new FxPreloadType(rsRelations.getLong(2)), rsRelations.getInt(3), rsRelations.getInt(4))); long parentId = rs.getLong(3); FxType parentType = id == 0 ? null : new FxPreloadType(parentId); final long defaultInstanceACLId = rs.getInt(24); final ACL defaultInstanceACL; if (!rs.wasNull()) defaultInstanceACL = environment.getACL(defaultInstanceACLId); else defaultInstanceACL = null; FxType _type = new FxType(id, environment.getACL(rs.getInt(19)), defaultInstanceACL, environment.getWorkflow(rs.getInt(20)), rs.getString(2), getTranslation(labels, id, 0, false), parentType, TypeStorageMode.getById(rs.getInt(4)), TypeCategory.getById(rs.getInt(5)), TypeMode.getById(rs.getInt(6)), LanguageMode.getById(rs.getInt(7)), TypeState.getById(rs.getInt(8)), rs.getByte(9), rs.getBoolean(22), rs.getBoolean(23), rs.getBoolean(10), rs.getLong(11), rs.getLong(12), rs.getBoolean(25), rs.getInt(13), rs.getInt(14), LifeCycleInfoImpl.load(rs, 15, 16, 17, 18), new ArrayList<FxType>(5), alRelations, FxSharedUtils.get(typeOptions, id, null)); long iconId = rs.getLong(21); if (!rs.wasNull()) _type.getIcon().setValue(new ReferencedContent(iconId)); result.add(_type); } catch (FxNotFoundException e) { throw new FxLoadException(LOG, e); } } } catch (SQLException exc) { throw new FxLoadException(LOG, "Failed to load all FxTypes: " + exc.getMessage(), exc); } finally { try { if (ps != null) ps.close(); } catch (SQLException e) { //ignore } Database.closeObjects(GenericEnvironmentLoader.class, null, stmt); } return result; } /** * {@inheritDoc} */ @Override public List<FxAssignment> loadAssignments(Connection con, FxEnvironment environment) throws FxLoadException { Statement stmt = null; String curSql; ArrayList<FxAssignment> result = new ArrayList<FxAssignment>(250); try { final Map<Long, FxString[]> translations = Database.loadFxStrings(con, TBL_STRUCT_ASSIGNMENTS, "DESCRIPTION", "HINT"); final Map<Long, List<FxStructureOption>> propertyAssignmentOptions = loadAllPropertyAssignmentOptions( con); final Map<Long, List<FxStructureOption>> groupAssignmentOptions = loadAllGroupAssignmentOptions(con); final Map<Long, FxFlatStorageMapping> flatStorageEntries = FxFlatStorageManager.getInstance() .loadAllFlatStorageMappings(con); final Map<Long, FxGroupAssignment> groupAssignments = Maps.newHashMap(); // group assignment lookup map by ID // 1 2 3 4 5 6 7 8 9 curSql = "SELECT ID, ATYPE, ENABLED, TYPEDEF, MINMULT, MAXMULT, POS, XPATH, XALIAS, " + //10 11 12 13 14 15 16 17 18 19 "PARENTGROUP, AGROUP, APROPERTY, ACL, BASE, DEFLANG, SYSINTERNAL, GROUPMODE, DEFMULT, DEFAULT_VALUE FROM " + TBL_STRUCT_ASSIGNMENTS + " ORDER BY TYPEDEF, PARENTGROUP, POS"; stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(curSql); final XStream xStream = ConversionEngine.getXStream(); while (rs != null && rs.next()) { final long id = rs.getLong(1); switch (rs.getInt(2)) { //ATYPE case FxAssignment.TYPE_GROUP: if (id == FxAssignment.NO_PARENT) break; FxGroupAssignment ga = new FxGroupAssignment(rs.getLong(1), rs.getBoolean(3), environment.getType(rs.getLong(4)), rs.getString(9), rs.getString(8), rs.getInt(7), FxMultiplicity.of(rs.getInt(5), rs.getInt(6)), rs.getInt(18), new FxPreloadGroupAssignment(rs.getLong(10)), rs.getLong(14), getTranslation(translations, id, 0, false), getTranslation(translations, id, 1, true), environment.getGroup(rs.getLong(11)), GroupMode.getById(rs.getInt(17)), FxSharedUtils.get(groupAssignmentOptions, rs.getLong(1), null)); if (rs.getBoolean(16)) ga._setSystemInternal(); result.add(ga); groupAssignments.put(ga.getId(), ga); break; case FxAssignment.TYPE_PROPERTY: FxValue defaultValue = null; String _def = rs.getString(19); if (!StringUtils.isEmpty(_def) && CacheAdmin.isEnvironmentLoaded()) { try { defaultValue = (FxValue) xStream.fromXML(_def); if (defaultValue != null && defaultValue.isEmpty()) defaultValue = null; } catch (Throwable e) { defaultValue = null; LOG.warn("Failed to unmarshall default value for assignment " + rs.getString(8) + ": " + e.getMessage(), e); } } FxPropertyAssignment pa = new FxPropertyAssignment(rs.getLong(1), rs.getBoolean(3), environment.getType(rs.getLong(4)), rs.getString(9), rs.getString(8), rs.getInt(7), FxMultiplicity.of(rs.getInt(5), rs.getInt(6)), rs.getInt(18), new FxPreloadGroupAssignment(rs.getLong(10)), rs.getLong(14), getTranslation(translations, id, 0, false), getTranslation(translations, id, 1, true), defaultValue, environment.getProperty(rs.getLong(12)), environment.getACL(rs.getInt(13)), rs.getInt(15), FxSharedUtils.get(propertyAssignmentOptions, rs.getLong(1), null), flatStorageEntries.get(rs.getLong(1))); if (rs.getBoolean(16)) pa._setSystemInternal(); result.add(pa); break; default: LOG.error("Invalid assignment type " + rs.getInt(2) + " for assignment #" + rs.getLong(1)); } } for (FxAssignment as : result) as.resolveDependencies(groupAssignments); } catch (SQLException exc) { throw new FxLoadException(LOG, "Failed to load all FxAssignments: " + exc.getMessage(), exc); } catch (FxNotFoundException e) { throw new FxLoadException(e); } finally { Database.closeObjects(GenericEnvironmentLoader.class, null, stmt); } return result; } /** * {@inheritDoc} */ @Override public List<Workflow> loadWorkflows(Connection con, FxEnvironment environment) throws FxLoadException { Statement stmt = null; final String sql = "SELECT ID, NAME, DESCRIPTION FROM " + TBL_WORKFLOW + " ORDER BY ID"; try { // Create the new workflow instance stmt = con.createStatement(); // Read all defined workflows stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql); ArrayList<Workflow> tmp = new ArrayList<Workflow>(10); while (rs != null && rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); String description = rs.getString(3); ArrayList<Step> wfSteps = new ArrayList<Step>(5); for (Step step : environment.getSteps()) { if (step.getWorkflowId() == id) { wfSteps.add(step); } } tmp.add(new Workflow(id, name, description, wfSteps, loadRoutes(con, id))); } return tmp; } catch (SQLException exc) { String sErr = "Unable to retrieve workflows"; LOG.error(sErr + ", sql=" + sql); throw new FxLoadException(sErr, exc); } finally { Database.closeObjects(GenericEnvironmentLoader.class, null, stmt); } } /** * {@inheritDoc} */ @Override public List<StepDefinition> loadStepDefinitions(Connection con) throws FxLoadException { Statement stmt = null; try { // Read all stepDefinitions from the database stmt = con.createStatement(); // 1 2 3 final ResultSet rs = stmt.executeQuery( "SELECT ID,NAME,UNIQUE_TARGET FROM " + TBL_WORKFLOW_STEPDEFINITION + " ORDER BY ID"); ArrayList<StepDefinition> tmp = new ArrayList<StepDefinition>(10); final Map<Long, FxString[]> labels = Database.loadFxStrings(con, TBL_WORKFLOW_STEPDEFINITION, "name"); // Build the result array set while (rs != null && rs.next()) { long id = rs.getLong(1); String name = rs.getString(2); int uniqueTargetId = rs.getInt(3); if (rs.wasNull()) { uniqueTargetId = -1; } StepDefinition aStepDef = new StepDefinition(id, getTranslation(labels, id, 0, false), name, uniqueTargetId); tmp.add(aStepDef); } return tmp; } catch (SQLException exc) { throw new FxLoadException(LOG, "Unable to read steps definitions", exc); } finally { Database.closeObjects(GenericEnvironmentLoader.class, null, stmt); } } /** * {@inheritDoc} */ @Override public List<Step> loadSteps(Connection con) throws FxLoadException { Statement stmt = null; // 1 2 3 4 final String sql = "SELECT DISTINCT stp.ID, stp.WORKFLOW, stp.STEPDEF,stp.ACL, stp.POS " + "FROM " + TBL_WORKFLOW_STEP + " stp ORDER BY stp.WORKFLOW, stp.POS, stp.ID"; try { // Load all steps in the database stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql); ArrayList<Step> steps = new ArrayList<Step>(30); while (rs != null && rs.next()) steps.add(new Step(rs.getLong(1), rs.getLong(3), rs.getLong(2), rs.getLong(4))); return steps; } catch (SQLException exc) { throw new FxLoadException(LOG, "Unable to read the steps from the database. " + "Error=" + exc.getMessage() + ", sql=" + sql, exc); } finally { Database.closeObjects(GenericEnvironmentLoader.class, null, stmt); } } /** * {@inheritDoc} */ @Override public List<Route> loadRoutes(Connection con, int workflowId) throws FxLoadException { // 1 2 3 4 final String sql = "SELECT ro.ID,ro.FROM_STEP,ro.TO_STEP,ro.USERGROUP " + "FROM " + TBL_WORKFLOW_ROUTES + " ro, " + TBL_WORKFLOW_STEP + " stp " + "WHERE ro.TO_STEP=stp.ID AND stp.WORKFLOW=" + workflowId + " " + "ORDER BY ro.USERGROUP ASC"; if (LOG.isDebugEnabled()) LOG.debug("getRoute(" + workflowId + ")=" + sql); Statement stmt = null; try { stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql); ArrayList<Route> routes = new ArrayList<Route>(50); // Process result set while (rs != null && rs.next()) { long routeId = rs.getLong(1); long fromId = rs.getLong(2); long toId = rs.getLong(3); long groupId = rs.getLong(4); Route route = new Route(routeId, groupId, fromId, toId); routes.add(route); } return routes; } catch (SQLException exc) { throw new FxLoadException(LOG, "Unable to load routes for workflow [" + workflowId + "], msg=" + exc.getMessage() + ", sql=" + sql, exc); } finally { Database.closeObjects(GenericEnvironmentLoader.class, null, stmt); } } /** * {@inheritDoc} */ @Override public List<FxScriptInfo> loadScripts(Connection con) throws FxLoadException, FxNotFoundException, FxInvalidParameterException { PreparedStatement ps = null; String sql; List<FxScriptInfo> scripts = new ArrayList<FxScriptInfo>(10); try { // 1 2 3 4 5 6 sql = "SELECT ID,SNAME,SDESC,STYPE,ACTIVE,IS_CACHED FROM " + TBL_SCRIPTS + " ORDER BY ID"; ps = con.prepareStatement(sql); ResultSet rs = ps.executeQuery(); while (rs != null && rs.next()) scripts.add(new FxScriptInfo(rs.getLong(1), FxScriptEvent.getById(rs.getLong(4)), rs.getString(2), rs.getString(3), rs.getBoolean(5), rs.getBoolean(6))); } catch (SQLException exc) { throw new FxLoadException(LOG, exc, "ex.scripting.load.failed", -1, exc.getMessage()); } finally { Database.closeObjects(GenericEnvironmentLoader.class, null, ps); } return scripts; } /** * {@inheritDoc} */ @Override public List<FxScriptMapping> loadScriptMapping(Connection con, FxEnvironmentImpl environment) throws FxLoadException { List<FxScriptMapping> mapping = new ArrayList<FxScriptMapping>(20); List<FxScriptMappingEntry> e_ass; List<FxScriptMappingEntry> e_types; PreparedStatement ps_a = null, ps_t = null; String sql; try { // 1 2 3 4 sql = "SELECT ASSIGNMENT,DERIVED_USAGE,ACTIVE,STYPE FROM " + TBL_SCRIPT_MAPPING_ASSIGN + " WHERE SCRIPT=? ORDER BY ASSIGNMENT"; ps_a = con.prepareStatement(sql); sql = "SELECT TYPEDEF,DERIVED_USAGE,ACTIVE,STYPE FROM " + TBL_SCRIPT_MAPPING_TYPES + " WHERE SCRIPT=? ORDER BY TYPEDEF"; ps_t = con.prepareStatement(sql); ResultSet rs; for (FxScriptInfo si : environment.getScripts()) { ps_a.setLong(1, si.getId()); ps_t.setLong(1, si.getId()); rs = ps_a.executeQuery(); e_ass = new ArrayList<FxScriptMappingEntry>(20); e_types = new ArrayList<FxScriptMappingEntry>(20); while (rs != null && rs.next()) { long[] derived; if (!rs.getBoolean(2)) derived = new long[0]; else { List<FxAssignment> ass = environment.getDerivedAssignments(rs.getLong(1)); derived = new long[ass.size()]; for (int i = 0; i < ass.size(); i++) derived[i] = ass.get(i).getId(); } e_ass.add(new FxScriptMappingEntry(FxScriptEvent.getById(rs.getLong(4)), si.getId(), rs.getBoolean(3), rs.getBoolean(2), rs.getLong(1), derived)); } rs = ps_t.executeQuery(); while (rs != null && rs.next()) { long[] derived; if (!rs.getBoolean(2)) derived = new long[0]; else { // determine derived types "manually" as resolveReferences() // which sets the derived types has not been called yet List<Long> derivedList = new ArrayList<Long>(5); for (FxType t : environment.getTypes()) { if (t.isDerived() && t.getParent().getId() == rs.getLong(1)) derivedList.add(t.getId()); } derived = new long[derivedList.size()]; int i = 0; for (long l : derivedList) derived[i++] = l; } e_types.add(new FxScriptMappingEntry(FxScriptEvent.getById(rs.getLong(4)), si.getId(), rs.getBoolean(3), rs.getBoolean(2), rs.getLong(1), derived)); } mapping.add(new FxScriptMapping(si.getId(), e_types, e_ass)); } } catch (SQLException exc) { throw new FxLoadException(LOG, exc, "ex.scripting.mapping.load.failed", exc.getMessage()); } catch (FxNotFoundException e) { throw new FxLoadException(e); } finally { try { if (ps_t != null) ps_t.close(); } catch (SQLException e) { //ignore } Database.closeObjects(GenericEnvironmentLoader.class, null, ps_a); } return mapping; } /** * {@inheritDoc} * @since 3.1.2 */ @Override public List<FxScriptSchedule> loadScriptSchedules(Connection con, FxEnvironmentImpl environment) throws FxLoadException { PreparedStatement ps = null; String sql; List<FxScriptSchedule> schedules = new ArrayList<FxScriptSchedule>(10); try { // 1 2 3 4 5 6 7 8 9 sql = "SELECT ID,SCRIPT,SNAME,ACTIVE,STARTTIME,ENDTIME,REPEATINTERVAL,REPEATTIMES,CRONSTRING FROM " + TBL_SCRIPT_SCHEDULES + " ORDER BY SCRIPT"; ps = con.prepareStatement(sql); ResultSet rs = ps.executeQuery(); while (rs != null && rs.next()) { long id = rs.getLong(1); long scriptId = rs.getLong(2); String name = rs.getString(3); if (rs.wasNull()) name = ""; boolean active = rs.getBoolean(4); java.util.Date startTime = new java.util.Date(rs.getTimestamp(5).getTime()); Timestamp endts = rs.getTimestamp(6); final java.util.Date endTime; if (rs.wasNull()) endTime = null; else endTime = new java.util.Date(endts.getTime()); long repeatInterval = rs.getLong(7); int repeatTimes = rs.getInt(8); String cronString = rs.getString(9); if (rs.wasNull()) cronString = ""; schedules.add(new FxScriptSchedule(id, scriptId, name, active, startTime, endTime, repeatInterval, repeatTimes, cronString)); } } catch (SQLException exc) { throw new FxLoadException(LOG, exc, "ex.scripting.schedule.load.failed", -1, exc.getMessage()); } finally { Database.closeObjects(GenericEnvironmentLoader.class, null, ps); } return schedules; } /** * {@inheritDoc} */ @Override public List<FxSelectList> loadSelectLists(Connection con, FxEnvironmentImpl environment) throws FxLoadException { PreparedStatement ps = null; String sql; List<FxSelectList> lists = new ArrayList<FxSelectList>(10); try { final Map<Long, FxString[]> translations = Database.loadFxStrings(con, TBL_STRUCT_SELECTLIST, "LABEL", "DESCRIPTION"); final Map<Long, FxString[]> itemTranslations = Database.loadFxStrings(con, TBL_STRUCT_SELECTLIST_ITEM, "LABEL"); // 1 2 3 4 5 6 7 8 9 10 sql = "SELECT ID,PARENTID,NAME,ALLOW_ITEM_CREATE,ACL_CREATE_ITEM,ACL_ITEM_NEW,DEFAULT_ITEM,BCSEP,SAMELVLSELECT,SORTENTRIES FROM " + TBL_STRUCT_SELECTLIST + " ORDER BY NAME"; ps = con.prepareStatement(sql); ResultSet rs = ps.executeQuery(); while (rs != null && rs.next()) { final long id = rs.getLong(1); long parent = rs.getLong(2); if (rs.wasNull()) parent = -1; String bcSep = rs.getString(8); if (rs.wasNull()) bcSep = " > "; boolean sameLvl = rs.getBoolean(9); if (rs.wasNull()) sameLvl = false; boolean sort = rs.getBoolean(10); if (rs.wasNull()) sort = false; lists.add(new FxSelectList(id, parent, rs.getString(3), getTranslation(translations, id, 0, false), getTranslation(translations, id, 1, false), rs.getBoolean(4), environment.getACL(rs.getLong(5)), environment.getACL(rs.getLong(6)), rs.getLong(7), bcSep, sameLvl, sort)); } ps.close(); // 1 2 3 4 5 6 7 8 9 10 11 12 13 sql = "SELECT ID,NAME,ACL,PARENTID,DATA,COLOR,CREATED_BY,CREATED_AT,MODIFIED_BY,MODIFIED_AT,DBIN_ID,DBIN_VER,DBIN_QUALITY FROM " + TBL_STRUCT_SELECTLIST_ITEM + " WHERE LISTID=? ORDER BY POS,ID"; ps = con.prepareStatement(sql); for (FxSelectList list : lists) { ps.setLong(1, list.getId()); rs = ps.executeQuery(); int pos = 0; while (rs != null && rs.next()) { final long id = rs.getLong(1); long parent = rs.getLong(4); if (rs.wasNull()) parent = -1; new FxSelectListItem(id, rs.getString(2), environment.getACL(rs.getLong(3)), list, parent, getTranslation(itemTranslations, id, 0, false), rs.getString(5), rs.getString(6), rs.getLong(11), rs.getInt(12), rs.getInt(13), LifeCycleInfoImpl.load(rs, 7, 8, 9, 10), pos++); } } } catch (SQLException exc) { throw new FxLoadException(LOG, exc, "ex.structure.list.load.failed", exc.getMessage()); } finally { Database.closeObjects(GenericEnvironmentLoader.class, null, ps); } return lists; } private FxString getTranslation(final Map<Long, FxString[]> translations, final long id, final int index, boolean emptyToNull) { final FxString[] values = translations.get(id); if (values == null) { return emptyToNull ? null : new FxString("").setEmpty(); } final FxString value = values[index]; return emptyToNull && value.isEmpty() ? null : value; } private String cachedString(Map<String, String> cache, String value) { if (cache.containsKey(value)) { return cache.get(value); } else { cache.put(value, value); return value; } } }