C# DB.GetAllLanes方法代码示例

		public object Any (LatestBuilds request)
		{
			var result = new List<KeyValuePair<DBHost, DBRevisionWorkView2>> ();
			List<DBLane> lanes = null;
			var hostLanes = new List<DBHostLane> ();

			using (DB db = new DB ()) {
				lanes = db.GetAllLanes ();

				using (IDbCommand cmd = db.CreateCommand ()) {
					cmd.CommandText = @"
SELECT HostLane.*
FROM HostLane
WHERE hidden = false";
					
					using (IDataReader reader = cmd.ExecuteReader ())
						while (reader.Read ())
							hostLanes.Add (new DBHostLane (reader));
				}

				foreach (DBHostLane hl in hostLanes) {
					DBRevisionWorkView2 revisionWork = null;
					using (IDbCommand cmd = db.CreateCommand ()) {
						cmd.CommandText = @"SELECT R.* FROM (" + DBRevisionWorkView2.SQL.Replace (';', ' ') + ") AS R WHERE R.host_id = @host_id AND R.lane_id = @lane_id LIMIT @limit";
						DB.CreateParameter (cmd, "host_id", hl.host_id);
						DB.CreateParameter (cmd, "lane_id", hl.lane_id);
						DB.CreateParameter (cmd, "limit", 1);
						
						using (IDataReader reader = cmd.ExecuteReader ())
							while (reader.Read ())
								revisionWork = new DBRevisionWorkView2 (reader);
					}

					result.Add (new KeyValuePair<DBHost, DBRevisionWorkView2> (Utils.FindHost (db, revisionWork.host_id), revisionWork));
				}
			}

			var list = result.Where (view => view.Value != null).Select (view => {
				var item = view.Value;
				var lane = lanes.Where (l => l.id == item.lane_id).FirstOrDefault ();
				var parent = Utils.GetTopMostParent (lane, lanes);
				return new Build {
					Commit = item.revision,
					CommitId = item.revision_id,
					Date = item.completed ? item.endtime : item.date,
					Lane = lane.lane,
					LaneID = lane.id,
					Project = parent.lane,
					State = item.State,
					Author = item.author,
					BuildBot = view.Key == null ? string.Empty : view.Key.host,
					HostID = item.host_id,
					Url = Utils.MakeBuildUrl (item.lane_id, item.host_id, item.revision_id)
				};
			}).OrderByDescending (b => b.Date).ToList ();

			return new LatestBuildsResponse {
				LatestBuilds = list
			};
		}

		public object Any (Projects request)
		{
			List<DBLane> lanes = null;
			using (DB db = new DB ())
				lanes = db.GetAllLanes ();
			var parents = new HashSet<int?> (lanes.Where (l => l.parent_lane_id != null).Select (l => l.parent_lane_id));
			return new ProjectsResponse {
				Projects = lanes.Where (l => !parents.Contains (l.id))
					            .ToLookup (l => Utils.GetTopMostParent (l, lanes))
					            .ToDictionary (ls => new Lane { ID = ls.Key.id, Name = ls.Key.lane },
					                           ls => ls.Select (l => new Lane { ID = l.id, Name = l.lane}).ToList ())
			};
		}

		public GetLaneForEditResponse GetLaneForEdit (WebServiceLogin login, int lane_id, string lane)
		{
			GetLaneForEditResponse response = new GetLaneForEditResponse ();
			using (DB db = new DB ()) {
				Authenticate (db, login, response);
				VerifyUserInRole (db, login, Roles.Administrator);

				// We do 2 trips to the database: first to get a list of all the lanes,
				// then to get all the rest of the information.

				response.Lanes = db.GetAllLanes ();

				if (lane_id > 0) {
					response.Lane = response.Lanes.Find ((l) => l.id == lane_id);
				} else {
					response.Lane = response.Lanes.Find ((l) => l.lane == lane);
				}

				var cmdText = new StringBuilder ();

				using (var cmd = db.CreateCommand ()) {
					// 1: db.GetAllLanes
					cmdText.AppendLine ("SELECT * FROM Lane ORDER BY lane;");

					// 2: response.Lane.GetCommandsInherited (db, response.Lanes);
					cmdText.Append ("SELECT * FROM Command WHERE lane_id = ").Append (response.Lane.id);
					DBLane parent = response.Lane;
					while (null != (parent = response.Lanes.FirstOrDefault ((v) => v.id == parent.parent_lane_id))) {
						cmdText.Append (" OR lane_id = ").Append (parent.id);
					}
					cmdText.AppendLine (" ORDER BY sequence;");

					// 3: response.Dependencies = response.Lane.GetDependencies (db);
					cmdText.AppendFormat ("SELECT * FROM LaneDependency WHERE lane_id = {0} ORDER BY dependent_lane_id;", response.Lane.id).AppendLine ();

//					// 4: response.FileDeletionDirectives = DBFileDeletionDirective_Extensions.GetAll (db);
//					cmdText.AppendLine ("SELECT * FROM FileDeletionDirective;");
//
//					// 5: response.LaneDeletionDirectives = DBLaneDeletionDirectiveView_Extensions.Find (db, response.Lane);
//					cmdText.AppendFormat ("SELECT * FROM LaneDeletionDirectiveView WHERE lane_id = {0};", response.Lane.id).AppendLine ();

					// 6: response.Files = response.Lane.GetFiles (db, response.Lanes);
					cmdText.Append (@"
SELECT Lanefile.id, LaneFile.name, '' AS contents, LaneFile.mime, Lanefile.original_id, LaneFile.changed_date 
FROM Lanefile 
INNER JOIN Lanefiles ON Lanefiles.lanefile_id = Lanefile.id 
WHERE Lanefile.original_id IS NULL AND Lanefiles.lane_id = ").Append (response.Lane.id);
					parent = response.Lane;
					while (null != (parent = response.Lanes.FirstOrDefault ((v) => v.id == parent.parent_lane_id))) {
						cmdText.Append (" OR LaneFiles.lane_id = ").Append (parent.id);
					}
					cmdText.AppendLine (" ORDER BY name ASC;");

					// 7: response.LaneFiles = db.GetAllLaneFiles ();
					cmdText.AppendLine ("SELECT * FROM LaneFiles;");

					// 8: response.HostLaneViews = response.Lane.GetHosts (db);
					cmdText.AppendFormat ("SELECT * FROM HostLaneView WHERE lane_id = {0} ORDER BY host;", response.Lane.id).AppendLine ();

					// 9: response.Hosts = db.GetHosts ();
					cmdText.AppendLine ("SELECT * FROM Host ORDER BY host;");

					// 10: response.ExistingFiles = new List<DBLanefile> (); [...]
					cmdText.AppendFormat (@"
SELECT Lanefile.id, LaneFile.name, '' AS contents, LaneFile.mime, Lanefile.original_id, LaneFile.changed_date 
FROM Lanefile
INNER JOIN Lanefiles ON Lanefiles.lanefile_id = Lanefile.id
WHERE Lanefile.original_id IS NULL AND Lanefiles.lane_id <> {0}
ORDER BY Lanefiles.lane_id, Lanefile.name ASC;", response.Lane.id).AppendLine ();

					// 11: response.Variables = DBEnvironmentVariable_Extensions.Find (db, response.Lane.id, null, null);
					cmdText.AppendFormat ("SELECT * FROM EnvironmentVariable WHERE lane_id = {0} AND host_id IS NULL ORDER BY id ASC;", response.Lane.id).AppendLine ();

					// 12: response.Notifications = new List<DBNotification> ();
					cmdText.AppendLine ("SELECT * FROM Notification;");

					// 13: response.LaneNotifications = new List<DBLaneNotification> ();
					cmdText.AppendFormat ("SELECT * FROM LaneNotification WHERE lane_id = {0};", response.Lane.id).AppendLine ();

					// 14
					cmdText.AppendFormat ("SELECT * FROM LaneTag WHERE lane_id = {0};", response.Lane.id).AppendLine ();

					cmd.CommandText = cmdText.ToString ();

					using (IDataReader reader = cmd.ExecuteReader ()) {
						// 1: db.GetAllLanes
						response.Lanes = new List<DBLane> ();
						while (reader.Read ())
							response.Lanes.Add (new DBLane (reader));

						// 2: response.Lane.GetCommandsInherited (db, response.Lanes);
						reader.NextResult ();
						response.Commands = new List<DBCommand> ();
						while (reader.Read ())
							response.Commands.Add (new DBCommand (reader));
						
						// 3: response.Dependencies = response.Lane.GetDependencies (db);
						reader.NextResult ();
						response.Dependencies = new List<DBLaneDependency> ();
						while (reader.Read ())
//.........这里部分代码省略.........

		public GetHostForEditResponse GetHostForEdit (WebServiceLogin login, int? host_id, string host)
		{
			GetHostForEditResponse response = new GetHostForEditResponse ();

			using (DB db = new DB ()) {
				VerifyUserInRole (db, login, Roles.Administrator);

				response.Host = FindHost (db, host_id, host);
				response.Lanes = db.GetAllLanes ();
				if (response.Host != null) {
					response.Person = FindPerson (db, response.Host.host);
					response.HostLaneViews = response.Host.GetLanes (db);
					response.Variables = DBEnvironmentVariable_Extensions.Find (db, null, response.Host.id, null);
					response.MasterHosts = GetMasterHosts (db, response.Host);
					response.SlaveHosts = GetSlaveHosts (db, response.Host);
				}
				response.Hosts = db.GetHosts ();
			}

			return response;
		}

		public GetBuildInfoResponse GetBuildInfoMultiple (WebServiceLogin login, string host, bool multiple_work)
		{
			List<DBHost> hosts = new List<DBHost> (); // list of hosts to find work for
			List<DBHostLane> hostlanes = new List<DBHostLane> ();
			List<DBLane> lanes = new List<DBLane> ();

			GetBuildInfoResponse response = new GetBuildInfoResponse ();

			response.Work = new List<List<BuildInfoEntry>> ();

			using (DB db = new DB ()) {
				VerifyUserInRole (db, login, Roles.BuildBot, true);

				response.Host = FindHost (db, null, host);

				if (!response.Host.enabled)
					return response;

				// find the master hosts for this host (if any)
				response.MasterHosts = FindMasterHosts (db, response.Host);

				// get the hosts to find work for
				if (response.MasterHosts != null && response.MasterHosts.Count > 0) {
					foreach (DBMasterHost mh in response.MasterHosts)
						hosts.Add (DBHost_Extensions.Create (db, mh.master_host_id));
				} else {
					hosts.Add (response.Host);
				}

				// find the enabled hostlane combinations for these hosts
				using (IDbCommand cmd = db.CreateCommand ()) {
					cmd.CommandText = "SELECT HostLane.* FROM HostLane INNER JOIN Lane ON Lane.id = HostLane.lane_id WHERE Lane.enabled = TRUE AND HostLane.enabled = TRUE AND (";
					for (int i = 0; i < hosts.Count; i++) {
						if (i > 0)
							cmd.CommandText += " OR ";
						cmd.CommandText += " HostLane.host_id = " + hosts [i].id;
					}
					cmd.CommandText += ")";
					using (IDataReader reader = cmd.ExecuteReader ()) {
						while (reader.Read ())
							hostlanes.Add (new DBHostLane (reader));
					}
				}

				if (hostlanes.Count == 0)
					return response; // nothing to do here

				lanes = db.GetAllLanes ();

				switch (response.Host.QueueManagement) {
				case DBQueueManagement.OneRevisionWorkAtATime:
					if (hostlanes.Count > 1) {
						int latest = -1;
						DateTime latest_date = DateTime.MaxValue;

						// we need to find the latest revisionwork each hostlane has completed.
						// we want to work on the hostlane which has waited the longest amount
						// of time without getting work done (but which has pending work to do).

						for (int i = 0; i < hostlanes.Count; i++) {
							DBHostLane hl = hostlanes [i];
							// check if this hostlane has pending work.
							// this would ideally be included in the query below, but I'm not sure
							// how to do that while still distinguising the case where nothing has
							// been done ever for a hostlane.
							using (IDbCommand cmd = db.CreateCommand ()) {
								cmd.CommandText = @"
SELECT RevisionWork.id
FROM RevisionWork
WHERE
    RevisionWork.host_id = @host_id
AND (RevisionWork.workhost_id = @workhost_id OR RevisionWork.workhost_id IS NULL)
AND RevisionWork.completed = false
AND RevisionWork.state <> 9 AND RevisionWork.state <> 10 AND RevisionWork.state <> 11
AND lane_id = @lane_id
LIMIT 1;
    ";
								DB.CreateParameter (cmd, "lane_id", hl.lane_id);
								DB.CreateParameter (cmd, "host_id", hl.host_id);
								DB.CreateParameter (cmd, "workhost_id", response.Host.id);

								object obj = cmd.ExecuteScalar ();
								if (obj == DBNull.Value || obj == null) {
									// there is nothing to do for this hostlane
									continue;
								}

							}

							// find the latest completed (this may not be correct, maybe find the latest unstarted?)
							// revisionwork for this hostlane.
							using (IDbCommand cmd = db.CreateCommand ()) {
								cmd.CommandText = @"
SELECT 	RevisionWork.endtime
FROM RevisionWork
WHERE 
RevisionWork.host_id = @host_id
AND (RevisionWork.workhost_id = @workhost_id OR RevisionWork.workhost_id IS NULL)
AND RevisionWork.completed = true
AND lane_id = @lane_id
//.........这里部分代码省略.........

		public GetLanesResponse GetLanes (WebServiceLogin login)
		{
			GetLanesResponse response = new GetLanesResponse ();

			using (DB db = new DB ()) {
				Authenticate (db, login, response);
				response.Lanes = db.GetAllLanes ();
			}

			return response;
		}

		public static void Execute ()
		{
			long space_recovered = 0;

			try {
				LogWithTime ("ExecuteDeletionDirectives: Start");
				
				is_executing = true;

				using (DB db = new DB (true)) {
					List<DBLane> lanes = db.GetAllLanes ();
					foreach (DBLane lane in lanes) {
						LogWithTime ("ExecuteDeletionDirectives: Lane = {0} {1}", lane.id, lane.lane);
						List<DBLaneDeletionDirectiveView> directives = DBLaneDeletionDirectiveView_Extensions.Find (db, lane);
						foreach (DBLaneDeletionDirectiveView directive in directives) {
							LogWithTime ("ExecuteDeletionDirectives: Found directive: '{0}' Enabled: {1}, Condition: {2}, Filename: '{3}', MatchMode: {4}, X: {5}",
								directive.name, directive.enabled, directive.Condition, directive.filename, directive.MatchMode, directive.x);

							if (!directive.enabled)
								continue;

							string sql = @"
SELECT 
	WorkFile.id AS workfile_id,
	WorkFile.filename AS workfile_filename,
	File.id AS file_id,
	File.file_id AS file_file_id,
	File.md5,
	File.size,
	Revision.revision
FROM 
	WorkFile
INNER JOIN Work ON Work.id = WorkFile.work_id
INNER JOIN File ON WorkFile.file_id = File.id	
INNER JOIN RevisionWork ON RevisionWork.id = Work.revisionwork_id
INNER JOIN Revision ON Revision.id = RevisionWork.revision_id
WHERE
	RevisionWork.lane_id = @lane_id
	AND RevisionWork.completed = TRUE
";
							switch (directive.Condition) {
							case DBDeleteCondition.AfterXBuiltRevisions:
								sql += string.Format (@" 
AND Revision.id NOT IN (
	SELECT Revision.id
	FROM Revision
	INNER JOIN RevisionWork ON Revision.id = RevisionWork.revision_id
	WHERE 
		RevisionWork.lane_id = @lane_id 
		AND Revision.lane_id = @lane_id
		AND RevisionWork.completed = TRUE
	ORDER BY Revision.date DESC
	LIMIT {0}
)
", (int) directive.x);
								break;
							case DBDeleteCondition.AfterXDays:
								sql += string.Format (@"
AND Work.endtime + interval '{0} days' < now ();
", (int) directive.x);
								break;
							default:
								continue;
							}

							using (IDbCommand cmd = db.CreateCommand (TimeSpan.FromHours (1 /* this is a slow query, have a big timeout */))) {
								cmd.CommandText = sql;
								DB.CreateParameter (cmd, "lane_id", lane.id);
								using (IDataReader reader = cmd.ExecuteReader ()) {
									using (DB write_db = new DB (true)) {
										while (reader.Read ()) {
											int index;
											string workfile_filename;
											int workfile_id;
											int? file_file_id = null;
											int file_id;
											int size;
											string md5;
											bool match;

											workfile_filename = reader.GetString (reader.GetOrdinal ("workfile_filename"));
											match = directive.IsFileNameMatch (workfile_filename);

											if (!match)
												continue;

											index = reader.GetOrdinal ("file_file_id");
											if (!reader.IsDBNull (index))
												file_file_id = reader.GetInt32 (reader.GetOrdinal ("file_file_id"));
											file_id = reader.GetInt32 (reader.GetOrdinal ("file_id"));
											size = reader.GetInt32 (reader.GetOrdinal ("size"));
											md5 = reader.GetString (reader.GetOrdinal ("md5"));
											workfile_id = reader.GetInt32 (reader.GetOrdinal ("workfile_id"));

											LogWithTime ("ExecuteDeletionDirectives:  >Processing: workfile_id: {0}, workfile_filename: '{1}', file_id: {2}, md5: {3}, match: {4}", workfile_id, workfile_filename, file_id, md5, match);

											// delete the work file
											DBRecord_Extensions.Delete (write_db, workfile_id, DBWorkFile.TableName);
											LogWithTime ("ExecuteDeletionDirectives:  >>WorkFile {0} deleted succesfully.", workfile_id);

//.........这里部分代码省略.........

		public static bool ExecuteScheduler (bool forcefullupdate)
		{
			DateTime start;
			Lock scheduler_lock = null;
			List<DBLane> lanes;

			List<DBHost> hosts;
			List<DBHostLane> hostlanes;
			List<XmlDocument> reports;
			
			try {
				scheduler_lock = Lock.Create ("MonkeyWrench.Scheduler");
				if (scheduler_lock == null) {
					log.Info ("Could not aquire scheduler lock.");
					return false;
				}

				log.Info ("Scheduler lock aquired successfully.");
				
				is_executing = true;
				start = DateTime.Now;

				// SVNUpdater.StartDiffThread ();

				// Check reports
				reports = GetReports (forcefullupdate);

				using (DB db = new DB (true)) {
					lanes = db.GetAllLanes ();
					hosts = db.GetHosts ();
					hostlanes = db.GetAllHostLanes ();

					log.InfoFormat ("Updater will now update {0} lanes.", lanes.Count);

					GITUpdater git_updater = null;
					// SVNUpdater svn_updater = null;

					foreach (DBLane lane in lanes) {
						if (!lane.enabled) {
							log.InfoFormat ("Schedule: lane {0} is disabled, skipping it.", lane.lane);
							continue;
						}

						SchedulerBase updater;
						switch (lane.source_control) {
							/*
						case "svn":
							if (svn_updater == null)
								svn_updater = new SVNUpdater (forcefullupdate);
							updater = svn_updater;
							break;
							 * */
						case "git":
							if (git_updater == null)
								git_updater = new GITUpdater (forcefullupdate);
							updater = git_updater;
							break;
						default:
							log.ErrorFormat ("Unknown source control: {0} for lane {1}", lane.source_control, lane.lane);
							continue;
						}
						updater.Clear ();
						updater.AddChangeSets (reports);
						updater.UpdateRevisionsInDB (db, lane, hosts, hostlanes);
					}

					AddRevisionWork (db);
					AddWork (db, hosts, lanes, hostlanes);
					CheckDependencies (db, hosts, lanes, hostlanes);
				}

				// SVNUpdater.StopDiffThread ();

				log.InfoFormat ("Update finished successfully in {0} seconds.", (DateTime.Now - start).TotalSeconds);

				return true;
			} catch (Exception ex) {
				log.ErrorFormat ("An exception occurred: {0}", ex);
				return false;
			} finally {
				if (scheduler_lock != null)
					scheduler_lock.Unlock ();
				is_executing = false;
			}
		}

		public static bool ExecuteScheduler (bool forcefullupdate)
		{
			Lock scheduler_lock = null;
			List<DBLane> lanes;

			List<DBHost> hosts;
			List<DBHostLane> hostlanes;
			List<XmlDocument> reports;
			
			try {
				scheduler_lock = Lock.Create ("MonkeyWrench.Scheduler");
				if (scheduler_lock == null) {
					Logger.Log ("Could not aquire scheduler lock.");
					return false;
				}

				Logger.Log ("Scheduler lock aquired successfully.");
				
				is_executing = true;

				SVNUpdater.StartDiffThread ();

				// Check reports
				reports = GetReports (forcefullupdate);

				using (DB db = new DB (true)) {
					lanes = db.GetAllLanes ();
					hosts = db.GetHosts ();
					hostlanes = db.GetAllHostLanes ();
					Logger.Log ("Updater will now update {0} lanes.", lanes.Count);
					foreach (DBLane lane in lanes) {
						SchedulerBase updater;
						switch (lane.source_control) {
						case "svn":
							updater = new SVNUpdater (forcefullupdate);
							break;
						case "git":
							updater = new GITUpdater (forcefullupdate);
							break;
						default:
							Logger.Log ("Unknown source control: {0} for lane {1}", lane.source_control, lane.lane);
							continue;
						}
						updater.AddChangeSets (reports);
						updater.UpdateRevisionsInDB (db, lane, hosts, hostlanes);
						UpdateBuildLogDB (db, lane, hosts, hostlanes);
					}
				}

				Logger.Log ("Update done, waiting for diff thread to finish...");

				SVNUpdater.StopDiffThread ();

				Logger.Log ("Update finished successfully.");

				return true;
			} catch (Exception ex) {
				Logger.Log ("An exception occurred: {0}", ex.ToString ());
				return false;
			} finally {
				if (scheduler_lock != null)
					scheduler_lock.Unlock ();
				is_executing = false;
			}
		}

		public GetLaneForEditResponse GetLaneForEdit (WebServiceLogin login, int lane_id, string lane)
		{
			GetLaneForEditResponse response = new GetLaneForEditResponse ();
			using (DB db = new DB ()) {
				Authenticate (db, login, response);
				VerifyUserInRole (db, login, Roles.Administrator);

				response.Lane = FindLane (db, lane_id, lane);
				response.Commands = response.Lane.GetCommands (db);
				response.Dependencies = response.Lane.GetDependencies (db);
				response.FileDeletionDirectives = DBFileDeletionDirective_Extensions.GetAll (db);
				response.LaneDeletionDirectives = DBLaneDeletionDirectiveView_Extensions.Find (db, response.Lane);
				response.Files = response.Lane.GetFiles (db);
				response.HostLaneViews = response.Lane.GetHosts (db);
				response.Hosts = db.GetHosts ();
				response.Lanes = db.GetAllLanes ();

				response.ExistingFiles = new List<DBLanefile> ();
				using (IDbCommand cmd = db.CreateCommand ()) {
					cmd.CommandText = @"
SELECT Lanefile.*
FROM Lanefile
INNER JOIN Lanefiles ON Lanefiles.lanefile_id = Lanefile.id
WHERE Lanefiles.lane_id <> @lane_id 
ORDER BY Lanefiles.lane_id, Lanefile.name ASC";
					DB.CreateParameter (cmd, "lane_id", response.Lane.id);
					using (IDataReader reader = cmd.ExecuteReader ()) {
						while (reader.Read ())
							response.ExistingFiles.Add (new DBLanefile (reader));
					}
				}

				response.Variables = DBEnvironmentVariable_Extensions.Find (db, response.Lane.id, null, null);

				response.Notifications = new List<DBNotification> ();
				response.LaneNotifications = new List<DBLaneNotification> ();
				using (IDbCommand cmd = db.CreateCommand ()) {
					cmd.CommandText = "SELECT * FROM Notification; SELECT * FROM LaneNotification WHERE lane_id = @lane_id;";
					DB.CreateParameter (cmd, "lane_id", response.Lane.id);
					using (IDataReader reader = cmd.ExecuteReader ()) {
						while (reader.Read ()) {
							response.Notifications.Add (new DBNotification (reader));
						}
						if (reader.NextResult ()) {
							while (reader.Read ()) {
								response.LaneNotifications.Add (new DBLaneNotification (reader));
							}
						}
					}
				}

				return response;
			}
		}