8000 Cleanup and refactoring of annotation cache. · utPLSQL/utPLSQL@07250a3 · GitHub
[go: up one dir, main page]
Skip to content

Commit 07250a3

Browse files
jgebaljgebal
committed
Cleanup and refactoring of annotation cache.
Added ability to refresh annotation cache with DDL trigger enabled if schema was not full-scanned. Resolves: #975
1 parent a2c0a58 commit 07250a3

17 files changed

+453
-351
lines changed

‎source/core/annotations/ut_annotation_cache_info.sql

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,10 @@ create table ut_annotation_cache_info (
1717
object_name varchar2(250) not null,
1818
object_type varchar2(250) not null,
1919
parse_time timestamp not null,
20-
constraint ut_annotation_cache_info_pk primary key(cache_id),
21-
constraint ut_annotation_cache_info_uk unique (object_owner, object_type, object_name)
20+
is_annotated varchar2(1) not null,
21+
constraint ut_annotation_cache_info_ck1 check(is_annotated in ('Y','N')),
22+
constraint ut_annotation_cache_info_pk primary key(cache_id) using index,
23+
constraint ut_annotation_cache_info_uk unique (object_owner, object_type, object_name) using index,
24+
constraint ut_annotation_cache_info_fk foreign key(object_owner, object_type) references ut_annotation_cache_schema(object_owner, object_type) on delete 8000 cascade
2225
) organization index;
2326

‎source/core/annotations/ut_annotation_cache_manager.pkb

Lines changed: 71 additions & 52 deletions
Original file line numberDiff line numberDiff line change
@@ -17,40 +17,42 @@ create or replace package body ut_annotation_cache_manager as
1717
*/
1818

1919
procedure update_cache(a_object ut_annotated_object) is
20-
l_cache_id integer;
21-
l_new_objects_count integer := 0;
20+
l_cache_id integer;
21+
l_timestamp timestamp := systimestamp;
2222
pragma autonomous_transaction;
2323
begin
24+
update ut_annotation_cache_schema s
25+
set s.max_parse_time = l_timestamp
26+
where s.object_type = a_object.object_type and s.object_owner = a_object.object_owner;
27+
28+
if sql%rowcount = 0 then
29+
insert into ut_annotation_cache_schema s
30+
(object_owner, object_type, max_parse_time)
31+
values (a_object.object_owner, a_object.object_type, l_timestamp);
32+
end if;
33+
2434
-- if not in trigger, or object has annotations
2535
if ora_sysevent is null or a_object.annotations is not null and a_object.annotations.count > 0 then
2636

2737
update ut_annotation_cache_info i
28-
set i.parse_time = systimestamp
38+
set i.parse_time = l_timestamp,
39+
i.is_annotated = case when a_object.annotations is not empty then 'Y' else 'N' end
2940
where (i.object_owner, i.object_name, i.object_type)
3041
in ((a_object.object_owner, a_object.object_name, a_object.object_type))
3142
returning cache_id into l_cache_id;
3243

3344
if sql%rowcount = 0 then
3445

3546
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)
47+
(cache_id, object_owner, object_name, object_type, parse_time, is_annotated)
48+
values (ut_annotation_cache_seq.nextval, a_object.object_owner, a_object.object_name, a_object.object_type, l_timestamp,
49+
case when a_object.annotations is not empty then 'Y' else 'N' end
50+
)
3851
returning cache_id into l_cache_id;
39-
l_new_objects_count := 1;
4052
end if;
4153

4254
end if;
4355

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-
5456
delete from ut_annotation_cache c where cache_id = l_cache_id;
5557

5658
if a_object.annotations is not null and a_object.annotations.count > 0 then
@@ -63,7 +65,8 @@ create or replace package body ut_annotation_cache_manager as
6365
end;
6466

6567

66-
procedure cleanup_cache(a_objects ut_annotation_objs_cache_info) is
68+
procedure reset_objects_cache(a_objects ut_annotation_objs_cache_info) is
69+
l_timestamp timestamp := systimestamp;
6770
pragma autonomous_transaction;
6871
begin
6972

@@ -77,21 +80,39 @@ create or replace package body ut_annotation_cache_manager as
7780
and o.object_owner = i.object_owner
7881
);
7982

