commons-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From to...@apache.org
Subject cvs commit: jakarta-commons-sandbox/sql/src/test/org/apache/commons/sql/task TestDDLTask.java
Date Thu, 14 Oct 2004 19:42:48 GMT
tomdz       2004/10/14 12:42:48

  Modified:    sql/src/java/org/apache/commons/sql/builder SqlBuilder.java
                        MSSqlBuilder.java FirebirdBuilder.java
               sql/src/java/org/apache/commons/sql/model Table.betwixt
                        Reference.java Table.java ForeignKey.java
                        UniqueColumn.java Unique.java Index.java
               sql/src/java/org/apache/commons/sql/task
                        JdbcToSchemaTask.java DDLTask.java
               sql/src/java/org/apache/commons/sql/util DDLExecutor.java
               sql/src/java/org/apache/commons/sql/io JdbcModelReader.java
               sql/src/test/org/apache/commons/sql/task TestDDLTask.java
  Log:
  Applied patch by John Marshall
  
  Revision  Changes    Path
  1.24      +60 -18    jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/SqlBuilder.java
  
  Index: SqlBuilder.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/SqlBuilder.java,v
  retrieving revision 1.23
  retrieving revision 1.24
  diff -u -r1.23 -r1.24
  --- SqlBuilder.java	14 Aug 2004 19:51:35 -0000	1.23
  +++ SqlBuilder.java	14 Oct 2004 19:42:48 -0000	1.24
  @@ -588,11 +588,21 @@
                       }
                   }
   
  -                // TODO: add constraints here...
  +                // add fk constraints
  +                for (Iterator fkIt = desiredTable.getForeignKeys().iterator(); fkIt.hasNext();)
{
  +                    ForeignKey desiredFk = (ForeignKey) fkIt.next();
  +                    ForeignKey currentFk = currentTable.findForeignKey(desiredFk);
  +                    if ( currentFk == null ) {
  +                        if (_log.isInfoEnabled())
  +                        {
  +                            _log.info("Creating foreign key " + desiredTable.getName()
+ "." + desiredFk);
  +                        }
  +                        writeExternalForeignKeyCreateStmt(desiredDb, desiredTable, desiredFk);
  +                    }
  +                }
   
  -                // Hmm, m-w.com says indices and indexes are both okay
                   // TODO: should we check the index fields for differences?
  -
  +                //create new indexes
                   for (Iterator indexIt = desiredTable.getIndexes().iterator(); indexIt.hasNext();)
                   {
                       Index desiredIndex = (Index)indexIt.next();
  @@ -608,8 +618,24 @@
                       }
                   }
   
  -                // TODO: drop constraints - probably need names on them for this
  +                // drop fk constraints
  +                for (Iterator fkIt = currentTable.getForeignKeys().iterator(); fkIt.hasNext();)
{
  +                    ForeignKey currentFk = (ForeignKey) fkIt.next();
  +                    ForeignKey desiredFk = desiredTable.findForeignKey(currentFk);
   
  +                    if ( desiredFk == null ) {
  +                        if (_log.isInfoEnabled())
  +                        {
  +                            _log.info((doDrops ? "" : "Not ") + "Dropping foreign key "
+ currentTable.getName() + "." + currentFk);
  +                        }
  +                        if ( doDrops ) {
  +                            writeExternalForeignKeyDropStmt(currentTable, currentFk);
  +                        }
  +                    }
  +                }
  +
  +
  +                //Drop columns
                   for (Iterator columnIt = currentTable.getColumns().iterator(); columnIt.hasNext();)
                   {
                       Column currentColumn = (Column)columnIt.next();
  @@ -638,6 +664,7 @@
                       }
                   }
   
  +                //Drop indexes
                   for (Iterator indexIt = currentTable.getIndexes().iterator(); indexIt.hasNext();)
                   {
                       Index currentIndex = (Index)indexIt.next();
  @@ -653,7 +680,7 @@
                               IndexColumn indexColumn = (IndexColumn)columnIt.next();
                               Column      column      = currentTable.findColumn(indexColumn.getName());
   
  -                            if (!column.isPrimaryKey())
  +                            if (column != null && !column.isPrimaryKey())
                               {
                                   isPk = false;
                                   break;
  @@ -663,9 +690,12 @@
                           {
                               if (_log.isInfoEnabled())
                               {
  -                                _log.info("Dropping non-primary index " + currentTable.getName()
+ "." + currentIndex.getName());
  +                                _log.info((doDrops ? "" : "Not ") + "Dropping non-primary
index " + currentTable.getName() + "." + currentIndex.getName());
  +                            }
  +                            if ( doDrops )
  +                            {
  +                                writeExternalIndexDropStmt(currentTable, currentIndex);
                               }
  -                            writeExternalIndexDropStmt(currentTable, currentIndex);
                           }
                       }
                   }
  @@ -674,6 +704,7 @@
           }
   
           // generating deferred foreignkeys
  +        //TODO should we try to generate new FKs on existing tables?
           for (Iterator fkIt = newTables.iterator(); fkIt.hasNext();)
           {
               createExternalForeignKeys(desiredDb, (Table)fkIt.next());
  @@ -793,7 +824,7 @@
   
               for (Iterator it = table.getForeignKeys().iterator(); it.hasNext(); numKey++)
               {
  -                writeExternalForeignKeyCreateStmt(database, table, (ForeignKey)it.next(),
numKey);
  +                writeExternalForeignKeyCreateStmt(database, table, (ForeignKey)it.next());
               }
           }
       }
  @@ -848,7 +879,7 @@
   
               for (Iterator it = table.getForeignKeys().iterator(); it.hasNext(); numKey++)
               {
  -                writeExternalForeignKeyDropStmt(table, (ForeignKey)it.next(), numKey);
  +                writeExternalForeignKeyDropStmt(table, (ForeignKey)it.next());
               }
           }
       }
  @@ -1146,7 +1177,7 @@
   
           if (column.getDefaultValue() != null)
           {
  -            print(" DEFAULT '");
  +            print(" DEFAULT ");
               print(_valueQuoteChar);
               print(column.getDefaultValue());
               print(_valueQuoteChar);
  @@ -1366,10 +1397,10 @@
   
           if ((columnA.getTypeCode() != columnB.getTypeCode()) ||
               (columnA.isRequired() != columnB.isRequired()) ||
  -            (sizeMatters && (columnA.getSize() != columnB.getSize())) ||
  +            (sizeMatters && (!columnA.getSize().equals(columnB.getSize()))) ||
  +            !defaultsEqual /*|| //determined these two to be hardly useful
               (columnA.getScale() != columnB.getScale()) ||
  -            !defaultsEqual ||
  -            (columnA.getPrecisionRadix() != columnB.getPrecisionRadix()))
  +            (columnA.getPrecisionRadix() != columnB.getPrecisionRadix())*/ )
           {
               return true;
           }
  @@ -1379,6 +1410,12 @@
           }
       }
   
  +    protected String getForeignKeyName(ForeignKey fk) {
  +        //table and local column should be sufficient - is it possible for one
  +        //column to reference multiple tables
  +        return fk.firstReference().getLocal() + "_" + fk.getForeignTable();
  +    }
  +
       /**
        * Returns the constraint name. This method takes care of length limitations imposed
by some databases.
        * 
  @@ -1551,7 +1588,12 @@
               {
                   IndexColumn idxColumn = (IndexColumn)it.next();
   
  -                print(getColumnName(table.findColumn(idxColumn.getName())));
  +                Column col = table.findColumn(idxColumn.getName());
  +                if ( col == null ) {
  +                    //would get null pointer on next line anyway, so throw exception
  +                    throw new RuntimeException("Invalid column '" + idxColumn.getName()
+ "' on index " + index.getName() + " for table " + table.getName());
  +                }
  +                print(getColumnName(col));
                   if (it.hasNext())
                   {
                       print(", ");
  @@ -1634,7 +1676,7 @@
        * @param key      The foreign key
        * @param numKey   The number of the key, starting with 1
        */
  -    protected void writeExternalForeignKeyCreateStmt(Database database, Table table, ForeignKey
key, int numKey) throws IOException
  +    protected void writeExternalForeignKeyCreateStmt(Database database, Table table, ForeignKey
key) throws IOException
       {
           if (key.getForeignTable() == null)
           {
  @@ -1645,7 +1687,7 @@
               writeTableAlterStmt(table);
   
               print("ADD CONSTRAINT ");
  -            print(getConstraintName(null, table, "FK", Integer.toString(numKey)));
  +            print(getConstraintName(null, table, "FK", getForeignKeyName(key)));
               print(" FOREIGN KEY (");
               writeLocalReferences(key);
               print(") REFERENCES ");
  @@ -1699,11 +1741,11 @@
        * @param key    The foreign key
        * @param numKey The number of the key, starting with 1
        */
  -    protected void writeExternalForeignKeyDropStmt(Table table, ForeignKey foreignKey,
int numKey) throws IOException
  +    protected void writeExternalForeignKeyDropStmt(Table table, ForeignKey foreignKey)
throws IOException
       {
           writeTableAlterStmt(table);
           print("DROP CONSTRAINT ");
  -        print(getConstraintName(null, table, "FK", Integer.toString(numKey)));
  +        print(getConstraintName(null, table, "FK", getForeignKeyName(foreignKey)));
           printEndOfStatement();
       }
   
  
  
  
  1.11      +28 -2     jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/MSSqlBuilder.java
  
  Index: MSSqlBuilder.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/MSSqlBuilder.java,v
  retrieving revision 1.10
  retrieving revision 1.11
  diff -u -r1.10 -r1.11
  --- MSSqlBuilder.java	14 Aug 2004 19:51:35 -0000	1.10
  +++ MSSqlBuilder.java	14 Oct 2004 19:42:48 -0000	1.11
  @@ -21,6 +21,7 @@
   
   import org.apache.commons.sql.model.Column;
   import org.apache.commons.sql.model.ForeignKey;
  +import org.apache.commons.sql.model.Index;
   import org.apache.commons.sql.model.Table;
   
   /**
  @@ -36,7 +37,7 @@
       {
           setEmbeddedForeignKeysNamed(true);
           setForeignKeysEmbedded(false);
  -        setCommentPrefix("#");
  +        //setCommentPrefix("#");
           addNativeTypeMapping(Types.BLOB,          "IMAGE");
           addNativeTypeMapping(Types.BOOLEAN,       "BIT");
           addNativeTypeMapping(Types.CHAR,          "NCHAR");
  @@ -48,7 +49,7 @@
           addNativeTypeMapping(Types.LONGVARCHAR,   "TEXT");
           addNativeTypeMapping(Types.TIME,          "DATETIME");
           addNativeTypeMapping(Types.TIMESTAMP,     "DATETIME");
  -        addNativeTypeMapping(Types.VARCHAR,       "NVARCHAR");
  +        addNativeTypeMapping(Types.VARCHAR,       "VARCHAR");
       }
   
       /* (non-Javadoc)
  @@ -119,5 +120,30 @@
       protected void writeColumnAutoIncrementStmt(Table table, Column column) throws IOException
       {
           print("IDENTITY (1,1) ");
  +    }
  +
  +    protected boolean shouldGeneratePrimaryKeys(java.util.List primaryKeyColumns) {
  +        /*
  +         * requires primary key indication for autoincrement key columns
  +         * I'm not sure why the default skips the pk statement if all are identity
  +         */
  +        return primaryKeyColumns.size() > 0;
  +    }
  +
  +    public void writeExternalIndexDropStmt(Table table, Index index) throws IOException
  +    {
  +        print("DROP INDEX ");
  +        print( getTableName(table) );
  +        print( "." );
  +        print( getIndexName(index) );
  +        printEndOfStatement();
  +    }
  +
  +    public void writeColumnAlterStmt(Table table, Column column, boolean isNewColumn) throws
IOException
  +    {
  +        writeTableAlterStmt(table);
  +        print(isNewColumn ? "ADD " : "ALTER COLUMN ");
  +        writeColumn(table, column);
  +        printEndOfStatement();
       }
   }
  
  
  
  1.3       +3 -3      jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/FirebirdBuilder.java
  
  Index: FirebirdBuilder.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/builder/FirebirdBuilder.java,v
  retrieving revision 1.2
  retrieving revision 1.3
  diff -u -r1.2 -r1.3
  --- FirebirdBuilder.java	14 Aug 2004 19:51:35 -0000	1.2
  +++ FirebirdBuilder.java	14 Oct 2004 19:42:48 -0000	1.3
  @@ -79,11 +79,11 @@
       }
   
       /* (non-Javadoc)
  -     * @see org.apache.commons.sql.builder.SqlBuilder#writeExternalForeignKeyCreateStmt(org.apache.commons.sql.model.Table,
org.apache.commons.sql.model.ForeignKey, int)
  +     * @see org.apache.commons.sql.builder.SqlBuilder#writeExternalForeignKeyCreateStmt(org.apache.commons.sql.model.Table,
org.apache.commons.sql.model.ForeignKey)
        */
  -    protected void writeExternalForeignKeyCreateStmt(Database database, Table table, ForeignKey
key, int numKey) throws IOException
  +    protected void writeExternalForeignKeyCreateStmt(Database database, Table table, ForeignKey
key) throws IOException
       {
  -        super.writeExternalForeignKeyCreateStmt(database, table, key, numKey);
  +        super.writeExternalForeignKeyCreateStmt(database, table, key);
           if (key.getForeignTable() != null)
           {
               print("COMMIT");
  
  
  
  1.5       +10 -8     jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Table.betwixt
  
  Index: Table.betwixt
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Table.betwixt,v
  retrieving revision 1.4
  retrieving revision 1.5
  diff -u -r1.4 -r1.5
  --- Table.betwixt	26 Jul 2004 22:15:39 -0000	1.4
  +++ Table.betwixt	14 Oct 2004 19:42:48 -0000	1.5
  @@ -1,8 +1,10 @@
  -<?xml version="1.0" encoding="UTF-8" ?>
  -<info>
  -  <element name="table">
  -    <hide property="primaryKeyColumns"/>
  -    <hide property="autoIncrementColumn"/>
  -    <addDefaults/>
  -  </element>
  -</info>
  +<?xml version="1.0" encoding="UTF-8" ?>
  +<info>
  +  <element name="table">
  +    <hide property="primaryKeyColumns"/>
  +    <hide property="autoIncrementColumn"/>
  +    <hide property="unique"/>
  +    <hide property="uniques"/>
  +    <addDefaults/>
  +  </element>
  +</info>
  
  
  
  1.6       +26 -1     jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Reference.java
  
  Index: Reference.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Reference.java,v
  retrieving revision 1.5
  retrieving revision 1.6
  diff -u -r1.5 -r1.6
  --- Reference.java	19 Jul 2004 22:14:00 -0000	1.5
  +++ Reference.java	14 Oct 2004 19:42:48 -0000	1.6
  @@ -16,7 +16,7 @@
   package org.apache.commons.sql.model;
   
   
  -public class Reference implements Cloneable
  +public class Reference implements Cloneable, Comparable
   {
       private String local;
       private String foreign;
  @@ -53,5 +53,30 @@
       public void setForeign(String foreign)
       {
           this.foreign = foreign;
  +    }
  +    
  +    public boolean equals(Object o) {
  +        boolean result = o != null && getClass().equals(o.getClass());
  +        if ( result ) {
  +            Reference ref = (Reference) o;
  +            result = this.local.equalsIgnoreCase(ref.local) && this.foreign.equalsIgnoreCase(ref.foreign);
  +        }
  +        return result;
  +    }
  +
  +    /* (non-Javadoc)
  +     * @see java.lang.Comparable#compareTo(java.lang.Object)
  +     */
  +    public int compareTo(Object o) {
  +        Reference ref = (Reference) o;
  +        int result = this.local.compareTo(ref.local);
  +        if ( result == 0 ) {
  +            result = this.foreign.compareTo(ref.foreign);
  +        }
  +        return result;
  +    }
  +    
  +    public String toString() {
  +        return "Reference[" + this.local + " to " + this.foreign + "]";
       }
   }
  
  
  
  1.15      +23 -0     jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Table.java
  
  Index: Table.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Table.java,v
  retrieving revision 1.14
  retrieving revision 1.15
  diff -u -r1.14 -r1.15
  --- Table.java	19 Jul 2004 22:14:00 -0000	1.14
  +++ Table.java	14 Oct 2004 19:42:48 -0000	1.15
  @@ -59,6 +59,11 @@
       {
       }
   
  +    public String toString()
  +    {
  +        return super.toString() + "[name=" + name + "]";
  +    }
  +
       /* (non-Javadoc)
        * @see java.lang.Object#clone()
        */
  @@ -280,6 +285,24 @@
               }
           }
           return null;
  +    }
  +
  +    /**
  +     * Finds a ForeignKey within the table that matches the supplied ForeignKey
  +     * exactly, meaning the foreign table and all column references are the same.
  +     */
  +    public ForeignKey findForeignKey(ForeignKey key)
  +    {
  +        ForeignKey result = null;
  +        for (Iterator iter = getForeignKeys().iterator(); iter.hasNext(); )
  +        {
  +            ForeignKey fk = (ForeignKey) iter.next();
  +            if ( fk.equals(key) ) {
  +                result = key;
  +                break;
  +            }
  +        }
  +        return result;
       }
   
       /**
  
  
  
  1.8       +27 -0     jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/ForeignKey.java
  
  Index: ForeignKey.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/ForeignKey.java,v
  retrieving revision 1.7
  retrieving revision 1.8
  diff -u -r1.7 -r1.8
  --- ForeignKey.java	25 Jul 2004 15:08:29 -0000	1.7
  +++ ForeignKey.java	14 Oct 2004 19:42:48 -0000	1.8
  @@ -16,6 +16,7 @@
   package org.apache.commons.sql.model;
   
   import java.util.ArrayList;
  +import java.util.Collections;
   import java.util.List;
   
   // TODO: Add a name property to the foreignkey that is respected by
  @@ -58,5 +59,31 @@
       public List getReferences()
       {
           return references;
  +    }
  +
  +    public Reference firstReference() {
  +        return (Reference) (references.size() == 0 ? null : references.get(0));
  +    }
  +
  +    public boolean equals(Object o) {
  +        boolean result = o != null && getClass().equals(o.getClass());
  +        if ( result ) {
  +            ForeignKey fk = (ForeignKey) o;
  +            result = this.foreignTable.equalsIgnoreCase(fk.foreignTable) && this.references.size()
== fk.references.size();
  +            if ( result ) {
  +                //check all references - need to ensure order is same for valid comparison
  +                List copyThis = (List) this.references.clone();
  +                List copyThat = (List) fk.references.clone();
  +                Collections.sort(copyThis);
  +                Collections.sort(copyThat);
  +                result = copyThis.equals(copyThat);
  +            }
  +        }
  +        return result;
  +    }
  +    
  +    public String toString() {
  +        return "ForeignKey[" + this.foreignTable + "]";
  +        //TODO show references
       }
   }
  
  
  
  1.4       +2 -2      jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/UniqueColumn.java
  
  Index: UniqueColumn.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/UniqueColumn.java,v
  retrieving revision 1.3
  retrieving revision 1.4
  diff -u -r1.3 -r1.4
  --- UniqueColumn.java	19 Jul 2004 22:14:00 -0000	1.3
  +++ UniqueColumn.java	14 Oct 2004 19:42:48 -0000	1.4
  @@ -20,7 +20,7 @@
    * &lt;unique&gt; tags, but adds no functionality.  All indexes are treated the
    * same by the Table.
    * 
  - * @author <a href="mailto:jmarshall@connectria.com">John Marshall</a>
  + * @author John Marshall/Connectria
    * @version $Revision$
    */
   public class UniqueColumn extends IndexColumn
  
  
  
  1.5       +2 -2      jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Unique.java
  
  Index: Unique.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Unique.java,v
  retrieving revision 1.4
  retrieving revision 1.5
  diff -u -r1.4 -r1.5
  --- Unique.java	19 Jul 2004 22:14:00 -0000	1.4
  +++ Unique.java	14 Oct 2004 19:42:48 -0000	1.5
  @@ -23,7 +23,7 @@
    * &lt;unique&gt; tags, but adds no functionality.  All indexes are treated the
    * same by the Table.
    * 
  - * @author <a href="mailto:jmarshall@connectria.com">John Marshall</a>
  + * @author John Marshall/Connectria
    * @version $Revision$
    */
   public class Unique extends Index
  
  
  
  1.7       +2 -0      jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Index.java
  
  Index: Index.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/model/Index.java,v
  retrieving revision 1.6
  retrieving revision 1.7
  diff -u -r1.6 -r1.7
  --- Index.java	19 Jul 2004 22:14:00 -0000	1.6
  +++ Index.java	14 Oct 2004 19:42:48 -0000	1.7
  @@ -13,6 +13,7 @@
    * See the License for the specific language governing permissions and
    * limitations under the License.
    */
  +
   package org.apache.commons.sql.model;
   
   import java.util.ArrayList;
  @@ -46,6 +47,7 @@
       
       public void setName(String name)
       {
  +        if ( name == null ) throw new IllegalArgumentException("Null index name");
           this.name = name;
       }
       
  
  
  
  1.5       +5 -92     jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/task/JdbcToSchemaTask.java
  
  Index: JdbcToSchemaTask.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/task/JdbcToSchemaTask.java,v
  retrieving revision 1.4
  retrieving revision 1.5
  diff -u -r1.4 -r1.5
  --- JdbcToSchemaTask.java	28 Feb 2004 03:35:48 -0000	1.4
  +++ JdbcToSchemaTask.java	14 Oct 2004 19:42:48 -0000	1.5
  @@ -1,5 +1,5 @@
   /*
  - * Copyright 1999-2002,2004 The Apache Software Foundation.
  + * Copyright 1999-2004 The Apache Software Foundation.
    * 
    * Licensed under the Apache License, Version 2.0 (the "License");
    * you may not use this file except in compliance with the License.
  @@ -17,15 +17,11 @@
   package org.apache.commons.sql.task;
   
   import java.io.FileWriter;
  -import java.sql.Connection;
  -import java.sql.DriverManager;
   import java.util.Hashtable;
   
   import org.apache.commons.sql.io.DatabaseWriter;
  -import org.apache.commons.sql.io.JdbcModelReader;
   import org.apache.commons.sql.model.Database;
   import org.apache.tools.ant.BuildException;
  -import org.apache.tools.ant.Task;
   
   /**
    * This class generates an XML schema of an existing database from
  @@ -34,29 +30,11 @@
    * @author <a href="mailto:drfish@cox.net">J. Russell Smyth</a>
    * @version $Id$
    */
  -public class JdbcToSchemaTask extends Task
  +public class JdbcToSchemaTask extends DatabaseTask
   {
       /** Name of database schema file produced. */
       protected String outputFile;
   
  -    /** JDBC URL. */
  -    protected String dbUrl;
  -
  -    /** JDBC driver. */
  -    protected String dbDriver;
  -
  -    /** JDBC user name. */
  -    protected String dbUser;
  -
  -    /** JDBC password. */
  -    protected String dbPassword;
  -
  -    /** DB catalog to use. */
  -    protected String dbCatalog;
  -
  -    /** DB schema to use. */
  -    protected String dbSchema;
  -
       /** Hashtable of columns that have primary keys. */
       protected Hashtable primaryKeys;
   
  @@ -66,41 +44,6 @@
       protected boolean useTypeNames = false;
   
       
  -    public String getDbSchema()
  -    {
  -        return dbSchema;
  -    }
  -
  -    public void setDbCatalog(String dbCatalog)
  -    {
  -        this.dbCatalog = dbCatalog;
  -    }
  -
  -    public void setDbSchema(String dbSchema)
  -    {
  -        this.dbSchema = dbSchema;
  -    }
  -
  -    public void setDbUrl(String v)
  -    {
  -        dbUrl = v;
  -    }
  -
  -    public void setDbDriver(String v)
  -    {
  -        dbDriver = v;
  -    }
  -
  -    public void setDbUser(String v)
  -    {
  -        dbUser = v;
  -    }
  -
  -    public void setDbPassword(String v)
  -    {
  -        dbPassword = v;
  -    }
  -
       public void setOutputFile (String v)
       {
           outputFile = v;
  @@ -117,16 +60,11 @@
       public void execute() throws BuildException
       {
           System.err.println("Commons-Sql JdbcToSchema starting\n");
  -        System.err.println("Your DB settings are:");
  -        System.err.println("driver : " + dbDriver);
  -        System.err.println("URL : " + dbUrl);
  -        System.err.println("user : " + dbUser);
  -        System.err.println("password : " + dbPassword);
  -        System.err.println("schema : " + dbSchema);
  -
  +        printDbSettings();
  +        
           Database db = null;
           try{
  -            db = getDbFromJdbc(); 
  +            db = getDbFromConnection( getDataSource().getConnection() ); 
               DatabaseWriter w = new DatabaseWriter(new FileWriter(outputFile));
              // w.setWriteIDs(true);
               w.write(db);
  @@ -136,29 +74,4 @@
           }
       }
   
  -    /**
  -     */
  -    public Database getDbFromJdbc() throws Exception
  -    {
  -        // Load the database Driver.
  -        Class.forName(dbDriver);
  -        System.err.println("DB driver sucessfuly instantiated");
  -
  -        // Attemtp to connect to a database.
  -        Connection con = DriverManager.getConnection(dbUrl,
  -                                                     dbUser,
  -                                                     dbPassword);
  -        System.err.println("DB connection established");
  -
  -        JdbcModelReader reader = new JdbcModelReader(con);
  -        if ( dbCatalog!=null ) {
  -            reader.setCatalog(dbCatalog);
  -        }
  -        if ( dbSchema!=null ) {
  -            reader.setSchema(dbSchema);
  -        }
  -
  -        Database db = reader.getDatabase();
  -        return db;
  -    } 
   }
  
  
  
  1.5       +237 -34   jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/task/DDLTask.java
  
  Index: DDLTask.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/task/DDLTask.java,v
  retrieving revision 1.4
  retrieving revision 1.5
  diff -u -r1.4 -r1.5
  --- DDLTask.java	28 Feb 2004 03:35:48 -0000	1.4
  +++ DDLTask.java	14 Oct 2004 19:42:48 -0000	1.5
  @@ -1,5 +1,5 @@
   /*
  - * Copyright 2001-2004 The Apache Software Foundation.
  + * Copyright 1999-2004 The Apache Software Foundation.
    * 
    * Licensed under the Apache License, Version 2.0 (the "License");
    * you may not use this file except in compliance with the License.
  @@ -13,28 +13,63 @@
    * See the License for the specific language governing permissions and
    * limitations under the License.
    */
  +
   package org.apache.commons.sql.task;
   
   import java.io.File;
   import java.io.FileWriter;
  +import java.io.StringWriter;
   import java.io.Writer;
  +import java.sql.Connection;
  +
  +import javax.sql.DataSource;
   
   import org.apache.commons.sql.builder.SqlBuilder;
   import org.apache.commons.sql.builder.SqlBuilderFactory;
   import org.apache.commons.sql.io.DatabaseReader;
   import org.apache.commons.sql.model.Database;
  +import org.apache.commons.sql.util.DDLExecutor;
   import org.apache.tools.ant.BuildException;
  -import org.apache.tools.ant.Task;
   
   /**
    * A base task which generates the SQL DDL to create a database
    * to a given output file from an XML schema representing
    * a data model contains tables for a <strong>single</strong>
  - * database.
  - *
  - * @version $Id$
  + * database.  This task can optionally generate DDL to upgrade an existing
  + * database to the current schema definition.  The results of either
  + * generation can be executed against an existing database.
  + * <p>
  + * Here is a ant/maven excerpt for using this:
  + * <pre>
  +  &lt;taskdef
  +    name="ddl"
  +    classname="org.apache.commons.sql.task.DDLTask"&gt;
  +      &lt;classpath refid="maven.dependency.classpath"/&gt;
  +  &lt;/taskdef&gt;
  +        
  +  &lt;target name="custom-ddl" description="Creates ddl"&gt;
  +    &lt;ddl
  +      xmlFile="schema/schema.xml" 
  +      targetDatabase="mysql"
  +      output="target/schema.sql"
  +      dbUrl="jdbc:mysql://localhost:3306/test"
  +      dbUser="user"
  +      dbPassword="pass"
  +      dbDriver="com.mysql.jdbc.Driver"
  +      alterDb="true"
  +      executeSql="true"
  +      modifyColumns="true"
  +      doDrops="true"
  +    /&gt;
  +  &lt;/target&gt;
  + * </pre>
  + * 
  + * @author <a href="mailto:jstrachan@apache.org">James Strachan</a>
  + * @author <a href="mailto:jvanzyl@zenplex.com">Jason van Zyl</a>
  + * @author <a href="mailto:dlr@finemaltcoding.com">Daniel Rall</a>
  + * @author John Marshall/Connectria
    */
  -public class DDLTask extends Task
  +public class DDLTask extends DatabaseTask
   {
       /**
        * XML that describes the database model, this is transformed
  @@ -54,11 +89,26 @@
       private String targetDatabase;
   
       /**
  -     * Flag indicates whether SQL drop statements should be generated.
  +     * Flag for executing the sql or not.
        */
  -    private boolean dropTables = true;
  +    private boolean executeSql;
   
       /**
  +     * Flag for whether to alter the database or recreate from scratch
  +     */
  +    private boolean alterDb;
  +    
  +    /**
  +     * Flag for whether to modify column definitions in an existing database
  +     */
  +    private boolean modifyColumns;
  +    
  +    /**
  +     * Flag for whether drops should be made when updating an existing database
  +     */
  +    private boolean doDrops;
  +    
  +    /**
        * Get the xml schema describing the application model.
        *
        * @return  String xml schema file.
  @@ -115,26 +165,80 @@
       {
           this.output = output;
       }
  -    
  +
       /**
  -     * @return Returns the dropTables.
  +     * Check if the database should be altered to match the schema or
  +     * recreated from scratch
  +     * @return alter flag
        */
  -    public boolean isDropTables() {
  -        return dropTables;
  +    public boolean getAlterDb() {
  +        return alterDb;
       }
   
       /**
  -     * @param dropTables The dropTables to set.
  +     * Set whether the database should be altered to match the schema or
  +     * recreated from scratch
  +     * @param alterDb alter flag
        */
  -    public void setDropTables(boolean dropTables) {
  -        this.dropTables = dropTables;
  +    public void setAlterDb(boolean alterDb) {
  +        this.alterDb = alterDb;
       }
  -    
  +
       /**
  -     * Create the SQL DDL for the given database.
  +     * Check if the generated ddl should be executed against the databsase
  +     * @return true if sql is to be executed
        */
  -    public void execute() throws BuildException
  -    {
  +    public boolean getExecuteSql() {
  +        return executeSql;
  +    }
  +
  +    /**
  +     * Set whether the generated ddl should be executed against the databsase
  +     * @param executeSql the execute flag
  +     */
  +    public void setExecuteSql(boolean executeSql) {
  +        this.executeSql = executeSql;
  +    }
  +
  +    /**
  +     * Check if tables/columns/indexes should be dropped when updating a database
  +     * @return true if drops should be made
  +     */
  +    public boolean getDoDrops() {
  +        return doDrops;
  +    }
  +
  +    /**
  +     * Set whether tables/columns/indexes should be dropped when updating a database
  +     * @param doDrops the new drop flag
  +     */
  +    public void setDoDrops(boolean doDrops) {
  +        this.doDrops = doDrops;
  +    }
  +
  +    /**
  +     * Modify column definitions in an existing database
  +     * @return true if columns should be modified
  +     */
  +    public boolean getModifyColumns() {
  +        return modifyColumns;
  +    }
  +
  +    /**
  +     * Modify column definitions in an existing database
  +     * @param modifyColumns the new flag
  +     */
  +    public void setModifyColumns(boolean modifyColumns) {
  +        this.modifyColumns = modifyColumns;
  +    }
  +
  +
  +    /**
  +     * Checks that settings exist and in valid combinations
  +     * 
  +     * @throws BuildException if parameters are incorrect
  +     */
  +    private void assertValidSettings() throws BuildException {
           if (targetDatabase == null) 
           {
               throw new BuildException( "Must specify a targetDatabase attribute" );
  @@ -147,7 +251,21 @@
           {
               throw new BuildException( "Must specify an output attribute" );
           }
  -        
  +        if (getDbUrl() == null && ( alterDb || executeSql ))
  +        {
  +            throw new BuildException( "Connection url is required if altering database
or executing sql" );
  +        }
  +    }
  +    
  +    /**
  +     * Create the SQL DDL for the given database.
  +     * 
  +     * @throws BuildException
  +     */
  +    public void execute() throws BuildException
  +    {
  +        assertValidSettings();
  +
           Database database = null;
           try 
           {
  @@ -155,26 +273,33 @@
           }
           catch (Exception e) 
           {
  +            e.printStackTrace();
               throw new BuildException( "Failed to parse file: " + getXmlFile(), e );   
            
           }
           
  -        FileWriter writer = null;
  -        try 
  +        DataSource dataSource = null;
  +        if (getDbUrl() != null)
           {
  -            writer = new FileWriter( getOutput() );
  -        }
  -        catch (Exception e) 
  -        {
  -            throw new BuildException( "Failed to create file: " + getOutput(), e );   
            
  +            try
  +            { 
  +                dataSource = getDataSource();
  +            }
  +            catch ( Exception e )
  +            {
  +                e.printStackTrace();
  +                throw new BuildException( "Could not get connection: " + dbUrl, e );
  +            }
           }
           
  +        StringWriter writer = new StringWriter();
           SqlBuilder builder = null;
           try
  -        {        
  +        {
               builder = newSqlBuilder(writer);
           }
           catch (Exception e) 
           {
  +            e.printStackTrace();
               throw new BuildException( "Failed to create SqlBuilder for database: " + getTargetDatabase(),
e );                
           }
           if ( builder == null)
  @@ -183,15 +308,79 @@
           }
           
           // OK we're ready now, lets try create the DDL
  +        Connection con = null;
  +        try 
  +        {
  +            if ( alterDb )
  +            {
  +                con = dataSource.getConnection();
  +                builder.alterDatabase(database, con, doDrops, modifyColumns);
  +            }
  +            else
  +            {
  +                builder.createDatabase(database);
  +            }
  +        }
  +        catch (Exception e) 
  +        {
  +            e.printStackTrace();
  +            throw new BuildException( "Error occurred while creating ddl", e );
  +        } 
  +        finally
  +        {
  +            try
  +            {
  +                if ( con != null )
  +                {
  +                    con.close();
  +                }
  +            }
  +            catch (Exception e)
  +            {
  +                //ignore
  +            }
  +        }
  +
  +        String sql = writer.toString();
  +        if ( executeSql )
  +        {
  +            try
  +            {
  +                DDLExecutor exec = new DDLExecutor( dataSource );
  +                exec.evaluateBatch(sql);
  +                
  +            }
  +            catch (Exception e)
  +            {
  +                e.printStackTrace();
  +                throw new BuildException( "Failed to create evaluate sql", e );       
        
  +            }
  +        }
  +
  +        //write it out
  +        FileWriter out = null;
           try 
           {
  -            builder.createDatabase(database, dropTables);
  -            writer.close();
  +            out = new FileWriter( getOutput() );
  +            out.write( sql );
           }
           catch (Exception e) 
           {
  -            throw new BuildException( "Error occurred while writing to file: " + getOutput(),
e );                
  +            e.printStackTrace();
  +            throw new BuildException( "Failed to create file: " + getOutput(), e );   
            
           }
  +        finally
  +        {
  +            try
  +            {
  +                out.close();
  +            }
  +            catch (Exception e)
  +            {
  +                //ignore
  +            }
  +        }
  +
       }
       
       // Implementation methods
  @@ -199,19 +388,33 @@
       
       /**
        * Loads the XML schema from the XML file and returns the database model bean
  +     * 
  +     * @return Database schema
  +     * @throws Exception
        */
       protected Database loadDatabase() throws Exception
       {
           DatabaseReader reader = new DatabaseReader();
  -        return (Database) reader.parse( getXmlFile() );
  +        Database db = (Database) reader.parse( getXmlFile() );
  +
  +//org.apache.commons.sql.io.DatabaseWriter writer = new org.apache.commons.sql.io.DatabaseWriter(System.err);
  +//writer.write(db);
  +        
  +        return db;
       }
       
  +    /**
  +     * Gets an SqlBuilder for the given writer
  +     * 
  +     * @param writer Destination writer
  +     * @return SqlBuilder
  +     * 
  +     * @throws Exception
  +     */
       protected SqlBuilder newSqlBuilder(Writer writer) throws Exception     
       {   
           SqlBuilder builder = SqlBuilderFactory.newSqlBuilder(getTargetDatabase());
           builder.setWriter(writer);
           return builder;
       }
  -    
  -
   }
  
  
  
  1.7       +3 -1      jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/util/DDLExecutor.java
  
  Index: DDLExecutor.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/util/DDLExecutor.java,v
  retrieving revision 1.6
  retrieving revision 1.7
  diff -u -r1.6 -r1.7
  --- DDLExecutor.java	18 Jul 2004 21:55:58 -0000	1.6
  +++ DDLExecutor.java	14 Oct 2004 19:42:48 -0000	1.7
  @@ -146,7 +146,7 @@
        *
        * @throws SQLException if an error occurs and isContinueOnError == false
        */
  -    public void evaluateBatch(String sql) throws SQLException {
  +    public int evaluateBatch(String sql) throws SQLException {
           Connection connection = borrowConnection();
           Statement statement = null;
           int errors = 0;
  @@ -204,6 +204,8 @@
               closeStatement(statement);
               returnConnection(connection);
           }
  +
  +        return errors;
       }
       
   
  
  
  
  1.8       +36 -19    jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/io/JdbcModelReader.java
  
  Index: JdbcModelReader.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/io/JdbcModelReader.java,v
  retrieving revision 1.7
  retrieving revision 1.8
  diff -u -r1.7 -r1.8
  --- JdbcModelReader.java	18 Jul 2004 21:55:58 -0000	1.7
  +++ JdbcModelReader.java	14 Oct 2004 19:42:48 -0000	1.8
  @@ -26,6 +26,8 @@
   import java.util.List;
   import java.util.Map;
   import java.util.Vector;
  +import java.util.regex.Matcher;
  +import java.util.regex.Pattern;
   
   import org.apache.commons.logging.Log;
   import org.apache.commons.logging.LogFactory;
  @@ -52,6 +54,7 @@
       String catalog = null;
       String schema = null;
       String[] tableTypes = { "TABLE", "VIEW" };
  +    Pattern defaultPattern = Pattern.compile("\\(\\'?(.*?)\\'?\\)"); //value with parenthesis
and/or quotes around it
   
       public JdbcModelReader() {
       }
  @@ -159,9 +162,7 @@
                   t1.setName(tableName);
                   tables.add(t1);
               }
  -            if (tableData != null) {
  -                tableData.close();
  -            }
  +
               Iterator i = tables.iterator();
               while (i.hasNext()) {
                   Table t = (Table) i.next();
  @@ -288,11 +289,12 @@
                    * An empty string means nobody knows.
                    * We make the assumption that "NO" means no, anything else means
                    * yes.
  +                 * jmm - sometimes (not always) jTDS/mssql would return "NO ", which doesn't
match "NO", hence the trim
                    */
                   boolean columnIsNullable = false;
  -                if (columnInfoColumns.contains("IS_NULLABLE")
  -                    && "NO".equalsIgnoreCase(
  -                        columnData.getString("IS_NULLABLE"))) {
  +                String isNullableValue = columnInfoColumns.contains("IS_NULLABLE") ? columnData.getString("IS_NULLABLE")
: null;
  +                if ( isNullableValue != null ) isNullableValue = isNullableValue.trim();
  +                if ( "NO".equalsIgnoreCase(isNullableValue) ) {
                       columnIsNullable = false;
                   }
                   else {
  @@ -337,13 +339,21 @@
                   col.setTypeCode(columnType);
                   col.setSize(columnSize);
                   col.setRequired(!columnIsNullable);
  -                col.setDefaultValue(columnDefaultValue);
                   if (primaryKeys.contains(col.getName())) {
                       col.setPrimaryKey(true);
                   }
                   else {
                       col.setPrimaryKey(false);
                   }
  +
  +                //sometimes the default comes back with parenthesis around it (jTDS/mssql)
  +                if ( columnDefaultValue != null ) {
  +                    Matcher m = defaultPattern.matcher(columnDefaultValue);
  +                    if ( m.matches() ) {
  +                        columnDefaultValue = m.group(1);
  +                    }
  +                    col.setDefaultValue(columnDefaultValue);
  +                }
                   col.setPrecisionRadix(columnPrecision);
                   col.setScale(columnScale);
                   columns.add(col);
  @@ -463,7 +473,12 @@
                       //importedKeyInitiallyDeferred - see SQL92 for definition
                       //importedKeyInitiallyImmediate - see SQL92 for definition 
                       //importedKeyNotDeferrable - see SQL92 for definition             
  
  -                    if (!pkTable.equals(prevPkTable)) {
  +
  +                    //if tables are different OR fk field is first within fk
  +                    //need new FK object
  +                    //without this two fks to the same table on different fields in the
source table
  +                    //get put in the same pk
  +                    if (!pkTable.equals(prevPkTable) || keySequence == 1) {
                           if (currFk != null) {
                               fks.add(currFk);
                           }
  @@ -495,34 +510,36 @@
           DatabaseMetaData dbmd = connection.getMetaData();
   
           Map indexesByName = new HashMap();
  -
  +        
           ResultSet columnData = null;
           try {
               columnData = dbmd.getIndexInfo(catalog, schema, tableName, false, false);
           } catch ( SQLException e ) {
               log.trace("database does not support getIndexInfo()", e);
           }
  -
  +        
           if ( columnData != null ) {
               try {
                   //can be multiple columns per index
                   while ( columnData.next() ) {
  -
  -                    String indexName = columnData.getString("INDEX_NAME");
  +    
                       boolean unique = !columnData.getBoolean("NON_UNIQUE");
  +                    String indexName = columnData.getString("INDEX_NAME");
                       String column = columnData.getString("COLUMN_NAME");
  -
  +    
                       Index index = (Index) indexesByName.get(indexName);
  -                    if ( index == null ) {
  +                    if ( index == null && indexName != null ) {
                           index = new Index();
                           index.setName( indexName );
                           indexesByName.put( indexName, index );
                           index.setUnique( unique );
                       }
  -
  -                    IndexColumn ic = new IndexColumn();
  -                    ic.setName( column );
  -                    index.addIndexColumn( ic );
  +                    
  +                    if ( index != null ) {
  +                        IndexColumn ic = new IndexColumn();
  +                        ic.setName( column );
  +                        index.addIndexColumn( ic );
  +                    }
                   }
               }
               finally {
  @@ -531,7 +548,7 @@
                   }
               }
           }
  -
  +        
           return new Vector(indexesByName.values());
       }
   
  
  
  
  1.3       +2 -2      jakarta-commons-sandbox/sql/src/test/org/apache/commons/sql/task/TestDDLTask.java
  
  Index: TestDDLTask.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/test/org/apache/commons/sql/task/TestDDLTask.java,v
  retrieving revision 1.2
  retrieving revision 1.3
  diff -u -r1.2 -r1.3
  --- TestDDLTask.java	28 Feb 2004 03:35:49 -0000	1.2
  +++ TestDDLTask.java	14 Oct 2004 19:42:48 -0000	1.3
  @@ -61,7 +61,7 @@
   
           // Without drop statements
           task.setOutput(createOutputFile());
  -        task.setDropTables(false);
  +        task.setDoDrops(false);
           task.execute();
       }
   
  
  
  

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org


Mime
View raw message