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