public async Task Extract_Columns_In_Mongo_Test() { // Arrange Mock <IExtractionDatabase> mongoExtractionDatabaseMock = new Mock <IExtractionDatabase>(); mongoExtractionDatabaseMock.Setup(a => a.ConnectionType).Returns(Core.Persistences.ConnectionType.MongoDB); mongoExtractionDatabaseMock .Setup(a => a.Extract(It.IsAny <DatabaseConnection>(), It.IsAny <string>(), It.IsAny <IEnumerable <ExecuteParamModel> >())) .Returns(Task.FromResult(new ExtractingSchemaQueryModel { ColumnFields = new System.Collections.Generic.List <LetPortal.Portal.Models.Shared.ColumnField> { new LetPortal.Portal.Models.Shared.ColumnField { Name = "A", DisplayName = "A", FieldType = "a" } } })); DatabaseService databaseService = new DatabaseService(null, new IExtractionDatabase[] { mongoExtractionDatabaseMock.Object }); // Act ExtractingSchemaQueryModel result = await databaseService.ExtractColumnSchema(new LetPortal.Portal.Entities.Databases.DatabaseConnection { ConnectionString = "mongodb://localhost:27017", DatabaseConnectionType = "mongodb", DataSource = "letportal" }, "", null); // Assert Assert.True(result.ColumnFields.Count > 0); }
public Task <ExtractingSchemaQueryModel> Extract(DatabaseConnection database, string formattedString, IEnumerable <ExecuteParamModel> parameters) { var paramsList = new List <SqlParameter>(); if (parameters != null) { foreach (var param in parameters) { // We need to detect a parameter type and then re-mapping to db type var splitted = param.Name.Split("|"); var paramDbType = SqlDbType.NVarChar; object parsedValue; if (splitted.Length == 1) { // Default: string type paramDbType = _sqlServerMapper.GetSqlDbType(MapperConstants.String); parsedValue = param.ReplaceValue; } else { // It must contain 2 words paramDbType = _sqlServerMapper.GetSqlDbType(splitted[1]); parsedValue = _cSharpMapper.GetCSharpObjectByType(param.ReplaceValue, splitted[1]); } var fieldParam = StringUtil.GenerateUniqueName(); formattedString = formattedString.Replace("{{" + param.Name + "}}", "@" + fieldParam); paramsList.Add( new SqlParameter(fieldParam, paramDbType) { Value = parsedValue, Direction = ParameterDirection.Input }); } } var extractModel = new ExtractingSchemaQueryModel { ColumnFields = new System.Collections.Generic.List <Models.Shared.ColumnField>() }; using (var sqlDbConnection = new SqlConnection(database.ConnectionString)) { sqlDbConnection.Open(); var warpQuery = @"Select * from ({0}) s limit 1"; warpQuery = string.Format(warpQuery, formattedString); using (var command = new SqlCommand(formattedString, sqlDbConnection)) { if (paramsList.Count > 0) { command.Parameters.AddRange(paramsList.ToArray()); } using (var reader = command.ExecuteReader()) { using (var dt = new DataTable()) { dt.Load(reader); foreach (DataColumn dc in dt.Columns) { extractModel.ColumnFields.Add(new Models.Shared.ColumnField { Name = dc.ColumnName, DisplayName = dc.ColumnName, FieldType = GetType(dc.DataType) }); } } } } } return(Task.FromResult(extractModel)); }
public async Task <ExtractingSchemaQueryModel> Extract(DatabaseConnection database, string formattedString, IEnumerable <ExecuteParamModel> parameters) { // queryJsonString sample // { "$query" : { // "users": [ // "$match" : { 'username': '******' } // ] // }} // Note: we just support aggreation framework only if (parameters != null) { foreach (var param in parameters) { if (param.RemoveQuotes) { formattedString = formattedString.Replace("\"{{" + param.Name + "}}\"", param.ReplaceValue); } else { formattedString = formattedString.Replace("{{" + param.Name + "}}", param.ReplaceValue); } } } var result = new ExtractingSchemaQueryModel(); var parsingObject = JObject.Parse(formattedString); var mongoClient = new MongoClient(database.ConnectionString).GetDatabase(database.DataSource); var executionGroupTypes = parsingObject.Children().Select(a => (a as JProperty).Name); foreach (var executionGroupType in executionGroupTypes) { switch (executionGroupType) { case Constants.QUERY_KEY: var collectionNames = parsingObject[Constants.QUERY_KEY].Children().Select(a => (a as JProperty).Name); foreach (var collectionName in collectionNames) { var mongoCollection = mongoClient.GetCollection <BsonDocument>(collectionName); var collectionQuery = parsingObject[Constants.QUERY_KEY][collectionName].ToString(Newtonsoft.Json.Formatting.Indented); var aggregatePipes = BsonSerializer.Deserialize <BsonDocument[]>(collectionQuery).Select(a => (PipelineStageDefinition <BsonDocument, BsonDocument>)a).ToList(); var aggregateFluent = mongoCollection.Aggregate(); foreach (var pipe in aggregatePipes) { aggregateFluent = aggregateFluent.AppendStage(pipe); } using (var executingCursor = await aggregateFluent.ToCursorAsync()) { while (executingCursor.MoveNext()) { // Important note: this query must have one row result for extracting params and filters var currentDocument = executingCursor.Current.First(); foreach (var element in currentDocument.Elements) { var columnField = new ColumnField { Name = element.Name, DisplayName = element.Name, FieldType = GetTypeByBsonDocument(element.Value) }; result.ColumnFields.Add(columnField); } } } } break; default: break; } } return(result); }