Author: tolusha Date: 2010-03-19 11:20:47 -0400 (Fri, 19 Mar 2010) New Revision: 2086 Added: core/trunk/exo.core.component.database/src/main/java/org/exoplatform/services/database/creator/DBConnectionInfo.java Modified: core/trunk/exo.core.component.database/src/main/java/org/exoplatform/services/database/creator/DBScriptExecutor.java core/trunk/exo.core.component.database/src/main/java/org/exoplatform/services/database/creator/DBScriptExecutorException.java core/trunk/exo.core.component.database/src/test/java/org/exoplatform/services/database/TestDBScriptExecutor.java core/trunk/exo.core.component.database/src/test/resources/conf/standalone/test-configuration.xml Log: EXOJCR-573: DBScriptExecutor small refactoring Added: core/trunk/exo.core.component.database/src/main/java/org/exoplatform/services/database/creator/DBConnectionInfo.java =================================================================== --- core/trunk/exo.core.component.database/src/main/java/org/exoplatform/services/database/creator/DBConnectionInfo.java (rev 0) +++ core/trunk/exo.core.component.database/src/main/java/org/exoplatform/services/database/creator/DBConnectionInfo.java 2010-03-19 15:20:47 UTC (rev 2086) @@ -0,0 +1,101 @@ +/* + * Copyright (C) 2010 eXo Platform SAS. + * + * This is free software; you can redistribute it and/or modify it + * under the terms of the GNU Lesser General Public License as + * published by the Free Software Foundation; either version 2.1 of + * the License, or (at your option) any later version. + * + * This software is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with this software; if not, write to the Free + * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA + * 02110-1301 USA, or see the FSF site: http://www.fsf.org. + */ +package org.exoplatform.services.database.creator; + +/** + * Class contains needed database connection information. + * + * @author <a href=&quot;anatoliy.bazko(a)exoplatform.org&quot;&gt;Anatoliy Bazko</a> + * @version $Id: DBConnectionInfo.java 111 2010-11-11 11:11:11Z tolusha $ + */ +public class DBConnectionInfo +{ + /** + * Driver class name. + */ + private final String driver; + + /** + * DB connection url; + */ + private final String url; + + /** + * DB connection user name; + */ + private final String username; + + /** + * User's password. + */ + private final String password; + + /** + * DBConnectionInfo constructor. + * + * @param driver + * driver class name + * @param url + * db connection url + * @param username + * db connection user name + * @param password + * user's password + */ + public DBConnectionInfo(String driver, String url, String username, String password) + { + this.driver = driver; + this.url = url; + this.username = username; + this.password = password; + } + + /** + * @return the driver + */ + public String getDriver() + { + return driver; + } + + /** + * @return the url + */ + public String getUrl() + { + return url; + } + + /** + * @return the username + */ + public String getUsername() + { + return username; + } + + /** + * @return the password + */ + public String getPassword() + { + return password; + } + +} Modified: core/trunk/exo.core.component.database/src/main/java/org/exoplatform/services/database/creator/DBScriptExecutor.java =================================================================== --- core/trunk/exo.core.component.database/src/main/java/org/exoplatform/services/database/creator/DBScriptExecutor.java 2010-03-19 15:19:01 UTC (rev 2085) +++ core/trunk/exo.core.component.database/src/main/java/org/exoplatform/services/database/creator/DBScriptExecutor.java 2010-03-19 15:20:47 UTC (rev 2086) @@ -29,6 +29,8 @@ import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; +import java.sql.Statement; +import java.util.Properties; /** * @author <a href=&quot;anatoliy.bazko(a)exoplatform.org&quot;&gt;Anatoliy Bazko</a> @@ -58,27 +60,32 @@ protected final String driver; /** - * Database url. + * Server url. */ - protected final String url; + protected final String serverUrl; /** - * SA user name. + * User name with administrative rights for connection to server. */ - protected final String userName; + protected final String adminName; /** - * SA user's password. + * User's password. */ - protected final String password; + protected final String adminPwd; /** - * DB script creation. + * Internal login connection property needed for Oracle. */ + protected final String internal_logon; + + /** + * DDL script database creation. + */ protected final String dbScript; /** - * User name for new DB. + * User name for new database. */ protected final String dbUserName; @@ -105,17 +112,37 @@ if (prop != null) { this.driver = prop.getProperty("driverClassName"); - this.url = prop.getProperty("url"); - this.userName = prop.getProperty("username"); - this.password = prop.getProperty("password"); + if (driver == null) + { + throw new ConfigurationException("driverClassName expected in db-connection properties section"); + } + + this.serverUrl = prop.getProperty("url"); + if (serverUrl == null) + { + throw new ConfigurationException("url expected in db-connection properties section"); + } + + this.adminName = prop.getProperty("username"); + if (adminName == null) + { + throw new ConfigurationException("username expected in db-connection properties section"); + } + + this.adminPwd = prop.getProperty("password"); + if (adminPwd == null) + { + throw new ConfigurationException("password expected in db-connection properties section"); + } + + this.internal_logon = prop.getProperty("internal_logon"); } else { throw new ConfigurationException("db-connection properties expected in initializations parameters"); } - prop = params.getPropertiesParam("script-execution"); - + prop = params.getPropertiesParam("db-creation"); if (prop != null) { String scriptPath = prop.getProperty("scriptPath"); @@ -132,75 +159,88 @@ } else { - throw new ConfigurationException("scriptPath expected in initializations parameters"); + throw new ConfigurationException("scriptPath expected in db-creation properties section"); } this.dbUserName = prop.getProperty("username"); + if (dbUserName == null) + { + throw new ConfigurationException("username expected in db-creation properties section"); + } + this.dbPassword = prop.getProperty("password"); + if (dbPassword == null) + { + throw new ConfigurationException("password expected in db-creation properties section"); + } } else { - throw new ConfigurationException("db-creation properties expected in initializations parameters"); + throw new ConfigurationException("db-creation properties expected in initializations parameters"); } - } /** * Execute DDL script for new database creation. Database name are passed as parameter, - * user name and password are passed via configuration. In SQL script database name, user name + * user name and password are passed via configuration. In script database name, user name * and password defined via templates as ${database}, ${username} and ${password} respectively. + * At execution time method replaces templates by real values. * * @param dbName * new database name * @throws DBScriptExecutorException * if any error occurs */ - public void execute(String dbName) throws DBScriptExecutorException + public DBConnectionInfo createDatabase(String dbName) throws DBScriptExecutorException { Connection conn = null; try { Class.forName(driver); - conn = DriverManager.getConnection(url, userName, password); + + Properties props = new java.util.Properties(); + props.put("user", adminName); + props.put("password", adminPwd); + if (internal_logon != null) + { + props.put("internal_logon", internal_logon); + } + conn = DriverManager.getConnection(serverUrl, props); } catch (SQLException e) { - throw new DBScriptExecutorException("Can't establish the JDBC connection to database " + url, e); + throw new DBScriptExecutorException("Can't establish the JDBC connection to database " + serverUrl, e); } catch (ClassNotFoundException e) { throw new DBScriptExecutorException("Can't load the JDBC driver " + driver, e); } + String dbProductName; try { - conn.setAutoCommit(false); + dbProductName = conn.getMetaData().getDatabaseProductName(); - for (String scr : dbScript.split(";")) + if (dbProductName.startsWith("Microsoft SQL Server") || dbProductName.startsWith("Adaptive Server Anywhere") + || dbProductName.equals("Sybase SQL Server") || dbProductName.equals("Adaptive Server Enterprise")) { - scr = scr.replace(DATABASE_TEMPLATE, dbName); - scr = scr.replace(USERNAME_TEMPLATE, dbUserName); - scr = scr.replace(PASSWORD_TEMPLATE, dbPassword); - - String s = cleanWhitespaces(scr.trim()); - if (s.length() > 0) - { - conn.createStatement().executeUpdate(s); - } + executeAutoCommitMode(conn, dbName); } - conn.commit(); + else + { + executeBatchMode(conn, dbName); + } } catch (SQLException e) { - try + String errorTrace = ""; + while (e != null) { - conn.rollback(); + errorTrace += e.getMessage() + "; "; + e = e.getNextException(); } - catch (SQLException e1) - { - throw new DBScriptExecutorException("Can't perform rollback", e1); - } - throw new DBScriptExecutorException("Can't execute SQL script", e); + + throw new DBScriptExecutorException("Can't execute SQL script " + errorTrace); } finally { @@ -213,9 +253,83 @@ throw new DBScriptExecutorException("Can't close connection", e); } } + + // try to solve database url connection depending on specific database + String dbUrl = serverUrl; + if (dbProductName.startsWith("Microsoft SQL Server")) + { + dbUrl = dbUrl + (dbUrl.endsWith(";") ? "" : ";") + "databaseName=" + dbName + ";"; + } + else if (dbProductName.equals("Oracle")) + { + // do nothing + } + else + { + dbUrl = dbUrl + (dbUrl.endsWith("/") ? "" : "/") + dbName; + } + + return new DBConnectionInfo(driver, dbUrl, dbUserName, dbPassword); } /** + * Executes DDL script in generic batch mode. + * + * @param conn + * connection to server + * @param dbName + * database name + * @throws SQLException + * if any errors occurs + */ + private void executeBatchMode(Connection conn, String dbName) throws SQLException + { + Statement statement = conn.createStatement(); + for (String scr : dbScript.split(";")) + { + scr = scr.replace(DATABASE_TEMPLATE, dbName); + scr = scr.replace(USERNAME_TEMPLATE, dbUserName); + scr = scr.replace(PASSWORD_TEMPLATE, dbPassword); + + String s = cleanWhitespaces(scr.trim()); + if (s.length() > 0) + { + statement.addBatch(s); + } + } + statement.executeBatch(); + } + + /** + * Executes DDL script with autocommit mode set true. Actually need for MSSQL and Sybase database servers. + * After execution "create database" command newly created database not available for "use" command and + * therefore you can't create user inside. + * + * @param conn + * connection to server + * @param dbName + * database name + * @throws SQLException + * if any errors occurs + */ + private void executeAutoCommitMode(Connection conn, String dbName) throws SQLException + { + conn.setAutoCommit(true); + for (String scr : dbScript.split(";")) + { + scr = scr.replace(DATABASE_TEMPLATE, dbName); + scr = scr.replace(USERNAME_TEMPLATE, dbUserName); + scr = scr.replace(PASSWORD_TEMPLATE, dbPassword); + + String s = cleanWhitespaces(scr.trim()); + if (s.length() > 0) + { + conn.createStatement().executeUpdate(s); + } + } + } + + /** * Read SQL script from file resource. */ protected String readScriptResource(String path) throws IOException Modified: core/trunk/exo.core.component.database/src/main/java/org/exoplatform/services/database/creator/DBScriptExecutorException.java =================================================================== --- core/trunk/exo.core.component.database/src/main/java/org/exoplatform/services/database/creator/DBScriptExecutorException.java 2010-03-19 15:19:01 UTC (rev 2085) +++ core/trunk/exo.core.component.database/src/main/java/org/exoplatform/services/database/creator/DBScriptExecutorException.java 2010-03-19 15:20:47 UTC (rev 2086) @@ -40,4 +40,12 @@ { super(message, e); } + + /** + * DBCreationException constructor. + */ + public DBScriptExecutorException(String message) + { + super(message); + } } Modified: core/trunk/exo.core.component.database/src/test/java/org/exoplatform/services/database/TestDBScriptExecutor.java =================================================================== --- core/trunk/exo.core.component.database/src/test/java/org/exoplatform/services/database/TestDBScriptExecutor.java 2010-03-19 15:19:01 UTC (rev 2085) +++ core/trunk/exo.core.component.database/src/test/java/org/exoplatform/services/database/TestDBScriptExecutor.java 2010-03-19 15:20:47 UTC (rev 2086) @@ -19,9 +19,17 @@ package org.exoplatform.services.database; import org.exoplatform.container.PortalContainer; +import org.exoplatform.services.database.creator.DBConnectionInfo; import org.exoplatform.services.database.creator.DBScriptExecutor; -import org.exoplatform.services.database.creator.DBScriptExecutorException; +import org.exoplatform.services.naming.InitialContextBinder; +import org.exoplatform.services.naming.InitialContextInitializer; +import java.sql.Connection; +import java.util.HashMap; +import java.util.Map; + +import javax.sql.DataSource; + import junit.framework.TestCase; /** @@ -31,24 +39,40 @@ public class TestDBScriptExecutor extends TestCase { - protected DBScriptExecutor dbCreator; + protected DBScriptExecutor dbExecutor; + private InitialContextBinder initialContextBinder; + + private InitialContextInitializer initContext; + public void setUp() throws Exception { PortalContainer container = PortalContainer.getInstance(); - dbCreator = (DBScriptExecutor)container.getComponentInstanceOfType(DBScriptExecutor.class); + + dbExecutor = (DBScriptExecutor)container.getComponentInstanceOfType(DBScriptExecutor.class); + initContext = (InitialContextInitializer)container.getComponentInstanceOfType(InitialContextInitializer.class); + initialContextBinder = (InitialContextBinder)container.getComponentInstanceOfType(InitialContextBinder.class); } - public void testExecute() throws Exception + public void testDBCreate() throws Exception { - assertNotNull(dbCreator); - try - { - dbCreator.execute("testDB"); - } - catch (DBScriptExecutorException e) - { - fail("Exception should not be thrown."); - } + assertNotNull(dbExecutor); + + DBConnectionInfo dbInfo = dbExecutor.createDatabase("testdb"); + + Map<String, String> refAddr = new HashMap<String, String>(); + refAddr.put("driverClassName", dbInfo.getDriver()); + refAddr.put("url", dbInfo.getUrl()); + refAddr.put("username", dbInfo.getUsername()); + refAddr.put("password", dbInfo.getPassword()); + + initialContextBinder.bind("testjdbcjcr", "javax.sql.DataSource", + "org.apache.commons.dbcp.BasicDataSourceFactory", null, refAddr); + + DataSource ds = (DataSource)initContext.getInitialContext().lookup("testjdbcjcr"); + assertNotNull(ds); + + Connection conn = ds.getConnection(); + assertNotNull(conn); } } Modified: core/trunk/exo.core.component.database/src/test/resources/conf/standalone/test-configuration.xml =================================================================== --- core/trunk/exo.core.component.database/src/test/resources/conf/standalone/test-configuration.xml 2010-03-19 15:19:01 UTC (rev 2085) +++ core/trunk/exo.core.component.database/src/test/resources/conf/standalone/test-configuration.xml 2010-03-19 15:20:47 UTC (rev 2086) @@ -51,28 +51,33 @@ </component> <component> - <key>org.exoplatform.services.database.creator.DBScriptExecutor</key> - <type>org.exoplatform.services.database.creator.DBScriptExecutor</type> - <init-params> - <properties-param> - <name>db-connection</name> - <description>database connection properties</description> - <property name="driverClassName" value="com.mysql.jdbc.Driver" /> - <property name="url" value="jdbc:mysql://localhost/" /> - <property name="username" value="root" /> - <property name="password" value="admin" /> + <key>org.exoplatform.services.database.creator.DBScriptExecutor</key> + <type>org.exoplatform.services.database.creator.DBScriptExecutor</type> + <init-params> + <properties-param> + <name>db-connection</name> + <description>database connection properties</description> + <property name="driverClassName" value="com.mysql.jdbc.Driver" /> + <property name="url" value="jdbc:mysql://localhost/" /> + <property name="username" value="root" /> + <property name="password" value="admin" /> + </properties-param> + <properties-param> + <name>db-creation</name> + <description>database creation properties</description> + <property name="scriptPath" value="script.sql" /> + <property name="username" value="testuser" /> + <property name="password" value="testpwd" /> </properties-param> - <properties-param> - <name>script-execution</name> - <description>properites for new database creation</description> - <property name="scriptPath" value="test.sql" /> - <property name="username" value="'user1'@'localhost'" /> - <property name="password" value="'pass1'" /> - </properties-param> </init-params> </component> <component> + <key>org.exoplatform.services.naming.InitialContextBinder</key> + <type>org.exoplatform.services.naming.InitialContextBinder</type> + </component> + + <component> <key>org.exoplatform.services.naming.InitialContextInitializer</key> <type>org.exoplatform.services.naming.InitialContextInitializer</type> <component-plugins>