Hi,
I'm running the latest v2.5 build of LLBLGen Pro. I have a project that would make great use of LLBLGen and FullText queries. However, we are using MySQL and LLBLGen only supports SQL Server for FullText. So I made a few minor changes to enable FullText predicates for MySQL. Please see the changes below to use the "MATCH" syntax for MySQL FullText searches:
1) ConstantEnums.cs - ORMSupportClasses
Added "Match" to FullTextSearchOperator
Here is new code:
/// <summary>
/// Operator to use with FieldFullTextSearchPredicate.
/// SqlServer and MySql specific.
/// </summary>
public enum FullTextSearchOperator:int
{
/// <summary>
/// Produces a CONTAINS() statement
/// </summary>
Contains,
/// <summary>
/// Produces a FREETEXT() statement
/// </summary>
Freetext,
/// <summary>
/// Produces a MySQL MATCH() statement
/// </summary>
Match
}
2) DataAccessAdapter.cs - (LLBLGen DatabaseSpecific generated code)
All I did was copy the "case FieldFullTextSearchPredicate" section from a SqlServer version of generated DataAccessAdapter code. Here is new code (which is identical to what LLBLGen Pro creates for SqlServer projects). Can you change LLBLGen to make the MySQL version of the DataAccessAdapter with this code? Since LLBLGen does not officially support MySQL FullText searches, I can't see any harm in generating this code for MySQL too. Even if your support for MySQL FullText is initially 'experimental', having LLBLGen create DatabaseSpecific projects with this code would save me copying and pasting, (or worse, forgetting to cut and paste) each time I generate classes with LLBLGen Pro.
case PredicateType.FieldFullTextSearchPredicate:
FieldFullTextSearchPredicate fullTextSearchPredicate = (FieldFullTextSearchPredicate)currentPredicate;
if (fullTextSearchPredicate.TargetIsFieldList)
{
if (fullTextSearchPredicate.PersistenceInfosFieldsList == null)
{
ArrayList persistenceInfos = new ArrayList();
foreach (IEntityField2 field in fullTextSearchPredicate.FieldsList)
{
persistenceInfos.Add(GetFieldPersistenceInfo(field));
}
fullTextSearchPredicate.PersistenceInfosFieldsList = persistenceInfos;
}
}
else
{
if (fullTextSearchPredicate.PersistenceInfo == null)
{
fullTextSearchPredicate.PersistenceInfo = GetFieldPersistenceInfo((IEntityField2)fullTextSearchPredicate.FieldCore);
}
}
break;
3) FieldFullTextSearchPredicate.cs - ORMSupportClasses
Change required to ToQueryText(ref int uniqueMarker, bool inHavingClause)
For background, syntax of SQL FullText queries:
Query: Contains ((TableName
.FieldName), @FieldsList2)
Multiple: {0}(({1}), {2})
Single: {0}({1}, {2})
Syntax of MySQL FullText queries (I'm assuming the "Boolean" mode of MySQL Fulltext queries):
Query: MATCH (TableName
.FieldName) AGAINST (@FieldsList2 in Boolean Mode)
Multiple: {0} ({1}) Against (({2}) in Boolean Mode)
Individual: {0} ({1}) Against ({2} in Boolean Mode)
The code below may not be elegant, but it supports CONTAINS and FREETEXT for SqlServer, and MATCH for MySQL:
string fullTextQueryFormat = "";
if (TargetIsFieldList)
{
StringBuilder targetBuilder = new StringBuilder();
for (int i = 0; i < _fieldsList.Count; i++)
{
if (i > 0)
{
targetBuilder.Append(", ");
}
IEntityFieldCore field = (IEntityFieldCore)_fieldsList[i];
targetBuilder.Append(
base.DatabaseSpecificCreator.CreateFieldName(field, (IFieldPersistenceInfo)_persistenceInfosFieldsList[i], field.Name, field.ObjectAlias, ref uniqueMarker, inHavingClause));
}
if (_operatorToUse == FullTextSearchOperator.Match)
fullTextQueryFormat = "{0} ({1}) Against ({2} in Boolean Mode)";
else
fullTextQueryFormat = "{0}(({1}), {2})";
queryText.AppendFormat(null, fullTextQueryFormat, _operatorToUse.ToString(), targetBuilder.ToString(), parameter.ParameterName);
}
else
{
if (_operatorToUse == FullTextSearchOperator.Match)
fullTextQueryFormat = "{0} ({1}) Against ({2} in Boolean Mode)";
else
fullTextQueryFormat = "{0}({1}, {2})";
queryText.AppendFormat(null, fullTextQueryFormat, _operatorToUse.ToString(),
base.DatabaseSpecificCreator.CreateFieldName(_field, _persistenceInfo, _field.Name, _objectAlias, ref uniqueMarker, inHavingClause),
parameter.ParameterName);
}
Thanks again for a great product!
-Bill