Data types

Field types for FairCom DB

A field type is the data type of a field in a table. In a FairCom server, data is stored in fields, records, tables, and databases. A record contains one or more fields. A table contains zero or more records. Each field has a predefined field type.

A FairCom server provides strongly and flexibly typed fields that can be freely mixed into any table. Strongly typed fields make queries predictable and fast.

The most common strongly typed fields are NUMERIC, INTEGER, VARCHAR, LVARBINARY, BIT, DATE, TIME, and TIMESTAMP. The remaining strongly typed fields have specialty purposes.

A FairCom server also supports JSON fields, which may flexibly contain any JSON type.

A FairCom table may have up to 2500 fields, and zero or more of those fields may have a JSON type. Each field (or group of fields) may be indexed by one or more indexes, with a maximum of 500 indexed fields. One or more individual properties inside a JSON field may also be indexed.

JSON field types are case-insensitive strings, like they are in SQL. In the Table 1, “Field types” table, they are shown in uppercase for emphasis and style.

The data types listed are available in JSON DB API. All FairCom’s SQL types are supported. Each field in a table and each property in JSON must be one of the listed field types. Each field type may be defined as nullable or non-nullable. The JSON DB API provides response options that return numeric and binary field types as JSON strings.

JSON API SQL Tag JSON Description ISAM number ISAM name
"bigint" BIGINT "number" number or number in a string BIGINT is an 8-byte, signed integer number with a range of -9,223,372,036,854,770,000 to 9,223,372,036,854,770,000. 231 CT_INT8
"binary" BINARY "binary" string containing Hex or Base64 or array of byte integers BINARY is a fixed-length series of bytes with 0x00 padding. Its length is specified when the field is created. 128 CT_ARRAY
"bit" BIT "boolean" true for 1 and false for 0 BIT is a Boolean value of 0 or 1. 8 CT_BOOL
"char" CHAR "string" string CHAR is a fixed-length UTF-8 string with padding. Its fixed length and padding are specified when the field is created. 144 CT_FSTRING
"date" DATE "date" string containing ISO8601 date DATE is a specific day, month, and year stored as a 4-byte structure. 75 CT_DATE
"double" DOUBLE "number" number or number in a string DOUBLE is an 8-byte, IEEE 754, base-two floating point number. 103 CT_DFLOAT
"integer" INTEGER "number" number or number in a string INTEGER is a 4-byte, signed integer number with a range of -2,147,483,648 to 2,147,483,647. 51 CT_INT4
"json" JSON "json" object, array, string, number, true, false, or null JSON is a variable-length UTF-8 string that can contain any valid JSON value. Specify a maximum length to store the JSON as a VARCHAR or omit the length to store it as an LVARCHAR, which can be up to 2 GB in size. 259 CT_JSON
"lvarbinary" LVARBINARY "lvarbinary" string containing Hex or Base64 or array of byte integers LVARBINARY is a variable-length series of bytes with no specified maximum length. It has a maximum physical length of 2 GB. 170 CT_4STRING
"lvarchar" LVARCHAR "string" string LVARCHAR is a variable-length UTF-8 string with no specified maximum length. Its physical maximum length is 2 GB. 170 CT_4STRING
"money" MONEY "number" number or a number in a string MONEY is a 19-byte, signed, base-ten number with 32 digits of precision and a scale specified as 2 or 4. Its fixed number of digits and scale are specified when the field is created. 105 CT_NUMBER
"nchar" NCHAR "string" string NCHAR is a fixed-length UTF-16 string with padding. Its fixed length is specified when the field is created. 177 CT_FUNICODE
"number" NUMBER "number" number or number in a string NUMBER is a 19-byte, signed, fixed-point, base-ten number with 32 digits of precision and user-defined length and scale. Its fixed number of digits and scale are specified when the field is created. 105 CT_NUMBER
"nvarchar" NVARCHAR "string" string NVARCHAR is a variable-length UTF-16 string. Its maximum length is specified when the field is created. 193 CT_2UNICODE
"real" REAL "number" number or number in a string REAL is a 4-byte, IEEE 754, base-two floating point number. 91 CT_SFLOAT
"smallint" SMALLINT "number" number or number in a string SMALLINT is a 2-byte, signed integer number with a range of -32,768 to 32,767. 33 CT_INT2
"time" TIME "time" string containing ISO8601 time TIME is a specific millisecond in a day stored as a 4-byte structure. 258 CT_TIME_MS
"timestamp" TIMESTAMP "timestamp" string containing ISO8601 date-time TIMESTAMP is a DATE and TIME combined, stored as an 8-byte structure. 257 CT_TIMES_MS
"tinyint" TINYINT "number" number or number in a string TINYINT is a 1-byte, signed integer number with a range of -128 to 127. 16 CT_CHAR
"varbinary" VARBINARY "binary" string containing Hex or Base64 or array of byte integers VARBINARY is a variable-length series of bytes. Its maximum length is specified when the field is created. 162 CT_2STRING
"varchar" VARCHAR "string" string VARCHAR is a variable-length UTF-8 string. Its maximum length is specified when the field is created. 162 CT_2STRING
"variant" VARIANT "variant" variant object - flexible extensible VARIANT is a variable-length field that may contain any type of value, including user-defined types. CT_VARIANT JSON

The driver for each language has specific constants for field types:

 

Essential information

  • FairCom’s NUMBER and MONEY types allow values that can exceed the numeric limits of JavaScript and some JSON parsers. Thus, the JSON DB API provides the option to embed the numeric values of number types, such as INTEGER, NUMBER, and MONEY, in a JSON string. This ensures JavaScript and JSON parsers have no problems parsing JSON containing extra-large numbers.
  • The JSON DB API automatically converts CHAR fields to and from JSON's variable-length strings. This makes CHAR fields behave like variable-length strings. You can include spaces when you want to pad its value.
  • SQL pads fixed-length strings with the space character. If the number of characters in a JSON string is less than the fixed length, the database adds a space character to the end of the string until it fills the fixed length. The following SQL query demonstrates this padding by returning quotes surrounding the value of a fixed-length field named "mychar".
    SELECT '"' || mychar || '"' from mytable;
  • All variable-length fields must be assigned a maximum length. FairCom's indexes and SQL use this length.
    • If a value exceeds the length, the operation returns an error.
    • You can set a VARCHAR string to its maximum possible length of 65,500 bytes as long as you do not index the field. Notice that the maximum length is bytes, not characters, because a UTF-8 character may contain multiple bytes.
  • FairCom indexes the maximum length of strings; thus, do not make a string larger than necessary.
    • The index copies a variable-length string into a fixed-length buffer that is the maximum length of the string. The buffer is initialized with spaces. Then, the index uses run-length encoding to compress repeated characters.
    • This can cause side effects when a string contains trailing spaces:
      • Keys are identical when the only difference is the number of trailing spaces.
      • The number of trailing spaces has no impact on key comparisons.
      • A key being added to a unique index will fail if the only difference between it and an existing key is the number of trailing spaces.
  • In the JSON DB API, all binary values are represented as a string encoded as a base64 or hexadecimal value.
    • When receiving a property containing a binary value, the JSON DB API converts the base64 or hex encoded value into a binary value and stores it in the field as binary data.
    • When returning data, it removes the binary value from a field, encodes it as a base64 or hex string, and puts it in the JSON property.
    • Encoding a binary value into a variable-length JSON string creates a string that is typically 1.3 to 2 times larger than the field's binary length. This is expected and is not an issue for JSON or the JSON DB API.