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);
}
