/[xmltv_parser]/trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs
ViewVC logotype

Contents of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 164 - (show annotations) (download)
Fri Mar 15 16:57:50 2013 UTC (7 years, 4 months ago) by william
File size: 21481 byte(s)
+ when filtering GBPVR programs by date-range, generate a list of programs that were filtered-out

1 //#define SQLLITE_CONNECTION_TEST
2 using System;
3 using System.Collections.Generic;
4 using System.Linq;
5 using System.Text;
6 using System.Data.SQLite;
7 using System.Diagnostics;
8 using Enterprise.Logging;
9 using libxmltv;
10 namespace GBPVRProgramDatabaseFixer
11 {
12 public interface ISQLLITE
13 {
14 List<SQLLITE.IPROGRAMME> Programs { get; }
15 List<SQLLITE.IRECORDING_SCHEDULE> Recordings { get; }
16 List<SQLLITE.ICHANNEL> Channels { get; }
17 IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs);
18 List<SQLLITE.IPROGRAMME> FilterProgramsByDateRange(List<SQLLITE.IPROGRAMME> programs, IDateTimeRange range, out List<SQLLITE.IPROGRAMME> removed_programs);
19
20 }
21 public class SQLLITE : ISQLLITE
22 {
23 public static ISQLLITE Create(string database, EventHandler<EventArgs> OnInstanceCreated)
24 {
25 return new SQLLITE(database, OnInstanceCreated);
26 }
27
28 #region DATABASE DEFINITIONS
29
30 public interface ICHANNEL
31 {
32 Int64 oid { get; }
33 String name { get; }
34 String channelID { get; }
35 Int64 channel_number { get; }
36 String favourite_channel { get; }
37 String display_name { get; }
38 }
39 private class CHANNEL : ICHANNEL
40 {
41 public CHANNEL()
42 {
43 BaseDatabaseDefinition<CHANNEL>.CreateDefault(this);
44 }
45 //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
46
47 public static void Create(ref CHANNEL instance, SQLiteDataReader r, int index)
48 {
49 BaseDatabaseDefinition<CHANNEL>.Create(ref instance, r, index);
50 }
51 #region ICHANNEL members
52 public Int64 oid { get; set; }
53 public String name { get; set; }
54 public String channelID { get; set; }
55 public Int64 channel_number { get; set; }
56 public String favourite_channel { get; set; }
57 public String display_name { get; set; }
58 #endregion
59 }
60 public interface IRECORDING_SCHEDULE
61 {
62 Int64 oid { get; }
63 Int64 programme_oid { get; }
64 Int64 capture_source_oid { get; }
65 Int16 status { get; }
66 String filename { get; }
67 Int64 recording_type { get; }
68 Int64 recording_group { get; }
69 DateTime manual_start_time { get; }
70 DateTime manual_end_time { get; }
71 Int64 manual_channel_oid { get; }
72 Int64 quality_level { get; }
73 Int64 pre_pad_minutes { get; }
74 Int64 post_pad_minutes { get;}
75 Int32 priority { get; }
76 String conversion_profile { get; }
77 }
78
79 private static class BaseDatabaseDefinition<T>
80 {
81 public static void CreateDefault(T instance)
82 {
83 try
84 {
85 Type t = typeof(T);
86 var props = t.GetProperties();
87 foreach (var prop in props)
88 {
89 Type prop_type = prop.PropertyType;
90 object field_value = null;
91 try
92 {
93 if (prop_type == typeof(string))
94 {
95 field_value = string.Empty;
96 }
97 else
98 {
99 field_value = Activator.CreateInstance(prop_type);
100 }
101 }
102 catch (Exception ex)
103 {
104 throw ex;
105 }
106 prop.SetValue(instance, field_value, null);
107 }
108 }
109 catch (Exception ex)
110 {
111 throw ex;
112 }
113 }
114 public static void Create(ref T instance, SQLiteDataReader r, int index)
115 {
116 string field_name = r.GetName(index);
117 Type field_type = r.GetFieldType(index);
118 object field_value = r.GetValue(index);
119 //gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
120
121 Type t = typeof(T);
122 var props = t.GetProperties();
123 foreach (var prop in props)
124 {
125 if (prop.Name.ToLower() == field_name.ToLower())
126 {
127 if (prop.PropertyType == field_type)
128 {
129 Type db_type = field_value.GetType();
130 try
131 {
132 if (db_type == typeof(System.DBNull))
133 {
134 prop.SetValue(instance, null, null);
135 }
136 else
137 {
138 prop.SetValue(instance, field_value, null);
139 }
140
141 }
142 catch (Exception ex)
143 {
144 throw ex;
145 }
146 }
147 else
148 {
149 gLog.Verbose.Debug.WriteLine("Found Property: {0} but there was a type mismatch. Property Type: '{1}' Expected: '{2}'", prop.Name, prop.PropertyType.Name, field_type.Name);
150 throw new InvalidOperationException(string.Format("Found Property: {0} but there was a type mismatch. Property Type: '{1}' Expected: '{2}'", prop.Name, prop.PropertyType.Name, field_type.Name));
151 }
152 }
153 }
154
155 }
156 }
157
158 private class RECORDING_SCHEDULE : IRECORDING_SCHEDULE
159 {
160 public RECORDING_SCHEDULE()
161 {
162 BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this);
163 }
164 //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
165
166 public static void Create(ref RECORDING_SCHEDULE instance, SQLiteDataReader r, int index)
167 {
168 BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(ref instance, r, index);
169 }
170
171 #region IRECORDING_SCHEDULE members
172 public Int64 oid { get; set;}
173 public Int64 programme_oid { get; set;}
174 public Int64 capture_source_oid { get; set;}
175 public Int16 status { get; set; }
176 public String filename { get; set; }
177 public Int64 recording_type { get;set; }
178 public Int64 recording_group { get; set;}
179 public DateTime manual_start_time { get; set;}
180 public DateTime manual_end_time { get; set; }
181 public Int64 manual_channel_oid { get; set; }
182 public Int64 quality_level { get; set; }
183 public Int64 pre_pad_minutes { get; set; }
184 public Int64 post_pad_minutes { get; set; }
185 public Int32 priority { get; set; }
186 public String conversion_profile { get; set; }
187 #endregion
188 }
189
190 public interface IPROGRAMME
191 {
192 Int64 oid { get; }
193 String name { get; }
194 String sub_title { get; }
195 String description { get; }
196 DateTime start_time { get; }
197 DateTime end_time { get; }
198 Int64 channel_oid { get; }
199 String unique_identifier { get; }
200 String rating { get; }
201 }
202 private class PROGRAMME : IPROGRAMME
203 {
204 public PROGRAMME()
205 {
206 BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this);
207 }
208 //public PROGRAMME(SQLiteDataReader r, int index) : base(r, index) { }
209 public static void Create(ref PROGRAMME instance, SQLiteDataReader r, int index)
210 {
211 BaseDatabaseDefinition<PROGRAMME>.Create(ref instance, r, index);
212 }
213 #region IPROGRAMME members
214 public Int64 oid { get; set; }
215 public String name { get; set; }
216 public String sub_title { get; set; }
217 public String description { get; set; }
218 public DateTime start_time { get; set; }
219 public DateTime end_time { get; set; }
220 public Int64 channel_oid { get; set; }
221 public String unique_identifier { get; set; }
222 public String rating { get; set; }
223 #endregion
224 }
225 #endregion
226 private static class TABLES
227 {
228 public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
229 public const string PROGRAMME = "PROGRAMME";
230 public const string CHANNEL = "CHANNEL";
231 }
232
233 //public SQLLite() { }
234 protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
235 {
236 this.OnInstanceCreated = OnInstanceCreated;
237 //CreateConnection(database);
238 this.Database = database;
239 if (!CreateDatabaseBackup())
240 {
241 gLog.Error.WriteLine("Failed to backup database.");
242 return;
243 }
244 ConnectionTest();
245 ReadChannelData();
246 ReadRecodringScheduleData();
247 ReadProgrammeData();
248 OnCreatedInstance(this, new EventArgs());
249 }
250
251 [NonSerialized]
252 private EventHandler<EventArgs> _OnInstanceCreated;
253 private EventHandler<EventArgs> OnInstanceCreated { get { return _OnInstanceCreated; } set { _OnInstanceCreated = value; } }
254
255 private void OnCreatedInstance(object sender, EventArgs e)
256 {
257 if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
258 }
259
260 private string Database;
261 #region ISQLLITE members
262 public List<IPROGRAMME> Programs { get; private set; }
263 public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
264 public List<ICHANNEL> Channels { get; private set; }
265
266 public IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs)
267 {
268 var list = new List<IPROGRAMME>(programs.ToArray());
269 DateTime first = new DateTime();
270 DateTime last = new DateTime();
271 first = list.OrderBy(s => s.start_time).ToList().First().start_time;
272 last = list.OrderBy(s => s.start_time).ToList().Last().start_time;
273 gLog.Info.WriteLine("\tFirst: {0} = ({1})", first.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), first.ToDateTimeString());
274 gLog.Info.WriteLine("\tLast: {0} = ({1})", last.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), last.ToDateTimeString());
275 var range = DateTimeRange.Create(first, last);
276 return range;
277 }
278
279 public List<SQLLITE.IPROGRAMME> FilterProgramsByDateRange(List<SQLLITE.IPROGRAMME> programs, IDateTimeRange range, out List<SQLLITE.IPROGRAMME> removed_programs)
280 {
281 removed_programs = new List<IPROGRAMME>();
282 List<SQLLITE.IPROGRAMME> list = new List<IPROGRAMME>(programs.ToArray());
283 list = list.OrderBy(s => s.start_time).ToList();
284 List<SQLLITE.IPROGRAMME> list2 = new List<IPROGRAMME>();
285 if (range == null)
286 {
287 gLog.Warn.WriteLine("The DateTimeRange passed in is null...returning the original program list");
288 return list;
289 }
290 //gLog.Warn.WriteLine("FilterProgramsByDateRange has not been implemented");
291
292 foreach (var program in list)
293 {
294 if (program.start_time >= range.Start &&
295 program.start_time <= range.End)
296 {
297 list2.Add(program);
298 }
299 else
300 {
301 removed_programs.Add(program);
302 }
303 }
304 list2 = list2.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
305 removed_programs = removed_programs.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
306 return list2;
307 }
308 #endregion
309
310
311 private string CreateConnectionString()
312 {
313 string connection_string = string.Format("Data Source={0}", this.Database);
314 return connection_string;
315 }
316
317 private SQLiteConnection CreateConnection() { SQLiteConnection connection = new SQLiteConnection(CreateConnectionString()); return connection; }
318
319 [Conditional("SQLLITE_CONNECTION_TEST")]
320 private void ConnectionTest()
321 {
322 try
323 {
324 using (SQLiteConnection con = CreateConnection())
325 {
326 con.Open();
327 string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
328 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
329 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
330 {
331 using (SQLiteDataReader r = cmd.ExecuteReader())
332 {
333 if (!r.HasRows)
334 {
335 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
336 }
337 else
338 {
339 while (r.Read())
340 {
341 for (int i = 0; i < r.FieldCount; i++)
342 {
343 string field_name = r.GetName(i);
344 Type field_type = r.GetFieldType(i);
345 object field_value = r.GetValue(i);
346 gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
347 }
348 break;
349 }
350 }
351 }
352 }
353 con.Clone();
354 }
355 OnCreatedInstance(this, new EventArgs());
356 }
357 catch (Exception ex)
358 {
359 gLog.Error.WriteLine(ex.ToString());
360 }
361 }
362
363
364 private bool CreateDatabaseBackup()
365 {
366 try
367 {
368 string backup_file = string.Format("{0}.{1}", this.Database, DateTime.Now.ToString("yyyyMMddHHmmss"));
369 gLog.Info.WriteLine("Creating Database backup...");
370 gLog.Info.WriteLine("\tSource: {0}", this.Database);
371 gLog.Info.WriteLine("\tDestination: {0}", backup_file);
372
373 System.IO.File.Copy(this.Database, backup_file);
374 return true;
375 }
376 catch (Exception ex)
377 {
378 gLog.Error.WriteLine(ex.ToString());
379 return false;
380 }
381 }
382 private void ReadChannelData()
383 {
384 try
385 {
386 List<ICHANNEL> channels = new List<ICHANNEL>();
387 using (SQLiteConnection con = CreateConnection())
388 {
389 con.Open();
390 string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
391 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
392 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
393 {
394 using (SQLiteDataReader r = cmd.ExecuteReader())
395 {
396 if (!r.HasRows)
397 {
398 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
399 }
400 else
401 {
402 while (r.Read())
403 {
404 CHANNEL channel = new CHANNEL();
405 for (int i = 0; i < r.FieldCount; i++)
406 {
407 CHANNEL.Create(ref channel, r, i);
408 }
409 channels.Add(channel);
410 }
411 }
412 }
413 }
414 con.Clone();
415 }
416 this.Channels = channels;
417 }
418 catch (Exception ex)
419 {
420 gLog.Error.WriteLine(ex.ToString());
421 }
422 }
423 private void ReadProgrammeData()
424 {
425 try
426 {
427 List<IPROGRAMME> programs = new List<IPROGRAMME>();
428 using (SQLiteConnection con = CreateConnection())
429 {
430 con.Open();
431 string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
432 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
433 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
434 {
435 using (SQLiteDataReader r = cmd.ExecuteReader())
436 {
437 if (!r.HasRows)
438 {
439 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
440 }
441 else
442 {
443 while (r.Read())
444 {
445 PROGRAMME program = new PROGRAMME();
446 for (int i = 0; i < r.FieldCount; i++)
447 {
448 PROGRAMME.Create(ref program, r, i);
449 }
450 programs.Add(program);
451 }
452 }
453 }
454 }
455 con.Clone();
456 }
457 this.Programs = programs;
458 }
459 catch (Exception ex)
460 {
461 gLog.Error.WriteLine(ex.ToString());
462 }
463 }
464 private void ReadRecodringScheduleData()
465 {
466 try
467 {
468 List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
469 using (SQLiteConnection con = CreateConnection())
470 {
471 con.Open();
472 string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
473 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
474 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
475 {
476 using (SQLiteDataReader r = cmd.ExecuteReader())
477 {
478 if (!r.HasRows)
479 {
480 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
481 }
482 else
483 {
484 while (r.Read())
485 {
486 RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
487 for (int i = 0; i < r.FieldCount; i++)
488 {
489 RECORDING_SCHEDULE.Create(ref recording, r, i);
490 }
491 recordings.Add(recording);
492 }
493 }
494 }
495 }
496 con.Clone();
497 }
498 this.Recordings = recordings;
499 }
500 catch (Exception ex)
501 {
502 gLog.Error.WriteLine(ex.ToString());
503 }
504 }
505
506 }
507 }

  ViewVC Help
Powered by ViewVC 1.1.22