db.java

大家共享愉快, 共享愉快, 共享愉快, 共享愉快,共享愉快

/******************************************************************************
 * The contents of this file are subject to the   Compiere License  Version 1.1
 * ("License"); You may not use this file except in compliance with the License
 * You may obtain a copy of the License at http://www.compiere.org/license.html
 * Software distributed under the License is distributed on an  "AS IS"  basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
 * the specific language governing rights and limitations under the License.
 * The Original Code is Compiere ERP & CRM Smart Business Solution. The Initial
 * Developer of the Original Code is Jorg Janke. Portions created by Jorg Janke
 * are Copyright (C) 1999-2005 Jorg Janke.
 * All parts are Copyright (C) 1999-2005 ComPiere, Inc.  All Rights Reserved.
 * Contributor(s): ______________________________________.
 *****************************************************************************/
package org.compiere.util;

import java.io.*;
import java.math.*;
import java.rmi.*;
import java.sql.*;
import java.text.*;
import java.util.*;
import java.util.logging.*;
import javax.sql.*;
import javax.swing.*;
import oracle.jdbc.*;
//
import org.compiere.*;
import org.compiere.db.*;
import org.compiere.interfaces.*;
import org.compiere.model.*;
import org.compiere.process.*;


/**
 *  General Database Interface
 *
 *  @author     Jorg Janke
 *  @version    $Id: DB.java,v 1.113 2005/12/31 06:33:21 jjanke Exp $
 */
public final class DB
{
	/** Connection Descriptor           */
	private static CConnection      s_cc = null;
	/** Connection Cache r/o            */
	private static Connection[]		s_connections = null;
	/** Connection Cache Size           */
	private static int              s_conCacheSize = Ini.isClient() ? 3 : 3;
	/** Connection counter              */
	private static int              s_conCount = 0;
	/** Connection r/w                  */
	private static Connection		s_connectionRW = null;
	/** Connection r/w for ID           */
	private static Connection		s_connectionID = null;
	/**	Logger							*/
	private static CLogger			log = CLogger.getCLogger (DB.class);

