Initial check-in.

There is still no error handling.
utPLSQL · lwasylow · Apr 23, 2022 · Mar 18, 2022 · Mar 19, 2022 · Mar 23, 2022
commit 86d4d0a76475a8a2eb78c1a1ae72e930c3afbf33
diff --git a/source/core/types/ut_path_item.tpb b/source/core/types/ut_path_item.tpb
@@ -0,0 +1,42 @@
+create or replace type body ut_path_item as
+  /*
+  utPLSQL - Version 3
+  Copyright 2016 - 2021 utPLSQL Project
+
+  Licensed under the Apache License, Version 2.0 (the "License"):
+  you may not use this file except in compliance with the License.
+  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+  Unless required by applicable law or agreed to in writing, software
+  distributed under the License is distributed on an "AS IS" BASIS,
+  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+  See the License for the specific language governing permissions and
+  limitations under the License.
+  */
+  constructor function ut_path_item(self in out nocopy ut_path_item, schema_name varchar2, object_name varchar2,procedure_name varchar2) return self as result is
+  begin
+    self.schema_name := schema_name;
+    self.object_name := object_name;
+    self.procedure_name :=  procedure_name;
+    return;
+  end;
+
+  constructor function ut_path_item(self in out nocopy ut_path_item, schema_name varchar2,suite_path varchar2) return self as result is
+  begin
+    self.schema_name := schema_name;
+    self.suite_path := suite_path;
+    return;
+  end;
+
+  constructor function ut_path_item(self in out nocopy ut_path_item, schema_name varchar2, object_name varchar2,procedure_name varchar2,suite_path varchar2) return self as result is
+  begin
+    self.schema_name := schema_name;
+    self.object_name := object_name;
+    self.procedure_name :=  procedure_name;
+    self.suite_path :=  suite_path;
+    return;
+  end;  
+end;
+/
diff --git a/source/core/types/ut_path_item.tps b/source/core/types/ut_path_item.tps
@@ -0,0 +1,26 @@
+create or replace type ut_path_item as object (
+  /*
+  utPLSQL - Version 3
+  Copyright 2016 - 2021 utPLSQL Project
+
+  Licensed under the Apache License, Version 2.0 (the "License"):
+  you may not use this file except in compliance with the License.
+  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+  Unless required by applicable law or agreed to in writing, software
+  distributed under the License is distributed on an "AS IS" BASIS,
+  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+  See the License for the specific language governing permissions and
+  limitations under the License.
+  */
+  schema_name    varchar2(4000),
+  object_name    varchar2(250),
+  procedure_name varchar2(250),
+  suite_path     varchar2(4000),
+  constructor function ut_path_item(self in out nocopy ut_path_item, schema_name varchar2, object_name varchar2,procedure_name varchar2) return self as result,
+  constructor function ut_path_item(self in out nocopy ut_path_item, schema_name varchar2,suite_path varchar2) return self as result,
+  constructor function ut_path_item(self in out nocopy ut_path_item, schema_name varchar2, object_name varchar2,procedure_name varchar2,suite_path varchar2) return self as result
+)
+/
diff --git a/source/core/types/ut_path_items.tps b/source/core/types/ut_path_items.tps
@@ -0,0 +1,19 @@
+create or replace type ut_path_items as
+  /*
+  utPLSQL - Version 3
+  Copyright 2016 - 2021 utPLSQL Project
+
+  Licensed under the Apache License, Version 2.0 (the "License"):
+  you may not use this file except in compliance with the License.
+  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+  Unless required by applicable law or agreed to in writing, software
+  distributed under the License is distributed on an "AS IS" BASIS,
+  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+  See the License for the specific language governing permissions and
+  limitations under the License.
+  */
+  table of ut_path_item
+/
diff --git a/source/core/ut_suite_cache_manager.pkb b/source/core/ut_suite_cache_manager.pkb
@@ -20,7 +20,7 @@ create or replace package body ut_suite_cache_manager is
   * Private code
   */
 
-  gc_get_cache_suite_sql    constant varchar2(32767) :=
+ gc_get_cache_suite_sql    constant varchar2(32767) :=
     q'[with
       suite_items as (
         select  /*+ cardinality(c 500) */ value(c) as obj
@@ -83,6 +83,87 @@ create or replace package body ut_suite_cache_manager is
       from items c
      order by c.obj.object_owner,{:random_seed:}]';
 
