Attributes.cs

// 
// Attributes.cs
// 
// Database-agnostic portions of the code are copyright (c) 2009-2010 Krueger Systems, Inc.
// Oracle support is copyright (c) 2010 Bojan Rajkovic
// 
// 
// Permission is hereby granted, free of charge, to any person obtaining a copy
// of this software and associated documentation files (the "Software"), to deal
// in the Software without restriction, including without limitation the rights
// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
// copies of the Software, and to permit persons to whom the Software is
// furnished to do so, subject to the following conditions:
// 
// The above copyright notice and this permission notice shall be included in
// all copies or substantial portions of the Software.
// 
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
// THE SOFTWARE.

using System;

namespace ThousandMonkeys.Oracle.Attributes
{
	public class PrimaryKeyAttribute : Attribute
	{
	}

	public class AutoIncrementAttribute : Attribute
	{
	}

	public class IndexedAttribute : Attribute
	{
	}

	public class IgnoreAttribute : Attribute
	{
	}

	public class NullableAttribute : Attribute
	{
	}

	public class ColumnNameAttribute : Attribute
	{
		/// <summary>
		/// Gets or sets the value.
		/// </summary>
		/// <value>
		/// The value.
		/// </value>
		public string Value { get; private set; }

		/// <summary>
		/// Initializes a new instance of the <see cref="APA.Oracle.Attributes.ColumnNameAttribute"/> class.
		/// </summary>
		/// <param name='name'>
		/// Name.
		/// </param>
		public ColumnNameAttribute (string name)
		{
			Value = name;
		}
	}

	public class MaxLengthAttribute : Attribute
	{
		/// <summary>
		/// Gets or sets the value.
		/// </summary>
		/// <value>
		/// The value.
		/// </value>
		public int Value { get; private set; }

		/// <summary>
		/// Initializes a new instance of the <see cref="MaxLengthAttribute"/> class.
		/// </summary>
		/// <param name='length'>
		/// Length.
		/// </param>
		public MaxLengthAttribute (int length)
		{
			Value = length;
		}
	}
}

SimpleOracleConnection.cs

//
// Database-agnostic portions of the code are copyright (c) 2009-2010 Krueger Systems, Inc.
// Oracle port is copyright (C) 2010 Air Power Analytics, Inc.
// 
// Permission is hereby granted, free of charge, to any person obtaining a copy
// of this software and associated documentation files (the "Software"), to deal
// in the Software without restriction, including without limitation the rights
// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
// copies of the Software, and to permit persons to whom the Software is
// furnished to do so, subject to the following conditions:
// 
// The above copyright notice and this permission notice shall be included in
// all copies or substantial portions of the Software.
// 
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
// THE SOFTWARE.
//

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.OracleClient;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.InteropServices;
using ParameterPair = System.Collections.Generic.KeyValuePair<string, object>;
using APA.Oracle.Attributes;
using System.Data;
using System.Text;

namespace APA.Oracle
{
	/// <summary>
	/// Represents an open connection to an Oracle database.
	/// </summary>
	public class SimpleOracleConnection : IDisposable
	{
		private Dictionary<string, TableMapping> _mappings = null;
		private Dictionary<string, TableMapping> _tables = null;

		private System.Diagnostics.Stopwatch _sw;
		private long _elapsedMilliseconds = 0;

		public string ConnectionString { get; private set; }

		public bool TimeExecution { get; set; }
		public bool Trace { get; set; }

		public OracleConnection Connection { get; private set; }

		public TextWriter TraceWriter { get; private set; }

		public SimpleOracleConnection (string connectionString) : this(connectionString, Console.Out)
		{
		}

		public SimpleOracleConnection (string connectionString, TextWriter traceWriter)
		{
			ConnectionString = connectionString;
			Connection = new OracleConnection (ConnectionString);
			Connection.Open ();
			TraceWriter = traceWriter;
		}

		public bool IsInTransaction { get; private set; }

		public OracleTransaction Transaction { get; private set; }

		public IEnumerable<TableMapping> TableMappings {
			get {
				if (_tables == null) {
					return Enumerable.Empty<TableMapping> ();
				} else {
					return _tables.Values;
				}
			}
		}

		public TableMapping GetMapping (Type type)
		{
			if (_mappings == null) {
				_mappings = new Dictionary<string, TableMapping> ();
			}
			TableMapping map;
			if (!_mappings.TryGetValue (type.FullName, out map)) {
				map = new TableMapping (type);
				_mappings[type.FullName] = map;
			}
			return map;
		}

