core/sql/regress/udr/TEST103

-- @@@ 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 associated 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',
  'great_expectations@ge.com', 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 ;

create function nonDeterministicRandom
  () returns (r integer)
  language c parameter style sql external name 'nonDeterministicRandom'
  library functionsForTest103
  not deterministic no sql final call allow any parallelism state area size 1024 ;

cqd nested_joins 'off';
cqd merge_joins 'off';
cqd join_order_by_user 'on';
prepare s from
select a, nonDeterministicRandom() r1,
       generateRandomNumber(a, 4) r2, generateRandomNumber(123, 4) r3
from (values (1), (2), (3)) T(a);
explain options 'f' s;
-- r1: Nested join, no probe cache, because it's non-deterministic
-- r2: Nested join, probe cache, because it refers to the outer table
-- r3: Hash join, because it's deterministic, no refs to outer
execute s;
cqd nested_joins reset;
cqd merge_joins reset;
cqd join_order_by_user reset;

-- 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
  ;