Skip to content

Commit 2fcca30

Browse files
author
xiaoyu
committed
fix: add DBA_CONS_COLUMNS view definition and update regression tests
1 parent d652536 commit 2fcca30

4 files changed

Lines changed: 190 additions & 0 deletions

File tree

‎contrib/ivorysql_ora/expected/ora_sysview.out‎

Lines changed: 62 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -137,3 +137,65 @@ WHERE OBJECT_NAME = 'FUNC_WITH_DEFAULT';
137137

138138
-- clean data
139139
DROP FUNCTION IF EXISTS FUNC_WITH_DEFAULT;
140+
-- Oracle dynamic views
141+
\d SYS.V$SESSION
142+
View "sys.v$session"
143+
Column | Type | Collation | Nullable | Default
144+
-------------------+------------------------+-----------+----------+---------
145+
sid | number | | |
146+
username | varchar2(128) | C | |
147+
status | varchar2(40) | | |
148+
machine | varchar2(128) | | |
149+
program | varchar2(256) | | |
150+
type | varchar2(30) | | |
151+
logon_time | pg_catalog.timestamptz | | |
152+
sql_exec_start | pg_catalog.timestamptz | | |
153+
sql_id | varchar2(19) | | |
154+
sql_text | varchar2(4000) | | |
155+
blocking_session | number | | |
156+
event | varchar2(64) | | |
157+
wait_class | varchar2(64) | | |
158+
transaction_start | pg_catalog.timestamptz | | |
159+
port | number | | |
160+
161+
\d SYS.V$PROCESS
162+
View "sys.v$process"
163+
Column | Type | Collation | Nullable | Default
164+
------------+---------------+-----------+----------+---------
165+
spid | number | | |
166+
username | varchar2(128) | C | |
167+
program | varchar2(256) | | |
168+
pname | varchar2(30) | | |
169+
background | varchar2(1) | | |
170+
171+
SELECT *
172+
FROM SYS.V$PARAMETER
173+
WHERE NAME IN ('listen_addresses','application_name','archive_command','archive_mode','block_size')
174+
ORDER BY NAME;
175+
name | type | value | default_value | isdefault | isses_modifiable | issys_modifiable | description
176+
------------------+------+------------------------+---------------+-----------+------------------+------------------+-------------------------------------------------------------------
177+
application_name | 2 | pg_regress/ora_sysview | | FALSE | TRUE | IMMEDIATE | Sets the application name to be reported in statistics and logs.
178+
archive_command | 2 | (disabled) | | FALSE | FALSE | IMMEDIATE | Sets the shell command that will be called to archive a WAL file.
179+
archive_mode | 2 | off | off | TRUE | FALSE | FALSE | Allows archiving of WAL files using "archive_command".
180+
block_size | 3 | 8192 | 8192 | TRUE | FALSE | FALSE | Shows the size of a disk block.
181+
listen_addresses | 2 | | localhost | FALSE | FALSE | FALSE | Sets the host name or IP address(es) to listen to.
182+
(5 rows)
183+
184+
-- Test DBA_CONS_COLUMNS view
185+
SELECT count(*) > 0 AS view_exists
186+
FROM information_schema.views
187+
WHERE table_schema = 'sys' AND table_name = 'dba_cons_columns';
188+
view_exists
189+
-------------
190+
t
191+
(1 row)
192+
193+
SELECT * FROM sys.dba_cons_columns LIMIT 5;
194+
owner | constraint_name | table_name | column_name | position | nullable
195+
--------------------+-------------------------------------+--------------------+-------------+----------+----------
196+
unknown (OID=9900) | UTL_FILE_DIRECTORY_DIRNAME_NOT_NULL | UTL_FILE_DIRECTORY | DIRNAME | 1 | Y
197+
unknown (OID=9900) | UTL_FILE_DIRECTORY_PKEY | UTL_FILE_DIRECTORY | DIRNAME | 1 | Y
198+
unknown (OID=2200) | NULL_SPECIAL_ID_NOT_NULL | NULL_SPECIAL | ID | 1 | Y
199+
unknown (OID=2200) | NULL_SPECIALBK_ID_NOT_NULL | NULL_SPECIALBK | ID | 1 | Y
200+
(4 rows)
201+

