/* * The MIT License * * Copyright (c) 2016, CloudBees, Inc. * * Permission is hereby granted, free of charge, to any person obtaining a copy * of this software and associated documentation files (the "Software"), to deal * in the Software without restriction, including without limitation the rights * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell * copies of the Software, and to permit persons to whom the Software is * furnished to do so, subject to the following conditions: * * The above copyright notice and this permission notice shall be included in * all copies or substantial portions of the Software. * * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN * THE SOFTWARE. */ package org.jenkinsci.plugins.pipeline.maven.dao; import hudson.model.Item; import hudson.model.Result; import hudson.model.Run; import org.apache.commons.io.IOUtils; import org.apache.commons.lang.StringUtils; import org.h2.api.ErrorCode; import org.jenkinsci.plugins.pipeline.maven.MavenArtifact; import org.jenkinsci.plugins.pipeline.maven.MavenDependency; import org.jenkinsci.plugins.pipeline.maven.db.migration.MigrationStep; import org.jenkinsci.plugins.pipeline.maven.util.ClassUtils; import org.jenkinsci.plugins.pipeline.maven.util.RuntimeIoException; import org.jenkinsci.plugins.pipeline.maven.util.RuntimeSqlException; import java.io.Closeable; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.text.DecimalFormat; import java.text.NumberFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.Objects; import java.util.SortedSet; import java.util.TreeSet; import java.util.logging.Level; import java.util.logging.Logger; import javax.annotation.Nonnull; import javax.annotation.Nullable; import javax.sql.DataSource; /** * @author <a href="mailto:[email protected]">Cyrille Le Clerc</a> */ public abstract class AbstractPipelineMavenPluginDao implements PipelineMavenPluginJdbcDao, Closeable { private static final int OPTIMIZATION_MAX_RECURSION_DEPTH = Integer.getInteger("org.jenkinsci.plugins.pipeline.PipelineMavenPluginDao.OPTIMIZATION_MAX_RECURSION_DEPTH",3); protected final Logger LOGGER = Logger.getLogger(getClass().getName()); @Nonnull private transient DataSource ds; @Nullable private transient Long jenkinsMasterPrimaryKey; public AbstractPipelineMavenPluginDao(@Nonnull DataSource ds) { ds.getClass(); // check non null this.ds = ds; registerJdbcDriver(); initializeDatabase(); testDatabase(); } protected abstract void registerJdbcDriver(); @Override public void recordDependency(String jobFullName, int buildNumber, String groupId, String artifactId, String version, String type, String scope, boolean ignoreUpstreamTriggers, String classifier) { LOGGER.log(Level.FINE, "recordDependency({0}#{1}, {2}:{3}:{4}:{5}, {6}, ignoreUpstreamTriggers:{7}})", new Object[]{jobFullName, buildNumber, groupId, artifactId, version, type, scope, ignoreUpstreamTriggers}); long buildPrimaryKey = getOrCreateBuildPrimaryKey(jobFullName, buildNumber); long artifactPrimaryKey = getOrCreateArtifactPrimaryKey(groupId, artifactId, version, type, classifier); try (Connection cnn = ds.getConnection()) { cnn.setAutoCommit(false); try (PreparedStatement stmt = cnn.prepareStatement("INSERT INTO MAVEN_DEPENDENCY(ARTIFACT_ID, BUILD_ID, SCOPE, IGNORE_UPSTREAM_TRIGGERS) VALUES (?, ?, ?, ?)")) { stmt.setLong(1, artifactPrimaryKey); stmt.setLong(2, buildPrimaryKey); stmt.setString(3, scope); stmt.setBoolean(4, ignoreUpstreamTriggers); stmt.execute(); } cnn.commit(); } catch (SQLException e) { throw new RuntimeSqlException(e); } } @Nonnull @Override public List<MavenDependency> listDependencies(@Nonnull String jobFullName, int buildNumber) { LOGGER.log(Level.FINER, "listDependencies({0}, {1})", new Object[]{jobFullName, buildNumber}); String dependenciesSql = "SELECT DISTINCT MAVEN_ARTIFACT.group_id, MAVEN_ARTIFACT.artifact_id, MAVEN_ARTIFACT.version, MAVEN_ARTIFACT.type, MAVEN_ARTIFACT.classifier, MAVEN_DEPENDENCY.scope " + " FROM MAVEN_ARTIFACT " + " INNER JOIN MAVEN_DEPENDENCY ON MAVEN_ARTIFACT.ID = MAVEN_DEPENDENCY.ARTIFACT_ID" + " INNER JOIN JENKINS_BUILD ON MAVEN_DEPENDENCY.BUILD_ID = JENKINS_BUILD.ID " + " INNER JOIN JENKINS_JOB ON JENKINS_BUILD.JOB_ID = JENKINS_JOB.ID " + " WHERE " + " JENKINS_JOB.FULL_NAME = ? AND" + " JENKINS_BUILD.NUMBER = ? "; List<MavenDependency> results = new ArrayList<>(); try (Connection cnn = this.ds.getConnection()) { try (PreparedStatement stmt = cnn.prepareStatement(dependenciesSql)) { stmt.setString(1, jobFullName); stmt.setInt(2, buildNumber); try (ResultSet rst = stmt.executeQuery()) { while (rst.next()) { MavenDependency artifact = new MavenDependency(); artifact.setGroupId(rst.getString("group_id")); artifact.setArtifactId(rst.getString("artifact_id")); artifact.setVersion(rst.getString("version")); artifact.setSnapshot(artifact.getVersion().endsWith("-SNAPSHOT")); artifact.setType(rst.getString("type")); artifact.setClassifier(rst.getString("classifier")); artifact.setScope(rst.getString("scope")); results.add(artifact); } } } } catch (SQLException e) { throw new RuntimeSqlException(e); } Collections.sort(results); return results; } @Override public void recordParentProject(@Nonnull String jobFullName, int buildNumber, @Nonnull String parentGroupId, @Nonnull String parentArtifactId, @Nonnull String parentVersion, boolean ignoreUpstreamTriggers) { LOGGER.log(Level.FINE, "recordParentProject({0}#{1}, {2}:{3} ignoreUpstreamTriggers:{5}})", new Object[]{jobFullName, buildNumber, parentGroupId, parentArtifactId, parentVersion, ignoreUpstreamTriggers}); long buildPrimaryKey = getOrCreateBuildPrimaryKey(jobFullName, buildNumber); long parentArtifactPrimaryKey = getOrCreateArtifactPrimaryKey(parentGroupId, parentArtifactId, parentVersion, "pom", null); try (Connection cnn = ds.getConnection()) { cnn.setAutoCommit(false); try (PreparedStatement stmt = cnn.prepareStatement("INSERT INTO MAVEN_PARENT_PROJECT(ARTIFACT_ID, BUILD_ID, IGNORE_UPSTREAM_TRIGGERS) VALUES (?, ?, ?)")) { stmt.setLong(1, parentArtifactPrimaryKey); stmt.setLong(2, buildPrimaryKey); stmt.setBoolean(3, ignoreUpstreamTriggers); stmt.execute(); } cnn.commit(); } catch (SQLException e) { throw new RuntimeSqlException(e); } } @Override public void recordGeneratedArtifact(String jobFullName, int buildNumber, String groupId, String artifactId, String version, String type, String baseVersion, String repositoryUrl, boolean skipDownstreamTriggers, String extension, String classifier) { LOGGER.log(Level.FINE, "recordGeneratedArtifact({0}#{1}, {2}:{3}:{4}:{5}, version:{6}, repositoryUrl:{7}, skipDownstreamTriggers:{8})", new Object[]{jobFullName, buildNumber, groupId, artifactId, baseVersion, type, version, repositoryUrl, skipDownstreamTriggers}); long buildPrimaryKey = getOrCreateBuildPrimaryKey(jobFullName, buildNumber); long artifactPrimaryKey = getOrCreateArtifactPrimaryKey(groupId, artifactId, baseVersion, type, classifier); try (Connection cnn = ds.getConnection()) { cnn.setAutoCommit(false); try (PreparedStatement stmt = cnn.prepareStatement("INSERT INTO GENERATED_MAVEN_ARTIFACT(ARTIFACT_ID, BUILD_ID, VERSION, REPOSITORY_URL, EXTENSION, SKIP_DOWNSTREAM_TRIGGERS) VALUES (?, ?, ?, ?, ?, ?)")) { stmt.setLong(1, artifactPrimaryKey); stmt.setLong(2, buildPrimaryKey); stmt.setString(3, version); stmt.setString(4, repositoryUrl); stmt.setString(5, extension); stmt.setBoolean(6, skipDownstreamTriggers); stmt.execute(); } cnn.commit(); } catch (SQLException e) { throw new RuntimeSqlException(e); } } @Override public void recordBuildUpstreamCause(String upstreamJobName, int upstreamBuildNumber, String downstreamJobName, int downstreamBuildNumber) { LOGGER.log(Level.FINE, "recordBuildUpstreamCause(upstreamBuild: {0}#{1}, downstreamBuild: {2}#{3})", new Object[]{upstreamJobName, upstreamBuildNumber, downstreamJobName, downstreamBuildNumber}); try (Connection cnn = ds.getConnection()) { cnn.setAutoCommit(false); String sql = "insert into JENKINS_BUILD_UPSTREAM_CAUSE (upstream_build_id, downstream_build_id) values (?, ?)"; long upstreamBuildPrimaryKey = getOrCreateBuildPrimaryKey(upstreamJobName, upstreamBuildNumber); long downstreamBuildPrimaryKey = getOrCreateBuildPrimaryKey(downstreamJobName, downstreamBuildNumber); try (PreparedStatement stmt = cnn.prepareStatement(sql)) { stmt.setLong(1, upstreamBuildPrimaryKey); stmt.setLong(2, downstreamBuildPrimaryKey); int rowCount = stmt.executeUpdate(); if (rowCount != 1) { LOGGER.log(Level.INFO, "More/less ({0}) than 1 record inserted in JENKINS_BUILD_UPSTREAM_CAUSE for upstreamBuild: {1}#{2}, downstreamBuild: {3}#{4}", new Object[]{rowCount, upstreamJobName, upstreamBuildNumber, downstreamJobName, downstreamBuildNumber}); } } cnn.commit(); } catch (SQLException e) { throw new RuntimeSqlException(e); } } @Override public void renameJob(String oldFullName, String newFullName) { LOGGER.log(Level.FINER, "renameJob({0}, {1})", new Object[]{oldFullName, newFullName}); try (Connection cnn = ds.getConnection()) { cnn.setAutoCommit(false); try (PreparedStatement stmt = cnn.prepareStatement("UPDATE JENKINS_JOB SET FULL_NAME = ? WHERE FULL_NAME = ? AND JENKINS_MASTER_ID = ?")) { stmt.setString(1, newFullName); stmt.setString(2, oldFullName); stmt.setLong(3, getJenkinsMasterPrimaryKey(cnn)); int count = stmt.executeUpdate(); LOGGER.log(Level.FINE, "renameJob({0}, {1}): {2}", new Object[]{oldFullName, newFullName, count}); } cnn.commit(); } catch (SQLException e) { throw new RuntimeSqlException(e); } } @Override public void deleteJob(String jobFullName) { LOGGER.log(Level.FINER, "deleteJob({0})", new Object[]{jobFullName}); try (Connection cnn = ds.getConnection()) { cnn.setAutoCommit(false); try (PreparedStatement stmt = cnn.prepareStatement("DELETE FROM JENKINS_JOB WHERE FULL_NAME = ? AND JENKINS_MASTER_ID = ?")) { stmt.setString(1, jobFullName); stmt.setLong(2, getJenkinsMasterPrimaryKey(cnn)); int count = stmt.executeUpdate(); LOGGER.log(Level.FINE, "deleteJob({0}): {1}", new Object[]{jobFullName, count}); } cnn.commit(); } catch (SQLException e) { throw new RuntimeSqlException(e); } } @Override public void deleteBuild(String jobFullName, int buildNumber) { LOGGER.log(Level.FINER, "deleteBuild({0}#{1})", new Object[]{jobFullName, buildNumber}); try (Connection cnn = ds.getConnection()) { cnn.setAutoCommit(false); Long jobPrimaryKey; Integer lastBuildNumber; Integer lastSuccessfulBuildNumber; try (PreparedStatement stmt = cnn.prepareStatement("SELECT ID, LAST_BUILD_NUMBER, LAST_SUCCESSFUL_BUILD_NUMBER FROM JENKINS_JOB WHERE FULL_NAME = ? AND JENKINS_MASTER_ID = ?")) { stmt.setString(1, jobFullName); stmt.setLong(2, getJenkinsMasterPrimaryKey(cnn)); try (ResultSet rst = stmt.executeQuery()) { if (rst.next()) { jobPrimaryKey = rst.getLong("ID"); lastBuildNumber = rst.getInt("LAST_BUILD_NUMBER"); lastSuccessfulBuildNumber = rst.getInt("LAST_SUCCESSFUL_BUILD_NUMBER"); } else { jobPrimaryKey = null; lastBuildNumber = null; lastSuccessfulBuildNumber = null; } } } if (jobPrimaryKey == null) { LOGGER.log(Level.FINE, "No record found for job {0}", new Object[]{jobFullName}); return; } if (buildNumber == lastBuildNumber || buildNumber == lastSuccessfulBuildNumber) { Integer newLastBuildNumber = (lastBuildNumber == buildNumber) ? null : lastBuildNumber; Integer newLastSuccessfulBuildNumber = (lastSuccessfulBuildNumber == buildNumber) ? null : lastSuccessfulBuildNumber; try (PreparedStatement stmt = cnn.prepareStatement("SELECT JENKINS_BUILD.number, JENKINS_BUILD.result_id FROM JENKINS_BUILD WHERE JOB_ID = ? AND NUMBER != ? ORDER BY NUMBER DESC")) { stmt.setLong(1, jobPrimaryKey); stmt.setInt(2, buildNumber); stmt.setFetchSize(5); try (ResultSet rst = stmt.executeQuery()) { while (rst.next() && (newLastBuildNumber == null || newLastSuccessfulBuildNumber == null)) { int currentBuildNumber = rst.getInt("number"); int currentBuildResultId = rst.getInt("result_id"); if(newLastBuildNumber == null) { newLastBuildNumber = currentBuildNumber; } if (newLastSuccessfulBuildNumber == null && Result.SUCCESS.ordinal == currentBuildResultId) { newLastSuccessfulBuildNumber = currentBuildNumber; } } } } try(PreparedStatement stmt = cnn.prepareStatement("UPDATE JENKINS_JOB SET LAST_BUILD_NUMBER = ?, LAST_SUCCESSFUL_BUILD_NUMBER = ? WHERE ID = ?")) { stmt.setInt(1, newLastBuildNumber); stmt.setInt(2, newLastSuccessfulBuildNumber); stmt.setLong(3, jobPrimaryKey); stmt.execute(); } } try (PreparedStatement stmt = cnn.prepareStatement("DELETE FROM JENKINS_BUILD WHERE JOB_ID = ? AND NUMBER = ?")) { stmt.setLong(1, jobPrimaryKey); stmt.setInt(2, buildNumber); int count = stmt.executeUpdate(); LOGGER.log(Level.FINE, "deleteJob({0}#{1}): {2}", new Object[]{jobFullName, buildNumber, count}); } cnn.commit(); } catch (SQLException e) { throw new RuntimeSqlException(e); } } @Override public void cleanup() { try (Connection cnn = ds.getConnection()) { cnn.setAutoCommit(false); String sql = "DELETE FROM MAVEN_ARTIFACT WHERE ID NOT IN (SELECT DISTINCT ARTIFACT_ID FROM MAVEN_DEPENDENCY UNION SELECT DISTINCT ARTIFACT_ID FROM GENERATED_MAVEN_ARTIFACT)"; try (Statement stmt = cnn.createStatement()) { int count = stmt.executeUpdate(sql); LOGGER.log(Level.FINE, "cleanup(): {0}", new Object[]{count}); } cnn.commit(); } catch (SQLException e) { throw new RuntimeSqlException(e); } } protected long getOrCreateBuildPrimaryKey(String jobFullName, int buildNumber) { try (Connection cnn = ds.getConnection()) { cnn.setAutoCommit(false); Long jobPrimaryKey = null; try (PreparedStatement stmt = cnn.prepareStatement("SELECT ID FROM JENKINS_JOB WHERE FULL_NAME = ? AND JENKINS_MASTER_ID = ?")) { stmt.setString(1, jobFullName); stmt.setLong(2, getJenkinsMasterPrimaryKey(cnn)); try (ResultSet rst = stmt.executeQuery()) { if (rst.next()) { jobPrimaryKey = rst.getLong(1); } } } if (jobPrimaryKey == null) { try (PreparedStatement stmt = cnn.prepareStatement("INSERT INTO JENKINS_JOB(FULL_NAME, JENKINS_MASTER_ID) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS)) { stmt.setString(1, jobFullName); stmt.setLong(2, getJenkinsMasterPrimaryKey(cnn)); stmt.execute(); jobPrimaryKey = getGeneratedPrimaryKey(stmt, "ID"); } } Long buildPrimaryKey = null; try (PreparedStatement stmt = cnn.prepareStatement("SELECT ID FROM JENKINS_BUILD WHERE JOB_ID=? AND NUMBER=?")) { stmt.setLong(1, jobPrimaryKey); stmt.setInt(2, buildNumber); try (ResultSet rst = stmt.executeQuery()) { if (rst.next()) { buildPrimaryKey = rst.getLong(1); } } } if (buildPrimaryKey == null) { try (PreparedStatement stmt = cnn.prepareStatement("INSERT INTO JENKINS_BUILD(JOB_ID, NUMBER) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS)) { stmt.setLong(1, jobPrimaryKey); stmt.setInt(2, buildNumber); stmt.execute(); buildPrimaryKey = getGeneratedPrimaryKey(stmt, "ID"); } } cnn.commit(); return buildPrimaryKey; } catch (SQLException e) { throw new RuntimeSqlException(e); } } protected Long getGeneratedPrimaryKey(PreparedStatement stmt, String column) throws SQLException { Long jobPrimaryKey; try (ResultSet rst = stmt.getGeneratedKeys()) { if (rst.next()) { jobPrimaryKey = rst.getLong(1); } else { throw new IllegalStateException(); } } return jobPrimaryKey; } protected long getOrCreateArtifactPrimaryKey(@Nonnull String groupId, @Nonnull String artifactId, @Nonnull String version, @Nonnull String type, @Nullable String classifier) { try (Connection cnn = ds.getConnection()) { cnn.setAutoCommit(false); // get or create build record Long artifactPrimaryKey = null; if (classifier == null) { // For an unknown reason, "where classifier = null" does not work as expected when "where classifier is null" does try (PreparedStatement stmt = cnn.prepareStatement("SELECT ID FROM MAVEN_ARTIFACT WHERE GROUP_ID = ? AND ARTIFACT_ID = ? AND VERSION = ? AND TYPE = ? AND CLASSIFIER is NULL")) { stmt.setString(1, groupId); stmt.setString(2, artifactId); stmt.setString(3, version); stmt.setString(4, type); try (ResultSet rst = stmt.executeQuery()) { if (rst.next()) { artifactPrimaryKey = rst.getLong(1); } } } } else { try (PreparedStatement stmt = cnn.prepareStatement("SELECT ID FROM MAVEN_ARTIFACT WHERE GROUP_ID = ? AND ARTIFACT_ID = ? AND VERSION = ? AND TYPE = ? AND CLASSIFIER = ?")) { stmt.setString(1, groupId); stmt.setString(2, artifactId); stmt.setString(3, version); stmt.setString(4, type); stmt.setString(5, classifier); try (ResultSet rst = stmt.executeQuery()) { if (rst.next()) { artifactPrimaryKey = rst.getLong(1); } } } } if (artifactPrimaryKey == null) { try (PreparedStatement stmt = cnn.prepareStatement("INSERT INTO MAVEN_ARTIFACT(GROUP_ID, ARTIFACT_ID, VERSION, TYPE, CLASSIFIER) VALUES (?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS)) { stmt.setString(1, groupId); stmt.setString(2, artifactId); stmt.setString(3, version); stmt.setString(4, type); stmt.setString(5, classifier); stmt.execute(); artifactPrimaryKey = getGeneratedPrimaryKey(stmt, "ID"); } } cnn.commit(); return artifactPrimaryKey; } catch (SQLException e) { throw new RuntimeSqlException(e); } } protected synchronized void initializeDatabase() { try (Connection cnn = ds.getConnection()) { cnn.setAutoCommit(false); int initialSchemaVersion = getSchemaVersion(cnn); LOGGER.log(Level.FINE, "Initialise database. Current schema version: {0}", new Object[]{initialSchemaVersion}); NumberFormat numberFormat = new DecimalFormat("00"); int idx = initialSchemaVersion; while (true) { idx++; String sqlScriptPath = "sql/" + getJdbcScheme() + "/" + numberFormat.format(idx) + "_migration.sql"; InputStream sqlScriptInputStream = ClassUtils.getResourceAsStream(sqlScriptPath); if (sqlScriptInputStream == null) { break; } else { String sqlScript; try { sqlScript = IOUtils.toString(sqlScriptInputStream); } catch (IOException e) { throw new RuntimeIoException("Exception reading " + sqlScriptPath, e); } LOGGER.log(Level.FINE, "Execute database migration script {0}", sqlScriptPath); for (String sqlCommand : sqlScript.split(";")) { sqlCommand = sqlCommand.trim(); if (sqlCommand.isEmpty()) { // ignore empty query } else { try (Statement stmt = cnn.createStatement()) { LOGGER.log(Level.FINER, "Execute command {0}", sqlCommand); stmt.execute(sqlCommand); } catch (SQLException e) { handleDatabaseInitialisationException(e); } } } String className = "org.jenkinsci.plugins.pipeline.maven.db.migration." + getJdbcScheme() + ".MigrationStep" + idx; try { MigrationStep migrationStep = (MigrationStep) Class.forName(className).newInstance(); LOGGER.log(Level.FINE, "Execute database migration step {0}", migrationStep.getClass().getName()); migrationStep.execute(cnn, getJenkinsDetails()); } catch (ClassNotFoundException e) { // no migration class found, just a migration script LOGGER.log(Level.FINER, "Migration step {0} not found", new Object[]{className}); } catch (Exception e) { cnn.rollback(); throw new RuntimeException(e); } } cnn.commit(); } int newSchemaVersion = getSchemaVersion(cnn); if (newSchemaVersion == 0) { // https://issues.jenkins-ci.org/browse/JENKINS-46577 throw new IllegalStateException("Failure to load database DDL files. " + "Files 'sql/" + getJdbcScheme() + "/xxx_migration.sql' NOT found in the Thread Context Class Loader. " + " Pipeline Maven Plugin may be installed in an unsupported manner " + "(thread.contextClassLoader: " + Thread.currentThread().getContextClassLoader() + ", " + "classLoader: " + ClassUtils.class.getClassLoader() + ")"); } else if (newSchemaVersion == initialSchemaVersion) { // no migration was needed } else { LOGGER.log(Level.INFO, "Database successfully migrated from version {0} to version {1}", new Object[]{initialSchemaVersion, newSchemaVersion}); } } catch (SQLException e) { throw new RuntimeSqlException(e); } } protected void handleDatabaseInitialisationException(SQLException e) { throw new RuntimeSqlException(e); } /** * * @return "h2", "mysql"... */ public abstract String getJdbcScheme(); protected int getSchemaVersion(Connection cnn) throws SQLException { int schemaVersion; try (Statement stmt = cnn.createStatement()) { try (ResultSet rst = stmt.executeQuery("SELECT * FROM VERSION")) { if (rst.next()) { schemaVersion = rst.getInt(1); } else { schemaVersion = 0; } } catch (SQLException e) { if (e.getErrorCode() == ErrorCode.TABLE_OR_VIEW_NOT_FOUND_1) { // H2 TABLE_OR_VIEW_NOT_FOUND_1 schemaVersion = 0; } else if (e.getErrorCode() == 1146) { // MySQL TABLE_OR_VIEW_NOT_FOUND_1 schemaVersion = 0; } else if ("42P01".equals(e.getSQLState())) { // ignore PostgreSQL "ERROR: relation "..." does not exist schemaVersion = 0; cnn.rollback(); // postgresql requires to rollback after a 42P01 error } else { throw new RuntimeSqlException(e); } } } return schemaVersion; } /** * Basic tests to ensure that the database is not corrupted */ protected synchronized void testDatabase() throws RuntimeSqlException { try (Connection cnn = ds.getConnection()) { List<String> tables = Arrays.asList("MAVEN_ARTIFACT", "JENKINS_JOB", "JENKINS_BUILD", "MAVEN_DEPENDENCY", "GENERATED_MAVEN_ARTIFACT", "MAVEN_PARENT_PROJECT"); for (String table : tables) { try (Statement stmt = cnn.createStatement()) { try (ResultSet rst = stmt.executeQuery("SELECT count(*) FROM " + table)) { if (rst.next()) { int count = rst.getInt(1); LOGGER.log(Level.FINE, "Table {0}: {1} rows", new Object[]{table, count}); } else { throw new IllegalStateException("Exception testing table '" + table + "'"); } } } catch (SQLException e) { throw new RuntimeSqlException("Exception testing table '" + table + "' on " + cnn.toString(), e); } } } catch (SQLException e) { throw new RuntimeSqlException(e); } } @Nonnull @Override @Deprecated public List<String> listDownstreamJobs(@Nonnull String jobFullName, int buildNumber) { List<String> downstreamJobs = listDownstreamPipelinesBasedOnMavenDependencies(jobFullName, buildNumber); downstreamJobs.addAll(listDownstreamPipelinesBasedOnParentProjectDependencies(jobFullName, buildNumber)); // JENKINS-50507 Don't return the passed job in case of pipelines consuming the artifacts they produce downstreamJobs.remove(jobFullName); return downstreamJobs; } @Nonnull @Override public Map<MavenArtifact, SortedSet<String>> listDownstreamJobsByArtifact(@Nonnull String jobFullName, int buildNumber) { Map<MavenArtifact, SortedSet<String>> downstreamJobsByArtifactBasedOnMavenDependencies = listDownstreamJobsByArtifactBasedOnMavenDependencies(jobFullName, buildNumber); LOGGER.log(Level.FINER, "Got downstreamJobsByArtifactBasedOnMavenDependencies for job named {0} and build #{1}: {2}", new Object[]{jobFullName, buildNumber, downstreamJobsByArtifactBasedOnMavenDependencies}); Map<MavenArtifact, SortedSet<String>> downstreamJobsByArtifactBasedOnParentProjectDependencies = listDownstreamJobsByArtifactBasedOnParentProjectDependencies(jobFullName, buildNumber); LOGGER.log(Level.FINER, "Got downstreamJobsByArtifactBasedOnParentProjectDependencies for job named {0} and build #{1}: {2}", new Object[]{jobFullName, buildNumber, downstreamJobsByArtifactBasedOnParentProjectDependencies}); Map<MavenArtifact, SortedSet<String>> results = new HashMap<>(); results.putAll(downstreamJobsByArtifactBasedOnMavenDependencies); for(Entry<MavenArtifact, SortedSet<String>> entry: downstreamJobsByArtifactBasedOnParentProjectDependencies.entrySet()) { MavenArtifact mavenArtifact = entry.getKey(); if (results.containsKey(mavenArtifact)) { results.get(mavenArtifact).addAll(entry.getValue()); } else { results.put(mavenArtifact, new TreeSet<>(entry.getValue())); } } LOGGER.log(Level.FINER, "Got results for job named {0} and build #{1}: {2}", new Object[]{jobFullName, buildNumber, results}); // JENKINS-50507 Don't return the passed job in case of pipelines consuming the artifacts they produce for (Iterator<Entry<MavenArtifact, SortedSet<String>>> it = results.entrySet().iterator(); it.hasNext();) { Entry<MavenArtifact, SortedSet<String>> entry = it.next(); MavenArtifact mavenArtifact = entry.getKey(); SortedSet<String> jobs = entry.getValue(); boolean removed = jobs.remove(jobFullName); if (removed) { LOGGER.log(Level.FINER, "Remove {0} from downstreamJobs of artifact {1}", new Object[]{jobFullName, mavenArtifact}); if (jobs.isEmpty()) { it.remove(); } } } return results; } @Nonnull @Override public SortedSet<String> listDownstreamJobs(@Nonnull String groupId, @Nonnull String artifactId, @Nonnull String version, @Nullable String baseVersion, @Nonnull String type, @Nullable String classifier) { return listDownstreamPipelinesBasedOnMavenDependencies(groupId, artifactId, (baseVersion == null ? version : baseVersion), type, classifier); } protected SortedSet<String> listDownstreamPipelinesBasedOnMavenDependencies(@Nonnull String groupId, @Nonnull String artifactId, @Nonnull String version, @Nonnull String type, @Nullable String classifier) { LOGGER.log(Level.FINER, "listDownstreamPipelinesBasedOnMavenDependencies({0}:{1}:{2}:{3}:{4})", new Object[]{groupId, artifactId, version, type, classifier}); String sql = "select distinct downstream_job.full_name \n" + "from MAVEN_ARTIFACT \n" + "inner join MAVEN_DEPENDENCY on (MAVEN_DEPENDENCY.artifact_id = MAVEN_ARTIFACT.id and MAVEN_DEPENDENCY.ignore_upstream_triggers = false) \n" + "inner join JENKINS_BUILD as downstream_build on MAVEN_DEPENDENCY.build_id = downstream_build.id \n" + "inner join JENKINS_JOB as downstream_job on (downstream_build.number = downstream_job.last_successful_build_number and downstream_build.job_id = downstream_job.id) \n" + "where MAVEN_ARTIFACT.group_id = ? and MAVEN_ARTIFACT.artifact_id = ? and MAVEN_ARTIFACT.version = ? \n" + "and (MAVEN_ARTIFACT.type = ? or MAVEN_ARTIFACT.classifier IS NULL or MAVEN_ARTIFACT.classifier = ?) \n" + "and downstream_job.jenkins_master_id = ?"; SortedSet<String> downstreamJobsFullNames = new TreeSet<>(); try (Connection cnn = ds.getConnection()) { try (PreparedStatement stmt = cnn.prepareStatement(sql)) { stmt.setString(1, groupId); stmt.setString(2, artifactId); stmt.setString(3, version); stmt.setString(4, type); stmt.setString(5, classifier); stmt.setLong(6, getJenkinsMasterPrimaryKey(cnn)); try (ResultSet rst = stmt.executeQuery()) { while (rst.next()) { downstreamJobsFullNames.add(rst.getString(1)); } } } } catch (SQLException e) { throw new RuntimeSqlException(e); } LOGGER.log(Level.FINER, "listDownstreamPipelinesBasedOnMavenDependencies({0}:{1}:{2}:{3}): {4}", new Object[]{groupId, artifactId, version, type, downstreamJobsFullNames}); return downstreamJobsFullNames; } @Deprecated protected List<String> listDownstreamPipelinesBasedOnMavenDependencies(@Nonnull String jobFullName, int buildNumber) { LOGGER.log(Level.FINER, "listDownstreamJobs({0}, {1})", new Object[]{jobFullName, buildNumber}); String sql = "select distinct downstream_job.full_name \n" + "from JENKINS_JOB as upstream_job \n" + "inner join JENKINS_BUILD as upstream_build on upstream_job.id = upstream_build.job_id \n" + "inner join GENERATED_MAVEN_ARTIFACT on (upstream_build.id = GENERATED_MAVEN_ARTIFACT.build_id and GENERATED_MAVEN_ARTIFACT.skip_downstream_triggers = false) \n" + "inner join MAVEN_ARTIFACT on GENERATED_MAVEN_ARTIFACT.artifact_id = MAVEN_ARTIFACT.id \n" + "inner join MAVEN_DEPENDENCY on (MAVEN_DEPENDENCY.artifact_id = MAVEN_ARTIFACT.id and MAVEN_DEPENDENCY.ignore_upstream_triggers = false) \n" + "inner join JENKINS_BUILD as downstream_build on MAVEN_DEPENDENCY.build_id = downstream_build.id \n" + "inner join JENKINS_JOB as downstream_job on (downstream_build.number = downstream_job.last_successful_build_number and downstream_build.job_id = downstream_job.id) \n" + "where upstream_job.full_name = ? and upstream_job.jenkins_master_id = ? and upstream_build.number = ? and downstream_job.jenkins_master_id = ?"; List<String> downstreamJobsFullNames = new ArrayList<>(); LOGGER.log(Level.FINER, "sql: {0}, jobFullName:{1}, buildNumber: {2}", new Object[]{sql, jobFullName, buildNumber}); try (Connection cnn = ds.getConnection()) { try (PreparedStatement stmt = cnn.prepareStatement(sql)) { stmt.setString(1, jobFullName); stmt.setLong(2, getJenkinsMasterPrimaryKey(cnn)); stmt.setInt(3, buildNumber); stmt.setLong(4, getJenkinsMasterPrimaryKey(cnn)); try (ResultSet rst = stmt.executeQuery()) { while (rst.next()) { downstreamJobsFullNames.add(rst.getString(1)); } } } } catch (SQLException e) { throw new RuntimeSqlException(e); } LOGGER.log(Level.FINE, "listDownstreamJobs({0}, {1}): {2}", new Object[]{jobFullName, buildNumber, downstreamJobsFullNames}); return downstreamJobsFullNames; } protected Map<MavenArtifact, SortedSet<String>> listDownstreamJobsByArtifactBasedOnMavenDependencies(@Nonnull String jobFullName, int buildNumber) { LOGGER.log(Level.FINER, "listDownstreamJobsByArtifactBasedOnMavenDependencies({0}, {1})", new Object[]{jobFullName, buildNumber}); String sql = "select distinct downstream_job.full_name, \n " + " dependency_artefact.group_id, dependency_artefact.artifact_id, dependency_artefact.version as base_version, dependency_artefact.type, dependency_artefact.classifier, \n" + " GENERATED_MAVEN_ARTIFACT.version as version, GENERATED_MAVEN_ARTIFACT.extension \n" + "from JENKINS_JOB as upstream_job \n" + "inner join JENKINS_BUILD as upstream_build on upstream_job.id = upstream_build.job_id \n" + "inner join GENERATED_MAVEN_ARTIFACT on (upstream_build.id = GENERATED_MAVEN_ARTIFACT.build_id and GENERATED_MAVEN_ARTIFACT.skip_downstream_triggers = false) \n" + "inner join MAVEN_ARTIFACT as generated_artefact on GENERATED_MAVEN_ARTIFACT.artifact_id = generated_artefact.id \n" + "inner join MAVEN_ARTIFACT as dependency_artefact on generated_artefact.group_id = dependency_artefact.group_id \n" + " and generated_artefact.artifact_id = dependency_artefact.artifact_id and generated_artefact.version = dependency_artefact.version \n" + " and (\n" + " generated_artefact.type = dependency_artefact.type\n" + " or generated_artefact.classifier is null\n" + " or dependency_artefact.classifier is null\n" + " or generated_artefact.classifier = dependency_artefact.classifier\n" + " )\n" + "inner join MAVEN_DEPENDENCY on (MAVEN_DEPENDENCY.artifact_id = dependency_artefact.id and MAVEN_DEPENDENCY.ignore_upstream_triggers = false) \n" + "inner join JENKINS_BUILD as downstream_build on MAVEN_DEPENDENCY.build_id = downstream_build.id \n" + "inner join JENKINS_JOB as downstream_job on (downstream_build.number = downstream_job.last_successful_build_number and downstream_build.job_id = downstream_job.id) \n" + "where upstream_job.full_name = ? and upstream_job.jenkins_master_id = ? and upstream_build.number = ? and downstream_job.jenkins_master_id = ?"; LOGGER.log(Level.FINER, "sql: {0}, jobFullName:{1}, buildNumber: {2}", new Object[]{sql, jobFullName, buildNumber}); Map<MavenArtifact, SortedSet<String>> results = new HashMap<>(); try (Connection cnn = ds.getConnection()) { try (PreparedStatement stmt = cnn.prepareStatement(sql)) { stmt.setString(1, jobFullName); stmt.setLong(2, getJenkinsMasterPrimaryKey(cnn)); stmt.setInt(3, buildNumber); stmt.setLong(4, getJenkinsMasterPrimaryKey(cnn)); try (ResultSet rst = stmt.executeQuery()) { while (rst.next()) { MavenArtifact artifact = new MavenArtifact(); artifact.setGroupId(rst.getString("group_id")); artifact.setArtifactId(rst.getString("artifact_id")); artifact.setVersion(rst.getString("version")); artifact.setBaseVersion(rst.getString("base_version")); artifact.setType(rst.getString("type")); artifact.setClassifier(rst.getString("classifier")); artifact.setExtension(rst.getString("extension")); String downstreamJobFullName = rst.getString("full_name"); results.computeIfAbsent(artifact, a -> new TreeSet<>()).add(downstreamJobFullName); } } } } catch (SQLException e) { throw new RuntimeSqlException(e); } LOGGER.log(Level.FINE, "listDownstreamJobsByArtifactBasedOnMavenDependencies({0}, {1}): {2}", new Object[]{jobFullName, buildNumber, results}); return results; } @Deprecated protected List<String> listDownstreamPipelinesBasedOnParentProjectDependencies(@Nonnull String jobFullName, int buildNumber) { LOGGER.log(Level.FINER, "listDownstreamPipelinesBasedOnParentProjectDependencies({0}, {1})", new Object[]{jobFullName, buildNumber}); String sql = "select distinct downstream_job.full_name \n" + "from JENKINS_JOB as upstream_job \n" + "inner join JENKINS_BUILD as upstream_build on upstream_job.id = upstream_build.job_id \n" + "inner join GENERATED_MAVEN_ARTIFACT on (upstream_build.id = GENERATED_MAVEN_ARTIFACT.build_id and GENERATED_MAVEN_ARTIFACT.skip_downstream_triggers = false) \n" + "inner join MAVEN_ARTIFACT on GENERATED_MAVEN_ARTIFACT.artifact_id = MAVEN_ARTIFACT.id \n" + "inner join MAVEN_PARENT_PROJECT on (MAVEN_PARENT_PROJECT.artifact_id = MAVEN_ARTIFACT.id and MAVEN_PARENT_PROJECT.ignore_upstream_triggers = false) \n" + "inner join JENKINS_BUILD as downstream_build on MAVEN_PARENT_PROJECT.build_id = downstream_build.id \n" + "inner join JENKINS_JOB as downstream_job on (downstream_build.number = downstream_job.last_successful_build_number and downstream_build.job_id = downstream_job.id) \n" + "where upstream_job.full_name = ? and upstream_job.jenkins_master_id = ? and upstream_build.number = ? and downstream_job.jenkins_master_id = ?"; List<String> downstreamJobsFullNames = new ArrayList<>(); LOGGER.log(Level.FINER, "sql: {0}, jobFullName:{1}, buildNumber: {2}", new Object[]{sql, jobFullName, buildNumber}); try (Connection cnn = ds.getConnection()) { try (PreparedStatement stmt = cnn.prepareStatement(sql)) { stmt.setString(1, jobFullName); stmt.setLong(2, getJenkinsMasterPrimaryKey(cnn)); stmt.setInt(3, buildNumber); stmt.setLong(4, getJenkinsMasterPrimaryKey(cnn)); try (ResultSet rst = stmt.executeQuery()) { while (rst.next()) { downstreamJobsFullNames.add(rst.getString(1)); } } } } catch (SQLException e) { throw new RuntimeSqlException(e); } LOGGER.log(Level.FINE, "listDownstreamPipelinesBasedOnParentProjectDependencies({0}, {1}): {2}", new Object[]{jobFullName, buildNumber, downstreamJobsFullNames}); return downstreamJobsFullNames; } protected Map<MavenArtifact, SortedSet<String>> listDownstreamJobsByArtifactBasedOnParentProjectDependencies(String jobFullName, int buildNumber) { LOGGER.log(Level.FINER, "listDownstreamPipelinesBasedOnParentProjectDependencies({0}, {1})", new Object[]{jobFullName, buildNumber}); String sql = "select distinct downstream_job.full_name, \n" + " MAVEN_ARTIFACT.group_id, MAVEN_ARTIFACT.artifact_id, MAVEN_ARTIFACT.version as base_version, MAVEN_ARTIFACT.type, MAVEN_ARTIFACT.classifier, \n" + " GENERATED_MAVEN_ARTIFACT.version as version, GENERATED_MAVEN_ARTIFACT.extension \n" + "from JENKINS_JOB as upstream_job \n" + "inner join JENKINS_BUILD as upstream_build on upstream_job.id = upstream_build.job_id \n" + "inner join GENERATED_MAVEN_ARTIFACT on (upstream_build.id = GENERATED_MAVEN_ARTIFACT.build_id and GENERATED_MAVEN_ARTIFACT.skip_downstream_triggers = false) \n" + "inner join MAVEN_ARTIFACT on GENERATED_MAVEN_ARTIFACT.artifact_id = MAVEN_ARTIFACT.id \n" + "inner join MAVEN_PARENT_PROJECT on (MAVEN_PARENT_PROJECT.artifact_id = MAVEN_ARTIFACT.id and MAVEN_PARENT_PROJECT.ignore_upstream_triggers = false) \n" + "inner join JENKINS_BUILD as downstream_build on MAVEN_PARENT_PROJECT.build_id = downstream_build.id \n" + "inner join JENKINS_JOB as downstream_job on (downstream_build.number = downstream_job.last_successful_build_number and downstream_build.job_id = downstream_job.id) \n" + "where upstream_job.full_name = ? and upstream_job.jenkins_master_id = ? and upstream_build.number = ? and downstream_job.jenkins_master_id = ?"; LOGGER.log(Level.FINER, "sql: {0}, jobFullName:{1}, buildNumber: {2}", new Object[]{sql, jobFullName, buildNumber}); Map<MavenArtifact, SortedSet<String>> results = new HashMap<>(); try (Connection cnn = ds.getConnection()) { try (PreparedStatement stmt = cnn.prepareStatement(sql)) { stmt.setString(1, jobFullName); stmt.setLong(2, getJenkinsMasterPrimaryKey(cnn)); stmt.setInt(3, buildNumber); stmt.setLong(4, getJenkinsMasterPrimaryKey(cnn)); try (ResultSet rst = stmt.executeQuery()) { while (rst.next()) { MavenArtifact artifact = new MavenArtifact(); artifact.setGroupId(rst.getString("group_id")); artifact.setArtifactId(rst.getString("artifact_id")); artifact.setVersion(rst.getString("version")); artifact.setBaseVersion(rst.getString("base_version")); artifact.setType(rst.getString("type")); artifact.setClassifier(rst.getString("classifier")); artifact.setExtension(rst.getString("extension")); String downstreamJobFullName = rst.getString("full_name"); results.computeIfAbsent(artifact, a -> new TreeSet<>()).add(downstreamJobFullName); } } } } catch (SQLException e) { throw new RuntimeSqlException(e); } LOGGER.log(Level.FINE, "listDownstreamJobsByArtifactBasedOnParentProjectDependencies({0}, {1}): {2}", new Object[]{jobFullName, buildNumber, results}); return results; } @Nonnull @Override public Map<String, Integer> listUpstreamJobs(@Nonnull String jobFullName, int buildNumber) { Map<String, Integer> upstreamJobs = listUpstreamPipelinesBasedOnMavenDependencies(jobFullName, buildNumber); upstreamJobs.putAll(listUpstreamPipelinesBasedOnParentProjectDependencies(jobFullName, buildNumber)); // JENKINS-50507 Don't return the passed job in case of pipelines consuming the artifacts they produce upstreamJobs.remove(jobFullName); return upstreamJobs; } /** * * @param downstreamJobFullName * @param downstreamBuildNumber * @return */ protected Map<String, Integer> listUpstreamPipelinesBasedOnMavenDependencies(@Nonnull String downstreamJobFullName, int downstreamBuildNumber) { LOGGER.log(Level.FINER, "listUpstreamPipelinesBasedOnMavenDependencies({0}, {1})", new Object[]{downstreamJobFullName, downstreamBuildNumber}); String sql = "select upstream_job.full_name, upstream_build.number\n" + "from JENKINS_JOB as upstream_job\n" + "inner join JENKINS_BUILD as upstream_build on (upstream_job.id = upstream_build.job_id and upstream_job.last_successful_build_number = upstream_build.number)\n" + "inner join GENERATED_MAVEN_ARTIFACT on (upstream_build.id = GENERATED_MAVEN_ARTIFACT.build_id and GENERATED_MAVEN_ARTIFACT.skip_downstream_triggers = false)\n" + "inner join MAVEN_ARTIFACT as generated_artefact on GENERATED_MAVEN_ARTIFACT.artifact_id = generated_artefact.id\n" + "inner join MAVEN_ARTIFACT as dependency_artefact on generated_artefact.group_id = dependency_artefact.group_id \n" + " and generated_artefact.artifact_id = dependency_artefact.artifact_id and generated_artefact.version = dependency_artefact.version \n" + " and (\n" + " generated_artefact.type = dependency_artefact.type\n" + " or generated_artefact.classifier is null\n" + " or dependency_artefact.classifier is null\n" + " or generated_artefact.classifier = dependency_artefact.classifier\n" + " )\n" + "inner join MAVEN_DEPENDENCY on (MAVEN_DEPENDENCY.artifact_id = dependency_artefact.id and MAVEN_DEPENDENCY.ignore_upstream_triggers = false)\n" + "inner join JENKINS_BUILD as downstream_build on MAVEN_DEPENDENCY.build_id = downstream_build.id\n" + "inner join JENKINS_JOB as downstream_job on downstream_build.job_id = downstream_job.id\n" + "where downstream_job.full_name = ? and downstream_job.jenkins_master_id = ? and downstream_build.number = ? and upstream_job.jenkins_master_id = ?"; Map<String, Integer> upstreamJobsFullNames = new HashMap<>(); LOGGER.log(Level.FINER, "sql: {0}, jobFullName:{1}, buildNumber: {2}", new Object[]{sql, downstreamJobFullName, downstreamBuildNumber}); try (Connection cnn = ds.getConnection()) { try (PreparedStatement stmt = cnn.prepareStatement(sql)) { stmt.setString(1, downstreamJobFullName); stmt.setLong(2, getJenkinsMasterPrimaryKey(cnn)); stmt.setInt(3, downstreamBuildNumber); stmt.setLong(4, getJenkinsMasterPrimaryKey(cnn)); try (ResultSet rst = stmt.executeQuery()) { while (rst.next()) { upstreamJobsFullNames.put(rst.getString(1), rst.getInt(2)); } } } } catch (SQLException e) { throw new RuntimeSqlException(e); } LOGGER.log(Level.FINE, "listUpstreamPipelinesBasedOnMavenDependencies({0}, {1}): {2}", new Object[]{downstreamJobFullName, downstreamBuildNumber, upstreamJobsFullNames}); return upstreamJobsFullNames; } protected Map<String, Integer> listUpstreamPipelinesBasedOnParentProjectDependencies(@Nonnull String downstreamJobFullName, int downstreamBuildNumber) { LOGGER.log(Level.FINER, "listUpstreamPipelinesBasedOnParentProjectDependencies({0}, {1})", new Object[]{downstreamJobFullName, downstreamBuildNumber}); String sql = "select upstream_job.full_name, upstream_build.number\n" + "from JENKINS_JOB as upstream_job\n" + "inner join JENKINS_BUILD as upstream_build on (upstream_job.id = upstream_build.job_id and upstream_job.last_successful_build_number = upstream_build.number)\n" + "inner join GENERATED_MAVEN_ARTIFACT on (upstream_build.id = GENERATED_MAVEN_ARTIFACT.build_id and GENERATED_MAVEN_ARTIFACT.skip_downstream_triggers = false)\n" + "inner join MAVEN_ARTIFACT on GENERATED_MAVEN_ARTIFACT.artifact_id = MAVEN_ARTIFACT.id\n" + "inner join MAVEN_PARENT_PROJECT on (MAVEN_PARENT_PROJECT.artifact_id = MAVEN_ARTIFACT.id and MAVEN_PARENT_PROJECT.ignore_upstream_triggers = false)\n" + "inner join JENKINS_BUILD as downstream_build on MAVEN_PARENT_PROJECT.build_id = downstream_build.id\n" + "inner join JENKINS_JOB as downstream_job on downstream_build.job_id = downstream_job.id\n" + "where downstream_job.full_name = ? and downstream_job.jenkins_master_id = ? and downstream_build.number = ? and upstream_job.jenkins_master_id = ?"; Map<String, Integer> upstreamJobsFullNames = new HashMap<>(); LOGGER.log(Level.FINER, "sql: {0}, jobFullName:{1}, buildNumber: {2}", new Object[]{sql, downstreamJobFullName, downstreamBuildNumber}); try (Connection cnn = ds.getConnection()) { try (PreparedStatement stmt = cnn.prepareStatement(sql)) { stmt.setString(1, downstreamJobFullName); stmt.setLong(2, getJenkinsMasterPrimaryKey(cnn)); stmt.setInt(3, downstreamBuildNumber); stmt.setLong(4, getJenkinsMasterPrimaryKey(cnn)); try (ResultSet rst = stmt.executeQuery()) { while (rst.next()) { upstreamJobsFullNames.put(rst.getString(1), rst.getInt(2)); } } } } catch (SQLException e) { throw new RuntimeSqlException(e); } LOGGER.log(Level.FINE, "listUpstreamPipelinesBasedOnParentProjectDependencies({0}, {1}): {2}", new Object[]{downstreamJobFullName, downstreamBuildNumber, upstreamJobsFullNames}); return upstreamJobsFullNames; } @Nonnull public Map<String, Integer> listTransitiveUpstreamJobs(@Nonnull String jobFullName, int buildNumber) { return listTransitiveUpstreamJobs(jobFullName, buildNumber, new HashMap<>(), 0); } private Map<String, Integer> listTransitiveUpstreamJobs(@Nonnull String jobFullName, int buildNumber, Map<String, Integer> transitiveUpstreamBuilds, int recursionDepth) { Map<String, Integer> upstreamBuilds = listUpstreamJobs(jobFullName, buildNumber); for (Entry<String, Integer> upstreamBuild : upstreamBuilds.entrySet()) { String upstreamJobFullName = upstreamBuild.getKey(); Integer upstreamBuildNumber = upstreamBuild.getValue(); if (transitiveUpstreamBuilds.containsKey(upstreamJobFullName)) { // job has already been visited, skip } else { transitiveUpstreamBuilds.put(upstreamJobFullName, upstreamBuildNumber); if (recursionDepth < OPTIMIZATION_MAX_RECURSION_DEPTH) { listTransitiveUpstreamJobs(upstreamJobFullName, upstreamBuildNumber, transitiveUpstreamBuilds, recursionDepth++); } } } return transitiveUpstreamBuilds; } /** * List the artifacts generated by the given build * * @param jobFullName see {@link Item#getFullName()} * @param buildNumber see {@link Run#getNumber()} * @return list of artifact details stored as maps ("gav", "type", "skip_downstream_triggers") */ @Nonnull public List<MavenArtifact> getGeneratedArtifacts(@Nonnull String jobFullName, @Nonnull int buildNumber) { LOGGER.log(Level.FINER, "getGeneratedArtifacts({0}, {1})", new Object[]{jobFullName, buildNumber}); String generatedArtifactsSql = "SELECT DISTINCT MAVEN_ARTIFACT.group_id, MAVEN_ARTIFACT.artifact_id, MAVEN_ARTIFACT.type, MAVEN_ARTIFACT.classifier, MAVEN_ARTIFACT.version as base_version, " + "GENERATED_MAVEN_ARTIFACT.version as version, GENERATED_MAVEN_ARTIFACT.repository_url, GENERATED_MAVEN_ARTIFACT.extension" + " FROM MAVEN_ARTIFACT " + " INNER JOIN GENERATED_MAVEN_ARTIFACT ON MAVEN_ARTIFACT.ID = GENERATED_MAVEN_ARTIFACT.ARTIFACT_ID" + " INNER JOIN JENKINS_BUILD AS UPSTREAM_BUILD ON GENERATED_MAVEN_ARTIFACT.BUILD_ID = UPSTREAM_BUILD.ID " + " INNER JOIN JENKINS_JOB AS UPSTREAM_JOB ON UPSTREAM_BUILD.JOB_ID = UPSTREAM_JOB.ID " + " WHERE " + " UPSTREAM_JOB.FULL_NAME = ? AND" + " UPSTREAM_JOB.JENKINS_MASTER_ID = ? AND" + " UPSTREAM_BUILD.NUMBER = ? "; List<MavenArtifact> results = new ArrayList<>(); try (Connection cnn = this.ds.getConnection()) { try (PreparedStatement stmt = cnn.prepareStatement(generatedArtifactsSql)) { stmt.setString(1, jobFullName); stmt.setLong(2, getJenkinsMasterPrimaryKey(cnn)); stmt.setInt(3, buildNumber); try (ResultSet rst = stmt.executeQuery()) { while (rst.next()) { MavenArtifact artifact = new MavenArtifact(); artifact.setGroupId(rst.getString("group_id")); artifact.setArtifactId(rst.getString("artifact_id")); artifact.setBaseVersion(rst.getString("base_version")); artifact.setType(rst.getString("type")); artifact.setClassifier(rst.getString("classifier")); String version = rst.getString("version"); if (version == null || version.isEmpty()) { version = rst.getString("base_version"); } artifact.setVersion(version); artifact.setRepositoryUrl(rst.getString("repository_url")); artifact.setExtension(rst.getString("extension")); artifact.setSnapshot(artifact.getVersion().endsWith("-SNAPSHOT")); // artifact.put("skip_downstream_triggers", rst.getString("GENERATED_MAVEN_ARTIFACT.skip_downstream_triggers")); results.add(artifact); } } } } catch (SQLException e) { throw new RuntimeSqlException(e); } Collections.sort(results); return results; } @Nonnull public synchronized Long getJenkinsMasterPrimaryKey(Connection cnn) throws SQLException { if (this.jenkinsMasterPrimaryKey == null) { String jenkinsMasterLegacyInstanceId = getJenkinsDetails().getMasterLegacyInstanceId(); String jenkinsMasterUrl = getJenkinsDetails().getMasterRootUrl(); String jenkinsMasterUrlValueInDb = null; try (PreparedStatement stmt = cnn.prepareStatement("SELECT ID, URL FROM JENKINS_MASTER WHERE LEGACY_INSTANCE_ID=?")) { stmt.setString(1, jenkinsMasterLegacyInstanceId); try (ResultSet rst = stmt.executeQuery()) { if (rst.next()) { this.jenkinsMasterPrimaryKey = rst.getLong("ID"); jenkinsMasterUrlValueInDb = rst.getString("URL"); } } } if (this.jenkinsMasterPrimaryKey == null) { // NOT FOUND IN DB try (PreparedStatement stmt = cnn.prepareStatement("INSERT INTO JENKINS_MASTER(LEGACY_INSTANCE_ID, URL) values (?, ?)", Statement.RETURN_GENERATED_KEYS)) { stmt.setString(1, jenkinsMasterLegacyInstanceId); stmt.setString(2, jenkinsMasterUrl); stmt.execute(); this.jenkinsMasterPrimaryKey = getGeneratedPrimaryKey(stmt, "ID"); } finally { cnn.commit(); } } else { // FOUND IN DB, UPDATE IF NEEDED if (!Objects.equals(jenkinsMasterUrl, jenkinsMasterUrlValueInDb)) { LOGGER.log(Level.INFO, "Update url from \"{0}\" to \"{1}\" for master with legacyId {2}", new Object[]{jenkinsMasterUrlValueInDb, jenkinsMasterUrl, jenkinsMasterLegacyInstanceId}); try (PreparedStatement stmt = cnn.prepareStatement("UPDATE JENKINS_MASTER set URL = ? where ID = ?")) { stmt.setString(1, jenkinsMasterUrl); stmt.setLong(2, this.jenkinsMasterPrimaryKey); int count = stmt.executeUpdate(); if (count != 1) { LOGGER.warning("Updated more/less than 1 JENKINS_MASTER.URL=" + jenkinsMasterUrl + " for ID=" + this.jenkinsMasterPrimaryKey); } } finally { cnn.commit(); } } } } return this.jenkinsMasterPrimaryKey; } /** * for mocking */ protected MigrationStep.JenkinsDetails getJenkinsDetails() { return new MigrationStep.JenkinsDetails(); } @Override public String toPrettyString() { List<String> prettyStrings = new ArrayList<>(); try (Connection cnn = ds.getConnection()) { prettyStrings.add("JDBC URL: " + cnn.getMetaData().getURL()); List<String> tables = Arrays.asList("JENKINS_MASTER", "MAVEN_ARTIFACT", "JENKINS_JOB", "JENKINS_BUILD", "MAVEN_DEPENDENCY", "GENERATED_MAVEN_ARTIFACT", "MAVEN_PARENT_PROJECT", "JENKINS_BUILD_UPSTREAM_CAUSE"); for (String table : tables) { try (Statement stmt = cnn.createStatement()) { try (ResultSet rst = stmt.executeQuery("SELECT count(*) FROM " + table)) { if (rst.next()) { int count = rst.getInt(1); prettyStrings.add("Table " + table + ": " + count + " rows"); } else { prettyStrings.add("Table " + table + ": #IllegalStateException 'select count(*)' didn't return any row#"); } } } catch (SQLException e) { prettyStrings.add("Table " + table + ": " + e); LOGGER.log(Level.WARNING, "SQLException counting rows on " + table, e); } } } catch (SQLException e) { prettyStrings.add("SQLException getting a connection to " + ds + ": " + e); LOGGER.log(Level.WARNING, "SQLException getting a connection to " + ds, e); } StringBuilder result = new StringBuilder(StringUtils.substringAfterLast(getClass().getName(), ".") + " - " + getDatabaseDescription()); for (String prettyString : prettyStrings) { result.append("\r\n\t" + prettyString); } return result.toString(); } protected String getDatabaseDescription() { try (Connection cnn = ds.getConnection()) { DatabaseMetaData metaData = cnn.getMetaData(); return metaData. getDatabaseProductName() + " " + metaData.getDatabaseProductVersion(); } catch (SQLException e) { return "#" + e.toString() + "#"; } } @Override public void updateBuildOnCompletion(@Nonnull String jobFullName, int buildNumber, int buildResultOrdinal, long startTimeInMillis, long durationInMillis) { LOGGER.log(Level.FINE, "updateBuildOnCompletion({0}, {1}, result: {2}, startTime): {3}, duration: {4}", new Object[]{jobFullName, buildNumber, buildResultOrdinal, startTimeInMillis, durationInMillis}); long buildPrimaryKey = getOrCreateBuildPrimaryKey(jobFullName, buildNumber); try (Connection cnn = ds.getConnection()) { cnn.setAutoCommit(false); try (PreparedStatement stmt = cnn.prepareStatement("UPDATE JENKINS_BUILD " + "SET RESULT_ID = ?, START_TIME = ?, DURATION_IN_MILLIS = ? " + "WHERE ID = ?")) { stmt.setInt(1, buildResultOrdinal); stmt.setTimestamp(2, new Timestamp(startTimeInMillis)); stmt.setLong(3, durationInMillis); stmt.setLong(4, buildPrimaryKey); int count = stmt.executeUpdate(); if (count != 1) { LOGGER.log(Level.WARNING, "updateBuildOnCompletion - more/less than 1 JENKINS_BUILD record updated (" + count + ") for " + jobFullName + "#" + buildNumber + ", buildPrimaryKey=" + buildPrimaryKey); } } if (Result.SUCCESS.ordinal == buildResultOrdinal) { try (PreparedStatement stmt = cnn.prepareStatement("UPDATE JENKINS_JOB set LAST_BUILD_NUMBER = ?, LAST_SUCCESSFUL_BUILD_NUMBER = ? where FULL_NAME = ? and JENKINS_MASTER_ID = ?")) { stmt.setInt(1, buildNumber); stmt.setInt(2, buildNumber); stmt.setString(3, jobFullName); stmt.setLong(4, getJenkinsMasterPrimaryKey(cnn)); int count = stmt.executeUpdate(); if (count != 1) { LOGGER.log(Level.WARNING, "updateBuildOnCompletion - more/less than 1 JENKINS_JOB record updated (" + count + ") for " + jobFullName + "#" + buildNumber); } } } else { try (PreparedStatement stmt = cnn.prepareStatement("UPDATE JENKINS_JOB set LAST_BUILD_NUMBER = ? where FULL_NAME = ? and JENKINS_MASTER_ID = ?")) { stmt.setInt(1, buildNumber); stmt.setString(2, jobFullName); stmt.setLong(3, getJenkinsMasterPrimaryKey(cnn)); int count = stmt.executeUpdate(); if (count != 1) { LOGGER.log(Level.WARNING, "updateBuildOnCompletion - more/less than 1 JENKINS_JOB record updated (" + count + ") for " + jobFullName + "#" + buildNumber); } } } cnn.commit(); } catch (SQLException e) { throw new RuntimeSqlException("Exception updating build " + jobFullName + "#" + buildNumber + " with result " + buildResultOrdinal, e); } } @Override @Nonnull public DataSource getDataSource() { return ds; } @Override public void close() throws IOException { if (this.ds instanceof Closeable) { Closeable closeable = (Closeable) this.ds; closeable.close(); } } }