		public int CreateTable<T> ()
		{
			var ty = typeof(T);
			
			if (_tables == null) {
				_tables = new Dictionary<string, TableMapping> ();
			}
			TableMapping map;
			if (!_tables.TryGetValue (ty.FullName, out map)) {
				map = GetMapping (ty);
				_tables.Add (ty.FullName, map);
			}
			
			if (TableExists (map.TableName))
				return 0;
			
			var query = string.Format ("create table {0} ({1}", map.TableName, "\n");
			
			var decls = map.Columns.Select (p => Orm.SqlDecl (p));
			var decl = string.Join (",\n", decls.ToArray ());
			query += decl;
			query += ")";
			
			Console.WriteLine ("Create table query: {0}", query);
			
			var count = Execute (query);
			
			foreach (var p in map.Columns.Where (x => x.IsIndexed)) {
				var indexName = map.TableName + "_" + p.Name;
				if (IndexExists (indexName))
					count += 0;
				else {
					var q = string.Format ("create index {0} on {1}({2})", indexName, map.TableName, p.Name);
					count += Execute (q);
				}
			}
			
			foreach (var p in map.Columns.Where (x => x.IsPK)) {
				var pkName = map.TableName + "_" + p.Name + "_PK";
				if (PrimaryKeyExists (map.TableName, pkName))
					count += 0;
				else {
					var q = string.Format ("alter table {0} add constraint {1} primary key ({2})", map.TableName, pkName, p.Name);
					count += Execute (q);
				}
			}
			
			foreach (var p in map.Columns.Where (x => x.IsAutoInc)) {
				var sequenceName = map.TableName + "_" + p.Name + "_SEQ";
				if (SequenceExists (sequenceName)) {
					count += 0;
				} else {
					var q = string.Format ("create sequence {0} start with 1 increment by 1 nomaxvalue", sequenceName);
					count += Execute (q);
				}
				var triggerName = map.TableName + "_" + p.Name + "_AUTOINC_TRIGGER";
				if (TriggerExists (triggerName)) {
					count += 0;
				} else {
					var q = string.Format ("create trigger {0} before insert on {1} for each row begin select {2}.nextval into :new.{3} from dual end", triggerName, map.TableName, sequenceName, p.Name);
					count += Execute (q);
				}
			}
			
			return count;
		}

		private bool PrimaryKeyExists (string tableName, string pkName)
		{
			var query = "select count(1) from all_constraints cons, all_cons_columns cols where cols.table_name = :pTableName " 
				+ "and cons.constraint_type = 'P' and cons.constraint_name = cols.constraint_name and cons.owner = cols.owner " 
					+ "and cons.constraint_name = :pPkName";
			var count = ExecuteScalar<float> (query, 
				new ParameterPair ("pTableName", tableName.ToUpperInvariant ()), 
				new ParameterPair ("pPkName", pkName.ToUpperInvariant ())
			);
			
			return count > 0;
		}

		private bool SequenceExists (string sequenceName)
		{
			var query = "select count(1) from user_sequences where sequence_name = :pSeqName";
			var count = ExecuteScalar<float> (query, new ParameterPair ("pSeqName", sequenceName));
			
			return count > 0;
		}

		private bool TriggerExists (string triggerName)
		{
			var query = "select count(1) from user_triggers where trigger_name = :pTrigName";
			var count = ExecuteScalar<float> (query, new ParameterPair ("pTrigName", triggerName));
			
			return count > 0;
		}

		private bool IndexExists (string indexName)
		{
			var query = "select count(1) from user_objects where object_type = 'INDEX' and object_name = :pIndexName";
			var count = ExecuteScalar<float> (query, new ParameterPair ("pIndexName", indexName.ToUpperInvariant ()));
			
			return count > 0;
		}

		private bool TableExists (string tableName)
		{
			var query = "select count(1) from user_objects where object_type = 'TABLE' and object_name = :pTableName";
			var count = ExecuteScalar<float> (query, new ParameterPair ("pTableName", tableName.ToUpperInvariant ()));
			
			return count > 0;
		}

		public OracleCommand CreateCommand (string cmdText, params KeyValuePair<string, object>[] ps)
		{
			var cmd = Connection.CreateCommand ();
			cmd.CommandText = cmdText;
			foreach (var parameter in ps) {
				if (parameter.Key == "pInternalIDReturnParameter") {
					var param = new OracleParameter { OracleType = OracleType.Float, Direction = ParameterDirection.Output, ParameterName = "pInternalIDReturnParameter" };
					cmd.Parameters.Add (param);
				}
				cmd.Parameters.Add (parameter.Key, parameter.Value);
			}
			return cmd;
		}

