Release Notes for 2.0.0-GA

INSERT INTO offer.blc_campaign(id, active_end_date, active_start_date, code_prefix, name, trk_application_context_id,
                               trk_application_default_locale, trk_application_name, trk_archived, trk_change_details,
                               trk_creating_application_id, trk_current_message, trk_level, trk_max_field_versions,
                               trk_sandbox_archived, trk_sub_container_catalog, trk_change_container_id,
                               trk_change_container_name, trk_change_container_route_key, trk_sub_container_name,
                               trk_sandbox_change_type, trk_sandbox_context_id, trk_sandbox_name, trk_sandbox_owner,
                               trk_sandbox_stage, trk_tenant_id, audit_creation_time, audit_creator, audit_update_time,
                               audit_updater)
SELECT id,
       active_end_date,
       active_start_date,
       code_prefix,
       name,
       trk_application_context_id,
       trk_application_default_locale,
       trk_application_name,
       trk_archived,
       trk_change_details,
       trk_creating_application_id,
       trk_current_message,
       trk_level,
       trk_max_field_versions,
       trk_sandbox_archived,
       trk_sub_container_catalog,
       trk_change_container_id,
       trk_change_container_name,
       trk_change_container_route_key,
       trk_sub_container_name,
       trk_sandbox_change_type,
       trk_sandbox_context_id,
       trk_sandbox_name,
       trk_sandbox_owner,
       trk_sandbox_stage,
       trk_tenant_id,
       audit_creation_time,
       audit_creator,
       audit_update_time,
       audit_updater
FROM campaign.blc_campaign;

INSERT INTO offer.blc_code_generator(id, active_end_date, active_start_date, allowed_letters, allowed_numbers,
                                     campaign_id, code_format, message, status, code_length, code_prefix, create_date,
                                     email_regex, max_uses, number_generated, number_to_generate,
                                     trk_application_context_id, trk_application_default_locale, trk_application_name,
                                     trk_archived, audit_creation_time, audit_creator, audit_update_time, audit_updater,
                                     trk_change_details, trk_creating_application_id, trk_current_message, trk_level,
                                     trk_max_field_versions, trk_sandbox_archived, trk_sub_container_catalog,
                                     trk_change_container_id, trk_change_container_name, trk_change_container_route_key,
                                     trk_sub_container_name, trk_sandbox_change_type, trk_sandbox_context_id,
                                     trk_sandbox_name, trk_sandbox_owner, trk_sandbox_stage, trk_tenant_id, update_date,
                                     is_for_voucher)
SELECT id,
       active_end_date,
       active_start_date,
       allowed_letters,
       allowed_numbers,
       campaign_id,
       code_format,
       message,
       status,
       code_length,
       code_prefix,
       create_date,
       email_regex,
       max_uses,
       number_generated,
       number_to_generate,
       trk_application_context_id,
       trk_application_default_locale,
       trk_application_name,
       trk_archived,
       audit_creation_time,
       audit_creator,
       audit_update_time,
       audit_updater,
       trk_change_details,
       trk_creating_application_id,
       trk_current_message,
       trk_level,
       trk_max_field_versions,
       trk_sandbox_archived,
       trk_sub_container_catalog,
       trk_change_container_id,
       trk_change_container_name,
       trk_change_container_route_key,
       trk_sub_container_name,
       trk_sandbox_change_type,
       trk_sandbox_context_id,
       trk_sandbox_name,
       trk_sandbox_owner,
       trk_sandbox_stage,
       trk_tenant_id,
       update_date,
       is_for_voucher
FROM campaign.blc_code_generator;

INSERT INTO offer.blc_offer_code (id, active_end_date, active_start_date, campaign_id, code, email_regex, generator_id,
                                  max_uses, trk_application_context_id, trk_application_default_locale,
                                  trk_application_name, trk_archived, trk_change_details, trk_creating_application_id,
                                  trk_current_message, trk_level, trk_max_field_versions, trk_sandbox_archived,
                                  trk_sub_container_catalog, trk_change_container_id, trk_change_container_name,
                                  trk_change_container_route_key, trk_sub_container_name, trk_sandbox_change_type,
                                  trk_sandbox_context_id, trk_sandbox_name, trk_sandbox_owner, trk_sandbox_stage,
                                  trk_tenant_id, audit_creation_time, audit_creator, audit_update_time, audit_updater,
                                  is_assigned)
