databaseutil.java

国外的一套开源CRM

/*
 * $Id: DatabaseUtil.java,v 1.12 2004/02/03 08:14:41 jonesde Exp $
 *
 * Copyright (c) 2001, 2002 The Open For Business Project - www.ofbiz.org
 *
 * Permission is hereby granted, free of charge, to any person obtaining a
 * copy of this software and associated documentation files (the "Software"),
 * to deal in the Software without restriction, including without limitation
 * the rights to use, copy, modify, merge, publish, distribute, sublicense,
 * and/or sell copies of the Software, and to permit persons to whom the
 * Software is furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included
 * in all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
 * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
 * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
 * IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
 * CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT
 * OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR
 * THE USE OR OTHER DEALINGS IN THE SOFTWARE.
 */
package org.ofbiz.entity.jdbc;

import java.sql.*;
import java.util.*;

import org.ofbiz.base.util.*;
import org.ofbiz.entity.*;
import org.ofbiz.entity.config.*;
import org.ofbiz.entity.model.*;

/**
 * Utilities for Entity Database Maintenance
 *
 * @author     <a href="mailto:[e-mail]">David E. Jones</a>
 * @version    $Revision: 1.12 $
 * @since      2.0
 */
public class DatabaseUtil {

    public static final String module = DatabaseUtil.class.getName();

    protected String helperName;
    protected ModelFieldTypeReader modelFieldTypeReader;
    protected EntityConfigUtil.DatasourceInfo datasourceInfo;

    public DatabaseUtil(String helperName) {
        this.helperName = helperName;
        this.modelFieldTypeReader = ModelFieldTypeReader.getModelFieldTypeReader(helperName);
        this.datasourceInfo = EntityConfigUtil.getDatasourceInfo(helperName);
    }

    protected Connection getConnection() throws SQLException, GenericEntityException {
        Connection connection = ConnectionFactory.getConnection(helperName);
        connection.setAutoCommit(true);
        return connection;
    }
    
    public EntityConfigUtil.DatasourceInfo getDatasourceInfo() {
        return this.datasourceInfo;
    }

    /* ====================================================================== */

    /* ====================================================================== */

