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

Contents of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.22