/*
* RHQ Management Platform
* Copyright (C) 2005-2008 Red Hat, Inc.
* All rights reserved.
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation version 2 of the License.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
*/
package org.rhq.core.db.upgrade;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.rhq.core.db.DatabaseType;
import org.rhq.core.db.OracleDatabaseType;
import org.rhq.core.db.PostgresqlDatabaseType;
/**
* The introduction of custom alert senders brought with it the denormalization of the AlertNotification schema.
* Instead of the AlertNotification entity storing the notification-related data itself (through referential integrity)
* it has been subsumed inside of configuration objects, which are then associated back to the entity.
*
* Each custom alert sender has full control over the structure within that configuration object. This task represents
* the work necessary to translate the first-class notification data (previously stored in the rhq_alert_notification
* table itself) into appropriate configuration objects to be used by the custom senders that will be shipped with the
* product by default.
*
* In particular, this task handles the upgrade tasks for alert notifications setup against the following types of data:
*
* <ul>
* <li>RHQ Subjects</li>
* <li>RHQ Roles</li>
* <li>Direct Email Addresses</li>
* <li>SNMP Trap Receivers</li>
* <li>Resource Operations</li>
* </ul>
*
* @author Joseph Marques
*/
public class CustomAlertSenderUpgradeTask implements DatabaseUpgradeTask {
private DatabaseType databaseType;
private Connection connection;
private final long NOW = System.currentTimeMillis();
public void execute(DatabaseType databaseType, Connection connection) throws SQLException {
this.databaseType = databaseType;
this.connection = connection;
// upgrade the notification audit trail
upgradeSubjectNotificationLogs();
upgradeRoleNotificationLogs();
upgradeEmailNotificationLogs();
upgradeOperationNotificationLogs();
// upgrade the notification rules
upgradeSubjectNotifications();
upgradeRoleNotifications();
upgradeEmailNotifications();
upgradeSNMPNotifications();
upgradeOperationNotifications();
upgradeSNMPPreferences();
}
private void upgradeSubjectNotificationLogs() throws SQLException {
/*
* alert.alertNotificationLog.sender = "System Users"
* alert.alertNotificationLog.result_state = "UNKNOWN" // success-failure is unknown for existing alerts
* alert.alertNotificationLog.message = "Sending to subjects: [<alert.alertNotificationLog.subjects>]"
*/
String field = "notif.subjects";
String message = concat("'Sending to subjects: '", field);
String insertSQL = getNotificationLogConversionSQL("'System Users'", "'UNKNOWN'", message, field);
System.out.println("Executing: " + insertSQL);
databaseType.executeSql(connection, insertSQL);
}
private void upgradeRoleNotificationLogs() throws SQLException {
/*
* alert.alertNotificationLog.sender = "System Roles"
* alert.alertNotificationLog.result_state = "UNKNOWN" // success-failure is unknown for existing alerts
* alert.alertNotificationLog.message = "Sending to roles: [<alert.alertNotificationLog.roles>]"
*/
String field = "notif.roles";
String message = concat("'Sending to roles: '", field);
String insertSQL = getNotificationLogConversionSQL("'System Roles'", "'UNKNOWN'", message, field);
System.out.println("Executing: " + insertSQL);
databaseType.executeSql(connection, insertSQL);
}
private void upgradeEmailNotificationLogs() throws SQLException {
/*
* alert.alertNotificationLog.sender = "Direct Emails"
* alert.alertNotificationLog.result_state = "UNKNOWN" // success-failure is unknown for existing alerts
* alert.alertNotificationLog.message = "Sending to subjects: [<alert.alertNotificationLog.emails>]"
*/
String field = "notif.emails";
String message = concat("'Sending to addresses: '", field);
String insertSQL = getNotificationLogConversionSQL("'Direct Emails'", "'UNKNOWN'", message, field);
System.out.println("Executing: " + insertSQL);
databaseType.executeSql(connection, insertSQL);
}
private String getNotificationLogConversionSQL(String sender, String resultState, String message,
String notNullField) {
if (databaseType instanceof PostgresqlDatabaseType) {
return "INSERT INTO rhq_alert_notif_log ( id, alert_id, sender, result_state, message )" //
+ " SELECT nextval('RHQ_ALERT_NOTIF_LOG_ID_SEQ'), " //
+ " notif.alert_id AS notifAlertId, " //
+ " " + sender + " AS notifSender, "//
+ " " + resultState + " AS notifResultState, " //
+ " " + message + " AS notifMessage "//
+ " FROM rhq_alert_notif_log notif " //
+ " WHERE " + notNullField + " IS NOT NULL";
} else if (databaseType instanceof OracleDatabaseType) {
return "INSERT INTO rhq_alert_notif_log ( id, alert_id, sender, result_state, message )" //
+ " SELECT RHQ_ALERT_NOTIF_LOG_ID_SEQ.nextval, " //
+ " notifAlertId, notifSender, notifResultState, notifMessage "
+ " FROM ( SELECT notif.alert_id AS notifAlertId, " //
+ " " + sender + " AS notifSender, "//
+ " " + resultState + " AS notifResultState, " //
+ " " + message + " AS notifMessage "//
+ " FROM rhq_alert_notif_log notif " //
+ " WHERE " + notNullField + " IS NOT NULL )";
} else {
throw new IllegalStateException(this.getClass().getSimpleName() + " does not support upgrades for "
+ databaseType.getName());
}
}
private void upgradeOperationNotificationLogs() throws SQLException {
/*
* alert.alertNotificationLog.sender = "Resource Operations"
* alert.alertNotificationLog.result_state = "SUCCESS"
* alert.alertNotificationLog.message = "Executed '<alert.triggeredOperation>' on this resource"
*/
String field = "alert.triggered_operation";
String message = concat("'Executed \"'", field, "'\" on this resource'");
String insertSQL = getNotificationLogInsertionSQL("'Resource Operations'", "'SUCCESS'", message, field);
System.out.println("Executing: " + insertSQL);
databaseType.executeSql(connection, insertSQL);
}
private String getNotificationLogInsertionSQL(String sender, String resultState, String message, String notNullField) {
if (databaseType instanceof PostgresqlDatabaseType) {
return "INSERT INTO rhq_alert_notif_log ( id, alert_id, sender, result_state, message )" //
+ " SELECT nextval('RHQ_ALERT_NOTIF_LOG_ID_SEQ'), " //
+ " alert.id AS notifAlertId, " //
+ " " + sender + " AS notifSender, "//
+ " " + resultState + " AS notifResultState, " //
+ " " + message + " AS notifMessage "//
+ " FROM rhq_alert alert " //
+ " WHERE " + notNullField + " IS NOT NULL";
} else if (databaseType instanceof OracleDatabaseType) {
return "INSERT INTO rhq_alert_notif_log ( id, alert_id, sender, result_state, message )" //
+ " SELECT RHQ_ALERT_NOTIF_LOG_ID_SEQ.nextval, " //
+ " notifAlertId, notifSender, notifResultState, notifMessage "
+ " FROM ( SELECT alert.id AS notifAlertId, " //
+ " " + sender + " AS notifSender, "//
+ " " + resultState + " AS notifResultState, " //
+ " " + message + " AS notifMessage "//
+ " FROM rhq_alert alert " //
+ " WHERE " + notNullField + " IS NOT NULL )";
} else {
throw new IllegalStateException(this.getClass().getSimpleName() + " does not support upgrades for "
+ databaseType.getName());
}
}
private String concat(String... elements) {
StringBuilder builder = new StringBuilder();
if (databaseType instanceof PostgresqlDatabaseType || databaseType instanceof OracleDatabaseType) {
boolean first = true;
for (String next : elements) {
if (first) {
first = false;
} else {
builder.append("||");
}
builder.append(next);
}
} else {
throw new IllegalStateException(this.getClass().getSimpleName() + " does not support upgrades for "
+ databaseType.getName());
}
return builder.toString();
}
private void upgradeSubjectNotifications() throws SQLException {
String dataMapSQL = "" //
+ " SELECT notif.alert_definition_id, notif.subject_id "//
+ " FROM rhq_alert_notification notif "//
+ " WHERE notif.notification_type = 'SUBJECT' "//
+ "ORDER BY notif.alert_definition_id";
List<Object[]> data = databaseType.executeSelectSql(connection, dataMapSQL);
String propertyName = "subjectId";
String senderName = "System Users";
persist(data, propertyName, senderName, "|", true);
}
private void upgradeRoleNotifications() throws SQLException {
String dataMapSQL = "" //
+ " SELECT notif.alert_definition_id, notif.role_id "//
+ " FROM rhq_alert_notification notif "//
+ " WHERE notif.notification_type = 'ROLE' "//
+ "ORDER BY notif.alert_definition_id";
List<Object[]> data = databaseType.executeSelectSql(connection, dataMapSQL);
String propertyName = "roleId";
String senderName = "System Roles";
persist(data, propertyName, senderName, "|", true);
}
private void upgradeEmailNotifications() throws SQLException {
String dataMapSQL = "" //
+ " SELECT notif.alert_definition_id, notif.email_address "//
+ " FROM rhq_alert_notification notif "//
+ " WHERE notif.notification_type = 'EMAIL' "//
+ "ORDER BY notif.alert_definition_id";
List<Object[]> data = databaseType.executeSelectSql(connection, dataMapSQL);
String propertyName = "emailAddress";
String senderName = "Direct Emails";
persist(data, propertyName, senderName, ",", false);
}
private void upgradeSNMPNotifications() throws SQLException {
String dataMapSQL = "" //
+ " SELECT notif.alert_definition_id, notif.snmp_host, notif.snmp_port, notif.snmp_oid "//
+ " FROM rhq_alert_notification notif "//
+ " WHERE notif.notification_type = 'SNMP' "//
+ "ORDER BY notif.alert_definition_id";
List<Object[]> data = databaseType.executeSelectSql(connection, dataMapSQL);
for (Object[] next : data) {
int alertDefinitionId = ((Number) next[0]).intValue();
String host = (String) next[1];
String port = ((Number) next[2]).toString();
String oid = (String) next[3];
// buffer will be 0 the very first time, since definitionId is initially -1
int configId = persistConfiguration("host", host, "port", port, "oid", oid);
persistNotification(alertDefinitionId, configId, "SNMP Traps");
}
}
private void upgradeOperationNotifications() throws SQLException {
String dataMapSQL = "" //
+ " SELECT def.id, def.operation_def_id" //
+ " FROM rhq_alert_definition def" //
+ " WHERE def.operation_def_id IS NOT NULL"; // not all alert definitions have operation notifications
List<Object[]> data = databaseType.executeSelectSql(connection, dataMapSQL);
for (Object[] next : data) {
int alertDefinitionId = ((Number) next[0]).intValue();
String operationDefinitionId = ((Number) next[1]).toString();
// buffer will be 0 the very first time, since definitionId is initially -1
int configId = persistConfiguration("operation-definition-id", operationDefinitionId, "selection-mode",
"SELF");
persistNotification(alertDefinitionId, configId, "Resource Operations");
}
}
/**
* Copy the system wide snmp preferences. This happens only on
* a fresh migration from pre RHQ3 and only if the user has
* actually changed the provided defaults.
*/
private void upgradeSNMPPreferences() throws SQLException {
String oldPrefsSQL = "" //
+ " SELECT property_key,property_value"
+ " FROM RHQ_SYSTEM_CONFIG"
+ " WHERE property_key LIKE 'SNMP%'";
String[] keyToProp = { //
"SNMP_AGENT_ADDRESS","agentAddress", //
"SNMP_AUTH_PASSPHRASE","authPassphrase", //
"SNMP_AUTH_PROTOCOL","authProtocol", //
"SNMP_COMMUNITY","community",//
"SNMP_CONTEXT_NAME","targetContext",//
"SNMP_ENGINE_ID","engineId",//
"SNMP_ENTERPRISE_OID","enterpriseOid",//
"SNMP_GENERIC_ID","genericId",//
"SNMP_PRIVACY_PROTOCOL","privacyProtocol",//
"SNMP_PRIV_PASSPHRASE","privacyPassphrase",//
"SNMP_SECURITY_NAME","securityName",//
"SNMP_SPECIFIC_ID","specificId",//
"SNMP_TRAP_OID","trapOid",//
"SNMP_VERSION","snmpVersion"
};
/*
* Check if there is already a config present.
* Only run the copy on a fresh upgrade from a pre RHQ 3 version.
*/
int configId = getPluginConfigurationId("alert-snmp");
if (configId!=0) {
System.out.println("Already found a snmp configuration, not copying the old one over.");
return;
}
// Get the properties from the database
List<Object[]> data = databaseType.executeSelectSql(connection, oldPrefsSQL);
// check if the user actually did set up the snmp settings in the older version
// If not, don't bother, as the plugin will set up its defaults later on.
for (Object[] next : data) {
String key = (String) next[0];
if (key.equals("SNMP_VERSION")) {
String val = (String) next[1];
if (val==null || val.equals("")) {
System.out.println("No SNMP config set in old db version, so not copying");
return;
}
}
}
// We have work to do ...
configId = databaseType.getNextSequenceValue(connection, "rhq_config", "id");
String insertConfigSQL = getInsertConfigSQL(configId);
databaseType.executeSql(connection, insertConfigSQL);
for (Object[] next : data) {
// find property
String propertyName = null;
for (int i = 0 ; i< keyToProp.length ; i++) {
if (keyToProp[i].equals(next[0])) {
propertyName = keyToProp[i+1];
break;
}
}
if (propertyName==null) {
System.err.println("Input property " + next[0] + " is not encoded");
System.err.println("Not copying the SNMP preferences");
}
String propertyValue = (String) next[1];
int propertyId = databaseType.getNextSequenceValue(connection, "rhq_config_property", "id");
String insertPropertySQL = getInsertPropertySQL(propertyId, configId, propertyName, propertyValue);
databaseType.executeSql(connection, insertPropertySQL);
}
//now we need to associate the plugin with its configuration
int pluginId = getPluginId("alert-snmp");
if (pluginId == 0) {
System.err.println("No 'alert-snmp' plugin found in the database. Creating a temporary one.");
String pluginName = "alert-snmp";
String displayName = "Alert:SNMP-invalid";
String description = "This is an automatically generated invalid plugin used to associate the SNMP "
+ "configuration upgraded from the legacy tables. You should not really ever see this plugin "
+ "deployed as it should be overwritten during the first server startup after the upgrade.";
String deploymentType = "SERVER";
String pluginDescriptorType =
"org.rhq.enterprise.server.xmlschema.generated.serverplugin.alert.AlertPluginDescriptorType";
pluginId = insertPluginEntry(pluginName, displayName, description, deploymentType, pluginDescriptorType);
}
setPluginConfiguration(pluginId, configId);
}
int getPluginConfigurationId(String pluginName) throws SQLException {
String getConfigIdSQL = "" //
+ " SELECT plugin_config_id " //
+ " FROM rhq_plugin" //
+ " WHERE name = '" + pluginName + "'";
List<Object[]> data = databaseType.executeSelectSql(connection, getConfigIdSQL);
if (data==null || data.size()==0)
return 0;
Object[] idos = data.get(0);
return (Integer)idos[0];
}
private void persist(List<Object[]> data, String propertyName, String sender, String delimiter,
boolean bufferWithDelimiter) throws SQLException {
int definitionId = -1;
StringBuilder buffer = new StringBuilder();
for (Object[] next : data) {
int nextDefinitionId = ((Number) next[0]).intValue();
String nextData = String.valueOf(next[1]);
if (nextDefinitionId != definitionId) {
if (buffer.length() != 0) {
// buffer will be 0 the very first time, since definitionId is initially -1
String bufferedData = bufferWithDelimiter ? (delimiter + buffer.toString() + delimiter) : buffer
.toString();
int configId = persistConfiguration(propertyName, bufferedData);
persistNotification(definitionId, configId, sender);
}
definitionId = nextDefinitionId;
buffer = new StringBuilder(); // reset for the next definitionId
}
if (buffer.length() != 0) {
// elements are already in the list, always add <delimiter> between them
buffer.append(delimiter);
}
buffer.append(nextData);
}
if (buffer.length() != 0) {
// always add <delimiter> to both side of the buffer -- this will enable searches for data
// using the JPQL fragment notification.configuration.value = <delimiter><data><delimiter>'
String bufferedData = bufferWithDelimiter ? (delimiter + buffer.toString() + delimiter) : buffer.toString();
int configId = persistConfiguration(propertyName, bufferedData);
persistNotification(definitionId, configId, sender);
}
}
private int persistConfiguration(String... propertyNameValues) throws SQLException {
int configId = databaseType.getNextSequenceValue(connection, "rhq_config", "id");
String insertConfigSQL = getInsertConfigSQL(configId);
databaseType.executeSql(connection, insertConfigSQL);
for (int i = 0; i < propertyNameValues.length; i += 2) {
String propertyName = propertyNameValues[i];
String propertyValue = propertyNameValues[i + 1];
int propertyId = databaseType.getNextSequenceValue(connection, "rhq_config_property", "id");
String insertPropertySQL = getInsertPropertySQL(propertyId, configId, propertyName, propertyValue);
databaseType.executeSql(connection, insertPropertySQL);
}
return configId;
}
private void persistNotification(int definitionId, int configId, String sender) throws SQLException {
int notificationId = databaseType.getNextSequenceValue(connection, "rhq_alert_notification", "id");
String insertNotificationSQL = getInsertNotificationSQL(notificationId, definitionId, configId, sender);
databaseType.executeSql(connection, insertNotificationSQL);
}
private String getInsertConfigSQL(int id) {
return "INSERT INTO rhq_config ( id, version, ctime, mtime )" //
+ " VALUES ( " + id + ", 0, " + NOW + ", " + NOW + " ) ";
}
private String getInsertPropertySQL(int id, int configId, String name, String value) {
return "INSERT INTO rhq_config_property ( id, configuration_id, name, string_value, dtype )" //
+ " VALUES ( " + id + ", " + configId + ", '" + name + "', '" + value + "', 'property' ) ";
}
private String getInsertNotificationSQL(int id, int definitionId, int configId, String sender) {
return "INSERT INTO rhq_alert_notification ( id, alert_definition_id, sender_config_id, sender_name )" //
+ " VALUES ( " + id + ", " + definitionId + ", " + configId + ", '" + sender + "' ) ";
}
private int getPluginId(String pluginName) throws SQLException {
String sql = ""
+ "SELECT id "
+ "FROM rhq_plugin "
+ "WHERE name = '" + pluginName + "'";
List<Object[]> data = databaseType.executeSelectSql(connection, sql);
if (data == null || data.isEmpty()) {
return 0;
} else {
return (Integer) data.get(0)[0];
}
}
private void setPluginConfiguration(int pluginId, int configurationId) throws SQLException {
databaseType.executeSql(connection,
"UPDATE rhq_plugin SET plugin_config_id = " + configurationId
+ " WHERE id = " + pluginId);
}
private int insertPluginEntry(String pluginName, String displayName, String description, String deploymentType, String pluginDescriptorType) throws SQLException {
int pluginId = databaseType.getNextSequenceValue(connection, "rhq_plugin", "id");
String sql = ""
+ "INSERT INTO rhq_plugin(id, name, display_name, description, enabled, status, path, md5, ctime, mtime, deployment, ptype) "
+ "VALUES("
+ pluginId + ", " //id
+ "'" + pluginName + "', " //name
+ "'" + displayName + "', " //display_name
+ "'" + description + "', " //description
+ databaseType.getBooleanValue(true) + ", " //enabled
+ "'INSTALLED', " //status
+ "'invalid-path.jar', " //path
+ "'0', " //md5
+ NOW + ", " //ctime
+ NOW + ", " //mtime
+ "'" + deploymentType + "', " //deployment
+ "'" + pluginDescriptorType + "'" //ptype
+ ")";
databaseType.executeSql(connection, sql);
return pluginId;
}
}