asterix-app/src/test/resources/runtimets/queries/open-closed/query-proposal.aql

/*
 * Description     : Insert open data into internal dataset and query the open data
 * Expected Result : Success
 * Date            : 23rd October 2012
 * Notes           : This test was written to cover the scenario which is used in the proposal.
 */

drop dataverse test if exists;
create dataverse test;

use dataverse test;

create type TweetMessageType as open {
tweetid : string,
user : {
         screen-name: string,
         lang: string,
         friends_count: int32,
         statuses_count: int32,
         name: string,
         followers_count: int32
},    sender-location: point?,
      send-time: datetime,
      referred-topics: {{ string }},
      message-text: string
};

create dataset TweetMessages(TweetMessageType)
primary key tweetid;

insert into dataset TweetMessages(
   {
      "tweetid": "1023",
      "user": {
         "screen-name": "dflynn24",
         "lang": "en",
         "friends_count": 46,
         "statuses_count": 987,
         "name": "danielle flynn",
         "followers_count": 47
      },
      "sender-location": create-point(40.904177,-72.958996),
      "send-time": datetime("2010-02-21T11:56:02-05:00"),
      "referred-topics": {{ "verizon" }},
      "message-text": "i need a #verizon phone like nowwwww! : ("
   });

insert into dataset TweetMessages(
   {
      "tweetid": "1024",
      "user": {
         "screen-name": "miriamorous",
         "lang": "en",
         "friends_count": 69,
         "statuses_count": 1068,
         "name": "Miriam Songco",
         "followers_count": 78
      },
      "send-time": datetime("2010-02-21T11:11:43-08:00"),
      "referred-topics": {{ "commercials", "verizon", "att" }},
      "message-text": "#verizon & #att #commercials, so competitive"
   });

insert into dataset TweetMessages(
   {
      "tweetid": "1025",
      "user": {
         "screen-name": "dj33",
         "lang": "en",
         "friends_count": 96,
         "send-time": "2010-02-21T11:56:02-05:00",      
         "statuses_count": 1696,
         "name": "Don Jango",
         "followers_count": 22
      },
      "send-time": datetime("2010-02-21T12:38:44-05:00"),
      "referred-topics": {{ "charlotte" }},
      "message-text": "Chillin at dca waiting for 900am flight to #charlotte and from there to providenciales"
   });

insert into dataset TweetMessages( 
    { "tweetid": "1026", 
      "user": { 
         "screen-name": "reallyleila", 
         "lang": "en", 
         "friends_count": 106, 
         "statuses_count": 107, 
         "name": "Leila Samii", 
         "followers_count": 52 
       }, 
       "send-time": datetime("2010-02-21T21:31:57-06:00"),
       "referred-topics": {{ "verizon", "at&t", "iphone" }},
       "message-text": "I think a switch from #verizon to #at&t may be in my near future... my smartphone is like a land line compared to the #iphone!"
});

write output to nc1:"rttest/open-closed_query-proposal.adm";

for $tp1 in (
    for $tweet in dataset('TweetMessages')
        where some $topic in $tweet.referred-topics satisfies contains($topic, 'verizon')
            for $tp in $tweet.referred-topics
                return 
                { "topic": $tp }
)
group by $tp2 := $tp1.topic with $tp1
order by $tp2
return { "topic": $tp2, "count": count($tp1) }