public override string BuildQuerySQL(MysqlVisitor <TextContent> visitor, out IEnumerable <Parameter> parameters) { string selectClause = "*"; if (visitor.SelectFields != null && visitor.SelectFields.Length > 0) { selectClause = string.Join(",", visitor.SelectFields); } string whereClause = visitor.WhereClause; if (textContentQuery.Folder != null) { var paraName = visitor.AppendParameter(textContentQuery.Folder.FullName); whereClause = whereClause + " AND FolderName=" + paraName; } var categoryClauses = string.Join(" AND ", GetCategoryClause(textContentQuery.Repository, visitor.CategoryQueries, visitor.Parameters).ToArray()); if (!string.IsNullOrEmpty(categoryClauses)) { whereClause = whereClause + " AND " + categoryClauses; } parameters = visitor.Parameters; string sql = string.Format("SELECT {0} FROM `{1}` content WHERE {2} ", selectClause, textContentQuery.Schema.GetTableName(), whereClause); return(sql); }
protected virtual MySqlCommand BuildCommand(MysqlVisitor <T> visitor) { string whereClause = "1=1"; if (!string.IsNullOrEmpty(visitor.WhereClause)) { whereClause = visitor.WhereClause; } string orderBy = "Id DESC"; if (visitor.OrderClauses != null && visitor.OrderClauses.Count() > 0) { orderBy = ToOrderString(visitor); } IEnumerable <Parameter> parameters; string sql = BuildQuerySQL(visitor, out parameters); sql = sql + " ORDER BY " + orderBy; if (visitor.Take != 0) { sql = string.Format(@" SELECT * FROM ({0})content limit {1},{2} " , sql , visitor.Skip , visitor.Take); } return(BuildCommand(sql, parameters)); }
private T FirstOrDefault(MysqlVisitor <T> visitor) { IEnumerable <Parameter> parameters; var sql = BuildQuerySQL(visitor, out parameters); string orderBy = "Id ASC"; if (visitor.OrderClauses != null && visitor.OrderClauses.Count > 0) { orderBy = ToOrderString(visitor); } sql = string.Format("SELECT * FROM ({0})T ORDER BY {1} limit {2},1", sql, orderBy, visitor.Skip); var command = BuildCommand(sql, parameters); T content = default(T); MySqlConnection connection; using (var dataReader = MysqlHelper.ExecuteReader(ContentQuery.Repository, command, out connection)) { if (dataReader.Read()) { content = new T(); dataReader.ToContent(content); } dataReader.Close(); connection.Close(); } return(content); }
public virtual object Execute() { MysqlVisitor <T> visitor = new MysqlVisitor <T>(); visitor.Visite(ContentQuery.Expression); switch (visitor.CallType) { case Kooboo.CMS.Content.Query.Expressions.CallType.Count: return(Count(visitor)); case Kooboo.CMS.Content.Query.Expressions.CallType.First: return(First(visitor)); case Kooboo.CMS.Content.Query.Expressions.CallType.LastOrDefault: return(LastOrDefault(visitor)); case Kooboo.CMS.Content.Query.Expressions.CallType.FirstOrDefault: return(FirstOrDefault(visitor)); case Kooboo.CMS.Content.Query.Expressions.CallType.Last: return(Last(visitor)); case Kooboo.CMS.Content.Query.Expressions.CallType.Unspecified: default: return(List(visitor)); } }
private T Last(MysqlVisitor <T> visitor) { var content = LastOrDefault(visitor); if (content == null) { throw new InvalidOperationException(SR.GetString("NoElements")); } return(content); }
private MysqlVisitor <T> VisitInner(IExpression expression) { MysqlVisitor <T> visitor = new MysqlVisitor <T>(this.Parameters); visitor.Visite(expression); this.CategoryQueries = this.CategoryQueries.Concat(visitor.CategoryQueries); return(visitor); }
private int Count(MysqlVisitor <T> visitor) { IEnumerable <Parameter> parameters; var sql = BuildQuerySQL(visitor, out parameters); sql = string.Format("SELECT COUNT(Id) FROM ({0})T", sql); var command = BuildCommand(sql, parameters); return((int)(long)MysqlHelper.ExecuteScalar(ContentQuery.Repository, command)); }
private IEnumerable <T> List(MysqlVisitor <T> visitor) { var command = BuildCommand(visitor); List <T> list = new List <T>(); MySqlConnection connection; using (var dataReader = MysqlHelper.ExecuteReader(ContentQuery.Repository, command, out connection)) { while (dataReader.Read()) { T content = new T(); list.Add(dataReader.ToContent(content)); } dataReader.Close(); connection.Close(); } return(list); }
public override string BuildQuerySQL(MysqlVisitor <TextContent> visitor, out IEnumerable <Parameter> parameters) { var innerVisitor = new MysqlVisitor <Models.TextContent>(visitor.Parameters); innerVisitor.Visite(categoriesQuery.InnerQuery.Expression); var innerExecutor = (new TextContentTranslator()).Translate(categoriesQuery.InnerQuery); var innerQuerySQL = innerExecutor.BuildQuerySQL(innerVisitor, out parameters); string selectClause = "*"; if (visitor.SelectFields != null && visitor.SelectFields.Length > 0) { selectClause = string.Join(",", visitor.SelectFields); } string whereClause = visitor.WhereClause; var paraName = visitor.AppendParameter(categoriesQuery.CategoryFolder.FullName); whereClause = whereClause + " AND FolderName=" + paraName; string sql = string.Format(@" SELECT {0} FROM `{1}` category WHERE EXISTS( SELECT ContentCategory.CategoryUUID FROM `{2}` ContentCategory, ({3})content WHERE content.UUID = ContentCategory.UUID AND ContentCategory.CategoryUUID = category.UUID ) AND {4}", selectClause , categoriesQuery.CategoryFolder.GetSchema().GetTableName() , categoriesQuery.Repository.GetCategoryTableName() , innerQuerySQL , whereClause); return(sql); }
public override string BuildQuerySQL(MysqlVisitor <Models.TextContent> visitor, out IEnumerable <Parameter> parameters) { var innerVisitor = new MysqlVisitor <Models.TextContent>(visitor.Parameters); innerVisitor.Visite(childrenQuery.ParentQuery.Expression); var innerExecutor = (new TextContentTranslator()).Translate(childrenQuery.ParentQuery); var innerQuerySQL = innerExecutor.BuildQuerySQL(innerVisitor, out parameters); string selectClause = "*"; if (visitor.SelectFields != null && visitor.SelectFields.Length > 0) { selectClause = string.Join(",", visitor.SelectFields); } string whereClause = visitor.WhereClause; if (this.childrenQuery.EmbeddedFolder != null) { string str = visitor.AppendParameter(this.childrenQuery.EmbeddedFolder.FullName); whereClause = whereClause + " AND FolderName=" + str; } string sql = string.Format(@" SELECT {0} FROM `{1}` children WHERE EXISTS( SELECT UUID FROM ({2})parent WHERE parent.UUID = children.ParentUUID ) AND {3}", selectClause , childrenQuery.ChildSchema.GetTableName() , innerQuerySQL , whereClause); return(sql); }
private IEnumerable <string> GetCategoryClause(Repository repository, IEnumerable <IContentQuery <TextContent> > categoryQueries, List <Parameter> parameters) { TextContentTranslator translator = new TextContentTranslator(); List <string> categoryQueryList = new List <string>(); foreach (var categoryQuery in categoryQueries) { var executor = translator.Translate(categoryQuery); var categoryVisitor = new MysqlVisitor <TextContent>(parameters); categoryVisitor.Visite(categoryQuery.Expression); IEnumerable <Parameter> outParameters; var categoryQuerySQL = executor.BuildQuerySQL(categoryVisitor, out outParameters); categoryQueryList.Add(string.Format(@"EXISTS( SELECT ContentCategory.CategoryUUID FROM `{0}` ContentCategory, ({1})category WHERE content.UUID = ContentCategory.UUID AND ContentCategory.CategoryUUID = category.UUID )", repository.GetCategoryTableName() , categoryQuerySQL)); } return(categoryQueryList); }
public abstract string BuildQuerySQL(MysqlVisitor <T> visitor, out IEnumerable <Parameter> parameters);
protected static string ToReverseOrderString(MysqlVisitor <T> visitor) { return(string.Join(",", visitor.OrderClauses.Distinct(new OrderClauseComparer()).Select(it => "`" + it.FieldName + "` " + (it.Descending ? "ASC" : "DESC")).ToArray())); }