[D] Database perspective: exploring a table's data, and scoping the tree to the connection (hop)

["fskorgen (via GitHub)" <gi...@apache.org>]

GitHub user fskorgen created a discussion: Database perspective: exploring a table's data, and scoping the tree to the connection

The Database perspective is good at the things that were expensive to build — background execution,
cancel, SQL tabs that survive a restart, indexes, real `CREATE` statements. Two gaps keep coming up
when I use it, and I would like to propose filling them. They are separable, so react to either.


# Part 1 — Looking at a table's data

I open a table because I want to understand it: what columns it has, what the data looks like, and
then — nearly always — a closer look at some of the rows. Find a customer code, see the rows that
carry it.

Today that is two places and a lot of typing. Double-clicking a table opens a SQL tab with rows but
no column list. *Show information* opens an object tab with columns, indexes and DDL, but no rows.
Structure and data are never side by side. And narrowing to the value I am looking at means editing
the query by hand, every time, for someone who already has that value on screen.

**What I would propose:** a **Data** tab beside Columns, Indexes and DDL, with double-click opening
the object tab rather than a bare SQL tab. From there:

- **Opening a table runs no data query.** Columns, indexes and DDL are read on open; the rows load
  when the Data tab is selected, and then exactly one query runs.
- **A row limit beside the grid**, re-run without leaving the tab. A blank or non-positive value
  falls back to the configured limit rather than reading everything.
- **Right-click a cell → Quick filter**: equals, contains, starts with, is not null, remove filter —
  built from the cell under the cursor. The active filter shows in an editable field above the grid,
  so a typed `WHERE` and a clicked one land in the same place.
- **Open as SQL** hands the same statement, filter included, to a SQL tab that runs unchanged.
- **Row count** for the filter that stands.
- **Columns shows the database's own type name** beside Hop's — `nvarchar` next to `String`.

The SQL tab stays exactly as it is; *Preview* still opens one.

It would run on the perspective's existing `DatabaseOperation` model — same background execution,
same cancel, same operations panel, no second mechanism. The Data tab and the SQL results would
render through one shared grid so the two cannot drift apart. The filter itself is a small class with
no SWT in it, so the SQL it builds is unit-testable, which matters for the literals: a null cell
becomes `IS NULL` rather than `= ''`, a timestamp keeps its fraction of a second, numerics stay
unquoted.

One dependency: the Data grid is read-only, and read-only `TableView` currently opens a cell editor
on click and disables its selection and copy actions — #8360, which has a PR open. Part 1 is only
pleasant to use once that lands.


# Part 2 — Scoping the tree to the connection

The tree lists everything the server has: every catalog, every schema, system schemas included. On a
large server that is a lot of walking to reach the one table you connected for.

**The reason I think this matters more than tidiness** is the advisor. `AiAdvisorInclusion` already
carries a `description` documented as explaining "cost and sensitivity of this inclusion", and a
picker for choosing what goes to the model — so Hop already treats what an AI component may read as
something to be scoped deliberately. Database metadata is the obvious next thing it would want, and
today the only view of it is the whole server. As the advisor grows in that direction, an unscoped
tree is what it would have to read: a large context to pay for, and metadata from schemas the user
never intended to share. Scoping the tree first seems cheaper than scoping it afterwards.

Three steps, smallest first, all fed by information the connection already carries:

1. **Leave system schemas out** — `sys`, `INFORMATION_SCHEMA` and their equivalents. `IDatabase`
   could answer this per dialect; as a default method it costs no existing implementation anything,
   and dialects that care override it.
2. **Honour the catalog or schema set on the connection.** When a connection names one, the tree
   could show that rather than the whole server. The user picked it already.
3. **When the whole server is shown, say which one the connection points at** — the connection's own
   schema or catalog in bold. Small, but it is the difference between scanning and seeing.


## What I would like an opinion on

For Part 1: is the object tab the right home for the rows, or should the SQL tab grow a column panel
instead? That choice shapes most of the rest.

For Part 2: should scoping be a connection setting, a per-session toggle in the tree, or simply the
default with a way to expand to the whole server?

Happy to open a PR for either part once the direction is clear.

GitHub link: https://github.com/apache/hop/discussions/8644

----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org

Re: [D] Database perspective: exploring a table's data, and scoping the tree to the connection (hop)

["mattcasters (via GitHub)" <gi...@apache.org>]

GitHub user mattcasters added a comment to the discussion: Database perspective: exploring a table's data, and scoping the tree to the connection

Great ideas Frank. I usually just build the basics to set an architecture in place, like you noticed.  Throw it in a couple of issues and we'll tackle them for 2.20.  We'll add options so people can pick their favorite behavior.


GitHub link: https://github.com/apache/hop/discussions/8644#discussioncomment-18684829

----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org

Re: [D] Database perspective: exploring a table's data, and scoping the tree to the connection (hop)

["hansva (via GitHub)" <gi...@apache.org>]

GitHub user hansva added a comment to the discussion: Database perspective: exploring a table's data, and scoping the tree to the connection

The current version is just a first version.
I don't think we want to build a complete DBeaver inside of Hop, but the suggestions you are making sound good.

For part 1), I personally am more of a fan of an extra tab (DBeaver-style Properties, Data, Diagram), though they have both
the other options all make sense

Part 2)
For the sys/INFORMATION_SCHEMA and similar schemas, I would suggest a hide button, as it might be useful for some users (checking locks or processes)

Feel free to create feature requests, and we can discuss further on each item.

GitHub link: https://github.com/apache/hop/discussions/8644#discussioncomment-18654122

----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org