/*****************************************************************
 *   Licensed to the Apache Software Foundation (ASF) under one
 *  or more contributor license agreements.  See the NOTICE file
 *  distributed with this work for additional information
 *  regarding copyright ownership.  The ASF licenses this file
 *  to you under the Apache License, Version 2.0 (the
 *  "License"); you may not use this file except in compliance
 *  with the License.  You may obtain a copy of the License at
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing,
 *  software distributed under the License is distributed on an
 *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 *  KIND, either express or implied.  See the License for the
 *  specific language governing permissions and limitations
 *  under the License.
 ****************************************************************/


package org.apache.cayenne.access;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;
import org.objectstyle.ashwood.dbutil.Table;
import org.apache.cayenne.CayenneException;
import org.apache.cayenne.dba.DbAdapter;
import org.apache.cayenne.dba.TypesMapping;
import org.apache.cayenne.map.DataMap;
import org.apache.cayenne.map.DbAttribute;
import org.apache.cayenne.map.DbEntity;
import org.apache.cayenne.map.DbJoin;
import org.apache.cayenne.map.DbRelationship;
import org.apache.cayenne.map.Entity;
import org.apache.cayenne.map.ObjEntity;
import org.apache.cayenne.map.Procedure;
import org.apache.cayenne.map.ProcedureParameter;
import org.apache.cayenne.util.EntityMergeSupport;
import org.apache.cayenne.util.NameConverter;
import org.apache.cayenne.util.Util;

/**
 * Utility class that does reverse engineering of the database. It can create DataMaps
 * using database meta data obtained via JDBC driver.
 * 
 * @author Michael Shengaout
 * @author Andrei Adamchik
 */
public class DbLoader {

    private static Logger logObj = Logger.getLogger(DbLoader.class);

    // TODO: remove this hardcoded stuff once delegate starts to support procedure
    // loading...
    private static final Collection EXCLUDED_PROCEDURES = Arrays.asList(new Object[] {
            "auto_pk_for_table", "auto_pk_for_table;1" /*
                                                         * the last name is some Mac OS X
                                                         * Sybase artifact
                                                         */
    });

    public static final String WILDCARD = "%";

    /** List of db entities to process. */
    private List dbEntityList = new ArrayList();
    
    /**
     * CAY-479 - need to track which entities are skipped in 
     * the loader so that relationships to non-skipped entities can be loaded 
     */
    private Set skippedEntities = new HashSet();

    /** Creates default name for loaded relationship */
    private static String defaultRelName(String dstName, boolean toMany) {
        String uglyName = (toMany) ? dstName + "_ARRAY" : "to_" + dstName;
        return NameConverter.underscoredToJava(uglyName, false);
    }

    /** Creates a unique name for loaded relationship on the given entity. */
    private static String uniqueRelName(Entity entity, String dstName, boolean toMany) {
        int currentSuffix = 1;
        String baseRelName = defaultRelName(dstName, toMany);
        String relName = baseRelName;

        while (entity.getRelationship(relName) != null) {
            relName = baseRelName + currentSuffix;
            currentSuffix++;
        }
        return relName;
    }

    protected Connection con;
    protected DbAdapter adapter;
    protected DatabaseMetaData metaData;
    protected DbLoaderDelegate delegate;
    protected String genericClassName;

    /** Creates new DbLoader. */
    public DbLoader(Connection con, DbAdapter adapter, DbLoaderDelegate delegate) {
        this.adapter = adapter;
        this.con = con;
        this.delegate = delegate;
    }

    /**
     * Returns DatabaseMetaData object associated with this DbLoader.
     */
    public DatabaseMetaData getMetaData() throws SQLException {
        if (null == metaData)
            metaData = con.getMetaData();
        return metaData;
    }

    /**
     * Returns database connection used by this DbLoader.
     */
    public Connection getCon() {
        return con;
    }

    /**
     * Returns a name of a generic class that should be used for all ObjEntities. The most
     * common generic class is {@link org.apache.cayenne.CayenneDataObject}. If
     * generic class name is null (which is the default), DbLoader will assign each entity
     * a unique class name derived from the table name.
     * 
     * @since 1.2
     */
    public String getGenericClassName() {
        return genericClassName;
    }