    public void checkDb(Map modelEntities, List messages, boolean addMissing) {
        UtilTimer timer = new UtilTimer();
        timer.timerString("Start - Before Get Database Meta Data");

        // get ALL tables from this database
        TreeSet tableNames = this.getTableNames(messages);
        TreeSet fkTableNames = tableNames == null ? null : new TreeSet(tableNames);
        TreeSet indexTableNames = tableNames == null ? null : new TreeSet(tableNames);

        if (tableNames == null) {
            String message = "Could not get table name information from the database, aborting.";
            if (messages != null) messages.add(message);
            Debug.logError(message, module);
            return;
        }
        timer.timerString("After Get All Table Names");

        // get ALL column info, put into hashmap by table name
        Map colInfo = this.getColumnInfo(tableNames, messages);
        if (colInfo == null) {
            String message = "Could not get column information from the database, aborting.";
            if (messages != null) messages.add(message);
            Debug.logError(message, module);
            return;
        }
        timer.timerString("After Get All Column Info");

        // -make sure all entities have a corresponding table
        // -list all tables that do not have a corresponding entity
        // -display message if number of table columns does not match number of entity fields
        // -list all columns that do not have a corresponding field
        // -make sure each corresponding column is of the correct type
        // -list all fields that do not have a corresponding column

        timer.timerString("Before Individual Table/Column Check");

        ArrayList modelEntityList = new ArrayList(modelEntities.values());
        // sort using compareTo method on ModelEntity
        Collections.sort(modelEntityList);
        Iterator modelEntityIter = modelEntityList.iterator();
        int curEnt = 0;
        int totalEnt = modelEntityList.size();
        List entitiesAdded = new LinkedList();
        while (modelEntityIter.hasNext()) {
            curEnt++;
            ModelEntity entity = (ModelEntity) modelEntityIter.next();

            // if this is a view entity, do not check it...
            if (entity instanceof ModelViewEntity) {
                String entMessage = "(" + timer.timeSinceLast() + "ms) NOT Checking #" + curEnt + "/" + totalEnt + " View Entity " + entity.getEntityName();
                Debug.logVerbose(entMessage, module);
                if (messages != null) messages.add(entMessage);
                continue;
            }

            String entMessage = "(" + timer.timeSinceLast() + "ms) Checking #" + curEnt + "/" + totalEnt +
                " Entity " + entity.getEntityName() + " with table " + entity.getTableName(datasourceInfo);

            Debug.logVerbose(entMessage, module);
            if (messages != null) messages.add(entMessage);

            // -make sure all entities have a corresponding table
            if (tableNames.contains(entity.getTableName(datasourceInfo))) {
                tableNames.remove(entity.getTableName(datasourceInfo));

                if (colInfo != null) {
                    Map fieldColNames = new HashMap();
                    for (int fnum = 0; fnum < entity.getFieldsSize(); fnum++) {
                        ModelField field = entity.getField(fnum);
                        fieldColNames.put(field.getColName(), field);
                    }

                    List colList = (List) colInfo.get(entity.getTableName(datasourceInfo));
                    int numCols = 0;

                    if (colList != null) {
                        for (; numCols < colList.size(); numCols++) {
                            ColumnCheckInfo ccInfo = (ColumnCheckInfo) colList.get(numCols);

                            // -list all columns that do not have a corresponding field
                            if (fieldColNames.containsKey(ccInfo.columnName)) {
                                ModelField field = null;

                                field = (ModelField) fieldColNames.remove(ccInfo.columnName);
                                ModelFieldType modelFieldType = modelFieldTypeReader.getModelFieldType(field.getType());

                                if (modelFieldType != null) {
                                    // make sure each corresponding column is of the correct type
                                    String fullTypeStr = modelFieldType.getSqlType();
                                    String typeName;
                                    int columnSize = -1;
                                    int decimalDigits = -1;

                                    int openParen = fullTypeStr.indexOf('(');
                                    int closeParen = fullTypeStr.indexOf(')');
                                    int comma = fullTypeStr.indexOf(',');

                                    if (openParen > 0 && closeParen > 0 && closeParen > openParen) {
                                        typeName = fullTypeStr.substring(0, openParen);
                                        if (comma > 0 && comma > openParen && comma < closeParen) {
                                            String csStr = fullTypeStr.substring(openParen + 1, comma);
                                            try {
                                                columnSize = Integer.parseInt(csStr);
                                            } catch (NumberFormatException e) {
                                                Debug.logError(e, module);
                                            }

                                            String ddStr = fullTypeStr.substring(comma + 1, closeParen);
                                            try {
                                                decimalDigits = Integer.parseInt(ddStr);
                                            } catch (NumberFormatException e) {
                                                Debug.logError(e, module);
                                            }
                                        } else {
                                            String csStr = fullTypeStr.substring(openParen + 1, closeParen);
                                            try {
                                                columnSize = Integer.parseInt(csStr);
                                            } catch (NumberFormatException e) {
                                                Debug.logError(e, module);
                                            }
                                        }
                                    } else {
                                        typeName = fullTypeStr;
                                    }

                                    // override the default typeName with the sqlTypeAlias if it is specified
                                    if (UtilValidate.isNotEmpty(modelFieldType.getSqlTypeAlias())) {
                                        typeName = modelFieldType.getSqlTypeAlias();
                                    }

                                    // NOTE: this may need a toUpperCase in some cases, keep an eye on it, okay just compare with ignore case
                                    if (!ccInfo.typeName.equalsIgnoreCase(typeName)) {
                                        String message = "WARNING: Column \"" + ccInfo.columnName + "\" of table \"" + entity.getTableName(datasourceInfo) + "\" of entity \"" +
                                            entity.getEntityName() + "\" is of type \"" + ccInfo.typeName + "\" in the database, but is defined as type \"" +
                                            typeName + "\" in the entity definition.";
                                        Debug.logError(message, module);
                                        if (messages != null) messages.add(message);
                                    }
                                    if (columnSize != -1 && ccInfo.columnSize != -1 && columnSize != ccInfo.columnSize) {
                                        String message = "WARNING: Column \"" + ccInfo.columnName + "\" of table \"" + entity.getTableName(datasourceInfo) + "\" of entity \"" +
                                            entity.getEntityName() + "\" has a column size of \"" + ccInfo.columnSize +
                                            "\" in the database, but is defined to have a column size of \"" + columnSize + "\" in the entity definition.";
                                        Debug.logWarning(message, module);
                                        if (messages != null) messages.add(message);
                                    }
                                    if (decimalDigits != -1 && decimalDigits != ccInfo.decimalDigits) {
                                        String message = "WARNING: Column \"" + ccInfo.columnName + "\" of table \"" + entity.getTableName(datasourceInfo) + "\" of entity \"" +
                                            entity.getEntityName() + "\" has a decimalDigits of \"" + ccInfo.decimalDigits +
                                            "\" in the database, but is defined to have a decimalDigits of \"" + decimalDigits + "\" in the entity definition.";
                                        Debug.logWarning(message, module);
                                        if (messages != null) messages.add(message);
                                    }
                                } else {
                                    String message = "Column \"" + ccInfo.columnName + "\" of table \"" + entity.getTableName(datasourceInfo) + "\" of entity \"" + entity.getEntityName() +
                                        "\" has a field type name of \"" + field.getType() + "\" which is not found in the field type definitions";
                                    Debug.logError(message, module);
                                    if (messages != null) messages.add(message);
                                }
                            } else {
                                String message = "Column \"" + ccInfo.columnName + "\" of table \"" + entity.getTableName(datasourceInfo) + "\" of entity \"" + entity.getEntityName() + "\" exists in the database but has no corresponding field";
                                Debug.logWarning(message, module);
                                if (messages != null) messages.add(message);
                            }
                        }
                    }

                    // -display message if number of table columns does not match number of entity fields
                    if (numCols != entity.getFieldsSize()) {
                        String message = "Entity \"" + entity.getEntityName() + "\" has " + entity.getFieldsSize() + " fields but table \"" + entity.getTableName(datasourceInfo) + "\" has " +
                            numCols + " columns.";
                        Debug.logWarning(message, module);