Skip to content

Commit 725d367

Browse files
jgebaljgebal
committed
Refactored and fixed code to use be fully schema-independent.
1 parent 8d37591 commit 725d367

File tree

11 files changed

+62
-54
lines changed

11 files changed

+62
-54
lines changed

‎.travis.yml‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -16,14 +16,15 @@ services:
1616

1717
env:
1818
global:
19-
- UT3_OWNER=ut3
20-
- UT3_OWNER_PASSWORD=ut3
21-
- UT3_RELEASE_VERSION_SCHEMA=UT3_LATEST_RELEASE
19+
- UT3_DEVELOP_SCHEMA=UT3_DEVELOP
20+
- UT3_DEVELOP_SCHEMA_PASSWORD=ut3
21+
- UT3_RELEASE_VERSION_SCHEMA=UT3
22+
- UT3_RELEASE_VERSION_SCHEMA_PASSWORD=ut3
2223
- UT3_USER="UT3\$USER#"
2324
- UT3_USER_PASSWORD=ut3
24-
- UT3_TESTER=ut3_tester
25+
- UT3_TESTER=UT3_TESTER
2526
- UT3_TESTER_PASSWORD=ut3
26-
- UT3_TESTER_HELPER=ut3_tester_helper
27+
- UT3_TESTER_HELPER=UT3_TESTER_HELPER
2728
- UT3_TESTER_HELPER_PASSWORD=ut3
2829
- UT3_TABLESPACE=users
2930
# Environment for building a release

‎.travis/install.sh‎

Lines changed: 23 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,11 @@
33
cd source
44
set -ev
55

6+
INSTALL_FILE="install_headless_with_trigger.sql"
7+
if [[ ! -f "${INSTALL_FILE}" ]]; then
8+
INSTALL_FILE="install_headless.sql"
9+
fi
10+
611
#install core of utplsql
712
time "$SQLCLI" sys/$ORACLE_PWD@//$CONNECTION_STR AS SYSDBA <<-SQL
813
whenever sqlerror exit failure rollback
@@ -11,33 +16,33 @@ set verify off
1116
1217
--alter session set plsql_warnings = 'ENABLE:ALL', 'DISABLE:(5004,5018,6000,6001,6003,6009,6010,7206)';
1318
alter session set plsql_optimize_level=0;
14-
@install_headless_with_trigger.sql $UT3_OWNER $UT3_OWNER_PASSWORD
19+
@${INSTALL_FILE} $UT3_DEVELOP_SCHEMA $UT3_DEVELOP_SCHEMA_PASSWORD
1520
SQL
1621

1722
#Run this step only on second child job (12.1 - at it's fastest)
1823
if [[ "${TRAVIS_JOB_NUMBER}" =~ \.2$ ]]; then
1924

2025
#check code-style for errors
21-
time "$SQLCLI" $UT3_OWNER/$UT3_OWNER_PASSWORD@//$CONNECTION_STR @../development/utplsql_style_check.sql
26+
time "$SQLCLI" $UT3_DEVELOP_SCHEMA/$UT3_DEVELOP_SCHEMA_PASSWORD@//$CONNECTION_STR @../development/utplsql_style_check.sql
2227

2328
#test install/uninstall process
2429
time "$SQLCLI" sys/$ORACLE_PWD@//$CONNECTION_STR AS SYSDBA <<-SQL
2530
set feedback off
2631
set verify off
2732
whenever sqlerror exit failure rollback
2833
29-
@uninstall_all.sql $UT3_OWNER
34+
@uninstall_all.sql $UT3_DEVELOP_SCHEMA
3035
whenever sqlerror exit failure rollback
3136
declare
3237
v_leftover_objects_count integer;
3338
begin
3439
select sum(cnt)
3540
into v_leftover_objects_count
3641
from (
37-
select count(1) cnt from dba_objects where owner = '$UT3_OWNER'
42+
select count(1) cnt from dba_objects where owner = '$UT3_DEVELOP_SCHEMA'
3843
where object_name not like 'PLSQL_PROFILER%' and object_name not like 'DBMSPCC_%'
3944
union all
40-
select count(1) cnt from dba_synonyms where table_owner = '$UT3_OWNER'
45+
select count(1) cnt from dba_synonyms where table_owner = '$UT3_DEVELOP_SCHEMA'
4146
where table_name not like 'PLSQL_PROFILER%' and table_name not like 'DBMSPCC_%'
4247
);
4348
if v_leftover_objects_count > 0 then
@@ -52,9 +57,9 @@ SQL
5257
set verify off
5358
5459
alter session set plsql_optimize_level=0;
55-
@install.sql $UT3_OWNER
56-
@install_ddl_trigger.sql $UT3_OWNER
57-
@create_synonyms_and_grants_for_public.sql $UT3_OWNER
60+
@install.sql $UT3_DEVELOP_SCHEMA
61+
@install_ddl_trigger.sql $UT3_DEVELOP_SCHEMA
62+
@create_synonyms_and_grants_for_public.sql $UT3_DEVELOP_SCHEMA
5863
SQL
5964

