public Core.DatabaseGeneratorResult Generate(Core.Database database)
{
System.Text.StringBuilder finalSQL = new StringBuilder();
database.UserName = String.IsNullOrWhiteSpace(database.UserName) ? string.Format("{0}_user", database.Name) : database.UserName;
database.Password = String.IsNullOrWhiteSpace(database.Password) ? GeneratePassword() : database.Password;
LOG.InfoFormat("Generating database sql creation script.");
finalSQL.AppendFormat(@"IF EXISTS (SELECT * FROM sys.databases WHERE name = '{0}')
BEGIN
DROP DATABASE WHERE name = '{0}'
END
CREATE DATABASE [{0}];
", database.Name);
//log: generating database user/login and assign roles
finalSQL.AppendFormat(@"CREATE LOGIN [{0}] WITH PASSWORD='******',
DEFAULT_DATABASE=[{2}], CHECK_POLICY=OFF
CREATE USER [{0}] FOR LOGIN [{0}]
EXEC sp_addrolemember N'db_datareader', N'{2}'
EXEC sp_addrolemember N'db_datawriter', N'{2}'
", database.UserName, database.Password, database.Name);
finalSQL.AppendLine(ROBOT_BLOCKS_TAG);
LOG.Info("Generating database.");
foreach (Core.Table t in database.Tables)
{
LOG.Info(m => m("Generating Table: {0}", t.Name));
System.Text.StringBuilder columnDefinitions = new StringBuilder();
bool first = true;
foreach (Core.Column c in t.Columns)
{
string columnSQL = GetColumnDefinition(c);
if (!first)
{
columnDefinitions.AppendFormat(@",
{0}", columnSQL);
}
else
{
first = false;
columnDefinitions.AppendFormat(@"{0}", columnSQL);
}
}
finalSQL.AppendLine();
finalSQL.AppendFormat("-- creating table {0}", t.Name);
finalSQL.AppendLine();
finalSQL.AppendFormat(@"CREATE TABLE {0}
(
{1}
);
", t.Name, columnDefinitions);
//generate indices on this table
finalSQL.AppendLine();
if (t.Indices.Any())
{
finalSQL.AppendLine(string.Format("-- indexes for {0}", t.Name));
}
foreach (Core.Index index in t.Indices)
{
finalSQL.AppendFormat(@"CREATE {0} INDEX IX_{1}_{2}
ON {1} ({3});
",
GetIndexTypeAsString(index),
t.Name,
String.Join("_", index.ColumnNames),
String.Join(",", index.ColumnNames));
}
}
//generate foreign keys
foreach (Core.Table t in database.Tables)
{
//generate cascading rules on record deletion on table
finalSQL.AppendLine();
if (t.Relationships.Any())
{
finalSQL.AppendLine(string.Format("-- fk constraints for {0}", t.Name));
}
foreach (Core.Relationship relationship in t.Relationships)
{
string onDeleteRule = string.Empty;
if (relationship.IsRequired)
{
onDeleteRule = "ON DELETE CASCADE";
}
else
{
onDeleteRule = "ON DELETE SET NULL";
}
finalSQL.AppendFormat(@"ALTER TABLE {0}
ADD CONSTRAINT fk_{0}_{1}
FOREIGN KEY ({2})
REFERENCES {1}({3}) {4}
",
relationship.FromTable,
relationship.ToTable,
relationship.FromColumnNamed,
relationship.ToColumnNamed,
onDeleteRule);
}
}
return(new Core.DatabaseGeneratorResult()
{
IsSuccessful = true,
Output = finalSQL.ToString()
});
}
public void Execute(IJobExecutionContext context)
{
_logger.InfoFormat("TestJob 测试");
}
