Skip to content

Cleanup and refactoring of annotation cache. #986

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
jgebal merged 5 commits into from
Feb 23, 2020
Merged

Cleanup and refactoring of annotation cache. #986

Show file tree
Hide file tree
Changes from 1 commit
Commits
Show all changes
5 commits
Select commit Hold shift + click to select a range
ba90abf
Cleanup and refactoring of annotation cache.
jgebal Aug 5, 2019
335e420
Fixed loophole in privilege check.
jgebal Nov 12, 2019
b39c5be
Added additional tests for annotation cache refresh with/without DDL …
jgebal Feb 17, 2020
641b3b4
Update install.sh
jgebal Feb 18, 2020
5d4c33c
Removed unneeded `is_annotated` column
jgebal Feb 22, 2020
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
Next Next commit
Cleanup and refactoring of annotation cache. 
Added ability to refresh annotation cache with DDL trigger enabled if schema was not full-scanned.
Resolves: #975
  • Loading branch information
@jgebal
jgebal committed Feb 15, 2020
commit ba90abf5be5ba05b44a9080349f9edd54fe7a62b
7 changes: 5 additions & 2 deletions source/core/annotations/ut_annotation_cache_info.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -17,7 +17,10 @@ create table ut_annotation_cache_info (
object_name varchar2(250) not null,
object_type varchar2(250) not null,
parse_time timestamp not null,
constraint ut_annotation_cache_info_pk primary key(cache_id),
constraint ut_annotation_cache_info_uk unique (object_owner, object_type, object_name)
is_annotated varchar2(1) not null,
constraint ut_annotation_cache_info_ck1 check(is_annotated in ('Y','N')),
constraint ut_annotation_cache_info_pk primary key(cache_id) using index,
constraint ut_annotation_cache_info_uk unique (object_owner, object_type, object_name) using index,
constraint ut_annotation_cache_info_fk foreign key(object_owner, object_type) references ut_annotation_cache_schema(object_owner, object_type) on delete cascade
) organization index;

123 changes: 71 additions & 52 deletions source/core/annotations/ut_annotation_cache_manager.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -17,40 +17,42 @@ create or replace package body ut_annotation_cache_manager as
*/

procedure update_cache(a_object ut_annotated_object) is
l_cache_id integer;
l_new_objects_count integer := 0;
l_cache_id integer;
l_timestamp timestamp := systimestamp;
pragma autonomous_transaction;
begin
update ut_annotation_cache_schema s
set s.max_parse_time = l_timestamp
where s.object_type = a_object.object_type and s.object_owner = a_object.object_owner;

if sql%rowcount = 0 then
insert into ut_annotation_cache_schema s
(object_owner, object_type, max_parse_time)
values (a_object.object_owner, a_object.object_type, l_timestamp);
end if;

-- if not in trigger, or object has annotations
if ora_sysevent is null or a_object.annotations is not null and a_object.annotations.count > 0 then

update ut_annotation_cache_info i
set i.parse_time = systimestamp
set i.parse_time = l_timestamp,
i.is_annotated = case when a_object.annotations is not empty then 'Y' else 'N' end
where (i.object_owner, i.object_name, i.object_type)
in ((a_object.object_owner, a_object.object_name, a_object.object_type))
returning cache_id into l_cache_id;

if sql%rowcount = 0 then

insert into ut_annotation_cache_info
(cache_id, object_owner, object_name, object_type, parse_time)
values (ut_annotation_cache_seq.nextval, a_object.object_owner, a_object.object_name, a_object.object_type, systimestamp)
(cache_id, object_owner, object_name, object_type, parse_time, is_annotated)
values (ut_annotation_cache_seq.nextval, a_object.object_owner, a_object.object_name, a_object.object_type, l_timestamp,
case when a_object.annotations is not empty then 'Y' else 'N' end
)
returning cache_id into l_cache_id;
l_new_objects_count := 1;
end if;

end if;

update ut_annotation_cache_schema s
set s.object_count = s.object_count + l_new_objects_count, s.max_parse_time = systimestamp
where s.object_type = a_object.object_type and s.object_owner = a_object.object_owner;

if sql%rowcount = 0 then
insert into ut_annotation_cache_schema s
(object_owner, object_type, object_count, max_parse_time)
values (a_object.object_owner, a_object.object_type, l_new_objects_count, systimestamp);
end if;

