8000 Merge pull request #986 from utPLSQL/feature/cache_fixes · utPLSQL/utPLSQL@62f3154 · GitHub
[go: up one dir, main page]
Skip to content

Commit 62f3154

Browse files
jgebaljgebal
authored
Merge pull request #986 from utPLSQL/feature/cache_fixes
Cleanup and refactoring of annotation cache.
2 parents 29b6db4 + 5d4c33c commit 62f3154

29 files changed

+1464
-386
lines changed

‎.travis/install.sh

Lines changed: 27 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -74,10 +74,6 @@ PROMPT Creating $UT3_TESTER - Power-user for testing internal framework code
7474
create user $UT3_TESTER identified by "$UT3_TESTER_PASSWORD" default tablespace $UT3_TABLESPACE quota unlimited on $UT3_TABLESPACE;
7575
grant create session, create procedure, create type, create table to $UT3_TESTER;
7676
77-
PROMPT Additional grants for disabling DDL trigger and testing parser without trigger enabled/present
78-
79-
grant alter any trigger to $UT3_TESTER;
80-
grant administer database trigger to $UT3_TESTER;
8177
grant execute on dbms_lock to $UT3_TESTER;
8278
8379
PROMPT Granting $UT3_OWNER code to $UT3_TESTER
@@ -98,7 +94,7 @@ end;
9894
PROMPT Granting $UT3_OWNER tables to $UT3_TESTER
9995
10096
begin
101-
for i in ( select table_name from all_tables t where owner = 'UT3' and nested = 'NO' and IOT_NAME is NULL)
97+
for i in ( select table_name from all_tables t where owner = 'UT3' and nested = 'NO' and iot_name is null)
10298
loop
10399
execute immediate 'grant select on UT3.'||i.table_name||' to UT3_TESTER';
104100
end loop;
@@ -124,8 +120,33 @@ grant create public database link to $UT3_TESTER_HELPER;
124120
grant drop public database link to $UT3_TESTER_HELPER;
125121
126122
PROMPT Grants for testing coverage outside of main UT3 schema.
127-
grant create any procedure, drop any procedure, execute any procedure, create any type, drop any type, execute any type, under any type, select any table, update any table, insert any table, delete any table, create any table, drop any table, alter any table, select any dictionary, create any synonym, drop any synonym to $UT3_TESTER_HELPER;
123+
grant create any procedure, drop any procedure, execute any procedure, create any type, drop any type, execute any type, under any type,
124+
select any table, update any table, insert any table, delete any table, create any table, drop any table, alter any table,
125+
select any dictionary, create any synonym, drop any synonym,
126+
grant any object privilege, grant any privilege
127+
to $UT3_TESTER_HELPER;
128+
128129
grant create job to $UT3_TESTER_HELPER;
129130
131+
PROMPT Additional grants for disabling DDL trigger and testing parser without trigger enabled/present
132+
133+
grant alter any trigger to $UT3_TESTER_HELPER;
134+
grant administer database trigger to $UT3_TESTER_HELPER;
135+
grant execute on dbms_lock to $UT3_TESTER_HELPER;
136+
137+
create user ut3_cache_test_owner identified by ut3;
138+
grant create session, create procedure to ut3_cache_test_owner;
139+
140+
create user ut3_no_extra_priv_user identified by ut3;
141+
grant create session, create procedure to ut3_no_extra_priv_user;
142+
143+
create user ut3_select_catalog_user identified by ut3;
144+
grant create session, create procedure, select_catalog_role to ut3_select_catalog_user;
145+
146+
create user ut3_select_any_table_user identified by ut3;
147+
grant create session, create procedure, select any table to ut3_select_any_table_user;
148+
149+
create user ut3_execute_any_proc_user identified by ut3;
150+
grant create session, create procedure, execute any procedure to ut3_execute_any_proc_user;
130151
exit
131152
SQL

