Hybrismart
SOLR Search Promo engine Tools Backoffice Platform Other
. . .
SOLR Search Promo engine Tools Backoffice Platform Other

Useful Groovy Scripts. Part 5.

Category: Other Author: 17 January 2018 1 comment
All these scripts are used as templates for something specific to your particular project. They just demonstrate that something like restarting the server is possible to do with Hybris via Groovy and show the approach. The first script is used to convert FlexibleSearch queries into raw SQL. Hybris HAC can convert it, but the result is not 100% ready: it contains question marks for the internal params which HAC doesn’t show to the administrator. The second script shows all hybris types that use the particular type. The third one will help you with executing DDL queries on the database server remotely (such as ALTER TABLE or CREATE INDEX, for example). I also provide the script for printing the database table structure and the indexes defined for it.

PART 5

Restart hybris server from HAC

de.hybris.platform.jmx.JmxClient.restartWrapper(new File('../../../../data/hybristomcat.java.pid').text as Integer);

Generating SQL for Flexible Search

query="SELECT count(a.pk) AS itemcount, a.code AS itemtype FROM "+
"({{SELECT {i:pk} AS pk, {c:code} AS code FROM {Item AS i},"+
"{ComposedType AS c} WHERE {i:itemtype}={c:pk}}}) a GROUP BY a.code ORDER BY code";

import de.hybris.platform.servicelayer.search.FlexibleSearchQuery;
import de.hybris.platform.persistence.property.JDBCValueMappings;
import de.hybris.platform.core.Registry;
import com.mysql.jdbc.JDBC4PreparedStatement;
import de.hybris.platform.jdbcwrapper.PreparedStatementImpl;

flexibleSearchService = spring.getBean("flexibleSearchService");
flexibleSearchQuery = new FlexibleSearchQuery(query);
params = flexibleSearchService.translate(flexibleSearchQuery).getSQLQueryParameters();
sql = flexibleSearchService.translate(flexibleSearchQuery).getSQLQuery();
con = Registry.getMasterTenant().getDataSource().getConnection();
preparedStatement = con.prepareStatement(sql);
JDBCValueMappings.getInstance().fillStatement(preparedStatement, params);
stringRepresentationOfPreparedStatement=preparedStatement.getPrepStmtPassthruString();
sql = (stringRepresentationOfPreparedStatement =~ /^(.*?)\: (.*?)$/)[0][2]

All hybris types that use the particular type

import de.hybris.platform.hac.data.dto.SqlSearchResultData;
import de.hybris.platform.hac.facade.impl.DefaultFlexibleSearchFacade;
import de.hybris.platform.core.PK;
TYPE="Media"
typeService = spring.getBean("typeService");
typePK = typeService.getTypeForCode(TYPE).getPk();

SqlSearchResultData searchResult;
query =
"select EnclosingTypePK, QualifierInternal from attributedescriptors where AttributeTypePK = '"+typePK + "'";
;
flexibleSearchFacade = new DefaultFlexibleSearchFacade();
result = flexibleSearchFacade.executeRawSql(query, 2000000, false);

for (item in result.getResultList()) {
enclosingTypePk = item[0];
enclosingType = modelService.get(new PK(enclosingTypePk as Long));
println (enclosingType.getCode() + "." + item[1]);
}

Executing SQL

import de.hybris.platform.core.Registry;
conn = Registry.getCurrentTenant().getDataSource().getConnection();
stmt = conn.createStatement();
alterTableQuery = "alter table...";
abc = stmt.executeUpdate(alterTableQuery)

Print mysql table structure from hybris HAC

import de.hybris.platform.core.Registry;
conn = Registry.getCurrentTenant().getDataSource().getConnection();
println conn
stmt = conn.createStatement();
query = "desc genericitems";
rs = stmt.executeQuery(query);
while (rs.next()) {
String field = rs.getString("Field");
String type = rs.getString("type");
String nullinfo = rs.getString("null");
String key = rs.getString("key");
String extra = rs.getString("extra");
println field+"\t"+type+"\t"+nullinfo+"\t"+key+"\t"+extra;
}

Print all indexes

import de.hybris.platform.core.Registry;
conn = Registry.getCurrentTenant().getDataSource().getConnection();
println conn
stmt = conn.createStatement();
stmt2 = conn.createStatement();
query = "show tables";
rs = stmt.executeQuery(query);
while (rs.next()) {
table = rs.getString(1);
getIndexesQuery = "SHOW INDEX FROM "+table;
println getIndexesQuery
indexesResult = stmt2.executeQuery(getIndexesQuery);
while (indexesResult.next()) {
for (i=1; i<=indexesResult.getMetaData().getColumnCount();i++) {
print "\t"+indexesResult.getString(i);
}
println "";
}
}
Tags:

One Response

  1. choukoukouamine

    choukoukouamine

    Reply

    19 January 2018 at 06:44

    Very interesting, thank you very much for sharing.

    Just a note, you do not need to declare a bean as a variable and retrieve it from the context, just use it with its alias or bean name as a variable.
    For example :
    instead of
    $ typeService = spring.getBean (“serviceType”);
    $ typePK = typeService.getTypeForCode (TYPE) .getPk ();
    Just:
    $ typePK = typeService.getTypeForCode (TYPE) .getPk ();

    This is very useful if we want to get the class of a bean if we want to know if we have the class we want.

Leave a Reply