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() }); }