    /**
     * Sets a name of a generic class that should be used for all ObjEntities. The most
     * common generic class is {@link org.apache.cayenne.CayenneDataObject}. If
     * generic class name is set to null (which is the default), DbLoader will assign each
     * entity a unique class name derived from the table name.
     * 
     * @since 1.2
     */
    public void setGenericClassName(String genericClassName) {
        this.genericClassName = genericClassName;
    }

    /**
     * Returns DbAdapter associated with this DbLoader.
     * 
     * @since 1.1
     */
    public DbAdapter getAdapter() {
        return adapter;
    }

    /**
     * Retrieves catalogues for the database associated with this DbLoader.
     * 
     * @return List with the catalog names, empty Array if none found.
     */
    public List getCatalogs() throws SQLException {
        List catalogs = new ArrayList();
        ResultSet rs = getMetaData().getCatalogs();

        try {
            while (rs.next()) {
                String catalog_name = rs.getString(1);
                catalogs.add(catalog_name);
            }
        }
        finally {
            rs.close();
        }
        return catalogs;
    }

    /**
     * Retrieves the schemas for the database.
     * 
     * @return List with the schema names, empty Array if none found.
     */
    public List getSchemas() throws SQLException {
        List schemas = new ArrayList();
        ResultSet rs = getMetaData().getSchemas();

        try {
            while (rs.next()) {
                String schema_name = rs.getString(1);
                schemas.add(schema_name);
            }
        }
        finally {
            rs.close();
        }
        return schemas;
    }

    /**
     * Returns all the table types for the given database. Types may be such as "TABLE",
     * "VIEW", "SYSTEM TABLE", etc.
     * 
     * @return List of Strings, empty array if nothing found.
     */
    public List getTableTypes() throws SQLException {
        List types = new ArrayList();
        ResultSet rs = getMetaData().getTableTypes();

        try {
            while (rs.next()) {
                types.add(rs.getString("TABLE_TYPE").trim());
            }
        }
        finally {
            rs.close();
        }
        return types;
    }

    /**
     * Returns all table names for given combination of the criteria.
     * 
     * @param catalog The name of the catalog, may be null.
     * @param schemaPattern The pattern for schema name, use "%" for wildcard.
     * @param tableNamePattern The pattern for table names, % for wildcard, if null or ""
     *            defaults to "%".
     * @param types The types of table names to retrieve, null returns all types.
     * @return List of TableInfo objects, empty array if nothing found.
     */
    public List getTables(
            String catalog,
            String schemaPattern,
            String tableNamePattern,
            String[] types) throws SQLException {

        List tables = new ArrayList();

        if (logObj.isDebugEnabled()) {
            logObj.debug("Read tables: catalog="
                    + catalog
                    + ", schema="
                    + schemaPattern
                    + ", tableNames="
                    + tableNamePattern);

            if (types != null && types.length > 0) {
                for (int i = 0; i < types.length; i++) {
                    logObj.debug("Read tables: table type=" + types[i]);
                }
            }
        }

        ResultSet rs = getMetaData().getTables(
                catalog,
                schemaPattern,
                tableNamePattern,
                types);

        try {
            while (rs.next()) {
                String cat = rs.getString("TABLE_CAT");
                String schema = rs.getString("TABLE_SCHEM");
                String name = rs.getString("TABLE_NAME");

                // Oracle 9i and newer has a nifty recycle bin feature... but we don't
                // want dropped tables to be included here; in fact they may even result
                // in errors on reverse engineering as their names have special chars like
                // "/", etc. So skip them all together

                // TODO: Andrus, 10/29/2005 - this type of filtering should be delegated
                // to adapter
                if (name == null || name.startsWith("BIN$")) {
                    continue;
                }

                Table info = new Table(cat, schema, name);
                tables.add(info);
            }
        }
        finally {
            rs.close();
        }
        return tables;
    }

