做公司网站要走哪些流程,成都便宜网站建设公司,网站制作的流程是什么,网站搭建培训学校查看Oracle、MySQL、PostGreSQL中的依赖关系
在有些程序员开发习惯中#xff0c;喜欢为了应用代码的简洁或复用#xff0c;而在数据库创建一个复杂关连查询的VIEW#xff0c;甚至是VIEW套VIEW嵌套使用#xff0c; 这里就有个问题如果上线后如发现依赖的表字段类型或长度不…查看Oracle、MySQL、PostGreSQL中的依赖关系
在有些程序员开发习惯中喜欢为了应用代码的简洁或复用而在数据库创建一个复杂关连查询的VIEW甚至是VIEW套VIEW嵌套使用 这里就有个问题如果上线后如发现依赖的表字段类型或长度不足时修复一个view依赖的table列时发现在oracle、mysql、postgresql本篇等同pg中有不同的表现 尤其是使用postgresql的用户需要格外注意 因为pg 不允许直接修改 学术派的严谨 子之琼浆,彼之砒霜. 喜忧参半。
Oracle
SQL CREATE TABLE t (id integer PRIMARY KEY);
Table created.SQL CREATE VIEW v AS SELECT * FROM t;
View created.SQL select status from user_objects where object_nameV;
STATUS
-------
VALIDSQL DROP TABLE T;
Table dropped.SQL select status from user_objects where object_nameV;
STATUS
-------
INVALIDSQL CREATE TABLE t (id integer PRIMARY KEY);
Table created.SQL select status from user_objects where object_nameV;
STATUS
-------
INVALIDSQL select * from v;
no rows selectedSQL select status from user_objects where object_nameV;
STATUS
-------
VALIDSQL alter table t add name varchar2(20);
Table altered.SQL select status from user_objects where object_nameV;
STATUS
-------
VALIDSQL desc vName Null? Type------------------------------- -------- ----------------------------1 ID NOT NULL NUMBER(38)SQL desc tName Null? Type------------------------------- -------- ----------------------------1 ID NOT NULL NUMBER(38)2 NAME VARCHAR2(20)SQL ddl v
PL/SQL procedure successfully completed.DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
--------------------------------------------------------------------------------CREATE OR REPLACE FORCE NONEDITIONABLE VIEW SYS.V (ID) ASSELECT ID FROM t;SQL alter table t modify id number(10);
Table altered.SQL select * from v;
no rows selectedSQL desc v;Name Null? Type------------------------------- -------- ----------------------------1 ID NOT NULL NUMBER(10)SQL alter table t rename to t100;
Table altered.SQL select status from user_objects where object_nameV;
STATUS
-------
INVALIDSQL select * from v;
select * from v*
ERROR at line 1:
ORA-04063: view SYS.V has errorsNote: 在oracle中VIEW依赖的TABLE发生DDL后会变为INVALID状态在下次调用时自动recompile 如果依赖的表名列名存在可以正常查询。修改依赖列长度正常修改在依赖表rename后VIEW无法查询.
查找依赖关系
SQL desc dba_dependenciesName Null? Type------------------------------- -------- ----------------------------1 OWNER NOT NULL VARCHAR2(128)2 NAME NOT NULL VARCHAR2(128)3 TYPE VARCHAR2(19)4 REFERENCED_OWNER VARCHAR2(128)5 REFERENCED_NAME VARCHAR2(128)6 REFERENCED_TYPE VARCHAR2(19)7 REFERENCED_LINK_NAME VARCHAR2(128)8 DEPENDENCY_TYPE VARCHAR2(4)SQL dep % v % %
OWNER DEPENDENT_NAME DEPENDENT_TY REF_OWNER REF_NAME REF_TYPE DEP_
---------------- ------------------------------ ------------ ---------------- ------------------------------ ------------ ----
SYS V VIEW SYS T100 TABLE HARDSQL l1 select2 owner dep_owner,3 name dep_name,4 type dep_type,5 referenced_owner dep_referenced_owner,6 referenced_name dep_referenced_name,7 referenced_type dep_referenced_type,8 dependency_type dep_dependency_type9 --10 --from dba_dependencies where owner like 1 and referenced_owner like 211 --from dba_dependencies where owner like 1 and name like 212 --13 from14 dba_dependencies15 where16 lower(owner) like lower(1)17 and lower(name) like lower(2)18 and lower(referenced_owner) like lower(3)19* and lower(referenced_name) like lower(4)
SQLMySQL
MYSQL_rootlocalhost [anbob] CREATE TABLE t (id integer PRIMARY KEY);
Query OK, 0 rows affected (0.05 sec)MYSQL_rootlocalhost [anbob] CREATE VIEW v AS SELECT * FROM t;
Query OK, 0 rows affected (0.02 sec)MYSQL_rootlocalhost [INFORMATION_SCHEMA] select * from tables where table_schemaanbob and TABLE_TYPEVIEW \G
*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: anbobTABLE_NAME: vTABLE_TYPE: VIEWENGINE: NULLVERSION: NULLROW_FORMAT: NULLTABLE_ROWS: NULLAVG_ROW_LENGTH: NULLDATA_LENGTH: NULL
MAX_DATA_LENGTH: NULLINDEX_LENGTH: NULLDATA_FREE: NULLAUTO_INCREMENT: NULLCREATE_TIME: 2022-08-17 18:03:22UPDATE_TIME: NULLCHECK_TIME: NULL
TABLE_COLLATION: NULLCHECKSUM: NULLCREATE_OPTIONS: NULLTABLE_COMMENT: VIEW
1 row in set (0.00 sec)MYSQL_rootlocalhost [anbob] DROP TABLE t;
Query OK, 0 rows affected (0.04 sec)MYSQL_rootlocalhost [anbob] select * from v;
ERROR 1356 (HY000): View anbob.v references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use themMYSQL_rootlocalhost [INFORMATION_SCHEMA] select * from tables where table_schemaanbob and TABLE_TYPEVIEW \G
*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: anbobTABLE_NAME: vTABLE_TYPE: VIEWENGINE: NULLVERSION: NULLROW_FORMAT: NULLTABLE_ROWS: NULLAVG_ROW_LENGTH: NULLDATA_LENGTH: NULL
MAX_DATA_LENGTH: NULLINDEX_LENGTH: NULLDATA_FREE: NULLAUTO_INCREMENT: NULLCREATE_TIME: 2022-08-17 18:03:22UPDATE_TIME: NULLCHECK_TIME: NULL
TABLE_COLLATION: NULLCHECKSUM: NULLCREATE_OPTIONS: NULLTABLE_COMMENT: View anbob.v references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
1 row in set, 1 warning (0.00 sec)MYSQL_rootlocalhost [INFORMATION_SCHEMA] CREATE TABLE anbob.t (id integer PRIMARY KEY);
Query OK, 0 rows affected (0.06 sec)MYSQL_rootlocalhost [INFORMATION_SCHEMA] select * from tables where table_schemaanbob and TABLE_TYPEVIEW \G
*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: anbobTABLE_NAME: vTABLE_TYPE: VIEWENGINE: NULLVERSION: NULLROW_FORMAT: NULLTABLE_ROWS: NULLAVG_ROW_LENGTH: NULLDATA_LENGTH: NULL
MAX_DATA_LENGTH: NULLINDEX_LENGTH: NULLDATA_FREE: NULLAUTO_INCREMENT: NULLCREATE_TIME: 2022-08-17 18:03:22UPDATE_TIME: NULLCHECK_TIME: NULL
TABLE_COLLATION: NULLCHECKSUM: NULLCREATE_OPTIONS: NULLTABLE_COMMENT: VIEW
1 row in set (0.00 sec)MYSQL_rootlocalhost [INFORMATION_SCHEMA] desc anbob.v- ;
----------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------
| id | int | NO | | NULL | |
----------------------------------------
1 row in set (0.00 sec)MYSQL_rootlocalhost [INFORMATION_SCHEMA] alter table anbob.t add name varchar(20);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0MYSQL_rootlocalhost [INFORMATION_SCHEMA] select * from tables where table_schemaanbob and TABLE_TYPEVIEW \G
*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: anbobTABLE_NAME: vTABLE_TYPE: VIEWENGINE: NULLVERSION: NULLROW_FORMAT: NULLTABLE_ROWS: NULLAVG_ROW_LENGTH: NULLDATA_LENGTH: NULL
MAX_DATA_LENGTH: NULLINDEX_LENGTH: NULLDATA_FREE: NULLAUTO_INCREMENT: NULLCREATE_TIME: 2022-08-17 18:03:22UPDATE_TIME: NULLCHECK_TIME: NULL
TABLE_COLLATION: NULLCHECKSUM: NULLCREATE_OPTIONS: NULLTABLE_COMMENT: VIEW
1 row in set (0.00 sec)MYSQL_rootlocalhost [INFORMATION_SCHEMA] alter table anbob.t rename to t100;
ERROR 1044 (42000): Access denied for user root% to database information_schemaMYSQL_rootlocalhost [INFORMATION_SCHEMA] alter table anbob.t rename to anbob.t100;
Query OK, 0 rows affected (0.20 sec)MYSQL_rootlocalhost [INFORMATION_SCHEMA] select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_COMMENT from tables where table_schemaanbob and TABLE_TYPEVIEW
and table_comme
*************************** 1. row ***************************TABLE_SCHEMA: anbobTABLE_NAME: vTABLE_TYPE: VIEW
TABLE_COMMENT: View anbob.v references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
1 row in set, 2 warnings (0.01 sec)Note: 在MySQL中和oracle基本一样 只是没有oracle的dba_object.status 是否invalid表示而是在INFORMATION_SCHEMA.TABLE_COMMENT有invalid关键字错误。
PostgreSQL
[local]:5432 postgresanbob# CREATE TABLE t (id integer PRIMARY KEY);
CREATE TABLE
[local]:5432 postgresanbob# CREATE VIEW v AS SELECT * FROM t;
CREATE VIEW
[local]:5432 postgresanbob# DROP TABLE t;
ERROR: cannot drop table t because other objects depend on it
DETAIL: view v depends on table t
HINT: Use DROP ... CASCADE to drop the dependent objects too.
[local]:5432 postgresanbob# alter table t add name varchar(20);
ALTER TABLE
[local]:5432 postgresanbob# select * from v;id
----
(0 rows)[local]:5432 postgresanbob# \d vView public.vColumn | Type | Collation | Nullable | Default
-----------------------------------------------id | integer | | |[local]:5432 postgresanbob# drop view v;
DROP VIEW
[local]:5432 postgresanbob# CREATE VIEW v AS SELECT * FROM t;
CREATE VIEW
[local]:5432 postgresanbob# \d vView public.vColumn | Type | Collation | Nullable | Default
-------------------------------------------------------------id | integer | | |name | character varying(20) | | |[local]:5432 postgresanbob# alter table t alter column name type varchar(40);
ERROR: cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view v depends on column name
[local]:5432 postgresanbob#[local]:5432 postgresanbob# create view vv as select * from v;
CREATE VIEW
[local]:5432 postgresanbob# alter table t alter column name type varchar(40);
ERROR: cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view v depends on column name
[local]:5432 postgresanbob# drop view v;
ERROR: cannot drop view v because other objects depend on it
DETAIL: view vv depends on view v
HINT: Use DROP ... CASCADE to drop the dependent objects too.[local]:5432 postgresanbob# explain analyze select * from vv where id1;QUERY PLAN
-----------------------------------------------------------------------------------------------------------Index Scan using t_pkey on t (cost0.15..8.17 rows1 width28) (actual time0.004..0.004 rows0 loops1)Index Cond: (id 1)Planning Time: 0.082 msExecution Time: 0.017 ms
(4 rows)[local]:5432 postgresanbob# alter table t rename to t100;
ALTER TABLE
[local]:5432 postgresanbob# select * from v;id | name
----------
(0 rows)Note: 在PG中view对其使用的对象没有直接依赖关系依赖对象实际上是视图的rewrite rule, 这增加了另一层间接性。 如果对view依赖的表或表列修改或删除时需要删除view和重建当然增加列并没有影响 报错中会提示表的直接依赖但递归包含并没有 可以想象如果在PG中大量使用view后期维护表时会有一些小麻烦这也可以简单认为pg中没有失效对象至少是view。
另外与oracle 和mysql不同的是pg中依赖的是对象oid, 在table对象rename后并不会影响view的使用,依赖的是OID 而不是NAME.
[local]:5432 postgresanbob# alter table t100 add address text;
ALTER TABLE
[local]:5432 postgresanbob# alter table t100 drop column address;
ALTER TABLEpostgres# CREATE TABLE t (id integer PRIMARY KEY);
CREATE TABLE
postgres# insert into t values(1),(2),(3);
INSERT 0 3
postgres# select * from t;id
----123
(3 rows)postgres# CREATE VIEW v AS SELECT * FROM t;
CREATE VIEW
postgres#
postgres# select * from v;id
----123
(3 rows)postgres# alter table t rename to tt;
ALTER TABLE
postgres# select * from v;id
----123
(3 rows)postgres#
postgres# 检查依赖关系
[local]:5432 postgresanbob# \d pg_dependTable pg_catalog.pg_dependColumn | Type | Collation | Nullable | Default
----------------------------------------------------classid | oid | | not null |objid | oid | | not null |objsubid | integer | | not null |refclassid | oid | | not null |refobjid | oid | | not null |refobjsubid | integer | | not null |deptype | char | | not null |classid存储包含依赖对象的目录表的对象 ID objid存储依赖对象的ID objsubid如果依赖项是针对列的则存储列号 refclassid,refobjid和refobjsubid上面的三列一样但是描述了依赖所引用的对象 deptype描述依赖的类型 查找表上的直接视图依赖项
[local]:5432 postgresanbob# SELECT v.oid::regclass AS view
anbob-# FROM pg_attribute AS a -- columns for the table
anbob-# JOIN pg_depend AS d -- objects that depend on the column
anbob-# ON d.refobjsubid a.attnum AND d.refobjid a.attrelid
anbob-# JOIN pg_rewrite AS r -- rules depending on the column
anbob-# ON r.oid d.objid
anbob-# JOIN pg_class AS v -- views for the rules
anbob-# ON v.oid r.ev_class
anbob-# WHERE v.relkind v -- only interested in views
anbob-# -- dependency must be a rule depending on a relation
anbob-# AND d.classid pg_rewrite::regclass
anbob-# AND d.refclassid pg_class::regclass
anbob-# AND d.deptype n -- normal dependency
anbob-# AND a.attrelid t100::regclass;view
------vv
(2 rows)查找对表列的直接依赖关系
[local]:5432 postgresanbob# SELECT v.oid::regclass AS view
anbob-# FROM pg_attribute AS a -- columns for the table
anbob-# JOIN pg_depend AS d -- objects that depend on the column
anbob-# ON d.refobjsubid a.attnum AND d.refobjid a.attrelid
anbob-# JOIN pg_rewrite AS r -- rules depending on the column
anbob-# ON r.oid d.objid
anbob-# JOIN pg_class AS v -- views for the rules
anbob-# ON v.oid r.ev_class
anbob-# WHERE v.relkind v -- only interested in views
anbob-# -- dependency must be a rule depending on a relation
anbob-# AND d.classid pg_rewrite::regclass
anbob-# AND d.refclassid pg_class::regclass
anbob-# AND d.deptype n -- normal dependency
anbob-# AND a.attrelid t100::regclass
anbob-# AND a.attname name;view
------v
(1 row)递归查找所有依赖视图
[local]:5432 postgresanbob# WITH RECURSIVE views AS (-- get the directly depending viewsSELECT v.oid::regclass AS view,v.relkind m AS is_materialized,1 AS levelFROM pg_depend AS dJOIN pg_rewrite AS rON r.oid d.objidJOIN pg_class AS vON v.oid r.ev_classWHERE v.relkind IN (v, m)AND d.classid pg_rewrite::regclassAND d.refclassid pg_class::regclassAND d.deptype nAND d.refobjid t100::regclass
UNION-- add the views that depend on theseSELECT v.oid::regclass,v.relkind m,views.level 1FROM viewsJOIN pg_depend AS dON d.refobjid views.viewJOIN pg_rewrite AS r ON r.oid d.objidJOIN pg_class AS vON v.oid r.ev_classWHERE v.relkind IN (v, m)AND d.classid pg_rewrite::regclassAND d.refclassid pg_class::regclassAND d.deptype nAND v.oid views.view -- avoid loop
)
SELECT format(CREATE%s VIEW %s AS%s,CASE WHEN is_materializedTHEN MATERIALIZEDELSE END,view,pg_get_viewdef(view))
FROM views
GROUP BY view, is_materialized
ORDER BY max(level);format
----------------------------------CREATE VIEW v AS SELECT t100.id,t100.name FROM t100;CREATE VIEW vv AS SELECT v.id, v.name FROM v;
(2 rows)
