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

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.22