ViewsMatviewsTrait::_getArrLinks() - Code Metrics - Inspection of "move wizard creation form of view and matview to t..." - HuasoFoundries/phpPgAdmin6 - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — develop ( 823958...5e1209 )

by Felipe

created 2018-06-05 23:53 UTC

ViewsMatviewsTrait::_getArrLinks() B

↳ Parent: ViewsMatviewsTrait

Complexity

Conditions	6
Paths	2

Size

Total Lines	18
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	18
rs	8.8571
c	0
b	0
f	0
cc	6
eloc	8
nc	2
nop	0

<?php

/**
 * PHPPgAdmin v6.0.0-beta.48
 */

namespace PHPPgAdmin\Traits;

use PHPPgAdmin\Decorators\Decorator;

/**
 * Common trait for dealing with views or materialized views.
 */
trait ViewsMatviewsTrait
{
    public $href = '';
    public $misc;
    public $view_name;

    public function doSubTree()
    {
        $tabs    = $this->misc->getNavTabs($this->keystring);
        $items   = $this->adjustTabsForTree($tabs);
        $reqvars = $this->misc->getRequestVars($this->keystring);

        $attrs = [
            'text'   => Decorator::field('title'),
            'icon'   => Decorator::field('icon'),
            'action' => Decorator::actionurl(Decorator::field('url'), $reqvars, Decorator::field('urlvars'), [$this->keystring => $_REQUEST[$this->keystring]]),
            'branch' => Decorator::ifempty(
                Decorator::field('branch'),
                '',
                Decorator::url(
                    Decorator::field('url'),
                    Decorator::field('urlvars'),
                    $reqvars,
                    [
                        'action'         => 'tree',
                        $this->keystring => $_REQUEST[$this->keystring],
                    ]
                )
            ),
        ];

        return $this->printTree($items, $attrs, $this->keystring);
    }

    /**
     * Ask for select parameters and perform select.
     *
     * @param mixed $confirm
     * @param mixed $msg
     */
    public function doSelectRows($confirm, $msg = '')
    {
        $data = $this->misc->getDatabaseAccessor();

        if ($confirm) {
            $this->printTrail($this->keystring);
            $this->printTabs($this->keystring, 'select');
            $this->printMsg($msg);

            $attrs = $data->getTableAttributes($_REQUEST[$this->keystring]);

            echo '<form action="'.\SUBFOLDER.'/src/views/'.$this->script.'" method="post" id="selectform">';
            echo "\n";

            if ($attrs->recordCount() > 0) {
                // JavaScript for select all feature
                echo "<script type=\"text/javascript\">\n";
                echo "//<![CDATA[\n";
                echo "  function selectAll() {\n";
                echo "      for (var i=0; i<document.getElementById('selectform').elements.length; i++) {\n";
                echo "          var e = document.getElementById('selectform').elements[i];\n";
                echo "          if (e.name.indexOf('show') == 0) { \n ";
                echo "              e.checked = document.getElementById('selectform').selectall.checked;\n";
                echo "          }\n";
                echo "      }\n";
                echo "  }\n";
                echo "//]]>\n";
                echo "</script>\n";

                echo "<table>\n";

                // Output table header
                echo "<tr><th class=\"data\">{$this->lang['strshow']}</th><th class=\"data\">{$this->lang['strcolumn']}</th>";
                echo "<th class=\"data\">{$this->lang['strtype']}</th><th class=\"data\">{$this->lang['stroperator']}</th>";
                echo "<th class=\"data\">{$this->lang['strvalue']}</th></tr>";

                $i = 0;
                while (!$attrs->EOF) {
                    $attrs->fields['attnotnull'] = $data->phpBool($attrs->fields['attnotnull']);
                    // Set up default value if there isn't one already
                    if (!isset($_REQUEST['values'][$attrs->fields['attname']])) {
                        $_REQUEST['values'][$attrs->fields['attname']] = null;
                    }

                    if (!isset($_REQUEST['ops'][$attrs->fields['attname']])) {
                        $_REQUEST['ops'][$attrs->fields['attname']] = null;
                    }

                    // Continue drawing row
                    $id = (0 == ($i % 2) ? '1' : '2');
                    echo "<tr class=\"data{$id}\">\n";
                    echo '<td style="white-space:nowrap;">';
                    echo '<input type="checkbox" name="show[', htmlspecialchars($attrs->fields['attname']), ']"',
                    isset($_REQUEST['show'][$attrs->fields['attname']]) ? ' checked="checked"' : '', ' /></td>';
                    echo '<td style="white-space:nowrap;">', $this->misc->printVal($attrs->fields['attname']), '</td>';
                    echo '<td style="white-space:nowrap;">', $this->misc->printVal($data->formatType($attrs->fields['type'], $attrs->fields['atttypmod'])), '</td>';
                    echo '<td style="white-space:nowrap;">';
                    echo "<select name=\"ops[{$attrs->fields['attname']}]\">\n";
                    foreach (array_keys($data->selectOps) as $v) {
                        echo '<option value="', htmlspecialchars($v), '"', ($_REQUEST['ops'][$attrs->fields['attname']] == $v) ? ' selected="selected"' : '',
                        '>', htmlspecialchars($v), "</option>\n";
                    }
                    echo "</select></td>\n";
                    echo '<td style="white-space:nowrap;">', $data->printField(
                        "values[{$attrs->fields['attname']}]",
                        $_REQUEST['values'][$attrs->fields['attname']],
                        $attrs->fields['type']
                    ), '</td>';
                    echo "</tr>\n";
                    ++$i;
                    $attrs->moveNext();
                }
                // Select all checkbox
                echo "<tr><td colspan=\"5\"><input type=\"checkbox\" id=\"selectall\" name=\"selectall\" accesskey=\"a\" onclick=\"javascript:selectAll()\" /><label for=\"selectall\">{$this->lang['strselectallfields']}</label></td></tr>";
                echo "</table>\n";
            } else {
                echo "<p>{$this->lang['strinvalidparam']}</p>\n";
            }

            echo "<p><input type=\"hidden\" name=\"action\" value=\"selectrows\" />\n";
            echo '<input type="hidden" name="view" value="', htmlspecialchars($_REQUEST[$this->keystring]), "\" />\n";
            echo "<input type=\"hidden\" name=\"subject\" value=\"view\" />\n";
            echo $this->misc->form;
            echo "<input type=\"submit\" name=\"select\" accesskey=\"r\" value=\"{$this->lang['strselect']}\" />\n";
            echo "<input type=\"submit\" name=\"cancel\" value=\"{$this->lang['strcancel']}\" /></p>\n";
            echo "</form>\n";

            return;
        }
        $this->coalesceArr($_POST, 'show', []);

        $this->coalesceArr($_POST, 'values', []);

        $this->coalesceArr($_POST, 'nulls', []);

        // Verify that they haven't supplied a value for unary operators
        foreach ($_POST['ops'] as $k => $v) {
            if ('p' == $data->selectOps[$v] && $_POST['values'][$k] != '') {
                $this->doSelectRows(true, $this->lang['strselectunary']);

                return;
            }
        }

        if (0 == sizeof($_POST['show'])) {
            return $this->doSelectRows(true, $this->lang['strselectneedscol']);
        }
        // Generate query SQL
        $query = $data->getSelectSQL($_REQUEST[$this->keystring], array_keys($_POST['show']), $_POST['values'], $_POST['ops']);

        $_REQUEST['query']  = $query;
        $_REQUEST['return'] = 'schema';

        $this->setNoOutput(true);

        $display_controller = new \PHPPgAdmin\Controller\DisplayController($this->getContainer());

        return $display_controller->render();
    }

