Skip to content

Commit 96b8480

Browse files
jgebaljgebal
committed
Adding trigger solution for annotation parsing.
1 parent 421888e commit 96b8480

14 files changed

+264
-43
lines changed

‎docs/userguide/install.md‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -121,6 +121,8 @@ The uninstall process will **not** drop profiler tables, as they can potentially
121121

122122
It is up to DBA to maintain the storage of the profiler tables.
123123

124+
Additionally the user performing the installation must have a `ADMINISTER DATABASE TRIGGER` privilege. This is required for installation of trigger that is responsible for parsing annotations at at compile-time of a package.
125+
124126
# Manual installation procedure
125127

126128
### Creating schema for utPLSQL

‎source/check_sys_grants.sql‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,15 +1,17 @@
11
declare
22
c_expected_grants constant dbmsoutput_linesarray
33
:= dbmsoutput_linesarray(
4-
'CREATE TYPE','CREATE VIEW','CREATE SYNONYM','CREATE SEQUENCE','CREATE PROCEDURE','CREATE TABLE'
4+
'CREATE TYPE','CREATE VIEW','CREATE SYNONYM','CREATE SEQUENCE','CREATE PROCEDURE','CREATE TABLE', 'ADMINISTER DATABASE TRIGGER'
55
);
66

77
l_expected_grants dbmsoutput_linesarray := c_expected_grants;
88
l_missing_grants varchar2(4000);
99
begin
1010
if user != SYS_CONTEXT('userenv','current_schema') then
1111
for i in 1 .. l_expected_grants.count loop
12-
l_expected_grants(i) := replace(l_expected_grants(i),' ',' ANY ');
12+
if l_expected_grants(i) != 'ADMINISTER DATABASE TRIGGER' then
13+
l_expected_grants(i) := replace(l_expected_grants(i),' ',' ANY ');
14+
end if;
1315
end loop;
1416
end if;
1517
select listagg(' - '||privilege,CHR(10)) within group(order by privilege)

‎source/core/annotations/ut_annotation_cache_info.sql‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,6 @@ create table ut_annotation_cache_info (
1818
object_type varchar2(250) not null,
1919
parse_time timestamp not null,
2020
constraint ut_annotation_cache_info_pk primary key(cache_id),
21-
constraint ut_annotation_cache_info_uk unique (object_owner, object_name, object_type)
21+
constraint ut_annotation_cache_info_uk unique (object_owner, object_type, object_name)
2222
) organization index;
2323

‎source/core/annotations/ut_annotation_cache_manager.pkb‎

Lines changed: 56 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -18,22 +18,40 @@ create or replace package body ut_annotation_cache_manager as
1818

1919
procedure update_cache(a_object ut_annotated_object) is
2020
l_cache_id integer;
21+
l_new_objects_count integer := 0;
2122
pragma autonomous_transaction;
2223
begin
23-
update ut_annotation_cache_info i
24-
set i.parse_time = systimestamp
25-
where (i.object_owner, i.object_name, i.object_type)
26-
in ((a_object.object_owner, a_object.object_name, a_object.object_type))
27-
returning cache_id into l_cache_id;
28-
if sql%rowcount = 0 then
29-
insert into ut_annotation_cache_info
30-
(cache_id, object_owner, object_name, object_type, parse_time)
31-
values (ut_annotation_cache_seq.nextval, a_object.object_owner, a_object.object_name, a_object.object_type, systimestamp)
24+
-- if not in trigger, or object has annotations
25+
if ora_sysevent is null or a_object.annotations is not null and a_object.annotations.count > 0 then
26+
27+
update ut_annotation_cache_info i
28+
set i.parse_time = systimestamp
29+
where (i.object_owner, i.object_name, i.object_type)
30+
in ((a_object.object_owner, a_object.object_name, a_object.object_type))
3231
returning cache_id into l_cache_id;
32+
33+
if sql%rowcount = 0 then
34+
35+
insert into ut_annotation_cache_info
36+
(cache_id, object_owner, object_name, object_type, parse_time)
37+
values (ut_annotation_cache_seq.nextval, a_object.object_owner, a_object.object_name, a_object.object_type, systimestamp)
38+
returning cache_id into l_cache_id;
39+
l_new_objects_count := 1;
40+
end if;
41+
3342
end if;
3443