delete from ut_annotation_cache c where cache_id = l_cache_id;

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


procedure cleanup_cache(a_objects ut_annotation_objs_cache_info) is
procedure reset_objects_cache(a_objects ut_annotation_objs_cache_info) is
l_timestamp timestamp := systimestamp;
pragma autonomous_transaction;
begin

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

update ut_annotation_cache_schema s
set s.max_parse_time = l_timestamp
where (s.object_owner, s.object_type)
in (
select o.object_owner, o.object_type
from table(a_objects) o
);

if sql%rowcount = 0 then
insert into ut_annotation_cache_schema s
(object_owner, object_type, max_parse_time)
select distinct o.object_owner, o.object_type, l_timestamp
from table(a_objects) o;
end if;

merge into ut_annotation_cache_info i
using (select o.object_name, o.object_type, o.object_owner
from table(a_objects) o ) o
on (o.object_name = i.object_name
and o.object_type = i.object_type
and o.object_owner = i.object_owner)
when matched then update set parse_time = systimestamp
when matched then
update
set parse_time = l_timestamp,
is_annotated = 'N'
when not matched then insert
(cache_id, object_owner, object_name, object_type, parse_time)
values (ut_annotation_cache_seq.nextval, o.object_owner, o.object_name, o.object_type, systimestamp);
(cache_id, object_owner, object_name, object_type, parse_time, is_annotated)
values (ut_annotation_cache_seq.nextval, o.object_owner, o.object_name, o.object_type, l_timestamp, 'N');

commit;
end;

function get_annotations_objects_info(a_object_owner varchar2, a_object_type varchar2) return ut_annotation_objs_cache_info is
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
l_result ut_annotation_objs_cache_info;
begin
select ut_annotation_obj_cache_info(
Expand All @@ -104,7 +125,8 @@ create or replace package body ut_annotation_cache_manager as
bulk collect into l_result
from ut_annotation_cache_info i
where i.object_owner = a_object_owner
and i.object_type = a_object_type;
and i.object_type = a_object_type
and (i.parse_time > a_parsed_after or a_parsed_after is null);
return l_result;
end;

Expand All @@ -123,6 +145,16 @@ create or replace package body ut_annotation_cache_manager as
return l_result;
end;

procedure set_fully_refreshed(a_object_owner varchar2, a_object_type varchar2) is
pragma autonomous_transaction;
begin
update ut_annotation_cache_schema s
set s.full_refresh_time = s.max_parse_time
where s.object_owner = a_object_owner
and s.object_type = a_object_type;
commit;
end;

procedure remove_from_cache(a_objects ut_annotation_objs_cache_info) is
pragma autonomous_transaction;
begin
Expand All @@ -138,10 +170,10 @@ create or replace package body ut_annotation_cache_manager as
commit;
end;

function get_annotations_for_objects(a_cached_objects ut_annotation_objs_cache_info, a_parse_time timestamp) return sys_refcursor is
l_results sys_refcursor;
function get_annotations_parsed_since(a_object_owner varchar2, a_object_type varchar2, a_parsed_after timestamp) return sys_refcursor is
l_results sys_refcursor;
begin
open l_results for q'[
open l_results for
select ut_annotated_object(
i.object_owner, i.object_name, i.object_type, i.parse_time,
cast(
Expand All @@ -151,14 +183,12 @@ create or replace package body ut_annotation_cache_manager as
) order by c.annotation_position
) as ut_annotations
)
)
from table(:a_cached_objects) o
join ut_annotation_cache_info i
on o.object_owner = i.object_owner and o.object_name = i.object_name and o.object_type = i.object_type
) as annotated_object
from ut_annotation_cache_info i
join ut_annotation_cache c on i.cache_id = c.cache_id
where ]'|| case when a_parse_time is null then ':a_parse_date is null' else 'i.parse_time > :a_parse_time' end ||q'[
group by i.object_owner, i.object_name, i.object_type, i.parse_time]'
using a_cached_objects, a_parse_time;
where i.object_owner = a_object_owner and i.object_type = a_object_type
and (i.parse_time > a_parsed_after or a_parsed_after is null)
group by i.object_owner, i.object_type, i.object_name, i.parse_time;
return l_results;
end;

