public override string BuildQuerySQL(SQLCeVisitor<TextContent> visitor, out IEnumerable<Parameter> parameters)
{
var innerVisitor = new SQLCeVisitor<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(SQLCeVisitor<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;
}
public override string BuildQuerySQL(SQLCeVisitor<Models.TextContent> visitor, out IEnumerable<Parameter> parameters)
{
var innerVisitor = new SQLCeVisitor<Models.TextContent>(visitor.Parameters);
innerVisitor.Visite(parentQuery.ChildrenQuery.Expression);
var innerExecutor = (new TextContentTranslator()).Translate(parentQuery.ChildrenQuery);
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 (parentQuery.ParentFolder != null)
{
var paraName = visitor.AppendParameter(parentQuery.ParentFolder.FullName);
whereClause = whereClause + " AND FolderName=" + paraName;
}
//var paraName = visitor.AppendParameter(parentQuery.ChildrenQuery.FullName);
//whereClause = whereClause + "AND FolderName=" + paraName;
string sql = string.Format(@"
SELECT {0} FROM [{1}] parent
WHERE EXISTS(
SELECT UUID
FROM ({2})children
WHERE parent.UUID = children.ParentUUID
) AND {3}", selectClause
, parentQuery.ParentSchema.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 SQLCeVisitor<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;
}