1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 package org.jumpmind.symmetric.db;
23
24 import java.sql.Types;
25 import java.util.Map;
26
27 import org.apache.commons.lang.NotImplementedException;
28 import org.apache.commons.lang.StringUtils;
29 import org.apache.ddlutils.model.Column;
30 import org.apache.ddlutils.model.Table;
31 import org.jumpmind.symmetric.model.DataEventType;
32 import org.jumpmind.symmetric.model.Node;
33 import org.jumpmind.symmetric.model.Trigger;
34 import org.jumpmind.symmetric.model.TriggerHistory;
35
36 public class SqlTemplate {
37
38 private static final String ORIG_TABLE_ALIAS = "orig";
39
40 static final String INSERT_TRIGGER_TEMPLATE = "insertTriggerTemplate";
41
42 static final String UPDATE_TRIGGER_TEMPLATE = "updateTriggerTemplate";
43
44 static final String DELETE_TRIGGER_TEMPLATE = "deleteTriggerTemplate";
45
46 static final String INITIAL_LOAD_SQL_TEMPLATE = "initialLoadSqlTemplate";
47
48 private Map<String, String> sqlTemplates;
49
50 private Map<String, String> functionTemplatesToInstall;
51
52 private String functionInstalledSql;
53
54 private String triggerPrefix;
55
56 private String stringColumnTemplate;
57
58 private String numberColumnTemplate;
59
60 private String datetimeColumnTemplate;
61
62 private String clobColumnTemplate;
63
64 private String blobColumnTemplate;
65
66 private String booleanColumnTemplate;
67
68 private String triggerConcatCharacter;
69
70 private String newTriggerValue;
71
72 private String oldTriggerValue;
73
74 public String createInitalLoadSql(Node node, IDbDialect dialect, Trigger trig, Table metaData) {
75 String sql = sqlTemplates.get(INITIAL_LOAD_SQL_TEMPLATE);
76
77 Column[] columns = trig.orderColumnsForTable(metaData);
78 String columnsText = buildColumnString("t", "t", columns);
79 sql = replace("columns", columnsText, sql);
80
81 sql = replace("tableName", trig.getSourceTableName(), sql);
82 sql = replace("schemaName", trig.getSourceSchemaName() != null ? trig.getSourceSchemaName() + "." : "", sql);
83 sql = replace("whereClause", trig.getInitialLoadSelect() == null ? "1=1" : trig.getInitialLoadSelect(), sql);
84 sql = replace("primaryKeyWhereString", getPrimaryKeyWhereString("t", metaData.getPrimaryKeyColumns()), sql);
85
86
87
88 sql = replace("groupId", node.getNodeGroupId(), sql);
89 sql = replace("externalId", node.getExternalId(), sql);
90 sql = replace("nodeId", node.getNodeId(), sql);
91
92 return sql;
93 }
94
95 public String createPurgeSql(Node node, IDbDialect dialect, Trigger trig, TriggerHistory hist) {
96
97 String sql = "delete from " + getDefaultTargetTableName(trig, hist);
98
99
100
101 return sql;
102 }
103
104 public String createCsvDataSql(Trigger trig, Table metaData, String whereClause) {
105 String sql = sqlTemplates.get(INITIAL_LOAD_SQL_TEMPLATE);
106
107 Column[] columns = trig.orderColumnsForTable(metaData);
108 String columnsText = buildColumnString("t", "t", columns);
109 sql = replace("columns", columnsText, sql);
110
111 sql = replace("tableName", trig.getSourceTableName(), sql);
112 sql = replace("schemaName", trig.getSourceSchemaName() != null ? trig.getSourceSchemaName() + "." : "", sql);
113 sql = replace("whereClause", whereClause, sql);
114 sql = replace("primaryKeyWhereString", getPrimaryKeyWhereString("t", metaData.getPrimaryKeyColumns()), sql);
115
116 return sql;
117 }
118
119 public String createCsvPrimaryKeySql(Trigger trig, Table metaData, String whereClause) {
120 String sql = sqlTemplates.get(INITIAL_LOAD_SQL_TEMPLATE);
121
122 Column[] columns = metaData.getPrimaryKeyColumns();
123 String columnsText = buildColumnString("t", "t", columns);
124 sql = replace("columns", columnsText, sql);
125
126 sql = replace("tableName", trig.getSourceTableName(), sql);
127 sql = replace("schemaName", trig.getSourceSchemaName() != null ? trig.getSourceSchemaName() + "." : "", sql);
128 sql = replace("whereClause", whereClause, sql);
129 sql = replace("primaryKeyWhereString", getPrimaryKeyWhereString("t", columns), sql);
130
131 return sql;
132 }
133
134 public String[] getFunctionsToInstall() {
135 if (functionTemplatesToInstall != null) {
136 return functionTemplatesToInstall.keySet().toArray(new String[functionTemplatesToInstall.size()]);
137 } else {
138 return new String[0];
139 }
140 }
141
142 public String createFunctionDDL(String name) {
143 if (functionTemplatesToInstall != null) {
144 return functionTemplatesToInstall.get(name);
145 } else {
146 return null;
147 }
148 }
149
150 /***
151 * TODO Document all the 'templated' values available for building triggers.
152 */
153 public String createTriggerDDL(IDbDialect dialect, DataEventType dml, Trigger trigger, TriggerHistory history,
154 String tablePrefix, Table metaData, String defaultCatalog, String defaultSchema) {
155
156 String ddl = sqlTemplates.get(dml.name().toLowerCase() + "TriggerTemplate");
157 if (ddl == null) {
158 throw new NotImplementedException(dml.name() + " trigger is not implemented for "
159 + dialect.getPlatform().getName());
160 }
161 return replaceTemplateVariables(dialect, dml, trigger, history, tablePrefix, metaData, defaultCatalog,
162 defaultSchema, ddl);
163 }
164
165 public String createPostTriggerDDL(IDbDialect dialect, DataEventType dml, Trigger trigger, TriggerHistory history,
166 String tablePrefix, Table metaData, String defaultCatalog, String defaultSchema) {
167
168 String ddl = sqlTemplates.get(dml.name().toLowerCase() + "PostTriggerTemplate");
169 return replaceTemplateVariables(dialect, dml, trigger, history, tablePrefix, metaData, defaultCatalog,
170 defaultSchema, ddl);
171 }
172
173 private String getDefaultTargetTableName(Trigger trigger, TriggerHistory history) {
174 String targetTableName = null;
175 if (StringUtils.isBlank(trigger.getTargetTableName())) {
176 targetTableName = history.getSourceTableName();
177 } else {
178 targetTableName = trigger.getTargetTableName();
179 }
180 return targetTableName;
181 }
182
183 public String replaceTemplateVariables(IDbDialect dialect, DataEventType dml, Trigger trigger,
184 TriggerHistory history, String tablePrefix, Table metaData, String defaultCatalog, String defaultSchema,
185 String ddl) {
186
187 boolean resolveSchemaAndCatalogs = trigger.getSourceCatalogName() != null
188 || trigger.getSourceSchemaName() != null;
189
190 ddl = replace("targetTableName", getDefaultTargetTableName(trigger, history), ddl);
191
192 ddl = replace("defaultSchema",
193 resolveSchemaAndCatalogs && defaultSchema != null && defaultSchema.length() > 0 ? defaultSchema + "."
194 : "", ddl);
195 ddl = replace("defaultCatalog", resolveSchemaAndCatalogs && defaultCatalog != null
196 && defaultCatalog.length() > 0 ? defaultCatalog + "." : "", ddl);
197
198 ddl = replace("triggerName", trigger.getTriggerName(dml, triggerPrefix, dialect.getMaxTriggerNameLength())
199 .toUpperCase(), ddl);
200 ddl = replace("engineName", dialect.getEngineName(), ddl);
201 ddl = replace("prefixName", tablePrefix, ddl);
202 ddl = replace("targetGroupId", trigger.getTargetGroupId(), ddl);
203 ddl = replace("channelName", trigger.getChannelId(), ddl);
204 ddl = replace("triggerHistoryId", Integer.toString(history.getTriggerHistoryId()), ddl);
205 String triggerExpression = dialect.getTransactionTriggerExpression(trigger);
206 if (dialect.isTransactionIdOverrideSupported() && trigger.getTxIdExpression() != null) {
207 triggerExpression = trigger.getTxIdExpression();
208 }
209 ddl = replace("txIdExpression", triggerExpression, ddl);
210 ddl = replace("nodeSelectWhere", trigger.getNodeSelect(), ddl);
211 ddl = replace("nodeSelectWhereEscaped", replace("'", "''", trigger.getNodeSelect()), ddl);
212 ddl = replace("syncOnInsertCondition", trigger.getSyncOnInsertCondition(), ddl);
213 ddl = replace("syncOnUpdateCondition", trigger.getSyncOnUpdateCondition(), ddl);
214 ddl = replace("syncOnDeleteCondition", trigger.getSyncOnDeleteCondition(), ddl);
215 ddl = replace("syncOnIncomingBatchCondition", trigger.isSyncOnIncomingBatch() ? "1=1" : dialect
216 .getSyncTriggersExpression(), ddl);
217 ddl = replace("origTableAlias", ORIG_TABLE_ALIAS, ddl);
218
219 Column[] columns = trigger.orderColumnsForTable(metaData);
220 String columnsText = buildColumnString(ORIG_TABLE_ALIAS, newTriggerValue, columns);
221 ddl = replace("columns", columnsText, ddl);
222 ddl = eval(containsBlobClobColumns(columns), "containsBlobClobColumns", ddl);
223
224
225 ddl = replace("tableName", history.getSourceTableName(), ddl);
226 ddl = replace("schemaName", resolveSchemaAndCatalogs && history.getSourceSchemaName() != null ? history
227 .getSourceSchemaName()
228 + "." : "", ddl);
229
230 columns = metaData.getPrimaryKeyColumns();
231 columnsText = buildColumnString(ORIG_TABLE_ALIAS, oldTriggerValue, columns);
232 ddl = replace("oldKeys", columnsText, ddl);
233 ddl = replace("oldNewPrimaryKeyJoin", aliasedPrimaryKeyJoin(oldTriggerValue, newTriggerValue, columns), ddl);
234 ddl = replace("tableNewPrimaryKeyJoin", aliasedPrimaryKeyJoin(ORIG_TABLE_ALIAS, newTriggerValue, columns), ddl);
235 ddl = replace("primaryKeyWhereString", getPrimaryKeyWhereString(newTriggerValue, columns), ddl);
236
237
238 ddl = replace("newTriggerValue", newTriggerValue, ddl);
239 ddl = replace("oldTriggerValue", oldTriggerValue, ddl);
240 switch (dml) {
241 case DELETE:
242 ddl = replace("curTriggerValue", oldTriggerValue, ddl);
243 break;
244 case INSERT:
245 case UPDATE:
246 default:
247 ddl = replace("curTriggerValue", newTriggerValue, ddl);
248 break;
249 }
250 return ddl;
251 }
252
253 private String eval(boolean condition, String prop, String ddl) {
254 if (ddl != null) {
255 String ifStmt = "$(if:" + prop + ")";
256 String elseStmt = "$(else:" + prop + ")";
257 String endStmt = "$(end:" + prop + ")";
258 int ifIndex = ddl.indexOf(ifStmt);
259 if (ifIndex >= 0) {
260 int endIndex = ddl.indexOf(endStmt);
261 if (endIndex >= 0) {
262 String onTrue = ddl.substring(ifIndex + ifStmt.length(), endIndex);
263 String onFalse = "";
264 int elseIndex = onTrue.indexOf(elseStmt);
265 if (elseIndex >= 0) {
266 onFalse = onTrue.substring(elseIndex + elseStmt.length());
267 onTrue = onTrue.substring(0, elseIndex);
268 }
269
270 if (condition) {
271 ddl = ddl.substring(0, ifIndex) + onTrue + ddl.substring(endIndex + endStmt.length());
272 } else {
273 ddl = ddl.substring(0, ifIndex) + onFalse + ddl.substring(endIndex + endStmt.length());
274 }
275
276 } else {
277 throw new IllegalStateException(ifStmt + " has to have a " + endStmt);
278 }
279 }
280 }
281 return ddl;
282 }
283
284 private boolean containsBlobClobColumns(Column[] columns) {
285 for (Column column : columns) {
286 switch (column.getTypeCode()) {
287 case Types.CLOB:
288 case Types.BLOB:
289 case Types.BINARY:
290 return true;
291 }
292 }
293 return false;
294 }
295
296 private String aliasedPrimaryKeyJoin(String aliasOne, String aliasTwo, Column[] columns) {
297 StringBuilder b = new StringBuilder();
298 for (Column column : columns) {
299 b.append(aliasOne).append(".\"").append(column.getName()).append("\"");
300 b.append("=").append(aliasTwo).append(".\"").append(column.getName()).append("\"");
301 if (!column.equals(columns[columns.length - 1])) {
302 b.append(" and ");
303 }
304 }
305
306 return b.toString();
307 }
308
309
310 private String getPrimaryKeyWhereString(String alias, Column[] columns) {
311 StringBuilder b = new StringBuilder();
312 for (Column column : columns) {
313 b.append("'\"").append(column.getName()).append("\"=");
314 switch (column.getTypeCode()) {
315 case Types.BIT:
316 case Types.TINYINT:
317 case Types.SMALLINT:
318 case Types.INTEGER:
319 case Types.BIGINT:
320 case Types.FLOAT:
321 case Types.REAL:
322 case Types.DOUBLE:
323 case Types.NUMERIC:
324 case Types.DECIMAL:
325 case Types.BOOLEAN:
326 b.append("'").append(triggerConcatCharacter);
327 b.append("rtrim(char(").append(alias).append(".\"").append(column.getName()).append("\"))");
328 b.append(triggerConcatCharacter).append("'");
329 break;
330 case Types.CHAR:
331 case Types.VARCHAR:
332 case Types.LONGVARCHAR:
333 b.append("'''").append(triggerConcatCharacter);
334 b.append(alias).append(".\"").append(column.getName()).append("\"");
335 b.append(triggerConcatCharacter).append("'''");
336 break;
337 case Types.DATE:
338 case Types.TIMESTAMP:
339 b.append("{ts '''").append(triggerConcatCharacter);
340 b.append("rtrim(char(").append(alias).append(".\"").append(column.getName()).append("\"))");
341 b.append(triggerConcatCharacter).append("'''}");
342 break;
343 }
344 if (!column.equals(columns[columns.length - 1])) {
345 b.append(" and ");
346 }
347 }
348 b.append("'");
349 return b.toString();
350 }
351
352 private String buildColumnString(String origTableAlias, String tableAlias, Column[] columns) {
353 String columnsText = "";
354 for (Column column : columns) {
355 String templateToUse = null;
356 switch (column.getTypeCode()) {
357 case Types.TINYINT:
358 case Types.SMALLINT:
359 case Types.INTEGER:
360 case Types.BIGINT:
361 case Types.FLOAT:
362 case Types.REAL:
363 case Types.DOUBLE:
364 case Types.NUMERIC:
365 case Types.DECIMAL:
366 templateToUse = numberColumnTemplate;
367 break;
368 case Types.CHAR:
369 case Types.VARCHAR:
370 case Types.LONGVARCHAR:
371 templateToUse = stringColumnTemplate;
372 break;
373 case Types.CLOB:
374 templateToUse = clobColumnTemplate;
375 break;
376 case Types.BLOB:
377 case Types.BINARY:
378 case Types.VARBINARY:
379 case Types.LONGVARBINARY:
380 templateToUse = blobColumnTemplate;
381 break;
382 case Types.DATE:
383 case Types.TIME:
384 case Types.TIMESTAMP:
385 templateToUse = datetimeColumnTemplate;
386 break;
387 case Types.BOOLEAN:
388 case Types.BIT:
389 templateToUse = booleanColumnTemplate;
390 break;
391 case Types.NULL:
392 case Types.OTHER:
393 case Types.JAVA_OBJECT:
394 case Types.DISTINCT:
395 case Types.STRUCT:
396 case Types.REF:
397 case Types.DATALINK:
398 throw new NotImplementedException(column.getName() + " is of type " + column.getType());
399 }
400
401 if (templateToUse != null) {
402 templateToUse = templateToUse.trim();
403 } else {
404 throw new NotImplementedException();
405 }
406
407 columnsText = columnsText + "\n " + replace("columnName", column.getName(), templateToUse);
408
409 }
410
411 String LAST_COMMAN_TOKEN = triggerConcatCharacter + "','" + triggerConcatCharacter;
412
413 if (columnsText.endsWith(LAST_COMMAN_TOKEN)) {
414 columnsText = columnsText.substring(0, columnsText.length() - LAST_COMMAN_TOKEN.length());
415 }
416
417 columnsText = replace("origTableAlias", origTableAlias, columnsText);
418 return replace("tableAlias", tableAlias, columnsText);
419
420 }
421
422 private String replace(String prop, String replaceWith, String sourceString) {
423 return StringUtils.replace(sourceString, "$(" + prop + ")", replaceWith);
424 }
425
426 public void setStringColumnTemplate(String columnTemplate) {
427 this.stringColumnTemplate = columnTemplate;
428 }
429
430 public void setDatetimeColumnTemplate(String datetimeColumnTemplate) {
431 this.datetimeColumnTemplate = datetimeColumnTemplate;
432 }
433
434 public void setNumberColumnTemplate(String numberColumnTemplate) {
435 this.numberColumnTemplate = numberColumnTemplate;
436 }
437
438 public void setSqlTemplates(Map<String, String> sqlTemplates) {
439 this.sqlTemplates = sqlTemplates;
440 }
441
442 public String getClobColumnTemplate() {
443 return clobColumnTemplate;
444 }
445
446 public void setClobColumnTemplate(String clobColumnTemplate) {
447 this.clobColumnTemplate = clobColumnTemplate;
448 }
449
450 public void setBooleanColumnTemplate(String booleanColumnTemplate) {
451 this.booleanColumnTemplate = booleanColumnTemplate;
452 }
453
454 public void setTriggerConcatCharacter(String triggerConcatCharacter) {
455 this.triggerConcatCharacter = triggerConcatCharacter;
456 }
457
458 public String getNewTriggerValue() {
459 return newTriggerValue;
460 }
461
462 public void setNewTriggerValue(String newTriggerValue) {
463 this.newTriggerValue = newTriggerValue;
464 }
465
466 public String getOldTriggerValue() {
467 return oldTriggerValue;
468 }
469
470 public void setOldTriggerValue(String oldTriggerValue) {
471 this.oldTriggerValue = oldTriggerValue;
472 }
473
474 public void setTriggerPrefix(String triggerPrefix) {
475 this.triggerPrefix = triggerPrefix;
476 }
477
478 public String getBlobColumnTemplate() {
479 return blobColumnTemplate;
480 }
481
482 public void setBlobColumnTemplate(String blobColumnTemplate) {
483 this.blobColumnTemplate = blobColumnTemplate;
484 }
485
486 public void setFunctionInstalledSql(String functionInstalledSql) {
487 this.functionInstalledSql = functionInstalledSql;
488 }
489
490 public void setFunctionTemplatesToInstall(Map<String, String> functionTemplatesToInstall) {
491 this.functionTemplatesToInstall = functionTemplatesToInstall;
492 }
493
494 public String getFunctionSql(String functionName) {
495 if (this.functionTemplatesToInstall != null) {
496 return this.functionTemplatesToInstall.get(functionName);
497 } else {
498 return null;
499 }
500 }
501
502 public String getFunctionInstalledSql(String functionName) {
503 if (functionInstalledSql != null) {
504 String ddl = replace("functionName", functionName, functionInstalledSql);
505 return ddl;
506 } else {
507 return null;
508 }
509 }
510 }