NTP Analyzer  0.8.2
Analyze the operation of time servers
HostReadingDatabaseMapper.cs
Go to the documentation of this file.
1 //
2 // Copyright (c) 2013-2017 Carsten Sonne Larsen <cs@innolan.net>
3 //
4 // Permission is hereby granted, free of charge, to any person obtaining a copy
5 // of this software and associated documentation files (the "Software"), to deal
6 // in the Software without restriction, including without limitation the rights
7 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8 // copies of the Software, and to permit persons to whom the Software is
9 // furnished to do so, subject to the following conditions:
10 //
11 // The above copyright notice and this permission notice shall be included in
12 // all copies or substantial portions of the Software.
13 //
14 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
20 // THE SOFTWARE.
21 
22 using System;
23 using System.Collections.Generic;
24 using System.Diagnostics.CodeAnalysis;
25 using Ntp.Analyzer.Data.Log;
26 using Ntp.Analyzer.Objects;
27 using Ntp.Common.Log;
28 using Ntp.Data.Log;
29 
30 namespace Ntp.Analyzer.Data.Sql
31 {
35  public sealed class HostReadingDatabaseMapper : FilteredSqlDatabaseMapper<HostReading>
36  {
38  HostDatabaseMapper hostMapper,
39  PeerDatabaseMapper peerMapper,
40  LogBase log)
41  : base(log)
42  {
43  this.hostMapper = hostMapper;
44  this.peerMapper = peerMapper;
45  }
46 
47  private const string SelectSql =
48  "SELECT " +
49  "id, time, zone, hostId, peerId, [offset], jitter, frequency, stability " +
50  "FROM hostReading";
51 
52  private const string InsertSql =
53  "INSERT INTO hostReading ( time, zone, hostId, peerId, [offset], jitter, frequency, stability ) " +
54  "VALUES ( @time, @zone, @hostId, @peerId, @offset, @jitter, @frequency, @stability );{0};";
55 
56  private readonly HostDatabaseMapper hostMapper;
57  private readonly PeerDatabaseMapper peerMapper;
58 
59  protected override bool UseCache => false;
60 
61  protected override string TableName => "hostReading";
62 
63  protected override string CreateSql => "CREATE TABLE hostReading ( " +
64  " id {0} PRIMARY KEY, " +
65  " time TIMESTAMP NOT NULL, " +
66  " zone INT NOT NULL, " +
67  " hostId INT NOT NULL, " +
68  " peerId INT NOT NULL, " +
69  " [offset] DOUBLE PRECISION NOT NULL, " +
70  " jitter DOUBLE PRECISION NOT NULL, " +
71  " frequency DOUBLE PRECISION NOT NULL, " +
72  " stability DOUBLE PRECISION NOT NULL, " +
73  " FOREIGN KEY (hostId) REFERENCES host(id), " +
74  " FOREIGN KEY (peerId) REFERENCES peer(id) " +
75  "){1};";
76 
81  [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
82  public override IEnumerator<HostReading> GetEnumerator()
83  {
84  lock (MapperLocker)
85  {
86  bool error = false;
87 
88  try
89  {
90  Open();
91  Command.CommandText = PrepareSql(SelectSql);
92  Log.SqlExecute(Command.CommandText);
93  Reader = Command.ExecuteReader();
94  }
95  catch (Exception e)
96  {
97  Log.ReadError(TableName, e);
98  error = true;
99  }
100 
101  if (error)
102  yield break;
103 
104  while (Reader.Read())
105  {
106  int id = Convert.ToInt32(Reader["id"]);
107  var time = Convert.ToDateTime(Reader["time"]);
108  int zone = Convert.ToInt32(Reader["zone"]);
109  int hostId = Convert.ToInt32(Reader["hostId"]);
110  var host = hostMapper[hostId];
111  int peerId = Convert.ToInt32(Reader["peerId"]);
112  var peer = peerMapper[peerId];
113  double offset = Convert.ToDouble(Reader["offset"]);
114  double jitter = Convert.ToDouble(Reader["jitter"]);
115  double frequency = Convert.ToDouble(Reader["frequency"]);
116  double stability = Convert.ToDouble(Reader["stability"]);
117 
118  var reading = new HostReading(
119  id, time, zone, host, peer,
120  offset, jitter, frequency, stability);
121 
122  yield return reading;
123  }
124 
125  Close();
126  }
127  }
128 
129  [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
130  protected override void Insert(HostReading item)
131  {
132  lock (MapperLocker)
133  {
134  try
135  {
136  Open();
137  Command.CommandText = PrepareInsertSql(InsertSql);
138  Command.Parameters.Add(CreateParameter("@time", item.Time));
139  Command.Parameters.Add(CreateParameter("@zone", item.UtcOffset));
140  Command.Parameters.Add(CreateParameter("@hostId", item.Host.Id));
141  Command.Parameters.Add(CreateParameter("@peerId", item.Peer.Id));
142  Command.Parameters.Add(CreateParameter("@offset", item.Offset));
143  Command.Parameters.Add(CreateParameter("@jitter", item.Jitter));
144  Command.Parameters.Add(CreateParameter("@frequency", item.Frequency));
145  Command.Parameters.Add(CreateParameter("@stability", item.Stability));
146  Command.Prepare();
147  Log.SqlExecute(Command.CommandText, Command.Parameters);
148  var idObject = Command.ExecuteScalar();
149  item.SetId(Convert.ToInt32(idObject));
150  }
151  catch (Exception e)
152  {
153  Log.InsertError(TableName, e);
154  }
155  finally
156  {
157  Close();
158  }
159  }
160  }
161 
162  protected override void ReadContent()
163  {
164  throw new NotSupportedException(LogMessages.DatabaseCacheError);
165  }
166 
167  protected override void Update(HostReading item)
168  {
169  throw new NotSupportedException(string.Format(LogMessages.DatabaseNoUpdate, TableName));
170  }
171  }
172 }
OR/M mapper for tables with a large amount of rows.
HostReadingDatabaseMapper(HostDatabaseMapper hostMapper, PeerDatabaseMapper peerMapper, LogBase log)
void SetId(int id)
Sets the identifier after the object have been stored in persistent storage.
var e
Definition: bootstrap.min.js:6
override IEnumerator< HostReading > GetEnumerator()
Read all data from table in a sequential manner.