trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

//#define SQLLITE_CONNECTION_TEST
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Diagnostics;
using Enterprise.Logging;

namespace GBPVRProgramDatabaseFixer
{
    internal class SQLLITE
    {
        #region DATABASE DEFINITIONS
        public interface IRECORDING_SCHEDULE
        {
            Int64 oid { get; }
            Int64 programme_oid { get; }
            Int64 capture_source_oid { get; }
            Int16 status { get; }
            String filename { get; }
            Int64 recording_type { get; }
            Int64 recording_group { get; }
            DateTime manual_start_time { get; }
            DateTime manual_end_time { get; }
            Int64 manual_channel_oid { get; }
            Int64 quality_level { get; }
            Int64 pre_pad_minutes { get; }
            Int64 post_pad_minutes { get;}
            Int32 priority { get; }
            String conversion_profile { get; }
        }

        private static class BaseDatabaseDefinition<T>
        {
            public static void CreateDefault(T instance)
            {
                try
                {
                    Type t = typeof(T);
                    var props = t.GetProperties();
                    foreach (var prop in props)
                    {
                        Type prop_type = prop.PropertyType;
                        object field_value = null;
                        try
                        {
                            if (prop_type == typeof(string))
                            {
                                field_value = string.Empty;
                            }
                            else
                            {
                                field_value = Activator.CreateInstance(prop_type);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                        prop.SetValue(instance, field_value, null);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            public static void Create(ref T instance, SQLiteDataReader r, int index)
            {                
                string field_name = r.GetName(index);
                Type field_type = r.GetFieldType(index);
                object field_value = r.GetValue(index);
                //gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());

                Type t = typeof(T);
                var props = t.GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name.ToLower() == field_name.ToLower())
                    {
                        if (prop.PropertyType == field_type)
                        {
                            Type db_type = field_value.GetType();
                            try
                            {
                                if (db_type == typeof(System.DBNull))
                                {
                                    prop.SetValue(instance, null, null);
                                }
                                else
                                {
                                    prop.SetValue(instance, field_value, null);
                                }
                                
                            }
                            catch (Exception ex) 
                            {
                                throw ex;
                            }
                        }
                        else
                        {
                            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);
                            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));
                        }
                    }
                }

            }
        }

        private class RECORDING_SCHEDULE : IRECORDING_SCHEDULE
        {
            public RECORDING_SCHEDULE() 
            {
                BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this); 
            }
            //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }

            public static void Create(ref RECORDING_SCHEDULE instance, SQLiteDataReader r, int index)
            {
                BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(ref instance, r, index); 
            }

            #region IRECORDING_SCHEDULE members
            public Int64 oid { get; set;}
            public Int64 programme_oid { get; set;}
            public Int64 capture_source_oid { get; set;}
            public Int16 status { get; set; }
            public String filename { get; set; }
            public Int64 recording_type { get;set; }
            public Int64 recording_group { get; set;}
            public DateTime manual_start_time { get; set;}
            public DateTime manual_end_time { get; set; }
            public Int64 manual_channel_oid { get; set; }
            public Int64 quality_level { get; set; }
            public Int64 pre_pad_minutes { get; set; }
            public Int64 post_pad_minutes { get; set; }
            public Int32 priority { get; set; }
            public String conversion_profile { get; set; }
            #endregion
        }

