path: root/src/traits/ViewsMatviewsTrait.php

<?php

/**
 * PHPPgAdmin 6.0.0
 */

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="' . \containerInstance()->subFolder . '/src/views/' . $this->script . '" method="post" id="selectform">';
            echo \PHP_EOL;

            if (0 < $attrs->recordCount()) {
                // JavaScript for select all feature
                echo '<script type="text/javascript">' . \PHP_EOL;
                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 '//]]>' . \PHP_EOL;
                echo '</script>' . \PHP_EOL;

                echo '<table>' . \PHP_EOL;

                // 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}\">" . \PHP_EOL;
                    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']}]\">" . \PHP_EOL;

                    foreach (\array_keys($data->selectOps) as $v) {
                        echo '<option value="', \htmlspecialchars($v), '"', ($_REQUEST['ops'][$attrs->fields['attname']] === $v) ? ' selected="selected"' : '',
                        '>', \htmlspecialchars($v), '</option>' . \PHP_EOL;
                    }
                    echo '</select></td>' . \PHP_EOL;
                    echo '<td style="white-space:nowrap;">';
                    echo $data->printField(
                        "values[{$attrs->fields['attname']}]",
                        $_REQUEST['values'][$attrs->fields['attname']],
                        $attrs->fields['type']
                    );
                    echo '</td></tr>' . \PHP_EOL;
                    ++$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>' . \PHP_EOL;
            } else {
                echo "<p>{$this->lang['strinvalidparam']}</p>" . \PHP_EOL;
            }

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

            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 === \count($_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(): void
    {
        $data = $this->misc->getDatabaseAccessor();

        $tables = $data->getAllTables();

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

        $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>" . \PHP_EOL;
        echo '</table>' . \PHP_EOL;
        echo '<p><input type="hidden" name="action" value="set_params_create" />' . \PHP_EOL;
        echo $this->view->form;
        echo "<input type=\"submit\" value=\"{$this->lang['strnext']}\" />" . \PHP_EOL;
        echo \sprintf('<input type="submit" name="cancel" value="%s"  /></p>%s', $this->lang['strcancel'], \PHP_EOL);
        echo '</form>' . \PHP_EOL;
    }

    /**
     * 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 (!\mb_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 = \mb_substr($selFields, 0, -2);
        unset($arrTmp, $tmpHsh);
        $linkFields = '';
        $arrJoined = [];
        $arrUsedTbls = [];

        [$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 .= \mb_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 (!\mb_strlen($linkFields)) {
            foreach ($_POST['formTables'] as $curTable) {
                $arrTmp = \unserialize($curTable);
                $data->fieldArrayClean($arrTmp);
                $linkFields .= (\mb_strlen($linkFields) ? ', ' : ' ') . "\"{$arrTmp['schemaname']}\".\"{$arrTmp['tablename']}\"";
            }
        }

        $addConditions = '';

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

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

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

        try {
            $status = $data->createView($_POST['formView'], $viewQuery, false, $_POST['formComment'], $is_materialized);

            if (0 === $status) {
                $this->view->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(): void
    {
        $data = $this->misc->getDatabaseAccessor();

        $tblCount = \count($_POST['formTables']);
        $arrSelTables = [];
        //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="' . \containerInstance()->subFolder . '/src/views/materializedviews" method="post">' . \PHP_EOL;
        echo '<table>' . \PHP_EOL;
        echo "<tr><th class=\"data\">{$this->lang['strviewname']}</th></tr>";
        echo "<tr>\n<td class=\"data1\">" . \PHP_EOL;
        // View name
        echo '<input name="formView" value="' . \htmlspecialchars($_REQUEST['formView']) . "\" size=\"32\" maxlength=\"{$data->_maxNameLen}\" />" . \PHP_EOL;
        echo "</td>\n</tr>" . \PHP_EOL;
        echo "<tr><th class=\"data\">{$this->lang['strcomment']}</th></tr>";
        echo "<tr>\n<td class=\"data1\">" . \PHP_EOL;
        // View comments
        echo '<textarea name="formComment" rows="3" cols="32">' . \htmlspecialchars($_REQUEST['formComment']) . '</textarea>' . \PHP_EOL;
        echo "</td>\n</tr>" . \PHP_EOL;
        echo '</table>' . \PHP_EOL;

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

        // Output the Linking keys combo boxes
        echo '<table>' . \PHP_EOL;
        echo "<tr><th class=\"data\">{$this->lang['strviewlink']}</th></tr>";
        $rowClass = 'data1';
        $formLink = [];

        for ($i = 0; $i < $linkCount; ++$i) {
            // Initialise variables
            $formLink[$i] = $formLink[$i] ?? [];
            $this->coalesceArr($formLink[$i], 'operator', 'INNER JOIN');

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

            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>" . \PHP_EOL;
            $rowClass = 'data1' === $rowClass ? 'data2' : 'data1';
        }
        echo "</table>\n<br />" . \PHP_EOL;

        // 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>' . \PHP_EOL;
        echo "<tr><th class=\"data\">{$this->lang['strviewconditions']}</th></tr>";
        $rowClass = 'data1';

        for ($i = 0; $i < $linkCount; ++$i) {
            echo "<tr>\n<td class=\"{$rowClass}\">" . \PHP_EOL;
            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]\" />" . \PHP_EOL;
            echo "</td>\n</tr>" . \PHP_EOL;
            $rowClass = 'data1' === $rowClass ? 'data2' : 'data1';
        }
        echo '</table>' . \PHP_EOL;
        echo '<p><input type="hidden" name="action" value="save_create_wiz" />' . \PHP_EOL;

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

        echo $this->view->form;
        echo "<input type=\"submit\" value=\"{$this->lang['strcreate']}\" />" . \PHP_EOL;
        echo \sprintf('<input type="submit" name="cancel" value="%s"  /></p>%s', $this->lang['strcancel'], \PHP_EOL);
        echo '</form>' . \PHP_EOL;
    }

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

    /**
     * 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): void
    {
        $field_arr = [$arrTmp['schemaname'], $arrTmp['tablename'], $arrTmp['fieldname']];

        $field_element = '"' . \implode('"."', $field_arr) . '"';

        if (empty($_POST['dblFldMeth'])) {
            // no doublon control
            $selFields .= $field_element . ', ';
        } 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 (\mb_strlen($curLink['leftlink']) && \mb_strlen($curLink['rightlink']) && \mb_strlen($curLink['operator'])) {
                    $arrLinks[] = $curLink;
                }
            }
            // We must perform some magic to make sure that we have a valid join order
            $count = \count($arrLinks);
        }

        return [$arrLinks, $count];
    }
}