/*
* Copyright (c) 2005-2010, WSO2 Inc. (http://www.wso2.org) All Rights Reserved.
*
* WSO2 Inc. 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
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* 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.wso2.carbon.adminconsole.core.connections;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.wso2.carbon.adminconsole.core.description.DatabaseInfo;
import org.wso2.carbon.adminconsole.core.description.DatabaseInstanceInfo;
import org.wso2.carbon.adminconsole.core.description.DatabaseUserInfo;
import org.wso2.carbon.adminconsole.core.multitenancy.TenantDBData;
import org.wso2.carbon.registry.core.exceptions.RegistryException;
import javax.xml.stream.XMLStreamException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class ConnectionDAO {
private PreparedStatement ps;
private TenantDBData tenantData;
public static final String MYSQL_SYSTEM_DB = "mysql";
public static final String YES = "Y";
public static final String ANY = "ANY";
private static final Log log = LogFactory.getLog(ConnectionDAO.class);
public ConnectionDAO(int tenantId) throws RegistryException {
try {
this.tenantData = new TenantDBData(tenantId);
} catch (RegistryException e) {
String msg = "Cannot Load Tenant Data";
throw new RegistryException(msg, e);
}
}
public String createDatabase(DatabaseInfo dbInfo,
String tenantDomain) throws RegistryException, SQLException, XMLStreamException, ClassNotFoundException {
String dbName = dbInfo.getDbName();
String fullyQualifiedDbName =
(tenantDomain != null) ? (tenantDomain + "_" + dbName) : dbName;
try {
if (log.isDebugEnabled()) {
log.debug("Creating Database For The Tenant " + tenantDomain);
}
DatabaseInstanceInfo dbInstanceInfo = tenantData.getDatabaseInstanceInfo(
dbInfo.getInstanceName());
String sqlQuery = "CREATE DATABASE " + fullyQualifiedDbName;
ps = ConnectionHandler.getConnection(
dbInstanceInfo.getInstanceUrl(), dbInstanceInfo.getUsername(),
dbInstanceInfo.getPassword()).prepareStatement(sqlQuery);
ps.execute();
if (log.isDebugEnabled()) {
log.debug("Database Created For The Tenant " + tenantDomain);
}
return dbInstanceInfo.getInstanceUrl() + "/" + dbName;
} catch (SQLException e) {
String msg = "Unable To Create Database For Tenant " + tenantDomain;
throw new SQLException(msg, e);
} catch (RegistryException e) {
String msg = "Cannot Retrieve Instance List";
throw new RegistryException(msg, e);
} catch (XMLStreamException e) {
throw new XMLStreamException(e);
} catch (ClassNotFoundException e) {
String msg = "Driver class not found";
log.error(msg, e);
throw new ClassNotFoundException(msg, e);
} finally {
ConnectionHandler.closeConnection();
}
}
public boolean createDatabaseUser(DatabaseUserInfo userInfo) throws Exception {
try {
if (log.isDebugEnabled()) {
log.debug("Creating A Database User For The Tenant");
}
DatabaseInstanceInfo instanceInfo = tenantData.getDatabaseInstanceInfo(
userInfo.getInstanceName());
if (instanceInfo != null) {
String createUserQuery = "CREATE USER '" + userInfo.getUsername() + "'@'" +
this.extractHostName(instanceInfo.getInstanceUrl()) + "' IDENTIFIED BY '" +
userInfo.getPassword() + "'";
String grantPrivilegesQuery = "GRANT ALL PRIVILEGES ON *.* TO '" +
userInfo.getUsername() + "'@'" +
this.extractHostName(instanceInfo.getInstanceUrl()) + "' IDENTIFIED BY '" +
userInfo.getPassword() + "'";
String systemDbUrl = processJdbcUrl(instanceInfo.getInstanceUrl(), MYSQL_SYSTEM_DB);
ps = ConnectionHandler.getConnection(
systemDbUrl,
instanceInfo.getUsername(),
instanceInfo.getPassword()).prepareStatement(createUserQuery);
ps.execute();
ps = ConnectionHandler.getConnection(
systemDbUrl,
instanceInfo.getUsername(),
instanceInfo.getPassword()).prepareStatement(grantPrivilegesQuery);
ps.execute();
}
if (log.isDebugEnabled()) {
log.debug("User Has Been Created For The Database");
}
return true;
} catch (SQLException e) {
throw new SQLException(e);
} catch (RegistryException e) {
String msg = "Error While Retrieving Resource From The Registry";
throw new RegistryException(msg, e);
} finally {
if (ps != null) {
ps.close();
}
ConnectionHandler.closeConnection();
}
}
private String processJdbcUrl(String url, String dbName) {
if (url != null && !"".equals(url)) {
return url.endsWith("/") ? (url + dbName) : (url + "/" + dbName);
}
return "";
}
private String extractHostName(String url) {
if (url != null && !"".equals(url)) {
return url.split("//")[1].split(":")[0].split("/")[0];
}
return "";
}
public void dropDatabase(String instanceName, String dbName) throws SQLException,
XMLStreamException, ClassNotFoundException, RegistryException {
try {
if (log.isDebugEnabled()) {
log.debug("Dropping Database" + dbName);
}
DatabaseInstanceInfo instanceInfo = tenantData.getDatabaseInstanceInfo(instanceName);
DatabaseUserInfo userInfo = tenantData.getDatabaseUserInfo(instanceName, dbName);
String dropDatabaseQuery = "DROP DATABASE " + dbName;
String dropUserQuery = "DROP USER '" + userInfo.getUsername() + "'@'" +
this.extractHostName(instanceInfo.getInstanceUrl()) + "'";
ps = ConnectionHandler.getConnection(
instanceInfo.getInstanceUrl(),
instanceInfo.getUsername(),
instanceInfo.getPassword()).prepareStatement(dropDatabaseQuery);
ps.execute();
if (log.isDebugEnabled()) {
log.debug("Database " + dbName + "Dropped");
}
if (log.isDebugEnabled()) {
log.debug("Dropping Database User " + userInfo.getUsername());
}
ps = ConnectionHandler.getConnection(
instanceInfo.getInstanceUrl(),
instanceInfo.getUsername(),
instanceInfo.getPassword()).prepareStatement(dropUserQuery);
ps.execute();
if (log.isDebugEnabled()) {
log.debug("Database User " + userInfo.getUsername() + "Dropped");
}
} catch (SQLException e) {
throw new SQLException(e);
} catch (RegistryException e) {
throw new RegistryException("Error while attempting to read the database information " +
"from the registry", e);
} catch (XMLStreamException e) {
throw new XMLStreamException(e);
} catch (ClassNotFoundException e) {
throw new ClassNotFoundException("Driver class not found", e);
} finally {
if (ps != null) {
ps.close();
}
ConnectionHandler.closeConnection();
}
}
public List<DatabaseUserInfo> getUsers(String dbName) throws RegistryException, SQLException,
XMLStreamException, ClassNotFoundException {
List<DatabaseUserInfo> users = new ArrayList<DatabaseUserInfo>();
try {
DatabaseInstanceInfo instanceInfo = tenantData.getDatabaseInstanceInfo(dbName);
String sqlQuery = "SELECT Host,User FROM db WHERE Db=?";
String dbUrl = processJdbcUrl(instanceInfo.getInstanceUrl(), MYSQL_SYSTEM_DB);
ps = ConnectionHandler.getConnection(dbUrl,
instanceInfo.getUsername(),
instanceInfo.getPassword()).prepareStatement(sqlQuery);
ps.setString(1, dbName);
ResultSet rs = ps.executeQuery();
if (rs != null) {
while (rs.next()) {
DatabaseUserInfo user = new DatabaseUserInfo();
user.setInstanceName(rs.getString("Host"));
user.setUsername(rs.getString("User"));
users.add(user);
}
}
return users;
} catch (RegistryException e) {
throw new RegistryException("Error while attempting to read the user list from " +
"the registry", e);
} catch (SQLException e) {
throw new SQLException(e);
} catch (XMLStreamException e) {
throw new XMLStreamException(e);
} catch (ClassNotFoundException e) {
throw new ClassNotFoundException("Driver class not found", e);
}
}
}