| public static CreateParameter ( IDbCommand cmd, string name, object value ) : void | ||
| cmd | IDbCommand | |
| name | string | |
| value | object | |
| return | void | |
public void ClearWork(int lane_id, int revision_id, int host_id)
{
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = @"
UPDATE
Work SET state = DEFAULT, summary = DEFAULT, starttime = DEFAULT, endtime = DEFAULT, duration = DEFAULT, logfile = DEFAULT, host_id = DEFAULT
WHERE
Work.revisionwork_id =
(SELECT RevisionWork.id
FROM RevisionWork
WHERE RevisionWork.host_id = @host_id AND RevisionWork.lane_id = @lane_id AND RevisionWork.revision_id = @revision_id);
UPDATE
RevisionWork SET state = DEFAULT, lock_expires = DEFAULT, completed = DEFAULT, workhost_id = DEFAULT,
createdtime = DEFAULT, assignedtime = DEFAULT, startedtime = DEFAULT, endtime = DEFAULT
WHERE
lane_id = @lane_id
AND revision_id = @revision_id
AND host_id = @host_id;
";
DB.CreateParameter(cmd, "lane_id", lane_id);
DB.CreateParameter(cmd, "revision_id", revision_id);
DB.CreateParameter(cmd, "host_id", host_id);
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Deletes all the files related to the work in the revision 'revision_id' of lane 'lane' on the host 'host'.
/// </summary>
/// <param name="host"></param>
/// <param name="lane"></param>
/// <param name="revision_id"></param>
public void DeleteFiles(int host_id, int lane_id, int revision_id)
{
using (IDbTransaction transaction = BeginTransaction()) {
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = @"
SELECT WorkFile.id AS id
INTO TEMP WorkFile_delete_tmpfile
FROM WorkFile
INNER JOIN Work ON Work.id = WorkFile.work_id
INNER JOIN RevisionWork ON RevisionWork.id = Work.revisionwork_id
WHERE
RevisionWork.lane_id = @lane_id AND
RevisionWork.host_id = @host_id AND
RevisionWork.revision_id = @revision_id;
DELETE FROM WorkFile
WHERE id IN (select * from WorkFile_delete_tmpfile);
DROP TABLE WorkFile_delete_tmpfile;
";
DB.CreateParameter(cmd, "lane_id", lane_id);
DB.CreateParameter(cmd, "host_id", host_id);
DB.CreateParameter(cmd, "revision_id", revision_id);
cmd.ExecuteNonQuery();
transaction.Commit();
}
}
}
public DBHost LookupHost(string host, bool throwOnError)
{
DBHost result = null;
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = "SELECT * FROM Host WHERE host = @host";
DB.CreateParameter(cmd, "host", host);
using (IDataReader reader = cmd.ExecuteReader()) {
if (!reader.Read())
{
if (!throwOnError)
{
return(null);
}
throw new Exception(string.Format("Could not find the host '{0}'.", host));
}
result = new DBHost(reader);
if (reader.Read())
{
if (!throwOnError)
{
return(null);
}
throw new Exception(string.Format("Found more than one host named '{0}'.", host));
}
}
}
return(result);
}
public List <DBRevision> GetDBRevisions(int lane_id, int limit, int offset, bool ordered = true)
{
List <DBRevision> result = new List <DBRevision> ();
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = "SELECT * FROM Revision WHERE lane_id = @lane_id";
if (ordered)
{
cmd.CommandText += " ORDER BY date DESC";
}
if (limit > 0)
{
cmd.CommandText += " LIMIT " + limit.ToString();
}
if (offset > 0)
{
cmd.CommandText += " OFFSET " + offset.ToString();
}
DB.CreateParameter(cmd, "lane_id", lane_id);
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
result.Add(new DBRevision(reader));
}
}
}
return(result);
}
public DBLane LookupLane(string lane, bool throwOnError)
{
DBLane result = null;
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = "SELECT * FROM Lane WHERE lane = @lane";
DB.CreateParameter(cmd, "lane", lane);
using (IDataReader reader = cmd.ExecuteReader()) {
if (!reader.Read())
{
if (!throwOnError)
{
return(null);
}
throw new Exception(string.Format("Could not find the lane '{0}'.", lane));
}
result = new DBLane(reader);
if (reader.Read())
{
if (!throwOnError)
{
return(null);
}
throw new Exception(string.Format("Found more than one lane named '{0}'.", lane));
}
}
}
return(result);
}
public void DeleteAllWork(int lane_id, int host_id)
{
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = "DELETE FROM Work WHERE lane_id = @lane_id AND host_id = @host_id;";
DB.CreateParameter(cmd, "lane_id", lane_id);
DB.CreateParameter(cmd, "host_id", host_id);
cmd.ExecuteNonQuery();
}
//TODO: Directory.Delete(Configuration.GetDataRevisionDir(lane, revision), true);
}
public bool HasWork(int lane_id, int revision_id, int host_id)
{
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = "SELECT Count (*) FROM Work WHERE lane_id = @lane_id AND revision_id = @revision_id AND host_id = @host_id";
DB.CreateParameter(cmd, "lane_id", lane_id);
DB.CreateParameter(cmd, "revision_id", revision_id);
DB.CreateParameter(cmd, "host_id", host_id);
return((int)cmd.ExecuteScalar() != 0);
}
}
public void ClearAllWork(int lane_id, int host_id)
{
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = "UPDATE Work SET state = 0, summary = '' " +
"WHERE lane_id = @lane_id " +
"AND host_id = @host_id;";
DB.CreateParameter(cmd, "lane_id", lane_id);
DB.CreateParameter(cmd, "host_id", host_id);
cmd.ExecuteNonQuery();
}
}
public List <DBWorkView2> GetWork(DBRevisionWork revisionwork)
{
List <DBWorkView2> result = new List <DBWorkView2> ();
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = "SELECT * FROM WorkView2 WHERE revisionwork_id = @revisionwork_id ORDER BY sequence";
DB.CreateParameter(cmd, "revisionwork_id", revisionwork.id);
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
result.Add(new DBWorkView2(reader));
}
}
}
return(result);
}
public List <DBHost> GetHostsForLane(int lane_id)
{
List <DBHost> result = new List <DBHost> ();
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = "SELECT *, HostLane.lane_id AS lane_id FROM Host INNER JOIN HostLane ON Host.id = HostLane.host_id WHERE lane_id = @lane_id";
DB.CreateParameter(cmd, "lane_id", lane_id);
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
result.Add(new DBHost(reader));
}
}
}
return(result);
}
public void DeleteLinks(int host_id, int lane_id, int revision_id)
{
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = @"
DELETE FROM FileLink WHERE work_id IN
(SELECT id FROM Work WHERE Work.revisionwork_id IN
(SELECT RevisionWork.id
FROM RevisionWork
WHERE RevisionWork.host_id = @host_id AND RevisionWork.lane_id = @lane_id AND RevisionWork.revision_id = @revision_id
)
);
";
DB.CreateParameter(cmd, "lane_id", lane_id);
DB.CreateParameter(cmd, "revision_id", revision_id);
DB.CreateParameter(cmd, "host_id", host_id);
cmd.ExecuteNonQuery();
}
}
public int GetLastRevision(string lane)
{
using (IDbCommand cmd = CreateCommand()) {
DBLane l = LookupLane(lane);
cmd.CommandText = "SELECT max (CAST (revision AS int)) FROM Revision WHERE lane_id = @lane_id";
DB.CreateParameter(cmd, "lane_id", l.id);
using (IDataReader reader = cmd.ExecuteReader()) {
if (!reader.Read())
{
return(0);
}
if (reader.IsDBNull(0))
{
return(0);
}
return(reader.GetInt32(0));
}
}
}
public List <int> GetRevisions(string lane, int limit)
{
List <int> result = new List <int> ();
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = "SELECT DISTINCT CAST (revision as int) FROM revisions WHERE lane = @lane ORDER BY revision DESC";
if (limit > 0)
{
cmd.CommandText += " LIMIT " + limit.ToString();
}
DB.CreateParameter(cmd, "lane", lane);
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
result.Add(reader.GetInt32(0));
}
}
}
return(result);
}
/// <summary>
/// Returns lane that matches lane_id in the database
/// </summary>
/// <returns></returns>
public DBLane GetLane(int id)
{
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = "SELECT * FROM Lane WHERE id = @id ";
DB.CreateParameter(cmd, "id", id);
using (IDataReader reader = cmd.ExecuteReader()) {
if (!reader.Read())
{
return(null);
}
if (reader.IsDBNull(0))
{
return(null);
}
DBLane lane = new DBLane();
lane.Load(reader);
return(lane);
}
}
}
public List <DBLane> GetLanesForHost(int host_id, bool only_enabled)
{
List <DBLane> result = new List <DBLane> ();
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = "SELECT *, HostLane.host_id AS host_id, HostLane.enabled AS lane_enabled FROM Lane INNER JOIN HostLane ON Lane.id = HostLane.lane_id WHERE host_id = @host_id ";
if (only_enabled)
{
cmd.CommandText += " AND HostLane.enabled = true;";
}
DB.CreateParameter(cmd, "host_id", host_id);
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
result.Add(new DBLane(reader));
}
}
}
return(result);
}
public void DeleteWork(int lane_id, int revision_id, int host_id)
{
using (IDbCommand cmd = CreateCommand()) {
// cmd.CommandText = "DELETE FROM Work WHERE lane_id = @lane_id AND revision_id = @revision_id AND host_id = @host_id;";
cmd.CommandText = @"
DELETE FROM Work
WHERE Work.revisionwork_id =
(SELECT id
FROM RevisionWork
WHERE lane_id = @lane_id
AND revision_id = @revision_id
AND host_id = @host_id
);
UPDATE RevisionWork SET state = 10 WHERE lane_id = @lane_id AND host_id = @host_id AND revision_id = @revision_id;";
DB.CreateParameter(cmd, "lane_id", lane_id);
DB.CreateParameter(cmd, "revision_id", revision_id);
DB.CreateParameter(cmd, "host_id", host_id);
cmd.ExecuteNonQuery();
}
//TODO: Directory.Delete(Configuration.GetDataRevisionDir(lane, revision), true);
}
public DBWork GetNextStep(string lane)
{
DBWork result = null;
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = "SELECT * FROM steps WHERE lane = @lane AND (state = 0 OR state = 1) ORDER BY revision DESC, sequence LIMIT 1";
DB.CreateParameter(cmd, "lane", lane);
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
if (result != null)
{
throw new Exception("Got more than one step");
}
result = new DBWork();
result.Load(reader);
}
}
}
return(result);
}
public DBRevision GetRevision(string lane, int revision)
{
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = "SELECT * from revisions where lane = @lane AND revision = @revision";
DB.CreateParameter(cmd, "lane", lane);
DB.CreateParameter(cmd, "revision", revision.ToString());
using (IDataReader reader = cmd.ExecuteReader()) {
if (!reader.Read())
{
return(null);
}
if (reader.IsDBNull(0))
{
return(null);
}
DBRevision rev = new DBRevision();
rev.Load(reader);
return(rev);
}
}
}
public List <DBCommand> GetCommands(int lane_id)
{
List <DBCommand> result = new List <DBCommand> ();
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = "SELECT * FROM Command ";
if (lane_id > 0)
{
cmd.CommandText += "WHERE lane_id = @lane_id ";
DB.CreateParameter(cmd, "lane_id", lane_id);
}
cmd.CommandText += "ORDER BY sequence ASC";
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
result.Add(new DBCommand(reader));
}
}
}
return(result);
}
/// <summary>
/// This will return a list of lane ids:
/// [0]: input lane_id
/// [1]: [0]'s parent lane id
/// [2]: [1]'s parent lane id
/// etc
/// </summary>
/// <param name="lane_id"></param>
/// <returns></returns>
public List <int> GetLaneHierarchy(int lane_id)
{
var result = new List <int> ();
using (var cmd = CreateCommand()) {
cmd.CommandText = @"
WITH RECURSIVE Family AS (
SELECT lane.id, lane.parent_lane_id, 0 AS depth FROM lane WHERE lane.id = @lane_id
UNION ALL
SELECT lane2.id, lane2.parent_lane_id, depth + 1 FROM lane lane2 JOIN family ON family.parent_lane_id = lane2.id)
SELECT family.id FROM family ORDER BY depth ASC;";
DB.CreateParameter(cmd, "lane_id", lane_id);
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
result.Add(reader.GetInt32(0));
}
}
}
return(result);
}
public DBHostLane GetHostLane(int host_id, int lane_id)
{
DBHostLane result;
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = "SELECT * FROM HostLane WHERE lane_id = @lane_id AND host_id = @host_id;";
DB.CreateParameter(cmd, "host_id", host_id);
DB.CreateParameter(cmd, "lane_id", lane_id);
using (IDataReader reader = cmd.ExecuteReader()) {
if (!reader.Read())
{
return(null);
}
result = new DBHostLane(reader);
if (reader.Read())
{
throw new Exception(string.Format("Found more than one HostLane with host_id {0} and lane_id {1}", host_id, lane_id));
}
}
}
return(result);
}
/// <summary>
/// Will return a locked revision work.
/// </summary>
/// <param name="lane"></param>
/// <param name="host"></param>
/// <returns></returns>
public DBRevisionWork GetRevisionWork(DBLane lane, DBHost host, DBHost workhost)
{
DBRevisionWork result = null;
using (IDbCommand cmd = CreateCommand()) {
// sorting by RevisionWork.workhost_id ensures that we'll get
// revisionwork which has been started at the top of the list.
cmd.CommandText = @"
SELECT
RevisionWork.*
FROM
RevisionWork
INNER JOIN
Revision ON RevisionWork.revision_id = Revision.id
WHERE
RevisionWork.host_id = @host_id
AND (RevisionWork.workhost_id = @workhost_id OR RevisionWork.workhost_id IS NULL)
AND RevisionWork.lane_id = @lane_id
AND RevisionWork.state <> @dependencynotfulfilled AND RevisionWork.state <> 10 AND RevisionWork.State <> @ignore
AND RevisionWork.completed = false
ORDER BY RevisionWork.workhost_id IS NULL ASC, Revision.date DESC
LIMIT 1
;";
DB.CreateParameter(cmd, "host_id", host.id);
DB.CreateParameter(cmd, "lane_id", lane.id);
DB.CreateParameter(cmd, "workhost_id", workhost.id);
DB.CreateParameter(cmd, "dependencynotfulfilled", (int)DBState.DependencyNotFulfilled);
DB.CreateParameter(cmd, "ignore", (int)DBState.Ignore);
using (IDataReader reader = cmd.ExecuteReader()) {
if (reader.Read())
{
result = new DBRevisionWork(reader);
}
}
}
return(result);
}
/// <summary>
/// Checks if the specified RevisionWork is the latest.
/// </summary>
/// <param name="current"></param>
/// <returns></returns>
public bool IsLatestRevisionWork(DBRevisionWork current)
{
using (IDbCommand cmd = CreateCommand()) {
cmd.CommandText = @"
SELECT
RevisionWork.id
FROM
RevisionWork
INNER JOIN
Revision ON RevisionWork.revision_id = Revision.id
WHERE
lock_expires < now () AND
RevisionWork.host_id = @host_id
AND RevisionWork.lane_id = @lane_id
AND RevisionWork.completed = false
ORDER BY Revision.date DESC
LIMIT 1
;";
DB.CreateParameter(cmd, "host_id", current.host_id);
DB.CreateParameter(cmd, "lane_id", current.lane_id);
using (IDataReader reader = cmd.ExecuteReader()) {
if (!reader.Read())
{
log.Debug("IsLatestRevisionWork: No result.");
return(true);
}
if (reader.GetInt32(0) <= current.id)
{
return(true);
}
log.DebugFormat("IsLatestRevisionWork: Latest id: {0}, current id: {1}", reader.GetInt32(0), current.id);
return(false);
}
}
}
