diff options
Diffstat (limited to 'src/Traits/ViewsMatviewsTrait.php')
| -rw-r--r-- | src/Traits/ViewsMatviewsTrait.php | 524 |
1 files changed, 524 insertions, 0 deletions
diff --git a/src/Traits/ViewsMatviewsTrait.php b/src/Traits/ViewsMatviewsTrait.php new file mode 100644 index 00000000..7c148768 --- /dev/null +++ b/src/Traits/ViewsMatviewsTrait.php @@ -0,0 +1,524 @@ +<?php + +/** + * PHPPgAdmin6 + */ + +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'), + $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\Core\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 + } + + /** + * @return (array|int)[] + * + * @psalm-return array{0: list<mixed>, 1: 0|positive-int} + */ + 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]; + } +} |