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.load;
23
24 import java.io.ByteArrayInputStream;
25 import java.io.ByteArrayOutputStream;
26 import java.math.BigDecimal;
27 import java.math.RoundingMode;
28 import java.text.DecimalFormat;
29 import java.util.zip.ZipInputStream;
30
31 import org.apache.commons.lang.ArrayUtils;
32 import org.apache.commons.math.random.RandomDataImpl;
33 import org.jumpmind.symmetric.common.Constants;
34 import org.jumpmind.symmetric.common.csv.CsvConstants;
35 import org.jumpmind.symmetric.db.mssql.MsSqlDbDialect;
36 import org.jumpmind.symmetric.db.mysql.MySqlDbDialect;
37 import org.jumpmind.symmetric.db.oracle.OracleDbDialect;
38 import org.jumpmind.symmetric.db.postgresql.PostgreSqlDbDialect;
39 import org.jumpmind.symmetric.test.TestConstants;
40 import org.jumpmind.symmetric.transport.TransportUtils;
41 import org.junit.Assert;
42 import org.junit.Test;
43
44 import com.csvreader.CsvWriter;
45
46 public class DataLoaderTest extends AbstractDataLoaderTest {
47
48 public DataLoaderTest() throws Exception {
49 }
50
51 public DataLoaderTest(String db) {
52 super(db);
53 }
54
55 @Test
56 public void testInsertExisting() throws Exception {
57 String[] values = { getNextId(), "string2", "string not null2", "char2", "char not null2",
58 "2007-01-02 03:20:10.0", "2007-02-03 04:05:06.0", "0", "47", "67.89",
59 "-0.0747663" };
60 massageExpectectedResultsForDialect(values);
61 testSimple(CsvConstants.INSERT, values, values);
62
63 values[1] = "insert fallback to update";
64 massageExpectectedResultsForDialect(values);
65 testSimple(CsvConstants.INSERT, values, values);
66 }
67
68 @Test
69 public void testLargeDouble() throws Exception {
70 String[] values = new String[TEST_COLUMNS.length];
71 values[0] = getNextId();
72 values[10] = "-0.07476635514018691588785046728971962617";
73 String[] expectedValues = (String[]) ArrayUtils.clone(values);
74 massageExpectectedResultsForDialect(expectedValues);
75 testSimple(CsvConstants.INSERT, values, expectedValues);
76 }
77
78 @Test
79 public void testDecimalLocale() throws Exception {
80 String[] values = new String[TEST_COLUMNS.length];
81 values[0] = getNextId();
82 values[10] = "123456,99";
83 String[] expectedValues = (String[]) ArrayUtils.clone(values);
84 massageExpectectedResultsForDialect(expectedValues);
85 testSimple(CsvConstants.INSERT, values, expectedValues);
86 }
87
88 @Test
89 public void testUpdateNotExisting() throws Exception {
90 String id = getNextId();
91 String[] values = { id, "it's /a/ string", "it's -not- null", "You're a \"character\"", "Where are you?",
92 "2007-12-31 02:33:45.0", "2007-12-31 23:59:59.0", "1", "13", "9.95", "-0.0747", id };
93 String[] expectedValues = (String[]) ArrayUtils.subarray(values, 0, values.length - 1);
94 massageExpectectedResultsForDialect(expectedValues);
95 testSimple(CsvConstants.UPDATE, values, expectedValues);
96 }
97
98 @Test
99 public void testStringQuotes() throws Exception {
100 String[] values = new String[TEST_COLUMNS.length];
101 values[0] = getNextId();
102 values[1] = "It's \"quoted,\" with a comma";
103 values[2] = "two 'ticks'";
104 values[3] = "One quote\"";
105 values[4] = "One comma,";
106 testSimple(CsvConstants.INSERT, values, values);
107 }
108
109 @Test
110 public void testStringSpaces() throws Exception {
111 String[] values = new String[TEST_COLUMNS.length];
112 values[0] = getNextId();
113 values[1] = " two spaces before";
114 values[2] = "two spaces after ";
115 values[3] = " one space before";
116 values[4] = "one space after ";
117 testSimple(CsvConstants.INSERT, values, values);
118 }
119
120 @Test
121 public void testStringOneSpace() throws Exception {
122 String[] values = new String[TEST_COLUMNS.length];
123 values[0] = getNextId();
124 values[2] = values[4] = " ";
125 testSimple(CsvConstants.INSERT, values, values);
126 }
127
128 @Test
129 public void testStringEmpty() throws Exception {
130 String[] values = new String[TEST_COLUMNS.length];
131 values[0] = getNextId();
132 values[1] = values[2] = values[3] = values[4] = "";
133 testSimple(CsvConstants.INSERT, values, values);
134 }
135
136 @Test
137 public void testStringNull() throws Exception {
138 String[] values = new String[TEST_COLUMNS.length];
139 values[0] = getNextId();
140 testSimple(CsvConstants.INSERT, values, values);
141 }
142
143 @Test
144 public void testStringBackslash() throws Exception {
145 String[] values = new String[TEST_COLUMNS.length];
146 values[0] = getNextId();
147 values[1] = "Here's a //, a (backslash)";
148 values[2] = "Fix TODO";
149
150
151 values[3] = "Tick quote //'//\"";
152 values[4] = "Comma quote //,//\"";
153 testSimple(CsvConstants.INSERT, values, values);
154 }
155
156 @Test
157 public void testDeleteExisting() throws Exception {
158 String[] values = { getNextId(), "a row to be deleted", "testDeleteExisting", "char2", "char not null2",
159 "2007-01-02 03:20:10.0", "2007-02-03 04:05:06.0", "0", "47", "67.89", "-0.0747" };
160 massageExpectectedResultsForDialect(values);
161 testSimple(CsvConstants.INSERT, values, values);
162 testSimple(CsvConstants.DELETE, new String[] { getId() }, null);
163 }
164
165 @Test
166 public void testDeleteNotExisting() throws Exception {
167 testSimple(CsvConstants.DELETE, new String[] { getNextId() }, null);
168 }
169
170 @Test
171 public void testColumnNotExisting() throws Exception {
172 String[] columns = (String[]) ArrayUtils.add(TEST_COLUMNS, "Unknown_Column");
173 String[] values = { getNextId(), "testColumnNotExisting", "string not null", "char", "char not null",
174 "2007-01-02 00:00:00.0", "2007-02-03 04:05:06.0", "0", "47", "67.89", "-0.0747", "i do not exist!" };
175 String[] expectedValues = (String[]) ArrayUtils.subarray(values, 0, values.length - 1);
176
177 ByteArrayOutputStream out = new ByteArrayOutputStream();
178 CsvWriter writer = getWriter(out);
179 writer.writeRecord(new String[] { CsvConstants.NODEID, TestConstants.TEST_CLIENT_EXTERNAL_ID });
180 writeTable(writer, TEST_TABLE, TEST_KEYS, columns);
181 String nextBatchId = getNextBatchId();
182 writer.writeRecord(new String[] { CsvConstants.BATCH, nextBatchId });
183 writer.write(CsvConstants.INSERT);
184 writer.writeRecord(values, true);
185 writer.writeRecord(new String[] { CsvConstants.COMMIT, nextBatchId });
186 writer.close();
187 load(out);
188 massageExpectectedResultsForDialect(expectedValues);
189 assertTestTableEquals(values[0], expectedValues);
190 }
191
192 @Test
193 public void testTableNotExisting() throws Exception {
194 String tableName = "UnknownTable";
195 String[] keys = { "id" };
196 String[] columns = { "id", "name" };
197 String[] badValues = { "1", "testTableNotExisting" };
198 String[] values = { getNextId(), "testTableNotExisting", "This row should load", "char", "char not null",
199 "2007-01-02 00:00:00.0", "2007-02-03 04:05:06.0", "0", "0", "12.10", "-0.0747" };
200
201 ByteArrayOutputStream out = new ByteArrayOutputStream();
202 CsvWriter writer = getWriter(out);
203 writer.writeRecord(new String[] { CsvConstants.NODEID, TestConstants.TEST_CLIENT_EXTERNAL_ID });
204 writeTable(writer, tableName, keys, columns);
205 String nextBatchId = getNextBatchId();
206 writer.writeRecord(new String[] { CsvConstants.BATCH, nextBatchId });
207 writer.write(CsvConstants.INSERT);
208 writer.writeRecord(badValues, true);
209 writeTable(writer, TEST_TABLE, TEST_KEYS, TEST_COLUMNS);
210 writer.write(CsvConstants.INSERT);
211 writer.writeRecord(values, true);
212 writer.writeRecord(new String[] { CsvConstants.COMMIT, nextBatchId });
213 writer.close();
214 load(out);
215 massageExpectectedResultsForDialect(values);
216 assertTestTableEquals(values[0], values);
217 }
218
219 @Test
220 public void testLargeColumn() throws Exception {
221 String tableName = "UnknownTable";
222 String[] keys = { "id" };
223 String[] columns = { "id", "name" };
224 String[] values = { "1", new RandomDataImpl().nextSecureHexString(110000) };
225
226 ByteArrayOutputStream out = new ByteArrayOutputStream();
227 CsvWriter writer = getWriter(out);
228 writer.writeRecord(new String[] { CsvConstants.NODEID, TestConstants.TEST_CLIENT_EXTERNAL_ID });
229 writeTable(writer, tableName, keys, columns);
230 String nextBatchId = getNextBatchId();
231 writer.writeRecord(new String[] { CsvConstants.BATCH, nextBatchId });
232 writer.write(CsvConstants.INSERT);
233 writer.writeRecord(values, true);
234 writer.close();
235 load(out);
236 }
237
238 private void massageExpectectedResultsForDialect(String[] values) {
239 if (values[5] != null && (!(getDbDialect() instanceof OracleDbDialect || getDbDialect() instanceof MsSqlDbDialect))) {
240 values[5] = values[5].replaceFirst(" //d//d://d//d://d//d//.?0?", " 00:00:00.0");
241 }
242 if (values[10] != null) {
243 values[10] = values[10].replace(',', '.');
244 }
245 if (values[10] != null && !(getDbDialect() instanceof OracleDbDialect)) {
246 int scale = 17;
247 if (getDbDialect() instanceof MySqlDbDialect || getDbDialect() instanceof PostgreSqlDbDialect) {
248 scale = 16;
249 }
250 DecimalFormat df = new DecimalFormat("0.00####################################");
251 values[10] = df.format(new BigDecimal(values[10]).setScale(scale, RoundingMode.DOWN));
252 }
253 }
254
255 @Test
256 public void testColumnLevelSync() throws Exception {
257 String[] insertValues = new String[TEST_COLUMNS.length];
258 insertValues[2] = insertValues[4] = "column sync";
259 insertValues[0] = getNextId();
260 String[] updateValues = new String[3];
261 updateValues[0] = updateValues[2] = insertValues[0];
262 updateValues[1] = "new value";
263
264 ByteArrayOutputStream out = new ByteArrayOutputStream();
265 CsvWriter writer = getWriter(out);
266 writer.writeRecord(new String[] { CsvConstants.NODEID, TestConstants.TEST_CLIENT_EXTERNAL_ID });
267 String nextBatchId = getNextBatchId();
268 writer.writeRecord(new String[] { CsvConstants.BATCH, nextBatchId });
269 writeTable(writer, TEST_TABLE, TEST_KEYS, TEST_COLUMNS);
270
271
272 writer.write(CsvConstants.INSERT);
273 writer.writeRecord(insertValues, true);
274
275
276 String[] columns = { "id", "string_value" };
277 writeTable(writer, TEST_TABLE, TEST_KEYS, columns);
278 writer.write(CsvConstants.UPDATE);
279 writer.writeRecord(updateValues, true);
280
281
282 columns = new String[] { "id", "char_value" };
283 writeTable(writer, TEST_TABLE, TEST_KEYS, columns);
284 writer.write(CsvConstants.UPDATE);
285 writer.writeRecord(updateValues, true);
286
287 writer.close();
288 load(out);
289
290 insertValues[1] = updateValues[1];
291 insertValues[3] = updateValues[1];
292 assertTestTableEquals(insertValues[0], insertValues);
293 }
294
295 @Test
296 public void testBenchmark() throws Exception {
297 ZipInputStream in = new ZipInputStream(getClass().getResourceAsStream("/test-data-loader-benchmark.zip"));
298 in.getNextEntry();
299 long startTime = System.currentTimeMillis();
300 IDataLoader dataLoader = getDataLoader();
301 dataLoader.open(TransportUtils.toReader(in));
302 while (dataLoader.hasNext()) {
303 dataLoader.load();
304 }
305 dataLoader.close();
306 double totalSeconds = (System.currentTimeMillis() - startTime) / 1000.0;
307
308 Assert.assertTrue("DataLoader running in " + totalSeconds + " is too slow", totalSeconds <= 12.0);
309 }
310
311 protected void load(ByteArrayOutputStream out) throws Exception {
312 ByteArrayInputStream in = new ByteArrayInputStream(out.toByteArray());
313 IDataLoader dataLoader = getDataLoader();
314 dataLoader.open(TransportUtils.toReader(in));
315 while (dataLoader.hasNext()) {
316 dataLoader.load();
317 }
318 dataLoader.close();
319 }
320
321 protected IDataLoader getDataLoader() {
322 return (IDataLoader) find(Constants.DATALOADER);
323 }
324
325 }