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

Contents of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.22