1 /*------------------------------------------------------------------------------
2 Name: TestResultSetToXmlConverter.java
3 Project: org.xmlBlasterProject: xmlBlaster.org
4 Copyright: xmlBlaster.org, see xmlBlaster-LICENSE file
5 ------------------------------------------------------------------------------*/
6 package org.xmlBlaster.test.contrib.dbwatcher;
7
8 import java.io.IOException;
9 import java.io.InputStream;
10 import java.util.prefs.Preferences;
11
12
13 import junit.framework.TestCase;
14 import org.custommonkey.xmlunit.XMLTestCase;
15 import org.xmlBlaster.contrib.I_Info;
16 import org.xmlBlaster.contrib.I_Update;
17 import org.xmlBlaster.contrib.db.DbPool;
18 import org.xmlBlaster.contrib.db.I_DbPool;
19 import org.xmlBlaster.contrib.dbwatcher.DbWatcher;
20 import org.xmlBlaster.contrib.dbwatcher.Info;
21 import org.xmlBlaster.contrib.dbwatcher.detector.I_ChangeDetector;
22 import org.xmlBlaster.contrib.dbwatcher.mom.XmlBlasterPublisher;
23 import org.xmlBlaster.test.contrib.TestUtils;
24
25 import java.util.logging.Logger;
26
27 import java.util.Map;
28 import java.util.HashMap;
29
30 /**
31 * Test basic functionality.
32 * <p>
33 * To run most of the tests you need to have a databse (for example Oracle)
34 * and XmlBlaster up and running.
35 * </p>
36 * <p>
37 * The connection configuration (url, password etc.) is configured
38 * as JVM property or in {@link #createTest(I_Info, Map)} and
39 * {@link #setUpDbPool(I_Info)}
40 * </p>
41 *
42 * @see DbWatcher
43 * @author Marcel Ruff
44 */
45 public class TestResultSetToXmlConverter extends XMLTestCase {
46 private static Logger log = Logger.getLogger(TestResultSetToXmlConverter.class.getName());
47 private Preferences prefs;
48 private I_Info info;
49 private I_DbPool dbPool;
50 private Map updateMap = new HashMap(); // collects received update messages
51 private DbWatcher processor;
52
53 /**
54 * Start the test.
55 * <pre>
56 * java -Ddb.password=secret junit.swingui.TestRunner -noloading org.xmlBlaster.test.contrib.dbwatcher.TestResultSetToXmlConverter
57 * </pre>
58 * @param args Command line settings
59 */
60 public static void main(String[] args) {
61 junit.swingui.TestRunner.run(TestResultSetToXmlConverter.class);
62 }
63
64 /**
65 * Default ctor.
66 */
67 public TestResultSetToXmlConverter() {
68 }
69
70 /**
71 * Constructor for TestResultSetToXmlConverter.
72 * @param arg0
73 */
74 public TestResultSetToXmlConverter(String arg0) {
75 super(arg0);
76 }
77
78 /**
79 * Configure database access.
80 * @see TestCase#setUp()
81 */
82 protected void setUp() throws Exception {
83 super.setUp();
84 this.prefs = Preferences.userRoot();
85 this.prefs.clear();
86 this.info = new Info(this.prefs);
87
88 this.dbPool = setUpDbPool(info);
89 try {
90 this.dbPool.update("DROP TABLE TEST_POLL");
91 } catch(Exception e) {
92 log.warning(e.toString());
93 }
94
95 this.processor = null;
96 }
97
98 /**
99 * Creates a database pooling instance and puts it to info.
100 * @param info The configuration
101 * @return The created pool
102 */
103 public static DbPool setUpDbPool(I_Info info) {
104 String driverClass = System.getProperty("jdbc.drivers", "org.hsqldb.jdbcDriver:oracle.jdbc.driver.OracleDriver:com.microsoft.jdbc.sqlserver.SQLServerDriver:org.postgresql.Driver");
105 ////System.setProperty("jdbc.drivers", driverClass);
106
107 /*
108 String dbUrl = System.getProperty("db.url", "jdbc:oracle:thin:@localhost:1521:orcl");
109 String dbUser = System.getProperty("db.user", "system");
110 String dbPassword = System.getProperty("db.password", "");
111 */
112
113 String dbUrl = System.getProperty("db.url", "jdbc:oracle:thin:@desktop:1521:test");
114 String dbUser = System.getProperty("db.user", "system");
115 String dbPassword = System.getProperty("db.password", "frifra20");
116
117 //String fs = System.getProperty("file.separator");
118 //String dbUrl = "jdbc:hsqldb:"+System.getProperty("user.home")+fs+"tmp"+fs+"testpoll";
119 //String dbUser = "sa";
120 //String dbPassword = "";
121
122 info.put("jdbc.drivers", driverClass);
123 info.put("db.url", dbUrl);
124 info.put("db.user", dbUser);
125 info.put("db.password", dbPassword);
126
127 DbPool dbPool = new DbPool();
128 dbPool.init(info);
129 info.putObject("db.pool", dbPool);
130
131 return dbPool;
132 }
133
134 /**
135 * Creates a DbWatcher instance and listens on MoM messages.
136 * @param info Configuration
137 * @param updateMap The map for received messages
138 * @return A new DbWatcher
139 * @throws Exception
140 */
141 public static DbWatcher createTest(I_Info info, final Map updateMap) throws Exception {
142 /*
143 // Configure the MoM
144 this.prefs.put("mom.connectQos",
145 "<qos>" +
146 " <securityService type='htpasswd' version='1.0'>" +
147 " <![CDATA[" +
148 " <user>michele</user>" +
149 " <passwd>secret</passwd>" +
150 " ]]>" +
151 " </securityService>" +
152 " <session name='joe/3'/>'" +
153 " <address type='SOCKET'>" +
154 " socket://192.168.110.10:7607" +
155 " </address>" +
156 " </qos>");
157 System.setProperty("protocol", "SOCKET");
158 System.setProperty("protocol/socket/hostname", "192.168.110.10");
159 */
160
161 DbWatcher pc = new DbWatcher(info);
162 XmlBlasterPublisher mom = (XmlBlasterPublisher)pc.getMom();
163 mom.subscribe("XPATH://key", new I_Update() {
164 public void update(String topic, java.io.InputStream is, Map attrMap) {
165 log.info("Received '" + topic + "' from MoM");
166 try {
167 writeToFile(topic, new String(TestUtils.getContent(is)));
168 }
169 catch (Exception e) {
170 // Ignore
171 }
172 updateMap.put(topic, is);
173 }
174 });
175
176 try { Thread.sleep(1000); } catch(Exception e) { /* Ignore */ }
177 updateMap.clear(); // Ignore any existing topics
178
179 pc.startAlertProducers();
180
181 return pc;
182 }
183
184 /*
185 * @see TestCase#tearDown()
186 */
187 protected void tearDown() throws Exception {
188 super.tearDown();
189
190 if (this.processor != null) {
191 this.processor.shutdown();
192 this.processor = null;
193 }
194
195 if (this.dbPool != null) {
196 try {
197 this.dbPool.update("DROP TABLE TEST_POLL");
198 } catch(Exception e) {
199 log.warning(e.toString());
200 }
201 this.dbPool.shutdown();
202 }
203 }
204
205 /**
206 * If the table does not exist we expect a null ResultSet
207 * @throws Exception Any type is possible
208 */
209 public final void testTableStates() throws Exception {
210 log.info("Start testTableStates()");
211
212 this.prefs.put("converter.rootName", "myRootTag");
213 this.prefs.put("db.queryMeatStatement", "");
214 this.prefs.put("alertScheduler.pollInterval", "0"); // switch off
215 this.prefs.put("changeDetector.groupColName", ""); // !!! Tests without grouping
216 this.prefs.put("converter.addMeta", ""+true);
217 this.prefs.put("changeDetector.detectStatement", "SELECT colKey, col1, col2, ICAO_ID FROM TEST_POLL");
218 this.prefs.put("mom.topicName", "db.change.event.TEST_POLL");
219
220 this.processor = createTest(new Info(prefs), this.updateMap);
221 I_ChangeDetector changeDetector = processor.getChangeDetector();
222
223 for (int i=0; i<2; i++) {
224 log.info("Testing no table ...");
225 changeDetector.checkAgain(null);
226 sleep(500);
227 assertEquals("Number of message is wrong", 0, this.updateMap.size());
228 }
229
230 {
231 log.info("Now testing an empty table ...");
232 this.dbPool.update("CREATE TABLE TEST_POLL (colKey NUMBER(10,3), col1 VARCHAR(20), col2 NUMBER(12), ICAO_ID VARCHAR(10))");
233 changeDetector.checkAgain(null);
234 sleep(500);
235 assertEquals("Number of message is wrong", 1, this.updateMap.size());
236 String xml = (String)this.updateMap.get("db.change.event.TEST_POLL");
237 assertNotNull("No db.change.event.${groupColValue} message has arrived", xml);
238 assertXpathNotExists("/myRootTag/row[@num='0']", xml);
239 assertXpathEvaluatesTo("CREATE", "/myRootTag/desc/command/text()", xml);
240 this.updateMap.clear();
241
242 writeToFile("db.change.event.CREATE", xml);
243
244 changeDetector.checkAgain(null);
245 sleep(500);
246 assertEquals("Number of message is wrong", 0, this.updateMap.size());
247 }
248
249 {
250 log.info("Insert one row");
251 this.dbPool.update("INSERT INTO TEST_POLL VALUES ('1.1', '<Bla', '9000', 'EDDI')");
252 changeDetector.checkAgain(null);
253 sleep(500);
254 assertEquals("Number of message is wrong", 1, this.updateMap.size());
255 String xml = (String)this.updateMap.get("db.change.event.TEST_POLL");
256 assertNotNull("xml returned is null", xml);
257 // TODO: We deliver a "UPDATE" because of the CREATE md5: Is it easy possible to detect the INSERT?
258 assertXpathEvaluatesTo("UPDATE", "/myRootTag/desc/command/text()", xml);
259 assertXpathEvaluatesTo("<Bla", "/myRootTag/row[@num='0']/col[@name='COL1']/text()", xml);
260 this.updateMap.clear();
261
262 writeToFile("db.change.event.INSERT", xml);
263
264 changeDetector.checkAgain(null);
265 sleep(500);
266 assertEquals("Number of message is wrong", 0, this.updateMap.size());
267 }
268
269 {
270 log.info("Update one row");
271 this.dbPool.update("UPDATE TEST_POLL SET col1='BXXX' WHERE ICAO_ID='EDDI'");
272 changeDetector.checkAgain(null);
273 sleep(500);
274 assertEquals("Number of message is wrong", 1, this.updateMap.size());
275 String xml = (String)this.updateMap.get("db.change.event.TEST_POLL");
276 assertXpathEvaluatesTo("UPDATE", "/myRootTag/desc/command/text()", xml);
277 assertXpathEvaluatesTo("BXXX", "/myRootTag/row[@num='0']/col[@name='COL1']/text()", xml);
278 this.updateMap.clear();
279
280 writeToFile("db.change.event.UPDATE", xml);
281
282 changeDetector.checkAgain(null);
283 sleep(500);
284 assertEquals("Number of message is wrong", 0, this.updateMap.size());
285 }
286
287 {
288 log.info("Delete one row");
289 this.dbPool.update("DELETE FROM TEST_POLL WHERE ICAO_ID='EDDI'");
290 changeDetector.checkAgain(null);
291 sleep(500);
292 assertEquals("Number of message is wrong", 1, this.updateMap.size());
293 String xml = (String)this.updateMap.get("db.change.event.TEST_POLL");
294 // TODO: We deliver "UPDATE" instead of DELETE:
295 assertXpathEvaluatesTo("UPDATE", "/myRootTag/desc/command/text()", xml);
296 assertXpathNotExists("/myRootTag/row[@num='0']", xml);
297 this.updateMap.clear();
298
299 writeToFile("db.change.event.DELETE", xml);
300
301 changeDetector.checkAgain(null);
302 sleep(500);
303 assertEquals("Number of message is wrong", 0, this.updateMap.size());
304 }
305
306 {
307 log.info("Drop a table");
308 this.dbPool.update("DROP TABLE TEST_POLL");
309 changeDetector.checkAgain(null);
310 sleep(500);
311 assertEquals("Number of message is wrong", 1, this.updateMap.size());
312 String xml = (String)this.updateMap.get("db.change.event.TEST_POLL");
313 assertXpathEvaluatesTo("DROP", "/myRootTag/desc/command/text()", xml);
314 assertXpathNotExists("/myRootTag/row[@num='0']", xml);
315 this.updateMap.clear();
316
317 writeToFile("db.change.event.DROP", xml);
318
319 changeDetector.checkAgain(null);
320 sleep(500);
321 assertEquals("Number of message is wrong", 0, this.updateMap.size());
322 }
323
324 log.info("SUCCESS");
325 }
326
327 /**
328 * @throws Exception Any type is possible
329 */
330 public final void testNULLcol() throws Exception {
331 log.info("Start testNULLcol()");
332
333 this.prefs.put("converter.rootName", "myRootTag");
334 this.prefs.put("db.queryMeatStatement", "");
335 this.prefs.put("alertScheduler.pollInterval", "0"); // switch off
336 this.prefs.put("changeDetector.groupColName", ""); // !!! Tests without grouping
337 this.prefs.put("converter.addMeta", ""+true);
338 this.prefs.put("changeDetector.detectStatement", "SELECT colKey, col1, col2, ICAO_ID FROM TEST_POLL");
339 this.prefs.put("mom.topicName", "db.change.event.TEST_POLL");
340
341 this.dbPool.update("CREATE TABLE TEST_POLL (colKey NUMBER(10,3), col1 VARCHAR(20), col2 NUMBER(12), ICAO_ID VARCHAR(10))");
342 //this.dbPool.update("INSERT INTO TEST_POLL (colKey, col1, col2) VALUES ('2.0', 'XXX', '2000')");
343 this.dbPool.update("INSERT INTO TEST_POLL (colKey) VALUES ('2.0')");
344
345 this.processor = createTest(new Info(prefs), this.updateMap);
346 I_ChangeDetector changeDetector = processor.getChangeDetector();
347
348 changeDetector.checkAgain(null);
349 sleep(500);
350 assertEquals("Number of message is wrong", 1, this.updateMap.size());
351 String xml = (String)this.updateMap.get("db.change.event.TEST_POLL");
352 assertXpathEvaluatesTo("UPDATE", "/myRootTag/desc/command/text()", xml);
353 assertXpathEvaluatesTo("2", "/myRootTag/row[@num='0']/col[@name='COLKEY']/text()", xml);
354 assertXpathEvaluatesTo("", "/myRootTag/row[@num='0']/col[@name='COL1']/text()", xml);
355 assertXpathEvaluatesTo("", "/myRootTag/row[@num='0']/col[@name='COL2']/text()", xml);
356 assertXpathEvaluatesTo("", "/myRootTag/row[@num='0']/col[@name='ICAO_ID']/text()", xml);
357
358 log.info("SUCCESS");
359 }
360
361 private void sleep(long millis) {
362 try { Thread.sleep(millis); } catch(Exception e) { /* Ignore */ }
363 }
364
365 /**
366 * If the table does not exist we expect a null ResultSet
367 * @throws Exception Any type is possible
368 */
369 public final void testQueryMeatTableStates() throws Exception {
370 log.info("Start testQueryMeatTableStates()");
371
372 this.prefs.put("converter.rootName", "myRootTag");
373 this.prefs.put("db.queryMeatStatement", "select 'Bla-'||COLKEY from TEST_POLL");
374 this.prefs.put("alertScheduler.pollInterval", "0"); // switch off
375 this.prefs.put("changeDetector.groupColName", ""); // !!! Tests without grouping
376 this.prefs.put("converter.addMeta", ""+true);
377 this.prefs.put("changeDetector.detectStatement", "SELECT colKey, col1, col2, ICAO_ID FROM TEST_POLL");
378 this.prefs.put("mom.topicName", "db.change.event.TEST_POLL");
379
380 this.processor = createTest(new Info(prefs), this.updateMap);
381 I_ChangeDetector changeDetector = processor.getChangeDetector();
382
383 for (int i=0; i<2; i++) {
384 log.info("Testing no table ...");
385 changeDetector.checkAgain(null);
386 sleep(500);
387 assertEquals("Number of message is wrong", 0, this.updateMap.size());
388 }
389
390 {
391 log.info("Now testing an empty table ...");
392 this.dbPool.update("CREATE TABLE TEST_POLL (colKey NUMBER(10,3), col1 VARCHAR(20), col2 NUMBER(12), ICAO_ID VARCHAR(10))");
393 changeDetector.checkAgain(null);
394 sleep(500);
395 assertEquals("Number of message is wrong", 1, this.updateMap.size());
396 String xml = (String)this.updateMap.get("db.change.event.TEST_POLL");
397 assertNotNull("No db.change.event.${groupColValue} message has arrived", xml);
398 assertXpathNotExists("/myRootTag/row[@num='0']", xml);
399 assertXpathEvaluatesTo("CREATE", "/myRootTag/desc/command/text()", xml);
400 this.updateMap.clear();
401
402 changeDetector.checkAgain(null);
403 sleep(500);
404 assertEquals("Number of message is wrong", 0, this.updateMap.size());
405 }
406
407 {
408 log.info("Insert one row");
409 this.dbPool.update("INSERT INTO TEST_POLL VALUES ('1.1', '<Bla', '9000', 'EDDI')");
410 changeDetector.checkAgain(null);
411 sleep(500);
412 assertEquals("Number of message is wrong", 1, this.updateMap.size());
413 String xml = (String)this.updateMap.get("db.change.event.TEST_POLL");
414 assertNotNull("xml returned is null", xml);
415 assertXpathEvaluatesTo("UPDATE", "/myRootTag/desc/command/text()", xml);
416 //assertXpathEvaluatesTo("Bla-1,1", "/myRootTag/row[@num='0']/col[@name='BLA-||COLKEY']/text()", xml);
417 assertTrue(xml.indexOf("Bla-1.1") != -1);
418 this.updateMap.clear();
419
420 writeToFile("db.change.event.INSERT", xml);
421
422 changeDetector.checkAgain(null);
423 sleep(500);
424 assertEquals("Number of message is wrong", 0, this.updateMap.size());
425 }
426
427 {
428 log.info("Update one row");
429 this.dbPool.update("UPDATE TEST_POLL SET colKey='4.44' WHERE ICAO_ID='EDDI'");
430 changeDetector.checkAgain(null);
431 sleep(500);
432 assertEquals("Number of message is wrong", 1, this.updateMap.size());
433 String xml = (String)this.updateMap.get("db.change.event.TEST_POLL");
434 assertXpathEvaluatesTo("UPDATE", "/myRootTag/desc/command/text()", xml);
435 //assertXpathEvaluatesTo("Bla-4.44", "/myRootTag/row[@num='0']/col[@name='BLA-||COLKEY']/text()", xml);
436 assertTrue(xml.indexOf("Bla-4.44") != -1);
437 this.updateMap.clear();
438
439 writeToFile("db.change.event.UPDATE", xml);
440
441 changeDetector.checkAgain(null);
442 sleep(500);
443 assertEquals("Number of message is wrong", 0, this.updateMap.size());
444 }
445
446 {
447 log.info("Delete one row");
448 this.dbPool.update("DELETE FROM TEST_POLL WHERE ICAO_ID='EDDI'");
449 changeDetector.checkAgain(null);
450 sleep(500);
451 assertEquals("Number of message is wrong", 1, this.updateMap.size());
452 String xml = (String)this.updateMap.get("db.change.event.TEST_POLL");
453 assertXpathEvaluatesTo("UPDATE", "/myRootTag/desc/command/text()", xml);
454 assertXpathNotExists("/myRootTag/row[@num='0']", xml);
455 this.updateMap.clear();
456
457 writeToFile("db.change.event.DELETE", xml);
458
459 changeDetector.checkAgain(null);
460 sleep(500);
461 assertEquals("Number of message is wrong", 0, this.updateMap.size());
462 }
463
464 {
465 log.info("Drop a table");
466 this.dbPool.update("DROP TABLE TEST_POLL");
467 changeDetector.checkAgain(null);
468 sleep(500);
469 assertEquals("Number of message is wrong", 1, this.updateMap.size());
470 String xml = (String)this.updateMap.get("db.change.event.TEST_POLL");
471 assertXpathEvaluatesTo("DROP", "/myRootTag/desc/command/text()", xml);
472 assertXpathNotExists("/myRootTag/row[@num='0']", xml);
473 this.updateMap.clear();
474
475 writeToFile("db.change.event.DROP", xml);
476
477 changeDetector.checkAgain(null);
478 sleep(500);
479 assertEquals("Number of message is wrong", 0, this.updateMap.size());
480 }
481
482 log.info("SUCCESS");
483 }
484
485 /**
486 * Test synchronous all possible table changes.
487 * We drive two test, one with meat and one as content less event messages.
488 * @throws Exception Any type is possible
489 */
490 public final void testGroupedQueryMeatTableStates() throws Exception {
491 log.info("Start testGroupedQueryMeatTableStates()");
492
493 this.prefs.put("converter.rootName", "myRootTag");
494 this.prefs.put("db.queryMeatStatement", "select ICAO_ID, 'Bla-'||COLKEY from TEST_POLL where ICAO_ID='${groupColValue}'");
495 this.prefs.put("alertScheduler.pollInterval", "0"); // switch off
496 this.prefs.put("changeDetector.groupColName", "ICAO_ID");
497 this.prefs.put("converter.addMeta", ""+true);
498 this.prefs.put("changeDetector.detectStatement", "SELECT colKey, col1, col2, ICAO_ID FROM TEST_POLL ORDER BY ICAO_ID");
499 this.prefs.put("mom.topicName", "db.change.event.${groupColValue}");
500
501 boolean hasConverter = false;
502 for (int run=0; run<2; run++) {
503 if (run == 0) {
504 this.prefs.put("converter.class", "");
505 }
506 else {
507 if (this.processor != null) this.processor.shutdown();
508 this.prefs.put("converter.class", "org.xmlBlaster.contrib.dbwatcher.convert.ResultSetToXmlConverter");
509 hasConverter = true;
510 }
511 this.processor = createTest(new Info(prefs), this.updateMap);
512 I_ChangeDetector changeDetector = processor.getChangeDetector();
513
514 for (int i=0; i<2; i++) {
515 log.info("Testing no table ...");
516 changeDetector.checkAgain(null);
517 sleep(500);
518 assertEquals("Number of message is wrong", 0, this.updateMap.size());
519 }
520
521 {
522 log.info("Now testing an empty table ...");
523 this.dbPool.update("CREATE TABLE TEST_POLL (colKey NUMBER(10,3), col1 VARCHAR(20), col2 NUMBER(12), ICAO_ID VARCHAR(10))");
524 changeDetector.checkAgain(null);
525 sleep(500);
526 assertEquals("Number of message is wrong", 1, this.updateMap.size());
527 String xml = (String)this.updateMap.get("db.change.event.${groupColValue}");
528 assertNotNull("No db.change.event.${groupColValue} message has arrived", xml);
529 if (hasConverter) {
530 assertXpathNotExists("/myRootTag/row[@num='0']", xml);
531 assertXpathEvaluatesTo("CREATE", "/myRootTag/desc/command/text()", xml);
532 }
533 this.updateMap.clear();
534
535 writeToFile("db.change.event.CREATE", xml);
536
537 changeDetector.checkAgain(null);
538 sleep(500);
539 assertEquals("Number of message is wrong", 0, this.updateMap.size());
540 }
541
542 {
543 log.info("Insert one row");
544 this.dbPool.update("INSERT INTO TEST_POLL VALUES ('1.1', '<Bla', '9000', 'EDDI')");
545 changeDetector.checkAgain(null);
546 sleep(500);
547 assertEquals("Number of message is wrong", 1, this.updateMap.size());
548 String xml = (String)this.updateMap.get("db.change.event.EDDI");
549 assertNotNull("No db.change.event.EDDI message has arrived", xml);
550 if (hasConverter) {
551 assertXpathEvaluatesTo("INSERT", "/myRootTag/desc/command/text()", xml);
552 assertTrue(xml.indexOf("Bla-1.1") != -1);
553 }
554 this.updateMap.clear();
555
556 writeToFile("db.change.event.INSERT", xml);
557
558 changeDetector.checkAgain(null);
559 sleep(500);
560 assertEquals("Number of message is wrong", 0, this.updateMap.size());
561 }
562
563 {
564 log.info("Update one row");
565 this.dbPool.update("UPDATE TEST_POLL SET col1='BXXX' WHERE ICAO_ID='EDDI'");
566 changeDetector.checkAga