package org.jenkinsci.plugins.environmentdashboard; import hudson.Extension; import hudson.model.Item; import hudson.model.TopLevelItem; import hudson.model.Descriptor.FormException; import hudson.model.Hudson; import hudson.model.View; import hudson.model.ViewDescriptor; import hudson.util.FormValidation; import hudson.util.ListBoxModel; import java.io.IOException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; import java.util.Collections; import java.util.HashMap; import javax.servlet.ServletException; import jenkins.model.Jenkins; import net.sf.json.JSONObject; import org.apache.commons.lang.StringUtils; import org.jenkinsci.plugins.environmentdashboard.utils.DBConnection; import org.kohsuke.stapler.DataBoundConstructor; import org.kohsuke.stapler.StaplerRequest; import org.kohsuke.stapler.StaplerResponse; import org.kohsuke.stapler.QueryParameter; import org.kohsuke.stapler.interceptor.RequirePOST; /** * Class to provide build wrapper for Dashboard. * * author vipin * date 15/10/2014 */ public class EnvDashboardView extends View { private String envOrder = null; private String compOrder = null; private String deployHistory = null; @DataBoundConstructor public EnvDashboardView(final String name, final String envOrder, final String compOrder, final String deployHistory) { super(name, Hudson.getInstance()); this.envOrder = envOrder; this.compOrder = compOrder; this.deployHistory = deployHistory; } static { ensureCorrectDBSchema(); } private static void ensureCorrectDBSchema() { String returnComment = ""; Connection conn = null; Statement stat = null; conn = DBConnection.getConnection(); try { assert conn != null; stat = conn.createStatement(); } catch (SQLException e) { System.out.println("E13" + e.getMessage()); } try { stat.execute("ALTER TABLE env_dashboard ADD IF NOT EXISTS packageName VARCHAR(255);"); } catch (SQLException e) { System.out.println( "E14: Could not alter table to add package column to table env_dashboard.\n" + e.getMessage()); } finally { DBConnection.closeConnection(); } return; } @Override protected void submit(final StaplerRequest req) throws IOException, ServletException, FormException { req.bindJSON(this, req.getSubmittedForm()); } @RequirePOST public void doPurgeSubmit(final StaplerRequest req, StaplerResponse res) throws IOException, ServletException, FormException { checkPermission(Jenkins.ADMINISTER); Connection conn = null; Statement stat = null; conn = DBConnection.getConnection(); try { assert conn != null; stat = conn.createStatement(); stat.execute("TRUNCATE TABLE env_dashboard"); } catch (SQLException e) { System.out.println("E15: Could not truncate table env_dashboard.\n" + e.getMessage()); } finally { DBConnection.closeConnection(); } res.forwardToPreviousPage(req); } @Override public Item doCreateItem(StaplerRequest req, StaplerResponse rsp) throws IOException, ServletException { return Hudson.getInstance().doCreateItem(req, rsp); } @Extension public static final class DescriptorImpl extends ViewDescriptor { private String envOrder; private String compOrder; private String deployHistory; /** * descriptor impl constructor This empty constructor is required for * stapler. If you remove this constructor, text name of * "Build Pipeline View" will be not displayed in the "NewView" page */ public DescriptorImpl() { load(); } public static ArrayList<String> getCustomColumns() { Connection conn = null; Statement stat = null; ArrayList<String> columns; columns = new ArrayList<String>(); String queryString = "SELECT DISTINCT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='ENV_DASHBOARD';"; String[] fields = { "envComp", "compName", "envName", "buildstatus", "buildJobUrl", "jobUrl", "buildNum", "created_at", "packageName" }; boolean columnFound = false; try { ResultSet rs = null; conn = DBConnection.getConnection(); try { assert conn != null; stat = conn.createStatement(); } catch (SQLException e) { System.out.println("E3" + e.getMessage()); } try { assert stat != null; rs = stat.executeQuery(queryString); } catch (SQLException e) { System.out.println("E4" + e.getMessage()); } String col = ""; while (rs.next()) { columnFound = false; col = rs.getString("COLUMN_NAME"); for (String presetColumn : fields) { if (col.toLowerCase().equals(presetColumn.toLowerCase())) { columnFound = true; break; } } if (!columnFound) { columns.add(col.toLowerCase()); } } DBConnection.closeConnection(); } catch (SQLException e) { System.out.println("E11" + e.getMessage()); return null; } return columns; } public ListBoxModel doFillColumnItems() { ListBoxModel m = new ListBoxModel(); ArrayList<String> columns = getCustomColumns(); int position = 0; m.add("Select column to remove", ""); for (String col : columns) { m.add(col, col); } return m; } @SuppressWarnings("unused") public FormValidation doDropColumn(@QueryParameter("column") final String column) { Hudson.getInstance().checkPermission(Jenkins.ADMINISTER); Connection conn = null; Statement stat = null; if ("".equals(column)) { return FormValidation.ok(); } String queryString = "ALTER TABLE ENV_DASHBOARD DROP COLUMN " + column + ";"; // Get DB connection conn = DBConnection.getConnection(); try { assert conn != null; stat = conn.createStatement(); } catch (SQLException e) { return FormValidation.error("Failed to create statement."); } try { assert stat != null; stat.execute(queryString); } catch (SQLException e) { DBConnection.closeConnection(); return FormValidation.error("Failed to remove column: " + column + "\nThis column may have already been removed. Refresh to update the list of columns to remove."); } DBConnection.closeConnection(); return FormValidation.ok("Successfully removed column " + column + "."); } /** * get the display name * * @return display name */ @Override public String getDisplayName() { return "Environment Dashboard"; } @Override public boolean configure(StaplerRequest req, JSONObject formData) throws FormException { envOrder = formData.getString("envOrder"); compOrder = formData.getString("compOrder"); deployHistory = formData.getString("deployHistory"); save(); return super.configure(req, formData); } } public ArrayList<String> splitEnvOrder(String envOrder) { ArrayList<String> orderOfEnvs = new ArrayList<String>(); if (!"".equals(envOrder)) { orderOfEnvs = new ArrayList<String>(Arrays.asList(envOrder.split("\\s*,\\s*"))); } return orderOfEnvs; } public ArrayList<String> splitCompOrder(String compOrder) { ArrayList<String> orderOfComps = new ArrayList<String>(); if (!"".equals(compOrder)) { orderOfComps = new ArrayList<String>(Arrays.asList(compOrder.split("\\s*,\\s*"))); } return orderOfComps; } public ResultSet runQuery(String queryString) { Connection conn = null; Statement stat = null; ResultSet rs = null; // Get DB connection conn = DBConnection.getConnection(); try { assert conn != null; stat = conn.createStatement(); } catch (SQLException e) { System.out.println("E3" + e.getMessage()); } try { assert stat != null; rs = stat.executeQuery(queryString); } catch (SQLException e) { System.out.println("E4" + e.getMessage()); } return rs; } public ArrayList<String> getOrderOfEnvs() { ArrayList<String> orderOfEnvs; orderOfEnvs = splitEnvOrder(envOrder); if (orderOfEnvs == null || orderOfEnvs.isEmpty()) { String queryString = "select distinct envname from env_dashboard order by envname;"; try { ResultSet rs = runQuery(queryString); if (rs == null) { return null; } while (rs.next()) { if (orderOfEnvs != null) { orderOfEnvs.add(rs.getString("envName")); } } DBConnection.closeConnection(); } catch (SQLException e) { System.out.println("E6" + e.getMessage()); return null; } } return orderOfEnvs; } public ArrayList<String> getOrderOfComps() { ArrayList<String> orderOfComps; orderOfComps = splitCompOrder(compOrder); if (orderOfComps == null || orderOfComps.isEmpty()) { String queryString = "select distinct compname from env_dashboard order by compname;"; try { ResultSet rs = runQuery(queryString); while (rs.next()) { if (orderOfComps != null) { orderOfComps.add(rs.getString("compName")); } } DBConnection.closeConnection(); } catch (SQLException e) { System.out.println("E8" + e.getMessage()); return null; } } return orderOfComps; } public Integer getLimitDeployHistory() { Integer lastDeploy; if (deployHistory == null || deployHistory.equals("")) { return 10; } else { try { lastDeploy = Integer.parseInt(deployHistory); } catch (NumberFormatException e) { return 10; } } return lastDeploy; } public ArrayList<String> getDeployments(String env, Integer lastDeploy) { if (lastDeploy <= 0) { lastDeploy = 10; } ArrayList<String> deployments; deployments = new ArrayList<String>(); String queryString = "select top " + lastDeploy + " created_at from env_dashboard where envName ='" + env + "' order by created_at desc;"; try { ResultSet rs = runQuery(queryString); while (rs.next()) { deployments.add(rs.getString("created_at")); } DBConnection.closeConnection(); } catch (SQLException e) { System.out.println("E11" + e.getMessage()); return null; } return deployments; } public String anyJobsConfigured() { ArrayList<String> orderOfEnvs; orderOfEnvs = getOrderOfEnvs(); if (orderOfEnvs == null || orderOfEnvs.isEmpty()) { return "NONE"; } else { return "ENVS"; } } public String getNiceTimeStamp(String timeStamp) { return timeStamp.substring(0, 19); } public HashMap getCompDeployed(String env, String time) { HashMap<String, String> deployment; deployment = new HashMap<String, String>(); String[] fields = { "buildstatus", "compName", "buildJobUrl", "jobUrl", "buildNum", "packageName" }; String queryString = "select " + StringUtils.join(fields, ", ").replace(".$", "") + " from env_dashboard where envName = '" + env + "' and created_at = '" + time + "';"; try { ResultSet rs = runQuery(queryString); rs.next(); for (String field : fields) { deployment.put(field, rs.getString(field)); } DBConnection.closeConnection(); } catch (SQLException e) { System.out.println("E10" + e.getMessage()); System.out.println("Error executing: " + queryString); } return deployment; } public ArrayList<String> getCustomDBColumns() { return DescriptorImpl.getCustomColumns(); } public ArrayList<HashMap<String, String>> getDeploymentsByComp(String comp, Integer lastDeploy) { if (lastDeploy <= 0) { lastDeploy = 10; } ArrayList<HashMap<String, String>> deployments; deployments = new ArrayList<HashMap<String, String>>(); HashMap<String, String> hash; String[] fields = { "envName", "buildstatus", "buildJobUrl", "jobUrl", "buildNum", "created_at", "packageName" }; ArrayList<String> allDBFields = getCustomDBColumns(); for (String field : fields) { allDBFields.add(field); } String queryString = "select top " + lastDeploy + " * from env_dashboard where compName='" + comp + "' order by created_at desc;"; try { ResultSet rs = runQuery(queryString); while (rs.next()) { hash = new HashMap<String, String>(); for (String field : allDBFields) { hash.put(field, rs.getString(field)); } deployments.add(hash); } DBConnection.closeConnection(); } catch (SQLException e) { System.out.println("E11" + e.getMessage()); return null; } return deployments; } public ArrayList<HashMap<String, String>> getDeploymentsByCompEnv(String comp, String env, Integer lastDeploy) { if (lastDeploy <= 0) { lastDeploy = 10; } ArrayList<HashMap<String, String>> deployments; deployments = new ArrayList<HashMap<String, String>>(); HashMap<String, String> hash; String[] fields = { "envName", "buildstatus", "buildJobUrl", "jobUrl", "buildNum", "created_at", "packageName" }; ArrayList<String> allDBFields = getCustomDBColumns(); for (String field : fields) { allDBFields.add(field); } String queryString = "select top " + lastDeploy + " " + StringUtils.join(allDBFields, ", ").replace(".$", "") + " from env_dashboard where compName='" + comp + "' and envName='" + env + "' order by created_at desc;"; try { ResultSet rs = runQuery(queryString); while (rs.next()) { hash = new HashMap<String, String>(); for (String field : allDBFields) { hash.put(field, rs.getString(field)); } deployments.add(hash); } DBConnection.closeConnection(); } catch (SQLException e) { System.out.println("E11" + e.getMessage()); return null; } return deployments; } public HashMap getCompLastDeployed(String env, String comp) { HashMap<String, String> deployment; deployment = new HashMap<String, String>(); String[] fields = { "buildstatus", "buildJobUrl", "jobUrl", "buildNum", "created_at", "packageName" }; ArrayList<String> allDBFields = getCustomDBColumns(); for (String field : fields) { allDBFields.add(field); } String queryString = "select top 1 " + StringUtils.join(allDBFields, ", ").replace(".$", "") + " from env_dashboard where envName = '" + env + "' and compName = '" + comp + "' order by created_at desc;"; try { ResultSet rs = runQuery(queryString); rs.next(); for (String field : allDBFields) { deployment.put(field, rs.getString(field)); } DBConnection.closeConnection(); } catch (SQLException e) { if (e.getErrorCode() == 2000) { // We'll assume this comp has never been deployed to this env } } else { System.out.println("E12" + e.getMessage()); System.out.println("Error executing: " + queryString); } } return deployment; } @Override public Collection<TopLevelItem> getItems() { return Collections.EMPTY_LIST; } public String getEnvOrder() { return envOrder; } public void setEnvOrder(final String envOrder) { this.envOrder = envOrder; } public String getCompOrder() { return compOrder; } public void setCompOrder(final String compOrder) { this.compOrder = compOrder; } public String getDeployHistory() { return deployHistory; } public void setDeployHistory(final String deployHistory) { this.deployHistory = deployHistory; } @Override public boolean contains(TopLevelItem topLevelItem) { return false; } @Override public void onJobRenamed(Item item, String s, String s2) { } }