Commits

Anonymous committed c3da39d Merge

Merge latest changes from parent fork

Comments (0)

Files changed (4)

BsonSql.Driver/BsonSql.Driver.csproj

     <WarningLevel>4</WarningLevel>
   </PropertyGroup>
   <ItemGroup>
+    <Reference Include="log4net, Version=1.2.10.0, Culture=neutral, PublicKeyToken=1b44e1d426115821, processorArchitecture=MSIL">
+      <SpecificVersion>False</SpecificVersion>
+      <HintPath>..\Libraries\log4net.dll</HintPath>
+    </Reference>
     <Reference Include="MongoDB.Bson">
       <HintPath>..\Libraries\MongoDB.Bson.dll</HintPath>
     </Reference>

BsonSql.Driver/BsonSqlCollection.cs

 using System.Threading.Tasks;
 using System.Runtime.Caching;
 
+using log4net;
+using log4net.Core;
+
 
 
 namespace BsonSql.Driver
 {
 	public class BsonSqlCollection
 	{
+        private static ILog _logger = LogManager.GetLogger("BsonSql.Audit");
+        private string _logMask;
+
 		private IndexSqlCache _indexCache = null;
 		private BsonSqlServer _server;
 		private string _domainId;
 		private string _name;
+        
 		private DomainConfiguration _configuration;
+        
 
 		private object _sync = new object();
+        private bool _logging = false;
 
 		private static Query _findOneById = QueryBuilder.Where(WhereClauseBuilder.CreateBound("BsonDocumentID", Comparison.Equals)).Compile();
 
 			_name = name;
 			_server = server;
 			_configuration = configuration;
+            _logMask = _domainId + "." + _name;
+
+            if (_logger.IsInfoEnabled)
+                _logging = true;
 
 			server.Dispatcher.Add("configuration.changed", new BsonSqlEventHandler<ConfigurationEvent>(ConfigurationChanged));
 		}
             }
         }
 
