The SQL Standard (ISO/IEC 9075)
S J Cannan
Technical Manager
Where it is
and
where it is going
The SQL Standard (ISO/IEC 9075)
Goal: Portability of SQL applications
Effect: Increase and stabilisation of the
database market
Mechanism: Joint efforts between implementers
and users
Joint effort among several countries
Past History
1987 SQL
1989 SQL + Integrity Enhancement
1992 SQL (2)
1995 SQL/CLI
1996 SQL/PSM
1999 SQL (3)
2000 SQL/OLB
2000 SQL/OLAP
2000 SQL/MED
The SQL Standard (ISO/IEC 9075)
Current Structure
ISO/IEC 9075-1 Framework
ISO/IEC 9075-2 Foundation
ISO/IEC 9075-3 Call-level interface
ISO/IEC 9075-4 Persistent Stored Modules
ISO/IEC 9075-5 Language Bindings
ISO/IEC 9075-9 Management of External Data
ISO/IEC 9075-10 Object Language Bindings
ISO/IEC 9075:Amd1 SQL/OLAP
The SQL Standard (ISO/IEC 9075)
The Standardisation Process
Working
Draft
Proposals
Committee
Draft
Final
Committee
Draft
Technical
Corrigenda
International
Standard
The SQL Standard (ISO/IEC 9075)
Yes/No
Draft
International
Standard
ISO/IEC 9075-1:
Framework (SQL/Framework)
Description of Parts
Terminology
Basic Concepts
Basic Conformance Clause
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-2:
Foundation (SQL/Foundation)
Data Definition
Data Manipulation
Data Access Control
Transaction Management
Information Schema
Miscellaneous
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-2: MOOSE
Large Objects
Arrays
User-defined types
Subtypes and inheritance
Encapsulation
Substitutability
Transforms
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-2:
Data Access
Data Manipulation Statements
INSERT
UPDATE
DELETE
SELECT
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-2:
Predicates
Comparison (=, <, >, <=, >=, <>)
value BETWEEN [A]SYMMETRIC value2 AND value3
value IN subquery or (value-list)
value [NOT] LIKE pattern [ESCAPE char]
row-value IS [NOT] NULL
row-value comp-op ALL|[SOME|ANY] subquery
EXISTS subquery
UNIQUE subquery
row-value MATCH [UNIQUE]
[SIMPLE|PARTIAL|FULL] subquery
row-value1 OVERLAPS row-value2
value [NOT] SIMILAR TO pattern [ESCAPE char]
row-value1 IS DISTINCT FROM row-value2
value IS [NOT] OF ( type-list )
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-2:
Cursors
Sensitivity
ASENSITIVE
SENSITIVE
INSENSITIVE
implies READ ONLY
Holdable
ORDER BY
columns not in select list
expressions
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-2:
Common Table Expressions
WITH New_Price AS (
SELECT Source, Destination, Carrier,
Cost * discount_rate AS New_Cost
FROM Flights f join Discounts d
on d.carrier=f.carrier )
SELECT a.Source, a.Destination, a.Carrier,
a.New_Cost, b.Carrier, b.New_Cost
FROM New_Price a, New_Price b
WHERE (a.Source, a.Destination)
=(b.Source, b.Destination)
AND a.Carrier <> b.Carrier
AND a.NewCost >= b.New_Cost
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-2:
Recursion
• Fixpoint theory
• Unique solutions
• Transformation on the right hand side of a recursive
definition must be monotonically increasing
Disallow:
• negation that crosses recursion
• aggregation that crosses recursion
• INTERSECT ALL
• EXCEPT ALL
• EXCEPT DISTINCT (on right hand side)
• FULL OUTER JOIN (LEFT|RIGHT with
recursion on right|left)
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-2: Recursion
ISO/IEC 9075-2:
Recursion
Mutual Recursion
WITH RECURSIVE
Even (N)
AS ( VALUES (0)
UNION
SELECT M +
FROM Odd
Odd (M)
AS ( SELECT N +
FROM Even
SELECT *
FROM Even
WHERE N < 12;
The SQL Standard (ISO/IEC 9075)
1
)
1
)
ISO/IEC 9075-2:
F261 Case expression
SELECT CASE
WHEN abbreviation = ‘CA’ THEN ‘California’
WHEN abbreviation = ‘SD’ THEN ‘South Dakota’
WHEN ...
ELSE ‘Unknown’
SELECT emp_name, deptno
END
FROM states FROM employee
WHERE ( CASE bonus + commission
WHERE ...
WHEN 0 THEN NULL
ELSE salary/(bonus+commission)
) > 10
Used to “implement” COALESCE
SELECT COALESCE (nickname, first_name, surname, ‘Unknown’)
FROM people
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-2:
OLAP
Enhances query capabilities
CUBE
ROLLUP
GROUPING SETS
Expressions in ORDER BY
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-2:
OLAP
Find the total sales per region and sales manager during each
month of 1996, with subtotals for each month, and concluding
with the grand total:
SELECT
FROM
WHERE
GROUP
month, region, sales_mgr, SUM (price)
Sales
year = 1996
BY ROLLUP (month, region, sales_mgr);
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-2:
OLAP
MONTH
April
April
April
April
April
April
May
May
May
May
May
-
REGION SALES_MGR SUM(price)
Central
Chow
25000
Central
Smith
15000
Central
40000
NorthWest Smith
15000
NorthWest 15000
55000
Central
Chow
25000
Central
25000
NorthWest Smith
15000
NorthWest 15000
40000
95000
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-2:
OLAP
Find the total sales per region and sales manager during each
month of 1996, with subtotals for each month, region and sales
manager and concluding with the grand total:
SELECT
FROM
WHERE
GROUP
month, region, sales_mgr, SUM(price)
Sales
year = 1996
BY CUBE (month, region, sales_mgr);
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-2:
OLAP
MONTH
April
April
April
April
April
April
April
April
May
May
May
May
May
May
May
The SQL Standard (ISO/IEC- 9075)
-
REGION
Central
Central
Central
NorthWest
NorthWest
Central
Central
NorthWest
NorthWest
Central
Central
Central
NorthWest
NorthWest
-
SALES_MGR
Chow
Smith
Smith
Chow
Smith
Chow
Smith
Chow
Smith
Chow
Smith
Smith
Chow
Smith
-
SUM(price)
25000
15000
40000
15000
15000
25000
30000
55000
25000
25000
15000
15000
25000
15000
40000
50000
15000
65000
30000
30000
50000
45000
95000
ISO/IEC 9075-2:
Data Protection
Privileges
GRANT, REVOKE
SELECT, UPDATE, DELETE, INSERT,
REFERENCE, UNDER, TRIGGER,
USAGE
Roles
CREATE, GRANT, REVOKE, DROP
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-2:
Roles
Enhanced security mechanisms
Simplifies definition of complex sets of privileges
CREATE/DROP role
GRANT/REVOKE privileges to roles
GRANT/REVOKE roles to users and other roles
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-2:
Data Description
The Information Schema
short name views
The Definition Schema
The SQL Standard (ISO/IEC 9075)

