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

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 174 - (hide annotations) (download)
Sat Mar 16 16:03:19 2013 UTC (7 years, 3 months ago) by william
File size: 34413 byte(s)

1 william 156 //#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 william 163 using libxmltv;
10 william 165 using libxmltv.Interfaces;
11     using libxmltv.Core;
12 william 156 namespace GBPVRProgramDatabaseFixer
13     {
14 william 163 public interface ISQLLITE
15 william 156 {
16 william 163 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 william 174 List<SQLLITE.IPROGRAMME> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> removed_programs);
21 william 163
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 william 156 #region DATABASE DEFINITIONS
31 william 159
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 william 156 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 william 168 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 william 165
204     IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite);
205 william 156 }
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 william 165 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 william 168 definition.Start = this.start_time.ToDateTimeString();
235     definition.Stop = this.end_time.ToDateTimeString();
236 william 165 definition.SubTitle = this.sub_title;
237     definition.Title = this.name;
238     return definition;
239     }
240 william 156 #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 william 159 public const string CHANNEL = "CHANNEL";
248 william 156 }
249    
250     //public SQLLite() { }
251 william 163 protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
252 william 156 {
253     this.OnInstanceCreated = OnInstanceCreated;
254     //CreateConnection(database);
255     this.Database = database;
256 william 157 if (!CreateDatabaseBackup())
257     {
258     gLog.Error.WriteLine("Failed to backup database.");
259     return;
260     }
261 william 156 ConnectionTest();
262 william 159 ReadChannelData();
263 william 156 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 william 163 #region ISQLLITE members
279 william 156 public List<IPROGRAMME> Programs { get; private set; }
280     public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
281 william 159 public List<ICHANNEL> Channels { get; private set; }
282 william 163
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 william 167 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 william 163 var range = DateTimeRange.Create(first, last);
293     return range;
294     }
295    
296 william 174 public List<SQLLITE.IPROGRAMME> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> source_invalid)
297 william 163 {
298 william 169 source_invalid = new List<IPROGRAMME>();
299 william 168 gbpvr_programs = gbpvr_programs.OrderBy(s => s.start_time).ToList();
300 william 166 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 william 163 //gLog.Warn.WriteLine("FilterProgramsByDateRange has not been implemented");
307 william 170
308 william 168 double total = gbpvr_programs.Count;
309 william 166 double index = 0;
310     double progress = 0;
311 william 168 foreach (var program in gbpvr_programs)
312 william 169 {
313     progress = 100.0 * (index / total);
314     gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Filtering GBPVR Programs: {0:00}%", (int)progress)));
315 william 168 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 william 172 //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 william 170 IProgramDefinition xmltv_entry = null;
326 william 172 if (query.Count()-1 > 0)
327 william 170 {
328     gLog.Error.WriteLine("Found more than one entry: Matching channel='{0}' and start='{1}'", channel.channel_number, start_date.ToDateTimeString());
329 william 171 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 william 170 if (Debugger.IsAttached)
331 william 172 {
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 william 170 Debugger.Break();
340     }
341     }
342     else
343     {
344 william 172 xmltv_entry = query.FirstOrDefault();
345 william 170 }
346    
347 william 168 if (xmltv_entry == null)
348 william 163 {
349 william 170 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 william 169 source_invalid.Add(program);
351 william 174 // remove database entry
352     if (!RemoveProgramDatabaseEntry(program))
353     {
354     gLog.Error.WriteLine("Failed to remove entry with oid: '{0}'", program.oid);
355     }
356 william 163 }
357 william 164 else
358     {
359 william 172 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 william 168 var updated_program = program;
361 william 174 bool needsupdate = false;
362 william 173 if (xmltv_entry.Title != program.name)
363 william 172 {
364     gLog.Warn.WriteLine(" Updating:");
365 william 173 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 william 174 needsupdate = true;
369 william 172 }
370 william 173 if (xmltv_entry.SubTitle != program.sub_title)
371 william 172 {
372     gLog.Warn.WriteLine(" Updating:");
373 william 173 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 william 174 needsupdate = true;
377 william 172 }
378 william 173 if (xmltv_entry.Description != program.description)
379 william 172 {
380     gLog.Warn.WriteLine(" Updating:");
381 william 173 gLog.Warn.WriteLine(" Old Descption: {0}", program.description);
382     gLog.Warn.WriteLine(" New Descption: {0}", xmltv_entry.Description);
383 william 174 updated_program.description = xmltv_entry.Description;
384     needsupdate = true;
385 william 172 }
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 william 173 updated_program.start_time = DateTime.Parse(xmltv_entry.Start);
392 william 174 needsupdate = true;
393 william 172 }
394 william 173 if (DateTime.Parse(xmltv_entry.Stop) != program.end_time)
395 william 172 {
396     gLog.Warn.WriteLine(" Updating:");
397 william 173 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 william 174 needsupdate = true;
401 william 172 }
402 william 168 source_valid.Add(updated_program);
403 william 174 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 william 164 }
412 william 169 index++;
413 william 163 }
414 william 166
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 william 169 source_invalid = source_invalid.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
427 william 167
428     gLog.Info.WriteLine("Total XMLTV Programs: 0x{0:x8}", xmltv_programs.Count);
429 william 168 gLog.Info.WriteLine("Updated: 0x{0:x8} GB-PVR Programs", source_valid.Count);
430 william 169 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 william 167
433 william 166 return source_valid;
434 william 163 }
435 william 156 #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 william 159 string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
455 william 156 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 william 157 con.Clone();
481 william 156 }
482     OnCreatedInstance(this, new EventArgs());
483     }
484     catch (Exception ex)
485     {
486     gLog.Error.WriteLine(ex.ToString());
487     }
488     }
489    
490 william 157
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 william 159 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 william 156 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 william 159
633 william 174
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("name", new_program.name));
703     cmd.Parameters.Add(new SQLiteParameter("sub_title", new_program.sub_title));
704     cmd.Parameters.Add(new SQLiteParameter("description", new_program.description));
705     cmd.Parameters.Add(new SQLiteParameter("start_time", new_program.start_time));
706     cmd.Parameters.Add(new SQLiteParameter("end_time", new_program.end_time));
707     cmd.Parameters.Add(new SQLiteParameter("channel_oid", new_program.channel_oid));
708     cmd.Parameters.Add(new SQLiteParameter("unique_identifier", new_program.unique_identifier));
709     cmd.Parameters.Add(new SQLiteParameter("rating", new_program.rating));
710     int rowsupdated = cmd.ExecuteNonQuery();
711     gLog.Info.WriteLine("Updated '{0}' rows", rowsupdated);
712     }
713     con.Clone();
714     result = true;
715     }
716     catch (SQLiteException ex)
717     {
718     gLog.Error.WriteLine(ex.ToString());
719     result = false;
720     }
721     }
722     }
723     catch (Exception ex)
724     {
725     gLog.Error.WriteLine(ex.ToString());
726     result = false;
727     }
728     return result;
729     }
730 william 156 }
731     }

  ViewVC Help
Powered by ViewVC 1.1.22