sql console in admin menu + other misc

10 files changed, 108 insertions, 4 deletions

diff --git a/README.md b/README.md
index 9fe8e2e..1020b35 100644
--- a/README.md
+++ b/README.md
@@ -7,7 +7,7 @@ protocol.
 
 you should have:
 
-* php >= 8.3 with the `pdo_pgsql` and `sockets` extensions
+* php >= 8.3 with the extensions: `dom`, `ctype`, `sockets`, `fileinfo`, `intl`, `mbstring`, `pdo_pgsql`, `openssl`
 * composer
 * a postgres server, database and user already set up
 * a beanstalkd server already running
diff --git a/locale/en_GB.json b/locale/en_GB.json
index 6e17c18..c3eaaf9 100644
--- a/locale/en_GB.json
+++ b/locale/en_GB.json
@@ -189,6 +189,11 @@
   "admin.plugins.download.url.label": "URL",
   "admin.plugins.download.url.placeholder": "https://plugins.example.net/plugin.phar",
   "admin.plugins.download.action": "Download",
+  "admin.sqlConsole.pageTitle": "SQL console",
+  "admin.sqlConsole.info": "Here you can execute SQL queries directly against Digitigrade's database. Be extremely careful!",
+  "admin.sqlConsole.query.label": "Query",
+  "admin.sqlConsole.query.action": "Execute",
+  "admin.sqlConsole.results.heading": "Results (%d)",
   "globalSettings.category.instance": "Instance settings",
   "globalSettings.category.about": "About this instance",
   "globalSettings.category.customisation": "Customisation",
diff --git a/routes/admin.php b/routes/admin.php
index 82981c9..20c6ee5 100644
--- a/routes/admin.php
+++ b/routes/admin.php
@@ -1,5 +1,6 @@
 <?php
 
+use Digitigrade\Db;
 use Digitigrade\GlobalSettings;
 use Digitigrade\HttpResponseStatus\NotFound;
 use Digitigrade\HttpResponseStatus\TemporaryRedirect;
@@ -58,4 +59,25 @@ Router::getInstance()->mount('/fragment/admin/plugins/download', function (array
         $success = true;
     }
     render_template('admin/plugin_download_form', ['success' => $success]);
+});
+
+Router::getInstance()->mount('/admin/sql-console', function (array $args) {
+    UserAccount::requireByCurrentSession()->requireAdmin();
+    render_template('admin/sql_console_page');
+});
+
+Router::getInstance()->mount('/fragment/admin/sql-console/query', function (array $args) {
+    UserAccount::requireByCurrentSession()->requireAdmin();
+    $results = null;
+    $error = null;
+    if (isset($_POST['query'])) {
+        try {
+            $pdo = Db::getInstance()->getPdo();
+            $query = $pdo->query($_POST['query'], PDO::FETCH_ASSOC);
+            $results = $query->fetchAll();
+        } catch (\PDOException $e) {
+            $error = $e;
+        }
+    }
+    render_template('admin/sql_console_form', ['results' => $results, 'error' => $error]);
 });
\ No newline at end of file
diff --git a/routes/auth.php b/routes/auth.php
index b03ee52..f18d4e7 100644
--- a/routes/auth.php
+++ b/routes/auth.php
@@ -20,7 +20,7 @@ Router::getInstance()->mount('/auth/main', function (array $args) {
                 throw new BadRequest('you need to specify the `target` GET parameter');
             }
             $domain = hostname_from_uri($_GET['target']);
-            $instance = Instance::findByHostname($domain);
+            $instance = Instance::findByHostname($domain, forceRefetch: true);
             if ($instance == null) {
                 throw new RuntimeException("i can't find your instance information, sorry");
             }
diff --git a/static/admin.css b/static/admin.css
index 441dd50..4bdaa21 100644
--- a/static/admin.css
+++ b/static/admin.css
@@ -33,13 +33,15 @@
   }
 }
 
