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.

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.

data

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

database

Connects to a SQlite database. Define the filename with a string.

in site/cache/ when you use a simple filename. If it does not exists, it will be created
anywhere on the website, if you use a path. In this case, the database is read-only.
in memory if you leave

Examples:

database "people.db"
database "site/files/uploaded.db"
database

Once you have connected to the database, you can communicate with it using the sql instruction or copy relations in to the database using the write database [tablename] instruction.

There can be only one database at once at the same time.

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.

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.

The hint can use boolean logic. Space is AND, pipe is OR.

filter title "house garden": "house" AND "garden"
filter title "house|garden": "house" OR "garden"
filter title "house|garden lake": "house" OR ("garden" AND "lake")

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 title "house", director, year "20"

filter index

The instruction filter index provides a fast search for fields if you know an exact value for a field

Examples:

filter index title, director, year "2020"

Use filter index when you

have queries that depend on a key you know exactly
the number of rows in the result is not too big.

User filter when you

need to make fuzzy searches
make global searches on all values of a field.

fulltext

Performs a search on the full text search index and returns the result as a relation with the columns score, url, lang, revision, title, body

Examples:

fulltext "geneva"
fulltext "geneva zurich"
fulltext "geneva zurich|bern"

fulltexturl

Faster version of fulltext returning only the url field.

functions

Wikitext functions wrap wikitext into a function. This makes the code more readable as you do not need to escape special characters

link(urlname) or * link(urlname, label) for internal links
tag(tagname) or tag(tagname,innertext) or tag(tagname, parameter, innertext) for HTML-tags
template(templatename, arg1, arg2 ...) for templates.

The functions link, max, min, substr, tag, template have variable parameter count.

import pagename

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

Example:

import "All applications 2020"

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

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.

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.

project aggreators

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, "::", ", ")

You can use everal statistical aggregators

project values product
project values gm // geometric mean
project values hm // harmonic mean
project values hill // true diversity
project values gini // Gini-Simpson
project values shannon // Shannon index

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

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

JSON files:

read "file.json" returns two columns path and value
read path pathotonode/ "file.json" returns all columns of a table that is of the level of this path. This must be an array and all substructures must have the same structure and only one level.

repeat

Repeat instructions a number of times. The counter starts at the initial values and goes down to zero.

set n = 5
repeat counter n
echo counter
end repeat

will print
5
4
3
2
1

The counter can be any name. You are not allowed to use a counter which is defined by set and you cannot change the value of the counter with set.

sql

Send queries to an SQLite databas connected with the database instruction. The entire query and execute syntax is accessible.

sql "SELECT * FROM people"
sql "SELECT * FROM sqlite_master" // list of all tables
sql "CREATE TABLE works IF NOT EXISTS (title, year)"
sql "UPDATE people SET age = age + 1"

NB: Each sql instruction is executed each time, so depending on context, use IF NOT EXISTS or DROP TABLE.
NB: Each sql instruction adds the result as a relation on the stack, if there are rows or if it starts with "SELECT".

template

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

virtual pagename

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

Example:

virtual "All applications 2020"

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

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"

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.