24.2 Database Descriptors

• Create a database/schema
• Create a user
• grant privileges
• Delete a database/schema

The built-in db descriptors include those for Mysql and Oracle databases. To support a new database, its XML descriptor must be provided. Its file name is [dbmsType]-descriptor.xml where dbmsType is the type of database, case-sensitive. For example, mysql-descriptor.xml, oracle-descriptor.xml, etc.

Database XML descriptors need to be put under conf/db before running Cmobilecom Installer. They will be available as choices for the target database, and will be copied to conf/db under installation directory by the installer.

<db-descriptor dbmsType="mysql">
	
	<param name="databaseSameAsSchema" value="true"/>
	<param name="userSameAsSchema" value="false"/>
	
	<param name="jdbc.driver" value="com.mysql.jdbc.Driver" />	
	<param name="jdbc.url" 
		value="jdbc:mysql://#{host}:#{port}/#{database}?useUnicode=true&characterEncoding=UTF-8"/>
		
	<dialect name="setCurrentSchema">
		<sql>USE #{schema}</sql>
	</dialect>
	
	<dialect name="createSchema">
		<sql>
		CREATE DATABASE #{schema} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
		GRANT ALL PRIVILEGES ON #{schema}.* TO '#{username}'@'#{userHost}' IDENTIFIED BY '#{password}' WITH GRANT OPTION;
		</sql>
	</dialect>
	
	<dialect name="createUser">
		<sql>CREATE USER IF NOT EXISTS '#{username}'@'#{userHost}' IDENTIFIED BY '#{password}'</sql>
	</dialect>

	<dialect name="grantPrivileges">
		<sql>GRANT ALL PRIVILEGES ON #{schema}.* TO '#{username}'@'#{userHost}' WITH GRANT OPTION</sql>
	</dialect>

	<dialect name="grantTablePrivileges">
		<!-- the same user already has all privileges on tables of all schema -->
	</dialect>
	
	<dialect name="deleteSchema">
		<sql>
		DROP DATABASE #{schema};
		revoke all privileges on #{schema}.* from '#{username}'@'#{userHost}';
		</sql>
	</dialect>
</db-descriptor>

<db-descriptor dbmsType="oracle">
	<param name="databaseSameAsSchema" value="false"/>
	<param name="userSameAsSchema" value="true"/>
	
	<param name="jdbc.driver" value="oracle.jdbc.driver.OracleDriver" /> 		
	<param name="jdbc.url" 
		value="jdbc:oracle:thin:@//#{host}:#{port}/#{database}"/>

	<dialect name="setCurrentSchema">
		<sql>ALTER SESSION SET CURRENT_SCHEMA = #{schema}</sql>
	</dialect>

	<dialect name="createSchema">
		<sql>GRANT CONNECT, RESOURCE TO #{schema} IDENTIFIED BY #{password}</sql>
	</dialect>
	
	<dialect name="createUser">
		<!-- user is the same as schema -->
	</dialect>

	<dialect name="grantPrivileges">
		<!-- privileges have been granted to the user(schema) -->
	</dialect>

	<dialect name="grantTablePrivileges">
		<sql>GRANT ALL PRIVILEGES ON #{srcSchema}.#{table} TO #{targetSchema}</sql>
	</dialect>
	
	<dialect name="deleteSchema">
		<sql>DROP USER #{schema} CASCADE</sql>
	</dialect>
</db-descriptor>

A param element defines a parameter value or some characteristics of a database. For example, driver class, connection url, etc.

A dialect is defined as a sequence of sql(s) and commands. A sql element defines a number of sql statements that will be sent to database server for execution. However, a command defines a program with its arguments and will be executed on server operating system where JVM is running.

<dialect name="dialect_name">
	<sql>sql statements</sql>
	<command>
		<program>command_path_on_server</program>
		<argument>command_argument1</argument>
		<argument>command_argument2</argument>
		<stdoutCharset>charset</stdoutCharset>
	</command>
</dialect>