8000 feat: GET /views · ITninja04/postgres-meta@3a1c846 · GitHub
[go: up one dir, main page]
Skip to content

Commit 3a1c846

Browse files
soedirgosoedirgo
committed
feat: GET /views
1 parent dc40472 commit 3a1c846

File tree

11 files changed

+172
-2
lines changed

11 files changed

+172
-2
lines changed

‎src/lib/PostgresMeta.ts

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,7 @@ import PostgresMetaTables from './PostgresMetaTables'
1212
import PostgresMetaTriggers from './PostgresMetaTriggers'
1313
import PostgresMetaTypes from './PostgresMetaTypes'
1414
import PostgresMetaVersion from './PostgresMetaVersion'
15+
import PostgresMetaViews from './PostgresMetaViews'
1516
import { init } from './db'
1617
import { PostgresMetaResult } from './types'
1718
export default class PostgresMeta {
@@ -29,6 +30,7 @@ export default class PostgresMeta {
2930
triggers: PostgresMetaTriggers
3031
types: PostgresMetaTypes
3132
version: PostgresMetaVersion
33+
views: PostgresMetaViews
3234

3335
parse = Parser.Parse
3436
deparse = Parser.Deparse
@@ -50,5 +52,6 @@ export default class PostgresMeta {
5052
this.triggers = new PostgresMetaTriggers(this.query)
5153
this.types = new PostgresMetaTypes(this.query)
5254
this.version = new PostgresMetaVersion(this.query)
55+
this.views = new PostgresMetaViews(this.query)
5356
}
5457
}

‎src/lib/PostgresMetaViews.ts

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,34 @@
1+
import { literal } from 'pg-format'
2+
import { DEFAULT_SYSTEM_SCHEMAS } from './constants'
3+
import { viewsSql } from './sql'
4+
import { PostgresMetaResult, PostgresView } from './types'
5+
6+
export default class PostgresMetaViews {
7+
query: (sql: string) => Promise<PostgresMetaResult<any>>
8+
9+
constructor(query: (sql: string) => Promise<PostgresMetaResult<any>>) {
10+
this.query = query
11+
}
12+
13+
async list({
14+
includeSystemSchemas = false,
15+
limit,
16+
offset,
17+
}: {
18+
includeSystemSchemas?: boolean
19+
limit?: number
20+
offset?: number
21+
} = {}): Promise<PostgresMetaResult<PostgresView[]>> {
22+
let sql = viewsSql
23+
if (!includeSystemSchemas) {
24+
sql = `${sql} AND n.nspname NOT IN (${DEFAULT_SYSTEM_SCHEMAS.map(literal).join(',')})`
25+
}
26+
if (limit) {
27+
sql = `${sql} LIMIT ${limit}`
28+
}
29+
if (offset) {
30+
sql = `${sql} OFFSET ${offset}`
31+
}
32+
return await this.query(sql)
33+
}
34+
}

‎src/lib/sql/index.ts

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,3 +16,4 @@ export const tablesSql = readFileSync(resolve(__dirname, 'tables.sql'), 'utf-8')
1616
export const triggersSql = readFileSync(resolve(__dirname, 'triggers.sql'), 'utf-8')
1717
export const typesSql = readFileSync(resolve(__dirname, 'types.sql'), 'utf-8')
1818
export const versionSql = readFileSync(resolve(__dirname, 'version.sql'), 'utf-8')
19+
export const viewsSql = readFileSync(resolve(__dirname, 'views.sql'), 'utf-8')

‎src/lib/sql/views.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
SELECT
2+
c.oid :: int8 AS id,
3+
n.nspname AS schema,
4+
c.relname AS name,
5+
obj_description(c.oid) AS comment
6+
FROM
7+
pg_class c
8+
JOIN pg_namespace n ON n.oid = c.relnamespace
9+
WHERE
10+
c.relkind = 'v'

‎src/lib/types.ts

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -297,3 +297,11 @@ export const postgresVersionSchema = Type.Object({
297297
max_connections: Type.Integer(),
298298
})
299299
export type PostgresVersion = Static<typeof postgresVersionSchema>
300+
301+
export const postgresViewSchema = Type.Object({
302+
id: Type.Integer(),
303+
schema: Type.String(),
304+
name: Type.String(),
305+
comment: Type.Union([Type.String(), Type.Null()]),
306+
})
307+
export type PostgresView = Static<typeof postgresViewSchema>

‎src/server/routes/index.ts

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -17,16 +17,17 @@ export default async (fastify: FastifyInstance) => {
1717
done()
1818
})
1919

