1、 Reference number ISO/IEC TS 13249-7:2013(E) ISO/IEC 2013TECHNICAL SPECIFICATION ISO/IEC TS 13249-7 First edition 2013-02-15Information technology Database languages SQL multimedia and application packages Part 7: History Technologies de linformation Langages de bases de donnes Multimdia SQL et paqu
2、etages dapplication Partie 7: Historique 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 photocop
3、ying, or posting on the internet or an intranet, without prior written permission. Permission can be requested from either ISO at the address below or ISOs 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
4、 E-mail copyrightiso.org Web www.iso.org Published in Switzerland ii ISO/IEC 2013 All rights reservedISO/IEC TS 13249-7:2013(E) ISO/IEC 2013 All rights reserved iiiContents Page Foreword vi Introduction . vii 1 Scope 1 2 Normative references 1 3 Terms and definitions, concepts, notations and convent
5、ions . 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 take
6、n 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 Tabl
7、e . 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
8、 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
9、 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_CreateHistoryTableSequenceNumberGenerat
10、or 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_Create
11、PNormalizeMethod Procedure . 31 ISO/IEC TS 13249-7:2013(E) iv ISO/IEC 2013 All rights reserved5.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_DropHistoryTa
12、bleTypeMethod 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 4
13、1 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_GetHistoryRowSetIdentif
14、ierColumns 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 Schem
15、a 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_SucceedsO
16、rMeets 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 8
17、6 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 C
18、odes 94 10 Conformance 96 10.1 Requirements for conformance .96 10.2 Features of ISO/IEC 9075 required in this Technical Specification 96 ISO/IEC TS 13249-7:2013(E) ISO/IEC 2013 All rights reserved v10.3 Claims of conformance . 97 Annex A (informative) Example Application 98 A.1 Introduction 98 A.2
19、Storing History Rows 98 A.3 Example of Queries to History Table . 102 Bibliography 107 ISO/IEC TS 13249-7:2013(E) vi ISO/IEC 2013 All rights reservedForeword ISO (the International Organization for Standardization) and IEC (the International Electrotechnical Commission) form the specialized system f
20、or 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 collab
21、orate 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
22、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
23、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),
24、 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
25、 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 elem
26、ents 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 13
27、249 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 ISO/IEC TS 13249-7:2013(
28、E) ISO/IEC 2013 All rights reserved viiIntroduction 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 Lang
29、uage (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 o
30、f 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) Clau
31、se 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 Def
32、inition 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,
33、 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 routin
34、es 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 doe
35、s 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 t
36、ype. 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 tracke
37、d table. ISO/IEC TS 13249-7:2013(E) viii ISO/IEC 2013 All rights reservedb) 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 cons
38、traint 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. TECHNICAL SPECIFICATION ISO/IEC TS 13249-7:2013(E) ISO/IEC 2013 All rights reserved 1Info
39、rmation 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
40、 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
41、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
42、 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) IS
43、O/IEC 13249-1:2007, Information technology Database languages SQL multimedia and application packages Part 1: Framework ISO/IEC TS 13249-7:2013(E) 2 ISO/IEC 2013 All rights reserved3 Terms and definitions, concepts, notations and conventions 3.1 Terms and definitions 3.1.1 Terms and definitions prov
44、ided 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 Te
45、chnical 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
46、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 1i=n (where n is the number of periods), the begin time of the i-th period is greater
47、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 represen
48、ts 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 ISO/IEC TS 13249-7:2013(E) ISO/IEC 2013 All rights reserved 33.1.3.5 identifier column (of a tracked table) either the primary key or one of the set of columns
49、 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