    /**
     * Prints the form wizard to create view or materialized view.
     */
    public function printWizardCreateForm()
    {
        $data = $this->misc->getDatabaseAccessor();

        $tables = $data->getTables(true);

        echo '<form action="'.\SUBFOLDER."/src/views/{$this->script}\" method=\"post\">\n";
        echo "<table>\n";
        echo "<tr><th class=\"data\">{$this->lang['strtables']}</th></tr>";
        echo "<tr>\n<td class=\"data1\">\n";

        $arrTables = [];
        while (!$tables->EOF) {
            $arrTmp                      = [];
            $arrTmp['schemaname']        = $tables->fields['nspname'];
            $arrTmp['tablename']         = $tables->fields['relname'];
            $schema_and_name             = $tables->fields['nspname'].'.'.$tables->fields['relname'];
            $arrTables[$schema_and_name] = serialize($arrTmp);
            $tables->moveNext();
        }
        echo \PHPPgAdmin\XHtml\HTMLController::printCombo($arrTables, 'formTables[]', false, '', true);

        echo "</td>\n</tr>\n";
        echo "</table>\n";
        echo "<p><input type=\"hidden\" name=\"action\" value=\"set_params_create\" />\n";
        echo $this->misc->form;
        echo "<input type=\"submit\" value=\"{$this->lang['strnext']}\" />\n";
        echo "<input type=\"submit\" name=\"cancel\" value=\"{$this->lang['strcancel']}\" /></p>\n";
        echo "</form>\n";
    }

    /**
     * Appends to selected fields.
     *
     * @param array  $arrTmp    The arr temporary
     * @param string $selFields The selected fields
     * @param array  $tmpHsh    The temporary hsh
     */
    private function _appendToSelFields($arrTmp, &$selFields, &$tmpHsh)
    {
        $field_arr = [$arrTmp['schemaname'], $arrTmp['tablename'], $arrTmp['fieldname']];

        $field_element = '"'.implode('"."', $field_arr).'"';
        if (empty($_POST['dblFldMeth'])) {
            // no doublon control
            $selFields .= $field_element.', ';
        // doublon control

        } elseif (empty($tmpHsh[$arrTmp['fieldname']])) {
            // field does not exist
            $selFields .= $field_element.', ';
            $tmpHsh[$arrTmp['fieldname']] = 1;
        } elseif ('rename' == $_POST['dblFldMeth']) {
            // field exist and must be renamed
            ++$tmpHsh[$arrTmp['fieldname']];
            $selFields .= $field_element.'  AS  "'.implode('_', $field_arr).'_'.$tmpHsh[$arrTmp['fieldname']].'", ';
        }
        //  if field already exist, just ignore this one
    }

    private function _getArrLinks()
    {
        $arrLinks = [];
        $count    = 0;
        // If we have links, out put the JOIN ... ON statements
        if (is_array($_POST['formLink'])) {
            // Filter out invalid/blank entries for our links

            foreach ($_POST['formLink'] as $curLink) {
                if (strlen($curLink['leftlink']) && strlen($curLink['rightlink']) && strlen($curLink['operator'])) {
                    $arrLinks[] = $curLink;
                }
            }
            // We must perform some magic to make sure that we have a valid join order
            $count = sizeof($arrLinks);
        }

        return [$arrLinks, $count];
    }

