From 97fe11b3e90ead341cecae5365340962c3896dea Mon Sep 17 00:00:00 2001 From: "Tschaen, Brendan" Date: Wed, 22 May 2019 15:29:16 -0400 Subject: Move trigger creation to startup Change-Id: I0f06d0d704914299ff8dd6dbda2517fd7518507a Issue-ID: MUSIC-396 Signed-off-by: Tschaen, Brendan --- .../java/org/onap/music/mdbc/MdbcConnection.java | 16 +--------------- .../org/onap/music/mdbc/MdbcPreparedStatement.java | 13 ------------- .../java/org/onap/music/mdbc/MdbcStatement.java | 22 +--------------------- .../java/org/onap/music/mdbc/StateManager.java | 12 ++++++++++++ .../org/onap/music/mdbc/mixins/DBInterface.java | 1 + .../org/onap/music/mdbc/mixins/MySQLMixin.java | 8 +++----- 6 files changed, 18 insertions(+), 54 deletions(-) diff --git a/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcConnection.java b/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcConnection.java index 3743638..0189f48 100755 --- a/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcConnection.java +++ b/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcConnection.java @@ -102,14 +102,6 @@ public class MdbcConnection implements Connection { logger.error(EELFLoggerDelegate.errorLogger, e.getMessage(), AppMessages.QUERYERROR, ErrorTypes.QUERYERROR, ErrorSeverity.CRITICAL); } - // Verify the tables in MUSIC match the tables in the database - // and create triggers on any tables that need them - try { - this.synchronizeTables(); - } catch (QueryException e) { - logger.error("Error syncrhonizing tables"); - logger.error(EELFLoggerDelegate.errorLogger, e.getMessage(), AppMessages.QUERYERROR, ErrorTypes.QUERYERROR, ErrorSeverity.CRITICAL); - } this.progressKeeper = progressKeeper; this.partition = partition; this.statemanager = statemanager; @@ -542,7 +534,7 @@ public class MdbcConnection implements Connection { * proxy first starts, and whenever there is the possibility that tables were created or dropped. It is synchronized * in order to prevent multiple threads from running this code in parallel. */ - public void synchronizeTables() throws QueryException { + public void createTriggers() throws QueryException { Set set1 = dbi.getSQLTableSet(); // set of tables in the database logger.debug(EELFLoggerDelegate.applicationLogger, "synchronizing tables:" + set1); for (String tableName : set1) { @@ -550,14 +542,8 @@ public class MdbcConnection implements Connection { if (!table_set.contains(tableName.toUpperCase()) && !dbi.getReservedTblNames().contains(tableName.toUpperCase())) { logger.info(EELFLoggerDelegate.applicationLogger, "New table discovered: "+tableName); try { - TableInfo ti = dbi.getTableInfo(tableName); - //mi.initializeMusicForTable(ti,tableName); - //mi.createDirtyRowTable(ti,tableName); dbi.createSQLTriggers(tableName); table_set.add(tableName.toUpperCase()); - //dbi.synchronizeData(tableName); - logger.debug(EELFLoggerDelegate.applicationLogger, "synchronized tables:" + - table_set.size() + "/" + set1.size() + "tables uploaded"); } catch (Exception e) { logger.error(EELFLoggerDelegate.errorLogger, e.getMessage(),AppMessages.UNKNOWNERROR, ErrorSeverity.CRITICAL, ErrorTypes.QUERYERROR); //logger.error(EELFLoggerDelegate.errorLogger, "Exception synchronizeTables: "+e); diff --git a/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcPreparedStatement.java b/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcPreparedStatement.java index 102072c..ab0f654 100755 --- a/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcPreparedStatement.java +++ b/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcPreparedStatement.java @@ -90,7 +90,6 @@ public class MdbcPreparedStatement extends MdbcStatement implements PreparedStat mConn.preStatementHook(sql); r = stmt.executeQuery(sql); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger, "executeQuery: exception "+nm); @@ -111,7 +110,6 @@ public class MdbcPreparedStatement extends MdbcStatement implements PreparedStat mConn.preStatementHook(sql); n = stmt.executeUpdate(sql); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger, "executeUpdate: exception "+nm+" "+e); @@ -194,7 +192,6 @@ public class MdbcPreparedStatement extends MdbcStatement implements PreparedStat mConn.preStatementHook(sql); b = stmt.execute(sql); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger, "execute: exception "+nm+" "+e); @@ -275,7 +272,6 @@ public class MdbcPreparedStatement extends MdbcStatement implements PreparedStat try { logger.debug(EELFLoggerDelegate.applicationLogger,"executeBatch() is not supported by MDBC; your results may be incorrect as a result."); n = stmt.executeBatch(); - synchronizeTables(null); } catch (Exception e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger,"executeBatch: exception "+nm); @@ -308,7 +304,6 @@ public class MdbcPreparedStatement extends MdbcStatement implements PreparedStat mConn.preStatementHook(sql); n = stmt.executeUpdate(sql, autoGeneratedKeys); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger,"executeUpdate: exception "+nm); @@ -328,7 +323,6 @@ public class MdbcPreparedStatement extends MdbcStatement implements PreparedStat mConn.preStatementHook(sql); n = stmt.executeUpdate(sql, columnIndexes); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger,"executeUpdate: exception "+nm); @@ -348,7 +342,6 @@ public class MdbcPreparedStatement extends MdbcStatement implements PreparedStat mConn.preStatementHook(sql); n = stmt.executeUpdate(sql, columnNames); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger,"executeUpdate: exception "+nm); @@ -368,7 +361,6 @@ public class MdbcPreparedStatement extends MdbcStatement implements PreparedStat mConn.preStatementHook(sql); b = stmt.execute(sql, autoGeneratedKeys); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger,"execute: exception "+nm); @@ -388,7 +380,6 @@ public class MdbcPreparedStatement extends MdbcStatement implements PreparedStat mConn.preStatementHook(sql); b = stmt.execute(sql, columnIndexes); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger,"execute: exception "+nm); @@ -408,7 +399,6 @@ public class MdbcPreparedStatement extends MdbcStatement implements PreparedStat mConn.preStatementHook(sql); b = stmt.execute(sql, columnNames); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger,"execute: exception "+nm); @@ -458,7 +448,6 @@ public class MdbcPreparedStatement extends MdbcStatement implements PreparedStat mConn.preStatementHook(sql); r = ((PreparedStatement)stmt).executeQuery();; mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { e.printStackTrace(); String nm = e.getClass().getName(); @@ -481,7 +470,6 @@ public class MdbcPreparedStatement extends MdbcStatement implements PreparedStat mConn.preStatementHook(sql); n = ((PreparedStatement)stmt).executeUpdate(); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { e.printStackTrace(); String nm = e.getClass().getName(); @@ -604,7 +592,6 @@ public class MdbcPreparedStatement extends MdbcStatement implements PreparedStat mConn.preStatementHook(sql); b = ((PreparedStatement)stmt).execute(); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { e.printStackTrace(); String nm = e.getClass().getName(); diff --git a/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcStatement.java b/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcStatement.java index 8afcca6..783c19b 100755 --- a/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcStatement.java +++ b/mdbc-server/src/main/java/org/onap/music/mdbc/MdbcStatement.java @@ -77,7 +77,6 @@ public class MdbcStatement implements Statement { mConn.preStatementHook(sql); r = stmt.executeQuery(sql); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger, "executeQuery: exception "+nm); @@ -98,7 +97,6 @@ public class MdbcStatement implements Statement { mConn.preStatementHook(sql); n = stmt.executeUpdate(sql); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger, "executeUpdate: exception "+nm+" "+e); @@ -183,7 +181,6 @@ public class MdbcStatement implements Statement { mConn.preStatementHook(sql); b = stmt.execute(sql); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger, "execute: exception "+nm+" "+e); @@ -264,7 +261,6 @@ public class MdbcStatement implements Statement { try { logger.debug(EELFLoggerDelegate.applicationLogger,"executeBatch() is not supported by MDBC; your results may be incorrect as a result."); n = stmt.executeBatch(); - synchronizeTables(null); } catch (Exception e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger,"executeBatch: exception "+nm); @@ -297,7 +293,6 @@ public class MdbcStatement implements Statement { mConn.preStatementHook(sql); n = stmt.executeUpdate(sql, autoGeneratedKeys); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger,"executeUpdate: exception "+nm); @@ -317,7 +312,6 @@ public class MdbcStatement implements Statement { mConn.preStatementHook(sql); n = stmt.executeUpdate(sql, columnIndexes); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger,"executeUpdate: exception "+nm); @@ -337,7 +331,6 @@ public class MdbcStatement implements Statement { mConn.preStatementHook(sql); n = stmt.executeUpdate(sql, columnNames); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger,"executeUpdate: exception "+nm); @@ -357,7 +350,6 @@ public class MdbcStatement implements Statement { mConn.preStatementHook(sql); b = stmt.execute(sql, autoGeneratedKeys); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger,"execute: exception "+nm); @@ -377,7 +369,6 @@ public class MdbcStatement implements Statement { mConn.preStatementHook(sql); b = stmt.execute(sql, columnIndexes); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger,"execute: exception "+nm); @@ -398,7 +389,6 @@ public class MdbcStatement implements Statement { mConn.preStatementHook(sql); b = stmt.execute(sql, columnNames); mConn.postStatementHook(sql); - synchronizeTables(sql); } catch (SQLException e) { String nm = e.getClass().getName(); logger.error(EELFLoggerDelegate.errorLogger,"execute: exception "+nm); @@ -440,15 +430,5 @@ public class MdbcStatement implements Statement { return stmt.isCloseOnCompletion(); } - protected void synchronizeTables(String sql) { - if (sql == null || sql.trim().toLowerCase().startsWith("create")) { - if (mConn != null) { - try { - mConn.synchronizeTables(); - } catch (QueryException e) { - logger.error(EELFLoggerDelegate.errorLogger, e.getMessage(),AppMessages.UNKNOWNERROR, ErrorSeverity.CRITICAL, ErrorTypes.QUERYERROR); - } - } - } - } + } diff --git a/mdbc-server/src/main/java/org/onap/music/mdbc/StateManager.java b/mdbc-server/src/main/java/org/onap/music/mdbc/StateManager.java index 18bc0db..4ee60bf 100644 --- a/mdbc-server/src/main/java/org/onap/music/mdbc/StateManager.java +++ b/mdbc-server/src/main/java/org/onap/music/mdbc/StateManager.java @@ -24,6 +24,7 @@ import java.util.List; import org.apache.commons.lang3.tuple.Pair; import org.onap.music.exceptions.MDBCServiceException; +import org.onap.music.exceptions.QueryException; import org.onap.music.logging.EELFLoggerDelegate; import org.onap.music.logging.format.AppMessages; import org.onap.music.logging.format.ErrorSeverity; @@ -176,6 +177,17 @@ public class StateManager { throw new MDBCServiceException(e.getMessage(), e); } } + + // Verify the tables in MUSIC match the tables in the database + // and create triggers on any tables that need them + try { + MdbcConnection mdbcConn = (MdbcConnection) openConnection("init"); + mdbcConn.createTriggers(); + closeConnection("init"); + } catch (QueryException e) { + logger.error("Error syncrhonizing tables"); + logger.error(EELFLoggerDelegate.errorLogger, e.getMessage(), AppMessages.QUERYERROR, ErrorTypes.QUERYERROR, ErrorSeverity.CRITICAL); + } } /** diff --git a/mdbc-server/src/main/java/org/onap/music/mdbc/mixins/DBInterface.java b/mdbc-server/src/main/java/org/onap/music/mdbc/mixins/DBInterface.java index dd71d97..063ea3f 100755 --- a/mdbc-server/src/main/java/org/onap/music/mdbc/mixins/DBInterface.java +++ b/mdbc-server/src/main/java/org/onap/music/mdbc/mixins/DBInterface.java @@ -49,6 +49,7 @@ public interface DBInterface { void close(); /** * Get a set of the table names in the database. The table names should be returned in UPPER CASE. + * @see #getSQLRangeSet() * @return the set */ @Deprecated diff --git a/mdbc-server/src/main/java/org/onap/music/mdbc/mixins/MySQLMixin.java b/mdbc-server/src/main/java/org/onap/music/mdbc/mixins/MySQLMixin.java index 15caf3f..7e9b5f4 100755 --- a/mdbc-server/src/main/java/org/onap/music/mdbc/mixins/MySQLMixin.java +++ b/mdbc-server/src/main/java/org/onap/music/mdbc/mixins/MySQLMixin.java @@ -107,6 +107,7 @@ public class MySQLMixin implements DBInterface { this.tables = new HashMap(); useAsyncStagingUpdate = Boolean.parseBoolean(info.getProperty(Configuration.KEY_ASYNC_STAGING_TABLE_UPDATE, Configuration.ASYNC_STAGING_TABLE_UPDATE)); + this.deleteStagingStatement = getStagingDeletePreparedStatement(); } class StagingTableUpdateRunnable implements Runnable{ @@ -193,10 +194,7 @@ public class MySQLMixin implements DBInterface { @Override public String getSchema() {return this.dbName;} - /** - * Get a set of the table names in the database. - * @return the set - */ + @Override public Set getSQLTableSet() { Set set = new TreeSet(); @@ -356,7 +354,7 @@ mysql> describe tables; Statement stmt = jdbcConn.createStatement(); stmt.execute(CREATE_TBL_SQL); stmt.close(); - this.deleteStagingStatement = getStagingDeletePreparedStatement(); + logger.info(EELFLoggerDelegate.applicationLogger,"createSQLTriggers: Server side dirty table created."); server_tbl_created = true; } catch (SQLException e) { -- cgit 1.2.3-korg