/*
Kooboo is a content management system based on ASP.NET MVC framework. Copyright 2009 Yardi Technology Limited.
This program is free software: you can redistribute it and/or modify it under the terms of the
GNU General Public License version 3 as published by the Free Software Foundation.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program.
If not, see http://www.kooboo.com/gpl3/.
*/
using System;
using System.Data;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.Data.Extensions;
using Everest.Library;
using Everest.Library.Data;
using Everest.Library.Data.Entity;
using Everest.Library.ExtensionMethod;
using Everest.CmsServices.Models;
using Everest.CmsServices.Services;
using Everest.CmsServices.DataRule;
using Everest.Library.Providers.Logging;
using Everest.Library.Providers.Caching;
namespace Everest.CmsServices.Providers.SqlServer{
public class SqlServerBinaryContentQuerier : IBinaryContentQuerier
{
public SqlServerBinaryContentQuerier()
{
SetQueryType(QueryType.Published);
}
#region ITextContentQuerier Members
public ContentStatus? QueriedContentStatus
{
get;
set;
}
public IBinaryContentQuerier SetQueryType(QueryType queryType)
{
switch (queryType)
{
case QueryType.All:
QueriedContentStatus = null;
break;
case QueryType.Published:
QueriedContentStatus = ContentStatus.Published;
break;
case QueryType.Unpublished:
QueriedContentStatus = ContentStatus.Draft;
break;
default:
break;
}
return this;
}
#endregion
#region IBinaryContentQuerier Members
public virtual IEnumerable<IDictionary<string, object>> QueryContents(IEverestCmsDataContext dataContext, string queryStatement,
string orderBy, IDictionary<string, object> parameterValues, Cms_Folder folder, IEnumerable<Cms_Folder> folders, int startIndex,
int limit, TimeSpan? cacheTime)
{
if (startIndex < 1)
{
startIndex = 1;
}
if (limit == int.MaxValue)
{
limit = limit - 1;
}
if (StringExtensions.IsNullOrEmptyTrim(orderBy))
{
orderBy = "ContentId DESC";
}
string cacheKey = string.Empty;
List<IDictionary<string, object>> data = null;
if (cacheTime.HasValue)
{
cacheKey = string.Format(@"SqlServerBinaryContentQuerier.QueryContents_queryStatement:{0}_orderBy:{1}_
queryString:{2}_folder:{3}_folders:{4}_startIndex:{5}_limit:{6}_cacheTime:{7}",
queryStatement == null ? "" : queryStatement, orderBy, parameterValues == null ? "" : parameterValues.ToKeyValueString(),
folder == null ? "" : folder.UUID.ToString(), folders == null ? "" : string.Join(",", folders.Select(f => folder.UUID.ToString()).ToArray()),
startIndex, limit, cacheTime.Value.ToString());
data = CacheManager.Get(CachedData.Content, cacheKey) as List<IDictionary<string, object>>;
if (data != null)
{
return data;
}
}
StringBuilder queryExpression = new StringBuilder("1=1");
DbCommand dbCommand = dataContext.ObjectContext.CreateStoreCommand("a", CommandType.Text);
ContentProviderHelper.CombineUserCondition(queryStatement, parameterValues, dbCommand, queryExpression);
queryExpression.AppendFormat(" AND {0}", ContentProviderHelper.FoldersQueryExpresion(folders, dbCommand));
if (QueriedContentStatus.HasValue)
{
queryExpression.AppendFormat(" AND Cms_Content.ContentStatus = @ContentStatus");
dbCommand.CreateParameter("@ContentStatus", DbType.Int32, (int)QueriedContentStatus.Value);
}
string includeFolder = "Cms_Content";
if (folder != null)
{
includeFolder = @"(SELECT Cms_Content.* FROM Cms_Content LEFT JOIN Cms_ContentInFolder
ON Cms_ContentInFolder.ContentUUID = Cms_Content.UUID
WHERE (Cms_Content.FolderUUID = @FolderUUID OR Cms_ContentInFolder.FolderUUID = @FolderUUID))";
dbCommand.CreateParameter("FolderUUID", DbType.Guid, folder.UUID);
}
dbCommand.CommandText = BuildQueryContentsSQL(queryExpression.ToString(), orderBy, includeFolder);
dbCommand.CreateParameter("startIndex", DbType.Int32, startIndex);
dbCommand.CreateParameter("limit", DbType.Int32, limit);
SqlServerContentProvider.Log("SqlServerBinaryContentQuerier.QueryContents", dbCommand);
using (dbCommand.Connection.CreateConnectionScope())
{
IDataReader dataReader = dbCommand.ExecuteReader();
data = new List<IDictionary<string, object>>();
while (dataReader.Read())
{
IDictionary<string, object> dic = DataReaderToDictionary(dataReader, folder);
data.Add(dic);
}
}
if (cacheTime.HasValue)
{
CacheManager.Add(CachedData.Content, cacheKey, data, CacheItemPriority.None, null, new SlidingTime(cacheTime.Value));
}
return data;
}
protected virtual string BuildQueryContentsSQL(string queryExpression, string orderBy, string includeFolder)
{
string sql = @"
SELECT * ,(SELECT ApplicationName FROM aspnet_Applications WHERE aspnet_Applications.ApplicationId=E.ApplicationId) AS [Application]
FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY {1}) AS RowIndex
FROM (SELECT A.ContentId,A.UUID,A.ParentUUID,A.Title,A.UserKey,A.ApplicationId,A.FolderUUID,A.SchemaUUID,
A.UserName,A.BaseUUID,A.ModifiedDate,A.PostDate,A.ContentStatus,A.OriginalUUID,B.FileSize,B.FilePath
FROM
(SELECT MAX(Cms_Content.FolderLevel) AS MaxFolderLevel,Cms_Content.OriginalUUID AS MaxOriginalUUID
FROM {2} Cms_Content,
Cms_BinaryContent
WHERE Cms_Content.UUID = [Cms_BinaryContent].ContentUUID AND {0}
Group By OriginalUUID) C,
{2} A,
Cms_BinaryContent B
WHERE A.UUID = B.ContentUUID AND A.FolderLevel = C.MaxFolderLevel AND A.OriginalUUID=C.MaxOriginalUUID
)D
)E
WHERE (RowIndex >= @startIndex) AND (RowIndex < @startIndex + @limit)";
return string.Format(sql, queryExpression, orderBy, includeFolder);
}
public virtual int CountContents(IEverestCmsDataContext dataContext, string queryStatement, IDictionary<string, object> parameterValues,
Cms_Folder folder, IEnumerable<Cms_Folder> folders, TimeSpan? cacheTime)
{
string cacheKey = string.Empty;
int? data = null;
if (cacheTime.HasValue)
{
cacheKey = string.Format(@"SqlServerBinaryContentQuerier.CountContents_queryStatement:{0}_
queryString:{1}_folder:{3}_folders:{4}_cacheTime:{5}",
queryStatement == null ? "" : queryStatement, parameterValues == null ? "" : parameterValues.ToKeyValueString(),
folder == null ? "" : folder.UUID.ToString(), folders == null ? "" : string.Join(",", folders.Select(f => folder.UUID.ToString()).ToArray()),
cacheTime.Value.ToString());
data = CacheManager.Get(CachedData.Content, cacheKey) as int?;
if (data != null)
{
return data.Value;
}
}
StringBuilder queryExpression = new StringBuilder("1=1");
DbCommand dbCommand = dataContext.ObjectContext.CreateStoreCommand("a", CommandType.Text);
ContentProviderHelper.CombineUserCondition(queryStatement, parameterValues, dbCommand, queryExpression);
queryExpression.AppendFormat(" AND {0}", ContentProviderHelper.FoldersQueryExpresion(folders, dbCommand));
if (QueriedContentStatus.HasValue)
{
queryExpression.AppendFormat(" AND Cms_Content.ContentStatus = @ContentStatus");
dbCommand.CreateParameter("@ContentStatus", DbType.Int32, (int)QueriedContentStatus.Value);
}
var includeFolder = @"(SELECT Cms_Content.* FROM Cms_Content LEFT JOIN Cms_ContentInFolder
ON Cms_ContentInFolder.ContentUUID = Cms_Content.UUID
WHERE (Cms_Content.FolderUUID = @FolderUUID OR Cms_ContentInFolder.FolderUUID = @FolderUUID))";
dbCommand.CreateParameter("FolderUUID", DbType.Guid, folder.UUID);
dbCommand.CommandText = BuildCountContentSQL(queryExpression.ToString(), includeFolder);
SqlServerContentProvider.Log("SqlServerBinaryContentQuerier.CountContents", dbCommand);
using (dbCommand.Connection.CreateConnectionScope())
{
object o = dbCommand.ExecuteScalar();
if (o != null)
{
data = int.Parse(o.ToString());
}
}
if (cacheTime.HasValue)
{
CacheManager.Add(CachedData.Content, cacheKey, data, CacheItemPriority.None, null, new SlidingTime(cacheTime.Value));
}
return data.Value;
}
protected virtual string BuildCountContentSQL(string queryExpression, string includeFolder)
{
string sql = @"
SELECT COUNT(*)
FROM (SELECT MAX(Cms_Content.FolderLevel) AS MaxFolderLevel,Cms_Content.OriginalUUID AS MaxOriginalUUID
FROM {1} Cms_Content,
Cms_BinaryContent
WHERE Cms_Content.UUID = [Cms_BinaryContent].ContentUUID AND {0}
Group By OriginalUUID
) C ";
return string.Format(sql, queryExpression, includeFolder);
}
#endregion
private static IDictionary<string, object> DataReaderToDictionary(IDataReader dataReader, Cms_Folder folder)
{
IDictionary<string, object> dic = ContentProviderHelper.ToDictionary(dataReader);
if (folder != null && dic["FolderUUID"] != DBNull.Value)
{
dic["IsLocalized"] = (Guid)dic["FolderUUID"] == folder.UUID;
}
dic["FileUrl"] = dic["FilePath"].ToString().Replace("\\", "/");
return dic;
}
#region IBinaryContentQuerier Members
public IDictionary<string, object> QueryContent(IEverestCmsDataContext dataContext, string queryStatement, string orderBy,
IDictionary<string, object> parameterValues, TimeSpan? cacheTime)
{
return QueryContents(dataContext, queryStatement, orderBy, parameterValues, null, new Cms_Folder[0], 0, 1, cacheTime).FirstOrDefault();
}
#endregion
}
}
|