/// <summary>Add a build to the build database.</summary>
/// <param name="pullRequestNumber">The GitHub pull request number.</param>
/// <param name="changeDBPassword">The password</param>
public void AddBuild(int pullRequestNumber, string changeDBPassword)
{
if (changeDBPassword == BuildsClassic.GetValidPassword())
{
using (SqlConnection connection = BuildsClassic.Open())
{
string sql = "INSERT INTO ApsimX (Date, PullRequestID, IssueNumber, IssueTitle, Released, Version) " +
"VALUES (@Date, @PullRequestID, @IssueNumber, @IssueTitle, @Released, @Version)";
PullRequest pull = GitHubUtilities.GetPullRequest(pullRequestNumber, owner, repo);
DateTime date = pull.GetTestDate(owner, repo);
pull.GetIssueDetails(out int issueNumber, out bool released);
string issueTitle = pull.GetIssueTitle(owner, repo);
int nextVersion = Convert.ToInt32(GetNextVersion());
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.Add(new SqlParameter("@Date", date));
command.Parameters.Add(new SqlParameter("@PullRequestID", pullRequestNumber));
command.Parameters.Add(new SqlParameter("@IssueNumber", issueNumber));
command.Parameters.Add(new SqlParameter("@IssueTitle", issueTitle));
command.Parameters.Add(new SqlParameter("@Released", released));
command.Parameters.Add(new SqlParameter("@Version", nextVersion));
command.ExecuteNonQuery();
}
}
}
}
/// <summary>Get the latest build.</summary>
private static Build GetLatestBuild()
{
string sql = "SELECT TOP 1 * FROM ApsimX " +
" WHERE Released=1" +
" ORDER BY Date DESC";
using (SqlConnection connection = BuildsClassic.Open())
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
Build build = new Build();
build.date = (DateTime)reader["Date"];
build.pullRequestID = (int)reader["PullRequestID"];;
build.issueNumber = (int)reader["IssueNumber"];
build.issueTitle = (string)reader["IssueTitle"];
string fileName = GetApsimXInstallerFileName(build.issueNumber, build.pullRequestID);
build.url = $@"https://apsimdev.apsim.info/ApsimXFiles/{fileName}";
return(build);
}
}
}
}
return(null);
}
/// <summary>
/// Gets a URL for a version that resolves the specified issue
/// </summary>
/// <param name="issueNumber">The issue number.</param>
public string GetURLOfVersionForIssue(int issueNumber)
{
List <Upgrade> upgrades = new List <Upgrade>();
DateTime issueResolvedDate = GetIssueResolvedDate(issueNumber);
string sql = "SELECT * FROM ApsimX " +
"WHERE IssueNumber = @IssueNumber";
using (SqlConnection connection = BuildsClassic.Open())
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@IssueNumber", issueNumber);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
int pullRequestID = (int)reader["PullRequestID"];
return($@"https://apsimdev.apsim.info/ApsimXFiles/{GetApsimXInstallerFileName(issueNumber, pullRequestID)}");
}
}
}
}
return(null);
}
/// <summary>
/// Return the date the specified issue was resolved.
/// </summary>
/// <param name="issueNumber">The issue number</param>
/// <returns>The date.</returns>
private DateTime GetIssueResolvedDate(int issueNumber)
{
DateTime resolvedDate = new DateTime(2015, 1, 1);
string sql = "SELECT * FROM ApsimX " +
"WHERE IssueNumber = @IssueNumber " +
"ORDER BY Date DESC";
using (SqlConnection connection = BuildsClassic.Open())
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@IssueNumber", issueNumber);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
resolvedDate = (DateTime)reader["Date"];
}
}
}
}
return(resolvedDate);
}
private List <Upgrade> GetAllUpgrades()
{
using (SqlConnection connection = BuildsClassic.Open())
using (SqlCommand command = new SqlCommand("SELECT * FROM ApsimX ORDER BY Date DESC;", connection))
using (SqlDataReader reader = command.ExecuteReader())
return(GetUpgrades(reader));
}
/// <summary>
/// Get the next version number.
/// </summary>
public uint GetNextVersion()
{
using (SqlConnection connection = BuildsClassic.Open())
{
string sql = "SELECT MAX([Version]) FROM ApsimX";
using (SqlCommand command = new SqlCommand(sql, connection))
// This will throw OverFlowException if last version is < 0.
return(Convert.ToUInt32((int)command.ExecuteScalar()) + 1);
}
}
/// <summary>
/// Gets a list of possible upgrades since the specified issue number.
/// </summary>
/// <param name="issueNumber">The issue number.</param>
/// <returns>The list of possible upgrades.</returns>
public List <Upgrade> GetUpgradesSinceIssue(int issueNumber)
{
List <Upgrade> upgrades = new List <Upgrade>();
DateTime issueResolvedDate = GetIssueResolvedDate(issueNumber);
string sql = "SELECT * FROM ApsimX " +
"WHERE Date >= " + string.Format("'{0:yyyy-MM-ddThh:mm:ss tt}'", issueResolvedDate) +
" ORDER BY Date DESC";
using (SqlConnection connection = BuildsClassic.Open())
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int buildIssueNumber = (int)reader["IssueNumber"];
if (buildIssueNumber > 0 && buildIssueNumber != issueNumber)
{
if (upgrades.Find(u => u.issueNumber == buildIssueNumber) == null)
{
int pullID = (int)reader["PullRequestID"];
DateTime date = (DateTime)reader["Date"];
Upgrade upgrade = new Upgrade();
upgrade.ReleaseDate = (DateTime)reader["Date"];
upgrade.issueNumber = buildIssueNumber;
upgrade.IssueTitle = (string)reader["IssueTitle"];
upgrade.IssueURL = @"https://github.com/APSIMInitiative/ApsimX/issues/" + buildIssueNumber;
upgrade.ReleaseURL = @"http://www.apsim.info/ApsimXFiles/ApsimSetup" + buildIssueNumber + ".exe";
upgrades.Add(upgrade);
}
}
}
}
}
}
return(upgrades);
}
/// <summary>
/// Gets all list of released upgrades since a given date.
/// </summary>
/// <param name="date">The date.</param>
/// <returns>List of possible upgrades.</returns>
private List <Upgrade> GetUpgradesSinceDate(DateTime date)
{
List <Upgrade> upgrades = new List <Upgrade>();
string sql = "SELECT * FROM ApsimX " +
"WHERE Date >= @Date" +
" ORDER BY Date DESC";
using (SqlConnection connection = BuildsClassic.Open())
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@Date", date.ToString("yyyy-MM-ddTHH:mm:ss"));
using (SqlDataReader reader = command.ExecuteReader())
{
return(GetUpgrades(reader));
}
}
}
}
/// <summary>
/// Gets the N most recent upgrades.
/// </summary>
/// <param name="n">Number of upgrades to fetch.</param>
public List <Upgrade> GetLastNUpgrades(int n)
{
using (SqlConnection connection = BuildsClassic.Open())
{
using (SqlCommand command = new SqlCommand("SELECT TOP (@NumRows) * FROM ApsimX WHERE Released = 1 ORDER BY Date DESC;", connection))
{
if (n > 0)
{
command.Parameters.AddWithValue("@NumRows", n);
}
else
{
command.CommandText = "SELECT * FROM ApsimX ORDER BY Date DESC;";
}
using (SqlDataReader reader = command.ExecuteReader())
return(GetUpgrades(reader));
}
}
}
/// <summary>Add a build to the build database.</summary>
/// <param name="pullRequestNumber">The GitHub pull request number.</param>
/// <param name="issueID">The issue ID.</param>
/// <param name="issueTitle">The issue title.</param>
/// <param name="changeDBPassword">The password</param>
public void AddBuild(int pullRequestNumber, int issueID, string issueTitle, bool released, string changeDBPassword)
{
if (changeDBPassword == BuildsClassic.GetValidPassword())
{
using (SqlConnection connection = BuildsClassic.Open())
{
string sql = "UPDATE ApsimX " +
"SET IssueNumber=@IssueNumber, IssueTitle=@IssueTitle, Released=@Released " +
"WHERE PullRequestID=" + pullRequestNumber;
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.Add(new SqlParameter("@IssueNumber", issueID));
command.Parameters.Add(new SqlParameter("@IssueTitle", issueTitle));
command.Parameters.Add(new SqlParameter("@Released", released));
command.ExecuteNonQuery();
}
}
}
}
/// <summary>Mark "failed" jobs</summary>
private static void MarkFailedJobs(SqlConnection Connection)
{
string SQL = "SELECT ID FROM Classic WHERE (Status = 'Fail' OR Status = 'Aborted') AND LinuxStatus = 'Queued' ORDER BY ID";
List <int> ignoredJobs = new List <int>();
using (SqlCommand command = new SqlCommand(SQL, Connection))
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ignoredJobs.Add(Convert.ToInt32(reader[0]));
}
}
BuildsClassic builds = new BuildsClassic();
foreach (int jobID in ignoredJobs)
{
builds.UpdateField(jobID, "linuxStatus", "Ignored", GetValidPassword());
}
}
/// <summary>Add a green build to the build database.</summary>
/// <param name="pullRequestNumber">The GitHub pull request number.</param>
/// <param name="buildTimeStamp">The build time stamp</param>
/// <param name="changeDBPassword">The password</param>
public void AddGreenBuild(int pullRequestNumber, string buildTimeStamp, string changeDBPassword)
{
if (changeDBPassword == BuildsClassic.GetValidPassword())
{
using (SqlConnection connection = BuildsClassic.Open())
{
string sql = "INSERT INTO ApsimX (Date, PullRequestID, IssueNumber, IssueTitle, Released) " +
"VALUES (@Date, @PullRequestID, @IssueNumber, @IssueTitle, @Released)";
DateTime date = DateTime.ParseExact(buildTimeStamp, "yyyy.MM.dd-HH:mm", null);
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.Add(new SqlParameter("@Date", date));
command.Parameters.Add(new SqlParameter("@PullRequestID", pullRequestNumber));
command.Parameters.Add(new SqlParameter("@IssueNumber", string.Empty));
command.Parameters.Add(new SqlParameter("@IssueTitle", string.Empty));
command.Parameters.Add(new SqlParameter("@Released", false));
command.ExecuteNonQuery();
}
}
}
}
/// <summary>
/// Return the date the specified issue was resolved.
/// </summary>
/// <param name="issueNumber">The issue number</param>
/// <returns>The date.</returns>
private DateTime GetIssueResolvedDate(int issueNumber)
{
DateTime resolvedDate = new DateTime(2015, 1, 1);
string sql = "SELECT * FROM ApsimX " +
"WHERE IssueNumber = " + issueNumber;
using (SqlConnection connection = BuildsClassic.Open())
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
resolvedDate = (DateTime)reader["Date"];
}
}
}
}
return(resolvedDate);
}
/// <summary>
/// Gets a URL for a version that resolves the specified issue
/// </summary>
/// <param name="issueNumber">The issue number.</param>
public string GetURLOfVersionForIssue(int issueNumber)
{
List <Upgrade> upgrades = new List <Upgrade>();
DateTime issueResolvedDate = GetIssueResolvedDate(issueNumber);
string sql = "SELECT * FROM ApsimX " +
"WHERE IssueNumber = " + issueNumber;
using (SqlConnection connection = BuildsClassic.Open())
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
return(@"http://www.apsim.info/ApsimXFiles/ApsimSetup" + issueNumber + ".exe");
}
}
}
}
return(null);
}
/// <summary>Mark "failed" jobs</summary>
private static void MarkFailedJobs(SqlConnection Connection)
{
string SQL = "SELECT ID FROM Classic WHERE (Status = 'Fail' OR Status = 'Aborted') AND LinuxStatus = 'Queued' ORDER BY ID";
List<int> ignoredJobs = new List<int>();
using (SqlCommand command = new SqlCommand(SQL, Connection))
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
ignoredJobs.Add(Convert.ToInt32(reader[0]));
}
BuildsClassic builds = new BuildsClassic();
foreach (int jobID in ignoredJobs)
builds.UpdateField(jobID, "linuxStatus", "Ignored", GetValidPassword());
}