/**
* Licensed to JumpMind Inc under one or more contributor
* license agreements. See the NOTICE file distributed
* with this work for additional information regarding
* copyright ownership. JumpMind Inc licenses this file
* to you under the GNU General Public License, version 3.0 (GPLv3)
* (the "License"); you may not use this file except in compliance
* with the License.
*
* You should have received a copy of the GNU General Public License,
* version 3.0 (GPLv3) along with this library; if not, see
* <http://www.gnu.org/licenses/>.
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*/
package org.jumpmind.symmetric.db;
import static org.apache.commons.lang.StringUtils.isBlank;
import static org.apache.commons.lang.StringUtils.isNotBlank;
import java.sql.Types;
import java.util.Map;
import org.apache.commons.lang.NotImplementedException;
import org.apache.commons.lang.StringUtils;
import org.jumpmind.db.model.Column;
import org.jumpmind.db.model.ColumnTypes;
import org.jumpmind.db.model.Table;
import org.jumpmind.db.model.TypeMap;
import org.jumpmind.db.sql.DmlStatement.DmlType;
import org.jumpmind.symmetric.common.Constants;
import org.jumpmind.symmetric.common.ParameterConstants;
import org.jumpmind.symmetric.io.data.DataEventType;
import org.jumpmind.symmetric.model.Channel;
import org.jumpmind.symmetric.model.Node;
import org.jumpmind.symmetric.model.Trigger;
import org.jumpmind.symmetric.model.TriggerHistory;
import org.jumpmind.symmetric.model.TriggerRouter;
import org.jumpmind.symmetric.service.IParameterService;
import org.jumpmind.symmetric.util.SymmetricUtils;
import org.jumpmind.util.FormatUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Responsible for generating dialect specific SQL such as trigger bodies and
* functions
*/
abstract public class AbstractTriggerTemplate {
protected final Logger log = LoggerFactory.getLogger(getClass());
protected static final String ORIG_TABLE_ALIAS = "orig";
static final String INSERT_TRIGGER_TEMPLATE = "insertTriggerTemplate";
static final String UPDATE_TRIGGER_TEMPLATE = "updateTriggerTemplate";
static final String DELETE_TRIGGER_TEMPLATE = "deleteTriggerTemplate";
static final String INITIAL_LOAD_SQL_TEMPLATE = "initialLoadSqlTemplate";
protected Map<String, String> sqlTemplates;
protected String emptyColumnTemplate = "''";
protected String stringColumnTemplate;
protected String xmlColumnTemplate;
protected String arrayColumnTemplate;
protected String numberColumnTemplate;
protected String datetimeColumnTemplate;
protected String timeColumnTemplate;
protected String dateColumnTemplate;
protected String dateTimeWithTimeZoneColumnTemplate;
protected String dateTimeWithLocalTimeZoneColumnTemplate;
protected String geometryColumnTemplate;
protected String geographyColumnTemplate;
protected String clobColumnTemplate;
protected String blobColumnTemplate;
protected String binaryColumnTemplate;
protected String imageColumnTemplate;
protected String wrappedBlobColumnTemplate;
protected String booleanColumnTemplate;
protected String triggerConcatCharacter;
protected String newTriggerValue;
protected String oldTriggerValue;
protected String oldColumnPrefix = "";
protected String newColumnPrefix = "";
protected String otherColumnTemplate;
protected ISymmetricDialect symmetricDialect;
protected AbstractTriggerTemplate(ISymmetricDialect symmetricDialect) {
this.symmetricDialect = symmetricDialect;
}
/**
* When {@link ParameterConstants#INITIAL_LOAD_CONCAT_CSV_IN_SQL_ENABLED} is false
* most dialects are going to want to still use the trigger templates because they
* have type translation details (like geometry templates). However, some dialects
* cannot handle the complex SQL generated (Firebird). We needed a way to tell
* the dialect that we want to select the columns straight up.
*/
public boolean useTriggerTemplateForColumnTemplatesDuringInitialLoad() {
return true;
}
public String createInitalLoadSql(Node node, TriggerRouter triggerRouter, Table originalTable,
TriggerHistory triggerHistory, Channel channel, String overrideSelectSql) {
IParameterService parameterService = symmetricDialect.getParameterService();
boolean dateTimeAsString = parameterService.is(
ParameterConstants.DATA_LOADER_TREAT_DATETIME_AS_VARCHAR);
boolean concatInCsv = parameterService.is(
ParameterConstants.INITIAL_LOAD_CONCAT_CSV_IN_SQL_ENABLED);
Table table = originalTable.copyAndFilterColumns(triggerHistory.getParsedColumnNames(),
triggerHistory.getParsedPkColumnNames(), true);
Column[] columns = table.getColumns();
String textColumnExpression = parameterService.getString(ParameterConstants.DATA_EXTRACTOR_TEXT_COLUMN_EXPRESSION);
String sql = null;
String tableAlias = symmetricDialect.getInitialLoadTableAlias();
if (concatInCsv) {
sql = sqlTemplates.get(INITIAL_LOAD_SQL_TEMPLATE);
String columnsText = buildColumnsString(tableAlias,
tableAlias, "", columns, DataEventType.INSERT,
false, channel, triggerRouter.getTrigger()).columnString;
if (isNotBlank(textColumnExpression)) {
columnsText = textColumnExpression.replace("$(columnName)", columnsText);
}
sql = FormatUtils.replace("columns", columnsText, sql);
} else {
sql = "select $(columns) from $(schemaName)$(tableName) t where $(whereClause)";
StringBuilder columnList = new StringBuilder();
for (int i = 0; i < columns.length; i++) {
Column column = columns[i];
if (column != null) {
if (i > 0) {
columnList.append(",");
}
boolean isLob = symmetricDialect.getPlatform()
.isLob(column.getMappedTypeCode());
if (!(isLob && triggerRouter.getTrigger().isUseStreamLobs())) {
String columnExpression = null;
if (useTriggerTemplateForColumnTemplatesDuringInitialLoad()) {
ColumnString columnString = fillOutColumnTemplate(tableAlias,
tableAlias, "", column, DataEventType.INSERT, false, channel,
triggerRouter.getTrigger());
columnExpression = columnString.columnString;
if (isNotBlank(textColumnExpression)
&& TypeMap.isTextType(column.getMappedTypeCode())) {
columnExpression = textColumnExpression.replace("$(columnName)",
columnExpression);
}
} else {
columnExpression = SymmetricUtils.quote(symmetricDialect,
column.getName());
if (dateTimeAsString
&& TypeMap.isDateTimeType(column.getMappedTypeCode())) {
columnExpression = castDatetimeColumnToString(column.getName());
} else if (isNotBlank(textColumnExpression)
&& TypeMap.isTextType(column.getMappedTypeCode())) {
columnExpression = textColumnExpression.replace("$(columnName)",
columnExpression);
}
}
columnList.append(columnExpression).append(" as ").append("x__").append(i);
} else {
columnList.append(" ").append(emptyColumnTemplate).append(" as ").append("x__").append(i);
}
}
}
sql = FormatUtils.replace("columns", columnList.toString(), sql);
}
String initialLoadSelect = StringUtils.isBlank(triggerRouter.getInitialLoadSelect()) ? Constants.ALWAYS_TRUE_CONDITION
: triggerRouter.getInitialLoadSelect();
if (StringUtils.isNotBlank(overrideSelectSql)) {
initialLoadSelect = overrideSelectSql;
}
sql = FormatUtils
.replace(
"whereClause", initialLoadSelect
, sql);
sql = FormatUtils.replace("tableName", SymmetricUtils.quote(symmetricDialect, table.getName()), sql);
sql = FormatUtils.replace("schemaName",
triggerHistory == null ? getSourceTablePrefix(originalTable)
: getSourceTablePrefix(triggerHistory), sql);
sql = FormatUtils.replace(
"primaryKeyWhereString",
getPrimaryKeyWhereString(symmetricDialect.getInitialLoadTableAlias(),
table.hasPrimaryKey() ? table.getPrimaryKeyColumns() : table.getColumns()),
sql);
// Replace these parameters to give the initiaLoadContition a chance to
// reference the node that is being loaded
sql = FormatUtils.replace("groupId", node.getNodeGroupId(), sql);
sql = FormatUtils.replace("externalId", node.getExternalId(), sql);
sql = FormatUtils.replace("nodeId", node.getNodeId(), sql);
sql = replaceDefaultSchemaAndCatalog(sql);
sql = FormatUtils.replace("prefixName", symmetricDialect.getTablePrefix(), sql);
sql = FormatUtils.replace("oracleToClob",
triggerRouter.getTrigger().isUseCaptureLobs() ? "to_clob('')||" : "", sql);
return sql;
}
protected String castDatetimeColumnToString(String columnName) {
return SymmetricUtils.quote(symmetricDialect, columnName);
}
protected String getSourceTablePrefix(Table table) {
String prefix = (isNotBlank(table.getSchema()) ? table.getSchema() + symmetricDialect.getPlatform().getDatabaseInfo().getSchemaSeparator() : "");
prefix = (isNotBlank(table.getCatalog()) ? table.getCatalog() + symmetricDialect.getPlatform().getDatabaseInfo().getCatalogSeparator() : "") + prefix;
if (isBlank(prefix)) {
prefix = (isNotBlank(symmetricDialect.getPlatform().getDefaultSchema()) ? SymmetricUtils
.quote(symmetricDialect, symmetricDialect.getPlatform().getDefaultSchema())
+ "." : "");
prefix = (isNotBlank(symmetricDialect.getPlatform().getDefaultCatalog()) ? SymmetricUtils
.quote(symmetricDialect, symmetricDialect.getPlatform().getDefaultCatalog())
+ "." : "") + prefix;
}
return prefix;
}
protected String getSourceTablePrefix(TriggerHistory triggerHistory) {
String prefix = (isNotBlank(triggerHistory.getSourceSchemaName()) ? SymmetricUtils.quote(
symmetricDialect, triggerHistory.getSourceSchemaName()) + symmetricDialect.getPlatform().getDatabaseInfo().getSchemaSeparator() : "");
prefix = (isNotBlank(triggerHistory.getSourceCatalogName()) ? SymmetricUtils.quote(
symmetricDialect, triggerHistory.getSourceCatalogName()) + symmetricDialect.getPlatform().getDatabaseInfo().getCatalogSeparator() : "")
+ prefix;
if (isBlank(prefix)) {
prefix = (isNotBlank(symmetricDialect.getPlatform().getDefaultSchema()) ? SymmetricUtils
.quote(symmetricDialect, symmetricDialect.getPlatform().getDefaultSchema())
+ "." : "");
prefix = (isNotBlank(symmetricDialect.getPlatform().getDefaultCatalog()) ? SymmetricUtils
.quote(symmetricDialect, symmetricDialect.getPlatform().getDefaultCatalog())
+ "." : "") + prefix;
}
return prefix;
}
protected String replaceDefaultSchemaAndCatalog(String sql) {
String defaultCatalog = symmetricDialect.getPlatform().getDefaultCatalog();
String defaultSchema = symmetricDialect.getPlatform().getDefaultSchema();
sql = replaceDefaultSchema(sql, defaultSchema);
sql = replaceDefaultCatalog(sql, defaultCatalog);
return sql;
}
public String createCsvDataSql(Trigger trigger, TriggerHistory triggerHistory, Table originalTable,
Channel channel, String whereClause) {
Table table = originalTable.copyAndFilterColumns(triggerHistory.getParsedColumnNames(),
triggerHistory.getParsedPkColumnNames(), true);
String sql = sqlTemplates.get(INITIAL_LOAD_SQL_TEMPLATE);
Column[] columns = table.getColumns();
String columnsText = buildColumnsString(symmetricDialect.getInitialLoadTableAlias(),
symmetricDialect.getInitialLoadTableAlias(), "", columns, DataEventType.INSERT,
false, channel, trigger).columnString;
sql = FormatUtils.replace("columns", columnsText, sql);
sql = FormatUtils.replace("oracleToClob",
trigger.isUseCaptureLobs() ? "to_clob('')||" : "", sql);
sql = FormatUtils.replace("tableName", SymmetricUtils.quote(symmetricDialect, table.getName()), sql);
sql = FormatUtils.replace("schemaName",
triggerHistory == null ? getSourceTablePrefix(originalTable)
: getSourceTablePrefix(triggerHistory), sql);
sql = FormatUtils.replace("whereClause", whereClause, sql);
sql = FormatUtils.replace(
"primaryKeyWhereString",
getPrimaryKeyWhereString(symmetricDialect.getInitialLoadTableAlias(),
table.hasPrimaryKey() ? table.getPrimaryKeyColumns() : table.getColumns()),
sql);
sql = replaceDefaultSchemaAndCatalog(sql);
return sql;
}
public String createCsvPrimaryKeySql(Trigger trigger, TriggerHistory triggerHistory,
Table table, Channel channel, String whereClause) {
String sql = sqlTemplates.get(INITIAL_LOAD_SQL_TEMPLATE);
Column[] columns = table.getPrimaryKeyColumns();
String columnsText = buildColumnsString(symmetricDialect.getInitialLoadTableAlias(),
symmetricDialect.getInitialLoadTableAlias(), "", columns, DataEventType.INSERT,
false, channel, trigger).toString();
sql = FormatUtils.replace("columns", columnsText, sql);
sql = FormatUtils.replace("oracleToClob",
trigger.isUseCaptureLobs() ? "to_clob('')||" : "", sql);
sql = FormatUtils.replace("tableName", SymmetricUtils.quote(symmetricDialect, table.getName()), sql);
sql = FormatUtils.replace("schemaName",
triggerHistory == null ? getSourceTablePrefix(table)
: getSourceTablePrefix(triggerHistory), sql);
sql = FormatUtils.replace("whereClause", whereClause, sql);
sql = FormatUtils.replace(
"primaryKeyWhereString",
getPrimaryKeyWhereString(symmetricDialect.getInitialLoadTableAlias(),
table.hasPrimaryKey() ? table.getPrimaryKeyColumns() : table.getColumns()),
sql);
return sql;
}
public String createTriggerDDL(DataEventType dml, Trigger trigger, TriggerHistory history,
Channel channel, String tablePrefix, Table originalTable, String defaultCatalog,
String defaultSchema) {
Table table = originalTable.copyAndFilterColumns(history.getParsedColumnNames(),
history.getParsedPkColumnNames(), true);
String ddl = sqlTemplates.get(dml.name().toLowerCase() + "TriggerTemplate");
if (dml.getDmlType().equals(DmlType.UPDATE) && trigger.isUseHandleKeyUpdates()) {
String temp = sqlTemplates.get(dml.name().toLowerCase() + "HandleKeyUpdates" + "TriggerTemplate");
if (StringUtils.trimToNull(temp)!=null) {
ddl=temp;
}
}
if (ddl == null) {
throw new NotImplementedException(dml.name() + " trigger is not implemented for "
+ symmetricDialect.getPlatform().getName());
}
return replaceTemplateVariables(dml, trigger, history, channel, tablePrefix, originalTable, table,
defaultCatalog, defaultSchema, ddl);
}
public String createPostTriggerDDL(DataEventType dml, Trigger trigger, TriggerHistory history,
Channel channel, String tablePrefix, Table originalTable, String defaultCatalog,
String defaultSchema) {
Table table = originalTable.copyAndFilterColumns(history.getParsedColumnNames(),
history.getParsedPkColumnNames(), true);
String ddl = sqlTemplates.get(dml.name().toLowerCase() + "PostTriggerTemplate");
return replaceTemplateVariables(dml, trigger, history, channel, tablePrefix, originalTable, table,
defaultCatalog, defaultSchema, ddl);
}
protected String getDefaultTargetTableName(Trigger trigger, TriggerHistory history) {
String targetTableName = null;
if (history != null) {
targetTableName = history.getSourceTableName();
} else {
targetTableName = trigger.getSourceTableName();
}
return targetTableName;
}
protected String replaceTemplateVariables(DataEventType dml, Trigger trigger,
TriggerHistory history, Channel channel, String tablePrefix, Table originalTable, Table table,
String defaultCatalog, String defaultSchema, String ddl) {
ddl = FormatUtils.replace("targetTableName", getDefaultTargetTableName(trigger, history),
ddl);
ddl = FormatUtils.replace("triggerName", history.getTriggerNameForDmlType(dml), ddl);
ddl = FormatUtils.replace("channelName", trigger.getChannelId(), ddl);
ddl = FormatUtils.replace("triggerHistoryId",
Integer.toString(history == null ? -1 : history.getTriggerHistoryId()), ddl);
String triggerExpression = symmetricDialect.getTransactionTriggerExpression(defaultCatalog,
defaultSchema, trigger);
if (symmetricDialect.isTransactionIdOverrideSupported()
&& !StringUtils.isBlank(trigger.getTxIdExpression())) {
triggerExpression = trigger.getTxIdExpression();
}
ddl = FormatUtils.replace("txIdExpression",
symmetricDialect.preProcessTriggerSqlClause(triggerExpression), ddl);
ddl = FormatUtils.replace("channelExpression", symmetricDialect.preProcessTriggerSqlClause(getChannelExpression(trigger)),
ddl);
ddl = FormatUtils.replace("externalSelect", (trigger.getExternalSelect() == null ? "null"
: "(" + symmetricDialect.preProcessTriggerSqlClause(trigger.getExternalSelect())
+ ")"), ddl);
ddl = FormatUtils.replace("syncOnInsertCondition",
symmetricDialect.preProcessTriggerSqlClause(trigger.getSyncOnInsertCondition()),
ddl);
ddl = FormatUtils.replace("syncOnUpdateCondition",
symmetricDialect.preProcessTriggerSqlClause(trigger.getSyncOnUpdateCondition()),
ddl);
ddl = FormatUtils.replace("syncOnDeleteCondition",
symmetricDialect.preProcessTriggerSqlClause(trigger.getSyncOnDeleteCondition()),
ddl);
ddl = FormatUtils.replace("custom_on_insert_text",
trigger.getCustomOnInsertText()==null ? "" : trigger.getCustomOnInsertText(), ddl);
ddl = FormatUtils.replace("custom_on_update_text",
trigger.getCustomOnUpdateText()==null ? "" : trigger.getCustomOnUpdateText(), ddl);
ddl = FormatUtils.replace("custom_on_delete_text",
trigger.getCustomOnDeleteText()==null ? "" : trigger.getCustomOnDeleteText(), ddl);
ddl = FormatUtils.replace("dataHasChangedCondition", symmetricDialect
.preProcessTriggerSqlClause(symmetricDialect.getDataHasChangedCondition(trigger)),
ddl);
ddl = FormatUtils.replace("sourceNodeExpression",
symmetricDialect.getSourceNodeExpression(), ddl);
ddl = FormatUtils.replace("oracleLobType", trigger.isUseCaptureLobs() ? "clob" : "long",
ddl);
String syncTriggersExpression = symmetricDialect.getSyncTriggersExpression();
ddl = FormatUtils.replace("syncOnIncomingBatchCondition",
trigger.isSyncOnIncomingBatch() ? Constants.ALWAYS_TRUE_CONDITION
: syncTriggersExpression, ddl);
ddl = FormatUtils.replace("origTableAlias", ORIG_TABLE_ALIAS, ddl);
Column[] orderedColumns = table.getColumns();
ColumnString columnString = buildColumnsString(ORIG_TABLE_ALIAS, newTriggerValue,
newColumnPrefix, orderedColumns, dml, false, channel, trigger);
ddl = FormatUtils.replace("columns", columnString.toString(), ddl);
ddl = replaceDefaultSchemaAndCatalog(ddl);
ddl = FormatUtils.replace("virtualOldNewTable",
buildVirtualTableSql(oldColumnPrefix, newColumnPrefix, originalTable.getColumns()), ddl);
ddl = FormatUtils.replace(
"oldColumns",
trigger.isUseCaptureOldData() ? buildColumnsString(ORIG_TABLE_ALIAS,
oldTriggerValue, oldColumnPrefix, orderedColumns, dml, true, channel,
trigger).toString() : "null", ddl);
ddl = eval(columnString.isBlobClob, "containsBlobClobColumns", ddl);
// some column templates need tableName and schemaName
ddl = FormatUtils.replace("tableName", SymmetricUtils.quote(symmetricDialect, table.getName()), ddl);
ddl = FormatUtils.replace("schemaName", history == null ? getSourceTablePrefix(originalTable)
: getSourceTablePrefix(history), ddl);
Column[] primaryKeyColumns = table.getPrimaryKeyColumns();
ddl = FormatUtils.replace(
"oldKeys",
buildColumnsString(ORIG_TABLE_ALIAS, oldTriggerValue, oldColumnPrefix,
primaryKeyColumns, dml, true, channel, trigger).toString(), ddl);
ddl = FormatUtils.replace(
"oldNewPrimaryKeyJoin",
aliasedPrimaryKeyJoin(oldTriggerValue, newTriggerValue,
primaryKeyColumns.length == 0 ? orderedColumns : primaryKeyColumns), ddl);
ddl = FormatUtils.replace(
"tableNewPrimaryKeyJoin",
aliasedPrimaryKeyJoin(ORIG_TABLE_ALIAS, newTriggerValue,
primaryKeyColumns.length == 0 ? orderedColumns : primaryKeyColumns), ddl);
ddl = FormatUtils.replace(
"primaryKeyWhereString",
getPrimaryKeyWhereString(dml == DataEventType.DELETE ? oldTriggerValue
: newTriggerValue, table.hasPrimaryKey() ? table.getPrimaryKeyColumns()
: table.getColumns()), ddl);
String builtString = buildColumnNameString(oldTriggerValue, true, trigger,
primaryKeyColumns);
ddl = FormatUtils.replace("oldKeyNames", StringUtils.isNotBlank(builtString) ? ","
+ builtString : "", ddl);
builtString = buildColumnNameString(newTriggerValue, true, trigger, primaryKeyColumns);
ddl = FormatUtils.replace("newKeyNames", StringUtils.isNotBlank(builtString) ? ","
+ builtString : "", ddl);
ddl = FormatUtils.replace("columnNames",
buildColumnNameString(null, false, trigger, orderedColumns), ddl);
ddl = FormatUtils.replace("pkColumnNames",
buildColumnNameString(null, false, trigger, primaryKeyColumns), ddl);
builtString = buildKeyVariablesString(primaryKeyColumns, "old");
ddl = FormatUtils.replace("oldKeyVariables", StringUtils.isNotBlank(builtString) ? ","
+ builtString : "", ddl);
builtString = buildKeyVariablesString(primaryKeyColumns, "new");
ddl = FormatUtils.replace("newKeyVariables", StringUtils.isNotBlank(builtString) ? ","
+ builtString : "", ddl);
ddl = FormatUtils.replace("varNewPrimaryKeyJoin",
aliasedPrimaryKeyJoinVar(newTriggerValue, "new", primaryKeyColumns), ddl);
ddl = FormatUtils.replace("varOldPrimaryKeyJoin",
aliasedPrimaryKeyJoinVar(oldTriggerValue, "old", primaryKeyColumns), ddl);
// replace $(newTriggerValue) and $(oldTriggerValue)
ddl = FormatUtils.replace("newTriggerValue", newTriggerValue, ddl);
ddl = FormatUtils.replace("oldTriggerValue", oldTriggerValue, ddl);
ddl = FormatUtils.replace("newColumnPrefix", newColumnPrefix, ddl);
ddl = FormatUtils.replace("oldColumnPrefix", oldColumnPrefix, ddl);
ddl = FormatUtils.replace("prefixName", tablePrefix, ddl);
ddl = replaceDefaultSchemaAndCatalog(ddl);
ddl = FormatUtils.replace("oracleToClob",
trigger.isUseCaptureLobs() ? "to_clob('')||" : "", ddl);
switch (dml) {
case DELETE:
ddl = FormatUtils.replace("curTriggerValue", oldTriggerValue, ddl);
ddl = FormatUtils.replace("curColumnPrefix", oldColumnPrefix, ddl);
break;
case INSERT:
case UPDATE:
default:
ddl = FormatUtils.replace("curTriggerValue", newTriggerValue, ddl);
ddl = FormatUtils.replace("curColumnPrefix", newColumnPrefix, ddl);
break;
}
return ddl;
}
protected String getChannelExpression(Trigger trigger) {
if (trigger.getChannelId().equals(Constants.CHANNEL_DYNAMIC)) {
if (StringUtils.isNotBlank(trigger.getChannelExpression())) {
return trigger.getChannelExpression();
} else {
throw new IllegalStateException("When the channel is set to '" + Constants.CHANNEL_DYNAMIC + "', a channel expression must be provided.");
}
} else {
return "'" + trigger.getChannelId() + "'";
}
}
protected String buildVirtualTableSql(String oldTriggerValue, String newTriggerValue,
Column[] columns) {
if (oldTriggerValue.indexOf(".") >= 0) {
oldTriggerValue = oldTriggerValue.substring(oldTriggerValue.indexOf(".") + 1);
}
if (newTriggerValue.indexOf(".") >= 0) {
newTriggerValue = newTriggerValue.substring(newTriggerValue.indexOf(".") + 1);
}
StringBuilder b = new StringBuilder("(SELECT ");
for (Column columnType : columns) {
String column = columnType.getName();
b.append("? as ");
b.append("\"").append(newTriggerValue).append(column).append("\",");
}
for (Column columnType : columns) {
String column = columnType.getName();
b.append("? AS ");
b.append("\"").append(oldTriggerValue).append(column).append("\",");
}
b.deleteCharAt(b.length() - 1);
b.append(" FROM DUAL) T ");
return b.toString();
}
protected String eval(boolean condition, String prop, String ddl) {
if (ddl != null) {
String ifStmt = "$(if:" + prop + ")";
String elseStmt = "$(else:" + prop + ")";
String endStmt = "$(end:" + prop + ")";
int ifIndex = ddl.indexOf(ifStmt);
if (ifIndex >= 0) {
int endIndex = ddl.indexOf(endStmt);
if (endIndex >= 0) {
String onTrue = ddl.substring(ifIndex + ifStmt.length(), endIndex);
String onFalse = "";
int elseIndex = onTrue.indexOf(elseStmt);
if (elseIndex >= 0) {
onFalse = onTrue.substring(elseIndex + elseStmt.length());
onTrue = onTrue.substring(0, elseIndex);
}
if (condition) {
ddl = ddl.substring(0, ifIndex) + onTrue
+ ddl.substring(endIndex + endStmt.length());
} else {
ddl = ddl.substring(0, ifIndex) + onFalse
+ ddl.substring(endIndex + endStmt.length());
}
} else {
throw new IllegalStateException(ifStmt + " has to have a " + endStmt);
}
}
}
return ddl;
}
protected String aliasedPrimaryKeyJoin(String aliasOne, String aliasTwo, Column[] columns) {
StringBuilder b = new StringBuilder();
for (Column column : columns) {
b.append(aliasOne).append(".\"").append(column.getName()).append("\"");
b.append("=").append(aliasTwo).append(".\"").append(column.getName()).append("\"");
if (!column.equals(columns[columns.length - 1])) {
b.append(" and ");
}
}
return b.toString();
}
protected String aliasedPrimaryKeyJoinVar(String alias, String prefix, Column[] columns) {
String text = "";
for (int i = 0; i < columns.length; i++) {
text += alias + ".\"" + columns[i].getName() + "\"";
text += "=@" + prefix + "pk" + i;
if (i + 1 < columns.length) {
text += " and ";
}
}
return text;
}
/**
* Specific to Derby. Needs to be removed when the initial load is
* refactored to concat in Java vs. in SQL
*/
@Deprecated
protected String getPrimaryKeyWhereString(String alias, Column[] columns) {
return null;
}
protected boolean requiresWrappedBlobTemplateForBlobType() {
return false;
}
protected boolean requiresEmptyLobTemplateForDeletes() {
return false;
}
protected String buildColumnNameString(String tableAlias, boolean quote, Trigger trigger,
Column[] columns) {
StringBuilder columnsText = new StringBuilder();
for (Column column : columns) {
boolean isLob = symmetricDialect.getPlatform().isLob(column.getMappedTypeCode());
String columnName = column.getName();
if (quote) {
columnName = SymmetricUtils.quote(symmetricDialect, columnName);
}
if (!(isLob && trigger.isUseStreamLobs())) {
if (StringUtils.isNotBlank(tableAlias)) {
columnsText.append(tableAlias);
columnsText.append(".");
}
columnsText.append(columnName);
} else {
columnsText.append("null");
}
columnsText.append(",");
}
return columnsText.length() > 0 ? columnsText.substring(0, columnsText.length() - 1) : columnsText.toString();
}
protected ColumnString buildColumnsString(String origTableAlias, String tableAlias,
String columnPrefix, Column[] columns, DataEventType dml, boolean isOld,
Channel channel, Trigger trigger) {
String columnsText = "";
boolean containsLob = false;
String lastCommandToken = symmetricDialect.escapesTemplatesForDatabaseInserts() ? (triggerConcatCharacter
+ "'',''" + triggerConcatCharacter)
: (triggerConcatCharacter + "','" + triggerConcatCharacter);
for (int i = 0; i < columns.length; i++) {
Column column = columns[i];
if (column != null) {
ColumnString columnString = fillOutColumnTemplate(origTableAlias, tableAlias,
columnPrefix, column, dml, isOld, channel, trigger);
columnsText = columnsText + "\n " + columnString.columnString
+ lastCommandToken;
containsLob |= columnString.isBlobClob;
}
}
if (columnsText.endsWith(lastCommandToken)) {
columnsText = columnsText
.substring(0, columnsText.length() - lastCommandToken.length());
}
return new ColumnString(columnsText, containsLob);
}
protected ColumnString fillOutColumnTemplate(String origTableAlias, String tableAlias,
String columnPrefix, Column column, DataEventType dml, boolean isOld, Channel channel,
Trigger trigger) {
boolean isLob = symmetricDialect.getPlatform().isLob(column.getMappedTypeCode());
String templateToUse = null;
if (column.getJdbcTypeName() != null
&& (column.getJdbcTypeName().toUpperCase().contains(TypeMap.GEOMETRY))
&& StringUtils.isNotBlank(geometryColumnTemplate)) {
templateToUse = geometryColumnTemplate;
}
else if (column.getJdbcTypeName() != null
&& (column.getJdbcTypeName().toUpperCase().contains(TypeMap.GEOGRAPHY))
&& StringUtils.isNotBlank(geographyColumnTemplate)) {
templateToUse = geographyColumnTemplate;
}
else {
switch (column.getMappedTypeCode()) {
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
case Types.BIGINT:
case Types.FLOAT:
case Types.REAL:
case Types.DOUBLE:
case Types.NUMERIC:
case Types.DECIMAL:
templateToUse = numberColumnTemplate;
break;
case Types.CHAR:
case Types.NCHAR:
case Types.VARCHAR:
case ColumnTypes.NVARCHAR:
templateToUse = stringColumnTemplate;
break;
case ColumnTypes.SQLXML:
templateToUse = xmlColumnTemplate;
break;
case Types.ARRAY:
templateToUse = arrayColumnTemplate;
break;
case Types.LONGVARCHAR:
case ColumnTypes.LONGNVARCHAR:
if (!isLob) {
templateToUse = stringColumnTemplate;
break;
}
case Types.CLOB:
if (isOld && symmetricDialect.needsToSelectLobData()) {
templateToUse = emptyColumnTemplate;
} else {
templateToUse = clobColumnTemplate;
}
break;
case Types.BINARY:
case Types.VARBINARY:
if (isNotBlank(binaryColumnTemplate)) {
templateToUse = binaryColumnTemplate;
break;
}
case Types.BLOB:
if (requiresWrappedBlobTemplateForBlobType()) {
templateToUse = wrappedBlobColumnTemplate;
break;
}
case Types.LONGVARBINARY:
case -10: // SQL-Server ntext binary type
if (column.getJdbcTypeName() != null
&& (column.getJdbcTypeName().toUpperCase().contains(TypeMap.IMAGE))
&& StringUtils.isNotBlank(imageColumnTemplate)) {
if (isOld) {
templateToUse = emptyColumnTemplate;
} else {
templateToUse = imageColumnTemplate;
}
} else if (isOld && symmetricDialect.needsToSelectLobData()) {
templateToUse = emptyColumnTemplate;
} else {
templateToUse = blobColumnTemplate;
}
break;
case Types.DATE:
if (noDateColumnTemplate()) {
templateToUse = datetimeColumnTemplate;
break;
}
templateToUse = dateColumnTemplate;
break;
case Types.TIME:
if (noTimeColumnTemplate()) {
templateToUse = datetimeColumnTemplate;
break;
}
templateToUse = timeColumnTemplate;
break;
case Types.TIMESTAMP:
templateToUse = datetimeColumnTemplate;
break;
case Types.BOOLEAN:
case Types.BIT:
templateToUse = booleanColumnTemplate;
break;
default:
if (column.getJdbcTypeName() != null) {
if (column.getJdbcTypeName().toUpperCase().equals(TypeMap.INTERVAL)) {
templateToUse = numberColumnTemplate;
break;
} else if (column.getMappedType().equals(TypeMap.TIMESTAMPTZ)
&& StringUtils.isNotBlank(this.dateTimeWithTimeZoneColumnTemplate)) {
templateToUse = this.dateTimeWithTimeZoneColumnTemplate;
break;
} else if (column.getMappedType().equals(TypeMap.TIMESTAMPLTZ)
&& StringUtils
.isNotBlank(this.dateTimeWithLocalTimeZoneColumnTemplate)) {
templateToUse = this.dateTimeWithLocalTimeZoneColumnTemplate;
break;
}
}
if (StringUtils.isBlank(templateToUse)
&& StringUtils.isNotBlank(this.otherColumnTemplate)) {
templateToUse = this.otherColumnTemplate;
break;
}
throw new NotImplementedException(column.getName() + " is of type "
+ column.getMappedType() + " with JDBC type of "
+ column.getJdbcTypeName());
}
}
if (dml == DataEventType.DELETE && isLob && requiresEmptyLobTemplateForDeletes()) {
templateToUse = emptyColumnTemplate;
} else if (isLob && trigger.isUseStreamLobs()) {
templateToUse = emptyColumnTemplate;
}
if (templateToUse != null) {
templateToUse = templateToUse.trim();
} else {
throw new NotImplementedException();
}
String formattedColumnText = FormatUtils.replace("columnName",
String.format("%s%s", columnPrefix, column.getName()), templateToUse);
formattedColumnText = FormatUtils.replace("columnSize",
column.getSize(), formattedColumnText);
formattedColumnText = FormatUtils.replace("masterCollation",
symmetricDialect.getMasterCollation(), formattedColumnText);
if (isLob) {
formattedColumnText = symmetricDialect.massageForLob(formattedColumnText, channel);
}
formattedColumnText = FormatUtils.replace("origTableAlias", origTableAlias,
formattedColumnText);
formattedColumnText = FormatUtils.replace("tableAlias", tableAlias, formattedColumnText);
formattedColumnText = FormatUtils.replace("prefixName", symmetricDialect.getTablePrefix(),
formattedColumnText);
return new ColumnString(formattedColumnText, isLob);
}
public String getOtherColumnTemplate() {
return otherColumnTemplate;
}
protected boolean noTimeColumnTemplate() {
return timeColumnTemplate == null || timeColumnTemplate.equals("null")
|| timeColumnTemplate.trim().equals("");
}
protected boolean noDateColumnTemplate() {
return dateColumnTemplate == null || dateColumnTemplate.equals("null")
|| dateColumnTemplate.trim().equals("");
}
protected String buildKeyVariablesDeclare(Column[] columns, String prefix) {
String text = "";
for (int i = 0; i < columns.length; i++) {
text += "declare @" + prefix + "pk" + i + " ";
switch (columns[i].getMappedTypeCode()) {
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
case Types.BIGINT:
text += "bigint\n";
break;
case Types.NUMERIC:
case Types.DECIMAL:
text += "decimal\n";
break;
case Types.FLOAT:
case Types.REAL:
case Types.DOUBLE:
text += "float\n";
break;
case Types.CHAR:
case Types.VARCHAR:
case ColumnTypes.NVARCHAR:
case ColumnTypes.LONGNVARCHAR:
case Types.LONGVARCHAR:
text += "varchar(1000)\n";
break;
case Types.DATE:
text += "date\n";
break;
case Types.TIME:
text += "time\n";
break;
case Types.TIMESTAMP:
text += "datetime\n";
break;
case Types.BOOLEAN:
case Types.BIT:
text += "bit\n";
break;
case Types.CLOB:
text += "varchar(max)\n";
break;
case Types.BLOB:
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
case -10: // SQL-Server ntext binary type
text += "varbinary(max)\n";
break;
case Types.OTHER:
text += "varbinary(max)\n";
break;
default:
if (columns[i].getJdbcTypeName() != null
&& columns[i].getJdbcTypeName().equalsIgnoreCase("interval")) {
text += "interval";
break;
}
throw new NotImplementedException(columns[i] + " is of type "
+ columns[i].getMappedType());
}
}
return text;
}
protected String buildKeyVariablesString(Column[] columns, String prefix) {
String text = "";
for (int i = 0; i < columns.length; i++) {
text += "@" + prefix + "pk" + i;
if (i + 1 < columns.length) {
text += ", ";
}
}
return text;
}
public String getClobColumnTemplate() {
return clobColumnTemplate;
}
public void setBooleanColumnTemplate(String booleanColumnTemplate) {
this.booleanColumnTemplate = booleanColumnTemplate;
}
public String getNewTriggerValue() {
return newTriggerValue;
}
public String getOldTriggerValue() {
return oldTriggerValue;
}
public String getBlobColumnTemplate() {
return blobColumnTemplate;
}
public String getWrappedBlobColumnTemplate() {
return wrappedBlobColumnTemplate;
}
protected String replaceDefaultSchema(String ddl, String defaultSchema) {
if (StringUtils.isNotBlank(defaultSchema)) {
ddl = FormatUtils.replace("defaultSchema", SymmetricUtils.quote(symmetricDialect, defaultSchema) + ".", ddl);
} else {
ddl = FormatUtils.replace("defaultSchema", "", ddl);
}
return ddl;
}
protected String replaceDefaultCatalog(String ddl, String defaultCatalog) {
if (StringUtils.isNotBlank(defaultCatalog)) {
ddl = FormatUtils.replace("defaultCatalog", SymmetricUtils.quote(symmetricDialect, defaultCatalog) + ".", ddl);
} else {
ddl = FormatUtils.replace("defaultCatalog", "", ddl);
}
return ddl;
}
public String getTimeColumnTemplate() {
return timeColumnTemplate;
}
public void setTimeColumnTemplate(String timeColumnTemplate) {
this.timeColumnTemplate = timeColumnTemplate;
}
public String getDateColumnTemplate() {
return dateColumnTemplate;
}
public void setDateColumnTemplate(String dateColumnTemplate) {
this.dateColumnTemplate = dateColumnTemplate;
}
public String getImageColumnTemplate() {
return imageColumnTemplate;
}
public void setImageColumnTemplate(String imageColumnTemplate) {
this.imageColumnTemplate = imageColumnTemplate;
}
protected class ColumnString {
String columnString;
boolean isBlobClob = false;
ColumnString(String columnExpression, boolean isBlobClob) {
this.columnString = columnExpression;
this.isBlobClob = isBlobClob;
}
@Override
public String toString() {
return StringUtils.isBlank(columnString) ? "null" : columnString;
}
}
public int toHashedValue() {
int hashedValue = 0;
if (sqlTemplates != null) {
for (String key : sqlTemplates.keySet()) {
hashedValue += sqlTemplates.get(key).hashCode();
}
}
return hashedValue;
}
}