createcompiere.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.db;

import java.io.*;
import java.math.*;
import java.sql.*;
import java.util.*;
import java.util.logging.*;
import org.compiere.*;
import org.compiere.model.*;
import org.compiere.util.*;

/**
 *  Class to Create a new Compiere Database from a reference DB.
 *  <pre>
 *  - Create User
 *  - Create DDL (table, procedures, functions, etc.)
 *  </pre>
 *
 *  @author     Jorg Janke
 *  @version    $Id: CreateCompiere.java,v 1.9 2005/10/26 00:38:20 jjanke Exp $
 */
public class CreateCompiere
{
	/**
	 * 	Constructor
	 *	@param databaseType CompiereDatabase.TYPE_
	 *	@param databaseHost database host
	 *	@param databasePort database port 0 for default
	 *	@param systemPassword system password
	 */
	public CreateCompiere(String databaseType, String databaseHost, int databasePort,
		String systemPassword)
	{
		initDatabase(databaseType);
		m_databaseHost = databaseHost;
		if (databasePort == 0)
			m_databasePort = m_dbTarget.getStandardPort();
		else
			m_databasePort = databasePort;
		m_systemPassword = systemPassword;
		log.info(m_dbTarget.getName() + " on " + databaseHost);
	}   //  create

	/** Compiere Target Database */
	private CompiereDatabase 	m_dbTarget = null;
	/** Compiere Source Database */
	private CompiereDatabase 	m_dbSource = null;
	//
	private String				m_databaseHost = null;
	private int					m_databasePort = 0;
	private String 				m_systemPassword = null;
	private String 				m_compiereUser = null;
	private String 				m_compierePassword = null;
	private String 				m_databaseName = null;
	private String 				m_databaseDevice = null;
	//
	private Properties			m_ctx = new Properties ();
	/** Cached connection		*/
	private Connection			m_conn = null;
	
	/**	Logger	*/
	private static CLogger	log	= CLogger.getCLogger (CreateCompiere.class);
	
	/**
	 * 	Create Compiere Database
	 *	@param databaseType Database.DB_
	 */
	private void initDatabase(String databaseType)
	{
		try
		{
			for (int i = 0; i < Database.DB_NAMES.length; i++)
			{
				if (Database.DB_NAMES[i].equals (databaseType))
				{
					m_dbTarget = (CompiereDatabase)Database.DB_CLASSES[i].
						   newInstance ();
					break;
				}
			}
		}
		catch (Exception e)
		{
			log.severe(e.toString ());
			e.printStackTrace();
		}
		if (m_dbTarget == null)
			throw new IllegalStateException("No database: " + databaseType);
		
		//	Source Database
		m_dbSource = DB.getDatabase();
	}	//	createDatabase
	
	/**
	 * 	Clean Start - drop & re-create DB
	 */
	public void cleanStart()
	{
		Connection conn = getConnection(true, true);
		if (conn == null)
			throw new IllegalStateException("No Database");
		//
		dropDatabase(conn);
		createUser(conn);
		createDatabase(conn);
		//
		try
		{
			if (conn != null)
				conn.close();
		}
		catch (SQLException e2)
		{
			log.log(Level.SEVERE, "close connection", e2);
		}
		conn = null;
	}	//	cleanStart
	
	
	/**
	 *  Set Compiere User
	 *  @param compiereUser compiere id
	 *  @param compierePassword compiere password
	 */
	public void setCompiereUser (String compiereUser, String compierePassword)
	{
		m_compiereUser = compiereUser;
		m_compierePassword = compierePassword;
	}   //  setCompiereUser

	/**
	 *  Set Database Name
	 *  @param databaseName db name
	 *  @param databaseDevice device or table space
	 */
	public void setDatabaseName (String databaseName, String databaseDevice)
	{
		m_databaseName = databaseName;
		m_databaseDevice = databaseDevice;
	}   //  createDatabase

	
	/**
	 * 	Test Connection
	 *	@return connection
	 */
	public boolean testConnection()
	{
		String dbUrl = m_dbTarget.getConnectionURL (m_databaseHost, m_databasePort, 
			m_databaseName, m_dbTarget.getSystemUser());	//	compiere may not be defined yet
		log.info(dbUrl + " - " + m_dbTarget.getSystemUser() + "/" + m_systemPassword);
		try
		{
			Connection conn = m_dbTarget.getDriverConnection(dbUrl, m_dbTarget.getSystemUser(), m_systemPassword);
			//
			JDBCInfo info = new JDBCInfo(conn);
			if (CLogMgt.isLevelFinest())
			{
				info.listCatalogs();
				info.listSchemas();
			}
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, "test", e);
			return false;
		}