35-
delete from ut_annotation_cache c
36-
where cache_id = l_cache_id;
44+
update ut_annotation_cache_schema s
45+
set s.object_count = s.object_count + l_new_objects_count, s.max_parse_time = systimestamp
46+
where s.object_type = a_object.object_type and s.object_owner = a_object.object_owner;
47+
48+
if sql%rowcount = 0 then
49+
insert into ut_annotation_cache_schema s
50+
(object_owner, object_type, object_count, max_parse_time)
51+
values (a_object.object_owner, a_object.object_type, l_new_objects_count, systimestamp);
52+
end if;
53+
54+
delete from ut_annotation_cache c where cache_id = l_cache_id;
3755

3856
if a_object.annotations is not null and a_object.annotations.count > 0 then
3957
insert into ut_annotation_cache
@@ -73,17 +91,32 @@ create or replace package body ut_annotation_cache_manager as
7391
commit;
7492
end;
7593

94+
function get_cache_schema_info(a_object_owner varchar2, a_object_type varchar2) return t_cache_schema_info is
95+
l_result t_cache_schema_info;
96+
begin
97+
begin
98+
select *
99+
into l_result
100+
from ut_annotation_cache_schema s
101+
where s.object_type = a_object_type and s.object_owner = a_object_owner;
102+
exception
103+
when no_data_found then
104+
null;
105+
end;
106+
return l_result;
107+
end;
108+
76109
procedure remove_from_cache(a_objects ut_annotation_objs_cache_info) is
77110
pragma autonomous_transaction;
78111
begin
79112

80113
delete from ut_annotation_cache_info i
81-
where exists (
82-
select 1 from table (a_objects) o
83-
where o.object_name = i.object_name
84-
and o.object_type = i.object_type
85-
and o.object_owner = i.object_owner
86-
);
114+
where exists (
115+
select 1 from table (a_objects) o
116+
where o.object_name = i.object_name
117+
and o.object_type = i.object_type
118+
and o.object_owner = i.object_owner
119+
);
87120

88121
commit;
89122
end;
@@ -139,6 +172,12 @@ create or replace package body ut_annotation_cache_manager as
139172
delete from ut_annotation_cache_info i
140173
where ' || l_filter
141174
using a_object_owner, a_object_type;
175+
176+
execute immediate '
177+
delete from ut_annotation_cache_schema s
178+
where ' || l_filter
179+
using a_object_owner, a_object_type;
180+
142181
commit;
143182
end;
144183

‎source/core/annotations/ut_annotation_cache_manager.pks‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@ create or replace package ut_annotation_cache_manager authid definer as
1515
See the License for the specific language governing permissions and
1616
limitations under the License.
1717
*/
18-
18+
subtype t_cache_schema_info is ut_annotation_cache_schema%rowtype;
1919
/**
2020
* Populates cache with information about object and it's annotations
2121
* Cache information for individual object is modified by this code
@@ -34,6 +34,8 @@ create or replace package ut_annotation_cache_manager authid definer as
3434
*/
3535
function get_annotations_for_objects(a_cached_objects ut_annotation_objs_cache_info, a_parse_time timestamp) return sys_refcursor;
3636

37+
function get_cache_schema_info(a_object_owner varchar2, a_object_type varchar2) return t_cache_schema_info;
38+
3739
/**
3840
* Removes cached information about annotations for objects on the list and updates parse_time in cache info table.
3941
*

‎source/core/annotations/ut_annotation_cache_schema.sql‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
create table ut_annotation_cache_schema (
2+
/*
3+
utPLSQL - Version 3
4+
Copyright 2016 - 2017 utPLSQL Project
5+
Licensed under the Apache License, Version 2.0 (the "License"):
6+
you may not use this file except in compliance with the License.
7+
You may obtain a copy of the License at
8+
http://www.apache.org/licenses/LICENSE-2.0
9+
Unless required by applicable law or agreed to in writing, software
10+
distributed under the License is distributed on an "AS IS" BASIS,
11+
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
See the License for the specific language governing permissions and
13+
limitations under the License.
14+
*/
15+
object_owner varchar2(250) not null,
16+
object_type varchar2(250) not null,
17+
object_count integer not null,
18+
max_parse_time date not null,
19+
constraint ut_annotation_cache_schema_pk primary key(object_owner, object_type)
20+
) organization index;
21+