6065
fi
@@ -65,8 +70,8 @@ set feedback off
6570
whenever sqlerror exit failure rollback
6671
6772
--------------------------------------------------------------------------------
68-
PROMPT Adding back create-trigger privilege to $UT3_OWNER for testing
69-
grant administer database trigger to $UT3_OWNER;
73+
PROMPT Adding back create-trigger privilege to $UT3_DEVELOP_SCHEMA for testing
74+
grant administer database trigger to $UT3_DEVELOP_SCHEMA;
7075
7176
--------------------------------------------------------------------------------
7277
PROMPT Creating $UT3_TESTER - Power-user for testing internal framework code
@@ -76,27 +81,27 @@ grant create session, create procedure, create type, create table to $UT3_TESTER
7681
7782
grant execute on dbms_lock to $UT3_TESTER;
7883
79-
PROMPT Granting $UT3_OWNER code to $UT3_TESTER
84+
PROMPT Granting $UT3_DEVELOP_SCHEMA code to $UT3_TESTER
8085
8186
begin
8287
for i in (
8388
select object_name from all_objects t
8489
where t.object_type in ('PACKAGE','TYPE')
85-
and owner = 'UT3'
90+
and owner = '$UT3_DEVELOP_SCHEMA'
8691
and generated = 'N'
8792
and object_name not like 'SYS%')
8893
loop
89-
execute immediate 'grant execute on $UT3_OWNER."'||i.object_name||'" to $UT3_TESTER';
94+
execute immediate 'grant execute on $UT3_DEVELOP_SCHEMA."'||i.object_name||'" to $UT3_TESTER';
9095
end loop;
9196
end;
9297
/
9398
94-
PROMPT Granting $UT3_OWNER tables to $UT3_TESTER
99+
PROMPT Granting $UT3_DEVELOP_SCHEMA tables to $UT3_TESTER
95100
96101
begin
97-
for i in ( select table_name from all_tables t where owner = 'UT3' and nested = 'NO' and iot_name is null)
102+
for i in ( select table_name from all_tables t where owner = '$UT3_DEVELOP_SCHEMA' and nested = 'NO' and iot_name is null)
98103
loop
99-
execute immediate 'grant select on $UT3_OWNER.'||i.table_name||' to $UT3_TESTER';
104+
execute immediate 'grant select on $UT3_DEVELOP_SCHEMA.'||i.table_name||' to $UT3_TESTER';
100105
end loop;
101106
end;
102107
/
@@ -119,11 +124,11 @@ PROMPT Grants for testing distributed transactions
119124
grant create public database link to $UT3_TESTER_HELPER;
120125
grant drop public database link to $UT3_TESTER_HELPER;
121126
122-
PROMPT Grants for testing coverage outside of main UT3 schema.
127+
PROMPT Grants for testing coverage outside of main $UT3_DEVELOP_SCHEMA schema.
123128
grant create any procedure, drop any procedure, execute any procedure, create any type, drop any type, execute any type, under any type,
124129
select any table, update any table, insert any table, delete any table, create any table, drop any table, alter any table,
125130
select any dictionary, create any synonym, drop any synonym,
126-
grant any object privilege, grant any privilege
131+
grant any object privilege, grant any privilege, create public synonym, drop public synonym
127132
to $UT3_TESTER_HELPER;
128133
129134
grant create job to $UT3_TESTER_HELPER;

