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

Contents of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 211 - (show annotations) (download)
Sun Mar 17 00:56:06 2013 UTC (7 years, 3 months ago) by william
File size: 55594 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<SQLLITE.IPROGRAMME> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> removed_programs);
26
27 void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs);
28 void UpdateGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs);
29
30 }
31 public class SQLLITE : ISQLLITE
32 {
33 private class OldNewProgram : IOldNewProgram
34 {
35 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 }
41 public static ISQLLITE Create(string database, EventHandler<EventArgs> OnInstanceCreated)
42 {
43 return new SQLLITE(database, OnInstanceCreated);
44 }
45
46 #region DATABASE DEFINITIONS
47
48 public interface ICHANNEL
49 {
50 Int64 oid { get; }
51 String name { get; }
52 String channelID { get; }
53 Int64 channel_number { get; }
54 String favourite_channel { get; }
55 String display_name { get; }
56 }
57 private class CHANNEL : ICHANNEL
58 {
59 public CHANNEL()
60 {
61 BaseDatabaseDefinition<CHANNEL>.CreateDefault(this);
62 }
63 //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
64
65 public static void Create(ref CHANNEL instance, SQLiteDataReader r, int index)
66 {
67 BaseDatabaseDefinition<CHANNEL>.Create(ref instance, r, index);
68 }
69 #region ICHANNEL members
70 public Int64 oid { get; set; }
71 public String name { get; set; }
72 public String channelID { get; set; }
73 public Int64 channel_number { get; set; }
74 public String favourite_channel { get; set; }
75 public String display_name { get; set; }
76 #endregion
77 }
78 public interface IRECORDING_SCHEDULE
79 {
80 Int64 oid { get; }
81 Int64 programme_oid { get; }
82 Int64 capture_source_oid { get; }
83 Int16 status { get; }
84 String filename { get; }
85 Int64 recording_type { get; }
86 Int64 recording_group { get; }
87 DateTime manual_start_time { get; }
88 DateTime manual_end_time { get; }
89 Int64 manual_channel_oid { get; }
90 Int64 quality_level { get; }
91 Int64 pre_pad_minutes { get; }
92 Int64 post_pad_minutes { get; }
93 Int32 priority { get; }
94 String conversion_profile { get; }
95 }
96
97 private static class BaseDatabaseDefinition<T>
98 {
99 public static void CreateDefault(T instance)
100 {
101 try
102 {
103 Type t = typeof(T);
104 var props = t.GetProperties();
105 foreach (var prop in props)
106 {
107 Type prop_type = prop.PropertyType;
108 object field_value = null;
109 try
110 {
111 if (prop_type == typeof(string))
112 {
113 field_value = string.Empty;
114 }
115 else
116 {
117 field_value = Activator.CreateInstance(prop_type);
118 }
119 }
120 catch (Exception ex)
121 {
122 throw ex;
123 }
124 prop.SetValue(instance, field_value, null);
125 }
126 }
127 catch (Exception ex)
128 {
129 throw ex;
130 }
131 }
132 public static void Create(ref T instance, SQLiteDataReader r, int index)
133 {
134 string field_name = r.GetName(index);
135 Type field_type = r.GetFieldType(index);
136 object field_value = r.GetValue(index);
137 //gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
138
139 Type t = typeof(T);
140 var props = t.GetProperties();
141 foreach (var prop in props)
142 {
143 if (prop.Name.ToLower() == field_name.ToLower())
144 {
145 if (prop.PropertyType == field_type)
146 {
147 Type db_type = field_value.GetType();
148 try
149 {
150 if (db_type == typeof(System.DBNull))
151 {
152 prop.SetValue(instance, null, null);
153 }
154 else
155 {
156 prop.SetValue(instance, field_value, null);
157 }
158
159 }
160 catch (Exception ex)
161 {
162 throw ex;
163 }
164 }
165 else
166 {
167 gLog.Verbose.Debug.WriteLine("Found Property: {0} but there was a type mismatch. Property Type: '{1}' Expected: '{2}'", prop.Name, prop.PropertyType.Name, field_type.Name);
168 throw new InvalidOperationException(string.Format("Found Property: {0} but there was a type mismatch. Property Type: '{1}' Expected: '{2}'", prop.Name, prop.PropertyType.Name, field_type.Name));
169 }
170 }
171 }
172
173 }
174 }
175
176 private class RECORDING_SCHEDULE : IRECORDING_SCHEDULE
177 {
178 public RECORDING_SCHEDULE()
179 {
180 BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this);
181 }
182 //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
183
184 public static void Create(ref RECORDING_SCHEDULE instance, SQLiteDataReader r, int index)
185 {
186 BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(ref instance, r, index);
187 }
188
189 #region IRECORDING_SCHEDULE members
190 public Int64 oid { get; set; }
191 public Int64 programme_oid { get; set; }
192 public Int64 capture_source_oid { get; set; }
193 public Int16 status { get; set; }
194 public String filename { get; set; }
195 public Int64 recording_type { get; set; }
196 public Int64 recording_group { get; set; }
197 public DateTime manual_start_time { get; set; }
198 public DateTime manual_end_time { get; set; }
199 public Int64 manual_channel_oid { get; set; }
200 public Int64 quality_level { get; set; }
201 public Int64 pre_pad_minutes { get; set; }
202 public Int64 post_pad_minutes { get; set; }
203 public Int32 priority { get; set; }
204 public String conversion_profile { get; set; }
205 #endregion
206 }
207
208 public interface IPROGRAMME : IEquatable<IPROGRAMME>
209 {
210 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
220 string ToString();
221 IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite);
222 }
223 private class PROGRAMME : IPROGRAMME
224 {
225 public PROGRAMME()
226 {
227 BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this);
228 }
229 //public PROGRAMME(SQLiteDataReader r, int index) : base(r, index) { }
230 public static void Create(ref PROGRAMME instance, SQLiteDataReader r, int index)
231 {
232 BaseDatabaseDefinition<PROGRAMME>.Create(ref instance, r, index);
233 }
234 #region IPROGRAMME members
235 public Int64 oid { get; set; }
236 public String name { get; set; }
237 public String sub_title { get; set; }
238 public String description { get; set; }
239 public DateTime start_time { get; set; }
240 public DateTime end_time { get; set; }
241 public Int64 channel_oid { get; set; }
242 public String unique_identifier { get; set; }
243 public String rating { get; set; }
244 public IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite)
245 {
246 ProgramList.ProgramDefintion definition = new ProgramList.ProgramDefintion();
247
248 var channel = sqllite.Channels.Find(s => s.oid == this.channel_oid);
249 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 definition.Description = this.description;
254 definition.Start = this.start_time.ToDateTimeString();
255 definition.Stop = this.end_time.ToDateTimeString();
256 definition.SubTitle = this.sub_title;
257 definition.Title = this.name;
258 return definition;
259 }
260 #endregion
261
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 }
296 #endregion
297 private static class TABLES
298 {
299 public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
300 public const string PROGRAMME = "PROGRAMME";
301 public const string CHANNEL = "CHANNEL";
302 }
303 private string DATABASE_BACKUP_FILE = string.Empty;
304 //public SQLLite() { }
305 protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
306 {
307 this.OnInstanceCreated = OnInstanceCreated;
308 //CreateConnection(database);
309 this.Database = database;
310 if (!CreateDatabaseBackup())
311 {
312 gLog.Error.WriteLine("Failed to backup database.");
313 return;
314 }
315 ConnectionTest();
316 ReadChannelData();
317 ReadRecodringScheduleData();
318 ReadProgrammeData();
319 OnCreatedInstance(this, new EventArgs());
320 }
321
322 [NonSerialized]
323 private EventHandler<EventArgs> _OnInstanceCreated;
324 private EventHandler<EventArgs> OnInstanceCreated { get { return _OnInstanceCreated; } set { _OnInstanceCreated = value; } }
325
326 private void OnCreatedInstance(object sender, EventArgs e)
327 {
328 if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
329 }
330
331 private string Database;
332 #region ISQLLITE members
333 public List<IPROGRAMME> Programs { get; private set; }
334 public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
335 public List<ICHANNEL> Channels { get; private set; }
336
337 public IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs)
338 {
339 var list = new List<IPROGRAMME>(programs.ToArray());
340 DateTime first = new DateTime();
341 DateTime last = new DateTime();
342 first = list.OrderBy(s => s.start_time).ToList().First().start_time;
343 last = list.OrderBy(s => s.start_time).ToList().Last().start_time;
344 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 var range = DateTimeRange.Create(first, last);
347 return range;
348 }
349
350 public List<SQLLITE.IPROGRAMME> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> source_invalid)
351 {
352 source_invalid = new List<IPROGRAMME>();
353 List<IPROGRAMME> source_valid = new List<IPROGRAMME>();
354 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
359 double total = gbpvr_programs.Count;
360 double index = 0;
361 double progress = 0;
362 foreach (var program in gbpvr_programs)
363 {
364 progress = 100.0 * (index / total);
365 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Filtering GBPVR Programs: {0:00}%", (int)progress)));
366 // 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 if (xmltv_entry == null)
370 {
371 // xmltv entry was not found
372 source_invalid.Add(program);
373 }
374 else
375 {
376 // check if the xmltv entry has different data from the current program
377 if (!xmltv_entry.Equals(program_xmltv_entry))
378 {
379 // data is different
380 var updated_program = program;
381
382 if (program_xmltv_entry.Title != xmltv_entry.Title)
383 {
384 gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing title", updated_program.oid);
385 updated_program.name = xmltv_entry.Title;
386 }
387 if (program_xmltv_entry.SubTitle != xmltv_entry.SubTitle)
388 {
389 gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing sub_title", updated_program.oid);
390 updated_program.sub_title = xmltv_entry.SubTitle;
391 }
392 if (program_xmltv_entry.Stop != xmltv_entry.Stop)
393 {
394 gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing end_time", updated_program.oid);
395 updated_program.end_time = DateTime.Parse(xmltv_entry.Stop);
396 }
397 if (program_xmltv_entry.Start != xmltv_entry.Start)
398 {
399 gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing start_time", updated_program.oid);
400 updated_program.start_time = DateTime.Parse(xmltv_entry.Start);
401 }
402 if (program_xmltv_entry.Description != xmltv_entry.Description)
403 {
404 gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing description", updated_program.oid);
405 updated_program.description = xmltv_entry.Description;
406 }
407
408 var updated_xmltv_entry = updated_program.AsXMLTVProgramDefinition(this);
409 if (!xmltv_entry.Equals(updated_xmltv_entry))
410 {
411 throw new Exception(string.Format("Program oid '{0}' was not properly updated.", updated_program.oid));
412 }
413 //source_valid.Add(new OldNewProgram(program, updated_program));
414 source_valid.Add(updated_program);
415 }
416 else
417 {
418 // data is the same
419 //source_valid.Add(new OldNewProgram(program, program));
420 source_valid.Add(program);
421 }
422 }
423 index++;
424 }
425 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 #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
593 return source_valid;
594 }
595 #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 string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
615 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 con.Clone();
641 }
642 OnCreatedInstance(this, new EventArgs());
643 }
644 catch (Exception ex)
645 {
646 gLog.Error.WriteLine(ex.ToString());
647 }
648 }
649
650
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 DATABASE_BACKUP_FILE = backup_file;
662 return true;
663 }
664 catch (Exception ex)
665 {
666 gLog.Error.WriteLine(ex.ToString());
667 return false;
668 }
669 }
670 private void ReadChannelData()
671 {
672 try
673 {
674 List<ICHANNEL> channels = new List<ICHANNEL>();
675 using (SQLiteConnection con = CreateConnection())
676 {
677 try
678 {
679 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 {
684 using (SQLiteDataReader r = cmd.ExecuteReader())
685 {
686 if (!r.HasRows)
687 {
688 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
689 }
690 else
691 {
692 while (r.Read())
693 {
694 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 }
701 }
702 }
703 }
704 }
705 catch (SQLiteException ex)
706 {
707 gLog.Error.WriteLine(ex.ToString());
708 }
709 finally
710 {
711 con.Close();
712 }
713 }
714 this.Channels = channels;
715 }
716 catch (Exception ex)
717 {
718 gLog.Error.WriteLine(ex.ToString());
719 }
720 }
721 private void ReadProgrammeData()
722 {
723 try
724 {
725 List<IPROGRAMME> programs = new List<IPROGRAMME>();
726 using (SQLiteConnection con = CreateConnection())
727 {
728 try
729 {
730 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 {
735 using (SQLiteDataReader r = cmd.ExecuteReader())
736 {
737 if (!r.HasRows)
738 {
739 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
740 }
741 else
742 {
743 while (r.Read())
744 {
745 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 }
752 }
753 }
754 }
755 }
756 catch (SQLiteException ex)
757 {
758 gLog.Error.WriteLine(ex.ToString());
759 }
760 finally
761 {
762 con.Close();
763 }
764 }
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 try
780 {
781 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 {
786 using (SQLiteDataReader r = cmd.ExecuteReader())
787 {
788 if (!r.HasRows)
789 {
790 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
791 }
792 else
793 {
794 while (r.Read())
795 {
796 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 }
803 }
804 }
805 }
806 }
807 catch (SQLiteException ex)
808 {
809 gLog.Error.WriteLine(ex.ToString());
810 }
811 finally
812 {
813 con.Close();
814 }
815 }
816 this.Recordings = recordings;
817 }
818 catch (Exception ex)
819 {
820 gLog.Error.WriteLine(ex.ToString());
821 }
822 }
823
824 public void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs)
825 {
826 //gLog.Info.WriteLine("Removing {0} gbpvr programs.", programs.Count);
827 //double total = programs.Count;
828 //double index = 0;
829 //double progress = 0;
830 //Stopwatch st = new Stopwatch();
831 //st.Start();
832 //foreach (var program in programs)
833 //{
834 // progress = 100.0 * (index / total);
835 // gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing GBPVR Program ({0:00}%) oid='{1}'", (int)progress, program.oid)));
836 // if (!RemoveProgramDatabaseEntry(program))
837 // {
838 // gLog.Error.WriteLine("Failed to remove program with oid: {0}", program.oid);
839 // }
840 // index++;
841 //}
842 ////Stopwatch st = new Stopwatch();
843 ////st.Start();
844 ////if (!RemoveAllProgramDatabaseEntries())
845 ////{
846 //// gLog.Error.WriteLine("Failed to remove one or more program(s)");
847 ////}
848 //st.Stop();
849 //gLog.Warn.WriteLine(" operation took: {0:0.00000} seconds", st.Elapsed.TotalSeconds);
850
851 int ChunkSize = 1024;
852 var ChunkList = programs.Chunk<IPROGRAMME>(ChunkSize);
853
854 double total = programs.Count;
855 double index = 0;
856 double progress = 0;
857 Stopwatch st1 = new Stopwatch();
858 st1.Start();
859 foreach (var p in ChunkList)
860 {
861 progress = 100.0 * (index / total);
862 if (!RemoveProgramDatabaseEntry(p))
863 {
864 gLog.Error.WriteLine("Failed to remove one or more program(s)");
865 }
866 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing {0} gbpvr programs ({1} of {2}) {0:00}%", p.Count(), index, total, (int)progress)));
867 index += (double)p.Count();
868 }
869 st1.Stop();
870 gLog.Warn.WriteLine(" operation took: {0:0.00000} seconds overall", st1.Elapsed.TotalSeconds);
871 }
872 public void UpdateGBPVRPrograms(List<IPROGRAMME> programs)
873 {
874 int ChunkSize = 1024;
875 var ChunkList = programs.Chunk<IPROGRAMME>(ChunkSize);
876
877 double total = programs.Count;
878 double index = 0;
879 double progress = 0;
880 Stopwatch st1 = new Stopwatch();
881 st1.Start();
882 foreach (var p in ChunkList)
883 {
884 //Stopwatch st2 = new Stopwatch();
885 //st2.Start();
886 progress = 100.0 * (index / total);
887 //gLog.Warn.WriteLine("Inserting {0} gbpvr programs ({1} of {2})", p.Count(), index, total);
888 if (!UpdateProgramEntryDatabase(p))
889 {
890 gLog.Error.WriteLine("Failed to update one or more program(s)");
891 }
892 //st2.Stop();
893 //gLog.Warn.WriteLine(" operation took: {0:0.00000} seconds", st2.Elapsed.TotalSeconds);
894 //gLog.Warn.WriteLine(System.Environment.NewLine);
895 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Updating {0} gbpvr programs ({1} of {2}) {0:00}%", p.Count(), index, total, (int)progress)));
896 index += (double)p.Count();
897 }
898 st1.Stop();
899 gLog.Warn.WriteLine(" operation took: {0:0.00000} seconds overall", st1.Elapsed.TotalSeconds);
900 }
901
902 private bool RemoveAllProgramDatabaseEntries()
903 {
904 bool result = false;
905 try
906 {
907 using (SQLiteConnection con = CreateConnection())
908 {
909 try
910 {
911 //gLog.Verbose.Info.WriteLine("Removing old program with oid: '{0}'", old_program.oid);
912 con.Open();
913 string command_text = string.Format(@"DELETE FROM [{0}];", TABLES.PROGRAMME);
914 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
915 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
916 {
917 //cmd.Parameters.Add(new SQLiteParameter("oid", old_program.oid));
918 int rowsupdated = cmd.ExecuteNonQuery();
919 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
920 }
921 result = true;
922 }
923 catch (SQLiteException ex)
924 {
925 gLog.Error.WriteLine(ex.ToString());
926 result = false;
927 }
928 finally
929 {
930 con.Close();
931 }
932 }
933 }
934 catch (Exception ex)
935 {
936 gLog.Error.WriteLine(ex.ToString());
937 result = false;
938 }
939 return result;
940 }
941 private bool RemoveProgramDatabaseEntry(IEnumerable<IPROGRAMME> list)
942 {
943 bool result = false;
944 try
945 {
946 using (SQLiteConnection con = CreateConnection())
947 {
948 try
949 {
950 //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
951 con.Open();
952 //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
953 string command_text = string.Empty;
954 command_text = BuildGBPVRMultiDeleteCommand(list);
955 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
956 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
957 {
958 int rowsupdated = cmd.ExecuteNonQuery();
959 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
960 }
961
962 result = true;
963 }
964 catch (SQLiteException ex)
965 {
966 gLog.Error.WriteLine(ex.ToString());
967 result = false;
968 }
969 finally
970 {
971 con.Close();
972 }
973 }
974 }
975 catch (Exception ex)
976 {
977 gLog.Error.WriteLine(ex.ToString());
978 result = false;
979 }
980 return result;
981 }
982 private bool UpdateProgramEntryDatabase(IEnumerable<IPROGRAMME> list)
983 {
984 bool result = false;
985 try
986 {
987 using (SQLiteConnection con = CreateConnection())
988 {
989 try
990 {
991 //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
992 con.Open();
993 //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
994 string command_text = string.Empty;
995 command_text = BuildGBPVRMultiUpdateCommand(list);
996 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
997 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
998 {
999 int rowsupdated = cmd.ExecuteNonQuery();
1000 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
1001 }
1002
1003 result = true;
1004 }
1005 catch (SQLiteException ex)
1006 {
1007 gLog.Error.WriteLine(ex.ToString());
1008 result = false;
1009 }
1010 finally
1011 {
1012 con.Close();
1013 }
1014 }
1015 }
1016 catch (Exception ex)
1017 {
1018 gLog.Error.WriteLine(ex.ToString());
1019 result = false;
1020 }
1021 return result;
1022 }
1023 private bool InsertProgramEntryDatabase(IEnumerable<IPROGRAMME> list)
1024 {
1025 bool result = false;
1026 try
1027 {
1028 using (SQLiteConnection con = CreateConnection())
1029 {
1030 try
1031 {
1032 //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
1033 con.Open();
1034 //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
1035 string command_text = string.Empty;
1036 command_text = BuildGBPVRMultiInsertCommand(list);
1037 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
1038 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
1039 {
1040 int rowsupdated = cmd.ExecuteNonQuery();
1041 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
1042 }
1043
1044 result = true;
1045 }
1046 catch (SQLiteException ex)
1047 {
1048 gLog.Error.WriteLine(ex.ToString());
1049 result = false;
1050 }
1051 finally
1052 {
1053 con.Close();
1054 }
1055 }
1056 }
1057 catch (Exception ex)
1058 {
1059 gLog.Error.WriteLine(ex.ToString());
1060 result = false;
1061 }
1062 return result;
1063 }
1064
1065 #region Multi-Delete Command Support
1066 private string BuildGBPVRMultiDeleteCommand(IEnumerable<IPROGRAMME> list)
1067 {
1068 StringBuilder builder = new StringBuilder();
1069 /*
1070 insert into table1 (field1,field2) values (value1,value2);
1071 insert into table1 (field1,field2) values (value1,value2);
1072 insert into table1 (field1,field2) values (value1,value2);
1073 insert into table1 (field1,field2) values (value1,value2)
1074 */
1075 builder.AppendLine("begin transaction;");
1076 foreach (var t in list)
1077 {
1078 builder.AppendLine(BuildGBPVRSingleDeleteCommand(t));
1079 }
1080 builder.AppendLine("end transaction;");
1081 return builder.ToString();
1082 }
1083 private string BuildGBPVRSingleDeleteCommand(IPROGRAMME program)
1084 {
1085 StringBuilder builder = new StringBuilder();
1086 builder.AppendFormat("delete from {0} where oid={1};", TABLES.PROGRAMME, program.oid);
1087 return builder.ToString();
1088 }
1089 #endregion
1090 #region Multi-Update Command Support
1091 private string BuildGBPVRMultiUpdateCommand(IEnumerable<IPROGRAMME> list)
1092 {
1093 StringBuilder builder = new StringBuilder();
1094 /*
1095 insert into table1 (field1,field2) values (value1,value2);
1096 insert into table1 (field1,field2) values (value1,value2);
1097 insert into table1 (field1,field2) values (value1,value2);
1098 insert into table1 (field1,field2) values (value1,value2)
1099 */
1100 builder.AppendLine("begin transaction;");
1101 foreach (var t in list)
1102 {
1103 builder.AppendLine(BuildGBPVRSingleInsertCommand(t));
1104 }
1105 builder.AppendLine("end transaction;");
1106 return builder.ToString();
1107 }
1108 private string BuildGBPVRSingleUpdateCommand(IPROGRAMME program)
1109 {
1110 StringBuilder builder = new StringBuilder();
1111 builder.AppendFormat("update {0} SET ", TABLES.PROGRAMME);
1112 builder.AppendFormat("name=\"{0}\", ", program.name);
1113 builder.AppendFormat("sub_title=\"{0}\", ", program.sub_title);
1114 builder.AppendFormat("description=\"{0}\", ", program.description);
1115 builder.AppendFormat("start_time='{0}', ", program.start_time.ToString("yyyy/MM/dd HH:mm:ss.fffffff"));
1116 builder.AppendFormat("end_time='{0}', ", program.end_time.ToString("yyyy/MM/dd HH:mm:ss.fffffff"));
1117 builder.AppendFormat("channel_oid={0}, ", program.channel_oid);
1118 builder.AppendFormat("unique_identifier=\"{0}\", ", program.unique_identifier);
1119 builder.AppendFormat("rating=\"{0}\" ", program.rating);
1120 builder.AppendFormat("where oid={0};", program.oid);
1121 return builder.ToString();
1122 }
1123 #endregion
1124 #region Multi-Insert Command Support
1125 private string BuildGBPVRMultiInsertCommand(IEnumerable<IPROGRAMME> list)
1126 {
1127 StringBuilder builder = new StringBuilder();
1128 /*
1129 insert into table1 (field1,field2) values (value1,value2);
1130 insert into table1 (field1,field2) values (value1,value2);
1131 insert into table1 (field1,field2) values (value1,value2);
1132 insert into table1 (field1,field2) values (value1,value2)
1133 */
1134 builder.AppendLine("begin transaction;");
1135 foreach (var t in list)
1136 {
1137 builder.AppendLine(BuildGBPVRSingleInsertCommand(t));
1138 }
1139 builder.AppendLine("end transaction;");
1140 return builder.ToString();
1141 }
1142
1143 private string BuildGBPVRSingleInsertCommand(IPROGRAMME program)
1144 {
1145 StringBuilder builder = new StringBuilder();
1146 builder.AppendFormat("insert into {0} (oid,name,sub_title,description,start_time,end_time,channel_oid,unique_identifier,rating) values (", TABLES.PROGRAMME);
1147 builder.AppendFormat("{0},",program.oid);
1148 builder.AppendFormat("\"{0}\",", program.name);
1149 builder.AppendFormat("\"{0}\",", program.sub_title);
1150 builder.AppendFormat("\"{0}\",", program.description);
1151 builder.AppendFormat("'{0}',", program.start_time.ToString("yyyy/MM/dd HH:mm:ss.fffffff"));
1152 builder.AppendFormat("'{0}',", program.end_time.ToString("yyyy/MM/dd HH:mm:ss.fffffff"));
1153 builder.AppendFormat("{0},", program.channel_oid);
1154 builder.AppendFormat("\"{0}\",", program.unique_identifier);
1155 builder.AppendFormat("\"{0}\");", program.rating);
1156 return builder.ToString();
1157 }
1158 #endregion
1159
1160 }
1161 }

  ViewVC Help
Powered by ViewVC 1.1.22