‎source/core/annotations/ut_annotation_manager.pkb‎

Lines changed: 78 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -58,26 +58,39 @@ create or replace package body ut_annotation_manager as
5858
l_cursor_text varchar2(32767);
5959
l_result ut_annotation_objs_cache_info;
6060
begin
61-
l_cursor_text :=
62-
q'[select ]'||l_ut_owner||q'[.ut_annotation_obj_cache_info(
63-
object_owner => o.owner,
64-
object_name => o.object_name,
65-
object_type => o.object_type,
66-
needs_refresh => case when o.last_ddl_time < cast(i.parse_time as date) then 'N' else 'Y' end
67-
)
68-
from ]'||l_objects_view||q'[ o
69-
left join ]'||l_ut_owner||q'[.ut_annotation_cache_info i
70-
on o.owner = i.object_owner
71-
and o.object_name = i.object_name
72-
and o.object_type = i.object_type
73-
where o.owner = ']'||a_object_owner||q'['
74-
and o.object_type = ']'||a_object_type||q'['
75-
and ]'
76-
|| case
77-
when a_parse_date is null
78-
then ':a_parse_date is null'
79-
else 'o.last_ddl_time >= cast(:a_parse_date as date)'
80-
end;
61+
if ut_trigger_check.is_alive() then
62+
l_cursor_text :=
63+
q'[select ]'||l_ut_owner||q'[.ut_annotation_obj_cache_info(
64+
object_owner => i.object_owner,
65+
object_name => i.object_name,
66+
object_type => i.object_type,
67+
needs_refresh => 'N'
68+
)
69+
from ]'||l_ut_owner||q'[.ut_annotation_cache_info i
70+
where i.object_owner = :a_object_owner
71+
and i.object_type = :a_object_type]';
72+
else
73+
l_cursor_text :=
74+
q'[select ]'||l_ut_owner||q'[.ut_annotation_obj_cache_info(
75+
object_owner => o.owner,
76+
object_name => o.object_name,
77+
object_type => o.object_type,
78+
needs_refresh => case when o.last_ddl_time < cast(i.parse_time as date) then 'N' else 'Y' end
79+
)
80+
from ]'||l_objects_view||q'[ o
81+
left join ]'||l_ut_owner||q'[.ut_annotation_cache_info i
82+
on o.owner = i.object_owner
83+
and o.object_name = i.object_name
84+
and o.object_type = i.object_type
85+
where o.owner = ']'||a_object_owner||q'['
86+
and o.object_type = ']'||a_object_type||q'['
87+
and ]'
88+
|| case
89+
when a_parse_date is null
90+
then ':a_parse_date is null'
91+
else 'o.last_ddl_time >= cast(:a_parse_date as date)'
92+
end;
93+
end if;
8194
open l_rows for l_cursor_text using a_parse_date;
8295
fetch l_rows bulk collect into l_result limit 1000000;
8396
close l_rows;
@@ -222,11 +235,55 @@ create or replace package body ut_annotation_manager as
222235
);
223236
end;
224237