		public void TraceQuery (string query, params ParameterPair[] parameters)
		{
			TraceWriter.WriteLine ("----BEGIN QUERY----");
			TraceWriter.WriteLine ("\tQuery Text: {0}", query);
			TraceWriter.WriteLine ("\tParameters:");
			foreach (var parameter in parameters) {
				TraceWriter.WriteLine ("\t\t{0} = {1}", parameter.Key, parameter.Value);
			}
			TraceWriter.WriteLine ("----END QUERY----");
		}

		public T ExecuteScalar<T> (string query, params ParameterPair[] parameters)
		{
			if (Trace) {
				TraceQuery (query, parameters);
			}
			return (T)Convert.ChangeType (CreateCommand (query, parameters).ExecuteScalar (), typeof(T));
		}

		public int Execute (string query, params ParameterPair[] args)
		{
			OracleCommand dummy;
			return Execute (query, out dummy, args);
		}

		private int Execute (string query, out OracleCommand lastCommand, params ParameterPair[] args)
		{
			if (Trace) {
				TraceQuery (query, args);
			}
			
			var cmd = CreateCommand (query, args);
			
			if (TimeExecution) {
				if (_sw == null) {
					_sw = new System.Diagnostics.Stopwatch ();
				}
				_sw.Reset ();
				_sw.Start ();
			}
			
			int r = cmd.ExecuteNonQuery ();
			
			if (TimeExecution) {
				_sw.Stop ();
				_elapsedMilliseconds += _sw.ElapsedMilliseconds;
				Console.WriteLine ("Finished in {0} ms ({1:0.0} s total)", _sw.ElapsedMilliseconds, _elapsedMilliseconds / 1000.0);
			}
			
			lastCommand = cmd;
			return r;
		}

		public List<T> Query<T> (string query, params ParameterPair[] args) where T : new()
		{
			if (Trace)
				TraceQuery (query, args);
			var cmd = CreateCommand (query, args);
			return ExecuteQuery<T> (cmd);
		}

		public List<object> Query (TableMapping map, string query, params ParameterPair[] args)
		{
			if (Trace)
				TraceQuery (query, args);
			var cmd = CreateCommand (query, args);
			return ExecuteQuery<object> (cmd, map);
		}

		public T Get<T> (object pk) where T : new()
		{
			var map = GetMapping (typeof(T));
			string query = string.Format ("select * from {0} where {1} = :pPKValue", map.TableName, map.PK.Name);
			ParameterPair[] param = new[] { new ParameterPair ("pPKValue", pk) };
			if (Trace)
				TraceQuery (query, param);
			return Query<T> (query, param).First ();
		}

		public void BeginTransaction ()
		{
			if (!IsInTransaction) {
				Transaction = Connection.BeginTransaction ();
				IsInTransaction = true;
			}
		}

		public void Rollback ()
		{
			if (IsInTransaction) {
				Transaction.Rollback ();
				IsInTransaction = false;
			}
		}

		public void Commit ()
		{
			if (IsInTransaction) {
				Transaction.Commit ();
				IsInTransaction = false;
			}
		}

		public void RunInTransaction (Action action)
		{
			if (IsInTransaction) {
				throw new InvalidOperationException ("The connection must not already be in a transaction when RunInTransaction is called");
			}
			try {
				BeginTransaction ();
				action ();
				Commit ();
			} catch (Exception) {
				Rollback ();
				throw;
			}
		}

		public int InsertAll (IEnumerable objects)
		{
			BeginTransaction ();
			var c = 0;
			foreach (var r in objects) {
				c += Insert (r);
			}
			Commit ();
			return c;
		}

		public int Insert (object obj)
		{
			if (obj == null) {
				return 0;
			}
			return Insert (obj, "", obj.GetType ());
		}

		public int Insert (object obj, Type objType)
		{
			return Insert (obj, "", objType);
		}

		public int Insert (object obj, string extra)
		{
			if (obj == null) {
				return 0;
			}
			return Insert (obj, extra, obj.GetType ());
		}

