`
brianyu
  • 浏览: 16463 次
  • 性别: Icon_minigender_1
  • 来自: Sydney
文章分类
社区版块
存档分类
最新评论

时态数据库(temporal database) 在 oracle 中的实现

Oracle 
阅读更多

时态数据库是种以时间为基础的数据库，它所实现的不仅仅是对当前数据库的处理，也可以对过去和未来进行处理。

A temporal database is a database with built-in time aspects, e.g. a temporal data model and a temporal version of structured query language.

 

1. 在oracle 中创建 Type

CREATE TYPE PeriodType AS OBJECT
(
  -------------------------------------------
  -- Section 1: Member variables
  -------------------------------------------  
-- Note1: This is a close-open period.
  -- Note2: This code is for Oracle
  m_start  DATE,	-- start  of the period
  m_end  DATE,	-- end of the period
. . . 
)

 2.上面的省略号中要插入member function.

  ---------------------------------------------------------------------------
  -- Section 2: Member functions to get the attribute of this object
  ---------------------------------------------------------------------------
  -- 2.1 public function: Get the length of this period. 
    --return -2 if this period is end-with-forever
    --return -1 if this period is invalid
  MEMBER FUNCTION PeriodLength RETURN NUMBER, 
  -- 2.2 public function: Check if the date is end with forever. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
  MEMBER FUNCTION IsEndWithForever RETURN INTEGER, 
  -- 2.3 private function: Check if the period is a valid period, that is m_end > m_begin. 
    --return 1 for valid, o for invalid.
  MEMBER FUNCTION IsValid RETURN INTEGER, 
  
  ---------------------------------------------------------------------------
  -- Section 3: Member functions to get the relationship with an instant point.
  ---------------------------------------------------------------------------
  -- 3.1 public function: Check if the date is in this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
    --Note: if the date is the end of the period, it is not in this period, 
    --because this is a close-open period.
  MEMBER FUNCTION IsDateIn(d DATE) RETURN INTEGER, 
  -- 3.2 public function: Check if the date is out of this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
    --Note: if the date is the end of the period, it is outside of the period, 
    --because this is a close-open period.
  MEMBER FUNCTION IsDateOut(d DATE) RETURN INTEGER, 
  -- 3.3 public function: Check if the date is same to the start point of this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
  MEMBER FUNCTION IsDateStart(d DATE) RETURN INTEGER, 
  -- 3.4 public function: Check if the date is same to the end point of this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
  MEMBER FUNCTION IsDateEnd(d DATE) RETURN INTEGER, 
  -- 3.5 public function: Check if the date is eailier than this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
  MEMBER FUNCTION IsDateBeforeStart(d DATE) RETURN INTEGER, 
  -- 3.6 public function: Check if the date is later than this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
  MEMBER FUNCTION IsDateAfterEnd(d DATE) RETURN INTEGER, 
  
  -- 3.7 public function: Get the interval between the date and the start of this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
  MEMBER FUNCTION DistanceToBegin(d DATE) RETURN NUMBER, 
  -- 3.8 public function: Get the interval between the date and the end of this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
	--Note: if either the date or the end of this period is MAX_TIME, this function returns -2
  MEMBER FUNCTION DistanceToEnd(d DATE) RETURN NUMBER, 
  -- 3.9 public function: Get the minimal interval between the date and the points in this period. 
  MEMBER FUNCTION DistanceToWholePeriod(d DATE) RETURN NUMBER, 

  ---------------------------------------------------------------------------
  -- Section 4: Member functions to get the relationship with an other period.
  ---------------------------------------------------------------------------
  -- 4.1 public function: check if the period is disjoint with this period, with a gap bigger than zero. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsDisjointWithGap(p PeriodType) RETURN INTEGER, 
  -- 4.2 public function: check if the period is disjoint with this period, without a gap. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsDisjointWithoutGap(p PeriodType) RETURN INTEGER, 
  -- 4.3 public function: check if the period is disjoint with this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsDisjoint(p PeriodType) RETURN INTEGER, 
  -- 4.4 public function: Get the gap getween the period and this period
  MEMBER FUNCTION GapLength(p PeriodType) RETURN NUMBER, 
  
  -- 4.5 public function: check if the period is same with this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsEqual(p PeriodType) RETURN INTEGER, 
  -- 4.6 public function: check if this period covers the period. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsCover(p PeriodType) RETURN INTEGER, 
  -- 4.7 public function: check if this period is covered by the period. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsCovered(p PeriodType) RETURN INTEGER, 
  -- 4.8 public function: check if the period is overlap with this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsOverlap(p PeriodType) RETURN INTEGER, 
  -- 4.9 public function: check if the period intersects with this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsIntersect(p PeriodType) RETURN INTEGER,
  
  -- 4.10 public function: Get the intersect length of the period and this period
    --NOTE: if both periods are end-with-forever, the function returns -1;
  MEMBER FUNCTION IntersectLength(p PeriodType) RETURN NUMBER, 

  -- 4.11 public constructor: Get the intersect period of the period and this period
  MEMBER FUNCTION IntercectPeriod(p PeriodType) RETURN PeriodType

3. 创建 global function

-- 5.1 private function: Check if the date is the max date time. 
CREATE OR REPLACE FUNCTION IsMaxDateTime(d Date) RETURN INTEGER IS 
BEGIN 
    IF TRUNC(d) = Date '9999-12-31' THEN
        RETURN 1;
    ELSE 
        RETURN 0;
    END IF;
END;
/
show errors

-- 5.2 private function: compare 2 dates. returns 1 if d1 > d2; returns 0 if d1 = d2; returns -1 if d1 < d2;
CREATE OR REPLACE FUNCTION CompareDates(d1 Date, d2 Date) RETURN INTEGER IS
BEGIN 
    IF IsMaxDateTime(d1) = 1 OR IsMaxDateTime(d2) = 1  THEN
        IF TRUNC(d1) > TRUNC(d2) THEN
            RETURN 1;
        ELSIF TRUNC(d1) = TRUNC(d2) THEN
            RETURN 0;
        ELSE
            RETURN -1;
        END IF;
    ELSE 
        IF d1 > d2 THEN
            RETURN 1;
        ELSIF d1 = d2 THEN
            RETURN 0;
        ELSE
            RETURN -1;
        END IF;
    END IF;
END;
/
show errors

CREATE OR REPLACE FUNCTION LengthBetweenDates(d1 Date, d2 Date) RETURN INTEGER IS
BEGIN 
    IF IsMaxDateTime(d1) = 1 THEN
        RETURN -2;
    ELSIF IsMaxDateTime(d2) = 1 THEN
        RETURN -2;
    ELSE 
        IF d1 > d2 THEN
            RETURN d1-d2;
        ELSE
            RETURN d2-d1;
        END IF;
    END IF;
END;
/
show errors

CREATE OR REPLACE FUNCTION MakePeriodFromStartEnd(d1 Date, d2 Date) RETURN PeriodType IS
BEGIN 
    IF CompareDates(d1, d2) = -1 THEN
        RETURN PeriodType(d1, d2);
    ELSE
        RETURN PeriodType(d2, d1);
    END IF;
END;
/
show errors

CREATE OR REPLACE FUNCTION MakePeriodFromStartLength(d_start Date, n_len NUMBER ) RETURN PeriodType IS
BEGIN 
    RETURN MakePeriodFromStartEnd(d_start, d_start+n_len);
END;
/
show errors
 

 4. 要实现这些member function

CREATE OR REPLACE TYPE BODY PeriodType AS 

    MEMBER FUNCTION IsValid RETURN INTEGER IS
    BEGIN
        IF IsMaxDateTime(m_start) = 1 THEN
            RETURN 0;
        END IF;

        IF m_end <= m_start THEN 
            RETURN 0;
        END IF;

        RETURN 1;
    END;
  
    MEMBER FUNCTION PeriodLength RETURN NUMBER IS 
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        RETURN LengthBetweenDates(m_end, m_start);
    END;
      
    MEMBER FUNCTION IsEndWithForever RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        
        RETURN IsMaxDateTime(m_end);
    END;
      
    MEMBER FUNCTION IsDateIn(d DATE) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        --if m_start > d, return false;
        IF m_start > d THEN 
            RETURN 0;               
        END IF;
        --if m_end < d, return false;
        IF CompareDates(m_end, d) = -1 THEN
            RETURN 0;               
        END IF;
        --if m_end = d, return false;
        IF CompareDates(m_end, d) = 0 THEN
            RETURN 0;               
        END IF;
        
        RETURN 1;
    END;

    MEMBER FUNCTION IsDateOut(d DATE) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
          RETURN -1;               
        END IF;

        IF IsDateIn(d) = 1 THEN
            RETURN 0;
        ELSE
            RETURN 1;
        END IF;
    END;

    MEMBER FUNCTION IsDateStart(d DATE) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
          RETURN -1;               
        END IF;

        IF m_start = d THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsDateEnd(d DATE) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
          RETURN -1;               
        END IF;

        IF CompareDates(m_end, d) = 0 THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsDateBeforeStart(d DATE) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
          RETURN -1;               
        END IF;

        IF m_start > d THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsDateAfterEnd(d DATE) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        IF CompareDates(m_end, d) = -1 THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION DistanceToBegin(d DATE) RETURN NUMBER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        RETURN LengthBetweenDates(m_start, d);
    END;

    MEMBER FUNCTION DistanceToEnd(d DATE) RETURN NUMBER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        RETURN LengthBetweenDates(m_end, d);
    END;

    MEMBER FUNCTION DistanceToWholePeriod(d DATE) RETURN NUMBER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        
        IF IsDateIn(d) = 1 THEN 
            RETURN 0;
        END IF;
        
        IF IsDateEnd(d) = 1 THEN 
            RETURN 0;
        END IF;
        
        IF IsDateBeforeStart(d) = 1 THEN 
            RETURN LengthBetweenDates(m_start, d);
        END IF;
        
        RETURN LengthBetweenDates(m_end, d);
    END;

    MEMBER FUNCTION IsDisjointWithGap(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        
        IF m_end < p.m_start THEN 
            RETURN 1;               
        ELSIF p.m_end < m_start THEN 
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;

    END;

    MEMBER FUNCTION IsDisjointWithoutGap(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        IF m_end = p.m_start THEN 
            RETURN 1;               
        ELSIF p.m_end = m_start THEN 
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsDisjoint(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        IF IsDisjointWithGap(p) = 1 THEN 
            RETURN 1;
        ELSIF IsDisjointWithoutGap(p) = 1 THEN 
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION GapLength(p PeriodType) RETURN NUMBER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        
        IF IsDisjointWithGap(p) = 0 THEN
            RETURN 0;
        ELSE
            IF m_start > p.m_end THEN
                RETURN m_start - p.m_end;
            ELSE
                RETURN p.m_start - m_end;
            END IF;
        END IF;
        
    END;

    MEMBER FUNCTION IsCover(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        IF m_start <= p.m_start THEN 
            IF CompareDates(m_end, p.m_end)=0 THEN
                RETURN 1;
            ELSIF CompareDates(m_end, p.m_end)=1 THEN
                RETURN 1;
            ELSE
                RETURN 0;
            END IF;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsCovered(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        IF p.IsCover(MakePeriodFromStartEnd(m_start, m_end)) = 1 THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsEqual(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        IF IsCover(p) = 1 AND IsCovered(p) = 1 THEN 
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsOverlap(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        
        IF m_start < p.m_end AND p.m_start < m_end THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsIntersect(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        
        IF IsDisjoint(p) = 0 THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IntercectPeriod(p PeriodType) RETURN PeriodType IS
    BEGIN 
        IF IsIntersect(p) = 0 THEN
            RETURN PeriodType(Date '9999-12-31', Date '9999-12-31');
        ELSE
            IF IsCover(p) = 1 THEN
                RETURN p;
            ELSIF IsCovered(p) = 1 THEN
                RETURN PeriodType(m_start, m_end);
            ELSE
                IF m_start < p.m_start THEN
                    RETURN PeriodType(p.m_start, m_end);
                ELSE
                    RETURN PeriodType(m_start, p.m_end);
                END IF;
            END IF;
        END IF;
        RETURN p;
    END;

    MEMBER FUNCTION IntersectLength(p PeriodType) RETURN NUMBER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        RETURN IntercectPeriod(p).PeriodLength();
    END;
END;
/
分享到：
| GPL 与 LGPL 的区别
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    SQL-Server.zip_temporal database_对象数据库_数据库_数据库 数据_时态数据库

    SQL Server虽然不是专门的时态数据库，但可以通过特定的设计和扩展来实现类似的功能。 描述中还提到了其他几种数据库类型： 1. 实时数据库：这类数据库以极高的速度处理和响应数据，常用于自动化和监控系统。 2. ...

    时态数据库语句

    时态数据库（Temporal Database）是一种特殊的数据库类型，它不仅存储数据的当前状态，还能记录数据的历史状态以及未来的预期状态。通过这种方式，时态数据库能够支持时间维度上的查询和分析，这对于需要追踪历史...

    论文研究-一种可行的时态数据库索引技术.pdf

    时态数据库（Temporal Database）是一种能够处理时间信息的数据模型，它不仅记录数据对象的当前状态，而且记录数据状态随时间的变化历史。时态数据库通过引入时间概念，使得系统能够查询对象在任意时间点或时间段的...

    22第22章 时态数据库.pdf

    在时态数据库中，可能需要针对时间维度添加特定的约束，比如确保时间区间不重叠、日期序列正确等。 还有“JOIN”操作，它用于将来自两个或多个表中的行按一定规则关联起来，这在处理时态数据时也很常见。例如，可能...

    高级数据库 数据技术

    同时，他引入了双时态概念，区分了有效时间和事务时间，前者描述对象在数据库中的生命周期，后者追踪数据库自身的历史。这一时期的工作构建了时态数据库的基础。 J. Clifford则在历史数据库领域做出了开创性工作，...

    oracle database 12c 升級考試指南1Z0-060

    Oracle Database 12c升级考试1Z0-060旨在验证考生在将现有Oracle数据库升级到Oracle Database 12c版本方面的知识与技能。此考试对于希望证明自己具备最新Oracle数据库管理技术的专业人士来说至关重要。 #### 本学习...

    时态GIS在ARCGIS平台中的实现

    ### 时态GIS在ARCGIS平台中的实现 #### 一、时态GIS简介 ##### 1.1.1 简介 时态地理信息系统（Temporal Geographic Information System，简称TGIS）是一种能够采集、存储、管理和分析地理实体随时间变化信息的...

    postgres-temporal-talk:我谈论Postgres和时态数据库

    时态数据库：理论和Postgres 这些是我在2019年2月21日在pdxpug上演讲的幻灯片和我的笔记，然后在2019年5月31日在渥太华的PGCon 2019上再次。 您还可以将，或 。 您也可以在我的网站上找到。 发展 您可以通过以下...

    Temporal Description Logics

    - **语义网**：在语义网中，时态描述逻辑被用来描述和推理关于Web资源的动态属性。 - **知识图谱**：时态描述逻辑可用于构建和维护包含时间信息的知识图谱。 - **智能信息系统**：时态描述逻辑在开发智能推荐系统...

    基于分布式内存数据库的移动对象全时态索引.pdf

    通过这些知识点，可以更深入地理解分布式内存数据库在处理移动对象全时态索引中的应用及其优化原理。同时，对三层存储结构的理解有助于设计和改进数据库存储机制，确保数据处理的高效性和可靠性。

    Temporal Fusion Transformers 的Pytorch实现

    在本文中，我们介绍了时态融合转换器（TFT）--一种基于注意力的新型架构，它将高性能多视距预测与对时态动态的可解释见解相结合。为了学习不同尺度的时间关系，TFT 利用递归层进行局部处理，并利用可解释的自我注意...

    时态空间数据库技术.pptx

    时态GIS（Temporal GIS）是实现这一目标的关键，它试图在时间维度上扩展传统的地理信息系统，但目前仍处于发展阶段，尚未有成熟的商业化产品出现。时态GIS的核心在于构建能准确表达时间语义和区分时间相关与无关信息...

    Temporal Logic and State Systems

    在计算机科学领域，特别是理论计算机科学中，时态逻辑（Temporal Logic）和状态系统（State Systems）是两个核心概念，它们在模型检测、程序验证、自动控制等多个方面发挥着至关重要的作用。本文将深入探讨这两个...

    地理信息系统时态特性的研究与实现

    在时态GIS的实际应用中，需要建立和维护空间数据库，管理历史数据，并实现高效的历史数据查询。传统的关系型数据库平台虽然能够支持时空数据模型的建立，但可能需要特别设计和优化以适应时态GIS的需求。在构建时态...

    Oracle Spatial Developer's Guide

    1. 数据存储：Oracle Spatial支持多种空间数据格式，如Shapefile、GML、KML等，可将这些数据转换并存储在关系数据库中。 2. 空间索引：使用R-树或SBT（Spatio-Temporal Bitmap）索引来快速定位和检索空间对象，提高...

    Temporal_ESA_PetriNet

    在时态Petri网中，每个转换都有一个与之关联的时间约束，表示该转换必须在特定的时间间隔内发生。这使得模型能够表达出实时行为，如任务的时限、响应时间和周期性事件。 3. **事件结构分析**： 事件结构分析...

    为数据赋能_腾讯TDSQL分布式金融级数据库前沿技术_李海翔@腾讯.pdf

    在本篇资源摘要中，我们将讨论腾讯TDSQL分布式金融级数据库的前沿技术，探讨其在金融业务中的应用和优势。 数据库的重要性 在现代金融业务中，数据库扮演着关键的角色。它是业务系统的核心组件，负责存储和管理...

    TEMPORAL LOGIC

    在自然语言处理和人工智能领域，时态逻辑可以帮助计算机理解自然语言中的时间概念，从而实现更准确的语言理解和生成。 从古代思想到现代人工智能的发展过程中，时态逻辑已经经历了若干阶段的演化。在古代，哲学家如...

Magicbox
Global site tag (gtag.js) - Google Analytics