SELECT id,
       active_end_date,
       active_start_date,
       campaign_id,
       code,
       email_regex,
       generator_id,
       max_uses,
       trk_application_context_id,
       trk_application_default_locale,
       trk_application_name,
       trk_archived,
       trk_change_details,
       trk_creating_application_id,
       trk_current_message,
       trk_level,
       trk_max_field_versions,
       trk_sandbox_archived,
       trk_sub_container_catalog,
       trk_change_container_id,
       trk_change_container_name,
       trk_change_container_route_key,
       trk_sub_container_name,
       trk_sandbox_change_type,
       trk_sandbox_context_id,
       trk_sandbox_name,
       trk_sandbox_owner,
       trk_sandbox_stage,
       trk_tenant_id,
       audit_creation_time,
       audit_creator,
       audit_update_time,
       audit_updater,
       is_assigned
FROM campaign.blc_campaign_code;

INSERT INTO offer.blc_offer_audit_detail(id, date_applied, offer_id, trk_archived, trk_change_details, trk_tenant_id,
                                         user_target_type, user_target_value, audit_creation_time, audit_creator,
                                         audit_update_time, audit_updater,
                                         campaign_tracking_id, offer_code_id,
                                         trk_application_context_id, trk_application_default_locale,
                                         trk_application_name, trk_creating_application_id, trk_current_message,
                                         trk_level, trk_max_field_versions, trk_sandbox_archived,
                                         trk_sub_container_catalog, trk_change_container_id, trk_change_container_name,
                                         trk_change_container_route_key, trk_sub_container_name,
                                         trk_sandbox_change_type, trk_sandbox_context_id, trk_sandbox_name,
                                         trk_sandbox_owner, trk_sandbox_stage)
SELECT cad.id,
       cad.date_applied,
       oc.offer_id,
       cad.trk_archived,
       cad.trk_change_details,
       cad.trk_tenant_id,
       cad.target_type  as user_target_type,
       cad.target_value as user_target_value,
       cad.audit_creation_time,
       cad.audit_creator,
       cad.audit_update_time,
       cad.audit_updater,
       cad.campaign_tracking_id,
       oc.id            as offer_code_id,
       cad.trk_application_context_id,
       cad.trk_application_default_locale,
       cad.trk_application_name,
       cad.trk_creating_application_id,
       cad.trk_current_message,
       cad.trk_level,
       cad.trk_max_field_versions,
       cad.trk_sandbox_archived,
       cad.trk_sub_container_catalog,
       cad.trk_change_container_id,
       cad.trk_change_container_name,
       cad.trk_change_container_route_key,
       cad.trk_sub_container_name,
       cad.trk_sandbox_change_type,
       cad.trk_sandbox_context_id,
       cad.trk_sandbox_name,
       cad.trk_sandbox_owner,
       cad.trk_sandbox_stage
FROM campaign.blc_campaign_audit_detail cad
         JOIN offer.blc_offer_code oc ON cad.campaign_code = oc.code AND cad.campaign_tracking_id = oc.campaign_id;

INSERT INTO offer.blc_export (id, addl_parameters, application_id, author, created, error_message, exclusions,
                              exporting_application_id, exporting_catalog_id, exporting_customer_context_id,
                              exporting_sandbox_id, file_location, file_type, filter_string, inclusions, last_updated,
                              name, number_of_records_to_process, parent_id, status, target, tenant_id, time_zone_id,
                              total_records_to_process)
SELECT id,
       addl_parameters,
       application_id,
       author,
       created,
       error_message,
       exclusions,
       exporting_application_id,
       exporting_catalog_id,
       exporting_customer_context_id,
       exporting_sandbox_id,
       file_location,
       file_type,
       filter_string,
       inclusions,
       last_updated,
       name,
       number_of_records_to_process,
       parent_id,
       status,
       target,
       tenant_id,
       time_zone_id,
       total_records_to_process
FROM campaign.blc_export;

INSERT INTO offer.blc_export_lob (id, content, path)
SELECT id, content, path
FROM campaign.blc_export_lob;

INSERT INTO offer.blc_notification_state (id, acked, attempts, change_timestamp, change_timestamp_ack, container,
                                          entity_type, message_type, message_value, notification_name, next_attempt,
                                          stopped)
