Author: bendewey
Date: Thu Jul 30 03:26:43 2009
New Revision: 799159
URL: http://svn.apache.org/viewvc?rev=799159&view=rev
Log:
STONEHENGE-95, Additionally I made a pretty major refactor to the SetupActions
class.
Added:
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/XmlLoadData.cs
Modified:
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/App.config
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/Program.cs
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/SetupAction.cs
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/SetupActions.csproj
Modified:
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/App.config
URL:
http://svn.apache.org/viewvc/incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/App.config?rev=799159&r1=799158&r2=799159&view=diff
==============================================================================
---
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/App.config
(original)
+++
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/App.config
Thu Jul 30 03:26:43 2009
@@ -21,7 +21,6 @@
<!--This is the input parameters for the db setup routine. -->
<add key="dbServer" value="." />
<add key="installPath" value="C:\StockTraderDemo\" />
- <add key="createDBs" value="StockTraderDB" />
<add key="dbAdmin" value="sa" />
<add key="dbPassword" value="Abc.123" />
<add key="authType" value="SQL" />
Modified:
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/Program.cs
URL:
http://svn.apache.org/viewvc/incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/Program.cs?rev=799159&r1=799158&r2=799159&view=diff
==============================================================================
---
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/Program.cs
(original)
+++
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/Program.cs
Thu Jul 30 03:26:43 2009
@@ -21,42 +21,25 @@
using System.Text;
using System.Configuration;
-namespace SetupAction
+namespace SetupActions
{
class Program
- {
- public static string INSTALL_PATH = null;
- public static string dbServer = null;
- public static string installPath = null;
- public static string createDBs = null;
- public static string dbAdmin = null;
- public static string dbPassword = null;
- public static string authType = null;
-
+ {
static void Main()
{
Console.WriteLine("Setting up Database");
+
//These are only for display
- //The install method wil get them again.
- dbServer = ConfigurationManager.AppSettings.Get("dbServer");
- installPath = ConfigurationManager.AppSettings.Get("installPath");
- //not used createDBs =
ConfigurationManager.AppSettings.Get("createDBs");
- dbAdmin = ConfigurationManager.AppSettings.Get("dbAdmin");
- dbPassword = ConfigurationManager.AppSettings.Get("dbPassword");
- authType = ConfigurationManager.AppSettings.Get("authType");
-
+ //The install method will get them again.
Console.WriteLine("Config.AppSettings:");
- Console.WriteLine("dbServer is {0}",dbServer );
- Console.WriteLine("installPath is {0}", installPath);
- //not used Console.WriteLine("createDBs is {0}", createDBs);
- Console.WriteLine("dbAdmin is {0}", dbAdmin);
- Console.WriteLine("authType is {0}", authType);
-
-
+ Console.WriteLine("dbServer is {0}",
ConfigurationManager.AppSettings.Get("dbServer"));
+ Console.WriteLine("installPath is {0}",
ConfigurationManager.AppSettings.Get("installPath"));
+ Console.WriteLine("dbAdmin is {0}",
ConfigurationManager.AppSettings.Get("dbAdmin"));
+ Console.WriteLine("authType is {0}",
ConfigurationManager.AppSettings.Get("authType"));
- SetupAction s = new SetupAction();
try
{
+ SetupAction s = new SetupAction();
s.Install();
Console.WriteLine();
Console.WriteLine("Setup completed. Press Enter to continue");
@@ -64,7 +47,9 @@
catch (Exception ex)
{
Console.WriteLine();
+ Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(ex.ToString());
+ Console.ForegroundColor = ConsoleColor.Gray;
Console.WriteLine("Setup Failed. Press Enter to continue");
}
Console.ReadLine();
Modified:
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/SetupAction.cs
URL:
http://svn.apache.org/viewvc/incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/SetupAction.cs?rev=799159&r1=799158&r2=799159&view=diff
==============================================================================
---
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/SetupAction.cs
(original)
+++
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/SetupAction.cs
Thu Jul 30 03:26:43 2009
@@ -16,186 +16,103 @@
//
using System;
-using System.DirectoryServices;
-using System.Data;
-using System.ComponentModel;
-using System.Collections.Generic;
-using System.Text;
-using System.Net;
-using System.Threading;
-using System.Diagnostics;
-using System.IO;
-using System.Reflection;
using System.Configuration;
+using System.Data;
using System.Data.SqlClient;
-using Microsoft.SqlServer.Management;
-using Microsoft.SqlServer.Management.Smo;
-using Microsoft.SqlServer.Management.Common;
-
-
+using System.IO;
+using System.Collections.Generic;
-namespace SetupAction
+namespace SetupActions
{
/// <summary>
/// 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.
/// </summary>
- public partial class SetupAction
+ internal sealed class SetupAction
{
- private string SQL_CONN = null;
- public static readonly string CONNSTRING_SQLAUTH =
"server={0};Database={1};user id={2};password={3};Pooling=false;";
- public static readonly string CONNSTRING_WINAUTH =
@"server={0};Database={1};Integrated Security=SSPI;";
- //newstrings
- string CREATE_LOADACCOUNTPROFILEXML =
@"setup_utilities\DATALOAD\CreateInsertXMLAccountProfileProc.sql";
- string CREATE_LOADACCOUNTXML =
@"setup_utilities\DATALOAD\CreateInsertXMLAccountProc.sql";
- string CREATE_LOADHOLDINGXML =
@"setup_utilities\DATALOAD\CreateInsertXMLHoldingProc.sql";
- string CREATE_LOADORDERSXML =
@"setup_utilities\DATALOAD\CreateInsertXMLOrdersProc.sql";
- string CREATE_LOADQUOTEXML =
@"setup_utilities\DATALOAD\CreateInsertXMLQuoteProc.sql";
- string MSG_CHECK_SQL_AUTH = string.Empty;
+ // 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<string> CREATE_PROC_SCRIPTS = new List<string>()
{
+ @"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"
+ };
- public static string dbServer = null;
- private string installPath = null;
- //private string createDBs = null;
- public string dbAdmin = null;
- public string dbPassword = null;
- public string authType = null;
- public static readonly string tradeuser = "trade";
- public static readonly string tradepassword = "yyy";
- public static readonly string TRADEDB = "StockTraderDB";
- public const string TRADEDB_CREATE_FILE = "createdb.sql";
- public const string TRADEDB_CREATE_DBCONFIG = "createdbconfig.sql";
- public const string TRADEDB_INSERT_DBCONFIG = "insertdbconfig.sql";
+ // Xml Files to Load
+ private readonly List<XmlLoadData> XML_LOAD_DATA = new
List<XmlLoadData>() {
+ 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";
- string GetConnectionString(string databaseName)
+ private string _connectionString = null;
+
+ public SetupAction()
{
- 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, dbAdmin, dbPassword });
+ //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(System.Collections.IDictionary stateSaver)
public void Install()
{
-
- //Get values from config
- dbServer = ConfigurationManager.AppSettings.Get("dbServer");
- installPath = ConfigurationManager.AppSettings.Get("installPath");
- //not used createDBs =
ConfigurationManager.AppSettings.Get("createDBs");
- dbAdmin = ConfigurationManager.AppSettings.Get("dbAdmin");
- dbPassword = ConfigurationManager.AppSettings.Get("dbPassword");
- authType = ConfigurationManager.AppSettings.Get("authType");
-
- //string CREATE_LOADACCOUNTPROFILEXML =
@"setup_utilities\DATALOAD\" + createfilename;
- //string CREATE_LOADACCOUNTXML = string.Empty;
- //string CREATE_LOADHOLDINGXML = string.Empty;
- //string CREATE_LOADORDERSXML = string.Empty;
- //string CREATE_LOADQUOTEXML = string.Empty;
-
- try
- {
- createDatabase(installPath, TRADEDB_CREATE_FILE, TRADEDB,
tradeuser, tradepassword);
- createLoadXmlStockTraderDBProcs(installPath, TRADEDB);
- loadStockTraderDB(installPath, TRADEDB);
- addDBConfig(installPath, TRADEDB, TRADEDB_CREATE_DBCONFIG,
TRADEDB_INSERT_DBCONFIG);
- }
- catch (Exception e)
- {
- throw new Exception("MSG_DBFAIL " + e.Message);
- }
+ CreateDatabase(_tradeDb, _tradeUser, _tradePassword);
+ CreateLoadXmlStoredProcs(_tradeDb);
+ LoadStockTraderDB(_tradeDb);
+ AddDBConfig(_tradeDb);
}
/// <summary>
- /// Creates the tables needed for the database configuration system
- /// i.e. SERVICE, CLIENTTOBS, BSTOOPS, DBCONFIG
+ /// Gets a Connection string based on the authentication Type and
supplied DB Name
/// </summary>
- /// <param name="installPath"></param>
- /// <param name="databaseName"></param>
- private void addDBConfig(string installPath, string databaseName,
string createdbconfigfilename, string insertdbconfigfilename)
+ /// <param name="databaseName">The database name to connect</param>
+ /// <returns>A SQL Server ConnectionString</returns>
+ private string GetConnectionString(string databaseName)
{
- SQL_CONN = GetConnectionString(databaseName);
- SqlConnection sqlConnection = new SqlConnection(SQL_CONN);
- sqlConnection.Open();
-
- //creates the necessary tables: CLIENTTOBS, BSTOOPS, SERVICE,
DBCONFIG
- System.IO.StreamReader file = new
System.IO.StreamReader(installPath + @"setup_utilities\DATALOAD\" +
createdbconfigfilename);
- string createCommand = file.ReadToEnd();
- file.Close();
- SqlCommand sqlCommand = new SqlCommand(createCommand,
sqlConnection);
- sqlCommand.CommandType = CommandType.Text;
- sqlCommand.ExecuteNonQuery();
- Console.Write(".");
-
- //inserts the config info into these tables
- file = new System.IO.StreamReader(installPath +
@"setup_utilities\DATALOAD\" + insertdbconfigfilename);
- createCommand = file.ReadToEnd();
- file.Close();
- sqlCommand = new SqlCommand(createCommand, sqlConnection);
- sqlCommand.CommandType = CommandType.Text;
- sqlCommand.ExecuteNonQuery();
- Console.Write(".");
-
- sqlConnection.Close();
- return;
+ return GetConnectionString(databaseName, _dbAdmin, _dbPassword);
}
-
+
/// <summary>
- /// Similar to createLoadXmlRepositoryProcs, except for StockTraderDB
tables.
+ /// Gets a Connection string based on the authentication Type and
supplied DB Name
/// </summary>
- /// <param name="installPath"></param>
- /// <param name="databaseName"></param>
- public void createLoadXmlStockTraderDBProcs(string installPath, string
databaseName)
+ /// <param name="databaseName">The database name to connect</param>
+ /// <param name="username">The username to use when connecting to the
database</param>
+ /// <param name="password">The password for the supplied
username</param>
+ /// <returns>A SQL Server ConnectionString</returns>
+ private string GetConnectionString(string databaseName, string
username, string password)
{
- SQL_CONN = GetConnectionString(databaseName);
- SqlConnection sqlConnection = new SqlConnection(SQL_CONN);
- sqlConnection.Open();
-
- ///ACCOUNT_PROFILE_LOAD_XML
- System.IO.StreamReader file = new
System.IO.StreamReader(installPath + CREATE_LOADACCOUNTPROFILEXML);
- string createCommand = file.ReadToEnd();
- file.Close();
- SqlCommand sqlCommand = new SqlCommand(createCommand,
sqlConnection);
- sqlCommand.CommandType = CommandType.Text;
- sqlCommand.ExecuteNonQuery();
- Console.Write(".");
-
- ///ACCOUNT_LOAD_XML
- file = new System.IO.StreamReader(installPath +
CREATE_LOADACCOUNTXML);
- createCommand = file.ReadToEnd();
- file.Close();
- sqlCommand = new SqlCommand(createCommand, sqlConnection);
- sqlCommand.CommandType = CommandType.Text;
- sqlCommand.ExecuteNonQuery();
- Console.Write(".");
-
- ///HOLDING_LOAD_XML
- file = new System.IO.StreamReader(installPath +
CREATE_LOADHOLDINGXML);
- createCommand = file.ReadToEnd();
- file.Close();
- sqlCommand = new SqlCommand(createCommand, sqlConnection);
- sqlCommand.CommandType = CommandType.Text;
- sqlCommand.ExecuteNonQuery();
- Console.Write(".");
-
- ///ORDERS_LOAD_XML
- file = new System.IO.StreamReader(installPath +
CREATE_LOADORDERSXML);
- createCommand = file.ReadToEnd();
- file.Close();
- sqlCommand = new SqlCommand(createCommand, sqlConnection);
- sqlCommand.CommandType = CommandType.Text;
- sqlCommand.ExecuteNonQuery();
- Console.Write(".");
-
- ///QUOTE_LOAD_XML
- file = new System.IO.StreamReader(installPath +
CREATE_LOADQUOTEXML);
- createCommand = file.ReadToEnd();
- file.Close();
- sqlCommand = new SqlCommand(createCommand, sqlConnection);
- sqlCommand.CommandType = CommandType.Text;
- sqlCommand.ExecuteNonQuery();
- Console.Write(".");
+ if (_authType != null && _authType.Equals("Integrated",
StringComparison.InvariantCultureIgnoreCase))
+ return string.Format(CONNSTRING_WINAUTH, new object[] {
_dbServer, databaseName });
- sqlConnection.Close();
+ return string.Format(CONNSTRING_SQLAUTH, new object[] { _dbServer,
databaseName, username, password });
}
/// <summary>
@@ -206,224 +123,258 @@
/// <param name="repositoryName"></param>
/// <param name="userid"></param>
/// <param name="password"></param>
- public void createDatabase(string installPath, string createfilename,
string repositoryName, string userid, string password)
+ private void CreateDatabase(string databaseName, string userid, string
password)
{
- string configDB = "IF (1 =
FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) EXEC [" + repositoryName +
"].[dbo].[sp_fulltext_database] @action = 'enable';" +
- "ALTER DATABASE [" + repositoryName +
"] SET ANSI_NULL_DEFAULT OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET ANSI_NULLS OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET ANSI_PADDING OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET ANSI_WARNINGS OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET ARITHABORT OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET AUTO_CLOSE OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET AUTO_CREATE_STATISTICS ON;" +
- "ALTER DATABASE [" + repositoryName +
"] SET AUTO_SHRINK OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET AUTO_UPDATE_STATISTICS ON;" +
- "ALTER DATABASE [" + repositoryName +
"] SET CURSOR_CLOSE_ON_COMMIT OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET CURSOR_DEFAULT GLOBAL;" +
- "ALTER DATABASE [" + repositoryName +
"] SET CONCAT_NULL_YIELDS_NULL OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET NUMERIC_ROUNDABORT OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET QUOTED_IDENTIFIER OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET RECURSIVE_TRIGGERS OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET ENABLE_BROKER;" +
- "ALTER DATABASE [" + repositoryName +
"] SET AUTO_UPDATE_STATISTICS_ASYNC OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET DATE_CORRELATION_OPTIMIZATION OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET TRUSTWORTHY OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET ALLOW_SNAPSHOT_ISOLATION OFF;" +
- "ALTER DATABASE [" + repositoryName +
"] SET PARAMETERIZATION SIMPLE;" +
- "ALTER DATABASE [" + repositoryName +
"] SET READ_WRITE;" +
- "ALTER DATABASE [" + repositoryName +
"] SET RECOVERY FULL;" +
- "ALTER DATABASE [" + repositoryName +
"] SET MULTI_USER;" +
- "ALTER DATABASE [" + repositoryName +
"] SET PAGE_VERIFY CHECKSUM;" +
- "ALTER DATABASE [" + repositoryName +
"] SET DB_CHAINING OFF;";
-
- string dropDB = null;
- SQL_CONN = GetConnectionString("master");
- SqlConnection sqlConnection = new SqlConnection(SQL_CONN);
+ _connectionString = GetConnectionString("master");
+ SqlConnection sqlConnection = new SqlConnection(_connectionString);
try
{
+ // Connect to master
sqlConnection.Open();
- dropDB = "IF EXISTS (SELECT name FROM sys.databases WHERE
name = N'" + repositoryName + "') DROP DATABASE [" + repositoryName + "]";
- SqlCommand sqlCommand = new SqlCommand(dropDB, sqlConnection);
- sqlCommand.CommandTimeout = 1200;
- sqlCommand.CommandType = CommandType.Text;
//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 strCreateDB = "CREATE DATABASE [" + repositoryName +
"];";
- sqlCommand.CommandText = strCreateDB;
+ 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(".");
- string createlogin = "IF NOT EXISTS (SELECT * FROM
sys.server_principals WHERE name = N'" + userid + "') " +
+ // Create Login
+ string createLogin = "IF NOT EXISTS (SELECT * FROM
sys.server_principals WHERE name = N'" + userid + "') " +
"CREATE LOGIN [" + userid + "] WITH
PASSWORD='" + password + "', DEFAULT_DATABASE=" +
- "[" + repositoryName + "],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;";
- string createuser = "IF NOT EXISTS (SELECT * FROM
sys.database_principals WHERE name = N'" + userid + "') CREATE USER" +
- "[" + userid + "] FOR LOGIN [" +
userid + "] WITH DEFAULT_SCHEMA=[dbo];";
+ "[" + databaseName + "],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;";
- //create login
- sqlCommand.CommandText = createlogin;
+ sqlCommand.CommandText = createLogin;
sqlCommand.ExecuteNonQuery();
Console.Write(".");
- //Logout of Master
-
+ // Logout of Master
sqlConnection.Close();
- //Now create user for new DB by logging in to new DB with
admin rights
+ // Login to the databaseName using the Master Account
+ _connectionString = GetConnectionString(databaseName);
+ sqlConnection = new SqlConnection(_connectionString);
+ sqlConnection.Open();
- SQL_CONN = GetConnectionString(repositoryName);
- sqlConnection = new SqlConnection(SQL_CONN);
- Server server = new Server(new
ServerConnection(sqlConnection));
- server.ConnectionContext.ExecuteNonQuery(createuser);
- Console.Write(".");
+ // 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];";
- //Logout of Trade DB
- sqlConnection.Close();
- System.Data.SqlClient.SqlConnection.ClearAllPools();
+ sqlCommand = new SqlCommand(createUser, sqlConnection);
+ sqlCommand.ExecuteNonQuery();
+ Console.Write(".");
- //Login to Master and use SMO to create role mapping of DBO
- sqlConnection = new SqlConnection(SQL_CONN);
- //new SMO functionality
- server = new Server(new ServerConnection(sqlConnection));
- server.ConnectionContext.ExecuteNonQuery("exec
sp_addrolemember 'db_owner','" + userid + "'");
+ // Add Role
+ string addRole = "EXEC sp_addrolemember 'db_owner', '" +
userid + "'";
+ sqlCommand.CommandText = addRole;
+ sqlCommand.ExecuteNonQuery();
Console.Write(".");
- //Logout
+ // 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 DB in good state for new trade user.
- SQL_CONN = GetConnectionString(repositoryName);
- sqlConnection = new SqlConnection(SQL_CONN);
+ // 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(MSG_CHECK_SQL_AUTH + ". Exc: " +
e.Message);
+ throw new Exception(CREATE_LOGIN_FAILED_MSG + " Error: "
+ e.Message);
}
///Create Schema for DB
- System.IO.StreamReader file = new
System.IO.StreamReader(installPath + @"setup_utilities\DATALOAD\" +
createfilename);
- string createCommand = file.ReadToEnd();
- file.Close();
+ string createCommand = ReadTextFile(Path.Combine(_installPath,
TRADEDB_CREATE_FILE));
sqlCommand = new SqlCommand(createCommand, sqlConnection);
- sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
Console.Write(".");
- sqlConnection.Close();
- return;
}
- catch (Exception e)
+ finally
{
+ // Close the connection
if (sqlConnection.State == ConnectionState.Open)
sqlConnection.Close();
- System.Data.SqlClient.SqlConnection.ClearAllPools();
sqlConnection.Dispose();
- throw new Exception(e.Message);
+
+ // Clear all pools so we don't get any master connections
going forward
+ SqlConnection.ClearAllPools();
}
}
-
/// <summary>
- /// Loads the StockTraderDB tables with initial data from XML files.
+ /// Similar to createLoadXmlRepositoryProcs, except for StockTraderDB
tables.
/// </summary>
- /// <param name="installPath"></param>
- /// <param name="repository"></param>
- public void loadStockTraderDB(string installPath, string repository)
+ ///
+ /// <param name="databaseName"></param>
+ private void CreateLoadXmlStoredProcs(string databaseName)
{
- SQL_CONN = GetConnectionString(repository);
- string xmlFile = null;
- string sql = null;
- string table = null;
- for (int i = 0; i < 5; i++)
+ _connectionString = GetConnectionString(databaseName);
+ SqlConnection sqlConnection = new SqlConnection(_connectionString);
+ try
{
- switch (i)
- {
- case 0:
- {
- xmlFile = installPath +
"setup_utilities\\DATALOAD\\" + repository + "\\Account.xml";
- sql = "InsertAccountFromXML";
- table = "dbo.ACCOUNT";
- break;
- }
- case 1:
- {
- xmlFile = installPath +
"setup_utilities\\DATALOAD\\" + repository + "\\AccountProfile.xml";
- sql = "InsertAccountProfileFromXML";
- table = null;
- break;
- }
- case 2:
- {
- xmlFile = installPath +
"setup_utilities\\DATALOAD\\" + repository + "\\Quote.xml";
- sql = "InsertQuoteFromXML";
- table = null;
- break;
- }
- case 3:
- {
- xmlFile = installPath +
"setup_utilities\\DATALOAD\\" + repository + "\\Holding.xml";
- sql = "InsertHoldingFromXML";
- table = "dbo.HOLDING";
- break;
- }
- case 4:
- {
- xmlFile = installPath +
"setup_utilities\\DATALOAD\\" + repository + "\\Orders.xml";
- sql = "InsertOrdersFromXML";
- table = "dbo.ORDERS";
- break;
- }
- }
- SqlConnection conn = new SqlConnection(SQL_CONN);
- conn.Open();
- string configContent = null;
- if (table != null)
- {
- //Need to turn off identities first for certain tables.
- SqlCommand cmd = new SqlCommand("SET IDENTITY_INSERT " +
table + " ON");
- cmd.Connection = conn;
- cmd.CommandType = CommandType.Text;
- cmd.ExecuteNonQuery();
- Console.Write(".");
- }
- using (StreamReader sr = new StreamReader(xmlFile))
- {
- configContent = sr.ReadToEnd();
- }
- SqlParameter parmDoc = new SqlParameter("sXML",
SqlDbType.NText, 60000000);
- parmDoc.Value = configContent;
- SqlCommand cmd2 = new SqlCommand(sql);
- cmd2.CommandType = CommandType.StoredProcedure;
- cmd2.Connection = conn;
- cmd2.Parameters.Add(parmDoc);
- try
+ sqlConnection.Open();
+
+ foreach (string file in CREATE_PROC_SCRIPTS)
{
- cmd2.ExecuteNonQuery();
+ // Create Store
+ string createCommand =
ReadTextFile(Path.Combine(_installPath, file));
+ SqlCommand sqlCommand = new SqlCommand(createCommand,
sqlConnection);
+ sqlCommand.ExecuteNonQuery();
Console.Write(".");
}
- catch (Exception e)
- {
- throw new Exception(e.Message);
- }
- if (table != null)
+ }
+ finally
+ {
+ // Close the connection
+ if (sqlConnection.State == ConnectionState.Open)
+ sqlConnection.Close();
+ sqlConnection.Dispose();
+ }
+ }
+
+ /// <summary>
+ /// Loads the StockTraderDB tables with initial data from XML files.
+ /// </summary>
+ ///
+ /// <param name="repository"></param>
+ private void LoadStockTraderDB(string repository)
+ {
+ _connectionString = GetConnectionString(repository);
+ SqlConnection sqlConnection = new SqlConnection(_connectionString);
+ try
+ {
+ sqlConnection.Open();
+
+ foreach (var xmlData in XML_LOAD_DATA)
{
- cmd2 = new SqlCommand("SET IDENTITY_INSERT " + table + "
OFF");
- cmd2.Connection = conn;
- cmd2.CommandType = CommandType.Text;
- cmd2.ExecuteNonQuery();
+ 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(".");
+ }
}
- conn.Close();
}
+ finally
+ {
+ // Close the connection
+ if (sqlConnection.State == ConnectionState.Open)
+ sqlConnection.Close();
+ sqlConnection.Dispose();
+ }
+ }
+
+ /// <summary>
+ /// Creates the tables needed for the database configuration system
+ /// i.e. SERVICE, CLIENTTOBS, BSTOOPS, DBCONFIG
+ /// </summary>
+ /// <param name="installPath"></param>
+ /// <param name="databaseName"></param>
+ 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();
+ }
+ }
+
+ /// <summary>
+ /// Use a StreamReader to return the string value of a text document.
+ /// </summary>
+ /// <param name="filename">The text file to load</param>
+ /// <returns>A string of the contents in the text file</returns>
+ private static string ReadTextFile(string filename)
+ {
+ string fileContents = string.Empty;
+ using (StreamReader file = new StreamReader(filename))
+ {
+ fileContents = file.ReadToEnd();
+ file.Close();
+ }
+ return fileContents;
}
}
}
Modified:
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/SetupActions.csproj
URL:
http://svn.apache.org/viewvc/incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/SetupActions.csproj?rev=799159&r1=799158&r2=799159&view=diff
==============================================================================
---
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/SetupActions.csproj
(original)
+++
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/SetupActions.csproj
Thu Jul 30 03:26:43 2009
@@ -31,12 +31,6 @@
<WarningLevel>4</WarningLevel>
</PropertyGroup>
<ItemGroup>
- <Reference Include="Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL" />
- <Reference Include="Microsoft.SqlServer.Management.Sdk.Sfc,
Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91,
processorArchitecture=MSIL">
- <SpecificVersion>False</SpecificVersion>
- <HintPath>..\Util\Microsoft.SqlServer.Management.Sdk.Sfc.dll</HintPath>
- </Reference>
- <Reference Include="Microsoft.SqlServer.Smo, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL" />
<Reference Include="System" />
<Reference Include="System.configuration" />
<Reference Include="System.Core">
@@ -56,6 +50,7 @@
<Compile Include="Program.cs" />
<Compile Include="Properties\AssemblyInfo.cs" />
<Compile Include="SetupAction.cs" />
+ <Compile Include="XmlLoadData.cs" />
</ItemGroup>
<ItemGroup>
<None Include="App.config" />
Added:
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/XmlLoadData.cs
URL:
http://svn.apache.org/viewvc/incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/XmlLoadData.cs?rev=799159&view=auto
==============================================================================
---
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/XmlLoadData.cs
(added)
+++
incubator/stonehenge/trunk/stocktrader/dotnet/setup_utilities/SetupActions/XmlLoadData.cs
Thu Jul 30 03:26:43 2009
@@ -0,0 +1,26 @@
+//
+// 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.
+//
+
+namespace SetupActions
+{
+ internal class XmlLoadData
+ {
+ public string Filename { get; set; }
+ public string StoredProcName { get; set; }
+ public string Table { get; set; }
+ }
+}
