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

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.22