Чтение метаданных таблицы Dynamics NAV с помощью SQL

Question

Я хотел бы иметь возможность считывать метаданные таблицы Dynamics NAV 2013 непосредственно из базы данных SQL Server, не требуя среды разработки NAV.

Я могу просмотреть двоичные столбцы больших двоичных объектов SQL "image" с помощью запроса, подобного следующему (отфильтруйте в соответствии с предложением WHERE):

SELECT 
    o.[Name],
    m.[Object Type],
    m.[Metadata], -- XML Metadata
    m.[User Code], -- C# Metadata
    m.[User AL Code] -- C/AL Metadata
FROM [Navision].[dbo].[Object Metadata] AS m
JOIN [Navision].[dbo].[Object] AS o
ON m.[Object ID] = o.[ID]
AND o.[Company Name] = 'YourCompanyName'
AND o.[Type] = 0 -- 0 is NAV Table Object Type

Я могу сохранить двоичные данные из [Метаданные], [Код пользователя] и [Код пользователя AL] в файлы, используя код.Net или быстрый скрипт с драйвером SQL. Я попытался использовать 7-zip для распаковки, шестнадцатеричный редактор для просмотра и команду Cygwin "file" для обнаружения этих типов BLOB-файлов.

К сожалению, я не могу понять, как декодировать или распаковывать двоичные данные в читаемый или пригодный для использования формат. До тех пор, пока я не смогу использовать данные в этих полях напрямую, я должен открыть среду разработки NAV и использовать конструктор объектов для просмотра свойств OptionString, разделенных запятыми, для раскрывающегося списка поиска с нуля (каждый элемент в списке хранится в внутренняя база данных в виде целого числа - 0 для первого элемента, 1 для второго и т. д.). Строковые значения отсутствуют в справочной таблице SQL, но NAV помещает их в большие двоичные метаданные таблицы.

Это недостающее звено для меня, чтобы полностью поддерживать моих пользователей NAV в качестве администратора базы данных без необходимости в NAV-разработчике искать эти сопоставления настраиваемых полей NAV для номеров. Затем я могу просмотреть эти значения списка и создать соответствующие операторы SQL CASE или пользовательские таблицы поиска по мере необходимости.

После того, как я получу эту часть, я смогу создавать расширенные представления SQL, запросы, отчеты и инструменты без необходимости доступа к инструментам внешнего пользователя или разработчика Dynamics NAV.

Пожалуйста, дайте мне знать, если у вас есть знания о формате двоичных данных, используемых для этих свойств BLOB-объектов метаданных объектов. Любой совет о том, как преобразовать в читаемый или пригодный для использования формат, был бы полезен.

Answer 1

Мне удалось получить ответ для формата этих двоичных полей метаданных от автора блога deV.ch - человек против кода, Dynamics NAV & C# .NET. Основываясь на обратном инжиниринге devch, мы определили, что первые четыре байта (32 бита) этих полей используются NAV для хранения "магического числа", которое определяет пользовательский тип BLOB-объекта NAV.

В случае этих полей метаданных магическое число NAV Compressed Blob-Type равно 0x02457d5b (шестнадцатеричное). Чтобы использовать стандартный.Net DeflateStream для распаковки, просто отбросьте эти первые четыре байта магического числа, а затем обработайте остальную часть потока с помощью DeflateStream как обычно.

Мне удалось успешно протестировать этот процесс с помощью.Net, теперь я планирую протестировать с Python или некоторыми другими инструментами для дефляции сторонних производителей, чтобы проверить, соответствует ли реализация дефлята отраслевому стандарту. Еще раз спасибо devch за статью, которая привела к этому решению: Доступ к сжатым BLOB-объектам извне NAV (NAV2013) (Revisited).

Обновление: протестировано с Python zlib и все работает! Соответствующий стандартам алгоритм Deflate используется после удаления пользовательского магического числа типа NAV Blob. Вот пример кода (Python):

# Example Using Python 3.x
import zlib, sys, struct

