ISO/IEC TS 13249-7:2013

Information technology — Database languages — SQL multimedia and application packages — Part 7: History

The ISO/IEC 13249 series defines a number of packages of generic data types 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. ISO/IEC TS 13249-7:2013: defines concepts specific to ISO/IEC TS 13249-7:2013; defines history user-defined types and their associated routines.

Technologies de l'information — Langages de bases de données — Multimédia SQL et paquetages d'application — Partie 7: Historique

General Information

• Status: Withdrawn
• Publication Date: 12-Feb-2013
• Withdrawal Date: 12-Feb-2013
• ICS: 35.060 - Languages used in information technology
• Technical Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Drafting Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Current Stage: 9599 - Withdrawal of International Standard
• Completion Date: 01-Jul-2021

Standards Content (Sample)

TECHNICAL ISO/IEC
SPECIFICATION TS
13249-7
First edition
2013-02-15


Information technology — Database
languages — SQL multimedia and
application packages —
Part 7:
History
Technologies de l'information — Langages de bases de données —
Multimédia SQL et paquetages d'application —
Partie 7: Historique




Reference number
ISO/IEC TS 13249-7:2013(E)
©
ISO/IEC 2013

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

COPYRIGHT PROTECTED DOCUMENT


©  ISO/IEC 2013
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
Case postale 56  CH-1211 Geneva 20
Tel. + 41 22 749 01 11
Fax + 41 22 749 09 47
E-mail [e-mail]
Web www.iso.org
Published in Switzerland

ii © ISO/IEC 2013 – All rights reserved

---------------------- Page: 2 ----------------------
ISO/IEC TS 13249-7:2013(E)
Contents Page
Foreword . vi
Introduction . vii
1 Scope . 1
2 Normative references . 1
3 Terms and definitions, concepts, notations and conventions . 2
3.1 Terms and definitions . 2
3.1.1 Terms and definitions provided in ISO/IEC 9075-1 . 2
3.1.2 Terms and definitions provided in ISO/IEC 9075-2 . 2
3.1.3 Terms and definition provided in this Technical Specification . 2
3.2 Concepts . 3
3.2.1 Concepts taken from ISO/IEC 9075-1 . 3
3.2.2 Concepts taken from ISO/IEC 9075-2 . 4
3.2.3 Syntactic elements taken from ISO/IEC 9075-2 . 4
3.2.4 Other concepts . 5
3.2.5 Notations . 5
3.2.6 Notations provided in ISO/IEC 13249-1 . 5
3.2.7 Notations provided in ISO/IEC 13249-7 . 5
3.3 Conventions . 6
4 Concepts . 6
4.1 Overview . 6
4.1.1 Tracked Table and History Table . 6
4.1.2 Concept of Transaction Timestamp . 7
4.1.3 Operations on Tracked Table . 7
4.1.4 Operations on time periods . 8
4.1.5 Concept of Period Normalization . 11
4.2 Structure of History Table . 13
4.3 Creating History Table and Storing History Row in History Table . 14
4.4 Retrieving a History Table . 14
4.5 Types representing history rows . 14
4.5.1 HS_History type . 14
4.5.2 type . 17
4.6 Complementary SQL-invoked regular functions . 17
4.6.1 Constructor method of the HS_History type . 18
4.6.2 Methods of the HS_History type for treating a period . 18
4.6.3 Methods of the type . 19
4.7 The History Information Schema . 19
5 History Procedures . 19
5.1 HS_CreateHistory Procedure and its related Procedures . 19
5.1.1 HS_CreateHistory Procedure . 19
5.1.2 HS_CreateHistoryErrorCheck Procedure . 20
5.1.3 HS_CreateHistoryPrivilegeCheck procedure . 23
5.1.4 HS_CreateHistoryTableSequenceNumberGenerator procedure . 24
5.1.5 HS_CreateHistoryTableType Procedure . 25
5.1.6 HS_CreateHistoryTable Procedure . 26
5.1.7 HS_CreateInsertTrigger Procedure . 26
5.1.8 HS_CreateUpdateTrigger Procedure . 27
5.1.9 HS_CreateDeleteTrigger Procedure . 29
5.1.10 HS_CreateHistoryTableMethod Procedure . 30
5.1.11 HS_CreatePNormalizeMethod Procedure . 31
© ISO/IEC 2013 – All rights reserved iii

