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

Contents of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.22