    /**
     * Loads dbEntities for the specified tables.
     * 
     * @param map DataMap to be populated with DbEntities.
     * @param tables The list of org.objectstyle.ashwood.dbutil.Table objects for which
     *            DbEntities must be created.
     * @return false if loading must be immediately aborted.
     */
    public boolean loadDbEntities(DataMap map, List tables) throws SQLException {
        this.dbEntityList = new ArrayList();

        Iterator iter = tables.iterator();
        while (iter.hasNext()) {
            Table table = (Table) iter.next();

            // Check if there already is a DbEntity under such name
            // if so, consult the delegate what to do
            DbEntity oldEnt = map.getDbEntity(table.getName());
            if (oldEnt != null) {
                if (delegate == null) {
                    // no delegate, don't know what to do, cancel import
                    return false;
                }

                try {
                    if (delegate.overwriteDbEntity(oldEnt)) {
                        logObj.debug("Overwrite: " + oldEnt.getName());
                        map.removeDbEntity(oldEnt.getName(), true);
                        delegate.dbEntityRemoved(oldEnt);
                    }
                    else {
                        logObj.debug("Keep old: " + oldEnt.getName());
                        
                        // cay-479 - need to track entities that were not loaded for 
                        // relationships exported to entities that were
                        skippedEntities.add(oldEnt);
                        continue;
                    }
                }
                catch (CayenneException ex) {
                    logObj.debug("Load canceled.");

                    // cancel immediately
                    return false;
                }
            }

            DbEntity dbEntity = new DbEntity();
            dbEntity.setName(table.getName());
            dbEntity.setSchema(table.getSchema());
            dbEntity.setCatalog(table.getCatalog());

            // Create DbAttributes from column information --
            ResultSet rs = getMetaData().getColumns(
                    table.getCatalog(),
                    table.getSchema(),
                    table.getName(),
                    "%");

            try {
                while (rs.next()) {
                    // for a reason not quiet apparent to me, Oracle sometimes
                    // returns duplicate record sets for the same table, messing up table
                    // names. E.g. for the system table "WK$_ATTR_MAPPING" columns are
                    // returned twice - as "WK$_ATTR_MAPPING" and "WK$$_ATTR_MAPPING"...
                    // Go figure

                    String tableName = rs.getString("TABLE_NAME");
                    if (!dbEntity.getName().equals(tableName)) {
                        logObj.info("Incorrectly returned columns for '"
                                + tableName
                                + ", skipping.");
                        continue;
                    }

                    // gets attribute's (column's) information
                    String columnName = rs.getString("COLUMN_NAME");

                    boolean allowNulls = rs.getBoolean("NULLABLE");
                    int columnType = rs.getInt("DATA_TYPE");
                    int columnSize = rs.getInt("COLUMN_SIZE");
                    String typeName = rs.getString("TYPE_NAME");

                    // ignore precision of non-decimal columns
                    int decimalDigits = -1;
                    if (TypesMapping.isDecimal(columnType)) {
                        decimalDigits = rs.getInt("DECIMAL_DIGITS");
                        if (rs.wasNull()) {
                            decimalDigits = -1;
                        }
                    }

                    // create attribute delegating this task to adapter
                    DbAttribute attr = adapter.buildAttribute(
                            columnName,
                            typeName,
                            columnType,
                            columnSize,
                            decimalDigits,
                            allowNulls);
                    attr.setEntity(dbEntity);
                    dbEntity.addAttribute(attr);
                }
            }
            finally {
                rs.close();
            }

            map.addDbEntity(dbEntity);

            // notify delegate
            if (delegate != null) {
                delegate.dbEntityAdded(dbEntity);
            }

            // delegate might have thrown this entity out... so check if it is still
            // around
            // before continuing processing
            if (map.getDbEntity(table.getName()) == dbEntity) {
                this.dbEntityList.add(dbEntity);
            }
        }

        // get primary keys for each table and store it in dbEntity
        Iterator i = map.getDbEntities().iterator();
        while (i.hasNext()) {
            DbEntity dbEntity = (DbEntity) i.next();
            String tableName = dbEntity.getName();
            ResultSet rs = metaData.getPrimaryKeys(null, dbEntity.getSchema(), tableName);

            try {
                while (rs.next()) {
                    String keyName = rs.getString(4);
                    DbAttribute attribute = (DbAttribute) dbEntity.getAttribute(keyName);

                    if (attribute != null) {
                        attribute.setPrimaryKey(true);
                    }
                    else {
                        // why an attribute might be null is not quiet clear
                        // but there is a bug report 731406 indicating that it is possible
                        // so just print the warning, and ignore
                        logObj.warn("Can't locate attribute for primary key: " + keyName);
                    }
                }
            }
            finally {
                rs.close();
            }
        }
        
        // cay-479 - iterate skipped DbEntities to populate exported keys
        Iterator skippedEntityIter = skippedEntities.iterator();
        while (skippedEntityIter.hasNext()) {

            DbEntity skippedEntity = (DbEntity) skippedEntityIter.next();
            loadDbRelationships(skippedEntity, map);
        }
            
        return true;
        
    }

