forked from go-jet/jet
-
Notifications
You must be signed in to change notification settings - Fork 0
/
query_set.go
117 lines (101 loc) · 3.63 KB
/
query_set.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
package postgres
import (
"context"
"database/sql"
"fmt"
"github.com/ag5/jet/v2/generator/metadata"
"github.com/ag5/jet/v2/qrm"
)
// postgresQuerySet is dialect query set for PostgreSQL
type postgresQuerySet struct{}
func (p postgresQuerySet) GetTablesMetaData(db *sql.DB, schemaName string, tableType metadata.TableType) ([]metadata.Table, error) {
query := `
SELECT table_name as "table.name"
FROM information_schema.tables
WHERE table_schema = $1 and table_type = $2
ORDER BY table_name;
`
var tables []metadata.Table
_, err := qrm.Query(context.Background(), db, query, []interface{}{schemaName, tableType}, &tables)
if err != nil {
return nil, fmt.Errorf("failed to query %s metadata: %w", tableType, err)
}
// add materialized views separately, because materialized views are not part of standard information schema
if tableType == metadata.ViewTable {
matViewQuery := `
select matviewname as "table.name"
from pg_matviews
where schemaname = $1;
`
var matViews []metadata.Table
_, err := qrm.Query(context.Background(), db, matViewQuery, []interface{}{schemaName}, &matViews)
if err != nil {
return nil, fmt.Errorf("failed to query materialized view metadata: %w", err)
}
tables = append(tables, matViews...)
}
for i := range tables {
tables[i].Columns, err = getColumnsMetaData(db, schemaName, tables[i].Name)
if err != nil {
return nil, fmt.Errorf("failed to query %s columns metadata: %w", tableType, err)
}
}
return tables, nil
}
func getColumnsMetaData(db *sql.DB, schemaName string, tableName string) ([]metadata.Column, error) {
query := `
select
attr.attname as "column.Name",
exists(
select 1
from pg_catalog.pg_index indx
where attr.attrelid = indx.indrelid and attr.attnum = any(indx.indkey) and indx.indisprimary
) as "column.IsPrimaryKey",
not attr.attnotnull as "column.isNullable",
attr.attgenerated = 's' as "column.isGenerated",
(case tp.typtype
when 'b' then 'base'
when 'd' then 'base'
when 'e' then 'enum'
when 'r' then 'range'
end) as "dataType.Kind",
(case when tp.typtype = 'd' then (select pg_type.typname from pg_catalog.pg_type where pg_type.oid = tp.typbasetype)
when tp.typcategory = 'A' then pg_catalog.format_type(attr.atttypid, attr.atttypmod)
else tp.typname
end) as "dataType.Name",
false as "dataType.isUnsigned"
from pg_catalog.pg_attribute as attr
join pg_catalog.pg_class as cls on cls.oid = attr.attrelid
join pg_catalog.pg_namespace as ns on ns.oid = cls.relnamespace
join pg_catalog.pg_type as tp on tp.oid = attr.atttypid
where
ns.nspname = $1 and
cls.relname = $2 and
not attr.attisdropped and
attr.attnum > 0
order by
attr.attnum;
`
var columns []metadata.Column
_, err := qrm.Query(context.Background(), db, query, []interface{}{schemaName, tableName}, &columns)
if err != nil {
return nil, fmt.Errorf("failed to query '%s' columns metadata: %w", tableName, err)
}
return columns, nil
}
func (p postgresQuerySet) GetEnumsMetaData(db *sql.DB, schemaName string) ([]metadata.Enum, error) {
query := `
SELECT t.typname as "enum.name",
e.enumlabel as "values"
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_enum e on t.oid = e.enumtypid
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE n.nspname = $1
ORDER BY n.nspname, t.typname, e.enumsortorder;`
var result []metadata.Enum
_, err := qrm.Query(context.Background(), db, query, []interface{}{schemaName}, &result)
if err != nil {
return nil, fmt.Errorf("failed to query enums metadata for schema '%s': %w", schemaName, err)
}
return result, nil
}