Solved

What does the 'sy_boolean;' / 'sy_guid;' / etc. comment in a database script mean?

  • 25 April 2021
  • 4 replies
  • 31 views

Good day,

I’ve been noticing some comments on database creation scripts such as 'sy_guid;' / 'si_autoincr;' / 'sy_boolean;', and have wondered what they mean/do.

think they might have something to do with easing things up when a DAC is generated via the Customization screen, but I’m probably completely wrong :joy::see_no_evil:

So… Does someone know what they mean, and more specifically, are there more ‘indicators’ like these that I can use?

Thanks in advance.

icon

Best answer by Gabriel Michaud 26 April 2021, 15:53

Hi @chris49,

I did some digging in the code, and here’s what I found.

These markers are added by Acumatica and used as metadata for Acumatica when reading data; it helps translate the native MySql data types to the internal types used by Acumatica and to ensure consistent behaviour between SQL Server and MySql.

A good example is the NoteID field -- in SQL Server, the uniqueidentifier type is used, but in MySql, it is a char(36). To ensure this gets parsed as a GUID the marker is used by Acumatica.

The different markers used can be found in the PX.DbServices.Points.MySql.MySqlMarkers class, contained in PX.DbServices.dll:

namespace PX.DbServices.Points.MySql
{
public static class MySqlMarkers
{
public static readonly bool UseBinary16ForGuid = false;
public const string SmallDateTime = "sy_smalldate";
public const string DateTime2 = "sy_datetime2";
public const string Boolean = "sy_boolean";
public const string Guid = "sy_guid";
public const string defaultMaskNoAccess = "df_Mask00";
public const string defaultMaskReadOnly = "df_MaskAA";
public const string defaultMaskReadWrite = "df_MaskFF";
public const string SpecialIndex = "AUTOINCR_Special";
public const string TriggerStart = "TriggerStart";
public const string IndexForForeignKey = "si_forFK";
public const string IndexForUpdate = "si_tempUpd";
public const string IndexForIdentity = "si_autoincr";
public const string GuidBeforeTrigger = "'00112233-4455-6677-8899-AABBCCDDEEFF'";
}
}

 

View original

4 replies

Userlevel 6
Badge +6

Hi Chris,

Could you attach a screenshot of the place where you see this?

 

Hi @Gabriel Michaud ,

Sure thing, it comes from the MySQL table creation syntax, e.g. here is the Account table’s syntax:

 

Userlevel 6
Badge +6

Hi @chris49,

I did some digging in the code, and here’s what I found.

These markers are added by Acumatica and used as metadata for Acumatica when reading data; it helps translate the native MySql data types to the internal types used by Acumatica and to ensure consistent behaviour between SQL Server and MySql.

A good example is the NoteID field -- in SQL Server, the uniqueidentifier type is used, but in MySql, it is a char(36). To ensure this gets parsed as a GUID the marker is used by Acumatica.

The different markers used can be found in the PX.DbServices.Points.MySql.MySqlMarkers class, contained in PX.DbServices.dll:

namespace PX.DbServices.Points.MySql
{
public static class MySqlMarkers
{
public static readonly bool UseBinary16ForGuid = false;
public const string SmallDateTime = "sy_smalldate";
public const string DateTime2 = "sy_datetime2";
public const string Boolean = "sy_boolean";
public const string Guid = "sy_guid";
public const string defaultMaskNoAccess = "df_Mask00";
public const string defaultMaskReadOnly = "df_MaskAA";
public const string defaultMaskReadWrite = "df_MaskFF";
public const string SpecialIndex = "AUTOINCR_Special";
public const string TriggerStart = "TriggerStart";
public const string IndexForForeignKey = "si_forFK";
public const string IndexForUpdate = "si_tempUpd";
public const string IndexForIdentity = "si_autoincr";
public const string GuidBeforeTrigger = "'00112233-4455-6677-8899-AABBCCDDEEFF'";
}
}

 

Hi @Gabriel Michaud ,

Thank you soooo much! This is really good to know. Thank you for digging around in the code, I tried but failed.

Again, thank you!

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2020  Acumatica, Inc. All rights reserved