Data type-hinting is the means by which SchemafreeSQL can determine the data types of your attributes at run-time.
Data type-hints are specified with the single character (the type) followed by a colon (e.g. 's:' which stands for 'string').
Within SFSQL, a leading dollar sign '$' before the type-hint (e.g. 'SELECT $s:name...') differentiates a SchemafreeSQL attribute from other SQL grammar.
Within a query command, data type-hint prefixes MUST be used.
With a modify command, data type-hint prefixes MAY be used on data types that can be auto-interpreted from their JSON counterpart types, but MUST be used when a data type is SFSQL only (i.e. datetime, time, or blob).
| SFSQL Type | Hint | JSON Type | Description | Size Limit |
| string | s | string | UTF-8 string | 4GB (practically limited by max network packet size) |
| integer | i | number | 64-bit integer | -9223372036854775807 to 9223372036854775807 |
| float | f | number | 64 bit floating-point | 1E-301 to 1E+301 |
| number | n | number | large integer or decimal | up to 50 digits to the left and 15 digits to the right of decimal. Non-rounding |
| boolean | b | boolean | boolean value | |
| datetime | d | date+time value | ||
| time | t | time | ||
| blob | l | binary large object | 4GB (practically limited by max network packet size |
|
| object | o | object | name-value collection |
UTF-8 string
type-hint: s
Stores JSON string types
On 'modify' when type hint is not supplied, a JSON string will be auto-interpreted as a SFSQL string
Example within 'modify': explicitly hinted: {"s:varname": "hello"}
Example within 'modify': auto-interpreted: {"varname": "hello"}
Example within 'query': WHERE $s:varname="hello"
64-bit integer
type-hint: i
Stores JSON number types (for integer values from -9223372036854775807 to 9223372036854775807)
On 'modify' when type hint is not supplied, a JSON number will be auto-interpreted as a SFSQL integer if the number falls within range
Example within 'modify': explicitly hinted: {"i:varname": 123}
Example within 'modify': auto-interpreted: {"varname": 123}
Example within 'query': WHERE $i:varname=123
NOTE 1: reliance on auto-interpretation is not recommended for inconsistent numbers
64-bit floating-point number
type-hint: f
Stores JSON number types (for values that contain a decimal or an exponent)
NOTE 1: reliance on auto-interpretation is not recommended for inconsistent numbers
Decimal number up to 50 digits to the left and 15 digits to the right of the decimal. Non-rounding (as compared to float)
type-hint: n
Stores JSON number types (for integer or decimal values from -99999999999999999999999999999999999999999999999999.999999999999999 to 99999999999999999999999999999999999999999999999999.999999999999999)
On 'modify' when type hint is not supplied, a JSON number will be auto-interpreted as a SFSQL number if it's an integer and outside allowed range for the 'integer' SFSQL type (less than -9223372036854775807 or greater than 9223372036854775807) but within allowed range for SFSQL number type. Use 'n' hint to specifically store a number as a SFSQL number
NOTE 1: reliance on auto-interpretation is not recommended for inconsistent numbers
Boolean value
type-hint: b
Accepts true/false or 1/0 on modify.
Stored as 1/0 in database.
Requires 1/0 comparison on database queries.
Stores JSON boolean types
On 'modify' when type hint is not supplied, a JSON boolean (true/false) will be auto-interpreted as a SFSQL boolean
Example within 'modify': explicitly hinted:{"b:varname": true} OR {"b:varname": 1}
Example within 'modify': auto-interpreted: {"varname": true}
Example within 'query': WHERE $b:varname=1
Date w/time. (see DateTime Expressions/Shortcuts)
type-hint: d
On 'modify': Requires use of 'd' hint
type-hint: l (lowercase L)
value is stored as a binary
value is set and returned via hex-encoded string
On 'modify': Requires use of 'l' (lowercase 'L') hint
Object-type under which a set of named values of any types may reside
type-hint: o
Stores JSON Object / Collection types
On 'modify': Auto-interpreted from JSON object without need of 'o' type hint
Example within 'modify': explicitly hinted:{"o:company": {"name":"ABC Co", "phone":"111-111-1111"}}
Example within 'modify': auto-interpreted: {"company": {"name":"ABC Co", "phone":"111-111-1111"}}
NOTE 1: it is not recommended to rely on auto-interpretation for attributes whose values may auto-interpret differently (e.g. sometimes looks like an int : 123, sometimes looks like a float: 123E10). This would result in the creation of two different attributes under the same parent, each attribute having the same name but a different type.