Il nostro punto della strada

User Tools

Site Tools

Trace: • postgresql
push_notifications:postgresql

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(((OLD.status + 1 ) % 2) 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 := ''user'', password := ''user'')'
        ) 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;

<code>

INSERT INTO honeywell_channel (channel) VALUES ('welfare')

INSERT INTO honeywell_heatpump_token (heat_pump_id,token,app_key,channel) VALUES 1);

1)
select id from honeywell_heatpump where id = 1),'APA91bFHoqygR3HUe3zFVirGJnWEQ6psTRqh7hkQ03Hdiyod_O3Ce6BUvHayPiR1jOxLRYbjkDE_6kN7mqMtt3id0LARa9xaQrXW-CkQGMudr84-5J7t5ejOyUJuhl5zEFgPHiNswx92qlv3pEVga3V6iRxC27_6c1no7cbWRy27aKswq9sYE3Y','OpSVzgLtcqPn5tDP96djYVHQNjEAOROU',(select id from honeywell_channel where id = 2
push_notifications/postgresql.1423650409.txt.gz · Last modified: 2015/02/11 10:26 by letti