
[[c-configuring-your-backend-database]]
= Configuring Your Backend Database

This appendix explains how to configure a backend database to use with a Jakarta Platform, Enterprise Edition server being tested against the Jakarta EE {tck_version} TCK.

The topics included in this appendix are as follows:

* <<c.1-overview>>
* <<c.2-the-init.database-ant-target>>
* <<c.3-database-properties-in-ts.jte>>
* <<c.4-database-ddl-and-dml-files>>

[[c.1-overview]]
== Overview

All Jakarta Platform, Enterprise Edition servers tested against the Jakarta EE {tck_version} TCK must be configured with a database and JDBC 4.1-compliant drivers.
Note that the Jakarta Platform, Enterprise Edition CI, Eclipse {glassfish_version} includes the Apache Derby database.

To perform interoperability testing, you need to configure two Jakarta Platform, Enterprise Edition servers and two databases, one of which must be the Jakarta Platform, Enterprise Edition RI with the bundled Apache Derby database. See <<java-platform-enterprise-edition-server-configuration-scenarios>> for more information.

For the purposes of Jakarta EE {tck_version} Platform TCK testing, all database configuration properties required by the TCK are made in the `<TS_HOME>/bin/ts.jte` file. The TCK `init.<`database> Ant target of the `<TS_HOME>/bin/xml/initdb.xml` file uses the properties you set in `ts.jte` to generate one or more SQL statement files that are in turn used create and populate database tables and configure procedures required by the TCK. You don't need to explicitly use this script, however you will have to achieve the same effect via other means if you do not use the script.

The database configuration process comprises four general steps:

1.  Set database-related properties in the `<TS_HOME>/bin/ts.jte` file.
2.  Configure your Jakarta Platform, Enterprise Edition server
implementation for your database and for TCK.
3.  Start your database.
4.  Run the `init.<database>` Ant target to initialize your database for TCK where `<database>` is the name of your database, e.g. `init.derby`, `init.javadb`, `init.postgresql` etc.

The procedure for configuring your Jakarta Platform, Enterprise Edition server for your database is described in <<configuring-a-jakarta-ee-11-server>>. The final step,
initializing your database for TCK by running `init.<`database> target, is explained more in the next section.

[[c.2-the-init.database-ant-target]]
== The init.<database> Ant Target

Before your Jakarta Platform, Enterprise Edition server database can be tested against the Jakarta EE {tck_version} Platform TCK, the database must be initialized for TCK by means of the Ant `init.<database>` target. For example, the `init.javadb` Ant task is used to initialize the Apache Derby database for TCK.

This Ant target references database properties in `ts.jte` file and database-specific DDL and DML files located under `<TS_HOME>/bin/sql` to generate SQL statement files that are read by the Jakarta EE {tck_version} Platform TCK when you start the test suite. The DDL and DML files are described later in this appendix, in <<c.4-database-ddl-and-dml-files>>.

The Jakarta EE {tck_version} Platform TCK includes the following database-specific Ant targets (possibly more):

* `init.cloudscape`
* `init.db2`
* `init.oracle`
* `init.oracleDD`
* `init.oracleInet`
* `init.derby`
* `init.javadb`
* `init.sybase`
* `init.sybaseInet`
* `init.mssqlserver`
* `init.mssqlserverInet`
* `init.mssqlserverDD`
* `init.postgresql`


Each Ant target uses a database-specific JDBC driver to configure a backend for a specific database; for example, OracleInet/Oracle Inet driver; OracleDD/Oracle DataDirect driver. These targets are configured in the `<TS_HOME>/xml/initdb.xml` file.

[[c.3-database-properties-in-ts.jte]]
== Database Properties in ts.jte

Listed below are the names and descriptions for the database properties you need to set for TCK testing.

Note that some properties take the form property`.ri`. In all cases, properties with an `.ri` suffix are used for interoperability testing only. In such cases, the property value applies to the Jakarta Platform, Enterprise Edition VI server (the server you want to test) and the property`.ri` value applies to the Jakarta Platform, Enterprise Edition CI, Eclipse {glassfish_version} server. For example:

[source,properties]
----
db.dml.file=VI_DML_filename
db.dml.file.ri=RI_DML_filename
----

The property`.ri` properties are only used in two-server configurations; that is, when you are performing interoperability tests.

=== Table D-1 ts.jte Database Properties

[width="100%",cols="35%,65%",options="header",]
|=======================================================================
|Property |Description
|database`.classes` |`CLASSPATH` to JDBC driver classes.

