dt_preproc.sql_in

Go to the documentation of this file.

/* ----------------------------------------------------------------------- *//** 
 *
 * @file dt_preproc.sql_in
 *
 * @brief Functions used in C4.5 and random forest for data preprocessing.
 *
 * @create    April 5, 2012
 * @modified  July 19, 2012
 *
 *//* ----------------------------------------------------------------------- */


m4_include(`SQLCommon.m4')

/* Own macro definitions */
m4_ifelse(
    m4_eval(
        m4_ifdef(`__GREENPLUM__', 1, 0) &&
        __DBMS_VERSION_MAJOR__ * 100 + __DBMS_VERSION_MINOR__ < 401
    ), 1,
    `m4_define(`__GREENPLUM_PRE_4_1__')'
)
m4_ifelse(
    m4_eval(
        m4_ifdef(`__POSTGRESQL__', 1, 0) &&
        __DBMS_VERSION_MAJOR__ < 9
    ), 1,
    `m4_define(`__POSTGRESQL_PRE_9_0__')'
)
m4_ifelse(
    m4_eval(
        m4_ifdef(`__GREENPLUM__', 1, 0) &&
        __DBMS_VERSION_MAJOR__ * 10000 +
            __DBMS_VERSION_MINOR__ * 100 +
            __DBMS_VERSION_PATCH__ >= 40201
    ), 1,
    `m4_define(`__GREENPLUM_GE_4_2_1__')'
)

/*
 * The file contains the functions to encode a training/classification table for 
 * C4.5 and random forest (RF). Given a training table, we encode it into 4 tables: 
 *     + A table that contains the distinct values and their assigned IDs for all
 *       features. We call it the Key-Value(KV) table for features.
 *     + A table that contains the distinct labels and their assigned IDs for the 
 *       class column. We call it the KV table for class.
 *     + A table that contains metadata descriptions about the columns of the training
 *       table. We call it the metatable.
 *     + A table that contains an encoded version of the training table using the 
 *       KV tables. We call it the encoded table.
 *
 * For a classification table, we only need the first three tables. We will use
 * Golf dataset as an example to illustrate the generated tables:
 *
 * testdb=# select * from golf order by id;
 *  id | outlook  | temperature | humidity | windy  |    class     
 * ----+----------+-------------+----------+--------+--------------
 *   1 | sunny    |          85 |       85 |  false |  Do not Play
 *   2 | sunny    |          80 |       90 |  true  |  Do not Play
 *   3 | overcast |          83 |       78 |  false |  Play
 *   4 | rain     |          70 |       96 |  false |  Play
 *   5 | rain     |          68 |       80 |  false |  Play
 *   6 | rain     |          65 |       70 |  true  |  Do not Play
 *   7 | overcast |          64 |       65 |  true  |  Play
 *   8 | sunny    |          72 |       95 |  false |  Do not Play
 *   9 | sunny    |          69 |       70 |  false |  Play
 *  10 | rain     |          75 |       80 |  false |  Play
 *  11 | sunny    |          75 |       70 |  true  |  Play
 *  12 | overcast |          72 |       90 |  true  |  Play
 *  13 | overcast |          81 |       75 |  false |  Play
 *  14 | rain     |          71 |       80 |  true  |  Do not Play
 * (14 rows)
 *
 *
 * The metatable contains the information of the columns in the training table.
 * For each column, it has a record whose structure is defined as:
 *
 *   +id             The ID assigned to a feature/class/id column. For the class 
 *                   colum,it's 0. To be determistic, the IDs for feature columns
 *                   starts at 1 and are assigned according to the alphabet order 
 *                   of the column names. The ID for the id column is the largest
 *                   feature ID plus one. 
 *   +column_name    The name of the class/feature/id column.
 *   +column_type    'c' means the column is a class. 
 *                   'f' means it's a feature column.
 *                   'i' means it's an id column.
 *   +is_cont        't' means the feature is continuous.
 *                   'f' means it's discrete.
 *   +table_oid      The OID of the KV table for features/class. 
 *                   For the id column, there is no KV table.
 *   +num_dist_value The number of distinct values for a feature/class column. 
 *
 * The metatable for the Golf dataset looks like this:
 * testdb=# select * from golf_meta order by id;
 *  id | column_name | column_type | is_cont | table_oid | num_dist_value 
 * ----+-------------+-------------+---------+-----------+----------------
 *   0 | class       | c           | f       |    787672 |              2
 *   1 | humidity    | f           | t       |    787749 |              9
 *   2 | outlook     | f           | f       |    787749 |              3
 *   3 | temperature | f           | t       |    787749 |             12
 *   4 | windy       | f           | f       |    787749 |              2
 *   5 | id          | i           | f       |           |              
 * (6 rows)
 * 
 * The KV table for features contains a record for each distinct value. The record
 * structure is:
 *   +fid      The ID assigned to a feature.
 *   +fval     For a discrete feature, it's the distinct value. 
 *             For a continuous feature, it's NULL.
 *   +code     For a discrete feature, it's the assigned key. 
 *             For a continuous feature, it's the average value.
 *
 * testdb=# select * from golf_kv_features order by fid, code;
 *  fid |   fval   |       code       
 * -----+----------+------------------
 *    1 |          | 80.2857142857143
 *    2 | overcast |                1
 *    2 | rain     |                2
 *    2 | sunny    |                3
 *    3 |          | 73.5714285714286
 *    4 | false    |                1
 *    4 | true     |                2
 * (7 rows)
 *
 * The KV table for class labels contains a record for each label. The record
 * structure is the same as the KV table for features.
 * testdb=# select * from golf_kv_class order by fid, code;
 *  fid |     fval     | code 
 * -----+--------------+------
 *    0 |  Do not Play |    1
 *    0 |  Play        |    2
 * 
 * The encoded table has a record for each cell in the training table. The record
 * structure is:
 *    +id       The ID from the training table. 
 *    +fid      The ID assigned to a feature
 *    +fval     For a discrete feature, it's the key. 
 *              For a continuous feature, it's the original feature value.
 *    +is_cont  't' if the feature is continuous, or 'f' for the discrete one.
 *    +class    The encoded value of the class label.
 * 
 * For Golf dataset, the vertical encoded table looks like this:
 * testdb=# select * from golf_ed order by fid, id;
 *  id | fid | fval | is_cont | class 
 * ----+-----+------+---------+-------
 *   1 |   1 |   85 | t       |     1
 *   2 |   1 |   90 | t       |     1
 *   3 |   1 |   78 | t       |     2
 *   4 |   1 |   96 | t       |     2
 *   5 |   1 |   80 | t       |     2
 *   6 |   1 |   70 | t       |     1
 *   7 |   1 |   65 | t       |     2
 *   8 |   1 |   95 | t       |     1
 *   9 |   1 |   70 | t       |     2
 *  10 |   1 |   80 | t       |     2
 *  11 |   1 |   70 | t       |     2
 *  12 |   1 |   90 | t       |     2
 *  13 |   1 |   75 | t       |     2
 *  14 |   1 |   80 | t       |     1
 *   1 |   2 |    3 | f       |     1
 *   2 |   2 |    3 | f       |     1
 *   3 |   2 |    1 | f       |     2
 *   4 |   2 |    2 | f       |     2
 *   5 |   2 |    2 | f       |     2
 *   6 |   2 |    2 | f       |     1
 *   7 |   2 |    1 | f       |     2
 *   8 |   2 |    3 | f       |     1
 *   9 |   2 |    3 | f       |     2
 *  10 |   2 |    2 | f       |     2
 *  11 |   2 |    3 | f       |     2
 *  12 |   2 |    1 | f       |     2
 *  13 |   2 |    1 | f       |     2
 *  14 |   2 |    2 | f       |     1
 *   1 |   3 |   85 | t       |     1
 *   2 |   3 |   80 | t       |     1
 *   3 |   3 |   83 | t       |     2
 *   4 |   3 |   70 | t       |     2
 *   5 |   3 |   68 | t       |     2
 *   6 |   3 |   65 | t       |     1
 *   7 |   3 |   64 | t       |     2
 *   8 |   3 |   72 | t       |     1
 *   9 |   3 |   69 | t       |     2
 *  10 |   3 |   75 | t       |     2
 *  11 |   3 |   75 | t       |     2
 *  12 |   3 |   72 | t       |     2
 *  13 |   3 |   81 | t       |     2
 *  14 |   3 |   71 | t       |     1
 *   1 |   4 |    1 | f       |     1
 *   2 |   4 |    2 | f       |     1
 *   3 |   4 |    1 | f       |     2
 *   4 |   4 |    1 | f       |     2
 *   5 |   4 |    1 | f       |     2
 *   6 |   4 |    2 | f       |     1
 *   7 |   4 |    2 | f       |     2
 *   8 |   4 |    1 | f       |     1
 *   9 |   4 |    1 | f       |     2
 *  10 |   4 |    1 | f       |     2
 *  11 |   4 |    2 | f       |     2
 *  12 |   4 |    2 | f       |     2
 *  13 |   4 |    1 | f       |     2
 *  14 |   4 |    2 | f       |     1
 * (56 rows)
 *
 * On databases that support compression, we can leverage that feature
 * to reduce the space required for keeping the encoded table.
 *
 * For classification, we will use the metatable and KV tables to encode 
 * the table (horizontal table) to be classified into some like this: 
 *
 * testdb# select * from golf_ed order by id;
 *  id |    fvals    | class 
 * ----+-------------+-------
 *   1 | {85,3,85,1} |     1
 *   2 | {90,3,80,2} |     1
 *   3 | {78,1,83,1} |     2
 *   4 | {96,2,70,1} |     2
 *   5 | {80,2,68,1} |     2
 *   6 | {70,2,65,2} |     1
 *   7 | {65,1,64,2} |     2
 *   8 | {95,3,72,1} |     1
 *   9 | {70,3,69,1} |     2
 *  10 | {80,2,75,1} |     2
 *  11 | {70,3,75,2} |     2
 *  12 | {90,1,72,2} |     2
 *  13 | {75,1,81,1} |     2
 *  14 | {80,2,71,2} |     1
 * (14 rows)
 *
 * In general, each record in the new encoded table has the following structure:
 *    +id     The ID from the classification table.
 *    +fvals  An array contains all the features' values for a given ID.
 *            For a discrete feature, the element in the array is the key.
 *            For a continuous feature, it's the original value.
 *    +class  The encoded value of a class label.
 *
 */


/*
 * The UDT for keeping the time for each step of the encoding procedure.
 *
 *      pre_proc_time            The time of pre-processing.
 *      breakup_tbl_time         The time of breaking up the training table.
 *      gen_kv_time              The time of generating KV-table for 
 *                               features/class. 
 *      gen_enc_time             The time of generating encoded table.
 *      post_proc_time           The time of post-processing.
 *
 */
DROP TYPE IF EXISTS MADLIB_SCHEMA.__enc_tbl_result;
CREATE TYPE MADLIB_SCHEMA.__enc_tbl_result AS 
(     
    pre_proc_time            INTERVAL,
    breakup_tbl_time         INTERVAL,
    gen_kv_time              INTERVAL,
    gen_enc_time             INTERVAL,
    post_proc_time           INTERVAL
);


/*
 * @brief Check if the input table has unsupported data type or not.
 *        Check if the id column of input table has duplicated value or not.
 *
 * @param full_table_name     The full table name.
 * @param feature_columns     The array including all feature names.
 * @param id_column           The name of the ID column.        
 * @param class_column        The name of the class column. 
 *
 * @return If the table has unsupported data types, then raise exception
 *         otherwise return nothing.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__validate_input_table    
    (
    full_table_name     TEXT,
    feature_columns     TEXT[],
    id_column           TEXT,
    class_column        TEXT
    )
RETURNS void AS $$  
DECLARE
    rec             RECORD;
    stmt            TEXT;
    all_columns     TEXT := '';
    index           INT;
BEGIN
    -- find the first (LIMIT 1) unsupported data type if the input table has.
    stmt= 'SELECT atttypid 
    FROM pg_attribute 
    WHERE attrelid ='||quote_literal(full_table_name)||'::regclass  AND
          attnum > 0                                                AND 
          (not attisdropped)                                        AND
          atttypid NOT IN 
          (
              SELECT unnest
                    (
                        ARRAY[
                            ''SMALLINT''::regtype::oid,
                            ''INT''::regtype::oid, 
                            ''BIGINT''::regtype::oid, 
                            ''FLOAT8''::regtype::oid, 
                            ''REAL''::regtype::oid, 
                            ''DECIMAL''::regtype::oid,
                            ''INET''::regtype::oid, 
                            ''CIDR''::regtype::oid, 
                            ''MACADDR''::regtype::oid, 
                            ''BOOLEAN''::regtype::oid, 
                            ''CHAR''::regtype::oid, 
                            ''VARCHAR''::regtype::oid, 
                            ''TEXT''::regtype::oid, 
                            ''"char"''::regtype::oid, 
                            ''DATE''::regtype::oid, 
                            ''TIME''::regtype::oid,
                            ''TIMETZ''::regtype::oid, 
                            ''TIMESTAMP''::regtype::oid, 
                            ''TIMESTAMPTZ''::regtype::oid, 
                            ''INTERVAL''::regtype::oid
                        ]
                    )
          ) ';

    IF (feature_columns IS NOT NULL) THEN
        -- If user do not specify feature columns, we use all those columns.
        -- Otherwise, we just need to check those specified columns.
        index = array_lower(feature_columns, 1);
        WHILE (index <= array_upper(feature_columns, 1)) LOOP
            all_columns = all_columns                           || 
                          quote_literal(feature_columns[index]) || 
                          ',';
            index = index+1;
        END LOOP; 
        
        all_columns = all_columns || quote_literal(id_column) || ',';
        all_columns = all_columns || quote_literal(class_column);
        stmt = stmt ||' AND attname IN ('||all_columns||') ';
    END IF;
    
    stmt = stmt||' LIMIT 1;';
    
    EXECUTE stmt INTO rec;
            
    IF (rec IS NOT NULL) THEN
        -- Print the first unsupported data type, and supported types.
        RAISE EXCEPTION 'Unsupported data type [%]. Supported types include:
                         SMALLINT, INT, BIGINT, FLOAT8, REAL,
                         DECIMAL, INET, CIDR, MACADDR, BOOLEAN,
                         CHAR, VARCHAR, TEXT, "char", 
                         DATE, TIME, TIMETZ, TIMESTAMP, TIMESTAMPTZ, and INTERVAL', 
                         rec.atttypid::regtype;
    END IF;

    SELECT MADLIB_SCHEMA.__format
            ('SELECT % AS n
              FROM % 
              GROUP BY %
              HAVING COUNT(%) > 1
              LIMIT 1',
              ARRAY[
                id_column,
                full_table_name,
                id_column,
                id_column
                ]
            )
    INTO stmt;
    
    EXECUTE stmt INTO rec;
    
    -- check if the id column has duplicated value
    PERFORM MADLIB_SCHEMA.__assert
                (
                    rec IS NULL,
                    'The training table ' || full_table_name || ' must not have duplicated id'
                );
                        
    RETURN;
END
$$ LANGUAGE PLPGSQL;    


/*
 * @brief Get the class table name by the metatable name.
 *
 * @param meta_tbl_name    The full name of the metatable.
 *
 * @return The name of the class table
 *
 */  
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__get_classtable_name
    (
    meta_tbl_name TEXT 
    ) 
RETURNS TEXT AS $$
DECLARE
    classtable_name TEXT;
    curstmt         TEXT;
BEGIN

    PERFORM MADLIB_SCHEMA.__assert_table
        (
            meta_tbl_name,
            't'
        ); 
          
    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT MADLIB_SCHEMA.__regclass_to_text
                (table_oid) as table_name 
             FROM %
             WHERE column_type = ''c''',
            ARRAY[
                meta_tbl_name
            ]
        );
    
    EXECUTE curstmt INTO classtable_name;
    
    RETURN classtable_name;
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Drop the metatable and KV tables 
 *        for the features and the class.
 *
 * @param meta_tbl_name The full name of the metatable.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__drop_metatable 
    (
    meta_tbl_name TEXT
    )
RETURNS void AS $$
DECLARE
    curstmt TEXT;
    name    TEXT;
BEGIN
    IF (meta_tbl_name is NULL ) THEN
        RETURN;
    END IF;
    
    PERFORM MADLIB_SCHEMA.__assert_table(meta_tbl_name, 't');
    
    -- get the Key-Value tables
    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT MADLIB_SCHEMA.__regclass_to_text
                (table_oid) as table_name 
             FROM 
                (
                SELECT table_oid
                FROM %
                WHERE table_oid IS NOT NULL
                GROUP BY table_oid
                ) t',
             ARRAY[
                 meta_tbl_name
             ]
        );
    
    -- drop all the Key-Value tables
    FOR name IN EXECUTE curstmt LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || name || ' CASCADE;';
    END LOOP;
    
    -- drop the metatable
    EXECUTE 'DROP TABLE ' || meta_tbl_name || ' CASCADE;';
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Create the metatable.
 
 * @param meta_tbl_name    The full name of the metatable.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__create_metatable 
    (
    meta_tbl_name TEXT
    )
RETURNS void AS $$
DECLARE
    curstmt TEXT;
    result  INT := 0;
BEGIN
    -- the maximum length of an identifier is 63
    PERFORM MADLIB_SCHEMA.__assert
        (
            length(MADLIB_SCHEMA.__strip_schema_name(meta_tbl_name)) <= 63, 
            'The maximum length of '                            ||
             MADLIB_SCHEMA.__strip_schema_name(meta_tbl_name)  || 
             ' is 63'
        );
        
    -- must not be existence
    PERFORM MADLIB_SCHEMA.__assert_table(meta_tbl_name, 'f');
    
    -- 'f' for feature, 'c' for class, 'i' for id
    -- 't' for continuous value, 'f' for discrete value
    curstmt = MADLIB_SCHEMA.__format
        (
            'CREATE TABLE %(
             id             INT,
             column_name    TEXT,
             column_type    TEXT,
             is_cont        BOOL,
             table_oid      OID,
             num_dist_value INT
             ) m4_ifdef(`__GREENPLUM__', `DISTRIBUTED BY (id)')',
            meta_tbl_name
        );
    EXECUTE curstmt;
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Insert a record to the metatable
 *        A row in the metatable represents a column's information.
 *
 * @param meta_tbl_name     The full name of the metatable.
 * @param column_name       The name of the column.
 * @param column_type       The type of the column.
 *                          'i' means id, 'c' means class, 'f' means feature.
 * @param is_cont           True if the column is continuous.
 * @param table_name        The full name of key-value table for the column.
 *                          The OID of this table will be stored.
 * @param num_dist_value    The number of distinct values for the column.
 *
 * @note  The null value will be included in the distinct values.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__insert_into_metatable 
    (
    meta_tbl_name       TEXT,
    col_index           INT,
    column_name         TEXT,
    column_type         CHAR,   
    is_cont             BOOLEAN,     
    table_name          TEXT,
    num_dist_value      INT         
    )
RETURNS void AS $$
DECLARE
    curstmt TEXT := '';
    tbl_txt TEXT := 'NULL';     
BEGIN
    PERFORM MADLIB_SCHEMA.__assert
        (
            column_type = 'f' OR column_type = 'i' OR column_type = 'c',
            'column type must be ''f'', ''i'' or ''c'''
        );
    IF (table_name IS NOT NULL) THEN
        tbl_txt = '''' || table_name || '''';
    END IF;

    curstmt = MADLIB_SCHEMA.__format
        (
            'INSERT INTO % VALUES
                (%, ''%'', ''%'', ''%'', %::regclass, %);',
            ARRAY[
                meta_tbl_name, 
                col_index::TEXT,
                column_name, 
                column_type, 
                MADLIB_SCHEMA.__to_char(is_cont), 
                tbl_txt,
                num_dist_value::TEXT
            ]
        ); 

    EXECUTE curstmt;
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Validate if the metatable exists or not.
 *        Validate if the tables in "table_oid" column exists or not.
 *
 * @param meta_tbl_name     The full name of the metatable.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__validate_metatable 
    (
    meta_tbl_name TEXT
    )
RETURNS VOID AS $$
DECLARE
    curstmt TEXT;
    name    TEXT;
BEGIN
    PERFORM MADLIB_SCHEMA.__assert_table(meta_tbl_name, 't');
    
    -- if one of those KV tables doesn't exist, 
    -- we raise exception.
    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT MADLIB_SCHEMA.__assert_table
                (MADLIB_SCHEMA.__regclass_to_text(table_oid), ''t'') 
             FROM 
                (
                SELECT table_oid
                FROM %
                WHERE table_oid IS NOT NULL
                GROUP BY table_oid
                ) t',
             ARRAY[
                 meta_tbl_name
             ]
        );
    EXECUTE curstmt;
