Less Code SFSQL SHOP Admin
Here is what we will do to create our “Less Code” Shop Admin
- Launch a new Cloud SFSQL Database and obtain credentials
- Create the SFSQL Payload consisting of
- A Modify Command with the following operations
- Create An array of suppliers
- Create An Array of products referencing created suppliers as manufacturers and creating manufacturers inline
- Append the manufacturers created in line to the array of suppliers
- Create An array of customers
- Create An array of orders with one order referencing a customer, we will add an email address to the referenced customer and in another order create a customer inline. Each order will contain at line items with a child item object referencing a product created in the same .
- Increment the "totalordered" attribute of the product by the quantity ordered
- Append the customer created in line to the customer array
- Create Scoped Parameters and Reflective Parameters, referenced within the Payload and the script file
- 4 queries: Suppliers, Products, Customers and Orders
- A Modify Command with the following operations
- Create a single script file which will post the JSON payload to the Cloud SFSQL DB and display the Shop Admin UI.
Lets take look at the payload that will be sent to Cloud SFSQL. We will construct it one section at a time then put it all together in the end.
Below we have the basic structure of our SHOP. It starts out with a root “shops” object which holds an array of “shop: objects. The shop object contains a “s:name” string attribute. The ”s" types the attribute as a sting. SFSQL will auto type attributes in the modify command in the absent of a type hint, but attributes must be typed in queries. The shops object has children objects supplier, product, customer, and order. With Cloud SFSQL everything is an array including primitive (non object) attributes. So even if you don't see [ ] which is JSON for array it's still an array, which means you can append values to it with out having to later declare it as an array. This structure and associated data is enclosed in a "modify" command.
[
{
"modify": {
"data": {
"shops": {
"shop": {
"s:name": "Less Code Shop",
"supplier": {},
"product": [],
"customer": [],
"o:order": []
}
}
}
}
}
]Lets add some initial data to our Shop
The structure and data are created in sequential order in real time so you can operate on and reference created objects within the same modify command.
Here we added a Sony supplier and 4 products. Two of the products reference the Sony supplier as a manufacturer. With Cloud SFSQL any object can reference any object even objects of different "types". This results in two references to the same object. In other words each referenced object now has two paths and two names but 1 object id (oid). E.G. o:shops.shop.supplier.oid() where id=sony and o:shops.shop.product.manufacturer.oid() where id = “sony” return the same Object ID
Payload scoped parameters are created for each modified object with the objects “oid” as its value !@result.oid@!. Payload scoped parameters are identified with two underscores preceding the name e.g “__sony”
Object references "#ref" are expecting an oid. For the camera manufacturer we referenced a scoped parameter "!@__sony@!" for the hard drive manufacturer we found the oid with a query "SELECT $o:.supplier.oid() WHERE $s:.supplier.id='sony'" To reference a parameter you enclose it name like so !@param_name@!. Notice the .supplier in the SELECT, instead of the full path to the supplier object the ‘.’ tells SFSQL to do a global search for all objects with name “supplier” matching the WHERE clause.
With the other two products we create the manufacturer object inline.
[
{
"modify": {
"data": {
"shops": {
"shop": {
"supplier": {
"__sony": "!@result.oid@!",
"name": "Sony Group Corporation",
"id": "sony"
},
"product": [
{
"__3DcAM01": "!@result.oid@!",
"name": "FinePix Pro2 3D Camera",
"code": "3DcAM01",
"image": "camera.jpg",
"n:price": 300.0,
"i:totalordered": 0,
"manufacturer": {
"#ref": "!@__sony@!"
}
},
{
"__USB02": "!@result.oid@!",
"name": "EXP Portable Hard Drive",
"code": "USB02",
"image": "external-hard-drive.jpg",
"n:price": 800.0,
"i:totalordered": 0,
"manufacturer": {
"#ref": "SELECT $o:.supplier.oid() WHERE $s:.supplier.id='sony'"
}
},
{
"__wristWear03": "!@result.oid@!",
"name": "Luxury Ultra thin Wrist Watch",
"code": "wristWear03",
"image": "watch.jpg",
"n:price": 100.0,
"i:totalordered": 0,
"manufacturer": {
"name": "TAG Heuer",
"id": "tag"
}
},
{
"__LPN45": "!@result.oid@!",
"name": "XP 1155 Intel Core Laptop",
"code": "LPN45",
"image": "laptop.jpg",
"n:price": 250.0,
"i:totalordered": 0,
"manufacturer": {
"name": "DELL Inc",
"id": "dell"
}
}
],
"customer": [],
"o:order": []
}
}
}
}
}
]Next we reference the two inline manufacturer objects under the suppler object array. We used the "modify" sub command "append" to add these objects to the supplier array. The JSON parser will bark at you if you have sibling objects with the same name. Remember we started with a supplier object and now we are adding another supplier object to the JSON, but this time we are "typing" it with "o:". This tells Cloud SFSQL it is an object. It's not necessary because Cloud SFSQL will auto type, but by adding the type hint the JSON parser does not throw an error because the sibling object names "supplier" and "o:supplier" are not the same, although Cloud SFSQL knows that they are the same object. It's a bit of a hack but it allows us to do it all in one modify command, remember we are showing off. Referencing objects cost you nothing, think of them as symbolic links, and objects can have a multitude of references so reference away.
"o:supplier": [
{
"#append": {}
},
{
"#ref": "!@__tag@!"
},
{
"#ref": "!@__dell@!"
}
]Ok so now lets add a customer. So far we have added data within the INV Object. The customer object will be out side the INV under the main SHOP object since customers are not a INV(inventory) thing. We used the "modify" sub command "set". If the object is found it is updated if not it is appended to the array.
"customer": {
"#set": {
"where": "$s:customer.id='c1111'"
},
"s:id": "c1111",
"s:first_name": "Larry",
"s:last_name": "Smith",
"o:address": {
"street": "5 Elmwood Avenue",
"city": "Rochester",
"state": "NY",
"zip": "14616"
}
},Now lets add some orders to this modify command. We added two orders each order has a id, datetime (notice the date data type), cust object , and a lineitem object array. Each lineitem object contains an item object, which references a product object, and a no, qty, and price . Notice "#pass": "v + 1" this tells SFSQL to increment the value of the “totalordered” attribute of the referenced product object by the qty ordered.
We attached a price attribute to the lineitem because prices of a products may change and we need a record of the price at the time of the sale. Changes to an object are reflect in all references to that object.
For the customer object we reference the existing customer and also created a customer inline. We added an email attribute under the referenced customer object, this has the same effect as setting the referenced customer object with the email, if the email attribute exist it updates it if not it will be added to the referenced object.
"o:order": [
{
"#append": {}
},
{
"i:order_id": 1,
"d:datetime":"now()",
"o:cust": {
"#ref": "!@__c1111@!",
"email": "support@schemafreesql.com"
},
"o:lineitem": [
{
"o:item": {
"#ref": "!@__3DcAM01@!",
"i:totalordered": {
"#pass": "v + 1"
}
},
"i:no": 1,
"i:qty": 1,
"n:price": 300.0
},
{
"o:item": {
"#ref": "!@__wristWear03@!",
"i:totalordered": {
"#pass": "v + 2"
}
},
"i:no": 2,
"i:qty": 2,
"n:price": 100.0
}
]
},
{
"i:order_id": 2,
"d:datetime":"now()",
"o:cust": {
"__c2222": "!@result.oid@!",
"s:id": "c2222",
"s:first_name": "Sally",
"s:last_name": "Swanson",
"email": "feedback@schemafreesql.com",
"o:address": {
"street": "7 Broadway",
"city": "New York",
"state": "NY",
"zip": "10003"
}
},
"o:lineitem": [
{
"o:item": {
"#ref": "!@__3DcAM01@!",
"i:totalordered": {
"#pass": "v + 1"
}
},
"i:no": 1,
"i:qty": 1,
"n:price": 300.0
},
{
"o:item": {
"#ref": "!@__LPN45@!",
"i:totalordered": {
"#pass": "v + 2"
}
},
"i:no": 2,
"i:qty": 2,
"n:price": 250.0
}
]
}
],For our final data structure in the modify we append the inline customer we created in the order to the customer array.
"o:customer": [
{
"#append": {}
},
{
"#ref": "!@__c2222@!"
}
]Here is the final payload with 4 queries we added at the end.
[
{
"modify": {
"data": {
"shops": {
"shop": {
"supplier": {
"__sony": "!@result.oid@!",
"name": "Sony Group Corporation",
"id": "sony"
},
"product": [
{
"__3DcAM01": "!@result.oid@!",
"name": "FinePix Pro2 3D Camera",
"code": "3DcAM01",
"image": "camera.jpg",
"n:price": 300.0,
"i:totalordered": 0,
"manufacturer": {
"#ref": "!@__sony@!"
}
},
{
"__USB02": "!@result.oid@!",
"name": "EXP Portable Hard Drive",
"code": "USB02",
"image": "external-hard-drive.jpg",
"n:price": 800.0,
"i:totalordered": 0,
"manufacturer": {
"#ref": "SELECT $o:.supplier.oid() WHERE $s:.supplier.id='sony'"
}
},
{
"__wristWear03": "!@result.oid@!",
"name": "Luxury Ultra thin Wrist Watch",
"code": "wristWear03",
"image": "watch.jpg",
"n:price": 100.0,
"i:totalordered": 0,
"manufacturer": {
"__tag": "!@result.oid@!",
"name": "TAG Heuer",
"id": "tag"
}
},
{
"__LPN45": "!@result.oid@!",
"name": "XP 1155 Intel Core Laptop",
"code": "LPN45",
"image": "laptop.jpg",
"n:price": 250.0,
"i:totalordered": 0,
"manufacturer": {
"__dell": "!@result.oid@!",
"name": "DELL Inc",
"id": "dell"
}
}
],
"o:supplier": [
{
"#append": {}
},
{
"#ref": "!@__tag@!"
},
{
"#ref": "!@__dell@!"
}
],
"customer": {
"#set": {
"where": "$s:customer.id='c1111'"
},
"__c1111": "!@result.oid@!",
"s:id": "c1111",
"s:first_name": "Larry",
"s:last_name": "Smith",
"o:address": {
"street": "5 Elmwood Avenue",
"city": "Rochester",
"state": "NY",
"zip": "14616"
}
},
"o:order": [
{
"#append": {}
},
{
"i:order_id": 1,
"o:cust": {
"#ref": "!@__c1111@!",
"email": "support@schemafreesql.com"
},
"o:lineitem": [
{
"o:item": {
"#ref": "!@__3DcAM01@!",
"i:totalordered": {
"#pass": "v + 1"
}
},
"i:no": 1,
"i:qty": 1,
"n:price": 300.0
},
{
"o:item": {
"#ref": "!@__wristWear03@!",
"i:totalordered": {
"#pass": "v + 2"
}
},
"i:no": 2,
"i:qty": 2,
"n:price": 100.0
}
]
},
{
"i:order_id": 2,
"o:cust": {
"__c2222": "!@result.oid@!",
"s:id": "c2222",
"s:first_name": "Sally",
"s:last_name": "Swanson",
"email": "feedback@schemafreesql.com",
"o:address": {
"street": "7 Broadway",
"city": "New York",
"state": "NY",
"zip": "10003"
}
},
"o:lineitem": [
{
"o:item": {
"#ref": "!@__3DcAM01@!",
"i:totalordered": {
"#pass": "v + 1"
}
},
"i:no": 1,
"i:qty": 1,
"n:price": 300.0
},
{
"o:item": {
"#ref": "!@__LPN45@!",
"i:totalordered": {
"#pass": "v + 2"
}
},
"i:no": 2,
"i:qty": 2,
"n:price": 250.0
}
]
}
],
"o:customer": [
{
"#append": {}
},
{
"#ref": "!@__c2222@!"
}
]
}
}
}
}
},
{
"query": {
"sfsql": "SELECT $s:.supplier.name as name, $s:.supplier.id as id",
"_q": "supp"
}
},
{
"query": {
"sfsql": "SELECT $s:.product.code as code,$s:.product.name as name,$s:.product.image as image,$n:.product.price as price,$i:.product.totalordered as totordered,$s:.product.manufacturer.name as manname ORDER BY code ASC",
"_q": "prod"
}
},
{
"query": {
"sfsql": "SELECT $s:.customer.id as cusid,$s:.customer.first_name as fname,$s:.customer.last_name as lname,$s:.customer.email as email,$s:.customer.address.street as street,$s:.customer.address.city as city,$s:.customer.address.state as state,$s:.customer.address.zip as zip",
"_q": "cust"
}
},
{
"query": {
"sfsql": "SELECT $i:.order.order_id as orderid, $s:.order.lineitem.item.code as code,$s:.order.lineitem.item.name as name,$s:.order.lineitem.item.manufacturer.name as manname, $s:.order.lineitem.item.image as image, $i:.order.lineitem.no as no, $i:.order.lineitem.qty as quantity, $n:.order.lineitem.price as price, $s:.order.cust.id as cusid,$s:.order.cust.first_name as fname, $s:.order.cust.last_name as lname,$s:.order.cust.email as email,$s:.order.cust.address.street as street, $s:.order.cust.address.city as city, $s:.order.cust.address.state as state, $s:.order.cust.address.zip as zip",
"_q": "order"
}
}
]We created 4 queries, Suppliers, Products, Customers and Orders. Theses four queries are added to the JSON payload after the modify command resulting in a final JSON payload of an array of 5 commands, 1 modify (including many sub commands) and 4 queries. These command will be processed by Cloud SFSQL with a single HTTPS post request. Each command is wrapped in a transaction. If a command fails it is rolled back and the processing of the payload is halted.
The queries are pretty self explanatory. A few things we should point out is that we do not have to enter the full path to the attributes selected, the "." will find all attributes matching the selected path, all attributes selected must start with the same initial path. If we want to increase the available attributes to select from you can just increase the path to the left of the attribute up to the root.
Each query also contains a reflective parameters. Reflective Parameters are returned (reflected) back within a Command Response. Reflective Parameters are underscore-prefixed. These are used by the script file to more easily process the results of the queries.
What's left is to create and display the SHOP. First things first is we need get SFSQL up and running. That is is accomplished with one click of a button TRY SFSQL. An endpoint and API key will be displayed
The JSON payload we post to Cloud SFSQL in script file will look something like this for PHP.
$Url = 'SFSQL END POINT';
$key='YOUR_API_KEY_GOES_HERE';
$json='THE_JSON_PAYLOAD_GOES_HERE';
$ch = curl_init($Url);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
curl_setopt($ch, CURLOPT_BINARYTRANSFER, true);
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, $json);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_FAILONERROR, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
'Content-Type: application/json',
'Content-Length: ' . strlen($json),
'x-sfsql-apikey: ' . $key)
);
$result = curl_exec($ch);The results sent back by Cloud SFSQL are below. An array of result objects are returned 1 for each command. Cloud SFSQ does not return an object graph it returns the values of the attributes selected but flattened out. Selects often have attribute values spanning objects as is the case of the results retuned by the Order query. For those who like complete objects graphs returned, stay tuned we are working on it.
[
{
"result.oid" : "49",
"__3DcAM01" : "34",
"__c1111" : "40",
"__c2222" : "46",
"success" : 1,
"__USB02" : "35",
"__dell" : "39",
"cmdname" : "modify",
"__sony" : "33",
"__LPN45" : "38",
"__wristWear03" : "36",
"__tag" : "37"
},
{
"_q" : "supp",
"data" : [
{
"name" : "Sony Group Corporation",
"id" : "sony"
},
{
"name" : "TAG Heuer",
"id" : "tag"
},
{
"name" : "DELL Inc",
"id" : "dell"
}
],
"success" : 1,
"cmdname" : "query"
},
{
"_q" : "prod",
"data" : [
{
"image" : "camera.jpg",
"code" : "3DcAM01",
"price" : 300.000000000000000,
"name" : "FinePix Pro2 3D Camera",
"manname" : "Sony Group Corporation",
"totordered" : 2
},
{
"image" : "laptop.jpg",
"code" : "LPN45",
"price" : 250.000000000000000,
"name" : "XP 1155 Intel Core Laptop",
"manname" : "DELL Inc",
"totordered" : 2
},
{
"image" : "external-hard-drive.jpg",
"code" : "USB02",
"price" : 800.000000000000000,
"name" : "EXP Portable Hard Drive",
"manname" : "Sony Group Corporation",
"totordered" : 0
},
{
"image" : "watch.jpg",
"code" : "wristWear03",
"price" : 100.000000000000000,
"name" : "Luxury Ultra thin Wrist Watch",
"manname" : "TAG Heuer",
"totordered" : 2
}
],
"success" : 1,
"cmdname" : "query"
},
{
"_q" : "cust",
"data" : [
{
"zip" : "14616",
"fname" : "Larry",
"lname" : "Smith",
"city" : "Rochester",
"cusid" : "c1111",
"street" : "5 Elmwood Avenue",
"state" : "NY",
"email" : "support@schemafreesql.com"
},
{
"zip" : "10003",
"fname" : "Sally",
"lname" : "Swanson",
"city" : "New York",
"cusid" : "c2222",
"street" : "7 Broadway",
"state" : "NY",
"email" : "feedback@schemafreesql.com"
}
],
"success" : 1,
"cmdname" : "query"
},
{
"_q" : "order",
"data" : [
{
"zip" : "14616",
"image" : "camera.jpg",
"no" : 1,
"fname" : "Larry",
"code" : "3DcAM01",
"quantity" : 1,
"orderid" : 1,
"city" : "Rochester",
"manname" : "Sony Group Corporation",
"lname" : "Smith",
"price" : 300.000000000000000,
"cusid" : "c1111",
"street" : "5 Elmwood Avenue",
"name" : "FinePix Pro2 3D Camera",
"state" : "NY",
"email" : "support@schemafreesql.com"
},
{
"zip" : "14616",
"image" : "watch.jpg",
"no" : 2,
"fname" : "Larry",
"code" : "wristWear03",
"quantity" : 2,
"orderid" : 1,
"city" : "Rochester",
"manname" : "TAG Heuer",
"lname" : "Smith",
"price" : 100.000000000000000,
"cusid" : "c1111",
"street" : "5 Elmwood Avenue",
"name" : "Luxury Ultra thin Wrist Watch",
"state" : "NY",
"email" : "support@schemafreesql.com"
},
{
"zip" : "10003",
"image" : "camera.jpg",
"no" : 1,
"fname" : "Sally",
"code" : "3DcAM01",
"quantity" : 1,
"orderid" : 2,
"city" : "New York",
"manname" : "Sony Group Corporation",
"lname" : "Swanson",
"price" : 300.000000000000000,
"cusid" : "c2222",
"street" : "7 Broadway",
"name" : "FinePix Pro2 3D Camera",
"state" : "NY",
"email" : "feedback@schemafreesql.com"
},
{
"zip" : "10003",
"image" : "laptop.jpg",
"no" : 2,
"fname" : "Sally",
"code" : "LPN45",
"quantity" : 2,
"orderid" : 2,
"city" : "New York",
"manname" : "DELL Inc",
"lname" : "Swanson",
"price" : 250.000000000000000,
"cusid" : "c2222",
"street" : "7 Broadway",
"name" : "XP 1155 Intel Core Laptop",
"state" : "NY",
"email" : "feedback@schemafreesql.com"
}
],
"success" : 1,
"cmdname" : "query"
}
]These result are passed to the JavaScript on the same page and displayed here is the beginning of the the PHP script where $query is the results shown above. The JavaScript uses the reflective parameters when populating the respective variables.
var results = ``;
var obj = JSON.parse(results);
let sups = obj.filter(supp => supp._q == "supp")[0]["data"];
let prods = obj.filter(prod => prod._q == "prod")[0]["data"];
let custs = obj.filter(cust => cust._q == "cust")[0]["data"];
let items = obj.filter(order => order._q == "order")[0]["data"];
let items1 = items.filter(obj => {
return obj.orderid === 1;
});
let items2 = items.filter(obj => {
return obj.orderid === 2;
});
...
Below are links to the Shop Admin in various platforms and here is the source code for each 
PHP SFSQL SHOP ADMIN
Cloudflare Worker SFSQL SHOP ADMIN
Deno Deploy SFSQL SHOP ADMIN
Google Cloud Function SFSQL SHOP ADMIN
Azure Cloud Functions SFSQL SHOP ADMIN
Netlify (AWS lamda) SFSQL SHOP ADMIN
We forgot to mention what we did not do
- We did not have to mess with setting up and configuring a database.
- We did not have to install a SDK or client for our environment
- We did not have to create or manage any schemas
- We did not have to create or mange any indexes.
- We did not have to put a lot of though into collections and query patterns.
- We created a normalized data structure without much effort.
LESS CODE means taking care of these details so you can get to working on the fun stuff.