|database`.dataSource` |DataSource driver.

|database`.dbName` |Database Name.

|database`.driver` |DriverManager driver.

|database`.password` |User password configured.

|database`.poolName` |Name of pool configured in the RI (do not
change!).

|database`.port` |Database Server port.

|database`.properties` |Additional properties required by the defined data source for each driver configuration in `ts.jte`. You should not need to modify this property.

|database`.server` |Database Server.

|database`.url` |URL for the TCK database; the `dbName`, `server`, and `port` properties are automatically substituted in to build the correct URL. You should never need to modify this property.

|database`.user` |User ID configured.

|`db.dml.file` |Tells `init.`database which DML file to use for the VI database; for example, `db.dml.file=${javadb.dml.file}`.

|`db.dml.file.ri` |Tells `init.`database which DML file to use for the RI database; for example, `db.dml.file=${javadb.dml.file}`.

|`jdbc.lib.class.path` |Used by the database`.classes` properties to point to the location of the JDBC drivers.

|`jdbc.poolName` |Configures the connection pool that will be used in
the TCK test run; for example, `jdbc.poolName=${javadb.poolName}`. Set this property when running against the RI if using a database other than Apache Derby.

|`password1` |Password for the JDBC/DB1 resource; for example,
`password1=${javadb.passwd}`.

|`password2` |Password for the JDBC/DB2 resource; for example,
`password2=${javadb.passwd}`.

|`password3` |Password for the JDBC/DBTimer resource; for example,
`password3=${javadb.passwd}`.

|`user1` |User name for the JDBC/DB1 resource; for example,
`user1=${javadb.user}`.

|`user2` |User name for the JDBC/DB2 resource; for example,
`user2=${javadb.user}`.

|`user3` |User name for the JDBC/DBTimer resource; for example,
`user3=${javadb.user}`.
|=======================================================================


[[c.4-database-ddl-and-dml-files]]
== Database DDL and DML Files

For each supported database type, the Jakarta EE {tck_version} Platform TCK includes a set of DDL and DML files in subdirectories off the `<TS_HOME>/bin/sql` directory.
The `config.vi` and `config.ri` targets use two `ts.jte` properties, `db.dml.file` and `db.dml.file.ri` (interop only), to determine the database type, and hence which database-specific DML files to copy as `<TS_HOME>/bin/tssql.stmt` and `tssql.stmt.ri` (for interop) files.

The `tssql.stmt` and `tssql.stmt.ri` files contain directives for
configuring and populating database tables as required by the TCK tests, and for defining any required primary or foreign key constraints and database-specific command line terminators.

In addition to the database-specific DML files, the Jakarta EE {tck_version} Platform TCK includes database-specific DDL files, also in subdirectories off `<TS_HOME>/bin/sql`. These DDL files are used by the `init.`database target to create and drop database tables and procedures required by the TCK.

The SQL statements in the `tssql.stmt` and `tssql.stmt.ri` files are read as requested by individual TCK tests, which use the statements to locate required DML files.

The DDL and DML files are as follows:

* database`.ddl.sql`: DDL for BMP, Session Beans
* database`.ddl.sprocs.sql`: DDL for creating stored procedures
* database`.ddl.interop.sql`: DDL for interop tests
* database`.dml.sql`: DML used during test runs

Each DDL command in each `<TS_HOME>/sql/`database is terminated with an ending delimiter. The delimiter for each database is defined in the `<TS_HOME>/bin/xml/initdb.xml` file. If your configuration requires the use of a database other than the databases that `initdb.xml` currently supports, you may modify `initdb.xml` to include a target to configure the database that you are using.

An example of the syntax for a database target in `initdb.xml` is shown below:

[source,xml]
----
<target name="init.sybase">
  <antcall target="configure.backend">
      <param name="db.driver" value="${sybase.driver}"/>
      <param name="db.url" value="${sybase.url}"/>
      <param name="db.user" value="${sybase.user}"/>
      <param name="db.password" value="${sybase.passwd}"/>
      <param name="db.classpath" value="${sybase.classes}"/>
      <param name="db.delimiter" value="!"/>
      <param name="db.name" value="sybase" />
  </antcall>
</target>
----

The database`.name` property should be added to your `ts.jte` file. The `db.name` property is the name of a subdirectory in `<TS_HOME>/sql`. After updating `initdb.xml`, you invoke the new target with:

[source,bash]
----
ant -f <TS_HOME>/bin/xml/initdb.xml init.databasename
----