+
+  gc_get_bulk_cache_suite_sql    constant varchar2(32767) :=
+    q'[with
+      suite_paths_tabs as  (
+        select schema_name,object_name,procedure_name,suite_path
+          from table(:l_schema_paths)
+      ),    
+      suite_items as (
+        select  /*+ cardinality(c 500) */ value(c) as obj
+          from ut_suite_cache c,
+    	  suite_paths_tabs sp     
+          where c.object_owner = upper(sp.schema_name)
+            and sp.suite_path is not null
+            and (
+              sp.suite_path||'.' like  c.path||'.%' /*all parents and self*/               
+              or
+                (
+                c.path||'.' like sp.suite_path||'.%'	/*all children and self*/
+                and c.object_name like nvl(upper(sp.object_name),c.object_name)						 
+                and c.name like nvl(upper(sp.procedure_name),c.name) 
+              )
+            )
+        union all
+        select  /*+ cardinality(c 500) */ value(c) as obj
+          from ut_suite_cache c,
+    	  suite_paths_tabs sp     
+          where c.object_owner = upper(sp.schema_name)
+          and sp.suite_path is null
+          and c.object_name like nvl(upper(replace(sp.object_name,'*','%')),c.object_name)					 
+    	  and c.name like nvl(upper(replace(sp.procedure_name,'*','%')),c.name)
+      ),
+      {:tags:}
+      suitepaths as (
+        select distinct substr(c.obj.path,1,instr(c.obj.path,'.',-1)-1) as suitepath,
+                        c.obj.path as path,
+                        c.obj.object_owner as object_owner
+          from {:suite_item_name:} c
+         where c.obj.self_type = 'UT_SUITE'
+      ),
+        gen as (
+        select rownum as pos
+          from xmltable('1 to 20')
+      ),
+      suitepath_part AS (
+        select distinct
+                        substr(b.suitepath, 1, instr(b.suitepath || '.', '.', 1, g.pos) -1) as path,
+                        object_owner
+          from suitepaths b
+               join gen g
+                 on g.pos <= regexp_count(b.suitepath, '\w+')
+      ),
+      logical_suite_data as (
+        select 'UT_LOGICAL_SUITE' as self_type, p.path, p.object_owner,
+               upper( substr(p.path, instr( p.path, '.', -1 ) + 1 ) ) as object_name,
+               cast(null as ut_executables) as x,
+               cast(null as ut_varchar2_rows) as y,
+               cast(null as ut_executable_test) as z
+          from suitepath_part p
+         where p.path
+           not in (select s.path from suitepaths s)
+      ),
+      logical_suites as (
+        select ut_suite_cache_row(
+                 null,
+                 s.self_type, s.path, s.object_owner, s.object_name,
+                 s.object_name, null, null, null, null, 0,null,
+                 ut_varchar2_rows(),
+                 s.x, s.x, s.x, s.x, s.x, s.x,
+                 s.y, null, s.z
+               ) as obj
+          from logical_suite_data s
+      ),
+      items as (
+        select obj from {:suite_item_name:}
+        union all
+        select obj from logical_suites
+      )
+    select /*+ no_parallel */ c.obj
+      from items c
+     order by c.obj.object_owner,{:random_seed:}]';
+
   function get_missing_cache_objects(a_object_owner varchar2) return ut_varchar2_rows is
     l_result       ut_varchar2_rows;
     l_data         ut_annotation_objs_cache_info;
@@ -190,6 +271,29 @@ create or replace package body ut_suite_cache_manager is
              ) desc nulls last'
            end;
   end;
+
+  --Possible move logic to type 
+  function group_paths_by_schema(a_paths ut_varchar2_list) return ut_path_items is
+    c_package_path_regex constant varchar2(100) := '^([A-Za-z0-9$#_]+)(\.([A-Za-z0-9$#_\*]+))?(\.([A-Za-z0-9$#_\*]+))?$';
+    l_results            ut_path_items := ut_path_items();
+    l_path_item          ut_path_item;
+  begin
+    for i in 1 .. a_paths.count loop
+      l_results.extend;     
+      if a_paths(i) like '%:%' then
+        l_path_item := ut_path_item(upper(regexp_substr(a_paths(i),'^[^.:]+')),
+                                    ltrim(regexp_substr(a_paths(i),'[.:].*$'),':'));
+        l_results(l_results.last) := l_path_item;
+      else
+        l_path_item := ut_path_item(regexp_substr(a_paths(i), c_package_path_regex, subexpression => 1),  
+                                    regexp_substr(a_paths(i), c_package_path_regex, subexpression => 3),
+                                    regexp_substr(a_paths(i), c_package_path_regex, subexpression => 5));
+        l_results(l_results.last) := l_path_item;
+      end if;  
+    end loop;
+
+    return l_results;
+  end;
 
 
 
@@ -254,6 +358,82 @@ create or replace package body ut_suite_cache_manager is
       using upper(l_object_owner), l_path, l_path, upper(a_object_name), upper(a_procedure_name), l_include_tags, l_include_tags, l_exclude_tags, a_random_seed;
     return l_results;
   end;
