66 lines
3.2 KiB
C#
66 lines
3.2 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using Npgsql;
|
|
using NpgsqlTypes;
|
|
using skyscraper5.Skyscraper.Scraper.Storage.Utilities;
|
|
using skyscraper5.Teletext;
|
|
|
|
namespace skyscraper5.Data.PostgreSql
|
|
{
|
|
public partial class PostgresqlDataStore
|
|
{
|
|
private HashSet<DatabaseKeyTeletextPage> knownTeletextPages;
|
|
public bool StoreTeletextPage(int networkId, int transportStreamId, ushort programNumber, TeletextMagazine magazine, DateTime timestamp)
|
|
{
|
|
if (knownTeletextPages == null)
|
|
knownTeletextPages = new HashSet<DatabaseKeyTeletextPage>();
|
|
|
|
timestamp = new DateTime(timestamp.Year, timestamp.Month, timestamp.Day, timestamp.Hour, timestamp.Minute, 0);
|
|
DatabaseKeyTeletextPage key = new DatabaseKeyTeletextPage(networkId, transportStreamId, programNumber, magazine.HumanReadablePageNumber, timestamp);
|
|
if (knownTeletextPages.Contains(key))
|
|
return false;
|
|
|
|
EnqueueTask(x => WriteTeletextPage(x, networkId, transportStreamId, programNumber, magazine, timestamp));
|
|
knownTeletextPages.Add(key);
|
|
return true;
|
|
}
|
|
|
|
private void WriteTeletextPage(NpgsqlConnection connection, int networkId, int transportStreamId, ushort programNumber, TeletextMagazine magazine, DateTime timestamp)
|
|
{
|
|
if (TestForTeletextPage(connection, networkId, transportStreamId, programNumber, magazine.HumanReadablePageNumber, timestamp))
|
|
return;
|
|
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"INSERT INTO teletext (nid, tsid, program_number, page, tdt_time, page_content) " +
|
|
"VALUES (@nid, @tsid, @program_number, @page, @tdt_time, @page_content)";
|
|
command.Parameters.AddWithValue("@nid", NpgsqlDbType.Integer, networkId);
|
|
command.Parameters.AddWithValue("@tsid", NpgsqlDbType.Integer, transportStreamId);
|
|
command.Parameters.AddWithValue("@program_number", NpgsqlDbType.Integer, (int)programNumber);
|
|
command.Parameters.AddWithValue("@page", NpgsqlDbType.Integer, (int)magazine.HumanReadablePageNumber);
|
|
command.Parameters.AddWithValue("@tdt_time", NpgsqlDbType.Timestamp, timestamp);
|
|
command.Parameters.AddWithValue("@page_content", NpgsqlDbType.Bytea, magazine.FormatAsByteArray());
|
|
command.ExecuteNonQuery();
|
|
}
|
|
|
|
private bool TestForTeletextPage(NpgsqlConnection connection, int networkId, int transportStreamId, ushort programNumber, int pageNumber, DateTime timestamp)
|
|
{
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "SELECT dateadded FROM teletext WHERE nid = @nid AND tsid = @tsid AND program_number = @program_number AND page = @page AND tdt_time = @tdt_time";
|
|
command.Parameters.AddWithValue("@nid", NpgsqlDbType.Integer, networkId);
|
|
command.Parameters.AddWithValue("@tsid", NpgsqlDbType.Integer, transportStreamId);
|
|
command.Parameters.AddWithValue("@program_number", NpgsqlDbType.Integer, (int)programNumber);
|
|
command.Parameters.AddWithValue("@page", NpgsqlDbType.Integer, pageNumber);
|
|
command.Parameters.AddWithValue("@tdt_time", NpgsqlDbType.Timestamp, timestamp);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
bool result = dataReader.Read();
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
return result;
|
|
}
|
|
}
|
|
}
|