001package io.ebean.config.dbplatform.h2;
002
003import org.h2.api.Trigger;
004import org.slf4j.Logger;
005import org.slf4j.LoggerFactory;
006
007import java.sql.Connection;
008import java.sql.PreparedStatement;
009import java.sql.ResultSet;
010import java.sql.SQLException;
011import java.sql.Timestamp;
012import java.util.Arrays;
013
014/**
015 * H2 database trigger used to populate history tables to support the @History feature.
016 */
017public class H2HistoryTrigger implements Trigger {
018
019  private static final Logger logger = LoggerFactory.getLogger(H2HistoryTrigger.class);
020
021  /**
022   * Hardcoding the column and history table suffix for now. Not sure how to get that
023   * configuration into the trigger instance nicely as it is instantiated by H2.
024   */
025  private static final String SYS_PERIOD_START = "SYS_PERIOD_START";
026  private static final String SYS_PERIOD_END = "SYS_PERIOD_END";
027  private static final String HISTORY_SUFFIX = "_history";
028
029  /**
030   * SQL to insert into the history table.
031   */
032  private String insertHistorySql;
033
034  /**
035   * Position of SYS_PERIOD_START column in the Object[].
036   */
037  private int effectStartPosition;
038
039  /**
040   * Position of SYS_PERIOD_END column in the Object[].
041   */
042  private int effectEndPosition;
043
044  @Override
045  public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before, int type) throws SQLException {
046
047    // get the columns for the table
048    ResultSet rs = conn.getMetaData().getColumns(null, schemaName, tableName, null);
049
050    // build the insert into history table SQL
051    StringBuilder insertSql = new StringBuilder(150);
052    insertSql.append("insert into ").append(schemaName).append(".").append(tableName).append(HISTORY_SUFFIX).append(" (");
053
054    int count = 0;
055    while (rs.next()) {
056      if (++count > 1) {
057        insertSql.append(",");
058      }
059      String columnName = rs.getString("COLUMN_NAME");
060      if (columnName.equalsIgnoreCase(SYS_PERIOD_START)) {
061        this.effectStartPosition = count - 1;
062      } else if (columnName.equalsIgnoreCase(SYS_PERIOD_END)) {
063        this.effectEndPosition = count - 1;
064      }
065      insertSql.append(columnName);
066    }
067    insertSql.append(") values (");
068    for (int i = 0; i < count; i++) {
069      if (i > 0) {
070        insertSql.append(",");
071      }
072      insertSql.append("?");
073    }
074    insertSql.append(");");
075
076    this.insertHistorySql = insertSql.toString();
077    logger.debug("History table insert sql: {}", insertHistorySql);
078  }
079
080  @Override
081  public void fire(Connection connection, Object[] oldRow, Object[] newRow) throws SQLException {
082
083    if (oldRow != null) {
084      // a delete or update event
085      Timestamp now = new Timestamp(System.currentTimeMillis());
086      oldRow[effectEndPosition] = now;
087      if (newRow != null) {
088        // update event. Set the effective start timestamp to now.
089        newRow[effectStartPosition] = now;
090      }
091      if (logger.isDebugEnabled()) {
092        logger.debug("History insert: {}", Arrays.toString(oldRow));
093      }
094      insertIntoHistory(connection, oldRow);
095    }
096  }
097
098  /**
099   * Insert the data into the history table.
100   */
101  private void insertIntoHistory(Connection connection, Object[] oldRow) throws SQLException {
102
103    try (PreparedStatement stmt = connection.prepareStatement(insertHistorySql)) {
104      for (int i = 0; i < oldRow.length; i++) {
105        stmt.setObject(i + 1, oldRow[i]);
106      }
107      stmt.executeUpdate();
108    }
109  }
110
111  @Override
112  public void close() throws SQLException {
113
114  }
115
116  @Override
117  public void remove() throws SQLException {
118
119  }
120}