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

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 187 - (hide annotations) (download)
Sat Mar 16 19:13:18 2013 UTC (7 years, 4 months ago) by william
File size: 51951 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 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 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 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     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 william 184 try
672 william 159 {
673 william 184 con.Open();
674     string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
675     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
676     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
677 william 159 {
678 william 184 using (SQLiteDataReader r = cmd.ExecuteReader())
679 william 159 {
680 william 184 if (!r.HasRows)
681 william 159 {
682 william 184 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
683     }
684     else
685     {
686     while (r.Read())
687 william 159 {
688 william 184 CHANNEL channel = new CHANNEL();
689     for (int i = 0; i < r.FieldCount; i++)
690     {
691     CHANNEL.Create(ref channel, r, i);
692     }
693     channels.Add(channel);
694 william 159 }
695     }
696     }
697     }
698     }
699 william 184 catch (SQLiteException ex)
700     {
701     gLog.Error.WriteLine(ex.ToString());
702     }
703     finally
704     {
705     con.Close();
706     }
707 william 159 }
708     this.Channels = channels;
709     }
710     catch (Exception ex)
711     {
712     gLog.Error.WriteLine(ex.ToString());
713     }
714     }
715 william 156 private void ReadProgrammeData()
716     {
717     try
718     {
719     List<IPROGRAMME> programs = new List<IPROGRAMME>();
720     using (SQLiteConnection con = CreateConnection())
721     {
722 william 184 try
723 william 156 {
724 william 184 con.Open();
725     string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
726     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
727     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
728 william 156 {
729 william 184 using (SQLiteDataReader r = cmd.ExecuteReader())
730 william 156 {
731 william 184 if (!r.HasRows)
732 william 156 {
733 william 184 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
734     }
735     else
736     {
737     while (r.Read())
738 william 156 {
739 william 184 PROGRAMME program = new PROGRAMME();
740     for (int i = 0; i < r.FieldCount; i++)
741     {
742     PROGRAMME.Create(ref program, r, i);
743     }
744     programs.Add(program);
745 william 156 }
746     }
747     }
748     }
749     }
750 william 184 catch (SQLiteException ex)
751     {
752     gLog.Error.WriteLine(ex.ToString());
753     }
754     finally
755     {
756     con.Close();
757     }
758 william 156 }
759     this.Programs = programs;
760     }
761     catch (Exception ex)
762     {
763     gLog.Error.WriteLine(ex.ToString());
764     }
765     }
766     private void ReadRecodringScheduleData()
767     {
768     try
769     {
770     List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
771     using (SQLiteConnection con = CreateConnection())
772     {
773 william 184 try
774 william 156 {
775 william 184 con.Open();
776     string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
777     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
778     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
779 william 156 {
780 william 184 using (SQLiteDataReader r = cmd.ExecuteReader())
781 william 156 {
782 william 184 if (!r.HasRows)
783 william 156 {
784 william 184 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
785     }
786     else
787     {
788     while (r.Read())
789 william 156 {
790 william 184 RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
791     for (int i = 0; i < r.FieldCount; i++)
792     {
793     RECORDING_SCHEDULE.Create(ref recording, r, i);
794     }
795     recordings.Add(recording);
796 william 156 }
797     }
798     }
799     }
800     }
801 william 184 catch (SQLiteException ex)
802     {
803     gLog.Error.WriteLine(ex.ToString());
804     }
805     finally
806     {
807     con.Close();
808     }
809 william 156 }
810     this.Recordings = recordings;
811     }
812     catch (Exception ex)
813     {
814     gLog.Error.WriteLine(ex.ToString());
815     }
816     }
817 william 159
818 william 183 public void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs)
819     {
820 william 187 gLog.Info.WriteLine("Removing all gbpvr programs.");
821 william 186 //double total = programs.Count;
822     //double index = 0;
823     //double progress = 0;
824     //foreach (var program in programs)
825     //{
826     // progress = 100.0 * (index / total);
827     // gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing GBPVR Programs: {0:00}%", (int)progress)));
828     // if (!RemoveProgramDatabaseEntry(program))
829     // {
830     // gLog.Error.WriteLine("Failed to remove program with oid: {0}", program.oid);
831     // }
832     // index++;
833     //}
834     if (!RemoveAllProgramDatabaseEntries())
835     {
836     gLog.Error.WriteLine("Failed to remove one or more program entries");
837     }
838 william 183 }
839     public void UpdateGBPVRPrograms(List<IOldNewProgram> programs)
840     {
841 william 187 gLog.Info.WriteLine("Inserting {0} gbpvr programs.", programs.Count);
842 william 183 double total = programs.Count;
843     double index = 0;
844     double progress = 0;
845     foreach (var program in programs)
846     {
847     progress = 100.0 * (index / total);
848 william 187 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Updating/Inserting GBPVR Programs: {0:00}%", (int)progress)));
849 william 183 if (program.NewProgram.Equals(program.OldProgram))
850     {
851     // program does not need to be updated
852     continue;
853     }
854     else
855     {
856 william 186 if (!InsertProgramDatabaseEntry(program.NewProgram))
857 william 183 {
858 william 186 gLog.Error.WriteLine("Failed to insert program with oid: {0}", program.NewProgram.oid);
859 william 183 }
860     }
861     index++;
862     }
863     }
864 william 174
865 william 186 private bool RemoveAllProgramDatabaseEntries()
866     {
867     bool result = false;
868     try
869     {
870     using (SQLiteConnection con = CreateConnection())
871     {
872     try
873     {
874     //gLog.Verbose.Info.WriteLine("Removing old program with oid: '{0}'", old_program.oid);
875     con.Open();
876     string command_text = string.Format(@"DELETE FROM [{0}];", TABLES.PROGRAMME);
877     //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
878     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
879     {
880     //cmd.Parameters.Add(new SQLiteParameter("oid", old_program.oid));
881     int rowsupdated = cmd.ExecuteNonQuery();
882     //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
883     }
884     result = true;
885     }
886     catch (SQLiteException ex)
887     {
888     gLog.Error.WriteLine(ex.ToString());
889     result = false;
890     }
891     finally
892     {
893     con.Close();
894     }
895     }
896     }
897     catch (Exception ex)
898     {
899     gLog.Error.WriteLine(ex.ToString());
900     result = false;
901     }
902     return result;
903     }
904 william 174 private bool RemoveProgramDatabaseEntry(IPROGRAMME old_program)
905     {
906     bool result = false;
907 william 183 try
908     {
909     using (SQLiteConnection con = CreateConnection())
910     {
911     try
912     {
913 william 185 //gLog.Verbose.Info.WriteLine("Removing old program with oid: '{0}'", old_program.oid);
914 william 183 con.Open();
915     string command_text = string.Format(@"DELETE FROM [{0}] WHERE [oid] = @oid;", TABLES.PROGRAMME);
916 william 185 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
917 william 183 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
918     {
919     cmd.Parameters.Add(new SQLiteParameter("oid", old_program.oid));
920     int rowsupdated = cmd.ExecuteNonQuery();
921 william 185 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
922 william 183 }
923     result = true;
924     }
925     catch (SQLiteException ex)
926     {
927     gLog.Error.WriteLine(ex.ToString());
928     result = false;
929     }
930 william 184 finally
931     {
932     con.Close();
933     }
934 william 183 }
935     }
936     catch (Exception ex)
937     {
938     gLog.Error.WriteLine(ex.ToString());
939     result = false;
940     }
941 william 174 return result;
942     }
943 william 186 private bool InsertProgramDatabaseEntry(IPROGRAMME new_program)
944     {
945     bool result = false;
946     try
947     {
948     using (SQLiteConnection con = CreateConnection())
949     {
950     try
951     {
952     //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
953     con.Open();
954     //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
955     string command_text = string.Empty;
956     StringBuilder builder = new StringBuilder();
957     builder.AppendLine(string.Format("INSERT INTO [{0}]", TABLES.PROGRAMME));
958     builder.Append("VALUES (");
959     builder.AppendFormat("[oid]=@oid,");
960     builder.AppendFormat("[name]=@name,");
961     builder.AppendFormat("[sub_title]=@sub_title,");
962     builder.AppendFormat("[description]=@description,");
963     builder.AppendFormat("[start_time]=@start_time,");
964     builder.AppendFormat("[end_time]=@end_time,");
965     builder.AppendFormat("[channel_oid]=@channel_oid,");
966     builder.AppendFormat("[unique_identifier]=@unique_identifier,");
967     builder.AppendFormat("[rating]=@rating");
968     //builder.AppendLine(" WHERE [oid] = @oid");
969     builder.AppendFormat(");");
970     command_text = builder.ToString();
971     //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
972     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
973     {
974     cmd.Parameters.Add(new SQLiteParameter("oid", new_program.oid));
975     cmd.Parameters.Add(new SQLiteParameter("name", new_program.name));
976     cmd.Parameters.Add(new SQLiteParameter("sub_title", new_program.sub_title));
977     cmd.Parameters.Add(new SQLiteParameter("description", new_program.description));
978     cmd.Parameters.Add(new SQLiteParameter("start_time", new_program.start_time));
979     cmd.Parameters.Add(new SQLiteParameter("end_time", new_program.end_time));
980     cmd.Parameters.Add(new SQLiteParameter("channel_oid", new_program.channel_oid));
981     cmd.Parameters.Add(new SQLiteParameter("unique_identifier", new_program.unique_identifier));
982     cmd.Parameters.Add(new SQLiteParameter("rating", new_program.rating));
983     int rowsupdated = cmd.ExecuteNonQuery();
984     //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
985     }
986    
987     result = true;
988     }
989     catch (SQLiteException ex)
990     {
991     gLog.Error.WriteLine(ex.ToString());
992     result = false;
993     }
994     finally
995     {
996     con.Close();
997     }
998     }
999     }
1000     catch (Exception ex)
1001     {
1002     gLog.Error.WriteLine(ex.ToString());
1003     result = false;
1004     }
1005     return result;
1006     }
1007 william 183 private bool UpdateProgramDatabaseEntry(IPROGRAMME new_program)
1008 william 174 {
1009     bool result = false;
1010 william 183 try
1011     {
1012     using (SQLiteConnection con = CreateConnection())
1013     {
1014     try
1015     {
1016 william 185 //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
1017 william 183 con.Open();
1018     //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
1019     string command_text = string.Empty;
1020     StringBuilder builder = new StringBuilder();
1021     builder.AppendLine(string.Format("UPDATE [{0}]", TABLES.PROGRAMME));
1022     builder.Append("SET ");
1023     builder.AppendFormat("[name]=@name,");
1024     builder.AppendFormat("[sub_title]=@sub_title,");
1025     builder.AppendFormat("[description]=@description,");
1026     builder.AppendFormat("[start_time]=@start_time,");
1027     builder.AppendFormat("[end_time]=@end_time,");
1028     builder.AppendFormat("[channel_oid]=@channel_oid,");
1029     builder.AppendFormat("[unique_identifier]=@unique_identifier,");
1030     builder.AppendFormat("[rating]=@rating");
1031 william 186 builder.AppendLine(" WHERE [oid] = @oid;");
1032 william 183 command_text = builder.ToString();
1033 william 185 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
1034 william 183 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
1035     {
1036     cmd.Parameters.Add(new SQLiteParameter("oid", new_program.oid));
1037     cmd.Parameters.Add(new SQLiteParameter("name", new_program.name));
1038     cmd.Parameters.Add(new SQLiteParameter("sub_title", new_program.sub_title));
1039     cmd.Parameters.Add(new SQLiteParameter("description", new_program.description));
1040     cmd.Parameters.Add(new SQLiteParameter("start_time", new_program.start_time));
1041     cmd.Parameters.Add(new SQLiteParameter("end_time", new_program.end_time));
1042     cmd.Parameters.Add(new SQLiteParameter("channel_oid", new_program.channel_oid));
1043     cmd.Parameters.Add(new SQLiteParameter("unique_identifier", new_program.unique_identifier));
1044     cmd.Parameters.Add(new SQLiteParameter("rating", new_program.rating));
1045     int rowsupdated = cmd.ExecuteNonQuery();
1046 william 185 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
1047 william 183 }
1048 william 184
1049 william 183 result = true;
1050     }
1051     catch (SQLiteException ex)
1052     {
1053     gLog.Error.WriteLine(ex.ToString());
1054     result = false;
1055     }
1056 william 184 finally
1057     {
1058     con.Close();
1059     }
1060 william 183 }
1061     }
1062     catch (Exception ex)
1063     {
1064     gLog.Error.WriteLine(ex.ToString());
1065     result = false;
1066     }
1067 william 174 return result;
1068     }
1069 william 156 }
1070     }

  ViewVC Help
Powered by ViewVC 1.1.22