    /**
     * Creates an ObjEntity for each DbEntity in the map. ObjEntities are created empty
     * without
     */
    public void loadObjEntities(DataMap map) {

        Iterator dbEntities = dbEntityList.iterator();
        if (!dbEntities.hasNext()) {
            return;
        }

        List loadedEntities = new ArrayList(dbEntityList.size());

        String packageName = map.getDefaultPackage();
        if (Util.isEmptyString(packageName)) {
            packageName = "";
        }
        else if (!packageName.endsWith(".")) {
            packageName = packageName + ".";
        }

        // load empty ObjEntities for all the tables
        while (dbEntities.hasNext()) {
            DbEntity dbEntity = (DbEntity) dbEntities.next();

            // check if there are existing entities
            Collection existing = map.getMappedEntities(dbEntity);
            if (existing.size() > 0) {
                loadedEntities.addAll(existing);
                continue;
            }

            String objEntityName = NameConverter.underscoredToJava(
                    dbEntity.getName(),
                    true);
            // this loop will terminate even if no valid name is found
            // to prevent loader from looping forever (though such case is very unlikely)
            String baseName = objEntityName;
            for (int i = 1; i < 1000 && map.getObjEntity(objEntityName) != null; i++) {
                objEntityName = baseName + i;
            }

            ObjEntity objEntity = new ObjEntity(objEntityName);
            objEntity.setDbEntity(dbEntity);

            objEntity.setClassName(getGenericClassName() != null
                    ? getGenericClassName()
                    : packageName + objEntity.getName());
            map.addObjEntity(objEntity);
            loadedEntities.add(objEntity);

            // added entity without attributes or relationships...
            if (delegate != null) {
                delegate.objEntityAdded(objEntity);
            }
        }

        // update ObjEntity attributes and relationships
        new EntityMergeSupport(map).synchronizeWithDbEntities(loadedEntities);
    }

    /** Loads database relationships into a DataMap. */
    public void loadDbRelationships(DataMap map) throws SQLException {
        Iterator it = dbEntityList.iterator();
        while (it.hasNext()) {
            DbEntity pkEntity = (DbEntity) it.next();
            loadDbRelationships(pkEntity, map);
        }
    }

