ISO/IEC 13249-1:2016

INTERNATIONAL ISO/IEC
STANDARD 13249-1
Fourth edition
2016-06-01
Information technology — Database
languages — SQL multimedia and
application packages —
Part 1:
Framework
Technologies de l’information — Langages de bases de données —
Multimédia SQL et paquetages d’application —
Partie 1: Cadre général
Reference number
ISO/IEC 13249-1:2016(E)
©
ISO/IEC 2016

---------------------- Page: 1 ----------------------
ISO/IEC 13249-1:2016(E)

COPYRIGHT PROTECTED DOCUMENT
© ISO/IEC 2016, Published in Switzerland
All rights reserved. Unless otherwise specified, no part of this publication may be reproduced or utilized otherwise in any form
or by any means, electronic or mechanical, including photocopying, or posting on the internet or an intranet, without prior
written permission. Permission can be requested from either ISO at the address below or ISO’s member body in the country of
the requester.
ISO copyright office
Ch. de Blandonnet 8 • CP 401
CH-1214 Vernier, Geneva, Switzerland
Tel. +41 22 749 01 11
Fax +41 22 749 09 47
copyright@iso.org
www.iso.org
ii © ISO/IEC 2016 – All rights reserved

---------------------- Page: 2 ----------------------
ISO/IEC 13249-1:2016(E)

Contents Page
Foreword .iv
Introduction .v
1 Scope . 1
2 Normative references . 1
3 Terms and definitions . 1
3.1 Terms defined in ISO/IEC 9075-1 . 1
3.2 Terms defined in ISO/IEC 9075-2 . 2
3.3 Terms defined in this part of ISO/IEC 13249 . 2
4 Concepts . 3
4.1 Concepts taken from parts of ISO/IEC 9075 . 3
4.2 Generic kinds of data and common table structures . 4
4.3 Use of parts of ISO/IEC 9075 . 4
4.3.1 User-defined types and routines . 4
4.3.2 Views and routines . 5
4.3.3 Information schema and definition schema . 5
4.4 Implementation of ISO/IEC 13249 . 6
4.5 Use of ISO/IEC 13249. 6
4.5.1 Use of user defined types . 6
4.5.2 Use of views . . 7
5 Parts of ISO/IEC 13249 . 7
6 Notations and conventions used in other parts . 7
6.1 Notation . 7
6.1.1 Notation of user-defined types and routines . 7
6.1.2 Notation for defining a format of a value . 8
6.1.3 Use of syntax elements defined in parts of ISO/IEC 9075 . 8
6.2 Conventions . 9
6.2.1 Clause structure . 9
6.2.2 Organization of specifications . 9
6.2.3 Data type, attribute, SQL-invoked routine, and view identifiers .10
6.2.4 Parameter identifiers .10
6.2.5 Meta-variables .10
6.2.6 Symbols .10
6.2.7 Exceptions .10
6.2.8 Status codes . . .10
7 Implementation requirements .11
7.1 General .11
7.1.1 Schemas .11
7.1.2 USAGE privileges on user-defined types .11
7.1.3 UNDER privileges on user-defined types .11
7.1.4 EXECUTE privileges on routines .11
7.1.5 SELECT privilege on views .12
7.1.6 DELETE, INSERT, and UPDATE privilege on views .12
8 Conformance .12
8.1 Implementations .12
8.2 Relationship to other International Standards .13
8.3 Claim of conformance .13
8.4 Extensions and options.13
Annex A (informative) ISO/IEC JTC1 formal procedures .14
© ISO/IEC 2016 – All rights reserved iii

---------------------- Page: 3 ----------------------
ISO/IEC 13249-1:2016(E)

