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);
}
private IEnumerable <T> List(SQLCeVisitor <T> visitor)
{
var command = BuildCommand(visitor);
var connectionString = ContentQuery.Repository.GetConnectionString();
List <T> list = new List <T>();
SqlCeConnection connection;
using (var dataReader = SQLCeHelper.ExecuteReader(connectionString, command, out connection))
{
try
{
while (dataReader.Read())
{
T content = new T();
list.Add(dataReader.ToContent(content));
}
}
finally
{
dataReader.Close();
connection.Close();
}
}
return(list);
}
public virtual object Execute()
{
SQLCeVisitor <T> visitor = new SQLCeVisitor <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(SQLCeVisitor <T> visitor)
{
var content = LastOrDefault(visitor);
if (content == null)
{
throw new InvalidOperationException(SR.GetString("NoElements"));
}
return(content);
}
private SQLCeVisitor <T> VisitInner(IExpression expression)
{
SQLCeVisitor <T> visitor = new SQLCeVisitor <T>(this.Parameters);
visitor.Visite(expression);
//combine the order expressions.
this.OrderClauses.AddRange(visitor.OrderClauses);
this.CategoryQueries = this.CategoryQueries.Concat(visitor.CategoryQueries);
return(visitor);
}
private int Count(SQLCeVisitor <T> visitor)
{
IEnumerable <Parameter> parameters;
var sql = BuildQuerySQL(visitor, out parameters);
var connectionString = ContentQuery.Repository.GetConnectionString();
sql = string.Format("SELECT COUNT(Id) FROM ({0})T", sql);
var command = BuildCommand(sql, parameters);
return((int)SQLCeHelper.ExecuteScalar(connectionString, command));
}
private T LastOrDefault(SQLCeVisitor <T> visitor)
{
IEnumerable <Parameter> parameters;
var sql = BuildQuerySQL(visitor, out parameters);
var connectionString = ContentQuery.Repository.GetConnectionString();
string orderBy = "Id ASC";
if (visitor.OrderClauses != null && visitor.OrderClauses.Count > 0)
{
orderBy = ToReverseOrderString(visitor);
}
if (visitor.Skip != 0)
{
sql = string.Format("SELECT * FROM ({0})T ORDER BY {1} OFFSET {2} ROWS FETCH NEXT 1 ROWS ONLY",
sql, orderBy, visitor.Skip);
}
else
{
sql = string.Format("SELECT TOP 1 * FROM ({0})T ORDER BY {1}", sql, orderBy);
}
var command = BuildCommand(sql, parameters);
T content = null;
SqlCeConnection connection;
using (var dataReader = SQLCeHelper.ExecuteReader(connectionString, command, out connection))
{
try
{
if (dataReader.Read())
{
content = new T();
dataReader.ToContent(content);
}
}
finally
{
dataReader.Close();
connection.Close();
}
}
return(content);
}
protected virtual SqlCeCommand BuildCommand(SQLCeVisitor <T> visitor)
{
string selectClause = "*";
if (visitor.SelectFields != null && visitor.SelectFields.Length > 0)
{
selectClause = string.Join(",", visitor.SelectFields);
}
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 = string.Format("{0} ORDER BY {1} OFFSET {2} ROWS",
BuildQuerySQL(visitor, out parameters), orderBy, visitor.Skip);
if (visitor.Take != 0)
{
sql = sql + string.Format(" FETCH NEXT {0} ROWS ONLY", visitor.Take);
}
var command = new SqlCeCommand();
command.CommandText = sql;
foreach (var item in parameters)
{
command.Parameters.Add(new SqlCeParameter(item.Name, item.Value));
}
return(BuildCommand(sql, parameters));
}
public override string BuildQuerySQL(SQLCeVisitor <Models.TextContent> visitor, out IEnumerable <Parameter> parameters)
{
var innerVisitor = new SQLCeVisitor <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 (childrenQuery.EmbeddedFolder != null)
{
var paraName = visitor.AppendParameter(childrenQuery.EmbeddedFolder.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}] children
WHERE EXISTS(
SELECT UUID
FROM ({2})parent
WHERE parent.UUID = children.ParentUUID
) AND {3}", selectClause
, childrenQuery.ChildSchema.GetTableName()
, innerQuerySQL
, whereClause);
return(sql);
}
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 <MediaContent> visitor, out IEnumerable <Parameter> parameters)
{
string selectClause = "*";
if (visitor.SelectFields != null && visitor.SelectFields.Length > 0)
{
selectClause = string.Join(",", visitor.SelectFields);
}
string whereClause = visitor.WhereClause;
var paraName = visitor.AppendParameter(mediaContentQuery.MediaFolder.FullName);
whereClause = whereClause + "AND FolderName=" + paraName;
parameters = visitor.Parameters;
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} ", selectClause
, mediaContentQuery.Repository.GetMediaContentTableName()
, 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);
}
public abstract string BuildQuerySQL(SQLCeVisitor <T> visitor, out IEnumerable <Parameter> parameters);
protected static string ToReverseOrderString(SQLCeVisitor <T> visitor)
{
return(string.Join(",", visitor.OrderClauses.Distinct(new OrderClauseComparer()).Select(it => "[" + it.FieldName + "] " + (it.Descending ? "ASC" : "DESC")).ToArray()));
}