Expand All @@ -168,35 +198,24 @@ create or replace package body ut_annotation_cache_manager as
pragma autonomous_transaction;
begin
if a_object_owner is null and a_object_type is null then
l_cache_filter := ':a_object_owner is null and :a_object_type is null';
l_filter := l_cache_filter;
l_filter := ':a_object_owner is null and :a_object_type is null';
l_cache_filter := l_filter;
else
l_filter :=
case when a_object_owner is null then ':a_object_owner is null' else 'object_owner = :a_object_owner' end || '
and '||case when a_object_type is null then ':a_object_type is null' else 'object_type = :a_object_type' end;
l_cache_filter := ' c.cache_id
in (select i.cache_id
from ut_annotation_cache_info i
where '|| l_filter || '
)';
l_filter := case when a_object_owner is null then ':a_object_owner is null' else 'object_owner = :a_object_owner' end;
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;
l_cache_filter := ' c.cache_id in (select i.cache_id from ut_annotation_cache_info i where ' || l_filter || ' )';
end if;
execute immediate '
delete from ut_annotation_cache c
where '||l_cache_filter
execute immediate 'delete from ut_annotation_cache c where ' || l_cache_filter
using a_object_owner, a_object_type;

execute immediate '
delete from ut_annotation_cache_info i
where ' || l_filter
execute immediate ' delete from ut_annotation_cache_info i where ' || l_filter
using a_object_owner, a_object_type;

execute immediate '
delete from ut_annotation_cache_schema s
where ' || l_filter
execute immediate ' delete from ut_annotation_cache_schema s where ' || l_filter
using a_object_owner, a_object_type;

commit;
end;

end ut_annotation_cache_manager;
end;
/
19 changes: 13 additions & 6 deletions source/core/annotations/ut_annotation_cache_manager.pks
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -16,6 +16,7 @@ create or replace package ut_annotation_cache_manager authid definer as
limitations under the License.
*/
subtype t_cache_schema_info is ut_annotation_cache_schema%rowtype;

/**
* Populates cache with information about object and it's annotations
* Cache information for individual object is modified by this code
Expand All @@ -30,21 +31,27 @@ create or replace package ut_annotation_cache_manager authid definer as
* Returns a ref_cursor containing `ut_annotated_object` as result
* Range of data returned is limited by the input collection o cache object info
*
* @param a_cached_objects a `ut_annotation_objs_cache_info` list with information about objects to get from cache
* @param a_cached_objects - list of `ut_annotation_objs_cache_info` containing objects to get from cache
* @param a_min_parse_time - limit results to annotations parsed after specified time only,
* if null - all cached annotations for given objects are returned
*/
function get_annotations_for_objects(a_cached_objects ut_annotation_objs_cache_info, a_parse_time timestamp) return sys_refcursor;

function get_annotations_parsed_since(a_object_owner varchar2, a_object_type varchar2, a_parsed_after timestamp) return sys_refcursor;

function get_annotations_objects_info(a_object_owner varchar2, a_object_type varchar2) return ut_annotation_objs_cache_info;
procedure set_fully_refreshed(a_object_owner varchar2, a_object_type varchar2);

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

/**
* Removes cached information about annotations for objects on the list and updates parse_time in cache info table.
* Returns information about all objects stored in annotation cache
*/
function get_cached_objects_list(a_object_owner varchar2, a_object_type varchar2, a_parsed_after timestamp := null) return ut_annotation_objs_cache_info;

/**
* Resets cached information about annotations for objects on the list and updates parse_time in cache info table.
*
* @param a_objects a `ut_annotation_objs_cache_info` list with information about objects to remove annotations for
*/
procedure cleanup_cache(a_objects ut_annotation_objs_cache_info);
procedure reset_objects_cache(a_objects ut_annotation_objs_cache_info);

/**
* Removes information about objects on the list
Expand Down
8 changes: 4 additions & 4 deletions source/core/annotations/ut_annotation_cache_schema.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -12,10 +12,10 @@ create table ut_annotation_cache_schema (
See the License for the specific language governing permissions and
limitations under the License.
*/
object_owner varchar2(250) not null,
object_type varchar2(250) not null,
object_count integer not null,
max_parse_time date not null,
object_owner varchar2(250) not null,
object_type varchar2(250) not null,
max_parse_time date not null,
full_refresh_time timestamp,
constraint ut_annotation_cache_schema_pk primary key(object_owner, object_type)
) organization index;

Loading