Docs: Overview
Data Structures
  • Object Structures are created from JSON sent in a HTTP POST payload conforming to the SFSQL API which is also a properly formatted JSON doc. SFSQL does NOT store JSON Documents. It creates an Object Structure representation of the JSON in the SFSQL table space created at set up. To be clear, we could have created a REST-based API or even a proprietary protocol but instead we chose the ubiquitous JSON as our data interchange format.
  • Below is a JSON doc comprised of a Person Object containing three Attributes. The Person Object is an Attribute of The "Root" Object
    { "Person": { "firstName": "Jay", "lastName": "Johnson", "age":50 } }
  • SFSQL supports the following Attribute value Data Types: String, Integer, Float, Number, Boolean, Datetime, Blob and Object
  • SFSQL will model the appropriate Attribute structure with a slight enhancement. All Attributes are Elements of an "Array"
  • In this example the "Person" Object is an Element of a "Person" Array with a position of 0, having three children Attributes,"firstName","lastName", and "age", all Elements of Arrays "firstName","lastName", and "age" with Data Types string, string, and integer respectfully, with positions of 0. Auto Typing is preformed in the absence of Type Hinting
  • Below is the a JSON representation of the Structure SFSQL will create from the JSON above.
    { "Root": { "o:Person": [ { "s:firstName": ["Jay"], "s:lastName": ["Johnson"], "i:age": [50] } ] } }

    The Attribute Structure Created by SFSQL is
    { "data" : [ "o:Person", "i:Person.age", "s:Person.firstName", "s:Person.lastName" ] }
  • Attribute may contain mixed Data Types.
    { "Person": { "ID": [ "abc", 123, { "value": 123 } ] } }

    The Attribute Structure Created by SFSQL is
    { "data" : [ "o:Person", "o:Person.ID", "s:Person.ID", "i:Person.ID", "i:Person.ID.value" ] }
  • Complex nested JSON structures are supported however SFSQL currently does not support nested Arrays {"Array":["value1","value2",[1,2,3]]}.
Object Data Types
  • All Object have a unique Object Id "oid"
  • All Attributes have a Parent Object Id "poid" equal to the "oid" of its Parent Object.
  • SFSQL has a ROOT object with an "oid" of 0. Attributes created at the "Root" have a poid of 0
  • Objects are not typed. Each Object is its own "type".
  • An Array of Objects (same name, different position, unique oid) may have different Structures if one desires.
Object References
  • References may be created to Objects by OID.
  • Multiple "references" may be created to an Object.
  • Object Self Referencing is allowed.(causes issues with displaying Attribute Structures, so avoid for now)
  • Modifying a Referenced Object will modify all other References to that Object.
  • Deleting a Object Reference has no effect on any remaining Object Reference.
  • All Object References have the same Object Id.
  • References to an Object may have a different Name than the Referenced Object
  • Object References allows Graph Structures to be modeled (see GOT Graph Example).
Attribute Functions
  • SFSQL has a growing list of functions that can be called on Attributes.
    • attribute.pos() - returns an attribute's position
    • attribute.poid() - returns an attribute's Parent Object ID
    • attribute.oid() - returns an attribute's Object ID (only for Object data types)
    • attribute.attrset(ATTR_SET_ID) - returns a preset SFSQL-formatted list of Attributes capable of inclusion in the SELECT portion of a SFSQL query. Current possible value of ATTR_SET_ID is 'delete' which is used within the delete command's 'objfilter' parameter
Creating and Querying Attribute Structures
  • SFSQL provides an easy way to query these structures using a "SQL" like language.
  • Native Database functions are made available from the SFSQL API
  • Query results are "flattened" (attribute values only) and returned in three styles "NVP" (name value pair is default), "colnames" (column names returned in first row). and "Data Only". The returned data may be in "data-formats" of JSON(default) and CSV (returned within a valid json doc properly escaped, NVP not allowed).
  • Returning query results in the "Data Graph" is being worked on.
SFSQL Offerings
  • SFSQL is offered as a hosted solution - Cloud SFSQL Try it For Free
  • Self-Hosted solution coming soon.