TEST001

-- @@@ START COPYRIGHT @@@
--
-- (C) Copyright 2000-2014 Hewlett-Packard Development Company, L.P.
--
--  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.
--
-- @@@ END COPYRIGHT @@@

set pattern $$QUOTE$$ '''';

obey TEST001(clean_up);
log LOG001 clear;
sh rm -f LOG001-SECONDARY;
obey TEST001(compile_libs);
obey TEST001(create_tables);
obey TEST001(register_functions);
obey TEST001(tests);
log;
obey TEST001(clean_up);
exit;

?section clean_up
--------------------------------------------------------------------------
drop table clicks;
drop table_mapping function sessionize_static;
drop table_mapping function sessionize_dynamic;
drop table_mapping function sessionize_dynamic_shared;
drop table_mapping function sessionize_err;
drop table_mapping function fibonacci;
drop library TEST001;

?section compile_libs
--------------------------------------------------------------------------
log;
sh rm -f ./TEST001.dll;
sh sh $$scriptsdir$$/tools/dll-compile.ksh TEST001.cpp
  2>&1 | tee LOG001-SECONDARY;
set pattern $$DLL$$ TEST001.dll;
log LOG001;

?section create_tables
--------------------------------------------------------------------------
create table clicks (userid char(32), ts TIME(6), ipAddr char(15)) ;
insert into clicks values
 ('super-user',cast(time'09:59:59.50 pm' as TIME(6)),'12.345.567.345'),
 ('super-user',cast(time'11:59:59.50 pm' as TIME(6)),'12.345.567.345'),
 ('super-services',cast(time'11:59:59.50 pm' as TIME(6)),'12.345.567.345'),
 ('super-services',cast(time'11:59:59.55 pm' as TIME(6)),'12.345.567.345');

?section register_functions
--------------------------------------------------------------------------

create library TEST001 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$;

-- the static version of sessionize does not have a compiler interface,
-- its result columns are defined in the DDL
create table_mapping function sessionize_static(ts_colname char(10),
                                                user_colname char(10),
                                                timeintval int)
returns (session_id largeint,
         userid char(32),
         ts largeint)
external name 'SESSIONIZE_STATIC'
library TEST001;

-- the dynamic version of sessionize uses a compiler interface to
-- create result columns that match those of any input table given
-- and it validates the column name for session id at compile time
create table_mapping function sessionize_dynamic(colname char(10),
                                                 user_colname char(10),
                                                 timeintval int)
external name 'SESSIONIZE_DYNAMIC'
library TEST001;

-- This shows that we can share the same binaries for multiple
-- TMUDFs, note that it uses the same external name SESSIONIZE_DYNAMIC
create table_mapping function sessionize_dynamic_shared(colname char(10),
                                                        user_colname char(10),
                                                        timeintval int)
external name 'SESSIONIZE_DYNAMIC'
library TEST001;

-- negative test case, the entry point SESSIONIZE_ERR does not exist
create table_mapping function sessionize_err(dummy char(10))
returns (session_id largeint)
external name 'SESSIONIZE_NON_EXISTENT'
library TEST001;

-- Testing a TMUDF with no table-valued inputs
create table_mapping function fibonacci(start_row int, num_rows int)
returns (ordinal int, fibonacci_number largeint)
external name 'fibonacci'
library TEST001;

?section tests
--------------------------------------------------------------------------
--cqd attempt_esp_parallelism 'off' ;

get table_mapping functions for library TEST001 ;

SELECT cast(CONVERTTIMESTAMP(ts) as TIME(6)), userid, session_id
FROM UDF(sessionize_static(TABLE(SELECT userid, JULIANTIMESTAMP(ts) as TS
                                 FROM clicks
                                 PARTITION BY userid ORDER BY ts),
                           cast('TS' as char(2)),
                           'USERID',
                           60000000)) XO(session_id,userid,ts);

SELECT cast(CONVERTTIMESTAMP(ts) as TIME(6)), userid, session_id, ipAddr
FROM UDF(sessionize_dynamic(TABLE(SELECT userid,
                                         JULIANTIMESTAMP(ts) as TS,
                                         ipAddr
                                  FROM clicks
                                  PARTITION BY userid ORDER BY ts),
                            cast('TS' as char(2)),
                            'USERID',
                            60000000)) XO;

-- meant to call sessionize_dynamic_shared, but sharing DLLs
-- is not yet working, getting an error so far
SELECT *
FROM UDF(sessionize_dynamic(TABLE(SELECT userid,
                                         JULIANTIMESTAMP(ts) as TS,
                                         ipAddr
                                  FROM clicks
                                  PARTITION BY userid ORDER BY ts),
                            cast('TS' as char(2)),
                            'USERID',
                            60000000)) XO;

cqd ATTEMPT_ESP_PARALLELISM 'off';

select *
from UDF(fibonacci(1,10)) XO
order by 1;

select sum(fibonacci_number)
from UDF(fibonacci(50,10)) XO;

-- negative tests
select * from udf(sessionize_err('abc')) XOX(a);
-- expect errors 11246 and 11248

-- these should be caught by the compiler interface
select * from udf(sessionize_dynamic('TS')) XOX;
-- too few input parameters and not a string
select * from udf(sessionize_dynamic(1,2,3)) XOX;
-- not a string parameter
select * from udf(sessionize_dynamic(TABLE(SELECT userid, JULIANTIMESTAMP(ts) as TS
                          FROM clicks
                          PARTITION BY userid ORDER BY ts),
TABLE(SELECT userid, JULIANTIMESTAMP(ts) as TS
                          FROM clicks
                          PARTITION BY userid ORDER BY ts),
cast('TS' as char(2)),
                    'USERID',
                    60000000)) XOX;
-- too many table-valued arguments (syntax error for now)
SELECT cast(CONVERTTIMESTAMP(ts) as TIME(6)), userid, session_id
FROM UDF(sessionize_dynamic(TABLE(SELECT userid, JULIANTIMESTAMP(ts) as TS
                                  FROM clicks
                                  PARTITION BY userid ORDER BY ts),
                            'NONEXISTENTCOL',
                            'USERID',
                            60000000)) XOX;
-- non-existent column specified in input parameter