Req: POST /v1/users/login.json
Resp:
Request Time 2014-11-06T14:28:40.514Z Request Name POST /v1/users/login.json Response Status 200 Parameters {“version”:“v1”,“action”:“create”,“controller”:“user_sessions”,“suppress_response_codes”:“true”,“ti_analytics”:“{\”mid\“:\”68c2554c54f77013\“,\”app_version\“:\”1.0\“,\”platform\“:\”android\“}”,“key”:“XC6OWeoFQGEgP6Xtfb9V0mbF9oqgicjT”,“format”:“json”,“password”:“[FILTERED]”,“login”:“APA91bFrI1834PnWi2drJJbWNdwJqLKh9bf28ncmrhGQkPELFL7zNT6feYtMqLzRnD3pV7OykE3xuer4jBufSBJTPmM3JLhjqJdBg7wR3eRHkvN76-hp2D1JgWgQHgR5N1vJqEBVZYkAI4RYHPAV5NQz1APBSoGozJPsUXhifwdWxz0ftxco5KQ”} User Agent Appcelerator Titanium/3.3.0 (XT1032; Android API Level: 19; it-IT;) Source IP 2.38.75.101 Response Size 648 bytes
Req: POST /v1/push_notification/subscribe.json
Resp:
Request Time 2014-11-06T14:28:42.063Z Request Name POST /v1/push_notification/subscribe.json Response Status 200 Parameters {“version”:“v1”,“channel”:“honeywell.userapp”,“type”:“android”,“device_token”:“APA91bFrI1834PnWi2drJJbWNdwJqLKh9bf28ncmrhGQkPELFL7zNT6feYtMqLzRnD3pV7OykE3xuer4jBufSBJTPmM3JLhjqJdBg7wR3eRHkvN76-hp2D1JgWgQHgR5N1vJqEBVZYkAI4RYHPAV5NQz1APBSoGozJPsUXhifwdWxz0ftxco5KQ”,“action”:“subscribe”,“controller”:“push_notification”,“suppress_response_codes”:“true”,“ti_analytics”:“{\”mid\“:\”68c2554c54f77013\“,\”app_version\“:\”1.0\“,\”platform\“:\”android\“}”,“key”:“XC6OWeoFQGEgP6Xtfb9V0mbF9oqgicjT”,“format”:“json”,“_session_id”:“ABVNeeMJmfygK5oGDlvG1d171bI”} User Agent Appcelerator Titanium/3.3.0 (XT1032; Android API Level: 19; it-IT;) Source IP 2.38.75.101 Response Size 73 bytes
Req: curl -b cookies.txt -c cookies.txt -X POST -F “channel=honeywell.userapp” -F “totokens=APA91bFrI1834PnWi2drJJbWNdwJqLKh9bf28ncmrhGQkPELFL7zNT6feYtMqLzRnD3pV7OykE3xuer4jBufSBJTPmM3JLhjqJdBg7wR3eRHkvN76-hp2D1JgWgQHgR5N1vJqEBVZYkAI4RYHPAV5NQz1APBSoGozJPsUXhifwdWxz0ftxco5KQ” -F “payload=prova_acs” “https://api.cloud.appcelerator.com/v1/push_notificationnotify_tokens.json?key=XC6OWeoFQGEgP6Xtfb9V0mbF9oqgicjT&pretty_json=true”
Resp:
Request Time 2014-11-06T14:56:57.648Z Request Name POST /v1/push_notification/notify_tokens.json Response Status 200 Parameters {“payload”:“prova_acs”,“version”:“v1”,“channel”:“honeywell.userapp”,“action”:“notify_tokens”,“controller”:“push_notification”,“key”:“XC6OWeoFQGEgP6Xtfb9V0mbF9oqgicjT”,“format”:“json”,“to_tokens”:“APA91bFrI1834PnWi2drJJbWNdwJqLKh9bf28ncmrhGQkPELFL7zNT6feYtMqLzRnD3pV7OykE3xuer4jBufSBJTPmM3JLhjqJdBg7wR3eRHkvN76-hp2D1JgWgQHgR5N1vJqEBVZYkAI4RYHPAV5NQz1APBSoGozJPsUXhifwdWxz0ftxco5KQ”,“pretty_json”:“true”} User Agent curl/7.26.0 Source IP 2.38.75.101 Response Size 64 bytes
Per una guida sulle push, vedere http://docs.befair.it/doku.php?id=push_notifications:appcelerator
doc http://stackoverflow.com/questions/3334809/python-urllib2-how-to-send-cookie-with-urlopen-request
To install PL/Python in a particular database: 1. apt-get install postgresql-plpython if necessary 2. form shell, createlang -U postgres -d push plpythonu
Since plpythonu is untrusted, onlu superusers can use it.
Create file with CREATE FUNCTION
Apply sql to db
ACS Login:
CREATE OR REPLACE FUNCTION push_notifications_login(url text, login text, password text)
RETURNS text AS
$BODY$
import urllib, urllib2
from cookielib import CookieJar
cj = CookieJar()
opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cj))
login_values = {'login': login,'password' : password}
login_data = urllib.urlencode(login_values)
login_resp = opener.open(url,login_data)
return login_resp.getcode()
$BODY$
LANGUAGE plpythonu VOLATILE
COST 100;
ALTER FUNCTION push_notifications_login(text, text, text)
OWNER TO postgres;
ACS push:
CREATE OR REPLACE FUNCTION push_notifications_notify(login_url text, login text, password text, push_url text, channel text, token text, payload text)
RETURNS text AS
$BODY$
import urllib, urllib2
from cookielib import CookieJar
cj = CookieJar()
opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cj))
login_values = {'login': login,'password' : password}
login_data = urllib.urlencode(login_values)
login_resp = opener.open(login_url,login_data)
if login_resp.getcode() == '200':
notify_values = {'channel' : channel, 'to_tokens':token,'payload':payload}
notify_data = urllib.urlencode(notify_values)
notify_resp = opener.open(push_url,notify_data)
if notify_restp.getcode() == '200':
return True
else:
return False
$BODY$
LANGUAGE plpythonu VOLATILE
COST 100;
ALTER FUNCTION push_notifications_notify(text, text, text, text, text, text, text)
OWNER TO postgres;
SLELECT[function_name]
ESEMPI:
SELECT push_notifications_login(url := 'https://api.cloud.appcelerator.com/v1/users/login.json?key=XC6OWeoFQGEgP6Xtfb9V0mbF9oqgicjT&pretty_json=true' , login := 'user', password := 'user');
SELECT push_notifications_notify(url := 'https://api.cloud.appcelerator.com/v1/push_notification/notify_tokens.json?key=XC6OWeoFQGEgP6Xtfb9V0mbF9oqgicjT&pretty_json=true', channel := 'honeywell.userapp', token := 'APA91bFrI1834PnWi2drJJbWNdwJqLKh9bf28ncmrhGQkPELFL7zNT6feYtMqLzRnD3pV7OykE3xuer4jBufSBJTPmM3JLhjqJdBg7wR3eRHkvN76-hp2D1JgWgQHgR5N1vJqEBVZYkAI4RYHPAV5NQz1APBSoGozJPsUXhifwdWxz0ftxco5KQ', payload := 'prova_acs_questions');
CREATE OR REPLACE FUNCTION notify_status_update() RETURNS trigger AS $notify_status_update$
DECLARE
app_key text;
channel text;
token text;
payload text;
login_url text;
notify_url text;
BEGIN
EXECUTE format('SELECT app_key FROM honeywell_heatpump_token WHERE heat_pump_id=$1;') USING OLD.id INTO app_key;
EXECUTE format('SELECT channel FROM honeywell_channel WHERE id=$1;') USING OLD.id INTO channel;
EXECUTE format('SELECT token FROM honeywell_heatpump_token WHERE heat_pump_id=$1;') USING OLD.id INTO token;
payload := 'status turned to' || cast(NEW.status as text);
login_url := 'https://api.cloud.appcelerator.com/v1/users/login.json?key=' || app_key || '&pretty_json=true';
notify_url := 'https://api.cloud.appcelerator.com/v1/push_notification/notify_tokens.json?key=' || app_key || '&pretty_json=true';
-- EXECUTE format('SELECT push_notifications_login(url := $1, login := ''admin'', password := ''admin'')'
) USING login_url;
EXECUTE format('SELECT push_notifications_notify(login_url := $1, login := $2, password := $3, push_url := $4, channel := $5, token := $6, payload := $7);'
) USING login_url, login, password, notify_url, channel, token, payload;
END;
$notify_status_update$ LANGUAGE plpgsql;
CREATE TRIGGER notify_status_update BEFORE UPDATE ON honeywell_heatpump
FOR EACH ROW EXECUTE PROCEDURE notify_status_update()
Target database:
Lista delle relazioni Schema | Nome | Tipo | Proprietario --------+---------------------------------+----------+-------------- public | honeywell_channel | tabella | matteo public | honeywell_channel_id_seq | sequenza | matteo public | honeywell_heatpump | tabella | matteo public | honeywell_heatpump_id_seq | sequenza | matteo public | honeywell_heatpump_token | tabella | matteo public | honeywell_heatpump_token_id_seq | sequenza | matteo
CREATE TABLE "honeywell_heatpump" ("id" SERIAL NOT NULL PRIMARY KEY ,"heat" varchar(32), "status" integer NOT NULL);
CREATE TABLE "honeywell_channel" ("id" SERIAL NOT NULL PRIMARY KEY, "channel" varchar(64));
CREATE TABLE "honeywell_heatpump_token" ("id" SERIAL NOT NULL PRIMARY KEY ,"heat_pump_id" integer NOT NULL REFERENCES "honeywell_heatpump" ("id"), "token" varchar(256),"app_key" varchar(64),"channel" integer NOT NULL REFERENCES "honeywell_channel" ("id"));
Define functions as in section 2 and 3.
INSERT INTO honeywell_heatpump (heat,status) VALUES ('heat1',0);
INSERT INTO honeywell_channel (channel) VALUES ('honeywell.userapp');
INSERT INTO honeywell_heatpump_token (heat_pump_id,token,app_key,channel) VALUES ((select id from honeywell_heatpump where id = 1),'APA91bFrI1834PnWi2drJJbWNdwJqLKh9bf28ncmrhGQkPELFL7zNT6feYtMqLzRnD3pV7OykE3xuer4jBufSBJTPmM3JLhjqJdBg7wR3eRHkvN76-hp2D1JgWgQHgR5N1vJqEBVZYkAI4RYHPAV5NQz1APBSoGozJPsUXhifwdWxz0ftxco5KQ','XC6OWeoFQGEgP6Xtfb9V0mbF9oqgicjT',(select id from honeywell_channel where id = 1));
UPDATE honeywell_heatpump SET status = 1;
Esempio di trigger su una tabella di uno schema.
CREATE OR REPLACE FUNCTION notify_status_update() RETURNS trigger AS $notify_status_update$
DECLARE
app_key text := 'lEJ3oT6DG92XYbx0pH3wzL2kOdphFr7y';
channel text := TG_TABLE_SCHEMA;
token text := 'everyone';
payload text;
login_url text := 'https://api.cloud.appcelerator.com/v1/users/login.json?key=' || app_key ;
notify_url text := 'https://api.cloud.appcelerator.com/v1/push_notification/notify_tokens.json?key=' || app_key ;
OLD RECORD;
TABLE_FULLNAME text := '"' || TG_TABLE_SCHEMA || '"."' || TG_TABLE_NAME || '"';
board_label text;
BEGIN
RAISE NOTICE 'channel: %', channel;
SELECT label FROM acs_service.chamber WHERE dbname = TG_TABLE_SCHEMA INTO board_label;
SELECT * FROM get_last_reliable_var_row(TABLE_FULLNAME,NEW.id) AS row_out (
id integer,
variablename character varying,
val character varying,
statuscode character varying,
"timestamp" integer,
timestampe integer,
started integer,
sampletime integer,
idactor integer
) INTO OLD;
RAISE NOTICE 'OLD.val: % NEW.val: %', OLD.val, NEW.val;
-- Do nothing if only statuscode is changed...
IF OLD.val != NEW.val THEN
IF NEW.val::float <> 0 THEN
payload := board_label || ' is faulting: error ' || NEW.val;
ELSE
payload := board_label || ' no faulting anymore';
END IF;
RAISE NOTICE 'login_url: %', login_url;
EXECUTE format('SELECT push_notifications_login(url := $1, login := ''admin'', password := ''admin'')'
) USING login_url;
EXECUTE format('SELECT push_notifications_notify(url := $1,channel := $2,token := $3,payload := $4);'
) USING notify_url, channel, token, payload;
RAISE NOTICE 'notify_url: % channel: % token: % payload: %', notify_url, channel, token, payload;
END IF;
RETURN NEW;
END;
$notify_status_update$ LANGUAGE plpgsql;
La CREATE è fatta su un AFTER INSERT perchè ad un update su una righa corrisponde, in realtà, una CREATE nella tabella.
drop trigger notify_status_update ON [RELATION]; CREATE TRIGGER notify_status_update AFTER INSERT ON [RELATION] FOR EACH ROW WHEN ( NEW.statuscode NOT LIKE '%FF__') EXECUTE PROCEDURE notify_status_update();
Ad esempio: “KIT-ff-1e-c0-13-d4-0c”.“v1”
Funzione per prendere l'ultima row valida nella tabella.
Dato che siamo in una AFTER INSERT, l'ultima row valida è in realtà quella corrente.
Per questo, dalla select viene esclusa l'ultima riga appena inserita (quella con id = NEW.id)
CREATE OR REPLACE FUNCTION get_last_reliable_var_row(tablename text, id integer)
RETURNS RECORD AS
$BODY$
DECLARE
last_row RECORD;
BEGIN
EXECUTE 'SELECT * FROM ' || tablename || ' WHERE statuscode NOT LIKE '
|| quote_literal('%%%%FF__') || ' AND id NOT IN (' || id || ') ORDER BY timestampe DESC LIMIT 1'
INTO last_row;
RETURN last_row;
END;
$BODY$ LANGUAGE plpgsql VOLATILE
COST 100;