This is an old revision of the document!
Table of Contents
GUIDA PER LE PUSH TRIGGHERATE DA postgreSQL DB
0. push con Curl all'ACS app da server
Registration fatta da device android
Login
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
Registration
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
Push
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
PROVA CON urllib, urllib2, cookielib.CookieJar
doc http://stackoverflow.com/questions/3334809/python-urllib2-how-to-send-cookie-with-urlopen-request
1. creare funzione per push con plpythonu (plpython2)
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 honeywell.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 honeywell.push_notifications_login(text, text, text) OWNER TO matteo;
ACS push:
CREATE OR REPLACE FUNCTION honeywell.push_notifications_notify(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)) notify_values = {{'channel' : channel, 'to_tokens':token,'payload':payload}} login_data = urllib.urlencode(notify_values) notify_resp = opener.open(url,login_data) return notify_resp.getcode() $BODY$ LANGUAGE plpythonu VOLATILE COST 100; ALTER FUNCTION honeywell.push_notifications_notify(text, text, text) OWNER TO matteo;
2. push con plpythonu (plpython2) da shell postgresql
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');
3. creare trigger pl/pgsql
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(url := $1,channel := $2,token := $3,payload := $4);' ) USING 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()
4. agganciare trigger al server
5. ESEMPIO
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;
5.1 ESEMPIO 2
Esempio di trigger su una tabella di uno schema.
Creazione funzione da triggherare
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;
Creazione del trigger (DROP preventivo se necessario)
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”
GET LAST ROW BEFORE INSERT
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;