public static DmTable ColumnsForTable(MySqlConnection connection, MySqlTransaction transaction, string tableName)
{
string commandColumn = "select * from information_schema.COLUMNS where table_schema = schema() and table_name = @tableName";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName, "`", "`");
DmTable dmTable = new DmTable(tableNameParser.ObjectNameNormalized);
using (MySqlCommand sqlCommand = new MySqlCommand(commandColumn, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
internal static DmTable PrimaryKeysForTable(MySqlConnection connection, MySqlTransaction transaction, string tableName)
{
var commandColumn = @"select * from information_schema.COLUMNS where table_schema = schema() and table_name = @tableName and column_key='PRI'";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName, "`", "`");
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (MySqlCommand sqlCommand = new MySqlCommand(commandColumn, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
public void DmTable_Clone_Copy()
{
var tbl = new DmTable("ServiceTickets");
var id = new DmColumn <Guid>("ServiceTicketID");
tbl.Columns.Add(id);
var key = new DmKey(new DmColumn[] { id });
tbl.PrimaryKey = key;
tbl.Columns.Add(new DmColumn <string>("Title"));
tbl.Columns.Add(new DmColumn <string>("Description"));
tbl.Columns.Add(new DmColumn <int>("StatusValue"));
tbl.Columns.Add(new DmColumn <int>("EscalationLevel"));
tbl.Columns.Add(new DmColumn <DateTime>("Opened"));
tbl.Columns.Add(new DmColumn <DateTime>("Closed"));
tbl.Columns.Add(new DmColumn <int>("CustomerID"));
var st = tbl.NewRow();
st["ServiceTicketID"] = Guid.NewGuid();
st["Title"] = "Titre AER";
st["Description"] = "Description 2";
st["EscalationLevel"] = 1;
st["StatusValue"] = 2;
st["Opened"] = DateTime.Now;
st["Closed"] = null;
st["CustomerID"] = 1;
tbl.Rows.Add(st);
var tbl2 = tbl.Clone();
Assert.NotSame(tbl2, tbl);
Assert.Empty(tbl2.Rows);
Assert.Equal(8, tbl2.Columns.Count);
Assert.NotNull(tbl2.PrimaryKey);
var tbl3 = tbl.Copy();
Assert.NotSame(tbl3, tbl2);
Assert.NotSame(tbl3, tbl);
Assert.NotNull(tbl3.PrimaryKey);
Assert.Equal(8, tbl3.Columns.Count);
Assert.NotEmpty(tbl3.Rows);
}
/// <summary>
/// Try to get a source row
/// </summary>
/// <returns></returns>
private DmTable GetRow(DmRow sourceRow)
{
// Get the row in the local repository
DbCommand selectCommand = GetCommand(DbObjectType.SelectRowProcName);
var alreadyOpened = Connection.State == ConnectionState.Open;
// Open Connection
if (!alreadyOpened)
{
Connection.Open();
}
if (Transaction != null)
{
selectCommand.Transaction = Transaction;
}
this.SetColumnParameters(selectCommand, sourceRow);
var dmTableSelected = new DmTable(this.TableDescription.TableName);
try
{
using (var reader = selectCommand.ExecuteReader())
dmTableSelected.Fill(reader);
// set the pkey since we will need them later
var pkeys = new DmColumn[this.TableDescription.PrimaryKey.Columns.Length];
for (int i = 0; i < pkeys.Length; i++)
{
var pkName = this.TableDescription.PrimaryKey.Columns[i].ColumnName;
pkeys[i] = dmTableSelected.Columns.First(dm => this.TableDescription.IsEqual(dm.ColumnName, pkName));
}
dmTableSelected.PrimaryKey = new DmKey(pkeys);
}
catch (Exception ex)
{
Logger.Current.Error("Server Error on Getting a row : " + ex.Message);
throw;
}
return(dmTableSelected);
}
internal static (ParserName tableName, ParserName trackingName) GetParsers(DmTable tableDescription)
{
string tableAndPrefixName = tableDescription.TableName;
var originalTableName = ParserName.Parse(tableDescription);
var pref = tableDescription.TrackingTablesPrefix != null ? tableDescription.TrackingTablesPrefix : "";
var suf = tableDescription.TrackingTablesSuffix != null ? tableDescription.TrackingTablesSuffix : "";
// be sure, at least, we have a suffix if we have empty values.
// othewise, we have the same name for both table and tracking table
if (string.IsNullOrEmpty(pref) && string.IsNullOrEmpty(suf))
{
suf = "_tracking";
}
var trackingTableName = ParserName.Parse($"{pref}{tableAndPrefixName}{suf}", "`");
return(originalTableName, trackingTableName);
}
/// <summary>
/// Adding failed rows when used by a bulk operation
/// </summary>
private void AddSchemaForFailedRowsTable(DmTable applyTable, DmTable failedRows)
{
if (failedRows.Columns.Count == 0)
{
foreach (var rowIdColumn in this.TableDescription.PrimaryKey.Columns)
{
failedRows.Columns.Add(rowIdColumn.ColumnName, rowIdColumn.DataType);
}
DmColumn[] keys = new DmColumn[this.TableDescription.PrimaryKey.Columns.Length];
for (int i = 0; i < this.TableDescription.PrimaryKey.Columns.Length; i++)
{
keys[i] = failedRows.Columns[i];
}
failedRows.PrimaryKey = new DmKey(keys);
}
}
public void DmColumn_Create_AutoIncorement_Column()
{
var tbl = new DmTable("ServiceTickets");
var id = new DmColumn <int>("ServiceTicketID");
id.IsAutoIncrement = true;
id.AutoIncrementSeed = 1;
id.AutoIncrementStep = 1;
tbl.Columns.Add(id);
var key = new DmKey(new DmColumn[] { id });
tbl.PrimaryKey = key;
output.WriteLine("AutoIncrement is correctly initialized");
}
/// <summary>
/// Adding filters to an existing configuration
/// </summary>
private void AddFilters(ICollection <FilterClause> filters, DmTable dmTable, DbBuilder builder)
{
if (filters != null && filters.Count > 0)
{
var tableFilters = filters.Where(f => dmTable.TableName.Equals(f.TableName, StringComparison.InvariantCultureIgnoreCase));
foreach (var filter in tableFilters)
{
var columnFilter = dmTable.Columns[filter.ColumnName];
if (columnFilter == null)
{
throw new InvalidExpressionException($"Column {filter.ColumnName} does not exist in Table {dmTable.TableName}");
}
builder.FilterColumns.Add(new FilterClause(filter.TableName, filter.ColumnName));
}
}
}
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);
}
}
}
/// <summary>
/// Get columns for table
/// </summary>
public static DmTable ColumnsForTable(OracleConnection connection, OracleTransaction transaction, string tableName)
{
var commandColumn = @"SELECT column_name AS name, column_id, data_type, data_length, data_precision, data_scale, decode(nullable, 'N', 0, 1) AS is_nullable
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = :tableName";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName);
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (OracleCommand oracleCommand = new OracleCommand(commandColumn, connection, transaction))
{
oracleCommand.Parameters.Add("tableName", tableNameParser.ObjectName);
using (var reader = oracleCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
public void DmTable_CaseSensitive()
{
var set0 = new DmSet("CaseSensitive");
set0.CaseSensitive = true;
var tbl0 = new DmTable("CASESENSITIVE");
set0.Tables.Add(tbl0);
// No error throws, it's ok
Assert.NotEqual(set0.DmSetName, tbl0.TableName);
Assert.Throws <ArgumentException>(() =>
{
var set1 = new DmSet("CaseSensitive");
set1.CaseSensitive = false;
var tbl1 = new DmTable("CASESENSITIVE");
set1.Tables.Add(tbl1);
});
}
public void DmTable_ImportRow()
{
var tbl2 = new DmTable();
var id = new DmColumn <Guid>("ServiceTicketID");
tbl2.Columns.Add(id);
var key = new DmKey(new DmColumn[] { id });
tbl2.PrimaryKey = key;
tbl2.Columns.Add(new DmColumn <string>("Title"));
tbl2.Columns.Add(new DmColumn <string>("Description"));
tbl2.Columns.Add(new DmColumn <int>("StatusValue"));
tbl2.Columns.Add(new DmColumn <int>("EscalationLevel"));
tbl2.Columns.Add(new DmColumn <DateTime>("Opened"));
tbl2.Columns.Add(new DmColumn <DateTime>("Closed"));
tbl2.Columns.Add(new DmColumn <int>("CustomerID"));
var st = tbl2.NewRow();
st["ServiceTicketID"] = Guid.NewGuid();
st["Title"] = "Titre AER";
st["Description"] = "Description 2";
st["EscalationLevel"] = 1;
st["StatusValue"] = 2;
st["Opened"] = DateTime.Now;
st["Closed"] = null;
st["CustomerID"] = 1;
tbl2.Rows.Add(st);
// Importing into tbl
var st2 = this.tbl.ImportRow(st);
// acceptchanges to change the row state
this.tbl.AcceptChanges();
Assert.NotEqual(st.RowState, st2.RowState);
// making change to be sure
st2["CustomerID"] = 2;
Assert.Equal(2, st2["CustomerID"]);
Assert.Equal(1, st["CustomerID"]);
}
/// <summary>
/// Copies the table schema from a DmTableSurrogate object into a DmTable object.
/// </summary>
public void ReadSchemaIntoDmTable(DmTable dt)
{
if (dt == null)
{
throw new ArgumentNullException("dt", "DmTable");
}
dt.TableName = this.TableName;
dt.Culture = new CultureInfo(this.CultureInfoName);
dt.Schema = this.Schema;
dt.CaseSensitive = this.CaseSensitive;
dt.OriginalProvider = this.OriginalProvider;
dt.SyncDirection = this.SyncDirection;
dt.TrackingTablesPrefix = this.TrackingTablesPrefix;
dt.TrackingTablesSuffix = this.TrackingTablesSuffix;
dt.StoredProceduresPrefix = this.StoredProceduresPrefix;
dt.StoredProceduresSuffix = this.StoredProceduresSuffix;
dt.TriggersPrefix = this.TriggersPrefix;
dt.TriggersSuffix = this.TriggersSuffix;
for (int i = 0; i < this.Columns.Count; i++)
{
DmColumn dmColumn = this.Columns[i].ConvertToDmColumn();
dt.Columns.Add(dmColumn);
}
if (this.PrimaryKeys != null && this.PrimaryKeys.Count > 0)
{
DmColumn[] keyColumns = new DmColumn[this.PrimaryKeys.Count];
for (int i = 0; i < this.PrimaryKeys.Count; i++)
{
string columnName = this.PrimaryKeys[i];
keyColumns[i] = dt.Columns.First(c => dt.IsEqual(c.ColumnName, columnName));
}
DmKey key = new DmKey(keyColumns);
dt.PrimaryKey = key;
}
}
private static DmTable CreateParametersTable()
{
DmTable dt = new DmTable("Procedure Parameters");
dt.Columns.Add("SPECIFIC_CATALOG", typeof(string));
dt.Columns.Add("SPECIFIC_SCHEMA", typeof(string));
dt.Columns.Add("SPECIFIC_NAME", typeof(string));
dt.Columns.Add("ORDINAL_POSITION", typeof(Int32));
dt.Columns.Add("PARAMETER_MODE", typeof(string));
dt.Columns.Add("PARAMETER_NAME", typeof(string));
dt.Columns.Add("DATA_TYPE", typeof(string));
dt.Columns.Add("CHARACTER_MAXIMUM_LENGTH", typeof(Int32));
dt.Columns.Add("CHARACTER_OCTET_LENGTH", typeof(Int32));
dt.Columns.Add("NUMERIC_PRECISION", typeof(byte));
dt.Columns.Add("NUMERIC_SCALE", typeof(Int32));
dt.Columns.Add("CHARACTER_SET_NAME", typeof(string));
dt.Columns.Add("COLLATION_NAME", typeof(string));
dt.Columns.Add("DTD_IDENTIFIER", typeof(string));
dt.Columns.Add("ROUTINE_TYPE", typeof(string));
return(dt);
}
/// <summary>
/// Get columns for table
/// </summary>
internal static DmTable ColumnsForTable(SqlConnection connection, SqlTransaction transaction, string tableName)
{
var commandColumn = $"Select col.name as name, col.column_id, typ.name as [type], col.max_length, col.precision, col.scale, col.is_nullable, col.is_identity from sys.columns as col " +
$"Inner join sys.tables as tbl on tbl.object_id = col.object_id " +
$"Inner Join sys.systypes typ on typ.xusertype = col.system_type_id " +
$"Where tbl.name = @tableName";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName);
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (SqlCommand sqlCommand = new SqlCommand(commandColumn, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
internal static DmTable PrimaryKeysForTable(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName)
{
var commandColumn = @"SELECT c.column_name, c.ordinal_position, *
FROM information_schema.key_column_usage AS c
LEFT JOIN information_schema.table_constraints AS t
ON t.constraint_name = c.constraint_name
WHERE t.table_schema = current_schema() AND t.table_name = @tableName AND t.constraint_type = 'PRIMARY KEY';";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName, "\"", "\"");
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (var sqlCommand = new NpgsqlCommand(commandColumn, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
public void DmRow_Add_Rows()
{
var tbl = new DmTable("ServiceTickets");
var idColumn = new DmColumn <Guid>("ServiceTicketID");
tbl.Columns.Add(idColumn);
var key = new DmKey(new DmColumn[] { idColumn });
tbl.PrimaryKey = key;
var titleColumn = new DmColumn <string>("Title");
tbl.Columns.Add(titleColumn);
var statusValueColumn = new DmColumn <int>("StatusValue");
tbl.Columns.Add(statusValueColumn);
var openedColumn = new DmColumn <DateTime>("Opened");
tbl.Columns.Add(openedColumn);
var st = tbl.NewRow();
Assert.Null(st[0]);
Assert.Null(st[1]);
var id = Guid.NewGuid();
var dateNow = DateTime.Now;
st["ServiceTicketID"] = id;
st["Title"] = "Titre AER";
st["StatusValue"] = 2;
st["Opened"] = dateNow;
tbl.Rows.Add(st);
Assert.Equal(id, st["ServiceTicketID"]);
Assert.Equal("Titre AER", st["Title"]);
Assert.Equal(2, st["StatusValue"]);
Assert.Equal(dateNow, st["Opened"]);
}
/// <summary>
/// Get columns for table
/// </summary>
internal static DmTable PrimaryKeysForTable(OracleConnection connection, OracleTransaction transaction, string tableName)
{
var commandColumn = @"SELECT ac.constraint_name as name, column_name as columnName, position as column_id
FROM all_cons_columns acc
INNER JOIN all_constraints ac
ON acc.constraint_name = ac.constraint_name AND acc.table_name = ac.table_name
WHERE CONSTRAINT_TYPE IN ('P') AND ac.table_name = :tableName";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName);
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (OracleCommand oracleCommand = new OracleCommand(commandColumn, connection, transaction))
{
oracleCommand.Parameters.Add("tableName", tableNameParser.ObjectName);
using (var reader = oracleCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
/// <summary>
/// Check if the database is already created.
/// If so, we won't do any check on the structure.
/// Edit this value after EnsureScopes to force checking.
/// </summary>
//public Boolean IsDatabaseCreated { get; set; }
/// <summary>
/// Generate a DmTable based on a SyncContext object
/// </summary>
public static void SerializeInDmSet(DmSet set, IEnumerable <ScopeInfo> scopesInfo)
{
if (set == null)
{
return;
}
DmTable dmTable = null;
if (!set.Tables.Contains("DotmimSync__ScopeInfo"))
{
dmTable = new DmTable("DotmimSync__ScopeInfo");
set.Tables.Add(dmTable);
}
dmTable = set.Tables["DotmimSync__ScopeInfo"];
dmTable.Columns.Add <Guid>("Id");
dmTable.Columns.Add <Boolean>("IsDatabaseCreated");
dmTable.Columns.Add <Boolean>("IsLocal");
dmTable.Columns.Add <Boolean>("IsNewScope");
dmTable.Columns.Add <Int64>("LastTimestamp");
dmTable.Columns.Add <String>("Name");
foreach (var scopeInfo in scopesInfo)
{
DmRow dmRow = dmTable.NewRow();
dmRow["Id"] = scopeInfo.Id;
//dmRow["IsDatabaseCreated"] = scopeInfo.IsDatabaseCreated;
dmRow["IsLocal"] = scopeInfo.IsLocal;
dmRow["IsNewScope"] = scopeInfo.IsNewScope;
dmRow["LastTimestamp"] = scopeInfo.LastTimestamp;
dmRow["Name"] = scopeInfo.Name;
dmTable.Rows.Add(dmRow);
}
}
internal static DmTable RelationsForTable(MySqlConnection connection, MySqlTransaction transaction, string tableName)
{
var commandRelations = @"
SELECT
ke.CONSTRAINT_NAME as ForeignKey,
ke.POSITION_IN_UNIQUE_CONSTRAINT as ForeignKeyOrder,
ke.referenced_table_name as TableName,
ke.REFERENCED_COLUMN_NAME as ColumnName,
ke.table_name ReferenceTableName,
ke.COLUMN_NAME ReferenceColumnName
FROM
information_schema.KEY_COLUMN_USAGE ke
WHERE
ke.referenced_table_name IS NOT NULL
and ke.REFERENCED_TABLE_SCHEMA = schema()
AND ke.REFERENCED_TABLE_NAME = @tableName
ORDER BY
ke.referenced_table_name;";
var tableNameParser = ParserName.Parse(tableName, "`");
var dmTable = new DmTable(tableNameParser.Unquoted().ToString());
using (MySqlCommand sqlCommand = new MySqlCommand(commandRelations, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.Unquoted().ToString());
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
private string DeleteTriggerBodyText(DmTable TableDescription)
{
(var tableName, var trackingName) = SqlBuilder.GetParsers(TableDescription);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("UPDATE [side] ");
stringBuilder.AppendLine("SET \t[sync_row_is_tombstone] = 1");
stringBuilder.AppendLine("\t,[update_scope_id] = NULL -- since the update if from local, it's a NULL");
stringBuilder.AppendLine("\t,[update_timestamp] = @@DBTS+1");
stringBuilder.AppendLine("\t,[last_change_datetime] = GetDate()");
// Filter columns
if (this.FilterColumns != null)
{
for (int i = 0; i < this.FilterColumns.Count; i++)
{
var filterColumn = this.FilterColumns[i];
if (TableDescription.PrimaryKey.Columns.Any(c => c.ColumnName == filterColumn.ColumnName))
{
continue;
}
ObjectNameParser columnName = new ObjectNameParser(filterColumn.ColumnName);
stringBuilder.AppendLine($"\t,{columnName.QuotedString} = [d].{columnName.QuotedString}");
}
stringBuilder.AppendLine();
}
stringBuilder.AppendLine($"FROM {trackingName.QuotedString} [side]");
stringBuilder.Append($"JOIN DELETED AS [d] ON ");
stringBuilder.AppendLine(SqlManagementUtils.JoinTwoTablesOnClause(TableDescription.PrimaryKey.Columns, "[side]", "[d]"));
return(stringBuilder.ToString());
}
/// <summary>
/// Get columns for table
/// </summary>
internal static DmTable PrimaryKeysForTable(SqlConnection connection, SqlTransaction transaction, string tableName)
{
var commandColumn = @"select ind.name, col.name as columnName, ind_col.column_id
from sys.indexes ind
left outer join sys.index_columns ind_col on ind_col.object_id = ind.object_id and ind_col.index_id = ind.index_id
inner join sys.columns col on col.object_id = ind_col.object_id and col.column_id = ind_col.column_id
inner join sys.tables tbl on tbl.object_id = ind.object_id
where tbl.name = @tableName and ind.index_id >= 0 and ind.type <> 3 and ind.type <> 4 and ind.is_hypothetical = 0 and ind.is_primary_key = 1
order by ind.index_id, ind_col.key_ordinal";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName);
DmTable dmTable = new DmTable(tableNameParser.ObjectNameNormalized);
using (SqlCommand sqlCommand = new SqlCommand(commandColumn, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
public DmRelationTests()
{
_set = new DmSet();
clientTable = new DmTable("Client");
clientTypeTable = new DmTable("TypeClient");
_set.Tables.Add(clientTable);
_set.Tables.Add(clientTypeTable);
DmColumn Col0 = new DmColumn <int>("ClientId");
DmColumn Col1 = new DmColumn <int>("ClientType");
DmColumn Col2 = new DmColumn <String>("ClientName");
clientTable.Columns.Add(Col0);
clientTable.Columns.Add(Col1);
clientTable.Columns.Add(Col2);
clientTable.PrimaryKey = new DmKey(Col0);
DmColumn Col3 = new DmColumn <int>("TypeId");
DmColumn Col4 = new DmColumn <string>("TypeName");
clientTypeTable.Columns.Add(Col3);
clientTypeTable.Columns.Add(Col4);
clientTypeTable.PrimaryKey = new DmKey(Col3);
}
/// <summary>
/// Serialize the BatchPartInfo WITHOUT the DmSet
/// </summary>
internal static void SerializeInDmSet(DmSet set, BatchPartInfo bpi)
{
if (set == null)
{
return;
}
DmTable dmTableBatchPartsInfo = null;
if (!set.Tables.Contains("DotmimSync__BatchPartsInfo"))
{
dmTableBatchPartsInfo = new DmTable("DotmimSync__BatchPartsInfo");
set.Tables.Add(dmTableBatchPartsInfo);
}
dmTableBatchPartsInfo = set.Tables["DotmimSync__BatchPartsInfo"];
dmTableBatchPartsInfo.Columns.Add <String>("FileName");
dmTableBatchPartsInfo.Columns.Add <int>("Index");
dmTableBatchPartsInfo.Columns.Add <Boolean>("IsLastBatch");
dmTableBatchPartsInfo.Columns.Add <String>("Tables");
var dmRow = dmTableBatchPartsInfo.NewRow();
dmRow["FileName"] = bpi.FileName;
dmRow["Index"] = bpi.Index;
dmRow["IsLastBatch"] = bpi.IsLastBatch;
if (bpi.Tables != null && bpi.Tables.Length > 0)
{
var tablesString = String.Join("|", bpi.Tables);
dmRow["Tables"] = tablesString;
}
dmTableBatchPartsInfo.Rows.Add(dmRow);
}
public override DbBuilder GetDatabaseBuilder(DmTable tableDescription) => new SqlBuilder(tableDescription);
internal static (ObjectNameParser tableName, ObjectNameParser trackingName) GetParsers(DmTable tableDescription)
{
string tableAndPrefixName = String.IsNullOrWhiteSpace(tableDescription.Schema) ? tableDescription.TableName : $"{tableDescription.Schema}.{tableDescription.TableName}";
var originalTableName = new ObjectNameParser(tableAndPrefixName, "[", "]");
var trackingTableName = new ObjectNameParser($"{tableAndPrefixName}_tracking", "[", "]");
return(originalTableName, trackingTableName);
}
/// <summary> /// Execute a batch command /// </summary> public abstract void ExecuteBatchCommand(DbCommand cmd, DmTable applyTable, DmTable failedRows, ScopeInfo scope);
/// <summary>
/// Launch apply bulk changes
/// </summary>
/// <returns></returns>
public int ApplyBulkChanges(DmView dmChanges, ScopeInfo fromScope, List <SyncConflict> conflicts)
{
DbCommand bulkCommand = null;
if (this.applyType == DmRowState.Added)
{
bulkCommand = this.GetCommand(DbObjectType.BulkInsertProcName);
}
else if (this.applyType == DmRowState.Modified)
{
bulkCommand = this.GetCommand(DbObjectType.BulkUpdateProcName);
}
else if (this.applyType == DmRowState.Deleted)
{
bulkCommand = this.GetCommand(DbObjectType.BulkDeleteProcName);
}
else
{
throw new Exception("DmRowState not valid during ApplyBulkChanges operation");
}
if (Transaction != null && Transaction.Connection != null)
{
bulkCommand.Transaction = Transaction;
}
DmTable batchDmTable = dmChanges.Table.Clone();
DmTable failedDmtable = new DmTable {
Culture = CultureInfo.InvariantCulture
};
// Create the schema for failed rows (just add the Primary keys)
this.AddSchemaForFailedRowsTable(batchDmTable, failedDmtable);
int batchCount = 0;
int rowCount = 0;
foreach (var dmRow in dmChanges)
{
// Cancel the delete state to be able to get the row, more simplier
if (applyType == DmRowState.Deleted)
{
dmRow.RejectChanges();
}
// Load the datarow
DmRow dataRow = batchDmTable.LoadDataRow(dmRow.ItemArray, false);
// Apply the delete
// is it mandatory ?
if (applyType == DmRowState.Deleted)
{
dmRow.Delete();
}
batchCount++;
rowCount++;
if (batchCount != 500 && rowCount != dmChanges.Count)
{
continue;
}
// Since the update and create timestamp come from remote, change name for the bulk operations
batchDmTable.Columns["update_timestamp"].ColumnName = "update_timestamp";
batchDmTable.Columns["create_timestamp"].ColumnName = "create_timestamp";
// execute the batch, through the provider
ExecuteBatchCommand(bulkCommand, batchDmTable, failedDmtable, fromScope);
// Clear the batch
batchDmTable.Clear();
// Recreate a Clone
// TODO : Evaluate if it's necessary
batchDmTable = dmChanges.Table.Clone();
batchCount = 0;
}
// Update table progress
//tableProgress.ChangesApplied = dmChanges.Count - failedDmtable.Rows.Count;
if (failedDmtable.Rows.Count == 0)
{
return(dmChanges.Count);
}
// Check all conflicts raised
var failedFilter = new Predicate <DmRow>(row =>
{
if (row.RowState == DmRowState.Deleted)
{
return(failedDmtable.FindByKey(row.GetKeyValues(DmRowVersion.Original)) != null);
}
else
{
return(failedDmtable.FindByKey(row.GetKeyValues()) != null);
}
});
// New View
var dmFailedRows = new DmView(dmChanges, failedFilter);
// Generate a conflict and add it
foreach (var dmFailedRow in dmFailedRows)
{
conflicts.Add(GetConflict(dmFailedRow));
}
int failedRows = dmFailedRows.Count;
// Dispose the failed view
dmFailedRows.Dispose();
// return applied rows - failed rows (generating a conflict)
return(dmChanges.Count - failedRows);
}
/// <summary>
/// Apply changes internal method for one Insert or Update or Delete for every dbSyncAdapter
/// </summary>
internal async Task <ChangeApplicationAction> ApplyChangesInternalAsync(
DmTable table,
SyncContext context,
MessageApplyChanges message,
DbConnection connection,
DbTransaction transaction,
DmRowState applyType,
DatabaseChangesApplied changesApplied)
{
var changeApplicationAction = ChangeApplicationAction.Continue;
// if we are in upload stage, so check if table is not download only
if (context.SyncWay == SyncWay.Upload && table.SyncDirection == SyncDirection.DownloadOnly)
{
return(ChangeApplicationAction.Continue);
}
// if we are in download stage, so check if table is not download only
if (context.SyncWay == SyncWay.Download && table.SyncDirection == SyncDirection.UploadOnly)
{
return(ChangeApplicationAction.Continue);
}
var builder = this.GetDatabaseBuilder(table);
var syncAdapter = builder.CreateSyncAdapter(connection, transaction);
syncAdapter.ApplyType = applyType;
if (message.Changes.BatchPartsInfo != null && message.Changes.BatchPartsInfo.Count > 0)
{
// getting the table to be applied
// we may have multiple batch files, so we can have multipe dmTable with the same Name
// We can say that dmTable may be contained in several files
foreach (var dmTablePart in message.Changes.GetTable(table.TableName))
{
if (dmTablePart == null || dmTablePart.Rows.Count == 0)
{
continue;
}
// check and filter
var dmChangesView = new DmView(dmTablePart, (r) => r.RowState == applyType);
if (dmChangesView.Count == 0)
{
dmChangesView.Dispose();
dmChangesView = null;
continue;
}
// Conflicts occured when trying to apply rows
var conflicts = new List <SyncConflict>();
context.SyncStage = SyncStage.TableChangesApplying;
// Launch any interceptor if available
await this.InterceptAsync(new TableChangesApplyingArgs(context, table, applyType, connection, transaction));
int rowsApplied;
// applying the bulkchanges command
if (this.Options.UseBulkOperations && this.SupportBulkOperations)
{
rowsApplied = syncAdapter.ApplyBulkChanges(dmChangesView, message.FromScope, conflicts);
}
else
{
rowsApplied = syncAdapter.ApplyChanges(dmChangesView, message.FromScope, conflicts);
}
// If conflicts occured
// Eventuall, conflicts are resolved on server side.
if (conflicts != null && conflicts.Count > 0)
{
foreach (var conflict in conflicts)
{
//var scopeBuilder = this.GetScopeBuilder();
//var scopeInfoBuilder = scopeBuilder.CreateScopeInfoBuilder(message.ScopeInfoTableName, connection, transaction);
//var localTimeStamp = scopeInfoBuilder.GetLocalTimestamp();
var fromScopeLocalTimeStamp = message.FromScope.Timestamp;
var conflictCount = 0;
DmRow resolvedRow = null;
(changeApplicationAction, conflictCount, resolvedRow) =
await this.HandleConflictAsync(syncAdapter, context, conflict, message.Policy, message.FromScope, fromScopeLocalTimeStamp, connection, transaction);
if (changeApplicationAction == ChangeApplicationAction.Continue)
{
// row resolved
if (resolvedRow != null)
{
context.TotalSyncConflicts += conflictCount;
rowsApplied++;
}
}
else
{
context.TotalSyncErrors++;
// TODO : Should we break at the first error ?
return(ChangeApplicationAction.Rollback);
}
}
}
// Handle sync progress for this syncadapter (so this table)
var changedFailed = dmChangesView.Count - rowsApplied;
// raise SyncProgress Event
var existAppliedChanges = changesApplied.TableChangesApplied.FirstOrDefault(
sc => string.Equals(sc.Table.TableName, table.TableName) && sc.State == applyType);
if (existAppliedChanges == null)
{
existAppliedChanges = new TableChangesApplied
{
Table = new DmTableSurrogate(table),
Applied = rowsApplied,
Failed = changedFailed,
State = applyType
};
changesApplied.TableChangesApplied.Add(existAppliedChanges);
}
else
{
existAppliedChanges.Applied += rowsApplied;
existAppliedChanges.Failed += changedFailed;
}
// Progress & Interceptor
context.SyncStage = SyncStage.TableChangesApplied;
var tableChangesAppliedArgs = new TableChangesAppliedArgs(context, existAppliedChanges, connection, transaction);
this.ReportProgress(context, tableChangesAppliedArgs, connection, transaction);
await this.InterceptAsync(tableChangesAppliedArgs);
}
}
return(ChangeApplicationAction.Continue);
}
private static void ParseProcedureBody(DmTable parametersTable, string body, string sqlMode, string schema, string procName)
{
List <string> modes = new List <string>(new string[3] {
"IN", "OUT", "INOUT"
});
int pos = 1;
MySqlTokenizer tokenizer = new MySqlTokenizer(body)
{
AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1,
BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") == -1,
ReturnComments = false
};
string token = tokenizer.NextToken();
// this block will scan for the opening paren while also determining
// if this routine is a function. If so, then we need to add a
// parameter row for the return parameter since it is ordinal position
// 0 and should appear first.
while (token != "(")
{
if (String.Compare(token, "FUNCTION", StringComparison.OrdinalIgnoreCase) == 0)
{
var newRow = parametersTable.NewRow();
InitParameterRow(newRow, schema, procName);
parametersTable.Rows.Add(newRow);
}
token = tokenizer.NextToken();
}
token = tokenizer.NextToken(); // now move to the next token past the (
while (token != ")")
{
DmRow parmRow = parametersTable.NewRow();
InitParameterRow(parmRow, schema, procName);
parmRow["ORDINAL_POSITION"] = pos++;
// handle mode and name for the parameter
string mode = token.ToUpperInvariant();
if (!tokenizer.Quoted && modes.Contains(mode))
{
parmRow["PARAMETER_MODE"] = mode;
token = tokenizer.NextToken();
}
if (tokenizer.Quoted)
{
token = token.Substring(1, token.Length - 2);
}
parmRow["PARAMETER_NAME"] = token;
// now parse data type
token = ParseDataType(parmRow, tokenizer);
if (token == ",")
{
token = tokenizer.NextToken();
}
parametersTable.Rows.Add(parmRow);
}
// now parse out the return parameter if there is one.
token = tokenizer.NextToken().ToUpperInvariant();
if (String.Compare(token, "RETURNS", StringComparison.OrdinalIgnoreCase) == 0)
{
DmRow parameterRow = parametersTable.Rows[0];
parameterRow["PARAMETER_NAME"] = "RETURN_VALUE";
ParseDataType(parameterRow, tokenizer);
}
}