    /**
     * Actually creates the new wizard view in the database.
     *
     * @param bool $is_materialized true if it's a materialized view, false by default
     *
     * @return mixed either a sucess message, a redirection, an error message and who knows
     */
    public function doSaveCreateWiz($is_materialized = false)
    {
        $data = $this->misc->getDatabaseAccessor();

        // Check that they've given a name and fields they want to select

        if (!strlen($_POST['formView'])) {
            return $this->doSetParamsCreate($this->lang['strviewneedsname']);
        }
        if (!isset($_POST['formFields']) || !count($_POST['formFields'])) {
            return $this->doSetParamsCreate($this->lang['strviewneedsfields']);
        }
        $selFields = '';

        $tmpHsh = [];

        foreach ($_POST['formFields'] as $curField) {
            $arrTmp = unserialize($curField);
            $data->fieldArrayClean($arrTmp);

            $this->_appendToSelFields($arrTmp, $selFields, $tmpHsh);
        }

        $selFields = substr($selFields, 0, -2);
        unset($arrTmp, $tmpHsh);
        $linkFields  = '';
        $arrJoined   = [];
        $arrUsedTbls = [];

        list($arrLinks, $count) = $this->_getArrLinks();

        // If we have at least one join condition, output it

        $j = 0;

        while ($j < $count) {
            foreach ($arrLinks as $curLink) {
                $arrLeftLink  = unserialize($curLink['leftlink']);
                $arrRightLink = unserialize($curLink['rightlink']);
                $data->fieldArrayClean($arrLeftLink);
                $data->fieldArrayClean($arrRightLink);

                $tbl1 = "\"{$arrLeftLink['schemaname']}\".\"{$arrLeftLink['tablename']}\"";
                $tbl2 = "\"{$arrRightLink['schemaname']}\".\"{$arrRightLink['tablename']}\"";

                if (!((!in_array($curLink, $arrJoined, true) && in_array($tbl1, $arrUsedTbls, true)) || !count($arrJoined))) {
                    continue;
                }
                // Make sure for multi-column foreign keys that we use a table alias tables joined to more than once
                // This can (and should be) more optimized for multi-column foreign keys
                $adj_tbl2 = in_array($tbl2, $arrUsedTbls, true) ? "${tbl2} AS alias_ppa_".time() : $tbl2;

                $clause1 = "{$curLink['operator']} ${adj_tbl2} ON ({$tbl1}.\"{$arrLeftLink['fieldname']}\" = {$tbl2}.\"{$arrRightLink['fieldname']}\") ";
                $clause2 = "${tbl1} {$curLink['operator']} ${adj_tbl2} ON ({$tbl1}.\"{$arrLeftLink['fieldname']}\" = {$tbl2}.\"{$arrRightLink['fieldname']}\") ";

                $linkFields .= strlen($linkFields) ? $clause1 : $clause2;

                $arrJoined[] = $curLink;
                if (!in_array($tbl1, $arrUsedTbls, true)) {
                    $arrUsedTbls[] = $tbl1;
                }

                if (!in_array($tbl2, $arrUsedTbls, true)) {
                    $arrUsedTbls[] = $tbl2;
                }
            }
            ++$j;
        }

        //if linkFields has no length then either _POST['formLink'] was not set, or there were no join conditions
        //just select from all seleted tables - a cartesian join do a
        if (!strlen($linkFields)) {
            foreach ($_POST['formTables'] as $curTable) {
                $arrTmp = unserialize($curTable);
                $data->fieldArrayClean($arrTmp);
                $linkFields .= (strlen($linkFields) ? ', ' : ' ')."\"{$arrTmp['schemaname']}\".\"{$arrTmp['tablename']}\"";
            }
        }

        $addConditions = '';
        if (is_array($_POST['formCondition'])) {
            foreach ($_POST['formCondition'] as $curCondition) {
                if (strlen($curCondition['field']) && strlen($curCondition['txt'])) {
                    $arrTmp = unserialize($curCondition['field']);
                    $data->fieldArrayClean($arrTmp);
                    $condition = " \"{$arrTmp['schemaname']}\".\"{$arrTmp['tablename']}\".\"{$arrTmp['fieldname']}\" {$curCondition['operator']} '{$curCondition['txt']}' ";
                    $addConditions .= (strlen($addConditions) ? ' AND ' : ' ').$condition;
                }
            }
        }

        $viewQuery = "SELECT ${selFields} FROM ${linkFields} ";

        //add where from additional conditions
        if (strlen($addConditions)) {
            $viewQuery .= ' WHERE '.$addConditions;
        }

        try {
            $status = $data->createView($_POST['formView'], $viewQuery, false, $_POST['formComment'], $is_materialized);
            if (0 == $status) {
                $this->misc->setReloadBrowser(true);

                return $this->doDefault($this->lang['strviewcreated']);
            }

            return $this->doSetParamsCreate($this->lang['strviewcreatedbad']);
        } catch (\PHPPgAdmin\ADOdbException $e) {
            return $this->halt($e->getMessage());
        }
    }

