[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,当前的实现中,只考虑视图与视图,视图与表的依赖关系,对于视图与相关函数,类型,存储过程暂未作处理;