Data Types

XINA has a fixed set of data types which apply to attributes and fields. They are intended to provide consistent behavior across MySQL, Java, and JavaScript data types.

Numeric Types

Type	Java	MySQL	JavaScript	Notes
`int(1)`	`byte`	`tinyint`	`number`	signed 1 byte integer, -2⁷ to 2⁷-1
`int(2)`	`short`	`smallint`	`number`	signed 2 byte integer, -2¹⁵ to 2¹⁵-1
`int(4)`	`int`	`int`	`number`	signed 4 byte integer, -2³¹ to 2³¹-1
`int(8)`	`long`	`bigint`	`number`	signed 8 byte integer, -2⁶³ to 2⁶³-1 ⚠️
`float(4)`	`float`	`float`	`number`	IEEE 754 4 byte floating point
`float(8)`	`double`	`double`	`number`	IEEE 754 8 byte floating point
`boolean`	`boolean`	`tinyint`	`boolean`	MySQL treats `0` as `false`, non-zero as `true`

⚠️ JavaScript number is 8 byte float, so only -2⁵³ to 2⁵³-1 is stored with exact precision

Character Types

Character data types offer two encoding options:

UTF-8 - default encoding, variable length, 1 to 4 bytes per character
ASCII - subset of UTF-8, fixed length, 1 byte per character

Two SQL types:

char(n) - data stored in the table, fastest search and index, uses fixed amount of space per row (n * max_bytes_per_character)
varchar(n) - data stored in the table, fast search and index, uses variable amount of space per row (up to n * max_bytes_per_character)
text - data stored outside the table, slower search and index, uses only as much space as needed

Two general types:

string - text is normalized before insertion
- leading and trailing whitespace is trimmed
- all internal whitespace is reduced to a single space character
text - text is inserted as provided

Note, all string operations are case-insensitive by default. This can be overridden with the collate expression by specifying a binary collation.

Type	Java	MySQL	JavaScript	Notes
`utf8string(n)`	`string`	`char(n)`	`string`	n up to 128, uses `n*4` bytes, normalized
`utf8vstring(n)`	`string`	`varchar(n)`	`string`	n up to 128, uses up to `n*4` bytes, normalized
`utf8string`	`string`	`mediumtext`	`string`	up to 2²⁴ bytes, normalized
`utf8text`	`string`	`mediumtext`	`string`	up to 2²⁴ bytes, not normalized
`asciistring(n)`	`string`	`char(n)`	`string`	n up to 256, uses n bytes, normalized
`asciivstring(n)`	`string`	`varchar(n)`	`string`	n up to 256, uses up to n bytes, normalized
`asciistring`	`string`	`mediumtext`	`string`	up to 2²⁴ bytes, normalized
`asciitext`	`string`	`mediumtext`	`string`	up to 2²⁴ bytes, not normalized

Temporal Types

Temporal data types store time data. There are two categories of temporal types:

instants - identify specific moment in time, independent of time zone
- stored numerically in the database in milliseconds
- datetime and date use Unix epoch
- datetime and date comparable in database
- date + time = datetime
- typically displayed in local time zone in front-end applications
timestamps - identify specific formatted time without time zone consideration (thus local)
- stored as ISO 8601 formatted string in database
- localdate and localdatetime comparable in database
- CONCAT(localdate, 'T', localtime) = localdatetime

Type	Java	MySQL	JavaScript	Notes
`datetime`	`DateTime`	`bigint`	`date`	instant with millisecond precision, as Unix time
`date`	`XDate`	`bigint`	`date`	instant at start of date UTC, as Unix time
`time`	`LocalTime`	`int`	`number`	length of time up to 23:59:59.999, as millisecond count
`localdatetime`	`LocalDateTime`	`char(24)`	`string`	full timestamp without timezone, stored as `string`
`localdate`	`LocalDate`	`char(10)`	`string`	date without timezone, stored as `string`
`localtime`	`LocalTime`	`char(12)`	`string`	length of time up to 23:59:59.999, as `string`

JSON Types

JSON data types store JSON data directly in the database.

Type	Java	MySQL	JavaScript
`json`	`JsonValue`	`json`	`*`
`jsonarray`	`JsonArray`	`json`	`array`
`jsonobject`	`JsonObject`	`json`	`object`

Enum Types

Enum types map a series of discrete numeric integer values to text names. Though additional values may be added in the future, existing values will not change names or IDs.

`notification_level`

ID	Name	Notes
`0`	`none`	default level, no associated formatting
`1`	`success`	green
`2`	`info`	cyan
`3`	`notice`	yellow
`4`	`warning`	red
`5`	`primary`	blue, elevated over `none`
`6`	`secondary`	grey, below `none`

`notification_type`

ID	Name	Notes
`0`	`post`
`1`	`task`
`2`	`request`	request received
`3`	`response`	response to request received

`post_level`

ID	Name	Notes
`0`	`none`	default level, no associated formatting
`1`	`success`	green
`2`	`info`	cyan
`3`	`notice`	yellow
`4`	`warning`	red
`5`	`primary`	blue, elevated over `none`
`6`	`secondary`	grey, below `none`

Revision #7
Created 9 June 2022 15:57:28 by Nick Dobson
Updated 13 May 2024 16:41:38 by Nick Dobson