SQL> CREATE TABLE t1 (
2 n NUMBER PRIMARY KEY
3 , v VARCHAR2(30)
4 , d DATE
5 )
6 /
Table created.
SQL> CREATE TABLE t2 (
2 n NUMBER PRIMARY KEY
3 , v VARCHAR2(30)
4 , d DATE
5 )
6 /
Table created.
SQL> CREATE OR REPLACE VIEW v1
2 AS
3 SELECT *
4 FROM t1
5 WHERE n = 1 OR n = 2
6 /
View created.
SQL> CREATE OR REPLACE VIEW v2
2 AS
3 SELECT *
4 FROM t2
5 WHERE n <= 10
6 /
View created.
SQL> CREATE OR REPLACE VIEW v
2 AS
3 SELECT t1.n alias_n
4 , ABS(t1.n) a
5 , t2.v alias_v
6 , LENGTH(t2.v) l
7 , t1.d alias_d
8 , TO_CHAR(t1.d, 'YYYY/MM/DD HH24:MI:SS') tc
9 , (t2.n) alias_p
10 , t2.n + 1 alias_n_plus
11 FROM v1 t1, v2 t2
12 WHERE t1.n = t2.n
13 /
View created.
SQL> SELECT column_name
2 , updatable
3 , insertable
4 , deletable
5 FROM user_updatable_columns
6 WHERE table_name = 'V'
7 /
COLUMN_NAME UPD INS DEL
------------------------------ --- --- ---
ALIAS_N YES YES YES
A NO NO NO
ALIAS_V YES YES YES
L NO NO NO
ALIAS_D YES YES YES
TC NO NO NO
ALIAS_P YES YES YES
ALIAS_N_PLUS NO NO NO
8 rows selected.
As you can see ALIAS_N -> V1.N -> T1.N, ALIAS_V -> V2.V -> T2.V. As you can guess the goal here to figure out the very base data columns like T1.N and T2.V and so on.
I'll update this post with a potential solution some time later. It's a relatively complex solution which requires a bit of understanding how some Oracle components work. It may not be a complete one but with some extra polishing and with the acceptance of certain limits (e.g. limits of Oracle RDBMS security) the ultimate goal can be achieved or somewhat achieved :-)
Update 04/30/2010:
REM
REM Updatable view: where the data lives.
REM
REM Views seeding example.
REM
REM This script creates a relatively complex set of views, which have
REM direct dependencies on tables (somewhat obvious) and other views.
REM Objects are residing in the local to the owner and other schemas,
REM referenced either explicitely via schema.object_name notation, or
REM via synonym, or public synonym.
REM
REM Please make sure that users are removed from the system after
REM playing with this script.
REM
DROP USER u1 CASCADE;
DROP USER u2 CASCADE;
DROP USER u3 CASCADE;
DROP USER u4 CASCADE;
DROP PUBLIC SYNONYM u1_v1;
GRANT RESOURCE TO u1 IDENTIFIED BY u1;
GRANT RESOURCE TO u2 IDENTIFIED BY u2;
GRANT RESOURCE TO u3 IDENTIFIED BY u3;
GRANT RESOURCE, SELECT ANY TABLE TO u4 IDENTIFIED BY u4;
CREATE TABLE u1.t1 (
n NUMBER PRIMARY KEY
, v VARCHAR2(30)
);
CREATE OR REPLACE VIEW u1.v1
AS
SELECT n v1_n, v v1_v
FROM u1.t1;
CREATE OR REPLACE PUBLIC SYNONYM u1_v1 FOR u1.v1;
GRANT ALL ON u1.v1 TO PUBLIC;
CREATE TABLE u2.t2 (
n NUMBER PRIMARY KEY
, v VARCHAR2(30)
);
CREATE OR REPLACE VIEW u2.v2
AS
SELECT t1.v1_n v
, t1.v1_v n
, u2_t2.n t2_n
, u2_t2.v t2_v
FROM u1_v1 t1
, u2.t2 u2_t2
WHERE t1.v1_n = u2_t2.n;
GRANT ALL ON u2.v2 TO u3;
CREATE OR REPLACE SYNONYM u3.u2_v2 FOR u2.v2;
CREATE TABLE u3.t3 (
n NUMBER PRIMARY KEY
, v VARCHAR2(30)
);
CREATE OR REPLACE VIEW u3.v3
AS
SELECT t3.n u3_v3_n
, t3.v u3_v3_v
, u2_v2.n u2_v2_n
, u2_v2.v u2_v2_v
, u2_v2.t2_n u1_v1_n
, u2_v2.t2_v u1_v1_v
FROM u3.t3
, u2_v2
WHERE u2_v2.n = t3.n;
CREATE OR REPLACE VIEW u4.v4
AS
SELECT u4_v4.u1_v1_n c1
, u4_v4.u1_v1_v c2
, u4_v4.u2_v2_n c3
, u4_v4.u2_v2_v c4
, u4_v4.u3_v3_n c5
, u4_v4.u3_v3_v c6
FROM u3.v3 u4_v4
/
If such dependency is traversed by this would be observed:
SQL> @check U4 V4
ALIAS OWNER TABLE_NAME COLUMN_NAME INS UPD DEL OBJECT_TYPE RESOLVED_FROM
--------------- --------------- --------------- --------------- --- --- --- --------------- --------------------
C1 U3 V3 U1_V1_N YES YES YES VIEW
C2 U3 V3 U1_V1_V YES YES YES VIEW
C3 U3 V3 U2_V2_N YES YES YES VIEW
C4 U3 V3 U2_V2_V YES YES YES VIEW
C5 U3 V3 U3_V3_N NO NO NO VIEW
C6 U3 V3 U3_V3_V NO NO NO VIEW
6 rows selected.
SQL> @check U3 V3
ALIAS OWNER TABLE_NAME COLUMN_NAME INS UPD DEL OBJECT_TYPE RESOLVED_FROM
--------------- --------------- --------------- --------------- --- --- --- --------------- --------------------
U3_V3_N U3 T3 N NO NO NO TABLE
U3_V3_V U3 T3 V NO NO NO TABLE
U2_V2_N U2 V2 N YES YES YES SYNONYM "U3"."U2_V2"
U2_V2_V U2 V2 V YES YES YES SYNONYM "U3"."U2_V2"
U1_V1_N U2 V2 T2_N YES YES YES SYNONYM "U3"."U2_V2"
U1_V1_V U2 V2 T2_V YES YES YES SYNONYM "U3"."U2_V2"
6 rows selected.
SQL> @check U2 V2
ALIAS OWNER TABLE_NAME COLUMN_NAME INS UPD DEL OBJECT_TYPE RESOLVED_FROM
--------------- --------------- --------------- --------------- --- --- --- --------------- --------------------
V U1 V1 V1_N YES YES YES SYNONYM "PUBLIC"."U1_V1"
N U1 V1 V1_V YES YES YES SYNONYM "PUBLIC"."U1_V1"
T2_N U2 T2 N YES YES YES TABLE
T2_V U2 T2 V YES YES YES TABLE
SQL> @check U1 V1
ALIAS OWNER TABLE_NAME COLUMN_NAME INS UPD DEL OBJECT_TYPE RESOLVED_FROM
--------------- --------------- --------------- --------------- --- --- --- --------------- --------------------
V1_N U1 T1 N YES YES YES TABLE
V1_V U1 T1 V YES YES YES TABLE
A solution is published here.
2 comments:
Nice puzzle.
I've had a few thoughts, posted
http://www.google.com/notebook/public/04718150697827617824/BDQtjDQoQ2LqUwoQl?hl=en
I'm looking at parsing the results of an explain plan, rather than the view source.
Thanks! It's indeed an interesting task however I have to admit that it was not invented by me, it comes from here (beware it's in Russian).
I updated the post with the actual test case I have used to play with the views -> table columns resolution. You can try to see if the idea you had in mind would work for such test case. I used "table" || "underscore" || "column" notation just to illustrate the dependencies; the actual script does not do any kind of parsing dependencies like that.
Post a Comment