Mercurial > hg > monetdb-java

comparison src/main/java/org/monetdb/util/SQLExporter.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/SQLExporter.java@31e79dd9e658
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.DatabaseMetaData;
12 import java.sql.ResultSet;
13 import java.sql.ResultSetMetaData;
14 import java.sql.SQLException;
15 import java.sql.Types;
16 import java.util.Iterator;
17 import java.util.LinkedHashSet;
18 import java.util.Map;
19 import java.util.Set;
20 import java.util.Stack;
21
22 public final class SQLExporter extends Exporter {
23 private int outputMode;
24 private Stack<String> lastSchema;
25
26 public final static short TYPE_OUTPUT = 1;
27 public final static short VALUE_INSERT = 0;
28 public final static short VALUE_COPY = 1;
29 public final static short VALUE_TABLE = 2;
30
31 public SQLExporter(final java.io.PrintWriter out) {
32 super(out);
33 }
34
35 /**
36 * A helper method to generate SQL CREATE code for a given table.
37 * This method performs all required lookups to find all relations and
38 * column information, as well as additional indices.
39 *
40 * @param dbmd a DatabaseMetaData object to query on (not null)
41 * @param type the type of the object, e.g. VIEW, TABLE (not null)
42 * @param schema the schema the object is in (not null)
43 * @param name the table to describe in SQL CREATE format (not null)
44 * @throws SQLException if a database related error occurs
45 */
46 public void dumpSchema(
47 final DatabaseMetaData dbmd,
48 final String type,
49 final String schema,
50 final String name)
51 throws SQLException
52 {
53 assert dbmd != null;
54 assert type != null;
55 assert schema != null;
56 assert name != null;
57
58 final String fqname = dq(schema) + "." + dq(name);
59
60 if (useSchema)
61 changeSchema(schema);
62
63 // handle views directly
64 if (type.endsWith("VIEW")) { // for types: VIEW and SYSTEM VIEW
65 final String viewDDL = fetchSysTablesQueryValue(dbmd.getConnection(), schema, name);
66 if (viewDDL != null)
67 out.println(viewDDL);
68 else
69 out.println("-- unknown " + type + " " + fqname + ": no SQL view definition found!");
70 return;
71 }
72
73 out.println("CREATE " + type + " " + fqname + " (");
74
75 // add all columns with their type, nullability and default definition
76 ResultSet cols = dbmd.getColumns(null, schema, name, null);
77 int colNmIndex = cols.findColumn("COLUMN_NAME");
78 final int colTypeNmIndex = cols.findColumn("TYPE_NAME");
79 final int datatypeIndex = cols.findColumn("DATA_TYPE");
80 final int sizeIndex = cols.findColumn("COLUMN_SIZE");
81 final int digitsIndex = cols.findColumn("DECIMAL_DIGITS");
82 final int isNotNullIndex = cols.findColumn("NULLABLE");
83 final int defaultValueIndex = cols.findColumn("COLUMN_DEF");
84 final ResultSetMetaData rsmd = cols.getMetaData();
85 final int colwidth = rsmd.getColumnDisplaySize(colNmIndex);
86 int typewidth = rsmd.getColumnDisplaySize(colTypeNmIndex);
87 if (typewidth < 13)
88 typewidth = 13; // use minimal 13 characters for the typename (same as used in mclient)
89
90 final StringBuilder sb = new StringBuilder(128);
91 int i;
92 for (i = 0; cols.next(); i++) {
93 if (i > 0)
94 out.println(",");
95
96 // print column name (with double quotes)
97 String s = dq(cols.getString(colNmIndex));
98 out.print("\t" + s + repeat(' ', (colwidth - s.length() + 3)));
99
100 int digits = cols.getInt(digitsIndex);
101 s = cols.getString(colTypeNmIndex).toUpperCase(); // ANSI SQL uses uppercase data type names
102 // do some data type substitutions to match SQL standard
103 if (s.equals("INT")) {
104 s = "INTEGER";
105 } else if (s.equals("SEC_INTERVAL")) {
106 s = "INTERVAL SECOND";
107 } else if (s.equals("MONTH_INTERVAL")) {
108 s = "INTERVAL MONTH";
109 } else if (s.equals("TIMETZ")) {
110 s = "TIME";
111 // small hack to get desired behaviour: set digits when we have
112 // a time with time zone and at the same time masking the internal types
113 digits = 1;
114 } else if (s.equals("TIMESTAMPTZ")) {
115 s = "TIMESTAMP";
116 // small hack to get desired behaviour: set digits when we have
117 // a timestamp with time zone and at the same time masking the internal types
118 digits = 1;
119 }
120 sb.append(s); // add the data type for this column
121
122 int ctype = cols.getInt(datatypeIndex);
123 int size = cols.getInt(sizeIndex);
124 // do some SQL/MonetDB type length/precision and scale specifics
125 switch (ctype) {
126 case Types.CHAR:
127 case Types.VARCHAR:
128 case Types.LONGVARCHAR:
129 case Types.CLOB:
130 case Types.BLOB:
131 case Types.FLOAT:
132 if (size > 0)
133 sb.append('(').append(size).append(')');
134 break;
135 case Types.TIME:
136 case Types.TIMESTAMP:
137 if (size > 1)
138 sb.append('(').append(size -1).append(')');
139 if (digits == 1) // flag is set to include suffix: WITH TIME ZONE
140 sb.append(" WITH TIME ZONE");
141 break;
142 case Types.DECIMAL:
143 case Types.NUMERIC:
144 sb.append('(').append(size);
145 if (digits != 0)
146 sb.append(',').append(digits);
147 sb.append(')');
148 break;
149 }
150
151 boolean isNotNull = cols.getInt(isNotNullIndex) == DatabaseMetaData.columnNoNulls;
152 String defaultValue = cols.getString(defaultValueIndex);
153 boolean hasDefault = (defaultValue != null && !defaultValue.isEmpty());
154 if (isNotNull || hasDefault) {
155 final int spaces = typewidth - sb.length();
156 if (spaces > 0)
157 sb.append(repeat(' ', spaces));
158 if (isNotNull)
159 sb.append(" NOT NULL");
160 if (hasDefault)
161 sb.append(" DEFAULT ").append(defaultValue);
162 }
163
164 // print column data type, optional length and scale, optional NOT NULL, optional DEFAULT value
165 out.print(sb.toString());
166
167 sb.setLength(0); // clear the buffer for next column
168 }
169 cols.close();
170
171 // add the primary key constraint definition
172 // unfortunately some idiot defined that getPrimaryKeys()
173 // returns the primary key columns sorted by column name, not
174 // key sequence order. So we have to sort ourself :(
175 cols = dbmd.getPrimaryKeys(null, schema, name);
176 int colKeySeq = cols.findColumn("KEY_SEQ");
177 // first make a 'index' of the KEY_SEQ columns
178 final java.util.SortedMap<Integer, Integer> seqIndex = new java.util.TreeMap<Integer, Integer>();
179 for (i = 1; cols.next(); i++) {
180 seqIndex.put(Integer.valueOf(cols.getInt(colKeySeq)), Integer.valueOf(i));
181 }
182 if (seqIndex.size() > 0) {
183 cols.absolute(1); // reset to first pk column row
184 // terminate the previous line
185 out.println(",");
186 out.print("\tCONSTRAINT " + dq(cols.getString("PK_NAME")) + " PRIMARY KEY (");
187
188 colNmIndex = cols.findColumn("COLUMN_NAME");
189 final Iterator<Map.Entry<Integer, Integer>> it = seqIndex.entrySet().iterator();
190 for (i = 0; it.hasNext(); i++) {
191 final Map.Entry<Integer, Integer> e = it.next();
192 cols.absolute(e.getValue().intValue());
193 if (i > 0)
194 out.print(", ");
195 out.print(dq(cols.getString(colNmIndex)));
196 }
197 out.print(")");
198 }
199 cols.close();
200
201 // add unique constraint definitions
202 // we use getIndexInfo to get unique indexes, but need to exclude
203 // the indexes which are generated by the system for pkey constraints
204 cols = dbmd.getIndexInfo(null, schema, name, true, true);
205 int colIndexNm = cols.findColumn("INDEX_NAME");
206 colNmIndex = cols.findColumn("COLUMN_NAME");
207 while (cols.next()) {
208 final String idxname = cols.getString(colIndexNm);
209 if (idxname != null && !idxname.endsWith("_pkey")) {
210 out.println(",");
211 out.print("\tCONSTRAINT " + dq(idxname) + " UNIQUE (" + dq(cols.getString(colNmIndex)));
212
213 boolean next;
214 while ((next = cols.next()) && idxname.equals(cols.getString(colIndexNm))) {
215 out.print(", " + dq(cols.getString(colNmIndex)));
216 }
217 // go back one, we've gone one too far
218 if (next)
219 cols.previous();
220
221 out.print(")");
222 }
223 }
224 cols.close();
225
226 // add foreign keys definitions
227 cols = dbmd.getImportedKeys(null, schema, name);
228 final int colFkNm = cols.findColumn("FK_NAME");
229 final int colFkColNm = cols.findColumn("FKCOLUMN_NAME");
230 final int colPkColNm = cols.findColumn("PKCOLUMN_NAME");
231 colKeySeq = cols.findColumn("KEY_SEQ");
232 final int colPkTblSch = cols.findColumn("PKTABLE_SCHEM");
233 final int colPkTblNm = cols.findColumn("PKTABLE_NAME");
234 while (cols.next()) {
235 out.println(",");
236 out.print("\tCONSTRAINT " + dq(cols.getString(colFkNm)) + " FOREIGN KEY (");
237
238 final Set<String> fk = new LinkedHashSet<String>();
239 fk.add(cols.getString(colFkColNm).intern());
240 final Set<String> pk = new LinkedHashSet<String>();
241 pk.add(cols.getString(colPkColNm).intern());
242
243 boolean next;
244 while ((next = cols.next()) && cols.getInt(colKeySeq) != 1) {
245 fk.add(cols.getString(colFkColNm).intern());
246 pk.add(cols.getString(colPkColNm ).intern());
247 }
248 // go back one
249 if (next)
250 cols.previous();
251
252 Iterator<String> it = fk.iterator();
253 for (i = 0; it.hasNext(); i++) {
254 if (i > 0)
255 out.print(", ");
256 out.print(dq(it.next()));
257 }
258 out.print(") REFERENCES " + dq(cols.getString(colPkTblSch)) + "." + dq(cols.getString(colPkTblNm)) + " (");
259 it = pk.iterator();
260 for (i = 0; it.hasNext(); i++) {
261 if (i > 0)
262 out.print(", ");
263 out.print(dq(it.next()));
264 }
265 out.print(")");
266 }
267 cols.close();
268
269 out.println();
270 // end the create table statement
271 if (type.equals("REMOTE TABLE")) {
272 final String on_clause = fetchSysTablesQueryValue(dbmd.getConnection(), schema, name);
273 out.println(") ON '" + ((on_clause != null) ? on_clause : "!!missing mapi:monetdb:// spec") + "';");
274 } else
275 out.println(");");
276
277 // create the non unique indexes defined for this table
278 // we use getIndexInfo to get non-unique indexes, but need to exclude
279 // the indexes which are generated by the system for fkey constraints
280 // (and pkey and unique constraints but those are marked as unique and not requested)
281 cols = dbmd.getIndexInfo(null, schema, name, false, true);
282 colIndexNm = cols.findColumn("INDEX_NAME");
283 colNmIndex = cols.findColumn("COLUMN_NAME");
284 final int tblNmIndex = cols.findColumn("TABLE_NAME");
285 final int tblSchIndex = cols.findColumn("TABLE_SCHEM");
286 final int nonUniqIndex = cols.findColumn("NON_UNIQUE");
287 while (cols.next()) {
288 if (cols.getBoolean(nonUniqIndex)) {
289 // We only process non-unique indexes here.
290 // The unique indexes are already covered as UNIQUE constraints in the CREATE TABLE above
291 final String idxname = cols.getString(colIndexNm);
292 if (idxname != null && !idxname.endsWith("_fkey")) {
293 out.print("CREATE INDEX " + dq(idxname) + " ON " +
294 dq(cols.getString(tblSchIndex)) + "." +
295 dq(cols.getString(tblNmIndex)) + " (" +
296 dq(cols.getString(colNmIndex)));
297
298 boolean next;
299 while ((next = cols.next()) && idxname.equals(cols.getString(colIndexNm))) {
300 out.print(", " + dq(cols.getString(colNmIndex)));
301 }
302 // go back one
303 if (next)
304 cols.previous();
305
306 out.println(");");
307 }
308 }
309 }
310 cols.close();
311 }
312
313 /**
314 * Dumps the given ResultSet as specified in the form variable.
315 *
316 * @param rs the ResultSet to dump
317 * @throws SQLException if a database error occurs
318 */
319 public void dumpResultSet(final ResultSet rs) throws SQLException {
320 switch (outputMode) {
321 case VALUE_INSERT:
322 resultSetToSQL(rs);
323 break;
324 case VALUE_COPY:
325 resultSetToSQLDump(rs);
326 break;
327 case VALUE_TABLE:
328 resultSetToTable(rs);
329 break;
330 }
331 }
332
333 public void setProperty(final int type, final int value) throws Exception {
334 switch (type) {
335 case TYPE_OUTPUT:
336 switch (value) {
337 case VALUE_INSERT:
338 case VALUE_COPY:
339 case VALUE_TABLE:
340 outputMode = value;
341 break;
342 default:
343 throw new Exception("Illegal value " + value + " for TYPE_OUTPUT");
344 }
345 break;
346 default:
347 throw new Exception("Illegal type " + type);
348 }
349 }
350
351 public int getProperty(final int type) throws Exception {
352 switch (type) {
353 case TYPE_OUTPUT:
354 return outputMode;
355 default:
356 throw new Exception("Illegal type " + type);
357 }
358 }
359
360 private static final short AS_IS = 0;
361 private static final short QUOTE = 1;
362
363 /**
364 * Helper method to dump the contents of a table in SQL INSERT INTO
365 * format.
366 *
367 * @param rs the ResultSet to convert into INSERT INTO statements
368 * @param absolute if true, dumps table name prepended with schema name
369 * @throws SQLException if a database related error occurs
370 */
371 private void resultSetToSQL(final ResultSet rs)
372 throws SQLException
373 {
374 final ResultSetMetaData rsmd = rs.getMetaData();
375 final int cols = rsmd.getColumnCount();
376 // get for each output column whether it requires quotes around the value based on data type
377 final short[] types = new short[cols +1];
378 for (int i = 1; i <= cols; i++) {
379 switch (rsmd.getColumnType(i)) {
380 case Types.CHAR:
381 case Types.VARCHAR:
382 case Types.LONGVARCHAR:
383 case Types.CLOB:
384 case Types.BLOB:
385 case Types.DATE:
386 case Types.TIME:
387 case Types.TIMESTAMP:
388 types[i] = QUOTE;
389 break;
390 case Types.NUMERIC:
391 case Types.DECIMAL:
392 case Types.BIT: // we don't use type BIT, it's here for completeness
393 case Types.BOOLEAN:
394 case Types.TINYINT:
395 case Types.SMALLINT:
396 case Types.INTEGER:
397 case Types.BIGINT:
398 case Types.REAL:
399 case Types.FLOAT:
400 case Types.DOUBLE:
401 types[i] = AS_IS;
402 break;
403 default:
404 // treat all other types (such as inet,url,json,objects) as complex types requiring quotes
405 types[i] = QUOTE;
406 }
407 }
408
409 final StringBuilder strbuf = new StringBuilder(1024);
410 strbuf.append("INSERT INTO ");
411 final String schema = rsmd.getSchemaName(1);
412 if (schema != null && !schema.isEmpty())
413 strbuf.append(dq(schema)).append(".");
414 strbuf.append(dq(rsmd.getTableName(1))).append(" VALUES (");
415 final int cmdpart = strbuf.length();
416
417 while (rs.next()) {
418 for (int i = 1; i <= cols; i++) {
419 final String val = rs.getString(i);
420 if (i > 1)
421 strbuf.append(", ");
422 if (val == null || rs.wasNull()) {
423 strbuf.append("NULL");
424 } else {
425 strbuf.append((types[i] == QUOTE) ? q(val) : val);
426 }
427 }
428 strbuf.append(");");
429 out.println(strbuf.toString());
430 // clear the variable part of the buffer contents for next data row
431 strbuf.setLength(cmdpart);
432 }
433 }
434
435 public void resultSetToSQLDump(final ResultSet rs) {
436 // TODO: write copy into statement
437 }
438
439 /**
440 * Helper method to write a ResultSet in a convenient table format
441 * to the output writer.
442 *
443 * @param rs the ResultSet to write out
444 * @throws SQLException if a database related error occurs
445 */
446 public void resultSetToTable(final ResultSet rs) throws SQLException {
447 final ResultSetMetaData md = rs.getMetaData();
448 final int cols = md.getColumnCount();
449 // find the optimal display widths of the columns
450 final int[] width = new int[cols + 1];
451 final boolean[] isSigned = new boolean[cols + 1]; // used for controlling left or right alignment of data
452 for (int j = 1; j < width.length; j++) {
453 final int coldisplaysize = md.getColumnDisplaySize(j);
454 final int collabellength = md.getColumnLabel(j).length();
455 final int maxwidth = (coldisplaysize > collabellength) ? coldisplaysize : collabellength;
456 // the minimum width should be 4 to represent: "NULL"
457 width[j] = (maxwidth > 4) ? maxwidth : 4;
458 isSigned[j] = md.isSigned(j);
459 }
460
461 // use a buffer to construct the text lines
462 final StringBuilder strbuf = new StringBuilder(1024);
463
464 // construct the frame lines and header text
465 strbuf.append('+');
466 for (int j = 1; j < width.length; j++)
467 strbuf.append(repeat('-', width[j] + 1)).append("-+");
468
469 final String outsideLine = strbuf.toString();
470
471 strbuf.setLength(0); // clear the buffer
472 strbuf.append('|');
473 for (int j = 1; j < width.length; j++) {
474 final String colLabel = md.getColumnLabel(j);
475 strbuf.append(' ').append(colLabel);
476 strbuf.append(repeat(' ', width[j] - colLabel.length()));
477 strbuf.append(" |");
478 }
479 // print the header text
480 out.println(outsideLine);
481 out.println(strbuf.toString());
482 out.println(outsideLine.replace('-', '='));
483
484 // print formatted data of each row from resultset
485 long count = 0;
486 for (; rs.next(); count++) {
487 strbuf.setLength(0); // clear the buffer
488 strbuf.append('|');
489 for (int j = 1; j < width.length; j++) {
490 String data = rs.getString(j);
491 if (data == null || rs.wasNull()) {
492 data = "NULL";
493 }
494
495 int filler_length = width[j] - data.length();
496 if (filler_length <= 0) {
497 if (filler_length == 0) {
498 strbuf.append(' ');
499 }
500 strbuf.append(data);
501 } else {
502 strbuf.append(' ');
503 if (isSigned[j]) {
504 // we have a numeric type here, right align
505 strbuf.append(repeat(' ', filler_length));
506 strbuf.append(data);
507 } else {
508 // all other left align
509 strbuf.append(data);
510 strbuf.append(repeat(' ', filler_length));
511 }
512 }
513 strbuf.append(" |");
514 }
515 out.println(strbuf.toString());
516 }
517
518 // print the footer text
519 out.println(outsideLine);
520 out.print(count);
521 out.println((count != 1) ? " rows" : " row");
522 }
523
524 private void changeSchema(final String schema) {
525 if (lastSchema == null) {
526 lastSchema = new Stack<String>();
527 lastSchema.push(null);
528 }
529
530 if (!schema.equals(lastSchema.peek())) {
531 if (!lastSchema.contains(schema)) {
532 // do not generate CREATE SCHEMA cmds for existing system schemas
533 if (!schema.equals("sys")
534 && !schema.equals("tmp")
535 && !schema.equals("json")
536 && !schema.equals("profiler")
537 && !schema.equals("wlc") // added in Nov2019
538 && !schema.equals("wlr") // added in Nov2019
539 && !schema.equals("logging") // added in Jun2020
540 && !schema.equals("bam")) {
541 // create schema
542 out.print("CREATE SCHEMA ");
543 out.print(dq(schema));
544 out.println(";\n");
545 }
546 lastSchema.push(schema);
547 }
548
549 out.print("SET SCHEMA ");
550 out.print(dq(schema));
551 out.println(";\n");
552 }
553 }
554 }