SQL Service Deep Dive
In this article, we do a deep dive on the SQL service. We will start with an overview of the SQL service, followed by an examination of the various components of the SQL service. We will then present some example SQL services. Finally we will have a discussion on substitution variable and batch input.
We assume that you have had some hands-on experience with Service Builder, the development tool for data access service, and with the data access service. If not, please take a look at these tutorial first:
Get Started with Service Builder
Get Started with Data Access Service
Overview
Use
SQL service is a command for manipulating data in the data source. It aims to provide the developer the flexibility to accomplish any data change with a sequence of DML statements in the most efficient way he/she sees. However, it does support DDL commands when needed.
The basic SQL service simply takes an input object and makes the change in database. If the developer chose, an optional query may be add to the basic SQL service and return the changed data. In the later case, the SQL service may return an output object or array, just like the query service.
Components
Conceptually, the SQL service is composed of the following components:
- input
- sqls
- input bindings
- optional output
- optional query, and
- optional output bindings
The basic SQL service only includes the input, sqls and input bindings components. The input is a JSON strings specifying the structure and data fields of the service input. The sqls components is a list of DML statements to be executed against the data source. The input bindings are data mappings that map the SQL parameters of the DML statements to the data fields of the input.
For the SQL service with query, the optional output, query and output bindings components may be specified. The output is JSON strings specifying the structure and data fields of the service output; the query is a SQL query for retrieving the data to populate the output object or array after the DMLs are executed. The output bindings are data mappings that map the data fields of the output to the columns of the query. In this case, the input bindings include data mappings that map both the SQL parameters and query parameters to the data fields of the input.
Development
Service Builder provides a streamlined process for developing the SQL service in a few simple steps: - create the service - specify the input, and optional output if needed, in JSON - develop the DML statements for the problem - develop the optional SQL query for the problem, only if needed - generate input and output bindings - generate, edit and run the test(s) for the service
The focus of the SQL service development is on developing the SQL statements for making the database change. As such, it is important to create a SQL development environment in VS Code with the various database extensions available, so that we have the visibility of database in VS Code and have the help of SQL syntax linting, SQL intellisense, SQL test, etc. Otherwise, we will need to compose and test the SQLs in a specialized SQL editor, such as Oracle SQL developer, and then copy and paste them into the sqls.sql file.
As soon as the SQL service is completed, we should try to deploy it into the remote workspace, which helps validate the service. We can then fix the issue, if any, and repeat the process until it is clean. Thereafter, we can proceed to adding the test.
The test for SQL service may be run with or without a commit. A rollback will be performed at the end of the test if it is run without commit.
Deployment
The SQL service is deployed on the data access server as an HTTP API. At development time, it is deployed into the remote workspace as it is developed. At runtime, it is deployed as part of the data access application.
Consumption
Since the query service is deployed as an HTTP API, it may be invoked by a HTTP request, like
POST: https://{baseUrl}/application/module/service
Content Type: application/json
{
"sqlParameter": 123
}
The HTTP method is always POST. The body of the request is the input JSON. The baseUrl depends on the workspace or runtime instance.
Anatomy
Lets take a look at the SQL service through an example: cloneProductLine. The service is to clone a product line from a source product line, including all products under the product line, and return the new product line.
Service Files
Upon creation, we will get a set of service files as below:
- clonePriceLine
- service.json
- input.json
- output.json
- sqls.sql
- query.sql
- input-bindings.json
- output-bindings.json
The service.json files is a service descriptor generated by Service Builder and is not supposed to be modified, except the variableSize attribute if we are to allow substitution variable in a DDL commands. The rest of files are the various components of the SQL service and are what are to be developed by the developer.
Input and Output
The input and output define the interface of the SQL service. For the basic SQL service, the output is ignored. For our example, we have
Input:
{
"sourceProductLineName": "Classic Cars",
"targetProductLineName": "Cloned Classic Cars",
"productCodePrefix": "N-"
}
Output:
{
"name": "New Classic Cars",
"description": "a short text",
"products": [{
"productCode": "S18_1749",
"productName": "1969 Harley Davidson Ultimate Chopper",
"buyPrice": 31.92,
"msrp": 79.80,
"quantityInStock": 0
}]
}
The input must be a simple object. Object structure is allowed, but array structure is not allowed at any level except for value lists, such as [1, 2, 3] and ["a", "b", "c"]. The output may be an object or Array of any shape.
For our example, the input is an object specifying the name of the source price line, the name of the new price line, and a prefix for product code. The output is the new price cloned by the service.
SQLs
The DML SQL statements is in the center of the SQL service. It is expected to take advantage of the power of the SQL to perform the required data change efficiently.
For our example, we have
INSERT INTO productlines (
productLine, textDescription
)
SELECT :targetProductLineName, textDescription
FROM productlines
WHERE productLine = :sourceProductLineName
;
INSERT INTO products (
productCode, productName, productLine,
productScale, productVendor, productDescription, quantityInStock,
buyPrice, MSRP
)
SELECT concat(:productCodePrefix, productCode), productName, :targetProductLineName,
productScale, productVendor, productDescription, 0,
buyPrice, MSRP
FROM products
WHERE productLine = :sourceProductLineName
Our example uses two insert-select statements to efficiently complete the job of cloning the source product line without the data leaving the database.
Query
The query executes after the sqls. It provide a mechanism to retrieve the changed data.
For our example, we have
SELECT pl.productLine, pl.textDescription,
p.productCode, p.productName, p.productVendor
FROM productlines pl
LEFT JOIN products p ON p.productLine = pl.productLine
WHERE pl.productLine = :targetProductLineName
It returns the new product line just cloned.
Input and Output Bindings
The input bindings indicate the source of data for the SQL and query parameters. The output bindings provide the critical information to construct the output object or array from the relational data set returned by the query.
For our example, we have the following generated from the input, output, sqls and query components.
Input Bindings:
[
{
"parameter": "targetProductLineName",
"field": ".targetProductLineName"
},
{
"parameter": "productCodePrefix",
"field": ".productCodePrefix"
},
{
"parameter": "sourceProductLineName",
"field": ".sourceProductLineName"
}
]
Output Bindings:
[
{
"field": ".productLine",
"column": "productLine"
},
{
"field": ".description",
"column": "textDescription"
},
{
"field": ".products..productCode",
"column": "productCode"
},
{
"field": ".products..productName",
"column": "productName"
},
{
"field": ".products..productVendor",
"column": "productVendor"
}
]
The input bindings map the and sql and query parameters to the date fields of the input object. Multiple parameters may be mapped to the same data fields, but all parameters must be mapped. The unmapped data fields are ignored.
The output bindings map the data fields of the output object to the columns of the query. Multiple data fields may be mapped to the same column, but all data fields must be mapped. The unmapped columns are ignored. If the column has an alias, the data field should be mapped to the alias name.
The data fields in the input and output bindings are designated with tailed JSON path, such as ".productLine" and ".products..productCode" in the output bindings. The dot "." and double-dot ".." in the path strings denote an object and an array structure, respectively. The dot "." at beginning of the path string denotes the root object. If the output is an array, the path string will begin with double-dot "..".
The input and output bindings are not expected to be hand-coded from scratch, but generated with Service Builder from the input, output and query components first, and then reviewed and fixed, if necessary, by the developer. The Service Builder may not generate perfect mappings in all cases, and the developer is ultimately responsible for the accuracy of the data mapping. Proper column aliasing in the SQL query helps Service Builder generate correct mappings.
Test
A test may be added after the SQL service is completed. For our example, we have a test named testCloneProductLine:
{
"name": "testCloneProductLine",
"input": {
"sourceProductLineName": "Classic Cars",
"targetProductLineName": "Cloned Classic Cars",
"productCodePrefix": "N-"
},
"comments": "Modify the example test name and input."
}
for testing the SQL service cloneProductLine. The test may run with or without commit.
Examples
The examples in this tutorial demonstrates what we can do with SQL service and how we could do it. The sample MySQL database used for the examples is the classicmodels database for MySQL Tutorial. The source code for these example SQL services can be found here.
Example #1 - updateInventoryForProduct
Problem
To deduct a quantity of a product from stock. The simplest SQL service that update a single field of a table and returns the updated number.
Input:
Output:
SQLs
UPDATE products
SET quantityInStock = quantityInStock - :qtyToDeduct
WHERE productCode = :productCode
Query
Input and Output Bindings
Input Bindings:
[
{
"parameter": "productCode",
"field": ".productCode"
},
{
"parameter": "qtyToDeduct",
"field": ".qtyToDeduct"
}
]
Output Bindings:
Example #2 - deleteCustomer
Problem
To remove a customer along with all its order and payment records. A basic SQL service that efficiently delete a cluster of records.
Input:
SQLs
DELETE FROM payments WHERE customerNumber = :customerNumber
;
DELETE FROM orderdetails WHERE orderNumber in (
select orderNumber from orders where customerNumber = :customerNumber
)
;
DELETE FROM orders WHERE customerNumber = :customerNumber
;
DELETE FROM customers WHERE customerNumber = :customerNumber
The payment, order, order detail and customer records are efficiently removed with four simple batch delete statements.
Input and Output Bindings
Input Bindings:
Output Bindings:
None.
Example #3 - cloneProductLine
Problem
To clone a product line with a full list of products and return the new product line. A basic SQL service to change data in place.
Input:
{
"sourceProductLineName": "Classic Cars",
"targetProductLineName": "Cloned Classic Cars",
"productCodePrefix": "N-"
}
Output:
{
"productLine": "",
"description": "",
"products": [{
"productCode": "S12_1099",
"productName": "1968 Ford Mustang",
"productVendor": "Autoart Studio Design"
}]
}
SQLs
INSERT INTO productlines (
productLine, textDescription
)
SELECT :targetProductLineName, textDescription
FROM productlines
WHERE productLine = :sourceProductLineName
;
INSERT INTO products (
productCode, productName, productLine,
productScale, productVendor, productDescription, quantityInStock,
buyPrice, MSRP
)
SELECT concat(:productCodePrefix, productCode), productName, :targetProductLineName,
productScale, productVendor, productDescription, 0,
buyPrice, MSRP
FROM products
WHERE productLine = :sourceProductLineName
Query
SELECT pl.productLine, pl.textDescription,
p.productCode, p.productName, p.productVendor
FROM productlines pl
LEFT JOIN products p ON p.productLine = pl.productLine
WHERE pl.productLine = :targetProductLineName
Input and Output Bindings
Input Bindings:
[
{
"parameter": "targetProductLineName",
"field": ".targetProductLineName"
},
{
"parameter": "productCodePrefix",
"field": ".productCodePrefix"
},
{
"parameter": "sourceProductLineName",
"field": ".sourceProductLineName"
}
]
Output Bindings:
[
{
"field": ".productLine",
"column": "productLine"
},
{
"field": ".description",
"column": "textDescription"
},
{
"field": ".products..productCode",
"column": "productCode"
},
{
"field": ".products..productName",
"column": "productName"
},
{
"field": ".products..productVendor",
"column": "productVendor"
}
]
Example #4 - createAccountReceivableTables
Problem
To create account tables needed for hypothetic account receivables. A SQL service that runs a number of DDL statements.
Input and Output
none.
SQLs
create table if not exists accountReceivables (
receivableNumber int AUTO_INCREMENT,
receivableDate date,
customerNumber int,
receivableAmount double,
PRIMARY KEY (receivableNumber),
UNIQUE KEY (receivableDate, customerNumber)
)
;
create table if not exists accountReceivableDetails (
receivableNumber int,
orderNumber int,
orderAmount double,
PRIMARY KEY (receivableNumber, orderNumber)
)
Input and Output Bindings
Input Bindings:
Output Bindings:
For SQL service with DDL statements,
Test without Commitdoes not apply and may lead to inconsistent transaction if the service has mixed DDL and DML statements.
Example #5 - createAccountReceivable
Problem
To create and return account receivable records from order data. A SQL service that creates computed records.
Input:
Output:
[
{
"receivableNumber": 12,
"receivableDate": "S10_4757",
"customerNumber": 100,
"receivableAmount": 123.45,
"details": [{
"orderNumber": 1,
"orderAmount": 34.56
}]
}
]
SQLs
insert into accountReceivables (
receivableDate, customerNumber, receivableAmount
)
select o.shippedDate as receivableDate, o.customerNumber, sum(od.priceEach*quantityOrdered) as receivableAmount
from orders o
join orderdetails od on od.orderNumber = o.orderNumber
where o.shippedDate = :receivableDate
group by customerNumber
;
insert into accountReceivableDetails (
receivableNumber, orderNumber, orderAmount
)
select ar.receivableNumber, o.orderNumber, sum(od.priceEach*quantityOrdered) as orderAmount
from orders o
join orderdetails od on od.orderNumber = o.orderNumber
join accountReceivables ar on ar.receivableDate = o.shippedDate and ar.customerNumber = o.customerNumber
where ar.receivableDate = :receivableDate
group by ar.receivableNumber, orderNumber
The first INSERT SELECT creates receivable records for all customers. The second INSERT SELECT add ORDER details for the receivable records. Combined with SELECT statement, the SQL DML is a very powerful and efficient way for creating and changing large amount of data.
Query
select ar.receivableNumber, ar.customerNumber, ar.receivableDate,
ar.receivableAmount, ard.orderNumber, ard.orderAmount
from accountReceivables ar
join accountReceivableDetails ard on ard.receivableNumber = ar.receivableNumber
where ar.receivableDate = :receivableDate
order by ar.receivableNumber, orderNumber
The query returns the data set for the newly created receivable records.
Input and Output Bindings
Input Bindings:
Output Bindings:
[
{
"field": "..receivableNumber",
"column": "receivableNumber"
},
{
"field": "..receivableDate",
"column": "receivableDate"
},
{
"field": "..customerNumber",
"column": "customerNumber"
},
{
"field": "..receivableAmount",
"column": "receivableAmount"
},
{
"field": "..details..orderNumber",
"column": "orderNumber"
},
{
"field": "..details..orderAmount",
"column": "orderAmount"
}
]
Example #6 - archiveOldOrders
Problem
To archive old order data on or before the last day of cutoff year. A SQL service that creates table on the fly and moves data records in large batch.
Input:
SQLs
drop table if exists ordersArchive${cutoffYear}
;
create table ordersArchive${cutoffYear} as
select o.*
from orders o
where o.orderDate <= LAST_DAY(concat(:cutoffYear, '-12-31'))
order by orderDate desc, o.orderNumber desc
;
drop table if exists orderDetailsArchive${cutoffYear}
;
create table orderDetailsArchive${cutoffYear} as
select od.*
from orders o
join orderdetails od on od.orderNumber = o.orderNumber
where o.orderDate <= LAST_DAY(concat(:cutoffYear, '-12-31'))
;
delete from orderdetails
where orderNumber in (
select orderNumber from orders o
where o.orderDate <= LAST_DAY(concat(:cutoffYear, '-12-31'))
)
;
delete from orders
where orderDate <= LAST_DAY(concat(:cutoffYear, '-12-31'))
There are mixed DDL and DML statements for this SQL service. We also see a substitution variable ${cutoffYear} is used in the DDL statements. The value of this substitution variable also comes from the input.
Input and Output Bindings
Input Bindings:
Output Bindings:
DDL and Substitution Variable
As we have already seen from the examples above, SQL service supports DDL and substitution variable in DDL. The substitution variable is disallowed by default. To enable substitution variable, we need to change the variableLength property in the service.json file to a number larger then zero. SQL service limits the length of the substitution value, to minimize the risk of SQL injection. The max value for variableLength is 100.
Batch Input
SQL service supports batch input, meaning that we can invoke a SQL service with an array of input objects. Let's look an example.
Suppose that we have a SQL service replenishInventory that increases the quantity in stock of a product and then returns the inventory for all products, as
Input:
Output:
SQLs:
Query:
We normal call this service with an input for one product:
but we can also call this service with an array of inputs for multiple products:
In this case, the sqls will be executed once for each input in the array, and the query will be executed only once at the end of service.
Conclusions
SQL service may be used from updating a single column of a single table to creating and changing a large amount of related data from multiple tables efficiently. Even DDL is supported in the command, if needed. The focus of the SQL service development is on preparing the SQL statements enabling the desired database changes. It is important for the developer to create a SQL development environment in VS Code with the database extensions available.