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
