Beispiel #1
0
        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}");
        }
Beispiel #2
0
        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());
        }
Beispiel #3
0
        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);
        }
Beispiel #5
0
        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]);
            }
        }
Beispiel #6
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;
 }
Beispiel #8
0
 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);
     }
 }
Beispiel #9
0
 public override void Delete(
     string sqlConnectionString,
     int timeoutSecs,
     Row stagingRow,
     TableColumnMappings colMappings,
     FixupCaches caches,
     DataStoreConfiguration configuration)
 {
     DeleteEventAssignment(sqlConnectionString, timeoutSecs, stagingRow);
 }
Beispiel #10
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("session_id");
            m.AddRemainingSimpleMappings(Columns);

            return(m);
        }
Beispiel #11
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("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);
        }
Beispiel #13
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("booking_id");
            m.AddResourceIdAndNameMapping();
            m.AddBooleanMapping("send_email");

            return(m);
        }
Beispiel #14
0
        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);
        }
Beispiel #16
0
        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;
 }
Beispiel #18
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("activity_id");
            m.AddStudentIdAndNameMapping(c);
            m.AddResourceIdAndNameMapping();
            m.AddAuditMapping(c);

            return(m);
        }
Beispiel #19
0
        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);
        }
Beispiel #20
0
        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);
        }
Beispiel #21
0
        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);
        }
Beispiel #22
0
        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);
            }
        }
Beispiel #23
0
        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);
        }
Beispiel #24
0
        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);
        }
Beispiel #25
0
 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;
 }
Beispiel #26
0
        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);
        }
Beispiel #27
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("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);
        }
Beispiel #28
0
        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);
        }
Beispiel #29
0
        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);
        }
Beispiel #30
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("mark_id");
            m.AddDescriptionMapping();
            m.AddMarkDefinition();
            m.AddOriginMapping();
            m.AddBooleanMapping("precedence");
            m.AddBooleanMapping("card");
            m.AddBooleanMapping("send_notification");

            m.AddRemainingSimpleMappings(Columns);

            return(m);
        }