databaseutil.java

Sequoia ERP是一个真正的企业级开源ERP解决方案。它提供的模块包括：电子商务应用(e-commerce), POS系统(point of sales),知识管理,存货与仓库管理

/*
 * $Id: DatabaseUtil.java 6984 2006-03-11 16:15:13Z jonesde $
 *
 * Copyright (c) 2001-2005 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.io.Serializable;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import javolution.util.FastList;
import javolution.util.FastMap;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

import org.ofbiz.base.util.Debug;
import org.ofbiz.base.util.UtilTimer;
import org.ofbiz.base.util.UtilValidate;
import org.ofbiz.base.util.UtilXml;
import org.ofbiz.entity.GenericEntityException;
import org.ofbiz.entity.config.DatasourceInfo;
import org.ofbiz.entity.config.EntityConfigUtil;
import org.ofbiz.entity.model.ModelEntity;
import org.ofbiz.entity.model.ModelField;
import org.ofbiz.entity.model.ModelFieldType;
import org.ofbiz.entity.model.ModelFieldTypeReader;
import org.ofbiz.entity.model.ModelIndex;
import org.ofbiz.entity.model.ModelKeyMap;
import org.ofbiz.entity.model.ModelRelation;
import org.ofbiz.entity.model.ModelViewEntity;

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

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

    // OFBiz Connections
    protected ModelFieldTypeReader modelFieldTypeReader = null;
    protected DatasourceInfo datasourceInfo = null;
    protected String helperName = null;

    // Legacy Connections
    protected String connectionUrl = null;
    protected String driverName = null;
    protected String userName = null;
    protected String password = null;

    boolean isLegacy = false;

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

    // Legacy DatabaseUtil
    public DatabaseUtil(String driverName, String connectionUrl, String userName, String password) {
        this.driverName = driverName;
        this.connectionUrl = connectionUrl;
        this.userName = userName;
        this.password = password;
        this.isLegacy = true;
    }

    protected Connection getConnection() throws SQLException, GenericEntityException {
        Connection connection = null;
        if (!isLegacy) {
            connection = ConnectionFactory.getConnection(helperName);
        } else {
            connection = ConnectionFactory.getConnection(driverName, connectionUrl, null, userName, password);
        }

        if (connection == null) {
            if (!isLegacy) {
                throw new GenericEntityException("No connection available for helper named [" + helperName + "]");
            } else {
                throw new GenericEntityException("No connection avaialble for URL [" + connectionUrl + "]");
            }
        }
        connection.setAutoCommit(true);
        return connection;
    }

    public DatasourceInfo getDatasourceInfo() {
        return this.datasourceInfo;
    }

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

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

    public void checkDb(Map modelEntities, List messages, boolean addMissing) {
        checkDb(modelEntities, null, messages, datasourceInfo.checkPrimaryKeysOnStart, (datasourceInfo.useFks && datasourceInfo.checkForeignKeysOnStart), (datasourceInfo.useFkIndices && datasourceInfo.checkFkIndicesOnStart), addMissing);
    }

    public void checkDb(Map modelEntities, List colWrongSize, List messages, boolean checkPks, boolean checkFks, boolean checkFkIdx, boolean addMissing) {
        if (isLegacy) {
            throw new RuntimeException("Cannot run checkDb on a legacy database connection; configure a database helper (entityengine.xml)");
        }
        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, checkPks, 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 = FastList.newInstance();
        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 = FastMap.newInstance();
                    Iterator fieldIter = entity.getFieldsIterator();
                    while (fieldIter.hasNext()) {
                        ModelField field = (ModelField) fieldIter.next();
                        fieldColNames.put(field.getColName(), field);
                    }

                    Map colMap = (Map) colInfo.get(entity.getTableName(datasourceInfo));
                    if (colMap != null) {
                        Iterator colEntryIter = colMap.entrySet().iterator();
                        while (colEntryIter.hasNext()) {
                            Map.Entry colEntry = (Map.Entry) colEntryIter.next();
                            ColumnCheckInfo ccInfo = (ColumnCheckInfo) colEntry.getValue();

                            // -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 {