ISO/IEC 9075-2:
Miscellaneous
Transaction Statements
Connection Statements
Diagnostic Statements
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-3:
Call Level Interface (SQL/CLI)
“SQL for Shrink-wrapped software”
Resource control
SQL connection control
Execution of SQL statements
Diagnostics
SQL implementation information
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-3:
Call Level Interface (SQL/CLI)
Application
Call
Return
CLI Driver
Client
Call
Server
Return
Database Management System
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-4:
Persistent Stored Modules (SQL/PSM)
Flow of control
Assignments
Condition handlers
Signal and Resignal conditions
SQL path specification
Declarations
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-5:
Host Language Bindings (SQL/Bindings)
Embedded SQL
Static SQL
Dynamic SQL
Support for:
Ada, C, COBOL,
FORTRAN,
MUMPS, Pascal, PL/I
Direct SQL
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-5:
Host Language Bindings (SQL/Bindings)
Embedded SQL
Precompiler
Standard
Programming
Language
The SQL Standard (ISO/IEC 9075)
SQL Module
ISO/IEC 9075:
Conformance
• Core
• Features
• Packages
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075: Conformance
•
•
•
•
•
All of SQL:1992 Entry level
Some Transitional SQL:1992 features
Some Intermediate SQL:1992 features
Some Full SQL:1992 features
The following new features of SQL:1999
• Distinct data types
• WITH HOLD cursors
• SQL-invoked routines (no PATHs)
• CALL statement
• RETURN statement
• ROUTINES and PARAMETERS view
• SQL-invoked routines written in SQL and an external
language
• Value expression in order by clause
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075: Conformance
Packages
PKG001 Enhanced datetime facilities
PKG002 Enhanced integrity management
PKG003 OLAP facilities
PKG004 PSM
PKG005 CLI
PKG006 Basic object support
PKG007 Enhanced object support
PKG008 Active database
PKG009 SQL/MM support
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-9:
SQL/Management of External Data
Datalink data type
Foreign tables
Foreign servers
Foreign data wrappers
User mappings
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-10:
Object Language Bindings (SQL/OLB)
Objectives
Simple embedding of static SQL statements in
Java programs
Permit assembly of binary components produced
by different tools
Binary portability
Hardware
Operating Systems
Database Systems
Co-exist with JDBC
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-10:
Object Language Bindings (SQL/OLB)
Based on JSQL
Extends Java to include SQL constructs as statements
and expressions
A JSQL translator that transforms those JSQL
clauses into standard Java code that accesses the
database through a call interface
An alternative to JDBC (JavaSoft)
Static instead of Dynamic
Tighter integration
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-10:
Object Language Bindings (SQL/OLB)
Advantages
Simple
Static
compile time syntax and type checking
Strongly typed cursors
iterators
Pre-compilation
Permits vendor customisation
at deployment
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-10:
Object Language Bindings (SQL/OLB)
SQLJ translator framework
Java Class
Files
SQLJ
program
SQLChecker
Java Frontend
SQLJ
Profiles
SQLJ Translator
Profile
Customizer
Utility
SQLJ JAR FILE
SQLJ
Customizations
ISO/IEC 9075Amd1:
SQL/OLAP
• ROLLUP
•
•
•
•
•
•
CUBE
Unary grouped table aggregate functions
Binary grouped table aggregate functions
Inverse distribution functions
What-if functions
Numeric Functions
• Windows
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075Amd1:
SQL/OLAP
Windows
Cumulative sum and centred average:
SELECT SH.Territory, SH.Month, SH.Sales,
SUM (SH.Sales) OVER Wa AS Cumulative_sum,
AVG (SH.Sales) OVER Wb AS Centred_average
FROM Sales_history AS SH
WINDOW WRoot AS ( PARTITION BY SH.Territory
ORDER BY SH.Month ASC ),
Wa AS ( W12root ROWS UNBOUNDED PRECEDING ),
Wb AS ( W12root ROWS BETWEEN 1 PRECEDING
AND 1 FOLLOWING);
The SQL Standard (ISO/IEC 9075)
SQL 4
Revised SQL 3
+
SQL/JRT
?
Java in Database Procedures
Java Data types in SQL
SQL/XML
SQL/Replication
SQL/Temporal
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075:
SQL/JRT
Direct use of existing Java libraries
Any Java static method callable as a stored
procedure
Portable across DBMS’s
Deployable across tiers
Implementation transparent for user
Equivalent functionality
Body of SQLJ stored procedure routines can use
JDBC and/or SQLJ to access SQL, or Java
computation
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075:
SQL/JRT
– Use Java classes as SQL data types for:
• Columns of SQL tables and views.
• Parameters of SQL routines.
– Advantage to SQL:
• A type extension mechanism.
• A supplement to SQL:1999 ADTs.
– Advantage to Java:
• Direct support for Java objects in SQL databases.
• No need to map Java objects to SQL scalar or
BLOB types.
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075:
SQL/XML
Scope
– Specifications for the representation of SQL data (specifically rows and
tables of rows, as well as views and query results) in XML form, and
vice versa.
– Specifications associated with mapping SQL schemata to and from
XML schemata. This may include performing the mapping between
existing arbitrary XML and SQL schemata.
– Specifications for the representation of SQL Schemas in XML.
– Specifications for the representation of SQL actions (insert, update,
delete).
– Specifications for messaging for XML when used with SQL.
– Specifications of the (perhaps “a”) manner in which SQL language can
be used with XML.
The SQL Standard (ISO/IEC 9075)
SQL/Replication
An “Ideal”
Distributed
System
N-Tier
1-Tier
C/S
C/S
Mobile
Devices
Head-Office
Servers
Web &
Thin-Client
Relational Transport Layer
no new relational concepts; business-rule driven
every site a peer; update-anywhere
every site autonomous, all transactions local; no point of failure
automatic, transparent; just add a database wherever needed
all a user’s favourite tools: Java/VB/HTML/X/C++/Perl/Delphi/…,
1-tier/N-tier/Web/Windows/Unix…; OLTP/OLAP/DSS/…
The SQL Standard (ISO/IEC 9075)
SQL/Replication
Approaches
Central
Server
Scalability
one site
Autonomy/
Availability
Performance
one site
good
(if ‘local
and one server’
is enough)
Points of
Failure
one site
Data
immediate
Currency
The SQL Standard (ISO/IEC 9075)
Traditional
Synchronous: Queue-Based
Ideal
N-Phase
Asynchronous Asynchronous
Commit
Replication
Replication
< 20 sites
poor
poor for
updates
many
immediate
< 100 sites
(hub/spoke)
full, local
(if updateanywhere)
local
1000+
(auto detection,
auto config,
dyn. load bal.)
full, local
local
subset live
one or many any
(repl. clustering,
(for repl.)
propagated
failover)
parallelised
propagation
SQL/Replication
Rate of Change
Local
Database
Size
Mobile
Laptop
Workgroup
Server
Regional
Server
Headquarters /
Warehouse
The SQL Standard (ISO/IEC 9075)
Required
10% change Bandwidth for
per month
Trickle
4:1
Replication
compression (Office Hours)
50 MB
60K / day
Cell, 28.8K
modem
1 GB
1.1M / day
28.8K
modem
20 GB
23M / day
56K
modem
1 TB
1G / day
256K ISDN
conflicts
conflict avoidance
SQL/Replication
Decreasingly
Consistent
Image
Single
System
Image
Increasing
Propagation
Time
log replay
net change
hub-and-spoke
parallelised
single server, or 2PC
“multi-master,” update-anywhere
single “master” for all changes
The SQL Standard (ISO/IEC 9075)
ISO/IEC 9075-7:
Temporal (SQL/Temporal)
One Valid Time Line
Transaction Time Line
Upwards compatible
Structural constraints
Simple Syntax
No interest according to database vendors
Religious wars in standards body
The SQL Standard (ISO/IEC 9075)

SQL/MM
Multimedia & Application Packages
Based on the facilities of SQL:1999
A multi-part standard
Full Text
Data Mining
Spatial
Still Image
Still Graphics
Animation
Full Motion Video
Seismic
Music
end…
Descargar

BCS Belgium Presentation