122 lines
3.4 KiB
Go
122 lines
3.4 KiB
Go
package system
|
|
|
|
import (
|
|
"context"
|
|
|
|
"gorm.io/gorm"
|
|
)
|
|
|
|
// AutoCodePgsql PostgreSQL适配器
|
|
type AutoCodePgsql struct {
|
|
db *gorm.DB
|
|
dbList map[string]*gorm.DB
|
|
}
|
|
|
|
// NewAutoCodePgsql 创建PostgreSQL适配器
|
|
func NewAutoCodePgsql(db *gorm.DB, dbList map[string]*gorm.DB) *AutoCodePgsql {
|
|
return &AutoCodePgsql{
|
|
db: db,
|
|
dbList: dbList,
|
|
}
|
|
}
|
|
|
|
// GetDB 获取数据库的所有数据库名
|
|
func (a *AutoCodePgsql) GetDB(ctx context.Context) ([]Db, error) {
|
|
var entities []Db
|
|
sql := `SELECT datname as database FROM pg_database WHERE datistemplate = false`
|
|
err := a.db.WithContext(ctx).Raw(sql).Scan(&entities).Error
|
|
return entities, err
|
|
}
|
|
|
|
// GetTables 获取数据库的所有表名
|
|
func (a *AutoCodePgsql) GetTables(ctx context.Context, dbName string) ([]Table, error) {
|
|
var entities []Table
|
|
sql := `select table_name as table_name from information_schema.tables where table_catalog = ? and table_schema = ?`
|
|
err := a.db.WithContext(ctx).Raw(sql, dbName, "public").Scan(&entities).Error
|
|
return entities, err
|
|
}
|
|
|
|
// GetColumn 获取指定数据库和指定数据表的所有字段名,类型值等
|
|
func (a *AutoCodePgsql) GetColumn(ctx context.Context, tableName, dbName string) ([]Column, error) {
|
|
sql := `
|
|
SELECT
|
|
psc.COLUMN_NAME AS COLUMN_NAME,
|
|
psc.udt_name AS data_type,
|
|
CASE
|
|
psc.udt_name
|
|
WHEN 'text' THEN
|
|
concat_ws ( '', '', psc.CHARACTER_MAXIMUM_LENGTH )
|
|
WHEN 'varchar' THEN
|
|
concat_ws ( '', '', psc.CHARACTER_MAXIMUM_LENGTH )
|
|
WHEN 'smallint' THEN
|
|
concat_ws ( ',', psc.NUMERIC_PRECISION, psc.NUMERIC_SCALE )
|
|
WHEN 'decimal' THEN
|
|
concat_ws ( ',', psc.NUMERIC_PRECISION, psc.NUMERIC_SCALE )
|
|
WHEN 'integer' THEN
|
|
concat_ws ( '', '', psc.NUMERIC_PRECISION )
|
|
WHEN 'int4' THEN
|
|
concat_ws ( '', '', psc.NUMERIC_PRECISION )
|
|
WHEN 'int8' THEN
|
|
concat_ws ( '', '', psc.NUMERIC_PRECISION )
|
|
WHEN 'bigint' THEN
|
|
concat_ws ( '', '', psc.NUMERIC_PRECISION )
|
|
WHEN 'timestamp' THEN
|
|
concat_ws ( '', '', psc.datetime_precision )
|
|
ELSE ''
|
|
END AS data_type_long,
|
|
(
|
|
SELECT
|
|
pd.description
|
|
FROM
|
|
pg_description pd
|
|
WHERE
|
|
(pd.objoid,pd.objsubid) in (
|
|
SELECT pa.attrelid,pa.attnum
|
|
FROM
|
|
pg_attribute pa
|
|
WHERE pa.attrelid = ( SELECT oid FROM pg_class pc WHERE
|
|
pc.relname = psc.table_name
|
|
)
|
|
and attname = psc.column_name
|
|
)
|
|
) AS column_comment,
|
|
(
|
|
SELECT
|
|
COUNT(*)
|
|
FROM
|
|
pg_constraint
|
|
WHERE
|
|
contype = 'p'
|
|
AND conrelid = (
|
|
SELECT
|
|
oid
|
|
FROM
|
|
pg_class
|
|
WHERE
|
|
relname = psc.table_name
|
|
)
|
|
AND conkey::int[] @> ARRAY[(
|
|
SELECT
|
|
attnum::integer
|
|
FROM
|
|
pg_attribute
|
|
WHERE
|
|
attrelid = conrelid
|
|
AND attname = psc.column_name
|
|
)]
|
|
) > 0 AS primary_key,
|
|
psc.ordinal_position
|
|
FROM
|
|
INFORMATION_SCHEMA.COLUMNS psc
|
|
WHERE
|
|
table_catalog = ?
|
|
AND table_schema = 'public'
|
|
AND TABLE_NAME = ?
|
|
ORDER BY
|
|
psc.ordinal_position;
|
|
`
|
|
var entities []Column
|
|
err := a.db.WithContext(ctx).Raw(sql, dbName, tableName).Scan(&entities).Error
|
|
return entities, err
|
|
}
|