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 if (history != null) {
177 targetTableName = history.getSourceTableName();
178 } else {
179 targetTableName = trigger.getSourceTableName();
180 }
181 } else {
182 targetTableName = trigger.getTargetTableName();
183 }
184 return targetTableName;
185 }
186
187 public String replaceTemplateVariables(IDbDialect dialect, DataEventType dml, Trigger trigger,
188 TriggerHistory history, String tablePrefix, Table metaData, String defaultCatalog, String defaultSchema,
189 String ddl) {
190
191 boolean resolveSchemaAndCatalogs = trigger.getSourceCatalogName() != null
192 || trigger.getSourceSchemaName() != null;
193
194 ddl = replace("targetTableName", getDefaultTargetTableName(trigger, history), ddl);
195
196 ddl = replace("defaultSchema",
197 resolveSchemaAndCatalogs && defaultSchema != null && defaultSchema.length() > 0 ? defaultSchema + "."
198 : "", ddl);
199 ddl = replace("defaultCatalog", resolveSchemaAndCatalogs && defaultCatalog != null
200 && defaultCatalog.length() > 0 ? defaultCatalog + "." : "", ddl);
201
202 ddl = replace("triggerName", dialect.getTriggerName(dml, triggerPrefix, dialect.getMaxTriggerNameLength(), trigger, history)
203 .toUpperCase(), ddl);
204 ddl = replace("engineName", dialect.getEngineName(), ddl);
205 ddl = replace("prefixName", tablePrefix, ddl);
206 ddl = replace("targetGroupId", trigger.getTargetGroupId(), ddl);
207 ddl = replace("channelName", trigger.getChannelId(), ddl);
208 ddl = replace("triggerHistoryId", Integer.toString(history == null ? -1 : history.getTriggerHistoryId()), ddl);
209 String triggerExpression = dialect.getTransactionTriggerExpression(trigger);
210 if (dialect.isTransactionIdOverrideSupported() && trigger.getTxIdExpression() != null) {
211 triggerExpression = trigger.getTxIdExpression();
212 }
213 ddl = replace("txIdExpression", triggerExpression, ddl);
214 ddl = replace("nodeSelectWhere", trigger.getNodeSelect(), ddl);
215 ddl = replace("nodeSelectWhereEscaped", replace("'", "''", trigger.getNodeSelect()), ddl);
216 ddl = replace("syncOnInsertCondition", trigger.getSyncOnInsertCondition(), ddl);
217 ddl = replace("syncOnUpdateCondition", trigger.getSyncOnUpdateCondition(), ddl);
218 ddl = replace("syncOnDeleteCondition", trigger.getSyncOnDeleteCondition(), ddl);
219 ddl = replace("syncOnIncomingBatchCondition", trigger.isSyncOnIncomingBatch() ? "1=1" : dialect
220 .getSyncTriggersExpression(), ddl);
221 ddl = replace("origTableAlias", ORIG_TABLE_ALIAS, ddl);
222
223 Column[] columns = trigger.orderColumnsForTable(metaData);
224 String columnsText = buildColumnString(ORIG_TABLE_ALIAS, newTriggerValue, columns);
225 ddl = replace("columns", columnsText, ddl);
226 if (trigger.isSyncColumnLevel()) {
227 columnsText = buildColumnString(ORIG_TABLE_ALIAS, oldTriggerValue, columns);
228 } else {
229 columnsText = "null";
230 }
231 ddl = replace("oldColumns", columnsText, ddl);
232 ddl = eval(containsBlobClobColumns(columns), "containsBlobClobColumns", ddl);
233
234
235 ddl = replace("tableName", history == null ? trigger.getSourceTableName() : history.getSourceTableName(), ddl);
236 ddl = replace("schemaName", (history == null ? (resolveSchemaAndCatalogs && trigger.getSourceSchemaName() != null ? trigger
237 .getSourceSchemaName()
238 + "." : "") : (resolveSchemaAndCatalogs && history.getSourceSchemaName() != null ? history
239 .getSourceSchemaName()
240 + "." : "")), ddl);
241
242 columns = metaData.getPrimaryKeyColumns();
243 columnsText = buildColumnString(ORIG_TABLE_ALIAS, oldTriggerValue, columns);
244 ddl = replace("oldKeys", columnsText, ddl);
245 ddl = replace("oldNewPrimaryKeyJoin", aliasedPrimaryKeyJoin(oldTriggerValue, newTriggerValue, columns), ddl);
246 ddl = replace("tableNewPrimaryKeyJoin", aliasedPrimaryKeyJoin(ORIG_TABLE_ALIAS, newTriggerValue, columns), ddl);
247 ddl = replace("primaryKeyWhereString", getPrimaryKeyWhereString(newTriggerValue, columns), ddl);
248
249
250 ddl = replace("newTriggerValue", newTriggerValue, ddl);
251 ddl = replace("oldTriggerValue", oldTriggerValue, ddl);
252 switch (dml) {
253 case DELETE:
254 ddl = replace("curTriggerValue", oldTriggerValue, ddl);
255 break;
256 case INSERT:
257 case UPDATE:
258 default:
259 ddl = replace("curTriggerValue", newTriggerValue, ddl);
260 break;
261 }
262 return ddl;
263 }
264
265 private String eval(boolean condition, String prop, String ddl) {
266 if (ddl != null) {
267 String ifStmt = "$(if:" + prop + ")";
268 String elseStmt = "$(else:" + prop + ")";
269 String endStmt = "$(end:" + prop + ")";
270 int ifIndex = ddl.indexOf(ifStmt);
271 if (ifIndex >= 0) {
272 int endIndex = ddl.indexOf(endStmt);
273 if (endIndex >= 0) {
274 String onTrue = ddl.substring(ifIndex + ifStmt.length(), endIndex);
275 String onFalse = "";
276 int elseIndex = onTrue.indexOf(elseStmt);
277 if (elseIndex >= 0) {
278 onFalse = onTrue.substring(elseIndex + elseStmt.length());
279 onTrue = onTrue.substring(0, elseIndex);
280 }
281
282 if (condition) {
283 ddl = ddl.substring(0, ifIndex) + onTrue + ddl.substring(endIndex + endStmt.length());
284 } else {
285 ddl = ddl.substring(0, ifIndex) + onFalse + ddl.substring(endIndex + endStmt.length());
286 }
287
288 } else {
289 throw new IllegalStateException(ifStmt + " has to have a " + endStmt);
290 }
291 }
292 }
293 return ddl;
294 }
295
296 private boolean containsBlobClobColumns(Column[] columns) {
297 for (Column column : columns) {
298 switch (column.getTypeCode()) {
299 case Types.CLOB:
300 case Types.BLOB:
301 case Types.BINARY:
302
303 case -10:
304 return true;
305 }
306 }
307 return false;
308 }
309
310 private String aliasedPrimaryKeyJoin(String aliasOne, String aliasTwo, Column[] columns) {
311 StringBuilder b = new StringBuilder();
312 for (Column column : columns) {
313 b.append(aliasOne).append(".\"").append(column.getName()).append("\"");
314 b.append("=").append(aliasTwo).append(".\"").append(column.getName()).append("\"");
315 if (!column.equals(columns[columns.length - 1])) {
316 b.append(" and ");
317 }
318 }
319
320 return b.toString();
321 }
322
323
324 private String getPrimaryKeyWhereString(String alias, Column[] columns) {
325 StringBuilder b = new StringBuilder();
326 for (Column column : columns) {
327 b.append("'\"").append(column.getName()).append("\"=");
328 switch (column.getTypeCode()) {
329 case Types.BIT:
330 case Types.TINYINT:
331 case Types.SMALLINT:
332 case Types.INTEGER:
333 case Types.BIGINT:
334 case Types.FLOAT:
335 case Types.REAL:
336 case Types.DOUBLE:
337 case Types.NUMERIC:
338 case Types.DECIMAL:
339 case Types.BOOLEAN:
340 b.append("'").append(triggerConcatCharacter);
341 b.append("rtrim(char(").append(alias).append(".\"").append(column.getName()).append("\"))");
342 b.append(triggerConcatCharacter).append("'");
343 break;
344 case Types.CHAR:
345 case Types.VARCHAR:
346 case Types.LONGVARCHAR:
347 b.append("'''").append(triggerConcatCharacter);
348 b.append(alias).append(".\"").append(column.getName()).append("\"");
349 b.append(triggerConcatCharacter).append("'''");
350 break;
351 case Types.DATE:
352 case Types.TIMESTAMP:
353 b.append("{ts '''").append(triggerConcatCharacter);
354 b.append("rtrim(char(").append(alias).append(".\"").append(column.getName()).append("\"))");
355 b.append(triggerConcatCharacter).append("'''}");
356 break;
357 }
358 if (!column.equals(columns[columns.length - 1])) {
359 b.append(" and ");
360 }
361 }
362 b.append("'");
363 return b.toString();
364 }
365
366 private String buildColumnString(String origTableAlias, String tableAlias, Column[] columns) {
367 String columnsText = "";
368 for (Column column : columns) {
369 String templateToUse = null;
370 switch (column.getTypeCode()) {
371 case Types.TINYINT:
372 case Types.SMALLINT:
373 case Types.INTEGER:
374 case Types.BIGINT:
375 case Types.FLOAT:
376 case Types.REAL:
377 case Types.DOUBLE:
378 case Types.NUMERIC:
379 case Types.DECIMAL:
380 templateToUse = numberColumnTemplate;
381 break;
382 case Types.CHAR:
383 case Types.VARCHAR:
384 case Types.LONGVARCHAR:
385 templateToUse = stringColumnTemplate;
386 break;
387 case Types.CLOB:
388 templateToUse = clobColumnTemplate;
389 break;
390 case Types.BLOB:
391 case Types.BINARY:
392 case Types.VARBINARY:
393 case Types.LONGVARBINARY:
394
395 case -10:
396 templateToUse = blobColumnTemplate;
397 break;
398 case Types.DATE:
399 case Types.TIME:
400 case Types.TIMESTAMP:
401 templateToUse = datetimeColumnTemplate;
402 break;
403 case Types.BOOLEAN:
404 case Types.BIT:
405 templateToUse = booleanColumnTemplate;
406 break;
407 case Types.NULL:
408 case Types.OTHER:
409 case Types.JAVA_OBJECT:
410 case Types.DISTINCT:
411 case Types.STRUCT:
412 case Types.REF:
413 case Types.DATALINK:
414 throw new NotImplementedException(column.getName() + " is of type " + column.getType());
415 }
416
417 if (templateToUse != null) {
418 templateToUse = templateToUse.trim();
419 } else {
420 throw new NotImplementedException();
421 }
422
423 columnsText = columnsText + "\n " + replace("columnName", column.getName(), templateToUse);
424
425 }
426
427 String LAST_COMMAN_TOKEN = triggerConcatCharacter + "','" + triggerConcatCharacter;
428
429 if (columnsText.endsWith(LAST_COMMAN_TOKEN)) {
430 columnsText = columnsText.substring(0, columnsText.length() - LAST_COMMAN_TOKEN.length());
431 }
432
433 columnsText = replace("origTableAlias", origTableAlias, columnsText);
434 return replace("tableAlias", tableAlias, columnsText);
435
436 }
437
438 private String replace(String prop, String replaceWith, String sourceString) {
439 return StringUtils.replace(sourceString, "$(" + prop + ")", replaceWith);
440 }
441
442 public void setStringColumnTemplate(String columnTemplate) {
443 this.stringColumnTemplate = columnTemplate;
444 }
445
446 public void setDatetimeColumnTemplate(String datetimeColumnTemplate) {
447 this.datetimeColumnTemplate = datetimeColumnTemplate;
448 }
449
450 public void setNumberColumnTemplate(String numberColumnTemplate) {
451 this.numberColumnTemplate = numberColumnTemplate;
452 }
453
454 public void setSqlTemplates(Map<String, String> sqlTemplates) {
455 this.sqlTemplates = sqlTemplates;
456 }
457
458 public String getClobColumnTemplate() {
459 return clobColumnTemplate;
460 }
461
462 public void setClobColumnTemplate(String clobColumnTemplate) {
463 this.clobColumnTemplate = clobColumnTemplate;
464 }
465
466 public void setBooleanColumnTemplate(String booleanColumnTemplate) {
467 this.booleanColumnTemplate = booleanColumnTemplate;
468 }
469
470 public void setTriggerConcatCharacter(String triggerConcatCharacter) {
471 this.triggerConcatCharacter = triggerConcatCharacter;
472 }
473
474 public String getNewTriggerValue() {
475 return newTriggerValue;
476 }
477
478 public void setNewTriggerValue(String newTriggerValue) {
479 this.newTriggerValue = newTriggerValue;
480 }
481
482 public String getOldTriggerValue() {
483 return oldTriggerValue;
484 }
485
486 public void setOldTriggerValue(String oldTriggerValue) {
487 this.oldTriggerValue = oldTriggerValue;
488 }
489
490 public void setTriggerPrefix(String triggerPrefix) {
491 this.triggerPrefix = triggerPrefix;
492 }
493
494 public String getBlobColumnTemplate() {
495 return blobColumnTemplate;
496 }
497
498 public void setBlobColumnTemplate(String blobColumnTemplate) {
499 this.blobColumnTemplate = blobColumnTemplate;
500 }
501
502 public void setFunctionInstalledSql(String functionInstalledSql) {
503 this.functionInstalledSql = functionInstalledSql;
504 }
505
506 public void setFunctionTemplatesToInstall(Map<String, String> functionTemplatesToInstall) {
507 this.functionTemplatesToInstall = functionTemplatesToInstall;
508 }
509
510 public String getFunctionSql(String functionName) {
511 if (this.functionTemplatesToInstall != null) {
512 return this.functionTemplatesToInstall.get(functionName);
513 } else {
514 return null;
515 }
516 }
517
518 public String getFunctionInstalledSql(String functionName) {
519 if (functionInstalledSql != null) {
520 String ddl = replace("functionName", functionName, functionInstalledSql);
521 return ddl;
522 } else {
523 return null;
524 }
525 }
526
527 }