1 Introduction

This cookbook answers typical questions arising in the context of the DBInit tool.

2 Recipe: Clean Database Account and DBInit

2.1 Problem

How to start DBinit to initialize a fresh database schema without any old database objects?

2.2 Solution

Starting DBinit with the default option -r=true or --recreate-tables=true will only drop the tables existing in the current deployed or development-configured version of all *.orm files.
Older tables or other database objects are not removed by default. To avoid conflicts regarding still existing old schema objects during DBInit call the ant task dbschema-clean beforehand.

cd IS_HOME/tool/misc
ant dbschema-clean

2.3 Discussion

ES1|...eserver/tools/misc> ant dbschema-clean
Buildfile: build.xml
build.environment:
dbschema-clean:
environment:
dump.clean:
     [echo] Clearing Database
     [echo]    dbtool.bat -p intershop.jdbc.password -is.share .../share .../bin/sqlplus -L _user_@ISSERVER.world @...eserver/tools/misc/droptables.sql
     [exec] SQL*Plus: Release ...
     [exec] Enter password:
     [exec] Connected to:
     [exec] Oracle Database ... Production ...
     [exec]
     [exec] [2011-11-07 15:34:09.753 +00:00] drop all objects of the db user.
     [exec] [2011-11-07 15:34:09.753 +00:00] user: _user_@jdbc:oracle:thin:@_host_:_port_:_sid_
     [exec] [2011-11-07 15:34:09.753 +00:00] please wait...
     [exec]
     [exec] [2011-11-07 15:34:39.883 +00:00] all objects of the db user dropped.
     [exec]
     [exec] Disconnected from Oracle Database ...
     [exec] ...
BUILD SUCCESSFUL
Total time: 31 seconds

3 Recipe: Java Debugging With DBInit/DBMigrate

3.1 Problem

How to debug the DBinit/ DBMigrate preparer within Intershop Studio?

3.2 Solution

Starting DBinit/ DBMigrate with the option -debug=debug_port or edit dbinit.bat|sh enables the Java debugging and Intershop Studio can attach to debug the DBinit/ DBMigrate process.

dbinit|dbmigrate -debug=debug_port

SET DEBUG=-Xdebug -Xnoagent -Xrunjdwp:transport=dt_socket,server=y,address=6666,suspend=n

3.3 Discussion

Example of debugging the DatabaseTablesPreparer(core) on port 6666 with Intershop version > 7.2.

ES1|.../eserver/bin> dbinit
Listening for transport dt_socket at address: 6666

Intershop Studio Configuration:

Intershop Studio Debug:

4 Recipe: DBInit/DBMigrate and New Database Tables, Indexes, Constraints and PL/SQL Code

4.1 Problem

How to install new database tables, indexes, constraints, named PL/SQL code with DBInit and DBMigrate?
How to execute PL/SQL scripts with DBInit and DBMigrate?

4.2 Solution

4.2.1 Tables, Constraints and Indexes

New cartridge-specific EDL files define tables, constraints and index structures in the database.

These structures are generated with the Intershop Studio into the *.orm, dbindex.ddl and dbconstraint.ddl files.

1. Start Intershop Studio.
2. Open the Package Explorer.
3. Expand the cartridge item.
4. Right-click the EDL folder.
   
5. From the context menu select Code Generator for Intershop.

• Generate Code for *.orm files ( DatabaseTablesPreparer)
• Generate Database Indexes for dbindex.ddl files ( DatabaseIndexesPreparer)
• Generate Database Constraints for dbconstraints.ddl files ( DatabaseConstraintsPreparer)

You should also create the enfinitytable.sql file which is used to describe the classification of a table.
The enfinitytable.sql, dbindex.ddl and dbconstraints.ddl file should be placed at the location <cartridge>/javasource/resources/<cartridge>/dbinit/scripts.
After that, the enfinitytable.sql, dbindex.ddl and dbconstraints.ddl files have to be used with the appropriate preparers in the dbinit.properties file, see below for an example.

4.2.2 Named PL/SQL Code