	/** SQL Statement Separator "; "	*/
	public static final String SQLSTATEMENT_SEPARATOR = "; ";
	
	
	/**************************************************************************
	 * 	Check need for post Upgrade
	 *	@return true if post upgrade ran - false if there was no need
	 */
	public static boolean afterMigration (Properties ctx)
	{
		//	UPDATE AD_System SET IsJustMigrated='Y'
		MSystem system = MSystem.get(ctx); 
		if (!system.isJustMigrated())
			return false;
		
		//	Role update
		log.info("Role");
		String sql = "SELECT * FROM AD_Role";
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement (sql, null);
			ResultSet rs = pstmt.executeQuery ();
			while (rs.next ())
			{
				MRole role = new MRole (ctx, rs, null);
				role.updateAccessRecords();
			}
			rs.close ();
			pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, "(1)", e);
		}
		try
		{
			if (pstmt != null)
				pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			pstmt = null;
		}
		
		//	Language check
		log.info("Language");
		MLanguage.maintain(ctx);
		
		//	Sequence check
		log.info("Sequence");
		SequenceCheck.validate(ctx);
		
		//	Costing Setup
		log.info("Costing");
		MAcctSchema[] ass = MAcctSchema.getClientAcctSchema(ctx, 0);
		for (int i = 0; i < ass.length; i++)
		{
			ass[i].checkCosting();
			ass[i].save();
		}
		
		try
		{
			Class clazz = Class.forName("org.compiere.MigrateData");
			clazz.newInstance();
		}
		catch (Exception e)
		{
			log.log (Level.SEVERE, "Data", e);
		}
		
		//	Reset Flag
		system.setIsJustMigrated(false);
		return system.save();
	}	//	afterMigration
	
	/**
	 * 	Update Mail Settings for System Client and System User
	 */
	public static void updateMail()
	{
		//	Get Property File
		String envName = Ini.getCompiereHome();
		if (envName == null)
			return;
		envName += File.separator + "CompiereEnv.properties";
		File envFile = new File(envName);
		if (!envFile.exists())
			return;
		
		Properties env = new Properties();
		try
		{
			FileInputStream in = new FileInputStream(envFile);
			env.load(in);
			in.close();
		}
		catch (Exception e)
		{
			return;
		}
		String updated = env.getProperty("COMPIERE_MAIL_UPDATED");
		if (updated != null || updated.equals("Y"))
			return;
		
		//	See org.compiere.install.ConfigurationData
		String server = env.getProperty("COMPIERE_MAIL_SERVER");
		if (server == null || server.length() == 0)
			return;
		String adminEMail = env.getProperty("COMPIERE_ADMIN_EMAIL");
		if (adminEMail == null || adminEMail.length() == 0)
			return;
		String mailUser = env.getProperty("COMPIERE_MAIL_USER");
		if (mailUser == null || mailUser.length() == 0)
			return;
		String mailPassword = env.getProperty("COMPIERE_MAIL_PASSWORD");
	//	if (mailPassword == null || mailPassword.length() == 0)
	//		return;
		//
		StringBuffer sql = new StringBuffer("UPDATE AD_Client SET")
			.append(" SMTPHost=").append(DB.TO_STRING(server))
			.append(", RequestEMail=").append(DB.TO_STRING(adminEMail))
			.append(", RequestUser=").append(DB.TO_STRING(mailUser))
			.append(", RequestUserPW=").append(DB.TO_STRING(mailPassword))
			.append(", IsSMTPAuthorization='Y' WHERE AD_Client_ID=0");
		int no = DB.executeUpdate(sql.toString(), null);
		//
		sql = new StringBuffer("UPDATE AD_User SET ")
			.append(" EMail=").append(DB.TO_STRING(adminEMail))
			.append(", EMailUser=").append(DB.TO_STRING(mailUser))
			.append(", EMailUserPW=").append(DB.TO_STRING(mailUser))
			.append(" WHERE AD_User_ID IN (0,100)");
		no = DB.executeUpdate(sql.toString(), null);
		//
		try
		{
			env.setProperty("COMPIERE_MAIL_UPDATED", "Y");
			FileOutputStream out = new FileOutputStream(envFile);
			env.store(out, "");
			out.flush();
			out.close();
		}
		catch (Exception e)
		{
		}

	}	//	updateMail
	
	/**************************************************************************
	 *  Set connection
	 *  @param cc connection
	 */
	public static void setDBTarget (CConnection cc)
	{
		if (cc == null)
			throw new IllegalArgumentException("Connection is NULL");

		if (s_cc != null && s_cc.equals(cc))
			return;
		
		DB.closeTarget();
		//
		if (s_cc == null)
			s_cc = cc;
		synchronized (s_cc)    //  use as mutex
		{
			s_cc = cc;
			s_connections = null;
			s_connectionRW = null;
		}
		s_cc.setDataSource();
		log.config(s_cc + " - DS=" + s_cc.isDataSource());
	//	Trace.printStack();
	}   //  setDBTarget

	/**
	 *  Is there a connection to the database ?
	 *  @return true, if connected to database
	 */
	public static boolean isConnected()
	{
		try
		{
			getConnectionRW();	//	try to get a connection
			return true;
		}
		catch (Exception e)
		{
		}
		return false;
	}   //  isConnected

	/**
	 *	Return (pooled) r/w AutoCommit, Serializable connection.
	 *	For Transaction control use Trx.getConnection()
	 *  @return Connection (r/w)
	 */
	public static Connection getConnectionRW ()
	{
		//	check health of connection
		try
		{
			if (s_connectionRW == null)
				;
			else if (s_connectionRW.isClosed())
			{
				log.finest("Closed");
				s_connectionRW = null;
			}
			else if (s_connectionRW instanceof OracleConnection && ((OracleConnection)s_connectionRW).pingDatabase(1) < 0)
			{
				log.warning("No ping");
				s_connectionRW = null;
			}
			else
			{
				if (s_connectionRW.getTransactionIsolation() != Connection.TRANSACTION_READ_COMMITTED)
					s_connectionRW.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
			}
		}
		catch (Exception e)
		{
			s_connectionRW = null;
		}
		//	Get new
		if (s_connectionRW == null)
		{
			s_connectionRW = s_cc.getConnection (true, Connection.TRANSACTION_READ_COMMITTED);
			log.finest("Con=" + s_connectionRW);
		}
		if (s_connectionRW == null)
			throw new UnsupportedOperationException("No DBConnection");
		//
	//	System.err.println ("DB.getConnectionRW - " + s_connectionRW); 
	//	Trace.printStack();
		return s_connectionRW;
	}   //  getConnectionRW

	/**
	 *	Return everytime a new r/w no AutoCommit, Serializable connection.
	 *	To be used to ID
	 *  @return Connection (r/w)
	 */
	public static Connection getConnectionID ()
	{
		if (s_connectionID != null)
		{
			try
			{
				if (s_connectionID.isClosed())
					s_connectionID = null;
			}
			catch (Exception e)
			{
				s_connectionID = null;
			}
		}
		if (s_connectionID == null)
		{
			s_connectionID = s_cc.getConnection (false, Connection.TRANSACTION_READ_COMMITTED);
		}
		if (s_connectionID == null)
			throw new UnsupportedOperationException("No DBConnection");
		log.log(Level.ALL, s_connectionID.toString());
		return s_connectionID;
	}   //  getConnectionID

	/**
	 *	Return read committed, read/only from pool.
	 *  @return Connection (r/o)
	 */
	public static Connection getConnectionRO ()
	{
		try
		{
			synchronized (s_cc)    //  use as mutex as s_connection is null the first time
			{
				if (s_connections == null)
					s_connections = createConnections (Connection.TRANSACTION_READ_COMMITTED);     //  see below
			}
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, "RO", e);
		}

		//  check health of connection
		int pos = s_conCount++;
		int connectionNo = pos % s_conCacheSize;
		Connection connection = s_connections[connectionNo];
		try
		{
			if (connection == null)
				;
			else if (connection.isClosed())
			{
			//	RowSet.close also closes connection!
			//	System.out.println("DB.getConnectionRO - closed #" + connectionNo);
				connection = null;
			}
			else if (connection instanceof OracleConnection && ((OracleConnection)connection).pingDatabase(1) < 0)
			{
				log.warning("No ping #" + connectionNo);
				connection = null;
			}
			else
			{
				if (!connection.isReadOnly())
					connection.setReadOnly(true);
				if (connection.getTransactionIsolation() != Connection.TRANSACTION_READ_COMMITTED)
					connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
			}
		}
		catch (Exception e)
		{
			log.severe("#" + connectionNo + " - " + e.toString());
			connection = null;
		}
		//	Get new
		if (connection == null)
		{
			log.finest("Replacing connection #" + connectionNo);
			connection = s_cc.getConnection (true, Connection.TRANSACTION_READ_COMMITTED); //  see above
			try
			{
				if (connection != null)
					connection.setReadOnly(true);
			}
			catch (Exception e)
			{
				log.severe("Cannot set to R/O - " + e);
			} 
			s_connections[connectionNo] = connection;
		}
		if (connection == null)
			throw new UnsupportedOperationException("DB.getConnectionRO - @NoDBConnection@");
		log.log(Level.ALL, "#" + connectionNo + " - " + connection);
	//	System.err.println ("DB.getConnectionRO - " + connection); 
		return connection;
	}	//	getConnectionRO

	/**
	 *	Create new Connection.
	 *  The connection must be closed explicitly by the application
	 *
	 *  @param autoCommit auto commit
	 *  @param trxLevel - Connection.TRANSACTION_READ_UNCOMMITTED, Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_REPEATABLE_READ, or Connection.TRANSACTION_READ_COMMITTED.
	 *  @return Connection connection
	 */
	public static Connection createConnection (boolean autoCommit, int trxLevel)
	{
		Connection conn = s_cc.getConnection (autoCommit, trxLevel);
		if (CLogMgt.isLevelFinest())
		{
			/**
			try
			{
				log.finest(s_cc.getConnectionURL()
					+ ", UserID=" + s_cc.getDbUid() 
					+ ", AutoCommit=" + conn.getAutoCommit() + " (" + autoCommit + ")"
					+ ", TrxIso=" + conn.getTransactionIsolation() + "( " + trxLevel + ")");
			}
			catch (Exception e)
			{
			}
			**/
		}
		return conn;
	}	//	createConnection

	/**
	 *	Create new set of r/o Connections.
	 *  R/O connection might not be supported by DB
	 *
	 *  @param trxLevel - Connection.TRANSACTION_READ_UNCOMMITTED, Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_REPEATABLE_READ, or Connection.TRANSACTION_READ_COMMITTED.
	 *  @return Array of Connections (size based on s_conCacheSize)
	 */
	private static Connection[] createConnections (int trxLevel)
	{
		log.finest("(" + s_conCacheSize + ") " + s_cc.getConnectionURL()
			+ ", UserID=" + s_cc.getDbUid() 
			+ ", TrxLevel=" + CConnection.getTransactionIsolationInfo(trxLevel));
		Connection cons[] = new Connection[s_conCacheSize];
		try
		{