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); } }