private static string ConvertToSqlWhere(QueryIsNotNull queryIsNotNull, TypeReflector typeReflector, List <KeyValuePair <int, object> > parameters, IDictionary <string, CrossApplyDefinition> crossApplyDefinitions)
{
var reflectedType = typeReflector.Navigate(queryIsNotNull.Field);
if (reflectedType == null)
{
throw new InvalidOperationException($"Unable to find property '{queryIsNotNull.Field}' on type '{typeReflector}'");
}
return($"JSON_VALUE(_document,'$.{queryIsNotNull.Field}') IS NOT NULL");
}
private static FieldType ResolveField(string originalPath, TypeReflector typeReflector, IDictionary <string, CrossApplyDefinition> crossApplyDefinitions, bool appendToGroupByClause = false)
{
var fieldTypes = typeReflector.Navigate(originalPath).ToList();
if (!fieldTypes.Any())
{
throw new InvalidOperationException($"Property '{originalPath}' is invalid");
}
var resultingPath = new List <string>();
var tempPath = new List <string>();
CrossApplyDefinition lastCrossApplyDefinition = null;
foreach (var fieldType in fieldTypes)
{
if (fieldType.IsObjectArray)
{
if (fieldType == fieldTypes.Last())
{
throw new InvalidOperationException();
}
tempPath.Add(fieldType.Name);
lastCrossApplyDefinition = new CrossApplyDefinition
{
Parent = fieldType.Parent?.Path != null ? crossApplyDefinitions[fieldType.Parent.Path] : null,
Name = string.Join(".", tempPath)
};
crossApplyDefinitions[lastCrossApplyDefinition.Name] = lastCrossApplyDefinition;
if (appendToGroupByClause)
{
lastCrossApplyDefinition.AppendToGroupBy = true;
}
tempPath.Clear();
}
else
{
resultingPath.Add(fieldType.Name);
tempPath.Add(fieldType.Name);
}
}
return(new FieldType()
{
Type = fieldTypes.Last(),
Path = string.Join(".", resultingPath),
ParentField = lastCrossApplyDefinition?.Path ?? "_document"
});
}
public void TypeReflectorTest()
{
var typePost = TypeReflector.Create <Post>();
Assert.IsNotNull(typePost);
Assert.AreEqual(9, typePost.Properties.Count);
Assert.AreEqual(typeof(string), typePost.Properties["Title"].PropertyType);
Assert.AreEqual(typeof(List <Comment>), typePost.Properties["Comments"].PropertyType);
Assert.AreEqual(typeof(string[]), typePost.Properties["Tags"].PropertyType);
var userTypes = TypeReflector.Navigate <Post>("Author.Username").ToList();
Assert.IsNotNull(userTypes);
Assert.AreEqual(typeof(User), userTypes[0].Type);
Assert.AreEqual(typeof(string), userTypes[1].Type);
var tagsType = TypeReflector.Navigate <Post>("Tags").ToList();
Assert.IsNotNull(tagsType);
Assert.IsTrue(tagsType[0].IsValueArray);
Assert.AreEqual(typeof(string), tagsType[0].Type);
var commentsType = TypeReflector.Navigate <Post>("Comments.Content").ToList();
Assert.IsNotNull(commentsType);
Assert.IsTrue(commentsType[0].IsObjectArray);
Assert.AreEqual(typeof(Comment), commentsType[0].Type);
Assert.AreEqual(typeof(string), commentsType[1].Type);
userTypes = TypeReflector.Navigate <Post>("Comments.Author.Username").ToList();
Assert.IsNotNull(userTypes);
Assert.IsTrue(userTypes[0].IsObjectArray);
Assert.AreEqual(typeof(Comment), userTypes[0].Type);
Assert.AreEqual(typeof(User), userTypes[1].Type);
Assert.AreEqual(typeof(string), userTypes[2].Type);
var replieTypes = TypeReflector.Navigate <Post>("Comments.Replies.Author.Username").ToList();
Assert.IsNotNull(replieTypes);
Assert.IsTrue(replieTypes[0].IsObjectArray);
Assert.AreEqual(typeof(Comment), replieTypes[0].Type);
Assert.IsTrue(replieTypes[1].IsObjectArray);
Assert.AreEqual(typeof(Reply), replieTypes[1].Type);
Assert.AreEqual(typeof(User), replieTypes[2].Type);
Assert.AreEqual(typeof(string), replieTypes[3].Type);
}
public async Task EnsureIndexAsync(NsDatabase database, string tableName, TypeReflector typeReflector, string path, bool unique = false, bool ascending = true)
{
Validate.NotNull(database, nameof(database));
Validate.NotNullOrEmptyOrWhiteSpace(tableName, nameof(tableName));
Validate.NotNullOrEmptyOrWhiteSpace(path, nameof(path));
Validate.NotNull(typeReflector, nameof(typeReflector));
/*
* USE [DatabaseTest_Index]
* IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[posts]') AND name = 'Updated')
* BEGIN
* ALTER TABLE [dbo].[Posts]
* ADD [Updated] AS (CONVERT(datetime2, JSON_VALUE([_document],'$.Updated'), 102))
* END
* IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = 'IDX_Updated' AND object_id = OBJECT_ID('posts'))
* BEGIN CREATE NONCLUSTERED INDEX [IDX_Updated] ON [dbo].[posts]
* ( [Updated] DESC )
* WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
* END
*/
var fieldTypes = typeReflector.Navigate(path).ToList();
if (!fieldTypes.Any())
{
throw new InvalidOperationException($"Path '{path}' doesn't represent a valid property");
}
if (fieldTypes.Any(_ => _.IsArray))
{
throw new InvalidOperationException($"Unable to create an index for path '{path}', it traverses arrays or collections of objects");
}
var columnName = path.Replace(".", "_");
string addColumnCommand = null;
var lastFieldType = fieldTypes.Last();
if (lastFieldType.Is(typeof(DateTime)))
{
addColumnCommand = $"ADD [{columnName}] AS (CONVERT(datetime2, JSON_VALUE([_document],'$.{path}'), 102))";
}
else if (lastFieldType.Is(typeof(int)))
{
addColumnCommand = $"ADD [{columnName}] AS (CONVERT(int, JSON_VALUE([_document],'$.{path}')))";
}
if (addColumnCommand == null)
{
throw new InvalidOperationException($"Type '{lastFieldType.Type}' is not (yet?) supported for indexes");
}
await ExecuteNonQueryAsync(
$"USE [{database.Name}]",
$"IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N\'[dbo].[{tableName}]\') AND name = \'{columnName}\')",
$"BEGIN",
$"ALTER TABLE [dbo].[Posts]",
addColumnCommand,
$"END",
$"IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = \'IDX_{columnName}\' AND object_id = OBJECT_ID(\'{tableName}\'))",
$"BEGIN",
$"CREATE {(unique ? "UNIQUE" : string.Empty)} NONCLUSTERED INDEX [IDX_{columnName}] ON [dbo].[{tableName}]",
$"( [{columnName}] {(ascending ? "ASC" : "DESC")} )",
$"WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]",
$"END");
}