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

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.22