/*
* RHQ Management Platform
* Copyright (C) 2005-2014 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.,
* 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
*/
package org.rhq.plugins.postgres;
import static org.rhq.core.domain.measurement.AvailabilityType.DOWN;
import static org.rhq.core.domain.measurement.AvailabilityType.UNKNOWN;
import static org.rhq.core.domain.measurement.AvailabilityType.UP;
import static org.rhq.core.util.StringUtil.isBlank;
import static org.rhq.plugins.database.DatabasePluginUtil.getNumericQueryValues;
import static org.rhq.plugins.database.DatabasePluginUtil.getSingleNumericQueryValue;
import static org.rhq.plugins.database.DatabasePluginUtil.safeClose;
import static org.rhq.plugins.postgres.PostgresTableDiscoveryComponent.SCHEMA_SEPARATOR;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.rhq.core.domain.configuration.Configuration;
import org.rhq.core.domain.configuration.ConfigurationUpdateStatus;
import org.rhq.core.domain.configuration.Property;
import org.rhq.core.domain.configuration.PropertyList;
import org.rhq.core.domain.configuration.PropertyMap;
import org.rhq.core.domain.configuration.PropertySimple;
import org.rhq.core.domain.measurement.AvailabilityType;
import org.rhq.core.domain.measurement.MeasurementDataNumeric;
import org.rhq.core.domain.measurement.MeasurementReport;
import org.rhq.core.domain.measurement.MeasurementScheduleRequest;
import org.rhq.core.pluginapi.configuration.ConfigurationFacet;
import org.rhq.core.pluginapi.configuration.ConfigurationUpdateReport;
import org.rhq.core.pluginapi.inventory.DeleteResourceFacet;
import org.rhq.core.pluginapi.inventory.InvalidPluginConfigurationException;
import org.rhq.core.pluginapi.inventory.ResourceContext;
import org.rhq.core.pluginapi.measurement.MeasurementFacet;
import org.rhq.core.pluginapi.operation.OperationFacet;
import org.rhq.core.pluginapi.operation.OperationResult;
import org.rhq.plugins.database.ConnectionPoolingSupport;
import org.rhq.plugins.database.DatabaseComponent;
import org.rhq.plugins.database.DatabaseQueryUtility;
import org.rhq.plugins.database.PooledConnectionProvider;
/**
* Represents a postgres table
*
* @author Greg Hinkle
*/
public class PostgresTableComponent implements DatabaseComponent<PostgresDatabaseComponent>, ConnectionPoolingSupport,
MeasurementFacet, ConfigurationFacet, DeleteResourceFacet, OperationFacet {
private static final Log LOG = LogFactory.getLog(PostgresTableComponent.class);
private static final String TABLE_EXISTS_QUERY = "select 1 from pg_stat_user_tables "
+ "where schemaname = ? and relname = ?";
private static final String TABLE_STATS_QUERY = "select ts.*, "
+ "pg_relation_size(ts.relid) AS table_size, pg_total_relation_size(ts.relid) AS total_size, "
+ "ios.heap_blks_read, ios.heap_blks_hit, ios.idx_blks_read, ios.idx_blks_hit, "
+ "ios.toast_blks_read, ios.toast_blks_hit, ios.tidx_blks_read, ios.tidx_blks_hit "
+ "from pg_stat_user_tables ts left join pg_statio_user_tables ios on ts.relid = ios.relid "
+ "where ts.schemaname = ? and ts.relname = ?";
private static final String TABLE_ROW_COUNT_APPROX_QUERY = "select pgc.reltuples "
+ "from pg_class pgc, pg_namespace pgn "
+ "where pgn.nspname = ? and pgc.relname = ? and pgc.relnamespace = pgn.oid";
/**
* @deprecated as of RHQ4.11. No longer used (and shouldn't have been exposed anyway).
*/
@Deprecated
public static final String PG_STAT_USER_TABLES_QUERY = "SELECT ts.*, pg_relation_size(ts.relid) AS table_size, pg_total_relation_size(ts.relid) AS total_size, \n"
+ " ios.heap_blks_read, ios.heap_blks_hit, ios.idx_blks_read, ios.idx_blks_hit, \n"
+ " ios.toast_blks_read, ios.toast_blks_hit, ios.tidx_blks_read, ios.tidx_blks_hit \n"
+ "FROM pg_stat_user_tables ts LEFT JOIN pg_statio_user_tables ios on ts.relid = ios.relid \n"
+ "WHERE ts.relname = ?";
/**
* @deprecated as of RHQ4.11. No longer used (and shouldn't have been exposed anyway).
*/
@Deprecated
public static final String PG_COUNT_ROWS = "SELECT COUNT(*) FROM ";
/**
* @deprecated as of RHQ4.11. No longer used (and shouldn't have been exposed anyway).
*/
@Deprecated
public static final String PG_COUNT_ROWS_APPROX = "SELECT reltuples FROM pg_class WHERE relname = ? ";
private ResourceContext<PostgresDatabaseComponent> resourceContext;
public void start(ResourceContext<PostgresDatabaseComponent> context) {
if (!context.getResourceKey().contains(SCHEMA_SEPARATOR)) {
throw new InvalidPluginConfigurationException("Resource key in old format (missing schema name)");
}
if (isBlank(getSchemaNameFromContext(context))) {
throw new InvalidPluginConfigurationException("schemaName is not defined");
}
if (isBlank(getTableNameFromContext(context))) {
throw new InvalidPluginConfigurationException("tableName is not defined");
}
try {
if (!tableExists(context)) {
throw new InvalidPluginConfigurationException("table does not exist");
}
} catch (SQLException e) {
throw new InvalidPluginConfigurationException("Exception while checking table existence", e);
}
this.resourceContext = context;
}
private boolean tableExists(ResourceContext<PostgresDatabaseComponent> context) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = context.getParentResourceComponent().getPooledConnectionProvider().getPooledConnection();
statement = connection.prepareStatement(TABLE_EXISTS_QUERY);
statement.setString(1, getSchemaNameFromContext(context));
statement.setString(2, getTableNameFromContext(context)); // Do not use quoted name here
resultSet = statement.executeQuery();
return resultSet.next();
} finally {
safeClose(connection, statement, resultSet);
}
}
public void stop() {
this.resourceContext = null;
}
@Override
public boolean supportsConnectionPooling() {
return true;
}
@Override
public PooledConnectionProvider getPooledConnectionProvider() {
return resourceContext.getParentResourceComponent().getPooledConnectionProvider();
}
/**
* May be useful for child components.
*
* @return the name of the schema the table belongs to
*/
public String getSchemaName() {
return getSchemaNameFromContext(resourceContext);
}
/**
* May be useful for child components.
*
* @return the name of the table
*/
public String getTableName() {
return getTableNameFromContext(resourceContext);
}
public AvailabilityType getAvailability() {
try {
return tableExists(resourceContext) ? UP : DOWN;
} catch (SQLException e) {
LOG.debug("Exception while checking table existence", e);
return UNKNOWN;
}
}
public void getValues(MeasurementReport report, Set<MeasurementScheduleRequest> requests) {
String tableName = getTableNameFromContext(resourceContext);
String schemaName = getSchemaNameFromContext(resourceContext);
Map<String, Double> results = getNumericQueryValues(this, TABLE_STATS_QUERY, schemaName, tableName);
for (MeasurementScheduleRequest request : requests) {
String metricName = request.getName();
Double value;
if (metricName.equals("rows")) {
value = getSingleNumericQueryValue(this, getCountQuery(schemaName, tableName));
} else if (metricName.equals("rows_approx")) {
value = getSingleNumericQueryValue(this, TABLE_ROW_COUNT_APPROX_QUERY, schemaName, tableName);
} else {
value = results.get(metricName);
}
if (value != null) {
MeasurementDataNumeric mdn = new MeasurementDataNumeric(request, value);
report.addData(mdn);
}
}
}
private String getCountQuery(String schemaName, String tableName) {
return "select count(1) from " + getFullyQualifiedTableName(schemaName, getQuoted(tableName));
}
private String getFullyQualifiedTableName(String schemaName, String tableName) {
return schemaName + SCHEMA_SEPARATOR + tableName;
}
public void deleteResource() throws Exception {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = getPooledConnectionProvider().getPooledConnection();
statement = connection.prepareStatement("drop table "
+ getFullyQualifiedTableName(getSchemaNameFromContext(resourceContext),
getQuoted(getTableNameFromContext(resourceContext))));
statement.executeUpdate();
} finally {
safeClose(connection, statement);
}
}
public Configuration loadResourceConfiguration() throws Exception {
Configuration config = new Configuration();
config.put(new PropertySimple("schemaName", resourceContext.getPluginConfiguration().getSimple("schemaName")
.getStringValue()));
config.put(new PropertySimple("tableName", resourceContext.getPluginConfiguration().getSimple("tableName")
.getStringValue()));
Connection connection = null;
ResultSet columns;
try {
connection = this.resourceContext.getParentResourceComponent().getConnection();
DatabaseMetaData databaseMetaData = connection.getMetaData();
columns = databaseMetaData.getColumns("", getSchemaNameFromContext(resourceContext),
getTableNameFromContext(resourceContext), "");
PropertyList columnList = new PropertyList("columns");
while (columns.next()) {
PropertyMap col = new PropertyMap("columnDefinition");
col.put(new PropertySimple("columnName", columns.getString("COLUMN_NAME")));
col.put(new PropertySimple("columnType", columns.getString("TYPE_NAME")));
col.put(new PropertySimple("columnLength", columns.getInt("COLUMN_SIZE")));
col.put(new PropertySimple("columnPrecision", columns.getInt("DECIMAL_DIGITS")));
col.put(new PropertySimple("columnDefault", columns.getString("COLUMN_DEF")));
col.put(new PropertySimple("columnNullable", Boolean.valueOf(isNullableToBoolean(columns.getInt("NULLABLE")))));
columnList.add(col);
}
config.put(columnList);
} finally {
safeClose(connection);
}
return config;
}
public void updateResourceConfiguration(ConfigurationUpdateReport report) {
try {
Configuration updatedConfiguration = report.getConfiguration();
PropertyList updatedColumns = updatedConfiguration.getList("columns");
Connection connection = this.resourceContext.getParentResourceComponent().getConnection();
DatabaseMetaData dmd = connection.getMetaData();
ResultSet rs = dmd.getColumns("", "", getTableNameFromContext(resourceContext), "");
Map<String, ColumnDefinition> existingDefs = new HashMap<String, ColumnDefinition>();
try {
while (rs.next()) {
ColumnDefinition def = new ColumnDefinition(rs);
existingDefs.put(def.columnName, def);
}
} finally {
rs.close();
}
for (Property newColumnDefinition : updatedColumns.getList()) {
PropertyMap colDef = (PropertyMap) newColumnDefinition;
ColumnDefinition existingDef = existingDefs.get(colDef.getSimple("columnName").getStringValue());
ColumnDefinition newDef = new ColumnDefinition(colDef);
if (existingDef == null) {
// This is a new column to add
String sql = "ALTER TABLE " + getQuoted(getTableNameFromContext(resourceContext)) + " ADD COLUMN "
+ newDef.getColumnSql();
if (DatabaseQueryUtility.executeUpdate(this, sql) != 0) {
throw new RuntimeException("Couldn't add column using SQL: " + sql);
}
} else {
existingDefs.remove(existingDef.columnName);
boolean columnLengthChanged = ((existingDef.columnLength != null && !existingDef.columnLength
.equals(newDef.columnLength)) || (existingDef.columnLength == null && existingDef.columnLength != null));
boolean columnPrecisionChanged = ((existingDef.columnPrecision != null && !existingDef.columnPrecision
.equals(newDef.columnPrecision)) || (existingDef.columnPrecision == null && existingDef.columnPrecision != null));
if (!existingDef.columnType.equals(newDef.columnType) || columnLengthChanged
|| columnPrecisionChanged) {
String sql = "ALTER TABLE " + getQuoted(getTableNameFromContext(resourceContext)) + " ALTER COLUMN "
+ getQuoted(newDef.columnName) + " TYPE " + newDef.columnType;
if (newDef.columnLength != null) {
sql += " ( " + newDef.columnLength;
// TODO: Implement a more robust check to figure out if this column has a numeric type.
if (newDef.columnPrecision != null && !newDef.columnType.startsWith("varchar"))
sql += ", " + newDef.columnPrecision;
sql += " ) ";
}
if (DatabaseQueryUtility.executeUpdate(this, sql) != 1) {
throw new RuntimeException("Couldn't alter column type using SQL: " + sql);
}
}
// Set default separately.
boolean columnDefaultChanged = ((existingDef.columnDefault != null && !existingDef.columnDefault
.equals(newDef.columnDefault)) || (existingDef.columnDefault == null && newDef.columnDefault != null));
if (columnDefaultChanged) {
String sql = "ALTER TABLE " + getQuoted(getTableNameFromContext(resourceContext)) + " ALTER COLUMN "
+ getQuoted(newDef.columnName);
if (newDef.columnDefault == null) {
sql += " DROP DEFAULT";
} else {
sql += " SET DEFAULT " + newDef.columnDefault;
}
if (DatabaseQueryUtility.executeUpdate(this, sql) != 1) {
throw new RuntimeException("Couldn't update column default using SQL: " + sql);
}
}
}
}
// Cols left in existdef map have been removed and need to be dropped
for (ColumnDefinition def : existingDefs.values()) {
DatabaseQueryUtility.executeUpdate(this, "ALTER TABLE " + getQuoted(getTableNameFromContext(resourceContext))
+ " DROP COLUMN " + getQuoted(def.columnName));
}
report.setStatus(ConfigurationUpdateStatus.SUCCESS);
} catch (SQLException e) {
report.setErrorMessageFromThrowable(e);
report.setStatus(ConfigurationUpdateStatus.FAILURE);
}
}
public Connection getConnection() {
return this.resourceContext.getParentResourceComponent().getConnection();
}
public void removeConnection() {
this.resourceContext.getParentResourceComponent().removeConnection();
}
public OperationResult invokeOperation(String name, Configuration parameters) throws InterruptedException,
Exception {
if ("vacuum".equals(name)) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = getPooledConnectionProvider().getPooledConnection();
statement = connection.prepareStatement("vacuum "
+ getFullyQualifiedTableName(getSchemaNameFromContext(resourceContext),
getQuoted(getTableNameFromContext(resourceContext))));
statement.executeUpdate();
} finally {
safeClose(connection, statement);
}
}
return null;
}
static class ColumnDefinition {
String columnName;
String columnType;
Integer columnLength;
Integer columnPrecision;
String columnDefault;
boolean columnNullable;
public ColumnDefinition(ResultSet rs) throws SQLException {
columnName = rs.getString("COLUMN_NAME");
columnType = rs.getString("TYPE_NAME");
columnLength = rs.getInt("COLUMN_SIZE");
columnPrecision = rs.getInt("DECIMAL_DIGITS");
columnDefault = rs.getString("COLUMN_DEF");
columnNullable = isNullableToBoolean(rs.getInt("NULLABLE"));
}
public ColumnDefinition(PropertyMap column) {
columnName = column.getSimple("columnName").getStringValue();
columnType = column.getSimple("columnType").getStringValue();
columnLength = (column.getSimple("columnLength") == null) ? null : column.getSimple("columnLength")
.getIntegerValue();
columnPrecision = (column.getSimple("columnPrecision") == null) ? null : column
.getSimple("columnPrecision").getIntegerValue();
columnDefault = (column.getSimple("columnDefault") == null) ? null : column.getSimple("columnDefault")
.getStringValue();
columnNullable = !(column.getSimple("columnNullable") == null || column.getSimple("columnNullable").getBooleanValue() == null)
&& column.getSimple("columnNullable").getBooleanValue().booleanValue();
}
public String getColumnSql() {
StringBuilder buf = new StringBuilder();
buf.append(getQuoted(columnName)).append(" ").append(columnType);
if(!isArrayColumnType(columnType)) {
if (columnLength != null) {
buf.append("(").append(columnLength).append(")");
}
if (columnPrecision != null) {
buf.append("(").append(columnPrecision).append(")");
}
}
if (columnDefault != null) {
buf.append(" DEFAULT ").append(columnDefault);
}
if (!columnNullable) {
buf.append(" NOT NULL");
}
return buf.toString();
}
private boolean isArrayColumnType(String columnType) {
return columnType != null && columnType.trim().endsWith("[]");
}
}
private static String getSchemaNameFromContext(ResourceContext<PostgresDatabaseComponent> resourceContext) {
return resourceContext.getPluginConfiguration().getSimpleValue("schemaName");
}
private static String getTableNameFromContext(ResourceContext<PostgresDatabaseComponent> resourceContext) {
return resourceContext.getPluginConfiguration().getSimpleValue("tableName");
}
private static String getQuoted(String s) {
return "\"" + s + "\"";
}
private static boolean isNullableToBoolean(int isNullable) {
return isNullable == ResultSetMetaData.columnNoNulls ? false : true;
}
}