Foreword
ISO (the International Organization for Standardization) and IEC (the International Electrotechnical
Commission) form the specialized system for worldwide standardization. National bodies that are
members of ISO or IEC participate in the development of International Standards through technical
committees established by the respective organization to deal with particular fields of technical
activity. ISO and IEC technical committees collaborate in fields of mutual interest. Other international
organizations, governmental and non-governmental, in liaison with ISO and IEC, also take part in the
work. In the field of information technology, ISO and IEC have established a joint technical committee,
ISO/IEC JTC 1.
The procedures used to develop this document and those intended for its further maintenance are
described in the ISO/IEC Directives, Part 1. In particular the different approval criteria needed for
the different types of document should be noted. This document was drafted in accordance with the
editorial rules of the ISO/IEC Directives, Part 2 (see www.iso.org/directives).
Attention is drawn to the possibility that some of the elements of this document may be the subject
of patent rights. ISO and IEC shall not be held responsible for identifying any or all such patent
rights. Details of any patent rights identified during the development of the document will be in the
Introduction and/or on the ISO list of patent declarations received (see www.iso.org/patents).
Any trade name used in this document is information given for the convenience of users and does not
constitute an endorsement.
For an explanation on the meaning of ISO specific terms and expressions related to conformity
assessment, as well as information about ISO’s adherence to the WTO principles in the Technical
Barriers to Trade (TBT) see the following URL: Foreword - Supplementary information
The committee responsible for this document is ISO/IEC JTC 1, Information technology, SC 32, Data
management and interchange.
This fourth edition cancels and replaces the third edition (ISO/IEC 13249-1:2007), which has been
technically revised.
ISO/IEC 13249 consists of the following parts, under the general title Information Technology ― Database
languages ― SQL multimedia and application packages:
— Part 1: Framework
— Part 2: Full-Text
— Part 3: Spatial
— Part 5: Still image
— Part 6: Data mining
— Part 7: History
Parts other than this part specify requirements, and all are dependent on various parts of ISO/IEC 9075
and also on this part of ISO/IEC 13249.
iv © ISO/IEC 2016 – All rights reserved

---------------------- Page: 4 ----------------------
ISO/IEC 13249-1:2016(E)

Introduction
The purpose of this part of ISO/IEC 13249 is to define multimedia and application specific types and
their associated routines using the user-defined features in parts of ISO/IEC 9075.
This document is based on the content of parts of ISO/IEC 9075, Information technology ― Database
languages ― SQL.
The organization of this part of ISO/IEC 13249 is as follows:
1) Clause 1 specifies the scope of this part of ISO/IEC 13249.
2) Clause 2 identifies additional International Standards that, through reference in ISO/IEC 13249,
constitute provisions of this part of ISO/IEC 13249, and hence to all parts of ISO/IEC 13249.
3) Clause 3 specifies terms and definitions used in ISO/IEC 13249.
4) Clause 4 describes the concepts used in ISO/IEC 13249.
5) Clause 5 summarizes the content of each of the parts of ISO/IEC 13249.
6) Clause 6 defines the notation and conventions used in other parts of ISO/IEC 13249.
7) Clause 7 describes the requirements relating to the implementation of ISO/IEC 13249.
8) Clause 8 specifies the conformance requirements for all or some of the parts of ISO/IEC 13249.
9) Annex A (Informative) describes the formal procedures for maintenance and interpretation of
ISO/IEC 13249.
© ISO/IEC 2016 – All rights reserved v

---------------------- Page: 5 ----------------------
INTERNATIONAL STANDARD ISO/IEC 13249-1:2016(E)
Information technology — Database languages — SQL
multimedia and application packages —
Part 1:
Framework
1 Scope
ISO/IEC 13249 defines a number of packages of generic data types and table structures common to
various kinds of data used in multimedia and application areas, to enable that data to be stored and
manipulated in an SQL database. The package in each subject area is defined as a part of ISO/IEC 13249.
This part of ISO/IEC 13249 defines those concepts, notations and conventions that are common to two
or more other parts of ISO/IEC 13249. In particular, it describes the way parts of ISO/IEC 9075 are used
to define the user-defined types and their behaviour and views as a representation of table structures
appropriate to each subject area.
2 Normative references
The following documents, in whole or in part, are normatively referenced in this document and are
indispensable for its application. For dated references, only the edition cited applies. For undated
references, the latest edition of the referenced document (including any amendments) applies.
ISO/IEC 9075-1, Information technology ― Database languages — SQL — Part 1: Framework
(SQL/Framework)
ISO/IEC 9075-2, Information technology ― Database languages — SQL — Part 2: Foundation
(SQL/Foundation)
ISO/IEC 9075-11, Information technology ― Database languages — SQL — Part 11: Information and
definition schemas (SQL/Schemata)
3 Terms and definitions
3.1 Terms defined in ISO/IEC 9075-1
For the purposes of this document, the following terms defined in ISO/IEC 9075-1 apply.
a) compilation unit
b) data type
c) descriptor
d) identifier
e) implementation-defined
f) implementation-dependent
g) instance (of a value)
h) null value
© ISO/IEC 2016 – All rights reserved 1

