Page 8704 Indexes List Part

System Application, System.DataAdministration

src/System Application/App/Table Information/src/IndexesListPart.Page.al382 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.Database;using System.Diagnostics;using System.Environment;using System.Reflection;/// <summary>/// Shows more detailed information about indexes and provides actions to manage them./// </summary>page 8704 "Indexes List Part"{    Caption = 'Indexes';    PageType = ListPart;    AdditionalSearchTerms = 'Database,Size,Storage';    ApplicationArea = All;    SourceTable = "Database Index";    SourceTableTemporary = true;    Editable = false;    InsertAllowed = false;    DeleteAllowed = false;    Permissions = tabledata "Key" = r,                  tabledata "Database Index" = r;    layout    {        area(Content)        {            repeater(Indexes)            {                field("Index name"; Rec."Index Name")                {                    Width = 30;                    Caption = 'Index Name';                    ToolTip = 'Specifies the name of the index.';                }                field("Index Type"; Rec."Index Type")                {                    Caption = 'Index Type';                    ToolTip = 'Specifies whether the row represents a regular database index or a SIFT structure.';                }                field(Enabled; Rec.Enabled)                {                    Caption = 'Enabled in Database';                    ToolTip = 'Specifies whether the index is enabled in the database.';                }                field("AL defined"; Rec."Metadata Defined")                {                    Caption = 'AL Defined';                    ToolTip = 'Specifies whether the index is defined as an AL key or automatically created to improve performance.';                }                field("Unique"; Rec.Unique)                {                    Caption = 'Unique';                    ToolTip = 'Specifies whether the index is defined as unique in AL.';                }                field("Fragmentation"; Rec."Fragmentation %")                {                    Caption = 'Fragmentation (%)';                    ToolTip = 'Specifies the percentage of fragmentation in the index.';                    AutoFormatType = 0;                    DecimalPlaces = 0;                }                field("Index size in KB"; Rec."Index Size (KB)")                {                    Caption = 'Index Size (kB)';                    ToolTip = 'Specifies the size of the index in kilobytes.';                }                field("User seeks"; Rec."User seeks")                {                    Width = 10;                    Caption = 'Seeks';                    ToolTip = 'Specifies the number of user seeks on this index since the database was last started.';                }                field("User scans"; Rec."User scans")                {                    Width = 10;                    Caption = 'Scans';                    ToolTip = 'Specifies the number of user scans on this index since the database was last started.';                }                field("User lookups"; Rec."User lookups")                {                    Width = 10;                    Caption = 'Lookups';                    ToolTip = 'Specifies the number of user lookups on this index since the database was last started.';                }                field("User updates"; Rec."User updates")                {                    Width = 10;                    Caption = 'Updates';                    ToolTip = 'Specifies the number of user updates on this index since the database was last started.';                }                field("Last seek"; FormatDateTime(Rec."Last seek"))                {                    Caption = 'Last Seek';                    ToolTip = 'Specifies the timestamp of the last user seek on this index since the database was last started.';                }                field("Last scan"; FormatDateTime(Rec."Last scan"))                {                    Caption = 'Last Scan';                    ToolTip = 'Specifies the timestamp of the last user scan on this index since the database was last started.';                }                field("Last lookup"; FormatDateTime(Rec."Last lookup"))                {                    Caption = 'Last Lookup';                    ToolTip = 'Specifies the timestamp of the last user lookup on this index since the database was last started.';                }                field("Last Update"; FormatDateTime(Rec."Last update"))                {                    Caption = 'Last Update';                    ToolTip = 'Specifies the timestamp of the last user update on this index since the database was last started.';                }                field("Stat updated at"; FormatDateTime(Rec."Statistics rebuild at"))                {                    Caption = 'Statistics updated at';                    ToolTip = 'Specifies the last time the index''s corresponding statistics was rebuild. Statistics are updated automatically by the database engine based on certain thresholds of data changes, or when an index is re-enabled.';                }            }        }    }    actions    {        area(Processing)        {            action(TurnIndexOff)            {                Caption = 'Turn index off';                Enabled = Rec.Enabled and not Rec.Unique;                Image = Delete;                ToolTip = 'Turn off the index in the database. For non-AL defined indexes, this action cannot be undone, for AL-defined indexes, the index can be re-created by enabling it again.';                trigger OnAction()                var                    IndexManagement: Codeunit "Index Management";                    RecordIDOfCurrentPosition: RecordId;                    IsMetadataDefined: Boolean;                begin                    IsMetadataDefined := Rec."Metadata Defined";                    if not IsMetadataDefined then                        if not Dialog.Confirm(TurnOffIndexWarningQst) then                            exit;                    IndexManagement.DisableIndex(Rec);                    RecordIDOfCurrentPosition := Rec.RecordId; // Save the current position to be able to return to it after refreshing the data.                    Rec.DeleteAll(); // Clear the temporary table to make sure the disabled index is not shown.                    BuildInMemoryList(Rec.TableId); // Rebuild the in-memory list to get the updated index status.                    if IsMetadataDefined then                        if Rec.Get(RecordIDOfCurrentPosition) then; // Done to avoid throwing an error, returning to the right position is of secondary importance.                    CurrPage.Update(false);                end;            }            action(TurnIndexOffInAllCompanies)            {                Caption = 'Turn index off (all companies)';                Enabled = Rec.Enabled and not Rec.Unique;                Image = Delete;                ToolTip = 'Turn off the index in the database in all companies. For non-AL defined indexes, this action cannot be undone, for AL-defined indexes, the index can be re-created by enabling it again.';                trigger OnAction()                var                    Company: Record Company;                    DatabaseIndex: Record "Database Index";                    IndexManagement: Codeunit "Index Management";                    RecordIDOfCurrentPosition: RecordId;                    IsMetadataDefined: Boolean;                begin                    IsMetadataDefined := Rec."Metadata Defined";                    if not IsMetadataDefined then                        if not Dialog.Confirm(TurnOffIndexWarningQst) then                            exit;                    RecordIDOfCurrentPosition := Rec.RecordId; // Save the current position to be able to return to it after refreshing the data.                    if Company.FindSet() then                        repeat                            if DatabaseIndex.Get(Rec.TableId, Rec."Index Name", Company.Name, Rec."Source App ID", Rec."Index Type") then                                IndexManagement.DisableIndex(DatabaseIndex);                        until Company.Next() = 0;                    Rec.DeleteAll(); // Clear the temporary table to make sure the disabled index is not shown.                    BuildInMemoryList(Rec.TableId); // Rebuild the in-memory list to get the updated index status.                    if IsMetadataDefined then                        if Rec.Get(RecordIDOfCurrentPosition) then; // Done to avoid throwing an error, returning to the right position is of secondary importance.                    CurrPage.Update(false);                end;            }            action(TurnOnIndex)            {                Caption = 'Turn index on';                Enabled = not Rec.Enabled and Rec."Metadata Defined" and not Rec.Unique;                Image = Add;                ToolTip = 'Enqueues the index to be turned on in the subsequent maintenance window.';                trigger OnAction()                var                    KeyRec: Record "Key";                begin                    if FindKeyFromDatabaseIndex(Rec, KeyRec) then                        EnableIndex(KeyRec, Rec."Company Name", Rec."Index Type" = Rec."Index Type"::SIFT);                    Message(TurnOnIndexQueueInfoMsg);                end;            }            action(TurnOnIndexAllCompanies)            {                Caption = 'Turn index on (all companies)';                Enabled = not Rec.Enabled and Rec."Metadata Defined" and not Rec.Unique;                Image = Add;                ToolTip = 'Enqueues the index to be turned on for all companies in the subsequent maintenance window.';                trigger OnAction()                var                    Company: Record Company;                    KeyRec: Record "Key";                begin                    if not FindKeyFromDatabaseIndex(Rec, KeyRec) then                        exit;                    if Company.FindSet() then                        repeat                            EnableIndex(KeyRec, Company.Name, Rec."Index Type" = Rec."Index Type"::SIFT);                        until Company.Next() = 0;                    Message(TurnOnIndexQueueInfoMsg);                end;            }        }    }    trigger OnFindRecord(Which: Text): Boolean    var        LinkTableId: Integer;        PrevFilterGroup: Integer;    begin        // After calling a action this method gets called again, ensure we don't double insert records into the temporary table.        if Rec.Count() <> 0 then            exit(Rec.Find(Which));        PrevFilterGroup := Rec.FilterGroup;        Rec.FilterGroup := 4; // Link group.        if not Evaluate(LinkTableId, Rec.GetFilter("TableId")) then begin            Rec.FilterGroup := PrevFilterGroup;            exit(false);        end;        Rec.FilterGroup := PrevFilterGroup;        BuildInMemoryList(LinkTableId);        exit(Rec.Find(Which));    end;    local procedure BuildInMemoryList(LinkTableId: Integer)    var        DatabaseIndex: Record "Database Index";        KeyRec: Record "Key";        IndexName: Text[128];    begin        // Combines the indexes from "Database Index" and "Key" virtual tables. "Database Index" contains all indexes currently in the database,        // including those automatically created by the database engine, while "Key" contains all metadata defined keys.        DatabaseIndex.SetRange(DatabaseIndex.TableId, LinkTableId);        DatabaseIndex.SetRange(DatabaseIndex."Company Name", SetCompanyName);        if DatabaseIndex.FindSet() then            repeat                Rec.TransferFields(DatabaseIndex);                Rec.Insert();            until DatabaseIndex.Next() = 0;        KeyRec.SetRange(KeyRec.TableNo, LinkTableId);        KeyRec.SetRange(KeyRec.SQLIndex);        if KeyRec.FindSet() then            repeat                if KeyRec.MaintainSQLIndex or not KeyRec.Enabled then begin                    IndexName := GetKeyIndexName(KeyRec, false);                    if not Rec.Get(LinkTableId, IndexName, SetCompanyName, KeyRec."Source App ID", Rec."Index Type"::Index) then                        InsertDisabledIndex(KeyRec, false);                end;                if KeyRec.MaintainSIFTIndex then begin                    IndexName := GetKeyIndexName(KeyRec, true);                    if not Rec.Get(LinkTableId, IndexName, SetCompanyName, KeyRec."Source App ID", Rec."Index Type"::SIFT) then                        InsertDisabledIndex(KeyRec, true);                end;            until KeyRec.Next() = 0;    end;    local procedure InsertDisabledIndex(KeyRec: Record "Key"; IsSift: Boolean)    begin        Clear(Rec);        Rec.TableId := KeyRec.TableNo;        Rec."Column Names" := KeyRec."Key";        Rec."Company Name" := CopyStr(SetCompanyName, 1, MaxStrLen(Rec."Company Name"));        Rec.Unique := KeyRec.Unique and not IsSift;        Rec.Enabled := false;        Rec."Metadata Defined" := true;        Rec."Index Name" := GetKeyIndexName(KeyRec, IsSift);        Rec."Source App ID" := KeyRec."Source App ID";        if IsSift then begin            Rec."Index Type" := Rec."Index Type"::SIFT;            Rec."Included Fields" := KeyRec.SumIndexFields;        end else            Rec."Index Type" := Rec."Index Type"::Index;        Rec.Insert();    end;    procedure SetCompanyFilter(NewCompanyName: Text)    begin        SetCompanyName := NewCompanyName;        // Clear the temporary table to make sure only indexes for the selected company is shown.        Rec.DeleteAll();    end;    local procedure FindKeyFromDatabaseIndex(DatabaseIndex: Record "Database Index"; var KeyRec: Record "Key"): Boolean    var        IsSift: Boolean;    begin        KeyRec.SetRange(KeyRec.TableNo, DatabaseIndex.TableId);        KeyRec.SetRange(KeyRec."Source App ID", DatabaseIndex."Source App ID");        IsSift := DatabaseIndex."Index Type" = DatabaseIndex."Index Type"::SIFT;        if KeyRec.FindSet() then            repeat                if GetKeyIndexName(KeyRec, IsSift) = DatabaseIndex."Index Name" then                    exit(true);            until KeyRec.Next() = 0;        exit(false);    end;    local procedure GetKeyIndexName(KeyRec: Record "Key"; IsSift: Boolean): Text[128]    var        IndexManagement: Codeunit "Index Management";    begin        if KeyRec."Key name" <> '' then            exit(KeyRec."Key name");        exit(IndexManagement.GetKeyIndexName(KeyRec, IsSift));    end;    local procedure FormatDateTime(DateTimeValue: DateTime): Text    begin        if (DateTimeValue = 0DT) or (DateTimeValue = CreateDateTime(00000101D, 0T)) then            exit(NoDateTimeValueLbl);        exit(Format(DateTimeValue));    end;    local procedure EnableIndex(KeyRec: Record "Key"; CompanyName: Text; IsSift: Boolean)    var        IndexManagement: Codeunit "Index Management";    begin        if IsSift then            IndexManagement.EnableSiftKey(KeyRec, CompanyName)        else            IndexManagement.EnableKey(KeyRec, CompanyName);    end;    var        SetCompanyName: Text;        NoDateTimeValueLbl: Label '-', Locked = true;        TurnOffIndexWarningQst: Label 'Turning a non-AL defined index off cannot be undone. Please confirm.';        TurnOnIndexQueueInfoMsg: Label 'The index has been enqueued to be turned on. It will be attempted during the subsequent maintenance window (overnight local time).';}