SELECT id,
       acked,
       attempts,
       change_timestamp,
       change_timestamp_ack,
       container,
       entity_type,
       message_type,
       message_value,
       notification_name,
       next_attempt,
       stopped
FROM campaign.blc_notification_state;

INSERT INTO offer.blc_resource_lock (id, concept_key, context_id, lock_expiration_timestamp, locked, sandbox_id,
                                     lock_timestamp, token, type_alias)
SELECT id,
       concept_key,
       context_id,
       lock_expiration_timestamp,
       locked,
       sandbox_id,
       lock_timestamp,
       token,
       type_alias
FROM campaign.blc_resource_lock;

public class SharedCodesToOfferCodesMigration implements CustomTaskChange {

    protected static final int BATCH_SIZE = 20;

    @Override
    public void execute(Database database) throws CustomChangeException {
        int batchCounter = 0;

        try {
            JdbcConnection jdbcConnection = (JdbcConnection) database.getConnection();

            PreparedStatement queryStatement = jdbcConnection.prepareStatement(
                    "SELECT sc.code, o.context_id as offer_id, sc.max_uses, o.trk_tenant_id, o.trk_application_context_id FROM blc_shared_code sc JOIN blc_offer o ON sc.offer_id = o.id WHERE o.trk_archived != 'Y'");
            PreparedStatement insertOfferCodeStatement = jdbcConnection.prepareStatement(
                    "INSERT INTO blc_offer_code (id, code, offer_id, max_uses, trk_tenant_id, trk_application_context_id, trk_level) VALUES (?, ?, ?, ?, ?, ?, ?)");
            PreparedStatement updateOfferAuditDetailStatement =
                    jdbcConnection.prepareStatement(
                            "UPDATE blc_offer_audit_detail SET offer_code_id=?, trk_application_context_id=?, trk_level=? WHERE shared_code=? AND offer_id=?");

            ResultSet sharedCodes = queryStatement.executeQuery();
            while (sharedCodes.next()) {
                String offerCodeId = ULID.random();
                String code = sharedCodes.getString("code");
                String offerId = sharedCodes.getString("offer_id");
                int maxUses = sharedCodes.getInt("max_uses");
                String tenantId = sharedCodes.getString("trk_tenant_id");
                String applicationContextId = sharedCodes.getString("trk_application_context_id");
                int trackingLevel = sharedCodes.getInt("trk_level");

                insertOfferCodeStatement.setString(1, offerCodeId);
                insertOfferCodeStatement.setString(2, code);
                insertOfferCodeStatement.setString(3, offerId);
                insertOfferCodeStatement.setInt(4, maxUses);
                insertOfferCodeStatement.setString(5, tenantId);
                insertOfferCodeStatement.setString(6, applicationContextId);
                if (trackingLevel == 0) {
                    insertOfferCodeStatement.setNull(7, 4);
                } else {
                    insertOfferCodeStatement.setInt(7, trackingLevel);
                }
                insertOfferCodeStatement.addBatch();

                updateOfferAuditDetailStatement.setString(1, offerCodeId);
                updateOfferAuditDetailStatement.setString(2, applicationContextId);
                if (trackingLevel == 0) {
                    updateOfferAuditDetailStatement.setNull(3, 4);
                } else {
                    updateOfferAuditDetailStatement.setInt(3, trackingLevel);
                }
                updateOfferAuditDetailStatement.setString(4, code);
                updateOfferAuditDetailStatement.setString(5, offerId);
                updateOfferAuditDetailStatement.addBatch();

                if (++batchCounter % BATCH_SIZE == 0) {
                    insertOfferCodeStatement.executeBatch();
                    updateOfferAuditDetailStatement.executeBatch();
                }
            }

            insertOfferCodeStatement.executeBatch();
            updateOfferAuditDetailStatement.executeBatch();

            sharedCodes.close();
            insertOfferCodeStatement.close();
            updateOfferAuditDetailStatement.close();
            queryStatement.close();
            // don't close DB connection

        } catch (DatabaseException | SQLException e) {
            throw new CustomChangeException(e);
        }
    }

    @Override
    public String getConfirmationMessage() {
        return "The data from blc_shared_code successfully migrated to blc_offer_code, blc_offer_audit_detail also updated!";
    }

    @Override
    public void setUp() throws SetupException {
        // do nothing
    }

    @Override
    public void setFileOpener(ResourceAccessor resourceAccessor) {
        // do nothing
    }

