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

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 164 - (hide annotations) (download)
Fri Mar 15 16:57:50 2013 UTC (7 years, 3 months ago) by william
File size: 21481 byte(s)
+ when filtering GBPVR programs by date-range, generate a list of programs that were filtered-out

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 156 namespace GBPVRProgramDatabaseFixer
11     {
12 william 163 public interface ISQLLITE
13 william 156 {
14 william 163 List<SQLLITE.IPROGRAMME> Programs { get; }
15     List<SQLLITE.IRECORDING_SCHEDULE> Recordings { get; }
16     List<SQLLITE.ICHANNEL> Channels { get; }
17     IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs);
18 william 164 List<SQLLITE.IPROGRAMME> FilterProgramsByDateRange(List<SQLLITE.IPROGRAMME> programs, IDateTimeRange range, out List<SQLLITE.IPROGRAMME> removed_programs);
19 william 163
20     }
21     public class SQLLITE : ISQLLITE
22     {
23     public static ISQLLITE Create(string database, EventHandler<EventArgs> OnInstanceCreated)
24     {
25     return new SQLLITE(database, OnInstanceCreated);
26     }
27    
28 william 156 #region DATABASE DEFINITIONS
29 william 159
30     public interface ICHANNEL
31     {
32     Int64 oid { get; }
33     String name { get; }
34     String channelID { get; }
35     Int64 channel_number { get; }
36     String favourite_channel { get; }
37     String display_name { get; }
38     }
39     private class CHANNEL : ICHANNEL
40     {
41     public CHANNEL()
42     {
43     BaseDatabaseDefinition<CHANNEL>.CreateDefault(this);
44     }
45     //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
46    
47     public static void Create(ref CHANNEL instance, SQLiteDataReader r, int index)
48     {
49     BaseDatabaseDefinition<CHANNEL>.Create(ref instance, r, index);
50     }
51     #region ICHANNEL members
52     public Int64 oid { get; set; }
53     public String name { get; set; }
54     public String channelID { get; set; }
55     public Int64 channel_number { get; set; }
56     public String favourite_channel { get; set; }
57     public String display_name { get; set; }
58     #endregion
59     }
60 william 156 public interface IRECORDING_SCHEDULE
61     {
62     Int64 oid { get; }
63     Int64 programme_oid { get; }
64     Int64 capture_source_oid { get; }
65     Int16 status { get; }
66     String filename { get; }
67     Int64 recording_type { get; }
68     Int64 recording_group { get; }
69     DateTime manual_start_time { get; }
70     DateTime manual_end_time { get; }
71     Int64 manual_channel_oid { get; }
72     Int64 quality_level { get; }
73     Int64 pre_pad_minutes { get; }
74     Int64 post_pad_minutes { get;}
75     Int32 priority { get; }
76     String conversion_profile { get; }
77     }
78    
79     private static class BaseDatabaseDefinition<T>
80     {
81     public static void CreateDefault(T instance)
82     {
83     try
84     {
85     Type t = typeof(T);
86     var props = t.GetProperties();
87     foreach (var prop in props)
88     {
89     Type prop_type = prop.PropertyType;
90     object field_value = null;
91     try
92     {
93     if (prop_type == typeof(string))
94     {
95     field_value = string.Empty;
96     }
97     else
98     {
99     field_value = Activator.CreateInstance(prop_type);
100     }
101     }
102     catch (Exception ex)
103     {
104     throw ex;
105     }
106     prop.SetValue(instance, field_value, null);
107     }
108     }
109     catch (Exception ex)
110     {
111     throw ex;
112     }
113     }
114     public static void Create(ref T instance, SQLiteDataReader r, int index)
115     {
116     string field_name = r.GetName(index);
117     Type field_type = r.GetFieldType(index);
118     object field_value = r.GetValue(index);
119     //gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
120    
121     Type t = typeof(T);
122     var props = t.GetProperties();
123     foreach (var prop in props)
124     {
125     if (prop.Name.ToLower() == field_name.ToLower())
126     {
127     if (prop.PropertyType == field_type)
128     {
129     Type db_type = field_value.GetType();
130     try
131     {
132     if (db_type == typeof(System.DBNull))
133     {
134     prop.SetValue(instance, null, null);
135     }
136     else
137     {
138     prop.SetValue(instance, field_value, null);
139     }
140    
141     }
142     catch (Exception ex)
143     {
144     throw ex;
145     }
146     }
147     else
148     {
149     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);
150     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));
151     }
152     }
153     }
154    
155     }
156     }
157    
158     private class RECORDING_SCHEDULE : IRECORDING_SCHEDULE
159     {
160     public RECORDING_SCHEDULE()
161     {
162     BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this);
163     }
164     //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
165    
166     public static void Create(ref RECORDING_SCHEDULE instance, SQLiteDataReader r, int index)
167     {
168     BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(ref instance, r, index);
169     }
170    
171     #region IRECORDING_SCHEDULE members
172     public Int64 oid { get; set;}
173     public Int64 programme_oid { get; set;}
174     public Int64 capture_source_oid { get; set;}
175     public Int16 status { get; set; }
176     public String filename { get; set; }
177     public Int64 recording_type { get;set; }
178     public Int64 recording_group { get; set;}
179     public DateTime manual_start_time { get; set;}
180     public DateTime manual_end_time { get; set; }
181     public Int64 manual_channel_oid { get; set; }
182     public Int64 quality_level { get; set; }
183     public Int64 pre_pad_minutes { get; set; }
184     public Int64 post_pad_minutes { get; set; }
185     public Int32 priority { get; set; }
186     public String conversion_profile { get; set; }
187     #endregion
188     }
189    
190     public interface IPROGRAMME
191     {
192     Int64 oid { get; }
193     String name { get; }
194     String sub_title { get; }
195     String description { get; }
196     DateTime start_time { get; }
197     DateTime end_time { get; }
198     Int64 channel_oid { get; }
199     String unique_identifier { get; }
200     String rating { get; }
201     }
202     private class PROGRAMME : IPROGRAMME
203     {
204     public PROGRAMME()
205     {
206     BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this);
207     }
208     //public PROGRAMME(SQLiteDataReader r, int index) : base(r, index) { }
209     public static void Create(ref PROGRAMME instance, SQLiteDataReader r, int index)
210     {
211     BaseDatabaseDefinition<PROGRAMME>.Create(ref instance, r, index);
212     }
213     #region IPROGRAMME members
214     public Int64 oid { get; set; }
215     public String name { get; set; }
216     public String sub_title { get; set; }
217     public String description { get; set; }
218     public DateTime start_time { get; set; }
219     public DateTime end_time { get; set; }
220     public Int64 channel_oid { get; set; }
221     public String unique_identifier { get; set; }
222     public String rating { get; set; }
223     #endregion
224     }
225     #endregion
226     private static class TABLES
227     {
228     public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
229     public const string PROGRAMME = "PROGRAMME";
230 william 159 public const string CHANNEL = "CHANNEL";
231 william 156 }
232    
233     //public SQLLite() { }
234 william 163 protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
235 william 156 {
236     this.OnInstanceCreated = OnInstanceCreated;
237     //CreateConnection(database);
238     this.Database = database;
239 william 157 if (!CreateDatabaseBackup())
240     {
241     gLog.Error.WriteLine("Failed to backup database.");
242     return;
243     }
244 william 156 ConnectionTest();
245 william 159 ReadChannelData();
246 william 156 ReadRecodringScheduleData();
247     ReadProgrammeData();
248     OnCreatedInstance(this, new EventArgs());
249     }
250    
251     [NonSerialized]
252     private EventHandler<EventArgs> _OnInstanceCreated;
253     private EventHandler<EventArgs> OnInstanceCreated { get { return _OnInstanceCreated; } set { _OnInstanceCreated = value; } }
254    
255     private void OnCreatedInstance(object sender, EventArgs e)
256     {
257     if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
258     }
259    
260     private string Database;
261 william 163 #region ISQLLITE members
262 william 156 public List<IPROGRAMME> Programs { get; private set; }
263     public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
264 william 159 public List<ICHANNEL> Channels { get; private set; }
265 william 163
266     public IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs)
267     {
268     var list = new List<IPROGRAMME>(programs.ToArray());
269     DateTime first = new DateTime();
270     DateTime last = new DateTime();
271     first = list.OrderBy(s => s.start_time).ToList().First().start_time;
272     last = list.OrderBy(s => s.start_time).ToList().Last().start_time;
273     gLog.Info.WriteLine("\tFirst: {0} = ({1})", first.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), first.ToDateTimeString());
274     gLog.Info.WriteLine("\tLast: {0} = ({1})", last.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), last.ToDateTimeString());
275     var range = DateTimeRange.Create(first, last);
276     return range;
277     }
278    
279 william 164 public List<SQLLITE.IPROGRAMME> FilterProgramsByDateRange(List<SQLLITE.IPROGRAMME> programs, IDateTimeRange range, out List<SQLLITE.IPROGRAMME> removed_programs)
280 william 163 {
281 william 164 removed_programs = new List<IPROGRAMME>();
282 william 163 List<SQLLITE.IPROGRAMME> list = new List<IPROGRAMME>(programs.ToArray());
283     list = list.OrderBy(s => s.start_time).ToList();
284     List<SQLLITE.IPROGRAMME> list2 = new List<IPROGRAMME>();
285     if (range == null)
286     {
287     gLog.Warn.WriteLine("The DateTimeRange passed in is null...returning the original program list");
288     return list;
289     }
290     //gLog.Warn.WriteLine("FilterProgramsByDateRange has not been implemented");
291    
292     foreach (var program in list)
293     {
294 william 164 if (program.start_time >= range.Start &&
295 william 163 program.start_time <= range.End)
296     {
297     list2.Add(program);
298     }
299 william 164 else
300     {
301     removed_programs.Add(program);
302     }
303 william 163 }
304     list2 = list2.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
305 william 164 removed_programs = removed_programs.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
306 william 163 return list2;
307     }
308 william 156 #endregion
309    
310    
311     private string CreateConnectionString()
312     {
313     string connection_string = string.Format("Data Source={0}", this.Database);
314     return connection_string;
315     }
316    
317     private SQLiteConnection CreateConnection() { SQLiteConnection connection = new SQLiteConnection(CreateConnectionString()); return connection; }
318    
319     [Conditional("SQLLITE_CONNECTION_TEST")]
320     private void ConnectionTest()
321     {
322     try
323     {
324     using (SQLiteConnection con = CreateConnection())
325     {
326     con.Open();
327 william 159 string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
328 william 156 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
329     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
330     {
331     using (SQLiteDataReader r = cmd.ExecuteReader())
332     {
333     if (!r.HasRows)
334     {
335     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
336     }
337     else
338     {
339     while (r.Read())
340     {
341     for (int i = 0; i < r.FieldCount; i++)
342     {
343     string field_name = r.GetName(i);
344     Type field_type = r.GetFieldType(i);
345     object field_value = r.GetValue(i);
346     gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
347     }
348     break;
349     }
350     }
351     }
352     }
353 william 157 con.Clone();
354 william 156 }
355     OnCreatedInstance(this, new EventArgs());
356     }
357     catch (Exception ex)
358     {
359     gLog.Error.WriteLine(ex.ToString());
360     }
361     }
362    
363 william 157
364     private bool CreateDatabaseBackup()
365     {
366     try
367     {
368     string backup_file = string.Format("{0}.{1}", this.Database, DateTime.Now.ToString("yyyyMMddHHmmss"));
369     gLog.Info.WriteLine("Creating Database backup...");
370     gLog.Info.WriteLine("\tSource: {0}", this.Database);
371     gLog.Info.WriteLine("\tDestination: {0}", backup_file);
372    
373     System.IO.File.Copy(this.Database, backup_file);
374     return true;
375     }
376     catch (Exception ex)
377     {
378     gLog.Error.WriteLine(ex.ToString());
379     return false;
380     }
381     }
382 william 159 private void ReadChannelData()
383     {
384     try
385     {
386     List<ICHANNEL> channels = new List<ICHANNEL>();
387     using (SQLiteConnection con = CreateConnection())
388     {
389     con.Open();
390     string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
391     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
392     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
393     {
394     using (SQLiteDataReader r = cmd.ExecuteReader())
395     {
396     if (!r.HasRows)
397     {
398     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
399     }
400     else
401     {
402     while (r.Read())
403     {
404     CHANNEL channel = new CHANNEL();
405     for (int i = 0; i < r.FieldCount; i++)
406     {
407     CHANNEL.Create(ref channel, r, i);
408     }
409     channels.Add(channel);
410     }
411     }
412     }
413     }
414     con.Clone();
415     }
416     this.Channels = channels;
417     }
418     catch (Exception ex)
419     {
420     gLog.Error.WriteLine(ex.ToString());
421     }
422     }
423 william 156 private void ReadProgrammeData()
424     {
425     try
426     {
427     List<IPROGRAMME> programs = new List<IPROGRAMME>();
428     using (SQLiteConnection con = CreateConnection())
429     {
430     con.Open();
431     string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
432     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
433     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
434     {
435     using (SQLiteDataReader r = cmd.ExecuteReader())
436     {
437     if (!r.HasRows)
438     {
439     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
440     }
441     else
442     {
443     while (r.Read())
444     {
445     PROGRAMME program = new PROGRAMME();
446     for (int i = 0; i < r.FieldCount; i++)
447     {
448     PROGRAMME.Create(ref program, r, i);
449     }
450     programs.Add(program);
451     }
452     }
453     }
454     }
455     con.Clone();
456     }
457     this.Programs = programs;
458     }
459     catch (Exception ex)
460     {
461     gLog.Error.WriteLine(ex.ToString());
462     }
463     }
464     private void ReadRecodringScheduleData()
465     {
466     try
467     {
468     List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
469     using (SQLiteConnection con = CreateConnection())
470     {
471     con.Open();
472     string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
473     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
474     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
475     {
476     using (SQLiteDataReader r = cmd.ExecuteReader())
477     {
478     if (!r.HasRows)
479     {
480     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
481     }
482     else
483     {
484     while (r.Read())
485     {
486     RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
487     for (int i = 0; i < r.FieldCount; i++)
488     {
489     RECORDING_SCHEDULE.Create(ref recording, r, i);
490     }
491     recordings.Add(recording);
492     }
493     }
494     }
495     }
496     con.Clone();
497     }
498     this.Recordings = recordings;
499     }
500     catch (Exception ex)
501     {
502     gLog.Error.WriteLine(ex.ToString());
503     }
504     }
505 william 159
506 william 156 }
507     }

  ViewVC Help
Powered by ViewVC 1.1.22