---------------------- Page: 3 ----------------------
ISO/IEC TS 13249-7:2013(E)
5.1.12 HS_InitializeHistoryTable Procedure .34
5.2 HS_DropHistory Procedure and its related Procedures .35
5.2.1 HS_DropHistory Procedure .35
5.2.2 HS_DropHistoryErrorCheck Procedure.35
5.2.3 HS_DropHistoryTableTypeMethod Procedure .36
5.2.4 HS_DropHistoryTrigger Procedure .37
5.2.5 HS_DropHistoryTable Procedure .38
5.2.6 HS_DropHistoryTableType Procedure .39
5.2.7 HS_DropHistoryTableSequenceNumberGenerator procedure .40
5.3 Utility Procedures for History .41
5.3.1 Functions for extracting an identifier .41
5.3.2 HS_CreateCommaSeparatedTrackedColumnList Procedure .44
5.3.3 HS_CreateCommaSeparatedTrackedColumnAndTypeList Procedure .45
5.3.4 Functions for constructing an identifier and .47
5.3.5 HS_GetPrimaryKeys function .50
5.3.6 HS_GetTransactionTimestamp function .51
5.3.7 HS_GetHistoryRowSetIdentifierColumns procedure .51
5.3.8 HS_CreateCommaSeparatedIdentifierColumnList procedure .54
5.3.9 HS_CreateIdentifierColumnSelfJoinCondition procedure .56
5.3.10 Functions for constructing an identifier literal .58
5.3.11 HS_CreateCommaSeparatedTrackedColumnLiteralList procedure .60
5.4 and .61
5.5 Schema for Type .61
5.6 .62
6 History Types .62
6.1 HS_History Type and Routines .62
6.1.1 HS_History Type .62
6.1.2 HS_History Method .66
6.1.3 HS_Overlaps Methods .67
6.1.4 HS_Meets Methods .70
6.1.5 HS_Precedes Methods .71
6.1.6 HS_PrecedesOrMeets Methods .72
6.1.7 HS_Succeeds Methods .73
6.1.8 HS_SucceedsOrMeets Methods .74
6.1.9 HS_Contains Methods .75
6.1.10 HS_Equals Methods .77
6.1.11 HS_MonthInterval Method .78
6.1.12 HS_DayInterval Method .79
6.1.13 HS_Intersect Methods .80
6.1.14 HS_Union Methods .82
6.1.15 HS_Except Methods .83
6.2 Type and Routines .85
6.2.1 Type .85
6.2.2 HS_HistoryTable Method .86
6.2.3 HS_PNormalize Methods .87
7 SQL/MM History Information Schema .92
7.1 Introduction .92
7.2 HS_TRACKED_TABLES view .93
7.3 HS_TRACKED_COLUMNS view .93
8 SQL/MM History Definition Schema .93
8.1 Introduction .93
8.2 HS_TRACKED_TABLES base table .93
8.3 HS_TRACKED_COLUMNS base table .94
9 Status Codes .94
10 Conformance .96
10.1 Requirements for conformance .96
10.2 Features of ISO/IEC 9075 required in this Technical Specification .96
iv © ISO/IEC 2013 – All rights reserved

---------------------- Page: 4 ----------------------
ISO/IEC TS 13249-7:2013(E)
10.3 Claims of conformance . 97
Annex A (informative) Example Application . 98
A.1 Introduction . 98
A.2 Storing History Rows . 98
A.3 Example of Queries to History Table . 102
Bibliography . 107

© ISO/IEC 2013 – All rights reserved v

