private string GetParamName(TableColumnMappings colMappings, string publicColName) { for (int n = 0; n < colMappings.Count; ++n) { if (colMappings[n].PublicColumn.Equals(publicColName, StringComparison.OrdinalIgnoreCase)) { return(GetParamName(n)); } } if (colMappings.EventExpansionRequired) { if (colMappings.EventExpansion.PublicEventInstanceColumn.Equals(publicColName, StringComparison.OrdinalIgnoreCase)) { return(GetParamName(EventInstanceParamNum)); } if (colMappings.EventExpansion.PublicWeekColumn.Equals(publicColName, StringComparison.OrdinalIgnoreCase)) { return(GetParamName(EventWeekParamNum)); } if (colMappings.EventExpansion.PublicWeekOccurrenceColumn.Equals(publicColName, StringComparison.OrdinalIgnoreCase)) { return(GetParamName(EventWeekOccurrenceParamNum)); } } throw new ApplicationException($"Could not find column name in mapping: {publicColName}"); }
private string GetParamNamesAsCsv(TableColumnMappings colMappings, string prefix = null) { var sb = new StringBuilder(); for (int n = 0; n < colMappings.Count; ++n) { if (n > 0) { sb.Append(","); } sb.Append(GetParamName(n, prefix)); } if (colMappings.EventExpansionRequired) { if (sb.Length > 0) { sb.Append(","); } sb.Append(GetParamName(EventInstanceParamNum, prefix)); sb.Append(","); sb.Append(GetParamName(EventWeekParamNum, prefix)); } return(sb.ToString()); }
public virtual void Delete( string sqlConnectionString, int timeoutSecs, Row stagingRow, TableColumnMappings colMappings, FixupCaches caches, DataStoreConfiguration configuration) { var b = new SqlBuilder(); b.AppendFormat("delete from {0}", QualifiedName); b.Append("where"); for (int n = 0; n < PrimaryKey.KeyPartsCount; ++n) { if (n > 0) { b.Append("and"); } var keyPart = PrimaryKey[n]; b.AppendFormat("{0}={1}", keyPart.ColumnName, GetParamName(colMappings, keyPart.ColumnName)); } var parameters = new List <SqlParameter>(); for (int n = 0; n < colMappings.Count; ++n) { string paramName = GetParamName(n); colMappings[n].AddParamValue(parameters, paramName, stagingRow, caches, configuration); } DatabaseUtils.ExecuteSql(sqlConnectionString, b.ToString(), timeoutSecs, parameters.ToArray()); }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings { EventExpansion = EventExpansionDefinition.Standard }; m.AddSimpleMapping("timetable_id", "src_timetable_id"); m.AddSimpleMapping("timetable_event_id", "event_id"); // must come before "event_id" mapping m.AddFederatedIdMapping("event_id"); m.AddEventStartEndTimeMapping("start_time"); m.AddEventStartEndTimeMapping("end_time"); m.AddSpanIdAndNameMapping(); m.AddEventCatIdAndNameMapping(c); m.AddDeptIdAndNameMapping(c); m.AddFacultyIdAndNameMapping(c); m.AddBooleanMapping("global_event"); m.AddBooleanMapping("protected"); m.AddBooleanMapping("suspended"); m.AddBooleanMapping("registers_req"); m.AddBooleanMapping(ColumnConstants.RegistersReqResolvedColumnName); m.AddAuditMapping(c); m.AddOriginMapping(); m.AddRemainingSimpleMappings(Columns); return(m); }
private void PopulateTempUpsertTableUsingBulkCopy( SqlConnection c, int timeoutSecs, IEnumerable <Row> stagingRows, TableColumnMappings colMappings, FixupCaches caches, DataStoreConfiguration configuration, Table tableName, bool modifyWeekNumbers) { var etl = new TempUpsertEtlProcess(c, timeoutSecs, stagingRows, colMappings, caches, configuration, tableName, modifyWeekNumbers); etl.Execute(); var errors = etl.GetAllErrors().ToArray(); if (errors.Any()) { string msg = $"Errors occurred during population of temporary upsert table: {tableName.Name}"; _log.Error(msg); // throw the first exception throw new ApplicationException(msg, errors[0]); } }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddFederatedIdMapping("exam_id"); m.AddExplicitColumnMappingLookup("federated_exam_id", "exam_id", "name", Entity.EsExam); m.AddExplicitColumnMappingLookup("federated_exam_id", "exam_id", "unique_name", Entity.EsExam); m.AddFederatedIdMapping("session_id"); m.AddColumnMappingLookup("session_id", "session_name", Entity.EsSession, c); m.AddEventCatIdAndNameMapping(c); m.AddDeptIdAndNameMapping(c); m.AddFacultyIdAndNameMapping(c); m.AddBooleanMapping("protected"); m.AddBooleanMapping("suspended"); m.AddBooleanMapping("registers_req"); m.AddBooleanMapping(ColumnConstants.RegistersReqResolvedColumnName); m.AddAuditMapping(c); m.AddOriginMapping(); m.AddRemainingSimpleMappings(Columns); return(m); }
public TransformationEventExpansionOperation(PublicTable targetTable, FixupCaches caches, DataStoreConfiguration configuration) { _colMappings = targetTable.GetColumnMappingsFromStage(configuration); _expandEvents = _colMappings != null && _colMappings.EventExpansionRequired; _caches = caches; _configuration = configuration; }
private IEnumerable <Row> ExecuteDelete(IEnumerable <Row> rows, TableColumnMappings colMappings) { foreach (var r in rows) { _targetTable.Delete(_connectionString, _timeoutSecs, r, colMappings, _caches, _configuration); yield return(r); } }
public override void Delete( string sqlConnectionString, int timeoutSecs, Row stagingRow, TableColumnMappings colMappings, FixupCaches caches, DataStoreConfiguration configuration) { DeleteEventAssignment(sqlConnectionString, timeoutSecs, stagingRow); }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddFederatedIdMapping("session_id"); m.AddRemainingSimpleMappings(Columns); return(m); }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddFederatedIdMapping("event_id"); m.AddStaffCatIdAndNameMapping(c); return(m); }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddGroupIdAndNameMapping(c); m.AddSubGroupIdAndNameMapping(c); return(m); }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddFederatedIdMapping("booking_id"); m.AddResourceIdAndNameMapping(); m.AddBooleanMapping("send_email"); return(m); }
public void Upsert( string sqlConnectionString, int timeoutSecs, IEnumerable <Row> stagingRows, TableColumnMappings colMappings, FixupCaches caches, DataStoreConfiguration configuration, bool modifyWeekNumbers = false) { using (var c = DatabaseUtils.CreateConnection(sqlConnectionString)) { var tmpTableName = GenerateTempUpsertTable(c, timeoutSecs, stagingRows, colMappings, caches, configuration, modifyWeekNumbers); var b = new SqlBuilder(); b.AppendFormat("MERGE {0} target", QualifiedName); b.AppendFormat("using (select {0} from {1}) source", colMappings.GetPublicColNamesAsCsv(), tmpTableName); b.Append("on ("); for (int n = 0; n < PrimaryKey.KeyPartsCount; ++n) { if (n > 0) { b.Append("and"); } var keyPart = PrimaryKey[n]; b.AppendFormat("target.{0} = source.{0}", keyPart.ColumnName); } b.Append(")"); b.Append("when MATCHED then"); b.Append("UPDATE SET"); for (int n = 0; n < Columns.Count; ++n) { if (n > 0) { b.AppendNoSpace(", "); } var col = Columns[n]; b.AppendFormat("target.{0} = source.{0}", col.Name); } b.Append("when NOT MATCHED then"); b.AppendFormat("INSERT ({0}) values({1})", colMappings.GetPublicColNamesAsCsv(), GetSourceColsNamesAsCsv(colMappings)); b.Append(";"); DatabaseUtils.ExecuteSql(c, null, b.ToString(), timeoutSecs); } }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddSimpleMapping("timetable_id", "src_timetable_id"); m.AddBooleanMapping("registers_req"); m.AddRemainingSimpleMappings(Columns); return(m); }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddModuleIdAndNameMapping(c); m.AddResourceIdAndNameMapping(); m.AddRemainingSimpleMappings(Columns); return(m); }
public TempUpsertGetRowsOperation( IEnumerable <Row> stagingRows, TableColumnMappings colMappings, FixupCaches caches, DataStoreConfiguration configuration, bool modifyWeekNumbers) { _stagingRows = stagingRows; _colMappings = colMappings; _caches = caches; _configuration = configuration; _modifyWeekNumbers = modifyWeekNumbers; }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddFederatedIdMapping("activity_id"); m.AddStudentIdAndNameMapping(c); m.AddResourceIdAndNameMapping(); m.AddAuditMapping(c); return(m); }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddConsolidatedOrFederatedIdMapping(c, Entity.StaffCat, "staff_cat_id"); m.AddAuditMapping(c); m.AddOriginMapping(); m.AddRemainingSimpleMappings(Columns); return(m); }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings { EventExpansion = EventExpansionDefinition.Standard }; m.AddModuleIdAndNameMapping(c); m.AddFederatedIdMapping("event_id"); m.AddEventStartEndTimeMapping("start_time"); m.AddEventStartEndTimeMapping("end_time"); return(m); }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { var m = new TableColumnMappings { SpanExpansionRequired = true }; m.AddFederatedIdMapping("span_id"); m.AddSimpleMapping("timetable_id", "src_timetable_id"); m.AddSimpleMapping("span_name", "name"); m.AddRemainingSimpleMappings(Columns); return(m); }
private void SpecialSpanExpansion(Row[] rows, TableColumnMappings colMappings) { var rowsToInsert = new List <Row>(); var spanIds = new List <long>(); foreach (var r in rows) { long spanId = (long)r["federated_span_id"]; spanIds.Add(spanId); string wks = ((string)r["weeks"]).ToUpper(); int timetableId = (int)r["src_timetable_id"]; var weekDates = _caches.WeekDatesCache.Get(timetableId); int weekNum = 0; int occurrence = 0; foreach (var ch in wks) { if (ch == 'Y') { ++occurrence; var dt = weekDates.StartingDates[weekNum]; for (int day = 0; day < 7; ++day) { Row vr = r.Clone(); vr["span_date"] = dt.AddDays(day); vr["span_week_number"] = weekNum + 1; vr["span_week_occurrence"] = occurrence; rowsToInsert.Add(vr); } } ++weekNum; } } if (spanIds.Any()) { // first remove all instances of span... _targetTable.DeleteSpanInstances(_connectionString, _timeoutSecs, spanIds); // then insert afresh... _targetTable.Upsert(_connectionString, _timeoutSecs, rowsToInsert, colMappings, _caches, _configuration); } }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddExamIdAndNameMapping(); m.AddFederatedIdMapping("event_id"); m.AddRoomIdAndNameMapping(c); m.AddRoomLayoutIdAndNameMapping(c); m.AddFederatedIdMapping("slot_id"); m.AddRemainingSimpleMappings(Columns); return(m); }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddConsolidatedOrFederatedIdMapping(c, Entity.Site, "site_id1"); m.AddColumnMappingLookup("site_id1", "site_name1", ConsolidationType.Site, c); m.AddConsolidatedOrFederatedIdMapping(c, Entity.Site, "site_id2"); m.AddColumnMappingLookup("site_id2", "site_name2", ConsolidationType.Site, c); m.AddRemainingSimpleMappings(Columns); return(m); }
public TransformationGetDataForBulkInsertOperation( string adminConnectionString, Table srcTable, PublicTable targetTable, FixupCaches caches, DataStoreConfiguration configuration, int srcTimetableId) : base(DatabaseUtils.CreateConnectionStringSettings(adminConnectionString)) { _srcTable = srcTable; _targetTable = targetTable; _caches = caches; _configuration = configuration; _colMappings = targetTable.GetColumnMappingsFromStage(_configuration); _srcTimetableId = srcTimetableId; }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddConsolidatedOrFederatedIdMapping(c, Entity.EventCat, "event_cat_id"); m.AddColumnMappingLookup("event_cat_id", "name", ConsolidationType.EventCat, c); m.AddBooleanMapping("registers_req"); m.AddBooleanMapping(ColumnConstants.RegistersReqResolvedColumnName); m.AddAuditMapping(c); m.AddOriginMapping(); m.AddRemainingSimpleMappings(Columns); return(m); }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddFederatedIdMapping("booking_id"); m.AddUserIdAndNameMapping(c); m.AddDeptIdAndNameMapping(c); m.AddEventCatIdAndNameMapping(c); m.AddBooleanMapping("add_me"); m.AddAuditMapping(c); m.AddOriginMapping(); m.AddRemainingSimpleMappings(Columns); return(m); }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddStudentIdAndNameMapping(c); m.AddEventInstanceMapping(); m.AddFederatedIdMapping("event_id"); m.AddSimpleMapping("timetable_week", "week"); m.AddMarkIdAndNameMapping(); m.AddAuditMapping(c); m.AddRemainingSimpleMappings(Columns); return(m); }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddConsolidatedOrFederatedIdMapping(c, Entity.Supervisor, "supervisor_id"); m.AddBooleanMapping("can_send_sms"); m.AddBooleanMapping("can_send_email"); m.AddAuditMapping(c); m.AddOriginMapping(); m.AddRemainingSimpleMappings(Columns); return(m); }
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c) { // establish the column mappings between the staging tables and this public table var m = new TableColumnMappings(); m.AddFederatedIdMapping("mark_id"); m.AddDescriptionMapping(); m.AddMarkDefinition(); m.AddOriginMapping(); m.AddBooleanMapping("precedence"); m.AddBooleanMapping("card"); m.AddBooleanMapping("send_notification"); m.AddRemainingSimpleMappings(Columns); return(m); }