Codeunit 8700 Table Information Cache Impl.

System Application, System.DataAdministration

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