‎development/cleanup.sh

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,11 @@ drop user ${UT3_RELEASE_VERSION_SCHEMA} cascade;
2323
drop user ${UT3_TESTER} cascade;
2424
drop user ${UT3_TESTER_HELPER} cascade;
2525
drop user ${UT3_USER} cascade;
26+
drop user ut3_cache_test_owner cascade;
27+
drop user ut3_no_extra_priv_user cascade;
28+
drop user ut3_select_catalog_user cascade;
29+
drop user ut3_select_any_table_user cascade;
30+
drop user ut3_execute_any_proc_user cascade;
2631
2732
begin
2833
for i in (

‎docs/userguide/install.md

Lines changed: 0 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -138,13 +138,6 @@ cd source
138138
sqlplus sys/sys_pass@db as sysdba @install_headless_with_trigger.sql utp3 my_verySecret_password utp3_tablespace
139139
```
140140

141-
**Note:**
142-
>When installing utPLSQL into database with existing unit test packages, utPLSQL will not be able to already-existing unit test packages. When utPSLQL was installed with DDL trigger, you have to do one of:
143-
>- Recompile existing Unit Test packages to make utPLSQL aware of their existence
144-
>- Invoke `exec ut_runner.rebuild_annotation_cache(a_object_owner=> ... );` for every schema containing unit tests in your database
145-
>
146-
> Steps above are required to assure annotation cache is populated properly from existing objects. Rebuilding annotation cache might be faster than code recompilation.
147-
148141
# Recommended Schema
149142
It is highly recommended to install utPLSQL in it's own schema. You are free to choose any name for this schema.
150143
Installing uPLSQL into shared schema is really not recommended as you loose isolation of framework.

‎source/core/annotations/ut_annotation_cache_info.sql

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,8 @@ 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+
constraint ut_annotation_cache_info_pk primary key(cache_id) using index,
21+
constraint ut_annotation_cache_info_uk unique (object_owner, object_type, object_name) using index,
22+
constraint ut_annotation_cache_info_fk foreign key(object_owner, object_type) references ut_annotation_cache_schema(object_owner, object_type) on delete cascade
2223
) organization index;
2324

‎source/core/annotations/ut_annotation_cache_manager.pkb

Lines changed: 72 additions & 52 deletions
Original file line numberDiff line numberDiff line change
@@ -17,15 +17,25 @@ 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
2939
where (i.object_owner, i.object_name, i.object_type)
3040
in ((a_object.object_owner, a_object.object_name, a_object.object_type))
3141
returning cache_id into l_cache_id;
@@ -34,23 +44,12 @@ create or replace package body ut_annotation_cache_manager as
3444

3545
insert into ut_annotation_cache_info
3646
(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+
values (ut_annotation_cache_seq.nextval, a_object.object_owner, a_object.object_name, a_object.object_type, l_timestamp)
3848
returning cache_id into l_cache_id;
39-
l_new_objects_count := 1;
4049
end if;
4150

4251
end if;
4352

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

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

6564

66-
procedure cleanup_cache(a_objects ut_annotation_objs_cache_info) is
65+
procedure reset_objects_cache(a_objects ut_annotation_objs_cache_info) is
66+
l_timestamp timestamp := systimestamp;
6767
pragma autonomous_transaction;
6868
begin
6969

@@ -75,23 +75,45 @@ create or replace package body ut_annotation_cache_manager as
7575
on o.object_name = i.object_name
7676
and o.object_type = i.object_type
7777
and o.object_owner = i.object_owner
78+
and o.needs_refresh = 'Y'
7879
);
7980

81+
update ut_annotation_cache_schema s
82+
set s.max_parse_time = l_timestamp
83+
where (s.object_owner, s.object_type)
84+
in (
85+
select o.object_owner, o.object_type
86+
from table(a_objects) o
87+
where o.needs_refresh = 'Y'
88+
);
89+
90+
if sql%rowcount = 0 then
91+
insert into ut_annotation_cache_schema s
92+
(object_owner, object_type, max_parse_time)
93+
select distinct o.object_owner, o.object_type, l_timestamp
94+
from table(a_objects) o
95+
where o.needs_refresh = 'Y';
96+
end if;
97+
8098
merge into ut_annotation_cache_info i
8199
using (select o.object_name, o.object_type, o.object_owner
82-
from table(a_objects) o ) o
100+
from table(a_objects) o
101+
where o.needs_refresh = 'Y'
102+
) o
83103
on (o.object_name = i.object_name
84104
and o.object_type = i.object_type
85105
and o.object_owner = i.object_owner)
86-
when matched then update set parse_time = systimestamp
106+
when matched then
107+
update
108+
set parse_time = l_timestamp
87109
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);
110+
(cache_id, object_owner, object_name, object_type, parse_time)
111+
values (ut_annotation_cache_seq.nextval, o.object_owner, o.object_name, o.object_type, l_timestamp);
90112

91113
commit;
92114
end;
93115

94-
function get_annotations_objects_info(a_object_owner varchar2, a_object_type varchar2) return ut_annotation_objs_cache_info is
116+
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
95117
l_result ut_annotation_objs_cache_info;
96118
begin
97119
select ut_annotation_obj_cache_info(
@@ -104,7 +126,8 @@ create or replace package body ut_annotation_cache_manager as
104126
bulk collect into l_result
105127
from ut_annotation_cache_info i
106128
where i.object_owner = a_object_owner
107-
and i.object_type = a_object_type;
129+
and i.object_type = a_object_type
130+
and (i.parse_time > a_parsed_after or a_parsed_after is null);
108131
return l_result;
109132
end;
110133

@@ -123,6 +146,16 @@ create or replace package body ut_annotation_cache_manager as
123146
return l_result;
124147
end;
125148

149+
procedure set_fully_refreshed(a_object_owner varchar2, a_object_type varchar2) is
150+
pragma autonomous_transaction;
151+
begin
152+
update ut_annotation_cache_schema s
153+
set s.full_refresh_time = s.max_parse_time
154+
where s.object_owner = a_object_owner
155+
and s.object_type = a_object_type;
156+
commit;
157+
end;
158+
126159
procedure remove_from_cache(a_objects ut_annotation_objs_cache_info) is
127160
pragma autonomous_transaction;
128161
begin
@@ -138,10 +171,10 @@ create or replace package body ut_annotation_cache_manager as
138171
commit;
139172
end;
140173

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;
174+
function get_annotations_parsed_since(a_object_owner varchar2, a_object_type varchar2, a_parsed_after timestamp) return sys_refcursor is
175+
l_results sys_refcursor;
143176
begin
144-
open l_results for q'[
177+
open l_results for
145178
select ut_annotated_object(
146179
i.object_owner, i.object_name, i.object_type, i.parse_time,
147180
cast(
@@ -151,14 +184,12 @@ create or replace package body ut_annotation_cache_manager as
151184
) order by c.annotation_position
152185
) as ut_annotations
153186
)
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
187+
) as annotated_object
188+
from ut_annotation_cache_info i
158189
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;
190+
where i.object_owner = a_object_owner and i.object_type = a_object_type
191+
and (i.parse_time > a_parsed_after or a_parsed_after is null)
192+
group by i.object_owner, i.object_type, i.object_name, i.parse_time;
162193
return l_results;
163194
end;
164195

@@ -168,35 +199,24 @@ create or replace package body ut_annotation_cache_manager as
168199
pragma autonomous_transaction;
169200
begin
170201
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;
202+
l_filter := ':a_object_owner is null and :a_object_type is null';
203+
l_cache_filter := l_filter;
173204
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-
)';
205+
l_filter := case when a_object_owner is null then ':a_object_owner is null' else 'object_owner = :a_object_owner' end;
206+
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;
207+
l_cache_filter := ' c.cache_id in (select i.cache_id from ut_annotation_cache_info i where ' || l_filter || ' )';
182208
end if;
183-
execute immediate '
184-
delete from ut_annotation_cache c
185-
where '||l_cache_filter
209+
execute immediate 'delete from ut_annotation_cache c where ' || l_cache_filter
186210
using a_object_owner, a_object_type;
187211

188-
execute immediate '
189-
delete from ut_annotation_cache_info i
190-
where ' || l_filter
212+
execute immediate ' delete from ut_annotation_cache_info i where ' || l_filter
191213
using a_object_owner, a_object_type;
192214

193-
execute immediate '
194-
delete from ut_annotation_cache_schema s
195-
where ' || l_filter
215+
execute immediate ' delete from ut_annotation_cache_schema s where ' || l_filter
196216
using a_object_owner, a_object_type;
197217

198218
commit;
199219
end;
200220

201-
end ut_annotation_cache_manager;
221+
end;
202222
/

‎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