NTP Analyzer  0.8.2
Analyze the operation of time servers
PeerActivityDatabaseMapper.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.Diagnostics.CodeAnalysis;
24 using Ntp.Analyzer.Data.Log;
25 using Ntp.Analyzer.Objects;
26 using Ntp.Common.Log;
27 using Ntp.Data.Log;
28 
29 namespace Ntp.Analyzer.Data.Sql
30 {
34  public sealed class PeerActivityDatabaseMapper : SqlDatabaseMapper<PeerActivity>
35  {
37  HostDatabaseMapper hostMapper,
38  PeerDatabaseMapper peerMapper,
39  LogBase log)
40  : base(log)
41  {
42  this.hostMapper = hostMapper;
43  this.peerMapper = peerMapper;
44  }
45 
46  private const string SelectSql =
47  "SELECT id, lastActive, zone, hide, hostId, peerId FROM peerActivity";
48 
49  private const string InsertSql =
50  "INSERT INTO peerActivity( lastActive, zone, hide, hostId, peerId ) " +
51  "VALUES( @lastActive, @zone, @hide, @hostId, @peerId );{0};";
52 
53  private const string UpdateSql =
54  "UPDATE peerActivity SET lastActive = @lastActive, zone = @zone WHERE id = @id";
55 
56  private readonly HostDatabaseMapper hostMapper;
57  private readonly PeerDatabaseMapper peerMapper;
58 
59  protected override bool UseCache => true;
60 
61  protected override string TableName => "peerActivity";
62 
63  protected override string CreateSql => "CREATE TABLE peerActivity ( " +
64  " id {0} PRIMARY KEY, " +
65  " lastActive TIMESTAMP NOT NULL, " +
66  " zone INT NOT NULL, " +
67  " hide BIT(1) NOT NULL, " +
68  " hostId INT NOT NULL, " +
69  " peerId INT NOT NULL, " +
70  " UNIQUE KEY (hostId, peerId), " +
71  " FOREIGN KEY (hostId) REFERENCES host(id), " +
72  " FOREIGN KEY (peerId) REFERENCES peer(id) " +
73  "){1};";
74 
75  [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
76  protected override void Insert(PeerActivity item)
77  {
78  lock (MapperLocker)
79  {
80  try
81  {
82  Open();
83  Command.CommandText = PrepareInsertSql(InsertSql);
84  Command.Parameters.Add(CreateParameter("@lastActive", item.LastActive));
85  Command.Parameters.Add(CreateParameter("@zone", item.UtcOffset));
86  Command.Parameters.Add(CreateParameter("@hide", item.Hide));
87  Command.Parameters.Add(CreateParameter("@hostId", item.Host.Id));
88  Command.Parameters.Add(CreateParameter("@peerId", item.Peer.Id));
89  Command.Prepare();
90  Log.SqlExecute(Command.CommandText, Command.Parameters);
91  var idObject = Command.ExecuteScalar();
92  item.SetId(Convert.ToInt32(idObject));
93  }
94  catch (Exception e)
95  {
96  Log.InsertError(TableName, e);
97  }
98  finally
99  {
100  Close();
101  }
102  }
103  }
104 
105  [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
106  protected override void ReadContent()
107  {
108  try
109  {
110  Open();
111  Command.CommandText = PrepareSql(SelectSql);
112  Log.SqlExecute(Command.CommandText);
113  Reader = Command.ExecuteReader();
114 
115  while (Reader.Read())
116  {
117  int id = Convert.ToInt32(Reader["id"]);
118  var time = Convert.ToDateTime(Reader["lastActive"]);
119  int zone = Convert.ToInt32(Reader["zone"]);
120  bool hide = Convert.ToBoolean(Reader["hide"]);
121  int hostId = Convert.ToInt32(Reader["hostId"]);
122  var host = hostMapper[hostId];
123  int peerId = Convert.ToInt32(Reader["peerId"]);
124  var peer = peerMapper[peerId];
125  var item = new PeerActivity(id, peer, host, time, zone, hide);
126  AddItem(item);
127  }
128  }
129  catch (Exception e)
130  {
131  Log.UpdateError(TableName, e);
132  }
133  finally
134  {
135  Close();
136  }
137  }
138 
139  [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
140  protected override void Update(PeerActivity item)
141  {
142  lock (MapperLocker)
143  {
144  try
145  {
146  Open();
147  Command.CommandText = PrepareSql(UpdateSql);
148  Command.Parameters.Add(CreateParameter("@lastActive", item.LastActive));
149  Command.Parameters.Add(CreateParameter("@zone", item.UtcOffset));
150  Command.Parameters.Add(CreateParameter("@id", item.Id));
151  Command.Prepare();
152  Log.SqlExecute(Command.CommandText, Command.Parameters);
153  Command.ExecuteNonQuery();
154  }
155  catch (Exception e)
156  {
157  Log.UpdateError(TableName, e);
158  }
159  finally
160  {
161  Close();
162  }
163  }
164  }
165  }
166 }
void SetId(int id)
Sets the identifier after the object have been stored in persistent storage.
var e
Definition: bootstrap.min.js:6
Base class for OR/M mappers. Can be used for mapping objects stored in SQL databases.
PeerActivityDatabaseMapper(HostDatabaseMapper hostMapper, PeerDatabaseMapper peerMapper, LogBase log)