Mercurial > hg > monetdb-java

comparison src/main/java/org/monetdb/util/XMLExporter.java @ 391:f523727db392

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Moved Java classes from packages starting with nl.cwi.monetdb.* to package org.monetdb.* This naming complies to the Java Package Naming convention as MonetDB's main website is www.monetdb.org.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 12 Nov 2020 22:02:01 +0100 (2020-11-12)
parents src/main/java/nl/cwi/monetdb/util/XMLExporter.java@54137aeb1f92
children bf9f6b6ecf40
comparison
equal deleted inserted replaced
390:6199e0be3c6e 391:f523727db392
1 /*
2 * This Source Code Form is subject to the terms of the Mozilla Public
3 * License, v. 2.0. If a copy of the MPL was not distributed with this
4 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
5 *
6 * Copyright 1997 - July 2008 CWI, August 2008 - 2020 MonetDB B.V.
7 */
8
9 package org.monetdb.util;
10
11 import java.sql.ResultSet;
12 import java.sql.SQLException;
13 import java.sql.Timestamp;
14 import java.sql.Types;
15 import java.text.SimpleDateFormat;
16
17 public final class XMLExporter extends Exporter {
18 private boolean useNil;
19
20 public static final short TYPE_NIL = 1;
21 public static final short VALUE_OMIT = 0;
22 public static final short VALUE_XSI = 1;
23
24 public XMLExporter(final java.io.PrintWriter out) {
25 super(out);
26 }
27
28 /**
29 * A helper method to generate XML xsd schema code for a given table.
30 * This method performs all required lookups to find all relations and
31 * column information.
32 *
33 * @param dbmd a DatabaseMetaData object to query on (not null)
34 * @param type the type of the object, e.g. VIEW, TABLE (not null)
35 * @param schema the schema the object is in (not null)
36 * @param name the table to describe (not null)
37 * @throws SQLException if a database related error occurs
38 */
39 public void dumpSchema(
40 final java.sql.DatabaseMetaData dbmd,
41 final String type,
42 final String schema,
43 final String name)
44 throws SQLException
45 {
46 // handle views directly
47 if (type.endsWith("VIEW")) { // for types: VIEW and SYSTEM VIEW
48 final String viewDDL = fetchSysTablesQueryValue(dbmd.getConnection(), schema, name);
49 if (viewDDL != null)
50 out.println("<!-- " + viewDDL + " -->");
51 else
52 out.println("<!-- unknown " + type + " " + dq(schema) + "." + dq(name) + ": no SQL view definition found! -->");
53 return;
54 }
55
56 out.println("<xsd:schema>");
57
58 final ResultSet cols = dbmd.getColumns(null, schema, name, null);
59 final int colNmIndex = cols.findColumn("COLUMN_NAME");
60 final int colTypeNmIndex = cols.findColumn("TYPE_NAME");
61 final int datatypeIndex = cols.findColumn("DATA_TYPE");
62 final int sizeIndex = cols.findColumn("COLUMN_SIZE");
63 final int digitsIndex = cols.findColumn("DECIMAL_DIGITS");
64
65 String ident;
66 final java.util.HashSet<String> types = new java.util.HashSet<String>();
67 // walk through the ResultSet and create the types
68 // for a bit of a clue on the types, see this url:
69 // http://books.xmlschemata.org/relaxng/relax-CHP-19.html
70 while (cols.next()) {
71 switch (cols.getInt(datatypeIndex)) {
72 case Types.CHAR:
73 ident = "CHAR_" + cols.getString(sizeIndex);
74 if (types.contains(ident))
75 break;
76 types.add(ident);
77
78 out.print(" <xsd:simpleType name=");
79 out.print(dq(ident));
80 out.println(">");
81 out.println(" <xsd:restriction base=\"xsd:string\">");
82 out.print(" <xsd:length value=");
83 out.print(dq(cols.getString(sizeIndex)));
84 out.println(" />");
85 out.println(" </xsd:restriction>");
86 out.println(" </xsd:simpleType>");
87 break;
88 case Types.VARCHAR:
89 case Types.LONGVARCHAR:
90 ident = "VARCHAR_" + cols.getString(sizeIndex);
91 if (types.contains(ident))
92 break;
93 types.add(ident);
94
95 out.print(" <xsd:simpleType name=");
96 out.print(dq(ident));
97 out.println(">");
98 out.println(" <xsd:restriction base=\"xsd:string\">");
99 out.print(" <xsd:maxLength value=");
100 out.print(dq(cols.getString(sizeIndex)));
101 out.println(" />");
102 out.println(" </xsd:restriction>");
103 out.println(" </xsd:simpleType>");
104 break;
105 case Types.CLOB:
106 ident = "CLOB";
107 if (types.contains(ident))
108 break;
109 types.add(ident);
110
111 out.print(" <xsd:simpleType name=");
112 out.print(dq(ident));
113 out.println(">");
114 out.println(" <xsd:restriction base=\"xsd:string\" />");
115 out.println(" </xsd:simpleType>");
116 break;
117 case Types.DECIMAL:
118 case Types.NUMERIC:
119 ident = "DECIMAL_" + cols.getString(sizeIndex) + "_" + cols.getString(digitsIndex);
120 if (types.contains(ident))
121 break;
122 types.add(ident);
123
124 out.print(" <xsd:simpleType name=");
125 out.print(dq(ident));
126 out.println(">");
127 out.println(" <xsd:restriction base=\"xsd:decimal\">");
128 out.print(" <xsd:totalDigits value=");
129 out.print(dq(cols.getString(sizeIndex)));
130 out.println(" />");
131 out.print(" <xsd:fractionDigits value=");
132 out.print(dq(cols.getString(digitsIndex)));
133 out.println(" />");
134 out.println(" </xsd:restriction>");
135 out.println(" </xsd:simpleType>");
136 break;
137 case Types.TINYINT:
138 ident = "TINYINT";
139 if (types.contains(ident))
140 break;
141 types.add(ident);
142
143 out.print(" <xsd:simpleType name=");
144 out.print(dq(ident));
145 out.println(">");
146 out.println(" <xsd:restriction base=\"xsd:byte\" />");
147 out.println(" </xsd:simpleType>");
148 break;
149 case Types.SMALLINT:
150 ident = "SMALLINT";
151 if (types.contains(ident))
152 break;
153 types.add(ident);
154
155 out.print(" <xsd:simpleType name=");
156 out.print(dq(ident));
157 out.println(">");
158 out.println(" <xsd:restriction base=\"xsd:short\" />");
159 out.println(" </xsd:simpleType>");
160 break;
161 case Types.INTEGER:
162 ident = "INTEGER";
163 if (types.contains(ident))
164 break;
165 types.add(ident);
166
167 out.print(" <xsd:simpleType name=");
168 out.print(dq(ident));
169 out.println(">");
170 out.println(" <xsd:restriction base=\"xsd:integer\" />");
171 out.println(" </xsd:simpleType>");
172 break;
173 case Types.BIGINT:
174 ident = "BIGINT";
175 if (types.contains(ident))
176 break;
177 types.add(ident);
178
179 out.print(" <xsd:simpleType name=");
180 out.print(dq(ident));
181 out.println(">");
182 out.println(" <xsd:restriction base=\"xsd:long\" />");
183 out.println(" </xsd:simpleType>");
184 break;
185 case Types.BIT:
186 ident = "BIT";
187 if (types.contains(ident))
188 break;
189 types.add(ident);
190
191 out.print(" <xsd:simpleType name=");
192 out.print(dq(ident));
193 out.println(">");
194 out.println(" <xsd:restriction base=\"xsd:bit\" />");
195 out.println(" </xsd:simpleType>");
196 break;
197 case Types.BOOLEAN:
198 ident = "BOOLEAN";
199 if (types.contains(ident))
200 break;
201 types.add(ident);
202
203 out.print(" <xsd:simpleType name=");
204 out.print(dq(ident));
205 out.println(">");
206 out.println(" <xsd:restriction base=\"xsd:boolean\" />");
207 out.println(" </xsd:simpleType>");
208 break;
209 case Types.DATE:
210 ident = "DATE";
211 if (types.contains(ident))
212 break;
213 types.add(ident);
214
215 out.print(" <xsd:simpleType name=");
216 out.print(dq(ident));
217 out.println(">");
218 out.println(" <xsd:restriction base=\"xsd:date\" />");
219 out.println(" </xsd:simpleType>");
220 break;
221 case Types.TIME:
222 if ("timetz".equals(cols.getString(colTypeNmIndex))) {
223 ident = "TIME_WTZ";
224 } else {
225 ident = "TIME";
226 }
227 if (types.contains(ident))
228 break;
229 types.add(ident);
230
231 out.print(" <xsd:simpleType name=");
232 out.print(dq(ident));
233 out.println(">");
234 out.println(" <xsd:restriction base=\"xsd:time\" />");
235 out.println(" </xsd:simpleType>");
236 break;
237 case Types.TIMESTAMP:
238 if ("timestamptz".equals(cols.getString(colTypeNmIndex))) {
239 ident = "TIMESTAMP_WTZ";
240 } else {
241 ident = "TIMESTAMP";
242 }
243 if (types.contains(ident))
244 break;
245 types.add(ident);
246
247 out.print(" <xsd:simpleType name=");
248 out.print(dq(ident));
249 out.println(">");
250 out.println(" <xsd:restriction base=\"xsd:dateTime\" />");
251 out.println(" </xsd:simpleType>");
252 break;
253 }
254 }
255
256 // rewind the ResultSet
257 cols.beforeFirst();
258
259 // create the RowType
260 final String tablenm = schema.replaceAll("\\.", "_x002e_") + "." + name.replaceAll("\\.", "_x002e_");
261 out.print(" <xsd:complexType name=");
262 out.print(dq("RowType." + tablenm));
263 out.println(">");
264 out.println(" <xsd:sequence>");
265 while (cols.next()) {
266 out.print(" <xsd:element name=");
267 out.print(dq(cols.getString(colNmIndex)));
268 out.print(" type=");
269 switch (cols.getInt(datatypeIndex)) {
270 case Types.CHAR:
271 ident = "CHAR_" + cols.getString(sizeIndex);
272 break;
273 case Types.VARCHAR:
274 case Types.LONGVARCHAR:
275 ident = "VARCHAR_" + cols.getString(sizeIndex);
276 break;
277 case Types.CLOB:
278 ident = "CLOB";
279 break;
280 case Types.DECIMAL:
281 case Types.NUMERIC:
282 ident = "DECIMAL_" + cols.getString(sizeIndex) +
283 "_" + cols.getString(digitsIndex);
284 break;
285 case Types.TINYINT:
286 ident = "TINYINT";
287 break;
288 case Types.SMALLINT:
289 ident = "SMALLINT";
290 break;
291 case Types.INTEGER:
292 ident = "INTEGER";
293 break;
294 case Types.BIGINT:
295 ident = "BIGINT";
296 break;
297 case Types.BIT:
298 ident = "BIT";
299 break;
300 case Types.BOOLEAN:
301 ident = "BOOLEAN";
302 break;
303 case Types.DATE:
304 ident = "DATE";
305 break;
306 case Types.TIME:
307 if ("timetz".equals(cols.getString(colTypeNmIndex))) {
308 ident = "TIME_WTZ";
309 } else {
310 ident = "TIME";
311 }
312 break;
313 case Types.TIMESTAMP:
314 if ("timestamptz".equals(cols.getString(colTypeNmIndex))) {
315 ident = "TIMESTAMP_WTZ";
316 } else {
317 ident = "TIMESTAMP";
318 }
319 break;
320 default:
321 ident = "(unknown)";
322 break;
323 }
324 out.print(dq(ident));
325 out.println(" />");
326 }
327 out.println(" </xsd:sequence>");
328 out.println(" </xsd:complexType>");
329
330 out.print(" <xsd:complexType name=");
331 out.print(dq("TableType." + tablenm));
332 out.println(">");
333 out.println(" <xsd:sequence>");
334 out.print(" <xsd:element name=\"row\" type=");
335 out.print(dq("RowType." + tablenm));
336 out.println(" minOccurs=\"0\" maxOccurs=\"unbounded\" />");
337 out.println(" </xsd:sequence>");
338 out.println(" </xsd:complexType>");
339
340 out.println("</xsd:schema>");
341 }
342
343 private static SimpleDateFormat xsd_ts;
344 private static SimpleDateFormat xsd_tstz;
345
346 /**
347 * Generates an XML representation of the given ResultSet.
348 *
349 * @param rs the ResultSet
350 */
351 public void dumpResultSet(final ResultSet rs) throws SQLException {
352 // write simple XML serialisation
353 final java.sql.ResultSetMetaData rsmd = rs.getMetaData();
354 final String schema = rsmd.getSchemaName(1);
355 final String fqname = (schema != null && !schema.isEmpty() ? schema + "." : "") + rsmd.getTableName(1);
356 out.println("<" + fqname + ">");
357
358 String data;
359 while (rs.next()) {
360 out.println(" <row>");
361 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
362 switch (rsmd.getColumnType(i)) {
363 case Types.TIMESTAMP:
364 final Timestamp ts = rs.getTimestamp(i);
365 if ("timestamptz".equals(rsmd.getColumnTypeName(i))) {
366 if (xsd_tstz == null) {
367 // first time it is needed, create it
368 xsd_tstz = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssZ");
369 }
370 data = xsd_tstz.format(ts).toString();
371 } else {
372 if (xsd_ts == null) {
373 // first time it is needed, create it
374 xsd_ts = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
375 }
376 data = xsd_ts.format(ts).toString();
377 }
378 break;
379 default:
380 data = rs.getString(i);
381 break;
382 }
383 if (data == null) {
384 if (useNil) {
385 // "nil" method: write <tag xsi:nil="true" />
386 out.println(" <" + rsmd.getColumnLabel(i) + " xsi:nil=\"true\" />");
387 } else {
388 // This is the "absent" method (of completely
389 // hiding the tag if null
390 }
391 } else {
392 if (data.length() == 0) {
393 out.println(" <" + rsmd.getColumnLabel(i) + " />");
394 } else {
395 final String colLabel = rsmd.getColumnLabel(i);
396 out.println(" <" + colLabel + ">" + escapeSpecialXMLChars(data) + "</" + colLabel + ">");
397 }
398 }
399 }
400 out.println(" </row>");
401 }
402 out.println("</" + fqname + ">");
403 }
404
405 // https://en.wikipedia.org/wiki/XML#Escaping
406 private static final String escapeSpecialXMLChars(final String val) {
407 final int len = val.length();
408 final StringBuilder sb = new StringBuilder(len + 50);
409 // replace each & or < or > or ' or " by special XML escape code
410 for (int i = 0; i < len; i++) {
411 char c = val.charAt(i);
412 switch (c) {
413 case '&':
414 sb.append("&amp;");
415 break;
416 case '<':
417 sb.append("&lt;");
418 break;
419 case '>':
420 sb.append("&gt;");
421 break;
422 case '\'':
423 sb.append("&apos;");
424 break;
425 case '"':
426 sb.append("&quot;");
427 break;
428 default:
429 sb.append(c);
430 }
431 }
432 return (sb.length() > len) ? sb.toString() : val;
433 }
434
435 public void setProperty(final int type, final int value) throws Exception {
436 switch (type) {
437 case TYPE_NIL:
438 switch (value) {
439 case VALUE_OMIT:
440 useNil = false;
441 break;
442 case VALUE_XSI:
443 useNil = true;
444 break;
445 default:
446 throw new Exception("Illegal value " + value + " for TYPE_NIL");
447 }
448 break;
449 default:
450 throw new Exception("Illegal type " + type);
451 }
452 }
453
454 public int getProperty(final int type) throws Exception {
455 switch (type) {
456 case TYPE_NIL:
457 return useNil ? VALUE_XSI : VALUE_OMIT;
458 default:
459 throw new Exception("Illegal type " + type);
460 }
461 }
462 }