src/ports/postgres/modules/assoc_rules/test/assoc_rules.ic.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.
 *
 *//* ----------------------------------------------------------------------- */

---------------------------------------------------------------------------
-- Rules:
-- ------
-- 1) Any DB objects should be created w/o schema prefix,
--    since this file is executed in a separate schema context.
-- 2) There should be no DROP statements in this script, since
--    all objects created in the default schema will be cleaned-up outside.
---------------------------------------------------------------------------

---------------------------------------------------------------------------
-- Setup:
---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION assoc_array_eq
    (
    arr1 TEXT[],
    arr2 TEXT[]
    )
RETURNS BOOL AS $$
    SELECT COUNT(*) = array_upper($1, 1) AND array_upper($1, 1) = array_upper($2, 1)
    FROM (SELECT unnest($1) id) t1, (SELECT unnest($2) id) t2
    WHERE t1.id = t2.id;

$$ LANGUAGE sql IMMUTABLE;


CREATE OR REPLACE FUNCTION install_test() RETURNS VOID AS $$
declare
    result1        TEXT;
    result2        TEXT;
    result3        TEXT;
    result_maxiter TEXT;
    res            MADLIB_SCHEMA.assoc_rules_results;
    output_schema  TEXT;
    output_table   TEXT;
    total_rules    INT;
    total_time     INTERVAL;
begin
    DROP TABLE IF EXISTS test_data1;
    CREATE TABLE test_data1 (
        trans_id INT
        , product INT
    );

    DROP TABLE IF EXISTS test_data2;
    CREATE TABLE test_data2 (
        trans_id INT
        , product VARCHAR
    );


    INSERT INTO test_data1 VALUES (1,1);
    INSERT INTO test_data1 VALUES (1,2);
    INSERT INTO test_data1 VALUES (3,3);
    INSERT INTO test_data1 VALUES (8,4);
    INSERT INTO test_data1 VALUES (10,1);
    INSERT INTO test_data1 VALUES (10,2);
    INSERT INTO test_data1 VALUES (10,3);
    INSERT INTO test_data1 VALUES (19,2);

    INSERT INTO test_data2 VALUES (1, 'beer');
    INSERT INTO test_data2 VALUES (1, 'diapers');
    INSERT INTO test_data2 VALUES (1, 'chips');
    INSERT INTO test_data2 VALUES (2, 'beer');
    INSERT INTO test_data2 VALUES (2, 'diapers');
    INSERT INTO test_data2 VALUES (3, 'beer');
    INSERT INTO test_data2 VALUES (3, 'diapers');
    INSERT INTO test_data2 VALUES (4, 'beer');
    INSERT INTO test_data2 VALUES (4, 'chips');
    INSERT INTO test_data2 VALUES (5, 'beer');
    INSERT INTO test_data2 VALUES (6, 'beer');
    INSERT INTO test_data2 VALUES (6, 'diapers');
    INSERT INTO test_data2 VALUES (6, 'chips');
    INSERT INTO test_data2 VALUES (7, 'beer');
    INSERT INTO test_data2 VALUES (7, 'diapers');

    DROP TABLE IF EXISTS test1_exp_result;
    CREATE TABLE test1_exp_result (
        ruleid integer,
        pre text[],
        post text[],
        support double precision,
        confidence double precision,
        lift double precision,
        conviction double precision
    ) ;

    DROP TABLE IF EXISTS test2_exp_result;
    CREATE TABLE test2_exp_result (
        ruleid integer,
        pre text[],
        post text[],
        support double precision,
        confidence double precision,
        lift double precision,
        conviction double precision
    ) ;


    INSERT INTO test1_exp_result VALUES (7, '{3}', '{1}', 0.20000000000000001, 0.5, 1.2499999999999998, 1.2);
    INSERT INTO test1_exp_result VALUES (4, '{2}', '{1}', 0.40000000000000002, 0.66666666666666674, 1.6666666666666667, 1.8000000000000003);
    INSERT INTO test1_exp_result VALUES (1, '{1}', '{2,3}', 0.20000000000000001, 0.5, 2.4999999999999996, 1.6000000000000001);
    INSERT INTO test1_exp_result VALUES (9, '{2,3}', '{1}', 0.20000000000000001, 1, 2.4999999999999996, 0);
    INSERT INTO test1_exp_result VALUES (6, '{1,2}', '{3}', 0.20000000000000001, 0.5, 1.2499999999999998, 1.2);
    INSERT INTO test1_exp_result VALUES (8, '{3}', '{2}', 0.20000000000000001, 0.5, 0.83333333333333337, 0.80000000000000004);
    INSERT INTO test1_exp_result VALUES (5, '{1}', '{2}', 0.40000000000000002, 1, 1.6666666666666667, 0);
    INSERT INTO test1_exp_result VALUES (2, '{3}', '{2,1}', 0.20000000000000001, 0.5, 1.2499999999999998, 1.2);
    INSERT INTO test1_exp_result VALUES (10, '{3,1}', '{2}', 0.20000000000000001, 1, 1.6666666666666667, 0);
    INSERT INTO test1_exp_result VALUES (3, '{1}', '{3}', 0.20000000000000001, 0.5, 1.2499999999999998, 1.2);

    INSERT INTO test2_exp_result VALUES (7, '{chips,diapers}', '{beer}', 0.2857142857142857, 1, 1, 0);
    INSERT INTO test2_exp_result VALUES (2, '{chips}', '{diapers}', 0.2857142857142857, 0.66666666666666663, 0.93333333333333324, 0.85714285714285698);
    INSERT INTO test2_exp_result VALUES (1, '{chips}', '{diapers,beer}', 0.2857142857142857, 0.66666666666666663, 0.93333333333333324, 0.85714285714285698);
    INSERT INTO test2_exp_result VALUES (6, '{diapers}', '{beer}', 0.7142857142857143, 1, 1, 0);
    INSERT INTO test2_exp_result VALUES (4, '{beer}', '{diapers}', 0.7142857142857143, 0.7142857142857143, 1, 1);
    INSERT INTO test2_exp_result VALUES (3, '{chips,beer}', '{diapers}', 0.2857142857142857, 0.66666666666666663, 0.93333333333333324, 0.85714285714285698);
    INSERT INTO test2_exp_result VALUES (5, '{chips}', '{beer}', 0.42857142857142855, 1, 1, 0);

    res = MADLIB_SCHEMA.assoc_rules (.1, .5, 'trans_id', 'product', 'test_data1','madlib_installcheck_assoc_rules', false);

    RETURN;

end $$ language plpgsql;

---------------------------------------------------------------------------
-- Test
---------------------------------------------------------------------------
SELECT install_test();