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

Contents of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 182 - (show annotations) (download)
Sat Mar 16 18:23:11 2013 UTC (7 years, 4 months ago) by william
File size: 43356 byte(s)

1 //#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 using libxmltv;
10 using libxmltv.Interfaces;
11 using libxmltv.Core;
12 namespace GBPVRProgramDatabaseFixer
13 {
14 public interface IOldNewProgram
15 {
16 SQLLITE.IPROGRAMME OldProgram { get; }
17 SQLLITE.IPROGRAMME NewProgram { get; }
18 }
19 public interface ISQLLITE
20 {
21 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 List<IOldNewProgram> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> removed_programs);
26
27 }
28 public class SQLLITE : ISQLLITE
29 {
30 private class OldNewProgram : IOldNewProgram
31 {
32 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 }
38 public static ISQLLITE Create(string database, EventHandler<EventArgs> OnInstanceCreated)
39 {
40 return new SQLLITE(database, OnInstanceCreated);
41 }
42
43 #region DATABASE DEFINITIONS
44
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 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 public interface IPROGRAMME : IEquatable<IPROGRAMME>
206 {
207 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
217 string ToString();
218 IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite);
219 }
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 public IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite)
242 {
243 ProgramList.ProgramDefintion definition = new ProgramList.ProgramDefintion();
244
245 var channel = sqllite.Channels.Find(s => s.oid == this.channel_oid);
246 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 definition.Description = this.description;
251 definition.Start = this.start_time.ToDateTimeString();
252 definition.Stop = this.end_time.ToDateTimeString();
253 definition.SubTitle = this.sub_title;
254 definition.Title = this.name;
255 return definition;
256 }
257 #endregion
258
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 }
293 #endregion
294 private static class TABLES
295 {
296 public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
297 public const string PROGRAMME = "PROGRAMME";
298 public const string CHANNEL = "CHANNEL";
299 }
300
301 //public SQLLite() { }
302 protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
303 {
304 this.OnInstanceCreated = OnInstanceCreated;
305 //CreateConnection(database);
306 this.Database = database;
307 if (!CreateDatabaseBackup())
308 {
309 gLog.Error.WriteLine("Failed to backup database.");
310 return;
311 }
312 ConnectionTest();
313 ReadChannelData();
314 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 #region ISQLLITE members
330 public List<IPROGRAMME> Programs { get; private set; }
331 public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
332 public List<ICHANNEL> Channels { get; private set; }
333
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 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 var range = DateTimeRange.Create(first, last);
344 return range;
345 }
346
347 public List<IOldNewProgram> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> source_invalid)
348 {
349 source_invalid = new List<IPROGRAMME>();
350 List<IOldNewProgram> source_valid = new List<IOldNewProgram>();
351 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
356 double total = gbpvr_programs.Count;
357 double index = 0;
358 double progress = 0;
359 foreach (var program in gbpvr_programs)
360 {
361 progress = 100.0 * (index / total);
362 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Filtering GBPVR Programs: {0:00}%", (int)progress)));
363 // 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 if (xmltv_entry == null)
367 {
368 // xmltv entry was not found
369 source_invalid.Add(program);
370 }
371 else
372 {
373 // check if the xmltv entry has different data from the current program
374 if(!xmltv_entry.Equals(program_xmltv_entry))
375 {
376 // data is different
377 var updated_program = program;
378
379 if (program_xmltv_entry.Title != xmltv_entry.Title)
380 {
381 gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing title", updated_program.oid);
382 updated_program.name = xmltv_entry.Title;
383 }
384 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 }
412 else
413 {
414 // data is the same
415 source_valid.Add(new OldNewProgram(program, program));
416 }
417 }
418 index++;
419 }
420
421 #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
585 return source_valid;
586 }
587 #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 string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
607 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 con.Clone();
633 }
634 OnCreatedInstance(this, new EventArgs());
635 }
636 catch (Exception ex)
637 {
638 gLog.Error.WriteLine(ex.ToString());
639 }
640 }
641
642
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 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 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
785
786
787 private bool RemoveProgramDatabaseEntry(IPROGRAMME old_program)
788 {
789 bool result = false;
790 //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 return result;
822 }
823 private bool UpdateProgramDatabaseEntry(IPROGRAMME old_program, IPROGRAMME new_program)
824 {
825 bool result = false;
826 //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 return result;
880 }
881 }
882 }

  ViewVC Help
Powered by ViewVC 1.1.22