---------------------- Page: 6 ----------------------
ISO/IEC 13249-1:2016(E)

i) row
j) sequence
k) SQL-environment
l) SQL-implementation
m) SQL-statement
n) table
3.2 Terms defined in ISO/IEC 9075-2
For the purposes of this document, the following terms defined in ISO/IEC 9075-2 apply.
a) assignment
b) attribute
c) cardinality
d) constructor function
e) declared type
f) external routine
g) mutator function
h) observer function
i) signature (of an SQL-invoked routine)
j) SQL routine
k) SQL-invoked routine
l) subtype
m) supertype
n) type-preserving function
o) user-defined type
p) white space
3.3 Terms defined in this part of ISO/IEC 13249
For the purposes of this document, the following terms and definitions apply.
3.3.1
generic data type
kind of data not already defined in any part of ISO/IEC 9075 that is used in a wider context than a single
enterprise and for which there are advantages in standardizing the way it is represented
3.3.2
common table structure
set of a table name and a sequence of a column name and data type pairs of columns consisting of an
SQL table that is used in a wider context than a single enterprise and for which there are advantages in
standardizing the way it is represented
2 © ISO/IEC 2016 – All rights reserved

---------------------- Page: 7 ----------------------
ISO/IEC 13249-1:2016(E)

3.3.3
meta-variable
variable which is used to define implementation-dependent or implementation-defined constants
3.3.4
method
specific implementation of an action or transformation that a user-defined type performs or a
representation of values of that type is subject to
Note 1 to entry: With such user-defined methods for structured types, an SQL-implementation allows users to
extend the function of the database system in object-oriented manner.
4 Concepts
4.1 Concepts taken from parts of ISO/IEC 9075
The following concepts defined in parts of ISO/IEC 9075 are used either in this part of ISO/IEC 13249 or
in other parts of ISO/IEC 13249.
a) array
b) base table
c) catalog
d) character set
e) column
f) completion condition
g) definition schema
h) distinct type
i) exception condition
j) False
k) field
l) host language
m) information schema
n) maximal supertype
o) ordering function
p) predefined data type
q) privilege
r) source type
s) SQL-agent
t) SQL-data
u) SQL-invoked method
NOTE Throughout ISO/IEC 13249, the method is referred to SQL-invoked method.
© ISO/IEC 2016 – All rights reserved 3

---------------------- Page: 8 ----------------------
ISO/IEC 13249-1:2016(E)

v) structured type
w) subtype family
x) transform function
y) True
z) Unknown
aa) user-defined cast
bb) view
4.2 Generic kinds of data and common table structures
All parts of ISO/IEC 9075 define the Database Language SQL, which is a language used to define
and manipulate SQL-data. For the purposes of this part of ISO/IEC 13249, the SQL-data in an SQL-
environment is referred to as an SQL database.
Using the data definition facilities of parts of ISO/IEC 9075, an enterprise may develop an SQL database
based on the kinds of data chosen for some specific purpose determined by the particular requirements
of the enterprise.
Many kinds of data are used in a wider context than that of an individual enterprise, in which case there
are benefits in being able to use a generic specification for the definition and manipulation of these
kinds of data.
These benefits include the enabling of the following:
a) shared understanding of this data;
b) exchange of this data;
c) provision of common manipulation facilities to process this data.
Recognition of these benefits has resulted in publicly available specifications for generic data types and
common table structures in the subject areas referred to in Clause 1 of each part of ISO/IEC 13249.
Many enterprises have requirements to use such generic data types combined with their own
enterprise-specific data types and their own enterprise-specific tables with such common structures
in an SQL database. These requirements include the manipulation of component elements of a generic
data type and of a table with a common structure, enabling both the construction and recording of
user-defined types and of rows of tables within an SQL database, and the use of these components in
selection and retrieval of data from an SQL database. ISO/IEC 13249 addresses these requirements.
4.3 Use of parts of ISO/IEC 9075
4.3.1 User-defined types and routines
Parts of ISO/IEC 9075 include facilities for defining user-defined types. A user-defined type can be either
a distinct type or a structured type. A distinct type is based on some predefined data type. A structured
type has attributes specified as either SQL data types or other user-defined types. A structured type
may be defined as a subtype of another structured type, with inheritance of its attributes. A user-
defined type may be specified as the data type of a column of an SQL table.
A user-defined type can only be manipulated by associated routines.
An attribute has automatically an associated observer and mutator function to retrieve and change
its value. Further routines may be defined in which the body of the routine, being the component that
4 © ISO/IEC 2016 – All rights reserved