        public interface IPROGRAMME
        {
            Int64 oid { get; }
            String name { get; }
            String sub_title { get; }
            String description { get; }
            DateTime start_time { get; }
            DateTime end_time { get; }
            Int64 channel_oid { get; }
            String unique_identifier { get; }
            String rating { get; }
        }
        private class PROGRAMME : IPROGRAMME
        {
            public PROGRAMME() 
            {
                BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this); 
            }
            //public PROGRAMME(SQLiteDataReader r, int index) : base(r, index) { }
            public static void Create(ref PROGRAMME instance, SQLiteDataReader r, int index)
            {
                BaseDatabaseDefinition<PROGRAMME>.Create(ref instance, r, index);
            }
            #region IPROGRAMME members
            public Int64 oid { get; set; }
            public String name { get; set; }
            public String sub_title { get; set; }
            public String description { get; set; }
            public DateTime start_time { get; set; }
            public DateTime end_time { get; set; }
            public Int64 channel_oid { get; set; }
            public String unique_identifier { get; set; }
            public String rating { get; set; }
            #endregion
        }
        #endregion
        private static class TABLES
        {
            public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
            public const string PROGRAMME = "PROGRAMME";
        }

        //public SQLLite() { }
        public SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated) 
        {
            this.OnInstanceCreated = OnInstanceCreated;
            //CreateConnection(database);
            this.Database = database;
            if (!CreateDatabaseBackup())
            {
                gLog.Error.WriteLine("Failed to backup database.");
                return;
            }
            ConnectionTest();
            ReadRecodringScheduleData();
            ReadProgrammeData();
            OnCreatedInstance(this, new EventArgs());
        }

        [NonSerialized]
        private EventHandler<EventArgs> _OnInstanceCreated;
        private EventHandler<EventArgs> OnInstanceCreated { get { return _OnInstanceCreated; } set { _OnInstanceCreated = value; } }
        
        private void OnCreatedInstance(object sender, EventArgs e)
        {
            if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
        }

        #region SQLLite 
        private string Database;
        public List<IPROGRAMME> Programs { get; private set; }
        public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
        #endregion


        private string CreateConnectionString()
        {
            string connection_string = string.Format("Data Source={0}", this.Database);
            return connection_string;
        }

        private SQLiteConnection CreateConnection() { SQLiteConnection connection = new SQLiteConnection(CreateConnectionString()); return connection; }

        [Conditional("SQLLITE_CONNECTION_TEST")]
        private void ConnectionTest()
        {
            try
            {
                using (SQLiteConnection con = CreateConnection())
                {
                    con.Open();
                    string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
                    gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                    using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                    {
                        using (SQLiteDataReader r = cmd.ExecuteReader())
                        {
                            if (!r.HasRows)
                            {
                                gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
                            }
                            else
                            {
                                while (r.Read())
                                {
                                    for (int i = 0; i < r.FieldCount; i++)
                                    {
                                        string field_name = r.GetName(i);
                                        Type field_type = r.GetFieldType(i);
                                        object field_value = r.GetValue(i);
                                        gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
                                    }
                                    break;
                                }
                            }
                        }
                    }
                    con.Clone();
                }
                OnCreatedInstance(this, new EventArgs());
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
            }
        }


        private bool CreateDatabaseBackup()
        {
            try
            {
                string backup_file = string.Format("{0}.{1}", this.Database, DateTime.Now.ToString("yyyyMMddHHmmss"));
                gLog.Info.WriteLine("Creating Database backup...");
                gLog.Info.WriteLine("\tSource: {0}", this.Database);
                gLog.Info.WriteLine("\tDestination: {0}", backup_file);

                System.IO.File.Copy(this.Database, backup_file);
                return true;
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
                return false;
            }
        }

        private void ReadProgrammeData()
        {
            try
            {
                List<IPROGRAMME> programs = new List<IPROGRAMME>();
                using (SQLiteConnection con = CreateConnection())
                {
                    con.Open();
                    string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
                    gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                    using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                    {
                        using (SQLiteDataReader r = cmd.ExecuteReader())
                        {
                            if (!r.HasRows)
                            {
                                gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
                            }
                            else
                            {
                                while (r.Read())
                                {
                                    PROGRAMME program = new PROGRAMME();
                                    for (int i = 0; i < r.FieldCount; i++)
                                    {
                                        PROGRAMME.Create(ref program, r, i);
                                    }
                                    programs.Add(program);
                                }
                            }
                        }
                    }
                    con.Clone();
                }
                this.Programs = programs;
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
            }
        }
        private void ReadRecodringScheduleData()
        {
            try
            {
                List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
                using (SQLiteConnection con = CreateConnection())
                {
                    con.Open();
                    string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
                    gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                    using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                    {
                        using (SQLiteDataReader r = cmd.ExecuteReader())
                        {
                            if (!r.HasRows)
                            {
                                gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
                            }
                            else
                            {
                                while (r.Read())
                                {
                                    RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
                                    for (int i = 0; i < r.FieldCount; i++)
                                    {
                                        RECORDING_SCHEDULE.Create(ref recording, r, i);
                                    }

                                    recordings.Add(recording);
                                }
                            }
                        }
                    }
                    con.Clone();
                }
                this.Recordings = recordings;
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
            }
        }
    }
}
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			}