Sample Code 2

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

Leave a Comment

Filed under Code sample

Leave a Reply

Your email address will not be published. Required fields are marked *