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

Contents of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 220 - (show annotations) (download)
Sun Mar 17 01:50:27 2013 UTC (7 years, 3 months ago) by william
File size: 54773 byte(s)

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 using libxmltv.Interfaces;
11 using libxmltv.Core;
12 namespace GBPVRProgramDatabaseFixer
13 {
14 public interface IOldNewProgram
15 {
16 SQLLITE.IPROGRAMME OldProgram { get; }
17 SQLLITE.IPROGRAMME NewProgram { get; }
18 }
19 public interface ISQLLITE
20 {
21 List<SQLLITE.IPROGRAMME> Programs { get; }
22 List<SQLLITE.IRECORDING_SCHEDULE> Recordings { get; }
23 List<SQLLITE.ICHANNEL> Channels { get; }
24 IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs);
25 List<IOldNewProgram> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> removed_programs);
26
27 void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs);
28 void UpdateGBPVRPrograms(List<IOldNewProgram> programs);
29
30 }
31 public class SQLLITE : ISQLLITE
32 {
33 private class OldNewProgram : IOldNewProgram
34 {
35 public OldNewProgram() : this(new PROGRAMME()) { }
36 public OldNewProgram(IPROGRAMME _old) : this(_old,_old) { }
37 public OldNewProgram(IPROGRAMME _old, IPROGRAMME _new) { OldProgram = _old; NewProgram = _new; }
38
39 public IPROGRAMME OldProgram { get; private set; }
40 public IPROGRAMME NewProgram { get; private set; }
41 }
42 public static ISQLLITE Create(string database, EventHandler<EventArgs> OnInstanceCreated)
43 {
44 return new SQLLITE(database, OnInstanceCreated);
45 }
46
47 #region DATABASE DEFINITIONS
48
49 public interface ICHANNEL
50 {
51 Int64 oid { get; }
52 String name { get; }
53 String channelID { get; }
54 Int64 channel_number { get; }
55 String favourite_channel { get; }
56 String display_name { get; }
57 }
58 private class CHANNEL : ICHANNEL
59 {
60 public CHANNEL()
61 {
62 BaseDatabaseDefinition<CHANNEL>.CreateDefault(this);
63 }
64 //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
65
66 public static void Create(ref CHANNEL instance, SQLiteDataReader r, int index)
67 {
68 BaseDatabaseDefinition<CHANNEL>.Create(ref instance, r, index);
69 }
70 #region ICHANNEL members
71 public Int64 oid { get; set; }
72 public String name { get; set; }
73 public String channelID { get; set; }
74 public Int64 channel_number { get; set; }
75 public String favourite_channel { get; set; }
76 public String display_name { get; set; }
77 #endregion
78 }
79 public interface IRECORDING_SCHEDULE
80 {
81 Int64 oid { get; }
82 Int64 programme_oid { get; }
83 Int64 capture_source_oid { get; }
84 Int16 status { get; }
85 String filename { get; }
86 Int64 recording_type { get; }
87 Int64 recording_group { get; }
88 DateTime manual_start_time { get; }
89 DateTime manual_end_time { get; }
90 Int64 manual_channel_oid { get; }
91 Int64 quality_level { get; }
92 Int64 pre_pad_minutes { get; }
93 Int64 post_pad_minutes { get; }
94 Int32 priority { get; }
95 String conversion_profile { get; }
96 }
97
98 private static class BaseDatabaseDefinition<T>
99 {
100 public static void CreateDefault(T instance)
101 {
102 try
103 {
104 Type t = typeof(T);
105 var props = t.GetProperties();
106 foreach (var prop in props)
107 {
108 Type prop_type = prop.PropertyType;
109 object field_value = null;
110 try
111 {
112 if (prop_type == typeof(string))
113 {
114 field_value = string.Empty;
115 }
116 else
117 {
118 field_value = Activator.CreateInstance(prop_type);
119 }
120 }
121 catch (Exception ex)
122 {
123 throw ex;
124 }
125 prop.SetValue(instance, field_value, null);
126 }
127 }
128 catch (Exception ex)
129 {
130 throw ex;
131 }
132 }
133 public static void Create(ref T instance, SQLiteDataReader r, int index)
134 {
135 string field_name = r.GetName(index);
136 Type field_type = r.GetFieldType(index);
137 object field_value = r.GetValue(index);
138 //gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
139
140 Type t = typeof(T);
141 var props = t.GetProperties();
142 foreach (var prop in props)
143 {
144 if (prop.Name.ToLower() == field_name.ToLower())
145 {
146 if (prop.PropertyType == field_type)
147 {
148 Type db_type = field_value.GetType();
149 try
150 {
151 if (db_type == typeof(System.DBNull))
152 {
153 prop.SetValue(instance, null, null);
154 }
155 else
156 {
157 prop.SetValue(instance, field_value, null);
158 }
159
160 }
161 catch (Exception ex)
162 {
163 throw ex;
164 }
165 }
166 else
167 {
168 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);
169 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));
170 }
171 }
172 }
173
174 }
175 }
176
177 private class RECORDING_SCHEDULE : IRECORDING_SCHEDULE
178 {
179 public RECORDING_SCHEDULE()
180 {
181 BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this);
182 }
183 //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
184
185 public static void Create(ref RECORDING_SCHEDULE instance, SQLiteDataReader r, int index)
186 {
187 BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(ref instance, r, index);
188 }
189
190 #region IRECORDING_SCHEDULE members
191 public Int64 oid { get; set; }
192 public Int64 programme_oid { get; set; }
193 public Int64 capture_source_oid { get; set; }
194 public Int16 status { get; set; }
195 public String filename { get; set; }
196 public Int64 recording_type { get; set; }
197 public Int64 recording_group { get; set; }
198 public DateTime manual_start_time { get; set; }
199 public DateTime manual_end_time { get; set; }
200 public Int64 manual_channel_oid { get; set; }
201 public Int64 quality_level { get; set; }
202 public Int64 pre_pad_minutes { get; set; }
203 public Int64 post_pad_minutes { get; set; }
204 public Int32 priority { get; set; }
205 public String conversion_profile { get; set; }
206 #endregion
207 }
208
209 public interface IPROGRAMME : IEquatable<IPROGRAMME>
210 {
211 Int64 oid { get; set; }
212 String name { get; set; }
213 String sub_title { get; set; }
214 String description { get; set; }
215 DateTime start_time { get; set; }
216 DateTime end_time { get; set; }
217 Int64 channel_oid { get; set; }
218 String unique_identifier { get; set; }
219 String rating { get; set; }
220
221 string ToString();
222 IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite);
223 }
224 private class PROGRAMME : IPROGRAMME
225 {
226 public PROGRAMME()
227 {
228 BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this);
229 }
230 //public PROGRAMME(SQLiteDataReader r, int index) : base(r, index) { }
231 public static void Create(ref PROGRAMME instance, SQLiteDataReader r, int index)
232 {
233 BaseDatabaseDefinition<PROGRAMME>.Create(ref instance, r, index);
234 }
235 #region IPROGRAMME members
236 public Int64 oid { get; set; }
237 public String name { get; set; }
238 public String sub_title { get; set; }
239 public String description { get; set; }
240 public DateTime start_time { get; set; }
241 public DateTime end_time { get; set; }
242 public Int64 channel_oid { get; set; }
243 public String unique_identifier { get; set; }
244 public String rating { get; set; }
245 public IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite)
246 {
247 ProgramList.ProgramDefintion definition = new ProgramList.ProgramDefintion();
248
249 var channel = sqllite.Channels.Find(s => s.oid == this.channel_oid);
250 var channelname = channel.display_name;
251 var split = channelname.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
252 definition.ChannelNumber = Convert.ToInt32(split.First());
253 definition.ChannelName = string.IsNullOrEmpty(split.Last()) ? string.Empty : split.Last().ToString();
254 definition.Description = this.description;
255 definition.Start = this.start_time.ToDateTimeString();
256 definition.Stop = this.end_time.ToDateTimeString();
257 definition.SubTitle = this.sub_title;
258 definition.Title = this.name;
259 return definition;
260 }
261 #endregion
262
263 public bool Equals(IPROGRAMME other)
264 {
265 return this.ToString() == other.ToString();
266 }
267 public override string ToString()
268 {
269 StringBuilder builder = new StringBuilder();
270
271 builder.AppendFormat("oid: '{0}' ", oid);
272 builder.AppendFormat("name: '{0}' ", name);
273 builder.AppendFormat("sub_title: '{0}' ", sub_title);
274 builder.AppendFormat("description: '{0}' ", description);
275 builder.AppendFormat("start_time: '{0}' ", start_time.ToDateTimeString());
276 builder.AppendFormat("end_time: '{0}' ", end_time.ToDateTimeString());
277 builder.AppendFormat("channel_oid: '{0}' ", channel_oid);
278 builder.AppendFormat("unique_identifier: '{0}' ", unique_identifier);
279 builder.AppendFormat("rating: '{0}'", rating);
280
281 return builder.ToString();
282 }
283 public override bool Equals(object obj)
284 {
285 if (obj == null) { throw new ArgumentNullException("obj", "Object to compare cannot be null"); }
286 if (obj.GetType().IsAssignableFrom(typeof(IPROGRAMME)))
287 {
288 return this.Equals((IPROGRAMME)obj);
289 }
290 return base.Equals(obj);
291 }
292 public override int GetHashCode()
293 {
294 return this.ToString().GetHashCode();
295 }
296 }
297 #endregion
298 private static class TABLES
299 {
300 public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
301 public const string PROGRAMME = "PROGRAMME";
302 public const string CHANNEL = "CHANNEL";
303 }
304 private string DATABASE_BACKUP_FILE = string.Empty;
305 //public SQLLite() { }
306 protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
307 {
308 this.OnInstanceCreated = OnInstanceCreated;
309 //CreateConnection(database);
310 this.Database = database;
311 if (!CreateDatabaseBackup())
312 {
313 gLog.Error.WriteLine("Failed to backup database.");
314 return;
315 }
316 ConnectionTest();
317 ReadChannelData();
318 ReadRecodringScheduleData();
319 ReadProgrammeData();
320 OnCreatedInstance(this, new EventArgs());
321 }
322
323 [NonSerialized]
324 private EventHandler<EventArgs> _OnInstanceCreated;
325 private EventHandler<EventArgs> OnInstanceCreated { get { return _OnInstanceCreated; } set { _OnInstanceCreated = value; } }
326
327 private void OnCreatedInstance(object sender, EventArgs e)
328 {
329 if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
330 }
331
332 private string Database;
333 #region ISQLLITE members
334 public List<IPROGRAMME> Programs { get; private set; }
335 public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
336 public List<ICHANNEL> Channels { get; private set; }
337
338 public IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs)
339 {
340 var list = new List<IPROGRAMME>(programs.ToArray());
341 DateTime first = new DateTime();
342 DateTime last = new DateTime();
343 first = list.OrderBy(s => s.start_time).ToList().First().start_time;
344 last = list.OrderBy(s => s.start_time).ToList().Last().start_time;
345 gLog.Verbose.Debug.WriteLine("\tFirst: {0} = ({1})", first.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), first.ToDateTimeString());
346 gLog.Verbose.Debug.WriteLine("\tLast: {0} = ({1})", last.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), last.ToDateTimeString());
347 var range = DateTimeRange.Create(first, last);
348 return range;
349 }
350
351 public List<IOldNewProgram> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> source_invalid)
352 {
353 source_invalid = new List<IPROGRAMME>();
354 List<IOldNewProgram> source_valid = new List<IOldNewProgram>();
355 gbpvr_programs = gbpvr_programs.OrderBy(s => s.start_time).ToList();
356 xmltv_programs = xmltv_programs.OrderBy(s => DateTime.Parse(s.Start)).ToList();
357 gbpvr_programs.TrimExcess();
358 xmltv_programs.TrimExcess();
359
360 double total = gbpvr_programs.Count;
361 double index = 0;
362 double progress = 0;
363 foreach (var program in gbpvr_programs)
364 {
365 progress = 100.0 * (index / total);
366 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Filtering GBPVR Programs: {0:00}%", (int)progress)));
367 // find an entry in xmltv_programs by Channel Number & StartDate
368 var program_xmltv_entry = program.AsXMLTVProgramDefinition(this);
369 var xmltv_entry = xmltv_programs.Find(s => s.ChannelNumber == program_xmltv_entry.ChannelNumber && s.Start == program_xmltv_entry.Start);
370 if (xmltv_entry == null)
371 {
372 // xmltv entry was not found
373 source_invalid.Add(program);
374 }
375 else
376 {
377 // check if the xmltv entry has different data from the current program
378 if (!xmltv_entry.Equals(program_xmltv_entry))
379 {
380 // data is different
381 var updated_program = program;
382
383 if (program_xmltv_entry.Title != xmltv_entry.Title)
384 {
385 gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing title", updated_program.oid);
386 updated_program.name = xmltv_entry.Title;
387 }
388 if (program_xmltv_entry.SubTitle != xmltv_entry.SubTitle)
389 {
390 gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing sub_title", updated_program.oid);
391 updated_program.sub_title = xmltv_entry.SubTitle;
392 }
393 if (program_xmltv_entry.Stop != xmltv_entry.Stop)
394 {
395 gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing end_time", updated_program.oid);
396 updated_program.end_time = DateTime.Parse(xmltv_entry.Stop);
397 }
398 if (program_xmltv_entry.Start != xmltv_entry.Start)
399 {
400 gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing start_time", updated_program.oid);
401 updated_program.start_time = DateTime.Parse(xmltv_entry.Start);
402 }
403 if (program_xmltv_entry.Description != xmltv_entry.Description)
404 {
405 gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing description", updated_program.oid);
406 updated_program.description = xmltv_entry.Description;
407 }
408
409 var updated_xmltv_entry = updated_program.AsXMLTVProgramDefinition(this);
410 if (!xmltv_entry.Equals(updated_xmltv_entry))
411 {
412 throw new Exception(string.Format("Program oid '{0}' was not properly updated.", updated_program.oid));
413 }
414 //source_valid.Add(new OldNewProgram(program, updated_program));
415 source_valid.Add(new OldNewProgram(program, updated_program));
416 }
417 else
418 {
419 // data is the same
420 //source_valid.Add(new OldNewProgram(program, program));
421 source_valid.Add(new OldNewProgram(program));
422 }
423 }
424 index++;
425 }
426 gLog.Info.WriteLine("Total XMLTV Programs: 0x{0:x8}", xmltv_programs.Count);
427 gLog.Info.WriteLine("Total Valid GB-PVR Programs: 0x{0:x8}", source_valid.Count);
428 gLog.Info.WriteLine("Total Invalid GB-PVR Programs: 0x{0:x8}", source_invalid.Count);
429 gLog.Info.WriteLine("Total GB-PVR Programs (Valid+Invalid): 0x{0:x8} == 0x{1:x8}", source_valid.Count + source_invalid.Count, gbpvr_programs.Count);
430 #region old-code
431 //List<OldNewProgram> source_update = new List<OldNewProgram>();
432 ////if (range == null)
433 ////{
434 //// gLog.Warn.WriteLine("The DateTimeRange passed in is null...returning the original program list");
435 //// return list;
436 ////}
437 ////gLog.Warn.WriteLine("FilterProgramsByDateRange has not been implemented");
438 //double total = gbpvr_programs.Count;
439 //double index = 0;
440 //double progress = 0;
441 //foreach (var program in gbpvr_programs)
442 //{
443 // progress = 100.0 * (index / total);
444 // gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Filtering GBPVR Programs: {0:00}%", (int)progress)));
445 // var channel_oid = program.channel_oid;
446 // var channel = this.Channels.Find(s => s.oid == channel_oid);
447 // var start_date = program.start_time;
448 // //var xmltv_entry_list = xmltv_programs.FindAll(s => s.ChannelNumber == channel.channel_number && s.Start == start_date.ToDateTimeString());
449 // var query = from c in xmltv_programs
450 // where
451 // c.ChannelNumber == channel.channel_number &&
452 // c.Start == start_date.ToDateTimeString()
453 // select c;
454 // IProgramDefinition xmltv_entry = null;
455 // if (query.Count()-1 > 0)
456 // {
457 // gLog.Verbose.Error.WriteLine("Found more than one entry: Matching channel='{0}' and start='{1}'", channel.channel_number, start_date.ToDateTimeString());
458 // gLog.Verbose.Error.WriteLine(" GB-PVR Program Data: oid='{0}' channel_oid='{1}' name='{2}' sub_title='{3}' description='{4}'", program.oid, program.channel_oid, program.name, program.sub_title, program.description);
459 // if (Debugger.IsAttached)
460 // {
461 // gLog.Error.WriteLine(" Found: {0} matching entries", query.Count());
462 // int k_index = 0;
463 // foreach (var k in query)
464 // {
465 // gLog.Verbose.Error.WriteLine(" query[{0}]: channel='{1}' start='{2}' ('{3}') title='{4}' subtitle='{5}' description='{6}'", k_index, k.ChannelNumber, k.Start, DateTime.Parse(k.Start).ToString("yyyy/MM/dd HH:mm:ss.fffffff"), k.Title, k.SubTitle, k.Description);
466 // k_index++;
467 // }
468 // Debugger.Break();
469 // }
470 // }
471 // else
472 // {
473 // xmltv_entry = query.FirstOrDefault();
474 // }
475 // if (xmltv_entry == null)
476 // {
477 // //gLog.Verbose.Warn.WriteLine("Invalidating GB-PVR Program: oid='{0}' channel_oid='{1}' title='{2}' start='{3}'", program.oid, program.channel_oid, program.name, program.start_time.ToDateTimeString());
478 // source_invalid.Add(program);
479 // }
480 // else
481 // {
482 // //gLog.Verbose.Warn.WriteLine("Updating GB-PVR Program (if needed): oid='{0}' channel_oid='{1}' title='{2}' subtitle='{3}' start='{4}'", program.oid, program.channel_oid, program.name, program.sub_title, program.start_time.ToDateTimeString());
483 // var updated_program = program;
484 // bool needsupdate = false;
485 // if (xmltv_entry.Title != program.name)
486 // {
487 // //gLog.Verbose.Warn.WriteLine(" Updating:");
488 // //gLog.Verbose.Warn.WriteLine(" Old Title: {0}", program.name);
489 // //gLog.Verbose.Warn.WriteLine(" New Title: {0}", xmltv_entry.Title);
490 // updated_program.name = xmltv_entry.Title;
491 // needsupdate = true;
492 // }
493 // if (xmltv_entry.SubTitle != program.sub_title)
494 // {
495 // //gLog.Verbose.Warn.WriteLine(" Updating:");
496 // //gLog.Verbose.Warn.WriteLine(" Old SubTile: {0}", program.sub_title);
497 // //gLog.Verbose.Warn.WriteLine(" New SubTile: {0}", xmltv_entry.SubTitle);
498 // updated_program.sub_title = xmltv_entry.SubTitle;
499 // needsupdate = true;
500 // }
501 // if (xmltv_entry.Description != program.description)
502 // {
503 // //gLog.Verbose.Warn.WriteLine(" Updating:");
504 // //gLog.Verbose.Warn.WriteLine(" Old Descption: {0}", program.description);
505 // //gLog.Verbose.Warn.WriteLine(" New Descption: {0}", xmltv_entry.Description);
506 // updated_program.description = xmltv_entry.Description;
507 // needsupdate = true;
508 // }
509 // if (DateTime.Parse(xmltv_entry.Start) != program.start_time)
510 // {
511 // //gLog.Verbose.Warn.WriteLine(" Updating:");
512 // //gLog.Verbose.Warn.WriteLine(" Old StartTime: {0}", program.start_time.ToDateTimeString());
513 // //gLog.Verbose.Warn.WriteLine(" New StartTime: {0}", DateTime.Parse(xmltv_entry.Start).ToDateTimeString());
514 // updated_program.start_time = DateTime.Parse(xmltv_entry.Start);
515 // needsupdate = true;
516 // }
517 // if (DateTime.Parse(xmltv_entry.Stop) != program.end_time)
518 // {
519 // //gLog.Verbose.Warn.WriteLine(" Updating:");
520 // //gLog.Verbose.Warn.WriteLine(" Old EndTime: {0}", program.end_time.ToDateTimeString());
521 // //gLog.Verbose.Warn.WriteLine(" New EndTime: {0}", DateTime.Parse(xmltv_entry.Stop).ToDateTimeString());
522 // updated_program.end_time = DateTime.Parse(xmltv_entry.Stop);
523 // needsupdate = true;
524 // }
525 // if (needsupdate)
526 // {
527 // OldNewProgram p = new OldNewProgram();
528 // p.OldProgram = program;
529 // p.NewProgram = updated_program;
530 // source_update.Add(p);
531 // }
532 // source_valid.Add(updated_program);
533 // }
534 // index++;
535 //}
536 //source_valid = source_valid.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
537 //source_invalid = source_invalid.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
538 //if (source_invalid.Count > 0)
539 //{
540 // double source_invalid_count = source_valid.Count;
541 // double source_invalid_index = 0;
542 // double source_invalid_progress = 0;
543 // foreach (var old_program in source_invalid)
544 // {
545 // source_invalid_progress = 100.0 * (source_invalid_index / source_invalid_count);
546 // gLog.ReportProgress(this, new ReportProgressEventArgs((int)source_invalid_progress, string.Format("Removing old GBPVR Programs: {0:00}%", (int)source_invalid_progress)));
547 // // remove database entry
548 // if (!RemoveProgramDatabaseEntry(old_program))
549 // {
550 // gLog.Error.WriteLine("Failed to remove program with oid: '{0}'", old_program.oid);
551 // }
552 // else
553 // {
554 // gLog.Verbose.Info.WriteLine("Removed program with oid: '{0}'", old_program.oid);
555 // }
556 // source_invalid_index++;
557 // }
558 //}
559 //else
560 //{
561 // gLog.Info.WriteLine("No old GB-PVR Programs needed to be removed.");
562 //}
563 //if (source_update.Count > 0)
564 //{
565 // double source_update_count = source_valid.Count;
566 // double source_update_index = 0;
567 // double source_update_progress = 0;
568 // foreach (var p in source_update)
569 // {
570 // source_update_progress = 100.0 * (source_update_index / source_update_count);
571 // gLog.ReportProgress(this, new ReportProgressEventArgs((int)source_update_progress, string.Format("Updating GBPVR Programs: {0:00}%", (int)source_update_progress)));
572 // // remove database entry
573 // if (!UpdateProgramDatabaseEntry(p.OldProgram,p.NewProgram))
574 // {
575 // gLog.Error.WriteLine("Failed to update program with oid: '{0}'", p.OldProgram.oid);
576 // }
577 // else
578 // {
579 // gLog.Verbose.Info.WriteLine("Upated program with oid: '{0}'", p.OldProgram.oid);
580 // }
581 // source_update_index++;
582 // }
583 //}
584 //else
585 //{
586 // gLog.Info.WriteLine("No GB-PVR Programs needed to be updated.");
587 //}
588 //gLog.Info.WriteLine("Total XMLTV Programs: 0x{0:x8}", xmltv_programs.Count);
589 //gLog.Info.WriteLine("Updated: 0x{0:x8} GB-PVR Programs", source_valid.Count);
590 //gLog.Info.WriteLine("Removed: 0x{0:x8} GB-PVR Programs", source_invalid.Count);
591 //gLog.Info.WriteLine("Total GB-PVR Programs (Updated & Removed): 0x{0:x8}", source_valid.Count + source_invalid.Count);
592 #endregion
593
594 return source_valid;
595 }
596 #endregion
597
598
599 private string CreateConnectionString()
600 {
601 string connection_string = string.Format("Data Source={0}", this.Database);
602 return connection_string;
603 }
604
605 private SQLiteConnection CreateConnection() { SQLiteConnection connection = new SQLiteConnection(CreateConnectionString()); return connection; }
606
607 [Conditional("SQLLITE_CONNECTION_TEST")]
608 private void ConnectionTest()
609 {
610 try
611 {
612 using (SQLiteConnection con = CreateConnection())
613 {
614 con.Open();
615 string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
616 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
617 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
618 {
619 using (SQLiteDataReader r = cmd.ExecuteReader())
620 {
621 if (!r.HasRows)
622 {
623 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
624 }
625 else
626 {
627 while (r.Read())
628 {
629 for (int i = 0; i < r.FieldCount; i++)
630 {
631 string field_name = r.GetName(i);
632 Type field_type = r.GetFieldType(i);
633 object field_value = r.GetValue(i);
634 gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
635 }
636 break;
637 }
638 }
639 }
640 }
641 con.Clone();
642 }
643 OnCreatedInstance(this, new EventArgs());
644 }
645 catch (Exception ex)
646 {
647 gLog.Error.WriteLine(ex.ToString());
648 }
649 }
650
651
652 private bool CreateDatabaseBackup()
653 {
654 try
655 {
656 string backup_file = string.Format("{0}.{1}", this.Database, DateTime.Now.ToString("yyyyMMddHHmmss"));
657 gLog.Info.WriteLine("Creating Database backup...");
658 gLog.Info.WriteLine("\tSource: {0}", this.Database);
659 gLog.Info.WriteLine("\tDestination: {0}", backup_file);
660
661 System.IO.File.Copy(this.Database, backup_file);
662 DATABASE_BACKUP_FILE = backup_file;
663 return true;
664 }
665 catch (Exception ex)
666 {
667 gLog.Error.WriteLine(ex.ToString());
668 return false;
669 }
670 }
671 private void ReadChannelData()
672 {
673 try
674 {
675 List<ICHANNEL> channels = new List<ICHANNEL>();
676 using (SQLiteConnection con = CreateConnection())
677 {
678 try
679 {
680 con.Open();
681 string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
682 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
683 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
684 {
685 using (SQLiteDataReader r = cmd.ExecuteReader())
686 {
687 if (!r.HasRows)
688 {
689 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
690 }
691 else
692 {
693 while (r.Read())
694 {
695 CHANNEL channel = new CHANNEL();
696 for (int i = 0; i < r.FieldCount; i++)
697 {
698 CHANNEL.Create(ref channel, r, i);
699 }
700 channels.Add(channel);
701 }
702 }
703 }
704 }
705 }
706 catch (SQLiteException ex)
707 {
708 gLog.Error.WriteLine(ex.ToString());
709 }
710 finally
711 {
712 con.Close();
713 }
714 }
715 this.Channels = channels;
716 }
717 catch (Exception ex)
718 {
719 gLog.Error.WriteLine(ex.ToString());
720 }
721 }
722 private void ReadProgrammeData()
723 {
724 try
725 {
726 List<IPROGRAMME> programs = new List<IPROGRAMME>();
727 using (SQLiteConnection con = CreateConnection())
728 {
729 try
730 {
731 con.Open();
732 string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
733 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
734 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
735 {
736 using (SQLiteDataReader r = cmd.ExecuteReader())
737 {
738 if (!r.HasRows)
739 {
740 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
741 }
742 else
743 {
744 while (r.Read())
745 {
746 PROGRAMME program = new PROGRAMME();
747 for (int i = 0; i < r.FieldCount; i++)
748 {
749 PROGRAMME.Create(ref program, r, i);
750 }
751 programs.Add(program);
752 }
753 }
754 }
755 }
756 }
757 catch (SQLiteException ex)
758 {
759 gLog.Error.WriteLine(ex.ToString());
760 }
761 finally
762 {
763 con.Close();
764 }
765 }
766 this.Programs = programs;
767 }
768 catch (Exception ex)
769 {
770 gLog.Error.WriteLine(ex.ToString());
771 }
772 }
773 private void ReadRecodringScheduleData()
774 {
775 try
776 {
777 List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
778 using (SQLiteConnection con = CreateConnection())
779 {
780 try
781 {
782 con.Open();
783 string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
784 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
785 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
786 {
787 using (SQLiteDataReader r = cmd.ExecuteReader())
788 {
789 if (!r.HasRows)
790 {
791 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
792 }
793 else
794 {
795 while (r.Read())
796 {
797 RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
798 for (int i = 0; i < r.FieldCount; i++)
799 {
800 RECORDING_SCHEDULE.Create(ref recording, r, i);
801 }
802 recordings.Add(recording);
803 }
804 }
805 }
806 }
807 }
808 catch (SQLiteException ex)
809 {
810 gLog.Error.WriteLine(ex.ToString());
811 }
812 finally
813 {
814 con.Close();
815 }
816 }
817 this.Recordings = recordings;
818 }
819 catch (Exception ex)
820 {
821 gLog.Error.WriteLine(ex.ToString());
822 }
823 }
824
825 public void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs)
826 {
827 gLog.Info.WriteLine("Removing {0} gbpvr programs.", programs.Count);
828 int ChunkSize = 1024;
829 var ChunkList = programs.Chunk<IPROGRAMME>(ChunkSize);
830 double total = programs.Count;
831 double index = 0;
832 double progress = 0;
833 Stopwatch st1 = new Stopwatch();
834 st1.Start();
835 foreach (var p in ChunkList)
836 {
837 progress = 100.0 * (index / total);
838 if (!RemoveProgramDatabaseEntry(p))
839 {
840 gLog.Error.WriteLine("Failed to remove one or more program(s)");
841 }
842 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing old gbpvr programs ({0} of {1}) {2:00}%", index, total, (int)progress)));
843 index += (double)p.Count();
844 }
845 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing old gbpvr programs ({0} of {1}) {2:00}%", index, total, (int)progress)));
846 st1.Stop();
847 gLog.Warn.WriteLine(" operation took: {0:0.00000} seconds overall", st1.Elapsed.TotalSeconds);
848 }
849 public void UpdateGBPVRPrograms(List<IOldNewProgram> programs)
850 {
851 gLog.Info.WriteLine("Updating {0} gbpvr programs.", programs.Count);
852 int ChunkSize = 1;
853 var ChunkList = programs.Chunk<IOldNewProgram>(ChunkSize);
854
855 double total = programs.Count;
856 double index = 0;
857 double progress = 0;
858 Stopwatch st1 = new Stopwatch();
859 st1.Start();
860 foreach (var p in ChunkList)
861 {
862 progress = 100.0 * (index / total);
863 if (!UpdateProgramEntryDatabase(p))
864 {
865 gLog.Error.WriteLine("Failed to update one or more program(s)");
866 }
867 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Updating gbpvr programs ({0} of {1}) {2:00}%", index, total, (int)progress)));
868 index += (double)p.Count();
869 }
870 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Updating gbpvr programs ({0} of {1}) {2:00}%", index, total, (int)progress)));
871 st1.Stop();
872 gLog.Warn.WriteLine(" operation took: {0:0.00000} seconds overall", st1.Elapsed.TotalSeconds);
873 }
874
875 private bool RemoveAllProgramDatabaseEntries()
876 {
877 bool result = false;
878 try
879 {
880 using (SQLiteConnection con = CreateConnection())
881 {
882 try
883 {
884 //gLog.Verbose.Info.WriteLine("Removing old program with oid: '{0}'", old_program.oid);
885 con.Open();
886 string command_text = string.Format(@"DELETE FROM [{0}];", TABLES.PROGRAMME);
887 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
888 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
889 {
890 //cmd.Parameters.Add(new SQLiteParameter("oid", old_program.oid));
891 int rowsupdated = cmd.ExecuteNonQuery();
892 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
893 }
894 result = true;
895 }
896 catch (SQLiteException ex)
897 {
898 gLog.Error.WriteLine(ex.ToString());
899 result = false;
900 }
901 finally
902 {
903 con.Close();
904 }
905 }
906 }
907 catch (Exception ex)
908 {
909 gLog.Error.WriteLine(ex.ToString());
910 result = false;
911 }
912 return result;
913 }
914 private bool RemoveProgramDatabaseEntry(IEnumerable<IPROGRAMME> list)
915 {
916 bool result = false;
917 try
918 {
919 using (SQLiteConnection con = CreateConnection())
920 {
921 try
922 {
923 //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
924 con.Open();
925 //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
926 string command_text = string.Empty;
927 command_text = BuildGBPVRMultiDeleteCommand(list);
928 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
929 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
930 {
931 int rowsupdated = cmd.ExecuteNonQuery();
932 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
933 }
934
935 result = true;
936 }
937 catch (SQLiteException ex)
938 {
939 gLog.Error.WriteLine(ex.ToString());
940 result = false;
941 }
942 finally
943 {
944 con.Close();
945 }
946 }
947 }
948 catch (Exception ex)
949 {
950 gLog.Error.WriteLine(ex.ToString());
951 result = false;
952 }
953 return result;
954 }
955 private bool UpdateProgramEntryDatabase(IEnumerable<IOldNewProgram> list)
956 {
957 bool result = false;
958 try
959 {
960 using (SQLiteConnection con = CreateConnection())
961 {
962 try
963 {
964 //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
965 con.Open();
966 //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
967 string command_text = string.Empty;
968 command_text = BuildGBPVRMultiUpdateCommand(list);
969 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
970 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
971 {
972 int rowsupdated = cmd.ExecuteNonQuery();
973 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
974 }
975
976 result = true;
977 }
978 catch (SQLiteException ex)
979 {
980 gLog.Error.WriteLine(ex.ToString());
981 result = false;
982 }
983 finally
984 {
985 con.Close();
986 }
987 }
988 }
989 catch (Exception ex)
990 {
991 gLog.Error.WriteLine(ex.ToString());
992 result = false;
993 }
994 return result;
995 }
996 private bool InsertProgramEntryDatabase(IEnumerable<IPROGRAMME> list)
997 {
998 bool result = false;
999 try
1000 {
1001 using (SQLiteConnection con = CreateConnection())
1002 {
1003 try
1004 {
1005 //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
1006 con.Open();
1007 //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
1008 string command_text = string.Empty;
1009 command_text = BuildGBPVRMultiInsertCommand(list);
1010 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
1011 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
1012 {
1013 int rowsupdated = cmd.ExecuteNonQuery();
1014 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
1015 }
1016
1017 result = true;
1018 }
1019 catch (SQLiteException ex)
1020 {
1021 gLog.Error.WriteLine(ex.ToString());
1022 result = false;
1023 }
1024 finally
1025 {
1026 con.Close();
1027 }
1028 }
1029 }
1030 catch (Exception ex)
1031 {
1032 gLog.Error.WriteLine(ex.ToString());
1033 result = false;
1034 }
1035 return result;
1036 }
1037
1038 #region Multi-Delete Command Support
1039 private string BuildGBPVRMultiDeleteCommand(IEnumerable<IPROGRAMME> list)
1040 {
1041 StringBuilder builder = new StringBuilder();
1042 /*
1043 insert into table1 (field1,field2) values (value1,value2);
1044 insert into table1 (field1,field2) values (value1,value2);
1045 insert into table1 (field1,field2) values (value1,value2);
1046 insert into table1 (field1,field2) values (value1,value2)
1047 */
1048 builder.AppendLine("begin transaction;");
1049 foreach (var t in list)
1050 {
1051 builder.AppendLine(BuildGBPVRSingleDeleteCommand(t));
1052 }
1053 builder.AppendLine("end transaction;");
1054 return builder.ToString();
1055 }
1056 private string BuildGBPVRSingleDeleteCommand(IPROGRAMME program)
1057 {
1058 StringBuilder builder = new StringBuilder();
1059 builder.AppendFormat("delete from {0} where oid={1};", TABLES.PROGRAMME, program.oid);
1060 return builder.ToString();
1061 }
1062 #endregion
1063 #region Multi-Update Command Support
1064 private string BuildGBPVRMultiUpdateCommand(IEnumerable<IOldNewProgram> list)
1065 {
1066 StringBuilder builder = new StringBuilder();
1067 /*
1068 insert into table1 (field1,field2) values (value1,value2);
1069 insert into table1 (field1,field2) values (value1,value2);
1070 insert into table1 (field1,field2) values (value1,value2);
1071 insert into table1 (field1,field2) values (value1,value2)
1072 */
1073 builder.AppendLine("begin transaction;");
1074 foreach (var t in list)
1075 {
1076 if (!t.OldProgram.Equals(t.NewProgram))
1077 {
1078 // only update the entry if it is different
1079 builder.AppendLine(BuildGBPVRSingleUpdateCommand(t.NewProgram));
1080 }
1081 }
1082 builder.AppendLine("end transaction;");
1083 return builder.ToString();
1084 }
1085 private string BuildGBPVRSingleUpdateCommand(IPROGRAMME program)
1086 {
1087 StringBuilder builder = new StringBuilder();
1088 builder.AppendFormat("update {0} SET ", TABLES.PROGRAMME);
1089 builder.AppendFormat("name=\"{0}\", ", program.name);
1090 builder.AppendFormat("sub_title=\"{0}\", ", program.sub_title);
1091 builder.AppendFormat("description=\"{0}\", ", program.description);
1092 builder.AppendFormat("start_time='{0}', ", program.start_time.ToString("yyyy/MM/dd HH:mm:ss.fffffff"));
1093 builder.AppendFormat("end_time='{0}', ", program.end_time.ToString("yyyy/MM/dd HH:mm:ss.fffffff"));
1094 builder.AppendFormat("channel_oid={0}, ", program.channel_oid);
1095 builder.AppendFormat("unique_identifier=\"{0}\", ", program.unique_identifier);
1096 builder.AppendFormat("rating=\"{0}\" ", program.rating);
1097 builder.AppendFormat("where oid={0};", program.oid);
1098 return builder.ToString();
1099 }
1100 #endregion
1101 #region Multi-Insert Command Support
1102 private string BuildGBPVRMultiInsertCommand(IEnumerable<IPROGRAMME> list)
1103 {
1104 StringBuilder builder = new StringBuilder();
1105 /*
1106 insert into table1 (field1,field2) values (value1,value2);
1107 insert into table1 (field1,field2) values (value1,value2);
1108 insert into table1 (field1,field2) values (value1,value2);
1109 insert into table1 (field1,field2) values (value1,value2)
1110 */
1111 builder.AppendLine("begin transaction;");
1112 foreach (var t in list)
1113 {
1114 builder.AppendLine(BuildGBPVRSingleInsertCommand(t));
1115 }
1116 builder.AppendLine("end transaction;");
1117 return builder.ToString();
1118 }
1119
1120 private string BuildGBPVRSingleInsertCommand(IPROGRAMME program)
1121 {
1122 StringBuilder builder = new StringBuilder();
1123 builder.AppendFormat("insert into {0} (oid,name,sub_title,description,start_time,end_time,channel_oid,unique_identifier,rating) values (", TABLES.PROGRAMME);
1124 builder.AppendFormat("{0},",program.oid);
1125 builder.AppendFormat("\"{0}\",", program.name);
1126 builder.AppendFormat("\"{0}\",", program.sub_title);
1127 builder.AppendFormat("\"{0}\",", program.description);
1128 builder.AppendFormat("'{0}',", program.start_time.ToString("yyyy/MM/dd HH:mm:ss.fffffff"));
1129 builder.AppendFormat("'{0}',", program.end_time.ToString("yyyy/MM/dd HH:mm:ss.fffffff"));
1130 builder.AppendFormat("{0},", program.channel_oid);
1131 builder.AppendFormat("\"{0}\",", program.unique_identifier);
1132 builder.AppendFormat("\"{0}\");", program.rating);
1133 return builder.ToString();
1134 }
1135 #endregion
1136
1137 }
1138 }

  ViewVC Help
Powered by ViewVC 1.1.22