001: /*
002: * DbDesignerWriter.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2007, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.db.report;
013:
014: import java.io.IOException;
015: import java.io.Writer;
016: import java.sql.Connection;
017: import java.sql.DriverManager;
018: import java.sql.PreparedStatement;
019: import java.sql.ResultSet;
020: import java.sql.SQLException;
021: import java.sql.Statement;
022: import java.util.ArrayList;
023: import java.util.HashMap;
024: import java.util.HashSet;
025: import java.util.List;
026: import java.util.Map;
027: import java.util.Set;
028: import workbench.db.ColumnIdentifier;
029: import workbench.db.TableIdentifier;
030: import workbench.db.WbConnection;
031: import workbench.db.importer.TableDependencySorter;
032: import workbench.gui.dbobjects.ProgressPanel;
033: import workbench.storage.RowActionMonitor;
034: import workbench.util.SqlUtil;
035:
036: /**
037: * @author support@sql-workbench.net
038: */
039: public class DbDesignerWriter {
040: private List<ReportTable> tables;
041: private String modelName;
042: private WbConnection dbConnection;
043: private int objectId = 1000;
044: private int columnId = 1;
045: private Map<DataType, Integer> dataTypeIds;
046: private Map<ReportTable, Integer> tableIds;
047: private Map<ReportTable, Map<ReportColumn, Integer>> columnIds;
048: private Map<String, TableRelation> relations = new HashMap<String, TableRelation>();
049: private RowActionMonitor monitor;
050: private ProgressPanel progressPanel;
051:
052: public DbDesignerWriter(WbConnection conn,
053: List<TableIdentifier> tableList, String name)
054: throws SQLException {
055: this .dbConnection = conn;
056: this .tables = new ArrayList<ReportTable>(tableList.size());
057: TableDependencySorter sorter = new TableDependencySorter(conn);
058: sorter.sortForInsert(tableList);
059: tableIds = new HashMap<ReportTable, Integer>(tables.size());
060: columnIds = new HashMap<ReportTable, Map<ReportColumn, Integer>>(
061: tables.size() * 5);
062: for (TableIdentifier tbl : tableList) {
063: ReportTable rtable = new ReportTable(tbl,
064: this .dbConnection, "", true, true, true, true,
065: false);
066: tables.add(rtable);
067: getTableId(rtable);
068: }
069: this .modelName = name;
070: for (ReportTable tbl : tables) {
071: addRelations(tbl);
072: }
073: }
074:
075: public void setProgressPanel(ProgressPanel panel) {
076: this .progressPanel = panel;
077: }
078:
079: public void setMonitor(RowActionMonitor m) {
080: this .monitor = m;
081: }
082:
083: public int getTableCount() {
084: return tables.size();
085: }
086:
087: private int getNextColumnId() {
088: return columnId++;
089: }
090:
091: private int getNextObjectId() {
092: return objectId++;
093: }
094:
095: public void writeXml(Writer out) throws IOException, SQLException {
096: writeHeader(out);
097: writeDataTypes(out);
098: out.write(" </SETTINGS>\n");
099: out.write(" <METADATA>\n");
100: writeTables(out);
101: writeRelations(out);
102: out.write(" </METADATA>\n");
103: writeEnd(out);
104: }
105:
106: protected void writeHeader(Writer out) throws IOException {
107: out.write("<?xml version=\"1.0\" standalone=\"yes\" ?>\n");
108: out.write("<DBMODEL Version=\"4.0\">\n");
109: out.write(" <SETTINGS>\n");
110: out.write(" <GLOBALSETTINGS ModelName=\"" + modelName
111: + "\" IDModel=\"0\" " + "IDVersion=\"0\" "
112: + "VersionStr=\"1.0.0.0\" "
113: + "UseVersionHistroy=\"1\" " + "AutoIncVersion=\"1\" "
114: + "DatabaseType=\"MySQL\" " + "ZoomFac=\"100.00\" "
115: + "XPos=\"0\" " + "YPos=\"0\" "
116: + "DefaultDataType=\"1\" "
117: + "DefaultTablePrefix=\"0\" "
118: + "PageOrientation=\"1\" "
119: + "PageFormat=\"A4 (210x297 mm, 8.26x11.7 inches)\" "
120: + "TableNameInRefs=\"0\" " + "FKPrefix=\"FK\" "
121: + "FKPostfix=\"\" " + "CreateFKRefDefIndex=\"0\" "
122: + "DBQuoteCharacter=\"\"/>\n");
123: }
124:
125: private void writeEnd(Writer out) throws IOException {
126: out.write("</DBMODEL>");
127: }
128:
129: private void writeDataTypes(Writer out) throws IOException,
130: SQLException {
131: Set<DataType> types = new HashSet<DataType>();
132: out.write(" <DATATYPEGROUPS>\n");
133: out
134: .write(" <DATATYPEGROUP Name=\"Numeric Types\" Icon=\"1\"/>\n");
135: out
136: .write(" <DATATYPEGROUP Name=\"String Types\" Icon=\"3\"/>\n");
137: out
138: .write(" <DATATYPEGROUP Name=\"Date and Time Types\" Icon=\"2\"/>\n");
139: out
140: .write(" <DATATYPEGROUP Name=\"Blob and Text Types\" Icon=\"4\" />\n");
141: out.write(" <DATATYPEGROUP Name=\"Other\" Icon=\"3\"/>\n");
142: out.write(" </DATATYPEGROUPS>\n");
143:
144: for (ReportTable tbl : tables) {
145: for (ReportColumn c : tbl.getColumns()) {
146: DataType type = DataType.getDataType(c.getColumn());
147: types.add(type);
148: int jdbctype = c.getColumn().getDataType();
149:
150: if (SqlUtil.isNumberType(jdbctype)) {
151: type.setTypeGroupId(0);
152: } else if (SqlUtil.isStringType(jdbctype)) {
153: type.setTypeGroupId(1);
154: } else if (SqlUtil.isDateType(jdbctype)) {
155: type.setTypeGroupId(2);
156: } else if (SqlUtil.isClobType(jdbctype)
157: || SqlUtil.isBlobType(jdbctype)) {
158: type.setTypeGroupId(3);
159: } else {
160: type.setTypeGroupId(4);
161: }
162: }
163: }
164:
165: out.write(" <DATATYPES>\n");
166: int typeId = 1;
167: this .dataTypeIds = new HashMap<DataType, Integer>();
168: for (DataType type : types) {
169: type.setId(typeId++);
170: dataTypeIds.put(type, type.getId());
171: int paramCount = type.getParameters().size();
172: int paramRequired = (paramCount == 0 ? 0 : 1);
173: boolean mapping = !type.getDbmsTypeName().equals(
174: type.getTypeName());
175: String mappingName = (mapping ? type.getDbmsTypeName() : "");
176:
177: out
178: .write(" <DATATYPE ID=\""
179: + type.getId()
180: + "\" "
181: + "IDGroup=\""
182: + type.getTypeGroupId()
183: + "\" "
184: + "TypeName=\""
185: + type.getTypeName()
186: + "\" "
187: + "ParamCount=\""
188: + paramCount
189: + "\" "
190: + "OptionCount=\"0\" ParamRequired=\""
191: + paramRequired
192: + "\" "
193: + "EditParamsAsString=\"0\" SynonymGroup=\"0\" PhysicalMapping="
194: + boolToInt(mapping) + " "
195: + "PhysicalTypeName=\"" + mappingName
196: + "\" >\n");
197: if (paramCount > 0) {
198: out.write(" <PARAMS>\n");
199: for (int i = 0; i < paramCount; i++) {
200: out.write(" <PARAM Name=\""
201: + type.getParameters().get(i) + "\" />\n");
202: }
203: out.write(" </PARAMS>\n");
204: }
205: out.write(" </DATATYPE>\n");
206: }
207: out.write(" </DATATYPES>\n");
208: out.write(" <COMMON_DATATYPES>\n");
209: for (DataType type : types) {
210: out.write(" <COMMON_DATATYPE ID=\"" + type.getId()
211: + "\"/>\n");
212: }
213: out.write(" </COMMON_DATATYPES>\n");
214: }
215:
216: private void writeTables(Writer out) throws IOException {
217: out.write(" <TABLES>\n");
218:
219: int tableOrder = 0;
220: int x = 5;
221: int y = 5;
222: int tableRowCount = 0;
223:
224: int currentTable = 1;
225:
226: for (ReportTable tbl : tables) {
227:
228: String tableName = tbl.getTable().getTableName();
229: if (this .monitor != null) {
230: this .monitor.setCurrentObject(tableName,
231: currentTable++, tables.size());
232: }
233: if (this .progressPanel != null) {
234: this .progressPanel.setInfoText(tableName);
235: }
236:
237: int id = getTableId(tbl);
238: tableOrder++;
239: out
240: .write(" <TABLE ID=\""
241: + id
242: + "\" "
243: + "Tablename=\""
244: + tbl.getTable().getTableName()
245: + "\" "
246: + "XPos=\""
247: + x
248: + "\" YPos=\""
249: + y
250: + "\" "
251: + "TableType=\"0\" TablePrefix=\"0\" nmTable=\"0\" Temporary=\"0\" UseStandardInserts=\"0\" StandardInserts=\"\\n\" "
252: + "Comments=\""
253: + tbl.getTableComment()
254: + "\" "
255: + "Collapsed=\"0\" "
256: + "IsLinkedObject=\"0\" IDLinkedModel=\"-1\" Obj_id_Linked=\"-1\" OrderPos=\""
257: + tableOrder + "\" " + ">\n");
258: out.write(" <COLUMNS>\n");
259: int maxCols = 0;
260: int maxNameLength = 0;
261:
262: List<ReportColumn> columns = tbl.getColumnsSorted();
263: // first write all PK columns
264: int pos = 1;
265: for (ReportColumn c : columns) {
266: if (!c.getColumn().isPkColumn())
267: continue;
268: int colLength = writeColumn(tbl, c, pos++, out);
269: pos++;
270: if (colLength > maxNameLength) {
271: maxNameLength = colLength;
272: }
273: }
274:
275: pos = 1;
276: // now write all FK columns
277: for (ReportColumn c : columns) {
278: if (c.getForeignKey() == null)
279: continue;
280: int colLength = writeColumn(tbl, c, pos++, out);
281: if (colLength > maxNameLength) {
282: maxNameLength = colLength;
283: }
284: }
285:
286: // now write the rest
287: pos = 1;
288: for (ReportColumn c : columns) {
289: if (c.getColumn().isPkColumn()
290: || c.getForeignKey() != null)
291: continue;
292: int colLength = writeColumn(tbl, c, pos++, out);
293: if (colLength > maxNameLength) {
294: maxNameLength = colLength;
295: }
296: }
297:
298: if (columns.size() > maxCols) {
299: maxCols = columns.size();
300: }
301: if (tbl.getTable().getTableName().length() > maxNameLength) {
302: maxNameLength = tbl.getTable().getTableName().length();
303: }
304:
305: out.write(" </COLUMNS>\n");
306: writeTableRelations(tbl, out);
307: out.write(" </TABLE>\n");
308: x += (maxNameLength * 10) + 10;
309: tableRowCount++;
310: if (tableRowCount == 4) {
311: x = 5;
312: y += (maxCols * 30) + 40;
313: maxCols = 0;
314: tableRowCount = 0;
315: }
316: }
317: out.write(" </TABLES>\n");
318: }
319:
320: private int writeColumn(ReportTable tbl, ReportColumn c, int pos,
321: Writer out) throws IOException {
322: DataType type = DataType.getDataType(c.getColumn());
323:
324: int typeId = dataTypeIds.get(type);
325: ColumnIdentifier col = c.getColumn();
326: int paramCount = type.getParameters().size();
327: String params = "";
328:
329: if (paramCount == 1) {
330: params = "(" + col.getColumnSize() + ")";
331: } else if (paramCount == 2) {
332: params = "(" + col.getColumnSize() + ","
333: + col.getDecimalDigits() + ")";
334: }
335: int colId = getColumnId(tbl, c);
336: String isFk = boolToInt(c.getForeignKey() != null);
337: out.write(" <COLUMN ID=\"" + colId + "\" "
338: + "ColName=\"" + col.getColumnName() + "\" "
339: + "idDatatype=\"" + typeId + "\" "
340: + "DatatypeParams=\"" + params + "\" "
341: + "Width=\"-1\" Prec=\"-1\" " + "Pos=\"" + pos + "\" "
342: + "PrimaryKey=" + boolToInt(col.isPkColumn())
343: + " NotNull=" + boolToInt(!col.isNullable()) + " "
344: + "PrevColName=\"\" " + "AutoInc=\"0\" IsForeignKey="
345: + isFk + "" + ">\n");
346:
347: out.write(" </COLUMN>\n");
348: int colLength = col.getColumnName().length() + params.length()
349: + type.getTypeName().length();
350: return colLength;
351: }
352:
353: private List<Integer> getStartRelations(ReportTable source) {
354: int sourceId = getTableId(source);
355: List<Integer> result = new ArrayList<Integer>();
356: for (TableRelation rel : relations.values()) {
357: if (rel.getSourceTableId() == sourceId) {
358: result.add(rel.getRelationId());
359: }
360: }
361: return result;
362: }
363:
364: private List<Integer> getEndRelations(ReportTable source) {
365: int sourceId = getTableId(source);
366: List<Integer> result = new ArrayList<Integer>();
367: for (TableRelation rel : relations.values()) {
368: if (rel.getTargetTableId() == sourceId) {
369: result.add(rel.getRelationId());
370: }
371: }
372: return result;
373: }
374:
375: private void writeTableRelations(ReportTable tbl, Writer out)
376: throws IOException {
377: List<Integer> start = getStartRelations(tbl);
378: if (start.size() > 0) {
379: out.write(" <RELATIONS_START>\n");
380: for (Integer i : start) {
381: out.write(" <RELATION_START ID=\"" + i
382: + "\"/>\n");
383: }
384: out.write(" </RELATIONS_START>\n");
385: }
386: List<Integer> end = getEndRelations(tbl);
387: if (end.size() > 0) {
388: out.write(" <RELATIONS_END>\n");
389: for (Integer i : end) {
390: out.write(" <RELATION_END ID=\"" + i
391: + "\"/>\n");
392: }
393: out.write(" </RELATIONS_END>\n");
394: }
395: }
396:
397: private void writeRelations(Writer out) throws IOException {
398: out.write(" <RELATIONS>\n");
399: int order = 1;
400: for (TableRelation relation : relations.values()) {
401: out.write(" <RELATION ID=\""
402: + relation.getRelationId() + "\"");
403: out.write(" RelationName=\"" + relation.getRelationName()
404: + "\"");
405: out.write(" Kind=\"" + relation.getRelationKind() + "\"");
406: out.write(" SrcTable=\"" + relation.getTargetTableId()
407: + "\"");
408: out.write(" DestTable=\"" + relation.getSourceTableId()
409: + "\"");
410: out.write(" FKFields=\"" + relation.getColumns() + "\"");
411: out.write(" relDirection=\"4\"");
412: out.write(" OptionalStart=\"0\" OptionalEnd=\"0\"");
413: out.write(" Invisible=\"0\"");
414: out.write(" CreateRefDef=\"1\"");
415: out.write(" FKFieldsComments=\"\\n\"");
416: out.write(" Comments=\"\"");
417: out.write(" OrderPos=\"" + order + "\"");
418: out
419: .write(" FKRefDefIndex_Obj_id=\"-1\" Splitted=\"0\" IsLinkedObject=\"0\" IDLinkedModel=\"-1\" Obj_id_Linked=\"-1\"");
420: out.write(" RefDef=\"Matching=0\\nOnDelete="
421: + relation.getDeleteAction() + "\\nOnUpdate="
422: + relation.getUpdateAction() + "\\n\"");
423: out
424: .write(" MidOffset=\"0\" CaptionOffsetX=\"0\" CaptionOffsetY=\"0\" StartIntervalOffsetX=\"0\" StartIntervalOffsetY=\"0\" EndIntervalOffsetX=\"0\" EndIntervalOffsetY=\"0\"");
425: out.write("/>\n");
426: }
427: out.write(" </RELATIONS>\n");
428: }
429:
430: private ReportTable findTable(TableIdentifier tableName) {
431: for (ReportTable tbl : tables) {
432: if (tbl.getTable().equals(tableName))
433: return tbl;
434: }
435: return null;
436: }
437:
438: private String buildRelationKey(ReportTable source,
439: ReportTable target) {
440: return source.getTable().getTableName() + "$-$"
441: + target.getTable().getTableName();
442: }
443:
444: private void addRelations(ReportTable source) {
445: ReportColumn[] cols = source.getColumns();
446: for (ReportColumn col : cols) {
447: ColumnReference ref = col.getForeignKey();
448: if (ref != null) {
449: String key = buildRelationKey(source, ref
450: .getForeignTable());
451: TableRelation rel = relations.get(key);
452: TableIdentifier tbl = ref.getForeignTable().getTable();
453: ReportTable rep = findTable(tbl);
454: if (rel == null) {
455: int sid = getTableId(source);
456: int tid = getTableId(rep);
457: int id = getNextObjectId();
458: rel = new TableRelation(id, sid, tid, ref
459: .getFkName());
460: rel.setDeleteAction(ref.getDeleteRule());
461: rel.setUpdateAction(ref.getUpdateRule());
462: relations.put(key, rel);
463: }
464: ReportColumn tcol = rep.findColumn(ref
465: .getForeignColumn());
466: rel.addColumnReference(col.getColumn(), tcol
467: .getColumn());
468: }
469: }
470: }
471:
472: private int getTableId(ReportTable table) {
473: Integer id = tableIds.get(table);
474: if (id == null) {
475: int tid = getNextObjectId();
476: tableIds.put(table, tid);
477: return tid;
478: }
479: return id.intValue();
480: }
481:
482: private int getColumnId(ReportTable table, ReportColumn column) {
483: Map<ReportColumn, Integer> idmap = columnIds.get(table);
484: if (idmap == null) {
485: idmap = new HashMap<ReportColumn, Integer>();
486: columnIds.put(table, idmap);
487: }
488: int colid = -1;
489: Integer id = idmap.get(column);
490:
491: if (id == null) {
492: colid = getNextColumnId();
493: idmap.put(column, colid);
494: } else {
495: colid = id.intValue();
496: }
497: return colid;
498: }
499:
500: private String boolToInt(boolean flag) {
501: if (flag)
502: return "\"1\"";
503: return "\"0\"";
504: }
505:
506: public static void main(String args[]) {
507: Connection con = null;
508: WbConnection wb = null;
509: ResultSet rs = null;
510: PreparedStatement pstmt = null;
511: Statement stmt = null;
512: try {
513: Class.forName("org.h2.Driver");
514: con = DriverManager.getConnection(
515: "jdbc:h2:c:/projects/turnierverwaltung/db/judo",
516: "sa", "");
517: wb = new WbConnection("test", con, null);
518: SchemaReporter rep = new SchemaReporter(wb);
519: TableIdentifier[] tables = new TableIdentifier[] {
520: new TableIdentifier("EVENT"),
521: new TableIdentifier("EVENT_TYPE") };
522: rep.setTableList(tables);
523: rep.setDbDesigner(true);
524: rep.setOutputFilename("c:/temp/report/test.xml");
525: rep.setIncludeTables(true);
526: rep.writeXml();
527:
528: rep.setDbDesigner(false);
529: rep.setOutputFilename("c:/temp/report/testwb.xml");
530: rep.setIncludeTables(true);
531: rep.writeXml();
532: } catch (Throwable e) {
533: e.printStackTrace();
534: } finally {
535: try {
536: stmt.executeUpdate("shutdown immediate");
537: } catch (Exception e) {
538: }
539: // System.out.println("Closing connection...");
540: SqlUtil.closeAll(rs, stmt);
541: try {
542: con.close();
543: } catch (Throwable th) {
544: }
545: }
546:
547: System.out.println("done.");
548: }
549: }
|