public void Append_GivenSqLInstance_ShouldBeValid()
{
_sql = new Sql("l0 @0", "a0");
var sql1 = new Sql("l1 @0", "a1");
var sql2 = new Sql("l2 @0", "a2");
_sql.Append(sql1).ShouldBe(_sql);
_sql.Append(sql2).ShouldBe(_sql);
_sql.SQL.ShouldBe("l0 @0\nl1 @1\nl2 @2");
_sql.Arguments.Length.ShouldBe(3);
_sql.Arguments[0].ShouldBe("a0");
_sql.Arguments[1].ShouldBe("a1");
_sql.Arguments[2].ShouldBe("a2");
}
/// <summary>
/// Adds a set of OR-ed where clauses to the query.
/// </summary>
public virtual IQuery <T> WhereAny(IEnumerable <Expression <Func <T, bool> > > predicates)
{
if (predicates == null)
{
return(this);
}
StringBuilder? sb = null;
List <object>? parameters = null;
Sql <ISqlContext>?sql = null;
foreach (var predicate in predicates)
{
// see notes in Where()
var expressionHelper = new ModelToSqlExpressionVisitor <T>(_sqlContext.SqlSyntax, _sqlContext.Mappers);
var whereExpression = expressionHelper.Visit(predicate);
if (sb == null)
{
sb = new StringBuilder("(");
parameters = new List <object>();
sql = Sql.BuilderFor(_sqlContext);
}
else
{
sb.Append(" OR ");
sql?.Append(" OR ");
}
sb.Append(whereExpression);
parameters?.AddRange(expressionHelper.GetSqlParameters());
sql?.Append(whereExpression, expressionHelper.GetSqlParameters());
}
if (sb == null)
{
return(this);
}
sb.Append(")");
_wheres.Add(Tuple.Create("(" + sql?.SQL + ")", sql?.Arguments) !);
return(this);
}
public void Can_Get_A_Page_Of_Keys()
{
var sql = new Sql();
sql.Append("SELECT *").Append("FROM [merchInvoice]");
var page = _db.Page<KeyDto>(1, 10, sql);
Assert.NotNull(page);
Assert.AreEqual(15, page.TotalItems);
Assert.AreEqual(2, page.TotalPages);
}
public PagedPeopleResult GetPaged(int itemsPerPage, int pageNumber, string sortColumn,
string sortOrder, string searchTerm)
{
var items = new List<People>();
var db = DatabaseContext.Database;
var currentType = typeof(People);
var query = new Sql().Select("*").From("demo_people");
if (!string.IsNullOrEmpty(searchTerm))
{
int c = 0;
foreach (var property in currentType.GetProperties())
{
string before = "WHERE";
if (c > 0)
{
before = "OR";
}
var columnAttri =
property.GetCustomAttributes(typeof(ColumnAttribute), false);
var columnName = property.Name;
if (columnAttri.Any())
{
columnName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
}
query.Append(before + " [" + columnName + "] like @0", "%" + searchTerm + "%");
c++;
}
}
if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder))
query.OrderBy(sortColumn + " " + sortOrder);
else
{
query.OrderBy("id asc");
}
var p = db.Page<People>(pageNumber, itemsPerPage, query);
var result = new PagedPeopleResult
{
TotalPages = p.TotalPages,
TotalItems = p.TotalItems,
ItemsPerPage = p.ItemsPerPage,
CurrentPage = p.CurrentPage,
People = p.Items.ToList()
};
return result;
}
public new Employee FindByName(string username)
{
Sql.Append("select * from fv_user.vwObtenerUsuarios where username=:pName ");
base.FindByName(username);
return(GetEmployeeData());
}
public new void FindAll()
{
Sql.Append("select * from fv_user.vwObtenerUsuarios ");
base.FindAll();
}
public Sql BuildJoin(IDatabase database, SqlExpression <T> sqlExpression, List <JoinData> joinSqlExpressions, List <SelectMember> newMembers, bool count, bool distinct)
{
var modelDef = database.PocoDataFactory.ForType(typeof(T));
var sqlTemplate = count
? "SELECT COUNT(*) FROM {1} {2} {3} {4}"
: "SELECT {0} FROM {1} {2} {3} {4}";
// build cols
var cols = modelDef.QueryColumns.Select((x, j) => new StringPocoCol
{
StringCol = database.DatabaseType.EscapeTableName(modelDef.TableInfo.AutoAlias) + "." +
database.DatabaseType.EscapeSqlIdentifier(x.Value.ColumnName) + " as " + database.DatabaseType.EscapeSqlIdentifier(x.Value.AutoAlias),
PocoColumn = x.Value
});
// build wheres
var wheres = new Sql();
var where = sqlExpression.Context.ToWhereStatement();
wheres.Append(string.IsNullOrEmpty(where) ? string.Empty : "\n" + where, sqlExpression.Context.Params);
// build joins and add cols
var joins = BuildJoinSql(database, joinSqlExpressions, ref cols);
// build orderbys
ISqlExpression exp = sqlExpression;
var orderbys = string.Empty;
if (!count && exp.OrderByMembers.Any())
{
var orderMembers = exp.OrderByMembers.Select(x => new
{
Column = database.PocoDataFactory.ForType(x.EntityType).Columns.Values.Single(z => z.MemberInfo.Name == x.PocoColumn.MemberInfo.Name),
x.AscDesc
}).ToList();
orderbys = "\nORDER BY " + string.Join(", ", orderMembers.Select(x => database.DatabaseType.EscapeSqlIdentifier(x.Column.AutoAlias) + " " + x.AscDesc).ToArray());
}
// Override select columns with projected ones
if (newMembers != null)
{
var selectMembers = ((ISqlExpression)_sqlExpression).OrderByMembers
.Select(x => new SelectMember()
{
PocoColumn = x.PocoColumn, EntityType = x.EntityType
})
.Where(x => !newMembers.Any(y => y.EntityType == x.EntityType && y.PocoColumn.MemberInfo.Name == x.PocoColumn.MemberInfo.Name));
cols = newMembers.Concat(selectMembers).Select(x =>
{
var pocoData = database.PocoDataFactory.ForType(x.EntityType);
return(new StringPocoCol
{
StringCol = database.DatabaseType.EscapeTableName(pocoData.TableInfo.AutoAlias) + "." +
database.DatabaseType.EscapeSqlIdentifier(x.PocoColumn.ColumnName) + " as " + database.DatabaseType.EscapeSqlIdentifier(x.PocoColumn.AutoAlias),
PocoColumn = x.PocoColumn
});
});
}
// replace templates
var resultantSql = string.Format(sqlTemplate,
(distinct ? "DISTINCT " : "") + string.Join(", ", cols.Select(x => x.StringCol).ToArray()),
database.DatabaseType.EscapeTableName(modelDef.TableInfo.TableName) + " " + database.DatabaseType.EscapeTableName(modelDef.TableInfo.AutoAlias),
joins,
wheres.SQL,
orderbys);
var newsql = ((ISqlExpression)_sqlExpression).ApplyPaging(resultantSql, cols.Select(x => x.PocoColumn));
return(new Sql(newsql, wheres.Arguments));
}
public PagedContacts GetPagedContacts(int pageNumber, string sortColumn, string sortOrder, string searchTerm, int typeId)
{
int itemsPerPage = PipelineConfig.GetConfig().AppSettings.PageSize;
var items = new List <Contact>();
var contactType = typeof(Contact);
var query = new Sql().Select("*").From("pipelineContact");
if (typeId == 0)
{
query.Append(" where Archived=0 ", typeId);
}
else if (typeId == -1)
{
query.Append(" where Archived=1 ", typeId);
}
else if (typeId == -2)
{
query.Append(" where TypeId=0 and Archived=0 ", typeId);
}
else
{
query.Append(" where TypeId=@0 and Archived=0 ", typeId);
}
if (!string.IsNullOrEmpty(searchTerm))
{
query.Append(" and (Name like @0 or Email like @0) ", "%" + searchTerm + "%");
}
if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder) && sortColumn != "OrganisationNames")
{
query.OrderBy(sortColumn + " " + sortOrder);
}
else
{
query.OrderBy("Name asc");
}
var p = DbService.db().Page <Contact>(pageNumber, itemsPerPage, query);
for (int i = 0; i < p.Items.ToList().Count(); i++)
{
p.Items[i].Organisations = new OrganisationApiController().GetByIds(p.Items[i].OrganisationIds);
p.Items[i].OrganisationNames = p.Items[i].Organisations.Select(x => x.Name).OrderBy(x => x);
}
// special sorting for organisations
if (sortColumn == "OrganisationNames")
{
p.Items = sortOrder.ToLower() != "desc" ?
p.Items.OrderBy(x => x.OrganisationNames.FirstOrDefault()).ToList() : p.Items.OrderByDescending(x => x.OrganisationNames.FirstOrDefault()).ToList();
}
return(new PagedContacts
{
TotalPages = p.TotalPages,
TotalItems = p.TotalItems,
ItemsPerPage = p.ItemsPerPage,
CurrentPage = p.CurrentPage,
Contacts = p.Items.ToList()
});
}
public static void AddFilters <T>(ref Sql sql, T poco, List <Filter> filters)
{
if (filters == null || filters.Count().Equals(0))
{
return;
}
foreach (var filter in filters)
{
if (string.IsNullOrWhiteSpace(filter.ColumnName))
{
if (!string.IsNullOrWhiteSpace(filter.PropertyName))
{
filter.ColumnName = GetColumnName(poco, filter.PropertyName);
}
}
string column = Sanitizer.SanitizeIdentifierName(filter.ColumnName);
if (string.IsNullOrWhiteSpace(column))
{
continue;
}
string statement = filter.FilterStatement;
if (statement == null || statement.ToUpperInvariant() != "OR")
{
statement = "AND";
}
statement += " ";
switch ((FilterCondition)filter.FilterCondition)
{
case FilterCondition.IsEqualTo:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " = @0", filter.FilterValue);
break;
case FilterCondition.IsNotEqualTo:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " != @0", filter.FilterValue);
break;
case FilterCondition.IsLessThan:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " < @0", filter.FilterValue);
break;
case FilterCondition.IsLessThanEqualTo:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " <= @0", filter.FilterValue);
break;
case FilterCondition.IsGreaterThan:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " > @0", filter.FilterValue);
break;
case FilterCondition.IsGreaterThanEqualTo:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " >= @0", filter.FilterValue);
break;
case FilterCondition.IsBetween:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " BETWEEN @0 AND @1", filter.FilterValue, filter.FilterAndValue);
break;
case FilterCondition.IsNotBetween:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " NOT BETWEEN @0 AND @1", filter.FilterValue, filter.FilterAndValue);
break;
case FilterCondition.IsLike:
sql.Append(statement + " lower(" + Sanitizer.SanitizeIdentifierName(column) + ") LIKE @0",
"%" + filter.FilterValue.ToString().ToLower(CultureManager.GetCurrent()) + "%");
break;
case FilterCondition.IsNotLike:
sql.Append(statement + " lower(" + Sanitizer.SanitizeIdentifierName(column) + ") NOT LIKE @0",
"%" + filter.FilterValue.ToString().ToLower(CultureManager.GetCurrent()) + "%");
break;
}
}
}
public void Append_GivenConsecutiveSetsAndWheres_ShouldBeValid()
{
_sql = new Sql()
.Append("UPDATE blah");
_sql.Append("SET a = 1");
_sql.Append("SET b = 2");
_sql.Append("WHERE x");
_sql.Append("WHERE y");
_sql.SQL.ShouldBe("UPDATE blah\nSET a = 1\n, b = 2\nWHERE x\nAND y");
}
public new ComercialData FindById(string id)
{
Sql.Append("select * from fv_user.vwObtenerDatosComerciales where id_cliente=:pId ");
base.FindById(id);
return(GetComercialData());
}
/// <summary>
/// 访问 <see cref="T:System.Linq.Expressions.BinaryExpression"/> 的子级。
/// </summary>
/// <returns>
/// 如果修改了该表达式或任何子表达式，则为修改后的表达式；否则返回原始表达式。
/// </returns>
/// <param name="binaryExpression">要访问的表达式。</param>
protected override Expression VisitBinary(BinaryExpression binaryExpression)
{
Check.NotNull(binaryExpression, nameof(binaryExpression));
if (binaryExpression.NodeType == ExpressionType.Coalesce)
{
Sql.Append("COALESCE(");
Visit(binaryExpression.Left);
Sql.Append(", ");
Visit(binaryExpression.Right);
Sql.Append(")");
}
else
{
var needParentheses
= !binaryExpression.Left.IsSimpleExpression() ||
!binaryExpression.Right.IsSimpleExpression() ||
binaryExpression.IsLogicalOperation();
if (needParentheses)
{
Sql.Append("(");
}
Visit(binaryExpression.Left);
if (binaryExpression.IsLogicalOperation() &&
binaryExpression.Left.IsSimpleExpression())
{
Sql.Append(" = ");
Sql.Append(_sqlHelper.EscapeLiteral(true));
}
if (!TryGenerateBinaryOperator(binaryExpression.NodeType, out var op))
{
throw new ArgumentOutOfRangeException();
}
if ((binaryExpression.NodeType == ExpressionType.NotEqual ||
binaryExpression.NodeType == ExpressionType.Equal) &&
binaryExpression.Right.NodeType == ExpressionType.Constant)
{
var value = binaryExpression.Right.Invoke();
if (value == null)
{
Sql.Append(binaryExpression.NodeType == ExpressionType.Equal
? " IS NULL "
: " IS NOT NULL ");
}
else
{
Sql.Append(op);
Sql.Append(_sqlHelper.EscapeLiteral(value));
}
}
else
{
Sql.Append(op);
Visit(binaryExpression.Right);
if (binaryExpression.IsLogicalOperation() &&
binaryExpression.Right.IsSimpleExpression())
{
Sql.Append(" = ");
Sql.Append(_sqlHelper.EscapeLiteral(true));
}
}
if (needParentheses)
{
Sql.Append(")");
}
}
return(binaryExpression);
}
/// <summary>
/// 名称别名设置
/// </summary>
protected override void AppendNameCast()
{
Sql.Append(" AS ");
}
protected virtual Expression VisitTemporalTable(TemporalTableExpression tableExpression)
{
Sql.Append(SqlGenerationHelper.DelimitIdentifier(tableExpression.Name, tableExpression.Schema));
switch (tableExpression.TemporalQueryType)
{
case TemporalQueryType.None:
break;
case TemporalQueryType.AsOf:
{
var _AsOfDate = tableExpression.AsOfDate.Name;
Sql.Append($" FOR SYSTEM_TIME AS OF @{_AsOfDate}");
if (!CommandBuilder.Parameters.Any(x => x.InvariantName == tableExpression.AsOfDate.Name))
{
CommandBuilder.AddParameter(tableExpression.AsOfDate.Name, _AsOfDate);
}
break;
}
case TemporalQueryType.FromTo:
{
var _StartDate = tableExpression.StartDate.Name;
var _EndDate = tableExpression.EndDate.Name;
Sql.Append($" FOR SYSTEM_TIME FROM @{_StartDate} TO @{_EndDate}");
if (!CommandBuilder.Parameters.Any(x => x.InvariantName == _StartDate))
{
CommandBuilder.AddParameter(_StartDate, _StartDate);
}
if (!CommandBuilder.Parameters.Any(x => x.InvariantName == _EndDate))
{
CommandBuilder.AddParameter(_EndDate, _EndDate);
}
break;
}
case TemporalQueryType.BetweenAnd:
{
var _StartDate = tableExpression.StartDate.Name;
var _EndDate = tableExpression.EndDate.Name;
Sql.Append($" FOR SYSTEM_TIME BETWEEN @{_StartDate} AND @{_EndDate}");
if (!CommandBuilder.Parameters.Any(x => x.InvariantName == _StartDate))
{
CommandBuilder.AddParameter(_StartDate, _StartDate);
}
if (!CommandBuilder.Parameters.Any(x => x.InvariantName == _EndDate))
{
CommandBuilder.AddParameter(_EndDate, _EndDate);
}
break;
}
case TemporalQueryType.ContainedIn:
{
var _StartDate = tableExpression.StartDate.Name;
var _EndDate = tableExpression.EndDate.Name;
Sql.Append($" FOR SYSTEM_TIME CONTAINED IN(@{_StartDate}, @{_EndDate})");
if (!CommandBuilder.Parameters.Any(x => x.InvariantName == _StartDate))
{
CommandBuilder.AddParameter(_StartDate, _StartDate);
}
if (!CommandBuilder.Parameters.Any(x => x.InvariantName == _EndDate))
{
CommandBuilder.AddParameter(_EndDate, _EndDate);
}
break;
}
case TemporalQueryType.All:
{
Sql.Append(" FOR SYSTEM_TIME ALL");
break;
}
default:
break;
}
Sql
.Append(AliasSeparator)
.Append(SqlGenerationHelper.DelimitIdentifier(tableExpression.Alias));
return(tableExpression);
}
public StoredProcedureBuilder(Sql sql, string procedureName)
{
string initalSql = string.Format(PROCEDURE_FORMAT, procedureName);
_sql = sql.Append(initalSql);
}
public void FindInactiveUsers()
{
Sql.Append("select * from fv_user.vwObtenerUsuarios where is_active=0 ");
base.FindAll();
}
public override Expression VisitSqlFunction(SqlFunctionExpression sqlFunctionExpression)
{
switch (sqlFunctionExpression.FunctionName)
{
case "EXTRACT":
{
Sql.Append(sqlFunctionExpression.FunctionName);
Sql.Append("(");
Visit(sqlFunctionExpression.Arguments[0]);
Sql.Append(" FROM ");
Visit(sqlFunctionExpression.Arguments[1]);
Sql.Append(")");
return(sqlFunctionExpression);
}
case "CAST":
{
Sql.Append(sqlFunctionExpression.FunctionName);
Sql.Append("(");
Visit(sqlFunctionExpression.Arguments[0]);
Sql.Append(" AS ");
Visit(sqlFunctionExpression.Arguments[1]);
Sql.Append(")");
return(sqlFunctionExpression);
}
case "AVG" when sqlFunctionExpression.Type == typeof(decimal):
case "SUM" when sqlFunctionExpression.Type == typeof(decimal):
{
Sql.Append("CAST(");
base.VisitSqlFunction(sqlFunctionExpression);
Sql.Append(" AS DECIMAL(29,4))");
return(sqlFunctionExpression);
}
case "INSTR":
{
if (sqlFunctionExpression.Arguments[1] is ParameterExpression parameterExpression &&
ParameterValues.TryGetValue(parameterExpression.Name, out var value) &&
(string)value == string.Empty)
{
return(Visit(Expression.Constant(1)));
}
break;
}
case "ADD_MONTHS":
{
Sql.Append("CAST(");
base.VisitSqlFunction(sqlFunctionExpression);
Sql.Append(" AS TIMESTAMP)");
return(sqlFunctionExpression);
}
}
return(base.VisitSqlFunction(sqlFunctionExpression));
}
public void CreateTable(Domain.Entity entity, List <Domain.Attribute> defaultAttributes)
{
if (this.TableExists(entity.Name))
{
return;
}
Sql s = Sql.Builder.Append(string.Format("CREATE TABLE [dbo].[{0}]", entity.Name))
.Append("(");
//.Append(string.Format("[{0}Id] [uniqueidentifier] NOT NULL,", entity.Name))
//.Append("[Name] [nvarchar](300) NULL,")
//.Append("[VersionNumber] [timestamp] NOT NULL,")
//.Append("[CreatedOn] [datetime] NOT NULL,")
//.Append("[CreatedBy] [uniqueidentifier] NOT NULL,")
//.Append("[ModifiedOn] [datetime] NULL,")
//.Append("[ModifiedBy] [uniqueidentifier] NULL,")
//.Append("[StateCode] [int] NOT NULL,")
//.Append("[StatusCode] [int] NOT NULL,");
foreach (var attribute in defaultAttributes)
{
s.Append("[{0}] {1} {2} NULL,".FormatWith(attribute.Name, attribute.GetDbType(), attribute.IsNullable ? "" : "NOT"));
}
List <string> idxColumns = new List <string>();//需要创建索引的字段
if (defaultAttributes.Exists(x => x.Name.IsCaseInsensitiveEqual("StateCode")))
{
idxColumns.Add("StateCode");
}
if (defaultAttributes.Exists(x => x.Name.IsCaseInsensitiveEqual("CreatedOn")))
{
idxColumns.Add("CreatedOn");
}
if (entity.EntityMask == EntityMaskEnum.User)
{
//s.Append("[OwnerId] [uniqueidentifier] NOT NULL,")
//.Append("[OwnerIdType] [int] NOT NULL,")
//.Append("[OwningBusinessUnit] [uniqueidentifier] NULL,");
if (defaultAttributes.Exists(x => x.Name.IsCaseInsensitiveEqual("OwnerId")))
{
if (!defaultAttributes.Exists(x => x.Name.IsCaseInsensitiveEqual("OwnerIdType")))
{
s.Append("[OwnerIdType] [int] NOT NULL,");
}
idxColumns.Add("OwnerId");
}
}
if (defaultAttributes.Exists(x => x.Name.IsCaseInsensitiveEqual("OrganizationId")))
{
//s.Append("[OrganizationId] [uniqueidentifier] NOT NULL,");
idxColumns.Add("OrganizationId");
}
//if (entity.WorkFlowEnabled)
//{
// s.Append("[WorkFlowId] [uniqueidentifier] NULL,");
// s.Append("[ProcessState] [int] NULL default(-1),");
//}
//if (entity.BusinessFlowEnabled)
//{
// s.Append("[StageId] [uniqueidentifier] NULL,");
//}
//主键
s.Append(string.Format("CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED", entity.Name))
.Append("(")
.Append(string.Format("[{0}Id] ASC", entity.Name))
.Append(")WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]")
.Append(") ON [PRIMARY]");
//创建外键约束
if (defaultAttributes.Exists(x => x.Name.IsCaseInsensitiveEqual("CreatedBy")))
{
s.Append(string.Format("ALTER TABLE [dbo].[{0}] WITH CHECK ADD CONSTRAINT [FK_{0}_SystemUser_CreatedBy] FOREIGN KEY([CreatedBy])", entity.Name));
s.Append("REFERENCES[dbo].[SystemUser]([SystemUserId])");
s.Append(string.Format("ALTER TABLE [dbo].[{0}] CHECK CONSTRAINT [FK_{0}_SystemUser_CreatedBy]", entity.Name));
}
if (entity.EntityMask == EntityMaskEnum.User)
{
if (defaultAttributes.Exists(x => x.Name.IsCaseInsensitiveEqual("OwningBusinessUnit")))
{
s.Append(string.Format("ALTER TABLE [dbo].[{0}] WITH NOCHECK ADD CONSTRAINT [FK_{0}_BusinessUnit_OwningBusinessUnit] FOREIGN KEY([OwningBusinessUnit])", entity.Name));
s.Append("REFERENCES[dbo].[BusinessUnit]([BusinessUnitId])");
s.Append(string.Format("ALTER TABLE [dbo].[{0}] CHECK CONSTRAINT [FK_{0}_BusinessUnit_OwningBusinessUnit]", entity.Name));
}
}
if (defaultAttributes.Exists(x => x.Name.IsCaseInsensitiveEqual("OrganizationId")))
{
s.Append(string.Format("ALTER TABLE [dbo].[{0}] WITH NOCHECK ADD CONSTRAINT [FK_{0}_Organization_OrganizationId] FOREIGN KEY([OrganizationId])", entity.Name));
s.Append("REFERENCES[dbo].[Organization]([OrganizationId])");
s.Append(string.Format("ALTER TABLE [dbo].[{0}] CHECK CONSTRAINT [FK_{0}_Organization_OrganizationId]", entity.Name));
}
//启动流程时建立约束
if (entity.WorkFlowEnabled)
{
if (defaultAttributes.Exists(x => x.Name.IsCaseInsensitiveEqual("WorkFlowId")))
{
s.Append(string.Format("ALTER TABLE [dbo].[{0}] WITH NOCHECK ADD CONSTRAINT [FK_{0}_WorkFlow_WorkFlowId] FOREIGN KEY([WorkFlowId])", entity.Name));
s.Append("REFERENCES[dbo].[WorkFlow]([WorkFlowId])");
s.Append(string.Format("ALTER TABLE [dbo].[{0}] CHECK CONSTRAINT [FK_{0}_WorkFlow_WorkFlowId]", entity.Name));
}
}
//创建默认字段索引
if (idxColumns.NotEmpty())
{
s.Append(string.Format("CREATE INDEX ndx_core ON {0}({1}) WITH (FILLFACTOR = 80)", entity.Name, string.Join(",", idxColumns)));
}
_database.Execute(s);
}
public IEnumerable<object> GetFiltered(string typeName, string filterColumn, string filterValue, string sortColumn, string sortOrder)
{
var currentType = Type.GetType(typeName);
var tableName = (TableNameAttribute)Attribute.GetCustomAttribute(currentType, typeof(TableNameAttribute));
var uioMaticAttri = (UIOMaticAttribute)Attribute.GetCustomAttribute(currentType, typeof(UIOMaticAttribute));
var db = (Database)DatabaseContext.Database;
if (!string.IsNullOrEmpty(uioMaticAttri.ConnectionStringName))
db = new Database(uioMaticAttri.ConnectionStringName);
var query = new Sql().Select("*").From(tableName.Value);
query.Append("where" + "[" + filterColumn + "] = @0", filterValue);
if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder))
query.OrderBy(sortColumn + " " + sortOrder);
foreach (dynamic item in db.Fetch<dynamic>(query))
{
// get settable public properties of the type
var props = currentType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(x => x.GetSetMethod() != null);
// create an instance of the type
var obj = Activator.CreateInstance(currentType);
// set property values using reflection
var values = (IDictionary<string, object>)item;
foreach (var prop in props)
{
var columnAttri =
prop.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));
var propName = prop.Name;
if (columnAttri.Any())
propName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
if (values.ContainsKey(propName))
prop.SetValue(obj, values[propName]);
}
yield return obj;
}
}
/// <summary>
/// Gets paged user results
/// </summary>
/// <param name="query"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalRecords"></param>
/// <param name="orderBy"></param>
/// <param name="orderDirection"></param>
/// <param name="includeUserGroups">
/// A filter to only include user that belong to these user groups
/// </param>
/// <param name="excludeUserGroups">
/// A filter to only include users that do not belong to these user groups
/// </param>
/// <param name="userState">Optional parameter to filter by specified user state</param>
/// <param name="filter"></param>
/// <returns></returns>
/// <remarks>
/// The query supplied will ONLY work with data specifically on the umbracoUser table because we are using NPoco paging (SQL paging)
/// </remarks>
public IEnumerable <IUser> GetPagedResultsByQuery(IQuery <IUser> query, long pageIndex, int pageSize, out long totalRecords,
Expression <Func <IUser, object> > orderBy, Direction orderDirection = Direction.Ascending,
string[] includeUserGroups = null, string[] excludeUserGroups = null, UserState[] userState = null, IQuery <IUser> filter = null)
{
if (orderBy == null)
{
throw new ArgumentNullException(nameof(orderBy));
}
Sql <ISqlContext> filterSql = null;
var customFilterWheres = filter?.GetWhereClauses().ToArray();
var hasCustomFilter = customFilterWheres != null && customFilterWheres.Length > 0;
if (hasCustomFilter ||
includeUserGroups != null && includeUserGroups.Length > 0 ||
excludeUserGroups != null && excludeUserGroups.Length > 0 ||
userState != null && userState.Length > 0 && userState.Contains(UserState.All) == false)
{
filterSql = SqlContext.Sql();
}
if (hasCustomFilter)
{
foreach (var clause in customFilterWheres)
{
filterSql.Append($"AND ({clause.Item1})", clause.Item2);
}
}
if (includeUserGroups != null && includeUserGroups.Length > 0)
{
const string subQuery = @"AND (umbracoUser.id IN (SELECT DISTINCT umbracoUser.id
FROM umbracoUser
INNER JOIN umbracoUser2UserGroup ON umbracoUser2UserGroup.userId = umbracoUser.id
INNER JOIN umbracoUserGroup ON umbracoUserGroup.id = umbracoUser2UserGroup.userGroupId
WHERE umbracoUserGroup.userGroupAlias IN (@userGroups)))";
filterSql.Append(subQuery, new { userGroups = includeUserGroups });
}
if (excludeUserGroups != null && excludeUserGroups.Length > 0)
{
const string subQuery = @"AND (umbracoUser.id NOT IN (SELECT DISTINCT umbracoUser.id
FROM umbracoUser
INNER JOIN umbracoUser2UserGroup ON umbracoUser2UserGroup.userId = umbracoUser.id
INNER JOIN umbracoUserGroup ON umbracoUserGroup.id = umbracoUser2UserGroup.userGroupId
WHERE umbracoUserGroup.userGroupAlias IN (@userGroups)))";
filterSql.Append(subQuery, new { userGroups = excludeUserGroups });
}
if (userState != null && userState.Length > 0)
{
//the "ALL" state doesn't require any filtering so we ignore that, if it exists in the list we don't do any filtering
if (userState.Contains(UserState.All) == false)
{
var sb = new StringBuilder("(");
var appended = false;
if (userState.Contains(UserState.Active))
{
sb.Append("(userDisabled = 0 AND userNoConsole = 0 AND lastLoginDate IS NOT NULL)");
appended = true;
}
if (userState.Contains(UserState.Inactive))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(userDisabled = 0 AND userNoConsole = 0 AND lastLoginDate IS NULL)");
appended = true;
}
if (userState.Contains(UserState.Disabled))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(userDisabled = 1)");
appended = true;
}
if (userState.Contains(UserState.LockedOut))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(userNoConsole = 1)");
appended = true;
}
if (userState.Contains(UserState.Invited))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(lastLoginDate IS NULL AND userDisabled = 1 AND invitedDate IS NOT NULL)");
appended = true;
}
sb.Append(")");
filterSql.Append("AND " + sb);
}
}
// create base query
var sql = SqlContext.Sql()
.Select <UserDto>()
.From <UserDto>();
// apply query
if (query != null)
{
sql = new SqlTranslator <IUser>(sql, query).Translate();
}
// get sorted and filtered sql
var sqlNodeIdsWithSort = ApplySort(ApplyFilter(sql, filterSql, query != null), orderBy, orderDirection);
// get a page of results and total count
var pagedResult = Database.Page <UserDto>(pageIndex + 1, pageSize, sqlNodeIdsWithSort);
totalRecords = Convert.ToInt32(pagedResult.TotalItems);
// map references
PerformGetReferencedDtos(pagedResult.Items);
return(pagedResult.Items.Select(UserFactory.BuildEntity));
}
/// <summary>
/// Return the audit items as paged result
/// </summary>
/// <param name="query">
/// The query coming from the service
/// </param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalRecords"></param>
/// <param name="orderDirection"></param>
/// <param name="auditTypeFilter">
/// Since we currently do not have enum support with our expression parser, we cannot query on AuditType in the query or the custom filter
/// so we need to do that here
/// </param>
/// <param name="customFilter">
/// A user supplied custom filter
/// </param>
/// <returns></returns>
public IEnumerable <IAuditItem> GetPagedResultsByQuery(IQuery <IAuditItem> query, long pageIndex, int pageSize,
out long totalRecords, Direction orderDirection,
AuditType[] auditTypeFilter,
IQuery <IAuditItem> customFilter)
{
if (auditTypeFilter == null)
{
auditTypeFilter = new AuditType[0];
}
var sql = GetBaseQuery(false);
if (query == null)
{
query = new Query <IAuditItem>();
}
var translatorIds = new SqlTranslator <IAuditItem>(sql, query);
var translatedQuery = translatorIds.Translate();
var customFilterWheres = customFilter != null?customFilter.GetWhereClauses().ToArray() : null;
var hasCustomFilter = customFilterWheres != null && customFilterWheres.Length > 0;
if (hasCustomFilter)
{
var filterSql = new Sql();
var first = true;
foreach (var filterClaus in customFilterWheres)
{
if (first == false)
{
filterSql.Append(" AND ");
}
filterSql.Append(string.Format("({0})", filterClaus.Item1), filterClaus.Item2);
first = false;
}
translatedQuery = GetFilteredSqlForPagedResults(translatedQuery, filterSql);
}
if (auditTypeFilter.Length > 0)
{
var filterSql = new Sql();
var first = true;
foreach (var filterClaus in auditTypeFilter)
{
if (first == false || hasCustomFilter)
{
filterSql.Append(" AND ");
}
filterSql.Append("(logHeader = @logHeader)", new { logHeader = filterClaus.ToString() });
first = false;
}
translatedQuery = GetFilteredSqlForPagedResults(translatedQuery, filterSql);
}
if (orderDirection == Direction.Descending)
{
translatedQuery.OrderByDescending("Datestamp");
}
else
{
translatedQuery.OrderBy("Datestamp");
}
// Get page of results and total count
var pagedResult = Database.Page <LogDto>(pageIndex + 1, pageSize, translatedQuery);
totalRecords = pagedResult.TotalItems;
var pages = pagedResult.Items.Select(
dto => new AuditItem(dto.Id, dto.Comment, Enum <AuditType> .ParseOrNull(dto.Header) ?? AuditType.Custom, dto.UserId)).ToArray();
//Mapping the DateStamp
for (int i = 0; i < pages.Length; i++)
{
pages[i].CreateDate = pagedResult.Items[i].Datestamp;
}
return(pages);
}
/// <summary>
/// 获取搜索过滤sql
/// </summary>
/// <param name="query"></param>
/// <param name="parms"></param>
/// <returns></returns>
private void GetSearchWhere(SearchProductQuery query, Sql where)
{
where.Append(" WHERE s.CanSearch=1 ");
#region 过滤条件
if (query.FilterVirtualProduct.HasValue && query.FilterVirtualProduct.Value)
{
where.Append(" AND p.ProductType=0 ");
}
if (query.ShopId != 0)
{
where.Append(" AND s.ShopId=@0 ", query.ShopId);
//parms.Add("@ShopId", query.ShopId);
}
if (query.VShopId != 0)
{
where.Append(" AND s.ShopId IN (SELECT ShopId FROM Mall_vshop where Id=@0) ", query.VShopId);
//parms.Add("@VShopId", query.VShopId);
}
if ((query.VShopId != 0 || query.ShopId != 0) && query.ShopCategoryId != 0)
{
where.Append(" AND s.ProductId IN (select ProductId from Mall_productshopcategory where ShopCategoryId in(select id from Mall_ShopCategory where ShopId = @0 and(id = @1 or ParentCategoryId = @1))) ", query.ShopId, query.ShopCategoryId);
//parms.Add("@ShopCategoryId", query.ShopCategoryId);
}
if (query.BrandId != 0)
{
where.Append(" AND s.BrandId=@0 ", query.BrandId);
//parms.Add("@BrandId", query.BrandId);
}
if (query.FirstCateId != 0)
{
where.Append(" AND s.FirstCateId=@0 ", query.FirstCateId);
//parms.Add("@FirstCateId", query.FirstCateId);
}
else if (query.SecondCateId != 0)
{
where.Append(" AND s.SecondCateId=@0 ", query.SecondCateId);
//parms.Add("@SecondCateId", query.SecondCateId);
}
else if (query.ThirdCateId != 0)
{
where.Append(" AND s.ThirdCateId=@0 ", query.ThirdCateId);
//parms.Add("@ThirdCateId", query.ThirdCateId);
}
if (query.StartPrice >= 0)
{
where.Append(" AND s.SalePrice>=@0 ", query.StartPrice);
//parms.Add("@StartPrice", query.StartPrice);
}
if (query.EndPrice > 0 && query.EndPrice >= query.StartPrice)
{
where.Append(" AND s.SalePrice <= @0 ", query.EndPrice);
//parms.Add("@EndPrice", query.EndPrice);
}
if (query.AttrValIds.Count > 0)
{
where.Append(" AND s.ProductId IN (SELECT DISTINCT ProductId FROM Mall_ProductAttribute ");
//此处属性筛选，要取交集非并集
foreach (var item in query.AttrValIds)
{
where.Append(" INNER JOIN (SELECT DISTINCT ProductId FROM Mall_ProductAttribute WHERE (ValueId = " + item + " ) ) t" + query.AttrValIds.IndexOf(item) + " USING (ProductId) ");
}
where.Append(")");
}
if (!string.IsNullOrEmpty(query.Keyword))
{
if (!query.IsLikeSearch)
{
where.Append(" AND MATCH(s.ProductName) AGAINST(@0 IN BOOLEAN MODE) ", string.Concat(UrnHtml(query.Keyword.TrimEnd(' ')), "*").Replace(" ", "*"));
//parms.Add("@ProductName", string.Concat(query.Keyword, "*").Replace(" ", "*"));
}
else
{
where.Append(" AND s.ProductName like @0 ", "%" + query.Keyword + "%");
//parms.Add("@ProductName", "%" + query.Keyword + "%");
}
}
#endregion
}
public void AddLowerFunctionToSqlQuery(string value)
=> Sql.Append("lower(")
.Append(value)
.Append(")");
public void UpdateSearchProduct(long productId)
{
var sql = new Sql();
sql.Append("update Mall_searchproduct a ");
sql.Append("left join Mall_product b on a.ProductId = b.Id ");
sql.Append("left join Mall_category c on SUBSTRING_INDEX(b.CategoryPath, '|', 1) = c.Id ");
sql.Append("left join Mall_category d on SUBSTRING_INDEX(SUBSTRING_INDEX(b.CategoryPath, '|', 2), '|', -1) = d.Id ");
sql.Append("left join Mall_category e on SUBSTRING_INDEX(b.CategoryPath, '|', -1) = e.Id ");
sql.Append("left join (select ProductId, group_concat(ValueId) as AttrValues from Mall_productattribute group by productId) f on a.ProductId = f.ProductId ");
sql.Append("left join Mall_shop g on b.ShopId = g.Id ");
sql.Append("left join Mall_brand h on b.BrandId = h.Id ");
sql.Append("set a.ProductName = b.ProductName,a.ShopName = g.ShopName,a.BrandId=IFNULL(h.Id,0),a.BrandName = h.`Name`,a.BrandLogo = h.Logo,a.FirstCateId = c.Id,a.FirstCateName = c.`Name`, ");
sql.Append("a.SecondCateId = d.Id,a.SecondCateName = d.`Name`,a.ThirdCateId = e.Id,a.ThirdCateName = e.`Name`,a.AttrValues = f.AttrValues,a.SalePrice = b.MinSalePrice,a.ImagePath = b.ImagePath, ");
sql.Append("a.CanSearch = (case when b.SaleStatus =1 and b.AuditStatus = 2 and b.IsDeleted=0 then 1 else 0 end) ");
sql.Append("where a.ProductId=@0 ", productId);
DbFactory.Default.Execute(sql);//, new { ProductId = productId });
}
public void Append_GivenSimpleStrings_ShouldBeValid()
{
_sql.Append("LINE 1");
_sql.Append("LINE 2");
_sql.Append("LINE 3");
_sql.SQL.ShouldBe("LINE 1\nLINE 2\nLINE 3");
_sql.Arguments.Length.ShouldBe(0);
}
/// <summary>
/// 获取搜索过滤sql
/// </summary>
/// <param name="query"></param>
/// <param name="parms"></param>
/// <returns></returns>
private void GetAppletSearchWhere(SearchProductQuery query, Sql where)
{
where.Append("WHERE CanSearch=1 ");
#region 过滤条件
if (query.ShopId != 0)
{
where.Append("AND ps.ShopId=@0 ", query.ShopId);
}
if (query.VShopId != 0)
{
where.Append(" AND ps.ShopId IN (SELECT ShopId FROM Mall_vshop where Id=@0) ", query.VShopId);
}
if ((query.VShopId != 0 || query.ShopId != 0) && query.ShopCategoryId != 0)
{
where.Append(" AND ps.ProductId IN (select ProductId from Mall_productshopcategory where ShopCategoryId in (select id from Mall_ShopCategory where ShopId = @1 and(id = @0 or ParentCategoryId = @0))) ", query.ShopCategoryId, query.ShopId);
}
if (query.BrandId != 0)
{
where.Append("AND ps.BrandId=@0 ", query.BrandId);
}
if (query.FirstCateId != 0)
{
where.Append("AND ps.FirstCateId=@0 ", query.FirstCateId);
}
else if (query.SecondCateId != 0)
{
where.Append("AND ps.SecondCateId=@0 ", query.SecondCateId);
}
else if (query.ThirdCateId != 0)
{
where.Append("AND ps.ThirdCateId=@0 ", query.ThirdCateId);
}
if (query.StartPrice > 0)
{
where.Append(" AND ps.SalePrice>=@0 ", query.StartPrice);
}
if (query.EndPrice > 0 && query.EndPrice >= query.StartPrice)
{
where.Append(" AND ps.SalePrice <= @EndPrice ");
}
if (query.AttrValIds.Count > 0)
{
where.Append(" AND ps.ProductId IN (SELECT DISTINCT ProductId FROM Mall_ProductAttribute WHERE ValueId IN (@0)) ", query.AttrValIds);
}
if (!string.IsNullOrEmpty(query.Keyword))
{
if (!query.IsLikeSearch)
{
where.Append("AND MATCH(ps.ProductName) AGAINST(@0 IN BOOLEAN MODE) ", string.Concat(UrnHtml(query.Keyword), "*").Replace(" ", "*"));
}
else
{
where.Append("AND ps.ProductName like @0 ", "%" + query.Keyword + "%");
}
}
#endregion
}
public async Task <List <object> > BulkImportAsync(List <Dictionary <string, object> > items)
{
if (!this.SkipValidation)
{
if (!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.ImportData, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if (!this.HasAccess)
{
Log.Information(
$"Access to import entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}.");
throw new UnauthorizedException("Access is denied.");
}
}
var result = new List <object>();
int line = 0;
try
{
using (var db = DbProvider.GetDatabase(this.Database))
{
using (var transaction = db.GetTransaction())
{
items = this.Crypt(items);
foreach (var item in items)
{
line++;
item["audit_user_id"] = this.UserId;
item["audit_ts"] = DateTimeOffset.UtcNow;
item["deleted"] = false;
var primaryKeyValue = item[this.PrimaryKey];
if (primaryKeyValue != null)
{
result.Add(primaryKeyValue);
var sql = new Sql("UPDATE " + this.FullyQualifiedObjectName + " SET");
int index = 0;
foreach (var prop in item.Where(x => !x.Key.Equals(this.PrimaryKey)))
{
if (index > 0)
{
sql.Append(",");
}
sql.Append(Sanitizer.SanitizeIdentifierName(prop.Key) + "=@0", prop.Value);
index++;
}
sql.Where(this.PrimaryKey + "=@0", primaryKeyValue);
await db.ExecuteAsync(sql).ConfigureAwait(false);
}
else
{
string columns = string.Join(",",
item.Where(x => !x.Key.Equals(this.PrimaryKey))
.Select(x => Sanitizer.SanitizeIdentifierName(x.Key)));
string parameters = string.Join(",",
Enumerable.Range(0, item.Count - 1).Select(x => "@" + x));
var arguments =
item.Where(x => !x.Key.Equals(this.PrimaryKey)).Select(x => x.Value).ToArray();
var sql = new Sql("INSERT INTO " + this.FullyQualifiedObjectName + "(" + columns + ")");
sql.Append("SELECT " + parameters, arguments);
sql.Append(FrapidDbServer.AddReturnInsertedKey(this.Database, this.PrimaryKey));
result.Add(await db.ExecuteScalarAsync <object>(sql).ConfigureAwait(false));
}
}
transaction.Complete();
}
return(result);
}
}
catch (Exception ex)
{
string errorMessage = $"Error on line {line}. {ex.Message} ";
throw new DataAccessException(errorMessage, ex);
}
}
public void Append_GivenConsecutiveWheres_ShouldBeValid()
{
_sql = new Sql()
.Append("SELECT * FROM blah");
_sql.Append("WHERE x");
_sql.Append("WHERE y");
_sql.SQL.ShouldBe("SELECT * FROM blah\nWHERE x\nAND y");
}
public void FindTableByName(string username)
{
Sql.Append("select * from fv_user.vwObtenerUsuarios where username=:pName ");
base.FindByName(username);
}
public void Append_GivenConsecutiveOrderBys_ShouldBeValid()
{
_sql = new Sql()
.Append("SELECT * FROM blah");
_sql.Append("ORDER BY x");
_sql.Append("ORDER BY y");
_sql.SQL.ShouldBe("SELECT * FROM blah\nORDER BY x\n, y");
}
public new Employee FindById(string id)
{
Sql.Append("select * from fv_user.vwObtenerUsuarios where id_usuario=:pId ");
base.FindById(id);
return(GetEmployeeData());
}
/// <inheritdoc />
protected override Expression VisitBinary(BinaryExpression binaryExpression)
{
GaxPreconditions.CheckNotNull(binaryExpression, nameof(binaryExpression));
switch (binaryExpression.NodeType)
{
case ExpressionType.Add:
{
if (binaryExpression.Type == typeof(string))
{
Sql.Append("CONCAT(");
Visit(binaryExpression.Left);
Sql.Append(", ");
Visit(binaryExpression.Right);
Sql.Append(")");
return(binaryExpression);
}
break;
}
case ExpressionType.And:
{
BeginNumericReturnCast(binaryExpression);
Sql.Append("(");
VisitNumericOperand(binaryExpression.Left);
Sql.Append(" & ");
VisitNumericOperand(binaryExpression.Right);
Sql.Append(")");
EndNumericReturnCast(binaryExpression);
return(binaryExpression);
}
case ExpressionType.Or:
{
BeginNumericReturnCast(binaryExpression);
Sql.Append("(");
VisitNumericOperand(binaryExpression.Left);
Sql.Append(" | ");
VisitNumericOperand(binaryExpression.Right);
Sql.Append(")");
EndNumericReturnCast(binaryExpression);
return(binaryExpression);
}
case ExpressionType.ExclusiveOr:
{
BeginNumericReturnCast(binaryExpression);
Sql.Append("(");
VisitNumericOperand(binaryExpression.Left);
Sql.Append(" ^ ");
VisitNumericOperand(binaryExpression.Right);
Sql.Append(")");
EndNumericReturnCast(binaryExpression);
return(binaryExpression);
}
case ExpressionType.LeftShift:
{
BeginNumericReturnCast(binaryExpression);
Sql.Append("(");
VisitNumericOperand(binaryExpression.Left);
Sql.Append(" << ");
VisitNumericOperand(binaryExpression.Right);
Sql.Append(")");
EndNumericReturnCast(binaryExpression);
return(binaryExpression);
}
case ExpressionType.RightShift:
{
BeginNumericReturnCast(binaryExpression);
Sql.Append("(");
VisitNumericOperand(binaryExpression.Left);
Sql.Append(" >> ");
VisitNumericOperand(binaryExpression.Right);
Sql.Append(")");
EndNumericReturnCast(binaryExpression);
return(binaryExpression);
}
case ExpressionType.Modulo:
{
BeginNumericReturnCast(binaryExpression);
Sql.Append("MOD(");
VisitNumericOperand(binaryExpression.Left);
Sql.Append(", ");
VisitNumericOperand(binaryExpression.Right);
Sql.Append(")");
EndNumericReturnCast(binaryExpression);
return(binaryExpression);
}
}
return(base.VisitBinary(binaryExpression));
}
public override Expression VisitSelect(SelectExpression selectExpression)
{
Check.NotNull(selectExpression, nameof(selectExpression));
IDisposable subQueryIndent = null;
if (selectExpression.Alias != null)
{
Sql.AppendLine("(");
subQueryIndent = Sql.Indent();
}
Sql.Append("SELECT ");
if (selectExpression.IsDistinct)
{
Sql.Append("DISTINCT ");
}
GenerateTop(selectExpression);
var projectionAdded = false;
if (selectExpression.IsProjectStar)
{
var tableAlias = selectExpression.ProjectStarTable.Alias;
Sql
.Append(SqlGenerator.DelimitIdentifier(tableAlias))
.Append(".*");
projectionAdded = true;
}
if (selectExpression.Projection.Any())
{
if (selectExpression.IsProjectStar)
{
Sql.Append(", ");
}
ProcessExpressionList(selectExpression.Projection, GenerateProjection);
projectionAdded = true;
}
if (!projectionAdded)
{
Sql.Append("1");
}
if (selectExpression.Tables.Any())
{
Sql.AppendLine()
.Append("FROM ");
ProcessExpressionList(selectExpression.Tables, sql => sql.AppendLine());
}
else
{
Sql.Append(" FROM DUAL");
}
if (selectExpression.Predicate != null)
{
GeneratePredicate(selectExpression.Predicate);
}
if (selectExpression.OrderBy.Any())
{
Sql.AppendLine();
GenerateOrderBy(selectExpression.OrderBy);
}
GenerateLimitOffset(selectExpression);
if (subQueryIndent != null)
{
subQueryIndent.Dispose();
Sql.AppendLine()
.Append(")");
if (selectExpression.Alias.Length > 0)
{
Sql.Append(" ")
.Append(SqlGenerator.DelimitIdentifier(selectExpression.Alias));
}
}
return(selectExpression);
}
protected override void GeneratePseudoFromClause()
{
Sql.Append(" FROM RDB$DATABASE");
}
protected override Expression VisitSqlUnary(SqlUnaryExpression sqlUnaryExpression)
{
if (sqlUnaryExpression.OperatorType != ExpressionType.Convert)
{
return(base.VisitSqlUnary(sqlUnaryExpression));
}
var typeMapping = sqlUnaryExpression.TypeMapping;
var storeTypeLower = typeMapping.StoreType.ToLower();
string castMapping = null;
foreach (var kvp in _castMappings)
{
foreach (var storeType in kvp.Value)
{
if (storeTypeLower.StartsWith(storeType))
{
castMapping = kvp.Key;
break;
}
}
if (castMapping != null)
{
break;
}
}
if (castMapping == null)
{
throw new InvalidOperationException($"Cannot cast from type '{typeMapping.StoreType}'");
}
if (castMapping == "signed" && storeTypeLower.Contains("unsigned"))
{
castMapping = "unsigned";
}
// FLOAT and DOUBLE are supported by CAST() as of MySQL 8.0.17.
// For server versions before that, a workaround is applied, that casts to a DECIMAL,
// that is then added to 0e0, which results in a DOUBLE.
// REF: https://stackoverflow.com/a/32991084/2618319
var useDecimalToDoubleFloatWorkaround = false;
if (castMapping.StartsWith("double") && !_connectionInfo.ServerVersion.SupportsDoubleCast)
{
useDecimalToDoubleFloatWorkaround = true;
castMapping = "decimal(65,30)";
}
if (castMapping.StartsWith("float") && !_connectionInfo.ServerVersion.SupportsFloatCast)
{
useDecimalToDoubleFloatWorkaround = true;
castMapping = "decimal(65,30)";
}
if (useDecimalToDoubleFloatWorkaround)
{
Sql.Append("(");
}
Sql.Append("CAST(");
Visit(sqlUnaryExpression.Operand);
Sql.Append(" AS ");
Sql.Append(castMapping);
Sql.Append(")");
if (useDecimalToDoubleFloatWorkaround)
{
Sql.Append(" + 0e0)");
}
return(sqlUnaryExpression);
}
protected override Expression VisitSqlBinary(SqlBinaryExpression sqlBinaryExpression)
{
if (sqlBinaryExpression.OperatorType == ExpressionType.Modulo)
{
Sql.Append("MOD(");
Visit(sqlBinaryExpression.Left);
Sql.Append(", ");
Visit(sqlBinaryExpression.Right);
Sql.Append(")");
return(sqlBinaryExpression);
}
else if (sqlBinaryExpression.OperatorType == ExpressionType.And && sqlBinaryExpression.TypeMapping.ClrType != typeof(bool))
{
Sql.Append("BIN_AND(");
Visit(sqlBinaryExpression.Left);
Sql.Append(", ");
Visit(sqlBinaryExpression.Right);
Sql.Append(")");
return(sqlBinaryExpression);
}
else if (sqlBinaryExpression.OperatorType == ExpressionType.Or && sqlBinaryExpression.TypeMapping.ClrType != typeof(bool))
{
Sql.Append("BIN_OR(");
Visit(sqlBinaryExpression.Left);
Sql.Append(", ");
Visit(sqlBinaryExpression.Right);
Sql.Append(")");
return(sqlBinaryExpression);
}
else if (sqlBinaryExpression.OperatorType == ExpressionType.ExclusiveOr)
{
Sql.Append("BIN_XOR(");
Visit(sqlBinaryExpression.Left);
Sql.Append(", ");
Visit(sqlBinaryExpression.Right);
Sql.Append(")");
return(sqlBinaryExpression);
}
else if (sqlBinaryExpression.OperatorType == ExpressionType.LeftShift)
{
Sql.Append("BIN_SHL(");
Visit(sqlBinaryExpression.Left);
Sql.Append(", ");
Visit(sqlBinaryExpression.Right);
Sql.Append(")");
return(sqlBinaryExpression);
}
else if (sqlBinaryExpression.OperatorType == ExpressionType.RightShift)
{
Sql.Append("BIN_SHR(");
Visit(sqlBinaryExpression.Left);
Sql.Append(", ");
Visit(sqlBinaryExpression.Right);
Sql.Append(")");
return(sqlBinaryExpression);
}
else
{
return(base.VisitSqlBinary(sqlBinaryExpression));
}
}
/// <summary>
/// 通用的获取SQL的方法
/// </summary>
/// <param name="tenantTypeId">租户类型id</param>
/// <param name="descriptionKeyword">描述性字段</param>
/// <param name="userId">用户id</param>
/// <param name="ownerId">拥有者id</param>
/// <param name="isEssential">是否精华</param>
/// <param name="auditStatus">审核状态</param>
/// <param name="tagName">标签名</param>
/// <param name="ignoreAuditAndPrivacy">是否忽略审核和隐私状态</param>
/// <param name="albumId">相册id</param>
/// <param name="sortBy">排序方式</param>
/// <returns>SQL</returns>
private Sql GetSql_GetPhotos(string tenantTypeId, string descriptionKeyword, long?userId, long?ownerId, bool?isEssential, DateTime?createDateTime
, AuditStatus?auditStatus, string tagName, bool?ignoreAuditAndPrivacy, long?albumId, SortBy_Photo sortBy)
{
Sql sql = Sql.Builder;
Sql sql_Where = Sql.Builder;
Sql sql_Orderby = Sql.Builder;
sql.Select("spb_Photos.*")
.From("spb_Photos");
if (!string.IsNullOrEmpty(tenantTypeId))
{
sql_Where.Where("spb_Photos.TenantTypeId = @0", tenantTypeId);
}
if (!string.IsNullOrEmpty(descriptionKeyword))
{
sql_Where.Where("spb_Photos.Description like @0", "%" + StringUtility.StripSQLInjection(descriptionKeyword) + "%");
}
if (userId.HasValue)
{
sql_Where.Where("spb_Photos.UserId = @0", userId.Value);
}
if (ownerId.HasValue)
{
sql_Where.Where("spb_Photos.OwnerId = @0", ownerId.Value);
}
if (auditStatus.HasValue)
{
sql_Where.Where("spb_Photos.AuditStatus = @0", auditStatus.Value);
}
if (isEssential.HasValue)
{
sql_Where.Where("spb_Photos.IsEssential = @0", isEssential);
}
if (createDateTime.HasValue)
{
sql_Where.Where("spb_Photos.DateCreated > @0", createDateTime);
}
if (!string.IsNullOrEmpty(tagName))
{
sql.InnerJoin("tn_ItemsInTags")
.On("spb_Photos.PhotoId = tn_ItemsInTags.ItemId");
sql_Where.Where("tn_ItemsInTags.TagName=@0", tagName)
.Where("tn_ItemsInTags.TenantTypeId = @0", TenantTypeIds.Instance().Photo());
}
if (ignoreAuditAndPrivacy.HasValue && !ignoreAuditAndPrivacy.Value)
{
switch (this.PubliclyAuditStatus)
{
case PubliclyAuditStatus.Again:
case PubliclyAuditStatus.Fail:
case PubliclyAuditStatus.Pending:
case PubliclyAuditStatus.Success:
sql_Where.Where("spb_Photos.AuditStatus = @0", this.PubliclyAuditStatus);
break;
case PubliclyAuditStatus.Pending_GreaterThanOrEqual:
case PubliclyAuditStatus.Again_GreaterThanOrEqual:
sql_Where.Where("spb_Photos.AuditStatus > @0", this.PubliclyAuditStatus);
break;
}
sql_Where.Where("spb_Photos.PrivacyStatus <> @0", PrivacyStatus.Private);
}
if (albumId.HasValue)
{
sql_Where.Where("spb_Photos.AlbumId = @0", albumId);
}
CountService countService = new CountService(TenantTypeIds.Instance().Photo());
string countTableName = countService.GetTableName_Counts();
switch (sortBy)
{
case SortBy_Photo.DateCreated_Desc:
sql_Orderby.OrderBy("spb_Photos.PhotoId desc");
break;
case SortBy_Photo.HitTimes_Desc:
sql.LeftJoin(countTableName).On("spb_Photos.PhotoId = " + countTableName + ".ObjectId");
sql_Where.Where(countTableName + ".CountType = @0 or " + countTableName + ".CountType is null", CountTypes.Instance().HitTimes());
sql_Orderby.OrderBy(countTableName + ".StatisticsCount desc");
break;
case SortBy_Photo.CommentCount_Desc:
sql.LeftJoin(countTableName).On("spb_Photos.PhotoId = " + countTableName + ".ObjectId");
sql_Where.Where(countTableName + ".CountType = @0 or " + countTableName + ".CountType is null", CountTypes.Instance().CommentCount());
sql_Orderby.OrderBy(countTableName + ".StatisticsCount desc");
break;
case SortBy_Photo.SupportCount_Desc:
sql.LeftJoin("tn_Attitudes").On("spb_Photos.PhotoId = tn_Attitudes.ObjectId");
sql_Where.Where("tn_Attitudes.TenantTypeId = @0", TenantTypeIds.Instance().Photo());
sql_Orderby.OrderBy("tn_Attitudes.SupportCount desc");
break;
}
sql.Append(sql_Where)
.Append(sql_Orderby);
return(sql);
}
/// <summary>
/// 清除指定用户的用户数据
/// </summary>
/// <param name="ownerId">用户Id</param>
/// <param name="tenantTypeId">租户类型Id</param>
public void ClearOwnerData(long ownerId, string tenantTypeId)
{
Sql sql = Sql.Builder;
sql.Append("delete from tn_OwnerData where OwnerId = @0 and TenantTypeId = @1", ownerId, tenantTypeId);
}
public UIOMaticPagedResult GetPaged(string typeName, int itemsPerPage, int pageNumber, string sortColumn,
string sortOrder, string searchTerm)
{
var currentType = Type.GetType(typeName);
var tableName = (TableNameAttribute)Attribute.GetCustomAttribute(currentType, typeof(TableNameAttribute));
var uioMaticAttri = (UIOMaticAttribute)Attribute.GetCustomAttribute(currentType, typeof(UIOMaticAttribute));
var db = (Database)DatabaseContext.Database;
if (!string.IsNullOrEmpty(uioMaticAttri.ConnectionStringName))
db = new Database(uioMaticAttri.ConnectionStringName);
var query = new Sql().Select("*").From(tableName.Value);
EventHandler<QueryEventArgs> tmp = BuildingQuery;
if (tmp != null)
tmp(this, new QueryEventArgs(tableName.Value, query));
if (!string.IsNullOrEmpty(searchTerm))
{
int c = 0;
foreach (var property in currentType.GetProperties())
{
//if (property.PropertyType == typeof (string))
//{
string before = "WHERE";
if (c > 0)
before = "OR";
var columnAttri =
property.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));
var columnName = property.Name;
if (columnAttri.Any())
columnName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
query.Append(before + " [" + columnName + "] like @0", "%" + searchTerm + "%");
c++;
//}
}
}
if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder))
query.OrderBy(sortColumn + " " + sortOrder);
else
{
var primaryKeyColum = "id";
var primKeyAttri = currentType.GetCustomAttributes().Where(x => x.GetType() == typeof(PrimaryKeyAttribute));
if (primKeyAttri.Any())
primaryKeyColum = ((PrimaryKeyAttribute)primKeyAttri.First()).Value;
foreach (var property in currentType.GetProperties())
{
var keyAttri = property.GetCustomAttributes().Where(x => x.GetType() == typeof(PrimaryKeyColumnAttribute));
if (keyAttri.Any())
primaryKeyColum = property.Name;
}
query.OrderBy(primaryKeyColum + " asc");
}
EventHandler<QueryEventArgs> temp = BuildedQuery;
if (temp != null)
temp(this, new QueryEventArgs(tableName.Value,query));
var p = db.Page<dynamic>(pageNumber, itemsPerPage, query);
var result = new UIOMaticPagedResult
{
CurrentPage = p.CurrentPage,
ItemsPerPage = p.ItemsPerPage,
TotalItems = p.TotalItems,
TotalPages = p.TotalPages
};
var items = new List<object>();
foreach (dynamic item in p.Items)
{
// get settable public properties of the type
var props = currentType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(x => x.GetSetMethod() != null);
// create an instance of the type
var obj = Activator.CreateInstance(currentType);
// set property values using reflection
var values = (IDictionary<string, object>)item;
foreach (var prop in props)
{
var columnAttri =
prop.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));
var propName = prop.Name;
if (columnAttri.Any())
propName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
if(values.ContainsKey(propName))
prop.SetValue(obj, values[propName]);
}
items.Add(obj);
}
result.Items = items;
return result;
}
public void Append_GivenConsecutiveSets_ShouldBeValid()
{
_sql = new Sql()
.Append("UPDATE blah");
_sql.Append("SET a = 1");
_sql.Append("SET b = 2");
_sql.SQL.ShouldBe("UPDATE blah\nSET a = 1\n, b = 2");
}
