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.
The data types listed in Table 1, “Field types” 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.
Note
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.
| SQL field type | JSON DB API field type | Description | ISAM type number | ISAM type name | JSON type |
|---|---|---|---|---|---|
| BIGINT | "bigint" |
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 | integer or string |
| BINARY | "binary" |
BINARY is a fixed-length series of bytes with 0x00 padding. Its length is specified when the field is created. |
128 | CT_ARRAY | array of bytes or a string containing Base64 or Hex |
| BIT | "bit" |
BIT is a Boolean value of 0 or 1. |
8 | CT_BOOL |
false for 0 and true for 1 |
| CHAR | "char" |
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 | string |
| DATE | "date" |
DATE is a specific day, month, and year stored as a 4-byte structure. |
75 | CT_DATE | string |
|
DOUBLE
|
"float" |
DOUBLE is an 8-byte, IEEE 754, base-two floating point number. |
103 | CT_DFLOAT | number or string |
| INTEGER | "integer" |
INTEGER is a 4-byte, signed integer number with a range of -2,147,483,648 to 2,147,483,647. |
51 | CT_INT4 | integer or string |
| JSON | "json" |
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 | Any JSON value |
| LVARBINARY | "lvarbinary" |
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 | array of bytes or a string containing Base64 or Hex |
| LVARCHAR | "lvarchar" |
LVARCHAR is a variable-length UTF-8 string with no specified maximum length. Its physical maximum length is 2 GB. |
170 | CT_4STRING | string |
| MONEY | "money" |
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 | number or string |
| NCHAR | "nchar" |
NCHAR is a fixed-length UTF-16 string with padding. Its fixed length is specified when the field is created. |
177 | CT_FUNICODE | string |
|
NUMBER
|
"number" |
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 | number or string |
| NVARCHAR | "nvarchar" |
NVARCHAR is a variable-length UTF-16 string. Its maximum length is specified when the field is created. |
193 | CT_2UNICODE | string |
| REAL | "real" |
REAL is a 4-byte, IEEE 754, base-two floating point number. |
91 | CT_SFLOAT | number or string |
| SMALLINT | "smallint" |
SMALLINT is a 2-byte, signed integer number with a range of -32,768 to 32,767. |
33 | CT_INT2 | integer or string |
| TIME | "time" |
TIME is a specific millisecond in a day stored as a 4-byte structure. |
258 | CT_TIME_MS | string |
| TIMESTAMP | "timestamp" |
TIMESTAMP is a DATE and TIME combined, stored as an 8-byte structure. |
257 | CT_TIMES_MS | string |
| TINYINT | "tinyint" |
TINYINT is a 1-byte, signed integer number with a range of -128 to 127. |
16 | CT_CHAR | integer or string |
| VARBINARY | "varbinary" |
VARBINARY is a variable-length series of bytes. Its maximum length is specified when the field is created. |
162 | CT_2STRING | array of bytes or a string containing Base64 or Hex |
| VARCHAR | "varchar" |
VARCHAR is a variable-length UTF-8 string. Its maximum length is specified when the field is created. |
162 | CT_2STRING | string |
| VARIANT | "variant" |
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:
- C ISAM Driver
- C CTDB Driver
- C Driver Data Type Overview
- C Driver Unicode Types
- C Direct SQL Driver
- C++ ISAM Driver
- C# ISAM and CTDB Driver
- C# Data Type Overview
Essential information
- FairCom’s
NUMBERandMONEYtypes 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 asINTEGER,NUMBER, andMONEY, 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
VARCHARstring 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.
Note
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.