20-
fastify.register(require('./config'), { prefix: '/config' })
2120
fastify.register(require('./columns'), { prefix: '/columns' })
21+
fastify.register(require('./config'), { prefix: '/config' })
2222
fastify.register(require('./extensions'), { prefix: '/extensions' })
2323
fastify.register(require('./functions'), { prefix: '/functions' })
2424
fastify.register(require('./policies'), { prefix: '/policies' })
2525
fastify.register(require('./publications'), { prefix: '/publications' })
2626
fastify.register(require('./query'), { prefix: '/query' })
2727
fastify.register(require('./schemas'), { prefix: '/schemas' })
28+
fastify.register(require('./roles'), { prefix: '/roles' })
2829
fastify.register(require('./tables'), { prefix: '/tables' })
2930
fastify.register(require('./triggers'), { prefix: '/triggers' })
3031
fastify.register(require('./types'), { prefix: '/types' })
31-
fastify.register(require('./roles'), { prefix: '/roles' })
32+
fastify.register(require('./views'), { prefix: '/views' })
3233
}

‎src/server/routes/views.ts

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,31 @@
1+
import { FastifyInstance } from 'fastify'
2+
import { PostgresMeta } from '../../lib'
3+
import { DEFAULT_POOL_CONFIG } from '../constants'
4+
import { extractRequestForLogging } from '../utils'
5+
6+
export default async (fastify: FastifyInstance) => {
7+
fastify.get<{
8+
Headers: { pg: string }
9+
Querystring: {
10+
include_system_schemas?: string
11+
limit?: number
12+
offset?: number
13+
}
14+
}>('/', async (request, reply) => {
15+
const connectionString = request.headers.pg
16+
const includeSystemSchemas = request.query.include_system_schemas === 'true'
17+
const limit = request.query.limit
18+
const offset = request.query.offset
19+
20+
const pgMeta = new PostgresMeta({ ...DEFAULT_POOL_CONFIG, connectionString })
21+
const { data, error } = await pgMeta.views.list({ includeSystemSchemas, limit, offset })
22+
await pgMeta.end()
23+
if (error) {
24+
request.log.error({ error, request: extractRequestForLogging(request) })
25+
reply.code(500)
26+
return { error: error.message }
27+
}
28+
29+
return data
30+
})
31+
}

‎test/db/00-init.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -49,3 +49,5 @@ begin
4949
return new;
5050
end;
5151
$$ language plpgsql;
52+
53+
CREATE VIEW todos_view AS SELECT * FROM public.todos;

‎test/lib/index.test.ts

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,3 +13,4 @@ import './roles'
1313
import './policies'
1414
import './publications'
1515
import './triggers'
16+
import './views'

‎test/lib/roles.ts

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -209,6 +209,69 @@ test('list', async () => {
209209
"table_name": "users_audit",
210210
"with_hierarchy": false,
211211
},
212+
Object {
213+
"grantee": "postgres",
214+
"grantor": "postgres",
215+
"is_grantable": true,
216+
"privilege_type": "INSERT",
217+
"schema": "public",
218+
"table_name": "todos_view",
219+
"with_hierarchy": false,
220+
},
221+
Object {
222+
"grantee": "postgres",
223+
"grantor": "postgres",
224+
"is_grantable": true,
225+
"privilege_type": "SELECT",
226+
"schema": "public",
227+
"table_name": "todos_view",
228+
"with_hierarchy": true,
229+
},
230+
Object {
231+
"grantee": "postgres",
232+
"grantor": "postgres",
233+
"is_grantable": true,
234+
"privilege_type": "UPDATE",
235+
"schema": "public",
236+
"table_name": "todos_view",
237+
"with_hierarchy": false,
238+
},
239+
Object {
240+
"grantee": "postgres",
241+
"grantor": "postgres",
242+
"is_grantable": true,
243+
"privilege_type": "DELETE",
244+
"schema": "public",
245+
"table_name": "todos_view",
246+
"with_hierarchy": false,
247+
},
248+
Object {
249+
"grantee": "postgres",
250+
"grantor": "postgres",
251+
"is_grantable": true,
252+
"privilege_type": "TRUNCATE",
253+
"schema": "public",
254+
"table_name": "todos_view",
255+
"with_hierarchy": false,
256+
},
257+
Object {
258+
"grantee": "postgres",
259+
"grantor": "postgres",
260+
"is_grantable": true,
261+
"privilege_type": "REFERENCES",
262+
"schema": "public",
263+
"table_name": "todos_view",
264+
"with_hierarchy": false,
265+
},
266+
Object {
267+
"grantee": "postgres",
268+
"grantor": "postgres",
269+
"is_grantable": true,
270+
"privilege_type": "TRIGGER",
271+
"schema": "public",
272+
"table_name": "todos_view",
273+
"with_hierarchy": false,
274+
},
212275
Object {
213276
"grantee": "postgres",
214277
"grantor": "postgres",

‎test/lib/views.ts

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
import { pgMeta } from './utils'
2+
3+
test('list', async () => {
4+
const res = await pgMeta.views.list()
5+
expect(res.data?.find(({ name }) => name === 'todos_view')).toMatchInlineSnapshot(
6+
{ id: expect.any(Number) },
7+
`
8+
Object {
9+
"comment": null,
10+
"id": Any<Number>,
11+
"name": "todos_view",
12+
"schema": "public",
13+
}
14+
`
15+
)
16+
})

0 commit comments

Comments
 (0)
0