private void UpdateDatabase(SqlInstallerConsoleProgramArgs args)
{
var scripts = GenerateScripts(args);
using (var connection = args.CreateConnection()) {
connection.Open();
//using (var tr = connection.BeginTransaction()) {
foreach (var script in scripts)
{
var command = connection.CreateCommand();
//command.Transaction = tr;
command.CommandText = script;
command.ExecuteNonQuery();
var message = "";
var commandmessage = Regex.Match(script, "--SQLINSTALL:([^\r\n]+)");
if (commandmessage.Success)
{
message = commandmessage.Groups[1].Value;
}
else
{
message = script;
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine(message + " : executed");
Console.ResetColor();
}
//tr.Commit();
//}
}
}
private void UpdateDatabase(SqlInstallerConsoleProgramArgs args) {
var scripts = GenerateScripts(args);
using (var connection = args.CreateConnection()) {
connection.Open();
//using (var tr = connection.BeginTransaction()) {
foreach (var script in scripts) {
var command = connection.CreateCommand();
//command.Transaction = tr;
command.CommandText = script;
command.ExecuteNonQuery();
var message = "";
var commandmessage = Regex.Match(script, "--SQLINSTALL:([^\r\n]+)");
if (commandmessage.Success) {
message = commandmessage.Groups[1].Value;
}
else {
message = script;
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine(message + " : executed");
Console.ResetColor();
}
//tr.Commit();
//}
}
}
public void Setup() {
var args = new SqlInstallerConsoleProgramArgs();
args.Assembly = GetType().Assembly;
args.GenerateScript = true;
args.Schema = "test";
_result = new SqlInstallerConsoleProgram().GenerateScripts(args);
}
/// <summary>
/// executes sql script or generate it
/// </summary>
/// <param name="args"> </param>
/// <returns> </returns>
/// <exception cref="NotImplementedException"></exception>
public int Run(SqlInstallerConsoleProgramArgs args) {
string argsvalidmessage;
if (!args.IsValid(out argsvalidmessage)) {
throw new SqlInstallerException("invalid arguments : " + argsvalidmessage);
}
if (args.GenerateScript) {
GenerateScript(args);
}
else {
UpdateDatabase(args);
}
return 0;
}
private static IEnumerable <string> GetCreateCustomLibrariesScripts(SqlInstallerConsoleProgramArgs args,
List <AssemblyName> customdepends,
Assembly assembly)
{
var dir = Path.GetDirectoryName(Path.GetFullPath(args.AssemblyName + ".dll"));
foreach (var dep in customdepends)
{
yield return
(string.Format(
@"
--SQLINSTALL: Create assembly {0}
if not exists(select assembly_id from sys.assemblies where name='{0}')
CREATE ASSEMBLY [{0}]
AUTHORIZATION dbo
FROM {1}
WITH PERMISSION_SET = UNSAFE
else begin
begin try
ALTER ASSEMBLY [{0}]
FROM {1}
WITH PERMISSION_SET = UNSAFE
end try
begin catch
declare @message nvarchar(max) set @message = ERROR_MESSAGE()
if @message not like '%MVID%' begin
declare @severity int set @severity = ERROR_SEVERITY()
declare @state int set @state = ERROR_STATE()
raiserror(@message,@severity,@state)
end
end catch
end
",
dep.Name, QueryGeneratorHelper.GetAssemblyBits(Path.Combine(dir, dep.Name + ".dll"))));
}
yield return
(string.Format(
@"
--SQLINSTALL: Create assembly {0}
CREATE ASSEMBLY [{0}]
AUTHORIZATION dbo
FROM {1}
WITH PERMISSION_SET = UNSAFE
",
assembly.GetName().Name, QueryGeneratorHelper.GetAssemblyBits(Path.Combine(dir, assembly.GetName().Name + ".dll")))
);
}
/// <summary>
/// executes sql script or generate it
/// </summary>
/// <param name="args"> </param>
/// <returns> </returns>
/// <exception cref="NotImplementedException"></exception>
public int Run(SqlInstallerConsoleProgramArgs args)
{
string argsvalidmessage;
if (!args.IsValid(out argsvalidmessage))
{
throw new SqlInstallerException("invalid arguments : " + argsvalidmessage);
}
if (args.GenerateScript)
{
GenerateScript(args);
}
else
{
UpdateDatabase(args);
}
return(0);
}
private void GenerateScript(SqlInstallerConsoleProgramArgs args)
{
var scripts = GenerateScripts(args);
var script = new StringBuilder();
foreach (var s in scripts)
{
script.AppendLine(s);
script.AppendLine("GO");
}
if (args.ScriptFile.IsNotEmpty())
{
File.WriteAllText(args.ScriptFile, script.ToString());
}
else
{
Console.WriteLine(script.ToString());
}
}
private static IEnumerable <string> GetDatabasePrepareScripts(SqlInstallerConsoleProgramArgs args, string dbname)
{
yield return(string.Format(
@"--SQLINSTALL: USE DATABASE
use {0}", dbname));
yield return(string.Format(
@"--SQLINSTALL: SET DATABASE TRUSTWORTHY
ALTER DATABASE {0} SET TRUSTWORTHY ON", dbname));
yield return
(@"--SQLINSTALL: SET CLR ON
sp_configure 'clr enabled', 1
");
yield return
(@"--SQLINSTALL: RECONFIGURE
reconfigure
");
yield return
(@"--SQLINSTALL: Normalize owner
EXEC sp_changedbowner 'sa'
");
yield return(string.Format(
@"--SQLINSTALL: Normalize authorization
ALTER AUTHORIZATION ON DATABASE::{0} to sa
", dbname));
yield return(string.Format(
@"--SQLINSTALL: CHECK SCHEMA
if SCHEMA_ID('{0}') is null {1}
", args.Schema,
QueryGeneratorHelper.GenerateExecuteSql("create schema {0}", args.Schema)));
}
private void GenerateScript(SqlInstallerConsoleProgramArgs args) {
var scripts = GenerateScripts(args);
var script = new StringBuilder();
foreach (var s in scripts) {
script.AppendLine(s);
script.AppendLine("GO");
}
if (args.ScriptFile.IsNotEmpty()) {
File.WriteAllText(args.ScriptFile, script.ToString());
}
else {
Console.WriteLine(script.ToString());
}
}
private static IEnumerable<string> GetDatabasePrepareScripts(SqlInstallerConsoleProgramArgs args, string dbname) {
yield return string.Format(
@"--SQLINSTALL: USE DATABASE
use {0}", dbname);
yield return string.Format(
@"--SQLINSTALL: SET DATABASE TRUSTWORTHY
ALTER DATABASE {0} SET TRUSTWORTHY ON", dbname);
yield return
@"--SQLINSTALL: SET CLR ON
sp_configure 'clr enabled', 1
";
yield return
@"--SQLINSTALL: RECONFIGURE
reconfigure
";
yield return
@"--SQLINSTALL: Normalize owner
EXEC sp_changedbowner 'sa'
";
yield return string.Format(
@"--SQLINSTALL: Normalize authorization
ALTER AUTHORIZATION ON DATABASE::{0} to sa
", dbname);
}
private static IEnumerable<string> GetCreateCustomLibrariesScripts(SqlInstallerConsoleProgramArgs args,
List<AssemblyName> customdepends,
Assembly assembly) {
var dir = Path.GetDirectoryName(Path.GetFullPath(args.AssemblyName + ".dll"));
foreach (var dep in customdepends) {
yield return
string.Format(
@"
--SQLINSTALL: Create assembly {0}
if not exists(select assembly_id from sys.assemblies where name='{0}')
CREATE ASSEMBLY [{0}]
AUTHORIZATION dbo
FROM {1}
WITH PERMISSION_SET = UNSAFE
else begin
begin try
ALTER ASSEMBLY [{0}]
FROM {1}
WITH PERMISSION_SET = UNSAFE
end try
begin catch
declare @message nvarchar(max) set @message = ERROR_MESSAGE()
if @message not like '%MVID%' begin
declare @severity int set @severity = ERROR_SEVERITY()
declare @state int set @state = ERROR_STATE()
raiserror(@message,@severity,@state)
end
end catch
end
",
dep.Name, QueryGeneratorHelper.GetAssemblyBits(Path.Combine(dir, dep.Name + ".dll")));
}
yield return
string.Format(
@"
--SQLINSTALL: Create assembly {0}
CREATE ASSEMBLY [{0}]
AUTHORIZATION dbo
FROM {1}
WITH PERMISSION_SET = {2}
",
assembly.GetName().Name, QueryGeneratorHelper.GetAssemblyBits(Path.Combine(dir, assembly.GetName().Name + ".dll")), args.Safe ? "SAFE" : "UNSAFE")
;
}
/// <summary>
/// Generates set of commands to execute on SqlServer
/// </summary>
/// <param name="args"> </param>
/// <returns> </returns>
public IEnumerable<string> GenerateScripts(SqlInstallerConsoleProgramArgs args) {
_args = args;
var assembly = args.LoadAssembly();
var sysdepends =
(assembly.GetReferencedAssemblies().Where(
x => x.Name.StartsWith("System.") && x.Name != "System.Data" && x.Name != "System.Xml" && x.Name != "System" && x.Name!="System.Web")).
ToList();
var customdepends =
assembly.GetReferencedAssemblies().Where(
x => !(x.Name.StartsWith("System") || x.Name == "mscorlib" || x.Name.StartsWith("Microsoft."))).ToList();
foreach (var assemblyName in customdepends.ToArray()) {
var a = Assembly.Load(assemblyName);
foreach (
var ccd in
a.GetReferencedAssemblies().Where(
x => !(x.Name.StartsWith("System") || x.Name == "mscorlib" || x.Name.StartsWith("Microsoft.")))) {
if (!customdepends.Any(x => x.Name == ccd.Name)) {
customdepends.Add(ccd);
}
}
}
foreach (var assemblyName in customdepends) {
var a = Assembly.Load(assemblyName);
foreach (
var csd in
a.GetReferencedAssemblies().Where(
x => x.Name.StartsWith("System") && x.Name != "System.Data" && x.Name != "System.Xml" && x.Name != "System")) {
if (!sysdepends.Any(x => x.Name == csd.Name)) {
sysdepends.Add(csd);
}
}
}
customdepends.Sort((a, b) =>
{
var aa = Assembly.Load(a);
var ba = Assembly.Load(b);
if (aa.GetReferencedAssemblies().Any(x => x.Name == b.Name)) {
return 1;
}
if (ba.GetReferencedAssemblies().Any(x => x.Name == a.Name)) {
return -1;
}
return 0;
});
var items = GetExportedItems(assembly, args.Schema);
var dbname = QueryGeneratorHelper.GetSafeSqlName(args.Database);
//1 Prepare database
if (!args.NoScriptDatabase) {
foreach (var p in GetDatabasePrepareScripts(args, dbname)) {
yield return p;
}
}
yield return string.Format(
@"--SQLINSTALL: CHECK SCHEMA
if SCHEMA_ID('{0}') is null {1}
", args.Schema,
QueryGeneratorHelper.GenerateExecuteSql("create schema {0}", args.Schema));
//2 drop existed items
foreach (var item in items.Where(x => !(x is UserTypeWrapper))) {
yield return item.GetDropScript();
}
foreach (var item in items.Where(x => (x is UserTypeWrapper))) {
yield return item.GetDropScript();
}
//3 Prepare system libs and drop referenced libs
foreach (var p in GetCleanupAssembliesScripts(sysdepends, customdepends, assembly)) {
yield return p;
}
//4 generate assemblies
if (!_args.CleanOnly) {
foreach (var p in GetCreateCustomLibrariesScripts(args, customdepends, assembly)) {
yield return p;
}
foreach (var item in items.Where(x => (x is UserTypeWrapper))) {
yield return item.GetCreateScript();
}
foreach (var item in items.Where(x => ! (x is UserTypeWrapper))) {
yield return item.GetCreateScript();
}
}
}
private static IEnumerable<string> GetDatabasePrepareScripts(SqlInstallerConsoleProgramArgs args, string dbname) {
yield return string.Format(
@"--SQLINSTALL: USE DATABASE
use {0}", dbname);
yield return string.Format(
@"--SQLINSTALL: SET DATABASE TRUSTWORTHY
ALTER DATABASE {0} SET TRUSTWORTHY ON", dbname);
yield return
@"--SQLINSTALL: SET CLR ON
sp_configure 'clr enabled', 1
";
yield return
@"--SQLINSTALL: RECONFIGURE
reconfigure
";
yield return
@"--SQLINSTALL: Normalize owner
EXEC sp_changedbowner 'sa'
";
yield return string.Format(
@"--SQLINSTALL: Normalize authorization
ALTER AUTHORIZATION ON DATABASE::{0} to sa
", dbname);
yield return string.Format(
@"--SQLINSTALL: CHECK SCHEMA
if SCHEMA_ID('{0}') is null {1}
", args.Schema,
QueryGeneratorHelper.GenerateExecuteSql("create schema {0}", args.Schema));
}
/// <summary>
/// Generates set of commands to execute on SqlServer
/// </summary>
/// <param name="args"> </param>
/// <returns> </returns>
public IEnumerable <string> GenerateScripts(SqlInstallerConsoleProgramArgs args)
{
_args = args;
var assembly = args.LoadAssembly();
var sysdepends =
(assembly.GetReferencedAssemblies().Where(
x => x.Name.StartsWith("System.") && x.Name != "System.Data" && x.Name != "System.Xml" && x.Name != "System")).
ToList();
var customdepends =
assembly.GetReferencedAssemblies().Where(
x => !(x.Name.StartsWith("System") || x.Name == "mscorlib" || x.Name.StartsWith("Microsoft."))).ToList();
foreach (var assemblyName in customdepends.ToArray())
{
var a = Assembly.Load(assemblyName);
foreach (
var ccd in
a.GetReferencedAssemblies().Where(
x => !(x.Name.StartsWith("System") || x.Name == "mscorlib" || x.Name.StartsWith("Microsoft."))))
{
if (!customdepends.Any(x => x.Name == ccd.Name))
{
customdepends.Add(ccd);
}
}
}
foreach (var assemblyName in customdepends)
{
var a = Assembly.Load(assemblyName);
foreach (
var csd in
a.GetReferencedAssemblies().Where(
x => x.Name.StartsWith("System") && x.Name != "System.Data" && x.Name != "System.Xml" && x.Name != "System"))
{
if (!sysdepends.Any(x => x.Name == csd.Name))
{
sysdepends.Add(csd);
}
}
}
customdepends.Sort((a, b) =>
{
var aa = Assembly.Load(a);
var ba = Assembly.Load(b);
if (aa.GetReferencedAssemblies().Any(x => x.Name == b.Name))
{
return(1);
}
if (ba.GetReferencedAssemblies().Any(x => x.Name == a.Name))
{
return(-1);
}
return(0);
});
var items = GetExportedItems(assembly, args.Schema);
var dbname = QueryGeneratorHelper.GetSafeSqlName(args.Database);
//1 Prepare database
if (!args.NoScriptDatabase)
{
foreach (var p in GetDatabasePrepareScripts(args, dbname))
{
yield return(p);
}
}
//2 drop existed items
foreach (var item in items.Where(x => !(x is UserTypeWrapper)))
{
yield return(item.GetDropScript());
}
foreach (var item in items.Where(x => (x is UserTypeWrapper)))
{
yield return(item.GetDropScript());
}
//3 Prepare system libs and drop referenced libs
foreach (var p in GetCleanupAssembliesScripts(sysdepends, customdepends, assembly))
{
yield return(p);
}
//4 generate assemblies
if (!_args.CleanOnly)
{
foreach (var p in GetCreateCustomLibrariesScripts(args, customdepends, assembly))
{
yield return(p);
}
foreach (var item in items.Where(x => (x is UserTypeWrapper)))
{
yield return(item.GetCreateScript());
}
foreach (var item in items.Where(x => !(x is UserTypeWrapper)))
{
yield return(item.GetCreateScript());
}
}
}
