<?php
// File           sql.php / ibWebAdmin
// Purpose        perform sql commands/scripts on the selected database
// Author         Lutz Brueckner <irie@gmx.de>
// Copyright      (c) 2000, 2001, 2002, 2003, 2004 by Lutz Brueckner,
//                published under the terms of the GNU General Public Licence v.2,
//                see file LICENCE for details
// Created        <00/09/12 08:59:22 lb>
//
// $Id: sql.php,v 1.29 2004/04/22 19:12:52 lbrueckner Exp $


require('./inc/script_start.inc.php');
require('./inc/handle_watchtable.inc.php');


if (isset($s_edit_where)  &&  count($s_edit_where) > 0) {
    include('./inc/handle_editdata.inc.php');
}

//
// if the sql_enter-panel is open, get the content for the textarea
//
$idx = get_panel_index($s_sql_panels, 'sql_enter');
if ($s_sql_panels[$idx][2] == 'open') {

    $sql_script = isset($s_sql_buffer[$s_sql_pointer]) ? $s_sql_buffer[$s_sql_pointer] : '';

    if (isset($HTTP_POST_VARS['sql_script'])) {

        $sql_script = get_request_data('sql_script');

        if (SQL_MAXSAVE == 0 || substr_count($sql_script, "\n") <= SQL_MAXSAVE) {
            $s_sql_buffer[$s_sql_pointer] = $sql_script;
        }
        else {
            unset($s_sql_buffer[$s_sql_pointer]);
        }
    }

    // load file into the textarea
    if (isset($HTTP_POST_VARS['sql_load'])  &&
        is_uploaded_file($HTTP_POST_FILES['sql_file']['tmp_name'])) {

        $sql_script = implode('',file($HTTP_POST_FILES['sql_file']['tmp_name']));

        if (SQL_MAXSAVE == 0 || substr_count($sql_script, "\n") <= SQL_MAXSAVE) {
            $s_sql_buffer[$s_sql_pointer] = $sql_script;
        }
        else {
            unset($s_sql_buffer[$s_sql_pointer]);
        }
    }

    // read and execute a sql file
    if (isset($HTTP_POST_VARS['sql_execute'])  &&
        is_uploaded_file($HTTP_POST_FILES['sql_file']['tmp_name'])) {

        $sql_script = implode('',file($HTTP_POST_FILES['sql_file']['tmp_name']));
    }

    // get the query plan
    if (isset($HTTP_POST_VARS['sql_plan'])) {
        $sql = "SET PLAN ON;\n".$sql_script.";\n";
        list($binary_output, $binary_error) = isql_execute($sql, $s_login['user'], $s_login['password'], $s_login['database'], $s_login['host']);

        if (isset($binary_output[1])  &&  substr($binary_output[1], 0, 4) == 'PLAN') {
            $binary_output = array_slice($binary_output, 0, 2);
        }
        $plan_flag = TRUE;
    }


    if (isset($HTTP_GET_VARS['buf'])) {
        if ($HTTP_GET_VARS['buf'] == 'next') {
            $s_sql_pointer = ($s_sql_pointer < SQL_HISTORY_SIZE -1) ? $s_sql_pointer +1 : 0;
        }
        elseif ($HTTP_GET_VARS['buf'] == 'prev') {
            $s_sql_pointer = ($s_sql_pointer == 0) ? SQL_HISTORY_SIZE -1 : $s_sql_pointer -1;
        }
        $s_sql['buffer'] = '';
        $s_sql['more'] = FALSE;
        $sql_script = isset($s_sql_buffer[$s_sql_pointer]) ? $s_sql_buffer[$s_sql_pointer] : '';
    }

    if (isset($HTTP_POST_VARS['sql_go'])) {
        if (isset($HTTP_POST_VARS['sql_pointer'])
        &&  abs($HTTP_POST_VARS['sql_pointer'] < SQL_HISTORY_SIZE)) {
            $s_sql_pointer = abs($HTTP_POST_VARS['sql_pointer']);
        }
        else {
            $s_sql_pointer = 0;
        }
        $s_sql['buffer'] = '';
        $sql_script = isset($s_sql_buffer[$s_sql_pointer]) ? $s_sql_buffer[$s_sql_pointer] : '';
    }

    // include the javascript for jsrs requests
    if ($s_use_jsrs == TRUE) {
        $js_stack .= js_jsrs_sql_buffer();
    }

}


