1package sqlstore 2 3import ( 4 "bytes" 5 "context" 6 "errors" 7 "fmt" 8 "strings" 9 "time" 10 11 "github.com/grafana/grafana/pkg/bus" 12 "github.com/grafana/grafana/pkg/models" 13 "github.com/grafana/grafana/pkg/util" 14) 15 16func (ss *SQLStore) DeleteAlertNotification(ctx context.Context, cmd *models.DeleteAlertNotificationCommand) error { 17 return inTransaction(func(sess *DBSession) error { 18 sql := "DELETE FROM alert_notification WHERE alert_notification.org_id = ? AND alert_notification.id = ?" 19 res, err := sess.Exec(sql, cmd.OrgId, cmd.Id) 20 if err != nil { 21 return err 22 } 23 rowsAffected, err := res.RowsAffected() 24 if err != nil { 25 return err 26 } 27 28 if rowsAffected == 0 { 29 return models.ErrAlertNotificationNotFound 30 } 31 32 if _, err := sess.Exec("DELETE FROM alert_notification_state WHERE alert_notification_state.org_id = ? AND alert_notification_state.notifier_id = ?", cmd.OrgId, cmd.Id); err != nil { 33 return err 34 } 35 36 return nil 37 }) 38} 39 40func (ss *SQLStore) DeleteAlertNotificationWithUid(ctx context.Context, cmd *models.DeleteAlertNotificationWithUidCommand) error { 41 existingNotification := &models.GetAlertNotificationsWithUidQuery{OrgId: cmd.OrgId, Uid: cmd.Uid} 42 if err := getAlertNotificationWithUidInternal(ctx, existingNotification, newSession(context.Background())); err != nil { 43 return err 44 } 45 46 if existingNotification.Result == nil { 47 return models.ErrAlertNotificationNotFound 48 } 49 50 cmd.DeletedAlertNotificationId = existingNotification.Result.Id 51 deleteCommand := &models.DeleteAlertNotificationCommand{ 52 Id: existingNotification.Result.Id, 53 OrgId: existingNotification.Result.OrgId, 54 } 55 if err := bus.DispatchCtx(ctx, deleteCommand); err != nil { 56 return err 57 } 58 59 return nil 60} 61 62func (ss *SQLStore) GetAlertNotifications(ctx context.Context, query *models.GetAlertNotificationsQuery) error { 63 return getAlertNotificationInternal(ctx, query, newSession(context.Background())) 64} 65 66func (ss *SQLStore) addAlertNotificationUidByIdHandler() { 67 bus.AddHandlerCtx("sql", ss.GetAlertNotificationUidWithId) 68} 69 70func (ss *SQLStore) GetAlertNotificationUidWithId(ctx context.Context, query *models.GetAlertNotificationUidQuery) error { 71 cacheKey := newAlertNotificationUidCacheKey(query.OrgId, query.Id) 72 73 if cached, found := ss.CacheService.Get(cacheKey); found { 74 query.Result = cached.(string) 75 return nil 76 } 77 78 err := getAlertNotificationUidInternal(ctx, query, newSession(context.Background())) 79 if err != nil { 80 return err 81 } 82 83 ss.CacheService.Set(cacheKey, query.Result, -1) // Infinite, never changes 84 85 return nil 86} 87 88func newAlertNotificationUidCacheKey(orgID, notificationId int64) string { 89 return fmt.Sprintf("notification-uid-by-org-%d-and-id-%d", orgID, notificationId) 90} 91 92func (ss *SQLStore) GetAlertNotificationsWithUid(ctx context.Context, query *models.GetAlertNotificationsWithUidQuery) error { 93 return getAlertNotificationWithUidInternal(ctx, query, newSession(context.Background())) 94} 95 96func (ss *SQLStore) GetAllAlertNotifications(ctx context.Context, query *models.GetAllAlertNotificationsQuery) error { 97 results := make([]*models.AlertNotification, 0) 98 if err := x.Where("org_id = ?", query.OrgId).Asc("name").Find(&results); err != nil { 99 return err 100 } 101 102 query.Result = results 103 return nil 104} 105 106func (ss *SQLStore) GetAlertNotificationsWithUidToSend(ctx context.Context, query *models.GetAlertNotificationsWithUidToSendQuery) error { 107 var sql bytes.Buffer 108 params := make([]interface{}, 0) 109 110 sql.WriteString(`SELECT 111 alert_notification.id, 112 alert_notification.uid, 113 alert_notification.org_id, 114 alert_notification.name, 115 alert_notification.type, 116 alert_notification.created, 117 alert_notification.updated, 118 alert_notification.settings, 119 alert_notification.secure_settings, 120 alert_notification.is_default, 121 alert_notification.disable_resolve_message, 122 alert_notification.send_reminder, 123 alert_notification.frequency 124 FROM alert_notification 125 `) 126 127 sql.WriteString(` WHERE alert_notification.org_id = ?`) 128 params = append(params, query.OrgId) 129 130 sql.WriteString(` AND ((alert_notification.is_default = ?)`) 131 params = append(params, dialect.BooleanStr(true)) 132 133 if len(query.Uids) > 0 { 134 sql.WriteString(` OR alert_notification.uid IN (?` + strings.Repeat(",?", len(query.Uids)-1) + ")") 135 for _, v := range query.Uids { 136 params = append(params, v) 137 } 138 } 139 sql.WriteString(`)`) 140 141 results := make([]*models.AlertNotification, 0) 142 if err := x.SQL(sql.String(), params...).Find(&results); err != nil { 143 return err 144 } 145 146 query.Result = results 147 return nil 148} 149 150func getAlertNotificationUidInternal(ctx context.Context, query *models.GetAlertNotificationUidQuery, sess *DBSession) error { 151 var sql bytes.Buffer 152 params := make([]interface{}, 0) 153 154 sql.WriteString(`SELECT 155 alert_notification.uid 156 FROM alert_notification 157 `) 158 159 sql.WriteString(` WHERE alert_notification.org_id = ?`) 160 params = append(params, query.OrgId) 161 162 sql.WriteString(` AND alert_notification.id = ?`) 163 params = append(params, query.Id) 164 165 results := make([]string, 0) 166 if err := sess.SQL(sql.String(), params...).Find(&results); err != nil { 167 return err 168 } 169 170 if len(results) == 0 { 171 return models.ErrAlertNotificationFailedTranslateUniqueID 172 } 173 174 query.Result = results[0] 175 176 return nil 177} 178 179func getAlertNotificationInternal(ctx context.Context, query *models.GetAlertNotificationsQuery, sess *DBSession) error { 180 var sql bytes.Buffer 181 params := make([]interface{}, 0) 182 183 sql.WriteString(`SELECT 184 alert_notification.id, 185 alert_notification.uid, 186 alert_notification.org_id, 187 alert_notification.name, 188 alert_notification.type, 189 alert_notification.created, 190 alert_notification.updated, 191 alert_notification.settings, 192 alert_notification.secure_settings, 193 alert_notification.is_default, 194 alert_notification.disable_resolve_message, 195 alert_notification.send_reminder, 196 alert_notification.frequency 197 FROM alert_notification 198 `) 199 200 sql.WriteString(` WHERE alert_notification.org_id = ?`) 201 params = append(params, query.OrgId) 202 203 if query.Name != "" || query.Id != 0 { 204 if query.Name != "" { 205 sql.WriteString(` AND alert_notification.name = ?`) 206 params = append(params, query.Name) 207 } 208 209 if query.Id != 0 { 210 sql.WriteString(` AND alert_notification.id = ?`) 211 params = append(params, query.Id) 212 } 213 } 214 215 results := make([]*models.AlertNotification, 0) 216 if err := sess.SQL(sql.String(), params...).Find(&results); err != nil { 217 return err 218 } 219 220 if len(results) == 0 { 221 query.Result = nil 222 } else { 223 query.Result = results[0] 224 } 225 226 return nil 227} 228 229func getAlertNotificationWithUidInternal(ctx context.Context, query *models.GetAlertNotificationsWithUidQuery, sess *DBSession) error { 230 var sql bytes.Buffer 231 params := make([]interface{}, 0) 232 233 sql.WriteString(`SELECT 234 alert_notification.id, 235 alert_notification.uid, 236 alert_notification.org_id, 237 alert_notification.name, 238 alert_notification.type, 239 alert_notification.created, 240 alert_notification.updated, 241 alert_notification.settings, 242 alert_notification.secure_settings, 243 alert_notification.is_default, 244 alert_notification.disable_resolve_message, 245 alert_notification.send_reminder, 246 alert_notification.frequency 247 FROM alert_notification 248 `) 249 250 sql.WriteString(` WHERE alert_notification.org_id = ? AND alert_notification.uid = ?`) 251 params = append(params, query.OrgId, query.Uid) 252 253 results := make([]*models.AlertNotification, 0) 254 if err := sess.SQL(sql.String(), params...).Find(&results); err != nil { 255 return err 256 } 257 258 if len(results) == 0 { 259 query.Result = nil 260 } else { 261 query.Result = results[0] 262 } 263 264 return nil 265} 266 267func (ss *SQLStore) CreateAlertNotificationCommand(ctx context.Context, cmd *models.CreateAlertNotificationCommand) error { 268 return inTransaction(func(sess *DBSession) error { 269 if cmd.Uid == "" { 270 uid, uidGenerationErr := generateNewAlertNotificationUid(ctx, sess, cmd.OrgId) 271 if uidGenerationErr != nil { 272 return uidGenerationErr 273 } 274 275 cmd.Uid = uid 276 } 277 existingQuery := &models.GetAlertNotificationsWithUidQuery{OrgId: cmd.OrgId, Uid: cmd.Uid} 278 err := getAlertNotificationWithUidInternal(ctx, existingQuery, sess) 279 280 if err != nil { 281 return err 282 } 283 284 if existingQuery.Result != nil { 285 return models.ErrAlertNotificationWithSameUIDExists 286 } 287 288 // check if name exists 289 sameNameQuery := &models.GetAlertNotificationsQuery{OrgId: cmd.OrgId, Name: cmd.Name} 290 if err := getAlertNotificationInternal(ctx, sameNameQuery, sess); err != nil { 291 return err 292 } 293 294 if sameNameQuery.Result != nil { 295 return models.ErrAlertNotificationWithSameNameExists 296 } 297 298 var frequency time.Duration 299 if cmd.SendReminder { 300 if cmd.Frequency == "" { 301 return models.ErrNotificationFrequencyNotFound 302 } 303 304 frequency, err = time.ParseDuration(cmd.Frequency) 305 if err != nil { 306 return err 307 } 308 } 309 310 // delete empty keys 311 for k, v := range cmd.SecureSettings { 312 if v == "" { 313 delete(cmd.SecureSettings, k) 314 } 315 } 316 317 alertNotification := &models.AlertNotification{ 318 Uid: cmd.Uid, 319 OrgId: cmd.OrgId, 320 Name: cmd.Name, 321 Type: cmd.Type, 322 Settings: cmd.Settings, 323 SecureSettings: cmd.EncryptedSecureSettings, 324 SendReminder: cmd.SendReminder, 325 DisableResolveMessage: cmd.DisableResolveMessage, 326 Frequency: frequency, 327 Created: time.Now(), 328 Updated: time.Now(), 329 IsDefault: cmd.IsDefault, 330 } 331 332 if _, err = sess.MustCols("send_reminder").Insert(alertNotification); err != nil { 333 return err 334 } 335 336 cmd.Result = alertNotification 337 return nil 338 }) 339} 340 341func generateNewAlertNotificationUid(ctx context.Context, sess *DBSession, orgId int64) (string, error) { 342 for i := 0; i < 3; i++ { 343 uid := util.GenerateShortUID() 344 exists, err := sess.Where("org_id=? AND uid=?", orgId, uid).Get(&models.AlertNotification{}) 345 if err != nil { 346 return "", err 347 } 348 349 if !exists { 350 return uid, nil 351 } 352 } 353 354 return "", models.ErrAlertNotificationFailedGenerateUniqueUid 355} 356 357func (ss *SQLStore) UpdateAlertNotification(ctx context.Context, cmd *models.UpdateAlertNotificationCommand) error { 358 return inTransaction(func(sess *DBSession) (err error) { 359 current := models.AlertNotification{} 360 361 if _, err = sess.ID(cmd.Id).Get(¤t); err != nil { 362 return err 363 } 364 365 if current.Id == 0 { 366 return models.ErrAlertNotificationNotFound 367 } 368 369 // check if name exists 370 sameNameQuery := &models.GetAlertNotificationsQuery{OrgId: cmd.OrgId, Name: cmd.Name} 371 if err := getAlertNotificationInternal(ctx, sameNameQuery, sess); err != nil { 372 return err 373 } 374 375 if sameNameQuery.Result != nil && sameNameQuery.Result.Id != current.Id { 376 return fmt.Errorf("alert notification name %q already exists", cmd.Name) 377 } 378 379 // delete empty keys 380 for k, v := range cmd.SecureSettings { 381 if v == "" { 382 delete(cmd.SecureSettings, k) 383 } 384 } 385 386 current.Updated = time.Now() 387 current.Settings = cmd.Settings 388 current.SecureSettings = cmd.EncryptedSecureSettings 389 current.Name = cmd.Name 390 current.Type = cmd.Type 391 current.IsDefault = cmd.IsDefault 392 current.SendReminder = cmd.SendReminder 393 current.DisableResolveMessage = cmd.DisableResolveMessage 394 395 if cmd.Uid != "" { 396 current.Uid = cmd.Uid 397 } 398 399 if current.SendReminder { 400 if cmd.Frequency == "" { 401 return models.ErrNotificationFrequencyNotFound 402 } 403 404 frequency, err := time.ParseDuration(cmd.Frequency) 405 if err != nil { 406 return err 407 } 408 409 current.Frequency = frequency 410 } 411 412 sess.UseBool("is_default", "send_reminder", "disable_resolve_message") 413 414 if affected, err := sess.ID(cmd.Id).Update(current); err != nil { 415 return err 416 } else if affected == 0 { 417 return fmt.Errorf("could not update alert notification") 418 } 419 420 cmd.Result = ¤t 421 return nil 422 }) 423} 424 425func (ss *SQLStore) UpdateAlertNotificationWithUid(ctx context.Context, cmd *models.UpdateAlertNotificationWithUidCommand) error { 426 getAlertNotificationWithUidQuery := &models.GetAlertNotificationsWithUidQuery{OrgId: cmd.OrgId, Uid: cmd.Uid} 427 428 if err := getAlertNotificationWithUidInternal(ctx, getAlertNotificationWithUidQuery, newSession(context.Background())); err != nil { 429 return err 430 } 431 432 current := getAlertNotificationWithUidQuery.Result 433 434 if current == nil { 435 return models.ErrAlertNotificationNotFound 436 } 437 438 if cmd.NewUid == "" { 439 cmd.NewUid = cmd.Uid 440 } 441 442 updateNotification := &models.UpdateAlertNotificationCommand{ 443 Id: current.Id, 444 Uid: cmd.NewUid, 445 Name: cmd.Name, 446 Type: cmd.Type, 447 SendReminder: cmd.SendReminder, 448 DisableResolveMessage: cmd.DisableResolveMessage, 449 Frequency: cmd.Frequency, 450 IsDefault: cmd.IsDefault, 451 Settings: cmd.Settings, 452 SecureSettings: cmd.SecureSettings, 453 454 OrgId: cmd.OrgId, 455 } 456 457 if err := bus.DispatchCtx(ctx, updateNotification); err != nil { 458 return err 459 } 460 461 cmd.Result = updateNotification.Result 462 463 return nil 464} 465 466func (ss *SQLStore) SetAlertNotificationStateToCompleteCommand(ctx context.Context, cmd *models.SetAlertNotificationStateToCompleteCommand) error { 467 return inTransactionCtx(ctx, func(sess *DBSession) error { 468 version := cmd.Version 469 var current models.AlertNotificationState 470 if _, err := sess.ID(cmd.Id).Get(¤t); err != nil { 471 return err 472 } 473 474 newVersion := cmd.Version + 1 475 sql := `UPDATE alert_notification_state SET 476 state = ?, 477 version = ?, 478 updated_at = ? 479 WHERE 480 id = ?` 481 482 _, err := sess.Exec(sql, models.AlertNotificationStateCompleted, newVersion, timeNow().Unix(), cmd.Id) 483 if err != nil { 484 return err 485 } 486 487 if current.Version != version { 488 sqlog.Error("notification state out of sync. the notification is marked as complete but has been modified between set as pending and completion.", "notifierId", current.NotifierId) 489 } 490 491 return nil 492 }) 493} 494 495func (ss *SQLStore) SetAlertNotificationStateToPendingCommand(ctx context.Context, cmd *models.SetAlertNotificationStateToPendingCommand) error { 496 return withDbSession(ctx, x, func(sess *DBSession) error { 497 newVersion := cmd.Version + 1 498 sql := `UPDATE alert_notification_state SET 499 state = ?, 500 version = ?, 501 updated_at = ?, 502 alert_rule_state_updated_version = ? 503 WHERE 504 id = ? AND 505 (version = ? OR alert_rule_state_updated_version < ?)` 506 507 res, err := sess.Exec(sql, 508 models.AlertNotificationStatePending, 509 newVersion, 510 timeNow().Unix(), 511 cmd.AlertRuleStateUpdatedVersion, 512 cmd.Id, 513 cmd.Version, 514 cmd.AlertRuleStateUpdatedVersion) 515 516 if err != nil { 517 return err 518 } 519 520 affected, _ := res.RowsAffected() 521 if affected == 0 { 522 return models.ErrAlertNotificationStateVersionConflict 523 } 524 525 cmd.ResultVersion = newVersion 526 527 return nil 528 }) 529} 530 531func (ss *SQLStore) GetOrCreateAlertNotificationState(ctx context.Context, cmd *models.GetOrCreateNotificationStateQuery) error { 532 return inTransactionCtx(ctx, func(sess *DBSession) error { 533 nj := &models.AlertNotificationState{} 534 535 exist, err := getAlertNotificationState(ctx, sess, cmd, nj) 536 537 // if exists, return it, otherwise create it with default values 538 if err != nil { 539 return err 540 } 541 542 if exist { 543 cmd.Result = nj 544 return nil 545 } 546 547 notificationState := &models.AlertNotificationState{ 548 OrgId: cmd.OrgId, 549 AlertId: cmd.AlertId, 550 NotifierId: cmd.NotifierId, 551 State: models.AlertNotificationStateUnknown, 552 UpdatedAt: timeNow().Unix(), 553 } 554 555 if _, err := sess.Insert(notificationState); err != nil { 556 if dialect.IsUniqueConstraintViolation(err) { 557 exist, err = getAlertNotificationState(ctx, sess, cmd, nj) 558 559 if err != nil { 560 return err 561 } 562 563 if !exist { 564 return errors.New("should not happen") 565 } 566 567 cmd.Result = nj 568 return nil 569 } 570 571 return err 572 } 573 574 cmd.Result = notificationState 575 return nil 576 }) 577} 578 579func getAlertNotificationState(ctx context.Context, sess *DBSession, cmd *models.GetOrCreateNotificationStateQuery, nj *models.AlertNotificationState) (bool, error) { 580 return sess. 581 Where("alert_notification_state.org_id = ?", cmd.OrgId). 582 Where("alert_notification_state.alert_id = ?", cmd.AlertId). 583 Where("alert_notification_state.notifier_id = ?", cmd.NotifierId). 584 Get(nj) 585} 586