linq2db · MaceWindu · Apr 13, 2019 · Feb 17, 2019 · Feb 20, 2019 · Feb 20, 2019
diff --git a/Data/Create Scripts/MySql.sql b/Data/Create Scripts/MySql.sql
@@ -458,8 +458,11 @@ GO
 
 CREATE TABLE FullTextIndexTest (
 	id int UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-	TestField TEXT(100),
-	FULLTEXT idx (TestField)
+	TestField1 TEXT(100),
+	TestField2 TEXT(200),
+	FULLTEXT idx_all (TestField1, TestField2),
+	FULLTEXT idx_field1 (TestField1),
+	FULLTEXT idx_field2 (TestField2)
 )
 -- SKIP MySql57 BEGIN
 -- SKIP MariaDB BEGIN
@@ -470,3 +473,7 @@ CREATE TABLE FullTextIndexTest (
 -- SKIP MySqlConnector END
 ;
 GO
+INSERT INTO FullTextIndexTest(TestField1, TestField2) VALUES('this is text1', 'this is text2');
+INSERT INTO FullTextIndexTest(TestField1, TestField2) VALUES('looking for something?', 'found it!');
+INSERT INTO FullTextIndexTest(TestField1, TestField2) VALUES('record not found', 'empty');
+GO
diff --git a/Data/Create Scripts/Northwind.sql b/Data/Create Scripts/Northwind.sql
diff --git a/Data/Create Scripts/SQLite.sql b/Data/Create Scripts/SQLite.sql
@@ -274,3 +274,21 @@ CREATE TABLE TEST_T4_CASING
 	snake_case            INT    NOT NULL,
 	camelCase             INT    NOT NULL
 );
+
+DROP TABLE IF EXISTS FTS3_TABLE;
+CREATE VIRTUAL TABLE FTS3_TABLE USING FTS3(text1 TEXT, text2 TEXT);
+
+DROP TABLE IF EXISTS FTS4_TABLE;
+CREATE VIRTUAL TABLE FTS4_TABLE USING FTS4(text1 TEXT, text2 TEXT);
+
+INSERT INTO FTS3_TABLE(text1, text2) VALUES('this is text1', 'this is text2');
+INSERT INTO FTS3_TABLE(text1, text2) VALUES('looking for something?', 'found it!');
+INSERT INTO FTS3_TABLE(text1, text2) VALUES('record not found', 'empty');
+INSERT INTO FTS3_TABLE(text1, text2) VALUES('for snippet testing', 'During 30 Nov-1 Dec, 2-3oC drops. Cool in the upper portion, minimum temperature 14-16oC and cool elsewhere, minimum temperature 17-20oC. Cold to very cold on mountaintops, minimum temperature 6-12oC. Northeasterly winds 15-30 km/hr. After that, temperature increases. Northeasterly winds 15-30 km/hr.');
+
+INSERT INTO FTS4_TABLE(text1, text2) VALUES('this is text1', 'this is text2');
+INSERT INTO FTS4_TABLE(text1, text2) VALUES('looking for something?', 'found it!');
+INSERT INTO FTS4_TABLE(text1, text2) VALUES('record not found', 'empty');
+INSERT INTO FTS4_TABLE(text1, text2) VALUES('for snippet testing', 'During 30 Nov-1 Dec, 2-3oC drops. Cool in the upper portion, minimum temperature 14-16oC and cool elsewhere, minimum temperature 17-20oC. Cold to very cold on mountaintops, minimum temperature 6-12oC. Northeasterly winds 15-30 km/hr. After that, temperature increases. Northeasterly winds 15-30 km/hr.');
+
+
@@ -21,7 +21,7 @@
 	}
 #>
 <#+
