[LightDB]Oracle视图兼容

支持的版本:24.1

背景:

  在Oracle中,视图与用于建立视图的对象是一种非强制的依赖关系,他们彼此之间可以独立地进行修改,删除,创建。

  如果视图依赖的对象被破坏后,影响了视图的定义,则视图会自动处于不正确的状态,当相应的对象恢复之后,视图自动恢复。

  LightDB自24.1版本之后,开始支持Oracle的视图特性。通过对此特性的支持,使得我们可以对视图所依赖的表进行如下操作:

  1,删除表;

  2,修改表的列名,列类型;

  3,删除视图依赖列;

  4,重新定义视图,并使得新定义的视图与之前的视图可以包含更少的列,或完全不一样的列;

  

  其中:1,2,3中对视图所依赖的表或视图的修改会使得视图失效,若后续操作使得对应的对象恢复到视图依赖的状态,则对应的视图也自动恢复。 

 

示例:

  1,创建表,视图

  

lightdb@oradb=# create table t(a int);
CREATE TABLE
lightdb@oradb=# create view v as select a from t;
CREATE VIEW
lightdb@oradb=# insert into t(a) values(1);
INSERT 0 1
lightdb@oradb=# select * from v;
 a
---
 1
(1 row)

  2,删除表,重建表

  

lightdb@oradb=# drop table t;
DROP TABLE
lightdb@oradb=# select * from v;
ERROR:  view broken: public.v
lightdb@oradb=# create table t(a int);
CREATE TABLE
lightdb@oradb=# insert into t(a) values(2);
INSERT 0 1
lightdb@oradb=# select * from v;
 a
---
 2
(1 row)

  

  3,修改列名,列类型

  

lightdb@oradb=# alter table t rename a to aa;
ALTER TABLE
lightdb@oradb=# select * from v;
ERROR:  view broken: public.v
lightdb@oradb=# alter table t add column a int;
ALTER TABLE
lightdb@oradb=# select * from v;
 a
---

(1 row)

lightdb@oradb=# alter table t modify a numeric;
ALTER TABLE
lightdb@oradb=# select * from v;
 a
---

(1 row)

lightdb@oradb=# \d+ v
                              View "public.v"
 Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
 a      | numeric |           |          |         | main    |
View definition:
 SELECT t.a
   FROM t;

  

  4,删除视图依赖列

  

lightdb@oradb=# alter table t drop a;
ALTER TABLE
lightdb@oradb=# \d+ t
                                     Table "public.t"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 aa     | integer |           |          |         | plain   |              |
Access method: heap

lightdb@oradb=# select * from v;
ERROR:  view broken: public.v
lightdb@oradb=# alter table t add a int;
ALTER TABLE
lightdb@oradb=# \d+ v
                              View "public.v"
 Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
 a      | integer |           |          |         | plain   |
View definition:
 SELECT t.a
   FROM t;

lightdb@oradb=# insert into t(a) values(1);
INSERT 0 1
lightdb@oradb=# select * from v;
 a
---

 1
(2 rows)

  

 5,重新定义视图

  

lightdb@oradb=# alter table t add b int;
ALTER TABLE
lightdb@oradb=# alter table t add c int;
ALTER TABLE
lightdb@oradb=# create or replace view v as select b from t;
CREATE VIEW
lightdb@oradb=# \d+ v
                              View "public.v"
 Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
 b      | integer |           |          |         | plain   |
View definition:
 SELECT t.b
   FROM t;

lightdb@oradb=# create or replace view v as select b,c from t;
CREATE VIEW

lightdb@oradb=# \d+ v
                              View "public.v"
 Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
 b      | integer |           |          |         | plain   |
 c      | integer |           |          |         | plain   |
View definition:
 SELECT t.b,
    t.c
   FROM t;

  

 注意:

  1,Oracle视图特性仅在Oracle模式下支持;

  2,对于已经坏掉的视图,导出再导入的时候,导入操作返回值非零。可以通过在lt_restore传入一个参数--ignore-restore-error去影响lt_restore返回值,之后再手动创建相关的缺失对象来恢复视图;

  3,当前的实现中,只考虑视图与视图,视图与表的依赖关系,对于视图与相关函数,类型,存储过程暂未作处理;

  

  

posted on 2024-01-11 14:12  aodb  阅读(13)  评论(0编辑  收藏  举报