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