| /* ----------------------------------------------------------------------- |
| * |
| * 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. |
| * |
| * ----------------------------------------------------------------------- */ |
| |
| /* ----------------------------------------------------------------------------- |
| * Test load custom function helper functions |
| * -------------------------------------------------------------------------- */ |
| |
| m4_include(`SQLCommon.m4') |
| |
| \i m4_regexp(MODULE_PATHNAME, |
| `\(.*\)libmadlib\.so', |
| `\1../../modules/deep_learning/test/madlib_keras_custom_function.setup.sql_in' |
| ) |
| |
| /* Test successful table creation where no table exists */ |
| DROP TABLE IF EXISTS __test_custom_function_table__; |
| SELECT load_custom_function('__test_custom_function_table__', custom_function_object(), 'sum_fn', 'returns sum'); |
| |
| SELECT assert(UPPER(atttypid::regtype::TEXT) = 'INTEGER', 'id column should be INTEGER type') |
| FROM pg_attribute WHERE attrelid = '__test_custom_function_table__'::regclass |
| AND attname = 'id'; |
| SELECT assert(UPPER(atttypid::regtype::TEXT) = 'BYTEA', 'object column should be BYTEA type' ) |
| FROM pg_attribute WHERE attrelid = '__test_custom_function_table__'::regclass |
| AND attname = 'object'; |
| SELECT assert(UPPER(atttypid::regtype::TEXT) = 'TEXT', |
| 'name column should be TEXT type') |
| FROM pg_attribute WHERE attrelid = '__test_custom_function_table__'::regclass |
| AND attname = 'name'; |
| SELECT assert(UPPER(atttypid::regtype::TEXT) = 'TEXT', |
| 'description column should be TEXT type') |
| FROM pg_attribute WHERE attrelid = '__test_custom_function_table__'::regclass |
| AND attname = 'description'; |
| |
| /* id should be 1 */ |
| SELECT assert(id = 1, 'Wrong id written by load_custom_function') |
| FROM __test_custom_function_table__; |
| |
| /* Validate function object created */ |
| SELECT assert(read_custom_function(object, 2, 3) = 5, 'Custom function should return sum of args.') |
| FROM __test_custom_function_table__; |
| |
| /* Test custom function insertion where valid table exists */ |
| SELECT load_custom_function('__test_custom_function_table__', custom_function_object(), 'sum_fn1'); |
| SELECT assert(name = 'sum_fn', 'Custom function sum_fn found in table.') |
| FROM __test_custom_function_table__ WHERE id = 1; |
| SELECT assert(name = 'sum_fn1', 'Custom function sum_fn1 found in table.') |
| FROM __test_custom_function_table__ WHERE id = 2; |
| |
| /* Test adding an existing function name should error out */ |
| SELECT assert(MADLIB_SCHEMA.trap_error($TRAP$ |
| SELECT load_custom_function('__test_custom_function_table__', custom_function_object(), 'sum_fn1'); |
| $TRAP$) = 1, 'Should error out for duplicate function name'); |
| |
| /* Test deletion by id where valid table exists */ |
| /* Assert id exists before deleting */ |
| SELECT assert(COUNT(id) = 1, 'id 2 should exist before deletion!') |
| FROM __test_custom_function_table__ WHERE id = 2; |
| SELECT delete_custom_function('__test_custom_function_table__', 2); |
| SELECT assert(COUNT(id) = 0, 'id 2 should have been deleted!') |
| FROM __test_custom_function_table__ WHERE id = 2; |
| |
| /* Test deletion by name where valid table exists */ |
| SELECT load_custom_function('__test_custom_function_table__', custom_function_object(), 'sum_fn1'); |
| /* Assert id exists before deleting */ |
| SELECT assert(COUNT(id) = 1, 'function name sum_fn1 should exist before deletion!') |
| FROM __test_custom_function_table__ WHERE name = 'sum_fn1'; |
| SELECT delete_custom_function('__test_custom_function_table__', 'sum_fn1'); |
| SELECT assert(COUNT(id) = 0, 'function name sum_fn1 should have been deleted!') |
| FROM __test_custom_function_table__ WHERE name = 'sum_fn1'; |
| |
| /* Test deleting an already deleted entry should error out */ |
| SELECT assert(MADLIB_SCHEMA.trap_error($TRAP$ |
| SELECT delete_custom_function('__test_custom_function_table__', 2); |
| $TRAP$) = 1, 'Should error out for trying to delete an entry that does not exist'); |
| |
| /* Test delete drops the table after deleting last entry*/ |
| DROP TABLE IF EXISTS __test_custom_function_table__; |
| SELECT load_custom_function('__test_custom_function_table__', custom_function_object(), 'sum_fn', 'returns sum'); |
| SELECT delete_custom_function('__test_custom_function_table__', 1); |
| SELECT assert(COUNT(relname) = 0, 'Table __test_custom_function_table__ should have been deleted.') |
| FROM pg_class where relname='__test_custom_function_table__'; |
| |
| /* Test deletion where empty table exists */ |
| SELECT load_custom_function('__test_custom_function_table__', custom_function_object(), 'sum_fn', 'returns sum'); |
| DELETE FROM __test_custom_function_table__; |
| SELECT assert(MADLIB_SCHEMA.trap_error($$SELECT delete_custom_function('__test_custom_function_table__', 1)$$) = 1, |
| 'Deleting function in an empty table should generate an exception.'); |
| |
| /* Test deletion where no table exists */ |
| DROP TABLE IF EXISTS __test_custom_function_table__; |
| SELECT assert(MADLIB_SCHEMA.trap_error($$SELECT delete_custom_function('__test_custom_function_table__', 1)$$) = 1, |
| 'Deleting a non-existent table should raise exception.'); |
| |
| /* Test where invalid table exists */ |
| SELECT load_custom_function('__test_custom_function_table__', custom_function_object(), 'sum_fn', 'returns sum'); |
| ALTER TABLE __test_custom_function_table__ DROP COLUMN id; |
| SELECT assert(MADLIB_SCHEMA.trap_error($$SELECT delete_custom_function('__test_custom_function_table__', 2)$$) = 1, |
| 'Deleting an invalid table should generate an exception.'); |
| |
| SELECT assert(MADLIB_SCHEMA.trap_error($$SELECT load_custom_function('__test_custom_function_table__', custom_function_object(), 'sum_fn', 'returns sum')$$) = 1, |
| 'Passing an invalid table to load_custom_function() should raise exception.'); |
| |
| /* Test input validation */ |
| DROP TABLE IF EXISTS __test_custom_function_table__; |
| SELECT assert(MADLIB_SCHEMA.trap_error($$ |
| SELECT load_custom_function('__test_custom_function_table__', custom_function_object(), NULL, NULL); |
| $$) = 1, 'Name cannot be NULL'); |
| SELECT assert(MADLIB_SCHEMA.trap_error($$ |
| SELECT load_custom_function('__test_custom_function_table__', NULL, 'sum_fn', NULL); |
| $$) = 1, 'Function object cannot be NULL'); |
| SELECT assert(MADLIB_SCHEMA.trap_error($$ |
| SELECT load_custom_function('__test_custom_function_table__', 'invalid_obj'::bytea, 'sum_fn', NULL); |
| $$) = 1, 'Invalid custom function object'); |
| SELECT load_custom_function('__test_custom_function_table__', custom_function_object(), 'sum_fn', NULL); |
| SELECT assert(name IS NOT NULL AND description IS NULL, 'validate name is not NULL.') |
| FROM __test_custom_function_table__; |
| SELECT assert(MADLIB_SCHEMA.trap_error($$ |
| SELECT delete_custom_function('__test_custom_function_table__', NULL); |
| $$) = 1, 'id/name cannot be NULL!'); |
| |
| /* Test top n accuracy */ |
| |
| DROP TABLE IF EXISTS __test_custom_function_table__; |
| SELECT load_top_k_accuracy_function('__test_custom_function_table__', 3); |
| SELECT load_top_k_accuracy_function('__test_custom_function_table__', 7); |
| SELECT load_top_k_accuracy_function('__test_custom_function_table__', 4); |
| SELECT load_top_k_accuracy_function('__test_custom_function_table__', 8); |
| |
| SELECT assert(count(*) = 4, 'Table __test_custom_function_table__ should have 4 entries') |
| FROM __test_custom_function_table__; |
| |
| SELECT assert(name = 'top_3_accuracy', 'Top 3 accuracy name is incorrect') |
| FROM __test_custom_function_table__ WHERE id = 1; |
| |
| SELECT assert(name = 'top_7_accuracy', 'Top 7 accuracy name is incorrect') |
| FROM __test_custom_function_table__ WHERE id = 2; |
| |
| SELECT assert(name = 'top_4_accuracy', 'Top 4 accuracy name is incorrect') |
| FROM __test_custom_function_table__ WHERE id = 3; |
| |
| SELECT assert(name = 'top_8_accuracy', 'Top 8 accuracy name is incorrect') |
| FROM __test_custom_function_table__ WHERE id = 4; |
| |
| CREATE SCHEMA MADLIB_SCHEMA_aaa; |
| CREATE TABLE pg_temp.temp1 AS SELECT * FROM MADLIB_SCHEMA.__test_custom_function_table__; |
| CREATE TABLE pg_temp.MADLIB_SCHEMA AS SELECT * FROM MADLIB_SCHEMA.__test_custom_function_table__; |
| CREATE TABLE MADLIB_SCHEMA_aaa.test_table AS SELECT * FROM MADLIB_SCHEMA.__test_custom_function_table__; |
| |
| SELECT assert(MADLIB_SCHEMA.trap_error($$ |
| SELECT load_custom_function('pg_temp.temp1', custom_function_object(), 'sum_fn', 'returns sum'); |
| $$) = 1, 'Cannot use non-madlib schemas'); |
| |
| SELECT assert(MADLIB_SCHEMA.trap_error($$ |
| SELECT load_custom_function('__test_custom_function_table__ UNION pg_temp.temp1', |
| custom_function_object(), 'sum_fn', 'returns sum'); |
| $$) = 1, 'UNION should not pass'); |
| |
| SELECT assert(MADLIB_SCHEMA.trap_error($$ |
| SELECT load_custom_function('pg_temp.MADLIB_SCHEMA', custom_function_object(), 'sum_fn', 'returns sum'); |
| $$) = 1, 'pg_temp.MADLIB_SCHEMA should not pass'); |
| |
| SELECT assert(MADLIB_SCHEMA.trap_error($$ |
| SELECT load_custom_function('MADLIB_SCHEMA_aaa.test_table', custom_function_object(), 'sum_fn', 'returns sum'); |
| $$) = 1, 'test_schema.MADLIB_SCHEMA should not pass'); |
| |
| DROP SCHEMA MADLIB_SCHEMA_aaa CASCADE; |
| DROP TABLE IF EXISTS pg_temp.temp1; |
| DROP TABLE IF EXISTS pg_temp.MADLIB_SCHEMA; |