    public function printParamsCreateForm()
    {
        $data = $this->misc->getDatabaseAccessor();

        $tblCount = sizeof($_POST['formTables']);
        //unserialize our schema/table information and store in arrSelTables
        for ($i = 0; $i < $tblCount; ++$i) {
            $arrSelTables[] = unserialize($_POST['formTables'][$i]);
        }

        //get linking keys
        $rsLinkKeys = $data->getLinkingKeys($arrSelTables);

        $linkCount  = $rsLinkKeys->recordCount() > $tblCount ? $rsLinkKeys->recordCount() : $tblCount;

        $arrFields = []; //array that will hold all our table/field names

        //if we have schemas we need to specify the correct schema for each table we're retrieiving
        //with getTableAttributes
        $curSchema = $data->_schema;
        for ($i = 0; $i < $tblCount; ++$i) {
            if ($arrSelTables[$i]['schemaname'] != $data->_schema) {
                $data->setSchema($arrSelTables[$i]['schemaname']);
            }

            $attrs = $data->getTableAttributes($arrSelTables[$i]['tablename']);
            while (!$attrs->EOF) {
                $arrFields["{$arrSelTables[$i]['schemaname']}.{$arrSelTables[$i]['tablename']}.{$attrs->fields['attname']}"] = serialize(
                    [
                        'schemaname' => $arrSelTables[$i]['schemaname'],
                        'tablename'  => $arrSelTables[$i]['tablename'],
                        'fieldname'  => $attrs->fields['attname'], ]
                );
                $attrs->moveNext();
            }

            $data->setSchema($curSchema);
        }
        asort($arrFields);

        echo '<form action="'.\SUBFOLDER."/src/views/materializedviews\" method=\"post\">\n";
        echo "<table>\n";
        echo "<tr><th class=\"data\">{$this->lang['strviewname']}</th></tr>";
        echo "<tr>\n<td class=\"data1\">\n";
        // View name
        echo '<input name="formView" value="', htmlspecialchars($_REQUEST['formView']), "\" size=\"32\" maxlength=\"{$data->_maxNameLen}\" />\n";
        echo "</td>\n</tr>\n";
        echo "<tr><th class=\"data\">{$this->lang['strcomment']}</th></tr>";
        echo "<tr>\n<td class=\"data1\">\n";
        // View comments
        echo '<textarea name="formComment" rows="3" cols="32">',
        htmlspecialchars($_REQUEST['formComment']), "</textarea>\n";
        echo "</td>\n</tr>\n";
        echo "</table>\n";

        // Output selector for fields to be retrieved from view
        echo "<table>\n";
        echo "<tr><th class=\"data\">{$this->lang['strcolumns']}</th></tr>";
        echo "<tr>\n<td class=\"data1\">\n";
        echo \PHPPgAdmin\XHtml\HTMLController::printCombo($arrFields, 'formFields[]', false, '', true);
        echo "</td>\n</tr>";
        echo "<tr><td><input type=\"radio\" name=\"dblFldMeth\" id=\"dblFldMeth1\" value=\"rename\" /><label for=\"dblFldMeth1\">{$this->lang['strrenamedupfields']}</label>";
        echo "<br /><input type=\"radio\" name=\"dblFldMeth\" id=\"dblFldMeth2\" value=\"drop\" /><label for=\"dblFldMeth2\">{$this->lang['strdropdupfields']}</label>";
        echo "<br /><input type=\"radio\" name=\"dblFldMeth\" id=\"dblFldMeth3\" value=\"\" checked=\"checked\" /><label for=\"dblFldMeth3\">{$this->lang['strerrordupfields']}</label></td></tr></table><br />";

        // Output the Linking keys combo boxes
        echo "<table>\n";
        echo "<tr><th class=\"data\">{$this->lang['strviewlink']}</th></tr>";
        $rowClass = 'data1';
        for ($i = 0; $i < $linkCount; ++$i) {
            // Initialise variables
            if (!isset($formLink[$i]['operator'])) {
                $formLink[$i]['operator'] = 'INNER JOIN';
            }

            echo "<tr>\n<td class=\"${rowClass}\">\n";

            if (!$rsLinkKeys->EOF) {
                $curLeftLink  = htmlspecialchars(serialize(['schemaname' => $rsLinkKeys->fields['p_schema'], 'tablename' => $rsLinkKeys->fields['p_table'], 'fieldname' => $rsLinkKeys->fields['p_field']]));
                $curRightLink = htmlspecialchars(serialize(['schemaname' => $rsLinkKeys->fields['f_schema'], 'tablename' => $rsLinkKeys->fields['f_table'], 'fieldname' => $rsLinkKeys->fields['f_field']]));
                $rsLinkKeys->moveNext();
            } else {
                $curLeftLink  = '';
                $curRightLink = '';
            }

            echo \PHPPgAdmin\XHtml\HTMLController::printCombo($arrFields, "formLink[${i}][leftlink]", true, $curLeftLink, false);
            echo \PHPPgAdmin\XHtml\HTMLController::printCombo($data->joinOps, "formLink[${i}][operator]", true, $formLink[$i]['operator']);

            echo \PHPPgAdmin\XHtml\HTMLController::printCombo($arrFields, "formLink[${i}][rightlink]", true, $curRightLink, false);
            echo "</td>\n</tr>\n";
            $rowClass = 'data1' == $rowClass ? 'data2' : 'data1';
        }
        echo "</table>\n<br />\n";

        // Build list of available operators (infix only)
        $arrOperators = [];
        foreach ($data->selectOps as $k => $v) {
            if ('i' == $v) {
                $arrOperators[$k] = $k;
            }
        }

        // Output additional conditions, note that this portion of the wizard treats the right hand side as literal values
        //(not as database objects) so field names will be treated as strings, use the above linking keys section to perform joins
        echo "<table>\n";
        echo "<tr><th class=\"data\">{$this->lang['strviewconditions']}</th></tr>";
        $rowClass = 'data1';
        for ($i = 0; $i < $linkCount; ++$i) {
            echo "<tr>\n<td class=\"${rowClass}\">\n";
            echo \PHPPgAdmin\XHtml\HTMLController::printCombo($arrFields, "formCondition[${i}][field]");
            echo \PHPPgAdmin\XHtml\HTMLController::printCombo($arrOperators, "formCondition[${i}][operator]", false, '', false);
            echo "<input type=\"text\" name=\"formCondition[${i}][txt]\" />\n";
            echo "</td>\n</tr>\n";
            $rowClass = 'data1' == $rowClass ? 'data2' : 'data1';
        }
        echo "</table>\n";
        echo "<p><input type=\"hidden\" name=\"action\" value=\"save_create_wiz\" />\n";

        foreach ($arrSelTables as $curTable) {
            echo '<input type="hidden" name="formTables[]" value="'.htmlspecialchars(serialize($curTable))."\" />\n";
        }

        echo $this->misc->form;
        echo "<input type=\"submit\" value=\"{$this->lang['strcreate']}\" />\n";
        echo "<input type=\"submit\" name=\"cancel\" value=\"{$this->lang['strcancel']}\" /></p>\n";
        echo "</form>\n";
    }

    abstract public function doSetParamsCreate($msg = '');
}


