Il nostro punto della strada

User Tools

Site Tools

Trace: • postgresql
push_notifications:postgresql

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}
    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;
push_notifications/postgresql.txt · Last modified: 2015/03/03 17:33 by letti