356 lines
27 KiB
C#
356 lines
27 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using Npgsql;
|
|
using NpgsqlTypes;
|
|
using skyscraper5.Scte35;
|
|
using skyscraper5.Skyscraper.Scraper.Storage.Utilities;
|
|
|
|
namespace skyscraper5.Data.PostgreSql
|
|
{
|
|
public partial class PostgresqlDataStore
|
|
{
|
|
public bool TestForScte35SpliceInsert(int currentNetworkId, int currentTransportStreamId, ushort programNumber, SpliceInsert spliceInsert)
|
|
{
|
|
bool result;
|
|
using (NpgsqlConnection conn = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
conn.Open();
|
|
NpgsqlCommand command = conn.CreateCommand();
|
|
command.CommandText = "SELECT dateadded " +
|
|
"FROM scte35_splices " +
|
|
"WHERE cnid = @cnid " +
|
|
"AND ctsid = @ctsid " +
|
|
"AND program_number = @program_number " +
|
|
"AND splice_event_id = @splice_event_id";
|
|
command.Parameters.AddParameter("@cnid", NpgsqlDbType.Integer, currentNetworkId);
|
|
command.Parameters.AddParameter("@ctsid", NpgsqlDbType.Integer, currentTransportStreamId);
|
|
command.Parameters.AddParameter("@program_number", NpgsqlDbType.Integer, programNumber);
|
|
command.Parameters.AddParameter("@splice_event_id", NpgsqlDbType.Bigint, (long)spliceInsert.SpliceEventId);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
result = dataReader.Read();
|
|
dataReader.Close();
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public void StoreScte35SpliceInsert(int currentNetworkId, int currentTransportStreamId, ushort programNumber,
|
|
SpliceInsert spliceInsert)
|
|
{
|
|
EnqueueTask(x => WriteScte35SpliceInsert(x, currentNetworkId, currentTransportStreamId, programNumber, spliceInsert));
|
|
}
|
|
|
|
private void WriteScte35SpliceInsert(NpgsqlConnection connection, int currentNetworkId, int currentTransportStreamId, ushort programNumber, SpliceInsert spliceInsert)
|
|
{
|
|
if (TestForScte35SpliceInsert(currentNetworkId,currentTransportStreamId,programNumber,spliceInsert))
|
|
{
|
|
return;
|
|
}
|
|
transaction.Commit();
|
|
transaction = null;
|
|
transaction = connection.BeginTransaction();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"insert into scte35_splices (cnid, ctsid, program_number, splice_event_id, splice_event_cancel_indicator, out_of_network_indicator, program_splice_flag, duration_flag, splice_immediate_flag, splice_time, duration_auto_return, duration, unique_program_id, avail_num, avails_expected) " +
|
|
"values " +
|
|
"(@cnid,@ctsid,@program_number,@splice_event_id,@splice_event_cancel_indicator,@out_of_network_indicator,@program_splice_flag,@duration_flag,@splice_immediate_flag,@splice_time,@duration_auto_return, @duration, @unique_program_id, @avail_num, @avails_expected)";
|
|
command.Parameters.AddParameter("@cnid", NpgsqlDbType.Integer, currentNetworkId);
|
|
command.Parameters.AddParameter("@ctsid", NpgsqlDbType.Integer, currentTransportStreamId);
|
|
command.Parameters.AddParameter("@program_number", NpgsqlDbType.Integer, programNumber);
|
|
command.Parameters.AddParameter("@splice_event_id", NpgsqlDbType.Bigint, (long)spliceInsert.SpliceEventId);
|
|
command.Parameters.AddParameter("@splice_event_cancel_indicator", NpgsqlDbType.Boolean, spliceInsert.SpliceEventCancelIndicator);
|
|
command.Parameters.AddParameter("@out_of_network_indicator", NpgsqlDbType.Boolean, spliceInsert.OutOfNetworkIndicator);
|
|
command.Parameters.AddParameter("@program_splice_flag", NpgsqlDbType.Boolean, spliceInsert.ProgramSpliceFlag);
|
|
command.Parameters.AddParameter("@duration_flag", NpgsqlDbType.Boolean, spliceInsert.DurationFlag);
|
|
command.Parameters.AddParameter("@splice_immediate_flag", NpgsqlDbType.Boolean, spliceInsert.SpliceImmediateFlag);
|
|
command.Parameters.AddParameter("@splice_time", NpgsqlDbType.Bigint, (long?)spliceInsert.SpliceTime);
|
|
command.Parameters.AddParameter("@duration_auto_return", NpgsqlDbType.Boolean, spliceInsert.DurationAutoReturn);
|
|
//@duration, @unique_program_id, @avail_num, @avails_expected\r\n );\r\n";
|
|
command.Parameters.AddParameter("@duration", NpgsqlDbType.Bigint, (long?)spliceInsert.Duration);
|
|
command.Parameters.AddParameter("@unique_program_id", NpgsqlDbType.Integer, spliceInsert.UniqueProgramId);
|
|
command.Parameters.AddParameter("@avail_num", NpgsqlDbType.Integer, spliceInsert.AvailNum);
|
|
command.Parameters.AddParameter("@avails_expected", NpgsqlDbType.Integer, spliceInsert.AvailsExpected);
|
|
try
|
|
{
|
|
command.ExecuteNonQuery();
|
|
}
|
|
catch (PostgresException pe)
|
|
{
|
|
transaction.Rollback();
|
|
transaction = null;
|
|
transaction = connection.BeginTransaction();
|
|
Console.WriteLine("Warning: Network {0}, TSID {1}, Program {2}, Splice {3} not unique.", currentNetworkId, currentTransportStreamId, programNumber, spliceInsert.SpliceEventId);
|
|
return;
|
|
}
|
|
|
|
WriteScte35SpliceComponents(connection, currentNetworkId, currentTransportStreamId, programNumber, spliceInsert);
|
|
|
|
if (spliceInsert.Descriptors == null)
|
|
return;
|
|
|
|
if (spliceInsert.Descriptors.availDescriptor != null)
|
|
{
|
|
WriteScte35SpliceAvailability(connection, currentNetworkId, currentTransportStreamId, programNumber, spliceInsert);
|
|
}
|
|
|
|
if (spliceInsert.Descriptors.segmentationDescriptor != null)
|
|
{
|
|
WriteScte35SpliceSegmentation(connection, currentNetworkId, currentTransportStreamId, programNumber, spliceInsert);
|
|
}
|
|
|
|
if (spliceInsert.Descriptors.timeDescriptor != null)
|
|
{
|
|
throw new NotImplementedException();
|
|
}
|
|
}
|
|
|
|
private void WriteScte35SpliceAvailability(NpgsqlConnection connection, int currentNetworkId, int currentTransportStreamId, ushort programNumber, SpliceInsert spliceInsert)
|
|
{
|
|
Scte35.Descriptors.AvailDescriptor descriptor = spliceInsert.Descriptors.availDescriptor;
|
|
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "update scte35_splices\r\n" +
|
|
"set provider_avail_id = @provider_avail_id, identifier = @identifier\r\n" +
|
|
"where cnid = @cnid " +
|
|
"and ctsid = @ctsid " +
|
|
"and program_number = @program_number " +
|
|
"and splice_event_id = @splice_event_id\r\n";
|
|
command.Parameters.AddParameter("@cnid", NpgsqlDbType.Integer, currentNetworkId);
|
|
command.Parameters.AddParameter("@ctsid", NpgsqlDbType.Integer, currentTransportStreamId);
|
|
command.Parameters.AddParameter("@program_number", NpgsqlDbType.Integer, programNumber);
|
|
command.Parameters.AddParameter("@splice_event_id", NpgsqlDbType.Bigint, (long)spliceInsert.SpliceEventId);
|
|
command.Parameters.AddParameter("@provider_avail_id", NpgsqlDbType.Bigint, (long)descriptor.ProviderAvailId);
|
|
command.Parameters.AddParameter("@identifier", NpgsqlDbType.Bigint, (long)descriptor.Identifier);
|
|
command.ExecuteNonQuery();
|
|
}
|
|
|
|
private void WriteScte35SpliceSegmentation(NpgsqlConnection connection, int currentNetworkId, int currentTransportStreamId, ushort programNumber, SpliceInsert spliceInsert)
|
|
{
|
|
Scte35.Descriptors.SegmentationDescriptor segment = spliceInsert.Descriptors.segmentationDescriptor;
|
|
|
|
NpgsqlCommand cmd = connection.CreateCommand();
|
|
cmd.CommandText = "insert into scte35_splices_segmentations (cnid, ctsid, program_number, splice_event_id, splice_time,\r\n identifier, segmentation_event_id, segmentation_event_cancel_indicator,\r\n program_segmentation_flag, segmentation_duration_flag, web_delivery_flag,\r\n no_regional_blackout_flag, archive_allowed_flag, device_restrictions,\r\n segmentation_duration, segmentation_upid_type, segmentation_upid,\r\n segmentation_type_id, segment_num, segments_expected, sub_segment_num,\r\n sub_segments_expected) " +
|
|
"values " +
|
|
"(@cnid,@ctsid,@program_number,@splice_event_id,@splice_time,@identifier,@segmentation_event_id,@segmentation_event_cancel_indicator,@program_segmentation_flag,@segmentation_duration_flag,@web_delivery_flag,\r\n @no_regional_blackout_flag, @archive_allowed_flag, @device_restrictions,\r\n @segmentation_duration, @segmentation_upid_type, @segmentation_upid,\r\n @segmentation_type_id, @segment_num, @segments_expected, @sub_segment_num,\r\n @sub_segments_expected\r\n )";
|
|
cmd.Parameters.AddWithValue("@cnid", NpgsqlDbType.Integer, currentNetworkId);
|
|
cmd.Parameters.AddWithValue("@ctsid", NpgsqlDbType.Integer, currentTransportStreamId);
|
|
cmd.Parameters.AddWithValue("@program_number", NpgsqlDbType.Integer, (int)programNumber);
|
|
cmd.Parameters.AddWithValue("@splice_event_id", NpgsqlDbType.Bigint, (long)spliceInsert.SpliceEventId);
|
|
cmd.Parameters.AddWithValue("@splice_time", NpgsqlDbType.Bigint, (long?)spliceInsert.SpliceTime);
|
|
cmd.Parameters.AddWithValue("@identifier", NpgsqlDbType.Bigint, (long?)segment.Identifier);
|
|
cmd.Parameters.AddWithValue("@segmentation_event_id", NpgsqlDbType.Bigint, (long?)segment.SegmentationEventId);
|
|
cmd.Parameters.AddWithValue("@segmentation_event_cancel_indicator", NpgsqlDbType.Boolean, segment.SegmentationEventCancelIndicator);
|
|
cmd.Parameters.AddWithValue("@program_segmentation_flag", NpgsqlDbType.Boolean, segment.ProgramSegmentationFlag);
|
|
cmd.Parameters.AddWithValue("@segmentation_duration_flag", NpgsqlDbType.Boolean, segment.SegmentationDurationFlag);
|
|
//@web_delivery_flag,@no_regional_blackout_flag,@archive_allowed_flag,@device_restrictions,@segmentation_duration,@segmentation_upid_type,@segmentation_upid,@segmentation_type_id,@segment_num,@segments_expected, @sub_segment_num,\r\n @sub_segments_expected\r\n )";
|
|
cmd.Parameters.AddWithValue("@web_delivery_flag", NpgsqlDbType.Boolean, segment.WebDeliveryFlag);
|
|
cmd.Parameters.AddWithValue("@no_regional_blackout_flag", NpgsqlDbType.Boolean, segment.NoRegionalBlackoutFlag);
|
|
cmd.Parameters.AddWithValue("@archive_allowed_flag", NpgsqlDbType.Boolean, segment.ArchiveAllowedFlag);
|
|
cmd.Parameters.AddWithValue("@device_restrictions", NpgsqlDbType.Integer, segment.DeviceRestrictions);
|
|
cmd.Parameters.AddWithValue("@segmentation_duration", NpgsqlDbType.Bigint, segment.SegmentationDuration);
|
|
cmd.Parameters.AddWithValue("@segmentation_upid_type", NpgsqlDbType.Integer, segment.SegmentationUpidType);
|
|
cmd.Parameters.AddWithValue("@segmentation_upid", NpgsqlDbType.Text, segment.SegmentationUpid);
|
|
cmd.Parameters.AddWithValue("@segmentation_type_id", NpgsqlDbType.Integer, (int?)segment.SegmentationTypeId);
|
|
cmd.Parameters.AddWithValue("@segment_num", NpgsqlDbType.Integer, (int?)segment.SegmentNum);
|
|
//@segments_expected,@sub_segment_num,@sub_segments_expected
|
|
cmd.Parameters.AddWithValue("@segments_expected", NpgsqlDbType.Integer, (int?)segment.SegmentsExpected);
|
|
cmd.Parameters.AddWithValue("@sub_segment_num", NpgsqlDbType.Integer, (int?)segment.SubSegmentNum);
|
|
cmd.Parameters.AddWithValue("@sub_segments_expected", NpgsqlDbType.Integer, (int?)segment.SubSegmentsExpected);
|
|
cmd.Parameters.CheckNulls();
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
private void WriteScte35SpliceComponents(NpgsqlConnection connection, int currentNetworkId, int currentTransportStreamId, ushort programNumber, SpliceInsert spliceInsert)
|
|
{
|
|
if (spliceInsert.Components == null)
|
|
return;
|
|
|
|
for (int i = 0; i < spliceInsert.Components.Length; i++)
|
|
{
|
|
throw new NotImplementedException();
|
|
}
|
|
}
|
|
|
|
private Dictionary<DatabaseKeyScte35TimeSignal, DateTime> knownScte35TimeSignals;
|
|
public void SetScte35TimeSignal(int currentNetworkId, int currentTransportStreamId, DateTime currentTime, ushort programNumber,
|
|
TimeSignal timeSignal)
|
|
{
|
|
EnqueueTask(x => WriteScte35TimeSignal(x, currentNetworkId, currentTransportStreamId, currentTime, programNumber, timeSignal));
|
|
EnqueueTask(CommitTransaction);
|
|
}
|
|
|
|
private void WriteScte35TimeSignal(NpgsqlConnection connection, int cnid, int ctsid, DateTime timeSignalled, ushort programNumber,
|
|
TimeSignal timeSignal)
|
|
{
|
|
if (knownScte35TimeSignals == null)
|
|
knownScte35TimeSignals = new Dictionary<DatabaseKeyScte35TimeSignal, DateTime>();
|
|
|
|
DatabaseKeyScte35TimeSignal key = new DatabaseKeyScte35TimeSignal(cnid, ctsid, programNumber);
|
|
|
|
if (knownScte35TimeSignals.ContainsKey(key))
|
|
{
|
|
//already cached
|
|
DateTime timeInDb = knownScte35TimeSignals[key];
|
|
if (timeSignalled > timeInDb)
|
|
{
|
|
//Need to update
|
|
UpdateScte35TimeSignal(connection, cnid, ctsid, timeSignalled, programNumber, timeSignal);
|
|
knownScte35TimeSignals[key] = timeSignalled;
|
|
}
|
|
else
|
|
{
|
|
//Nothing to do
|
|
return;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
//not yet cached
|
|
DateTime? timeInDb = SelectScte35TimeSignal(connection, cnid, ctsid, programNumber);
|
|
if (timeInDb.HasValue)
|
|
{
|
|
if (timeSignalled > timeInDb.Value)
|
|
{
|
|
//Need to update and cache
|
|
UpdateScte35TimeSignal(connection, cnid, ctsid, timeSignalled, programNumber, timeSignal);
|
|
knownScte35TimeSignals[key] = timeInDb.Value;
|
|
}
|
|
else
|
|
{
|
|
//Cache past value and do nothing
|
|
knownScte35TimeSignals[key] = timeInDb.Value;
|
|
return;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
//Need to insert and cache
|
|
InsertScte35TimeSignal(connection, cnid, ctsid, timeSignalled, programNumber, timeSignal);
|
|
knownScte35TimeSignals[key] = timeSignalled;
|
|
}
|
|
}
|
|
}
|
|
|
|
private void UpdateScte35TimeSignal(NpgsqlConnection connection, int cnid, int ctsid, DateTime timeSignalled, ushort programNumber, TimeSignal timeSignal)
|
|
{
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"update scte35_time_signal\r\nset ctime = @ctime,\r\n scte35_value = @scte35_value,\r\n provider_avail_id = @provider_avail_id,\r\n identifier = @identifier,\r\n sub_segments_expected = @sub_segments_expected,\r\n sub_segment_num = @sub_segment_num,\r\n segments_expected = @segments_expected,\r\n segment_num = @segment_num,\r\n segmentation_type_id = @segmentation_type_id,\r\n segmentation_upid = @segmentation_upid,\r\n segmentation_upid_type = @segmentation_upid_type,\r\n segmentation_duration = @segmentation_duration,\r\n program_segmentation_flag = @program_segmentation_flag,\r\n segmentation_event_cancel_indicator = @segmentation_event_cancel_indicator,\r\n device_restrictions = @device_restrictions,\r\n archive_allowed_flag = @archive_allowed_flag,\r\n no_regional_blackout_flag = @no_regional_blackout_flag,\r\n web_delivery_flag = @web_delivery_flag,\r\n segmentation_duration_flag = @segmentation_duration_flag,\r\n segmentation_event_id = @segmentation_event_id,\r\n seg_identifier = @seg_identifier,\r\n utc_offset = @utc_offset,\r\n tai_ns = @tai_ns,\r\n tai_seconds = @tai_seconds,\r\n time_identifier = @time_identifier,\r\n updated_counter = updated_counter + 1,\r\n updated_timestamp = CURRENT_TIMESTAMP\r\nwhere cnid = @cnid\r\nand tsid = @tsid\r\nand program_number = @program_number";
|
|
command.Parameters.AddParameter("@cnid", NpgsqlDbType.Integer, cnid);
|
|
command.Parameters.AddParameter("@tsid", NpgsqlDbType.Integer, ctsid);
|
|
command.Parameters.AddParameter("@ctime", NpgsqlDbType.Timestamp, timeSignalled);
|
|
command.Parameters.AddParameter("@program_number", NpgsqlDbType.Integer, programNumber);
|
|
command.Parameters.AddParameter("@scte35_value", NpgsqlDbType.Bigint, (long?)timeSignal.Value);
|
|
command.Parameters.AddParameter("@provider_avail_id", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.availDescriptor?.ProviderAvailId);
|
|
command.Parameters.AddParameter("@identifier", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.availDescriptor?.Identifier);
|
|
command.Parameters.AddParameter("@sub_segments_expected", NpgsqlDbType.Integer, (byte?)timeSignal.Descriptors.segmentationDescriptor?.SubSegmentsExpected);
|
|
command.Parameters.AddParameter("@sub_segment_num", NpgsqlDbType.Integer, (byte?)timeSignal.Descriptors.segmentationDescriptor?.SubSegmentNum);
|
|
command.Parameters.AddParameter("@segments_expected", NpgsqlDbType.Integer, (byte?)timeSignal.Descriptors.segmentationDescriptor?.SegmentsExpected);
|
|
command.Parameters.AddParameter("@segment_num", NpgsqlDbType.Integer, (byte?)timeSignal.Descriptors.segmentationDescriptor?.SegmentNum);
|
|
command.Parameters.AddParameter("@segmentation_type_id", NpgsqlDbType.Integer, (byte?)timeSignal.Descriptors.segmentationDescriptor?.SegmentationTypeId);
|
|
if (timeSignal.Descriptors.segmentationDescriptor != null)
|
|
{
|
|
if (timeSignal.Descriptors.segmentationDescriptor.SegmentationUpid.Contains("\0"))
|
|
{
|
|
timeSignal.Descriptors.segmentationDescriptor.SegmentationUpid = "";
|
|
}
|
|
}
|
|
command.Parameters.AddParameter("@segmentation_upid", NpgsqlDbType.Text, timeSignal.Descriptors.segmentationDescriptor?.SegmentationUpid);
|
|
//@segmentation_upid_type,@segmentation_duration,@program_segmentation_flag,@segmentation_event_cancel_indicator,@device_restrictions,@archive_allowed_flag,@no_regional_blackout_flag,@web_delivery_flag,@segmentation_duration_flag, @segmentation_event_id, @seg_identifier, @utc_offset, @tai_ns, @tai_seconds, @time_identifier)";
|
|
command.Parameters.AddParameter("@segmentation_upid_type", NpgsqlDbType.Integer, (byte?)timeSignal.Descriptors.segmentationDescriptor?.SegmentationUpidType);
|
|
command.Parameters.AddParameter("@segmentation_duration", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.segmentationDescriptor?.SegmentationDuration);
|
|
command.Parameters.AddParameter("@program_segmentation_flag", NpgsqlDbType.Boolean, (bool?)timeSignal.Descriptors.segmentationDescriptor?.ProgramSegmentationFlag);
|
|
command.Parameters.AddParameter("@segmentation_event_cancel_indicator", NpgsqlDbType.Boolean, (bool?)timeSignal.Descriptors.segmentationDescriptor?.SegmentationEventCancelIndicator);
|
|
command.Parameters.AddParameter("@device_restrictions", NpgsqlDbType.Integer, (int?)timeSignal.Descriptors.segmentationDescriptor?.DeviceRestrictions);
|
|
command.Parameters.AddParameter("@archive_allowed_flag", NpgsqlDbType.Boolean, (bool?)timeSignal.Descriptors.segmentationDescriptor?.ArchiveAllowedFlag);
|
|
command.Parameters.AddParameter("@no_regional_blackout_flag", NpgsqlDbType.Boolean, (bool?)timeSignal.Descriptors.segmentationDescriptor?.NoRegionalBlackoutFlag);
|
|
command.Parameters.AddParameter("@web_delivery_flag", NpgsqlDbType.Boolean, (bool?)timeSignal.Descriptors.segmentationDescriptor?.WebDeliveryFlag);
|
|
//@segmentation_duration_flag, @segmentation_event_id, @seg_identifier, @utc_offset, @tai_ns, @tai_seconds, @time_identifier)";
|
|
command.Parameters.AddParameter("@segmentation_duration_flag", NpgsqlDbType.Boolean, (bool?)timeSignal.Descriptors.segmentationDescriptor?.SegmentationDurationFlag);
|
|
command.Parameters.AddParameter("@segmentation_event_id", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.segmentationDescriptor?.SegmentationEventId);
|
|
command.Parameters.AddParameter("@seg_identifier", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.segmentationDescriptor?.Identifier);
|
|
if (timeSignal.Descriptors.timeDescriptor != null)
|
|
{
|
|
command.Parameters.AddParameter("@utc_offset", NpgsqlDbType.Integer, (int?)timeSignal.Descriptors.timeDescriptor.UtcOffset);
|
|
command.Parameters.AddParameter("@tai_ns", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.timeDescriptor.TaiNs);
|
|
command.Parameters.AddParameter("@tai_seconds", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.timeDescriptor.TaiSeconds);
|
|
command.Parameters.AddParameter("@time_identifier", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.timeDescriptor.Identifier);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.AddParameter("@utc_offset", NpgsqlDbType.Integer, DBNull.Value);
|
|
command.Parameters.AddParameter("@tai_ns", NpgsqlDbType.Bigint, DBNull.Value);
|
|
command.Parameters.AddParameter("@tai_seconds", NpgsqlDbType.Bigint, DBNull.Value);
|
|
command.Parameters.AddParameter("@time_identifier", NpgsqlDbType.Bigint, DBNull.Value);
|
|
}
|
|
command.ExecuteNonQuery();
|
|
}
|
|
|
|
private void InsertScte35TimeSignal(NpgsqlConnection connection, int cnid, int ctsid, DateTime timeSignalled, ushort programNumber, TimeSignal timeSignal)
|
|
{
|
|
if (timeSignal.Descriptors != null)
|
|
if (timeSignal.Descriptors.segmentationDescriptor != null)
|
|
if (timeSignal.Descriptors.segmentationDescriptor.SegmentationUpid != null)
|
|
if (timeSignal.Descriptors.segmentationDescriptor.SegmentationUpid.Contains("\0"))
|
|
timeSignal.Descriptors.segmentationDescriptor.SegmentationUpid = "";
|
|
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"insert into scte35_time_signal (cnid, tsid, ctime, program_number, scte35_value, provider_avail_id, identifier, sub_segments_expected, sub_segment_num, segments_expected, segment_num," +
|
|
" segmentation_type_id, segmentation_upid, segmentation_upid_type, segmentation_duration, program_segmentation_flag, segmentation_event_cancel_indicator, device_restrictions, archive_allowed_flag,\r\n no_regional_blackout_flag, web_delivery_flag, segmentation_duration_flag,\r\n segmentation_event_id, seg_identifier, utc_offset, tai_ns, tai_seconds, time_identifier) " +
|
|
"values " +
|
|
"(@cnid,@tsid,@ctime,@program_number,@scte35_value,@provider_avail_id,@identifier,@sub_segments_expected,@sub_segment_num,@segments_expected,@segment_num,@segmentation_type_id,@segmentation_upid,@segmentation_upid_type, @segmentation_duration, @program_segmentation_flag, @segmentation_event_cancel_indicator, @device_restrictions, @archive_allowed_flag, @no_regional_blackout_flag, @web_delivery_flag, @segmentation_duration_flag, @segmentation_event_id, @seg_identifier, @utc_offset, @tai_ns, @tai_seconds, @time_identifier)";
|
|
command.Parameters.AddParameter("@cnid", NpgsqlDbType.Integer, cnid);
|
|
command.Parameters.AddParameter("@tsid", NpgsqlDbType.Integer, ctsid);
|
|
command.Parameters.AddParameter("@ctime", NpgsqlDbType.Timestamp, timeSignalled);
|
|
command.Parameters.AddParameter("@program_number", NpgsqlDbType.Integer, programNumber);
|
|
command.Parameters.AddParameter("@scte35_value", NpgsqlDbType.Bigint, (long?)timeSignal.Value);
|
|
command.Parameters.AddParameter("@provider_avail_id", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.availDescriptor?.ProviderAvailId);
|
|
command.Parameters.AddParameter("@identifier", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.availDescriptor?.Identifier);
|
|
command.Parameters.AddParameter("@sub_segments_expected", NpgsqlDbType.Integer, (byte?)timeSignal.Descriptors.segmentationDescriptor?.SubSegmentsExpected);
|
|
command.Parameters.AddParameter("@sub_segment_num", NpgsqlDbType.Integer, (byte?)timeSignal.Descriptors.segmentationDescriptor?.SubSegmentNum);
|
|
command.Parameters.AddParameter("@segments_expected", NpgsqlDbType.Integer, (byte?)timeSignal.Descriptors.segmentationDescriptor?.SegmentsExpected);
|
|
command.Parameters.AddParameter("@segment_num", NpgsqlDbType.Integer, (byte?)timeSignal.Descriptors.segmentationDescriptor?.SegmentNum);
|
|
command.Parameters.AddParameter("@segmentation_type_id", NpgsqlDbType.Integer, (byte?)timeSignal.Descriptors.segmentationDescriptor?.SegmentationTypeId);
|
|
command.Parameters.AddParameter("@segmentation_upid", NpgsqlDbType.Text, timeSignal.Descriptors.segmentationDescriptor?.SegmentationUpid);
|
|
//@segmentation_upid_type,@segmentation_duration,@program_segmentation_flag,@segmentation_event_cancel_indicator,@device_restrictions,@archive_allowed_flag,@no_regional_blackout_flag,@web_delivery_flag,@segmentation_duration_flag, @segmentation_event_id, @seg_identifier, @utc_offset, @tai_ns, @tai_seconds, @time_identifier)";
|
|
command.Parameters.AddParameter("@segmentation_upid_type", NpgsqlDbType.Integer, (byte?)timeSignal.Descriptors.segmentationDescriptor?.SegmentationUpidType);
|
|
command.Parameters.AddParameter("@segmentation_duration", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.segmentationDescriptor?.SegmentationDuration);
|
|
command.Parameters.AddParameter("@program_segmentation_flag", NpgsqlDbType.Boolean, (bool?)timeSignal.Descriptors.segmentationDescriptor?.ProgramSegmentationFlag);
|
|
command.Parameters.AddParameter("@segmentation_event_cancel_indicator", NpgsqlDbType.Boolean, (bool?)timeSignal.Descriptors.segmentationDescriptor?.SegmentationEventCancelIndicator);
|
|
command.Parameters.AddParameter("@device_restrictions", NpgsqlDbType.Integer, (int?)timeSignal.Descriptors.segmentationDescriptor?.DeviceRestrictions);
|
|
command.Parameters.AddParameter("@archive_allowed_flag", NpgsqlDbType.Boolean, (bool?)timeSignal.Descriptors.segmentationDescriptor?.ArchiveAllowedFlag);
|
|
command.Parameters.AddParameter("@no_regional_blackout_flag", NpgsqlDbType.Boolean, (bool?)timeSignal.Descriptors.segmentationDescriptor?.NoRegionalBlackoutFlag);
|
|
command.Parameters.AddParameter("@web_delivery_flag", NpgsqlDbType.Boolean, (bool?)timeSignal.Descriptors.segmentationDescriptor?.WebDeliveryFlag);
|
|
//@segmentation_duration_flag, @segmentation_event_id, @seg_identifier, @utc_offset, @tai_ns, @tai_seconds, @time_identifier)";
|
|
command.Parameters.AddParameter("@segmentation_duration_flag", NpgsqlDbType.Boolean, (bool?)timeSignal.Descriptors.segmentationDescriptor?.SegmentationDurationFlag);
|
|
command.Parameters.AddParameter("@segmentation_event_id", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.segmentationDescriptor?.SegmentationEventId);
|
|
command.Parameters.AddParameter("@seg_identifier", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.segmentationDescriptor?.Identifier);
|
|
command.Parameters.AddParameter("@utc_offset", NpgsqlDbType.Integer, (int?)timeSignal.Descriptors.timeDescriptor?.UtcOffset);
|
|
command.Parameters.AddParameter("@tai_ns", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.timeDescriptor?.TaiNs);
|
|
command.Parameters.AddParameter("@tai_seconds", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.timeDescriptor?.TaiSeconds);
|
|
command.Parameters.AddParameter("@time_identifier", NpgsqlDbType.Bigint, (long?)timeSignal.Descriptors.timeDescriptor?.Identifier);
|
|
command.ExecuteNonQuery();
|
|
}
|
|
|
|
private DateTime? SelectScte35TimeSignal(NpgsqlConnection connection, int cnid, int ctsid, ushort programNumber)
|
|
{
|
|
DateTime? result = null;
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"SELECT ctime FROM scte35_time_signal WHERE cnid = @cnid AND tsid = @tsid AND program_number = @program_number";
|
|
command.Parameters.AddWithValue("@cnid", NpgsqlDbType.Integer, cnid);
|
|
command.Parameters.AddWithValue("@tsid", NpgsqlDbType.Integer, ctsid);
|
|
command.Parameters.AddWithValue("@program_number", NpgsqlDbType.Integer, (int)programNumber);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
if (dataReader.Read())
|
|
result = dataReader.GetDateTime(0);
|
|
dataReader.Close();
|
|
return result;
|
|
}
|
|
}
|
|
}
|