public string CreateForeignKeyConstraintsScriptText(DmRelation constraint)
{
StringBuilder stringBuilder = new StringBuilder();
var constraintName =
$"Create Constraint {constraint.RelationName} " +
$"between parent {constraint.ParentTable.TableName} " +
$"and child {constraint.ChildTable.TableName}";
var constraintScript = BuildForeignKeyConstraintsCommand(constraint).CommandText;
stringBuilder.Append(SqlBuilder.WrapScriptTextWithComments(constraintScript, constraintName));
stringBuilder.AppendLine();
return(stringBuilder.ToString());
}
private SqlCommand BuildForeignKeyConstraintsCommand(DmRelation foreignKey)
{
var sqlCommand = new SqlCommand();
string childTable = foreignKey.ChildTable.TableName;
string childSchema = foreignKey.ChildTable.Schema;
string childFullName = String.IsNullOrEmpty(childSchema) ? childTable : $"{childSchema}.{childTable}";
var childTableName = new ObjectNameParser(childFullName);
string parentTable = foreignKey.ParentTable.TableName;
string parentSchema = foreignKey.ParentTable.Schema;
string parentFullName = String.IsNullOrEmpty(parentSchema) ? parentTable : $"{parentSchema}.{parentTable}";
var parentTableName = new ObjectNameParser(parentFullName);
var relationName = NormalizeRelationName(foreignKey.RelationName);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("ALTER TABLE ");
stringBuilder.AppendLine(childTableName.FullQuotedString);
stringBuilder.Append("ADD CONSTRAINT ");
stringBuilder.AppendLine(relationName);
stringBuilder.Append("FOREIGN KEY (");
string empty = string.Empty;
foreach (var childColumn in foreignKey.ChildColumns)
{
var childColumnName = new ObjectNameParser(childColumn.ColumnName);
stringBuilder.Append($"{empty} {childColumnName.FullQuotedString}");
empty = ", ";
}
stringBuilder.AppendLine(" )");
stringBuilder.Append("REFERENCES ");
stringBuilder.Append(parentTableName.FullQuotedString).Append(" (");
empty = string.Empty;
foreach (var parentdColumn in foreignKey.ParentColumns)
{
var parentColumnName = new ObjectNameParser(parentdColumn.ColumnName);
stringBuilder.Append($"{empty} {parentColumnName.FullQuotedString}");
empty = ", ";
}
stringBuilder.Append(" ) ");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
/// <summary>
/// Initializes a new instance of the DmSetSurrogate class from an existing DmSet
/// </summary>
public DmSetSurrogate(DmSet ds)
{
if (ds == null)
{
throw new ArgumentNullException("ds");
}
this.DmSetName = ds.DmSetName;
this.CultureInfoName = ds.Culture.Name;
this.CaseSensitive = ds.CaseSensitive;
this.Tables = new List <DmTableSurrogate>(ds.Tables.Count);
for (int i = 0; i < ds.Tables.Count; i++)
{
this.Tables.Add(new DmTableSurrogate(ds.Tables[i]));
}
if (ds.Relations != null && ds.Relations.Count > 0)
{
this.Relations = new List <DmRelationSurrogate>(ds.Relations.Count);
for (int i = 0; i < ds.Relations.Count; i++)
{
DmRelation dr = ds.Relations[i];
DmRelationSurrogate drs = new DmRelationSurrogate();
drs.ChildKeySurrogates = new DmColumnSurrogate[dr.ChildKey.Columns.Length];
for (int keyIndex = 0; keyIndex < dr.ChildKey.Columns.Length; keyIndex++)
{
drs.ChildKeySurrogates[keyIndex] = new DmColumnSurrogate(dr.ChildKey.Columns[keyIndex]);
}
drs.ParentKeySurrogates = new DmColumnSurrogate[dr.ParentKey.Columns.Length];
for (int keyIndex = 0; keyIndex < dr.ParentKey.Columns.Length; keyIndex++)
{
drs.ParentKeySurrogates[keyIndex] = new DmColumnSurrogate(dr.ParentKey.Columns[keyIndex]);
}
drs.RelationName = dr.RelationName;
this.Relations.Add(drs);
}
}
}
private MySqlCommand BuildForeignKeyConstraintsCommand(DmRelation foreignKey)
{
var sqlCommand = new MySqlCommand();
var childTable = foreignKey.ChildTable;
var childTableName = new ObjectNameParser(childTable.TableName, "`", "`");
var parentTable = foreignKey.ParentTable;
var parentTableName = new ObjectNameParser(parentTable.TableName, "`", "`");;
var relationName = NormalizeRelationName(foreignKey.RelationName);
DmColumn[] foreignKeyColumns = foreignKey.ChildColumns;
DmColumn[] referencesColumns = foreignKey.ParentColumns;
var stringBuilder = new StringBuilder();
stringBuilder.Append("ALTER TABLE ");
stringBuilder.AppendLine(childTableName.FullQuotedString);
stringBuilder.Append("ADD CONSTRAINT ");
stringBuilder.AppendLine($"`{relationName}`");
stringBuilder.Append("FOREIGN KEY (");
string empty = string.Empty;
foreach (var foreignKeyColumn in foreignKeyColumns)
{
var foreignKeyColumnName = new ObjectNameParser(foreignKeyColumn.ColumnName, "`", "`");
stringBuilder.Append($"{empty} {foreignKeyColumnName.FullQuotedString}");
empty = ", ";
}
stringBuilder.AppendLine(" )");
stringBuilder.Append("REFERENCES ");
stringBuilder.Append(parentTableName.FullQuotedString).Append(" (");
empty = string.Empty;
foreach (var referencesColumn in referencesColumns)
{
var referencesColumnName = new ObjectNameParser(referencesColumn.ColumnName, "`", "`");
stringBuilder.Append($"{empty} {referencesColumnName.FullQuotedString}");
empty = ", ";
}
stringBuilder.Append(" ) ");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
/// <summary>
/// For a foreign key, check if the Parent table exists
/// </summary>
private bool EnsureForeignKeysTableExist(DmRelation foreignKey)
{
var childTable = foreignKey.ChildTable;
var parentTable = foreignKey.ParentTable;
// The foreignkey comes from the child table
var ds = foreignKey.ChildTable.DmSet;
if (ds == null)
{
return(false);
}
// Check if the parent table is part of the sync configuration
var exist = ds.Tables.Any(t => ds.IsEqual(t.TableName, parentTable.TableName));
if (!exist)
{
return(false);
}
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
connection.Open();
}
return(SqlManagementUtils.TableExists(connection, transaction, parentTable.TableName));
}
catch (Exception ex)
{
Debug.WriteLine($"Error during EnsureForeignKeysTableExist : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
private NpgsqlCommand BuildForeignKeyConstraintsCommand(DmRelation foreignKey)
{
var sqlCommand = new NpgsqlCommand();
var childTable = foreignKey.ChildTable;
var childTableName = new ObjectNameParser(childTable.TableName.ToLowerInvariant(), "\"", "\"");
var parentTable = foreignKey.ParentTable;
var parentTableName = new ObjectNameParser(parentTable.TableName.ToLowerInvariant(), "\"", "\"");
var relationName = foreignKey.RelationName.Length > 50
? foreignKey.RelationName.Substring(0, 50)
: foreignKey.RelationName;
var stringBuilder = new StringBuilder();
stringBuilder.Append("ALTER TABLE ");
stringBuilder.AppendLine(parentTableName.QuotedString);
stringBuilder.Append("ADD CONSTRAINT ");
stringBuilder.AppendLine(relationName);
stringBuilder.Append("FOREIGN KEY (");
var empty = string.Empty;
foreach (var parentdColumn in foreignKey.ParentColumns)
{
var parentColumnName = new ObjectNameParser(parentdColumn.ColumnName.ToLowerInvariant(), "\"", "\"");
stringBuilder.Append($"{empty} {parentColumnName.QuotedString}");
empty = ", ";
}
stringBuilder.AppendLine(" )");
stringBuilder.Append("REFERENCES ");
stringBuilder.Append(childTableName.QuotedString).Append(" (");
empty = string.Empty;
foreach (var childColumn in foreignKey.ChildColumns)
{
var childColumnName = new ObjectNameParser(childColumn.ColumnName.ToLowerInvariant(), "\"", "\"");
stringBuilder.Append($"{empty} {childColumnName.QuotedString}");
}
stringBuilder.Append(" ) ");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
public bool NeedToCreateForeignKeyConstraints(DmRelation foreignKey)
{
string parentTable = foreignKey.ParentTable.TableName;
string parentSchema = foreignKey.ParentTable.Schema;
string parentFullName = string.IsNullOrEmpty(parentSchema) ? parentTable : $"{parentSchema}.{parentTable}";
var relationName = NormalizeRelationName(foreignKey.RelationName);
bool alreadyOpened = this.connection.State == ConnectionState.Open;
// Don't want foreign key on same table since it could be a problem on first
// sync. We are not sure that parent row will be inserted in first position
if (string.Equals(parentTable, foreignKey.ChildTable.TableName, StringComparison.CurrentCultureIgnoreCase))
{
return(false);
}
try
{
if (!alreadyOpened)
{
this.connection.Open();
}
var dmTable = MySqlManagementUtils.RelationsForTable(this.connection, this.transaction, parentFullName);
var foreignKeyExist = dmTable.Rows.Any(r =>
dmTable.IsEqual(r["ForeignKey"].ToString(), relationName));
return(!foreignKeyExist);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during checking foreign keys: {ex}");
throw;
}
finally
{
if (!alreadyOpened && this.connection.State != ConnectionState.Closed)
{
this.connection.Close();
}
}
}
internal void ReadSchemaIntoDmSet(DmSet ds)
{
var dmTableSurrogateArray = this.Tables;
for (int i = 0; i < dmTableSurrogateArray.Count; i++)
{
DmTableSurrogate dmTableSurrogate = dmTableSurrogateArray[i];
DmTable dmTable = new DmTable();
dmTableSurrogate.ReadSchemaIntoDmTable(dmTable);
dmTable.Culture = new CultureInfo(dmTableSurrogate.CultureInfoName);
dmTable.CaseSensitive = dmTableSurrogate.CaseSensitive;
dmTable.TableName = dmTableSurrogate.TableName;
ds.Tables.Add(dmTable);
}
if (this.Relations != null && this.Relations.Count > 0)
{
foreach (var dmRelationSurrogate in this.Relations)
{
DmColumn[] parentColumns = new DmColumn[dmRelationSurrogate.ParentKeySurrogates.Length];
DmColumn[] childColumns = new DmColumn[dmRelationSurrogate.ChildKeySurrogates.Length];
for (int i = 0; i < parentColumns.Length; i++)
{
var columnName = dmRelationSurrogate.ParentKeySurrogates[i].ColumnName;
var tableName = dmRelationSurrogate.ParentKeySurrogates[i].TableName;
parentColumns[i] = ds.Tables[tableName].Columns[columnName];
columnName = dmRelationSurrogate.ChildKeySurrogates[i].ColumnName;
tableName = dmRelationSurrogate.ChildKeySurrogates[i].TableName;
childColumns[i] = ds.Tables[tableName].Columns[columnName];
}
DmRelation relation = new DmRelation(dmRelationSurrogate.RelationName, parentColumns, childColumns);
ds.Relations.Add(relation);
}
}
}
public bool NeedToCreateForeignKeyConstraints(DmRelation foreignKey, DbBuilderOption builderOptions)
{
if (!builderOptions.HasFlag(DbBuilderOption.CreateOrUseExistingSchema))
{
return(false);
}
string parentTable = foreignKey.ParentTable.TableName;
string parentSchema = foreignKey.ParentTable.Schema;
string parentFullName = String.IsNullOrEmpty(parentSchema) ? parentTable : $"{parentSchema}.{parentTable}";
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
connection.Open();
}
var dmTable = SqlManagementUtils.RelationsForTable(connection, transaction, parentFullName);
var foreignKeyExist = dmTable.Rows.Any(r =>
dmTable.IsEqual(r["ForeignKey"].ToString(), foreignKey.RelationName));
return(!foreignKeyExist);
}
catch (Exception ex)
{
Logger.Current.Error($"Error during checking foreign keys: {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
private SqlCommand BuildForeignKeyConstraintsCommand(DmRelation foreignKey)
{
SqlCommand sqlCommand = new SqlCommand();
var childTable = foreignKey.ChildTable;
var childTableName = new ObjectNameParser(childTable.TableName);
var parentTable = foreignKey.ParentTable;
var parentTableName = new ObjectNameParser(parentTable.TableName);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("ALTER TABLE ");
stringBuilder.AppendLine(childTableName.QuotedString);
stringBuilder.Append("ADD CONSTRAINT ");
stringBuilder.AppendLine(foreignKey.RelationName);
stringBuilder.Append("FOREIGN KEY (");
string empty = string.Empty;
foreach (var childColumn in foreignKey.ChildColumns)
{
var childColumnName = new ObjectNameParser(childColumn.ColumnName);
stringBuilder.Append($"{empty} {childColumnName.QuotedString}");
empty = ", ";
}
stringBuilder.AppendLine(" )");
stringBuilder.Append("REFERENCES ");
stringBuilder.Append(parentTableName.QuotedString).Append(" (");
empty = string.Empty;
foreach (var parentColumn in foreignKey.ParentColumns)
{
var parentColumnName = new ObjectNameParser(parentColumn.ColumnName);
stringBuilder.Append($"{empty} {parentColumnName.QuotedString}");
}
stringBuilder.Append(" ) ");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
private SqlCommand BuildForeignKeyConstraintsCommand(DmRelation foreignKey)
{
var sqlCommand = new SqlCommand();
var childTableName = ParserName.Parse(foreignKey.ChildTable).Quoted().Schema().ToString();
var parentTableName = ParserName.Parse(foreignKey.ParentTable).Quoted().Schema().ToString();
var relationName = NormalizeRelationName(foreignKey.RelationName);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("ALTER TABLE ");
stringBuilder.AppendLine(childTableName);
stringBuilder.Append("ADD CONSTRAINT ");
stringBuilder.AppendLine(relationName);
stringBuilder.Append("FOREIGN KEY (");
string empty = string.Empty;
foreach (var childColumn in foreignKey.ChildColumns)
{
var childColumnName = ParserName.Parse(childColumn).Quoted().ToString();
stringBuilder.Append($"{empty} {childColumnName}");
empty = ", ";
}
stringBuilder.AppendLine(" )");
stringBuilder.Append("REFERENCES ");
stringBuilder.Append(parentTableName).Append(" (");
empty = string.Empty;
foreach (var parentdColumn in foreignKey.ParentColumns)
{
var parentColumnName = ParserName.Parse(parentdColumn).Quoted().ToString();
stringBuilder.Append($"{empty} {parentColumnName}");
empty = ", ";
}
stringBuilder.Append(" ) ");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
public void CreateForeignKeyConstraints(DmRelation constraint)
{
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
connection.Open();
}
using (var command = BuildForeignKeyConstraintsCommand(constraint))
{
command.Connection = connection;
if (transaction != null)
{
command.Transaction = transaction;
}
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Debug.WriteLine($"Error during CreateForeignKeyConstraints : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
public bool NeedToCreateForeignKeyConstraints(DmRelation constraint)
{
throw new NotImplementedException();
}
public void Properties()
{
DmSet set = new DmSet("DMSET");
DmTable clientsTable = new DmTable("Clients");
DmTable productsTable = new DmTable("Products");
set.Tables.Add(clientsTable);
set.Tables.Add(productsTable);
DmColumn productId = new DmColumn <Int32>("Id");
productId.AllowDBNull = false;
productId.IsAutoIncrement = true;
productId.IsCompute = false;
productId.IsUnicode = false;
productId.IsUnsigned = false;
productsTable.Columns.Add(productId);
DmColumn fkClientId = new DmColumn <Guid>("clientId");
fkClientId.AllowDBNull = true;
productsTable.Columns.Add(fkClientId);
DmColumn productName = new DmColumn <string>("name");
productName.AllowDBNull = true;
productName.DbType = System.Data.DbType.StringFixedLength;
productName.MaxLength = 150;
productId.IsCompute = false;
productId.IsUnicode = true;
productId.IsUnsigned = true;
productsTable.Columns.Add(productName);
DmColumn productPrice = new DmColumn <Decimal>("price");
productPrice.AllowDBNull = false;
productPrice.DbType = System.Data.DbType.VarNumeric;
productPrice.Precision = 6;
productPrice.Scale = 2;
// for test purpose
productId.IsCompute = true;
productsTable.Columns.Add(productPrice);
productsTable.PrimaryKey = new DmKey(new DmColumn[] { productId, productName, productPrice });
DmColumn clientId = new DmColumn <Guid>("Id");
clientId.AllowDBNull = false;
clientsTable.Columns.Add(clientId);
DmColumn clientName = new DmColumn <string>("Name");
clientsTable.Columns.Add(clientName);
clientsTable.PrimaryKey = new DmKey(clientId);
// ForeignKey
DmRelation fkClientRelation = new DmRelation("FK_Products_Clients", clientId, fkClientId);
productsTable.AddForeignKey(fkClientRelation);
var clientGuid = Guid.NewGuid();
var drClient = clientsTable.NewRow();
drClient["Name"] = "Pertus";
drClient["Id"] = clientGuid;
clientsTable.Rows.Add(drClient);
var drProduct = productsTable.NewRow();
drProduct["clientId"] = clientGuid;
drProduct["name"] = "Ensemble bleu blanc rouge";
drProduct["price"] = 12.23d;
productsTable.Rows.Add(drProduct);
// Convert to DmSetSurrogate
var surrogateDs = new DmSetSurrogate(set);
// serialize as json string
var stringSurrogate = JsonConvert.SerializeObject(surrogateDs);
// deserialize as surrogate
var surrogateDsFromJson = JsonConvert.DeserializeObject <DmSetSurrogate>(stringSurrogate);
// Convert to DmSet
var set2 = surrogateDs.ConvertToDmSet(set);
// Assertions on DmSet properties
Assert.Equal(set.DmSetName, set2.DmSetName);
Assert.Equal(set.Culture, set2.Culture);
Assert.Equal(set.CaseSensitive, set2.CaseSensitive);
Assert.Equal(set.Relations.Count, set2.Relations.Count);
Assert.Equal(set.Tables.Count, set2.Tables.Count);
//Assertions on Table properties
var productsTable2 = set2.Tables["Products"];
var clientsTable2 = set2.Tables["Clients"];
AssertIsEqual(productsTable, productsTable2);
AssertIsEqual(clientsTable, clientsTable2);
// Assertions on columns
var productId2 = set2.Tables["Products"].Columns["Id"];
AssertIsEqual(productId, productId2);
var fkClientId2 = set2.Tables["Products"].Columns["clientId"];
AssertIsEqual(fkClientId, fkClientId2);
var productName2 = set2.Tables["Products"].Columns["name"];
AssertIsEqual(productName, productName2);
var productPrice2 = set2.Tables["Products"].Columns["price"];
AssertIsEqual(productPrice, productPrice2);
var clientId2 = set2.Tables["Clients"].Columns["Id"];
AssertIsEqual(clientId, clientId2);
var clientName2 = set2.Tables["Clients"].Columns["Name"];
AssertIsEqual(clientName, clientName2);
}
public string CreateForeignKeyConstraintsScriptText(DmRelation constraint)
{
return(string.Empty);
}
public void CreateForeignKeyConstraints(DmRelation constraint)
{
return;
}
public bool NeedToCreateForeignKeyConstraints(DmRelation constraint)
{
return(false);
}
/// <summary>
/// update configuration object with tables desc from server database
/// </summary>
public async Task ReadConfigurationAsync(SyncConfiguration configuration)
{
if (configuration == null || configuration.Count() == 0)
{
throw new SyncException("Configuration should contains Tables, at least tables with a name",
SyncStage.ConfigurationApplying, SyncExceptionType.Argument);
}
DbConnection connection;
DbTransaction transaction;
using (connection = this.CreateConnection())
{
try
{
await connection.OpenAsync();
using (transaction = connection.BeginTransaction())
{
foreach (var dmTable in configuration)
{
var builderTable = this.GetDbManager(dmTable.TableName);
var tblManager = builderTable.GetManagerTable(connection, transaction);
// get columns list
var lstColumns = tblManager.GetTableDefinition();
// Validate the column list and get the dmTable configuration object.
this.ValidateTableFromColumnsList(dmTable, lstColumns, tblManager);
var relations = tblManager.GetTableRelations();
if (relations != null)
{
foreach (var r in relations)
{
DmColumn tblColumn = dmTable.Columns[r.ColumnName];
DmColumn foreignColumn = null;
var foreignTable = configuration[r.ReferenceTableName];
// Since we can have a table with a foreign key but not the parent table
// It's not a problem, just forget it
if (foreignTable == null)
{
continue;
}
foreignColumn = foreignTable.Columns[r.ReferenceColumnName];
if (foreignColumn == null)
{
throw new SyncException($"Foreign column {r.ReferenceColumnName} does not exist in table {r.TableName}", SyncStage.ConfigurationApplying, SyncExceptionType.DataStore);
}
DmRelation dmRelation = new DmRelation(r.ForeignKey, tblColumn, foreignColumn);
configuration.ScopeSet.Relations.Add(dmRelation);
}
}
}
transaction.Commit();
}
connection.Close();
}
catch (Exception ex)
{
Debug.WriteLine($"Error during building BuildConfiguration : {ex.Message}");
throw;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
}
public SqlBuilderTableTest()
{
databaseName = "TESTDB" + DateTime.Now.ToString("yyyyMMddHHmm");
masterConnectionString = String.Format(connectionString, "master");
clientConnectionString = String.Format(connectionString, databaseName);
// Create database
using (SqlConnection connection = new SqlConnection(masterConnectionString))
{
using (var cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = $"if exists(select * from sys.databases where name = '{databaseName}') " +
$"Begin " +
$"ALTER DATABASE {databaseName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE " +
$"Drop Database {databaseName} " +
$"End " +
$"Create Database {databaseName} ";
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
}
// Generate the DmSet schema
set = new DmSet();
DmTable clientsTable = new DmTable("Clients");
DmTable productsTable = new DmTable("Products");
// orders matters !!
set.Tables.Add(clientsTable);
set.Tables.Add(productsTable);
DmColumn id = new DmColumn <Int32>("Id");
id.AllowDBNull = false;
id.AutoIncrement = true;
productsTable.Columns.Add(id);
DmColumn fkClientId = new DmColumn <Guid>("clientId");
fkClientId.AllowDBNull = true;
productsTable.Columns.Add(fkClientId);
DmColumn name = new DmColumn <string>("name");
name.AllowDBNull = false;
name.DbType = System.Data.DbType.StringFixedLength;
name.MaxLength = 150;
productsTable.Columns.Add(name);
DmColumn salary = new DmColumn <Decimal>("salary");
salary.AllowDBNull = false;
salary.DbType = System.Data.DbType.VarNumeric;
salary.Precision = 6;
salary.Scale = 2;
productsTable.Columns.Add(salary);
productsTable.PrimaryKey = new DmKey(new DmColumn[] { id, name, salary });
DmColumn clientId = new DmColumn <Guid>("Id");
clientId.AllowDBNull = false;
clientsTable.Columns.Add(clientId);
DmColumn clientName = new DmColumn <string>("Name");
clientsTable.Columns.Add(clientName);
clientsTable.PrimaryKey = new DmKey(clientId);
// ForeignKey
DmRelation fkClientRelation = new DmRelation("FK_Products_Clients", clientId, fkClientId);
productsTable.AddForeignKey(fkClientRelation);
}
public bool NeedToCreateForeignKeyConstraints(DmRelation constraint, DbBuilderOption builderOption)
{
return(false);
}
private static void TestCreateTrackingTable(SqlSyncProvider provider)
{
DmSet set = new DmSet();
DmTable clientsTable = new DmTable("Clients");
DmTable productsTable = new DmTable("Products");
// orders matters !!
set.Tables.Add(clientsTable);
set.Tables.Add(productsTable);
DmColumn id = new DmColumn <Int32>("Id");
id.AllowDBNull = false;
id.AutoIncrement = true;
productsTable.Columns.Add(id);
DmColumn fkClientId = new DmColumn <Guid>("clientId");
fkClientId.AllowDBNull = true;
productsTable.Columns.Add(fkClientId);
DmColumn name = new DmColumn <string>("name");
name.AllowDBNull = true;
name.DbType = System.Data.DbType.StringFixedLength;
name.MaxLength = 150;
productsTable.Columns.Add(name);
DmColumn salary = new DmColumn <Decimal>("salary");
salary.AllowDBNull = false;
salary.DbType = System.Data.DbType.VarNumeric;
salary.Precision = 6;
salary.Scale = 2;
productsTable.Columns.Add(salary);
productsTable.PrimaryKey = new DmKey(new DmColumn[] { id, name, salary });
DmColumn clientId = new DmColumn <Guid>("Id");
clientId.AllowDBNull = false;
clientsTable.Columns.Add(clientId);
DmColumn clientName = new DmColumn <string>("Name");
clientsTable.Columns.Add(clientName);
clientsTable.PrimaryKey = new DmKey(clientId);
// ForeignKey
DmRelation fkClientRelation = new DmRelation("FK_Products_Clients", clientId, fkClientId);
productsTable.AddForeignKey(fkClientRelation);
DbConnection connection = null;
try
{
using (connection = provider.CreateConnection())
{
foreach (var table in set.Tables)
{
var builder = provider.GetDatabaseBuilder(table, DbBuilderOption.CreateOrUseExistingSchema);
if (table.TableName == "Clients")
{
builder.AddFilterColumn("Id");
}
if (table.TableName == "Products")
{
builder.AddFilterColumn("clientId");
}
builder.Apply(connection);
Console.WriteLine(builder.Script(connection));
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
return;
}
finally
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
/// <summary>
/// update configuration object with tables desc from server database
/// </summary>
private async Task ReadSchemaAsync(DmSet schema)
{
if (schema == null || schema.Tables.Count <= 0)
{
throw new ArgumentNullException("syncConfiguration", "Configuration should contains Tables, at least tables with a name");
}
DbConnection connection = null;
DbTransaction transaction;
try
{
using (connection = this.CreateConnection())
{
await connection.OpenAsync();
using (transaction = connection.BeginTransaction())
{
foreach (var dmTable in schema.Tables)
{
var builderTable = this.GetDbManager(dmTable.TableName);
var tblManager = builderTable.CreateManagerTable(connection, transaction);
tblManager.TableName = dmTable.TableName;
// get columns list
var lstColumns = tblManager.GetTableDefinition();
// Validate the column list and get the dmTable configuration object.
this.ValidateTableFromColumns(dmTable, lstColumns, tblManager);
var relations = tblManager.GetTableRelations();
if (relations != null)
{
foreach (var r in relations)
{
DmColumn tblColumn = dmTable.Columns[r.ColumnName];
DmColumn foreignColumn = null;
var foreignTable = schema.Tables[r.ReferenceTableName];
// Since we can have a table with a foreign key but not the parent table
// It's not a problem, just forget it
if (foreignTable == null)
{
continue;
}
foreignColumn = foreignTable.Columns[r.ReferenceColumnName];
if (foreignColumn == null)
{
throw new NotSupportedException(
$"Foreign column {r.ReferenceColumnName} does not exist in table {r.TableName}");
}
DmRelation dmRelation = new DmRelation(r.ForeignKey, tblColumn, foreignColumn);
schema.Relations.Add(dmRelation);
}
}
}
transaction.Commit();
}
connection.Close();
}
}
catch (Exception ex)
{
throw new SyncException(ex, SyncStage.SchemaApplying, this.ProviderTypeName);
}
finally
{
if (connection != null && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
/// <summary>
/// update configuration object with tables desc from server database
/// </summary>
private async Task ReadSchemaAsync(DmSet schema, DbConnection connection, DbTransaction transaction)
{
if (schema == null || schema.Tables.Count <= 0)
{
throw new ArgumentNullException("syncConfiguration", "Configuration should contains Tables, at least tables with a name");
}
try
{
var relations = new List <DbRelationDefinition>(20);
var syncConfiguration = schema.Tables;
using (connection = this.CreateConnection())
{
await connection.OpenAsync();
using (transaction = connection.BeginTransaction())
{
foreach (var dmTable in syncConfiguration)
{
var builderTable = this.GetDbManager(dmTable.TableName);
var tblManager = builderTable.CreateManagerTable(connection, transaction);
// get columns list
var lstColumns = tblManager.GetTableDefinition();
// Validate the column list and get the dmTable configuration object.
this.ValidateTableFromColumns(dmTable, lstColumns, tblManager);
relations.AddRange(tblManager.GetTableRelations());
}
transaction.Commit();
}
connection.Close();
}
if (relations.Any())
{
foreach (var r in relations)
{
// Get table from the relation where we need to work on
var dmTable = schema.Tables[r.TableName];
// get DmColumn from DmTable, based on the columns from relations
var tblColumns = r.Columns.OrderBy(kc => kc.Order)
.Select(kc => dmTable.Columns[kc.KeyColumnName])
.ToArray();
// then Get the foreign table as well
var foreignTable = syncConfiguration[r.ReferenceTableName];
// Since we can have a table with a foreign key but not the parent table
// It's not a problem, just forget it
if (foreignTable == null || foreignTable.Columns.Count == 0)
{
continue;
}
var foreignColumns = r.Columns.OrderBy(kc => kc.Order)
.Select(fc => foreignTable.Columns[fc.ReferenceColumnName])
.ToArray();
if (foreignColumns == null || foreignColumns.Any(c => c == null))
{
throw new NotSupportedException(
$"Foreign columns {string.Join(",", r.Columns.Select(kc => kc.ReferenceColumnName))} does not exist in table {r.ReferenceTableName}");
}
var dmRelation = new DmRelation(r.ForeignKey, tblColumns, foreignColumns);
schema.Relations.Add(dmRelation);
}
}
}
catch (Exception ex)
{
throw new SyncException(ex, SyncStage.SchemaReading);
}
finally
{
if (connection != null && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}