The Pyrrho Database Management System

Malcolm Crowe, University of the West of Scotland

 

Pyrrho SQL syntax

Data Definition

As is usual for a practical DBMS Pyrrho's Alter statements are richer than SQL2008.

Alter = ALTER DOMAIN id AlterDomain { ',' AlterDomain }
| ALTER FUNCTION id '(' Parameters ')' RETURNS Type AlterBody
| ALTER PROCEDURE id '(' Parameters ')' AlterBody
| ALTER Method AlterBody
| ALTER TABLE id AlterTable { ',' AlterTable }
| ALTER TRIGGER id Trigger
| ALTER TYPE id AlterType { ',' AlterType }
| ALTER VIEW id AlterView { ',' AlterView } .

Procedures, functions and methods are distinguished by their name and arity (number of parameters) .

Method = MethodType METHOD id '(' Parameters ')' [RETURNS Type] [FOR id].

Parameters = Parameter {',' Parameter } .

Parameter = id Type .

The specification of IN, OUT, INOUT and RESULT are not (yet) supported.

MethodType = [ OVERRIDING | INSTANCE | STATIC | CONSTRUCTOR ] .

The default method type is INSTANCE. All OVERRIDING methods are instance methods.

AlterDomain = SET DEFAULT Default
| DROP DEFAULT
| TYPE Type
| AlterCheck .

AlterBody = AlterOp { ',' AlterOp } .

AlterOp = TO id
| Statement
| [ADD|DROP] {Metadata}.

Default = Literal | DateTimeFunction | CURRENT_USER | CURRENT_ROLE | NULL | ARRAY'('')'|MULTISET'('')' .

AlterCheck = ADD CheckConstraint
| [ADD|DROP] {Metadata}
| DROP CONSTRAINT id .

CheckConstraint = [ CONSTRAINT id ] CHECK '(' [XMLOption] SearchCondition ')' .

XMLOption = WITH XMLNAMESPACES '(' XMLNDec {',' XMLNDec } ')' .

XMLNDec = (string AS id) | (DEFAULT string) | (NO DEFAULT) .

The following standard namespaces and prefixes are predefined:
'http://www.w3.org/1999/02/22-rdf-syntax-ns#' AS rdf
'http://www.w3.org/2000/01/rdf-schema#' AS rdfs
'http://www.w3.org/2001/XMLSchema#' AS xsd
'http://www.w3.org/2002/07/owl#' AS owl

AlterTable = TO id
| ADD ColumnDefinition
| ALTER [COLUMN] id AlterColumn { ',' AlterColumn }
| DROP [COLUMN] id DropAction
| (ADD|DROP) (TableConstraintDef | VersioningClause )
| ADD TablePeriodDefinition AddPeriodColumnList
| ALTER PERIOD id TO id
| DROP TablePeriodDefinition
| [ADD|DROP] { Metadata}
| AlterCheck .

AlterColumn = TO id
| POSITION int
| (SET|DROP) ColumnConstraint
| AlterDomain
| [ADD|DROP] {Metadata}
| GenerationRule .

AlterType = TO id
| ADD ( Member | Method )
| DROP ( Member_id | Routine)
| Representation
| [ADD|DROP] {Metadata}
| ALTER Member_id AlterMember { ',' AlterMember } .

Member = id Type [DEFAULT Value] Collate .

Use SET METHOD (see Rename above) to change the name of a method. Other details of a Method can be changed with the ALTER METHOD statement (see Alter above).

AlterMember = TO id
| TYPE Type
| [DROP] {Metadata}
| SET DEFAULT Value
| DROP DEFAULT .

AlterView = SET SOURCE TO QueryExpression
| TYPE Type
| TO id
| [ADD|DROP] {Metadata}.

Metadata = ATTRIBUTE | ENTITY | CAPTION | HISTOGRAM | LINE | POINTS | PIE | X | Y | JSON | CSV | INVERTS | MONOTONIC | string | iri | id .

Metadata is a Pyrrho extension. Attribute and Entity affect XML output, Caption, Histogram, Legend, Line, Points, Pie, X and Y affect HTML output. For RestViews, the string is the url for the view. For INVERTS the id should be the name of the function being inverted.

AddPeriodColumnList = ADD [COLUMN] Start_ColumnDefinition ADD [COLUMN] End_ColumnDefinition .

Create = CREATE ROLE id [ Description_string ]
| CREATE DOMAIN id [AS] DomainDefinition
| CREATE FUNCTION id '(' Parameters ')' RETURNS Type {Metadata} Statement
| CREATE ORDERING FOR UDType_id (EQUALS ONLY|ORDER FULL) BY Ordering
| CREATE PROCEDURE id '(' Parameters ')' Statement
| CREATE Method Statement
| CREATE TABLE id TableContents [UriType] {Metadata}
| CREATE TRIGGER id (BEFORE|(INSTEAD OF)|AFTER) Event ON id [ RefObj ] Trigger
| CREATE TYPE id [UNDER id| Representation) [ Method {',' Method} ]
| CREATE VIEW id ViewDefinition
| CREATE XMLNAMESPACES XMLNDec { ',' XMLNDec } .

