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 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 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);
}
}
public List<String> getNames() throws SystemException {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(_SQL_SELECT_NAMES);
resultSet = preparedStatement.executeQuery();
List<String> list = new ArrayList<String>();
while (resultSet.next()) {
list.add(resultSet.getString(1));
}
return list;
} catch (SQLException sqle) {
throw processException(sqle);
} finally {
DataAccess.cleanUp(connection, preparedStatement, resultSet);
}
}
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 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 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 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 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 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 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);
}
}
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 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 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 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 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 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 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 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 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);
}
}
public void rename(String oldName, String newName) throws SystemException {
CounterRegister counterRegister = getCounterRegister(oldName);
synchronized (counterRegister) {
if (_counterRegisterMap.containsKey(newName)) {
throw new SystemException("Cannot rename " + oldName + " to " + newName);
}
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(_SQL_UPDATE_NAME_BY_NAME);
preparedStatement.setString(1, newName);
preparedStatement.setString(2, oldName);
preparedStatement.executeUpdate();
} catch (ObjectNotFoundException onfe) {
} catch (Exception e) {
throw processException(e);
} finally {
DataAccess.cleanUp(connection, preparedStatement);
}
counterRegister.setName(newName);
_counterRegisterMap.put(newName, counterRegister);
_counterRegisterMap.remove(oldName);
}
}
protected void addResourcePermission(
long resourcePermissionId,
long companyId,
String name,
int scope,
String primKey,
long roleId,
long actionIds)
throws Exception {
PreparedStatement ps = null;
try {
ps =
connection.prepareStatement(
"insert into ResourcePermission (resourcePermissionId, "
+ "companyId, name, scope, primKey, roleId, actionIds) "
+ "values (?, ?, ?, ?, ?, ?, ?)");
ps.setLong(1, resourcePermissionId);
ps.setLong(2, companyId);
ps.setString(3, name);
ps.setInt(4, scope);
ps.setString(5, primKey);
ps.setLong(6, roleId);
ps.setLong(7, actionIds);
ps.executeUpdate();
} finally {
DataAccess.cleanUp(ps);
}
}
protected boolean tableHasColumn(String tableName, String columnName) throws Exception {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = connection.prepareStatement("select * from " + tableName);
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
String curColumnName = rsmd.getColumnName(i + 1);
if (StringUtil.equalsIgnoreCase(curColumnName, columnName)) {
return true;
}
}
} catch (Exception e) {
_log.error(e, e);
} finally {
DataAccess.cleanUp(ps, rs);
}
return false;
}
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 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 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 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 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);
}
}