/*
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.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.Data.Extensions;
using Everest.Library.Data;
using Everest.Library.Data.Entity;
using Everest.CmsServices.Models;
using Everest.Library.ExtensionMethod;
using Everest.CmsServices.Search;
using System.IO;
namespace Everest.CmsServices.Providers.SqlServer{
public class SqlSystemManager : ISystemManager
{
protected class ExportedTableOption
{
public string TableName { get; set; }
public string PrimaryKey { get; set; }
public string ForeignKey { get; set; }
public string OrderBy { get; set; }
public string Condition { get; set; }
public IList<ExportedTableOption> SubTables { get; set; }
}
#region ISystemManager Members
public virtual void DeleteApplication(IEverestCmsDataContext dataContext, string app)
{
var command = dataContext.ObjectContext.CreateStoreCommand("Cms_DeleteApplication", CommandType.StoredProcedure);
var p = command.CreateParameter("ApplicationName", DbType.String, app);
using (command.Connection.CreateConnectionScope())
{
var dbTrans = dataContext.ObjectContext.GetCurrentStoreTransaction();
if (dbTrans != null)
{
command.Transaction = dbTrans;
}
command.ExecuteNonQuery();
}
}
public virtual void IncludeFolderItems(IEverestCmsDataContext dataContext, Guid folderUUID, Guid baseFolderUUID)
{
string sql = GetIncludeFolderItemsSQL();
var command = dataContext.ObjectContext.CreateStoreCommand(sql, CommandType.Text);
command.CreateParameter("@FolderUUID", DbType.Guid, folderUUID);
command.CreateParameter("@BaseFolderUUID", DbType.Guid, baseFolderUUID);
command.CreateParameter("@ChangeDate", DbType.DateTime, DateTime.Now);
using (command.Connection.CreateConnectionScope())
{
var dbTrans = dataContext.ObjectContext.GetCurrentStoreTransaction();
if (dbTrans != null)
{
command.Transaction = dbTrans;
}
command.ExecuteNonQuery();
}
}
protected virtual string GetIncludeFolderItemsSQL()
{
string sql = @"
INSERT INTO [Cms_ContentInFolder]
([ContentUUID]
,[FolderUUID] )
SELECT UUID,@FolderUUID FROM Cms_Content WHERE FolderUUID=@BaseFolderUUID;
INSERT INTO [Cms_IndexTrigger]
([ContentUUID]
,[Action]
,[ChangedDate])
SELECT UUID,0,@ChangeDate FROM Cms_Content WHERE FolderUUID=@BaseFolderUUID;";
return sql;
}
public virtual void AddContentIndex(IEverestCmsDataContext dataContext, string applicationName, IndexType indexType)
{
string sql = @"
INSERT INTO [Cms_IndexTrigger]
([ContentUUID]
,[Action]
,[ChangedDate])
SELECT UUID,@Action,@ChangedDate FROM Cms_Content WHERE ApplicationId=@ApplicationId
";
var command = dataContext.ObjectContext.CreateStoreCommand(sql, CommandType.Text);
command.CreateParameter("@ApplicationId", DbType.Guid, CachedData.GetApplication(applicationName).ApplicationId);
command.CreateParameter("@Action", DbType.Int32, (int)indexType);
command.CreateParameter("@ChangedDate", DbType.DateTime, DateTime.Now);
using (command.Connection.CreateConnectionScope())
{
var dbTrans = dataContext.ObjectContext.GetCurrentStoreTransaction();
if (dbTrans != null)
{
command.Transaction = dbTrans;
}
command.ExecuteNonQuery();
}
}
#endregion
#region ISystemManager Members
public virtual string ExportSiteElementData(string application)
{
List<ExportedTableOption> exportTables = GetSiteElementExportedOptions();
var applicationId = CachedData.GetApplication(application).ApplicationId;
StringBuilder exportedSQL = new StringBuilder();
foreach (var option in exportTables)
{
exportedSQL.AppendLine(ExportedTable(applicationId, null, option));
}
return exportedSQL.ToString();
}
protected virtual List<ExportedTableOption> GetSiteElementExportedOptions()
{
#region ExportedTableOptions
List<ExportedTableOption> exportTables = new List<ExportedTableOption>()
{
new ExportedTableOption(){TableName="aspnet_Applications",PrimaryKey="ApplicationId"},
new ExportedTableOption(){TableName="Cms_CustomerForm",PrimaryKey="UUID",
SubTables=new List<ExportedTableOption>(){
new ExportedTableOption(){TableName="Cms_CustomerFormValues",PrimaryKey="UUID",ForeignKey="FormUUID"}
}
},
new ExportedTableOption(){TableName="Cms_CustomError",PrimaryKey="UUID"},
new ExportedTableOption(){TableName="Cms_Schedule",PrimaryKey="UUID",
SubTables=new List<ExportedTableOption>(){
new ExportedTableOption(){TableName="Cms_ScheduleParameter",PrimaryKey="UUID",ForeignKey="ScheduleUUID"}
}
},
new ExportedTableOption(){TableName="Cms_Workflow",PrimaryKey="UUID",OrderBy= "WorkflowId ASC",
SubTables=new List<ExportedTableOption>(){
new ExportedTableOption(){TableName="Cms_WorkflowSequence",PrimaryKey="UUID",ForeignKey="WorkflowUUID",OrderBy="SequenceId ASC"}
}
},
new ExportedTableOption(){TableName="Cms_Validator",PrimaryKey="UUID",OrderBy="ValidatorId ASC"},
new ExportedTableOption(){TableName="Cms_ValidatorGroup",PrimaryKey="UUID",OrderBy="ValidateGroupId ASC",
SubTables=new List<ExportedTableOption>(){
new ExportedTableOption(){TableName="Cms_ValidatorInGroup",PrimaryKey="ValidateGroupUUID,ValidatorUUID",ForeignKey="ValidateGroupUUID"}
}
},
new ExportedTableOption(){TableName="Cms_Schema",PrimaryKey="UUID",OrderBy="SchemaId ASC",Condition="SchemaType = 1",
SubTables=new List<ExportedTableOption>(){
new ExportedTableOption(){TableName="Cms_Column",PrimaryKey="UUID",ForeignKey="SchemaUUID"},
new ExportedTableOption(){TableName="Cms_SchemaFunction",PrimaryKey="UUID",ForeignKey="SchemaUUID"}
}
},
new ExportedTableOption(){TableName="Cms_Schema",PrimaryKey="UUID",OrderBy="SchemaId ASC",Condition="SchemaType = 0"},
new ExportedTableOption(){TableName="Cms_Folder",PrimaryKey="UUID",OrderBy="FolderId ASC",
SubTables=new List<ExportedTableOption>(){
new ExportedTableOption(){TableName="Cms_Properties",PrimaryKey="FolderUUID",ForeignKey="FolderUUID"}
}
},
new ExportedTableOption(){TableName="Cms_SearchSetting",PrimaryKey="UUID"},
new ExportedTableOption(){TableName="Cms_RssChannel",PrimaryKey="UUID",
SubTables = new List<ExportedTableOption>(){
new ExportedTableOption(){TableName="Cms_DataRule",PrimaryKey="UUID",ForeignKey="RssChannelUUID"}
}
},
new ExportedTableOption(){TableName="Cms_ModulePermission",PrimaryKey="ModuleName,FunctionName,ApplicationId"},
new ExportedTableOption(){TableName="Cms_ModuleSetting",PrimaryKey="ApplicationId,ModuleName,Key"},
new ExportedTableOption(){TableName="Cms_PageTemplate",PrimaryKey="UUID",OrderBy = "PageTemplateId ASC",
SubTables=new List<ExportedTableOption>(){
new ExportedTableOption(){TableName="Cms_PageTemplateHolders",PrimaryKey="UUID",ForeignKey="PageTemplateUUID"}
}
},
new ExportedTableOption(){TableName="Cms_ContentTemplate",PrimaryKey="UUID",OrderBy="ContentTemplateId",
SubTables=new List<ExportedTableOption>(){
new ExportedTableOption(){TableName="Cms_ContentTemplateParameters",PrimaryKey="UUID",ForeignKey="ContentTemplateUUID"},
new ExportedTableOption(){TableName="Cms_DataRule",PrimaryKey="UUID",ForeignKey="ContentTemplateUUID"},
new ExportedTableOption(){TableName="Cms_Plugin",PrimaryKey="UUID",ForeignKey="ContentTemplateUUID"}
}
},
new ExportedTableOption(){TableName="Cms_TextResource",PrimaryKey="UUID",OrderBy="ResourceId ASC"},
new ExportedTableOption(){TableName="Cms_Page",PrimaryKey="UUID",OrderBy="PageId ASC",
SubTables = new List<ExportedTableOption>(){
new ExportedTableOption(){TableName="Cms_ContentTemplateInPageHolder",PrimaryKey="UUID",ForeignKey="PageUUID",
SubTables=new List<ExportedTableOption>(){
new ExportedTableOption(){TableName="Cms_ParameterValues",PrimaryKey="UUID",ForeignKey="ContentTemplateInPageHolderUUID"}
}
},
new ExportedTableOption(){TableName="Cms_DataRule",PrimaryKey="UUID",ForeignKey="PageUUID"},
new ExportedTableOption(){TableName="Cms_Plugin",PrimaryKey="UUID",ForeignKey="PageUUID"}
}
}
};
#endregion
return exportTables;
}
private string ExportedTable(Guid? applicationId, object foreignValue, ExportedTableOption option)
{
string sql = "SELECT * FROM [" + option.TableName + "] WHERE 1=1";
if (foreignValue != null)
{
sql = sql + string.Format(" AND [{0}]='{1}'", option.ForeignKey, foreignValue);
}
else
{
sql = sql + string.Format(" AND [{0}]='{1}'", "ApplicationId", applicationId.Value);
}
if (!string.IsNullOrEmpty(option.Condition))
{
sql += " AND " + option.Condition;
}
if (!StringExtensions.IsNullOrEmptyTrim(option.OrderBy))
{
sql = sql + " ORDER BY " + option.OrderBy;
}
IEverestCmsDataContext dataContext = EverestCmsEntities.GetDataContext();
StringBuilder exportedSQL = new StringBuilder();
using (dataContext.ObjectContext.Connection.CreateConnectionScope())
{
var command = dataContext.ObjectContext.CreateStoreCommand(sql, CommandType.Text);
var dataReader = command.ExecuteReader();
string insertLayout = ConstructInsertLayout(dataReader, option.TableName);
while (dataReader.Read())
{
exportedSQL.AppendLine(GenereateInsertSQL(dataReader, insertLayout));
if (option.SubTables != null && option.SubTables.Count > 0)
{
var primaryKeyValue = dataReader.GetValue(dataReader.GetOrdinal(option.PrimaryKey));
foreach (var subOption in option.SubTables)
{
exportedSQL.AppendLine(ExportedTable(applicationId, primaryKeyValue, subOption));
}
}
}
}
return exportedSQL.ToString();
}
/// <summary>
/// Exports the content data.
/// </summary>
/// <param name="application">The application.</param>
/// <returns></returns>
public virtual string ExportContentData(string application)
{
var exportedContentTable = GetContentExportedOptions();
var dataContext = EverestCmsEntities.GetDataContext();
var schemas = dataContext.QuerySchemas(application, Everest.CmsServices.Models.SchemaType.Text);
foreach (var schema in schemas)
{
var schemaApplication = CachedData.GetApplicationBySchema(schema.UUID);
var tableName = ContentProviderHelper.GetTableName(schemaApplication.ApplicationName, schema.SchemaName);
exportedContentTable.SubTables.Add(new ExportedTableOption()
{
TableName = tableName,
PrimaryKey = "ContentUUID",
ForeignKey = "ContentUUID"
});
}
var applicationId = CachedData.GetApplication(application).ApplicationId;
StringBuilder exportedSQL = new StringBuilder();
exportedSQL.AppendLine(ExportedTable(applicationId, null, exportedContentTable));
return exportedSQL.ToString();
}
protected virtual ExportedTableOption GetContentExportedOptions()
{
var exportedContentTable = new ExportedTableOption()
{
TableName = "Cms_Content",
OrderBy = "ContentId ASC",
PrimaryKey = "UUID",
SubTables = new List<ExportedTableOption>(){
new ExportedTableOption(){TableName="Cms_BinaryContent",PrimaryKey="ContentUUID",ForeignKey="ContentUUID"},
new ExportedTableOption(){TableName="Cms_ContentFile",PrimaryKey="ContentFileUUID",ForeignKey="ContentUUID",OrderBy ="ContentFileId ASC"},
//new ExportedTableOption(){TableName="Cms_ContentInFolder",PrimaryKey="ContentUUID,FolderUUID",ForeignKey="ContentUUID",OrderBy="ContentInFolderId ASC"},
new ExportedTableOption(){TableName="Cms_ContentReferencing",PrimaryKey="UUID",ForeignKey="ContentUUID"}
}
};
return exportedContentTable;
}
private static string GenereateInsertSQL(IDataReader dataReader, string insertLayout)
{
object[] values = new object[dataReader.FieldCount];
dataReader.GetValues(values);
List<string> stringValues = new List<string>();
foreach (var item in values)
{
if (item == DBNull.Value)
{
stringValues.Add("null");
}
else
{
var dataType = item.GetType();
if (dataType.IsNumericalType())
{
stringValues.Add(item.ToGlobalizedString());
}
else if (dataType == typeof(bool))
{
stringValues.Add(((bool)item) == true ? "1" : "0");
}
else if (dataType == typeof(DateTime))
{
stringValues.Add(string.Format("'{0}'", ((DateTime)item).ToGlobalizedDateTimeString()));
}
else
{
stringValues.Add(string.Format("'{0}'", item.ToString().Trim().Replace("'", "''")));
}
}
}
return (string.Format(insertLayout, stringValues.ToArray()));
}
private string ConstructInsertLayout(IDataReader dataReader, string tableName)
{
IList<string> ignoredColumns = new List<string>()
{
"ColumnId",
"ContentId",
"ContentFileId",
"ContentInFolderId",
"ContentTemplateId",
"ContentTemplateInHolderId",
"ParameterId",
"FormId",
"FormValueId",
"DataRuleId",
"FolderId",
"PageId",
"PageTemplateId",
"HolderId",
"ParameterValueId",
"PermissionId",
"PluginId",
"ScheduleId",
"SchemaId",
"FunctionId",
"ResourceId",
"ValidatorId",
"ValidateGroupId",
"WorkflowId",
"HistoryId",
"SequenceId",
"VersionID"
};
StringBuilder fieldNames = new StringBuilder();
StringBuilder fieldValues = new StringBuilder();
var dataTable = dataReader.GetSchemaTable();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
var row = dataTable.Rows[i];
if (!ignoredColumns.Contains(row["ColumnName"].ToString()))
{
fieldNames.AppendFormat("[{0}],", row["ColumnName"]);
fieldValues.AppendFormat("{{{0}}},", i);
}
}
if (fieldNames.Length == 0)
{
return string.Empty;
}
return string.Format("INSERT INTO [{0}]({1}) VALUES({2});", tableName, fieldNames.RemoveLastSpecifiedChar(','), fieldValues.RemoveLastSpecifiedChar(','));
}
#endregion
//#region ISystemManager Members
//public virtual void EnsureDatabaseInitialized(IEverestCmsDataContext dataContext)
//{
// string installSQLFile = Path.Combine(CmsGlobal.BaseDirPath, @"sql\installMSSQL.sql");
// if (File.Exists(installSQLFile))
// {
// string sql = "SELECT COUNT(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_SchemaVersions]') AND type in (N'U')";
// var command = dataContext.ObjectContext.CreateStoreCommand(sql, CommandType.Text);
// using (command.Connection.CreateConnectionScope())
// {
// int count = (int)command.ExecuteScalar();
// if (count == 0)
// {
// string installSQL = FileExtensions.GetFileBody(installSQLFile);
// command.CommandText = "EXEC(@sql)";
// command.CreateParameter("@sql", DbType.String, installSQL);
// command.ExecuteNonQuery();
// }
// }
// }
//}
//#endregion
}
}
|