‎.travis/install_utplsql_release.sh‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -11,15 +11,15 @@ set trimspool on
1111
declare
1212
i integer := 0;
1313
begin
14-
dbms_output.put_line('Dropping synonyms pointing to schema '||upper('${UT3_OWNER}'));
14+
dbms_output.put_line('Dropping synonyms pointing to schema '||upper('${UT3_DEVELOP_SCHEMA}'));
1515
for syn in (
1616
select
1717
case when owner = 'PUBLIC'
1818
then 'public synonym '
1919
else 'synonym ' || owner || '.' end || synonym_name as syn_name,
2020
table_owner||'.'||table_name as for_object
2121
from all_synonyms s
22-
where table_owner = upper('${UT3_OWNER}') and table_owner != owner
22+
where table_owner = upper('${UT3_DEVELOP_SCHEMA}') and table_owner != owner
2323
)
2424
loop
2525
i := i + 1;
@@ -28,7 +28,7 @@ begin
2828
dbms_output.put_line('Dropped '||syn.syn_name||' for object '||syn.for_object);
2929
exception
3030
when others then
31-
dbms_output.put_line('FAILED to drop '||syn.syn_name||' for object '||syn.for_object);
31+
dbms_output.put_line('FAILED to drop '||syn.syn_name||' for object '||syn.for_object||' with error '||sqlerrm);
3232
end;
3333
end loop;
3434
dbms_output.put_line(i||' synonyms dropped');
@@ -45,7 +45,7 @@ fi
4545
alter session set plsql_optimize_level=0;
4646
alter session set plsql_ccflags = 'SELF_TESTING_INSTALL:TRUE';
4747
48-
@${INSTALL_FILE} ${UT3_RELEASE_VERSION_SCHEMA}
48+
@${INSTALL_FILE} ${UT3_RELEASE_VERSION_SCHEMA} ${UT3_RELEASE_VERSION_SCHEMA_PASSWORD}
4949
exit
5050
SQL
5151

‎.travis/run_examples.sh‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22

33
set -ev
44

5-
"$SQLCLI" $UT3_OWNER/$UT3_OWNER_PASSWORD@//$CONNECTION_STR <<SQL
5+
"$SQLCLI" $UT3_DEVELOP_SCHEMA/$UT3_DEVELOP_SCHEMA_PASSWORD@//$CONNECTION_STR <<SQL
66
whenever sqlerror exit failure rollback
77
whenever oserror exit failure rollback
88

‎development/cleanup.sh‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -10,15 +10,15 @@ set echo on
1010
begin
1111
for x in (
1212
select * from dba_objects
13-
where owner in ( upper('${UT3_RELEASE_VERSION_SCHEMA}'), upper('${UT3_OWNER}') )
13+
where owner in ( upper('${UT3_RELEASE_VERSION_SCHEMA}'), upper('${UT3_DEVELOP_SCHEMA}') )
1414
and object_name like 'SYS_PLSQL%')
1515
loop
1616
execute immediate 'drop type '||x.owner||'.'||x.object_name||' force';
1717
end loop;
1818
end;
1919
/
2020
21-
drop user ${UT3_OWNER} cascade;
21+
drop user ${UT3_DEVELOP_SCHEMA} cascade;
2222
drop user ${UT3_RELEASE_VERSION_SCHEMA} cascade;
2323
drop user ${UT3_TESTER} cascade;
2424
drop user ${UT3_TESTER_HELPER} cascade;

‎development/install.sh‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@ if ! development/cleanup.sh; then
1111
exit 1
1212
fi
1313
if ! .travis/install.sh; then
14-
echo -e ${header}"\nFailed to install utPLSQL from current branch into ${UT3_OWNER} schema\n"${header}
14+
echo -e ${header}"\nFailed to install utPLSQL from current branch into ${UT3_DEVELOP_SCHEMA} schema\n"${header}
1515
exit 1
1616
fi
1717
if ! .travis/install_utplsql_release.sh; then

‎development/refresh_ut3.sh‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@ git rev-parse && cd "$(git rev-parse --show-cdup)"
88
cd source
99

