protected void updatePortalPreferences() throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps =
con.prepareStatement(
"select ownerId, ownerType, preferences from "
+ "PortletPreferences where portletId = ?");
ps.setString(1, PortletKeys.LIFERAY_PORTAL);
rs = ps.executeQuery();
while (rs.next()) {
long ownerId = rs.getLong("ownerId");
int ownerType = rs.getInt("ownerType");
String preferences = rs.getString("preferences");
addPortalPreferences(ownerId, ownerType, preferences);
}
runSQL(
"delete from PortletPreferences where portletId = '" + PortletKeys.LIFERAY_PORTAL + "'");
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected long getMessageIdsCount() throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getConnection();
StringBundler sb = new StringBundler(7);
sb.append("select count(*) from ");
sb.append("MBMessage childMessage ");
sb.append("inner join MBMessage parentMessage on ");
sb.append("childMessage.parentMessageId = ");
sb.append("parentMessage.messageId where ");
sb.append("parentMessage.categoryId != childMessage.categoryId ");
sb.append("or parentMessage.threadId != childMessage.threadId");
String sql = sb.toString();
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
return rs.getLong(1);
}
return 0;
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected void verifyURLTitle() throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps =
con.prepareStatement(
"select distinct groupId, articleId, urlTitle from " + "JournalArticle");
rs = ps.executeQuery();
while (rs.next()) {
long groupId = rs.getLong("groupId");
String articleId = rs.getString("articleId");
String urlTitle = GetterUtil.getString(rs.getString("urlTitle"));
updateURLTitle(groupId, articleId, urlTitle);
}
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected long getGroupId(long questionId) throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps = con.prepareStatement("select groupId from PollsQuestion where questionId = ?");
ps.setLong(1, questionId);
rs = ps.executeQuery();
if (rs.next()) {
return rs.getLong("groupId");
}
if (_log.isDebugEnabled()) {
_log.debug("Unable to find question " + questionId);
}
return 0;
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected void addPortletPreferences(
long ownerId, int ownerType, long plid, String portletId, String preferences)
throws Exception {
Connection con = null;
PreparedStatement ps = null;
try {
con = DataAccess.getConnection();
ps =
con.prepareStatement(
"insert into PortletPreferences (portletPreferencesId, "
+ "ownerId, ownerType, plid, portletId, preferences) "
+ "values (?, ?, ?, ?, ?, ?)");
ps.setLong(1, increment());
ps.setLong(2, ownerId);
ps.setInt(3, ownerType);
ps.setLong(4, plid);
ps.setString(5, portletId);
ps.setString(6, preferences);
ps.executeUpdate();
} finally {
DataAccess.cleanUp(con, ps);
}
}
protected void updateFileEntryTitle(long fileEntryId, String title, String version)
throws Exception {
Connection con = null;
PreparedStatement ps = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps = con.prepareStatement("update DLFileEntry set title = ? where fileEntryId = ?");
ps.setString(1, title);
ps.setLong(2, fileEntryId);
ps.executeUpdate();
ps =
con.prepareStatement(
"update DLFileVersion set title = ? where fileEntryId = " + "? and version = ?");
ps.setString(1, title);
ps.setLong(2, fileEntryId);
ps.setString(3, version);
ps.executeUpdate();
} finally {
DataAccess.cleanUp(con, ps);
}
}
protected void updateFileVersionFileNames() throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps = con.prepareStatement("select fileVersionId, extension, title from DLFileVersion");
rs = ps.executeQuery();
while (rs.next()) {
long fileVersionId = rs.getLong("fileVersionId");
String extension = GetterUtil.getString(rs.getString("extension"));
String title = GetterUtil.getString(rs.getString("title"));
String fileName = DLUtil.getSanitizedFileName(title, extension);
updateFileVersionFileName(fileVersionId, fileName);
}
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected void addLayoutSetPrototype(long layoutSetPrototypeId, long companyId, String name)
throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getConnection();
ps =
con.prepareStatement(
"insert into LayoutSetPrototype (layoutSetPrototypeId, "
+ "companyId, name, description, active_) values (?, ?, ?, "
+ "?, ?)");
ps.setLong(1, layoutSetPrototypeId);
ps.setLong(2, companyId);
ps.setString(3, getNameXML(name));
ps.setString(4, name);
ps.setBoolean(5, true);
ps.execute();
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected void addResourcePermission(
long resourcePermissionId, long companyId, String name, long primKey, long ownerId)
throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getConnection();
ps =
con.prepareStatement(
"insert into ResourcePermission (resourcePermissionId, "
+ "companyId, name, scope, primKey, roleId, ownerId, "
+ "actionIds) values (?, ?, ?, ?, ?, ?, ?, ?)");
ps.setLong(1, resourcePermissionId);
ps.setLong(2, companyId);
ps.setString(3, name);
ps.setLong(4, ResourceConstants.SCOPE_INDIVIDUAL);
ps.setString(5, String.valueOf(primKey));
ps.setLong(6, getRoleId(companyId, RoleConstants.OWNER));
ps.setLong(7, ownerId);
ps.setLong(8, getActionIds(name));
ps.executeUpdate();
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
private String _findUniqueUrlTitle(Connection con, String urlTitle) throws SQLException {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = con.prepareStatement("select count(*) from KBFolder where KBFolder.urlTitle like ?");
ps.setString(1, urlTitle + "%");
rs = ps.executeQuery();
if (!rs.next()) {
return urlTitle;
}
int kbFolderCount = rs.getInt(1);
if (kbFolderCount == 0) {
return urlTitle;
}
return null;
} finally {
DataAccess.cleanUp(ps);
DataAccess.cleanUp(rs);
}
}
private Map<Long, String> _getInitialUrlTitles(Connection con) throws SQLException {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps =
con.prepareStatement(
"select kbFolderId, name from KBFolder where "
+ "(KBFolder.urlTitle is null) or (KBFolder.urlTitle = '')");
rs = ps.executeQuery();
Map<Long, String> urlTitles = new HashMap<>();
while (rs.next()) {
long kbFolderId = rs.getLong(1);
String name = rs.getString(2);
String urlTitle = _getUrlTitle(kbFolderId, name);
urlTitles.put(kbFolderId, urlTitle);
}
return urlTitles;
} finally {
DataAccess.cleanUp(ps);
DataAccess.cleanUp(rs);
}
}
protected String getUserName(long userId) throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps =
con.prepareStatement(
"select firstName, middleName, lastName from User_ where " + "userId = ?");
ps.setLong(1, userId);
rs = ps.executeQuery();
if (rs.next()) {
String firstName = rs.getString("firstName");
String middleName = rs.getString("middleName");
String lastName = rs.getString("lastName");
FullNameGenerator fullNameGenerator = FullNameGeneratorFactory.getInstance();
return fullNameGenerator.getFullName(firstName, middleName, lastName);
}
return StringPool.BLANK;
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
private static long _getResourceCodesCount() throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getConnection();
ps = con.prepareStatement("select count(*) from ResourceCode");
rs = ps.executeQuery();
if (rs.next()) {
int count = rs.getInt(1);
return count;
}
return 0;
} catch (Exception e) {
return 0;
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected void deleteConflictingIGPermissions(String igResourceName, String dlResourceName)
throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getConnection();
DatabaseMetaData databaseMetaData = con.getMetaData();
boolean supportsBatchUpdates = databaseMetaData.supportsBatchUpdates();
ps =
con.prepareStatement(
"select companyId, scope, primKey, roleId from "
+ "ResourcePermission where name = ?");
ps.setString(1, igResourceName);
rs = ps.executeQuery();
ps =
con.prepareStatement(
"delete from ResourcePermission where name = ? and "
+ "companyId = ? and scope = ? and primKey = ? and "
+ "roleId = ?");
int count = 0;
while (rs.next()) {
ps.setString(1, dlResourceName);
ps.setLong(2, rs.getLong("companyId"));
ps.setLong(3, rs.getLong("scope"));
ps.setLong(4, rs.getLong("primKey"));
ps.setLong(5, rs.getLong("roleId"));
if (supportsBatchUpdates) {
ps.addBatch();
if (count == PropsValues.HIBERNATE_JDBC_BATCH_SIZE) {
ps.executeBatch();
count = 0;
} else {
count++;
}
} else {
ps.executeUpdate();
}
}
if (supportsBatchUpdates && (count > 0)) {
ps.executeBatch();
}
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected long getCompanyGroupId(long companyId) throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps =
con.prepareStatement(
"select groupId from Group_ where classNameId = ? and " + "classPK = ?");
ps.setLong(1, PortalUtil.getClassNameId(Company.class.getName()));
ps.setLong(2, companyId);
rs = ps.executeQuery();
if (rs.next()) {
return rs.getLong("groupId");
}
return 0;
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected long getActionIds(String name) throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getConnection();
ps =
con.prepareStatement(
"select sum(bitwiseValue) as actionIds from ResourceAction " + "where name = ?");
ps.setString(1, name);
rs = ps.executeQuery();
if (rs.next()) {
return rs.getLong("actionIds");
}
} finally {
DataAccess.cleanUp(con, ps, rs);
}
return 0;
}
protected void updateFileVersions() throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps = con.prepareStatement("select fileEntryId, folderId from DLFileEntry");
rs = ps.executeQuery();
while (rs.next()) {
long fileEntryId = rs.getLong("fileEntryId");
long folderId = rs.getLong("folderId");
StringBundler sb = new StringBundler(4);
sb.append("update DLFileVersion set folderId = ");
sb.append(folderId);
sb.append(" where fileEntryId = ");
sb.append(fileEntryId);
runSQL(sb.toString());
}
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected long getGroupId(long companyId, long classNameId, long classPK) throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
long groupId = 0;
try {
con = DataAccess.getConnection();
ps =
con.prepareStatement(
"select groupId from Group_ where (companyId = ?) and "
+ "(classNameId = ?) and (classPK = ?)");
ps.setLong(1, companyId);
ps.setLong(2, classNameId);
ps.setLong(3, classPK);
rs = ps.executeQuery();
if (rs.next()) {
groupId = rs.getLong("groupId");
}
} finally {
DataAccess.cleanUp(con, ps, rs);
}
return groupId;
}
protected void updateFileEntryTypeNamesAndDescriptions() throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps = con.prepareStatement("select companyId, groupId from Group_ where classNameId = ?");
long classNameId = PortalUtil.getClassNameId(Company.class);
ps.setLong(1, classNameId);
rs = ps.executeQuery();
while (rs.next()) {
long companyId = rs.getLong(1);
long groupId = rs.getLong(2);
updateFileEntryTypeNamesAndDescriptions(companyId, groupId);
}
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected long getRoleId(long companyId, String name) throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getConnection();
ps =
con.prepareStatement(
"select roleId from Role_ where (companyId = ?) and (name = " + "?)");
ps.setLong(1, companyId);
ps.setString(2, name);
rs = ps.executeQuery();
if (rs.next()) {
return rs.getLong("roleId");
}
} finally {
DataAccess.cleanUp(con, ps, rs);
}
return 0;
}
protected boolean hasFileEntry(long groupId, long folderId, String fileName) throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps =
con.prepareStatement(
"select count(*) from DLFileEntry where groupId = ? and "
+ "folderId = ? and fileName = ?");
ps.setLong(1, groupId);
ps.setLong(2, folderId);
ps.setString(3, fileName);
rs = ps.executeQuery();
while (rs.next()) {
int count = rs.getInt(1);
if (count > 0) {
return true;
}
}
return false;
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected void updateLayout(long oldGroupId, long newGroupId, boolean privateLayout)
throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getConnection();
ps =
con.prepareStatement(
"update Layout set groupId = ?, privateLayout = ? where "
+ "(groupId = ?) and (privateLayout = ?)");
ps.setLong(1, newGroupId);
ps.setBoolean(2, true);
ps.setLong(3, oldGroupId);
ps.setBoolean(4, privateLayout);
ps.executeUpdate();
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected void updateVotes() throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps = con.prepareStatement("select voteId, questionId from PollsVote");
rs = ps.executeQuery();
while (rs.next()) {
long voteId = rs.getLong("voteId");
long questionId = rs.getLong("questionId");
long groupId = getGroupId(questionId);
if (groupId > 0) {
runSQL("update PollsVote set groupId = " + groupId + " where voteId = " + voteId);
}
}
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected void updateUserGroup(long userGroupId, boolean privateLayout, long layoutSetPrototypeId)
throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getConnection();
String layoutSetPrototypeIdColumnName = null;
if (privateLayout) {
layoutSetPrototypeIdColumnName = "privateLayoutSetPrototypeId";
} else {
layoutSetPrototypeIdColumnName = "publicLayoutSetPrototypeId";
}
ps =
con.prepareStatement(
"update UserGroup set "
+ layoutSetPrototypeIdColumnName
+ " = ? where userGroupId = ?");
ps.setLong(1, layoutSetPrototypeId);
ps.setLong(2, userGroupId);
ps.executeUpdate();
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected long getPortletPreferencesId(long ownerId, int ownerType, long plid, String portletId)
throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getConnection();
ps =
con.prepareStatement(
"select portletPreferencesId from PortletPreferences where "
+ "ownerId = ? and ownerType = ? and plid = ? and "
+ "portletId = ?");
ps.setLong(1, ownerId);
ps.setInt(2, ownerType);
ps.setLong(3, plid);
ps.setString(4, portletId);
rs = ps.executeQuery();
if (rs.next()) {
return rs.getLong("portletPreferencesId");
}
} finally {
DataAccess.cleanUp(con, ps, rs);
}
return 0;
}
protected void updatePortletPreference(
long portletPreferencesId, String newPortletId, String newPreferences) throws Exception {
Connection con = null;
PreparedStatement ps = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps =
con.prepareStatement(
"update PortletPreferences set preferences = ?, "
+ "portletId = ? where portletPreferencesId = "
+ portletPreferencesId);
ps.setString(1, newPreferences);
ps.setString(2, newPortletId);
ps.executeUpdate();
} catch (SQLException sqle) {
if (_log.isWarnEnabled()) {
_log.warn(sqle, sqle);
}
} finally {
DataAccess.cleanUp(con, ps);
}
}
protected void verifySearch() throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps =
con.prepareStatement(
"select groupId, portletId from JournalContentSearch group "
+ "by groupId, portletId having count(groupId) > 1 and "
+ "count(portletId) > 1");
rs = ps.executeQuery();
while (rs.next()) {
long groupId = rs.getLong("groupId");
String portletId = rs.getString("portletId");
verifyContentSearch(groupId, portletId);
}
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected long getCategoryId(long companyId, String type) throws Exception {
if (Validator.isNull(type)) {
return 0;
}
long groupId = getCompanyGroupId(companyId);
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps =
con.prepareStatement(
"select categoryId from AssetCategory where groupId = " + groupId + " and name = ?");
ps.setString(1, type);
rs = ps.executeQuery();
while (rs.next()) {
return rs.getLong("categoryId");
}
return 0;
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
protected void updateURLTitle(long groupId, String articleId, String urlTitle) throws Exception {
String normalizedURLTitle = FriendlyURLNormalizerUtil.normalize(urlTitle, _friendlyURLPattern);
if (urlTitle.equals(normalizedURLTitle)) {
return;
}
normalizedURLTitle =
JournalArticleLocalServiceUtil.getUniqueUrlTitle(groupId, articleId, normalizedURLTitle);
Connection con = null;
PreparedStatement ps = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps = con.prepareStatement("update JournalArticle set urlTitle = ? where urlTitle = ?");
ps.setString(1, normalizedURLTitle);
ps.setString(2, urlTitle);
ps.executeUpdate();
} finally {
DataAccess.cleanUp(con, ps);
}
}
protected void upgradeMicroblogActivities() throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getUpgradeOptimizedConnection();
ps =
con.prepareStatement(
"select activityId, extraData from SocialActivity where " + "classNameId = ?");
ps.setLong(1, PortalUtil.getClassNameId(MicroblogsEntry.class));
rs = ps.executeQuery();
while (rs.next()) {
long activityId = rs.getLong("activityId");
String extraData = rs.getString("extraData");
JSONObject extraDataJSONObject = JSONFactoryUtil.createJSONObject(extraData);
long parentMicroblogsEntryId = extraDataJSONObject.getLong("receiverMicroblogsEntryId");
extraDataJSONObject.put("parentMicroblogsEntryId", parentMicroblogsEntryId);
extraDataJSONObject.remove("receiverMicroblogsEntryId");
updateSocialActivity(activityId, extraDataJSONObject);
}
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
