Compare commits
41
Commits
| Author | SHA1 | Date | |
|---|---|---|---|
|
|
23e145eddd
|
||
|
|
e581c4b427 | ||
|
|
131648e91d
|
||
|
|
e9d55b1be0
|
||
|
|
c450c451e5
|
||
|
|
c42209a009
|
||
|
|
40fb92d7d3
|
||
|
|
92066b8e34
|
||
|
|
15fb48a56c
|
||
|
|
0f929ed561 | ||
|
|
00bc6f646c
|
||
|
|
1784a5bf5d
|
||
|
|
602f7f866f
|
||
|
|
c63d6c8ec0
|
||
|
|
308890c974
|
||
|
|
f430b0a28e
|
||
|
|
eb0b9ca4b0
|
||
|
|
a182076722 | ||
|
|
7f0e9b38dc | ||
|
|
d3327cd4f3 | ||
|
|
8f90bc546a | ||
|
|
af7510efc0 | ||
|
|
2e535d5037 | ||
|
|
1cc0e61766 | ||
|
|
edd82d9007
|
||
|
|
6903d7ff5d | ||
|
|
a8251a6e90
|
||
|
|
2a2bd15b99 | ||
|
|
0c3fdd397b
|
||
|
|
6294c52eee | ||
|
|
422094fecb
|
||
|
|
aa13c1367e | ||
|
|
660278e020
|
||
|
|
6a0eae112c | ||
|
|
bb43871212
|
||
|
|
a0e3c222f7 | ||
|
|
b531d9c046
|
||
|
|
763c0e4352 | ||
|
|
d0153ebd9c | ||
|
|
0d8af84fbc
|
||
|
|
0d06a04953
|
No files matched your search
@@ -1,12 +1,12 @@
|
||||
version: 2.1
|
||||
setup: true
|
||||
orbs:
|
||||
qqq-orb: kingsrook/qqq-orb@0.7.12
|
||||
qqq-orb: kingsrook/qqq-orb@0.8
|
||||
workflows:
|
||||
setup:
|
||||
jobs:
|
||||
- qqq-orb/generate_pipeline:
|
||||
orb_version: 0.7.12
|
||||
orb_version: '0.8'
|
||||
filters:
|
||||
branches:
|
||||
ignore: automation/qbit-rc-pending
|
||||
|
||||
@@ -0,0 +1,7 @@
|
||||
name: build
|
||||
on:
|
||||
push:
|
||||
jobs:
|
||||
maven:
|
||||
uses: self:KofTwentyTwo/ci-workflows/.gitea/workflows/maven.yaml@v1
|
||||
secrets: inherit
|
||||
@@ -0,0 +1,23 @@
|
||||
name: CodeQL
|
||||
'on':
|
||||
push:
|
||||
branches:
|
||||
- main
|
||||
- develop
|
||||
- release/**
|
||||
- hotfix/**
|
||||
pull_request: {}
|
||||
schedule:
|
||||
- cron: 41 16 * * 0
|
||||
workflow_dispatch: {}
|
||||
permissions:
|
||||
contents: read
|
||||
actions: read
|
||||
security-events: write
|
||||
packages: read
|
||||
concurrency:
|
||||
group: codeql-${{ github.event.pull_request.number || github.ref }}
|
||||
cancel-in-progress: ${{ github.event_name == 'pull_request' }}
|
||||
jobs:
|
||||
analyze:
|
||||
uses: QRun-IO/qqq-orb/.github/workflows/qbit-codeql.yml@c288a8864fe9054f68410e1d78f5863aea4c4d22
|
||||
+1
-1
@@ -1,5 +1,5 @@
|
||||
profile: qbit
|
||||
orb_version: 0.7.12
|
||||
orb_version: '0.8'
|
||||
default_branch: develop
|
||||
java_version: '21'
|
||||
browsers: true
|
||||
|
||||
@@ -16,6 +16,7 @@ All notable changes to this project are documented here. The format follows Keep
|
||||
- **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
|
||||
- **Table standard dates.** Every table has `createDate` and `modifyDate`, typed `DATE_TIME`, not editable, with QQQ's `CREATE_DATE` and `MODIFY_DATE` behaviors. `sql_workbench_notebook_version`, `sql_workbench_result_snapshot` and `sql_workbench_execution` gain `modifyDate` (column `modify_date`, nullable). `docs/ddl/postgres.sql` creates it and carries an idempotent upgrade block; see UPGRADE.md. On the append-only tables a row's `modifyDate` equals its `createDate` on insert.
|
||||
- UI API extension sql-workbench 1.1.0 (requires QQQ 4.1.0-RC.15, UI API 1.6.0): operationIds, standard error responses with BasicErrorResponseV1, extension tags, and deprecated SqlWorkbenchErrorV1 retained for compatibility. Existing operationIds, HTTP statuses and success payloads are preserved.
|
||||
- Require QQQ `4.1.0-RC.6` for the UI API extension contract. Replace the raw Javalin route provider with endpoint specs registered by the qbit producer. `registerRoutes=false` still disables HTTP registration. No existing operation wire changes.
|
||||
- **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`.
|
||||
|
||||
+21
@@ -1,5 +1,26 @@
|
||||
# Upgrading
|
||||
|
||||
## Table standard dates: `modify_date` on three tables (unreleased)
|
||||
|
||||
Every table now has `createDate` and `modifyDate` (date-time, not editable, set by QQQ's `CREATE_DATE` and `MODIFY_DATE` field behaviors). Three tables had no `modifyDate`: `sql_workbench_notebook_version`, `sql_workbench_result_snapshot` and `sql_workbench_execution`. The other three already had both columns.
|
||||
|
||||
**Run this before deploying the release** if your host keeps its own migrations (the statements are in `docs/ddl/postgres.sql`, in the block "Upgrading from 1.0.0-RC.11 or earlier"). With snake_case backend names (the usual `inferBackendName` / `setInferredFieldBackendNames` setup) the column is `modify_date`; without them it is `modifyDate`.
|
||||
|
||||
```sql
|
||||
ALTER TABLE sql_workbench_notebook_version ADD COLUMN IF NOT EXISTS modify_date TIMESTAMP WITHOUT TIME ZONE;
|
||||
UPDATE sql_workbench_notebook_version SET modify_date = COALESCE(create_date, CURRENT_TIMESTAMP) WHERE modify_date IS NULL;
|
||||
|
||||
ALTER TABLE sql_workbench_result_snapshot ADD COLUMN IF NOT EXISTS modify_date TIMESTAMP WITHOUT TIME ZONE;
|
||||
UPDATE sql_workbench_result_snapshot SET modify_date = COALESCE(create_date, CURRENT_TIMESTAMP) WHERE modify_date IS NULL;
|
||||
|
||||
ALTER TABLE sql_workbench_execution ADD COLUMN IF NOT EXISTS modify_date TIMESTAMP WITHOUT TIME ZONE;
|
||||
UPDATE sql_workbench_execution SET modify_date = COALESCE(create_date, CURRENT_TIMESTAMP) WHERE modify_date IS NULL;
|
||||
```
|
||||
|
||||
The new column is nullable with no default, so it is safe while the previous release is still running: adding it is a catalog change (no table rewrite, a momentary lock), the previous release never writes it, and the new release treats an empty value as "unknown". The backfill uses row locks only, touches only rows whose `modify_date` is still empty, and can be re-run (run it once more after the old release is gone to fill rows it wrote in between). On a very large `sql_workbench_execution`, run the backfill in `id` ranges.
|
||||
|
||||
Nothing else changes for a host: no setting, no customizer, no permission. The qbit writes all of its own tables through QQQ's insert and update actions, so the field behaviors keep the dates current. Behaviour notes: an audit row's `createDate` is now the time the row was inserted (a few milliseconds after `startTime`), and its `modifyDate` moves once, when the run's outcome is recorded; a version row's `modifyDate` equals its `createDate`. If you run the qbit's SQL through the separate read-only database account as before, nothing about that account or what it may read changes.
|
||||
|
||||
## Table help and `auditRetentionDays` (unreleased)
|
||||
|
||||
No action needed. The qbit now fills empty `help` slots on its app and tables (help a host already set is kept). The new `auditRetentionDays` config defaults to null, which keeps every audit row as before; set it to bound the size of `sql_workbench_execution`. `notebookVersionRetention` (default 100 per notebook) was already enforced. There is no DDL change and no seed.
|
||||
|
||||
@@ -53,6 +53,7 @@ CREATE TABLE IF NOT EXISTS sql_workbench_notebook_version (
|
||||
saved_by_name VARCHAR(255),
|
||||
comment VARCHAR(255),
|
||||
create_date TIMESTAMP WITHOUT TIME ZONE,
|
||||
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
||||
CONSTRAINT pk_sql_workbench_notebook_version PRIMARY KEY (id),
|
||||
CONSTRAINT uq_sql_workbench_notebook_version UNIQUE (notebook_id, version_number),
|
||||
CONSTRAINT fk_sql_workbench_notebook_version_notebook FOREIGN KEY (notebook_id) REFERENCES sql_workbench_notebook (id)
|
||||
@@ -71,6 +72,7 @@ CREATE TABLE IF NOT EXISTS sql_workbench_result_snapshot (
|
||||
truncated BOOLEAN,
|
||||
created_by_user_id VARCHAR(255),
|
||||
create_date TIMESTAMP WITHOUT TIME ZONE,
|
||||
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
||||
CONSTRAINT pk_sql_workbench_result_snapshot PRIMARY KEY (id),
|
||||
CONSTRAINT fk_sql_workbench_result_snapshot_notebook FOREIGN KEY (notebook_id) REFERENCES sql_workbench_notebook (id)
|
||||
);
|
||||
@@ -130,6 +132,7 @@ CREATE TABLE IF NOT EXISTS sql_workbench_execution (
|
||||
error_message TEXT,
|
||||
client_ip VARCHAR(64),
|
||||
create_date TIMESTAMP WITHOUT TIME ZONE,
|
||||
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
||||
CONSTRAINT pk_sql_workbench_execution PRIMARY KEY (id),
|
||||
CONSTRAINT uq_sql_workbench_execution_uuid UNIQUE (execution_uuid)
|
||||
);
|
||||
@@ -137,3 +140,24 @@ CREATE INDEX IF NOT EXISTS ix_sql_workbench_execution_user ON sql_workbench_exec
|
||||
CREATE INDEX IF NOT EXISTS ix_sql_workbench_execution_start ON sql_workbench_execution (start_time);
|
||||
-- the notebook page's "Runs" list joins executions by notebook_id
|
||||
CREATE INDEX IF NOT EXISTS ix_sql_workbench_execution_notebook ON sql_workbench_execution (notebook_id);
|
||||
|
||||
-- Upgrading from 1.0.0-RC.11 or earlier (table standard dates): the version,
|
||||
-- snapshot and execution tables gain modify_date (create_date already exists on
|
||||
-- every table). The column is nullable with no default, so adding it changes
|
||||
-- only the catalog (no table rewrite, a momentary lock) and the previous release
|
||||
-- keeps writing these tables while this runs. The backfill gives existing rows
|
||||
-- their create_date, or the time of the migration where there is none; it takes
|
||||
-- row locks only, fills only rows that are still empty, and is safe to re-run
|
||||
-- (run it again after the old release is gone to fill rows it wrote meanwhile).
|
||||
-- On a very large sql_workbench_execution, run the backfill in id ranges, for example
|
||||
-- UPDATE sql_workbench_execution SET modify_date = COALESCE(create_date, CURRENT_TIMESTAMP)
|
||||
-- WHERE modify_date IS NULL AND id >= 1 AND id < 100001;
|
||||
-- Without snake_case backend names the columns are createDate and modifyDate.
|
||||
ALTER TABLE sql_workbench_notebook_version ADD COLUMN IF NOT EXISTS modify_date TIMESTAMP WITHOUT TIME ZONE;
|
||||
UPDATE sql_workbench_notebook_version SET modify_date = COALESCE(create_date, CURRENT_TIMESTAMP) WHERE modify_date IS NULL;
|
||||
|
||||
ALTER TABLE sql_workbench_result_snapshot ADD COLUMN IF NOT EXISTS modify_date TIMESTAMP WITHOUT TIME ZONE;
|
||||
UPDATE sql_workbench_result_snapshot SET modify_date = COALESCE(create_date, CURRENT_TIMESTAMP) WHERE modify_date IS NULL;
|
||||
|
||||
ALTER TABLE sql_workbench_execution ADD COLUMN IF NOT EXISTS modify_date TIMESTAMP WITHOUT TIME ZONE;
|
||||
UPDATE sql_workbench_execution SET modify_date = COALESCE(create_date, CURRENT_TIMESTAMP) WHERE modify_date IS NULL;
|
||||
@@ -18,9 +18,10 @@
|
||||
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
|
||||
|
||||
<properties>
|
||||
<qqq.version>4.1.0-RC.26</qqq.version> <!-- feeds qbit-build-parent's qqq-bom-pom import -->
|
||||
<qbit.guardrails.enforce>true</qbit.guardrails.enforce>
|
||||
<qbit.guardrails.strictUiApiRoutes>true</qbit.guardrails.strictUiApiRoutes>
|
||||
<revision>1.0.0-SNAPSHOT</revision>
|
||||
<revision>1.0.0-RC.11</revision>
|
||||
<maven.compiler.source>21</maven.compiler.source>
|
||||
<maven.compiler.target>21</maven.compiler.target>
|
||||
<jacoco.version>0.8.15</jacoco.version>
|
||||
@@ -31,7 +32,7 @@
|
||||
<parent>
|
||||
<groupId>com.kingsrook</groupId>
|
||||
<artifactId>qbit-build-parent</artifactId>
|
||||
<version>2.1.0</version>
|
||||
<version>2.2.1</version>
|
||||
</parent>
|
||||
|
||||
<groupId>com.kingsrook.qbits</groupId>
|
||||
@@ -264,7 +265,7 @@
|
||||
<activeByDefault>true</activeByDefault>
|
||||
</activation>
|
||||
<properties>
|
||||
<qqq.snapshot.version>4.1.0-RC.15</qqq.snapshot.version>
|
||||
<qqq.snapshot.version>4.1.0-RC.26</qqq.snapshot.version>
|
||||
</properties>
|
||||
<dependencyManagement>
|
||||
<dependencies>
|
||||
@@ -277,18 +278,6 @@
|
||||
</dependency>
|
||||
</dependencies>
|
||||
</dependencyManagement>
|
||||
<repositories>
|
||||
<repository>
|
||||
<id>sonatype-snapshots</id>
|
||||
<url>https://central.sonatype.com/repository/maven-snapshots/</url>
|
||||
<releases>
|
||||
<enabled>false</enabled>
|
||||
</releases>
|
||||
<snapshots>
|
||||
<enabled>true</enabled>
|
||||
</snapshots>
|
||||
</repository>
|
||||
</repositories>
|
||||
</profile>
|
||||
</profiles>
|
||||
</project>
|
||||
+23
@@ -19,6 +19,7 @@ package com.kingsrook.qbits.sqlworkbench.customizers;
|
||||
|
||||
import java.util.List;
|
||||
import java.util.Optional;
|
||||
import com.kingsrook.qqq.backend.core.actions.customizers.AbstractPreInsertCustomizer;
|
||||
import com.kingsrook.qqq.backend.core.exceptions.QException;
|
||||
import com.kingsrook.qqq.backend.core.model.actions.tables.delete.DeleteInput;
|
||||
import com.kingsrook.qqq.backend.core.model.actions.tables.insert.InsertInput;
|
||||
@@ -44,8 +45,30 @@ public class AppendOnlyTableCustomizer extends WorkbenchTableCustomizer
|
||||
**
|
||||
*******************************************************************************/
|
||||
@Override
|
||||
public AbstractPreInsertCustomizer.WhenToRun whenToRunPreInsert(InsertInput insertInput, boolean isPreview)
|
||||
{
|
||||
/////////////////////////////////////////////////////////////////////
|
||||
// after QQQ's field behaviors have stamped createDate / modifyDate //
|
||||
/////////////////////////////////////////////////////////////////////
|
||||
return (AbstractPreInsertCustomizer.WhenToRun.BEFORE_UNIQUE_KEY_CHECKS);
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** A row of an append-only table is never modified by a user, so its
|
||||
** modifyDate is its createDate: the two are one value, not two clock reads.
|
||||
*******************************************************************************/
|
||||
@Override
|
||||
public List<QRecord> preInsert(InsertInput insertInput, List<QRecord> records, boolean isPreview) throws QException
|
||||
{
|
||||
for(QRecord record : records)
|
||||
{
|
||||
if(record.getValue("createDate") != null)
|
||||
{
|
||||
record.setValue("modifyDate", record.getValue("createDate"));
|
||||
}
|
||||
}
|
||||
return (denyUnlessSystem(records));
|
||||
}
|
||||
|
||||
|
||||
+4
-1
@@ -34,7 +34,10 @@ import com.kingsrook.qqq.backend.core.model.data.QRecord;
|
||||
*******************************************************************************/
|
||||
public class SnapshotTableCustomizer extends WorkbenchTableCustomizer
|
||||
{
|
||||
private static final Set<String> EDITABLE = Set.of("id", "label");
|
||||
//////////////////////////////////////////////////////////////////////////////////////
|
||||
// modifyDate is QQQ's own bookkeeping (MODIFY_DATE behavior), set before this hook //
|
||||
//////////////////////////////////////////////////////////////////////////////////////
|
||||
private static final Set<String> EDITABLE = Set.of("id", "label", "modifyDate");
|
||||
|
||||
|
||||
|
||||
|
||||
+3
-3
@@ -168,7 +168,7 @@ public class SqlWorkbenchTableMetaDataHelper
|
||||
.withRecordSecurityLock(childLock(config, JOIN_NOTEBOOK_VERSION))
|
||||
.withSection(new QFieldSection("identity", new QIcon("badge"), Tier.T1, List.of("id", "notebookId", "versionNumber", "comment")))
|
||||
.withSection(new QFieldSection("content", new QIcon("code"), Tier.T2, List.of("contentJson")))
|
||||
.withSection(new QFieldSection("system", new QIcon("calendar_month"), Tier.T3, List.of("savedByName", "savedByUserId", "createDate")));
|
||||
.withSection(new QFieldSection("system", new QIcon("calendar_month"), Tier.T3, List.of("savedByName", "savedByUserId", "createDate", "modifyDate")));
|
||||
table.getField("comment").setIsEditable(false);
|
||||
table.getField("contentJson").withFieldAdornment(new FieldAdornment(AdornmentType.CODE_EDITOR).withValue(AdornmentType.CodeEditorValues.languageMode("json")));
|
||||
appendOnly(table);
|
||||
@@ -190,7 +190,7 @@ public class SqlWorkbenchTableMetaDataHelper
|
||||
.withAuditRules(new QAuditRules().withAuditLevel(AuditLevel.RECORD))
|
||||
.withSection(new QFieldSection("identity", new QIcon("badge"), Tier.T1, List.of("id", "notebookId", "label", "cellKey")))
|
||||
.withSection(new QFieldSection("result", new QIcon("table_chart"), Tier.T2, List.of("backendName", "rowCount", "truncated", "executionUuid", "sqlText", "resultJson")))
|
||||
.withSection(new QFieldSection("system", new QIcon("calendar_month"), Tier.T3, List.of("createdByUserId", "createDate")));
|
||||
.withSection(new QFieldSection("system", new QIcon("calendar_month"), Tier.T3, List.of("createdByUserId", "createDate", "modifyDate")));
|
||||
table.getField("sqlText").withFieldAdornment(new FieldAdornment(AdornmentType.CODE_EDITOR).withValue(AdornmentType.CodeEditorValues.languageMode("sql")));
|
||||
table.getField("resultJson").withFieldAdornment(new FieldAdornment(AdornmentType.CODE_EDITOR).withValue(AdornmentType.CodeEditorValues.languageMode("json")));
|
||||
table.withoutCapability(Capability.TABLE_INSERT);
|
||||
@@ -235,7 +235,7 @@ public class SqlWorkbenchTableMetaDataHelper
|
||||
.withSection(new QFieldSection("run", new QIcon("timer"), Tier.T2, List.of("userName", "userId", "startTime", "endTime", "durationMs", "rowsReturned", "rowsAffected", "truncated",
|
||||
"statementCount", "statementKinds", "notebookId", "cellKey", "clientIp")))
|
||||
.withSection(new QFieldSection("sql", new QIcon("code"), Tier.T2, List.of("sqlText", "sqlHash", "parametersJson", "errorMessage")))
|
||||
.withSection(new QFieldSection("system", new QIcon("calendar_month"), Tier.T3, List.of("createDate")));
|
||||
.withSection(new QFieldSection("system", new QIcon("calendar_month"), Tier.T3, List.of("createDate", "modifyDate")));
|
||||
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)
|
||||
|
||||
@@ -21,6 +21,7 @@ import java.time.Instant;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QField;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QRecord;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QRecordEntity;
|
||||
import com.kingsrook.qqq.backend.core.model.metadata.fields.DynamicDefaultValueBehavior;
|
||||
import com.kingsrook.qqq.backend.core.model.metadata.fields.ValueTooLongBehavior;
|
||||
|
||||
|
||||
@@ -62,10 +63,10 @@ public class SqlWorkbenchCell extends QRecordEntity
|
||||
@QField(maxLength = 100)
|
||||
private String backendName;
|
||||
|
||||
@QField(isEditable = false)
|
||||
@QField(isEditable = false, dynamicDefaultValueBehavior = DynamicDefaultValueBehavior.CREATE_DATE)
|
||||
private Instant createDate;
|
||||
|
||||
@QField(isEditable = false)
|
||||
@QField(isEditable = false, dynamicDefaultValueBehavior = DynamicDefaultValueBehavior.MODIFY_DATE)
|
||||
private Instant modifyDate;
|
||||
|
||||
|
||||
|
||||
@@ -21,6 +21,7 @@ import java.time.Instant;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QField;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QRecord;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QRecordEntity;
|
||||
import com.kingsrook.qqq.backend.core.model.metadata.fields.DynamicDefaultValueBehavior;
|
||||
import com.kingsrook.qqq.backend.core.model.metadata.fields.ValueTooLongBehavior;
|
||||
|
||||
|
||||
@@ -106,9 +107,12 @@ public class SqlWorkbenchExecution extends QRecordEntity
|
||||
@QField(isEditable = false, maxLength = 64)
|
||||
private String clientIp;
|
||||
|
||||
@QField(isEditable = false)
|
||||
@QField(isEditable = false, dynamicDefaultValueBehavior = DynamicDefaultValueBehavior.CREATE_DATE)
|
||||
private Instant createDate;
|
||||
|
||||
@QField(isEditable = false, dynamicDefaultValueBehavior = DynamicDefaultValueBehavior.MODIFY_DATE)
|
||||
private Instant modifyDate;
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
@@ -841,4 +845,35 @@ public class SqlWorkbenchExecution extends QRecordEntity
|
||||
return (this);
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** Getter for modifyDate
|
||||
*******************************************************************************/
|
||||
public Instant getModifyDate()
|
||||
{
|
||||
return (this.modifyDate);
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** Setter for modifyDate
|
||||
*******************************************************************************/
|
||||
public void setModifyDate(Instant modifyDate)
|
||||
{
|
||||
this.modifyDate = modifyDate;
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** Fluent setter for modifyDate
|
||||
*******************************************************************************/
|
||||
public SqlWorkbenchExecution withModifyDate(Instant modifyDate)
|
||||
{
|
||||
this.modifyDate = modifyDate;
|
||||
return (this);
|
||||
}
|
||||
|
||||
}
|
||||
@@ -21,6 +21,7 @@ import java.time.Instant;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QField;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QRecord;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QRecordEntity;
|
||||
import com.kingsrook.qqq.backend.core.model.metadata.fields.DynamicDefaultValueBehavior;
|
||||
import com.kingsrook.qqq.backend.core.model.metadata.fields.ValueTooLongBehavior;
|
||||
|
||||
|
||||
@@ -66,10 +67,10 @@ public class SqlWorkbenchNotebook extends QRecordEntity
|
||||
@QField(isEditable = false)
|
||||
private Integer currentVersion;
|
||||
|
||||
@QField(isEditable = false)
|
||||
@QField(isEditable = false, dynamicDefaultValueBehavior = DynamicDefaultValueBehavior.CREATE_DATE)
|
||||
private Instant createDate;
|
||||
|
||||
@QField(isEditable = false)
|
||||
@QField(isEditable = false, dynamicDefaultValueBehavior = DynamicDefaultValueBehavior.MODIFY_DATE)
|
||||
private Instant modifyDate;
|
||||
|
||||
|
||||
|
||||
@@ -21,6 +21,7 @@ import java.time.Instant;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QField;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QRecord;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QRecordEntity;
|
||||
import com.kingsrook.qqq.backend.core.model.metadata.fields.DynamicDefaultValueBehavior;
|
||||
import com.kingsrook.qqq.backend.core.model.metadata.fields.ValueTooLongBehavior;
|
||||
|
||||
|
||||
@@ -63,10 +64,10 @@ public class SqlWorkbenchNotebookShare extends QRecordEntity
|
||||
@QField(isEditable = false, maxLength = 255, valueTooLongBehavior = ValueTooLongBehavior.TRUNCATE_ELLIPSIS)
|
||||
private String createdByName;
|
||||
|
||||
@QField(isEditable = false)
|
||||
@QField(isEditable = false, dynamicDefaultValueBehavior = DynamicDefaultValueBehavior.CREATE_DATE)
|
||||
private Instant createDate;
|
||||
|
||||
@QField(isEditable = false)
|
||||
@QField(isEditable = false, dynamicDefaultValueBehavior = DynamicDefaultValueBehavior.MODIFY_DATE)
|
||||
private Instant modifyDate;
|
||||
|
||||
|
||||
|
||||
+36
-1
@@ -21,6 +21,7 @@ import java.time.Instant;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QField;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QRecord;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QRecordEntity;
|
||||
import com.kingsrook.qqq.backend.core.model.metadata.fields.DynamicDefaultValueBehavior;
|
||||
import com.kingsrook.qqq.backend.core.model.metadata.fields.ValueTooLongBehavior;
|
||||
|
||||
|
||||
@@ -59,9 +60,12 @@ public class SqlWorkbenchNotebookVersion extends QRecordEntity
|
||||
@QField(maxLength = 255, valueTooLongBehavior = ValueTooLongBehavior.TRUNCATE_ELLIPSIS)
|
||||
private String comment;
|
||||
|
||||
@QField(isEditable = false)
|
||||
@QField(isEditable = false, dynamicDefaultValueBehavior = DynamicDefaultValueBehavior.CREATE_DATE)
|
||||
private Instant createDate;
|
||||
|
||||
@QField(isEditable = false, dynamicDefaultValueBehavior = DynamicDefaultValueBehavior.MODIFY_DATE)
|
||||
private Instant modifyDate;
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
@@ -329,4 +333,35 @@ public class SqlWorkbenchNotebookVersion extends QRecordEntity
|
||||
return (this);
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** Getter for modifyDate
|
||||
*******************************************************************************/
|
||||
public Instant getModifyDate()
|
||||
{
|
||||
return (this.modifyDate);
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** Setter for modifyDate
|
||||
*******************************************************************************/
|
||||
public void setModifyDate(Instant modifyDate)
|
||||
{
|
||||
this.modifyDate = modifyDate;
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** Fluent setter for modifyDate
|
||||
*******************************************************************************/
|
||||
public SqlWorkbenchNotebookVersion withModifyDate(Instant modifyDate)
|
||||
{
|
||||
this.modifyDate = modifyDate;
|
||||
return (this);
|
||||
}
|
||||
|
||||
}
|
||||
+36
-1
@@ -21,6 +21,7 @@ import java.time.Instant;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QField;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QRecord;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QRecordEntity;
|
||||
import com.kingsrook.qqq.backend.core.model.metadata.fields.DynamicDefaultValueBehavior;
|
||||
import com.kingsrook.qqq.backend.core.model.metadata.fields.ValueTooLongBehavior;
|
||||
|
||||
|
||||
@@ -72,9 +73,12 @@ public class SqlWorkbenchResultSnapshot extends QRecordEntity
|
||||
@QField(isEditable = false, maxLength = 255)
|
||||
private String createdByUserId;
|
||||
|
||||
@QField(isEditable = false)
|
||||
@QField(isEditable = false, dynamicDefaultValueBehavior = DynamicDefaultValueBehavior.CREATE_DATE)
|
||||
private Instant createDate;
|
||||
|
||||
@QField(isEditable = false, dynamicDefaultValueBehavior = DynamicDefaultValueBehavior.MODIFY_DATE)
|
||||
private Instant modifyDate;
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
@@ -466,4 +470,35 @@ public class SqlWorkbenchResultSnapshot extends QRecordEntity
|
||||
return (this);
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** Getter for modifyDate
|
||||
*******************************************************************************/
|
||||
public Instant getModifyDate()
|
||||
{
|
||||
return (this.modifyDate);
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** Setter for modifyDate
|
||||
*******************************************************************************/
|
||||
public void setModifyDate(Instant modifyDate)
|
||||
{
|
||||
this.modifyDate = modifyDate;
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** Fluent setter for modifyDate
|
||||
*******************************************************************************/
|
||||
public SqlWorkbenchResultSnapshot withModifyDate(Instant modifyDate)
|
||||
{
|
||||
this.modifyDate = modifyDate;
|
||||
return (this);
|
||||
}
|
||||
|
||||
}
|
||||
@@ -0,0 +1,206 @@
|
||||
/*
|
||||
* 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;
|
||||
|
||||
|
||||
import java.nio.file.Files;
|
||||
import java.nio.file.Path;
|
||||
import java.time.Instant;
|
||||
import java.util.List;
|
||||
import java.util.Map;
|
||||
import java.util.regex.Matcher;
|
||||
import java.util.regex.Pattern;
|
||||
import com.kingsrook.qbits.sqlworkbench.engine.ExecutionAuditor;
|
||||
import com.kingsrook.qbits.sqlworkbench.engine.ExecutionMode;
|
||||
import com.kingsrook.qbits.sqlworkbench.engine.ExecutionStatus;
|
||||
import com.kingsrook.qbits.sqlworkbench.model.SqlWorkbenchExecution;
|
||||
import com.kingsrook.qbits.sqlworkbench.model.SqlWorkbenchNotebookVersion;
|
||||
import com.kingsrook.qbits.sqlworkbench.model.SqlWorkbenchResultSnapshot;
|
||||
import com.kingsrook.qbits.sqlworkbench.notebook.NotebookService;
|
||||
import com.kingsrook.qqq.backend.core.actions.tables.InsertAction;
|
||||
import com.kingsrook.qqq.backend.core.actions.tables.QueryAction;
|
||||
import com.kingsrook.qqq.backend.core.actions.tables.UpdateAction;
|
||||
import com.kingsrook.qqq.backend.core.model.actions.tables.insert.InsertInput;
|
||||
import com.kingsrook.qqq.backend.core.model.actions.tables.query.QCriteriaOperator;
|
||||
import com.kingsrook.qqq.backend.core.model.actions.tables.query.QFilterCriteria;
|
||||
import com.kingsrook.qqq.backend.core.model.actions.tables.query.QQueryFilter;
|
||||
import com.kingsrook.qqq.backend.core.model.actions.tables.update.UpdateInput;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QRecord;
|
||||
import com.kingsrook.qqq.backend.core.model.data.QRecordEntity;
|
||||
import com.kingsrook.qqq.backend.core.model.metadata.fields.DynamicDefaultValueBehavior;
|
||||
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 org.junit.jupiter.api.Test;
|
||||
import static org.assertj.core.api.Assertions.assertThat;
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** The QQQ table standard: every table the qbit declares has createDate and
|
||||
** modifyDate (DATE_TIME, not editable, maintained by QQQ's field behaviors),
|
||||
** and the rows the qbit writes itself carry them correctly. Every write the
|
||||
** qbit makes to its own tables goes through QQQ's insert and update actions
|
||||
** (it has no SQL or JDBC writer for them), so the behaviors cover them.
|
||||
*******************************************************************************/
|
||||
class TableStandardDatesTest extends BaseSqlWorkbenchTest
|
||||
{
|
||||
|
||||
/*******************************************************************************
|
||||
** Iterates the tables the qbit declares, so a table added later without the
|
||||
** two dates fails here.
|
||||
*******************************************************************************/
|
||||
@Test
|
||||
void testEveryTableHasBothDates()
|
||||
{
|
||||
List<QTableMetaData> tables = qInstance.getTables().values().stream().filter(t -> t.getName().startsWith("sql_workbench_")).toList();
|
||||
assertThat(tables).hasSize(6);
|
||||
for(QTableMetaData table : tables)
|
||||
{
|
||||
assertDateField(table, "createDate", DynamicDefaultValueBehavior.CREATE_DATE);
|
||||
assertDateField(table, "modifyDate", DynamicDefaultValueBehavior.MODIFY_DATE);
|
||||
}
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
**
|
||||
*******************************************************************************/
|
||||
private static void assertDateField(QTableMetaData table, String fieldName, DynamicDefaultValueBehavior behavior)
|
||||
{
|
||||
assertThat(table.getFields()).as(table.getName() + " has " + fieldName).containsKey(fieldName);
|
||||
QFieldMetaData field = table.getField(fieldName);
|
||||
assertThat(field.getType()).as(table.getName() + "." + fieldName + " type").isEqualTo(QFieldType.DATE_TIME);
|
||||
assertThat(field.getIsEditable()).as(table.getName() + "." + fieldName + " editable").isFalse();
|
||||
assertThat(field.getBehaviors()).as(table.getName() + "." + fieldName + " behaviors").contains(behavior);
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** The shipped DDL (docs/ddl/postgres.sql) creates both columns on every table
|
||||
** and carries the upgrade for the three tables that lacked modify_date.
|
||||
*******************************************************************************/
|
||||
@Test
|
||||
void testShippedDdlHasBothColumns() throws Exception
|
||||
{
|
||||
String ddl = Files.readString(Path.of("docs", "ddl", "postgres.sql"));
|
||||
Matcher tables = Pattern.compile("CREATE TABLE IF NOT EXISTS (sql_workbench_\\w+) \\((.*?)\\n\\);", Pattern.DOTALL).matcher(ddl);
|
||||
int count = 0;
|
||||
while(tables.find())
|
||||
{
|
||||
count++;
|
||||
assertThat(tables.group(2)).as(tables.group(1)).contains("\n create_date TIMESTAMP WITHOUT TIME ZONE").contains("\n modify_date TIMESTAMP WITHOUT TIME ZONE");
|
||||
}
|
||||
assertThat(count).isEqualTo(6);
|
||||
|
||||
for(String table : List.of("sql_workbench_notebook_version", "sql_workbench_result_snapshot", "sql_workbench_execution"))
|
||||
{
|
||||
assertThat(ddl).contains("ALTER TABLE " + table + " ADD COLUMN IF NOT EXISTS modify_date TIMESTAMP WITHOUT TIME ZONE;");
|
||||
}
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** A notebook save writes a version row (the qbit's own bookkeeping, as the
|
||||
** system user): modifyDate is createDate.
|
||||
*******************************************************************************/
|
||||
@Test
|
||||
void testVersionRowDates() throws Exception
|
||||
{
|
||||
NotebookService service = new NotebookService(SqlWorkbenchRuntime.get(qInstance));
|
||||
service.create(Map.of("title", "Dates", "defaultBackendName", BACKEND_NAME), as(READER));
|
||||
|
||||
List<SqlWorkbenchNotebookVersion> versions = SystemUser.call(() -> QueryAction.execute(SqlWorkbenchNotebookVersion.TABLE_NAME, SqlWorkbenchNotebookVersion.class, new QQueryFilter()));
|
||||
assertThat(versions).isNotEmpty().allSatisfy(version ->
|
||||
{
|
||||
assertThat(version.getCreateDate()).isNotNull();
|
||||
assertThat(version.getModifyDate()).isEqualTo(version.getCreateDate());
|
||||
});
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** The audit row: both dates on insert (equal), then the outcome update moves
|
||||
** modifyDate and leaves createDate.
|
||||
*******************************************************************************/
|
||||
@Test
|
||||
void testExecutionRowDates() throws Exception
|
||||
{
|
||||
loginAs(READER);
|
||||
ExecutionAuditor auditor = new ExecutionAuditor(config);
|
||||
SqlWorkbenchExecution execution = new SqlWorkbenchExecution()
|
||||
.withExecutionUuid("00000000-0000-0000-0000-000000000001")
|
||||
.withUserId(READER)
|
||||
.withBackendName(BACKEND_NAME)
|
||||
.withMode(ExecutionMode.READ_ONLY.name())
|
||||
.withSqlText("SELECT 1")
|
||||
.withStartTime(Instant.now());
|
||||
Integer id = auditor.start(execution);
|
||||
|
||||
SqlWorkbenchExecution running = fetch(SqlWorkbenchExecution.TABLE_NAME, id, SqlWorkbenchExecution.class);
|
||||
assertThat(running.getCreateDate()).isNotNull();
|
||||
assertThat(running.getModifyDate()).isEqualTo(running.getCreateDate());
|
||||
|
||||
Thread.sleep(20);
|
||||
auditor.finish(execution, ExecutionStatus.SUCCEEDED, Instant.now(), 1, null, false, null);
|
||||
|
||||
SqlWorkbenchExecution finished = fetch(SqlWorkbenchExecution.TABLE_NAME, id, SqlWorkbenchExecution.class);
|
||||
assertThat(finished.getCreateDate()).isEqualTo(running.getCreateDate());
|
||||
assertThat(finished.getModifyDate()).isAfter(running.getModifyDate());
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** A pinned result: both dates on insert; a label edit moves modifyDate only.
|
||||
*******************************************************************************/
|
||||
@Test
|
||||
void testSnapshotRowDates() throws Exception
|
||||
{
|
||||
NotebookService service = new NotebookService(SqlWorkbenchRuntime.get(qInstance));
|
||||
Integer notebookId = (Integer) service.create(Map.of("title", "Pins", "defaultBackendName", BACKEND_NAME), as(READER)).get("id");
|
||||
Integer snapshotId = SystemUser.call(() -> new InsertAction().execute(new InsertInput(SqlWorkbenchResultSnapshot.TABLE_NAME)
|
||||
.withRecordEntity(new SqlWorkbenchResultSnapshot().withNotebookId(notebookId).withLabel("one"))).getRecords().get(0).getValueInteger("id"));
|
||||
|
||||
SqlWorkbenchResultSnapshot inserted = fetch(SqlWorkbenchResultSnapshot.TABLE_NAME, snapshotId, SqlWorkbenchResultSnapshot.class);
|
||||
assertThat(inserted.getCreateDate()).isNotNull();
|
||||
assertThat(inserted.getModifyDate()).isNotNull();
|
||||
|
||||
Thread.sleep(20);
|
||||
SystemUser.call(() -> new UpdateAction().execute(new UpdateInput(SqlWorkbenchResultSnapshot.TABLE_NAME)
|
||||
.withRecord(new QRecord().withValue("id", snapshotId).withValue("label", "two"))));
|
||||
|
||||
SqlWorkbenchResultSnapshot updated = fetch(SqlWorkbenchResultSnapshot.TABLE_NAME, snapshotId, SqlWorkbenchResultSnapshot.class);
|
||||
assertThat(updated.getCreateDate()).isEqualTo(inserted.getCreateDate());
|
||||
assertThat(updated.getModifyDate()).isAfter(inserted.getModifyDate());
|
||||
}
|
||||
|
||||
|
||||
|
||||
/*******************************************************************************
|
||||
** One row, read as the system user.
|
||||
*******************************************************************************/
|
||||
private static <T extends QRecordEntity> T fetch(String table, Integer id, Class<T> type) throws Exception
|
||||
{
|
||||
List<T> rows = SystemUser.call(() -> QueryAction.execute(table, type, new QQueryFilter(new QFilterCriteria("id", QCriteriaOperator.EQUALS, id))));
|
||||
assertThat(rows).hasSize(1);
|
||||
return (rows.get(0));
|
||||
}
|
||||
}
|
||||
@@ -49,6 +49,7 @@ CREATE TABLE IF NOT EXISTS sql_workbench_notebook_version (
|
||||
saved_by_name VARCHAR(255),
|
||||
comment VARCHAR(255),
|
||||
create_date TIMESTAMP,
|
||||
modify_date TIMESTAMP,
|
||||
CONSTRAINT pk_sql_workbench_notebook_version PRIMARY KEY (id),
|
||||
CONSTRAINT uq_sql_workbench_notebook_version UNIQUE (notebook_id, version_number),
|
||||
CONSTRAINT fk_sql_workbench_notebook_version_notebook FOREIGN KEY (notebook_id) REFERENCES sql_workbench_notebook (id)
|
||||
@@ -67,6 +68,7 @@ CREATE TABLE IF NOT EXISTS sql_workbench_result_snapshot (
|
||||
truncated BOOLEAN,
|
||||
created_by_user_id VARCHAR(255),
|
||||
create_date TIMESTAMP,
|
||||
modify_date TIMESTAMP,
|
||||
CONSTRAINT pk_sql_workbench_result_snapshot PRIMARY KEY (id),
|
||||
CONSTRAINT fk_sql_workbench_result_snapshot_notebook FOREIGN KEY (notebook_id) REFERENCES sql_workbench_notebook (id)
|
||||
);
|
||||
@@ -114,6 +116,7 @@ CREATE TABLE IF NOT EXISTS sql_workbench_execution (
|
||||
error_message VARCHAR(1000000),
|
||||
client_ip VARCHAR(64),
|
||||
create_date TIMESTAMP,
|
||||
modify_date TIMESTAMP,
|
||||
CONSTRAINT pk_sql_workbench_execution PRIMARY KEY (id),
|
||||
CONSTRAINT uq_sql_workbench_execution_uuid UNIQUE (execution_uuid)
|
||||
);
|
||||
|
||||
Reference in new issue
Block a user