//
// Licensed to the Apache Software Foundation (ASF) under one or more
// contributor license agreements. See the NOTICE file distributed with
// this work for additional information regarding copyright ownership.
// The ASF licenses this file to You under the Apache License, Version 2.0
// (the "License"); you may not use this file except in compliance with
// the License. You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
//
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Collections.Generic;
namespace SetupActions
{
///
/// This is a custom setup action that overrides Installer base and is called after setup copies filed, automatically
/// by the setup program. It will create IIS virtual directories based on WMI, register script maps, and create the databases.
///
internal sealed class SetupAction
{
// Connection Strings
private readonly string CONNSTRING_SQLAUTH = @"server={0};Database={1};user id={2};password={3};Pooling=false;";
private readonly string CONNSTRING_WINAUTH = @"server={0};Database={1};Integrated Security=SSPI;";
// DB Create/Load Scripts
private readonly string TRADEDB_CREATE_FILE = @"setup_utilities\DataLoad\createdb.sql";
private readonly string TRADEDB_CREATE_DBCONFIG = @"setup_utilities\DataLoad\createdbconfig.sql";
private readonly string TRADEDB_INSERT_DBCONFIG = @"setup_utilities\DataLoad\insertdbconfig.sql";
// Xml Loading Stored Proc Scripts
private readonly List CREATE_PROC_SCRIPTS = new List() {
@"setup_utilities\DataLoad\CreateInsertXMLAccountProfileProc.sql",
@"setup_utilities\DataLoad\CreateInsertXMLAccountProc.sql",
@"setup_utilities\DataLoad\CreateInsertXMLHoldingProc.sql",
@"setup_utilities\DataLoad\CreateInsertXMLOrdersProc.sql",
@"setup_utilities\DataLoad\CreateInsertXMLQuoteProc.sql"
};
// Xml Files to Load
private readonly List XML_LOAD_DATA = new List() {
new XmlLoadData { Filename=@"setup_utilities\DataLoad\StockTraderDB\Account.xml", StoredProcName="InsertAccountFromXML", Table="dbo.ACCOUNT" },
new XmlLoadData { Filename=@"setup_utilities\DataLoad\StockTraderDB\AccountProfile.xml", StoredProcName="InsertAccountProfileFromXML" },
new XmlLoadData { Filename=@"setup_utilities\DataLoad\StockTraderDB\Holding.xml", StoredProcName="InsertHoldingFromXML", Table="dbo.HOLDING" },
new XmlLoadData { Filename=@"setup_utilities\DataLoad\StockTraderDB\Quote.xml", StoredProcName="InsertQuoteFromXML" },
new XmlLoadData { Filename=@"setup_utilities\DataLoad\StockTraderDB\Orders.xml", StoredProcName="InsertOrdersFromXML", Table="dbo.ORDERS" }
};
// Error Messages
private readonly string CREATE_LOGIN_FAILED_MSG = "Unable to login with the newly created trade user account.";
// DB Creation Configuration
private readonly string _dbServer;
private readonly string _installPath;
private readonly string _dbAdmin;
private readonly string _dbPassword;
private readonly string _authType;
private readonly string _tradeUser = "trade";
private readonly string _tradePassword = "yyy";
private readonly string _tradeDb = "StockTraderDB";
private string _connectionString = null;
public SetupAction()
{
//Get values from config
_dbServer = ConfigurationManager.AppSettings.Get("dbServer");
_installPath = ConfigurationManager.AppSettings.Get("installPath");
_dbAdmin = ConfigurationManager.AppSettings.Get("dbAdmin");
_dbPassword = ConfigurationManager.AppSettings.Get("dbPassword");
_authType = ConfigurationManager.AppSettings.Get("authType");
}
public void Install()
{
CreateDatabase(_tradeDb, _tradeUser, _tradePassword);
CreateLoadXmlStoredProcs(_tradeDb);
LoadStockTraderDB(_tradeDb);
AddDBConfig(_tradeDb);
}
///
/// Gets a Connection string based on the authentication Type and supplied DB Name
///
/// The database name to connect
/// A SQL Server ConnectionString
private string GetConnectionString(string databaseName)
{
return GetConnectionString(databaseName, _dbAdmin, _dbPassword);
}
///
/// Gets a Connection string based on the authentication Type and supplied DB Name
///
/// The database name to connect
/// The username to use when connecting to the database
/// The password for the supplied username
/// A SQL Server ConnectionString
private string GetConnectionString(string databaseName, string username, string password)
{
if (_authType != null && _authType.Equals("Integrated", StringComparison.InvariantCultureIgnoreCase))
return string.Format(CONNSTRING_WINAUTH, new object[] { _dbServer, databaseName });
return string.Format(CONNSTRING_SQLAUTH, new object[] { _dbServer, databaseName, username, password });
}
///
/// Creates the initial DB schemas for all StockTrader databases.
///
///
///
///
///
///
private void CreateDatabase(string databaseName, string userid, string password)
{
_connectionString = GetConnectionString("master");
SqlConnection sqlConnection = new SqlConnection(_connectionString);
try
{
// Connect to master
sqlConnection.Open();
//Drop DB
string dropDB = "IF EXISTS (SELECT name FROM sys.databases WHERE name = N'" + databaseName + "') DROP DATABASE [" + databaseName + "]";
SqlCommand sqlCommand = new SqlCommand(dropDB, sqlConnection);
sqlCommand.ExecuteNonQuery();
//Create DB
string createDB = "CREATE DATABASE [" + databaseName + "];";
sqlCommand.CommandText = createDB;
sqlCommand.ExecuteNonQuery();
Console.Write(".");
//Config DB
string configDB = "IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) EXEC [" + databaseName + "].[dbo].[sp_fulltext_database] @action = 'enable';" +
"ALTER DATABASE [" + databaseName + "] SET ANSI_NULL_DEFAULT OFF;" +
"ALTER DATABASE [" + databaseName + "] SET ANSI_NULLS OFF;" +
"ALTER DATABASE [" + databaseName + "] SET ANSI_PADDING OFF;" +
"ALTER DATABASE [" + databaseName + "] SET ANSI_WARNINGS OFF;" +
"ALTER DATABASE [" + databaseName + "] SET ARITHABORT OFF;" +
"ALTER DATABASE [" + databaseName + "] SET AUTO_CLOSE OFF;" +
"ALTER DATABASE [" + databaseName + "] SET AUTO_CREATE_STATISTICS ON;" +
"ALTER DATABASE [" + databaseName + "] SET AUTO_SHRINK OFF;" +
"ALTER DATABASE [" + databaseName + "] SET AUTO_UPDATE_STATISTICS ON;" +
"ALTER DATABASE [" + databaseName + "] SET CURSOR_CLOSE_ON_COMMIT OFF;" +
"ALTER DATABASE [" + databaseName + "] SET CURSOR_DEFAULT GLOBAL;" +
"ALTER DATABASE [" + databaseName + "] SET CONCAT_NULL_YIELDS_NULL OFF;" +
"ALTER DATABASE [" + databaseName + "] SET NUMERIC_ROUNDABORT OFF;" +
"ALTER DATABASE [" + databaseName + "] SET QUOTED_IDENTIFIER OFF;" +
"ALTER DATABASE [" + databaseName + "] SET RECURSIVE_TRIGGERS OFF;" +
"ALTER DATABASE [" + databaseName + "] SET ENABLE_BROKER;" +
"ALTER DATABASE [" + databaseName + "] SET AUTO_UPDATE_STATISTICS_ASYNC OFF;" +
"ALTER DATABASE [" + databaseName + "] SET DATE_CORRELATION_OPTIMIZATION OFF;" +
"ALTER DATABASE [" + databaseName + "] SET TRUSTWORTHY OFF;" +
"ALTER DATABASE [" + databaseName + "] SET ALLOW_SNAPSHOT_ISOLATION OFF;" +
"ALTER DATABASE [" + databaseName + "] SET PARAMETERIZATION SIMPLE;" +
"ALTER DATABASE [" + databaseName + "] SET READ_WRITE;" +
"ALTER DATABASE [" + databaseName + "] SET RECOVERY FULL;" +
"ALTER DATABASE [" + databaseName + "] SET MULTI_USER;" +
"ALTER DATABASE [" + databaseName + "] SET PAGE_VERIFY CHECKSUM;" +
"ALTER DATABASE [" + databaseName + "] SET DB_CHAINING OFF;";
sqlCommand.CommandText = configDB;
sqlCommand.ExecuteNonQuery();
Console.Write(".");
// Create Login
string createLogin = "IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'" + userid + "') " +
"CREATE LOGIN [" + userid + "] WITH PASSWORD='" + password + "', DEFAULT_DATABASE=" +
"[" + databaseName + "], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;";
sqlCommand.CommandText = createLogin;
sqlCommand.ExecuteNonQuery();
Console.Write(".");
// Logout of Master
sqlConnection.Close();
// Login to the databaseName using the Master Account
_connectionString = GetConnectionString(databaseName);
sqlConnection = new SqlConnection(_connectionString);
sqlConnection.Open();
// Create User
string createUser = "IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'" + userid + "') CREATE USER" +
"[" + userid + "] FOR LOGIN [" + userid + "] WITH DEFAULT_SCHEMA=[dbo];";
sqlCommand = new SqlCommand(createUser, sqlConnection);
sqlCommand.ExecuteNonQuery();
Console.Write(".");
// Add Role
string addRole = "EXEC sp_addrolemember 'db_owner', '" + userid + "'";
sqlCommand.CommandText = addRole;
sqlCommand.ExecuteNonQuery();
Console.Write(".");
// Logout of databaseName with Master credentials
sqlConnection.Close();
// Login as newly created user.
// Don't need to but rather test connectivity here right away to make sure
// the DB is in good state for the new trade user.
_connectionString = GetConnectionString(databaseName, userid, password);
sqlConnection = new SqlConnection(_connectionString);
try
{
sqlConnection.Open();
}
catch (Exception e)
{
throw new Exception(CREATE_LOGIN_FAILED_MSG + " Error: " + e.Message);
}
///Create Schema for DB
string createCommand = ReadTextFile(Path.Combine(_installPath, TRADEDB_CREATE_FILE));
sqlCommand = new SqlCommand(createCommand, sqlConnection);
sqlCommand.ExecuteNonQuery();
Console.Write(".");
}
finally
{
// Close the connection
if (sqlConnection.State == ConnectionState.Open)
sqlConnection.Close();
sqlConnection.Dispose();
// Clear all pools so we don't get any master connections going forward
SqlConnection.ClearAllPools();
}
}
///
/// Similar to createLoadXmlRepositoryProcs, except for StockTraderDB tables.
///
///
///
private void CreateLoadXmlStoredProcs(string databaseName)
{
_connectionString = GetConnectionString(databaseName);
SqlConnection sqlConnection = new SqlConnection(_connectionString);
try
{
sqlConnection.Open();
foreach (string file in CREATE_PROC_SCRIPTS)
{
// Create Store
string createCommand = ReadTextFile(Path.Combine(_installPath, file));
SqlCommand sqlCommand = new SqlCommand(createCommand, sqlConnection);
sqlCommand.ExecuteNonQuery();
Console.Write(".");
}
}
finally
{
// Close the connection
if (sqlConnection.State == ConnectionState.Open)
sqlConnection.Close();
sqlConnection.Dispose();
}
}
///
/// Loads the StockTraderDB tables with initial data from XML files.
///
///
///
private void LoadStockTraderDB(string repository)
{
_connectionString = GetConnectionString(repository);
SqlConnection sqlConnection = new SqlConnection(_connectionString);
try
{
sqlConnection.Open();
foreach (var xmlData in XML_LOAD_DATA)
{
SqlCommand identityCommand;
if (xmlData.Table != null)
{
// Need to turn off identities first for certain tables.
identityCommand = new SqlCommand("SET IDENTITY_INSERT " + xmlData.Table + " ON", sqlConnection);
identityCommand.ExecuteNonQuery();
Console.Write(".");
}
// Create the sql parameter with the xml data from the file
SqlParameter xmlParameter = new SqlParameter("sXML", SqlDbType.NText, 60000000);
xmlParameter.Value = ReadTextFile(Path.Combine(_installPath, xmlData.Filename));
// execute the load xml Stored Procedure
SqlCommand insertProcCommand = new SqlCommand(xmlData.StoredProcName, sqlConnection);
insertProcCommand.CommandType = CommandType.StoredProcedure;
insertProcCommand.Parameters.Add(xmlParameter);
insertProcCommand.ExecuteNonQuery();
Console.Write(".");
if (xmlData.Table != null)
{
// reset the identities for certain tables
identityCommand = new SqlCommand("SET IDENTITY_INSERT " + xmlData.Table + " OFF", sqlConnection);
identityCommand.ExecuteNonQuery();
Console.Write(".");
}
}
}
finally
{
// Close the connection
if (sqlConnection.State == ConnectionState.Open)
sqlConnection.Close();
sqlConnection.Dispose();
}
}
///
/// Creates the tables needed for the database configuration system
/// i.e. SERVICE, CLIENTTOBS, BSTOOPS, DBCONFIG
///
///
///
private void AddDBConfig(string databaseName)
{
_connectionString = GetConnectionString(databaseName);
SqlConnection sqlConnection = new SqlConnection(_connectionString);
try
{
sqlConnection.Open();
//creates the necessary tables: CLIENTTOBS, BSTOOPS, SERVICE, DBCONFIG
string createCommand = ReadTextFile(Path.Combine(_installPath, TRADEDB_CREATE_DBCONFIG));
SqlCommand sqlCommand = new SqlCommand(createCommand, sqlConnection);
sqlCommand.ExecuteNonQuery();
Console.Write(".");
//inserts the config info into these tables
createCommand = ReadTextFile(Path.Combine(_installPath, TRADEDB_INSERT_DBCONFIG));
sqlCommand = new SqlCommand(createCommand, sqlConnection);
sqlCommand.ExecuteNonQuery();
Console.Write(".");
}
finally
{
// Close the connection
if (sqlConnection.State == ConnectionState.Open)
sqlConnection.Close();
sqlConnection.Dispose();
}
}
///
/// Use a StreamReader to return the string value of a text document.
///
/// The text file to load
/// A string of the contents in the text file
private static string ReadTextFile(string filename)
{
string fileContents = string.Empty;
using (StreamReader file = new StreamReader(filename))
{
fileContents = file.ReadToEnd();
file.Close();
}
return fileContents;
}
}
}