JBoss.orgCommunity Documentation
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:
${database} for database name;
${username} for user name;
${password} for user's password;
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.
public DBConnectionInfo getDBConnectionInfo(String dbName) throws DBCreatorException;
Return database connection information without database creation.
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" />
<property name="additional_property" value="value">
...
<property name="additional_property_n" value="value">
</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
There is four reserved and mandatory properties driverClassName, url, username and password. But db-connection may contain additonal properties.
For example, next additional proprites allows reconnect to MySQL database when connection was refused:
<properties-param>
<name>db-connection</name>
...
<property name="validationQuery" value="select 1"/>
<property name="testOnReturn" value="true"/>
...
</properties-param>db-creation properties section contains paramaters
for database creation using DDL script:
scriptPath: absolute path to DDL script
file;
username: user name for substitution
${username} template in DDL script;
password: user's password for substitution
${password} template in 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" />
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}";