Compare commits

..
Author SHA1 Message Date
james.maes 6029174344 Merge branch 'develop' into feature/record-screen-polish 2026-10-12 16:06:11 -05:00
james.maes 1ffc663ba5 Merge pull request #6 from QRun-IO/feature/developer-panel
feat: SQL Workbench record developer panel
2026-10-12 16:05:24 -05:00
james.maes 777eb3b95b Merge branch 'develop' into feature/developer-panel 2026-10-12 16:03:18 -05:00
james.maes 7f21e46a2f Merge pull request #15 from QRun-IO/feature/ga-only-release-tags
ci: publish only GA tags (vX.Y.Z), never RC tags
2026-10-12 16:01:09 -05:00
james.maesandClaude Opus 5.5 dbdb5eb113 ci: publish only GA tags (vX.Y.Z), never RC tags
An RC tag matched /v.*/ and the orb republished it as the next RC.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-12 15:58:14 -05:00
james.maesandClaude Opus 5.5 dd4bd63b2b fix(screens): plain child-list labels, backend and cell-type pickers, read-only keys and JSON
From the kof22 record-screen audit:
- notebook child lists are labelled Cells, Shares, Snapshots and Versions
  instead of raw join names
- notebook defaultBackendName and cell backendName use a possible-value source
  of the backends the workbench can target (invalid values are rejected)
- cell cellType uses a SQL/MARKDOWN enum possible-value source
- cell cellKey is read-only
- execution parametersJson shows as read-only JSON

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-12 15:53:17 -05:00
james.maesandClaude Opus 5.5 c36a0e8c94 Merge develop into feature/developer-panel; open SQL in the Runner
Brings the snake_case rename (#7) and the app home and Runner (#9).
The developer panel's "open as a new cell" links now target the Runner
app (SqlWorkbenchRunnerAppMetaDataProducer.APP_NAME, prefixed), since
/app/sqlWorkbench is the home dashboard. Tests and README updated; the
changelog entry moves under 1.0.0-RC.3.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-12 15:31:22 -05:00
james.maes 72647623fa Merge pull request #9 from QRun-IO/feature/app-home-runner-nav
feat: app home dashboard, Runner nav entry, main tables only (1.0.0-RC.3)
2026-10-12 15:14:21 -05:00
james.maesandClaude Opus 5.5 ad8f9001a6 ci: rebuild now that QQQ 4.1.0-RC.4 is on Maven Central
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-12 12:25:22 -05:00
james.maesandClaude Opus 5.5 e0d9312aff feat: SQL Workbench record developer panel
Add SqlWorkbenchDeveloperPanelProvider (id sqlWorkbench, order 450, icon
terminal), registered by SqlWorkbenchDeveloperPanelsMetaDataProducer via
DeveloperPanelsMetaData. For tables on an allowed RDBMS backend, and users
with the workbench access permission, it shows read-only SELECTs for the
record and its table in the backend's own table/column names (dialect
quoted identifiers, escaped primary-key literal), backend details, and
links that open each query in the workbench (?sql=&backend=, not run).

Build against QQQ 4.1.0-RC.4 (the developer panel contract).

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-12 10:38:51 -05:00
21 changed files with 1063 additions and 28 deletions

No files matched your search