1			<?php
2
3			/**
4			* PHPPgAdmin v6.0.0-beta.48
5			*/
6
7			namespace PHPPgAdmin\Traits;
8
9			use PHPPgAdmin\Decorators\Decorator;
10
11			/**
12			* Common trait for dealing with views or materialized views.
13			*/
14			trait ViewsMatviewsTrait
15			{
16			public $href = '';
17			public $misc;
18			public $view_name;
19
20			public function doSubTree()
21			{
22			$tabs = $this->misc->getNavTabs($this->keystring);
23			$items = $this->adjustTabsForTree($tabs);
24			$reqvars = $this->misc->getRequestVars($this->keystring);
25
26			$attrs = [
27			'text' => Decorator::field('title'),
28			'icon' => Decorator::field('icon'),
29			'action' => Decorator::actionurl(Decorator::field('url'), $reqvars, Decorator::field('urlvars'), [$this->keystring => $_REQUEST[$this->keystring]]),
30			'branch' => Decorator::ifempty(
31			Decorator::field('branch'),
32			'',
33			Decorator::url(
34			Decorator::field('url'),
35			Decorator::field('urlvars'),
36			$reqvars,
37			[
38			'action' => 'tree',
39			$this->keystring => $_REQUEST[$this->keystring],
40			]
41			)
42			),
43			];
44
45			return $this->printTree($items, $attrs, $this->keystring);
46			}
47
48			/**
49			* Ask for select parameters and perform select.
50			*
51			* @param mixed $confirm
52			* @param mixed $msg
53			*/
54			public function doSelectRows($confirm, $msg = '')
55			{
56			$data = $this->misc->getDatabaseAccessor();
57
58			if ($confirm) {
59			$this->printTrail($this->keystring);
60			$this->printTabs($this->keystring, 'select');
61			$this->printMsg($msg);
62
63			$attrs = $data->getTableAttributes($_REQUEST[$this->keystring]);
64
65			echo '<form action="'.\SUBFOLDER.'/src/views/'.$this->script.'" method="post" id="selectform">';
66			echo "\n";
67
68			if ($attrs->recordCount() > 0) {
69			// JavaScript for select all feature
70			echo "<script type=\"text/javascript\">\n";
71			echo "//<![CDATA[\n";
72			echo " function selectAll() {\n";
73			echo " for (var i=0; i<document.getElementById('selectform').elements.length; i++) {\n";
74			echo " var e = document.getElementById('selectform').elements[i];\n";
75			echo " if (e.name.indexOf('show') == 0) { \n ";
76			echo " e.checked = document.getElementById('selectform').selectall.checked;\n";
77			echo " }\n";
78			echo " }\n";
79			echo " }\n";
80			echo "//]]>\n";
81			echo "</script>\n";
82
83			echo "<table>\n";
84
85			// Output table header
86			echo "<tr><th class=\"data\">{$this->lang['strshow']}</th><th class=\"data\">{$this->lang['strcolumn']}</th>";
87			echo "<th class=\"data\">{$this->lang['strtype']}</th><th class=\"data\">{$this->lang['stroperator']}</th>";
88			echo "<th class=\"data\">{$this->lang['strvalue']}</th></tr>";
89
90			$i = 0;
91			while (!$attrs->EOF) {
92			$attrs->fields['attnotnull'] = $data->phpBool($attrs->fields['attnotnull']);
93			// Set up default value if there isn't one already
94			if (!isset($_REQUEST['values'][$attrs->fields['attname']])) {
95			$_REQUEST['values'][$attrs->fields['attname']] = null;
96			}
97
98			if (!isset($_REQUEST['ops'][$attrs->fields['attname']])) {
99			$_REQUEST['ops'][$attrs->fields['attname']] = null;
100			}
101
102			// Continue drawing row
103			$id = (0 == ($i % 2) ? '1' : '2');
104			echo "<tr class=\"data{$id}\">\n";
105			echo '<td style="white-space:nowrap;">';
106			echo '<input type="checkbox" name="show[', htmlspecialchars($attrs->fields['attname']), ']"',
107			isset($_REQUEST['show'][$attrs->fields['attname']]) ? ' checked="checked"' : '', ' /></td>';
108			echo '<td style="white-space:nowrap;">', $this->misc->printVal($attrs->fields['attname']), '</td>';
109			echo '<td style="white-space:nowrap;">', $this->misc->printVal($data->formatType($attrs->fields['type'], $attrs->fields['atttypmod'])), '</td>';
110			echo '<td style="white-space:nowrap;">';
111			echo "<select name=\"ops[{$attrs->fields['attname']}]\">\n";
112			foreach (array_keys($data->selectOps) as $v) {
113			echo '<option value="', htmlspecialchars($v), '"', ($_REQUEST['ops'][$attrs->fields['attname']] == $v) ? ' selected="selected"' : '',
114			'>', htmlspecialchars($v), "</option>\n";
115			}
116			echo "</select></td>\n";
117			echo '<td style="white-space:nowrap;">', $data->printField(
118			"values[{$attrs->fields['attname']}]",
119			$_REQUEST['values'][$attrs->fields['attname']],
120			$attrs->fields['type']
121			), '</td>';
122			echo "</tr>\n";
123			++$i;
124			$attrs->moveNext();
125			}
126			// Select all checkbox
127			echo "<tr><td colspan=\"5\"><input type=\"checkbox\" id=\"selectall\" name=\"selectall\" accesskey=\"a\" onclick=\"javascript:selectAll()\" /><label for=\"selectall\">{$this->lang['strselectallfields']}</label></td></tr>";
128			echo "</table>\n";
129			} else {
130			echo "<p>{$this->lang['strinvalidparam']}</p>\n";
131			}
132
133			echo "<p><input type=\"hidden\" name=\"action\" value=\"selectrows\" />\n";
134			echo '<input type="hidden" name="view" value="', htmlspecialchars($_REQUEST[$this->keystring]), "\" />\n";
135			echo "<input type=\"hidden\" name=\"subject\" value=\"view\" />\n";
136			echo $this->misc->form;
137			echo "<input type=\"submit\" name=\"select\" accesskey=\"r\" value=\"{$this->lang['strselect']}\" />\n";
138			echo "<input type=\"submit\" name=\"cancel\" value=\"{$this->lang['strcancel']}\" /></p>\n";
139			echo "</form>\n";
140
141			return;
142			}
143			$this->coalesceArr($_POST, 'show', []);
144
145			$this->coalesceArr($_POST, 'values', []);
146
147			$this->coalesceArr($_POST, 'nulls', []);
148
149			// Verify that they haven't supplied a value for unary operators
150			foreach ($_POST['ops'] as $k => $v) {
151			if ('p' == $data->selectOps[$v] && $_POST['values'][$k] != '') {
152			$this->doSelectRows(true, $this->lang['strselectunary']);
153
154			return;
155			}
156			}
157
158			if (0 == sizeof($_POST['show'])) {
159			return $this->doSelectRows(true, $this->lang['strselectneedscol']);
160			}
161			// Generate query SQL
162			$query = $data->getSelectSQL($_REQUEST[$this->keystring], array_keys($_POST['show']), $_POST['values'], $_POST['ops']);
163
164			$_REQUEST['query'] = $query;
165			$_REQUEST['return'] = 'schema';
166
167			$this->setNoOutput(true);
168
169			$display_controller = new \PHPPgAdmin\Controller\DisplayController($this->getContainer());
170
171			return $display_controller->render();
172			}
173
174			/**
175			* Prints the form wizard to create view or materialized view.
176			*/
177			public function printWizardCreateForm()
178			{
179			$data = $this->misc->getDatabaseAccessor();
180
181			$tables = $data->getTables(true);
182
183			echo '<form action="'.\SUBFOLDER."/src/views/{$this->script}\" method=\"post\">\n";
184			echo "<table>\n";
185			echo "<tr><th class=\"data\">{$this->lang['strtables']}</th></tr>";
186			echo "<tr>\n<td class=\"data1\">\n";
187
188			$arrTables = [];
189			while (!$tables->EOF) {
190			$arrTmp = [];
191			$arrTmp['schemaname'] = $tables->fields['nspname'];
192			$arrTmp['tablename'] = $tables->fields['relname'];
193			$schema_and_name = $tables->fields['nspname'].'.'.$tables->fields['relname'];
194			$arrTables[$schema_and_name] = serialize($arrTmp);
195			$tables->moveNext();
196			}
197			echo \PHPPgAdmin\XHtml\HTMLController::printCombo($arrTables, 'formTables[]', false, '', true);
198
199			echo "</td>\n</tr>\n";
200			echo "</table>\n";
201			echo "<p><input type=\"hidden\" name=\"action\" value=\"set_params_create\" />\n";
202			echo $this->misc->form;
203			echo "<input type=\"submit\" value=\"{$this->lang['strnext']}\" />\n";
204			echo "<input type=\"submit\" name=\"cancel\" value=\"{$this->lang['strcancel']}\" /></p>\n";
205			echo "</form>\n";
206			}
207
208			/**
209			* Appends to selected fields.
210			*
211			* @param array $arrTmp The arr temporary
212			* @param string $selFields The selected fields
213			* @param array $tmpHsh The temporary hsh
214			*/
215			private function _appendToSelFields($arrTmp, &$selFields, &$tmpHsh)
216			{
217			$field_arr = [$arrTmp['schemaname'], $arrTmp['tablename'], $arrTmp['fieldname']];
218
219			$field_element = '"'.implode('"."', $field_arr).'"';
220			if (empty($_POST['dblFldMeth'])) {
221			// no doublon control
222			$selFields .= $field_element.', ';
223			// doublon control
			0 ignored issues – show Coding Style introduced 2018-06-05 23:44 UTC by Report Bug Copy Issue Report Line indented incorrectly; expected at least 12 spaces, found 8 Loading history...
224			} elseif (empty($tmpHsh[$arrTmp['fieldname']])) {
225			// field does not exist
226			$selFields .= $field_element.', ';
227			$tmpHsh[$arrTmp['fieldname']] = 1;
228			} elseif ('rename' == $_POST['dblFldMeth']) {
229			// field exist and must be renamed
230			++$tmpHsh[$arrTmp['fieldname']];
231			$selFields .= $field_element.' AS "'.implode('_', $field_arr).'_'.$tmpHsh[$arrTmp['fieldname']].'", ';
232			}
233			// if field already exist, just ignore this one
234			}
235
236			private function _getArrLinks()
237			{
238			$arrLinks = [];
239			$count = 0;
240			// If we have links, out put the JOIN ... ON statements
241			if (is_array($_POST['formLink'])) {
242			// Filter out invalid/blank entries for our links
243
244			foreach ($_POST['formLink'] as $curLink) {
245			if (strlen($curLink['leftlink']) && strlen($curLink['rightlink']) && strlen($curLink['operator'])) {
246			$arrLinks[] = $curLink;
247			}
248			}
249			// We must perform some magic to make sure that we have a valid join order
250			$count = sizeof($arrLinks);
251			}
252
253			return [$arrLinks, $count];
254			}
255
256			/**
257			* Actually creates the new wizard view in the database.
258			*
259			* @param bool $is_materialized true if it's a materialized view, false by default
260			*
261			* @return mixed either a sucess message, a redirection, an error message and who knows
262			*/
263			public function doSaveCreateWiz($is_materialized = false)
264			{
265			$data = $this->misc->getDatabaseAccessor();
266
267			// Check that they've given a name and fields they want to select
268
269			if (!strlen($_POST['formView'])) {
270			return $this->doSetParamsCreate($this->lang['strviewneedsname']);
271			}
272			if (!isset($_POST['formFields']) \|\| !count($_POST['formFields'])) {
273			return $this->doSetParamsCreate($this->lang['strviewneedsfields']);
274			}
275			$selFields = '';
276
277			$tmpHsh = [];
278
279			foreach ($_POST['formFields'] as $curField) {
280			$arrTmp = unserialize($curField);
281			$data->fieldArrayClean($arrTmp);
282
283			$this->_appendToSelFields($arrTmp, $selFields, $tmpHsh);
284			}
285
286			$selFields = substr($selFields, 0, -2);
287			unset($arrTmp, $tmpHsh);
288			$linkFields = '';
289			$arrJoined = [];
290			$arrUsedTbls = [];
291
292			list($arrLinks, $count) = $this->_getArrLinks();
293
294			// If we have at least one join condition, output it
295
296			$j = 0;
297
298			while ($j < $count) {
299			foreach ($arrLinks as $curLink) {
300			$arrLeftLink = unserialize($curLink['leftlink']);
301			$arrRightLink = unserialize($curLink['rightlink']);
302			$data->fieldArrayClean($arrLeftLink);
303			$data->fieldArrayClean($arrRightLink);
304
305			$tbl1 = "\"{$arrLeftLink['schemaname']}\".\"{$arrLeftLink['tablename']}\"";
306			$tbl2 = "\"{$arrRightLink['schemaname']}\".\"{$arrRightLink['tablename']}\"";
307
308			if (!((!in_array($curLink, $arrJoined, true) && in_array($tbl1, $arrUsedTbls, true)) \|\| !count($arrJoined))) {
309			continue;
310			}
311			// Make sure for multi-column foreign keys that we use a table alias tables joined to more than once
312			// This can (and should be) more optimized for multi-column foreign keys
313			$adj_tbl2 = in_array($tbl2, $arrUsedTbls, true) ? "${tbl2} AS alias_ppa_".time() : $tbl2;
314
315			$clause1 = "{$curLink['operator']} ${adj_tbl2} ON ({$tbl1}.\"{$arrLeftLink['fieldname']}\" = {$tbl2}.\"{$arrRightLink['fieldname']}\") ";
316			$clause2 = "${tbl1} {$curLink['operator']} ${adj_tbl2} ON ({$tbl1}.\"{$arrLeftLink['fieldname']}\" = {$tbl2}.\"{$arrRightLink['fieldname']}\") ";
317
318			$linkFields .= strlen($linkFields) ? $clause1 : $clause2;
319
320			$arrJoined[] = $curLink;
321			if (!in_array($tbl1, $arrUsedTbls, true)) {
322			$arrUsedTbls[] = $tbl1;
323			}
324
325			if (!in_array($tbl2, $arrUsedTbls, true)) {
326			$arrUsedTbls[] = $tbl2;
327			}
328			}
329			++$j;
330			}
331
332			//if linkFields has no length then either _POST['formLink'] was not set, or there were no join conditions
333			//just select from all seleted tables - a cartesian join do a
334			if (!strlen($linkFields)) {
335			foreach ($_POST['formTables'] as $curTable) {
336			$arrTmp = unserialize($curTable);
337			$data->fieldArrayClean($arrTmp);
338			$linkFields .= (strlen($linkFields) ? ', ' : ' ')."\"{$arrTmp['schemaname']}\".\"{$arrTmp['tablename']}\"";
339			}
340			}
341
342			$addConditions = '';
343			if (is_array($_POST['formCondition'])) {
344			foreach ($_POST['formCondition'] as $curCondition) {
345			if (strlen($curCondition['field']) && strlen($curCondition['txt'])) {
346			$arrTmp = unserialize($curCondition['field']);
347			$data->fieldArrayClean($arrTmp);
348			$condition = " \"{$arrTmp['schemaname']}\".\"{$arrTmp['tablename']}\".\"{$arrTmp['fieldname']}\" {$curCondition['operator']} '{$curCondition['txt']}' ";
349			$addConditions .= (strlen($addConditions) ? ' AND ' : ' ').$condition;
350			}
351			}
352			}
353
354			$viewQuery = "SELECT ${selFields} FROM ${linkFields} ";
355
356			//add where from additional conditions
357			if (strlen($addConditions)) {
358			$viewQuery .= ' WHERE '.$addConditions;
359			}
360
361			try {
362			$status = $data->createView($_POST['formView'], $viewQuery, false, $_POST['formComment'], $is_materialized);
363			if (0 == $status) {
364			$this->misc->setReloadBrowser(true);
365
366			return $this->doDefault($this->lang['strviewcreated']);
367			}
368
369			return $this->doSetParamsCreate($this->lang['strviewcreatedbad']);
370			} catch (\PHPPgAdmin\ADOdbException $e) {
371			return $this->halt($e->getMessage());
372			}
373			}
374
375			public function printParamsCreateForm()
376			{
377			$data = $this->misc->getDatabaseAccessor();
378
379			$tblCount = sizeof($_POST['formTables']);
380			//unserialize our schema/table information and store in arrSelTables
381			for ($i = 0; $i < $tblCount; ++$i) {
382			$arrSelTables[] = unserialize($_POST['formTables'][$i]);
383			}
384
385			//get linking keys
386			$rsLinkKeys = $data->getLinkingKeys($arrSelTables);
			0 ignored issues – show Comprehensibility Best Practice introduced 2018-01-10 08:54 UTC by Report Bug Copy Issue Report The variable `$arrSelTables` does not seem to be defined for all execution paths leading up to this point. Loading history...
387			$linkCount = $rsLinkKeys->recordCount() > $tblCount ? $rsLinkKeys->recordCount() : $tblCount;
388
389			$arrFields = []; //array that will hold all our table/field names
390
391			//if we have schemas we need to specify the correct schema for each table we're retrieiving
392			//with getTableAttributes
393			$curSchema = $data->_schema;
394			for ($i = 0; $i < $tblCount; ++$i) {
395			if ($arrSelTables[$i]['schemaname'] != $data->_schema) {
396			$data->setSchema($arrSelTables[$i]['schemaname']);
397			}
398
399			$attrs = $data->getTableAttributes($arrSelTables[$i]['tablename']);
400			while (!$attrs->EOF) {
401			$arrFields["{$arrSelTables[$i]['schemaname']}.{$arrSelTables[$i]['tablename']}.{$attrs->fields['attname']}"] = serialize(
402			[
403			'schemaname' => $arrSelTables[$i]['schemaname'],
404			'tablename' => $arrSelTables[$i]['tablename'],
405			'fieldname' => $attrs->fields['attname'], ]
406			);
407			$attrs->moveNext();
408			}
409
410			$data->setSchema($curSchema);
411			}
412			asort($arrFields);
413
414			echo '<form action="'.\SUBFOLDER."/src/views/materializedviews\" method=\"post\">\n";
415			echo "<table>\n";
416			echo "<tr><th class=\"data\">{$this->lang['strviewname']}</th></tr>";
417			echo "<tr>\n<td class=\"data1\">\n";
418			// View name
419			echo '<input name="formView" value="', htmlspecialchars($_REQUEST['formView']), "\" size=\"32\" maxlength=\"{$data->_maxNameLen}\" />\n";
420			echo "</td>\n</tr>\n";
421			echo "<tr><th class=\"data\">{$this->lang['strcomment']}</th></tr>";
422			echo "<tr>\n<td class=\"data1\">\n";
423			// View comments
424			echo '<textarea name="formComment" rows="3" cols="32">',
425			htmlspecialchars($_REQUEST['formComment']), "</textarea>\n";
426			echo "</td>\n</tr>\n";
427			echo "</table>\n";
428
429			// Output selector for fields to be retrieved from view
430			echo "<table>\n";
431			echo "<tr><th class=\"data\">{$this->lang['strcolumns']}</th></tr>";
432			echo "<tr>\n<td class=\"data1\">\n";
433			echo \PHPPgAdmin\XHtml\HTMLController::printCombo($arrFields, 'formFields[]', false, '', true);
434			echo "</td>\n</tr>";
435			echo "<tr><td><input type=\"radio\" name=\"dblFldMeth\" id=\"dblFldMeth1\" value=\"rename\" /><label for=\"dblFldMeth1\">{$this->lang['strrenamedupfields']}</label>";
436			echo "<br /><input type=\"radio\" name=\"dblFldMeth\" id=\"dblFldMeth2\" value=\"drop\" /><label for=\"dblFldMeth2\">{$this->lang['strdropdupfields']}</label>";
437			echo "<br /><input type=\"radio\" name=\"dblFldMeth\" id=\"dblFldMeth3\" value=\"\" checked=\"checked\" /><label for=\"dblFldMeth3\">{$this->lang['strerrordupfields']}</label></td></tr></table><br />";
438
439			// Output the Linking keys combo boxes
440			echo "<table>\n";
441			echo "<tr><th class=\"data\">{$this->lang['strviewlink']}</th></tr>";
442			$rowClass = 'data1';
443			for ($i = 0; $i < $linkCount; ++$i) {
444			// Initialise variables
445			if (!isset($formLink[$i]['operator'])) {
446			$formLink[$i]['operator'] = 'INNER JOIN';
447			}
448
449			echo "<tr>\n<td class=\"${rowClass}\">\n";
450
451			if (!$rsLinkKeys->EOF) {
452			$curLeftLink = htmlspecialchars(serialize(['schemaname' => $rsLinkKeys->fields['p_schema'], 'tablename' => $rsLinkKeys->fields['p_table'], 'fieldname' => $rsLinkKeys->fields['p_field']]));
453			$curRightLink = htmlspecialchars(serialize(['schemaname' => $rsLinkKeys->fields['f_schema'], 'tablename' => $rsLinkKeys->fields['f_table'], 'fieldname' => $rsLinkKeys->fields['f_field']]));
454			$rsLinkKeys->moveNext();
455			} else {
456			$curLeftLink = '';
457			$curRightLink = '';
458			}
459
460			echo \PHPPgAdmin\XHtml\HTMLController::printCombo($arrFields, "formLink[${i}][leftlink]", true, $curLeftLink, false);
461			echo \PHPPgAdmin\XHtml\HTMLController::printCombo($data->joinOps, "formLink[${i}][operator]", true, $formLink[$i]['operator']);
			0 ignored issues – show Comprehensibility Best Practice introduced 2018-01-10 08:54 UTC by Report Bug Copy Issue Report The variable `$formLink` does not seem to be defined for all execution paths leading up to this point. Loading history...
462			echo \PHPPgAdmin\XHtml\HTMLController::printCombo($arrFields, "formLink[${i}][rightlink]", true, $curRightLink, false);
463			echo "</td>\n</tr>\n";
464			$rowClass = 'data1' == $rowClass ? 'data2' : 'data1';
465			}
466			echo "</table>\n<br />\n";
467
468			// Build list of available operators (infix only)
469			$arrOperators = [];
470			foreach ($data->selectOps as $k => $v) {
471			if ('i' == $v) {
472			$arrOperators[$k] = $k;
473			}
474			}
475
476			// Output additional conditions, note that this portion of the wizard treats the right hand side as literal values
477			//(not as database objects) so field names will be treated as strings, use the above linking keys section to perform joins
478			echo "<table>\n";
479			echo "<tr><th class=\"data\">{$this->lang['strviewconditions']}</th></tr>";
480			$rowClass = 'data1';
481			for ($i = 0; $i < $linkCount; ++$i) {
482			echo "<tr>\n<td class=\"${rowClass}\">\n";
483			echo \PHPPgAdmin\XHtml\HTMLController::printCombo($arrFields, "formCondition[${i}][field]");
484			echo \PHPPgAdmin\XHtml\HTMLController::printCombo($arrOperators, "formCondition[${i}][operator]", false, '', false);
485			echo "<input type=\"text\" name=\"formCondition[${i}][txt]\" />\n";
486			echo "</td>\n</tr>\n";
487			$rowClass = 'data1' == $rowClass ? 'data2' : 'data1';
488			}
489			echo "</table>\n";
490			echo "<p><input type=\"hidden\" name=\"action\" value=\"save_create_wiz\" />\n";
491
492			foreach ($arrSelTables as $curTable) {
493			echo '<input type="hidden" name="formTables[]" value="'.htmlspecialchars(serialize($curTable))."\" />\n";
494			}
495
496			echo $this->misc->form;
497			echo "<input type=\"submit\" value=\"{$this->lang['strcreate']}\" />\n";
498			echo "<input type=\"submit\" name=\"cancel\" value=\"{$this->lang['strcancel']}\" /></p>\n";
499			echo "</form>\n";
500			}
501
502			abstract public function doSetParamsCreate($msg = '');
503			}
504

HuasoFoundries / phpPgAdmin6

Push — develop ( 823958...5e1209 )

ViewsMatviewsTrait::_getArrLinks() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like