Virtuoso SQL

Data Type Support

• Four Number Types, Including Decimal Floating Point
• Narrow and Wide Strings, Character and Binary
• Narrow and Wide LOB's, Character and Binary
• Date, Time, Timestamp, with Timezone
• ANY type for fully run-time typed columns and variables
• XML
• User Defined Types with Inheritance
• Arrays, Hash Tables and Streams

Supported SQL Features

• Foreign Key, Primary Key, Unique, Check Constraints
• Subqueries, Quantified Subqueries, Derived Tables
• Qualified Joins, Outer Joins, UNION, EXCEPT, INTERSECT with ALL or column match list
• Table Valued Functions - A View or Derived Table Can be a Procedure Call
• User Defined Types, Inheritance, Polymorphism, Late Binding
• Table Inheritance, UNDER Clause for defining subbtables.
• User defined aggregates
• Basic OLAP, CUBE and ROLLUP extension to GROUP BY, Over 20 Statistics Aggregates
• Row Level Triggers Before, After, Instead of Insert, Update, Delete, Column Sensitivity and Specifiable Order, After and Before Values
• Identity and Automatic Timestamp Columns, Sequences
• User Defined Functions and Procedures
• Flexible ALTER TABLE and ALTER TYPE
• TOP SKIP , DISTINCT optionally working together with ORDER BY

SQL Optimizer

Virtuoso has a cost based optimizer that uses statistics for deciding join order, join type, index usage and other execution plan matters. Virtuoso supports hash and loop joins, reusing hash join hash tables if the underlying data does not change and merge intersection of indices for multiple index lookup. The optimizer automatically recognizes loop invariants and calculates these as early as possible. A special explain function allows viewing the execution plan. Optimizer directives exist for direct control of join order, join type and index usage.

Security

Virttuoso implements role based security and optionally row level security based on user account and SQL object specific policies.

Role based security provides for arbitrary non-cyclic inheritance of roles, a single useror role belonging to multiple roles. A user or role is a valid grantee for table/view/column access, procedure execution and type instance creation or type inheritance.

Policy based security is enforced by the SQL compiler by inserting extra conditions into statements, depending on which user is preparing the statement. The tables or views can themselves be readable to a large group of users but compartmentalization is achieved by the database automatically adding extra conditions. For example, a policy procedure can look up the user's department and add conditions that limit the statement being compiled to data pertaining to the user's department.

Special hook functions are provided for user login and logout. These can for example consult an LDAP server for validating the credentials, perform extra logging or any other type of custom processing.

Procedure Language

The Virtuoso procedure language is an adaptation of PSM 96 with a C-like syntax.

• In, out and inout parameters, passed byposition or by name, optional parameters with defaults are supported
• Procedure language statements may occur in procedure, function, method or trigger bodies.
• Scrollable and forward only cursors can be defined inline in procedures
• Inline Select and BML statements as well as execution of dynamically prepared SQL strings
• Run time data types
• Full exception handling
• Returning result sets from procedures to client applications or enclosing select statements - Also known as table valued functions.
• Convenient syntax for common operations like looping over a cursor or array.
• A procedure executes with the effective privileges of its owner. Execute privileges on procedures can be granted to users or roles.

CategoryWebSite CategoryOpenSource CategoryVirtuoso