Not A Document Store

Collections Documents or Objects

Cloud SFSQL has been called a document store.  We are here to tell you it is not, but we understand why this label is sometimes applied.  Cloud SFSQL implements  a simple yet power HTTPs API where JSON document are posted to an endpoint. Sounds like  a document store .  Yes Cloud SFSQL accepts JSON documents as input but what we do with them is nothing like a document store. Lets take a closer look.

Here is  a "thing"  "document" with embedded sub  "documents" that we send to Cloud SFSQL. The "modify" command is how we edit and create data and structure. We also have a show attributes command and a query in there. The commands are highlighted in red.

[
 {
  "modify": {
   "data": {
    "things": {
     "cameras": {
      "camera": [
       {
        "i:id": "1",
        "name": "FinePix Pro2 3D Camera",
        "code": "3DcAM01",
        "n:price": 300.0
       },
       {
        "i:id": "2",
        "name": "FinePix Pro5 3D Camera",
        "code": "3DcAM05",
        "n:price": 800.0
       }
      ]
     },
     "balls": {
      "ball": [
       {
        "i:id": "1",
        "material": "rubber",
        "n:weighlb": 1.5
       },
       {
        "i:id": "2",
        "material": "metal",
        "n:weighlb": 10
       }
      ]
     }
    }
   }
  }
 },
 {
  "showattrs": {}
 },
 {
  "query": {
   "sfsql": "SELECT $o:.cameras.oid(),$o:.cameras.poid(),$o:.cameras.camera.oid(),$o:.cameras.camera.poid(),$s:.cameras.camera.name,$n:.cameras.camera.price where $i:.cameras.camera.id=1"
  }
 }
]

Below are the results. The "showattrs" command returns the current structure of the data and the query returned the selected attributes of  a single "camera" embedded "document" including the "camera" Object Id, "camera" Parent Object, "cameras" Object Id, and "cameras" Parent Object Id. The "camera" Parent Object Id and the "cameras" Object Id are the same because the "cameras" Object is the parent of the "camera" Object. A global select was performed, instead of selecting the full path to the attributes  the select will match on the abbreviated attribute path.