---------------------- Page: 9 ----------------------
ISO/IEC 13249-1:2016(E)

determines behaviour on invocation of the routine, is specified either by SQL statements or by reference
to an external routine prepared in some other programming language.
Each part of ISO/IEC 13249 defines a number of user-defined types and associated routines, defined
according to parts of ISO/IEC 9075. The types and routines of each part are intended for use with data
for a specific subject area. Each part constitutes a package that aims to satisfy the requirements for
including generic data types for that subject area in an SQL database. It does not define how data from
multiple subject areas may be combined.
Each user-defined type is fully defined using the SQL syntax of parts of ISO/IEC 9075.
For each routine there is a specification of its signature, which includes its name and all parameters
with their type. A routine body is specified either by SQL statements or by reference to a definition,
which is given either in some formal language or as descriptive text.
The definition of a user-defined type in a part of ISO/IEC 13249 can include user-defined cast functions
to convert between a value of the user-defined type and another data type.
The definition of a user-defined type in a part of ISO/IEC 13249 can include an ordering function to
specify the order of two values of the user-defined type.
4.3.2 Views and routines
ISO/IEC 9075-2 includes facilities for defining viewed tables which are derived from one or more base
tables or other viewed tables. A viewed table is also called simply a view.
ISO/IEC 13249 (all parts) can define views and base tables, which derive those views, for making
descriptors of persistent database objects with common table structures in the subject area. The only
purpose of defining the base tables is to provide a data model to support their deriving views and to
assist understanding.
Each view and base table is fully defined using SQL syntax of ISO/IEC 9075-2. The definition of a
base table includes its table name, a sequence of name and data type pairs of columns, and optional
constraints. The definition of a view includes its table name, a sequence of column names, and a query
expression which derives the view from base tables. The data types of columns of a view are derived
from those of columns of base tables in the query expression.
ISO/IEC 13249 (all parts) can also define routines for manipulating rows of the views. For each routine
there is a specification of its signature, which includes its routine name and a sequence of name and
data type pairs of parameters. A routine body is specified either by SQL statements or by reference to a
definition, which is given either in some formal language or as descriptive text.
4.3.3 Information schema and definition schema
ISO/IEC 9075-11 defines the Information Schema and the Definition Schema for making descriptors of
persistent database objects available to any application.
The Information Schema includes the descriptors of a number of schema objects including the
Information Schema itself, mostly view definitions, that together allow every descriptor in that catalog
to be accessed, but not changed, as though it was SQL-data. These views are defined in terms of the base
tables of the Definition Schema. The only purpose of the Definition Schema is to provide a data model to
support the Information Schema and to assist understanding.
ISO/IEC 13249 (all parts) may define Information Schema views and corresponding Definition Schema
base tables to provide information about the common manipulation facilities and data in the subject
area applications. Applications can obtain information about facilities and data supported by these
application packages by querying the application package’s Information Schema views. SELECT privilege
on all of Information Schema views may be granted to PUBLIC WITH GRANT OPTION so that they can be
queried by any user and so that SELECT privilege can be further granted on views that reference these
Information Schema views. Updating the data provided by these views is implementation-defined.
© ISO/IEC 2016 – All rights reserved 5

