1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47 package pl.kernelpanic.dbmonster;
48
49 import java.io.File;
50 import java.io.FileOutputStream;
51 import java.io.IOException;
52 import java.io.OutputStream;
53 import java.io.PrintWriter;
54 import java.sql.Connection;
55 import java.sql.DatabaseMetaData;
56 import java.sql.ResultSet;
57 import java.sql.SQLException;
58 import java.sql.Types;
59 import java.util.HashMap;
60 import java.util.Iterator;
61 import java.util.Map;
62 import java.util.Properties;
63 import java.util.Vector;
64
65 import org.apache.commons.logging.Log;
66 import org.apache.commons.logging.LogFactory;
67
68 import pl.kernelpanic.dbmonster.connection.ConnectionProvider;
69 import pl.kernelpanic.dbmonster.connection.DBCPConnectionProvider;
70 import pl.kernelpanic.dbmonster.connection.Transaction;
71 import pl.kernelpanic.dbmonster.generator.BinaryGenerator;
72 import pl.kernelpanic.dbmonster.generator.BooleanGenerator;
73 import pl.kernelpanic.dbmonster.generator.DataGenerator;
74 import pl.kernelpanic.dbmonster.generator.DateTimeGenerator;
75 import pl.kernelpanic.dbmonster.generator.ForeignKeyGenerator;
76 import pl.kernelpanic.dbmonster.generator.KeyGenerator;
77 import pl.kernelpanic.dbmonster.generator.MaxKeyGenerator;
78 import pl.kernelpanic.dbmonster.generator.NullGenerator;
79 import pl.kernelpanic.dbmonster.generator.NumberGenerator;
80 import pl.kernelpanic.dbmonster.generator.StringGenerator;
81 import pl.kernelpanic.dbmonster.generator.StringKeyGenerator;
82 import pl.kernelpanic.dbmonster.schema.Column;
83 import pl.kernelpanic.dbmonster.schema.Key;
84 import pl.kernelpanic.dbmonster.schema.Schema;
85 import pl.kernelpanic.dbmonster.schema.SchemaException;
86 import pl.kernelpanic.dbmonster.schema.SchemaUtil;
87 import pl.kernelpanic.dbmonster.schema.Table;
88 import pl.kernelpanic.dbmonster.sql.ExtendedTypes;
89
90 /***
91 * Schema Grabber.
92 *
93 * @author Piotr Maj <pm@jcake.com>
94 *
95 * @version $Id: SchemaGrabber.java,v 1.3 2006/01/05 16:29:37 majek Exp $
96 */
97 public class SchemaGrabber {
98
99 /***
100 * Logger.
101 */
102 private Log log = LogFactory.getLog(SchemaGrabber.class);
103
104 /***
105 * Connection provider.
106 */
107 private ConnectionProvider connectionProvider = null;
108
109 /***
110 * Specifies the tables we want to grab. If set to <code>null</code>
111 * all tables will be grabbed.
112 */
113 private Vector tables = null;
114
115 /***
116 * Output stream.
117 */
118 private OutputStream output = System.out;
119
120 /***
121 * Properties.
122 */
123 private Properties properties = new Properties();
124
125 /***
126 * Database schema used in grabbing table. Useful for Oracle,
127 * and other databases that support schema.
128 */
129 private String dbSchema = null;
130
131 /***
132 * Number of rows to generate.
133 */
134 private int numRows = 1000;
135
136 /***
137 * Constructs new SchemaGrabber.
138 */
139 public SchemaGrabber() {
140 }
141
142 /***
143 * Starts SchemaGrabber
144 *
145 * @param args command line arguments
146 *
147 * @throws Exception on errors
148 */
149 public static void main(String[] args) throws Exception {
150 SchemaGrabber sg = new SchemaGrabber();
151 ConnectionProvider cp = new DBCPConnectionProvider();
152 sg.setConnectionProvider(cp);
153 Schema schema = sg.grabSchema();
154 OutputStream os = sg.getOutput();
155 SchemaUtil.serializeSchema(new PrintWriter(os), schema);
156 }
157
158 /***
159 * Starts schema grabber.
160 *
161 * @throws Exception on errors
162 */
163 public void doTheJob() throws Exception {
164 Schema schema = grabSchema();
165 SchemaUtil.serializeSchema(new PrintWriter(output), schema);
166 }
167
168 /***
169 * Returns the output stream where the serialized schema should
170 * be pushed.
171 *
172 * @return output stream
173 */
174 public OutputStream getOutput() {
175 return output;
176 }
177
178 /***
179 * Grabs the schema.
180 *
181 * @return Schema schema
182 *
183 * @throws Exception on errors
184 */
185 public Schema grabSchema() throws Exception {
186
187
188 dbSchema = properties.getProperty("dbmonster.jdbc.schema", null);
189 if (dbSchema != null && "".equals(dbSchema)) {
190 dbSchema = null;
191 }
192 String rows = properties.getProperty("dbmonster.rows", "1000");
193 try {
194 numRows = Integer.valueOf(rows).intValue();
195 } catch (Exception e) {
196 }
197 Schema schema = new Schema();
198 schema.setName("Change me!");
199 if (tables == null) {
200 tables = getTableNames();
201 }
202 Iterator it = tables.iterator();
203 int count = tables.size();
204 int current = 1;
205 log.info("Grabbing schema from database. "
206 + count + " tables to grab.");
207 while (it.hasNext()) {
208 String tableName = (String) it.next();
209 Table t = grabTable(tableName);
210 schema.addTable(t);
211 log.info("Grabbing table " + tableName + ". "
212 + ((current * 100) / count) + "% done.");
213 ++current;
214 }
215 log.info("Grabbing schema from database complete.");
216 return schema;
217 }
218
219 /***
220 * Grabs the table.
221 *
222 * @param name table name
223 *
224 * @return table
225 *
226 * @throws SQLException on SQL errors
227 * @throws SchemaException on schema errors
228 */
229 public Table grabTable(String name)
230 throws SQLException, SchemaException {
231 ResultSet rs;
232
233 Table t = new Table();
234 String tableName = name;
235 if (dbSchema != null) {
236 tableName = dbSchema + "." + tableName;
237 }
238 t.setName(tableName);
239 t.setRows(numRows);
240 Transaction tx = null;
241 try {
242 tx = new Transaction(connectionProvider);
243 Connection conn = tx.begin();
244 DatabaseMetaData md = conn.getMetaData();
245
246
247 Vector keyColumns = new Vector();
248 rs = md.getPrimaryKeys(null, dbSchema, name);
249 while (rs.next()) {
250 String columnName = rs.getString("COLUMN_NAME");
251 keyColumns.add(columnName);
252 }
253 KeyGenerator generator = suggestKeyGenerator(name, keyColumns);
254 if (generator != null) {
255 Key key = new Key();
256 key.setGenerator(generator);
257 t.setKey(key);
258 }
259 else {
260
261 keyColumns.clear();
262 }
263
264
265 HashMap foreignKeys = new HashMap();
266 rs = md.getImportedKeys(null, null, name);
267 while (rs.next()) {
268 String fkColumn = rs.getString("FKCOLUMN_NAME");
269 String pkColumn = rs.getString("PKCOLUMN_NAME");
270 String pkTable = rs.getString("PKTABLE_NAME");
271
272
273
274
275
276
277 foreignKeys.put(fkColumn, new PairBean(pkTable, pkColumn));
278 }
279
280
281 rs = md.getColumns(null, dbSchema, name, "%");
282 while (rs.next()) {
283 String columnName = rs.getString("COLUMN_NAME");
284 if (keyColumns.contains(columnName)) {
285 continue;
286 }
287 Column c = new Column();
288 c.setName(columnName);
289 c.setGenerator(suggestGenerator(name, columnName, foreignKeys));
290 t.addColumn(c);
291 }
292
293 } catch (SQLException e) {
294 tx.abort();
295 log.fatal(e.getMessage());
296 throw e;
297 } finally {
298 if (tx != null) {
299 tx.close();
300 }
301 }
302 return t;
303 }
304
305 /***
306 * Returns names of the tables in the database.
307 *
308 * @return vector of strings
309 *
310 * @throws SQLException on errors
311 */
312 public Vector getTableNames() throws SQLException {
313 Vector v = new Vector();
314 Transaction tx = null;
315 try {
316 tx = new Transaction(connectionProvider);
317 Connection conn = tx.begin();
318 DatabaseMetaData md = conn.getMetaData();
319 ResultSet rs =
320 md.getTables(null, dbSchema, "%", new String[] {"TABLE"});
321 while (rs.next()) {
322 String name = rs.getString("TABLE_NAME");
323 v.add(name);
324 }
325 tx.commit();
326 } catch (SQLException e) {
327 log.fatal(e.getMessage());
328 tx.abort();
329 throw e;
330 } finally {
331 if (tx != null) {
332 tx.close();
333 }
334 }
335 return v;
336 }
337
338 /***
339 * Returns a connection provider used by the grabber.
340 *
341 * @return connection provider instance
342 */
343 public ConnectionProvider getConnectionProvider() {
344 return connectionProvider;
345 }
346
347 /***
348 * Sets the connection provider.
349 *
350 * @param provider connection provider
351 */
352 public void setConnectionProvider(ConnectionProvider provider) {
353 connectionProvider = provider;
354 }
355
356 /***
357 * Returns the logger.
358 *
359 * @return logger
360 */
361 public Log getLog() {
362 return log;
363 }
364
365 /***
366 * Sets the logger
367 *
368 * @param logger new logger
369 */
370 public void setLog(Log logger) {
371 log = logger;
372 }
373
374 /***
375 * Adds a table names to the list of grabbed tables.
376 *
377 * @param name name of the table
378 *
379 * @throws SQLException if table cannot be found
380 */
381 public void addTable(String name) throws SQLException {
382 if (tables == null) {
383 tables = new Vector();
384 }
385 String tableName = name;
386 Transaction tx = null;
387 try {
388 tx = new Transaction(connectionProvider);
389 Connection conn = tx.begin();
390 DatabaseMetaData md = conn.getMetaData();
391 if (md.storesLowerCaseIdentifiers()) {
392 tableName = name.toLowerCase();
393 } else if (md.storesUpperCaseIdentifiers()) {
394 tableName = name.toUpperCase();
395 }
396 ResultSet rs =
397 md.getTables(null, dbSchema, tableName, new String[] {"TABLE"});
398 if (!rs.next()) {
399 throw new SQLException("No such table <" + name + ">.");
400 }
401 tx.commit();
402 tables.add(tableName);
403 } catch (SQLException e) {
404 log.fatal(e.getMessage());
405 tx.abort();
406 throw e;
407 } finally {
408 if (tx != null) {
409 tx.close();
410 }
411 }
412 }
413
414 /***
415 * Sets the output file name.
416 *
417 * @param file file name
418 *
419 * @throws IOException on I/O errors
420 */
421 public void setOutputFile(String file) throws IOException {
422 File f = new File(file);
423 output = new FileOutputStream(f);
424 }
425
426 /***
427 * Sets the properties for the SchemaGrabber
428 * @param p
429 */
430 public void setProperties(Properties p) {
431 properties = p;
432 }
433
434 /***
435 * Suggests which key generator should be used. In current implementation
436 * it only check if the key consists of only one table and if so it uses
437 * MaxKeyGenerator (it the type of the column allow it).
438 *
439 * @param tableName the name ot the table
440 * @param columns columns
441 *
442 * @return key generator or <code>null</code>
443 *
444 * @throws SQLException on errors
445 */
446 private KeyGenerator suggestKeyGenerator(String tableName, Vector columns) throws SQLException {
447
448 KeyGenerator generator = null;
449
450 if (columns.size() != 1) {
451 return generator;
452 }
453 String columnName = (String) columns.get(0);
454 Transaction tx = null;
455 try {
456 tx = new Transaction(connectionProvider);
457 Connection conn = tx.begin();
458 DatabaseMetaData md = conn.getMetaData();
459 ResultSet rs = md.getColumns(null, null, tableName, columnName);
460 rs.next();
461 int dataType = rs.getInt("DATA_TYPE");
462 if (isIntegerType(dataType)) {
463 generator = new MaxKeyGenerator();
464 ((MaxKeyGenerator) generator).setColumnName(columnName);
465 } else if (isTextType(dataType)) {
466 generator = new StringKeyGenerator();
467 ((StringKeyGenerator) generator).setStartValue("0");
468 ((StringKeyGenerator) generator).setColumnName(columnName);
469 } else {
470 if (log.isWarnEnabled()) {
471 log.warn("Datatype " + dataType + " for " + columnName
472 + " is unknown, no Key Generator in schema.xml");
473 }
474 }
475 tx.commit();
476 } catch (SQLException e) {
477 tx.abort();
478 log.fatal(e.getMessage());
479 throw e;
480 } finally {
481 if (tx != null) {
482 tx.close();
483 }
484 }
485 return generator;
486 }
487
488 /***
489 * Suggests the data generator for a column.
490 *
491 * @param tableName table name
492 * @param columnName column name
493 * @param foreignKeys infomation about foreing keys in this table
494 *
495 * @return data generator. Never <code>null</code>.
496 *
497 * @throws SQLException on SQL errors
498 */
499 private DataGenerator suggestGenerator(
500 String tableName,
501 String columnName,
502 Map foreignKeys) throws SQLException {
503
504 DataGenerator generator = new NullGenerator();
505 if (foreignKeys.containsKey(columnName)) {
506 generator = new ForeignKeyGenerator();
507 PairBean bean = (PairBean) foreignKeys.get(columnName);
508 ((ForeignKeyGenerator) generator).setTableName(bean.getKey());
509 ((ForeignKeyGenerator) generator).setColumnName(bean.getValue());
510 return generator;
511 }
512 Transaction tx = null;
513 try {
514 tx = new Transaction(connectionProvider);
515 Connection conn = tx.begin();
516 DatabaseMetaData md = conn.getMetaData();
517 ResultSet rs = md.getColumns(null, null, tableName, columnName);
518 rs.next();
519 int dataType = rs.getInt("DATA_TYPE");
520 String isNullable = rs.getString("IS_NULLABLE");
521 int nulls = 0;
522 if (isNullable == null) {
523 isNullable = "NO";
524 }
525 if ("YES".equals(isNullable)) {
526 nulls = 10;
527 }
528 int columnSize = rs.getInt("COLUMN_SIZE");
529 int decimalDigits = rs.getInt("DECIMAL_DIGITS");
530 if (isIntegerType(dataType)) {
531 generator = new NumberGenerator();
532 NumberGenerator numGen = (NumberGenerator) generator;
533 numGen.setNulls(nulls);
534 if (dataType == Types.BIGINT) {
535 numGen.setReturnedType("long");
536 } else if (dataType == Types.INTEGER) {
537 numGen.setReturnedType("integer");
538 } else if (dataType == Types.SMALLINT
539 || dataType == Types.TINYINT) {
540 numGen.setReturnedType("short");
541 } else if (dataType == Types.DECIMAL || dataType == Types.NUMERIC) {
542 if (decimalDigits > 0) {
543 numGen.setReturnedType("float");
544 numGen.setScale(decimalDigits);
545 } else {
546 numGen.setReturnedType("integer");
547 }
548 }
549 } else if (isDoubleType(dataType)) {
550 generator = new NumberGenerator();
551 NumberGenerator numGen = (NumberGenerator) generator;
552 numGen.setNulls(nulls);
553 if (decimalDigits > 0) {
554 numGen.setReturnedType("double");
555 numGen.setScale(decimalDigits);
556 } else {
557 numGen.setReturnedType("long");
558 }
559 } else if (isBooleanType(dataType)) {
560 generator = new BooleanGenerator();
561 ((BooleanGenerator) generator).setNulls(nulls);
562 } else if (isTextType(dataType)) {
563 generator = new StringGenerator();
564 ((StringGenerator) generator).setNulls(nulls);
565 if (columnSize > 0) {
566 ((StringGenerator) generator).setMaxLength(columnSize);
567 }
568 } else if (isTimeType(dataType)) {
569 generator = new DateTimeGenerator();
570 ((DateTimeGenerator) generator).setNulls(nulls);
571 if (dataType == Types.DATE) {
572 ((DateTimeGenerator) generator).setReturnedType("date");
573 } else if (dataType == Types.TIME) {
574 ((DateTimeGenerator) generator).setReturnedType("time");
575 } else if (dataType == Types.TIMESTAMP) {
576 ((DateTimeGenerator) generator)
577 .setReturnedType("timestamp");
578 }
579 } else if (isBinaryType(dataType)) {
580 generator = new BinaryGenerator();
581 ((BinaryGenerator) generator).setNulls(nulls);
582 if (columnSize > 0) {
583 ((BinaryGenerator) generator).setMaxLength(columnSize);
584 }
585 } else {
586 if (log.isWarnEnabled()) {
587 log.warn("Unknown datatype. No generator created.");
588 }
589 }
590 tx.commit();
591 } finally {
592 if (tx != null) {
593 tx.close();
594 }
595 }
596 return generator;
597 }
598
599 /***
600 * Checks if the type is one of numeric type.
601 *
602 * @param type type to check
603 *
604 * @return <code>true</code> if type accepts numbers
605 */
606 private boolean isIntegerType(int type) {
607 int tempType;
608
609 tempType = ExtendedTypes.getInstance().
610 getStandardAlias(properties.getProperty(
611 "dbmonster.jdbc.driver"), type);
612
613 return (tempType == Types.BIGINT)
614 || (tempType == Types.INTEGER)
615 || (tempType == Types.SMALLINT)
616 || (tempType == Types.TINYINT)
617 || (tempType == Types.DECIMAL)
618 || (tempType == Types.NUMERIC);
619 }
620
621 /***
622 * Checks if the type is one of double type.
623 *
624 * @param type type to check
625 *
626 * @return <code>true</code> if type accepts numbers;
627 * <code>false</code> otherwise
628 */
629 private boolean isDoubleType(int type) {
630 return type == Types.DOUBLE;
631 }
632
633 /***
634 * Checks if the type is one of text or character type.
635 *
636 * @param type type to check
637 *
638 * @return <code>true</code> if type accepts strings
639 */
640 private boolean isTextType(int type) {
641 int tempType;
642
643 tempType = ExtendedTypes.getInstance().
644 getStandardAlias(properties.getProperty(
645 "dbmonster.jdbc.driver"), type);
646
647 return (tempType == Types.CHAR)
648 || (tempType == Types.LONGVARCHAR)
649 || (tempType == Types.VARCHAR);
650 }
651 /***
652 * Checks if the type is one of text or date/time type.
653 *
654 * @param type type to check
655 *
656 * @return <code>true</code> if type accepts dates
657 */
658 private boolean isTimeType(int type) {
659 int tempType;
660
661 tempType = ExtendedTypes.getInstance().
662 getStandardAlias(properties.getProperty(
663 "dbmonster.jdbc.driver"), type);
664
665 return (tempType == Types.DATE)
666 || (tempType == Types.TIME)
667 || (tempType == Types.TIMESTAMP);
668 }
669
670 /***
671 * Checks if the type is boolean
672 *
673 * @param type type to check
674 *
675 * @return <code>true</code> if type is boolean
676 */
677 private boolean isBooleanType(int type) {
678 int tempType;
679
680 tempType = ExtendedTypes.getInstance().
681 getStandardAlias(properties.getProperty(
682 "dbmonster.jdbc.driver"), type);
683
684 return (tempType == Types.BIT)
685 || (tempType == Types.BOOLEAN);
686 }
687
688 /***
689 * Checks if the type is binary, blob, longvarbinary or varbinary
690 *
691 * @param type type to check
692 *
693 * @return <code>true</code> if type is boolean;
694 * <code>false</code> otherwise
695 */
696 private boolean isBinaryType(int type) {
697 if (type == Types.BINARY
698 || type == Types.BLOB
699 || type == Types.LONGVARBINARY
700 || type == Types.VARBINARY)
701 {
702 return true;
703 } else {
704 return false;
705 }
706 }
707 }
708
709 /***
710 * Simple maps a key to a value.
711 */
712 final class PairBean {
713
714 /***
715 * Key.
716 */
717 private String key = null;
718
719 /***
720 * Value.
721 */
722 private String value = null;
723
724 /***
725 * Constructs new PairBean
726 *
727 * @param k key
728 * @param v value
729 */
730 public PairBean(String k, String v) {
731 key = k;
732 value = v;
733 }
734
735 /***
736 * Returns the key.
737 *
738 * @return key
739 */
740 public String getKey() {
741 return key;
742 }
743
744 /***
745 * Sets the key.
746 *
747 * @param k key
748 */
749 public void setKey(String k) {
750 key = k;
751 }
752
753 /***
754 * Returns the value.
755 *
756 * @return value
757 */
758 public String getValue() {
759 return value;
760 }
761
762 /***
763 * Sets the value
764 *
765 * @param v value to set
766 */
767 public void setValue(String v) {
768 value = v;
769 }
770 }