-bool GenerateSqlServerFreeText = true; // Defines whether to generate extensions for Free Text search, or not
+bool GenerateSqlServerFreeText = false; // Defines whether to generate extensions for Free Text search, or not
 
 void DoGenerateSqlServerFreeText()
 {

diff --git a/Source/LinqToDB.Templates/README.md b/Source/LinqToDB.Templates/README.md
@@ -231,7 +231,10 @@ Func<string, bool, string> ConvertToCompilable = ConvertToCompilableDefault;
 
 ```cs
 // Enables generation of extensions for Free Text Search
-bool GenerateSqlServerFreeText = true;
+//
+// NOTE: this option is not needed anymore, as it generates old-style FTS support code and not recommeded for use
+// use new extesions from this PR: https://github.com/linq2db/linq2db/pull/1649
+bool GenerateSqlServerFreeText = false;
 ```
 
 ### PostgreSQL

@@ -0,0 +1,119 @@
+using LinqToDB.Expressions;
+using LinqToDB.Linq;
+using System;
+
+namespace LinqToDB.DataProvider.MySql
+{
+	public interface IMySqlExtensions
+	{
+	}
+
+	public static class MySqlExtensions
+	{
+		public static IMySqlExtensions MySql(this Sql.ISqlExtension ext) => null;
+
+		#region FTS
+		class ModifierBuilder : Sql.IExtensionCallBuilder
+		{
+			public void Build(Sql.ISqExtensionBuilder builder)
+			{
+				var modifier = builder.GetValue<MatchModifier>("modifier");
+
+				switch (modifier)
+				{
+					case MatchModifier.NaturalLanguage:
+						// default modifier, no need to add it to SQL
+						break;
+					case MatchModifier.Boolean:
+						builder.AddExpression("modifier", " IN BOOLEAN MODE");
+						break;
+					case MatchModifier.WithQueryExpansion:
+						// use short form without 'IN NATURAL LANGUAGE MODE' prefix
+						builder.AddExpression("modifier", " WITH QUERY EXPANSION");
+						break;
+					default:
+						throw new ArgumentOutOfRangeException("modifier");
+				}
+			}
+		}
+
+		/// <summary>
+		/// Search modifier for MATCH AGAINST full-text search predicate.
+		/// </summary>
+		public enum MatchModifier
+		{
+			/// <summary>
+			/// Applies 'IN NATURAL LANGUAGE MODE' (default value) search modifier.
+			/// </summary>
+			NaturalLanguage,
+			/// <summary>
+			/// Applies 'IN BOOLEAN MODE' (default value) search modifier.
+			/// </summary>
+			Boolean,
+			/// <summary>
+			/// Applies 'IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION'/'WITH QUERY EXPANSION' search modifier.
+			/// </summary>
+			WithQueryExpansion
+		}
+
+		/// <summary>
+		/// Applies full-text search condition using MATCH AGAINST predicate against specified full-text columns using default mode (IN NATURAL LANGUAGE MODE).
+		/// Example: MATCH(col1, col2) AGAINST('search query').
+		/// </summary>
+		/// <param name="ext">Extension point.</param>
+		/// <param name="search">Full-text search condition.</param>
+		/// <param name="columns">Full-text columns that should be queried.</param>
+		/// <returns>Returns <c>true</c> if full-text search found matching records.</returns>
+		[Sql.Extension("MATCH({columns, ', '}) AGAINST ({search})", IsPredicate = true, ServerSideOnly = true)]
+		public static bool Match(this IMySqlExtensions ext, [ExprParameter("search")] string search, [ExprParameter("columns")] params object[] columns)
+		{
+			throw new LinqException($"'{nameof(Match)}' is server-side method.");
+		}
+
+		/// <summary>
+		/// Calculates relevance of full-text search for current record using MATCH AGAINST predicate against specified full-text columns using default mode (IN NATURAL LANGUAGE MODE).
+		/// Example: MATCH(col1, col2) AGAINST('search query').
+		/// </summary>
+		/// <param name="ext">Extension point.</param>
+		/// <param name="search">Full-text search condition.</param>
+		/// <param name="columns">Full-text columns that should be queried.</param>
+		/// <returns>Returns full-text search relevance value for current record.</returns>
+		[Sql.Extension("MATCH({columns, ', '}) AGAINST ({search})", ServerSideOnly = true)]
+		public static double MatchRelevance(this IMySqlExtensions ext, [ExprParameter("search")] string search, [ExprParameter("columns")] params object[] columns)
+		{
+			throw new LinqException($"'{nameof(MatchRelevance)}' is server-side method.");
+		}
+
+		/// <summary>
+		/// Applies full-text search condition using MATCH AGAINST predicate against specified full-text columns using specified search modifier.
+		/// Example: MATCH(col1, col2) AGAINST('search query' MODIFIER).
+		/// </summary>
+		/// <param name="ext">Extension point.</param>
+		/// <param name="modifier">Search modifier.</param>
+		/// <param name="search">Full-text search condition.</param>
+		/// <param name="columns">Full-text columns that should be queried.</param>
+		/// <returns>Returns <c>true</c> if full-text search found matching records.</returns>
+		[Sql.Extension("MATCH({columns, ', '}) AGAINST ({search}{modifier?})", IsPredicate = true, ServerSideOnly = true, BuilderType = typeof(ModifierBuilder))]
+		public static bool Match(this IMySqlExtensions ext, [SqlQueryDependent] MatchModifier modifier, [ExprParameter("search")] string search, [ExprParameter("columns")] params object[] columns)
+		{
+			throw new LinqException($"'{nameof(Match)}' is server-side method.");
+		}
+
+		/// <summary>
+		/// Calculates relevance of full-text search for current record using MATCH AGAINST predicate against specified full-text columns using specified search modifier.
+		/// Example: MATCH(col1, col2) AGAINST('search query' MODIFIER).
+		/// </summary>
+		/// <param name="ext">Extension point.</param>
+		/// <param name="modifier">Search modifier.</param>
+		/// <param name="search">Full-text search condition.</param>
+		/// <param name="columns">Full-text columns that should be queried.</param>
+		/// <returns>Returns full-text search relevance value for current record.</returns>
+		[Sql.Extension("MATCH({columns, ', '}) AGAINST ({search}{modifier?})", ServerSideOnly = true, BuilderType = typeof(ModifierBuilder))]
+		public static double MatchRelevance(this IMySqlExtensions ext, [SqlQueryDependent] MatchModifier modifier, [ExprParameter("search")] string search, [ExprParameter("columns")] params object[] columns)
+		{
+			throw new LinqException($"'{nameof(MatchRelevance)}' is server-side method.");
+		}
+
+		#endregion
+	}
+}
-Original file line number
+Diff line change
@@ Expand Up / @@ -21,7 +21,7 @@ @@
     	}
     #>
     <#+
-    bool GenerateSqlServerFreeText = true; // Defines whether to generate extensions for Free Text search, or not
+    bool GenerateSqlServerFreeText = false; // Defines whether to generate extensions for Free Text search, or not
     void DoGenerateSqlServerFreeText()
     {
@@ Expand Down @@