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

Contents of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.22