JBoss.orgCommunity Documentation

Chapter 61. Database Creator

61.1. About
61.2. API
61.3. A configuration examples
61.4. An examples of a DDL script

61.1. About

Database creator DBCreator is responsible for execution DDL script in runtime. A DDL script may contain templates for database name, user name and password which will be replaced by real values at execution time.

Three templates supported:

61.2. API

Service provide method for execute script for new database creation. Database name which are passed as parameter will be substituted in DDL script instead of ${database} template. Returns DBConnectionInfo object (with all neccesary information of new database's connection) or throws DBCreatorException exception if any errors occurs in other case.

public DBConnectionInfo createDatabase(String dbName) throws DBCreatorException;

For MSSQL and Sybase servers, use autocommit mode to set true for connection. It's due to after execution "create database" command newly created database not available for "use" command and therefore you can't create new user inside database per one script.

61.3. A configuration examples

Service's configuration.

   <component>
      <key>org.exoplatform.services.database.creator.DBCreator</key>
      <type>org.exoplatform.services.database.creator.DBCreator</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>
      </init-params>
   </component>

db-connection properties section contains parameters needed for connection to database server

db-creation properties section contains paramaters for database creation using DDL script:

Specific db-connection properties section for different databases.

MySQL:

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

PostgreSQL:

<property name="driverClassName" value="org.postgresql.Driver" />
<property name="url" value="jdbc:postgresql://localhost/" />
<property name="username" value="root" />
<property name="password" value="admin" />

MSSQL:

<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<property name="url" value="jdbc:sqlserver://localhost:1433;"/>
<property name="username" value="root"/>
<property name="password" value="admin"/>

Sybase:

<property name="driverClassName" value="com.sybase.jdbc3.jdbc.SybDriver" />
<property name="url" value="jdbc:sybase:Tds:localhost:5000/"/>
<property name="username" value="root"/>
<property name="password" value="admin"/>

Oracle:

<property name="driverClassName" value="oracle.jdbc.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@db2.exoua-int:1521:orclvm" />
<property name="username" value="root" />
<property name="password" value="admin" />

61.4. An examples of a DDL script

MySQL:

CREATE DATABASE ${database};
USE ${database};
CREATE USER '${username}' IDENTIFIED BY '${password}';
GRANT SELECT,INSERT,UPDATE,DELETE ON ${database}.* TO '${username}';

PostgreSQL:

CREATE USER ${username} WITH PASSWORD '${password}';
CREATE DATABASE ${database} WITH OWNER ${username};

MSSQL:

USE MASTER;
CREATE DATABASE ${database};
USE ${database};
CREATE LOGIN ${username} WITH PASSWORD = '${password}';
CREATE USER ${username} FOR LOGIN ${username};

Sybase:

sp_addlogin ${username}, ${password};
CREATE DATABASE ${database};
USE ${database};
sp_adduser ${username};

Oracle:

CREATE TABLESPACE "${database}" DATAFILE '/var/oracle_db/orclvm/${database}' SIZE 10M AUTOEXTEND ON NEXT 6M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TEMPORARY TABLESPACE "${database}.TEMP" TEMPFILE '/var/oracle_db/orclvm/${database}.temp' SIZE 5M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
CREATE USER "${username}" PROFILE "DEFAULT" IDENTIFIED BY "${password}" DEFAULT TABLESPACE "${database}" TEMPORARY TABLESPACE "${database}.TEMP" ACCOUNT UNLOCK;
GRANT CREATE SEQUENCE TO "${username}";
GRANT CREATE TABLE TO "${username}";
GRANT CREATE TRIGGER TO "${username}";
GRANT UNLIMITED TABLESPACE TO "${username}";
GRANT "CONNECT" TO "${username}";
GRANT "RESOURCE" TO "${username}";