NotificationSqlDao.sql.stg

group NotificationSqlDao;

getReadyNotifications() ::= <<
    select
      record_id
      , id
      , class_name
      , account_id
      , notification_key
      , created_date
      , creating_owner
      , effective_date
      , queue_name
      , processing_owner
      , processing_available_date
      , processing_state
    from notifications
    FORCE INDEX (idx_comp_where)
    where
      effective_date \<= :now
      and queue_name = :queueName
      and processing_state != 'PROCESSED'
      and processing_state != 'REMOVED'
      and (processing_owner IS NULL OR processing_available_date \<= :now)
    order by
      effective_date asc
      , created_date asc
      , record_id
    limit :max
    ;
>>

getNotificationForAccountAndDate() ::= <<
   select
     record_id
     , id
     , class_name
     , account_id
     , notification_key
     , created_date
     , creating_owner
     , effective_date
     , queue_name
     , processing_owner
     , processing_available_date
     , processing_state
   from notifications
   where
   account_id = :accountId AND effective_date = :effectiveDate
   ;
>>

removeNotification()  ::= <<
  update notifications
    set
  processing_state = 'REMOVED'
    where
  id = :id
; 
>>

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

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

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

insertNotification() ::= <<
    insert into notifications (
      id
      , class_name
      , account_id
      , notification_key
      , created_date
      , creating_owner
      , effective_date
      , queue_name
      , processing_owner
      , processing_available_date
      , processing_state
    ) values (
      :id
      , :className
      , :accountId
      , :notificationKey
      , :createdDate
      , :creatingOwner
      , :effectiveDate
      , :queueName
      , :processingOwner
      , :processingAvailableDate
      , :processingState
    );   
>>

insertClaimedHistory() ::= <<
    insert into claimed_notifications (
          owner_id
        , claimed_date
        , notification_id
      ) values (
          :ownerId
        , :claimedDate
        , :notificationId
      );
>>