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;