674 lines
33 KiB
C#
674 lines
33 KiB
C#
using Npgsql;
|
|
using NpgsqlTypes;
|
|
using skyscraper8.DvbNip;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Net;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using System.Xml;
|
|
using Newtonsoft.Json;
|
|
using Npgsql.Replication.PgOutput.Messages;
|
|
using skyscraper8.Skyscraper.Scraper.Storage.Utilities;
|
|
|
|
namespace skyscraper5.Data.PostgreSql
|
|
{
|
|
public partial class PostgresqlDataStore
|
|
{
|
|
private Dictionary<NipActualCarrierInformation, int> _knownNipCarriers;
|
|
|
|
public bool DvbNipTestForCarrier(NipActualCarrierInformation currentCarrierInformation)
|
|
{
|
|
if (_knownNipCarriers != null)
|
|
{
|
|
if (_knownNipCarriers.ContainsKey(currentCarrierInformation))
|
|
return true;
|
|
}
|
|
|
|
bool result = false;
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "SELECT serial FROM dvbnip_carrier_information WHERE nid = @nid AND cid = @cid AND lid = @lid AND sid = @sid";
|
|
command.Parameters.AddWithValue("@nid", NpgsqlDbType.Integer, (int)currentCarrierInformation.NipNetworkId);
|
|
command.Parameters.AddWithValue("@cid", NpgsqlDbType.Integer, (int)currentCarrierInformation.NipCarrierId);
|
|
command.Parameters.AddWithValue("@lid", NpgsqlDbType.Integer, (int)currentCarrierInformation.NipLinkId);
|
|
command.Parameters.AddWithValue("@sid", NpgsqlDbType.Integer, (int)currentCarrierInformation.NipServiceId);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
if (dataReader.Read())
|
|
{
|
|
if (_knownNipCarriers == null)
|
|
_knownNipCarriers = new Dictionary<NipActualCarrierInformation, int>();
|
|
|
|
int value = dataReader.GetInt32(0);
|
|
_knownNipCarriers.Add(currentCarrierInformation, value);
|
|
result = true;
|
|
}
|
|
dataReader.Close();
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public void DvbNipInsertCarrier(NipActualCarrierInformation currentCarrierInformation)
|
|
{
|
|
EnqueueTask(x => DvbNipInsertCarrierEx(x, currentCarrierInformation));
|
|
EnqueueTask(CommitTransaction);
|
|
}
|
|
|
|
private void DvbNipInsertCarrierEx(NpgsqlConnection connection, NipActualCarrierInformation currentCarrierInformation)
|
|
{
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "INSERT INTO dvbnip_carrier_information VALUES (DEFAULT,DEFAULT,@nid,@cid,@lid,@sid,@spn)";
|
|
command.Parameters.AddWithValue("@nid", NpgsqlDbType.Integer, (int)currentCarrierInformation.NipNetworkId);
|
|
command.Parameters.AddWithValue("@cid", NpgsqlDbType.Integer, (int)currentCarrierInformation.NipCarrierId);
|
|
command.Parameters.AddWithValue("@lid", NpgsqlDbType.Integer, (int)currentCarrierInformation.NipLinkId);
|
|
command.Parameters.AddWithValue("@sid", NpgsqlDbType.Integer, (int)currentCarrierInformation.NipServiceId);
|
|
command.Parameters.AddWithValue("@spn", NpgsqlDbType.Text, currentCarrierInformation.NipStreamProviderName);
|
|
int a = command.ExecuteNonQuery();
|
|
if (a != 1)
|
|
throw new DataException(String.Format("Wanted to insert 1 row, got {0}", a));
|
|
command.Dispose();
|
|
}
|
|
|
|
private Dictionary<Tuple<ushort, ushort, ushort, ushort, uint>, DateTime> _dvbNipPrivateDataSpecifiers;
|
|
public bool DvbNipPrivateDataSpecifier(NipActualCarrierInformation currentCarrierInformation, DateTime versionUpdate,
|
|
uint privateDataSpecifier, List<string> privateDataSessions)
|
|
{
|
|
if (_dvbNipPrivateDataSpecifiers == null)
|
|
_dvbNipPrivateDataSpecifiers = new Dictionary<Tuple<ushort, ushort, ushort, ushort, uint>, DateTime>();
|
|
|
|
Tuple<ushort, ushort, ushort, ushort, uint> coordinates = new Tuple<ushort, ushort, ushort, ushort, uint>(
|
|
currentCarrierInformation.NipNetworkId, currentCarrierInformation.NipCarrierId,
|
|
currentCarrierInformation.NipLinkId, currentCarrierInformation.NipServiceId, privateDataSpecifier);
|
|
|
|
if (_dvbNipPrivateDataSpecifiers.ContainsKey(coordinates))
|
|
{
|
|
//PDS bereits bekannt.
|
|
DateTime lastChecked = _dvbNipPrivateDataSpecifiers[coordinates];
|
|
if (versionUpdate > lastChecked)
|
|
{
|
|
//Ist neuer, also updaten.
|
|
EnqueueTask(x => UpdateDvbNipPrivateDataSpecifier(x,coordinates,versionUpdate,privateDataSessions));
|
|
_dvbNipPrivateDataSpecifiers[coordinates] = versionUpdate;
|
|
return true;
|
|
}
|
|
else
|
|
{
|
|
//Ist älter oder hat sich nicht geändert.
|
|
return false;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
//PDS noch nicht bekannt, also in DB nachgucken
|
|
|
|
bool result = false;
|
|
DateTime lastUpdated = DateTime.MinValue;
|
|
NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString());
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"SELECT version_update FROM dvbnip_private_data_specifiers WHERE nid = @nid AND cid = @cid AND lid = @lid AND sid = @sid AND private_data_specifier = @pds";
|
|
command.Parameters.AddWithValue("@nid", NpgsqlDbType.Integer, (int)coordinates.Item1);
|
|
command.Parameters.AddWithValue("@cid", NpgsqlDbType.Integer, (int)coordinates.Item2);
|
|
command.Parameters.AddWithValue("@lid", NpgsqlDbType.Integer, (int)coordinates.Item3);
|
|
command.Parameters.AddWithValue("@sid", NpgsqlDbType.Integer, (int)coordinates.Item4);
|
|
command.Parameters.AddWithValue("@pds", NpgsqlDbType.Bigint, (long)coordinates.Item5);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
result = dataReader.Read();
|
|
if (result)
|
|
{
|
|
lastUpdated = dataReader.GetDateTime(0);
|
|
}
|
|
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
connection.Close();
|
|
|
|
if (result)
|
|
{
|
|
//schon in DB
|
|
if (lastUpdated < versionUpdate)
|
|
{
|
|
//Gesendetes Paket neuer als das in DB
|
|
EnqueueTask(x => UpdateDvbNipPrivateDataSpecifier(x, coordinates, versionUpdate, privateDataSessions));
|
|
_dvbNipPrivateDataSpecifiers[coordinates] = versionUpdate;
|
|
return true;
|
|
}
|
|
else
|
|
{
|
|
//Keine Änderung.
|
|
_dvbNipPrivateDataSpecifiers[coordinates] = versionUpdate;
|
|
return false;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
//noch nicht in DB
|
|
EnqueueTask(x => InsertDvbNipPrivateDataSpecifier(x, coordinates, versionUpdate, privateDataSessions));
|
|
_dvbNipPrivateDataSpecifiers[coordinates] = versionUpdate;
|
|
return true;
|
|
}
|
|
}
|
|
}
|
|
|
|
private void InsertDvbNipPrivateDataSpecifier(NpgsqlConnection connection, Tuple<ushort, ushort, ushort, ushort, uint> coordinates, DateTime versionUpdate, List<string> privateDataSessions)
|
|
{
|
|
DateTime localUpdate = new DateTime(versionUpdate.Year, versionUpdate.Month, versionUpdate.Day,
|
|
versionUpdate.Hour, versionUpdate.Minute, versionUpdate.Second, DateTimeKind.Local);
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "INSERT INTO dvbnip_private_data_specifiers VALUES (@nid,@cid,@lid,@sid,@pds,DEFAULT,@vu,@s)";
|
|
command.Parameters.AddWithValue("@nid", NpgsqlDbType.Integer, (int)coordinates.Item1);
|
|
command.Parameters.AddWithValue("@cid", NpgsqlDbType.Integer, (int)coordinates.Item2);
|
|
command.Parameters.AddWithValue("@lid", NpgsqlDbType.Integer, (int)coordinates.Item3);
|
|
command.Parameters.AddWithValue("@sid", NpgsqlDbType.Integer, (int)coordinates.Item4);
|
|
command.Parameters.AddWithValue("@pds", NpgsqlDbType.Bigint, (long)coordinates.Item5);
|
|
command.Parameters.AddWithValue("@vu", NpgsqlDbType.Timestamp, localUpdate);
|
|
command.Parameters.AddWithValue("@s", NpgsqlDbType.Json, JsonConvert.SerializeObject(privateDataSessions));
|
|
int executeNonQuery = command.ExecuteNonQuery();
|
|
if (executeNonQuery != 1)
|
|
{
|
|
throw new DataException(String.Format("Expected to insert {0} rows, but it were {1}", 1, executeNonQuery));
|
|
}
|
|
}
|
|
|
|
private void UpdateDvbNipPrivateDataSpecifier(NpgsqlConnection connection, Tuple<ushort, ushort, ushort, ushort, uint> coordinates, DateTime versionUpdate, List<string> privateDataSessions)
|
|
{
|
|
DateTime localUpdate = new DateTime(versionUpdate.Year, versionUpdate.Month, versionUpdate.Day,
|
|
versionUpdate.Hour, versionUpdate.Minute, versionUpdate.Second, DateTimeKind.Local);
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "UPDATE dvbnip_private_data_specifiers SET version_update = @vu, sessions = @s WHERE nid = @nid AND cid = @cid AND lid=@lid AND sid=@sid AND private_data_specifier = @pds";
|
|
command.Parameters.AddWithValue("@nid", NpgsqlDbType.Integer, (int)coordinates.Item1);
|
|
command.Parameters.AddWithValue("@cid", NpgsqlDbType.Integer, (int)coordinates.Item2);
|
|
command.Parameters.AddWithValue("@lid", NpgsqlDbType.Integer, (int)coordinates.Item3);
|
|
command.Parameters.AddWithValue("@sid", NpgsqlDbType.Integer, (int)coordinates.Item4);
|
|
command.Parameters.AddWithValue("@pds", NpgsqlDbType.Bigint, (long)coordinates.Item5);
|
|
command.Parameters.AddWithValue("@vu", NpgsqlDbType.Timestamp, localUpdate);
|
|
command.Parameters.AddWithValue("@s", NpgsqlDbType.Json, JsonConvert.SerializeObject(privateDataSessions));
|
|
int executeNonQuery = command.ExecuteNonQuery();
|
|
if (executeNonQuery != 1)
|
|
{
|
|
throw new DataException(String.Format("Expected to insert {0} rows, but it were {1}", 1, executeNonQuery));
|
|
}
|
|
}
|
|
|
|
private bool[] _dvbNipKnownNetworks;
|
|
public bool DvbNipTestForNetwork(BroadcastNetworkType network)
|
|
{
|
|
if (_dvbNipKnownNetworks == null)
|
|
_dvbNipKnownNetworks = new bool[ushort.MaxValue];
|
|
|
|
if (_dvbNipKnownNetworks[network.NIPNetworkID])
|
|
return true;
|
|
|
|
bool result = false;
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "SELECT dateadded FROM dvbnip_network WHERE nid = @nid";
|
|
command.Parameters.AddWithValue("@nid", NpgsqlDbType.Integer, (int)network.NIPNetworkID);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
if (dataReader.Read())
|
|
{
|
|
_dvbNipKnownNetworks[network.NIPNetworkID] = true;
|
|
result = true;
|
|
}
|
|
dataReader.Close();
|
|
connection.Close();
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public void DvbNipInsertNetwork(BroadcastNetworkType network)
|
|
{
|
|
_dvbNipKnownNetworks[network.NIPNetworkID] = true;
|
|
EnqueueTask(x => DvbNipInsertNetworkEx(x, network));
|
|
}
|
|
|
|
private void DvbNipInsertNetworkEx(NpgsqlConnection connection, BroadcastNetworkType network)
|
|
{
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "INSERT INTO dvbnip_network VALUES (@nid,DEFAULT,@pn,@nn,@nt,@op,@wef)";
|
|
command.Parameters.AddWithValue("@nid", NpgsqlDbType.Integer, (int)network.NIPNetworkID);
|
|
command.Parameters.AddWithValue("@pn", NpgsqlDbType.Text, network.NIPNetworkProviderName);
|
|
command.Parameters.AddWithValue("@nn", NpgsqlDbType.Text, network.NetworkName);
|
|
command.Parameters.AddWithValue("@nt", NpgsqlDbType.Integer, (int)network.NetworkType);
|
|
command.Parameters.AddWithValue("@op", NpgsqlDbType.Double, network.SatellitePosition.OrbitalPosition);
|
|
command.Parameters.AddWithValue("@wef", NpgsqlDbType.Integer, (int)network.SatellitePosition.West_East_flag);
|
|
command.ExecuteNonQuery();
|
|
command.Dispose();
|
|
|
|
foreach (NIPStreamType nipStreamType in network.NIPStream)
|
|
{
|
|
DvbNipInsertNetworkStreamType(connection, network.NIPNetworkID, nipStreamType);
|
|
}
|
|
}
|
|
|
|
private void DvbNipInsertNetworkStreamType(NpgsqlConnection connection, ushort networkNipNetworkId, NIPStreamType nipStreamType)
|
|
{
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "INSERT INTO dvbnip_network_stream VALUES (@nid,@cid,@lid,@sid,@spn,DEFAULT,@llf,@bt,@s)";
|
|
command.Parameters.AddWithValue("@nid", NpgsqlDbType.Integer, (int)networkNipNetworkId);
|
|
command.Parameters.AddWithValue("@cid", NpgsqlDbType.Integer, int.Parse(nipStreamType.NIPCarrierID));
|
|
command.Parameters.AddWithValue("@lid", NpgsqlDbType.Integer, int.Parse(nipStreamType.NIPLinkID));
|
|
command.Parameters.AddWithValue("@sid", NpgsqlDbType.Integer, (int)nipStreamType.NIPServiceID);
|
|
command.Parameters.AddWithValue("@spn", NpgsqlDbType.Text, nipStreamType.NIPStreamProviderName);
|
|
command.Parameters.AddWithValue("@llf", NpgsqlDbType.Integer, (int)nipStreamType.LinkLayerFormat);
|
|
command.Parameters.AddWithValue("@bt", NpgsqlDbType.Integer, (int)nipStreamType.BootstrapStream.BootstrapType);
|
|
command.Parameters.AddWithValue("@s", NpgsqlDbType.Integer, (int)nipStreamType.BootstrapStream.Status);
|
|
command.ExecuteNonQuery();
|
|
command.Dispose();
|
|
|
|
}
|
|
|
|
private HashSet<Tuple<ushort, ushort, ushort, ushort>> _knownDvbNipServices;
|
|
public bool DvbNipTestForService(BroadcastMediaStreamType broadcastMediaStreamType)
|
|
{
|
|
if (_knownDvbNipServices == null)
|
|
_knownDvbNipServices = new HashSet<Tuple<ushort, ushort, ushort, ushort>>();
|
|
|
|
Tuple<ushort, ushort, ushort, ushort> coordinates = new Tuple<ushort, ushort, ushort, ushort>(
|
|
broadcastMediaStreamType.NIPNetworkID, ushort.Parse(broadcastMediaStreamType.NIPCarrierID),
|
|
ushort.Parse(broadcastMediaStreamType.NIPLinkID), broadcastMediaStreamType.NIPServiceID);
|
|
|
|
if (_knownDvbNipServices.Contains(coordinates))
|
|
return true;
|
|
|
|
bool result = false;
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"SELECT dateadded FROM dvbnip_services WHERE nid = @nid AND cid = @cid AND lid = @lid AND sid = @sid";
|
|
command.Parameters.AddWithValue("@nid", NpgsqlDbType.Integer, (int)coordinates.Item1);
|
|
command.Parameters.AddWithValue("@cid", NpgsqlDbType.Integer, (int)coordinates.Item2);
|
|
command.Parameters.AddWithValue("@lid", NpgsqlDbType.Integer, (int)coordinates.Item3);
|
|
command.Parameters.AddWithValue("@sid", NpgsqlDbType.Integer, (int)coordinates.Item4);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
if (dataReader.Read())
|
|
{
|
|
_knownDvbNipServices.Add(coordinates);
|
|
result = true;
|
|
}
|
|
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
connection.Close();
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public void DvbNipInsertService(BroadcastMediaStreamType broadcastMediaStreamType)
|
|
{
|
|
EnqueueTask(x => DvbNipInsertServiceEx(x,broadcastMediaStreamType));
|
|
}
|
|
|
|
private void DvbNipInsertServiceEx(NpgsqlConnection connection, BroadcastMediaStreamType broadcastMediaStreamType)
|
|
{
|
|
if (broadcastMediaStreamType.BroadcastMedia.InteractiveApplications != null)
|
|
throw new NotImplementedException(nameof(broadcastMediaStreamType.BroadcastMedia.InteractiveApplications));
|
|
|
|
if (_knownDvbNipServices == null)
|
|
_knownDvbNipServices = new HashSet<Tuple<ushort, ushort, ushort, ushort>>();
|
|
|
|
Tuple<ushort, ushort, ushort, ushort> coordinates = new Tuple<ushort, ushort, ushort, ushort>(
|
|
broadcastMediaStreamType.NIPNetworkID, ushort.Parse(broadcastMediaStreamType.NIPCarrierID),
|
|
ushort.Parse(broadcastMediaStreamType.NIPLinkID), broadcastMediaStreamType.NIPServiceID);
|
|
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "INSERT INTO dvbnip_services VALUES (@nid,@cid,@lid,@sid,DEFAULT,@uri)";
|
|
command.Parameters.AddWithValue("@nid", NpgsqlDbType.Integer, (int)coordinates.Item1);
|
|
command.Parameters.AddWithValue("@cid", NpgsqlDbType.Integer, (int)coordinates.Item2);
|
|
command.Parameters.AddWithValue("@lid", NpgsqlDbType.Integer, (int)coordinates.Item3);
|
|
command.Parameters.AddWithValue("@sid", NpgsqlDbType.Integer, (int)coordinates.Item4);
|
|
command.Parameters.AddWithValue("@uri", NpgsqlDbType.Json, JsonConvert.SerializeObject(broadcastMediaStreamType.BroadcastMedia.URI));
|
|
int rowsInserted = command.ExecuteNonQuery();
|
|
if (rowsInserted != 1)
|
|
throw new DataException(String.Format("Expected to insert {0} rows, but it were {1}", 1, rowsInserted));
|
|
_knownDvbNipServices.Add(coordinates);
|
|
}
|
|
|
|
private HashSet<string> _knownDvbNipMulticastSessions;
|
|
public bool DvbNipTestForMulticastSession(MulticastSessionType multicastSession)
|
|
{
|
|
if (_knownDvbNipMulticastSessions == null)
|
|
_knownDvbNipMulticastSessions = new HashSet<string>();
|
|
|
|
if (_knownDvbNipMulticastSessions.Contains(multicastSession.serviceIdentifier))
|
|
return true;
|
|
|
|
bool result;
|
|
NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString());
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "SELECT dateadded FROM dvbnip_multicast_sessions WHERE service_identifier = @si";
|
|
command.Parameters.AddWithValue("@si", NpgsqlDbType.Text, multicastSession.serviceIdentifier);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
if (result = dataReader.Read())
|
|
{
|
|
_knownDvbNipMulticastSessions.Add(multicastSession.serviceIdentifier);
|
|
}
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
connection.Close();
|
|
return result;
|
|
}
|
|
|
|
public void DvbNipInsertMulticastSession(MulticastSessionType multicastSession)
|
|
{
|
|
EnqueueTask(x => DvbNipInsertMulticastSessionEx(x, multicastSession));
|
|
}
|
|
|
|
private void DvbNipInsertMulticastSessionEx(NpgsqlConnection connection, MulticastSessionType multicastSession)
|
|
{
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"INSERT INTO dvbnip_multicast_sessions (service_identifier, content_playback_availability_offset) VALUES (@si,@cpao) RETURNING serial";
|
|
command.Parameters.AddWithValue("@si", multicastSession.serviceIdentifier);
|
|
command.Parameters.AddWithValue("@cpao", multicastSession.contentPlaybackAvailabilityOffset);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
dataReader.Read();
|
|
int serial = dataReader.GetInt32(0);
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
|
|
if (multicastSession.MulticastGatewaySessionReporting != null)
|
|
{
|
|
throw new NotImplementedException(nameof(multicastSession.MulticastGatewaySessionReporting));
|
|
}
|
|
|
|
if (multicastSession.MulticastTransportSession != null)
|
|
{
|
|
for (int i = 0; i < multicastSession.MulticastTransportSession.Length; i++)
|
|
{
|
|
DvbNipInsertMulticastSessionTransport(connection,serial,i,multicastSession.MulticastTransportSession[i]);
|
|
}
|
|
}
|
|
|
|
if (multicastSession.PresentationManifestLocator != null)
|
|
{
|
|
for (int i = 0; i < multicastSession.PresentationManifestLocator.Length; i++)
|
|
{
|
|
DvbNipInsertPresentationManifestLocator(connection, serial, i, multicastSession.PresentationManifestLocator[i]);
|
|
}
|
|
}
|
|
}
|
|
|
|
private void DvbNipInsertPresentationManifestLocator(NpgsqlConnection connection, int serial, int ordinal, MulticastSessionTypePresentationManifestLocator multicastSessionTypePresentationManifestLocator)
|
|
{
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "INSERT INTO dvbnip_presentation_manifest_locator VALUES (@parent,@ordinal,DEFAULT,@cppp,@ct,@mi,@to)";
|
|
command.Parameters.AddWithValue("@parent", NpgsqlDbType.Integer, serial);
|
|
command.Parameters.AddWithValue("@ordinal", NpgsqlDbType.Integer, ordinal);
|
|
command.Parameters.AddWithValue("@cppp", NpgsqlDbType.Text, multicastSessionTypePresentationManifestLocator.contentPlaybackPathPattern);
|
|
command.Parameters.AddWithValue("@ct", NpgsqlDbType.Text, multicastSessionTypePresentationManifestLocator.contentType);
|
|
command.Parameters.AddWithValue("@mi", NpgsqlDbType.Text, multicastSessionTypePresentationManifestLocator.manifestId);
|
|
command.Parameters.AddWithValue("@to", NpgsqlDbType.Text, multicastSessionTypePresentationManifestLocator.transportObjectURI);
|
|
SetNulls(command);
|
|
int executeNonQuery = command.ExecuteNonQuery();
|
|
if (executeNonQuery != 1)
|
|
throw new DataException(String.Format("Expected to insert {0} rows, but it were {1}", 1, executeNonQuery));
|
|
}
|
|
|
|
private void DvbNipInsertMulticastSessionTransport(NpgsqlConnection connection, int serial, int ordinal, MulticastTransportSessionType transport)
|
|
{
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"INSERT INTO dvbnip_multicast_transport_session VALUES " +
|
|
"(@serial,@ordinal,DEFAULT,DEFAULT,@id,@average,@maximum,@mediaTsi,@dstaddr,@srcaddr,@dstport,@protocolId,@protocolVersion,@contentIngestMethod," +
|
|
" @sessionIdleTimeout,@transmissionMode,@transportSecurity) " +
|
|
"RETURNING uuid";
|
|
command.Parameters.AddWithValue("@serial", NpgsqlDbType.Integer, serial);
|
|
command.Parameters.AddWithValue("@ordinal", NpgsqlDbType.Integer, ordinal);
|
|
command.Parameters.AddWithValue("@id", NpgsqlDbType.Text, transport.id);
|
|
if (!string.IsNullOrEmpty(transport.BitRate.average))
|
|
{
|
|
command.Parameters.AddWithValue("@average", NpgsqlDbType.Integer, int.Parse(transport.BitRate.average));
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.AddWithValue("@average", NpgsqlDbType.Integer, DBNull.Value);
|
|
}
|
|
|
|
command.Parameters.AddWithValue("@maximum", NpgsqlDbType.Integer, int.Parse(transport.BitRate.maximum));
|
|
command.Parameters.AddWithValue("@mediaTsi", NpgsqlDbType.Integer, int.Parse(transport.EndpointAddress[0].MediaTransportSessionIdentifier));
|
|
command.Parameters.AddWithValue("@dstaddr", NpgsqlDbType.Inet, IPAddress.Parse(transport.EndpointAddress[0].NetworkDestinationGroupAddress));
|
|
|
|
if (!string.IsNullOrEmpty(transport.EndpointAddress[0].NetworkSourceAddress))
|
|
{
|
|
command.Parameters.AddWithValue("@srcaddr", NpgsqlDbType.Inet, IPAddress.Parse(transport.EndpointAddress[0].NetworkSourceAddress));
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.AddWithValue("@srcaddr", NpgsqlDbType.Inet, DBNull.Value);
|
|
}
|
|
|
|
command.Parameters.AddWithValue("@dstport", NpgsqlDbType.Integer, int.Parse(transport.EndpointAddress[0].TransportDestinationPort));
|
|
command.Parameters.AddWithValue("@protocolId", NpgsqlDbType.Text, transport.TransportProtocol.protocolIdentifier);
|
|
command.Parameters.AddWithValue("@protocolVersion", NpgsqlDbType.Integer, int.Parse(transport.TransportProtocol.protocolVersion));
|
|
command.Parameters.AddWithValue("@contentIngestMethod", NpgsqlDbType.Integer, (int)transport.contentIngestMethod);
|
|
command.Parameters.AddWithValue("@sessionIdleTimeout", NpgsqlDbType.Integer, int.Parse(transport.sessionIdleTimeout));
|
|
command.Parameters.AddWithValue("@transmissionMode", NpgsqlDbType.Integer, (int)transport.transmissionMode);
|
|
command.Parameters.AddWithValue("@transportSecurity", NpgsqlDbType.Integer, (int)transport.transportSecurity);
|
|
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
dataReader.Read();
|
|
Guid uuid = dataReader.GetGuid(0);
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
|
|
if (transport.ForwardErrorCorrectionParameters != null)
|
|
{
|
|
for (int i = 0; i < transport.ForwardErrorCorrectionParameters.Length; i++)
|
|
{
|
|
InsertForwardErrorCorrectionParameters(connection,uuid, i, transport.ForwardErrorCorrectionParameters[i]);
|
|
}
|
|
}
|
|
|
|
if (transport.EndpointAddress.Length > 1)
|
|
{
|
|
throw new NotImplementedException(nameof(transport.EndpointAddress));
|
|
}
|
|
|
|
if (transport.ServiceComponentIdentifier != null)
|
|
{
|
|
for (int i = 0; i < transport.ServiceComponentIdentifier.Length; i++)
|
|
{
|
|
ServiceComponentIdentifierType serviceComponentIdentifier = transport.ServiceComponentIdentifier[i];
|
|
DvbNipInsertServiceComponentIdentifier(connection,uuid, i, serviceComponentIdentifier);
|
|
}
|
|
}
|
|
|
|
if (transport.UnicastRepairParameters != null)
|
|
{
|
|
throw new NotImplementedException(nameof(transport.UnicastRepairParameters));
|
|
}
|
|
}
|
|
|
|
private void InsertForwardErrorCorrectionParameters(NpgsqlConnection connection, Guid uuid, int ordinal, ForwardErrorCorrectionParametersType transportForwardErrorCorrectionParameter)
|
|
{
|
|
if (transportForwardErrorCorrectionParameter.Any != null)
|
|
{
|
|
throw new NotImplementedException(nameof(transportForwardErrorCorrectionParameter.Any));
|
|
}
|
|
|
|
if (transportForwardErrorCorrectionParameter.AnyAttr != null)
|
|
{
|
|
throw new NotImplementedException(nameof(transportForwardErrorCorrectionParameter.AnyAttr));
|
|
}
|
|
|
|
if (transportForwardErrorCorrectionParameter.EndpointAddress != null)
|
|
{
|
|
throw new NotImplementedException(nameof(transportForwardErrorCorrectionParameter.EndpointAddress));
|
|
}
|
|
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "INSERT INTO dvbnip_forward_error_correction_parameter VALUES (@uuid,@ordinal,DEFAULT,@sidentifier,@opercentage)";
|
|
command.Parameters.AddWithValue("@uuid", NpgsqlDbType.Uuid, uuid);
|
|
command.Parameters.AddWithValue("@ordinal", NpgsqlDbType.Integer, ordinal);
|
|
command.Parameters.AddWithValue("@sidentifier", NpgsqlDbType.Text, transportForwardErrorCorrectionParameter.SchemeIdentifier);
|
|
command.Parameters.AddWithValue("@opercentage", NpgsqlDbType.Integer, Convert.ToInt32(transportForwardErrorCorrectionParameter.OverheadPercentage));
|
|
int executeNonQuery = command.ExecuteNonQuery();
|
|
if (executeNonQuery != 1)
|
|
throw new DataException(String.Format("Expected {0}, got {1}", 1, executeNonQuery));
|
|
command.Dispose();
|
|
}
|
|
|
|
private void DvbNipInsertServiceComponentIdentifier(NpgsqlConnection connection, Guid uuid, int i, ServiceComponentIdentifierType serviceComponentIdentifier)
|
|
{
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "INSERT INTO dvbnip_service_component_identifier VALUES (@guid,@ordinal,@xsiType,@manifestId,@representation_id,@period_id,@adaption_set_id,@media_playlist_locator)";
|
|
command.Parameters.AddWithValue("@guid", NpgsqlDbType.Uuid, uuid);
|
|
command.Parameters.AddWithValue("@ordinal", NpgsqlDbType.Integer, i);
|
|
command.Parameters.Add("@xsiType", NpgsqlDbType.Integer);
|
|
command.Parameters.AddWithValue("@manifestId", NpgsqlDbType.Text, serviceComponentIdentifier.manifestIdRef);
|
|
command.Parameters.Add("@representation_id", NpgsqlDbType.Text);
|
|
command.Parameters.Add("@period_id", NpgsqlDbType.Text);
|
|
command.Parameters.Add("@adaption_set_id", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@media_playlist_locator", NpgsqlDbType.Text);
|
|
|
|
XmlAttribute xmlAttribute = serviceComponentIdentifier.AnyAttr[0];
|
|
string xsiType = xmlAttribute.Value;
|
|
switch (xsiType)
|
|
{
|
|
case "DASHComponentIdentifierType":
|
|
command.Parameters["@xsiType"].Value = 1;
|
|
DASHComponentIdentifierType dash = (DASHComponentIdentifierType)serviceComponentIdentifier;
|
|
command.Parameters["@representation_id"].Value = dash.representationIdentifier;
|
|
command.Parameters["@period_id"].Value = dash.periodIdentifier;
|
|
command.Parameters["@adaption_set_id"].Value = (int)dash.adaptationSetIdentifier;
|
|
break;
|
|
case "HLSComponentIdentifierType":
|
|
command.Parameters["@xsiType"].Value = 2;
|
|
HLSComponentIdentifierType hls = (HLSComponentIdentifierType)serviceComponentIdentifier;
|
|
command.Parameters["@media_playlist_locator"].Value = hls.mediaPlaylistLocator;
|
|
break;
|
|
case "ns2:DASHComponentIdentifierType":
|
|
command.Parameters["@xsiType"].Value = 3;
|
|
DASHComponentIdentifierType dash3 = (DASHComponentIdentifierType)serviceComponentIdentifier;
|
|
command.Parameters["@adaption_set_id"].Value = (int)dash3.adaptationSetIdentifier;
|
|
command.Parameters["@period_id"].Value = dash3.periodIdentifier;
|
|
command.Parameters["@representation_id"].Value = dash3.representationIdentifier;
|
|
break;
|
|
case "ns2:HLSComponentIdentifierType":
|
|
command.Parameters["@xsiType"].Value = 4;
|
|
HLSComponentIdentifierType hls4 = (HLSComponentIdentifierType)serviceComponentIdentifier;
|
|
command.Parameters["@media_playlist_locator"].Value = hls4.mediaPlaylistLocator;
|
|
break;
|
|
default:
|
|
throw new NotImplementedException(xsiType);
|
|
}
|
|
|
|
SetNulls(command);
|
|
int executeNonQuery = command.ExecuteNonQuery();
|
|
if (executeNonQuery != 1)
|
|
throw new DataException(String.Format("Expected to insert {0} rows, but it were {1}", 1, executeNonQuery));
|
|
command.Dispose();
|
|
}
|
|
|
|
private HashSet<DatabaseKeyNipMulticastGatewayConfigurationTransportSession> _knownNipMulticastGatewayConfigurationTransportSessions;
|
|
public bool DvbNipTestForMulticastGatewayConfigurationTransportSession(NipActualCarrierInformation carrier,
|
|
MulticastEndpointAddressType multicastEndpointAddressType)
|
|
{
|
|
if (_knownNipMulticastGatewayConfigurationTransportSessions == null)
|
|
_knownNipMulticastGatewayConfigurationTransportSessions = new HashSet<DatabaseKeyNipMulticastGatewayConfigurationTransportSession>();
|
|
|
|
DatabaseKeyNipMulticastGatewayConfigurationTransportSession key = new DatabaseKeyNipMulticastGatewayConfigurationTransportSession(carrier, multicastEndpointAddressType);
|
|
if (_knownNipMulticastGatewayConfigurationTransportSessions.Contains(key))
|
|
return true;
|
|
|
|
bool result;
|
|
NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString());
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"SELECT dateadded " +
|
|
"FROM dvbnip_multicast_gateway_configuration_transport_sessions " +
|
|
"WHERE network_id = @nid AND carrier_id = @cid AND link_id = @lid AND service_id = @sid " +
|
|
"AND source_address = @srcaddr AND destination_address = @dstaddr AND destination_port = @dstport AND tsi = @tsi";
|
|
command.Parameters.AddWithValue("@nid", NpgsqlDbType.Integer, (int)key.NetworkId);
|
|
command.Parameters.AddWithValue("@cid", NpgsqlDbType.Integer, (int)key.CarrierId);
|
|
command.Parameters.AddWithValue("@lid", NpgsqlDbType.Integer, (int)key.LinkId);
|
|
command.Parameters.AddWithValue("@sid", NpgsqlDbType.Integer, (int)key.ServiceId);
|
|
command.Parameters.AddWithValue("@srcaddr", NpgsqlDbType.Inet, key.SourceAddress);
|
|
command.Parameters.AddWithValue("@dstaddr", NpgsqlDbType.Inet, key.DestinationAddress);
|
|
command.Parameters.AddWithValue("@dstport", NpgsqlDbType.Integer, (int)key.DestinationPort);
|
|
command.Parameters.AddWithValue("@tsi", NpgsqlDbType.Integer, key.TSI);
|
|
SetNulls(command);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
if (result = dataReader.Read())
|
|
{
|
|
_knownNipMulticastGatewayConfigurationTransportSessions.Add(key);
|
|
}
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
connection.Close();
|
|
return result;
|
|
}
|
|
|
|
public void DvbNipInsertMulticastGatewayConfigurationTransportSession(NipActualCarrierInformation carrier,
|
|
MulticastEndpointAddressType multicastGatewayConfigurationTransportSession)
|
|
{
|
|
EnqueueTask(x => DvbNipInsertMulticastGatewayConfigurationTransportSessionEx(x, carrier, multicastGatewayConfigurationTransportSession));
|
|
}
|
|
|
|
private void DvbNipInsertMulticastGatewayConfigurationTransportSessionEx(NpgsqlConnection connection, NipActualCarrierInformation carrier, MulticastEndpointAddressType multicastEndpointAddressType)
|
|
{
|
|
DatabaseKeyNipMulticastGatewayConfigurationTransportSession key = new DatabaseKeyNipMulticastGatewayConfigurationTransportSession(carrier, multicastEndpointAddressType);
|
|
if (_knownNipMulticastGatewayConfigurationTransportSessions.Contains(key))
|
|
return;
|
|
_knownNipMulticastGatewayConfigurationTransportSessions.Add(key);
|
|
|
|
NpgsqlCommand checkCommand = connection.CreateCommand();
|
|
checkCommand.CommandText =
|
|
"SELECT dateadded FROM dvbnip_multicast_gateway_configuration_transport_sessions WHERE network_id = @nid " +
|
|
"AND carrier_id = @cid AND link_id = @lid AND service_id = @sid AND source_address = @srcaddr " +
|
|
"AND destination_address = @dstaddr AND destination_port = @dstport AND tsi = @tsi";
|
|
checkCommand.Parameters.AddWithValue("@nid", NpgsqlDbType.Integer, (int)key.NetworkId);
|
|
checkCommand.Parameters.AddWithValue("@cid", NpgsqlDbType.Integer, (int)key.CarrierId);
|
|
checkCommand.Parameters.AddWithValue("@lid", NpgsqlDbType.Integer, (int)key.LinkId);
|
|
checkCommand.Parameters.AddWithValue("@sid", NpgsqlDbType.Integer, (int)key.ServiceId);
|
|
if (key.SourceAddress != null)
|
|
checkCommand.Parameters.AddWithValue("@srcaddr", NpgsqlDbType.Inet, key.SourceAddress);
|
|
else
|
|
checkCommand.Parameters.AddWithValue("@srcaddr", NpgsqlDbType.Inet, new IPAddress(0));
|
|
checkCommand.Parameters.AddWithValue("@dstaddr", NpgsqlDbType.Inet, key.DestinationAddress);
|
|
checkCommand.Parameters.AddWithValue("@dstport", NpgsqlDbType.Integer, (int)key.DestinationPort);
|
|
checkCommand.Parameters.AddWithValue("@tsi", NpgsqlDbType.Integer, key.TSI);
|
|
NpgsqlDataReader dataReader = checkCommand.ExecuteReader();
|
|
bool alreadyExist = dataReader.Read();
|
|
dataReader.Close();
|
|
checkCommand.Dispose();
|
|
if (alreadyExist)
|
|
return;
|
|
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "INSERT INTO dvbnip_multicast_gateway_configuration_transport_sessions VALUES (@nid,@cid,@lid,@sid,@srcaddr,@dstaddr,@dstport,@tsi,DEFAULT)";
|
|
command.Parameters.AddWithValue("@nid", NpgsqlDbType.Integer, (int)key.NetworkId);
|
|
command.Parameters.AddWithValue("@cid", NpgsqlDbType.Integer, (int)key.CarrierId);
|
|
command.Parameters.AddWithValue("@lid", NpgsqlDbType.Integer, (int)key.LinkId);
|
|
command.Parameters.AddWithValue("@sid", NpgsqlDbType.Integer, (int)key.ServiceId);
|
|
if (key.SourceAddress != null)
|
|
command.Parameters.AddWithValue("@srcaddr", NpgsqlDbType.Inet, key.SourceAddress);
|
|
else
|
|
command.Parameters.AddWithValue("@srcaddr", NpgsqlDbType.Inet, new IPAddress(0));
|
|
command.Parameters.AddWithValue("@dstaddr", NpgsqlDbType.Inet, key.DestinationAddress);
|
|
command.Parameters.AddWithValue("@dstport", NpgsqlDbType.Integer, (int)key.DestinationPort);
|
|
command.Parameters.AddWithValue("@tsi", NpgsqlDbType.Integer, key.TSI);
|
|
SetNulls(command);
|
|
int executeNonQuery = command.ExecuteNonQuery();
|
|
if (executeNonQuery != 1)
|
|
throw new DataException(String.Format("Expected to insert {0} rows, but it were {1}", 1, executeNonQuery));
|
|
command.Dispose();
|
|
}
|
|
}
|
|
}
|