    @Override
    public ValidationErrors validate(Database database) {
        return null;
    }
}

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
     <changeSet id="shared-code-migration" author="broadleaf">
        <customChange class="com.myapp.changeset.SharedCodesToOfferCodesMigration" />
     </changeSet>
</databaseChangeLog>

public class PopulateOfferAndOfferCodeAuditSummaryMigration implements CustomTaskChange {

    protected static final int BATCH_SIZE = 20;

    @Override
    public void execute(Database database) throws CustomChangeException {
        try {
            JdbcConnection jdbcConnection = (JdbcConnection) database.getConnection();

            populateOfferAuditSummary(jdbcConnection);
            populateSharedCodeAuditSummary(jdbcConnection);

        } catch (DatabaseException | SQLException e) {
            throw new CustomChangeException(e);
        }
    }

    private void populateOfferAuditSummary(JdbcConnection jdbcConnection)
            throws DatabaseException, SQLException {
        PreparedStatement queryStatementForOfferAuditSummary =
                jdbcConnection.prepareStatement(
                        "SELECT detail.offer_id, COUNT(detail.*) AS recordCount, detail.trk_application_context_id, detail.trk_level, detail.trk_tenant_id " +
                                "FROM blc_offer_audit_detail detail " +
                                "LEFT JOIN blc_offer_audit_summary summary " +
                                "  ON detail.offer_id = summary.offer_id " +
                                "    AND COALESCE(detail.trk_application_context_id, 'null') = COALESCE(summary.trk_application_context_id, 'null') " +
                                "    AND detail.trk_tenant_id = summary.trk_tenant_id " +
                                "WHERE detail.trk_archived != 'Y' AND summary.id IS NULL " +
                                "GROUP BY detail.offer_id, detail.trk_application_context_id, detail.trk_level, detail.trk_tenant_id ");
        PreparedStatement insertOfferAuditSummaryStatement =
                jdbcConnection.prepareStatement(
                        "INSERT INTO blc_offer_audit_summary (id, offer_id, total_usages, trk_application_context_id, trk_level, trk_tenant_id) VALUES (?, ?, ?, ?, ?, ?)");

        int batchCounter = 0;
        ResultSet offerAuditDetails = queryStatementForOfferAuditSummary.executeQuery();
        while (offerAuditDetails.next()) {
            String offerId = offerAuditDetails.getString("offer_id");
            int totalUsages = offerAuditDetails.getInt("recordCount");
            String applicationContextId = offerAuditDetails.getString("trk_application_context_id");
            int trackingLevel = offerAuditDetails.getInt("trk_level");
            String tenantId = offerAuditDetails.getString("trk_tenant_id");

            // need to build primary key, see JpaAlternateLifecycleOfferAuditSummaryRepository
            ContextInfo contextInfo = new ContextInfo();
            ContextRequest contextRequest =
                    (new ContextRequest())
                            .withApplicationId(applicationContextId)
                            .withTenantId(tenantId);
            contextInfo.setContextRequest(contextRequest);
            String offerAuditSummaryId = KeyUtils.buildKey(offerId, contextInfo);

            insertOfferAuditSummaryStatement.setString(1, offerAuditSummaryId);
            insertOfferAuditSummaryStatement.setString(2, offerId);
            insertOfferAuditSummaryStatement.setInt(3, totalUsages);
            insertOfferAuditSummaryStatement.setString(4, applicationContextId);

            if (trackingLevel == 0) {
                insertOfferAuditSummaryStatement.setNull(5, 4);
            } else {
                insertOfferAuditSummaryStatement.setInt(5, trackingLevel);
            }

            insertOfferAuditSummaryStatement.setString(6, tenantId);
            insertOfferAuditSummaryStatement.addBatch();

            if (++batchCounter % BATCH_SIZE == 0) {
                insertOfferAuditSummaryStatement.executeBatch();
            }
        }

        insertOfferAuditSummaryStatement.executeBatch();

        insertOfferAuditSummaryStatement.close();
        // don't close DB connection
    }

