/*******************************************************************************
* This file is part of OpenNMS(R).
*
* Copyright (C) 2006-2011 The OpenNMS Group, Inc.
* OpenNMS(R) is Copyright (C) 1999-2011 The OpenNMS Group, Inc.
*
* OpenNMS(R) is a registered trademark of The OpenNMS Group, Inc.
*
* OpenNMS(R) 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, either version 3 of the License,
* or (at your option) any later version.
*
* OpenNMS(R) 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 OpenNMS(R). If not, see:
* http://www.gnu.org/licenses/
*
* For more information contact:
* OpenNMS(R) Licensing <license@opennms.org>
* http://www.opennms.org/
* http://www.opennms.com/
*******************************************************************************/
package org.opennms.netmgt.dao.db;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileFilter;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintStream;
import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.sql.DataSource;
import org.opennms.netmgt.dao.db.columnchanges.AutoIntegerReplacement;
import org.opennms.netmgt.dao.db.columnchanges.DoNotAddColumnReplacement;
import org.opennms.netmgt.dao.db.columnchanges.EventSourceReplacement;
import org.opennms.netmgt.dao.db.columnchanges.FixedIntegerReplacement;
import org.opennms.netmgt.dao.db.columnchanges.NextValReplacement;
import org.opennms.netmgt.dao.db.columnchanges.RowHasBogusDataReplacement;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;
public class InstallerDb {
private static final String IPLIKE_SQL_RESOURCE = "iplike.sql";
public static final float POSTGRES_MIN_VERSION = 7.4f;
public static final float POSTGRES_MAX_VERSION_PLUS_ONE = 9.2f;
private static final int s_fetch_size = 1024;
private static Comparator<Constraint> constraintComparator = new Comparator<Constraint>() {
public int compare(final Constraint o1, final Constraint o2) {
return o1.getName().compareTo(o2.getName());
}
};
private final IndexDao m_indexDao = new IndexDao();
private final TriggerDao m_triggerDao = new TriggerDao();
private DataSource m_dataSource = null;
private DataSource m_adminDataSource = null;
private PrintStream m_out = System.out;
private boolean m_debug = false;
private String m_createSqlLocation = null;
private String m_storedProcedureDirectory = null;
private String m_databaseName = null;
private String m_schemaName = null;
private String m_pass = null;
private String m_pg_iplike = null;
private String m_pg_plpgsql = null;
private final Map<String, ColumnChangeReplacement> m_columnReplacements = new HashMap<String, ColumnChangeReplacement>();
private String m_sql;
private LinkedList<String> m_tables = null;
private LinkedList<String> m_sequences = null;
private final HashMap<String, List<Insert>> m_inserts = new HashMap<String, List<Insert>>();
private final HashSet<String> m_drops = new HashSet<String>();
private final HashSet<String> m_changed = new HashSet<String>();
private Map<String, Integer> m_dbtypes = null;
private HashMap<String, String[]> m_seqmapping = null;
private Connection m_connection;
private Connection m_adminConnection;
private String m_user;
private boolean m_force = false;
private boolean m_ignore_notnull = false;
private boolean m_no_revert = false;
private final Pattern m_seqmappingPattern = Pattern.compile("\\s*--#\\s+install:\\s*"
+ "(\\S+)\\s+(\\S+)\\s+(\\S+)\\s*.*");
private final Pattern m_createPattern = Pattern.compile("(?i)\\s*create\\b.*");
private final Pattern m_criteriaPattern = Pattern.compile("\\s*--#\\s+criteria:\\s*(.*)");
private final Pattern m_insertPattern = Pattern.compile("(?i)INSERT INTO "
+ "[\"']?([\\w_]+)[\"']?.*");
private final Pattern m_dropPattern = Pattern.compile("(?i)DROP TABLE [\"']?"
+ "([\\w_]+)[\"']?.*");
private final Pattern m_emptyLine = Pattern.compile("^\\s*(\\\\.*)?$");
private final Pattern m_createUnique = Pattern.compile("(?i)\\s*create\\s+((?:unique )?\\w+)\\s+[\"']?(\\w+)[\"']?.*");
private final Pattern m_createLanguagePattern = Pattern.compile("(?i)\\s*create\\s+trusted procedural language\\s+[\"']?(\\w+)[\"']?.*");
private final FileFilter m_sqlFilter = new FileFilter() {
public boolean accept(final File pathname) {
return (pathname.getName().startsWith("get") && pathname.getName().endsWith(".sql"))
|| pathname.getName().endsWith("Trigger.sql");
}
};
private final Pattern m_createFunction = Pattern.compile("(?is)\\b(CREATE(?: OR REPLACE)? FUNCTION\\s+"
+ "(\\w+)\\s*\\((.*?)\\)\\s+"
+ "RETURNS\\s+(\\S+)\\s+AS\\s+"
+ "(.+? language ['\"]?\\w+['\"]?);)");
private final SimpleDateFormat m_dateParser = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
private final SimpleDateFormat m_dateFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
private final char m_spins[] = { '/', '-', '\\', '|' };
private LinkedList<Constraint> m_constraints;
/**
* <p>Constructor for InstallerDb.</p>
*/
public InstallerDb() {
}
/**
* <p>readTables</p>
*
* @throws java.lang.Exception if any.
*/
public void readTables() throws Exception {
readTables(new InputStreamReader(new FileInputStream(m_createSqlLocation), "UTF-8"));
}
/**
* <p>readTables</p>
*
* @param reader a {@link java.io.Reader} object.
* @throws java.lang.Exception if any.
*/
public void readTables(final Reader reader) throws Exception {
final BufferedReader r = new BufferedReader(reader);
String line;
m_tables = new LinkedList<String>();
m_seqmapping = new HashMap<String, String[]>();
m_sequences = new LinkedList<String>();
m_indexDao.reset();
LinkedList<String> sql_l = new LinkedList<String>();
String criteria = null;
while ((line = r.readLine()) != null) {
Matcher m;
m = m_emptyLine.matcher(line);
if (m.matches()) {
continue;
}
m = m_seqmappingPattern.matcher(line);
if (m.matches()) {
final String[] a = { m.group(2), m.group(3) };
m_seqmapping.put(m.group(1), a);
continue;
}
m = m_criteriaPattern.matcher(line);
if (m.matches()) {
criteria = m.group(1);
continue;
}
if (line.matches("^--.*$")) {
continue;
}
if (m_createPattern.matcher(line).matches()) {
m = m_createUnique.matcher(line);
if (m.matches()) {
String type = m.group(1);
String name = m.group(2).replaceAll("^[\"']", "").replaceAll("[\"']$", "");
if (type.toLowerCase().indexOf("table") != -1) {
m_tables.add(name);
} else if (type.toLowerCase().indexOf("sequence") != -1) {
m_sequences.add(name);
} else if (type.toLowerCase().indexOf("function") != -1) {
if (type.toLowerCase().indexOf("language 'c'") != -1) {
//m_cfunctions.add(name);
} else {
//m_functions.add(name);
}
} else if (type.toLowerCase().indexOf("trusted") != -1) {
m = m_createLanguagePattern.matcher(line);
if (!m.matches()) {
throw new Exception("Could not match name and type of the trusted procedural language in this line: " + line);
}
//m_languages.add(m.group(1));
} else if (type.toLowerCase().matches("^.*\\bindex\\b.*$")) {
final Index i = Index.findIndexInString(line);
if (i == null) {
throw new Exception("Could not match name and type of the index in this line: " + line);
}
m_indexDao.add(i);
} else {
throw new Exception("Unknown CREATE encountered: CREATE " + type + " " + name);
}
} else {
throw new Exception("Unknown CREATE encountered: " + line);
}
sql_l.add(line);
continue;
}
m = m_insertPattern.matcher(line);
if (m.matches()) {
final String table = m.group(1);
final Insert insert = new Insert(table, line, criteria);
criteria = null;
if (!m_inserts.containsKey(table)) {
m_inserts.put(table, new LinkedList<Insert>());
}
m_inserts.get(table).add(insert);
continue;
}
if (line.toLowerCase().startsWith("select setval ")) {
final String table = "select_setval";
final Insert insert = new Insert("select_setval", line, null);
if (!m_inserts.containsKey(table)) {
m_inserts.put(table, new LinkedList<Insert>());
}
m_inserts.get(table).add(insert);
sql_l.add(line);
continue;
}
m = m_dropPattern.matcher(line);
if (m.matches()) {
m_drops.add(m.group(1));
sql_l.add(line);
continue;
}
// XXX should do something here so we can catch what we can't
// parse
// m_out.println("unmatched line: " + line);
sql_l.add(line);
}
r.close();
m_sql = cleanText(sql_l);
}
/**
* <p>cleanText</p>
*
* @param list a {@link java.util.List} object.
* @return a {@link java.lang.String} object.
*/
public static String cleanText(final List<String> list) {
final StringBuffer s = new StringBuffer();
for (final String l : list) {
s.append(l.replaceAll("\\s+", " "));
if (l.indexOf(';') != -1) {
s.append('\n');
}
}
return s.toString();
}
/**
* <p>createSequences</p>
*
* @throws java.lang.Exception if any.
*/
public void createSequences() throws Exception {
assertUserSet();
final Statement st = getConnection().createStatement();
ResultSet rs;
m_out.println("- creating sequences... ");
for (final String sequence : getSequenceNames()) {
if (getSequenceMapping(sequence) == null) {
throw new Exception("Cannot find sequence mapping for " + sequence + "-- sequence mapping is setup by comments in the create.sql script. Look:--# DO NOT forget to add an \"install\" comment" );
}
}
for (final String sequence : getSequenceNames()) {
int minvalue = 1;
boolean alreadyExists;
m_out.print(" - checking \"" + sequence + "\" sequence... ");
rs = st.executeQuery("SELECT relname FROM pg_class WHERE relname = '" + sequence.toLowerCase() + "'");
alreadyExists = rs.next();
if (alreadyExists) {
m_out.println("ALREADY EXISTS");
} else {
m_out.println("DOES NOT EXIST");
m_out.print(" - creating sequence \"" + sequence + "\"... ");
st.execute("CREATE SEQUENCE " + sequence + " minvalue "
+ minvalue);
m_out.println("OK");
grantAccessToObject(sequence, 4);
}
}
m_out.println("- creating sequences... DONE");
}
/**
* <p>updatePlPgsql</p>
*
* @throws java.lang.Exception if any.
*/
public void updatePlPgsql() throws Exception {
final Statement st = getConnection().createStatement();
ResultSet rs;
m_out.print("- adding PL/pgSQL call handler... ");
rs = st.executeQuery("SELECT oid FROM pg_proc WHERE " + "proname='plpgsql_call_handler' AND " + "proargtypes = ''");
if (rs.next()) {
m_out.println("EXISTS");
} else if (isPgPlPgsqlLibPresent()) {
st.execute("CREATE FUNCTION plpgsql_call_handler () " + "RETURNS OPAQUE AS '" + m_pg_plpgsql + "' LANGUAGE 'c'");
m_out.println("OK");
} else {
m_out.println("SKIPPED (location of PL/pgSQL library not set, will try to continue)");
}
m_out.print("- adding PL/pgSQL language module... ");
rs = st.executeQuery("SELECT pg_language.oid FROM "
+ "pg_language, pg_proc WHERE "
+ "pg_proc.proname='plpgsql_call_handler' AND "
+ "pg_proc.proargtypes = '' AND "
+ "pg_proc.oid = pg_language.lanplcallfoid AND "
+ "pg_language.lanname = 'plpgsql'");
if (rs.next()) {
m_out.println("EXISTS");
} else {
st.execute("CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'");
m_out.println("OK");
}
}
/**
* <p>isIpLikeUsable</p>
*
* @return a boolean.
*/
public boolean isIpLikeUsable() {
Statement st = null;
try {
m_out.print("- checking if iplike is usable... ");
st = getConnection().createStatement();
st.execute("SELECT IPLIKE('127.0.0.1', '*.*.*.*')");
m_out.println("YES");
} catch (final SQLException selectException) {
m_out.println("NO");
return false;
} finally {
closeQuietly(st);
}
try {
m_out.print("- checking if iplike supports IPv6... ");
st = getConnection().createStatement();
st.execute("SELECT IPLIKE('fe80:0000:5ab0:35ff:feee:cecd', 'fe80:*::cecd')");
m_out.println("YES");
} catch (final SQLException selectException) {
m_out.println("NO");
return false;
} finally {
closeQuietly(st);
}
return true;
}
/**
* <p>updateIplike</p>
*
* @throws java.lang.Exception if any.
*/
public void updateIplike() throws Exception {
boolean insert_iplike = !isIpLikeUsable();
if (insert_iplike) {
dropExistingIpLike();
if (!installCIpLike()) {
setupPlPgsqlIplike();
}
}
// XXX This error is generated from Postgres if eventtime(text)
// does not exist:
// ERROR: function eventtime(text) does not exist
m_out.print("- checking for stale eventtime.so references... ");
Statement st = null;
try {
st = getConnection().createStatement();
st.execute("DROP FUNCTION eventtime(text)");
m_out.println("REMOVED");
} catch (final SQLException e) {
/*
* SQL Status code: 42883: ERROR: function %s does not exist
*/
if (e.toString().indexOf("does not exist") != -1
|| "42883".equals(e.getSQLState())) {
m_out.println("OK");
} else {
m_out.println("FAILED");
throw e;
}
} finally {
closeQuietly(st);
}
}
private boolean installCIpLike() {
m_out.print("- inserting C iplike function... ");
boolean success;
if (m_pg_iplike == null) {
success = false;
m_out.println("SKIPPED (location of iplike function not set)");
} else {
Statement st = null;
try {
st = getConnection().createStatement();
st.execute("CREATE FUNCTION iplike(text,text) RETURNS bool " + "AS '" + m_pg_iplike + "' LANGUAGE 'c' WITH(isstrict)");
success = true;
m_out.println("OK");
} catch (final SQLException e) {
success = false;
m_out.println("FAILED (" + e + ")");
} finally {
closeQuietly(st);
}
}
return success;
}
private void dropExistingIpLike() throws SQLException {
Statement st = null;
m_out.print("- removing existing iplike definition (if any)... ");
try {
st = getConnection().createStatement();
st.execute("DROP FUNCTION iplike(text,text)");
m_out.println("OK");
} catch (final SQLException dropException) {
if (dropException.toString().contains("does not exist")
|| "42883".equals(dropException.getSQLState())) {
m_out.println("OK");
} else {
m_out.println("FAILED");
throw dropException;
}
}
finally {
closeQuietly(st);
}
}
/**
* <p>setupPlPgsqlIplike</p>
*
* @throws java.lang.Exception if any.
*/
public void setupPlPgsqlIplike() throws Exception {
InputStream sqlfile = null;
Statement st = null;
try {
st = getConnection().createStatement();
m_out.print("- inserting PL/pgSQL iplike function... ");
sqlfile = getClass().getResourceAsStream(IPLIKE_SQL_RESOURCE);
if (sqlfile == null) {
String message = "unable to locate " + IPLIKE_SQL_RESOURCE;
m_out.println("FAILED (" + message + ")");
throw new Exception(message);
}
final BufferedReader in = new BufferedReader(new InputStreamReader(sqlfile, "UTF-8"));
final StringBuffer createFunction = new StringBuffer();
String line;
while ((line = in.readLine()) != null) {
createFunction.append(line).append("\n");
}
st.execute(createFunction.toString());
m_out.println("OK");
} catch (final Throwable e) {
m_out.println("FAILED");
if (e instanceof Exception) {
throw (Exception)e;
}
else {
throw new Exception(e);
}
} finally {
// don't forget to close the statement
closeQuietly(st);
// don't forget to close the input stream
closeQuietly(sqlfile);
}
}
private void closeQuietly(final InputStream sqlfile) {
try {
if (sqlfile != null) {
sqlfile.close();
}
} catch(final IOException e) {
}
}
private void closeQuietly(final Statement st) {
try {
if (st != null) {
st.close();
}
} catch(final Throwable e) {
}
}
/**
* <p>addStoredProcedures</p>
*
* @throws java.lang.Exception if any.
*/
public void addStoredProcedures() throws Exception {
m_triggerDao.reset();
Statement st = getConnection().createStatement();
m_out.print("- adding stored procedures... ");
File[] list = new File(m_storedProcedureDirectory).listFiles(m_sqlFilter);
for (final File element : list) {
final LinkedList<String> drop = new LinkedList<String>();
final StringBuffer create = new StringBuffer();
String line;
m_out.print("\n - " + element.getName() + "... ");
final BufferedReader r = new BufferedReader(new InputStreamReader(new FileInputStream(element), "UTF-8"));
while ((line = r.readLine()) != null) {
line = line.trim();
if (line.matches("--.*")) {
continue;
}
if (line.toLowerCase().startsWith("drop function")
|| line.toLowerCase().startsWith("drop trigger")) {
drop.add(line);
} else {
create.append(line);
create.append("\n");
}
}
r.close();
/*
* Find the trigger first, because if there is a trigger that
* uses this stored procedure on this table, we'll need to drop
* it first.
*/
final Trigger t = Trigger.findTriggerInString(create.toString());
if (t != null) {
m_triggerDao.add(t);
}
final Matcher m = m_createFunction.matcher(create.toString());
if (!m.find()) {
throw new Exception("For stored procedure in file '"
+ element.getName()
+ "' couldn't match \""
+ m.pattern().pattern()
+ "\" in string \"" + create + "\"");
}
final String createSql = m.group(1);
final String function = m.group(2);
final String columns = m.group(3);
final String returns = m.group(4);
// final String rest = m.group(5);
if (functionExists(function, columns, returns)) {
if (t != null && t.isOnDatabase(getConnection())) {
t.removeFromDatabase(getConnection());
}
st.execute("DROP FUNCTION " + function + " (" + columns + ")");
st.execute(createSql);
m_out.print("OK (dropped and re-added)");
} else {
st.execute(createSql);
m_out.print("OK");
}
}
m_out.println("");
}
/**
* <p>functionExists</p>
*
* @param function a {@link java.lang.String} object.
* @param columns a {@link java.lang.String} object.
* @param returnType a {@link java.lang.String} object.
* @return a boolean.
* @throws java.lang.Exception if any.
*/
public boolean functionExists(final String function, String columns, final String returnType) throws Exception {
final Map<String, Integer> types = getTypesFromDB();
int[] columnTypes = new int[0];
columns = columns.trim();
if (columns.length() > 0) {
final String[] splitColumns = columns.split("\\s*,\\s*");
columnTypes = new int[splitColumns.length];
Column c;
for (int j = 0; j < splitColumns.length; j++) {
c = new Column();
c.parseColumnType(splitColumns[j]);
columnTypes[j] = (types.get(c.getType())).intValue();
}
}
final Column c = new Column();
try {
c.parseColumnType(returnType);
} catch (final Throwable e) {
throw new Exception("Could not parse column type '" + returnType + "' for function '" + function + "'. Nested exception: " + e.getMessage(), e);
}
final int retType = (types.get(c.getType())).intValue();
return functionExists(function, columnTypes, retType);
}
/**
* <p>functionExists</p>
*
* @param function a {@link java.lang.String} object.
* @param columnTypes an array of int.
* @param retType a int.
* @return a boolean.
* @throws java.lang.Exception if any.
*/
public boolean functionExists(final String function, final int[] columnTypes, final int retType) throws Exception {
final Statement st = getConnection().createStatement();
ResultSet rs;
final StringBuffer ct = new StringBuffer();
for (final int columnType : columnTypes) {
ct.append(" " + columnType);
}
final String query = "SELECT oid FROM pg_proc WHERE proname='"
+ function.toLowerCase() + "' AND " + "prorettype=" + retType
+ " AND " + "proargtypes='" + ct.toString().trim() + "'";
rs = st.executeQuery(query);
return rs.next();
}
/**
* <p>getTypesFromDB</p>
*
* @return a {@link java.util.Map} object.
* @throws java.sql.SQLException if any.
*/
public Map<String, Integer> getTypesFromDB() throws SQLException {
if (m_dbtypes != null) {
return Collections.unmodifiableMap(m_dbtypes);
}
final Statement st = getConnection().createStatement();
ResultSet rs;
final HashMap<String, Integer> m = new HashMap<String, Integer>();
rs = st.executeQuery("SELECT oid,typname,typlen FROM pg_type");
while (rs.next()) {
try {
m.put(Column.normalizeColumnType(rs.getString(2), (rs.getInt(3) < 0)), Integer.valueOf(rs.getInt(1)));
} catch (final Throwable e) {
// ignore
}
}
m_dbtypes = m;
return Collections.unmodifiableMap(m_dbtypes);
}
/**
* <p>addTriggersForTable</p>
*
* @param table a {@link java.lang.String} object.
* @throws java.sql.SQLException if any.
*/
public void addTriggersForTable(final String table) throws SQLException {
final List<Trigger> triggers = m_triggerDao.getTriggersForTable(table.toLowerCase());
for (final Trigger trigger : triggers) {
m_out.print(" - checking trigger '" + trigger.getName() + "' on this table... ");
if (!trigger.isOnDatabase(getConnection())) {
trigger.addToDatabase(getConnection());
}
m_out.println("DONE");
}
}
/**
* <p>createTables</p>
*
* @throws java.lang.Exception if any.
*/
public void createTables() throws Exception {
assertUserSet();
final Statement st = getConnection().createStatement();
ResultSet rs;
m_out.println("- creating tables...");
for (String tableName : getTableNames()) {
if (m_force) {
tableName = tableName.toLowerCase();
final String create = getTableCreateFromSQL(tableName);
boolean remove;
rs = st.executeQuery("SELECT relname FROM pg_class "
+ "WHERE relname = '" + tableName + "'");
remove = rs.next();
m_out.print(" - removing old table... ");
if (remove) {
st.execute("DROP TABLE " + tableName + " CASCADE");
m_out.println("REMOVED");
} else {
m_out.println("CLEAN");
}
m_out.print(" - creating table \"" + tableName + "\"... ");
st.execute("CREATE TABLE " + tableName + " (" + create + ")");
m_out.println("CREATED");
addIndexesForTable(tableName);
addTriggersForTable(tableName);
grantAccessToObject(tableName, 2);
} else {
m_out.println(" - checking table \"" + tableName + "\"... ");
tableName = tableName.toLowerCase();
final Table newTable = getTableFromSQL(tableName);
final Table oldTable = getTableFromDB(tableName);
if (newTable.equals(oldTable)) {
addIndexesForTable(tableName);
addTriggersForTable(tableName);
m_out.println(" - checking table \"" + tableName + "\"... UPTODATE");
} else {
if (oldTable == null) {
final String create = getTableCreateFromSQL(tableName);
final String createSql = "CREATE TABLE " + tableName + " (" + create + ")";
m_out.print(" - checking table \"" + tableName + "\"... ");
st.execute(createSql);
m_out.println("CREATED");
addIndexesForTable(tableName);
addTriggersForTable(tableName);
grantAccessToObject(tableName, 2);
} else {
try {
changeTable(tableName, oldTable, newTable);
} catch (final Throwable e) {
throw new Exception("Error changing table '" + tableName + "'. Nested exception: " + e.getMessage(), e);
}
}
}
}
}
m_out.println("- creating tables... DONE");
}
/**
* <p>getTableFromSQL</p>
*
* @param tableName a {@link java.lang.String} object.
* @return a {@link org.opennms.netmgt.dao.db.Table} object.
* @throws java.lang.Exception if any.
*/
public Table getTableFromSQL(String tableName) throws Exception {
final Table table = new Table();
final LinkedList<Column> columns = new LinkedList<Column>();
final LinkedList<Constraint> constraints = new LinkedList<Constraint>();
boolean parens = false;
StringBuffer accumulator = new StringBuffer();
final String create = getTableCreateFromSQL(tableName);
for (int i = 0; i <= create.length(); i++) {
char c = ' ';
if (i < create.length()) {
c = create.charAt(i);
if (c == '(' || c == ')') {
parens = (c == '(');
accumulator.append(c);
continue;
}
}
if (((c == ',') && !parens) || i == create.length()) {
final String a = accumulator.toString().trim();
if (a.toLowerCase().startsWith("constraint ")) {
Constraint constraint;
try {
constraint = new Constraint(tableName, a);
} catch (final Throwable e) {
throw new Exception("Could not parse constraint for table '" + tableName + "'. Nested exception: " + e.getMessage(), e);
}
final List<String> constraintColumns = constraint.getColumns();
if (constraint.getType() != Constraint.CHECK) {
Assert.state(constraintColumns.size() > 0, "constraint '" + constraint.getName() + "' has no constrained columns");
for (final String constrainedName : constraintColumns) {
final Column constrained = findColumn(columns, constrainedName);
if (constrained == null) {
throw new Exception(
"constraint "
+ constraint.getName()
+ " references column \""
+ constrainedName
+ "\", which is not a column in the table "
+ tableName);
}
}
}
constraints.add(constraint);
} else {
final Column column = new Column();
try {
column.parse(accumulator.toString());
columns.add(column);
} catch (final Throwable e) {
throw new Exception("Could not parse table " + tableName + ". Chained: " + e.getMessage(), e);
}
}
accumulator = new StringBuffer();
} else {
accumulator.append(c);
}
}
table.setName(tableName);
table.setColumns(columns);
Collections.sort(constraints, InstallerDb.constraintComparator);
table.setConstraints(constraints);
table.setNotNullOnPrimaryKeyColumns();
return table;
}
/**
* <p>getXFromSQL</p>
*
* @param item a {@link java.lang.String} object.
* @param regex a {@link java.lang.String} object.
* @param itemGroup a int.
* @param returnGroup a int.
* @param description a {@link java.lang.String} object.
* @return a {@link java.lang.String} object.
* @throws java.lang.Exception if any.
*/
public String getXFromSQL(String item, final String regex, final int itemGroup, final int returnGroup, final String description) throws Exception {
item = item.toLowerCase();
final Matcher m = Pattern.compile(regex).matcher(getSql());
while (m.find()) {
if (m.group(itemGroup).toLowerCase().equals(item)) {
return m.group(returnGroup);
}
}
throw new Exception("could not find " + description + " \"" + item + "\"");
}
/**
* <p>findColumn</p>
*
* @param columns a {@link java.util.List} object.
* @param column a {@link java.lang.String} object.
* @return a {@link org.opennms.netmgt.dao.db.Column} object.
*/
public Column findColumn(final List<Column> columns, final String column) {
for (final Column c : columns) {
if (c.getName().equals(column.toLowerCase())) {
return c;
}
}
return null;
}
/**
* <p>tableColumnExists</p>
*
* @param table a {@link java.lang.String} object.
* @param column a {@link java.lang.String} object.
* @return a boolean.
* @throws java.lang.Exception if any.
*/
public boolean tableColumnExists(final String table, final String column) throws Exception {
return (findColumn(getTableColumnsFromDB(table), column) != null);
}
/**
* <p>getTableColumnsFromDB</p>
*
* @param tableName a {@link java.lang.String} object.
* @return a {@link java.util.List} object.
* @throws java.lang.Exception if any.
*/
public List<Column> getTableColumnsFromDB(final String tableName) throws Exception {
final Table table = getTableFromDB(tableName);
if (table == null) {
return null;
}
return table.getColumns();
}
/**
* <p>getTableFromDB</p>
*
* @param tableName a {@link java.lang.String} object.
* @return a {@link org.opennms.netmgt.dao.db.Table} object.
* @throws java.lang.Exception if any.
*/
public Table getTableFromDB(final String tableName) throws Exception {
if (!tableExists(tableName)) {
return null;
}
final Table table = new Table();
table.setName(tableName.toLowerCase());
final List<Column> columns = getColumnsFromDB(tableName);
final List<Constraint> constraints = getConstraintsFromDB(tableName);
Collections.sort(constraints, InstallerDb.constraintComparator);
table.setColumns(columns);
table.setConstraints(constraints);
return table;
}
/**
* <p>tableExists</p>
*
* @param table a {@link java.lang.String} object.
* @return a boolean.
* @throws java.sql.SQLException if any.
*/
public boolean tableExists(final String table) throws SQLException {
final Statement st = getConnection().createStatement();
ResultSet rs;
rs = st.executeQuery("SELECT DISTINCT tablename FROM pg_tables WHERE lower(tablename) = '" + table.toLowerCase() + "'");
return rs.next();
}
/**
* <p>getColumnsFromDB</p>
*
* @param tableName a {@link java.lang.String} object.
* @return a {@link java.util.List} object.
* @throws java.lang.Exception if any.
*/
public List<Column> getColumnsFromDB(final String tableName) throws Exception {
final LinkedList<Column> columns = new LinkedList<Column>();
Statement st = getConnection().createStatement();
String query = "SELECT "
+ " attname, "
+ " format_type(atttypid, atttypmod), "
+ " attnotnull "
+ "FROM "
+ " pg_attribute "
+ "WHERE "
+ " attrelid = (SELECT oid FROM pg_class WHERE relname = '" + tableName.toLowerCase() + "') "
+ " AND "
+ " attnum > 0"
+ " AND "
+ " attisdropped = false";
query = query + " ORDER BY attnum";
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
final Column c = new Column();
c.setName(rs.getString(1));
final String columnType = rs.getString(2);
try {
c.parseColumnType(columnType);
} catch (final Throwable e) {
throw new Exception("Error parsing column type '"
+ columnType + "' for column '" + rs.getString(1)
+ "' in table '" + tableName + "'. Nested: "
+ e.getMessage(), e);
}
c.setNotNull(rs.getBoolean(3));
columns.add(c);
}
rs.close();
st.close();
st = getConnection().createStatement();
query = "SELECT "
+ " attr.attname, "
+ " pg_get_expr(def.adbin, def.adrelid) "
+ "FROM "
+ " pg_attribute attr, "
+ " pg_attrdef def "
+ "WHERE "
+ " attr.attrelid = (SELECT oid FROM pg_class WHERE relname = '" + tableName.toLowerCase() + "') "
+ " AND "
+ " attr.attnum > 0"
+ " AND "
+ " attr.atthasdef = 't' "
+ " AND "
+ " attr.attrelid = def.adrelid"
+ " AND "
+ " attr.attnum = def.adnum"
+ " AND "
+ " attr.attisdropped = false";
rs = st.executeQuery(query);
while (rs.next()) {
Column column = null;
for (final Column c : columns) {
if (c.getName().equals(rs.getString(1))) {
column = c;
break;
}
}
if (column == null) {
throw new Exception("Could not find column '" + rs.getString(1) + "' in original column list when adding default values");
}
column.setDefaultValue(rs.getString(2).replaceAll("'(.*)'::([a-zA-Z ]+)", "'$1'"));
}
rs.close();
st.close();
return columns;
}
/**
* <p>getConstraintsFromDB</p>
*
* @param tableName a {@link java.lang.String} object.
* @return a {@link java.util.List} object.
* @throws java.sql.SQLException if any.
* @throws java.lang.Exception if any.
*/
public List<Constraint> getConstraintsFromDB(final String tableName) throws SQLException, Exception {
final Statement st = getConnection().createStatement();
ResultSet rs;
final LinkedList<Constraint> constraints = new LinkedList<Constraint>();
final String query = "SELECT c.oid, c.conname, c.contype, c.conrelid, "
+ "c.confrelid, a.relname, c.confupdtype, c.confdeltype, c.consrc from pg_class a "
+ "right join pg_constraint c on c.confrelid = a.oid "
+ "where c.conrelid = (select oid from pg_class where relname = '"
+ tableName.toLowerCase() + "') order by c.oid";
rs = st.executeQuery(query);
while (rs.next()) {
final int oid = rs.getInt(1);
final String name = rs.getString(2);
final String type = rs.getString(3);
final int conrelid = rs.getInt(4);
final int confrelid = rs.getInt(5);
final String ftable = rs.getString(6);
final String foreignUpdType = rs.getString(7);
final String foreignDelType = rs.getString(8);
final String checkExpression = rs.getString(9);
final Constraint constraint;
if ("p".equals(type)) {
final List<String> columns = getConstrainedColumnsFromDBForConstraint(oid, conrelid);
constraint = new Constraint(tableName.toLowerCase(), name, columns);
} else if ("f".equals(type)) {
final List<String> columns = getConstrainedColumnsFromDBForConstraint(oid, conrelid);
final List<String> fcolumns = getForeignColumnsFromDBForConstraint(oid, confrelid);
constraint = new Constraint(tableName.toLowerCase(), name, columns, ftable, fcolumns, foreignUpdType, foreignDelType);
} else if ("c".equals(type)) {
constraint = new Constraint(tableName.toLowerCase(), name, checkExpression);
} else {
throw new Exception("Do not support constraint type \"" + type + "\" in constraint \"" + name + "\"");
}
constraints.add(constraint);
}
return constraints;
}
private List<String> getConstrainedColumnsFromDBForConstraint(final int oid, final int conrelid) throws Exception {
final Statement st = getConnection().createStatement();
final ResultSet rs;
final LinkedList<String> columns = new LinkedList<String>();
final String query = "select a.attname from pg_attribute a, pg_constraint c where a.attrelid = c.conrelid and a.attnum = ANY (c.conkey) and c.oid = "
+ oid + " and a.attrelid = " + conrelid;
rs = st.executeQuery(query);
while (rs.next()) {
columns.add(rs.getString(1));
}
rs.close();
st.close();
return columns;
}
private List<String> getForeignColumnsFromDBForConstraint(final int oid, final int confrelid) throws Exception {
final Statement st = getConnection().createStatement();
final ResultSet rs;
final LinkedList<String> columns = new LinkedList<String>();
final String query = "select a.attname from pg_attribute a, pg_constraint c where a.attrelid = c.confrelid and a.attnum = ANY (c.confkey) and c.oid = "
+ oid + " and a.attrelid = " + confrelid;
rs = st.executeQuery(query);
while (rs.next()) {
columns.add(rs.getString(1));
}
rs.close();
st.close();
return columns;
}
/**
* <p>changeTable</p>
*
* @param table a {@link java.lang.String} object.
* @param oldTable a {@link org.opennms.netmgt.dao.db.Table} object.
* @param newTable a {@link org.opennms.netmgt.dao.db.Table} object.
* @throws java.lang.Exception if any.
*/
public void changeTable(final String table, final Table oldTable, final Table newTable) throws Throwable {
assertUserSet();
final List<Column> oldColumns = oldTable.getColumns();
final List<Column> newColumns = newTable.getColumns();
final Statement st = getConnection().createStatement();
final TreeMap<String, ColumnChange> columnChanges = new TreeMap<String, ColumnChange>();
final String[] oldColumnNames = new String[oldColumns.size()];
int i;
if (hasTableChanged(table)) {
return;
}
tableChanged(table);
m_out.println(" - checking table \"" + table
+ "\"... SCHEMA DOES NOT MATCH");
m_out.println(" - differences:");
for (final Constraint newConstraint : newTable.getConstraints()) {
m_out.println("new constraint: " + newConstraint.getTable() + ": " + newConstraint);
}
for (final Constraint oldConstraint : oldTable.getConstraints()) {
m_out.println("old constraint: " + oldConstraint.getTable() + ": " + oldConstraint);
}
for (final Column newColumn : newColumns) {
final Column oldColumn = findColumn(oldColumns, newColumn.getName());
if (oldColumn == null || !newColumn.equals(oldColumn)) {
m_out.println(" - column \"" + newColumn.getName() + "\" is different");
if (m_debug) {
m_out.println(" - old column: " + ((oldColumn == null) ? "null" : oldColumn.toString()));
m_out.println(" - new column: " + newColumn);
}
}
if (!columnChanges.containsKey(newColumn.getName())) {
columnChanges.put(newColumn.getName(), new ColumnChange());
}
final ColumnChange columnChange = columnChanges.get(newColumn.getName());
columnChange.setColumn(newColumn);
/*
* If the new column has a NOT NULL constraint, set a null replace
* value for the column. Throw an exception if it is possible for
* null data to be inserted into the new column. This would happen
* if there is not a null replacement and the column either didn't
* exist before or it did NOT have the NOT NULL constraint before.
*/
if (m_columnReplacements.containsKey(table + "." + newColumn.getName())) {
columnChange.setColumnReplacement(m_columnReplacements.get(table + "." + newColumn.getName()));
}
if (newColumn.isNotNull() && columnChange.getColumnReplacement() == null) {
if (oldColumn == null) {
String message = "Column " + newColumn.getName()
+ " in new table has NOT NULL "
+ "constraint, however this column "
+ "did not exist before and there is "
+ "no change replacement for this column";
if (m_ignore_notnull) {
m_out.println(message + ". Ignoring due to '-N'");
} else {
throw new Exception(message);
}
} else if (!oldColumn.isNotNull()) {
final String message = "Column " + newColumn.getName()
+ " in new table has NOT NULL "
+ "constraint, however this column "
+ "did not have the NOT NULL "
+ "constraint before and there is "
+ "no change replacement for this column";
if (m_ignore_notnull) {
m_out.println(message + ". Ignoring due to '-N'");
} else {
throw new Exception(message);
}
}
}
}
i = 0;
for (final Column oldColumn : oldColumns) {
oldColumnNames[i] = oldColumn.getName();
if (columnChanges.containsKey(oldColumn.getName())) {
ColumnChange columnChange = columnChanges.get(oldColumn.getName());
final Column newColumn = columnChange.getColumn();
if (newColumn.getType().indexOf("timestamp") != -1) {
columnChange.setUpgradeTimestamp(true);
}
} else {
m_out.println(" * WARNING: column \""
+ oldColumn.getName() + "\" exists in the "
+ "database but is not in the new schema. "
+ "REMOVING COLUMN");
}
i++;
}
final String tmpTable = table + "_old_" + System.currentTimeMillis();
try {
if (tableExists(tmpTable)) {
st.execute("DROP TABLE " + tmpTable + " CASCADE");
}
m_out.print(" - creating temporary table... ");
st.execute("CREATE TABLE " + tmpTable + " AS SELECT "
+ StringUtils.arrayToDelimitedString(oldColumnNames, ", ")
+ " FROM " + table);
m_out.println("done");
st.execute("DROP TABLE " + table + " CASCADE");
m_out.print(" - creating new '" + table + "' table... ");
st.execute("CREATE TABLE " + table + " ("
+ getTableCreateFromSQL(table) + ")");
m_out.println("done");
addIndexesForTable(table);
addTriggersForTable(table);
transformData(table, tmpTable, columnChanges, oldColumnNames);
grantAccessToObject(table, 4);
m_out.print(" - optimizing table " + table + "... ");
st.execute("VACUUM ANALYZE " + table);
m_out.println("DONE");
} catch (final Throwable e) {
if (m_no_revert) {
m_out.println("FAILED! Not reverting due to '-R' being "
+ "passed. Old data in " + tmpTable);
throw e;
}
try {
getConnection().rollback();
getConnection().setAutoCommit(true);
if (tableExists(table)) {
st.execute("DROP TABLE " + table + " CASCADE");
}
st.execute("CREATE TABLE " + table + " AS SELECT "
+ StringUtils.arrayToDelimitedString(oldColumnNames, ", ")
+ " FROM " + tmpTable);
st.execute("DROP TABLE " + tmpTable);
} catch (final SQLException se) {
throw new Exception("Got SQLException while trying to "
+ "revert table changes due to original " + "error: "
+ e + "\n" + "SQLException while reverting table: "
+ se, e);
}
m_out.println("FAILED! Old data restored, however indexes and "
+ "constraints on this table were not re-added");
throw e;
}
// We don't care if dropping the tmp table fails since we've
// completed copying it, so it's outside of the try/catch block above.
st.execute("DROP TABLE " + tmpTable);
m_out.println(" - checking table \"" + table
+ "\"... COMPLETED UPDATING TABLE");
}
/*
* Note: every column has a ColumnChange record for it, which lists
* the column name, a null replacement, if any, and the indexes for
* selected rows (for using in ResultSet.getXXX()) and prepared rows
* (PreparedStatement.setObject()).
* Monkey. Make monkey dance.
*/
/**
* <p>transformData</p>
*
* @param table a {@link java.lang.String} object.
* @param oldTable a {@link java.lang.String} object.
* @param columnChanges a {@link java.util.TreeMap} object.
* @param oldColumnNames an array of {@link java.lang.String} objects.
* @throws java.sql.SQLException if any.
* @throws java.text.ParseException if any.
* @throws java.lang.Exception if any.
*/
public void transformData(final String table, final String oldTable, final TreeMap<String, ColumnChange> columnChanges, final String[] oldColumnNames) throws SQLException, ParseException,
Exception {
final Statement st = getConnection().createStatement();
int i;
st.setFetchSize(s_fetch_size);
for (i = 0; i < oldColumnNames.length; i++) {
ColumnChange c = columnChanges.get(oldColumnNames[i]);
if (c != null) {
c.setSelectIndex(i + 1);
}
}
final LinkedList<String> insertColumns = new LinkedList<String>();
final LinkedList<String> questionMarks = new LinkedList<String>();
for (final ColumnChange c : columnChanges.values()) {
c.setColumnType(c.getColumn().getColumnSqlType());
final ColumnChangeReplacement r = c.getColumnReplacement();
if (r == null || c.getSelectIndex() > 0
|| r.addColumnIfColumnIsNew()) {
insertColumns.add(c.getColumn().getName());
questionMarks.add("?");
c.setPrepareIndex(questionMarks.size());
}
}
/*
* Pull everything in from the old table and filter it to update the
* data to any new formats.
*/
m_out.print(" - transforming data into the new table...\r");
ResultSet rs = st.executeQuery("SELECT count(*) FROM " + oldTable);
rs.next();
final long num_rows = rs.getLong(1);
final String order;
if (table.equals("outages")) {
order = " ORDER BY iflostservice";
} else {
order = "";
}
String dbcmd = "SELECT "
+ StringUtils.arrayToDelimitedString(oldColumnNames, ", ")
+ " FROM "
+ oldTable + order;
if (m_debug) {
m_out.println(" - performing select: " + dbcmd);
}
final PreparedStatement select = getConnection().prepareStatement(dbcmd);
select.setFetchSize(s_fetch_size);
dbcmd = "INSERT INTO " + table + " ("
+ StringUtils.collectionToDelimitedString(insertColumns, ", ")
+ ") values ("
+ StringUtils.collectionToDelimitedString(questionMarks, ", ")
+ ")";
if (m_debug) {
m_out.println(" - performing insert: " + dbcmd);
}
final PreparedStatement insert = getConnection().prepareStatement(dbcmd);
rs = select.executeQuery();
getConnection().setAutoCommit(false);
Object obj;
int current_row = 0;
while (rs.next()) {
for (final ColumnChange change : columnChanges.values()) {
final String name = change.getColumn().getName();
if (change.getSelectIndex() == 0 &&
change.hasColumnReplacement()
&& !change.getColumnReplacement().addColumnIfColumnIsNew()) {
continue;
}
if (change.getSelectIndex() > 0) {
obj = rs.getObject(change.getSelectIndex());
if (rs.wasNull()) {
obj = null;
}
} else {
if (m_debug) {
m_out.println(" - don't know what to do "
+ "for \"" + name + "\", prepared column "
+ change.getPrepareIndex()
+ ": setting to null");
}
obj = null;
}
if (obj == null && change.hasColumnReplacement()) {
ColumnChangeReplacement replacement =
change.getColumnReplacement();
obj = replacement.getColumnReplacement(rs, columnChanges);
if (m_debug) {
m_out.println(" - " + name
+ " was NULL but is a "
+ "requires NULL replacement -- "
+ "replacing with '" + obj + "'");
}
}
if (obj != null) {
if (change.isUpgradeTimestamp()
&& !obj.getClass().equals(java.sql.Timestamp.class)) {
if (m_debug) {
m_out.println(" - " + name
+ " is an old-style timestamp");
}
final String newObj = m_dateFormatter.format(m_dateParser.parse((String) obj));
if (m_debug) {
m_out.println(" - " + obj + " -> " + newObj);
}
obj = newObj;
}
if (m_debug) {
m_out.println(" - " + name + " = " + obj);
}
} else {
if (m_debug) {
m_out.println(" - " + name + " = undefined");
}
}
if (obj == null) {
insert.setNull(change.getPrepareIndex(),
change.getColumnType());
} else {
insert.setObject(change.getPrepareIndex(), obj);
}
}
try {
insert.execute();
} catch (final SQLException e) {
final SQLException ex = new SQLException(
"Statement.execute() threw an "
+ "SQLException while inserting a row: "
+ "\""
+ insert.toString()
+ "\". "
+ "Original exception: "
+ e.toString(),
e.getSQLState(),
e.getErrorCode());
ex.setNextException(e);
throw ex;
}
current_row++;
if ((current_row % 20) == 0) {
System.err.print(" - transforming data into the new "
+ "table... "
+ (int) Math.floor((current_row * 100.0) / num_rows)
+ "% [" + m_spins[(current_row / 20) % m_spins.length]
+ "]\r");
}
}
rs.close();
select.close();
insert.close();
getConnection().commit();
getConnection().setAutoCommit(true);
if (table.equals("events") && num_rows == 0) {
st.execute("INSERT INTO events (eventid, eventuei, eventtime, "
+ "eventsource, eventdpname, eventcreatetime, "
+ "eventseverity, eventlog, eventdisplay) values "
+ "(0, 'http://uei.opennms.org/dummyevent', now(), "
+ "'OpenNMS.Eventd', 'localhost', now(), 1, 'Y', 'Y')");
}
st.close();
m_out.println(" - transforming data into the new table... "
+ "DONE ");
}
/**
* <p>checkOldTables</p>
*
* @throws java.sql.SQLException if any.
* @throws org.opennms.netmgt.dao.db.BackupTablesFoundException if any.
*/
public void checkOldTables() throws SQLException, BackupTablesFoundException {
final Statement st = getConnection().createStatement();
final ResultSet rs = st.executeQuery("SELECT relname FROM pg_class "
+ "WHERE relkind = 'r' AND " + "relname LIKE '%_old_%'");
LinkedList<String> oldTables = new LinkedList<String>();
m_out.print("- checking database for old backup tables... ");
while (rs.next()) {
oldTables.add(rs.getString(1));
}
rs.close();
st.close();
if (oldTables.size() == 0) {
// No problems, so just print "NONE" and return.
m_out.println("NONE");
return;
}
throw new BackupTablesFoundException(oldTables);
}
/**
* <p>getForeignKeyConstraints</p>
*
* @return a {@link java.util.List} object.
* @throws java.lang.Exception if any.
*/
public List<Constraint> getForeignKeyConstraints() throws Exception {
if (m_constraints == null) {
m_constraints = new LinkedList<Constraint>();
for (final String table : getTableNames()) {
final String tableLower = table.toLowerCase();
for (final Constraint constraint : getTableFromSQL(tableLower).getConstraints()) {
if (constraint.getType() == Constraint.FOREIGN_KEY) {
m_constraints.add(constraint);
}
}
}
}
return m_constraints;
}
/**
* <p>checkConstraints</p>
*
* @throws java.lang.Exception if any.
*/
public void checkConstraints() throws Exception {
final List<Constraint> constraints = getForeignKeyConstraints();
m_out.println("- checking for rows that violate constraints...");
for (final Constraint constraint : constraints) {
m_out.print(" - checking for rows that violate constraint '" + constraint.getName() + "'... ");
checkConstraint(constraint);
m_out.println("DONE");
}
m_out.println("- checking for rows that violate constraints... DONE");
}
/**
* <p>checkConstraint</p>
*
* @param constraint a {@link org.opennms.netmgt.dao.db.Constraint} object.
* @throws java.lang.Exception if any.
*/
public void checkConstraint(final Constraint constraint) throws Exception {
final String name = constraint.getName();
final String table = constraint.getTable();
final List<String> columns = constraint.getColumns();
final String ftable = constraint.getForeignTable();
final List<String> fcolumns = constraint.getForeignColumns();
if (!tableExists(table)) {
// The constrained table does not exist
return;
}
for (final String column : columns) {
if (!tableColumnExists(table, column)) {
// This constrained column does not exist
return;
}
}
// XXX Not sure if it's okay to leave this out
/*
* if (table.equals("usersNotified") && column.equals("id")) { //
* m_out.print("Skipping usersNotified.id"); continue; }
*/
// String partialQuery = "FROM " + table + " WHERE "
// + getForeignConstraintWhere(table, columns, ftable, fcolumns);
final String partialQuery = getJoinForRowsThatFailConstraint(table, columns, ftable, fcolumns);
final Statement st = getConnection().createStatement();
ResultSet rs;
final String query = "SELECT count(*) " + partialQuery;
try {
rs = st.executeQuery(query);
} catch (final SQLException e) {
throw new Exception("Failed to execute query '" + query + "'. "
+ "Nested exception: " + e.getMessage(), e);
}
rs.next();
int count = rs.getInt(1);
rs.close();
if (count != 0) {
rs = st.executeQuery("SELECT count(*) FROM " + table);
rs.next();
int total = rs.getInt(1);
rs.close();
st.close();
throw new Exception("Table " + table + " contains " + count
+ " rows " + "(out of " + total
+ ") that violate new constraint " + name + ". "
+ "See the install guide for details "
+ "on how to correct this problem. You can execute this "
+ "SQL query to see a list of the rows that violate the "
+ "constraint:\n"
+ "SELECT * " + partialQuery);
}
st.close();
}
private String getJoinForRowsThatFailConstraint(final String table, final List<String> columns, final String ftable, final List<String> fcolumns) throws Exception {
final String notNulls = notNullWhereClause(table, columns);
final String noForeign = "FROM " + table + " WHERE " + notNulls;
if (!tableExists(ftable)) {
return noForeign;
}
for (final String fcolumn : fcolumns) {
if (!tableColumnExists(ftable, fcolumn)) {
return noForeign;
}
}
String partialQuery = "FROM " + table + " LEFT JOIN " + ftable + " ON (";
for (int i = 0; i < columns.size(); i++) {
final String column = columns.get(i);
final String fcolumn = fcolumns.get(i);
if (i != 0) {
partialQuery += " AND ";
}
partialQuery += table+'.'+column+" = "+ftable+'.'+fcolumn;
}
partialQuery += ") WHERE "+ftable+'.'+fcolumns.get(0)+" is NULL AND "+ notNulls;
return partialQuery;
}
/**
* <p>getForeignConstraintWhere</p>
*
* @param table a {@link java.lang.String} object.
* @param columns a {@link java.util.List} object.
* @param ftable a {@link java.lang.String} object.
* @param fcolumns a {@link java.util.List} object.
* @return a {@link java.lang.String} object.
* @throws java.lang.Exception if any.
*/
public String getForeignConstraintWhere(final String table, final List<String> columns, final String ftable, final List<String> fcolumns) throws Exception {
final String notNulls = notNullWhereClause(table, columns);
if (!tableExists(ftable)) {
return notNulls;
}
for (final String fcolumn : fcolumns) {
if (!tableColumnExists(ftable, fcolumn)) {
return notNulls;
}
}
return notNulls + " AND ( "
+ StringUtils.collectionToDelimitedString(tableColumnList(table, columns), ", ")
+ " ) NOT IN (SELECT "
+ StringUtils.collectionToDelimitedString(tableColumnList(ftable, fcolumns), ", ")
+ " FROM " + ftable + ")";
}
/**
* <p>notNullWhereClause</p>
*
* @param table a {@link java.lang.String} object.
* @param columns a {@link java.util.List} object.
* @return a {@link java.lang.String} object.
*/
public String notNullWhereClause(final String table, final List<String> columns) {
final List<String> isNotNulls = new ArrayList<String>(columns.size());
for (final String column : columns) {
isNotNulls.add(table + "." + column + " IS NOT NULL");
}
return StringUtils.collectionToDelimitedString(isNotNulls, " AND ");
}
/**
* <p>tableColumnList</p>
*
* @param table a {@link java.lang.String} object.
* @param columns a {@link java.util.List} object.
* @return a {@link java.util.List} object.
*/
public List<String> tableColumnList(final String table, final List<String> columns) {
final List<String> tableColumns = new ArrayList<String>(columns.size());
for (final String column : columns) {
tableColumns.add(table + "." + column);
}
return tableColumns;
}
/**
* <p>fixConstraint</p>
*
* @param constraintName a {@link java.lang.String} object.
* @param removeRows a boolean.
* @throws java.lang.Exception if any.
*/
public void fixConstraint(final String constraintName, final boolean removeRows) throws Exception {
final List<Constraint> constraints = getForeignKeyConstraints();
m_out.print("- fixing rows that violate constraint " + constraintName + "... ");
for (final Constraint c : constraints) {
if (constraintName.equals(c.getName())) {
m_out.println(fixConstraint(c, removeRows));
return;
}
}
throw new Exception("Did not find constraint "
+ constraintName + " in the database.");
}
/**
* <p>fixConstraint</p>
*
* @param constraint a {@link org.opennms.netmgt.dao.db.Constraint} object.
* @param removeRows a boolean.
* @return a {@link java.lang.String} object.
* @throws java.lang.Exception if any.
*/
public String fixConstraint(final Constraint constraint, final boolean removeRows) throws Exception {
final String table = constraint.getTable();
final List<String> columns = constraint.getColumns();
final String ftable = constraint.getForeignTable();
final List<String> fcolumns = constraint.getForeignColumns();
if (!tableExists(table)) {
throw new Exception("Constraint " + constraint.getName()
+ " is on table " + table + ", but table does "
+ "not exist (so fixing this constraint does "
+ "nothing).");
}
for (final String column : columns) {
if (!tableColumnExists(table, column)) {
throw new Exception("Constraint " + constraint.getName()
+ " constrains column " + column
+ " of table " + table
+ ", but column does "
+ "not exist (so fixing this constraint "
+ "does nothing).");
}
}
// String where = getForeignConstraintWhere(table, columns, ftable,
// fcolumns);
String tuple = "";
for(int i = 0; i < columns.size(); i++) {
if (i != 0) {
tuple += ", ";
}
tuple += table+'.'+columns.get(i);
}
final String where = "( "+ tuple + ") IN ( SELECT " + tuple + " " +
getJoinForRowsThatFailConstraint(table, columns, ftable, fcolumns) +")";
String query;
String change_text;
if (removeRows) {
query = "DELETE FROM " + table + " WHERE " + where;
change_text = "DELETED";
} else {
final List<String> sets = new ArrayList<String>(columns.size());
for (final String column : columns) {
sets.add(column + " = NULL");
}
query = "UPDATE " + table + " SET "
+ StringUtils.collectionToDelimitedString(sets, ", ") + " "
+ "WHERE " + where;
change_text = "UPDATED";
}
final Statement st = getConnection().createStatement();
final int num = st.executeUpdate(query);
return change_text + " " + num + (num == 1 ? " ROW" : " ROWS");
}
/**
* <p>databaseUserExists</p>
*
* @return a boolean.
* @throws java.sql.SQLException if any.
*/
public boolean databaseUserExists() throws SQLException {
assertUserSet();
boolean exists;
final Statement st = getAdminConnection().createStatement();
final ResultSet rs = st.executeQuery("SELECT usename FROM pg_user WHERE "
+ "usename = '" + m_user + "'");
exists = rs.next();
rs.close();
st.close();
return exists;
}
/**
* <p>databaseSetUser</p>
*
* @throws java.sql.SQLException if any.
*/
public void databaseSetUser() throws SQLException {
final ResultSet rs = getAdminConnection().getMetaData().getTables(null, "public", "%", null);
final HashSet<String> objects = new HashSet<String>();
while (rs.next()) {
objects.add(rs.getString("TABLE_NAME"));
}
final PreparedStatement st = getAdminConnection().prepareStatement("ALTER TABLE ? OWNER TO ?");
for (final String objName : objects) {
st.setString(1, objName);
st.setString(2, m_user);
st.execute();
}
st.close();
}
/**
* <p>databaseAddUser</p>
*
* @throws java.sql.SQLException if any.
*/
@Deprecated
public void databaseAddUser() throws SQLException {
assertUserSet();
final Statement st = getAdminConnection().createStatement();
st.execute("CREATE USER " + m_user + " WITH PASSWORD '" + m_pass + "' CREATEDB CREATEUSER");
}
/**
* <p>databaseDBExists</p>
*
* @return a boolean.
* @throws java.sql.SQLException if any.
*/
public boolean databaseDBExists() throws SQLException {
boolean exists;
final Statement st = getAdminConnection().createStatement();
final ResultSet rs = st.executeQuery("SELECT datname from pg_database "
+ "WHERE datname = '" + getDatabaseName() + "'");
exists = rs.next();
rs.close();
st.close();
return exists;
}
/**
* <p>databaseAddDB</p>
*
* @throws java.lang.Exception if any.
*/
@Deprecated
public void databaseAddDB() throws Exception {
assertUserSet();
m_out.print("- creating database '" + getDatabaseName() + "'... ");
final Statement st = getAdminConnection().createStatement();
st.execute("CREATE DATABASE \"" + getDatabaseName() + "\" WITH ENCODING='UNICODE'");
st.execute("GRANT ALL ON DATABASE \"" + getDatabaseName() + "\" TO \"" + m_user + "\"");
st.close();
m_out.print("DONE");
}
/**
* <p>databaseRemoveDB</p>
*
* @throws java.sql.SQLException if any.
*/
public void databaseRemoveDB() throws SQLException {
assertUserSet();
m_out.print("- removing database '" + getDatabaseName() + "'... ");
final Statement st = getAdminConnection().createStatement();
st.execute("DROP DATABASE \"" + getDatabaseName() + "\"");
st.close();
m_out.print("DONE");
}
/**
* <p>addIndexesForTable</p>
*
* @param table a {@link java.lang.String} object.
* @throws java.sql.SQLException if any.
*/
public void addIndexesForTable(final String table) throws SQLException {
final List<Index> indexes = getIndexDao().getIndexesForTable(table.toLowerCase());
for (final Index index : indexes) {
m_out.print(" - checking index '" + index.getName()
+ "' on this table... ");
if (!index.isOnDatabase(getConnection())) {
index.addToDatabase(getConnection());
}
m_out.println("DONE");
}
}
/**
* <p>grantAccessToObject</p>
*
* @param object a {@link java.lang.String} object.
* @param indent a int.
* @throws java.sql.SQLException if any.
*/
public void grantAccessToObject(final String object, final int indent) throws SQLException {
assertUserSet();
for (int i = 0; i < indent; i++) {
m_out.print(" ");
}
m_out.print("- granting access to '" + object + "' for user '" + m_user + "'... ");
final Statement st = getConnection().createStatement();
try {
st.execute("GRANT ALL ON " + object + " TO " + m_user);
} finally {
st.close();
}
m_out.println("DONE");
}
/**
* <p>fixData</p>
*
* @throws java.lang.Exception if any.
*/
public void fixData() throws Exception {
final Statement st = getConnection().createStatement();
st.execute("UPDATE ipinterface SET issnmpprimary='N' "
+ "WHERE issnmpprimary IS NULL");
st.execute("UPDATE service SET servicename='SSH' "
+ "WHERE servicename='OpenSSH'");
st.execute("UPDATE snmpinterface SET snmpipadentnetmask=NULL");
}
// XXX This causes the following Postgres error:
// ERROR: duplicate key violates unique constraint "pk_dpname"
/**
* <p>insertData</p>
*
* @throws java.lang.Exception if any.
*/
public void insertData() throws Exception {
for (final String table : getInserts().keySet()) {
Status status = Status.OK;
m_out.print("- inserting initial table data for \"" + table + "\"... ");
// XXX: criteria are checked for all inserts before
// any of them are done so inserts don't interfere with
// other inserts criteria
final List<Insert> toBeInserted = new LinkedList<Insert>();
for (final Insert insert : getInserts().get(table)) {
if (insert.isCriteriaMet()) {
toBeInserted.add(insert);
}
}
for(final Insert insert : toBeInserted) {
status = status.combine(insert.doInsert());
}
m_out.println(status);
}
}
/**
* <p>checkUnicode</p>
*
* @throws java.lang.Exception if any.
*/
public void checkUnicode() throws Exception {
assertUserSet();
m_out.print("- checking if database \"" + getDatabaseName() + "\" is unicode... ");
Statement st = null;
ResultSet rs = null;
try {
try {
st = getAdminConnection().createStatement();
try {
rs = st.executeQuery("SELECT encoding FROM pg_database WHERE LOWER(datname)='" + getDatabaseName().toLowerCase() + "'");
if (rs.next()) {
if (rs.getInt(1) == 5 || rs.getInt(1) == 6) {
m_out.println("ALREADY UNICODE");
return;
}
}
m_out.println("NOT UNICODE");
throw new Exception("OpenNMS requires a Unicode database. Please delete and recreate your\ndatabase and try again.");
} finally {
if (rs != null) {
rs.close();
}
}
} finally {
if (st != null) {
st.close();
}
}
} finally {
this.disconnect();
}
}
/**
* <p>checkIndexUniqueness</p>
*
* @throws java.lang.Exception if any.
*/
public void checkIndexUniqueness() throws Exception {
final Collection<Index> indexes = getIndexDao().getAllIndexes();
final Statement st = getConnection().createStatement();
for (final Index index : indexes) {
if (!index.isUnique()) {
continue;
}
if (!tableExists(index.getTable())) {
continue;
}
boolean missingColumn = false;
for (final String column : index.getColumns()) {
if (!tableColumnExists(index.getTable(), column)) {
missingColumn = true;
}
}
if (missingColumn) {
continue;
}
final String query = index.getIndexUniquenessQuery();
if (query == null) {
continue;
}
final String countQuery = query.replaceFirst("(?i)\\s(\\S+)\\s+FROM",
" count(\\1) FROM").replaceFirst("(?i)\\s*ORDER\\s+BY\\s+[^()]+$", "");
final ResultSet rs = st.executeQuery(countQuery);
rs.next();
final int count = rs.getInt(1);
rs.close();
if (count > 0) {
st.close();
throw new Exception("Unique index '" + index.getName() + "' "
+ "cannot be added to table '" +
index.getTable() + "' because " + count
+ " rows are not unique. See the "
+ "install guide for details on how to "
+ "correct this problem. You can use the "
+ "following SQL to see which rows are not "
+ "unique:\n"
+ query);
}
}
st.close();
}
/**
* <p>getTableColumnsFromSQL</p>
*
* @param tableName a {@link java.lang.String} object.
* @return a {@link java.util.List} object.
* @throws java.lang.Exception if any.
*/
public List<Column> getTableColumnsFromSQL(final String tableName) throws Exception {
return getTableFromSQL(tableName).getColumns();
}
/**
* <p>getTableCreateFromSQL</p>
*
* @param table a {@link java.lang.String} object.
* @return a {@link java.lang.String} object.
* @throws java.lang.Exception if any.
*/
public String getTableCreateFromSQL(final String table) throws Exception {
return getXFromSQL(table, "(?i)\\bcreate table\\s+['\"]?(\\S+)['\"]?"
+ "\\s+\\((.+?)\\);", 1, 2, "table");
}
/**
* <p>getIndexFromSQL</p>
*
* @param index a {@link java.lang.String} object.
* @return a {@link java.lang.String} object.
* @throws java.lang.Exception if any.
*/
public String getIndexFromSQL(final String index) throws Exception {
return getXFromSQL(index, "(?i)\\b(create (?:unique )?index\\s+"
+ "['\"]?(\\S+)['\"]?\\s+.+?);", 2, 1, "index");
}
/**
* <p>getFunctionFromSQL</p>
*
* @param function a {@link java.lang.String} object.
* @return a {@link java.lang.String} object.
* @throws java.lang.Exception if any.
*/
public String getFunctionFromSQL(final String function) throws Exception {
return getXFromSQL(function, "(?is)\\bcreate function\\s+"
+ "['\"]?(\\S+)['\"]?\\s+"
+ "(.+? language ['\"]?\\w+['\"]?);", 1, 2, "function");
}
/**
* <p>getLanguageFromSQL</p>
*
* @param language a {@link java.lang.String} object.
* @return a {@link java.lang.String} object.
* @throws java.lang.Exception if any.
*/
public String getLanguageFromSQL(final String language) throws Exception {
return getXFromSQL(language, "(?is)\\bcreate trusted procedural "
+ "language\\s+['\"]?(\\S+)['\"]?\\s+(.+?);", 1, 2,
"language");
}
private void assertUserSet() {
Assert.state(m_user != null, "postgresOpennmsUser property has not been set");
}
private Connection getConnection() throws SQLException {
if (m_connection == null) {
initializeConnection();
}
return m_connection;
}
private void initializeConnection() throws SQLException {
Assert.state(m_dataSource != null, "dataSource property has not been set");
try {
m_connection = getDataSource().getConnection();
} catch (final SQLException e) {
rethrowDatabaseConnectionException(getDataSource(), e, "Could not get a connection to the OpenNMS database.");
}
}
/**
* <p>closeConnection</p>
*
* @throws java.sql.SQLException if any.
*/
public void closeConnection() throws SQLException {
if (m_connection == null) {
return;
}
m_connection.close();
m_connection = null;
}
private Connection getAdminConnection() throws SQLException {
if (m_adminConnection == null) {
initializeAdminConnection();
}
return m_adminConnection;
}
private void initializeAdminConnection() throws SQLException {
Assert.state(m_adminDataSource != null, "adminDataSource property has not been set");
try {
m_adminConnection = getAdminDataSource().getConnection();
} catch (final SQLException e) {
rethrowDatabaseConnectionException(getAdminDataSource(), e, "Could not get an administrative connection to the database.");
}
}
/**
* <p>closeAdminConnection</p>
*
* @throws java.sql.SQLException if any.
*/
public void closeAdminConnection() throws SQLException {
if (m_adminConnection == null) {
return;
}
m_adminConnection.close();
m_adminConnection = null;
}
/**
* Close all connections to the database.
*
* @throws java.sql.SQLException if any.
*/
public void disconnect() throws SQLException {
this.closeColumnReplacements();
this.closeConnection();
this.closeAdminConnection();
}
private void rethrowDatabaseConnectionException(final DataSource ds, final SQLException e, final String msg) throws SQLException {
final SQLException newE = new DatabaseConnectionException(msg + " Is the database running, listening for TCP connections, and allowing us to connect and authenticate from localhost? Tried connecting to database specified by data source " + ds.toString() + ". Original error: " + e);
newE.initCause(e);
throw newE;
}
/**
* <p>setCreateSqlLocation</p>
*
* @param createSqlLocation a {@link java.lang.String} object.
*/
public void setCreateSqlLocation(final String createSqlLocation) {
m_createSqlLocation = createSqlLocation;
}
/**
* <p>getCreateSqlLocation</p>
*
* @return a {@link java.lang.String} object.
*/
public String getCreateSqlLocation() {
return m_createSqlLocation;
}
/**
* <p>getTableNames</p>
*
* @return a {@link java.util.List} object.
*/
public List<String> getTableNames() {
return m_tables;
}
/**
* <p>getSequenceNames</p>
*
* @return a {@link java.util.List} object.
*/
public List<String> getSequenceNames() {
return m_sequences;
}
/**
* <p>getSequenceMapping</p>
*
* @param sequence a {@link java.lang.String} object.
* @return an array of {@link java.lang.String} objects.
*/
public String[] getSequenceMapping(final String sequence) {
return m_seqmapping.get(sequence);
}
/**
* <p>getIndexDao</p>
*
* @return a {@link org.opennms.netmgt.dao.db.IndexDao} object.
*/
public IndexDao getIndexDao() {
return m_indexDao;
}
/**
* <p>getInserts</p>
*
* @return a {@link java.util.Map} object.
*/
public Map<String, List<Insert>> getInserts() {
return Collections.unmodifiableMap(m_inserts);
}
/**
* <p>getSql</p>
*
* @return a {@link java.lang.String} object.
*/
public String getSql() {
return m_sql;
}
/**
* <p>hasTableChanged</p>
*
* @param table a {@link java.lang.String} object.
* @return a boolean.
*/
public boolean hasTableChanged(final String table) {
return m_changed.contains(table);
}
/**
* <p>tableChanged</p>
*
* @param table a {@link java.lang.String} object.
*/
public void tableChanged(final String table) {
m_changed.add(table);
}
/**
* <p>setOutputStream</p>
*
* @param out a {@link java.io.PrintStream} object.
*/
public void setOutputStream(final PrintStream out) {
m_out = out;
}
/**
* <p>getTriggerDao</p>
*
* @return a {@link org.opennms.netmgt.dao.db.TriggerDao} object.
*/
public TriggerDao getTriggerDao() {
return m_triggerDao;
}
/**
* <p>setStoredProcedureDirectory</p>
*
* @param directory a {@link java.lang.String} object.
*/
public void setStoredProcedureDirectory(final String directory) {
m_storedProcedureDirectory = directory;
}
/**
* <p>getStoredProcedureDirectory</p>
*
* @return a {@link java.lang.String} object.
*/
public String getStoredProcedureDirectory() {
return m_storedProcedureDirectory;
}
/**
* <p>setDataSource</p>
*
* @param dataSource a {@link javax.sql.DataSource} object.
*/
public void setDataSource(final DataSource dataSource) {
m_dataSource = dataSource;
}
/**
* <p>getDataSource</p>
*
* @return a {@link javax.sql.DataSource} object.
*/
public DataSource getDataSource() {
return m_dataSource;
}
/**
* <p>setAdminDataSource</p>
*
* @param dataSource a {@link javax.sql.DataSource} object.
*/
public void setAdminDataSource(final DataSource dataSource) {
m_adminDataSource = dataSource;
}
/**
* <p>getAdminDataSource</p>
*
* @return a {@link javax.sql.DataSource} object.
*/
public DataSource getAdminDataSource() {
return m_adminDataSource;
}
/**
* <p>setForce</p>
*
* @param force a boolean.
*/
public void setForce(final boolean force) {
m_force = force;
}
/**
* <p>getForce</p>
*
* @return a boolean.
*/
public boolean getForce() {
return m_force;
}
/**
* <p>setDebug</p>
*
* @param debug a boolean.
*/
public void setDebug(final boolean debug) {
m_debug = debug;
}
/**
* <p>getDebug</p>
*
* @return a boolean.
*/
public boolean getDebug() {
return m_debug;
}
/**
* <p>addColumnReplacement</p>
*
* @param tableColumn a {@link java.lang.String} object.
* @param replacement a {@link org.opennms.netmgt.dao.db.ColumnChangeReplacement} object.
*/
public void addColumnReplacement(final String tableColumn, final ColumnChangeReplacement replacement) {
m_columnReplacements.put(tableColumn, replacement);
}
/**
* <p>setIgnoreNotNull</p>
*
* @param ignoreNotNull a boolean.
*/
public void setIgnoreNotNull(final boolean ignoreNotNull) {
m_ignore_notnull = ignoreNotNull;
}
/**
* <p>getDatabaseName</p>
*
* @return a {@link java.lang.String} object.
*/
public String getDatabaseName() {
return m_databaseName;
}
/**
* <p>setDatabaseName</p>
*
* @param name a {@link java.lang.String} object.
*/
public void setDatabaseName(final String name) {
m_databaseName = name;
}
public String getSchemaName() {
return m_schemaName;
}
public void setSchemaName(final String name) {
m_schemaName = name;
}
/**
* <p>setNoRevert</p>
*
* @param noRevert a boolean.
*/
public void setNoRevert(final boolean noRevert) {
m_no_revert = noRevert;
}
/**
* <p>setPostgresOpennmsUser</p>
*
* @param user a {@link java.lang.String} object.
*/
public void setPostgresOpennmsUser(final String user) {
m_user = user;
}
/**
* <p>getPostgresOpennmsUser</p>
*
* @return a {@link java.lang.String} object.
*/
public String getPostgresOpennmsUser() {
return m_user;
}
/**
* <p>setPostgresOpennmsPassword</p>
*
* @param password a {@link java.lang.String} object.
*/
public void setPostgresOpennmsPassword(final String password) {
m_pass = password;
}
/**
* <p>getPostgresOpennmsPassword</p>
*
* @return a {@link java.lang.String} object.
*/
public String getPostgresOpennmsPassword() {
return m_pass;
}
/**
* <p>setPostgresIpLikeLocation</p>
*
* @param location a {@link java.lang.String} object.
*/
public void setPostgresIpLikeLocation(final String location) {
if (location != null) {
final File iplike = new File(location);
if (!iplike.exists()) {
m_out.println("WARNING: missing " + location + ": OpenNMS will use a slower stored procedure if the native library is not available");
}
}
m_pg_iplike = location;
}
/**
* <p>getPgIpLikeLocation</p>
*
* @return a {@link java.lang.String} object.
*/
public String getPgIpLikeLocation() {
return m_pg_iplike;
}
/**
* <p>setPostgresPlPgsqlLocation</p>
*
* @param location a {@link java.lang.String} object.
*/
public void setPostgresPlPgsqlLocation(final String location) {
if (location != null) {
final File plpgsql = new File(location);
if (!plpgsql.exists()) {
m_out.println("FATAL: missing " + location + ": Unable to set up even the slower IPLIKE stored procedure without PL/PGSQL language support");
}
}
m_pg_plpgsql = location;
}
/**
* <p>getPgPlPgsqlLocation</p>
*
* @return a {@link java.lang.String} object.
*/
public String getPgPlPgsqlLocation() {
return m_pg_plpgsql;
}
/**
* <p>isPgPlPgsqlLibPresent</p>
*
* @return a boolean.
*/
public boolean isPgPlPgsqlLibPresent() {
if (m_pg_plpgsql == null)
return false;
final File plpgsqlLib = new File(m_pg_plpgsql);
if (plpgsqlLib.exists() && plpgsqlLib.canRead())
return true;
return false;
}
/**
* <p>addColumnReplacements</p>
*
* @throws java.sql.SQLException if any.
*/
public void addColumnReplacements() throws SQLException {
/*
* The DEFAULT value for these columns will take care of these primary keys
*/
addColumnReplacement("snmpinterface.id", new DoNotAddColumnReplacement());
addColumnReplacement("ipinterface.id", new DoNotAddColumnReplacement());
addColumnReplacement("ifservices.id", new DoNotAddColumnReplacement());
addColumnReplacement("acks.id", new DoNotAddColumnReplacement());
addColumnReplacement("assets.id", new DoNotAddColumnReplacement());
addColumnReplacement("atinterface.id", new DoNotAddColumnReplacement());
addColumnReplacement("datalinkinterface.id", new DoNotAddColumnReplacement());
addColumnReplacement("element.id", new DoNotAddColumnReplacement());
// Triggers will take care of these surrogate foreign keys
addColumnReplacement("ipinterface.snmpinterfaceid", new DoNotAddColumnReplacement());
addColumnReplacement("ifservices.ipinterfaceid", new DoNotAddColumnReplacement());
addColumnReplacement("outages.ifserviceid", new DoNotAddColumnReplacement());
addColumnReplacement("events.eventsource", new EventSourceReplacement());
addColumnReplacement("outages.outageid", new AutoIntegerReplacement(1));
addColumnReplacement("snmpinterface.nodeid", new RowHasBogusDataReplacement("snmpInterface", "nodeId"));
addColumnReplacement("snmpinterface.snmpifindex", new RowHasBogusDataReplacement("snmpInterface", "snmpIfIndex"));
addColumnReplacement("ipinterface.nodeid", new RowHasBogusDataReplacement("ipInterface", "nodeId"));
addColumnReplacement("ipinterface.ipaddr", new RowHasBogusDataReplacement("ipInterface", "ipAddr"));
addColumnReplacement("ifservices.nodeid", new RowHasBogusDataReplacement("ifservices", "nodeId"));
addColumnReplacement("ifservices.ipaddr", new RowHasBogusDataReplacement("ifservices", "ipaddr"));
addColumnReplacement("ifservices.serviceid", new RowHasBogusDataReplacement("ifservices", "serviceId"));
addColumnReplacement("outages.nodeid", new RowHasBogusDataReplacement("outages", "nodeId"));
addColumnReplacement("outages.serviceid", new RowHasBogusDataReplacement("outages", "serviceId"));
addColumnReplacement("usersnotified.id", new NextValReplacement("userNotifNxtId", getDataSource()));
addColumnReplacement("alarms.x733probablecause", new FixedIntegerReplacement(0));
/*
* - checking table "alarms"... SCHEMA DOES NOT MATCH
* - differences:
* new constraint: alarms: constraint fk_eventidak2 foreign key (lasteventid) references events (eventid) on delete cascade
* new constraint: alarms: constraint pk_alarmid primary key (alarmid)
* - column "alarmid" is different
* Exception in thread "main" java.lang.Exception: Error changing table 'alarms'. Nested exception: Column alarmid in new table has NOT NULL constraint, however this column did not have the NOT NULL constraint before and there is no change replacement for this column
* at org.opennms.netmgt.dao.db.InstallerDb.createTables(InstallerDb.java:785)
* at org.opennms.install.Installer.install(Installer.java:251)
* at org.opennms.install.Installer.main(Installer.java:778)
* Caused by: java.lang.Exception: Column alarmid in new table has NOT NULL constraint, however this column did not have the NOT NULL constraint before and there is no change replacement for this column
* at org.opennms.netmgt.dao.db.InstallerDb.changeTable(InstallerDb.java:1224)
* at org.opennms.netmgt.dao.db.InstallerDb.createTables(InstallerDb.java:783)
*
* Not sure if this is the proper fix, but it seems like in some cases folks have alarms
* without an alarmid properly set. Should it have a default?
*/
addColumnReplacement("alarms.alarmid", new NextValReplacement("alarmsNxtId", getDataSource()));
/* linkd updates */
addColumnReplacement("vlan.id", new DoNotAddColumnReplacement());
addColumnReplacement("stpnode.id", new DoNotAddColumnReplacement());
addColumnReplacement("stpinterface.id", new DoNotAddColumnReplacement());
addColumnReplacement("iprouteinterface.id", new DoNotAddColumnReplacement());
}
/**
* <p>closeColumnReplacements</p>
*
* @throws java.sql.SQLException if any.
*/
public void closeColumnReplacements() throws SQLException {
for (ColumnChangeReplacement r : m_columnReplacements.values()) {
r.close();
}
}
enum Status {
OK,
SKIPPED,
EXISTS;
Status combine(Status s) {
if (this.ordinal() > s.ordinal()) {
return this;
} else {
return s;
}
}
}
public class Insert {
private final String m_table;
private final String m_insertStatement;
private final String m_criteria;
public Insert(final String table, final String line, final String criteria) {
m_table = table;
m_insertStatement = line;
m_criteria = criteria;
}
public String getTable() {
return m_table;
}
public String getCriteria() {
return m_criteria;
}
public String getInsertStatement() {
return m_insertStatement;
}
Status execute() throws SQLException {
if (isCriteriaMet()) {
return doInsert();
} else {
return Status.SKIPPED;
}
}
private boolean isCriteriaMet() throws SQLException {
if (getCriteria() == null) {
return true;
}
Statement st = null;
try {
st = getConnection().createStatement();
ResultSet rs = null;
try {
rs = st.executeQuery(getCriteria());
// if we find a row the first column must be 't'
if (rs.next()) {
return rs.getBoolean(1);
}
// other wise return false
return false;
} finally {
if (rs != null) {
rs.close();
}
}
} finally {
if (st != null) {
st.close();
}
}
}
private Status doInsert() throws SQLException {
Statement st = null;
try {
st = getConnection().createStatement();
st.execute(getInsertStatement());
} catch (final SQLException e) {
/*
* SQL Status codes: 23505: ERROR: duplicate key violates
* unique constraint "%s"
*/
if (e.toString().indexOf("duplicate key") != -1
|| "23505".equals(e.getSQLState())) {
return Status.EXISTS;
} else {
throw e;
}
} finally {
if (st != null) {
st.close();
}
}
return Status.OK;
}
}
/**
* <p>vacuumDatabase</p>
*
* @param full a boolean.
* @throws java.sql.SQLException if any.
*/
public void vacuumDatabase(final boolean full) throws SQLException {
final Statement st = getConnection().createStatement();
m_out.print("- optimizing database (VACUUM ANALYZE)... ");
st.execute("VACUUM ANALYZE");
m_out.println("OK");
if (full) {
m_out.print("- recovering database disk space (VACUUM FULL)... ");
st.execute("VACUUM FULL");
m_out.println("OK");
}
}
}