/// <summary>
/// Generates the WHERE section of the query for the <see cref="ISqlQueryBuilder"/> based on recursively processing the <see cref="ISqlQueryBuilder.RootFilterContainer"/>
/// </summary>
/// <param name="qb"></param>
/// <returns>WHERE block or empty string if there are no <see cref="IContainer"/></returns>
public static string GetWHERESQL(ISqlQueryBuilder qb)
{
string toReturn = "";
//if the root filter container is disabled don't render it
if (!IsEnabled(qb.RootFilterContainer))
{
return("");
}
var emptyFilters = qb.Filters.Where(f => string.IsNullOrWhiteSpace(f.WhereSQL)).ToArray();
if (emptyFilters.Any())
{
throw new QueryBuildingException("The following empty filters were found in the query:" + Environment.NewLine + string.Join(Environment.NewLine, emptyFilters.Select(f => f.Name)));
}
//recursively iterate the filter containers joining them up with their operation (AND or OR) and doing tab indentation etc
if (qb.Filters.Any())
{
string filtersSql = WriteContainerTreeRecursively(toReturn, 0, qb.RootFilterContainer, qb);
if (!string.IsNullOrWhiteSpace(filtersSql))
{
toReturn += Environment.NewLine;
toReturn += "WHERE" + Environment.NewLine;
toReturn += filtersSql;
}
}
return(toReturn);
}
public QueryCsvWriter(IAppServiceProvider provider, IDataContext dataContext, SqlQuery query, StreamWriter writer)
{
Provider = provider;
DataContext = dataContext;
Query = query;
Writer = writer;
_sqlQueryBuilder = provider.Get <ISqlQueryBuilder>();
}
public CensusRepository(string connectionString)
{
_connectionString = connectionString ??
throw new ArgumentNullException(nameof(connectionString));
_connection = new MySqlConnection(_connectionString);
_filter = new StandardTextFilter();
_queryBuilder = new MySqlQueryBuilder(_filter);
}
private static bool TableIsLookupTable(TableInfo tableInfo, ISqlQueryBuilder qb)
{
return
//tables where there is any columns which
(qb.SelectColumns.Any(
//are lookup descriptions and belong to this table
c => c.IsLookupDescription && c.UnderlyingColumn.TableInfo_ID == tableInfo.ID));
}
public Db(IDbConnectionProvider dbConnectionProvider, IDbValueConverter dbValueConverter,
IDataReaderToPoco dataReaderToPoco, ISqlQueryBuilder sqlQueryBuilder)
{
_dbConnectionProvider =
dbConnectionProvider ?? throw new ArgumentNullException(nameof(dbConnectionProvider));
_dbValueConverter = dbValueConverter ?? throw new ArgumentNullException(nameof(dbValueConverter));
_dataReaderToPoco = dataReaderToPoco ?? throw new ArgumentNullException(nameof(dataReaderToPoco));
_sqlQueryBuilder = sqlQueryBuilder ?? throw new ArgumentNullException(nameof(sqlQueryBuilder));
}
public void CreateTables()
{
Database.Execute(ISqlQueryBuilder.CreateKeyTable());
Database.Execute(ISqlQueryBuilder.CreateAttributeTable());
Database.Execute(ISqlQueryBuilder.CreateAttributeChoiceTable());
Database.Execute(ISqlQueryBuilder.CreateSpeciesTable());
Database.Execute(ISqlQueryBuilder.CreateSpeciesAttributeValueTable());
Database.Execute(ISqlQueryBuilder.CreateSpeciesSizeAttributeValueTable());
}
/// <summary>
/// Removes the SELECT TOP X logic from the supplied <see cref="ISqlQueryBuilder"/>
/// </summary>
/// <param name="queryBuilder"></param>
public static void ClearTopX(ISqlQueryBuilder queryBuilder)
{
//if we have a lingering custom line from last time
if (queryBuilder.TopXCustomLine != null)
{
queryBuilder.CustomLines.Remove(queryBuilder.TopXCustomLine); //remove it
queryBuilder.SQLOutOfDate = true;
}
}
public InterpretationResult Visit(ISqlQueryBuilder sqlQueryBuilder, Expression node)
{
this.sqlQueryBuilder = sqlQueryBuilder;
Visit(node);
return(new InterpretationResult
{
SqlQuery = this.sqlQueryBuilder.GetSqlQuery()
});
}
public QueryCsvWriter(IAppServiceProvider provider, IDataContext dataContext, QueryDef queryDef, StreamWriter writer, Guid userId)
{
Provider = provider;
DataContext = dataContext;
QueryDef = queryDef;
Writer = writer;
UserId = userId;
_sqlQueryBuilder = provider.Get <ISqlQueryBuilder>();
}
/// <inheritdoc cref="QueryTimeColumn.SetLookupStatus"/>
public static void FindLookups(ISqlQueryBuilder qb)
{
//if there is only one table then user us selecting stuff from the lookup table only
if (qb.TablesUsedInQuery.Count == 1)
{
return;
}
QueryTimeColumn.SetLookupStatus(qb.SelectColumns.ToArray(), qb.TablesUsedInQuery);
}
/// <summary>
/// Applies <paramref name="topX"/> to the <see cref="ISqlQueryBuilder"/> as a <see cref="CustomLine"/> based on the database engine syntax e.g. LIMIT vs TOP
/// and puts in in the correct location in the query (<see cref="QueryComponent"/>)
/// </summary>
/// <param name="queryBuilder"></param>
/// <param name="syntaxHelper"></param>
/// <param name="topX"></param>
public static void HandleTopX(ISqlQueryBuilder queryBuilder, IQuerySyntaxHelper syntaxHelper, int topX)
{
//if we have a lingering custom line from last time
ClearTopX(queryBuilder);
//if we are expected to have a topx
var response = syntaxHelper.HowDoWeAchieveTopX(topX);
queryBuilder.TopXCustomLine = AddCustomLine(queryBuilder, response.SQL, response.Location);
queryBuilder.TopXCustomLine.Role = CustomLineRole.TopX;
}
public MsSql2014Factory(string connectionString, IEntityMapper entityMapper = null)
{
_dbConnectionProvider = new InMemoryDbConnectionProvider();
_dbConnectionProvider.AddConnectionFactory("default", new MsSql2014ConnectionMaker(connectionString));
_dbValueConverter = new StrategiesDbValueConverter();
_entityDatabaseMapProvider =
new EntityDatabaseMapProvider(entityMapper ?? new DirectPropertyEntityMapper());
_dataReaderToPoco = new DataReaderToPoco(_entityDatabaseMapProvider);
ISqlQueryCutter sqlQueryCutter = new SqlQueryCutter();
_sqlQueryBuilder = new MsSql2014SqlQueryBuilder(_entityDatabaseMapProvider, sqlQueryCutter);
}
/// <summary>
/// Returns all <see cref="Lookup"/> linked to for the FROM section of the query
/// </summary>
/// <param name="qb"></param>
/// <returns></returns>
public static IEnumerable <Lookup> GetDistinctRequiredLookups(ISqlQueryBuilder qb)
{
//from all columns
return(from column in qb.SelectColumns
where
(
column.IsLookupForeignKey
&&
column.IsLookupForeignKeyActuallyUsed(qb.SelectColumns)
)
||
column.IsIsolatedLookupDescription //this is when there are no foreign key columns in the SelectedColumns set but there is still a lookup description field so we have to link to the table anyway
select column.LookupTable);
}
/// <summary>
/// Returns all <see cref="CustomLine"/> declared in <see cref="ISqlQueryBuilder.CustomLines"/> for the given stage but also adds some new ones to ensure valid syntax (for example
/// adding the word WHERE/AND depending on whether there is an existing <see cref="ISqlQueryBuilder.RootFilterContainer"/>.
/// </summary>
/// <param name="queryBuilder"></param>
/// <param name="stage"></param>
/// <returns></returns>
public static IEnumerable <CustomLine> GetCustomLinesSQLForStage(ISqlQueryBuilder queryBuilder, QueryComponent stage)
{
var lines = queryBuilder.CustomLines.Where(c => c.LocationToInsert == stage).ToArray();
if (!lines.Any())//no lines
{
yield break;
}
//Custom Filters (for people who can't be bothered to implement IFilter or when IContainer doesnt support ramming in additional Filters at runtime because you feel like it ) - these all get AND together and a WHERE is put at the start if needed
//if there are custom lines being rammed into the Filter section
if (stage == QueryComponent.WHERE)
{
//if we haven't put a WHERE yet, put one in
if (queryBuilder.Filters.Count == 0)
{
yield return(new CustomLine("WHERE", QueryComponent.WHERE));
}
else
{
yield return(new CustomLine("AND", QueryComponent.WHERE)); //otherwise just AND it with every other filter we currently have configured
}
//add user custom Filter lines
for (int i = 0; i < lines.Count(); i++)
{
yield return(lines[i]);
if (i + 1 < lines.Count())
{
yield return(new CustomLine("AND", QueryComponent.WHERE));
}
}
yield break;
}
//not a custom filter (which requires ANDing - see above) so this is the rest of the cases
foreach (CustomLine line in lines)
{
yield return(line);
}
}
/// <summary>
/// Add a custom line of code into the query at the specified position. This will be maintained throughout the lifespan of the object such that if
/// you add other columns etc then your code will still be included at the appropriate position.
/// </summary>
/// <param name="builder"></param>
/// <param name="text"></param>
/// <param name="positionToInsert"></param>
public static CustomLine AddCustomLine(ISqlQueryBuilder builder, string text, QueryComponent positionToInsert)
{
CustomLine toAdd = new CustomLine(text, positionToInsert);
if (positionToInsert == QueryComponent.GroupBy || positionToInsert == QueryComponent.OrderBy || positionToInsert == QueryComponent.FROM || positionToInsert == QueryComponent.Having)
{
throw new QueryBuildingException("Cannot inject custom lines into QueryBuilders at location " + positionToInsert);
}
if (positionToInsert == QueryComponent.WHERE)
{
if (text.Trim().StartsWith("AND ") || text.Trim().StartsWith("OR "))
{
throw new Exception("Custom filters are always AND, you should not specify the operator AND/OR, you passed\"" + text + "\"");
}
}
builder.CustomLines.Add(toAdd);
return(toAdd);
}
public DataServiceV2(string databaseConnectionString, ISqlQueryBuilder sqlQueryBuilder, IResultTransformer resultTransformer)
{
if (String.IsNullOrWhiteSpace(databaseConnectionString))
{
throw new ArgumentException("Argument is null or whitespace", nameof(databaseConnectionString));
}
if (sqlQueryBuilder == null)
{
throw new ArgumentNullException(nameof(sqlQueryBuilder));
}
if (resultTransformer == null)
{
throw new ArgumentNullException(nameof(resultTransformer));
}
_connectionString = databaseConnectionString;
_sqlQueryBuilder = sqlQueryBuilder;
_resultTransformer = resultTransformer;
}
public override void Initialize(string name, NameValueCollection config)
{
Condition.Requires(name, "name").IsNotNullOrWhiteSpace();
Condition.Requires(config, "config").IsNotNull();
var membershipProviderName = config.GetString("membershipProviderName");
if (!string.IsNullOrWhiteSpace(membershipProviderName))
{
this.membershipProvider = this.membershipProviders[membershipProviderName] as BetterMembershipProvider;
}
if (this.membershipProvider == null)
{
throw new ProviderException("membershipProviderName is required");
}
config.Remove("membershipProviderName");
base.Initialize(name, config);
var providerName = string.Empty;
var connectionString = ConfigurationManager.ConnectionStrings[this.membershipProvider.ConnectionStringName];
if (connectionString != null)
{
providerName = connectionString.ProviderName;
}
this.sqlQueryBuilder = this.sqlQueryBuilderFactory(
providerName,
this.membershipProvider.UserTableName,
this.membershipProvider.UserIdColumn,
this.membershipProvider.UserNameColumn,
this.membershipProvider.UserEmailColumn);
}
public AuthResourceProvider(ISqlQueryBuilder <AuthResource> queryBuilder, ISqlConnectionProvider connectionProvider)
: base(queryBuilder, connectionProvider)
{
_connectionProvider = connectionProvider;
_queryBuilder = queryBuilder;
}
public override void Initialize(string name, NameValueCollection config)
{
Condition.Requires(name, "name").IsNotNullOrWhiteSpace();
Condition.Requires(config, "config").IsNotNull();
if (config.ContainsKey("requiresQuestionAndAnswer"))
{
throw new ProviderException("unrecognized attribute requiresQuestionAndAnswer");
}
if (config.ContainsKey("enablePasswordRetrieval"))
{
throw new ProviderException("unrecognized attribute enablePasswordRetrieval");
}
if (config.ContainsKey("enablePasswordReset"))
{
throw new ProviderException("unrecognized attribute enablePasswordReset");
}
if (config.ContainsKey("passwordFormat"))
{
throw new ProviderException("unrecognized attribute passwordFormat");
}
this.connectionStringName = config.GetString("connectionStringName", "DefaultConnection");
this.userTableName = config.GetString("userTableName", "UserProfile");
this.userIdColumn = config.GetString("userIdColumn", "UserId");
this.userNameColumn = config.GetString("userNameColumn", "UserName");
this.userEmailColumn = config.GetString("userEmailColumn");
this.autoCreateTables = config.GetBoolean("autoCreateTables", true);
this.autoInitialize = config.GetBoolean("autoInitialize", true);
this.maxInvalidPasswordAttempts = config.GetInteger("maxInvalidPasswordAttempts", int.MaxValue);
this.minRequiredNonalphanumericCharacters = config.GetInteger("minRequiredNonalphanumericCharacters");
this.minRequiredPasswordLength = config.GetInteger("minRequiredPasswordLength", 1);
this.requiresUniqueEmail = config.GetBoolean("requiresUniqueEmail");
this.maxEmailLength = config.GetInteger("maxEmailLength", 254);
this.maxUserNameLength = config.GetInteger("maxUserNameLength", 56);
this.maxPasswordLength = config.GetInteger("maxPasswordLength", 128);
this.emailStrengthRegularExpression = config.GetString(
"emailStrengthRegularExpression", @"^[0-9a-zA-Z.+_-]+@[0-9a-zA-Z.+_-]+\.[a-zA-Z]{2,4}$");
this.userNameRegularExpression = config.GetString("userNameRegularExpression", @"^[0-9a-zA-Z_-]+$");
this.ApplicationName = config.GetString("applicationName", "/");
this.allowEmailAsUserName = config.GetBoolean("allowEmailAsUserName", true);
try
{
new Regex(this.emailStrengthRegularExpression);
}
catch (ArgumentException e)
{
throw new ProviderException("invalid value for emailStrengthRegularExpression", e);
}
try
{
new Regex(this.userNameRegularExpression);
}
catch (ArgumentException e)
{
throw new ProviderException("invalid value for userNameRegularExpression", e);
}
if (config.ContainsKey("passwordAttemptWindowInSeconds") && config.ContainsKey("passwordAttemptWindow"))
{
throw new ProviderException(
"passwordAttemptWindowInSeconds and passwordAttemptWindow cannot both be set");
}
if (config.ContainsKey("passwordAttemptWindowInSeconds"))
{
this.passwordAttemptWindowInSeconds = config.GetInteger("passwordAttemptWindowInSeconds", int.MaxValue);
}
else
{
var passwordAttemptWindowInMinutes = config.GetInteger("passwordAttemptWindow", -1);
if (passwordAttemptWindowInMinutes < 0)
{
this.passwordAttemptWindowInSeconds = int.MaxValue;
}
else
{
this.passwordAttemptWindowInSeconds = passwordAttemptWindowInMinutes * 60;
}
}
if (this.requiresUniqueEmail && !this.HasEmailColumnDefined)
{
throw new ProviderException("requiresUniqueEmail cannot be defined without userEmailColumn");
}
config.Remove("userTableName");
config.Remove("userIdColumn");
config.Remove("userNameColumn");
config.Remove("userEmailColumn");
config.Remove("autoCreateTables");
config.Remove("autoInitialize");
config.Remove("passwordAttemptWindow");
config.Remove("passwordAttemptWindowInSeconds");
config.Remove("maxEmailLength");
config.Remove("maxUserNameLength");
config.Remove("maxPasswordLength");
config.Remove("emailStrengthRegularExpression");
config.Remove("userNameRegularExpression");
config.Remove("allowEmailAsUserName");
var providerName = string.Empty;
var connectionString = ConfigurationManager.ConnectionStrings[this.ConnectionStringName];
if (connectionString != null)
{
providerName = connectionString.ProviderName;
}
this.sqlQueryBuilder = this.sqlQueryBuilderFactory(
providerName, this.userTableName, this.userIdColumn, this.userNameColumn, this.userEmailColumn);
base.Initialize(name, config);
if (this.AutoInitialize)
{
this.InitializeDatabaseConnection();
if (this.HasEmailColumnDefined)
{
this.CreateUserEmailColumn();
}
}
}
public InitQueryState(ISqlQueryBuilder sqlQueryBuilder, QeuryStateContext context)
{
this.sqlQueryBuilder = sqlQueryBuilder;
this.context = context;
}
/// <summary>
/// Must be called only after the ISqlQueryBuilder.TablesUsedInQuery has been set (see GetTablesUsedInQuery). This method will resolve how
/// the various tables can be linked together. Throws QueryBuildingException if it is not possible to join the tables with any known
/// JoinInfos / Lookup knowledge
/// </summary>
/// <param name="qb"></param>
/// <returns></returns>
public static List <JoinInfo> FindRequiredJoins(ISqlQueryBuilder qb)
{
List <JoinInfo> Joins = new List <JoinInfo>();
if (qb.TablesUsedInQuery == null)
{
throw new NullReferenceException("You must populate TablesUsedInQuery before calling FindRequiredJoins, try calling GetTablesUsedInQuery");
}
//there are no tables so how could there be any joins!
if (!qb.TablesUsedInQuery.Any())
{
throw new QueryBuildingException("Query has no TableInfos! Make sure your query has at least one column with an underlying ColumnInfo / TableInfo set - possibly you have deleted the TableInfo? this would result in orphan CatalogueItem");
}
ICatalogueRepository cataRepository;
try
{
cataRepository = (ICatalogueRepository)qb.TablesUsedInQuery.Select(t => t.Repository).Distinct().Single();
}
catch (Exception e)
{
throw new Exception("Tables (" + string.Join(",", qb.TablesUsedInQuery) + ") do not seem to come from the same repository", e);
}
foreach (TableInfo table1 in qb.TablesUsedInQuery)
{
foreach (TableInfo table2 in qb.TablesUsedInQuery)
{
if (table1.ID != table2.ID) //each table must join with a single other table
{
//figure out which of the users columns is from table 1 to join using
JoinInfo[] availableJoins = cataRepository.JoinManager.GetAllJoinInfosBetweenColumnInfoSets(
table1.ColumnInfos.ToArray(),
table2.ColumnInfos.ToArray());
if (availableJoins.Length == 0)
{
continue; //try another table
}
bool comboJoinResolved = false;
//if there are more than 1 join info between the two tables then we need to either do a combo join or complain to user
if (availableJoins.Length > 1)
{
string additionalErrorMessageWhyWeCantDoComboJoin = "";
//if there are multiple joins but they all join between the same 2 tables in the same direction
if (availableJoins.Select(j => j.PrimaryKey.TableInfo_ID).Distinct().Count() == 1
&&
availableJoins.Select(j => j.ForeignKey.TableInfo_ID).Distinct().Count() == 1)
{
if (availableJoins.Select(j => j.ExtractionJoinType).Distinct().Count() == 1)
{
//add as combo join
for (int i = 1; i < availableJoins.Length; i++)
{
availableJoins[0].AddQueryBuildingTimeComboJoinDiscovery(availableJoins[i]);
}
comboJoinResolved = true;
}
else
{
additionalErrorMessageWhyWeCantDoComboJoin =
" Although joins are all between the same tables in the same direction, the ExtractionJoinTypes are different (e.g. LEFT and RIGHT) which prevents forming a Combo AND based join using both relationships";
}
}
else
{
additionalErrorMessageWhyWeCantDoComboJoin =
" The Joins do not go in the same direction e.g. Table1.FK=>Table=2.PK and then a reverse relationship Table2.FK=>Table1.PK, in this case the system cannot do a Combo AND based join";
}
string possibleJoinsWere = availableJoins.Select(s => "JoinInfo[" + s.ToString() + "]").Aggregate((a, b) => a + Environment.NewLine + b);
if (!comboJoinResolved)
{
throw new QueryBuildingException("Found " + availableJoins.Length + " possible Joins for " + table1.Name +
" and " + table2.Name + ", did not know which to use. Available joins were:" + Environment.NewLine + possibleJoinsWere +
Environment.NewLine + " It was not possible to configure a Composite Join because:" + Environment.NewLine + additionalErrorMessageWhyWeCantDoComboJoin);
}
}
if (!Joins.Contains(availableJoins[0]))
{
Joins.Add(availableJoins[0]);
}
}
}
}
if (qb.TablesUsedInQuery.Count - GetDistinctRequiredLookups(qb).Count() - Joins.Count > 1)
{
throw new QueryBuildingException("There were " + qb.TablesUsedInQuery.Count + " Tables involved in assembling this query ( " + qb.TablesUsedInQuery.Aggregate("", (s, n) => s + n + ",").TrimEnd(',') + ") of which " + GetDistinctRequiredLookups(qb).Count() + " were Lookups and " + Joins.Count + " were JoinInfos, this leaves 2+ tables unjoined (no JoinInfo found)");
}
//make sure there are not multiple primary key tables (those should be configured as lookups
if (Joins.Count > 0 && qb.PrimaryExtractionTable == null)
{
List <string> primaryKeyTables = new List <string>(Joins.Select(p => p.PrimaryKey.TableInfo.Name).Distinct());
if (primaryKeyTables.Count > 1)
{
//there are multiple primary key tables... see if we are configured to support them
string primaryKeyTablesAsString = primaryKeyTables.Aggregate((a, b) => a + "," + b);
throw new QueryBuildingException("Found " + primaryKeyTables.Count + " primary key tables but PrimaryExtractionTable (Fix this by setting one TableInfo as 'IsPrimaryExtractionTable'), primary key tables identified include: " + primaryKeyTablesAsString);
}
}
if (qb.PrimaryExtractionTable != null && qb.TablesUsedInQuery.Contains(qb.PrimaryExtractionTable) == false)
{
throw new QueryBuildingException("Specified PrimaryExtractionTable was not found amongst the chosen extraction columns");
}
return(Joins);
}
public ClientProvider(ISqlQueryBuilder <Client> queryBuilder, ISqlConnectionProvider connectionProvider)
: base(queryBuilder, connectionProvider)
{
_connectionProvider = connectionProvider;
_queryBuilder = queryBuilder;
}
public override void Initialize(string name, NameValueCollection config)
{
Condition.Requires(name, "name").IsNotNullOrWhiteSpace();
Condition.Requires(config, "config").IsNotNull();
if (config.ContainsKey("requiresQuestionAndAnswer"))
{
throw new ProviderException("unrecognized attribute requiresQuestionAndAnswer");
}
if (config.ContainsKey("enablePasswordRetrieval"))
{
throw new ProviderException("unrecognized attribute enablePasswordRetrieval");
}
if (config.ContainsKey("enablePasswordReset"))
{
throw new ProviderException("unrecognized attribute enablePasswordReset");
}
if (config.ContainsKey("passwordFormat"))
{
throw new ProviderException("unrecognized attribute passwordFormat");
}
this.connectionStringName = config.GetString("connectionStringName", "DefaultConnection");
this.userTableName = config.GetString("userTableName", "UserProfile");
this.userIdColumn = config.GetString("userIdColumn", "UserId");
this.userNameColumn = config.GetString("userNameColumn", "UserName");
this.userEmailColumn = config.GetString("userEmailColumn");
this.autoCreateTables = config.GetBoolean("autoCreateTables", true);
this.autoInitialize = config.GetBoolean("autoInitialize", true);
this.maxInvalidPasswordAttempts = config.GetInteger("maxInvalidPasswordAttempts", int.MaxValue);
this.minRequiredNonalphanumericCharacters = config.GetInteger("minRequiredNonalphanumericCharacters");
this.minRequiredPasswordLength = config.GetInteger("minRequiredPasswordLength", 1);
this.requiresUniqueEmail = config.GetBoolean("requiresUniqueEmail");
this.maxEmailLength = config.GetInteger("maxEmailLength", 254);
this.maxUserNameLength = config.GetInteger("maxUserNameLength", 56);
this.maxPasswordLength = config.GetInteger("maxPasswordLength", 128);
this.emailStrengthRegularExpression = config.GetString(
"emailStrengthRegularExpression", @"^[0-9a-zA-Z.+_-]+@[0-9a-zA-Z.+_-]+\.[a-zA-Z]{2,4}$");
this.userNameRegularExpression = config.GetString("userNameRegularExpression", @"^[0-9a-zA-Z_-]+$");
this.ApplicationName = config.GetString("applicationName", "/");
this.allowEmailAsUserName = config.GetBoolean("allowEmailAsUserName", true);
try
{
new Regex(this.emailStrengthRegularExpression);
}
catch (ArgumentException e)
{
throw new ProviderException("invalid value for emailStrengthRegularExpression", e);
}
try
{
new Regex(this.userNameRegularExpression);
}
catch (ArgumentException e)
{
throw new ProviderException("invalid value for userNameRegularExpression", e);
}
if (config.ContainsKey("passwordAttemptWindowInSeconds") && config.ContainsKey("passwordAttemptWindow"))
{
throw new ProviderException(
"passwordAttemptWindowInSeconds and passwordAttemptWindow cannot both be set");
}
if (config.ContainsKey("passwordAttemptWindowInSeconds"))
{
this.passwordAttemptWindowInSeconds = config.GetInteger("passwordAttemptWindowInSeconds", int.MaxValue);
}
else
{
var passwordAttemptWindowInMinutes = config.GetInteger("passwordAttemptWindow", -1);
if (passwordAttemptWindowInMinutes < 0)
{
this.passwordAttemptWindowInSeconds = int.MaxValue;
}
else
{
this.passwordAttemptWindowInSeconds = passwordAttemptWindowInMinutes * 60;
}
}
if (this.requiresUniqueEmail && !this.HasEmailColumnDefined)
{
throw new ProviderException("requiresUniqueEmail cannot be defined without userEmailColumn");
}
config.Remove("userTableName");
config.Remove("userIdColumn");
config.Remove("userNameColumn");
config.Remove("userEmailColumn");
config.Remove("autoCreateTables");
config.Remove("autoInitialize");
config.Remove("passwordAttemptWindow");
config.Remove("passwordAttemptWindowInSeconds");
config.Remove("maxEmailLength");
config.Remove("maxUserNameLength");
config.Remove("maxPasswordLength");
config.Remove("emailStrengthRegularExpression");
config.Remove("userNameRegularExpression");
config.Remove("allowEmailAsUserName");
var providerName = string.Empty;
var connectionString = ConfigurationManager.ConnectionStrings[this.ConnectionStringName];
if (connectionString != null)
{
providerName = connectionString.ProviderName;
}
this.sqlQueryBuilder = this.sqlQueryBuilderFactory(
providerName, this.userTableName, this.userIdColumn, this.userNameColumn, this.userEmailColumn);
base.Initialize(name, config);
if (this.AutoInitialize)
{
this.InitializeDatabaseConnection();
if (this.HasEmailColumnDefined)
{
this.CreateUserEmailColumn();
}
}
}
/// <summary>
/// Searches the index with the specified query.
/// </summary>
/// <param name="query">The query text.</param>
/// <param name="options">The paging options.</param>
/// <returns>Page of results.</returns>
/// <exception cref="ArgumentNullException">query or options</exception>
public DataPage <ItemInfo> Search(string query, PagingOptions options)
{
if (query == null)
{
throw new ArgumentNullException(nameof(query));
}
if (options == null)
{
throw new ArgumentNullException(nameof(options));
}
if (_queryBuilder == null)
{
_queryBuilder = GetQueryBuilder();
}
if (_queryBuilder == null)
{
return(null);
}
var pageAndTot = _queryBuilder.Build(query, options);
if (Connection == null)
{
Connection = GetConnection();
Connection.Open();
}
DbCommand pageCommand = GetCommand();
#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities
pageCommand.CommandText = pageAndTot.Item1;
pageCommand.Connection = Connection;
DbCommand totCommand = GetCommand();
totCommand.CommandText = pageAndTot.Item2;
totCommand.Connection = Connection;
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities
List <ItemInfo> items = new List <ItemInfo>();
int total = Convert.ToInt32(totCommand.ExecuteScalar());
if (total == 0)
{
return(new DataPage <ItemInfo>(
options.PageNumber,
options.PageSize,
0,
items));
}
using (DbDataReader reader = pageCommand.ExecuteReader())
{
while (reader.Read())
{
ItemInfo item = new ItemInfo
{
// for some reason, the type from MySql is GUID here
Id = reader.GetValue(reader.GetOrdinal("id")).ToString(),
//Id = reader.GetFieldValue<string>(
// reader.GetOrdinal("id")),
Title = reader.GetFieldValue <string>(
reader.GetOrdinal("title")),
Description = reader.GetFieldValue <string>(
reader.GetOrdinal("description")),
FacetId = reader.GetFieldValue <string>(
reader.GetOrdinal("facetId")),
GroupId = reader.GetFieldValue <string>(
reader.GetOrdinal("groupId")),
SortKey = reader.GetFieldValue <string>(
reader.GetOrdinal("sortKey")),
Flags = reader.GetFieldValue <int>(
reader.GetOrdinal("flags")),
TimeCreated = reader.GetFieldValue <DateTime>(
reader.GetOrdinal("timeCreated")),
CreatorId = reader.GetFieldValue <string>(
reader.GetOrdinal("creatorId")),
TimeModified = reader.GetFieldValue <DateTime>(
reader.GetOrdinal("timeModified")),
UserId = reader.GetFieldValue <string>(
reader.GetOrdinal("userId"))
};
items.Add(item);
}
}
return(new DataPage <ItemInfo>(
options.PageNumber,
options.PageSize,
total, items));
}
/// <summary>
/// Make sure you have set your Filters and SelectColumns properties before calling this method so that it can find table dependencies
/// </summary>
/// <param name="qb"></param>
/// <param name="primaryExtractionTable"></param>
/// <param name="forceJoinsToTheseTables"></param>
/// <returns></returns>
public static List <TableInfo> GetTablesUsedInQuery(ISqlQueryBuilder qb, out TableInfo primaryExtractionTable, TableInfo[] forceJoinsToTheseTables = null)
{
if (qb.SelectColumns == null)
{
throw new QueryBuildingException("ISqlQueryBuilder.SelectedColumns is null");
}
if (qb.SelectColumns.Count == 0)
{
throw new QueryBuildingException("ISqlQueryBuilder.SelectedColumns is empty, use .AddColumn to add a column to the query builder");
}
List <TableInfo> toReturn = new List <TableInfo>(forceJoinsToTheseTables ?? new TableInfo[0]);
if (forceJoinsToTheseTables != null)
{
if (forceJoinsToTheseTables.Count(t => t.IsPrimaryExtractionTable) > 1)
{
throw new QueryBuildingException("Found 2+ tables marked IsPrimaryExtractionTable in force joined tables");
}
primaryExtractionTable = forceJoinsToTheseTables.SingleOrDefault(t => t.IsPrimaryExtractionTable);
}
else
{
primaryExtractionTable = null;
}
//get all the tables based on selected columns
foreach (QueryTimeColumn toExtract in qb.SelectColumns)
{
if (toExtract.UnderlyingColumn == null)
{
continue;
}
if (qb.CheckSyntax)
{
toExtract.CheckSyntax();
}
TableInfo table = toExtract.UnderlyingColumn.TableInfo;
if (!toReturn.Contains(table))
{
toReturn.Add(table);
if (table.IsPrimaryExtractionTable)
{
if (primaryExtractionTable == null)
{
primaryExtractionTable = table;
}
else
{
throw new QueryBuildingException("There are multiple tables marked as IsPrimaryExtractionTable:" +
qb.PrimaryExtractionTable.Name + "(ID=" + qb.PrimaryExtractionTable.ID +
") and " + table.Name + "(ID=" + table.ID + ")");
}
}
}
}
//get other tables we might need because they are referenced by filters
if (qb.Filters != null && qb.Filters.Any())
{
foreach (IFilter filter in qb.Filters)
{
ColumnInfo col = filter.GetColumnInfoIfExists();
if (col != null)
{
var tableInfoOfFilter = col.TableInfo;
if (!toReturn.Contains(tableInfoOfFilter))
{
toReturn.Add(tableInfoOfFilter);
}
}
}
toReturn = AddOpportunisticJoins(toReturn, qb.Filters);
}
//Some TableInfos might be TableValuedFunctions or for some other reason have a paramter associated with them
qb.ParameterManager.AddParametersFor(toReturn);
return(toReturn);
}
public void CreateSpeciesSizeAttributeValueTable()
{
Database.Execute(ISqlQueryBuilder.CreateSpeciesSizeAttributeValueTable());
}
public ObjectProvider(ISqlQueryBuilder <T> queryBuilder, ISqlConnectionProvider connectionProvider)
{
_queryBuilder = queryBuilder ?? throw new ArgumentNullException(nameof(queryBuilder));
_connectionProvider = connectionProvider ?? throw new ArgumentNullException(nameof(connectionProvider));
}
/// <summary>
/// Generates the FROM sql including joins for all the <see cref="TableInfo"/> required by the <see cref="ISqlQueryBuilder"/>. <see cref="JoinInfo"/> must exist for
/// this process to work
/// </summary>
/// <param name="qb"></param>
/// <returns></returns>
public static string GetFROMSQL(ISqlQueryBuilder qb)
{
//add the from bit
string toReturn = "FROM " + Environment.NewLine;
if (qb.TablesUsedInQuery.Count == 0)
{
throw new QueryBuildingException("There are no tables involved in the query: We were asked to compute the FROM SQL but qb.TablesUsedInQuery was of length 0");
}
if (qb.JoinsUsedInQuery.Count == 0)
{
TableInfo firstTable = null;
//is there only one table involved in the query?
if (qb.TablesUsedInQuery.Count == 1)
{
firstTable = qb.TablesUsedInQuery[0];
}
else if (qb.TablesUsedInQuery.Count(t => t.IsPrimaryExtractionTable) == 1) //has the user picked one to be primary?
{
firstTable = qb.TablesUsedInQuery.Single(t => t.IsPrimaryExtractionTable);
//has user tried to make a lookup table the primary table!
if (TableIsLookupTable(firstTable, qb))
{
throw new QueryBuildingException("Lookup tables cannot be marked IsPrimaryExtractionTable (Offender =" + firstTable + ")");
}
}
else
{
//User has not picked one and there are multiple!
//can we discard all tables but one based on the fact that they are look up tables?
//maybe! lookup tables are tables where there is an underlying column from that table that is a lookup description
var winners =
qb.TablesUsedInQuery.Where(t =>
!TableIsLookupTable(t, qb))
.ToArray();
//if we have discarded all but 1 it is the only table that does not have any lookup descriptions in it so clearly the correct table to start joins from
if (winners.Count() == 1)
{
firstTable = winners[0];
}
else
{
throw new QueryBuildingException("There were " + qb.TablesUsedInQuery.Count + " Tables (" + String.Join(",", qb.TablesUsedInQuery) + ") involved in the query, some of them might have been lookup tables but there was no clear table to start joining from, either mark one of the TableInfos IsPrimaryExtractionTable or refine your query columns / create new lookup relationships");
}
}
toReturn += firstTable.Name; //simple case "FROM tableX"
}
else
if (qb.PrimaryExtractionTable != null)
{
//user has specified which table to start from
toReturn += qb.PrimaryExtractionTable.Name;
List <int> tablesAddedSoFar = new List <int>();
//now find any joins which involve the primary extraction table
for (int i = 0; i < qb.JoinsUsedInQuery.Count; i++)
{
if (qb.JoinsUsedInQuery[i].PrimaryKey.TableInfo_ID == qb.PrimaryExtractionTable.ID)
{
//we are joining to a table where the PrimaryExtractionTable is the PK in the relationship so join into the foreign key side
toReturn += JoinHelper.GetJoinSQLForeignKeySideOnly(qb.JoinsUsedInQuery[i]) + Environment.NewLine;
tablesAddedSoFar.Add(qb.JoinsUsedInQuery[i].ForeignKey.TableInfo_ID);
}
else
if (qb.JoinsUsedInQuery[i].ForeignKey.TableInfo_ID == qb.PrimaryExtractionTable.ID)
{
//we are joining to a table where the PrimaryExtractionTable is the FK in the relationship so join into the primary key side
toReturn += JoinHelper.GetJoinSQLPrimaryKeySideOnly(qb.JoinsUsedInQuery[i]) + Environment.NewLine;
tablesAddedSoFar.Add(qb.JoinsUsedInQuery[i].PrimaryKey.TableInfo_ID);
}
}
//now add any joins which don't involve the primary table
for (int i = 0; i < qb.JoinsUsedInQuery.Count; i++)
{
if (qb.JoinsUsedInQuery[i].ForeignKey.TableInfo_ID != qb.PrimaryExtractionTable.ID &&
qb.JoinsUsedInQuery[i].PrimaryKey.TableInfo_ID != qb.PrimaryExtractionTable.ID)
{
//if we have already seen foreign key table before
if (tablesAddedSoFar.Contains(qb.JoinsUsedInQuery[i].ForeignKey.TableInfo_ID))
{
toReturn += JoinHelper.GetJoinSQLPrimaryKeySideOnly(qb.JoinsUsedInQuery[i]) + Environment.NewLine; //add primary
}
else
//else if we have already seen primary key table before
if (tablesAddedSoFar.Contains(qb.JoinsUsedInQuery[i].PrimaryKey.TableInfo_ID))
{
toReturn += JoinHelper.GetJoinSQLForeignKeySideOnly(qb.JoinsUsedInQuery[i]) + Environment.NewLine; //add foreign instead
}
else
{
throw new NotImplementedException("We are having to add a Join for a table that is not 1 level down from the PrimaryExtractionTable");
}
}
}
}
else
{
//user has not specified which table to start from so just output them all in a random order (hopefully FindRequiredJoins bombed out if they tried to do anything too mental)
toReturn += JoinHelper.GetJoinSQL(qb.JoinsUsedInQuery[0]) + Environment.NewLine; //"FROM ForeignKeyTable JOIN PrimaryKeyTable ON ..."
//any subsequent joins
for (int i = 1; i < qb.JoinsUsedInQuery.Count; i++)
{
toReturn += JoinHelper.GetJoinSQLForeignKeySideOnly(qb.JoinsUsedInQuery[i]) + Environment.NewLine; //right side only (ForeignKeyTable)
}
}
//any subsequent lookup joins
foreach (QueryTimeColumn column in qb.SelectColumns)
{
if (
(column.IsLookupForeignKey && column.IsLookupForeignKeyActuallyUsed(qb.SelectColumns))
||
column.IsIsolatedLookupDescription)
{
toReturn += JoinHelper.GetJoinSQLPrimaryKeySideOnly(column.LookupTable, column.LookupTableAlias) + Environment.NewLine;
}
}
return(toReturn);
}
public void CreateLiteratureTable()
{
Database.Execute(ISqlQueryBuilder.CreateLiteratureTable());
}
public override void Initialize(string name, NameValueCollection config)
{
Condition.Requires(name, "name").IsNotNullOrWhiteSpace();
Condition.Requires(config, "config").IsNotNull();
var membershipProviderName = config.GetString("membershipProviderName");
if (!string.IsNullOrWhiteSpace(membershipProviderName))
{
this.membershipProvider = this.membershipProviders[membershipProviderName] as BetterMembershipProvider;
}
if (this.membershipProvider == null)
{
throw new ProviderException("membershipProviderName is required");
}
config.Remove("membershipProviderName");
base.Initialize(name, config);
var providerName = string.Empty;
var connectionString = ConfigurationManager.ConnectionStrings[this.membershipProvider.ConnectionStringName];
if (connectionString != null)
{
providerName = connectionString.ProviderName;
}
this.sqlQueryBuilder = this.sqlQueryBuilderFactory(
providerName,
this.membershipProvider.UserTableName,
this.membershipProvider.UserIdColumn,
this.membershipProvider.UserNameColumn,
this.membershipProvider.UserEmailColumn);
}
private static string WriteContainerTreeRecursively(string toReturn, int tabDepth, IContainer currentContainer, ISqlQueryBuilder qb)
{
string tabs = "";
//see how far we have to tab in
for (int i = 0; i < tabDepth; i++)
{
tabs += "\t";
}
//get all the filters in the current container
IFilter[] filtersInContainer = currentContainer.GetFilters().Where(IsEnabled).ToArray();
//see if we have subcontainers
IContainer[] subcontainers = currentContainer.GetSubContainers().Where(IsEnabled).ToArray();
//if there are no filters or subcontainers return nothing
if (!filtersInContainer.Any() && !subcontainers.Any())
{
return("");
}
//output starting bracket
toReturn += tabs + "(" + Environment.NewLine;
//write out subcontainers
for (int i = 0; i < subcontainers.Length; i++)
{
toReturn = WriteContainerTreeRecursively(toReturn, tabDepth + 1, subcontainers[i], qb);
//there are more subcontainers to come
if (i + 1 < subcontainers.Length)
{
toReturn += tabs + currentContainer.Operation + Environment.NewLine;
}
}
//if there are both filters and containers we need to join the trees with the operator (e.g. AND)
if (subcontainers.Length >= 1 && filtersInContainer.Length >= 1)
{
toReturn += currentContainer.Operation + Environment.NewLine;
}
//output each filter also make sure it is tabbed in correctly
for (int i = 0; i < filtersInContainer.Count(); i++)
{
if (qb.CheckSyntax)
{
filtersInContainer[i].Check(new ThrowImmediatelyCheckNotifier());
}
toReturn += tabs + @"/*" + filtersInContainer[i].Name + @"*/" + Environment.NewLine;
// the filter may span multiple lines, so collapse it to a single line cleaning up any whitespace issues, e.g. to avoid double spaces in the collapsed version
var trimmedFilters = (filtersInContainer[i].WhereSQL ?? "")
.Split(new[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries)
.Select(s => s.Trim());
var singleLineWhereSQL = string.Join(" ", trimmedFilters);
toReturn += tabs + singleLineWhereSQL + Environment.NewLine;
//if there are more filters to come
if (i + 1 < filtersInContainer.Count())
{
toReturn += tabs + currentContainer.Operation + Environment.NewLine;
}
}
toReturn += tabs + ")" + Environment.NewLine;
return(toReturn);
}
public SelectClosedState(ISqlQueryBuilder sqlQueryBuilder, QeuryStateContext context)
{
this.sqlQueryBuilder = sqlQueryBuilder;
this.context = context;
}
