Useful Groovy Scripts. Part 4. Finding all external references to an object

Finding all external references to an object (EARLY BETA)

The script I present today is designed for finding the references for any particular item in hybris. It can be useful if you want to figure out if the product or image is used somewhere in the system or it can be safely removed. It is a beta version, and it doesn’t cover everything, so use it at your own risk 🙂

For demonstrating purposes, let’s take the image which is definitely linked to the product.

Let’s start with the simplest case,

product-media

The script shows all objects (here products) having the particular object (here Media) set as a value of one of the attributes with “Media” type. For 8797134192670 the script shows:

2017-12-16_21h31_55

The first item in the list is a product with the PK 8796373942273. All other items are product variants which have the image assigned as well.

There is a special case with the collection of objects. The collection is stored as a comma-separated PKs. In SAP hybris accelerators, the collections are used quite intensively, despite the fact SAP don’t recommend using them in the projects. Let’s take the following example:

product-mediacontainer-media
2017-12-16_19h45_52.png

We expect that the system will determine the objects this image is used with and one of these is this product.

String pkObj = "8797135929374";

The script shows all objects having the references to the object with this PK:

2017-12-16_19h59_54

We can see that our expected product is a first item in the list. All other products are different versions of the product.

This media is found in the collection attribute, “thumbnail”. Internally, the collections are stored as comma-separated values, so for our product the value of the attribute looks like this:

,#1,8797135896606,8797135962142,8797135994910, 8797135929374,8797136027678,
8797136060446, 8797136093214,

Let’s take another example:

pos-mediacontainer.png

For the particular media container (8796244312114) the system shows the list of objects which use the media container.

2017-12-17_23h21_16

You can see both Media objects on the list because both use the same media container.

Categories

Let’s take the following example:

extref-blog-1.png

For PK=8796146172046 (Digital Compacts, the Category object), the script shows the following results:

2017-12-17_23h48_15.png

That means that the category is used in the following external objects:

  • Category/Principal relation,
  • Category/Product relation,
  • Category/Category relation.

The PKs are specified are PKs of these relations (for example, CategoryProductRelation), not the related objects (Product).

For the Brands category (8796147253390),  you will get the report like the following:

2017-12-17_23h53_31

There are no products because the Brand category is not linked to products directly.

How it works

The system determines a type of the specified PK. For this type, the system extracts the type hierarchy. For each type in the hierarchy, the system finds the types with the attributes of the particular types or involved in hybris relations. For collections and relations, there is special handling. Finally, the system creates an SQL query and executes it. For example, for “Category” object the SQL will look like:

SELECT 'ClassAttributeAssignment','cat2attrrel',"(cat2attrrel.p_classificationclass = '8796146172046' AND  TypePkString = 8796110258258)", cat2attrrel.pk
    FROM cat2attrrel
    WHERE
       (cat2attrrel.p_classificationclass = '8796146172046' AND  TypePkString = 8796110258258)
 UNION
SELECT 'CockpitItemTemplate','cockpittemplclassifrels',"(cockpittemplclassifrels.TargetPK = '8796146172046')", cockpittemplclassifrels.pk
    FROM cockpittemplclassifrels
    WHERE
       (cockpittemplclassifrels.TargetPK = '8796146172046')
 UNION
SELECT 'TextFieldConfiguratorSetting','configuratorsettings',"(configuratorsettings.p_configurationcategory = '8796146172046' AND  TypePkString = 8796109111378)", configuratorsettings.pk
    FROM configuratorsettings
    WHERE
       (configuratorsettings.p_configurationcategory = '8796146172046' AND  TypePkString = 8796109111378)
 UNION
SELECT 'CategoryCatalogVersionDifference','catverdiffs',"(catverdiffs.p_sourcecategory = '8796146172046' AND  TypePkString = 8796110061650) OR (catverdiffs.p_targetcategory = '8796146172046' AND  TypePkString = 8796110061650)", catverdiffs.pk
    FROM catverdiffs
    WHERE
       (catverdiffs.p_sourcecategory = '8796146172046' AND  TypePkString = 8796110061650) OR
       (catverdiffs.p_targetcategory = '8796146172046' AND  TypePkString = 8796110061650)
 UNION