‎contrib/ivorysql_ora/sql/ora_sysview.sql‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -82,3 +82,18 @@ SELECT ARGUMENT_NAME, IN_OUT, POSITION, DEFAULTED FROM ALL_ARGUMENTS
8282
WHERE OBJECT_NAME = 'FUNC_WITH_DEFAULT';
8383
-- clean data
8484
DROP FUNCTION IF EXISTS FUNC_WITH_DEFAULT;
85+
86+
-- Oracle dynamic views
87+
\d SYS.V$SESSION
88+
\d SYS.V$PROCESS
89+
SELECT *
90+
FROM SYS.V$PARAMETER
91+
WHERE NAME IN ('listen_addresses','application_name','archive_command','archive_mode','block_size')
92+
ORDER BY NAME;
93+
94+
-- Test DBA_CONS_COLUMNS view
95+
SELECT count(*) > 0 AS view_exists
96+
FROM information_schema.views
97+
WHERE table_schema = 'sys' AND table_name = 'dba_cons_columns';
98+
99+
SELECT * FROM sys.dba_cons_columns LIMIT 5;

‎contrib/ivorysql_ora/src/sysview/sysview--1.0.sql‎

Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1192,3 +1192,100 @@ WHERE
11921192
AND PG_GET_USERBYID(C.RELOWNER) = CURRENT_USER;
11931193
GRANT SELECT ON SYS.USER_VIEWS TO PUBLIC;
11941194

1195+
------------------------------------------------
1196+
-- V$ DYNAMIC VIEWS
1197+
------------------------------------------------
1198+
1199+
-- V$SESSION:
1200+
-- 1. For SID we use PG_STAT_ACTIVITY.PID and do not set SERIAL#:
1201+
-- we should avoid implementing "killing by SID" without either an additional counter or second factor like PG_STAT_ACTIVITY.BACKEND_START.
1202+
-- 2. SQL_ID is based on PG_STAT_ACTIVITY.QUERY_ID which depends on compute_query_id setting: SQL_ID is NULL if compute_query_id is 'off'.
1203+
-- SQL_ID is incompatible with Oracle because QUERY_ID a integer string in PostgreSQL and in Oracle SQL_ID is 13-character base32-encoded
1204+
-- hash.
1205+
--
1206+
-- We do not grant SELECT privilege on V$SESSION to PUBLIC because SQL_TEXT is retrieved.
1207+
-- We do not grant SELECT privilege on V$PROCESS or on V$PARAMETER to PUBLIC
1208+
-- because it contradicts with least security privilege principle.
1209+
1210+
CREATE OR REPLACE VIEW SYS.V$SESSION AS
1211+
SELECT
1212+
PG_STAT_ACTIVITY.PID::NUMBER AS SID,
1213+
USENAME::VARCHAR2(128) AS USERNAME,
1214+
CASE WHEN STATE = 'active' OR STATE = 'fastpath function call' THEN 'ACTIVE'
1215+
WHEN STATE = 'idle' OR STATE = 'idle in transaction'
1216+
OR STATE = 'idle in transaction (aborted)' THEN 'INACTIVE'
1217+
ELSE 'INACTIVE'
1218+
END::VARCHAR2(40) AS STATUS,
1219+
COALESCE(CLIENT_HOSTNAME, HOST(CLIENT_ADDR),CLIENT_ADDR::TEXT)::VARCHAR2(128) AS MACHINE,
1220+
APPLICATION_NAME::VARCHAR2(256) AS PROGRAM,
1221+
CASE WHEN BACKEND_TYPE = 'client backend' THEN 'USER'
1222+
ELSE 'BACKGROUND'
1223+
END::VARCHAR2(30) AS TYPE,
1224+
BACKEND_START AS LOGON_TIME,
1225+
QUERY_START AS SQL_EXEC_START,
1226+
QUERY_ID::VARCHAR2(19) AS SQL_ID,
1227+
QUERY::VARCHAR2(4000) AS SQL_TEXT,
1228+
(PG_CATALOG.PG_BLOCKING_PIDS(PID))[1]::NUMBER AS BLOCKING_SESSION,
1229+
WAIT_EVENT::VARCHAR2(64) AS EVENT,
1230+
WAIT_EVENT_TYPE::VARCHAR2(64) AS WAIT_CLASS,
1231+
XACT_START AS TRANSACTION_START,
1232+
CLIENT_PORT::NUMBER AS PORT
1233+
FROM PG_STAT_ACTIVITY;
1234+
1235+
1236+
-- V$PROCESS: USERNAME maps to the database username (pg_stat_activity.usename),
1237+
-- not the OS username as in Oracle V$PROCESS.
1238+
CREATE OR REPLACE VIEW SYS.V$PROCESS AS
1239+
SELECT
1240+
PID::NUMBER AS SPID,
1241+
USENAME::VARCHAR2(128) AS USERNAME,
1242+
APPLICATION_NAME::VARCHAR2(256) AS PROGRAM,
1243+
BACKEND_TYPE::VARCHAR2(30) AS PNAME,
1244+
CASE WHEN BACKEND_TYPE = 'client backend' THEN NULL
1245+
ELSE '1'
1246+
END::VARCHAR2(1) AS BACKGROUND
1247+
FROM PG_STAT_ACTIVITY;
1248+
1249+
CREATE OR REPLACE VIEW SYS.V$PARAMETER AS
1250+
SELECT
1251+
NAME::VARCHAR2(80) AS NAME,
1252+
CASE
1253+
WHEN VARTYPE = 'bool' THEN 1
1254+
WHEN VARTYPE = 'integer' THEN 3
1255+
ELSE 2
1256+
END::NUMBER AS TYPE,
1257+
SETTING::VARCHAR2(4000) AS VALUE,
1258+
BOOT_VAL::VARCHAR2(255) AS DEFAULT_VALUE,
1259+
-- Mapping update levels
1260+
CASE
1261+
WHEN setting IS NOT DISTINCT FROM boot_val THEN 'TRUE'
1262+
ELSE 'FALSE'
1263+
END::VARCHAR2(5) AS ISDEFAULT,
1264+
CASE
1265+
WHEN CONTEXT IN ('user', 'superuser') THEN 'TRUE'
1266+
ELSE 'FALSE'
1267+
END::VARCHAR2(5) AS ISSES_MODIFIABLE,
1268+
CASE
1269+
WHEN CONTEXT IN ('user', 'superuser','sighup') THEN 'IMMEDIATE'
1270+
WHEN CONTEXT IN ('postmaster','internal') THEN 'FALSE'
1271+
ELSE 'DEFERRED'
1272+
END::VARCHAR2(9) AS ISSYS_MODIFIABLE,
1273+
SHORT_DESC::VARCHAR2(255) AS DESCRIPTION
1274+
FROM PG_SETTINGS;
1275+
1276+
-- DBA_CONS_COLUMNS
1277+
CREATE OR REPLACE VIEW sys.dba_cons_columns AS
1278+
SELECT
1279+
SYS.ORA_CASE_TRANS(pg_get_userbyid(n.oid)::varchar2(128)) AS owner,
1280+
SYS.ORA_CASE_TRANS(c.conname::varchar2(128)) AS constraint_name,
1281+
SYS.ORA_CASE_TRANS(cl.relname::varchar2(128)) AS table_name,
1282+
SYS.ORA_CASE_TRANS(a.attname::varchar2(128)) AS column_name,
1283+
(row_number() OVER (PARTITION BY c.oid ORDER BY a.attnum))::int AS position,
1284+
CASE WHEN a.attnotnull THEN 'Y' ELSE 'N' END AS nullable
1285+
FROM pg_constraint c
1286+
JOIN pg_class cl ON c.conrelid = cl.oid
1287+
JOIN pg_namespace n ON cl.relnamespace = n.oid
1288+
JOIN pg_attribute a ON a.attrelid = cl.oid AND a.attnum = ANY(c.conkey)
1289+
WHERE cl.relkind = 'r'
1290+
AND a.attisdropped = false
1291+
AND n.nspname NOT IN ('pg_catalog', 'information_schema');