-.pluginDownloadForm {
+.pluginDownloadForm,
+.sqlQueryForm {
   max-width: 100% !important;
   .row {
     grid-template-columns: auto max-content;
     align-items: end !important;
 
-    input[type="url"] {
+    input[type="url"],
+    #sqlQueryInput {
       width: 100%;
       margin-bottom: 0;
     }
diff --git a/static/icons.css b/static/icons.css
index 76dd360..b231faf 100644
--- a/static/icons.css
+++ b/static/icons.css
@@ -138,4 +138,7 @@
   &.person-off-outline {
     --svg: url("data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' width='24' height='24' viewBox='0 0 24 24'%3E%3Cpath fill='%23000' d='M19.775 22.625L17.15 20H4v-2.8q0-.85.438-1.562T5.6 14.55q1.125-.575 2.288-.925t2.362-.525L1.375 4.225L2.8 2.8l18.4 18.4zM6 18h9.15l-3-3H12q-1.4 0-2.775.338T6.5 16.35q-.225.125-.363.35T6 17.2zm12.4-3.45q.725.35 1.15 1.063T20 17.15l-3.35-3.35q.45.175.888.35t.862.4m-4.2-3.2l-1.475-1.475q.575-.225.925-.737T14 8q0-.825-.587-1.412T12 6q-.625 0-1.137.35t-.738.925L8.65 5.8q.575-.85 1.45-1.325T12 4q1.65 0 2.825 1.175T16 8q0 1.025-.475 1.9T14.2 11.35m.95 6.65H6zm-3.725-9.425'/%3E%3C/svg%3E");
   }
+  &.database-search-outline {
+    --svg: url("data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' width='24' height='24' viewBox='0 0 24 24'%3E%3Cpath fill='%23000' d='M11.8 21q-1.825-.025-3.437-.337t-2.8-.85t-1.875-1.263T3 17q0 .825.688 1.55t1.874 1.263t2.8.85T11.8 21m-1.775-5.1q-.75-.075-1.45-.2t-1.338-.3t-1.2-.388T5 14.526q.475.275 1.038.488t1.2.387t1.337.3t1.45.2M12 9.025q2.15 0 4.437-.65T19 7.025q-.275-.725-2.512-1.375T12 5q-2.275 0-4.462.638T5 7.025q.375.725 2.613 1.363T12 9.025m-1.525 9.9q.25.575.575 1.1t.75.975q-1.825-.025-3.438-.337t-2.8-.85t-1.875-1.263T3 17V7q0-.825.713-1.55T5.65 4.175t2.863-.862T12 3t3.488.313t2.862.862t1.938 1.275T21 7t-.712 1.55t-1.938 1.275t-2.863.863T12 11q-2.125 0-3.925-.375T5 9.525v2.525q1 .925 2.5 1.35t3.025.55q-.2.375-.325.863t-.175 1.087q-1.5-.175-2.788-.5T5 14.525V17q.35.625 1.925 1.175t3.55.75M21.6 23l-2.7-2.7q-.55.325-1.15.513T16.5 21q-1.875 0-3.187-1.312T12 16.5t1.313-3.187T16.5 12t3.188 1.313T21 16.5q0 .65-.187 1.25T20.3 18.9l2.7 2.7zm-5.1-4q1.05 0 1.775-.725T19 16.5t-.725-1.775T16.5 14t-1.775.725T14 16.5t.725 1.775T16.5 19'/%3E%3C/svg%3E");
+  }
 }
diff --git a/static/misc.css b/static/misc.css
index 8b619b9..7793ce0 100644
--- a/static/misc.css
+++ b/static/misc.css
@@ -183,6 +183,25 @@ details[open] > summary::before {
   --svg: url("data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' width='1em' height='1em' viewBox='0 0 24 24'%3E%3Cpath fill='%23000' d='M9 19v-2h6.6L4 5.4L5.4 4L17 15.6V9h2v10z'/%3E%3C/svg%3E");
 }
 
+table {
+  border-collapse: collapse;
+  td,
+  th {
+    border: var(--border);
+    margin: 0;
+    padding: var(--spacing-single);
+  }
+  th {
+    background-color: var(--clr-secondary);
+    color: var(--clr-foreground-on-secondary);
+  }
+}
+
+.tableContainer {
+  width: 100%;
+  overflow-x: auto;
+}
+
 .indentedContainer {
   display: block;
   margin: 0 var(--spacing-double);
diff --git a/templates/admin/overview_page.php b/templates/admin/overview_page.php
index d3325ef..6906e3c 100644
--- a/templates/admin/overview_page.php
+++ b/templates/admin/overview_page.php
@@ -8,6 +8,10 @@
             'icon' => 'extension-outline',
             'label' => __('admin.plugins.pageTitle'),
             'href' => '/admin/plugins'
+        ], [
+            'icon' => 'database-search-outline',
+            'label' => __('admin.sqlConsole.pageTitle'),
+            'href' => '/admin/sql-console'
         ]
     ]]);
 });
\ No newline at end of file
diff --git a/templates/admin/sql_console_form.php b/templates/admin/sql_console_form.php
new file mode 100644
index 0000000..3b27daa
--- /dev/null
+++ b/templates/admin/sql_console_form.php
@@ -0,0 +1,45 @@
+<form class="sqlQueryForm" hx-post="/fragment/admin/sql-console/query" hx-target="this" hx-swap="outerHTML"
+    hx-disabled-elt="find button">
+    <div class="row">
+        <div class="column">
+            <label for="sqlQueryInput"><?= __('admin.sqlConsole.query.label') ?></label>
+            <textarea id="sqlQueryInput" name="query" class="codeEdit"
+                autocomplete="off"><?= $_POST['query'] ?? '' ?></textarea>
+        </div>
+        <div class="column">
+            <button type="submit" class="primary"><?= __('admin.sqlConsole.query.action') ?></button>
+        </div>
+    </div>
+
+    <?php if (isset($error)) {
+        call_template('alertbox', ['variety' => 'error', 'message' => $error->getMessage()]);
+    } ?>
+
+    <?php if (isset($results)): ?>
+        <div class="row tableContainer">
+            <h3><?= __f('admin.sqlConsole.results.heading', count($results)) ?></h3>
+            <?php if (count($results) > 0): ?>
+                <div class="tableContainer">
+                    <table>
+                        <thead>
+                            <tr>
+                                <?php foreach ($results[0] as $header => $_): ?>
+                                    <th><?= htmlspecialchars($header) ?></th>
+                                <?php endforeach; ?>
+                            </tr>
+                        </thead>
+                        <tbody>
+                            <?php foreach ($results as $row): ?>
+                                <tr>
+                                    <?php foreach ($row as $value): ?>
+                                        <td><?= htmlspecialchars($value); ?></td>
+                                    <?php endforeach; ?>
+                                </tr>
+                            <?php endforeach; ?>
+                        </tbody>
+                    </table>
+                </div>
+            <?php endif; ?>
+        </div>
+    <?php endif; ?>
+</form>
\ No newline at end of file
diff --git a/templates/admin/sql_console_page.php b/templates/admin/sql_console_page.php
new file mode 100644
index 0000000..110e3ab
--- /dev/null
+++ b/templates/admin/sql_console_page.php
@@ -0,0 +1,4 @@
+<?php call_template('skeleton', ['pageTitle' => __('admin.sqlConsole.pageTitle')], function () {
+    call_template('alertbox', ['variety' => 'info', 'message' => __('admin.sqlConsole.info')]);
+    call_template('admin/sql_console_form');
+});
\ No newline at end of file