+1 -1
View File
@@ -57,7 +57,7 @@ workflows:
branches:
ignore: /.*/
tags:
only: [/v.*/]
only: [/^v\d+\.\d+\.\d+$/]
publish_hotfix_release:
jobs:
+2 -1
View File
@@ -44,6 +44,7 @@ How tests are split:
- `SqlWorkbenchIntegrations`: opt-in ESB and Quick Search hooks for the main tables.
- `customizers/`: table customizers holding the notebook invariants for every entry point; `SqlWorkbenchSecurity` puts the lock keys in sessions.
- `processes/`: the record-action steps (Share, Duplicate, Restore Version).
- `developer/`: `SqlWorkbenchDeveloperPanelProvider` (the "SQL Workbench" record developer panel, registered by `metadata/SqlWorkbenchDeveloperPanelsMetaDataProducer`) and `RecordSql` (read-only SELECTs in backend names, dialect quoting and literals).
**Invariants (do not break):**
- Never log SQL text, parameters, or results. Only the audit table holds them.
@@ -64,7 +65,7 @@ How tests are split:
## Dependencies
- Java 21.
- Parent `com.kingsrook:qbit-build-parent:2.0.0` (qqq 4.0.0). The default-active `qqq-snapshot` profile imports `qqq-bom-pom:4.1.0-RC.2` ahead of it, as in qbit-quick-search (ADR-0007). Remove that activation when the parent moves to 4.1.
- Parent `com.kingsrook:qbit-build-parent:2.0.0` (qqq 4.0.0). The default-active `qqq-snapshot` profile imports `qqq-bom-pom:4.1.0-RC.4` ahead of it (RC.4 brings the record developer panel contract), as in qbit-quick-search (ADR-0007). Remove that activation when the parent moves to 4.1.
- `qqq-backend-core`, `qqq-backend-module-rdbms`, and `qqq-middleware-javalin` (optional).
- Tests: JUnit 5, AssertJ, Mockito, H2, `qqq-backend-module-sqlite`/`-postgres` (drivers), Testcontainers 1.21.4.
+1
View File
@@ -9,6 +9,7 @@ All notable changes to this project are documented here. The format follows Keep
- **Runner.** The notebook editor (`SQL_WORKBENCH` widget) moves to its own nav entry: the `sqlWorkbenchRunner` child app, labeled Runner. Open a notebook with `/app/sqlWorkbenchRunner?notebook=<id>`.
- On a notebook's record view: an Open in Runner widget, and a Runs child list (its executions the viewer may see) through the new join `sql_workbench_notebook_join_sql_workbench_execution`.
- `SqlWorkbenchIntegrations`: opt-in ESB (`esbTableNames`) and Quick Search (`quickSearchTables`) hooks for the main tables, with no dependency on either module.
- **Record developer panel** "SQL Workbench" (`SqlWorkbenchDeveloperPanelProvider`, registered by `SqlWorkbenchDeveloperPanelsMetaDataProducer`): read-only `SELECT`s for the record and its table in the backend's own names, backend details, and links that open them in the Runner as a new cell (`/app/sqlWorkbenchRunner?sql=&backend=`, never run). Requires QQQ 4.1.0-RC.4 (the developer panel contract) and a Next UI with the Runner `?sql=` support.
### Changed
- **The nav lists only the main tables**: Notebooks and the execution audit, after the Runner. Cells, versions, pinned results and shares are child lists on a notebook (still standard tables, reachable by URL and API). The app's widget list is now the dashboard, not the `SQL_WORKBENCH` widget; links to `/app/sqlWorkbench` that expected the editor should point at `/app/sqlWorkbenchRunner`.
+2 -1
View File
@@ -44,6 +44,7 @@ How tests are split:
- `SqlWorkbenchIntegrations`: opt-in ESB and Quick Search hooks for the main tables.
- `customizers/`: table customizers holding the notebook invariants for every entry point; `SqlWorkbenchSecurity` puts the lock keys in sessions.
- `processes/`: the record-action steps (Share, Duplicate, Restore Version).
- `developer/`: `SqlWorkbenchDeveloperPanelProvider` (the "SQL Workbench" record developer panel, registered by `metadata/SqlWorkbenchDeveloperPanelsMetaDataProducer`) and `RecordSql` (read-only SELECTs in backend names, dialect quoting and literals).
**Invariants (do not break):**
- Never log SQL text, parameters, or results. Only the audit table holds them.
@@ -64,7 +65,7 @@ How tests are split:
## Dependencies
- Java 21.
- Parent `com.kingsrook:qbit-build-parent:2.0.0` (qqq 4.0.0). The default-active `qqq-snapshot` profile imports `qqq-bom-pom:4.1.0-RC.2` ahead of it, as in qbit-quick-search (ADR-0007). Remove that activation when the parent moves to 4.1.
- Parent `com.kingsrook:qbit-build-parent:2.0.0` (qqq 4.0.0). The default-active `qqq-snapshot` profile imports `qqq-bom-pom:4.1.0-RC.4` ahead of it (RC.4 brings the record developer panel contract), as in qbit-quick-search (ADR-0007). Remove that activation when the parent moves to 4.1.
- `qqq-backend-core`, `qqq-backend-module-rdbms`, and `qqq-middleware-javalin` (optional).
- Tests: JUnit 5, AssertJ, Mockito, H2, `qqq-backend-module-sqlite`/`-postgres` (drivers), Testcontainers 1.21.4.
+10
View File
@@ -142,6 +142,16 @@ A session without the access permission gets no keys, so it finds no workbench r
`writeModeEnabled=false` turns write mode off for everyone.
## Record developer panel
With QQQ 4.1.0-RC.4 or later, the qbit registers a **SQL Workbench** panel for a record's developer view (`/app/{table}/{id}/dev` in the Next UI). It shows for tables on an RDBMS backend the workbench targets (honoring `allowedBackendNames` / `excludedBackendNames`), and only to users with `sql_workbench.hasAccess`. It contains:
- A read-only `SELECT` for the record and one for the table (`LIMIT 100`), in the backend's own table and column names (aliased to the QQQ field names), with identifiers quoted and the primary key written as an escaped literal for the dialect.
- The backend, dialect, database table, and whether read-only sessions use the backend's read-only replica.
- Links that open each query in the workbench (`/app/sqlWorkbenchRunner?sql=...&backend=...`, the Runner, with the table name prefix applied). The Runner opens the SQL as a new cell and does not run it.
The panel only builds text. What runs is what the user executes, under their workbench permissions; the write permission does not change the panel.
## Administering through the tables
Everything the Runner does can be done and inspected through the tables. Notebooks and the execution audit are in the SQL Workbench app's nav; the others open from a notebook's record view (or by URL). The same customizers run either way.
+4 -3
View File
@@ -250,9 +250,10 @@
<profiles>
<!-- Built against qqq 4.1 (QBitMetaDataProducer, runtime services, Javalin 7 -->
<!-- routes). Until qbit-build-parent 2.1.0 imports the released 4.1 BOM, -->
<!-- this profile is active by default and imports qqq-bom-pom 4.1.0-RC.4 -->
<!-- ahead of the parent's 4.0.0 BOM (same as qbit-quick-search, ADR-0007). -->
<!-- routes; the record developer panel contract needs 4.1.0-RC.4). Until -->
<!-- qbit-build-parent 2.1.0 imports the released 4.1 BOM, this profile is -->
<!-- active by default and imports qqq-bom-pom 4.1.0-RC.4 ahead of the -->
<!-- parent's 4.0.0 BOM (same as qbit-quick-search, ADR-0007). -->
<profile>
<id>qqq-snapshot</id>
<activation>
@@ -202,7 +202,7 @@ public class CellTableCustomizer extends WorkbenchTableCustomizer
/*******************************************************************************
** Type, content and key checks.
*******************************************************************************/
private static void validate(SqlWorkbenchQBitConfig config, QRecord record, boolean inserting)
private static void validate(SqlWorkbenchQBitConfig config, QRecord record, boolean inserting) throws QException
{
if(inserting || record.getValues().containsKey("cellType"))
{
@@ -216,6 +216,7 @@ public class CellTableCustomizer extends WorkbenchTableCustomizer
record.setValue("cellType", type);
}
validateBackend(record, "backendName");
if(inserting && record.getValue("content") == null)
{
record.setValue("content", "");
@@ -262,7 +262,7 @@ public class NotebookTableCustomizer extends WorkbenchTableCustomizer
/*******************************************************************************
** Title (required on insert, never blank) and parameters (a JSON list).
*******************************************************************************/
private static void validateContent(QRecord record, boolean inserting)
private static void validateContent(QRecord record, boolean inserting) throws QException
{
if(inserting || record.getValues().containsKey("title"))
{
@@ -277,6 +277,7 @@ public class NotebookTableCustomizer extends WorkbenchTableCustomizer
}
}
validateBackend(record, "defaultBackendName");
String parameters = record.getValueString("parametersJson");
if(StringUtils.hasContent(parameters))
{
@@ -27,11 +27,14 @@ import java.util.Set;
import com.kingsrook.qbits.sqlworkbench.SqlWorkbenchQBitConfig;
import com.kingsrook.qbits.sqlworkbench.SqlWorkbenchRuntime;
import com.kingsrook.qbits.sqlworkbench.SqlWorkbenchSecurity;
import com.kingsrook.qbits.sqlworkbench.engine.WorkbenchBackends;
import com.kingsrook.qbits.sqlworkbench.notebook.NotebookVersioning;
import com.kingsrook.qqq.backend.core.actions.QBackendTransaction;
import com.kingsrook.qqq.backend.core.actions.customizers.AbstractPreInsertCustomizer;
import com.kingsrook.qqq.backend.core.actions.customizers.TableCustomizerInterface;
import com.kingsrook.qqq.backend.core.context.QContext;
import com.kingsrook.qqq.backend.core.exceptions.QException;
import com.kingsrook.qqq.backend.core.exceptions.QNotFoundException;
import com.kingsrook.qqq.backend.core.logging.QLogger;
import com.kingsrook.qqq.backend.core.model.actions.tables.delete.DeleteInput;
import com.kingsrook.qqq.backend.core.model.actions.tables.insert.InsertInput;
@@ -278,4 +281,26 @@ public abstract class WorkbenchTableCustomizer implements TableCustomizerInterfa
{
return (SqlWorkbenchSecurity.isSystemUser());
}
/*******************************************************************************
** Optional backend selections must name an allowed workbench target.
*******************************************************************************/
protected static void validateBackend(QRecord record, String fieldName) throws QException
{
String name = record.getValueString(fieldName);
if(name != null)
{
try
{
new WorkbenchBackends(QContext.getQInstance(), SqlWorkbenchRuntime.get()).getTarget(name);
}
catch(QNotFoundException e)
{
badInput(record, "Select an available SQL Workbench backend.");
}
}
}
}
@@ -0,0 +1,160 @@
/*
* Copyright 2026 Kingsrook, LLC
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.kingsrook.qbits.sqlworkbench.developer;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData;
import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldType;
import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
import com.kingsrook.qbits.sqlworkbench.sql.SqlDialect;
import com.kingsrook.qqq.backend.module.rdbms.actions.AbstractRDBMSAction;
/*******************************************************************************
** Builds read-only SELECT statements for a QQQ table in its RDBMS backend's
** own names (the table's backend table name and each field's backend column
** name, as QQQ's RDBMS module resolves them), with identifiers quoted and
** the primary-key value written as an escaped literal for the dialect.
**
** The SQL is only shown to the user (and opened, not run, in the
** workbench), so it uses literals rather than parameters.
*******************************************************************************/
public final class RecordSql
{
public static final int TABLE_ROW_LIMIT = 100;
/*******************************************************************************
**
*******************************************************************************/
private RecordSql()
{
}
/*******************************************************************************
** SELECT the table's columns for one primary-key value.
*******************************************************************************/
public static String selectRecord(QTableMetaData table, SqlDialect dialect, Serializable primaryKey)
{
QFieldMetaData primaryKeyField = table.getField(table.getPrimaryKeyField());
return (selectColumns(table, dialect)
+ "\nWHERE " + quoteIdentifier(dialect, AbstractRDBMSAction.getColumnName(primaryKeyField)) + " = " + literal(dialect, primaryKeyField.getType(), primaryKey));
}
/*******************************************************************************
** SELECT the table's columns, first rows only.
*******************************************************************************/
public static String selectTable(QTableMetaData table, SqlDialect dialect)
{
String select = selectColumns(table, dialect);
if(dialect == SqlDialect.GENERIC)
{
return (select + "\nFETCH FIRST " + TABLE_ROW_LIMIT + " ROWS ONLY");
}
return (select + "\nLIMIT " + TABLE_ROW_LIMIT);
}
/*******************************************************************************
** SELECT clause listing every field's column (aliased to the QQQ field
** name where the names differ) and the FROM clause.
*******************************************************************************/
static String selectColumns(QTableMetaData table, SqlDialect dialect)
{
List<String> columns = new ArrayList<>();
for(QFieldMetaData field : table.getFields().values())
{
String column = AbstractRDBMSAction.getColumnName(field);
columns.add(column.equals(field.getName())
? quoteIdentifier(dialect, column)
: quoteIdentifier(dialect, column) + " AS " + quoteIdentifier(dialect, field.getName()));
}
return ("SELECT\n " + String.join(",\n ", columns)
+ "\nFROM " + quoteIdentifier(dialect, AbstractRDBMSAction.getTableName(table)));
}
/*******************************************************************************
** Quote an identifier: backticks for MySQL, double quotes otherwise (ANSI;
** Postgres, SQLite and H2 all accept them). A quote character inside the
** identifier is doubled.
*******************************************************************************/
public static String quoteIdentifier(SqlDialect dialect, String identifier)
{
String quote = dialect == SqlDialect.MYSQL ? "`" : "\"";
return (quote + identifier.replace(quote, quote + quote) + quote);
}
/*******************************************************************************
** A value as a SQL literal: NULL; a plain number for numeric fields whose
** value parses as one; otherwise a single-quoted string with quotes
** doubled (and, for MySQL, backslashes escaped).
*******************************************************************************/
public static String literal(SqlDialect dialect, QFieldType type, Serializable value)
{
if(value == null)
{
return ("NULL");
}
if(type == QFieldType.INTEGER || type == QFieldType.LONG || type == QFieldType.DECIMAL || value instanceof Number)
{
try
{
return (new BigDecimal(value.toString().trim()).toPlainString());
}
catch(NumberFormatException e)
{
///////////////////////////////////////////////////////
// not a number after all: fall through to a string //
///////////////////////////////////////////////////////
}
}
return (stringLiteral(dialect, value.toString()));
}
/*******************************************************************************
** A single-quoted string literal.
*******************************************************************************/
public static String stringLiteral(SqlDialect dialect, String value)
{
String escaped = value;
if(dialect.isBackslashEscapes())
{
escaped = escaped.replace("\\", "\\\\");
}
return ("'" + escaped.replace("'", "''") + "'");
}
}
@@ -0,0 +1,269 @@
/*
* Copyright 2026 Kingsrook, LLC
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.kingsrook.qbits.sqlworkbench.developer;
import java.io.Serializable;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import com.kingsrook.qqq.backend.core.context.QContext;
import com.kingsrook.qqq.backend.core.exceptions.QException;
import com.kingsrook.qqq.backend.core.exceptions.QNotFoundException;
import com.kingsrook.qqq.backend.core.model.developer.DeveloperPanel;
import com.kingsrook.qqq.backend.core.model.developer.DeveloperPanelBlock;
import com.kingsrook.qqq.backend.core.model.developer.DeveloperPanelContext;
import com.kingsrook.qqq.backend.core.model.developer.DeveloperPanelLink;
import com.kingsrook.qqq.backend.core.model.developer.DeveloperPanelProviderInterface;
import com.kingsrook.qqq.backend.core.model.developer.DeveloperPanelTone;
import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
import com.kingsrook.qqq.backend.core.model.session.QSession;
import com.kingsrook.qqq.backend.core.utils.StringUtils;
import com.kingsrook.qbits.sqlworkbench.SqlWorkbenchQBitConfig;
import com.kingsrook.qbits.sqlworkbench.SqlWorkbenchRuntime;
import com.kingsrook.qbits.sqlworkbench.engine.WorkbenchBackends;
import com.kingsrook.qbits.sqlworkbench.metadata.SqlWorkbenchAppMetaDataProducer;
import com.kingsrook.qbits.sqlworkbench.metadata.SqlWorkbenchRunnerAppMetaDataProducer;
import com.kingsrook.qqq.backend.module.rdbms.actions.AbstractRDBMSAction;
/*******************************************************************************
** Record developer panel "SQL Workbench": read-only SELECTs for the record
** (and its table) in the backend's own table and column names, with links
** that open them in the workbench as new cells (never run).
**
** Available when the workbench is in the instance (its runtime and app), the
** user has the workbench access permission (the same check its endpoints
** make), and the table's backend is an RDBMS backend the workbench allows.
** The panel only builds text: nothing is executed, and the user's write
** permission makes no difference.
*******************************************************************************/
public class SqlWorkbenchDeveloperPanelProvider implements DeveloperPanelProviderInterface
{
public static final String ID = "sqlWorkbench";
/*******************************************************************************
**
*******************************************************************************/
@Override
public String getId()
{
return (ID);
}
/*******************************************************************************
**
*******************************************************************************/
@Override
public String getTitle()
{
return ("SQL Workbench");
}
/*******************************************************************************
**
*******************************************************************************/
@Override
public Integer getOrder()
{
return (450);
}
/*******************************************************************************
**
*******************************************************************************/
@Override
public String getIcon()
{
return ("terminal");
}
/*******************************************************************************
** Available for a table on an allowed RDBMS backend, to users with the
** workbench access permission, when the workbench app exists.
*******************************************************************************/
@Override
public boolean isAvailable(DeveloperPanelContext context)
{
return (resolve(context) != null);
}
/*******************************************************************************
**
*******************************************************************************/
@Override
public DeveloperPanel build(DeveloperPanelContext context) throws QException
{
Resolved resolved = resolve(context);
if(resolved == null)
{
throw (new QNotFoundException("The SQL Workbench is not available for this table."));
}
QTableMetaData table = context.getTable();
WorkbenchBackends.Target target = resolved.target();
String runnerApp = resolved.config().applyPrefix(SqlWorkbenchRunnerAppMetaDataProducer.APP_NAME);
Serializable primaryKey = context.getPrimaryKey();
DeveloperPanel panel = new DeveloperPanel()
.withDescription("Read-only SQL for this record on the " + target.name() + " backend, in the database's own table and column names.")
.withStatusLabel("Read-only")
.withStatusTone(DeveloperPanelTone.INFO);
DeveloperPanelBlock links = DeveloperPanelBlock.links("Open in SQL Workbench");
if(primaryKey != null)
{
String recordSql = RecordSql.selectRecord(table, target.dialect(), primaryKey);
panel.withBlock(DeveloperPanelBlock.code("Read-only query for this record", "sql", recordSql));
links.withLink(new DeveloperPanelLink()
.withLabel("Open in SQL Workbench")
.withHref(workbenchHref(runnerApp, recordSql, target.name()))
.withDescription("This record's query, as a new cell (not run)"));
}
String tableSql = RecordSql.selectTable(table, target.dialect());
panel.withBlock(DeveloperPanelBlock.code("Read-only query for this table (first " + RecordSql.TABLE_ROW_LIMIT + " rows)", "sql", tableSql));
links.withLink(new DeveloperPanelLink()
.withLabel(primaryKey == null ? "Open in SQL Workbench" : "Open table query in SQL Workbench")
.withHref(workbenchHref(runnerApp, tableSql, target.name()))
.withDescription("The table's query, as a new cell (not run)"));
panel.withBlock(DeveloperPanelBlock.keyValues("Backend")
.withItem("Backend", target.name())
.withItem("Dialect", target.dialect().getUiName())
.withItem("Database table", AbstractRDBMSAction.getTableName(table))
.withItem("Read-only replica", describeReadOnlySource(resolved)));
panel.withBlock(links);
panel.withBlock(DeveloperPanelBlock.text("The workbench runs only what you execute, under your SQL Workbench permissions. "
+ "These queries are SELECTs; opening them does not run them.", DeveloperPanelTone.INFO));
return (panel);
}
/*******************************************************************************
** The Runner page with the SQL to open (as a new cell, never run) and its
** backend.
*******************************************************************************/
static String workbenchHref(String appName, String sql, String backendName)
{
return ("/app/" + encode(appName) + "?sql=" + encode(sql) + "&backend=" + encode(backendName));
}
/*******************************************************************************
** URL-encode a query parameter (spaces as %20, not '+').
*******************************************************************************/
static String encode(String value)
{
return (URLEncoder.encode(value, StandardCharsets.UTF_8).replace("+", "%20"));
}
/*******************************************************************************
** Where read-only workbench sessions on the target connect, as
** WorkbenchBackends.connectionSource resolves it.
*******************************************************************************/
private String describeReadOnlySource(Resolved resolved)
{
if(resolved.backends().hasReadOnlyCredentials(resolved.target()))
{
return ("No (dedicated read-only credentials)");
}
if(Boolean.TRUE.equals(resolved.config().getUseBackendReadOnlyReplica()) && resolved.target().backend().getReadOnlyBackendMetaData() != null)
{
return ("Yes (" + resolved.target().backend().getReadOnlyBackendMetaData().getName() + ")");
}
return ("No");
}
/*******************************************************************************
** The workbench config and target for the context's table, or null when
** the panel is not available.
*******************************************************************************/
private Resolved resolve(DeveloperPanelContext context)
{
QInstance qInstance = QContext.getQInstance();
QSession session = QContext.getQSession();
if(qInstance == null || session == null || context == null || context.getTable() == null)
{
return (null);
}
QTableMetaData table = context.getTable();
if(!StringUtils.hasContent(table.getBackendName()) || !StringUtils.hasContent(table.getPrimaryKeyField()) || table.getField(table.getPrimaryKeyField()) == null)
{
return (null);
}
SqlWorkbenchRuntime runtime;
try
{
runtime = SqlWorkbenchRuntime.get(qInstance);
}
catch(QNotFoundException e)
{
return (null);
}
SqlWorkbenchQBitConfig config = runtime.getConfig();
if(qInstance.getApp(config.applyPrefix(SqlWorkbenchAppMetaDataProducer.APP_NAME)) == null || !session.hasPermission(config.getAccessPermissionName()))
{
return (null);
}
WorkbenchBackends backends = new WorkbenchBackends(qInstance, runtime);
try
{
return (new Resolved(config, backends, backends.getTarget(table.getBackendName())));
}
catch(QNotFoundException e)
{
return (null);
}
}
/*******************************************************************************
** The workbench config, backends and target for a table.
*******************************************************************************/
private record Resolved(SqlWorkbenchQBitConfig config, WorkbenchBackends backends, WorkbenchBackends.Target target)
{
}
}
@@ -0,0 +1,47 @@
/*
* Copyright 2026 Kingsrook, LLC
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.kingsrook.qbits.sqlworkbench.metadata;
import com.kingsrook.qqq.backend.core.exceptions.QException;
import com.kingsrook.qqq.backend.core.model.developer.DeveloperPanelsMetaData;
import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
import com.kingsrook.qqq.backend.core.model.metadata.code.QCodeReference;
import com.kingsrook.qqq.backend.core.model.metadata.qbits.QBitComponentMetaDataProducer;
import com.kingsrook.qbits.sqlworkbench.SqlWorkbenchQBitConfig;
import com.kingsrook.qbits.sqlworkbench.developer.SqlWorkbenchDeveloperPanelProvider;
/*******************************************************************************
** Registers the "SQL Workbench" record developer panel
** ({@link SqlWorkbenchDeveloperPanelProvider}) in the instance's developer
** panels (idempotent). The provider decides per table and user whether it
** shows.
*******************************************************************************/
public class SqlWorkbenchDeveloperPanelsMetaDataProducer extends QBitComponentMetaDataProducer<DeveloperPanelsMetaData, SqlWorkbenchQBitConfig>
{
/*******************************************************************************
**
*******************************************************************************/
@Override
public DeveloperPanelsMetaData produce(QInstance qInstance) throws QException
{
return (DeveloperPanelsMetaData.ofOrWithNew(qInstance).withProvider(new QCodeReference(SqlWorkbenchDeveloperPanelProvider.class)));
}
}
@@ -19,11 +19,16 @@ package com.kingsrook.qbits.sqlworkbench.metadata;
import com.kingsrook.qbits.sqlworkbench.SqlWorkbenchQBitConfig;
import com.kingsrook.qbits.sqlworkbench.SqlWorkbenchSecurity;
import com.kingsrook.qbits.sqlworkbench.model.CellType;
import com.kingsrook.qqq.backend.core.exceptions.QException;
import com.kingsrook.qqq.backend.core.model.metadata.MetaDataProducerMultiOutput;
import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
import com.kingsrook.qqq.backend.core.model.metadata.code.QCodeReference;
import com.kingsrook.qqq.backend.core.model.metadata.dashboard.QWidgetMetaDataInterface;
import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldType;
import com.kingsrook.qqq.backend.core.model.metadata.joins.QJoinMetaData;
import com.kingsrook.qqq.backend.core.model.metadata.possiblevalues.QPossibleValueSource;
import com.kingsrook.qqq.backend.core.model.metadata.possiblevalues.QPossibleValueSourceType;
import com.kingsrook.qqq.backend.core.model.metadata.qbits.QBitComponentMetaDataProducer;
import com.kingsrook.qqq.backend.core.model.metadata.security.QSecurityKeyType;
@@ -49,6 +54,10 @@ public class SqlWorkbenchSecurityAndJoinsMetaDataProducer extends QBitComponentM
output.add(keyType);
}
output.add(SqlWorkbenchTableMetaDataHelper.buildAudienceTypePossibleValueSource(config));
output.add(QPossibleValueSource.newForEnum(config.applyPrefix(CellType.NAME), CellType.values()));
output.add(new QPossibleValueSource().withName(config.applyPrefix(WorkbenchBackendPossibleValueProvider.NAME))
.withType(QPossibleValueSourceType.CUSTOM).withIdType(QFieldType.STRING)
.withCustomCodeReference(new QCodeReference(WorkbenchBackendPossibleValueProvider.class)));
for(QJoinMetaData join : SqlWorkbenchTableMetaDataHelper.buildJoins(config))
{
output.add(join);
@@ -27,6 +27,7 @@ import com.kingsrook.qbits.sqlworkbench.customizers.CellTableCustomizer;
import com.kingsrook.qbits.sqlworkbench.customizers.NotebookTableCustomizer;
import com.kingsrook.qbits.sqlworkbench.customizers.ShareTableCustomizer;
import com.kingsrook.qbits.sqlworkbench.customizers.SnapshotTableCustomizer;
import com.kingsrook.qbits.sqlworkbench.model.CellType;
import com.kingsrook.qbits.sqlworkbench.model.ShareAudienceType;
import com.kingsrook.qbits.sqlworkbench.model.SqlWorkbenchCell;
import com.kingsrook.qbits.sqlworkbench.model.SqlWorkbenchExecution;
@@ -113,11 +114,12 @@ public class SqlWorkbenchTableMetaDataHelper
.withSection(new QFieldSection("runner", "Runner", new QIcon("play_circle"), Tier.T2, null).withWidgetName(config.applyPrefix(SqlWorkbenchHomeWidgetsMetaDataProducer.NOTEBOOK_RUNNER)))
.withSection(new QFieldSection("details", new QIcon("description"), Tier.T2, List.of("defaultBackendName", "parametersJson")))
.withSection(widgetSection(config, JOIN_NOTEBOOK_CELL, "Cells", "notes"))
.withSection(widgetSection(config, JOIN_NOTEBOOK_SHARE, "Shared With", "share"))
.withSection(widgetSection(config, JOIN_NOTEBOOK_SNAPSHOT, "Pinned Results", "push_pin"))
.withSection(widgetSection(config, JOIN_NOTEBOOK_SHARE, "Shares", "share"))
.withSection(widgetSection(config, JOIN_NOTEBOOK_SNAPSHOT, "Snapshots (pinned results)", "push_pin"))
.withSection(widgetSection(config, JOIN_NOTEBOOK_VERSION, "Versions", "history"))
.withSection(widgetSection(config, JOIN_NOTEBOOK_EXECUTION, "Runs", "policy"))
.withSection(new QFieldSection("system", new QIcon("calendar_month"), Tier.T3, List.of("ownerUserId", "currentVersion", "createDate", "modifyDate")));
table.getField("defaultBackendName").setPossibleValueSourceName(config.applyPrefix(WorkbenchBackendPossibleValueProvider.NAME));
table.getField("visibility").setIsEditable(false);
table.getField("visibility").setIsRequired(false);
table.getField("visibility").setDefaultValue("PERSONAL");
@@ -142,9 +144,12 @@ public class SqlWorkbenchTableMetaDataHelper
.withSection(new QFieldSection("identity", new QIcon("badge"), Tier.T1, List.of("id", "notebookId", "sortOrder", "cellType", "title")))
.withSection(new QFieldSection("content", new QIcon("code"), Tier.T2, List.of("content", "backendName")))
.withSection(new QFieldSection("system", new QIcon("calendar_month"), Tier.T3, List.of("cellKey", "createDate", "modifyDate")));
table.getField("backendName").setPossibleValueSourceName(config.applyPrefix(WorkbenchBackendPossibleValueProvider.NAME));
table.getField("cellKey").setIsEditable(false);
table.getField("cellKey").setIsRequired(false);
table.getField("cellType").setPossibleValueSourceName(config.applyPrefix(CellType.NAME));
table.getField("cellType").setIsRequired(false);
table.getField("cellType").setDefaultValue("SQL");
table.getField("cellType").setDefaultValue(CellType.SQL.getPossibleValueId());
table.getField("content").withFieldAdornment(new FieldAdornment(AdornmentType.CODE_EDITOR).withValue(AdornmentType.CodeEditorValues.languageMode("sql")));
return (customize(linkNotebook(config, table), CellTableCustomizer.class));
}
@@ -233,6 +238,8 @@ public class SqlWorkbenchTableMetaDataHelper
.withSection(new QFieldSection("system", new QIcon("calendar_month"), Tier.T3, List.of("createDate")));
table.getField("sqlText").withFieldAdornment(new FieldAdornment(AdornmentType.CODE_EDITOR).withValue(AdornmentType.CodeEditorValues.languageMode("sql")));
table.getField("notebookId").setPossibleValueSourceName(config.applyPrefix(SqlWorkbenchNotebook.TABLE_NAME));
table.getField("parametersJson").withIsEditable(false)
.withFieldAdornment(new FieldAdornment(AdornmentType.CODE_EDITOR).withValue(AdornmentType.CodeEditorValues.languageMode("json")));
appendOnly(table);
return (table);
}
@@ -329,11 +336,11 @@ public class SqlWorkbenchTableMetaDataHelper
.withOnlyIncludeFieldNames(List.of("id", "sortOrder", "cellType", "title", "backendName", "modifyDate"))
.getWidgetMetaData());
widgets.add(ChildRecordListRenderer.widgetMetaDataBuilder(joins.get(config.applyPrefix(JOIN_NOTEBOOK_SHARE)))
.withLabel("Shared With").withCanAddChildRecord(true)
.withLabel("Shares").withCanAddChildRecord(true)
.withOnlyIncludeFieldNames(List.of("id", "audienceType", "audienceId", "createdByName", "createDate"))
.getWidgetMetaData());
widgets.add(ChildRecordListRenderer.widgetMetaDataBuilder(joins.get(config.applyPrefix(JOIN_NOTEBOOK_SNAPSHOT)))
.withLabel("Pinned Results")
.withLabel("Snapshots (pinned results)")
.withOnlyIncludeFieldNames(List.of("id", "label", "cellKey", "rowCount", "truncated", "createDate"))
.getWidgetMetaData());
widgets.add(ChildRecordListRenderer.widgetMetaDataBuilder(joins.get(config.applyPrefix(JOIN_NOTEBOOK_VERSION)))
@@ -0,0 +1,69 @@
/*
* Copyright 2026 Kingsrook, LLC
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.kingsrook.qbits.sqlworkbench.metadata;
import java.io.Serializable;
import java.util.List;
import com.kingsrook.qbits.sqlworkbench.SqlWorkbenchRuntime;
import com.kingsrook.qbits.sqlworkbench.engine.WorkbenchBackends;
import com.kingsrook.qqq.backend.core.actions.values.BasicCustomPossibleValueProvider;
import com.kingsrook.qqq.backend.core.context.QContext;
import com.kingsrook.qqq.backend.core.exceptions.QException;
import com.kingsrook.qqq.backend.core.model.metadata.possiblevalues.QPossibleValue;
/*******************************************************************************
** Backend choices use the same live allow/deny filtering as the Runner.
*******************************************************************************/
public class WorkbenchBackendPossibleValueProvider extends BasicCustomPossibleValueProvider<WorkbenchBackends.Target, String>
{
public static final String NAME = "sql_workbench_backend";
/*******************************************************************************
**
*******************************************************************************/
@Override
protected QPossibleValue<String> makePossibleValue(WorkbenchBackends.Target target)
{
return (new QPossibleValue<>(target.name(), target.name()));
}
/*******************************************************************************
** Unknown or excluded stored values have no selectable translation.
*******************************************************************************/
@Override
protected WorkbenchBackends.Target getSourceObject(Serializable id) throws QException
{
return (getAllSourceObjects().stream().filter(target -> target.name().equals(id)).findFirst().orElse(null));
}
/*******************************************************************************
**
*******************************************************************************/
@Override
protected List<WorkbenchBackends.Target> getAllSourceObjects() throws QException
{
return (new WorkbenchBackends(QContext.getQInstance(), SqlWorkbenchRuntime.get()).listTargets());
}
}
@@ -0,0 +1,54 @@
/*
* Copyright 2026 Kingsrook, LLC
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.kingsrook.qbits.sqlworkbench.model;
import com.kingsrook.qqq.backend.core.model.metadata.possiblevalues.PossibleValueEnum;
/*******************************************************************************
** The supported notebook cell types.
*******************************************************************************/
public enum CellType implements PossibleValueEnum<String>
{
SQL,
MARKDOWN;
public static final String NAME = "sql_workbench_cell_type";
/*******************************************************************************
**
*******************************************************************************/
@Override
public String getPossibleValueId()
{
return (name());
}
/*******************************************************************************
**
*******************************************************************************/
@Override
public String getPossibleValueLabel()
{
return (this == SQL ? "SQL" : "Markdown");
}
}
@@ -36,6 +36,7 @@ import com.kingsrook.qbits.sqlworkbench.engine.Caller;
import com.kingsrook.qbits.sqlworkbench.engine.CollectedResult;
import com.kingsrook.qbits.sqlworkbench.engine.ColumnInfo;
import com.kingsrook.qbits.sqlworkbench.engine.ResultCache;
import com.kingsrook.qbits.sqlworkbench.model.CellType;
import com.kingsrook.qbits.sqlworkbench.model.ShareAudienceType;
import com.kingsrook.qbits.sqlworkbench.model.SqlWorkbenchCell;
import com.kingsrook.qbits.sqlworkbench.model.SqlWorkbenchNotebook;
@@ -89,8 +90,8 @@ public class NotebookService
{
public static final String VISIBILITY_PERSONAL = "PERSONAL";
public static final String VISIBILITY_SHARED = "SHARED";
public static final String CELL_SQL = "SQL";
public static final String CELL_MARKDOWN = "MARKDOWN";
public static final String CELL_SQL = CellType.SQL.getPossibleValueId();
public static final String CELL_MARKDOWN = CellType.MARKDOWN.getPossibleValueId();
public static final int MAX_CELLS = 200;
private final SqlWorkbenchRuntime runtime;
@@ -20,21 +20,13 @@ package com.kingsrook.qbits.sqlworkbench;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import com.kingsrook.qqq.backend.core.exceptions.QException;
import com.kingsrook.qqq.backend.core.model.metadata.QBackendMetaData;
import com.kingsrook.qqq.backend.core.context.QContext;
import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
import com.kingsrook.qqq.backend.core.model.metadata.dashboard.QWidgetMetaData;
import com.kingsrook.qqq.backend.core.model.metadata.layout.QAppMetaData;
import com.kingsrook.qqq.backend.core.model.metadata.permissions.PermissionLevel;
import com.kingsrook.qqq.backend.core.model.metadata.processes.QProcessMetaData;
import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
import com.kingsrook.qqq.backend.core.modules.backend.implementations.memory.MemoryBackendModule;
import com.kingsrook.qbits.sqlworkbench.metadata.SqlWorkbenchAppMetaDataProducer;
import com.kingsrook.qbits.sqlworkbench.metadata.SqlWorkbenchHomeWidgetsMetaDataProducer;
import com.kingsrook.qbits.sqlworkbench.metadata.SqlWorkbenchRunnerAppMetaDataProducer;
import com.kingsrook.qbits.sqlworkbench.metadata.SqlWorkbenchWidgetMetaDataProducer;
import com.kingsrook.qbits.sqlworkbench.metadata.SqlWorkbenchWidgetRenderer;
import com.kingsrook.qbits.sqlworkbench.metadata.WorkbenchBackendPossibleValueProvider;
import com.kingsrook.qbits.sqlworkbench.model.CellType;
import com.kingsrook.qbits.sqlworkbench.model.ShareAudienceType;
import com.kingsrook.qbits.sqlworkbench.model.SqlWorkbenchCell;
import com.kingsrook.qbits.sqlworkbench.model.SqlWorkbenchExecution;
@@ -42,6 +34,18 @@ import com.kingsrook.qbits.sqlworkbench.model.SqlWorkbenchNotebook;
import com.kingsrook.qbits.sqlworkbench.model.SqlWorkbenchNotebookShare;
import com.kingsrook.qbits.sqlworkbench.model.SqlWorkbenchNotebookVersion;
import com.kingsrook.qbits.sqlworkbench.model.SqlWorkbenchResultSnapshot;
import com.kingsrook.qqq.backend.core.context.QContext;
import com.kingsrook.qqq.backend.core.exceptions.QException;
import com.kingsrook.qqq.backend.core.model.actions.values.SearchPossibleValueSourceInput;
import com.kingsrook.qqq.backend.core.model.metadata.QBackendMetaData;
import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
import com.kingsrook.qqq.backend.core.model.metadata.dashboard.QWidgetMetaData;
import com.kingsrook.qqq.backend.core.model.metadata.fields.AdornmentType;
import com.kingsrook.qqq.backend.core.model.metadata.layout.QAppMetaData;
import com.kingsrook.qqq.backend.core.model.metadata.permissions.PermissionLevel;
import com.kingsrook.qqq.backend.core.model.metadata.processes.QProcessMetaData;
import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
import com.kingsrook.qqq.backend.core.modules.backend.implementations.memory.MemoryBackendModule;
import com.kingsrook.qqq.backend.module.rdbms.model.metadata.RDBMSTableBackendDetails;
import com.kingsrook.qqq.middleware.javalin.QJavalinMetaData;
import org.junit.jupiter.api.Test;
@@ -108,7 +112,8 @@ class SqlWorkbenchQBitProducerTest extends BaseSqlWorkbenchTest
QContext.clear();
assertThat(com.kingsrook.qqq.backend.core.actions.permissions.PermissionsHelper.getAllAvailablePermissionNames(qInstance))
.contains("sql_workbench_notebook.read", "sql_workbench_notebook_share.insert", "sql_workbench.hasAccess")
.doesNotContain("sql_workbench_execution.delete", "sql_workbench_notebook_version.insert");
.doesNotContain("sql_workbench_execution.insert", "sql_workbench_execution.edit", "sql_workbench_execution.delete",
"sql_workbench_notebook_version.insert", "sql_workbench_notebook_version.edit", "sql_workbench_result_snapshot.insert");
loginAs(READER);
QWidgetMetaData widget = (QWidgetMetaData) qInstance.getWidget(SqlWorkbenchWidgetMetaDataProducer.NAME);
@@ -155,6 +160,10 @@ class SqlWorkbenchQBitProducerTest extends BaseSqlWorkbenchTest
assertThat(instance.getTable("ns_sql_workbench_notebook")).isNotNull();
assertThat(instance.getTable("ns_sql_workbench_cell").getField("notebookId").getPossibleValueSourceName()).isEqualTo("ns_sql_workbench_notebook");
assertThat(instance.getApp("ns_sqlWorkbench")).isNotNull();
assertThat(instance.getTable("ns_sql_workbench_cell").getField("backendName").getPossibleValueSourceName()).isEqualTo("ns_sql_workbench_backend");
assertThat(instance.getPossibleValueSource("ns_sql_workbench_backend")).isNotNull();
assertThat(instance.getTable("ns_sql_workbench_cell").getField("cellType").getPossibleValueSourceName()).isEqualTo("ns_sql_workbench_cell_type");
assertThat(instance.getPossibleValueSource("ns_sql_workbench_cell_type")).isNotNull();
assertThat(QJavalinMetaData.of(instance)).isNull();
SqlWorkbenchRuntime first = prefixed.getRuntime();
@@ -227,6 +236,70 @@ class SqlWorkbenchQBitProducerTest extends BaseSqlWorkbenchTest
/*******************************************************************************
** Record screens expose readable sections and constrained editor fields.
*******************************************************************************/
@Test
void testRecordScreenMetaData() throws Exception
{
QTableMetaData notebook = qInstance.getTable(SqlWorkbenchNotebook.TABLE_NAME);
QTableMetaData cell = qInstance.getTable(SqlWorkbenchCell.TABLE_NAME);
assertThat(notebook.getSections()).filteredOn(section -> section.getWidgetName() != null)
.extracting(section -> section.getLabel())
.containsExactly("Runner", "Cells", "Shares", "Snapshots (pinned results)", "Versions", "Runs");
for(var section : notebook.getSections())
{
if(section.getWidgetName() != null && !"runner".equals(section.getName()))
{
assertThat(qInstance.getWidget(section.getWidgetName()).getLabel()).isEqualTo(section.getLabel());
}
}
String backendPvs = WorkbenchBackendPossibleValueProvider.NAME;
assertThat(notebook.getField("defaultBackendName").getPossibleValueSourceName()).isEqualTo(backendPvs);
assertThat(cell.getField("backendName").getPossibleValueSourceName()).isEqualTo(backendPvs);
assertThat(qInstance.getPossibleValueSource(backendPvs).getType().name()).isEqualTo("CUSTOM");
assertThat(cell.getField("cellKey").getIsEditable()).isFalse();
assertThat(qInstance.getTable(SqlWorkbenchResultSnapshot.TABLE_NAME).getField("label").getIsEditable()).isTrue();
assertThat(qInstance.getTable(SqlWorkbenchResultSnapshot.TABLE_NAME).getField("resultJson").getIsEditable()).isFalse();
assertThat(cell.getField("cellType").getDefaultValue()).isEqualTo("SQL");
assertThat(cell.getField("cellType").getPossibleValueSourceName()).isEqualTo(CellType.NAME);
assertThat(qInstance.getPossibleValueSource(CellType.NAME).getEnumValues())
.extracting(value -> value.getId().toString()).containsExactly("SQL", "MARKDOWN");
for(String table : List.of(SqlWorkbenchNotebook.TABLE_NAME, SqlWorkbenchExecution.TABLE_NAME))
{
var field = qInstance.getTable(table).getField("parametersJson");
assertThat(field.getAdornment(AdornmentType.CODE_EDITOR).orElseThrow().getValues())
.containsEntry("languageMode", "json");
assertThat(field.getIsEditable()).isEqualTo(table.equals(SqlWorkbenchNotebook.TABLE_NAME));
}
}
/*******************************************************************************
** The live backend PVS honors all execution target exclusions.
*******************************************************************************/
@Test
void testBackendChoices() throws Exception
{
qInstance.addBackend(defineH2Backend("other"));
qInstance.addBackend(defineH2Backend("excluded"));
qInstance.addBackend(defineH2Backend("h2__sqlWorkbenchReadOnly"));
qInstance.addBackend(defineH2Backend("h2__sqlWorkbenchWrite"));
qInstance.addBackend(new QBackendMetaData().withName("mem").withBackendType(MemoryBackendModule.class));
config.setExcludedBackendNames(List.of("excluded"));
var provider = new WorkbenchBackendPossibleValueProvider();
var input = new SearchPossibleValueSourceInput();
assertThat(provider.search(input)).extracting(value -> value.getId()).containsExactlyInAnyOrder(BACKEND_NAME, "other");
assertThat(provider.getPossibleValue(BACKEND_NAME).getLabel()).isEqualTo(BACKEND_NAME);
assertThat(provider.getPossibleValue("excluded")).isNull();
assertThat(provider.getPossibleValue("unknown")).isNull();
config.setAllowedBackendNames(List.of(BACKEND_NAME, "excluded"));
assertThat(provider.search(input)).extracting(value -> value.getId()).containsExactly(BACKEND_NAME);
}
/*******************************************************************************
**
*******************************************************************************/
@@ -0,0 +1,271 @@
/*
* Copyright 2026 Kingsrook, LLC
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.kingsrook.qbits.sqlworkbench.developer;
import java.net.URLDecoder;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import com.kingsrook.qqq.backend.core.actions.developer.DeveloperPanelsAction;
import com.kingsrook.qqq.backend.core.model.data.QRecord;
import com.kingsrook.qqq.backend.core.model.developer.DeveloperPanel;
import com.kingsrook.qqq.backend.core.model.developer.DeveloperPanelBlock;
import com.kingsrook.qqq.backend.core.model.developer.DeveloperPanelBlockType;
import com.kingsrook.qqq.backend.core.model.developer.DeveloperPanelContext;
import com.kingsrook.qqq.backend.core.model.developer.DeveloperPanelLink;
import com.kingsrook.qqq.backend.core.model.developer.DeveloperPanelsMetaData;
import com.kingsrook.qqq.backend.core.model.metadata.QBackendMetaData;
import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData;
import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldType;
import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
import com.kingsrook.qbits.sqlworkbench.BaseSqlWorkbenchTest;
import com.kingsrook.qbits.sqlworkbench.sql.SqlDialect;
import com.kingsrook.qqq.backend.module.rdbms.jdbc.ConnectionManager;
import com.kingsrook.qqq.backend.module.rdbms.model.metadata.RDBMSBackendMetaData;
import com.kingsrook.qqq.backend.module.rdbms.model.metadata.RDBMSTableBackendDetails;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import static org.assertj.core.api.Assertions.assertThat;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertNull;
import static org.junit.jupiter.api.Assertions.assertTrue;
/*******************************************************************************
** Unit test for {@link SqlWorkbenchDeveloperPanelProvider} and
** {@link RecordSql}.
*******************************************************************************/
class SqlWorkbenchDeveloperPanelProviderTest extends BaseSqlWorkbenchTest
{
private static final String TABLE_NAME = "orderLine";
private static final String SKU = "AB'C-1";
/*******************************************************************************
** A table whose backend names differ from its QQQ names, with a string
** primary key, plus a table on a non-RDBMS backend.
*******************************************************************************/
@BeforeEach
void setUp() throws Exception
{
qInstance.addTable(new QTableMetaData()
.withName(TABLE_NAME)
.withBackendName(BACKEND_NAME)
.withPrimaryKeyField("sku")
.withBackendDetails(new RDBMSTableBackendDetails().withTableName("order_line"))
.withField(new QFieldMetaData("sku", QFieldType.STRING).withBackendName("line_sku"))
.withField(new QFieldMetaData("quantity", QFieldType.INTEGER))
.withField(new QFieldMetaData("unitPrice", QFieldType.DECIMAL).withBackendName("unit_price")));
qInstance.addBackend(new QBackendMetaData().withName("memory"));
qInstance.addTable(new QTableMetaData()
.withName("memoryThing")
.withBackendName("memory")
.withPrimaryKeyField("id")
.withField(new QFieldMetaData("id", QFieldType.INTEGER)));
try(Connection connection = ConnectionManager.getConnection((RDBMSBackendMetaData) qInstance.getBackend(BACKEND_NAME)); Statement statement = connection.createStatement())
{
statement.execute("DROP TABLE IF EXISTS order_line");
statement.execute("CREATE TABLE order_line (line_sku VARCHAR(40) PRIMARY KEY, quantity INT, unit_price DECIMAL(10,2))");
statement.execute("INSERT INTO order_line VALUES ('AB''C-1', 3, 9.50), ('other', 1, 1.00)");
}
}
/*******************************************************************************
**
*******************************************************************************/
private DeveloperPanelContext context(String tableName, Object primaryKey)
{
QTableMetaData table = qInstance.getTable(tableName);
return (new DeveloperPanelContext().withTable(table).withRecord(new QRecord().withValue(table.getPrimaryKeyField(), (java.io.Serializable) primaryKey)));
}
/*******************************************************************************
** The producer registers the provider; the panel shows through the
** framework action with the record and table queries, backend details,
** links and the note.
*******************************************************************************/
@Test
void testPanel() throws Exception
{
assertThat(DeveloperPanelsMetaData.of(qInstance).getProviders())
.anyMatch(reference -> SqlWorkbenchDeveloperPanelProvider.class.getName().equals(reference.getName()));
DeveloperPanel panel = new DeveloperPanelsAction().execute(context(TABLE_NAME, SKU)).stream()
.filter(p -> SqlWorkbenchDeveloperPanelProvider.ID.equals(p.getId())).findFirst().orElseThrow();
assertNull(panel.getError());
assertEquals("SQL Workbench", panel.getTitle());
assertEquals(450, panel.getOrder());
assertEquals("terminal", panel.getIcon());
List<DeveloperPanelBlock> code = panel.getBlocks().stream().filter(b -> b.getType() == DeveloperPanelBlockType.CODE).toList();
assertEquals(2, code.size());
assertEquals("sql", code.get(0).getLanguage());
assertEquals("""
SELECT
"line_sku" AS "sku",
"quantity",
"unit_price" AS "unitPrice"
FROM "order_line"
WHERE "line_sku" = 'AB''C-1'""", code.get(0).getCode());
assertEquals("""
SELECT
"line_sku" AS "sku",
"quantity",
"unit_price" AS "unitPrice"
FROM "order_line"
LIMIT 100""", code.get(1).getCode());
////////////////////////////////////////////////////////////
// the generated SQL runs on the backend and finds the row //
////////////////////////////////////////////////////////////
try(Connection connection = ConnectionManager.getConnection((RDBMSBackendMetaData) qInstance.getBackend(BACKEND_NAME)); Statement statement = connection.createStatement())
{
try(ResultSet resultSet = statement.executeQuery(code.get(0).getCode()))
{
assertTrue(resultSet.next());
assertEquals(SKU, resultSet.getString("sku"));
assertEquals(3, resultSet.getInt("quantity"));
assertFalse(resultSet.next());
}
try(ResultSet resultSet = statement.executeQuery(code.get(1).getCode()))
{
int rows = 0;
while(resultSet.next())
{
rows++;
}
assertEquals(2, rows);
}
}
DeveloperPanelBlock keyValues = panel.getBlocks().stream().filter(b -> b.getType() == DeveloperPanelBlockType.KEY_VALUES).findFirst().orElseThrow();
assertEquals(BACKEND_NAME, keyValues.getItems().get(0).getValue());
assertEquals("h2", keyValues.getItems().get(1).getValue());
assertEquals("order_line", keyValues.getItems().get(2).getValue());
assertEquals("No", keyValues.getItems().get(3).getValue());
DeveloperPanelBlock links = panel.getBlocks().stream().filter(b -> b.getType() == DeveloperPanelBlockType.LINKS).findFirst().orElseThrow();
assertEquals(2, links.getLinks().size());
DeveloperPanelLink recordLink = links.getLinks().get(0);
assertThat(recordLink.getHref()).startsWith("/app/sqlWorkbenchRunner?sql=").endsWith("&backend=h2").doesNotContain(" ", "+", "'", "\n");
String sqlParam = recordLink.getHref().substring("/app/sqlWorkbenchRunner?sql=".length(), recordLink.getHref().indexOf("&backend="));
assertEquals(code.get(0).getCode(), URLDecoder.decode(sqlParam, StandardCharsets.UTF_8));
assertThat(links.getLinks().get(1).getHref()).contains("LIMIT%20100");
assertTrue(panel.getBlocks().stream().anyMatch(b -> b.getType() == DeveloperPanelBlockType.TEXT && b.getText().contains("runs only what you execute")));
}
/*******************************************************************************
** A prefixed workbench links to its prefixed Runner; the replica is reported.
*******************************************************************************/
@Test
void testPrefixAndReplica() throws Exception
{
config.setTableNamePrefix("ops");
qInstance.getApp("sqlWorkbench").setName("ops_sqlWorkbench");
qInstance.getApps().put("ops_sqlWorkbench", qInstance.getApps().remove("sqlWorkbench"));
config.setUseBackendReadOnlyReplica(true);
((RDBMSBackendMetaData) qInstance.getBackend(BACKEND_NAME)).setReadOnlyBackendMetaData(defineH2Backend("h2Replica"));
DeveloperPanel panel = new SqlWorkbenchDeveloperPanelProvider().build(context("person", 7));
String href = panel.getBlocks().stream().filter(b -> b.getType() == DeveloperPanelBlockType.LINKS).findFirst().orElseThrow().getLinks().get(0).getHref();
assertThat(href).startsWith("/app/ops_sqlWorkbenchRunner?sql=");
assertThat(panel.getBlocks().get(0).getCode()).endsWith("WHERE \"id\" = 7");
DeveloperPanelBlock keyValues = panel.getBlocks().stream().filter(b -> b.getType() == DeveloperPanelBlockType.KEY_VALUES).findFirst().orElseThrow();
assertEquals("Yes (h2Replica)", keyValues.getItems().get(3).getValue());
}
/*******************************************************************************
** Writers get the same (SELECT-only) panel.
*******************************************************************************/
@Test
void testWriterSameQueries() throws Exception
{
String readerSql = new SqlWorkbenchDeveloperPanelProvider().build(context(TABLE_NAME, SKU)).getBlocks().get(0).getCode();
loginAs(WRITER);
assertEquals(readerSql, new SqlWorkbenchDeveloperPanelProvider().build(context(TABLE_NAME, SKU)).getBlocks().get(0).getCode());
}
/*******************************************************************************
** Not available without the access permission, for a non-RDBMS backend,
** for an excluded or not-allowed backend, or without the app.
*******************************************************************************/
@Test
void testNotAvailable()
{
SqlWorkbenchDeveloperPanelProvider provider = new SqlWorkbenchDeveloperPanelProvider();
assertTrue(provider.isAvailable(context(TABLE_NAME, SKU)));
loginAs("nobody-1");
assertFalse(provider.isAvailable(context(TABLE_NAME, SKU)));
assertTrue(new DeveloperPanelsAction().execute(context(TABLE_NAME, SKU)).stream().noneMatch(p -> SqlWorkbenchDeveloperPanelProvider.ID.equals(p.getId())));
loginAs(READER);
assertFalse(provider.isAvailable(context("memoryThing", 1)));
config.setExcludedBackendNames(List.of(BACKEND_NAME));
assertFalse(provider.isAvailable(context(TABLE_NAME, SKU)));
config.setExcludedBackendNames(null);
config.setAllowedBackendNames(List.of("someOtherBackend"));
assertFalse(provider.isAvailable(context(TABLE_NAME, SKU)));
config.setAllowedBackendNames(null);
qInstance.getApps().remove("sqlWorkbench");
assertFalse(provider.isAvailable(context(TABLE_NAME, SKU)));
}
/*******************************************************************************
** Identifier quoting and literals per dialect.
*******************************************************************************/
@Test
void testQuotingAndLiterals()
{
assertEquals("`my``col`", RecordSql.quoteIdentifier(SqlDialect.MYSQL, "my`col"));
assertEquals("\"my\"\"col\"", RecordSql.quoteIdentifier(SqlDialect.POSTGRES, "my\"col"));
assertEquals("'a''b\\c'", RecordSql.stringLiteral(SqlDialect.POSTGRES, "a'b\\c"));
assertEquals("'a''b\\\\c'", RecordSql.stringLiteral(SqlDialect.MYSQL, "a'b\\c"));
assertEquals("42", RecordSql.literal(SqlDialect.POSTGRES, QFieldType.INTEGER, 42));
assertEquals("12.50", RecordSql.literal(SqlDialect.POSTGRES, QFieldType.DECIMAL, new java.math.BigDecimal("12.50")));
assertEquals("'1; DROP TABLE x'", RecordSql.literal(SqlDialect.POSTGRES, QFieldType.INTEGER, "1; DROP TABLE x"));
assertEquals("'42'", RecordSql.literal(SqlDialect.POSTGRES, QFieldType.STRING, "42"));
assertEquals("NULL", RecordSql.literal(SqlDialect.POSTGRES, QFieldType.STRING, null));
assertThat(RecordSql.selectTable(qInstance.getTable("person"), SqlDialect.GENERIC)).endsWith("FETCH FIRST 100 ROWS ONLY");
assertThat(RecordSql.selectTable(qInstance.getTable("person"), SqlDialect.MYSQL)).contains("`first_name` AS `firstName`").endsWith("LIMIT 100");
assertEquals("%27a%20b%27%26c%3D1", SqlWorkbenchDeveloperPanelProvider.encode("'a b'&c=1"));
}
}
@@ -118,7 +118,7 @@ class AppNavigationTest extends BaseSqlWorkbenchTest
assertThat(qInstance.getJoin(SqlWorkbenchTableMetaDataHelper.JOIN_NOTEBOOK_EXECUTION).getRightTable()).isEqualTo(SqlWorkbenchExecution.TABLE_NAME);
assertThat(qInstance.getWidget(SqlWorkbenchTableMetaDataHelper.JOIN_NOTEBOOK_EXECUTION).getLabel()).isEqualTo("Runs");
assertThat(notebook.getSections().stream().filter(s -> s.getWidgetName() != null).map(QFieldSection::getLabel).toList())
.containsExactly("Runner", "Cells", "Shared With", "Pinned Results", "Versions", "Runs");
.containsExactly("Runner", "Cells", "Shares", "Snapshots (pinned results)", "Versions", "Runs");
assertThat(notebook.getSections().get(0).getFieldNames()).containsExactly("id", "title", "description", "ownerName", "visibility");
}
@@ -53,6 +53,7 @@ import com.kingsrook.qqq.backend.core.exceptions.QNotFoundException;
import com.kingsrook.qqq.backend.core.exceptions.QPermissionDeniedException;
import com.kingsrook.qqq.backend.core.model.actions.processes.RunProcessInput;
import com.kingsrook.qqq.backend.core.model.actions.processes.RunProcessOutput;
import com.kingsrook.qqq.backend.core.model.actions.reporting.ReportFormat;
import com.kingsrook.qqq.backend.core.model.actions.tables.count.CountInput;
import com.kingsrook.qqq.backend.core.model.actions.tables.delete.DeleteInput;
import com.kingsrook.qqq.backend.core.model.actions.tables.delete.DeleteOutput;
@@ -86,7 +87,6 @@ import com.kingsrook.qqq.middleware.javalin.executors.io.TableQueryInput;
import com.kingsrook.qqq.middleware.javalin.executors.io.TableQueryOutputInterface;
import com.kingsrook.qqq.middleware.javalin.executors.io.TableUpdateInput;
import com.kingsrook.qqq.middleware.javalin.executors.io.TableUpdateOutputInterface;
import com.kingsrook.qqq.backend.core.model.actions.reporting.ReportFormat;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import static org.assertj.core.api.Assertions.assertThat;
@@ -991,4 +991,38 @@ class TableSecurityMatrixTest extends BaseSqlWorkbenchTest
static final List<String> ALL = List.of(SqlWorkbenchNotebook.TABLE_NAME, SqlWorkbenchCell.TABLE_NAME, SqlWorkbenchNotebookVersion.TABLE_NAME,
SqlWorkbenchResultSnapshot.TABLE_NAME, SqlWorkbenchNotebookShare.TABLE_NAME, SqlWorkbenchExecution.TABLE_NAME);
}
/*******************************************************************************
** Both insert and update paths reject backend text outside the live PVS.
*******************************************************************************/
@Test
void testBackendSelectionValidation() throws Exception
{
login(OWNER);
Integer notebookId = ids.get("Private");
for(String invalid : List.of("unknown", "", BACKEND_NAME))
{
config.setExcludedBackendNames(invalid.equals(BACKEND_NAME) ? List.of(BACKEND_NAME) : List.of());
QRecord notebook = new InsertAction().execute(new InsertInput(SqlWorkbenchNotebook.TABLE_NAME)
.withRecord(new QRecord().withValue("title", "Invalid backend").withValue("defaultBackendName", invalid))).getRecords().get(0);
assertThat(notebook.getErrors()).isNotEmpty();
QRecord changed = new UpdateAction().execute(new UpdateInput(SqlWorkbenchNotebook.TABLE_NAME)
.withRecord(new QRecord().withValue("id", notebookId).withValue("defaultBackendName", invalid))).getRecords().get(0);
assertThat(changed.getErrors()).isNotEmpty();
QRecord cell = new InsertAction().execute(new InsertInput(SqlWorkbenchCell.TABLE_NAME)
.withRecord(new QRecord().withValue("notebookId", notebookId).withValue("backendName", invalid))).getRecords().get(0);
assertThat(cell.getErrors()).isNotEmpty();
Integer cellId = QueryAction.execute(SqlWorkbenchCell.TABLE_NAME,
new QQueryFilter(new QFilterCriteria("notebookId", QCriteriaOperator.EQUALS, notebookId))).get(0).getValueInteger("id");
QRecord changedCell = new UpdateAction().execute(new UpdateInput(SqlWorkbenchCell.TABLE_NAME)
.withRecord(new QRecord().withValue("id", cellId).withValue("backendName", invalid))).getRecords().get(0);
assertThat(changedCell.getErrors()).isNotEmpty();
}
config.setExcludedBackendNames(List.of());
QRecord valid = new InsertAction().execute(new InsertInput(SqlWorkbenchCell.TABLE_NAME)
.withRecord(new QRecord().withValue("notebookId", notebookId).withValue("backendName", BACKEND_NAME))).getRecords().get(0);
assertThat(valid.getErrors()).isNullOrEmpty();
assertThat(valid.getValueString("cellKey")).isNotBlank();
}
}