/*
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.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using Everest.Library.ExtensionMethod;
using System.Text.RegularExpressions;
namespace Everest.CmsServices.Installation{
public class MSSQLInstaller : InstallerBase
{
public MSSQLInstaller(string koobooPath)
: base(koobooPath)
{
}
protected override void InstallDb(DatabaseInfo databaseInfo)
{
if (databaseInfo.CreateDatabase)
{
if (!TestMSSQLConnection(databaseInfo.AdministratorConnectionString))
{
throw new Exception(Resources.Install_ConnectMasterDbFailure);
}
CreateDatabase(databaseInfo);
}
}
private void CreateDatabase(DatabaseInfo databaseInfo)
{
#region DDL CreateDatabase
string ddl_CreateDatabase = @"/**********************************************************************/
/* Install.SQL */
/* Creates a login and makes the user a member of db roles */
/* */
/**********************************************************************/
-- Declare variables for database name, username and password
DECLARE @dbName sysname,
@dbUser sysname,
@dbPwd nvarchar(max);
-- Set variables for database name, username and password
SET @dbName = '{0}';
SET @dbUser = '{1}';
SET @dbPwd = '{2}';
DECLARE @cmd nvarchar(max)
CREATE DATABASE [{0}]
-- Create login
IF( SUSER_SID(@dbUser) is null )
BEGIN
print '-- Creating login '
SET @cmd = N'CREATE LOGIN ' + quotename(@dbUser) + N' WITH PASSWORD ='''+ replace(@dbPwd, '''', '''''') + N''''
EXEC(@cmd)
END
-- Create database user and map to login
-- and add user to the datareader, datawriter, ddladmin and securityadmin roles
--
SET @cmd = N'USE ' + quotename(@DBName) + N';
IF( NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + replace(@dbUser, '''', '''''') + N'''))
BEGIN
print ''-- Creating user'';
CREATE USER ' + quotename(@dbUser) + N' FOR LOGIN ' + quotename(@dbUser) + N'WITH DEFAULT_SCHEMA=[dbo];
print ''-- Adding user'';
EXEC sp_addrolemember ''db_ddladmin'', ''' + replace(@dbUser, '''', '''''') + N''';
print ''-- Adding user'';
EXEC sp_addrolemember ''db_securityadmin'', ''' + replace(@dbUser, '''', '''''') + N''';
print ''-- Adding user'';
EXEC sp_addrolemember ''db_datareader'', ''' + replace(@dbUser, '''', '''''') + N''';
print ''-- Adding user'';
EXEC sp_addrolemember ''db_datawriter'', ''' + replace(@dbUser, '''', '''''') + N''';
EXEC sp_addrolemember ''db_owner'', ''' + replace(@dbUser, '''', '''''') + N''';
END'
EXEC(@cmd)
";
#endregion
string app_dataPath = Path.Combine(KoobooPath, "App_Data");
string sql = string.Format(ddl_CreateDatabase, databaseInfo.DatabaseName, databaseInfo.UserName, databaseInfo.Password, app_dataPath);
DbHelper.ExecuteNoQuery(databaseInfo.DbProviderFactory, databaseInfo.AdministratorConnectionString, sql);
}
private bool TestMSSQLConnection(string connectionString)
{
SqlConnection connection = new SqlConnection(connectionString);
try
{
using (connection)
{
connection.Open();
}
return true;
}
catch
{
return false;
}
}
private void EnsureDatabaseInitialized(DatabaseInfo databaseInfo)
{
string installSQLFile = Path.Combine(KoobooPath, @"sql\MSSQL\installMSSQL.sql");
if (System.IO.File.Exists(installSQLFile))
{
string sql = "SELECT COUNT(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_SchemaVersions]') AND type in (N'U')";
int count = (int)DbHelper.ExecuteScalar(databaseInfo.DbProviderFactory, databaseInfo.DatabaseConnectionString, sql);
if (count == 0)
{
string installSQL = FileExtensions.GetFileBody(installSQLFile);
DbHelper.ExecuteNoQuery(databaseInfo.DbProviderFactory, databaseInfo.DatabaseConnectionString, installSQL);
}
}
}
protected override void SetConfiguration(DatabaseInfo databaseInfo)
{
if (!TestMSSQLConnection(databaseInfo.DatabaseConnectionString))
{
throw new Exception(Resources.Install_ConnectKoobooDbFailure);
}
EnsureDatabaseInitialized(databaseInfo);
ChangeConfigFile(databaseInfo.DatabaseConnectionString, KoobooPath);
}
internal static void ChangeConfigFile(string connectionString, string koobooPath)
{
string connectionStrings = string.Format(@" <connectionStrings>
<!-- Used by Entity Framework -->
<add name=""EverestCmsEntities"" connectionString=""metadata=~\bin\MSSQL;provider=System.Data.SqlClient;provider connection string="{0}""" providerName=""System.Data.EntityClient"" />
<!-- Used by membership -->
<add name=""EverestCms"" connectionString=""{0}"" providerName=""System.Data.SqlClient""/>
<!-- for versioning-->
<add name=""EverestVersioningEntities"" connectionString=""metadata=~\bin\MSSQL\Versioning;provider=System.Data.SqlClient;provider connection string="{0}""" providerName=""System.Data.EntityClient"" />
<!--for content sync-->
<add name=""ContentSyncEntities"" connectionString=""metadata=~\bin\MSSQL\ContentSync;provider=System.Data.SqlClient;provider connection string="{0}""" providerName=""System.Data.EntityClient"" />
</connectionStrings>", connectionString);
string unityFile = string.Format(Unity_File, "MSSQL");
string webFile = string.Format(Web_File, "MSSQL");
string webBody = FileExtensions.GetFileBody(Path.Combine(koobooPath, webFile));
webBody = Regex.Replace(webBody, "<connectionStrings>.*</connectionStrings>", connectionStrings, RegexOptions.Singleline);
FileExtensions.SaveFileBody(Path.Combine(koobooPath, "unity.config"), FileExtensions.GetFileBody(Path.Combine(koobooPath, unityFile)));
FileExtensions.SaveFileBody(Path.Combine(koobooPath, "Default.aspx"), RuntimeDefault_FileBody);
FileExtensions.SaveFileBody(Path.Combine(koobooPath, "web.config"), webBody);
}
}
}
|