Mercurial > hg > monetdb-java

comparison src/main/java/nl/cwi/monetdb/util/SQLExporter.java @ 0:a5a898f6886c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Copy of MonetDB java directory changeset e6e32756ad31.
author Sjoerd Mullender <sjoerd@acm.org>
date Wed, 21 Sep 2016 09:34:48 +0200 (2016-09-21)
parents
children b07b4940c3ed
comparison
equal deleted inserted replaced
-1:000000000000 0:a5a898f6886c
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 - 2016 MonetDB B.V.
7 */
8
9 package nl.cwi.monetdb.util;
10
11 import java.io.PrintWriter;
12 import java.sql.DatabaseMetaData;
13 import java.sql.ResultSet;
14 import java.sql.ResultSetMetaData;
15 import java.sql.SQLException;
16 import java.sql.Types;
17 import java.util.Iterator;
18 import java.util.LinkedHashSet;
19 import java.util.Map;
20 import java.util.Set;
21 import java.util.SortedMap;
22 import java.util.Stack;
23 import java.util.TreeMap;
24
25 public class SQLExporter extends Exporter {
26 private int outputMode;
27 private Stack<String> lastSchema;
28
29 public final static int TYPE_OUTPUT = 1;
30 public final static int VALUE_INSERT = 0;
31 public final static int VALUE_COPY = 1;
32 public final static int VALUE_TABLE = 2;
33
34 public SQLExporter(PrintWriter out) {
35 super(out);
36 }
37
38 /**
39 * A helper method to generate SQL CREATE code for a given table.
40 * This method performs all required lookups to find all relations and
41 * column information, as well as additional indices.
42 *
43 * @param dbmd a DatabaseMetaData object to query on (not null)
44 * @param type the type of the object, e.g. VIEW, TABLE (not null)
45 * @param catalog the catalog the object is in
46 * @param schema the schema the object is in (not null)
47 * @param name the table to describe in SQL CREATE format (not null)
48 * @throws SQLException if a database related error occurs
49 */
50 public void dumpSchema(
51 DatabaseMetaData dbmd,
52 String type,
53 String catalog,
54 String schema,
55 String name)
56 throws SQLException
57 {
58 assert dbmd != null;
59 assert type != null;
60 assert schema != null;
61 assert name != null;
62
63 String fqname = (!useSchema ? dq(schema) + "." : "") + dq(name);
64
65 if (useSchema)
66 changeSchema(schema);
67
68 // handle views directly
69 if (type.indexOf("VIEW") != -1) {
70 String[] types = new String[1];
71 types[0] = type;
72 ResultSet tbl = dbmd.getTables(catalog, schema, name, types);
73 if (!tbl.next()) throw new SQLException("Whoops no meta data for view " + fqname);
74
75 // This will probably only work for MonetDB
76 String remarks = tbl.getString("REMARKS"); // for MonetDB driver this contains the view definition
77 if (remarks == null) {
78 out.println("-- invalid " + type + " " + fqname + ": no definition found");
79 } else {
80 out.print("CREATE " + type + " " + fqname + " AS ");
81 out.println(remarks.replaceFirst("create view [^ ]+ as", "").trim());
82 }
83 return;
84 }
85
86 int i;
87 String s;
88 out.println("CREATE " + type + " " + fqname + " (");
89
90 // add all columns with their type, nullability and default definition
91 ResultSet cols = dbmd.getColumns(catalog, schema, name, null);
92 ResultSetMetaData rsmd = cols.getMetaData();
93 int colwidth = rsmd.getColumnDisplaySize(cols.findColumn("COLUMN_NAME"));
94 int typewidth = rsmd.getColumnDisplaySize(cols.findColumn("TYPE_NAME"));
95 for (i = 0; cols.next(); i++) {
96 if (i > 0) out.println(",");
97 // print column name
98 s = dq(cols.getString("COLUMN_NAME"));
99 out.print("\t" + s + repeat(' ', (colwidth - s.length()) + 3));
100
101 s = cols.getString("TYPE_NAME");
102 int ctype = cols.getInt("DATA_TYPE");
103 int size = cols.getInt("COLUMN_SIZE");
104 int digits = cols.getInt("DECIMAL_DIGITS");
105 // small hack to get desired behaviour: set digits when we
106 // have a time or timestamp with time zone and at the same
107 // time masking the internal types
108 if (s.equals("timetz")) {
109 digits = 1;
110 s = "time";
111 } else if (s.equals("timestamptz")) {
112 digits = 1;
113 s = "timestamp";
114 }
115 // print column type
116 out.print(s + repeat(' ', typewidth - s.length()));
117
118 // do some MonetDB type specifics
119 switch (ctype) {
120 case Types.CHAR:
121 case Types.VARCHAR:
122 case Types.LONGVARCHAR:
123 case Types.CLOB:
124 case Types.BLOB:
125 case Types.FLOAT:
126 if (size > 0)
127 out.print("(" + size + ")");
128 break;
129 case Types.TIME:
130 case Types.TIMESTAMP:
131 if (size > 1)
132 out.print("(" + (size - 1) + ")");
133 if (digits != 0)
134 out.print(" WITH TIME ZONE");
135 break;
136 case Types.DECIMAL:
137 case Types.NUMERIC:
138 if (digits != 0)
139 out.print("(" + size + "," + digits + ")");
140 else
141 out.print("(" + size + ")");
142 break;
143 }
144 if (cols.getInt("NULLABLE") == DatabaseMetaData.columnNoNulls)
145 out.print("\tNOT NULL");
146 if ((s = cols.getString("COLUMN_DEF")) != null)
147 out.print("\tDEFAULT " + q(s));
148 }
149 cols.close();
150
151 // add the primary key constraint definition
152 // unfortunately some idiot defined that getPrimaryKeys()
153 // returns the primary key columns sorted by column name, not
154 // key sequence order. So we have to sort ourself :(
155 cols = dbmd.getPrimaryKeys(catalog, schema, name);
156 // first make an 'index' of the KEY_SEQ column
157 SortedMap<Integer, Integer> seqIndex = new TreeMap<Integer, Integer>();
158 for (i = 1; cols.next(); i++) {
159 seqIndex.put(
160 Integer.valueOf(cols.getInt("KEY_SEQ")),
161 Integer.valueOf(i));
162 }
163 if (seqIndex.size() > 0) {
164 // terminate the previous line
165 out.println(",");
166 cols.absolute(1);
167 out.print("\tCONSTRAINT " + dq(cols.getString("PK_NAME")) +
168 " PRIMARY KEY (");
169 i = 0;
170 for (Iterator<Map.Entry<Integer, Integer>> it = seqIndex.entrySet().iterator();
171 it.hasNext(); i++)
172 {
173 Map.Entry<Integer, Integer> e = it.next();
174 cols.absolute(e.getValue().intValue());
175 if (i > 0)
176 out.print(", ");
177 out.print(dq(cols.getString("COLUMN_NAME")));
178 }
179 out.print(")");
180 }
181 cols.close();
182
183 // add unique constraint definitions
184 cols = dbmd.getIndexInfo(catalog, schema, name, true, true);
185 while (cols.next()) {
186 String idxname = cols.getString("INDEX_NAME");
187 out.println(",");
188 out.print("\tCONSTRAINT " + dq(idxname) + " UNIQUE (" +
189 dq(cols.getString("COLUMN_NAME")));
190
191 boolean next;
192 while ((next = cols.next()) && idxname != null &&
193 idxname.equals(cols.getString("INDEX_NAME"))) {
194 out.print(", " + dq(cols.getString("COLUMN_NAME")));
195 }
196 // go back one, we've gone one too far
197 if (next)
198 cols.previous();
199
200 out.print(")");
201 }
202 cols.close();
203
204 // add foreign keys definitions
205 cols = dbmd.getImportedKeys(catalog, schema, name);
206 while (cols.next()) {
207 out.println(",");
208 out.print("\tCONSTRAINT " + dq(cols.getString("FK_NAME")) + " FOREIGN KEY (");
209
210 boolean next;
211 Set<String> fk = new LinkedHashSet<String>();
212 fk.add(cols.getString("FKCOLUMN_NAME").intern());
213 Set<String> pk = new LinkedHashSet<String>();
214 pk.add(cols.getString("PKCOLUMN_NAME").intern());
215
216 while ((next = cols.next()) &&
217 cols.getInt("KEY_SEQ") != 1)
218 {
219 fk.add(cols.getString("FKCOLUMN_NAME").intern());
220 pk.add(cols.getString("PKCOLUMN_NAME").intern());
221 }
222 // go back one
223 if (next) cols.previous();
224
225 Iterator<String> it = fk.iterator();
226 for (i = 0; it.hasNext(); i++) {
227 if (i > 0) out.print(", ");
228 out.print(dq(it.next()));
229 }
230 out.print(") ");
231
232 out.print("REFERENCES " + dq(cols.getString("PKTABLE_SCHEM")) +
233 "." + dq(cols.getString("PKTABLE_NAME")) + " (");
234 it = pk.iterator();
235 for (i = 0; it.hasNext(); i++) {
236 if (i > 0) out.print(", ");
237 out.print(dq(it.next()));
238 }
239 out.print(")");
240 }
241 cols.close();
242 out.println();
243 // end the create table statement
244 out.println(");");
245
246 // create the non unique indexes defined for this table
247 cols = dbmd.getIndexInfo(catalog, schema, name, false, true);
248 while (cols.next()) {
249 if (!cols.getBoolean("NON_UNIQUE")) {
250 // we already covered this one as UNIQUE constraint in the CREATE TABLE
251 continue;
252 } else {
253 String idxname = cols.getString("INDEX_NAME");
254 out.print("CREATE INDEX " + dq(idxname) + " ON " +
255 dq(cols.getString("TABLE_NAME")) + " (" +
256 dq(cols.getString("COLUMN_NAME")));
257
258 boolean next;
259 while ((next = cols.next()) && idxname != null &&
260 idxname.equals(cols.getString("INDEX_NAME")))
261 {
262 out.print(", " + dq(cols.getString("COLUMN_NAME")));
263 }
264 // go back one
265 if (next) cols.previous();
266
267 out.println(");");
268 }
269 }
270 cols.close();
271 }
272
273 /**
274 * Dumps the given ResultSet as specified in the form variable.
275 *
276 * @param rs the ResultSet to dump
277 * @throws SQLException if a database error occurs
278 */
279 public void dumpResultSet(ResultSet rs) throws SQLException {
280 switch (outputMode) {
281 case VALUE_INSERT:
282 resultSetToSQL(rs);
283 break;
284 case VALUE_COPY:
285 resultSetToSQLDump(rs);
286 break;
287 case VALUE_TABLE:
288 resultSetToTable(rs);
289 break;
290 }
291 }
292
293 public void setProperty(int type, int value) throws Exception {
294 switch (type) {
295 case TYPE_OUTPUT:
296 switch (value) {
297 case VALUE_INSERT:
298 case VALUE_COPY:
299 case VALUE_TABLE:
300 outputMode = value;
301 break;
302 default:
303 throw new Exception("Illegal value " + value + " for TYPE_OUTPUT");
304 }
305 break;
306 default:
307 throw new Exception("Illegal type " + type);
308 }
309 }
310
311 public int getProperty(int type) throws Exception {
312 switch (type) {
313 case TYPE_OUTPUT:
314 return outputMode;
315 default:
316 throw new Exception("Illegal type " + type);
317 }
318 }
319
320 private final static int AS_IS = 0;
321 private final static int QUOTE = 1;
322
323 /**
324 * Helper method to dump the contents of a table in SQL INSERT INTO
325 * format.
326 *
327 * @param rs the ResultSet to convert into INSERT INTO statements
328 * @param absolute if true, dumps table name prepended with schema name
329 * @throws SQLException if a database related error occurs
330 */
331 private void resultSetToSQL(ResultSet rs)
332 throws SQLException
333 {
334 ResultSetMetaData rsmd = rs.getMetaData();
335 String statement = "INSERT INTO ";
336 if (!useSchema) {
337 String schema = rsmd.getSchemaName(1);
338 if (schema != null && schema.length() > 0)
339 statement += dq(schema) + ".";
340 }
341 statement += dq(rsmd.getTableName(1)) + " VALUES (";
342
343 int cols = rsmd.getColumnCount();
344 short[] types = new short[cols +1];
345 for (int i = 1; i <= cols; i++) {
346 switch (rsmd.getColumnType(i)) {
347 case Types.CHAR:
348 case Types.VARCHAR:
349 case Types.LONGVARCHAR:
350 case Types.CLOB:
351 case Types.BLOB:
352 case Types.DATE:
353 case Types.TIME:
354 case Types.TIMESTAMP:
355 types[i] = QUOTE;
356 break;
357 case Types.NUMERIC:
358 case Types.DECIMAL:
359 case Types.BIT: // we don't use type BIT, it's here for completeness
360 case Types.BOOLEAN:
361 case Types.TINYINT:
362 case Types.SMALLINT:
363 case Types.INTEGER:
364 case Types.BIGINT:
365 case Types.REAL:
366 case Types.FLOAT:
367 case Types.DOUBLE:
368 types[i] = AS_IS;
369 break;
370 default:
371 types[i] = AS_IS;
372 }
373 }
374
375 StringBuilder strbuf = new StringBuilder(1024);
376 strbuf.append(statement);
377 while (rs.next()) {
378 for (int i = 1; i <= cols; i++) {
379 String val = rs.getString(i);
380 if (i > 1)
381 strbuf.append(", ");
382 if (val == null || rs.wasNull()) {
383 strbuf.append("NULL");
384 } else {
385 strbuf.append((types[i] == QUOTE) ? q(val) : val);
386 }
387 }
388 strbuf.append(");");
389 out.println(strbuf.toString());
390 // clear the variable part of the buffer contents for next data row
391 strbuf.setLength(statement.length());
392 }
393 }
394
395 public void resultSetToSQLDump(ResultSet rs) {
396 // TODO: write copy into statement
397 }
398
399 /**
400 * Helper method to write a ResultSet in a convenient table format
401 * to the output writer.
402 *
403 * @param rs the ResultSet to write out
404 */
405 public void resultSetToTable(ResultSet rs) throws SQLException {
406 ResultSetMetaData md = rs.getMetaData();
407 int cols = md.getColumnCount();
408 // find the optimal display widths of the columns
409 int[] width = new int[cols + 1];
410 boolean[] isSigned = new boolean[cols + 1]; // used for controlling left or right alignment of data
411 for (int j = 1; j < width.length; j++) {
412 int coldisplaysize = md.getColumnDisplaySize(j);
413 int collabellength = md.getColumnLabel(j).length();
414 int maxwidth = (coldisplaysize > collabellength) ? coldisplaysize : collabellength;
415 // the minimum width should be 4 to represent: "NULL"
416 width[j] = (maxwidth > 4) ? maxwidth : 4;
417 isSigned[j] = md.isSigned(j);
418 }
419
420 // use a buffer to construct the text lines
421 StringBuilder strbuf = new StringBuilder(1024);
422
423 // construct the frame lines and header text
424 strbuf.append('+');
425 for (int j = 1; j < width.length; j++)
426 strbuf.append(repeat('-', width[j] + 1) + "-+");
427
428 String outsideLine = strbuf.toString();
429
430 strbuf.setLength(0); // clear the buffer
431 strbuf.append('|');
432 for (int j = 1; j < width.length; j++) {
433 String colLabel = md.getColumnLabel(j);
434 strbuf.append(' ');
435 strbuf.append(colLabel);
436 strbuf.append(repeat(' ', width[j] - colLabel.length()));
437 strbuf.append(" |");
438 }
439 // print the header text
440 out.println(outsideLine);
441 out.println(strbuf.toString());
442 out.println(outsideLine.replace('-', '='));
443
444 // print formatted data of each row from resultset
445 long count = 0;
446 for (; rs.next(); count++) {
447 strbuf.setLength(0); // clear the buffer
448 strbuf.append('|');
449 for (int j = 1; j < width.length; j++) {
450 String data = rs.getString(j);
451 if (data == null || rs.wasNull()) {
452 data = "NULL";
453 }
454
455 int filler_length = width[j] - data.length();
456 if (filler_length <= 0) {
457 if (filler_length == 0) {
458 strbuf.append(' ');
459 }
460 strbuf.append(data);
461 } else {
462 strbuf.append(' ');
463 if (isSigned[j]) {
464 // we have a numeric type here, right align
465 strbuf.append(repeat(' ', filler_length));
466 strbuf.append(data);
467 } else {
468 // all other left align
469 strbuf.append(data);
470 strbuf.append(repeat(' ', filler_length));
471 }
472 }
473 strbuf.append(" |");
474 }
475 out.println(strbuf.toString());
476 }
477
478 // print the footer text
479 out.println(outsideLine);
480 out.println(count + " row" + (count != 1 ? "s" : ""));
481 }
482
483 private void changeSchema(String schema) {
484 if (lastSchema == null) {
485 lastSchema = new Stack<String>();
486 lastSchema.push(null);
487 }
488
489 if (!schema.equals(lastSchema.peek())) {
490 if (!lastSchema.contains(schema)) {
491 // create schema
492 out.print("CREATE SCHEMA ");
493 out.print(dq(schema));
494 out.println(";\n");
495 lastSchema.push(schema);
496 }
497
498 out.print("SET SCHEMA ");
499 out.print(dq(schema));
500 out.println(";\n");
501 }
502 }
503 }