Korero – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 using System;
2 using System.Collections.Generic;
3 using System.Globalization;
4 using System.IO;
5 using System.Linq;
6 using System.Threading.Tasks;
7 using Korero.Chat;
8 using Microsoft.Data.Sqlite;
9 using Serilog;
10  
11 namespace Korero.Database
12 {
13 public class MessageDatabase : IDisposable
14 {
15 #region Static Fields and Constants
16  
17 private const string CreateSelfMessageTableSql =
18 "CREATE TABLE IF NOT EXISTS \"SelfMessage\" ( \"FirstName\" TEXT NOT NULL, \"LastName\" TEXT NOT NULL, \"Message\" TEXT NOT NULL, \"Time\" Text NOT NULL, PRIMARY KEY (\"Time\"))";
19  
20 private const string CreateMessageTableSql =
21 "CREATE TABLE IF NOT EXISTS \"Message\" ( \"FirstName\" TEXT NOT NULL, \"LastName\" TEXT NOT NULL, \"Message\" TEXT NOT NULL, \"Time\" Text NOT NULL, PRIMARY KEY (\"Time\"))";
22  
23 private const string CreateMessageGroupTableSql =
24 "CREATE TABLE IF NOT EXISTS \"MessageGroup\" ( \"FirstName\" TEXT NOT NULL, \"LastName\" TEXT NOT NULL, \"Group\" TEXT NOT NULL, \"Message\" TEXT NOT NULL, \"Time\" Text NOT NULL, PRIMARY KEY (\"Time\"))";
25  
26 private const string SaveMessageSql =
27 "INSERT INTO \"Message\" ( \"FirstName\", \"LastName\", \"Message\", \"Time\" ) VALUES ( @firstName, @lastName, @message, @time )";
28  
29 private const string SaveSelfMessageSql =
30 "INSERT INTO \"SelfMessage\" ( \"FirstName\", \"LastName\", \"Message\", \"Time\" ) VALUES ( @firstName, @lastName, @message, @time )";
31  
32 private const string SaveMessageGroupSql =
33 "INSERT INTO \"MessageGroup\" ( \"FirstName\", \"LastName\", \"Message\", \"Group\", \"Time\" ) VALUES ( @firstName, @lastName, @message, @group, @time )";
34  
35 private const string LoadMessagesSql =
36 "SELECT \"FirstName\", \"LastName\", \"Message\", \"Time\" FROM \"Message\" WHERE FirstName = @firstName AND LastName = @lastName ORDER BY \"Time\" DESC LIMIT @limit";
37  
38 private const string LoadSelfMessagesSql =
39 "SELECT \"FirstName\", \"LastName\", \"Message\", \"Time\" FROM \"SelfMessage\" WHERE FirstName = @firstName AND LastName = @lastName ORDER BY \"Time\" DESC LIMIT @limit";
40  
41 private const string LoadMessagesGroupSql =
42 "SELECT \"FirstName\", \"LastName\", \"Message\", \"Time\", \"Group\" FROM \"MessageGroup\" WHERE \"Group\" = @group ORDER BY \"Time\" DESC LIMIT @limit";
43  
44 private const string LoadConversationSql =
45 "SELECT DISTINCT \"FirstName\", \"LastName\" FROM \"Message\"";
46  
47 private const string LoadSelfConversationSql =
48 "SELECT DISTINCT \"FirstName\", \"LastName\" FROM \"SelfMessage\"";
49  
50 private const string LoadGroupConversationSql =
51 "SELECT DISTINCT \"Group\" FROM \"MessageGroup\"";
52  
53 private const string CreateConversationSeenTableSql =
54 "CREATE TABLE IF NOT EXISTS \"ConversationSeen\" ( \"Name\" TEXT NOT NULL, \"Seen\" TEXT DEFAULT '0', PRIMARY KEY (\"Name\"))";
55  
56 private const string LoadConversationSeenSql =
57 "SELECT \"Name\", \"Seen\" FROM \"ConversationSeen\" WHERE \"Name\" = @name";
58  
59 private const string MarkConversationSeenSql =
60 "REPLACE INTO \"ConversationSeen\" ( \"Name\", \"Seen\" ) VALUES ( @name, @seen )";
61  
62 #endregion
63  
64 #region Private Delegates, Events, Enums, Properties, Indexers and Fields
65  
66 private static string DatabaseDirectory => Path.Combine(
67 Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), @"Wizardry and Steamworks", @"Korero");
68  
69 private static string DatabaseFilePath => Path.Combine(DatabaseDirectory, @"Korero.db");
70  
71 #endregion
72  
73 #region Constructors, Destructors and Finalizers
74  
75 public MessageDatabase()
76 {
77 Directory.CreateDirectory(DatabaseDirectory);
78  
79 #pragma warning disable 4014
80 CreateTable(CreateSelfMessageTableSql);
81 #pragma warning restore 4014
82  
83 #pragma warning disable 4014
84 CreateTable(CreateMessageTableSql);
85 #pragma warning restore 4014
86 #pragma warning disable 4014
87 CreateTable(CreateMessageGroupTableSql);
88 #pragma warning restore 4014
89  
90 #pragma warning disable 4014
91 CreateTable(CreateConversationSeenTableSql);
92 #pragma warning restore 4014
93 }
94  
95 public void Dispose()
96 {
97 }
98  
99 #endregion
100  
101 #region Public Methods
102  
103 public async Task SaveSelfMessage(DatabaseMessage databaseMessage)
104 {
105 var parameters = new Dictionary<string, string>
106 {
107 {"firstName", databaseMessage.FirstName},
108 {"lastName", databaseMessage.LastName},
109 {"message", databaseMessage.Message},
110 {"time", databaseMessage.Time.ToString(Constants.DateTimeStamp)}
111 };
112  
113 await Execute(SaveSelfMessageSql, parameters);
114 }
115  
116 public async Task SaveMessage(DatabaseMessage databaseMessage)
117 {
118 var parameters = new Dictionary<string, string>
119 {
120 {"firstName", databaseMessage.FirstName},
121 {"lastName", databaseMessage.LastName},
122 {"message", databaseMessage.Message},
123 {"time", databaseMessage.Time.ToString(Constants.DateTimeStamp)}
124 };
125  
126 await Execute(SaveMessageSql, parameters);
127 }
128  
129 public async Task SaveGroupMessage(DatabaseMessageGroup databaseGroupMessage)
130 {
131 var parameters = new Dictionary<string, string>
132 {
133 {"firstName", databaseGroupMessage.FirstName},
134 {"lastName", databaseGroupMessage.LastName},
135 {"message", databaseGroupMessage.Message},
136 {"group", databaseGroupMessage.Group},
137 {"time", databaseGroupMessage.Time.ToString(Constants.DateTimeStamp)}
138 };
139  
140 await Execute(SaveMessageGroupSql, parameters);
141 }
142  
143 public async Task<IEnumerable<ConversationAvatar>> LoadSelfConversations()
144 {
145 var queries = new List<string>
146 {
147 "FirstName",
148 "LastName"
149 };
150  
151 var result = new List<ConversationAvatar>();
152 var databaseResults =
153 await Execute(LoadSelfConversationSql, new Dictionary<string, string>(), queries);
154  
155 for (var i = 0; i < databaseResults.Count; ++i)
156 {
157 var firstName = databaseResults.Columns["FirstName"][i];
158 var lastName = databaseResults.Columns["LastName"][i];
159 result.Add(new ConversationAvatar(firstName, lastName, 1));
160 }
161  
162 return result;
163 }
164  
165 public async Task<IEnumerable<ConversationAvatar>> LoadConversations()
166 {
167 var queries = new List<string>
168 {
169 "FirstName",
170 "LastName"
171 };
172  
173 var result = new List<ConversationAvatar>();
174 var databaseResults =
175 await Execute(LoadConversationSql, new Dictionary<string, string>(), queries);
176  
177 for (var i = 0; i < databaseResults.Count; ++i)
178 {
179 var firstName = databaseResults.Columns["FirstName"][i];
180 var lastName = databaseResults.Columns["LastName"][i];
181 var seenDatabaseResults = await Execute(LoadConversationSeenSql, new Dictionary<string, string>
182 {
183 {"name", $"{firstName} {lastName}"}
184 }, new List<string>(new[]
185 {
186 "Name",
187 "Seen"
188 }));
189 var seen = 0;
190 if (seenDatabaseResults.Columns.Count > 0)
191 {
192 if (!int.TryParse(seenDatabaseResults.Columns["Seen"].FirstOrDefault(), out seen))
193 {
194 seen = 0;
195 }
196 }
197  
198 result.Add(new ConversationAvatar(firstName, lastName, seen));
199 }
200  
201 return result;
202 }
203  
204 public async Task<IEnumerable<ConversationGroup>> LoadGroupConversations()
205 {
206 var queries = new List<string>
207 {
208 "Group"
209 };
210  
211 var result = new List<ConversationGroup>();
212 var databaseResults =
213 await Execute(LoadGroupConversationSql, new Dictionary<string, string>(), queries);
214  
215 for (var i = 0; i < databaseResults.Count; ++i)
216 {
217 var group = databaseResults.Columns["Group"][i];
218 var seenDatabaseResults = await Execute(LoadConversationSeenSql, new Dictionary<string, string>
219 {
220 {"name", $"{group}"}
221 }, new List<string>(new[]
222 {
223 "Name",
224 "Seen"
225 }));
226 var seen = 0;
227 if (seenDatabaseResults.Columns.Count > 0)
228 {
229 if (!int.TryParse(seenDatabaseResults.Columns["Seen"].FirstOrDefault(), out seen))
230 {
231 seen = 0;
232 }
233 }
234  
235 result.Add(new ConversationGroup(group, seen));
236 }
237  
238 return result;
239 }
240  
241 public async Task<IEnumerable<DatabaseMessage>> LoadMessages(string firstName, string lastName, int limit)
242 {
243 var parameters = new Dictionary<string, string>
244 {
245 {"firstName", firstName},
246 {"lastName", lastName},
247 {"limit", $"{limit}"}
248 };
249 var queries = new List<string>
250 {
251 "Message",
252 "Time"
253 };
254  
255 var result = new List<DatabaseMessage>();
256 var databaseResults =
257 await Execute(LoadMessagesSql, parameters, queries);
258  
259 for (var i = 0; i < databaseResults.Count; ++i)
260 {
261 var message = databaseResults.Columns["Message"][i];
262 var time = databaseResults.Columns["Time"][i];
263 if (!DateTime.TryParseExact(time, Constants.DateTimeStamp,
264 CultureInfo.InvariantCulture, DateTimeStyles.None, out var dateTime))
265 {
266 continue;
267 }
268  
269 var databaseMessage =
270 new DatabaseMessage(firstName, lastName, message, dateTime);
271  
272 result.Add(databaseMessage);
273 }
274  
275 result.Reverse();
276  
277 return result;
278 }
279  
280 public async Task<IEnumerable<DatabaseMessage>> LoadSelfMessages(string firstName, string lastName, int limit)
281 {
282 var parameters = new Dictionary<string, string>
283 {
284 {"firstName", firstName},
285 {"lastName", lastName},
286 {"limit", $"{limit}"}
287 };
288 var queries = new List<string>
289 {
290 "Message",
291 "Time"
292 };
293  
294 var result = new List<DatabaseMessage>();
295 var databaseResults =
296 await Execute(LoadSelfMessagesSql, parameters, queries);
297  
298 for (var i = 0; i < databaseResults.Count; ++i)
299 {
300 var message = databaseResults.Columns["Message"][i];
301 var time = databaseResults.Columns["Time"][i];
302 if (!DateTime.TryParseExact(time, Constants.DateTimeStamp,
303 CultureInfo.InvariantCulture, DateTimeStyles.None, out var dateTime))
304 {
305 continue;
306 }
307  
308 var databaseMessage =
309 new DatabaseMessage(firstName, lastName, message, dateTime);
310  
311 result.Add(databaseMessage);
312 }
313  
314 result.Reverse();
315  
316 return result;
317 }
318  
319 public async Task<IEnumerable<DatabaseMessageGroup>> LoadMessagesGroup(string group, int limit)
320 {
321 var parameters = new Dictionary<string, string>
322 {
323 {"group", group},
324 {"limit", $"{limit}"}
325 };
326 var queries = new List<string>
327 {
328 "FirstName",
329 "LastName",
330 "Message",
331 "Time"
332 };
333  
334 var result = new List<DatabaseMessageGroup>();
335 var databaseResults =
336 await Execute(LoadMessagesGroupSql, parameters, queries);
337  
338 for (var i = 0; i < databaseResults.Count; ++i)
339 {
340 var firstName = databaseResults.Columns["FirstName"][i];
341 var lastName = databaseResults.Columns["LastName"][i];
342 var message = databaseResults.Columns["Message"][i];
343 var time = databaseResults.Columns["Time"][i];
344 if (!DateTime.TryParseExact(time, Constants.DateTimeStamp,
345 CultureInfo.InvariantCulture, DateTimeStyles.None, out var dateTime))
346 {
347 continue;
348 }
349  
350 var databaseMessageGroup =
351 new DatabaseMessageGroup(firstName, lastName, message, dateTime);
352  
353 result.Add(databaseMessageGroup);
354 }
355  
356 result.Reverse();
357  
358 return result;
359 }
360  
361 public async Task MarkConversationSeen(string name, bool seen)
362 {
363 var parameters = new Dictionary<string, string>
364 {
365 {"name", name},
366 {"seen", seen ? "1" : "0"}
367 };
368  
369 await Execute(MarkConversationSeenSql, parameters);
370 }
371  
372 #endregion
373  
374 #region Private Methods
375  
376 private static async Task Execute(string sql, Dictionary<string, string> parameters)
377 {
378 await Execute(sql, parameters, new List<string>());
379 }
380  
381 private static async Task<DatabaseResults> Execute(string sql, Dictionary<string, string> parameters,
382 List<string> queries)
383 {
384 var result = new DatabaseResults();
385  
386 var connectionString = new SqliteConnectionStringBuilder
387 {
388 ConnectionString = $"Data Source={DatabaseFilePath};",
389 Mode = SqliteOpenMode.ReadWriteCreate
390 };
391  
392 try
393 {
394 using (var sqliteConnection =
395 new SqliteConnection(connectionString.ConnectionString))
396 {
397 await sqliteConnection.OpenAsync();
398  
399 using (var dbTransaction = sqliteConnection.BeginTransaction())
400 {
401 using (var sqliteCommand = new SqliteCommand(sql,
402 sqliteConnection, dbTransaction))
403 {
404 try
405 {
406 foreach (var parameter in parameters)
407 {
408 var sqliteParameter = new SqliteParameter(parameter.Key, parameter.Value);
409  
410 sqliteCommand.Parameters.Add(sqliteParameter);
411 }
412  
413 sqliteCommand.Prepare();
414 using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync())
415 {
416 while (await sqlDataReader.ReadAsync())
417 {
418 foreach (var query in queries)
419 {
420 var data = (string) sqlDataReader[query];
421  
422 if (result.Columns.ContainsKey(query))
423 {
424 result.Columns[query].Add(data);
425 continue;
426 }
427  
428 result.Columns.Add(query, new List<string>());
429 result.Columns[query].Add(data);
430 }
431  
432 result.Count++;
433 }
434 }
435  
436 dbTransaction.Commit();
437 }
438 catch
439 {
440 dbTransaction.Rollback();
441  
442 throw;
443 }
444 }
445 }
446 }
447 }
448 catch (Exception ex)
449 {
450 Log.Warning(ex, "Unable to execute SQL.");
451 }
452  
453 return result;
454 }
455  
456 private static async Task CreateTable(string sql)
457 {
458 var connectionString = new SqliteConnectionStringBuilder
459 {
460 ConnectionString = $"Data Source={DatabaseFilePath};",
461 Mode = SqliteOpenMode.ReadWriteCreate
462 };
463  
464 try
465 {
466 using (var sqliteConnection =
467 new SqliteConnection(connectionString.ConnectionString))
468 {
469 await sqliteConnection.OpenAsync();
470  
471 using (var dbTransaction = sqliteConnection.BeginTransaction())
472 {
473 // Create the table if it does not exist.
474 using (var sqliteCommand = new SqliteCommand(sql,
475 sqliteConnection, dbTransaction))
476 {
477 try
478 {
479 sqliteCommand.ExecuteNonQuery();
480 dbTransaction.Commit();
481 }
482 catch
483 {
484 dbTransaction.Rollback();
485  
486 throw;
487 }
488 }
489 }
490 }
491 }
492 catch (Exception ex)
493 {
494 Log.Warning(ex, "Unable to create database.");
495 }
496 }
497  
498 #endregion
499 }
500 }