Fixing AX SQLDICTIONARY table and field IDs

// Note: You may have to drop and then later re-create the I_65518FIELD
// constraint on the SqlDictionary table to do this successfully.
// Adapted from concept by Martin Drab, replacing the releaseUpdateDB calls
// with the equivalent logic, and preventing incorrect updates to records where the
// field IDs are temporarily duplicated, e.g. if they need to be swapped.
//
// v2: Also now avoids updating records with shadow != 0, since views sometimes
//     legitimately seem to have a duplicate field name with a different id and shadow = 1.
// v3: Adds support for scenarios where table IDs need to be swapped.
// v4: Adds support for table inheritance.
//
// Written by Martin Walker (mwalker@syndaxis.com)
static void MW_FixSQLDictTableAndFieldIDs(Args _args)
{
    Dictionary      dictionary = new Dictionary();
    SysDictTable    dictTable;
    DictField       dictField;
    TableId         tableId, tempTableId, rootTableId;
    FieldId         fieldId;
    SqlDictionary   sqlDictionaryTable;
    SqlDictionary   sqlDictionaryField;
    SqlDictionary   sqlDictionary;
    SqlDictionary   sqlField;
    Map            tableIdToFromMap = new Map(Types::Integer, Types::Integer);
    MapEnumerator   mapEnum;
    List            newTableIdList = new List(Types::Integer);
    ListEnumerator  listEnum;
    TableName       tableName,rootTableName;

    setPrefix("Update of data dictionary IDs");

    ttsBegin;

     // First find the maximum table ID in use, and start temporary IDs one higher
    select maxOf(TabId) from sqlDictionary
      where sqlDictionary.fieldId == 0;
    tempTableId = sqlDictionary.TabId + 1;

    // Iterate through the tables and fix SQLDICTIONARY.TABLEID
    tableId = dictionary.tableNext(0);
    while (tableId)
    {
        dictTable = new SysDictTable(tableId);

        if (!dictTable.isSystemTable())
        {
            // Finds table in SqlDictionary by name in AOT, if ID was changed.
            // 0 field ID means a table.
            select firstOnly sqlDictionaryTable
                where sqlDictionaryTable.name    == dictTable.name()
                   && sqlDictionaryTable.fieldId == 0
                   && sqlDictionaryTable.tabId   != dictTable.id();

            if (sqlDictionaryTable)
            {
                // Check whether target id already exists
                select firstonly sqlDictionary
                  where sqlDictionary.TabId   == dictTable.id()
                     && sqlDictionary.FieldId == 0;
                if (!sqlDictionary)
                {
                    // Store new (key) and old (value) table IDs in map
                    tableIdToFromMap.insert(dictTable.id(),sqlDictionaryTable.tabId);
                }
                else
                {
                    // First store a temporary new (key) and old (value) table ID in map
                    tableIdToFromMap.insert(tempTableId,sqlDictionaryTable.tabId);
                    // Store a second record in the map to change the temporary ID to the final value
                    tableIdToFromMap.insert(dictTable.id(),tempTableId);
                    tempTableId++;
                }
            }
        }
        tableId = dictionary.tableNext(tableId);
    }

    // Create list with new table IDs in _descending_ order
    mapEnum = tableIdToFromMap.getEnumerator(); //Note: Enumerates in ascending key order
    while (mapEnum.moveNext())
    {
        newTableIdList.addStart(mapEnum.currentKey());
    }

    // Now enumerate the list and update the table IDs
    listEnum = newTableIdList.getEnumerator();
    while (listEnum.moveNext())
    {
        tableId = listEnum.current();
        dictTable = new SysDictTable(tableId);
        // Where we have used a temporary table ID, dictTable cannot be used
        if (dictTable)
            tableName = dictTable.name();
        else
            tableName = "";

        select firstOnly sqlDictionaryTable
            where sqlDictionaryTable.tabId   == tableId
               && sqlDictionaryTable.fieldId == 0;

        if (sqlDictionaryTable)
        {
            // We dealt with this by first changing to a dummy table ID
            // and then from the dummy ID to the desired ID
            // so this should no longer happen, but just in case...
            warning(strFmt("Unable to change Table %1 ID from %2 to %3 - target ID already in use",
                           tableName,
                           tableIdToFromMap.lookup(tableId),
                           tableId));
        }
        else
        {
            // Note that this must fix the table ID in the records for the table and its fields
            // (which is why we can't just add the table name to the where clause
            // to cover the scenario where the target table ID is in use).
            // The field IDs will be corrected later in the job if necessary.
            update_recordset sqlDictionary
                setting TabId = tableId
            where sqlDictionary.TabId == tableIdToFromMap.lookup(tableId);

            info(strFmt("Table %1 ID changed from %2 to %3",
                        tableName,
                        tableIdToFromMap.lookup(tableId),
                        tableId));
        }
    }

    // Now iterate through the tables again and fix SQLDICTIONARY.FIELDID
    tableId = dictionary.tableNext(0);

    while (tableId)
    {
        dictTable = new SysDictTable(tableId);
        setPrefix(dictTable.name());

        if (!dictTable.isSystemTable())
        {
            //With table inheritance, the SqlDictionary records have the root table id.
            rootTableId = SysDictTable::getRootTable(tableId);
            rootTableName = new SysDictTable(rootTableId).name();

            select firstonly sqlDictionary
                where sqlDictionary.tabId   == rootTableId
                   && sqlDictionary.fieldId == 0
                   && sqlDictionary.name    == rootTableName;

            if (sqlDictionary)
            {
                fieldId = dictTable.fieldNext(0);

                // For all fields in table
                while (fieldId)
                {
                    dictField = dictTable.fieldObject(fieldId);

                    if (dictField.isSql() && !dictField.isSystem())
                    {
                        // Finds fields in SqlDictionary by name and compares IDs
                        select firstOnly sqlDictionaryField
                            where sqlDictionaryField.tabId   == rootTableId
                               && sqlDictionaryField.name    == dictField.name()
                               && sqlDictionaryField.fieldId != 0
                               && sqlDictionaryField.fieldId != dictField.id()
                               && sqlDictionaryField.shadow  == 0;

                        if (sqlDictionaryField && dictField.id() != 0)
                        {
                            // Updates field ID in SqlDictionary
                            update_recordset sqlField
                                setting FieldId = dictField.id()
                            where sqlField.TabId   == rootTableId
                               && sqlField.FieldId == sqlDictionaryField.fieldId
                               && sqlField.Name    == dictField.name()
                               && sqlField.shadow  == 0;

                            info(strFmt("Field %1 - ID changed (%2 -> %3)",
                                        dictField.name(),
                                        sqlDictionaryField.fieldId,
                                        dictField.id()));
                        }
                    }
                    fieldId = dictTable.fieldNext(fieldId);
                }
            }
        }
        tableId = dictionary.tableNext(tableId);
    }
    ttsCommit;
}