English Français Deutsch

Home Features Demos Download Installation User Manual Developer Manual Credits

Login

Relation function

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 resembles 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 a Desktop and a command line application.
https://www.belle-nuit.com/relation/

There is a playground special:relation

Implementation differences

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.

Expressions

Single { and } are possible, also single [ and ]. If you need double, quote them separately.
To avoid parsing conflicts, the expression engine has also predefined constants _pipe, _colon, _leftsquare, _rightsquare, _leftcurly, _rightcurly, _lt, _gt, _amp, _quote, _singlequote, _backslash, _slash.

Both examples are equivalent

  • update _name = "{"."{"._name."}"."}"
  • update _name = _leftcurly._leftcurly._name._rightcurly._rightcurly

The template function can embed templates and functions. Use template content as argument.

Many native wiki functions can also be used. You can use your own wiki functions, if the arity function is defined and returns at least 0.

See also Expression parser and Infinite and undefined

filter name (hint)? (, name (hint)?)*

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 string. 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 fulfills the requirement for this field even if the field is not present. If you provide a wildcard "*", the field must be present, but can be empty. The hint can also be a name for a variable you defined before.

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.
The internal field _paragraph splits up the content in paragraphs.
The internal field _word splits up the content in words.
The internal field _name speeds up search when a hint is set.
The internal field _any searches in all fields.

The key must start with a Latin letter and can contain only letters, numbers and underscore. For fields defined with other keys (not recommended) you can access them by replacing the invalid letters with underscore.

Filter finds internally all results but returns only results from namespaces that the user can access or that can be transcluded.

Examples:

  • filter title "house", director, year
  • filter title "house", *
  • filter sw_name, title "house", director, year "20"

virtual pagename

Executes the page and then reads the fields. Pagename must be quoted and can be an expression.

Example:

  • virtual "All applications 2020"

import pagename

Reads the fields of a page. Pagename must be quoted and can be an expression.

Example:

  • import "All applications 2020"

logs

Reads the log files to into a relation. The syntax is similar to fields with hints, but you cannot use the wildcard for all fields

  • logs file "2020-01", name, user, timestamp
  • logs file "2020-05", name, time, memory, error

The fields are timestamp, user, name, action, referer, time, memory, error.
The field file is a special field with the filename of the logfiles.

read

You can either read from a comma separated or tab separated file from within the uploads, if the filename has either the extension ".csv" or ".txt". The formats ".json" and ".xml" are flattened in a relation with the column path and value. The format ".html" reads the first table of a html page.
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.

Examples:

  • read "films.csv"
  • read "films"

You can also read from external sources, if you provide a swVirtualLinkHook function in configuration.php. This can be a very simple form

function swVirtualLinkHook($url,$foo,$bar)
{
  if (substr($url,0,8) == 'https://') return $url;
}

To recognize the file format, the url must end with the fileformat extension. See Relation external URL

write

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.

Examples:

  • write "films.csv"
  • write "films"

data

The data instruction can have the option csv. In this case, the lines are read as csv without evaluating expressions (faster).

project concat

You can use concat as aggregator to concatenate text with the separator "::". You can combine this with the replace function to get the separator of your choice

  • project kanton, partei concat
  • update partei_concat = replace(partei_concat, "::", ", ")

project pivot

You can create quickly a pivot table on two columns and one aggregation. The first column becomes the rows, the second column the columns. Each cell contains the aggregation, and there is a _all column and _row.

  • project pivot kanton, partei, fiktive_waehlende sum

If you define the format for the value in advance, it will be populated for all created columns

See example Relation pivot

project drop

You can remove a column with drop.

  • project drop bar

analyze

Analyze adds descriptive statistics for the datasets. The first column is expected to be the id, the following columns are the data. Empty fields are considered missing values.

  • analyze means calculates count, sum, mean, max, avg, median, stddev and var for each column
  • analyze correlation calculates r for each combination of columns.

print

In Belle Nuit Relation the only parameter is a number which can limit the lines as in print 5. In Sofawiki there are more options:

  • print csv: textarea with formatted output
  • print tab: textarea with formatted output
  • print csv: textarea with formatted output
  • print raw: direct text output of tab format
  • print space: direct text output separated by space and without header.
  • print grid: shows a table where you can explore big tables very quickly, with a table enhanced with some javascript code (in inc/skins/table.js).
    • The table can be sorted when you click on the header
    • The table has a filter field, that does a case-insensitive regex search on the entire row.
    • print grid 5: When you use a limit, only as many rows are shown at a time, but the filter still acts on all rows.
    • print grid edit: Makes the table editable and creates relation code for new table.

Note that unlike the Query function, there is no output if you do not use at least once the print instruction.

template

The template uses a page of the template namespace, and the name does not need the extension ".txt".

delegate

Delegate hands over the current relation to a template or a function with the given name as parameter. The result of the lines can be used for functions. Delegate is particularly useful for the Charts functions.

input variable default (, variable default)*

The relation query can be made interactive. The input instruction shows a form with variables where you define a default value and a submit button. The relation stops here, until the user uses the submit button.

Example:

  • input director "Godard", year "1959"

The default input type is text. You can use the name of the field with a qualifier to have special types

  • input sampletext_textareay "write here"
  • input options_select "1999|2000|2001"

The type select uses the list separated by the pipe characters.
In the following code, use the field without the qualifier

walk

Normally, all relation based instruction work globally on all tuples of a relation. Walk allows you to treat one tuple after another. This allows you to remain values of a row and to reuse them for the next row.
On the walk instruction, you add columns you may want to add. Other variables you create inside the walk loop are not used for the relation

  • walk rolling
  • set rolling = retain
  • set retain = retain + bar
  • end walk

Examples


Example for other code use


  • Text Style Checker 14 lines of relation code to implement a tool that uses DeepL and Diff to check the style of text

Examples for Rosetta Code


Not yet supported functions


  • beep

Execute

In the special:relation console you can also modify pages based on queries. See relational execute.