-
Notifications
You must be signed in to change notification settings - Fork 566
/
table_info.go
112 lines (104 loc) · 3.05 KB
/
table_info.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
package pachsql
import (
"context"
"database/sql"
"fmt"
"github.com/pachyderm/pachyderm/v2/src/internal/errors"
)
// TableInfo contains information about a SQL table
type TableInfo struct {
Driver string
Name string
Schema string
Columns []ColumnInfo
}
// ColumnInfo is information about a single column in a SQL table
type ColumnInfo struct {
Name string
DataType string
IsNullable bool
}
// GetTableInfo looks up information about the table using INFORMATION_SCHEMA
func GetTableInfo(ctx context.Context, db *DB, tableName string) (*TableInfo, error) {
readonly := true
if db.DriverName() == "snowflake" {
readonly = false
}
tx, err := db.BeginTxx(ctx, &sql.TxOptions{ReadOnly: readonly}) // TODO Snowflake doesn't support ReadOnly
if err != nil {
return nil, errors.EnsureStack(err)
}
defer tx.Rollback()
ti, err := GetTableInfoTx(tx, tableName)
if err != nil {
return nil, errors.EnsureStack(err)
}
return ti, errors.EnsureStack(tx.Rollback())
}
// GetTableInfoTx looks up information about the table using INFORMATION_SCHEMA
func GetTableInfoTx(tx *Tx, tablePath string) (*TableInfo, error) {
schemaName, tableName := SplitTableSchema(tx.DriverName(), tablePath)
if schemaName == "" {
// Check whether table is unique, and infer schema_name
if rows, err := tx.Query(fmt.Sprintf(`
SELECT lower(table_schema) as schema_name
FROM information_schema.tables
WHERE lower(table_name) = lower('%s')`, tableName)); err != nil {
return nil, errors.EnsureStack(err)
} else {
count := 0
for rows.Next() {
count++
if count > 1 {
return nil, errors.Errorf("table %s is not unique, please specify schema name", tableName)
}
if err = rows.Scan(&schemaName); err != nil {
return nil, errors.EnsureStack(err)
}
}
if err = rows.Err(); err != nil {
return nil, errors.EnsureStack(err)
}
}
}
q := fmt.Sprintf(`
SELECT
column_name,
upper(data_type),
CASE upper(is_nullable)
WHEN 'YES' THEN true
ELSE false
END,
numeric_precision,
numeric_scale
FROM information_schema.columns
WHERE upper(table_name) = upper('%s') AND upper(table_schema) = upper('%s')
ORDER BY ordinal_position`, tableName, schemaName)
// We use tx.Query, not tx.Select here because MySQL and Postgres have conflicting capitalization
// and sqlx complains about scanning using struct tags.
rows, err := tx.Query(q)
if err != nil {
return nil, errors.EnsureStack(err)
}
defer rows.Close()
var cinfos []ColumnInfo
var precision, scale sql.NullInt64
for rows.Next() {
var ci ColumnInfo
if err := rows.Scan(&ci.Name, &ci.DataType, &ci.IsNullable, &precision, &scale); err != nil {
return nil, errors.EnsureStack(err)
}
cinfos = append(cinfos, ci)
}
if err := rows.Err(); err != nil {
return nil, errors.EnsureStack(err)
}
return &TableInfo{Driver: tx.DriverName(), Name: tableName, Schema: schemaName, Columns: cinfos}, nil
}
func (t *TableInfo) ColumnNames() []string {
columns := make([]string, len(t.Columns))
for i := range t.Columns {
columns[i] = t.Columns[i].Name
}
return columns
}