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 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}}
    login_data = urllib.urlencode(notify_values)

    notify_resp = opener.open(push_url,login_data)
    return notify_resp.getcode()
else:
    return login_resp.getcode()

$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;
push_notifications/postgresql.1425401362.txt.gz · Last modified: 2015/03/03 16:49 by letti