83+
update ut_annotation_cache_schema s
84+
set s.max_parse_time = l_timestamp
85+
where (s.object_owner, s.object_type)
86+
in (
87+
select o.object_owner, o.object_type
88+
from table(a_objects) o
89+
);
90+
91+
if sql%rowcount = 0 then
92+
insert into ut_annotation_cache_schema s
93+
(object_owner, object_type, max_parse_time)
94+
select distinct o.object_owner, o.object_type, l_timestamp
95+
from table(a_objects) o;
96+
end if;
97+
8098
merge into ut_annotation_cache_info i
8199
using (select o.object_name, o.object_type, o.object_owner
82100
from table(a_objects) o ) o
83101
on (o.object_name = i.object_name
84102
and o.object_type = i.object_type
85103
and o.object_owner = i.object_owner)
86-
when matched then update set parse_time = systimestamp
104+
when matched then
105+
update
106+
set parse_time = l_timestamp,
107+
is_annotated = 'N'
87108
when not matched then insert
88-
(cache_id, object_owner, object_name, object_type, parse_time)
89-
values (ut_annotation_cache_seq.nextval, o.object_owner, o.object_name, o.object_type, systimestamp);
109+
(cache_id, object_owner, object_name, object_type, parse_time, is_annotated)
110+
values (ut_annotation_cache_seq.nextval, o.object_owner, o.object_name, o.object_type, l_timestamp, 'N');
90111

91112
commit;
92113
end;
93114

94-
function get_annotations_objects_info(a_object_owner varchar2, a_object_type varchar2) return ut_annotation_objs_cache_info is
115+
function get_cached_objects_list(a_object_owner varchar2, a_object_type varchar2, a_parsed_after timestamp := null) return ut_annotation_objs_cache_info is
95116
l_result ut_annotation_objs_cache_info;
96117
begin
97118
select ut_annotation_obj_cache_info(
@@ -104,7 +125,8 @@ create or replace package body ut_annotation_cache_manager as
104125
bulk collect into l_result
105126
from ut_annotation_cache_info i
106127
where i.object_owner = a_object_owner
107-
and i.object_type = a_object_type;
128+
and i.object_type = a_object_type
129+
and (i.parse_time > a_parsed_after or a_parsed_after is null);
108130
return l_result;
109131
end;
110132

@@ -123,6 +145,16 @@ create or replace package body ut_annotation_cache_manager as
123145
return l_result;
124146
end;
125147

148+
procedure set_fully_refreshed(a_object_owner varchar2, a_object_type varchar2) is
149+
pragma autonomous_transaction;
150+
begin
151+
update ut_annotation_cache_schema s
152+
set s.full_refresh_time = s.max_parse_time
153+
where s.object_owner = a_object_owner
154+
and s.object_type = a_object_type;
155+
commit;
156+
end;
157+
126158
procedure remove_from_cache(a_objects ut_annotation_objs_cache_info) is
127159
pragma autonomous_transaction;
128160
begin
@@ -138,10 +170,10 @@ create or replace package body ut_annotation_cache_manager as
138170
commit;
139171
end;
140172

141-
function get_annotations_for_objects(a_cached_objects ut_annotation_objs_cache_info, a_parse_time timestamp) return sys_refcursor is
142-
l_results sys_refcursor;
173+
function get_annotations_parsed_since(a_object_owner varchar2, a_object_type varchar2, a_parsed_after timestamp) return sys_refcursor is
174+
l_results sys_refcursor;
143175
begin
144-
open l_results for q'[
176+
open l_results for
145177
select ut_annotated_object(
146178
i.object_owner, i.object_name, i.object_type, i.parse_time,
147179
cast(
@@ -151,14 +183,12 @@ create or replace package body ut_annotation_cache_manager as
151183
) order by c.annotation_position
152184
) as ut_annotations
153185
)
154-
)
155-
from table(:a_cached_objects) o
156-
join ut_annotation_cache_info i
157-
on o.object_owner = i.object_owner and o.object_name = i.object_name and o.object_type = i.object_type
186+
) as annotated_object
187+
from ut_annotation_cache_info i
158188
join ut_annotation_cache c on i.cache_id = c.cache_id
159-
where ]'|| case when a_parse_time is null then ':a_parse_date is null' else 'i.parse_time > :a_parse_time' end ||q'[
160-
group by i.object_owner, i.object_name, i.object_type, i.parse_time]'
161-
using a_cached_objects, a_parse_time;
189+
where i.object_owner = a_object_owner and i.object_type = a_object_type
190+
and (i.parse_time > a_parsed_after or a_parsed_after is null)
191+
group by i.object_owner, i.object_type, i.object_name, i.parse_time;
162192
return l_results;
163193
end;
164194

