public void TestInitialise()
{
_OriginalClassFactory = Factory.TakeSnapshot();
_RuntimeEnvironment = TestUtilities.CreateMockSingleton<IRuntimeEnvironment>();
_ConfigurationStorage = TestUtilities.CreateMockSingleton<IConfigurationStorage>();
_Configuration = new Configuration();
_ConfigurationStorage.Setup(s => s.Load()).Returns(_Configuration);
_CreateDatabaseFileName = Path.Combine(TestContext.TestDeploymentDir, "CreatedDatabase.sqb");
if(File.Exists(_CreateDatabaseFileName)) File.Delete(_CreateDatabaseFileName);
_EmptyDatabaseFileName = Path.Combine(TestContext.TestDeploymentDir, "TestCopyBaseStation.sqb");
File.Copy(Path.Combine(TestContext.TestDeploymentDir, "BaseStation.sqb"), _EmptyDatabaseFileName, true);
_Database = Factory.Singleton.Resolve<IBaseStationDatabase>();
_Database.FileName = _EmptyDatabaseFileName;
_Provider = new Mock<IBaseStationDatabaseProvider>() { DefaultValue = DefaultValue.Mock }.SetupAllProperties();
_Database.Provider = _Provider.Object;
_Provider.Setup(p => p.UtcNow).Returns(DateTime.UtcNow);
_ConnectionStringBuilder = new SQLiteConnectionStringBuilder() { DataSource = _EmptyDatabaseFileName };
_Criteria = new SearchBaseStationCriteria() {
FromDate = DateTime.MinValue,
ToDate = DateTime.MaxValue,
};
_FileNameChangingEvent = new EventRecorder<EventArgs>();
_FileNameChangedEvent = new EventRecorder<EventArgs>();
}
public void SearchBaseStationCriteria_Constructor_Initialises_To_Known_State_And_Properties_Work()
{
var criteria = new SearchBaseStationCriteria();
TestUtilities.TestProperty(criteria, "Callsign", null, "Ab");
TestUtilities.TestProperty(criteria, "Country", null, "Hh");
TestUtilities.TestProperty(criteria, "FromDate", DateTime.MinValue, DateTime.Now);
TestUtilities.TestProperty(criteria, "Icao", null, "Gg");
TestUtilities.TestProperty(criteria, "IsEmergency", false);
TestUtilities.TestProperty(criteria, "Operator", null, "Jj");
TestUtilities.TestProperty(criteria, "Registration", null, "Ll");
TestUtilities.TestProperty(criteria, "ToDate", DateTime.MinValue, DateTime.Now);
}
/// <summary>
/// See interface docs.
/// </summary>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
/// <returns></returns>
public int GetCountOfFlightsForAircraft(BaseStationAircraft aircraft, SearchBaseStationCriteria criteria)
{
if(criteria == null) throw new ArgumentNullException("criteria");
FlightsTable.NormaliseCriteria(criteria);
int result = 0;
if(aircraft != null) {
lock(_ConnectionLock) {
OpenConnection();
if(_Connection != null) result = _FlightTable.GetCountForAircraft(_Connection, null, _DatabaseLog, aircraft, criteria);
}
}
return result;
}
/// <summary>
/// See interface docs.
/// </summary>
/// <param name="criteria"></param>
/// <param name="fromRow"></param>
/// <param name="toRow"></param>
/// <param name="sortField1"></param>
/// <param name="sortField1Ascending"></param>
/// <param name="sortField2"></param>
/// <param name="sortField2Ascending"></param>
/// <returns></returns>
public List<BaseStationFlight> GetFlights(SearchBaseStationCriteria criteria, int fromRow, int toRow, string sortField1, bool sortField1Ascending, string sortField2, bool sortField2Ascending)
{
if(criteria == null) throw new ArgumentNullException("criteria");
FlightsTable.NormaliseCriteria(criteria);
List<BaseStationFlight> result = new List<BaseStationFlight>();
lock(_ConnectionLock) {
OpenConnection();
if(_Connection != null) result = _FlightTable.GetFlights(_Connection, null, _DatabaseLog, criteria, fromRow, toRow, sortField1, sortField1Ascending, sortField2, sortField2Ascending);
}
return result;
}
/// <summary>
/// Returns the count of flight records that match the criteria passed across.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="criteria"></param>
/// <returns></returns>
public int GetCount(IDbConnection connection, IDbTransaction transaction, TextWriter log, SearchBaseStationCriteria criteria)
{
return DoGetCount(connection, transaction, log, null, criteria);
}
private void LimitDatesWhenNoStrongCriteriaPresent(SearchBaseStationCriteria criteria, bool isInternetRequest)
{
if(String.IsNullOrEmpty(criteria.Callsign) && String.IsNullOrEmpty(criteria.Registration) && String.IsNullOrEmpty(criteria.Icao)) {
const int defaultDayCount = 7;
DateTime now = Provider.UtcNow;
bool fromIsMissing = criteria.FromDate.Year == DateTime.MinValue.Year;
bool toIsMissing = criteria.ToDate.Year == DateTime.MaxValue.Year;
if(fromIsMissing && toIsMissing) {
criteria.ToDate = now.Date;
toIsMissing = false;
}
if(fromIsMissing) criteria.FromDate = criteria.ToDate.AddDays(-defaultDayCount);
else if(toIsMissing) criteria.ToDate = criteria.FromDate.AddDays(defaultDayCount);
else if(isInternetRequest && (criteria.ToDate - criteria.FromDate).TotalDays > defaultDayCount) criteria.ToDate = criteria.FromDate.AddDays(defaultDayCount);
}
}
/// <summary>
/// Returns true if the criteria attempts to restrict the search to a single aircraft.
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
private bool FilterByAircraftFirst(SearchBaseStationCriteria criteria)
{
return !String.IsNullOrEmpty(criteria.Icao) ||
!String.IsNullOrEmpty(criteria.Registration);
}
public List<BaseStationFlight> GetFlights(SearchBaseStationCriteria criteria, int fromRow, int toRow, string sortField1, bool sortField1Ascending, string sortField2, bool sortField2Ascending)
{
return new List<BaseStationFlight>();
}
/// <summary>
/// Performs the work for the Get***Flights methods.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
/// <param name="fromRow"></param>
/// <param name="toRow"></param>
/// <param name="sort1"></param>
/// <param name="sort1Ascending"></param>
/// <param name="sort2"></param>
/// <param name="sort2Ascending"></param>
/// <returns></returns>
private List<BaseStationFlight> DoGetFlights(IDbConnection connection, IDbTransaction transaction, TextWriter log, BaseStationAircraft aircraft, SearchBaseStationCriteria criteria, int fromRow, int toRow, string sort1, bool sort1Ascending, string sort2, bool sort2Ascending)
{
List<BaseStationFlight> result = new List<BaseStationFlight>();
sort1 = ConvertSortFieldToColumnName(sort1);
sort2 = ConvertSortFieldToColumnName(sort2);
StringBuilder commandText = new StringBuilder();
commandText.Append(CreateSelectFrom(aircraft, criteria, false));
string criteriaText = GetFlightsCriteria(aircraft, criteria);
if(criteriaText.Length > 0) commandText.AppendFormat(" WHERE {0}", criteriaText);
if(sort1 != null || sort2 != null) {
commandText.Append(" ORDER BY ");
if(sort1 != null) commandText.AppendFormat("{0} {1}", sort1, sort1Ascending ? "ASC" : "DESC");
if(sort2 != null) commandText.AppendFormat("{0}{1} {2}", sort1 == null ? "" : ", ", sort2, sort2Ascending ? "ASC" : "DESC");
}
commandText.Append(" LIMIT ? OFFSET ?");
bool decodeFlightsFirst = aircraft != null || !FilterByAircraftFirst(criteria);
using(IDbCommand command = connection.CreateCommand()) {
command.Transaction = transaction;
command.CommandText = commandText.ToString();
int limit = toRow == -1 || toRow < fromRow ? int.MaxValue : (toRow - Math.Max(0, fromRow)) + 1;
int offset = fromRow < 0 ? 0 : fromRow;
AddFlightsCriteriaParameters(command, aircraft, criteria);
Sql.AddParameter(command, limit);
Sql.AddParameter(command, offset);
Sql.LogCommand(log, command);
using(IDataReader reader = command.ExecuteReader()) {
Dictionary<int, BaseStationAircraft> aircraftMap = new Dictionary<int,BaseStationAircraft>();
while(reader.Read()) {
int ordinal = 0;
BaseStationFlight flight = DecodeFullFlight(reader, ref ordinal);
if(aircraft != null) flight.Aircraft = aircraft;
else {
if(aircraftMap.ContainsKey(flight.AircraftID)) flight.Aircraft = aircraftMap[flight.AircraftID];
else {
flight.Aircraft = AircraftTable.DecodeFullAircraft(reader, ref ordinal);
aircraftMap.Add(flight.AircraftID, flight.Aircraft);
}
}
result.Add(flight);
}
}
}
return result;
}
/// <summary>
/// Builds a select statement from criteria.
/// </summary>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
/// <param name="justCount"></param>
/// <returns></returns>
private string CreateSelectFrom(BaseStationAircraft aircraft, SearchBaseStationCriteria criteria, bool justCount)
{
StringBuilder result = new StringBuilder();
if(aircraft != null) {
result.AppendFormat("SELECT {0} FROM [Flights] AS f", justCount ? "COUNT(*)" : FieldList());
} else {
result.AppendFormat("SELECT {0}{1}{2} FROM ",
justCount ? "COUNT(*)" : FieldList(),
justCount ? "" : ", ",
justCount ? "" : AircraftTable.FieldList());
if(FilterByAircraftFirst(criteria)) result.Append("[Aircraft] AS a LEFT JOIN [Flights] AS f");
else result.Append("[Flights] AS f LEFT JOIN [Aircraft] AS a");
result.Append(" ON (a.[AircraftID] = f.[AircraftID])");
}
return result.ToString();
}
/// <summary>
/// Performs the work for the Get***Count methods.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
/// <returns></returns>
private int DoGetCount(IDbConnection connection, IDbTransaction transaction, TextWriter log, BaseStationAircraft aircraft, SearchBaseStationCriteria criteria)
{
int result = 0;
StringBuilder commandText = new StringBuilder();
commandText.Append(CreateSelectFrom(aircraft, criteria, true));
string criteriaText = GetFlightsCriteria(aircraft, criteria);
if(criteriaText.Length > 0) commandText.AppendFormat(" WHERE {0}", criteriaText);
using(IDbCommand command = connection.CreateCommand()) {
command.Transaction = transaction;
command.CommandText = commandText.ToString();
AddFlightsCriteriaParameters(command, aircraft, criteria);
Sql.LogCommand(log, command);
result = (int)(long)command.ExecuteScalar();
}
return result;
}
/// <summary>
/// Returns an ordered subset of flight records for a single aircraft that match the criteria passed across.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
/// <param name="fromRow"></param>
/// <param name="toRow"></param>
/// <param name="sort1"></param>
/// <param name="sort1Ascending"></param>
/// <param name="sort2"></param>
/// <param name="sort2Ascending"></param>
/// <returns></returns>
public List<BaseStationFlight> GetForAircraft(IDbConnection connection, IDbTransaction transaction, TextWriter log, BaseStationAircraft aircraft, SearchBaseStationCriteria criteria, int fromRow, int toRow, string sort1, bool sort1Ascending, string sort2, bool sort2Ascending)
{
return DoGetFlights(connection, transaction, log, aircraft, criteria, fromRow, toRow, sort1, sort1Ascending, sort2, sort2Ascending);
}
/// <summary>
/// Returns a count of flight records for a single aircraft that match the criteria passed across.
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="log"></param>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
/// <returns></returns>
public int GetCountForAircraft(IDbConnection connection, IDbTransaction transaction, TextWriter log, BaseStationAircraft aircraft, SearchBaseStationCriteria criteria)
{
return DoGetCount(connection, transaction, log, aircraft, criteria);
}
public List<BaseStationFlight> GetFlightsForAircraft(BaseStationAircraft aircraft, SearchBaseStationCriteria criteria, int fromRow, int toRow, string sort1, bool sort1Ascending, string sort2, bool sort2Ascending)
{
return new List<BaseStationFlight>();
}
/// <summary>
/// Returns the WHERE portion of an SQL statement contains the fields describing the criteria passed across.
/// </summary>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
/// <returns></returns>
private string GetFlightsCriteria(BaseStationAircraft aircraft, SearchBaseStationCriteria criteria)
{
StringBuilder result = new StringBuilder();
if(aircraft != null) AddCriteria(result, "f.[AircraftID]", " = ?");
if(!String.IsNullOrEmpty(criteria.Callsign)) AddCriteria(result, "f.[Callsign]", " = ?");
if(criteria.FromDate.Year != DateTime.MinValue.Year) AddCriteria(result, "f.[StartTime]", " >= ?");
if(criteria.ToDate.Year != DateTime.MaxValue.Year) AddCriteria(result, "f.[StartTime]", " <= ?");
if(!String.IsNullOrEmpty(criteria.Operator)) AddCriteria(result, "a.[RegisteredOwners]", " = ?");
if(!String.IsNullOrEmpty(criteria.Registration)) AddCriteria(result, "a.[Registration]", " = ?");
if(!String.IsNullOrEmpty(criteria.Icao)) AddCriteria(result, "a.[ModeS]", " = ?");
if(!String.IsNullOrEmpty(criteria.Country)) AddCriteria(result, "a.[ModeSCountry]", " = ?");
if(criteria.IsEmergency) AddCriteria(result, "f.[HadEmergency]", " <> 0");
return result.ToString();
}
public int GetCountOfFlightsForAircraft(BaseStationAircraft aircraft, SearchBaseStationCriteria criteria)
{
return 0;
}
/// <summary>
/// Attaches ADO.NET parameters to the command passed across to carry the criteria values to the database engine.
/// </summary>
/// <param name="command"></param>
/// <param name="aircraft"></param>
/// <param name="criteria"></param>
private void AddFlightsCriteriaParameters(IDbCommand command, BaseStationAircraft aircraft, SearchBaseStationCriteria criteria)
{
if(aircraft != null) Sql.AddParameter(command, aircraft.AircraftID);
if(!String.IsNullOrEmpty(criteria.Callsign)) Sql.AddParameter(command, criteria.Callsign);
if(criteria.FromDate.Year != DateTime.MinValue.Year) Sql.AddParameter(command, criteria.FromDate);
if(criteria.ToDate.Year != DateTime.MaxValue.Year) Sql.AddParameter(command, criteria.ToDate);
if(!String.IsNullOrEmpty(criteria.Operator)) Sql.AddParameter(command, criteria.Operator);
if(!String.IsNullOrEmpty(criteria.Registration)) Sql.AddParameter(command, criteria.Registration);
if(!String.IsNullOrEmpty(criteria.Icao)) Sql.AddParameter(command, criteria.Icao);
if(!String.IsNullOrEmpty(criteria.Country)) Sql.AddParameter(command, criteria.Country);
}
public int GetCountOfFlights(SearchBaseStationCriteria criteria)
{
return 0;
}
/// <summary>
/// Normalises strings in the criteria object.
/// </summary>
/// <remarks>
/// Previous versions of VRS would implement case insensitivity in searches by using COLLATE NOCASE
/// statements in the SQL. This had the unfortunate side-effect of producing very slow searches, so
/// it has been removed. All searches are consequently case-sensitive but it is assumed that some
/// fields are always written in upper case. This method converts the criteria for those fields to
/// upper-case.
/// </remarks>
/// <param name="criteria"></param>
public static void NormaliseCriteria(SearchBaseStationCriteria criteria)
{
if(criteria.Callsign != null) criteria.Callsign = criteria.Callsign.ToUpper(CultureInfo.InvariantCulture);
if(criteria.Icao != null) criteria.Icao = criteria.Icao.ToUpper(CultureInfo.InvariantCulture);
if(criteria.Registration != null) criteria.Registration = criteria.Registration.ToUpper(CultureInfo.InvariantCulture);
}