---------------------- Page: 5 ----------------------
ISO/IEC TS 13249-7:2013(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.
International Standards are drafted in accordance with the rules given in the ISO/IEC Directives, Part 2.
The main task of the joint technical committee is to prepare International Standards. Draft International
Standards adopted by the joint technical committee are circulated to national bodies for voting. Publication as
an International Standard requires approval by at least 75 % of the national bodies casting a vote.
In other circumstances, particularly when there is an urgent market requirement for such documents, the joint
technical committee may decide to publish an ISO/IEC Technical Specification (ISO/IEC TS), which
represents an agreement between the members of the joint technical committee and is accepted for
publication if it is approved by 2/3 of the members of the committee casting a vote.
An ISO/IEC TS is reviewed after three years in order to decide whether it will be confirmed for a further three
years, revised to become an International Standard, or withdrawn. If the ISO/IEC TS is confirmed, it is
reviewed again after a further three years, at which time it must either be transformed into an International
Standard or be withdrawn.
Attention is drawn to the possibility that some of the elements of this document may be the subject of patent
rights. ISO shall not be held responsible for identifying any or all such patent rights.
ISO/IEC 13249-7 was prepared by Joint Technical Committee ISO/IEC JTC 1, Information technology,
Subcommittee SC 32, Data management and interchange.
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 [Technical Specification]
vi © ISO/IEC 2013 – All rights reserved

---------------------- Page: 6 ----------------------
ISO/IEC TS 13249-7:2013(E)
Introduction
The purpose of ISO/IEC 13249 is to define multimedia and application specific types and their associated
routines using the user-defined features in ISO/IEC 9075.
ISO/IEC 13249 is based on the content of ISO/IEC International Standard Database Language (SQL).
The organization of this Technical Specification is as follows:
1) Clause 1, "Scope", specifies the scope of this Technical Specification.
2) Clause 2, "Normative references", identifies additional standards that, through reference in this
Technical Specification, constitute provisions of this Technical Specification.
3) Clause 3, "Terms, definitions, notations, and conventions", defines the definitions, concepts,
notations and conventions used in this Technical Specification.
4) Clause 4, "Concepts", presents concepts used in the definition of this Technical Specification.
5) Clause 5, "History Procedures", defines the history associated routines.
6) Clause 6, "History Types", defines the user-defined types provided for the manipulation of history.
7) Clause 7, "SQL/MM History Information Schema" defines the SQL/MM History Information Schema.
8) Clause 8, "SQL/MM History Definition Schema" defines the SQL/MM History Definition Schema.
9) Clause 9, "Status Codes", defines the SQLSTATE codes used in this Technical Specification.
10) Clause 10, "Conformance", defines the criteria for conformance to this Technical Specification.
In the text of this Technical Specification, clauses begin a new page. Any resulting blank space is not
significant.
The history user-defined types and routines defined in this Technical Specification adhere to the following:
a) A history user-defined type and routine are generic to history data handling. History user-defined types
and routines provide the means to record changes to the rows of a persistent base table in an SQL
database, so that applications using such a persistent base table shall be completely independent of
whether there is any recording of changes. This means that, when changes are to be recorded, an
application does not need to be modified and its behaviour remains the same.
b) History user-defined types and routines provide the means to query the recorded changes for such a
table.
c) A history user-defined type does not redefine the database language SQL directly or in combination with
another history data type.
The scope of this Technical Specification is limited to support for history when there are no changes to the
definition of the tracked columns of a tracked table. The following operations are not supported in this
Technical Specification:
a) DROP COLUMN operation to a tracked column of a tracked table.
© ISO/IEC 2013 – All rights reserved vii

---------------------- Page: 7 ----------------------
ISO/IEC TS 13249-7:2013(E)
b) ALTER COLUMN operation to a tracked column of a tracked table except changes of the default value.
The scope of this Technical Specification is limited to support for history when a tracked table has at least one
unique constraint with NOT NULL that is not modified by any ALTER TABLE statements.
If a transaction does not have an isolation level that is SERIALIZABLE, the results in the recorded history are
implementation-dependent.

viii © ISO/IEC 2013 – All rights reserved

---------------------- Page: 8 ----------------------
TECHNICAL SPECIFICATION ISO/IEC TS 13249-7:2013(E)

Information technology — Database languages — SQL
multimedia and application packages —
Part 7:
History
1 Scope
The ISO/IEC 13249 series defines a number of packages of generic data types 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.
This Technical Specification:
a) defines concepts specific to this Technical Specification;
b) defines history user-defined types and their associated routines.
2 Normative references
The following referenced documents are indispensable for the application of this document. 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:2008, Information technology — Database languages — SQL — Part 1: Framework
(SQL/Framework)
ISO/IEC 9075-2:2008, Information technology — Database languages — SQL — Part 2: Foundation
(SQL/Foundation)
ISO/IEC 9075-4:2008, Information technology — Database languages — SQL — Part 4: Persistent Stored
Modules (SQL/PSM)
ISO/IEC 9075-11:2008, Information technology — Database languages — SQL — Part 11: Information and
Definition Schemas (SQL/Schemata)
ISO/IEC 13249-1:2007, Information technology — Database languages — SQL multimedia and application
packages — Part 1: Framework
© ISO/IEC 2013 – All rights reserved 1

