ViewVC Help
View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing
root/xmltv_parser/trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs
Revision: 197
Committed: Sat Mar 16 22:02:01 2013 UTC (10 years, 2 months ago) by william
File size: 48973 byte(s)
Log Message:

File Contents

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