Repository: incubator-trafodion Updated Branches: refs/heads/master f34fceb0b -> 4dda1198c
TRAFODION [1902] - Showddl continues to show function after it's dropped Fixed a problem during a drop library request, where functions and procedures were being dropped but not removed from DBRoutineCache. Added a new regression test (udr/TEST103) that tests DDL operations related to functions, procedures, and libraries. The regression tests were missing. Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/3fab46b1 Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/3fab46b1 Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/3fab46b1 Branch: refs/heads/master Commit: 3fab46b198d0146386a01964439df57963b86e54 Parents: 6950f11 Author: Roberta Marton <[email protected]> Authored: Fri Mar 25 16:58:16 2016 +0000 Committer: Roberta Marton <[email protected]> Committed: Fri Mar 25 16:58:16 2016 +0000 ---------------------------------------------------------------------- core/sql/regress/tools/runregr_udr.ksh | 2 +- core/sql/regress/udr/EXPECTED103 | 529 ++++++++++++++++++++++++ core/sql/regress/udr/TEST103 | 246 +++++++++++ core/sql/regress/udr/TEST103_functions.cpp | 191 +++++++++ core/sql/regress/udr/TEST103_procs.java | 224 ++++++++++ core/sql/sqlcomp/CmpSeabaseDDLroutine.cpp | 23 +- 6 files changed, 1211 insertions(+), 4 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fab46b1/core/sql/regress/tools/runregr_udr.ksh ---------------------------------------------------------------------- diff --git a/core/sql/regress/tools/runregr_udr.ksh b/core/sql/regress/tools/runregr_udr.ksh index 2906026..c87376d 100755 --- a/core/sql/regress/tools/runregr_udr.ksh +++ b/core/sql/regress/tools/runregr_udr.ksh @@ -446,7 +446,7 @@ done seabase="$SEABASE_REGRESS" # sbtestfiles contains the list of tests to be run in seabase mode if [ "$seabase" -ne 0 ]; then - TESTFILES="TEST001 TEST002 TEST100 TEST101 TEST102 TEST107 TEST108 TEST163" + TESTFILES="TEST001 TEST002 TEST100 TEST101 TEST102 TEST103 TEST107 TEST108 TEST163" SBPFILES= for i in $PFILES; do for j in $TESTFILES; do http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fab46b1/core/sql/regress/udr/EXPECTED103 ---------------------------------------------------------------------- diff --git a/core/sql/regress/udr/EXPECTED103 b/core/sql/regress/udr/EXPECTED103 new file mode 100644 index 0000000..a691f1d --- /dev/null +++ b/core/sql/regress/udr/EXPECTED103 @@ -0,0 +1,529 @@ +>>obey TEST103(set_up); +>>-- compile functions +>>log; +------------------------------------------------------------------------------ +-- Compiling Java source files: Utils.java TEST103_procs.java +-- Executing: $javac -d $REGRRUNDIR $REGRTSTDIR/Utils.java $REGRTSTDIR/TEST103_procs.java +-- $javac returned 0 +------------------------------------------------------------------------------ +------------------------------------------------------------------------------ +-- Archiving Java class files: +-- TEST103_procs.class +-- Utils.class +-- Archive will be written to: TEST103_procs.jar +-- Executing: $jar cMf TEST103_procs.jar TEST103_procs.class Utils.class +-- $jar returned 0 +------------------------------------------------------------------------------ +>> +>>obey TEST103(create_db); +>>-- create a schema with objects +>>create schema udr103sch; + +--- SQL operation complete. +>>set schema udr103sch; + +--- SQL operation complete. +>>drop table if exists subscriber_names; + +--- SQL operation complete. +>>create table subscriber_names ++>( ++> name_id largeint generated by default as identity, ++> company_name varchar(50) not null, ++> company_suffix varchar (50) not null, ++> database_name varchar (50) not null, ++> primary key (name_id) ++>); + +--- SQL operation complete. +>> +>>insert into subscriber_names (company_name, company_suffix, database_name) values ('acme', 'acme.com', 'sql_user1'); + +--- 1 row(s) inserted. +>>insert into subscriber_names (company_name, company_suffix, database_name) values ('esgyn', 'esgyn.com', 'sql_user2'); + +--- 1 row(s) inserted. +>>insert into subscriber_names (company_name, company_suffix, database_name) values ('bethany', 'gmail.com', 'sql_user3'); + +--- 1 row(s) inserted. +>>insert into subscriber_names (company_name, company_suffix, database_name) values ('"texas.instruments"', 'ti.com', 'sql_user4'); + +--- 1 row(s) inserted. +>>insert into subscriber_names (company_name, company_suffix, database_name) values ('"united.airlines"', 'ua.com', 'sql_user5'); + +--- 1 row(s) inserted. +>>insert into subscriber_names (company_name, company_suffix, database_name) values ('webroot', 'webroot.com', 'sql_user6'); + +--- 1 row(s) inserted. +>>insert into subscriber_names (company_name, company_suffix, database_name) values ('"mission.help"', 'comcast.net', 'sql_user7'); + +--- 1 row(s) inserted. +>>insert into subscriber_names (company_name, company_suffix, database_name) values ('radium', 'rd.gov', 'sql_user8'); + +--- 1 row(s) inserted. +>>insert into subscriber_names (company_name, company_suffix, database_name) values ('alcoa', 'alcoa.com', 'sql_user9'); + +--- 1 row(s) inserted. +>>insert into subscriber_names (company_name, company_suffix, database_name) values ('hbase', 'apache.com', 'sql_user10'); + +--- 1 row(s) inserted. +>> +>>CREATE TABLE subscriber_addresses ( ++> id largeint generated by default as identity, ++> street varchar(255) NOT NULL, ++> city varchar(255) NOT NULL, ++> state char(2) NOT NULL, ++> country varchar(255) NOT NULL, ++> code varchar(255) NOT NULL, ++> phone varchar(20) NOT NULL, ++> PRIMARY KEY (id) ++>); + +--- SQL operation complete. +>> +>>INSERT INTO subscriber_addresses (street, city, state, country, code, phone) VALUES ++>('3412 Pleasant Bear Nook', 'Smoke Tree', 'AR', '71629-6396', 'US', '(870) 347-7290'), ++>('1682 Misty Zephyr Manor', 'Owyhee', 'ID', '83976-9599', 'US', '(208) 325-5585'), ++>('2782 Honey Canyon', 'Spikenard', 'WV', '25849-6450', 'US', '(681) 986-5817'), ++>('2413 Burning Prairie Bay', 'Electric City', 'AR', '72311-5330', 'US', '(501) 940-7620'), ++>('2921 Wishing Impasse', 'Frisken Wye', 'UT', '84921-6955', 'US', '(801) 037-7153'), ++>('9058 Foggy Timber Alley', 'Burnt Water', 'CT', '06092-5085', 'US', '(203) 787-4917'), ++>('918 Rocky Terrace', 'Zinzer', 'KY', '40101-3844', 'US', '(859) 721-1763'), ++>('1287 Little Passage', 'Sleeping Buffalo', 'UT', '84177-8231', 'US', '(435) 576-9295'), ++>('5434 Silver Barn Harbour', 'Alcatraz', 'WV', '24893-2726', 'US', '(681) 428-9145'), ++>('2122 Colonial Swale', 'Dugway', 'MN', '56314-6558', 'US', '(612) 814-7362'), ++>('8996 Dusty Embers Expressway', 'Frog Eye', 'IN', '47893-1450', 'US', '(219) 696-1689'), ++>('9714 Crystal Rise Downs', 'Three Brothers', 'CA', '91390-3498', 'US', '(831) 451-2536'), ++>('6504 Broad Cloud Byway', 'Agricola', 'SD', '57815-0738', 'US', '(605) 429-0448'), ++>('7277 Cotton Point', 'Muddy Ford', 'LA', '71379-1156', 'US', '(504) 497-5662'), ++>('8009 Iron Willow Via', 'Clappers', 'AR', '72567-9326', 'US', '(479) 988-8059'), ++>('2882 Indian Robin Orchard', 'Chulahoma', 'NC', '28425-9027', 'US', '(704) 036-8613'), ++>('3106 Umber Green', 'Improve', 'CA', '93982-4948', 'US', '(714) 466-4395'), ++>('9228 Bright Hickory Crossing', 'Morocco', 'VT', '05215-0676', 'US', '(802) 624-7015'), ++>('6725 Shady Oak Estates', 'Devils Lake', 'WA', '99182-8374', 'US', '(206) 202-1976'), ++>('3169 Tawny Grove Square', 'Bushnell', 'AZ', '85671-8633', 'US', '(928) 430-8819'); + +--- 20 row(s) inserted. +>> +>> +>>create table subscribers ++>( ++> subscriber_id largeint generated by default as identity, ++> subscriber_user varchar (128) default null, ++> subscriber_name varchar(150) not null, ++> subscriber_address varchar (500) not null, ++> subscriber_state char(2) not null, ++> subscriber_phone char(25) not null, ++> subscriber_email char (100) not null, ++> subscription_package int not null, ++> zones_available char(10) not null, ++> devices_available int not null, ++> primary key (subscriber_address) ++>); + +--- SQL operation complete. +>> +>>insert into subscribers ++> (subscriber_user, subscriber_name, subscriber_address, ++> subscriber_state, subscriber_phone, subscriber_email, ++> subscription_package, zones_available, devices_available) ++>select ++> database_name, ++> company_name, ++> street || ' ' || city, ++> state, ++> phone, ++> company_name || '@' || company_suffix, ++> 1, 'ABCDEFGHI-', 3 ++>from subscriber_names n, subscriber_addresses a ++>where a.id = n.name_id; + +--- 10 row(s) inserted. +>> +>>insert into subscribers ++> (subscriber_user, subscriber_name, subscriber_address, ++> subscriber_state, subscriber_phone, subscriber_email, ++> subscription_package, zones_available, devices_available) values ++> ('DB__ROOT', 'GREAT_EXPECTATIONS', 'an address', 'CA', '(408) 123-1234', ++> '[email protected]', 6, 'ABCDEFGHI-', 3); + +--- 1 row(s) inserted. +>> +>> +>>obey TEST103(tests); +>>set schema udr103sch; + +--- SQL operation complete. +>>-- create some libraries +>>obey TEST103(create_libraries); +>>-- create a library, make it so name falls alphabetically between +>>-- functions canAccessViews and generatePhoneNumber +>>set schema udr103sch; + +--- SQL operation complete. +>>create library functionsForTest103 ++> file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$; + +--- SQL operation complete. +>>showddl library functionsForTest103; +CREATE LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 FILE '/mnt/rmarton/gitws/incubator-trafodion/core/sqf/../sql/regress/rundir/udr/TEST103_functions.dll' +; + +-- GRANT UPDATE, USAGE ON LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 TO DB__ROOT WITH GRANT OPTION; + +--- SQL operation complete. +>> +>>create library procsForTest103 ++> file $$QUOTE$$ $$REGRRUNDIR$$/$$JARF$$ $$QUOTE$$; + +--- SQL operation complete. +>> +>>-- create some routines; +>>obey TEST103(create_routines); +>>-- functions +>>set schema udr103sch; + +--- SQL operation complete. +>>create function canAccessView ++> (theZone char(1), listOfZones char(10), ++> packageNeeded int, packageHas int) returns (results int) ++> language c parameter style sql external name 'canAccessView' ++> library functionsForTest103 ++> deterministic no sql final call allow any parallelism state area size 1024 ; + +--- SQL operation complete. +>> +>>create function generatePhoneNumber ++> (seedValue int, areaCode char (4)) returns (results char (14)) ++> language c parameter style sql external name 'genPhoneNumber' ++> library functionsForTest103 ++> deterministic no sql final call allow any parallelism state area size 1024 ; + +--- SQL operation complete. +>> +>>create function generateRandomNumber ++> (seedValue int, numberDigits int) returns (results char (14)) ++> language c parameter style sql external name 'genRandomNumber' ++> library functionsForTest103 ++> deterministic no sql final call allow any parallelism state area size 1024 ; + +--- SQL operation complete. +>> +>>-- procedures +>>create procedure updateSubscriptions( ++> IN operation char(20), ++> IN value char(20), ++> IN userName char(128), ++> OUT results varchar(1000)) ++> EXTERNAL NAME 'TEST103_procs.updateSubscriptions' ++> LIBRARY udr103sch.procsForTest103 ++> LANGUAGE JAVA ++> PARAMETER STYLE JAVA ++> READS SQL DATA ++> NO TRANSACTION REQUIRED ++> ISOLATE ++> ; + +--- SQL operation complete. +>> +>> +>> +>> +>> +>> +>>-- try to drop libraries - should fail +>>drop library functionsForTest103; + +*** ERROR[1366] Request failed. One or more dependent procedures exist. + +--- SQL operation failed with errors. +>>drop library procsForTest103; + +*** ERROR[1366] Request failed. One or more dependent procedures exist. + +--- SQL operation failed with errors. +>> +>>-- make sure query invalidation works when dropping routines and libraries +>>showddl library functionsForTest103; +CREATE LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 FILE '/mnt/rmarton/gitws/incubator-trafodion/core/sqf/../sql/regress/rundir/udr/TEST103_functions.dll' +; + +-- GRANT UPDATE, USAGE ON LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 TO DB__ROOT WITH GRANT OPTION; + +--- SQL operation complete. +>>showddl function generatePhoneNumber; + +CREATE FUNCTION TRAFODION.UDR103SCH.GENERATEPHONENUMBER + ( + IN SEEDVALUE INTEGER SIGNED + , IN AREACODE CHAR(4) CHARACTER SET ISO88591 + ) + RETURNS + ( + OUT RESULTS CHAR(14) CHARACTER SET ISO88591 + ) + EXTERNAL NAME 'genPhoneNumber' + LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 + LANGUAGE C + PARAMETER STYLE SQL + NO SQL + NO FINAL CALL + STATE AREA SIZE 1024 + ALLOW ANY PARALLELISM + DETERMINISTIC + SAFE EXECUTION MODE + ; + +-- GRANT EXECUTE ON FUNCTION TRAFODION.UDR103SCH.GENERATEPHONENUMBER TO DB__ROOT WITH GRANT OPTION; + +--- SQL operation complete. +>>showddl procedure updateSubscriptions; + +CREATE PROCEDURE TRAFODION.UDR103SCH.UPDATESUBSCRIPTIONS + ( + IN OPERATION CHAR(20) CHARACTER SET ISO88591 + , IN "VALUE" CHAR(20) CHARACTER SET ISO88591 + , IN USERNAME CHAR(128) CHARACTER SET ISO88591 + , OUT RESULTS VARCHAR(1000) CHARACTER SET ISO88591 + ) + EXTERNAL NAME 'TEST103_procs.updateSubscriptions (java.lang.String,java.lang.String,java.lang.String,java.lang.String[])' + LIBRARY TRAFODION.UDR103SCH.PROCSFORTEST103 + EXTERNAL SECURITY INVOKER + LANGUAGE JAVA + PARAMETER STYLE JAVA + READS SQL DATA + DYNAMIC RESULT SETS 0 + NO TRANSACTION REQUIRED + ISOLATE + ; + +-- GRANT EXECUTE ON FUNCTION TRAFODION.UDR103SCH.UPDATESUBSCRIPTIONS TO DB__ROOT WITH GRANT OPTION; + +--- SQL operation complete. +>> +>>drop library functionsForTest103 cascade; + +--- SQL operation complete. +>>showddl function generatePhoneNumber; + +*** ERROR[1389] Object GENERATEPHONENUMBER does not exist in Trafodion. + +*** ERROR[4082] Object TRAFODION.UDR103SCH.GENERATEPHONENUMBER does not exist or is inaccessible. + +--- SQL operation failed with errors. +>>showddl function canAccessView; + +*** ERROR[1389] Object CANACCESSVIEW does not exist in Trafodion. + +*** ERROR[4082] Object TRAFODION.UDR103SCH.CANACCESSVIEW does not exist or is inaccessible. + +--- SQL operation failed with errors. +>>showddl library functionsForTest103; + +*** ERROR[1389] Object FUNCTIONSFORTEST103 does not exist in Trafodion. + +*** ERROR[4082] Object TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 does not exist or is inaccessible. + +--- SQL operation failed with errors. +>> +>>drop library procsForTest103 cascade; + +--- SQL operation complete. +>>showddl procedure updateSubscriptions; + +*** ERROR[1389] Object UPDATESUBSCRIPTIONS does not exist in Trafodion. + +*** ERROR[4082] Object TRAFODION.UDR103SCH.UPDATESUBSCRIPTIONS does not exist or is inaccessible. + +--- SQL operation failed with errors. +>>showddl library procsForTest103; + +*** ERROR[1389] Object PROCSFORTEST103 does not exist in Trafodion. + +*** ERROR[4082] Object TRAFODION.UDR103SCH.PROCSFORTEST103 does not exist or is inaccessible. + +--- SQL operation failed with errors. +>> +>>-- make sure drop schema works +>>obey TEST103(create_libraries); +>>-- create a library, make it so name falls alphabetically between +>>-- functions canAccessViews and generatePhoneNumber +>>set schema udr103sch; + +--- SQL operation complete. +>>create library functionsForTest103 ++> file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$; + +--- SQL operation complete. +>>showddl library functionsForTest103; +CREATE LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 FILE '/mnt/rmarton/gitws/incubator-trafodion/core/sqf/../sql/regress/rundir/udr/TEST103_functions.dll' +; + +-- GRANT UPDATE, USAGE ON LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 TO DB__ROOT WITH GRANT OPTION; + +--- SQL operation complete. +>> +>>create library procsForTest103 ++> file $$QUOTE$$ $$REGRRUNDIR$$/$$JARF$$ $$QUOTE$$; + +--- SQL operation complete. +>> +>>obey TEST103(create_routines); +>>-- functions +>>set schema udr103sch; + +--- SQL operation complete. +>>create function canAccessView ++> (theZone char(1), listOfZones char(10), ++> packageNeeded int, packageHas int) returns (results int) ++> language c parameter style sql external name 'canAccessView' ++> library functionsForTest103 ++> deterministic no sql final call allow any parallelism state area size 1024 ; + +--- SQL operation complete. +>> +>>create function generatePhoneNumber ++> (seedValue int, areaCode char (4)) returns (results char (14)) ++> language c parameter style sql external name 'genPhoneNumber' ++> library functionsForTest103 ++> deterministic no sql final call allow any parallelism state area size 1024 ; + +--- SQL operation complete. +>> +>>create function generateRandomNumber ++> (seedValue int, numberDigits int) returns (results char (14)) ++> language c parameter style sql external name 'genRandomNumber' ++> library functionsForTest103 ++> deterministic no sql final call allow any parallelism state area size 1024 ; + +--- SQL operation complete. +>> +>>-- procedures +>>create procedure updateSubscriptions( ++> IN operation char(20), ++> IN value char(20), ++> IN userName char(128), ++> OUT results varchar(1000)) ++> EXTERNAL NAME 'TEST103_procs.updateSubscriptions' ++> LIBRARY udr103sch.procsForTest103 ++> LANGUAGE JAVA ++> PARAMETER STYLE JAVA ++> READS SQL DATA ++> NO TRANSACTION REQUIRED ++> ISOLATE ++> ; + +--- SQL operation complete. +>> +>> +>> +>> +>> +>>get functions in schema udr103sch; + +Functions in Schema TRAFODION.UDR103SCH +======================================= + +CANACCESSVIEW +GENERATEPHONENUMBER +GENERATERANDOMNUMBER + +--- SQL operation complete. +>>get procedures in schema udr103sch; + +Procedures in Schema TRAFODION.UDR103SCH +======================================== + +UPDATESUBSCRIPTIONS + +--- SQL operation complete. +>>showddl function generatePhoneNumber; + +CREATE FUNCTION TRAFODION.UDR103SCH.GENERATEPHONENUMBER + ( + IN SEEDVALUE INTEGER SIGNED + , IN AREACODE CHAR(4) CHARACTER SET ISO88591 + ) + RETURNS + ( + OUT RESULTS CHAR(14) CHARACTER SET ISO88591 + ) + EXTERNAL NAME 'genPhoneNumber' + LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 + LANGUAGE C + PARAMETER STYLE SQL + NO SQL + NO FINAL CALL + STATE AREA SIZE 1024 + ALLOW ANY PARALLELISM + DETERMINISTIC + SAFE EXECUTION MODE + ; + +-- GRANT EXECUTE ON FUNCTION TRAFODION.UDR103SCH.GENERATEPHONENUMBER TO DB__ROOT WITH GRANT OPTION; + +--- SQL operation complete. +>>showddl procedure updateSubscriptions; + +CREATE PROCEDURE TRAFODION.UDR103SCH.UPDATESUBSCRIPTIONS + ( + IN OPERATION CHAR(20) CHARACTER SET ISO88591 + , IN "VALUE" CHAR(20) CHARACTER SET ISO88591 + , IN USERNAME CHAR(128) CHARACTER SET ISO88591 + , OUT RESULTS VARCHAR(1000) CHARACTER SET ISO88591 + ) + EXTERNAL NAME 'TEST103_procs.updateSubscriptions (java.lang.String,java.lang.String,java.lang.String,java.lang.String[])' + LIBRARY TRAFODION.UDR103SCH.PROCSFORTEST103 + EXTERNAL SECURITY INVOKER + LANGUAGE JAVA + PARAMETER STYLE JAVA + READS SQL DATA + DYNAMIC RESULT SETS 0 + NO TRANSACTION REQUIRED + ISOLATE + ; + +-- GRANT EXECUTE ON FUNCTION TRAFODION.UDR103SCH.UPDATESUBSCRIPTIONS TO DB__ROOT WITH GRANT OPTION; + +--- SQL operation complete. +>>drop schema udr103sch; + +*** ERROR[1028] The schema must be empty. It contains at least one object CANACCESSVIEW. + +--- SQL operation failed with errors. +>>drop schema udr103sch cascade; + +--- SQL operation complete. +>>showddl function generatePhoneNumber; + +*** ERROR[1389] Object GENERATEPHONENUMBER does not exist in Trafodion. + +*** ERROR[4082] Object TRAFODION.UDR103SCH.GENERATEPHONENUMBER does not exist or is inaccessible. + +--- SQL operation failed with errors. +>>showddl procedure updateSubscriptions; + +*** ERROR[1389] Object UPDATESUBSCRIPTIONS does not exist in Trafodion. + +*** ERROR[4082] Object TRAFODION.UDR103SCH.UPDATESUBSCRIPTIONS does not exist or is inaccessible. + +--- SQL operation failed with errors. +>> +>>log LOG103; +>>obey TEST103(clean_up); +>>set schema udr103sch; + +--- SQL operation complete. +>>drop schema udr103sch cascade; + +*** ERROR[1003] Schema TRAFODION.UDR103SCH does not exist. + +--- SQL operation failed with errors. +>> +>>sh rm -f TEST103_procs.class TEST103_procs.jar; +>> +>> +>>exit; + +End of MXCI Session + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fab46b1/core/sql/regress/udr/TEST103 ---------------------------------------------------------------------- diff --git a/core/sql/regress/udr/TEST103 b/core/sql/regress/udr/TEST103 new file mode 100644 index 0000000..8043cc2 --- /dev/null +++ b/core/sql/regress/udr/TEST103 @@ -0,0 +1,246 @@ +-- @@@ START COPYRIGHT @@@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you 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. +-- +-- @@@ END COPYRIGHT @@@ +-- +-- This script tests DDL operations associted with libraries +-- functions, and procedures +-- +cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; +set pattern $$QUOTE$$ ''''; + + + +obey TEST103(clean_up); +log LOG103 clear; +obey TEST103(set_up); +obey TEST103(create_db); +obey TEST103(tests); +log LOG103; +obey TEST103(clean_up); +exit; + +?section clean_up +set schema udr103sch; +drop schema udr103sch cascade; + +sh rm -f TEST103_procs.class TEST103_procs.jar; + + +?section set_up +-- compile functions +log; +sh rm -f ./TEST103_functions.dll; +sh sh $$scriptsdir$$/tools/dll-compile.ksh TEST103_functions.cpp + 2>&1 | tee LOG103-SECONDARY; +set pattern $$DLL$$ TEST103_functions.dll; + +-- compile procedures +sh sh $$scriptsdir$$/tools/java-compile.ksh Utils.java TEST103_procs.java 2> LOG103-SECONDARY | tee -a LOG103; +sh sh $$scriptsdir$$/tools/java-archive.ksh TEST103_procs.jar TEST103_procs.class Utils.class 2>> LOG103-SECONDARY | tee -a LOG103; +set pattern $$JARF$$ TEST103_procs.jar; + +log LOG103; + +?section create_db +-- create a schema with objects +create schema udr103sch; +set schema udr103sch; +drop table if exists subscriber_names; +create table subscriber_names +( + name_id largeint generated by default as identity, + company_name varchar(50) not null, + company_suffix varchar (50) not null, + database_name varchar (50) not null, + primary key (name_id) +); + +insert into subscriber_names (company_name, company_suffix, database_name) values ('acme', 'acme.com', 'sql_user1'); +insert into subscriber_names (company_name, company_suffix, database_name) values ('esgyn', 'esgyn.com', 'sql_user2'); +insert into subscriber_names (company_name, company_suffix, database_name) values ('bethany', 'gmail.com', 'sql_user3'); +insert into subscriber_names (company_name, company_suffix, database_name) values ('"texas.instruments"', 'ti.com', 'sql_user4'); +insert into subscriber_names (company_name, company_suffix, database_name) values ('"united.airlines"', 'ua.com', 'sql_user5'); +insert into subscriber_names (company_name, company_suffix, database_name) values ('webroot', 'webroot.com', 'sql_user6'); +insert into subscriber_names (company_name, company_suffix, database_name) values ('"mission.help"', 'comcast.net', 'sql_user7'); +insert into subscriber_names (company_name, company_suffix, database_name) values ('radium', 'rd.gov', 'sql_user8'); +insert into subscriber_names (company_name, company_suffix, database_name) values ('alcoa', 'alcoa.com', 'sql_user9'); +insert into subscriber_names (company_name, company_suffix, database_name) values ('hbase', 'apache.com', 'sql_user10'); + +CREATE TABLE subscriber_addresses ( + id largeint generated by default as identity, + street varchar(255) NOT NULL, + city varchar(255) NOT NULL, + state char(2) NOT NULL, + country varchar(255) NOT NULL, + code varchar(255) NOT NULL, + phone varchar(20) NOT NULL, + PRIMARY KEY (id) +); + +INSERT INTO subscriber_addresses (street, city, state, country, code, phone) VALUES +('3412 Pleasant Bear Nook', 'Smoke Tree', 'AR', '71629-6396', 'US', '(870) 347-7290'), +('1682 Misty Zephyr Manor', 'Owyhee', 'ID', '83976-9599', 'US', '(208) 325-5585'), +('2782 Honey Canyon', 'Spikenard', 'WV', '25849-6450', 'US', '(681) 986-5817'), +('2413 Burning Prairie Bay', 'Electric City', 'AR', '72311-5330', 'US', '(501) 940-7620'), +('2921 Wishing Impasse', 'Frisken Wye', 'UT', '84921-6955', 'US', '(801) 037-7153'), +('9058 Foggy Timber Alley', 'Burnt Water', 'CT', '06092-5085', 'US', '(203) 787-4917'), +('918 Rocky Terrace', 'Zinzer', 'KY', '40101-3844', 'US', '(859) 721-1763'), +('1287 Little Passage', 'Sleeping Buffalo', 'UT', '84177-8231', 'US', '(435) 576-9295'), +('5434 Silver Barn Harbour', 'Alcatraz', 'WV', '24893-2726', 'US', '(681) 428-9145'), +('2122 Colonial Swale', 'Dugway', 'MN', '56314-6558', 'US', '(612) 814-7362'), +('8996 Dusty Embers Expressway', 'Frog Eye', 'IN', '47893-1450', 'US', '(219) 696-1689'), +('9714 Crystal Rise Downs', 'Three Brothers', 'CA', '91390-3498', 'US', '(831) 451-2536'), +('6504 Broad Cloud Byway', 'Agricola', 'SD', '57815-0738', 'US', '(605) 429-0448'), +('7277 Cotton Point', 'Muddy Ford', 'LA', '71379-1156', 'US', '(504) 497-5662'), +('8009 Iron Willow Via', 'Clappers', 'AR', '72567-9326', 'US', '(479) 988-8059'), +('2882 Indian Robin Orchard', 'Chulahoma', 'NC', '28425-9027', 'US', '(704) 036-8613'), +('3106 Umber Green', 'Improve', 'CA', '93982-4948', 'US', '(714) 466-4395'), +('9228 Bright Hickory Crossing', 'Morocco', 'VT', '05215-0676', 'US', '(802) 624-7015'), +('6725 Shady Oak Estates', 'Devils Lake', 'WA', '99182-8374', 'US', '(206) 202-1976'), +('3169 Tawny Grove Square', 'Bushnell', 'AZ', '85671-8633', 'US', '(928) 430-8819'); + + +create table subscribers +( + subscriber_id largeint generated by default as identity, + subscriber_user varchar (128) default null, + subscriber_name varchar(150) not null, + subscriber_address varchar (500) not null, + subscriber_state char(2) not null, + subscriber_phone char(25) not null, + subscriber_email char (100) not null, + subscription_package int not null, + zones_available char(10) not null, + devices_available int not null, + primary key (subscriber_address) +); + +insert into subscribers + (subscriber_user, subscriber_name, subscriber_address, + subscriber_state, subscriber_phone, subscriber_email, + subscription_package, zones_available, devices_available) +select + database_name, + company_name, + street || ' ' || city, + state, + phone, + company_name || '@' || company_suffix, + 1, 'ABCDEFGHI-', 3 +from subscriber_names n, subscriber_addresses a +where a.id = n.name_id; + +insert into subscribers + (subscriber_user, subscriber_name, subscriber_address, + subscriber_state, subscriber_phone, subscriber_email, + subscription_package, zones_available, devices_available) values + ('DB__ROOT', 'GREAT_EXPECTATIONS', 'an address', 'CA', '(408) 123-1234', + '[email protected]', 6, 'ABCDEFGHI-', 3); + + +?section tests +set schema udr103sch; +-- create some libraries +obey TEST103(create_libraries); +-- create some routines; +obey TEST103(create_routines); + +-- try to drop libraries - should fail +drop library functionsForTest103; +drop library procsForTest103; + +-- make sure query invalidation works when dropping routines and libraries +showddl library functionsForTest103; +showddl function generatePhoneNumber; +showddl procedure updateSubscriptions; + +drop library functionsForTest103 cascade; +showddl function generatePhoneNumber; +showddl function canAccessView; +showddl library functionsForTest103; + +drop library procsForTest103 cascade; +showddl procedure updateSubscriptions; +showddl library procsForTest103; + +-- make sure drop schema works +obey TEST103(create_libraries); +obey TEST103(create_routines); +get functions in schema udr103sch; +get procedures in schema udr103sch; +showddl function generatePhoneNumber; +showddl procedure updateSubscriptions; +drop schema udr103sch; +drop schema udr103sch cascade; +showddl function generatePhoneNumber; +showddl procedure updateSubscriptions; + +?section create_libraries +-- create a library, make it so name falls alphabetically between +-- functions canAccessViews and generatePhoneNumber +set schema udr103sch; +create library functionsForTest103 + file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$; +showddl library functionsForTest103; + +create library procsForTest103 + file $$QUOTE$$ $$REGRRUNDIR$$/$$JARF$$ $$QUOTE$$; + +?section create_routines +-- functions +set schema udr103sch; +create function canAccessView + (theZone char(1), listOfZones char(10), + packageNeeded int, packageHas int) returns (results int) + language c parameter style sql external name 'canAccessView' + library functionsForTest103 + deterministic no sql final call allow any parallelism state area size 1024 ; + +create function generatePhoneNumber + (seedValue int, areaCode char (4)) returns (results char (14)) + language c parameter style sql external name 'genPhoneNumber' + library functionsForTest103 + deterministic no sql final call allow any parallelism state area size 1024 ; + +create function generateRandomNumber + (seedValue int, numberDigits int) returns (results char (14)) + language c parameter style sql external name 'genRandomNumber' + library functionsForTest103 + deterministic no sql final call allow any parallelism state area size 1024 ; + +-- procedures +create procedure updateSubscriptions( + IN operation char(20), + IN value char(20), + IN userName char(128), + OUT results varchar(1000)) + EXTERNAL NAME 'TEST103_procs.updateSubscriptions' + LIBRARY udr103sch.procsForTest103 + LANGUAGE JAVA + PARAMETER STYLE JAVA + READS SQL DATA + NO TRANSACTION REQUIRED + ISOLATE + ; + + + + + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fab46b1/core/sql/regress/udr/TEST103_functions.cpp ---------------------------------------------------------------------- diff --git a/core/sql/regress/udr/TEST103_functions.cpp b/core/sql/regress/udr/TEST103_functions.cpp new file mode 100644 index 0000000..1ee0531 --- /dev/null +++ b/core/sql/regress/udr/TEST103_functions.cpp @@ -0,0 +1,191 @@ +// @@@ START COPYRIGHT @@@ +// +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you 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. +// +// @@@ END COPYRIGHT @@@ + + +#include <stdio.h> +#include <stdlib.h> +#include <string.h> +#include "sqludr.h" + +extern "C" { + +/* genPhoneNumber */ +SQLUDR_LIBFUNC SQLUDR_INT32 genPhoneNumber(SQLUDR_INT32 *in1, // seed + SQLUDR_CHAR *in2, // areacode + SQLUDR_CHAR *out, + SQLUDR_INT16 *in1Ind, + SQLUDR_INT16 *in2Ind, + SQLUDR_INT16 *outInd, + SQLUDR_TRAIL_ARGS) +{ + if (calltype == SQLUDR_CALLTYPE_FINAL) + return SQLUDR_SUCCESS; + + std::string result (in2); + if (*in1Ind == SQLUDR_NULL || *in2Ind == SQLUDR_NULL) + { + *outInd = SQLUDR_NULL; + } + else + { + srand(*in1); + int number = 7; // 7 digit phone number + for (int i = 0; i < number; i++) + { + int randNumber = rand() %10; + if (i == 0 && randNumber == 0) + randNumber++; + switch (randNumber) + { + case 0: result += '0'; break; + case 1: result += '1'; break; + case 2: result += '2'; break; + case 3: result += '3'; break; + case 4: result += '4'; break; + case 5: result += '5'; break; + case 6: result += '6'; break; + case 7: result += '7'; break; + case 8: result += '8'; break; + default : result += '9'; break; + } + } + } + + strcpy(out, result.c_str()); + return SQLUDR_SUCCESS; +} + + + +/* genRandomNumber */ +SQLUDR_LIBFUNC SQLUDR_INT32 genRandomNumber(SQLUDR_INT32 *in1, + SQLUDR_INT32 *in2, + SQLUDR_CHAR *out, + SQLUDR_INT16 *in1Ind, + SQLUDR_INT16 *in2Ind, + SQLUDR_INT16 *outInd, + SQLUDR_TRAIL_ARGS) +{ + if (calltype == SQLUDR_CALLTYPE_FINAL) + return SQLUDR_SUCCESS; + + std::string result; + if (*in1Ind == SQLUDR_NULL || *in2Ind == SQLUDR_NULL) + { + *outInd = SQLUDR_NULL; + } + else + { + srand(*in1); + int number = *in2; + for (int i = 0; i < number; i++) + { + int randNumber = rand() %10; + if (i == 0 && randNumber == 0) + randNumber++; + switch (randNumber) + { + case 0: result += '0'; break; + case 1: result += '1'; break; + case 2: result += '2'; break; + case 3: result += '3'; break; + case 4: result += '4'; break; + case 5: result += '5'; break; + case 6: result += '6'; break; + case 7: result += '7'; break; + case 8: result += '8'; break; + default : result += '9'; break; + } + } + } + + strcpy(out, result.c_str()); + return SQLUDR_SUCCESS; +} + + +SQLUDR_LIBFUNC SQLUDR_INT32 canAccessView(SQLUDR_CHAR *inZoneNeeded, + SQLUDR_CHAR *inZoneHas, + SQLUDR_INT32 *inPackageNeeded, + SQLUDR_INT32 *inPackageHas, + SQLUDR_INT32 *out, + SQLUDR_INT16 *in1Ind, + SQLUDR_INT16 *in2Ind, + SQLUDR_INT16 *in3Ind, + SQLUDR_INT16 *in4Ind, + SQLUDR_INT16 *outInd, + SQLUDR_TRAIL_ARGS) +{ + if (calltype == SQLUDR_CALLTYPE_FINAL) + return SQLUDR_SUCCESS; + + int zoneFound = 0; + int hasPackage = 0; + + if (*in1Ind == SQLUDR_NULL || + *in2Ind == SQLUDR_NULL || + *in3Ind == SQLUDR_NULL || + *in4Ind == SQLUDR_NULL) + { + *outInd = SQLUDR_NULL; + } + else + { + // verify that the requester has privileges to view + // rows in the requested geographic zone + std::string theZone(inZoneNeeded); + std::string zonesAllowed(inZoneHas); + for (size_t i = 0; i < zonesAllowed.size(); i++) + { + if (theZone[0] == zonesAllowed[i]) + { + zoneFound = 1; + break; + } + } + + // verify that the requester has bought the correct package + switch (*inPackageNeeded) + { + case 1: + if (*inPackageHas == 1 || *inPackageHas == 3 || + *inPackageHas == 5 ||*inPackageHas == 7) + hasPackage = 1; + break; + case 2: + if (*inPackageHas == 2 || *inPackageHas == 3 || + *inPackageHas == 6 || *inPackageHas == 7) + hasPackage = 1; + break; + case 3: + if (*inPackageHas == 4 || *inPackageHas == 5 || + *inPackageHas == 6 || *inPackageHas == 7) + hasPackage = 1; + break; + default: + hasPackage = 0; + } + } + *out = (zoneFound && hasPackage) ? 1 : 0; + return SQLUDR_SUCCESS; +} + +} /* extern "C" */ http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fab46b1/core/sql/regress/udr/TEST103_procs.java ---------------------------------------------------------------------- diff --git a/core/sql/regress/udr/TEST103_procs.java b/core/sql/regress/udr/TEST103_procs.java new file mode 100644 index 0000000..88b02e3 --- /dev/null +++ b/core/sql/regress/udr/TEST103_procs.java @@ -0,0 +1,224 @@ +import java.io.*; +import java.sql.*; +import java.util.*; + +// =================================================================== +// Class: subscriptions +// +// =================================================================== +public class TEST103_procs +{ + // -------------------------------------------------------------------------- + // Main code for subscriptions procedure + // -------------------------------------------------------------------------- + public static void updateSubscriptions ( + String operationIn, + String valueIn, + String userIn, + String[] results) + + throws SQLException + { + // Initialize the Utils structure + // Utils provides connection, user, current time, and logging information + Utils util = null; + + try + { + util = new Utils(); + } + catch(Exception e) + { + String theError = "ERROR: unable to create the Util object"; + throw e; + } + + util.log (""); + util.log ("***** Starting subscriptions request *****"); + + // Set up the connection + Connection conn = null; + String sessionUser = null; + String currentTime = null; + try + { + conn = util.myConnection(); + } + catch(Exception e) + { + String theError = "ERROR: unable to get a connection"; + throw e; + } + + sessionUser = util.getSessionUser(conn); + currentTime = util.getCurrentTime(conn); + + results[0] = "Session user: " + sessionUser + '\n'; + results[0] += "Current time: " + currentTime + '\n'; + + util.log("Session user: " + sessionUser); + util.log("Current time: " + currentTime); + + + boolean isAdd = false; + boolean isDrop = false; + boolean isSub = false; + boolean isRegion = false; + + String operation = operationIn.toUpperCase().trim(); + String inputRqst = "Operation: " + operation + " Change: " + valueIn + "User: " + userIn + '\n'; + util.log (inputRqst); + if (operation.equals("ADD_SUB")) + { + isAdd = true; + isSub = true; + } + else if (operation.equals("DROP_SUB")) + { + isDrop = true; + isSub = true; + } + else if (operation.equalsIgnoreCase("ADD_REGIONS")) + { + isAdd = true; + isRegion = true; + } + else if (operation.equals("DROP_REGIONS")) + { + isDrop = true; + isRegion = true; + } + else //help + { + results[0] += "Help option requested: " + '\n' + '\n'; + results[0] += "PROCEDURE SUBSCRIPTIONS " + '\n'; + results[0] += " (<operation>, <newvalue>, <results>) " + '\n'; + results[0] += " <operation> ::= one of the following" + '\n'; + results[0] += " ADD_SUB - add a subscription" + '\n'; + results[0] += " DROP_SUB - drop a subscription" + '\n'; + results[0] += " ADD_REGIONS - adds regions" + '\n'; + results[0] += " DROP_REGIONS - drops regions" + '\n'; + results[0] += " HELP - add a subscription" + '\n'; + results[0] += " <newValue> ::= what to add or drop " + '\n'; + results[0] += " <results> ::= details of the operation"; + return; + } + + // get subscription record + int updatedSub = 0; + int updatedDevice = 0; + String userName = userIn.toUpperCase().trim(); + String updatedRegions = "----------"; + try + { + String selectQuery = "select subscription_package, zones_available, "; + selectQuery += "devices_available from subscribers.subscribers "; + selectQuery += "where upper(subscriber_user) = '"; + selectQuery += userName; + selectQuery += "';"; + //results[0] += "selectQuery: " + selectQuery + '\n'; + util.log("selecting subscriber: " + selectQuery); + + + Statement selectStmt = conn.createStatement(); + ResultSet selectRS = selectStmt.executeQuery(selectQuery); + if (selectRS.next()) + { + updatedSub = selectRS.getInt(1); + updatedRegions = selectRS.getString(2); + updatedDevice = selectRS.getInt(3); + } + util.log("retrieved values - sub: " + updatedSub + " device: " + updatedDevice + " regions: " + updatedRegions + '\n'); + + } + catch(SQLException e) + { + util.log("ERROR: unable to retrieve subscription information" + e.getLocalizedMessage()); + results[0] += "ERROR: unable to retrieve subscription information: "; + results[0] += "code: " + e.getErrorCode() + " msg: "; + results[0] += e.getLocalizedMessage(); + throw new SQLException (results[0]); + } + + try + { + // Calculate new values for subscription + if (isSub) + { + String sub = valueIn.trim(); + if (sub.equals("1")) + if (isAdd) + updatedSub = updatedSub |= 1; + else + updatedSub = updatedSub &= ~1; + else if (sub.equals("2")) + if (isAdd) + updatedSub = updatedSub |= 2; + else + updatedSub = updatedSub &= ~2; + else if (sub.equals("3")) + if (isAdd) + updatedSub = updatedSub |= 4; + else + updatedSub = updatedSub &= ~4; + else + { + results[0] += "ERROR: invalid subscription value"; + return; + } + util.log("New subscription type: " + updatedSub + '\n'); + } + else if (isRegion) + { + String rangeValues = "ABCDEFGHI-"; + + // Calculate new values for regions + char[] tmpRegions = updatedRegions.toCharArray(); + String inRegions = valueIn.trim(); + for (int i = 0; i < inRegions.length(); i++) + { + char in = valueIn.charAt(i); + int ndx = rangeValues.indexOf(in); + if (ndx == -1) + { + results[0] += "ERROR: invalid range value specified: " + in + '\n'; + return; + } + if (isAdd) + tmpRegions[ndx] = in; + else + tmpRegions[ndx] = '-'; + + } + updatedRegions = String.valueOf(tmpRegions); + util.log("New regions: " + updatedRegions + '\n'); + } + + String updateQuery = "update subscribers.subscribers set subscription_package = "; + updateQuery += updatedSub; + updateQuery += ", zones_available = '"; + updateQuery += updatedRegions; + updateQuery += "' where upper(subscriber_user) = '"; + updateQuery += userName; + updateQuery += "';"; + + //results[0] += "updateQuery: " + updateQuery + '\n'; + util.log("update subscriber: " + updateQuery); + PreparedStatement updateStmt = null; + updateStmt = conn.prepareStatement (updateQuery); + updateStmt.executeUpdate(); + } + + catch(SQLException e) + { + util.log("ERROR: unable to update subscriptions " + e.getLocalizedMessage()); + results[0] += "ERROR: unable to update subscriptions - "; + results[0] += e.getLocalizedMessage(); + throw new SQLException (results[0]); + } + + results[0] += "Operation successful"; + util.log ("***** Completed subscriptions request *****"); + } +} + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fab46b1/core/sql/sqlcomp/CmpSeabaseDDLroutine.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLroutine.cpp b/core/sql/sqlcomp/CmpSeabaseDDLroutine.cpp index e2e3105..5f29264 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLroutine.cpp +++ b/core/sql/sqlcomp/CmpSeabaseDDLroutine.cpp @@ -227,8 +227,8 @@ short CmpSeabaseDDL::getUsingRoutines(ExeCliInterface *cliInterface, Lng32 cliRC = 0; char buf[4000]; - str_sprintf(buf, "select trim(catalog_name) || '.' || trim(schema_name) || '.' || trim(object_name), object_type " - "from %s.\"%s\".%s T, %s.\"%s\".%s LU " + str_sprintf(buf, "select trim(catalog_name) || '.' || trim(schema_name) || '.' || trim(object_name), " + "object_type, object_uid from %s.\"%s\".%s T, %s.\"%s\".%s LU " "where LU.using_library_uid = %Ld and " "T.object_uid = LU.used_udr_uid and T.valid_def = 'Y' ", getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_OBJECTS, @@ -429,6 +429,8 @@ void CmpSeabaseDDL::dropSeabaseLibrary(StmtDDLDropLibrary * dropLibraryNode, Lng32 cliRC = 0; Lng32 retcode = 0; + BindWA bindWA(ActiveSchemaDB(), CmpCommon::context(), FALSE/*inDDL*/); + NARoutineDB *pRoutineDBCache = ActiveSchemaDB()->getNARoutineDB(); const NAString &objName = dropLibraryNode->getLibraryName(); ComObjectName libraryName(objName); @@ -512,9 +514,9 @@ void CmpSeabaseDDL::dropSeabaseLibrary(StmtDDLDropLibrary * dropLibraryNode, return; } + usingRoutinesQueue->position(); for (size_t i = 0; i < usingRoutinesQueue->numEntries(); i++) { - usingRoutinesQueue->position(); OutputInfo * rou = (OutputInfo*)usingRoutinesQueue->getNext(); char * routineName = rou->get(0); @@ -529,6 +531,21 @@ void CmpSeabaseDDL::dropSeabaseLibrary(StmtDDLDropLibrary * dropLibraryNode, processReturn(); return; } + + // Remove routine from DBRoutinCache + ComObjectName objectName(routineName); + QualifiedName qualRoutineName(objectName, STMTHEAP); + NARoutineDBKey key(qualRoutineName, STMTHEAP); + NARoutine *cachedNARoutine = pRoutineDBCache->get(&bindWA, &key); + + if (cachedNARoutine) + { + Int64 routineUID = *(Int64*)rou->get(2); + pRoutineDBCache->removeNARoutine(qualRoutineName, + ComQiScope::REMOVE_FROM_ALL_USERS, + routineUID); + } + } // can get a slight perf. gain if we pass in objUID
