Is #SQL #lowcode? #JoelKallmanDay 2022
I was giving 3 presentations at OUG Norways event yesterday, one of which was an old one of mine on analytic functions (oldie but goodie.) Afterwards Morten Braten tweeted about it using the hashtag #lowcode. That got me thinking... could SQL be considered low-code?
Morten used the #lowcode hashtag in conjunction with the #orclapex hashtag, APEX being the premier low-code solution from Oracle, but let's look at it a bit more generally.
85 lines of code - a nice medium-sized query. But all classic typed programming - how on earth could that be considered low-code?
A low-code development platform (LCDP) provides a development environment used to create application software through a graphical user interface.
The Low-Code Guide says:
Low-code is an application development method that elevates coding from textual to visual.
Okay, so low-code is development by mouse instead of keyboard, right? Okay, maybe that's simplified a bit too much, but at least it means using a minimum of classic typed programming language.
The specific SQL statement that I was presenting that prompted Mortens tweet can also be found in an old blog post of mine. Here's the statement in full:
with orderlines as (
select o.ordno
, o.item
, o.qty
, nvl(sum(o.qty) over (
partition by o.item
order by o.ordno
rows between unbounded preceding and 1 preceding
),0) + 1 from_qty
, nvl(sum(o.qty) over (
partition by o.item
order by o.ordno
rows between unbounded preceding and current row
),0) to_qty
from orderline o
where ordno in (51, 62, 73)
), orderbatch as (
select o.item
, sum(o.qty) qty
from orderlines o
group by o.item
), fifo as (
select s.loc
, s.item
, s.loc_qty
, least(s.loc_qty, s.ord_qty - s.sum_prv_qty) pick_qty
, sum_prv_qty + 1 from_qty
, least(sum_qty, ord_qty) to_qty
from (
select o.item
, o.qty ord_qty
, i.loc
, i.qty loc_qty
, nvl(sum(i.qty) over (
partition by i.item
order by i.purch, i.loc
rows between unbounded preceding and 1 preceding
),0) sum_prv_qty
, nvl(sum(i.qty) over (
partition by i.item
order by i.purch, i.loc
rows between unbounded preceding and current row
),0) sum_qty
from orderbatch o
join inventory i
on i.item = o.item
) s
where s.sum_prv_qty < s.ord_qty
), pick as (
select to_number(substr(f.loc,1,1)) warehouse
, substr(f.loc,3,1) aisle
, dense_rank() over (
order by
to_number(substr(f.loc,1,1)), -- warehouse
substr(f.loc,3,1) -- aisle
) aisle_no
, to_number(substr(f.loc,5,2)) position
, f.loc
, f.item
, f.pick_qty pick_at_loc
, o.ordno
, least(
f.loc_qty
, least(o.to_qty, f.to_qty) - greatest(o.from_qty, f.from_qty) + 1
) qty_for_ord
from fifo f
join orderlines o
on o.item = f.item
and o.to_qty >= f.from_qty
and o.from_qty <= f.to_qty
)
select p.loc
, p.item
, p.pick_at_loc
, p.ordno
, p.qty_for_ord
from pick p
order by p.warehouse
, p.aisle_no
,case
when mod(p.aisle_no,2) = 1 then
p.position
else
-p.position
end
/
Well, no, it's not something that came about by pointing and clicking in a GUI, true. But compare it to the 13 java classes containing total 969 lines of code that I've used to implement the same (okay, almost the same) using Hibernate in Java. Then 85 lines of SQL is definitely low-code, right?
Although SQL does not fit the definitions of low-code, to me it is still a very good way of minimizing the use of typed programming language, as you'd need a lot more code to achieve the same without SQL in other classic typed languages. Add to it the benefit that you utilize the power in your database and get a much more performant solution - then it's definitely something to aim for in my opinion.
I will forward the opinion that low-code can become too low! Meaning that if you aim for no-code (or as near to it as you can get), you might solve the task (my Hibernate solution above or something very similar could conceivably have been made without much typing, I think) - but at the cost of getting a slow performing application that only uses a fraction of the power of your database.
My opinion is that the ideal is to take those parts of your application that can benefit from hand-written SQL (mostly business logic) and actually write that SQL by hand - it'll be the "lowest code" that'll do a good job, even if it doesn't fit the definition of low-code. Then you use "real" low-code on the rest, which will include calling that SQL (preferably wrapped in PL/SQL.)
You should have SQL developers on your team that make performant SQL to implement APIs that the rest of your team can call (thereby coding in pure low-code.)
Oracle APEX happens to be an environment where it's easy to make an application conform to this stated ideal of mine - SQL as an integral part of low-code that makes the app better by not-quite being 100% point-and-click low-code, but having just enough hand-typed SQL to make it perform well.
You can achieve the same or similar in other development environments, but why not go for the easy way - APEX is there for you.
Really intersting
ReplyDelete