Starting with version 3.0, Sofawiki supports the Relation language. Relation is at first a query language for the Sofawiki database. But it is more than that. It is a general purpose programming language that allows you to program calculations directly in your templates.
In certain aspects, Relation ressembles the Query function and it has its origin in it. Relation executes Relational Algebra on a stack of relations. Relation is however a complete refactoring and has several improvements:
- Algebraic expression instead of RPN expressions
- Structures (if, while, function)
- Variables to write intermediate results
- A notebook paradigm (output can mix tables and text) and a template mechanism that integrates grouping and headers (as we did on Filemaker in the 1980s).
You find the full documentation of Relation on the website for Belle Nuit Relation, because this is also an Desktop and a command line application.
There is a playground special:relation
For 90%, the code is identical with the code described in Belle Nuit Relation. There are some differences, first because we are in a server environment and in a Sofawiki context, second because some instructions are not yet ported.
Belle Nuit Relation provides no instruction to create a relation from Sofawiki fields. Filter fills this gap. You provide the names of the columns and for each name you can give a hint as a quoted strings. Filter returns a relation with all tuples where the value of the field is url-equal (see swNameURL in inc/utilities.php) to the hint, means it contains the hint.
The hint is optional. If you do not provide a hint, the tuple always fullfills the requirement for this field even if the field is not present. If you provide an empty string, the field must be present, but can be empty.
You can use the wildcard * at the end of the list, but only if you provide a non-empty hint for at least one field.
The internal fields with the prefix _ (like _name) can be used.
Filter finds internally all results but returns only results from namespaces that the user can access or that can be transcluded.
- filter title "house", director, year
- filter title "house", *
- filter sw_name, title "house", director, year "20"
Executes the page and then reads the fields. Pagename must be quoted and can be an expression. You do not define fields, they are all returned.
- virtual "All applications 2020"
You can either read from a comma separated or tab separated file from within the uploads, if the filename has either the extention ".csv" or ".txt". JSON is not yet ported.
You can read from the cache folder. If the file exists both in site/files and site/cache, the file with the newer modification date is used.
Or you can read from memory within the function, if the filename does not have an extension
- read "films.csv"
- read "films"
You can either write to the cache folder with an extensions ".csv" or ".txt. This allows you to cache complex relations you use often, but which do not update every second.
Or you can write to memory without extension. The lifetime is during the relation function, not the page.
- write "films.csv"
- write "films"
In Belle Nuit Relation the only parameter is a number which can limit the lines as in print 5.
In Sofawiki, you can also use the paramter 'csv', 'tab' and 'json' and you get a textarea with the formatted output to be used for copy paste.
- print 5
- print csv
Note that unlike the Query function, there is no output if you do not use at least once the print instruction.
The implementation uses sprintf formats instead of numberformat like Excel.
There is on special format you can use which is n where k is the number of decimals and decimalseparator and thousandseparator are single characters (feel free to propose a better format string syntax here).
It is important that the format only happens on output. During relational algebra, the full fractions are used. If you need to round, use round().
- format chf "%0.2f"
- format chd "n2. "
The template uses a page of the template namespace and the name does not need the extension ".txt".
- Relation Tutorial 3
- Relation Tutorial 4
- Relation Tutorial 5
- Relation Tutorial 6
- Relation Tutorial 7
- Relation Tutorial 8
- Relation Tutorial 9
- Relation Election 1
- Relation Election 2
- Relation Election 3
- Relation Election 4
- Relation Election 5
- Relation Election 6
- Relation filter and virtual
- Relation template