The Pyrrho Database Management System

Malcolm Crowe, University of the West of Scotland

 

Pyrrho SQL syntax

Data Manipulation

Insert = INSERT [WITH PROVENANCE string] [XMLOption] INTO Table_id [ Cols ] Value .

A simple case is INSERT INTO t VALUES (4,5) , or INSERT INTO t SELECT c,d FROM e .

UpdatePositioned = UPDATE [XMLOption] Table_id Assignment WHERE CURRENT OF Cursor_id .

UpdateSearched = UPDATE [XMLOption] Table_id Assignment [WhereClause] .

DeletePositioned = DELETE [XMLOption] FROM Table_id WHERE CURRENT OF Cursor_id .

DeleteSearched = DELETE [XMLOption] FROM Table_id [ WhereClause] .

CursorSpecification = [ XMLOption ] QueryExpression .

QueryExpression = QueryExpression Body  [ OrderByClause ] [ FetchFirstClause]  .

QueryExpressionBody = QueryTerm | QueryExpression ( UNION | EXCEPT ) [ ALL | DISTINCT ] QueryTerm .

DISTINCT is the default and discards duplicates from both operands.

QueryTerm = QueryPrimary | QueryTerm INTERSECT [ ALL | DISTINCT ] QueryPrimary .

DISTINCT is the default.

QueryPrimary = SimpleTable | '(' QueryExpressionBody [ OrderByClause ] [ FetchFirstClause] ')'.

SimpleTable = QuerySpecification | Value | TABLE id .

QuerySpecification = SELECT [ALL|DISTINCT] SelectList TableExpression .

SelectList = '*' | SelectItem { ',' SelectItem } .

SelectItem = Value [AS id ] | ([id{'.'id}'.']'*') .

TableExpression = FromClause [ WhereClause ] [ GroupByClause ] [ HavingClause ] [WindowClause].

GroupByClause and HavingClause are used with aggregate functions. WindowClause is used with window functions.

FromClause = FROM TableReference { ',' TableReference } .

WhereClause = WHERE BooleanExpr .

GroupByClause = GROUP BY (DISTINCT|ALL) GroupingSet { ',' GroupingSet } .

GroupingSet = OrdinaryGroup | RollCube | GroupingSpec | '(' ')' .

OrdinaryGroup = ColumnRef [ Collate ]
| '(' ColumnRef [ Collate ]  { ',' ColumnRef [ Collate ] } ')' .

HavingClause = HAVING BooleanExpr .

WindowClause = WINDOW WindowDef { ',' WindowDef } .

WindowDef = id AS '(' WindowDetails ')' .

WindowDetails = [Window_id ] [ PartitionClause ] [ OrderByClause ] [ WindowFrame ] .

PartitionClause = PARTITION BY OrdinaryGroup .

WindowFrame = (ROWS|RANGE) (WindowStart | WindowBetween) [ Exclusion ] .

WindowStart = ((Value|UNBOUNDED) PRECEDING)|(CURRENT ROW) .

WindowBetween = BETWEEN WindowBound AND WindowBound .

WindowBound = WindowStart | ((Value|UNBOUNDED) FOLLOWING).

Exclusion = EXCLUDE ((CURRENT ROW)|GROUP|TIES|(NO OTHERS)) .

TableReference = TableFactor Alias | JoinedTable .

TableFactor = Table_id [FOR SYSTEM_TIME [TimePeriodSpecification]]
| View_id
| ROWS'('int[',' int]')'
| Table_FunctionCall
| Subquery
| '('TableReference ')'
| TABLE '(' Value ')'
| UNNEST '(' Value ')'
| XMLTABLE '(' [XMLOption] xml [PASSING  NamedValue {','  NamedValue}] XmlColumns ')'
| STATIC
| DocArray.

ROWS is a Pyrrho extension for table and cell logsand the last two options above are also Pyrrho-specific: static is for a single query that does not access a table, and the other allows a specific list of documents to be supplied (static is actually equaivalent to [{}] ).

Alias = [[AS] id [ Cols ]] .

TimePeriodSpecification = AS OF Value
| BETWEEN [ASYMMETRIC | SYMMETRIC] Value AND Value
| FROM Value TO Value .

This syntax is slightly more general than SQL2011.

Subquery = '('QueryExpression')' .

Subqueries return different sorts of values depending on the context, including simple values (scalars, structures, arrays, multisets, etc), rows and tables.

JoinedTable = TableReference CROSS JOIN TableFactor
| TableReference NATURAL [JoinType] JOIN TableFactor
| TableReference [JoinType] JOIN TableFactor USING '(' Cols ')' [ TO '(' Cols ')']
| TableReference [JoinType] JOIN TableReference ON SearchCondition .

The TO part is a Pyrrho extension allowing the use of adapter functions in named column joins: both sets of columns will be in the result rowset.

JoinType = INNER | ( LEFT | RIGHT | FULL ) [OUTER] .

SearchCondition = BooleanExpr .

OrderByClause = ORDER BY OrderSpec { ',' OrderSpec } .

OrderSpec = Value [ ASC | DESC ] [ NULLS ( FIRST | LAST )] .

The default order is ascending, nulls first.

FetchFirstClause = FETCH FIRST [ int ] (ROW|ROWS) ONLY .

XmlColumns = COLUMNS XmlColumn { ',' XmlColumn } .

XmlColumn = id Type [ DEFAULT Value ] [ PATH str ] .