		return true;
	}	//	testConnection
	
	
	/**************************************************************************
	 *  Create User
	 *  @return true if success
	 */
	public boolean createUser (Connection sysConn)
	{
		log.info(m_compiereUser + "/" + m_compierePassword);
		return executeCommands(m_dbTarget.getCommands(CompiereDatabase.CMD_CREATE_USER), 
			sysConn, true, false);
	}   //  createUser

	/**
	 *  Create Database
	 *  @return true if success
	 */
	public boolean createDatabase (Connection sysConn)
	{
		log.info(m_databaseName + "(" + m_databaseDevice + ")");
		return executeCommands(m_dbTarget.getCommands(CompiereDatabase.CMD_CREATE_DATABASE), 
			sysConn, true, false);
	}   //  createDatabase	
	
	/**
	 *  Drop Database
	 *  @return true if success
	 */
	public boolean dropDatabase (Connection sysConn)
	{
		log.info(m_databaseName);
		return executeCommands(m_dbTarget.getCommands(CompiereDatabase.CMD_DROP_DATABASE), 
			sysConn, true, false);
	}   //  dropDatabase	
	
	
	/**
	 * 	Create Tables and copy data
	 * 	@param whereClause optional where clause
	 * 	@param dropFirst drop first
	 *	@return true if executed
	 */
	public boolean copy (String whereClause, boolean dropFirst)
	{
		log.info(whereClause);
		if (getConnection(false, true) == null)
			return false;
		//
		boolean success = true;
		int count = 0;
		ArrayList<String> list = new ArrayList<String>();
		String sql = "SELECT * FROM AD_Table";
		if (whereClause != null && whereClause.length() > 0)
			sql += " WHERE " + whereClause;
		sql += " ORDER BY TableName";
		//
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement (sql, null);
			DatabaseMetaData md = pstmt.getConnection().getMetaData();
			ResultSet rs = pstmt.executeQuery ();
			while (rs.next() && success)
			{
				M_Table table = new M_Table (m_ctx, rs, null);
				if (table.isView())
					continue;
				if (dropFirst)
				{
					executeCommands(new String[]
					    {"DROP TABLE " + table.getTableName()}, 
						m_conn, false, false);
				}
				//
				if (createTable (table, md))
				{
					list.add(table.getTableName());
					count++;
				}
				else
					success = false;
			}
			rs.close ();
			pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.log (Level.SEVERE, sql, e);
			success = false;
		}
		try
		{
			if (pstmt != null)
				pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			pstmt = null;
		}
		if (!success)
			return false;
		
		/**	Enable Contraints */
		enableConstraints(list);

		databaseBuild();
		
		log.info("#" + count);
		
		try
		{
			if (m_conn != null)
				m_conn.close();
		}
		catch (SQLException e2)
		{
			log.log(Level.SEVERE, "close connection", e2);
		}
		m_conn = null;
		return success;
	}	//	copy

	/**
	 * 	Execute Script
	 *	@return true if executed
	 */
	public boolean execute (File script)
	{
		return false;
	}	//	createTables
	

	
	/**
	 * 	Create Table
	 *	@param mTable table model
	 *	@param md meta data
	 *	@return true if created
	 */
	private boolean createTable (M_Table mTable, DatabaseMetaData md)
	{
		String tableName = mTable.getTableName();
		log.info(tableName);
		String catalog = m_dbSource.getCatalog();
		String schema = m_dbSource.getSchema();
		String table = tableName.toUpperCase();
		//	
		M_Column[] columns = mTable.getColumns(false);
		
		StringBuffer sb = new StringBuffer("CREATE TABLE ");
		sb.append(tableName).append(" (");
		try
		{
			//	Columns
			boolean first = true;
			ResultSet sourceColumns = md.getColumns(catalog, schema, table, null);
			while (sourceColumns.next())
			{
				sb.append(first ? "" : ", ");
				first = false;
				//	Case sensitive Column Name
				M_Column column = null;
				String columnName = sourceColumns.getString("COLUMN_NAME");
				for (int i = 0; i < columns.length; i++)
				{
					String cn = columns[i].getColumnName();
					if (cn.equalsIgnoreCase(columnName))
					{
						columnName = cn;
						column = columns[i];
						break;
					}
				}
				sb.append(columnName).append(" ");
				//	Data Type & Precision
				int sqlType = sourceColumns.getInt ("DATA_TYPE");		//	sql.Types
				String typeName = sourceColumns.getString ("TYPE_NAME");	//	DB Dependent
				int size = sourceColumns.getInt ("COLUMN_SIZE");
				int decDigits = sourceColumns.getInt("DECIMAL_DIGITS");
				if (sourceColumns.wasNull())
					decDigits = -1;
				if (typeName.equals("NUMBER"))
				{
					/** Oracle Style	*
					if (decDigits == -1)
						sb.append(typeName);
					else
						sb.append(typeName).append("(")
							.append(size).append(",").append(decDigits).append(")");
					/** Other DBs		*/
					int dt = column.getAD_Reference_ID();
					if (DisplayType.isID(dt))
						sb.append("INTEGER");
					else 
					{
						int scale = DisplayType.getDefaultPrecision(dt);
						sb.append("DECIMAL(")
							.append(18+scale).append(",").append(scale).append(")");
					}
				}					
				else if (typeName.equals("DATE") || typeName.equals("BLOB") || typeName.equals("CLOB"))
					sb.append(typeName);
				else if (typeName.equals("CHAR") || typeName.startsWith("VARCHAR"))
					sb.append(typeName).append("(").append(size).append(")");
				else if (typeName.startsWith("NCHAR") || typeName.startsWith("NVAR"))
					sb.append(typeName).append("(").append(size/2).append(")");
				else if (typeName.startsWith("TIMESTAMP"))
					sb.append("DATE");
				else 
					log.severe("Do not support data type " + typeName);
				//	Default
				String def = sourceColumns.getString("COLUMN_DEF");
				if (def != null)
					sb.append(" DEFAULT ").append(def);
				//	Null
				if (sourceColumns.getInt("NULLABLE") == DatabaseMetaData.columnNoNulls)
					sb.append(" NOT NULL");
				else
					sb.append(" NULL");
				
				//	Check Contraints