This cookbook answers typical questions arising in the context of the DBInit tool.
How to start
DBinit to initialize a fresh database schema without any old database objects?
DBinit with the default option
--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
cd IS_HOME/tool/misc ant dbschema-clean
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
How to debug the
DBMigrate preparer within Intershop Studio?
DBMigrate with the option
-debug=debug_port or edit dbinit.bat|sh enables the Java debugging and Intershop Studio can attach to debug the
SET DEBUG=-Xdebug -Xnoagent -Xrunjdwp:transport=dt_socket,server=y,address=6666,suspend=n
Pipeline debugging is not available within
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:
This recipe applies to DBInit and DBMigrate because there is a strong relationship between both of them when it comes to new tables, indexes and constraints as well as PL/SQL code. As a rule of thumb, indexes, constraints and named PL/SQL code should be recreated every time a DBMigrate is executed.
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?
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.
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.
A similar version independent migration.properties file should be created to recreate the table descriptions, indexes and constraints upon every DBMigrate.
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.
A similar version-independent migration.properties file should be created to recreate the PL/SQL code upon every DBMigrate.
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.
# # 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
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?
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
The batch or shell script contains the debug port, so it is easy to debug your failed implementation.
The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.