/**
* Licensed to Apereo under one or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information regarding copyright ownership. Apereo
* licenses this file to you 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 the
* following location:
*
* <p>http://www.apache.org/licenses/LICENSE-2.0
*
* <p>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 org.apereo.portal.layout.simple;
import com.google.common.cache.Cache;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Hashtable;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.apereo.portal.EntityIdentifier;
import org.apereo.portal.IUserProfile;
import org.apereo.portal.UserProfile;
import org.apereo.portal.i18n.ILocaleStore;
import org.apereo.portal.i18n.LocaleManager;
import org.apereo.portal.jdbc.DatabaseMetaDataImpl;
import org.apereo.portal.jdbc.IDatabaseMetadata;
import org.apereo.portal.jdbc.IJoinQueryString;
import org.apereo.portal.jpa.BasePortalJpaDao;
import org.apereo.portal.layout.IUserLayoutStore;
import org.apereo.portal.layout.LayoutStructure;
import org.apereo.portal.layout.dao.IStylesheetDescriptorDao;
import org.apereo.portal.portlet.registry.IPortletDefinitionRegistry;
import org.apereo.portal.security.IPerson;
import org.apereo.portal.security.ISecurityContext;
import org.apereo.portal.security.provider.PersonImpl;
import org.apereo.portal.spring.locator.CounterStoreLocator;
import org.apereo.portal.utils.DocumentFactory;
import org.apereo.portal.utils.Tuple;
import org.apereo.portal.utils.threading.SingletonDoubleCheckedCreator;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionOperations;
import org.springframework.transaction.support.TransactionTemplate;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
/**
* SQL implementation for the 2.x relational database model.
*
* <p>Prior to uPortal 2.5, this class existed in the org.jasig.portal package. It was moved to its
* present package to express that it is part of the Simple Layout Manager implementation.
*
*/
public abstract class RDBMUserLayoutStore implements IUserLayoutStore, InitializingBean {
protected final Logger logger = LoggerFactory.getLogger(getClass());
private static String PROFILE_TABLE = "UP_USER_PROFILE";
private static final String UNSUPPORTED_MULTIPLE_LAYOUTS_FOUND =
"User ID {}'s profiles contain more than one non-zero layout id. This is currently not supported.";
//This class is instantiated ONCE so NO class variables can be used to keep state between calls
protected static final String channelPrefix = "n";
protected static final String folderPrefix = "s";
protected TransactionOperations transactionOperations;
protected TransactionOperations nextStructTransactionOperations;
protected JdbcOperations jdbcOperations;
private ILocaleStore localeStore;
protected IDatabaseMetadata databaseMetadata;
protected IPortletDefinitionRegistry portletDefinitionRegistry;
protected IStylesheetDescriptorDao stylesheetDescriptorDao;
// I18n property
protected static final boolean localeAware = LocaleManager.isLocaleAware();
@Autowired
public void setLocaleStore(ILocaleStore localeStore) {
this.localeStore = localeStore;
}
@Autowired
public void setStylesheetDescriptorDao(IStylesheetDescriptorDao stylesheetDescriptorDao) {
this.stylesheetDescriptorDao = stylesheetDescriptorDao;
}
@Autowired
public void setPlatformTransactionManager(
@Qualifier(BasePortalJpaDao.PERSISTENCE_UNIT_NAME)
PlatformTransactionManager platformTransactionManager) {
this.transactionOperations = new TransactionTemplate(platformTransactionManager);
final DefaultTransactionDefinition nextStructTransactionDefinition =
new DefaultTransactionDefinition();
nextStructTransactionDefinition.setPropagationBehavior(
DefaultTransactionDefinition.PROPAGATION_REQUIRES_NEW);
this.nextStructTransactionOperations =
new TransactionTemplate(
platformTransactionManager, nextStructTransactionDefinition);
}
@Resource(name = BasePortalJpaDao.PERSISTENCE_UNIT_NAME)
public void setDataSource(DataSource dataSource) {
this.jdbcOperations = new JdbcTemplate(dataSource);
}
@Autowired
public void setDatabaseMetadata(IDatabaseMetadata databaseMetadata) {
this.databaseMetadata = databaseMetadata;
}
@Autowired
public void setPortletDefinitionRegistry(IPortletDefinitionRegistry portletDefinitionRegistry) {
this.portletDefinitionRegistry = portletDefinitionRegistry;
}
@Override
public void afterPropertiesSet() throws Exception {
if (this.databaseMetadata.supportsOuterJoins()) {
final IJoinQueryString joinQuery = this.databaseMetadata.getJoinQuery();
if (joinQuery instanceof DatabaseMetaDataImpl.JdbcDb) {
joinQuery.addQuery(
"layout",
"{oj UP_LAYOUT_STRUCT ULS LEFT OUTER JOIN UP_LAYOUT_PARAM USP ON ULS.USER_ID = USP.USER_ID AND ULS.STRUCT_ID = USP.STRUCT_ID} WHERE");
joinQuery.addQuery(
"ss_struct",
"{oj UP_SS_STRUCT USS LEFT OUTER JOIN UP_SS_STRUCT_PAR USP ON USS.SS_ID=USP.SS_ID} WHERE");
joinQuery.addQuery(
"ss_theme",
"{oj UP_SS_THEME UTS LEFT OUTER JOIN UP_SS_THEME_PARM UTP ON UTS.SS_ID=UTP.SS_ID} WHERE");
} else if (joinQuery instanceof DatabaseMetaDataImpl.PostgreSQLDb) {
joinQuery.addQuery(
"layout",
"UP_LAYOUT_STRUCT ULS LEFT OUTER JOIN UP_LAYOUT_PARAM USP ON ULS.USER_ID = USP.USER_ID AND ULS.STRUCT_ID = USP.STRUCT_ID WHERE");
joinQuery.addQuery(
"ss_struct",
"UP_SS_STRUCT USS LEFT OUTER JOIN UP_SS_STRUCT_PAR USP ON USS.SS_ID=USP.SS_ID WHERE");
joinQuery.addQuery(
"ss_theme",
"UP_SS_THEME UTS LEFT OUTER JOIN UP_SS_THEME_PARM UTP ON UTS.SS_ID=UTP.SS_ID WHERE");
} else if (joinQuery instanceof DatabaseMetaDataImpl.OracleDb) {
joinQuery.addQuery(
"layout",
"UP_LAYOUT_STRUCT ULS, UP_LAYOUT_PARAM USP WHERE ULS.STRUCT_ID = USP.STRUCT_ID(+) AND ULS.USER_ID = USP.USER_ID(+) AND");
joinQuery.addQuery(
"ss_struct",
"UP_SS_STRUCT USS, UP_SS_STRUCT_PAR USP WHERE USS.SS_ID=USP.SS_ID(+) AND");
joinQuery.addQuery(
"ss_theme",
"UP_SS_THEME UTS, UP_SS_THEME_PARM UTP WHERE UTS.SS_ID=UTP.SS_ID(+) AND");
} else {
throw new RuntimeException("Unknown database driver");
}
}
}
private final SingletonDoubleCheckedCreator<IPerson> systemPersonCreator =
new SingletonDoubleCheckedCreator<IPerson>() {
protected IPerson createSingleton(Object... args) {
// be sure we only do this once...
// Load the "system" user id from the database
final int systemUserId =
jdbcOperations.queryForObject(
"SELECT USER_ID FROM UP_USER WHERE USER_NAME = 'system'",
Integer.class);
logger.info("Found user id {} for the 'system' user.", systemUserId);
return new SystemUser(systemUserId);
}
};
private final IPerson getSystemUser() {
return this.systemPersonCreator.get();
}
/**
* Add a user profile
*
* @param person
* @param profile
* @return userProfile
* @exception Exception
*/
public UserProfile addUserProfile(final IPerson person, final IUserProfile profile) {
final int userId = person.getID();
final int layoutId = getLayoutId(person, profile);
// generate an id for this profile
return this.jdbcOperations.execute(
new ConnectionCallback<UserProfile>() {
@Override
public UserProfile doInConnection(Connection con)
throws SQLException, DataAccessException {
String sQuery = null;
PreparedStatement pstmt =
con.prepareStatement(
"INSERT INTO UP_USER_PROFILE "
+ "(USER_ID,PROFILE_ID,PROFILE_FNAME,PROFILE_NAME,STRUCTURE_SS_ID,THEME_SS_ID,"
+ "DESCRIPTION, LAYOUT_ID) VALUES (?,?,?,?,?,?,?,?)");
int profileId = getNextKey();
pstmt.setInt(1, userId);
pstmt.setInt(2, profileId);
pstmt.setString(3, profile.getProfileFname());
pstmt.setString(4, profile.getProfileName());
pstmt.setInt(5, profile.getStructureStylesheetId());
pstmt.setInt(6, profile.getThemeStylesheetId());
pstmt.setString(7, profile.getProfileDescription());
pstmt.setInt(8, layoutId);
sQuery =
"INSERT INTO UP_USER_PROFILE (USER_ID,PROFILE_ID,PROFILE_FNAME,PROFILE_NAME,STRUCTURE_SS_ID,THEME_SS_ID,DESCRIPTION, LAYOUT_ID) VALUES ("
+ userId
+ ",'"
+ profileId
+ ",'"
+ profile.getProfileFname()
+ "','"
+ profile.getProfileName()
+ "',"
+ profile.getStructureStylesheetId()
+ ","
+ profile.getThemeStylesheetId()
+ ",'"
+ profile.getProfileDescription()
+ "', "
+ profile.getLayoutId()
+ ")";
logger.debug("addUserProfile(): {}", sQuery);
try {
pstmt.executeUpdate();
UserProfile newProfile = new UserProfile();
newProfile.setProfileId(profileId);
newProfile.setLayoutId(layoutId);
newProfile.setLocaleManager(profile.getLocaleManager());
newProfile.setProfileDescription(profile.getProfileDescription());
newProfile.setProfileFname(profile.getProfileFname());
newProfile.setProfileName(profile.getProfileName());
newProfile.setStructureStylesheetId(profile.getStructureStylesheetId());
newProfile.setSystemProfile(false);
newProfile.setThemeStylesheetId(profile.getThemeStylesheetId());
return newProfile;
} finally {
pstmt.close();
}
}
});
}
/*
* The code currently only supports one instantiated/saved layout for a user's set
* of profiles. If the layout hasn't been instantiated/saved for a user the layout id
* will be zero for all the profiles and if it has been saved it will be one.
* When more than one layout becomes supported, this logic will need to be reworked to
* support multiple layouts. It currently just checks if the layout for the user has
* been instantiated/saved and sets the new profile to the saved layout id (currently hardcoded
* elsewhere to always be one)
*/
private int getLayoutId(final IPerson person, final IUserProfile profile) {
final Hashtable<Integer, UserProfile> profiles = this.getUserProfileList(person);
int layoutId = profile.getLayoutId();
Set<Integer> layoutIds = getAllNonZeroLayoutIdsFromProfiles(profiles.values());
if (!layoutIds.isEmpty()) {
layoutId = layoutIds.iterator().next();
}
if (layoutIds.size() > 1) {
// log that only one non-zero layout id assumption has been broken
logger.error(UNSUPPORTED_MULTIPLE_LAYOUTS_FOUND, person.getID());
}
return layoutId;
}
private Set<Integer> getAllNonZeroLayoutIdsFromProfiles(
final Collection<UserProfile> profiles) {
Set<Integer> layoutIds = new HashSet<Integer>();
for (UserProfile profile : profiles) {
int userProfileLayoutId = profile.getLayoutId();
if (userProfileLayoutId != 0) {
layoutIds.add(userProfileLayoutId);
}
}
return layoutIds;
}
private int getNextKey() {
return CounterStoreLocator.getCounterStore().getNextId(PROFILE_TABLE);
}
/**
* Checks if a channel has been approved
*
* @param approvedDate
* @return boolean Channel is approved
*/
protected static boolean channelApproved(java.util.Date approvedDate) {
java.util.Date rightNow = new java.util.Date();
return (approvedDate != null && rightNow.after(approvedDate));
}
/**
* Create a layout
*
* @param layoutStructure
* @param doc
* @param root
* @param structId
* @exception java.sql.SQLException
*/
protected final void createLayout(
HashMap layoutStructure, Document doc, Element root, int structId)
throws java.sql.SQLException {
while (structId != 0) {
LayoutStructure ls = (LayoutStructure) layoutStructure.get(new Integer(structId));
// replaced with call to method in containing class to allow overriding
// by subclasses of RDBMUserLayoutStore.
// Element structure = ls.getStructureDocument(doc);
Element structure = getStructure(doc, ls);
root.appendChild(structure);
String id = structure.getAttribute("ID");
if (id != null && !id.equals("")) {
structure.setIdAttribute("ID", true);
}
createLayout(layoutStructure, doc, structure, ls.getChildId());
structId = ls.getNextId();
}
}
/**
* convert true/false into Y/N for database
*
* @param value to check
* @result boolean
*/
protected static final boolean xmlBool(String value) {
return (value != null && value.equals("true") ? true : false);
}
/**
* Return the next available channel structure id for a user
*
* @param person
* @return the next available channel structure id
*/
public String generateNewChannelSubscribeId(IPerson person) {
return getNextStructId(person, channelPrefix);
}
/**
* Return the next available folder structure id for a user
*
* @param person
* @return a <code>String</code> that is the next free structure ID
* @exception Exception
*/
public String generateNewFolderId(IPerson person) {
return getNextStructId(person, folderPrefix);
}
/**
* Return the next available structure id for a user
*
* @param person
* @param prefix
* @return next free structure ID
* @exception Exception
*/
protected String getNextStructId(final IPerson person, final String prefix) {
final int userId = person.getID();
return this.nextStructTransactionOperations.execute(
new TransactionCallback<String>() {
@Override
public String doInTransaction(TransactionStatus status) {
return jdbcOperations.execute(
new ConnectionCallback<String>() {
@Override
public String doInConnection(Connection con)
throws SQLException, DataAccessException {
Statement stmt = con.createStatement();
try {
String sQuery =
"SELECT NEXT_STRUCT_ID FROM UP_USER WHERE USER_ID="
+ userId;
logger.debug("getNextStructId(): {}", sQuery);
ResultSet rs = stmt.executeQuery(sQuery);
int currentStructId;
try {
if (rs.next()) {
currentStructId = rs.getInt(1);
} else {
throw new SQLException(
"no rows returned for query ["
+ sQuery
+ "]");
}
} finally {
rs.close();
}
int nextStructId = currentStructId + 1;
String sUpdate =
"UPDATE UP_USER SET NEXT_STRUCT_ID="
+ nextStructId
+ " WHERE USER_ID="
+ userId
+ " AND NEXT_STRUCT_ID="
+ currentStructId;
logger.debug("getNextStructId(): {}", sUpdate);
stmt.executeUpdate(sUpdate);
return prefix + nextStructId;
} finally {
stmt.close();
}
}
});
}
});
}
protected Document getPersonalUserLayout(final IPerson person, final IUserProfile profile) {
final LocaleManager localeManager = profile.getLocaleManager();
return jdbcOperations.execute(
new ConnectionCallback<Document>() {
@Override
public Document doInConnection(Connection con)
throws SQLException, DataAccessException {
ResultSet rs;
int userId = person.getID();
final int realUserId = userId;
Document doc = DocumentFactory.getThreadDocument();
Element root = doc.createElement("layout");
final Statement stmt = con.createStatement();
// A separate statement is needed so as not to interfere with ResultSet
// of statements used for queries
Statement insertStmt = con.createStatement();
try {
long startTime = System.currentTimeMillis();
// eventually, we need to fix template layout implementations so you can just do this:
// int layoutId=profile.getLayoutId();
// but for now:
int layoutId = getLayoutID(userId, profile.getProfileId());
if (layoutId
== 0) { // First time, grab the default layout for this user
final Tuple<Integer, Integer> userLayoutIds =
transactionOperations.execute(
new TransactionCallback<Tuple<Integer, Integer>>() {
@Override
public Tuple<Integer, Integer> doInTransaction(
TransactionStatus status) {
return jdbcOperations.execute(
new ConnectionCallback<
Tuple<Integer, Integer>>() {
@Override
public Tuple<Integer, Integer>
doInConnection(
Connection con)
throws
SQLException,
DataAccessException {
int newLayoutId;
int newUserId;
String sQuery =
"SELECT USER_DFLT_USR_ID, USER_DFLT_LAY_ID FROM UP_USER WHERE USER_ID="
+ realUserId;
logger.debug(
"getUserLayout(): {}",
sQuery);
ResultSet rs =
stmt.executeQuery(
sQuery);
try {
if (rs.next()) {
newUserId =
rs.getInt(1);
newLayoutId =
rs.getInt(2);
} else {
final String msg = "Unable to find default user for USER_ID=" + realUserId;
throw new SQLException(msg);
}
} finally {
rs.close();
}
// Make sure the next struct id is set in case the user adds a channel
sQuery =
"SELECT NEXT_STRUCT_ID FROM UP_USER WHERE USER_ID="
+ newUserId;
logger.debug(
"getUserLayout(): {}",
sQuery);
int nextStructId;
rs =
stmt.executeQuery(
sQuery);
try {
if (rs.next()) {
nextStructId =
rs.getInt(1);
} else {
final String msg = "Unable to find NEXT_STRUCT_ID for USER_ID=" + realUserId;
throw new SQLException(msg);
}
} finally {
rs.close();
}
int realNextStructId = 0;
if (realUserId
!= newUserId) {
// But never make the existing value SMALLER, change it only to make it LARGER
// (so, get existing value)
sQuery =
"SELECT NEXT_STRUCT_ID FROM UP_USER WHERE USER_ID="
+ realUserId;
logger.debug(
"getUserLayout(): {}",
sQuery);
rs =
stmt
.executeQuery(
sQuery);
try {
if (rs.next()) {
realNextStructId =
rs.getInt(1);
} else {
final String msg = "Unable to find NEXT_STRUCT_ID for USER_ID=" + realUserId;
throw new SQLException(msg);
}
} finally {
rs.close();
}
}
if (nextStructId
> realNextStructId) {
sQuery =
"UPDATE UP_USER SET NEXT_STRUCT_ID="
+ nextStructId
+ " WHERE USER_ID="
+ realUserId;
logger.debug(
"getUserLayout(): {}",
sQuery);
stmt.executeUpdate(
sQuery);
}
return new Tuple<
Integer, Integer>(
newUserId,
newLayoutId);
}
});
}
});
userId = userLayoutIds.first;
layoutId = userLayoutIds.second;
}
int firstStructId = -1;
//Flags to enable a default layout lookup if it's needed
boolean foundLayout = false;
boolean triedDefault = false;
//This loop is used to ensure a layout is found for a user. It tries
//looking up the layout for the current userID. If one isn't found
//the userID is replaced with the template user ID for this user and
//the layout is searched for again. This loop should only ever loop once.
do {
String sQuery =
"SELECT INIT_STRUCT_ID FROM UP_USER_LAYOUT WHERE USER_ID="
+ userId
+ " AND LAYOUT_ID = "
+ layoutId;
logger.debug("getUserLayout(): {}", sQuery);
rs = stmt.executeQuery(sQuery);
try {
if (rs.next()) {
firstStructId = rs.getInt(1);
} else {
throw new RuntimeException(
"getUserLayout(): No INIT_STRUCT_ID in UP_USER_LAYOUT for USER_ID: "
+ userId
+ " and LAYOUT_ID: "
+ layoutId);
}
} finally {
rs.close();
}
String sql;
if (localeAware) {
// This needs to be changed to get the localized strings
sql =
"SELECT ULS.STRUCT_ID,ULS.NEXT_STRUCT_ID,ULS.CHLD_STRUCT_ID,ULS.CHAN_ID,ULS.NAME,ULS.TYPE,ULS.HIDDEN,"
+ "ULS.UNREMOVABLE,ULS.IMMUTABLE";
} else {
sql =
"SELECT ULS.STRUCT_ID,ULS.NEXT_STRUCT_ID,ULS.CHLD_STRUCT_ID,ULS.CHAN_ID,ULS.NAME,ULS.TYPE,ULS.HIDDEN,"
+ "ULS.UNREMOVABLE,ULS.IMMUTABLE";
}
if (databaseMetadata.supportsOuterJoins()) {
sql +=
",USP.STRUCT_PARM_NM,USP.STRUCT_PARM_VAL FROM "
+ databaseMetadata
.getJoinQuery()
.getQuery("layout");
} else {
sql += " FROM UP_LAYOUT_STRUCT ULS WHERE ";
}
sql +=
" ULS.USER_ID="
+ userId
+ " AND ULS.LAYOUT_ID="
+ layoutId
+ " ORDER BY ULS.STRUCT_ID";
logger.debug("getUserLayout(): {}", sql);
rs = stmt.executeQuery(sql);
//check for rows in the result set
foundLayout = rs.next();
if (!foundLayout && !triedDefault && userId == realUserId) {
//If we didn't find any rows and we haven't tried the default user yet
triedDefault = true;
rs.close();
//Get the default user ID and layout ID
sQuery =
"SELECT USER_DFLT_USR_ID, USER_DFLT_LAY_ID FROM UP_USER WHERE USER_ID="
+ userId;
logger.debug("getUserLayout(): {}", sQuery);
rs = stmt.executeQuery(sQuery);
try {
rs.next();
userId = rs.getInt(1);
layoutId = rs.getInt(2);
} finally {
rs.close();
}
} else {
//We tried the default or actually found a layout
break;
}
} while (!foundLayout);
HashMap layoutStructure = new HashMap();
StringBuffer structChanIds = new StringBuffer();
try {
int lastStructId = 0;
LayoutStructure ls = null;
String sepChar = "";
if (foundLayout) {
int structId = rs.getInt(1);
// Result Set returns 0 by default if structId was null
// Except if you are using poolman 2.0.4 in which case you get -1 back
if (rs.wasNull()) {
structId = 0;
}
readLayout:
while (true) {
int nextId = rs.getInt(2);
if (rs.wasNull()) {
nextId = 0;
}
int childId = rs.getInt(3);
if (rs.wasNull()) {
childId = 0;
}
int chanId = rs.getInt(4);
if (rs.wasNull()) {
chanId = 0;
}
String temp5 =
rs.getString(
5); // Some JDBC drivers require columns accessed in order
String temp6 =
rs.getString(
6); // Access 5 and 6 now, save till needed.
// uPortal i18n
int name_index, value_index;
if (localeAware) {
Locale[] locales = localeManager.getLocales();
String locale = locales[0].toString();
ls =
new LayoutStructure(
structId,
nextId,
childId,
chanId,
rs.getString(7),
rs.getString(8),
rs.getString(9),
locale);
name_index = 10;
value_index = 11;
} else {
ls =
new LayoutStructure(
structId,
nextId,
childId,
chanId,
rs.getString(7),
rs.getString(8),
rs.getString(9));
name_index = 10;
value_index = 11;
}
layoutStructure.put(new Integer(structId), ls);
lastStructId = structId;
if (!ls.isChannel()) {
ls.addFolderData(
temp5, temp6); // Plug in saved column values
}
if (databaseMetadata.supportsOuterJoins()) {
do {
String name = rs.getString(name_index);
String value =
rs.getString(
value_index); // Oracle JDBC requires us to do this for longs
if (name
!= null) { // may not be there because of the join
ls.addParameter(name, value);
}
if (!rs.next()) {
break readLayout;
}
structId = rs.getInt(1);
if (rs.wasNull()) {
structId = 0;
}
} while (structId == lastStructId);
} else { // Do second SELECT later on for structure parameters
if (ls.isChannel()) {
structChanIds.append(sepChar + ls.getChanId());
sepChar = ",";
}
if (rs.next()) {
structId = rs.getInt(1);
if (rs.wasNull()) {
structId = 0;
}
} else {
break readLayout;
}
}
} // while
}
} finally {
rs.close();
}
if (!databaseMetadata.supportsOuterJoins()
&& structChanIds.length() > 0) { // Pick up structure parameters
// first, get the struct ids for the channels
String sql =
"SELECT STRUCT_ID FROM UP_LAYOUT_STRUCT WHERE USER_ID="
+ userId
+ " AND LAYOUT_ID="
+ layoutId
+ " AND CHAN_ID IN ("
+ structChanIds.toString()
+ ") ORDER BY STRUCT_ID";
logger.debug("getUserLayout(): {}", sql);
StringBuffer structIdsSB = new StringBuffer("");
String sep = "";
rs = stmt.executeQuery(sql);
try {
// use the results to build a correct list of struct ids to look for
while (rs.next()) {
structIdsSB.append(sep + rs.getString(1));
sep = ",";
} // while
} finally {
rs.close();
} // be a good doobie
sql =
"SELECT STRUCT_ID, STRUCT_PARM_NM,STRUCT_PARM_VAL FROM UP_LAYOUT_PARAM WHERE USER_ID="
+ userId
+ " AND LAYOUT_ID="
+ layoutId
+ " AND STRUCT_ID IN ("
+ structIdsSB.toString()
+ ") ORDER BY STRUCT_ID";
logger.debug("getUserLayout(): {}", sql);
rs = stmt.executeQuery(sql);
try {
if (rs.next()) {
int structId = rs.getInt(1);
readParm:
while (true) {
LayoutStructure ls =
(LayoutStructure)
layoutStructure.get(
new Integer(structId));
int lastStructId = structId;
do {
ls.addParameter(rs.getString(2), rs.getString(3));
if (!rs.next()) {
break readParm;
}
} while ((structId = rs.getInt(1)) == lastStructId);
}
}
} finally {
rs.close();
}
}
if (layoutStructure.size() > 0) { // We have a layout to work with
createLayout(layoutStructure, doc, root, firstStructId);
layoutStructure.clear();
if (logger.isDebugEnabled()) {
long stopTime = System.currentTimeMillis();
long timeTook = stopTime - startTime;
logger.debug(
"getUserLayout(): Layout document for user {} took {} milliseconds to create",
userId,
timeTook);
}
doc.appendChild(root);
}
} finally {
stmt.close();
insertStmt.close();
}
return doc;
}
});
}
private final ThreadLocal<Cache<Tuple<String, String>, UserProfile>> profileCacheHolder =
new ThreadLocal<Cache<Tuple<String, String>, UserProfile>>();
/** Cache used during import/export operations */
public void setProfileImportExportCache(
Cache<Tuple<String, String>, UserProfile> profileCache) {
if (profileCache == null) {
this.profileCacheHolder.remove();
} else {
this.profileCacheHolder.set(profileCache);
}
}
private Cache<Tuple<String, String>, UserProfile> getProfileImportExportCache() {
return this.profileCacheHolder.get();
}
public UserProfile getUserProfileByFname(final IPerson person, final String profileFname) {
Tuple<String, String> key = null;
final Cache<Tuple<String, String>, UserProfile> profileCache =
getProfileImportExportCache();
if (profileCache != null) {
key = new Tuple<String, String>(person.getUserName(), profileFname);
final UserProfile profile = profileCache.getIfPresent(key);
if (profile != null) {
return profile;
}
}
logger.debug("Getting profile {} for user {}", profileFname, person.getID());
final int userId = person.getID();
final UserProfile userProfile =
jdbcOperations.execute(
new ConnectionCallback<UserProfile>() {
@Override
public UserProfile doInConnection(Connection con)
throws SQLException, DataAccessException {
String query =
"SELECT USER_ID, PROFILE_ID, PROFILE_NAME, DESCRIPTION, "
+ "LAYOUT_ID, STRUCTURE_SS_ID, THEME_SS_ID FROM UP_USER_PROFILE WHERE "
+ "USER_ID=? AND PROFILE_FNAME=?";
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setInt(1, userId);
pstmt.setString(2, profileFname);
try {
logger.debug(
"getUserProfileByFname(): {} userId: {} profileFname: {}",
query,
userId,
profileFname);
ResultSet rs = pstmt.executeQuery();
try {
if (rs.next()) {
int profileId = rs.getInt(2);
String profileName = rs.getString(3);
String profileDesc = rs.getString(4);
int layoutId = rs.getInt(5);
if (rs.wasNull()) {
layoutId = 0;
}
int structSsId = rs.getInt(6);
if (rs.wasNull()) {
// This is probably a data issue and probably an export operation; defer to the system user...
if (!person.equals(getSystemUser())) {
structSsId =
getSystemProfileByFname(profileFname)
.getStructureStylesheetId();
} else {
String msg =
"The system user profile has no structure stylesheet Id.";
throw new IllegalStateException(msg);
}
}
int themeSsId = rs.getInt(7);
if (rs.wasNull()) {
// This is probably a data issue and probably an export operation; defer to the system user...
if (!person.equals(getSystemUser())) {
themeSsId =
getSystemProfileByFname(profileFname)
.getThemeStylesheetId();
} else {
String msg =
"The system user profile has no theme stylesheet Id.";
throw new IllegalStateException(msg);
}
}
UserProfile userProfile =
new UserProfile(
profileId,
profileFname,
profileName,
profileDesc,
layoutId,
structSsId,
themeSsId);
final Locale[] userLocales =
localeStore.getUserLocales(person);
userProfile.setLocaleManager(
new LocaleManager(person, userLocales));
return userProfile;
}
/* Try to copy the template profile. */
logger.debug(
"Copying template profile {} to user {}",
profileFname,
person.getID());
rs.close();
pstmt.close();
pstmt =
con.prepareStatement(
"SELECT USER_DFLT_USR_ID FROM UP_USER WHERE USER_ID=?");
pstmt.setInt(1, person.getID());
rs = pstmt.executeQuery();
if (rs.next()) {
int defaultProfileUser = rs.getInt(1);
if (rs.wasNull()) {
throw new RuntimeException(
"Need to clone the '"
+ profileFname
+ "' profile from template user for "
+ person
+ " but they have no template user");
}
IPerson defaultProfilePerson = new PersonImpl();
defaultProfilePerson.setID(defaultProfileUser);
if (defaultProfilePerson.getID() != person.getID()) {
UserProfile templateProfile =
getUserProfileByFname(
defaultProfilePerson, profileFname);
if (templateProfile != null) {
UserProfile newUserProfile =
new UserProfile(templateProfile);
final Locale[] userLocales =
localeStore.getUserLocales(person);
newUserProfile.setLayoutId(0);
newUserProfile =
addUserProfile(person, newUserProfile);
newUserProfile.setLocaleManager(
new LocaleManager(person, userLocales));
return newUserProfile;
}
}
}
throw new RuntimeException(
"Unable to find User Profile for userId "
+ userId
+ " and profile "
+ profileFname);
} finally {
rs.close();
}
} finally {
pstmt.close();
}
}
});
if (profileCache != null && key != null) {
profileCache.put(key, userProfile);
}
return userProfile;
}
public Hashtable<Integer, UserProfile> getUserProfileList(final IPerson person) {
final int userId = person.getID();
return jdbcOperations.execute(
new ConnectionCallback<Hashtable<Integer, UserProfile>>() {
@Override
public Hashtable<Integer, UserProfile> doInConnection(Connection con)
throws SQLException, DataAccessException {
Hashtable<Integer, UserProfile> pv = new Hashtable<Integer, UserProfile>();
Statement stmt = con.createStatement();
try {
String sQuery =
"SELECT USER_ID, PROFILE_ID, PROFILE_FNAME, PROFILE_NAME, DESCRIPTION, LAYOUT_ID, STRUCTURE_SS_ID, THEME_SS_ID FROM UP_USER_PROFILE WHERE USER_ID="
+ userId;
logger.debug("getUserProfileList(): {}", sQuery);
ResultSet rs = stmt.executeQuery(sQuery);
try {
while (rs.next()) {
int layoutId = rs.getInt(6);
if (rs.wasNull()) {
layoutId = 0;
}
int structSsId = rs.getInt(7);
if (rs.wasNull()) {
structSsId = 0;
}
int themeSsId = rs.getInt(8);
if (rs.wasNull()) {
themeSsId = 0;
}
UserProfile upl =
new UserProfile(
rs.getInt(2),
rs.getString(3),
rs.getString(4),
rs.getString(5),
layoutId,
structSsId,
themeSsId);
pv.put(new Integer(upl.getProfileId()), upl);
}
} finally {
rs.close();
}
} finally {
stmt.close();
}
return pv;
}
});
}
protected abstract Element getStructure(Document doc, LayoutStructure ls);
protected abstract int saveStructure(
Node node, PreparedStatement structStmt, PreparedStatement parmStmt)
throws SQLException;
/**
* Save the user layout.
*
* @param person
* @param profile
* @param layoutXML
* @throws Exception
*/
public void setUserLayout(
final IPerson person,
final IUserProfile profile,
final Document layoutXML,
final boolean channelsAdded) {
final long startTime = System.currentTimeMillis();
final int userId = person.getID();
final int profileId = profile.getProfileId();
// don't try to save null layouts.
if (layoutXML == null) {
logger.error(
"Invalid attempt to save NULL user layout for user "
+ person.getUserName()
+ ". Skipping!");
return;
}
this.transactionOperations.execute(
new TransactionCallback<Object>() {
@Override
public Object doInTransaction(TransactionStatus status) {
return jdbcOperations.execute(
new ConnectionCallback<Object>() {
@Override
public Object doInConnection(Connection con)
throws SQLException, DataAccessException {
int layoutId = 0;
ResultSet rs;
// Eventually we want to be able to just get layoutId from the
// profile, but because of the template user layouts we have to do this for now ...
layoutId = getLayoutID(userId, profileId);
boolean firstLayout = false;
if (layoutId == 0) {
// First personal layout for this user/profile
layoutId = 1;
firstLayout = true;
}
String sql =
"DELETE FROM UP_LAYOUT_PARAM WHERE USER_ID=? AND LAYOUT_ID=?";
PreparedStatement pstmt = con.prepareStatement(sql);
try {
pstmt.clearParameters();
pstmt.setInt(1, userId);
pstmt.setInt(2, layoutId);
logger.debug(sql);
pstmt.executeUpdate();
} finally {
pstmt.close();
}
sql =
"DELETE FROM UP_LAYOUT_STRUCT WHERE USER_ID=? AND LAYOUT_ID=?";
pstmt = con.prepareStatement(sql);
try {
pstmt.clearParameters();
pstmt.setInt(1, userId);
pstmt.setInt(2, layoutId);
logger.debug(sql);
pstmt.executeUpdate();
} finally {
pstmt.close();
}
PreparedStatement structStmt =
con.prepareStatement(
"INSERT INTO UP_LAYOUT_STRUCT "
+ "(USER_ID, LAYOUT_ID, STRUCT_ID, NEXT_STRUCT_ID, CHLD_STRUCT_ID,EXTERNAL_ID,CHAN_ID,NAME,TYPE,HIDDEN,IMMUTABLE,UNREMOVABLE) "
+ "VALUES ("
+ userId
+ ","
+ layoutId
+ ",?,?,?,?,?,?,?,?,?,?)");
PreparedStatement parmStmt =
con.prepareStatement(
"INSERT INTO UP_LAYOUT_PARAM "
+ "(USER_ID, LAYOUT_ID, STRUCT_ID, STRUCT_PARM_NM, STRUCT_PARM_VAL) "
+ "VALUES ("
+ userId
+ ","
+ layoutId
+ ",?,?,?)");
int firstStructId;
try {
firstStructId =
saveStructure(
layoutXML
.getFirstChild()
.getFirstChild(),
structStmt,
parmStmt);
} finally {
structStmt.close();
parmStmt.close();
}
//Check to see if the user has a matching layout
sql =
"SELECT * FROM UP_USER_LAYOUT WHERE USER_ID=? AND LAYOUT_ID=?";
pstmt = con.prepareStatement(sql);
try {
pstmt.clearParameters();
pstmt.setInt(1, userId);
pstmt.setInt(2, layoutId);
logger.debug(sql);
rs = pstmt.executeQuery();
try {
if (!rs.next()) {
// If not, the default user is found and the layout rows from the default user are copied for the current user.
int defaultUserId;
sql =
"SELECT USER_DFLT_USR_ID FROM UP_USER WHERE USER_ID=?";
PreparedStatement pstmt2 =
con.prepareStatement(sql);
try {
pstmt2.clearParameters();
pstmt2.setInt(1, userId);
logger.debug(sql);
ResultSet rs2 = null;
try {
rs2 = pstmt2.executeQuery();
rs2.next();
defaultUserId = rs2.getInt(1);
} finally {
if (rs2 != null) {
rs2.close();
}
}
} finally {
pstmt2.close();
}
// Add to UP_USER_LAYOUT
sql =
"SELECT USER_ID,LAYOUT_ID,LAYOUT_TITLE,INIT_STRUCT_ID FROM UP_USER_LAYOUT WHERE USER_ID=?";
pstmt2 = con.prepareStatement(sql);
try {
pstmt2.clearParameters();
pstmt2.setInt(1, defaultUserId);
logger.debug(sql);
ResultSet rs2 = pstmt2.executeQuery();
try {
if (rs2.next()) {
// There is a row for this user's template user...
sql =
"INSERT INTO UP_USER_LAYOUT (USER_ID, LAYOUT_ID, LAYOUT_TITLE, INIT_STRUCT_ID) VALUES (?,?,?,?)";
PreparedStatement pstmt3 =
con.prepareStatement(sql);
try {
pstmt3.clearParameters();
pstmt3.setInt(1, userId);
pstmt3.setInt(
2,
rs2.getInt(
"LAYOUT_ID"));
pstmt3.setString(
3,
rs2.getString(
"LAYOUT_TITLE"));
pstmt3.setInt(
4,
rs2.getInt(
"INIT_STRUCT_ID"));
logger.debug(sql);
pstmt3.executeUpdate();
} finally {
pstmt3.close();
}
} else {
// We can't rely on the template user, but we still need a row...
sql =
"INSERT INTO UP_USER_LAYOUT (USER_ID, LAYOUT_ID, LAYOUT_TITLE, INIT_STRUCT_ID) VALUES (?,?,?,?)";
PreparedStatement pstmt3 =
con.prepareStatement(sql);
try {
pstmt3.clearParameters();
pstmt3.setInt(1, userId);
pstmt3.setInt(2, layoutId);
pstmt3.setString(
3, "default layout");
pstmt3.setInt(4, 1);
logger.debug(sql);
pstmt3.executeUpdate();
} finally {
pstmt3.close();
}
}
} finally {
rs2.close();
}
} finally {
pstmt2.close();
}
}
} finally {
rs.close();
}
} finally {
pstmt.close();
}
//Update the users layout with the correct inital structure ID
sql =
"UPDATE UP_USER_LAYOUT SET INIT_STRUCT_ID=? WHERE USER_ID=? AND LAYOUT_ID=?";
pstmt = con.prepareStatement(sql);
try {
pstmt.clearParameters();
pstmt.setInt(1, firstStructId);
pstmt.setInt(2, userId);
pstmt.setInt(3, layoutId);
logger.debug(sql);
pstmt.executeUpdate();
} finally {
pstmt.close();
}
// Update the last time the user saw the list of available channels
if (channelsAdded) {
sql =
"UPDATE UP_USER SET LST_CHAN_UPDT_DT=? WHERE USER_ID=?";
pstmt = con.prepareStatement(sql);
try {
pstmt.clearParameters();
pstmt.setDate(
1,
new java.sql.Date(
System.currentTimeMillis()));
pstmt.setInt(2, userId);
logger.debug(sql);
pstmt.executeUpdate();
} finally {
pstmt.close();
}
}
if (firstLayout) {
sql =
"UPDATE UP_USER_PROFILE SET LAYOUT_ID=1 WHERE USER_ID=? AND PROFILE_ID=?";
pstmt = con.prepareStatement(sql);
try {
pstmt.clearParameters();
pstmt.setInt(1, userId);
pstmt.setInt(2, profileId);
logger.debug(sql);
pstmt.executeUpdate();
} finally {
pstmt.close();
}
}
return null;
}
});
}
});
if (logger.isDebugEnabled()) {
long stopTime = System.currentTimeMillis();
long timeTook = stopTime - startTime;
logger.debug(
"setUserLayout(): Layout document for user {} took {} milliseconds to save",
userId,
timeTook);
}
}
public void updateUserProfile(final IPerson person, final IUserProfile profile) {
final int userId = person.getID();
this.transactionOperations.execute(
new TransactionCallback<Object>() {
@Override
public Object doInTransaction(TransactionStatus status) {
return jdbcOperations.execute(
new ConnectionCallback<Object>() {
@Override
public Object doInConnection(Connection con)
throws SQLException, DataAccessException {
String query =
"UPDATE UP_USER_PROFILE SET LAYOUT_ID=?,THEME_SS_ID=?,STRUCTURE_SS_ID=?,"
+ "DESCRIPTION=?,PROFILE_NAME=?, PROFILE_FNAME=? WHERE USER_ID=? AND PROFILE_ID=?";
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setInt(1, profile.getLayoutId());
pstmt.setInt(2, profile.getThemeStylesheetId());
pstmt.setInt(3, profile.getStructureStylesheetId());
pstmt.setString(4, profile.getProfileDescription());
pstmt.setString(5, profile.getProfileName());
pstmt.setString(6, profile.getProfileFname());
pstmt.setInt(7, userId);
pstmt.setInt(8, profile.getProfileId());
try {
if (logger.isDebugEnabled())
logger.debug(
"updateUserProfile() : {} layout_id: {} theme_ss_id: {} structure_ss_id: {} description: {} name: {} user_id: {} fname: {}",
query,
profile.getLayoutId(),
profile.getThemeStylesheetId(),
profile.getStructureStylesheetId(),
profile.getProfileDescription(),
profile.getProfileName(),
userId,
profile.getProfileFname());
pstmt.execute();
} finally {
pstmt.close();
}
return null;
}
});
}
});
}
public IUserProfile getSystemProfileByFname(String profileFname) {
IUserProfile up = this.getUserProfileByFname(this.getSystemUser(), profileFname);
up.setSystemProfile(true);
return up;
}
public Hashtable getSystemProfileList() {
Hashtable pl = this.getUserProfileList(this.getSystemUser());
for (Enumeration e = pl.elements(); e.hasMoreElements(); ) {
IUserProfile up = (IUserProfile) e.nextElement();
up.setSystemProfile(true);
}
return pl;
}
private static class SystemUser implements IPerson {
private static final long serialVersionUID = 1L;
private final int systemUserId;
public SystemUser(int systemUserId) {
this.systemUserId = systemUserId;
}
public void setID(int sID) {}
public int getID() {
return this.systemUserId;
}
public String getUserName() {
return null;
}
public void setUserName(String userName) {}
public void setFullName(String sFullName) {}
public String getFullName() {
return "uPortal System Account";
}
public Object getAttribute(String key) {
return null;
}
public Object[] getAttributeValues(String key) {
return null;
}
public Map<String, List<Object>> getAttributeMap() {
return null;
}
public void setAttribute(String key, Object value) {}
public void setAttribute(String key, List<Object> values) {}
public void setAttributes(Map attrs) {}
public boolean isGuest() {
return (false);
}
public ISecurityContext getSecurityContext() {
return (null);
}
public void setSecurityContext(ISecurityContext context) {}
public EntityIdentifier getEntityIdentifier() {
return null;
}
public String getName() {
return null;
}
}
/**
* Returns the current layout ID for the user and profile. If the profile doesn't exist or the
* layout_id field is null 0 is returned.
*
* @param userId The userId for the profile
* @param profileId The profileId for the profile
* @return The layout_id field or 0 if it does not exist or is null
* @throws SQLException
*/
protected int getLayoutID(final int userId, final int profileId) throws SQLException {
return jdbcOperations.execute(
new ConnectionCallback<Integer>() {
@Override
public Integer doInConnection(Connection con)
throws SQLException, DataAccessException {
String query =
"SELECT LAYOUT_ID "
+ "FROM UP_USER_PROFILE "
+ "WHERE USER_ID=? AND PROFILE_ID=?";
int layoutId = 0;
PreparedStatement pstmt = con.prepareStatement(query);
final int u = userId;
final int p = profileId;
logger.debug("getLayoutID(userId={}, profileId={} ): {}", u, p, query);
pstmt.setInt(1, u);
pstmt.setInt(2, p);
try {
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
layoutId = rs.getInt(1);
if (rs.wasNull()) {
layoutId = 0;
}
}
} finally {
pstmt.close();
}
return layoutId;
}
});
}
/* (non-Javadoc)
* @see org.apereo.portal.layout.IUserLayoutStore#importLayout(org.dom4j.Element)
*/
public abstract void importLayout(org.dom4j.Element layout);
}