    private void populateSharedCodeAuditSummary(JdbcConnection jdbcConnection)
            throws DatabaseException, SQLException {
        PreparedStatement queryStatementForSharedCodeAuditSummary =
                jdbcConnection.prepareStatement(
                        "SELECT detail.offer_code_id, COUNT(detail.*) AS recordCount, detail.shared_code, detail.trk_application_context_id, detail.trk_level, detail.trk_tenant_id " +
                                "FROM blc_offer_audit_detail detail " +
                                "LEFT JOIN blc_shared_code_audit_summary summary " +
                                "  ON detail.offer_code_id = summary.offer_code_id " +
                                "    AND COALESCE(detail.trk_application_context_id, 'null') = COALESCE(summary.trk_application_context_id, 'null') " +
                                "    AND detail.trk_tenant_id = summary.trk_tenant_id " +
                                "WHERE detail.trk_archived != 'Y' AND detail.shared_code IS NOT NULL AND summary.id IS NULL " +
                                "GROUP BY detail.offer_code_id, detail.shared_code, detail.trk_application_context_id, detail.trk_level, detail.trk_tenant_id ");
        PreparedStatement insertSharedCodeAuditSummaryStatement =
                jdbcConnection.prepareStatement(
                        "INSERT INTO blc_shared_code_audit_summary (id, offer_code_id, total_usages, offer_code, trk_application_context_id, trk_level, trk_tenant_id) VALUES (?, ?, ?, ?, ?, ?, ?)");

        int batchCounter = 0;
        ResultSet offerAuditDetailsForOfferCodes =
                queryStatementForSharedCodeAuditSummary.executeQuery();

        while (offerAuditDetailsForOfferCodes.next()) {
            String offerCodeId = offerAuditDetailsForOfferCodes.getString("offer_code_id");
            int totalUsages = offerAuditDetailsForOfferCodes.getInt("recordCount");
            String code = offerAuditDetailsForOfferCodes.getString("shared_code");
            String applicationContextId =
                    offerAuditDetailsForOfferCodes.getString("trk_application_context_id");
            int trackingLevel = offerAuditDetailsForOfferCodes.getInt("trk_level");
            String tenantId = offerAuditDetailsForOfferCodes.getString("trk_tenant_id");

            // need to build primary key, see JpaAlternateLifecycleSharedCodeAuditSummaryRepository
            ContextInfo contextInfo = new ContextInfo();
            ContextRequest contextRequest =
                    (new ContextRequest())
                            .withApplicationId(applicationContextId)
                            .withTenantId(tenantId);
            contextInfo.setContextRequest(contextRequest);
            String sharedCodeAuditSummaryId = KeyUtils.buildKey(offerCodeId, contextInfo);

            insertSharedCodeAuditSummaryStatement.setString(1, sharedCodeAuditSummaryId);
            insertSharedCodeAuditSummaryStatement.setString(2, offerCodeId);
            insertSharedCodeAuditSummaryStatement.setInt(3, totalUsages);
            insertSharedCodeAuditSummaryStatement.setString(4, code);
            insertSharedCodeAuditSummaryStatement.setString(5, applicationContextId);

            if (trackingLevel == 0) {
                insertSharedCodeAuditSummaryStatement.setNull(6, 4);
            } else {
                insertSharedCodeAuditSummaryStatement.setInt(6, trackingLevel);
            }

            insertSharedCodeAuditSummaryStatement.setString(7, tenantId);
            insertSharedCodeAuditSummaryStatement.addBatch();

            if (++batchCounter % BATCH_SIZE == 0) {
                insertSharedCodeAuditSummaryStatement.executeBatch();
            }
        }

        insertSharedCodeAuditSummaryStatement.executeBatch();

        insertSharedCodeAuditSummaryStatement.close();
        // don't close DB connection
    }

    @Override
    public String getConfirmationMessage() {
        return "The data for blc_offer_audit_summary and blc_shared_code_audit_summary successfully populated!";
    }

    @Override
    public void setUp() throws SetupException {
        // do nothing
    }

    @Override
    public void setFileOpener(ResourceAccessor resourceAccessor) {
        // do nothing
    }

    @Override
    public ValidationErrors validate(Database database) {
        return null;
    }
}

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
     <changeSet id="shared-code-migration" author="broadleaf">
        <customChange class="com.myapp.changeset.SharedCodesToOfferCodesMigration" />
     </changeSet>
    <changeSet id="offer-and-offer-code-audit-summary-migration" author="broadleaf">
        <customChange class="com.myapp.changeset.PopulateOfferAndOfferCodeAuditSummaryMigration" />
    </changeSet>
</databaseChangeLog>