Monday, 11 September 2017

Database Mapped vs Virtual Fields

Hi All,

Today want to share with you how Database vs Virtual fields are working in Acumatica.

You may notice that some of the fields are linked to DB, some of them not.The difference is in the Attributes are used on it - PXDB<Type>Attribute is mapped on database field, PX<Type>Attribute is not. So from user standpoint it is just prefix "DB" or "".

In Acumatica there are many fields are mapped to database and most of them have a clone, that is not mapped. Here you can see list of most common attributes.
Description
Database Mapped Field
Virtual Field
String Value
PXDBStringAttribute
PXStringAttribute
Byte Value
PXDBByteAttribute
PXByteAttribute
Short Value
PXDBShortAttribute
PXShortAttribute
Uniqueidentifier Value
PXDBGuidAttribute
PXGuidAttribute
Bool Value
PXDBBoolAttribute
PXBoolAttribute
Integer Value
PXDBIntAttribute
PXIntAttribute
Long (Int64) Value
PXDBLongAttribute
PXLongAttribute
Double Value
PXDBDoubleAttribute
PXDoubleAttribute
Decimal Value
PXDBDecimalAttribute
PXDecimalAttribute
Date Value
PXDBDateAttribute
PXDateAttribute
Timestamp Value
PXDBTimestampAttribute
<NONE>
Integer Identity Value
PXDBIdentityAttribute
<NONE>
Long (Int64) Identity Value
PXDBLongIdentityAttribute
<NONE>
Binary Field
PXDBBinaryAttribute
<NONE>

How does that work?



Reading Data
You know that all attributes are subscribed for Acumatica Row and Field level events. There is one event that is responsible for generation SQL statement - CommandPreparing.
So basically all DB fields are inherited from PXDBFieldAttribute that implements CommandPreparing event;
public virtual void CommandPreparing(PXCache sender, PXCommandPreparingEventArgs e)
{
if (DatabaseFieldName != null)
{
e.BqlTable = _BqlTable;
string tableName = e.Table == null ? _BqlTable.Name : e.Table.Name;
e.FieldName = e.SqlDialect.quoteTableAndColumn(tableName, dbFieldName);
}
e.DataValue = e.Value;
e.IsRestriction = e.IsRestriction || _IsKey;
e.DataType = PXDbType.Bit;
e.DataLength = 1;
}
The main part here is providing FieldName and DataType, If field name is not populated, than Acumatica will automatically skip that DAC field from select statement.
For example if you have "[PXDBString] Field1", "[PXBool] Field2" and "[PXDBInt] Field3",, than select statement will looks like "Select Field1, Field3 from DAC".

So as you may understand virtual fields do not implement command preparing event handler at all.

For reading data, Acumatica uses RowSelecting event handler where SQL Data Reader still open and accessible.
public virtual void RowSelecting(PXCache sender, PXRowSelectingEventArgs e)
{
if (e.Row != null) {
object dbValue = e.Record.GetValue(e.Position);
sender.SetValue(e.Row, _FieldOrdinal, dbValue);
}
e.Position++;
}
As with CommandPreparing, all DB related fields implementing RowSelecting, but virtual fields skipping it.


Read/Write vs Read-Only Fields
Some of the fields just read data from database and never saves it back. Good example here is PXDBScalarAttribute, which is just sub-select. For sub-selects you cannot save anything, so your logic should be working only of reading.
To implement it you can use Operation property from PXCommandPreparingEventArgs. For example here you can see how PXDBScalarAttribute handling only select operations:
public override void CommandPreparing(PXCache sender, PXCommandPreparingEventArgs e)
{
if ((e.Operation & PXDBOperation.Command) == PXDBOperation.Select && sender.BqlSelect != null)
{
............
}
}

Custom SQL Command
You remember I told you about FieldName in PXCommandPreparingEventArgs? That is very important property, as it goes directly to SQL Query, so you can put there custom operations.
As for example see, how PXDBScalarAttribute using it for generation sub-selects.
public override void CommandPreparing(PXCache sender, PXCommandPreparingEventArgs e)
{
............
string fieldtext = BqlCommand.GetSingleField(field, sender.Graph, new List<Type>(), null, 
BqlCommand.FieldPlace.Select); 
 e.FieldName = BqlCommand.SubSelect + function + "(" + 
(String.IsNullOrEmpty(fieldtext) ? field.Name : fieldtext) + ") " + text + ")";b
...........
}
So you can add your own SQL commands on and that read data on CommandPreparing and that read it back on RowSelecting
Using that approach you can create own Database-related and Virtual fields.

Have a nice development!

No comments: