eXo Platform 3.5

## Chapter 71. Database Creator

## 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:

- `${database}` for database name;

- `${username}` for user name;

- `${password}` for user's password;

## 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.

    public DBConnectionInfo getDBConnectionInfo(String dbName) throws DBCreatorException;

Return database connection information without database creation.

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

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

## 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}";