skyscraper8/DataTableStorages/skyscraper5.Data.PostgreSql/Tot.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;
using NpgsqlTypes;
using skyscraper5.Dvb.Descriptors;
using skyscraper5.Skyscraper.Scraper.Storage.Utilities;

namespace skyscraper5.Data.PostgreSql
{
	public partial class PostgresqlDataStore
	{
		private Dictionary<DatabaseKeyTot, DateTime> _knownTots;
		public bool UpdateTimeOffsetTable(int currentNetworkId, int currentTransportStreamId, DateTime utcTime, LocalTimeOffsetDescriptor ltod)
		{
			if (_knownTots == null)
				_knownTots = new Dictionary<DatabaseKeyTot, DateTime>();
			if (ltod == null)
				return false;

			bool result = false;
			foreach (LocalTimeOffsetDescriptor.LocalTime offset in ltod.LocalTimeOffsets)
			{
				DatabaseKeyTot key = new DatabaseKeyTot(currentNetworkId, currentTransportStreamId, offset.CountryCode, (int)offset.LocalTimeOffset.TotalSeconds, offset.LocalTimeOffsetPolarity);
				if (_knownTots.ContainsKey(key))
				{
					//Already cached
					DateTime inCache = _knownTots[key];
					if (inCache < utcTime)
					{
						//We are in the future
						EnqueueTask(x => WriteUpdateTot(x,currentNetworkId,currentTransportStreamId,utcTime, offset));
						_knownTots[key] = utcTime;
						result = true;
					}
					else
					{
						//We are in the past, nothing to do.
						continue;
					}
				}
				else
				{
					//not yet cached
					DateTime? inDb = SelectTot(currentNetworkId, currentTransportStreamId, offset.CountryCode, (int)offset.LocalTimeOffset.TotalSeconds, offset.LocalTimeOffsetPolarity);
					if (inDb.HasValue)
					{
						if (inDb < utcTime)
						{
							//Database time in the past, need to update
							EnqueueTask(x => WriteUpdateTot(x, currentNetworkId, currentTransportStreamId, utcTime, offset));
							_knownTots[key] = utcTime;
							result = true;
						}
						else
						{
							//Database time in the future, no update needed
							_knownTots[key] = inDb.Value;
							continue;
						}
					}
					else
					{
						//not yet in database, need to insert
						EnqueueTask(x => WriteTot(x, currentNetworkId, currentTransportStreamId, utcTime, offset));
						_knownTots[key] = utcTime;
						result = true;
					}
				}
			}

			EnqueueTask(CommitTransaction);
			return result;
		}

		private void WriteTot(NpgsqlConnection connection, int currentNetworkId, int currentTransportStreamId, DateTime utcTime, LocalTimeOffsetDescriptor.LocalTime offset)
		{
			NpgsqlCommand command = connection.CreateCommand();
			command.CommandText =
				"insert into dvb_tot (cnid, ctsid, country_code, utc, country_region_id, local_time_offset_polarity,\r\n                     local_time_offset, time_of_change, next_time_offset) " +
				"values (@cnid, @ctsid, @country_code, @utc, @country_region_id, @local_time_offset_polarity,\r\n                     @local_time_offset, @time_of_change, @next_time_offset)";
			command.Parameters.AddParameter("@cnid", NpgsqlDbType.Integer, (ushort)currentNetworkId);
			command.Parameters.AddParameter("@ctsid", NpgsqlDbType.Integer, (ushort)currentTransportStreamId);
			command.Parameters.AddParameter("@country_code", NpgsqlDbType.Varchar, offset.CountryCode.Trim('\0'));
			command.Parameters.AddParameter("@utc", NpgsqlDbType.Timestamp, utcTime);
			command.Parameters.AddParameter("@country_region_id", NpgsqlDbType.Integer, offset.CountryRegionId);
			command.Parameters.AddParameter("@local_time_offset_polarity", NpgsqlDbType.Boolean, offset.LocalTimeOffsetPolarity);
			command.Parameters.AddParameter("@local_time_offset", NpgsqlDbType.Integer, (int)offset.LocalTimeOffset.TotalSeconds);
			command.Parameters.AddParameter("@time_of_change", NpgsqlDbType.Timestamp, offset.TimeOfChange);
			command.Parameters.AddParameter("@next_time_offset", NpgsqlDbType.Integer, offset.NextTimeOffset.TotalSeconds);
			command.ExecuteNonQuery();
		}

