DDLGenerator


1   /*
2    * The contents of this file are subject to the terms 
3    * of the Common Development and Distribution License 
4    * (the License).  You may not use this file except in
5    * compliance with the License.
6    * 
7    * You can obtain a copy of the license at 
8    * https://glassfish.dev.java.net/public/CDDLv1.0.html or
9    * glassfish/bootstrap/legal/CDDLv1.0.txt.
10   * See the License for the specific language governing 
11   * permissions and limitations under the License.
12   * 
13   * When distributing Covered Code, include this CDDL 
14   * Header Notice in each file and include the License file 
15   * at glassfish/bootstrap/legal/CDDLv1.0.txt.  
16   * If applicable, add the following below the CDDL Header, 
17   * with the fields enclosed by brackets [] replaced by
18   * you own identifying information: 
19   * "Portions Copyrighted [year] [name of copyright owner]"
20   * 
21   * Copyright 2006 Sun Microsystems, Inc. All rights reserved.
22   */
23  
24  /*
25   * DDLGenerator.java
26   *
27   * Created on Jan 14, 2003
28   */
29  
30  package com.sun.jdo.spi.persistence.generator.database;
31  
32  import java.io.*;
33  import java.util.*;
34  import java.sql.*;
35  
36  import org.netbeans.modules.dbschema.*;
37  
38  // XXX Instead of only static methods, generateDDL could create an
39  // instance of this class with the parameters that are currently passed
40  // around, such as mappingPolicy, being fields.  Other methods then become
41  // instance methods, and don't take all the parameters.
42  
43  /** 
44   * This class generates DDL for a given SchemaElement.
45   *
46   * @author Jie Leng, Dave Bristor
47   */    
48  public class DDLGenerator {
49   
50      /** For writing DDL. */
51      private static final char SPACE = ' '; //NOI18N
52  
53      /** For writing DDL. */
54      private static final char START = '('; //NOI18N
55  
56      /** For writing DDL. */
57      private static final char END = ')'; //NOI18N
58  
59      /** For writing DDL. */
60      private static final String   COLUMN_SEPARATOR = ", "; //NOI18N
61  
62      /** For writing DDL. */
63      private static final String   NOT_NULL_STRING = "NOT NULL"; //NOI18N
64  
65      /** For writing DDL. */
66      private static final String   NULL_STRING = "NULL"; //NOI18N
67  
68  
69      // XXX The use of streams can be improved.  They probably should be
70      // writers anyway, and the names should be consistent.
71      
72      /**
73       * Generate DDL from schema and database vendor name.  Up to four files
74       * containing DDL are created:
75       * <ul>
76       * <li>create table DDL destined for human readers</li>
77       * <li>create table DDL destined for a JDBC connection</li>
78       * <li>drop table DDL destined for human readers</li>
79       * <li>drop table DDL destined for a JDBC connection</li>
80       * </ul>
81       * Furthermore, if the datbase output stream is not null, then create
82       * tables in database.
83       * @param schema Database schema for which DDL is generated.
84       * @param dbVendorName Name of database vendor, which must match one of
85       * the names of the .properties files in this package.
86       * @param createDDLSql An OutputStream to which human-readable DDL for
87       * creating tables gets written.
88       * @param dropDDLSql An OutputStream to which human-readable DDL for
89       * dropping tables gets written.
90       * @param dropDDLJdbc An OutputStream to which DDL for dropping tables
91       * gets written.  This parameter differs from dropDDLSql because the data
92       * written into it is supposed to be used with a JDBC connection.
93       * @param createDDLJdbc An OutputStream to which DDL for creating tables
94       * gets written.  This parameter differs from createDDLSql because the data
95       * written into it is supposed to be used with a JDBC connection.
96       * @param dbStream DatabaseOutputStream which, if not null, is used to
97       * create tables in a live database during this method call.
98       * @param dropAndCreateTbl If true, and dbStream is not null, then
99       * existing tables are dropped before new ones are created.
100      * @throws DBException
101      * @throws SQLException
102      * @throws IOException
103      */
104     // XXX Change param dbStream to be of type DatabaseOutputStream.
105     // XXX Change names of all streams to indicate that they are streams.
106     // XXX Consider changing this API to take the location of where the files
107     // are to be written, and to create the streams here.
108     // XXX Reorder params so they are in order create, drop, create, drop or
109     // maybe create, create, drop, drop, but not create, drop, drop, create!
110     public static void generateDDL(SchemaElement schema, String   dbVendorName, 
111             OutputStream createDDLSql, OutputStream dropDDLSql,
112             OutputStream dropDDLJdbc, OutputStream createDDLJdbc,
113             OutputStream dbStream, boolean dropAndCreateTbl) 
114             throws DBException, SQLException, IOException {
115 
116         if (schema != null) {
117             MappingPolicy mappingPolicy = MappingPolicy.getMappingPolicy(
118                     dbVendorName);
119             DDLTemplateFormatter.reset(mappingPolicy);
120             String   schemaName = schema.getName().getName();
121             List createAllTblDDL = new ArrayList();
122             List alterAddConstraintsDDL = new ArrayList();
123             List alterDropConstraintsDDL = new ArrayList();
124             List dropAllTblDDL = new ArrayList();
125             TableElement[] tables = schema.getTables();
126             
127             if (tables != null) { 
128                 for (int ii = 0; ii < tables.length; ii++) {
129                     TableElement table = tables[ii];
130 
131                     createAllTblDDL.add(
132                             createCreateTableDDL(table, mappingPolicy));
133                     alterAddConstraintsDDL.addAll(
134                             createAddConstraintsDDL(table));
135                     alterDropConstraintsDDL.addAll(
136                             createDropConstraintsDDL(table));
137                     dropAllTblDDL.add(
138                         createDropTableDDL(table));
139                 }
140             }
141             String   stmtSeparator = mappingPolicy.getStatementSeparator();
142             generateSQL(createDDLSql, dropDDLSql, dropDDLJdbc, createDDLJdbc, 
143                 (DatabaseOutputStream) dbStream, createAllTblDDL, 
144                 alterAddConstraintsDDL, alterDropConstraintsDDL, dropAllTblDDL,
145                 stmtSeparator, dropAndCreateTbl);
146         }
147     }
148 
149     /**
150      * Write DDL to files or drop or create table in database 
151      * @param createDDLSql a file for writing create DDL
152      * @param dropDDLSql a file for writing drop DDL
153      * @param dropDDLTxt a file for writing drop DDL and can be easily
154      * executes drop in undeployment time
155      * @param dbStream for creating table in database
156      * @param createAllTblDDL a list of create table statement
157      * @param alterAddConstraintsDDL a list of adding constraints statement
158      * @param alterDropConstraintDDL a list of droping constrains statement
159      * @param dropAllTblDDL a list of droping tables statement
160      * @param stmtSeparator for separating each statement 
161      * @param dropAndCreateTbl true for dropping tables first
162      * @throws DBException
163      * @throws SQLException
164      */
165     // XXX FIXME The above javadoc is wrong, change it if/when the
166     // generateDDL api changes.
167     // XXX Fix method body comments.
168     private static void generateSQL(OutputStream createSql,
169             OutputStream dropSql, OutputStream dropTxt, OutputStream createTxt,
170             DatabaseOutputStream dbStream, List createAllTblDDL,
171             List alterAddConstraintsDDL, List alterDropConstraintsDDL,
172             List dropAllTblDDL, String   stmtSeparator, boolean dropAndCreateTbl)
173             throws DBException, SQLException {
174 
175         PrintStream workStream = null;
176         PrintStream txtStream = null;
177 
178         try {
179             // drop constraints first 
180             workStream = new PrintStream(dropSql);
181             if (dropTxt != null) {
182                 txtStream = new PrintStream(dropTxt);
183             }
184             for (int i = 0; i < alterDropConstraintsDDL.size(); i++) {
185                 String   dropConstStmt = (String  ) alterDropConstraintsDDL.get(i);
186                 writeDDL(workStream, txtStream, stmtSeparator, dropConstStmt);
187                 if ((dbStream != null) && dropAndCreateTbl) {
188                     dbStream.write(dropConstStmt);
189                 }
190             }
191 
192             // drop tables
193             for (int i = 0; i < dropAllTblDDL.size(); i++) {
194                 String   dropTblStmt = (String  ) dropAllTblDDL.get(i);
195                 writeDDL(workStream, txtStream, stmtSeparator, dropTblStmt);
196                 if ((dbStream != null) && dropAndCreateTbl) {
197                     dbStream.write(dropTblStmt);
198                 }
199             }
200             workStream.close();
201             if (txtStream != null) {
202                 txtStream.close();
203             }
204 
205             // create tables and constraints
206             workStream = new PrintStream(createSql);
207             if (createTxt != null) {
208                 txtStream = new PrintStream(createTxt);
209             }
210 
211             // First create tables...
212             for (int i = 0; i < createAllTblDDL.size(); i++) {
213                 StringBuffer   createStmt = new StringBuffer  ();
214                 String  [] createTbl = (String   []) createAllTblDDL.get(i);
215 
216                 for (int j = 0; j < createTbl.length; j++) {
217                     workStream.println(createTbl[j]);
218                     createStmt.append(createTbl[j]);
219                 }
220                 workStream.println(stmtSeparator);
221                 if (txtStream != null) {
222                     txtStream.println(createStmt);
223                 }
224                 if (dbStream != null) {
225                    dbStream.write(createStmt.toString());
226                 }
227             }
228 
229             // ...then create constraints
230             for (int i = 0; i < alterAddConstraintsDDL.size(); i++) {
231                 String   stmt = (String  )alterAddConstraintsDDL.get(i);
232                 writeDDL(workStream, txtStream, stmtSeparator, stmt);
233                 if (dbStream != null) {
234                     dbStream.write(stmt);
235                 }
236             }
237             
238             workStream.close();
239             if (txtStream != null) {
240                 txtStream.close();
241             }
242 
243             if (dbStream != null) {
244                 dbStream.flush();
245             }
246         } finally {
247             if (workStream != null) {
248                 workStream.close();
249             }
250             if (txtStream != null) {
251                 txtStream.close();
252             }
253         }
254     }
255 
256     /**
257      * Writes string to given streams.
258      * @param sqlFile Stream representing human-readable DDL.
259      * @param txtFile Stream representing DDL to be used via JDBC
260      * statements.
261      * @param stmtSeparator Separator between DDL statements in
262      * human-readable DDL.
263      */
264     private static void writeDDL(PrintStream sql, PrintStream txt, 
265             String   stmtSeparator, String   stmt) {
266 
267         sql.println(stmt);
268         sql.println(stmtSeparator);
269 
270         // Could be null, if null was passed to generateDDL.
271         if (txt != null) {
272             txt.println(stmt);
273         }
274     } 
275 
276     // XXX Some methods below use oneParam, etc.  These should be renamed
277     // "formatterParam" or somesuch, leaving out their number.
278 
279     /**
280      * Returns DDL in String[] form to create a table.  The returned String[]
281      * elements have the format:
282      * <pre>
283      * CREATE TABLE table_name (
284      *     column_name type(precision, scale) null,
285      *     ...
286      *     CONSTRAINT pk_name PRIMARY KEY (id, name)
287      * )
288      * </pre>
289      * @param table Table for which DDL is to be created.
290      * @param mappingPolicy Policy that determines database- and
291      * user-supplied properties on generating DDL.
292      * @return String[] containing DDL to create a table.
293      */
294     private static String  [] createCreateTableDDL(TableElement table, 
295             MappingPolicy mappingPolicy) {
296 
297         List createTblList = new ArrayList();
298         String  [] oneParam = { table.getName().getName() };
299 
300         createTblList.add(
301                 DDLTemplateFormatter.formatCreateTable(oneParam));
302         
303     // add columns for each table
304         ColumnElement[] columns = table.getColumns();
305         String   constraint = createPrimaryKeyConstraint(table);
306         int size = columns.length;
307         
308     for (int i = 0; i < size; i++) {
309             StringBuffer   columnContent = new StringBuffer  ();
310             columnContent.append(getColumnDef(columns[i], mappingPolicy));
311 
312             // If we haven't added the last column, or we have but there's a
313             // constraint yet to be added, add a column separator.
314             if ((i < size - 1) || ((i == size - 1) && (constraint != null))) {
315             columnContent.append(COLUMN_SEPARATOR);
316             }
317             createTblList.add(columnContent.toString());
318     }
319 
320         if (constraint != null) {
321             createTblList.add(constraint);
322         }
323         createTblList.add(mappingPolicy.getCreateTableEnd());
324 
325         return (String  []) createTblList.toArray(
326                 new String  [createTblList.size()]);
327     }
328 
329     /**
330      * Returns DDL in String form to drop a table.  The returned string has
331      * the format:
332      * <pre>
333      * DROP TABLE table_name
334      * </pre>
335      * @param table Table for which dropping DDL is returned.
336      * @return DDL to drop a table.
337      */
338     private static String   createDropTableDDL(TableElement table) {
339         String  [] oneParam = { table.getName().getName() };
340         return DDLTemplateFormatter.formatDropTable(oneParam);
341     }
342 
343     /**
344      * Returns DDL in String form to create a primary key constraint.  The
345      * string has the format:
346      * <pre>
347      * CONSTRAINT pk_name PRIMARY KEY(id, name)
348      * </pre>
349      * @param table Table for which constraint DDL is returned.
350      * @return DDL to create a PK constraint or null if there is no PK.
351      */
352     private static String   createPrimaryKeyConstraint(TableElement table) {
353         String   rc = null;
354         UniqueKeyElement pk = table.getPrimaryKey();
355         
356         if (pk != null) {
357             String  [] twoParams = new String  [2];
358             twoParams[0] = pk.getName().getName();
359             twoParams[1] = getColumnNames(pk.getColumns());
360             rc = DDLTemplateFormatter.formatPKConstraint(twoParams);
361         }
362         return rc;
363     }
364 
365     /**
366      * Returns a List of Strings of "add constraint" DDL commands.  Strings
367      * in the array have the format:
368      * <pre>
369      * ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY
370      * (id, ..) REFERENCES table_name (id, ..)
371      * </pre>
372      * @param table Table for which constraints are returned.
373      * @return Array of constraints (as Strings), one for each constraint on
374      * the table.
375      */
376     private static List createAddConstraintsDDL(TableElement table) {
377         List alterTblDDL = new ArrayList();
378         String  [] fourParams = new String  [4];
379         String  [] oneParam = { table.getName().getName() };
380 
381         // Add foreign keys
382         ForeignKeyElement[] fkeys = table.getForeignKeys();
383         if (fkeys != null) {
384             String   alterTblString =
385                 DDLTemplateFormatter.formatAlterTableAddConstraint(oneParam);
386             
387             for (int jj=0; jj < fkeys.length; jj++) {
388                 ForeignKeyElement fkey = fkeys[jj];
389                 fourParams[0] = fkey.getName().getName();
390                 fourParams[1] = getColumnNames(fkey.getLocalColumns());
391                 fourParams[2] = fkey.getReferencedTable().getName().getName();
392                 fourParams[3] = getColumnNames(fkey.getReferencedColumns());
393 
394                 StringBuffer   alterTblDDLString = new StringBuffer  (
395                     alterTblString);
396                 alterTblDDLString.append(SPACE);
397                 alterTblDDLString.append(
398                         DDLTemplateFormatter.formatFKConstraint(fourParams));
399                 alterTblDDL.add(alterTblDDLString.toString());
400             }
401         }
402         return alterTblDDL;
403     }
404 
405     /** Returns a List of Strings of "drop constraint" DDL commands.  Each
406      *  String has the format:
407      * <pre>
408      * DROP TABLE table_name
409      * </pre>
410      * @param table Table for which constraints are to be dropped.
411      * @return List of Strings, one per "drop constraint" command.
412      */
413     private static List createDropConstraintsDDL(TableElement table) {
414         List alterTbls = new ArrayList();
415         String  [] twoParams = new String  [2];
416         twoParams[0] = table.getName().getName();
417         ForeignKeyElement[] fkeys = table.getForeignKeys();
418 
419         if (fkeys != null) {
420             for (int i = 0; i < fkeys.length; i++) {
421                 twoParams[1] = fkeys[i].getName().getName();
422                 String   alterTblString =
423                     DDLTemplateFormatter.formatAlterTableDropConstraint(
424                             twoParams);
425                 alterTbls.add(alterTblString);
426             }
427         }
428         return alterTbls;
429     }
430 
431     /**
432      * Returns a String which indicates how a column should be declared in
433      * DDL.  It has the format:
434      * <pre>
435      * column_name column_type(length, precision, scale) null
436      * </pre>
437      * length, precision, scale, and null are optional, and depend on
438      * properties of the given column.
439      * @param column Column to be declared in DDL.
440      * @param mappingPolicy Policy that determines database- and
441      * user-supplied properties on generating DDL.
442      * @return String containing DDL to create column in database.
443      */
444     private static String   getColumnDef(ColumnElement column,
445             MappingPolicy mappingPolicy) {
446 
447         int jdbcType = column.getType();
448         Integer   scale = column.getScale();
449         Integer   precision = column.getPrecision();
450         Integer   length = column.getLength();
451         String   sqlType = mappingPolicy.getSQLTypeName(jdbcType);
452         StringBuffer   columnContent = new StringBuffer  ();
453         
454         columnContent.append(column.getName().getName());
455         columnContent.append(SPACE);
456         columnContent.append(sqlType);
457         if (column.isCharacterType() && length != null) {
458             columnContent.append(START);
459             columnContent.append(length.toString());
460             columnContent.append(END);
461 
462         } else if (column.isNumericType() && precision != null) {
463             columnContent.append(START);
464             columnContent.append(precision.toString());
465             if (scale != null) {
466                 columnContent.append(COLUMN_SEPARATOR);
467                 columnContent.append(scale.toString());
468             }
469             columnContent.append(END);
470 
471         } else if (column.isBlobType() && length != null) {
472             columnContent.append(START);
473             columnContent.append(length.toString());
474             columnContent.append(END);
475         }
476             
477         // Add extra information required by LOB columns.
478         if (jdbcType == Types.BLOB || jdbcType == Types.CLOB) {
479             String   lobText = mappingPolicy.getLobLogging();
480             if (lobText != null && lobText.length() > 0) {
481                 columnContent.append(SPACE).append(lobText);
482             }
483         }
484 
485         // Add information about nullability.
486         if (column.isNullable()) {
487             String   nullText =  mappingPolicy.getColumnNullability();
488             if (nullText != null && nullText.length() > 0) {
489                 columnContent.append(SPACE).append(nullText);
490             }
491         } else {
492             columnContent.append(SPACE).append(NOT_NULL_STRING);
493         }
494 
495         return columnContent.toString();
496     }
497 
498     /**
499      * Returns a comma-separated list of column names.
500      * @param columns Array of ColumnElements
501      * @return String of form "columnA, columnB", with as many named columns
502      * as there are in given array.
503      */
504     private static String   getColumnNames(ColumnElement[] columns) {
505         StringBuffer   columnNames = new StringBuffer  ();
506         for (int i = 0; i < columns.length; i++) {
507             if (i > 0) {
508                 columnNames.append(COLUMN_SEPARATOR);
509             }
510             columnNames.append(columns[i].getName().getName());
511         }
512         return columnNames.toString();
513     }
514 }
515
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags