Code to add MySQL FullText functionality

Posts   
 
    
safehome
User
Posts: 19
Joined: 23-Jul-2007
# Posted on: 26-Jan-2008 06:06:57   

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

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 28-Jan-2008 03:09:00   

Thanks for the contribution Bill simple_smile

David Elizondo | LLBLGen Support Team
swallace
User
Posts: 648
Joined: 18-Aug-2003
# Posted on: 16-Jun-2008 19:42:22   

These bits of code are of great help to me. Has this functionality been added to the new 2.6 version, or do I need to continue to insert this?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 16-Jun-2008 20:47:06   

You need to continue to insert this. As we expect more and more people to go for Linq anyway, we left this for the user to add, but it's really easy. In the documentation there's an example how to add fulltextsearch for sqlserver for Linq, and I expect it's similarly easy for other databases.

Frans Bouma | Lead developer LLBLGen Pro
swallace
User
Posts: 648
Joined: 18-Aug-2003
# Posted on: 16-Jun-2008 21:08:30   

No troubles, thanks. Looking forward to upgrading.

swallace
User
Posts: 648
Joined: 18-Aug-2003
# Posted on: 16-Jun-2008 21:22:00   

Otis wrote:

You need to continue to insert this. As we expect more and more people to go for Linq anyway, we left this for the user to add, but it's really easy. In the documentation there's an example how to add fulltextsearch for sqlserver for Linq, and I expect it's similarly easy for other databases.

Yeah, baby! That new FunctionMapping is sweet! Much easier to implement fulltext, and perhaps other things. Nice work!

FYI, I had a friend once describe the difficulties an old SQL person can have getting their mind around the seemingly-backwards nature of from/where/select in Linq.

He said, as an experiment, type your name. Backwards.

You know your name, and have your whole life. You can type it without thinking. But backwards? Suddenly it's a struggle.

Such is Linq. Simple. Powerful. Easily learned, yet still a struggle.