+        private IndexSqlCache GetIndexCache(CollectionInstance instance)
+        {
+            IndexSqlCache sqlCache = _indexCache;
+            if (sqlCache == null)
+            {
+                sqlCache = new IndexSqlCache();
+                IndexInstance[] indexes = instance.Indexes;
+
+                sqlCache.DoNothing = !(indexes != null && indexes.Length > 0);
+                Dictionary<string, IndexKey> uniqueList = new Dictionary<string, IndexKey>();
+                StringBuilder insertSql = new StringBuilder(500);
+                StringBuilder allSql = new StringBuilder(500);
+                SqlCommand cmd = new SqlCommand();
+                int count = 0;
+
+                if (!sqlCache.DoNothing)
+                {
+                    foreach (IndexInstance index in indexes)
+                    {
+                        foreach (IndexKey key in index.Keys)
+                        {
+                            if (key.FieldName != "_id" && !uniqueList.ContainsKey(key.FieldName))
+                            {
+                                uniqueList.Add(key.FieldName, key);
+
+                                string parameterName = "@Idx" + count.ToString();
+                                sqlCache.ParameterMap[parameterName] = key;
+                                SqlDbType sqlType = BsonSqlSerializer.GetSqlTypeForBsonType(key.DataType);
+                                if (sqlType == SqlDbType.VarChar && !key.UseVarChar)
+                                    sqlType = SqlDbType.NVarChar;
+
+                                cmd.Parameters.Add(parameterName, sqlType);
+                                count += 1;
+                            }
+                        }
+                    }
+                }
+
+                //build the insert sql
+                //and the normal sql
+                insertSql.AppendLine();
+                insertSql.Append("INSERT INTO dbo." + _configuration.Prefix);
+                insertSql.Append(instance.CollectionName);
+                insertSql.Append("_I (BsonDocumentID, SchemaVersion");
+
+                StringBuilder insertSql2 = new StringBuilder(500);
+                insertSql2.AppendLine();
+                insertSql2.Append("VALUES (@IdxDocumentId,@IdxSchemaVersion");
+
+                allSql.AppendLine();
+                allSql.Append("UPDATE dbo." + _configuration.Prefix);
+                allSql.Append(instance.CollectionName);
+                allSql.Append("_I SET SchemaVersion = @IdxSchemaVersion");
+
+                if (!sqlCache.DoNothing)
+                {
+                    foreach (SqlParameter parameter in cmd.Parameters)
+                    {
+                        IndexKey parameterKey = sqlCache.ParameterMap[parameter.ParameterName];
+                        string columnName = parameterKey.FieldName.Replace(".", "_");
+
+                        insertSql.AppendFormat(",{0}", columnName);
+                        insertSql2.AppendFormat(",{0}", parameter.ParameterName);
+
+                        allSql.AppendFormat(",{0} = {1}", columnName, parameter.ParameterName);
+                    }
+                }
+
+                insertSql.Append(") ");
+                insertSql2.Append(") ");
+                insertSql.Append(insertSql2.ToString());
+
+                allSql.Append(" WHERE BsonDocumentID = @IdxDocumentId");
+                allSql.AppendLine();
+                allSql.AppendLine("IF @@ROWCOUNT = 0");
+                allSql.Append(insertSql.ToString());
+
+                sqlCache.InsertOnlySql = insertSql.ToString();
+                sqlCache.Sql = allSql.ToString();
+                sqlCache.Command = cmd;
+                sqlCache.UniqueList = uniqueList;
+
+                _indexCache = sqlCache;
+            }
+
+            return sqlCache;
+        }
+
 		private void UpdateDocumentIndex(SqlCommand command, StringBuilder sql, BsonDocument document, bool insertOnly)
 		{
 			CollectionInstance instance = GetConfiguration();
-			IndexSqlCache sqlCache = _indexCache;
-			if (sqlCache == null)
-			{
-				sqlCache = new IndexSqlCache();
-				IndexInstance[] indexes = instance.Indexes;
-				
-				sqlCache.DoNothing = !(indexes != null && indexes.Length > 0);
-				Dictionary<string, IndexKey> uniqueList = new Dictionary<string, IndexKey>();
-				StringBuilder insertSql = new StringBuilder(500);
-				StringBuilder allSql = new StringBuilder(500);
-				SqlCommand cmd = new SqlCommand();
-				int count = 0;
-
-				if (!sqlCache.DoNothing)
-				{
-					foreach (IndexInstance index in indexes)
-					{
-						foreach (IndexKey key in index.Keys)
-						{
-							if (key.FieldName != "_id" && !uniqueList.ContainsKey(key.FieldName))
-							{
-								uniqueList.Add(key.FieldName, key);
-
-								string parameterName = "@Idx" + count.ToString();
-								sqlCache.ParameterMap[parameterName] = key;
-								SqlDbType sqlType = BsonSqlSerializer.GetSqlTypeForBsonType(key.DataType);
-								if (sqlType == SqlDbType.VarChar && !key.UseVarChar)
-									sqlType = SqlDbType.NVarChar;
-
-								cmd.Parameters.Add(parameterName, sqlType);
-								count += 1;
-							}
-						}
-					}
-				}
-
-				//build the insert sql
-				//and the normal sql
-				insertSql.AppendLine();
-                insertSql.Append("INSERT INTO dbo." + _configuration.Prefix);
-				insertSql.Append(instance.CollectionName);
-				insertSql.Append("_I (BsonDocumentID, SchemaVersion");
-
-				StringBuilder insertSql2 = new StringBuilder(500);
-				insertSql2.AppendLine();
-				insertSql2.Append("VALUES (@IdxDocumentId,@IdxSchemaVersion");
-
-				allSql.AppendLine();
-                allSql.Append("UPDATE dbo." + _configuration.Prefix);
-				allSql.Append(instance.CollectionName);
-				allSql.Append("_I SET SchemaVersion = @IdxSchemaVersion");
-
-				if (!sqlCache.DoNothing)
-				{
-					foreach (SqlParameter parameter in cmd.Parameters)
-					{
-						IndexKey parameterKey = sqlCache.ParameterMap[parameter.ParameterName];
-						string columnName = parameterKey.FieldName.Replace(".", "_");
-
-						insertSql.AppendFormat(",{0}", columnName);
-						insertSql2.AppendFormat(",{0}", parameter.ParameterName);
-
-						allSql.AppendFormat(",{0} = {1}", columnName, parameter.ParameterName);
-					}
-				}
-
-				insertSql.Append(") ");
-				insertSql2.Append(") ");
-				insertSql.Append(insertSql2.ToString());
-
-				allSql.Append(" WHERE BsonDocumentID = @IdxDocumentId");
-				allSql.AppendLine();
-				allSql.AppendLine("IF @@ROWCOUNT = 0");
-				allSql.Append(insertSql.ToString());
-
-				sqlCache.InsertOnlySql = insertSql.ToString();
-				sqlCache.Sql = allSql.ToString();
-				sqlCache.Command = cmd;
-
-				_indexCache = sqlCache;
-			}
-
+            IndexSqlCache sqlCache = GetIndexCache(instance);
 			
 			//otherwise we need to add some sql here...
 			command.Parameters.Add("@IdxDocumentId", SqlDbType.UniqueIdentifier).Value =  document["_id"].AsGuid;
 			command.Parameters.Add("@IdxSchemaVersion", SqlDbType.TinyInt).Value = instance.SchemaVersion;
 
-			if (!_indexCache.DoNothing)
+            if (!sqlCache.DoNothing)
 			{
 				SqlCommand indexCommand = sqlCache.Command;
 				foreach (SqlParameter parameter in indexCommand.Parameters)
 										break;
 									case BsonType.DateTime:
 										commandParameter.Value = bsonValue.AsDateTime;
+                                        if (commandParameter.Value != null)
+                                            commandParameter.Value = ((DateTime)commandParameter.Value).ToUniversalTime(); //bson takes care of this internally, we need to do it for indexes
 										break;
 									case BsonType.String:
 										string bsonString = bsonValue.AsString;
 										break;
 									case BsonType.DateTime:
 										commandParameter.Value = bsonValue.AsDateTime;
+                                        if (commandParameter.Value != null)
+                                            commandParameter.Value = ((DateTime)commandParameter.Value).ToUniversalTime(); //bson takes care of this internally, we need to do it for indexes
 										break;
 									case BsonType.String:
 										string bsonString = bsonValue.AsString;
 
 			using (SqlConnection conn = new SqlConnection(_configuration.GetServer(connectionId).ConnectionString))
 			{
+                if (_logging)
+                {
+                    if (current != null && current.Contains("_id"))
+                        MDC.Set("auditKey.BsonID", current["_id"].AsGuid.ToString());
+
+                    MDC.Set("auditKey.Server", conn.DataSource);
+                    MDC.Set("auditKey.Db", conn.Database);
+
+                    if (current != null)
+                    {
+                        foreach (BsonElement e in current)
+                        {
+                            MDC.Set("audit." + e.Name, e.ToJson());
+                        }
+                    }
+                    _logger.Info(_logMask + ((forDelete) ? "Delete" : "Update"));
+
+                    //have to wipe them out or else another thread gets them and may taint the results
+                    MDC.Set("auditKey.BsonID", "");
+
+                    if (current != null)
+                    {
+                        foreach (BsonElement e in current)
+                        {
+                            MDC.Set("audit." + e.Name, "");
+                        }
+                    }
+                }
+
 				if (!startedTransaction)
 				{
 					command.Connection = transaction.Connection;
 
 			using (SqlConnection conn = new SqlConnection(_configuration.GetServer(connectionId).ConnectionString))
 			{
-				SqlCommand cmd = query.Command.Clone();
+                SqlCommand cmd = query.PrepareCommandForExecution(GetIndexCache(instance).UniqueList);
 				cmd.CommandText = sql;
+                
+                if (_logging)
+                {
+                    MDC.Set("auditKey.Server", conn.DataSource);
+                    MDC.Set("auditKey.Db", conn.Database);
+                    MDC.Set("audit.Sql", sql);
+                    MDC.Set("auditKey.QID", Guid.NewGuid().ToString());
 
+                    if(cmd.Parameters != null)
+                    {
+                        foreach (SqlParameter p in cmd.Parameters)
+                        {
+                            if (p.Value != null && p.Value != DBNull.Value)
+                            {
+                                try { MDC.Set("audit." + p.ParameterName, p.Value.ToString()); }
+                                catch { }
+                            }
+                        }
+                    }
+
+                    _logger.Info(_logMask + "Query");
+                    
+                    
+                    MDC.Set("audit.Sql", "");
+
+                    if (cmd.Parameters != null)
+                    {
+                        foreach (SqlParameter p in cmd.Parameters)
+                        {
+                            MDC.Set("audit." + p.ParameterName, "");
+                        }
+                    }
+                }
 
 				if (transaction != null)
 				{
 					else
 						wrapper = new DbReaderWrapper(dr);
 
+                    int logIndex = 0;
+
 					while (dr.Read())
 					{
+                        int logCount = 0;
+
 						//always get it as a document first
 						BsonDocument document = wrapper.GetBsonValue<BsonDocument>("BsonDocument");
 						document.Set("_id", wrapper.GetGuid("BsonDocumentID"));
 
+                        if (_logging)
+                        {
+                            MDC.Set("auditKey.Idx", logIndex++.ToString());
+                            MDC.Set("auditKey.BsonID", wrapper.GetGuid("BsonDocumentID").ToString());
+                            logCount++;
+                        }
+
 						if (fields != null)
 						{
 							//we arent' grabbing all of the fields
 									{
 										BsonDocument addlDocument = wrapper.GetBsonValue<BsonDocument>("BsonDocument" + joinSuffix);
 										addlDocument.Set("_id", wrapper.GetGuid("BsonDocumentID" + joinSuffix));
+
+                                        if (_logging)
+                                        {
+                                            MDC.Set("auditKey.BsonID" + logCount++.ToString(), addlDocument["_id"].AsGuid.ToString());
+                                        }
+
 										joinResult.SetResult(joinIndex, BsonSqlSerializer.Deserialize(join.ReturnType, addlDocument, (byte[])wrapper.GetValue("Version" + joinSuffix)));
 									}
 								}
 							//otherwise you return the type
 							documents.Add((TDocument)BsonSqlSerializer.Deserialize<TDocument>(document, (byte[])wrapper.GetValue("Version")));
 
+                        if (_logging)
+                        {
+                            _logger.Info(_logMask + "QueryResult");
+
+                            //clear what we need to
+                            MDC.Set("auditKey.BsonID", "");
+                            for (int logI = 0; logI < logCount; logI++)
+                            {
+                                MDC.Set("auditKey.BsonID" + logI.ToString(), "");
+                            }
+                        }
 					}
 
 					dr.Close();
+
+                    if (_logging)
+                    {                        
+                        MDC.Set("auditKey.QID", "");
+                        MDC.Set("auditKey.Idx", "");
+                    }
 				}
 			}
 
 
 			using (SqlConnection conn = new SqlConnection(_configuration.GetServer(connectionId).ConnectionString))
 			{
-				SqlCommand cmd = query.Command.Clone();
+                SqlCommand cmd = query.PrepareCommandForExecution(GetIndexCache(instance).UniqueList);
 				cmd.CommandText = sql;
 
+                if (_logging)
+                {
+                    MDC.Set("auditKey.Server", conn.DataSource);
+                    MDC.Set("auditKey.Db", conn.Database);
+                    MDC.Set("audit.Sql", sql);
+
+                    if (cmd.Parameters != null)
+                    {
+                        foreach (SqlParameter p in cmd.Parameters)
+                        {
+                            if (p.Value != null && p.Value != DBNull.Value)
+                            {
+                                try { MDC.Set("audit." + p.ParameterName, p.Value.ToString()); }
+                                catch { }
+                            }
+                        }
+                    }
+
+                    _logger.Info(_logMask + "Count");
+                    MDC.Set("audit.Sql", "");
+
+                    if (cmd.Parameters != null)
+                    {
+                        foreach (SqlParameter p in cmd.Parameters)
+                        {
+                            MDC.Set("audit." + p.ParameterName, "");
+                        }
+                    }
+                }
+
 
 				if (transaction != null)
 				{
 
 		private void ExecuteRemoveAgainstSingleConnection(string connectionId, Query query, int limit, SqlTransaction transaction, CollectionInstance instance, string sql)
 		{
-			SqlCommand command = query.Command.Clone();
+            SqlCommand command = query.PrepareCommandForExecution(GetIndexCache(instance).UniqueList);
 			command.CommandText = sql;
 			ExecuteCommandNonQuery(command, connectionId, transaction, true, null, null);
 		}
 			public Dictionary<string, IndexKey> ParameterMap { get; set; }
 			public SqlCommand Command { get; set; }
 			public bool DoNothing { get; set; }
+            public Dictionary<string, IndexKey> UniqueList { get; set; }
 
 			public IndexSqlCache()
 			{

BsonSql.Driver/BsonSqlDomain.cs

 			return instance;
 		}
 
+        public Configuration.ServerConnection[] GetConnections()
+        {
+            return _configuration.Servers;
+        }
+
 		public BsonSqlCollection GetCollection(string name)
 		{
 			DomainInstance configuration = GetConfiguration();

BsonSql.Driver/Builders/Query.cs

 using MongoDB.Bson;
 using BsonSql.Common;
 using System.Collections.Concurrent;
+using BsonSql.Driver.Configuration;
+using System.Data;
 
 namespace BsonSql.Driver.Builders
 {
 				else
 					_orderByClause = string.Empty;
 
+                _compiled = true;
+
 				return this;
 			}
 		}
 			}
 
 			cloned._command = command;
+            cloned._wrappedValuesForWhereClause = _wrappedValuesForWhereClause;
 
 			return cloned;
 		}
 				return _command;
 			}
 		}
+
+
+        //makes sure parameter types are of the correct type (varchar, nvarchar, int, etc, to avoid unnecessary conversions....)
+        public SqlCommand PrepareCommandForExecution(Dictionary<string, IndexKey> indexedKeys)
+        {
+            SqlCommand command = this.Command.Clone();
+
+            if (command.Parameters.Count == 0 || indexedKeys.Count == 0)
+                return command;
+
+            //otherwise we need to go through all the parameters, if it is an indexed field then check the index data type
+            //and modify the parameter type to the correct type...by default SQL will use nvarchar and if you indexed
+            //a field as varchar the resulting sql will cause an index scan because of the conversion, not allowing you
+            //to fully leverage indexing...
+
+            Dictionary<string, bool> processedParameters = new Dictionary<string, bool>();
+
+            foreach (KeyValuePair<string, BsonSqlFieldWrapper> kvp in _wrappedValuesForWhereClause)
+            {
+                SqlParameter parameter = command.Parameters[kvp.Key];
+                processedParameters[parameter.ParameterName] = true;    
+                BsonSqlFieldWrapper wrapper = kvp.Value;
+                IndexKey indexKey = null;
+                if (indexedKeys.TryGetValue(wrapper.FieldName, out indexKey))
+                {
+                    switch (indexKey.DataType)
+                    {
+                        case BsonType.String:
+                        case BsonType.Array:
+                            parameter.SqlDbType = ((indexKey.UseVarChar) ? SqlDbType.VarChar : SqlDbType.NVarChar);
+                            break;
+                        case BsonType.Int32:
+                            parameter.SqlDbType = SqlDbType.Int;
+                            break;
+                        case BsonType.Int64:
+                            parameter.SqlDbType = SqlDbType.BigInt;
+                            break;
+                    }
+                }
+            }
+
+            //for any not processed, which would be the static values, need to match those against the indexed keys as well
+            foreach (SqlParameter parameter in command.Parameters)
+            {
+                bool processed = false;
+                if (!processedParameters.TryGetValue(parameter.ParameterName, out processed))
+                {
+                    string fieldName = parameter.ParameterName.Substring(parameter.ParameterName.IndexOf('_') + 1).Replace("_", ".");
+                    IndexKey indexKey = null;
+                    if (indexedKeys.TryGetValue(fieldName, out indexKey))
+                    {
+                        switch (indexKey.DataType)
+                        {
+                            case BsonType.String:
+                            case BsonType.Array:
+                                parameter.SqlDbType = ((indexKey.UseVarChar) ? SqlDbType.VarChar : SqlDbType.NVarChar);
+                                break;
+                            case BsonType.Int32:
+                                parameter.SqlDbType = SqlDbType.Int;
+                                break;
+                            case BsonType.Int64:
+                                parameter.SqlDbType = SqlDbType.BigInt;
+                                break;
+                        }
+                    }
+                }
+            }
+
+            return command;
+        }
 	}
 }