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

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.22