//
// script is called from the enter command form
//
if (isset($HTTP_POST_VARS['sql_run'])  ||
    isset($HTTP_POST_VARS['sql_display_all'])  ||
    isset($HTTP_POST_VARS['sql_execute'])) {

    // remove empty lines from userinput and put the statements into $lines[]
    if (isset($HTTP_POST_VARS['sql_run'])  ||
        isset($HTTP_POST_VARS['sql_execute'])) {

        $lines = explode(';', $sql_script);

        //remove whitespace and empty lines
        foreach($lines as $idx => $cmd){
            $cmd = trim($cmd);
            if ($cmd == '') {
                array_splice($lines, $idx, 1); 
                continue;
            }
            $lines[$idx] = $cmd;

            // execute the whole script through isql, if it contains a CREATE DATABASE
            if (preg_match('/^CREATE(\s)+(DATABASE|SCHEMA)/i', $cmd)) {
                $isql_flag = TRUE;

                if ($cmd{(strlen($cmd) -1)} != ';') {
                    $lines[$idx] .= ';';
                }
            }

            // empty the sql buffer if the script contains one or more select statements
            if (strncasecmp('select', $cmd, 6) == 0) {
                $s_sql['buffer'] = '';
            }
        }

        // make sure that there are no disabled commands in the script
        if (is_array($SQL_DISABLE)  &&  count($SQL_DISABLE) > 0
        &&  ($s_login['user'] != 'SYSDBA'  || SYSDBA_GET_ALL === FALSE)) {
            foreach ($SQL_DISABLE as $disable) {
                $len = strlen($disable);
                foreach ($lines as $line) {
                    if (strncasecmp($disable, $line, $len) == 0) {
                        $error = sprintf($ERRORS['DISABLED_CMD'], $disable);
                        break 2;
                    }
                }
            }
        }
        $s_sql['queries'] = $lines;
    }

    // 'display all'
    else {
        $lines = $s_sql['queries'];
    }

    // execute command/script by isql
    if (isset($isql_flag)  &&  empty($error)) {
        
        list($binary_output, $binary_error) = isql_execute($sql_script);

        $s_sql['buffer'] = '';
        array_shift($binary_output);      // discard the first line
        foreach($binary_output as $line) {
            $s_sql['buffer'] .= nl2br(str_replace(' ', '&nbsp;', $line)) . "<br>\n";
        }
    }

    // perform the query(s) by ibase_query()
    elseif ($s_connected == TRUE  &&  empty($error)) {

        $s_sql['more'] = FALSE;
        foreach ($lines as $lnr => $cmd) {
            $cnt = 0;
            $trans =  ibase_trans(TRANS_WRITE, $dbhandle);
            $res = @ibase_query($trans, $cmd)
                or $ib_error = ibase_errmsg();

            // if sql_output-panel is open         
            $idx = get_panel_index($s_sql_panels, 'sql_output');
            if ($s_sql_panels[$idx][2] == 'open') {     

                // if the query have result rows
                if (is_resource($res) && @ibase_num_fields($res) > 0) {

                    $fieldinfo[$lnr] = get_field_info($res);

                    // save the rows for the output in the sql_output panel
                    while ($row = @ibase_fetch_object($res)) {
                        $results[$lnr][] = get_object_vars ($row);
                        $cnt++;
                        if ($cnt >= SHOW_OUTPUT_ROWS
                        &&  !isset($HTTP_POST_VARS['sql_display_all'])) {
                            $s_sql['more'] = TRUE;
                            break;
                        }
                    }
                }
            }
            ibase_commit($trans);
        }
    }

    // cleanup the watchtable output buffer
    $s_watch_buffer = '';
}


//
// process the export buttons from the sql_enter panel
//
if ($HTTP_SERVER_VARS['REQUEST_METHOD'] == 'POST'  &&  !empty($HTTP_POST_VARS)) {
    foreach (array_keys($HTTP_POST_VARS) as $name) {
        if (preg_match('/sql_export_([0-9]+)/', $name, $matches)  
        &&  isset($s_sql['queries'][$matches[1]])) {

            $trans =  ibase_trans(TRANS_WRITE, $dbhandle);
            $res = @ibase_query($trans, $s_sql['queries'][$matches[1]]);

            if ($res === FALSE) {
                $ib_error = ibase_errmsg();
                break;
            }

            // send headers for user-download
            send_export_headers('application/octet-stream', 'export.csv');

            // build one line for the csv file from every result row
            while ($row = @ibase_fetch_row($res)) {
                $line = '';
                foreach ($row as $val) {
                    $line .= '"'.str_replace('"', '""', rtrim($val)).'",';
                }
                $line  = substr($line, 0, -1) . "\n";

                // send line to client
                echo $line;
            }
            ibase_free_result($res);
            ibase_commit($trans);
            exit();
        }
    }
}


//
// print out all the panels
//
$active = 'SQL';
$panels = &$s_sql_panels;

require('./inc/script_end.inc.php');



//
// build a result table for an 'select' from the sql_enter panel
//
function &get_result_table($result, &$fieldinfo, $idx) {

    $table = '<table id="resulttable_'.$idx."\" border=\"1\" cellspacing=\"0\" onselectstart=\"return false\" style=\"-moz-user-select: none\">\n"
            ."   <tr align=\"left\">\n"
            .'      <th>'.implode('</th><th>', array_keys($result[0]))."</th>\n"
            ."   </tr>\n";

    $cnt = count($result[0]);
    foreach ($result as $row) {
        $table .= "   <tr>\n";
        $nr = 0;
        foreach ($row as $val) {
            if ($val === NULL) {
                $val = '<i>NULL</i>';
            }
            elseif ($fieldinfo[$nr]['type'] == 'BLOB' && !empty($val)) {
                $val = '<i>BLOB</i>';
            }
            else {
                $val = trim($val);
            }
            $table .= "      <td>&nbsp;".$val."</td>\n";
            $nr++;
        }
        $table .= "   </tr>\n";
    }
    
    $table .= "</table>\n"
             .js_selectableelements()
             .js_selectabletablerows_listener('resulttable_'.$idx);

    return $table;
}


//
// build an export button for the sql_output panel
//
function sql_export_button($idx) {
    global $button_strings;

    $name = 'sql_export_'.$idx;

    return sprintf('<input type="submit" name="%s" value="%s"><br>', $name, $button_strings['Export'])."\n";
}


//
// get informations for the fields of a result set
//
function &get_field_info($res) {

    $info = array();
    $num = ibase_num_fields($res);
    for ($i=0; $i < $num; $i++) {
        $info[] = ibase_field_info($res, $i); 
    }

    return $info;
}

?>