Continues on from a0afa9e, reducing the number of unnecessary requests made when table changes are saved.
Currently, when you open the "Edit table" side panel, make zero changes, and click "Save", an unnecessary request runs to:
- Alter the table's comment to its existing comment
- Alter the table's schema to its existing schema
- Alter the table's RLS enable status to its existing RLS enable status
This PR changes the payload so we only alter what has actually changed. It also adds some refactoring to define clearer types and add type safety for the three possible kinds of table save actions (create new, duplicate, update existing).
After this, there are still a few unnecessary column update actions happening on array columns; those will be fixed in a final PR.
There is an edge case interaction between the Postgres query parser and
MSSQL foreign tables, where the query parser may drop sort clauses that
are redundant with applied filters. This leads to invalid MSSQL syntax,
because the resulting query has a `limit` but no `sort`, and the user
sees a confusing error message.
This PR detects this edge case on MSSQL foreign tables. There are three
cases:
1. The user filters by a column, but there are still other columns
available for sorting. The default search for a sorting column will
leave out the filtered column.
2. The user filters by a column/several columns, and there are no more
columns that can be used for sorting. We stop the query and show an
admonition.
3. The user filters by a column, then tries to sort by the same column.
We stop the query and show an admonition.
* Updated to use optimized search columns when getting user counts
* added unit tests for query builders
* minor
* Nit fix keywords URL param not getting loaded into search input field
* removed user footer
* updated tests for upstream
* updated integration tests
* updated tests to be int for paginated
* updated schema
* updated types
* updated type imports
* updated type imports
* Apply suggestion from @avallete
Co-authored-by: Andrew Valleteau <avallete@users.noreply.github.com>
* updated literal import
* refactor: use common escaping (#40186)
refactor: use utils for sql escaping
---------
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
Co-authored-by: Andrew Valleteau <avallete@users.noreply.github.com>
* fix(policy editor): reduce number of table role access queries
On the Policies page, every single PolicyTableRow fires off its own
table roles access query (because each query is keyed to an individual
table). For users with many tables (400+), this causes the API to
rate-limit the dashboard.
Changed so that the table roles access query now fetches information for
the entire schema in one go, then organizes it by table.
* Nit refactors
* refactor: return minimal data from table access query
All we need to know is whether a given table has anon or authenticated
access, so just query for the list of tables that match that criteria
from information_schema.role_table_grants.
* refactor(policies page): more efficient sql query for getting anon/auth access tables
---------
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
* feat(pgmeta): filter by composite values
Current behavior:
Only single columns can be used as filters: `where version = 2`
New behavior:
Composite values can be used as filters: `where (id, version) = (1, 2)`
Motivation:
In order to correct an existing bug when fetching table rows, we need to
use cursor pagination on the primary key, but in order to handle
composite primary keys, we need to be able to filter by composite
values.
* refactor(pgmeta): allowlist in-tuple operators
* feat: optimized users page
* Update UI
* Reinstate footer with count if mode is freeform
* Simplify disabled sort in performance mode
* Clean
* Small fix
* Final fixes
* Shift users SQL query to packages/pg-meta
* Nit unrelated: Clear query params from useLogsUrlState when going to logs tab of a selected user
* Shift user count SQL and get count estimate SQL into packages/pg-meta
* Fix
* Nit
* Nit
* Minor nits
* Refactor UX for searching
---------
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
* Table editor cast column type to text if filtering with like based filter
* Update pg-meta tests
* Fix test cases for ~~ in advanced-query.test
* Fix tests in table-row-query.test
* fix: move table create update delete to query route
* chore: implement query to fetch a single table
* fix: retrieve table after update
* chore: assign type to update table payload
* chore: use updated table columns for edit
* chore: make executeSql castable with generic (#35685)
* Chore/refactor derivate more types from queries (#35687)
* chore: make executeSql castable with generic
* chore: derivate types from performed queries
- It allows to decouple more the frontend logic and the pg-meta/sql-query logic allowing to reduce the number of cast
and get closer types between what we do fetch and what we expect in our components
* fix: remove existing check
* chore: handle null comment and check
* fix: format check name as identifier
---------
Co-authored-by: avallete <andrew.valleteau@supabase.io>
Co-authored-by: Andrew Valleteau <avallete@users.noreply.github.com>
* chore(studio): move Query to pgMeta add tests
- Move the Query builder from studio to pgMeta
- Add e2e tests over the generated sql to ensure syntax and runtime
result over pg database
- fix bug with orde by for table with undefined column
* chore: add table-row-query to pgMeta and tests
* chore: fix query import path
* chore: reduce maxArraySize
* chore: use pg-meta getTableRowsSql implementation in studio
* chore: add truncation on large array fields
* chore: set ES target for lint
* chore: update comment
* chore: reduce test size for CI
* chore(studio): move Query to pgMeta add tests
- Move the Query builder from studio to pgMeta
- Add e2e tests over the generated sql to ensure syntax and runtime
result over pg database
- fix bug with orde by for table with undefined column
* chore: fix query import path
* chore: set ES target for lint
* chore: add github action for pg-meta test package
* chore: add tsconfig to sparse checkout
Charis