public Core.Database ToDatabase2()
{
var db = new Core.Database
{
Accounts = Accounts,
Categories = Categories,
};
db.Payees.AddRange(Payees.Select(p => new Core.Payee
{
Id = p.Id,
Name = p.Name,
DefaultCategoryId = p.DefaultCategory?.Id,
}));
db.Transactions.AddRange(Transactions.Select(t => new Core.Transaction
{
AccountId = t.Account?.Id,
Amount = t.Amount,
CategoryId = t.Category?.Id,
CheckedDate = t.CheckedDate,
Comment = t.Comment,
Id = t.Id,
LinkedTransactionId = t.LinkedTransaction?.Id,
PayeeId = t.Payee?.Id,
ReconciliationDate = t.ReconciliationDate,
ValueDate = t.ValueDate,
}));
db.ScheduledTransactions.AddRange(ScheduledTransactions.Select(t => new Core.ScheduledTransaction
{
AccountId = t.Account?.Id,
Amount = t.Amount,
CategoryId = t.Category?.Id,
CreditedAccountId = t.CreditedAccount?.Id,
Id = t.Id,
Name = t.Name,
NextOccurenceDate = t.NextOccurenceDate,
PayeeId = t.Payee?.Id,
RecurrenceRuleText = t.RecurrenceRuleText,
StartDate = t.StartDate,
Comment = t.Comment,
}));
return(db);
}
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()
});
}
