===== 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 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; ==== 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(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() ==== 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;