258 lines
15 KiB
C#
258 lines
15 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using Npgsql;
|
|
using NpgsqlTypes;
|
|
using skyscraper5.Dvb.SystemSoftwareUpdate.Model;
|
|
using skyscraper5.Skyscraper.Scraper.Storage.Utilities;
|
|
|
|
namespace skyscraper5.Data.PostgreSql
|
|
{
|
|
public partial class PostgresqlDataStore
|
|
{
|
|
private HashSet<DatabaseKeyUntGroup> _knownUntGroups;
|
|
private HashSet<DatabaseKeyUntCompatibility> _knownUntCompatibility;
|
|
private HashSet<DatabaseKeyUntPlatform> _knownUntPlatforms;
|
|
|
|
public void StoreUpdateNotification(int hashCode, UpdateNotificationGroup common, Compatibility compatibility, Platform platform)
|
|
{
|
|
if (_knownUntGroups == null)
|
|
_knownUntGroups = new HashSet<DatabaseKeyUntGroup>();
|
|
if (_knownUntCompatibility == null)
|
|
_knownUntCompatibility = new HashSet<DatabaseKeyUntCompatibility>();
|
|
if (_knownUntPlatforms == null)
|
|
_knownUntPlatforms = new HashSet<DatabaseKeyUntPlatform>();
|
|
|
|
DatabaseKeyUntPlatform platformCoordinate = new DatabaseKeyUntPlatform(common.ActionType, common.OuiHash,
|
|
common.Oui, common.ProcessingOrder, compatibility.DescriptorType, platform.GetFlagsForSqlKey());
|
|
if (_knownUntPlatforms.Contains(platformCoordinate))
|
|
{
|
|
return;
|
|
}
|
|
|
|
EnqueueTask(x => WriteUnt(x, common, compatibility, platform));
|
|
|
|
_knownUntPlatforms.Add(platformCoordinate);
|
|
}
|
|
|
|
private void WriteUnt(NpgsqlConnection connection, UpdateNotificationGroup common, Compatibility compatibility,
|
|
Platform platform)
|
|
{
|
|
if (!TestForUnt(connection, common))
|
|
InsertUnt(connection, common);
|
|
|
|
if (!TestForUntCompat(connection, common, compatibility))
|
|
InsertUntCompat(connection, common, compatibility);
|
|
|
|
if (!TestForUntCompatPlatform(connection, common, compatibility, platform))
|
|
InsertUntCompatPlatform(connection, common, compatibility, platform);
|
|
}
|
|
|
|
private bool TestForUnt(NpgsqlConnection connection, UpdateNotificationGroup common)
|
|
{
|
|
DatabaseKeyUntGroup groupCoordinate =
|
|
new DatabaseKeyUntGroup(common.ActionType, common.OuiHash, common.Oui, common.ProcessingOrder);
|
|
if (_knownUntGroups.Contains(groupCoordinate))
|
|
return true;
|
|
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.Transaction = transaction;
|
|
command.CommandText =
|
|
"SELECT dateadded FROM dvb_unt WHERE action_type = @action_type AND oui_hash = @oui_hash AND oui = @oui AND processing_order = @processing_order";
|
|
command.Parameters.AddParameter("@action_type", NpgsqlDbType.Integer, common.ActionType);
|
|
command.Parameters.AddParameter("@oui_hash", NpgsqlDbType.Integer, common.OuiHash);
|
|
command.Parameters.AddParameter("@oui", NpgsqlDbType.Varchar, common.Oui);
|
|
command.Parameters.AddParameter("@processing_order", NpgsqlDbType.Integer, common.ProcessingOrder);
|
|
NpgsqlDataReader mySqlDataReader = command.ExecuteReader();
|
|
bool read = mySqlDataReader.Read();
|
|
if (read)
|
|
_knownUntGroups.Add(groupCoordinate);
|
|
mySqlDataReader.Close();
|
|
return read;
|
|
}
|
|
|
|
private void InsertUnt(NpgsqlConnection connection, UpdateNotificationGroup common)
|
|
{
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.Transaction = transaction;
|
|
command.CommandText =
|
|
"INSERT INTO dvb_unt (action_type, oui_hash, oui, processing_order, data_broadcast_id, association_tag, private_data, update_flag, update_method, update_priority) " +
|
|
"VALUES (@action_type, @oui_hash, @oui, @processing_order, @data_broadcast_id, @association_tag, @private_data, @update_flag, @update_method, @update_priority)";
|
|
command.Parameters.AddParameter("@action_type", NpgsqlDbType.Integer, common.ActionType);
|
|
command.Parameters.AddParameter("@oui_hash", NpgsqlDbType.Integer, common.OuiHash);
|
|
command.Parameters.AddParameter("@oui", NpgsqlDbType.Varchar, common.Oui);
|
|
command.Parameters.AddParameter("@processing_order", NpgsqlDbType.Integer, common.ProcessingOrder);
|
|
command.Parameters.AddParameter("@data_broadcast_id", NpgsqlDbType.Integer, common.DataBroadcastId);
|
|
command.Parameters.AddParameter("@association_tag", NpgsqlDbType.Integer, common.AssociationTag);
|
|
command.Parameters.AddParameter("@private_data", NpgsqlDbType.Bytea, common.PrivateData);
|
|
command.Parameters.AddParameter("@update_flag", NpgsqlDbType.Integer, common.UpdateFlag);
|
|
command.Parameters.AddParameter("@update_method", NpgsqlDbType.Integer, common.UpdateMethod);
|
|
command.Parameters.AddParameter("@update_priority", NpgsqlDbType.Integer, common.UpdatePriority);
|
|
command.Parameters.CheckNulls();
|
|
command.ExecuteNonQuery();
|
|
|
|
DatabaseKeyUntGroup groupCoordinate =
|
|
new DatabaseKeyUntGroup(common.ActionType, common.OuiHash, common.Oui, common.ProcessingOrder);
|
|
_knownUntGroups.Add(groupCoordinate);
|
|
}
|
|
|
|
private bool TestForUntCompat(NpgsqlConnection connection, UpdateNotificationGroup common,
|
|
Compatibility compatibility)
|
|
{
|
|
DatabaseKeyUntCompatibility compatibilityCoordinate = new DatabaseKeyUntCompatibility(common.ActionType,
|
|
common.OuiHash, common.Oui, common.ProcessingOrder, compatibility.DescriptorType);
|
|
if (_knownUntCompatibility.Contains(compatibilityCoordinate))
|
|
return true;
|
|
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.Transaction = transaction;
|
|
command.CommandText =
|
|
"SELECT dateadded FROM dvb_unt_compatibility WHERE " +
|
|
"action_type = @action_type AND oui_hash = @oui_hash AND oui = @oui AND processing_order = @processing_order AND descriptor_type = @descriptor_type";
|
|
command.Parameters.AddParameter("@action_type", NpgsqlDbType.Integer,common.ActionType);
|
|
command.Parameters.AddParameter("@oui_hash", NpgsqlDbType.Integer, common.OuiHash);
|
|
command.Parameters.AddParameter("@oui", NpgsqlDbType.Varchar, common.Oui);
|
|
command.Parameters.AddParameter("@processing_order", NpgsqlDbType.Integer, common.ProcessingOrder);
|
|
command.Parameters.AddParameter("@descriptor_type", NpgsqlDbType.Integer, compatibility.DescriptorType);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
bool result = dataReader.Read();
|
|
if (result)
|
|
_knownUntCompatibility.Add(compatibilityCoordinate);
|
|
dataReader.Close();
|
|
return result;
|
|
}
|
|
|
|
private void InsertUntCompat(NpgsqlConnection connection, UpdateNotificationGroup common,
|
|
Compatibility compatibility)
|
|
{
|
|
NpgsqlCommand preCommand = connection.CreateCommand();
|
|
preCommand.CommandText = "SELECT dateadded FROM dvb_unt_compatibility WHERE action_type = @atype AND oui_hash = @ohash AND oui = @oui AND processing_order = @porder AND descriptor_type = @dtype";
|
|
preCommand.Parameters.AddParameter("@atype", NpgsqlDbType.Integer, common.ActionType);
|
|
preCommand.Parameters.AddParameter("@ohash", NpgsqlDbType.Integer, common.OuiHash);
|
|
preCommand.Parameters.AddParameter("@oui", NpgsqlDbType.Varchar, common.Oui);
|
|
preCommand.Parameters.AddParameter("@porder", NpgsqlDbType.Integer, common.ProcessingOrder);
|
|
preCommand.Parameters.AddParameter("@dtype", NpgsqlDbType.Integer, compatibility.DescriptorType);
|
|
NpgsqlDataReader dataReader = preCommand.ExecuteReader();
|
|
bool alreadyKnown = dataReader.Read();
|
|
dataReader.Close();
|
|
preCommand.Dispose();
|
|
if (alreadyKnown)
|
|
return;
|
|
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.Transaction = transaction;
|
|
command.CommandText =
|
|
"INSERT INTO dvb_unt_compatibility (action_type, oui_hash, oui, processing_order, descriptor_type, private_data, specifier_type, specifier_data, model, version) " +
|
|
"VALUES (@action_type, @oui_hash, @oui, @processing_order, @descriptor_type, @private_data, @specifier_type, @specifier_data, @model, @version)";
|
|
command.Parameters.AddParameter("@action_type", NpgsqlDbType.Integer, common.ActionType);
|
|
command.Parameters.AddParameter("@oui_hash", NpgsqlDbType.Integer, common.OuiHash);
|
|
command.Parameters.AddParameter("@oui", NpgsqlDbType.Varchar, common.Oui);
|
|
command.Parameters.AddParameter("@processing_order", NpgsqlDbType.Integer, common.ProcessingOrder);
|
|
command.Parameters.AddParameter("@descriptor_type", NpgsqlDbType.Integer, compatibility.DescriptorType);
|
|
command.Parameters.AddParameter("@private_data", NpgsqlDbType.Bytea, compatibility.PrivateData);
|
|
command.Parameters.AddParameter("@specifier_type", NpgsqlDbType.Integer, compatibility.SpecifierType);
|
|
command.Parameters.AddParameter("@specifier_data", NpgsqlDbType.Text, compatibility.SpecifierData);
|
|
command.Parameters.AddParameter("@model",NpgsqlDbType.Integer, compatibility.Model);
|
|
command.Parameters.AddParameter("@version", NpgsqlDbType.Integer, compatibility.Version);
|
|
command.Parameters.CheckNulls();
|
|
command.ExecuteNonQuery();
|
|
_knownUntCompatibility.Add(new DatabaseKeyUntCompatibility(common.ActionType, common.OuiHash, common.Oui,
|
|
common.ProcessingOrder, compatibility.DescriptorType));
|
|
}
|
|
|
|
private bool TestForUntCompatPlatform(NpgsqlConnection connection, UpdateNotificationGroup common,
|
|
Compatibility compatibility, Platform platform)
|
|
{
|
|
int sqlKey = platform.GetFlagsForSqlKey();
|
|
DatabaseKeyUntPlatform platformCoordinate = new DatabaseKeyUntPlatform(common.ActionType, common.OuiHash,
|
|
common.Oui, common.ProcessingOrder, compatibility.DescriptorType, sqlKey);
|
|
|
|
NpgsqlCommand command = transaction.Connection.CreateCommand();
|
|
command.Transaction = transaction;
|
|
command.CommandText =
|
|
"SELECT dateadded FROM dvb_unt_compatibility_platform WHERE action_type = @action_type AND oui_hash = @oui_hash AND oui = @oui AND processing_order = @processing_order " +
|
|
"AND descriptor_type = @descriptor_type AND platform_type_bitmask = @platform_type_bitmask";
|
|
command.Parameters.AddParameter("@action_type", NpgsqlDbType.Integer, common.ActionType);
|
|
command.Parameters.AddParameter("@oui_hash", NpgsqlDbType.Integer, common.OuiHash);
|
|
command.Parameters.AddParameter("@oui", NpgsqlDbType.Varchar, common.Oui);
|
|
command.Parameters.AddParameter("@processing_order", NpgsqlDbType.Integer, common.ProcessingOrder);
|
|
command.Parameters.AddParameter("@descriptor_type", NpgsqlDbType.Integer, compatibility.DescriptorType);
|
|
command.Parameters.AddParameter("@platform_type_bitmask", NpgsqlDbType.Integer, sqlKey);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
bool result = dataReader.Read();
|
|
if (result)
|
|
_knownUntPlatforms.Add(platformCoordinate);
|
|
dataReader.Close();
|
|
return result;
|
|
}
|
|
|
|
private void InsertUntCompatPlatform(NpgsqlConnection connection, UpdateNotificationGroup common,
|
|
Compatibility compatibility, Platform platform)
|
|
{
|
|
int sqlKey = platform.GetFlagsForSqlKey();
|
|
DatabaseKeyUntPlatform platformCoordinate = new DatabaseKeyUntPlatform(common.ActionType, common.OuiHash,
|
|
common.Oui, common.ProcessingOrder, compatibility.DescriptorType, sqlKey);
|
|
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.Transaction = transaction;
|
|
command.CommandText =
|
|
"INSERT INTO dvb_unt_compatibility_platform (action_type, oui_hash, oui, processing_order, descriptor_type, platform_type_bitmask, update_flag, update_method, update_priority, data_broadcast_id, association_tag, private_data, smart_card_number, super_ca_system_id, serial_data, mac_address_mask, subgroup_tag) " +
|
|
"VALUES (@action_type, @oui_hash, @oui, @processing_order, @descriptor_type, @platform_type_bitmask, @update_flag, @update_method, @update_priority, @data_broadcast_id, " +
|
|
" @association_tag, @private_data, @smart_card_number, @super_ca_system_id, @serial_data, @mac_address_mask, @subgroup_tag)";
|
|
command.Parameters.AddParameter("@action_type", NpgsqlDbType.Integer, common.ActionType);
|
|
command.Parameters.AddParameter("@oui_hash", NpgsqlDbType.Integer, common.OuiHash);
|
|
command.Parameters.AddParameter("@oui", NpgsqlDbType.Varchar, common.Oui);
|
|
command.Parameters.AddParameter("@processing_order", NpgsqlDbType.Integer, common.ProcessingOrder);
|
|
command.Parameters.AddParameter("@descriptor_type", NpgsqlDbType.Integer, compatibility.DescriptorType);
|
|
command.Parameters.AddParameter("@platform_type_bitmask", NpgsqlDbType.Integer, sqlKey);
|
|
command.Parameters.AddParameter("@update_flag", NpgsqlDbType.Integer, platform.UpdateFlag);
|
|
command.Parameters.AddParameter("@update_method", NpgsqlDbType.Integer, platform.UpdateMethod);
|
|
command.Parameters.AddParameter("@update_priority", NpgsqlDbType.Integer, platform.UpdatePriority);
|
|
command.Parameters.AddParameter("@data_broadcast_id", NpgsqlDbType.Integer, platform.DataBroadcastId);
|
|
command.Parameters.AddParameter("@association_tag", NpgsqlDbType.Integer, platform.AssociationTag);
|
|
command.Parameters.AddParameter("@private_data", NpgsqlDbType.Bytea, platform.PrivateData);
|
|
command.Parameters.AddParameter("@smart_card_number", NpgsqlDbType.Text, platform.SmartCardNumber);
|
|
command.Parameters.AddParameter("@super_ca_system_id", NpgsqlDbType.Bigint, platform.SuperCaSystemId);
|
|
command.Parameters.AddParameter("@serial_data", NpgsqlDbType.Bytea, platform.SerialData);
|
|
command.Parameters.AddParameter("@mac_address_mask", NpgsqlDbType.Varchar, platform.MacAddressMask);
|
|
command.Parameters.AddParameter("@subgroup_tag", NpgsqlDbType.Bytea, platform.SubgroupTag);
|
|
command.ExecuteNonQuery();
|
|
|
|
InsertUntCompatPlatformMacAddressMatches(connection, platformCoordinate, platform);
|
|
_knownUntPlatforms.Add(platformCoordinate);
|
|
}
|
|
|
|
private void InsertUntCompatPlatformMacAddressMatches(NpgsqlConnection connection,
|
|
DatabaseKeyUntPlatform platformCoordinate, Platform platform)
|
|
{
|
|
if (platform.MacAddressMatches == null)
|
|
return;
|
|
|
|
if (platform.MacAddressMatches.Length == 0)
|
|
return;
|
|
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.Transaction = transaction;
|
|
command.CommandText =
|
|
"INSERT INTO dvb_unt_compatibility_platform_mac_address_matches (action_type, oui_hash, oui, processing_order, descriptor_type, platform_type_bitmask, mac_match) " +
|
|
"VALUES (@action_type, @oui_hash, @oui, @processing_order, @descriptor_type, @platform_type_bitmask, @mac_match) ";
|
|
command.Parameters.AddParameter("@action_type", NpgsqlDbType.Integer, platformCoordinate.CommonActionType);
|
|
command.Parameters.AddParameter("@oui_hash", NpgsqlDbType.Integer, platformCoordinate.CommonOuiHash);
|
|
command.Parameters.AddParameter("@oui", NpgsqlDbType.Varchar, platformCoordinate.CommonOui);
|
|
command.Parameters.AddParameter("@processing_order", NpgsqlDbType.Integer, platformCoordinate.CommonProcessingOrder);
|
|
command.Parameters.AddParameter("@descriptor_type", NpgsqlDbType.Integer, platformCoordinate.CompatibilityDescriptorType);
|
|
command.Parameters.AddParameter("@platform_type_bitmask", NpgsqlDbType.Integer, platformCoordinate.PlatformKey);
|
|
command.Parameters.Add("@mac_match", NpgsqlDbType.Varchar);
|
|
|
|
foreach (string match in platform.MacAddressMatches)
|
|
{
|
|
command.Parameters["@mac_match"].Value = match;
|
|
command.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|
|
}
|