asterixdb/asterix-app/src/test/resources/optimizerts/queries/ASTERIXDB-2402.sqlpp

/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */

drop dataverse channels if exists;
create dataverse channels;
use channels;

create type UserLocation as {
  location: circle,
  userName: string,
  timeStamp: datetime
};


create type UserLocationFeedType as {
  location: circle,
  userName: string
};

create type EmergencyReport as {
  reportId: uuid,
  Etype: string,
  location: circle,
  timeStamp: datetime
};

create type EmergencyReportFeedType as {
  Etype: string,
  location: circle
};


create type EmergencyShelter as {
  shelterName: string,
  location: point
};

create dataset UserLocations(UserLocation)
primary key userName;
create dataset Shelters(EmergencyShelter)
primary key shelterName;
create dataset Reports(EmergencyReport)
primary key reportId autogenerated;

create index location_time on UserLocations(timeStamp);
create index u_location on UserLocations(location) type RTREE;
create index s_location on Shelters(location) type RTREE;
create index report_time on Reports(timeStamp);

create function EmergenciesNearMe(userName) {
  (
  select report, shelters from
   ( select value r from Reports r where r.timeStamp >
   current_datetime() - day_time_duration("PT10S"))report,
  UserLocations u
    let shelters = (select s.location from Shelters s where spatial_intersect(s.location,u.location))
  where u.userName = userName
  and spatial_intersect(report.location,u.location)
  )
};

create type result as {
  resultId:uuid
};
create type channelSub as {
  channelSubId:uuid
};
create type brokerSub as {
  channelSubId:uuid,
  brokerSubId:uuid
};
create type broke as {
  DataverseName: string,
  BrokerName: string,
  BrokerEndpoint: string
};

create dataset EmergenciesNearMeChannelResults(result) primary key resultId autogenerated;
create dataset EmergenciesNearMeChannelChannelSubscriptions(channelSub) primary key channelSubId;
create dataset EmergenciesNearMeChannelBrokerSubscriptions(brokerSub) primary key channelSubId,brokerSubId;
create dataset Broker(broke) primary key DataverseName,BrokerName;



SET inline_with "false";
insert into channels.EmergenciesNearMeChannelResults as a (
with channelExecutionTime as current_datetime()
select result, channelExecutionTime, sub.channelSubId as channelSubId,current_datetime() as deliveryTime,
(select b.BrokerEndPoint, bs.brokerSubId from
channels.EmergenciesNearMeChannelBrokerSubscriptions bs,
channels.Broker b
where bs.BrokerName = b.BrokerName
and bs.DataverseName = b.DataverseName
and bs.channelSubId = sub.channelSubId
) as brokerSubIds
from channels.EmergenciesNearMeChannelChannelSubscriptions sub,
channels.EmergenciesNearMe(sub.param0) result
) returning
(select
a.channelExecutionTime, a.result, sub.BrokerEndpoint
from (select sub from a.brokerSubIds sub) sub
group by sub.BrokerEndpoint
);