English Français Deutsch

Differences SQL vs Sofawiki DB

In SQL, you can select, insert, update and delerte
In Sofawiki, you can only SELECT.

In SQL, fields are predefined in a schema.
In Sofawiki, there are no predefined fields. Fields are defined using a key-value pair on square brackets wherever you want on a wiki page.

Some text [[key::value]] some other text [[key2::value1::value2]]

A page can have more than one value for a field. The order of the fields does normally not matter. The order of repetition does however matter if you have multiple values for a field. In this case, all tuples of the same position in the field are part of the same tuple. If a field has less values than another, then the last value is repeated. Also, if a field has a null value, it must specified empty.

For our examples, we have added all tuples in one page. However, the result of the searches would have been the same if each tuple was on its own page.

Sofawiki calculates with relations. Relations are sets of tuples, so they do not have duplicates and the rows are not in a particular orders (unless you use ORDER or GROUP). Tuples in a relation have the same parameters, but which are not in a particular order (unless you use PROJECT).

The relation exist only once you SELECT or IMPORT. At this stage, missing fields are completed with empty values and missing values for the same field are padded (repeating the last value in that page).

By default, all SELECT search the entire wiki. You can however use the FROM clause to restrict the search to pages who begins with that name. If you want to restrict ro namespace Film, you write FROM film:. Note that you must have the authorization to view the pages with that name or you get an error.

All keywords must be UPPER CASE. The command keywords are CALC COPY CROSS EXCEPT FIELDS FIXED FORMAT GROUP IMPORT JOIN LEFTJOIN LIMIT ORDER OUTPUT POP PROJECT RENAME SELECT STYLE SUM SWAP TEMPLATE OUTERJOIN WHERE
Keywords in the command are ABS AVG COPY COUNT DESC NUMERIC FIELDS FIELDSCOMPACT FROM HTML LIST MAX MIN NULL POP REPLACE RAND REGEX ROWTEMPLATE SIGN SQRT STRLEN SUBSTR SWAP TAB TABHEADER TEXT TEXTSPACE TRIM URLIFY WHERE
Case matters when you name a key. You are save if you use only lowercase field names. Some keys are reserved:

By convention you would write a key in lowercase and without spaces, though spaces are allowed. The following characters however cannot be used neither by key nor by value: :[|.

Special characters can be expressed as tags

These tags are preserved until the last parser (style). The filter function is aware of the tags and replaces them by the real characters before the comparison.

In SQL, you specify * if you want all fields.
In Sofawiki, you must explicitely specify all fields of the tuple. You can make a query FIELDS to get all fields, or you can SELECT _fields to get the fields names of a specific query.