pma/js/db_multi_table_query.js

/* vim: set expandtab sw=4 ts=4 sts=4: */
/**
 * @fileoverview    function used in QBE for DB
 * @name            Database Operations
 *
 * @requires    jQuery
 * @requires    jQueryUI
 * @requires    js/functions.js
 *
 */

/**
 * js file for handling AJAX and other events in db_multi_table_query.php
 */

/**
 * Unbind all event handlers before tearing down a page
 */
AJAX.registerTeardown('db_multi_table_query.js', function () {
    $('.tableNameSelect').each(function () {
        $(this).off('change');
    });
    $('#update_query_button').off('click');
    $('#add_column_button').off('click');
});

AJAX.registerOnload('db_multi_table_query.js', function () {
    var editor = PMA_getSQLEditor($('#MultiSqlquery'), {}, 'both');
    $('.CodeMirror-line').css('text-align', 'left');
    editor.setSize(-1, 50);

    var column_count = 3;
    PMA_init_slider();
    addNewColumnCallbacks();

    function escapeBacktick (s) {
        return s.replace('`', '``');
    }

    function escapeSingleQuote (s) {
        return s.replace('\\', '\\\\').replace('\'', '\\\'');
    }

    $('#update_query_button').on('click', function () {
        var columns = [];
        var table_aliases = {};
        $('.tableNameSelect').each(function () {
            $show = $(this).siblings('.show_col').first();
            if ($(this).val() !== '' && $show.prop('checked')) {
                var table_alias = $(this).siblings('.table_alias').first().val();
                var column_alias = $(this).siblings('.col_alias').first().val();

                if (table_alias !== '') {
                    columns.push([table_alias, $(this).siblings('.columnNameSelect').first().val()]);
                } else {
                    columns.push([$(this).val(), $(this).siblings('.columnNameSelect').first().val()]);
                }

                columns[columns.length - 1].push(column_alias);

                if ($(this).val() in table_aliases) {
                    if (!(table_aliases[$(this).val()].includes(table_alias))) {
                        table_aliases[$(this).val()].push(table_alias);
                    }
                } else {
                    table_aliases[$(this).val()] = [table_alias];
                }
            }
        });
        if (Object.keys(table_aliases).length === 0) {
            PMA_ajaxShowMessage('Nothing selected', false, 'error');
            return;
        }

        query = 'SELECT ';
        if(columns[0][1] == '*')
            query += '`' + escapeBacktick(columns[0][0]) + '`.' + escapeBacktick(columns[0][1]) + '';
        else
            query += '`' + escapeBacktick(columns[0][0]) + '`.`' + escapeBacktick(columns[0][1]) + '`';
        if (columns[0][2] !== '') {
            query += ' AS ' + columns[0][2];
        }
        for (var i = 1; i < columns.length; i++) {
            if(columns[i][1] == '*')
                query += ', `' + escapeBacktick(columns[i][0]) + '`.' + escapeBacktick(columns[i][1]) + '';
            else
                query += ', `' + escapeBacktick(columns[i][0]) + '`.`' + escapeBacktick(columns[i][1]) + '`';
            if (columns[i][2] !== '') {
                query += ' AS `' + escapeBacktick(columns[0][2]) + '`';
            }
        }
        query += '\nFROM ';
        var table_count = 0;
        for (var table in table_aliases) {
            for (var i = 0; i < table_aliases[table].length; i++) {
                if (table_count > 0) {
                    query += ', ';
                }
                query += '`' + escapeBacktick(table) + '`';
                if (table_aliases[table][i] !== '') {
                    query += ' AS `' + escapeBacktick(table_aliases[table][i]) + '`';
                }
                table_count++;
            }
        }

        $criteria_col_count = $('.criteria_col:checked').length;
        if ($criteria_col_count > 0) {
            query += '\nWHERE ';

            var logical_ops = [];

            var count = 0;

            $('.tableNameSelect').each(function () {
                $criteria_div = $(this).siblings('.slide-wrapper').first();
                $use_criteria = $(this).siblings('.criteria_col').first();
                if ($(this).val() !== '' && $use_criteria.prop('checked')) {
                    if (count > 0) {
                        $criteria_div.find('input.logical_op').each(function () {
                            if ($(this).prop('checked')) {
                                query += ' ' + $(this).val() + ' ';
                            }
                        });
                    }
                    formats_text = {
                        '=' : ' = \'%s\'',
                        '>' : ' > \'%s\'',
                        '>=' : ' >= \'%s\'',
                        '<' : ' < \'%s\'',
                        '<=' : ' <= \'%s\'',
                        '!=' : ' != \'%s\'',
                        'LIKE' : ' LIKE \'%s\'',
                        'LIKE \%...\%' : ' LIKE \'%%%s%%\'',
                        'NOT LIKE' : ' NOT LIKE \'%s\'',
                        'BETWEEN' : ' BETWEEN \'%s\'',
                        'NOT BETWEEN' : ' NOT BETWEEN \'%s\'',
                        'IS NULL' : ' \'%s\' IS NULL',
                        'IS NOT NULL' : ' \'%s\' IS NOT NULL',
                        'REGEXP' : ' REGEXP \'%s\'',
                        'REGEXP ^...$' : ' REGEXP \'^%s$\'',
                        'NOT REGEXP' : ' NOT REGEXP \'%s\''
                    };
                    query += '`' + escapeBacktick($(this).val()) + '`.';
                    query += '`' + escapeBacktick($(this).siblings('.columnNameSelect').first().val()) + '`';
                    if ($criteria_div.find('.criteria_rhs').first().val() === 'text') {
                        // query += " '" + $criteria_div.find('.rhs_text_val').first().val() + "'";
                        query += sprintf(formats_text[$criteria_div.find('.criteria_op').first().val()], escapeSingleQuote($criteria_div.find('.rhs_text_val').first().val()));
                    } else {
                        query += ' ' + $criteria_div.find('.criteria_op').first().val();
                        query += ' `' + escapeBacktick($criteria_div.find('.tableNameSelect').first().val()) + '`.';
                        query += '`' + escapeBacktick($criteria_div.find('.columnNameSelect').first().val()) + '`';
                    }
                    count++;
                }
            });
        }

        query += ';';
        editor.getDoc().setValue(query);
    });

    $('#submit_query').on('click', function () {
        var query = editor.getDoc().getValue();
        var data = {
            'db': $('#db_name').val(),
            'sql_query': query,
            'ajax_request': '1',
            'token': PMA_commonParams.get('token')
        };
        $.ajax({
            type: 'POST',
            url: 'db_multi_table_query.php',
            data: data,
            success: function (data) {
                $results_dom = $(data.message);
                $results_dom.find('.ajax:not(.pageselector)').each(function () {
                    $(this).on('click', function (event) {
                        event.preventDefault();
                    });
                });
                $results_dom.find('.autosubmit, .pageselector, .showAllRows, .filter_rows').each(function () {
                    $(this).on('change click select focus', function (event) {
                        event.preventDefault();
                    });
                });
                $('#sql_results').html($results_dom);
                $('#page_content').find('a').first().click();
            }
        });
    });

    $('#add_column_button').on('click', function () {
        column_count++;
        $new_column_dom = $($('#new_column_layout').html()).clone();
        $new_column_dom.find('div').first().find('div').first().attr('id', column_count.toString());
        $new_column_dom.find('a').first().remove();
        $new_column_dom.find('.pma_auto_slider').first().unwrap();
        $new_column_dom.find('.pma_auto_slider').first().attr('title', 'criteria');
        $('#add_column_button').parent().before($new_column_dom);
        PMA_init_slider();
        addNewColumnCallbacks();
    });

    function addNewColumnCallbacks () {
        $('.tableNameSelect').each(function () {
            $(this).on('change', function () {
                $sibs = $(this).siblings('.columnNameSelect');
                if ($sibs.length === 0) {
                    $sibs = $(this).parent().parent().find('.columnNameSelect');
                }
                $sibs.first().html($('#' + $.md5($(this).val())).html());
            });
        });

        $('.removeColumn').each(function () {
            $(this).on('click', function () {
                $(this).parent().remove();
            });
        });

        $('a.ajax').each(function () {
            $(this).on('click', function (event, from) {
                if (from === null) {
                    $checkbox = $(this).siblings('.criteria_col').first();
                    $checkbox.prop('checked', !$checkbox.prop('checked'));
                }
                $criteria_col_count = $('.criteria_col:checked').length;
                if ($criteria_col_count > 1) {
                    $(this).siblings('.slide-wrapper').first().find('.logical_operator').first().css('display','table-row');
                }
            });
        });

        $('.criteria_col').each(function () {
            $(this).on('change', function () {
                $anchor = $(this).siblings('a.ajax').first();
                $anchor.trigger('click', ['Trigger']);
            });
        });

        $('.criteria_rhs').each(function () {
            $(this).on('change', function () {
                $rhs_col = $(this).parent().parent().siblings('.rhs_table').first();
                $rhs_text = $(this).parent().parent().siblings('.rhs_text').first();
                if ($(this).val() === 'text') {
                    $rhs_col.css('display', 'none');
                    $rhs_text.css('display', 'table-row');
                } else if ($(this).val() === 'anotherColumn') {
                    $rhs_text.css('display', 'none');
                    $rhs_col.css('display', 'table-row');
                } else {
                    $rhs_text.css('display', 'none');
                    $rhs_col.css('display', 'none');
                }
            });
        });
    }
});