# NAV custom Blob-Type identifier (first 4 bytes)
magic = struct.unpack('>I',sys.stdin.buffer.read(4))[0]
print('magic number = %#010x' % magic, file=sys.stderr)
# Remaining binary data is standard DEFLATE without header
input = sys.stdin.buffer.read()
output = zlib.decompress(input,-15)
sys.stdout.buffer.write(output)

Для проверки используйте что-то вроде следующего:

python -u test.py < Input_Meta.blob > Output_Meta.txt

Конечно.Net DeflateStream работает и после удаления первых четырех байтов. Этот пример просто показывает, что вы не ограничены использованием языков.Net.

Answer 2

Можно извлечь metadata из этой таблицы через Nav и, как я вижу, он хранится в виде простого текста, но в двоичном поле. Это может быть сохранено в файл, используя простой MemoryStream (в Nav это называется OutSteram). Итак, для таблицы № 3 я получаю следующий XML:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<MetaTable xmlns="urn:schemas-microsoft-com:dynamics:NAV:MetaObjects" ID="3" CaptionML="ENU=Payment Terms;RUS=Условия платежа" DataPerCompany="1" Name="Payment Terms" LookupFormID="4" DataCaptionFields="1,5">
    <Fields>
        <Field ID="1" Datatype="Code" DataLength="10" Enabled="1" FieldClass="Normal" Name="Code" CaptionML="ENU=Code;RUS=Код" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" NotBlank="1" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
        <Field ID="2" Datatype="DateFormula" Enabled="1" FieldClass="Normal" Name="Due Date Calculation" CaptionML="ENU=Due Date Calculation;RUS=Расчет срока оплаты" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" NotBlank="0" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
        <Field ID="3" Datatype="DateFormula" Enabled="1" FieldClass="Normal" Name="Discount Date Calculation" CaptionML="ENU=Discount Date Calculation;RUS=Расчет даты скидки" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" NotBlank="0" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
        <Field ID="4" Datatype="Decimal" Enabled="1" FieldClass="Normal" Name="Discount %" CaptionML="ENU=Discount %;RUS=Скидка (%)" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" MinValue="0" MaxValue="100" NotBlank="0" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
        <Field ID="5" Datatype="Text" DataLength="50" Enabled="1" FieldClass="Normal" Name="Description" CaptionML="ENU=Description;RUS=Описание" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" NotBlank="0" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
        <Field ID="6" Datatype="Boolean" Enabled="1" FieldClass="Normal" Name="Calc. Pmt. Disc. on Cr. Memos" CaptionML="ENU=Calc. Pmt. Disc. on Cr. Memos;RUS=Расчет скидки оплаты по кредит-нотам" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" NotBlank="0" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
    </Fields>
    <Keys>
        <Key Enabled="1" Key="Field1" MaintainSQLIndex="1" MaintainSIFTIndex="1" Clustered="1"/>
    </Keys>
    <FieldGroups>
        <FieldGroup GroupID="1" GroupName="DropDown" GroupFields="Field1,Field5,Field2"/>
    </FieldGroups>
</MetaTable>

Предположим, это то, что вы хотите.

Код, записывающий его в файл в Nav, будет выглядеть так:

ObjectMetadata:Record(Object Metadata)
Code:BigText
File:File       
CodeStream:InStream     
FileStream:OutStream        

ObjectMetadata.INIT;

IF ObjectMetadata.GET(1,3) THEN
 BEGIN
  ObjectMetadata.CALCFIELDS(Metadata);
  File.CREATE('C:\temp\Code.txt');
  File.CREATEOUTSTREAM(FileStream);

  clear(codestream);
  ObjectMetadata."Metadata".CREATEINSTREAM(CodeStream);
  Code.READ(CodeStream);
  Code.WRITE(FileStream);

  file.close();
 END;

Теперь у вас есть варианты: попробуйте сделать то же самое в SQL/.Net (я не заинтересован в этом), или вы можете попросить своего разработчика Nav выполнить какое-то пакетное задание, которое будет периодически (или по запросу) обрабатывать все таблицы ". matadata и сохранить его во внешнюю таблицу / файл / что угодно, что вы можете получить доступ из SQL.