    private void loadDbRelationships(DbEntity pkEntity, DataMap map) throws SQLException {
        DatabaseMetaData md = getMetaData();
        String pkEntName = pkEntity.getName();

        // Get all the foreign keys referencing this table
        ResultSet rs = null;

        try {
            rs = md.getExportedKeys(
                    pkEntity.getCatalog(),
                    pkEntity.getSchema(),
                    pkEntity.getName());
        }
        catch (SQLException cay182Ex) {
            // Sybase-specific - the line above blows on VIEWS, see CAY-182.
            logObj.info("Error getting relationships for '"
                    + pkEntName
                    + "', ignoring.");
            return;
        }

        try {
            if (!rs.next())
                return;

            // these will be initailzed every time a new target entity
            // is found in the result set (which should be ordered by table name among
            // other things)
            DbRelationship forwardRelationship = null;
            DbRelationship reverseRelationship = null;
            DbEntity fkEntity = null;

            do {
                short keySeq = rs.getShort("KEY_SEQ");
                if (keySeq == 1) {

                    if (forwardRelationship != null) {
                        postprocessMasterDbRelationship(forwardRelationship);
                        forwardRelationship = null;
                    }

                    // start new entity
                    String fkEntityName = rs.getString("FKTABLE_NAME");

                    fkEntity = map.getDbEntity(fkEntityName);

                    if (fkEntity == null) {
                        logObj.info("FK warning: no entity found for name '"
                                + fkEntityName
                                + "'");
                    } else if (skippedEntities.contains(pkEntity) && skippedEntities.contains(fkEntity)) {
                        // cay-479 - don't load relationships between two
                        // skipped entities.
                        continue;
                    }
                    else {

                        // init relationship
                        forwardRelationship = new DbRelationship(DbLoader
                                .uniqueRelName(pkEntity, fkEntityName, true));

                        forwardRelationship.setSourceEntity(pkEntity);
                        forwardRelationship.setTargetEntity(fkEntity);
                        pkEntity.addRelationship(forwardRelationship);

                        reverseRelationship = new DbRelationship(uniqueRelName(
                                fkEntity,
                                pkEntName,
                                false));
                        reverseRelationship.setToMany(false);
                        reverseRelationship.setSourceEntity(fkEntity);
                        reverseRelationship.setTargetEntity(pkEntity);
                        fkEntity.addRelationship(reverseRelationship);
                    }
                }

                if (fkEntity != null) {
                    // Create and append joins
                    String pkName = rs.getString("PKCOLUMN_NAME");
                    String fkName = rs.getString("FKCOLUMN_NAME");

                    // skip invalid joins...
                    DbAttribute pkAtt = (DbAttribute) pkEntity.getAttribute(pkName);
                    if (pkAtt == null) {
                        logObj.info("no attribute for declared primary key: "
                                + pkName);
                        continue;
                    }

                    DbAttribute fkAtt = (DbAttribute) fkEntity.getAttribute(fkName);
                    if (fkAtt == null) {
                        logObj.info("no attribute for declared foreign key: "
                                + fkName);
                        continue;
                    }

                    forwardRelationship.addJoin(new DbJoin(
                            forwardRelationship,
                            pkName,
                            fkName));
                    reverseRelationship.addJoin(new DbJoin(
                            reverseRelationship,
                            fkName,
                            pkName));
                }
            } while (rs.next());

            if (forwardRelationship != null) {
                postprocessMasterDbRelationship(forwardRelationship);
                forwardRelationship = null;
            }

        }
        finally {
            rs.close();
        }
    }
    /**
     * Detects correct relationship multiplicity and "to dep pk" flag. Only called on
     * relationships from PK to FK, not the reverse ones.
     */
    protected void postprocessMasterDbRelationship(DbRelationship relationship) {
        boolean toPK = true;
        List joins = relationship.getJoins();

        Iterator joinsIt = joins.iterator();
        while (joinsIt.hasNext()) {
            DbJoin join = (DbJoin) joinsIt.next();
            if (!join.getTarget().isPrimaryKey()) {
                toPK = false;
                break;
            }

        }

        boolean toDependentPK = false;
        boolean toMany = true;

        if (toPK) {
            toDependentPK = true;
            if (((DbEntity) relationship.getTargetEntity()).getPrimaryKey().size() == joins
                    .size()) {
                toMany = false;
            }
        }

        // if this is really to-one we need to rename the relationship
        if (!toMany) {
            Entity source = relationship.getSourceEntity();
            source.removeRelationship(relationship.getName());
            relationship.setName(DbLoader.uniqueRelName(source, relationship
                    .getTargetEntityName(), false));
            source.addRelationship(relationship);
        }

        relationship.setToDependentPK(toDependentPK);
        relationship.setToMany(toMany);
    }

    private String[] getDefaultTableTypes() {
        String viewType = adapter.tableTypeForView();
        String tableType = adapter.tableTypeForTable();

        // use types that are not null
        List list = new ArrayList();
        if (viewType != null) {
            list.add(viewType);
        }
        if (tableType != null) {
            list.add(tableType);
        }

        String[] types = new String[list.size()];
        list.toArray(types);
        return types;
    }

    /**
     * Performs database reverse engineering and generates DataMap that contains default
     * mapping of the tables and views. By default will include regular tables and views.
     * 
     * @since 1.0.7
     */
    public DataMap loadDataMapFromDB(
            String schemaName,
            String tablePattern,
            DataMap dataMap) throws SQLException {

        String[] types = getDefaultTableTypes();
        if (types.length == 0) {
            throw new SQLException("No supported table types found.");
        }

        return loadDataMapFromDB(schemaName, tablePattern, types, dataMap);
    }

    /**
     * Performs database reverse engineering and generates DataMap object that contains
     * default mapping of the tables and views. Allows to limit types of tables to read.
     */
    public DataMap loadDataMapFromDB(
            String schemaName,
            String tablePattern,
            String[] tableTypes,
            DataMap dataMap) throws SQLException {

        if (tablePattern == null) {
            tablePattern = WILDCARD;
        }

        if (!loadDbEntities(
                dataMap,
                getTables(null, schemaName, tablePattern, tableTypes))) {
            return dataMap;
        }

        loadDbRelationships(dataMap);
        loadObjEntities(dataMap);
        return dataMap;
    }

