Andl

This is a page of sample code, showing basic relational capabilities. It will be part of the download, when available.

// Andl samples -- relational
// Aim is to show an example of every feature
// Also useful as a quick smoke test

// ===== Relational types =====

// Tuples - not used much
{}
{name := 'Smith', age := 17}
{age := 17, name := 'Smith'}

// Relations - used heavily
{{:}}       // empty relation, no attributes, no tuples
{{:}{}}     // ditto, one tuple
{{}}        // same, with type derived by inference
{{},{},{},{},{},{},{},{}}        // exactly the same value (duplicates discarded)

// all the same -- order does not matter
{{name := 'Smith', age := 17}}
{{name:'',age:0}{'Smith', 17}}
{{name:text,age:number}{'Smith', 17}}

// all the same -- differences in order and syntax
{{name := 'Smith', age := 17},{name := 'Jones', age := 35},{age :=199,name:='Frankenstein' }}
{{name := 'Smith', age := 17},{age :=199,name:='Frankenstein' },{age := 35, name := 'Jones'}}
{{name:,age:0}{'Smith', 17}{'Jones', 35}{'Frankenstein',199 }}

// Built in functions
sequence(5)     // relation of N integers {{ N:number }}

r1 := {{name:,age:0}{'Smith', 17}{'Jones', 35}{'Frankenstein',199 }}
r1.schema       // relation of attributes
r1.count        // cardinality
r1.degree       // degree

//===== Basic operations =====

// Load some data from CSV files
S := source('csv:', 'S.csv')
P := source('csv:', 'P.csv')
SP := source('csv:', 'SP.csv')
S
S.schema
S.count
S.degree

//==== monadics =====
// all monadic operations are inside [], in a fixed sequence ?$%{}

// restriction - remove rows
S [ ?(CITY = 'Paris') ]
S [ ?(STATUS > 15 or CITY = 'London') ]

