public void AddOrMerge(IEnumerable <T> items)
{
lock (m_Dictionary)
{
foreach (var item in items)
{
var key = m_Core.ExtractKey(item);
T existing;
if (m_Dictionary.TryGetValue(key, out existing))
{
existing.Merge(item);
}
else
{
m_Dictionary.Add(key, item);
var insert = new DbChange(DbChangeType.Inserted, item.Table.Name, null, item);
m_IndexChangedManager.Invoke(this, new DbIndexChangeEventArgs(new[] { insert }));
}
}
}
}
public void SerializationTest()
{
var change = new DbChange(
//new object[] {1, 2L, "one", DateTime.Now.Date},
DbChangeType.Updated,
"TestTable",
new string[] { "ColumnA", "ColumnB" },
null
);
using (var memory = new MemoryStream())
{
SerializeDataContractXml(change, memory);
memory.Position = 0;
var result = DeserializeDataContractXml <DbChange>(memory);
var columns = new HashSet <string>(new string[] { "ColumnA", "ColumnB" });
var affected = result.AffectedColumns.ToList().TrueForAll(columns.Contains);
Assert.Equal(result.ChangeType, DbChangeType.Updated);
Assert.Equal(result.AffectedTable, "TestTable");
Assert.True(affected);
//Assert.AreEqual(result.Identity.Length, 4);
//Assert.AreEqual(result.Identity[0], 1);
//Assert.AreEqual(result.Identity[1], 2L);
//Assert.AreEqual(result.Identity[2], "one");
//Assert.AreEqual(result.Identity[3], DateTime.Now.Date);
}
}
public IEnumerable <DbChange> GetCreateScripts(string schema)
{
var createRawTable = new DbChange();
var script = new StringBuilder();
//script.AppendLine($"CREATE TABLE [{schema}].[{Name}] (");
script.AppendLine($"CREATE TABLE [{Name}] ("); //should use default schema!
script.AppendLine(" ID BIGINT NOT NULL,");
var writeColumns = string.Join(", " + Environment.NewLine, from field in Fields
select field.MsSqlCreate());
script.AppendLine(writeColumns);
script.AppendLine($", CONSTRAINT PK_{Name} PRIMARY KEY (ID)");
script.AppendLine(")");
if (script.Length > 4000)
{
// we need to split this create into more than one insert....
// TODO: write this algorithm!
throw new NotImplementedException("Too many columns. create table exceeds 4000 character limit!");
}
createRawTable.Forward = script.ToString();
//createRawTable.Backward = $"DROP TABLE [{schema}].[{Name}]";
createRawTable.Backward = $"DROP TABLE [{Name}]";
yield return(createRawTable);
//next foriegn keys
// var fkChange = new DbChange{
// Forward = $"ALTER TABLE [{schema}].[{Name}] WITH CHECK ADD CONSTRAINT [FK_DbEntity_{schema}_{Name}] FOREIGN KEY([ID])"
// + $"REFERENCES [dbo].[DbEntity] ([ID])",
// Backward = $"ALTER TABLE [{schema}].[{Name}] DROP CONSTRAINT [FK_DbEntity_{schema}_{Name}]"
// };
var constraint = new StringBuilder();
var rollback = new StringBuilder();
constraint.AppendLine("DECLARE @ConstStr AS NVARCHAR(MAX) = ");
rollback.AppendLine(constraint.ToString());
constraint.Append($"'ALTER TABLE [{Name}] WITH CHECK ADD CONSTRAINT [FK_DbEntity_'+SCHEMA_NAME()+'_{Name}] FOREIGN KEY([ID])");
constraint.Append($" REFERENCES [dbo].[DbEntity] ([ID])';");
constraint.AppendLine();
constraint.AppendLine("EXEC(@ConstStr)");
rollback.Append($"'ALTER TABLE [{Name}] DROP CONSTRAINT [FK_DbEntity_'+SCHEMA_NAME()+'_{Name}]'");
rollback.AppendLine();
rollback.AppendLine("EXEC(@ConstStr)");
var fkChange = new DbChange {
Forward = constraint.ToString(),
Backward = rollback.ToString()
};
yield return(fkChange);
}
public bool Remove(T item)
{
lock (m_Dictionary)
{
var key = m_Core.ExtractKey(item);
var result = m_Dictionary.Remove(key);
if (result)
{
var delete = new DbChange(DbChangeType.Deleted, item.Table.Name, null, item);
m_IndexChangedManager.Invoke(this, new DbIndexChangeEventArgs(new[] { delete }));
}
return(result);
}
}
public bool Remove(K key)
{
lock (m_Dictionary)
{
T existing;
if (m_Dictionary.TryGetValue(key, out existing))
{
var delete = new DbChange(DbChangeType.Deleted, existing.Table.Name, null, existing);
m_IndexChangedManager.Invoke(this, new DbIndexChangeEventArgs(new[] { delete }));
}
return(true);
}
}
public void AddOrFail(IEnumerable <T> items)
{
lock (m_Dictionary)
{
foreach (var item in items)
{
var key = m_Core.ExtractKey(item);
m_Dictionary.Add(key, item);
var insert = new DbChange(DbChangeType.Inserted, item.Table.Name, null, item);
m_IndexChangedManager.Invoke(this, new DbIndexChangeEventArgs(new[] { insert }));
}
}
}
private void CheckForUpdates()
{
using (var repo = new DbChangeRepository())
{
var dbChanges = repo.GetEntities();
if (dbChanges.Length > 0)
{
var dbChange = dbChanges.Last();
if (_lastDbChange == null)
{
_lastDbChange = dbChange;
}
else if (_lastDbChange.Id != dbChange.Id)
{
_lastDbChange = dbChange;
NotifyDbChanged?.Invoke();
}
}
}
}
public IEnumerable <DbChange> GetCreateScripts()
{
var createSchema = new DbChange {
Forward = $"CREATE SCHEMA [{Name}]",
Backward = $"DROP SCHEMA [{Name}]"
};
yield return(createSchema);
var createLogin = new DbChange {
Forward = $"CREATE LOGIN [user_{Name}] WITH PASSWORD = '******';",
Backward = $"DROP LOGIN [user_{Name}];"
};
yield return(createLogin);
var createUser = new DbChange {
Forward = $"CREATE USER [user_{Name}] FOR LOGIN [user_{Name}] WITH DEFAULT_SCHEMA = [{Name}]",
Backward = $"DROP USER IF EXISTS [user_{Name}]"
};
yield return(createUser);
var denyPermissions = new DbChange {
Forward = $"DENY EXECUTE ON SCHEMA:: [{Name}] TO [user_{Name}]",
Backward = $"REVOKE EXECUTE ON SCHEMA:: [{Name}] TO [user_{Name}]"
};
yield return(denyPermissions); //removes permissions to procs entirly
var grantPermissions = new DbChange {
Forward = $"GRANT CONTROL ON SCHEMA:: [{Name}] TO [user_{Name}]",
Backward = $"REVOKE CONTROL ON SCHEMA:: [{Name}] TO [user_{Name}]"
};
yield return(grantPermissions);
var grantCreateTable = new DbChange {
Forward = $"GRANT CREATE TABLE TO [user_{Name}]",
Backward = $"REVOKE CREATE TABLE TO [user_{Name}]"
};
yield return(grantCreateTable);
var grantDboPermissions = new DbChange {
Forward = $"GRANT REFERENCES, SELECT, INSERT, UPDATE ON DbEntity TO [user_{Name}]",
Backward = $"REVOKE REFERENCES, SELECT, INSERT, UPDATE ON DbEntity TO [user_{Name}]"
};
yield return(grantCreateTable);
var grantSelectDbSchema = new DbChange {
Forward = $"GRANT SELECT ON DbSchema TO [user_{Name}]",
Backward = $"REVOKE SELECT ON DbSchema TO [user_{Name}]"
};
yield return(grantSelectDbSchema);
var grantDbRelationship = new DbChange {
Forward = $"GRANT SELECT, INSERT, DELETE ON DbRelationship TO [user_{Name}]",
Backward = $"REVOKE SELECT, INSERT, DELETE ON DbRelationship TO [user_{Name}]"
};
yield return(grantSelectDbSchema);
//TODO: add db changes to duplicate copiedschema
//TODO: create method for adding views to referenced schemas
}
