Redeploy of custom plugin using ServiceBuilder does not re-create database indexes after tables upgrade

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 6.1.0 CE GA1, 6.1.10 EE GA1, 6.2.0 CE B3
• Fix Version/s: 6.1.X EE, 6.2.0 CE RC1
• Component/s: Core Infrastructure > Service Builder, Environments, Environments > Databases
• Labels:
  • liferay-fixpack-development-2-6130
• Environment: Tomcat 7 + PostgreSQL 9.1. Portal 6.1.x EE GIT ID: d8c16ee8eee29699cd4d3d2a2f596dbe9aea67ea.; Tomcat 7 + PostgreSQL 9.1. Portal 6.2.x CE GIT ID: 8f35f9c075878f7b1769c05d2f8c7487e8c3e7c7.
• Branch Version/s:
  6.1.x
• Backported to Branch:
  Committed
• Story Points: 4
• Fix Priority: 4
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/13722

When a custom plugin is using ServiceBuilder, custom database tables are automatically upgraded during deploy (if they changed since last deploy). But such upgrade never recreates database indexes on those custom tables (usually located in WEB-INF/sql/indexes.sql).

The responsible code goes through PluginPackageHotDeployListener.doInvokeDeploy() -> initServiceComponent() -> ServiceComponentLocalServiceImpl.initServiceComponent() -> upgradeDB() -> doUpgradeDB() ... -> upgradeModels().

For every entity in plugin, upgradeModels() backs up the DB table into temp file, then drops the table from DB and creates it based on new createSQL (this is taken from static field on XXXModelImpl class). Indexes defined in WEB-INF/sql/indexes.sql are never created again.

This can of course cause major performance issues, when DB tables grow in size and ServiceBuilder generated DB queries cannot use database indexes.

Reproduction Steps

1a - Use this sample portlet project.
1b - Alternatively you can create an emptry portlet with the following service.xml.

<?xml version="1.0"?>
<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.2.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_2_0.dtd">

<service-builder package-path="com.liferay.support">
    <namespace>hd</namespace>

    <entity name="Test" local-service="true" remote-service="false">
        <!-- Primary key column -->
        <column name="testId" type="long" primary="true" />

        <!-- Foreign key columns -->
        <column name="companyId" type="long" />
        <column name="userId" type="long" />

        <!-- Other columns -->		
        <column name="test" type="String" />

        <finder name="C_U" return-type="Collection">
            <finder-column name="companyId" />
            <finder-column name="userId" />
        </finder> 
    </entity>
</service-builder>

2 - Build.

$ ant build-service
$ ant all

3 - Deploy the portlet to a Liferay.
4 - Check table hd_test; you'll see that there is an index ix_33e60be7 that holds companyId and userId.
5 - Edit the service.xml and comment out the following or remove it completely.

<column name="test" type="String" />

6 - Perform steps 2,3,4 again.

You'll see that the index has been removed from the database. Actually table hd_test has been dropped and re-created, but its index ix_33e60be7 has not.