Intershop 7 supports installations with huge amount of data like products and users. Further, it supports the creation and deletion of web shops at runtime. The document describes how the mass data of these web shops is removed from the database.
Asynchronous Cleanup: The cleanup of mass data can not happen during execution of a web request, because the browser would run into a timeout resulting in an error page. That's why, mass data is asynchronously removed using the jobs for particular mass data objects. Another advantage of asynchronous cleanup is, that the cleanup job can be scheduled to run in the night to reduce the load on database during high web traffic periods.
Transaction Handling and Stored Procedures: Using the persistent API (ORM objects) to cleanup the data would lead to a huge amount of SQL statements resulting in a high load at database server as well as poor runtime performance. Furthermore, the transaction handling needs to be considered. Huge transactions should be avoided in OLTP applications like web shops. That's why, the cleanup jobs use PL/SQL stored procedures with accordingly optimized transaction handling.
Extensibility: The business objects are defined in one or more cartridges, additionally the customers can define or extend the business objects (which references domains, products and users) in their own cartridges, a cartridge must take care itself of properly removing referencing objects.
Each cartridge which has to take care of removing cross-cartridge business objects provides a pipeline to trigger its removal process. This pipeline is usually triggered periodically by a job (also prepared by the cartridge).
Currently the following jobs for the root site are prepared:
DeleteDomainReferences
DeleteUserReferences
DeleteProductReferences
Such a trigger pipeline tries to executes a deletion pipeline (definition is optional) in each started cartridge (processing the list of started cartridges in reverse order -> last created, first removed principle).
The known jobs for mass data deletion with detailed job parameters are:
Type | Cartridge | Job | Job Interval | Job | 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) can display the configured deletion jobs, see "Schedules > Scheduling" within the root domain.
Each mass data cleanup job calls all pipelines in cartridges defining according property with pipeline and start node name:
intershop.cartridges.<cartridge>.DomainDeletionPipeline=<pipeline>-<start node>
intershop.cartridges.<cartridge>.UserDeletionPipeline=<pipeline>-<start node>
intershop.cartridges.<cartridge>.ProductDeletionPipeline=<pipeline>-<start node>
Example for bc_organization
:
intershop.cartridges.bc_organization.DomainDeletionPipeline = ORG_RemoveDomainReferences-Start
All stored procedures removing 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 7 changes the table structure in replication environments. Means, each stored procedure need to work within replication and non-replication environments. According utility PL/SQL packages are available.
The default location for stored procedures are <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 prepares are:
com.intershop.beehive.core.dbinit.preparer.database.SQLScriptPreparer resources/<cartridge>/dbinit/scripts/
<spSingleFile>
orspmainfile.ddl
com.intershop.beehive.core.dbmigrate.preparer.database.ExecuteSQLScriptPreparer resources/<cartridge>/dbinit/scripts/
<spSingleFile>
orspmainfile.ddl
All stored procedures removing domains are triggered by several executions of the pipelet ExecuteStoredProcedure
.
For example of sp_deleteFooBarsByDomain
, see Sample Domain Deletion Stored Procedure
This glossary describes the terms in this document:
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 a 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. |
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.