Introduction
Intershop Commerce Management 11 supports installations with large amounts of data, including products and users. Additionally, it allows for the creation and deletion of web shops at runtime. The document explains how the mass data of these web shops is removed from the database.
Asynchronous Cleanup: To avoid browser timeouts and error pages, mass data cannot be cleaned up during web request execution. Instead, jobs are used to asynchronously remove specific mass data objects. Asynchronous cleanup also allows for scheduling the cleanup job during low web traffic periods, reducing the load on the database.
Transaction Handling and Stored Procedures: Using the persistent API (ORM objects) to cleanup the data leads to a large amount of SQL statements resulting in a high load on the database server and poor runtime performance. Additionally, it is important to consider transaction handling and avoid large transactions in OLTP applications like web shops. Therefore, the cleanup jobs use PL/SQL stored procedures with accordingly optimized transaction handling.
Extensibility: The business objects are defined in one or more cartridges. Customers can also define or extend the business objects, which reference domains, products, and users in their own cartridges. Each cartridge is responsible for properly removing referencing objects.
Glossary
This glossary describes the terms in this document:
Term | Definition |
|---|---|
PL/SQL | PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension language for SQL and the Oracle relational database. PL/SQL's general syntax resembles that of Ada or Pascal. PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java. |
Oracle PL/SQL program unit - Stored Procedure | Procedures are similar to Functions, in that they can be executed to perform work. The primary difference is that procedures cannot be used in an SQL statement. Another difference is that it can return multiple values. Procedures are traditionally the workhorse of the coding world and functions are traditionally the smaller, more specific pieces of code. PL/SQL maintains many of the distinctions between functions and procedures found in many general-purpose programming languages, but in addition, functions can be called from SQL, while procedures cannot. |
Implementation
Jobs
Each cartridge responsible for removing cross-cartridge business objects has a pipeline for triggering the removal process. The pipeline is triggered periodically by a job, also prepared by the cartridge.
Currently the following jobs for the root site are available:
DeleteDomainReferencesDeleteUserReferencesDeleteProductReferences
The trigger pipeline executes a deletion pipeline (definition is optional) in each started cartridge. The cartridges are processed in reverse order, following last created, first removed principle.
Mass Data Deletion Jobs
The available jobs for mass data deletion with detailed job parameters are:
Type | Cartridge | Job Name | Job Interval | Job Enabled | Attributes | Pipeline | Description |
|---|---|---|---|---|---|---|---|
Domain | core | 60 | true | TriggerDeleteDomainReferences | Triggers deleted domain cleanup. | ||
User | core | 60 | true | BlockSize = 200 | TriggerDeleteUserReferences | Triggers deleted user cleanup. | |
Product | xcs | 60 | true | BlockSize = 200 | TriggerDeleteProductReferences | Triggers deleted product cleanup. The maximum number of products which the |
The System Management Console (SMC) displays the configured deletion jobs, navigate to Schedules | Scheduling within the root domain.
Cartridge Properties
Each mass data cleanup job calls all pipelines in cartridges defining corresponding property with the pipeline and start node name:
DeleteDomainReferences:
intershop.cartridges.<cartridge>.DomainDeletionPipeline=<pipeline>-<start node>DeleteUserReferences:
intershop.cartridges.<cartridge>.UserDeletionPipeline=<pipeline>-<start node>DeleteProductReferences:
intershop.cartridges.<cartridge>.ProductDeletionPipeline=<pipeline>-<start node>
Example for bc_organization:
bc_organization.properties
intershop.cartridges.bc_organization.DomainDeletionPipeline = ORG_RemoveDomainReferences-Start
Stored Procedures
All stored procedures that remove domains, users, and products referencing objects are named
Type | ProcedureName |
|---|---|
Domain |
|
User |
|
Product |
|
whereby * gives a hint about the objects, the stored procedure removes.
Replication Environments
Intershop Commerce Management 11 alters the table structure in replication environments. It means that each stored procedure must function in both replication and non-replication environments. According utility PL/SQL packages are available.
Deployment
The default location for stored procedures is <cartridge>\javasource\resources\<cartridge>\dbinit\scripts.
All stored procedures removing domains are created and replaced within the database by DBInit and DBMigrate.
The usage of preparers are:
Preparer (DBInit)
com.intershop.beehive.core.dbinit.preparer.database.SQLScriptPreparer resources/<cartridge>/dbinit/scripts/<spSingleFile>orspmainfile.ddl
MigrationPreparer (DBMigrate)
com.intershop.beehive.core.dbmigrate.preparer.database.ExecuteSQLScriptPreparer resources/<cartridge>/dbinit/scripts/<spSingleFile>orspmainfile.ddl
Usage
All stored procedures removing domains are triggered by several executions of the pipelet ExecuteStoredProcedure.
Example
For example of sp_deleteFooBarsByDomain, see Sample Domain Deletion Stored Procedure.