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

Contents of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.22