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

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 183 - (hide annotations) (download)
Sat Mar 16 18:30:41 2013 UTC (7 years, 3 months ago) by william
File size: 45205 byte(s)

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 182 List<IOldNewProgram> 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     void UpdateGBPVRPrograms(List<IOldNewProgram> programs);
29    
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     public CHANNEL()
60     {
61     BaseDatabaseDefinition<CHANNEL>.CreateDefault(this);
62     }
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     Int64 post_pad_minutes { get;}
93     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     {
134     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    
159     }
160     catch (Exception ex)
161     {
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     public RECORDING_SCHEDULE()
179     {
180     BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this);
181     }
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     BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(ref instance, r, index);
187     }
188    
189     #region IRECORDING_SCHEDULE members
190     public Int64 oid { get; set;}
191     public Int64 programme_oid { get; set;}
192     public Int64 capture_source_oid { get; set;}
193     public Int16 status { get; set; }
194     public String filename { get; set; }
195     public Int64 recording_type { get;set; }
196     public Int64 recording_group { get; set;}
197     public DateTime manual_start_time { get; set;}
198     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     {
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     public PROGRAMME()
226     {
227     BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this);
228     }
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 163 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    
326     private void OnCreatedInstance(object sender, EventArgs e)
327     {
328     if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
329     }
330    
331     private string Database;
332 william 163 #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     first = list.OrderBy(s => s.start_time).ToList().First().start_time;
343     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 182 public List<IOldNewProgram> 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 182 List<IOldNewProgram> source_valid = new List<IOldNewProgram>();
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     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     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     source_valid.Add(new OldNewProgram(program, updated_program));
414 william 172 }
415 william 176 else
416     {
417 william 180 // data is the same
418 william 182 source_valid.Add(new OldNewProgram(program, program));
419 william 176 }
420     }
421 william 181 index++;
422 william 176 }
423    
424 william 180 #region old-code
425     //List<OldNewProgram> source_update = new List<OldNewProgram>();
426     ////if (range == null)
427     ////{
428     //// gLog.Warn.WriteLine("The DateTimeRange passed in is null...returning the original program list");
429     //// return list;
430     ////}
431     ////gLog.Warn.WriteLine("FilterProgramsByDateRange has not been implemented");
432     //double total = gbpvr_programs.Count;
433     //double index = 0;
434     //double progress = 0;
435     //foreach (var program in gbpvr_programs)
436     //{
437     // progress = 100.0 * (index / total);
438     // gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Filtering GBPVR Programs: {0:00}%", (int)progress)));
439     // var channel_oid = program.channel_oid;
440     // var channel = this.Channels.Find(s => s.oid == channel_oid);
441     // var start_date = program.start_time;
442     // //var xmltv_entry_list = xmltv_programs.FindAll(s => s.ChannelNumber == channel.channel_number && s.Start == start_date.ToDateTimeString());
443     // var query = from c in xmltv_programs
444     // where
445     // c.ChannelNumber == channel.channel_number &&
446     // c.Start == start_date.ToDateTimeString()
447     // select c;
448     // IProgramDefinition xmltv_entry = null;
449     // if (query.Count()-1 > 0)
450     // {
451     // gLog.Verbose.Error.WriteLine("Found more than one entry: Matching channel='{0}' and start='{1}'", channel.channel_number, start_date.ToDateTimeString());
452     // 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);
453     // if (Debugger.IsAttached)
454     // {
455     // gLog.Error.WriteLine(" Found: {0} matching entries", query.Count());
456     // int k_index = 0;
457     // foreach (var k in query)
458     // {
459     // 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);
460     // k_index++;
461     // }
462     // Debugger.Break();
463     // }
464     // }
465     // else
466     // {
467     // xmltv_entry = query.FirstOrDefault();
468     // }
469     // if (xmltv_entry == null)
470     // {
471     // //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());
472     // source_invalid.Add(program);
473     // }
474     // else
475     // {
476     // //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());
477     // var updated_program = program;
478     // bool needsupdate = false;
479     // if (xmltv_entry.Title != program.name)
480     // {
481     // //gLog.Verbose.Warn.WriteLine(" Updating:");
482     // //gLog.Verbose.Warn.WriteLine(" Old Title: {0}", program.name);
483     // //gLog.Verbose.Warn.WriteLine(" New Title: {0}", xmltv_entry.Title);
484     // updated_program.name = xmltv_entry.Title;
485     // needsupdate = true;
486     // }
487     // if (xmltv_entry.SubTitle != program.sub_title)
488     // {
489     // //gLog.Verbose.Warn.WriteLine(" Updating:");
490     // //gLog.Verbose.Warn.WriteLine(" Old SubTile: {0}", program.sub_title);
491     // //gLog.Verbose.Warn.WriteLine(" New SubTile: {0}", xmltv_entry.SubTitle);
492     // updated_program.sub_title = xmltv_entry.SubTitle;
493     // needsupdate = true;
494     // }
495     // if (xmltv_entry.Description != program.description)
496     // {
497     // //gLog.Verbose.Warn.WriteLine(" Updating:");
498     // //gLog.Verbose.Warn.WriteLine(" Old Descption: {0}", program.description);
499     // //gLog.Verbose.Warn.WriteLine(" New Descption: {0}", xmltv_entry.Description);
500     // updated_program.description = xmltv_entry.Description;
501     // needsupdate = true;
502     // }
503     // if (DateTime.Parse(xmltv_entry.Start) != program.start_time)
504     // {
505     // //gLog.Verbose.Warn.WriteLine(" Updating:");
506     // //gLog.Verbose.Warn.WriteLine(" Old StartTime: {0}", program.start_time.ToDateTimeString());
507     // //gLog.Verbose.Warn.WriteLine(" New StartTime: {0}", DateTime.Parse(xmltv_entry.Start).ToDateTimeString());
508     // updated_program.start_time = DateTime.Parse(xmltv_entry.Start);
509     // needsupdate = true;
510     // }
511     // if (DateTime.Parse(xmltv_entry.Stop) != program.end_time)
512     // {
513     // //gLog.Verbose.Warn.WriteLine(" Updating:");
514     // //gLog.Verbose.Warn.WriteLine(" Old EndTime: {0}", program.end_time.ToDateTimeString());
515     // //gLog.Verbose.Warn.WriteLine(" New EndTime: {0}", DateTime.Parse(xmltv_entry.Stop).ToDateTimeString());
516     // updated_program.end_time = DateTime.Parse(xmltv_entry.Stop);
517     // needsupdate = true;
518     // }
519     // if (needsupdate)
520     // {
521     // OldNewProgram p = new OldNewProgram();
522     // p.OldProgram = program;
523     // p.NewProgram = updated_program;
524     // source_update.Add(p);
525     // }
526     // source_valid.Add(updated_program);
527     // }
528     // index++;
529     //}
530     //source_valid = source_valid.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
531     //source_invalid = source_invalid.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
532     //if (source_invalid.Count > 0)
533     //{
534     // double source_invalid_count = source_valid.Count;
535     // double source_invalid_index = 0;
536     // double source_invalid_progress = 0;
537     // foreach (var old_program in source_invalid)
538     // {
539     // source_invalid_progress = 100.0 * (source_invalid_index / source_invalid_count);
540     // gLog.ReportProgress(this, new ReportProgressEventArgs((int)source_invalid_progress, string.Format("Removing old GBPVR Programs: {0:00}%", (int)source_invalid_progress)));
541     // // remove database entry
542     // if (!RemoveProgramDatabaseEntry(old_program))
543     // {
544     // gLog.Error.WriteLine("Failed to remove program with oid: '{0}'", old_program.oid);
545     // }
546     // else
547     // {
548     // gLog.Verbose.Info.WriteLine("Removed program with oid: '{0}'", old_program.oid);
549     // }
550     // source_invalid_index++;
551     // }
552     //}
553     //else
554     //{
555     // gLog.Info.WriteLine("No old GB-PVR Programs needed to be removed.");
556     //}
557     //if (source_update.Count > 0)
558     //{
559     // double source_update_count = source_valid.Count;
560     // double source_update_index = 0;
561     // double source_update_progress = 0;
562     // foreach (var p in source_update)
563     // {
564     // source_update_progress = 100.0 * (source_update_index / source_update_count);
565     // gLog.ReportProgress(this, new ReportProgressEventArgs((int)source_update_progress, string.Format("Updating GBPVR Programs: {0:00}%", (int)source_update_progress)));
566     // // remove database entry
567     // if (!UpdateProgramDatabaseEntry(p.OldProgram,p.NewProgram))
568     // {
569     // gLog.Error.WriteLine("Failed to update program with oid: '{0}'", p.OldProgram.oid);
570     // }
571     // else
572     // {
573     // gLog.Verbose.Info.WriteLine("Upated program with oid: '{0}'", p.OldProgram.oid);
574     // }
575     // source_update_index++;
576     // }
577     //}
578     //else
579     //{
580     // gLog.Info.WriteLine("No GB-PVR Programs needed to be updated.");
581     //}
582     //gLog.Info.WriteLine("Total XMLTV Programs: 0x{0:x8}", xmltv_programs.Count);
583     //gLog.Info.WriteLine("Updated: 0x{0:x8} GB-PVR Programs", source_valid.Count);
584     //gLog.Info.WriteLine("Removed: 0x{0:x8} GB-PVR Programs", source_invalid.Count);
585     //gLog.Info.WriteLine("Total GB-PVR Programs (Updated & Removed): 0x{0:x8}", source_valid.Count + source_invalid.Count);
586     #endregion
587 william 176
588 william 166 return source_valid;
589 william 163 }
590 william 156 #endregion
591    
592    
593     private string CreateConnectionString()
594     {
595     string connection_string = string.Format("Data Source={0}", this.Database);
596     return connection_string;
597     }
598    
599     private SQLiteConnection CreateConnection() { SQLiteConnection connection = new SQLiteConnection(CreateConnectionString()); return connection; }
600    
601     [Conditional("SQLLITE_CONNECTION_TEST")]
602     private void ConnectionTest()
603     {
604     try
605     {
606     using (SQLiteConnection con = CreateConnection())
607     {
608     con.Open();
609 william 159 string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
610 william 156 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
611     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
612     {
613     using (SQLiteDataReader r = cmd.ExecuteReader())
614     {
615     if (!r.HasRows)
616     {
617     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
618     }
619     else
620     {
621     while (r.Read())
622     {
623     for (int i = 0; i < r.FieldCount; i++)
624     {
625     string field_name = r.GetName(i);
626     Type field_type = r.GetFieldType(i);
627     object field_value = r.GetValue(i);
628     gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
629     }
630     break;
631     }
632     }
633     }
634     }
635 william 157 con.Clone();
636 william 156 }
637     OnCreatedInstance(this, new EventArgs());
638     }
639     catch (Exception ex)
640     {
641     gLog.Error.WriteLine(ex.ToString());
642     }
643     }
644    
645 william 157
646     private bool CreateDatabaseBackup()
647     {
648     try
649     {
650     string backup_file = string.Format("{0}.{1}", this.Database, DateTime.Now.ToString("yyyyMMddHHmmss"));
651     gLog.Info.WriteLine("Creating Database backup...");
652     gLog.Info.WriteLine("\tSource: {0}", this.Database);
653     gLog.Info.WriteLine("\tDestination: {0}", backup_file);
654    
655     System.IO.File.Copy(this.Database, backup_file);
656     return true;
657     }
658     catch (Exception ex)
659     {
660     gLog.Error.WriteLine(ex.ToString());
661     return false;
662     }
663     }
664 william 159 private void ReadChannelData()
665     {
666     try
667     {
668     List<ICHANNEL> channels = new List<ICHANNEL>();
669     using (SQLiteConnection con = CreateConnection())
670     {
671     con.Open();
672     string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
673     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
674     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
675     {
676     using (SQLiteDataReader r = cmd.ExecuteReader())
677     {
678     if (!r.HasRows)
679     {
680     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
681     }
682     else
683     {
684     while (r.Read())
685     {
686     CHANNEL channel = new CHANNEL();
687     for (int i = 0; i < r.FieldCount; i++)
688     {
689     CHANNEL.Create(ref channel, r, i);
690     }
691     channels.Add(channel);
692     }
693     }
694     }
695     }
696     con.Clone();
697     }
698     this.Channels = channels;
699     }
700     catch (Exception ex)
701     {
702     gLog.Error.WriteLine(ex.ToString());
703     }
704     }
705 william 156 private void ReadProgrammeData()
706     {
707     try
708     {
709     List<IPROGRAMME> programs = new List<IPROGRAMME>();
710     using (SQLiteConnection con = CreateConnection())
711     {
712     con.Open();
713     string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
714     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
715     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
716     {
717     using (SQLiteDataReader r = cmd.ExecuteReader())
718     {
719     if (!r.HasRows)
720     {
721     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
722     }
723     else
724     {
725     while (r.Read())
726     {
727     PROGRAMME program = new PROGRAMME();
728     for (int i = 0; i < r.FieldCount; i++)
729     {
730     PROGRAMME.Create(ref program, r, i);
731     }
732     programs.Add(program);
733     }
734     }
735     }
736     }
737     con.Clone();
738     }
739     this.Programs = programs;
740     }
741     catch (Exception ex)
742     {
743     gLog.Error.WriteLine(ex.ToString());
744     }
745     }
746     private void ReadRecodringScheduleData()
747     {
748     try
749     {
750     List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
751     using (SQLiteConnection con = CreateConnection())
752     {
753     con.Open();
754     string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
755     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
756     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
757     {
758     using (SQLiteDataReader r = cmd.ExecuteReader())
759     {
760     if (!r.HasRows)
761     {
762     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
763     }
764     else
765     {
766     while (r.Read())
767     {
768     RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
769     for (int i = 0; i < r.FieldCount; i++)
770     {
771     RECORDING_SCHEDULE.Create(ref recording, r, i);
772     }
773     recordings.Add(recording);
774     }
775     }
776     }
777     }
778     con.Clone();
779     }
780     this.Recordings = recordings;
781     }
782     catch (Exception ex)
783     {
784     gLog.Error.WriteLine(ex.ToString());
785     }
786     }
787 william 159
788 william 183 public void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs)
789     {
790     gLog.Info.WriteLine("Removing {0} old gbpvr programs.", programs.Count);
791     double total = programs.Count;
792     double index = 0;
793     double progress = 0;
794     foreach (var program in programs)
795     {
796     progress = 100.0 * (index / total);
797     gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing GBPVR Programs: {0:00}%", (int)progress)));
798     if (!RemoveProgramDatabaseEntry(program))
799     {
800     gLog.Error.WriteLine("Failed to remove program with oid: {0}", program.oid);
801     }
802     index++;
803     }
804     }
805     public void UpdateGBPVRPrograms(List<IOldNewProgram> programs)
806     {
807     gLog.Info.WriteLine("Updating {0} gbpvr programs.", programs.Count);
808     double total = programs.Count;
809     double index = 0;
810     double progress = 0;
811     foreach (var program in programs)
812     {
813     progress = 100.0 * (index / total);
814     gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Updating GBPVR Programs: {0:00}%", (int)progress)));
815     if (program.NewProgram.Equals(program.OldProgram))
816     {
817     // program does not need to be updated
818     continue;
819     }
820     else
821     {
822     if (!UpdateProgramDatabaseEntry(program.NewProgram))
823     {
824     gLog.Error.WriteLine("Failed to update program with oid: {0}", program.NewProgram.oid);
825     }
826     }
827     index++;
828     }
829     }
830 william 174
831     private bool RemoveProgramDatabaseEntry(IPROGRAMME old_program)
832     {
833     bool result = false;
834 william 183 try
835     {
836     using (SQLiteConnection con = CreateConnection())
837     {
838     try
839     {
840     gLog.Verbose.Info.WriteLine("Removing old program with oid: '{0}'", old_program.oid);
841     con.Open();
842     string command_text = string.Format(@"DELETE FROM [{0}] WHERE [oid] = @oid;", TABLES.PROGRAMME);
843     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
844     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
845     {
846     cmd.Parameters.Add(new SQLiteParameter("oid", old_program.oid));
847     int rowsupdated = cmd.ExecuteNonQuery();
848     gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
849     }
850     con.Clone();
851     result = true;
852     }
853     catch (SQLiteException ex)
854     {
855     gLog.Error.WriteLine(ex.ToString());
856     result = false;
857     }
858     }
859     }
860     catch (Exception ex)
861     {
862     gLog.Error.WriteLine(ex.ToString());
863     result = false;
864     }
865 william 174 return result;
866     }
867 william 183 private bool UpdateProgramDatabaseEntry(IPROGRAMME new_program)
868 william 174 {
869     bool result = false;
870 william 183 try
871     {
872     using (SQLiteConnection con = CreateConnection())
873     {
874     try
875     {
876     gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
877     con.Open();
878     //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
879     string command_text = string.Empty;
880     StringBuilder builder = new StringBuilder();
881     builder.AppendLine(string.Format("UPDATE [{0}]", TABLES.PROGRAMME));
882     builder.Append("SET ");
883     builder.AppendFormat("[name]=@name,");
884     builder.AppendFormat("[sub_title]=@sub_title,");
885     builder.AppendFormat("[description]=@description,");
886     builder.AppendFormat("[start_time]=@start_time,");
887     builder.AppendFormat("[end_time]=@end_time,");
888     builder.AppendFormat("[channel_oid]=@channel_oid,");
889     builder.AppendFormat("[unique_identifier]=@unique_identifier,");
890     builder.AppendFormat("[rating]=@rating");
891     builder.AppendLine(" WHERE [oid] = @oid");
892     command_text = builder.ToString();
893     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
894     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
895     {
896     cmd.Parameters.Add(new SQLiteParameter("oid", new_program.oid));
897     cmd.Parameters.Add(new SQLiteParameter("name", new_program.name));
898     cmd.Parameters.Add(new SQLiteParameter("sub_title", new_program.sub_title));
899     cmd.Parameters.Add(new SQLiteParameter("description", new_program.description));
900     cmd.Parameters.Add(new SQLiteParameter("start_time", new_program.start_time));
901     cmd.Parameters.Add(new SQLiteParameter("end_time", new_program.end_time));
902     cmd.Parameters.Add(new SQLiteParameter("channel_oid", new_program.channel_oid));
903     cmd.Parameters.Add(new SQLiteParameter("unique_identifier", new_program.unique_identifier));
904     cmd.Parameters.Add(new SQLiteParameter("rating", new_program.rating));
905     int rowsupdated = cmd.ExecuteNonQuery();
906     gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
907     }
908     con.Clone();
909     result = true;
910     }
911     catch (SQLiteException ex)
912     {
913     gLog.Error.WriteLine(ex.ToString());
914     result = false;
915     }
916     }
917     }
918     catch (Exception ex)
919     {
920     gLog.Error.WriteLine(ex.ToString());
921     result = false;
922     }
923 william 174 return result;
924     }
925 william 156 }
926     }

  ViewVC Help
Powered by ViewVC 1.1.22