1010
"${SQLCLI}" sys/${ORACLE_PWD}@//${CONNECTION_STR} AS SYSDBA <<-SQL
11-
@uninstall ${UT3_OWNER}
12-
@install ${UT3_OWNER}
11+
@uninstall ${UT3_DEVELOP_SCHEMA}
12+
@install ${UT3_DEVELOP_SCHEMA}
1313
exit
1414
SQL

‎development/template.env.sh‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,12 +8,13 @@ export UTPLSQL_CLI_VERSION="3.1.6"
88
export SELFTESTING_BRANCH=develop
99

1010
export UTPLSQL_DIR="utPLSQL_latest_release"
11-
export UT3_OWNER=ut3
12-
export UT3_OWNER_PASSWORD=ut3
13-
export UT3_RELEASE_VERSION_SCHEMA=ut3_latest_release
14-
export UT3_TESTER=ut3_tester
11+
export UT3_DEVELOP_SCHEMA=UT3_DEVELOP
12+
export UT3_DEVELOP_SCHEMA_PASSWORD=ut3
13+
export UT3_RELEASE_VERSION_SCHEMA=UT3
14+
export UT3_RELEASE_VERSION_SCHEMA_PASSWORD=ut3
15+
export UT3_TESTER=UT3_TESTER
1516
export UT3_TESTER_PASSWORD=ut3
16-
export UT3_TESTER_HELPER=ut3_tester_helper
17+
export UT3_TESTER_HELPER=UT3_TESTER_HELPER
1718
export UT3_TESTER_HELPER_PASSWORD=ut3
1819
export UT3_TABLESPACE=users
1920
export UT3_USER="UT3\$USER#"

‎docs/userguide/install.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -225,7 +225,7 @@ To grant utPLSQL to an individual user, execute scripts `source/create_user_gran
225225
Example invocation:
226226
```bash
227227
cd source
228-
sqlplus ut3_user/ut3_password@database @create_user_grants.sql ut3 hr
228+
sqlplus ut3_owner_schema/ut3_password@database @create_user_grants.sql ut3 hr
229229
sqlplus user/user_password@database @create_user_synonyms.sql ut3 hr
230230
```
231231

‎source/core/ut_suite_manager.pkb‎

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -374,7 +374,7 @@ create or replace package body ut_suite_manager is
374374
a_owner_name varchar2
375375
) return boolean is
376376
begin
377-
return sys_context( 'userenv', 'current_user' ) = a_owner_name or ut_metadata.user_has_execute_any_proc();
377+
return sys_context( 'userenv', 'current_user' ) = upper(a_owner_name) or ut_metadata.user_has_execute_any_proc();
378378
end;
379379

380380
procedure build_and_cache_suites(
@@ -425,17 +425,18 @@ create or replace package body ut_suite_manager is
425425
) is
426426
l_annotations_cursor sys_refcursor;
427427
l_suite_cache_time timestamp;
428+
l_owner_name varchar2(128) := upper(a_owner_name);
428429
begin
429430
ut_event_manager.trigger_event('refresh_cache - start');
430-
l_suite_cache_time := ut_suite_cache_manager.get_schema_parse_time(a_owner_name);
431+
l_suite_cache_time := ut_suite_cache_manager.get_schema_parse_time(l_owner_name);
431432
l_annotations_cursor := ut_annotation_manager.get_annotated_objects(
432-
a_owner_name, 'PACKAGE', l_suite_cache_time
433+
l_owner_name, 'PACKAGE', l_suite_cache_time
433434
);
434435

435-
build_and_cache_suites(a_owner_name, l_annotations_cursor);
436+
build_and_cache_suites(l_owner_name, l_annotations_cursor);
436437

437-
if can_skip_all_objects_scan(a_owner_name) or ut_metadata.is_object_visible( 'dba_objects') then
438-
ut_suite_cache_manager.remove_missing_objs_from_cache( a_owner_name );
438+
if can_skip_all_objects_scan(l_owner_name) or ut_metadata.is_object_visible( 'dba_objects') then
439+
ut_suite_cache_manager.remove_missing_objs_from_cache( l_owner_name );
439440
end if;
440441

441442
ut_event_manager.trigger_event('refresh_cache - end');

0 commit comments

Comments
 (0)