// --- rename - change column names
// rename all
S [ { F1 := S#, F2 := SNAME, F3 := STATUS, F4 := CITY }]
// rename some, the * means keep the rest unchanged
S [ { * F1 := SNAME }]

// --- projection - remove columns
// name all to be kept
S [ { S#, SNAME, CITY }]
// now * means keep all but the ones named
S [ { * STATUS }]

// --- extension - add new columns
// Here * means keep all, add new ones
S [ { * Initial := left(SNAME, 1) }]

// --- combine all three
S [ { CITY, F := STATUS, Initial := left(SNAME, 1) }]
S [ { * SNAME, Initial := left(SNAME, 1) }]

// --- aggregated projection - projection with totalling
S [ { CITY, 
    total := fold(+,STATUS), 
    average := fold(+,STATUS)/fold(+,1) 
} ]

// Note: fold() is only allowed in projection, but looks nicer in a function
sum(n:0) => fold(+,n)
ave(n:0) => fold(+,n)/fold(+,1)
S [ { CITY, total := sum(STATUS), average := ave(STATUS) } ]

// --- ordered extension - means extension with access to other rows
// ordered on CITY but no grouping, so all in one group
S [ $(CITY) { *  
    ord:=ord(),     // unique index based on input, not output
    ordg:=ordg(),   // ord value for first member of group
    lag:=lag(STATUS,1),     // previous value in group, or default
    lead:=lead(STATUS,1),   // next value in group, or default
    nth:=nth(STATUS,1),     // nth value in group, or default
} ]
// ordered and grouped on CITY
S [ $(%CITY) { *  
    ord:=ord(),
    ordg:=ordg(),
    lag:=lag(STATUS,1), 
    lead:=lead(STATUS,1), 
    nth:=nth(STATUS,1), 
} ]
// ordered and grouped on CITY descending, with subtotalling/running sum
S [ $(%-CITY) { *  
    ord:=ord(),
    ordg:=ordg(),
    lag:=lag(STATUS,1), 
    lead:=lead(STATUS,1), 
    nth:=nth(STATUS,1), 
    sum:=fold(+,STATUS),    // running sum within group
    ave:=fold(+,STATUS)/fold(+,1),
} ]

// Ordered used just for display sort
P[$(WEIGHT)]
P[$(COLOR,-WEIGHT)]     // descending

// --- lift anonymous value out of singleton relation
S [ { sum(STATUS) } ]
S [ { ave(STATUS) } ]

//--- nested relation
nr1 := {{ name := 'S', contents := S }}
nr1
nr2 := {
    { name := 'S1', contents := S [?( CITY = 'London')] },
    { name := 'S2', contents := S [?( CITY = 'Paris')] },
    { name := 'S2', contents := S [?( CITY = 'Athens')] } }
nr2
// retrieve one row as relation
nr2 [?(name='S1') { contents }]
// put the relation back together again using fold and union
nr2 [ { fold(union,contents) } ]

//==== dyadics =====

// prepare some subsets
S3 := S [?( S# = 'S3')]    // one single supplier S3
SX := S [?( S# <> 'S3')]   // all suppliers except S3 to make this work better
SY := S [?( S# <> 'S1')]   // all suppliers except S1

// set membership -- all true
S3 sub S        // subset
S sup SX        // superset
S3 sep SX       // separate

// joins

S join SP       // natural join preserves all columns for matching tuples
S compose SP    // projects onto non-common attributes
S semijoin SP   // projects onto left and common attributes
S divide SP     // projects onto left only attributes
S rsemijoin SP  // projects onto right and common attributes
S rdivide SP    // projects onto right only attributes

// antijoins

SX ajoin SP      // antijoin preserves all left attributes for non-matching
SX ajoinl SP     // projects onto left only attributes
SX rajoin SP     // reverse antijoin has right and common attributes
SX rajoinr SP    // projects onto right only attributes

// set operations

SX union SY      // combines all tuples
SX intersect SY  // keep common tuples
SX symdiff SY    // keep non-common tuples
SX minus SY      // keep left minus right
SX rminus SY     // keep right minus left

// all set operations project onto common attributes
SZ := {{ S#:='S99', STATUS:= 999, CITY:='Paris' }}
S union SZ
S minus SZ

// ===== Advanced Usage =====

// --- Nest: replace each tuple of S by one converted into a singleton relation
// {{*}} means 'the current tuple as a singleton relation'
ES1 := S [{ embed := {{*}} }]
ES1

// --- Unnest: using fold union and lift -- advanced usage!
ES1 [{ fold(union,embed) }]

// --- Image relation -- extend S with a partion of SP, removing common fields
// note that S5 gets a partition that is an empty relation
ES2 := S [{ * partition := ( {{*}} rdivide SP) }]
ES2

// Report total no of parts and qty (including S5 which supplies no parts)
ES2 [{ S#, parts:=partition.count, qtys:=partition[{sum(QTY)}] }]

// --- Transitive closure
// MM has tuples reprenting part/subpart assemblies
MM := source('csv:','MM.csv')
MM

// define a relational type
xyt := {{x:='',y:=''}}
// define a recursive function that takes a relation of that type as an argument
tranclo:xyt(xy:xyt) => do {
        ttt := xy[{*z := y}] compose xy[{*z := x}] union xy
        if(ttt = xy, ttt, tranclo(ttt))
    }
// call it with MM as argument, renaming attributes to match
tranclo(MM[ {x:=MAJOR_P#, y:= MINOR_P# } ]) [{MAJOR_P#:=x, MINOR_P#:=y }]

// ===== Updates =====

// Define the 3 updates

// Insert: argument is relation of same heading
up1 => S := union {{ S#:='S9', SNAME:='Moriarty', STATUS:=99, CITY:='Timbuktu' }}
// Delete: read as replace matching rows by nothing
up2 => S := [ ?(S#='S3') ]
// Update: make changes to matching rows
up3 => S := [ ?(S#='S4') { *STATUS:= -10 } ]
// Now perform each update in turn
S // original
up1
S // add S9
up2
S // delete S3
up3
S // update STATUS of S4

// Persistence
// any relvar starting with ^ is persisted

^S := S

// end

This is a page of sample code, showing basic non-relational capabilities. It will be part of the download, when available.

// Andl samples -- scalar
// Aim is to show an example of every feature
// Also useful as a quick smoke test

//=== Tokens ===
// A free expression is evaluated immediately
'hello world!'
// Single and double quoted strings are concatenated immediately
"Hel""lo" ' World' "!"
// d-string is decimal Unicode char
"Hello" & d'32 87' & "orld"
// h-string is hex Unicode char
"Hello" & h'20 57' & "orld"
// t-string is a Time
t'2015/12/31'
// i-string is an identifier
i'x( )' := 'hello world!'
i'x(' h'20' ')'

//=== Expressions ===
// Logical: all true
true = not false
3 <> 4
'ABC' > 'abc'
t'2015/12/31' < t'01/01/2016'
'Z' >= 'A' and (3<=4 or (true xor false))

// Numeric: all 42
5-8*6/4+7**2
(58 div 8) * (86 mod 8)
(9 or 7) and (9 xor 3) xor 32

// String: mostly Hello World!
'Hello' & " " & 'World!'
trim('Hello ') & ' ' & trim(' World') & trim('   !   ')
fill(' ',10) & fill('Hello Planet???', 5) & fill(' World!', 35)
before(after("@@@>>>Hello World!<<<@@@", '>>>'), '<<<')
toupper('h') & tolower('ELLO') & toupper(' w') & tolower('ORLD!')
// String: other
'hello'.length = length('world')
"Interpolate date: " & t'2015/12/31' & " number:" & 12345 & " logical:" & (2=2)

// Date: 
d1 := dateymd(2015,1,31)
"Date: " & d1 & " Year:" & d1.year & " month:" & d1.month & " day:" & d1.day & " dow:" & d1.dow

// Special: if(), only evaluates one of its arguments
if(true,'Hello World!', "goodbye!")
if(2>2,1/0, 7*6)

//=== Statements ===
// Assignment -- evaluated once
v1 := 'Hello World!'
v1
// Assignment to out sends direct to output
out := v1
// Deferred assignment -- evaluated every time
v2 => v1
v2

// do block creates a local scope and returns value of last expression (statement returns void)
do {
  v2 := v1
  v2
}
// Deferred evaluation wiht do block
v3 => do {
  v2 := v1
  v2
}
v3
// Deferred evaluation with do block and arguments
v4(a) => do {
  v2 := a
  v2
}
// Arguments with literal types (default is text)
v5(a:'',b:0,c:false,d:d1) => do {
  a & b & c & d
}
v5(v1,42,true,dateymd(2015,1,1))
// Arguments with named types
v6(a:text,b:number,c:bool,d:date) => do {
  a & b & c & d
}
v6(v1,42,true,dateymd(2015,1,1))
// Recursion, function name must be typed
fact:0(n:0) => if(n<=1,1,n*fact(n-1))
fact(20)

// ===== Types =====

u1 := ut1 { n:=42, t:=v1, d:= d1 }
"n:" & u1.n & " t:" & u1.t & " d:" & u1.d
u2 := ut1 { n:=41, t:="!"&v1, d:= dateymd(d1.year+1,d1.month,d1.day) }
"n:" & u2.n & " t:" & u2.t & " d:" & u2.d
// Comparison left-to-right
u1 > u2
// Deferred function
f7(u:ut1) => do {
    "n:" & u.n & " t:" & u.t & " d:" & u.d
}
f7(u1)
f7(u2)

// done