/// <summary> /// Create SQL statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { if (parms.Length != 1) { throw new ArgumentException("Approx requires at least one parameter"); } var config = ApplicationServiceContext.Current.GetService <IConfigurationManager>().GetSection <ApproximateMatchingConfigurationSection>(); if (config == null) { config = new ApproximateMatchingConfigurationSection() { ApproxSearchOptions = new List <ApproxSearchOption>() { new ApproxPatternOption() { Enabled = true, IgnoreCase = true } } } } ; var filter = new SqlStatement(); foreach (var alg in config.ApproxSearchOptions.Where(o => o.Enabled)) { if (alg is ApproxDifferenceOption difference && m_hasSpellFix.GetValueOrDefault()) { filter.Or($"(length(trim({filterColumn})) > {difference.MaxDifference * 2} AND editdist3(TRIM(LOWER({filterColumn})), TRIM(LOWER(?))) <= {difference.MaxDifference})", QueryBuilder.CreateParameterValue(parms[0], typeof(String))); } else if (alg is ApproxPhoneticOption phonetic && m_hasSoundex.GetValueOrDefault()) { var min = phonetic.MinSimilarity; if (!phonetic.MinSimilaritySpecified) { min = 1.0f; } if (phonetic.Algorithm == ApproxPhoneticOption.PhoneticAlgorithmType.Soundex || phonetic.Algorithm == ApproxPhoneticOption.PhoneticAlgorithmType.Auto) { filter.Or($"((4 - editdist3(soundex({filterColumn}), soundex(?)))/4.0) >= {min}", QueryBuilder.CreateParameterValue(parms[0], typeof(String))); } else if (phonetic.Algorithm == ApproxPhoneticOption.PhoneticAlgorithmType.Metaphone) { filter.Or($"((length(spellfix1_phonehash({filterColumn})) - editdist3(spellfix1_phonehash({filterColumn}), spellfix1_phonehash(?)))/length(spellfix1_phonehash({filterColumn}))) >= {min}", QueryBuilder.CreateParameterValue(parms[0], typeof(String))); } else { throw new InvalidOperationException($"Phonetic algorithm {phonetic.Algorithm} is not valid"); } }
/// <summary> /// Creates the SQL statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { if (parms.Length != 1) { throw new ArgumentException("Approx requires at least one parameter"); } var config = ApplicationServiceContext.Current.GetService <IConfigurationManager>().GetSection <ApproximateMatchingConfigurationSection>(); if (config == null) { config = new ApproximateMatchingConfigurationSection() { ApproxSearchOptions = new List <ApproxSearchOption>() { new ApproxPatternOption() { Enabled = true, IgnoreCase = true } } } } ; var filter = new SqlStatement(current.DbProvider); foreach (var alg in config.ApproxSearchOptions.Where(o => o.Enabled)) { if (alg is ApproxPatternOption pattern) { if (pattern.IgnoreCase) { filter.Or($"LOWER({filterColumn}) like LOWER(?)", QueryBuilder.CreateParameterValue(parms[0].Replace("*", "%").Replace("?", "_"), typeof(String))); } else { filter.Or($"{filterColumn} like ?", QueryBuilder.CreateParameterValue(parms[0].Replace("*", "%").Replace("?", "_"), typeof(String))); } } } return(current.Append("(").Append(filter).Append(")")); } }
/// <summary> /// Install the specified extension /// </summary> public bool Install() { // Connected to sync var syncMode = ApplicationContext.Current.Configuration.GetSection <SynchronizationConfigurationSection>()?.Mode; if (!syncMode.HasValue || syncMode == SynchronizationMode.Online) { ApplicationContext.Current.RemoveServiceProvider(typeof(SimpleRecordMatchingService), true); ApplicationContext.Current.RemoveServiceProvider(typeof(FileMatchConfigurationProvider), true); ApplicationContext.Current.AddServiceProvider(typeof(RemoteRecordMatchConfigurationService), true); } else // user central server for checkout { ApplicationContext.Current.RemoveServiceProvider(typeof(RemoteRecordMatchConfigurationService), true); ApplicationContext.Current.AddServiceProvider(typeof(SimpleRecordMatchingService), true); ApplicationContext.Current.AddServiceProvider(typeof(FileMatchConfigurationProvider), true); // Setup the match configurations var fileConfig = ApplicationContext.Current.Configuration.GetSection <FileMatchConfigurationSection>(); if (fileConfig == null) { fileConfig = new FileMatchConfigurationSection { FilePath = new List <FilePathConfiguration> { new FilePathConfiguration { Path = Path.Combine(ApplicationContext.Current.ConfigurationPersister.ApplicationDataDirectory, "matching"), ReadOnly = false } } }; ApplicationContext.Current.Configuration.AddSection(fileConfig); } } // Setup the approx configuration var approxConfig = ApplicationContext.Current.Configuration.GetSection <ApproximateMatchingConfigurationSection>(); if (approxConfig == null) { approxConfig = new ApproximateMatchingConfigurationSection { ApproxSearchOptions = new List <ApproxSearchOption>() }; // Add pattern approxConfig.ApproxSearchOptions.Add(new ApproxPatternOption { Enabled = true, IgnoreCase = true }); // Add soundex as preferred approxConfig.ApproxSearchOptions.Add(new ApproxPhoneticOption { Enabled = true, Algorithm = ApproxPhoneticOption.PhoneticAlgorithmType.Auto, MinSimilarity = 1.0f, MinSimilaritySpecified = true }); // Add levenshtein approxConfig.ApproxSearchOptions.Add(new ApproxDifferenceOption { Enabled = true, MaxDifference = 1 }); ApplicationContext.Current.Configuration.AddSection(approxConfig); } return(true); }
/// <summary> /// Creates the SQL statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { if (parms.Length != 1) { throw new ArgumentException("Approx requires at least one parameter"); } var config = ApplicationServiceContext.Current.GetService <IConfigurationManager>().GetSection <ApproximateMatchingConfigurationSection>(); if (config == null) { config = new ApproximateMatchingConfigurationSection() { ApproxSearchOptions = new List <ApproxSearchOption>() { new ApproxPatternOption() { Enabled = true, IgnoreCase = true }, new ApproxPhoneticOption() { Enabled = true, Algorithm = ApproxPhoneticOption.PhoneticAlgorithmType.Metaphone }, new ApproxDifferenceOption() { Enabled = true, MaxDifference = 1 } } } } ; var filter = new SqlStatement(current.DbProvider); foreach (var alg in config.ApproxSearchOptions.Where(o => o.Enabled)) { if (alg is ApproxDifferenceOption difference) { filter.Or($"(length(trim({filterColumn})) > {difference.MaxDifference * 2} AND levenshtein(TRIM(LOWER({filterColumn})), TRIM(LOWER(?))) <= {difference.MaxDifference})", QueryBuilder.CreateParameterValue(parms[0], typeof(String))); } else if (alg is ApproxPhoneticOption phonetic) { var min = phonetic.MinSimilarity; if (!phonetic.MinSimilaritySpecified) { min = 1.0f; } if (phonetic.Algorithm == ApproxPhoneticOption.PhoneticAlgorithmType.Soundex) { filter.Or($"soundex({filterColumn}) = soundex(?)", QueryBuilder.CreateParameterValue(parms[0], typeof(String))); } else if (phonetic.Algorithm == ApproxPhoneticOption.PhoneticAlgorithmType.Metaphone) { filter.Or($"metaphone({filterColumn},4) = metaphone(?,4)", QueryBuilder.CreateParameterValue(parms[0], typeof(String))); } else if (phonetic.Algorithm == ApproxPhoneticOption.PhoneticAlgorithmType.DoubleMetaphone) { filter.Or($"dmetaphone({filterColumn}) = dmetaphone(?)", QueryBuilder.CreateParameterValue(parms[0], typeof(String))); } else { throw new InvalidOperationException($"Phonetic algorithm {phonetic.Algorithm} is not valid"); } } else if (alg is ApproxPatternOption pattern) { if (pattern.IgnoreCase) { filter.Or($"{filterColumn} ilike ?", QueryBuilder.CreateParameterValue(parms[0].Replace("*", "%").Replace("?", "_"), typeof(String))); } else { filter.Or($"{filterColumn} like ?", QueryBuilder.CreateParameterValue(parms[0].Replace("*", "%").Replace("?", "_"), typeof(String))); } } } return(current.Append("(").Append(filter).Append(")")); } }