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); } } }