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(&current); 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 = &current
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(&current); 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