src/ports/postgres/modules/deep_learning/test/madlib_keras_custom_function.sql_in

/* -----------------------------------------------------------------------
 *
 * 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*/
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!');