---------------------- Page: 9 ----------------------
ISO/IEC TS 13249-7:2013(E)
3 Terms and definitions, concepts, notations and conventions
3.1 Terms and definitions
3.1.1 Terms and definitions provided in ISO/IEC 9075-1
This Technical Specification makes use of the following terms defined in ISO/IEC 9075-1:
a) atomic
b) fully qualified of a name of some SQL object
c) identify
d) object (as in 'x object')
e) persistent
f) SQL-session
3.1.2 Terms and definitions provided in ISO/IEC 9075-2
This Technical Specification makes us of the following terms defined in ISO/IEC 9075-2:
a) distinct (of a pair of comparable values)
b) qual (of a pair of comparable values)
c) dentical (of a pair of values)
d) SQL parameter
e) structured type
f) variable-length
3.1.3 Terms and definition provided in this Technical Specification
For the purposes of this document, the terms and definitions given in ISO/IEC 13249-1 and the following
apply:
3.1.3.1
contiguous periods
sequence of two or more periods, such that, for all 1 of the i-th period is greater than the begin time of the (i-1)-th period and is equal to the end time of the (i-1)-th
period
3.1.3.2
history row
row in a history table
3.1.3.3
history row set
set of rows in a history table that represent all the changes to a single row of a tracked table
3.1.3.4
history table
table that represents values of the tracked columns of a tracked table and the period during which all the
values in each row were present in the tracked table
2 © ISO/IEC 2013 – All rights reserved

---------------------- Page: 10 ----------------------
ISO/IEC TS 13249-7:2013(E)
3.1.3.5
identifier column (of a tracked table)
either the primary key or one of the set of columns defined as UNIQUE and NOT NULL
3.1.3.6
period
duration of time with a begin time and an end time
NOTE In this Technical Specification a period value is a half-open duration that includes the begin time but not the
end time.
3.1.3.7
period normalization
operation that makes one or more contiguous periods into a single period
3.1.3.8
period-normalized table
table resulting from selecting one or more columns in a history table, including the column(s) corresponding to
the unique constraint columns with NOT NULL of the tracked table, and applying period normalization to rows
that are otherwise not distinct
NOTE Each row in a period-normalized table is formed from one or more rows of a history table with the same values
in one or more specified columns that relate to a continuous period, which may either be a single period from one row or
contiguous periods from many rows.
3.1.3.9
tracked column
column of a tracked table for which changes are to be recorded
NOTE The tracked columns of a tracked table shall include unique constraint columns with NOT NULL of that table.
3.1.3.10
tracked row
row in a tracked table
3.1.3.11
tracked table
persistent base table for which changes are to be recorded for one or more tracked columns
3.1.3.12
transaction timestamp
timestamp value that is within the duration of an SQL-transaction
NOTE This value is implementation-dependent, preferably corresponding to the end of an SQL-transaction
3.2 Concepts
3.2.1 Concepts taken from ISO/IEC 9075-1
This Technical Specification makes use of the following concepts defined in ISO/IEC 9075-1:
a) assertion
b) domain
c) primary key
d) query
© ISO/IEC 2013 – All rights reserved 3

---------------------- Page: 11 ----------------------
ISO/IEC TS 13249-7:2013(E)
e) role
f) SQL-client module
g) SQL-schema
h) SQL-server module
i) SQL-transaction
j) SQLSTATE
k) trigger
l) unique constraint
3.2.2 Concepts taken from ISO/IEC 9075-2
This Technical Specification makes use of the following concepts defined in ISO/IEC 9075-2:
a) applicable role
b) authorization identifier
c) default unqualified schema name
d) default catalog name
e) enabled authorization identifier
f) parameter
g) procedure
h) sequence generator
i) SQL-path
j) SQL-session context
k) transaction timestamp
3.2.3 Syntactic elements taken from ISO/IEC 9075-2
This Technical Specification makes use of the following syntactic elements (BNF non-terminal symbols)
defined in ISO/IEC 9075-2:
a)
b)
c)
d)
e)
f)
4 © ISO/IEC 2013 – All rights reserved

---------------------- Page: 12 ----------------------
ISO/IEC TS 13249-7:2013(E)
g)
h)
i)
j)
k)
l)
m)
n) ...