		public int Insert (object obj, string extra, Type objType)
		{
			if (obj == null || objType == null) {
				return 0;
			}
			
			var map = GetMapping (objType);
			
			var cols = map.InsertColumns;
			var vals = new Dictionary<string, object> ();
			
			for (var i = 0; i < cols.Length; i++) {
				vals.Add ("p" + cols[i].Name, cols[i].GetValue (obj));
			}
			
			vals.Add ("pInternalIDReturnParameter", 0.0f);
			
			OracleCommand cmd;
			var count = Execute (map.InsertSql (extra), out cmd, vals.ToArray ());
			var id = Convert.ToInt64 (cmd.Parameters["pInternalIDReturnParameter"].Value);
			map.SetAutoIncPK (obj, id);
			
			return count;
		}

		public int Update (object obj)
		{
			if (obj == null) {
				return 0;
			}
			return Update (obj, obj.GetType ());
		}

		public int Update (object obj, Type objType)
		{
			if (obj == null || objType == null) {
				return 0;
			}
			
			var map = GetMapping (objType);
			
			var pk = map.PK;
			
			if (pk == null) {
				throw new NotSupportedException (string.Format ("Cannot update {0}: it has no PK", map.TableName));
			}
			
			var cols = from p in map.Columns
				where p != pk
				select p;
			var parameters = cols.ToDictionary (c => c.Name + "value", c => c.GetValue (obj));
			parameters.Add ("pPKValue", pk.GetValue (obj));
			var q = string.Format ("update {0} set {1} where {2} = :pPKValue", map.TableName, string.Join (",", (from c in cols
				select c.Name + " = :p" + c.Name).ToArray ()), pk.Name);
			return Execute (q, parameters.ToArray ());
		}

		public int Delete<T> (T obj)
		{
			var map = GetMapping (obj.GetType ());
			var pk = map.PK;
			if (pk == null) {
				throw new NotSupportedException (string.Format ("Cannot delete {0}: it has no PK", map.TableName));
			}
			var q = string.Format ("delete from {0} where {1} = :pPKValue", map.TableName, pk.Name);
			return Execute (q, new ParameterPair (pk.Name, pk.GetValue (obj)));
		}

		public void Dispose ()
		{
			Close ();
		}

		public void Close ()
		{
			if (Connection != null) {
				if (Connection.State == ConnectionState.Open) {
					if (IsInTransaction) {
						Transaction.Commit ();
					}
					Connection.Dispose ();
					Connection = null;
				}
			}
		}

		private List<T> ExecuteQuery<T> (OracleCommand comm) where T : new()
		{
			return ExecuteQuery<T> (comm, GetMapping (typeof(T)));
		}

		private List<T> ExecuteQuery<T> (OracleCommand comm, TableMapping map) where T : new()
		{
			if (Trace) {
				TraceQuery (comm.CommandText, comm.Parameters.OfType<OracleParameter> ().ToDictionary (f => f.ParameterName, f => f.Value).ToArray ());
			}
			
			var reader = comm.ExecuteReader ();
			var r = new List<T> ();
			
			if (!reader.HasRows)
				return r;
			
			var cols = new TableMapping.Column[reader.VisibleFieldCount];
			
			for (int i = 0; i < cols.Length; i++) {
				var name = reader.GetName (i);
				cols[i] = map.FindColumn (name);
			}
			
			while (reader.Read ()) {
				var obj = Activator.CreateInstance (map.MappedType);
				
				for (int i = 0; i < cols.Length; i++) {
					if (cols[i] == null)
						continue;
					cols[i].SetValue (obj, reader[i]);
				}
				r.Add ((T)obj);
			}
			
			return r;
		}
	}

	public class TableMapping
	{
		public Type MappedType { get; private set; }
		public string TableName { get; private set; }

		public Column[] Columns { get; private set; }
		public Column PK { get; private set; }
		Column _autoPk = null;
		Column[] _insertColumns = null;

		string _insertSql = null;

		public TableMapping (Type type)
		{
			MappedType = type;
			TableName = MappedType.Name;
			var props = MappedType.GetProperties (BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty);
			var cols = new List<Column> ();
			foreach (var p in props) {
				var ignore = p.GetCustomAttributes (typeof(IgnoreAttribute), true).Length > 0;
				if (p.CanWrite && !ignore) {
					cols.Add (new PropColumn (p));
				}
			}
			Columns = cols.ToArray ();
			foreach (var c in Columns) {
				if (c.IsAutoInc && c.IsPK) {
					_autoPk = c;
				}
				if (c.IsPK) {
					PK = c;
				}
			}
		}

