public void ConversionToTsVector()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
context.Blogs.Add(new Blog {
Name = "_"
});
context.SaveChanges();
const string expected = "'a':5 'b':10";
var casted = context.Blogs.Select(x => NpgsqlTextFunctions.AsTsVector(expected)).First();
Assert.That(
NpgsqlTsVector.Parse(casted).ToString(),
Is.EqualTo(NpgsqlTsVector.Parse(expected).ToString()));
var converted = context.Blogs.Select(x => NpgsqlTextFunctions.ToTsVector("banana car")).First();
Assert.That(
NpgsqlTsVector.Parse(converted).ToString(),
Is.EqualTo(NpgsqlTsVector.Parse("'banana':1 'car':2").ToString()));
converted = context.Blogs.Select(x => NpgsqlTextFunctions.ToTsVector("english", "banana car")).First();
Assert.That(
NpgsqlTsVector.Parse(converted).ToString(),
Is.EqualTo(NpgsqlTsVector.Parse("'banana':1 'car':2").ToString()));
}
}
public void Match()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog1 = new Blog
{
Name = "The quick brown fox jumps over the lazy dog."
};
var blog2 = new Blog
{
Name = "Jackdaws loves my big sphinx of quartz."
};
context.Blogs.Add(blog1);
context.Blogs.Add(blog2);
context.SaveChanges();
var foundBlog = context
.Blogs
.FirstOrDefault(
x =>
NpgsqlTextFunctions.Match(
NpgsqlTextFunctions.ToTsVector(x.Name),
NpgsqlTextFunctions.ToTsQuery("jump & dog")));
Assert.That(foundBlog != null);
Assert.That(foundBlog.Name, Is.EqualTo(blog1.Name));
}
}
public void TsQueryOr()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
context.Blogs.Add(new Blog {
Name = "_"
});
context.SaveChanges();
var result = context.Blogs.Select(
x => NpgsqlTextFunctions.QueryOr(
NpgsqlTextFunctions.AsTsQuery("fat | rat"),
NpgsqlTextFunctions.AsTsQuery("cat"))).First();
Assert.That(
NpgsqlTsQuery.Parse(result).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse("( 'fat' | 'rat' ) | 'cat'").ToString()));
result = context.Blogs.Select(
x => NpgsqlTextFunctions.AsTsQuery("fat | rat")
+ NpgsqlTextFunctions.AsTsQuery("cat")).First();
Assert.That(
NpgsqlTsQuery.Parse(result).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse("( 'fat' | 'rat' ) | 'cat'").ToString()));
}
}
public void TsRewrite()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog1 = new Blog
{
Name = "_"
};
context.Blogs.Add(blog1);
context.SaveChanges();
var newQuery = context
.Blogs.Select(
x =>
NpgsqlTextFunctions.TsRewrite(
"a & b",
"a",
"foo|bar"))
.FirstOrDefault();
Assert.That(
NpgsqlTsQuery.Parse(newQuery).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse("'b' & ( 'foo' | 'bar' )").ToString()));
}
}
public void TsRankCd()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog1 = new Blog
{
Name = "cooky cookie piano pianos"
};
context.Blogs.Add(blog1);
context.SaveChanges();
var rank = context
.Blogs
.Select(
x => NpgsqlTextFunctions.TsRankCd(
NpgsqlTextFunctions.ToTsVector(x.Name),
NpgsqlTextFunctions.PlainToTsQuery("cookie")))
.FirstOrDefault();
Assert.That(rank, Is.GreaterThan(0));
rank = context
.Blogs
.Select(
x => NpgsqlTextFunctions.TsRankCd(
NpgsqlTextFunctions.ToTsVector(x.Name),
NpgsqlTextFunctions.PlainToTsQuery("cookie"),
NpgsqlRankingNormalization.DivideByLength
| NpgsqlRankingNormalization.DivideByUniqueWordCount))
.FirstOrDefault();
Assert.That(rank, Is.GreaterThan(0));
rank = context
.Blogs
.Select(
x => NpgsqlTextFunctions.TsRankCd(
0.1f, 0.2f, 0.4f, 1.0f,
NpgsqlTextFunctions.ToTsVector(x.Name),
NpgsqlTextFunctions.PlainToTsQuery("cookie")))
.FirstOrDefault();
Assert.That(rank, Is.GreaterThan(0));
rank = context
.Blogs
.Select(
x => NpgsqlTextFunctions.TsRankCd(
0.1f, 0.2f, 0.4f, 1.0f,
NpgsqlTextFunctions.ToTsVector(x.Name),
NpgsqlTextFunctions.PlainToTsQuery("cookie"),
NpgsqlRankingNormalization.DivideByLength
| NpgsqlRankingNormalization.DivideByUniqueWordCount))
.FirstOrDefault();
Assert.That(rank, Is.GreaterThan(0));
}
}
public void MatchRegex_NotSupportedOption(RegexOptions options)
{
using (var context = new BloggingContext(ConnectionString))
{
Assert.That(() =>
{
var results = (from b in context.Blogs
where NpgsqlTextFunctions.MatchRegex(b.Name, "Some pattern", options)
select b.Name).ToList();
}, Throws.InnerException.TypeOf <NotSupportedException>());
}
}
public void TsHeadline()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog1 = new Blog
{
Name = "cooky cookie piano pianos"
};
context.Blogs.Add(blog1);
var blog2 = new Blog
{
Name = "blue crab denominates elephant"
};
context.Blogs.Add(blog2);
context.SaveChanges();
var headlines = context
.Blogs
.Select(
x => NpgsqlTextFunctions.TsHeadline(
x.Name,
NpgsqlTextFunctions.ToTsQuery("cookie"),
"StartSel=<i> StopSel=</i>"))
.ToList();
Assert.That(headlines.Count, Is.EqualTo(2));
Assert.That(headlines[0], Is.EqualTo("<i>cooky</i> <i>cookie</i> piano pianos"));
Assert.That(headlines[1], Is.EqualTo(blog2.Name));
headlines = context
.Blogs
.Select(
x => NpgsqlTextFunctions.TsHeadline(
"english",
x.Name,
NpgsqlTextFunctions.ToTsQuery("piano"),
"StartSel=<i> StopSel=</i>"))
.ToList();
Assert.That(headlines.Count, Is.EqualTo(2));
Assert.That(headlines[0], Is.EqualTo("cooky cookie <i>piano</i> <i>pianos</i>"));
Assert.That(headlines[1], Is.EqualTo(blog2.Name));
}
}
public void MatchRegexOptions(RegexOptions options, string pattern, string matchingInput, string mismatchingInput)
{
// Arrange
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
context.Blogs.Add(new Blog()
{
Name = matchingInput
});
context.Blogs.Add(new Blog()
{
Name = mismatchingInput
});
context.SaveChanges();
}
// Act
// Ensure correctness of a test case
var netMatchResult = Regex.IsMatch(matchingInput, pattern, options);
var netMismatchResult = Regex.IsMatch(mismatchingInput, pattern, options);
List <string> pgMatchResults;
List <string> pgMismatchResults;
using (var context = new BloggingContext(ConnectionString))
{
pgMatchResults = (from b in context.Blogs
where NpgsqlTextFunctions.MatchRegex(b.Name, pattern, options)
select b.Name).ToList();
pgMismatchResults = (from b in context.Blogs
where !NpgsqlTextFunctions.MatchRegex(b.Name, pattern, options)
select b.Name).ToList();
}
// Assert
Assert.That(netMatchResult, Is.True);
Assert.That(netMismatchResult, Is.False);
Assert.That(pgMatchResults.Count, Is.EqualTo(1));
Assert.That(pgMatchResults[0], Is.EqualTo(matchingInput));
Assert.That(pgMismatchResults.Count, Is.EqualTo(1));
Assert.That(pgMismatchResults[0], Is.EqualTo(mismatchingInput));
}
public void SetWeight()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog1 = new Blog
{
Name = "The quick brown fox jumps over the lazy dog."
};
context.Blogs.Add(blog1);
var post1 = new Post
{
Blog = blog1,
Title = "Lorem ipsum",
Content = "Dolor sit amet",
Rating = 5
};
context.Posts.Add(post1);
var post2 = new Post
{
Blog = blog1,
Title = "consectetur adipiscing elit",
Content = "Sed sed rhoncus",
Rating = 4
};
context.Posts.Add(post2);
context.SaveChanges();
var foundPost = context.Posts.FirstOrDefault(
x => NpgsqlTextFunctions.Match(
NpgsqlTextFunctions.SetWeight(
NpgsqlTextFunctions.ToTsVector(x.Title ?? string.Empty),
NpgsqlWeightLabel.D)
+ NpgsqlTextFunctions.SetWeight(
NpgsqlTextFunctions.ToTsVector(x.Content ?? string.Empty),
NpgsqlWeightLabel.C),
NpgsqlTextFunctions.PlainToTsQuery("dolor")));
Assert.That(foundPost != null);
Assert.That(foundPost.Title, Is.EqualTo(post1.Title));
}
}
public void TsIsContained()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
context.Blogs.Add(new Blog {
Name = "_"
});
context.SaveChanges();
var result = context.Blogs.Select(
x =>
NpgsqlTextFunctions.QueryIsContained(
NpgsqlTextFunctions.AsTsQuery("cat"),
NpgsqlTextFunctions.AsTsQuery("cat & rat"))).First();
Assert.That(result, Is.True);
}
}
public void TsVectorConcat()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
context.Blogs.Add(new Blog {
Name = "_"
});
context.SaveChanges();
var result = context.Blogs.Select(
x => NpgsqlTextFunctions.AsTsVector("a:1 b:2")
+ NpgsqlTextFunctions.AsTsVector("c:1 d:2 b:3")).First();
Assert.That(
NpgsqlTsVector.Parse(result).ToString(),
Is.EqualTo(NpgsqlTsVector.Parse("'a':1 'b':2,5 'c':3 'd':4").ToString()));
}
}
public void Length()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog = new Blog
{
Name = "cooky cookie cookies piano pianos"
};
context.Blogs.Add(blog);
context.SaveChanges();
var lexemeCount = context
.Blogs
.Select(x => NpgsqlTextFunctions.Length(NpgsqlTextFunctions.ToTsVector(x.Name)))
.FirstOrDefault();
Assert.That(lexemeCount, Is.EqualTo(2));
}
}
public void NumNode()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog = new Blog
{
Name = "_"
};
context.Blogs.Add(blog);
context.SaveChanges();
var nodeCount = context
.Blogs
.Select(x => NpgsqlTextFunctions.NumNode(NpgsqlTextFunctions.ToTsQuery("(fat & rat) | cat")))
.FirstOrDefault();
Assert.That(nodeCount, Is.EqualTo(5));
}
}
public void ConversionToTsQuery()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
context.Blogs.Add(new Blog {
Name = "_"
});
context.SaveChanges();
const string expected = "'b' & 'c'";
var casted = context.Blogs.Select(x => NpgsqlTextFunctions.AsTsQuery(expected)).First();
Assert.That(
NpgsqlTsQuery.Parse(casted).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse(expected).ToString()));
var converted = context.Blogs.Select(x => NpgsqlTextFunctions.ToTsQuery("b & c")).First();
Assert.That(
NpgsqlTsQuery.Parse(converted).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse(expected).ToString()));
converted = context.Blogs.Select(x => NpgsqlTextFunctions.ToTsQuery("english", "b & c")).First();
Assert.That(
NpgsqlTsQuery.Parse(converted).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse(expected).ToString()));
converted = context.Blogs.Select(x => NpgsqlTextFunctions.PlainToTsQuery("b & c")).First();
Assert.That(
NpgsqlTsQuery.Parse(converted).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse(expected).ToString()));
converted = context.Blogs.Select(x => NpgsqlTextFunctions.PlainToTsQuery("english", "b & c")).First();
Assert.That(
NpgsqlTsQuery.Parse(converted).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse(expected).ToString()));
}
}
public void QueryTree()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog = new Blog
{
Name = "_"
};
context.Blogs.Add(blog);
context.SaveChanges();
var queryTree = context
.Blogs
.Select(x => NpgsqlTextFunctions.QueryTree(NpgsqlTextFunctions.ToTsQuery("foo & ! bar")))
.FirstOrDefault();
Assert.That(
NpgsqlTsQuery.Parse(queryTree).ToString(),
Is.EqualTo(NpgsqlTsQuery.Parse("'foo'").ToString()));
}
}
public void Strip()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog = new Blog
{
Name = "cooky cookie cookies piano pianos"
};
context.Blogs.Add(blog);
context.SaveChanges();
var strippedTsVector = context
.Blogs
.Select(x => NpgsqlTextFunctions.Strip(NpgsqlTextFunctions.ToTsVector(x.Name)))
.FirstOrDefault();
Assert.That(
NpgsqlTsVector.Parse(strippedTsVector).ToString(),
Is.EqualTo(NpgsqlTsVector.Parse("'cooki' 'piano'").ToString()));
}
}
public void TsQueryPhrase()
{
using (var context = new BloggingContext(ConnectionString))
{
context.Database.Log = Console.Out.WriteLine;
var blog1 = new Blog
{
Name = "_"
};
context.Blogs.Add(blog1);
context.SaveChanges();
var newQuery = context
.Blogs.Select(x => NpgsqlTextFunctions.TsQueryPhrase("b", "c"))
.FirstOrDefault();
Assert.That(newQuery, Is.EqualTo("'b' <-> 'c'"));
newQuery = context
.Blogs.Select(x => NpgsqlTextFunctions.TsQueryPhrase("b", "c", 10))
.FirstOrDefault();
Assert.That(newQuery, Is.EqualTo("'b' <10> 'c'"));
}
}
