| 1 | // SPDX-License-Identifier: GPL-3.0-or-later |
| 2 | |
| 3 | package sql |
| 4 | |
| 5 | import ( |
| 6 | "context" |
| 7 | "database/sql" |
| 8 | "errors" |
| 9 | "strings" |
| 10 | "time" |
| 11 | |
| 12 | "github.com/netdata/netdata/go/plugins/pkg/funcapi" |
| 13 | "github.com/netdata/netdata/go/plugins/plugin/framework/collectorapi" |
| 14 | ) |
| 15 | |
| 16 | type funcTable struct { |
| 17 | collector *Collector |
| 18 | } |
| 19 | |
| 20 | func newFuncTable(c *Collector) *funcTable { |
| 21 | return &funcTable{collector: c} |
| 22 | } |
| 23 | |
| 24 | var _ funcapi.MethodHandler = (*funcTable)(nil) |
| 25 | |
| 26 | // sqlJobMethods returns method configs for a specific SQL job. |
| 27 | // Each configured function becomes a separate method: "jobName:functionID" |
| 28 | // This results in functions like "sql:postgres_test:active-queries" |
| 29 | func sqlJobMethods(job collectorapi.RuntimeJob) []funcapi.MethodConfig { |
| 30 | c, ok := job.Collector().(*Collector) |
| 31 | if !ok || len(c.Config.Functions) == 0 { |
| 32 | return nil |
| 33 | } |
| 34 | |
| 35 | methods := make([]funcapi.MethodConfig, 0, len(c.Config.Functions)) |
| 36 | for _, fn := range c.Config.Functions { |
| 37 | // Method ID format: "jobName:functionID" (e.g., "postgres_test:active-queries") |
| 38 | // Full function name will be: "sql:postgres_test:active-queries" |
| 39 | methodID := job.Name() + ":" + fn.ID |
| 40 | |
| 41 | methodName := fn.derivedName() |
| 42 | help := fn.Description |
| 43 | if help == "" { |
| 44 | help = "Execute SQL query: " + fn.ID |
| 45 | } |
| 46 | |
| 47 | methods = append(methods, funcapi.MethodConfig{ |
| 48 | ID: methodID, |
| 49 | Name: methodName, |
| 50 | Help: help, |
| 51 | UpdateEvery: 10, |
| 52 | RequireCloud: true, |
| 53 | }) |
| 54 | } |
| 55 | |
| 56 | return methods |
| 57 | } |
| 58 | |
| 59 | func sqlMethodHandler(job collectorapi.RuntimeJob) funcapi.MethodHandler { |
| 60 | c, ok := job.Collector().(*Collector) |
| 61 | if !ok { |
| 62 | return nil |
| 63 | } |
| 64 | return c.funcTable |
| 65 | } |
| 66 | |
| 67 | func (f *funcTable) Cleanup(context.Context) { |
| 68 | // No-op: DB connection managed by collector |
| 69 | } |
| 70 | |
| 71 | func (f *funcTable) MethodParams(ctx context.Context, method string) ([]funcapi.ParamConfig, error) { |
| 72 | // Each function is now a separate method endpoint, no __function selector needed |
| 73 | return nil, nil |
| 74 | } |
| 75 | |
| 76 | func (f *funcTable) Handle(ctx context.Context, method string, params funcapi.ResolvedParams) *funcapi.FunctionResponse { |
| 77 | // Check if collector is shutting down (fast path) |
| 78 | if f.collector.dbCtx != nil && f.collector.dbCtx.Err() != nil { |
| 79 | return funcapi.ErrorResponse(503, "collector is shutting down") |
| 80 | } |
| 81 | |
| 82 | // Acquire read lock to prevent DB close during query |
| 83 | f.collector.dbMu.RLock() |
| 84 | defer f.collector.dbMu.RUnlock() |
| 85 | |
| 86 | if f.collector.db == nil { |
| 87 | return funcapi.ErrorResponse(503, "database connection not initialized") |
| 88 | } |
| 89 | |
| 90 | // Method format is "jobName:functionID" (e.g., "postgres_test:active-queries") |
| 91 | // Extract the functionID part after the last colon |
| 92 | functionID := method |
| 93 | if idx := strings.LastIndex(method, ":"); idx != -1 { |
| 94 | functionID = method[idx+1:] |
| 95 | } |
| 96 | |
| 97 | funcCfg := f.findFunction(functionID) |
| 98 | if funcCfg == nil { |
| 99 | return funcapi.ErrorResponse(404, "unknown function: %s", functionID) |
| 100 | } |
| 101 | |
| 102 | // Merge request context with collector's shutdown context |
| 103 | // Query cancels if either request times out OR collector shuts down |
| 104 | queryCtx, queryCancel := context.WithCancel(ctx) |
| 105 | defer queryCancel() |
| 106 | |
| 107 | if f.collector.dbCtx != nil { |
| 108 | stop := context.AfterFunc(f.collector.dbCtx, queryCancel) |
| 109 | defer stop() |
| 110 | } |
| 111 | |
| 112 | return f.executeFunction(queryCtx, funcCfg) |
| 113 | } |
| 114 | |
| 115 | func (f *funcTable) findFunction(id string) *ConfigFunction { |
| 116 | for i := range f.collector.Config.Functions { |
| 117 | if f.collector.Config.Functions[i].ID == id { |
| 118 | return &f.collector.Config.Functions[i] |
| 119 | } |
| 120 | } |
| 121 | return nil |
| 122 | } |
| 123 | |
| 124 | func (f *funcTable) executeFunction(ctx context.Context, cfg *ConfigFunction) *funcapi.FunctionResponse { |
| 125 | timeout := f.collector.Timeout.Duration() |
| 126 | if cfg.Timeout.Duration() > 0 { |
| 127 | timeout = cfg.Timeout.Duration() |
| 128 | } |
| 129 | queryCtx, cancel := context.WithTimeout(ctx, timeout) |
| 130 | defer cancel() |
| 131 | |
| 132 | rows, err := f.collector.db.QueryContext(queryCtx, cfg.Query) |
| 133 | if err != nil { |
| 134 | if errors.Is(queryCtx.Err(), context.DeadlineExceeded) { |
| 135 | return funcapi.ErrorResponse(504, "query timeout after %v", timeout) |
| 136 | } |
| 137 | return funcapi.ErrorResponse(500, "query failed: %v", err) |
| 138 | } |
| 139 | defer rows.Close() |
| 140 | |
| 141 | colTypes, err := rows.ColumnTypes() |
| 142 | if err != nil { |
| 143 | return funcapi.ErrorResponse(500, "failed to get column types: %v", err) |
| 144 | } |
| 145 | |
| 146 | sortDesc := cfg.DefaultSortDesc == nil || *cfg.DefaultSortDesc |
| 147 | columns := f.buildColumnMetadata(colTypes, cfg.Columns, cfg.DefaultSort, sortDesc) |
| 148 | |
| 149 | limit := cfg.Limit |
| 150 | if limit <= 0 { |
| 151 | limit = defaultFunctionLimit |
| 152 | } |
| 153 | if limit > maxFunctionLimit { |
| 154 | limit = maxFunctionLimit |
| 155 | } |
| 156 | |
| 157 | data := make([][]any, 0, limit) |
| 158 | for rows.Next() && len(data) < limit { |
| 159 | row, err := f.scanRow(rows, len(colTypes)) |
| 160 | if err != nil { |
| 161 | f.collector.Warningf("scan row failed: %v", err) |
| 162 | continue |
| 163 | } |
| 164 | data = append(data, row) |
| 165 | } |
| 166 | |
| 167 | if err := rows.Err(); err != nil { |
| 168 | if errors.Is(queryCtx.Err(), context.DeadlineExceeded) { |
| 169 | return funcapi.ErrorResponse(504, "query timeout during iteration") |
| 170 | } |
| 171 | return funcapi.ErrorResponse(500, "row iteration failed: %v", err) |
| 172 | } |
| 173 | |
| 174 | defaultSort := cfg.DefaultSort |
| 175 | if defaultSort != "" { |
| 176 | found := false |
| 177 | for _, ct := range colTypes { |
| 178 | if ct.Name() == defaultSort { |
| 179 | found = true |
| 180 | break |
| 181 | } |
| 182 | } |
| 183 | if !found { |
| 184 | f.collector.Warningf("function %q: default_sort column %q not in query results", cfg.ID, defaultSort) |
| 185 | defaultSort = "" |
| 186 | } |
| 187 | } |
| 188 | |
| 189 | return &funcapi.FunctionResponse{ |
| 190 | Status: 200, |
| 191 | Help: cfg.Description, |
| 192 | Columns: columns, |
| 193 | Data: data, |
| 194 | DefaultSortColumn: defaultSort, |
| 195 | } |
| 196 | } |
| 197 | |
| 198 | func (f *funcTable) scanRow(rows *sql.Rows, numCols int) ([]any, error) { |
| 199 | values := make([]any, numCols) |
| 200 | ptrs := make([]any, numCols) |
| 201 | for i := range values { |
| 202 | ptrs[i] = &values[i] |
| 203 | } |
| 204 | if err := rows.Scan(ptrs...); err != nil { |
| 205 | return nil, err |
| 206 | } |
| 207 | |
| 208 | for i, v := range values { |
| 209 | values[i] = normalizeValue(v) |
| 210 | } |
| 211 | return values, nil |
| 212 | } |
| 213 | |
| 214 | func normalizeValue(v any) any { |
| 215 | if v == nil { |
| 216 | return nil |
| 217 | } |
| 218 | switch val := v.(type) { |
| 219 | case []byte: |
| 220 | return string(val) |
| 221 | case time.Time: |
| 222 | return val.UnixMilli() |
| 223 | case int: |
| 224 | return int64(val) |
| 225 | case int32: |
| 226 | return int64(val) |
| 227 | case float32: |
| 228 | return float64(val) |
| 229 | default: |
| 230 | return v |
| 231 | } |
| 232 | } |
| 233 | |
| 234 | // typeMapping maps database type names to funcapi field types. |
| 235 | // Covers MySQL, PostgreSQL (pgx), SQL Server, and Oracle drivers. |
| 236 | var typeMapping = map[string]funcapi.FieldType{ |
| 237 | // MySQL (uppercase) |
| 238 | "INT": funcapi.FieldTypeInteger, |
| 239 | "BIGINT": funcapi.FieldTypeInteger, |
| 240 | "TINYINT": funcapi.FieldTypeInteger, |
| 241 | "SMALLINT": funcapi.FieldTypeInteger, |
| 242 | "MEDIUMINT": funcapi.FieldTypeInteger, |
| 243 | "FLOAT": funcapi.FieldTypeFloat, |
| 244 | "DOUBLE": funcapi.FieldTypeFloat, |
| 245 | "DECIMAL": funcapi.FieldTypeFloat, |
| 246 | "VARCHAR": funcapi.FieldTypeString, |
| 247 | "CHAR": funcapi.FieldTypeString, |
| 248 | "TEXT": funcapi.FieldTypeString, |
| 249 | "DATETIME": funcapi.FieldTypeTimestamp, |
| 250 | "TIMESTAMP": funcapi.FieldTypeTimestamp, |
| 251 | "DATE": funcapi.FieldTypeTimestamp, |
| 252 | "TIME": funcapi.FieldTypeDuration, |
| 253 | |
| 254 | // PostgreSQL (pgx) - lowercase |
| 255 | "int2": funcapi.FieldTypeInteger, |
| 256 | "int4": funcapi.FieldTypeInteger, |
| 257 | "int8": funcapi.FieldTypeInteger, |
| 258 | "smallint": funcapi.FieldTypeInteger, |
| 259 | "integer": funcapi.FieldTypeInteger, |
| 260 | "bigint": funcapi.FieldTypeInteger, |
| 261 | "float4": funcapi.FieldTypeFloat, |
| 262 | "float8": funcapi.FieldTypeFloat, |
| 263 | "numeric": funcapi.FieldTypeFloat, |
| 264 | "decimal": funcapi.FieldTypeFloat, |
| 265 | "varchar": funcapi.FieldTypeString, |
| 266 | "char": funcapi.FieldTypeString, |
| 267 | "text": funcapi.FieldTypeString, |
| 268 | "bpchar": funcapi.FieldTypeString, |
| 269 | "timestamp": funcapi.FieldTypeTimestamp, |
| 270 | "timestamptz": funcapi.FieldTypeTimestamp, |
| 271 | "date": funcapi.FieldTypeTimestamp, |
| 272 | "bool": funcapi.FieldTypeBoolean, |
| 273 | "boolean": funcapi.FieldTypeBoolean, |
| 274 | "interval": funcapi.FieldTypeDuration, |
| 275 | |
| 276 | // SQL Server |
| 277 | "NVARCHAR": funcapi.FieldTypeString, |
| 278 | "NCHAR": funcapi.FieldTypeString, |
| 279 | "DATETIME2": funcapi.FieldTypeTimestamp, |
| 280 | "BIT": funcapi.FieldTypeBoolean, |
| 281 | "REAL": funcapi.FieldTypeFloat, |
| 282 | |
| 283 | // Oracle |
| 284 | "VARCHAR2": funcapi.FieldTypeString, |
| 285 | "NVARCHAR2": funcapi.FieldTypeString, |
| 286 | "CLOB": funcapi.FieldTypeString, |
| 287 | "NUMBER": funcapi.FieldTypeFloat, // Could be int, safer as float |
| 288 | "BINARY_FLOAT": funcapi.FieldTypeFloat, |
| 289 | "BINARY_DOUBLE": funcapi.FieldTypeFloat, |
| 290 | } |
| 291 | |
| 292 | func inferType(dbTypeName string) funcapi.FieldType { |
| 293 | if t, ok := typeMapping[dbTypeName]; ok { |
| 294 | return t |
| 295 | } |
| 296 | if t, ok := typeMapping[strings.ToUpper(dbTypeName)]; ok { |
| 297 | return t |
| 298 | } |
| 299 | if t, ok := typeMapping[strings.ToLower(dbTypeName)]; ok { |
| 300 | return t |
| 301 | } |
| 302 | return funcapi.FieldTypeString |
| 303 | } |
| 304 | |
| 305 | func parseFieldType(s string) funcapi.FieldType { |
| 306 | switch strings.ToLower(s) { |
| 307 | case "integer": |
| 308 | return funcapi.FieldTypeInteger |
| 309 | case "float": |
| 310 | return funcapi.FieldTypeFloat |
| 311 | case "boolean": |
| 312 | return funcapi.FieldTypeBoolean |
| 313 | case "duration": |
| 314 | return funcapi.FieldTypeDuration |
| 315 | case "timestamp": |
| 316 | return funcapi.FieldTypeTimestamp |
| 317 | default: |
| 318 | return funcapi.FieldTypeString |
| 319 | } |
| 320 | } |
| 321 | |
| 322 | func deriveTransform(fieldType funcapi.FieldType) funcapi.FieldTransform { |
| 323 | switch fieldType { |
| 324 | case funcapi.FieldTypeInteger, funcapi.FieldTypeFloat: |
| 325 | return funcapi.FieldTransformNumber |
| 326 | case funcapi.FieldTypeDuration: |
| 327 | return funcapi.FieldTransformDuration |
| 328 | case funcapi.FieldTypeTimestamp: |
| 329 | return funcapi.FieldTransformDatetime |
| 330 | default: |
| 331 | return funcapi.FieldTransformNone |
| 332 | } |
| 333 | } |
| 334 | |
| 335 | func deriveFilterSummary(fieldType funcapi.FieldType) (funcapi.FieldFilter, funcapi.FieldSummary) { |
| 336 | switch fieldType { |
| 337 | case funcapi.FieldTypeInteger, funcapi.FieldTypeFloat, funcapi.FieldTypeDuration: |
| 338 | return funcapi.FieldFilterRange, funcapi.FieldSummarySum |
| 339 | case funcapi.FieldTypeTimestamp: |
| 340 | return funcapi.FieldFilterRange, funcapi.FieldSummaryMax |
| 341 | case funcapi.FieldTypeBoolean: |
| 342 | return funcapi.FieldFilterMultiselect, funcapi.FieldSummaryCount |
| 343 | default: |
| 344 | return funcapi.FieldFilterMultiselect, funcapi.FieldSummaryCount |
| 345 | } |
| 346 | } |
| 347 | |
| 348 | // buildColumnMetadata constructs column definitions from SQL query results. |
| 349 | // |
| 350 | // This uses funcapi.Column directly instead of funcapi.ColumnMeta because: |
| 351 | // - Columns are discovered dynamically at runtime from query results |
| 352 | // - There are no static, pre-defined columns to embed ColumnMeta into |
| 353 | // - ColumnMeta is designed for collectors with static columns and custom Value extractors |
| 354 | func (f *funcTable) buildColumnMetadata(colTypes []*sql.ColumnType, overrides map[string]ConfigFuncColumn, defaultSort string, sortDesc bool) map[string]any { |
| 355 | columns := make(map[string]any, len(colTypes)) |
| 356 | |
| 357 | for i, ct := range colTypes { |
| 358 | colName := ct.Name() |
| 359 | |
| 360 | fieldType := inferType(ct.DatabaseTypeName()) |
| 361 | |
| 362 | override, hasOverride := overrides[colName] |
| 363 | if hasOverride && override.Type != "" { |
| 364 | fieldType = parseFieldType(override.Type) |
| 365 | } |
| 366 | |
| 367 | transform := deriveTransform(fieldType) |
| 368 | filter, summary := deriveFilterSummary(fieldType) |
| 369 | |
| 370 | visible := true |
| 371 | if hasOverride && override.Visible != nil { |
| 372 | visible = *override.Visible |
| 373 | } |
| 374 | |
| 375 | sortable := true |
| 376 | if hasOverride && override.Sortable != nil { |
| 377 | sortable = *override.Sortable |
| 378 | } |
| 379 | |
| 380 | sort := funcapi.FieldSortAscending |
| 381 | if colName == defaultSort && sortDesc { |
| 382 | sort = funcapi.FieldSortDescending |
| 383 | } |
| 384 | |
| 385 | units := "" |
| 386 | if hasOverride { |
| 387 | units = override.Units |
| 388 | } |
| 389 | |
| 390 | tooltip := colName |
| 391 | if hasOverride && override.Tooltip != "" { |
| 392 | tooltip = override.Tooltip |
| 393 | } |
| 394 | |
| 395 | col := funcapi.Column{ |
| 396 | Index: i, |
| 397 | Name: tooltip, |
| 398 | Type: fieldType, |
| 399 | Units: units, |
| 400 | Visible: visible, |
| 401 | Sortable: sortable, |
| 402 | Sort: sort, |
| 403 | Filter: filter, |
| 404 | Summary: summary, |
| 405 | ValueOptions: funcapi.ValueOptions{ |
| 406 | Transform: transform, |
| 407 | }, |
| 408 | } |
| 409 | columns[colName] = col.BuildColumn() |
| 410 | } |
| 411 | |
| 412 | return columns |
| 413 | } |