Codeunit 8700 Table Information Cache Impl.
src/System Application/App/Table Information/src/TableInformationCacheImpl.Codeunit.al170 lines, Copyright (c) Microsoft Corporation. MIT
// ------------------------------------------------------------------------------------------------// Copyright (c) Microsoft Corporation. All rights reserved.// Licensed under the MIT License. See License.txt in the project root for license information.// ------------------------------------------------------------------------------------------------namespace System.DataAdministration;using System.Environment;codeunit 8700 "Table Information Cache Impl."{ Access = Internal; InherentEntitlements = X; InherentPermissions = X; Permissions = tabledata "Table Information Cache" = rimd, tabledata "Company Size Cache" = rimd; var ProgressDialogLbl: Label 'Progress: @1@@@@@@@@@@', Comment = 'The string should always end with @1@@@@@@@@@@.'; CrossCompanyDataLbl: Label '(Cross-Company Data)', MaxLength = 30; FailedToCalculateGrowthTxt: Label 'Failed to calculate growth for table %1 %2', Locked = true; CategoryTok: Label 'Table Information Cache', Locked = true; procedure RefreshTableInformationCache() var TableInformation: Record "Table Information"; TableInformationCache: Record "Table Information Cache"; ExistingTableInformationCache: Record "Table Information Cache"; ProgressDialog: Dialog; Total: Integer; Counter: Integer; begin if not TableInformationCache.IsEmpty() then TableInformationCache.DeleteAll(); Total := TableInformation.Count(); if GuiAllowed() then ProgressDialog.Open(ProgressDialogLbl); if TableInformation.FindSet() then repeat TableInformationCache.TransferFields(TableInformation); if TableInformationCache."Company Name" = '' then TableInformationCache."Company Name" := CrossCompanyDataLbl; // The platform's "Table Information" virtual table is keyed on Company Name, Table No. and Table Name, // so it can return several rows for the same company and table number (e.g. orphaned/renamed SQL tables). // The cache is keyed only on Company Name and Table No., so aggregate the sizes for such rows instead of // failing the whole refresh with a duplicate primary key error. if ExistingTableInformationCache.Get(TableInformationCache."Company Name", TableInformationCache."Table No.") then begin if TableInformationCache."Data Size (KB)" > ExistingTableInformationCache."Data Size (KB)" then ExistingTableInformationCache."Table Name" := TableInformationCache."Table Name"; if TableInformationCache."Record Size" > ExistingTableInformationCache."Record Size" then ExistingTableInformationCache."Record Size" := TableInformationCache."Record Size"; ExistingTableInformationCache."No. of Records" += TableInformationCache."No. of Records"; ExistingTableInformationCache."Size (KB)" += TableInformationCache."Size (KB)"; ExistingTableInformationCache."Data Size (KB)" += TableInformationCache."Data Size (KB)"; ExistingTableInformationCache."Index Size (KB)" += TableInformationCache."Index Size (KB)"; ExistingTableInformationCache.Modify(); end else TableInformationCache.Insert(); Counter += 1; if GuiAllowed() then ProgressDialog.Update(1, Round(Counter / Total) * 10000) until TableInformation.Next() = 0; if GuiAllowed() then ProgressDialog.Close(); RefreshCompanySizeCache(); CalcThirtyDayGrowth(); end; procedure CalcCompaniesTotalSize(): Integer var CompanySizeCache: Record "Company Size Cache"; begin CompanySizeCache.CalcSums("Size (KB)"); exit(CompanySizeCache."Size (KB)"); end; local procedure RefreshCompanySizeCache() var Company: Record Company; TableInformationCache: Record "Table Information Cache"; CompanySizeCache: Record "Company Size Cache"; begin if not CompanySizeCache.IsEmpty() then CompanySizeCache.DeleteAll(); InsertCompanySizeCache(TableInformationCache, CrossCompanyDataLbl); // common data if Company.FindSet() then repeat InsertCompanySizeCache(TableInformationCache, Company.Name); until Company.Next() = 0; end; local procedure InsertCompanySizeCache(var TableInformationCache: Record "Table Information Cache"; CompanyName: Text) var CompanySizeCache: Record "Company Size Cache"; begin TableInformationCache.SetRange("Company Name", CompanyName);#pragma warning disable AA0181 TableInformationCache.FindSet();#pragma warning restore TableInformationCache.CalcSums("Size (KB)", "Data Size (KB)", "Index Size (KB)"); CompanySizeCache.Init(); CompanySizeCache."Company Name" := TableInformationCache."Company Name"; CompanySizeCache."Size (KB)" := TableInformationCache."Size (KB)"; CompanySizeCache."Data Size (KB)" := TableInformationCache."Data Size (KB)"; CompanySizeCache."Index Size (KB)" := TableInformationCache."Index Size (KB)"; CompanySizeCache.Insert(); end; local procedure CalcThirtyDayGrowth() var TableInformationCache: Record "Table Information Cache"; begin SetBiggestTablesFilter(TableInformationCache); if TableInformationCache.FindSet() then repeat if TryCalcThirtyDayGrowthForTable(TableInformationCache) then TableInformationCache.Modify() else Session.LogMessage('00001PV', StrSubstNo(FailedToCalculateGrowthTxt, TableInformationCache."Table No.", TableInformationCache."Table Name"), Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', CategoryTok); until TableInformationCache.Next() = 0; end; procedure SetBiggestTablesFilter(var TableInformationCache: Record "Table Information Cache") begin TableInformationCache.SetCurrentKey("Data Size (KB)"); TableInformationCache.SetAscending("Data Size (KB)", false); TableInformationCache.FilterGroup := 2; // get top 25 biggest tables if TableInformationCache.FindSet() then begin TableInformationCache.Next(24); TableInformationCache.SetFilter("Data Size (KB)", '>=%1', TableInformationCache."Data Size (KB)"); TableInformationCache.FindFirst(); end; TableInformationCache.FilterGroup := 0; end; [TryFunction] local procedure TryCalcThirtyDayGrowthForTable(var TableInformationCache: Record "Table Information Cache") var RecordRef: RecordRef; FieldRef: FieldRef; begin RecordRef.Open(TableInformationCache."Table No."); if not (TableInformationCache."Company Name" in ['', CrossCompanyDataLbl, CompanyName()]) then RecordRef.ChangeCompany(TableInformationCache."Company Name"); FieldRef := RecordRef.Field(RecordRef.SystemCreatedAtNo);#pragma warning disable AA0217 FieldRef.SetFilter(StrSubstNo('''''|<%1', CreateDateTime(CalcDate('<-30D>', Today), 0T)));#pragma warning restore AA0217 TableInformationCache."Last Period No. of Records" := RecordRef.Count(); TableInformationCache."Last Period Data Size (KB)" := Round((RecordRef.Count() * TableInformationCache."Record Size" / 1024), 1); if TableInformationCache."Last Period Data Size (KB)" <> 0 then TableInformationCache."Growth %" := Round(((TableInformationCache."Data Size (KB)" / TableInformationCache."Last Period Data Size (KB)") - 1), 0.01) * 100; RecordRef.Close(); end; procedure GetTableUrl(Company: Text; TableNo: Integer): Text begin if Company = CrossCompanyDataLbl then Company := ''; // Clear the Company text if it is the CrossCompanyDataLbl to avoid errors with GetUrl exit(GetUrl(ClientType::Web, Company, ObjectType::Table, TableNo)); end;}