1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 package org.jumpmind.symmetric.db.mssql;
22
23 import java.util.ArrayList;
24
25 import org.apache.commons.collections.CollectionUtils;
26 import org.apache.commons.logging.Log;
27 import org.apache.commons.logging.LogFactory;
28 import org.apache.ddlutils.model.Column;
29 import org.apache.ddlutils.model.Table;
30 import org.jumpmind.symmetric.db.AbstractDbDialect;
31 import org.jumpmind.symmetric.db.BinaryEncoding;
32 import org.jumpmind.symmetric.db.IDbDialect;
33 import org.jumpmind.symmetric.load.IColumnFilter;
34 import org.jumpmind.symmetric.load.IDataLoaderContext;
35 import org.jumpmind.symmetric.load.StatementBuilder.DmlType;
36 import org.jumpmind.symmetric.model.Trigger;
37 import org.jumpmind.symmetric.model.TriggerHistory;
38
39 /***
40 * This dialect was tested with the jTDS JDBC driver on SQL Server 2005.
41 *
42 * TODO support text and image fields, they cannot be referenced from the
43 * inserted or deleted tables in the triggers. Here is one idea we could
44 * implement: http://www.devx.com/getHelpOn/10MinuteSolution/16544
45 */
46 public class MsSqlDbDialect extends AbstractDbDialect implements IDbDialect {
47
48 static final Log logger = LogFactory.getLog(MsSqlDbDialect.class);
49
50 protected void initForSpecificDialect() {
51 }
52
53 protected boolean allowsNullForIdentityColumn() {
54 return false;
55 }
56
57 @Override
58 public IColumnFilter getDatabaseColumnFilter() {
59 return new IColumnFilter() {
60 int[] indexesToRemove = null;
61
62 public String[] filterColumnsNames(IDataLoaderContext ctx, DmlType dml, Table table, String[] columnNames) {
63 ArrayList<String> columns = new ArrayList<String>();
64 CollectionUtils.addAll(columns, columnNames);
65 if (dml == DmlType.UPDATE) {
66 Column[] autoIncrementColumns = table.getAutoIncrementColumns();
67 indexesToRemove = new int[autoIncrementColumns.length];
68 int i = 0;
69 for (Column column : autoIncrementColumns) {
70 String name = column.getName();
71 int index = columns.indexOf(name);
72
73 if (index < 0) {
74 name = name.toLowerCase();
75 index = columns.indexOf(name);
76 }
77 if (index < 0) {
78 name = name.toUpperCase();
79 index = columns.indexOf(name);
80 }
81 indexesToRemove[i++] = index;
82 columns.remove(name);
83 }
84 }
85 return columns.toArray(new String[columns.size()]);
86 }
87
88 public Object[] filterColumnsValues(IDataLoaderContext ctx, DmlType dml, Table table, Object[] columnValues) {
89 if (dml == DmlType.UPDATE && indexesToRemove != null) {
90 ArrayList<Object> values = new ArrayList<Object>();
91 CollectionUtils.addAll(values, columnValues);
92 for (int index : indexesToRemove) {
93
94 if (index >= 0) {
95 values.remove(index);
96 }
97
98 }
99 return values.toArray(new Object[values.size()]);
100 }
101 return columnValues;
102 }
103
104 public boolean isAutoRegister() {
105 return false;
106 }
107
108 };
109 }
110
111 @Override
112 public void prepareTableForDataLoad(Table table) {
113 if (table != null && table.getAutoIncrementColumns().length > 0) {
114 jdbcTemplate.execute("SET IDENTITY_INSERT " + table.getName() + " ON");
115 }
116 }
117
118 @Override
119 public void cleanupAfterDataLoad(Table table) {
120 if (table != null && table.getAutoIncrementColumns().length > 0) {
121 jdbcTemplate.execute("SET IDENTITY_INSERT " + table.getName() + " OFF");
122 }
123 }
124
125 @Override
126 public BinaryEncoding getBinaryEncoding() {
127 return BinaryEncoding.BASE64;
128 }
129
130 @Override
131 protected boolean doesTriggerExistOnPlatform(String catalogName, String schema, String tableName, String triggerName) {
132 return jdbcTemplate.queryForInt("select count(*) from sysobjects where type = 'TR' AND name = ?",
133 new Object[] { triggerName }) > 0;
134 }
135
136 public void disableSyncTriggers(String nodeId) {
137 if (nodeId == null) {
138 nodeId = "";
139 }
140 jdbcTemplate.update("DECLARE @CI VarBinary(128);" + "SET @CI=cast ('1" + nodeId
141 + "' as varbinary(128));" + "SET context_info @CI;");
142 }
143
144 public void enableSyncTriggers() {
145 jdbcTemplate.update("set context_info 0x0");
146 }
147
148 public String getSyncTriggersExpression() {
149 return "dbo.fn_sym_triggers_disabled() = 0";
150 }
151
152 public String getTransactionTriggerExpression(Trigger trigger) {
153 return "@TransactionId";
154 }
155
156 public boolean supportsTransactionId() {
157 return true;
158 }
159
160 /***
161 * SQL Server always pads character fields out to the right to fill out
162 * field with space characters.
163 *
164 * @return true always
165 */
166 public boolean isCharSpacePadded() {
167 return true;
168 }
169
170 /***
171 * @return false always
172 */
173 public boolean isCharSpaceTrimmed() {
174 return false;
175 }
176
177 public boolean isTransactionIdOverrideSupported() {
178 return false;
179 }
180
181 public boolean isDateOverrideToTimestamp() {
182 return true;
183 }
184
185 /***
186 * SQL Server pads an empty string with spaces.
187 *
188 * @return false always
189 */
190 public boolean isEmptyStringNulled() {
191 return false;
192 }
193
194 /***
195 * Nothing to do for SQL Server
196 */
197 public void purge() {
198 }
199
200 public String getDefaultCatalog() {
201 return (String) jdbcTemplate.queryForObject("select DB_NAME()", String.class);
202 }
203
204 public String getDefaultSchema() {
205 return (String) jdbcTemplate.queryForObject("select SCHEMA_NAME()", String.class);
206 }
207
208 public void removeTrigger(String catalogName, String schemaName, String triggerName, String tableName, TriggerHistory oldHistory) {
209 schemaName = schemaName == null ? "" : (schemaName + ".");
210 try {
211 jdbcTemplate.update("drop trigger " + schemaName + triggerName);
212 } catch (Exception e) {
213 logger.warn("Trigger does not exist");
214 }
215 }
216
217 public boolean storesUpperCaseNamesInCatalog() {
218 return true;
219 }
220
221 }