		public void SetAutoIncPK (object obj, long id)
		{
			if (_autoPk != null) {
				_autoPk.SetValue (obj, Convert.ChangeType (id, _autoPk.ColumnType));
			}
		}

		public Column[] InsertColumns {
			get {
				_insertColumns = _insertColumns ?? Columns.Where (c => !c.IsAutoInc).ToArray ();
				return _insertColumns;
			}
		}

		public Column FindColumn (string name)
		{
			return Columns.Where (c => c.Name == name).FirstOrDefault ();
		}

		public string InsertSql (string extra)
		{
			if (_insertSql == null) {
				var cols = InsertColumns;
				_insertSql = string.Format ("insert {3} into {0}({1}) values ({2}) returning {3} into :pID", TableName, string.Join (",", (from c in cols
					select c.Name).ToArray ()), string.Join (",", (from c in cols
					select ":p" + c.Name).ToArray ()), extra, _autoPk.Name);
			}
			return _insertSql;
		}

		public abstract class Column
		{
			public string Name { get; protected set; }
			public Type ColumnType { get; protected set; }
			public bool IsAutoInc { get; protected set; }
			public bool IsPK { get; protected set; }
			public bool IsIndexed { get; protected set; }
			public bool IsNullable { get; protected set; }
			public int MaxStringLength { get; protected set; }
			public abstract void SetValue (object obj, object val);
			public abstract object GetValue (object obj);
		}

		public class PropColumn : Column
		{
			PropertyInfo _prop;

			public PropColumn (PropertyInfo prop)
			{
				_prop = prop;
				var nameAttributes = prop.GetCustomAttributes (typeof(ColumnNameAttribute), true);
				Name = nameAttributes.Length > 0 ? ((ColumnNameAttribute)nameAttributes.Last ()).Value : prop.Name;
				ColumnType = prop.PropertyType;
				IsAutoInc = Orm.IsAutoInc (prop);
				IsPK = Orm.IsPK (prop);
				IsIndexed = Orm.IsIndexed (prop);
				IsNullable = Orm.IsNullable (prop);
				MaxStringLength = Orm.MaxStringLength (prop);
			}

			public override void SetValue (object obj, object val)
			{
				_prop.SetValue (obj, val, null);
			}

			public override object GetValue (object obj)
			{
				return _prop.GetValue (obj, null);
			}
		}
	}

	public static class Orm
	{
		public const int DefaultMaxStringLength = 140;

		public static string SqlDecl (TableMapping.Column p)
		{
			return string.Format ("{0} {1} {2}", p.Name, SqlType (p), p.IsNullable ? "null" : "not null");
		}

		public static string SqlType (TableMapping.Column p)
		{
			var clrType = p.ColumnType;
			if (clrType == typeof(Boolean) || clrType == typeof(Byte) || clrType == typeof(UInt16) || clrType == typeof(SByte) || clrType == typeof(Int16) || clrType == typeof(Int32)) {
				return "integer";
			} else if (clrType == typeof(UInt32) || clrType == typeof(Int64)) {
				return "integer";
			} else if (clrType == typeof(Single) || clrType == typeof(Double) || clrType == typeof(Decimal)) {
				return "double";
			} else if (clrType == typeof(String)) {
				int len = p.MaxStringLength;
				return "varchar2(" + len + ")";
			} else if (clrType == typeof(DateTime)) {
				return "timestamp";
			} else if (clrType.IsEnum) {
				return "integer";
			} else {
				throw new NotSupportedException ("Don't know about " + clrType);
			}
		}

		public static bool IsPK (MemberInfo p)
		{
			return p.GetCustomAttributes (typeof(PrimaryKeyAttribute), true).Length > 0;
		}

		public static bool IsAutoInc (MemberInfo p)
		{
			return p.GetCustomAttributes (typeof(AutoIncrementAttribute), true).Length > 0;
		}

		public static bool IsIndexed (MemberInfo p)
		{
			return p.GetCustomAttributes (typeof(IndexedAttribute), true).Length > 0;
		}

		public static bool IsNullable (MemberInfo p)
		{
			return p.GetCustomAttributes (typeof(NullableAttribute), true).Length > 0;
		}

		public static int MaxStringLength (PropertyInfo p)
		{
			var attrs = p.GetCustomAttributes (typeof(MaxLengthAttribute), true);
			if (attrs.Length > 0) {
				return ((MaxLengthAttribute)attrs[0]).Value;
			} else {
				return DefaultMaxStringLength;
			}
		}
	}
}