Results of "showattrs" 
 {
    "data" : [
      "o:things",
      "o:things.balls",
      "o:things.balls.ball",
      "i:things.balls.ball.id",
      "s:things.balls.ball.material",
      "n:things.balls.ball.weighlb",
      "o:things.cameras",
      "o:things.cameras.camera",
      "s:things.cameras.camera.code",
      "i:things.cameras.camera.id",
      "s:things.cameras.camera.name",
      "n:things.cameras.camera.price"
    ]
Results of "query" 
    "data" : [
      {
        "o:cameras.camera.oid()" : 51,
        "o:cameras.poid()" : 49,
        "s:cameras.camera.name" : "FinePix Pro2 3D Camera",
        "n:cameras.camera.price" : 300.00,
        "o:cameras.camera.poid()" : 50,
        "o:cameras.oid()" : 50
      }
    ]

Hmm still  behaves like a document store. Lets add some more structure

[
 {
  "modify": {
   "data": {
    "Inventory": {
     "electronics": {
      "cameras": {
       "#ref": "SELECT $o:.camera.poid() WHERE $i:.camera.id=1"
      }
     },
     "toys": {
      "balls": {
       "#ref": "SELECT $o:.ball.poid() WHERE $i:.ball.id=1"
      }
     }
    }
   }
  }
 },
 {
  "showattrs": {}
 },
 {
  "query": {
   "sfsql": "SELECT $o:.cameras.oid(),$o:.cameras.poid(),$o:.cameras.camera.oid(),$o:.cameras.camera.poid(),$s:.cameras.camera.name,$n:.cameras.camera.price where $i:.cameras.camera.id=1"
  }
 }
]

What we did here is add a new "document", Inventory with two sub "documents" electronics and toys which each contain children objects cameras and balls referencing the parent objects of a camera and ball object respectfully which would be the cameras and balls objects. The select statement in the "ref" command  finds the Object ID. This only runs once when the modify is command is executed  The reference Object is then mapped to its new parent. Below is the new structure of our database

"data" : [
      "o:Inventory",
      "o:Inventory.electronics",
      "o:Inventory.electronics.cameras",
      "o:Inventory.electronics.cameras.camera",
      "s:Inventory.electronics.cameras.camera.code",
      "i:Inventory.electronics.cameras.camera.id",
      "s:Inventory.electronics.cameras.camera.name",
      "n:Inventory.electronics.cameras.camera.price",
      "o:Inventory.toys",
      "o:Inventory.toys.balls",
      "o:Inventory.toys.balls.ball",
      "i:Inventory.toys.balls.ball.id",
      "s:Inventory.toys.balls.ball.material",
      "n:Inventory.toys.balls.ball.weighlb",
      "o:things",
      "o:things.balls",
      "o:things.balls.ball",
      "i:things.balls.ball.id",
      "s:things.balls.ball.material",
      "n:things.balls.ball.weighlb",
      "o:things.cameras",
      "o:things.cameras.camera",
      "s:things.cameras.camera.code",
      "i:things.cameras.camera.id",
      "s:things.cameras.camera.name",
      "n:things.cameras.camera.price"
    ]

The "cameras" and "balls" Objects and all of their children Objects are now also under the electronics and toys Objects, now lets run that that query again

{
  "query": {
   "sfsql": "SELECT $o:.cameras.oid(),$o:.cameras.poid(),$o:.cameras.camera.oid(),$o:.cameras.camera.poid(),$s:.cameras.camera.name,$n:.cameras.camera.price where $i:.cameras.camera.id=1"
  }
 }
  Results of Query
    "data" : [
      {
        "o:cameras.camera.oid()" : 51,
        "o:cameras.poid()" : 49,
        "s:cameras.camera.name" : "FinePix Pro2 3D Camera",
      "n:cameras.camera.price" : 300.00,
        "o:cameras.camera.poid()" : 50,
        "o:cameras.oid()" : 50
      },
      {
        "o:cameras.camera.oid()" : 51,
        "o:cameras.poid()" : 57,
        "s:cameras.camera.name" : "FinePix Pro2 3D Camera",
      "n:cameras.camera.price" : 300.00,
        "o:cameras.camera.poid()" : 50,
        "o:cameras.oid()" : 50
      }
    ]

The global select as expected  returned 2 matches.  The "cameras" object (oid=50 ) has two references to it, so two "cameras" (oid=50) are returned in the query. Two paths are now available to the same objects and to the entire object graph under the referenced object. Notice the Parent ID of the "cameras" object are different poid=49 and poid=57, these are the "things" and "electronics" objects. Every Object has at least one reference, in other words when you create an Object you are creating a reference to an object.

Lets run two more queries this time being more specific with the paths to the selected attributes in the select statement

 {
  "query": {
   "sfsql": "SELECT $o:.things.cameras.oid() where $i:.things.camera.id =1"
  }
 },
 {
  "query": {
   "sfsql": "SELECT $o:.electronics.cameras.oid() where $i:.electronics.camera.id =1"
  }
 }
Results query
{
    "data" : [
      {
        "o:things.cameras.oid()" : 50
      }
    ],
    "success" : 1,
    "cmdname" : "query"
  },
  {
    "data" : [
      {
       "o:electronics.camera.oid()" : 50
      }
    ],
    "success" : 1,
    "cmdname" : "query"
  }

Each query returned the same object (oid=50)   but with a unique path to the object.  An object in Cloud SFSQL can have multiple paths to it, it can also have multiple names, the referencing object does not have to have the same name  as the object being referenced, resulting in multiple paths and multiple names for the same object..  Now lets to something drastic, delete the "things" "document" and run show attributes

[
{
  "delete": {
   "objfilter": "SELECT $o:things.attrset('delete')"
  }
 },
 {
  "purge": {}
 },
 {
  "showattrs": {}
 }
]

Results
[
  {
    "data" : [
      "o:Inventory",
      "o:Inventory.electronics",
      "o:Inventory.electronics.cameras",
      "o:Inventory.electronics.cameras.camera",
      "s:Inventory.electronics.cameras.camera.code",
      "i:Inventory.electronics.cameras.camera.id",
      "s:Inventory.electronics.cameras.camera.name",
      "n:Inventory.electronics.cameras.camera.price",
      "o:Inventory.toys",
      "o:Inventory.toys.balls",
      "o:Inventory.toys.balls.ball",
      "i:Inventory.toys.balls.ball.id",
      "s:Inventory.toys.balls.ball.material",
      "n:Inventory.toys.balls.ball.weighlb",
    ],
    "success" : 1,
    "cmdname" : "showattrs"
  }
]

"Things" are gone but the new structure we created remains now lets run those queries again, the ones with the specific paths in them. here are the results

[
  {
    "data" : [ ],
    "success" : 1,
    "cmdname" : "query"
  },
  {
    "data" : [
      {
        "o:electronics.camera.oid()" : 50,
        "s:electronics.camera.name" : "FinePix Pro2 3D Camera"
      }
    ],
    "success" : 1,
    "cmdname" : "query"
  }
]

As we expected the first query looking for "thing.cameras" returned an empty set the second query looking for "electronics.cameras" returned the same result.  OK now we know for sure that this is not a document store but has some document store features.

With Cloud SFSQL there is no "defining" structure, there is data which has structure. Schemaless does not mean "structure less" it means flexibility and freedom to change your data's structure and also the ability to create, maintain, and query different structures for the same data like we have shown in here. (Philosophy bog post).

We invite you to try Cloud SFSQL for free and your comments and  Feedback are much appreciated.