<%@ page import="com.atlassian.spring.container.ContainerManager,
com.atlassian.confluence.setup.BootstrapManager,
javax.naming.InitialContext,
javax.naming.NamingException,
javax.sql.DataSource,
java.sql.*,
java.util.Properties" %>
<%@ page import="com.atlassian.confluence.util.GeneralUtil"%>
<%@ page import="com.atlassian.confluence.util.HtmlUtil"%>
<%@ page contentType="text/html; charset=UTF-8" %>
Atlassian Database Check Utility
<%!
private void updateDatabase(Connection connection)
{
Statement statement = null;
try
{
statement = connection.createStatement();
statement.execute("update os_user set USERNAME = lower( USERNAME )");
statement.execute("update ATTACHMENTS set CREATOR = lower( CREATOR ), LASTMODIFIER = lower( LASTMODIFIER)");
statement.execute("update CONTENT set USERNAME = lower( USERNAME), CREATOR = lower( CREATOR ), LASTMODIFIER = lower( LASTMODIFIER)");
statement.execute("update CONTENTLOCK set CREATOR = lower( CREATOR ), LASTMODIFIER = lower( LASTMODIFIER)");
statement.execute("update EMAILTEMPLATES set USERNAME = lower( USERNAME), CREATOR = lower( CREATOR ), LASTMODIFIER = lower( LASTMODIFIER)");
statement.execute("update EXTRNLNKS set CREATOR = lower( CREATOR ), LASTMODIFIER = lower( LASTMODIFIER)");
statement.execute("update LINKS set CREATOR = lower( CREATOR ), LASTMODIFIER = lower( LASTMODIFIER)");
statement.execute("update NOTIFICATIONS set USERNAME = lower( USERNAME), CREATOR = lower( CREATOR ), LASTMODIFIER = lower( LASTMODIFIER)");
statement.execute("update PAGETEMPLATES set CREATOR = lower( CREATOR ), LASTMODIFIER = lower( LASTMODIFIER)");
statement.execute("update SPACEPERMISSIONS set PERMUSERNAME = lower( PERMUSERNAME), CREATOR = lower( CREATOR ), LASTMODIFIER = lower( LASTMODIFIER)");
statement.execute("update SPACES set CREATOR = lower( CREATOR ), LASTMODIFIER = lower( LASTMODIFIER)");
statement.execute("update TRACKBACKLINKS set CREATOR = lower( CREATOR ), LASTMODIFIER = lower( LASTMODIFIER)");
if (!connection.getAutoCommit())
connection.commit();
}
catch (SQLException e)
{
e.printStackTrace();
}
finally
{
if (statement != null)
try
{
statement.close();
}
catch (Exception e)
{
}
}
}
public String hasDuplicateUsers(Connection connection)
{
String errorMessage = "";
PreparedStatement statement = null;
try
{
statement = connection.prepareStatement("SELECT lower(username), COUNT(lower(username)) AS NumOccurrences " +
"FROM os_user " +
"GROUP BY lower(username) " +
"HAVING ( COUNT(lower(username)) > 1 )");
ResultSet rs = statement.executeQuery();
// If there are no duplicates
if (rs.next())
{
// Show error message
errorMessage = "" +
"
Multiple users with the same username found in database.
" +
"You will need to to delete the duplicates in order to continue with the upgrade.
" +
"Following users have duplicate entries:
";
String username = rs.getString(1);
errorMessage = errorMessage + HtmlUtil.htmlEncode(username) + "
";
while (rs.next())
{
username = rs.getString(1);
errorMessage = errorMessage + HtmlUtil.htmlEncode(username) + "
";
}
errorMessage = errorMessage + "
" +
"
MixedCased usernames found in the database.
" +
"Perform the database upgrade by clicken the button below.
";
errorMessage = errorMessage + "
\n" +
"\n" +
"
";
String username = rs.getString(1);
errorMessage = errorMessage + HtmlUtil.htmlEncode(username) + "
";
while (rs.next())
{
username = rs.getString(1);
errorMessage = errorMessage + HtmlUtil.htmlEncode(username) + "
";
}
errorMessage = errorMessage + "
Your database contains no invalid usernames.
" +
"Note: If you have a case insensitive Database (e.g. MySQL) this test won't work and you should perform the upgrade to be sure you data is valid.
" +
"
Click here ";
}
}
catch (SQLException e)
{
e.printStackTrace();
}
finally
{
if (statement != null)
try
{
statement.close();
}
catch (Exception e)
{
}
}
return errorMessage;
}
%>
<%!
private Connection getDatabaseConnection()
{
BootstrapManager bootstrapManager = (BootstrapManager) ContainerManager.getInstance().getContainerContext().getComponent("bootstrapManager");
Properties hibernateConfig = bootstrapManager.getHibernateProperties();
Connection connection = null;
if (hibernateConfig.containsKey("hibernate.connection.datasource"))
{
// Datasource connection
String datasource = hibernateConfig.getProperty("hibernate.connection.datasource");
try
{
InitialContext ctx = new InitialContext();
DataSource dsrc = (DataSource) ctx.lookup(datasource);
connection = dsrc.getConnection();
}
catch (SQLException e)
{
e.printStackTrace();
}
catch (NamingException e)
{
e.printStackTrace();
}
}
else
{
//JDBC Connection
String driverStr = hibernateConfig.getProperty("hibernate.connection.driver_class");
String jdbcURL = hibernateConfig.getProperty("hibernate.connection.url");
String username = hibernateConfig.getProperty("hibernate.connection.username");
String password = hibernateConfig.getProperty("hibernate.connection.password");
try
{
connection = DriverManager.getConnection(jdbcURL, username, password);
}
catch (SQLException e)
{
e.printStackTrace();
}
}
return connection;
}
%>
Atlassian Confluence
Database User Upgrade
Due to a bug in Confluence, usernames were case sensitive. This might introduce problems when a username is used
twice (e.g. user and User).
This JSP will lowercase all users in you database to eliminate those problems.
<%
String operation = request.getParameter("operation");
//String operation = request.getParameter("operation");
Connection connection = getDatabaseConnection();
try
{
if (operation == null)
{
out.print(hasDuplicateUsers(connection));
out.print(hasMixedCaseEntries(connection));
}
else
{
updateDatabase(connection);
out.println("
Database Update successful");
}
}
finally
{
try
{
connection.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
%>