/// <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(")"));
}
}
