    001    //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.4_testing/src/org/deegree/tools/datastore/DDLGenerator.java $
    002    /*----------------------------------------------------------------------------
    003     This file is part of deegree, http://deegree.org/
    004     Copyright (C) 2001-2009 by:
    005       Department of Geography, University of Bonn
    006     and
    007       lat/lon GmbH
    008
    009     This library is free software; you can redistribute it and/or modify it under
    010     the terms of the GNU Lesser General Public License as published by the Free
    011     Software Foundation; either version 2.1 of the License, or (at your option)
    012     any later version.
    013     This library is distributed in the hope that it will be useful, but WITHOUT
    014     ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
    015     FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
    016     details.
    017     You should have received a copy of the GNU Lesser General Public License
    018     along with this library; if not, write to the Free Software Foundation, Inc.,
    019     59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
    020
    021     Contact information:
    022
    023     lat/lon GmbH
    024     Aennchenstr. 19, 53177 Bonn
    025     Germany
    026     http://lat-lon.de/
    027
    028     Department of Geography, University of Bonn
    029     Prof. Dr. Klaus Greve
    030     Postfach 1147, 53001 Bonn
    031     Germany
    032     http://www.geographie.uni-bonn.de/deegree/
    033
    034     e-mail: info@deegree.org
    035    ----------------------------------------------------------------------------*/
    036    package org.deegree.tools.datastore;
    037
    038    import java.io.File;
    039    import java.io.FileOutputStream;
    040    import java.io.IOException;
    041    import java.io.OutputStreamWriter;
    042    import java.io.PrintWriter;
    043    import java.net.MalformedURLException;
    044    import java.net.URL;
    045    import java.util.ArrayList;
    046    import java.util.Collection;
    047    import java.util.HashMap;
    048    import java.util.HashSet;
    049    import java.util.Iterator;
    050    import java.util.LinkedHashMap;
    051    import java.util.Map;
    052    import java.util.Set;
    053
    054    import org.deegree.datatypes.Types;
    055    import org.deegree.datatypes.UnknownTypeException;
    056    import org.deegree.framework.xml.XMLParsingException;
    057    import org.deegree.framework.xml.schema.XMLSchemaException;
    058    import org.deegree.io.datastore.schema.MappedFeaturePropertyType;
    059    import org.deegree.io.datastore.schema.MappedFeatureType;
    060    import org.deegree.io.datastore.schema.MappedGMLId;
    061    import org.deegree.io.datastore.schema.MappedGMLSchema;
    062    import org.deegree.io.datastore.schema.MappedGMLSchemaDocument;
    063    import org.deegree.io.datastore.schema.MappedGeometryPropertyType;
    064    import org.deegree.io.datastore.schema.MappedPropertyType;
    065    import org.deegree.io.datastore.schema.MappedSimplePropertyType;
    066    import org.deegree.io.datastore.schema.TableRelation;
    067    import org.deegree.io.datastore.schema.content.MappingField;
    068    import org.deegree.io.datastore.schema.content.MappingGeometryField;
    069    import org.deegree.io.datastore.schema.content.SimpleContent;
    070    import org.deegree.io.datastore.sql.idgenerator.DBSeqIdGenerator;
    071    import org.deegree.model.crs.UnknownCRSException;
    072    import org.deegree.model.feature.schema.FeatureType;
    073    import org.deegree.model.feature.schema.PropertyType;
    074    import org.xml.sax.SAXException;
    075
    076    /**
    077     * Abstract base class for DDL generation from annotated GML schema files.
    078     * <p>
    079     * This abstract base class only implements the functionality needed to retrieve the necessary tables and columns used
    080     * in an annotated GML schema. Some DDL generation may be dependent on the specific SQL backend to be used, so this is
    081     * implemented in concrete extensions of this class.
    082     *
    083     * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider</a>
    084     * @author last edited by: $Author: mschneider $
    085     *
    086     * @version $Revision: 18195 $, $Date: 2009-06-18 17:55:39 +0200 (Do, 18. Jun 2009) $
    087     */
    088    public abstract class DDLGenerator {
    089
    090        protected static final String FT_PREFIX = "FT_";
    091
    092        protected static final int FEATURE_TYPE_TABLE = 0;
    093
    094        protected static final int JOIN_TABLE = 1;
    095
    096        protected static final int MULTI_PROPERTY_TABLE = 2;
    097
    098        protected MappedGMLSchema schema;
    099
    100        // key type: String (table names), value type: TableDefinition
    101        protected Map<String, TableDefinition> tables = new HashMap<String, TableDefinition>();
    102
    103        // names of sequences (for id generation)
    104        protected Set<String> sequences = new HashSet<String>();
    105
    106        /**
    107         * Generates the SQL statements necessary for setting the schema search path. Must be overwritten by the concrete
    108         * implementation.
    109         *
    110         * @param dbSchemaName
    111         * @return the SQL statements necessary for setting the schema search path accordingly
    112         */
    113        protected abstract StringBuffer generateSetSchemaStmt( String dbSchemaName );
    114
    115        /**
    116         * Generates the DDL statements necessary for the creation of the given schema. May be overwritten by a concrete
    117         * implementation.
    118         *
    119         * @param dbSchemaName
    120         * @return the DDL statements necessary for the creation of the given db schema
    121         */
    122        protected StringBuffer generateCreateSchemaStmts( String dbSchemaName ) {
    123            StringBuffer sb = new StringBuffer( "CREATE SCHEMA " );
    124            sb.append( dbSchemaName );
    125            sb.append( ";\n" );
    126            return sb;
    127        }
    128
    129        /**
    130         * Generates the DDL statements necessary for the creation of the given table definition. Must be overwritten by the
    131         * concrete implementation.
    132         *
    133         * @param table
    134         * @return the DDL statements necessary for the creation of the given table definition
    135         */
    136        protected abstract StringBuffer generateCreateTableStmt( TableDefinition table );
    137
    138        /**
    139         * Generates the DDL statements necessary for the creation of standard indexes for the given table definition. Must
    140         * be overwritten by the concrete implementation.
    141         *
    142         * @param table
    143         * @return the DDL statements necessary for the creation of standard indexes for the given table definition
    144         */
    145        protected abstract StringBuffer generateCreateIndexStmts( TableDefinition table );
    146
    147        /**
    148         * Generates the DDL statements necessary for the creation of the given sequence. May be overwritten by a concrete
    149         * implementation.
    150         *
    151         * @param sequenceName
    152         * @return the DDL statements necessary for the creation of the given sequence definition
    153         */
    154        protected StringBuffer generateCreateSequenceStmt( String sequenceName ) {
    155            StringBuffer sb = new StringBuffer( "CREATE SEQUENCE " );
    156            sb.append( sequenceName );
    157            sb.append( ";\n" );
    158            return sb;
    159        }
    160
    161        /**
    162         * Generates the DDL statements necessary for the removal of the given schema. May be overwritten by a concrete
    163         * implementation.
    164         *
    165         * @param dbSchemaName
    166         * @return the DDL statements necessary for the removal of the given db schema
    167         */
    168        protected StringBuffer generateDropSchemaStmt( String dbSchemaName ) {
    169            StringBuffer sb = new StringBuffer();
    170            sb.append( "DROP SCHEMA " );
    171            sb.append( dbSchemaName );
    172            sb.append( " CASCADE;\n" );
    173            return sb;
    174        }
    175
    176        /**
    177         * Generates the DDL statements necessary for the removal of the given table definition. May be overwritten by a
    178         * concrete implementation.
    179         *
    180         * @param table
    181         * @return the DDL statements necessary for the removal of the given table definition
    182         */
    183        protected StringBuffer generateDropTableStmt( TableDefinition table ) {
    184            StringBuffer sb = new StringBuffer();
    185            sb.append( "DROP TABLE " );
    186            sb.append( table.getName() );
    187            sb.append( " CASCADE;\n" );
    188            return sb;
    189        }
    190
    191        /**
    192         * Generates the DDL statements necessary for the dropping of standard indexes for the given table definition. May
    193         * be overwritten by a concrete implementation.
    194         *
    195         * @param table
    196         * @return the DDL statements necessary for the dropping of standard indexes for the given table definition
    197         */
    198        protected StringBuffer generateDropIndexStmts( TableDefinition table ) {
    199            StringBuffer sb = new StringBuffer();
    200
    201            // build drop statements for geometry indexes
    202            Collection<ColumnDefinition> geometryColumns = new ArrayList<ColumnDefinition>();
    203            for ( ColumnDefinition column : table.getColumns() ) {
    204                if ( column.isGeometry() ) {
    205                    geometryColumns.add( column );
    206                }
    207            }
    208
    209            Iterator<ColumnDefinition> iter = geometryColumns.iterator();
    210            int spatialIdxCnt = 1;
    211            while ( iter.hasNext() ) {
    212                iter.next();
    213                sb.append( "DROP INDEX " );
    214                sb.append( table.getName() + ( spatialIdxCnt++ ) );
    215                sb.append( "_SPATIAL_IDX;" );
    216                sb.append( '\n' );
    217            }
    218
    219            // build table type specific drop index statements
    220            switch ( table.getType() ) {
    221            case JOIN_TABLE: {
    222                // create an index on every column
    223                ColumnDefinition[] columns = table.getColumns();
    224                for ( int i = 0; i < columns.length; i++ ) {
    225                    if ( columns[i].isFK() ) {
    226                        sb.append( "DROP INDEX " );
    227                        sb.append( table.getName().toUpperCase() );
    228                        sb.append( "_" );
    229                        sb.append( columns[i].getName() + "_IDX" );
    230                        sb.append( ';' );
    231                        sb.append( '\n' );
    232                    }
    233                }
    234                break;
    235            }
    236            default: {
    237                break;
    238            }
    239            }
    240            return sb;
    241        }
    242
    243        /**
    244         * Generates the DDL statements necessary for the removal of the given sequence. May be overwritten by a concrete
    245         * implementation.
    246         *
    247         * @param sequenceName
    248         * @return the DDL statements necessary for the removal of the given sequence definition
    249         */
    250        protected StringBuffer generateDropSequenceStmt( String sequenceName ) {
    251            StringBuffer sb = new StringBuffer( "DROP SEQUENCE " );
    252            sb.append( sequenceName );
    253            sb.append( ";\n" );
    254            return sb;
    255        }
    256
    257        /**
    258         * Creates a new instance of <code>DDLGenerator</code> from the given parameters.
    259         *
    260         * @param schemaURL
    261         * @throws MalformedURLException
    262         * @throws IOException
    263         * @throws SAXException
    264         * @throws XMLParsingException
    265         * @throws XMLSchemaException
    266         * @throws UnknownCRSException
    267         */
    268        protected DDLGenerator( URL schemaURL ) throws MalformedURLException, IOException, SAXException,
    269                                XMLParsingException, XMLSchemaException, UnknownCRSException {
    270
    271            System.out.println( Messages.format( "LOADING_SCHEMA_FILE", schemaURL ) );
    272            MappedGMLSchemaDocument schemaDoc = new MappedGMLSchemaDocument();
    273            schemaDoc.load( schemaURL );
    274            schema = schemaDoc.parseMappedGMLSchema();
    275            FeatureType[] featureTypes = schema.getFeatureTypes();
    276            int concreteCount = 0;
    277            for ( int i = 0; i < featureTypes.length; i++ ) {
    278                if ( !featureTypes[i].isAbstract() ) {
    279                    concreteCount++;
    280                }
    281            }
    282            System.out.println( Messages.format( "SCHEMA_INFO", new Integer( featureTypes.length ),
    283                                                 new Integer( featureTypes.length - concreteCount ),
    284                                                 new Integer( concreteCount ) ) );
    285            System.out.println( Messages.getString( "RETRIEVING_TABLES" ) );
    286            buildTableMap();
    287        }
    288
    289        /**
    290         * Returns all table definitions of the given type.
    291         *
    292         * @param type
    293         *            FEATURE_TYPE_TABLE, JOIN_TABLE or MULTI_PROPERTY_TABLE
    294         * @return all table definitions of the given type.
    295         */
    296        protected TableDefinition[] getTables( int type ) {
    297            Collection<TableDefinition> tableList = new ArrayList<TableDefinition>();
    298            Iterator<String> iter = this.tables.keySet().iterator();
    299            while ( iter.hasNext() ) {
    300                String tableName = iter.next();
    301                TableDefinition table = this.tables.get( tableName );
    302                if ( table.getType() == type ) {
    303                    tableList.add( table );
    304                }
    305            }
    306            return tableList.toArray( new TableDefinition[tableList.size()] );
    307        }
    308
    309        /**
    310         * Returns the table definition for the table with the given name. If no such definition exists, a new table
    311         * definition is created and added to the internal <code>tables</code> map.
    312         *
    313         * @param tableName
    314         *            table definition to look up
    315         * @param type
    316         *            type of the table (only respected, if a new TableDefinition instance is created)
    317         * @return the table definition for the table with the given name.
    318         */
    319        private TableDefinition lookupTableDefinition( String tableName, int type ) {
    320            TableDefinition table = this.tables.get( tableName );
    321            if ( table == null ) {
    322                table = new TableDefinition( tableName, type );
    323                this.tables.put( tableName, table );
    324            }
    325            return table;
    326        }
    327
    328        /**
    329         * Collects the referenced tables and their columns from the input schema. Builds the member map <code>tables</code>
    330         * from this data.
    331         */
    332        private void buildTableMap() {
    333            FeatureType[] featureTypes = schema.getFeatureTypes();
    334            for ( int i = 0; i < featureTypes.length; i++ ) {
    335                if ( !featureTypes[i].isAbstract() ) {
    336                    buildTableMap( (MappedFeatureType) featureTypes[i] );
    337                }
    338            }
    339        }
    340
    341        /**
    342         * Collects the tables and their columns used in the annotation of the given feature type. Builds the member map
    343         * <code>tables</code> from this data.
    344         *
    345         * @param ft
    346         *            feature type to process
    347         */
    348        private void buildTableMap( MappedFeatureType ft ) {
    349            TableDefinition table = lookupTableDefinition( ft.getTable(), FEATURE_TYPE_TABLE );
    350
    351            MappedGMLId gmlId = ft.getGMLId();
    352            addGMLIdColumns( gmlId, table );
    353
    354            if ( gmlId.getIdGenerator() instanceof DBSeqIdGenerator ) {
    355                extractSequence( (DBSeqIdGenerator) ft.getGMLId().getIdGenerator() );
    356            }
    357
    358            PropertyType[] properties = ft.getProperties();
    359            for ( int i = 0; i < properties.length; i++ ) {
    360                MappedPropertyType property = (MappedPropertyType) properties[i];
    361                if ( property instanceof MappedSimplePropertyType ) {
    362                    buildTableMap( (MappedSimplePropertyType) property, table );
    363                } else if ( property instanceof MappedGeometryPropertyType ) {
    364                    buildTableMap( (MappedGeometryPropertyType) property, table );
    365                } else if ( property instanceof MappedFeaturePropertyType ) {
    366                    buildTableMap( (MappedFeaturePropertyType) property, table );
    367                } else {
    368                    throw new RuntimeException( Messages.format( "ERROR_UNEXPECTED_PROPERTY_TYPE",
    369                                                                 property.getClass().getName() ) );
    370                }
    371            }
    372        }
    373
    374        /**
    375         * Adds the name of the sequence that the given {@link DBSeqIdGenerator} refers to.
    376         *
    377         * @param idGenerator
    378         *            generator instance
    379         */
    380        private void extractSequence( DBSeqIdGenerator idGenerator ) {
    381            this.sequences.add( idGenerator.getSequenceName() );
    382        }
    383
    384        /**
    385         * Adds the columns used in the given <code>MappedGMLId</code> to the also given <code>TableDefinition</code>.
    386         *
    387         * @param gmlId
    388         *            columns are taken from this gmlId mapping
    389         * @param table
    390         *            columns are added to this table definition
    391         */
    392        private void addGMLIdColumns( MappedGMLId gmlId, TableDefinition table ) {
    393            MappingField[] idFields = gmlId.getIdFields();
    394            for ( int i = 0; i < idFields.length; i++ ) {
    395                ColumnDefinition column = new ColumnDefinition( idFields[i].getField(), idFields[i].getType(), false, true,
    396                                                                false, -1, false );
    397                table.addColumn( column );
    398            }
    399        }
    400
    401        /**
    402         * Collects the tables and their columns used in the annotation of the given simple property type. Builds the
    403         * <code>table</code> member map from this data.
    404         * <p>
    405         * If the data for the property is stored in a related table, the table and column information used on the path to
    406         * this table is also added to the <code>tables</code> member map.
    407         *
    408         * @param simpleProperty
    409         *            simple property type to process
    410         * @param table
    411         *            table definition associated with the property definition
    412         */
    413        private void buildTableMap( MappedSimplePropertyType simpleProperty, TableDefinition table ) {
    414            Collection<ColumnDefinition> newColumns = new ArrayList<ColumnDefinition>();
    415            // array must always have length 1
    416            TableRelation[] relations = simpleProperty.getTableRelations();
    417            if ( simpleProperty.getMaxOccurs() != 1 && ( relations == null || relations.length < 1 ) ) {
    418                throw new RuntimeException( Messages.format( "ERROR_INVALID_PROPERTY_DEFINITION", simpleProperty.getName() ) );
    419            }
    420
    421            SimpleContent content = simpleProperty.getContent();
    422            if ( content instanceof MappingField ) {
    423                MappingField mf = (MappingField) content;
    424                if ( relations == null || relations.length == 0 ) {
    425                    newColumns.add( new ColumnDefinition( mf.getField(), mf.getType(), simpleProperty.getMinOccurs() == 0,
    426                                                          false, -1, false ) );
    427                } else {
    428                    TableRelation firstRelation = relations[0];
    429                    MappingField[] fromFields = firstRelation.getFromFields();
    430                    for ( int i = 0; i < fromFields.length; i++ ) {
    431                        MappingField fromField = fromFields[i];
    432                        newColumns.add( new ColumnDefinition( fromField.getField(), fromField.getType(), false, false, -1,
    433                                                              true ) );
    434                    }
    435                    buildTableMap( relations, mf );
    436                }
    437            } else {
    438                String msg = "Ignoring property '" + simpleProperty + "' - has virtual content.";
    439                System.out.println( msg );
    440            }
    441            table.addColumns( newColumns );
    442        }
    443
    444        /**
    445         * Collects the tables and their columns used in the annotation of the given geometry property type. Builds the
    446         * <code>table</code> member map from this data.
    447         * <p>
    448         * If the geometry for the property is stored in a related table, the table and column information used on the path
    449         * to this table is also added to the <code>tables</code> member map.
    450         *
    451         * @param geometryProperty
    452         *            feature property type to process
    453         * @param table
    454         *            table definition associated with the property definition
    455         */
    456        private void buildTableMap( MappedGeometryPropertyType geometryProperty, TableDefinition table ) {
    457            Collection<ColumnDefinition> newColumns = new ArrayList<ColumnDefinition>();
    458            TableRelation[] relations = geometryProperty.getTableRelations();
    459            if ( geometryProperty.getMaxOccurs() != 1 && ( relations == null || relations.length < 1 ) ) {
    460                throw new RuntimeException( Messages.format( "ERROR_INVALID_PROPERTY_DEFINITION",
    461                                                             geometryProperty.getName() ) );
    462            }
    463            if ( relations == null || relations.length == 0 ) {
    464                newColumns.add( new ColumnDefinition( geometryProperty.getMappingField().getField(),
    465                                                      geometryProperty.getMappingField().getType(),
    466                                                      geometryProperty.getMinOccurs() == 0, true,
    467                                                      geometryProperty.getMappingField().getSRS(), false ) );
    468            } else {
    469                TableRelation firstRelation = relations[0];
    470                MappingField[] fromFields = firstRelation.getFromFields();
    471                for ( int i = 0; i < fromFields.length; i++ ) {
    472                    MappingField fromField = fromFields[i];
    473                    newColumns.add( new ColumnDefinition( fromField.getField(), fromField.getType(), false, true,
    474                                                          geometryProperty.getMappingField().getSRS(), true ) );
    475                }
    476                buildTableMap( relations, geometryProperty.getMappingField() );
    477            }
    478            table.addColumns( newColumns );
    479        }
    480
    481        /**
    482         * Collects the tables and their columns used in the annotation of the given feature property type. Builds the
    483         * <code>table</code> member map from this data.
    484         * <p>
    485         * The table and column information used on the path to the table of the feature type is also added to the
    486         * <code>tables</code> member map.
    487         *
    488         * @param featureProperty
    489         *            feature property type to process
    490         * @param table
    491         *            table definition associated with the property definition
    492         */
    493        private void buildTableMap( MappedFeaturePropertyType featureProperty, TableDefinition table ) {
    494            Collection<ColumnDefinition> newColumns = new ArrayList<ColumnDefinition>();
    495
    496            // array must always have length 1
    497            TableRelation[] relations = featureProperty.getTableRelations();
    498
    499            // target feature type table must always be accessed via 'Relation'-elements
    500            if ( relations == null || relations.length < 1 ) {
    501                throw new RuntimeException( Messages.format( "ERROR_INVALID_FEATURE_PROPERTY_DEFINITION_1",
    502                                                             featureProperty.getName() ) );
    503            }
    504
    505            // maxOccurs > 1: target feature type table must be accessed via join table
    506            if ( featureProperty.getMaxOccurs() != 1 && ( relations.length < 2 ) ) {
    507                throw new RuntimeException( Messages.format( "ERROR_INVALID_FEATURE_PROPERTY_DEFINITION_2",
    508                                                             featureProperty.getName() ) );
    509            }
    510
    511            // add this feature type's key columns to current table
    512            TableRelation firstRelation = relations[0];
    513            MappingField[] fromFields = firstRelation.getFromFields();
    514            boolean isNullable = featureProperty.getMinOccurs() == 0 && relations.length == 1;
    515            for ( int i = 0; i < fromFields.length; i++ ) {
    516                MappingField fromField = fromFields[i];
    517                if ( featureProperty.externalLinksAllowed() ) {
    518                    newColumns.add( new ColumnDefinition( fromField.getField(), fromField.getType(), true, false, -1, true ) );
    519                    newColumns.add( new ColumnDefinition( fromField.getField() + "_external", fromField.getType(), true,
    520                                                          false, -1, true ) );
    521                } else {
    522                    newColumns.add( new ColumnDefinition( fromField.getField(), fromField.getType(), isNullable, false, -1,
    523                                                          true ) );
    524                }
    525            }
    526            table.addColumns( newColumns );
    527
    528            MappedFeatureType contentType = featureProperty.getFeatureTypeReference().getFeatureType();
    529            buildTableMap( relations, featureProperty, contentType );
    530        }
    531
    532        /**
    533         * Collects the tables and their columns used in the relation tables from a simple/geometry property to it's content
    534         * table. Builds the <code>table</code> member map from this data.
    535         *
    536         * @param relations
    537         *            relation tables from annotation of property type
    538         * @param targetField
    539         *            holds the properties data
    540         */
    541        private void buildTableMap( TableRelation[] relations, MappingField targetField ) {
    542
    543            // process tables used in 'To'-element of each 'Relation'-element
    544            for ( int i = 0; i < relations.length; i++ ) {
    545                String tableName = relations[i].getToTable();
    546                TableDefinition table = lookupTableDefinition( tableName, MULTI_PROPERTY_TABLE );
    547                MappingField[] toFields = relations[i].getToFields();
    548                for ( int j = 0; j < toFields.length; j++ ) {
    549                    boolean toIsFK = relations[i].getFKInfo() == TableRelation.FK_INFO.fkIsToField;
    550                    ColumnDefinition column = new ColumnDefinition( toFields[j].getField(), toFields[j].getType(), false,
    551                                                                    !toIsFK, false, -1, toIsFK );
    552                    // schmitz: assuming not part of PK
    553                    table.addColumn( column );
    554                }
    555            }
    556
    557            // process table used in 'To'-element of last 'Relation'-element (targetField refers to
    558            // this)
    559            ColumnDefinition column = null;
    560            if ( targetField instanceof MappingGeometryField ) {
    561                column = new ColumnDefinition( targetField.getField(), targetField.getType(), false, true,
    562                                               ( (MappingGeometryField) targetField ).getSRS(), false );
    563            } else {
    564                column = new ColumnDefinition( targetField.getField(), targetField.getType(), false, false, -1, false );
    565            }
    566
    567            TableDefinition table = lookupTableDefinition( relations[relations.length - 1].getToTable(),
    568                                                           MULTI_PROPERTY_TABLE );
    569            table.addColumn( column );
    570        }
    571
    572        /**
    573         * Collects the tables and their columns used in the relation tables from a feature property to it's content feature
    574         * type. Builds the <code>table</code> member map from this data.
    575         *
    576         * @param relations
    577         *            relation tables from annotation of feature property type
    578         * @param property
    579         * @param targetType
    580         *            type contained in the feature property
    581         */
    582        private void buildTableMap( TableRelation[] relations, MappedPropertyType property, MappedFeatureType targetType ) {
    583
    584            TableDefinition table = lookupTableDefinition( relations[0].getFromTable(), FEATURE_TYPE_TABLE );
    585
    586            // process tables used in 'To'-element of each 'Relation'-element (except the last)
    587            for ( int i = 0; i < relations.length - 1; i++ ) {
    588                String tableName = relations[i].getToTable();
    589                table = lookupTableDefinition( tableName, JOIN_TABLE );
    590                MappingField[] toFields = relations[i].getToFields();
    591                for ( int j = 0; j < toFields.length; j++ ) {
    592                    boolean toIsFK = relations[i].getFKInfo() == TableRelation.FK_INFO.fkIsToField;
    593                    ColumnDefinition column = new ColumnDefinition( toFields[j].getField(), toFields[j].getType(), false,
    594                                                                    true, false, -1, toIsFK );
    595                    // schmitz: assuming NOT part of PK
    596                    table.addColumn( column );
    597                }
    598            }
    599
    600            // process table used in 'To'-element of last 'Relation'-element
    601            MappedFeatureType[] concreteTypes = targetType.getConcreteSubstitutions();
    602            MappingField[] toFields = relations[relations.length - 1].getToFields();
    603
    604            // if it refers to several target tables (target feature type is abstract), an additional
    605            // column is needed (which determines the target feature type)
    606            if ( concreteTypes.length > 1 ) {
    607                String typeColumn = "featuretype";
    608                if ( relations.length == 1 ) {
    609                    typeColumn = FT_PREFIX + property.getTableRelations()[0].getFromFields()[0].getField();
    610                }
    611                ColumnDefinition column = new ColumnDefinition( typeColumn, Types.VARCHAR, property.getMinOccurs() == 0,
    612                                                                false, -1, false );
    613                table.addColumn( column );
    614            }
    615            for ( int i = 0; i < concreteTypes.length; i++ ) {
    616                MappedFeatureType concreteType = concreteTypes[i];
    617                String tableName = concreteType.getTable();
    618                table = lookupTableDefinition( tableName, FEATURE_TYPE_TABLE );
    619                for ( int j = 0; j < toFields.length; j++ ) {
    620                    ColumnDefinition column = new ColumnDefinition( toFields[j].getField(), toFields[j].getType(), false,
    621                                                                    false, -1, false );
    622                    table.addColumn( column );
    623                }
    624            }
    625
    626            // process tables used in 'From'-element of each 'Relation'-element (except the first)
    627            for ( int i = 1; i < relations.length; i++ ) {
    628                String tableName = relations[i].getFromTable();
    629                if ( i != relations.length - 1 ) {
    630                    table = lookupTableDefinition( tableName, JOIN_TABLE );
    631                } else {
    632                    table = lookupTableDefinition( tableName, FEATURE_TYPE_TABLE );
    633                }
    634                MappingField[] fromFields = relations[i].getFromFields();
    635                for ( int j = 0; j < fromFields.length; j++ ) {
    636                    boolean fromIsFK = relations[i].getFKInfo() == TableRelation.FK_INFO.fkIsFromField;
    637                    ColumnDefinition column = new ColumnDefinition( fromFields[j].getField(), fromFields[j].getType(),
    638                                                                    false, true, false, -1, fromIsFK );
    639                    table.addColumn( column );
    640                }
    641            }
    642        }
    643
    644        /**
    645         * @param outputFile
    646         * @throws IOException
    647         */
    648        public void generateCreateScript( String outputFile )
    649                                throws IOException {
    650            generateCreateScript( outputFile, null );
    651        }
    652
    653        /**
    654         * Generates the DDL statements to create a relational schema that backs the GML schema.
    655         *
    656         * @param outputFile
    657         * @param dbSchema
    658         *            (may be null)
    659         * @throws IOException
    660         */
    661        public void generateCreateScript( String outputFile, String dbSchema )
    662                                throws IOException {
    663            PrintWriter writer = new PrintWriter( new OutputStreamWriter( new FileOutputStream( outputFile ), "UTF-8" ) );
    664
    665            if ( dbSchema != null ) {
    666                writer.print( "/* CREATE DB SCHEMA (" + dbSchema + ") */\n\n" );
    667                writer.print( generateCreateSchemaStmts( dbSchema ) );
    668                writer.print( generateSetSchemaStmt( dbSchema ) );
    669                writer.println();
    670                writer.println();
    671            }
    672
    673            System.out.println( Messages.format( "CREATE_SEQUENCES", new Integer( sequences.size() ) ) );
    674            if ( sequences.size() > 0 ) {
    675                writer.print( "/* CREATE SEQUENCES (" + sequences.size() + ") */\n" );
    676                for ( String sequenceName : sequences ) {
    677                    writer.print( '\n' );
    678                    writer.print( generateCreateSequenceStmt( sequenceName ) );
    679                }
    680            }
    681
    682            TableDefinition[] tables = getTables( FEATURE_TYPE_TABLE );
    683            System.out.println( Messages.format( "CREATE_FEATURE_TYPE", new Integer( tables.length ) ) );
    684            writer.print( "\n\n/* CREATE FEATURE TABLES (" + tables.length + ") */\n" );
    685            for ( int i = 0; i < tables.length; i++ ) {
    686                System.out.println( tables[i].tableName );
    687                writer.print( '\n' );
    688                writer.print( generateCreateTableStmt( tables[i] ) );
    689                writer.print( generateCreateIndexStmts( tables[i] ) );
    690            }
    691
    692            tables = getTables( JOIN_TABLE );
    693            if ( tables.length != 0 ) {
    694                writer.print( "\n\n/* CREATE JOIN TABLES (" + tables.length + ") */\n" );
    695            }
    696            System.out.println( Messages.format( "CREATE_JOIN_TABLES", new Integer( tables.length ) ) );
    697            for ( int i = 0; i < tables.length; i++ ) {
    698                System.out.println( tables[i].tableName );
    699                writer.print( '\n' );
    700                writer.print( generateCreateTableStmt( tables[i] ) );
    701                writer.print( generateCreateIndexStmts( tables[i] ) );
    702            }
    703
    704            tables = getTables( MULTI_PROPERTY_TABLE );
    705            if ( tables.length != 0 ) {
    706                writer.print( "\n\n/* CREATE PROPERTY TABLES (" + tables.length + ") */\n" );
    707            }
    708            System.out.println( Messages.format( "CREATE_PROPERTY_TABLES", new Integer( tables.length ) ) );
    709            for ( int i = 0; i < tables.length; i++ ) {
    710                System.out.println( tables[i].tableName );
    711                writer.print( '\n' );
    712                writer.print( generateCreateTableStmt( tables[i] ) );
    713                writer.print( generateCreateIndexStmts( tables[i] ) );
    714            }
    715            writer.close();
    716        }
    717
    718        /**
    719         * Generates the DDL statements that can be used to remove the relational schema again.
    720         *
    721         * @param outputFile
    722         * @param dbSchema
    723         *            (may be null)
    724         * @throws IOException
    725         */
    726        public void generateDropScript( String outputFile, String dbSchema )
    727                                throws IOException {
    728            PrintWriter writer = new PrintWriter( new OutputStreamWriter( new FileOutputStream( outputFile ), "UTF-8" ) );
    729
    730            if ( dbSchema != null ) {
    731                writer.println( generateSetSchemaStmt( dbSchema ) );
    732                writer.println();
    733                writer.println();
    734            }
    735
    736            TableDefinition[] tables = getTables( FEATURE_TYPE_TABLE );
    737            System.out.println( Messages.format( "DROP_FEATURE_TYPE", new Integer( tables.length ) ) );
    738            writer.print( "/* DROP FEATURE TABLES (" + tables.length + ") */\n" );
    739            for ( int i = 0; i < tables.length; i++ ) {
    740                writer.print( '\n' );
    741                writer.print( generateDropIndexStmts( tables[i] ) );
    742                writer.print( generateDropTableStmt( tables[i] ) );
    743            }
    744
    745            tables = getTables( JOIN_TABLE );
    746            writer.print( "\n\n/* DROP JOIN TABLES (" + tables.length + ") */\n" );
    747            System.out.println( Messages.format( "DROP_JOIN_TABLES", new Integer( tables.length ) ) );
    748            for ( int i = 0; i < tables.length; i++ ) {
    749                writer.print( '\n' );
    750                writer.print( generateDropIndexStmts( tables[i] ) );
    751                writer.print( generateDropTableStmt( tables[i] ) );
    752            }
    753
    754            tables = getTables( MULTI_PROPERTY_TABLE );
    755            writer.print( "\n\n/* DROP PROPERTY TABLES (" + tables.length + ") */\n" );
    756            System.out.println( Messages.format( "DROP_PROPERTY_TABLES", new Integer( tables.length ) ) );
    757            for ( int i = 0; i < tables.length; i++ ) {
    758                writer.print( '\n' );
    759                writer.print( generateDropIndexStmts( tables[i] ) );
    760                writer.print( generateDropTableStmt( tables[i] ) );
    761            }
    762
    763            System.out.println( Messages.format( "DROP_SEQUENCES", new Integer( sequences.size() ) ) );
    764            if ( sequences.size() > 0 ) {
    765                writer.print( "\n\n/* DROP SEQUENCES (" + sequences.size() + ") */\n" );
    766                for ( String sequenceName : sequences ) {
    767                    writer.print( '\n' );
    768                    writer.print( generateDropSequenceStmt( sequenceName ) );
    769                }
    770            }
    771
    772            if ( dbSchema != null ) {
    773                writer.print( "\n\n/* DROP DB SCHEMA (" + dbSchema + ") */\n" );
    774                writer.print( generateDropSchemaStmt( dbSchema ) );
    775                writer.println();
    776            }
    777
    778            writer.close();
    779        }
    780
    781        /**
    782         * @param args
    783         * @throws IOException
    784         * @throws SAXException
    785         * @throws XMLParsingException
    786         * @throws XMLSchemaException
    787         * @throws UnknownCRSException
    788         */
    789        public static void main( String[] args )
    790                                throws IOException, SAXException, XMLParsingException, XMLSchemaException,
    791                                UnknownCRSException {
    792
    793            if ( args.length < 4 || args.length > 5 ) {
    794                System.out.println( "Usage: DDLGenerator <FLAVOUR> <input.xsd> <create.sql> <drop.sql> [DB_SCHEMA]" );
    795                System.exit( 0 );
    796            }
    797
    798            String flavour = args[0];
    799            String schemaFile = args[1];
    800            String createFile = args[2];
    801            String dropFile = args[3];
    802            String dbSchema = args.length == 4 ? null : args[4];
    803
    804            DDLGenerator generator = null;
    805            if ( "POSTGIS".equals( flavour ) ) {
    806                generator = new PostGISDDLGenerator( new File( schemaFile ).toURI().toURL() );
    807            } else if ( "ORACLE".equals( flavour ) ) {
    808                generator = new OracleDDLGenerator( new File( schemaFile ).toURI().toURL() );
    809            } else {
    810                System.out.println( Messages.format( "ERROR_UNSUPPORTED_FLAVOUR", flavour ) );
    811            }
    812
    813            generator.generateCreateScript( createFile, dbSchema );
    814            generator.generateDropScript( dropFile, dbSchema );
    815        }
    816
    817        /**
    818         * Returns a string representation of the object.
    819         *
    820         * @return a string representation of the object.
    821         */
    822        @Override
    823        public String toString() {
    824            StringBuffer sb = new StringBuffer( Messages.getString( "RELATIONAL_SCHEMA" ) );
    825            sb.append( '\n' );
    826
    827            TableDefinition[] tables = getTables( FEATURE_TYPE_TABLE );
    828            sb.append( '\n' );
    829            sb.append( tables.length );
    830            sb.append( " feature type tables\n\n" );
    831            for ( int i = 0; i < tables.length; i++ ) {
    832                sb.append( tables[i] );
    833                sb.append( '\n' );
    834            }
    835
    836            sb.append( '\n' );
    837            tables = getTables( JOIN_TABLE );
    838            sb.append( tables.length );
    839            sb.append( " join tables\n\n" );
    840            for ( int i = 0; i < tables.length; i++ ) {
    841                sb.append( tables[i] );
    842                sb.append( '\n' );
    843            }
    844
    845            sb.append( '\n' );
    846            tables = getTables( MULTI_PROPERTY_TABLE );
    847            sb.append( tables.length );
    848            sb.append( " property tables\n\n" );
    849            for ( int i = 0; i < tables.length; i++ ) {
    850                sb.append( tables[i] );
    851                sb.append( '\n' );
    852            }
    853            return sb.toString();
    854        }
    855
    856        class TableDefinition {
    857
    858            private int type;
    859
    860            String tableName;
    861
    862            private Map<String, ColumnDefinition> columnsMap = new LinkedHashMap<String, ColumnDefinition>();
    863
    864            TableDefinition( String tableName, int type ) {
    865                this.type = type;
    866                this.tableName = tableName;
    867            }
    868
    869            String getName() {
    870                return this.tableName;
    871            }
    872
    873            int getType() {
    874                return this.type;
    875            }
    876
    877            ColumnDefinition[] getColumns() {
    878                Collection<ColumnDefinition> columns = new ArrayList<ColumnDefinition>();
    879                Iterator<String> iter = columnsMap.keySet().iterator();
    880                while ( iter.hasNext() ) {
    881                    String columnName = iter.next();
    882                    columns.add( columnsMap.get( columnName ) );
    883                }
    884                return columns.toArray( new ColumnDefinition[columns.size()] );
    885            }
    886
    887            ColumnDefinition[] getPKColumns() {
    888                Collection<ColumnDefinition> columns = new ArrayList<ColumnDefinition>();
    889                Iterator<String> iter = columnsMap.keySet().iterator();
    890                while ( iter.hasNext() ) {
    891                    String columnName = iter.next();
    892                    ColumnDefinition column = columnsMap.get( columnName );
    893                    if ( column.isPartOfPK() ) {
    894                        columns.add( columnsMap.get( columnName ) );
    895                    }
    896                }
    897                return columns.toArray( new ColumnDefinition[columns.size()] );
    898            }
    899
    900            ColumnDefinition getColumn( String name ) {
    901                return columnsMap.get( name );
    902            }
    903
    904            void addColumn( ColumnDefinition column ) {
    905                ColumnDefinition oldColumn = columnsMap.get( column.getName() );
    906                if ( oldColumn != null ) {
    907                    if ( !( column.getType() == oldColumn.getType() ) ) {
    908                        String msg = null;
    909                        try {
    910                            msg = Messages.format( "ERROR_COLUMN_DEFINITION_TYPES", column.getName(),
    911                                                   Types.getTypeNameForSQLTypeCode( oldColumn.getType() ),
    912                                                   Types.getTypeNameForSQLTypeCode( column.getType() ) );
    913                        } catch ( UnknownTypeException e ) {
    914                            msg = e.getMessage();
    915                            e.printStackTrace();
    916                        }
    917                        throw new RuntimeException( msg );
    918
    919                    }
    920                    if ( oldColumn.isPartOfPK() ) {
    921                        column = oldColumn;
    922                    }
    923                }
    924                columnsMap.put( column.getName(), column );
    925            }
    926
    927            void addColumns( Collection<ColumnDefinition> columns ) {
    928                Iterator<ColumnDefinition> iter = columns.iterator();
    929                while ( iter.hasNext() ) {
    930                    ColumnDefinition column = iter.next();
    931                    addColumn( column );
    932                }
    933            }
    934
    935            @Override
    936            public String toString() {
    937                StringBuffer sb = new StringBuffer();
    938                sb.append( Messages.format( "TABLE", this.tableName ) );
    939                sb.append( Messages.getString( "PRIMARY_KEY" ) );
    940                ColumnDefinition[] pkColumns = getPKColumns();
    941                for ( int i = 0; i < pkColumns.length; i++ ) {
    942                    sb.append( '"' );
    943                    sb.append( pkColumns[i].getName() );
    944                    sb.append( '"' );
    945                    if ( i != pkColumns.length - 1 ) {
    946                        sb.append( ", " );
    947                    }
    948                }
    949                sb.append( '\n' );
    950                Iterator<String> columnNameIter = this.columnsMap.keySet().iterator();
    951                while ( columnNameIter.hasNext() ) {
    952                    String columnName = columnNameIter.next();
    953                    ColumnDefinition column = this.columnsMap.get( columnName );
    954                    try {
    955                        sb.append( Messages.format( "COLUMN", columnName,
    956                                                    Types.getTypeNameForSQLTypeCode( column.getType() ) + ":"
    957                                                                            + column.getType(),
    958                                                    new Boolean( column.isNullable() ) ) );
    959                    } catch ( UnknownTypeException e ) {
    960                        // TODO Auto-generated catch block
    961                        e.printStackTrace();
    962                    }
    963                    sb.append( '\n' );
    964                }
    965                return sb.toString();
    966            }
    967        }
    968
    969        class ColumnDefinition {
    970
    971            private String columnName;
    972
    973            private int type;
    974
    975            private boolean isNullable;
    976
    977            private boolean isGeometryColumn;
    978
    979            private int srsCode;
    980
    981            private boolean isPartOfPK;
    982
    983            private boolean isFK;
    984
    985            ColumnDefinition( String columnName, int type, boolean isNullable, boolean isGeometryColumn, int srsCode,
    986                              boolean isFK ) {
    987                this.columnName = columnName;
    988                this.type = type;
    989                this.isNullable = isNullable;
    990                this.isGeometryColumn = isGeometryColumn;
    991                this.srsCode = srsCode;
    992                this.isFK = isFK;
    993            }
    994
    995            ColumnDefinition( String columnName, int type, boolean isNullable, boolean isPartOfPK,
    996                              boolean isGeometryColumn, int srsCode, boolean isFK ) {
    997                this( columnName, type, isNullable, isGeometryColumn, srsCode, isFK );
    998                this.isPartOfPK = isPartOfPK;
    999            }
    1000
    1001            String getName() {
    1002                return this.columnName;
    1003            }
    1004
    1005            int getType() {
    1006                return this.type;
    1007            }
    1008
    1009            boolean isNullable() {
    1010                return this.isNullable;
    1011            }
    1012
    1013            boolean isGeometry() {
    1014                return this.isGeometryColumn;
    1015            }
    1016
    1017            int getSRS() {
    1018                return this.srsCode;
    1019            }
    1020
    1021            boolean isPartOfPK() {
    1022                return this.isPartOfPK;
    1023            }
    1024
    1025            boolean isFK() {
    1026                return isFK;
    1027            }
    1028        }
    1029    }