    /**
     * Loads database stored procedures into the DataMap.
     * <p>
     * <i>As of 1.1 there is no boolean property or delegate method to make procedure
     * loading optional or to implement custom merging logic, so currently this method is
     * NOT CALLED from "loadDataMapFromDB" and should be invoked explicitly by the user.
     * </i>
     * </p>
     * 
     * @since 1.1
     */
    public void loadProceduresFromDB(
            String schemaPattern,
            String namePattern,
            DataMap dataMap) throws SQLException {

        Map procedures = null;

        // get procedures
        ResultSet rs = getMetaData().getProcedures(null, schemaPattern, namePattern);
        try {
            while (rs.next()) {
                String name = rs.getString("PROCEDURE_NAME");

                // TODO: this will be moved to Delegate...
                if (EXCLUDED_PROCEDURES.contains(name)) {
                    logObj.info("skipping Cayenne PK procedure: " + name);
                    continue;
                }

                String catalog = rs.getString("PROCEDURE_CAT");
                String schema = rs.getString("PROCEDURE_SCHEM");

                short type = rs.getShort("PROCEDURE_TYPE");

                Procedure procedure = new Procedure(name);
                procedure.setCatalog(catalog);
                procedure.setSchema(schema);

                switch (type) {
                    case DatabaseMetaData.procedureNoResult:
                    case DatabaseMetaData.procedureResultUnknown:
                        procedure.setReturningValue(false);
                        break;
                    case DatabaseMetaData.procedureReturnsResult:
                        procedure.setReturningValue(true);
                        break;
                }

                if (procedures == null) {
                    procedures = new HashMap();
                }

                procedures.put(procedure.getFullyQualifiedName(), procedure);
            }
        }
        finally {
            rs.close();
        }

        // if nothing found, return
        if (procedures == null) {
            return;
        }

        // get columns
        ResultSet columnsRS = getMetaData().getProcedureColumns(
                null,
                schemaPattern,
                namePattern,
                null);
        try {
            while (columnsRS.next()) {

                String schema = columnsRS.getString("PROCEDURE_SCHEM");
                String name = columnsRS.getString("PROCEDURE_NAME");

                // TODO: this will be moved to Delegate...
                if (EXCLUDED_PROCEDURES.contains(name)) {
                    continue;
                }

                String columnName = columnsRS.getString("COLUMN_NAME");
                short type = columnsRS.getShort("COLUMN_TYPE");

                String key = (schema != null) ? schema + '.' + name : name;

                // skip ResultSet columns, as they are not described in Cayenne procedures
                // yet...
                if (type == DatabaseMetaData.procedureColumnResult) {
                    logObj.debug("skipping ResultSet column: " + key + "." + columnName);
                }

                Procedure procedure = (Procedure) procedures.get(key);

                if (procedure == null) {
                    logObj.info("invalid procedure column, no procedure found: "
                            + key
                            + "."
                            + columnName);
                    continue;
                }

                ProcedureParameter column = new ProcedureParameter(columnName);

                if (columnName == null) {
                    if (type == DatabaseMetaData.procedureColumnReturn) {
                        logObj.debug("null column name, assuming result column: " + key);
                        column.setName("_return_value");
                    }
                    else {
                        logObj.info("invalid null column name, skipping column : " + key);
                        continue;
                    }
                }

                int columnType = columnsRS.getInt("DATA_TYPE");
                int columnSize = columnsRS.getInt("LENGTH");

                // ignore precision of non-decimal columns
                int decimalDigits = -1;
                if (TypesMapping.isDecimal(columnType)) {
                    decimalDigits = columnsRS.getShort("SCALE");
                    if (columnsRS.wasNull()) {
                        decimalDigits = -1;
                    }
                }

                switch (type) {
                    case DatabaseMetaData.procedureColumnIn:
                        column.setDirection(ProcedureParameter.IN_PARAMETER);
                        break;
                    case DatabaseMetaData.procedureColumnInOut:
                        column.setDirection(ProcedureParameter.IN_OUT_PARAMETER);
                        break;
                    case DatabaseMetaData.procedureColumnOut:
                        column.setDirection(ProcedureParameter.OUT_PARAMETER);
                        break;
                    case DatabaseMetaData.procedureColumnReturn:
                        procedure.setReturningValue(true);
                        break;
                }

                column.setMaxLength(columnSize);
                column.setPrecision(decimalDigits);
                column.setProcedure(procedure);
                column.setType(columnType);
                procedure.addCallParameter(column);
            }
        }
        finally {
            columnsRS.close();
        }

        Iterator it = procedures.values().iterator();
        while (it.hasNext()) {
            // overwrite existing procedures...

            Procedure procedure = (Procedure) it.next();
            dataMap.addProcedure(procedure);
        }
    }
}
