/*Create an alert when more than 20 type 'A' squirrels 
are in Jennifer's backyard. */

/* We simply return a message every time 20 new squirrel of type A
   enter that backyard. There is no benefit in using windows in
   this application */

STREAM SquirrelSensors(sID char(6), region char(8), Time int);

TABLE  SquirrelType(sID char(6), sType char(1));

AGGREGATE every20(iID char(6)): char
{ 
	TABLE temp(cnt int) MEMORY;
        INITIALIZE: {
		INSERT intO temp VALUES(1)
	}
	ITERATE: {
		UPDATE temp SET cnt= cnt+1;
	        UPDATE temp SET cnt=0 WHERE cnt=20;
	        INSERT intO return VALUES('ALERT! 20 more in JWs yard')
		  WHERE SQLCODE =0
	}
}    

LOAD FROM 'stype.txt' intO SquirrelType;
LOAD FROM 'ssensors.txt' INTO SquirrelSensors;

 /* the next two lines just print the input and should be left 
    out from the actual program
SELECT ST.* FROM SquirrelType AS ST;
SELECT sID, region,  Time  FROM SquirrelSensors; */
SELECT every20(sID)
  FROM SquirrelSensors AS SS, SquirrelType AS ST
  WHERE SS.sID=ST.sID AND sType='A'
    AND SS.region=' JW_yard'
   /* there is an extra space in the input file before JW_yard */