1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 package nl.b3p;
18
19 import static org.junit.jupiter.api.Assertions.assertAll;
20 import static org.junit.jupiter.api.Assertions.assertEquals;
21 import static org.junit.jupiter.api.Assertions.assertNotNull;
22 import static org.junit.jupiter.api.Assumptions.assumeTrue;
23 import static org.junit.jupiter.params.provider.Arguments.arguments;
24
25 import java.io.File;
26 import java.io.FileInputStream;
27 import java.util.concurrent.locks.Lock;
28 import java.util.concurrent.locks.ReentrantLock;
29 import java.util.stream.Stream;
30 import nl.b3p.brmo.loader.BrmoFramework;
31 import nl.b3p.brmo.loader.entity.Bericht;
32 import nl.b3p.brmo.test.util.database.dbunit.CleanUtil;
33 import nl.b3p.jdbc.util.converter.OracleConnectionUnwrapper;
34 import org.apache.commons.dbcp2.BasicDataSource;
35 import org.apache.commons.logging.Log;
36 import org.apache.commons.logging.LogFactory;
37 import org.dbunit.database.DatabaseConfig;
38 import org.dbunit.database.DatabaseConnection;
39 import org.dbunit.database.DatabaseDataSourceConnection;
40 import org.dbunit.database.IDatabaseConnection;
41 import org.dbunit.dataset.Column;
42 import org.dbunit.dataset.IDataSet;
43 import org.dbunit.dataset.ITable;
44 import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
45 import org.dbunit.dataset.xml.XmlDataSet;
46 import org.dbunit.ext.oracle.Oracle10DataTypeFactory;
47 import org.dbunit.ext.postgresql.PostgresqlDataTypeFactory;
48 import org.dbunit.operation.DatabaseOperation;
49 import org.junit.jupiter.api.AfterEach;
50 import org.junit.jupiter.api.BeforeEach;
51 import org.junit.jupiter.api.Tag;
52 import org.junit.jupiter.params.ParameterizedTest;
53 import org.junit.jupiter.params.provider.Arguments;
54 import org.junit.jupiter.params.provider.MethodSource;
55
56
57
58
59
60
61
62
63
64 @Tag("skip-windows-java11")
65 public class WozXMLToStagingIntegrationTest extends AbstractDatabaseIntegrationTest {
66
67 private static final Log LOG = LogFactory.getLog(WozXMLToStagingIntegrationTest.class);
68 private final Lock sequential = new ReentrantLock();
69 private BrmoFramework brmo;
70
71 private IDatabaseConnection staging;
72 private IDatabaseConnection rsgb;
73 private BasicDataSource dsRsgb;
74 private BasicDataSource dsStaging;
75
76 static Stream<Arguments> argumentsProvider() {
77 return Stream.of(
78
79
80
81 arguments(
82 "/woz/800000793120/204253181.xml",
83 1,
84 1,
85 new String[] {"WOZ.WOZ.800000793120"},
86 "800000793120",
87 4000,
88 8000,
89 "8106",
90 new String[0][0],
91 new String[0],
92 new String[] {"8000552570003"},
93 0),
94 arguments(
95 "/woz/800000793120/204325718.xml",
96 2,
97 1,
98 new String[] {
99 "WOZ.NPS.295f133e37f55dd610756bbb0e6eebcf0ebbc555", "WOZ.WOZ.800000200014"
100 },
101 "800000200014",
102 500,
103 8000,
104 "8106",
105 new String[][] {
106 {"WOZ.NPS.295f133e37f55dd610756bbb0e6eebcf0ebbc555", "800000200014", "E"}
107 },
108 new String[] {"800000793120"},
109 new String[] {"8000552570004"},
110 0),
111 arguments(
112 "/woz/800000200021/204405262.xml",
113 2,
114 1,
115 new String[] {"WOZ.NNP.428228574", "WOZ.WOZ.800000200021"},
116 "800000200021",
117 200,
118 8000,
119 "8106",
120 new String[][] {{"WOZ.NNP.428228574", "800000200021", "E"}},
121 new String[0],
122 new String[] {"8000552570005"},
123 0),
124 arguments(
125 "/woz/object_met_geom.xml",
126 1,
127 1,
128 new String[] {"WOZ.WOZ.800000003123"},
129 "800000003123",
130 450,
131 8000,
132 "0372",
133 new String[][] {
134 {"WOZ.NPS.e19242199d42fea43af7201c13ec4ad980f1e2cb", "800000003123", "E"}
135 },
136 new String[0],
137 new String[] {"8000010170000"},
138 0),
139 arguments(
140 "/woz/BRMO-204_lege_kad_identif/086600003277.anon.xml",
141 1,
142 1,
143 new String[] {"WOZ.WOZ.086600003277"},
144 "86600003277",
145 218,
146 866,
147 "0106",
148 new String[][] {
149 {"WOZ.NPS.42ee4ccf42af8c36500c43a5d105dfbf38175c31", "86600003277", "G"},
150 {"WOZ.NPS.b9d71af53ab19882d968391b1d13497d2a5c5cf0", "86600003277", "E"},
151 {"WOZ.NPS.ccc686355b0dd382c7bedce5f98ac6e4f846be25", "86600003277", "E"}
152 },
153 new String[0],
154 new String[0],
155 0),
156
157 arguments(
158 "/woz/BRMO-184/45272376.anon.xml",
159 1,
160 1,
161 new String[] {"WOZ.WOZ.077200029855"},
162 "77200029855",
163 18,
164 772,
165 "0106",
166 new String[][] {
167 {"WOZ.NPS.1e4cfa740d8a05720aa51ac670a2561cd659a6f8", "77200029855", "E"},
168 {"WOZ.NPS.88cfb32b49e0f41c205813a681c29d702fef9f56", "77200029855", "E"},
169 {"WOZ.VES.000050302590", "77200029855", "E"}
170 },
171 new String[0],
172 new String[0],
173 0),
174 arguments(
175 "/woz/086600005516/086600005516.anon.xml",
176 2,
177 1,
178 new String[] {"WOZ.WOZ.086600005516"},
179 "86600005516",
180 null,
181 866,
182 "0106",
183 new String[][] {
184 {"WOZ.NPS.2a86e2ec6709c4ad32446a928be0335656906bd0", "86600005516", "G"}
185 },
186 new String[0],
187 new String[0],
188 0),
189 arguments(
190 "/woz/085800012189/085800012189.anon.xml",
191 2,
192 1,
193 new String[] {"WOZ.WOZ.085800012189"},
194 "85800012189",
195 null,
196 858,
197 "0106",
198 new String[0][0],
199 new String[0],
200 new String[0],
201 1),
202 arguments(
203 "/woz/085500191054/085500191054.anon.xml",
204 1,
205 1,
206 new String[] {"WOZ.WOZ.085500191054"},
207 "85500191054",
208 null,
209 855,
210 null,
211 new String[0][0],
212 new String[0],
213 new String[0],
214 2));
215 }
216
217 @BeforeEach
218 @Override
219 public void setUp() throws Exception {
220 dsStaging = new BasicDataSource();
221 dsStaging.setUrl(params.getProperty("staging.jdbc.url"));
222 dsStaging.setUsername(params.getProperty("staging.user"));
223 dsStaging.setPassword(params.getProperty("staging.passwd"));
224 dsStaging.setAccessToUnderlyingConnectionAllowed(true);
225
226 dsRsgb = new BasicDataSource();
227 dsRsgb.setUrl(params.getProperty("rsgb.jdbc.url"));
228 dsRsgb.setUsername(params.getProperty("rsgb.user"));
229 dsRsgb.setPassword(params.getProperty("rsgb.passwd"));
230 dsRsgb.setAccessToUnderlyingConnectionAllowed(true);
231
232 staging = new DatabaseDataSourceConnection(dsStaging);
233 rsgb = new DatabaseDataSourceConnection(dsRsgb, params.getProperty("rsgb.schema"));
234
235 if (this.isOracle) {
236 staging =
237 new DatabaseConnection(
238 OracleConnectionUnwrapper.unwrap(dsStaging.getConnection()),
239 params.getProperty("staging.user").toUpperCase());
240 staging
241 .getConfig()
242 .setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new Oracle10DataTypeFactory());
243 staging.getConfig().setProperty(DatabaseConfig.FEATURE_SKIP_ORACLE_RECYCLEBIN_TABLES, true);
244
245 rsgb =
246 new DatabaseConnection(
247 OracleConnectionUnwrapper.unwrap(dsRsgb.getConnection()),
248 params.getProperty("rsgb.user").toUpperCase());
249 rsgb.getConfig()
250 .setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new Oracle10DataTypeFactory());
251 rsgb.getConfig().setProperty(DatabaseConfig.FEATURE_SKIP_ORACLE_RECYCLEBIN_TABLES, true);
252 } else if (this.isPostgis) {
253
254 staging
255 .getConfig()
256 .setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new PostgresqlDataTypeFactory());
257 rsgb.getConfig()
258 .setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new PostgresqlDataTypeFactory());
259 }
260
261 brmo = new BrmoFramework(dsStaging, dsRsgb, null);
262
263 FlatXmlDataSetBuilder fxdb = new FlatXmlDataSetBuilder();
264 fxdb.setCaseSensitiveTableNames(false);
265 IDataSet stagingDataSet =
266 fxdb.build(
267 new FileInputStream(
268 new File(
269 WozXMLToStagingIntegrationTest.class
270 .getResource("/staging-empty-flat.xml")
271 .toURI())));
272
273 sequential.lock();
274
275
276
277 DatabaseOperation.CLEAN_INSERT.execute(staging, stagingDataSet);
278 assumeTrue(
279 0L == brmo.getCountBerichten(BrmoFramework.BR_WOZ, "STAGING_OK"),
280 "Er zijn STAGING_OK berichten");
281 assumeTrue(
282 0L == brmo.getCountLaadProcessen(BrmoFramework.BR_WOZ, "STAGING_OK"),
283 "Er zijn STAGING_OK laadprocessen");
284 }
285
286 @AfterEach
287 public void cleanup() throws Exception {
288 brmo.closeBrmoFramework();
289 CleanUtil.cleanSTAGING(staging, false);
290 CleanUtil.cleanRSGB_WOZ(rsgb, true);
291 staging.close();
292 dsStaging.close();
293 rsgb.close();
294 dsRsgb.close();
295 sequential.unlock();
296 }
297
298 @ParameterizedTest(name = "testWozBerichtToStagingToRsgb #{index}: bestand: {0}")
299 @MethodSource("argumentsProvider")
300 public void testWozBerichtToStagingToRsgb(
301 String bestandNaam,
302 long aantalBerichten,
303 long aantalProcessen,
304 String[] objectRefs,
305 String objNummer,
306 Number grondoppervlakte,
307 Number gemCode,
308 String wsCode,
309 String[][] wozBelang,
310 String[] deelObjectNums,
311 String[] wozOmvatKadIdentif,
312 long aantalBrondocumenten)
313 throws Exception {
314
315 if (objNummer.equalsIgnoreCase("800000003123")) {
316 IDataSet rsgbDataSet =
317 new XmlDataSet(
318 new FileInputStream(
319 WozXMLToStagingIntegrationTest.class
320 .getResource("/woz/subject-setup.xml")
321 .getFile()));
322 DatabaseOperation.INSERT.execute(rsgb, rsgbDataSet);
323 }
324
325 assumeTrue(
326 WozXMLToStagingIntegrationTest.class.getResource(bestandNaam) != null,
327 "Het bestand met test bericht zou moeten bestaan.");
328 brmo.loadFromFile(
329 BrmoFramework.BR_WOZ,
330 WozXMLToStagingIntegrationTest.class.getResource(bestandNaam).getFile(),
331 null);
332
333 assertEquals(
334 aantalBerichten,
335 brmo.getCountBerichten(BrmoFramework.BR_WOZ, "STAGING_OK"),
336 "Verwacht aantal STAGING_OK berichten");
337 assertEquals(
338 aantalProcessen,
339 brmo.getCountLaadProcessen(BrmoFramework.BR_WOZ, "STAGING_OK"),
340 "Verwacht aantal laadprocessen");
341
342 ITable bericht = staging.createDataSet().getTable("bericht");
343 int rowNum = 0;
344 for (String objectRef : objectRefs) {
345 assertEquals(objectRef, bericht.getValue(rowNum, "object_ref"), "'object_ref' klopt niet");
346 rowNum++;
347 }
348
349 LOG.debug("Transformeren berichten naar rsgb DB.");
350 brmo.setOrderBerichten(true);
351 Thread t = brmo.toRsgb();
352 t.join();
353
354 assertEquals(
355 aantalBerichten,
356 brmo.getCountBerichten(BrmoFramework.BR_WOZ, "RSGB_OK"),
357 "Niet alle berichten zijn OK getransformeerd");
358
359 for (Bericht b : brmo.getBerichten(0, 0, 10, null, null, BrmoFramework.BR_WOZ, "RSGB_OK")) {
360 assertNotNull(b, "Bericht is 'null'");
361 assertNotNull(b.getDbXml(), "'db-xml' van bericht is 'null'");
362 }
363
364 ITable woz_obj = rsgb.createDataSet().getTable("woz_obj");
365 assertAll(
366 "woz_obj",
367 () -> assertEquals(1, woz_obj.getRowCount(), "Er is 1 WOZ object verwacht"),
368 () ->
369 assertEquals(
370 objNummer, woz_obj.getValue(0, "nummer").toString(), "WOZ 'obj_nummer' klopt niet"),
371 () ->
372 assertEquals(
373 grondoppervlakte,
374 null == woz_obj.getValue(0, "grondoppervlakte")
375 ? null
376 : ((Number) woz_obj.getValue(0, "grondoppervlakte")).intValue(),
377 "Oppervlakte van object klopt niet"),
378 () ->
379 assertEquals(
380 wsCode, woz_obj.getValue(0, "waterschap"), "Waterschap van object klopt niet"),
381 () ->
382 assertEquals(
383 gemCode,
384 ((Number) woz_obj.getValue(0, "fk_verantw_gem_code")).intValue(),
385 "Gemeentecode van object klopt niet"));
386
387 if (objNummer.equalsIgnoreCase("800000003123") | objNummer.equalsIgnoreCase("77200029855")) {
388 assertNotNull(woz_obj.getValue(0, "geom"), "geometrie verwacht voor dit object");
389 }
390
391 ITable woz_deelobj = rsgb.createDataSet().getTable("woz_deelobj");
392 assertEquals(
393 deelObjectNums.length,
394 woz_deelobj.getRowCount(),
395 "Het aantal 'woz_deelobj' records klopt niet");
396 for (int i = 0; i < deelObjectNums.length; i++) {
397 assertEquals(
398 deelObjectNums[i],
399 woz_deelobj.getValue(i, "nummer").toString(),
400 "WOZ deelobject nummer is niet correct");
401 assertEquals(
402 objNummer,
403 woz_deelobj.getValue(i, "fk_6woz_nummer").toString(),
404 "WOZ object nummer is niet correct");
405 }
406
407 ITable woz_belang = rsgb.createDataSet().getTable("woz_belang");
408 assertEquals(
409 wozBelang.length, woz_belang.getRowCount(), "Het aantal 'woz_belang' records klopt niet");
410 if (wozBelang.length > 0) {
411 final Column[] woz_belang_cols = woz_belang.getTableMetaData().getColumns();
412 for (int row = 0; row < wozBelang.length; row++) {
413 for (int i = 0; i < woz_belang_cols.length; i++) {
414 int col = i;
415 assertEquals(
416 wozBelang[row][i],
417 woz_belang.getValue(row, woz_belang_cols[i].getColumnName()).toString(),
418 () -> "woz belang " + woz_belang_cols[col].getColumnName() + " is niet correct");
419 }
420 }
421 }
422
423 ITable woz_omvat = rsgb.createDataSet().getTable("woz_omvat");
424 assertEquals(
425 wozOmvatKadIdentif.length,
426 woz_omvat.getRowCount(),
427 "Het aantal 'woz_omvat' records klopt niet");
428 for (int i = 0; i < wozOmvatKadIdentif.length; i++) {
429 assertEquals(
430 wozOmvatKadIdentif[i],
431 woz_omvat.getValue(i, "fk_sc_lh_kad_identif").toString(),
432 "kad-identif nummer is niet correct");
433 }
434
435 ITable brondocument = rsgb.createDataSet().getTable("brondocument");
436 assertEquals(
437 aantalBrondocumenten,
438 brondocument.getRowCount(),
439 "Het aantal 'brondocument' records klopt niet");
440
441 ITable woz_waarde = rsgb.createDataSet().getTable("woz_waarde");
442 }
443 }