NotificationSqlDao.sql.stg

group NotificationSqlDao;

getReadyNotifications(now, max) ::= <<
    select
      id
      ,  notification_id
      , notification_key
      , created_dt
      , creating_owner
      , effective_dt
      , queue_name
      , processing_owner
      , processing_available_dt
      , processing_state
    from notifications
    where
      effective_dt \<= :now
      and queue_name = :queue_name
      and processing_state != 'PROCESSED'
      and processing_state != 'REMOVED'
      and (processing_owner IS NULL OR processing_available_dt \<= :now)
    order by
      effective_dt asc
      , created_dt asc
      , id
    limit :max
    ;
>>


claimNotification(owner, next_available, id, now) ::= <<
    update notifications
    set
      processing_owner = :owner
      , processing_available_dt = :next_available
      , processing_state = 'IN_PROCESSING'
    where
      id = :id
      and processing_state != 'PROCESSED'
      and processing_state != 'REMOVED'
      and (processing_owner IS NULL OR processing_available_dt \<= :now)
    ;
>>

clearNotification(id, owner) ::= <<
    update notifications
    set
      processing_state = 'PROCESSED'
    where
      id = :id
    ;
>>

removeNotificationsByKey(notification_key) ::= <<
    update notifications
    set
      processing_state = 'REMOVED'
    where
      notification_key = :notification_key
    ;
>>


insertNotification() ::= <<
    insert into notifications (
      notification_id
    , notification_key
      , created_dt
      , creating_owner
      , effective_dt
      , queue_name
      , processing_owner
      , processing_available_dt
      , processing_state
    ) values (
      :notification_id
      , :notification_key
      , :created_dt
      , :creating_owner
      , :effective_dt
      , :queue_name
      , :processing_owner
      , :processing_available_dt
      , :processing_state
    );   
>>


insertClaimedHistory(sequence_id, owner, hostname, claimed_dt, notification_id) ::= <<
    insert into claimed_notifications (
          owner_id
        , claimed_dt
        , notification_id
      ) values (
          :owner
        , :claimed_dt
        , :notification_id
      );
>>