Source code: org/enableit/db/SchemaExporter.java
1 // Package declaration
2 package org.enableit.db ;
3
4 // Java imports
5 import java.io.File ;
6 import java.io.FileOutputStream ;
7 import java.sql.Connection ;
8 import java.sql.DatabaseMetaData ;
9 import java.sql.ResultSet ;
10 import java.util.ArrayList ;
11 import java.util.Iterator ;
12 import java.util.StringTokenizer ;
13 import java.util.TreeMap ;
14
15 // XML imports (inc in J2EE)
16 import javax.xml.parsers.DocumentBuilder;
17 import javax.xml.parsers.DocumentBuilderFactory;
18 import javax.xml.parsers.FactoryConfigurationError;
19 import javax.xml.parsers.ParserConfigurationException;
20
21 import org.w3c.dom.Document;
22 import org.w3c.dom.Element;
23 import org.w3c.dom.Node;
24 import org.w3c.dom.NodeList;
25
26 // Log4J imports
27 import org.apache.log4j.Category;
28
29
30 /**
31 * Export database schemas as XML.
32 *
33 * Note on ASA interpretation of DatabaseMetaData object:
34 * SCHEMA = users and groups
35 * @deprecated Moved to darrt subpackage
36 */
37 public class SchemaExporter
38 {
39 /**
40 * Define a static Category instance for logging.
41 */
42 private static Category logger = Category.getInstance(SchemaExporter.class);
43 /*
44 * Constructors
45 */
46 /**
47 * Default Constructor
48 */
49 public SchemaExporter()
50 {
51
52 }
53
54 /**
55 * A pattern identifying tables whose schema should be exported.
56 * <p>
57 * Single chars may be represented by '_' and multiple chars by '%'.
58 */
59 public void setTablePattern(String tablePattern)
60 {
61 this.tablePattern = tablePattern ;
62 }
63
64 /**
65 * The name of the schema to be exported.
66 * <p>
67 * If no schema is specified all will be exported.
68 */
69 public void setTargetSchema(String targetSchema)
70 {
71 this.targetSchema = targetSchema ;
72 }
73
74 /**
75 * Export the schema identified by the provided connection parameters.
76 */
77 public Document export(String driver, String url, String userid, String password)
78 throws DBException
79 {
80 Document doc = null ;
81 try {
82 Connection conn = ConnectionFactory.getConnection(driver,
83 url,
84 userid,
85 password );
86 if (conn==null) {
87 throw new DBException("Null connection when connecting to database") ;
88 }
89
90 doc = export(conn) ;
91 conn.close() ;
92 conn = null ;
93
94 } catch (DBException e) {
95 throw e ;
96 } catch (Exception e) {
97 throw new DBException(e.getClass().getName() + ":" + e.getMessage()) ;
98 }
99
100 return doc ;
101 }
102
103 /**
104 * Export the schema at the database already connected to.
105 */
106 private Document export(Connection conn)
107 throws DBException
108 {
109 Document doc = null ;
110 // Construct an xml doc
111 DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
112 try {
113 DocumentBuilder docBuilder = factory.newDocumentBuilder();
114 doc = docBuilder.newDocument() ;
115 } catch (ParserConfigurationException pce) {
116 pce.printStackTrace();
117 throw new DBException(pce.getMessage()) ;
118 }
119
120 // get schema items to place in doc
121 try {
122 Element databaseTag = doc.createElement(ROOT_ELEMENT) ;
123 DatabaseMetaData dmd = conn.getMetaData() ;
124
125 /*
126 * General database provider information
127 */
128 Element providerTag = doc.createElement(PROVIDER) ;
129 Element productNameTag = doc.createElement(NAME) ;
130 productNameTag.appendChild(doc.createTextNode(dmd.getDatabaseProductName()));
131 providerTag.appendChild(productNameTag) ;
132
133 Element productVsnTag = doc.createElement(VSN) ;
134 productVsnTag.appendChild(doc.createTextNode(dmd.getDatabaseProductVersion())) ;
135 providerTag.appendChild(productVsnTag) ;
136
137 Element productUrlTag = doc.createElement(URL_STRING) ;
138 productUrlTag.appendChild(doc.createTextNode(dmd.getURL())) ;
139 providerTag.appendChild(productUrlTag) ;
140
141 // Database driver info
142 Element driverTag = doc.createElement(DRIVER) ;
143 Element driverNameTag = doc.createElement(NAME) ;
144 driverNameTag.appendChild(doc.createTextNode(dmd.getDriverName())) ;
145 driverTag.appendChild(driverNameTag) ;
146 Element driverVsnTag = doc.createElement(VSN) ;
147 driverVsnTag.appendChild(doc.createTextNode(dmd.getDriverVersion())) ;
148 driverTag.appendChild(driverVsnTag) ;
149 providerTag.appendChild(driverTag) ;
150 databaseTag.appendChild(providerTag);
151
152 /*
153 * Database metadata information (not sure if this is useful yet)
154 */
155 Element metadataTag = doc.createElement(METADATA) ;
156 Element catalogsTag = doc.createElement(CATALOGS) ;
157 ResultSet catalogs = dmd.getCatalogs() ;
158 ArrayList catList = (ArrayList)DBUtils.convertResultToList(catalogs) ;
159 logger.debug("CATALOGUES: " + catList.toString());
160 //Element productNameTag = doc.createElement(NAME) ;
161 catalogsTag.appendChild(doc.createTextNode(catList.toString()));
162 metadataTag.appendChild(catalogsTag) ;
163 databaseTag.appendChild(metadataTag) ;
164
165 /*
166 * Get the schema data (including the table data)
167 */
168 // TODO accept a pattern instead of using the DBA schema
169
170 ResultSet schemas = dmd.getSchemas() ;
171 ArrayList schList = (ArrayList)DBUtils.convertResultToList(schemas) ;
172
173 for (Iterator i=schList.iterator() ; i.hasNext() ; ) {
174 Element schemaTag = doc.createElement(SCHEMA) ;
175 TreeMap row = (TreeMap)i.next() ;
176 String schemaName = (String)row.get("TABLE_SCHEM") ;
177
178 if (targetSchema==null || schemaName.equalsIgnoreCase(targetSchema)) {
179 Element schemaNameTag = doc.createElement(NAME) ;
180 schemaNameTag.appendChild(doc.createTextNode(schemaName));
181 schemaTag.appendChild(schemaNameTag) ;
182
183 addTables(dmd, doc, schemaName, schemaTag) ;
184 databaseTag.appendChild(schemaTag) ;
185 }
186 }
187 doc.appendChild(databaseTag);
188 } catch (Exception e) {
189 // Things to catch : array out of bounds from no column results found
190 logger.debug(e.getMessage()) ;
191 logger.debug(e) ;
192 //if (e.getMessage()==null || e.getMessage().length()==0) {
193 e.printStackTrace(System.err) ;
194 //}
195 }
196 return doc ;
197 }
198
199 /**
200 * Extract table metadata adding appropriate tags.
201 */
202 private void addTables(DatabaseMetaData dmd, Document doc, String schemaName, Element schemaTag)
203 throws Exception {
204 // TODO define schemaexport exception
205 try {
206
207 ResultSet tables = dmd.getTables(null, schemaName, "%", null) ;
208 while (tables.next()) {
209 String tableName = tables.getString("TABLE_NAME") ;
210 /*
211 * The table portion
212 */
213 Element tableTag = doc.createElement(TABLE) ;
214 Element nameTag = doc.createElement(NAME) ;
215 nameTag.appendChild(doc.createTextNode(tableName));
216 tableTag.appendChild(nameTag) ;
217
218 // Capture Primary Key Name
219 ResultSet pk = dmd.getPrimaryKeys(catalog, schemaName, tableName) ;
220 ArrayList pkList = (ArrayList)DBUtils.convertResultToList(pk) ;
221 pk.close() ;
222 if (pkList.size()==0) {
223 logger.debug("No primary key was found for " + tableName) ;
224 //throw new DBException(msg) ;
225 } else {
226 TreeMap pkCol = (TreeMap)pkList.get(0);
227 logger.debug(pkList.toString()) ;
228 }
229
230 // All columns' data
231 ResultSet columns = dmd.getColumns(catalog, schemaName, tableName, null) ;
232 ArrayList colList = (ArrayList)DBUtils.convertResultToList(columns) ;
233 columns.close() ;
234 ArrayList pks = new ArrayList() ;
235 pks.add( ((TreeMap)colList.get(0)).get("COLUMN_NAME") ) ;
236
237 // All foreign keys' data
238 ResultSet fKeys = dmd.getImportedKeys(catalog, schemaName, tableName) ;
239 ArrayList fKeyList = (ArrayList)DBUtils.convertResultToList(fKeys) ;
240 //logger.debug("FKs found=" + fKeyList);
241 fKeys.close() ;
242
243 // For each list element (represents a table column) ...
244 Element columnTag = null ;
245 for ( Iterator i=colList.iterator() ; i.hasNext() ; ) {
246 TreeMap tableCol = (TreeMap)i.next() ;
247 logger.debug(tableCol) ;
248 // ... for each column construct column element
249 columnTag = doc.createElement(COLUMN) ;
250
251 // get column name & description
252 String colName = (String)tableCol.get("COLUMN_NAME") ;
253 Element colDescTag = doc.createElement(DESCRIPTION) ;
254 colDescTag.appendChild(doc.createTextNode( colName )) ;
255 Element columnNameTag = doc.createElement(COL_NAME) ;
256 columnNameTag.appendChild(doc.createTextNode(colName));
257 columnTag.appendChild(colDescTag) ;
258 columnTag.appendChild(columnNameTag) ;
259
260 // get column datatype
261 Integer typeObj = (Integer)tableCol.get("DATA_TYPE") ;
262 int type = ( typeObj==null ? Integer.MIN_VALUE : typeObj.intValue() );
263 Integer scaleObj = (Integer)tableCol.get("COLUMN_SIZE") ;
264 int scale = ( scaleObj==null ? Integer.MIN_VALUE : scaleObj.intValue() );
265 Integer precisionObj = (Integer)tableCol.get("DECIMAL_DIGITS") ;
266 int precision = ( precisionObj==null ? Integer.MIN_VALUE : precisionObj.intValue() );
267 String colType = new SqlType(type, scale, precision).toString() ;
268 Element columnTypeTag = doc.createElement(COL_TYPE) ;
269 columnTypeTag.appendChild(doc.createTextNode(colType)) ;
270 columnTag.appendChild(columnTypeTag) ;
271
272 // get column null / not null
273 // assume the worst (includes don't know case)
274 boolean isNullable = false ;
275 logger.debug("Nullable: " + tableCol.get("IS_NULLABLE").toString()) ;
276 if ( ((String)tableCol.get("IS_NULLABLE")).equals("YES") ) {
277 isNullable = true ;
278 }
279 Element nullableTag = doc.createElement(NULLABLE) ;
280 nullableTag.appendChild(doc.createTextNode(String.valueOf(isNullable))) ;
281 columnTag.appendChild(nullableTag) ;
282
283 // determine if column is primary key
284 if ( pks.contains(colName) ) {
285 Element pkTag = doc.createElement(PK) ;
286 pkTag.appendChild(doc.createTextNode("true")) ;
287 columnTag.appendChild(pkTag) ;
288 }
289
290 // determine if column is a foreign key
291 for ( Iterator j=fKeyList.iterator() ; j.hasNext() ; ) {
292 TreeMap key = (TreeMap)j.next() ;
293 String fKeyName = (String)key.get("FKCOLUMN_NAME") ;
294 if (fKeyName!=null && fKeyName.equals(colName)) {
295 logger.debug("Found foreign key for column: " + fKeyName);
296 Element fkTag = doc.createElement(FK) ;
297 Element fkTableTag = doc.createElement(FK_TABLE) ;
298 fkTableTag.appendChild(doc.createTextNode(
299 (String)key.get("PKTABLE_NAME")) ) ;
300 fkTag.appendChild(fkTableTag) ;
301
302 // foreign key data col (the actual foreign key)
303 Element fkDataColTag = doc.createElement(FK_DATA_COLUMN) ;
304 String pkName = (String)key.get("PKCOLUMN_NAME") ;
305 Element fkDataColDescTag = doc.createElement(DESCRIPTION) ;
306 fkDataColDescTag.appendChild(doc.createTextNode( pkName )) ;
307 fkDataColTag.appendChild(fkDataColDescTag) ;
308 Element fkDataColNameTag = doc.createElement(FK_DATA_COL_NAME) ;
309 fkDataColNameTag.appendChild(doc.createTextNode( pkName )) ;
310
311 // fk display column
312 Element fkDisplayColTag = doc.createElement(FK_DISPLAY_COLUMN) ;
313 Element fkDisplayColNameTag = doc.createElement(FK_DISPLAY_COL_NAME) ;
314 fkDisplayColNameTag.appendChild(doc.createTextNode( pkName )) ;
315 fkDataColTag.appendChild(fkDataColNameTag) ;
316 Element fkDisplayColDescTag = doc.createElement(DESCRIPTION) ;
317 fkDisplayColDescTag.appendChild(doc.createTextNode( pkName )) ;
318 fkDisplayColTag.appendChild(fkDisplayColDescTag) ;
319 fkDisplayColTag.appendChild(fkDisplayColNameTag) ;
320 fkTag.appendChild(fkDataColTag) ;
321 fkTag.appendChild(fkDisplayColTag) ;
322
323 columnTag.appendChild(fkTag) ;
324 }
325 }
326
327 tableTag.appendChild(columnTag) ;
328 }
329 schemaTag.appendChild(tableTag);
330 } // end while have tables
331 } catch (Exception e) {
332 logger.debug("Error whilst trying to process schema named: " + schemaName) ;
333 e.printStackTrace() ;
334 throw new Exception(e.getMessage()) ;
335 }
336
337 }
338
339 /*
340 * Methods
341 */
342 /**
343 *
344 *
345 * @param args
346 * args[0] - tableName to turn into XML representation
347 */
348 public static void main( String[] args )
349 {
350 try {
351 SchemaExporter transformer = new SchemaExporter() ;
352 transformer.export(args[0], args[1], args[2], args[3]) ;
353
354 } catch (Exception e) {
355 System.out.println("Failed: " + e.getMessage()) ;
356 }
357 }
358
359 /**
360 * Returns the directory name classes should be stored in based on the
361 * Java package name received
362 */
363 private String getPackageDir( String javaPackage )
364 {
365 StringBuffer dir = new StringBuffer() ;
366 StringTokenizer st = new StringTokenizer(javaPackage, ".");
367 while (st.hasMoreTokens()) {
368 dir.append(st.nextToken()) ;
369 dir.append("/") ;
370 }
371
372 // remove final '/' and return
373 return dir.substring(0, dir.length()-1) ;
374 }
375
376 /*
377 * Properties
378 */
379 private String tablePattern ;
380 private String catalog ;
381 private String targetDir ;
382 private String targetSchema ;
383
384 /*
385 * Constant Properties
386 */
387
388
389 /*
390 * Application specifics
391 *//*
392 public static final String JAVA_PACKAGE_DIR = "package-dir" ;
393 public static final String JAVA_PACKAGE_NAME = "package-name" ;
394 public static final String JAVA_PACKAGE_VALUE = "org.thestephensons.webservices" ;
395 */
396 /*
397 * Document Tags
398 */
399 //public static final String APP = "app" ;
400 //public static final String JAVA = "java" ;
401 public static final String ROOT_ELEMENT = "database" ;
402 public static final String SCHEMA = "schema" ;
403 public static final String PROVIDER = "provider" ;
404 public static final String DRIVER = "driver" ;
405 public static final String VSN = "version" ;
406 public static final String URL_STRING = "url" ;
407 public static final String METADATA = "metadata" ;
408 public static final String CATALOGS = "catalogues" ;
409 public static final String SCHEMAS = "schemas" ;
410 public static final String DESCRIPTION = "description" ;
411 public static final String TABLE = "table" ;
412 public static final String NAME = "name" ;
413 public static final String COLUMN = "column" ;
414 public static final String COL_NAME = "col-name" ;
415 public static final String COL_TYPE = "col-type" ;
416 public static final String NULLABLE = "null" ;
417 public static final String PK = "primary-key" ;
418 public static final String FK = "foreign-key" ;
419 public static final String FK_TABLE = "fk-table-name" ;
420 public static final String FK_DATA_COLUMN = "data-column" ;
421 public static final String FK_DATA_COL_NAME = "col-name" ;
422 public static final String FK_DISPLAY_COLUMN = "display-column" ;
423 public static final String FK_DISPLAY_COL_NAME = "col-name" ;
424
425 }
426