---------------------- Page: 10 ----------------------
ISO/IEC 13249-1:2016(E)

ISO/IEC 13249 (all parts) may define additional schemas to provide a data model to support
understanding of the related functions or required by an implementation of that part. When views are
provided in those schemas, these are enabled for access in the same way as any other tables. SELECT
privilege on all of views in these schemas may be granted to PUBLIC so that they can be queried.
4.4 Implementation of ISO/IEC 13249
An implementation that claims conformance to any part of ISO/IEC 13249 shall provide its users with the
capability of using the user-defined types according to the specifications of that part of ISO/IEC 13249.
An implementation of a part of ISO/IEC 13249 has the freedom to provide a routine in any way that has
the same effect as the specification of its body in that part. In particular, a specification of a routine
body using SQL does not have to be implemented exactly as given, but may be implemented by another
set of SQL statements or by an external routine having the same effect (see 8.1).
4.5 Use of ISO/IEC 13249
4.5.1 Use of user defined types
The requirements described in 4.2 involve data for user-defined types being recorded and manipulated
with other enterprise data in an SQL database. Data can be in an SQL database only when a column of
a table is defined as an appropriate type to record it. The user-defined types defined in other parts of
ISO/IEC 13249 can be used for this purpose in at least three different ways:
a) as the basis of a column in a table;
b) as the basis of an attribute of a user-defined type;
c) as the basis of a new derived subtype.
In the first way, a column’s type is specified as one of the user-defined types of a part of ISO/IEC 13249.
This use of a user-defined type of ISO/IEC 13249 requires the USAGE privilege for the user-defined type
(see 7.1.2). The routines defined by ISO/IEC 13249 are used to manipulate values of the user-defined
type. Invocation of these routines requires the EXECUTE privilege for the routines (see 7.1.4).
In the second way, a new user-defined type is defined with an attribute that is one of the user-defined
types of ISO/IEC 13249. The new user-defined type may have additional attributes to satisfy specific
enterprise requirements. This use of a user-defined type of ISO/IEC 13249 requires the USAGE privilege
for the user-defined type (see 7.1.2). The new user-defined type can then be used as a column’s type,
as in the first way. New routines may be created for the new user-defined type. If these new routines
incorporate routines defined by ISO/IEC 13249 to manipulate values of the attribute that is one of the
user-defined types of ISO/IEC 13249, then the EXECUTE privilege is required for the incorporated
routines in order to create the new routine (see 7.1.4).
In the third way, a new user-defined type is defined as a subtype of one of the user-defined types of
a part of ISO/IEC 13249. The new user-defined type may have additional attributes to satisfy specific
enterprise requirements. This use of a user-defined type of ISO/IEC 13249 requires the UNDER privilege
for the user-defined type (see 7.1.3). The new user-defined type can then be used as a column’s type, as
in the first way. The routines defined by ISO/IEC 13249 can be used to manipulate the new user-defined
type (though further routines may be needed). Invocation of these routines requires the EXECUTE
privilege for the routines (see 7.1.4).
Whichever way is considered most appropriate for a specific enterprise context, ISO/IEC 13249 does
not define the use of user-defined types other than in an SQL-environment.
An SQL-agent consists of one or more compilation units that interact with an SQL-environment. An SQL-
agent can interact only via predefined data types for which parts of ISO/IEC 9075 define a mapping for
the host language or programming language used to create the SQL-agent.
6 © ISO/IEC 2016 – All rights reserved

---------------------- Page: 11 ----------------------
ISO/IEC 13249-1:2016(E)

The definition of a user-defined type in a part of ISO/IEC 13249 can include transform functions to
convert between a value of the user-defined type and predefined types.
4.5.2 Use of views
The use of a view defined by ISO/IEC 13249 requires appropriate privileges. SELECT privilege on
the view is required for querying the view. EXECUTE privilege on the routine for manipulating the
view is required for invoking that routine. If the routine is SQL routine, then the definer can have the
EXECUTE privilege on the routi
...