‎contrib/ivorysql_ora/src/sysview/sysview_constraints.sql‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
-- Create DBA_CONS_COLUMNS view for Oracle compatibility
2+
CREATE OR REPLACE VIEW sys.dba_cons_columns AS
3+
SELECT
4+
SYS.ORA_CASE_TRANS(pg_get_userbyid(n.oid)::varchar2(128)) AS owner,
5+
SYS.ORA_CASE_TRANS(c.conname::varchar2(128)) AS constraint_name,
6+
SYS.ORA_CASE_TRANS(cl.relname::varchar2(128)) AS table_name,
7+
SYS.ORA_CASE_TRANS(a.attname::varchar2(128)) AS column_name,
8+
(row_number() OVER (PARTITION BY c.oid ORDER BY a.attnum))::int AS position,
9+
CASE WHEN a.attnotnull THEN 'Y' ELSE 'N' END AS nullable
10+
FROM pg_constraint c
11+
JOIN pg_class cl ON c.conrelid = cl.oid
12+
JOIN pg_namespace n ON cl.relnamespace = n.oid
13+
JOIN pg_attribute a ON a.attrelid = cl.oid AND a.attnum = ANY(c.conkey)
14+
WHERE cl.relkind = 'r'
15+
AND a.attisdropped = false
16+
AND n.nspname NOT IN ('pg_catalog', 'information_schema');

0 commit comments

Comments
 (0)