@@ -168,35 +198,24 @@ create or replace package body ut_annotation_cache_manager as
168198
pragma autonomous_transaction;
169199
begin
170200
if a_object_owner is null and a_object_type is null then
171-
l_cache_filter := ':a_object_owner is null and :a_object_type is null';
172-
l_filter := l_cache_filter;
201+
l_filter := ':a_object_owner is null and :a_object_type is null';
202+
l_cache_filter := l_filter;
173203
else
174-
l_filter :=
175-
case when a_object_owner is null then ':a_object_owner is null' else 'object_owner = :a_object_owner' end || '
176-
and '||case when a_object_type is null then ':a_object_type is null' else 'object_type = :a_object_type' end;
177-
l_cache_filter := ' c.cache_id
178-
in (select i.cache_id
179-
from ut_annotation_cache_info i
180-
where '|| l_filter || '
181-
)';
204+
l_filter := case when a_object_owner is null then ':a_object_owner is null' else 'object_owner = :a_object_owner' end;
205+
l_filter := l_filter || ' and ' || case when a_object_type is null then ':a_object_type is null' else 'object_type = :a_object_type' end;
206+
l_cache_filter := ' c.cache_id in (select i.cache_id from ut_annotation_cache_info i where ' || l_filter || ' )';
182207
end if;
183-
execute immediate '
184-
delete from ut_annotation_cache c
185-
where '||l_cache_filter
208+
execute immediate 'delete from ut_annotation_cache c where ' || l_cache_filter
186209
using a_object_owner, a_object_type;
187210

188-
execute immediate '
189-
delete from ut_annotation_cache_info i
190-
where ' || l_filter
211+
execute immediate ' delete from ut_annotation_cache_info i where ' || l_filter
191212
using a_object_owner, a_object_type;
192213

193-
execute immediate '
194-
delete from ut_annotation_cache_schema s
195-
where ' || l_filter
214+
execute immediate ' delete from ut_annotation_cache_schema s where ' || l_filter
196215
using a_object_owner, a_object_type;
197216

198217
commit;
199218
end;
200219

201-
end ut_annotation_cache_manager;
220+
end;
202221
/

‎source/core/annotations/ut_annotation_cache_manager.pks

Lines changed: 13 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@ create or replace package ut_annotation_cache_manager authid definer as
1616
limitations under the License.
1717
*/
1818
subtype t_cache_schema_info is ut_annotation_cache_schema%rowtype;
19+
1920
/**
2021
* Populates cache with information about object and it's annotations
2122
* Cache information for individual object is modified by this code
@@ -30,21 +31,27 @@ create or replace package ut_annotation_cache_manager authid definer as
3031
* Returns a ref_cursor containing `ut_annotated_object` as result
3132
* Range of data returned is limited by the input collection o cache object info
3233
*
33-
* @param a_cached_objects a `ut_annotation_objs_cache_info` list with information about objects to get from cache
34+
* @param a_cached_objects - list of `ut_annotation_objs_cache_info` containing objects to get from cache
35+
* @param a_min_parse_time - limit results to annotations parsed after specified time only,
36+
* if null - all cached annotations for given objects are returned
3437
*/
35-
function get_annotations_for_objects(a_cached_objects ut_annotation_objs_cache_info, a_parse_time timestamp) return sys_refcursor;
36-
38+
function get_annotations_parsed_since(a_object_owner varchar2, a_object_type varchar2, a_parsed_after timestamp) return sys_refcursor;
3739

38-
function get_annotations_objects_info(a_object_owner varchar2, a_object_type varchar2) return ut_annotation_objs_cache_info;
40+
procedure set_fully_refreshed(a_object_owner varchar2, a_object_type varchar2);
3941

4042
function get_cache_schema_info(a_object_owner varchar2, a_object_type varchar2) return t_cache_schema_info;
4143

4244
/**
43-
* Removes cached information about annotations for objects on the list and updates parse_time in cache info table.
45+
* Returns information about all objects stored in annotation cache
46+
*/
47+
function get_cached_objects_list(a_object_owner varchar2, a_object_type varchar2, a_parsed_after timestamp := null) return ut_annotation_objs_cache_info;
48+
49+
/**
50+
* Resets cached information about annotations for objects on the list and updates parse_time in cache info table.
4451
*
4552
* @param a_objects a `ut_annotation_objs_cache_info` list with information about objects to remove annotations for
4653
*/
47-
procedure cleanup_cache(a_objects ut_annotation_objs_cache_info);
54+
procedure reset_objects_cache(a_objects ut_annotation_objs_cache_info);
4855

4956
/**
5057
* Removes information about objects on the list

‎source/core/annotations/ut_annotation_cache_schema.sql

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -12,10 +12,10 @@ create table ut_annotation_cache_schema (
1212
See the License for the specific language governing permissions and
1313
limitations under the License.
1414
*/
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,
15+
object_owner varchar2(250) not null,
16+
object_type varchar2(250) not null,
17+
max_parse_time date not null,
18+
full_refresh_time timestamp,
1919
constraint ut_annotation_cache_schema_pk primary key(object_owner, object_type)
2020
) organization index;
2121

0 commit comments

Comments
 (0)
0