private void CreateResult(ResultType resultType, DatabaseView view, string script)
{
var result = new CompareResult
{
SchemaObjectType = SchemaObjectType.View,
ResultType = resultType,
Name = view.Name,
SchemaOwner = view.SchemaOwner,
Script = script
};
_results.Add(result);
}
private static DatabaseSchema PrepareModel()
{
var schema = new DatabaseSchema(null, null);
schema.AddTable("Categories")
.AddColumn("CategoryId", DbType.Int32).AddPrimaryKey()
.AddColumn("CategoryName", DbType.String);
var view = new DatabaseView { Name = "AlphabeticCategories"};
schema.Views.Add(view);
view
.AddColumn("CategoryId", DbType.Int32)
.AddColumn("CategoryName", DbType.String);
DatabaseSchemaFixer.UpdateReferences(schema);
PrepareSchemaNames.Prepare(schema, new Namer());
return schema;
}
private DatabaseSchema ReadEntityFramework(XElement storageSchema)
{
var databaseSchema = new DatabaseSchema(null, SqlType.SqlServer);
var entityContainer = storageSchema.Element(_schema + "EntityContainer");
if (entityContainer == null) return databaseSchema;
foreach (var entitySet in entityContainer.Elements(_schema + "EntitySet"))
{
var name = (string)entitySet.Attribute("Name");
var schema = (string)entitySet.Attribute("Schema");
var storeName = (string)entitySet.Attribute(_store + "Name");
var storeSchema = (string)entitySet.Attribute(_store + "Schema");
var type = (string)entitySet.Attribute(_store + "Type");
DatabaseTable table;
if (type.Equals("Tables", StringComparison.OrdinalIgnoreCase))
{
table = databaseSchema.AddTable(name);
table.SchemaOwner = schema;
}
else if (type.Equals("Views", StringComparison.OrdinalIgnoreCase))
{
var view = new DatabaseView { Name = storeName, SchemaOwner = storeSchema };
databaseSchema.Views.Add(view);
table = view;
}
else
{
//some other type eg something with a DefiningQuery
continue;
}
AddProperties(storageSchema, table);
}
AddForeignKeys(storageSchema, databaseSchema);
return databaseSchema;
}
public string DropView(DatabaseView view)
{
return "DROP VIEW " + SchemaPrefix(view.SchemaOwner) + Escape(view.Name) + ";"
+ _sqlFormatProvider.RunStatements();
}
public string AddView(DatabaseView view)
{
//CREATE VIEW cannot be combined with other statements in a batch, so be preceeded by and terminate with a "GO" (sqlServer) or "/" (Oracle)
var sql = view.Sql;
if (string.IsNullOrEmpty(sql))
{
//without the sql, we can't do anything
return "-- add view " + view.Name;
}
if (sql.TrimStart().StartsWith("CREATE VIEW ", StringComparison.OrdinalIgnoreCase))
{
//helpfully, SqlServer includes the create statement
return sql + _sqlFormatProvider.RunStatements();
}
//Oracle and MySql have CREATE OR REPLACE
var addView = "CREATE VIEW " + SchemaPrefix(view.SchemaOwner) + Escape(view.Name) + " AS " + sql;
return addView + _sqlFormatProvider.RunStatements();
}
public string AddView(DatabaseView view)
{
return _migration.AddView(view);
}
public string DropView(DatabaseView view)
{
return _migration.DropView(view);
}
/// <summary>
/// Converts the "Views" DataTable into <see cref="DatabaseView"/> objects.
/// </summary>
public static List<DatabaseView> Views(DataTable dt)
{
List<DatabaseView> list = new List<DatabaseView>();
ViewKeyMap viewKeyMap = new ViewKeyMap(dt);
foreach (DataRow row in dt.Rows)
{
if (viewKeyMap.TypeKey != null)
{
var type = row[viewKeyMap.TypeKey].ToString();
if (type != "VIEW") continue;
}
DatabaseView t = new DatabaseView();
t.Name = row[viewKeyMap.Key].ToString();
t.SchemaOwner = row[viewKeyMap.OwnerKey].ToString();
//ignore db2 system tables
if (viewKeyMap.TypeKey != null && t.SchemaOwner.StartsWith("SYS", StringComparison.OrdinalIgnoreCase)) continue;
if (viewKeyMap.HasSql) t.Sql = row[viewKeyMap.Definition].ToString();
list.Add(t);
}
return list;
}
private void BuildViewMenu(ToolStrip menu, DatabaseView view, SqlType sqlType)
{
if (!string.IsNullOrEmpty(view.Sql))
{
var create = new ToolStripMenuItem("CREATE VIEW " + view.Name + " to clipboard");
create.Click += (s, ea) => new SqlTasks(sqlType).BuildView(view);
menu.Items.Add(create);
var bar = new ToolStripSeparator();
menu.Items.Add(bar);
}
var select = new ToolStripMenuItem("SELECT VIEW to clipboard");
select.Click += (s, ea) => new SqlTasks(sqlType).BuildTableSelect(view);
menu.Items.Add(select);
var selectPaged = new ToolStripMenuItem("SELECT VIEW PAGED to clipboard");
selectPaged.Click += (s, ea) => new SqlTasks(sqlType).BuildTableSelectPaged(view);
menu.Items.Add(selectPaged);
}
/// <summary> Adds a view to model. </summary>
///
/// <param name="modelBuilder"> The builder that defines the model for the context being
/// created. </param>
/// <param name="dynamicClassFactory"> The dynamic class factory. </param>
/// <param name="failedViewColumns"> The failed view columns. </param>
/// <param name="view"> The view. </param>
///
/// <returns> A Type. </returns>
private static Type AddViewToModel(
DbModelBuilder modelBuilder,
DynamicClassFactory dynamicClassFactory,
List<string> failedViewColumns,
DatabaseView view)
{
var property = new Dictionary<string, DynamicPropertyData>();
foreach (var col in view.Columns)
{
if (col.DataType == null)
{
failedViewColumns.Add(string.Format("{0} - {1}", view.Name, col.ToString()));
}
else
{
Type colType = Type.GetType(col.DataType.NetDataType);
if (col.Nullable)
{
if (col.DataType.IsInt)
{
colType = typeof(Nullable<int>);
}
else if (col.DataType.IsDateTime)
{
colType = typeof(Nullable<DateTime>);
}
else if (col.DataType.IsFloat)
{
colType = typeof(Nullable<float>);
}
else if (col.DataType.IsNumeric)
{
colType = typeof(Nullable<decimal>);
}
else if (col.DataType.TypeName == "datetimeoffset")
{
colType = typeof(Nullable<DateTimeOffset>);
}
else if (col.DataType.NetDataTypeCSharpName == "bool")
{
colType = typeof(Nullable<bool>);
}
}
DynamicPropertyData dynamicPropertyData = new FieldPropertyData()
{
IsPrimaryKey = col.IsPrimaryKey,
IsForeignKey = col.IsForeignKey,
Order = view.Columns.IndexOf(col) + 1,
Nullable = col.Nullable,
Type = colType,
MaxLength = col.Length,
ColumnName = col.Name
};
string name = col.Name;
while (property.ContainsKey(name) || view.Name == name)
{
name = name + "1";
}
property.Add(name, dynamicPropertyData);
}
}
//Make all existing foreign keys as primary key if entity has no primary key
if (property.Values.FirstOrDefault(x => ((FieldPropertyData)x).IsPrimaryKey) == null)
{
var foreignRows = property.Values.Where(x => (((FieldPropertyData)x).IsForeignKey)).ToList();
foreignRows.ForEach(p => ((FieldPropertyData)p).IsPrimaryKey = true);
}
var viewType = CreateType(dynamicClassFactory, view.Name, property);
var entity = modelBuilder.Entity(viewType);
var methodInfoMap = entity.TypeConfiguration.GetType().GetMethod("MapToStoredProcedures", new Type[] { });
methodInfoMap.Invoke(entity.TypeConfiguration, new object[] { });
return viewType;
}
private static DatabaseSchema PrepareModel()
{
var schema = new DatabaseSchema(null, null);
schema.AddTable("Categories")
.AddColumn("CategoryId", DbType.Int32).AddPrimaryKey()
.AddColumn("CategoryName", DbType.String);
schema.AddTable("Products")
.AddColumn("ProductId", DbType.Int32).AddPrimaryKey()
.AddColumn("ProductName", DbType.String)
.AddColumn("CategoryId", DbType.Int32).AddForeignKey("fk", "Categories");
var view = new DatabaseView { Name = "ActiveCategories" };
view.AddColumn<string>("CategoryName");
schema.Views.Add(view);
DatabaseSchemaFixer.UpdateReferences(schema);
return schema;
}
public void BuildView(DatabaseView view)
{
try
{
var txt = _migrationGenerator.AddView(view);
Clipboard.SetText(txt, TextDataFormat.UnicodeText);
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
}
public void WriteViewTest()
{
//arrange
var view = new DatabaseView();
view.Name = "AlphabeticNames";
view.AddColumn("FirstName", typeof(string)).AddNullable()
.AddColumn("LastName", typeof(string)).AddNullable();
var schema = new DatabaseSchema(null, null);
schema.Views.Add(view);
PrepareSchemaNames.Prepare(schema, new Namer());
var codeWriterSettings = new CodeWriterSettings
{
CodeTarget = CodeTarget.PocoNHibernateHbm,
IncludeViews = true
};
var cw = new ClassWriter(view, codeWriterSettings);
//act
var txt = cw.Write();
//assert
var hasFirstName = txt.Contains("public virtual string FirstName");
var hasLastName = txt.Contains("public virtual string LastName");
var hasEquals = txt.Contains("public override bool Equals(object obj)");
Assert.IsTrue(hasFirstName);
Assert.IsTrue(hasLastName);
Assert.IsTrue(hasEquals);
}