Andl is a New Database Language designed to replace SQL and then go beyond.
Andl is a full programming language with an advanced type system; it is relationally complete and has higher order queries; and it has platform independent interfacing. So Andl does two things well: perform advanced relational queries and build an application data model for any platform.
First, Andl can perform relational queries at or beyond the capabilities of any SQL dialect. It can do all the ordinary things like select, where and join but it can also do generative queries, self-joins, complex aggregations, subtotals and running totals (a bit like SQL recursive common table expressions and windowing). Here is some sample source code.
// Flatten org chart to show levels
org := {{ name:= 'Alice', level := 0 }}
.while( {{ boss := name, level := level+1 }} compose orgchart)
org .select{ t:=fill('.', level*3) & name }
// ordered and grouped on CITY descending, with subtotalling/running sum
S .order(%-CITY) .select{ *
ord:=ord(),
ordg:=ordg(),
lag:=lag(STATUS,1),
lead:=lead(STATUS,1),
nth:=nth(STATUS,1),
sum:=sum(STATUS), // running sum within group
max:=hi(STATUS),
min:=lo(STATUS),
ave:=fold(+,STATUS)/fold(+,1),
}
Andl can also provide a complete application backend for any kind of user interface on any platform. It can easily be used to program a data model as a set of tables just like SQL, but including all the access routines, updating logic and interfaces. These can be accessed on any platform: mobile, desktop, web or cloud. The user interface can be written in any popular language or technology, such as Java, Dot Net, JavaScript and using any available communications method. Here is source code for part of a simple data model with a REST interface.
// load initial data
var WebEmp(csv)
// function to get new employee number
newempno => WebEmp .select{ fold(max,EmpNo)}
// this is the CRUD interface using REST
get_employee_id(empno:text) => do {
WebEmp .where(EmpNo.text = empno)
}
get_employee() => do {
WebEmp .order(EmpNo)
}
add_employee(emp:WebEmp) => do {
update WebEmp union emp .select{ * EmpNo := newempno}
newempno := newempno+1
}
delete_employee_id(empno:text) => do {
update WebEmp .where(EmpNo.text = empno) .select{}
}
put_employee_id(empno:text, emp:WebEmp) => do {
update WebEmp .where(EmpNo.text = empno) .select{}
update WebEmp union emp
}
Andl comes with the Workbench for easy experimentation, and sample scripts that show just about everything it can do (which is quite a lot). It supports SQLite and Postgres as the backend database with more to come. There are samples of Andl source code under this tag. Some of the more interesting recent releases include Workbench, Sqlite and Thrift. The most recent posts are here.
See the role of Andl, why we need it, problems with SQL and a TTM paraphrase to read further. Download from GitHub.
If this something you’re interested in, please email me as david at this domain.
The picture is the Lasseter Highway in outback Australia. It too is a path through a strange territory, thought to be well-charted but full of surprises and dangers.