596 lines
28 KiB
C#
596 lines
28 KiB
C#
using Npgsql;
|
|
using NpgsqlTypes;
|
|
using skyscraper5.Dvb.Descriptors;
|
|
using skyscraper5.Skyscraper;
|
|
using skyscraper5.Skyscraper.Gps;
|
|
using skyscraper5.Skyscraper.IO.CrazycatStreamReader;
|
|
using skyscraper5.src.Skyscraper.FrequencyListGenerator;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Net.NetworkInformation;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace skyscraper5.Data.PostgreSql
|
|
{
|
|
public partial class PostgresqlDataStore
|
|
{
|
|
public void InsertBlindscanJob(DbBlindscanJob jobInDb)
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "insert into skyscraper5_blindscan_jobs (uuid, tuner_mac, tuner_std, diseqc_index, horizontal_low, horizontal_high,\r\n" +
|
|
" vertical_low, vertical_high, gps_lon, gps_lat, sat_position)\r\n" +
|
|
"values (@uuid,@tuner_mac,@tuner_std,@diseqc_index,@hl,@hh,@vl,@vh,@gps_lon,@gps_lat,@sat_position);";
|
|
command.Parameters.AddWithValue("@uuid", NpgsqlDbType.Uuid, jobInDb.JobGuid);
|
|
command.Parameters.AddWithValue("@tuner_mac", NpgsqlDbType.MacAddr, jobInDb.TunerMAC);
|
|
command.Parameters.AddWithValue("@tuner_std", NpgsqlDbType.Integer, (int)jobInDb.TunerStandard);
|
|
command.Parameters.AddWithValue("@diseqc_index", NpgsqlDbType.Integer, jobInDb.DiseqCIndex);
|
|
command.Parameters.AddWithValue("@hl", NpgsqlDbType.Integer, (int)jobInDb.HorizontalLowState);
|
|
command.Parameters.AddWithValue("@hh", NpgsqlDbType.Integer, (int)jobInDb.HorizontalHighState);
|
|
command.Parameters.AddWithValue("@vl", NpgsqlDbType.Integer, (int)jobInDb.VerticalLowState);
|
|
command.Parameters.AddWithValue("@vh", NpgsqlDbType.Integer, (int)jobInDb.VerticalHighState);
|
|
if (jobInDb.GpsCoordinate.HasValue)
|
|
{
|
|
command.Parameters.AddWithValue("@gps_lon", NpgsqlDbType.Double, (double)jobInDb.GpsCoordinate.Value.Longitude);
|
|
command.Parameters.AddWithValue("@gps_lat", NpgsqlDbType.Double, (double)jobInDb.GpsCoordinate.Value.Latitude);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.AddWithValue("@gps_lon", NpgsqlDbType.Double, DBNull.Value);
|
|
command.Parameters.AddWithValue("@gps_lat", NpgsqlDbType.Double, DBNull.Value);
|
|
}
|
|
|
|
if (jobInDb.SatPosition != null)
|
|
{
|
|
command.Parameters.AddWithValue("@sat_position", NpgsqlDbType.Integer, jobInDb.SatPosition.Checksum);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.AddWithValue("@sat_position", NpgsqlDbType.Integer, DBNull.Value);
|
|
}
|
|
|
|
command.ExecuteNonQuery();
|
|
command.Dispose();
|
|
connection.Close();
|
|
}
|
|
}
|
|
|
|
public void UpdateJobState(DbBlindscanJob jobInDb)
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"UPDATE skyscraper5_blindscan_jobs SET horizontal_low = @hl, horizontal_high = @hh, vertical_low = @vl, vertical_high = @vh, dateupdated = CURRENT_TIMESTAMP, version = version + 1 WHERE uuid = @uuid";
|
|
command.Parameters.AddWithValue("@uuid", NpgsqlDbType.Uuid, jobInDb.JobGuid);
|
|
command.Parameters.AddWithValue("@hl", NpgsqlDbType.Integer, (int)jobInDb.HorizontalLowState);
|
|
command.Parameters.AddWithValue("@hh", NpgsqlDbType.Integer, (int)jobInDb.HorizontalHighState);
|
|
command.Parameters.AddWithValue("@vl", NpgsqlDbType.Integer, (int)jobInDb.VerticalLowState);
|
|
command.Parameters.AddWithValue("@vh", NpgsqlDbType.Integer, (int)jobInDb.VerticalHighState);
|
|
int result = command.ExecuteNonQuery();
|
|
if (result != 1)
|
|
throw new InvalidDataException("db update failed");
|
|
command.Dispose();
|
|
connection.Close();
|
|
}
|
|
}
|
|
|
|
public void InsertSearchResult(DbBlindscanJob jobInDb, bool satellite, SearchResult searchResult, int polarityIndex, SearchResult2 searchResult2)
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"insert into skyscraper5_blindscan_search_results (related_job, satellite, polarity_index,\r\n" +
|
|
" lock, freq, pol, sr, std_type, mod_type, fec,\r\n" +
|
|
" inversion, roll_off, pilot, frame, coding_type, stream_type, mis,\r\n" +
|
|
" input_stream_ids, issyi, npd, pls, cw, bitrate, rf_level, snr, ber,\r\n" +
|
|
" pre_ber, bw, feclp, transmode, guard, hierarchy, plp, lid, s1type,\r\n" +
|
|
" payload, bw_ext, mode_t2, ver_t2, cell_id, net_id)\r\n" +
|
|
"values (@related_job, @satellite, @polarity_index, @lock, @freq, @pol, @sr, @std_type, @mod_type, @fec, @inversion,\r\n" +
|
|
" @roll_off, @pilot, @frame, @coding_type, @stream_type, @mis, @input_stream_ids, @issyi, @npd, @pls, @cw,\r\n" +
|
|
" @bitrate, @rf_level, @snr, @ber, @pre_ber, @bw, @feclp, @transmode, @guard, @hierarchy, @plp, @lid, @s1type,\r\n" +
|
|
" @payload, @bw_ext, @mode_t2, @ver_t2, @cell_id, @net_id)";
|
|
command.Parameters.Add("@related_job", NpgsqlDbType.Uuid);
|
|
command.Parameters.Add("@satellite", NpgsqlDbType.Boolean);
|
|
command.Parameters.Add("@polarity_index", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@lock", NpgsqlDbType.Boolean);
|
|
command.Parameters.Add("@freq", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@pol", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@sr", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@std_type", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@mod_type", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@fec", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@inversion", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@roll_off", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@pilot", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@frame", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@coding_type", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@stream_type", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@mis", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@input_stream_ids", NpgsqlDbType.Bytea);
|
|
command.Parameters.Add("@issyi", NpgsqlDbType.Smallint);
|
|
command.Parameters.Add("@npd", NpgsqlDbType.Smallint);
|
|
command.Parameters.Add("@pls", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@cw", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@bitrate", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@rf_level", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@snr", NpgsqlDbType.Double);
|
|
command.Parameters.Add("@ber", NpgsqlDbType.Double);
|
|
command.Parameters.Add("@pre_ber", NpgsqlDbType.Double);
|
|
//SR2
|
|
command.Parameters.Add("@bw", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@feclp", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@transmode", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@guard", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@hierarchy", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@plp", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@lid", NpgsqlDbType.Bytea);
|
|
command.Parameters.Add("@s1type", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@payload", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@bw_ext", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@mode_t2", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@ver_t2", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@cell_id", NpgsqlDbType.Integer);
|
|
command.Parameters.Add("@net_id", NpgsqlDbType.Integer);
|
|
|
|
foreach (NpgsqlParameter parameter in command.Parameters)
|
|
parameter.Value = DBNull.Value;
|
|
|
|
command.Parameters["@related_job"].Value = jobInDb.JobGuid;
|
|
command.Parameters["@satellite"].Value = satellite;
|
|
command.Parameters["@polarity_index"].Value = polarityIndex;
|
|
|
|
if (satellite)
|
|
{
|
|
command.Parameters["@lock"].Value = searchResult.Lock;
|
|
command.Parameters["@freq"].Value = searchResult.Freq;
|
|
command.Parameters["@pol"].Value = searchResult.Pol;
|
|
command.Parameters["@sr"].Value = searchResult.SR;
|
|
command.Parameters["@std_type"].Value = searchResult.StdType;
|
|
command.Parameters["@mod_type"].Value = searchResult.ModType;
|
|
command.Parameters["@fec"].Value = (int)searchResult.FEC;
|
|
command.Parameters["@inversion"].Value = searchResult.Inversion;
|
|
command.Parameters["@roll_off"].Value = searchResult.RollOff;
|
|
command.Parameters["@pilot"].Value = searchResult.Pilot;
|
|
command.Parameters["@frame"].Value = searchResult.Frame;
|
|
command.Parameters["@coding_type"].Value = searchResult.CodingType;
|
|
command.Parameters["@stream_type"].Value = searchResult.StreamType;
|
|
command.Parameters["@mis"].Value = searchResult.MIS;
|
|
command.Parameters["@input_stream_ids"].Value = searchResult.IS;
|
|
command.Parameters["@issyi"].Value = searchResult.ISSYI;
|
|
command.Parameters["@npd"].Value = searchResult.NPD;
|
|
command.Parameters["@pls"].Value = searchResult.PLS;
|
|
command.Parameters["@cw"].Value = searchResult.CW;
|
|
command.Parameters["@bitrate"].Value = DBNull.Value;
|
|
command.Parameters["@rf_level"].Value = searchResult.RFLevel;
|
|
command.Parameters["@snr"].Value = searchResult.SNR;
|
|
command.Parameters["@ber"].Value = searchResult.BER;
|
|
command.Parameters["@pre_ber"].Value = searchResult.preBER;
|
|
}
|
|
else
|
|
{
|
|
command.Parameters["@lock"].Value = searchResult2.Lock;
|
|
command.Parameters["@pol"].Value = 0;
|
|
command.Parameters["@freq"].Value = searchResult2.Freq;
|
|
command.Parameters["@sr"].Value = searchResult2.SR;
|
|
command.Parameters["@std_type"].Value = searchResult2.StdType;
|
|
command.Parameters["@mod_type"].Value = searchResult2.ModType;
|
|
command.Parameters["@fec"].Value = searchResult2.FEC;
|
|
command.Parameters["@bw"].Value = searchResult2.BW;
|
|
command.Parameters["@feclp"].Value = searchResult2.FECLP;
|
|
command.Parameters["@transmode"].Value = searchResult2.TransMode;
|
|
command.Parameters["@guard"].Value = searchResult2.Guard;
|
|
command.Parameters["@hierarchy"].Value = searchResult2.Hierarchy;
|
|
command.Parameters["@plp"].Value = searchResult2.PLP;
|
|
command.Parameters["@lid"].Value = searchResult2.LID;
|
|
command.Parameters["@s1type"].Value = searchResult2.S1Type;
|
|
command.Parameters["@payload"].Value = searchResult2.Payload;
|
|
command.Parameters["@bw_ext"].Value = searchResult2.BwExt;
|
|
command.Parameters["@mode_t2"].Value = searchResult2.ModeT2;
|
|
command.Parameters["@ver_t2"].Value = searchResult2.VerT2;
|
|
command.Parameters["@cell_id"].Value = searchResult2.CellID;
|
|
command.Parameters["@net_id"].Value = searchResult2.NetID;
|
|
}
|
|
|
|
command.ExecuteNonQuery();
|
|
command.Dispose();
|
|
connection.Close();
|
|
}
|
|
}
|
|
|
|
public void UpdateTransponderState(DbBlindscanJob jobInDb, bool satellite, SearchResult searchResult,BlindscanResultState blindscanResultState, SearchResult2 searchResult2)
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
if (satellite)
|
|
{
|
|
command.CommandText =
|
|
"UPDATE skyscraper5_blindscan_search_results\r\n" +
|
|
"SET state = @state, dateupdated = CURRENT_TIMESTAMP, version = version + 1\r\n" +
|
|
"WHERE related_job = @related_job AND satellite = TRUE AND freq = @freq AND pol = @pol";
|
|
command.Parameters.AddWithValue("@freq", NpgsqlDbType.Integer, searchResult.Freq);
|
|
command.Parameters.AddWithValue("@pol", NpgsqlDbType.Integer, searchResult.Pol);
|
|
}
|
|
else
|
|
{
|
|
command.CommandText =
|
|
"UPDATE skyscraper5_blindscan_search_results\r\n" +
|
|
"SET state = @state, dateupdated = CURRENT_TIMESTAMP, version = version + 1\r\n" +
|
|
"WHERE related_job = @related_job AND satellite = FALSE AND freq = @freq AND pol = 0";
|
|
command.Parameters.AddWithValue("@freq", NpgsqlDbType.Integer, searchResult2.Freq);
|
|
}
|
|
|
|
command.Parameters.AddWithValue("@related_job", NpgsqlDbType.Uuid, jobInDb.JobGuid);
|
|
command.Parameters.AddWithValue("@state", NpgsqlDbType.Integer, (int)blindscanResultState);
|
|
int i = command.ExecuteNonQuery();
|
|
if (i != 1)
|
|
throw new DBConcurrencyException(String.Format("Somehow I got {0} matches when I expected one.", i));
|
|
command.Dispose();
|
|
connection.Close();
|
|
}
|
|
}
|
|
|
|
public void InsertTransponderService(DbBlindscanJob jobInDb, bool resultSatellite, SearchResult resultSr1,SearchResult2 resultSr2, HumanReadableService humanReadableService)
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"insert into skyscraper5_blindscan_services (related_job, freq, pol, sid, provider_name, service_name, ca_id,service_type)\r\n" +
|
|
"values (@related_job,@freq,@pol,@sid,@provider_name,@service_name,@ca_id,@service_type)";
|
|
command.Parameters.AddWithValue("@related_job", NpgsqlDbType.Uuid, jobInDb.JobGuid);
|
|
if (resultSatellite)
|
|
{
|
|
command.Parameters.AddWithValue("@freq", NpgsqlDbType.Integer, resultSr1.Freq);
|
|
command.Parameters.AddWithValue("@pol", NpgsqlDbType.Integer, resultSr1.Pol);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.AddWithValue("@freq", NpgsqlDbType.Integer, resultSr2.Freq);
|
|
command.Parameters.AddWithValue("@pol", NpgsqlDbType.Integer, 0);
|
|
}
|
|
|
|
command.Parameters.AddWithValue("@sid", NpgsqlDbType.Integer, (int)humanReadableService.ServiceId);
|
|
command.Parameters.AddWithValue("@provider_name", NpgsqlDbType.Text, humanReadableService.ProviderName.Trim((char)0));
|
|
string serviceName = humanReadableService.ServiceName.Trim((char)0).Trim();
|
|
command.Parameters.AddWithValue("@service_name", NpgsqlDbType.Text, serviceName);
|
|
|
|
if (humanReadableService.CaId.HasValue)
|
|
command.Parameters.AddWithValue("@ca_id", NpgsqlDbType.Integer, (int)humanReadableService.CaId.Value);
|
|
else
|
|
command.Parameters.AddWithValue("@ca_id", NpgsqlDbType.Integer, DBNull.Value);
|
|
|
|
command.Parameters.AddWithValue("@service_type", NpgsqlDbType.Integer, (int)humanReadableService.ServiceType);
|
|
|
|
command.ExecuteNonQuery();
|
|
command.Dispose();
|
|
connection.Close();
|
|
}
|
|
}
|
|
|
|
public bool TestForIncompleteJob()
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "SELECT uuid FROM skyscraper5_blindscan_jobs WHERE horizontal_low < 100 OR horizontal_high < 100 OR vertical_low < 100 OR vertical_high < 100";
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
bool result = dataReader.Read();
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
connection.Close();
|
|
return result;
|
|
}
|
|
}
|
|
|
|
public DbBlindscanJob GetPastBlindscanJob(long offset)
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "SELECT * FROM skyscraper5_blindscan_jobs ORDER BY dateadded DESC LIMIT 1 OFFSET @pastindex";
|
|
command.Parameters.AddWithValue("@pastindex", NpgsqlDbType.Integer, (int)offset);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
DbBlindscanJob result = null;
|
|
if (dataReader.Read())
|
|
{
|
|
Guid uuid = dataReader.GetGuid(0);
|
|
PhysicalAddress tuner_mac = (PhysicalAddress)dataReader.GetValue(1);
|
|
int tuner_std = dataReader.GetInt32(2);
|
|
int diseqc_index = dataReader.GetInt32(3);
|
|
int horizontal_low = dataReader.GetInt32(4);
|
|
int horizontal_high = dataReader.GetInt32(5);
|
|
int vertical_low = dataReader.GetInt32(6);
|
|
int vertical_high = dataReader.GetInt32(7);
|
|
DateTime date_added = dataReader.GetDateTime(8);
|
|
DateTime? date_updated = null;
|
|
if (!dataReader.IsDBNull(9))
|
|
date_updated = dataReader.GetDateTime(9);
|
|
int version = dataReader.GetInt32(10);
|
|
double gps_lon = dataReader.GetDouble(11);
|
|
double gps_lat = dataReader.GetDouble(12);
|
|
int sat_position = dataReader.GetInt32(13);
|
|
int serial = dataReader.GetInt32(14);
|
|
string note = null;
|
|
if (!dataReader.IsDBNull(15))
|
|
note = dataReader.GetString(15);
|
|
result = new DbBlindscanJob(uuid, tuner_mac, (STD_TYPE)tuner_std, diseqc_index, new DummyGpsReceiver(true,(float)gps_lat,(float)gps_lon), SatellitePosition.FromChecksum(sat_position));
|
|
result.DateAdded = date_added;
|
|
result.HorizontalHighState = (DbBlindscanJobPolarizationStatus)horizontal_high;
|
|
result.HorizontalLowState = (DbBlindscanJobPolarizationStatus)horizontal_low;
|
|
result.VerticalHighState = (DbBlindscanJobPolarizationStatus)vertical_high;
|
|
result.VerticalLowState = (DbBlindscanJobPolarizationStatus)vertical_low;
|
|
}
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
return result;
|
|
}
|
|
}
|
|
|
|
public void DeleteBlindscanJob(Guid guid)
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
connection.Open();
|
|
NpgsqlTransaction transaction = connection.BeginTransaction();
|
|
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "DELETE FROM skyscraper5_blindscan_services WHERE related_job = @uuid";
|
|
command.Parameters.AddWithValue("@uuid", NpgsqlDbType.Uuid, guid);
|
|
int servicesRemoved = command.ExecuteNonQuery();
|
|
command.Dispose();
|
|
|
|
command = connection.CreateCommand();
|
|
command.CommandText = "DELETE FROM skyscraper5_blindscan_search_results WHERE related_job = @uuid";
|
|
command.Parameters.AddWithValue("@uuid", NpgsqlDbType.Uuid, guid);
|
|
int searchResultRemoves = command.ExecuteNonQuery();
|
|
command.Dispose();
|
|
|
|
command = connection.CreateCommand();
|
|
command.CommandText = "DELETE FROM skyscraper5_blindscan_jobs WHERE uuid = @uuid";
|
|
command.Parameters.AddWithValue("@uuid", NpgsqlDbType.Uuid, guid);
|
|
int searchResultJobs = command.ExecuteNonQuery();
|
|
command.Dispose();
|
|
|
|
if (searchResultJobs != 1)
|
|
throw new DataException(String.Format("Expected to delete 1 row, but removed {0}", searchResultJobs));
|
|
|
|
transaction.Commit();
|
|
transaction.Dispose();
|
|
connection.Close();
|
|
}
|
|
}
|
|
|
|
public void DeleteBlindscanResults(Guid uuid, int pol)
|
|
{
|
|
pol++;
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "SELECT freq, pol, state FROM skyscraper5_blindscan_search_results WHERE related_job = @uuid AND polarity_index = @pol";
|
|
command.Parameters.AddWithValue("@uuid", NpgsqlDbType.Uuid, uuid);
|
|
command.Parameters.AddWithValue("@pol", NpgsqlDbType.Integer, pol);
|
|
NpgsqlDataReader reader = command.ExecuteReader();
|
|
List<Tuple<int, int, BlindscanResultState>> toBeDeleted = new List<Tuple<int, int, BlindscanResultState>>();
|
|
while (reader.Read())
|
|
{
|
|
int left = reader.GetInt32(0);
|
|
int mid = reader.GetInt32(1);
|
|
BlindscanResultState right = (BlindscanResultState)reader.GetInt32(2);
|
|
toBeDeleted.Add(new Tuple<int, int, BlindscanResultState>(left, mid, right));
|
|
}
|
|
reader.Close();
|
|
command.Dispose();
|
|
|
|
NpgsqlTransaction transaction = connection.BeginTransaction();
|
|
foreach (Tuple<int, int, BlindscanResultState> deleteMe in toBeDeleted)
|
|
{
|
|
switch (deleteMe.Item3)
|
|
{
|
|
case BlindscanResultState.Found:
|
|
//not yet touched, no services to be deleted.
|
|
break;
|
|
case BlindscanResultState.Tuning:
|
|
//failed to touch, no serives to be deleted
|
|
break;
|
|
case BlindscanResultState.Done:
|
|
DeleteBlindscanServices(connection, uuid, deleteMe.Item1, deleteMe.Item2);
|
|
break;
|
|
default:
|
|
throw new NotImplementedException(deleteMe.Item3.ToString());
|
|
}
|
|
}
|
|
|
|
command = connection.CreateCommand();
|
|
command.CommandText = "DELETE FROM skyscraper5_blindscan_search_results WHERE related_job = @uuid AND polarity_index = @pol";
|
|
command.Parameters.AddWithValue("@uuid", NpgsqlDbType.Uuid, uuid);
|
|
command.Parameters.AddWithValue("@pol", NpgsqlDbType.Integer, pol);
|
|
command.ExecuteNonQuery();
|
|
command.Dispose();
|
|
|
|
SetBlindscanPolarityState(connection, uuid, pol, DbBlindscanJobPolarizationStatus.SELECTED_WAITING);
|
|
|
|
transaction.Commit();
|
|
transaction.Dispose();
|
|
connection.Close();
|
|
}
|
|
}
|
|
|
|
private void SetBlindscanPolarityState(NpgsqlConnection connection, Guid uuid, int polarityIndex, DbBlindscanJobPolarizationStatus newStatus)
|
|
{
|
|
string updateColumn = null;
|
|
switch (polarityIndex)
|
|
{
|
|
case 1: updateColumn = "horizontal_low"; break;
|
|
case 2: updateColumn = "horizontal_high"; break;
|
|
case 3: updateColumn = "vertical_low"; break;
|
|
case 4: updateColumn = "vertical_high"; break;
|
|
default: throw new ArgumentOutOfRangeException(nameof(polarityIndex));
|
|
}
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = String.Format("UPDATE skyscraper5_blindscan_jobs SET {0} = @value, dateupdated = CURRENT_TIMESTAMP, version = version + 1 WHERE uuid = @uuid", updateColumn);
|
|
command.Parameters.AddWithValue("@value", NpgsqlDbType.Integer, (int)newStatus);
|
|
command.Parameters.AddWithValue("@uuid", NpgsqlDbType.Uuid, uuid);
|
|
int result = command.ExecuteNonQuery();
|
|
if (result != 1)
|
|
throw new DataException(String.Format("Expected to update 1 row, but got {0}", result));
|
|
command.Dispose();
|
|
}
|
|
|
|
private void DeleteBlindscanServices(NpgsqlConnection connection, Guid uuid, int freq, int pol)
|
|
{
|
|
NpgsqlCommand cmd = connection.CreateCommand();
|
|
cmd.CommandText = "DELETE FROM skyscraper5_blindscan_services WHERE related_job = @uuid AND freq = @freq AND pol = @pol";
|
|
cmd.Parameters.AddWithValue("@uuid", NpgsqlDbType.Uuid, uuid);
|
|
cmd.Parameters.AddWithValue("@freq", NpgsqlDbType.Integer, freq);
|
|
cmd.Parameters.AddWithValue("@pol", NpgsqlDbType.Integer, pol);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public void MoveBlScanResultsToAnotherJob(Guid moveFrom, Guid moveTo, int polarity)
|
|
{
|
|
polarity++;
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
//Services, die bewegt werden müssen finden
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "SELECT freq, pol FROM skyscraper5_blindscan_search_results WHERE related_job = @uuid AND polarity_index = @pol";
|
|
command.Parameters.AddWithValue("@uuid", NpgsqlDbType.Uuid, moveFrom);
|
|
command.Parameters.AddWithValue("@pol", NpgsqlDbType.Integer, polarity);
|
|
NpgsqlDataReader reader = command.ExecuteReader();
|
|
List<Tuple<int, int>> toBeMoved = new List<Tuple<int, int>>();
|
|
while (reader.Read())
|
|
{
|
|
int left = reader.GetInt32(0);
|
|
int mid = reader.GetInt32(1);
|
|
toBeMoved.Add(new Tuple<int, int>(left, mid));
|
|
}
|
|
reader.Close();
|
|
command.Dispose();
|
|
|
|
NpgsqlTransaction transaction = connection.BeginTransaction();
|
|
|
|
//Services bewegen
|
|
int totalServices = 0;
|
|
command = connection.CreateCommand();
|
|
command.CommandText = "UPDATE skyscraper5_blindscan_services SET related_job = @moveTo WHERE freq = @freq AND pol = @pol AND related_job = @moveFrom";
|
|
foreach (Tuple<int, int> moveMe in toBeMoved)
|
|
{
|
|
command.Parameters.Clear();
|
|
command.Parameters.AddWithValue("@moveFrom", NpgsqlDbType.Uuid, moveFrom);
|
|
command.Parameters.AddWithValue("@moveTo", NpgsqlDbType.Uuid, moveTo);
|
|
command.Parameters.AddWithValue("@freq", NpgsqlDbType.Integer, moveMe.Item1);
|
|
command.Parameters.AddWithValue("@pol", NpgsqlDbType.Integer, moveMe.Item2);
|
|
totalServices += command.ExecuteNonQuery();
|
|
}
|
|
command.Dispose();
|
|
|
|
//Transponder bewegen
|
|
command = connection.CreateCommand();
|
|
command.CommandText = "UPDATE skyscraper5_blindscan_search_results SET related_job = @moveTo WHERE related_job = @moveFrom AND polarity_index = @polarity_index";
|
|
command.Parameters.AddWithValue("@moveTo", NpgsqlDbType.Uuid, moveTo);
|
|
command.Parameters.AddWithValue("@moveFrom", NpgsqlDbType.Uuid, moveFrom);
|
|
command.Parameters.AddWithValue("@pol", NpgsqlDbType.Integer, polarity);
|
|
command.ExecuteNonQuery();
|
|
command.Dispose();
|
|
|
|
SetBlindscanPolarityState(connection, moveFrom, polarity, DbBlindscanJobPolarizationStatus.SELECTED_WAITING);
|
|
SetBlindscanPolarityState(connection, moveTo, polarity, DbBlindscanJobPolarizationStatus.SELECTED_DONE);
|
|
|
|
transaction.Commit();
|
|
transaction.Dispose();
|
|
connection.Close();
|
|
}
|
|
}
|
|
|
|
public IEnumerable<DbBlindscanJob> GetDbBlindscanJobs()
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
//Services, die bewegt werden müssen finden
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "SELECT * FROM skyscraper5_blindscan_jobs ORDER BY dateadded DESC";
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
while (dataReader.Read())
|
|
{
|
|
Guid uuid = dataReader.GetGuid("uuid");
|
|
DateTime dateAdded = dataReader.GetDateTime("dateadded");
|
|
DbBlindscanJobPolarizationStatus horizontalLow = (DbBlindscanJobPolarizationStatus)dataReader.GetInt32("horizontal_low");
|
|
DbBlindscanJobPolarizationStatus horizontalHigh = (DbBlindscanJobPolarizationStatus)dataReader.GetInt32("horizontal_high");
|
|
DbBlindscanJobPolarizationStatus verticalLow = (DbBlindscanJobPolarizationStatus)dataReader.GetInt32("vertical_low");
|
|
DbBlindscanJobPolarizationStatus verticalHigh = (DbBlindscanJobPolarizationStatus)dataReader.GetInt32("vertical_high");
|
|
PhysicalAddress tunerMac = (PhysicalAddress)dataReader.GetValue("tuner_mac");
|
|
STD_TYPE tunerStd = (STD_TYPE)dataReader.GetInt32("tuner_std");
|
|
DummyGpsReceiver dgr = null;
|
|
if (!dataReader.IsDBNull("gps_lon"))
|
|
{
|
|
float gpsLon = (float)dataReader.GetDouble("gps_lon");
|
|
float gpsLat = (float)dataReader.GetDouble("gps_lat");
|
|
dgr = new DummyGpsReceiver(true, gpsLat, gpsLon);
|
|
}
|
|
|
|
SatellitePosition satPositionObj = null;
|
|
if (!dataReader.IsDBNull("sat_position"))
|
|
{
|
|
int satPosition = dataReader.GetInt32("sat_position");
|
|
satPositionObj = SatellitePosition.FromChecksum(satPosition);
|
|
}
|
|
int diseqcIndex = dataReader.GetInt32("diseqc_index");
|
|
int version = dataReader.GetInt32("version");
|
|
|
|
|
|
DbBlindscanJob child = new DbBlindscanJob(uuid, tunerMac, tunerStd, diseqcIndex, dgr, satPositionObj);
|
|
child.DateAdded = dateAdded;
|
|
child.HorizontalLowState = horizontalLow;
|
|
child.HorizontalHighState = horizontalHigh;
|
|
child.VerticalLowState = verticalLow;
|
|
child.VerticalHighState = verticalHigh;
|
|
yield return child;
|
|
}
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
connection.Close();
|
|
}
|
|
}
|
|
|
|
public IEnumerable<Tuple<int, SatelliteDeliverySystemDescriptor.PolarizationEnum>> GetBlindscanResultFrequencies(Guid selectedGuid)
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText = "SELECT freq, pol FROM skyscraper5_blindscan_search_results WHERE related_job = @job";
|
|
command.Parameters.AddWithValue("@job", NpgsqlDbType.Uuid, selectedGuid);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
while (dataReader.Read())
|
|
{
|
|
int freq = dataReader.GetInt32(0);
|
|
int pol = dataReader.GetInt32(1);
|
|
Tuple<int, SatelliteDeliverySystemDescriptor.PolarizationEnum> child = new Tuple<int, SatelliteDeliverySystemDescriptor.PolarizationEnum>(freq, (SatelliteDeliverySystemDescriptor.PolarizationEnum)pol);
|
|
yield return child;
|
|
}
|
|
dataReader.Close();
|
|
dataReader.Dispose();
|
|
connection.Close();
|
|
}
|
|
}
|
|
}
|
|
}
|