		private void WriteUpdateTot(NpgsqlConnection connection, int currentNetworkId, int currentTransportStreamId, DateTime utcTime, LocalTimeOffsetDescriptor.LocalTime offset)
		{
			NpgsqlCommand command = connection.CreateCommand();
			command.CommandText =
				"UPDATE dvb_tot\r\n" +
				"SET utc = @utc," +
				"    country_region_id = @country_region_id," +
				"    time_of_change = @time_of_change," +
				"    next_time_offset = @next_time_offset," +
				"    updated_counter = updated_counter + 1," +
				"    updated_timestamp = CURRENT_TIMESTAMP " +
				"WHERE cnid = @cnid " +
				"AND ctsid = @ctsid " +
				"AND country_region_id = @country_region_id " +
                "AND local_time_offset_polarity = @local_time_offset_polarity " +
                "AND local_time_offset = @local_time_offset";
			command.Parameters.AddParameter("@cnid", NpgsqlDbType.Integer, (ushort)currentNetworkId);
			command.Parameters.AddParameter("@ctsid", NpgsqlDbType.Integer, (ushort)currentTransportStreamId);
			command.Parameters.AddParameter("@country_code", NpgsqlDbType.Varchar, offset.CountryCode);
			command.Parameters.AddParameter("@utc", NpgsqlDbType.Timestamp, utcTime);
			command.Parameters.AddParameter("@country_region_id", NpgsqlDbType.Integer, offset.CountryRegionId);
			command.Parameters.AddParameter("@local_time_offset_polarity", NpgsqlDbType.Boolean, offset.LocalTimeOffsetPolarity);
			command.Parameters.AddParameter("@local_time_offset", NpgsqlDbType.Integer, (int)offset.LocalTimeOffset.TotalSeconds);
			command.Parameters.AddParameter("@time_of_change", NpgsqlDbType.Timestamp, offset.TimeOfChange);
			command.Parameters.AddParameter("@next_time_offset", NpgsqlDbType.Integer, offset.NextTimeOffset.TotalSeconds);
			command.ExecuteNonQuery();
		}

		private DateTime? SelectTot(int currentNetworkId, int currentTransportStreamId, string countyCode, int totalSeconds, bool offsetLocalTimeOffsetPolarity)
		{
			DateTime? result = null;
			using (NpgsqlConnection conn = new NpgsqlConnection(connectionStringBuilder.ToString()))
			{
				conn.Open();
				NpgsqlCommand command = conn.CreateCommand();
				command.CommandText =
					"SELECT utc FROM dvb_tot WHERE cnid = @cnid AND ctsid = @ctsid AND country_code = @country_code AND local_time_offset = @local_time_offset AND local_time_offset_polarity = @local_time_offset_polarity";
				command.Parameters.AddParameter("@cnid", NpgsqlDbType.Integer, currentNetworkId);
				command.Parameters.AddParameter("@ctsid", NpgsqlDbType.Integer, currentTransportStreamId);
				command.Parameters.AddParameter("@country_code", NpgsqlDbType.Varchar, countyCode.Trim('\0'));
				command.Parameters.AddParameter("@local_time_offset", NpgsqlDbType.Integer, totalSeconds);
				command.Parameters.AddParameter("@local_time_offset_polarity", NpgsqlDbType.Boolean, offsetLocalTimeOffsetPolarity);
				NpgsqlDataReader dataReader = command.ExecuteReader();
				if (dataReader.Read())
					result = dataReader.GetDateTime(0);
				dataReader.Close();
				command.Dispose();
				conn.Close();
			}

			return result;
		}
	}
}