END
$$ LANGUAGE PLPGSQL;

/*
 * @brief Get the number of distinct values for the feature with given ID.
 *
 * @param meta_tbl_name     The full name of the metatable.
 * @param feature_id        The ID of the feature in the metatable.
 *
 * @return The number of the distinct values for the given feature.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__distinct_feature_value 
    (
    meta_tbl_name   TEXT,
    feature_id      INT
    )
RETURNS INT4 AS $$
DECLARE
    curstmt     TEXT := '';
    result      INT4 := 0;
BEGIN
    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT num_dist_value 
             FROM % 
             WHERE column_type=''f'' AND id = %',
            meta_tbl_name,
            feature_id::TEXT
        ); 
                
    EXECUTE curstmt INTO result;
    
    RETURN result;
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Get the number of features.
 *
 * @param meta_tbl_name    The full name of the metatable.
 *
 * @return The number of features in the training table.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__num_of_feature 
    (
    meta_tbl_name TEXT
    )
RETURNS INT4 AS $$
DECLARE
    curstmt TEXT := '';
    result INT4 := 0;
BEGIN
    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT COUNT(*) 
             FROM % 
             WHERE column_type=''f''',
            meta_tbl_name
        ); 
                
    EXECUTE curstmt INTO result;
    
    RETURN result;
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Get the number of distinct class values.
 *
 * @param meta_tbl_name    The full name of the metatable.
 *
 * @return The number of class labels in the training table.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__num_of_class
    (
    meta_tbl_name TEXT
    )
RETURNS INT4 AS $$
DECLARE
    curstmt             TEXT := '';
    result              INT4 := 0;
    class_table_name    TEXT := '';
BEGIN
    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT MADLIB_SCHEMA.__regclass_to_text(table_oid) 
             FROM % 
             WHERE column_type=''c''',
            meta_tbl_name
        ); 
                
    EXECUTE curstmt INTO class_table_name;
    
    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT COUNT(code)
             FROM %',
            class_table_name
        );
    
    EXECUTE curstmt INTO result;
    
    RETURN result;
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Get the feature name by the specified feature ID.
 *
 * @param feature_index     The ID of the feature.
 * @param meta_tbl_name     The full name of the metatable.
 *
 * @return The feature name.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__get_feature_name    
    (
    feature_index   INT,
    meta_tbl_name  TEXT
    )
RETURNS TEXT AS $$
DECLARE
    curstmt TEXT;
    result  TEXT := '';
BEGIN
    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT column_name 
             FROM   % 
             WHERE  id = % AND column_type = ''f'';',
            meta_tbl_name,
            MADLIB_SCHEMA.__to_char(feature_index)
        );
        
    EXECUTE curstmt INTO result;
    
    RETURN result;
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Get the column value by the specified column ID and code.
 *
 * @param column_index      The ID of the column.
 * @param code              The code of the column value.
 * @param column_type       The type of the column.
 *                          'i' means id, 'c' means class, 'f' means feature.
 * @param meta_tbl_name     The full name of the metatable.
 * 
 * @return The column's value corresponding to the give code. 
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__get_column_value    
    (
    column_index    INT,
    code            INT,
    column_type     CHAR,
    meta_tbl_name   TEXT
    )
RETURNS TEXT AS $$  
DECLARE
    curstmt     TEXT;
    names       TEXT[];
    result      TEXT := '';
    tmp_txt     TEXT := ' WHERE column_type = ''c''';
BEGIN
    PERFORM MADLIB_SCHEMA.__assert
        (
            code IS NOT NULL, 
            'the code of the value should not be null'
        );

    IF (column_type <> 'c') THEN
        tmp_txt = MADLIB_SCHEMA.__format
            (
                ' WHERE id = % AND column_type = ''%''', 
                column_index::TEXT, 
                column_type::TEXT
            );
    END IF;

    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT 
                 ARRAY[column_name, 
                       MADLIB_SCHEMA.__regclass_to_text(table_oid)] 
             FROM % 
             %',
            meta_tbl_name,
            tmp_txt
        ); 
                   
    EXECUTE curstmt INTO names;
    
    PERFORM MADLIB_SCHEMA.__assert(names[1] IS NOT NULL, 'No such column name');
    PERFORM MADLIB_SCHEMA.__assert(names[2] IS NOT NULL, 'No such table name');
    
    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT MADLIB_SCHEMA.__to_char(fval) 
             FROM % 
             WHERE code = %;',
            names[2],
            code::TEXT
        );

    EXECUTE curstmt INTO result;
    
    IF (result IS NULL) THEN
        result = 'NULL';
    END IF;
    
    RETURN result;
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Get the feature value by the specified feature ID and code.
 *
 * @param feature_index     The ID of the feature.
 * @param code              The code of the feature value.
 * @param meta_tbl_name     The full name of the metatable.  
 *
 * @return The value of specified code of the feature 
 *         whose id specified in feature_index.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__get_feature_value    
    (
    feature_index   INT,
    code            INT,
    meta_tbl_name  TEXT
    )
RETURNS TEXT AS $$  
DECLARE
    result TEXT := '';
BEGIN
    result = MADLIB_SCHEMA.__get_column_value
        (
            feature_index, 
            code, 
            'f', 
            meta_tbl_name
        );
            
    RETURN result;
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Get the ID column name.
 *
 * @param meta_tbl_name    The full name of the metatable.
 *
 * @return The ID column name.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__get_id_column_name    
    (
    meta_tbl_name  TEXT
    )
RETURNS TEXT AS $$
DECLARE
    curstmt TEXT;
    result  TEXT := '';
BEGIN
    PERFORM MADLIB_SCHEMA.__assert_table
        (
            meta_tbl_name,
            't'
        );
        
    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT column_name 
             FROM   % 
             WHERE  column_type = ''i'' 
             LIMIT 1',
            meta_tbl_name
        );
        
    EXECUTE curstmt INTO result;
    
    RETURN result;
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Get the class column name.
 *
 * @param meta_tbl_name    The full name of the metatable.
 *
 * @return The class column name. 
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__get_class_column_name    
    (
    meta_tbl_name  TEXT
    )
RETURNS TEXT AS $$
DECLARE
    curstmt TEXT;
    result  TEXT := '';
BEGIN
    PERFORM MADLIB_SCHEMA.__assert_table
        (
            meta_tbl_name,
            't'
        );
        
    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT column_name 
             FROM   % 
             WHERE  column_type = ''c'' LIMIT 1',
            meta_tbl_name
        );
        
    EXECUTE curstmt INTO result;
    
    RETURN result;
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Get the class value by the specified code.
 *
 * @param code               The code of the class value.
 * @param meta_tbl_name      The full name of the metatable.
 *
 * @return The class value corresponding to the code. 
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__get_class_value    
    (
    code            INT,
    meta_tbl_name  TEXT
    )
RETURNS TEXT AS $$  
DECLARE
    result TEXT := '';
BEGIN
    result = MADLIB_SCHEMA.__get_column_value(0, code, 'c', meta_tbl_name);
            
    RETURN result;
END
$$ LANGUAGE PLPGSQL;  


/*
 * @brief breakup each record from the training table.
 *        For example, we have the training table t(id, f1, f2, f3, class), 
 *        then the breakup table is bt(id, fid, fval, is_cont, class). 
 *        The id column of the two tables is the same. Each feature will be
 *        encoded to continuous numeric number. Assume that t has values
 *          (1, 'a', 1, 10, '+') 
 *          (2, 'b', 2, 8, '-')
 *          (3, 'd', null, 2, '+') 
 *        and all of them are discrete features, then the values of bt are 
 *          (1, 1, 'a', 'f', '+') 
 *          (2, 1, 'b', 'f', '-') 
 *          (3, 1, 'd', 'f', '+')
 *          (1, 2, 1, 'f', '+') 
 *          (2, 2, 2, 'f', '-') 
 *          (3, 2, null, 'f', '+')
 *          (1, 3, 10, 'f', '+') 
 *          (2, 3, 8, 'f', '-') 
 *          (3, 3, 2, 'f', '+')
 *
 * @param input_tbl_name      The full name of the input training table.
 * @param breakup_tbl_name    The name of the breakup table.
 * @param kv_cls_name         The name of the key-value table for class column.
 * @param id_col_name         The name of the ID column. 
 * @param attr_col_names      The array contains all the features' names.
 * @param is_conts            The subscript of the array denotes the feature index.
 *                            Each value of the array denotes the feature is
 *                            continuous ('t') or discrete ('f')
 * @param verbosity           > 0 means this function runs in verbose mode. 
 *
 * @return The name of the breakup table, which will be used to generate the encoded
 *         table.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__breakup_table
    (
    input_tbl_name        TEXT, 
    breakup_tbl_name      TEXT,
    kv_cls_name           TEXT,
    id_col_name           TEXT,
    cls_col_name          TEXT,
    attr_col_names        TEXT[],
    is_conts              BOOL[],
    h2hmv_routine_id      INT,
    verbosity             INT
    ) 
RETURNS VOID AS $$
DECLARE
    curstmt             TEXT;
    exec_begin          TIMESTAMP;
    where_txt           TEXT := '';
    fval_txt            TEXT := 'fval';
BEGIN
    exec_begin = clock_timestamp();
    
    EXECUTE 'DROP TABLE IF EXISTS ' || breakup_tbl_name;

m4_changequote(`>>>', `<<<')
m4_ifdef(>>>__GREENPLUM_GE_4_2_1__<<<, >>>
    -- if the DB is GPDB and its version is greater than or equal
    -- to 4.2, then we will use RLE compression for the encoded table.
    curstmt = MADLIB_SCHEMA.__format
        (
            'CREATE TEMP TABLE %
             (
             id      BIGINT ENCODING (compresstype=RLE_TYPE),
             fid     INT    ENCODING (compresstype=RLE_TYPE),
             fval    TEXT   ENCODING (compresstype=RLE_TYPE),
             is_cont BOOL   ENCODING (compresstype=RLE_TYPE),
             class   INT    ENCODING (compresstype=RLE_TYPE)
             )
             WITH(appendonly=true, orientation=column)
             DISTRIBUTED BY(id)',
            ARRAY[
                breakup_tbl_name
            ]
        );
<<<, >>>
    curstmt = MADLIB_SCHEMA.__format
        (
            'CREATE TEMP TABLE %
             (
             id      BIGINT,
             fid     INT,
             fval    TEXT,
             is_cont BOOL,
             class   INT
             )
            m4_ifdef(`__GREENPLUM__', `DISTRIBUTED BY (id)')',
            ARRAY[
                breakup_tbl_name
            ]
        );
<<<)
m4_changequote(>>>`<<<, >>>'<<<)

    EXECUTE curstmt;

    -- the supported missing value representation (' ', '?' and NULL) will
    -- be replace with NULL for easy processing later.
    -- the function __to_char is needed because on some databases an explicit
    -- cast to text is unavailable.
    IF (h2hmv_routine_id = 1) THEN
        where_txt = ' WHERE NULLIF(NULLIF(btrim(fval, '' ''), ''?''), '''') IS NOT NULL';
    ELSE
        fval_txt  = ' CASE WHEN NULLIF(NULLIF(btrim(fval, '' ''), ''?''), '''') IS NULL THEN
                                NULL
                            ELSE
                                fval
                      END ';
    END IF;

    IF (cls_col_name IS NULL) THEN
        -- if the kv_cls_name is null, then the class column will be null        
        curstmt = MADLIB_SCHEMA.__format
            (
                'INSERT INTO %(id, fid, fval, is_cont, class)
                 SELECT id, fid, % as fval, is_cont, class
                 FROM 
                    (
                     SELECT %, generate_series(1, %) as fid, 
                        unnest(array[MADLIB_SCHEMA.__to_char(%)]) as fval, 
                        unnest(array[''%''::BOOL]::BOOL[]) as is_cont, NULL as class
                     FROM
                        % t1
                    ) t
                  %',
                ARRAY[
                    breakup_tbl_name,
                    fval_txt,
                    id_col_name,
                    array_upper(is_conts, 1)::TEXT,
                    array_to_string(attr_col_names, '), MADLIB_SCHEMA.__to_char('),
                    array_to_string(is_conts, ''','''),
                    input_tbl_name,
                    where_txt
                ]
            );

    ELSE
        -- for scoring, as the class column may have some values which are not 
        -- appear in the training table, we need use left join here to ensure all
        -- the rows of the input table were breakup. Here, we simple encode those 
        -- values to 0. Therefore, during scoring, the samples with 0 (encoded
        -- value) as class label will be recognized as mis-classified.
        curstmt = MADLIB_SCHEMA.__format
            (
                'INSERT INTO %(id, fid, fval, is_cont, class)
                 SELECT id, fid, % as fval, is_cont, class
                 FROM 
                    (
                     SELECT %, generate_series(1, %) as fid, 
                        unnest(array[MADLIB_SCHEMA.__to_char(%)]) as fval, 
                        unnest(array[''%''::BOOL]::BOOL[]) as is_cont,
                        coalesce(code, 0::INT) as class
                     FROM
                        % t1 LEFT JOIN % t2
                     ON MADLIB_SCHEMA.__to_char(t1.%) = t2.fval                    
                    ) t
                 %',
                ARRAY[
                    breakup_tbl_name,
                    fval_txt,
                    id_col_name,
                    array_upper(is_conts, 1)::TEXT,
                    array_to_string(attr_col_names, '), MADLIB_SCHEMA.__to_char('),
                    array_to_string(is_conts, ''','''),
                    input_tbl_name,
                    kv_cls_name, 
                    cls_col_name,
                    where_txt
                ]
            );
    END IF;

    EXECUTE curstmt;

    IF (verbosity > 0) THEN
        RAISE INFO '%', curstmt;
        RAISE INFO 'time of breaking up the training table:%', 
            clock_timestamp() - exec_begin;
    END IF;
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Generate the vertical encoded table from the breakup table.
 *
 * @param breakup_tbl_name    The full name of the breakup table.
 * @param enc_tbl_name        The name of the encoded table. its schema is:
 *                              id BIGINT, 
 *                              fid INT, 
 *                              fval FLOAT8, 
 *                              is_cont BOOL, 
 *                              class INT
 * @param kv_attr_name        The name of the key-value table contains the encoded
 *                            result for all the features. For continuous feature,
 *                            it kept the average value of it if in 'explicit' mode;
 *                            nothing will kept if in 'ignore' mode.
 * @param is_tbl_tmp          If ture we will create the encoded table as a temp one.
 * @param verbosity           > 0 means this function runs in verbose mode. 
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__gen_vertical_encoded_table
    (
    breakup_tbl_name      TEXT,     
    enc_tbl_name          TEXT,
    kv_attr_name          TEXT,
    is_tbl_tmp            BOOL,
    verbosity             INT
    ) 
RETURNS VOID AS $$
DECLARE
    curstmt             TEXT;
    exec_begin          TIMESTAMP;
    tmp_txt             TEXT = '';
BEGIN
    IF (is_tbl_tmp) THEN
        tmp_txt = ' TEMP ';
    END IF;
               
    EXECUTE 'DROP TABLE IF EXISTS ' || enc_tbl_name;

m4_changequote(`>>>', `<<<')
m4_ifdef(>>>__GREENPLUM_GE_4_2_1__<<<, >>>
    curstmt = MADLIB_SCHEMA.__format
        (
            'CREATE % TABLE %
             (
             id      BIGINT  ENCODING (compresstype=RLE_TYPE),
             fid     INT     ENCODING (compresstype=RLE_TYPE),
             fval    FLOAT8  ENCODING (compresstype=RLE_TYPE),
             is_cont BOOL    ENCODING (compresstype=RLE_TYPE),
             class   INT     ENCODING (compresstype=RLE_TYPE)
             )
             WITH(appendonly=true, orientation=column)
             DISTRIBUTED BY(id)',
            ARRAY[
                tmp_txt,
                enc_tbl_name
            ]
        );
<<<, >>>
    curstmt = MADLIB_SCHEMA.__format
        (
            'CREATE % TABLE %
             (
             id      BIGINT,
             fid     INT,
             fval    FLOAT8,
             is_cont BOOL,
             class   INT
             )
            m4_ifdef(`__GREENPLUM__', `DISTRIBUTED BY (id)')',
            ARRAY[
                tmp_txt,
                enc_tbl_name
            ]
        );
<<<)
m4_changequote(>>>`<<<, >>>'<<<)

    IF (verbosity > 0) THEN
        RAISE INFO '%', curstmt;
    END IF;
    EXECUTE curstmt;

    -- Generating the encoded table through join the breakup table with
    -- the KV table for all the features
    curstmt = MADLIB_SCHEMA.__format
        (
            'INSERT INTO %(id, fid, fval, is_cont, class)
             SELECT p.id AS id, p.fid AS fid,
                 CASE WHEN (p.is_cont AND p.fval IS NOT NULL) THEN
                    p.fval::FLOAT8
                 ELSE 
                    m.code::FLOAT8 
                 END AS fval,
                 p.is_cont AS is_cont,
                 p.class::INT AS class
            FROM
                % p LEFT JOIN % m
            ON
                m.fid = p.fid AND
                (coalesce(m.fval, '''') = (coalesce(p.fval, '''')))',
            ARRAY[    
                enc_tbl_name,
                breakup_tbl_name,
                kv_attr_name
            ]
        );
    
    IF (verbosity > 0) THEN
        RAISE INFO '%', curstmt;
    END IF;
    EXECUTE curstmt;
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Generate the horizontal table from a given vertical table.
 *
 * @param hor_tbl_name          The full name of the horizontal table.
 * @param ver_tbl_name          The full name of the vertical table.
 * @param meta_tbl_name         The full name of the meta data table.
 * @param verbosity             > 0 means this function runs in verbose mode. 
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__gen_horizontal_encoded_table
    (
    hor_tbl_name        TEXT,
    ver_tbl_name        TEXT,
    attr_count          INT,
    verbosity           INT
    ) 
RETURNS VOID AS $$
DECLARE
    curstmt             TEXT;
    exec_begin          TIMESTAMP;
BEGIN
    exec_begin = clock_timestamp();

    EXECUTE 'DROP TABLE IF EXISTS ' || hor_tbl_name;
    curstmt = MADLIB_SCHEMA.__format
        (
            'CREATE TEMP TABLE %(id, fvals, class) AS
             SELECT
                 id,
                 MADLIB_SCHEMA.__array_indexed_agg(fval, %, fid) as fvals,
                 min(class)::INT as class
             FROM %
             GROUP BY id
             m4_ifdef(`__GREENPLUM__', `DISTRIBUTED BY (id)')',
            ARRAY[
                hor_tbl_name,
                attr_count::TEXT,
                ver_tbl_name
            ]
        );
    EXECUTE curstmt;

    IF (verbosity > 0) THEN
        RAISE INFO 'time of generating horizontal table from vertical table:%', 
            clock_timestamp() - exec_begin;
    END IF;
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Encode the continuous and discrete features and the class column.
 *        In 'ignore' mode, for each discrete feature/class, we will use 
 *        continuous integer to encode each distinct value (null value 
 *        will be excluded). Continuous feature will not be processed. 
 *        In 'explicit' mode, null value will be included for discrete 
 *        feature. For continuous feature, null value will be replaced by
 *        the average value of this feature.
 * 
 * @param kv_attr_name        The name of the key-value table contains the encoded
 *                            result for all the features. For continuous feature,
 *                            it kept the average value of it if in 'explicit' mode;
 *                            nothing will kept if in 'ignore' mode.
 * @param breakup_tbl_name    The name of the breakup table from raw training table.
 * @param h2hmv_routine_id    The ID of the routine which specifies 
 *                            How to handle missing value(h2hmv).
 * @param verbosity           > 0 means this function runs in verbose mode.
 *
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__encode_columns
    (
    kv_attr_name        TEXT,
    breakup_tbl_name    TEXT,
    h2hmv_routine_id    INT,
    verbosity           INT
    )
RETURNS VOID AS $$
DECLARE
    curstmt             TEXT;
    tmp_txt             TEXT = '';
BEGIN
    
    -- This table will be used to generate the KV table
    -- for the discrete features and retrieve the number 
    -- of distinct values for a feature outside of this
    -- function. Therefore, don't drop this table in this
    -- function.
    EXECUTE 'DROP TABLE IF EXISTS tmp_dist_table';
    curstmt = MADLIB_SCHEMA.__format
        (
            'CREATE TEMP TABLE tmp_dist_table AS 
             SELECT fid, fval, is_cont 
             FROM %
             GROUP BY fid, fval, is_cont',
            ARRAY[
                breakup_tbl_name
            ]
        );
    IF (verbosity > 0) THEN
        RAISE INFO '%', curstmt;
    END IF;

    EXECUTE curstmt;
    
    -- create the KV table for all the features and
    -- populate the keys of the discrete features
    -- to the table.
    EXECUTE 'DROP TABLE IF EXISTS ' || kv_attr_name;
    curstmt = MADLIB_SCHEMA.__format
        (
            'CREATE TABLE %(fid, fval, code) AS
             SELECT
                fid,
                fval,
                (rank() OVER (PARTITION BY fid ORDER BY fval))::FLOAT8 AS code
             FROM tmp_dist_table
             WHERE (NOT is_cont)
             m4_ifdef(`__GREENPLUM__', `DISTRIBUTED BY (fid, fval)')',
            ARRAY[
                kv_attr_name
            ]
        );
    IF (verbosity > 0) THEN
        RAISE INFO '%', curstmt;
    END IF;
    EXECUTE curstmt;
    
    -- In "explicit" mode, we need to replace the missing
    -- value with the average value. Therefore, we keep
    -- those values to the KV table.
    IF (h2hmv_routine_id = 2) THEN
        curstmt = MADLIB_SCHEMA.__format
            (
                'INSERT INTO %(fid, fval, code)
                    SELECT
                        fid,
                        null, 
                        coalesce(avg(fval::FLOAT8), 0.0)
                    FROM
                        % s 
                    WHERE is_cont
                    GROUP BY fid',
                ARRAY[
                    kv_attr_name,
                    breakup_tbl_name
                ]
            );         
        IF (verbosity > 0) THEN
            RAISE INFO '%', curstmt;
        END IF;

        EXECUTE curstmt;
    END IF;
END
$$ LANGUAGE PLPGSQL;


/*
 * @brief Encode a table for training in C4.5 and RF.
 *
 * @param input_tbl_name      The full name of the input table.
 * @param id_col_name         The name of id column.
 * @param feature_names       An array contains all the feature. If it's null, 
 *                            we will get all the columns of the input table.
 * @param cls_col_name        The name of class column.
 * @param cont_attr_names     An array contains all the continuous feature.
 *                            Null means no continuous feature.
 * @param enc_table_name      The full name of the encoded table.
 * @param meta_tbl_name       The full name of the metatable.
 * @param h2hmv_routine_id    The ID of the routine which specifies 
 *                            How to handle missing value(h2hmv).
 * @param verbosity           > 0 means this function runs in verbose mode. 
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__encode_table
    (
    input_tbl_name      TEXT, 
    id_col_name         TEXT, 
    feature_names       TEXT[],
    cls_col_name        TEXT, 
    cont_attr_names     TEXT[], 
    enc_table_name      TEXT,
    meta_tbl_name       TEXT,
    h2hmv_routine_id    INT,
    verbosity           INT
    ) 
RETURNS VOID AS $$
DECLARE
    curstmt             TEXT := '';
    attr_col_names      TEXT[]; 
    lit_attr_col_names  TEXT[];
    kv_attr_name        TEXT := enc_table_name || '_col';
    kv_cls_name         TEXT := enc_table_name || '_class';
    is_conts            BOOL[];
    breakup_tbl_name    TEXT := 'tmp_breakup_table';
    exec_begin          TIMESTAMP;
    ret                 MADLIB_SCHEMA.__enc_tbl_result;
BEGIN
    exec_begin = clock_timestamp();
        
    -- validate the training table
    PERFORM MADLIB_SCHEMA.__validate_input_table
        (
            input_tbl_name,
            feature_names,
            id_col_name,
            cls_col_name
        );
    
    -- create metatable
    PERFORM MADLIB_SCHEMA.__create_metatable(meta_tbl_name);
    
    -- retrieve all the features' names
    IF (feature_names IS NULL) THEN
m4_changequote(`>>>', `<<<')
m4_ifdef(`__HAS_ORDERED_AGGREGATES__', >>>
        curstmt = MADLIB_SCHEMA.__format
            (
                'SELECT array_agg(quote_ident(attname) ORDER BY attname) as attnames 
                 FROM   pg_attribute 
                 WHERE  attrelid = ''%''::regclass and attnum > 0 AND 
                        attname <> ''%'' AND
                        attname <> ''%'' AND
                        NOT attisdropped;', 
                ARRAY[
                    input_tbl_name,
                    id_col_name,
                    cls_col_name
                ]
            );   
        
        EXECUTE curstmt INTO attr_col_names;
<<<, >>>
        curstmt = MADLIB_SCHEMA.__format
            (
                'SELECT ARRAY
                 (
                 SELECT quote_ident(attname) 
                 FROM   pg_attribute 
                 WHERE  attrelid = ''%''::regclass and attnum > 0 AND 
                        attname <> ''%'' AND
                        attname <> ''%'' AND
                        NOT attisdropped
                 ORDER BY attname
                 LIMIT ALL
                 )', 
                ARRAY[
                    input_tbl_name,
                    id_col_name,
                    cls_col_name
                ]
            );          
        EXECUTE curstmt INTO attr_col_names;
<<<)
m4_changequote(>>>`<<<, >>>'<<<)    
    ELSE
        attr_col_names = MADLIB_SCHEMA.__array_sort(feature_names);
    END IF;

    -- an array contains if a feature is continuous or not
    -- the subscript is corresponding to the feature's ID 
    is_conts = MADLIB_SCHEMA.__array_elem_in(cont_attr_names, attr_col_names);

    ret.pre_proc_time = clock_timestamp() - exec_begin;
    exec_begin = clock_timestamp();

    -- create the KV table for the class column.
    EXECUTE 'DROP TABLE IF EXISTS ' || kv_cls_name;
    curstmt = MADLIB_SCHEMA.__format
            (
                'CREATE TABLE % AS
                    SELECT 0 as fid, 
                        MADLIB_SCHEMA.__to_char(%) AS fval, 
                        rank() OVER (ORDER BY %) AS code
                    FROM 
                        (
                            SELECT % FROM % GROUP BY %
                        ) t
                 m4_ifdef(`__GREENPLUM__', `DISTRIBUTED BY (fval)')',
                ARRAY[
                    kv_cls_name,
                    cls_col_name,
                    cls_col_name,
                    cls_col_name,
                    input_tbl_name,
                    cls_col_name
                ]
            );
    IF (verbosity > 0) THEN
        RAISE INFO '%', curstmt;
    END IF;
    EXECUTE curstmt;
    
    ret.gen_kv_time = clock_timestamp() - exec_begin;
    exec_begin = clock_timestamp();
    
    -- breakup each record of the training table and keep the result
    -- into a new table.
    PERFORM MADLIB_SCHEMA.__breakup_table
        (
            input_tbl_name,
            breakup_tbl_name,
            kv_cls_name,
            id_col_name,
            cls_col_name,
            attr_col_names,
            is_conts,
            h2hmv_routine_id,
            verbosity
        );  

    ret.breakup_tbl_time= clock_timestamp() - exec_begin;
    exec_begin = clock_timestamp();
    
    -- generate the KV table for both continuous features
    -- and discrete features.
    PERFORM MADLIB_SCHEMA.__encode_columns
        (   
            kv_attr_name,
            breakup_tbl_name,
            h2hmv_routine_id,
            verbosity
        );
    
    ret.gen_kv_time = ret.gen_kv_time + (clock_timestamp() - exec_begin);
    exec_begin = clock_timestamp();
    
    -- generate the encoded table using the breakup table
    -- and KV table for all the features.
    PERFORM MADLIB_SCHEMA.__gen_vertical_encoded_table
        (
            breakup_tbl_name, 
            enc_table_name,
            kv_attr_name,
            'f'::BOOL,
            verbosity
        );  
        
    ret.gen_enc_time = clock_timestamp() - exec_begin;
    exec_begin = clock_timestamp();
    
    SELECT ARRAY(SELECT quote_literal(unnest(attr_col_names))) INTO lit_attr_col_names;
    
    -- put the features' meta information to the metatable
    curstmt = MADLIB_SCHEMA.__format
        (
            'INSERT INTO %
             SELECT fid as id, (ARRAY[%])[fid] as column_name,
                    ''f'' as column_type,
                    is_cont,
                    ''%''::regclass::OID,
                count(fid) as num_dist_value
             FROM % t
             GROUP BY fid, is_cont',
            ARRAY[
                meta_tbl_name,
                array_to_string(lit_attr_col_names, ','),
                kv_attr_name,                
                'tmp_dist_table'
            ]
        );

    EXECUTE curstmt;

    IF (h2hmv_routine_id = 1) THEN
        -- retrieve the information of the columns (all the values in those 
        -- columns are missing), and insert them to the meta table. 
        curstmt = MADLIB_SCHEMA.__format
            (
                'INSERT INTO %
                 SELECT id, (ARRAY[%])[id] as column_name,
                        ''f'' as column_type, ''t'', NULL, 0
                 FROM (
                     SELECT generate_series(1, %) id
                     EXCEPT
                     SELECT id FROM % WHERE column_type = ''f'' 
                 ) t',
                ARRAY[
                    meta_tbl_name,
                    array_to_string(lit_attr_col_names, ','),
                    array_upper(attr_col_names, 1)::TEXT,
                    meta_tbl_name
                ]
            );
        EXECUTE curstmt;
    END IF;
    
    -- no need this table
    EXECUTE 'DROP TABLE IF EXISTS tmp_dist_table';

    -- put the class's meta information to the metatable
    curstmt = MADLIB_SCHEMA.__format
        (
            'INSERT INTO %
             SELECT 0 as id,''%'',
                    ''c'' as column_type,
                    ''f''::BOOL,
                    ''%''::regclass::OID,
                count(code) as num_dist_value
             FROM % t
             GROUP BY fid',
            ARRAY[
                meta_tbl_name,
                cls_col_name,
                kv_cls_name,                
                kv_cls_name
            ]
        );

    EXECUTE curstmt;

    -- put the id's meta information to the metatable
    PERFORM MADLIB_SCHEMA.__insert_into_metatable
        (
            meta_tbl_name, 
            array_upper(attr_col_names, 1) + 1, 
            id_col_name, 
            'i', 'f', NULL, 0
        );

    -- analyze the table, so that later the optimizer has the statistics
    -- information about this table
    EXECUTE 'ANALYZE ' || enc_table_name;

    ret.post_proc_time = clock_timestamp() - exec_begin;
    
    IF (verbosity > 0) THEN
        RAISE INFO 'time of encoding: %', ret;
    END IF;
END
$$ LANGUAGE PLPGSQL;  


/*
 * @brief Encode a table for classification/scoring.
 *
 * @param input_tbl_name        The full name of the input table.
 * @param enc_tbl_name          The full name of the encoded table.
 * @param meta_tbl_name         The full name of the metatable.
 * @param h2hmv_routine_id      The ID of the routine which specifies 
 *                              how to handle missing value(h2hmv). 
 * @param verbosity             > 0 means this function runs in verbose mode. 
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__encode_table
    (
    input_tbl_name      TEXT, 
    enc_tbl_name        TEXT,
    meta_tbl_name       TEXT,
    h2hmv_routine_id    INT,
    verbosity           INT
    ) 
RETURNS VOID AS $$
DECLARE
    curstmt             TEXT;
    attr_col_names      TEXT[];
    cls_col_name        TEXT;
    id_col_name         TEXT;
    kv_attr_name        TEXT;
    kv_cls_name         TEXT;
    is_conts            BOOL[];
    exec_begin          TIMESTAMP;
    breakup_tbl_name    TEXT := 'tmp_breakup_table';
BEGIN
    exec_begin = clock_timestamp();

    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT column_name FROM % WHERE column_type=''i''',
            meta_tbl_name
        ); 
    EXECUTE curstmt INTO id_col_name;

    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT column_name FROM % WHERE column_type=''c''',
            meta_tbl_name
        ); 
    EXECUTE curstmt INTO cls_col_name;

    IF (NOT MADLIB_SCHEMA.__column_exists(input_tbl_name, cls_col_name)) THEN
        cls_col_name = NULL;
    END IF;

m4_changequote(`>>>', `<<<')
m4_ifdef(`__HAS_ORDERED_AGGREGATES__', >>>
    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT array_agg(column_name order by id) 
             FROM % WHERE column_type=''f''',
            meta_tbl_name
        ); 
    EXECUTE curstmt INTO attr_col_names;

    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT 
                array_agg(is_cont order by id) 
             FROM % 
             WHERE column_type=''f''',
            meta_tbl_name
        ); 
    EXECUTE curstmt INTO is_conts;
<<<, >>>
    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT ARRAY
             (
              SELECT column_name 
              FROM % WHERE column_type=''f''
              ORDER BY id
              LIMIT ALL
             )',
            meta_tbl_name
        ); 
    EXECUTE curstmt INTO attr_col_names;

    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT ARRAY
             (
              SELECT is_cont 
              FROM % 
              WHERE column_type=''f''
              ORDER BY id
              LIMIT ALL
             )',
            meta_tbl_name
        ); 
    EXECUTE curstmt INTO is_conts;
<<<)
m4_changequote(>>>`<<<, >>>'<<<)

    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT 
                MADLIB_SCHEMA.__regclass_to_text(table_oid) as table_name 
             FROM % 
             WHERE column_type=''f'' limit 1',
            meta_tbl_name
        ); 
    EXECUTE curstmt INTO kv_attr_name;

    curstmt = MADLIB_SCHEMA.__format
        (
            'SELECT 
                MADLIB_SCHEMA.__regclass_to_text(table_oid) as table_name 
             FROM % 
             WHERE column_type=''c'' limit 1',
            meta_tbl_name
        ); 
    EXECUTE curstmt INTO kv_cls_name;

    PERFORM MADLIB_SCHEMA.__validate_input_table
        (
            input_tbl_name,
            NULL,
            id_col_name,
            NULL
        );
    
    -- breakup each record from the classification/scoring
    -- table and kept the results into a new table.
    PERFORM MADLIB_SCHEMA.__breakup_table
        (
            input_tbl_name, 
            breakup_tbl_name,
            kv_cls_name,
            id_col_name,
            cls_col_name,
            attr_col_names,
            is_conts,
            h2hmv_routine_id,
            verbosity
        );  
    
    -- generate the vertical encoded table.
    PERFORM MADLIB_SCHEMA.__gen_vertical_encoded_table
        (
            breakup_tbl_name, 
            'dt_tmp_ver_table',
            kv_attr_name,
            't'::BOOL,
            verbosity
        );      
    
    -- generate the horizontal encoded table.
    EXECUTE 'DROP TABLE IF EXISTS ' || enc_tbl_name;    
    PERFORM MADLIB_SCHEMA.__gen_horizontal_encoded_table
        (
            enc_tbl_name,
            'dt_tmp_ver_table',
            array_upper(is_conts, 1),
            verbosity
        ); 

    EXECUTE 'DROP TABLE IF EXISTS dt_tmp_ver_table';

    IF (verbosity > 0) THEN
        RAISE INFO 'Encoding time:%', clock_timestamp() - exec_begin;
    END IF;
END
$$ LANGUAGE PLPGSQL;