The Pyrrho Database Management System

Malcolm Crowe, University of the West of Scotland

 

SQL2011 compliance

Pyrrho conforms to the mandatory features in SQL2008 listed below except as noted:

E011 Numeric data types Precision if specified is in decimal digits.
E011-01 INTEGER and SMALLINT data types (including all spellings) INTEGER and INT are supported and are synonyms. The default precision is unbounded. SMALLINT is not supported
E011-02 REAL, DOUBLE PRECISION and FLOAT data types REAL and FLOAT are supported and are synonyms. Default precision is 13 digits. Precision greater than this can be specified. DOUBLE PRECISION is not supported (double what?)
E011-03 DECIMAL and NUMERIC data types DECIMAL and NUMERIC are synonyms. Arithmetic is exact unless constrained by the appropriate precision. Default precision is 13 digits. Precision greater than this can be specified.
E011-04 Arithmetic operators
E011-05 Numeric comparison
E011-06 Implicit casting among the numeric data types
E021 Character string types
E021-01 CHARACTER data type Represents an unbounded string by default. A maximum length can be specified.
E021-02 CHARACTER VARYING data type VARYING is permitted but ignored (all character string data types are variable length, possibly limited to a specified maximum length)
E021-03 Character literals
E021-04 CHARACTER_LENGTH function
E021-05 OCTET_LENGTH function
E021-06 SUBSTRING function
E021-07 Character concatenation
E021-08 UPPER and LOWER functions
E021-09 TRIM function
E021-10 Implicit casting among the fixed length and variable-length character string types
E021-11 POSITION function
E021-12 Character comparison
E031 Identifiers
E051 Basic query specification
E061 Basic predicates and search conditions
E071 Basic query expressions
E081 Basic privileges. Revoke semantics are altered to be more intuitive and effective.
E091 Set functions
E101 Basic data manipulation
E111 Single row SELECT statement
E121 Basic cursor support
E131 Null value support
E141 Basic integrity constraints . NO ACTION is not permitted: the default is RESTRICT
E151 Transaction support
E152 Basic Set Transaction statement Not supported. Serializable isolated transactions are supported (see T241 below). No other modes are permitted.
E153 Updatable queries with subqueries
E161 SQL comments using leading double minus
E171 SQLSTATE support
E182 Module language. Represents obsolete technology. ADO.NET and persistent SQL stored modules (SQL2008-4) are supported instead.
F031 Basic schema manipulation
F041 Basic joined table
F051 Basic date and time
F081 UNION and EXCEPT in views
F131 Grouped operations
F181 Multiple module support. Represents obsolete technology. Pyrrho supports multiple client applications.
F201 CAST function
F221 Explicit defaults. Defaults for columns can be any meaningful expression
F261 CASE expression
F311 Schema definition statement. CREATE SCHEMA is not supported. Other database objects belong to the database
F471 Scalar subquery values
F812 Basic flagging. Pyrrho's extensions such as Rename, ROWS, POSITION, PROVENANCE, NEXT, LAST, Representation extensions.
S011 Distinct data types
T321 Basic SQL-invoked routines. Parentheses are required even for empty parameter lists.
T631 IN predicate with one list element

Unless otherwise indicated conformance/non-conformance with a feature implies conformance/non-conformance with its subfeatures.

Pyrrho conforms to the following optional features in SQL2008:
B021 Direct SQL Using System.Data; (ADO.NET) class library
B128 Routine language SQL The keywords SET and CALL can usually be omitted within routines
F032 CASCADE drop behaviour
F033 ALTER TABLE statement: DROP COLUMN clause
F034 Extended REVOKE statement Semantics are intuitive
F052 Intervals and datetime arithmetic “Leap seconds” are not implemented
F121 Basic diagnostics management
F191 Referential delete actions NO ACTION and SET NULL are not supported.
F231 Privilege tables System table structure is modified from SQL2008
F251 Domain support
F291 UNIQUE predicate
F302 INTERSECT table operation
F304 EXCEPT ALL table operator
F312 MERGE statement
F321 User authorization
F341 Usage tables. Help with renaming of objects
F381 Extended schema manipulation
F391 Long identifiers. There is no practical limit on length of identifiers
F401 Extended joined table. TEMPORAL join is added, and adapter functions are supported for named column joins
F461 Named character sets. Names as specified in SQL2011 only
F491 Constraint management
F555 Enhanced seconds precision
F591 Derived tables
F641 Row and table constructors
F661 Simple tables
F671 Subqueries in CHECK
F672 Retrospective check constraints
F690 Collation support. CREATE COLLATION is not supported since space-padding behaviour is irrelevant in Pyrrho.
F701 Referential update actions NO ACTION and SET NULL are not supported since both conflict with referential integrity (a null value may be implied by SET DEFAULT).
F711 ALTER domain
F731 INSERT column privileges
F741 Referential MATCH types
F841 LIKE_REGEX predicate
F841 LIKE_REGEX predicate
F842 OCCURRENCE_REGEX function
F843 POSITION_REGEX function
F844 SUBSTRING_REGEX function
F846 Octet support in regular expression operators
F847 Nonconstant regular expressions
F850 Top-level <order by clause> in <query expression>
F851 <order by clause> in subqueries
F852 Top-level <order by clause> in views
F855 Nested <order by clause> in <query expression>
F856 Nested <fetch first clause> in <query expression>
F857 Top-level <fetch first clause> in <query expression>
F858 <fetch first clause> in subqueries
F859 Top-level <fetch first clause> in views
P002 Computational completeness
P006 Multiple assignment
S023 Basic structured types. Parentheses are required for method calls
S091 Basic array support
S092 Arrays of user-defined types
S095 Array constructors by query
S111 ONLY in query expressions
S151 Type predicate
S161 Subtype treatment
S211 User-defined cast functions
S251 User-defined orderings
S271 Basic multiset support
S272 Multisets of user-defined types
S275 Advanced multiset support
T031 BOOLEAN data type
T041 Basic LOB data type support Note that CLOB is equivalent to CHAR (no practical size limit)
T051 Row types
T061 UCS support Pyrrho uses the Normalize function but does not make it available to SQL.
T175 Generated columns
T191 Referential action RESTRICT
T201 Comparable data types for referential constraints
T211 Basic trigger capability
T241 START TRANSACTION statement
T281 SELECT privilege with column granularity
T326 Table functions
T331 Basic roles
T332 Extended roles
T431 Extended grouping function
T432 Nested and concatenated GROUPING SETS
T433 Multiargument GROUPING function
T434 GROUP BY DISTINCT
T441 ABS and MOD functions
T461 Symmetric BETWEEN predicate
T471 Result sets return value
T581 Regular expression substring function
T611 Elementary OLAP operations
T612 Advanced OLAP operations
T641 Multiple column assignment
X010 XML type
X031 XML Element
X032 XML Forest
X034 XMLAgg Order By is not implemented
X037 XML Processing Instruction
X080 Namespaces in XML publishing
X060 XML Parse: CONTENT option
X070 XML Serialize: CONTENT option