Built-in datatypes

Built-in types are provided out of the box in the platform.

Built-in datatypes

Built-in types include:

  • Numeric types:

    • ByteInteger:

      • 8 bytes signed

      • Range: -128 to 127

    • ShortInteger:

      • 16 bytes signed

      • Range: -32,768 to -32,767

    • Integer:

      • 32 bytes signed

      • Range: -232 to 232-1

    • LongInteger:

      • 64 bytes signed

      • Range: -264 to 264-1

    • Decimal:

      • Number with specified precision and scale

      • Precision range: 1 to 38

      • Scale range: -84 to 127

        Only decimal numbers containing up to 14 digits can be entered on authoring forms. This constraint does not apply to data published using the REST or SQL APIs.

  • Text types:

    • String:

      • Length less than 4,000 characters.

      • Stored in the database as VARCHAR2 (Oracle), VARCHAR (PostgreSQL), or NVARCHAR (SQL Server).

        To increase the maximum string length from 4,000 to 32,767 bytes, platform administrators can add com.semarchy.mdm.supportExtendedMaxStringSize to the xDM system properties.

        Once set to true, the com.semarchy.mdm.supportExtendedMaxStringSize property cannot be reverted to false.

        Oracle database administrators should also set the MAX_STRING_SIZE parameter to EXTENDED in the Oracle database settings.

        Once set to EXTENDED, the MAX_STRING_SIZE parameter cannot be set back to STANDARD.

    • LongText:

      • No size limit.

      • Translated in the database to a CLOB (Oracle), TEXT (PostgreSQL), or NVARCHAR(MAX) (SQL Server).

  • Date types:

    • Date:

      • Date without time or timezone.

      • Translates to DATE (PostgreSQL and SQL Server) or TIMESTAMP (Oracle). For Oracle, the time portion is automatically truncated by the certification process for date attributes.

    • Timestamp:

      • Date and time (to fractional digits of a second).

      • Translates to TIMESTAMP (Oracle and PostgreSQL) or DATETIME2 (SQL Server). For more information about dates, timestamps, and timezones, see the note below.

  • Binary types:

    • Binary:

      • Stores any type of binary content (image, document, video, etc.) with no size limit.

      • Translates to BLOB (Oracle), BYTEA (PostgreSQL), or VARBINARY (SQL Server) in the database.

  • Misc. types:

    • UUID:

      • 16 bytes global unique ID.

      • Translates to RAW (Oracle), UUID (PostgreSQL), and UNIQUEIDENTIFIER (SQL Server).

    • Boolean:

      • Translates to a CHAR(1) containing either 1 (true) or 0 (false) in Oracle, and to BOOLEAN and BIT in PostgreSQL and SQL Server, respectively.

Date, timestamp value, and timezone conversion

Date values do not store the time and are not subject to timezone conversions. However, timestamp values contain the date and time, and are therefore subject to timezone conversions.

  • Timestamps are stored in the repository and data locations according to the timezone of the application server.

  • About timestamps and dates used in applications:

    • Users view or author timestamps in the timezone defined in their user profile. These values are automatically converted to and from the application server timezone when accessed or stored in the database.

    • Users view and author timestamps and dates in the format defined in their user profile by default. Alternatively, they can follow the format specified by the application designer in a form or collection.

    • Importing and exporting timestamps are exceptions. Users import or export timestamps in the application server timezone, regardless of their profile timezone. This approach allows exporting timestamps in one timezone and reimporting them in a different timezone without altering the values.

  • About timestamps and dates in integration:

    • The REST API uses the ECMAScript standard format (YYYY-MM-DDTHH:mm:ss.sssZ) for timestamps. This format includes an explicit timezone component, and conversion is performed automatically.

    • The REST API uses the date format without a timezone in the ISO-8601 calendar system (YYYY-MM-DD) for dates (e.g., 2007-12-03).

    • Integration specialists using SQL to either consume timestamp data from the MDM hub or publish timestamp data to it should be aware that conversion may occur depending on their driver configuration. To avoid such conversions, it is recommended that the integration component be located in the same timezone as the application server.