338 lines
15 KiB
C#
338 lines
15 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using MySqlConnector;
|
|
using skyscraper5.Dvb.Descriptors;
|
|
using skyscraper5.Dvb.Psi.Model;
|
|
using skyscraper5.Teletext;
|
|
using skyscraper5.Dvb.DataBroadcasting.IntModel;
|
|
using System.IO;
|
|
using skyscraper5.Skyscraper;
|
|
using skyscraper5.Skyscraper.Headless;
|
|
using skyscraper5.Skyscraper.Equipment;
|
|
using skyscraper5.Mpeg2.Psi.Model;
|
|
using skyscraper5.Skyscraper.Gps;
|
|
using skyscraper5.src.Skyscraper.FrequencyListGenerator;
|
|
using skyscraper5.Skyscraper.IO.CrazycatStreamReader;
|
|
using skyscraper5.Skyscraper.Scraper.Storage.Utilities;
|
|
using skyscraper5.src.InteractionChannel.Model;
|
|
using skyscraper5.src.InteractionChannel.Model.Descriptors;
|
|
using System.Net.NetworkInformation;
|
|
using System.Net;
|
|
using skyscraper5.src.Skyscraper.Scraper.Dns;
|
|
using skyscraper8.Skyscraper.Scraper.Storage;
|
|
|
|
namespace skyscraper5.Data.MySql
|
|
{
|
|
public partial class MySqlDataStorage : DataStorage
|
|
{
|
|
private void InsertBatCountryAvailability(MySqlConnection connection, MySqlTransaction transaction, BatBouquet batBouquet)
|
|
{
|
|
if (batBouquet.CountryAvailabilityDictionary == null)
|
|
return;
|
|
|
|
if (batBouquet.CountryAvailabilityDictionary.Count == 0)
|
|
return;
|
|
|
|
MySqlCommand command = connection.CreateCommand();
|
|
command.Transaction = transaction;
|
|
command.CommandText =
|
|
"INSERT INTO dvb_bat_country_availability (id, country, availability) VALUES (@id, @country, @availability)";
|
|
command.Parameters.AddWithValue("@id", batBouquet.BouquetId);
|
|
command.Parameters.Add("@country", MySqlDbType.VarChar);
|
|
command.Parameters.Add("@availability", MySqlDbType.Bool);
|
|
foreach (KeyValuePair<string, bool> keyValuePair in batBouquet.CountryAvailabilityDictionary)
|
|
{
|
|
command.Parameters["@country"].Value = keyValuePair.Key;
|
|
command.Parameters["@availability"].Value = keyValuePair.Value;
|
|
command.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
private void InsertBatMultilingualName(MySqlConnection connection, MySqlTransaction transaction, BatBouquet batBouquet)
|
|
{
|
|
if (batBouquet.MultilingualBouquetName == null)
|
|
return;
|
|
|
|
if (batBouquet.MultilingualBouquetName.MultilingualBouquetName == null)
|
|
return;
|
|
|
|
if (batBouquet.MultilingualBouquetName.MultilingualBouquetName.Count == 0)
|
|
return;
|
|
|
|
MySqlCommand command = connection.CreateCommand();
|
|
command.Transaction = transaction;
|
|
command.CommandText =
|
|
"INSERT INTO dvb_bat_multilingual_bouquet_name (id, lang, name) VALUES (@id, @lang, @name)";
|
|
command.Parameters.AddWithValue("@id", batBouquet.BouquetId);
|
|
command.Parameters.Add("@lang", MySqlDbType.VarChar);
|
|
command.Parameters.Add("@name", MySqlDbType.VarChar);
|
|
foreach (KeyValuePair<string, string> keyValuePair in batBouquet.MultilingualBouquetName
|
|
.MultilingualBouquetName)
|
|
{
|
|
command.Parameters["@lang"].Value = keyValuePair.Key;
|
|
command.Parameters["@name"].Value = keyValuePair.Value;
|
|
command.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
private BatBouquet GetBatBouquet(MySqlConnection connection, ushort newerBouquetId)
|
|
{
|
|
MySqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "SELECT * FROM dvb_bat WHERE id = @id";
|
|
command.Parameters.AddWithValue("@id", newerBouquetId);
|
|
MySqlDataReader dataReader = command.ExecuteReader();
|
|
BatBouquet result = null;
|
|
if (dataReader.Read())
|
|
{
|
|
ushort id = dataReader.GetUInt16(0);
|
|
result = new BatBouquet(id);
|
|
DateTime dateAdded = dataReader.GetDateTime(1);
|
|
|
|
if (!dataReader.IsDBNull(2))
|
|
result.BouquetName = dataReader.GetString(2);
|
|
|
|
if (!dataReader.IsDBNull(3))
|
|
{
|
|
Guid guid = Guid.Parse(dataReader.GetString(3));
|
|
}
|
|
|
|
if (!dataReader.IsDBNull(4))
|
|
result.PrivateDataSpecifier = dataReader.GetUInt32(4);
|
|
|
|
if (!dataReader.IsDBNull(5))
|
|
result.UriLinkageType = dataReader.GetByte(5);
|
|
|
|
if (!dataReader.IsDBNull(6))
|
|
result.Uri = dataReader.GetString(6);
|
|
|
|
if (!dataReader.IsDBNull(7))
|
|
result.MinPollingInterval = dataReader.GetUInt16(7);
|
|
|
|
if (!dataReader.IsDBNull(8))
|
|
result.ControlRemoteAccessOverInternet = dataReader.GetInt32(8);
|
|
|
|
if (!dataReader.IsDBNull(9))
|
|
result.DoNotApplyRevocation = dataReader.GetBoolean(9);
|
|
|
|
if (!dataReader.IsDBNull(10))
|
|
result.DoNotScramble = dataReader.GetBoolean(10);
|
|
}
|
|
dataReader.Close();
|
|
return result;
|
|
}
|
|
|
|
private struct BatTransportStreamCoordinate
|
|
{
|
|
public ushort BouquetId { get; }
|
|
public ushort NetworkId { get; }
|
|
public ushort TsId { get; }
|
|
|
|
public BatTransportStreamCoordinate(ushort bouquetId, ushort networkId, ushort tsId)
|
|
{
|
|
BouquetId = bouquetId;
|
|
NetworkId = networkId;
|
|
TsId = tsId;
|
|
}
|
|
|
|
public bool Equals(BatTransportStreamCoordinate other)
|
|
{
|
|
return BouquetId == other.BouquetId && NetworkId == other.NetworkId && TsId == other.TsId;
|
|
}
|
|
|
|
public override bool Equals(object obj)
|
|
{
|
|
return obj is BatTransportStreamCoordinate other && Equals(other);
|
|
}
|
|
|
|
public override int GetHashCode()
|
|
{
|
|
return HashCode.Combine(BouquetId, NetworkId, TsId);
|
|
}
|
|
}
|
|
|
|
private HashSet<BatTransportStreamCoordinate> _batTransportStreamCoordinates;
|
|
|
|
private void InsertBatTransportStreamCountryAvailability(MySqlTransaction transaction, ushort batBouquetBouquetId, BatTransportStream child)
|
|
{
|
|
if (child.CountryAvailability == null)
|
|
return;
|
|
|
|
if (child.CountryAvailability.Count == 0)
|
|
return;
|
|
|
|
MySqlCommand command = transaction.Connection.CreateCommand();
|
|
command.Transaction = transaction;
|
|
command.CommandText =
|
|
"INSERT INTO dvb_bat_transport_stream_country_availability (bouquet_id, original_network_id, transport_stream_id, country, availability) " +
|
|
"VALUES (@bouquet_id, @original_network_id, @transport_stream_id, @country, @availability)";
|
|
command.Parameters.AddWithValue("@bouquet_id", batBouquetBouquetId);
|
|
command.Parameters.AddWithValue("@original_network_id", child.OriginalNetworkId);
|
|
command.Parameters.AddWithValue("@transport_stream_id", child.TransportStreamId);
|
|
command.Parameters.Add("@country", MySqlDbType.VarChar);
|
|
command.Parameters.Add("@availability", MySqlDbType.Bool);
|
|
foreach (KeyValuePair<string, bool> valuePair in child.CountryAvailability)
|
|
{
|
|
command.Parameters["@country"].Value = valuePair.Key;
|
|
command.Parameters["@availability"].Value = valuePair.Value;
|
|
command.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
private void InsertBatTransportStreamServiceList(MySqlTransaction transaction, ushort batBouquetBouquetId, BatTransportStream child)
|
|
{
|
|
if (child.ServiceList == null)
|
|
return;
|
|
|
|
if (child.ServiceList.Count == 0)
|
|
return;
|
|
|
|
MySqlCommand command = transaction.Connection.CreateCommand();
|
|
command.Transaction = transaction;
|
|
command.CommandText =
|
|
"INSERT INTO dvb_bat_transport_stream_service_list (bouquet_id, original_network_id, transport_stream_id, service_id, service_type) " +
|
|
"VALUES (@bouquet_id, @original_network_id, @transport_stream_id, @service_id, @service_type)";
|
|
command.Parameters.AddWithValue("@bouquet_id", batBouquetBouquetId);
|
|
command.Parameters.AddWithValue("@original_network_id", child.OriginalNetworkId);
|
|
command.Parameters.AddWithValue("@transport_stream_id", child.TransportStreamId);
|
|
command.Parameters.Add("@service_id", MySqlDbType.Int32);
|
|
command.Parameters.Add("@service_type", MySqlDbType.Int32);
|
|
|
|
foreach (ServiceListDescriptor.Service service in child.ServiceList)
|
|
{
|
|
command.Parameters["@service_id"].Value = service.ServiceId;
|
|
command.Parameters["@service_type"].Value = (int)service.ServiceType;
|
|
command.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
public bool TestForBatBouquet(BatBouquet batBouquet)
|
|
{
|
|
using (MySqlConnection connection = new MySqlConnection(_mcsb.ToString()))
|
|
{
|
|
connection.Open();
|
|
MySqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "SELECT dateadded FROM dvb_bat WHERE id = @id";
|
|
command.Parameters.AddWithValue("@id", batBouquet.BouquetId);
|
|
MySqlDataReader dataReader = command.ExecuteReader();
|
|
bool result = dataReader.Read();
|
|
dataReader.Close();
|
|
connection.Close();
|
|
return result;
|
|
}
|
|
}
|
|
|
|
public bool UpdateBatBouquet(BatBouquet newer)
|
|
{
|
|
using (MySqlConnection connection = new MySqlConnection(_mcsb.ToString()))
|
|
{
|
|
connection.Open();
|
|
bool result = false;
|
|
BatBouquet older = GetBatBouquet(connection, newer.BouquetId);
|
|
if (older.NeedUpdate(newer))
|
|
{
|
|
MySqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"UPDATE dvb_bat SET name = @name, private_dat_specifier = @private_dat_specifier, uri_linkage_type = @uri_linkage_type, url = @url, min_polling_interval =@min_polling_interval, control_remote_access_over_internet = @control_remote_access_over_internet, do_not_apply_revocation = @do_not_apply_revocation, do_not_scramble = @do_not_scramble WHERE id = @id";
|
|
command.Parameters.AddWithValue("@id", newer.BouquetId);
|
|
command.Parameters.AddWithValue("@name", newer.BouquetName);
|
|
command.Parameters.AddWithValue("@private_dat_specifier", newer.PrivateDataSpecifier);
|
|
command.Parameters.AddWithValue("@uri_linkage_type", newer.UriLinkageType);
|
|
command.Parameters.AddWithValue("@url", newer.Uri);
|
|
command.Parameters.AddWithValue("@min_polling_interval", newer.MinPollingInterval);
|
|
command.Parameters.AddWithValue("@control_remote_access_over_internet", newer.ControlRemoteAccessOverInternet);
|
|
command.Parameters.AddWithValue("@do_not_apply_revocation", newer.DoNotApplyRevocation);
|
|
command.Parameters.AddWithValue("@do_not_scramble", newer.DoNotScramble);
|
|
command.ExecuteNonQuery();
|
|
result = true;
|
|
}
|
|
connection.Close();
|
|
return result;
|
|
}
|
|
}
|
|
|
|
public void StoreBatBouquet(BatBouquet batBouquet)
|
|
{
|
|
bool hasLinkages = HasLinkages(batBouquet.Linkages);
|
|
Guid uuid = hasLinkages ? Guid.NewGuid() : Guid.Empty;
|
|
|
|
using (MySqlConnection connection = new MySqlConnection(_mcsb.ToString()))
|
|
{
|
|
connection.Open();
|
|
MySqlTransaction transaction = connection.BeginTransaction();
|
|
MySqlCommand command = connection.CreateCommand();
|
|
command.Transaction = transaction;
|
|
command.CommandText =
|
|
"INSERT INTO dvb_bat (id, name, uuid, private_dat_specifier, uri_linkage_type, url, min_polling_interval, control_remote_access_over_internet, do_not_apply_revocation, do_not_scramble) " +
|
|
"VALUES " +
|
|
"(@id, @name, @uuid, @private_dat_specifier, @uri_linkage_type, @url, @min_polling_interval, @control_remote_access_over_internet, @do_not_apply_revocation, @do_not_scramble)";
|
|
command.Parameters.AddWithValue("@id", batBouquet.BouquetId);
|
|
command.Parameters.AddWithValue("@name", batBouquet.BouquetName);
|
|
if (hasLinkages)
|
|
command.Parameters.AddWithValue("@uuid", uuid.ToString());
|
|
else
|
|
command.Parameters.AddWithValue("@uuid", DBNull.Value);
|
|
command.Parameters.AddWithValue("@private_dat_specifier", batBouquet.PrivateDataSpecifier);
|
|
command.Parameters.AddWithValue("@uri_linkage_type", batBouquet.UriLinkageType);
|
|
command.Parameters.AddWithValue("@url", batBouquet.Uri);
|
|
command.Parameters.AddWithValue("@min_polling_interval", batBouquet.MinPollingInterval);
|
|
command.Parameters.AddWithValue("@control_remote_access_over_internet", batBouquet.ControlRemoteAccessOverInternet);
|
|
command.Parameters.AddWithValue("@do_not_apply_revocation", batBouquet.DoNotApplyRevocation);
|
|
command.Parameters.AddWithValue("@do_not_scramble", batBouquet.DoNotScramble);
|
|
command.ExecuteNonQuery();
|
|
|
|
if (hasLinkages)
|
|
InsertSdtLinkages(connection, uuid, batBouquet.Linkages, transaction);
|
|
|
|
InsertBatCountryAvailability(connection, transaction, batBouquet);
|
|
InsertBatMultilingualName(connection, transaction, batBouquet);
|
|
transaction.Commit();
|
|
connection.Close();
|
|
}
|
|
}
|
|
|
|
public bool TestForBatTransportStream(ushort batBouquetBouquetId, BatTransportStream child)
|
|
{
|
|
if (_batTransportStreamCoordinates == null)
|
|
_batTransportStreamCoordinates = new HashSet<BatTransportStreamCoordinate>();
|
|
|
|
BatTransportStreamCoordinate coordinate = new BatTransportStreamCoordinate(batBouquetBouquetId, child.OriginalNetworkId, child.TransportStreamId);
|
|
if (_batTransportStreamCoordinates.Contains(coordinate))
|
|
return true;
|
|
|
|
using (MySqlConnection connection = new MySqlConnection(_mcsb.ToString()))
|
|
{
|
|
connection.Open();
|
|
MySqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"SELECT dateadded FROM dvb_bat_transport_stream WHERE bouquet_id = @bouquet_id AND original_network_id = @original_network_id AND transport_stream_id = @transport_stream_id";
|
|
command.Parameters.AddWithValue("@bouquet_id", batBouquetBouquetId);
|
|
command.Parameters.AddWithValue("@original_network_id", child.OriginalNetworkId);
|
|
command.Parameters.AddWithValue("@transport_stream_id", child.TransportStreamId);
|
|
MySqlDataReader dataReader = command.ExecuteReader();
|
|
bool result = dataReader.Read();
|
|
if (result)
|
|
_batTransportStreamCoordinates.Add(coordinate);
|
|
dataReader.Close();
|
|
return result;
|
|
}
|
|
}
|
|
|
|
public bool UpdateBatTransportStream(ushort batBouquetBouquetId, BatTransportStream child)
|
|
{
|
|
//It's very unlikely that we'll need to Update BAT TS Info, as these don't contain much.
|
|
return false;
|
|
}
|
|
|
|
public void StoreBatTransportStream(ushort batBouquetBouquetId, BatTransportStream child)
|
|
{
|
|
BatTransportStreamCoordinate coordinate = new BatTransportStreamCoordinate(batBouquetBouquetId, child.OriginalNetworkId, child.TransportStreamId);
|
|
_batTransportStreamCoordinates.Add(coordinate);
|
|
EnqueueSpeedhack(SpeedhackType.InsertBatTs, batBouquetBouquetId, child);
|
|
}
|
|
|
|
}
|
|
}
|