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