The named PL/SQL code is stored within individual *.sql files (or *.pks and *.pkb files for PL/SQL package definitions and bodies) and should be included into spmainfile.ddl. The *.sql files and the file spmainfile.ddl should be placed at location <cartridge>/javasource/resources/<cartridge>/dbinit/scripts. After that the spmainfile.ddl has to be used with the appropriate preparer in the dbinit.properties file, see below for an example.

4.2.3 PL/SQL Scripts

Single PL/SQL scripts should be placed at location <cartridge>/javasource/resources/<cartridge>/dbinit/scripts. After that, the PL/SQL script should be used with the appropiate preparer in the dbinit.properties file, see below for an example. For DBMigrate there are two scenarios:

1) If the script must be executed every time a DBMigrate is executed, one should call the script with the appropriate preparer in a version independent migration.properties file.

2) If the script must be executed only for a specific version, you should call the script with the appropiate preparer in a version dependent migration-to-<version>.properties file.

4.2.4 Examples

#
# Database Tables Preparer, implicit added by DBInit as pre.Class1
# pre.Class1 = com.intershop.beehive.core.dbinit.preparer.database.DatabaseTablesPreparer

#
# Indexes
Class1  = com.intershop.beehive.core.dbinit.preparer.database.DatabaseIndexesPreparer \
          resources/<cartridge>/dbinit/scripts/dbindex.ddl

#
# Constraints
Class2  = com.intershop.beehive.core.dbinit.preparer.database.DatabaseConstraintsPreparer \
          resources/<cartridge>/dbinit/scripts/dbconstraints.ddl

#
# Stored Prodecure/Function main file
Class3  = com.intershop.beehive.core.dbinit.preparer.database.SQLScriptPreparer \
          resources/<cartridge>/dbinit/scripts/spmainfile.ddl

#
# Single PL/SQL script
Class4  = com.intershop.beehive.core.dbinit.preparer.database.SQLScriptPreparer \
          resources/<cartridge>/dbinit/scripts/<sql_file>.sql

spool spmainfile.log

@sp_delete_X_ByDomain.sql
@sp_delete_Y_ByDomain.sql
...
@sf_foo_bar.sql

spool off
exit

#
# Database Tables Preparer
Class1  = com.intershop.beehive.core.dbmigrate.preparer.database.DatabaseTablesPreparer

#
# Indexes
Class2  = com.intershop.beehive.core.dbmigrate.preparer.database.DatabaseIndexesPreparer \
          resources/<cartridge>/dbinit/scripts/dbindex.ddl

#
# Constraints
Class3  = com.intershop.beehive.core.dbmigrate.preparer.database.DatabaseConstraintsPreparer \
          resources/<cartridge>/dbinit/scripts/dbconstraints.ddl

#
# Stored Prodecure/Function main file
Class4  = com.intershop.beehive.core.dbmigrate.preparer.database.ExecuteSQLScriptPreparer \
          resources/<cartridge>/dbinit/scripts/spmainfile.ddl

#
# Single PL/SQL script
Class5  = com.intershop.beehive.core.dbmigrate.preparer.database.ExecuteSQLScriptPreparer \
          resources/<cartridge>/dbinit/scripts/<sql_file>.sql

#
# Single PL/SQL script
Class1  = com.intershop.beehive.core.dbmigrate.preparer.database.ExecuteSQLScriptPreparer \
          resources/<cartridge>/dbmigrate/scripts/<version>/<sql_file>.sql

5 Recipe: Execute a Single Preparer

5.1 Problem

The developer creates new DBinit preparer or changes the data. Sometimes the first execution fails. The old UI-driven dbinit tools supported the execution of selected preparers. How can the developer do the same thing in the dbinit command line tool?

5.2 Solution

The dbinit command line tool provides two options to select preparers.

• A single preparer:

  dbinit.bat --exec-id=<cartridge>:<preparer>
  REM example
  dbinit.bat --exec-id=data_fs2:Class2
  REM Attention: You need to provide the name of the entry from the dbinit.properties (Class2) not the class name of the preparer (DatabaseConstraintsPreparer).

• All preparers of the cartridge can be executed:

  dbinit.bat -t=<cartridge>
  REM example
  dbinit.bat -t=data_fs2