Method bodies are specified by CREATE METHOD once the type has been created. . In Pyrrho types, UNDER or Representation must be specified (not both). CREATE XMLNAMESPACES  is for creating a persistent association of namespace uris with identifiers.

Representation = AS (StandardType | Table_id | '(' Member {',' Member }')')  [UriType] {CheckConstraint} .

UriType = [Abbrev_id] '^^' ([Namespace_id] ':' id | uri ) .

The UriType syntax here has been added to SQL2011 as has the availability of standard types in the representation clause. The Abbrev_id can only be supplied within a CREATE DOMAIN clause.

Ordering = (RELATIVE|MAP) WITH Routine
| STATE .

TableContents = '('TableClause {',' TableClause}') { VersioningClause }
| OF Type_id ['('TypedTableElement {',' TypedTableElement} ')' ]
| AS Subquery .

VersioningClause = WITH (SYSTEM|APPLICATION) VERSIONING .

WITH APPLICATION VERSIONING is Pyrrho specific.

TableClause = ColumnDefinition {Metadata}| TableConstraint | TablePeriodDefinition .

ColumnDefinition = id Type [DEFAULT Value] {ColumnConstraint|CheckConstraint} Collate
| id GenerationRule
| id Table_id '.' Column_id

The last rule here is a convenience form for lookup tables, e.g. a.b is shorthand for check(value in(select b from a)).

GenerationRule =  GENERATED ALWAYS AS '('Value')' [ UPDATE '(' Assignments ')' ]
| GENERATED ALWAYS AS ROW (START|END) .

The value expression for default and generation rule can be any expression that makes sense in the context (e.g. generated columns can refer to data outside the current row). However, the default and generated behaviour applies only to subsequently inserted records and updates. The update option is an innovation in Pyrrho. The second version is specified in SQL2011: for a new row the start time is initially the current time, and is updated to the system time when the row is committed.

ColumnConstraint = [CONSTRAINT id ] ColumnConstraintDef .

ColumnConstraintDef = NOT NULL
| PRIMARY KEY
| REFERENCES id [Cols] [USING (id|Values)] { ReferentialAction }
| UNIQUE .

TableConstraint = [CONSTRAINT id] TableConstraintDef .

TableConstraintDef = UNIQUE Cols
| PRIMARY KEY Cols
| FOREIGN KEY Cols REFERENCES Table_id [ Cols ] [USING (id|Values)] { ReferentialAction } .

The Using clauses here are extensions to SQL2011 behaviour.

TablePeriodDefinition = PERIOD FOR PeriodName '(' Column_id ',' Column_id ')' .

PeriodName = SYSTEM_TIME | id .

TypedTableElement = ColumnOptionsPart | TableConstraint .

ColumnOptionsPart = id WITH OPTIONS '(' ColumnOption { ',' ColumnOption } ')' .

ColumnOption = (SCOPE Table_id)|(DEFAULT Value) ColumnConstraint .

Values = '(' Value { ',' Value } ')' .

Cols = '(' ColRef { ',' ColRef } [',' PERIOD ApplicationTime_id ] �)� .

The period syntax here can only be used in a foreign key constraint declaration where both tables have application time period definitions, and allows them to be matched up.

ColRef = Column_id { '.' Field_id [AS Type]} .

The Field_id syntax is Pyrrho specific and can be used to reference fields of structured types or documents.

ReferentialAction = ON (DELETE|UPDATE) (CASCADE| SET DEFAULT|RESTRICT) .

The default ReferentialAction is RESTRICT. Constraints are retrospective: they cannot be applied if existing data conflicts with them.

ViewDefinition = [ViewSpecification] AS (QueryExpression|GET [USING Table_id]) {Metadata} .

ViewSpecification = Cols | OF Type_id | OF'('id Type {',' id Type} ')'.

Event = INSERT | DELETE | (UPDATE [ OF id { ',' id } ] ) .

RefObj = REFERENCING { (OLD|NEW)[ROW|TABLE][AS] id } .

The default is ROW.

Trigger = FOR EACH ROW [ TriggerCond ] (Statement | (BEGIN ATOMIC Statements END)) .

TriggerCond = WHEN '(' SearchCondition ')' .

DropStatement = DROP DropObject DropAction .

DropObject = ObjectName
| ORDERING FOR id
| ROLE id
| TRIGGER id
| XMLNAMESPACES (id|DEFAULT) { ',' (id|DEFAULT) .

DropAction = | RESTRICT | CASCADE .

The default DropAction is RESTRICT.

Rename =SET ObjectName TO id .