public static async Task AnalyseViews_GivenViewWithColumnNameContainingWhitespace_ProducesMessages()
{
var rule = new WhitespaceNameRule(RuleLevel.Error);
var viewName = new Identifier("test");
var testColumn = new DatabaseColumn(
" test_column ",
Mock.Of <IDbType>(),
false,
null,
null
);
var view = new DatabaseView(
viewName,
"select 1",
new List <IDatabaseColumn> {
testColumn
}
);
var views = new[] { view };
var hasMessages = await rule.AnalyseViews(views).AnyAsync().ConfigureAwait(false);
Assert.That(hasMessages, Is.True);
}
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);
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);
}
/// <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);
}
public static async Task AnalyseViews_GivenViewWithRegularColumnNames_ProducesNoMessages()
{
var rule = new ReservedKeywordNameRule(CreateFakeDialect(), RuleLevel.Error);
var viewName = new Identifier("test");
var testColumn = new DatabaseColumn(
"test_column",
Mock.Of <IDbType>(),
false,
null,
null
);
var view = new DatabaseView(
viewName,
"select 1",
new List <IDatabaseColumn> {
testColumn
}
);
var views = new[] { view };
var hasMessages = await rule.AnalyseViews(views).AnyAsync().ConfigureAwait(false);
Assert.That(hasMessages, Is.False);
}
private void UpdateTriggers(DatabaseView view, IList <DatabaseTrigger> triggers)
{
var viewTriggers = triggers.Where(x => x.SchemaOwner == view.SchemaOwner &&
x.TableName == view.Name);
view.Triggers.Clear();
view.Triggers.AddRange(viewTriggers);
}
private void ExecuteOpenDatabaseCommand()
{
var result = new DatabaseView().ShowDialog();
if (result == true)
{
DatabaseVisibility = Visibility.Visible;
}
}
public void InvalidSql()
{
const string viewSql = "CREATE OR VIEW dbo.X AS SELECT 0";
var(view, errors) = DatabaseView.FromSql(connection, viewSql);
Assert.IsNull(view);
Assert.AreNotEqual(0, errors.Count);
}
public void CountColumnReferences()
{
const string viewSql = "CREATE VIEW dbo.VActivePeople AS SELECT p.Id, p.FirstName, p.LastName, 'hardcoded' Ignored FROM dbo.VPeople p WHERE p.IsActive = 1";
var(view, errors) = DatabaseView.FromSql(connection, viewSql);
Assert.AreEqual(0, errors.Count);
Assert.IsNotNull(view);
Assert.AreEqual(4, view.References.ColumnReferences.Count);
}
public static void IsMaterialized_PropertyGet_ReturnsFalse()
{
Identifier viewName = "test_view";
const string definition = "select * from test";
var columns = new[] { Mock.Of <IDatabaseColumn>() };
var view = new DatabaseView(viewName, definition, columns);
Assert.That(view.IsMaterialized, Is.False);
}
public static void Definition_PropertyGet_EqualsCtorArg()
{
Identifier viewName = "test_view";
const string definition = "select * from test";
var columns = new[] { Mock.Of <IDatabaseColumn>() };
var view = new DatabaseView(viewName, definition, columns);
Assert.That(view.Definition, Is.EqualTo(definition));
}
public void CountColumnReferencesSkipParametersToIgnore()
{
const string viewSql = "CREATE VIEW dbo.VActivePeople AS SELECT CONVERT(varchar, p.Id) Id, dateadd(day, 1, p.DayOfBirth) DayOfBirth, CAST(10.5 AS INT) Number FROM dbo.VPeople p";
var(view, errors) = DatabaseView.FromSql(connection, viewSql);
Assert.AreEqual(0, errors.Count);
Assert.IsNotNull(view);
Assert.AreEqual(2, view.References.ColumnReferences.Count(r => r.MultiPartIdentifier[0].Value == "p"));
Assert.AreEqual(2, view.References.ColumnReferences.Count);
}
public ActionResult Edit(string id, DatabaseView entity)
{
var model = db.FirstOrDefault(x => x.Id == id);
if (model == null)
{
return(HttpNotFound());
}
TryUpdateModel(model);
db.Collection.Save(model);
return(RedirectToAction("Index"));
}
public void BuildView(DatabaseView view)
{
try
{
var txt = _migrationGenerator.AddView(view);
Clipboard.SetText(txt, TextDataFormat.UnicodeText);
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
}
public static void ToString_WhenInvoked_ReturnsExpectedString(string schema, string localName, string expectedOutput)
{
var viewName = Identifier.CreateQualifiedIdentifier(schema, localName);
const string definition = "select * from test";
var columns = new[] { Mock.Of <IDatabaseColumn>() };
var view = new DatabaseView(viewName, definition, columns);
var result = view.ToString();
Assert.That(result, Is.EqualTo(expectedOutput));
}
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);
}
public static string GenerateView(DatabaseView view)
{
if (_viewTemplate == null)
{
_viewTemplate = ReadTemplate("view");
}
var builder = new StringBuilder(_viewTemplate);
var properties = view.Columns.Select(GenerateAutoProperty).ToList();
var propertyString = properties.Aggregate((c, n) => c + n);
return(builder.Replace("$viewName$", view.Name)
.Replace("$properties$", propertyString).ToString());
}
public string BuildView(DatabaseView view)
{
StringBuilder sb = new StringBuilder();
try
{
sb.Append(m_migrationGenerator.AddView(view));
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
return(sb.ToString());
}
public static void Columns_PropertyGet_EqualsCtorArg()
{
Identifier viewName = "test_view";
const string definition = "select * from test";
Identifier columnName = "star";
var columnMock = new Mock <IDatabaseColumn>();
columnMock.SetupGet(c => c.Name).Returns(columnName);
var columns = new[] { columnMock.Object };
var view = new DatabaseView(viewName, definition, columns);
var viewColumnName = view.Columns[0].Name;
Assert.That(viewColumnName, Is.EqualTo(columnName));
}
public static async Task AnalyseViews_GivenViewWithRegularName_ProducesNoMessages()
{
var rule = new WhitespaceNameRule(RuleLevel.Error);
var viewName = new Identifier("test");
var view = new DatabaseView(
viewName,
"select 1",
new List <IDatabaseColumn>()
);
var views = new[] { view };
var hasMessages = await rule.AnalyseViews(views).AnyAsync().ConfigureAwait(false);
Assert.That(hasMessages, Is.False);
}
public static async Task AnalyseViews_GivenViewWithNameContainingReservedKeyword_ProducesMessages()
{
var rule = new ReservedKeywordNameRule(CreateFakeDialect(), RuleLevel.Error);
var viewName = new Identifier("SELECT");
var view = new DatabaseView(
viewName,
"select 1",
Array.Empty <IDatabaseColumn>()
);
var views = new[] { view };
var hasMessages = await rule.AnalyseViews(views).AnyAsync().ConfigureAwait(false);
Assert.That(hasMessages, Is.True);
}
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);
}
/// <summary>
/// Gets all views.
/// </summary>
public IList <DatabaseView> AllViews(CancellationToken ct)
{
if (ct.IsCancellationRequested)
{
return(DatabaseSchema.Views);
}
RaiseReadingProgress(SchemaObjectType.Views);
DataTable dt = _schemaReader.Views();
if (ct.IsCancellationRequested)
{
return(DatabaseSchema.Views);
}
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.Views);
List <DatabaseView> views = SchemaConverter.Views(dt);
var viewFilter = Exclusions.ViewFilter;
if (viewFilter != null)
{
views.RemoveAll(v => viewFilter.Exclude(v.Name));
}
//get full datatables for all tables, to minimize database calls
if (ct.IsCancellationRequested)
{
return(views);
}
RaiseReadingProgress(SchemaObjectType.ViewColumns);
var columnLoader = new ViewColumnLoader(_schemaReader);
var count = views.Count;
for (var index = 0; index < count; index++)
{
if (ct.IsCancellationRequested)
{
return(views);
}
DatabaseView v = views[index];
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.ViewColumns, v.Name, index, count);
v.Columns.AddRange(columnLoader.Load(v.Name, v.SchemaOwner));
}
DatabaseSchema.Views.Clear();
DatabaseSchema.Views.AddRange(views);
return(views);
}
private void AddTabPage(DatabaseServer databaseServer)
{
XtraTabPage newPage = new XtraTabPage
{
Text = databaseServer.DisplayName,
Tooltip = databaseServer.ConnectionString,
Tag = databaseServer
};
var view = new DatabaseView(newPage, databaseServer)
{
Dock = DockStyle.Fill
};
newPage.Controls.Add(view);
xtraTabControl.TabPages.Add(newPage);
xtraTabControl.SelectedTabPage = newPage;
}
public void InlineSimpleViewWithRemovedColumns()
{
const string viewSql = "CREATE VIEW dbo.VActivePeople AS SELECT p.Id, p.FirstName FROM dbo.VPeople p WHERE p.IsActive = 1";
var inliner = new DatabaseViewInliner(connection, DatabaseView.CreateOrAlter(viewSql), options);
Assert.AreEqual(0, inliner.Errors.Count);
Assert.AreNotEqual(viewSql, inliner.Sql);
var result = inliner.Result;
Assert.IsNotNull(result);
Assert.IsTrue(result.Sql.Contains("dbo.People"));
Assert.IsTrue(result.ConvertedSql.Contains("dbo.People"));
Assert.IsFalse(result.ConvertedSql.Contains("dbo.VPeople"));
Assert.IsFalse(result.ConvertedSql.Contains("UnusedFunction"));
Assert.IsFalse(result.ConvertedSql.Contains("LastName"));
}
public DataModel(MainWindow mainWindow)
{
MainWindow = mainWindow;
ServerList = new ServerList();
ServerSession = new ServerSession(this);
ServerStatus = new ServerStatus(this);
Database = new Database(this);
QuickSearch = new QuickSearch(this);
AdvancedSearch = new AdvancedSearch(this);
DatabaseView = new DatabaseView(this);
StreamsCollection = new StreamsCollection();
SavedPlaylists = new SavedPlaylists(this);
CurrentSong = new CurrentSong(this);
Playlist = new Playlist(this);
OutputCollection = new OutputCollection(this);
CustomDateNormalizer = new DateNormalizer();
CustomDateNormalizer.ReadFromSettings();
YearNormalizer = new DateNormalizer(new string[] {"YYYY"});
}
public DataModel(MainWindow mainWindow)
{
MainWindow = mainWindow;
NetworkLog = null;
ServerList = new ServerList();
ServerSession = new ServerSession(this);
ServerStatus = new ServerStatus(this);
Database = new Database(this);
QuickSearch = new QuickSearch(this);
AdvancedSearch = new AdvancedSearch(this);
DatabaseView = new DatabaseView(this);
StreamsCollection = new StreamsCollection();
SavedPlaylists = new SavedPlaylists(this);
CurrentSong = new CurrentSong(this);
Playlist = new Playlist(this);
OutputCollection = new OutputCollection(this);
CustomDateNormalizer = new DateNormalizer();
CustomDateNormalizer.ReadFromSettings();
YearNormalizer = new DateNormalizer(new string[] { "YYYY" });
}
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());
}
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 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) => BuildSqlTasks(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) => BuildSqlTasks(sqlType).BuildTableSelect(view);
menu.Items.Add(select);
var selectPaged = new ToolStripMenuItem("SELECT VIEW PAGED to clipboard");
selectPaged.Click += (s, ea) => BuildSqlTasks(sqlType).BuildTableSelectPaged(view);
menu.Items.Add(selectPaged);
}
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 string DropView(DatabaseView view)
{
return(_migration.DropView(view));
}