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

Contents of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 157 - (show annotations) (download)
Thu Mar 14 19:36:01 2013 UTC (7 years, 4 months ago) by william
File size: 15521 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
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 if (!CreateDatabaseBackup())
194 {
195 gLog.Error.WriteLine("Failed to backup database.");
196 return;
197 }
198 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 con.Clone();
263 }
264 OnCreatedInstance(this, new EventArgs());
265 }
266 catch (Exception ex)
267 {
268 gLog.Error.WriteLine(ex.ToString());
269 }
270 }
271
272
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 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