238+
procedure trigger_obj_annotation_rebuild is
239+
l_sql_text ora_name_list_t;
240+
l_parts binary_integer;
241+
l_object_to_parse ut_annotation_obj_cache_info;
242+
243+
function get_source_from_sql_text(a_object_name varchar2, a_sql_text ora_name_list_t, a_parts binary_integer) return sys_refcursor is
244+
l_sql_clob clob;
245+
l_sql_lines ut_varchar2_rows := ut_varchar2_rows();
246+
l_result sys_refcursor;
247+
l_sql_text ora_name_list_t := a_sql_text;
248+
begin
249+
if a_parts > 0 then
250+
l_sql_text(1) := regexp_replace(l_sql_text(1),'^\s*create(\s+or\s+replace)?\s+', modifier => 'i');
251+
for i in 1..a_parts loop
252+
ut_utils.append_to_clob(l_sql_clob, l_sql_text(i));
253+
end loop;
254+
l_sql_lines := ut_utils.convert_collection( ut_utils.clob_to_table(l_sql_clob) );
255+
end if;
256+
open l_result for
257+
select a_object_name as name, column_value||chr(10) as text from table(l_sql_lines);
258+
return l_result;
259+
end;
260+
begin
261+
ut_trigger_check.is_alive();
262+
263+
if ora_dict_obj_type = 'PACKAGE' then
264+
265+
l_object_to_parse := ut_annotation_obj_cache_info(ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, 'Y');
266+
267+
if ora_sysevent = 'CREATE' then
268+
l_parts := ORA_SQL_TXT(l_sql_text);
269+
build_annot_cache_for_sources(
270+
ora_dict_obj_owner, ora_dict_obj_type,
271+
get_source_from_sql_text(ora_dict_obj_name, l_sql_text, l_parts)
272+
);
273+
elsif ora_sysevent = 'ALTER' then
274+
null;
275+
--update parse_time
276+
elsif ora_sysevent = 'DROP' then
277+
ut_annotation_cache_manager.remove_from_cache(ut_annotation_objs_cache_info(l_object_to_parse));
278+
end if;
279+
end if;
280+
end;
281+
225282
function get_annotated_objects(a_object_owner varchar2, a_object_type varchar2, a_parse_date timestamp := null) return ut_annotated_objects pipelined is
226283
l_info_rows ut_annotation_objs_cache_info;
227284
l_cursor sys_refcursor;
228285
l_results ut_annotated_objects;
229-
c_object_fetch_limit constant integer := 10;
286+
c_object_fetch_limit constant integer := 10;
230287
begin
231288

232289
l_info_rows := get_annotation_objs_info(a_object_owner, a_object_type, a_parse_date);

‎source/core/annotations/ut_annotation_manager.pks‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -43,6 +43,11 @@ create or replace package ut_annotation_manager authid current_user as
4343
*/
4444
procedure rebuild_annotation_cache(a_object_owner varchar2, a_object_type varchar2);
4545

46+
/**
47+
* Rebuilds annotation cache for a specified object.
48+
*/
49+
procedure trigger_obj_annotation_rebuild;
50+
4651
/**
4752
* Removes cached information about annotations for objects of specified type and specified owner
4853
*

‎source/core/annotations/ut_trigger_annotation_parsing.trg‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
create or replace trigger ut_trigger_annotation_parsing
2+
after create or alter or drop
3+
on database
4+
begin
5+
if ora_dict_obj_type = 'PACKAGE'
6+
or (ora_dict_obj_owner = UPPER('&&UT3_OWNER')
7+
and ora_dict_obj_name = 'UT3_TRIGGER_ALIVE'
8+
and ora_dict_obj_type = 'SYNONYM')
9+
then
10+
execute immediate 'begin ut_annotation_manager.trigger_obj_annotation_rebuild; end;';
11+
end if;
12+
exception
13+
when others then null;
14+
end;
15+
/

‎source/core/annotations/ut_trigger_check.pkb‎

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
create or replace package body ut_trigger_check is
2+
/*
3+
utPLSQL - Version 3
4+
Copyright 2016 - 2018 utPLSQL Project
5+
6+
Licensed under the Apache License, Version 2.0 (the "License"):
7+
you may not use this file except in compliance with the License.
8+
You may obtain a copy of the License at
9+
10+
http://www.apache.org/licenses/LICENSE-2.0
11+
12+
Unless required by applicable law or agreed to in writing, software
13+
distributed under the License is distributed on an "AS IS" BASIS,
14+
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15+
See the License for the specific language governing permissions and
16+
limitations under the License.
17+
*/
18+
19+
g_is_trigger_live boolean := false;
20+
21+
function is_alive return boolean is
22+
pragma autonomous_transaction;
23+
l_ut_owner varchar2(250) := ut_utils.ut_owner;
24+
l_is_trigger_live boolean;
25+
begin
26+
execute immediate 'create or replace synonym '||l_ut_owner||'.ut3_trigger_alive for no_object';
27+
l_is_trigger_live := g_is_trigger_live;
28+
g_is_trigger_live := false;
29+
return l_is_trigger_live;
30+
end;
31+
32+
procedure is_alive is
33+
begin
34+
if ora_dict_obj_owner = 'UT3' and ora_dict_obj_name = 'UT3_TRIGGER_TEST' and ora_dict_obj_type = 'SYNONYM' then
35+
g_is_trigger_live := true;
36+
end if;
37+
end;
38+
39+
end;
40+
/

0 commit comments

Comments
 (0)