agave-migrations/src/main/java/org/iplantc/service/common/migration/V2_2_27_10__Backfill_jobs_execution_type_scheduler_type.java
/**
     * Populates the execution_type column in the jobs table.
     *
     * @param connection the current database connectino passed in by Flyway
     * @throws SQLException if things go horribly wrong
     */
Populates the execution_type column in the jobs table.
@param connection the current database connectino passed in by Flyway
@throws SQLException if things go horribly wrong
Populates the execution_type column in the jobs table . @param connection the current database connectino passed in by Flyway @throws SQLException if things go horribly wrong
private void backfillSchedulerTypeAndExecutionType(Connection connection) throws SQLException {
        String jobSql;
        jobSql = "UPDATE jobs set execution_type = ?, scheduler_type = ? where tenant_id = ? and software_name = ?";
        int fetchSize = 100;
        int i = 0;
        try (PreparedStatement updateStmt = connection.prepareStatement(jobSql); Statement stmt = connection.createStatement()) {
            ResultSet rs = stmt.executeQuery("SELECT count(id) FROM softwares");
            rs.next();
            long softwareTableSize = rs.getLong(1);
            String sql = "SELECT s.tenant_id, s.name, s.version, s.publicly_available, s.revision_count, s.execution_type, e.scheduler_type FROM softwares s left join executionsystems e on s.system_id = e.id";
            stmt.setFetchSize(fetchSize);
            ResultSet softwareResultSet = stmt.executeQuery(sql);
            while (softwareResultSet.next()) {
                i++;
                String tenantId = softwareResultSet.getString(1);
                String name = softwareResultSet.getString(2);
                String version = softwareResultSet.getString(3);
                boolean publiclyAvailable = softwareResultSet.getBoolean(4);
                int revisionCount = softwareResultSet.getInt(5);
                String executionType = softwareResultSet.getString(6);
                String schedulerType = softwareResultSet.getString(7);
                if (executionType.equals("CLI")) {
                    schedulerType = "FORK";
                }
                String softwareUniqueName = String.format("%s-%s%s", name, version, publiclyAvailable ? "u" + revisionCount : "");
                updateStmt.setString(1, executionType);
                updateStmt.setString(2, schedulerType);
                updateStmt.setString(3, tenantId);
                updateStmt.setString(4, softwareUniqueName);
                updateStmt.addBatch();
                if (i % fetchSize == 0) {
                    updateStmt.executeBatch();
                    System.out.printf("[%d/%d] Migrating jobs table...%n", i, softwareTableSize);
                }
            }
            updateStmt.executeBatch();
        }
    }
private void backfillSchedulerTypeAndExecutionType ( Connection connection ) throws SQLException { String jobSql ; jobSql = "UPDATE jobs set execution_type = ?, scheduler_type = ? where tenant_id = ? and software_name = ?" ; int fetchSize = 100 ; int i = 0 ; try ( PreparedStatement updateStmt = connection . prepareStatement ( jobSql ) ; Statement stmt = connection . createStatement ( ) ) { ResultSet rs = stmt . executeQuery ( "SELECT count(id) FROM softwares" ) ; rs . next ( ) ; long softwareTableSize = rs . getLong ( 1 ) ; String sql = "SELECT s.tenant_id, s.name, s.version, s.publicly_available, s.revision_count, s.execution_type, e.scheduler_type FROM softwares s left join executionsystems e on s.system_id = e.id" ; stmt . setFetchSize ( fetchSize ) ; ResultSet softwareResultSet = stmt . executeQuery ( sql ) ; while ( softwareResultSet . next ( ) ) { i ++ ; String tenantId = softwareResultSet . getString ( 1 ) ; String name = softwareResultSet . getString ( 2 ) ; String version = softwareResultSet . getString ( 3 ) ; boolean publiclyAvailable = softwareResultSet . getBoolean ( 4 ) ; int revisionCount = softwareResultSet . getInt ( 5 ) ; String executionType = softwareResultSet . getString ( 6 ) ; String schedulerType = softwareResultSet . getString ( 7 ) ; if ( executionType . equals ( "CLI" ) ) { schedulerType = "FORK" ; } String softwareUniqueName = String . format ( "%s-%s%s" , name , version , publiclyAvailable ? "u" + revisionCount : "" ) ; updateStmt . setString ( 1 , executionType ) ; updateStmt . setString ( 2 , schedulerType ) ; updateStmt . setString ( 3 , tenantId ) ; updateStmt . setString ( 4 , softwareUniqueName ) ; updateStmt . addBatch ( ) ; if ( i % fetchSize == 0 ) { updateStmt . executeBatch ( ) ; System . out . printf ( "[%d/%d] Migrating jobs table...%n" , i , softwareTableSize ) ; } } updateStmt . executeBatch ( ) ; } }
Populates the execution_type column in the jobs table.
Populates the execution_type column in the jobs table .
// This is our job update query. We will update all job records for a given software id in a single query. // This query will be updated in batch as we finish processing a batch of software records. // Count the number of software records so we can monitor progress. // Query all the software records, batching in groups of {@code fetchSize} // Iterate over the result set, creating job update records for each software record and running them // when the batch of software results is complete. // Here we preserve the same construct used to generate the sofware id in our domain classes. // populate the query, observing tenancy since software unique names are only so within a tenant. // If we are at the last record in the currently fetched result set, then execute all the statements. // progress report just so we have it // Execute any remaining queries in case the number of software records was not a multiple of the // fetch size.