The SysDA framework

In this blog I am going to talk about the SysDA (System Data Access) features, what they can do, and what they cannot. There is a good description of the SysDA classes here: Access data by using the SysDa classes - Finance & Operations | Dynamics 365 | Microsoft Docs. These classes used to be called the VEDAS,  (an abbreviation for Very Extensible Data Access Statements), in tribute to my Indian colleagues who recognize the reference to the ancient Hindu scriptures. The classes are designed to provide data access statements (select, while select, delete, insert and update) that are built at runtime.

Introduction

When the X++ compiler compiles the data access statements, it generates calls to an API that in turn instructs that data access layer in the kernel to generate T/SQL code to access the designated records. Let me illustrate with a simple example. Take the simple X++ code:

        Batch b;

        Select firstonly10 info, priority from b
        Where b.priority > 1;

 (Why did I use the Batch table in this example? Because it is much easier than the PurchPurchaseRequisitionExpenditureReviewerFinancialDimensionConfigurationStaging table)

The compiler will generate this code (as decompiled with the excellent dnspy tool (dnSpy/dnSpy: .NET debugger and assembly editor (github.com)))

                Batch b = new Batch();
                b.Find(b.__tablehandle());

                FieldList fieldList = new FieldList();

                int _k_Info_ID = Batch.__k_Info_ID;
                fieldList.Add(_k_Info_ID, 0);

                int _k_Priority_ID = Batch.__k_Priority_ID;
                fieldList.Add(_k_Priority_ID, 0);

                b.AggregationList(fieldList);
                b.FirstOnly10();
                Batch table = b;
                PredefinedFunctions.Where(PredefinedFunctions.newBinNode(PredefinedFunctions.newFieldExpr(table, Batch.__k_Priority_ID), new valueNode(1), 20), b);
                b.EndFind();

The code above builds an instance of the Batch table (called b), by creating a field list, adding the info and priority fields, then setting the hint (firstonly10 to indicating that only 10 records should be fetched). Finally, the where clause is built by adding a tree that describes the expression.

Now, obviously the lower layers that generate the T/SQL code for the database to execute does not really care where this code came from: The runtime will go ahead and build the necessary structures behind the scenes and then traverse them to generate the T/SQL code required. The simple idea lies behind the SysDA APIs: Provide a thin layer of veneer over these low-level API calls to make them available to X++ developers without too much trouble. As you will see below, the SysDA APIs build a tree structure, and it will make calls like the ones shown above when the data access statement is executed.

Let us consider some examples, and how you would write it with the SysDA classes. This X++ code

select IntField, StrField from bt
exists join IntField from jt
where jt.IntField == bt.IntField;

Can be generated with this SysDA code:

SysDaQueryObject qe = new SysDaQueryObject(bt);

var s = qe.projection()
   .add(fieldStr(CQTestBaseTable, IntField))
   .add(fieldStr(CQTestBaseTable, StrField));

SysDaQueryObject joinedQuery = new SysDaQueryObject(jt);
joinedQuery.projection()
   .add(fieldStr(CQTestJoinTable, IntField));

joinedQuery.WhereClause(
   new SysDaEqualsExpression(
      new SysDaFieldExpression(bt, fieldStr(CQTestBaseTable, IntField)),
      new SysDaFieldExpression(jt, fieldStr(CQTestJoinTable, IntField))));

qe.joinClause(SysDaJoinKind::ExistsJoin, joinedquery);

var so = new SysDaSearchObject(qe);
var ss = new SysDaSearchStatement();

while (ss.findNext(so)) // Traverse over the records.
{
    // use the bt and jt buffers
}


Here is an insert_recordset statement:

Table1 source;
Table2 target;

insert_recordset target (InfField, StrField)
    select id, String
    from source 
    where source.Id >= 2

encoded in SysDA:

var io = new SysDaInsertObject(target);
io.fields()
    .add(fieldStr(Table1, IntField))
    .add(fieldStr(Table1, StrField));

var qe = new SysDaQueryObject(source);
var s1 = qe.projection()
    .add(fieldStr(Table2, Id))
    .add(fieldStr(Table2, String));

qe.WhereClause(new SysDaGreaterThanOrEqualsExpression(
    new SysDaFieldExpression(source, fieldStr(Table2, Id)),
    new SysDaValueExpression(2)));
io.query(qe);

var istmt = new SysDaInsertStatement();

ttsbegin;
istmt.insert(io);
ttsabort;

The update statement:

update_recordset base
setting StrField = 'Updated'
join IntField from joined
where base.IntField == joined.IntField && (joined.IntField == 2);

would look something like this:

Table1 baseTable;
Table2 joinedTable;

var uo = new SysDaUpdateObject(baseTable);
uo.settingClause()
    .add(fieldStr(Table1, StrField), new SysDaValueExpression('Banana'));

SysDaQueryObject qe = new SysDaQueryObject(joinedTable);
qe.projection()
    .add(fieldStr(Table2, IntField));

qe.whereClause(
    new SysDaAndExpression(
        new SysDaEqualsExpression(
            new SysDaFieldExpression(baseTable, fieldStr(Table2, IntField)),
            new SysDaFieldExpression(joinedTable, fieldStr(Table2, IntField))),
        new SysDaEqualsExpression(
            new SysDaFieldExpression(joinedTable, fieldStr(Table2, IntField)),
            new SysDaValueExpression(2))));

uo.joinClause(SysDaJoinKind::InnerJoin, qe);

var us = new SysDaUpdateStatement();

ttsbegin;
us.update(uo);
ttsabort;

The code for find statements and delete statements should be easy after this. 

Using the SysDA classes has a few advantages:

1. The layer is very thin, so there is virtually no runtime overhead. This is not true if you compare with using QueryRun and its friends to accomplish the same thing.
2. The API can be used as an alternative to using the AX LINQ provider to access data from C#.
3. It is extensible: You can build the structure of your query and add to it later as needed. This is a characteristic it has in common with LINQ.
4. It is easy to innovate in this layer: Any time a new feature becomes available in the data access layer, it is easy to leverage in the SysDA classes in the application platform layer. You will probably see things coming here sooner than in the compiler.
5. The abstractions used are not tied to any particular database engine. You could easily build a provider that interprets the structure built with the SysDA API to create queries in other relational databases. This is not something we have leveraged, but it was part of the design.

It has a few disadvantages too:

1. You must be careful how you use it. When you write X++ select statements etc. the X++ compiler will point out your mistakes for you, there is no error detection anywhere in this layer. This is by design for now, but perhaps we will add a few more tests to cover the most egregious error situations later. Make sure you always you the compile-time functions (like tableStr, fieldStr etc) to designate fields and tables – Then at least the compiler will tell you if you are referring to tables and fields that do not exist.
2. The code that builds complex queries can get quite verbose, but then not as verbose as the corresponding code using the QueryBuild classes.
3. They map onto the capabilities of the data layer. Therefore, it cannot do anything the datalayer cannot do.

 I hope this was helpful in understanding what the SysDA. In short: It is great for extensible data statements, but it cannot do more than the underlying data layer.