The database creator named DBCreator is responsible for
executing the DDL script at runtime. A DDL script may contain templates for
database name, username and password which will be replaced by real
values at the execution time.
Three templates are supported:
${database} for database name;
${username} for username;
${password} for user's password;
The service provides method for executing script for new database creation.
The database name which is passed as parameter will be substituted in the DDL script instead of the ${database} template. The
DBConnectionInfo object is returned (with all necessary information of new database's connection) or DBCreatorException
exception will be thrown if any error occurs in the other case.
public DBConnectionInfo createDatabase(String dbName) throws DBCreatorException;
For MSSQL and Sybase servers, use the autocommit mode to set "true" for connection. It is due to that after the "create database" command is executed, the newly created database is not available for the "use" command. Therefore, you cannot create a new user inside database per one script.
public DBConnectionInfo getDBConnectionInfo(String dbName) throws DBCreatorException;
Information of the database connection is returned 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>
The properties section of db-connection contains parameters needed for connection to the database server.
There are four reserved and mandatory properties, including: driverClassName, url, username and password.
However, db-connection may contain additional properties.
For example, the following additional properties allow reconnecting to the 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>
The properties section of db-creation contains parameters for database creation using the DDL script:
scriptPath: The absolute path to the DDL script file;
username: The username for substitution ${username} template in the DDL script;
password: The user's password for substitution ${password} template in the DDL script;
The specific properties section of db-connection 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" />
PostgrePlus:
<property name="driverClassName" value="com.edb.Driver" />
<property name="url" value="jdbc:edb://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};PostgrePlus:
<property name="driverClassName" value="com.edb.Driver" />
+<property name="url" value="jdbc:edb://localhost/" />
+<property name="username" value="root" />
+<property name="password" value="admin" />
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}";