+
+  function expand_paths(a_schema_paths ut_path_items) return ut_path_items is
+    l_schema_paths ut_path_items:= ut_path_items();
+  begin
+    with paths_to_expand as (
+      select /*+ no_parallel */ min(path) as suite_path,sp.schema_name as schema_name,nvl(sp.object_name,c.object_name) as object_name,sp.procedure_name as procedure_name
+        from ut_suite_cache c,
+        table(a_schema_paths) sp
+       where c.object_owner = upper(sp.schema_name)
+         and c.object_name like  nvl(replace(upper(sp.object_name),'*','%'),c.object_name)
+         and c.name like nvl(replace(upper(sp.procedure_name),'*','%'), c.name)
+         and sp.suite_path is null
+         group by sp.schema_name,nvl(sp.object_name,c.object_name),sp.procedure_name
+      union all
+      select /*+ no_parallel */ c.path as suite_path,sp.schema_name,sp.object_name,sp.procedure_name as procedure_name
+      from
+        ut_suite_cache c,
+        table(a_schema_paths) sp
+       where c.object_owner = upper(sp.schema_name)
+       and sp.suite_path is not null
+       and instr(sp.suite_path,'*') > 0
+       and c.path like replace(sp.suite_path,'*','%')
+      union all
+      select /*+ no_parallel */ sp.suite_path as suite_path,sp.schema_name,sp.object_name,sp.procedure_name as procedure_name
+      from table(a_schema_paths) sp
+       where sp.suite_path is not null
+       and instr(sp.suite_path,'*') = 0
+    )
+    select ut_path_item(schema_name,object_name,procedure_name,suite_path)
+      bulk collect into l_schema_paths
+      from paths_to_expand;
+    return l_schema_paths;
+  end;
+
+  function get_cached_suite_rows(
+    a_paths            ut_varchar2_list,
+    a_random_seed      positive := null,
+    a_tags             ut_varchar2_rows := null
+  ) return ut_suite_cache_rows is
+    l_results         ut_suite_cache_rows := ut_suite_cache_rows();
+    l_schema_paths    ut_path_items;
+    l_tags            ut_varchar2_rows := coalesce(a_tags,ut_varchar2_rows());
+    l_include_tags    ut_varchar2_rows;
+    l_exclude_tags    ut_varchar2_rows;
+    l_suite_item_name varchar2(20);
+    l_paths           ut_varchar2_rows;
+    l_schema          varchar2(4000);
+    l_sql             varchar2(32767);
+  begin     
+    select /*+ no_parallel */ column_value
+      bulk collect into l_include_tags
+      from table(l_tags)
+     where column_value not like '-%';
+
+    select /*+ no_parallel */ ltrim(column_value,'-')
+      bulk collect into l_exclude_tags
+      from table(l_tags)
+     where column_value like '-%';
+
+    l_schema_paths := expand_paths(group_paths_by_schema(a_paths));
+    --We still need to turn this into qualified SQL name....maybe as part of results ?      
+    l_suite_item_name := case when l_tags.count > 0 then 'suite_items_tags' else 'suite_items' end;
+
+    l_sql := gc_get_bulk_cache_suite_sql;
+    l_sql := replace(l_sql,'{:suite_item_name:}',l_suite_item_name);
+    l_sql := replace(l_sql,'{:tags:}',get_tags_sql(l_tags.count));
+    l_sql := replace(l_sql,'{:random_seed:}',get_random_seed_sql(a_random_seed));
+    ut_event_manager.trigger_event(ut_event_manager.gc_debug, ut_key_anyvalues().put('l_sql',l_sql) );
+
+    execute immediate l_sql
+      bulk collect into l_results
+      using l_schema_paths, l_include_tags, l_include_tags, l_exclude_tags, a_random_seed;
+    return l_results;
+  end;
+
+
 
   function get_schema_parse_time(a_schema_name varchar2) return timestamp result_cache is
     l_cache_parse_time timestamp;

diff --git a/source/core/ut_suite_cache_manager.pks b/source/core/ut_suite_cache_manager.pks
@@ -53,6 +53,12 @@ create or replace package ut_suite_cache_manager authid definer is
     a_random_seed      positive := null,
     a_tags             ut_varchar2_rows := null
   ) return ut_suite_cache_rows;
+
+  function get_cached_suite_rows(
+    a_paths            ut_varchar2_list,
+    a_random_seed      positive := null,
+    a_tags             ut_varchar2_rows := null
+  ) return ut_suite_cache_rows;
 
   /*
   * Retrieves suite item info rows from cache.