SELECT 'CMSSite','cmssite',"(cmssite.p_defaultpreviewcategory = '8796146172046' AND  TypePkString = 8796105179218)", cmssite.pk
    FROM cmssite
    WHERE
       (cmssite.p_defaultpreviewcategory = '8796146172046' AND  TypePkString = 8796105179218)
 UNION
SELECT 'SolrCategoryRedirect','solrabskwrdredir',"(solrabskwrdredir.p_redirectitem = '8796146172046' AND  TypePkString = 8796119203922)", solrabskwrdredir.pk
    FROM solrabskwrdredir
    WHERE
       (solrabskwrdredir.p_redirectitem = '8796146172046' AND  TypePkString = 8796119203922)
 UNION
SELECT 'PreviewData','previewdata',"(previewdata.p_previewcategory = '8796146172046' AND  TypePkString = 8796126675026)", previewdata.pk
    FROM previewdata
    WHERE
       (previewdata.p_previewcategory = '8796146172046' AND  TypePkString = 8796126675026)
 UNION
SELECT 'AsCategoryAwareSearchConfiguration','assearchconf',"(assearchconf.p_category = '8796146172046' AND  TypePkString = 8796128575570)", assearchconf.pk
    FROM assearchconf
    WHERE
       (assearchconf.p_category = '8796146172046' AND  TypePkString = 8796128575570)
 UNION
SELECT 'ProductCategoryRestriction','categories',"(restrictions.p_categories LIKE '%8796146172046%')", restrictions.pk
    FROM restrictions
    WHERE
       (restrictions.p_categories LIKE '%8796146172046%')
 UNION
SELECT 'Principal','cat2princrel',"(cat2princrel.SourcePK = '8796146172046')", cat2princrel.pk
    FROM cat2princrel
    WHERE
       (cat2princrel.SourcePK = '8796146172046')
 UNION
SELECT 'Media','cat2medrel',"(cat2medrel.SourcePK = '8796146172046')", cat2medrel.pk
    FROM cat2medrel
    WHERE
       (cat2medrel.SourcePK = '8796146172046')
 UNION
SELECT 'Product','cat2prodrel',"(cat2prodrel.SourcePK = '8796146172046')", cat2prodrel.pk
    FROM cat2prodrel
    WHERE
       (cat2prodrel.SourcePK = '8796146172046')
 UNION
SELECT 'Keyword','cat2keywordrel',"(cat2keywordrel.SourcePK = '8796146172046')", cat2keywordrel.pk
    FROM cat2keywordrel
    WHERE
       (cat2keywordrel.SourcePK = '8796146172046')
 UNION
SELECT 'Category','cat2catrel',"(cat2catrel.SourcePK = '8796146172046') OR (cat2catrel.TargetPK = '8796146172046')", cat2catrel.pk
    FROM cat2catrel
    WHERE
       (cat2catrel.SourcePK = '8796146172046') OR
       (cat2catrel.TargetPK = '8796146172046')
 UNION
SELECT 'ProductPromotion','catpromrelations',"(catpromrelations.SourcePK = '8796146172046')", catpromrelations.pk
    FROM catpromrelations
    WHERE
       (catpromrelations.SourcePK = '8796146172046')
 UNION
SELECT 'CMSCategoryRestriction','categories4restriction',"(categories4restriction.TargetPK = '8796146172046')", categories4restriction.pk
    FROM categories4restriction
    WHERE
       (categories4restriction.TargetPK = '8796146172046')
 UNION
SELECT 'ProductCarouselComponent','catsforprodcarcomprels',"(catsforprodcarcomprels.TargetPK = '8796146172046')", catsforprodcarcomprels.pk
    FROM catsforprodcarcomprels
    WHERE
       (catsforprodcarcomprels.TargetPK = '8796146172046')

The script is in BETA.
Some cases are not covered (such as localized composed type attributes).
Please use it at your own risk: the code of the script is quick and dirty, it was developed mainly for experimenting.

THE SCRIPT

(github)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: