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 TsQueryOperatorPrecedence()
{
var query = NpgsqlTsQuery.Parse("!a <-> b & c | d & e");
var expectedGrouping = NpgsqlTsQuery.Parse("((!(a) <-> b) & c) | (d & e)");
Assert.AreEqual(expectedGrouping.ToString(), query.ToString());
}
public void TsQuery()
{
NpgsqlTsQuery query;
query = new NpgsqlTsQueryLexeme("a", NpgsqlTsQueryLexeme.Weight.A | NpgsqlTsQueryLexeme.Weight.B);
query = new NpgsqlTsQueryOr(query, query);
query = new NpgsqlTsQueryOr(query, query);
var str = query.ToString();
query = NpgsqlTsQuery.Parse("a & b | c");
Assert.AreEqual("'a' & 'b' | 'c'", query.ToString());
query = NpgsqlTsQuery.Parse("'a''':*ab&d:d&!c");
Assert.AreEqual("'a''':*AB & 'd':D & !'c'", query.ToString());
query = NpgsqlTsQuery.Parse("(a & !(c | d)) & (!!a&b) | c | d | e");
Assert.AreEqual("( ( 'a' & !( 'c' | 'd' ) & !( !'a' ) & 'b' | 'c' ) | 'd' ) | 'e'", query.ToString());
Assert.AreEqual(query.ToString(), NpgsqlTsQuery.Parse(query.ToString()).ToString());
query = NpgsqlTsQuery.Parse("(((a:*)))");
Assert.AreEqual("'a':*", query.ToString());
query = NpgsqlTsQuery.Parse(@"'a\\b''cde'");
Assert.AreEqual(@"a\b'cde", ((NpgsqlTsQueryLexeme)query).Text);
Assert.AreEqual(@"'a\\b''cde'", query.ToString());
Assert.Throws(typeof(FormatException), () => NpgsqlTsQuery.Parse("a b c & &"));
Assert.Throws(typeof(FormatException), () => NpgsqlTsQuery.Parse("&"));
Assert.Throws(typeof(FormatException), () => NpgsqlTsQuery.Parse("|"));
Assert.Throws(typeof(FormatException), () => NpgsqlTsQuery.Parse("!"));
Assert.Throws(typeof(FormatException), () => NpgsqlTsQuery.Parse("("));
Assert.Throws(typeof(FormatException), () => NpgsqlTsQuery.Parse(")"));
Assert.Throws(typeof(FormatException), () => NpgsqlTsQuery.Parse("()"));
}
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 async Task TsQuery()
{
using var conn = await OpenConnectionAsync();
var queryString = conn.PostgreSqlVersion < new Version(9, 6)
? "'a' & !( 'c' | 'd' ) & !!'a' & 'b' | 'ä' | 'f'"
: "'a' & !( 'c' | 'd' ) & !!'a' & 'b' | 'ä' | 'x' <-> 'y' | 'x' <10> 'y' | 'd' <0> 'e' | 'f'";
var query = NpgsqlTsQuery.Parse(queryString);
using var cmd = new NpgsqlCommand("SELECT @s::tsquery, @q, @q::text", conn)
{
Parameters =
{
new NpgsqlParameter("s", queryString),
new NpgsqlParameter("q", query),
}
};
using var reader = await cmd.ExecuteReaderAsync();
await reader.ReadAsync();
Assert.AreEqual(query.ToString(), reader.GetFieldValue <NpgsqlTsQuery>(0).ToString());
Assert.AreEqual(query.ToString(), reader.GetFieldValue <NpgsqlTsQuery>(1).ToString());
Assert.AreEqual(queryString, reader.GetFieldValue <string>(2));
}
static MappedDataTypes CreateMappedDataTypes(int id)
=> new MappedDataTypes
{
Int = id,
LongAsBigint = 78L,
ShortAsSmallint = 79,
ByteAsSmallint = 80,
UintAsInt = uint.MaxValue,
UlongAsBigint = ulong.MaxValue,
UShortAsSmallint = ushort.MaxValue,
UintAsBigint = uint.MaxValue,
UShortAsInt = ushort.MaxValue,
BoolAsBoolean = true,
DecimalAsMoney = 81.1m,
Decimal = 101.7m,
DecimalAsNumeric = 103.9m,
FloatAsReal = 84.4f,
DoubleAsDoublePrecision = 85.5,
DateTimeAsTimestamp = new DateTime(2015, 1, 2, 10, 11, 12),
DateTimeAsTimestamptz = new DateTime(2016, 1, 2, 11, 11, 12, DateTimeKind.Utc),
DateTimeAsDate = new DateTime(2015, 1, 2, 0, 0, 0),
TimeSpanAsTime = new TimeSpan(11, 15, 12),
DateTimeOffsetAsTimetz = new DateTimeOffset(1, 1, 1, 12, 0, 0, TimeSpan.FromHours(2)),
TimeSpanAsInterval = new TimeSpan(11, 15, 12),
StringAsText = "Gumball Rules!",
StringAsVarchar = "Gumball Rules OK",
CharAsChar1 = 'f',
CharAsText = 'g',
CharAsVarchar = 'h',
BytesAsBytea = new byte[] { 86 },
GuidAsUuid = new Guid("a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"),
EnumAsText = StringEnum16.Value4,
EnumAsVarchar = StringEnumU16.Value4,
PhysicalAddressAsMacaddr = PhysicalAddress.Parse("08-00-2B-01-02-03"),
NpgsqlPointAsPoint = new NpgsqlPoint(5.2, 3.3),
StringAsJsonb = @"{""a"": ""b""}",
StringAsJson = @"{""a"": ""b""}",
DictionaryAsHstore = new Dictionary<string, string> { { "a", "b" } },
NpgsqlRangeAsRange = new NpgsqlRange<int>(4, true, 8, false),
IntArrayAsIntArray = new[] { 2, 3 },
PhysicalAddressArrayAsMacaddrArray = new[] { PhysicalAddress.Parse("08-00-2B-01-02-03"), PhysicalAddress.Parse("08-00-2B-01-02-04") },
UintAsXid = (uint)int.MaxValue + 1,
SearchQuery = NpgsqlTsQuery.Parse("a & b"),
SearchVector = NpgsqlTsVector.Parse("a b"),
RankingNormalization = NpgsqlTsRankingNormalization.DivideByLength,
Mood = Mood.Sad
};
static void AssertMappedDataTypes(MappedDataTypes entity, int id)
{
var expected = CreateMappedDataTypes(id);
Assert.Equal(id, entity.Int);
Assert.Equal(78, entity.LongAsBigint);
Assert.Equal(79, entity.ShortAsSmallint);
Assert.Equal(uint.MaxValue, entity.UintAsInt);
Assert.Equal(ulong.MaxValue, entity.UlongAsBigint);
Assert.Equal(ushort.MaxValue, entity.UShortAsSmallint);
Assert.Equal(uint.MaxValue, entity.UintAsBigint);
Assert.Equal(ushort.MaxValue, entity.UShortAsInt);
Assert.True(entity.BoolAsBoolean);
Assert.Equal(81.1m, entity.DecimalAsMoney);
Assert.Equal(101.7m, entity.Decimal);
Assert.Equal(103.9m, entity.DecimalAsNumeric);
Assert.Equal(84.4f, entity.FloatAsReal);
Assert.Equal(85.5, entity.DoubleAsDoublePrecision);
Assert.Equal(new DateTime(2015, 1, 2, 10, 11, 12), entity.DateTimeAsTimestamp);
Assert.Equal(new DateTime(2016, 1, 2, 11, 11, 12, DateTimeKind.Utc), entity.DateTimeAsTimestamptz);
Assert.Equal(new DateTime(2015, 1, 2, 0, 0, 0), entity.DateTimeAsDate);
Assert.Equal(new TimeSpan(11, 15, 12), entity.TimeSpanAsTime);
Assert.Equal(new DateTimeOffset(1, 1, 1, 12, 0, 0, TimeSpan.FromHours(2)), entity.DateTimeOffsetAsTimetz);
Assert.Equal(new TimeSpan(11, 15, 12), entity.TimeSpanAsInterval);
Assert.Equal("Gumball Rules!", entity.StringAsText);
Assert.Equal("Gumball Rules OK", entity.StringAsVarchar);
Assert.Equal('f', entity.CharAsChar1);
Assert.Equal('g', entity.CharAsText);
Assert.Equal('h', entity.CharAsVarchar);
Assert.Equal(new byte[] { 86 }, entity.BytesAsBytea);
Assert.Equal(new Guid("a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"), entity.GuidAsUuid);
Assert.Equal(StringEnum16.Value4, entity.EnumAsText);
Assert.Equal(StringEnumU16.Value4, entity.EnumAsVarchar);
Assert.Equal(PhysicalAddress.Parse("08-00-2B-01-02-03"), entity.PhysicalAddressAsMacaddr);
Assert.Equal(new NpgsqlPoint(5.2, 3.3), entity.NpgsqlPointAsPoint);
Assert.Equal(@"{""a"": ""b""}", entity.StringAsJsonb);
Assert.Equal(@"{""a"": ""b""}", entity.StringAsJson);
Assert.Equal(new Dictionary<string, string> { { "a", "b" } }, entity.DictionaryAsHstore);
Assert.Equal(new NpgsqlRange<int>(4, true, 8, false), entity.NpgsqlRangeAsRange);
Assert.Equal(new[] { 2, 3 }, entity.IntArrayAsIntArray);
Assert.Equal(new[] { PhysicalAddress.Parse("08-00-2B-01-02-03"), PhysicalAddress.Parse("08-00-2B-01-02-04") }, entity.PhysicalAddressArrayAsMacaddrArray);
Assert.Equal((uint)int.MaxValue + 1, entity.UintAsXid);
Assert.Equal(NpgsqlTsQuery.Parse("a & b").ToString(), entity.SearchQuery.ToString());
Assert.Equal(NpgsqlTsVector.Parse("a b").ToString(), entity.SearchVector.ToString());
Assert.Equal(NpgsqlTsRankingNormalization.DivideByLength, entity.RankingNormalization);
}
public void TsQueryParse_converted_to_cast()
{
using var context = CreateContext();
var tsquery = context.Customers.Select(c => NpgsqlTsQuery.Parse("a & b")).First();
Assert.NotNull(tsquery);
AssertSql(
@"SELECT 'a & b'::tsquery
FROM ""Customers"" AS c
LIMIT 1");
}
public void TsQuery()
{
using (var cmd = Conn.CreateCommand())
{
var query = NpgsqlTsQuery.Parse("(a & !(c | d)) & (!!a&b) | ä | d | e");
cmd.CommandText = "Select :p";
cmd.Parameters.AddWithValue("p", query);
var output = cmd.ExecuteScalar();
Assert.AreEqual(query.ToString(), output.ToString());
}
}
/// <summary>
/// Creates a "followed by" operator, specifying 2 child nodes and the
/// distance between them in lexemes.
/// </summary>
/// <param name="left"></param>
/// <param name="distance"></param>
/// <param name="right"></param>
public NpgsqlTsQueryFollowedBy(
[CanBeNull] NpgsqlTsQuery left,
int distance,
[CanBeNull] NpgsqlTsQuery right)
{
if (distance < 0)
{
throw new ArgumentOutOfRangeException(nameof(distance));
}
Left = left;
Distance = distance;
Right = right;
Kind = NodeKind.Phrase;
}
public void TsQuery()
{
using (var conn = OpenConnection())
using (var cmd = conn.CreateCommand())
{
var query = conn.PostgreSqlVersion < new Version(9, 6)
? NpgsqlTsQuery.Parse("(a & !(c | d)) & (!!a&b) | ä | f")
: NpgsqlTsQuery.Parse("(a & !(c | d)) & (!!a&b) | ä | x <-> y | x <10> y | d <0> e | f");
cmd.CommandText = "Select :p";
cmd.Parameters.AddWithValue("p", query);
var output = cmd.ExecuteScalar();
Assert.AreEqual(query.ToString(), output.ToString());
}
}
/// <summary>
/// 全文检索 查询
/// </summary>
/// <param name="keyword"></param>
/// <returns></returns>
public NpgsqlTsQuery GetSerachNpgsqlTsQuery(string keyword)
{
if (keyword.Contains('&'))
{
string[] keys = keyword.Split('&');
return(GetSerachNpgsqlTsQuery_And(keys));
}
if (keyword.Contains("|"))
{
string[] keys = keyword.Split('|');
return(GetSerachNpgsqlTsQuery_Or(keys));
}
NpgsqlTsQuery vector;
try
{
var segmenter = new JiebaSegmenter();
HtmlToTextHelper htmlToTextHelper = new HtmlToTextHelper();
if (string.IsNullOrWhiteSpace(keyword))
{
return(null);;
}
string noHtmlConent = htmlToTextHelper.Convert(keyword);
var list = segmenter.Cut(noHtmlConent, hmm: true);
var cutList = new List <string>();
foreach (var item in list)
{
if (item.Length > 1)
{
cutList.Add(item.ToUpper());
}
}
string str = string.Join(" & ", cutList);
vector = NpgsqlTsQuery.Parse(str);
}
catch (Exception ex)
{
return(null);
}
return(vector);
}
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 NpgsqlTsQuery GetSerachNpgsqlTsQuery_And(string[] keywords)
{
NpgsqlTsQuery vector;
try
{
var cutList = new List <string>();
foreach (var item in keywords)
{
if (item.Length > 1)
{
cutList.Add(item.ToUpper());
}
}
string str = string.Join(" & ", cutList);
vector = NpgsqlTsQuery.Parse(str);
}
catch (Exception ex)
{
return(null);
}
return(vector);
}
/// <summary> /// Returns a tsquery that searches for a match to <paramref name="query1" /> followed by a match /// to <paramref name="query2" />. /// http://www.postgresql.org/docs/current/static/textsearch-features.html#TEXTSEARCH-MANIPULATE-TSQUERY /// </summary> public static NpgsqlTsQuery ToPhrase(this NpgsqlTsQuery query1, NpgsqlTsQuery query2) => throw new InvalidOperationException(CoreStrings.FunctionOnClient(nameof(NpgsqlTsQuery) + "." + nameof(ToPhrase)));
/// <summary>
/// rum_ts_score(tsvector,tsquery)
/// </summary>
public static float Score(this NpgsqlTsVector vector, NpgsqlTsQuery query)
{
throw new NotSupportedException();
}
/// <summary>
/// Creates a not operator, with a given child node.
/// </summary>
/// <param name="child"></param>
public NpgsqlTsQueryNot([CanBeNull] NpgsqlTsQuery child)
{
Kind = NodeKind.Not;
Child = child;
}
/// <summary>
/// Searches <paramref name="query" /> for occurrences of <paramref name="target" />, and replaces
/// each occurrence with a <paramref name="substitute" />. All parameters are of type tsquery.
/// http://www.postgresql.org/docs/current/static/textsearch-features.html#TEXTSEARCH-MANIPULATE-TSQUERY
/// </summary>
public static NpgsqlTsQuery Rewrite(
[NotNull] this NpgsqlTsQuery query,
[NotNull] NpgsqlTsQuery target,
[NotNull] NpgsqlTsQuery substitute)
=> throw new InvalidOperationException(CoreStrings.FunctionOnClient(nameof(NpgsqlTsQuery) + "." + nameof(Rewrite)));
/// <summary>
/// Returns a string suitable for display containing a query match using the text
/// search configuration specified by <paramref name="config" />.
/// http://www.postgresql.org/docs/current/static/textsearch-controls.html#TEXTSEARCH-HEADLINE
/// </summary>
public static string GetResultHeadline(
[NotNull] this NpgsqlTsQuery query,
[NotNull] string config,
[NotNull] string document,
[NotNull] string options)
=> throw new InvalidOperationException(CoreStrings.FunctionOnClient(nameof(NpgsqlTsQuery) + "." + nameof(GetResultHeadline)));
/// <summary> /// Get the indexable part of <paramref name="query" />. /// http://www.postgresql.org/docs/current/static/textsearch-features.html#TEXTSEARCH-MANIPULATE-TSQUERY /// </summary> public static string GetQueryTree([NotNull] this NpgsqlTsQuery query) => throw new InvalidOperationException(CoreStrings.FunctionOnClient(nameof(NpgsqlTsQuery) + "." + nameof(GetQueryTree)));
public Task TsQuery(string sqlLiteral, NpgsqlTsQuery query) => AssertType(query, sqlLiteral, "tsquery", NpgsqlDbType.TsQuery);
/// <summary>
/// Calculates the rank of <paramref name="vector" /> for <paramref name="query" /> with custom
/// weighting for word instances depending on their labels (D, C, B or A).
/// http://www.postgresql.org/docs/current/static/textsearch-controls.html#TEXTSEARCH-RANKING
/// </summary>
public static float Rank(
[NotNull] this NpgsqlTsVector vector,
[NotNull] float[] weights,
[NotNull] NpgsqlTsQuery query)
=> throw new InvalidOperationException(CoreStrings.FunctionOnClient(nameof(NpgsqlTsVector) + "." + nameof(Rank)));
/// <summary> /// OR tsquerys together. Generates the "||" operator. /// http://www.postgresql.org/docs/current/static/textsearch-features.html#TEXTSEARCH-MANIPULATE-TSQUERY /// </summary> public static NpgsqlTsQuery Or([NotNull] this NpgsqlTsQuery query1, [NotNull] NpgsqlTsQuery query2) => throw new InvalidOperationException(CoreStrings.FunctionOnClient(nameof(NpgsqlTsQuery) + "." + nameof(Or)));
/// <summary> /// This method generates the "@@" match operator. /// http://www.postgresql.org/docs/current/static/textsearch-intro.html#TEXTSEARCH-MATCHING /// </summary> public static bool Matches([NotNull] this NpgsqlTsVector vector, [NotNull] NpgsqlTsQuery query) => throw new InvalidOperationException(CoreStrings.FunctionOnClient(nameof(NpgsqlTsVector) + "." + nameof(Matches)));
/// <summary>
/// Returns a tsquery that searches for a match to <paramref name="query1" /> followed by a match
/// to <paramref name="query2" /> at a distance of <paramref name="distance" /> lexemes using
/// the <N> tsquery operator
/// http://www.postgresql.org/docs/current/static/textsearch-features.html#TEXTSEARCH-MANIPULATE-TSQUERY
/// </summary>
public static NpgsqlTsQuery ToPhrase(
[NotNull] this NpgsqlTsQuery query1,
[NotNull] NpgsqlTsQuery query2,
int distance)
=> throw new InvalidOperationException(CoreStrings.FunctionOnClient(nameof(NpgsqlTsQuery) + "." + nameof(ToPhrase)));
/// <summary>
/// Creates an or operator, with two given child nodes.
/// </summary>
/// <param name="left"></param>
/// <param name="right"></param>
public NpgsqlTsQueryOr([CanBeNull] NpgsqlTsQuery left, [CanBeNull] NpgsqlTsQuery right)
{
Kind = NodeKind.Or;
Left = left;
Right = right;
}
/// <summary> /// Returns a string suitable for display containing a query match. /// http://www.postgresql.org/docs/current/static/textsearch-controls.html#TEXTSEARCH-HEADLINE /// </summary> public static string GetResultHeadline(this NpgsqlTsQuery query, string document) => throw new InvalidOperationException(CoreStrings.FunctionOnClient(nameof(NpgsqlTsQuery) + "." + nameof(GetResultHeadline)));
/// <summary>
/// Calculates the rank of <paramref name="vector" /> for <paramref name="query" /> using the cover density
/// method while normalizing the result according to the behaviors specified by <paramref name="normalization" />
/// and using custom weighting for word instances depending on their labels (D, C, B or A).
/// http://www.postgresql.org/docs/current/static/textsearch-controls.html#TEXTSEARCH-RANKING
/// </summary>
public static float RankCoverDensity(
[NotNull] this NpgsqlTsVector vector,
[NotNull] float[] weights,
[NotNull] NpgsqlTsQuery query,
NpgsqlTsRankingNormalization normalization)
=> throw new InvalidOperationException(CoreStrings.FunctionOnClient(nameof(NpgsqlTsVector) + "." + nameof(RankCoverDensity)));
/// <summary> /// Returns whether <paramref name="query1" /> is contained within <paramref name="query2" />. /// Generates the "<@" operator. /// http://www.postgresql.org/docs/current/static/functions-textsearch.html /// </summary> public static bool IsContainedIn([NotNull] this NpgsqlTsQuery query1, [NotNull] NpgsqlTsQuery query2) => throw new InvalidOperationException(CoreStrings.FunctionOnClient(nameof(NpgsqlTsQuery) + "." + nameof(IsContainedIn)));