Answer 3

Приношу извинения за то, что поднял эту тему из мертвых, но я наконец-то заработал и подумал, что другим это может оказаться полезным:

      select
  CONVERT(
    varchar(max)
    , DECOMPRESS(
        CONVERT(varbinary(max), 0x1F8B0800000000000400) 
        + 
        CONVERT(varbinary(max), SUBSTRING([Metadata],5,8000))
      )
  )
from 
  [dbo].[Object Metadata] 
where 
  [Object Type] = 1 and [Object ID] = @objId

Это будет работать «из коробки», еслиDECOMPRESS(SQL Server 2016+) имел возможность игнорировать заголовок gzip, но, увы, этого не происходит. Я проверил несколько таблиц, и заголовок не изменился, поэтому я просто разместил его спереди.

Надеюсь, это поможет кому-то еще!

Answer 4

Почему бы не использовать веб-сервисы и не получать нужные данные, не делая ничего необычного?

Затем пользователи могут использовать PowerPivot для Excel и создавать свои собственные отчеты.

Вы также можете использовать надстройку Excel для NAV и просто экспортировать данные в Excel непосредственно из NAV, а затем обновлять их в Excel.

Answer 5

Написал это на основе приведенных выше данных Брэндона Робертса (спасибо, Брэндон!) Я получаю некоторые таблицы, которые не распаковываются должным образом, и я думаю, что это, возможно, проблема с заголовком.

С cte AS ( выберите om.[Тип объекта], om.[ID объекта], TRY_CAST(CONVERT(varchar(max), DECOMPRESS(CONVERT(varbinary(max), 0x1F8B0800000000000400)+CONVERT(varbinary(max), SUBSTRING(om) .[Метаданные],5,80000)) ) КАК XML) КАК [XML],CONVERT(varchar(max), DECOMPRESS(CONVERT(varbinary(max), 0x1F8B0800000000000400)+CONVERT(varbinary(max), SUBSTRING(om. [Метаданные],5,80000)) ) ) AS [Raw] из [dbo].[Метаданные объекта] om где om.[Тип объекта] = 1 )

SELECT TOP 1000 XML.value('declare namespace ns="urn:schemas-microsoft-com:dynamics:NAV:MetaObjects"; (/ns:MetaTable/@ID)[1]', 'nvarchar(100)') AS [Таблица №_],XML.value('объявить пространство имен ns="urn:schemas-microsoft-com:dynamics:NAV:MetaObjects"; (/ns:MetaTable/@Name)[1]', 'nvarchar(100)' ) AS [Имя таблицы], FieldData.value('@ID', 'int') AS [Номер поля_],FieldData.value('@Name', 'nvarchar(100)') AS [Имя поля], FieldData.value('@Datatype', 'nvarchar(100)') AS [Тип данных], FieldData.value('@DataLength', 'int') AS [Длина данных], FieldData.value('@OptionString', 'nvarchar (max)') AS [Option String], XML FROM cte CROSS APPLY XML.nodes('declare namespace ns="urn:schemas-microsoft-com:dynamics:NAV:MetaObjects"; /ns:MetaTable/ns:Fields/ ns:Field[@FieldClass="Normal" и @Enabled="1"]') AS Fields(FieldData);

Answer 6

Прежде всего спасибо за сценарий!

У меня также возникли некоторые проблемы с этим. Для меня в NAV2015 Таблица 27 и 5363 не будут работать... Я вижу, что он способен распаковывать метаданные, но похоже, что строка заканчивается в одной точке, что делает XML неполным и, как таковое, терпит неудачу...

Я решил это сейчас с помощью следующей настройки:

DECOMPRESS(CONVERT(varbinary(max), 0x1F8B0800000000000400) +CONVERT(varbinary(max), SUBSTRING(CONVERT(varbinary(max), om.[Metadata]),5,2147483647 )) ) AS [XML],