mirror of
https://github.com/supabase/supabase.git
synced 2026-10-11 20:35:07 +03:00
Charis
df63ce3658
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.
304 lines
8.4 KiB
TypeScript
304 lines
8.4 KiB
TypeScript
import { afterAll, beforeAll, expect, test } from 'vitest'
|
|
import pgMeta from '../src/index'
|
|
import { cleanupRoot, createTestDatabase } from './db/utils'
|
|
|
|
beforeAll(async () => {
|
|
// Any global setup if needed
|
|
})
|
|
|
|
afterAll(async () => {
|
|
await cleanupRoot()
|
|
})
|
|
|
|
const withTestDatabase = (
|
|
name: string,
|
|
fn: (db: Awaited<ReturnType<typeof createTestDatabase>>) => Promise<void>
|
|
) => {
|
|
test(name, async () => {
|
|
const db = await createTestDatabase()
|
|
try {
|
|
await fn(db)
|
|
} finally {
|
|
await db.cleanup()
|
|
}
|
|
})
|
|
}
|
|
|
|
withTestDatabase('list foreign tables', async ({ executeQuery }) => {
|
|
const { sql: listSql, zod: listZod } = pgMeta.foreignTables.list()
|
|
const tables = listZod.parse(await executeQuery(listSql))
|
|
const foreignTable = tables.find(({ name }) => name === 'foreign_table')
|
|
expect(foreignTable).toMatchInlineSnapshot(
|
|
{ id: expect.any(Number) },
|
|
`
|
|
{
|
|
"columns": [
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "bigint",
|
|
"default_value": null,
|
|
"enums": [],
|
|
"format": "int8",
|
|
"id": "16451.1",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": false,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "id",
|
|
"ordinal_position": 1,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "text",
|
|
"default_value": null,
|
|
"enums": [],
|
|
"format": "text",
|
|
"id": "16451.2",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": true,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "name",
|
|
"ordinal_position": 2,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "USER-DEFINED",
|
|
"default_value": null,
|
|
"enums": [
|
|
"ACTIVE",
|
|
"INACTIVE",
|
|
],
|
|
"format": "user_status",
|
|
"id": "16451.3",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": true,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "status",
|
|
"ordinal_position": 3,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
],
|
|
"comment": null,
|
|
"foreign_data_wrapper_handler": "postgres_fdw_handler",
|
|
"foreign_data_wrapper_name": "postgres_fdw",
|
|
"foreign_server_name": "foreign_server",
|
|
"id": Any<Number>,
|
|
"name": "foreign_table",
|
|
"schema": "public",
|
|
}
|
|
`
|
|
)
|
|
})
|
|
|
|
withTestDatabase('list foreign tables without columns', async ({ executeQuery }) => {
|
|
const { sql: listSql, zod: listZod } = pgMeta.foreignTables.list({ includeColumns: false })
|
|
const tables = listZod.parse(await executeQuery(listSql))
|
|
const foreignTable = tables.find(({ name }) => name === 'foreign_table')
|
|
expect(foreignTable).toMatchInlineSnapshot(
|
|
{ id: expect.any(Number) },
|
|
`
|
|
{
|
|
"comment": null,
|
|
"foreign_data_wrapper_handler": "postgres_fdw_handler",
|
|
"foreign_data_wrapper_name": "postgres_fdw",
|
|
"foreign_server_name": "foreign_server",
|
|
"id": Any<Number>,
|
|
"name": "foreign_table",
|
|
"schema": "public",
|
|
}
|
|
`
|
|
)
|
|
})
|
|
|
|
withTestDatabase('retrieve foreign table by name', async ({ executeQuery }) => {
|
|
const { sql: retrieveSql, zod: retrieveZod } = pgMeta.foreignTables.retrieve({
|
|
name: 'foreign_table',
|
|
schema: 'public',
|
|
})
|
|
const table = retrieveZod.parse((await executeQuery(retrieveSql))[0])
|
|
expect(table).toMatchInlineSnapshot(
|
|
{ id: expect.any(Number) },
|
|
`
|
|
{
|
|
"columns": [
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "bigint",
|
|
"default_value": null,
|
|
"enums": [],
|
|
"format": "int8",
|
|
"id": "16451.1",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": false,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "id",
|
|
"ordinal_position": 1,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "text",
|
|
"default_value": null,
|
|
"enums": [],
|
|
"format": "text",
|
|
"id": "16451.2",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": true,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "name",
|
|
"ordinal_position": 2,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "USER-DEFINED",
|
|
"default_value": null,
|
|
"enums": [
|
|
"ACTIVE",
|
|
"INACTIVE",
|
|
],
|
|
"format": "user_status",
|
|
"id": "16451.3",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": true,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "status",
|
|
"ordinal_position": 3,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
],
|
|
"comment": null,
|
|
"foreign_data_wrapper_handler": "postgres_fdw_handler",
|
|
"foreign_data_wrapper_name": "postgres_fdw",
|
|
"foreign_server_name": "foreign_server",
|
|
"id": Any<Number>,
|
|
"name": "foreign_table",
|
|
"schema": "public",
|
|
}
|
|
`
|
|
)
|
|
})
|
|
|
|
withTestDatabase('retrieve foreign table by id', async ({ executeQuery }) => {
|
|
const { sql: retrieveSql, zod: retrieveZod } = pgMeta.foreignTables.retrieve({
|
|
id: 16451,
|
|
})
|
|
const table = retrieveZod.parse((await executeQuery(retrieveSql))[0])
|
|
expect(table).toMatchInlineSnapshot(
|
|
{ id: expect.any(Number) },
|
|
`
|
|
{
|
|
"columns": [
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "bigint",
|
|
"default_value": null,
|
|
"enums": [],
|
|
"format": "int8",
|
|
"id": "16451.1",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": false,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "id",
|
|
"ordinal_position": 1,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "text",
|
|
"default_value": null,
|
|
"enums": [],
|
|
"format": "text",
|
|
"id": "16451.2",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": true,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "name",
|
|
"ordinal_position": 2,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
{
|
|
"check": null,
|
|
"comment": null,
|
|
"data_type": "USER-DEFINED",
|
|
"default_value": null,
|
|
"enums": [
|
|
"ACTIVE",
|
|
"INACTIVE",
|
|
],
|
|
"format": "user_status",
|
|
"id": "16451.3",
|
|
"identity_generation": null,
|
|
"is_generated": false,
|
|
"is_identity": false,
|
|
"is_nullable": true,
|
|
"is_unique": false,
|
|
"is_updatable": true,
|
|
"name": "status",
|
|
"ordinal_position": 3,
|
|
"schema": "public",
|
|
"table": "foreign_table",
|
|
"table_id": 16451,
|
|
},
|
|
],
|
|
"comment": null,
|
|
"foreign_data_wrapper_handler": "postgres_fdw_handler",
|
|
"foreign_data_wrapper_name": "postgres_fdw",
|
|
"foreign_server_name": "foreign_server",
|
|
"id": Any<Number>,
|
|
"name": "foreign_table",
|
|
"schema": "public",
|
|
}
|
|
`
|
|
)
|
|
})
|